第二阶段 MYSQL

数据库

Database，简称DB。按照一定的数据结构来组织、存储和管理数据的仓库。

SQL

SQL（Structured Query Language），结构化查询语言。

关系型数据库

关系型数据库： 由多张相互连接的二维表组成的数据库，就像Excel一样，由行和列组成的二维表。

代表：

• Mysql(免费的，性能不稳定)
• Oracle(收费，性能稳定.)

字符集

建议用utf8mb4：兼容性更好
utf-8应限于国内通用，性能没有上边的兼容性好

排序规则：

1.默认utf8mb4_0900_ai_ci。 0900 指的是 Unicode 校对算法版本 ， ai 指的是口音不敏感；
2.就是说排序时 e，è，é，ê 和 ë 之间没有区别； ci 表示不区分大小写。也就是说，排序时 p 和 P 之间没有区别。

数据类型

整数

类型	大小	描述
TINYINT	1 byte	小整数值
INT或INTEGER	4 bytes	整数值
FLOAT	4 bytes	单精度浮点数值
字符串
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TEXT	0-65 535 bytes	长文本数据
日期类型
DATE	YYYY-MM-DD	日期值
--------	-------------------	----------------
TIME	HH:MM:SS	时间值或持续时间
YEAR	YYYY	年份值
DATETIME	YYYY-MM-DD HH:MM:SS	混合日期和时间值

CHAR和VARCHAR的区别：（重点面试题）

1.char不论你实际存储了多少的字符，都会占用设置的n个字符空间 ， 比如手机号码，性别，身份证号等
2.varchar它存储的长度并不固定， 实际使用多少就占用多少

注释

• 单行注释：# 注释内容
• 多行注释： /* 注释内容 */

表的操作

增

1.给指定字段添加数据
insert into 表名 (字段1,字段2…) values (值1,值2…);
2.给全部字段添加数据
insert into 表名 values (值1,值2…);
3.批量添加数据
insert into 表名 (字段1,字段2…) values (值1,值2…),(值1,值2…),(值1,值2…);
insert into 表名 values (值1,值2…),(值1,值2…),(值1,值2…);

删

delete from 表名 [where 条件]

改

update 表名 set 字段1=值1， 字段2=值2,…[where 条件]
#查
##基本查询
select * from person
select id,name,age,sex,favourite from person
可以用as给字段起别名，as也可以省略。

条件查询

用where表示查询的条件

SELECT id,name,age ,sex ,favourite from person where id BETWEEN 1 and 10
SELECT id,name,age ,sex ,favourite from person where id>5 and sex=‘男’
SELECT id,name,age ,sex ,favourite from person where id>5 or sex=‘男’

匹配0或多个字符，一般不用左模糊（%放在左边，导致索引失效，降低查询效率）

SELECT id,name,age ,sex ,favourite from person where name like ‘%豪’

_代表匹配一个字符

SELECT id,name,age ,sex ,favourite from person where name like ‘_豪’
SELECT id,name,age ,sex ,favourite from person where name like '%二%

分页查询

语法 limit 起始索引，个数
分页查询第m页，每页展示n 条数据 limit (m-1)*n, n

SELECT id,name,age ,sex ,favourite from person limit 0,5
SELECT id,name,age ,sex ,favourite from person limit 5,5
SELECT id,name,age ,sex ,favourite from person limit 10,5

排序

排序 order by 字段名 （ASC 升序，DESC降序）

SELECT id,name,age ,sex ,favourite from person ORDER BY age ASC;
SELECT id,name,age ,sex ,favourite from person ORDER BY age DESC;

聚合函数

count()   sum()  avg()   max()  min()

select count(0) from person 查询有多少条记录
select sum(字段) from person
select avg(字段) from person
select min(字段) from person
select max(字段) from person

分组查询

 group by 字段

SELECT sex,count(0),avg(age) from person GROUP BY sex;
having条件查询 where分组前，having分组后的条件
SELECT sex,count(0),avg(age) from person where age>8 GROUP BY sex having avg(age)>20;

子查询

SELECT id,name,age ,sex ,money from person where id in (SELECT id from dept where name=‘小卖部’);

内连接

注意：也可以给表名起别名，如果两个表中有相同的字段名称，那么需要用别名+点号来区别，否则报错

select * from person  JOIN dept on person.dept_choose=dept.id

外连接

1.左连接
左外连接 left [outer] join 查询的是左边表的所有部分和右边表的交集
select * from person p LEFT JOIN dept d on p.dept_choose=d.id
2.有链接
右外连接 right [outer] join 查询的是右边表的所有部分和左边表的交集
select * from person p RIGHT JOIN dept d on p.dept_choose=d.id

左外连接和右外连接的区别：

左外是以左边的表为主表，查询的结果只关注左表，不在乎右边
as 可以给字段名另外起个名字（两个表里的字段一样的情况下）
as 也可以用在表名上（表名有点长，写的麻烦）

运算符

=、！=、<>、<、<=、>、>=、

关键字

between...and...、in(set)、is null、and、or、NOT(不常用)

索引

索引的优缺点

优点:

• 1.创建唯一性索引，保证数据库表中每一行数据的唯一性
• 2.大大加快数据的检索速度，这也是创建索引的最主要的原因
• 3.减少磁盘IO（向字典一样可以直接定位）

缺点:

• 1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
• 2.索引需要占用额外的物理空间
• 3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度

索引类型

1. 普通（Normal）：
   也叫非唯一索引，是最普通的索引，没有任何的限制。

2. 唯一（Unique）：
   唯一索引要求键值不能重复。另外需要注意的是，主键索引是一种特殊的唯一索引，它还多了一个限制条件，要求键值不能为空。主键索引用 primay key创建。

3. 全文（Fulltext）：
   针对比较大的数据，比如我们存放的是消息内容，有几 KB 的数 据的这种情况，如果要解决like 查询效率低的问题，可以创建全文索引。只有文本类型 的字段才可以创建 全文索引，比如 char、varchar、text。

4. 聚合索引：
   由多个字段共同组成的索引。

索引优化

1. 尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描

SELECT * FROM user WHERE name LIKE ‘%用%’
如果需求是要在前面使用模糊查询，使用MySQL内置函数INSTR(str,substr) 来匹配，作用类似于java中的indexOf()

2. 尽量避免使用in 和not in，会导致引擎走全表扫描
SELECT * FROM user WHERE id IN (2,3)

优化方式：如果是连续数值，可以用between代替

3. 尽量避免使用 or，会导致数据库引擎放弃索引进行全表扫描

-- 优化方式：可以用union（且）代替or
select * from user WHERE id = 1	
UNION
select * from user WHERE id = 3

4. 尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描

-- 优化方式：可以给字段添加默认值0，对0值进行判断
select * from user WHERE age = 0

5.尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描

 全表扫描
SELECT * FROM user WHERE age/10 = 9
 走索引
SELECT * FROM user WHERE age = 10*9

6. 当数据量大时，避免使用where 1=1的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描

-- 优化方式：用代码拼装sql时进行判断，没 where 条件就去掉 where，有where条件就加 and
SELECT name, age, pwd FROM user WHERE 1 and 1

7. 查询条件不能用 < > 或者 !=

使用索引列作为条件进行查询时，需要避免使用<>或者!=等判断条件。要去java进行判断，不能对数据库进行操作

8. where条件仅包含复合索引非前置列

如下：复合（联合）索引包含a1，b2，c3三列，但SQL语句没有包含索引前置列"a1"，按照MySQL联合索引的最左匹配原则，不会走联合索引

create index user_index on user (name,age,pwd)
-- 这种方式是不会走索引的
select name,age,pwd from user where age=123 and pwd='312'

9. 隐式类型转换造成不使用索引

如下SQL语句由于索引对列类型为varchar，但给定的值为数值，涉及隐式类型转换，造成不能正确走索引。
select name from table where name = 123;

事务

事务的四大特征：ACID
原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。
持久性：当事务提交或回滚后，数据库会持久化的保存数据。
隔离性：多个事务之间。相互独立。
一致性：事务操作前后，数据总量不变

函数

函数	功能
concat(s1,s2,…sn)	字符串拼接，将 s1，s2 ，sn 拼接成一个字符串
lower(str)	将字符串 全部转为小写
upper(str)	将字符串 全部转为大写
lpad(str,n,pad)	左填充，用字符串pad对 的左边进行填充，达到 个字符串长度
rpad(str,n,pad)	右填充，用字符串pad对 的左边进行填充，达到 个字符串长度
trim(str)	去掉字符串头部和尾部的空格
sbustring(str,start,len)	返回从字符串 str从start位置起的len个长度

select CONCAT(‘a’,‘b’,‘c’)
select LOWER(‘ABC’)
select UPPER(‘abc’)
select LPAD(‘123’,5,‘000’)
select RPAD(‘123’,5,‘000’)
select TRIM(’ a a ')
select SUBSTRING(‘18035185213’,3,4)