mySQL

存储引擎

存储引擎是 MySQL（以及其他关系型数据库）用于管理数据存储、读取、写入、缓存、事务、锁机制和索引实现等底层操作的组件。每种存储引擎都有自己的一套数据存储格式、索引算法、事务支持与锁机制，因此在性能、可靠性和功能上各有侧重

// 查看存储引擎
show engines;

存储引擎的对比

存储引擎	事务支持	锁机制	外键支持	性能特点	适用场景
InnoDB	支持	行级锁/MVCC	支持	适合高并发写操作	电商、金融、内容管理等要求数据一致性
MyISAM	不支持	表级锁	不支持	查询速度快	只读或读多写少的应用，如日志、数据仓库
Memory	不支持	表级锁	不支持	内存存储，极快	临时数据、会话、缓存
Archive	不支持	无锁	不支持	高速插入、压缩存储	归档数据、历史日志
CSV	不支持	无锁	不支持	文本存储，易于交换	数据导入导出、数据交换
NDB Cluster	支持	分布式锁	支持部分	高可用、高并发	分布式环境、实时高并发应用

SQL语句

SQL（Structured Query Language）是一种用于管理和操作关系型数据库的标准语言。它主要分为四大类，每一类都有各自的作用：

DDL【data definition language】数据定义语言，用来维护存储数据的结构

代表指令: create, drop, alter

DML【data manipulation language】数据操纵语言，用来对数据进行操作

代表指令： insert，delete，update

DML中又单独分了一个DQL，数据查询语言，代表指令： select

DCL【Data Control Language】数据控制语言，主要负责权限管理和事务

代表指令： grant，revoke，commit

DDL（数据定义语言，Data Definition Language）

DDL 主要用于定义和管理数据库结构，包括创建、修改和删除数据库对象。常用的语句及作用如下：

// create
CREATE TABLE table_name (
field1 datatype,
field2 datatype,
field3 datatype
) character set 字符集 collate 校验规则 engine 存储引擎;field 表示列名
datatype 表示列的类型
character set 字符集，如果没有指定字符集，则以所在数据库的字符集为准
collate 校验规则，如果没有指定校验规则，则以所在数据库的校验规则为准create table users (
id int,
name varchar(20) comment '用户名',
password char(32) comment '密码是32位的md5值',
birthday date comment '生日'
) character set utf8 engine MyISAM;不同的存储引擎，创建表的文件不一样。
users 表存储引擎是 MyISAM ，在数据目中有三个不同的文件，分别是：
users.frm：表结构
users.MYD：表数据
users.MYI：表索引// 查看表结构
desc 表名;// 创建一个使用utf8字符集的 db2 数据库
create database db2 charset=utf8;//查看系统默认字符集以及校验规则
show variables like 'character_set_database';
show variables like 'collation_database';// 查看数据库支持的字符集
show charset;// 查看数据库支持的数据集的校验规则
utf8_ general_ ci[不区分大小写]
utf8_ bin[区分大小写]
show collation;
create database test2 collate utf8_bin;// 显示创建语句
show create database 数据库名;
MySQL 建议我们关键字使用大写，但是不是必须的。
数据库名字的反引号``,是为了防止使用的数据库名刚好是关键字
/*!40100 default.... */ 这个不是注释，表示当前mysql版本大于4.01版本，就执行这句话// 将 mytest 数据库字符集改成 gbk
alter databases mytest charset=gbk// drop 用于删除整个数据库对象（例如表、视图、数据库、索引等）。执行 DROP TABLE 后，不仅表中的数据会被清空，而且表的结构及所有相关定义都会被完全移除。
drop table employees;// alter 修改已有的表结构
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);
在users表添加二条记录
insert into users values(1,'a','b','1982-01-04'),(2,'b','c','1984-01-04');
修改name，将其长度改成60
alter table users modify name carchar(60);
修改表名为employee
alter table users rename to employee;// truncate 清空表数据，保留表结构
truncate table employees;// comment 增加或修改表或列的注释
comment  on column employess.name is '员工姓名';// rename 重命名表或列
alter table employees rename to emp;

区别 DELETE 和 TRUNCATE

DELETE删除满足条件的记录，可以删除部分数据，但会记录日志并触发删除触发器。

TRUNCATE快速清空整张表，不触发DELETE触发器，重置自增计数，但不可回滚（在大多数引擎中）。如果表被其他表通过外键引用（在 InnoDB 中），通常无法直接 TRUNCATE 该表。

TRUNCATE 会快速删除指定表中的所有行，但不会删除表本身。执行后，表结构、列定义、索引、约束等依然存在，可以继续向该表中插入数据。

TRUNCATE 通常比 DELETE 更高效，因为它不会逐行删除数据，而是通过释放数据页来快速清空表。由于这种操作记录的日志较少，所以在清空大表数据时能显著提升性能。

-- 截断整表数据，注意影响行数是 0，所以实际上没有对数据真正操作
TRUNCATE for_truncate;
Query OK, 0 rows affected (0.10 sec)
-- 查看删除结果
SELECT * FROM for_truncate;
Empty set (0.00 sec)

DML 主要用于操作数据，包含对数据的增、删、改、查操作。

insert into employees(id, name, age) values
(2, '小米', 12), (3, '小白'， 13);delete from employees where id = 1;
DELETE FROM employees;update employees set age = 20 where id = 1;SELECT * FROM employees;-- 通过 MySQL 函数获取受到影响的数据行数
SELECT ROW_COUNT();//  排序 ORDER BY 可指定升序（ASC）或降序（DESC）。
-- NULL 视为比任何值都小，升序出现在最上面
-- NULL 视为比任何值都小，降序出现在最下面
-- 多字段排序，排序优先级随书写顺序
SELECT name, salary FROM employees ORDER BY salary DESC;// 限制结果数（LIMIT）
SELECT * FROM employees LIMIT 5;//DISTINCT —— 去重查询
select distinct deparment from employees; //只返回不同的部门名称。-- 数据更新
UPDATE exam_result SET math = 80 WHERE name = '孙悟空';
--将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分
UPDATE exam_result SET math = 60, chinese = 70 WHERE name = '曹孟德';--将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
SELECT name, math, chinese + math + english 总分 FROM exam_result
ORDER BY 总分 LIMIT 3;
UPDATE exam_result SET math = math + 30
ORDER BY chinese + math + english LIMIT 3;--孙悟空同学的考试成绩
delete from exam_result where name = '孙悟空';--用于删除表中一行或多行数据。你可以使用 WHERE 子句精确控制删除哪些记录。如果不加 WHERE，则会删除该表中的所有记录，但表的结构、列定义、索引、约束等仍然保留。
DELETE FROM for_delete;// AS 别名
SELECT name, salary, salary * 12 AS annual_salary FROM employees;// 子查询
SELECT name FROM employees WHERE age > (SELECT AVG(age) FROM employees);
SELECT name, department FROM employees 
WHERE department IN (SELECT department FROM employees WHERE salary > 5000);

语文成绩在 [80, 90] 分的同学及语文成绩

SELECT name, chinese FROM exam_result WHERE chinese >= 80 AND chinese <= 90;
SELECT name, chinese FROM exam_result WHERE chinese BETWEEN 80 AND 90;

数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩

SELECT name, math FROM exam_result
WHERE math = 58
OR math = 59
OR math = 98
OR math = 99;IN (option, ...)如果是 option 中的任意一个，返回 TRUE(1)
select name, math from exam_result where math in (58, 59, 98, 99)

姓孙的同学及孙某同学

% 匹配任意多个（包括 0 个）任意字符
select name from exam_result where name like '孙%';
+-----------+
| name |
+-----------+
| 孙悟空 |
| 孙权 |
+-----------+
select name from exam_result where name like '孙_';
+--------+
| name |
+--------+
| 孙权 |
+--------+

总分在 200 分以下的同学

// -- 别名不能用在 WHERE 条件中
select name, chinese + math + english 总分 from exam_result where hinese + math + english < 200;// 语文成绩 > 80 并且不姓孙的同学
SELECT name, chinese FROM exam_result
WHERE chinese > 80 AND name NOT LIKE '孙%';

=等于，NULL 不安全，例如 NULL = NULL 的结果是 NULL

<=>等于，NULL 安全，例如 NULL <=> NULL 的结果是 TRUE(1)

SELECT NULL = NULL, NULL = 1, NULL = 0;
+-------------+----------+----------+
| NULL = NULL | NULL = 1 | NULL = 0 |
+-------------+----------+----------+
| NULL        | NULL     | NULL     |
+-------------+----------+----------+SELECT NULL <=> NULL, NULL <=> 1, NULL <=> 0;
+---------------+------------+------------+
| NULL <=> NULL | NULL <=> 1 | NULL <=> 0 |
+---------------+------------+------------+
| 1             | 0          | 0          |
+---------------+------------+------------+

JOIN操作用于在多个表之间建立关联查询。

内连接（INNER JOIN）：返回两个表中满足连接条件的记录。例如：

SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

左连接（LEFT JOIN）：返回左表的所有记录，即使右表中没有匹配的记录也会显示，未匹配部分返回NULL。例如：

SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

右外连接（RIGHT JOIN / RIGHT OUTER JOIN）

返回右表的所有记录，即使左表中没有匹配，左表字段返回 NULL。

SELECT e.name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

交叉连接

返回两个表的笛卡尔积，每个左表记录与每个右表记录组合。

SELECT a.name, b.department_name
FROM employees a
CROSS JOIN departments b;

**自连接（Self Join）**同一表中不同记录之间的连接，用于关联同一张表中的数据。

SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
INNER JOIN employees e2 ON e1.manager_id = e2.id;

GROUP BY用于将查询结果按照某个字段进行分组，然后可以对每个组进行聚合操作。HAVING用于对分组后的结果进行筛选。

SELECT department, COUNT(*) AS num_employees 
FROM employees 
GROUP BY department 
HAVING COUNT(*) > 5;-- ORDER BY 子句中可以使用列别名
SELECT name, chinese + english + math 总分 FROM exam_result
ORDER BY 总分 DESC;
+-----------+--------+
| name | 总分 |
+-----------+--------+
| 猪悟能 | 276 |
| 孙悟空 | 242 |
| 曹孟德 | 233 |
| 唐三藏 | 221 |
| 孙权 | 221 |
| 刘玄德 | 185 |
| 宋公明 | 170 |
+-----------+--------+显示平均工资低于2000的部门和它的平均工资
select avg(sal) as myavg from EMP group by deptno having myavg < 2000

UNION 用于将多个查询结果合并成一个结果集，要求各个查询的列数、数据类型相同。

// 合并两个或多个查询结果，并自动去重重复行。
SELECT name FROM employees WHERE department = 'HR'
UNION
SELECT name FROM employees WHERE department = 'IT';// 合并多个查询结果，不去重，保留所有记录。
SELECT name FROM employees WHERE department = 'HR'
UNION ALL
SELECT name FROM employees WHERE department = 'IT';

DCL（数据控制语言，Data Control Language）

DCL 用于管理数据库用户的访问权限和安全性。

语句	作用
GRANT	赋予用户权限
REVOKE	撤销用户权限

grant select, insert on employees to user1;
revoke insert on employees from user1;

TCL（事务控制语言，Transaction Control Language）

TCL 用于管理事务，确保数据的一致性和完整性。事务是由一组SQL语句组成的逻辑工作单元，要么全部执行，要么全部回滚。主要语句包括：

语句	作用
COMMIT	提交事务
ROLLBACK	回滚事务
SAVEPOINT	设置事务保存点

BEGIN;
UPDATE employees SET salary = salary + 1000 WHERE id = 1;
SAVEPOINT sp1;
UPDATE employees SET salary = salary - 500 WHERE id = 2;
ROLLBACK TO sp1;
COMMIT;

数据类型

MySQL 数据类型主要分为三大类：

数值类型：包括整数、浮点数以及定点数（DECIMAL），用于存储数值数据。
字符串类型：包括字符型、文本型和二进制型数据，用于存储各种文本和二进制信息。
日期和时间类型：用于存储日期、时间、时间戳等信息（虽然本篇文章主要聚焦于数值与字符串类型，但在实际开发中日期时间类型同样至关重要）。

整数

整数类型主要用于存储不包含小数部分的数字。MySQL 提供了多种整数类型，主要包括：TINYINT、SMALLINT、MEDIUMINT、INT 和 BIGINT。每种类型的存储字节数、取值范围均有所不同。

类型	字节数	最小值（有符号/无符号）	最大值（有符号/无符号）
TINYINT	1	-128 / 0	127 / 255
SMALLINT	2	-32,768 / 0	32,767 / 65,535
MEDIUMINT	3	-8,388,608 / 0	8,388,607 / 16,777,215
INT	4	-2,147,483,648 / 0	2,147,483,647 / 4,294,967,295
BIGINT	8	-9,223,372,036,854,775,808 / 0	9,223,372,036,854,775,807 / 18,446,744,073,709,551,615

INYINT 存储空间：1 字节
取值范围有符号：-128 至 127 无符号：0 至 255
应用场景：常用于存储状态标志、布尔值（虽然 MySQL 没有专门的 BOOLEAN 类型，但 TINYINT(1) 常用于表示真/假）以及一些小范围数值。

SMALLINT 存储空间：2 字节
取值范围有符号：-32768 至 32767 无符号：0 至 65535
应用场景：适合存储范围较 TINYINT 大但仍较小的数值，例如人数、分数、短编号等。

INT 存储空间：4 字节
有符号：-2147483648 至 2147483647
无符号：0 至 4294967295
注意事项
可以使用 AUTO_INCREMENT 属性生成自增长的唯一标识。
对于存储 id 字段时，如果数据量较大，无符号 INT 可以使数值范围翻倍。

BIGINT 存储空间：8 字节
取值范围

有符号：-9223372036854775808 至 9223372036854775807
无符号：0 至 18446744073709551615
应用场景：适用于存储极大数值，如高并发系统中的唯一标识、计数器、统计数据等。
性能考量：由于 BIGINT 占用存储空间较大，可能对索引和查询性能有一定影响，只有在确实需要存储超大范围数值时才使用。

MySQL 的整数类型支持有符号（SIGNED）和无符号（UNSIGNED）两种形式。

有符号类型可以存储正负数，而无符号类型则仅存储正数，从而使最大值提高一倍。
在实际应用中，如果数据明确不可能为负（如库存数量、点击次数等），建议使用无符号类型，以便充分利用数值范围。

需要注意的是，默认是有符号的，转换为无符号后，一旦误输入负值可能导致数据截断或错误，因此在使用时要确保数据的合法性。

浮点数

浮点数类型用于存储带有小数部分的近似数值，MySQL 中主要有 FLOAT 和 DOUBLE 两种类型。它们采用 IEEE 754 标准进行近似存储，因此在存储和运算时可能会存在精度问题，适用于科学计算、统计等对精度要求不极端严格的场景。
FLOAT 存储空间：通常为 4 字节（单精度）

精度：大约 7 位十进制数字
应用场景：适用于需要存储大量数据但对精度要求不高的场景，例如科学计算中的测量数据、图形渲染时的坐标数据等。
注意事项：由于采用近似计算，进行累加或减法运算时可能出现舍入误差，因此在涉及精确计算（如财务数据）时不推荐使用 FLOAT。

create table t1(salary float(4, 2)); 
float(4,2)表示的范围是-99.99 ~ 99.99，MySQL在保存值时会进行四舍五入。
如果定义的是float(4,2) unsigned 这时，因为把它指定为无符号的数，范围是 0 ~ 99.99

DOUBLE 存储空间：通常为 8 字节（双精度）精度：大约 15 位十进制数字

DECIMAL 类型是一种固定精度的数值类型，专为需要精确表示小数（如金钱金额、计量数据）而设计。它在内部采用字符串形式存储，保证数值在计算时不会出现舍入误差。定义时一般采用 DECIMAL(M, D) 的形式，其中：

M 表示总位数（precision），
D 表示小数位数（scale），即小数点右边的位数。

例如，DECIMAL(10,2) 表示总共 10 位数字，其中小数位 2 位，可存储的数值范围为 -99999999.99 到 99999999.99。

字符串

字符串类型用于存储文本、字符以及二进制数据。MySQL 中的字符串类型非常丰富，既包括定长和变长的字符类型，也包括用于存储大段文本的 TEXT 类型、二进制数据的 BLOB 类型，以及特殊的枚举和集合类型。

CHAR 是定长字符类型(最大为255)，其长度固定，不论存储的字符串实际长度如何，都按照定义长度进行填充。

优点：由于长度固定，读取速度较快，适合频繁查询且数据长度统一的场景。
缺点：浪费空间，当存储的数据长度变化较大时，不建议使用 CHAR。

VARCHAR 是变长字符类型，根据实际存储的字符数动态分配存储空间。

存储：在存储时会额外占用 1 或 2 个字节来记录字符串的实际长度（当最大长度不超过 255 字节时，使用 1 个字节；否则使用 2 个字节）。
注意事项
- VARCHAR 的最大长度与字符集有关，例如 UTF-8 编码下，每个字符可能占用多字节存储。
- varchar长度可以指定为0到65535之间的值，但是有1 - 3 个字节用于记录数据大小，所以说有效字节数是65532。
- 当我们的表的编码是utf8时，varchar(n)的参数n最大值是65532/3=21844[因为utf中，一个字符占用3个字节]
- 如果编码是gbk，varchar(n)的参数n最大是65532/2=32766（因为gbk中，一个字符占用2字节）。

文本类型

文本类型主要用于存储大段文本数据。根据文本长度的不同，MySQL 提供了 TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT 四种类型。
TINYTEXT 最大 255 字节
适用场景：适用于存储短文本，如简短描述、注释或状态信息。

TEXT 最大 65,535 字节（约 64 KB）
适用场景：适合存储较长文本，如文章内容、评论、说明文字等。

MEDIUMTEXT 最大 16,777,215 字节（约 16 MB）
适用场景：适用于存储中等长度的文档、博客正文、大段描述等数据。

LONGTEXT 最大 4,294,967,295 字节（约 4 GB）
适用场景：适用于存储超大文本，如日志文件、超长文章、数据备份内容等。

在选择文本类型时，应考虑实际数据的大小和查询性能问题。由于 TEXT 类型的数据通常不会放在内存中进行处理，因此在频繁搜索或全文检索时，可能需要借助全文索引或外部搜索引擎来提高效率。

二进制字符串与字符型数据类似，但用于存储二进制数据，如图片、音频、加密数据等。主要包括 BINARY、VARBINARY 以及 BLOB 类型。

BINARY：定长二进制类型，与 CHAR 类似，其长度固定。

VARBINARY：变长二进制类型，与 VARCHAR 类似，其存储长度根据实际数据而定。

使用场景：适合存储需要精确控制长度的二进制数据，如固定格式的文件标识符、加密散列值等。

BLOB（Binary Large OBject）系列类型主要用于存储二进制大对象数据，类似于文本类型的 TINYTEXT、TEXT 等，MySQL 同样提供 TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB 四种类型，其存储容量与文本类型对应。

INYBLOB：最大 255 字节，适用于小型二进制数据。

BLOB：最大 65,535 字节，适合存储中小型二进制数据，如图片缩略图。

MEDIUMBLOB：最大 16 MB，适用于存储较大二进制数据，如高清图片或中等视频片段。

LONGBLOB：最大 4 GB，适用于存储超大二进制文件，如高清视频、大型文档扫描件等。

枚举与集合

枚举与集合是 MySQL 独有的特殊数据类型，用于存储预定义的一组字符串值，其内部通过数字进行存储，但在展示和查询时表现为字符串。

ENUM 类型

ENUM 类型允许一个字段从预定义的多个字符串值中选择一个存储。

存储机制：内部将每个可能值映射为一个整数，从而实现高效的存储和比较。

优点
- 能有效限制数据的取值范围，保证数据一致性。
- 存储空间小，查询速度快。
缺点
- 当预定义的枚举值需要修改时，ALTER 表操作较为麻烦。
- 对于新增值可能存在兼容性问题，且排序规则依据枚举定义顺序而非字母顺序。

SET 类型

定义：SET 类型允许一个字段从预定义的字符串集合中选择一个或多个值。
存储机制：采用位图方式存储，每个预定义值对应一个位，允许组合存储。
优点
- 适合存储多选的标志信息，如用户权限、兴趣标签等。
- 节省存储空间，并且查询效率较高。
缺点
- 一旦定义后，修改预定义集合较为复杂。
- 对于多值查询与比较的逻辑处理需要特别注意。

有一个调查表votes，需要调查人的喜好，比如（登山，游泳，篮球，武术）中去选择(可以多选)，（男，女）[单选]

create votes(hobby set('登山'，'游泳'，'篮球'，'武术'),gender enum('男', '女')
);find_in_set(sub,str_list) ：如果 sub 在 str_list 中，则返回下标；如果不在，返回0； str_list 用逗号分隔的字符串。
select * from votes where find_in_set('登山', hobby);
+----------+---------------+--------+
| username | hobby | gender |
+----------+---------------+--------+
| 雷锋 | 登山,武术 | 男 |
| Juse | 登山,武术 | 女 |
| LiLei | 登山 | 男 |
+----------+---------------+--------+

日期和时间类型

MySQL 的日期和时间类型包括 DATE、TIME、DATETIME、TIMESTAMP、YEAR 等。

DATE：用于存储日期，格式为 YYYY-MM-DD。
TIME：用于存储时间，格式为 HH:MM:SS。
DATETIME：同时存储日期和时间，格式为 YYYY-MM-DD HH:MM:SS。
TIMESTAMP：存储时间戳，受时区影响，可自动转换。
YEAR：专门用于存储年份信息。

聚合函数

//count() 可以使用 COUNT(*) 统计所有行，也可以对指定列（排除 NULL）进行统计。
-- 统计 employees 表中的总记录数
SELECT COUNT(*) AS total_records FROM employees;-- 统计 employees 表中 age 不为 NULL 的记录数
SELECT COUNT(age) AS age_count FROM employees;// SUM() 对数值型字段求和，忽略 NULL 值。
-- 计算 employees 表中所有员工的工资总和
SELECT SUM(salary) AS total_salary FROM employees;// AVG() 对数值型字段求平均值，忽略 NULL 值。
-- 计算 employees 表中员工的平均工资
SELECT AVG(salary) AS avg_salary FROM employees;// MIN() 适用于数值、日期、字符串等类型。
-- 查找 employees 表中最低工资
SELECT MIN(salary) AS min_salary FROM employees;// GROUP_CONCAT() 将同一组中非 NULL 的字符串值连接成一个字符串，默认用逗号分隔。
假设有一张员工表，我们希望按部门将所有员工姓名连接成一个字符串：
-- 按部门将员工姓名连接起来，显示为一个字符串
SELECT department, GROUP_CONCAT(name SEPARATOR ', ') AS employee_names
FROM employees
GROUP BY department;
该语句将返回每个部门的名称和该部门所有员工姓名用逗号分隔后的字符串。

日期函数

函数名称	描述
`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()`	当前日期时间

select current_date();
+----------------+
| current_date() |
+----------------+
| 2017-11-19     |
+----------------+select date_add('2017-10-28', interval 10 day);
+-----------------------------------------+
| date_add('2017-10-28', interval 10 day) |
+-----------------------------------------+
| 2017-11-07 |
+-----------------------------------------+select datediff('2017-10-10', '2016-9-1');
+------------------------------------+
| datediff('2017-10-10', '2016-9-1') |
+------------------------------------+
| 404                                |
+------------------------------------+显示所有留言信息，发布日期只显示日期，不用显示时间
select content,date(sendtime) from msg;查询在2分钟内发布的贴子
select * from msg where data_add(sendtime, interval 2 minute) > 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` 的 `position` 位置开始，取 `length` 个字符
`ltrim(string)`	去除前导空格
`rtrim(string)`	去除尾部空格
`trim(string)`	去除前导和尾部空格

--获取emp表的ename列的字符集
select charset(ename) from EMP;--要求显示student表中的信息，显示格式：“XXX的语文是XXX分，数学XXX分，英语XXX分”
select concat(nanm, '的语文是', chinese,'数学是', math,'分') from student;--求学生表中学生姓名占用的字节数
select length(name), name from student;--将EMP表中所有名字中有S的替换成'上海
select replace(ename, 'S', '上海') ,ename from EMP;--截取EMP表中ename字段的第二个到第三个字符
select substring(ename, 2, 2), ename from EMP;以首字母小写的方式显示所有员工的姓名
select concat(lcase(substring(ename, 1, 1)),substring(ename,2)) from EMP;

数学函数

函数名称	描述
`abs(number)`	绝对值函数
`bin(decimal_number)`	十进制转换二进制
`hex(decimalNumber)`	转换成十六进制
`conv(number, from_base, to_base)`	进制转换
`ceiling(number)`	向上取整
`floor(number)`	向下取整
`format(number, decimal_places)`	格式化，保留小数位数
`rand()`	返回随机浮点数，范围 [0.0, 1.0)
`mod(number, denominator)`	取模，求余

--绝对值
select abs(-100.2);--向上取整
select ceiling(23.04); //24--向下取整
select floor(23.7);  // 23--保留2位小数数位（小数四舍五入)
select format(12.3456, 2);--产生随机数
select rand();

其他函数

--user() 查询当前用户
select user(); --md5(str)对一个字符串进行md5摘要，摘要后得到一个32位字符串
select md5('admin');
+----------------------------------+
| md5('admin') |
+----------------------------------+
| 21232f297a57a5a743894a0e4a801fc3 |
+----------------------------------+

表的约束

空属性

每一列在定义时可以允许存放空值（NULL）或要求不为空（NOT NULL）。默认情况下大部分字段是允许为空的，但实际开发中，为了保证数据完整性与参与计算的正确性，通常会对重要字段设置 NOT NULL。

create table myClass(class_name varchar(20) not null,
);

为什么要设置 NOT NULL？（保证数据完整性、防止运算出错）

如果一个列既设置了 NOT NULL 又设置了默认值，那么插入时未指定值就会自动使用默认值；如果插入显式的 NULL 则会报错。

默认值

默认值约束用于指定当插入数据时未提供该字段的值时，数据库自动赋予的一个预设值。例如，性别字段可以默认值为 ‘男’ 或 ‘女’，年龄字段可以默认 0 或某个合理的初始值。

create table people(sex char(2) default '男'
);

列描述

列描述并不影响实际数据存储，它主要用于对字段进行说明、注释，方便开发人员、DBA或后续维护人员了解字段的业务含义。在 MySQL 中，通过 COMMENT 关键字为字段添加描述。

 create table t(name varchar(20) not null comment '姓名');

zerofill

Zerofill 主要用于数值类型字段，它指定当显示该字段值时，如果实际数字位数不足定义的显示宽度，则在数字前面自动补零。例如，定义 INT(5) UNSIGNED ZEROFILL，插入值 1 显示为 “00001”。
注意： Zerofill 仅仅影响显示效果，数据库内部存储的仍然是数值本身。

主键

主键用于唯一标识表中每一行记录，具有以下特性：

唯一性：每一条记录的主键值必须唯一。
非空性：主键列不能为 NULL。
单一性：一张表只能有一个主键，但主键可以是单列，也可以是复合主键（联合主键）。

create table t(age int primary key
);// 追加主键
alter table 表名 add primary key(字段列表)
// 删除主键
alter table 表面 drop primary key;

主键与唯一键的区别：主键不允许空值，唯一键允许空值（尽管空值的比较规则不同）；而且一张表只能有一个主键。

在创建表的时候，在所有字段之后，使用primary key(主键字段列表)来创建主键，如果有多个字段作为主键，可以使用复合主键。

create table t(id int,name varchar(20),primary key(id, name)
);

自增长

自增长属性（AUTO_INCREMENT）通常用于主键字段，在插入数据时如果不提供该字段值，数据库会自动为该字段生成一个唯一的递增数字。

只能作用于整数类型。
每个表只能有一个自增长字段，而且该字段必须是索引（通常是主键）。
自增长值不一定连续（删除记录、事务回滚或插入失败时可能导致跳号）。

create table t(id int primary key auto_increment
);

唯一键

唯一键约束确保某一列或列组合中的值在表内唯一，但与主键不同，唯一键允许存在 NULL 值（通常允许多个 NULL，但各数据库实现可能略有不同）。

一个表中可以定义多个唯一键。
唯一键主要用于业务数据要求不重复（如用户名、电子邮件等）。

create  table t(id char(10) unique 
);

外键

外键用于定义主表和从表之间的关系：外键约束主要定义在从表上，主表则必须是有主键约束或unique约束。当定义外键后，要求外键列数据必须在主表的主键列存在或为null。

外键约束有助于维护数据的参照完整性。
外键可以设置级联更新（ON UPDATE CASCADE）和级联删除（ON DELETE CASCADE），以便在主表数据更改时自动调整从表数据。

foreign key (字段名) references 主表(列)// 先创建主键表
create table class(id int primary key
);// 再创建从表
create table stu(id int primary key,class_id int,foregin key (class_id) references class(id)
);

理论上假如我们不创建外键约束，就正常建立学生表，以及班级表，该有的字段我们都有。此时，在实际使用的时候，可能会出现什么问题？

有没有可能插入的学生信息中有具体的班级，但是该班级却没有在班级表中？

解决方案就是通过外键完成的。建立外键的本质其实就是把相关性交给mysql去审核了，提前告诉mysql表之间的约束关系，那么当用户插入不符合业务逻辑的数据的时候，mysql不允许你插入。

有一个商店的数据，记录客户及购物情况，有以下三个表组成：

商品goods(商品编号goods_id，商品名goods_name, 单价unitprice, 商品类别category, 供应商provider)

客户customer(客户号customer_id,姓名name,住址address,邮箱email,性别sex，身份证card_id)

购买purchase(购买订单号order_id,客户号customer_id,商品号goods_id,购买数量nums)

要求：

每个表的主外键
客户的姓名不能为空值
邮箱不能重复
客户的性别(男，女)

// 创建数据库
create databases if not exits shops;use shops;create table goods(goods_id varchar(10) primary key auto_increment comment '商品编号',goods_name varchar(10) not null ,unitprice varchar(10) default 0
);create table customer(customer_id varchar(10) primary key auto_increment ,customet_name varchar(10) not null ,email varchar(10) unique key ,sex enum('男'，'女') not null
);create table purchase(order_id int primary key auto_increment,customer_id varchar(10),goods_id varchar(10),foregin key (customer_id) references customer(customer_id),foregin key (good_id) references goods(goods_id)
);

复合查询

查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J

select * from EMP where (sal>500 or job='MANAGER') and ename like 'J%';

按照部门号升序而雇员的工资降序排序

select * from EMP order by deptno, sal desc;

使用年薪进行降序排序

// 年薪计算公式为：月薪 (sal) × 12，加上佣金（comm）；如果佣金为 NULL，则用 0 代替（使用 ifnull(comm,0)）
select ename, sal*12+ifnull(comm,0) as '年薪' from EMP order by 年薪 desc;

显示工资最高的员工的名字和工作岗位

select ename, job from EMP where sal = (select max(sal) from EMP);

显示工资高于平均工资的员工信息

select ename, sal from EMP where sal>(select avg(sal) from EMP);

显示每个部门的平均工资和最高工资

//format(avg(sal), 2) 计算每个部门的平均薪资，并使用 FORMAT 函数格式化结果为保留2位小数的字符串。
select deptno, format(avg(sal), 2) , max(sal) from EMP group by deptno;

显示平均工资低于2000的部门号和它的平均工资

select deptno, avg(sal) as avg_sal from EMP group by deptno having avg_sal < 2000;

显示每种岗位的雇员总数，平均工资

select count(*) , format(agv(sal), 2) from EMP group by job;

多表查询

显示部门号为10的部门名，员工名和工资

select ename, sal,dname from EMP, DEPT where EMP.deptno=DEPT.deptno and DEPT.deptno =
10;

显示各个员工的姓名，工资，及工资级别

select ename, sal, grade from EMP, SALGRADE where EMP.sal between losal and hisal;

显示员工FORD的上级领导的编号和姓名（mgr是员工领导的编号–empno）

select empno,ename from emp where emp.empno=(select mgr from emp where ename='FORD');select leader.empno,leader.ename from emp leader, emp worker where leader.empno =
worker.mgr and worker.ename='FORD';

in关键字；查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的

select ename,job,sal,deptno from emp where job in (select distinct job from emp where
deptno=10) and deptno<>10;

all关键字；显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

select ename, sal, deptno from EMP where sal > all(select sal from EMP where
deptno=30);

any关键字；显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工）

select ename, sal, deptno from EMP where sal > any(select sal from EMP where
deptno=30);

多列子查询
查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人

mysql> select ename from EMP where (deptno, job)=(select deptno, job from EMP where
ename='SMITH') and ename <> 'SMITH';
+-------+
| ename |
+-------+
| ADAMS |
+-------+

from子句中使用子查询

显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资

//获取各个部门的平均工资，将其看作临时表
select ename, deptno, sal, format(asal,2) from EMP,
(select avg(sal) asal, deptno dt from EMP group by deptno) tmp
where EMP.sal > tmp.asal and EMP.deptno=tmp.dt;

查找每个部门工资最高的人的姓名、工资、部门、最高工资

select EMP.ename, EMP.sal, EMP.deptno, ms from EMP,
(select max(sal) ms, deptno from EMP group by deptno) tmp
where EMP.deptno=tmp.deptno and EMP.sal=tmp.ms;

合并查询
将工资大于2500或职位是MANAGER的人找出来

mysql> select ename, sal, job from EMP where sal>2500 union
-> select ename, sal, job from EMP where job='MANAGER';--去掉了重复记录
+-------+---------+-----------+
| ename | sal | job |
+-------+---------+-----------+
| JONES | 2975.00 | MANAGER |
| BLAKE | 2850.00 | MANAGER |
| SCOTT | 3000.00 | ANALYST |
| KING | 5000.00 | PRESIDENT |
| FORD | 3000.00 | ANALYST |
| CLARK | 2450.00 | MANAGER |
+-------+---------+-----------+mysql> select ename, sal, job from EMP where sal>2500 union all
-> select ename, sal, job from EMP where job='MANAGER';
+-------+---------+-----------+
| ename | sal | job |
+-------+---------+-----------+
| JONES | 2975.00 | MANAGER |
| BLAKE | 2850.00 | MANAGER |
| SCOTT | 3000.00 | ANALYST |
| KING | 5000.00 | PRESIDENT |
| FORD | 3000.00 | ANALYST |
| JONES | 2975.00 | MANAGER |
| BLAKE | 2850.00 | MANAGER |
| CLARK | 2450.00 | MANAGER |
+-------+---------+-----------+

内外链接

内连接实际上就是利用where子句对两种表形成的笛卡儿积进行筛选，我们前面学习的查询都是内连接，也是在开发过程中使用的最多的连接查询。

select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件；

显示SMITH的名字和部门名称

-- 用前面的写法
select ename, dname from EMP, DEPT where EMP.deptno=DEPT.deptno and ename='SMITH';-- 用标准的内连接写法
select ename, dname from EMP inner join DEPT on EMP.deptno=DEPT.deptno and
ename='SMITH';

如果联合查询，左侧的表完全显示我们就说是左外连接。

select 字段名 from 表名1 left join 表名2 on 连接条件

建两张表

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);

查询所有学生的成绩，如果这个学生没有成绩，也要将学生的个人信息显示出来

select * from stu left join exam on stu.id = exam.id;

索引

硬盘作为一种机械存储设备，其内部结构决定了数据的读写方式和效率。主要概念包括：

磁盘（硬盘盘片）
- 硬盘内部有一个或多个盘片，每个盘片上涂有磁性材料，用于存储数据。
柱面（Cylinder）
- 硬盘的多个盘片在同一位置（即同一半径）上的磁道构成一个柱面。每个柱面里的磁道在物理位置上大致对齐，因此当磁头在同一柱面上切换时，无需重新移动磁头，可以更快地访问不同盘片上的数据。
磁道（Track）
- 每个盘片上由中心向外绘制的一系列同心圆称为磁道。磁道是硬盘上最基本的数据存储“环”，数据通常按照磁道顺序存放。
扇区（Sector）
- 磁道进一步被划分为若干个扇区，每个扇区是硬盘最小的数据读写单位，通常为 512 字节或 4KB。操作系统对硬盘数据的读写都是以扇区为基本单位进行的。
磁头（Head）
- 磁头负责在盘片表面读写数据。每个盘片通常有一个磁头，当盘片旋转时，磁头根据磁道和扇区定位数据。由于磁头物理移动或切换的开销较大，因此高效的磁盘设计尽量减少频繁的磁头寻道。

这种结构决定了磁盘 IO 的基本特性：相对于内存，磁盘的随机访问速度较慢（主要受限于磁头的定位和扇区传输速度），因此数据库系统必须尽量减少对磁盘的随机读写，从而大大提高整体性能。

MySQL 作为一款应用软件，可以想象成一种特殊的文件系统。它有着更高的IO场景，所以，为了提高基本的IO效率， MySQL 进行IO的基本单位是 16KB

mysql> SHOW GLOBAL STATUS LIKE 'innodb_page_size';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Innodb_page_size | 16384 | -- 16*1024=16384
+------------------+-------+

也就是说，磁盘这个硬件设备的基本单位是 512 字节，而 MySQL InnoDB引擎使用 16KB 进行IO交互。即MySQL 和磁盘进行数据交互的基本单位是 16KB 。这个基本数据单元，在 MySQL 这里叫做page。

MySQL 中的数据文件，是以page为单位保存在磁盘当中的。MySQL 的 CURD 操作，都需要通过计算，找到对应的插入位置，或者找到对应要修改或者查询的数据。而只要涉及计算，就需要CPU参与，而为了便于CPU参与，一定要能够先将数据移动到内存当中。
所以在特定时间内，数据一定是磁盘中有，内存中也有。后续操作完内存数据之后，以特定的刷新策略，刷新到磁盘。而这时，就涉及到磁盘和内存的数据交互，也就是IO了。而此时IO的基本单位就是Page。
为了更好的进行上面的操作， MySQL 服务器在内存中运行的时候，在服务器内部，就申请了被称为 BufferPool 的的大内存空间，来进行各种缓存。其实就是很大的内存空间，来和磁盘数据进行IO交互。为何更高的效率，一定要尽可能的减少系统和磁盘IO的次数

MySQL 中要管理很多数据表文件，而要管理好这些文件，就需要先描述，在组织 ,我们目前可以简单理解成一个个独立文件是有一个或者多个Page构成的。不同的 Page ，在 MySQL 中，都是 16KB ，使用 prev 和 next 构成双向链表

因为有主键的问题， MySQL 会默认按照主键给我们的数据进行排序，从Page内数据记录可以看出，数据是有序且彼此关联的。

为什么数据库在插入数据时要对其进行排序呢？我们按正常顺序插入数据不是也挺好的吗？

插入数据时排序的目的，就是优化查询的效率。页内部存放数据的模块，实质上也是一个链表的结构，链表的特点也就是增删快，查询修改慢，所以优化查询的效率是必须的。正式因为有序，在查找的时候，从头到后都是有效查找，没有任何一个查找是浪费的，而且，如果运气好，是可以提前结束查找过程的。

上面页模式中，只有一个功能，就是在查询某条数据的时候直接将一整页的数据加载到内存中，以减少硬盘IO次数，从而提高性能。但是，我们也可以看到，现在的页模式内部，实际上是采用了链表的结构，前一条数据指向后一条数据，本质上还是通过数据的逐条比较来取出特定的数据。如果有1千万条数据，一定需要多个Page来保存1千万条数据，多个Page彼此使用双链表链接起来，而且每个Page内部的数据也是基于链表的。那么，查找特定一条记录，也一定是线性查找。这效率也太低了。

针对上面的单页Page，我们能否也引入目录呢？

MySQL 中每一页的大小只有 16KB ，单个Page大小固定，所以随着数据量不断增大， 16KB 不可能存下所有的数据，那么必定会有多个页来存储数据。在单表数据不断被插入的情况下， MySQL 会在容量不足的时候，自动开辟新的Page来保存新的数据，然后通过指针的方式，将所有的Page组织起来。

这样，我们就可以通过多个Page遍历，Page内部通过目录来快速定位数据。可是，貌似这样也有效率问题，在Page之间，也是需要 MySQL 遍历的，遍历意味着依旧需要进行大量的IO，将下一个Page加载到内存，进行线性检测。这样就显得我们之前的Page内部的目录，有点杯水车薪了。

那么如何解决呢？解决方案，其实就是我们之前的思路，给Page也带上目录。

存在一个目录页来管理页目录，目录页中的数据存放的就是指向的那一页中最小的数据。有数据，就可通过比较，找到该访问那个Page，进而通过指针，找到下一个Page。其实目录页的本质也是页，普通页中存的数据是用户数据，而目录页中存的数据是普通页的地址。

Page分为目录页和数据页。目录页只放各个下级Page的最小键值。

查找的时候，自定向下找，只需要加载部分目录页到内存，即可完成算法的整个查找过程，大大减少了IO次数

什么是索引？

索引是一种数据结构（常见的有 B-Tree、哈希、R-Tree 等），用于加快数据库查询速度。它类似于书的目录，可以使 MySQL 在海量数据中快速定位到所需数据，而无需全表扫描。

作用
- 提高查询效率
- 降低磁盘 I/O
- 帮助数据库优化器选择最佳查询计划

索引的基本原理

数据结构：MySQL 中最常用的是 B-Tree（及其变种 B+Tree）。B+Tree 将数据按顺序存储在叶子节点上，并通过树形结构快速定位数据。
叶子节点与非叶子节点：叶子节点存储实际数据或指向数据行的指针（在 InnoDB 中，主键索引的叶子节点存储行数据，二级索引则存储主键值）；非叶子节点只存储关键字及指向子节点的指针。
聚集索引与辅助索引
- 聚集索引：数据实际存储顺序与索引顺序一致（InnoDB 的主键索引就是聚集索引）。
- 辅助索引：又称非聚集索引，只存储索引列和指向聚集索引的指针。

为什么不用其他数据结构？
链表的特点是：
增删快：插入和删除操作只需调整指针，时间复杂度为 O(1)（前提是已定位到目标位置）。
查询慢：查找时需要从头到尾遍历，时间复杂度为 O(n)。
在数据库中，尤其是大数据量情况下，使用链表存储数据（或索引）会导致每次查询都需要线性遍历所有记录，显然无法满足高效定位和范围查询的要求。因此，链表结构并不适合用作存储引擎的索引。

普通二叉搜索树

退化问题：普通的二叉搜索树在极端情况下（例如插入已排序的数据）会退化为链表，查找效率变为 O(n)。

不稳定的平衡性：没有自平衡机制时，树的高度可能随数据分布严重不均而增高，导致查找效率大幅下降。

AVL 树：严格平衡，查询效率高，但在插入和删除时需要频繁旋转，更新成本较高。

红黑树：平衡性略逊于 AVL，但插入、删除的旋转次数较少，是许多语言和库中常用的平衡二叉搜索树（如 Java 的 TreeMap）。

二叉结构局限：虽然 AVL 和红黑树保证了树的高度为 O(log n)，但二叉树（每个节点最多两个子节点）的分支因子为 2，相对于多阶树（例如 B+树）来说，同样数量的记录需要更高的树。树的高度越高，查询时需要的层级查找次数越多，每一层可能都涉及一次内存访问或在极端情况下甚至一次磁盘 IO（如果对应页不在缓存中）。这就使得二叉树结构在面对大量数据时，与 B+树相比存在固有的层高劣势。

传统 B 树

存储结构：在 B 树中，非叶子节点既保存键值也保存数据记录。

问题：由于数据记录分布在内部节点和叶子节点中，B 树在磁盘 IO 上不够高效。当查询数据时，不仅要搜索到相应的叶子节点，还可能需要在内部节点中进行多次数据复制和判断。此外，由于数据分散在各个节点上，B 树的范围扫描性能和顺序扫描性能也不如 B+树。

B+树

统一叶子节点存储数据：所有数据记录都存放在叶子节点中，而非叶子节点仅用于索引（存储键值和指向下级节点的指针）。

多路分支（阶数较高）：B+树的每个节点（特别是非叶子节点）的分支因子可以达到数百（取决于页大小和键值大小）。这就使得整个树的高度非常低（通常 3～4 层就足够存储百万级甚至亿级数据），减少了层级查找的次数。

范围查询优化：由于叶子节点通过双向链表相连，B+树天然支持范围查询和顺序扫描，能将多个连续数据页连续地读入内存，利用预读机制降低磁盘 IO。

缓存友好：B+树中，非叶子节点体积较小，可以容易地加载到内存缓存（Buffer Pool）中，进一步加快查找速度。

InnoDB 主键索引（聚簇索引）

在 InnoDB 中，主键索引又称为聚簇索引（Clustered Index），其特点是：

数据记录本身存放在 B+Tree 的叶子节点上，并且记录的物理存储顺序与主键顺序一致。这样，在根据主键查询时，可以直接在索引中找到数据记录，而无需再进行额外的“回表”操作。
优点
- 高效的范围查询：由于数据有序存储，范围查询可以连续读取多个页（Page），降低磁盘 IO。
- 利用预读（prefetch）机制和 Buffer Pool，可以使得数据页大部分在内存中，查询速度更快。

InnoDB 的普通（辅助）索引

非聚簇索引的特点

普通索引（也称二级索引）在叶子节点上不直接存放完整数据，而是存储对应记录的主键值作为指针。
当查询条件仅涉及二级索引时，可以先定位到二级索引记录，再通过主键查找完整数据（回表）。

缺点

如果查询涉及的列不全在索引中（即非覆盖索引），就需要回表查找，会增加额外的 IO 开销。

操作

创建主键/唯一键索引

-- 在创建表的时候，直接在字段名后指定 primary key
create table user1(id int primary key, name varchar(30));
create table user2(id int unique , name varchar(30));-- 在创建表的最后，指定某列或某几列为主键索引
create table user2(id int, name varchar(30), primary key(id));create table user3(id int, name varchar(30));
-- 创建表以后再添加主键
alter table user3 add primary key(id);

主键索引的特点：

一个表中，最多有一个主键索引，当然可以使符合主键

主键索引的效率高（主键不可重复）

创建主键索引的列，它的值不能为null，且不能重复

主键索引的列基本上是in

唯一索引的特点：

一个表中，可以有多个唯一索引

查询效率高

如果在某一列建立唯一索引，必须保证这列不能有重复数据

如果一个唯一索引上指定not null，等价于主键索引

普通键索引

create table user8(id int primary key,
name varchar(20),
email varchar(30),
index(name) --在表的定义最后，指定某列为索引
);create table user9(id int primary key, name varchar(20), email varchar(30));
alter table user9 add index(name); --创建完表以后指定某列为普通索引create table user10(id int primary key, name varchar(20), email varchar(30));
-- 创建一个索引名为 idx_name 的索引
create index idx_name on user10(name);

普通索引的特点：
一个表中可以有多个普通索引，普通索引在实际开发中用的比较多
如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引

全文索引创建

当对文章字段或有大量文字的字段进行检索时，会使用到全文索引。MySQL提供全文索引机制，但是有要求，要求表的存储引擎必须是MyISAM，而且默认的全文索引支持英文，不支持中文。如果对中文进行全文检索，可以使用
sphinx的中文版(coreseek)。

CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
)engine=MyISAM;

查询索引

第一种方法：show keys from 表名

mysql> show keys from goods\G
*********** 1. row ***********
Table: goods <= 表名
Non_unique: 0 <= 0表示唯一索引
Key_name: PRIMARY <= 主键索引
Seq_in_index: 1
Column_name: goods_id <= 索引在哪列
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE <= 以二叉树形式的索引
Comment:
1 row in set (0.00 sec)

第二种方法: show index from 表名;

第三种方法（信息比较简略）： desc 表名；

删除索引

第一种方法-删除主键索引： alter table 表名 drop primary key;

第二种方法-其他索引的删除： alter table 表名 drop index 索引名；索引名就是show keys from 表名中的Key_name 字段

mysql> alter table user10 drop index idx_name;

第三种方法方法： drop index 索引名 on 表名

mysql> drop index name on user8;

索引创建原则

比较频繁作为查询条件的字段应该创建索引

唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件

更新非常频繁的字段不适合作创建索引

不会出现在where子句中的字段不该创建索引

事务

是指一组操作的逻辑单位，这组操作要么全部成功，要么全部失败。事务确保数据库状态从一个一致性状态转变为另一个一致性状态，即使在出现系统故障、并发冲突或其他异常情况时也能保证数据完整性。

ACID 是事务必须满足的四个特性，用来保证事务的可靠性和数据一致性。

Atomicity（原子性）

事务中的所有操作要么全部完成，要么全部不做。如果事务中某个操作失败，则整个事务都将回滚，数据库恢复到事务开始之前的状态。

Consistency（一致性）

事务开始前和事务结束后，数据库都处于一致状态。事务必须使数据库从一个一致性状态转变到另一个一致性状态。

数据库约束（如主键、外键、唯一性、检查约束等）在事务内外均应保持不变。

Isolation（隔离性）

并发事务之间相互隔离，互不干扰。一个事务的中间状态对其他事务不可见，从而避免脏读、不可重复读和幻读问题。

Durability（持久性）

一旦事务提交，对数据库所做的改变应该永久保存，即使系统崩溃也不会丢失。数据库通常通过日志（如 redo log）和定期刷新（flush）等机制来保证这一特性。

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务， MyISAM 不支持。

事务的提交方式

--查看事务的提交方式
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+START TRANSACTION;
INSERT INTO orders (order_id, amount) VALUES (1001, 500);
UPDATE inventory SET stock = stock - 1 WHERE product_id = 200;
-- 若无异常，则提交事务
COMMIT;
-- 若出现问题，则回滚事务
ROLLBACK;

自动提交模式：
MySQL 默认处于自动提交状态，每条独立的 SQL 语句（除非是显式事务控制语句）都会作为一个单独事务自动提交。

手动提交模式：
开启事务后（例如使用 START TRANSACTION 或 BEGIN），一组操作被视为同一个事务，直到执行 COMMIT 提交或 ROLLBACK 回滚。

--SET 来改变 MySQL 的自动提交模式:
mysql> SET AUTOCOMMIT=0; #SET AUTOCOMMIT=0 禁止自动提交
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+

mysql> start transaction; -- 开始一个事务begin也可以，推荐beginmysql> savepoint save1; -- 创建一个保存点save1mysql> insert into account values (1, '张三', 100); -- 插入一条记录mysql> savepoint save2; -- 创建一个保存点save2mysql> insert into account values (2, '李四', 10000); -- 在插入一条记录mysql> select * from account; -- 两条记录都在了
+----+--------+----------+
| id | name | blance |
+----+--------+----------+
| 1 | 张三 | 100.00 |
| 2 | 李四 | 10000.00 |
+----+--------+----------+mysql> rollback to save2; -- 回滚到保存点save2mysql> select * from account; -- 一条记录没有了
+----+--------+--------+
| id | name | blance |
+----+--------+--------+
| 1 | 张三 | 100.00 |
+----+--------+--------+mysql> rollback; -- 直接rollback，回滚在最开始mysql> select * from account; -- 所有刚刚的记录没有了

结论:
只要输入begin或者start transaction，事务便必须要通过commit提交，才会持久化，与是否设置setautocommit无关。
事务可以手动回滚，同时，当操作异常，MySQL会自动回滚
对于 InnoDB 每一条 SQL 语言都默认封装成事务，自动提交。（select有特殊情况，因为 MySQL 有MVCC ）
如果没有设置保存点，也可以回滚，只能回滚到事务的开始。直接使用 rollback(前提是事务还没有提交)
如果一个事务被提交了（commit），则不可以回退（rollback）
可以选择回退到哪个保存点 InnoDB 支持事务， MyISAM 不支持事务

事务隔离级别

读未提交（Read Uncommitted） 允许读取其他事务未提交的数据（脏读）。
问题：会出现脏读、不可重复读、幻读等问题。

**读已提交（Read Committed）**只允许读取已提交事务的数据。
问题：可以避免脏读，但仍可能出现不可重复读和幻读。
应用：Oracle 默认级别；适用于大部分 OLTP 应用，保证一定程度的一致性。

可重复读（Repeatable Read）
确保在同一事务内多次读取同一数据时结果一致，可以避免脏读和不可重复读。幻读问题：InnoDB 默认隔离级别为可重复读，通过 MVCC 和 Next-Key 锁机制解决了幻读问题。
适用场景：InnoDB 默认隔离级别，适用于大多数应用场景，在保证数据一致性的同时还能提供较高的并发性。

串行化（Serializable）
最严格的隔离级别，事务按顺序依次执行，完全避免脏读、不可重复读和幻读。
缺点：并发性能极低，容易出现锁竞争和事务等待。
适用场景：在对数据一致性要求极高且并发量不大的场景下使用。

脏读是指一个事务读取了另一个事务尚未提交的修改数据。由于另一个事务可能最终回滚，这样读取到的数据就属于“脏数据”，可能是错误或无效的。

事务 A：
开始一个事务，对某个账户余额做修改，但未提交：
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- 此时不执行 COMMIT，事务 A 尚未提交事务 B：
在事务 A 未提交时，读取账户余额：
START TRANSACTION;
SELECT balance FROM accounts WHERE account_id = 1;
-- 得到的余额中已经包含事务 A 的修改
COMMIT;
如果后续事务 A 因为某些原因执行了 ROLLBACK，那么事务 B 读取的数据就是不正确的——这就是脏读。

不可重复读指的是同一事务内，多次读取同一数据记录却得到不同的结果。通常是因为在第一次读取后，另一个事务对该记录进行了修改并提交，从而导致后续读取结果发生变化。

A开始事务并读取某账户余额：
START TRANSACTION;
SELECT balance FROM accounts WHERE account_id = 1;
-- 假设返回余额为 1000B开始另一个事务，对同一记录进行修改并提交：
START TRANSACTION;
UPDATE accounts SET balance = balance + 500 WHERE account_id = 1;
COMMIT;在事务 A 中再次读取同一记录：
SELECT balance FROM accounts WHERE account_id = 1;
-- 此时返回的余额为 1500，不同于第一次读取的结果 1000
COMMIT;

幻读是指一个事务在执行某个查询时，第一次查询返回一定数量的记录，而在同一事务中再次执行相同查询时，由于其他事务插入或删除了满足查询条件的记录，导致返回的记录数发生变化。这种“新出现”或“消失”的记录就称为“幻影”。

A开始事务，执行查询以获取金额大于 1000 的订单记录：
START TRANSACTION;
SELECT * FROM orders WHERE amount > 1000;
-- 假设返回 5 条记录B在事务 A 执行查询后，另一个事务插入一条新的订单记录，并提交：
START TRANSACTION;
INSERT INTO orders (order_id, amount, order_date) VALUES (2001, 1500, NOW());
COMMIT;事务 A 再次执行相同的查询：
SELECT * FROM orders WHERE amount > 1000;
-- 此时可能返回 6 条记录，因为事务 B 插入了一条满足条件的新记录
COMMIT;

操作隔离级别

ysql> SELECT @@global.tx_isolation; --查看全局隔级别
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ |
+-----------------------+mysql> SELECT @@session.tx_isolation; --查看会话(当前)全局隔级别
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ |
+------------------------+mysql> set session transaction isolation level serializable; -- 串行化mysql> SELECT @@global.tx_isolation; --全局隔离性还是RR
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ |
+-----------------------+mysql> SELECT @@session.tx_isolation; --会话隔离性成为串行化
+------------------------+
| @@session.tx_isolation |
+------------------------+
| SERIALIZABLE |
+------------------------+

一致性问题

在事务处理的 ACID 特性中，“一致性”指的是事务执行的结果必须使数据库从一个有效、符合预定义规则和约束的状态转换到另一个有效状态。换句话说，在事务开始之前，数据库处于一个一致性状态；事务执行过程中，如果所有操作成功完成，数据库将保持（或恢复为）一致性状态；如果事务执行失败，则必须回滚到事务开始前的一致性状态。

数据库一致性约束：
数据库的一致性状态通常由各种约束决定，例如主键唯一性、外键约束、检查约束、触发器、存储过程以及其他业务规则。只有当数据满足所有这些约束时，我们才能认为数据库处于一致性状态。

业务逻辑一致性：
除了数据库层面提供的约束外，一致性还反映了用户业务规则。例如，在银行转账业务中，账户总余额必须保持不变；在订单处理系统中，订单的状态变更必须符合业务流程。数据库技术提供了基础工具（如外键约束、触发器等）来协助保证一致性，但真正的业务一致性往往需要由应用程序逻辑来定义和维护。

MVCC

MVCC 是“多版本并发控制”（Multi-Version Concurrency Control）的缩写，是一种用于提高数据库并发性能的机制。它的核心思想是为每个事务提供数据库某个时刻的“快照”，使得读操作无需加锁，从而实现高并发读写而不会相互干扰。下面详细解释其工作原理和关键点：

MVCC 通过保存数据的多个版本，允许一个事务读取数据的旧版本，而其他事务同时对同一数据进行修改。这种方式保证了：
非锁定读：读操作可以读取到事务开始时的数据快照，而不必等待写锁释放，从而避免了读操作被阻塞。
一致性视图：在一个事务内，多次读取同一数据时，看到的是同一个版本的数据，即使其他事务已经提交了修改，这样可以避免“不可重复读”的问题（在合适隔离级别下）。

实现机制
在 InnoDB 存储引擎中，MVCC 的实现依赖于：
Undo 日志：每当一行数据被修改，InnoDB 会在 Undo 日志中记录修改前的数据版本。这样，当一个事务需要读取“旧版本”数据时，可以通过 Undo 日志还原出当时的数据状态。
隐藏系统列：每行记录都有几个隐藏的系统列，比如 DB_TRX_ID（记录最后一次修改该行的事务ID）、DB_ROLL_PTR（指向 Undo 日志记录的指针）以及 DB_ROW_ID。这些信息帮助数据库确定一行数据在某个时刻的版本，并决定某个事务是否可以看到这行数据。
事务版本号：每个事务在开始时会获取一个版本号（通常是一个递增的事务ID），在读取数据时，事务只会看到在自己开始之前已经提交的数据版本。

MVCC 的优势
提高并发性：因为读操作不需要加锁，所以多个事务可以同时读取数据，而不会相互阻塞。
减少锁竞争：写操作依然需要加锁，但由于读操作不阻塞写操作，系统整体的锁竞争减少，从而提高了吞吐量。
一致性快照：在事务的生命周期内，所有读操作都能看到一致的数据状态，保证了业务逻辑的一致性。

MVCC 的局限性
Undo 日志积累：长事务或频繁更新的数据可能导致 Undo 日志堆积，从而占用大量空间和内存，进而影响性能。
版本冲突问题：虽然 MVCC 能够提供非阻塞的读取，但在写操作密集时，仍可能出现冲突（例如两事务同时修改同一行数据时），这时必须通过锁机制协调。

实际应用中的事务隔离
MVCC 通常与事务隔离级别相结合使用，例如在 InnoDB 中默认的“可重复读”隔离级别（Repeatable Read）就利用了 MVCC 和 Next-Key 锁的组合，既保证了数据读取的一致性，又避免了幻读现象。

MVCC 通过保存数据的多个版本，为每个事务提供一致的快照，从而使得读操作可以无锁地进行，大大提高了并发处理能力。其实现依赖 Undo 日志、隐藏系统列和事务版本号等机制，既能保证数据一致性，又能减少锁竞争，适用于高并发环境。不过，在设计和使用 MVCC 时，也需要注意长事务可能带来的 Undo 日志堆积问题。

视图

视图（VIEW）本质上是基于一个或多个基本表的 SELECT 查询结果而形成的虚拟表。视图本身不存储数据，而是在每次查询时动态从基础表中取出数据。

优点：

简化复杂查询：将多表关联、筛选、计算等复杂 SQL 封装起来，使用时只需查询视图。
数据安全：可以只暴露部分字段，屏蔽敏感数据。
逻辑独立性：对基础表结构变动时，可以只修改视图定义，而不影响上层应用。

缺点：

性能开销：查询视图时，MySQL 每次都要动态解析视图对应的 SQL，嵌套或多层视图可能导致查询性能下降。
限制较多：复杂视图（例如涉及聚合、联合、多表关联的视图）往往不可更新，且有一些限制（例如不能创建索引、不能直接创建触发器）。

操作

//创建视图
create view 视图名 as select语句;create view v_ename_dname as select ename, dname from EMP, DEPT where
EMP， deptno=DEPT.deptno;select * from v_ename_dname;
+--------+------------+
| ename | dname |
+--------+------------+
| CLARK | ACCOUNTING |
| KING | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH | RESEARCH |
| JONES | RESEARCH |
| SCOTT | RESEARCH |
| ADAMS | RESEARCH |
| FORD | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| MARTIN | SALES |
| BLAKE | SALES |
| TURNER | SALES |
| JAMES | SALES |
+--------+------------+// 删除视图
drop view 视图名;

视图的更新（INSERT/UPDATE/DELETE）需满足以下条件：

视图基于单表（不能是 JOIN 或 UNION）。
未使用聚合函数（如 SUM, COUNT）、DISTINCT、GROUP BY、UNION 等复杂操作；
未包含子查询或计算列。
包含所有基表的必填字段（无默认值字段必须出现在视图中）。

视图的权限管理

创建视图需要权限： CREATE VIEW 权限。对基表的 SELECT 权限。

查询视图需要权限：用户只需视图的 SELECT 权限，无需基表权限。

WITH CHECK OPTION 的作用？

确保通过视图更新或插入的数据满足视图的 WHERE 条件。

CREATE VIEW view2 AS
SELECT * FROM employees WHERE salary > 5000
WITH CHECK OPTION;
-- 插入 salary=4000 的数据会失败

在 MySQL 中，视图的规则和限制与表基本类似。首先，视图必须具有唯一的名称，不能与已有的表或视图同名；其次，虽然可以创建任意数量的视图，但在将复杂查询封装为视图后可能会影响查询性能，因此应谨慎设计。视图本身不能建立索引，也不能关联触发器或设置默认值，不过通过视图可以提高数据安全性，因为它允许只暴露必要的字段和数据，只要用户具备相应的访问权限即可。此外，虽然在视图定义中可以使用 ORDER BY 子句来指定默认排序，但如果在查询该视图时的 SELECT 语句中再指定 ORDER BY，则视图中的排序将被覆盖。最后，视图不仅可以单独使用，还可以与表联合使用，从而实现更灵活的数据访问和查询操作。

表的设计

需求分析与概念设计

需求分析：明确业务需求、数据量、操作频率、扩展性、安全性等要求。
概念模型设计：通常使用 ER 图（实体关系图）来描述业务实体及其关系。

逻辑设计

将概念模型转换为关系模型，确定数据库中的表、字段和它们之间的关系。
进行范式设计，既保证数据冗余最小化，又考虑实际查询性能。

物理设计

根据逻辑模型确定每个字段的数据类型、长度、索引、约束等。
考虑存储引擎的选择（如 InnoDB、MyISAM 等）、分区方案、字符集与排序规则等问题。
优化存储：合理选择数据类型、索引、分表或分区，减少磁盘 IO，降低内存和 CPU 消耗。

第一范式（1NF）

表中每个字段都必须是不可拆分的原子值。例如，一个姓名字段不应同时存储姓和名。

-- 错误设计（地址包含省市区）
CREATE TABLE user (id INT PRIMARY KEY,address VARCHAR(200)  -- 如 "广东省深圳市南山区"
);-- 符合1NF的设计
CREATE TABLE user (id INT PRIMARY KEY,province VARCHAR(20),city VARCHAR(20),district VARCHAR(20)
);

第二范式（2NF）

在满足1NF的基础上，所有非主键字段必须完全依赖于主键（主要针对联合主键场景），避免部分依赖。

-- 错误设计（订单号+产品ID为主键，但产品名称仅依赖产品ID）
CREATE TABLE order_detail (order_id INT,product_id INT,product_name VARCHAR(50),  -- 部分依赖quantity INT,PRIMARY KEY (order_id, product_id)
);-- 符合2NF的设计（拆分表）
CREATE TABLE products (product_id INT PRIMARY KEY,product_name VARCHAR(50)
);
CREATE TABLE order_detail (order_id INT,product_id INT,quantity INT,PRIMARY KEY (order_id, product_id)
);

第三范式（3NF）

除了满足2NF外，要求非主键字段不依赖于其他非主键字段，即消除传递依赖。

-- 错误设计（学院名称依赖学院ID，学院ID依赖学生ID）
CREATE TABLE student (student_id INT PRIMARY KEY,student_name VARCHAR(50),college_id INT,college_name VARCHAR(50)  -- 传递依赖
);-- 符合3NF的设计（拆分学院表）
CREATE TABLE college (college_id INT PRIMARY KEY,college_name VARCHAR(50)
);
CREATE TABLE student (student_id INT PRIMARY KEY,student_name VARCHAR(50),college_id INT
);

分区分表

当单表数据量巨大时，可以使用分区技术，将数据根据某个规则（如日期、ID 范围）划分到不同的分区中。查询时如果能利用分区裁剪（Partition Pruning）就只需扫描部分分区数据。

示例：按月份对订单表进行范围分区：

CREATE TABLE orders (order_id INT AUTO_INCREMENT,order_date DATE NOT NULL,customer_id INT,amount DECIMAL(10,2),PRIMARY KEY (order_id, order_date)
)
PARTITION BY RANGE (TO_DAYS(order_date)) (PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),PARTITION p202303 VALUES LESS THAN (TO_DAYS('2023-04-01'))-- 依此类推
);

当数据量和访问压力非常大时，可能需要水平拆分（分表）或垂直拆分（分库）。

水平拆分：例如订单表按订单号或日期拆分到多个表；

垂直拆分：将不同功能模块（用户、订单、产品）拆分到不同数据库中。

存储管理

InnoDB

默认存储引擎，支持事务、行级锁、MVCC 和崩溃恢复。
数据以聚簇索引组织（表数据存储在主键叶子节点），适合随机读和范围查询。
优化点：合理设置 innodb_buffer_pool_size（建议设置为物理内存 60%~80%）、innodb_log_file_size、innodb_flush_log_at_trx_commit 等参数。

MyISAM

主要特点是读性能较高、支持全文索引，但不支持事务和行级锁，只支持表级锁。
对于写操作和高并发环境不适合，现代系统几乎统一选择 InnoDB。

其他存储引擎如 MEMORY、ARCHIVE 等，根据业务场景选择（例如 MEMORY 用于临时数据，但数据不持久）。

Redo Log

用于持久化事务的修改，保证提交后的数据不会丢失，参数如 innodb_flush_log_at_trx_commit 对性能有较大影响。

Undo Log

用于事务回滚和实现 MVCC（快照读），需要合理配置以支持高并发事务操作。

用户管理

MySQL 用户管理主要涉及对数据库用户的创建、权限分配、密码管理及账户维护。

用户创建与授权

CREATE USER 'username'@'host' IDENTIFIED BY 'password';// 使用 GRANT 语句为用户赋予权限。权限可分为全局、数据库、表、列和存储过程级别。
GRANT privilege_list ON scope TO 'username'@'host' [WITH GRANT OPTION];--为 testuser 在数据库 mydb 上授予 SELECT 和 INSERT 权限
GRANT SELECT, INSERT ON mydb.* TO 'testuser'@'localhost';--如果需要授予全局权限（即对所有数据库生效）：
GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'localhost' WITH GRANT OPTION;--使用 SHOW GRANTS 查看某个用户的权限。
SHOW GRANTS FOR 'testuser'@'localhost';

. : 代表本系统中的所有数据库的所有对象（表，视图，存储过程等）

库.* : 表示某个数据库中的所有数据对象(表，视图，存储过程等)

identified by可选。如果用户存在，赋予权限的同时修改密码,如果该用户不存在，就是创建用户

用户修改与密码管理

ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';
--将 testuser 的密码修改为 NewP@ssw0rd
ALTER USER 'testuser'@'localhost' IDENTIFIED BY 'NewP@ssw0rd';--自己改自己密码
set password=password('新的密码');--root用户改指定用户的密码
set password for '用户名'@'主机名'=password('新的密码')；PASSWORD EXPIRE INTERVAL 90 DAY：设置密码有效期
ACCOUNT LOCK / ACCOUNT UNLOCK：锁定或解锁账户
--创建用户时设置密码 90 天后过期CREATE USER 'secureuser'@'localhost' 
IDENTIFIED BY 'StrongP@ss123' PASSWORD EXPIRE INTERVAL 90 DAY;

撤销权限与删除用户

REVOKE privilege_list ON scope FROM 'username'@'host';--撤销 testuser 在 mydb 上的 INSERT 权限
REVOKE INSERT ON mydb.* FROM 'testuser'@'localhost';DROP USER 'username'@'host';
--删除用户 testuser
DROP USER 'testuser'@'localhost';

给用户授权

grant 权限列表 on 库.对象名 to '用户名'@'登陆位置' [identified by '密码']--创建角色
CREATE ROLE 'read_role', 'write_role';--授予角色权限
GRANT SELECT ON mydb.* TO 'read_role';
GRANT INSERT, UPDATE ON mydb.* TO 'write_role';--将角色授予用户
GRANT 'read_role' TO 'dev_user'@'localhost';
-- 激活角色
SET DEFAULT ROLE 'read_role' TO 'dev_user'@'localhost';

WITH GRANT OPTION` 的作用是什么？
允许用户将自己拥有的权限授予其他用户。

GRANT SELECT ON mydb.* TO 'user1'@'%' WITH GRANT OPTION;

MySQL 权限信息存储在系统库 mysql 的表中：

mysql.user：用户账户及全局权限。
mysql.db：数据库级权限。
mysql.tables_priv：表级权限。
mysql.columns_priv：列级权限。
mysql.roles_edges（8.0+）：角色与用户的映射。

牛客练习

对于表actor批量插入如下数据

CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))-- 方法一：
insert into actor (actor_id, first_name, last_name, last_update)
values(1, 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33'),(2, 'NICK', 'WAHLBERG','2006-02-15 12:34:33');-- 方法二：
insert into actor values (1,'PENELOPE','GUINESS','2006-02-15 12:34:33'), (2,'NICK','WAHLBERG','2006-02-1512:34:33');

有一个薪水表，salaries简况如下:

emp_no	salary	from_date	to_date
10001	72527	2002-06-22	9999-01-01
10002	72527	2001-08-02	9999-01-01
10003	43311	2001-12-01	9999-01-01

请你找出所有员工具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示，以上例子输出如下:

select distinct salary from salaries where to_date='9999-01-01'order by salary desc;

有一个员工employees表简况如下:

emp_no	birth_date	first_name	last_name	gender	hire_date
10001	1953-09-02	Georgi	Facello	M	1986-06-26
10002	1964-06-02	Bezalel	Simmel	F	1985-11-21
10003	1959-12-03	Parto	Bamford	M	1986-08-28
10004	1954-05-01	Christian	Koblick	M	1986-12-01
10005	1955-01-21	Kyoichi	Maliniak	M	1989-09-

请你查找employees里最晚入职员工的所有信息

select * from employees order by hire_date desc limit 1;

请你查找employees里入职员工时间升序排名的情况下倒数第三的员工所有信息

select * from employees order by hire_date desc limit 1 offset 2;

有一个薪水表，salaries简况如下:

emp_no	salary	from_date	to_date
10001	60117	1986-06-26	1987-06-26
10001	62102	1987-06-26	1988-06-25
10001	66074	1988-06-25	1989-06-25
10001	66596	1989-06-25	1990-06-25
10001	66961	1990-06-25	1991-06-25
10001	71046	1991-06-25	1992-06-24

请你查找薪水记录超过15条的员工号emp_no以及其对应的记录次数t

select emp_no, count(emp_no) as t from salaries group by emp_no having count(emp_no) > 15;

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| email       | varchar |
+-------------+---------+
id is the primary key (column with unique values) for this table.
Each row of this table contains an email. The emails will not contain uppercase letters.

Write a solution to report all the duplicate emails. Note that it’s guaranteed that the email field is not NULL.

select Email from Person
where Id in (select Id from Person group by Email having count(Email)>1);

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| name        | varchar |
| continent   | varchar |
| area        | int     |
| population  | int     |
| gdp         | bigint  |
+-------------+---------+
name is the primary key (column with unique values) for this table.
Each row of this table gives information about the name of a country, the continent to which it belongs, its area, the population, and its GDP value.

A country is big if:

it has an area of at least three million (i.e., 3000000 km2), or
it has a population of at least twenty-five million (i.e., 25000000).

Write a solution to find the name, population, and area of the big countries.

select name,population,area from World
where area > 3000000
union
select name,population,area from World
where population > 25000000;

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+
id is the primary key (column with unique values) for this table.
Each row of this table contains information about the salary of an employee.

Write a solution to find the nth highest salary from the Employee table. If there is no nth highest salary, return null.

create function  getNthHightesSalyary(N INT) returns INT
BEGINset N = N - 1;RETURN(select salary from employee order by salary desc limit 1 offset N);
END

查找字符串’10, A, B’ 中逗号’,’ 出现的次数cnt

select length('10,A,B') - length(replace('10,A,B',',','')) as cnt

length(‘10,A,B’) 返回字符串 “10,A,B” 的总长度，为 6 个字符（字符依次为：1、0、,、A、,、B）。
replace(‘10,A,B’, ‘,’, ‘’) 将字符串中的所有逗号替换为空字符串，结果为 “10AB”。
length(replace(‘10,A,B’, ‘,’, ‘’)) 返回替换后的字符串 “10AB” 的长度，为 4 个字符。
最后，用原字符串的长度减去替换后字符串的长度：6 - 4 = 2，这正好是逗号的个数。

select concat('select count(*) from', s.TABLE_NAME) as cnts from TABLES as s;

假设在 TABLES 中有如下数据：
TABLE_NAME
employees
departments

那么这条 SQL 语句的执行结果大致为：
cnts
select count() from employees
select count() from departments

获取非manager的员工emp_no

select emp_no from employees where emp_no not in (select emp_no from dept_manager);

获取所有员工当前的manger，如果当前的manger是自己的话结果不显示，当前表示to_date=‘9999-01-01’

select
distinct de.emp_no as emp_no, dm.emp_no as manager_no
from
dept_emp as de , dept_manager as dm
where de.emp_no !=dm.emp_no
and de.dept_no = dm.dept_no
and de.to_date='9999-01-01'
and dm.to_date='9999-01-01';

针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，first_name为first_name_v，last_name修改为last_name_v：

create view if not exists actor_name_view as select first_name as first_name_v , last_name as last_name_v  from actor;