帮你们回顾一下SQL语句

前言：有一段时间没接触SQL语句了，最近准备工作了，所以复习一下常用的SQL语句

我的公众号：不颓废的程序员

一、创建数据库

create database test;		-- 创建一个名为test的数据库
drop database test;			-- 删除test数据库
use test;					-- 使用test数据库
show databases;        		-- 显示数据库列表
show tables;                -- 显示test中所有表的名字
describe 表名;			    -- 显示数据表的结构

二、创建数据库表

• CREATE TABLE：创建表；
• NULL： 数据列可包含NULL值；
• NOT NULL： 数据列不可包含NULL值；
• AUTO_INCREMENT：自动递增，适用于整型；
• COMMENT：表和字段的说明；
• DEFAULT：默认值；
• PRIMARY KEY：主键；
• ENGINE：引擎；
• CHARSET：字符集；

-- 用户表
CREATE TABLE `t_user`(
    `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
    `name` varchar(10) NOT NULL DEFAULT '' COMMENT '用户名',
    `phone` varchar(11) NOT NULL DEFAULT '' COMMENT '电话号码',
    `password` varchar(32) NOT NULL DEFAULT '' COMMENT '用户密码',
    -- 设置主键
    PRIMARY KEY (`id`),
    -- 创建唯一索引，不允许重复
  	UNIQUE INDEX idx_user_phone(`phone`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表信息';

-- 成绩表
CREATE TABLE `t_score`(
 	`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '成绩ID',
 	`uid` int(11) NOT NULL COMMENT '用户ID',    -- uid对应t_user的id
 	`subject` varchar(11) NOT NULL DEFAULT '' COMMENT '科目',
 	`score` int(11) NOT NULL DEFAULT 0 COMMENT '成绩',
 	-- 主键
    PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='成绩表信息';

三、表数据的增删查改

主要是对上面创建的 t_user 表和 t_score 表进行操作

SELECT：查询数据

• SELECT * FROM 表名;
• SELECT 字段名 FROM 表名;

1、AND 和 OR 可在 WHERE 子语句中把两个或多个条件结合起来

-- 使用AND，查询出name为'hgq'并且phone为'10086'的用户
SELECT * FROM t_user WHERE name = 'hgq' AND phone = '10086';
-- 使用OR，查询出name为'hgq'或者phone为'10086'的用户
SELECT * FROM t_user WHERE name = 'hgq' OR phone = '10086';

2、ORDER BY 和 ASC 和 DESC

-- 按照成绩降序排序
SELECT * FROM t_score ORDER BY score DESC;
-- 按照成绩升序排序，默认是升序排序
SELECT * FROM t_score ORDER BY score ASC;

3、GROUP BY 用于根据一个或多个列对结果集进行分组,合计函数 (比如 SUM) 常常需要添加 GROUP BY 语句

-- 比如按照用户的name进行分组查询，查询name和每位用户的总成绩
SELECT u.name,SUM(s.score) FROM t_user AS u LEFT JOIN t_score AS s ON u.id = s.uid GROUP BY u.name;

4、HAVING 一般用在 GROUP BY 的后面，由于 WHERE 关键字无法与合计函数(比如 SUM)一起使用，所以需要使用HAVING

-- 比如查询总成绩超过250分的用户
SELECT u.name,SUM(s.score) FROM t_user AS u LEFT JOIN t_score AS s ON u.id = s.uid GROUP BY u.name HAVING SUM(s.score) > 250;

5、IN 操作符允许我们在 WHERE 子句中规定多个值

-- 比如查询t_user表中name为hgq和fxx的用户
SELECT * FROM t_user WHERE name IN ('hgq','fxx');

6、WHERE 子句中的 NOT 操作符有且只有一个功能：否定它之后所跟的任何条件。可以使用 NOT 操作符对 IN、BETWEEN、EXISTS子句进行取反

-- 比如查询t_user表中name不为hgq和fxx的其他用户
SELECT * FROM t_user WHERE name NOT IN ('hgq','fxx');

7、AS就是起别名的意思，可以给表起别名，也可以给列起别名

-- 比如:SUM(s.score)起了一个别名：sum_score，给t_user起了一个别名：u，给t_score起了一个别名：s
SELECT u.name,SUM(s.score) AS sum_score FROM t_user AS u LEFT JOIN t_score AS s ON u.id = s.uid GROUP BY u.name;

8、操作符BETWEEN … AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期

-- 比如查询总成绩在250至280的用户
SELECT u.name,SUM(s.score) FROM t_user AS u LEFT JOIN t_score AS s ON u.id = s.uid GROUP BY u.name HAVING SUM(s.score) BETWEEN 250 AND 280;

9、UNION 和 UNION ALL

-- UNION：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序
-- UNION All：对两个结果集进行并集操作，包括重复行，不进行排序
-- 举个简单的例子：创建两张表：Table1和Table2,这两张表的结构是一样的，只是插入的数据不完全相同
Insert into Table1 values (1,'A');
Insert into Table1 values (2,'B');
Insert into Table2 values (1,'B');
Insert into Table2 values (2,'C');
Insert into Table2 values (3,'D');

如果执行SELECT * FROM Table1 UNION SELECT * FROM Table2;将会查询出A,B,B,C,D
如果执行SELECT * FROM Table1 UNION ALL SELECT * FROM Table2;将会查询出A,B,C,D

10、LIKE 常用于模糊查询

SELECT * FROM t_user WHERE name LIKE 'h%';  -- 查询name有‘h’开头的用户
SELECT * FROM t_user WHERE name LIKE '%h';  -- 查询name有‘h’结尾的用户
SELECT * FROM t_user WHERE name LIKE '%h%'; -- 查询name中有‘h’出现的用户

11、DISTINCT 是用于返回唯一不同的值，也就是查询的值如果有重复，则只会返回一个

SELECT DISTINCT subject FROM t_score;    -- 查询所有的科目，如果不加DISTINCT，则可能会有重复值

12、LIMIT 是用于限制查询的条数

-- 用法：【select * from tableName limit i,n 】
-- 参数：tableName : 为数据表；i : 为查询结果的索引值（默认从0开始）；n : 为查询结果返回的数量
SELECT * FROM t_score ORDER BY score DESC LIMIT 0,3;	-- 从第0条开始，查询3条数据

13、CASE WHEN条件表达式函数：类似JAVA中的IF ELSE语句

CASE WHEN SCORE = 'A' THEN '优'
     WHEN SCORE = 'B' THEN '良'
     WHEN SCORE = 'C' THEN '中' ELSE '不及格' END

14、注意点：

1.having只能用在group by之后，对分组后的结果进行筛选(也就是使用having的前提条件是分组)。
2.where肯定在group by 之前。
3.where后的条件表达式里不允许使用聚合函数，而having可以。
4.当一个查询语句同时出现了where,group by,having,order by的时候，执行顺序和编写顺序是：
(1) 执行where xx对全表数据做筛选，返回第1个结果集。
(2) 针对第1个结果集使用group by分组，返回第2个结果集。
(3) 针对第2个结果集中的每1组数据执行select xx，有几组就执行几次，返回第3个结果集。
(4) 针对第3个结集执行having xx进行筛选，返回第4个结果集。
(5) 针对第4个结果集，使用order by进行排序，然后返回结果。

DELETE

• 删除某个表的所有行：DELETE FROM 表名称; 或者 DELETE * FROM 表名称;
• 按条件删除表的行：DELETE FROM 表名称 WHERE 列名称 = 值;

DELETE FROM t_user WHERE name = 'hgq';

UPDATE

• UPDATE 语句用于修改表中的数据
• UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值;

UPDATE t_user SET password = '123' WHERE name = 'fxx';

INSERT

• INSERT INTO 语句用于向表格中插入新的行
• INSERT INTO 表名称 VALUES (值1, 值2,…); 或者 INSERT INTO 表名称(列名,列名,…) VALUES (值1, 值2,…);

INSERT INTO t_user VALUES(8,'hkz','10086111','123456');
INSERT INTO t_user(id,name,phone,password) VALUES(8,'hkz','10086111','123456');

四、SQL函数

COUNT

-- 返回指定列的值的数目（NULL不计入）
SELECT COUNT(*) FROM 表名;
SELECT COUNT(列名) FROM 表名;

SUM

-- 返回数值列的总数
SELECT SUM(列名) FROM 表名;

MIN 和 MAX

-- 返回一列中的最小值，但NULL值不包括在计算中
SELECT MIN(列名) FROM 表名;
-- 返回一列中的最大值，但NULL值不包括在计算中
SELECT MAX(列名) FROM 表名;

AVG

-- 返回数值列的平均值，但NULL值不包括在计算中
SELECT AVG(列名) FROM 表名;

五、补充点：left join 和 right join 和 inner join 和 full join

left join(左连接) ：返回包括左表中的所有记录和右表中联结字段相等的记录

right join(右连接) ：返回包括右表中的所有记录和左表中联结字段相等的记录

inner join(等值连接，内连接) ：只返回两个表中联结字段相等的行

full join(全连接)：在两张表进行连接查询时，返回左表和右表中所有 没有匹配的行

left join

在这里主要深入了解一下最常见的 left join 吧，， 我们印象最深的就是左表的数据会全部显示，右边的数据满足条件会显示 ！

先设计两张简单的表（一般的数据库不会设计成这样，我这样设计是为了读者能更加方便地理解）：

表A：					    		表B：
ID              NAME     |        ID              AGE
1               A        |        2               18
2               B		 |		  3				  19
3               C        |        4               20

执行 SELECT * FROM A LEFT JOIN B ON A.id = B.id;结果为

A.ID		A.NAME		B.ID		B.AGE
1			A			NULL		NULL
2			B			2			18	
3			C			3			19

原因是： 查询结果是以左表A为基础，ON的条件是用来筛选右表B的

执行 SELECT * FROM A LEFT JOIN B ON A.ID = B.ID AND A.ID = 2;结果为

A.ID		A.NAME		B.ID		B.AGE
1			A			NULL		NULL
2			B			2			18
3			C			NULL		NULL

原因是：当没有WHERE条件的时候，左表的数据可以全部显示， 而ON的条件是来筛选B表数据的 ， ON后面的条件中有一个A.ID=2,第三条数据A.ID=3，不符合条件，所以第三条数据中B部分为NULL

再进行一次测试：

执行 SELECT * FROM A LEFT JOIN B ON A.ID = B.ID WHERE A.ID = 2;

结果变为下面这样了：

A.ID		A.NAME		B.ID		B.AGE
2			B			2			18

原因：出现了WHERE条件，而WHERE条件是可以影响左右表的，所以就只查询出符合 WHERE A.ID = 2 的数据；而ON的条件只能影响右表的数据，也就是用来筛选右表的数据，左表的数据是能全部查询出来的

right join

SELECT * FROM A RIGTH JOIN B ON A.ID = B.ID;

A.ID		A.NAME		B.ID		B.AGE
2			B			2		    18
3			C			3			19
NULL		NULL		4		    20

inner join

SELECT * FROM A INNER JOIN B ON A.ID = B.ID;

A.ID		A.NAME		B.ID		B.AGE
2			B			2			18
3			C			3			19