看前tip1.本文全部用小写字母书写因为我看见大写单词不认识2.[ ]表示可选项3.{ }表示需要写| 在大括号中选一个总结查找的执行顺序确实不好记所以我写在前面了。①from - ②join on - ③where - ④group by - ⑤having - ⑥select - ⑦distinct - ⑧order by - ⑨limit我的理解①②数据从哪些表来③第一次筛选数据④分组⑤分组之后筛选⑥显示哪些列⑦⑧⑨对显示的列进行排版。一.库的操作1.查看数据库show databases;2.创建数据库create {database | schema} [if not exists] 数据库名 [create_option]... 下面的是create_option的可选项 { character [] charset_name | collate [] collation_name } -- charset_name的可选项有{utf8mb4 | latin1}但是latin1不能储存中文 -- collation_name的可选项有{utf8mb4_0900_ai_ci | utf8mb4_general_ci} 我们一般选择第一个因为表示口声不敏感和大小写不敏感3.修改数据库alter database 数据库名 alter_option -- alter_option可以选的内容就是上面create_option可选的内容4.删除数据库drop { database | schema } [if exists] 数据库名5.其他指令select database(); -- 查看当前选中是那个数据库 use 数据库名; -- 切换到这个数据库 show charset; -- 查看数据库⽀持的字符集编码 show collation; -- 查看数据库支持的排序规则 show variables like %character%; -- 查看系统默认支持的字符集 show variables like %collation%; -- 查看系统默认排序规则 show create database 数据库名; -- 查看数据库创建语句二.数据类型1.数据值类型2.字符串类型3.日期类型我一般只用过varchar(M)和int这两个数据类型三.表的操作1.查看所有表show tables;2.创建表①直接创建一个表create table [if not exists] 表名 ( 列名 数据类型 [约束] [comment 注解内容] [, 列名 数据类型 [约束] [comment 注解内容]] ... )[character set 字符集] [collate 排序规则]; -- 这里的字符集和排序规则就是上面创建库里面的规则②通过其他的表创建一个结构相同的表create table 新表的表名 like 旧表的表名;3.查看表结构desc 表名4.修改表结构alter table 要修改的表名 [alter_option [, alter_option] ...]; alter_option: { table_options | add [column] col_name column_definition [first | after col_name] -- 向表中添加一列 | modify [column] col_name column_definition [first | after col_name] -- 修改某列的长度 | drop [column] col_name -- 删除某列 | rename column old_col_name to new_col_name -- 重命名某列 | rename [to | as] new_tbl_name -- 修改表名 }5.删除表drop table [if exists] 表名 [,表名] ... ; -- 也可以用库名.表名的方法指定四.表的增删查改1.新增insert into 表名[(列名 [,列名] ...)] values (插入的值)[,(插入的值)] ... ; -- 注意这里插入的值一定要与插入的列名相对应2.查询下面时全部写在一起的格式select [distinct] 列名 [, 列名] ... from 表名 [where where_condition] [group by {col_name | expr}, ...] [having where_condition] [order by {col_name | expr } [asc | desc], ... ] [limit {[start_count] 显示的行数 | 显示的行数 offset start_count}]①select1.全列查询select * from exam;2.指定列查询select 列名[, 列名]... from 表名;3.查询的字段为表达式(就是列表可以为一个表达式)-- 示例1把所有学⽣的语⽂成绩加10分 select id, name, chinese 10 from exam; -- 示例2计算所有学⽣语⽂、数学和英语成绩的总分 select id, name, chinese math english from exam; -- 建议用了表达式之后建议换取别名因为这样太难看了。4.为查询结果指定别名select 需要取别名的列(这个列也可以为上面所说的表达式) [as] 别名 [, ...] from 表名; -- 这里的as可写可不写5.结果去重的查询select distinct 列名... from 表名; -- 只有所有的列表相同才会算重复才会被去除。②where1.比较运算符2.逻辑运算符3.where的使用格式select 列名[,列名] from 表名 where 用上面的关键字组成的判断语句4.模糊查询①查询所有姓孙的同学select * from exam where name like 孙%;②查询姓孙且姓名共有两个字同学select * from exam where name like 孙_;注意%代表0个或者多个字符_代表一个字符③order byselect ... from 表名 [where...] order by 需要排序的列名 [asc | desc] [,需要排序的列名 [asc | desc]], ... ; -- asc 为升序从⼩到⼤ -- desc 为降序从⼤到⼩ -- 默认为 asc -- 然后这里的列名是可以使用别名的这是因为执行顺序决定的注意事项NULL 进⾏排序时视为⽐任何值都⼩升序出现在最上⾯降序出现在最下⾯。④分页查询-- 起始下标为 0 -- 从 0 开始筛选 num 条结果 select ... from table_name [where ...] [order by ...] limit num; -- 从 start 开始筛选 num 条结果 select ... from table_name [where ...] [order by ...] limit start, num; -- 从 start 开始筛选 num 条结果⽐第⼆种⽤法更明确建议使⽤ select ... from table_name [where ...] [order by ...] limit num offset start;3.插入查询结果-- 代码模板 insert into table_name [(column [, column 1 ...])] select ...演示案例原表中的记录去重后写入到新表insert into t_recored_new select distinct * from t_recored_old;4. 聚合函数①常用的函数演示案例数据表演示案例1.记录有多少行数据 -- count2.有多少人参加参加了英语考试 -- countcount不会统计null所以只有6个3.统计所有学生英语成绩总分 -- sum②分组查询这个查询时用于把几行数据根据一个指标合在一起的然后用聚合函数把这些合在一起的行算出我们需要的内容。1.基本使用select column1,sum(column), ... from 表名 group by column1; -- 对column1进行分组2.having筛选用不了where因为where会比分组先进行所以只能用having进行筛选案例演示把上面的两个知识点都综合了5.修改update 表名 set 列名 值 [,列名 值] [where ...] [order by ...] [limit ...]注意事项①sql中没有 这个运算符号②不加where会导致全表更新6.删除数据delete from 表名 [where where_condition] [order by ...] [limit row_count]注意事项①根据后面的表名添加内容的限制条件筛选删除的内容。②如果不添加的这些限制条件就会把整张表删除。五.约束约束书写的地方create table [if not exists] 表名 ( 列名 数据类型 [约束] [comment 注解内容] [, 列名 数据类型 [约束] [comment 注解内容]] ... )[character set 字符集] [collate 排序规则];1.not null约束添加了这个约束之后这个位置的值不能填null查看表结构这加了not null 之后就是yes会变成no。2.unique唯一约束添加了这个约束之后这一列就不能在有重复值了。查看表结构这力添加了unique之后key的主键从空变成nui。3.default 默认值默认值约束添加了这个约束之后如果这里在插入的时候不填值就会用这个默认值。查看表结构default下面的null会变成默认值4.primary key主键约束添加了这个约束之后这一列在插入的时候不能为空且不能有重复值。①查看表结构key从空的变为pri②自增主键只需要在添加主键约束的时候后面加上 auto_increment 这个关键字就可以了。1.每次自增就是在最大值的上面加12.插入自增主键后表的结构③主键或唯⼀键冲突时的更新操作否则插⼊。-- 方法1 insert into ... values ... on duplicate key update column value [, column value] ... -- 更新这条主键重复的值上面的这个模板也不好理解建议结合ai的讲解一起看-- 方法2 replace [into] table_name [(column [, column] ...)] values (value_list) [, (value_list)] ... ;④表中最多只能有一个主键⑤复合主键主键冲突由选中作为复合主键的内容全部相同才会引起主键冲突。-- 声明复合主键的样例 create table student ( id bigint, name varchar(20), primary key (id, name) # 指定复合主键 );5.foreign key外键约束外键⽤于定义主表和从表之间的关系外键约束主定义在从表的列上主表关联的列必须是主键或唯⼀约束当定义外键后要求从表中的外键列数据必须在主表的主键或唯⼀列存在或为null。1.创建外键约束上图是我们需要建立的关系假设主表已经创建我们直接创建从表-- 语法格式 foreign key (id) references 主表的表名(id); create table student( id bigint PRIMARY KEY auto_increment, name varchar(20) not null, age int DEFAULT 18, class_id bigint, foreign key (class_id) references class(id) # 创建外键约束 )2.查看表结构key的空变为MUL3.删除主表某条记录时从表中不能有对该记录的引⽤删除主表时要先删除从表。六.多表查询两张表取笛卡尔积的结果这里我们需要查几张表就需要把几张表取笛卡尔积。1.内连接SELECT p.FirstName, a.City FROM Person p INNER JOIN -- INNER 关键字可省略直接写 JOIN Address a ON p.PersonId a.PersonId; -- 连接条件写在 ON 子句中select 字段 from 表1 别名1, 表2 别名2 where 连接条件 and 其他条件;2.外连接这是右外连接和内连接的区别然后左外连接与右外连接类似①左外连接返回左表的所有记录和右表中匹配的记录。如果右表中没有匹配的记录则结果集中对应字段会显示为NULL。-- 左外连接表1完全显⽰ select 字段名 from 表名1 left join 表名2 on 连接条件;②右外连接返回右表的所有记录和左表中匹配的记录。如果左表中没有匹配的记录则结果集中对应字段会显示为NULL。-- 右外连接表2完全显⽰ select 字段 from 表名1 right join 表名2 on 连接条件;3.自连接之前我们做比较都是在一行中的某一列和莫一列之间比较如果我们在某一列中需要某几行的内容做比较就需要自连接。select 字段列表 from 表A 别名A join 表A 别名B on 条件...;注意连接的时候一定要取别名。4.子查询(嵌套查询)①⼦查询是把⼀个select语句的结果当做另⼀个select语句的条件这里一定要注意对应关系select * from table1 where col_name1 { | in} ( select col_name1 from table2 where ... ) -- 用表示单行子查询 -- 用in表示多行子查询 -- 如果这里的col_name1是多列这就是多列子查询②上面是把子查询写在where语句里面其实子查询的语句也可以写在from语句里面这就相当于使用子查询创建了一张临时表让后在临时表里面查询。5.合并查询结果使用时一定要列名匹配要不然返回的结果没有意义。在单表查询中建议使用or连接但是在多表连接一般是用到下面的关键字。-- 使用案例 select * from student where id 3 union select * from student1; -- 其实语法规则很简单只需要把两条sql语句用符号连接起来就可以了。①union该操作符⽤于取得两个结果集的并集。当使⽤该操作符时会⾃动去掉结果集中的重复⾏。②union all该操作符⽤于取得两个结果集的并集。当使⽤该操作符时不会去掉结果集中的重复⾏。七.视图视图是⼀个虚拟的表它是基于⼀个或多个基本表或其他视图的查询结果集。1.视图的优点1. 简单性视图可以将复杂的查询封装成⼀个简单的查询。例如针对⼀个复杂的多表连接查询可以创建⼀个视图⽤⼾只需查询视图⽽⽆需了解底层的复杂逻辑。2. 安全性通过视图可以隐藏表中的敏感数据。例如⼀个系统的⽤⼾表中可以创建⼀个不包含密码列视图普通⽤⼾只能访问这个视图⽽不能访问原始表。3. 逻辑数据独⽴性视图提供了⼀种逻辑数据独⽴性即使底层表结构发⽣变化只需修改视图定义⽽⽆需修改依赖视图的应⽤程序。使⽤到应⽤程序与数据库的解耦4. 重命名列视图允许⽤⼾重命名列名以增强数据可读性。2.创建视图create view 视图名 [(column_list)] as (sql语句); -- 上面包含了两种创建视图的方法 -- 1. 创建视图在select中使⽤别名不需要用到上面[]里面的内容。 -- 2.创建视图指定结果集中的列名需要用到上面[]里面的内容。 column_list是指定的别名。注意 column_list 中可能会出现重复的列所以需要注意取别名因为sql语句中可能有多个表表中的列重复了所以需要取别名。然后在里面书写列的时候语法为 ‘ 表名.列名 ’ 。可以用 show tables 查看创建了哪些视图。3.使用视图-- 方法1 show create view 视图名; -- 方法2 select * from 视图名;4.修改数据①通过真实表修改数据会影响视图②通过视图修改数据会影响基表在以下情况下修改不了视图1.创建视图时使⽤聚合函数的视图2.创建视图时使⽤ distinct3.创建视图时使⽤ group by 以及 having ⼦句4.创建视图时使⽤ union 或 union all5.查询列表中使⽤⼦查询6.在 from ⼦句中引⽤不可更新视图7.使用了order by也不能更新视图5.删除视图drop view 视图的名字八.索引1.索引的数据结构①哈希索引一般不会使用不支持范围查询哈希冲突可能导致性能下降。②B树索引结构如下图数据全部储存在叶子节点。2.索引的分类①主键索引• 当在⼀个表上定义⼀个主键 PRIMARY KEY 时InnoDB使⽤它作为聚集索引。• 推荐为每个表定义⼀个主键。如果没有逻辑上唯⼀且⾮空的列或列集可以使⽤主键则添加⼀个⾃增列。②普通索引• 最基本的索引类型没有唯⼀性的限制。• 可能为多列创建组合索引称为复合索引或组全索引。③唯一索引• 当在⼀个表上定义⼀个唯⼀键 UNQUE 时⾃动创建唯⼀索引。• 与普通索引类似但区别在于唯⼀索引的列不允许有重复值。④全文索引• 基于⽂本列(CHAR、VARCHAR或TEXT列)上创建以加快对这些列中包含的数据查询和DML操作。• ⽤于全⽂搜索仅MyISAM和InnoDB引擎⽀持。两个查询时的重要概念回表查询当查询所需的字段不完全包含在索引树中时数据库需要通过索引定位到数据表的行再从数据表中读取额外的字段。索引覆盖 查询所需要的所有字段都能从索引树中直接获取查询不需要访问表中的数据页。3.使用索引①自动创建1.当我们为⼀张表加主键约束(Primary key)外键约束(Foreign Key)唯⼀约束(Unique)时MySQL会为对应的的列⾃动创建⼀个索引。2.如果表不指定任何约束时MySQL会⾃动为每⼀列⽣成⼀个索引并⽤ ROW_ID 进⾏标识②手动创建1.手动创建主键索引-- ⽅式⼀创建表时创建主键 create table t_test_pk ( id bigint primary key auto_increment, name varchar(20) ); -- ⽅式⼆创建表时单独指定主键列 create table t_test_pk1 ( id bigint auto_increment, name varchar(20), primary key (id) ); -- ⽅式三修改表中的列为主键索引 create table t_test_pk2 ( id bigint, name varchar(20) ); alter table t_test_pk2 add primary key (id) ; alter table t_test_pk2 modify id bigint auto_increment; -- 这里的alter可以看看上面表修改的语法2.手动创建唯一索引和创建主键索引类似只是把primary key 换成了 unique 了所以这里不演示。3.手动创建普通索引查看表结构的时候key里面显示的是 MUL-- ⽅式⼀创建表时指定索引列 create table t_test_index ( id bigint primary key auto_increment, name varchar(20), sno varchar(10), index(sno) ); -- ⽅式⼆修改表中的列为普通索引 create table t_test_index1 ( id bigint primary key auto_increment, name varchar(20), sno varchar(10) ); alter table t_test_index1 add index (sno) ; -- ⽅式三单独创建索引并指定索引名建议使用 create table t_test_index2 ( id bigint primary key auto_increment, name varchar(20), sno varchar(10) ); create index 索引名 on t_test_index2(sno);③创建复合索引创建复合索引和上面的创建普通索引一样只是把上面的需要创建索引的字段用,分隔开就可以了。索引名就是第一个字段的名字。④查看索引-- 方式一 show keys from 表名; -- 方式二 show index from 表名; -- 方式三这个显示的少一点 desc 表名;⑤删除索引1.删除主键索引alter table 表名 drop primary key;如果是自增的主键需要先删除自增的属性才可以删除主键-- 下面是一个案例是删除自增不是模板模板就是前面的修改表里面的 alter table t_test_index6 modify id bigint;2.删除其他索引alter table 表名 drop index 索引名; -- 唯一健也是这样删除的唯一健的索引名就是字段的名字 -- 好像创建索引的时候没有索引名索引名就是字段的名字九.事务1.事务的四大特性ACID①Atomicity (原⼦性)⼀个事务中的所有操作要么全部成功要么全部失败不会出现只执⾏了⼀半的情况如果事务在执⾏过程中发⽣错误会回滚 Rollback 到事务开始前的状态就像这个事务从来没有执⾏过⼀样。②Consistency (⼀致性)在事务开始之前和事务结束以后数据库的完整性不会被破坏。这表⽰写⼊的数据必须完全符合所有的预设规则包括数据的精度、关联性以及关于事务执⾏过程中服务器崩溃后如何恢复。③Isolation (隔离性)数据库允许多个并发事务同时对数据进⾏读写和修改隔离性可以防⽌多个事务并发执⾏时由于交叉执⾏⽽导致数据的不⼀致。事务可以指定不同的隔离级别以权衡在不同的应⽤场景下数据库性能和安全。④Durability (持久性)事务处理结束后对数据的修改将永久的写⼊存储介质即便系统故障也不会丢失。2.使用事务# 开始⼀个新的事务 start transaction; # 或 begin # 提交当前事务并对更改持久化保存 commit # 回滚当前事务取消其更改 rollback3.设置保存点在事务执⾏的过程中设置保存点回滚时指定保存点可以把数据恢复到保存点的状态。-- 设置保存点 savepoint 保存点名 -- 回滚到哪个保存点 rollback to 想要回滚到的保存点 -- 回滚时不指定保存点直接回滚到事务开始时的原始状态事务关闭4.手动/自动提交事务默认情况下MySQL是⾃动提交事务的也就是说我们执⾏的每个修改操作⽐如插⼊、更新和删除都会⾃动开启⼀个事务并在语句执⾏完成之后⾃动提交发⽣异常时⾃动回滚。1.检测事务是否为自动提交show variables like autocommit;2.设置事务为手动还是自动提交-- 设置事务自动提交 set autocommit 1; -- 方式一 set autocommit on; -- 方式二 -- 设置事务手动提交 set autocommit 0; -- 方式一 set autocommit o ff; -- 方式二注意事项• 只要使用 start transaction 或 begin 开启事务必须要通过 commit 提交才会持久化与是否设置 set autocommit 无关。• 手动提交模式下不用显示开启事务执行修改操作后提交或回滚事务时直接使用 commit或rollback• 已提交的事务不能回滚5.事务与隔离级别事务间不同程度的隔离称为事务的隔离级别不同的隔离级别在性能和安全方面做了取舍有的隔离级别注重并发性有的注重安全性有的则是并发和安全适中在MySQL的InnoDB引擎中事务的隔离级别有四种。1.查看和设置事务的隔离级别①事务的隔离级别分为全局作用域和会话作用域查看不同作用域事务的隔离级别可以使用以下的-- 全局作用域 SELECT GLOBAL.transaction_isolation; -- 会话作用域 SELECT SESSION.transaction_isolation; -- 会话作用域的意思是不同的客户端打开的一个终端我们称为一个会话②设置事务的隔离级别和访问模式语法 set [global | session] transaction isolation level level_1; -- 通过 global|session 分别指定不同作用域的事务隔离级别 -- 设置全局事务隔离级别后续所有事务生效不影响当前事务 -- 设置会话事务隔离级别当前会话后续的所有事务生效不影响当前事务可以在任何时候执行 -- 如果不指定任何作用域设置只针对下一个事务随后的事务恢复之前的隔离级别 -- 隔离级别 level_1: { REPEATABLE READ -- 可重复读 | READ COMMITTED -- 读已提交 | READ UNCOMMITTED -- 读未提交 | SERIALIZABLE -- 串行化 }2.不同隔离级别存在的问题①未读提交脏读脏读是指一个事务读取了另一个事务尚未提交的修改数据。如果后续事务回滚这个数据就会变得无效从而导致读取到的数据是“脏”的。②读已提交不可重复读是指同一个事务中先后两次读取同一行数据时读到的值不一致。这通常是因为另一个事务在中途修改并提交了该数据。③可重复读幻读是指一个事务中两次执行相同的查询时结果集中出现了新的数据行或消失了某些数据行。这是因为另一事务在中途插入或删除了记录。④串行化此时所有事务串行执行可以解决所有并发中的安全问题。