数据库MySQL一些命令整理（一）_表's_sale_rec_mor other'已存在是什么意思

一些命令的一览：

对数据库的增删改查：

增数据库

CREATE DATABASE my_database; -- 新建数据库
CREATE DATABASE IF NOT EXISIT my_database;    -- 如果数据库不存在才新建数据库
1
2

删数据库

DROP DATABASE IF EXISIT my_database;   -- 如果数据库存在才删除，因为重复删除会报错。
DROP DATABASE my_database;    -- 删除数据库
1
2

更改数据库

USE my_database;    -- 切换数据库
ALTER DATABASE my_database DEFAULT CHARACTER SET UTF8;    -- 更改数据库的字符集
1
2

查询数据库

SHOW DATABASES;     -- 显示所有数据库
SELECT DATABASE();      -- 显示当前选中的数据库
SHOW CREATE DATABASE my_database;     -- 显示数据库的创建语句以及characterset。
1
2
3

对表的增删改查：

我们能执行选中操作的只有数据库，所以任何命令，都需要显式地指定表名。

新建表

-- 增加一个表
CREATE TABLE  t_student (
    first_name VARCHAR(30),
	second_name VARCHAR(30),
	age INT
);

-- 如果表不存在才增加一个表
CREATE TABLE IF NOT EXIST t_student(
    first_name VARCHAR(30),
	second_name VARCHAR(30),
	age INT
);

-- 可以在新建表的时候就定义后约束
CREATE TABLE IF NOT EXISTS t_students(
	first_name VARCHAR(30),
    second_name VARCHAR(30),
    age INT,
    NOT NULL(first_name,second_name,age)    -- 建议属性都加个NOT NULL约束比较好
);

-- 新增一个类似的表
CREATE TABLE t_newtable LIKE t_oldtable;

-- 使用蠕虫复制来复制表
CREATE TABLE t_newtable LIKE t_oldtable;	-- 先建好一个新的表
INSERT INTO t_newtable (属性值1，属性值2) -- 这里的属性值可以省略
SELECT 属性值1, 属性值2   -- 注意，这里的属性值可以是常量来的。就t_newtable对应的属性永远都插常量。
FROM t_oldtable;
-- t_newtable后面的属性值要跟SELECT后面的属性值一对一对应才可以的。
-- 就像是普通INSERT INTO那样，只是VALUES 变成了SELECT而已。

-- 这个命令适合已经有表存在的情况，如果想新建一个表，建议用
CREATE TABLE t_newtable (
SELECT * 
FROM t_oldtable
WHERE condition);
-- 注意，跟建普通表一样，括号不能省。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

修改表的信息

ALTER TABLE t_students RENAME TO t_student;     -- 修改表的命名
ALTER TABLE t_student CHARACTER SET utf8;   -- 把一个表的编码改成指定编码
ALTER TABLE t_student ENGINE='InnoDB';   -- 修改表的引擎，用InnoDB就好了，支持外键和事物。
/*用 SHOW CREATE TABLE t_student 可以看到表的当前引擎*/
1
2
3
4

查看表的信息

SHOW TABLES; -- 显示选中的数据库(use my_database)的所有表
SHOW CREATE TABLE t_student;   -- 显示表的创建语句

-- 显示表的所有列与列的属性，两者一样的。
DESC t_student;  
SHOW COLUMNS FROM t_student; 
1
2
3
4
5
6

删除表

DROP TABLE t_student;          -- 直接删除表
DROP TABLE IF EXISIT t_student;   -- 如果表存在就删除表
1
2

对表的列进行操作

ALTER TABLE t_student ADD score INT;  -- 增加表一列
ALTER TABLE t_student CHANGE score address VARCHAR(30) NOT NULL;  -- 修改一个列的名字和类型 
ALTER TABLE t_student DROP scroe; 	 -- 删除一列

--增加一个主键，可以用多个列作为联合主键
ALTER TABLE t_student ADD PRIMARY KEY [AUTO_INCREMENT] (id1 [,id2]);   

-- 删除主键
ALTER TABLE t_student DROP PRIMARY KEY;  -- 因为主键一般只有一个，所以要删除就只需这样。
-- 但是当主键连住AUTO_INCREMENT，就不能用这种方法删除主键了。

 -- 修改自增值的大小
ALTER TABLE t_student AUTO_INCREASE = 100;

-- AUTO_INCREMENT 只能由两种方法设置
1、在创建表的时候就在PRIMARY KEY后面设置
2、用MODIFY把一个列设为PRIMARY KEY的时候设置（注意要先把原来的主键删除了）

-- 增加一个外键列
ALTER TABLE t_student 
ADD CONSTRAINT fk_1 
FOREIGN KEY (classroom_id) 
REFERENCE t_classroom (id);  -- 比较少用，因为java也能做外键操作，而且性能更好。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

对表中数据增删改查

我们能选中的只有数据库，不能选中表，所以任何对表的增删改查操作，都需要指定表名。

增

-- 可以只增加一部分
INSERT INTO t_student (valueName_1,valueName_2) 
VALUES (value_1,value2),(value_1,value2),(value_1,value2)...;

--需要全部属性都增加，不增加的要用null
INSERT INTO t_student VALUES (value_1,value_2,value_3...);
1
2
3
4
5
6

删

-- 按条件删除
DELETE FROM t_student WHERE condition1 && condition2;

-- 没有条件删除，一条一条删，auto_increase值会保留
DELETE FROM t_student;

-- 销毁表格，auto_increase值不会保留
TRUNCATE TABLE t_student;
1
2
3
4
5
6
7
8

改

-- 按条件更改某一列的数值
UPDATE t_student SET valueName_1 = newVAl,valueName_2 = newVal 
WHERE condition1 && condition2

--全部对所有数值全部改完
UPDATE t_student SET valueName_1 = newVal ,valueName_2= newVal;
1
2
3
4
5
6

查

--查询表所有数据
SELECT * FROM t_student;

-- 查询指定列
SELECT valName_1 , valName_2 FROM t_student;

-- 别名查询，加上别名方便观看和处理查询到的数据
SELECT valName_1 otherName, valName_2 anotherName FROM t_student;
 
-- 清除重复值查询。
SELECT DISTINCT valName_1,valName_2 FROM t_student;

-- 查询结果参与运算：要求参与运算的值都是整数才行
	-- 某列数据与固定值运算
SELECT valName_1 + 20 FROM t_student;
	
	-- 多列数据之间运算
SELECT price * count TotalPrice FROM t_student;
	
-- 条件查询
SELECT * FROM t_student WHERE id>3 && id<10;

-- 查询后排序
SELECT * FROM t_student ORDER BY score [DESC / ASC]  -- 默认是升序，ASC，降序需要设置。

-- 分页查询
SELECT * FROM t_stduent LIMIT beginIndex,pageSize;  
/*
分页查询分为逻辑分页和物理分页，逻辑分页就是一次过把东西都拿出来，在JAVA中模拟分页效果，内存占用比较大，但是速度就很快，物理分页就是真实地从MYSQL中拿出指定位置和长度的数据，内存占用比较低， 但是速度慢一点。
*/

-- 当只需要前面几个的时候，可以用LIMIT来限制生成数据的数量
SELECT b.s_id 学号, s_name 姓名, s_birth 生日, s_sex 性别, c_name 科目, s_score 成绩
FROM t_scores a,t_students b,t_courses c
WHERE a.s_id = b.s_id && c.c_id = a.c_id
ORDER BY a.s_score DESC
LIMIT 5;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

一些DDL命令：

-- 创建用户名
CREATE USER 'username'@'主机名' IDENTIFIED BY '密码';

-- 删除用户
DROP USER 'username'@'主机名'
1
2
3
4
5

• IDENTIFIED BY '密码’是可以不要的，就等于用户不需要输入密码就能登录。
• 主机名用于指定该用户从哪个主机上登录，如果只有本地能用这个用户，那就把主机名设置为’localhost’，如果想让任意远程主机登录，可以把主机名设为’%’。
• 数据库是以"用户名"+"主机名"来分别用户的，同一个用户名用不同的主机名，也算是不同的用户。
• 所有用户信息都存放在mysql数据库下的user 表中，密码是加密过的。
• 要登录有删除用户权限的用户才能删除用户。所以不能自己删除自己

-- 给用户赋予权限
GRANT 权限1,权限2,权限3,权限4 ON database_name.table_name TO 'username'@'主机名'

-- 收回用户的权限
REVOKE 权限1，权限2，权限3 ON database_name.table_name FROM 'username'@'主机名'

-- 查看权限
SHOW　GRANTS FOR 'username'@'主机名'
1
2
3
4
5
6
7
8

• 可以用ALL来代替各种权限。
• 数据库和表名定义了权限在哪个数据库的哪个表可用，用*.* 可以表示所有数据库的所有表。

-- 修改普通用户密码：
(登录有权限的用户状态下)SET PASSWORD FOR 'username'@'主机名'='new password';

-- 修改管理员密码：
(不必登录)mysqladmin -uroot -p password;  -- 然后按照提示操作即可
1
2
3
4
5

• mysqladmin是一个程序。password是传给这个程序的命令。

运用命令的方法

一些条件语句

在WHERE后面可以放这些语句：

-- BETWEEN ... AND...  不同数据库处理方式不一样，MySQL是左右都是闭区间
SELECT * FROM t_student WHERE score BETWEEN 80 AND 100; 
-- 当然也可以配合NOT来使用

-- IN运算符：查询列的值是否在指定的集合中。精确查找指定的。
SELECT * FROM t_student WHERE id IN (1,4,5,10);

-- 空值判断： IS NULL
SELECT * FROM t_stdent WHERE score IS NULL;  -- 查找成绩为空的人。

-- 模糊查询：LIKE,只有LIKE条件才能用通配符！等号或者不等号不能用通配符
SELECT * FROM t_student WHERE name LIKE '王_%';
-- 其中'_'表示一个任意字符，
-- '%'表示0个或多个任意字符，

-- 住址不包含'lon'三个字母的人，这时不能用'!'来表示取反了
SELECT * FROM Persons
WHERE City NOT LIKE '%lon%'

-- 经典的判断
SELECT * FROM t_student WHERE score >= 80 && name LIKE '王_%' OR id IN (2,4,6);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

还有一些常用的比较运算符：

一些聚合函数

数据库也是可以用函数的，叫聚合函数，常用的聚合函数如下：

-- 把一些属性连接成字符串返回
SELECT CONCAT(productName,'的售价为：',salePrice*cutoff) detail FROM table_name
COUNT(*); --统计计算最大值
COUNT(val_name); -- 统计某列的行数，不包括null值。
COUNT(DISTINCT val_name); --统计某列不重复的行数。
MIN(val_name);--统计计算最小值
MAX(val_name);-- 统计计算最大值
SUM(val_name);-- 统计计算求和
AVG(val_name);-- 统计计算求平均
GROUPCONCAT(val_name); -- 把表的值聚合成一条数据

FIRST(val_name); -- 返回第一个值，可以配合ORDER BY来获取值。
LAST(val_name); --返回最后一个值，也可以配合ORDER BY
-- 这两个方法跟MAX那些不同的地方就是，ORDER是可以考虑很多前提的，而MAX则只能处理1个列。

SELECT AVG(val_name) FROM table_name;
SELECT COUNT(*) FROM table_name;   -- 在JAVA中必须用long来接受结果。
SELECT SUM(val_name) FROM table_name WHERE dir_id IN(2，3）;
-- 这些函数都是返回一个数值，除了放在SELECT后面获得返回的表，还可以直接用来做HAVING后面做比较的，用来跟其他数据比较的：

-- 找到订单总价小于2000的客户。
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000  -- 因为聚合函数不能放在WHERE后面，所以当聚合函数作为条件时，就要用到HAVING代替。HAVING是专门用来处理GROUP BY分裂出来的表的。

-- 找客户 "Bush" 或 "Adams" 拥有超过 1500 的订单总金额。
SELECT Customer,SUM(OrderPrice) FROM Orders
WHERE Customer='Bush' OR Customer='Adams'
GROUP BY Customer
HAVING SUM(OrderPrice)>1500
-- 要注意跟VIEW区分开，VIEW是放在FROM后面的。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

• 一个查询语句中，只要**SELECT**后面跟了一个这种聚合函数，表中的所有记录就会马上塌陷成一行。如果有GROUP BY则会塌陷成好几个表。
• 因为GROUP BY就是把一个表先分裂成几个表，就特别适合跟这些聚合函数在一起使用，聚合函数把一个表塌陷了，还是有好几个数据**（因为GROUP BY会把表分开的）**
• 另外GROUP BY在把表分裂成几个以后，即使没有聚合函数，也是会把表塌陷成几个表的。
• 把表分裂以后，可以直接：HAVING COUNT(id) 获得某个分裂表的数量
• AVG() 函数如果只有null值，则返回null，否则像其他所有聚合函数一样，忽略null值
• 所有聚合函数都一定要传参数！
• WHERE 后面不能使用聚合函数的！聚合函数只能用在两个地方，SELECT后面和HAVING后面
• 要注意，所有聚合函数的作用域都优先是被group by分裂出来的表
  • COUNT(id) 是只计算分裂表中的id数，
  • SUM(score) 是只计算分列表中的每一条score的总和
  • SUM(CASE WHEN) 是只对分裂表中的对应列每一条记录进行转换然后求最大值。

一些提示

• 像CREATE ，ALTER ，DROP ， RENAME ，SHOW 这种既能对database操作，又能对table进行操作的关键字，就统一需要加上DATABASE 或者 TABLE

• 像 SELECT , INSERT INTO , DELETE FROM , UPDATE 这种只能作用在table的命令行，就不需要加上TABLE或者DATABASE。

• INT(10) 的意思是数字最多有10位，**VARCHAR(20)**表示最多有20个字符。

• DATE是yyyy-mm-dd 的格式，DATE是要加单引号的，在SQL中除了整数、小数以外，其他类型数字都要加上单引号。

• 用飘号：` 包住的关键字，会失去关键字的效果，变成普通的单词。

• 想跨数据库访问表，应该用如下格式：otherDB.otherTable 其他库+.+其他表。

• 不要在数据库中放入时间，只放入日期，那么就可以用日期进行各种比较。一涉及时间数据库就蒙圈了。

• 当一个表包含了另外两个表的主键，那么可以用联合主键来代替自己维护一个主键。比如：

  CREATE TABLE t_students (	-- 学生表格
    s_id varchar(20) PRIMARY KEY,
    s_name varchar(20) DEFAULT '',
    s_birth date DEFAULT '1990-09-20',
    s_sex varchar(10) DEFAULT '',
  );
  
  CREATE TABLE t_coureses(    --课程表格
  	c_id VARCHAR(20) PRIMARY KEY,
  	c_name VARCHAR(20) DEFAULT '' NOT NULL,
  	t_id VARCHAR(20) NOT NULL
  );
  
  CREATE TABLE t_scores(      -- 成绩表格
  	s_id VARCHAR(20)  , -- 学生编号
  	c_id VARCHAR(20) , -- 课程编号
  	s_score DECIMAL NOT NULL,  -- 分数
      PRIMARY KEY (s_id,c_id)
  );
  -- 成绩表格可以用学生表格和课程表格作为主键，只有两个主键都一样，才认为是同一条成绩数据。这样可以省下一个主键位。
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20

• 但是要注意：联合主键的定义方式只能像上面代码那样定义，如果逐个定义主键，会报错诶。

• 另外，因为一个表只能有一个主键，所以不用为主键起名也可以用语句正常增加和删除主键，算式一个小语法糖啦

  -- 下面这句话
  SELECT * 
  FROM t_student ts 
  WHERE condition_1 
  GROUP BY ts_valName
  HAVING condition_2
  ORDER BY valName_1 
  DESC LIMIT beginIndex  , pageSize; 
  -- 执行的真正顺序是：
  1. FROM t_student ts
  2. WHERE condition_1
  3. GROUP BY ts_valueName
  4. SELECT *
  5. HAVING condition_2
  6. ORDER BY val_name DESC
  7. LIMIT beginIndex , pageSize;
  
  -- 所以在Where的时候就能用到table的别名，但是不能用select中定义的别名。
  -- where后面不能定义别名。HAVING 后面也不能定义别名，但是HAVING可以用SELECT定义的别名
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19

• 清除重复查询时，当有多个字段名，要求指定的字段都一样才算是重复的。

• 应该把筛选最多的条件放到WHERE前面，可以提高点性能

MySQL支持的数值类型：

• 金额货币一般都是用DECIMAL的啦
• 一个数字如果是FLOAT(5,2) 表示有5个数字，其中小数点后面有2个。
• 如果CHAR(20)来存储，如果不足20个，还是按照20个字符位置来存，这样找起来速度就快很多
• VARCHAR(20)如果不足20个字符就用实际的字符数储存，占位小一点但是速度会低一点。
• 但日常使用还是**VARCHAR(20)**这种多一点。

表的一些约束：

约束可以直接接到类型后面。但建议PRIMARY KEY和FROEIGN KEY 还是另外放在后面。

• NOT NULL 非空约束，如果传入null值会报错，一般值最好还是加个非空约束，对运行速度有提升的;

• UNIQUE 唯一约束，该列的内容必须唯一。

• 增加**PRIMARY KEY这个约束会送你一个NOT NULL和UNIQUE，无论是建表时期还是后来加**的。

• PRIMARY KEY 最好只用来约束数值类型，而且加上**AUTO_INCREASE。系统并不会自动变成AUTO_INCREASE** 的！还是要自己设置。只有PRIMARY KEY才有资格加上AUTO_INCREASE

• AUTO_INCREASE 只要不赋值或者赋值为null，就会从1开始，步长为1，不能与DEFAULT共用。

• **AUTO_INCREASE这个约束只能加在INT等数值后面，而且DEFAULT只能是NULL否则会报错。如果给AUTO_INCREASE**的主键赋值为null，系统会自动计算新的主键值。

• 如果没有特别定义约束的话，是啥约束都没有的，没有NOT NULL，没有定义DEFAULT，也没有UNIQUE的。每次MODIFY或者CHANGE都要重新定义约束，但MODIFY不会删除PRIMARY KEY约束。

• NULL值是不能进行**>=、！=、<=** 那些逻辑判断的，他只能接受 IS NULL 和 IS NOT NULL这两种判断。

• NULL值也是不能参与四则运算的，可以用MySQL的**IFNULL(val_name,0)**来定义如果值为null时，用来计算的默认值

• DEFAULT XX 默认值约束，当不传入数据，就会用这个默认值，不能给有AUTO_INCREASE的PRIMARY KEY设置默认值。

• NOT NULL 只能在建表的时候就定义好的，想追加或者删除只能用MODIFY了，不能用ALTER TABLE ADD 那个的。

• UNIQUE 和 DEFAULT 都可以。在建表以后追加和删除：

  -- 向已存在的表定义新增default值
  ALTER TABLE Persons
  ALTER City SET DEFAULT 'SANDNES';
  
  -- 把已经定好的default值删掉
  ALTER TABLE Persons
  ALTER City DROP DEFAULT;
  
  -- 向已存在的表追加UNIQUE
  ALTER TABLE Persons ADD UNIQUE (name);
  
  -- 给UNIQUE约束起个名可以方便删除UNQIE约束
  ALTER TABLE Persons
  ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName);
  
  -- 删除UNIQUE约束
  ALTER TABLE Persons
  DROP INDEX uc_PersonID;   --注意是INDEX而不是UNQUE
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18

• 所有约束都可以通过ADD，MODIFY，CHANGE等方式来重置和定义，上面所述的只是多一个快捷一点的方式而已。

• 当PRIMARY KEY跟AUTO_INCREMENT一起用，则不能直接通过DROP删除PRIMARY KEY了，要先用MODIFY把AUTO_INCREMENT 去掉（MODIFY不能去掉PRIMARY KEY），再DROP PRIMARY KEY

外键约束

• 外键格式有点复杂，只能记住吧：

  -- 在建表时期增加一个外键
  CREATE TABLE Employee(
  	id INT,
      name VARCHAR(20),
      dep_id INT,
      PRIMARY KEY AUTO_INCREASE (id),
      CONSTRAINT E_d_D_id_fk    -- 注意，要在定义好外键的列之后，再声明外键约束！
      FOREIGN KEY (dep_id) REFERENCES Department (id)   --要注意从表的属性列也要用括号包住的！
  );
  
  -- 当建好表以后，对表添加外键指向另一个表
  ALTER TABLE Employee ADD 
  CONSTRAINT employee_dep_id_to_department_id_fk
  FOREIGN KEY (dep_id) REFERENCES Department(id);
  
  -- 删除外键约束
  ALTER TABLE Employee DROP FOREIGN KEY E_d_D_id_fk;  
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17

• 如果想关联的列跟随着主键更新或者删除，应该在定义的时候加上ON DELETE CASCADE 以及ON UPDATE CASCADE

  -- 创建带有级联更新外键的表完整写法
  CREATE TABLE Employee(
  	id INT,
      name VARCHAR(20),
      dep_id INT,
      PRIMARY KEY AUTO_INCREASE (id),
      CONSTRAINT E_d_D_id_fk 
      FOREIGN KEY (dep_id) REFERENCES Department (id) --要注意主从表的属性列也要用括号包住的！
      ON DELETE CASCADE ON UPDATE CASCADE
  );
  
  -- 添加级联更新的外键完整写法
  ALTER TABLE Employee ADD 
  CONSTRAINT E_d_D_id_fk 
  FOREIGN KEY (dep_id) REFERENCES Department (id) 
  ON DELETE CASCADE ON UPDATE CASCADE
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16

• 但是要知道，级联操作好像很叼，但是他运行效率比java低很多，尽量不要在数据库中进行各种运算操作。

• 外键只能是主表的主键或者空。

JOIN导致的笛卡尔积：

当多表查询时，就会有笛卡尔积，因为两个表相交的话，会导致表之间的记录相乘。所以才要有不会重复的主键，方便多表查询时可以找到对应的记录。

-- 多表查询，有多少个表，就至少要有n-1个链接
SELECT p.id,p.productName,pd.dirName,ps.storeNum,p.salePrice,p.salePrice*ps.storeNum 利润
FROM product p,productdir pd,productstock ps  -- FROM中可以声明表的别名，可以让SELECT用。
WHERE p.dir_id=pd.id AND p.id=ps.product_id   -- 用链接消除笛卡尔积
ORDER BY 利润 DESC;  -- 这里可以用别名了

-- 但是这种叫隐式连接，已经逐渐被淘汰了，以后都用JOIN...ON ...这种形式的显示连接了
SELECT p.id,p.productName,pd.dirName,ps.storeNum,p.salePrice,p.salePrice*ps.storeNum 利润
FROM product p
JOIN productdir pd ON p.dir_id = pd.id		-- 显式连接
JOIN productstock ps ON ps.product_id = p.id	-- 显示连接
ORDER BY 利润 DESC;
1
2
3
4
5
6
7
8
9
10
11
12

上面是**JOIN…ON…的形式，他是INNER JOIN … ON…**的简化版，就用简化版好了。

当有表格Persons

还有表格Orders

如果只是用JOIN，而不用ON的时候，会返回如下结果

当用了ON以后，就会按照要求，把Persons.id_p和Orders.id_p相同的筛选出来，也就是标红的两条记录。

如果用普通JOIN，则只返回匹配到的，也就是这两条标红的。

如果用LEFT JOIN，就会把JOIN左边的表，无论是否有匹配到，全部都返回，**如果没有匹配到，那值对应的值就是null。**非常强势

RIGHT JOIN同理的。注意，JOIN右边的列表，永远是RIGHT。

还有一个两个都强势的：FULL JOIN，两个都返回了再说，没有匹配就用null，有匹配才填信息。

用UNION关键字来组合两个表的信息

UNION / UNION ALL是用来把两个查询的结果合并的，他要求两次查询的结果，列数相等，类型相似。

SELECT val_name(s) FROM table_1
UNION ALL   -- UNION 和 UNION ALL 的区别就是是否会保留相同的记录，UNION ALL会保留相同的记录的。
SELECT val_name(s) FROM table_2;
1
2
3

UNION本来是被设计用来组合两个不同的表的，但是他的效率比OR高，所以即使我们是对同一个表操作，也可以使用UNION来代替OR！

获得的记录，属性名会由UNION前面的语句来定义

SQL判断是否相同的逻辑是：所有列的元素都相同，才认为两条记录相同。

用CREATE SELECT 跟INSERT INTO SELECT 互补

在INSERT INTO table_2 (a,b,c) SELECT a , b ,c FROM table_1;之前，我们总是要新建一个表：CREATE TABLE table_2 LIKE table_1 ，显得有点麻烦了。而SELECT INTO 就是用来处理没有表的情况。

但是MySQL不支持SELECT INTO命令！他有代替的命令：

-- 把table_1选中的列插入table_2中
CREATE TABLE Persons_orders_backup(
SELECT p.id_p 客户id,p.name 客户姓名,p.city 城市,o.order_num 订单号码
FROM Persons p
JOIN Orders o ON p.id_p = o.id_p); -- 是可以用JOIN的！
-- 因为新的表啥信息都没有，所以会以SELECT后面指定的属性值作为列
1
2
3
4
5
6

CREATE SELECT要求table_2不存在，所以在已经存在一个table_2，只是想用另外的表扩充下的情况他是处理不了的。

但是用来作为快速备份，或者把查询的结果保存成一个新的表格，是非常有用的！跟INSERT INTO SELECT各有优势。

用视图简化你的代码

数据库里是没有变量保存值这种概念的，视图是你保存数据的唯一方式了。

-- 新建一个视图
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;
-- 当新建了一个视图之后，就可以把它看成一张表了，MySQL每次用到这个视图，都会重新查询一次，保证数据是最新的

-- 新建好以后就可以使用视图，就当是普通表来用就好。
SELECT * FROM view_name;   

-- 更新一个视图，表示用同一个视图名，但是视图里面的内容就不同了
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;

-- 删除视图
DROP VIEW view_name;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

利用DATE()函数来定义时间

虽然JAVA也能做，但是知道一下SQL里是如何操作时间的也是不错的，但是SQL处理时间就很尴尬，一般都是用来处理日期：

一些关于时间的方法

利用GROUP BY 给表格分类

-- GROUP BY的常规用法
SELECT Customer,SUM(OrderPrice) 
FROM Orders
GROUP BY Customer

-- 可以看做GROUP BY按照列，把一个表分成好几个表，然后SELECT 对每一个表执行操作以后，再把所有表结合成一个表。有点像java 中stream流的toMap方法。
1
2
3
4
5
6

子查询语句的几种结果

所谓子查询，就是让一个查询结果成为另一个查询的一部分：

-- 找到成绩比平均分高的学生
SELECT * 
FROM t_student
WHERE s_score > (SELECT AVG(s_score) FROM t_student);
1
2
3
4

注意，子查询语句一定要放在小括号中的，否则程序会报错。

数据库查询的步骤是先查询出子查询的结果，再将子查询结果作为父查询的一部分执行父查询。

子查询一共会有3种情况：

• 单行单列：可以用作 ＞、＜、＝、！=、＞＝、＜＝的比较条件（案例同上）。

• 多行单列：可以放在IN或者NOT IN后面作为条件

  -- 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
  select stu_b.s_id 学号, avg(sco_b.s_score) 平均分
  from t_students stu_b
         join t_scores sco_b on sco_b.s_id = stu_b.s_id and stu_b.s_id in (
    select stu.s_id
    from t_students stu
           left join t_scores sco on sco.s_id = stu.s_id and sco.s_score < 60
    group by stu.s_name
    having COUNT(*) >= 2
  )
  group by stu_b.s_name;
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11

• 多行多列：可以放在FROM 或者JOIN 后面。暂时找不到比较好的案例。

功能异常强大的CASE函数

这部分参考了璀璨小二的博客

CASE WHEN THEN ELSE END 这个语法对数据库操作太重要了，他有两种写法：

-- 简单CASE函数
CASE sex
WHEN 1 THEN '男'
WHEN 2 THEN '女'
ELSE '其他' END

-- CASE搜索函数
CASE
WHEN sex=1 THEN '男'
WHEN sex=2 THEN '女'
ELSE '其他' END
1
2
3
4
5
6
7
8
9
10
11

CASE函数其实可以看成一个普通列，只是他会逐条改变这个列的内容，所以这个函数可以放在SELECT、HAVING、WHERE、聚合函数参数这些地方。

他最后会把这个列的值变成THEN 后面的类型。

案例一：用CASE来按条件筛选

-- 按照人口统计人数
SELECT country, 
       SUM( CASE WHEN sex = '1' THEN 
                      population ELSE 0 END),  --男性人口 
       SUM( CASE WHEN sex = '2' THEN 
                      population ELSE 0 END)   --女性人口 
FROM  Table_A 
GROUP BY country; 
-- 这样就可以一个select统计两个数，不用JOIN
1
2
3
4
5
6
7
8
9

所以有了case以后，就再也不用为了一个数值就JOIN一个新的表了！

案例二：在Check中使用CASE函数

可以作为检查条件，又不会阻挡其他数据的输入

-- 要求如果是女员工，那个工资就要比1000高
CONSTRAINT check_salary CHECK 
           (CASE WHEN sex = '2' 
                 THEN CASE WHEN salary > 1000 
                       		THEN 1 ELSE 0 END 
                 ELSE 1 END = 1 ) 

1
2
3
4
5
6
7

案例三：利用CASE函数筛选更新条件

/*
工资5000以上的职员，工资减少10% 
工资在2000到4600之间的职员，工资增加15% 
很容易考虑的是选择执行两次UPDATE语句，如下所示 
*/

UPDATE Personnel 
SET salary = CASE WHEN salary >= 5000 
　            THEN salary * 0.9 
WHEN salary >= 2000 AND salary < 4600 
THEN salary * 1.15 
ELSE salary END; 
1
2
3
4
5
6
7
8
9
10
11
12

案例四：嵌套CASE函数

假设有学生表如下：

/*
只选修一门课程的人，返回那门课程的ID
选修多门课程的人，返回所选的主课程ID
 */
select *,case
  when count(stu.c_id) =1  
    then max(stu.c_id)
    else max(case stu.main_flag
      when 'y' then stu.c_id
      else null end )
      end 主修课程ID
from Students stu
group by stu.s_id;
1
2
3
4
5
6
7
8
9
10
11
12
13

一些值得记录的案例：

在IN()中加入多行单列的子查询

-- 查询没学过"张三"老师授课的同学的信息
SELECT *
FROM t_students
WHERE s_id NOT IN (
SELECT stu_a.`s_id`
FROM t_students stu_a
JOIN t_scores sco ON sco.`s_id`=stu_a.`s_id`
JOIN t_courses cou ON sco.`c_id`=cou.`c_id`
JOIN t_teachers tea ON cou.`t_id`=tea.`t_id` AND tea.`t_name`='张三'
);

-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
SELECT *
FROM t_students
WHERE s_id IN (SELECT s_id FROM t_scores WHERE c_id='01')
AND s_id NOT IN(SELECT s_id FROM t_scores WHERE c_id='02');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

同一个表的多次插入，以获得特定的值

-- 查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT a.* ,b.s_score  01_score,c.s_score  02_score 
FROM t_students a 
JOIN t_scores b ON a.s_id=b.s_id AND b.c_id='01'
LEFT JOIN t_scores c ON a.s_id=c.s_id AND c.c_id='02'
WHERE b.s_score>c.s_score;
1
2
3
4
5
6

用聚合函数和单行单列的子查询做比较

-- 查询没有学全所有课程的同学的信息
SELECT * 
FROM t_students
WHERE s_id NOT IN(
SELECT stu.s_id 
FROM t_students stu
JOIN t_scores sco ON stu.`s_id`=sco.`s_id`
GROUP BY stu.`s_id`
HAVING COUNT(DISTINCT sco.`c_id`)=(SELECT COUNT(c_id) FROM t_courses)
);
-- 注意，只有SELECT 后面跟单个聚合函数，才能比较，跟c_id那种属性名，或者多个聚合函数，是会报错的
1
2
3
4
5
6
7
8
9
10
11

JOIN多行多列的子查询

-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select stu_b.s_name 学生姓名,sco_b.s_score,ss.平均成绩 平均成绩
from t_students stu_b
left join t_scores sco_b on sco_b.s_id=stu_b.s_id
left join (select avg(sco.s_score) 平均成绩,stu.s_id student_id
      from t_students stu
             join t_scores sco on stu.s_id = sco.s_id
      group by stu.s_name)  ss on sco_b.s_id=ss.student_id
order by sco_b.s_score desc ;
1
2
3
4
5
6
7
8
9