MySQL笔记
2022-08-02 08:01:26 66 举报MySQL笔记，里面没有用户权限分配等内容
作者其他创作
大纲/内容
DDL
库操作
查询所有数据库：show databases;
查询数据库创建语句：show create database db;
创建数据库：create database db;
创建数据库（判断，如果不存在则创建）：create database if not exists db;
创建数据库（指定字符集）：create database db character set utf8;
创建数据库（判断，如果不存在则创建并且指定字符集）：create database if not exists db character set gbk;
修改数据库（修改字符集）：alter database db character set utf8;
删除数据库：drop database db;
删除数据库（判断，如果存在则删除）：drop database if exists db;
使用数据库：use db;
查看当前使用的数据库：select database();
表操作
查询所有的数据表：show tables;
查询表结构：desc tb;
查询表字符集：show table status from db like 'tb';
创建表（创建商品表）：create table product(id int,name varchar(20),price double,stock int,insert_time date);
修改表名：alter table 旧表名 rename to 新表名;
修改表的字符集：alter table 表名 character set 字符集名称;
单独添加一列：alter table 表名 add 列名 数据类型;
修改某列的数据类型：alter table 表名 modify 列名 数据类型;
修改列名和数据类型：alter table 表名 change 旧列名 新列名 数据类型;
删除数据表中某一列：alter table 表名 drop 列名;
删除数据表：drop table 表名;
删除数据表（判断，存在则删除）：drop table if exists 表名;
DML
新增表数据
给指定列添加数据：insert into 表名 (列名1,列名2,...) values (值1,值2,...);
给全部列添加数据：insert into 表名 values (值1,值2,...);
批量添加数据：insert into 表名 values (值1,值2,...),(值1,值2,...);
修改以及删除表数据
修改表中数据：update 表名 set 列名1=值1,列名2=值2, ...[where条件];
删除表中数据：delete from 表名 [where条件];
DQL
表数据查询语法
select 字段列表 from 表名列表 where 条件列表 group by 分组字段 having 分组后过滤条件 order by 排序 limit 分页
查询全部数据
查询全部的表数据：select * from 表名;
查询指定字段的表数据：select 列名1,列名2, from 表名;
去除重复查询：select distinct * from 表名;
计算列的值（四则运算）：select ifnull(列名1，0) （+，-，*，/）数值,列名2 from 表名;
起别名查询：select 列名1 as 别名1 from 表名;
条件查询
条件
语法：select 列名列表 from 表名 where 条件；
聚合函数查询
聚合函数
语法：select 聚合函数(列名) from 表名 [where 条件];
排序查询
语法：select [列名列表] from 表名 [where条件] order by 列名1 排序方式1，列名2 排序方式2;
分组查询
语法：select [列名列表] from 表名 [where条件] group by 分组列名 [having 分组后的过滤条件] [order by 列名 排序方式];
分页查询
语法：slect [列名列表] from 表名 [wheret条件] [group by 分组列名] [having 分组后的过滤条件] [order by 列名 排序方式] limit 当前页数,每页显示条数;
当前页公式：当前页数=（当前页-1）* 每页显示条数;
约束
外键约束
建表时添加外键约束：CREATE TABLE 表名(列名 数据类型 约束,...CONSTRAINTS 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名));
建表后单独添加外键约束：ALTER TABLE 表名 ADD CONSTANT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名);
删除外键约束：ALTER TABLE 表名 DROP FOREIGN KEY 主键名;
外键级联
添加级联更新
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名) ON UPDATE CASCADE;
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名) ON UPDATE CASCADE;
添加级联删除
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名) ON DELETE CASCADE;
同时添加级联更新和级联删除
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名) ON UPDATE CASCADE ON DELETE CASCADE;
主键约束
建表时添加主键约束：create table 表名(列名1 数据类型 primary key,...,列名2 数据类型 约束);
删除主键约束：alter table 表名 drop primary key;
建表后单独添加主键约束：alter table 表名 modify 列名 数据类型 primary key;
主键自增约束
建表时添加主键自增约束：create table 表名(列名1 数据类型 primary key auto_increment,...,列名2 数据类型 约束);
删除主键自增约束：alter table 表名 modify 列名 数据类型;
建表后单独添加主键自增约束：alter table 表名 modify 列名 数据类型 auto_increment;
唯一约束
建表时添加唯一约束：create table 表名(列名1 数据类型 约束,...,列名2 数据类型 unique);
删除唯一约束：alter table 表名 drop index 列名;
建表后单独添加唯一约束：
非空约束
建表时添加非空约束：create table 表名(列名1 数据类型 约束,...,列名2 数据类型 not null);
删除非空约束：alter table 表名 modify 列名 数据类型;
建表后单独添加非空约束：alter table 表名 modify 列名 数据类型 not null;
多表操作
多表关系
一对一
使用场景：人与身份证
建表原则：在任意一张表建立外键约束，去关联另一张表的主键；
一对多
使用场景：用户和订单
建表原则：在多的一方建立外键约束，去关联一的一方的主键；
多对多
使用场景：学生和课程；
建表原则：需要借助第三张中间表，中间表至少包含两个列，这两个列作为中间表的外键，分别关联两张表的主键；
多表查询
内连接查询
查询原理：内连接查询的是两张表有交集的部分数据（有主外键关联的数据）；
查询语法
显示内连接：select 列名列表 from 表名1 [inner] join 表名2 on 条件;
隐式内连接：select 列名列表 from 表名1,表名2 where 条件;
外连接查询
左连接
查询原理：查询左表全部数据，以及左右两张表有交集的部分数据
查询语法：select 列名列表 from 表1 left [outer] join 表2 on 条件;
右连接
查询原理：查询右表全部数据，以及左右两张表有交集的部分数据
查询语法：select 列名列表 from 表1 right [outer] join 表2 on 条件;
子查询
结果为单行当列
查询作用：可以将结果作为另一条语句的查询条件，使用运算符判断。=,>,<,>=,<=等条件
查询语法：select 列名列表 from 表名 where 列名=(select 列名 from 表名 [where条件])
结果为多行多列
查询作用：可以将结果作为另一条语句的查询条件，使用运算符in或not in进行判断。
查询语法：select 列名列表 from 表名 where 列名 [not] in (select 列名 from 表名 [where条件])
结果为多行多列
查询作用：可以将结果作为一张虚拟表参与查询。
查询语法：select 列名列表 from 表名 [别名],(select 列名列表 from 表名 [where条件]) 别名 [where条件];
自关联查询
概念：在同一张表中数据有关联性，我们可以把这张表当成多个表来查询。
视图
视图的创建与查询
创建视图的语法：create view 视图名称 [(列名列表)] as 查询语句;
查询视图的语法：select 列名列表 from 视图名称;
视图的修改与删除
修改视图数据语法：update 视图名 set 列名=值 where条件;(注意：修改视图中的值，源表的值也会该表)
修改视图数据结构语法：alter view 视图名 (列名列表) as 查询语句;
删除视图语法：drop view [if exists] 视图名;
备份和恢复
命令行方式
备份：mysqldump -u root -p 数据库名>文件保存路径;
恢复
1、登录mysql数据库
2、删除已备份的数据库
3、重新创建数据库名称相同的数据库
4、使用该数据库
5、导入执行文件：source 备份文件全路径
图形化界面方式
备份：选中数据库右键备份
恢复：删除备份的数据库，重新创建同名的数据库，右键导入，选择之前备份的sql文件导入即可。
存储过程
存储过程的创建以及调用
存储过程的创建
调用存储过程：CALL 存储过程名([实际参数列表]);
存储过程的查看和删除
查看数据库中所有的存储过程：SELECT * FROM mysql.proc where db='数据库名';
删除存储过程：DROP PROCEDURE [IF EXISTS] 存储过程名称;
存储过程语法
变量
定义变量：DECLARE 变量名 数据类型 [DEFAULT默认值];
变量赋值方式一：SET 变量名=变量值;
变量赋值方式二：SELECT 列名 INTO 变量名 FROM 表名 [WHERE条件];
if语句
if语句标准语法
参数传递
in：代表输入参数，需要由调用者传递实际数据(默认)
out：代表输出参数，该参数可以作为返回值
inout：代表既可以作为输入参数，也可以作为输出参数
while循环
存储函数：和存储过程相比，存储函数必须有返回值
创建存储函数
调用存储函数：select 函数名称(实际参数);
删除存储函数：drop function 函数名称;
注意事项
触发器
触发器介绍：与表有关得数据库对象，可以在insert、update、delete之前或之后出发并执行触发器中定义的SQL语句。
触发器的作用：协助应用系统在数据库端确保数据的完整性、日志记录、数据校验等操作。
触发器原理：使用别名NEW和OLD来引用触发器中发生变化的内容记录。
触发器的分类
触发器的操作
创建触发器
查看触发器：show triggers;
删除触发器：drop trigger 触发器名称;
事务
事务的介绍：一条或多条sql语句组成一个执行单元，其特点是这个单元要么同时成功要么同时失败。
事务的操作
开启事务：start transaction;
回滚：rollback;
提交：commit;
事务的提交方式
事务提交方式分类
自动提交（MySQL默认）
手动方式
查看事务提交方式：select @@autocommit;（1： 自动提交，2：手动提交）
修改事务提交方式：set @@autocommit=数字;
事务的四大特征（ACID）
原子性（Atomicity）：事务包含的所有操作要么全部成功，要么全部回滚。
一致性（Consistency）： 事务执行之前和执行之后必须处于一致状态。
隔离性（isolation）：多个用户访问同一张表时，多个并发事务之间要相互隔离。
持久性（Durability）：事务一旦提交了，对数据库中的改变就是永久性的。
事务的隔离级别
事务隔离级别分类
查询事务的隔离级别：select @@transaction_isolation;
修改数据库隔离级别：set global transaction isolation level 级别字符串;（修改后需要重新连接数据库后生效）
MySQL存储引擎
存储引擎介绍
存储引擎的操作
查询数据库支持的存储引擎：show engines;
查询某个数据库中所有数据表的存储引擎：show table status from 数据库名称;
查询某个数据库中某个数据表的存储引擎：show table status from 数据库名称 where name='数据表名称';
创建数据表指定存储引擎：create table 表名(列名 数据类型,...) engine=引擎名称;
修改数据表的存储引擎：alter table 表名 engine = 引擎名称;
存储引擎的选择
MyISAM
特点：不支持事务和外键操作。读取速度快、节约资源。
使用场景：以查询为主、只有很少的更新和删除操作，并且对事务的完整性、并发要求不高。
InnoDB
特点：MySQL的默认引擎、支持事务和外键操作。
使用场景：对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，读写频繁。
MEMORY
特点：将数据保存在内存中，在需要快速定位记录和其他类似数据的环境下，可以提供更快的访问。
使用场景：通常用于更新不太频繁的小表，用来快速得到访问结果。
总结：针对不同场景来选择合适的存储引擎，不确定的情况使用数据库的默认存储引擎。
MySQL索引
MySQL索引的介绍：本质就是一种数据结构，可以提高查询数据的效率。
索引的分类
按功能分类
普通索引：最基本的索引，没有任何限制。
唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值组合必须唯一
主键索引：一种特殊的唯一索引，不允许有空值。在建表时有主键列同时创建主键索引。
联合索引：就是将单列索引进行组合。
外键索引：只有InnoDB引擎支持外键索引，用来保证数据的一致性、完整性和实现级联操作。
全文索引：快速匹配全部文档的方式。InnoDB引擎5.6版本后才支持全文索引。MEMORY引擎不支持。
按结构分类
BTree索引：MySQL使用最频繁的索引数据结构，是InnoDB和MylSAM存储引擎默认的索引类型，底层基于B+Tree数据结构。
Hash索引：MySQl中MEMORY存储引擎默认支持的索引类型。
索引的操作
创建索引：create [unique|fulltext] index 索引名称 [using 索引类型] on 表名(列名...);
查看索引：show index from 表名;
添加索引
普通索引：alter table 表名 add index 索引名称(列名);
组合索引：alter table 表名 add index 索引名称(列名1,列名2,...);
主键索引：alter table 表名 add primary key (主键列名);
外键索引：alter table 表名 add constraint 外键名 foreign key (本表外键列名) references 主表名(主键列名);
唯一索引：alter table 表名 add unique 索引名称(列名);
全文索引：alter table 表名 add fulltext 索引名称(列名);
删除索引：drop index 索引名称 on 表名;
索引的原理
磁盘存储
系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位。
位于同一个磁盘块中的数据会别一次性读取出来，而不是需要什么读取什么。
InnDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位，InnDB存储引擎中默认每页大小为16kb。
InnoDB存储引擎将若干个地址连接磁盘块，以此来达到页大小为16kb，在数据查询时如果每一页的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。
BTree
每个节点不仅包含key值，还有数据。会增加查询数据时磁盘的IO数。
B+Tree
非叶子节点只存储key值。
所有数据存储在叶子节点。
所有叶子节点之间都有连接指针。
索引的设计原则
1、对查询频次较高，且数据量比较大的表建立索引。
2、使用唯一索引，区分度越高，使用索引的效率越高。
3、索引字段的选择，最佳候选列应当从where子句的条件中提取。
4、索引虽然可以有效的提升查询数据的效率，但并不是多多益善。
组合索引中注意事项
最左匹配原则（适合组合索引）。
索引字段出现的顺序可以是任意的，MySQL优化器会帮我们自动调整where条件中的顺序。
锁机
锁机制：数据库为了保证数据的唯一性，在共享资源被并发访问时变得安全锁设计的一种规则。
锁分类
按操作分类
共享锁：也叫读锁。针对同一份数据，多个事务读取操作可以同时加锁而不影响，但是不能修改数据。
排他锁：也叫写锁。当前的操作没有油完成前，会阻断其他操作的读取和写入。
按粒度分类
表级锁：会锁定整个表。开销小，加锁快。锁定粒度大，发生锁冲突概率高，并发度低。不会出现死锁情况。
行级锁：会锁定当前行。开销大，加锁慢。锁定粒度小，发生锁冲突概率低，并发度高。会出现死锁情况。
按使用方式分类
悲观锁：每次查询数据都会认为别人会修改数据，很悲观，所以查询时加锁。
乐观锁：每次查询数据时都认为别人不会修改数据，很乐观，但是更新时会判断一下在此期间别人有没有去更新这个数据。
不同存储引擎支持的锁
InnoDB共享锁
特点：数据可以被多个事务查询，但是不能修改。
语法：select 语句 lock in share mode;
注意：所过采用带所引的列加锁为行锁，不带索引的列加锁为表锁。
InnoDB排他锁
特点：加锁的数据，不能被其他事务加锁查询或修改。
语法：select 语句 for update;
注意：普通查询是没有问题，普通修改、加锁查询、加锁修改需要前一个加排他锁的事务执行提交后方可执行。
MyiSAM读锁
特点：所有连接只能查询数据，不能修改数据。
语法
加锁：lock table 表名 read;
解锁：unlock tables;
注意：加读锁后所有连接不能修改数据。
MyiSAM写锁
特点：其他连接不能查询和修改数据。
语法
加锁：lock table 表名 write;
解锁：unlock tables;
注意：当前连接可以进行查询和修改数据。
乐观锁和悲观锁
悲观锁：一般依靠关系型数据库提供的锁机制。
乐观锁：用户自己实现，加标记思想。
0 条评论
下一页
