数据库知识点整理

DDL

DDL-数据库操作

show databases    ------------  查看所有数据库

select database();  ----------查看当前数据库

create database 数据库名；---- 创建数据库

use 数据库名；  --------------使用数据库

drop database 数据库名；-----删除数据库

DDL-表操作

show tables；-----查看当前数据库所有表

desc 表名； -----查询表结构

show create 表名；---查询指定表的建表语句

create table 表名（字段 字段类型 ，字段 字段类型) [ comment 表注释 ]; ---表创建

alter table 表名 add（增加）/modify（修改数据类型）/change（修改字段）/drop（删除字段）/rename to（修改表名）...;

drop table 表名；  ------删除表

truncate table 表名；------删除指定表并重新创建该表

DML

 insert into 表名（字段1,字段2...）value [(值1，值2...)] 添加数据

update 表名 set 字段1 = 值1，字段2 = 值2 [where 条件 ]；----修改数据

delete from 表名 [ where 条件 ]；---------------删除数据

delete from 表名；--------删除整张表的数据

DQL

 select                       字段列表--------->字段名[ AS ]别名

from                          表名列表

where                       条件列表----------->    ( >  >=  <  <=  <>  like  between...and...  in   and   or ）

                                                           where 分组之前过滤，不满足where条件不分组

group by                  分组字段列表

Having                     分组后条件列表 ------->分组之后过滤

order by                   排序字段列表------------>升序ASC  降序DESC

limit                          分页参数------------------>起始索引（从0开始，每页展示记录数）

DQL基础查询

select 字段1，字段2，....from  表名；-------------> 查询多个字段

select * from 表名； -------------------------------------->查询所有字段

select 字段 as（as可省）‘ 别名 ’ from 表名；--------->设置别名

select distinct 字段列表 FROM 表名;---------------->去除重复记录：

like " %x"  最后一位为x

like " _ _ " 查询两位

聚合函数

常见聚合函数：

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

语法：

select  聚合函数(字段列表) from 表名;

分组查询

语法：

select 字段列表 from 表名 [  条件 ] group by 分组字段名 [ having 分组后的过滤条件 ];

where 和 having 的区别：

执行时机不同：where是分组之前进行过滤，不满足where条件不参与分组；having是分组后对结果进行过滤。

判断条件不同：where不能对聚合函数进行判断，而having可以。

注意事项

执行顺序：where > 聚合函数 > having

分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义

排序查询

select 字段列表 from 表名 order by 字段1 排序方式1, 字段2 排序方式2;

ASC: 升序（默认）

DESC: 降序

注意：如果是多字段排序，第一个字段相同时才会根据第二个字段进行排序

分页查询：

select 字段列表 from 表名 limit 起始索引, 查询记录数;

注意事项

• 起始索引从0开始，起始索引 = （查询页码 - 1） * 每页显示记录数
• 分页查询是数据库的方言，不同数据库有不同实现，MySQL是LIMIT
• 如果查询的是第一页数据，起始索引可以省略，直接简写 LIMIT 10

DQL执行顺序

from ->where -> group by -> select-> order by ->limit

DCL

DCL----管理数据库用户、控制数据库的访问权限

1.用户管理

use mysql;

select * from user;------------->查询用户

create user ‘ 用户名 ’ @ ‘ 主机名 ’ identified by ' 密码 '；------>创建用户

alter user ‘ 用户名 ’ @ ‘ 主机名 ’ identified with mysql_native password by ' 新密码 '；---修改用户密码

drop user ‘ 用户名 ’ @  ‘ 主机名 ’；-------删除用户

2.权限控制

show grants for ‘ 用户名 ’ @ ‘ 主机名 ’;----------->查询权限

grant 权限列表 on 数据库名.表名 to ' 用户名 '  @ ‘ 主机名 ’；------>授予权限

revoke  权限列表 on 数据库名.表名 from ' 用户名 '  @ ‘ 主机名 ’；------>撤销权限

函数

1.字符串函数

concat（字符串拼接）、lower（全转小写）、upper（全转大写）、lpad(左填充)、rpad(右填充)、trim(去头和尾空格)、substring(字符串截取)

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

2.数值函数

ceil(向上取整)、floor（向下取整）、mod(x，y)返回x/y的模、rand()返回0-1内的随机数  、round（x,y）求参数x四舍五入的值，保留y位小数

函数	功能
ceil(x)	向上取整
floor(x)	向下取整
mod(x, y)	返回x/y的模
rand()	返回0~1内的随机数
round(x, y)	求参数x的四舍五入值，保留y位小数

3.日期函数

函数	功能
curdate ()	返回当前日期
curtime ()	返回当前时间
now ()	返回当前日期和时间
year (date)	获取指定date的年份
month (date)	获取指定date的月份
day (date)	获取指定date的日期
date_add (date, interval expr type)	返回一个日期/时间值加上一个时间间隔expr后的时间值
datediff (date1, date2)	返回起始时间date1和结束时间date2之间的天数

4.流程函数

函数	功能
if (value, t, f)	如果value为true，则返回t，否则返回f
ifnull (value1, value2)	如果value1不为空，返回value1，否则返回value2
case when[ val1 ] then [ res1 ] … else [ default ] end	如果val1为true，返回res1，… 否则返回default默认值
case [ expr ] when  [ val1 ]  then  [ res1 ] … else [ default ] end	如果expr的值等于val1，返回res1，… 否则返回default默认值

约束

1.非空约束 

not null

2.唯一约束

unique

3.主键约束

primary key 主键约束非空且唯一 （自增：auto_increment）

4.默认约束

default

5.检查约束

check 要求存储的值满足条件

6.外键约束

foreign key  保证数据的一致性和完整性

多表查询

1.多表关系

一对一：用于表结构拆分，在其中任何一方设置外键，关联两张表的主键

一对多：在多的一方设置外键，关联一的一方的主键

多对多：建立中间表中间表包含两个外键，关联两张表的主键

索引

1.索引：

索引是高效获取数据的数据结构

2.索引结构：

B-Tree（B树）：平衡的多路查找树

B+tree：Btree和双向链表

Hash（无法利用索引完成排序）只能用于等值比较，不支持范围查询

R-tree

3.索引分类：

单值索引：一个索引只包含单个列，一个表中可以有多个单值索引

唯一索引:索引列的值必须唯一，可为空

复合索引：一个索引包含多个列

4.索引语法：

create [ unique] index xxx on xxx(xxx);  创建索引

show index from xxxx;  查看索引

drop index xxx on xxxx;  删除指定索引

5.SQL性能分析：

执行频次、慢查询日志、profile、explain

6.索引使用：

联合索引：最左前缀法则（最左边的列必须存在）

索引失效：

1.索引列函数运算

2.字符串不加引号

3.like 模糊匹配

4.or连接条件，一侧有索引一侧无索引

5.mysql评估，全表扫描比索引快

6.没有用最左前缀法则则失效

SQL提示

覆盖索引：查询返回的列在索引结构都包含了  不需要再回表查询

前缀索引：场景（字符串长度长或者大文本字段）

单列索引/联合索引：推荐联合索引，联合索引性能较高，运用得当可避免回表查询

7.索引设计原则：

1.需要针对哪些表设计索引 数据量大且查询频次较高

2.需要针对这些表的哪些字段建立索引  经常在where、order by，group by 之后出现的字段建立对应索引

3.如果此列唯一 建唯一索引 区分度高

 如果能建立联合索引尽量用联合索引

字符串长度较长或者大文本字段用前缀索引

SQL优化

1.插入数据

insert ：批量插入、手动控制事务、主键顺序插入

2.主键优化

数据组织方式：基于索引

主键长度尽量短、顺序插入   ---主键自增

3.order by 优化

using index：直接通过索引返回数据，性能高

using filesort：需要将返回结果在排序缓冲区排序

4.group by 优化

索引，多字段分组满足最左前缀法则

5.limit优化

覆盖索引+子查询

6.count优化

性能：count（字段）<count（主键id）<count(1)约=count（*）

7.update优化

尽量根据主键/索引字段进行数据更新

视图

视图：

虚拟的表，不保存查询结果，只保存查询SQL逻辑

作用：简单、安全、数据独立

创建：create [or replace] view 视图名称（列名列表）AS select 语句 [ with [ cascaded | local ] check option ]

查询：

查看创建视图语句：show create view 视图名称

查看视图数据：select * from 视图名称...；

修改

方式一：create [ or replace ] view 视图名称[( 列名列表)] AS select语句[ with [ cascaded | local ] check option ]

方式二：alter view 视图名称[( 列名列表)] AS select语句 [ with [ cascaded | local ] check option ]

删除

drop view [ if exists ] 视图名称 [ 视图名称 ]...

代码示例：

 

触发器

可以在表数据进行insert、update、delete之前或之后触发

保证数据完整性、日志记录、数据校验