3.4 查询去重(distinct)
作用:把查询结果去除重复记录。
注意:原表数据不会被修改,只是查询结果去重。
去重需要一个关键字:distinct
例:select distinct job from ...;
distinct 前面不能加其他的字段,也即出现在最前方,此时表示多个字段联合起来去重
4 连接查询
定义:从一张表中查询称为单表查询,从多张表中跨表查询称为连接查询
分类:年代分类:SQL92、SQL99(重点)
表连接方式分类:内连接(等值连接、非等值连接、自连接)、
外连接(左外连接(左连接)、右外连接(右连接 ))、
全连接
当两张表进行连接查询时没有条件限制的时候,最终查询结果的条数,是两张表条数的乘积,这种现象被称为笛卡尔积现象。
例:select name,dname from tb1,tb2;
如何避免:连接时加条件。
例:select tb1.name,tb2.dname from tb1,tb2 where tb1.col=tb2.col
说明:最终查询是匹配的条数,但是后台匹配的时候次数没有减少,尽量减少表的连接次数
效率:给表起别名:
select
e.ename,d.dname
from
emp e,dept d
where
e.deptno=d.deptno; //SQL92 语法
4.1 内连接之等值连接
select
e.ename,d.dname
from
emp e,dept d //结构不清晰,表的连接条件和后续的筛选条件混杂不清
where
e.deptno=d.deptno; //SQL92 语法
重点SQL99:
select
e.ename,d.dname
from
emp e
(inner) join //inner 可以省略,但是写了更容易看
dept d //表连接和条件分离了,连接之后需要进一步筛选就继续添加where
on //SQL99 语法
e.deptno=d.deptno //因为是等号,所以被称为等值连接
where
...;
4.2 内连接之非等值连接
select
e.ename,e.sal,s.grade
from
emp e
inner join
salgrade s
on
e.sal between s.losal ans s.hisal;//条件不是一个等值条件,所以被称为非等值连接
4.3 内连接之自连接
select
a.ename as 'worker',b.ename as 'manager'
from
emp a
inner join
emp b //技巧:一张表看做两张表
on
a.mgr=b.empno;
4.4 外连接
select
e.ename,d.dname
from
emp e
right (outer) join
dept d
on
e.deptno = d.deptno;
right:表示将join关键字右边的这张表看成主表,主要是为了将这张表的数据全部查询出来,捎带着关联查询左边的表,如果左边的表没有匹配,则用NULL匹配。
外连接的两张表是有主次关系的,内连接是没有主次关系的。
任意一个右连接都有对应的左连接写法
select
e.ename,d.dname
from
dept d
left (outer) join
emp e
on
e.deptno = d.deptno
outer 关键字可以省略
外连接的查询结果条数肯定>=内连接的查询结果条数
4.5 三(多)张表的连接
语法
select
...
from
a
inner join
b
on
a和b表的连接条件
left join
c
on
a和c表的连接条件
right join
d
on
a和d表的连接条件
一条SQL语句内连接和外连接可以混合,都可以出现
例:找出每一个员工的部门名称以及工资等级,还有上级领导,要求显示员工名、领导名、部门名、薪资、薪资等级。
select
e.ename,e.sal,d.dname,s.grade,l.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 l
on
e.mgr = l.empno;
5 子查询
定义:select语句中嵌套select语句,被嵌套的select语句被称为子查询。
出现位置:select、from、where后面
5.1 where中的子查询
例:找出比最低工资高的员工的姓名和工资
注意:where中不能用min()
实现思路:
第一步:查询最低工资select min(sal) from emp;
第二步:找出大于最低工资的select ename,sal from emp where sal>800
第三步:合并
select ename,sal from emp where sal>(select min(sal) from emp);
5.2 from中的子查询
注意:from后面的子查询,可以将子查询的查询结果当做一个临时表 (技巧)
例:找出每个岗位的平均工资的薪资等级
select
t.*,s.grade
from
(select job,avg(sal) as avgsal from emp group by job) t //起别名很重要!
join
salgrade s
on
t.avgsal between s.losal and s.hisal;
5.3 select中的子查询
例:找出每个员工的部门名称,要求显示员工名、部门名
select
e.name, (select d.dname from dept d where e.deptno = d.deptno) as dname
from
emp e;
缺陷:一次只能查出一条结果;多了就会报错。
6 union的用法
select ename,job from emp where job = 'MANAGE'
union
select ename,job from emp where job = 'SALESMAN';
union把乘法变加法,减少了匹配次数,效率更高
注意:
- union在进行结果集合并的时候要求两个结果集的列数相同,否则报错
- mysql中允许不同的数据类型拼接,但是Oracle不允许
7 limit
概述:limi是将查询结果集的一部分取出来,通常使用在分页查询中
用法:完整:limit startindex ,length(下标从0开始)
缺省:limit length (默认startindex=0)
select ename,sal
from emp
order by sal desc
limit 6;
分页的作用是为了提高用户的体验。
limit 在order by后面执行
8 表
8.1 表的创建
属于DDL语句,包括create drop alter
create table 表名(
字段名1 数据类型, default '...' //可以指定默认值
字段名2 数据类型,
...);
表名建议以t_或者 tbl_ 开始 ,可读性强,见名知意
8.2 mysql中的数据类型:
-
varchar 可变长度字符串,最长255,比较智能,会根据实际长度动态分配空间,节省空间,但速度慢
-
char 定长字符串 不管实际空间大小,只会分配固定空间,使用不当可能会导致空间浪费,但速度快
-
int 整数型,最长11
-
bigint 长整型
-
float 单精度浮点型
-
double 双精度浮点型
-
date 短日期
-
datetime 长日期
-
clob:Character Large OBject
字符大对象,最多可以储存4G字符串,如简介,文章,超过255个字符都要使用CLOB字符大对象来储存
-
blob:二进制大对象 Binary Large OBject
专门用来储存图片、声音、视频等流媒体数据
往BLOB类型的字段上插入数据时,必须使用IO流
8.3 删除表
drop table 表名;
当这张表不存在的时候会报错
drop table if exists 表名;
更加健壮
8.4 插入表(DML)
语法
insert into 表名(字段名1,字段名2,字段名3 ...) values(值1,值2,值3 ...)
字段名和值要一一对应,数量要对应,数据类型也要对应。
注意:insert语句执行成功了必定会增加一条记录,如果没有指定值就为NULL
如果字段名全省略了,后面的值就要全部填充
8.5 insert 插入日期
在表中插入date数据类型时:
如果是 ‘YYYY-MM-DD’类型的字符串,mysql会自动做str_to_date函数。
否则要写 str_to_date(‘日期字符串’ ,‘日期格式’ )函数
mysql的日期格式
%Y 年
%m 月
%d 日
%h 时
%i 分
%s 秒
该函数将varchar类型转换成date类型
展示日期的时候,可以写date_format(date字段名,’日期格式化‘)来展示特定的日期格式
将date类型转换成varchar类型
如果不写,MySQL会自动执行date_format(date字段名,’%Y-%m-%d’)来展示
select date_format(birth,’%Y/%m/%d’) as birth from …
8.6 date和datetime的区别
date是短日期,只包含年月日信息;
datetime是长日期,包含年月日时分秒信息。
MySQL默认长日期格式:%Y-%m-%d %h:%i:%s
在MySQL中获取系统当前时间函数:now() ,获取长日期
8.7 修改update(DML)
语法格式:
update 表名 set 字段名1=值1, 字段名2=值2, ... where 条件;
注意:没有条件限制会导致所有的数据被更新
8.8 delete 删除数据(DML)
语法
delete from 表名 where 条件;
注意:没有条件,整张表的数据都会被删除!!