SQL

MySQL, are you OK ?

"重逢的开端,MySQL"

Posted by HZY on December 9, 2025
  • 关系型数据库
  • 非关系型数据库

SQL : Structured Query Language

  • DDL: Data Defination Language
  • DML: Data Manipulation Language
  • DCL: Data Control Language

    启动服务

    ``` bash mysql -h 主机名 -P 端口号 -u 用户名 -p密码

mysql -h localhost -P 3306 -u root -p Enter password:**

#也可以使用MySQL自带的终端启动工具打开 #密码不要忘了，一般就是123456

mysql -V mysql –version

exit quit

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

``` sql
create database db_name;
use db_name;

show tables;
show tables from db_name;

create table t_name (
    field1  类型,
    field2  类型
);

select * from t_name;

insert int t_name values();

/*查看创建表的详细信息*/
show create table t_name\G

drop table t_name;
drop datebase db_name;

SELECT

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

select 列名 from table_name;
select * from tb_name;

-- 别名，不建议省略AS
SELECT long_name AS name,
    commission_pct comm,
    FROM tb_name;

-- 甚至可以加入算式
SELECT last_name "Name",
salary*12 "Annual Salary"
FROM em_table;

SELECT DISTINCT a_id,salary
FROM tb_name;

-- MySQL中，空值不等于空字符串，空战占用空间
SELECT emp_id,salary,commission_pct, 12 * salarry * (1 + commission_pct) "anual_sal" 
FROM tb_name;

-- 也有这种需求，增加一个new 字段
SELECT 'A表' as new_field , l_name 
FROM tb_name;

-- 显示表结构 
DESC tb_name;
DESCRIBE tb_name;

/*
- Field：表示字段名称。 
- Type：表示字段类型，这里 barcode、goodsname 是文本型的，price 是整数类型的。
- Null：表示该列是否可以存储NULL值。
- Key：表示该列是否已编制索引。PRI表示该列是表主键的一部分；UNI表示该列是UNIQUE索引的一部分；MUL表示在列中某个给定值允许出现多次。
- Default：表示该列是否有默认值，如果有，那么值是多少。
- Extra：表示可以获取的与给定列有关的附加信息，例如AUTO_INCREMENT等。
*/

-- 过滤
SELECT * FROM tb_name
WHERE 条件;

运算符

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97

/*
在Java中，+的左右两边如果有字符串，那么表示字符串的拼接。但是在MySQL中+只表示数值相加。如果遇到非数值类型，先尝试转成数值，如果转失败，就按0计算。（补充：MySQL中字符串拼接要使用字符串函数CONCAT()实现）

整数与浮点数进行运算也会有转型

+ - * / % MOD
*/

SELECT employee_id,salary,salary * 12 annual_sal 
FROM employees;

-- 比较运算符
SELECT FROM tb_name WHERE A=B
SELECT FROM tb_name WHERE A<=>B  --安全等于运算符
<>  != 

 = 
-- 等号两边比较，若类型相同的时候
-- 如果有一个是字符串，则自动转字符串再进行比较
-- 如果有一个为NULL，那么结果也是NULL
 <=>
-- 安全运算符对NULL进行判断
SELECT com_id ,comss_id, FROM employees WHERE  comm_id <=> 
0.40 

!= <>
-- 对于这些不等于的判断，没有空值判断，如果出现空值，结果就是空值

-- 非类型的运算符
IS NULL; 
IS NOTNULL;
LEAST;  -- return min
GREATEST -- return max 
ISNULL  
IN  -- 判断是否在表中
NOT IN 

LIKE    -- 模拟匹配
REGEXP  -- 判断是否符合正则
RLIKE   --   判断值是否符合正则

SELECT emp_id,comm_id FROM tb_name WHERE col_value IS NULL;
SELECT emp_id,comm_id FROM tb_name WHERE col_value com <=> NULL;
SELECT emp_id,comm_id FROM tb_name WHERE ISNULL(col_value);
SELECT emp_id,comm_id FROM tb_name WHERE col_value = NULL;
SELECT

#查询commission_pct不等于NULL
SELECT employee_id,commission_pct FROM employees WHERE commission_pct IS NOT NULL;
SELECT employee_id,commission_pct FROM employees WHERE NOT commission_pct <=> NULL;
SELECT employee_id,commission_pct FROM employees WHERE NOT ISNULL(commission_pct);

-- minVal
SELECT LEAST (1.0,3),l, LEAST('B','a','c'), LEAST(1,NULL,2);
-- 由结果可以看到，当参数是整数或者浮点数时，LEAST将返回其中最小的值；当参数为字符串时，返回字母表中顺序最靠前的字符；当比较值列表中有NULL时，不能判断大小，返回值为NULL。

--maxVal
SELECT LEAST (1.0,3),l, LEAST('B','a','c'), LEAST(1,NULL,2);
-- 那肯定和上面一样

--BETWEEN AND
SELECT salary,emp_id FROM tb_name 
WHERE salary BETWEEN 2500 AND 3500;

-- IN
-- IN运算符用于判断给定的值是否是IN列表中的一个值，如果是则返回1，否则返回0。如果给定的值为NULL，或者IN列表中存在NULL，则结果为NULL。
SELECT emp_id,last_name,manager_id;
FRPM emp_tbale WHERE manager_id IN (100,200,201);

-- NOT IN 

运算符用于判断给定的值是否不是IN列表中的一个值，如果不是IN列表中的一个值，则返回1，否则返回0。

-- LIKE运算符
SELECT first_name FROM emp_table WHERE first_name LIKE '%fhakj'
SELECT first_name FROM emp_table WHERE first_name LIKE '_A_'
-- 这里%占位符表示多个字符，_占位表示一个字符
-- NULL LIKE 'abc' 和 'abc' LIKE NULL的值都是NULL

-- ESCAPE
-- 用来自定义转义字符
SELECT job_id FROM job_table WHERE job_id LIKE 'IT\_%' ESCAP '\'
--我们用escape来自定义转义字符，也可使用其他字符

-- REGEXP 正则表达式
WHERE phone REGEXP '^[0-9]{11}$';

-- 逻辑运算符
NOT  或  !
-- NOT NULL 还是 NULL 
AND  &&
--
OR ||
--
XOR 
-- 位运算符号，这些太阴间了，我感觉用不到

排序与分页

1
2
3
4
5
6
7
8
9
10
11
12
13
14

ORDER BY id ASC; -- 默认升序
ORDER BY id DESC ;
ORDER BY id1,id2 DESC ;

LIMIT 0,10 ;
LIMIT 10 ;

LIMIT 10,10;   -- 11-20条

LIMIT 10 OFFSET 10  -- 8.0新语法，和上面那个一样

SELECT id FROM table_name WHERE id = '' LIMIT 1 
-- 我们如果提前知道只需要返回一条记录，就加上这句话，可以不扫描完整个表，提高效率

多表查询

  • 等值连接与非等值连接

    对于id连接的是等值连接 对于评定工资等级，用的是between，非等值连接

  • 自连接 非自连接
  • 内连接 与 外连接

    内连接就是，合并多个表，只有有相对应数据的行合并，对应不上的不要 而外连接就恰恰相反，没对应上的我用NULl替代

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64

-- sql中大多采用笛卡尔积，但这在很多时候并不是有效的
-- 所以我们使用 WHERE 关键字加入有效的连接条件
SELECT * FROM tb_1,tb_2 
WHERE tb_1.id = tb_2.id ;

SELECT employees.last_name, departments.department_name,employees.department_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

-- 使用别名简化
SELECT e.employee_id, e.last_name, e.department_id,
       d.department_id, d.location_id
FROM   employees e , departments d
WHERE  e.department_id = d.department_id;
-- 需要注意的是，如果我们使用了表的别名，在查询字段中、过滤条件中就只能使用别名进行代替，不能使用原有的表名，否则就会报错。

SELECT CONCAT(worker.last_name ,' works for ' 
       , manager.last_name)
FROM   employees worker, employees manager
WHERE  worker.manager_id = manager.employee_id ;

JOIN 
INNER JOIN 
CROSS JOIN 
--都是内连接

-- 一般使用 JOIN 和 ON 进行内连接
SELECT e.id,e.lm,e.id2,d.id
FROM table_e e JOIN table_d d 
ON (e.id = d.id )
-- 多级
SELECT employee_id, city, department_name
FROM   employees e 
JOIN   departments d
ON     d.department_id = e.department_id 
JOIN   locations l
ON     d.location_id = l.location_id;

-- 外连接
LEFT OUTER JOIN;
RIGHT OUTER JOIN;
FULL OUTER JOIN;
FULL JOIN; --Mysql不支持,但是可以用：
LEFT JOIN UNION RIGHT JOIN;

-- 直观地说，右连接就是留下第二个表的所有行，第一个表向它看齐
SELECT e.last_name, e.department_id, d.department_name
FROM   employees e
RIGHT OUTER JOIN departments d
ON    (e.department_id = d.department_id) ;


-- NUION 合并查询
UNION; -- 合并，去重
UNION ALL ;  -- 不去重


SELECT * FROM employees  WHERE email LIKE '%a%'
UNION
SELECT * FROM employees  WHERE department_id>90;


-- SQL JOINS 集合论说是
 


1
2
3
4
5
6
7
8

-- NATURAL JOIN : 自然连接 
 SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

--  USING连接
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

单行函数

| 函数 | 用法 | | ——————- | ———————————————————— | | ABS(x) | 返回x的绝对值 | | SIGN(X) | 返回X的符号。正数返回1，负数返回-1，0返回0 | | PI() | 返回圆周率的值 | | CEIL(x)，CEILING(x) | 返回大于或等于某个值的最小整数 | | FLOOR(x) | 返回小于或等于某个值的最大整数 | | LEAST(e1,e2,e3…) | 返回列表中的最小值 | | GREATEST(e1,e2,e3…) | 返回列表中的最大值 | | MOD(x,y) | 返回X除以Y后的余数 | | RAND() | 返回0~1的随机值 | | RAND(x) | 返回0~1的随机值，其中x的值用作种子值，相同的X值会产生相同的随机数 | | ROUND(x) | 返回一个对x的值进行四舍五入后，最接近于X的整数 | | ROUND(x,y) | 返回一个对x的值进行四舍五入后最接近X的值，并保留到小数点后面Y位 | | TRUNCATE(x,y) | 返回数字x截断为y位小数的结果 | | SQRT(x) | 返回x的平方根。当X的值为负数时，返回NULL |

函数 用法
RADIANS(x) 将角度转化为弧度，其中，参数x为角度值
DEGREES(x) 将弧度转化为角度，其中，参数x为弧度值
函数 用法
SIN(x) 返回x的正弦值，其中，参数x为弧度值
ASIN(x) 返回x的反正弦值，即获取正弦为x的值。如果x的值不在-1到1之间，则返回NULL
COS(x) 返回x的余弦值，其中，参数x为弧度值
ACOS(x) 返回x的反余弦值，即获取余弦为x的值。如果x的值不在-1到1之间，则返回NULL
TAN(x) 返回x的正切值，其中，参数x为弧度值
ATAN(x) 返回x的反正切值，即返回正切值为x的值
ATAN2(m,n) 返回两个参数的反正切值
COT(x) 返回x的余切值，其中，X为弧度值

| 函数 | 用法 | | ——————– | —————————————————- | | POW(x,y)，POWER(X,Y) | 返回x的y次方 | | EXP(X) | 返回e的X次方，其中e是一个常数，2.718281828459045 | | LN(X)，LOG(X) | 返回以e为底的X的对数，当X <= 0 时，返回的结果为NULL | | LOG10(X) | 返回以10为底的X的对数，当X <= 0 时，返回的结果为NULL | | LOG2(X) | 返回以2为底的X的对数，当X <= 0 时，返回NULL |

SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),
HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE())
FROM DUAL;

这里东西太多了，我们直接把时间这里跳过了，以后有实际需求再慢慢学

1
2
3
4
5
6
7
8
9
10
11
12
13
14

IF(value1,Tvalue,Fvalue);  -- 如果value1为T，返回Tvalue，否则
IFNULL(value1,value2);  --value1不为NULL，返回value1，否则

SELECT 
    employee_id,
    department_id,
    salary,
    CASE department_id
        WHEN 10 THEN salary * 1.1
        WHEN 20 THEN salary * 1.2
        WHEN 30 THEN salary * 1.3
        ELSE salary
    END AS adjusted_salary
FROM employee_table;

1
2
3
4
5
6
7
8
9

//函数加密
PASSWORD(str);
MD5(str);
SHA(str);

-- 下面的函数已经废弃了，这什么傻鸟教程
ENCODE(value,password_seed) -- 返回使用password_seed作为加密密码加密
valueDECODE(value,password_seed) -- 返回使用password_seed作为加密密码解密value

函数这一节学的有点头疼，主要还是api过多。

聚合函数

  • AVG()
  • SUM()
  • MAX()
  • MIN()
  • COUNT()
1
2

COUNT(*) --会统计值为NULL的行
COUNT(列名) -- 不会统计列为NULL的值
  • GROUP BY ``` sql SELECT department_id, AVG(salary) FROM employees GROUP BY department_id ;

SELECT department_id,AVG(salary) FROM employees WHERE department_id > 80 GROUP BY department_id WITH ROLLUP;

SELECT department_id, MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary)>10000 ; – 使用GROUP BY之后就要使用HAVING进行过滤， – 禁止 在WHERE中使用GROUP BY ！！！

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

``` sql
#方式1：
SELECT ...,....,...
FROM ...,...,....
WHERE 多表的连接条件
AND 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...

#方式2：
SELECT ...,....,...
FROM ... JOIN ... 
ON 多表的连接条件
JOIN ...
ON ...
WHERE 不包含组函数的过滤条件
AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...

#其中：
#（1）from：从哪些表中筛选
#（2）on：关联多表查询时，去除笛卡尔积
#（3）where：从表中筛选的条件
#（4）group by：分组依据
#（5）having：在统计结果中再次筛选
#（6）order by：排序
#（7）limit：分页

子查询

从本章开始，不记忆那么多的api，而把时间专注在如何实现 ``` sql SELECT last_name,salary FROM employees WHERE salary > ( SELECT salary FROM employees WHERE last_name = ‘Abel’ );

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77

// 这里内容挺多的，主要还是api


#### 创建和管理表
 
| 类型             | 类型举例                                                     |
| ---------------- | ------------------------------------------------------------ |
| 整数类型         | TINYINT、SMALLINT、MEDIUMINT、**INT(或INTEGER)**、BIGINT     |
| 浮点类型         | FLOAT、DOUBLE                                                |
| 定点数类型       | **DECIMAL**                                                  |
| 位类型           | BIT                                                          |
| 日期时间类型     | YEAR、TIME、**DATE**、DATETIME、TIMESTAMP                    |
| 文本字符串类型   | CHAR、**VARCHAR**、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT      |
| 枚举类型         | ENUM                                                         |
| 集合类型         | SET                                                          |
| 二进制字符串类型 | BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB      |
| JSON类型         | JSON对象、JSON数组                                           |
| 空间数据类型     | 单值：GEOMETRY、POINT、LINESTRING、POLYGON；<br/>集合：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION |

其中，常用的几类类型介绍如下：

| 数据类型      | 描述                                                         |
| ------------- | ------------------------------------------------------------ |
| INT           | 从-2^31到2^31-1的整型数据。存储大小为 4个字节                |
| CHAR(size)    | 定长字符数据。若未指定，默认为1个字符，最大长度255           |
| VARCHAR(size) | 可变长字符数据，根据字符串实际长度保存，**必须指定长度**     |
| FLOAT(M,D)    | 单精度，占用4个字节，M=整数位+小数位，D=小数位。 D<=M<=255,0<=D<=30，默认M+D<=6 |
| DOUBLE(M,D)   | 双精度，占用8个字节，D<=M<=255,0<=D<=30，默认M+D<=15         |
| DECIMAL(M,D)  | 高精度小数，占用M+2个字节，D<=M<=65，0<=D<=30，最大取值范围与DOUBLE相同。 |
| DATE          | 日期型数据，格式'YYYY-MM-DD'                                 |
| BLOB          | 二进制形式的长文本数据，最大可达4G                           |
| TEXT          | 长文本数据，最大可达4G                                       |

``` sql
CREATE DATABASE db_name;
CREATE DATABASE IF NOT EXISTS db_name 
CHARACTER SET 字符集；

SHOW DATABASES ;
SELECT DATABASE();

ALTER DATABASE 数据库名 CHARACTER SET 字符集;  #比如：gbk、utf8等

DROP DATABASE IF EXISTS 数据库名;

CREATE TABLE [IF NOT EXISTS] 表名(
	字段1, 数据类型 [约束条件] [默认值],
	字段2, 数据类型 [约束条件] [默认值],
	字段3, 数据类型 [约束条件] [默认值],
	……
	[表约束条件]
);

DESC emp;  -- 显示创建表的语句

CREATE TABLE emp1 AS SELECT * FROM employees;

SHOW CREATE TABLE 表名\G;

ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;

ALTER TABLE 表名 MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER 字段名2】;

ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;

ALTER TABLE tb_name DROP COLUMN col_name

-- 重命名表
RENAME TABLE tb_name TO new_tb_name;
ALTER TABLE tb_name REANME TO new_tb_name

--删除表
DROP TABLE [IF EXISTS] tb_name
-- 清空表
TRUNCATE TABLE tb_name; --使用这条语句不能进行回滚，但DELETE可以回滚

在MySQL 8.0版本中，InnoDB表的DDL支持事务完整性，即DDL操作要么成功要么回滚。DDL操作回滚日志写入到data dictionary数据字典表mysql.innodb_ddl_log（该表是隐藏的表，通过show tables无法看到）中，用于回滚操作。通过设置参数，可将DDL操作日志打印输出到MySQL错误日志中。

数据处理之增删改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

-- 增
INSERT INTO tb_name
VALUES (v1,v2,v3);

INSERT INTO tb_name(field1)
VALUES (value1)

INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM   employees
WHERE  job_id LIKE '%REP%';

-- 改
UPDATE tb_name 
SET field1 = value1, field2 = value2
WHERE   id=id   -- 不加WHERE就可以死了


-- 删
DELETE FROM departments
WHERE  department_name = 'Finance';

-- 计算列
CREATE TABLE tb1(
id INT,
a INT,
b INT,
c INT GENERATED ALWAYS AS (a + b) VIRTUAL
);
CATALOG
    FEATURED TAGS
    Ubuntu Java java MathModeling 杂记 about me C++ OpenCV Vue Memory OS RMV SQL ai rust 青协宣传办 面经 Algorithm CMake DataStructure Development Diary Error Golang Linux NoSQL RoboCup Rust Version vibe coding
    FRIENDS