一、连接查询

什么是连接查询？从一张表中单独查询，称为单表查询。emp表和dept表联合起来查询数据，这种跨表查询，多张表联合起来查询数据，被称为连接查询。

sql99的语法：

select ...
from a
join b
on a和b的连接条件
1
2
3
4

连接查询的分类：
1、内连接：

• 等值连接
• 非等值连接
• 自连接

2、外连接：

• 左外连接（左连接）
• 右外连接（右连接）

如何避免笛卡尔积现象：
连接时加条件，满足这个条件的记录被筛选出来。

• 通过笛卡尔积现象得出，表的连接次数越多，效率越低，要尽量避免表的连接次数。

select e.ename,d.dname
from emp e,dapt d
where e.deptno=d.deptno;
1
2
3

1、内连接之等值连接

查询每个员工所在的部门名称。显示员工名和部门名：

sql92的语法：
缺点：结构不清晰，表的连接条件，和后期进一步筛选的条件，都放在where后面。

select e.ename,d.dname
from emp e,dept d
where e.deptno=d.deptno;
1
2
3

sql99的语法：
优点：表的连接条件是独立的，连接之后，如果还需要进一步筛选，再往后继续添加where

select e.ename,d.dname
from emp e
inner join dept d
on e.deptno=d.deptno;
1
2
3
4

join前面的inner可以省略。

2、内连接之非等值连接

找出每个员工的薪资等级，要求显示员工名，薪资，薪资等级：

select e.ename,e.sal,s.grade
from emp e
join salgrade s
on e.sal between s.losal and s.hisal;
1
2
3
4

3、内连接之自连接

内连接中的自连接：一张表看成两张表。

查询员工的上级领导，要求显示员工名和对应的领导名：

select a.ename as '员工名'，b.ename as '领导名'
from emp a 
join emp b
on a.mgr=b.empno;
1
2
3
4

4、外连接

内连接的特点：完全能够匹配上这个条件的数据查询出来。
外连接的特点：在外连接当中，两张表连接，产生了主次关系。

带有right的连接是右外连接，又叫右连接。
带有left的连接是左外连接，又叫左连接。
任何一个右连接都有左连接的写法。
任何一个左连接都有右连接的写法。

外连接的查询结果条数一定 >= 内连接的查询结果条数。

除了将e表和d表匹配上的查出来之外，还要将d表没有匹配上的也查出来：

select e.ename,d.dname
from emp e
right join dept d
on e.deptno=d.deptno;
1
2
3
4

right join的right代表什么：表示将join关键字右边这张表看成主表，主要是为了将这张表的数据全部查询出来，顺便查询左边的这张表。

5、多张表连接

语法：

select ...
from a
join b
on a和b的连接条件
join c
on a和c的连接条件
join d
on a和d的连接条件
1
2
3
4
5
6
7
8

• 一条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;
1
2
3
4
5
6
7
8

6、子查询

什么是子查询？
select语句当中嵌套select语句，被嵌套的select语句称为子查询。

子查询可以出现在哪里？

select ..(select)
from ..(select)
where ..(select)
1
2
3

7、where中的子查询

找出比最低工资高的员工姓名和工资：

思路：
第一步：查询最低工资
第二步：找出大于800的
第三步：合并

select ename,sal 
from emp 
where sal>(select min(sal) from emp); 
1
2
3

8、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;
1
2
3
4

9、select后的子查询

找出每个员工的部门名称（要求显示员工名，部门名）：

select 
e.ename,(select d.dname from dept d where e.deptno=d.deptno) as dname
from emp e;
1
2
3

10、union合并查询

union的效率更高，对于表连接来说，每连接一次新表，则匹配的次数满足笛卡尔积，成倍的翻，但是union可以减少匹配的次数。在减少匹配次数的情况下，还可以完成两个结果集的拼接。

注意：union在进行结果集合并的时候，要求两个结果集的列数相同。

查询工作岗位是MANAGER和SALESMAN的员工：

select ename,job from emp where job='MANAGER'
union
select ename,job from emp where job='SALESMAN';
1
2
3

11、limit

limit是将查询结果集的一部分取出来，通常使用在分页查询当中：
例如：百度默认一页显示10条记录。

分页的作用：为了提高用户的体验，因为一次全部都查出来，则用户体验差，分页就可以一页一页翻看。

注意：mysql当中，limit在order by之后执行

limit的使用：

• 完整用法：limit startIndex ,length
  startIndex：起始下标
  length：长度
• 缺省用法：limit 5;表示取前5

取前五条记录：

select ename,sal
from emp
order by sal desc
limit 5;//取前5
1
2
3
4

取出工资排名在3-5名的员工：

select ename,sal
from emp
order by sal desc
limit 2,3;
//2代表第三名开始
//3代表取3个人，也就是长度
1
2
3
4
5
6

分页：
每页显示3条记录

• 第一页：limit 0,3
• 第二页：limit 3,3
• 第三页：limit 6,3
• 第四页：limit 9,3

公式：

每页显示pageSize条记录

• 第pageNo页：limit pageSize*(pageNo-1)，pageSize

public class Test01 {
    public static void main(String[] args) {
        //用户提交过来一个页码，以及每页显示的记录条数
        int pageNo=5;//第5页
        int pageSize=10;//每页显示的记录条数
        int startIndex=(pageNo-1)*pageSize;
        String sql="select ...limit "+startIndex+","+pageSize;
    }
}
1
2
3
4
5
6
7
8
9

12、关于DQL语句总结

select ...
from ...
where ...
group by ...
having ...
order by...
limit ...
1
2
3
4
5
6
7

执行顺序：
1、from
2、where
3、group by
4、having
5、select
6、order by
7、limit

二、表

1、表的创建

建表的语法格式：
建表属于DDL语句，DDL包括：create drop alter

create table 表名(
	字段名1 数据类型，
	字段名2 数据类型，
	字段名3 数据类型
);
1
2
3
4
5

数据库的命名规范：
所有的标识符都是全部小写，单词和单词之间使用下划线进行衔接。

2、mysql中的数据类型

varchar
char
int
bigint
float
double
datetime
clob
blob
1
2
3
4
5
6
7
8
9

1、varchar（最长255）： 可变长度字符串，比较智能，节省空间。会根据实际的数据长度动态分配空间。
优点：节省空间
缺点：需要动态分配空间，速度慢。

2、char（最长255）： 定长字符串，不管实际的数据长度是多少，分配固定长度的空间去存储数据。使用不恰当的时候，可能会导致空间的浪费。
优点：不需要动态分配空间，速度快。
缺点：使用不当可能会导致空间的浪费。

varchar和char怎么选择？
性别字段选char,因为性别是固定长度的。
姓名字段选varchar，因为每一个人的姓名长度不同。

3、int（最长11）： 整数型，等同于java的int

4、bigint： 长整型，等同于java的long

5、float： 单精度浮点型

6、double： 双精度浮点型

7、date： 短日期类型

8、datetime： 长日期类型

9、clob： 字符大对象，最多可以存储4G的字符串，比如存储一篇文章，存储一个说明。
超过255个字符的都要采用CLOB字符大对象来存储。
Character Large Object :CLOB

10、blob： 二进制大对象。Binary Large Object。
专门用来存储图片，声音，视频等流媒体数据。往BLOG类型的字段上插入数据的时候，例如插入一个图片，视频等，需要使用IO流。

t_movie 电影表（专门存储电影信息的）

创建一个学生表？学号，姓名，年龄，邮箱地址

create table t_student(
	no int(3),
	name varchar(32),
	age int(3),
	email varchar(255)
);
1
2
3
4
5
6

3、删除表

drop table t_student;
1

当这张表不存在的时候，删除表会报错。

如果这张表存在的话，就删除：

drop table if exists t_student ;
1

4、insert

插入数据insert（DML）

语法格式：

insert into 表名(字段名1，字段名2...)
values(值1，值2...)
1
2

注意：
1、字段名和值要一一对应，数量要对应，数据类型要对应。
2、在insert时，如果字段名省略不写，则相当于全部都写上，那么值也要都写上。

insert into t_student(no,name,sex,age,email)
values(1,'zhangsan','m',20,165@123.com);
1
2

注意：insert语句但凡执行成功，则必然会多一条记录。

在insert时，如果字段名省略不写，则相当于全部都写上，那么值也要都写上:

insert into t_student values(2,'lisi','f',20,'lisi@123.com');
1

insert还可以一次性插入多条记录：

insert into t_student values
(2,'lisi','s',21,'lisi@123.com'),
(1,'wangwu','f',20,'lwangwu@123.com'),
(3,'zhangsan','d',25,'zhangsan@123.com');
1
2
3
4

5、default

使用default可以指定默认值

create table t_student(
	no int(3),
	name varchar(32),
	age int(3),
	sex char(1) default 'm',
	email varchar(255)
);
1
2
3
4
5
6
7

6、insert插入日期

7、format

format：格式化
语法：format(数字，‘格式’)

对工资进行千分位：

select ename,format(sal,'$999.999') as sal 
from emp;
1
2

1、str_to_date： 将字符串varchar类型转换成date类型。通常使用在插入insert方面，因为插入的时候需要一个日期类型的数据，需要通过该函数将字符串转换成date。

语法格式：str_to_date('字符串日期','日期类型')；

此外：如果提供的日期字符串是这个格式：%Y-%m-%d，这个格式，str_to_date函数就不需要了。

insert into t_user(id,name,birth)
values(1,'zhangsan',str_to_date('01-10-1990','%d-%m-%Y'));
1
2

insert into t_user(id,name,birth) values(2,'lisi','1990-10-01');
1

上面的代码当中：mysql会自动进行类型转换。

2、date_format： 将date类型转换成具有一定格式的varchar字符串类型。这个函数通常使用在查询日期方面，设置展示的日期格式。

select id,name,date_format(birth,'%m/%d/%Y') as birth from t_user;
1

select id,name,birth from t_user;
1

以上的SQL语句实际上是进行了默认的日期格式化，自动将数据库中的date类型转换成varchar类型。并且采用格式是mysql默认的日期格式：‘%Y-%m-%d’

java当中的日期格式：yyyy-MM-dd HH:mm:ss SSS

8、date和datetime的区别

date：是短日期，只包括年月日信息。
datetime：是长日期，包括年月日时分秒信息。

mysql短日期默认格式：%Y-%m-%d
mysql长日期默认格式：%Y-%m-%d %h : %i : %s

create table t_user(
	id int,
	name varchar(32),
	birth date,
	create_time datetime
);
1
2
3
4
5
6

id是整数
name是字符串
birth是短日期
create_time是这条记录的创建时间：长日期类型

insert into t_user(id,name,birth,create_time)
values(1,'zhangsan','1990-10-03','2020-06-19 15:49:50'); 
1
2

在mysql当中获取系统当前时间：now()函数
now()函数获取的时间带有时分秒信息。

insert into t_user(id,name,birth,create_time)
values(1,'zhangsan','1990-10-03',now()); 
1
2

9、update

语法格式：

update 表名 set 字段名1=值1，字段名2=值2，字段名3=值3...where 条件；
1

注意：没有条件限制会导致所有数据全部更新。

将id=2的记录修改字段值：

update t_user set name='jack',birth='2000-10-11' 
where id=2;
1
2

10、delete

语法格式：

delete from 表名 where 条件;
1

注意：没有条件限制，则整张表的数据会全部删除。

delete from t_user where id=2;
1

11、快速创建表

create table emp2 as select * from emp;
1

原理：将一个查询结果当做一张表新建。这个可以完成表的快速复制。表创建出来，同时表中的数据也存在了。

12、将查询结果当做一张表创建

create table  mytable as 
select empno,ename 
from emp
where job='MANAGER';
1
2
3
4

13、快速删除数据的原理

如果使用delete from：
表中的数据被删除了，但是这种数据在硬盘上的真实存储空间不会被释放。
这种删除的缺点是：删除的效率比较低。
这种删除的优点是：支持回滚，后悔了可以再恢复数据。

truncate：这种删除效率比较高，表被一次性截断了，物理删除。缺点：不支持回滚。优点：快速。

truncate table dept_nak;//属于DDL操作
1

三、存储引擎

存储引擎：

mysql默认的存储引擎是：InnoDB
mysql默认的字符编码方式是：utf-8

在建表的时候可以指定存储引擎，以及字符编码方式。

如何查看mysql支持哪些存储引擎呢？

show engine \G
1

常用存储引擎介绍：

1、MyISAM存储引擎：
它管理的表具有以下特征：
使用三个文件表示每个表：

• 格式文件 —— 存储表的定义（mytable.frm）
• 数据文件 —— 存储表行的内容（mytable.MYD）
• 索引文件 —— 存储表上索引（mytable.MYI）：索引是一本书的目录，缩小扫描范围，提高检索效率。
  可被转换为压缩，只读表来节省空间。MyISAM不支持事务机制，安全性低。

2、InnoDB存储引擎： 这是mysql默认的存储引擎，同时也是一个重量级的存储引擎。InnoDB支持事务，支持数据库崩溃后自动恢复机制。特点：非常安全。

3、MEMORY存储引擎：
优点：查询效率最高。
缺点：不安全，关机之后数据消失。因为数据和索引都在内存当中。