前言:有一段时间没接触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