文章目录
一、简单查询
1、查询特定的列
示例:查询出所有员工的编号及其姓名
SELECT eid,ename FROM emp;
练习:查询出所有员工的姓名,性别,生日,工资
SELECT ename,sex,birthday,salary FROM emp;
2、查询所有的列(2种方法)
SELECT eid,ename,sex,birthday,salary,deptld FROM emp;
SELECT * FROM emp;
3、给列起别名(使用AS或是空格)
示例:查询出所有员工的编号和姓名,使用中文别名
SELECT eid AS 编号,ename AS 姓名 FROM emp;
练习:查询出所有员工的姓名,生日,工资,使用中文别名
SELECT ename AS 姓名,birthday AS 生日,salary AS 工资 FROM emp;
练习:查询出所有员工的姓名和工资,使用1个字母作为别名
SELECT ename e,salary s FROM emp;
AS关键字可以省略,保留空格即可起别名
4、显示不同的记录 (可以实现去重的功能)
示例:查询所有的员工的性别有哪些
SELECT DISTINCT sex FROM emp;
练习:查询出员工都分布在哪些部门
SELECT DISTINCT deptId FROM emp;
5、查询时执行计算
示例:计算2+547.8+361.5
SELECT 2+5*47.8+3*61.5;
练习:查询出所有员工的姓名及其年薪
SELECT ename,salary*12 FROM emp;
练习:假设每个员工的工资增长1000,年终奖20000,查询出所有员工的姓名及其年薪,使用汉字别名
SELECT ename 姓名,(salary+1000)*12 +20000 年薪FROM emp;
6、查询的结果集排序(默认按照升序排列)
示例:查询出所有部门的数据,结果集按照部门编号从小到大排列
SELECT * FROM dept ORDER BY did ASC; #ascendant 升序的
示例:查询出所有部门的数据,结果集按照部门编号从大到小排列
SELECT * FROM dept ORDER BY did DESC; #descendant 降序的
练习:查询所有的员工,结果集按照工资的降序排列
SELECT * FROM emp ORDER BY salary DESC;
练习:查询所有的员工,结果集按照年龄从大到小排列
SELECT * FROM emp ORDER BY birthday ASC;
练习:查询所有员工,结果集按照姓名的升序排列
SELECT * FROM emp ORDER BY ename ASC;
练习:查询所有员工,结果集按照工资的降序排序,如果工资相同按照年龄从大到小排列
SELECT * FROM emp ORDER BY salary DESC,birthday; #birthday后省略了ASC
练习:查询所有的员工,结果集中女员工显示在前,如果性别相同按照姓名升序排列
SELECT * FROM emp ORDER BY sex,ename;
不加排序规则,默认是按照升序排列 ASC
7、条件查询
示例:查询出编号为5的员工的所有列
SELECT * FROM emp WHERE eid=5;
练习:查询出姓名为king的员工所有列
SELECT * FROM emp WHERE ename='king';
练习:查询出20号部门下的员工有哪些
SELECT * FROM emp WHERE deptId=20;
练习:查询出所有的女员工有哪些
SELECT * FROM emp WHERE sex=0;
练习:查询出工资在5000以上的员工有哪些
SELECT *FROM emp WHERE salary>5000;
小于:<
大于等于: >=
小于等于:<=
等于:=
不等于:!=
练习:查询出不在20号部门下的员工有哪些
SELECT * FROM emp WHERE deptId!=20;
练习:查找出没有明确部门的员工有哪些
SELECT * FROM emp WHERE deptId IS NULL;
练习:查找有明确部门的员工有哪些
SELECT * FROM emp WHERE deptId IS NOT NULL;
练习:查询出7000以上的男员工有哪些
SELECT * FROM emp WHERE salary>7000 AND sex=1;
练习:查询出工资在7000~10000之间的员工有哪些
SELECT * FROM emp WHERE salary>=7000 AND salary<=10000;
SELECT * FROM emp WHERE salary BETWEEN 7000 AND 10000;
练习:查询出工资在7000以下和10000以上的员工有哪些
SELECT * FROM emp WHERE salary < 7000 OR salary>10000;
SELECT * FROM emp WHERE salary NOT BETWEEN 7000 AND 10000;
练习:查询出1993年出生的员工有哪些
SELECT * FROM emp WHERE birthday>='1993-1-1' AND birthday<'1993-12-31';
SELECT * FROM emp WHERE birthday BETWEEN '1993-1-1' AND '1993-12-31';
练习:查询出20号部门和30号部门的员工有哪些
SELECT * FROM emp WHERE deptId=20 OR deptId=30;
SELECT * FROM emp WHERE deptId IN(20,30);
练习:查询出不在20号部门接30号部门的员工有哪些
SELECT * FROM emp WHERE deptId NOT IN(20,30);
IS NULL / IS NOT NULL
AND / OR
BETWEENT … AND … / NOT BETWEENT … AND …
IN() / NOT IN() (使用IN或是NOT IN时 括号里的必须是确切值)
8、模糊条件查询
通常用于搜索
另外,like后是模糊值查询,不能跟确切值
练习:查询出姓名中含有o的员工有哪些(不用=号是因为=号是确切查询)
SELECT * FROM emp WHERE ename LIKE '%o%';
练习:查询出姓名中以o结尾的员工有哪些
SELECT * FROM emp WHERE ename LIKE '%o';
练习:查询出姓名中第2个字符是o的员工有哪些
SELECT * FROM emp WHERE ename LIKE '_o%';
ex:查询出工资在8000以上的男员工的姓名,生日,工资;结果集按照工资的降序排列,使用汉字别名。
SELECT ename 姓名,birthday 生日,salary 工资 FROM emp WHERE salary>8000 AND sex=1 ORDER BY salary DESC;
9、分页查询
查询的结果集这个有太多的数据,一次显示不完可以做成分页
需要有两个已知的条件:当前的页码、每页的数量
每页开始查询的值=(当前的页码-1)* 每页的数据量
SELECT * FROM emp LIMIT 每页开始查询的值,每页的数据量;
练习:假设每页显示5条数据,分别查询出前3页的数据
第一页:SELECT * FROM emp LIMIT 0,5;
第二页:SELECT * FROM emp LIMIT 5,5;
第三页:SELECT * FROM emp LIMIT 10,5;
注意事项:每页开始查询的值和每页的数据量必须是数值,不能加引号。
二、复杂查询
1、聚合查询/分组查询
示例:查询出所有员工的数量
SELECT COUNT(*) FROM emp;
练习:分别使用员工的编号列和所在部门编号列查询员工的数量
SELECT COUNT(eid) FROM emp; # 推荐写法
SELECT COUNT(deptId) FROM emp;
函数:是一个功能体,提供若干个数据,返回结果
数量 count() / 平均 avg() / 总和 sum() / 最大 max() / 最小 min()
🛎️注意事项
(1). sum和avg是忽略NULL值的,
(2). count用于统计总数时,不忽略NULL;
(3). count用于统计单个字段总数时,忽略NULL;
(4). count(1)用于统计表中第一个字段的总数,count(1)等同于count(),实际开发时count(1)速度远远快于count()
练习:查询出所有男员工的平均工资
SELECT AVG(salary) FROM emp WHERE sex=1;
练习:查询出30号部门员工的工资总和
SELECT SUM(salary) FROM emp WHERE deptId=30;
练习:查询出女员工的最高工资是多少
SELECT MAX(salary) FROM emp WHERE sex=0;
练习:查询出年龄最大的员工生日是多少
SELECT MIN(birthday) FROM emp;
分组查询只适合查询分组条件列和聚合函数
示例:查询出男女员工的数量,平均工资,最低工资
SELECT COUNT(eid),AVG(salary),MIN(salary),sex FROM emp GROUP BY sex;
练习:查询出每个部门的工资总和,最高工资,最低工资
SELECT deptId,SUM(salary),MAX(salary),MIN(salary) FROM emp GROUP BY deptId;
year() 获取日期中的年份
示例:获取’2020-8-5’中的年份
SELECT year('2020-8-5');
练习:查询出1993年出生的员工有哪些
SELECT * FROM emp WHERE year(birthday)=1993;
2、子查询
多个sql命令的组合,把一个的结果作为另一个的条件使用
🔖 释义:
- 子查询就是查询语句的嵌套,即在外部查询中还包含一个内部查询。当一个查询是另一个查询的条件时,称之为子查询。
- 子查询可以使用几个简单命令构造功能强大的复合命令,子查询最常用于SELECT语句的where子句中。另外,子查询可作为一个临时表来使用,完成更为复杂表联结数据的检索功能。
示例:查询出比tom工资高的员工有哪些
步骤1:查询出tom工资是多少
SELECT salary FROM emp WHERE ename='tom';
步骤2:查询出比tom工资高的
SELECT * FROM emp WHERE salary>6000;
综合:
SELECT * FROM emp WHERE salary>( SELECT salary FROM emp WHERE ename='tom');
练习:查询出和king同一个部门下的员工有哪些
步骤1:查询出king的部门编号——10
SELECT deptId FROM emp WHERE ename='king';
步骤2:查询出部门下的员工
SELECT * FROM emp WHERE deptId=10;
综合:
SELECT * FROM emp WHERE deptId=(SELECT deptId FROM emp WHERE ename='king') AND ename!='king';
练习:查询出和tom同一年出生的员工有哪些
步骤1:查询出tom出生的年份
SELECT YEAR(birthday) FROM emp WHERE ename='tom'
步骤2:查询出生年份(1990)下的员工
SELECT * FROM emp WHERE YEAR(birthday)=1990;
综合:
SELECT * FROM emp WHERE YEAR(birthday)=(SELECT YEAR(birthday) FROM emp WHERE ename='tom');
3、多表查询
要查询的列分布在不同的表中,前提是表之间有关联。
示例:查询出所有员工的姓名及其部门名称
SELECT ename,dname FROM emp,dept WHERE deptId=did;
🛎️注意事项:多表查询时,在列前添加所在的表名称,可以避免不同表出现相同的名字而造成冲突
SELECT emp.ename,dept.dname FROM emp,dept WHERE emp.deptId=dept.did;
SQL命令中可以在列前添加所在的表名称
多表查询需要添加查询条件,就是两个表的关联
内连接(等值连接)
SELECT ename,dname FROM emp INNER JOIN dept ON deptId=did;
和之前的查询结果一样
补:WHERE也是一种内连接,属于隐式内连接,INNER JOIN属于显式内连接
左外连接
SELECT ename,dname FROM emp LEFT OUTER JOIN dept ON deptId=did;
显示左侧表中所有记录, outer关键子可以省略
右外连接
SELECT ename,dname FROM emp RIGHT OUTER JOIN dept ON deptId=did;
显示右侧表中所有记录,outer关键子可以省略
全连接(MySQL不支持)
FULL JOIN… ON …
联合
union all 联合后不合并相同记录
union 联合后合并相同的记录
全连接的解决:左外连接和右外连接
(SELECT ename,dname FROM emp LEFT OUTER JOIN dept ON deptId=did)
UNION ALL
(SELECT ename,dname FROM emp RIGHT OUTER JOIN dept ON deptId=did);
番外:左连接 ,右连接,内连接以及全外连接
基本含义:
(1). left join 左连接
返回包括左表中的所有记录和右表中连接字段相等的记录。
(2). right join (右连接)
返回包括右表中的所有记录和左表中连接字段相等的记录。
(3). inner join (等值连接或者叫内连接)
只返回两个表中连接字段相等的行。
(4). full join (全外连接)
返回左右表中所有的记录和左右表中连接字段相等的记录。
示例:
A表
id | name |
---|---|
1 | 小王 |
2 | 小李 |
3 | 小刘 |
B表
id | A_id | name |
---|---|---|
1 | 2 | 老师 |
2 | 4 | 程序猿 |
内连接:只有2张表存在匹配的行才能显示
select a.name,b.job from A a inner join B b on a.id=b.A_id
匹配结果:仅能得到一条记录
name | job |
---|---|
小李 | 老师 |
左连接:(左边的表不加限制)
select a.name,b.job from A a left join B b on a.id=b.A_id
匹配结果:有三条记录
name | job |
---|---|
小王 | NULL |
小李 | 老师 |
小刘 | NULL |
右连接:(右边的表不加限制)
匹配结果:有两条记录
select a.name,b.job from A a right join B b on a.id=b.A_id
name | job |
---|---|
小李 | 老师 |
NULL | 程序猿 |
全外连接: (左右2张表都不加限制)
select a.name,b.job from A a full join B b on a.id=b.A_id
匹配结果:有四条数据
name | job |
---|---|
小王 | NULL |
小李 | 老师 |
小刘 | NULL |
NULL | 程序猿 |
回顾上一单元,你可以点击👉【MySQL】DDL语句详解:列类型、列约束及自增列_Unit03