1.简单查询

SELECT USERNAME FROM STUDENT WHERE ID = 1;

2.between

SELECT USERNAME FROM STUDENT WHERE SCORE BETWEEN 60 AND 100;    //性能更优
等价于：SELECT USERNAME FROM STUDENT WHERE SCORE >= 60 AND SCORE <= 100;

3.like模糊查询

SELECT USERNAME FROM STUDENT WHERE USERNAME LIKE "_杰%";  //第二个字为杰的
SELECT USERNAME FROM STUDENT WHERE USERNAME LIKE "张\_%" ESCAPE '\';

4.IS NULL

SELECT USERNAME FROM STUDENT WHERE PHONE IS NULL;

5.IN集合查询

SELECT USERNAME FROM STUDENT WHERE ADDRESS IN ("北京","广州");    //性能更优
等价于SELECT USERNAME FROM STUDENT WHERE ADDRESS = "北京" OR ADDRESS = "广州";

6.DISTINCT

// DISTINCT短语的作用范围是所有目标列，SELECT子句中只出现一次。作用：消除结果集重复的元素。
SELECT DISTINCT ADDRESS FROM STUDENT;

7.INTO 查询结果存入新表

SELECT USERNAME,PHONE,ADDRESS INTO BJ_STUDENT FROM STUDENT WHERE ADDRESS = "北京";

8.ORDER BY

SELECT USERNAME FROM STUDENT ORDER BY AGE,SCORE DESC;  
//先按AGE升序排列，再按SCORE降序排列。(缺结果图)

Orderby.png

9.TOP

SELECT TOP 2 * FROM STUDENT ORDER BY SCORE;  
//分数最少的两个
SELECT TOP 2 WITH TIES * FROM STUDENT ORDER BY SCORE; 
//分数最少的两个集合

10.UNION 并

//把查询的结果合并起来并去掉重复的元组（要保留，必须用UNION ALL）
SELECT USERNAME FROM STUDENT WHERE ADDRESS = '北京' 
    -> UNION 
    -> SELECT USERNAME FROM STUDENT WHERE ADDRESS = '上海';
//等价于
SELECT DISTINCT USERNAME FROM STUDENT WHERE ADDRESS IN ('北京', '上海');

11.INTERSECT 交

SELECT * FROM STUDENT WHERE SCHOOL = 'CTGU' 
    -> INTERSECT 
    -> SELECT * FROM STUDENT WHERE SCORE > 100;
//等价于
SELECT * FROM STUDENT WHERE SCHOOL = 'CTGU' AND SCORE > 100;

12.EXCEPT 差

SELECT USERNAME FROM STUDENT WHERE ADDRESS = '北京'
    -> EXCEPT 
    -> SELECT USERNAME FROM STUDENT WHERE ADDRESS = '上海';

13.连接查询

SELECT USER.ID, USERNAME, ORDER_NAME, PRICE 
    -> FROM USER, USER_ORDER 
    -> WHERE USER.ID = USER_ORDER.USER_ID;

SELECT USER.ID, USERNAME, ORDER_NAME, PRICE 
    -> FROM USER, USER_ORDER 
    -> WHERE USER.ID = USER_ORDER.USER_ID 
    -> AND ADDRESS = '北京';

SELECT USER.ID, USERNAME,PRODUCE_NAME, PRICE, PCS, PHONE, ADDRESS 
    -> FROM USER, USER_PRODUCE, PRODUCE 
    -> WHERE USER.ID = USER_PRODUCE.USER_ID 
    -> AND PRODUCE.ID = USER_PRODUCE.PRODUCE_ID;

14.内连接

SELECT USER.ID, USERNAME, ADDRESS, PCS
    -> FROM USER INNER JOIN USER_PRODUCE
    -> ON USER.ID = USER_PRODUCE.USER_ID
    -> WHERE ADDRESS = '北京';

SELECT USER.ID, USERNAME, ADDRESS, PCS 
    -> FROM USER INNER JOIN USER_PRODUCE 
    -> ON USER.ID = USER_PRODUCE.USER_ID 
    -> INNER JOIN PRODUCE ON PRODUCE.ID = USER_PRODUCE.PRODUCE_ID 
    -> WHERE USERNAME = 'ct';

15.自连接

SELECT UA.* FROM USER UA, USER UB 
    -> WHERE UA.ADDRESS = UB.ADDRESS 
    -> AND UB.USERNAME = 'ct';

16.外连接

//左连接
SELECT USER.ID, USERNAME, ADDRESS, PCS FROM USER     
    -> LEFT JOIN USER_PRODUCE ON USER.ID = USER_PRODUCE.USER_ID;
//右连接
SELECT USER.ID, USERNAME, ADDRESS, PCS FROM USER     
    -> RIGHT JOIN USER_PRODUCE ON USER.ID = USER_PRODUCE.USER_ID;

17.分组及汇总查询

// COUNT
SELECT COUNT(DISTINCT ADDRESS) FROM USER;
// 统计起别名
SELECT COUNT(DISTINCT Address) AS AddressNumber FROM USER;
// SUM
SELECT COUNT(*) AS produceNumber, SUM(PCS) as SumNumber FROM USER_PRODUCE;
// GROUP BY
SELECT PRODUCE_ID, SUM(PCS) as SumAmount FROM USER_PRODUCE GROUP BY PRODUCE_ID;
//分组，统计
SELECT ADDRESS, COUNT(*) as TotalPerson FROM USER GROUP BY ADDRESS;
//分组，统计，max，min
SELECT PRODUCE_ID,COUNT(*) as CountNum, MAX(PCS) as max_PCS, MIN(PCS) as min_PCS FROM USER_PRODUCE GROUP BY PRODUCE_ID HAVING COUNT(*) > 0;
//mysql 居然不支持 GROUPING SETS
GROUPING SETS ？

18.子查询

SELECT * FROM USER WHERE ID = (SELECT USER_ID FROM USER_PRODUCE WHERE ID = 4);

SELECT * FROM USER WHERE ID IN (SELECT USER_ID FROM USER_PRODUCE WHERE PRODUCE_ID = 2);

//查询子查询中最好的记录，应该有优化方案
SELECT * FROM USER_PRODUCE WHERE USER_ID = 1 AND PRODUCE_ID = 2 AND PCS >= ALL (SELECT PCS FROM USER_PRODUCE WHERE USER_ID = 1 AND PRODUCE_ID = 2);
//优化
SELECT * FROM USER_PRODUCE WHERE ID = (SELECT MAX(PCS) FROM USER_PRODUCE WHERE USER_ID = 1 AND PRODUCE_ID = 2);
// ANY ALL 使用较少
// 内外层互相关嵌套查询
SELECT * FROM USER_PRODUCE A WHERE PCS = (SELECT MAX(PCS) FROM USER_PRODUCE B WHERE A.USER_ID = B.USER_ID AND A.PRODUCE_ID = B.PRODUCE_ID) ORDER BY ID;

数据库.png

19.EXISTS嵌套查询

SELECT * FROM USER_PRODUCE WHERE EXISTS (SELECT * FROM PRODUCE WHERE PRODUCE.ID = USER_PRODUCE.PRODUCE_ID AND PRODUCE_NAME = '橙汁');
//等价于
SELECT * FROM USER_PRODUCE WHERE PRODUCE_ID IN (SELECT ID FROM PRODUCE WHERE PRODUCE_NAME = '橙汁');
// NOT EXISTS
SELECT * FROM PRODUCE A WHERE NOT EXISTS (SELECT * FROM PRODUCE B WHERE B.PRICE > A.PRICE AND B.PRODUCE_NAME='橙汁');

20.子查询额外操作

// insert 子查询
insert into TargetTable(id, total) select produce_id, sum(pcs) from user_produce group by produce_id;
// update 子查询
update PRODUCE set PRICE = PRICE * 2 where id not in (select PRODUCE_ID from USER_PRODUCE group by PRODUCE_ID having sum(PCS)>=10);
// delete 子查询
delete from USER_PRODUCE where PRODUCE_ID in (select ID from PRODUCE where PRODUCE_NAME = '咖啡');

21.数据库设计三范式

1NF：如果关系模式R中每个属性值都是一个不可分解的数据项，则该关系模式满足第一范式。

2NF：满足1NF且所有非主属性都完全依赖与其主码。

3NF：数据不能存在传递关系，即每个属性都跟主键有直接关系而不是间接关系

22.新建数据库 utf-8

CREATE DATABASE mytest DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

23.创建用户并赋予用户权限

create user 'repl'@'192.168.1.%' identified by 'repl';
grant all privileges on mytest.* to 'repl'@'192.168.1.%' identified by 'repl';
grant all privileges on *.* to 'repl'@'192.168.1.%' identified by 'repl';