文章目录
distinct：把查询结果去除重复记录
- 注意：原表的数据不会被修改，只是修改结果去重
select distinct job from emp;
- 如果这样写呢：select ename, distinct job from emp;
- 我们发现，这样编写是语法错误的
- 结论：distinct只能出现在所有字段的最前方
如果distinct出现在job和deptno两个字段之前，则表示两个联合起来去重
select distinct job, deptno from emp;
统计一下工作岗位的数量：select count(distinct job) from emp;
连接查询
基本介绍
从一张表中单独查询，称为单表查询
而emp表和deptiao联合起来查询数据，从emp表中取员工名字，从dept表中取部门名字
这种跨表查询，多张表联合起来查询数据，被称为连接查询
连接查询的分类
- 根据语法的年代分类：
SQL92：1992年的时候出现的语法
SQL99：1999年的时候出现的语法
- 根据表连接的方式分类：
内连接：
等值连接
非等值连接
子链接
外连接：
左外连接（左连接）
右外连接（右连接）
全连接（不讲）
笛卡尔积现象
基本介绍+引入
- 如果当两张表进行连接查询时，没有任何条件的限制会发现什么现象？
- 案例：查询每个员工所在部门名称：select ename, dname from emp, dept;//error
部分数据：
14 * 4 = 56
- 我们发现，当两张表进行连接查询，没有任何条件限制的时候，最终查询结果条数，是两张表条数的乘积。这种现象就被称为“笛卡尔积现象”
如何避免笛卡尔积现象
- 连接时加上条件，只有满足这个条件的记录才会被筛选出来
select ename, dname from emp, dept where emp.deptno = dpet.deptno;
- 注意：表起别名很重要，这能大大提高我们的效率
即select e.ename, d.dname from emp e, dept d where e.deptno = d.deptno;//这是SQL92的语法
- 思考：最终查询的结果条数是14条，但是在匹配的过程中，匹配的次数有减少吗？
- 没有，还是56次，只不过进行了四选一，次数并没有减少
注意：通过笛卡尔积现象得出，表的连接次数越多效率越低，尽量避免表的连接次数
内连接
内连接之等值连接
案例：查询每个员工所在部门名称，显示员工们和部门名，用emp e 和 dept d表进行连接，条件是：e.deptno = d.deptno;
SQL92语法：select e.ename, d.dname from emp e, dept d where e.deptno = d.deptno;
SQL92的缺点：结构不清晰，表的连接条件，和后期进一步筛选的条件，都放到了where后面
SQL99语法：select e.ename, d.dname from emp e (inner) join dept d on e.deptno = d.deptno;
//最后的e.deptno = d.deptno; //条件是等量关系，所以被称为****等值连接
**注意：inner可以省略（带着inner的可读性更好，一眼就能看出来是内连接）
**
SQL99优点：表连接的条件是独立的，连接之后，如果还需要进一步筛选，再往后继续添加where即可
内连接之非等值连接
案例：找出每个员工的薪资等级，要求显示员工们、薪资、薪资等级
select e.name, e.sal, s.grade from emp e (inner) join salgrade s on e.sal between s.losal and s.hisal;
inner可写可不写
条件不是一个等量关系，所以被称为非等值连接
内连接之自连接
案例：查询员工的上级领导，要求显示员工们和对应的领导名
技巧：一张表看成两张表
- 然后将其结合
select a.ename as ‘员工名’, b.ename as ‘领导名’ from emp a join emp b on a.mgr = b.empno;//员工的领导编号 = 领导的员工编号
- 13条记录，没有KING
内连接：（A和B连接，AB两张表没有主次关系，是平等的）
select e.ename, d.dname from emp e join dept d on e.deptno = d.deptno;
内连接特点：将能够匹配条件的数据查询出来
外连接
右外连接
select e.ename, d.dname from emp e right (outer) join dept d on e.deptno = d.deptno;
- outer是可以省略的，带着可读性强
- right：表示将join关键字右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表。
- 在外连接当中，两张表连接，产生了主次关系
左外连接
select e.ename, d.dname from emp e left (outer) join dept d on e.deptno = d.deptno;
- outer是可以省略的，带着可读性强
细节
带有right的是右外连接，又叫做右连接
带有left的是左外连接，又叫做左连接
任何一个右连接都有左连接的写法
任何一个左连接都有右连接的写法
外连接的查询结果条数一定是 >= 内连接的查询结果条数
多表连接
语法：select … from a join b on a和b的连接条件 join c on a和c的连接条件 right join d on a和d的连接条件
- 一条SQL中内连接和外连接可以混用，都可以出现
案例：找出每个员工的部门名称以及工资等级，要求显示员工名、部门名、薪资、薪资等级
select e.ename, e.sal, 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,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
…(select)…
from
…(select)…
where
…(select)…
where子句中的子查询
- 案例：找出比最低工资高的员工姓名和工资
实现思路：
- 查询最低工资是多少
select min(sal) from emp;
- 找出 > 800的
select ename, sal from emp where sal > 800;
- 合并
select ename, sal from emp where sal > (select min(sal) from emp);
from子句中的子查询
- 注意：from后面的子查询，可以将子查询的查询结果当作一张临时表（技巧）
案例：找出每个岗位的平均工资的薪资等级
- 找出每个岗位的平均工资（按照岗位分组求平均值）
select job, avg(sal) from emp group by job;// t表
- 将以上的查询结果当作一张真实存在的表t
select * from salgrade;// s表
- 将t表和s表进行表连接，条件：t表avg(sal) between s.losal and s.hisal;
- 注意：由于join on的执行顺序在group by 前面，因此如果不将avg(sal)重命名，则会发生报错，因为未分组就使用了分组函数
select子句中的子查询（了解）
案例：找出每个员工的部门名称，要求显示员工名、部门名
- 注意：对于select后面的子查询来说，这个子查询一次只能返回一条结果，多余一条，就会报错
- 由于dname有四条数据，使得一次返回多个结果，则报错
Union合并查询结果集
基本介绍
案例：查询工作岗位是MANAGER和SALESMAN的员工
select ename, job from emp where job = ‘MANAGER’ or job = ‘SALESMAN’
- 使用union
select ename, job from emp where job = ‘MANAGER’ union select ename, job from emp where job = ‘SALESMAN’;
union的效率要高一些**，对于表连接来说，每连接一次新表，则匹配的次数满足笛卡尔积，会导致匹配次数成倍的翻，但是union可以减少匹配的次数，在减少匹配次数的情况下，还可以****完成两个结果集的连接**
如：a连接b连接c，a，b，c各10条记录，用表连接的匹配次数就是10*10*10 = 1000
而使用union就是，a连接b一个结果100，a连接c一个结果100，即100+100 = 200
相当于union把乘法变成了加法运算
注意事项
要求两个结果集的列数相同
- select ename, job from emp where job = ‘MANAGER’ union select ename from emp where job = ‘SALESMAN’;
- **结论：**union在进行结果集合并的时候，要求两个结果集的列数相同
结果集合并时列和列的数据类型也要一致
- select ename, job from emp where job = ‘MANAGER’ union select ename, sal from emp where job = ‘SALESMAN’;
limit
基本介绍
limit作用：将查询结果集的一部分取出来，通常使用在分页查询当中
如：百度查询，一页显示10条记录
分页的作用是为了提高用户的体验，因为一次全部都查出来了，用户体验差，可以一页一页翻页看
limit的使用
完整用法：limit startindex, length
- startindex是起始下标，length是长度，起始下标从0开始
缺省用法：limit 5；这个的意思是取前5个
案例：按照薪资降序，取出排名在前5名的员工
- select ename, sal from emp order by sal desc limit 5;
- select ename, sal from emp order by sal desc limit 0, 5;
- 注意：MySQL当中的limit是在order by之后执行的
分页
公式：imit(pageNo - 1) * pageSize, pageSize;
DQL语句大总结
- 执行顺序：
- from
- where
- group by
- having
- select
- order by
- limit…
表的创建（建表）
建表的语法格式
- 建表属于DDL语句，DDL包括：create drop alter
create table 表名(字段1 数据类型，字段名2 数据类型，字段名3 数据类型);
create table 表名 (
字段名1 数据类型，
字段名2 数据类型，
字段名3 数据类型，
);
表名：建议以 t_ 开始，可读性强，见名知意
字段名：见名知意
表名和字段名都属于标识符
MySQL中的数据类型
varchar（最长255）
可变长度的字符串，比较智能，节省空间，会根据实际的数据长度动态分配空间
优点：节省空间
缺点：需要动态分配空间，速度慢
char（最长255）
定长字符串，不管实际的数据长度是多少，分配固定长度的空间去存储数据，因此使用不恰当的时候，可能会导致空间的浪费
优点：不需要动态分配空间，速度快
缺点：使用不当可能会导致空间的浪费
varchar和char的选择
当使用到的是固定长度的字符串，选择char
当使用到的是不同长度的字符串，选择varchar
int（最长11）
- 数字中的整数型，等同于java中的int
bigint
- 数字中的长整型，等同于java中的long
float
- 单精度浮点型数据
double
- 双精度浮点型数据
date
- 短日期类型
datetime
- 长日期类型
clob
字符大对象，最多可以存储4G的字符串
应用于：存储一篇文章，存储一个说明
超过255个字符的都要采用CLOB字符大对象来存储
blob
**二进制大对象，**专门用来存储图片、声音、视频等流媒体数据
往BLOB类型的字段上插入数据的时候，例如插入一个图片、视频等，需要使用IO流
创建一个学生表
- 学号、姓名、年龄、性别、邮箱地址
create table t_student (
no int,
name varchar(32),
sex char(1),
age int(3),
email varchar(255)
);
- 删除表：drop table t_student;
- 注意：如果要删除的这个表不存在的话，上面的语句会报错
- **因此为了健壮，会这样写：**drop table if exists t_student;
插入数据insert（DML）
基本介绍+案例分析
语法格式：insert into 表名(字段名1，字段名2，字段名3…) values (值1，值2，值3)；
注意：字段名和值要一一对应，即数量要对应，数据类型也要对应
- insert into t_student(no, name, sex, age, email) values (1, ‘zhangsan’, ‘m’, 20, ‘zhangsan@123.com’);
- 字段顺序可以打乱，但是后面的值必须与字段名一一对应
insert into t_student(email,name,sex,age,no) values(‘lisi@123.com’,‘lisi’,‘f’,20,2);
- 只要insert执行成功，就会添加一个记录，如果没有给其他字段指定值的话，默认值是NULL
- insert into t_student(name) values (‘wangwu’)
- 问：insert语句中的"字段名"可以省略吗？ == 可以
insert into t_student values(2);//error
- **上述会报错，**因为如果将前面的字段名省略的话，代表全部都写上了，由于一一对应原则，因此后面的值都要写上
- insert into t_student values (2, ‘lisi’, ‘f’, 20, ‘lisi@123.com’);
insert插入日期
数字格式化：format
- **语法：**format(数字，‘格式’)
select ename, format(sal, ‘$999,999’) as sal from emp;
str_to_date：将字符串varchar类型转换成date类型
- **数据库中有一条命名规范：**所有的标识符都是全部小写，单词和单词之间使用下划线进行衔接
- insert into t_user(id, name, birth) values (1, ‘zhangsan’, ‘01-10-1990’);//1990年10月1日
- **error了，**原因就是类型不匹配，因为数据库birth是date类型，而这里是一个字符串varchar类型
- 解决方案：使用str_to_date函数进行类型转换
语法格式：str_to_date(‘字符串日期’, ‘日期格式’)；
MySQL的日期格式：
| %Y | 年 |
|---|---|
| %m | 月 |
| %d | 日 |
| %h | 时 |
| %i | 分 |
| %s | 秒 |
insert into t_user(id,name,birth) values(1, ‘zhangsan’, str_to_date(‘01-10-1990’,‘%d-%m-%Y’));
- 总结：str_to_date函数可以把字符串varchar转换成日期date类型数据，通常使用在插入insert方面，因为插入的时候需要一个日期类型的数据，需要通过该函数将字符串转换成date。
注意：如果提供的日期字符串的格式是 %Y-%m-%d，那么就需要这个函数了
因为默认的就是这样子的格式，会自动转换
date_format：将date类型转换成具有一定格式的varchar字符串类型
语法：date_format(日期类型数据，‘日期格式’)；
这个函数通常使用在查询日期方面。设置展示的日期格式
- 另外，date_format函数也有默认的日期格式化，会自动将数据库中的date类型转换成varchar类型，采用的是 %Y-%m-%d
- 注意：能够在DOS命令行窗口下显示的都是字符串类型
date和datetime两个类型的区别
date是短日期：只包括年月日信息
datetime是长日期：包括年月日时分秒信息
- 短日期默认格式：%Y’-%m-%d
- 长日期默认格式：%Y-%m-%d %h:%i:%s
now()函数：获取系统当前的时间，并且带有时分秒，是datetime类型
修改update（DML）
语法：update 表名 set 字段名1 = 值1，字段名2 = 值2，字段名3 = 值3 … where 条件
注意：如果说没有条件限制的话会导致所有的数据全部更新
- 不加限制条件，更新所有
删除数据delete（DML）
语法：delete from 表名 where 条件；
注意：如果没有条件，会导致整张表的数据全部删除
- 删除单个数据
- 删除所有数据
扫一扫
622
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
