SQL文のまとめ
基本構文と分類
DDL-データベース操作
DDL-テーブル操作-作成と確認
DDLデータ型
create table employees(
employee_id int comment 'ID番号',
employee_number varchar(10) comment '従業員番号',
full_name varchar(10) comment '氏名',
sex char(1) comment '性別',
age_years tinyint unsigned comment '年齢',
id_card char(18) comment '身分証番号',
join_date date comment '入社日'
)comment '従業員マスタ';
DDL-テーブル操作-変更と削除
alter table employees change nickname user_name varchar(30) comment 'ユーザー名';
DDLまとめ
-- DDL-データベース操作
show databases; # すべてのデータベースを表示
create database データベース名; # データベースを作成
use データベース名; # データベースを選択
select database(); # 現在のデータベースを確認
drop database データベース名; # データベースを削除
-- DDL-テーブル操作
show tables; # 現在のデータベース内のすべてのテーブルを表示
create table テーブル名(カラム カラムタイプ,カラム カラムタイプ); # テーブルを作成
desc テーブル名; # テーブル構造を確認
show create table テーブル名; # 指定されたテーブルの作成文を表示
alter table テーブル名 add/modify/change/drop/rename to # フィールド追加/データ型変更/フィールド削除/テーブル名変更
drop table テーブル名; # テーブルを削除
DML-挿入
insert into staff(employee_id,employee_number,full_name,sex,age_years,id_card,join_date) values (1,'1','田中太郎','男',21,'123456789098765432','2023-01-05');
insert into staff values (2,'2','山田花子','女',22,'123456789098455432','2023-01-05');
select * from staff;
insert into staff values (3,'3','佐藤次郎','男',23,'123456787898455432','2023-01-05'),(4,'4','鈴木三郎','女',24,'123258789098455432','2023-01-05');
DML-更新と削除
-- IDが1のデータを更新し、氏名をzhangsanに変更
update staff set full_name = '張三' where employee_id = 1;
-- IDが1のデータを更新し、氏名を趙敏、性別を女に変更
update staff set full_name = '趙敏',sex = '女' where employee_id = 1;
-- 全ての従業員の入社日を2008-01-01に変更
update staff set join_date = '2008-01-01'
-- 性別が女の従業員を削除
delete from staff where sex = '女';
-- 全ての従業員を削除
delete from staff;
DMLまとめ
DQL-基本クエリ
-- 基本クエリ
-- 1.指定されたカラムname,workno,ageを取得
select full_name,employee_number,age_years from employees;
-- 2.全てのカラムを取得
select employee_id,employee_number,full_name,sex,age_years,id_card,work_address,join_date from employees;
select * from employees;
-- 3.全ての従業員の勤務先を取得し、別名を付与
select work_address as '勤務先' from employees;
select work_address '勤務先' from employees;
-- 4.会社の勤務先を取得（重複なし）
select distinct work_address '勤務先' from employees; # distinctによる重複除去
DQL-条件クエリ
-- 条件クエリ
-- 1.年齢が88の従業員を検索
select * from employees where age_years = 88;
-- 2.年齢が20未満の従業員情報を取得
select * from employees where age_years < 20;
-- 3.年齢が20以下の従業員情報を取得
select * from employees where age_years <= 20;
-- 4.身分証番号が空の従業員情報を取得
select * from employees where id_card is null;
-- 5.身分証番号が存在する従業員情報を取得
select * from employees where id_card is not null;
-- 6.年齢が88ではない従業員情報を取得
select * from employees where age_years != 88;
select * from employees where age_years <> 88;
-- 7.15歳以上20歳以下の従業員情報を取得
select * from employees where age_years >= 15 && age_years <= 20;
select * from employees where age_years >= 15 and age_years <= 20;
select * from employees where age_years between 15 and 20;
-- 8.性別が女でかつ25歳未満の従業員情報を取得
select * from employees where sex = '女' and age_years < 25;
-- 9.年齢が18または20または40の従業員情報を取得
select * from employees where age_years = 18 or age_years = 20 or age_years = 40;
select * from employees where age_years in(18,20,40);
-- 10.名前が2文字の従業員情報を取得 _%
select * from employees where full_name = '__';
-- 11.身分証番号の最後がXの従業員情報を取得
select * from employees where id_card like '%X';
select * from employees where id_card like '_________________X'; # 17個の下線
DQL-集約関数
-- 集約関数
-- 1.企業内の従業員数をカウント
select count(*) from employees;
-- 2.企業内の平均年齢を計算
select avg(age_years) from employees;
-- 3.企業内の最大年齢を取得
select max(age_years) from employees;
-- 4.企業内の最小年齢を取得
select min(age_years) from employees;
-- 5.西安市の従業員の年齢合計を取得
select sum(age_years) from employees where work_address = '西安市';
DQL-グループ化クエリ
-- グループ化クエリ
-- 1.性別でグループ化し、男性と女性の従業員数を統計
select sex,count(*) from employees group by sex;
-- 2.性別でグループ化し、男性と女性の平均年齢を統計
select sex,avg(age_years) from employees group by sex;
-- 3.年齢が45未満の従業員を検索し、従業員数が3以上の勤務先を取得
-- a.年齢が45未満の従業員を検索
select * from employees where age_years < 45;
-- b.年齢が45未満の従業員を勤務先でグループ化
select work_address,count(*) from employees where age_years < 45 group by work_address;
-- c.従業員数が3以上の勤務先を取得
select work_address,count(*) from employees where age_years < 45 group by work_address having count(*) >= 3;
select work_address,count(*) address_count from employees where age_years < 45 group by work_address having address_count >= 3; # 別名で判断
DQL-ソートクエリ
-- ソートクエリ
-- 1.従業員を年齢で昇順ソート
select * from employees order by age_years ASC;
select * from employees order by age_years;
-- 2.入社日で降順ソート
select * from employees order by join_date desc;
-- 3.年齢で昇順ソートし、年齢が同じ場合は入社日で降順ソート
select * from employees order by age_years asc,join_date desc;
select * from employees order by age_years,join_date desc;
DQL-ページングクエリ
-- ページングクエリ
-- 1.1ページ目の従業員データを取得（10件表示）
select * from employees limit 0,10;
-- 2.2ページ目の従業員データを取得（10件表示） ---> (ページ番号-1)*表示件数
select * from employees limit 10,10;
DQL-実践演習
-- 1.20,21,22,23歳の女性従業員情報を取得
select * from employees where sex = '女' and age_years in(20,21,22,23);
-- 2.性別が男で、年齢が20-40歳（含む）で、名前が3文字の従業員を取得
select * from employees where sex = '男' and (age_years between 20 and 40) and full_name like '___';
-- 3.60歳未満の男性と女性従業員数を統計
select sex,count(*) from employees where age_years < 60 group by sex;
-- 4.35歳未満の従業員の氏名と年齢を取得し、年齢で昇順ソート、年齢が同じ場合は入社日で降順ソート
select full_name,age_years from employees where age_years<= 35 order by age_years asc,join_date desc;
-- 5.性別が男で、年齢が20-40歳（含む）の上位5件を取得し、年齢で昇順ソート、年齢が同じ場合は入社日で昇順ソート
select * from employees where sex = '男' and age_years between 20 and 40 order by age_years asc,join_date asc limit 5;
DQL-実行順序
-- 15歳以上の従業員の氏名と年齢を取得し、年齢で昇順ソート
select full_name,age_years from employees where age_years > 15 order by age_years asc;
select full_name,age_years from employees where age_years > 15 order by age_years;
DQLまとめ
DCL-ユーザー管理
-- ユーザーTomをローカルホストからアクセス可能にし、パスワードを設定
create user 'Tom'@'localhost' identified by '111111';
-- ユーザーyueを任意のホストからアクセス可能にし、パスワードを設定
create user 'yue'@'%' identified by '123456'; # %で全ホストアクセスを許可
-- yueユーザーのパスワードを変更
alter user 'yue'@'%' identified with mysql_native_password by '111111';
-- Tom@localhostユーザーを削除
drop user 'Tom'@'localhost';
DCL-権限制御
-- 権限を確認
show grants for 'yue'@'%';
-- 権限を付与
grant all on itcast.* to 'yue'@'%';
-- 権限を剥奪
revoke all on itcast.* from 'yue'@'%';