MySQL 笔记

MySQL笔记

2023/9/20 星期三
今天学习了数据库

# 创建数据库

create database 数据库名

删除数据库
drop database 数据库名

使用数据库
use数据库名

创建表
create tabie 表名（
列1列1类型，
列2列2类型，
列3列3类型，
.......

）
例：
create table student(
    Id int,
    Name varchar(10),
    Age int,
    Gender char(2)
)

添加列
alter table 表名 add 列名 列类型

修改表中列的类型
alter table 表名 modify 列名 新列类型

修改列名和列的类型
alter table 表名 change 旧列名 新列名 列的类型

删除列
alter table 表名 drop 列名

修改表名
alter table 表名 rename 新表名

删除表
drop table 表名

给指定列添加数据
insert into 表名 （列名1，列名2.....）values（值1，值2....）

给全部列添加数据
insert into 表名（列名1，列名2....） values（值1，值2.....）（值1，值2.....）（值1，值2.....）......

select * from表名（查看当前所编辑的表）

修改数据
update 表名 set 列名1=值1，列名2=值2.......where 条件         /*两个及以上要有and连接*/

删除数据（行）
delete from 表名 where 条件

总结：
基本题型，过程都会，理解也能理解，但单词不会拼，出现了符号大小写混乱的错误，空格占字符，打错字母的问题。
改正：多练习单词拼写，注意符号中英文书写。

  

2023年9月21日 星期四
今天学习了数据库
语法
select 字段列表
from  表名列表
where  条件列表
group by 分组字段列表
having 分组后条件列表
order by 排序字段列表
limit 分页参数

查询多个字段
①select 字段1，字段2.....from 表名；
②select * form 表名

设置别名
select 字段1[as 别名1]，字段2[as 别名2]....from 表名

去重复记录
select distinct 字段列表 from 表名

条件查询
select 字段列表 from 表名 where 条件列表

例①查询年龄小于88的员工
select * from emp（表）where age<88    其他条件(<,>,=,=!,>=,<=)

例②查询没有身份证的员工信息
select * from emp（表）where IDcard（信息）is null   有...信息（is not null）

例③查询在15（包含）到20（包含）之间的信息
select * from emp（表）where age（信息） between 15 and 20

例④查询性别为 女 且年龄小于25的员工信息
select * from emp where agn='女'and age<25

例⑤查询年龄等于 18 或 20 或 40 的员工信息
select * from emp where age in（18,20,40）
select * from emp where age = 18 or age = 20 or age = 40;

例⑥查询两个字的员工信息
select * from emp where name like'__'   _(一个字符或汉字)

例⑦模糊查询
select * from emp where name like‘li%’    (%相当于什么什么的意思)

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

select 聚合函数 from 表名

统计数量
select count（*） from 表

统计平均数
select avg (*) from 表

统计最大数
select max（*） from 表

统计最小数
select min（*） from 表

统计数之和
select sum（*） from 表   where 条件限制

分组查询
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件]

例①根据性别分组，统计男性员工 和 女性员工的数量
select count（*） from empty group by sex（性别）

例②根据性别分组，统计男性员工 和 女性员工的平均年龄
select avg(age) from emp group by gender;

例③查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址
select workaddress from emp where age < 45 group by workaddress having count(workaddress)>=3

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

asc 升序（默认）
desc 降序

分页查询
第一页，分十条记录
select * from 表 limit 0,10   （起始索引从0开始，起始索引 = (查询页码-1) * 每页显示记录数，如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10）

第二页，分十条记录
select * from 表 limit 10,10

总结：今天学习了多种查询方式，在做题时出现了很多问题，例如题目两者关系搞混了，导致做题一直出现报错，个别字母打错，连接关系出错等等，但相比于昨天已经有很大进步了，基本会建表和查询了还是英语单词有些不会这一点该努努力争取把基本语法都熟记于心

2023年9月22日 星期五

今天没有学习新内容，但做了7套题

总结：
一开始的做题还是看着笔记跟着敲代码，随着题目的增加，题目也越看越简单到了最后两套题目都没有看笔记，直接敲出来了这一点还是很高兴的。

不足：个别单词敲错了，还是不熟英语，基本语法不熟悉，也有犯错的时候，还有忘记字符要加引号，理解能力也有点差，有些题目都没有搞明白，逻辑关系有些混乱。limit语法不熟悉，前面以上急需改正，特别长或者多个条件的代码还是迷糊。

进步：建表和查询操作基本已经掌握，除了个别单词易忘大多数已经掌握，还得每天多多练习，熟记单词，注意符号中英文。

定个学习方法：每天早上起床到公司2个小时背单词，来到公司先做套题，再学新内容，下午做题，5点左右做笔记，总结反思，回顾一天的学习内容，纠错，准备一个手写本子把每天用到的关键词记录下来，多默写几遍。

2023年9月23日 星期六 

今天学习了数据库的函数

一.字符串函数

concat  字符串拼接

lower 字符串转小写

upper 字符串转大写

lpad 左填充

rpad 右填充

trim 去掉字符串的头部和尾部的空格

substring 截取字符串

二.数值函数

ceil 向上取整

floor 向下取整

mod 返回余数

rand 返回0-1的随机数

round 参数x四舍五入，保留y位小数

三.日期函数

curdate 返回当前日期

curtime 返回当前时间

now 返回当前时间和日期

year 获取指定年份

month 获取指定月份

day 获取指定的日期

date_add 返回一个日期/时间值加上一个时间间隔后的时间值

datediff 返回起始时间1 和 结束时间2之间的天数

四.流程函数

if 如果value 为true,则返回t否则返回f

ifnull 如果value1 不为空，则返回value1，否则返回value2

case when then end
例
select name,(case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) ‘工作地址’ from emp;

表的员工姓名和工作地址（北京/上海 ---> 一线城市，其他 ----> 二线城市）

总结
这些函数是基本函数，需要会背会用。

2023年9月25日 星期一

今天学习了数据库中的约束

约束
约束是作用于表中字段上的规则用于限制储存在表中的数据，目的保证数据库中的数据的正确，有效性和完整性

not null 非空约束 限制该字段的数据不能为null

unique 唯一约束 保证该字段的所有数据都是唯一，不重复的

primary key 主键约束 主键是一行数据的唯一标识，要求非空且唯一

default 默认约束 保存数据时，如果未指定该字段的值，则采用默认值0

check 检查约束 保证字段值满足某一个条件

foreign key 外键约束 用来让两张表的数据之间建立联系，保证数据的一致性和完整性

注意：
约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。

外键约束

语法

constra 外键名称 foreign key （外键字段名） references 主表（列名）     建表的时候用这个

alter table 表名 add constraint 外键名称 foreign key （外键字段名） references 主表（主表的列名）     有表时，添加外键

alter table 表名 drop foreign key 外键名称    删除外键

总结：今天学习了约束，没有题目，只敲了一下源代码，做了两套查询添加的题目

问题：还是出现了英语单词拼写的错误，个别单词忘记的问题。

进步：过程还行基本不看资料就能敲出代码了，除了个别题目理解不透彻，绝大多数题目都可以独立解决了。

2023年9月26日 星期二

今天学习了数据库中的多表查询
多表查询

（一）一对多（多对一）
实现：在多的一方建立主键，指向另一方的主键

（二）多对多
实现：建立第三张表，中间表至少包含两个外键，分别关联两方的主键

（三）一对一
实现：在任意一方加入外键，关联另一方主键，并且设置外键是唯一的（unique）

笛卡尔积：指数学中，两个集合，A集合和B集合所有组合的情况

连接查询
内连接：相当于查询A,B交集部分数据

外连接：
左外连接：查询左表所有数据，以及两表交集部分数据
右外连接：查询右表所有数据，以及两表交集部分数据

自连接：当前表与自身的连接查询，自连接必须使用别名

隐式内连接：
select 字段列表 from 表1  表2 where 连接条件

显示内连接：
select 字段列表 from 表1 join 表2 on 连接条件....

左外连接：
select 字段列表 from 表1 left join 表2 on 条件。。。

右外连接：
select 字段列表 from 表1 right join 表2 on 条件。。。

子连接：
select 字段列表 from 表A 别名A join 表A 别名 B on 条件....

自连接查询，可以是内连接查询，也可以是外连接查询

联合查询
Select 字段列表 from 表A  Union[all]Select  字段列表 from 表B

子查询
概念：SQL语句中嵌套select语句，称为嵌套查询，又称子查询

Select * from 表名1 where 字段名 = (select 字段名 form 表名2);
子查询外部的语句可以是 insert/update/delete/select的任何一个

根据子查询结果不同，分为：
标量子查询(子查询结果为单个值)
列子查询(子查询结果为一列)
行子查询(子查询结果一行)
表子查询(子查询结果为多行多列)

根据子查询位置，分别在where之后，from之后，select之后

标量子查询
子查询返回的结果是单个值，形成了标量子查询
常用的操作符：=，<,>,<=,>=,<>

列子查询
子查询返回结果是一列，形成列子查询
常用操作符：in，not in，any，some，all

in：在指定的集合范围之内，多选一
not in：在不指定的范围之内
any：子查询返回列表，有任意满足即可
some：与any等同
all：子查询返回列表所有值必须都满足

行子查询
子查询返回结果是一行（或多行），这种就叫行子查询
常用操作符：=,<>,in,not in

表子查询
子查询返回结果是多行多列，这种子查询就叫表子查询
常用操作符：in

总结：一对一表还可以。多对多的表就不怎么会了，逻辑思维不太行，转不过来，题目条件太多就犯晕，有点难

2023年9月27日 星期三

今天没有学习新内容

总结：今天做了四套题，两套多表查询，两套单表查询，多表查询过于复杂掌握不来，单表查询基本掌握，但是经常打错字母导致运行出错，is null没有记住，总是记成 = null，需要改正，多表查询

 2023年10月7日 星期六

今天没有学习新内容，复习了多表查询，做了两套题

总结：今天有很大进步，两表的多表查询已经会做了，有自连接查询的问题有点不会，联合查询的单词不会写，今天出现了，字符号大小写的问题，个别单词打错了的问题，三张表以上的题还没接触感觉很难，今天罚写了20遍avg，count，‘,’，‘;’，‘()’等错误点感觉收获很大争取明天不会再出现这类问题

2023年10月10日 星期二

今天没有学习新内容，做了两套题

总结：今天没有犯太大的错误但小问题不断，在做题时总是给问题填一些无关的东西，
老是分不清给谁分组，把简单的问题复杂化，没有系统的书写，老是忘记句末的分号.

2023年10月10日 星期三

今天没有学习新内容，做了两套题

总结：今天没有犯太大的错误，但电脑崩溃了，导致做了一下原来的题目，在做题时总是给问题填一些无关的东西，
但错误已经很少了，做题速度比原来快了不少.再做一遍原来的题发现了不少小错误，及时的改正了，也更深刻了解了那些函数语法的用处

2023年10月12日 星期四

今天么有学习新内容 做了三套题

总结：多表查询已经没有太大问题了，连做三套题没犯太大错误，个别小符号问题，看错题意的小问题，
现在的做题速度也很快了，基本函数和语句也记住了

2023年10月13日 星期五

今天没有学习，做了两套半的题

收获：单词拼写的错误已经没有了，分号有时会忘记书写，题目基本没有问题了，现在做题速度也很快

了，今天出现的错误有，分组不明确导致答案有偏差，筛选过滤时，把题目的条件因素带了进去，答案

中包含了题目没有要求的东西

自联查询，左链接查询，个别函数，建立外键的语句等还有一点生疏，其他都还行

2023年10月14日 星期六

今天没有学习新内容， 做了三套题，两套单表，一套多表

总结：今天上午做多表时，不在状态，做题老是卡克，出现老多问题，老是分组导致结果错误

单表查询还可以，基本没什么错误

2023年10月16日 星期一

今天，没有学习新内容，做了两套题

总结：做的综合题和数据库的增删改，经过一个月的学习基础的增删改已经是炉火纯青，十拿九稳了

多表查询基本语句也掌握的不错了，两表，三表都不在话下了，四张表甚至五张表的还不是很行

2023年10月17日 星期二

今天没有学习新内容 做了半套题

总结：多表基本快学会了，今天的题目非常非常难。经常把一个表的某个部分，拆成好几个部分，再把

这些分开的当做表来用，求取之中的某些值，一道题动辄十好几行代码，赋的值经常出错，代码运行时

间也变的漫长，中间也出现了单词遗忘的老毛病，到了快下班才做完绝大部分

2023年10月18日 星期三

今天没有学习新内容，做了一套半的题

总结：今天把昨天没有做完的题解决了，学会了给表排名，分开排名等问题，做了一套综合题基本没有问题，忘了一个知识点，把函数放到where里导致题目出错，函数应当放到having后面要记住，而且having前面要跟着group by 否则无意义

例：按照各科成绩进行排序，并显示排名
select a.*,count(b.s_score)+1 from score a left join score b on a.s_score<b.s_score and a.c_id=b.c_id group by c_id,s_id order by c_id,s_score desc;
解析：先排序，后加入count（x）+1用来显示排名，用左链接，在left join...on...后面加条件，过程用自连接查询，把表分为a,b

显示排名前三的

（count（b.x）+1）， 用having（count（b.x）+1）进行筛选 

2023年10月19日 星期四

今天没有学习新内容，做了两套题
总结：今天还是练习了多表查询，基本没什么错误，把会做到都做了

2023年10月20日 星期五
今天没有学习新内容，做了两套题，复习了一下函数的拼写和约束的写法、

添加外键
①在创表时
constrain （自己起的外键名） foreign key （要把那个数据当外键）reference 主表（当主键的那个数据）   （创多个外键时用逗号链接）
②已经建好表了
alter table （要出去当外键的那个表）add constrain （自己起的外键名） foreign key （要把那个数据当外键）reference 主表 （当主键的那个数据）
删除外键
alter table 表名（有外键的表）drop foreign key （ 自己起的外键名）

2023年10月21日 星期六
没有学习新内容，做了几套题
总结：在复习时出了好多问题，函数忘记了，多表查询的语句使用模糊，英语发音不好，单词不会读等问题，
练习时没有出错误，这一点还可以

2023年10月23日 星期一
今天没有学习新内容做了三套题
总结：分别是增删改，单表查询，和一个四表查询的题目，基本没有大问题

例题：查询学生成绩，信息和课程数

不可以直接都连接起来
要单独求出一个或两个结果在连起来
都连起来时用*来表示 
select 学生编号,课程数 from 表 group by 学生编号；
select * from 学生表，成绩表，（课程数表） where 学生编号=（课程数表里的学生编号）and （课程表里学生编号）=成绩表里学生编号 
总结：这三个语句必须有相同条件（学生编号）才能链接起来否则出不来结果

2023年10月24日 星期二

今天没有学习新内容，做了四套多题

总结：都是原来做过的题目，做了一套综合题，一套单表查询，两套多表查询，随着做题的数
量增加，现在建数据库，建表，插入数据都不会有打错单词的操作了，做题时也很轻松的解答，今天在做题时遇到了两道原来不会做跳过的题目，现在反过来再做一遍十分的轻松

例如：列出每个部门中收入最高的员工姓名，部门名称，收入，按照收入降序
这道题用到了自联查询，就是自己查自己中符合要求的数据
我先把这个中要求的最大值和id求了出来
我又把我所求的数据当作表来使用，带入其它的表利用id相等就把所要的因素得出来了

2023年10月25日 星期三    2023年10月26日 星期四

今天没有学习新内容，做了四套题

星期四：今天把昨天的不会题型给解决了，并加了点新知识，例题

总结：不会的题型
1.查询课程用排名表示   (ok)

select a.sid,a.cid,a.score,count(b.score)+1 from score a left join score b on a.score<b.score and a.cid=b.cid group by a.sid,a.cid order by a.cid,a.score desc

count(b.score)+1 来表示排名

2.各科成绩的前三名     (ok)

select * from (select a.sid,a.cid,a.score,count(b.score)+1 l from score a left join score b on a.score<b.score and a.cid=b.cid group by a.sid,a.cid order by a.cid,a.score desc) k where k.l<=3
 或
select a.sid,a.cid,a.score,count(b.score)+1 l from score a left join score b on a.score<b.score and a.cid=b.cid group by a.sid,a.cid having count(b.score)+1 <=3 
3.查询及格率的例题     (ok)
（补充：
(1)、CONCAT（）函数用于将多个字符串连接成一个字符串
(2)、TRUNCATE(X,D)函数作用是将一个数字(X)截断为指定的小数位数(
若D为负，则TRUNCATE(X,D)函数使小数点左边的D位变为O,
若D为0，则返回值没有小数点）
--用来连接%
truncate 可写可不写
它的作用是保留或省略小数点几位

select k.m 课号,concat(truncate(l.n/k.n * 100,3),'%') 优秀 from (select cid k,count(cid) n from score where score between 85 and 100 group by cid) l,(select cid m,count(cid) n from score group by cid) k where k.m=l.k;

4.值为null的查数目时不显示，左连接不会用  （ok）

 左右链接误区
左表和右表是相对于 “ left join " 和 “right join” 而言的， “ left join "的左边就是左表，右边就是右表。左连接可以理解为：在内连接的基础上，保证 “ left join "左边的表（左表）的数据全部显示。同理，右连接可以理解为：在内连接的基础上，保证 “ right join "右边的表（右表）的数据全部显示

select k.*,m.优秀 from (select score.cid n,name,a.不及格,b.中等,c.优良 from score,course,(select k.m 课号,concat(truncate(l.n/k.n * 100,3),"%") 不及格 from (select cid k,count(cid) n from score where score between 0 and 60 group by cid) l,(select cid m,count(cid) n from score group by cid) k where k.m=l.k) a,( select k.m 课号,concat(truncate(l.n/k.n * 100,3),'%') 中等 from (select cid k,count(cid) n from score where score between 60 and 70 group by cid) l,(select cid m,count(cid) n from score group by cid) k where k.m=l.k) b, (select k.m 课号, concat(truncate(l.n/k.n * 100,3),'%') 优良 from (select cid k,count(cid) n from score where score between 70 and 85 group by cid) l,(select cid m,count(cid) n from score group by cid) k where k.m=l.k) c where a.课号=score.cid and b.课号=score.cid and c.课号=score.cid and score.cid=course.id group by score.cid) k left join (select k.m 课号,concat(truncate(l.n/k.n * 100,3),'%') 优秀 from (select cid k,count(cid) n from score where score between 85 and 100 group by cid) l,(select cid m,count(cid) n from score group by cid) k where k.m=l.k) m on k.n=m.课号

2023年10月27日 星期五    2023年10月28日 星期六
这两天没有学习新内容，做了两套题
总结：多表查询没有问题了除了几个现阶段没办法做出来的题已经全部都会做了，题目也都做了好几遍。
问题：做题时发现了几个问题，写条件时看错了，或漏写了导致答案不对或者不全或者多了无关的内容，做题总用最复杂的方法不简便，糅杂无章，有时条件不成立

2023年10月30日 星期一
总结：做了一套其他数据库的题，有好几个函数没有见过，在navicat中没有办法运行，试着用MySQL的方法做的，效果一般

2023年10月31日 星期二
总结：做了一套从 别的数据库中挪过来的题，做题时没有出错哦

?                    

2023年11月1日 星期三
今天学习了新内容，事务
1.事务简介
事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败
一般情况下MySQL的事务是默认自动提交的
2.事务的操作 （步骤）
①查看/设置事务提交的方式
select @@autocommit--查询结果为1时，是自动提交。结果为0是手动提交
set @@autocommit=0--设置事务为手动提交
set @@autocommit=1--设置事务为自动提交
②手动开启事务
start transaction 或 begin
③提交事务      回滚事务(有错误时)
commit       rollback
3.事务的四大特性
①原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败
②一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态
③隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
④持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的
4.并发事务的问题
①脏读：一个事务读到另外一个事务还没有提交的数据
②不可重复读：一个事务先后读取一条记录，但两次读取的数据不同，称之为不可重复读-- （数据被修改时）
③幻读：一个事务按照条件查询数据时，没有对应数据行，但是在插入数据时发现这行数据已存在，好像出现了幻觉 -- （增加或删除数据时）
5.事务的隔离级别
1.读未提交（read uncommitted）（级别最低）
2.读已提交（read committed）
3.可重复读（默认）（repeatable read）
4.序列化 （serializble）（级别最高）
查看事务隔离级别
select @@tx_isolation
设置事务隔离级别
set [session|global]  （二选一，新人一般用session） transaction isolation level {read uncommitted | read committed | repeatable read | serializable} （四选一）
-- session：指的是只在当前会话中隔离级别有效
-- global：值得是所有会话中隔离级别有效
-- serializable: 串行化，只有一个事务提交完成以后另一个事务才能进行下一步操作
注意：事务隔离级别越高，数据越安全，但是性能越低
13、什么是保存点（Savepoint）？它有什么作用？
??保存点是一个事务中的一个标记，用于将事务分成更小的逻辑单元。它可以在事务中的特定点设置，然后可以回滚到该点而不影响事务中的其他操作。保存点可以用于在事务执行过程中做部分回滚，而不必回滚整个事务
mysql常用命令
1.查看MySQL数据库版本号
select version（）
2.启动MySQL服务 （基于Windows）
net start MySQL
3.停止MySQL服务
net stop MySQL
4.登录MySQL数据库
mysql -u 用户名 -p
5.退出数据库
exit或quit
6.显示所有数据库
show database

2023年11月2日 星期四 

今天学习了新内容，视图

1.视图的介绍
视图是一种虚拟存在的表，视图中的数据不在数据库中真实存在，行和列的数据都来自于定义视图的表
视图只保留了查询sql语句的逻辑，不保存查询结果

2.创建视图
create view （视图名称）view_xxx as select * from xxx  (一般视图名称用view_xxx来表示)、

3.查询视图

①查看创建视图的语句
show create view view_xxx

②查看视图数据
select * from view_xxx    (如果查看单个或某些数据在from前写要查的东西)

4.修改视图
①删除重建视图
create or replace view view_xxx as select * from xxx

②修改视图
alter view view_xxx as select * from xxx

5.删除视图
drop view view_xxx

6.视图的更新
要使视图更新，视图中的行与基础表的行必须存在一对一的关系，如果视图总包含以下一项，则该视图不可更新
①聚合函数 （sum,min,max,count,avg）
②去重 distinct
③分组 group by
④分组后过滤 having
⑤排序合并 union
⑥去重排序合并 union all
注：针对视图的增删改，会影响到基础表的数据，相反也一样

7.视图的作用

优点
①简单
视图可以简化用户对数据的理解，可以简化他们的操作，哪些被经常使用的查询可以被定义为视图，从而使得用户不必为以后得操作每次指定全部的条件
因为视图是查询语句执行后返回的已经过滤好的复合条件的结果集，所以使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件
②安全
数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据
③数据独立
视图可帮助用户屏蔽真实表结构变化带来的影响。一旦视图的结构被确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响

2023年11月3日 星期五
今天学习了新内容触发器
1.触发器简介
触发器：监视某种情况。并触发某种操作，它是提供程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，例如当对一个表进行操作（ insert，delete， update）时就会激活它执行。
  触发器经常用于加强数据的完整性约束和业务规则等。 触发器创建语法四要素：
        1.监视地点(table)
        2.监视事件(insert/update/delete) 
        3.触发时间(after/before) 
        4.触发事件(insert/update/delete)
2.触发器的语法
Create trigger 触发器名称
Before/after insert/update/delete
On 表名 for each row -- 行级触发器
Begin
    Insert语句/update语句/delete语句
End;

例
Create trigger trigger_student
After insert on student（表一） for each row 
Begin
Insert into student_logs（表二） values (null,’insert’,now(),new.sid,’dwadad’);
End;
解析：要用到两个表 表一，表二，其中表二后面的东西是查触发器里日志内容的东西

3.触发的类型
insert 型     new表示将要或者已经新增的类型
update 型     old表示修改之前的数据 ，new 表示将要或者已经修改的数据
delete 型     old表示将要或者已经删除的数据
4.查看触发器的日志
show triggers
5.删除触发器
drop trigger 触发器名

注意：触发器只能在（insert，delete， update）中使用其他情况下无用

2023年11月4日 星期六

今天学习了新内容索引

一.索引的概述
索引是帮助MySQL高效获取数据的数据结构（有序），在数据之外，数据库系统维护还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引入数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引

类型：B+tree，红黑树，二叉树，B-tree

二.索引的优缺点
优点：1.提高数据检索的效率，降低数据库io成本
2.通过索引列对数据进行排序，降低数据排序的成本，降低cpu的消耗
缺点：1.索引列占很大空间
2.虽然索引提高了查询的效率，同时降低了更新表的速度，对表的insert，update，delete时，效率降低

三.索引结构
B+tree：最常见的索引类型，大部分引擎都支持B+树的索引
hash：底层数据结构使用哈希实现，只有精确匹配索引列的查询才有效，不支持范围查询
r-tree:空间索引是mylsam引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
full-text:是一种通过建立倒排索引，快速匹配文档的方式，类似于lucene,solr,e5

索引                 InnoDB           MyISAM           Memory
B+tree索引           支持             支持              支持
Hash索引             不支持           不支持            支持
R-tree索引           不支持           支持              不支持
Full-text           5.6版本之后支持   支持              不支持
我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引

二叉树:二叉树缺点：顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级越深，检索速度慢。（二叉树一个节点只能包含两个子节点）

红黑树：大数据量情况下，层级越深，检索速度慢

1.    所有数据都会出现在叶子节点
2.    叶子节点形成一个单向链表

b+tree
MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子的链表指针，形成了带有顺序指针的B+tree，提高区间访问的性能

Hash
哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决

Hash索引特点

1.    Hash索引只能用于对等比较（=，in），不支持范围查询（between，>，<，…）
2.    无法利用索引完成排序操作
3.    查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引

存储引擎支持
在MySQL中，支持hash索引的是Memory引擎，而InnoDB中具有自适应hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构架的

四.索引分类
分类                        含义                                   特点                          关键字
主键索引                  针对于表中主键创建的索引                   默认自动创建，只能有一个        PRIMARY
唯一索引                  避免同一个表中某数据列中的值重复            可以有多个                    UNIQUE
常规索引                  快速定位特定数据                          可以有多个
全文索引                  全文索引查找的是文本中的关键词              可以有多个                    FULLTEXT
                         ，而不是比较索引中的值                       

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：
分类                              含义                                        特点
聚集索引(Clustered Index)         将数据存储与索引放到一块，索引结构的叶子节         必须有，而且只有一个
                                                         点保存了行数据
二级索引(Secondary Index)        将数据与索引分开存储，索引结构的叶子节点关           可以存在多个
                                                       联的是对应的主键

如果存在主键，主键索引就是聚集索引。
如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
如果表没有主键，或没有合适的唯一索引，则InnoDB自动生成一个rowid作为隐藏的聚集索引

索引的分类
一、单列索引

一个索引只包含单个列，但一个表中可以有多个单列索引。 这里不要搞混淆了

1、普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一 点。

2、唯一索引：索引列中的值必须是唯一的，但是允许为空值。

3、主键索引：是一种特殊的唯一索引，不允许有空值。（主键约束，就是一个主键索引）。
二、组合索引

在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。

三、全文索引

全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行。

五.索引的语法
1.创建索引
Create [ unique | fulltext ] index 索引名 on 表名(字段名,…)
注意：如果不指定unique后fulltext默认为常规索引

2.查看索引
Show index from 表名

3.删除索引
Drop index 索引名 on 表名

MySQL支持多种方法在单个或多个列上创建索引：

使用CREATE TABLE创建表时，在定义语句中指定索引列，
1.建表时加主键约束，外键约束，唯一约束在定义约束的同时相当于创建了索引  （隐式）

2.[unique | fulltext | spatial][index | key][index_name](col_name[lenght])[asc | desc]

(UNIQUE、 FULLTEXT 和 SPATIAL 为可选参数，分别表示唯一索引、全文索引和空间索引

INDEX 与 KEY 用来指定创建索引,选其一即可;

index_name 指定索引的名称，可选参数，如果不指定，默认col_name为索引名

col_name 为需要创建索引的字段列，该列必须是数据表已定义的列

length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度

ASC 或DESC 指定升序或者降序的索引值存储   )

例如：INDEX idx_book_name(book_name) （给book_name加一个索引,名字叫idx_book_name）
例如：UNIQUE INDEX idx_ue_book_name(book_name) （给book_name加一个唯一索引, 名字叫id_ue_book_name）
0

使用ALTER TABLE语句创建索引
ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY]
[index_name] (col_name [length],...） [ASC|DESC]

与创建表时创建索引的语法不同的是，在这里使用了 ALTER TABLE 和ADD 关键字，ADD表示向表中添加索引

例如：# 1.向已存在的book表中通过' ALTER TABLE... ADD '的方式添加一个普通索引
ALTER TABLE book ADD INDEX idx_book_name(book_name);

# 2.向已存在的book表中通过' ALTER TABLE... ADD '的方式添加一个唯一索引

ALTER TABLE book ADD INDEX uk_idx_auth(authors);

# 3.向已存在的book表中通过' ALTER TABLE... ADD '的方式添加一个联合索引

ALTER TABLE book ADD INDEX mul_idx_name_auth(book_name,authors);

使用CREATE INDEX语句在已存在的表上添加索引。

CREATE [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name]
ON 'table_name' (col_name[length],...） [ASC|DESC]

例如：# 1.向已存在的book表中通过' CREATE INDEX... ON '的方式添加一个普通索引
CREATE INDEX idx_book_name ON book(book_name);

# 2.向已存在的book表中通过' CREATE INDEX... ON '的方式添加一个唯一索引

CREATE INDEX uk_idx_auth ON book(authors);

# 3.向已存在的book表中通过' CREATE INDEX... ON '的方式添加一个联合索引

CREATE INDEX mul_idx_name_auth ON book(book_name,authors);

六.索引的设计原则
1.针对于数据量较大，且查询比较频繁的表建立索引。

2.针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。

3.尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。

4.如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引

5.尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。

6.要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。

7.如果索引列不能存储null值，请在创建表时使用not null约束它。当优化器知道每列是否包含null值时，它可以更好地确定哪个索引最有效地用于查询

2023年11月6日 星期一

今天没有学习新内容

复习了事务，索引，触发器，视图等知识点，随后又复习了MySQL常用命令手册，知道了如何在黑框里输入SQL语句

补充：

系统函数
1.获取MySQL的版本号函数
select version()

2.查看当前用户的连接数的id函数
select connection_id()

3.查看当前使用的数据库函数
select database

4.查看当前登录的用户名的函数
select user()

查看系统当前所有的登录用户的详细信息
show processlist

5.查看指定字符串的字符集函数
select charset('a')

6.查看指定字符串的排列方式的函数
select collation（'a'）

7.获取最后一个自动生成id值的函数
select last_insrt_id()

其他函数
1.格式化函数
select format（123.123，4）

(123.1230)

2.不同进制的数字进行转换的函数: #将二进制转为16进制
select conv('101010111101000',2,16)

(55E8)

3.ip地址与数字相互转换的函数
select inet_aton （'127.0.0.1'）

(2130706433)

select inet_ntoa('2130706433')
('127.0.0.1')

4.重复执行指定操作的函数
select benchmark(1000,password('123'))

5.改变字符集函数
select convert('a' using 'gb18030')

6.改变数据类别
select convert(123,char(2))

时间函数
1.获取时间戳函数
select unix_tomestamp()

2.转换时间戳函数
select from_unixtime()

3.获取utc(世界日期/时间) 日期函数
select utc_date

4.获取utc(世界日期/时间) 时间函数
select utc_time

5.获取月份的函数：
select month(now());

6.获取星期的函数
select dayofweek(now())    #英文显示第周几

select dayofweek(now());    #一周中的第多少天  1 表示周日，2 表示周一，...... ，7 表示周六

select weekday(now()); #0 表示周一，1 表示周二，...... ，6 表示周日

7.获取天数的函数
select dayofyear(now());        #一年中的第多少天

mysql> select dayofmonth(now());    #一个月中的第多少天

8.获取季度的函数
select quarter(now());

9.获取分钟的函数
select minute(now())

10.获取秒钟的函数
select second(now())

11.获取日期的指定值的函数
select extract(year from now());

select extract(year_month from now());

select extract(hour_minute from now());

12.时间和秒转换函数

select time_to_sec(now());

select sec_to_time(32874)

13.计算日期和时间的函数
select date_add(now(),interval 1 year);        #当前日期增加一年

select date_sub(now(),interval 1 month);        #当前日期减一个月

select datediff(now(),'2023-03-14 09:09:58');    #取两个时间的差

14.将日期和时间格式化的函数

select date_format(now(),'%y %m %d');

date_format(now(),get_format(date,'usa'));