数据库总结-05 Mysql数据库 单表的增删改查sql

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字段的聚合函数条件（按组查询）；

6. 下一节我们将学习多表联查sql

数据库总结-06 Mysql数据库 多表联查