视频指路
作用:把查询结果去除重复记录。
注意:原表数据不会被修改,只是查询结果去重。
去重需要一个关键字:distinct
例:select distinct job from ...;
distinct 前面不能加其他的字段,也即出现在最前方,此时表示多个字段联合起来去重
定义:从一张表中查询称为单表查询,从多张表中跨表查询称为连接查询
分类:年代分类: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 语法
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 ...;
select e.ename,e.sal,s.grade from emp e inner join salgrade s on e.sal between s.losal ans s.hisal;//条件不是一个等值条件,所以被称为非等值连接
select a.ename as 'worker',b.ename as 'manager' from emp a inner join emp b //技巧:一张表看做两张表 on a.mgr=b.empno;
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 关键字可以省略
外连接的查询结果条数肯定>=内连接的查询结果条数
语法
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;
定义:select语句中嵌套select语句,被嵌套的select语句被称为子查询。
出现位置:select、from、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);
注意: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;
例:找出每个员工的部门名称,要求显示员工名、部门名
select e.name, (select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;
缺陷:一次只能查出一条结果;多了就会报错。
select ename,job from emp where job = 'MANAGE' union select ename,job from emp where job = 'SALESMAN';
union把乘法变加法,减少了匹配次数,效率更高
注意:
概述:limi是将查询结果集的一部分取出来,通常使用在分页查询中
用法:完整:limit startindex ,length(下标从0开始)
缺省:limit length (默认startindex=0)
select ename,sal from emp order by sal desc limit 6;
分页的作用是为了提高用户的体验。
limit 在order by后面执行
属于DDL语句,包括create drop alter
create table 表名( 字段名1 数据类型, default '...' //可以指定默认值 字段名2 数据类型, ...);
表名建议以t_或者 tbl_ 开始 ,可读性强,见名知意
varchar 可变长度字符串,最长255,比较智能,会根据实际长度动态分配空间,节省空间,但速度慢
char 定长字符串 不管实际空间大小,只会分配固定空间,使用不当可能会导致空间浪费,但速度快
int 整数型,最长11
bigint 长整型
float 单精度浮点型
double 双精度浮点型
date 短日期
datetime 长日期
clob:Character Large OBject
字符大对象,最多可以储存4G字符串,如简介,文章,超过255个字符都要使用CLOB字符大对象来储存
blob:二进制大对象 Binary Large OBject
专门用来储存图片、声音、视频等流媒体数据
往BLOB类型的字段上插入数据时,必须使用IO流
drop table 表名;
当这张表不存在的时候会报错
drop table if exists 表名;
更加健壮
语法
insert into 表名(字段名1,字段名2,字段名3 ...) values(值1,值2,值3 ...)
字段名和值要一一对应,数量要对应,数据类型也要对应。
注意:insert语句执行成功了必定会增加一条记录,如果没有指定值就为NULL
如果字段名全省略了,后面的值就要全部填充
在表中插入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 …
date是短日期,只包含年月日信息;
datetime是长日期,包含年月日时分秒信息。
MySQL默认长日期格式:%Y-%m-%d %h:%i:%s
在MySQL中获取系统当前时间函数:now() ,获取长日期
语法格式:
update 表名 set 字段名1=值1, 字段名2=值2, ... where 条件;
注意:没有条件限制会导致所有的数据被更新
语法
delete from 表名 where 条件;
注意:没有条件,整张表的数据都会被删除!!