MySQL（5）

2022-01-31 15:45:11

一、DQL（重点）

数据库在执行DQL的时候，不会都数据进行改动，仅仅是看。只要把服务器中的数据获取返回得客户端。

查询得到的结果也是一张表

9.1数据的准备

INSERT INTO stu VALUES('S_1001', 'liuYi', 35, 'male');
INSERT INTO stu VALUES('S_1002', 'chenEr', 15, 'female');
INSERT INTO stu VALUES('S_1003', 'zhangSan', 95, 'male');
INSERT INTO stu VALUES('S_1004', 'liSi', 65, 'female');
INSERT INTO stu VALUES('S_1005', 'wangWu', 55, 'male');
INSERT INTO stu VALUES('S_1006', 'zhaoLiu', 75, 'female');
INSERT INTO stu VALUES('S_1007', 'sunQi', 25, 'male');
INSERT INTO stu VALUES('S_1008', 'zhouBa', 45, 'female');
INSERT INTO stu VALUES('S_1009', 'wuJiu', 85, 'male');
INSERT INTO stu VALUES('S_1010', 'zhengShi', 5, 'female');
INSERT INTO stu VALUES('S_1011', 'xxx', NULL, NULL);

INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

INSERT INTO dept values(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept values(20, 'RESEARCH', 'DALLAS');
INSERT INTO dept values(30, 'SALES', 'CHICAGO');
INSERT INTO dept values(40, 'OPERATIONS', 'BOSTON');

9.2基础查询

9.2.1查询所有列

查询所有的列：*表示所有的列

select * from 表名;

9.2.2查询指定列

select 列名 from 表名;

9.3条件查询where

9.3.1常见的运算符

关系运算符：=，!=，<，>，>=，<=

区间：between A and B -> [A,B]

And:并且，和。

Or:或者

Is null:空

Not:否，非

Is not null：非空

In:在什么里面

9.3.2或or、并且and

##（2.1）查询性别女，并且年龄65的学生记录

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

##(2.2)查询学号是S_1001或者名字为liSi的记录。

select * from stu where sid = 'S_1001' or age = 65;

9.3.3在什么里面in

select * from stu where sid in ('S_1001','S_1002','S_1003');

9.3.4是否为空is (not) null

select * from stu where age is null;

9.3.5区间between

select * from stu where age between 20 and 40;

9.3.6非not

select * from stu where sname is not null;

9.4模糊查询

9.4.1介绍

（1）当想查询学生姓名中包括字符a，就需要使用模糊查询，模糊查询的话使用关键字是like（像）

（2）通配符

_:任意一个字符

张_:张三，张三疯

张__:张三，张三疯

%:表示0或者多个字符串

张%

9.4.2通配符：_

##查询名字由5个字母构成的学生记录

select * from stu where sname like '_____';

##查询名字由5个字母构成的,并且第五个字符是i 学生记录

select * from stu where sname like '____i';

9.4.3通配符：%

##查询名字以‘z’开头的学生记录

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

##查询名字中第二个字符是i的学生记录

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

##查询名字中包含a字符的学生记录

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

课后作业：

(1)隐式（implicit）转换

(2)ID号，不一定非得是数字

4月4日0:00 11001

4月5日 0:00 11190

(3)SQL的性能优化

（a）避免使用*，如果表中有100个字段，全部列出

（b）模糊查询时候，避免使用通配符开头（避免全表扫描）

9.5字段控制查询

9.5.1去处重复数据（distinct）

#（1）查询员工的所有部门

select distinct deptno from emp;

9.5.2ifnull

##（2）查询员工的薪资（工资+奖金）

ifnull (A,B);如果A是null，使用B的值，如果A不是null，就使用A

select

sal,comm,sal + ifnull(comm,0)

from

emp;

9.5.3别名（as）

别名：给这个列换一个新名字。

select

sal,comm,sal + ifnull(comm,0) as money

from

emp;

##as 关键字可以省略

select

ename name,sal,comm,sal+ifnull(comm,0) as money

from

emp;

9.5.4排序（order by asc/desc）

##排序order by

升序：asc

##（1）查询所有的学生信息，根据年龄进行升序排序

select * from stu order by age asc;

降序：desc

##（2）查询所有的学生信息，根据年龄进行降序排序

select * from stu order by age desc;

##(3)查询所有的学生信息，根据名字进行升序排序

select * from stu order by sname asc;

##(4)查询所有员工信息，根据工资进行排序，如果工资相同按照员工号升序

select * from emp order by sal desc,empno asc;

9.6聚合函数

Mysql系统提供了很多的函数

Count：统计个数，次数，null不统计

Max：最大值

Min：最小值

Sum：求和

Avg：平均值

Round：四舍五入

有使用聚合函数的时候，一般都会给别名

9.6.1 count函数

##查询emp表中记录数

##count(*):不会忽略null的值。

select count(*) from emp;

##统计emp表中，有提成的人数

##count（具体字段）；null的话会被忽略

select count(comm) from emp;

##（3）工资大于2500的员工的人数

select count(*) from emp where sal > 2500;

##(4)统计薪资（工资+奖金）大于2500的员工的人数

select count(*) from emp where sal+ifnull(comm,0)>2500;

##（5）查询有提成的人数，有上一级领导的人数。

select count(*) from emp where comm is not null or mgr is not null;

9.6.2 sum求和，avg平均分，round四舍五入

#sum、avg、round

##（1）查询所有员工工资总和

select sum(sal) from emp;

##(2)查询所有员工工资总和、提成总和

select sum(sal),sum(comm) from emp;

##(3)查询所有员工工资总和、提成总和、薪资

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

##(4)计算员工的平均工资

select avg(sal),avg(comm) from emp;

#(5)平均工资，保留2位小数

select round(avg(sal),0),avg(comm) from emp;

9.6.3 max和min

#max最大值和min最小值

##查询员工表中，工资最高、最底的

select max(sal) max_sal,min(sal) min_sal from emp;

9.6.4 分段函数 csae

#分段函数 case

##员工表中，根据薪资不一样，给不一样的评论

>4000,NB666

>3000,NB66

>2000,NB6

<2000,gui

select

sal,

case

when sal >4000 then "NB666"

when sal >3000 then "NB66"

when sal >2000 then "NB6"

else "gui"

end as pj

from

emp;

9.6.5字符串截取

Left（A,4）从右边开始截取4个字符

Righr（A,4）从右边开始截取4个字符

##截取

1987-04-19 -> 1987

select right('1987-04-19',4)

###(1)获取员工的入职年份

select ename,hiredate,left(hiredate,4) from emp;

###(2)获取员工的入职工龄

select ename,hiredate,2021 - left(hiredate,4) from emp;

作业：

（1）怎么获取中间的内容（左右截取）

--1、SUBSTRING()方法---

-- substring()函数说明---

--1）语法：SUBSTRING(character,start,length)

--2）介绍：参数1：要截取的字符串，参数2：开始截取的下标，参数3：截取的字符长度

--3）使用：

select ename,hiredate,substring(hiredate,6,2) from emp;

（2）工龄获取

select ename,hiredate,substring(hiredate,6,2) from emp;

9.7分组查询

9.7.1场景

统计各个部门的人数情况

按照性别统计人数。

按照班级统计比赛的人数

##（1）统计各个部门的人数

部门的ID 人数

10 6

20 3

30 4

##（2）统计各个部门的平均工资。

部门的ID 平均工资

10 600

20 300

30 400

（1）分组之后的结果：和具体一条数据没有关系

（2）分组会和聚合函数一起用

（3）需求中包含：“每”，“各”这种，group by

9.7.2 group by

##(1)统计各个部门的人数

deptno,count(*),avg(sal)

from

emp

group by

deptno;

##（2）统计各个部门的平均工资

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

部门的ID 平均工资

10 600

20 300

30 400

##统计各个部门各个岗位人数

部门的ID 岗位人数

10 JAVA开发 10

1 前端开发 6

2 出纳 2

2 会记 4

select

deptno,job,count(*)

from

emp

group by

9.7.3 having

需求：统计超过工资超过2000的部门

（1）算出各个部门的平均工资。

（2）过滤，只留下超过2000的。

select

deptno,avg(sal) as sal_avg

from

emp

group by

deptno

having

sal_avg>2000;

9.7.4 where与having的区别

Where：过滤；分组之前的过滤；对数据源进行过滤；

Having：过滤；分组之后的过滤

9.8 Limit

##语法limit,start len；从start开始，差len条

##（1）查询前五条数据

select * from emp limit 0,5;

select * from emp limit 5;

##（2）查询第二条开始，查询五条

select * from emp limit 2,5;

使用场景：分页查询

如果一个页面显示10条数据，获取数据

第一次 limit 0,10

第二次 limit 10,10

第三次 limit 20,10

9.9执行顺序

需求：查询各个部门平均工具前2；

select

deptno,ename,avg(sal) as sal_avg

from

emp

where

mgr is not null

group by

deptno

order by

sal_avg desc

limit

关键字执行顺序：

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

码农公寓

一、DQL（重点）

9.1数据的准备

9.2基础查询

9.2.1查询所有列

9.2.2查询指定列

9.3条件查询where

9.3.1常见的运算符

9.3.2或or、并且and

9.3.3在什么里面in

9.3.4是否为空is (not) null

9.3.5区间between

9.3.6非not

9.4模糊查询

9.4.1介绍

9.4.2通配符：_

9.4.3通配符：%

9.5字段控制查询

9.5.1去处重复数据（distinct）

9.5.2ifnull

9.5.3别名（as）

9.5.4排序（order by asc/desc）

9.6聚合函数

9.6.1 count函数

9.6.2 sum求和，avg平均分，round四舍五入

9.6.3 max和min

9.6.4 分段函数 csae

9.6.5字符串截取

9.7分组查询

9.7.1场景

9.7.2 group by

9.7.3 having

9.7.4 where与having的区别

9.8 Limit

9.9执行顺序

相关文章