【笔记】MySQL学习笔记

发表于 更新于 阅读次数：

前言

MySQL（官方发音为/maɪ ˌɛskjuːˈɛl/“My S-Q-L”，但也经常被读作/maɪ ˈsiːkwəl/“My Sequel”）原本是一个开放源码的关系数据库管理系统，原开发者为瑞典的MySQL AB公司，该公司于2008年被昇阳微系统（Sun Microsystems）收购。2009年，甲骨文公司（Oracle）收购昇阳微系统公司，MySQL成为Oracle旗下产品。（维基百科）

注释

1
-- 单行注释
1
# 单行注释
1
/* 多行注释 */

特殊注释

  • 在MySQL 5.5.0+会被执行，其他关系型数据库会当作普通注释忽略
1
/*! SQL语句 */

帮助

查看所有帮助

1
HELP contents;
1
? contents;

查看所有函数帮助

1
HELP functions;

查看指定内容的帮助

<msg>：指定内容

1
HELP <msg>;

查看数据库状态

1
STATUS

数据类型

数值类型

数据类型 备注
TINYINT 8位有符号整型
SMALLINT 16位有符号整型
MEDIUMINT 24位有符号整型
INTEGER、INT 32位有符号整型
BIGINT 64位有符号整型
FLOAT 32位有符号近似浮点型
DOUBLE 64位有符号近似浮点型
DECIMAL(10,0)、NUMERIC(10,0) 有符号精确数值型，总有效数字最大65，默认值为10，小数位数最大30，默认值为0

字符串

数据类型 备注
CHAT(255) 定长字符串，数据长度不足会自动在末尾补空格，数据类型定义范围为[CHAT(0),CHAT(255)]
VARCHAR(65535) 可变长字符串，数据类型定义范围为[VARCHAR(0),VARCHAR(65535)]
TEXT 超大可变长字符串
BINARY(255) 定长二进制字符串，数据长度不足会自动在末尾补0x00，数据类型定义范围为[BINARY(0),BINARY(255)]
VARBINARY(65535) 可变长二进制字符串，数据类型定义范围为[VARBINARY(0),VARBINARY(65535)]
BLOB 超大可变长二进制字符串

日期类型

数据类型 备注
YEAR 格式为yyyy，范围为[‘1901’,’2155’]
DATE 格式为yyyy-MM-dd，范围为[‘1000-01-01’,’9999-12-31’]
DATETIME 格式为yyyy-MM-dd HH:mm:ss，范围为[‘1000-01-01 00:00:00.000000’,’9999-12-31 23:59:59.999999’]
TIMESTAMP 格式为yyyy-MM-dd HH:mm:ss，范围为[‘1970-01-01 00:00:00.000000’,’2038-01-19 03:14:07.999999’]

对数据库的操作（Data Definition Language, DDL）

查询所有数据库

1
SHOW DATABASES;

创建数据库

1
CREATE DATABASE <database_name>;

同时指定字符集

1
CREATE DATABASE <database_name> CHARSET utf8mb4;

同时指定数据库和数据表的字符集

1
CREATE DATABASE <database_name> DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

如果不存在

1
CREATE DATABASE IF NOT EXISTS <database_name>;

查看创建数据库语句

1
SHOW CREATE DATABASE <database_name>;

进入数据库

1
USE <database_name>;

查看当前进入的数据库

1
SELECT database();

删除数据库

1
DROP DATABASE <database_name>;

如果存在

1
DROP DATABASE IF EXISTS <database_name>;

修改数据库的默认字符集

1
ALTER DATABASE <database_name> DEFAULT CHARACTER SET <character_set>;

修改默认校对规则

1
ALTER DATABASE <database_name> DEFAULT COLLATE <proofreading_rules>;

对数据表的操作（Data Definition Language, DDL）

创建数据表

<table_name>：数据表名，通过添加``可以避免与关键字重复
<field_name>：字段名，通过添加``可以避免与关键字重复
<type_of_data>：数据类型
<length>：长度（根据需求添加）
<constraint>：约束（根据需求添加）

1
2
3
4
CREATE TABLE <table_name>(
    <field_name_1> <type_of_data>(<length>) <constraint>,
    <field_name_2> <type_of_data>(<length>) <constraint>
);

如果不存在

1
2
3
4
CREATE TABLE IF NOT EXISTS <table_name>(
    <field_name_1> <type_of_data>(<length>) <constraint>,
    <field_name_2> <type_of_data>(<length>) <constraint>
);

查询所有数据表

1
SHOW TABLES;

查询数据表结构

1
DESC <table_name>;

查询数据表结构（详细）

1
SHOW CREATE TABLE <table_name>;

修改数据表名

1
ALTER TABLE <old_table_name> RENAME TO <new_table_name>;

删除数据表

1
DROP TABLE <table_name>;

删除指定数据表的所有数据

1
TRUNCATE TABLE <table_name>;

复制表

  • 复制一张副本表

只复制表结构

1
CREATE <new_table_name> like <old_table_name>;

复制表结构和所有数据

1
CREATE TABLE <new_table_name> AS (SELECT * FROM <old_table_name>);

对字段的操作（Data Definition Language, DDL）

增加字段

1
ALTER TABLE <table_name> ADD <new_field_name> <new_type_of_data>(<length>) <constraint>;
1
ALTER TABLE <table_name> ADD <new_field_name> <new_type_of_data>(<length>) <constraint> first;
1
ALTER TABLE <table_name> ADD <new_field_name> <new_type_of_data>(<length>) <constraint> after <old_field_name>;

删除字段

1
ALTER TABLE <table_name> DROP COLUMN <field_name>;

修改数据类型

1
ALTER TABLE <table_name> MODIFY <field_name> <new_type_of_data>;

修改字段

1
ALTER TABLE <table_name> CHANGE <old_field_name> <new_field_name> <new_type_of_data>(<new_length>) <new_constraint>;

修改字段名

1
ALTER TABLE <table_name> CHANGE <old_field_name> <new_field_name> <new_type_of_data>;

修改字段排列位置

  • 将指定字段放到第一个位置
1
ALTER TABLE <table_name> MODIFY <field_name_1> <type_of_data_1> FIRST;
  • 将指定字段放到指定字段后一个位置
1
ALTER TABLE <table_name> MODIFY <field_name_1> <type_of_data_1> AFTER <field_name_2>;

对数据的操作（Data Manipulation Language, DML）

插入数据

1
INSERT INTO <table_name>(<field_name_1>, <field_name_2>) VALUES(<value_1>, <value_2>);
1
INSERT INTO <table_name> SET <field_name_1>=<value_1>, <field_name_2>=<value_2>;

插入数据（多条）

1
INSERT INTO <table_name>(<field_name_1>, <field_name_2>) VALUES(value_1_1, value_1_2),(value_2_1, value_2_2);

插入数据（所有字段）

1
INSERT INTO <table_name> VALUES(<value_1>, <value_2>);

修改数据

1
UPDATE <table_name> SET <field_name_1>=<value_1>, <field_name_2>=<value_2> WHERE <field_name><operator><value>;

覆盖插入

  • 删除原有数据创建新数据
  • REPLACE依赖主键约束，没有主键约束等同于INSTERT
1
REPLACE INTO <table_name> (<field_name_1>,<field_name_2>) VALUES(<value_1>,<value_2>);
1
REPLACE INTO <table_name> SET <field_name_1>=<value_1>, <field_name_2>=<value_2>;

删除所有数据

1
DELETE FROM <table_name>;

删除指定数据

1
DELETE FROM <table_name> WHERE <field_name><operator><value>;

对数据的操作（Data Query Language, DQL）

查询字段

查询所有字段的数据

1
SELECT * FROM <table_name>;

查询部分字段的数据

1
SELECT <field_name_1>,<field_name> FROM <table_name>;
为查询到的数据定义表头别名
1
SELECT <old_field_name_1> AS <new_field_name_1> FROM <table_name>;
  • 简写
1
SELECT <old_field_name_1> <new_field_name_1> FROM <table_name>;
表名也可以设置别名
  • 但是指定表名别名后，后面只能使用别名，不能再使用原表名
1
SELECT * FROM <old_table_name> AS <old_table_name>;
  • 简写
1
SELECT * FROM <old_table_name> <old_table_name>;

查询结果

查询运算符计算后的结果

<operator>：这里的运算符指的是+、-、*、/、%

1
SELECT <field_name><operator><value> FROM <table_name>;

查询聚合函数计算后的结果

  • 不能用在WHERE子句中
  • 会默认直接丢弃NULL值
聚合函数 备注
SUM() 求和
AVG() 求平均值
MAX() 求最大值
MIN() 求最小值
COUNT() 统计数据总数

<function>：聚合函数函数名

1
SELECT <function>(<field_name>) FROM <table_name>;
  • 统计所有数据总数
1
SELECT COUNT(*) FROM <table_name>;

查询结果去重

1
SELECT DISTINCT <field_name> FROM <table_name>;

条件查询

运算符

比较运算
  • 比较两个表达式的大小

=、<、>、<=、>=、<>、!=、!<、!>

多重条件
  • 对两个表达式进行与或非的运算

AND、OR、NOT

确定范围
  • 判断值是否在指定的范围内

BETWEEN...AND...、NOT BETWEEN...AND...

确定集合
  • 查询值是否属于列表值之一

IN、NOT IN

字符匹配
  • 用于模糊查询

LIKE、NOT LIKE

  • 配合通配符使用

%：表示0或多个任意的字符
_：表示1个任意的字符

测试空值
  • 判断值是否为空

IS NULL、IS NOT NULL

空值替换
  • 一些数据存在NULL值时，参与运算结果为NULL，不准确，为了解决问题，将NULL值替换为0

<field_name>：可能会出现NULL值的字段

1
SELECT IFNULL(<field_name>, 0) FROM <table_name>;

按条件查询部分数据

<operator>：运算符

1
SELECT * FROM <table_name> WHERE <field_name><operator><value>;

按条件替换查询的数据

1
SELECT CASE WHEN <field_name><operator><old_value> THEN <new_value> END FROM <table_name>;
1
SELECT CASE WHEN <field_name><operator><old_value> THEN <new_value> ELSE <default_value> END FROM <table_name>;

分组查询

  • 将查询到的数据根据字段进行分组
1
SELECT <field_name> FROM <table_name> GROUP BY <field_name>;

筛选分组后的数据

1
SELECT <field_name> FROM <table_name> GROUP BY <field_name> HAVING <field_name><operator><value>;

WHERE和HAVING的区别

WHERE是在分组之前,对记录进行筛选过滤，HAVING是在分组之后,对记录进行筛选过滤
WHERE子句中不能使用多行函数以及列别名，HAVING子句中可以使用多行函数以及列别名、表别名

查询结果排序

  • 默认为升序

升序（从小到大）

1
SELECT <field_name> FROM <table_name> ORDER BY <field_name>;
1
SELECT <field_name> FROM <table_name> ORDER BY <field_name> ASC;

降序（从大到小）

1
SELECT <field_name> FROM <table_name> ORDER BY <field_name> DESC;

多重排序

1
SELECT <field_name> FROM <table_name> ORDER BY <field_name_1> ASC, <field_name_2> DESC;

多表查询（连接查询）

  • 可以两个表，也可以多个表

内连接

显式内连接
1
SELECT <table_name_x>.<field_name_x> FROM <table_name_2> INNER JOIN <table_name_1> ON <table_name_2>.<field_name_1>=<table_name_1>.<field_name_1> JOIN <table_name_3> ON <table_name_2>.<field_name_2>=<table_name_3>.<field_name_2>;
省略INNER关键字
1
SELECT <table_name_x>.<field_name_x> FROM <table_name_2> JOIN <table_name_1> ON <table_name_2>.<field_name_1>=<table_name_1>.<field_name_1> JOIN <table_name_3> ON <table_name_2>.<field_name_2>=<table_name_3>.<field_name_2>;
隐式内连接
1
SELECT <table_name_x>.<field_name_x> FROM <table_name_1>,<table_name_2>,<table_name_3> WHERE <table_name_1>.<field_name_1>=<table_name_2>.<field_name_1> AND <table_name_2>.<field_name_2>=<table_name_3>.<field_name_2>;

外连接

左外连接查询
  • 左侧显示所有数据，右侧如果没有对应关系则显示为null
1
SELECT * FROM <table_name_1> LEFT JOIN <table_name_2> ON <table_name_1>.<field_name_2>=<table_name_2>.<field_name_1>;
右外连接查询
  • 右侧显示所有数据，左侧如果没有对应关系则显示为null
1
SELECT * FROM <table_name_1> RIGHT JOIN <table_name_2> ON <table_name_1>.<field_name_2>=<table_name_2>.<field_name_1>;

（模拟）全外连接查询

去重（取交集）
  • 通过UNION关键字将左外连接和右外连接取交集，模拟全外连接查询
  • 两表展示出的列数必须相同
  • 两表展示的表头名如果不同，可以用AS关键字为表头添加别名
1
2
3
SELECT id,name FROM <table_name_1> LEFT JOIN <table_name_2> ON <table_name_1>.<field_name_2>=<table_name_2>.<field_name_1>
UNION   
SELECT id,name FROM <table_name_1> RIGHT JOIN <table_name_2> ON <table_name_1>.<field_name_2>=<table_name_2>.<field_name_1>;
不去重（取并集）
  • 通过UNION ALL关键字将左外连接和右外连接取并集，模拟全外连接查询
1
2
3
SELECT id,name FROM <table_name_1> LEFT JOIN <table_name_2> ON <table_name_1>.<field_name_2>=<table_name_2>.<field_name_1>
UNION ALL
SELECT id,name FROM <table_name_1> RIGHT JOIN <table_name_2> ON <table_name_1>.<field_name_2>=<table_name_2>.<field_name_1>;

分页查询

<limit>：限制，每页显示的数据总数
<offset>：偏移，每页的第一条数据在总数据中的下标（下标从0开始），所以公式为(当前页码数-1)*每页显示的数据总数

1
SELECT <field_name> FROM <table_name> LIMIT <limit> OFFSET <offset>;
1
SELECT <field_name> FROM <table_name> LIMIT <offset>,<limit>;

子查询

  • 将一个sql语句查询的结果作为条件完成另一个sql语句查询

子查询结果是一行一列

  • 直接用做判断

<select>：子查询的SQL语句

1
SELECT * FROM <table_name> WHERE <field_name><operator>(<select>);

子查询的结果是多行一列

  • 可以采用IN关键字
1
SELECT * FROM <table_name> WHERE <field_name> IN (<select>);

子查询结果是多行多列

  • 可以将子查询语句作为虚拟表放到WHERE关键字后
1
SELECT * FROM <table_name_1>,(<select>) AS <table_name_2> WHERE <field_name><operator><field_value>;

对视图的操作（Data Definition Language, DDL）

  • 将特定条件下的查询结果创建为视图，可以通过对视图的操作简便查询

创建视图

<select>：select语句

1
CREATE VIEW <view_name> AS <select>;

重建视图

1
ALTER VIEW <view_name> AS <select>;

创建视图（强制）

1
CREATE OR REPLACE VIEW <view_name> AS <select>;

查询所有视图

1
SHOW TABLES;

查询视图结构

1
DESC <view_name>;

修改视图

1
ALTER VIEW <view_name> AS <select>;

删除视图

1
DROP VIEW <view_name>;

查询视图中的数据

1
SELECT * FROM <view_name>;
1
SELECT <field_name> FROM <view_name>;

插入数据

1
INSERT INTO <view_name> VALUES(<value_1>, <value_2>);

修改数据

1
UPDATE <view_name> SET <field_name>=<value>;

删除数据

1
DELETE FROM <view_name> WHERE <field_name>=<value>;

对索引的操作（Data Definition Language, DDL）

查看所有索引

1
SHOW INDEX FROM <table_name>;

创建索引

1
CREATE INDEX <index_name> ON <table_name>(<field_name>);
1
ALTER TABLE <table_name> ADD INDEX <index_name>(<field_name_1>, <field_name_2>);
1
CREATE TABLE <table_name>(<field_name> <type_of_data>(<length>), INDEX <index_name>(field_name));

删除索引

1
DROP INDEX <index_name> ON <table_name>;
1
ALTER TABLE <table_name> DROP INDEX <index_name>;

对约束的操作（Data Definition Language, DDL）

添加约束

  • 在建表时直接加约束到字段定义的后面

<constraint>：约束（同时指定多个约束时，用空格分隔）

NULL：可以为空，缺省值
NOT NULL：非空约束，定义这个字段不允许为空
PRIMARY KEY：主键约束，必须为非空，必须不重复

AUTO_INCREMENT：如果是数值型主键，可以定义逐渐自增
UNIQUE：唯一约束，定义这个列的值必须是唯一的、不能重复的，但可以为空
DEFAULT 默认值：默认约束，当没有为字段赋值时的默认值
ON UPDATE 值：没当执行修改操作时，字段的值会更新为指定值
AUTO_INCREMENT：自增
COMMENT ''：注释

1
CREATE TABLE <table_name>(<field_name> <type_of_data>(<length>) <constraint>);
  • 在建表时定义约束在所有字段定义的后面

<constraint>：约束（同时指定多个约束时，每行一种约束的定义）

PRIMARY(<field_name_1>)：定义主键约束，必须为非空，必须不重复
PRIMARY(<field_name_1>, <field_name_2>)：定义联合主键约束，每个主键字段的数值可以重复，但是多个联合主键的数值的并集不可以重复
UNIQUE(<field_name>)：定义唯一约束，定义这个列的值必须是唯一的、不能重复的，但可以为空
CHECK(<field_name><operator><value>)：定义完整性约束，为字段定义范围，仅8.x可以使用

1
2
3
4
5
CREATE TABLE <table_name>(
    <field_name_1> <operator>(length),
    <field_name_2> <operator>(length),
    <constraint>
);

修改或删除约束

<constraint>：修改后的约束（同时指定多个约束时，用空格分隔），如果修改后不添加约束，相当于删除已有约束

1
ALTER TABLE <table_name> MODIFY <field_name> <operator>(length) <constraint>;
1
ALTER TABLE <table_name> MODIFY <field_name> <operator>(length);

外键约束

  • 通过外键约束将表与表建立联系，当出现有损表的完整性操作时，外键约束会对表进行保护

一对多

创建表时直接添加外键
1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE 职位表(
    职位编号 <type_of_data>,
    职位名称 <type_of_data>
);

CREATE TABLE 员工表(
    员工编号 <type_of_data>,
    员工名称 <type_of_data>,
    员工表_职位编号 <type_of_data>,
    FOREIGN KEY(员工表_职位编号) REFERENCES 职位表(职位编号)
);
对已创建的表添加外键
1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE 职位表(
    职位编号 <type_of_data>,
    职位名称 <type_of_data>
);

CREATE TABLE 员工表(
    员工编号 <type_of_data>,
    员工名称 <type_of_data>,
    员工表_职位编号 <type_of_data>
);

ALTER TABLE 员工表 ADD CONSTRAINT 外键名 FOREIGN KEY(员工表_职位编号) REFERENCES 职位表(职位编号);
  • 可以省略外键名
1
ALTER TABLE 员工表 ADD CONSTRAINT FOREIGN KEY(员工表_职位编号) REFERENCES 职位表(职位编号);
删除外键
1
ALTER TABLE 员工表 DROP FOREIGN KEY 外键名;

多对多

创建表时直接添加外键
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE TABLE 职位表(
    职位编号 <type_of_data>,
    职位名称 <type_of_data>
);

CREATE TABLE 等级表(
    等级编号 <type_of_data>,
    等级名称 <type_of_data>
);

CREATE TABLE 员工表(
    员工编号 <type_of_data>,
    员工名称 <type_of_data>,
    员工表_职位编号 <type_of_data>,
    员工表_等级编号 <type_of_data>,
    FOREIGN KEY(员工表_职位编号) REFERENCES 职位表(职位编号),
    FOREIGN KEY(员工表_等级编号) REFERENCES 等级表(等级编号)
);

内置函数

时间函数

函数 备注
CURDATE() 获取当前日期，格式为yyyy-MM-dd
CURTIME() 获取当前时间，格式为HH:mm:ss
SYSDATE()、CURRENT_TIMESTAMP() 获取当前日期和时间，格式为HH:mm:ss
YEAR('') 获取年，格式为yyyy
MONTH('') 获取月，格式为M
DAY('') 获取日，格式为d
HOUR('') 获取小时，格式为H
MINUTE('') 获取分钟，格式为m
SECOND('') 获取秒，格式为s

完成

参考文献

博客园——Bigben
博客园——下页、再停留
CSDN——时光·漫步zth
哔哩哔哩——黑马程序员
哔哩哔哩——黑马程序员
CSDN——念去去千里烟波
CSDN——辛丑年正月十五