By

Contents
  1. 数据表的查询语句
    1. 一、简单查询
      1. 1.1 指定列名
      2. 1.2 指定别名
      3. 1.3 列运算
      4. 1.4 字符串类型可以做连续运算
      5. 1.5 去重：distinct 字段名
    2. 二、 条件查询
      1. 2.1 比较运算符
      2. 2.2 逻辑运算符
      3. 2.3 模糊查询
      4. 2.4 区间查询
        1. 2.4.1 in (…)
        2. 2.4.2 between…and…
      5. 2.5 NULL判断
    3. 三、排序
      1. 3.1 升序(asc)
      2. 3.2 降序(desc)
      3. 3.3 多种排序规则
    4. 四、聚合函数
      1. 4.1 sum(列)
      2. 4.2 max(列)
      3. 4.3 min(列)
      4. 4.4 avg(列)
      5. 4.5 count(列)
    5. 五、分组查询
      1. 5.1 按照某列分组
      2. 5.2 按照多列分组
      3. 5.3 分组后使用聚合函数
      4. 5.4 分组后限定查询：having
    6. 六、分页查询
    7. 七、内置函数[扩展]
      1. 7.1 字符串函数
      2. 7.2 数学函数
      3. 7.3 日期时间函数
    8. 七、多表查询
      1. 7.1 笛卡尔积
      2. 7.2 内连接
      3. 7.3 外连接
        1. 7.3.1 左外连接
        2. 7.3.2 右外连接
        3. 7.3.3 全外连接
      4. 7.3 自连接
    9. 八、子查询
    10. 九、总结

数据表的查询语句

用户对于数据表或视图等最常用的操作就是查询。也叫检索。通过select语句来实现
语法规则：

1
2
3
4
5
6
select {columns}
  from {table|view|other select}
  [where 查询条件]
  [group by 分组条件]
  [having 分组后再限定]
  [order by 排序]

注意事项：

  1. sql的语句对于大小写不敏感。数值也不分。(Oracle中数值区分大小写)
  2. 关键字不能被分行

一、简单查询

select 的检索语句中，只有select ，以及from子句。也是一个select语句必须的。

1.1 指定列名

查询指定的列：

1
select 列名,列名 from 表名

如果查询表中所有的字段，使用*。

1.2 指定别名

1
select 列名 as 别名,列名 别名 from 表名

as关键字：其实也可以省略。

1.3 列运算

可以使用算术表达式

在select语句中，检索到字段类型如果是number，date可以使用算术表达式
加：+，减：-，乘：*，除:/,括号

如果某一列有null数值，那么计算后的结果也是null。通用函数:ifnull(a,b),a为null那么取b，如果a不为null那么取a的值。

1.4 字符串类型可以做连续运算

concat(“我的名字是”, name,’我今年。。’);

把搜索到的列和列进行拼接显示。

1.5 去重：distinct 字段名

distinct 列名，表示去除重复
distinct 列名1，列名2，一行的数据都相同，才会被认为是重复的数据，去除。

1
select distinct job,deptno from emp;

二、 条件查询

在实际查询的过程中, 我们需要根据需要来过滤出我们想要的数据, 这种查询就叫条件查询。在检索数据信息的时候，使用限定条件。表示满足条件才会被检索到。

使用where子句语法格式：

1
select 检索列 from 表名 where 筛选条件

条件查询使用where子句对表中的数据进行筛选, 结果为true的行会出现的结果集中.

条件的表达式支持多种运算:

2.1 比较运算符

  • 等于 =
  • 大于 >
  • 小于 <
  • 大于等于 >=
  • 小于等于 <=
  • 不等于 != 或 <>

栗子：

1
2
3
4
5
6
7
查找年龄大于30岁的:

select * from stus where age>30;

练习：查询1981年之后入职的员工信息

select * from emp where hiredate>='1981-1-1';

2.2 逻辑运算符

  • and, && (与)
  • or, || (或)
  • not, !(非)
  • xor (异或)

栗子：

1
2
3
4
5
6
7
8
9
10
查询年龄大于30并且性别是女的:
select * from stus where age>30 and sex='女';
或
select * from stus where age>30 && sex='女';
查询年龄小于30或者性别是男的:
select * from stus where age<30 or sex='男';
或
select * from stus where age<30 || sex='男';
练习：查询20部门的销售，或者是工资大于3000的员工信息
select * from emp where deptno=20 and job='salesman' or sal>3000;

2.3 模糊查询

比如想查询姓名中含有’张’的这种需求, 就需要用到模糊查询.

模糊查询用到关键字 like

语法:

  1. %表示任意多个字符（0-多个）
  2. _(下划线)表示任意一个字符

查询姓名是两个字符的:

1
select * from stus where name like '张__';

栗子：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
select * from stus name like '%张%';
练习1：查询名字中以s开头的员工信息
select * from emp where ename like 's%';
练习2：查询名字中包含字母o的员工信息
select * from emp where ename like '%o%';
练习3：查询名字中第二个字母为A的员工信息
select * from emp where ename like '_a%';
练习4：查询名字中最后一个字母为N或D的员工信息
select * from emp where ename like '%n' or ename like '%d';
练习5：查询名字以J开头，长度为4的员工信息
select * from emp where ename like 'j___';
mysql> select * from emp where length(ename)=5 and ename like 'j%';
练习6：查询名字以j开头或以m开头的员工信息
select * from emp where ename like 'j%' or ename like 'm%';
练习7：查询81年入职的员工信息
mysql> select * from emp where hiredate like '1981%';
练习8：查询名字不以s开头的员工信息
mysql> select * from emp where ename not like 's%';
练习9：不是81年入职
mysql> select * from emp where hiredate not like '1981%';

2.4 区间查询

2.4.1 in (…)

  • in 表示在一个离散的非连续的范围中查找

查找id的值等于1或3或4的:

1
select * from stus where id in (1, 3,4);

2.4.2 between…and…

  • between...and...
  • 表示在一个连续的区间中查找.

栗子：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
练习1：在emp表中，使用in关键字查询职务为”president”,”manager”和”analyst”中任意一种的员工信息。
select * from emp where job in ('president','manager','analyst');

练习2：查询部门编号deptno为10,20的员工信息
select * from emp where deptno in(10,20);

练习3：查询工资sal为3000,5000,8000的员工信息
select * from emp where sal in(3000,5000,8000);

练习4：查询名字ename为SCOTT,KING,JONES的员工信息
select * from emp where ename in('scott','king','jones');

练习5：在emp表中，使用in关键字查询职务不是”president”,”manager”和”analyst”中任意一种的员工信息。
select * from emp where job not in ('president','manager','analyst');

练习6：查询部门编号deptno不是10,20的员工信息
select * from emp where deptno not in (10,20);
练习7：在emp表中，使用”between and”关键字查询工资在2000到3000之间的员工信息
select * from emp where sal between 2000 and 3000;

练习8：查询奖金在500-1000之间的员工信息
select * from emp where comm between 500 and 1000

练习9：查询入职时间在81年4月到9月之间入职的员工信息
select * from emp where hiredate between '1981-4-1' and '1981-9-30';

练习10：查询年收入在20000到30000之间的员工信息。
mysql> select ename, sal,comm, (sal+ifnull(comm,0))*12 as '年收入' 
from emp where (sal+ifnull(comm,0))*12 between 20000 and 30000;

注意：

1
where后不能跟别名

2.5 NULL判断

is NULL 用来判断某列是否为空. 注意: NULL用小写也是可以的.

1
2
3
4
5
练习11：检索出所有没有comm的员工信息
mysql> select * from emp where comm is NULL;

练习12：有comm的员工信息
select * from emp where comm is not NULL;

三、排序

默认情况下, 我们查到的顺序是按照数据在数据库中的插入顺序排列的.
但是在实际情况下,我们需要根据不同的条件来排序, 比如按照更新日期排序, 或者按照价格从低到高排序, 或者按照人气从高到底排序等等.
mysql支持对查询的结果进行重新排序.
使用order by 列子句可以完成排序的功能. 后面的列表示排序规则.

order by 子句。语法上位于一条sql语句的末尾。

3.1 升序(asc)

默认是升序排序
按照年龄从低到高排序.

1
select * from stus order by age;

说明:

  1. 默认情况下是按照升序排序.asc

    也可以在列的后面添加asc(ascend 提升)表示升序.

  2. 使用desc表示降序

    desc是单词 descend(下降)的缩写, 来表示降序排序.

3.2 降序(desc)

1
select * from stus order by age asc;

3.3 多种排序规则

可以先按照一定规则来排序, 当碰到相同的情况下, 按照第二种规则来排序, …
先按照年龄升序排序, 如果年龄相等再按照id的降序排序.

1
select * from stus order by age asc,id desc;

栗子：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
练习1：按照奖金排序降序
select * from emp order by comm desc;
select * from emp where comm is not null  order by comm desc;
练习2：按照员工编号升序排序
select * from emp order by empno asc;
练习3：按照入职日期排序
select * from emp order by hiredate asc;
练习4：按照deptno升序排序,部门编号相同按照工资降序
select * from emp order by deptno asc,sal desc;
练习5：按照员工工作排序，工作相同的按照工资降序。
mysql> select * from emp order by job asc, sal desc;
练习6：查询工作是clerk的员工并按照薪资降序排列:
select * from emp where job='clerk' order by sal desc;

练习7：查询部门编号是30或者是10的，并且主管是7839的员工并按照雇佣日期排序
select * from emp where deptno in(10,30) and mgr=7839 order by hiredate;
练习8：查询没有绩效的所有员工信息并按照薪水降序排列
select * from emp where comm is NULL order by sal desc;

四、聚合函数

在查询中，统计,求和等是很常用的，通过聚合函数完成能十分省事.

如果没有聚合函数，可能需要各种子查询，各种sql嵌套才能完成.

但是有了聚合函数，很多问题迎刃而解。

mysql提供了5个常用的聚合函数:sum(), max(), min(), avg(), count()

当然配合聚合函数使用的还有分组group by 和 having子句, 下一节再细讲.

4.1 sum(列)

这个聚合函数用来对那些列不是null的值进行求和.

  • 对所有的age求和
    ​

  • 多所年龄小于100的age求和

    ​

4.2 max(列)

这个聚合函数用来计算那些列不是null的值最大值.

  • 找到最大的年龄

    ​

4.3 min(列)

这个聚合函数用来计算那些列不是null的值最小值.

  • 找到最小的年龄

4.4 avg(列)

这个聚合函数用来计算那些列不是null的值平均值.

  • 计算age的平均值
1
2
练习2：求emp表中部门编号为20的，平均工资，工资总和，最高工资，最低工资，人数。
select sum(sal),avg(sal),min(sal),max(sal) from emp where deptno=20;

​

4.5 count(列)

统计函数.

count(*)—>统计所有行的数据,16

count(主键列)–>16

count(非主键列1),是否存在null数值。

count(comm)–>6

  • 统计一共有多少行数据

  • 统计性别不为null的数据 行数

    ​

五、分组查询

分组(group by)
group by 列名, 按照指定的列进行分组, 值相同的会分在一组. 分组前where，分组后having限定。
语法:

1
select 列名 from 表名 group by 列名;

说明:

  1. select后面跟的列名必须和group by后的列列名保持一致.
  2. 当group by单独使用时, 只显示每组的第一条记录. 所以group by单独使用的意义不大.大多要配合聚合函数。
  3. group by后面也可以给多个列进行分组, 表示这些列都相同的时候在一组.

5.1 按照某列分组

1
select sex from  stus group by sex;

5.2 按照多列分组

1
select name, sex from stus group by name,sex;

5.3 分组后使用聚合函数

1
select sex,count(*) from stus group by sex;

注意：

  1. 如果没有分组，那么组函数(max,min,avg,count….)作用在整张表上。
  2. 如果有分组，组函数作用在分组后的数据上。
  3. 在写select 子句中列，如果没有在组函数里，那么就要在group by后边。
1
select a，b，count(c),sum(d) from 表 group by a,b;

5.4 分组后限定查询：having

二次筛选：二次筛选是指分组后再对数据进行筛选.
需要使用having子句来完成.

1
select 列名 from  表名 group by 列名 having 条件

也就是说，分组后再限定，使用having子句

having子句对于group by分组后的结果，进行再次筛选。最后输出的结果就满足having条件的。

having子句和where子句的用法类似，都是用于限定条件。

1
2
3
4
5
6
7
8
9
10
11
> 对比：
> 1. where和having后面都是跟条件
> 2. where是对表中的数据的原始筛选
> 3. having是对group by的结果的二次筛选
> 4. having必须配合group by使用, 一般也会跟着聚合函数一起使用
> 5. 可以先有where, 后面跟着group by和having
>
> 区别和结论：
>   1.语法上：在having中使用组函数(max,min,avg,count...),而where后不能用组函数。
>   2.执行上：where是先过滤再分组。having是先分组再过滤。
>

栗子：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
练习1：求每个部门的人数
mysql> select deptno as 部门,count(*)as 人数 from emp group by deptno;

练习2：统计1981年以后入职的，每个部门的人数
select deptno,count(*)
from emp
where hiredate >= '1981-1-1'
group by deptno;

练习3：统计每个管理者，管理的人数。
select mgr,count(*) from emp group by mgr; //老板没人管

mysql> select mgr,count(*) from emp where mgr is not NULL group by mgr;

练习4：按照部门和工作分组。
mysql> select deptno,job from emp group by deptno,job;
group by deptno,
	10, 20,30
group by job
	5个工种
group by deptno,job //注意它俩前后的区别

练习5：按照部门分组，统计每个部门的人数，如果该部门人数大于5，被检索出
mysql> select deptno,count(*) from emp group by deptno having count(*)>=5;

六、分页查询

Limit子句(方言)

方言的意思是limit是mysql特有的。

Limit用于限定查询结果的起始行，以及总行数。

1
select * from 表名 limit start,count;

例如：查询起始行为第5行，一共查询3行

select * from stus limit 4, 3;

​ 其中4表示从第5行开始，其中3表是查询3行。即5,6,7行记录。

七、内置函数[扩展]

7.1 字符串函数

  • 查看字符的ascii码值ascii(str)，str是空串时返回0
1
select ascii('a');
  • 查看ascii码值对应的字符char(数字)
1
select char(97);
  • 拼接字符串concat(str1,str2…)
1
select concat(12,34,'ab');
  • 包含字符个数length(str)
1
select length('abc');
  • 截取字符串
    • left(str,len)返回字符串str的左端len个字符
    • right(str,len)返回字符串str的右端len个字符
    • substring(str,pos,len)返回字符串str的位置pos起len个字符，从1开始
1
select substring('abc123',2,3);
  • 去除空格
    • ltrim(str)返回删除了左空格的字符串str
    • rtrim(str)返回删除了右空格的字符串str
    • trim([方向 remstr from str)返回从某侧删除remstr后的字符串str，方向词包括both、leading、trailing，表示两侧、左、右
1
2
3
4
select trim('  bar   ');
select trim(leading 'x' FROM 'xxxbarxxx');
select trim(both 'x' FROM 'xxxbarxxx');
select trim(trailing 'x' FROM 'xxxbarxxx');
  • 返回由n个空格字符组成的一个字符串space(n)
1
select space(10);
  • 替换字符串replace(str,from_str,to_str)
1
select replace('abc123','123','def');
  • 大小写转换，函数如下
    • lower(str)
    • upper(str)
1
select lower('aBcD');

7.2 数学函数

  • 求绝对值abs(n)
1
select abs(-32);
  • 求m除以n的余数mod(m,n)，同运算符%
1
2
select mod(10,3);
select 10%3;
  • 地板floor(n)，表示不大于n的最大整数
1
select floor(2.3);
  • 天花板ceiling(n)，表示不小于n的最大整数
1
select ceiling(2.3);
  • 求四舍五入值round(n,d)，n表示原数，d表示小数位置，默认为0
1
select round(1.6);
  • 求x的y次幂pow(x,y)
1
select pow(2,3);
  • 获取圆周率PI()
1
select PI();
  • 随机数rand()，值为0-1.0的浮点数
1
select rand();
  • 还有其它很多三角函数，使用时可以查询文档

7.3 日期时间函数

  • 获取子值，语法如下
    • year(date)返回date的年份(范围在1000到9999)
    • month(date)返回date中的月份数值
    • day(date)返回date中的日期数值
    • hour(time)返回time的小时数(范围是0到23)
    • minute(time)返回time的分钟数(范围是0到59)
    • second(time)返回time的秒数(范围是0到59)
1
select year('2016-12-21');
  • 日期计算，使用+-运算符，数字后面的关键字为year、month、day、hour、minute、second
1
2
3
select '2016-12-21'+interval 1 day;
select '2017-12-12' + interval 3 month;
select date_add('2017-12-12', interval 1 day);
  • 日期格式化date_format(date,format)，format参数可用的值如下
    • 获取年%Y，返回4位的整数
      • 获取年%y，返回2位的整数
      • 获取月%m，值为1-12的整数
    • 获取日%d，返回整数
      • 获取时%H，值为0-23的整数
      • 获取时%h，值为1-12的整数
      • 获取分%i，值为0-59的整数
      • 获取秒%s，值为0-59的整数
1
2
select date_format('2016-12-21','%Y %m %d');
select str_to_date('12/12/2017','%d/%m/%Y');
  • 当前日期current_date()
1
select current_date();
  • 当前时间current_time()
1
select current_time();
  • 当前日期时间now()
1
select now();

七、多表查询

连接查询：当需要对有关系的多张表进行查询时，需要使用连接join

合并结果集

  1. 要求被合并的标中，列的类型和列数相同
  2. UNION,去除重复行
  3. UNION ALL， 不去除重复行
1
2
3
SELECT * FROM ta 
UNION ALL
SELECT * FROM tb;

分类 

1
2
3
4
5
6
1. 内连接
2. 外连接
	 A：左外连接
	 B：右外连接
	 C：全外连接(Mysql不支持)
3. 自连接(术语一种简化方式)

7.1 笛卡尔积

如果两张表在连接查询时，如果没有连接条件，那么就会产生笛卡尔积。(冗余数据)。

1
2
SELECT *
FROM emp,dept;

7.2 内连接

内连接查询出的所有的记录都满足条件

注意：join和on是连用的，不能和where连用

1
2
3
4
5
6
7
方言：select * from 表1 别名1, 表2 别名2 where 别名1.xx=别名2.xx
	也叫等值链接
如：
SELECT e.empno,e.ename,e.job,sal,e.deptno,d.deptno,d.dname,d.loc WHERE e.deptno=d.deptno;

标准：select * from 表1 别名1 inner join 表2 别名2 on 别名1.xx=别名2.xx
自然：select * from 表1 别名1 natural join 表2 别名2

7.3 外连接

内连接所检索出来的结果，都是满足连接条件的。外链接是扩展内连接检索出来的结果集。外链接返回的结果，除了包含满足链接条件的记录，还包括不满足连接条件。

7.3.1 左外连接

左表记录无论是否满足条件都会查询出来，而右表只有满足条件才能查询出来。左表中不满足条件的记录，右表部分都为NULL

语法格式：

1
2
3
4
5
6
左外：
	select * from 表1 别名1 left outer join 表2 别名2 on 别名1.xx=别名2.xx
	
左外自然：
	select * from 表1 别名1 natural left outer join 表2 别名2 
	自然连接即不用指定on 相等的条件，自动关联两张表中相同的列

7.3.2 右外连接

右表记录无论是否满足条件都会查询出来，而左表只有满足条件才能查询出来。右表中不满足条件的记录，左表部分都为NULL

语法格式：

1
2
3
4
5
右外：
	select * from 表1 别名1 right outer join 表2 别名2 on 别名1.xx=别名2.xx
	
右外自然：
	select * from 表1 别名1 natural right outer join 表2 别名2

7.3.3 全外连接

Mysql不支持全外连接(full outer join)，但是可以使用union来合并左外连接以及右外连接的结果，达到全外连接的效果。

1
2
3
4
5
6
7
8
9
左外连接
SELECT e.ename,e.sal, d.dname,e.deptno,d.deptno
FROM emp e LEFT OUTER JOIN dept d
ON e.deptno=d.deptno
UNION
右外连接
SELECT e.ename,e.sal, d.dname,e.deptno,d.deptno
FROM emp e RIGHT OUTER JOIN dept d
ON e.deptno=d.deptno

7.3 自连接

自连接(术语一种简化方式)。其实就是一张表连接自己。

典型应用：查询员工的上级名称

1
2
3
SELECT e.empno,e.ename,e.mgr,m.empno,m.ename
FROM emp e LEFT OUTER JOIN emp m
ON e.mgr=m.empno

本章栗子：

1
2
3
4
5
6
7
8
-- 练习1：查询员工信息，员工号，姓名，月薪，部门名称。
SELECT e.empno,e.ename,e.sal,d.deptno,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno=d.deptno
-- 练习2：查询员工工资等级，员工号，姓名，月薪,工资等级
SELECT s.grade,e.empno,e.ename,e.sal, s.losal,s.hisal
FROM emp e, salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;

八、子查询

子查询是指sql语句中包含另一个select语句。内查询或者内select语句

一条sql语句中，包含多个select关键字

外查询，内查询

子查询可以出现的位置：

​ from后，作为表

​ where后，作为条件

注意事项：
1.子查询必须在()里
2.在子查询中不能使用order by子句。
3.子查询可以再嵌套子查询，最多不能超过255层。

子查询细分为：单行子查询，多行子查询，关联子查询。

1.单行子查询
子查询的结果是单行数据。

1
在where条件后需要配合单行运算符：>,<,>=,<=,!=,=

2.多行子查询
子查询的结果是多行数据。

1
2
3
4
在where条件后需要配合：in，any，all运算符
in：
any：匹配上子查询的结果集中任意一个即可。
all：匹配上子查询的所有的结果。

3.关联子查询
对于单行子查询和多行子查询，外查询和内查询是分开执行的。
如果外查询使用到内查询的结果，就使用关联子查询。
关联子查询，是指在内查询中需要借助于外查询，而外查询离不开内查询的执行。就叫关联子查询。

九、总结

完整的select语句书写：

1
2
3
4
5
6
select distinct *
from 表名
where ....
group by ... having ...
order by ...
limit start,count

执行顺序：

1
2
3
4
5
6
7
from 表名
where ...
group by ...
having ...
select distinct*..
order by ...
limit start,count
Contents
  1. 数据表的查询语句
    1. 一、简单查询
      1. 1.1 指定列名
      2. 1.2 指定别名
      3. 1.3 列运算
      4. 1.4 字符串类型可以做连续运算
      5. 1.5 去重：distinct 字段名
    2. 二、 条件查询
      1. 2.1 比较运算符
      2. 2.2 逻辑运算符
      3. 2.3 模糊查询
      4. 2.4 区间查询
        1. 2.4.1 in (…)
        2. 2.4.2 between…and…
      5. 2.5 NULL判断
    3. 三、排序
      1. 3.1 升序(asc)
      2. 3.2 降序(desc)
      3. 3.3 多种排序规则
    4. 四、聚合函数
      1. 4.1 sum(列)
      2. 4.2 max(列)
      3. 4.3 min(列)
      4. 4.4 avg(列)
      5. 4.5 count(列)
    5. 五、分组查询
      1. 5.1 按照某列分组
      2. 5.2 按照多列分组
      3. 5.3 分组后使用聚合函数
      4. 5.4 分组后限定查询：having
    6. 六、分页查询
    7. 七、内置函数[扩展]
      1. 7.1 字符串函数
      2. 7.2 数学函数
      3. 7.3 日期时间函数
    8. 七、多表查询
      1. 7.1 笛卡尔积
      2. 7.2 内连接
      3. 7.3 外连接
        1. 7.3.1 左外连接
        2. 7.3.2 右外连接
        3. 7.3.3 全外连接
      4. 7.3 自连接
    9. 八、子查询
    10. 九、总结