MYSQL

通用语法

DDL

一.数据库操作

//查询所有数据库
show databases;
//查询当前数据库
SELECT DATABASE();
//创建数据库
CREATE DATABAS [IF NOT EXISTS]数据库名;
//删除数据库
DROP DATABASE [IF EXISTS] 数据库名;
//使用数据库
USE 数据库名;

二.数据类型

三.表操作

//查询当前数据库的所有表
SHOW TABLES;
//查询表结构
DESC 表名;
//查询指定表的建表语句
SHOW CREATE TABLE 表名;
//创建表
CREATE TABLE 表名(
字段1 类型 [可加主键，自增什么的],
字段2 类型
)[COMMENT 表注释]; 
//修改表-添加字段
ALTER TABLE 表名 ADD 字段名 类型(长度)[comment 注释] [约束];
//1.修改字段类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
//2.修改字段名和数据类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度)[comment 注释] [约束];
//删除字段
ALTER TABLE 表名 DROP 字段名;
//修改表名
ALTER TABLE 表名 RENAME TO 新表名;
／／.删除表
DROP TABLE [IF　ＥＸＩＳＴＳ]表名；
／／２.删除并重新创建该表
TRUNCATE TABLE 表名;

DML

一.插入数据

//给指定的字段添加数据
INSERT INTO 表名(字段名1，字段名2，...)VALUES(值1，值2，...);
//给全部字段添加数据
INSERT INTO 表名 VALUES(值1，值2，...); 
//批量添加数据
INSERT INTO 表名 （字段名1，字段名2，...）VALUES (值1，值2，...)，（值1，值2，...）；
INSERT INTO 表名 VALUES(值1，值2，...)，(值1，值2，...);

二.修改数据

UPDATE 表名 SET 字段名1=值1，字段名2=值2，...[WHERE];

三.删除数据

DELETE FROM 表名 [WHERE];

DQL

一.基本查询

//查询多个字段
SELECT 字段1,字段2,字段3,...FROM 表名;
//设置别名
SELECT 字段1[AS 别名],字段2[AS 别名],字段3,...FROM 表名;
//查询全部字段
SELECT * FROM 表名;
//去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;

二.条件查询

SELECT  字段列表 from 表名 where 条件列表;

2.1比较运算符

等于 (=): 用于检查两个值是否相等。

SELECT * FROM table_name WHERE column_name = 'value';

不等于 (<> 或 !=): 用于检查两个值是否不相等。

SELECT * FROM table_name WHERE column_name <> 'value';

大于 (>): 用于检查一个值是否大于另一个值。

SELECT * FROM table_name WHERE column_name > 10;

小于 (<): 用于检查一个值是否小于另一个值。

SELECT * FROM table_name WHERE column_name < 10;

大于或等于 (>=): 用于检查一个值是否大于或等于另一个值。
```
SELECT * FROM table_name WHERE column_name >= 10;

    
```
小于或等于 (<=): 用于检查一个值是否小于或等于另一个值。
```
SELECT * FROM table_name WHERE column_name <= 10;

    
```

LIKE: 用于模式匹配（通配符 % 和 _）。

SELECT * FROM table_name WHERE column_name LIKE 'a%'; //-- a开头的所有字符串

BETWEEN: 用于检查值是否在指定的范围内（包括边界）。

SELECT * FROM table_name WHERE column_name BETWEEN 1 AND 10;

IN: 用于检查值是否在指定的集合中。

SELECT * FROM table_name WHERE column_name IN (1, 2, 3);

2.2逻辑运算符

AND: 用于组合多个条件，所有条件必须为真。

SELECT * FROM table_name WHERE condition1 AND condition2;

OR: 用于组合多个条件，任意一个条件为真即可。

SELECT * FROM table_name WHERE condition1 OR condition2;

NOT: 用于取反条件。

SELECT * FROM table_name WHERE NOT condition;

XOR: 用于表示异或逻辑（在MySQL中没有直接的XOR运算符，但可以通过组合AND、OR和NOT来实现）。
```
SELECT * FROM table_name WHERE (condition1 AND NOT condition2) OR (NOT condition1 AND condition2);

    
```

2.3示例查询

假设有一个名为employees的表，包含以下列：id, name, age, department。以下是一些使用这些运算符的示例查询：

查找年龄大于30的员工：

SELECT * FROM employees WHERE age > 30;

查找名字以"J"开头的员工：

SELECT * FROM employees WHERE name LIKE 'J%';

查找属于'Sales'或'Marketing'部门的员工：

SELECT * FROM employees WHERE department = 'Sales' OR department = 'Marketing';

查找年龄在20到30岁之间的员工：

SELECT * FROM employees WHERE age BETWEEN 20 AND 30;

查找年龄大于25且不属于'HR'部门的员工：

SELECT * FROM employees WHERE age > 25 AND department <> 'HR';

三.聚合函数

SELECT 聚合函数(字段列表) FROM 表名;

3.1AVG(): 计算员工的平均薪资。

SELECT AVG(salary) AS average_salary FROM employees;

3.2SUM(): 计算所有员工的总薪资。

SELECT SUM(salary) AS total_salary FROM employees;

3.3MAX(): 找到最高的薪资。

SELECT MAX(salary) AS highest_salary FROM employees;

3.4MIN(): 找到最低的薪资。

SELECT MIN(salary) AS lowest_salary FROM employees;

3.5COUNT(): 计算公司的员工数量。

SELECT COUNT(*) AS employee_count FROM employees;

四.分组查询

SELECT  字段列表 from 表名 [where 条件列表] GROUP BY 分组字段名 [HAVING 分组后的过滤条件];

五.排序查询

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2; 
//ASC:升序
//DESC:降序

六.分页查询

SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;

七. DQL执行顺序与综合练习

DCL

7.1基本语法

//查询用户
USE mysql;
SELECT * FROM user;
//创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
//修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native _password BY '新密码';
//删除用户
DROP USER  '用户名'@'主机名';
//查询权限
SHOW GRANT FOR '用户名'@'主机名';
//授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
//撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

7.2权限控制

函数

一.字符串函数

//CONCAT(str1, str2, ...)：连接两个或多个字符串。
SELECT CONCAT('Hello', ' ', 'World');
-- 结果： 'Hello World'

//LENGTH(str)：返回字符串的长度（字节数）。
SELECT LENGTH('Hello World');
-- 结果： 11

//SUBSTRING(str, pos, len)：从字符串中提取子字符串。
SELECT SUBSTRING('Hello World', 1, 5);
-- 结果： 'Hello'

//REPLACE(str, from_str, to_str)：替换字符串中的某个子串。
SELECT REPLACE('Hello World', 'World', 'MySQL');
-- 结果： 'Hello MySQL'

//UPPER(str)：将字符串转换为大写。
SELECT UPPER('Hello World');
-- 结果： 'HELLO WORLD'

//LOWER(str)：将字符串转换为小写。
SELECT LOWER('Hello World');
-- 结果： 'hello world'

//LTRIM(str)：去除字符串左侧的空格。
SELECT LTRIM('  Hello World  ');
-- 结果： 'Hello World  '

//RTRIM(str)：去除字符串右侧的空格。
SELECT RTRIM('  Hello World  ');
-- 结果： '  Hello World'

//TRIM(str)：去除字符串两侧的空格。
SELECT TRIM('  Hello World  ');
-- 结果： 'Hello World'

//INSTR(str, substr)：返回子字符串在字符串中首次出现的位置。
SELECT INSTR('Hello World', 'o');
-- 结果： 4

//LOCATE(substr, str[, start])：返回子字符串在字符串中首次出现的位置。
SELECT LOCATE('o', 'Hello World');
-- 结果： 4

二.数值函数

//ABS(x)：返回x的绝对值。
SELECT ABS(-5); -- 结果为5

//RAND()：返回一个介于0和1之间的随机浮点数。
SELECT RAND(); -- 结果为一个随机数

//MOD(x, y)：返回x除以y的余数。
SELECT MOD(10, 3); -- 结果为1

//POWER(x, y)：返回x的y次幂。
SELECT POWER(2, 3); -- 结果为8

//SQRT(x)：返回x的平方根。
SELECT SQRT(9); -- 结果为3

//ROUND(x, d)：将x四舍五入到小数点后d位。如果省略d，则默认为0。
SELECT ROUND(3.14159, 2); -- 结果为3.14

//CEILING(x)：返回大于或等于x的最小整数。
SELECT CEILING(3.14); -- 结果为4

//FLOOR(x)：返回小于或等于x的最大整数。
SELECT FLOOR(3.99); -- 结果为3

三.日期函数

//NOW()：返回当前日期和时间。
SELECT NOW(); -- 结果为当前日期和时间

//CURDATE()：返回当前日期。
SELECT CURDATE(); -- 结果为当前日期

//CURTIME()：返回当前时间。
SELECT CURTIME(); -- 结果为当前时间

//DATE(str)：将字符串转换为日期格式。
SELECT DATE('2022-01-01'); -- 结果为'2022-01-01'

//TIME(str)：将字符串转换为时间格式。
SELECT TIME('12:34:56'); -- 结果为'12:34:56'

//YEAR(date)：返回日期的年份部分。
SELECT YEAR(CURDATE()); -- 结果为当前年份

//MONTH(date)：返回日期的月份部分。
SELECT MONTH(CURDATE()); -- 结果为当前月份

//DAY(date)：返回日期的天数部分。
SELECT DAY(CURDATE()); -- 结果为当前天数

//HOUR(time)：返回时间的小时部分。
SELECT HOUR(CURTIME()); -- 结果为当前小时数

//MINUTE(time)：返回时间的分钟部分。
SELECT MINUTE(CURTIME()); -- 结果为当前分钟数

//SECOND(time)：返回时间的秒数部分。
SELECT SECOND(CURTIME()); -- 结果为当前秒数

四.流程控制函数

//IF(condition, value_if_true, value_if_false)：根据条件返回不同的值。
SELECT IF(1 > 0, 'True', 'False'); -- 结果为'True'

//CASE expression WHEN value THEN result [... WHEN ...] [ELSE result] END：根据表达式的值返回不同的结果。
SELECT CASE 
        WHEN 1 > 0 THEN 'True'
        WHEN 1 < 0 THEN 'False'
        ELSE 'Unknown'
        END; -- 结果为'True'

//IFNULL(expression1, expression2)：如果expression1不为NULL，则返回expression1，否则返回expression2。
SELECT IFNULL(NULL, 'Default Value'); -- 结果为'Default Value'

约束

一.概述

二.外键

三.语法

//创建外键法1
-- 创建 orders 表，并将 customer_id 设置为外键，引用 customers 表的 id 列
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    product_name VARCHAR(50),
    quantity INT,
    customer_id INT,
    [CONSTRAINT][外键名]FOREIGN KEY (外键字段名) REFERENCES customers(主表列名)
);

//法2
 ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (外键字段名) REFERENCES customers(主表列名);

//删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名;

多表查询

一.多表关系概述

二.多表查询

//出现笛卡尔积,在多表查询中需要消除无效的笛卡尔积
SELECT * FROM 表1,表2 ;  
//消除了笛卡尔积
SELECT * FROM 表1,表2 where 表1.表2的字段名 = 表2.表1的字段名;

三.连接查询

3.1内连接

//隐式内连接
SELECT 字段列表 FROM 表1,表2 WHERE 条件;
//显示内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;

3.2外连接

//左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 on 条件...;
//右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 on 条件...;

3.3自连接

SELECT 字段列表 FROM 表A [AS]别名A  JOIN 表A [as]别名B  ON 条件...;

四.联合查询

SELECT 字段列表 FROM 表1...
UNION [ALL]//无all去重.
SELECT 字段列表 FROM 表2..;

五.子查询

SELECT * FROM  表1 WHERE 字段=(SELECT 字段 FROM表2)

5.1标量子查询

5.2列子查询

5.3行子查询

5.4表子查询

事务

一.事务简介

二.事务的特点

事务的几个特性(ACID) -重点

原子性(Atomicity)

事务的整个过程如原子操作一样，最终要么全部成功，或者全部失败，这个原子性是从最终结果来看的，从最终结果来看这个过程是不可分割的。

一致性(Consistency)

一个事务必须使数据库从一个一致性状态变换到另一个一致性状态。

首先回顾一下一致性的定义。所谓一致性，指的是数据处于一种有意义的状态，这种状态是语义上的而不是语法上的。最常见的例子是转帐。例如从帐户A转一笔钱到帐户B上，如果帐户A上的钱减少了，而帐户B上的钱却没有增加，那么我们认为此时数据处于不一致的状态。

从这段话的理解来看，所谓一致性，即，从实际的业务逻辑上来说，最终结果是对的、是跟程序员的所期望的结果完全符合的

隔离性(Isolation)

一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

这里先提一下事务的隔离级别：
读未提交：read uncommitted
读已提交：read committed
可重复读：repeatable read
串行化：serializable

持久性(Durability)

一个事务一旦提交，他对数据库中数据的改变就应该是永久性的。当事务提交之后，数据会持久化到硬盘，修改是永久性的。

三.事务的操作

mysql中事务默认是隐式事务，执行insert、update、delete操作的时候，数据库自动开启事务、提交或回滚事务。

是否开启隐式事务是由变量autocommit控制的。

所以事务分为隐式事务和显式事务。

3.1隐式事务

事务自动开启、提交或回滚，比如insert、update、delete语句，事务的开启、提交或回滚由mysql内部自动控制的。

查看变量autocommit是否开启了自动提交

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit   | ON   |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

autocommit为ON表示开启了自动提交。

3.2显性任务

方式1

事务需要手动开启、提交或回滚，由开发者自己控制。

//设置不自动提交事务
set autocommit=0;
//执行事务操作
commit|rollback;

示例1：提交事务操作，如下：

mysql> create table test1 (a int);
Query OK, 0 rows affected (0.01 sec)

mysql> select * from test1;
Empty set (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;
+------+
| a   |
+------+
|   1 |
+------+
1 row in set (0.00 sec)

示例2：回滚事务操作，如下：

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 values(2);
Query OK, 1 row affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;
+------+
| a   |
+------+
|   1 |
+------+
1 row in set (0.00 sec)

方式2

start transaction;//开启事务
//执行事务操作
commit|rollback;

示例1：提交事务操作，如下：

mysql> select * from test1;
+------+
| a   |
+------+
|   1 |
+------+
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 values (2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test1 values (3);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;
+------+
| a   |
+------+
|   1 |
|   2 |
|   3 |
+------+
3 rows in set (0.00 sec)

示例2：回滚事务操作，如下：

mysql> select * from test1;
+------+
| a   |
+------+
|   1 |
|   2 |
|   3 |
+------+
3 rows in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from test1;
Query OK, 3 rows affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;
+------+
| a   |
+------+
|   1 |
|   2 |
|   3 |
+------+
3 rows in set (0.00 sec)

3.3savepoint关键字

在事务中我们执行了一大批操作，可能我们只想回滚部分数据，怎么做呢？

我们可以将一大批操作分为几个部分，然后指定回滚某个部分。可以使用savepoin来实现，效果如下：

先清除test1表数据：

mysql> delete from test1;
Query OK, 3 rows affected (0.00 sec)

mysql> select * from test1;
Empty set (0.00 sec)

演示savepoint效果，认真看：

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 values (1);
Query OK, 1 row affected (0.00 sec)

mysql> savepoint part1;//设置一个保存点
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 values (2);
Query OK, 1 row affected (0.00 sec)

mysql> rollback to part1;//将savepint = part1的语句到当前语句之间所有的操作回滚
Query OK, 0 rows affected (0.00 sec)

mysql> commit;//提交事务
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;
+------+
| a   |
+------+
|   1 |
+------+
1 row in set (0.00 sec)

从上面可以看出，执行了2次插入操作，最后只插入了1条数据。

savepoint需要结合rollback to sp1一起使用，可以将保存点sp1到rollback to之间的操作回滚掉。

3.4只读事务

表示在事务中执行的是一些只读操作，如查询，但是不会做insert、update、delete操作，数据库内部对只读事务可能会有一些性能上的优化。

start transaction read only;

只读事务中执行delete会报错。

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction read only;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;
+------+
| a   |
+------+
|   1 |
|   1 |
+------+
2 rows in set (0.00 sec)

mysql> delete from test1;
ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction.
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;
+------+
| a   |
+------+
|   1 |
|   1 |
+------+
2 rows in set (0.00 sec)

3.5事务中的一些问题（重点）

这些问题主要是基于数据在多个事务中的可见性来说的。也是并发事务产生的问题。

更新丢失

丢失更新就是两个不同的事务（或者Java程序线程）在某一时刻对同一数据进行读取后，先后进行修改。导致第一次操作数据丢失。

「

第一类丢失更新 ：A，B 事务同时操作同一数据，A先对改数据进行了更改，B再次更改时失败然后回滚，把A更新的数据也回滚了。（事务撤销造成的撤销丢失）

第二类丢失更新：A，B 事务同时操作同一数据，A先对改数据进行了更改，B再次更改并且提交，把A提交的数据给覆盖了。（事务提交造成的覆盖丢失）

」

脏读

一个事务在执行的过程中读取到了其他事务还没有提交的数据。这个还是比较好理解的。

「

两个事务同时操作同一数据，A事务对该数据进行了修改还没提交的时候，B事务访问了该条事务，并且使用了该数据，此时A事务回滚，那么B事务读到的就是脏数据。

比如事务1，修改了某个数据事务2，刚好访问了事务1修改后的数据

此时事务1，回滚了操作事务2，读到还是回滚前的数据

」

读已提交

从字面上我们就可以理解，即一个事务操作过程中可以读取到其他事务已经提交的数据。

事务中的每次读取操作，读取到的都是数据库中其他事务已提交的最新的数据（相当于当前读）

不可重复读

在同一事务中，多次读取同一数据返回的结果有所不同，换句话说，后续读取可以读到另一事务已提交的更新数据。相反，“可重复读” 在同一事务中多次读取数据时, 能够保证所读数据一样, 也就是后续读取不能读到另一事务已提交的更新数据。

「

这种情况发生在一个事务内多次读同一数据。A事务查询某条数据，该事务未结束时，B事务也访问同一数据并进行了修改。那么在A事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。

事务1，查询某个数据事务2，修改了某个数据，提交

事务1，再次查询这个数据

这样事务1两次查询的数据不一样，称为不可重复读

」

可重复读

一个事务操作中对于一个读取操作不管多少次，读取到的结果都是一样的。

幻读

脏读、不可重复读、可重复读、幻读，其中最难理解的是幻读

以mysql为例：

幻读现象例子：

可重复读模式下，比如有个用户表，手机号码为主键，有两个事物进行如下操作
事务A操作如下： 1、打开事务 2、查询号码为X的记录，不存在 3、插入号码为X的数据，插入报错（为什么会报错，先向下看） 4、查询号码为X的记录，发现还是不存在（由于是可重复读，所以读取记录X还是不存在的）
事物B操作：在事务A第2步操作时插入了一条X的记录，所以会导致A中第3步插入报错（违反了唯一约束）
上面操作对A来说就像发生了幻觉一样，明明查询X（A中第二步、第四步）不存在，但却无法插入成功
幻读可以这么理解：事务中后面的操作（插入号码X）需要上面的读取操作（查询号码X的记录）提供支持，但读取操作却不能支持下面的操作时产生的错误，就像发生了幻觉一样。
看第二种解释：

事务A在操作一堆数据的时候，事务B插入了一条数据，A事务再次（第二次）查询，发现多了一条数据，像是幻觉。与不可重复读类似，不同的是一个是修改删除操作，一个是新增操作。

3.6事务的隔离级别

当多个事务同时进行的时候，如何确保当前事务中数据的正确性，比如A、B两个事物同时进行的时候，A是否可以看到B已提交的数据或者B未提交的数据，这个需要依靠事务的隔离级别来保证，不同的隔离级别中所产生的效果是不一样的。

事务隔离级别主要是解决了上面多个事务之间数据可见性及数据正确性的问题。（或者说为了解决并发控制可能产生的异常问题，数据库定义了四种事务的隔离级别）

隔离级别分为4种：

读未提交：READ-UNCOMMITTED
读已提交：READ-COMMITTED
可重复读：REPEATABLE-READ
串行：SERIALIZABLE
上面4中隔离级别越来越强，会导致数据库的并发性也越来越低。

查看隔离级别

mysql> show variables like 'transaction_isolation';
+-----------------------+----------------+
| Variable_name     | Value      |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
1 row in set, 1 warning (0.00 sec)