MySQL不熟悉知识点
一、基础部分
-
insert操作
insert into 表名(字段1，字段2) values(值1，值2);
-
update操作
update 表名 set 列名1=值1，列名2=值2··· where ···; -
delete操作
delete from 表名 where ···; -
select
image-20201203195411841-
distinct关键字可以去除重复
image-20201203201819343 -
排序降序DESC
select * from emp order by sal DESC,empno;--sal降序排列，sal相同则按照empno升序 having区别于where，having在分组后（group by）进行过滤
-
limit
select * from emp limit 0,5; --0,5没有括号！ -
顺序
查询语句的书写顺序：select-from-where-group by-having-order by-limit
查询语句的执行顺序：FROM-WHERE-GROUP BY-HAVING-SELECT-ORDER BY-LIMIT
-
-
主键
-
方式一
create table stu( sid int primary key, sname varchar(20) ); -
方式二
CREATE TABLE student1( sid INT, sname VARCHAR(20), PRIMARY KEY(sid) ); -
方式三：创建联合主键——同时参照所有主键，都完全相同视为数据不唯一，所有键联合为一个主键。如下例，只有当
sid,score这两个都相同时，才算作主键重复CREATE TABLE student1_1( sid INT, sname VARCHAR(20), score DOUBLE, PRIMARY KEY(sid,score) ); -
追加添加主键（alter）
-- 在表格外部给表添加单主键 CREATE TABLE student2( sid INT, classid INT, sname VARCHAR(20) ); ALTER TABLE student2 ADD CONSTRAINT PRIMARY KEY(sid); -- 在表格外部给表格添加联合主键 CREATE TABLE student2_1( sid INT, classid INT, sname VARCHAR(20) ); ALTER TABLE student2_1 ADD PRIMARY KEY(sid,classid); --CONSTRAINT 关键字可以省略
-
-
unique唯一约束，可以有null
CREATE TABLE student3( sid INT , sname VARCHAR(20) UNIQUE ); -
auto_increment自动增长列
CREATE TABLE student4( sid INT UNIQUE AUTO_INCREMENT, sname VARCHAR(20) ); CREATE TABLE student4_1( sid INT PRIMARY KEY AUTO_INCREMENT, sname VARCHAR(20) ); -
外键约束
格式：
constraint 外键名称 foreign key(字段名1,字段名2···) references 从表的名称(从表主键列)CREATE TABLE connStudent( sid INT PRIMARY KEY, sname VARCHAR(20), sbirthday DATE ); CREATE TABLE connScore( id INT, sid INT, sscore DOUBLE, CONSTRAINT fk_connScore_connStudent FOREIGN KEY(sid) REFERENCES connStudent(sid) ); -
连接查询（实质也是内连接，但不是标准格式）
-
运用主外键去除无用信息
select * from emp,dept where emp.deptno=dept.deptno;指定别名
SELECT e.ename,e.sal,e.comm,d.dname FROM emp e,dept d WHERE e.deptno=d.deptno;
-
-
内连接（标准格式，inner join ··· on···）
SELECT e.ename,e.sal,e.comm,d.dname FROM emp e INNER JOIN dept d ON e.deptno=d.deptno; -
外连接（分为左连接和右连接）
外连接可以分为左连接和右连接，两种连接方式大同小异，只是左连接按照条件查询时参照左边的表的值，右连接按照参照条件参照右边表的值。当两个表的查询条件都满足时，查询出来的结果与内连接相同；当查询只满足左边查询时，使用左查询可以查出表，右表部分使用null填充，使用右连接无法查询出这条记录；当查询只满足左边查询时道理相似。
例子来说明：表中emp表中“张三”这条记录中，部门编号为50，而dept表中不存在部门编号为50的记录，所以“张三”这条记录，不能满足e.deptno=d.deptno这条件。但在左连接中，因为emp表是左表，所以左表中的记录都会查询出来，即“张三”这条记录也会查出，但相应的右表部分显示NULL。
SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno;
因为左表中没有40这个部门，所以没有查出deptno=40这条记录：
[图片上传失败...(image-45b6df-1609650136719)]
二、创建索引
1.建表时创建索引
2.建表后创建索引（create···on··· 也可以创建复合索引）
创建复合索引
3.建表后创建索引（alter：用来更改表格式）
alter table 表名 add 主键/唯一索引/普通索引/全文索引(需要加索引的列名)
4.查看索引
show index from 表名;
5.删除索引
drop index 索引名称 on 表名;
三、视图
**视图就是一条select语句执行后返回的结果集 **，简单、安全、数据独立。
- 创建修改视图（create，alter）
create view 视图名称 as 查询语句
alter view 视图名称 as 查询语句
--例如：
create or replace view city_country_view
as
select t.*,c.country_name from country c , city t where c.country_id = t.country_id;
四、存储过程和函数
-
创建存储过程(无返回值)
create procedure 存储过程名称(参数1，参数2···) begin --sql语句 end; -
调用存储过程
call 存储过程名称(); -
写存储过程的语法
declare声明局部变量
set 可以赋值
输入参数in(默认)，输出参数out,inout既可以作为输入参数也可以作为输出参数
case-when-else 相当于switch语句
循环：while，repeat-until
-
游标：是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、OPEN、FETCH 和 CLOSE
image-20201203212032372例子：
create table emp( id int(11) not null auto_increment , name varchar(50) not null comment '姓名', age int(11) comment '年龄', salary int(11) comment '薪水', primary key(`id`) )engine=innodb default charset=utf8 ; insert into emp(id,name,age,salary) values(null,'金毛狮王',55,3800),(null,'白眉鹰 王',60,4000),(null,'青翼蝠王',38,2800),(null,'紫衫龙王',42,1800); -- 查询emp表中数据, 并逐行获取进行展示 create procedure pro_test11() begin declare e_id int(11); declare e_name varchar(50); declare e_age int(11); declare e_salary int(11); declare emp_result cursor for select * from emp; open emp_result; fetch emp_result into e_id,e_name,e_age,e_salary; select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary); fetch emp_result into e_id,e_name,e_age,e_salary; select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary); fetch emp_result into e_id,e_name,e_age,e_salary; select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary); fetch emp_result into e_id,e_name,e_age,e_salary; select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary); fetch emp_result into e_id,e_name,e_age,e_salary; select concat('id=',e_id , ', name=',e_name, ', age=', e_age, ', 薪资为: ',e_salary); close emp_result; end$ DELIMITER $ create procedure pro_test12() begin DECLARE id int(11); DECLARE name varchar(50); DECLARE age int(11); DECLARE salary int(11); DECLARE has_data int default 1; DECLARE emp_result CURSOR FOR select * from emp; DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0; --读完数据触发 open emp_result; repeat fetch emp_result into id , name , age , salary; select concat('id为',id, ', name 为' ,name , ', age为 ' ,age , ', 薪水为: ',salary); until has_data = 0 end repeat; close emp_result; end$
-
存储函数(有返回值)
-
创建语法结构
create function 名称(参数1，参数2···) returns 返回值类型(int,varchar等等) begin ······ end;
-
五、触发器
触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合
-
创建语法
create trigger 触发器名称 before/after insert/update/delete on 表名称 begin 对应触发器的语句，如insert into / update / delete语句 end; show triggers 查看触发器
六、索引
-
避免索引失效
全值匹配
最左前缀法则
-
范围查询右边的列不能使用索引
select * from tb_seller where name='小米科技' and status >'1' and address='北京市'; --address的索引失效，因为在范围查询语句的右边 在索引列上进行运算，索引失效
-
字符串不加单引号，索引失效：
在查询时，没有对字符串加单引号，MySQL的查询优化器会自动的进行类型转换，造成索引失效。
尽量使用覆盖索引，避免select *。如果查询列超出索引列，也会降低性能
用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
以%开头的like模糊查询索引失效，如果仅仅是尾部模糊匹配，索引不会失效
in走索引，not in索引失效
······
七、SQL优化
-
大批量插入数据优化
按主键顺序插入
-
关闭唯一性校验
在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。
-
手动提交事务
如果应用使用自动提交的方式，建议在导入前执行 SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行 SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。
-
优化select语句
- 一次性insert into，在事务中进行数据插入，数据有序插入
优化order by语句
-
优化group by语句
group by 实际上也会进行排序，与order by 相比只是多了分组工作
-
order by null禁止排序
explain select age,count(*) from emp group by 1 age order by null;
-
优化嵌套查询：子查询变为连接查询
-
优化or
- OR之间的每个条件列都必须用到索引 ， 而且不能使用到复合索引； 如果没有索引，则应该考虑增加索引
- 使用union替换or
-
优化分页查询
- 在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。
- 对于主键自增的表，可以把limit替换为范围查询
八、缓存失效情况
sql语句必须要完全一致(字母大小写也要一样)，这样才会走缓存
查询语句中有些不确定时，不会走缓存，如：now(),current_date()······
-
不使用任何表查询语句
select 'A'; 查询mysql，information_schema或者performance_schema数据库表时不走查询缓存
在存储函数，触发器或事件的主题内执行的查询不走缓存
如果表更改，则使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除
九、事务与锁
-
ACID属性
image-20201204093136316-
脏读
脏读是指一个事务在处理数据的过程中，读取到另一个为提交事务的数据。
--原数据 --id name --1 lisi --事务1 START TRANSACTION； updata t_table set name = 'wangwu' where id = 1; --此时事务2查询id = 1 ROLLBACK； --事务2 select * from t_table where id = 1; --查询到 id = 1， name = 'wangwu' 事务1并没有提交，name 还是 lisi，但是事务2却读到了 name = wangwu，这就是脏读。如果换成A给B转账，B查询到了没有提交的事务，认为已经收到A转过来的钱，那岂不是很恐怖。
-
不可重复读
不可重复读是指对于数据库中的某个数据，一个事务范围内的多次查询却返回了不同的结果，这是由于在查询过程中，数据被另外一个事务修改并提交了。
--原数据 --id name --1 lisi --事务1 select * from t_table where id = 1; -- 查询到 id = 1, name = list, 事务2在此时提交 select * from t_table where id = 1; -- 查询到 id = 1, name = wangwu --事务2 start transaction; update t_table set name = 'wangwu' where id = 1; COMMIT; 不可重复读和脏读的区别是，脏读读取到的是一个未提交的数据，而不可重复读读取到的是前一个事务提交的数据。
而不可重复读在一些情况也并不影响数据的正确性，比如需要多次查询的数据也是要以最后一次查询到的数据为主。
-
幻读
幻读是事务非独立执行时发生的一种现象。例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。
幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。
--原数据 --id name --1 lisi --事务1 select * from t_table where id = 2; --返回NULL,此时事务2提交 select * from t_table where id = 2; --返回id = 2, name = wangwu --事务2 insert into t_table values(2,'wangwu'); COMMIT;总的来说，解决不可重复读的方法是 锁行，解决幻读的方式是 锁表。
-
-
并发事务带来的问题
image-20201204093229137 -
事务隔离级别
image-20201204093358856 innodb引擎，无索引，行锁升级为表锁
间隙锁
