> ### ***MySQL語言***
* [ ] DDL 數據定義語言，create,drop,alter
* [ ] DML 數據操作語言，insert,update,delete
* [ ] DQL 數據查詢語言, select
* [ ] DCL 數據控制語言, grant,commit,rollback
*****
```
select distinct username from user; 查詢出唯一值
select username from user is null; 查詢空值null
select username from user is not null; 查詢非空值
```
*****
> ### ***like使用方法***
* [ ] “%” 匹配所有
* [ ] “\_” 匹配一個字符
> ### ***regexp正則查詢***
```
select * from tablename where name regexp "^php"; 查詢以php開頭
select * from tablename where name regexp "php$"; 查詢以php結尾
```
> ### *** MySQL常用函數***
```
concat()：連接函數
rand()：隨機數
count()：統計個數
sum()：求和
avg()：平均值
max()：最大值
min()：最小值
select * from tablename order by rand() limit 3; 隨機查出3條數據
```
> ### ***group by分組聚合***
```
select concat(class_id,'-','class') as class,count(id) as total from user group by class_id； #查詢各班級的總人數
```
> ### *** 多表查詢***
```
select * from user where id in (select max(id) from user); #嵌套查詢
```
一對一
一對多 多個的留另外一個id
一個表一個主體
兩個表是一對一關系，則可以合并
*****
> ### ***鏈接查詢***
```
select * from class,user left join user on class.id = user.class\_id;
select class.name,count(user.id) from class left join user on class.id = user.class\_id group by class.id;
```
```
#所有班的總人數統計出來，將沒有人數的班級也要統計出來
select class.name, if(count(user.id),count(user.id),'無') from class left join user on class.id = user.class\_id group by class.id;
#所有班的總人數統計出來，將沒有人數的班級也要統計出來，將人數為0的顯示出無
```
> 內鏈接inner join on，完全等于普通多表查詢，必須是符合條件的多個表的數據才會顯示！
> 右鏈接：把右邊的表數據全部輸出
> 左鏈接：把左邊的表數據全部輸出
*****
求出及格與不及格的人數，一條sql寫出：
第一種方案：（簡單）
```
select (select count(*) from user where score>=60) as '及格', (select count(\*) from user where score<60) as '不及格'
```
第二種方案：
```
select sum(if(scroe>=60),1,0)) '及格', sum(if(scroe<60),1,0)) '不及格' from user;
having
```
要在分組聚合后用having,此時where不能用
```
select class_id from user group by class_id having class_id<2;
```
