day01
1、SQL、DB、DBMS分别是什么,他们之间的关系?
DBMS(数据库管理系统)负责执行sql语句,通过执行sql语句来操作DB当中的数据
DBMS-(执行)-SQL-(操作)-DB
2、什么是表
表:table是数据库的基本组成单元,所有的数据都以表格的形式组织,目的是可读性强
一个表包括行和列:
行:被称为数据/记录(data)
列:被称为字段(column)
每一个字段应该包括哪些属性?
字段名,数据类型,相关的约束
3、通用的SQL语句,包括增删改查,怎么分类?
DQL(数据查询):查询语句,凡是select语句都是DQL
DML(数据操作语言):insert delete updata 对表当中的数据进行增删改查
DDL(数据定义语言):create drop alter, 对表结构的增删改
TCL(事务控制语言):commit提交事务,rollback回滚事务
DCL(数据控制语言):grant授权,revoke撤销权限
4、导入数据
第一步:登录mysql-数据库管理系统
dos命令窗口:
mysql -uroot -p333
第二步:查看有哪些数据库
show database;
第三步:创建属于我们自己的数据库
create database bjpowernode;
第四步:使用bjpowernode数据
user bjpowernode;
第五步:查看当前使用的数据库中有那些表
show tables;
第六步:初始化数据
mysql>source D:course 5-MySQL
5、什么是sql脚本
bjpowernode.sql 这个文件以sql结尾,这样的文件被称为sql脚本,什么是sql脚本
当一个文件的扩展名是.sql,并且该文件编写了大量sql语句
SQL脚本的数据量太大的时候,无法打开,请使用source命令完成初始化
6、删除数据库
drop database bjpowernode;
7、查看表结构:
desc dept;查看部门表
8、表中的数据
select * from dept;
9、常用命令
select database();查看当前使用的是哪个数据库
select version();查看mysql版本号
c 结束一条语句
exit 退出
10、查看创建表的语句
show create table emp;
11、简单的查询语句(DQL)
语法格式:
select 字段名1,字段名2,字段名3,,,from 表名;
提示:
1、任何一条SQL语句以‘;’结尾
2、SQL不区分大小写
例子:查询员工的年薪?(字段可以参与数学运算)
select ename,sal * 12 from emp;
给查询结果的列重命名
select ename,sal * 12 as yearsal from emp;
别名中有中文?
select ename,sal * 12 as ‘年薪’from emp;
注意:标准sql语句中要求字符串使用单引号括起来,虽然支持双引号,尽量别用
as关键字可以省略
查询所有字段
select * from emp;
12、条件查询
语法格式:
select
字段,字段,,,
from
表名
where
条件;
执行顺序,先from 然后where 最后select
查询工资等于5000的员工姓名?
select ename from emp where sal=5000;
查询SMITH的工资?
select sal from emp where ename = ‘SMITH’;
找出工资高于3000的员工?
select ename,sal from emp where sal >3000;
找出工资在1100和3000之间的员工,包括1100和3300?
select ename,sal from emp where sal >=1100 and sal <=3000;
select ename,sal from emp where sal between 1100 and 3000;
between and 除了 可以使用在数字方面之外,还可以在使用字符串方面
select ename from emp where ename between ‘A’and ‘C’;#左闭右开
找出哪些人没有津贴?
在数据库当中NULL不是一个值,代表什么也没有,为空
空不是一个值,不能用等号衡量
必须使用is null 或者is not null
select ename,sal,comm from emp where comm is null;
找出工作岗位是MANAGER和SALESMAN的员工?
select ename,job from emp where job = ‘MANAGER’or job = ‘SALESMAN’;
and和or联合起来用:找出薪资大于1000的并且部门编号是20或30部门的员工
select ename,sal,deptno from emp where sal >1000 and (deptno = 20 or deptno =30);
注意:优先级不确定时,加小括号
in等于or:找出工作岗位是MANGER和SALESMAN的员工?
select ename,job,from emp where job = 'SALWSMAN' or 'MANAGER';
select ename,job,from emp where job in ('SALESMAN','MANAGER');
in后面的值不是区间,是具体的值
not in:不在这几个值当中
select ename,job,from emp where sal not in (800,5000);
模糊查询like
在模糊查询中,必须掌握两个特殊的符号,一个是%,一个是_
找出名字当中含有0的?
%代表任意多个字符,_代表任意1个字符
select ename from emp where ename like '%o%';
找出名字中第二个字母是A的?
select ename from emp where ename like '_A%';
找出名字中有下划线的?
select ename from emp where ename like '%\_%';
找出名字中最后一个字母是T的?
select ename from emp where ename like'%T';
13、排序(升序、降序)
按照工资升序,找出员工名和薪资?
select ename,sal from emp order by sal;
注意:默认是升序,怎么指定升序或者降序呢?asc表示升序,desc表示降序
select ename,sal from emp order by sal;升序
select ename,sal from emp order by sal asc; 升序
select ename,sal from emp order by sal desc;降序
按照工资的降序排列,当工资相同的时候再按照名字的升序排列
select ename,sal from emp order by sal desc,ename asc;
注意:越靠前的字段越能起到主导作用,只有当前的字段无法完成排序的时候,才会启用后面的字段
找出工作岗位是SALESMAN的员工,并且按照薪资的降序排列
select ename,job,sal from emp order by sal desc;
14、分组函数?
记住:所有的分组函数都是对“某一组”数据进行操作的
找出工资总和?
select sum(sal) from emp;
分组函数一共5个
分组函数还有另一个名字:多行处理函数
多行处理函数的特点:输入多行,最终输出的结果是1行
分组函数自动忽略NULL
select count(count)from emp;
select sum(comm) from emp where comm is not null; //不需要额外添加这个过滤条件,sum函数自动忽略NULL
找出工资高于平均工资的员工?
select ename,sal from emp where sal > avg(sal); //[Err] 1111 - Invalid use of group function无效使用分组函数
原因:SQL语句中有一个语法规则,分组函数不可直接使用在where子句中
因为group by 是在where 执行之后 才会执行
第一步:找出平均工资
第二步:找出高于平均的工资
select ename,sal from emp where sal > (select avg(sal) from emp);
count(*)和count(具体的某个字段),他们有什么区别?
count(*):不是统计某个字段中数据的个数,二是统计总记录条数(和统计字段无关)
count(comm):表示统计comm字段中不为NULL的数据总量
分组函数也能组合起来使用:
select count(*),sum(sal),avg(sal),min(sal),max(sal) from emp;
15、单行处理函数
什么是单行处理函数?
输入一行,输出一行。
计算每个员工 的年薪?
select ename,(sal+comm)*12 as yearsal from emp;
重点:只要有NULL参与的运算结果一定是NULL
ifnull()空处理函数?
ifnull(可能为null的数据,被当做什么处理)
select ename, ifnull(comm,0) from emp;
16、group by 和having
group by:按照某个字段或某些字段进行分组
having:对分组之后的数据进行再次过滤
案例:找出每个工作岗位的最高薪资
select max(sal)from emp group by job;
select max(sal),job from emp group by job;
注意:分组函数一般都会和group by联合使用,这也是为什么成为分组函数的原因
并且任何一个分组函数(count,sum,avg,max,min)都是在group by语句执行结束后才会执行
当一条sql语句没有group by的话,整张表的数据会自成一组
select ename,max(sal),job from emp group by job; //错误
以上在MySQL当中,查询结果是有的,但是没有意义,在oracle数据库当中会报错,语法错误
Oracle的语法规则比mysql语法规则更严谨
记住一个规则:当一条语句中有group by的话,select后面只跟分组函数和参与分组的函数
select 5
..
from 1
..
where 2
..
group by 3
..
having 4
..
order by 6
..
每个工作岗位的平均薪资?
select job,avg(sal)from emp group by job;
多个字段能不能联合一块分组?
找出每个部门不同工作岗位的最高薪?
select deptno,job,max(sal) from emp group by deptno,job;
找出每个部门的最高薪资,要求显示薪资大于2900的数据
第一步:找出每个部门的最高薪资
select max(sal),deptno from emp group by deptno;
第二步:找出薪资大于2900
select max(sal),deptno from emp group by deptno having max(sal)>2900 //效率低
使用此方法:
select max(sal),deptno from emp where sal>2900 group by deptno; //效率高,建议使用where过滤
不能使用where的情况
找出每个部门的平均薪资,要求显示薪资大于2000的数据;
第一步:找出每个部门的平均薪资
select deptno,avg(sal) from emp group by deptno;
第二步:要求薪资大于2900
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
select deptno,avg(sal) from emp where avg(sal)>2000 group by deptno;//错误 where后面不能使用分组函数
17、总结一个完整的DQL语句怎么写?
select 5
..
from 1
..
where 2
..
group by 3
..
having 4
..
order by 6
..
day02
1、关于查询结果集的去重
select distinct job from emp;
select ename,distinct job from emp;
以上的sql是错误的
记住:distinct只能出现在所有字段的最前面
select distinct deptno,job from emp;
后面字段联合去重
统计岗位的数量?
select count(distinct job) from emp;
2、连接查询
2.1、什么是连接查询
在实际开发中,大部分的情况下都不是从单表中查询数据,一般都是多张表联合查询取出最终的数据。
在实际开发中,一般一个业务都会对应多张表,比如:学生和班级,起码两张表
存放在一张表中,数据会出现大量的重复,导致数据冗余
2.2、连接查询的分类?
根据语法出现的年代来划分,包括:
SQL92(一些老的DBA可能还在使用这种语法)
SQL99(比较新的语法)
根据表的连接方式划分,包括
内连接:
等值联结
非等值联结
自连接
外连接:
左外连接(左连接)
右外连接(右连接)
全连接(不讲)
2.3、在表的连接查询方面有一种现象被称为:笛卡尔积现象
案例:找出每一个员工的部门名称,要求显示员工名和部门名
select ename,dname from emp,dept;
笛卡尔积现象:当两张表进行查询的时候,没有任何条件进行限制,最终查询的结果条数是两张表记录条数的乘积
关于表的别名:
select e.ename,d.dname from emp e dept d;
表的别名有什么好处?
第一:执行效率高
第二:可读性好
2.4、怎么避免笛卡尔积现象?当然是加条件进行过滤
思考:避免笛卡尔积现象,会减少记录的匹配次数吗?
不会,次数还是56次,只不过显示的是有效记录
案例:找出每一个员工的部门名称,要求显示员工名和部门名
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno; // SQL92语法,以后不用
2.5、内连接之等值连接:最大的特点是:条件是等量关系
案例:找每个员工的部门名称,要求显示员工名和部门名
SQL99:
select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;
语法:
....
A
join
B
on
连接条件
where
...
SQL 99语法结构更清晰一些:表的连接条件和后来的where条件分离
//inner可以省略的,带着inner目的是可读性好一些
select e.ename,d.dname from emp e inner join dept d on e,deptno= d.deptno;
2.6、内连接之非等值连接:最大的特点是 连接条件的关系是非等量关系
案例:找出每个员工的工资等级,要求显示员工名,工资,工资等级
select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
2.7、自连接:最大的特点是:一张表看做两张表 自己连接自己
案例:找出每个员工的上级领导,要求显示员工名和对应领导名
员工的领导编号=领导的员工编号
select a.ename,b.ename from emp a inner join emp b on a.mgr = b.empno;
2.8、外连接?
什么是外连接,和内连接有什么区别
内连接:假设A和B表进行连接,使用内连接的话,凡是A表和B表能匹配上的记录查询出来,这就是内连接
AB两张表没有主副之分,两张表是平等的
外连接:
假设A和B表进行连接,使用内连接的话,AB两张表中一张表是主表,一张是副表
主要查询主表的数据,捎带着查询副表,当副表中的数据没有和主表中的数据匹配上,副表自动模拟出NULL 与之匹配
外连接的分类?
左外连接(左连接):表示左边的这张表是主表
右外连接(右连接):表示的右边的表是主表
左连接有右连接的写法,右连接也会有对应的左连接的写法
案例:找出每个员工的上级领导(所有员工必须全部查询出来)
内连接:
select a.ename,b.ename from emp a inner join emp b on a.mgr = b.empno; //null的不匹配
左外连接:
select a.ename '员工',b.ename'领导' from emp a left join emp b on a.mgr = b.empno; //左表为主表
右外连接
select a.ename '员工',b.ename'领导' from emp b right join emp a on a.mgr = b.empno;
select a.ename,b.ename from emp a left outer join emp b on a.mgr = b.empno;
outer 可省略
外连接最重要的特点是:主表的数据无条件的全部查出来
案例:找出哪个部门没有员工?
select e.*,d.* from emp e right join dept d on e.deptno = d.deptno where e.empno is null;
2.9、三张表怎么连接查询?
案例:找出每一个员工的部门名称以及工资等级
注意:
A
join
B
join
C
on
..
表示:A表和B表先进行表连接,连接之后A表继续和C表进行连接
select e.ename,d.dname,s.grade from emp e join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and s.hisal;
案例:找出每一个员工的部门名称以及工资等级、以及上级领导
select
e.ename '员工',d.dname,s.grade ,e1.ename '领导'
from
emp e
join
dept d
on
e.deptno = d.deptno
join
salgrade s
on
e.sal between s.losal and s.hisal
left join
emp e1
on
e.mgr = e1.empno;
3、子查询
3.1、什么是子查询?都可以出现在哪里
select 语句当中嵌套select 语句,被嵌套的select 语句是子查询
子查询可以出现在哪里
select
.. (select),
from
..(select)
where
..(select)
3.2、where子句中使用子查询
案例:找出高于平均薪资的员工信息
第一步:找出平均薪资
select avg(sal)from emp;
第二步:where过滤
select * from emp where sal>2073.214286;
合并
select * from emp where sal>(select avg(sal) from emp);
3.3、from后面嵌套子查询
案例:找出每个部门平均薪水的薪资等级(按照部门编号分组,求sal平均水平)
第一步:找出每个部门平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;
第二步:将以上的查询结果当做临时表t,让t表和salgrade表连接,条件是t.avgsal between s.losal and s.hisal
案例:找出每个部门平均的薪水等级
第一步:找出每个员工的薪水等级
第二步:基于以上结果,继续按照deptno分组,求grade平均值
3.4、在select后面嵌套子查询
案例:找出每个员工所在的部门名称,要求显示员工名和部门名
4、union(可以将查询结果集相加)
案例:找出工作岗位是SALESMAN和MANAGER的员工?
第一种:
第二种:
第三种:
两张不相干的表中的数据拼接在一起显示
不同列报错
5、limit(重点中的重点,以后分页查询全靠它)
5.1、limit是mysql特有的,其他数据库没有,不通用。(Oracle中有一个相同的机制,叫做rownum)
5.2、limit取结果集中的部分数据,这时它的作用
5.3、语法机制:
limit startIndex,length
startIndex表示起始位置,从0开始,0表示第一条数据
length表示取几个
案例:取出工资前5名的员工(思路:降序取前5个)
取前5个:
5.4、limit是SQL语句最后一个执行的一个环节
select 5
...
from 1
...
where 2
...
group by 3
..
having 4
..
order by 6
..
limit 7
..;
5.5、找出工资排名在第4到第9名的员工?
5.6、通用的标准分页sql
每页显示3条记录:
第一页:0,3
第二页:3,3
第三页:6,3
第四页:9,3
每页显示pagesize条记录
第pageNO页:?,pageSize
第pageNO页:(pageNO-1)*pageSize,pageSize
pageSiaze是什么?是每页显示多少条记录
pageNO是什么?显示第几页
limit(pageNO -1) * pageSize,pageSize