MySQL快速入门

安装mysql

Ubuntu直接安装：
sudo apt-get install mysql-server mysql-client-8.0
# 在成功安装mysql后，可以直接使用root账户登录，注意这个账户是默认没有密码的。
# 因此为了数据库的安全，需要第一时间给root用户设置密码。
mysql> GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY "passwd";
Docker启动MySQL：
docker run -p 3306:3306 --name mysql --restart=always -v /root/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=passwd  -d mysql:8.0.24

链接到数据库

mysql -h 127.0.0.1 -u root -p
Enter password:   # 此处输入密码
# 数据库链接成功
mysql> 

# 退出
exit
quit

常见命令速记

# 数据库操作
show				# 显示
desc				# 数据表详情
use					# 选择数据库
drop				# 删除
create				# 创建

# 名称
database			# 数据库
table				# 数据表
databases			# 所有数据库
tables				# 所有数据表

# 数据表操作
select				# 查询
delete				# 删除
insert				# 插入
update				# 更新
where				# 查询条件
between				# 范围
limit				# 检索数量
offset				# 跳过数量
join				# 连接

# 逻辑操作
AND					# 和
OR					# 或
XOR					# 非

数据库操作

# 查看已有的数据库
mysql> show databases;

# 创建数据库
create DATABASE <databasename>;

# 进入数据库
use <databasename>;

# 删除数据库
drop DATABASE <databasename>;

数据表操作

创建数据表：
CREATE TABLE table_name (<字段描述>);
例如：
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;
具体操作应结合实际情况或使用第三方工具创建数据表
查看数据表字段信息：
desc <tablename>;

+-----------------+---------------+------+-----+---------+-------+
| Field           | Type          | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+-------+
| id              | varchar(20)   | NO   | PRI | NULL    |       |
| bid             | varchar(12)   | NO   |     | NULL    |       |
| user_id         | varchar(20)   | YES  |     | NULL    |       |
| screen_name     | varchar(30)   | YES  | MUL | NULL    |       |
| text            | varchar(2000) | YES  |     | NULL    |       |
| article_url     | varchar(100)  | YES  |     | NULL    |       |
| topics          | varchar(200)  | YES  | MUL | NULL    |       |
| at_users        | varchar(1000) | YES  |     | NULL    |       |
| pics            | varchar(3000) | YES  |     | NULL    |       |
| video_url       | varchar(1000) | YES  |     | NULL    |       |
| location        | varchar(100)  | YES  |     | NULL    |       |
| created_at      | datetime      | YES  |     | NULL    |       |
| source          | varchar(30)   | YES  |     | NULL    |       |
| attitudes_count | int           | YES  |     | NULL    |       |
| comments_count  | int           | YES  |     | NULL    |       |
| reposts_count   | int           | YES  |     | NULL    |       |
| retweet_id      | varchar(20)   | YES  |     | NULL    |       |
+-----------------+---------------+------+-----+---------+-------+
17 rows in set (0.01 sec)

# Field 		字段名称
# Type 			数据类型(最大长度)
	-- varchar	文本
	-- int		整数
	-- datetime	时间
				* 更多数据类型可查看下面表格
	
# Null			是否允许为空
# Key			数据表表约束
	-- PRI		主键
	-- MUL		可以重复
	-- UNI		唯一
	
# Default		默认值
# Extra 		拓展属性
删除数据表
DROP TABLE <tablename>;
数据表所支持的数据类型：
整数 含义（有符号）
tinyint(m) 1个字节 范围(-128~127)
smallint(m) 2个字节 范围(-32768~32767)
mediumint(m) 3个字节 范围(-8388608~8388607)
int(m) 4个字节 范围(-2147483648~2147483647)
bigint(m) 8个字节 范围(±9.22*10的18次方)
浮点 含义
float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位
double(m,d) 双精度浮点型 16位精度(8字节) m总个数，d小数位
字符串 含义
char(n) 固定长度，最多255个字符
varchar(n) 固定长度，最多65535个字符
tinytext 可变长度，最多255个字符
text 可变长度，最多65535个字符
mediumtext 可变长度，最多2的24次方-1个字符
longtext 可变长度，最多2的32次方-1个字符
时间 含义
date 日期 ‘2008-12-2’
time 时间 ‘12:25:36’
datetime 日期时间 ‘2008-12-2 22:06:44’
timestamp 自动存储记录修改时间
数据表约束类型：
约束 含义
NULL 数据列可包含NULL值
NOT NULL 数据列不允许包含NULL值
DEFAULT 默认值
PRIMARY KEY 主键
AUTO_INCREMENT 自动递增，适用于整数类型
UNSIGNED 无符号
CHARACTER SET name 指定一个字符集

数据操作

插入数据：
INSERT INTO table_name ( field1, field2,...fieldN )  # 字段
                       VALUES
                       ( value1, value2,...valueN ); # 值
查找数据：

数据库查询语句格式为：

SELECT <字段1>,<字段2> FROM <表名> [WHERE 条件] [LIMIT N] [OFFSET M];
# [LIMIT N] 	获取数量
# [OFFSET M] 	起始位置

例： * 注意表名要加点号

# 查找该表全部数据
SELECT * FROM `<tablename>`;

# 查找指定字段
SELECT <字段1>,<字段2> FROM `<tablename>`;

# 输出数据排序
# 升序
SELECT * FROM `<tablename>` ORDER BY `<tablename>`.`<字段>` ASC
# 降序
SELECT * FROM `<tablename>` ORDER BY `<tablename>`.`<字段>` DESC

# 获取指定数量的数据
SELECT <字段1>,<字段2> FROM `<tablename>` limit <数量>;
# 设定起始位置
SELECT <字段1>,<字段2> FROM `<tablename>` limit <数量> offset <数量>；

# 条件查询 * 注意字符串需要加引号
# 判断式查询 可用字符 = > < >= <= !=
SELECT * FROM `<tablename>` WHERE `<字段>` = '索尼';
# 模糊查询 配合%使用
SELECT * FROM `blog_article` WHERE `title` LIKE '如何%' ORDER BY `title` DESC
SELECT * FROM `blog_article` WHERE `title` LIKE '%合集' ORDER BY `title` DESC
SELECT * FROM `blog_article` WHERE `title` LIKE '%py%' ORDER BY `title` DESC
# 分别表示 以什么开头 以什么结尾 包含什么

# 多条件查询 多条件用连接符连接
SELECT * FROM `blog_article` WHERE `title` LIKE '%py%' AND `body` LIKE '%flask%';
# AND=和 | OR=或 | XOR=非

# 范围查询 显示 views 1000到1200之间的数据
SELECT * FROM `<tablename>` WHERE `views` BETWEEN 1000 AND 1200
更新数据：

使用 UPDATE 语句修改单个表，语法格式为：

UPDATE <tablename> SET 字段 1=值 1 [,字段 2=值 2… ] [WHERE 子句 ] [ORDER BY 子句] [LIMIT 子句]

语法说明如下：

  • <表名>：用于指定要更新的表名称。
  • SET 子句：用于指定表中要修改的列名及其列值。其中，每个指定的列值可以是表达式，也可以是该列对应的默认值。如果指定的是默认值，可用关键字 DEFAULT 表示列值。
  • WHERE 子句：可选项。用于限定表中要修改的行。若不指定，则修改表中所有的行。
  • ORDER BY 子句：可选项。用于限定表中的行被修改的次序。
  • LIMIT 子句：可选项。用于限定被修改的行数。

注意：修改一行数据的多个列值时，SET 子句的每个值用逗号分开即可。

例：

UPDATE `blog_article` SET `views` = '999' WHERE `blog_article`.`id` = 50
删除数据：

使用 DELETE 语句从单个表中删除数据，语法格式为：

DELETE FROM <tablename> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]

语法说明如下：

  • <表名>：指定要删除数据的表名。
  • ORDER BY 子句：可选项。表示删除时，表中各行将按照子句中指定的顺序进行删除。
  • WHERE 子句：可选项。表示为删除操作限定删除条件，若省略该子句，则代表删除该表中的所有行。
  • LIMIT 子句：可选项。用于告知服务器在控制命令被返回到客户端前被删除行的最大值。

注意：在不使用 WHERE 条件的时候，将删除所有数据。

【实例 1】删除 tb_courses_new 表中的全部数据，输入的 SQL 语句和执行结果如下所示。

mysql> DELETE FROM tb_courses_new;
Query OK, 3 rows affected (0.12 sec)
mysql> SELECT * FROM tb_courses_new;
Empty set (0.00 sec)

【实例 2】指定条件删除

# 注意条件字段不要打符号，值要打引号
mysql> DELETE FROM `image` WHERE pic_id = '0016aXrply1guyxgkhwt6j635t4qmb2c02';
Query OK, 1 row affected (0.01 sec)

mysql> DELETE FROM `image` WHERE pic_id like '%0009%';
Query OK, 97 rows affected (1.49 sec)

*对于有外键引用的数据行，要先删除外键引用，再删除该数据。

高级查询

交叉连接（CROSS JOIN）一般用来返回连接表的笛卡尔积。

本节的末尾介绍了笛卡尔积，不了解笛卡尔积的读者可以先阅读文章末尾部分，然后再继续学习交叉连接。

交叉连接的语法格式如下：

SELECT <字段名> FROM <表1> CROSS JOIN <表2> [WHERE子句]

或

SELECT <字段名> FROM <表1>, <表2> [WHERE子句]

语法说明如下：

  • 字段名：需要查询的字段名称。
  • <表1><表2>：需要交叉连接的表名。
  • WHERE 子句：用来设置交叉连接的查询条件。

注意：多个表交叉连接时，在 FROM 后连续使用 CROSS JOIN 或,即可。以上两种语法的返回结果是相同的，但是第一种语法才是官方建议的标准写法。

当连接的表之间没有关系时，我们会省略掉 WHERE 子句，这时返回结果就是两个表的笛卡尔积，返回结果数量就是两个表的数据行相乘。需要注意的是，如果每个表有 1000 行，那么返回结果的数量就有 1000×1000 = 1000000 行，数据量是非常巨大的。

交叉连接可以查询两个或两个以上的表，为了让读者更好的理解，下面先讲解两个表的交叉连接查询。

例 1

查询学生信息表和科目信息表，并得到一个笛卡尔积。

为了方便观察学生信息表和科目表交叉连接后的运行结果，我们先分别查询出这两个表的数据，再进行交叉连接查询。

1）查询 tb_students_info 表中的数据，SQL 语句和运行结果如下：

mysql> SELECT * FROM tb_students_info;
+----+--------+------+------+--------+-----------+
| id | name   | age  | sex  | height | course_id |
+----+--------+------+------+--------+-----------+
|  1 | Dany   |   25 | 男   |    160 |         1 |
|  2 | Green  |   23 | 男   |    158 |         2 |
|  3 | Henry  |   23 | 女   |    185 |         1 |
|  4 | Jane   |   22 | 男   |    162 |         3 |
|  5 | Jim    |   24 | 女   |    175 |         2 |
|  6 | John   |   21 | 女   |    172 |         4 |
|  7 | Lily   |   22 | 男   |    165 |         4 |
|  8 | Susan  |   23 | 男   |    170 |         5 |
|  9 | Thomas |   22 | 女   |    178 |         5 |
| 10 | Tom    |   23 | 女   |    165 |         5 |
+----+--------+------+------+--------+-----------+
10 rows in set (0.00 sec)

2）查询 tb_course 表中的数据，SQL 语句和运行结果如下：

mysql> SELECT * FROM tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | Java        |
|  2 | MySQL       |
|  3 | Python      |
|  4 | Go          |
|  5 | C++         |
+----+-------------+
5 rows in set (0.00 sec)

3）使用 CROSS JOIN 查询出两张表中的笛卡尔积，SQL 语句和运行结果如下：

mysql> SELECT * FROM tb_course CROSS JOIN tb_students_info;
+----+-------------+----+--------+------+------+--------+-----------+
| id | course_name | id | name   | age  | sex  | height | course_id |
+----+-------------+----+--------+------+------+--------+-----------+
|  1 | Java        |  1 | Dany   |   25 | 男   |    160 |         1 |
|  2 | MySQL       |  1 | Dany   |   25 | 男   |    160 |         1 |
|  3 | Python      |  1 | Dany   |   25 | 男   |    160 |         1 |
|  4 | Go          |  1 | Dany   |   25 | 男   |    160 |         1 |
|  5 | C++         |  1 | Dany   |   25 | 男   |    160 |         1 |
|  1 | Java        |  2 | Green  |   23 | 男   |    158 |         2 |
|  2 | MySQL       |  2 | Green  |   23 | 男   |    158 |         2 |
|  3 | Python      |  2 | Green  |   23 | 男   |    158 |         2 |
|  4 | Go          |  2 | Green  |   23 | 男   |    158 |         2 |
|  5 | C++         |  2 | Green  |   23 | 男   |    158 |         2 |
|  1 | Java        |  3 | Henry  |   23 | 女   |    185 |         1 |
|  2 | MySQL       |  3 | Henry  |   23 | 女   |    185 |         1 |
|  3 | Python      |  3 | Henry  |   23 | 女   |    185 |         1 |
|  4 | Go          |  3 | Henry  |   23 | 女   |    185 |         1 |
|  5 | C++         |  3 | Henry  |   23 | 女   |    185 |         1 |
|  1 | Java        |  4 | Jane   |   22 | 男   |    162 |         3 |
|  2 | MySQL       |  4 | Jane   |   22 | 男   |    162 |         3 |
|  3 | Python      |  4 | Jane   |   22 | 男   |    162 |         3 |
|  4 | Go          |  4 | Jane   |   22 | 男   |    162 |         3 |
|  5 | C++         |  4 | Jane   |   22 | 男   |    162 |         3 |
|  1 | Java        |  5 | Jim    |   24 | 女   |    175 |         2 |
|  2 | MySQL       |  5 | Jim    |   24 | 女   |    175 |         2 |
|  3 | Python      |  5 | Jim    |   24 | 女   |    175 |         2 |
|  4 | Go          |  5 | Jim    |   24 | 女   |    175 |         2 |
|  5 | C++         |  5 | Jim    |   24 | 女   |    175 |         2 |
|  1 | Java        |  6 | John   |   21 | 女   |    172 |         4 |
|  2 | MySQL       |  6 | John   |   21 | 女   |    172 |         4 |
|  3 | Python      |  6 | John   |   21 | 女   |    172 |         4 |
|  4 | Go          |  6 | John   |   21 | 女   |    172 |         4 |
|  5 | C++         |  6 | John   |   21 | 女   |    172 |         4 |
|  1 | Java        |  7 | Lily   |   22 | 男   |    165 |         4 |
|  2 | MySQL       |  7 | Lily   |   22 | 男   |    165 |         4 |
|  3 | Python      |  7 | Lily   |   22 | 男   |    165 |         4 |
|  4 | Go          |  7 | Lily   |   22 | 男   |    165 |         4 |
|  5 | C++         |  7 | Lily   |   22 | 男   |    165 |         4 |
|  1 | Java        |  8 | Susan  |   23 | 男   |    170 |         5 |
|  2 | MySQL       |  8 | Susan  |   23 | 男   |    170 |         5 |
|  3 | Python      |  8 | Susan  |   23 | 男   |    170 |         5 |
|  4 | Go          |  8 | Susan  |   23 | 男   |    170 |         5 |
|  5 | C++         |  8 | Susan  |   23 | 男   |    170 |         5 |
|  1 | Java        |  9 | Thomas |   22 | 女   |    178 |         5 |
|  2 | MySQL       |  9 | Thomas |   22 | 女   |    178 |         5 |
|  3 | Python      |  9 | Thomas |   22 | 女   |    178 |         5 |
|  4 | Go          |  9 | Thomas |   22 | 女   |    178 |         5 |
|  5 | C++         |  9 | Thomas |   22 | 女   |    178 |         5 |
|  1 | Java        | 10 | Tom    |   23 | 女   |    165 |         5 |
|  2 | MySQL       | 10 | Tom    |   23 | 女   |    165 |         5 |
|  3 | Python      | 10 | Tom    |   23 | 女   |    165 |         5 |
|  4 | Go          | 10 | Tom    |   23 | 女   |    165 |         5 |
|  5 | C++         | 10 | Tom    |   23 | 女   |    165 |         5 |
+----+-------------+----+--------+------+------+--------+-----------+
50 rows in set (0.00 sec)

由运行结果可以看出，tb_course 和 tb_students_info 表交叉连接查询后，返回了 50 条记录。可以想象，当表中的数据较多时，得到的运行结果会非常长，而且得到的运行结果也没太大的意义。所以，通过交叉连接的方式进行多表查询的这种方法并不常用，我们应该尽量避免这种查询。

例 2

查询 tb_course 表中的 id 字段和 tb_students_info 表中的 course_id 字段相等的内容， SQL 语句和运行结果如下：

mysql> SELECT * FROM tb_course CROSS JOIN tb_students_info 
    -> WHERE tb_students_info.course_id = tb_course.id;
+----+-------------+----+--------+------+------+--------+-----------+
| id | course_name | id | name   | age  | sex  | height | course_id |
+----+-------------+----+--------+------+------+--------+-----------+
|  1 | Java        |  1 | Dany   |   25 | 男   |    160 |         1 |
|  2 | MySQL       |  2 | Green  |   23 | 男   |    158 |         2 |
|  1 | Java        |  3 | Henry  |   23 | 女   |    185 |         1 |
|  3 | Python      |  4 | Jane   |   22 | 男   |    162 |         3 |
|  2 | MySQL       |  5 | Jim    |   24 | 女   |    175 |         2 |
|  4 | Go          |  6 | John   |   21 | 女   |    172 |         4 |
|  4 | Go          |  7 | Lily   |   22 | 男   |    165 |         4 |
|  5 | C++         |  8 | Susan  |   23 | 男   |    170 |         5 |
|  5 | C++         |  9 | Thomas |   22 | 女   |    178 |         5 |
|  5 | C++         | 10 | Tom    |   23 | 女   |    165 |         5 |
+----+-------------+----+--------+------+------+--------+-----------+
10 rows in set (0.01 sec)

如果在交叉连接时使用 WHERE 子句，MySQL 会先生成两个表的笛卡尔积，然后再选择满足 WHERE 条件的记录。因此，表的数量较多时，交叉连接会非常非常慢。一般情况下不建议使用交叉连接。

子查询在 WHERE 中的语法格式如下：

WHERE <表达式> <操作符> (子查询)

其中，操作符可以是比较运算符和 IN、NOT IN、EXISTS、NOT EXISTS 等关键字。

1）IN | NOT IN

当表达式与子查询返回的结果集中的某个值相等时，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回值正好相反。

2）EXISTS | NOT EXISTS

用于判断子查询的结果集是否为空，若子查询的结果集不为空，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回的值正好相反。

例 1

使用子查询在 tb_students_info 表和 tb_course 表中查询学习 Java 课程的学生姓名，SQL 语句和运行结果如下。

mysql> SELECT name FROM tb_students_info 
    -> WHERE course_id IN (SELECT id FROM tb_course WHERE course_name = 'Java');
+-------+
| name  |
+-------+
| Dany  |
| Henry |
+-------+
2 rows in set (0.01 sec)

结果显示，学习 Java 课程的只有 Dany 和 Henry。上述查询过程也可以分为以下 2 步执行，实现效果是相同的。