7、数据查询

说听
62 阅读11分钟

7.1 数据库表的基本结构

关系结构数据库是以表格（Table）进行数据存储，表格由“行”和“列”组成

7.2 基本查询

语法：SELECT 列名 FROM 表名

关键字描述
SELECT指定要查询的列
FROM指定要查询的表
7.2.2 查询所有列
#查询员工表中所有员工的所有信息（所有列）
SELECT 所有列的列名 FROM t_employees;
SELECT * FROM t_employees;
7.2.1 查询部分列
#查询员工表中所有员工的编号、名字、邮箱
SELECT employee_id,first_name,email 
FROM t_employees;
7.2.3 对列中的数据进行运算
#查询员工表中所有员工的编号、名字、年薪
SELECT employee_id , first_name , salary*12 
FROM t_employees;
算数运算符描述
+两列做加法运算
-两列做减法运算
*两列做乘法运算
/两列做除法运算
7.2.4 列的别名

列 as '列名'

#查询员工表中所有员工的编号、名字、年薪（列名均为中文）
SELECT employee_id as "编号" , first_name as "名字" , salary*12 as "年薪" 
FROM t_employees;
7.2.5 查询结果去重

DISTINCT 列名

#查询员工表中所有经理的ID。
SELECT DISTINCT manager_id 
FROM t_employees;

7.3排序查询

语法： SELECT 列名 FROM 表名 ORDER BY 排序列 [排序规则]

排序规则描述
ASC对前面排序列做升序排序
DESC对前面排序列做降序排序
7.3.1 依据单列排序
#查询员工的编号，名字，薪资。按照工资高低进行降序排序。
SELECT employee_id , first_name , salary
FROM t_employees
ORDER BY salary DESC;
7.3.2 依据多列排序
#查询员工的编号，名字，薪资。按照工资高低进行升序排序（薪资相同时，按照编号进行升序排序）。
SELECT employee_id , first_name , salary
FROM t_employees
ORDER BY salary DESC , employee_id ASC;

7.4 条件查询

语法：SELECT 列名 FROM 表名 WHERE 条件

关键字描述
WHERE 条件在查询结果中，筛选符合条件的查询结果，条件为布尔表达式
7.4.1 等值判断（=）
#查询薪资是11000的员工信息（编号、名字、薪资）
SELECT employee_id , first_name , salary
FROM t_employees
WHERE salary = 11000;
7.4.2 逻辑判断（and、or、not）
#查询薪资是11000并且提成是0.30的员工信息（编号、名字、薪资）
SELECT employee_id , first_name , salary
FROM t_employees
WHERE salary = 11000 AND commission_pct = 0.30;
7.4.3 不等值判断（> 、< 、>= 、<= 、!= 、<>）
#查询员工的薪资在6000~10000之间的员工信息（编号，名字，薪资）
SELECT employee_id , first_name , salary
FROM t_employees
WHERE salary >= 6000 AND salary <= 10000;
7.4.4 区间判断（between and）
#查询员工的薪资在6000~10000之间的员工信息（编号，名字，薪资）
SELECT employee_id , first_name , salary
FROM t_employees
WHERE salary BETWEEN 6000 AND 10000; #闭区间，包含区间边界的两个值
7.4.5 NULL 值判断（IS NULL、IS NOT NULL）

  • IS NULL

    列名 IS NULL

  • IS NOT NULL

    列名 IS NOT NULL

#查询没有提成的员工信息（编号，名字，薪资 , 提成）
SELECT employee_id , first_name , salary , commission_pct
FROM t_employees
WHERE commission_pct IS NULL;
7.4.6 枚举查询（ IN (值 1，值 2，值 3 ) ）
#查询部门编号为70、80、90的员工信息（编号，名字，薪资 , 部门编号）
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE department_id IN(70,80,90);
注：in的查询效率较低，可通过多条件拼接。
7.4.7 模糊查询

  • LIKE _ （单个任意字符）

    列名 LIKE '张_'

  • LIKE %（任意长度的任意字符）

    列名 LIKE '张%'

#查询名字以"L"开头的员工信息（编号，名字，薪资 , 部门编号）
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE first_name LIKE 'L%';
​
​
#查询名字以"L"开头并且长度为4的员工信息（编号，名字，薪资 , 部门编号）
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE first_name LIKE 'L___';
7.4.8 分支结构查询
CASE
    WHEN 条件1 THEN 结果1
    WHEN 条件2 THEN 结果2
    WHEN 条件3 THEN 结果3
    ELSE 结果
END

#查询员工信息（编号，名字，薪资 , 薪资级别<对应条件表达式生成>）
SELECT employee_id , first_name , salary , department_id , 
       CASE
           WHEN salary>=10000 THEN 'A'
           WHEN salary>=8000 AND salary<10000 THEN 'B'
           WHEN salary>=6000 AND salary<8000  THEN 'C'
           WHEN salary>=4000 AND salary<6000  THEN 'D'
   ELSE 'E'
       END as "LEVEL"
FROM t_employees;

7.5 时间查询

语法：SELECT 时间函数([参数列表])

时间函数描述
SYSDATE()当前系统时间（日、月、年、时、分、秒）
CURDATE()获取当前日期
CURTIME()获取当前时间
WEEK(DATE)获取指定日期为一年中的第几周
YEAR(DATE)获取指定日期的年份
HOUR(TIME)获取指定时间的小时值
MINUTE(TIME)获取时间的分钟值
DATEDIFF(DATE1,DATE2)获取DATE1 和 DATE2 之间相隔的天数
ADDDATE(DATE,N)计算DATE 加上 N 天后的日期
7.5.1 获得当前系统时间
#查询当前时间
SELECT SYSDATE();

#查询当前时间
SELECT NOW();

#获取当前日期
SELECT CURDATE();

#获取当前时间
SELECT CURTIME();

7.6 字符串查询

语法： SELECT 字符串函数 ([参数列表])

字符串函数说明
CONCAT(str1,str2,str....)将多个字符串连接
INSERT(str,pos,len,newStr)将str 中指定 pos 位置开始 len 长度的内容替换为 newStr
LOWER(str)将指定字符串转换为小写
UPPER(str)将指定字符串转换为大写
SUBSTRING(str,num,len)将str 字符串指定num位置开始截取 len 个内容
7.6.1 字符串应用
#拼接内容
SELECT CONCAT('My','S','QL');

#字符串替换
SELECT INSERT('这是一个数据库',3,2,'MySql');#结果为这是 MySql 数据库

#指定内容转换为小写
SELECT LOWER('MYSQL');#mysql

#指定内容转换为大写
SELECT UPPER('mysql');#MYSQL

#指定内容截取
SELECT SUBSTRING('JavaMySQLOracle',5,5);#MySQL

7.7 聚合函数

语法：SELECT 聚合函数(列名) FROM 表名;

聚合函数说明
SUM()求所有行中单列结果的总和
AVG()平均值
MAX()最大值
MIN()最小值
COUNT()求总行数
7.7.1 单列总和
#统计所有员工每月的工资总和
SELECT sum(salary)
FROM t_employees;
7.7.2 单列平均值
#统计所有员工每月的平均工资
SELECT AVG(salary)
FROM t_employees;
7.7.3 单列最大值
#统计所有员工中月薪最高的工资
SELECT MAX(salary)
FROM t_employees;
7.7.4 单列最小值
#统计所有员工中月薪最低的工资
SELECT MIN(salary)
FROM t_employees;
7.7.5 总行数
#统计员工总数
SELECT COUNT(*)
FROM t_employees;

#统计有提成的员工人数
SELECT COUNT(commission_pct) 
FROM t_employees;

7.8 分组查询

语法：SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组依据（列）;

关键字说明
GROUP BY分组依据，必须在 WHERE 之后生效
7.8.1 查询各部门的总人数
#思路：
#1.按照部门编号进行分组（分组依据是 department_id）
#2.再针对各部门的人数进行统计（count）
SELECT department_id,COUNT(employee_id)
FROM t_employees
GROUP BY department_id;
7.8.2 查询各部门的平均工资
#思路：
#1.按照部门编号进行分组（分组依据department_id）。
#2.针对每个部门进行平均工资统计（avg）。
SELECT department_id , AVG(salary)
FROM t_employees
GROUP BY department_id
7.8.3 查询各个部门、各个岗位的人数
#思路：
#1.按照部门编号进行分组（分组依据 department_id）。
#2.按照岗位名称进行分组（分组依据 job_id）。
#3.针对每个部门中的各个岗位进行人数统计（count）。
SELECT department_id , job_id , COUNT(employee_id)
FROM t_employees
GROUP BY department_id , job_id;
7.8.4 常见问题
#查询各个部门id、总人数、first_name
SELECT department_id , COUNT(*) , first_name
FROM t_employees
GROUP BY department_id; #error

7.9 分组过滤查询

语法：SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组列 HAVING 过滤规则

关键字说明
HAVING 过滤规则过滤规则定义对分组后的数据进行过滤
7.9.1 统计部门的最高工资
#统计60、70、90号部门的最高工资
#思路：
#1).    确定分组依据（department_id）
#2).    对分组后的数据，过滤出部门编号是60、70、90信息
#3).    max()函数处理
​
SELECT department_id , MAX(salary)
FROM t_employees
GROUP BY department_id
HAVING department_id in (60,70,90)
​
# group确定分组依据department_id 
#having过滤出60 70 90部门
#select查看部门编号和max函数。

7.10 限定查询

SELECT 列名 FROM 表名 LIMIT 起始行，查询行数

关键字说明
LIMIT offset_start，row_count限定查询结果的起始行和总行数
7.10.1 查询前 5 行记录
#查询表中前五名员工的所有信息
SELECT * FROM t_employees LIMIT 0,5;
7.10.2 查询范围记录
#查询表中从第四条开始，查询 10 行
SELECT * FROM t_employees LIMIT 3,10;
7.10.3 LIMIT典型应用

分页查询：一页显示 10 条，一共查询三页

#思路：第一页是从 0开始，显示 10 条
SELECT * FROM LIMIT 0,10;
​
#第二页是从第 10 条开始，显示 10 条
SELECT * FROM LIMIT 10,10;
​
#第三页是从 20 条开始，显示 10 条
SELECT * FROM LIMIT 20,10;

7.11 查询总结

7.11.1 SQL 语句编写顺序

SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组 HAVING 过滤条件 ORDER BY 排序列（asc|desc）LIMIT 起始行，总条数

7.11.2 SQL 语句执行顺序
1.FROM ：指定数据来源表
2.WHERE : 对查询数据做第一次过滤
3.GROUP BY ： 分组
4.HAVING : 对分组后的数据第二次过滤
5.SELECT : 查询各字段的值
6.ORDER BY : 排序
7.LIMIT : 限定查询结果

7.12 子查询（作为条件判断）

SELECT 列名 FROM 表名 Where 条件 (子查询结果)

7.12.1 查询工资大于Bruce 的员工信息
#1.先查询到 Bruce 的工资（一行一列）
SELECT SALARY FROM t_employees WHERE FIRST_NAME = 'Bruce';#工资是 6000
​
#2.查询工资大于 Bruce 的员工信息
SELECT * FROM t_employees WHERE SALARY > 6000;
​
#3.将 1、2 两条语句整合
SELECT * FROM t_employees WHERE SALARY > （SELECT SALARY FROM t_employees WHERE FIRST_NAME = 'Bruce' ）;

7.13 子查询（作为枚举查询条件）

SELECT 列名 FROM 表名 Where 列名 in(子查询结果);

7.13.1 查询与名为'King'同一部门的员工信息
#思路：
#1. 先查询 'King' 所在的部门编号(多行单列)
SELECT department_id
FROM t_employees
WHERE last_name = 'King'; //部门编号：80、90
​
#2. 再查询80、90号部门的员工信息
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE department_id in (80,90); 
​
#3.SQL：合并
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE department_id in (SELECT department_id cfrom t_employees WHERE last_name = 'King'); #N行一列
​
7.13.2 工资高于60部门所有人的信息
#1.查询 60 部门所有人的工资（多行单列）
SELECT SALARY from t_employees WHERE DEPARTMENT_ID=60;
​
#2.查询高于 60 部门所有人的工资的员工信息（高于所有）
select * from t_employees where SALARY > ALL(select  SALARY from t_employees WHERE DEPARTMENT_ID=60);
​
#。查询高于 60 部门的工资的员工信息（高于部分）
select * from t_employees where SALARY > ANY(select  SALARY from t_employees WHERE DEPARTMENT_ID=60);
​

7.14 子查询（作为一张表）

SELECT 列名 FROM（子查询的结果集）WHERE 条件;

7.14.1 查询员工表中工资排名前 5 名的员工信息
#思路：
#1. 先对所有员工的薪资进行排序（排序后的临时表）
select employee_id , first_name , salary
from t_employees
order by salary desc
​
#2. 再查询临时表中前5行员工信息
select employee_id , first_name , salary
from (临时表) 
limit 0,5;
​
#SQL：合并
select employee_id , first_name , salary
from (select employee_id , first_name , salary from t_employees order by salary desc) as temp
limit 0,5;

7.15 合并查询（了解）

  • SELECT * FROM 表名1 UNION SELECT * FROM 表名2
  • SELECT * FROM 表名1 UNION ALL SELECT * FROM 表名2
7.15.1 合并两张表的结果（去除重复记录）
#合并两张表的结果，去除重复记录
SELECT * FROM t1 UNION SELECT * FROM t2;
7.15.2 合并两张表的结果（保留重复记录）
#合并两张表的结果，不去除重复记录（显示所有）
SELECT * FROM t1 UNION ALL SELECT * FROM t2;

7.16 表连接查询

SELECT 列名 FROM 表1 连接方式 表2 ON 连接条件

7.16.1 内连接查询（INNER JOIN ON）
#1.查询所有有部门的员工信息（不包括没有部门的员工） SQL 标准
SELECT * from t_employees e INNER JOIN t_departments d ON e.DEPARTMENT_ID=d.DEPARTMENT_ID;
​
#2.查询所有有部门的员工信息（不包括没有部门的员工） MYSQL
SELECT * from t_employees e,t_departments d where e.DEPARTMENT_ID=d.DEPARTMENT_ID;
7.16.2 三表连接查询
#查询所有员工工号、名字、部门名称、部门所在国家ID
SELECT * FROM t_employees e 
INNER JOIN t_departments d 
on e.department_id = d.department_id
INNER JOIN t_locations l
ON d.location_id = l.location_id
7.16.3 左外连接（LEFT JOIN ON）
#查询所有员工信息，以及所对应的部门名称（没有部门的员工，也在查询结果中,部门名称以NULL 填充）
SELECT e.employee_id , e.first_name , e.salary , d.department_name FROM t_employees e
LEFT JOIN t_departments d 
ON e.department_id = d.department_id;
7.16.4 右外连接（RIGHT JOIN ON）
#查询所有部门信息，以及此部门中的所有员工信息（没有员工的部门，也在查询结果中，员工信息以NULL 填充）
SELECT e.employee_id , e.first_name , e.salary , d.department_name FROM t_employees e 
RIGHT JOIN t_departments d 
ON e.department_id = d.department_id;
avatar
文章
阅读
粉丝