4.导入数据
mysql -uroot -p密码
show databases; 查看有哪些数据库
create database bjpowernode; 创建数据库
use bjpowernode; 使用数据库
show tables; 查看数据库中的有哪些表
source D:\course\05-MySQL\resources\bjpowernode.sql 初始化数据
5. .sql 文件称为sql脚本,当太多使用source命令完成初始化
6. 删除数据库
drop database bjpowernode;
7.查看表结构
desc dept; 查看表结构
8.表中的数据
select * from emp;
9.常用命令
select database(); 查看当前使用的是哪个数据库
select version(); 查看mysql的版本号
\c 结束一条语句
exit 退出mysql
10.查看创建表的语句
show create table emp;
11.简单的查询语句(DQL)
select 字段名,字段名,字段名... from 表名;
提示:任何一条sql语句以";"结尾
sql 语句不区分大小写
select ename,sal*12 from emp;
select ename,sal*12 as yearsal from emp; 给查询结果的列重命名
select ename,sal*12 as '年薪' from emp; //别名有中文
标准sql语句中要求字符串使用单引号括起来,不建议使用双引号“”
select ename,sal*12 yearsal from emp; as关键字可以省略
select * from emp; 查询所有字段,//实际开发中不建议使用*,效率较低
12.条件查询
语法格式
select
字段,字段....
from
表名
where
条件;
执行顺序:先from,然后where,最后select
select ename from emp where sal = 5000; 查询工资等于5000的员工姓名
select sal from emp where ename = 'SMITH'; 查询SMITH的工资
//字符串使用单引号括起来
select ename,sal from emp where sal >= 3000; 找出工资高于3000的员工
select ename,sal from emp where sal !=/<> 3000; 找出工资不等于3000的员工
select ename,sal from emp where sal >=1100 and sal <= 3000;
select ename,sal from emp where sal between 1100 and 3000;
//between ..and... 是闭区间[1100~3000]
between and在使用的时候必须左小右大。
找出工资在1100和3000之间的员工,包括1100和3000
between and除了可以使用在数字方面之外,还可以使用在字符串方面。
select ename from emp where ename between 'A' and 'C' //左闭右开
select ename,sal,comm from emp where comm is null; 找出那些人津贴为NULL
在数据库中NULL不是一个值,不代表空,用is null 或者is not null
select ename,sal,comm from emp where comm = null
select ename,sal,comm from emp where comm is not null;找出那些人津贴不为NULL
select ename,sal,comm from emp where comm is null or comm = 0;找出那些人没有津贴
select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
找出工作岗位是MANAGER和SALESMAN的员工
select ename,sal from emp where sal > 1000
select ename,deptno emp where deptno = 20 or deptno = 30
selec ename,sal,deptno emp where sal > 1000 and (deptno = 20 or deptno = 30)
and和or联合起来使用:找出薪资大于1000并且部门编号是20或30部门的员工,
注意:当运算符的优先级不确定的时候加小括号
in等同于or:找出工作岗位是MANAGER和SALESMAN的员工
select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
select ename,job from emp where job in('MANAGER','SALESMAN');
select ename,sal from emp where sal in(800,5000);
//in后面的值是具体的值不是区间
select ename,job from emp where sal not in(800,5000); not in:不在这几个值当中
模糊查询like
(在模糊查询中,必须掌握两个特殊的符号,一个是%,一个是_)
%代表任意多个字符,_代表任意1个字符
select name from t_user where ename like '%O%';
select name from t_user where ename like '_A%'; 找出名字中第二个字母是A的
select name from t_user where ename like '%_%'; 找出名字中有下划线的
select ename from emp where ename like '%T';找出名字中最后一个字母是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;
按照工资的降序排列,当工资相同的时候再按照名字的升序排序
注意:越靠前的字段越能起主导作用,只有当前面的字段无法完成排序的时候,才会启用后面的字段
select ename,job,sal from emp where job = 'SALESMAN'/ in('SALESMAN') order by sal desc;
找出工作岗位是SALESMAN的员工,并且要求按照薪资的降序排列
执行代码 select .. from .. where .. order by ...
执行顺序from where order by select
14. 分组函数
count 计数
sum 求和
avg 平均值
max 最大值
min 最小值
记住:所有的分组函数都是对“”某一组“”数据进行操作的
select sum(sal) from emp; 找出工资总和
select max(sal) from emp;找出最高工资
select min(sal) from emp;找出最低工资
select avg(sal) from emp;找出平均工资
select count(*/ename) from emp;找出总人数
分组函数一共有5个(计求平大小)
分组函数还有另一个名字:多行处理函数
多行处理函数的特点:输入多行,最终输出的结果是1行
select count(comm) from emp; 分组函数自动忽略NULL
select sum(comm) from emp;
select sum(comm) from emp where comm is not null;//不需要额外添加这个条件,sum函数自动忽略NULL
找出工资高于平均工资的员工
select ename,sal,avg(sal) from emp where sal > avg(sal); //error
思考以上的错误信息:无效的使用过了分组函数?
原因:SQL语句中有一个语法规则,分组函数不可直接使用在where子句当中,why?
因为group by是在where执行之后才会执行。
select 5
..
from 1
..
where 2
..
group by 3
..
having 4
..
order by 6
..
select ename,sal.avg(sal) from emp having sal > avg(sal)
count(*)和count(具体的某个字段),他们有什么区别?
count(*)统计总记录条数(和某个字段无关)
count(comm)表示统计comm字段中不为NULL的数据总数量
分组函数也能联合起来用
select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;
找出工资高于平均工资的员工?
select avg(sal) from emp as e;
select ename,sal from where sal >(select avg(sal) from emp);
15.单行处理函数
输入一行,输出一行
计算每个员工的年薪
select ename,(sal+comm)*12 as yearsal from emp;
重点:所有数据库都是这样规定的,只要有NULL参与的运算结果一定是NULL,
使用ifnull函数
select emame,(sal + ifnull(comm,0))*12 as yearsal from emp;
ifnull()空处理函数?
ifnull(可能为NULL的数据,被当做什么处理):属于单行处理函数
select ename,ifnull(comm,0) as comm from emp;
16. group by 和having
group by 按照某个字段或者某些字段进行分组
having 是对分组之后的数据进行再次过滤
找出每个工作岗位的最高薪资
select max(sal),job from emp group by job;
注意:分组函数一般都会和group by联合使用,这也是为什么它被称为分组函数的,
并且任何一个分组函数(计求平大小),都是在group by语句执行结束之后才会执行
当一条sql语句没有group by的话,整张表的数据会自成一组
select ename,max(sal),job from emp groupby job;//有结果,但结果没有意义
记住一个规则:当一条语句中有group by的话,select后面只能跟分组函数和参与分组的字段
每个工作岗位的平均薪资
select avg(sal) as avgsal ,job from emp group by job;
多个字段能不能联合起来一块分组?
找出每个部门不同工作岗位的最高薪资
select deptno,job,max(sal) from emp group by deptno,job;
找出每个部门的最高薪资,要求显示薪资大于2900的数据
select deptno,max(sal) from emp group by deptno having max(sal) > 2900;
//这种效率低,建议能够使用where过滤的尽量使用where
select deptno,max(sal) from emp where sal > 2900 group by deptno;
找出每个部门的平均薪资,要求显示薪资大于2000的数据
select deptno,avg(sal) as avgSal from emp group by deptno;
select deptno,avg(sal) as avgSal from emp group by deptno having avg(sal) > 2000;
//这里明显不能使用where 因为先删除了一些sal值,数据不准确
select deptno,avg(sal) from emp where avg(sal) > 2000 group by deptno;
// 错误了。 这种情况只能使用having过滤。
//where后面不能使用分组函数
总结一个完整的DQL语句怎么写?
select .. from .. where ... group by ... having .... order by ....
1.关于查询结果集的去重
select distinct job from emp; //distinct 关键字去除重复记录
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可能还在使用这种语法)DBA:数据库管理员
SQL99语法(比较新的语法)
根据表的连接方式来划分,包括
内连接
等值连接
非等值连接
自连接
外连接
左外连接(左连接)
右外连接(右连接)
全连接 (不讲,很少用)
2.3在表的连接查询方面有一种现象被称为:笛卡尔积现象(笛卡尔乘积现象)
找出每一个员工的部门名称,要求显示员工名和部门名(两张表)
select ename,dname from emp,dept;//这种会出现笛卡尔积现象,
笛卡尔积现象,当两张表进行连接查询的时候,没有任何条件进行限制,最终的查询结果条数是两张表记录条数的乘积
关于表的别名:select e.ename,d.dname from emp e,dept d;
表的别名好处?
1.执行效率高
2.可读性好
2.4怎么避免笛卡尔积现象?当然是加条件进行过滤
思考:避免了笛卡尔积现象,会减少记录的匹配次数吗?
不会,次数还是不变,只不过显示的是有效记录
找出每一个员工的部门名称,要求显示员工名和部门名
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;//SQL92,以后不用
2.5内连接之等值连接:最大特点是:调教是等量关系
查询每个员工的部门名称,要求显示员工名和部门名
SQL92:
select e.ename,d.dname from emp e.dept d where e.detpno = d.deptno;
SQL99(常用):
select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;
(join ... on )
//inner可以省略的,带着inner目的是可读性号一些
select e.ename,d.dname from emp e inner join dept d on e.deptno = d.deptno
语法: ......
A
join
B
on
连接条件
where
.....
SQL99语法结构更清晰一些:表的连接条件和后来的where条件分离了,
2.6内连接之非等值连接:最大特点:连接条件中的关系是非等量关系
找出每个员工的工资等级,要求显示员工名,工资,工资等级
select ename,sal from emp e;
select * from salgrade s;
select
e.ename,e.sal,s.grade
from
emp e
(inner)join
salgrade s
on
e.sal between s.losal and s.hisal
2.7自连接:最大的特点:一张表看做两张表,自己连接自己
找出每个员工的上级领导,要求显示员工名和对应的领导名
select
e1.ename as '员工名',e2.ename as '领导名'
from
emp e1
join
emp e2
on
e1.mgr = e2.empno;
2.8 外连接
什么是外连接,和内连接有什么区别?
内连接:假设A和B表进行连接,使用内连接的话,凡是A表和B表能够匹配上的记录查询出来,这就是内连接,AB两张表没有主副之分,两张表是平等的
外连接:假设A和B表进行连接,使用外连接的话,AB两张表有一张表时主表,一张表是副表,主要查询主表中的数据,捎带查询副表,当副表中的数据没有和主表中的数据匹配上,副表自动模拟出NULL与之匹配
外连接的分类?
左外连接:表示左边的这张表是主表
右外连接:表示右边的这张表时主表
左连接有右连接的写法,右连接也有对应的左连接的写法
找出每个员工的上级领导?(所有的员工必须全部查询出来)
内连接
select
e1.ename,e2.ename
from
emp e1
join
emp e2
on
e1.mgr = e2.empno;
外连接(左外连接)
select
e1.ename as '员工',e2.ename as '领导'
from
emp e1
left(outer) join
emp e2
on
e1.mgr = e2.empno;
//outer可以省略
外连接(右外连接)
select
e1.ename '员工',e2.ename '领导'
from
emp e1
right (outer)join
emp e2
on
e1.mgr = b.empno;
外连接最重要的特点是:主表的数据无条件的全部查询出来
找出那个部门没有员工?
select
d.deptno,d.dname,e.ename
from
dept d
left join
emp e
on
e.deptno = d.deptno
where
e.empno is null;
2.9三张表怎么连接查询?
解释一下:
.....
A
join
B
on
.....
join
C
on
......
表示A表和B表先进行表连接,连接之后A表继续和C表进行连接
找出每一个员工的部门名称以及工资等级(3张表)
select
e.ename,d.dname,s.grade
from
emp e
left join
dept d
on
e.deptno = d.deptno
left join
salgrade s
on
e.sal between s.losal and s.hisal;
找出每个员工的部门名称,工资等级,以及上级领导(4张表)
select
e1.ename,d.dname,s.grade,e2.ename
from
emp e1
join
dept d
on
e1.deptno = d.deptno
join
salgrade s
on
e.sal between s.losal and s.hisal
left join
emp e2
on
e1.mgr = e2.empno;
3.子查询
3.1什么是子查询?子查询都可以出现在哪里?
select语句当中嵌套select语句,被嵌套的select语句就是子查询
子查询可以出现在哪里?
select
..(select)
from
..(select)
where
..(select)
3.2where子句中使用子查询
找出高于平均工资的员工信息
select avg(sal) as avgSal from emp;
select
ename,sal,avg(sal)/*
from
emp
where
sal >(select avg(sal) as avgSal from emp);
3.3from后面嵌套子查询
找出每个部门平均薪水的等级
select deptno,avg(sal) as avgSal from emp group by deptno;
select
t.*,s.grade
from
(select deptno,avg(sal) as avgSal from emp group by deptno) t
left join
salgrade s
on
t.avgSal between s.losal and s.hisal;
找出每个部门平均的薪水等级
1.每个员工的薪水等级
select e.ename,e.deptno,e.sal,s.grade from emp e left join salgrade s on e.sal between s.local and s.hisal
2.每个部门平均的薪水等级
select e.deptno,avg(s.grade) from emp e left join salgrade s on e.sal between s.losal and s.hisal group by e.deptno;
3.4在select后面嵌套子查询
找出每个员工所在的部门名称,要求显示员工名和部门名
select dname,deptno from dept;
select
ename,(select d.dname from dept d where e.deptno = d.deptno) as dname
from
emp e;
4. union(可以将查询结果集相加)
找出工作岗位是SALESMAN和MANAGER的员工?
select ename,job from emp where job = 'SALESMAN' or 'MANAGER';
select ename,job from emp where job in('SALESMAN' ,'MANAGER');
select ename,job from emp where job = 'SALESMAN'
union
select ename,job from emp where jon = 'MANAGER';
两张不相干的表中的数据拼接在一起显示?
select ename from emp
union
select dname from dept; //error
5. limit(重点中的重点,以后分页查询全靠它)
5.1 limit是mysql特有的,其他数据库中没有,不通用,
5.2limit取结果集中的部分数据,这时它的作用就体现出来了
5.3语法机制
limit startIndex,length
起始位置的索引 length表示取几个
取出工资前5名的员工(思路:降序取前5个)
select ename,sal from emp order by sal desc limit 0,5;
5.4limit是sql语句最后执行的一个环节
select 5
from 1
where 2
group by 3
having 4
order by 6
limit 7
5.5找出工资排名在第4到第9的员工
select ename,sal from emp order by sal desc limit 3,6;
5.6通用的标准分页sql?
每页显示3条记录:
第一页 0,3
第二页 3,3
第三页 6,3
第四页 9,3
第五页 12,3
每页显示pageSize条记录:
第pageNo页:(pageNo -1 ) * pageSize,pageSize
pageSize是什么?是每页显示多少条记录
pageNo是什么?显示第几页
java代码{
int pageNo = 2;//页码是2
int pageSize = 10;//每页显示10条
limit (pageNo - 1) * pageSize,pageSize;
}