DQL语言---MySQL学习

最新推荐文章于 2024-07-11 20:24:07 发布

梦·翔

最新推荐文章于 2024-07-11 20:24:07 发布

阅读量202

点赞数

文章标签： mysql 学习 sql

本文链接：https://blog.csdn.net/qq_53790682/article/details/125813306

版权

MySQL—DQL语言

DQL语言介绍

DQL全称Data Query Language
中文名称数据查询语言
就是select语句

一、单表查询

1. 单表查询语法基本格式

SELECT [DISTINCT] <column1 [as new name],column2,...>
FROM <table1_name>
[WHERE <search_condition]
[GROUP BY <gruop_by_expression>]
[HAVING <gruop_condition>]
[ORDER BY <column_list> [ASC|DESC]]

查看部门表的所有信息
```
SELECT * FROM dept;
```
查看部门表中部门号、部门名称的字段信息
```
SELECT deptid,deptname FROM dept;
```

面试考点：不建议使用 * ，因为会查出不必要的列，消耗系统时间，效率低。

使用指定列的好处，不仅可以提高查询效率，还可以改变列在查询结果中的显示顺序

2. 定义别名

表和字段都可以定义别名

定义别名使用as，当然也可以省略

// 不省略as
SELECT u.name FROM user as u;
// 省略as
SELECT u.name FROM user u;
// 给字段起别名
SELECT name 姓名 FROM user;

别名中有特殊字符（例如：空格，大小写）时必须加双引号，如下

// 别名中有空格
SELECT name "姓 名" FROM user;
// 别名中有大写字母
SELECT name "Name" FROm user;

3. 去掉重复行（DISTINCT）（针对查询结果）

DISTINCT的作用是去除重复行。

DISTINCT的作用范围是==后面所有字段的组合==

例：

// 查询所有学生的姓名，且name不重复
SELECT DISTINCT name FROM t_student;
// 查询所有学生的姓名和生日，且姓名和生日都不重复
SELECT DISTINCT name,birthday FROM t_student;

4. WHERE子句

  1. 在SELECT语句中可以使用WHERE子句实现对数据行的筛选
  2. **在WHERE子句中不可以使用列的别名**

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xliaKtDH-1657894581478)(C:\Users\24538\AppData\Roaming\Typora\typora-user-images\image-20220713084744396.png)]

使用算术运算符比较筛选

// 查询id=1的用户
SELECT * FROM user WHERE id=1;
// 查询id大于等于1的用户
SELECT * FROM user WHERE id>=1;
// 查询id不等于1的用户，<>的作用与!=一样，都是不等于的意思
SELECT * FROM user WHERE id<>1;

使用逻辑运算符（AND和OR）进行筛选

// 查询id=1且姓名是 鲲坤 的用户
SELECT * FROM user WHERE id=1 AND name="鲲坤";
// 查询id=1或者姓名是 鲲坤 的用户
SELECT * FROM user WHERE id=1 OR name="鲲坤";

带BETWEEN…AND…的范围查询

// 查询工资在1000到2000的员工
SELECT name FROM emp WHERE salary BETWEEN 1000 AND 2000;
// 查询公仔不在1000到2000之间的员工
SELECT name FROM emp WHERE salary NOT BETWEEN 1000 AND 2000;

带IN关键字的查询

// 查询10，20，30部门的员工的工资
SELECT salary FROM emp WHERE deptid IN (10,20,30);
// 查询不在10，20，30部门的员工的工资
SELECT salary FROM emp WHERE deptid NOT IN (10,20,30);

带LIKE的字符匹配查询（常用于模糊查询）

// 查询名字中带有 鲲 字的员工   因为name在mysql中是关键字所以加了``
SELECT `name` FROM emp WHERE `name` LIKE '%鲲%';
// 查询名字中带有_下划线的员工的姓名  ESCAPE的作用就是指定一个字符代替转义字符‘\’
SELECT `name` FROM emp WHERE `name` LIKE '%\_%' ESCAPE '\\';
// 查询姓刘的员工
SELECT `name` FROM emp WHERE `name` LIKE '%刘_%';

查询空值（IS NULL)

// 查询没有奖金的员工姓名
SELECT `name` FROM emp WHERE bonus IS NULL OR bonus=0;
// 查询有奖金的员工姓名
SELECT `name` FROM emp WHERE bonus IS NOT NULL OR bonus<>0;

排序ORDER BY

// 查询用户信息，并按用户id升序排列, **MySQL中默认为升序排列，ASC可以省略**
SELECT username,upwd FROM user ORDER BY id ASC;

// 查询用户信息，并按用户id降序排列  **ORDER BY 子句中可以使用列别名，而WHERE子句中不可以使用**
SELECT userid uid,username,upwd FROM user ORDER BY uid DESC;

// 查询出员工的姓名，职位，入职时间和部门号，按着入职时间升序排序和部门号排序降序排序。
// **DESC的作用域是离它最近的字段**
SELECT ename,job,hiredate,deptno FROM emp ORDER BY hiredate,deptno DESC;

分组GRUOP BY子句和分组聚合函数
```
// 查询各个部门的员工的平均工资
SELECT AVG(salary) FROM emp GRUOP BY deptno;
// 查询各个部门各个职位的平均工资和最大工资。并按照平均工资升序排序。
SELECT AVG(salary),MAX(salary) FROM emp
GROUP BY deptno,job
ORDER BY AVG(salary) ASC;
// 统计10，20, 30 号部门的员工人数
SELECT deptno,COUNT(empno)
FROM emp
WHERE empno IN (10,20,30)
GROUP BY deptno;
// 从员工表中统计职位的个数
SELECT COUNT(DISTINCT job) emp;
// 统计员工表中有奖金（包括奖金不能为0）的员工人数
SELECT COUNT(*) FROM emp WHERE bonus IS NOT NULL AND bonus <> 0;
-- 或者
SELECT COUNT(bonus) FROM emp WHERE bonus != 0;
```
注意
1. COUNT() 与*COUNT(列名)*的区别： COUNT()将返回表格中所有存在的行的总数包括值为NULL的
  
  行，然而COUNT(列名)将返回表格中除去NULL以外的所有行的总数(有默认值的列也会被计入），
  
  COUNT(DISTINCT 列名),得到的结果将是除去值为NULL和重复数据后的结果。
2. 分组函数用于统计表数据。
  
  MAX( ) 求最大值，返回表达式中所有值的最大值。适用任何数据类型 MIN( ) 求最小值，返回表达式中所有值
  
  的最小值。适用任何数据类型 AVG( ) 求平均值，返回表达式中所有值的平均值，只能用于数字 SUM( ) 求和，
  
  返回表达式中所有值的和，只能用于数字求和 COUNT( ) 统计计算总行数，返回整数。

HAVING子句

对分组后的结果进行过滤筛选

// 列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数
SELECT job,MIN(sal),COUNT(empno)
FROM emp
GROUP BY job
HAVING MIN(sal) > 1500;

LIMIT关键字的使用及分页查询

LIMIT后面的数字代表限制的条数，若条数小于总记录数，则显示限制的数量，若大于总记录数，则显示全部数据。

LIMIT接受一个或两个数字参数。参数必须是一个整数常量。

如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始行记录的偏移量是0（而不是1）。

LIMIT后面跟一个参数，表示的是从0开始查多少数据，例如：LIMIT 3，就是从0开始查询三位数据
```
// 查询用户表前三名的信息
SELECT * FROM user LIMIT 3;
-- 或者
SELECT * FROM user LIMIT 0,3;
// 查询用户表第三页的用户信息（假设每页显示3条，总共有10条数据）
SELECT * FROM user LIMIT 6,3;
```
注意：

MySQL的分页语句中 LIMIT后面的第一个参数是 (page - 1) * pageSize,其中page表示页码，pageSize表示每一页显示的最大记录数。

二、多表连接查询

1. 什么是多表连接查询？

多表查询：在查询时，需要涉及两个以上表的查询

2. 为什么使用多表查询？

业务需求：在实际的业务中，由于数据库的设计规范，需要查询的信息往往在多张表中

3. 如何建立多表连接查询？

通过主键-外键连接
通过相同字段连接

注意：

主键与外键名称不是必须相同，习惯相同而已

外键的取值不能为空

4. 等值连接

多张表的连接值相等

WHERE子句中使用等号连接相同的两个字段，例：表1.字段 = 表2.字段

连接条件的个数有N-1个（N表示数据表的个数）

面试要点：等值连接的连接条件

--任务1：所有员工信息，以及他们所在部门的基本信息 
SELECT e.*,d.dname,d.loc FROM emp e,dept d WHERE e.deptno = d.deptno; 
--任务2：查询10号部门的所有员工信息，显示员工姓名，部门编号，部门名称。 
SELECT e.ename,d.deptno,d.dname FROM emp e,dept d WHERE e.deptno = d.deptno AND d.deptno = 10; 
--任务3 查询20号部门的员工的员工姓名，职位，平均工资，所在部门名称。 
SELECT e.ename,e.job,AVG(e.sal) avgsal,d.dname 
FROM emp e,dept d
WHERE e.deptno=d.deptno AND e.deptno=20;

5. 不等值连接

多张表的连接值不相等

WHERE子句条件中写的多表连接的条件中要使用除了等号以外的运算符连接多张表的连接值。

在连接条件中可以使用的运算符有：>,<,>=,<=,<>,BETWEEN…AND

-- 查询出员工的姓名、薪水、及薪水等级
SELECT e.name,e.sal,sg.grade
FROM emp e, salgrade sg
WHERE e.sal BETWEEN sg.losal AND sg.hisal;
-- 查询10好部门工资小于2000的员工的姓名，工资及工资等级
SELECT e.name,e.deptno,e.sal,sg.grade
FROM emp e,salgrade sg
WHERE e.sal BETWEEN sg.losal AND sg.hisal AND e.deptno AND e.sal<2000;
-- 查询工资等级处于第四级别的员工的姓名
SELECT e.name FROM emp e,salgrade sg 
WHERE e.sal BETWEEN sg.losal AND sg.hisal AND sg.grade=4;

6. 内连接

内连接是一种常用的多表关联查询方式，一般使用关键字INNER JOIN来实现
INNER关键字可以省略，当只使用JOIN关键字时，语句指标是内连接操作。
在使用内连接查询多个表时，必须在FROM子句之后定义一个ON子句，该子句用来指定两个表实现内连接的“连接条件”。

在使用内连接查询时，所有的记录行都是满足查询条件的（左外连接和右外连接并不一定都满足）。

内连接语法格式如下：

SELECT column_list(字段列表)

FROM table1 [INNER] JOIN table2

ON condition(条件)

面试要点：

注意内连接的语法格式
注意内连接的条件是写在on子句后面

-- 查询10号部门的所有员工的信息
SELECT * 
FROM emp e 
INNER JOIN dept d 
ON e.deptno=d.deptno AND d.deptno=10;
-- 查询在IT部门的员工的信息
SELECT * 
FROM emp e 
INNER JOIN dept d 
ON e.deptno=d.deptno 
WHERE d.dname='IT'

7. 自连接

即自己和自己连接，把一张表当成两张表用。需要给两张表分别取别名。
语法：SELECT select_list FROM table_name t1, table_name t2 WHERE search_condition;

面试要点：用户可能会拥有“自引用式”的外键。

比如：emp表中某一行的mgr列值（管理者列）可能是另一行的empno列值（员工列），因为管理者本身也是公司的员工
```
-- 显示BLAKE的上级领导的姓名
SELECT m.ename
FROM emp e, emp m
WHERE e.mgr = m.empno AND e.ename='BLAKE';
-- 或者
SELECT m.ename
FROM emp e INNER JOIN emp m
ON e.mgr = m.empno
WHERE e.ename='BLANK';
```

8.外连接

外连接：会返回所有的匹配行和一些或全部不匹配的行，这取决于所建立的外连接的类型。
MySQL支持：左外连接LEFT [OUTER] JOIN…ON；右外连接 RIGHT [OUTER] JOIN…ON
OUTER可以省略

面试要点：根据关联的表匹配返回的行记录与所想要查询的记录判断清楚到底需要用左外连接还是右外连接

-- 统计各个部门的员工人数
-- 左外连接
SELECT  d.deptno,COUNT(e.empno)
FROM dept d LEFT [OUTER] JOIN emp e 
ON d.deptno = e.deptno
GROUP BY d.deptno;

-- 右外连接
SELECT d.deptno,COUNT(e.empno)
FROM emp e RIGHT [OUTER] JOIN dept d
ON d.deptno = e.deptno
GROUP BY d.deptno;