数据库基础
1.什么是数据库？
存储数据用文件就可以了，为什么还要弄个数据库？
文件保存数据有以下几个缺点：
- 文件的安全性问题
- 文件不利于数据查询和管理
- 文件不利于存储海量的数据
- 文件在程序中控制不方便
数据库存储介质：
- 磁盘
- 内存
为了解决上述问题，专家们设计出更加利于管理数据的东西 —— 数据库 ，它能更有效的管理数据。
2.主流数据库
- SQL Sever： 微软的产品，.Net程序员的最爱，中大型项目。
- Oracle： 甲骨文产品，适合大型项目，复杂的业务逻辑，并发一般来说不如MySQL。
- MySQL：世界上最受欢迎的数据库，属于甲骨文，并发性好，不适合做复杂的业务。主要用在电商，SNS，论坛。对简单的SQL处理效果好。
- PostgreSQL :加州大学伯克利分校计算机系开发的关系型数据库，不管是私用，商用，还是学术研究使用，可以免费使用，修改和分发。
- SQLite： 是一款轻型的数据库，是遵守ACID的关系型数据库管理系统，它包含在一个相对小的C库中。它的设计目标是嵌入式的，而且目前已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。
- H2：是一个用Java开发的嵌入式数据库，它本身只是一个类库，可以直接嵌入到应用项目中。
3.基本使用
连接
mysql -h 127.0.0.1 -P 3306 -u root -p- 如果没有写-h 127.0.0.1默认是连接本地
- 如果没有写-P 3306默认是连接3306号端口
服务器，数据库，表的关系
创建数据库
create database school;使用数据库
use school;创建数据库表
create table student(id int,name varchar(32),gender varchar(2)
);表中插入数据
insert into student (id, name, gender) values (1, '张三', '男');
insert into student (id, name, gender) values (2, '李四', '女');
insert into student (id, name, gender) values (3, '王五', '男');查询表中的数据
select * from student;4.MySQL架构
MySQL 是一个可移植的数据库，几乎能在当前所有的操作系统上运行，如 Unix/Linux 、 Windows
、Mac 和 Solaris 。各种系统在底层实现方面各有不同，但是 MySQL 基本上能保证在各个平台上的物理体 系结构的一致性。
5.SQL分类
- DDL：数据定义语言，用来维护存储数据的结构，代表指令: create, drop, alter
- DML：数据操纵语言，用来对 数据进行操作，代表指令： insert ， delete ， update
- DCL： 数据控制语言，主要负责权限管理和事务，代表指令： grant ， revoke ， commit
6.存储引擎
存储引擎是：数据库管理系统如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术 的实现方法。
MySQL 的核心就是插件式存储引擎，支持多种存储引擎。
数据库的操作
1.创建数据库
语法：
CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [,
create_specification] ...]
create_specification:
[DEFAULT] CHARACTER SET charset_name
[DEFAULT] COLLATE collation_name- 大写的是关键字
- []表示可选
- CHARACTER SET：指定字符集
- COLLATE：指定校验规则
举例：
- 创建一个db1的数据库
create database db1;- 创建一个使用utf8字符集的db2数据库
create database db2 charset=utf8;- 创建一个使用utf字符集，并携带校对规则的数据库
create database db3 charset=utf8 collate utf8_general_ci;2.字符集和校验规则
查看一个默认的字符集和校验规则
字符集主要是控制用什么语言。比如 utf8 就可以使用中文。
校验规则主要控制如何比较和排序这些字符。比如用utf8_ general_ ci不区分大小写，用utf8_ bin区分大小写。
3.操作数据库
查看数据库
show databases;显示创建语句
show create database 数据库名；修改数据库
语法：
ALTER DATABASE db_name
[alter_spacification [,alter_spacification]...]
alter_spacification:
[DEFAULT] CHARACTER SET charset_name
[DEFAULT] COLLATE collation_name- 对数据库的修改主要是修改数据库的字符集，校验规则
比如：
alter database mytest charset=gbk;数据库删除
DROP DATABASE [IF EXISTS] db_ name删除之后
- 数据库内部看不到对应的数据库
- 对应的数据库文件夹被删除，级联删除，里面的数据表被删
备份
语法：
# mysqldump -P3306 -u root -p 密码 -B 数据库名 > 数据库备份存储的文件路径比如：
mysqldump -P3306 -u root -p123456 -B mytest > D:/mytest.sql还原：
source D:/mysql-5.7.22/mytest.sql;注意：
- 可以只备份一张表
mysqldump -u root -p 数据库名 表名1 表名2 > D:/mytest.sql- 可以同时备份多个数据库
mysqldump -u root -p -B 数据库名1 数据库名2 ... > 数据库存放路径表的操作
创建表
CREATE TABLE table_name (
field1 datatype,
field2 datatype,
field3 datatype
) character set 字符集 collate 校验规则 engine 存储引擎;- field 表示列名
- datatype 表示列的类型
- character set 字符集，如果没有指定字符集，则以所在数据库的字符集为准
- collate 校验规则，如果没有指定校验规则，则以所在数据库的校验规则为准
不同的存储引擎，创建的表的文件不一样。
查看表的结构
desc 表名;修改表
在项目实际开发中，经常修改某个表的结构，比如字段名字，字段大小，字段类型，表的字符集类型， 表的存储引擎等等。我们还有需求，添加字段，删除字段等等。这时我们就需要修改表。
语法
ALTER TABLE tablename ADD (column datatype [DEFAULT expr][,column
datatype]...);
ALTER TABLE tablename MODIfy (column datatype [DEFAULT expr][,column
datatype]...);
ALTER TABLE tablename DROP (column);删除表
语法
DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...数据类型
表的约束
表的约束又很多，主要是null/not null,default, comment, zerofill，primary key，auto_increment，unique key
空属性
- 两个值： null （默认的）和 not null( 不为空 )
- 数据库默认字段基本都是字段为空，但是实际开发时，尽可能保证字段不为空，因为数据为空没办法参与运算。
默认值
默认值：某一种数据会经常性的出现某个具体的值，可以在一开始就指定好，在需要真实数据的时候， 用户可以选择性的使用默认值。
数据在插入的时候不给这个字段赋值，就是用默认值
列描述
列描述： comment ，没有实际含义，专门用来描述字段，会根据表创建语句保存，用来给程序员或 DBA 来进行了解。
不能通过desc查看，但是可以通过show
比如这个表的名字叫做User
desc User;show create table User\G;zerofill
刚开始学习数据库时，很多人对 数字类型 后面的长度很迷茫。通过 show 看看 tt3 表的建表语句：
mysql> show create table tt3\G
***************** 1. row *****************
Table: tt3
Create Table: CREATE TABLE `tt3` (
`a` int(10) unsigned DEFAULT NULL,
`b` int(10) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk
1 row in set(0.00 sec) 可以看到 int(10), 这个代表什么意思呢？整型不是 4 字节码？这个 10 又代表什么呢？其实没有 zerofill 这个属性，括号内的数字是毫无意义的。a 和 b 列就是前面插入的数据，如下：
mysql> insert into tt3 values(1,2);
Query OK, 1 row affected (0.00 sec)
mysql> select * from tt3;
+------+------+
| a | b |
+------+------+
| 1 | 2 |
+------+------+ 但是对列添加了 zerofill 属性后，显示的结果就有所不同了。修改 tt3 表的属性：
mysql> alter table tt3 change a a int(5) unsigned zerofill;
mysql> show create table tt3\G
*************************** 1. row ***************************
Table: tt3
Create Table: CREATE TABLE `tt3` (
`a` int(5) unsigned zerofill DEFAULT NULL, --具有了zerofill
`b` int(10) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk
1 row in set (0.00 sec)mysql> select * from tt3;
+-------+------+
| a | b |
+-------+------+
| 00001 | 2 |
+-------+------+ zerofill：宽度小于设定的宽度，会自动填充0。
这里数据库内部存储的还是 1,00001， 只是设置了 zerofill 属性后的一种格式化输出而已。
主键
主键： primary key 用来唯一的约束该字段里面的数据，不能重复，不能为空，一张表中最多只能有一个主键；
主键所在的列通常是整数类型。
- 主键约束：逐渐对应的字段不能重复，一旦重复，操作失败
- 当表创建好以后但是没有主键的时候，可以再次追加主键
alter table 表名 add primary key(字段列表)- 删除主键
alter table 表名 drop primary key;- 复合主键
在创建表的时候，在所有字段之后，可以使用primary key来创建主键，如果有多个字段作为主键，可以使用复合主键。
mysql> create table tt14(
-> id int unsigned,
-> course char(10) comment '课程代码',
-> score tinyint unsigned default 60 comment '成绩',
-> primary key(id, course) -- id和course为复合主键
-> );
Query OK, 0 rows affected (0.01 sec)mysql> desc tt14;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | PRI | 0 | | <= 这两列合成主键
| course | char(10) | NO | PRI | | |
| score | tinyint(3) unsigned | YES | | 60 | |
+--------+---------------------+------+-----+---------+-------+
自增长
auto_increment ：当对应的字段，不给值，会自动的被系统触发，系统会从当前字段中已经有的最大值+1操作，得到一个新的不同的值。通常和主键搭配使用，作为逻辑主键。
自增长的特点 :
- 任何一个字段要做自增长，前提是本身是一个索引
- 自增长必须是整数
- 一张表最多只能有一个自增长
索引：
在关系数据库中， 索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结 构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。 索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
索引提供 指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。 数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的 SQL 语句执行得 更快，可快速访问数据库表中的特定信息。
唯一键
一张表中有往往有很多字段需要唯一性，数据不能重复，但是一张表中只能有一个主键：唯一键就可以
解决表中有多个字段需要唯一性约束的问题。
唯一键的本质和主键差不多，唯一键允许为空，而且可以多个为空，空字段不做唯一性比较。
外键
外键用于定义主表和从表之间的关系：外键约束主要定义在从表上，主表则必须是有主键约束或 unique 约束。当定义外键后，要求外键列数据必须在主表的主键列存在或为null 。
语法
foreign key (字段名) references 主表(列)个人理解，从表中和主表相关联的字段，需填入主表中出现过的值或空值。
表的增删改查
1.CREATE
INSERT [INTO] table_name
[(column [, column] ...)]
VALUES (value_list) [, (value_list)] ...
value_list: value, [, value] ...可能由于主键或者唯一键对应的值已经存在而导致插入失败。可以这样设置--如果表中有冲突数据的话，就改为更新操作。
INSERT ... ON DUPLICATE KEY UPDATE
column = value [, column = value] ...替换
-- 主键 或者 唯一键 没有冲突，则直接插入；
-- 主键 或者 唯一键 如果冲突，则删除后再插入
REPLACE INTO students (sn, name) VALUES (20001, '曹阿瞒');
Query OK, 2 rows affected (0.00 sec)
-- 1 row affected: 表中没有冲突数据，数据被插入
-- 2 row affected: 表中有冲突数据，删除后重新插入2.Retrieve
SELECT
[DISTINCT] {* | {column [, column] ...}
[FROM table_name]
[WHERE ...]
[ORDER BY column [ASC | DESC], ...]
LIMIT ...举个例子来介绍一下查询语句
-- 创建表结构
CREATE TABLE exam_result (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL COMMENT '同学姓名',
chinese float DEFAULT 0.0 COMMENT '语文成绩',
math float DEFAULT 0.0 COMMENT '数学成绩',
english float DEFAULT 0.0 COMMENT '英语成绩'
);
-- 插入测试数据
INSERT INTO exam_result (name, chinese, math, english) VALUES
('唐三藏', 67, 98, 56),
('孙悟空', 87, 78, 77),
('猪悟能', 88, 98, 90),
('曹孟德', 82, 84, 67),
('刘玄德', 55, 85, 45),
('孙权', 70, 73, 78),
('宋公明', 75, 65, 30);
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0 全列查询
SELECT * FROM exam_result;
+----+-----------+-------+--------+--------+
| id | name | chinese | math | english |
+----+-----------+-------+--------+--------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孙悟空 | 87 | 78 | 77 |
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 刘玄德 | 55 | 85 | 45 |
| 6 | 孙权 | 70 | 73 | 78 |
| 7 | 宋公明 | 75 | 65 | 30 |
+----+-----------+-------+--------+--------+
7 rows in set (0.00 sec)指定列查询
SELECT id, name, english FROM exam_result;
+----+-----------+--------+
| id | name | english |
+----+-----------+--------+
| 1 | 唐三藏 | 56 |
| 2 | 孙悟空 | 77 |
| 3 | 猪悟能 | 90 |
| 4 | 曹孟德 | 67 |
| 5 | 刘玄德 | 45 |
| 6 | 孙权 | 78 |
| 7 | 宋公明 | 30 |
+----+-----------+--------+
7 rows in set (0.00 sec)查询字段为表达式
SELECT id, name, chinese + math + english FROM exam_result;
+----+-----------+-------------------------+
| id | name | chinese + math + english |
+----+-----------+-------------------------+
| 1 | 唐三藏 | 221 |
| 2 | 孙悟空 | 242 |
| 3 | 猪悟能 | 276 |
| 4 | 曹孟德 | 233 |
| 5 | 刘玄德 | 185 |
| 6 | 孙权 | 221 |
| 7 | 宋公明 | 170 |
+----+-----------+-------------------------+
7 rows in set (0.00 sec)为查询结构指定别名
SELECT id, name, chinese + math + english 总分 FROM exam_result;
+----+-----------+--------+
| id | name | 总分 |
+----+-----------+--------+
| 1 | 唐三藏 | 221 |
| 2 | 孙悟空 | 242 |
| 3 | 猪悟能 | 276 |
| 4 | 曹孟德 | 233 |
| 5 | 刘玄德 | 185 |
| 6 | 孙权 | 221 |
| 7 | 宋公明 | 170 |
+----+-----------+--------+
7 rows in set (0.00 sec)结果去重
SELECT DISTINCT math FROM exam_result;
+--------+
| math |
+--------+
| 98 |
| 78 |
| 84 |
| 85 |
| 73 |
| 65 |
+--------+
6 rows in set (0.00 sec)where条件
| 运算符 | 说明 |
| >, >=, <, <= | 大于，大于等于，小于，小于等于 |
| = | 等于， NULL 不安全，例如 NULL = NULL 的结果是 NULL |
| <=> | 等于， NULL 安全，例如 NULL <=> NULL 的结果是 TRUE(1) |
| !=, <> | 不等于 |
| BETWEEN a0 AND a1 | 范围匹配， [a0, a1] ，如果 a0 <= value <= a1 ，返回 TRUE(1) |
| IN (option, ...) | 如果是 option 中的任意一个，返回 TRUE(1) |
| IS NULL | 是 NULL |
| IS NOT NULL | 不是 NULL |
| LIKE | 模糊匹配。 % 表示任意多个（包括 0 个）任意字符； _ 表示任意一个字符 |
逻辑运算符
| 运算符 | 说明 |
| AND | 多个条件必须都为 TRUE(1) ，结果才是 TRUE(1) |
| OR | 任意一个条件为 TRUE(1), 结果为TRUE(1) |
| NOT | 条件为 TRUE(1) ，结果为 FALSE(0) |
结果排序
-- ASC 为升序（从小到大）
-- DESC 为降序（从大到小）
-- 默认为 ASC
SELECT ... FROM table_name [WHERE ...]
ORDER BY column [ASC|DESC], [...];分页查询
-- 起始下标为 0
-- 从 s 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n
-- 从 0 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
;
-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;3.UPDATE
UPDATE table_name SET column = expr [, column = expr ...]
[WHERE ...] [ORDER BY ...] [LIMIT ...]4.DELETE
DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]截断表
TRUNCATE [TABLE] table_name这个操作慎用
- 只能对整表操作，不能像DELETE一样针对部分数据操作
- 实际上MySQL不对数据操作，所以比DELETE更快，但是TRUNCATE在删除数据的时候，并不经过真正的事物，所以不能回滚
- 会重置AUTO_INCREMENT项
5.聚合函数
| 函数 | 说明 |
| COUNT([DISTINCT] expr) | 返回查询到的数据的 数量 |
| SUM([DISTINCT] expr) | 返回查询到的数据的 总和，不是数字没有意义 |
| AVG([DISTINCT] expr) | 返回查询到的数据的 平均值，不是数字没有意义 |
| MAX([DISTINCT] expr) | 返回查询到的数据的 最大值，不是数字没有意义 |
| MIN([DISTINCT] expr) | 返回查询到的数据的 最小值，不是数字没有意义 |
6.group by子句的使用
select column1, column2, .. from table group by column;小结：
SQL查询中各个关键字的执行先后顺序 from > on> join > where > group by > with > having > select > distinct > order by > limit
函数
日期函数
| 函数名称 | 描述 |
| current_date() | 当前日期 |
| current_time() | 当前时间 |
| current_timestamp() | 当前时间戳 |
| date(datetime) | 返回datetime参数的日期部分 |
| date_add(date,interval d_value_type) | 在date中添加日期或事件interval后数值单位可以是：year minute second day |
| date_sub(date,interval d_value_type) | 在date中减去日期或事件interval后的数值可以是：year minute second day |
| datediff(date1,date2) | 两个日期的差，单位是天 |
| now() | 当前日期时间 |
字符串函数
| charset(str) | 返回字符串字符集 |
| concat(string2 [,...]) | 连接字符串 |
| instr(string,substring) | 返回substring在string中出现的位置，没有返回0 |
| ucase(string2) | 转换成大写 |
| lcase(string2) | 转换成小写 |
| left(string2,length) | 从string2中的左边起取length个字符 |
| length(string) | string的长度 |
| replace(str,search_str,replace_str) | 在str中用replace_str替换search_str |
| strcmp(string1,string2) | 逐字符比较两字符串大小 |
| substring(str,position,[,...length]) | 从str的postion开始，取length个字符 |
| ltrim(string), rtrim(string), trim(string) | 去除前空格或后空格 |
数学函数
| 函数名称 | 描述 |
| abs(number) | 绝对值函数 |
| bin(decimal_number) | 十进制转换成二进制 |
| hex(decimalNumber) | 转换成十六进制 |
| conv(number,from_base,to_base) | 进制转换 |
| ceiling(number) | 向上取整 |
| floor(number) | 向下取整 |
| format(number,decimal_places) | 格式化，保留小数位数 |
| hex(decimalNumber) | 转换成十六进制 |
| rand() | 返回随机附带念书，范围[0.0,1.0) |
| mod(number,denominator) | 取模，求余 |
表的内连和外连
内连接
内连接实际上就是利用where子句对两种表形成的笛卡尔积进行筛选，前面用到的都是内连接，也就是在开发过程中使用最多的连接查询
select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件；外连接
外连接分为左外连接和右外连接
左外连接
如果联合查询，左侧的表完全显示我们就说是左外连接。
举个栗子
-- 建两张表
create table stu (id int, name varchar(30)); -- 学生表
insert into stu values(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');
create table exam (id int, grade int); -- 成绩表
insert into exam values(1, 56),(2,76),(11, 8);也就是这样
mysql> select * from stu;
+------+------+
| id | name |
+------+------+
| 1 | jack |
| 2 | tom |
| 3 | kity |
| 4 | nono |
+------+------+
4 rows in set (0.00 sec)mysql> select * from exam;
+------+-------+
| id | grade |
+------+-------+
| 1 | 56 |
| 2 | 76 |
| 11 | 8 |
+------+-------+
3 rows in set (0.00 sec)使用这条查询
select * from stu left join exam on stu.id=exam.id;结果是
mysql> select * from stu left join exam on stu.id=exam.id;
+------+------+------+-------+
| id | name | id | grade |
+------+------+------+-------+
| 1 | jack | 1 | 56 |
| 2 | tom | 2 | 76 |
| 3 | kity | NULL | NULL |
| 4 | nono | NULL | NULL |
+------+------+------+-------+
4 rows in set (0.00 sec)
右外连接
如果联合查询，右侧的表完全显示就是我们所说的右外连接
select 字段 from 表名1 right join 表名2 on 连接条件；比如
mysql> select * from exam right join stu on exam.id=stu.id;
+------+-------+------+------+
| id | grade | id | name |
+------+-------+------+------+
| 1 | 56 | 1 | jack |
| 2 | 76 | 2 | tom |
| NULL | NULL | 3 | kity |
| NULL | NULL | 4 | nono |
+------+-------+------+------+
4 rows in set (0.00 sec)
索引
索引：提高数据库的性能，索引是物美价廉的东西了。不用加内存，不用改程序，不用调 sql ，只要执行正确的 create index ，查询速度就可能提高成百上千倍。但是天下没有免费的午餐，查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的IO 。所以它的价值，在于提高一个 海量数据的检索速度。
常见的索引分为：
- 主键索引(primary key)：唯一并且非空，一张表只能有一个
- 唯一索引(unique)：值唯一，可以有多个
- 普通索引(index)：最常用，可以有重复值
- 全文索引(fulltext)：用于文本检索，MyISAM引擎支持，默认不支持中文
磁盘结构基础
- 扇区：磁盘最小存储单位，默认是512字节，部分新磁盘为4K
- 柱面、磁道、磁头：磁盘物理寻址方式，系统通过LBA线性地址转换为CHS寻址
- IO效率关键：随机访问效率低于连续访问，应减少IO次数
MySQL与磁盘交互
InnoDB引擎：IO基本单位是16KB（page），通过Buffer Poll缓存数据，减少磁盘IO。
Page结构：每个Page存多条记录，通过page_prev和page_next组成双向链表，内部数据按主键排序。
索引的数据结构——B+树
非叶子结点只存键值和指针，叶子节点存数据，树高更低，IO次数少
叶子结点之间相连，方便范围查询
聚簇索引与非聚簇索引
聚簇索引：
- 主键索引与数据存储在一起，叶子结点存放完整数据
- 辅助索引(普通索引)的叶子节点存放主键，查询需要“回表”（先查辅助索引得到主键，在查主键得到数据）
非聚簇索引：
- 索引和数据分离，叶子节点都存放数据地址
- 辅助索引和主键索引结构类型，查询效率一致
索引的创建方式
主键索引：
-- 创建表时指定
CREATE TABLE user1(id INT PRIMARY KEY, name VARCHAR(30));
-- 表定义后添加
ALTER TABLE user3 ADD PRIMARY KEY(id);唯一索引：
CREATE TABLE user4(name VARCHAR(30) UNIQUE);
ALTER TABLE user6 ADD UNIQUE(name);普通索引：
CREATE TABLE user8(index(name)); -- 表定义时
ALTER TABLE user9 ADD INDEX(name); -- 表创建后
CREATE INDEX idx_name ON user10(name); -- 指定索引名全文索引：
CREATE TABLE articles(FULLTEXT(title, body)) ENGINE=MyISAM;
-- 查询使用MATCH...AGAINST
SELECT * FROM articles WHERE MATCH(title,body) AGAINST('database');索引的查询和删除
查询索引：
SHOW KEYS FROM 表名; -- 详细索引信息
SHOW INDEX FROM 表名;
DESC 表名; -- 简略信息删除索引：
ALTER TABLE 表名 DROP PRIMARY KEY; -- 删除主键索引
ALTER TABLE 表名 DROP INDEX 索引名; -- 删除普通索引
DROP INDEX 索引名 ON 表名; 索引创建的原则：
- 频繁作为查询条件的字段创建索引
- 唯一性差、更新频繁的字段不适合创建索引
- 不在where子句中出现的字段不创建索引
事务
事务的必要性
比如在买票的过程中，多个客户端同时查询并修改数据，可能导致同一张表被重复售卖，这也绝对不允许的。根本原因就是CURD没有加事务控制，导致非原子性操作。
事务的属性(ACID)：
- 原子性：事务内操作要么全部成功，要么全部回滚
- 一致性：事务执行前后数据库状态必须一致
- 隔离性：并发事务之间互相不干扰
- 持久性：事务提交之后数据永远保存
事务基础
- 引擎支持：InnoDB支持事务，MyISAM不支持事务（可用show engines验证）
- 提交方式
- 自动提交（默认）：单条SQL自动提交
- 手动提交：begin开启事务，comit提交事务，rollback回滚事务
- 设置：set autocommit=0/1关闭/开启自动提交
- 事务操作关键点：
- 没有提交的事务即使崩溃，MySQL也会自动回滚
- 已经提交的事务崩溃之后也不会丢失（持久性）
- begin会隐式禁用当前事务的自动提交
事务隔离级别
| 隔离级别 | 脏读 | 不可重复的 | 幻读 | 加锁方式 |
| 读未提交 | √ | √ | √ | 不加锁 |
| 读已提交 | × | √ | √ | 不加锁 |
| 可重复读 | × | × | × | 不加锁 |
| 串行化 | × | × | × | 加锁 |
MySQL的RR级别通过Next-Key锁解决幻读问题
- 读未提交（RU）：事务可读到其他事务未提交的数据（脏读），生产环境禁用
- 读已提交（RC）：只读到已提交的数据，但同一事务多次查询结果可能不同
- 可重复读（RR）：同一事物内多次查询结果一致，通过快照读实现
- 串行化：所有操作串行执行，效率低，极少使用
MVCC：
多版本并发控制（MVCC）解决读写冲突，核心机制：
隐藏字段：
- DB_TRX_ID：最近修改事务ID
- DB_ROLL_PTR：指向undo log历史版本的指针
undo log：
- 存储数据的历史版本，形成版本链
Read View：
- 事务快照读时生成，包含：
- m_ids：活跃事务ID列表
- up_limit_id：最小活跃事务ID
- low_limit_id：下一个待分配的事务ID
- 可见性规则
- DB_TRX_ID < up_limit_id：可见（事务已提交）
- DB_TRX_ID >= low_limit_id：不可见（事务尚未开始）
- DB_TRX_ID在m_ids中：不可见（事务未提交）
RR和RC的本质区别：
- RR：事务首次快照读时候生成Read View，后续服用该视图。
- RC：每次快照读都生成新的Read View，导致不可重复读。
视图
视 图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。视 图的数据变化会影响到基表，基表的数据变化也会影响到视图。
基本使用
create view 视图名 as select语句; 删除视图
drop view 视图名;视图规则和限制：
- 和表一样，不能出现同名视图
- 创建视图数目无限制，但要考虑复杂查询创建为视图之后的性能影响
- 视图不能添加索引，也不能有关联的触发器或者默认值
- 视图可以提高安全性，必须具有足够的访问权限
- order by 可以用在视图中，但是如果从该视图检索数据 select 中也含有 order by ,那么该视图 中的 order by 将被覆盖
- 视图可以和表一起使用
用户管理
用户存储位置
- 所有用户信息存储在mysql.user系统表中
- 关键字段：
- host：允许登录的主机（losthost表示仅主机）
- user：用户名
- authentication_string：加密后的密码
- *_priv：权限字段
创建用户
CREATE USER '用户名'@'登录主机' IDENTIFIED BY '密码';删除用户
DROP USER '用户名'@'主机名'; -- 必须指定主机名！修改密码
#自己修改
SET PASSWORD = PASSWORD('新密码');#root修改其他用户
SET PASSWORD FOR '用户'@'主机' = PASSWORD('新密码');权限管理
授权语法
GRANT 权限列表 ON 数据库.对象 TO '用户'@'主机';举例
GRANT SELECT ON test.* TO 'user'@'localhost'; -- 授权test库所有表的查询权限权限作用域
| 作用范围 | 示例 | 说明 |
| 全局权限 | *.* | 所有数据库的所有对象 |
| 单库权限 | 数据库名.* | 指定数据库的所有的视图/表 |
| 单表权限 | 数据库名.表名 | 指定表的权限 |
查看权限
SHOW GRANTS FOR '用户'@'主机'; -- 示例：SHOW GRANTS FOR 'user'@'localhost';回收权限
REVOKE 权限列表 ON 数据库.对象 FROM '用户'@'主机';举例
REVOKE ALL ON test.* FROM 'whb'@'localhost'; -- 回收test库所有权限注意
- 最小权限原则：用户只拥有完整工作所需要的最小的权限，避免直接使用root账户操作日常业务
- 主机限制：创建用户时严格限制host
- 权限刷新：授权/回收之后执行刷新使权限生效
FLUSH PRIVILEGES;4. 权限列表参考
| 常用权限 | 使用对象 |
| SELECT | 表/视图 |
| INSERT | 表 |
| CREATE | 数据库/表 |
| DROP | 数据库/表 |
| EXECUTE | 存储过程 |
| GRANT OPTION | 允许给其他用户授权 |
典型流程示例
-- 1. 创建用户（仅允许本机登录）
CREATE USER 'zhangsan'@'localhost' IDENTIFIED BY 'StrongPwd123!';-- 2. 授权mytest库的所有权限
GRANT ALL ON mytest.* TO 'zhangsan'@'localhost';-- 3. 查看权限
SHOW GRANTS FOR 'zhangsan'@'localhost';-- 4. 回收删除权限
REVOKE DROP ON mytest.* FROM 'zhangsan'@'localhost';-- 5. 删除用户
DROP USER 'zhangsan'@'localhost';
![P8784 [蓝桥杯 2022 省 B] 积木画](http://pic.xiahunao.cn/P8784 [蓝桥杯 2022 省 B] 积木画)
)
