SQL - DQL 数据查询语言

数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端。

数据查询结果集是一张虚拟表。

关键字：SELECT

语法：SELECT 列名 FORM 表名 【WHERE   -->GROUP BY   -->HAVING   -->ORDER BY】

* 表示所有列

语法格式：

select 要查询的列表
from 表名
where 限定条件 /* 行条件 */
group by grouping_columns /*对结果分组*/
having condition  /*分组后的行条件*/
order by sorting_columns  /*对结果进行排序*/
limit off_start,row_count /*结果返回行数*/

示例：

-- 创建学生表
create table stu(
	sid char(6),
	sname varchar(50),
	age int,
	gender varchar(50)
);
insert into stu values
('s_1001','LiuYi',23,'male'),
('s_1002','Zhangjiaju',13,'male'),
('s_1003','Wangli',23,'female'),
('s_1004','Qili',22,'male'),
('s_1005','Dongdong',21,'female'),
('s_1006','LiuSD',25,'female'),
('s_1007','WUJIN',23,'male'),
('s_1008','wa',31,'female'),
('s_1009','QIL',23,'male');


-- 创建部门表
CREATE TABLE dept( 
	deptno INT, 
	dname varchar(14), 
	loc varchar(13) 
);
INSERT INTO dept values
(10, 'ACCOUNTING', 'NEW YORK'),
(20, 'RESEARCH', 'DALLAS'),
(30, 'SALES', 'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON');

1、简单查询

-- 查询所有列
select * from stu;
-- 查询指定列
select sid,sname,age from stu;

 

2、条件查询

条件查询就是在查询时候给出where子句，在where子句中可以使用如下关键字和运算符：

>、>=、<、<=、!=、<>、=;

between......and;  in(set);  is null;  and;  or;  not;

 

1、查询性别为女，年龄50岁以内的学生；

select * from stu where gender='female' and age<50;

 

2、查询学号为s_1001 或者 姓名为 LiuSD 的学生；

select * from stu where sid='s_1001' or sname ='LiuSD';

 

3、查询学号为s_1001、s_1003、s_1004的学生

select * from stu where sid in('s_1001','s_1003','s_1004');

 

4、查询学号不是s_1001、s_1003、s_1004的学生

select * from stu where sid not in('s_1001','s_1003','s_1004');

 

5、查询年龄为null的记录

select * from stu where age is null;

 

6、查询年龄在20到40岁之间的学生

select * from stu where age between 20 and 40;

between使用注意：

1. 开始值<结束值
2. 包含临界值

7、查询非男生的学生

select * from stu where gender !='male';

 

3、模糊查询

当查询学生姓名中包含a的时候就需要用到模糊查询，使用关键字 like。

语法： 列名 like '表达式'

表达式通配符：

_:任意一个字符。

%：任意0-n个字符。

1、查询姓名由三个字符组成的学生

select * from stu where sname like '___';

 

2、查询姓名由5个字符组成，且第五个字符为i的学生

select * from stu where sname like '____i';

 

3、查询姓名以z开头的学生

select * from stu where sname like 'z%';

 

4、查询姓名中包含a字符的学生

select * from stu where sname like '%a%';

 

4、字段控制查询

-- 创建职员表
create table emp2(
	empno int,
	ename varchar(50),
	job varchar(50),
	mgr int,
	hiredate date,
	sal decimal(7,2),
	comm decimal(7,2),
	deptno int
);

insert into emp2 values
(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20),
(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30),
(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30),
(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20),
(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30),
(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30),
(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10),
(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20),
(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);

 

4.1、去除重复记录

去除重复记录（两行或者两行以上记录中字段值存在相同），可以使用DISTINCT 关键字去重重复字段值。

select distinct sal from emp2;

 

4.2、查看雇员月薪与佣金之和

两个相同的数值类型，可以进行加法运算；如果两个中有一个字段不是数值类型，会出现错误。

select sal+comm from emp2;

运算过程中有NULL值，可以将NULL转换为0，使用函数 IFNULL(表达式,变换结果)

select ifnull(sal,0)+ifnull(comm,0) from emp2;

 

4.3、给列名添加别名

as 可以省略

select ifnull(sal,0)+ifnull(comm,0) as total from emp2;

select ifnull(sal,0)+ifnull(comm,0) total from emp2;

 

5、排序

语法：order by 列名 asc / desc

//asc 升序；desc 降序；默认不写为升序

1、所有学生按照年龄升序

select * from stu order by age;

2、所有雇员按照月薪降序，月薪相同时，按照编号升序

select * from emp2 order by sal desc,empno;

 

6、聚合函数

聚合函数是用来做纵向运算的函数。

count(列名)：统计列不为null 的行数。

max(列名)：计算指定列最大值，如果指定列为字符串，那么使用字符串排序运算。

min(列名)：计算指定列最小值，如果指定列为字符串，那么使用字符串排序运算。

sum(列名)：计算指定列的和，如果指定列不是数值类型，那么计算结果为0。

avg(列名)：计算指定列的平均值，如果指定列不是数值类型，那么计算结果是0.

6.1、COUNT

查询雇员数量

select count(*) as cnt from emp2;

 

查询雇员中有佣金的人数

select count(comm) as cnt from emp2;

 

查询月薪大于2500的雇员人数

select count(*) cnt from emp2 where sal>2500;

 

6.2、SUM和AVG

查询所有雇员月薪和

select sum(sal) from emp2;

 

查询所有雇员月薪加佣金和

select sum(ifnull(sal,0)+ifnull(comm,0)) from emp2;

 

统计所有雇员平均工资

select avg(sal) from emp2;

 

3、MAX和MIN

查询最高和最低工资

select max(sal),min(sal) from emp2;

 

7、分组查询

当需要计算每个部门的工资和时，就需要使用分组 group by。

注意：如果查询语句中有分组操作，那么select 后面只能查询聚合函数和分组的列名。

7.1、分组查询

查询每个部门的工资和和部门编号

select sum(sal),deptno from emp2 group by deptno;

 

查询每个部门部门编号和工资大于1500的人数

select deptno,count(*) from emp2 where sal>1500 group by deptno;

 

7.2、使用having子句过滤

查询工资总和大于9000的部门编号和工资总和

select deptno,sum(sal) as totalSal from emp2 group by deptno having totalSal>9000;  

 

having与where的区别：

1. 是在分组后对数据进行过滤，where是在分组前对数据进行过滤；
2. 后面可以使用分组函数，where后面不可以使用分组函数；

where是分组前对数据进行过滤，如果某行没有满足where条件，那么它不会参加分组；而having是对分组后的数据约束。

 

7.3、多列分组

统计各个班级男女生人数

select gradename,gender,count(*) from stu group by gradename,gender;

 

8、LIMIT

limit用来限制查询结果的起始行数，以及总行数。

语法：

1. 开始下标，显示条数；//开始下标从0开始
2. 显示条数；//表示默认从0开始获取数据

 

从下标3开始，查询10行雇员信息

select * from emp2 limit 3,10;

 

分页查询：

pageIndex 页码值，pageSize 每页显示行数

limit (pageIndex-1)*pageSize,pageSize;

 

总结：

• select - from - where - group by - having - order by - limit
• from - where - group by - having - select - order by - limit