MySQL自学基础笔记

m0_52969641

已于 2023-03-01 22:59:37 修改

阅读量2.1k

点赞数

文章标签： mysql 数据库 sql Powered by 金山文档

于 2023-03-01 22:55:10 首次发布

本文链接：https://blog.csdn.net/m0_52969641/article/details/129199647

版权

1.连接数据库

1.1.软件

1.1.左上角database -> connect to database ->输入密码即可
1.2.host->information sheme ->新建查询

1.2.用cmd连接数据库

mysql -uroot -p

2.对数据库的常用操作（DDL）

配置：工具->选项->编辑器
调节字体大小：ctrl+鼠标滚轮

2.1.对数据库的常用操作

注释（灰色就是注释）

1.1. #

1.2. -- 加上空格

show , create , drop , use(不区分大小写）

2.2.对表结构的常用操作（运行后记得刷新）

注意：

1.括号（）

2.每一行后面有逗号，

3.创建表格前，需要use 数据库名字：即选择数据库先

或者：create table if not exists mybd1.student(

如果想要无符号加unsigned，例如 sid int unsigned

DECIMAL（M,D）：表示，有效位数为M位，保留小数点后D位

推荐使用varchar（）
例如：varchar（20）：表示20个字符而不是20个字节，没存够也不会浪费空间

创建临时表：creat temporary table student；
当对话结束，表格自动删除。

2.3.修改表结构

添加列（alter）

2.修改列名（alter)

3.删除列

4.修改表名

3.对数据库常用操作--DML

DML是数据操作语言，主要对mysql数据进行增删改操作

3.1.数据插入（insert）

方式1：必须要一一对应
如果想要多次赋值，可以这么写：后面的values不写直接跟着值，用逗号隔开
insert into student（sid,gender,age)
values(1001,'男',18), #用逗号结尾
(1002,'男’,20);#不用value
方式2：方式2意味着必须给每一列赋值，不能像方法1一样选择性赋值，就是说要补全。

3.2.大量插入数据

field terminated ：字段间的分隔符

lines terminated ：行之间的分隔符

3.3.数据修改（update）

3.4.数据删除（delete from）

3.5.总结

4.MYSQL约束

加入约束为了保证表中数据的完整性和有效性，例如限制年龄不能为负数。

4.1.创建主键约束（主键必须唯一对应某一行，相当于索引）

定义了主键列后主键列必须唯一且不能空，相当于唯一索引
添加重复的值会报错，即不能重复

4.1.1.添加单列主键（方法1）

eid int primary key:创建主键，且主键不能为空，唯一表示某一行。

4.1.2.添加单列主键（方法2）

[constraint pk1（这个是主键名字）] (这个可以不写）primary key（eid):指定eid为主键

4.1.3.添加联合主键（只能用前面的方法2，后面定义主键）

联合主键任何一个都不能为空，两个值联合一起作为该行的唯一索引
只要联合主键不同即两个值有一个不同就满足

4.1.4.创建表格后添加主键

通过修改表结构添加主键

4.1.5.删除主键

4.2.自增长约束

主键自动增长赋值

4.2.1.定义字段自增长

4.2.2.指定自增字段初始值

注意：分号；在auto_increment = 100之后

***delete 和 truncate删除列表后自增列的变化：
delete：自动增长从断点开始
truncate:(删除表格后重新创建一样的表格）：自动增长从默认起始值开始

4.3.非空约束（not null)

添加约束后如果值没有初始化是错误的，也就是它不会默认给你赋一个NULL

4.4.唯一约束

加上唯一约束的列的元素不能重复
但是NULL可以重复，因为在mysql中NULL和任何值都不相同，包括NULL

方法二：

alter table t_user8 add constraint unique_ph unique(phone_number)

4.5.删除唯一约束（删除约束）

alter table <表名> drop index <约束名>
alter table t_user8 drop index unique_ph(如果没有指定名字，就是列名phone_number);

4.6.默认约束

默认约束用来指定某列的默认值，即如果没有给值，自动添加默认值

4.7.零填充约束（zerofill）

自动用unsigned格式填充0，例如输入123，填充后为0000000123

4.8.总结

5.MYSQL基本查询——DQL

5.1.简单查询

select 后面的在输出时才自成一列：
例如select a,b,c from table
输出： a b c
1 2 3
2 3 4

取别名时as可以省略，意味着：select * from product pn;成立
select price+10 from product；只是在显示价格的时候加10，原来的表格数据不变化
并且该列名字变成“price + 10”，最好加个别名

5.2.运算符

5.2.1.算数运算符

5.2.2.比较运算符

重点：max（）和greatest（）的区别：
max（列名）；返回某一列中的最大值，例如max（column）
greatest（列名1，列名2.....）：某几列的最大值，横向求最大；
例如：greatest（time1，time2，time3）：返回三列中的最大值
还可以greatest（1，2，3）：返回3
***如果多列中的某一个列中的一个值为NULL，则不会进行比较直接返回结果为NULL。

min（）和least（）同理

注意：%表示任意字符
%裤：以“裤”为结尾的任意字符
裤%：以“裤”开头的任意字符
%裤%：含有”裤”的任意字符

注意：_下划线匹配单个字符

5.2.3.逻辑运算符

5.2.4.位运算符

5.3.排序查询

只有字段1 price相同，才会按照字段2 category进行排序

5.4.聚合查询（纵向查询）

针对单列的操作
这类聚合函数都忽略NULL，而不是视作0

5.5.分组查询（group by——相当于切割表格为小的临时表)

5.5.1.理解

group by category_id：相当于用category_id为依据，相同的被分到同一张临时表，并对其临时表进行count（pid）计算个数

select category_id, count(pid) from product group by category_id;

用了group by后对表格进行分组后，前面的select只能出现分组字段即category_id和组内一致的元素（聚合函数数据），即值都是一个
否则报错，因为对其分组后相当于这整个组对外呈现一列的数据，因此只能组内一致的才能被select并且输出

5.5.2.分组查询筛选（having）

注意不能用where
区别：
where 用在from后，用来筛选from子句中指定的行
having才是对分组产生临时组中结果进行筛选

5.6.分页查询

限制一页中出现的查询出的结果条数

select * from product limit 0,50;第一页从第0开始的50个商品
select * from product limit 50,50;第二页从第51个商品开始的50个商品
select * from product limit （n-1),50;第三页从第n个商品开始的50个商品

5.7.表格间数据导入（insert into）

要求
1.必须一一对应
2.Table 2必须存在

5.8.易错练习题

1.横向操作和纵向操作

此处不能用sum（），因为sum（）是纵向操作，此处要求横向操作

2.SQL执行顺序

一：整合表格：from，join……on……
from 表第一步
连接类型 join 表2 第二步
on 连接条件第三步
二：筛选整合出的大表格：where
where 筛选条件第四步
三：分割符合要求的大表格并筛选分组结果：group by，having
group by 分组列表第五步
having 分组后的筛选条件第六步
四：整体结果排序：order by
order by 排序列表第八步
五：结果分页：limit
limit 偏移，条目数第九步
六：输出：select
select 查询列表（字段）第七步

依照sql执行顺序，where在select之前，也就是处理where时候total_score还不存在，因此第二中方法错误

3.not表否定

6.正则表达式（REGEXP关键词）

正则表达式本身就是一类字符串

6.1.开头 ^

第一行：abc是否是以a开头的字符串——返回1，即true
第二行：选出所有pname以“海”开头的行

6.2.结尾$

6.3.匹配单个字符（.)

6.4.匹配括号内任意单个字符[abc]

[abc]：前面字符是否有abc之中的一个出现？

中括号中有的任意字符出现在前面即可。

6.5.取反（[^……])

[^abc]：意思：除了abc之外，是否有其他字符出现？有则1，没有则0

第三行：除了a之外，abc中是否有其他字符出现？显然有bc存在，所以是1.
注意：^必须出现在[ ]的第一位且在其内部

6.6.匹配出现0次或多次（*）

6.7.匹配出现一次或多次（*）

6.8.匹配0次或一次（？）

只能出现0次或者1次才为true，多了为false

6.9.匹配a或者b（a|b)

只要是a或者b，就是true

6.10.匹配字符出现次数（a{})

a{m}：字符a恰好出现m次
a{m,}：字符a出现最少m次
a{m,n}：字符a出现m到n次（包括m,n)

6.11.括号（）

括号里的内容看作一个整体，不加括号表示单独字符匹配

7.MYSQL多表操作

7.1.外键约束

外键是在从表中，与主表中的主键有着对应关系的键，相当于联合表格之间的线，例如join on中on后面的条件

7.1.1.创建外键约束（foreign key）

7.2.添加外键约束后的影响

1. 必须先添加主键约束；
2.给从表添加的外键列的值必须是主表中主键所有的。
3.从表外键值可以随意删除，主表主键值不被从表依赖才可以删除。

7.3.删除外键约束

7.4.多对多的外键约束

一般有多个主键，多个外键
外键在中间表中，即多个表因为对应关系合成的表，其他表的键为主键

7.2.多表联合查询

7.2.1.交叉连接（产生笛卡尔积）

产生的结果，表1*表2

7.2.2.内连接查询（inner join/natural join)

7.2.2.1.(inner join,注意：inner可以省略）

7.2.2.2.自然连接（natural join）

Natural join即自然连接，natural join等同于inner join或inner using，其作用是将两个表中具有相同名称的列进行匹配

7.2.3.外连接（outer join)

outer 可以省略

注意：mysql不支持满外连接，需要union实现
多次连接：select *from A left join B on 条件1
left join C on 条件2

7.2.4.子查询（select嵌套）

将查询结果当做一个表进行二次运用

7.2.4.1.子查询关键字

7.2.4.1.1.all（与子查询所有值比较）

7.2.4.1.2.any 和 some(作用一样）（与子查询任一值）

与返回结果中的任意值比较，其中一个值满足条件即可
例如： >any 大于其中一个值即可

7.2.4.1.3.in（判断值是否在子查询集合中）

7.2.4.1.4.exists（子查询存在则返回true，比in效率高）

理解: select .... from ... where exists(查询语句）
注意：一行一行判断exists是否为true
要执行select，首先判断exists是否成立，而exists是否成立取决于查询语句是否有数据返回，只要有一条数据返回则为true，就执行前面的select

易错点：
-- 查询是否有大于60 岁员工

select * from emp3 where exists(select * from emp3 where age > 60); ❌

原因：查询emp3第一行时，它判断后面的exists，但是在select * from emp3 where age>60中，前面的select * from emp3，此时的emp3是程序自行选取的一行，但是后面的emp3却不是从第一行开始，而是相当于一个完整的emp3表格，而后面的select * from emp3 where age>60相当于检索整张表格是否存在age>60的一列，显然是存在的，所以前面到哪一行他都存在，都输出

正解： select 是一行一行检索的，因此select后的emp3 a 在后面也变成了一行一行的
将后面的条件a.age与前面的emp3 a关联起来，也就是emp3看一行，age也只看一行即可
select * from emp3 a where exists(select * from emp3 b where a.age>60);

7.2.4.1.5.自关联（必须取别名）

例如： select * from table1 a,table2 b where a.eid = b.manager.id;

8.MYSQL函数

8.1.聚合函数（对纵列进行操作）

8.1.1.group_contact()（与group by连用，一组一行）

将纵列变成行输出
如果加上group by分组，输出结果是每一组为一行
seperator ‘ ’：一定要放在最末尾

8.2.数学函数（大小写一样，后面可以接字段）

8.3.字符串函数

补充：length(s):放回字符串所占字节

补充：
ltrim（）：去除字符串左边空格
rtrim（）：去除字符串右边空格
trim（）：去除两端空格

8.4.日期函数

%Y：年；%m:月；%d：日；%H：小时；%i：分钟；%s:秒

8.5.控制流函数

8.5.1.IF逻辑判断语句

8.5.2.case when语句

判断expression和condition是否相等，相等则输出相应的result
注意：只有一个result可以被执行，相当于后面的是else if

或者：

case when 条件1（例如：2>1) then result1

when 条件2 then result 2;

8.6.窗口函数

partition by和group by相似

8.6.1.序号函数

1. row_number():分组后每个组的第一行为1，第二行为2……
2. rank（）：分组后每个组的第一位为1，如果有相同的排序值（salary)可以并列，但如果并列了两个1，则第三个为3
3.dense_rank():分组后每个组的第一位为1，如果有相同的排序值（salary)可以并列，但如果并列了两个1，则第三个为2

8.6.1.1.求topN问题（子嵌套出新参数，用新参数）

为什么不能直接select …… where t.rn <=3？
因为先执行from后面的内容，from后是where，where的时候还没有rn
因此需要子嵌套

8.6.2.开窗聚合函数

8.6.2.1.sum

有order by:输出当前行（包括）之前所有选中数据的累加结果。
没有order by：默认将分组内的所有数据进行sum操作。

还可以写成：

sum（salary) over (partition by dname rows between preceding and current row)as c1,

意思：从之前行加到现在行

还可以写成：between 3 preceding and current row

当前行前三行加到当前行

between current and unbounded following

当前行加到最后

8.6.2.2.max（）

从开头到当前行的最大值

8.6.3.分布函数

8.6.3.1.CUME_DIST

8.6.3.2.Percent_rank()

这里的rows是分组内的总行数
rank由rank（）函数求

8.6.4.前后函数

8.6.4.1.LAG

lag(expression,n,default):返回该行的前n行的语句（在一组内），如果为null，则设为默认值default。

8.6.5.头尾函数

返回该分组内第一个（最后一个）expr的值。

8.6.6.其他函数-nth_value(),ntile()

8.6.6.1.nth_value(expr,n)

8.6.6.2.ntile(n)

例子：ntile（3），假设一组有6行数据，ntile（3），将6列分为3组，每两个一组，于是输出结果：
rn = 1 1 2 2 3 3(也就是每组的编号依次递增，同组编号相同）

9.MYSQL的视图（view)

查看表和视图：show tables；

相当于子嵌套的封装；
视图本身不存储数据，只存储数据的逻辑，这些数据存在原来的表中。

9.1.视图的创建

核心： create view view_name as select……

9.2.修改视图

9.3.视图更新（更新视图数据就是更新原表数据）

9.3.1.不可更新的视图

9.3.2.更新操作

insert into view_name values()

update view_name set

9.4.其他操作

10.MYSQL存储过程（相当于“方法”，“函数”）

10.1.定义存储过程

dilimiter：自定义结束符例如：dilimiter ；——将结束符指定为；
自定义结束符推荐：$$ 或者 \\

10.2.调用存储过程

call 存储名（）；

10.3.定义变量（需要给变量赋值）

10.3.1.局部变量

10.3.1.1.变量赋值

10.3.2.用户变量（可以全局使用（@var_name)）

10.3.3.系统变量

10.3.3.1.全局变量（系统提供的）

10.3.3.2.会话变量(全局变量的拷贝，只在当前会话使用）

10.4.参数传递

10.4.1.in(传入变量）

10.4.2.out(传出，类似return）

给传出去的参数赋值： select a,b,c…… into a1，b1，c1……
要接受变量要自定义一个用户变量

10.4.3.inout（开始传入，结束传出）

10.5.流程控制

10.5.1. IF（一个IF一个分号）

end if后面需要加分号

10.5.2.case语句（一个case一个分号）

10.5.3.循环语句

10.5.3.1.while

标签可写可不写

10.5.3.1.1.while + leave（跳出循环——break）

10.5.3.1.2.while + iterate（跳出此次循环，不进行下面语句——continue）

10.5.3.2. repeat……until……（注意repeat和until后面都没“；”）

10.5.3.3.loop（相当于while（true）——用leave退出循环）

10.6.游标（相当于指针，指向对应数据）

理解：
1.声明游标：在声明游标的同时，给游标赋值（相当于将游标指向一个select出的表格）
2.打开游标：只有先打开才能进行后续操作
3.取值：游标声明后指向一个表格，但取值的时候游标每fetch一次，实际上是指向它所指向的表格中的一行数据，取值语法就是将游标指向的那一行数据赋值给into后面的内容。
如果需要再次获取，需要第二个fetch语句。

10.7.异常处理

设置了句柄，相当于告诉程序如果相应错误发生了，就直接运行句柄语句。

continue：处理异常后继续执行下列语句
exit：直接退出程序

declare flag int default 1;这是定义标记值（如果易仓触发则flag为0，以flag为条件决定一些语句是否执行）

11.MYSQL存储函数

创建函数时候需要写下面一行语句：
--允许创建函数权限信任
set global log_bin_trust_function_creators = TRUE;

11.1.函数创建

11.1.1.无传入参数

11.1.2有传入参数

11.2.函数调用

直接select
select function（）；

12.MYSQL触发器（与存储过程不同的是它自动触发）

用处：用一个用触发器自动修改的表格记录另一个主表格的修改情况，相当于日志。

12.1.创建触发器

触发事件只能写：insert ， delete ， update
before 和 after意思：在触发时间发生之前/之后执行语句

12.2.new 和 old（触发器获取主表格被修改或添加的数据）

12.3.查看/删除

12.4.注意事项——少用触发器

触发器少用，效率低。

13.MYSQL的索引——提高查找效率

没有索引就会从列表的第一行开始找，全表扫描；
有索引相当于对数据进行分类，例如查找单词，有了分类就会按首字母去查找，b开头直接从b开始找，没有索引从a开始找。

13.1.单列索引

13.1.1.普通索引（索引值不唯一，可以NULL）

13.1.1.1创建

注意：如果定义age为索引，搜索数据时条件用age才会更快，因为数据会用Btree搜索，如果没有用age去筛选，与没有索引没什么区别。

13.1.1.2.查看索引

注意：只要是主键自动创建索引

查找语句中，只有数据库名和表名不同，其他的照抄下列即可

13.1.1.2.删除索引

13.1.2.唯一索引（索引唯一，可以为NULL）

13.1.2.1.创建

13.1.2.2.删除索引（drop index）

13.1.3.主键索引（索引唯一且不能为空）

只要创建了主键，就自动添加了主键索引

13.2.组合索引（多个列组合起来的索引）

13.2.1.创建

creat index :创建普通索引
creat unique index:创建唯一索引

13.2.2.索引的最左原则（优先和最左边的匹配）

要么最左边的相同，要么全部相同。

13.3.全文索引

13.3.1.创建(用create创建全文索引比直接在表中添加快得多）

创建全文索引时候，先将数据放入表中再create 全文索引。

13.3.2.使用

没有结果时因为小于最小搜索长度

数据量大的时候，全文索引比like 快得多
select * from t_article where content like "%you%".

13.4.空间索引（了解即可，必须声明为非空）

13.5.索引原理（选择最优索引，减少磁盘I/O存取次数）

13.5.1.索引相关算法——Hash算法

缺点：无法大小比较，因为排列时无序的

例如：
20通过hash算法，相当于20——f(x)——0X14，放到0X14这一类里，之后要找20直接通过索引在这一类找即可。

13.5.2.二叉树（主键值在二叉树中存储）

平衡二叉树

13.5.3.BTREE（***）

B+tree 与 B-tree最大的不同在于：
B+tree的底层叶子节点囊括了所有节点并且已经排序，而上面的父节点用于分类查找

13.5.3.1.索引应用

13.5.3.1.1.MyLSAM——存地址

注意：
1.叶子节点时存放数据记录的地址
2.箭头表示白色区域存放的是指针
如果要查找数据是50，就按红色箭头的顺序找到50，然后知道放在0x77，然后在0x77的地址找就找到了数据。

13.5.3.1.2.InnoDB直接存放数据

13.6.索引的特点

13.7.创键索引原则

14.MYSQL的存储引擎（InnoBD)

常用的操作引擎：INNOBD，MYISAM.

14.1.常用操作

15.MYSQL的事务（保证成批的SQL语句要么全部执行，要么全部不执行）

相当于把一批SQL语句打包到一块，这一块语句要么全部执行成功，要么全部失败。

15.1.基础操作

回滚事务：只有没有进行提交的事务才能进行回滚，回滚事务相当于把内存中的数据还原，如果提交了就已经落实到硬盘上了，不能再进行回滚。

Mysql图形界面必须要提交后才显示最新状态，而select显示的是内存中的最新状态。

运用例子：
每一行语句如果默认自动提交，那么每次执行一条SQL语句都自动提交事务
但是如果我们想要把两条SQL语句绑定在一起，成一个事务，就需要将自动提交关闭，这样才能使得两个语句要么同时成功，要么同时失败。

例如：转账，必须一个扣钱，一个加钱，而且必须同时成功或失败。

15.2.事务的特性

15.3.事务的隔离级别

15.3.1.事务的隔离级别操作

1.读未提交：A事务在自身没提交的时候可以看到B事务未提交的值。
脏读:读取了到未提交的数据。
例子：A事务设置了张三的钱＋1000，变成了2000，还未提交，此时B事务读取A事务未提交的值，即读出张三的钱为2000，但是A突然返回rollback，此时B事务再读取时候就是1000，这就是脏读、

2.读提交：A事务在自身没提交的时候只能看到B事务提交后的值。
不可重复读：A事务在提交之前，B事务提交事务的前后，读取到B事务的操作数据不一致。
例如：B事务设置了张三的钱-999，变为1，但是还没提交，此时A事务读取张三的钱，仍然是1000，但是当B事务提交以后，A事务再次查看，张三的钱变成了1.

3.可重复读：A事务只有在自身提交以后，才能看到B已经提交的事务。
幻读：A事务在提交前后，看到数据不一致。
例如：B事务设置了张三的钱-999，变为1，且已经提交（没有提交的话A事务看不到），但是A事务自身没有提交，看到的还是1000，而当A提交后看到的则是1.

4.串行：A和B两个事务，当一个事务率先对表格进行操作，另一个事务不能动，只能等第一个事务commit后才能进行操作。
安全但是效率低，A事务操作表格，B就不能操作了。

16.MYSQL的锁机制

16.1.锁的种类和概述

16.2.加锁

16.2.1.MYISAM——只支持表锁

读锁是共享锁，用户a加上了的同时用户b也可以加。用户a用select查询表1时，给表1加上读锁后，用户b也可以读表1并且也可以加上读锁，但是一旦加上读锁，用户就不可以在未解锁状态下访问其他表，除非解锁。
解锁语句： unlock tables;

写锁是互斥锁，用户a加上了后，用户b不可以访问及修改相应表格，也不可以加锁，只有当用户a解锁后才能进行其他操作，

16.2.2.INNOBD——支持行锁和表锁

update，delete，insert语句，innoBD会自动给涉及数据加锁
select语句，innobd不加锁

16.2.2.1.加锁

给一行语句用了update，delete，insert后系统自动给这行语句加行锁，其他事务只可以读但是不可以改此行数据。

17.MYSQL的日志（相当于记录曾经的操作）

17.1.错误日志

上述命令可以找到相应的位置，然后自行寻找打开。

17.2.binlog日志（除了查询之外的操作语句记录）

17.2.1.操作

17.3.查询日志(显示所有语句）

查看目录：自行打开安装目录下的data->host_name.log文件

17.4.慢查询日志（用于找查询时间长的语句，方便改善）

18.MYSQL的优化

18.1.查看SQL执行频率（针对频率较多的语句进行优化）

18.2.定位低效率执行SQL

18.2.1.慢查询日志

18.2.2.show processlist

命令：show processlist

作用：查看当前客户端连接服务器的线程执行状态信息

18.3.explain分析执行计划

18.3.1.查询结果含义

18.3.2.explain之id（id越大，优先级越高，越先被执行）

18.3.3.explain之select_type

1.primary：包含子查询语句的最外层查询（主干）——灰色的主干为primary
例子：select * from table 1 where id = （select id from table 2 ）
2 .subquery：在select和where 中包含的子查询—— 红色的为subquery