1. 建表
为了方便接下来的学习,我们依旧还是先建一个表。
USE test2;
DROP TABLE IF EXISTS test02;
CREATE TABLE test02(
id INT PRIMARY KEY AUTO_INCREMENT,
username CHAR(8) COMMENT '用户名',
`password` CHAR(8) COMMENT '用户密码'
)ENGINE=INNODB CHARACTER SET=UTF8;
2. 插入语句
2.1 不指定字段名
INSERT test02 VALUE(1,'小一','111111');
INSERT test02 VALUES(2,'小二','222222'),(3,'小三','333333');
2. 2 指定字段名
INSERT test02(id,username,password) VALUE(4,'小四','444444');
2.3 INSERT SET
INSERT test02 SET id=5,username='小五',password='555555';
2.4 INSERT SELECT
INSERT test02(username,password) SELECT username,password FROM test02;
3. 修改语句
3.1 DELETE
-- DELETE
DELETE FROM test02 WHERE username='小二';
3.2 清空数据表,并将AUTO_INCREMENT重置为1
-- 清空数据表,并将AUTO_INCREMENT重置为1
DELETE FROM test02;
ALTER TABLE test02 AUTO_INCREMENT=1;
3.3 彻底清空数据表
-- 彻底清空数据表
TRUNCATE TABLE test02;
4. 查询语句
4.1 基本查询语句
-- 查询所有
SELECT * FROM test02;
SELECT id,username,password FROM test02;
-- 不打开数据库查询
SELECT * FROM test2.test02;
4.2 查询并取别名
SELECT id AS '编号',username AS '用户名',password AS '密码' FROM test02;
4.3 条件查询
4.3.1 =,<,>,<=,>=
SELECT * FROM test02 WHERE id=3;
SELECT * FROM test02 WHERE id>=3;
INSERT test02 VALUE(6,NULL,null);
4.4 查询NULL
SELECT * FROM test02 WHERE username=NULL;
我们发现什么也查不到,这是怎么回事呢?这就是NULL值查询需要特殊查询语句
4.4.1 <=>
SELECT * FROM test02 WHERE username<=>NULL;
4.4.2 IS NULL AND IS NOT NULL
SELECT * FROM test02 WHERE username IS NULL;
SELECT * FROM test02 WHERE username IS NOT NULL;
4.5 范围查询(BETWEEN AND)
-- 范围查询BETWEEN AND(包含边界值)
SELECT * FROM test02 WHERE id BETWEEN 2 and 4;
4.6 集合查询(IN ,NOT IN)
SELECT * FROM test02 WHERE id IN(1,3,4);
SELECT * FROM test02 WHERE id NOT IN(1,3,4);
4.7 AND ,OR
SELECT * FROM test02 WHERE id=2 AND username='小二';
SELECT * FROM test02 WHERE id=2 OR id=3;
4.8 模糊查询(like)
like,%代表0-n个,_代表一个 注意%忽略大小写
-- 姓小的用户
SELECT * FROM test02 WHERE username LIKE '小%';
-- 以二结尾的用户
SELECT * FROM test02 WHERE username LIKE '%二';
-- 包含小的用户
SELECT * FROM test02 WHERE username LIKE '%小%';
-- 用户名有2位的用户
SELECT * FROM test02 WHERE username LIKE '__'; -- 两个_
4.9 分组查询
4.9.1 简单分组(GROUP BY)
SELECT id,username FROM test02 GROUP BY id;
4.9.2 分组,并展示组内有哪些人(GROUP_CONCAT())
-- 分组,并展示组内有哪些人
INSERT test02(username,password) VALUES('小二','222222'),('小三','333333');
INSERT test02(username,password) VALUES('小二','222222'),('小三','333333');
INSERT test02(username,password) VALUES('小二','222222'),('小三','333333');
SELECT GROUP_CONCAT(id),username FROM test02 GROUP BY username;
5. 聚合函数
5.1 COUNT(*/字段名) 不统计NULL也不排重
-- COUNT(*/字段名) 不统计NULL也不排重
SELECT COUNT(*) FROM test02;
SELECT COUNT(username) FROM test02;
-- 分组并统计每个组的总人数(GROUP BY)
SELECT COUNT(*) AS '总人数',GROUP_CONCAT(id),username FROM test02 GROUP BY username;
5.2 SUM()、AVG()、MAX()、MIN()
-- 分组并统计总人数和id的平均值、最大值、最小值、总和
SELECT
GROUP_CONCAT(id),
username,
COUNT(*) AS '总人数',
SUM(id) AS '总和',
AVG(id) AS '平均值',
MAX(id) AS '最大值',
MIN(id) AS '最小值'
FROM test02
GROUP BY username;
5.3 对分组进行二次筛选(HAVING)
-- 分组并统计总人数和id的平均值,并且只需要id平均值大于5的分组
SELECT
GROUP_CONCAT(id),
username,
COUNT(*) AS '总人数',
SUM(id) AS '总和',
AVG(id) AS '平均值'
FROM test02
GROUP BY username
HAVING AVG(id)>5;
5.4 排序(ORDER BY默认升序)
5.4.1 单字段排序
-- 单字段排序
SELECT * FROM test02 ORDER BY id DESC; -- 降序
SELECT * FROM test02 ORDER BY id ASC; -- 升序
5.4.2 多字段排序
-- 多字段排序(按照第一个字段排序后再排序)
SELECT * FROM test02 ORDER BY id DESC,username ASC;
5.4.3 随机排序
-- 随机排序
SELECT * FROM test02 ORDER BY RAND();
5.5 限制结果集(LIMIT 下标从0开始)
5.5.1 LIMIT n
-- 只要前几条
SELECT * FROM test02 LIMIT 3;
5.5.2 LIMIT m,n
-- 只要从n开始的前m条
SELECT * FROM test02 LIMIT 2,2;
5.6 注意
– 注意:WHERE、GROUP BY、HAVING、ORDER BY的顺序是WHERE、GROUP BY、HAVING、ORDER BY。这是为什呢?我们只需要大概了解下面的原因即可,以后讲解数据库原理时会为大家具体解释
– 1.having只能用在group by之后,对分组后的结果进行筛选(即使用having的前提条件是分组)。
– 2.where肯定在group by 之前,即也在having之前。
– 3.where后的条件表达式里不允许使用聚合函数,而having可以。
– 4.having后只能跟group by后边字段条件 或者 非group by字段的聚合函数条件(按组查询);