2021-11-07

1、子查询?
1.1、什么是子查询?select语句中嵌套select语句,被嵌套的select语句称为子查询。1.2、子查询可以出现在哪里呢?select…(select)from …(select)where…(select).1.3、where子句中的子查询?案例:找出比最低工资高的员工姓名和工资?select ename,sal from emp where sal>(select min(sal) from emp);1.4、from子句中的子查询?注意:from后面的子查询,可以将子查询的查询结果当做一张临时表。案例:找出每个岗位的平均工资的薪资等级。第一步:找出每个岗位的平均工资的薪资等级。(按照岗位分组求平均值)select job,avg(sal) from emp group by job;±----------±------------+| job | avg(sal) |±----------±------------+| ANALYST | 3000.000000 || CLERK | 1037.500000 || MANAGER | 2758.333333 || PRESIDENT | 5000.000000 || SALESMAN | 1400.000000 |±----------±------------+第二步:把以上查询结果当做一张真实存在的表tselect * from salgrade; s表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 join salgrade son t.avgsal between s.losal and s.hisal;2、union合并查询结果集案例:查询工作岗位是MANAGER和SALESMAN的员工?select ename,job from emp where job=‘MANAGER’ or job=‘SALESMAN’;±-------±---------+| ename | job |±-------±---------+| ALLEN | SALESMAN || WARD | SALESMAN || JONES | MANAGER || MARTIN | SALESMAN || BLAKE | MANAGER || CLARK | MANAGER || TURNER | SALESMAN |±-------±---------+7 rows in set (0.04 sec)select ename,job from emp where job=‘MANAGER’ unionselect ename,job from emp where job=‘SALESMAN’;union的效率要高一些。对于表连接来说,每连接一次新表,则匹配的次数满足笛卡儿积,成倍的翻。。。但是union可以减少匹配的次数。在减少匹配次数的情况下,还可以完成两个结果集的拼接。a 连接b 连接 ca 10条记录b 10条记录c 10条记录匹配次数是:1000a 连接b一个结果: 1010–>100次a连接c一个结果: 10*10–>100次使用union的话是:100次+100次=200次。(union把乘法变成加法运算)union在使用的时候有注意事项?select ename,job from emp where job=‘MANAGER’ unionselect ename from emp where job=‘SALESMAN’;//会报错。union在进行结果集合并的时候,要求两个结果集的列数相同。select ename,job from emp where job=‘MANAGER’ unionselect ename,sal from emp where job=‘SALESMAN’;//MYSQL可以,oracle语法严格,不可以,报错。要求结果合并时列和列的数据类型也要相同。
3、limit
3.1、limit是将查询结果集的一部分取出来,通常使用在分页查询中。
百度默认:一页显示10条记录。
分页的作用是为了提高用户的体验,因为一次全部都查出来,用户体验差,
可以一页一页的翻页看。
3.2、limit怎么用呢?
按照薪资降序,取出排名在前5名的员工?
完整用法:limit startIndex,length
startIndex是起始下标,length是长度
起始下标从0开始。
缺省用法: limit 5; 这是取前5
select ename,sal from emp order by sal desc limit 5 //取前5
select ename,sal from emp order by sal desc limit 0,5
3.3、取出工资排名在[3-5]名的员工?
select ename,sal from emp order by sal desc limit 2,3;
2表示起始位置从下标2开始,就是第三条记录。
3表示长度。
3.4、取出工资排名在[5-9]名的员工?
select ename sal from emp order by sal desc limit 4,5;
3.5、分页
每页显示3条记录
第1页:limit 0,3 [0 1 2]
第2页:limit 3,3 [3 4 5]
第3页:limit 6,3 [6 7 8]
第4页:limit 9,3 [9 10 11]
每页显示pageSize条记录
第pageNo页:limit (pageNo-1)*pageSizq,pageSize //记住公式
4、关于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…
5、表的创建(建表)
5.1、建表的语法格式:(建表属于DDL语句,DDL包括: create drop alter)
create table 表名(字段名1 数据类型,字段名2 数据类型,字段名3 数据类型);
表名:建议以t_或者 tbl_开始,可读性强。见名知意。
字段名:见名知意。
表名和字段名都属于标识符。
5.2、关于mysql中的数据类型?
很多数据类型,我们只需要掌握一些常见的数据类型即可。
varchar(最长255) 可变长度的字符串,比较智能,节省空间。会根据实际的数据长度动态的分配空间。
优点:节省空间。
缺点:需要动态分配空间,速度慢。
char (最长255) 定长字符串,不管实际的数据长度是多少。分配固定长度的空间去存储数据。使用不恰当的时候可能会导致空间的浪费。
优点:不需要动态分配空间,速度块。
缺点:使用不当可能会导致空间的浪费。
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 OBject,专门用来存放图片、声音、视频等流媒体数据。
往BLOB类型的字段上插入数据的时候,例如插入一个图片、视频等,你需要使用IO流才行。
t_movie 电影表(专门存储电影信息的)
编号 名字 故事情节 上映日期 时长 海报 类型
no(bigint) name(varchar) history(clob) playtime(date) time(double) image(blob) type(char)

10000 哪吒 … 2019-10-11 2.5 … ‘1’
5.3、创建一个学生表?
学号、姓名、年龄、性别、邮箱地址
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;
5.4、插入数据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(no) values(3);
insert into t_student(name) values(‘wangwu’);
注意:insert语句但凡是执行成功了,那么必然会多一条记录。
没有给其它字段指定值的话,默认值是NULL。
drop table if exists t_student;
create table t_student(
no int,
name varchar(32),
sex char(1) default ‘m’,
age int(3),
email varchar(255)
);
insert into t_student(no) values(1);
insert语句中的"字段名"可以省略吗? 可以
insert into t_student values(2);//错误的
//注意:前面的字段名省略的话,等于都写上了!所以值也要都写上!
insert into t_student values(2,‘lisi’,‘f’,20,‘lisi@123.com’);
查询的时候可以以某个特定的日期格式展示?
date_format
这个函数可以将日期类型转换成特定格式的字符串。
select id,name,date_format(birth,’%m/%d/%Y’) as birth from t_user;
date_format函数怎么用?
date_format(日期类型数据,‘日期格式’)
这个函数通常使用在查询日期方面。设置展示的日期格式。
select id name,birth from t_user;
±-----±-----------+
| name | birth |
±-----±-----------+
| 1 | 1990-10-01 |
| 1 | 1990-10-01 |
| 2 | 1990-10-01 |
±-----±-----------+
3 rows in set (0.00 sec)
以上的SQL语句实际上是进行了默认的日期格式化,自动将
数据库中的date类型转换成varchar类型。
并且采用的格式是mysql默认的日期格式:’%Y-%m-%d’
java中的日期格式?
yyyy-MM-dd HH:mm:ss SSS
5.6、date和datetime两个类型的区别?
date是短日期:只包括年月日信息。
datetime是长日期:包括年月日时分秒信息。
drop table if exists t_user;
create table t_user(
id int,
name varchar(32),
birth date,
create_time datetime
);
id是整数
name是字符串
birth是短日期
create_time是这条记录的创建时间:长日期类型
mysql短日期默认格式:%Y-%m-%d
mysql长日期默认格式:%Y-%m-%d %h:%i:%s
insert into t_user(id,name,birth,create_time) values(2,‘zhangsan’,‘1999-10-1’,‘1990-10-1 15:41:33’);
在mysql当中怎么获取系统当前时间?
now()函数,并且获取的时间带有:时分秒信息！！！是datetime类型的
insert into t_user(id,name,birth,create_time) values(1,‘zhangsan’,‘1999-10-1’,now());
5.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;
update t_user set name=‘sss’,birth=‘2000-10-03’,create_time=now() where id=2;
更新所有?
update t_user set name=‘abc’;
5.8、删除数据delete(DML)
语法格式?
delete from 表名 where 条件;
注意:没有条件,整张表的数据会全部删除!
delete from t_user where id=2;
insert into t_user(id) values(2);
delete from t_user;//删除所有。
6、insert语句可以一次插入多条记录吗?
可以
insert into t_user(id,name,birth,create_time) values(1,‘zs’,‘1980-10-11’,now()),(2,‘zsa’,‘1980-10-12’,now());
语法: insert into t_user(字段名1,字段名2) values(),(),(),();
7、快速创建表?【了解】
create table emp2 as select * from emp;
原理:将一个查询结果当做一张表新建!!!
这个可以完成表的快速复制!!!
表创建出来,同时表的数据也存在了。
create table mytable as select empno ename from emp where job=‘MANAGER’;
8、将查询结果插入到一张表当中?insert相关的【了解】
create table dept_bak as select * from dept;
insert into dept_bak select * from emp;//很少用
9、快速删除表中的数据?
//删除dept_bak表中的数据
delete from dept_bak;//这种删除数据的方式比较慢
delete语句删除数据的原理?(delete属于DML语句)
表中的数据被删除了,但是这个数据在硬盘上的真实存储空间不会被释放！！！
这种删除缺点是:删除效率比较低。
这种删除优点是:支持回滚,后悔了可以再恢复数据！！！
truncate语句删除语句的原理?
这种删除效率比较高,表被一次截断,物理删除。
这种删除缺点:不支持回滚。
这种删除优点:效率高，快。
用法:truncate table dept_bak;(这种操作属于DDL操作)
如果有一个大表非常大,上亿条记录???
删除的时候,使用delete,也许需要执行很久才能删除完,效率较低。
可以选择使用truncate删除。只需要不到1秒钟就可以完全删除。效率较高。
truncate是删除表中的数据,表还在。
删除表操作?
drop table 表名; //这不是删除表中的数据,而是把表删除。
10、约束
10.1、什么是约束?
约束对应的英语单词:constraint
在创建表的时候,我们可以给表中的字段加上一些约束,来保证这个表中数据的完整性、有效性！！！
约束的作用就是为了保证:表中的数据有效！！
10.2、约束包括哪些?
非空约束 : not null
唯一性约束 : unique
主键约束 : primary key(简称PK)
外键约束 ： foreign key(简称FK）
检查约束 : check(mysql不支持,oracle支持)
重点学习4个约束:
not null
unique
primary key
foreign key
10.3、非空约束 : not null
非空约束not null约束的字段不能为NULL。
drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255) not null //not null只有列级约束,叫做列级约束。
);
insert into t_vip(id,name) values(1,‘zhangsan’);
insert into t_vip(id,name) values(1,‘lisi’);
insert into t_vip(id) values(3);//会报错
xxxx.sql这种文件被称为sql脚本文件
sql脚本文件中编写了大量的sql语句。
我们执行sql脚本文件的时候,该文件所有的sql语句全部会被执行。
批量的执行sql语句,可以使用sql语句。
在mysql中怎么执行sql脚本呢?
source +路径;
10.4、唯一性约束:unique
唯一性约束unique约束的字段不能重复,但是可以为null。
drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255) unique,
email varchar(255)
);
insert into t_vip(id,name,email) values(1,‘zhangsan’,‘zhangsan@123.com’);
insert into t_vip(id,name,email) values(2,‘lisi’,‘lisi@123.com’);
insert into t_vip(id,name,email) values(3,‘wangwu’,‘wangwu@123.com’);
insert into t_vip(id,name,email) values(2,‘lisi’,‘lisi@123.com’);//会报错,不能重复。
新需求:name和email两个字段联合起来具有唯一性！！！！
drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255) unique,
email varchar(255) unique
);
这张表这样创建是不符合要求的。
这样创建表示:name具有唯一性,email具有唯一性。各自唯一。
insert into t_vip(id,name,email) values(1,‘zhangsan’,‘zhangsan@123.com’);
insert into t_vip(id,name,email) values(2,‘zhangsan’,‘zhangsan@sina.com’);
这样创建肯定失败,因为’zhangsan’和’zhangsan’重复了
怎么创建这样的表,才能符合新需求呢?
drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255),
email varchar(255),
unique(name,email) //约束没有添加在列的后面,这种约束被称为表级约束。
);
insert into t_vip(id,name,email) values(1,‘zhangsan’,‘zhangsan@123.com’);
insert into t_vip(id,name,email) values(2,‘zhangsan’,‘zhangsan@sina.com’);
name和email两个字段联合起来唯一！！！
什么时候使用表级约束呢?
需要给多个字段联合起来添加某一个约束的时候,需要使用表级约束。
unique和not null可以联合吗?
drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255) not null unique
);
mysql> desc t_vip;
±------±-------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±-------------±-----±----±--------±------+
| id | int | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| email | varchar(255) | NO | PRI | NULL | |
±------±-------------±-----±----±--------±------+
3 rows in set (0.01 sec)
在mysql当中,如果一个字段同时被not null和unique约束的话,
该字段自动变成主键字段。(注意:oracle不一样)
insert into t_vip(id,name) values(1,‘zhangsan’);
insert into t_vip(id,name) values(2,‘zhangsan’); //错误了
insert into t_vip(id) values(2);//报错,name不能为null。