对于Java后台开发，掌握掌握数据库相关的知识是必不可少的。一直觉得数据库是自己的最薄弱的环节，趁寒假时间对数据库知识做一个补充！

1.SQL基础

SQL分为数据库定义语言（DDL：Data Defineition Language）、数据操纵语言（DML：Data Manipulation Language）、数据定义语言（DCL：Data Control Language）。

image.png

1.1数据库定义语言DDL

1.创建数据库

 #创建数据库
 create database dbname
 #查看数据库
 show databases
 #打开数据
 use dbname
 #查看数据库中的表
 show tables

2.删除数据库

#删除数据库
drop database dbname

3.创建表

#创建表
create table emp(ename varchar(10),hiredate date,sal decimal(10,2),deptno int(2))
#查看表定义
desc emp
#查看详细的表信息
show create table emp \G

4.删除表

#删除表
drop table emp

5.修改表

（1）修改表类型

#修改表emp的ename字段定义，将varchar(10)改为varchar(20)
alter table emp modify ename varchar(20)

（2）增加表字段

#在emp表中增加新字段age，类型为int(3)
alter table emp add column age int(3)

（3）删除表字段

#将字段age删除掉
alter table emp drop column age

（4）字段改名

#将age改名为age1，同时修改字段类型为int(4)
alter table emp change age age1 int(4)

注意：change和modify都可以修改表的定义，不同的是change后面需要写两次列名，不方便。但是change的优点是可以修改列名称，modify则不能。

（5）修改字段排列顺序

#将新的字段birth date加载ename之后
alter table emp add birth date after ename

（6）更改表名

#将表emp改名为emp1
alter table emp rename emp1

1.2数据操纵语言DML

1.插入记录

#将表emp中插入以下记录
insert into emp (ename,hiredate,sal,deptno)values('zzx1','2000-01-01','2000',1)
#也可以不用指定字段名称，但是values后面的顺序应该和字段的排列顺序一致
insert into emp values('lisi','2003-02-01','3000',2)
#字段中没有赋值的默认为NULL

#插入多条记录，每条记录之间都用逗号进行了分隔。这个特性可以使得mysql在插入大量记录时，节省很多的网络开销，大大提高了插入效率
insert into dept values(5,'dept5'),(6,'dept6')

2.更新记录

#将表emp中ename为`lisi`的薪水sal从3000更改为4000
update emp set sal=4000 where ename='lisi'

#多表更新
#同时更新表emp中的字段sal和表dept中的字段deptname,更新后的sal为之前的sal乘以deptno,deptname为emp表中的ename
update emp a,dept b set a.sal=a.sal*b.deptno,b.deptname=a.ename where a.deptno=b.deptno

注意：多表更新的语法更多地用在根据一个表的字段来动态的更新另外一个表的字段

3.删除记录

#在emp中将ename为`dony`的记录全部删除
delete from emp where ename='dony'

#一次删除多个表的数据
#同时删除表emp和表dept中的deptno为3的记录
delete a,b from emp a,dept b where a.deptno=b.deptno and a.deptno=3

4.查询记录

image.png

（1）查询不重复的记录（distinct）

#将表中的记录去掉重复后显示出来，可以用distinct来实现
select distinct deptno from emp

（2）条件查询（where）

#查询所有deptno为1的记录
select * from emp where deptno =1

where后面的条件是一个字段的=比较，除了=之外，还可以使用>、<、>=、<=、!=等比较运算符；多个条件查询还可以使用or、and等逻辑运算符进行多条件联合查询

（3）排序和限制（order by）

我们经常会有这样的需求，取出按照某个字段进行排序后的记录结果集，这就要用到order by来实现

select * from tablename [where condition][order by field1 [desc/asc],field2 [desc/asc]...]

如果排序字段的值一样，则值相同得字段按照第二个排序字段进行排序，依次类推。如果只有一个排序字段，则这些字段相同的记录将会无需排列。

对于排序后的记录，如果只希望显示一部分，而不是全部。这时，就可以使用limit关键字来实现。

select ... limit offset_start,row_count
#offset_start表示记录的起始偏移量，row_count表示显示的行数

limit经常和order by一起配合使用来进行记录的分页显示

（4）聚合

很多情况下，用户都需要进行一个汇总操作，这时就要用到SQL的聚合操作。

select [field1,field2,...] fun_name
from tablename
[where where_contition]
[group by field1,field2...[with rollup]]
[having where_contition]

对其参数进行一下说明：

• fun_name表示要做的聚合操作，也就是聚合函数，常用的有sum、count(*)、max、min
• group by关键字表示要进行分类聚合的字段
• with rollup是可选语法，表明是否对分类聚合后的结果进行再汇总
• having关键字表示对分类后的结果在进行条件的过滤

注意：having和where的区别在于，having是对聚合后的结果进行条件的过滤，而where是在聚合前就对记录进行过滤，如果逻辑允许，我们尽可能用where先过滤记录，这样因为结果集减少，将对聚合的效率大大提高，最后在根据逻辑看是否用having进行再过滤。

#统计人数大于1人的部门
select deptno,count(1) from emp group by deptno having count(1)>1

（5）表连接

当需要同时显示多个表中的字段时，就可以用表连接来实现这样的功能。从大类上分，表分为内连接和外连接，它们之间最主要的区别是，内连接仅选出两张表中相互匹配的记录，而外连接会选出其他不匹配的记录。我们最常用的是内连接。

外连接又分为左连接和右连接 ，具体定义如下：

• 左连接：包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录。
• 右连接：包含所有的右边表中的记录甚至是左边表中没有和它匹配的记录。

select ename,deptname from emp left join dept where emp.deptno=dept.deptno

（6）子查询（**重点 **）

某些情况下，当进行查询的时候，需要的条件是另外一个select语句的结果，这个时候，就要用到子查询。用于子查询的关键字主要包括in、not in、=、!=、exist、not exist等。

如果子查询记录数唯一，还可以用=代替in

详细查询过程在基于Hive查询中可以看到。

（7）记录联合

我们经常会碰到这样的应用，将两个表的数据按照一定的查询条件查询出来之后，将结果合并到一起显示出来，这个时候，就需要用union和union all关键字来实现这样的功能，具体语法如下：

select * from t1
union/union all
select * from t2
...
union/union all
select * from tm

union和union all的主要区别是union all是把结果集直接合并在一起，而union是将union all后的结果进行一次distinct，去除重复记录后的结果。

select deptno from emp
union all
select deptno from dept

1.3数据控制语言DDL

DCL语句主要是DBA用来管理系统中的对象权限时使用，一般的开发人员很少使用。

grant select,insert on sakila.*to 'z1@localhost' identified by '123'

2.MySQL支持的数据类型

2.1数据类型

2.2.日期时间类型

2.3字符串类型

3.MySQL中的运算符

3.1算术运算符

3.2比较运算符

当使用select进行查询时，MySQL允许用户对表达式的左边操作数和右边操作数进行比较，比较结果为真则返回1，比较结果为假则返回0，比较结果不确定则返回NULL。

3.3逻辑运算符

XOR表示逻辑异或，当任意一个操作数为NULL时，返回值为NULL。对于非NULL的操作数，如果两个的逻辑真假值相异，则返回结果1，否则返回0

3.4位运算符

对1做位取反：在MySQL中，常量数字默认会以8个字节来表示，8个字节就是64位，常量1的二进制位表示为63个0加一个1，位取反后就是63个1加一个0。

4.常用函数

4.1字符串函数

4.2数值函数

4.3日期和时间函数

4.4流程函数

参考资料

深入浅出MySQL（数据库开发、优化与管理维护）第2版