SQL中的DQL（数据查询）语言

SQL语句分类

通常分为五类：

DDL（数据定义语言）：create、alter、drop等
DQL（数据查询语言）：select等
DML（数据操纵语言）：insert、delete、update等
TCL（事务操纵语言）：commit、rollback等
DCL（数据控制语言）：grant、revoke等

本篇文章重点讲述DQL（数据查询语言的语法格式），如有错误请指正~

DQL（数据查询语言）

数据查询语言需要重点掌握！！！

• 格式：select 字段信息 from 表名;
• 举例：
  • select name from person;
  • select name,age from person;
  • select * from person;
  • select * from person where age=50;
  • select age from person where name=’悟空’;

条件查询(where)

1、查询员工表中所有不同的工作

select distinct job from emp;

2、查询员工表中出现了哪几个不同的部门id?

select distinct dept_id from emp;

3、查询有领导的员工姓名和领导id

select name,manager from emp where manager is not null;

4、查询没有领导的员工姓名；

select name from emp where manager is null;

多条件查询 (and 和 or)

1、查询1号部门工资高于2000的员工信息

select * from emp where dept_id=1 and sal>2000;

2、查询3号部门或工资等于5000的员工信息

select * from emp where dept_id=3 or sal=5000;

3、查询出孙悟空和猪八戒的员工信息

select * from emp where name=”孙悟空” or name=”猪八戒”;

between x and y 两者之间

1、查询工资在2000到3000之间的员工信息

select * from emp where sal=>2000 and sal<= 3000;

select * from emp where sal between 2000 and 3000;

select * from emp where sal not between 2000 and 3000;

in 关键字

1、查询工资等于5000,1500,3000的员工信息

select * from emp where sal in(5000,1500,3000);

select * from emp where sal=5000 or sal=1500 or sal=3000;

select * from emp where sal not in(5000,1500,3000);

模糊查询like

• %：代表0或多个未知字符
• _：代表1个未知字符
• 举例：
  • 以x开头 x%
  • 以x结尾 %x
  • 包含 %x%
  • 第二个字符是x _x%
  • 以x开头以y结尾 x%y
  • 第二个是x，倒数第三个是y x%y _

1、查询名字姓孙的员工信息

select * from emp where name like “孙%”;

2、查询名字以精结尾的员工姓名

select name from emp where name like “%精”;

3、查询工作第二个字是售的员工姓名和工作

select name,job from emp where job like “_售”;

4、查询名字中包含僧并且工资大于2000的员工姓名和工资

select name,sal from emp where name like “%僧%” and sal > 2000;

比较运算符 >< ≥ ≤ = ≠ 和<>

1、查询工资大于等于3000的员工信息

select * from emp where sal>=3000;

2、查询工作不是程序员的员工信息(两种写法)

select * from emp where job!=”程序员”;

排序查询order by

• 格式：order by 字段名 asc(升序，默认Ascending)/desc（降序Descending）

  1、查询所有员工姓名和工资，并按照工资升序排序

  select name,sal from emp order by sal;

  select name,sal from emp order by sal asc;

  2、查询所有员工姓名和工资，并按照工资降序排序

  select name,sal from emp order by sal desc;

  3、查询所有员工姓名，工资和部门id并且按照部门id升序排序，如果部门id一致则按照工资降序排序

  select name,sal,dept_id from emp order by dept_id,sal desc;

分页查询

• 格式：limit 跳过的条数,请求的条数(每页的条数)

• 跳过的条数=（请求的页数-1）*请求的条数（每页条数）

• 举例：

  • 查询1-5条数据 limit 0,5 第一页
  • 查询6-10条数据 limit 5,5 第二页
  • 请求第一页的10条数据 limit 0,10
  • 请求第三页的10条数据 limit 20,10
  • 请求第八页的10条数据 limit 70,10
  • 请求第六页的8条数据 limit 40,8

  1、查询工资最低的3个员工信息(排序和分页结合一起)

  select * from emp order by sal limit 0,3;

  2、按照入职日期（hiredate）升序排序 查询第三页的3条数据

  select * from emp order by hiredate limit 6,3;

  3、查询工资最高的员工信息

  select * from emp order by sal desc limit 1;

  4、查询按照工资降序第二页的5条数据

  select * from emp order by sal desc limit 5,5;

查询的别名

select name as “姓名” from emp;

select name “姓名” from emp;

select name 姓名 from emp;

聚合函数

• 通过聚合函数可以对查询的多条数据进行统计查询,统计查询的方式包括:求平均值, 求最大值,求最小值,求和，计数

1、平均值avg(字段名)

• 查询1号部门的平均工资

  select avg(sal) from emp where dept_id=1;

• 查询销售的平均工资

  select avg(sal) from emp where job=”销售”;

2、最大值max(字段名) 最小值min(字段名)

• 查询程序员的最高工资

  select max(sal) from emp where job = “程序员”;

• 查询3号部门的最低工资

  select min(sal) from emp where dept_id = 3;

3、求和sum(字段名)

• 查询2号部门的工资总和

  select sum(sal) from emp where dept_id = 2;

5、计数count(*或字段名) null不能统计

• 查询程序员的数量

  select count(*) from emp where job = “程序员”;

数值计算（+ - * / %）

1、查询每个员工的姓名，工资和年终奖（年终奖=5个月工资）

select name,sal,sal * 5 as 年终奖 from emp;

2、给3号部门的员工每人涨薪5块钱

update emp set sal= sal + 5 where dept_id = 3;

分组查询Group by

• 格式：group by 分组的字段名

• 举例：

  1、查询每个部门的平均工资

  select dept_id,avg(sal) from emp group by dept_id;

  2、查询每个部门的最高工资

  select dept_id,max(sal) from emp group by dept_id;

  3、查询每种工作的最高工资

  select job,max(sal) from emp group by job;

  4、查询每种工作的人数

  select job,count(*) from emp group by job;

  5、查询每个部门工资高于2000的人数

  select dept_id,count(*) from emp where sal > 2000 group by dept_id;

  6、查询每个部门有领导的员工的人数

  select dept_id,count(*) from emp where manager is not null group by dept_id;

having关键字

• where 后面只能写普通字段的条件，不能包含聚合函数

• having后面可以包含聚合函数的条件，且having需要和group by 结合使用，写在group by的后面

• 举例：

  1、查询每个部门的平均工资，要求平均工资高于2000

  select dept_id,avg(sal) from emp group by dept_id having avg(sal) > 2000;

  2、查询每种工作的人数，只查询人数大于1的

  select job,count() from emp group by job having count() > 1;

  select job,count(*) from emp group by job having c > 1;

  3、查询每个部门的工资总和，只查询有领导的员工，并且要求工资总和大于5400；

  select dept_id,sum(sal) from emp where manager is not null group by dept_id having sum(sal) > 5400;

  4、查询每个部门的平均工资，只查询工资在1000到3000之间的，并且过滤掉平均工资低于2000的

  select dept_id,avg(sal) from emp where sal between 1000 and 3000 group by dept_id having avg(sal) >= 2000;

各个关键字的书写顺序

select 查询的字段信息 from 表名 where 普通字段条件 group by 分组字段名 having 聚合函数条件 order by 排序字段名 limit 跳过条数，请求条数

子查询(嵌套查询)

\qquad 1. 查询工资大于2号部门平均工资的员工信息

\qquad select * from emp where sal > (select avg(sal) from emp where dept_id = 2);

\qquad 2. 查询工资高于程序员最高工资的员工信息

\qquad select * from emp where sal > (select avg(sal) from emp where job = “程序员”);

\qquad 3. 查询工资最高的员工信息

\qquad select * from emp where sal = (select max(sal) from emp);

\qquad 4. 查询和孙悟空相同工作的员工信息

\qquad select job from emp where name=“孙悟空”;

\qquad select * from emp where job=(select job from emp where name=“孙悟空”) and name!=”孙悟空";

\qquad 5. 查询拿最低工资员工的同事们的信息（同事指同一部门）

\qquad select * from emp where dept_id = (select dept_id from emp \qquad where sal = (select min(sal) from emp)) and name != (select \qquad name from emp where sal = (select min(sal) from emp));

关联关系

• 指创建的表和表之间存在的业务关系

• 有哪几种关系

  • 一对一：有AB两张表，A表中的一条数据对应B表中的一条数据，B表中的一条数据也对应A表中的一条数据

  • 一对多：有AB两张表,A表中的一条数据对应B表中的多条数据, 同时B表中的一条数据对应A表中的一条数据

  • 多对多：有AB两张表,A表中的一条数据对应B表中的多条数据, 同时B表中的一条数据也对应A表中的多条数据

• 表和表之间如何建立关系（通过外键字段建立关系，注意：外键不能添加多个值）

  • 一对一：在任意表中添加一个建立关系的字段指向另外一张表的主键
  • 一对多：在多的表中添加建立关系的字段（外键）指向另外一张表的主键
  • 多对多：需要建立单独的关系表，里面至少包含两个字段，分别指向另外两个表的主键

关联查询

• 同时查询多张表数据的查询方式称为关联查询

• 关联查询包含：等值连接，内连接和外连接

  关联查询之等值连接：

  • 格式：select * from A,B where 关联关系
  • 举例：

  1. 查询工资高于2000的员工的姓名和对应的部门名

     select e.name,d.name from emp e,dept d where e.dept_id = d.id and e.sal > 2000;

  关联查询之内连接

  • 格式：select * from A join B on 关联关系
  • 举例：

  1. 查询每个员工的姓名和对应的部门名

     select e.name,d.name,sal from emp e join dept d on e.dept_id=d.id;

  关联查询之外连接

  • 等值连接和内连接查询到的都是两张表的交集数据
  • 外连接查询的是一张表的全部和另外一张表的交集
  • 格式：select * from A left/right join B on 关联关系；left和right选一个，表示左边或右边的表为全部
  • 举例：

  1. 查询所有员工姓名和对应的部门名

     insert into emp(name,sal) values(“灭霸”,5);

     select e.name,d.name from emp e left join dept d on e.dept_id = d.id;

  2. 查询所有部门的名称，地点和对应的员工姓名和工资

     select d.name,d.loc,e.name,e.sal from dept d left join emp e on d.id=e.dept_id;

  关联查询总结

  1. 如果需要同时查询多张表的数据使用关联查询
  2. 关联查询包括：等值连接，内连接和外连接
  3. 等值连接和内连接查询的是两个表的交集数据，推荐使用内连接
  4. 如果需要查询一张表的全部和另外一张表的交集时，使用外连接，只需要掌握左外即可，因为表的位置可以交换

  如何查询多对多的数据(多张表连接)

  • 创建表：

    create table student(id int primary key auto_increment, name varchar(50))charset=utf8;

    create table teacher(id int primary key auto_increment, name varchar(50))charset=utf8;

    create table t_s(tid int, sid int);

  • 插入数据

    insert into teacher values(null,”苍老师”),(null,”传奇哥”);

    insert into student values(null,”小明”),(null,”小红”),(null,”小绿”),(null,”小狗”),(null,”小黄”);

    insert into t_s values(1,1),(1,5),(1,4),(2,2),(2,3),(2,1),(2,5);

  1. 查询每个老师对应的学生

     select t.name,s.name from teacher t join t_s ts on t.id=ts.tid join student s on s.id=ts.sid;

  2. 查询苍老师的学生都有谁

     select t.name,s.name from teacher t join t_s ts on t.id=ts.tid join student s on s.id=ts.sid where t.name= “苍老师”;

  3. 查询小明的老师是谁

     select t.name,s.name from teacher t join t_s ts on t.id=ts.tid join student s on s.id=ts.sid where s.name= “小明”;

总结：
DQL作为程序员最需要掌握的一类语言，其变化繁多，需要多敲多练。尤其需要注意的是：DQL语言中的所有语句都不会对数据本身进行修改！！！

这是来自新手程序员的亿点点总结，正在努力搭建自己的知识体系，若文章有误，请多多指正，万分感谢！