三天数据库——02
1、关于查询结果去重
案例:查询在职员工的所有工作岗位
select distinct job from emp ;
+-----------+
| job |
+-----------+
| CLERK |
| SALESMAN |
| MANAGER |
| ANALYST |
| PRESIDENT |
+-----------+
案例:查询不同部门的不同工作岗位
select deptno, distinct job from emp;
select deptno, distinct job from emp;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct job from emp' at line 1
结果错误!
原因: distinct只能出现在所有字段的最前面。
修改:
select distinct deptno,job from emp;
+--------+-----------+
| deptno | job |
+--------+-----------+
| 20 | CLERK |
| 30 | SALESMAN |
| 20 | MANAGER |
| 30 | MANAGER |
| 10 | MANAGER |
| 20 | ANALYST |
| 10 | PRESIDENT |
| 30 | CLERK |
| 10 | CLERK |
+--------+-----------+
案例:统计岗位的数量
select count(distinct job) from emp;
+---------------------+
| count(distinct job) |
+---------------------+
| 5 |
+---------------------+
2、连接查询
2.1、什么是连接查询
在实际开发中,都不是从单表查询数据,一般都是从多表查询,取出最终结果。
在实际开发中,一般一个业务都会对应多张表,比如:学生和班级。
2.2、连接查询的分类
根据语法出现的年代来划分,包括
SQL92(一些老的DBA可能还在使用这种语法,DBA:数据库管理员)
SQL99(比较新的语法)
根据表的连接方式来划分,包括:
内连接:
等值连接
非等值连接
自连接
外连接:
左外连接
右外连接
全连接(很少用)
2.3、笛卡尔积现象
在表的连接查询方面一种现象被称为:笛卡尔积现象(笛卡尔乘积现象)
案例:找出每个员工的部门名称,要求显示员工名和部门名
select ename,dname from emp,dept;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | ACCOUNTING |
| SMITH | RESEARCH |
| SMITH | SALES |
+--------+------------+
56 rows in set (0.12 sec)
笛卡尔积:当两张表进行连接查询的时候,没有任何条件限制,最终查询结果条数是两张表记录条数的乘积
关于表的别名:
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,以后不用
select e.ename , d.dname
from emp e join dept d on e.deptno = d.deptno;//SQL99
2.5、内连接中的等值连接
最大的特点:条件是等量关系
案例:查询每个员工的部门名称,要求显示员工名和部门名
select e.ename , d.dname
from emp e join dept d on e.deptno = d.deptno;//SQL99
//inner可以省略,带着inner目的是可读性更好
select
e.ename,d.dname
from
emp e
inner join
dept d
on
e.deptno = d.deptno;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
语法:
…
A
join
B
on
连接条件
where
…
SQL99的语法结构更清晰一些,表的连接条件和后来的where条件分离
2.6、内连接中的非等值连接
案例:找出每个员工的工资等级,要求显示员工名、工资、工资等级
select e.ename , sal ,s.grade level_sal from emp e join salgrade s on e.sal between s.losal and hisal;
+--------+---------+-----------+
| ename | sal | level_sal |
+--------+---------+-----------+
| SMITH | 800.00 | 1 |
| ALLEN | 1600.00 | 3 |
| WARD | 1250.00 | 2 |
| JONES | 2975.00 | 4 |
| MARTIN | 1250.00 | 2 |
| BLAKE | 2850.00 | 4 |
| CLARK | 2450.00 | 4 |
| SCOTT | 3000.00 | 4 |
| KING | 5000.00 | 5 |
| TURNER | 1500.00 | 3 |
| ADAMS | 1100.00 | 1 |
| JAMES | 950.00 | 1 |
| FORD | 3000.00 | 4 |
| MILLER | 1300.00 | 2 |
+--------+---------+-----------+
2.7、自连接
自连接:最大的特点就是:一张表看作是两张表。自己与自己相连。
案例:找出每个员工的上级领导,要求显示员工名和对应的领导名
select e.ename name ,t.ename as manager
from emp e join emp t on e.mgr = t.empno;
+--------+---------+
| name | manager |
+--------+---------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+--------+---------+
13 rows in set (0.00 sec)
对结果的分析:结果的记录条数显示为13条,但是在职员工为14个,观察emp表可见,KING的上级为NULL,结合内连接的特点:没有匹配的记录会被舍去。
2.8、外连接
什么是外连接?和内连接有什么区别
内连接:A和B表进行连接,使用内连接,凡是A表和B表能够匹配上的记录查询出来,这就是内连接
外连接:A和B表进行连接,使用外连接的话,AB两张表有一张表是主表,另一张表是副表,主要查询主表中的数据,捎带查询副表,当副表中的数据没有匹配上,副表会自动模拟一个NULL与之匹配
外连接的分类:
左外连接(左连接):表示左边的这张表是主表
右外连接(右连接):表示右边的这张表是主表
左连接有左连接的写法,右连接也有对应的右连接的写法
案例:找出所有员工的上级领导(没有置空)
使用外连接就会解决上个案例中的数据条数缺少问题
select e.ename name ,t.ename as manager
from emp e left join emp t on e.mgr = t.empno;
+--------+---------+
| name | manager |
+--------+---------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| KING | NULL |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+--------+---------+
找出哪个部门没有员工
select d.deptno from dept d left join (select distinct deptno from emp) e on d.deptno = e.deptno where e.deptno is null;
+--------+
| deptno |
+--------+
| 40 |
+--------+
2.9、三张表怎么连接查询
案例:找出每一个员工的部门名称以及工资等级
select e.ename,d.dname,s.grade level_sal
from emp e
join dept d
join salgrade s
on e.deptno = d.deptno and e.sal between s.losal and s.hisal;
+--------+------------+-----------+
| ename | dname | level_sal |
+--------+------------+-----------+
| SMITH | RESEARCH | 1 |
| ALLEN | SALES | 3 |
| WARD | SALES | 2 |
| JONES | RESEARCH | 4 |
| MARTIN | SALES | 2 |
| BLAKE | SALES | 4 |
| CLARK | ACCOUNTING | 4 |
| SCOTT | RESEARCH | 4 |
| KING | ACCOUNTING | 5 |
| TURNER | SALES | 3 |
| ADAMS | RESEARCH | 1 |
| JAMES | SALES | 1 |
| FORD | RESEARCH | 4 |
| MILLER | ACCOUNTING | 2 |
+--------+------------+-----------+
注意:
…
A
join
B
join
C
on
…
表示A表先和B表进行表连接,连接之后A表继续和C表进行连接
案例:找出每个员工的部门名称、工资等级、以及上级领导
select e.ename , s.grade,d.dname,t.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 t
on e.mgr = t.empno;
+--------+-------+------------+-------+
| ename | grade | dname | ename |
+--------+-------+------------+-------+
| SMITH | 1 | RESEARCH | FORD |
| ALLEN | 3 | SALES | BLAKE |
| WARD | 2 | SALES | BLAKE |
| JONES | 4 | RESEARCH | KING |
| MARTIN | 2 | SALES | BLAKE |
| BLAKE | 4 | SALES | KING |
| CLARK | 4 | ACCOUNTING | KING |
| SCOTT | 4 | RESEARCH | JONES |
| KING | 5 | ACCOUNTING | NULL |
| TURNER | 3 | SALES | BLAKE |
| ADAMS | 1 | RESEARCH | SCOTT |
| JAMES | 1 | SALES | BLAKE |
| FORD | 4 | RESEARCH | JONES |
| MILLER | 2 | ACCOUNTING | CLARK |
+--------+-------+------------+-------+
3、子查询
3.1、什么是子查询
select语句中嵌套select语句,被嵌套的select语句叫做子查询
子查询可以出现在哪里?
select
…(select)
from
…(select)
where
…(select)
3.2、where子句中使用子查询
案例:找出高于平均薪资的员工信息
select ename , sal from emp where sal > (select avg(sal) from emp);
+-------+---------+
| ename | sal |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
+-------+---------+
3.3、from后面嵌套子查询
案例:找出每个部门的平均薪水的薪资等级
第一步:找出每个部门的平均薪水
select avg(sal) avgsal,deptno from emp group by deptno
第二步:将以上的查询结果当作临时表,让d表和salgrade表连接
select d.deptno , s.grade
from
(select avg(sal) avgsal,deptno from emp group by deptno) d
join
salgrade s
on
d.avgsal between s.losal and s.hisal;
+--------+-------+
| deptno | grade |
+--------+-------+
| 20 | 4 |
| 30 | 3 |
| 10 | 4 |
+--------+-------+
案例:找出每个部门平均的薪水等级
select deptno , avg(grade) from (select e.deptno deptno, s.grade grade from emp e join salgrade s on e.sal between s.losal and s.hisal) t group by t.deptno;
+--------+------------+
| deptno | avg(grade) |
+--------+------------+
| 20 | 2.8000 |
| 30 | 2.5000 |
| 10 | 3.6667 |
+--------+------------+
3,4、在select后面嵌套子查询
案例:找出每个员工所在的部门名称,要求显示员工名和部门名
select e.ename ,(select d.dname from dept d where e.deptno = d.deptno ) as dname from emp e;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
4、union(可以将查询结果集相加)
案例:找出工作岗位是SALASMAN和MANAGER的员工
第一种:where子句
select ename,job from emp where job = 'SALESMAN' or job = 'MANAGER';
+--------+----------+
| ename | job |
+--------+----------+
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| JONES | MANAGER |
| MARTIN | SALESMAN |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| TURNER | SALESMAN |
+--------+----------+
第二种:union将两个查询结果集相加
select ename,job from emp where job = 'SALESMAN' union select ename,job from emp where job = 'MANAGER';
+--------+----------+
| ename | job |
+--------+----------+
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
| JONES | MANAGER |
| BLAKE | MANAGER |
| CLARK | MANAGER |
+--------+----------+
第三种:使用in在SALESMAN和MANAGE集合中选择
select ename , job from emp where job in ('SALESMAN','MANAGER');
+--------+----------+
| ename | job |
+--------+----------+
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| JONES | MANAGER |
| MARTIN | SALESMAN |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| TURNER | SALESMAN |
+--------+----------+
两个不相干的表拼在一起显示
两个select语句选择的列数必须一样
5、limit(重点中的重点,以后分页查询都靠它)
5.1、limit是MySQL特有的,其他数据库没有的,不通用
5.2、limit取结果集的部分数据,这是它的作用
5.3、语法机制
limit startindex,length
startindex 表示起始位置
length 表示几个
案例:取出工资降序排列
第一步:按照工资降序排列
select ename,sal from emp order by sal desc;
+--------+---------+
| ename | sal |
+--------+---------+
| KING | 5000.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| SMITH | 800.00 |
+--------+---------+
第二步:取出前五个
select ename,sal from emp order by sal desc limit 0,5;
select ename,sal from emp order by sal desc limit 5;
+-------+---------+
| ename | sal |
+-------+---------+
| KING | 5000.00 |
| FORD | 3000.00 |
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
5.4、limit是SQL语句中最后执行的一个环节
select 5
…
from 1
…
where 2
…
group by 3
…
having 4
…
order by 6
…
limit 7
…
5.5、找出工资在第四名到第九名的员工
select ename,sal from emp order by sal desc limit 3,6;
+--------+---------+
| ename | sal |
+--------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
+--------+---------+
5.6、通用的标准分页SQL
每页显示3条记录:
第一页:0,3
第二页:3,3
第三页:6,3
第四页:9,3
第五页:12,3
每页显示PageSize条记录:
第PageNo页:PageSize*(PageNo-1),PageSize
6、创建表
建表语句:
create table 表名(
字段名1 数据类型(length),
字段名2 数据类型(length),
字段名3 数据类型(length),
…
);
关于MySQL当中字段的数据类型?以下只说常见的
int 整数型(Java中的int)
bigint 长整型(Java中的long)
float 浮点型(Java中的float double)
char 字长字符型(String)
varchar 可变长字符串(StringBuffer\StringBuilder)
date 日期类型(对应Java中的java.sql.Date类型)
BLOB 二进制大对象(存储图片,视频等流媒体信息)Binary Large Object(对应Java中的Object)
CLOB 字符大对象(存储较大文本,比如可以存储4G的字符串)Character Large Object(对应Java中的Object)
char和varchar怎么选择
在实际开发中,当某个字段中的数据长度不发生改变的时候,是定长的时候,例如:性别、生日都是采用char。当一个字段的数据长度不确定的时候,例如:简介、姓名都是采用varchar。
BLOB和CLOB的使用
电影表:t_movie
id(int) | name(varchar) | playtime(date/char) | post(BLOB) | description(CLOB) |
---|---|---|---|---|
1 | 《蜘蛛侠》 | yyyy-MM-dd | 海报(图片) | 描述:balabala |
表名在数据库一般以:t_或者tb_开始
创建学生表
学生信息包括:
create table t_student(
id bigint(16),
name varchar(255),
sex char(1),
birth char(10),
classno int(10)
);
查看表结构:
desc t_student;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | bigint | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | char(10) | YES | | NULL | |
| classno | int | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
7、insert语句插入数据
语法格式:
insert into 表名(字段1,字段2,字段3,…) values (值1,值2,值3,…)
要求:字段的数量和值的数量相同,并且数据类型要对应相同
insert into t_student(id,name,sex,birth,classno) values (1,'zhangsan','1','1999-9-22',1801);
+------+----------+------+-----------+---------+
| id | name | sex | birth | classno |
+------+----------+------+-----------+---------+
| 1 | zhangsan | 1 | 1999-9-22 | 1801 |
+------+----------+------+-----------+---------+
insert into t_student(name,sex,birth,classno) values ('lisi','1','1929-6-12',1801);
+------+----------+------+-----------+---------+
| id | name | sex | birth | classno |
+------+----------+------+-----------+---------+
| 1 | zhangsan | 1 | 1999-9-22 | 1801 |
| NULL | lisi | 1 | 1929-6-12 | 1801 |
+------+----------+------+-----------+---------+
insert into t_student(name) values ('zhangsan');
//除name字段以外,剩下的所有字段自动插入NULL
+------+----------+------+-----------+---------+
| id | name | sex | birth | classno |
+------+----------+------+-----------+---------+
| 1 | zhangsan | 1 | 1999-9-22 | 1801 |
| NULL | lisi | 1 | 1929-6-12 | 1801 |
| NULL | zhangsan | NULL | NULL | NULL |
+------+----------+------+-----------+---------+
注意:insert只要执行成功就会插入一个字段,不能通过insert完成修改
字段可以省略不写,但是后面的values对数量和顺序都有要求
insert into t_student values (12,'lucy','m','2000-8-1','1812032');
一次插入多条数据
insert into t_student
(id,name,sex,birth,classno)
values
('2','eric','f','2001-3-7',1812022),
('32','ammy','m','2002-10-20',1913023);
删除表
drop table if exists t_student;
8、表的复制
语法:
create table 表名 as select语句;
将查询结果当作表创建出来
create table emp1 as select * from emp;
9、将查询结果插到一张表中
insert into dept1 select * from dept;
10、修改数据:update
语法格式:
update 表名 set 字段名1=值1,字段名2=值2,…where 条件;
注意:没有条件整张表数据全部更新。
案例:将t_student的张三的学号改成“1”,性别改成“男”。
update t_student set id=1,sex='男' where name=‘张三’;
11、删除数据
数据格式:
delete from 表名 where 条件;
注意:没有条件全部删除
删除“eric”的那条记录
delete from t_student where name='eric';
12、对于表结构的修改可以用工具完成
增删改查:CRDU操作
13、约束(Constraint)
13.1、什么是约束,常见的约束有哪些
在创建表的时候,可以给表的字段添加响应的约束,添加约束的目的是为了保证表中数据的合法性、有效性、完整性。
常见的约束都有哪些
非空约束(no null) 不能为NULL;
唯一约束(unique) 不能重复
主键约束(primary key) 既不为null,也不能重复
外键约束(foreign key)
检查约束(check) 注意:Oracle数据库有,但是MySQL没有
13.2、非空约束 not null
create table t_user(
id int(10),
username varchar(255) not null,
password varchar(255)
);