- 把查询结果去除重复记录
注意:原表中的数据不会修改,只是查询结果去重
去重需要使用一个关键字:distinct(不同,鲜明)
Select distinct job from emp;
Select ename,distinct job from emp//这样书写是错误的,distinct只能出现在字段的最前端
Select distinct ename,job from emp//distinct出现在job和depno 2个字段之前,表示2个字段联合起来去重(ename和job都相同才去重)
统计工作岗位的数量?
Select count(distinct job) from emp;//可以出现在分组函数的后面
- 连接查询
2.1什么是连接查询
从一张表里查询的单独查询,叫单表查询
Emp表和dept表联合起来查询数据,从emp表中取员工名字,从dept表中取部门名字,这种叫跨表查询,多张表联合起来查询数据,被称为连接查询。
2.2.连接查询的分类:
根据语法的年代分类:
SQL92:1992年的时候出现的语法
SQL99:1999年出现的语法
我们这里重点学习SQL99
根据表的连接方式分类:
内连接:
等值连接
非等值连接
自连接
外连接:
左外连接(左连接)
右外连接(右连接)
全连接(这里不讲,一般不怎么用)
2.3 当2张表进行连接查询时,没有任何条件限制会发生什么现象?
案例:查询每个员工所在部门名称
Select ename,depno from emp;
//假设14rows
Select * from dept;//结合这2个一起看,但是很麻烦
//假设4rows
2张表连接没有任何条件限制
Select ename,dname from emp,dept;
//4*14=56rows
//当2张表进行连接查询,没有任何条件限制的时候,最终查询结果条数,是2张表条数的乘积,这种现象被称为:笛卡尔积现象。(笛卡尔发现的,这是一个数学现象)
2.4怎么避免笛卡尔积现象?
连接时加条件,满足这个条件的记录被筛选出来。
Select ename,dname from emp,dept where emp.depno=dept.depno
//14rows
思考:最终查询的结果是14条,但是匹配的过程中,匹配的次数减少了吗?
还是56次!只不过进行了四选一。次数没有减少
//表起别名(可省略as)很重要,效率问题
Select e.ename,d.dname from emp e,dept d where e.depno=d.deptno
//SQL92语法
注意:通过笛卡尔积现象得出,表的连接次数越多效率越低,尽量避免表的连接次数
2.5.内连接之等值连接
SQL92:Select ename,dname from emp,dept where emp.depno=dept.depno and 后面加条件
缺点:结构不清晰,表的连接条件,和后期的进一步筛选条件,都放到了where后面
SQL99:Select ename,dname from emp inner join dept on emp.depno=dept.depno
//条件是等号连接,所有以称为等值连接
//内连接 inner可以省略,带着inner可读性更好,一眼就能看出是内连接
优点:表连接的条件是独立的,连接之后,如果还需进一步筛选----添加where
SQL99 语法:
Select 。。。From a join b on a和b连接的条件 where 筛选条件
2.6.内连接值非等值连接
案例:找出每个员工的薪资等级,要求显示员工名,薪资,薪资等级
Select
e.ename,e.sal,s.grade
from
emp e join salgrade s
on
e.sal between s.sal and s.hisal//条件不是一个等值关系,称为非等值连接
2.7内连接之自连接
查询员工的上级领导,要求显示员工名称和对应的领导名
(因为表中只有每个员工对应的领导的empno,要通过empno再去找名字)
技巧:一张表看作2张表!!!
Select
A.ename as ‘员工名’,b.ename as’领导名’
From
Emp a
Join
Emp b //同一个表看作2个表,起2个别名
On
A.mgr=b.empno; //a表的领导编号和b表的编号相同
2.8外连接
内连接: 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右边的这张表看作主表,主要是为了将这张表的数据全部查询出来,捎带查询左边的emp表,在外连接中,2张表产生了主次关系。(符合on条件的dept全部显示出来,没有符合on条件的也显示出来)
//任何一个右连接都有左连接的写法,反过来也成立。
//区别外内连接主要看有没有left 或者right关键字
思考:外连接的查询结果一定>=内连接的查询结果条数?
正确!
查询每一个员工的上机领导,要求显示所有员工的名字和领导名(king也是员工都是没有领导,也要显示)!!
Select
A.ename as ‘员工名’,b.ename as’领导名’
From
Emp a
Left outer Join
Emp b //同一个表看作2个表,起2个别名
On
- mgr=b.empno; //a表的领导编号和b表的编号相同
2.9三张表,四张表怎么连接:
语法:
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,l.name
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 //避免由于king属于员工但是没有领导不显示的问题
emp l
on
e.mgr=l.empno;
- 子查询
3.1什么是子查询?
select 语句中嵌套select 语句,被嵌套的select语句称为子查询
3.2子查询都可以出现在哪里?
select
。。。(select)
from
。。。(select)
where
。。。(select)
3.3.where子句中的子查询
案例:找出比最低工资高的员工姓名和工资;
select
ename,sal
from
emp
where
sal>min(sal)//显然错误,where后不可以跟分组函数
思路: 第一步:查最低工资
第二步:找出大于最低工资的
第三步:合并
select
ename,sal
from
emp
where
sal>(select min(sal) from emp )
3.3 from子句中的子查询
注意:from后面的子查询,可以将子查询的查询结果当作一张临时表(技巧)
案例:找出每个岗位的平均工资的薪资等级
第一步:找出每个岗位的平均工资(安照岗位分组求平均值)
select job,avg(sal) from emp group by job
第二步:克服心理障碍,把上面查询结果当作一张真实存在的表t
t表和s表进行连接,
条件:t表avg(sal) between s.losal and s.hisal ;
select
t.*, s.grade
from
(select job ,avg(sal) as avgsal from emp group by job) t
//分别给avg(sal)和查询的结果取别名avgsal 和t
join
salgrade s
on
t.avgsal between s.losal and s.hisal;
3.4select后面出现的子查询(这个内容不需要掌握,了解即可!!)
找出每个员工的部门名称,要求显示员工名,部门名?
select
e.ename, ( select d.dname from dep d where e.deptno =d.dept no) as dname
from
emp;//可以
select
e.name,e.depno,(select dname from dept) as dname
from
emp e;//错误,子查询返回超过一行
注意:select 后面的子查询,这个子查询只能一次返回一条结果,多余一条,报错
- union 合并查询结果集;
查询工作岗位是manager和salesman的员工?
A:Select ename ,job from emp where job =’manager’ or ‘salesman’;
B:Select ename ,job from emp where job in (’manager’ , ‘salesman’);
C:Select ename ,job from emp where job =’manager’
union
Select ename ,job from emp where job =’salesman’ ;
//union的效率更高,对于表连接来说,每一次连接新表,则匹配的次数满足笛卡尔积,成倍的翻。。。。但是union可以减少匹配的次数,在减少匹配次数的情况下,还可以完成2个结果集的拼接
a连接b 连接c
a十条记录
b十条记录
c十条记录
匹配次数是1000
a连接b一个结果:10*10=100次
a连接c一个结果:10*10=100次
使用union的话:100+100=200次(union把乘法做成加法计算)
//注意事项:union在进行结果集合并的时候,要求2个结果集的列数相等。
select ename,job from emp where job =’manager’
union
select ename from emp where job =’salesman’;//错误
//下面这个MYSQL可以,oracle不行,要求:结果集 合并 时列和列的数据类型也要一致。
select ename ,job from emp where job =‘manager’
union
select ename ,sal from emp where job =‘salesman’;
//job和sal数据类型不一致,oracle报错
- limit(非常重要)!!!
- limit 是将查询的结果集的一部分取出来,通常使用在分页查询中。
如:百度搜索结果:默认一页显示10条记录,分页的作用是为了提高用户体验,因为一次全部查询出来,用户体验差,可以一页一页翻页看
5.2 怎么用?
语法: 完整用法:limit startIndex,length;
//startIndex 是起始下标(默认从0开始),length是长度
缺省用法 :limit 5;取前五;
按照薪资降序,取出排名在前五名的员工?
select
ename, sal
from
emp
order by
sal deasc
limit 5;//取前五
5.3 注意:mysql中limit在order by 之后执行!!!!
5.4取出排名在【3-5】的员工?
select
ename,sal
from
emp
order by
sal desc
limit
2,3;//2代表起始位置从2开始,就是第三条记录,3是一共三条记录
5.5取出工资在【5-9】名的员工
select
ename ,sal
order by
sal desc
limt
4,5;
5.6分页,
每页显示3条记录
第一页:limit 0,3 (0,1,2)
第2页:limit 3,3 (3,4,5)
第3页:limit 5,3 (6,7,8)
//记公式!!
每页显示pageSize记录
第pageNo页: limit (pageNo-1)*pageSize ,pageSize;
public static void main(String【】 args){
//用户提交过来一个页码,以及每页显示的记录条数
int pageNo=5; //第五页
int pageSize=10; //每页10条记录
int startIndex=(pageNo-1)*pageSize;
String sql=”select...limit”+startIndex + ”,” + pageSize;
}
- 关于DQL的大总结
select
...
from
...
where
...
group by
...
having
...
order by
...
limit
...
执行顺序1:from 2.where 3. group by 4.having 5,select 6.order by 7.limit
7建表
7.1 建表的语法格式:(属于DDL语句 DDL包括create drop alter)
create table 表名(字段名1 数据类型1,字段2,数据类型2。。。。。。);(最后一个没有逗号)
表名:建议以t_或者 tbl_ 开始,可读性强,见名知意
字段名:见名知意
7.2关于mysql的数据类型
很多数据类型,我们只需要掌握一些常见的数据类型
//注:后面说的最长都是指长度,如int(3) 表示最多为999
一个中文也是一个长度
varchar(variable char)最长255
可变长度字符串,比较智能,节省空间,会根据实际的数据长度动态分配空间。
优点:节省空间 缺点:需要动态分配空间,速度慢
varchar(10) -传入jack -占用四个
char最长255
定长字符串,不管实际的数据长度是多少,分配固定的长度空间去存储数据。
优点:不需要动态分配空间,速度块 缺点:使用不恰当,可能会导致空间的浪费
char(10) -传入jack -占用10个
varchar 和char应该怎么选?
性别字段你选什么?——char (固定男女)
姓名字段选什么?——varchar
int 最长11
数字中的整数型,等同于Java中的int,
bigint
数字中的长整型,等同于Java中的long,
float
单精度浮点型数据
double
双精度浮点型数据
date
短日期类型
datetime
长日期类型
clob
字符大对象,最多可以存储4G的字符串
比如:存储一篇文章,存储一个说明。超过255个字符的都要用clob字符大对象来存储 Character Large OBject :CLOB
blob
二进制大对象 Binary Large OBj
专门用来存储图片,声音,视频等媒体数据
往BLOB里插入数据的时候,比如插入一个图片,视频等,需要使用IO流才行.
t_movie 电影表
编号 名字 故事情节 上映日期 时长
no(bigint) name(varchar) history(clob) playtime(date) time(double)
海报 类型
image(blob) type(char(1))
7.3创建一个学生表?
学号,姓名,年龄,邮箱地址
create table t_student(
no int,
name varchar(255),
sex char(1),
age int(3),
email varchar(255)
);
删除表:
drop table t_student;//当这张表不存在的时候会报错!
drop table if exists t_student;//当这张表存在时删除,没有也不会报错
(show tables; 展示某databse中的所有表格
desc 。。。 展示表格属性结构)
7.4 插入数据(insert DML语句)
语法格式:
insert into 表名(字段名1,字段名2,。。。。) values(值1,值2,。。。)
注意:字段名和值要一一对应,即数量要对应,数据类型也要对应。
insert into t_student(no,name,sex,age,email) values(1,’zhangsan’,’m’, 20, ‘zhangsan@123’);
//m不能写出man了,因为前面写了char(1) man超过了!即数据类型不对应了;
insert into t_student(email,name,sex,age,no) values('lisi@123','lisi','m',20,2);
//不一定要对应原来表格的顺序,只要前面的字段名和后面的values对应就行
insert into t_student(no) values(3);
select *from t_student;
+------+----------+------+------+--------------+
| no | name | sex | age | email |
+------+----------+------+------+--------------+
| 1 | zhangsan | m | 20 | zhangsan@123 |
| 2 | lisi | m | 20 | lisi@123 |
| 3 | NULL | NULL | NULL | NULL |
+------+----------+------+------+--------------+
insert into t_student(name) values(‘wangwu’);
mysql> select *from t_student;
+------+----------+------+------+--------------+
| no | name | sex | age | email |
+------+----------+------+------+--------------+
| 1 | zhangsan | m | 20 | zhangsan@123 |
| 2 | lisi | m | 20 | lisi@123 |
| 3 | NULL | NULL | NULL | NULL |
| NULL | wangwu | NULL | NULL | NULL |
+------+----------+------+------+--------------+
4 rows in set (0.00 sec)
注意:insert 语句但凡执行成功了,那么必然会多一条记录,没有给其他字段值的话,默认值null;此时就不能在null里插入数据了,只能修改;
drop table if exists t_student;//删除表
mysql> create table t_student(
-> no int,
-> name varchar(32)
-> ,
-> sex char(1) default 'm',
-> age int(3),
-> email varchar(255));
Query OK, 0 rows affected (0.00 sec)
//再重建一个表,修改默认值
desc t_student;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| no | int(11) | YES | | NULL | |
| name | varchar(32) | YES | | NULL | |
| sex | char(1) | YES | | m | |
| age | int(3) | YES | | NULL | |
| email | varchar(255) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
insert into t_student(no) values(1);
mysql> select * from t_student;
+------+------+------+------+-------+
| no | name | sex | age | email |
+------+------+------+------+-------+
| 1 | NULL | m | NULL | NULL |
+------+------+------+------+-------+
1 row in set (0.00 sec)
//建表时可以指定默认值default
insert 语句中的字段名可以省略吗? 可以
insert into t_student() values (2); //错误
//注意,前面的字段名省略的话,等于都写上了,所以值也要都写上!!
insert into t_student() values(2,'lisi','f',20,'lisi@123');
select *from t_student;
+------+------+------+------+----------+
| no | name | sex | age | email |
+------+------+------+------+----------+
| 1 | NULL | m | NULL | NULL |
| 2 | lisi | f | 20 | lisi@123 |
+------+------+------+------+----------+
7.5 insert 插入日期
数字格式化 format
格式化数字:format(数字 ,’格式’);
select ename , format(sal,’$999,999) as sal from emp;
//把sal换为带逗号的不含小数点的形式
str_to_date:将字符串varchar类型转换为data类型
data_format:将date类型转换为具有一定格式的varchar类型
create table t_user(
id int,
name varchar(32),
birth date//生日可以使用date类型
)
create table t_user(
id int,
name varchar(32),
birth char(10) //生日也可以使用char类型 22-09-2000 10个字符
)
注意:数据库中的有一条命名规范:
所有的标识符都是全部小写,单词和单词之间使用下划线连接
desc t_user;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(32) | YES | | NULL | |
| birth | date | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
插入数据?
mysql> insert into t_user(id,name,birth) values(1,'zhangsan',’22-09-2000’);
//出问题了,原因是类型不匹配,数据库birth是date类型,这里给了一个单引号意思为varchar类型,
怎么办?
可以使用str_to_date将字符串转换为日期类型date
语法格式:str_to_date(‘字符串日期’,’日期格式’)
mysql的日期格式:
%Y 年 大写
%m月
%d 日
%h 时
%i 分
%s 秒
insert into t_user(id,name,birth) values(1,'zhangsan',str_to_date('22-09-2000','%d-%m-%Y'));
str_to_date 函数可以将字符串varchar转换为日期达特类型数据,通常需要使用在插入insert方面,因为插入的时候需要一个日期类型的数据,需要通过该函数将字符串转换为date
排查问题,发现str_to_date(date,format)函数的第一个参数不能是空字符串,反而为null时并不影响。
解决方案:
STR_TO_DATE(if(date=’’, null , date), format)
好消息?
如果你提供的函数是这个格式: %Y-%m-%d
str_to_date函数就不需要了。
insert into t_user(id,name,birth) values(2,'lisi',’2000-09-22’);
查询的时候可以以某个特定的日期类型显示吗?
date-format 可以将日期类型以特定格式的字符串
select id,name,date_format(birth,'%m/%d/%Y') as birth from t_user;
+------+----------+------------+
| id | name | birth |
+------+----------+------------+
| 1 | zhangsan | 09/22/2000 |
| 2 | lisi | 09/22/2000 |
+------+----------+------------+
date_fromat 函数怎么用?
date_formate(日期类型数据,’日期格式’)
通常使用在查询日期方面,设置显示日期的格式
select id,name,birth from t_user;
+------+----------+------------+
| id | name | birth |
+------+----------+------------+
| 1 | zhangsan | 2000-09-22 |
| 2 | lisi | 2000-09-22 |
+------+----------+------------+
以上的SQL语句实际上进行了默认的日期格式化,自动将数据库中的数据转换为varchar类型,并且采用Mysql默认的格式:’%Y-%m-%d’
java中的日期格式: YYYY-MM-dd HH:mm:ss SSS
7.6 date和datetime的区别
date 是短日期,只包括年月日信息,
datetime是长日期,包括年月日时分秒信息
drop table if exists t_user;
mysql> create table t_user(
-> id int,
-> name varchar(32),
-> birth date, //短日期
-> create_date datetime);//长日期
//mysql短日期默认格式:%Y-%m-%d
长日期默认格式:%Y-%m-%d %h:%i:%s
insert into t_user(id,name,birth,create_date) values(1,'zhangsan','1999-10-01','2022-2-22 14:22:50');
select * from t_user;
+------+----------+------------+---------------------+
| id | name | birth | create_date |
+------+----------+------------+---------------------+
| 1 | zhangsan | 1999-10-01 | 2022-02-22 14:22:50 |
+------+----------+------------+---------------------+
在mysql中怎么获取系统当前的时间?
now();并且获取的时间带有时分秒信息,是datetime类型的;
insert into t_user(id,name,birth,create_date) values(1,'zhangsan','1999-10-01',now());
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_user;
+------+----------+------------+---------------------+
| id | name | birth | create_date |
+------+----------+------------+---------------------+
| 1 | zhangsan | 1999-10-01 | 2022-02-22 14:22:50 |
| 1 | zhangsan | 1999-10-01 | 2022-02-22 14:25:59 |
+------+----------+------------+---------------------+
7.7.修改 update(DML)
语法格式:update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3.。。。where 条件;
注意没有条件限制会导致数据全部更新!
update t_user set name='jack',birth='2000-10-02' where id=1;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from t_user;
+------+------+------------+---------------------+
| id | name | birth | create_date |
+------+------+------------+---------------------+
| 1 | jack | 2000-10-02 | 2022-02-22 14:22:50 |
| 1 | jack | 2000-10-02 | 2022-02-22 14:25:59 |
+------+------+------------+---------------------+
更新所有“??
select t_user set name =’abc’;//不加条件就更新所有!!!
7.8 删除数据 delete(DML)
语法格式:
delete from 表名 where 条件。
注意没有条件,整张表会全部删除!!!!
delete from t_user where id=2;