【笔记】Mysql 学习笔记

发表于 更新于 阅读次数：

前言

MySQL原本是一个开放源码的关系数据库管理系统，原开发者为瑞典的MySQL AB公司，该公司于2008年被昇阳微系统（Sun Microsystems）收购。2009年，甲骨文公司（Oracle）收购昇阳微系统公司，MySQL成为Oracle旗下产品。（维基百科）

注释

单行注释

1
2
3
4
-- 注释内容
# 注释内容
/* 注释内容 */
/*! 注释内容 */

多行注释

1
/* 注释内容 */

帮助

查看所有帮助

1
2
3
mysql> help contents;

mysql> ? contents;

查看所有函数帮助

1
mysql> help functions;

查看指定内容的帮助

<msg>：指定内容

1
mysql> help <msg>;

查看数据库状态

1
mysql> STATUS

对数据库的操作

查询所有数据库

1
SHOW DATABASES;

创建数据库

1
CREATE DATABASE <database_name>;

同时指定字符集

1
CREATE DATABASE <database_name> CHARSET utf8;

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

utf8
1
CREATE DATABASE <database_name> DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
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>;

对数据表的操作

创建数据表

<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
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
CREATE <new_table_name> like <old_table_name>;

复制表结构和所有数据

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

对字段的操作

增加字段

1
2
3
4
5
ALTER TABLE <table_name> ADD <new_filed_name> <new_type_of_data>(<length>) <constraint>;

ALTER TABLE <table_name> ADD <new_filed_name> <new_type_of_data>(<length>) <constraint> first;

ALTER TABLE <table_name> ADD <new_filed_name> <new_type_of_data>(<length>) <constraint> after <old_field_name>;

删除字段

1
ALTER TABLE <table_name> DROP COLUMN <filed_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
2
3
ALTER TABLE <table_name> MODIFY <field_name_1> <type_of_data_1> first <field_name_2>;

ALTER TABLE <table_name> MODIFY <field_name_2> <type_of_data_1> after <field_name_2>;

对数据的操作

插入数据

1
2
3
INSERT INTO <table_name>(<field_name_1>, <field_name_2>) VALUES(<value_1>, <value_2>);

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>;

覆盖插入

  • 删除原有数据创建新数据
1
2
3
REPLACE INTO <table_name> (<field_name_1>,<field_name_2>) VALUES(<value_1>,<value_2>);

REPLACE INTO <table_name> SET <field_name_1>=<value_1>, <field_name_2>=<value_2>;

删除所有数据

1
2
3
DELETE FROM <table_name>;

TRUNCATE TABLE <table_name>;

删除指定数据

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

查询所有数据

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
2
3
SELECT * FROM <old_table_name> AS <old_table_name>;

SELECT * FROM <old_table_name> <old_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>
查询重新计算后的数据

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

1
SELECT <field_name><operator> FROM <table_name>
不显示重复结果
1
SELECT DISTINCT <field_name> FROM <table_name>

聚合函数（多行函数）

  • 不能用在WHERE子句中
  • 会默认直接丢弃NULL值
求和
  • 计算一个数值列的总和

SUM()

求平均值
  • 计算一个数值列的平均值

AVG()

求最大值
  • 返回指定列中的最大值

MAX()

求最小值
  • 返回指定列中的最小值

MIN()

计算总数
  • 计算符合查询条件的总行数

COUNT()

计算所有数据总行数

COUNT(*)

聚合函数的应用

查询计算后的数据

<function>：函数名

1
SELECT <function>(<field_name>) FROM <table_name>;
分组查询
  • 将查询到的数据根据字段进行分组
1
SELECT <field_name> FROM <table_name> GROUP BY <field_name>;
筛选
  • 查询分组后筛选符合条件的数据
1
SELECT <field_name> FROM <table_name> GROUP BY <filed_name> HAVING <field_name><operator><value>;

WHERE和HAVING的区别

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

排序
  • 默认为升序
升序（从小到大）
1
2
3
SELECT <field_name> FROM <table_name> ORDER BY <field_name>;

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_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>;
隐式内连接
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>;

外连接

左外连接查询
  • 左侧显示所有数据，右侧如果没有对应关系则显示为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_2> 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_2> RIGHT JOIN <table_name_2> ON <table_name_1>.<field_name_2>=<table_name_2>.<field_name_1>;

分页查询

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

1
SELECT <field_name> FROM <table_name> LIMIT <index_first>,<index_all>;

子查询

  • 将一个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>,(<select>) WHERE <field_name><operator><field_value>;

对视图的操作

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

创建视图

<select>：select语句

1
CREATE VIEW <view_name> AS <select>;

重建视图

1
REPLACE 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
2
3
SELECT * FROM <view_name>;

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>;

对索引的操作

查看所有索引

1
SHOW INDEX FROM <table_name>;

创建索引

1
2
3
4
5
CREATE INDEX <index_name> ON <table_name>(<field_name>);

ALTER TABLE <table_name> ADD INDEX <index_name>(<field_name_1>, <field_name_2>);

CREATE TABLE <table_name>(<field_name> <operator>(<length>), INDEX <index_name>(field_name));

删除索引

1
2
3
DROP INDEX <index_name> <table_name>;

ALTER TABLE <table_name> DROP INDEX <index_name>;

对约束的操作

操作约束的方式

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

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

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

AUTO_INCREMENT：如果是数值型主键，可以定义逐渐自增
UNIQUE：唯一约束，定义这个列的值必须是唯一的、不能重复的，但可以为空
DEFAULT 默认值：默认约束，当没有为字段赋值时的默认值

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

在建表时添加约束（定义在所有字段定义的后面）

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

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

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

修改约束（删除约束）

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

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

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() 获取当前日期，格式是：年月日
curtime() 获取当前时间 ，格式是：时分秒
sysdate()/now() 获取当前日期+时间，格式是：年月日 时分秒
year(date) 返回date中的年份
month(date) 返回date中的月份
day(date) 返回date中的天数
hour(date) 返回date中的小时
minute(date) 返回date中的分钟
second(date) 返回date中的秒
CONCAT(s1,s2..) 将s1,s2 等多个字符串合并为一个字符串
CONCAT_WS(x,s1,s2..) 同CONCAT(s1,s2,..)函数，但是每个字符串之间要加上x，x是分隔符

完成

参考文献

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