一、什么是数据库
基础知识
数据库就是一系列数据由某种有组织的方式存储的数据集合。数据库相当于一个储存数据的容器。
MySQL关系型数据库管理系统，将数据保存在不同的表中。能加快读取速度，提高灵活性。（免费开源，体积小，速度快）
那么，什么是关系型数据库？
关系型数据库是由多张表互联的二维行列表格组成的数据库
专业术语：
DB:数据库（database）:存储数据的“仓库”,它保存了一系列有组织的数据。
DBMS：数据库管理系统（database management system）.数据库通过DBMS创建和操作的容器。也就是管理数据库中的数据。
SQL：结构化查询语言，与数据库通信的语言。
优点：
1.几乎所有的数据库软件都支持的语言。
2.简单易学。
3.灵活使用，可以处理复杂和高级的数据库操作。
数据库特点：
1.将数据存放表中，表存放库中
2.一个数据库可以有多个表，每个表都有名称来标志，表名具有唯一性。
3.表在创建时，定义了数据在表中如何存储。
4.每个表由列即字段组成。
5.表中的数据按行来存储。
MySQL优点：
1.成本低，开源
2.性能高，执行快
3.简单易用
数据库主键，外键，索引
数据库中的表的主键（也是主索引。primary key ）：在表中具有唯一性，用于唯一的标识表中的某一条记录；主键不重复并且不能为空。
作用：
1.保持数据的唯一性，完整性
2.提高查询效率
一个表只有一个主键，可以由一个属性组成，也可以是多个属性组成，称为联合主键
外键（foreign key）：外键表示两个关系表之间的相关联。位于表A中，同时是另一个表B的主键。也可以说表A是从表，表B是主表。外键也被称为关键字。
作用：
1.保持数据的一致性，完整性
2.主要是控制存储在外键表中的数据。使两张表形成关联，外键只能引用外表中的值或使用空值
3.最直观的作用使防止非法数据录入。即，在录入外键数据时，必须要在其所属的主表中存在相应的数据，否则会报错。
给已存在的字段添加外键约束
alter table students
add foreign key(c_id从表的外键字段) references classes(id主表的主键字段);
删除外键约束：
先查看表的属性设置
show create table teacher
alter table teacher drop foreign key s_id(外键名)
索引：是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B_TREE索引，它加速了数据访问，因为存储引擎不会再去扫描整个表得到数据，他是从根节点开始，根节点保存了子节点的指针，存储引擎会根据指针快速寻找数据。
关系型和非关系型数据库的区别
关系型数据库:mysql ,orcal,SQLite等 ； 非关系型数据库NoSQL：MongoDb，redis等
关系型数据库：最典型就是数据结构是表，由二维表及其之间的联系所组成的一个数据组织
非关系型数据库：严格说他不算是数据库，是一种数据结构化存储方法的集合，可以是键值对，文档，图片等等
区别：
1.nosql成本低：易部署，而且是开源软件。关系型数据库价格高
2.nosql查询速度快：nosql数据库将数据存储在缓存之中，关系型数据库将数据存储在硬盘中，so 查询速度肯定nosql快
3.存储数据的格式不同：nosql存储是key,value形式，文档，图片形式等，所以可以存储基础类以及对象或是集合等各种形式；关系型数据库则只支持基础类型，存在特定结构的表中。
4.扩展性：nosql基于键值对，数据之间没有耦合，扩展性好；关系型数据库有多表查询机制的限制，导致扩展性难；
5.数据的一致性：nosql没有事务处理；关系型数据库有高级事务处理，提高安全性；
6.添加数据便携性：sql中必须先定义好表和字段结果才能添加，比如主键，索引等，如果有较大的结构变更，修改复杂；nosql中数据可以在任何地方随时添加，不需要定义表；
NoSQL缺点：
1.维护的工具和资料有限，nosql是新技术，这方面的资料少
2.不提供对sql的支持，学习和使用成本高
3.不提供关系型数据库对事务的处理。
关系型数据库优点：
1.适用于复杂查询，多表之间的数据查询
2.支持事务，安全性高（也是保持数据的一致性）
缺点：
1.大量数据的写入处理慢
2.为有数据更新的表做所以或表结构变更难
3.字段不固定的时候不好用
4.对简单查询需要快速返回结构的处理不够好
二、数据库命令
MySQL语法规范
1.不区分大小写。建议关键字大写；表名，列名小写。
2.每条命令用 ; 结尾
3.命令可以根据需要缩进，换行
4.注释：
单行注释：#注释文字 或–注释文字
多行注释/* 注释文字 */
基础查询
show databases; 查看数据库中有哪些数据库
use sys; 进入sys数据库
show tables; 查看sys数据库中的表
show database(); 调用函数寻找当前所在的数据库名字
创建表
create table 表名（
id int，
name varchar(20)）;
desc 表名;
查看表结构，如desc students;
常见增删改查语法介绍
表的创建：create table tablename (列名1 类型 约束，列名2 类型 约束，…)
表的删除：drop table tablename;
表的更改（结构的更改，不是数据的更改）：alter table tablename add|drop 列名|约束名
插入记录：insert into 表名 values(…)
更新记录：update tablename set 列名=值 where 条件
删除记录：delete from 表名 where 条件
详细见以下各个分支：
三、DQL语言的学习
SQL的执行顺序
1.from子句组装来自不同数据源的数据
2.where子句基于指定的条件对记录进行筛选
3.group by 子句将数据进行分组
4.使用聚合函数进行计算
5.使用having子句筛选分组的数据
6.计算所有表达式
7.select子句
8.order by 对结果集进行排序
9.limit 分页查询
select 查询关键字
select * from student;
select * from help_topic;
select * from help_topic WHERE name LIKE ‘CREATE_DIGEST’;
select 10*2 as res;
select VERSION() as version;
select description as “描述” from help_topic;
条件查询
- where查询条件子句的规范
select * from student where id=1234565;
2.or 或，满足多个条件中的一个即可; and 需要同时满足多个条件的查询。 and的优先级比or高，（）的优先级最高，他的使用是为了防止条件组合错误导致问题
select * from student where id=1233 or id=5546;
3.in / not in 用来指定条件范围(可以是数字或是字符串)
select * from student where id in (100,105,110);
4.like 模糊查询，通过某个关键字查询
select * from student where name like ‘%小明’；查找名为小明的所有信息
select * from student where name like ‘_明’；查找名字为明的并且名字为2个字的学生的所有信息
| 操作符 | 含义 | 举例 |
|---|---|---|
| in/not in | 等于/不等于列表中的任意一个值 | / |
| any|some | 返回的值和列表里的某一个值比较 | 例如：a>any(10,20,30)则表示a>列表值中的所有值，实际上就是大于最小值即可满足其他条件 |
| all | 返回的值跟列表的所有值比较 | 例如：a>all(10,20,30) 则等于a只要大于最大的值即可 |
distinct去重
去重,若多个条件，对后面所有的条件组合去重
select DISTINCT help_category_id from help_topic;
查询显示limit
select * ftom student limit 1,99； 下标从0开始算，从第二行开始查询，一共看99行
算术运算+ - * /
直接在结果中
select quality, price, quality*price as total from order;
+号作用
1.运算符:左右两边都是数值型
2.若其中一方是数值型，会尝试将其转换为数值型计算；若是字母形式无法转换成功，则统一转为0做运算
3.只要一方为null,结果为null
select 1+“1” ;
select 1+null;
比较，逻辑运算，模糊查询
1.条件表达式查询
条件运算符:> < =！= <> >= <=
select * from help_topic WHERE help_category_id>50;
2.逻辑运算符 ： && || ！
and or not
select * from help_topic WHERE help_category_id = 1 && help_topic_id=1;
select * from help_topic WHERE help_category_id=5 and example=‘not’;
3.模糊查询： like , between … and , in , is null
通配符：% 任意字符 ， _ 表示一个字符
若实际数据内容有通配符，需要转义，例如\_ 表示_; 或者通过关键字escape转义
1》like 查询
select * from help_topic WHERE description like “%information%”;
2》between … and
1)使用between and 可以提高语句的简洁度，完全等价于>= and <=
2)包含临界值
3)两个临界值不能颠倒
select * from help_topic WHERE help_category_id BETWEEN 1 and 3;
3》in包含查询 ：查询的内容需要在in的（）内容中的具体值。
注意：数字的查询不能是范围。
select *from help_topic where name in (xiaoming,xiaohong,xiaohuang);
4》判断null
is null :判断实际数据内容是否是null；
安全等于 <=> : 可以判断null值；
select * from help_topic WHERE help_category_id <=>1;
is null 和<=>差异：
1）is null 只能判断null值，可读性高；
2）<=> 不仅可以判断null,也可以判断数值，可读性低，使用少；
面试题：以下两种结果是否一样？
select * from help_topic;
select * from help_topic where help_category_id LIKE “%%” AND example LIKE “%%”;
答：不一样，若变量的值是null时不一样。
常见函数
概念：跟java中的方法类似；将一组逻辑语句封装在方法中，对外暴露方法名
好处：1.隐藏了实现细节；2.提高代码的复用性
调用： select 函数名（实参列表） 【from 表】；
特点：1.函数名 ； 2.函数实现功能
分类：
1.单行函数
length()；concat 拼接；upper() 大写； lower()小写；substring() ; as 别名 ； ifnull等
1）length 获取字节长度或个数
select length(“hhh账户”);
2)concat拼接字符
select concat(name,id) from employes;
CONCAT(str1,str2,…) 拼接
select CONCAT(help_topic_id,help_category_id) as num FROM help_topic;
#查找表中所有数据以，隔开并命名；
select CONCAT(help_topic_id,‘,’,help_category_id,‘,’,description) as “all” from help_topic;
select * from help_topic WHERE description like “%information%”;
3)upper\lower
select upper(“pengq”);
select lower(“john”);
4)substr、substring剪切字符串
注意：sql中索引从1开始
截取指定索引及后的所有字符
select substr(‘nanli’,2) as output;
截取索引2开始，长度为4的字符
select substr(‘nanli’,2，4) as output;
5）instr 返回子串第一次出现的索引，如果找不到返回0；
select instr(“isabellali”,“lali”) as 索引；
select instr(“isalalibellali”,“lali”) as 索引；
6）trim（）默认去除前后空格，也可以通过设计，去除指定的内容
select length(trim(" lali ")) as length;
select trim(‘a’ from ‘aaaaisabelaaaaa’) as name;
select stdname,TRIM("i"from stdname) from students;
7)lpad 用指定的字符实现左填充长度
select lpad(‘Isabella’,15,‘*’);
rpad用指定的字符实现右填充长度
select rpad(‘Isabella’,15,‘*’);
8)replace替换
select replace(“isabel*****dsd*isab,dnjs…cdkab”,“ab”,“@@”);
用@@代替ab的内容。
2、数学函数
1）round四舍五入。
select round(π)；
select round(-3.14);
select round(3.14159,2);
2)ceil 向上取整,返回大于等于该参数的最小整数
select ceil(3.1415);
select ceil(1.002);
select ceil(-1.002);
floor 向下取整，返回<=该参数的最大整数
select floor(-9.99)
3)truncate截断
select truncate(3.6667);
4)mod取余
mod(a,b)=a-a/b*b
取余的正负值跟被除数一样
select mod(-10,-3);
3、日期函数
返回当前时间日期
select now();
返回当前日期，不包含时间（年月日）
select curdate();
返回当前时间，不包含日期（时分秒）
select curtime();
返回当前指定的年，月，日，时分秒
selet Year(now()) 年 ,month(now()) 月；
str_to_date:将日期格式字符转换成指定格式日期
str_to_date(‘2021-10-16’,‘%Y-%m-%D’);
将日期格式转换为字符串格式
select date_format(now(),“%y年%m月%d日”)；
select order_num,order_date,adddate(order_date,10) from order;
查找当前日期后10天的订单数据，若是查找前10天的用-10
两个时间差异计算使用函数datediff(); 格式例如
select name,datediff(now(),order_date) as 时间差 from order;
查找具体时间的订单：select * from order where date_format(order_date,‘%Y-%m’)=‘2020-7’
2.分组函数
功能：做统计使用，又叫聚合函数，组函数
sum求和 ，avg平均值，max/min最大最小值，count 计算个数
1.sum/avg一般用于处理数值型
2.max/min可以用于任何数据类型
3.以上分组函数都忽略了null值
4.可以和distinct搭配去重运算
count介绍：
select count(*) from students; //统计行数，不会过滤null值
也可以通过count(1)来计算行数，跟count(*)等价
效率：
myISAM存储引擎下，count(*)效率高些
innoDB存储引擎下，count(*)和count(1)差不多，但是效果都高于对字段统计。
6.和分组函数一同查询的字段要求是group by后的字段
分组查询
分组查询一般搭配分组函数使用，查询的字段就是group by后面的字段。例如：
查询学生成绩每个等级的学生个数
select count(*),stdgrdlevel from students group by stdgrdlevel;
select vend_id ，count(*) from products group by vend_id,prod_id;
以两个id进行分组查询，最后再对查询的id进行统计有多少个
having 对分组的数据进行过滤
select vend_id ，count(*) from products group by vend_id having count(*)>2;
（一）分组查询分为2类：
1）分组前的筛选：数据源来自原始表，筛选语句放在group by之前，关键字where
2）分组后的筛选：数据源来自分组后的结果集 ，筛选语句放在group by之后，关键字having
总结：
①分组函数做条件肯定是放在having子句中；
②能用分组前做筛选，尽量用分组前做筛选，性能好些
（二）group by子句支持单个字段分组，多个字段分组（多个字段之间用， 分开）
（三）也可以添加排序放在最后order by
添加分组后的筛选：
查询员工个数>2的部门
1、首先查询每个部门的员工
2.然后对查询后的结果进行筛选（用having筛选）
select count(*) ,dep_id
from employees
group by dep_id
having count(*)>2; // 对前面整个查询结果做筛选
案例：查询每个工资有奖金的员工的最高工资>12000的工种编号和最高工资
1.先查询每个工资有奖金的员工最高工资
分组查询通用模板：
select max(salary) , jog_id
from employees
group by job_id;
然后根据问题看过滤的条件是在group by之前还是之后。例如有奖金是在employees中的字段，可以直接通过where字句过滤
2.根据1的结果来继续筛选最高工资>12000;
select max(salary) , jog_id
from employees
where award is not null
group by job_id
having max(salary)>12000;
如果涉及多个字段分组，将字段名放在group by之后即可，例如：
案例： 查询每个部门每个工种的员工的平均工资
selecr avg(salary) , dep_id,jod_id
from employees
group by dep_id,job_id;
注意事项：
1.where +group by +having可以组合使用：select vend_id from product where price>=10 group by prod_id having count(*)>=2;
2.select后面只能查看group by 子句后面的列，和聚合计算的列
3.group by 可以根据多个列进行分组。多个值相同的分为一组。
group_concat()函数
group_concat(字段名)函数是统计每个指定字段的信息集合。
group by + with rollup 的使用
with rollup的作用是：在最后记录后面新增一行，显示select查询时聚合函数的统计和计算结果。
如下案例：
第一行是展示user_phone是130的这个分组，后面展示的是这个分组中的数量；第二行也是；第三行展示的是分组中user_phone的所有人。
order by 排序
1、ASC升序，DESC降序 ； 默认ASC升序
2、每一次使用只能作用于一个字段，不能调试作用多个字段
select * from employ order by dep_id ASC，emp_id DESC;
3、order by 一般是放在查询语句最后面，除了limit语句外
select * from student order by id asc;升序排序（默认是升序）
select * from student order by id desc; 降序排序
select * from student order by id ，name desc; 以id升序，name降序排序。
asc/desc只对前面一个参数有作用。
四、数据库的表联结
多表连接
通过相关联的字段将三个表进行连接
内连接
内连接
等值连接：通过某一个字段连接两个不同的表进行数据操作。两表中相同列的值相等，则会出现在结果集中。
非等值连接：两个表的连接通过关键字的值不同来进行连接
自连接：
1张表操作:在多个查询条件的时候，可以将这一个表当作多个表进行操作，最后将这个表通过相关联字段连接
select a.name from pro1 a, pro2 b where a.id=b.id and a.id=12453
内连接–等值连接：
select * from a inner join b on a.id=b.id;
外连接
外连接分为左外连接和右外连接，
左连接：select * from a left join b on a.id=b.id; #以左边的表为准，获取内容，右边的若没有值则为null;
右连接：select * from a right join b on a.id=b.id; #以右边的表为准，获取内容，右边的若没有值则为null;
全外连接：select * from a full join b on a.id=b.id; 获取两个标的所有内容
MySQL不支持全外连接，用union来实现的
分页查询
应用场景：当用户访问数据量大，无法一页显示完全，需要分页提交sql请求
语法：
select *
from table
where条件
group by 分组字段
having 分组后的筛选
order by排序的字段
limit 【offset,】size; //offset可选
offset代表是起始索引，从0开始 ； size ：要显示的条目个数
特点：
1.limit语句放在查询语句最后
2.公式：
要显示的页数是page，每页条目数是size
select*
from table
limit (page-1)*size,size;
案例：查询前5条员工信息
select *
from employees
limit 0,5;
案例：查询工资前10名员工信息
select *
from employees
order by salary desc
limit 10;
联合查询union
union联合 ，合并：将多条查询语句的结果合并成一个结果
可以多个union联合使用
语法：
查询语句1
union
查询语句2
union
…
特点：
1.要求多条查询语句的查询列表是一致的
2.要求多条查询条件依据的每一列类型和顺序最好一致
3.union关键字默认去重。可以使用union all关键字取消去重项
4.union关键字只能和一个order by组合使用，order by和limit的优先级最低，想要对整个结果排序就只能放在最后
适用场景：要查询的结果来自多个表，并且查询的信息是一致的。多个表之间没有任关系。
案例：查询部门编号>90或部门名带a的员工信息
select * from dep where dep_id>90
union
select * from dep where dep_name like “%a%”;
五、DML语言的学习
数据插入insert
特点：
1.若没有值要填，那么字段和值需要一起去掉
2.列数跟值的个数必须一致，且需要类型一致
3.可以省略列名，默认所有列，但是顺序和值需要一一对应
语法：insert into table1(字段1，字段2…) values(value1,value2…);
insert into table1 values(v1,v2…); // value值需要跟table1表中的默认字段顺序类型一致
语法2：insert into table1
set 列名=值，列名=值…
insert into student
set sid=121,sname=“edwd”,…;
两种插入方式差异：
1、方式一可以插入多行：
insert into table1
values(v1,v2),
(v3,v4),
…;
2.方式一支持子查询，即将子查询的数据当作value加入表中
insert into table1(zifun,ziduan2)
select * from student;
insert into student(id,name) values(123,pengqi);
插入多行：insert into student values(123,ppp),(456,qqq);
插入其他表查询得到的数据 insert into student (列1，列2) select 列1，列2 from student表B；
数据修改update
update语句如果后面不跟where限制条件，修改的是表中的所有行
更改一个字段值：
update 表名 set 属性=‘’ where id=234;
更改多个字段，用逗号隔开：
update 表名 set 属性=‘’ ， 属性2=‘’ where id=234;
1.修改单表
语法：
update 表名
set 列=新值，列=新值，…
where 筛选条件；
2.修改多表的记录
语法：
update 表名
join/left join/right join表2
on 连接条件
set 列=新值，列=新值，…
where 筛选条件；
使用连接更新表中某个字段数据:
update (goods g inner join cates as c on g.id=c.id) set g.id=c.id;
其实就是把两个表连接当做一个表操作！！
数据删除
方式一：delete
语法：
1.delete from table1 where 条件；
2.删除条件查询出来的结果集，来达到删除数据效果：
delete 表的别名
from 表1
inner 表2 on 连接条件
where筛选条件；
案例：删除表1中的数据
delete a
from 表1 a
inner join 表2 b on 连接条件
where筛选条件；
方式二：truncate 删除表内容和结构，数据库中还有表名
语法：truncate table 表名；
方法三：drop table 表名：删除整个表，数据库中再无表
表备份：
create table student_backup as select * from student;
两种删除的对比差异：
1、truncate效率较高
2、delete 可以加where条件,truncate不可以
3、如果表中有自增长的字段，那么删除一个数据再添加，truncate删除，再插入数据，自增长列从1开始增加。而delete删除是从下一个数据添加。
六、DDL:数据定义语言
1.库的管理
创建库
语法：create database [if not exists] 库名；
create database if not exists dbku;
修改库
alter database dbku character set gbk; // 更改库字符集
删除库
drop database [if exists] dbku;
2.表的管理
表的创建
create table 表名（
列名 类型【（长度）约束】，
，，，
）
create table students(
id int primary key,
sname varchar(24)
)
创建表的同时将已知的数据插入表中。
create table goods_brands(
id int unsigned not null primary key auto_increment,
name varchar(50) not null ) select brand_name *as name* from goods group by brand_name;
说明：
1.在goods表中查出所有的品牌名称
2.创建一个表的同时，将上一步查到的数据插入。注意创建属性的名称要与插入的查询结果的数据名称保持一致，因此，通过别名as来实现
小窍门：
可以将多个表查询的结果作为一个表，对这个表进行操作。
如：将goods表中的品牌名称更改为对应表中的品牌id
update goods as g inner join good_brands gb on g.brand_name=gb.name set g.brand_name=gb.id
其中goods as g inner join good_brands gb on g.brand_name=gb.name 这个表其实就是两个表连接查询的结果当作是一个表。然后对其修改，而这个修改操作会修改更新到对应的表中，比如此时的goods中的brand_name就会更新。
修改表
alter table表名 change /modify/add/drop/ column 列名【约束 类型】；
1.修改列名
alter table students change column sname stuname varchar;
//将sname列名修改为stuname
2.修改类型或约束
alter table students modify column id varchar;
3.添加列
alter table students add phone int；
4，删除列
alter table students drop clomn phone;
5.修改表名
alter table students rename to stu;
删除表
drop table 【if exists】表名；
表的复制
1.复制表的结构
create table copy1 like students;
2.复制表的结构+数据
create table stu
select * from employees;
3.仅仅复制列名
create table stu
select id
from studnets
where 0; // 使得条件不满足数据
创建表时添加的约束
通用写法：
create table if not exists stuinfo{
id int primary key ,
sname varchar(20) not null,
gender char(1),
seat int unique,
marjorid int ,
constraint fk_stumajor foreign key(majorid) references major(id)
}
主键和唯一的对比：
| 保证唯一性 | 是否允许为空 | 一个表中可以有多少个 | 是否允许组合 | |
|---|---|---|---|---|
| 主键 | ✔ | ✘ | 至多有一个 | 是，但是不推荐 |
| 唯一 | ✔ | ✔ | 可以有多个 | 是，但不推荐 |
外键：
1.要求在从表设置外键关联
2.外键字段从表的类型和主表的类型需要一致，类型也要一样
3.主表的关联列必须是一个key(一般是主键或是唯一键)
4.插入数据时，需要先插入主表，再插入从表；删除数据时，需要先删从表，再删主表
事务TCL（transaction control language）
事务就是用户定义的一系列执行sql语句的操作，这些操作要么完全都执行，要么完全都不执行，它是一个不可分割的工作单元。
常用的表的存储引擎是InnoDB和MyISAM , InnoDB是支持事务的；MyISAM不支持事务，但是它的访问速度快，对事务没有要求。
查看当前数据库支持的数据引擎：show engines;
修改表的存储引擎：alter table students engine=‘MyISAM’;
事务特性
- 原子性：一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚。对于一个事务来说，不可能只执行其中一部分操作，这就是事务的原子性。例如银行转账：一个用户已执行转出，另一个用户转入失败，那就会回滚回到最初状态，相当于两个账户没有做任何操作。
- 一致性：数据库总是从一个一致性的状态转换到另一个一致性的状态。比如从A账户减少了，到B账户增加了。
- 隔离性：通常来说，一个事务所做的修改操作在提交事务之前，对于其他事务来说是不可见的。（比如前面说的转账：转出用户执行完，转入用户还未执行，那么其他用户是看不到转出用户已经执行了的这个操作。）
- 持久性：一旦事务提交，则其所做的修改会永久保存到数据库。即使数据库出现故障，数据也不会丢失。
事务的创建
隐式事务：事务没有明显的开启和结束标记
例如：insert \updata\delete
显式事务：事务具有明显的开启和技术标记
前提：必须先设置自动提交功能为禁用
set autocommit=0; /关闭事务自动提交
语法：
1.开启事务
begin；
或
start transaction ;可选的
2.编写事务的sql语句；
3.结束事务；
commit;提交事务
rollback;回滚事务（有出现问题，则会回滚，该次操作不生效）
对于同时运行的多个事务，当这些 事务访问数据库中相同的数据 时，如果没有采取必要的隔离机制，则会导致各种并发问题：
1.脏读：对于两个事务T1,T2 ，T1读取了已经被T2 更新但是还没提交的字段 之后，若T2回滚，则T1读取的内容就是临时无效的；
2.不可重复读：对于两个事务T1,T2，T1读取了一个字段，后面T2更新了该字段，T1又读取了同一个字段，值就不同了；
3.幻读：对于事务T1,T2，T1读取了事务，T2在该表中 插入了新的行，之后T1在读取该表，就会出现多几行。
数据库事务的隔离性：数据库系统必须具有隔离并发运行各个事务的能力，使他们不会相互影响，避免各种并发问题。
每启动一个mysql程序，就会获得一个单独的数据库连接，
每个数据库连接都有一个全局变量@@tx_isolation ,表示当前的事务隔离级别
查看当前隔离级别：
select @@tx_isolation;
设置mysql连接隔离级别：set transaction isolation level read commited;
设置数据库系统全局隔离级别：set global transaction isolation level read commited;
| 脏读 | 不可重复读 | 幻读 | |
|---|---|---|---|
| read uncommited | √ | √ | √ |
| read commited | × | √ | √ |
| repeatable read | × | × | √ |
| serializable | × | × | × |
Mysql中默认隔离级别是repeatable read ； oracle默认隔离级别是read commited
×表示不会出现的问题；√表示会出现的问题
回滚点：savepoint
set autocommit=0;
start transaction;
insert into s(id,sname) values(1,“cdjkll”);
savepoint a;#设置保存点
insert into s(id,sname) values(2,“小马ll”);
rollback to a; #回滚到保存点处
综上，只回滚了id=2的 sql，执行了id=1的sql语句。
视图
视图，即虚拟表，和普通表一样使用；是普通表动态生成的数据，只保存了sql逻辑，不保存查询结果
应用场景：
- 多个地方用到同样的查询结果
- 该查询结构使用的sql语句较复杂
语法：
create view 视图名
as
查询语句；
例如查找名字姓张的学生信息
select sname,mname
from student
inner join major
on s.id=m.id
where sname like “彭%”;
用视图写：
create view my_view
as
select sname,mname
from student
inner join major
on s.id=m.id
select * from my_view where sname like “彭%”;
视图优点：
1、重用sql语句
2、简化复杂的sql操作
3、保护数据，提高安全性
修改视图
方法一：
create or replace view 视图名
as
查询语句；
方法二：
alter view 视图名
as
查询语句；
删除视图：
drop view 视图1，视图2；
查看视图j结构：
desc myview;
show create view myview;
一般视图层，只有读取的权限，具有以下特点的视图不允许更新：
1.包含分组函数：group by ,distinct ,having,union
2.join
3.常量视图
4.where后的子查询用到了from中的表
5.用到了不可更新的视图
视图和表的对比：
| 关键字 | 是否占用物理内容 | 使用 | |
|---|---|---|---|
| 视图 | view | 占用空间较小，只保留sql逻辑 | 一般用于查询 |
| 表 | table | 保存实际的数据 | 数据增删改查都常用 |
存储过程
含义：一组预先编译好的sql语句的集合。它们通常用于执行特定的任务或操作。存储过程可以包含SQL查询、流程控制语句（如条件判断和循环）、变量声明和赋值、异常处理等，从而实现复杂的业务逻辑和数据操作。存储过程可以被多次调用，提高了代码的重用性，并且可以在数据库服务器上进行执行，减少了网络传输开销。
优点：
①提高代码的复用性
②简化操作
③减少编译次数，并且减少了和数据库服务器的连接次数，提高系统性能。
④安全性：通过存储过程对数据访问进行权限控制。
1、创建语法
create procedure 存储过程名（参数列表）
begin
存储过程体（一组合法的sql语句）
end
注意：
1）参数列表包含三部分
参数模式、参数名、参数类型
参数模式：
in : 可以作为输入 ，也就是传入值
out：可以作为输出，也就是返回值
inout：既可以作为输入，又可以作为输出
2）假如存储过程体仅仅只有一句话，则begin end 可以省略
存储过程体的每条sql语句的结尾要求必须加分号，存储过程的结尾可以使用delimiter重新设置
语法：delimiter 结束标记
例：delimiter $
2、调用语法
call 存储过程名（实参列表）；
1.空参列表
例：插入5条数据
select * from myp1()
begin
insert into admin(username,pwd)
values(‘romio’,‘111’),(‘zhuliy’,‘222’);
end $
调用
call myp1()$
2.创建带in模式参数的存储过程
例：创建存储过程实现 ，根据女神名，查询对应的男神信息
delimiter $
create procedure my13(IN girlname varchar(20))
begin
select bo.*
from boy bo inner join girl g on bo.boyid=g.boyfriendid
where g.girlname=girlname;
end $
调用方法：
call my2(“杨颖”)；
创建存储过程实现，用户是否登录成功(带in的存储过程)
create procedure my3(IN uname varchar(20),IN pwd varchar(20))
begin
DECLARE result varchar(20) default 0;声明并初始化
select count(*) into result
from student s
where s.uname=uname and s.pwd=pwd;
select if (result>0 ,“登录成功”,“登录失败”);
end $
调用
my3(“xiaohong”,"123456)$
带out的存储过程
例：根据女神名，返回对应的男神名
delimiter $
create procedure my15(IN girlname varchar(20), OUT bname varchar(20))
begin
select b.boyname into bname
from boy b
inner join girl g on b.boyid=g.boyfriendid
where g.girlname=girlname;
end $
调用
set bname$ #定义，也可以直接用out的变量名
call my15(“杨颖”,@bname);
select @bname;
或者
delimiter $
call my15(“杨颖”,@bname)$
select @bname$ #查看
带inout的存储过程
例：传入a,b两个值，最终a和b都翻倍并返回
delimiter $
create procedure my6(INOUT a int,INOUT b int)
begin
set a=a2;
set b=b2;
end $
调用
set @m=2$
set @n=3$
call my6(@m,@n)$
select @m,@n$
二、删除存储过程
语法：drop procedure 存储过程名
drop procedure my2;
三、查看存储过程的信息
show create procedure my2;
例：创建存储过程或函数，实现传入女神名，返回女神 and 男神 格式字符串，如：杨颖 and 黄晓明
drop PROCEDURE my10;
create PROCEDURE my10(IN girlname varchar(20),OUT str VARCHAR(20))
begin
select CONCAT(girlname,“and”,IFNULL(boyname,“null”)) into str
from girl g inner join boy b on g.boyfriendid=b.boyid
where g.girlname=girlname;
end $
call my10(“杨颖”,@str)$
select @str$
疑问？？
六、函数
基本语法
一、创建语法
create function 函数名（参数列表） returns 返回类型
begin
函数体
end
注意：
1.参数列表包含两部分：函数名 ， 参数类型
2.函数体：肯定会有return语句，如果没有会报错
return 值；
3.函数体中仅有一句话，则可以省略begin end
4.使用delimiter语句设置结束标记
二、调用语法
select 函数名（参数列表）
1.无参有返回
set global log_bin_trust_function_creators=TRUE;
delimiter $
create function myfun1() returns int
begin
DECLARE c int default 0;
select count(*) into c
from help_topic;
return c;
end $
调用：
select myfun()$
执行时报错，需要添加：set global log_bin_trust_function_creators=TRUE;
参考链接：https://blog.csdn.net/qq_40990854/article/details/84194344?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522163678896416780261926574%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fall.%2522%257D&request_id=163678896416780261926574&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2allfirst_rank_ecpm_v1~rank_v31_ecpm-2-84194344.first_rank_v2_pc_rank_v29&utm_term=%E6%89%A7%E8%A1%8C%E5%87%BD%E6%95%B0%E6%8A%A51418%E9%94%99%E6%88%91&spm=1018.2226.3001.4187
2.有参数有返回
例：根据员工名，返回工资
create function myfun2(epname varchar(20)) returns double
begin
set @s=0; #定义用户变量
select salary into @s
from emp
where emp.ename=epname;
return @s;
end $
select myfun2(“马云”)$
三、查看函数
show create function myfun1;
四、删除函数
drop function myfun1;
创建函数，实现传入两个float ， 返回二者之和
create function test_fun(num1 float,num2 float) returns float
begin
declare sum float default 0;
set sum=num1+num2;
return sum;
end $
select test_fun(1,2)$
流程控制结构
顺序结构：程序从上往下依次执行
分支结构：程序从两条或多条路径中选择一条路去执行
循环结构：程序在满足一点条件的基础上，重复执行一段代码
if函数
语法：
if（表达式1，执行语句2，执行语句3）
如果表达式1成立，则执行语句2；否则则执行语句3；
select if(10>5,“大”,“小”)；
case 结构
情况1：一般用于实现等值判断
语法：
case 变量|表达式|字段（要判断的字段或表达式）
when 要判断的值 then 返回的值或语句；
when 要判断的值 then 返回的值或语句；
。。。
else 要返回的值或语句；
end
例：
如果学生分数=85 A奖励￥100 ；B 70-84 ￥50; C 60-69 ￥10;D 0-60 没有奖励;
SELECT stdgrdlevel as “分数等级”,
case stdgrdlevel
when ‘A’ then 100
when ‘B’ then 50
when ‘C’ then 10
else 0
end as 奖励
from students;
情况二：一般用于实现区间的判断
语法：
case （注意case后面没有任何东西）
when 要判断的条件 then 返回的值或语句；
when 要判断的条件 then 返回的值或语句；
。。。
else 要返回的值或语句；
end
特点：
可以作为表达式或值，嵌套在其他语句中使用，可以放在任何地方，begin end中或外面
可以作为独立的语句使用，只能放在begin end中
例：查询分数对应的等级
create procedure gradelevel(IN score int )
begin
case
when score>=90 and score<=100 then select ‘A’;
…
ELSE SELECT ‘D’;
end $
例2：
查询：如果学生分数>85 A; 70-84 B; 60-69 C;0-60 D;
SELECT stdgrade,
case
when stdgrade>=85 then 100
when stdgrade>=70 then 50
when stdgrade>=60 then 10
else 0
end as 奖励
from students;
循环结构
分类：
while\loop\repeat
循环控制：
iterate：结束本次循环，继续下一次循环
leave 结束当前所在的循环
while
语法：
【标签：】while 循环条件 do
循环体；
end while 【标签】；
loop
语法：
【标签：】 loop
循环体；
end loop 【标签】；
可以用来模拟兼容的死循环
repeat
语法：
【标签：】repeat
循环体；
until 结束条件
end repeat【标签】；
案例：批量插入，根据次数插入到admin表中多条记录
delimiter $
create procedure prowhile2(IN insertcount INT)
begin
DECLARE i int default 1;
while i<insertcount do
insert into girl(girlname,boyfriendid) values(CONCAT(‘LEE’,i),i);
set i=i+1;
end while;
end $
delimiter $
call prowhile2(10)$
select * from girl;
插入数据，当添加超过20条则停止
delimiter $
create procedure prowhile2(IN insertcount INT)
begin
DECLARE i int default 1;
a:while i<insertcount do
insert into girl(girlname,boyfriendid) values(CONCAT('LEE',i),i);
if i>=20 then leave a;
end if;
set i=i+1;
end while a;
end $
delimiter $
call prowhile2(100)$
select * from girl;
MySQL基础小结：
系统变量
由系统定义，无需用户定义的变量
①查看系统变量
show 【global|session】 variables like ’ '; 如果没有显式声明global 还是session，则默认是session
查看所有的系统变量
show global |session variables;
查看满足条件的部分系统变量
show global |session variable like “%张三%”；
②查看指定的系统变量的值
select @@【global|session】.变量名； 如果没有显式声明global还是session,则默认是session
③为系统变量赋值
方法一：set 【global|session】 变量名=值； 没有显式声明，则默认是session
方式二： set @@global.变量名=值； #全局变量
set @@变量名=值； #局部变量
1.全局变量
服务器层面上的，必须拥有super权限才能为系统变量赋值，作用域为整个服务器，一次对所有连接的会话都有效
2.会话变量
服务器为每一个连接的客户端都提供了系统变量，作用域为当前的连接（会话）
自定义变量
1、用户变量
作用域：针对的当前连接（会话）有效
位置，可以放在begin end里面也可以放在外面
声明并赋值的三种方式：
set @变量名=值；
set @变量名：=值；
select @变量名：=值；
赋值（更新用户变量的值）：
方式一：
set @变量名=值；
set @变量名：=值；
select @变量名：=值；
方式二：select xx into @变量名 from 表；
使用：select @变量名；
2、局部变量
作用域：只在begin end中有效
位置，只能放在begin end中的第一句
声明并赋值
declare 变量名 类型【default 值】；
赋值：
方式一：
set @变量名=值；
set @变量名：=值；
select @变量名：=值；
方式二：select xx into 变量名 from表；
使用：select 局部变量名；
七、MySQL的原理和调优：
先简单描述下，从以下几个点去优化：
说说mysql数据存储过程的原理
存储过程是一个可编程的函数，它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结果组成。当希望在不同的应用程序或平台上执行相同的函数，或者封装特定的功能时，存储过程是非常有用的。数据库中的存储过程可以看作是对编程中面向对象的方法的模拟。它允许控制数据的访问方式。
存储过程有以下优点：
1.存储过程能实现较快的执行速度
2.存储过程允许标准组件是编程
3.存储过程可以用流程控制的语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算
4.存储过程可被作为一种安全机制来充分利用
5.存储过程能减少网络流量
索引
索引是一个数据库对象，也是一个特殊的文件，它保存着数据表里所有记录的位置信息。使用索引，可以使数据库程序无须对整个数据进行扫描，可以在其中找到目标数据，从而提高查找效率。
索引：运用索引的数据结构（B+数）会使得数据查询速度更快。
存放的位置：
windows，索引存放在c:/programedata下得隐藏文件中。
- 对于innoDB存储引擎的表：将索引和数据存放在一起
- 对于MyISAME存储引擎的表：索引和数据分成两个文件来存储。索引：*.MYI；数据MYD
索引分类
- 主键索引：主键自带索引效果
- 普通索引：为普通列创建索引
创建索引：
命令：create index 索引名称 on 表名（列名）
create index idx_name on student(name);
或alter table student add index (name);
查询表中的索引：show index from 表名；
唯一索引：就是唯一列，列中的数据是唯一的，比普通索引性能好
创建唯一索引：
create unique index idx_name on student(name)
联合索引(组合索引)：一次性为表中的多个字段一起创建索引。最左前缀法则。注意：一个联合索引建议不超过5个列
create index ixd_name_age on student(name,age)
全文索引：
进行查询的时候，数据源可能来自不同的字段或是不同的表，例如去百度中随机查询某些资料内容。
MyISAM存储引擎支持全文索引。在实际生产环境中，并不会使用MySQL提供的MyISAM全文索引来实现全文查找。而是用第三方的搜索引擎中间件，如：ElasticSeach,Solr
索引使用的数据结构
使用索引查找数据性能很快，避免了全表扫描的多次磁盘io读写。但是索引实际上也需要在索引中查找数据，而且数据量是一样的。为什么索引查询会更快呢？这就跟索引使用的数据结构支持快速查找有关了。
验证索引性能操作：
--开启运行时间监测
set profiling=1;
-- 查找1w条数据
select * from test ;
--查看执行时间：
show profiles;
--给title字段创建索引：
alter table test add index (title);
--再次执行查询语句
select * from test;
--再次查看执行时间
show profiles;
什么是数据结构？存放数据的结构，例如：数组，栈，堆，队列等等
1、线性表：
对于线性表来说，有两种数据结构：
1）线性顺序表：前后连接的数据逻辑关系和物理位置相对应。
2）线性链式表：两个数据的逻辑关系和物理存放位置没有关系。数据有先后逻辑关系，但是数据的物理存储位置并不连续。
单向链表：能够通过当前结点找到下一个节点的位置。
结点结构：数据内容下一个数据指针
双向链表：能够通过当前结点找到上一个或下一个结点的位置，双向都可找。
结点结构：上一个数据的指针+数据内容+下一个数据的指针
顺序表和链式表的区别：
-
数组：进行数据的查询性能（可以通过数组的索引/下标来快速实现） 时间复杂度（比较次数）/ 空间复杂度（算法需要使用多少个变量空间）
查询性能好，增删性能差。 -
链表：查询的性能差：时间复杂度O(n)，增删性能好。
聚集索引和非聚集索引的区别
聚集索引：根据记录的key在表中排序数据行
非聚集索引：独立于记录的结构，非聚集索引包含的key,且每个键值项都有指向该键值的数据行的指针。
区别：
1.聚集索引的物理存储按索引排序，非聚集索引的物理存储不按索引排序；
2.聚集索引插入、更新数据的速度比非聚集索引慢，但查询速度更快；
3.聚集索引的叶级结点保存的是时间的数据项，而非聚集节点的叶级结点保存的使指向数据项的指针；
4.一个表只能有一个聚集索引（因为只要一种排序方式），但可以有多个非聚集索引；
栈，队列，串，广义表
栈：先进后出
队列：先进先出
串：定长串，stringBuffer/stringbuilder动态串
广义表：更加灵活的多维数组
树
查找树的查找性能明显比线性的性能好，接下来了解不同的“树”：
- 多叉树：非二叉树，有根节点的子节点大于2个的结点
- 二叉树：所有的结点中，最多只能有2个子结点，可以是0个，1个，2个
- 二叉查找树：二叉查找树的性能ok，特点：二叉树的根节点的数值比所有左边的节点数大，比右边结点的树值小。
- 平衡二叉树：所有左子树和右子树之间的深度差不能超过1。如果平衡二叉树不满足特点，那么会自己旋转。
- 红黑树：每个叶子结点是黑色的，有两个黑色叶子结点的根结点是红色的。红黑树自旋会跟系统的开销进行权衡。
- B树：允许一个结点存放多个数据，这样可以使更小的树的深度，存放更多的数据。但是B树的一个节点多能存放多少个数据，决定了树的深度。
- B+树：非叶子结点冗余了叶子结点中的键；叶子结点是从小到大，从左到右排列；叶子结点之间提高了指针，提高了区间访问的速度；叶子结点存放数据，非叶子结点不存放数据，只存放键。
使用哈希表来存储数据的性能是最快的，O（1），但是不自持范围查找。
InnoDB和MyISAM区别
InnoDB是聚集索引：把索引和数据存放在一个文件中，通过找到索引后就能直接在索引树上的叶子结点中获取完整的数据。
MyISAM是非聚集索引：将索引和数据存放在两个文件夹，查到索引后还要去另一个文件中查找数据，性能会慢一些。除此之外，MyISAM天然支持表锁，而且支持全文索引。
联合索引和最左前缀法则
1、联合索引的特点
使用一个索引来实现多个表中的索引效果。
2、联合索引是如何存储的？
将索引的数值，通过键值对形式存储在索引树中，最下面的是主键的值
3、最左前缀法则
表示一条sql语句在联合索引中，有没有走索引（命中索引/不会走全表扫描）
创建联合索引：
create index idx_a_b_c on table(a,b,c)
sql语句有没有使用索引
select * fromtable1 where a=10; #会有走a的索引
select * fromtable1 where a=10 and b=20; #会有走a，b的索引
select * fromtable1 where a=10 and b=20 and c=30;#会有走a，b,c的索引
select * fromtable1 where a=10 and c=30; and b=20 ；（abc索引全走，mysql的内部优化器会做一次内部优化）
select * fromtable1 where a=10 and c=30; #走的是a的索引
select * fromtable1 where b=20; #不会走索引
select * fromtable1 where b=20 and c=30; #不会走索引
select * fromtable1 where c=30;#不会走索引
常见面试题：
问题一：为什么B+树中，非主键索引的叶子结点存放的数据是主键值？？？
如果是在普通索引上不存放主键，放完整的数据，那么会造成：
1、数据冗余，虽然提升了查询性能，但是需要更多的空间来存放冗余的数据，会造成空间浪费。
2、维护麻烦。若有多个地方引用，则需要修改很多地方，维护更加困难。
问题二：为什么innoDB表必须创建主键
因为如果不创建主键，MySQL优化器会生成一个虚拟主键，于是普通索引会使用这个虚拟主键----会造成性能开销，为了性能考虑，创建表时就应该创建主键。
问题三：为什么使用主键时推荐使用整型的自增主键？
1）为什么使用整型
主键索引树-树里的叶子结点和非叶子结点的键存放的是主键的值，而且这个树是一个二叉查找树，数据的存放是有大小顺序的
- 整数：大小顺序很好比较，简单
- 字符串：字符串的比较，需要先进行一次编码成为数值后才能进行比较
2)为什么要自增
如果不用自增，使用无规律的整数作为主键，那么主键索引树会使用更多的自旋次数保证索引树的叶子结点数据是从小到大，从左到右排列，一次性能肯定比使用自增的主键性能差！
八、SQL优化
优化目的：为了sql语句能够具备更优秀的查询性能，提高查询效率
优化的多个途径：
- 工程优化：数据表的结构标准，字段，索引命名规则
- SQL语句优化：当前sql查询有没有命中索引
1、工程优化
1）基础规范
- 表存储引擎必须使用innoDB
- 表字符集默认用utf8，必要时使用utf8mb4(表中有使用表情符号时)
- 禁止使用存储过程，视图，触发器，event(影响数据库的性能，能不用数据库干的事情，尽量避免在数据中处理；调试。迁移困难，扩展性差)
- 禁止在数据库中使用大文件，如视频，图片。可以将大文件存储在对象存储系统，数据库中存储路径
- 禁止在线上环境做数据库压力测试
- 测试，开发，线上数据库环境必须隔离
2）命名规范
- 库名，表名，列名必须用小写，采用下划线分隔
- 库名，列名，表名必须见名知意，不要超过32字符
- 库备份必须已bak为前缀，日期为后缀
- 从库必须以-s为后缀
- 备库必须以-ss为后缀
3）表设计规范
- 单实例表必须控制在2000个以内
- 单表个数控制在1024个以内
- 表必须有主键，推荐unsigned整数为主键
- 禁止使用外键，如果要保证完整性，使用应用程式实现（外键使得表之间相互耦合，影响sql性能，可能会造成死锁，高并发情况下，容易成为瓶颈）
- 建议大字段，访问频率低的字段拆分到单独的表中存储，分离冷热数据
4）列设计规范
- 根据业务区分使用tinyint/int/bigint ， 分别会占用1/4/8字节
- 根据业务区分使用char/varchar
- 字段长度 固定，或长度类似的业务场景，适合使用char，减少碎片，查询性能高
- 字段长差异大的，或更新较少业务场景，使用varchar，减少空间 - 必须把字段定义为not null，并设置默认值
- null的列使用索引，索引统计，值都更加复杂，mysqk更难优化
- null需要更多的存储空间
- null只能采用 is null 或是is not null ，在=/!=/in/not in有大坑 - 使用int unsigned存储ipv4，不要用char
- 使用varchar存储手机号，不要用整数。varchar可以模糊查询
- 使用tinyint代替enum . enum增加新值要DDL操作
5）索引规范
- 唯一索引使用uniq_[字段名]来命名
- 非唯一索引用idx_[字段名]命名
- 单张表索引数量建议控制在5个以内
- 互联网高办法业务，太多索引会影响写性能
- 生成执行计划时，如果索引太多，会降低性能，并可能导致mysql选择不到最优索引
- 异常复杂的查询需求，可以选择ES等更为合适的方式存储 - 组合索引字段数不建议超过5个
- 不建议在频繁更新的字段上建立索引
- 非必要不要进行join查询。如果要进行join查询，被join的字段必须类型相同，并建立索引。若类型不同，会导致全表扫描
- 理解组合索引最左前缀法则，避免重复建索引
数据库的优化？
SQL语句的优化
SQL性能查询工具—Explain
SQL优化神器—Explain执行计划
知道当前系统有哪些sql是慢sql(查询性能超过1s的sql)，通过explain对当前的sql语句性能进行判断分析。慢的原因，该怎么解决？
想要知道哪些sql是慢sql，有两种方式：
1、开启本地MySQL的慢查询日志
2、将mysql部署在第三方部署的mysql服务器（阿里云rds），已经提供了查询慢sql的功能
explain select * from student where name like "琪"
通过在SQL语句前面加上explain关键字，执行后并不会真正的执行mysql语句本身，而是通过explain工具来分析当前sql语句的性能：比如什么样的查询类型，可能用到的索引及实际用到的索引，和一些额外的信息。
MySQL的内部优化器
某些简单的字段内容会直接从索引树上获取，不一定都会从表中获取
explain之select_type和table
explain之type列
type列可以直观的判断出当前的sql语句的性能，type里的取值和性能的优劣顺序如下：
null > system > const > eq_ref > ref > range > index > all
对于sql优化来说，要尽量保证type列的值是属于range及以上级别。
-
null
性能是最好的，一般在使用聚合函数操作索引列，直接从索引树上获取数据。 -
system
很少见，直接和一条记录进行匹配 -
const
当前用主键索引或唯一索引，和常量进行比较，性能非常好。 -
eq_ref
在进行多表连接查询时，查询条件时使用了主键进行比较，那么当前查询类型是eq_ref -
ref
简单查询，如果是以普通列作为查询条件，那么就是ref类型
复杂查询，如果查询条件是普通列索引，那么类型时ref -
range
在索引上使用了范围查找，性能okexplain select* from table2 where id>1; -
index
在查询表中的所有吉利，但是所有的记录可以直接从索引树上获取，此类型就是indexexplain select * from table1; -
all
全表扫描。
索引的相关属性：
-
id列
在多个select中，id越大越先执行，如果id一样，先执行上面的。 -
possible keys列
表示这一次的查询可能用到的索引，也就是mysql内部优化器会进行判断。如果这一次查询走的索引性能比全表扫描的性能要差，则内部优化器就会让此次查询进行全表扫描--------通过trace工具来查看explain select * from *student where name like '明' -
key列
实际该sql语句使用的索引 -
rows列
该sql语句可能要查询的数据条数 -
key_len列
通过该键的长度，可以知道此时该语句命中了哪些联合索引。计算规则： explain select * from table1 where name='hi' and age=32 and position='dsc' 1.字符串型 char(n):n字节长度 varchar(n):如果是utf-8则长度是3*n+2 name在数据库结构中查看，设置的数据长度为24：所以多个命中name索引的语句此时长为3*24+2=74 2.数值型 tinyint：1字节 smallint:2字节 int:4字节 bigint:8字节 3.时间类型 date:3字节 timestamp:4字节 datetime:8字节 如果允许字段为null，则需要1字节来记录是否为null， 索引最大长度是768字节，当字符过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。 -
extra列
提供了额外的信息，帮助判断当前sql是否使用了覆盖索引、文件排序，使用了索引进行查询等信息-
using index:使用了覆盖索引
覆盖索引：当前查询的所有的数据字段都是索引列，可以直接从索引树上获取数据，不需要查表。explain select id，name from table1 where id=1;#使用了覆盖索引 explain select * from table1 where id=1;#没有使用覆盖索引 -
using where
使用了普通列做索引查询 -
using index condition
查询结果没有使用索引，建议用覆盖索引来优化 -
using temporary
会创建临时表来执行，比如在没有索引的列上执行去重操作。可以通过增加索引来优化 -
using filesort
MySQL会对数据进行排序，都会使用磁盘来完成，可能会借助内存 -
using tables optimized away
直接在索引树上使用聚合函数，意味着不需要操作表
-
**
不能在索引列上做计算，函数，类型转换，否则索引会失效造成全表扫描
**
trace工具
SQL优化实战
1.order by优化
在排序应用场景中很容易出现文件排序的问题。文件排序会对sql语句查询的性能造成影响，因此需要优化。
例子：
优化手段：
- 如果排序的字段创建了联合索引，尽量在业务不冲突的情况下，遵循最左前缀法则来写排序语句。
如：Explain select * from student where name=‘san’ order by position; #使用了文件排序
优化后：Explain select * from student where name=‘san’ order by age,position; #使用了联合索引，没有使用文件排序。 - 如果文件排序没法避免，那么尽量想办法使用覆盖索引，all–>index
数据库安全
sql注入是如何产生的，如何防止？
当服务器使用请求参数构造SQL语句时，恶意的SQL被嵌入到sql中交给数据库执行。SQL注入攻击需要攻击者对数据库结构有所了解才能进行。
程序开发中，不注意规范书写sql语句和对特殊字符进行过滤，也会导致客户端可以通过全局变量POST和GET提交一些恶意sql语句被执行，导致产生的sql注入。
防止措施：
1.过滤掉一些常见的数据库操作关键字，或者通过系统函数来进行过滤。
2.在PHP配置文件中将register_globals=off ；设置为关闭状态。
3.sql语句书写的时候尽量不要省略小引号和单引号。
4.提高数据库命名技巧，对于一些重要的字段根据程序的特点命名，取不易被猜到的。
5.对于常用的方法加以封装，避免直接暴露sql语句。
6.开启PHP安全模式：safe_mode=on;
7.打开magic_quotes_gpc来防止sql注入；
8.控制错误信息，关闭错误提示信息，将错误信息写道系统日志上。
9.使用mysqli或pdo预处理。---------------？？待了解补充
攻击者想要获得表结构有多种方式：
数据库面试题
1.进行多表联查时，应该注意什么
a）多表联查的关键是找出表与表之间的关联字段，一般设计表的时候会根据具体业务和模块将数据拆分保存到多个表中间，在一个表中设计字段指代另一个表的字段，从而保证数据间的关系。比如外键。
b）另外多表联查需要根据需要选择合适的连接查询方式，如内连接，外连接等
2.如何查询结果进行条件映射归集
答：条件查询相当于对结果集进行的过滤筛选。这时候我们会使用where关键字，后面使用一个或多个条件表达式，多个条件表达式之间使用and 或or进行连接
3.对某个表的值进行按天数统计总条数，总金额数，如何编写这个sql
select date_format(datetime,‘%Y%m%d’) as 日期 ,count(*) as 总条数,sum(money) as 金额
from table_name
where 条件值(Sep=“test”)
group by date_format(datetime,‘%Y%m%d’)；
6万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
