MySQL数据管理 - 查询语句

查询数据

数据库最常用的操作就是查询，也是数据操作的基础，进行重点学习介绍

参考数据表

1 查询指定列

查询所有列

SELECT * FROM users;

查询指定的列

SELECT username FROM users;

查询指定列，并制定别名

SELECT username AS name FROM users;

查询列中的不同的值

SELECT DISTINCT username AS name FROM users;

2 条件查询

使用WHERE进行条件查询

SELECT * FROM users WHERE id = 1;

逻辑 与 或

SELECT * FROM users WHERE id = 1 OR id = 3;

SELECT * FROM users WHERE username = 'zhangsan' AND password = '123456';

查询不为NULL的情况

SELECT * FROM users WHERE birthday is NOT NULL;

【注意】数据库中不等于不是!=而是<>

查询数值范围

SELECT * FROM users WHERE id BETWEEN 1 AND 5; 

3 合并查询

合并字符串

SELECT CONCAT(username, ' ', password) as userpasswd FROM users WHERE id BETWEEN 1 AND 5;

同样可以不同的运算符对不同列的结果进行操作

4 模糊查询

• % 代表任意数量的字符
• _代表单个任意字符

如果要对_进行查询，需要进行转义\_

查询姓zhang的人

SELECT * FROM users WHERE username LIKE 'zhang%';

5 聚合函数查询

查询所有数据的调试

SELECT COUNT(*) FROM users;

查询某一列有值的函数

SELECT COUNT(birthday) FROM users;

求平均值，最大值，最小值，求和，都是对查询的列的值进行运算

SELECT AVG(value_1) FROM users;

SELECT MAX(value_1) FROM users;

SELECT MIN(value_1) FROM users;

SELECT SUM(value_1) FROM users;

6 对值进行排序

升序

SELECT * FROM users ORDER BY id ASC;

降序

SELECT * FROM users ORDER BY id DESC;

多个排序标准，使用, 进行分割

SELECT * FROM users ORDER BY id ASC, phone_number DESC;

7 分组查询

按照列进行分组，使用聚合函数统计每个组中元素的个数

SELECT username, count(*), AVG(value_1) FROM users GROUP BY username;

分组查询一般是对列进行聚合

如果需要对多列进行聚合，此时需要使用合并查询，对不同列的数采取运算操作

SELECT username, AVG(value_1 + value_2)/2 FROM users GROUP BY id;

运行的逻辑，在行上进行相加，然后在列上进行求平均，聚合函数还是聚合的列，聚合函数和分组常常在一起使用

面试题：

数据表为
student
id name chinese math english
1 zhangsan 80 70 65
2 lisi 90 80 72
3 wangwu 98 72 12

请使用SQL查询平均成绩大于60的学生

【注意】对每个学生进行分组查询，使用HAVING进行过滤

SELECT name FROM student GROUP BY id HAVING AVG(chinese + math + english)/3 > 60;

8 分页查询

使用LIMIT指定开始的位置和数据的条数

SELECT * FROM users LIMIT 1, 5;

9 数据库关联查询

根据两个表和多个表之间的关系进行查询和展示数据，使用JOIN语句实现

数据库ER图（摆脱了传递函数依赖）

1 内连接 INNER JOIN

展示仓库和工人的关系，使用了工作关系联结

使用INNER JOIN

SELECT * FROM worker INNER JOIN work ON worker.worker_id = work.worker_id INNER JOIN repository ON repository.repo_id = work.repo_id ;

内连接：也使用等于 = 进行连接

SELECT * FROM worker, work, repository WHERE worker.worker_id = work.work_id AND repository.repo_id = work.repo_id ;

2 LEFT JOIN

左连接允许连接的过程中右表没有匹配的情况

SELECT * FROM worker LEFT JOIN work ON worker.worker_id = work.worker_id LEFT JOIN repository ON worl.repo_id = repository.repo_id;

3 右连接

类似左连接，右连接允许左边的数据为NULL

10 数据库子查询

子查询将一个查询的结果用作数据库语句的条件

将id为1-3的user名字都修改为zhangsan1

UPDATE users SET username='zhangsan1' WHERE id IN (SELECT id FROM users WHERE id BETWEEN 1 AND 3);

报错：SQL Error [1093] [HY000]: You can't specify target table 'users' for update in FROM clause

踩坑：不能先SELECT一个表然后再更新
参考：https://blog.csdn.net/qq_40036754/article/details/115174800

删除呢？

DELETE FROM users WHERE id IN (SELECT id FROM users WHERE id BETWEEN 1 AND 3);

同样报这个错误

【问题】查询已经管理仓库的工人id；需要注意查询的结果是一行还是多行

SELECT worker_name FROM worker WHERE worker.worker_id IN (SELECT worker_id FROM work);

---

参考

MYSQL基础 https://www.bilibili.com/video/BV15m421T7RQ