目录

1.MySQL基础定义学习

2.MySQL约束

1)主键约束

2)自增长约束

3)非空约束

4)唯一约束(unique)

5)默认约束(default)

6)零填充约束(zerofill)*

3.DQL基本查询

1)排序查询

2)聚合查询

3)分组查询

4)分页查询(Limit)

5)insert into select

4.正则表达式

5.多表操作

1)外键约束

2)多表联合查询

3)外连接查询

4)子查询

5)自关联查询

6.聚合函数

1)group_concat 

2)其他简单的数学函数

3)字符串函数

4)日期函数 

5)if逻辑判断语句

6)窗口函数

7.MySQL视图

8.MySQL的存储过程

变量

4)存储过程传参

9.MySQL触发器

触发器的NEW与OLD

10.MySQL索引

1.普通索引

2.单例索引

3.组合索引

4.空间索引

11.MySQL的事务

1)事务操作

2)事务隔离级别操作

12.MySQL日志

 1)错误日志

2)二进制日志(binlog)

3)查询日志

4)慢查询日志

1.MySQL基础定义学习

MySQL语句分为DDL(数据定义语句),DML(数据操作语句),DQL(数据查询语句),DCL(数据控制语句);

其中DDL语句基础就是用来创建数据库语句,如:

CREATE DATABASES `数据库名字`; ---创建一个新数据库

CREATE TABLE(`字段名` 基础类型 约束,2,3,4...); ---创建一个新表,在USE的数据库下

SHOW DATABASES; ---查询所有数据库名

SELECT DATABASE(); ---查询当前使用的数据库

SHOW TABLES; ---查询MySQL数据库内所有的表

USE `数据库名` ---使用当前数据库

DML基础语句:

INSERT INTO `表名`(字段名1,2,3,4....) VALUES(值1,2,3,4....); ---在表中插入一条数据后面带逗号可以插入多条数据.

UPDATE `表名` SET `字段名` = `值`; ---修改表中相应字段的数据,可以配合WHERE使用,修改特定的值.

DELETE FROM `表名` ---清除该表的内容,可以配合WHERE清空指定的内容.

TRUNCATE `表名` ---删除该表,然后重新创建该表.

DQL语句:

SELECT `查找的字段` FROM `表名` ---用于查找数据,使用最广泛的一个方法,同时在查找的内容那块还可以使用表达式,常量等;

DCL语句: 暂时没学多少 只知道事务COMMINT和ROLLBACK;

2.MySQL约束

1)主键约束

主键约束是用来进行查找的依据,通常一个表中只能有一个主键,分为单列主键,或者是联合主键.

单列主键:

在一个表当中,通常将对象全部不同的属性作为主键,如uid,身份证等等不能重复的属性,因为单列主键应该是唯一的,不能重复,且不能为空.

定义语句 PRIMARY KEY

例子: CREATE TABLE my_Data(uid int PRIMARY KEY,name VARCHAR(20));

在这个句子中,uid就是一个主键,被创建的时候必须赋值,且不能与其他主键值重复.我们也可以通过主键索引查找到符合要求的数据.

联合主键:

在一个表当中,有多个字段的主键就是联合主键 ,他们中只要有一列数据不同就可以插入,但是每一列都不能为NULL.

例子: CREATE TABLE my_DATA(uid int,name VARCHAR(20),PRIMARY KEY(uid,name));

将uid和名字声明成联合主键,其实还是只有一个主键,不过里面有两个字段.

修改表结构添加主键:

上面都是通过创建表的时候添加主键,而修改表结构的话要使用ALTER来进行修改.

语句:ALTER TABLE `表名` ADD PRIMARY KEY(`字段列表...`); 

删除主键约束:删除主键

语句:ALTER TABLE `表名` DROP PRIMARY KEY;

2)自增长约束

在MySQL中,当主键定义为自增长后,这个主键就不需要输入数值,而会由数据库系统根据定义自动赋值,每增加一条记录就会同步增长.

语法:`字段名` 数据类型 AUTO_INCREMENT

例子:CREATE TABLE my_DATA(id int PRIMARY KEY AUTO_INCREMENT,name VARCHAR(20))AUTO_INCREMENT=`指定开始值` ;

当我们在进行插入的时候不需要对id进行赋值,而默认情况下系统会自动从1开始自增长.和自增++i一样,而如果要设置值,则需要在创建末尾指定开始值.

或者通过ALTER TABLE `表名` AUTO_INCREMENT=`指定值`来进行后续的自增长值更新.

一个表中只能有一个字段使用AUTO_INCREMENT约束,且该字段必须有唯一索引,避免重复.

自增长约束字段必须具备NOT NULL属性.

自增长字段只能是整数类型(TINYINT,SMALLINT,INT,BIGINT等).

自增长约束字段的最大值受该字段的数据类型约束,如果达到上限就会失效.

当对某些字段数据进行delete删除数据之后,自增长还是在原来基础上+1,而使用truncate删除则会从原来开始.

也可以在没有主键的时候创建自增长的主键

ALTER TABLE `mydata` ADD uid int(14) PRIMARY KEY auto_increment;

3)非空约束

MySQL非空约束(NOT NULL)指定字段值不能为空,如果该字段添加空值则报错.

语法1:`字段名` 数据类型 NOT NULL;

语法2:ALTER TABLE `表名` MODIFY `字段名` 类型 NOT NULL;

而删除非空约束只需要重新定义列表,将后面的NOT NULL去掉就行了.

ALTER TABLE `表名` MODIFY `字段名` 类型;

4)唯一约束(unique)

唯一约束就代表该字段值不能重复.

语句1:`字段名` 数据类型 UNIQUE;

语句2: ALTER TABLE `表名` ADD CONSTRAINT `约束名` UNIQUE(`列名`);

但是要注意,如果是NULL值则可以重复,因为NULL与任何值都不相同包括自己.

删除唯一约束:ALTER TABLE `表名` DROP INDEX `约束名`

5)默认约束(default)

MySQL默认值约束,用来指定某字段的默认值.

语法1:`字段名` 数据类型 default 默认值;

语法2: ALTER TABLE `表名` MODIFY `字段名` 类型 default 默认值;

如果输入没有为该字段指定值将使用默认值.

删除:ALTER TABLE `表名` MODFIY `字段名` 类型 default null;

6)零填充约束(zerofill)*

1.插入数据时,当被约束字段的值长度小于定义的长度时,会在该值前补上相应的0.

2.zerofill默认为int(10).

3.当使用zerofill时,默认会自动加unsigned(无符号)属性,使用unsigned后,数值范围是原来的两倍.

语法1:CREATE TABLE my_DATA(id int zerofill,name varchar(20));

删除:ALTER TABLE `表名` MODFIY id int;

3.DQL基本查询

一些基本的关键字用法:

关键字:like "字符";    ---查询某个数据是否包含某个字符,"%A"表示查询字符串尾部为A的数据,"A%"表示查询首字符为A的字符串,"_A%"表示查询第二个字符是A的数据,%表示省略前或后数据,_表示占一个字符位.

关键字:between `值` and `值` ;   ---起始值 - 结束值的中间的数值.

关键字: in(`值`,`值`);  ---查询书记是否是in当中的任意一个值.

1)排序查询

在查询数据时,按照字段的值大小进行排序,分为升序和降序.

关键字:order by

语句:select * from myData order by salary `desc/asc`;

如果后面排序规则没有,则默认为升序(desc);

2)聚合查询

聚合查询有几种,count(),sum(),max(),min(),avg().

语句:select count(`字段名`) from mydata; 查询该表指定字段的个数,如果为null则不计入其中,如果是多字段则有一个字段不为null也进行统计.

语句:select sum(`字段名`) from mydata;  查询统计指定字段之和.

其余max和min分别返回该指定字段最大/最小值,语句使用同上.

3)分组查询

关键字:group by `字段名` having `分组条件`

语句:select uid,count(groupId) from mydata group by `字段名`,... ;

指定字段groupId进行分割,将不同的组id分割成不同的临时表,然后对每个表进行count统计

 group by可以跟多个字段,但是select后面只能写分组字段和聚合函数,否则参数不匹配会报错.

添加筛选条件 having

语句:select count(uid) from mydata group by `指定字段` having `过滤条件` order by uid;

执行先进行from拿到表,然后进行分组,进行count统计,select查询,最后having过滤,然后进行排序.

4)分页查询(Limit)

分页的显示数据,如游览商城商品一样,一个页面展示多少条数据.

语法:select `字段1`,`字段2`... from mydata limit m,n;  m表示从第几条开始显示,n表示查询多少条.

公式:(m-1)*n,n;  表示第m页从哪开始显示n条数据.

5)insert into select

将一张表的数据导入到另一张表.

insert into table2(`字段1`,`字段2`...) select `字段1`,`字段2`... from table1;

将后面table1查询的结果插入到table2中,而且字段的基本值必须对应,否则报错,而且table2表必须存在.

总结:sql书写顺序,不能乱写

select
    groupId,count(pid) as cnt
from
    mydata
where
    salary > 1000
group by
    groupId
having
    cnt > 4
order by
    cnt
limit m,n
;

//执行顺序
from --> where --> group by --> count(pid) --> having
--> select -->order by --> limit;

4.正则表达式

正则表达式关键字REGEXP

1.^   以a开头的字符串匹配 select 'abc' REGEXP "^a"; ---true

2.$   以a字符结尾的字符串 select 'abc' REGEXP "a$"; ---true

3. .    字任意字符+b的字符串 select "abc" REGEXP '.b'; ---true

4.[...]  匹配括号内的任意字符 select "abc" REGEXP '[xyz]'; ---false

5.*     匹配0个或者多个a,包括空字符. select 'stav' REGEXP '.ta*v'; ---true;

6.+    匹配1个或者多个a,不包括空字符. select 'stab' REGEXP '.ta+b'; ---true;

7.?    匹配0个或者一个a. select 'stb' REGEXP '.ta?b';   ---true;

8.|     a1|a2 匹配值是a1或者a2. select 'a' REGEXP 'a|b';

5.多表操作

1)外键约束

关键字:foreign key

[constraint <外键名>] foreign key(`字段名`) references `表名`(`字段名`);

alter table emp2 add constraint emp2_fk foreign key(`字段名`) references 表名(`字段名`);

外键约束分为主表和从表,主表的主键控制从表的指定字段,而从表数据可以随意修改,而主表被引用字段不能删除,修改.

删除外键

格式: alter table <表名> drop foreign key <外键约束名>;

外键约束可以一对一,一对多,多对多.

一对多:一个主表对应多个从表;

多对多:一个中间表对应多个主表;

同时还有自关联约束,用本表的主键来约束本表的字段

create table `sg`(
user_id int,
user_name varchar(20),
user_Supervisor_id int,    --外键列

foreign key(user_Supervisor_id) references `sg`(id)    --自关联约束
)

2)多表联合查询

1.交叉连接查询:select * from Table1,Table2;

2.内连接查询

隐式内连接:select * from Table1,Table2 where 条件;

显示内连接:select * from Table1 inner join Table2 条件;

3)外连接查询

左外连接:left join

select * from A left join B on 条件;

左边表作为主表查询,右表没有的数据填充为null;

右外连接:right join

select * from A right join B on 条件;

右边表作为主表查寻,左边表没有的填充为null;

在查询中以左,右为主表进行查询,作为主要查询,如果没有的字段则不显示,而从表没有的字段则为null;

同时后面可跟group by再进行分割和过滤

full join(Oracle方法),而在MySQL中使用union 和union all来进行满外连接查询

select * from emp1 join left emp 2 on a=b union select * from emp1 join right emp2 on a = b;

这样会将左,右查询后的两张表拼接起来.

4)子查询

select的嵌套查询.

例子:查询研发部年龄小于20的员工,包含工号和名字.

子查询:

--现在有两张表,一个部门表和一个员工表,部门表有部门cid和部门名字
--而员工表则有员工id,名字,部门id;

select *
    from `员工表` 
        where cid = (
select cid
    from `部门表`
        where name="研发部")
and age < 20 ;

这里是直接嵌套查询,将部门表查询到的值返回给员工表进行查询.

连接查询:

select * 
    from `部门表` 
        join `员工表`
            on work.cid = club.cid 
and(name = "研发部" and age <20);

这里用关联查询,查询到关联的表后再进行筛选.

或者将两个查询结果进行关联查询:

select * 
    from (
select * 
    from `部门表` 
        where name="研发部") as `club`
join (
    select * 
        from `员工表` 
            where < 20) as `work`
on work.cid = club.cid;

这里将两个表查询的结果当作一个零时表,然后进行关联的id查询,得到相应的结果.

子查询关键字-ALL

格式 :select * from `表名` where num > all(查询语句);

表示,all中返回的所有值与num进行比较,符合则返回true

ANY和SOME

格式:select * from `表名` where num >any(查询语句);

any表示与子查询返回的任何值进行比较,有一个为真返回true;

some等于是any的别名.

IN

格式:select * from `表名` where num in (查询语句);

in关键字,用于判断num是否在查询的指定集合中,同时可以配合not使用将条件反过来.

EXISTS

格式:select * from `表名` where exists(查询语句);

exists关键字表示,如果查询语句至少返回一条数据,则成立返回true,如果一条数据都没有返回null则返回false,而前面的select是否答应当前查找的值就看是否为true;

exists效率比in高.

--判断公司是否有大于60岁的员工,有则输出
select * from emp3 a where exists(select * from emp3 where a.age > 60);

--查询 有 所属部门的员工信息
select * from emp3 a where exists(select * from dept3 b where a.cid = b.cid);

当一个员工的cid不等于部门表任意一个cid,将不会被输出.

5)自关联查询

MySQL有时候在信息查询时需要进行对表自身进行关联查询,一张表自己和自己关联,一张表当作多张表来用,但是注意要给表起别名.

格式:select `字段列表` from `表1` a ,`表1` b where 条件;

select `字段列表` from `表1` join a ,`表1` b on 条件;

--将sg当作三张表进行关联查询
select 
a.ename,b.ename,c.ename
from `sg` a
    left join `sg` b on a.id = b.id
    left join `sg` c on b.id = c.id;

6.聚合函数

1)group_concat 

该函数可以将一列数据全都放入到一行中,并且可以排序

语法:select group_concat(`字段名` order by salary separator ';') from emp;

group_concat里的字段将被合并成一行数据,并且可以排序,同时默认的分隔符为,如果需要更改则需要在末尾加上separator '分隔符'.

同时也可以配合group by使用,可以将对应部门的人都显示出来,并且不会报错

select demp,group_concat(emp_name) from emp group by demp;

2)其他简单的数学函数

abs(x):求x的绝对值.   ---|x|

ceil(1.1):向上取整  ---2

floor(1.9):向下取整  ---1

greatest(1,2,3):取列表最大值  ---3

least(1,2,3):取列表最小值   ---1

mod(5,2):取模  ---1

power(2,3):取x的y次方 ---8

rand():取随机数,但是是0-1之间的小数 ---0.294849343342

round(3.115,2):x位四舍五入取整,保留x位小数  ---3.12

truncate(3.14159,3):小数截取x位   ---3.141

3)字符串函数

char_length("abc"):返回字符串长度   ---3

length("abc"):返回字符串字节长度  ---3

length("你好a"):不同字符返回字节不同    ---7

concat("c1","c2"):将c1和c2两列拼接成一行,没有分隔符.   ---c1c2

concat_ws('-',"hello","world"):指定分隔符将两列合并.   ---hello-world

field("aaa","aaa","bbb","ccc"):返回aaa在后面三个字符串中出现的位置   ---1

ltrim("    cccc"):去除字符串左边空格.   ---ccc

trim("   c1cc    "):去两端空格  ---c1cc

rtrim("cccc     "):去除字符串右端空格   ---cccc

mid("helloworld",2,3):截取字符串,从第2个开始截取三个   ---ell

position("abc" in "helloabcworld"):获取字符在字符串中第一次出现的位置 ---6

replace("helloabcworld","abc","bbb")

reverse("abc"):字符串反转  ---cba

ucase(s):转换为大写

lcase(s):转换为小写

strcmp("hello","world"):字符比较,按照字典的顺序比较第一个字符   --- -1

4)日期函数 

unix_timestamp("2022-4-3 10:10:10"):将一个日期字符串转换为毫秒数   --- 1648951810

from_unixtime(1648951810,"%Y-%m-%-%d %H:%i:%s"):将毫秒数转成一个指定的字符串格式

curdate():获取当前年月日

curtime():获取当前时分秒

current_timestamp():获取当前年月日 时分秒

date("2022-1-1 12:32:12"):从当前日期字符串中提取年月日  ---2022-1-1

datediff("2022-10-10","2000-2-3"):计算两个日期的天数差    ---8285天

datediff(curdate(),"2020-10-10"):获取当前时间到指定时间的差值

timediff("12:12:32","10:12:32"):获取时间的差值(时分秒)

date_format("2021-1-1 12:21:21","%Y-%m-%d %H:%i:%s"):日期格式化 

date_sub("2022-1-1",interval x day):将指定的日期减去x天

date_add("2022-1-1",interval x day):将指定的日期加上x天

ps:日期加减单位是可变的可以是day,month,secound,hour等等时间单位

extract(时间单位 from "2020-10-3" 11:12:32):从指定的日期中获取对应时间单位的值.

例子extract(hour from "2020-10-3" 11:12:32)  ----11

extract(month from "2020-10-3" 11:12:32)  -----10

last_day("2020-10-12"):获取给定日期的最后一天 ---2020-10-30

makedate("2021",53):获取给定年份和天数换算为日期时间  ---2021-02-22

select 时间单位 ("2021-12-1" 11:21:21):还是根据时间单位获取日期

ps:时间单位 year month minute quarter(季度)等

日期名称 ("2021-1-1 11:11:13"):获取当前日期名称 

select monthname("上面日期")   ---January

select dayname("上面")   ---Friday 

select dayofweek("")    ---6 (6-1=5) 周日是1

select dayofyear("")  ---1 本年第几天

select week("")     ---1  本年第几周

5)if逻辑判断语句

if(条件,返回值1,返回值2):跟三元运算符?一样

例子:select *,if(score >= 85,'nice',bad) flag from score;

当分数返回大于或等于85,返回nice.会多出一个flag列来显示.

ifnull(comm,0):如果给定字段为null,那么值就是0.

isnull(`值`):判断值是否为null,如果不是则返回false.

6)窗口函数

select `字段1`,`2`,`3`,lag(`字段`,n,`默认值`) from `表名`

lag()函数可以将给定字段按照指定数读取前第n行的数据.,没有则填充默认值,默认值不写则为null.

lead()函数则相反,是读取后面的值.

first_value(`字段`) over (partition by `字段` order by `字段`) as `别名`,

last_value() from emp;

上面first和last一个是查询表开头该字段的值,而last是到当前行数据的值.

select * from(select name,salary,ntile(n) over(partition by name order by hiredate) as `se` from emp)t where t.se = 1;

ntile()函数可以将当前表分为n组,这里通过名字将一张表分为几张零时表,同时对表内数据进行分组,然后通过分组的别名id进行查找.

7.MySQL视图

视图(view)是一个虚拟表,其实本质是根据SQL语句获得动态的数据集,并为其命名,用户使用时只需使用视图名称就可以获得结果,跟我们Java中封装的工具类一样,我们只需要调用就可以获得想要的结果,而视图可以当作表来使用.

作用:简化代码,可以把重复使用的查询封装成视图重复使用,同时可以使复杂的查询易于理解.

并且我们可以控制对不同的用户输设定不同的视图,比如用户只能看见自己的个人信息,但是管理员可以看见所有人的信息一样.

创建视图语法:

create [or replace] view `视图名字` as `select查询语句`;

or replace如果不加,该视图如果存在则会报错,加了则会用创建的替换现有的视图.

例子:

--创建一个视图,供我们用来进行使用
create view view_name as select * from stu;

--使用视图进行查询
select * from view_name;

--查看当前有哪些视图
show full tables;

修改视图:

修改视图是指修改数据库中已经存在的表的定义,当基本表的某些字段发生改变时,可以通过修改视图来保存视图和基本表之间一致,MySQL中通过create or replace view语句和alter view语句来修改视图.

格式:alter view `视图名` as select语句;

更新视图*:

某些视图是可以更新的,可以在update,delete,insert等语句中使用它,以跟新基表的内容.

对于可更新的视图,在视图中的行和基表之间必须具有一对一的关系.如果包含下面任何一种结构,则不可更新,大白话(视图中的select语句中不能包含下面关键字,因为是辅助进行查询的)

• 聚合函数(sum(),min(),max(),count()等)
• distinct
• group by
• having
• union或union all
• 位于选择列表中的子查询
• join
• from字句中的不可更新视图
• where子句中的子查询,引用from字句中的表
• 仅引用文字值(在该情况下,没必要更新基本表)

虽然可以更新数据,但是没必要限制太多了.

可以更新例子:

--通过视图对基表进行数据修改
update view_name set ename = "aaa" where ename = "bbb";

--通过视图插入数据到基表
insert into view_name values("aaa");

不可以:

create view view_name as select count(*) from emp;

create view view_name as select distince salary from emp;

create view view_name as select ename from emp group by ename;

create view view_name as select * from emp union select * from emp;

create view view_name as select * from emp where age = (select age from emp);

create view view_name as select * from view_name2(这是不可更新的视图);

删除和修改视图名:

--修改视图名称
rename table view_name to view_name2;

--删除视图
drop view [if exists] view_name;

8.MySQL的存储过程

 存储过程就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于Java中的方法，存储过程就是数据库SQL语言层面的代码封装与重用。

创建存储过程（函数）:

--创建一个存储过程

delimiter `自定义结束符号`

create procedure `函数名`()
  begin
    --中间是执行的SQL语句
    select empno,ename from emp;
  end `结束符号`

--将delimiter恢复到分号
delimiter ;

----------------------

--示例

delimiter $$

create procedure proc01()
    begin
      select empom,ename from emp;
    end $$
delimiter ;

调用存储过程:

--调用存储过程,可以多次调用
call proc01();
call proc01();
call proc01();

变量

1）局部变量

用户自定义，在begin/end块中有效

语法: 声明变量 declare `变量名` `变量类型` default `默认值`;

第一种赋值方式，set赋值

delimiter $$

create procedure proc02()
  begin
    declare var_name01 varchar(20) default 'aaa'; --这个是局部变量
    set var_name01 = "张三";
    select var_name01;  --输出变量
   end $$

delimiter ;

第二种方式赋值，into

delimiter $$

create procedure proc03()
  begin
    declare var_name01 varchar(20); --这个是局部变量
    --通过表查询，然后将单个值赋值给变量
    select ename into var_name01 from emp where empno = 1001;
    select var_name01;  --输出变量
   end $$

delimiter ;

2)用户变量

用户自定义，在当前会话都有效,类比Java的成员变量.

语法:@`变量名`

不需要提前声明，使用就声明

--用户变量

delimiter $$

create procedure proc04()
    begin

      set @var_name01 = "six";

    end $$

delimiter ;

--调用函数
call proc04()

--在函数体外也可以使用用户变量
select @var_name01;

3)系统变量

系统变量又分为全局变量和会话变量。

全局变量会在MySQL启动的时候，服务器自动将他们初始化默认值，这个默认值可以通过更改my.ini文件来更改。

会话变量是在每次建立一个连接的时候，由MySQL进行初始化。

全局变量可以对整个数据库可见，会话变量只在当前会话可见（等于是当前线程可见）。

全局变量语法:@@global.`全局变量名`; 

--查询所有全局变量
show global variables;

--修改全局变量
set @@global.auto_increment_increment = 100;

会话变量语法:@@session.`会话变量名`

--查询会话变量
show session variables;

--修改会话变量
set session sort_buffer_size = 10000;

4)存储过程传参

 in表示传入的参数，可以传入数值或者变量,即使传入变量，也不会更改变量的值。

可以内部更改，仅作用在函数体内.

delimiter $$

create procedure proc06(in empno int)
    begin
     select * from emp where emp.empno = empno;
    end $$

delimiter ;

call proc06(1002);

out表示将函数过程内部值给调用者，相当于返回值.

--out返回值

delimiter $$

create procedure proc08(in user_id int,out out_username varchar(20))
    begin

      --通过传入的id查找到相应的名字并且返回
      select username into out_username from emp where id = user_id;
      
    end $$

delimiter ;

--调用函数，并且创建一个变量来接收值
call proce08(1001,@user_name);

select @user_name;

inout相当于in和out的结合，使用一个变量传入后，再进行一个接收，不再演示。

9.MySQL触发器

 触发器，是一个特殊的存储过程（函数），当对数据库表中数据执行DML操作时自动触发这个SQL片段执行，无需手动调用。

在MySQL中，只有执行insert,delete,update操作时才能触发触发器执行。

可以用在数据库的日志记录，数据校验增操作。

触发器的特性:

1. 什么条件触发:I.D.U
2. 什么时候触发:在操作前面还是后面
3. 触发频率:针对每一行执行
4. 触发器定义在表上,附着在表上

格式:创建只有一个执行语句的触发器

create trigger `触发器名` `触发时间` 触发事件
on `要绑定的表名` for each row
执行的语句;

--其中，触发时间指的是在操作之前或者之后before/after
--触发事件指的是I U D这三个操作语句

格式:创建有多个执行语句的触发器

create trigger 触发器名 触发时间 触发事件
on 绑定表名 for each row
begin
    --需要执行的语句列表
end;

触发器的NEW与OLD

在进行触发器操作的时候，有时候需要获取当前修改的数据数值。

通过NEW和OLD来进行获取.

使用方法:NEW.`列名`

--insert型触发器
create trigger t1 after insert
    on `user` for each row
        insert into user_logs values(
NULL,now(),concat("有新用户添加:",NEW.uid,NEW.`列名`)

);



insert into user values(2,"张三","123456");

--有新用户添加:2张三

create trigger t2 after update
on user for each row
insert into user_logs values(
NULL,now(),concat("更新之前:",OLD.uid,"更新之后",NEW.uid));

10.MySQL索引

索引就是给每个数据一个标记,到时查找时可以迅速找到,等于就是用空间换时间.

在创建主键的时候会自动给主键字段创建索引!!!

1.普通索引

普通索引允许值重复并且可以为null或者空

创建普通索引的方式:

--在创建表的时候创建
create table student(
id int,
name varchar(20),
salary int,
age int,
index `索引名`(`需要创建索引的字段`)
);



--直接创建索引
create index `索引名` on `表名`(`字段名`);



--修改表结构创建

alter table `表名` add index `索引名`(`字段名`);

查看索引:

--查看指定数据库索引
select * from mysql.`innodb_index_stats` a 
where a.`database_name` = `数据库名`


--查看表中的所有索引
show index from `表名`

删除索引:

drop index `索引名` on `表名`;

alter table `表名` drop index `索引名`;

2.单例索引

 单例索引不允许索引字段重复,只能加在唯一的列上,但是值可以是空的.

创建:

--创建表时添加单例索引

create table student(
id int primary key,
card_id varchar(20),
age int,
number int,
unique `索引名`(`字段名`)
);


--直接创建
create unique index `索引名` on `表名`(`字段名`);


--修改表结构
alter table `表名` add unique `索引名`(`字段名`);

3.组合索引

 组合索引,指建立索引的时候使用多个字段,同时也可以将其定义为普通索引或者单例索引.

格式:

create index `字段名` on `表名`(字段1,字段2);

*全文索引

只有字段数据类型为char,varchar,text系列的才能建立全文索引.

fulltext(`字段名`);

4.空间索引

空间索引是MySQL5.7之后才有的,可以用来对经纬度进行索引.

--创建空间索引

create table student(
id int not null,
shop_name varchar(20) not null,
geom_point geometry not null,
spatial key `索引名`(`字段名`)
);

11.MySQL的事务

  MySQL的事务(Transaction)是由存储引擎实现的,在MySQL中只有InnoDB存储引擎才支持事务.

事务处理可以用来维护数据库的完整性,保证SQL语句要么全部执行成功,要么全部失败不执行.

事务用来管理DDL,DML,DCL操作,都是默认自动提交的.

1)事务操作

--开启事务
Start Transaction

--提交事务
Commit Transaction

--回滚事务
Rollback Transaction

其实在使用SQL操作的时候有事务,但是MySQL自动帮我们提交了.

我们也可以手动关闭MySQL的自动提交.

--关闭自动提交
set autocommit = 0 

--开启自动提交
set autocommit = 1

事务的特性:

1. 原子性:事务是一个不可分割的整体,要么全部完成,要么全部失败.
2. 一致性:从一个正确的状态迁移到另一个正确的状态.
3. 隔离性:每个事务的对象对其他事务的操作对象互相隔离,事务提交前对其他事务不可见.
4. 持久性:事务一旦提交,则会写到主表中结果是永久性的

事务隔离级别:

• read uncommitted 读未提交
• read committed 读已提交 (Orlace默认事务隔离级别)
• repeateable read 可重复读(MySQL默认隔离级别)
• serializable 序列化 

读未提交:一个事务可以读另一个事务未提交事务中的数据,会造成脏读.

读已提交:一个事务要等到另一个事务提交后才能读取数据,会造成不可重复读.

可重复读:在开始读取数据(事务开启)时,不再允许修改操作,会造成幻读.

串行:最高事务隔离级别,保证不会发生前三种问题,但是效率低下,一般不用.

2)事务隔离级别操作

查看隔离级别: show variables like '%isolation%';

设置隔离级别:

--设置读未提交
set session transaction isolation level read uncommitted;

--读已提交
set session transaction isolation level read committed;

--可重复读
set session transaction isolation level repeatable read;

--串行
set session transaction isolation level serializable;

12.MySQL日志

 1)错误日志

错误日志记录了MySQL启动和停止时,以及服务器在允许过程中发生任何严重错误的相关信息,当数据库出现任何故障导致无法正常使用时,可以查看此日志.

查看日志位置:show variables like 'log_error%';

2)二进制日志(binlog)

该日志记录了所有的DDL语句和DML语句,但是不包括数据库查询语句,此日志对于灾难时的数据恢复起着极其重要的作用,MySQL的主从复制就是通过该日志实现的.

二进制日志,MySQL8.0默认开启,低版本的MySQL需要通过配置文件开启,并且配置MySQL格式

Windows系统:my.ini  Linux系统:my.cnf

#配置开启binlog日志,日志的文件前缀为mysqlbin,例如:mysqlbin.01 mysqlbin.02
log_bin=mysqlbin


#配置二进制日志的格式
binlog_format=STATEMENT

查看日志

--查看MySQL是否开启了binlog日志
show variables like 'log_bin';

--查看所有日志
show binlog events;

--查看最新的日志
show master status;

--查看指定日志数据
show binlog events in '日志名';

--同时还支持指定位置查看和分页显示
show binlog events in `日志名` from 123 limit 1,2;

3)查询日志

 所有的DQL查询操作都会被记录到这个里面.

默认情况是未开启的,如果要开启则要进行配置:

#设置开启查询日志,0关1开
general_log=1


#设置日志的文件名,如果没有指定默认名未host_name.log
general_log_file=日志名

4)慢查询日志

慢查询日志记录了所有执行时间超过参数long_query_time设置值并且扫描记录数据不小于min_examined_row_limit的所有SQL语句的日志.

long_query_time默认为10s,最小为0,精度可以到微秒.

#该参数用来控制慢查询日志是否开启
slow_query_log=1


#该参数用来指定文件名
slow_query_log_file=slow_query.log


#用来配置查询的时间限制,超过这个时间则被认为是慢查询,进行记录
long_query_time=10

--查看是否开启慢查询日志
show variables like 'slow_query_log%';


--开启慢查询日志
set global slow_query_log = 1;

--查看慢查询的超时时间
show variavbles like 'long_query_time%';