发表更新数据库10 分钟读完 (大约1522个字)0次访问

简明 MySQL 教程

一切的前提，你有一个已经安装好并可以使用的 MySQL 应用（如果没安装的点这里）！

本文以「用户的图书」场景为例子，一起学习 MySQL ～

1 连接数据库

1
2
mysql -h 127.0.0.1 -P 3306 -u root -p
# 输入密码进入

2 创建数据库和表 CREATE

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
-- 创建 book 数据库
CREATE DATABASE book;
-- Query OK, 1 row affected (0.03 sec)

-- 查看所有数据库，book 也在其中
SHOW DATABASES;
-- +--------------------+
-- | Database           |
-- +--------------------+
-- | book               |
-- | information_schema |
-- | mysql              |
-- | performance_schema |
-- | sys                |
-- +--------------------+
-- 5 rows in set (0.01 sec)

-- 进入 book 数据库
USE book;
-- Database changed

-- 创建用户表
CREATE TABLE user (
  id INT UNSIGNED AUTO_INCREMENT,
  nick VARCHAR(100) NOT NULL,
  PRIMARY KEY (id)
);
-- Query OK, 0 rows affected (0.17 sec)

-- 查看所有表，此时只有唯一一个 user 表
SHOW TABLES;
-- +----------------+
-- | Tables_in_book |
-- +----------------+
-- | user           |
-- +----------------+
-- 1 row in set (0.00 sec)

-- 创建图书表
CREATE TABLE book (
  id INT UNSIGNED AUTO_INCREMENT,
  title VARCHAR(100) NOT NULL,
  info VARCHAR(1000) NOT NULL,
  PRIMARY KEY (id)
);
-- Query OK, 0 rows affected (0.18 sec)

-- 创建用户和图书的关系表
CREATE TABLE user_book (
  user_id INT UNSIGNED NOT NULL,
  book_id INT UNSIGNED NOT NULL,
  FOREIGN KEY (user_id) REFERENCES user (id),
  FOREIGN KEY (book_id) REFERENCES book (id)
);
-- Query OK, 0 rows affected (0.19 sec)

-- 查看所有表
SHOW TABLES;
-- +----------------+
-- | Tables_in_book |
-- +----------------+
-- | book           |
-- | user           |
-- | user_book      |
-- +----------------+
-- 3 rows in set (0.00 sec)

3 基本的增删改查 INSERT DELETE UPDATE 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
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
-- 插入数据到 user 表中
INSERT INTO user(nick) 
VALUES
  ("xiaoming"),
  ("xiaohong"),
  ("xiaoli");

-- 查看刚刚插入的数据
SELECT * FROM user;
-- +----+----------+
-- | id | nick     |
-- +----+----------+
-- |  1 | xiaoming |
-- |  2 | xiaohong |
-- |  3 | xiaoli   |
-- +----+----------+
-- 3 rows in set (0.00 sec)
-- id 字段虽然没有插入，它会自增的生成

-- 此时我们发现 xiaoli 这条数据插入错了，把它删掉
DELETE FROM user WHERE nick="xiaoli";
-- Query OK, 1 row affected (0.01 sec)

-- 再次查看，xiaoli 这条数据已经被删掉了
SELECT * FROM user;
-- +----+----------+
-- | id | nick     |
-- +----+----------+
-- |  1 | xiaoming |
-- |  2 | xiaohong |
-- +----+----------+
-- 2 rows in set (0.00 sec)

-- 这时 xiaoming 把自己的名字修改成了 mingming
UPDATE user SET nick="mingming" WHERE id=1;
-- Query OK, 1 row affected (0.01 sec)
-- Rows matched: 1  Changed: 1  Warnings: 0

-- 查看数据，mingming 已经修改好了
SELECT * FROM user;
-- +----+----------+
-- | id | nick     |
-- +----+----------+
-- |  1 | mingming |
-- |  2 | xiaohong |
-- +----+----------+
-- 2 rows in set (0.00 sec)

-- 类似地，插入 book 的数据
INSERT INTO book(title, info) 
VALUES
  ("yuwen", "zi yue: xue er shi xi zhi"),
  ("shuxue", "1+1=2, 2+2=4"),
  ("yingyu", "hello world");
-- Query OK, 3 rows affected (0.03 sec)
-- Records: 3  Duplicates: 0  Warnings: 0

-- 查看 book 数据
SELECT * FROM book;
-- +----+--------+---------------------------+
-- | id | title  | info                      |
-- +----+--------+---------------------------+
-- |  1 | yuwen  | zi yue: xue er shi xi zhi |
-- |  2 | shuxue | 1+1=2, 2+2=4              |
-- |  3 | yingyu | hello world               |
-- +----+--------+---------------------------+
-- 3 rows in set (0.01 sec)

-- 插入关系表 user_book 的数据
INSERT INTO user_book (user_id, book_id)
VALUES 
    (1, 1),
    (1, 2),
    (1, 3),
    (2, 1),
    (2, 3);
-- Query OK, 5 rows affected (0.06 sec)
-- Records: 5  Duplicates: 0  Warnings: 0

-- 查看 user_book 的数据
SELECT * FROM user_book;
-- +---------+---------+
-- | user_id | book_id |
-- +---------+---------+
-- |       1 |       1 |
-- |       1 |       2 |
-- |       1 |       3 |
-- |       2 |       1 |
-- |       2 |       3 |
-- +---------+---------+
-- 5 rows in set (0.00 sec)

4 模糊查询 LIKE

1
2
3
4
5
6
7
8
-- 查看带有 xue 的图书，可以使用 LIKE 进行模糊查询
SELECT * FROM book WHERE info LIKE "%xue%";
-- +----+-------+---------------------------+
-- | id | title | info                      |
-- +----+-------+---------------------------+
-- |  1 | yuwen | zi yue: xue er shi xi zhi |
-- +----+-------+---------------------------+
-- 1 row in set (0.00 sec)

5 执行计算

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 将书名全部大写
mysql> SELECT UPPER(title) FROM book;
+--------------+
| UPPER(title) |
+--------------+
| YUWEN        |
| SHUXUE       |
| YINGYU       |
+--------------+
3 rows in set (0.00 sec)

-- 将书名和内容拼接到一起
mysql> SELECT CONCAT(title, "--", info) FROM book;
+----------------------------------+
| CONCAT(title, "--", info)        |
+----------------------------------+
| yuwen--zi yue: xue er shi xi zhi |
| shuxue--1+1=2, 2+2=4             |
| yingyu--hello world              |
+----------------------------------+
3 rows in set (0.00 sec)

6 子查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 查看 mingming 的拥有所有图书名称
-- 通过子查询，先找到 mingming 的 user id，根据关联表找到 book id，最后通过 book id 找到所有 book title
SELECT title FROM book WHERE id IN (
    SELECT book_id FROM user_book WHERE user_id = (
        SELECT id FROM user WHERE nick="mingming"
    )
);
-- +--------+
-- | title  |
-- +--------+
-- | yuwen  |
-- | shuxue |
-- | yingyu |
-- +--------+
-- 3 rows in set (0.00 sec)

7 视图 VIEW

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
-- 我们如果多次用到 mingming 的 book id，那么就可以考虑创建虚拟表 mingming_book_id
-- mysql 中 VIEW 就是用来创建这种虚拟表的
CREATE VIEW mingming_book AS
(
  SELECT * FROM book WHERE id IN (
      SELECT book_id FROM user_book WHERE user_id = (
          SELECT id FROM user WHERE nick="mingming"
      )
  )
);
-- Query OK, 0 rows affected (0.03 sec)

-- 如果此时查看所有表，那么 mingming_book 也会出现在里面
SHOW TABLES;
-- +------------------+
-- | Tables_in_book   |
-- +------------------+
-- | book             |
-- | mingming_book    |
-- | user             |
-- | user_book        |
-- +------------------+
-- 4 rows in set (0.00 sec)

-- 查看 mingming_book 的内容
SELECT * FROM mingming_book;
-- +----+--------+---------------------------+
-- | id | title  | info                      |
-- +----+--------+---------------------------+
-- |  1 | yuwen  | zi yue: xue er shi xi zhi |
-- |  2 | shuxue | 1+1=2, 2+2=4              |
-- |  3 | yingyu | hello world               |
-- +----+--------+---------------------------+
-- 3 rows in set (0.00 sec)

-- 此时如果我们再想查看 mingming 的拥有所有图书名称
-- 现在就很方便了
SELECT title FROM mingming_book;
-- +--------+
-- | title  |
-- +--------+
-- | yuwen  |
-- | shuxue |
-- | yingyu |
-- +--------+
-- 3 rows in set (0.00 sec)

-- mingming_book 虽然是个虚拟表，但无法用删表语句删除
DROP TABLE mingming_book;
-- ERROR 1051 (42S02): Unknown table 'book.mingming_book'

-- 正确的删除方式是使用 DROP VIEW
DROP VIEW mingming_book;
-- Query OK, 0 rows affected (0.04 sec)

-- 查看所有表，此时 mingming_book 已经被删除了
SHOW TABLES;
-- +----------------+
-- | Tables_in_book |
-- +----------------+
-- | book           |
-- | user           |
-- | user_book      |
-- +----------------+
-- 3 rows in set (0.01 sec)

8 排序和分组 ORDER/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
-- 以 book_id 升序排 user_book，ASC 可以省略
SELECT * FROM user_book ORDER BY book_id;
SELECT * FROM user_book ORDER BY book_id ASC;
-- +---------+---------+
-- | user_id | book_id |
-- +---------+---------+
-- |       1 |       1 |
-- |       2 |       1 |
-- |       1 |       2 |
-- |       1 |       3 |
-- |       2 |       3 |
-- +---------+---------+
-- 5 rows in set (0.00 sec)

-- 以 book_id 降序排 user_book
SELECT * FROM user_book ORDER BY book_id DESC;
-- +---------+---------+
-- | user_id | book_id |
-- +---------+---------+
-- |       1 |       3 |
-- |       2 |       3 |
-- |       1 |       2 |
-- |       1 |       1 |
-- |       2 |       1 |
-- +---------+---------+
-- 5 rows in set (0.00 sec)

-- 查看每个用户的拥有多少本书
SELECT user_id, COUNT(book_id) AS book_count FROM user_book 
GROUP BY user_id;
-- +---------+------------+
-- | user_id | book_count |
-- +---------+------------+
-- |       1 |          3 |
-- |       2 |          2 |
-- +---------+------------+
-- 2 rows in set (0.00 sec)

-- 查看拥有超过两本书的用户
-- 这里需要使用 HAVING，它和 WHERE 的作用类似，作用于分组后的数据
SELECT user_id, COUNT(book_id) AS book_count FROM user_book 
GROUP BY user_id HAVING book_count > 2;
-- +---------+------------+
-- | user_id | book_count |
-- +---------+------------+
-- |       1 |          3 |
-- +---------+------------+
-- 1 row in set (0.00 sec)

9 存储过程 PROCEDURE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 使用存储过程创建功能
DELIMITER //
CREATE PROCEDURE get_user_id_from_nick_name(IN nick_name VARCHAR(100))
BEGIN
    SELECT id FROM user WHERE nick = nick_name;
END
//
DELIMITER ;
-- Query OK, 0 rows affected (0.03 sec)

-- 调用存储过程，查找用户 mingming 的 user id
CALL get_user_id_from_nick_name("mingming");
-- +----+
-- | id |
-- +----+
-- |  1 |
-- +----+
-- 1 row in set (0.00 sec)

-- Query OK, 0 rows affected (0.00 sec)

-- 删除 存储过程
DROP PROCEDURE get_user_id_from_nick_name;
-- Query OK, 0 rows affected (0.02 sec)

10 联结查询 INNER/LEFT/RIGHT 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
48
49
50
51
52
53
54
55
-- 先插入一个新用户 huahua，他并没有图书
INSERT INTO user(nick)
VALUES('huahua');
-- Query OK, 1 row affected (0.01 sec)

-- 查看所有用户
SELECT * FROM user;
-- +----+----------+
-- | id | nick     |
-- +----+----------+
-- |  1 | mingming |
-- |  2 | xiaohong |
-- |  4 | huahua   |
-- +----+----------+
-- 3 rows in set (0.00 sec)

-- 使用 INNER JOIN
SELECT * FROM user u INNER JOIN user_book ub ON u.id = ub.user_id;
-- +----+----------+---------+---------+
-- | id | nick     | user_id | book_id |
-- +----+----------+---------+---------+
-- |  1 | mingming |       1 |       1 |
-- |  1 | mingming |       1 |       2 |
-- |  1 | mingming |       1 |       3 |
-- |  2 | xiaohong |       2 |       1 |
-- |  2 | xiaohong |       2 |       3 |
-- +----+----------+---------+---------+
5 rows in set (0.00 sec)

-- 使用 LEFT JOIN
SELECT * FROM user u LEFT JOIN user_book ub ON u.id = ub.user_id;
-- +----+----------+---------+---------+
-- | id | nick     | user_id | book_id |
-- +----+----------+---------+---------+
-- |  1 | mingming |       1 |       3 |
-- |  1 | mingming |       1 |       2 |
-- |  1 | mingming |       1 |       1 |
-- |  2 | xiaohong |       2 |       3 |
-- |  2 | xiaohong |       2 |       1 |
-- |  4 | huahua   |    NULL |    NULL |
-- +----+----------+---------+---------+
-- 6 rows in set (0.00 sec)

-- 使用 RIGHT JOIN
SELECT * FROM user u RIGHT JOIN user_book ub ON u.id = ub.user_id;
-- +------+----------+---------+---------+
-- | id   | nick     | user_id | book_id |
-- +------+----------+---------+---------+
-- |    1 | mingming |       1 |       1 |
-- |    1 | mingming |       1 |       2 |
-- |    1 | mingming |       1 |       3 |
-- |    2 | xiaohong |       2 |       1 |
-- |    2 | xiaohong |       2 |       3 |
-- +------+----------+---------+---------+
-- 5 rows in set (0.00 sec)

11 参考

简明 MySQL 教程

https://blog.ailln.com/v2ai/2019/12/31/database/6-mysql-tutorial/

作者

Ailln

发布于

2019-12-31

更新于

2024-03-02

许可协议

#

喜欢这篇文章？打赏一下作者吧

评论

关注我

分类

归档

最新文章

标签

Anaconda1
CUDA1
F1度量1
GPU1
Git3
Github1
Gitlab2
Hexo1
Jupyter1
LaTeX1
Linux3
MongoDB2
MySQL3
Neo4j1
NoSQL1
Node1
PyMongo1
PyTorch1
Python14
Redis1
Ruby1
SQL2
Supervisor1
TUNA1
TensorFlow3
Ubuntu3
Vim1
cn2an1
cuDNN1
dashboard1
demo1
docker2
elastic search1
frp1
helm1
http1
https1
jekyll1
jieba 分词1
k8s8
kubectl1
mysql1
pip3
pypi1
regex1
screen1
shell1
ssh2
timeit1
tmux1
tree1
zsh1
主题1
人工智能1
会议1
入门16
内网穿透1
写作1
函数命名1
分布表示1
分词3
加速1
匹配分词1
反向传播1
变量1
变量命名1
召回率1
回归1
图书2
复杂度1
女朋友1
好书推荐1
学习1
安全1
安装1
宝可梦1
导数1
小技巧1
并发1
并行1
序列1
序言1
异或问题1
微分1
性能度量1
持续部署1
持续集成1
排列1
排序1
教程1
数据库2
显卡1
智能1
机器学习5
机械分词1
树莓派2
校验码1
模块1
正则1
深度学习5
清单1
激活函数1
炼丹锦囊2
版本控制系统1
神器2
算法3
素数1
线程1
组合1
组成原理1
自然语言处理5
规范1
论文1
评估模型1
词向量2
词嵌入2
词袋模型1
质数1
资料5
资源1
距离1
软件工程1
进程1
进程管理1
进阶2
通俗易懂4
速查表1
集群1
面试1
×