MySQL学习记录

发表于 本文字数： 14k 阅读时长 ≈ 12 分钟

MySQL学习笔记（易忘）

节选自 ： MySQL 教程 | 菜鸟教程 侵权删

一、数据库

1、基础操作

1
2
3
4
5
6
7
8
9
10
11
12
#创建数据库
CREATE DATABASE 数据库名;

#扩展语法
CREATE DATABASE [IF NOT EXISTS] database_name
  [CHARACTER SET charset_name]
  [COLLATE collation_name];

#删除数据库
DROP DATABASE <database_name>;        -- 直接删除数据库，不检查是否存在
或
DROP DATABASE [IF EXISTS] <database_name>;

2、选择数据库

1
2
3
4
5
6
7
8
9
10
11
#登录数据库
mysql -uroot -p123456

#查看已有数据库
SHOW DATABASES;

#选择已有数据库
USE learn_sql;

#选择已有数据表
SHOW TABLES;

二、数据表

1、数据类型略

MySQL 数据类型 | 菜鸟教程

2、操作数据库表

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
#创建数据库表
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
);


#实例
CREATE TABLE users(
	id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    birthdate DATE,
    is_active BOOLEAN DEFAULT TRUE
);

#指定字符编码、排序规则、默认引擎
CREATE TABLE mytable (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

3、删除数据库表

1
2
3
DROP TABLE [IF EXISTS] table_name;  -- 会检查是否存在，如果存在则删除

TRUNCATE TABLE table_name; -- 只删除数据，不删除表结构

4、插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
INSERT INTO users (username, email, birthdate, is_active) VALUES
('john_doe', 'john@example.com', '1990-05-15', TRUE),
('jane_smith', 'jane.smith@example.org', '1985-08-22', DEFAULT),
('bob_johnson', 'bob@test.net', NULL, FALSE),
('alice_wonder', 'alice@wonderland.com', '1995-11-30', TRUE),
('mike_brown', 'mike.brown@company.com', '1988-03-10', TRUE),
('sarah_lee', 'sarah.lee@mail.com', '1992-07-18', DEFAULT),
('tom_hanks', 'tom@hollywood.com', '1956-07-09', FALSE),
('emma_watson', 'emma@actress.org', '1990-04-15', TRUE),
('test_user', 'test@demo.io', NULL, FALSE),
('admin', 'admin@system.com', '1980-01-01', TRUE);

INSERT INTO users VALUES (NULL,'test', 'test@runoob.com', '1990-01-01', true); -- NULL 是用于自增长列的占位符，表示系统将为 id 列生成一个唯一的值。

5、查询数据

1
2
3
4
5
6
7
8
#基础语法
SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[ORDER BY column_name [ASC | DESC]]
[LIMIT number];

SELECT username from users where is_active=1 order by id asc limit 5;

在 WHERE 子句中，你可以使用各种条件运算符（如 =, <, >, <=, >=, !=），逻辑运算符（如 AND, OR, NOT），以及通配符（如 %）等

MySQL WHERE 子句 | 菜鸟教程

1
2
3
4
5
6
SELECT * FROM users WHERE birthdate IN ('1990-01-01', '1992-03-15', '1993-05-03');

# 模糊匹配条件（LIKE）
SELECT * FROM customers WHERE first_name LIKE 'J%';
# BETWEEN 条件
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

6、更新语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

UPDATE users SET username = 'john' where id = 1;

#使用表达式更新值
UPDATE products
SET price = price * 1.1
WHERE category = 'Electronics';

#更新使用子查询的值
UPDATE customers
SET total_purchases = (
    SELECT SUM(amount)
    FROM orders
    WHERE orders.customer_id = customers.customer_id
)
WHERE customer_type = 'Premium';

7、删除语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DELETE FROM table_name
WHERE condition;


DELETE FROM students
WHERE graduation_year = 2021;

#使用子查询删除符合条件的行
DELETE FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    WHERE order_date < '2023-01-01'
);

8、LIKE

如果没有通配符%，那么LIKE和=效果一样。 通配符包括**_** 与%

1
2
3
4
5
6
7
8
9
10
SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern;

SELECT * from where username like '%e%' ;


#组合使用 % 和 _ 
SELECT * FROM users WHERE username LIKE 'a%o_';
#以上 SQL 语句将匹配以字母 'a' 开头，然后是零个或多个字符，接着是 'o'，最后是一个任意字符的字符串，如 'aaron'、'apol'。

9、UNION

将查询到的两个表结果汇总

UNION默认去重。UNION ALL不会去重。UNION 操作中的列数和数据类型必须相同

1
2
3
4
5
6
7
8
9
10
SELECT column1, column2, ...
FROM table1
WHERE condition1
UNION
SELECT column1, column2, ...
FROM table2
WHERE condition2
[ORDER BY column1, column2, ...];

SELECT username from users where id=1 UNION SELECT username from users where id=2 ORDER BY username asc;

10、ORDER

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;


#可以按顺序排序
SELECT * from users ORDER BY id desc , username desc;

#使用数字表示列的位置
SELECT first_name, last_name, salary
FROM employees
ORDER BY 3 DESC, 1 ASC;

#使用表达式排序
SELECT product_name, price * discount_rate AS discounted_price
FROM products
ORDER BY discounted_price DESC;

# NULL 值排在最前/最后 MySQL 8.0.16 版本开始
SELECT product_name, price
FROM products
ORDER BY price DESC NULLS FIRST/LAST;

11、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
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
SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition
GROUP BY column1;

#有一个表
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    order_amount DECIMAL(10, 2) NOT NULL,
    INDEX idx_customer (customer_id)  -- 为customer_id添加索引提高查询性能
);

# 将相同customer_id的用户的订单额order_amount聚合变为total_amount 
#聚合函数有 COUNT, SUM, AVG,等函数
SELECT customer_id,SUM(order_amount) AS total_amount FROM orders GROUP BY customer_id;
+-------------+--------------+
| customer_id | total_amount |
+-------------+--------------+
|         101 |       411.85 |
|         102 |       335.49 |
|         103 |       430.30 |
|         104 |       445.50 |
|         105 |       250.50 |
|         106 |       251.74 |
|         107 |       750.75 |
|         108 |       478.70 |
|         109 |       299.99 |
|         110 |        88.40 |
+-------------+--------------+

#使用 WITH ROLLUP WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。
SELECT customer_id,SUM(order_amount) AS total_amount FROM orders GROUP BY customer_id WITH ROLLUP;

+-------------+--------------+
| customer_id | total_amount |
+-------------+--------------+
|         101 |       411.85 |
|         102 |       335.49 |
|         103 |       430.30 |
|         104 |       445.50 |
|         105 |       250.50 |
|         106 |       251.74 |
|         107 |       750.75 |
|         108 |       478.70 |
|         109 |       299.99 |
|         110 |        88.40 |
|        NULL |      3743.22 |
+-------------+--------------+

#coalesce 来设置一个可以取代 NUll 的名称
SELECT coalesce(customer_id,'总数') customer_id,SUM(order_amount) AS total_amount FROM orders GROUP BY customer_id WITH ROLLUP;
+-------------+--------------+
| customer_id | total_amount |
+-------------+--------------+
| 101         |       411.85 |
| 102         |       335.49 |
| 103         |       430.30 |
| 104         |       445.50 |
| 105         |       250.50 |
| 106         |       251.74 |
| 107         |       750.75 |
| 108         |       478.70 |
| 109         |       299.99 |
| 110         |        88.40 |
| 总数        |      3743.22 |
+-------------+--------------+

12、JOIN

  • INNER JOIN 交连接

  • LEFT JOIN 左连接，保留左边，LEFT JOIN 返回左表的所有行，并包括右表中匹配的行，如果右表中没有匹配的行，将返回 NULL 值

  • RIGHT JOIN 右连接，保留右边，RIGHT JOIN 返回右表的所有行，并包括左表中匹配的行，如果左表中没有匹配的行，将返回 NULL 值

下面重点展示INNER JOIN

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
SELECT column1, column2, ...
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name


#基础例子
SELECT id,username,order_amount FROM users INNER JOIN orders ON users.id = orders.customer_id;
+----+--------------+--------------+
| id | username     | order_amount |
+----+--------------+--------------+
|  1 | john         |       125.50 |
|  1 | john         |        45.75 |
|  1 | john         |       240.60 |
|  2 | jane_smith   |        89.99 |
|  2 | jane_smith   |       150.00 |
|  2 | jane_smith   |        95.50 |
|  3 | bob_johnson  |       220.00 |
|  3 | bob_johnson  |       210.30 |
|  4 | alice_wonder |       320.50 |
|  4 | alice_wonder |       125.00 |
|  5 | mike_brown   |        75.25 |
|  5 | mike_brown   |       175.25 |
|  6 | sarah_lee    |        95.99 |
|  6 | sarah_lee    |       155.75 |
|  7 | tom_hanks    |       430.75 |
|  7 | tom_hanks    |       320.00 |
|  8 | emma_watson  |        68.50 |
|  8 | emma_watson  |       410.20 |
|  9 | test_user    |       299.99 |
| 10 | admin        |        88.40 |
+----+--------------+--------------+


#使用表别名
SELECT o.order_id, c.customer_name
FROM orders AS o
INNER JOIN customers AS c ON o.customer_id = c.customer_id;	

#复杂写法
SELECT u.id,u.username,SUM(o.order_amount)AS total_price  FROM users u INNER JOIN orders o ON u.id = o.customer_id group by u.id;

#多表 INNER JOIN
SELECT orders.order_id, customers.customer_name, products.product_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
INNER JOIN order_items ON orders.order_id = order_items.order_id
INNER JOIN products ON order_items.product_id = products.product_id;

13、NULL值处理

  • IS NULL: 当列的值是 NULL,此运算符返回 true。

  • IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。

  • <=>: 比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true。

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
# 检查是否为 NULL：
SELECT * FROM employees WHERE department_id IS NULL;
SELECT * FROM employees WHERE department_id IS NOT NULL;
# 使用 COALESCE 函数处理 NULL COALESCE 函数可以用于替换为 NULL 的值，
select id,username,coalesce(birthdate,0) AS birth from users;
+----+--------------+------------+
| id | username     | birth      |
+----+--------------+------------+
|  1 | john         | 1990-05-15 |
|  2 | jane_smith   | 1985-08-22 |
|  3 | bob_johnson  | 0          |
|  4 | alice_wonder | 1995-11-30 |
|  5 | mike_brown   | 1988-03-10 |
|  6 | sarah_lee    | 1992-07-18 |
|  7 | tom_hanks    | 1956-07-09 |
|  8 | emma_watson  | 1990-04-15 |
|  9 | test_user    | 0          |
| 10 | admin        | 1980-01-01 |
+----+--------------+------------+
# 使用 IFNULL 函数处理 NULL：
select id,username,IFNULL(birthdate,0) AS birth from users;

# 使用 <=> 操作符进行 NULL 比较
select id,username  from users where birthdate <=> NULL;
+----+-------------+
| id | username    |
+----+-------------+
|  3 | bob_johnson |
|  9 | test_user   |
+----+-------------+
# 注意聚合函数对 NULL 的处理： 在使用聚合函数（如 COUNT, SUM, AVG）时，它们会忽略 NULL 值，因此可能会得到不同于预期的结果。如果希望将 NULL 视为 0，可以使用 COALESCE 或 IFNULL。
SELECT AVG(COALESCE(salary, 0)) AS avg_salary FROM employees;

14、正则表达式

REGEXP RLIKE

MySQL 正则表达式 | 菜鸟教程

正则表达式匹配的字符类

  • .：匹配任意单个字符。

  • ^：匹配字符串的开始。

  • $：匹配字符串的结束。

  • *：匹配零个或多个前面的元素。

  • +：匹配一个或多个前面的元素。

  • ?：匹配零个或一个前面的元素。

  • [abc]：匹配字符集中的任意一个字符。

  • [^abc]：匹配除了字符集中的任意一个字符以外的字符。

  • [a-z]：匹配范围内的任意一个小写字母。

  • [0-9]：匹配一个数字字符。

  • \w：匹配一个字母数字字符（包括下划线）。

  • \s：匹配一个空白字符。

例子可参考网站

1
2
# 查找 name 字段中以元音字符开头或以 'ok' 字符串结尾的所有数据：
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

15、事务

  • BEGIN 、 START TRANSACTION 开启一个事务

  • COMMIT 提交事务

  • ROLLBACK 回滚事务

  • SAVEPOINT savepoint_name 设置保存点

  • ROLLBACK TO SAVEPOINT 回滚到之前设置的保存点

16、ALTER

更改数据表结构

对列的修改：

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
#添加列
ALTER TABLE employees
ADD COLUMN birth_date DATE;

ALTER TABLE users 
ADD COLUMN sex ENUM('男','女');
#修改列的数据类型
ALTER TABLE TABLE_NAME
MODIFY COLUMN column_name new_datatype;

ALTER TABLE users MODIFY username varchar(250);
#修改列名
ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name datatype;

ALTER TABLE users CHANGE COLUMN  sexy sex ENUM('男','女') ;

#删除列
ALTER TABLE table_name
DROP COLUMN column_name;

ALTER TABLE users
DROP COLUMN birth_date;

#添加主键
ALTER TABLE table_name
ADD PRIMARY KEY (column_name);

ALTER TABLE users
ADD PRIMARY KEY(id);

#添加外键
ALTER TABLE child_table
ADD CONSTRAINT fk_name
FOREIGN KEY (column_name)
REFERENCES parent_table (column_name);

ALTER TABLE orders 
ADD CONSTRAINT fk_customer 
FOREIGN KEY(customer_id) 
REFERENCES users(id);

#修改表名
ALTER TABLE old_table_name
RENAME TO new_table_name;

ALTER TABLE orderss RENAME TO orders

17、索引

  1. 操作索引

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
# 普通索引
CREATE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

CREATE INDEX index_username on users (username ASC);

#通过ALTER TABLE命令
ALTER TABLE table_name
ADD INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

ALTER TABLE orders ADD INDEX index_customer_id (customer_id asc);

#创建表的时候直接指定
CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  ...,
  INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...)
);


#删除索引的用法
DROP INDEX index_name ON table_name;

ALTER TABLE table_name
DROP INDEX index_name;

  1. 唯一索引

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
#创建唯一索引
CREATE UNIQUE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

CREATE UNIQUE INDEX unique_email on users(email asc);

#通过ALTER TABLE命令
ALTER table table_name 
ADD CONSTRAINT unique_constraint_name UNIQUE (column1, column2, ...);


ALTER TABLE users ADD CONSTRAINT uniquee_email UNIQUE (email asc);

#创建表的时候直接指定
CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  ...,
  CONSTRAINT index_name UNIQUE (column1 [ASC|DESC], column2 [ASC|DESC], ...)
);

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(100) UNIQUE
);

  1. 使用ALTER命令添加删除索引

1
2
3
4
5
6
7
8
9
10
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，主键列中的值必须唯一，主键的列的列表，可以是一个或多个列，不能包含 NULL 值。 。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。


#显示索引信息
SHOW INDEX FROM table_name\G

SHOW INDEX FROM users \G;

三、杂项

1、临时表

临时表只在连接会话连接的时候生效，关闭连接就会失效

关于临时表：

MySQL 临时表 | 菜鸟教程

2、复制表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#查看建表代码
SHOW CREATE TABLE users;

#复制表的内容  INSERT INTO ... SELECT
mysql> INSERT INTO clone_tbl (runoob_id,
    ->                        runoob_title,
    ->                        runoob_author,
    ->                        submission_date)
    -> SELECT runoob_id,runoob_title,
    ->        runoob_author,submission_date
    -> FROM runoob_tbl;
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

使用 mysqldump 命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysqldump -u root -p123456 learn_sql users > learn_sql_users.sql

#保存的文件一般在 C盘用户目录下： C:\Users\Lenovo\learn_sql_users.sql

#将数据还原
mysql -u root -p123456 new_users < learn_sql_users.sql

#加上 --no-data 表示，只导出数据库结构
mysqldump -u username -p password -h hostname --no-data database_name > output_file.sql

#备份所有数据库数据--all-databases
mysqldump -u root -p --all-databases > database_dump.sql

#备份远程数据库的文件
mysqldump -h 127.0.0.1 -P 3306  -u root -p 123456  wpf > wpf_dump.sql

3、元数据

MySQL 元数据 | 菜鸟教程

MySQL 元数据是关于数据库和其对象（如表、列、索引等）的信息。

元数据存储在系统表中，这些表位于 MySQL 数据库的 information_schema 数据库中，通过查询这些系统表，你可以获取关于数据库结构、对象和其他相关信息的详细信息。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
#查看表的结构
DESC table_name;

#查看列的信息
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_KEY
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'learn_sql'
AND TABLE_NAME = 'users';

+-------------+-----------+-------------+------------+
| COLUMN_NAME | DATA_TYPE | IS_NULLABLE | COLUMN_KEY |
+-------------+-----------+-------------+------------+
| id          | int       | NO          | PRI        |
| username    | varchar   | YES         | MUL        |
| email       | varchar   | NO          | UNI        |
| birthdate   | date      | YES         |            |
| is_active   | tinyint   | YES         |            |
| sex         | enum      | YES         |            |
+-------------+-----------+-------------+------------+
6 rows in set (0.00 sec)

4、自增 AUTO_INCREMENT

1
2
3
4
5
#获取刚刚插入的行的自增值
SELECT LAST_INSERT_ID();

#获取表的当前自增值
SHOW TABLE STATUS LIKE 'users';

重置序列

1
2
3
4
5
6
#删除自增列
ALTER TABLE users DROP id
#重新排列。但是有风险
ALTER TABLE users
ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (id);

5、重复数据

MySQL 处理重复数据 | 菜鸟教程

1
2
3
#忽略设置UNIQUE的重复数据
INSERT IGNORE INTO person_tbl (last_name, first_name)
    VALUES( 'Jay', 'Thomas');

6、导入数据

MySQL 导入数据 | 菜鸟教程

当然，都5202年了，用Navicat也是很方便的。 常用：

  1. mysql命令导入

    1
    mysql -uroot -p123456 < runoob.sql

  2. source命令导入

    1
    2
    3
    4
    mysql> create database abc;      # 创建数据库
    mysql> use abc;                  # 使用已创建的数据库 
    mysql> set names utf8;           # 设置编码
    mysql> source /home/abc/abc.sql  # 导入备份数据库

7、函数

自行查看：MySQL 函数 | 菜鸟教程