深入浅出MySQL之基础篇

对于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数据类型

整数类型	字节	最大值、最小值
TINYINT	1	-128~127
SMALLINT	2	-32768~32767
MEDIUMINT	3
INT、INTGER	4
BIGINT	8
浮点数	字节	最大值、最小值
FLOAT	4
DOUBLE	8
定点数	字节
DEC(M,D)	M+2
DECIMAL(M,D)	M+2
位类型	字节
BIT(M)	1~8

2.2.日期时间类型

日期和时间类型	字节	零值表示
DATE	4	0000-00-00 00:00:00
DATETIME	8	0000-00-00
TIMESTAMP	4	00000000000000
TIME	3	00:00:00
YEAR	1	0000

2.3字符串类型

字符串类型	字节	描述及存储需求
CHAR(M)	M	M为0~255之间的整数
VARCHAR(M)		M为0~65535之间的整数
TINYBLOB		允许长度0~255字节
BLOB		允许长度0~65535字节
MEDIUMBLOB
LONGBLOB
TINYTEXT		允许长度0~255字节
TEXT		允许长度0~65535字节
MEDIUMTEXT
LONGTEXT
VARBINARY(M)
BINARY(M)	M

3.MySQL中的运算符

3.1算术运算符

运算符	作用
+	加法
-	减法
*	乘法
/，DIV	除法，返回商
%，MOD	除法，返回余数

3.2比较运算符

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

运算符	作用
=	等于
<>或!=	不等于
<=>	NULL安全的等于
<	小于
<=	小于等于
>	大于
(>=)	大于等于
BETWEEN	存在于指定范围
IN	存在于指定集合
IS NULL	为NULL
IS NOT NULL	不为NULL
LIKE	通配符匹配
REGEXP或RLIKE	正则表达式匹配

3.3逻辑运算符

运算符	作用
NOT或!	非
AND或&&	与
OR或||	或
XOR	异或

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

3.4位运算符

运算符	作用
&	位与（位AND）
|	位或（位OR）
^	位异或（位XOR）
~	位取反
(<<)	位右移
<<	位左移

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

4.常用函数

4.1字符串函数

函数	功能
concat(s1,s2,...,sn)	连接s1,s2,...sn为一个字符串
insert(str,x,y,instr)	将字符串str从第x位置开始，y个字符长的子串替换为字符串instr
lower(str)	将字符串str中的所有字符变为小写
upper(str)	将字符串str中所有的字符变为大写
left(str,x)	返回字符串str最左边的x个字符
right(str,x)	返回字符串str最右边的x个字符
lpad(str,n,pad)	用字符串pad对str最左边进行填充，直到长度为n个字符长度
rpad(str,n,pad)	用字符串pad对str最右边进行填充，直到长度为n个字符长度
ltrim(str)	去掉字符串str左侧的空格
rtrim(str)	去掉字符串str行尾的空格
repeat(str,x)	返回str重复x次的结果
replace(str,a,b)	用字符串b替换字符串str中所有出现的字符串a
strcmp(s1,s2)	比较字符串s1和s2
trim(str)	去掉字符串行尾和行头的空格
substring(str,x,y)	返回从字符串str x位置起y个字符长度的子串

4.2数值函数

函数	功能
abs(x)	返回x的绝对值
ceil(x)	返回大于x的最小正整数
floor(x)	返回小于x的最大正整数
mod(x,y)	返回x/y的摸
rand()	返回0~1内的随机值
round(x,y)	返回参数x的四舍五入的有y位小数的值
truncate(x,y)	返回数字x截断为y位小数的结果

4.3日期和时间函数

函数	功能
curdate()	返回当前日期
curtime()	返回当前时间
now()	返回当前的日期和时间
unix_timestamp(date)	返回日期date的unix时间戳
from_unixtime	返回unix时间戳的日期值
week(date)	返回日期为date为一年中的第几周
year(date)	返回日期date的年份
hour(time)	返回time的小时值
minute(time)	返回time的分钟值
monthname(date)	返回date的月份值
date_format(date,fmt)	返回按字符串fmt格式化日期date值
date_add(date,interval expr type)	返回一个日期或时间值加上一个时间间隔的时间值
datediff(expr,expr2)	返回起始时间expr和结束时间expr2之间的天数

4.4流程函数

函数	功能
if(value,t f )	如果value是真，返回t，否则返回f
ifnull(value1,value2)	如果value不为空，返回value1，否则返回value2
case when [value1] then [result1] ..else [default] end	如果value1是真，返回result1，否则返回default
case [expr] when [value1] then [result1]... else[default] end	如果expr等于value1，返回result1，否则返回default

参考资料

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