MySQL高级操作

1. 数据库

现代的 SQL 服务器构建在 RDBMS 之上。

1.1 服务器 RDBMS

DBMS - 数据库管理系统（Database Management System）

数据库管理系统是一种可以访问数据库中数据的计算机程序。DBMS 使我们有能力在数据库中提取、修改或者存贮信息。不同的 DBMS 提供不同的函数供查询、提交以及修改数据。

RDBMS - 关系数据库管理系统（Relational Database Management System）

关系数据库管理系统 (RDBMS) 也是一种数据库管理系统，其数据库是根据数据间的关系来组织和访问数据的。20 世纪 70 年代初，IBM 公司发明了 RDBMS。RDBMS 是 SQL 的基础，也是所有现代数据库系统诸如 Oracle、SQL Server、IBM DB2、Sybase、MySQL 以及 Microsoft Access 的基础。

1.2 数据库设计

设计数据库可能由专门的数据库设计人员完成，也可能是由开发组人员完成，一般是项目经理带领组员完成，经过研究，对于数据库的设计提出了一些规范，这些规范被称为范式(Normal Form)，目前已经提出的有8种范式，一般使用的是三范式。

1.2.1 第一范式(1NF)

强调列的原子性，即列不能再分

‘中国 - 北京’ 这个信息可以拆分成 ‘中国’ 和 ‘北京’ 来进行存储

1.2.2 第二范式(2NF)

首先满足1NF，另外要满足表必须有一个主键，并且非主键列必须完全依赖于主键。简单来说就是每一张表都要有一个主键，并且一张表只能做一件事

例如：人员表、部门表等等，每张表存储独立完整的信息

1.2.3 第三范式(3NF)

前提是满足2NF，要求一个数据库表中不包含已经在其他表中包含的非主键关键字信息

2. 表操作

2.1 创建表

2.1.1 create DB

语法结构：

-- 用于创建数据库
CREATE DATABASE database_name;

应用举例：

-- 创建一个名为 "my_db" 的数据库。
CREATE DATABASE my_db;

2.1.2 create table

语法结构：

-- 用于创建数据库中的表
CREATE TABLE 表名称
(
列名称1 数据类型 [约束条件] [默认值],
列名称2 数据类型 [约束条件] [默认值],
列名称3 数据类型 [约束条件] [默认值],
....
primary key(字段)
)engine = innoDB auto_increment = 1 default charset = utf8 comment = '表里面的内容'

primary key(字段) # 主键

engine = InnoDB # 引擎类型(事务处理引擎)

auto_increment = 1 # 没增加一行时自动增量

charset = utf8 # 编码格式

comment # 备注注释

数据类型（data_type）规定了列可容纳何种数据类型。下面的表格包含了SQL中最常用的数据类型：

数据类型	描述
integer(size) int(size) smallint(size) tinyint(size)	仅容纳整数。 在括号内规定数字的最大位数。
decimal(size,d) numeric(size,d)	容纳带有小数的数字。 “size” 规定数字的最大位数。”d” 规定小数点右侧的最大位数。
char(size)	容纳固定长度的字符串（可容纳字母、数字以及特殊字符）。 在括号中规定字符串的长度。
varchar(size)	容纳可变长度的字符串（可容纳字母、数字以及特殊的字符）。 在括号中规定字符串的最大长度。
date(yyyymmdd)	容纳日期。

应用举例：

-- 创建名为 "Person" 的表。
CREATE TABLE Persons
(
Id_P int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

查看建表语句：

-- 查看建表语句
show create table 表名;

2.1.3 Constraints

约束用于限制加入表的数据的类型。可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后也可以（通过 ALTER TABLE 语句）。我们将主要探讨以下几种约束：

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
• DEFAULT

2.1.3.1 not null

NOT NULL 约束强制列不接受 NULL 值，强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。

应用举例：

-- 强制 "Id_P" 列和 "LastName" 列不接受 NULL 值
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

2.1.3.2 unique

UNIQUE 约束唯一标识数据库表中的每条记录。UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。PRIMARY KEY 拥有自动定义的 UNIQUE 约束。

请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

应用举例：

-- 在 "Id_P" 列创建 UNIQUE 约束：
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (Id_P)
)

-- 命名 UNIQUE 约束，以及为多个列定义 UNIQUE 约束
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)
)

-- 当表已被创建时，如需在 "Id_P" 列创建 UNIQUE 约束
ALTER TABLE Persons
ADD UNIQUE (Id_P)

-- 当表已被创建时，如需命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)

-- 撤销 UNIQUE 约束
ALTER TABLE Persons
DROP INDEX uc_PersonID

以上示例为MySQL数据库，如在SQL Server / Oracle / MS Access数据库，语法稍有变化：

>-- 在 "Id_P" 列创建 UNIQUE 约束：
CREATE TABLE Persons
(
Id_P int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

-- 命名 UNIQUE 约束，以及为多个列定义 UNIQUE 约束
-- 当表已被创建时，如需在 "Id_P" 列创建 UNIQUE 约束
-- 当表已被创建时，如需命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束
-- 与MySQL相同

-- 撤销 UNIQUE 约束
ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID

其他约束的创建与撤销语法差异类似

2.1.3.3 primary key

PRIMARY KEY 约束唯一标识数据库表中的每条记录。主键必须包含唯一的值。主键列不能包含 NULL 值。每个表都应该有一个主键，并且每个表只能有一个主键。

应用举例：

-- 在 "Persons" 表创建时在 "Id_P" 列创建 PRIMARY KEY 约束
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (Id_P)
)

-- 命名 PRIMARY KEY 约束，以及为多个列定义 PRIMARY KEY 约束
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)
)

-- 表已存在的情况下为 "Id_P" 列创建 PRIMARY KEY 约束
ALTER TABLE Persons
ADD PRIMARY KEY (Id_P)

-- 表已存在的情况下命名 PRIMARY KEY 约束，以及为多个列定义 PRIMARY KEY 约束
-- 必须把主键列声明为不包含 NULL 值（在表首次创建时）
ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)

-- 撤销 PRIMARY KEY 约束
ALTER TABLE Persons
DROP PRIMARY KEY

2.1.3.4 foreign key

一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。

举一个简单的例子来解释外键。请看下面两个表：

“Persons” 表：

Id_P	LastName	FirstName	Address	City
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

“Orders” 表：

Id_O	OrderNo	Id_P
1	77895	3
2	44678	3
3	22456	1
4	24562	1

“Orders” 中的 “Id_P” 列指向 “Persons” 表中的 “Id_P” 列。”Persons” 表中的 “Id_P” 列是 “Persons” 表中的 PRIMARY KEY。**”Orders” 表中的 “Id_P” 列是 “Orders” 表中的 FOREIGN KEY**。

FOREIGN KEY 约束用于预防破坏表之间连接的动作。

FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

应用举例：

-- 在 "Orders" 表创建时为 "Id_P" 列创建 FOREIGN KEY
CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (Id_O),
FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
)

-- 命名 FOREIGN KEY 约束，以及为多个列定义 FOREIGN KEY 约束
CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (Id_O),
CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)
)

-- 在 "Orders" 表已存在的情况下为 "Id_P" 列创建 FOREIGN KEY 约束
ALTER TABLE Orders
ADD FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)

-- 在 "Orders" 表已存在的情况下,命名 FOREIGN KEY 约束，以及为多个列定义 FOREIGN KEY 约束
ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)

-- 撤销 FOREIGN KEY 约束
ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders

2.1.3.5 check

CHECK 约束用于限制列中的值的范围。如果对单个列定义 CHECK 约束，那么该列只允许特定的值。如果对一个表定义 CHECK 约束，那么此约束会在特定的列中对值进行限制。

-- 在 "Persons" 表创建时为 "Id_P" 列创建 CHECK 约束。CHECK 约束规定 "Id_P" 列必须只包含大于 0 的整数
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (Id_P>0)
)

-- 要命名 CHECK 约束，以及为多个列定义 CHECK 约束
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')
)

-- 在表已存在的情况下为 "Id_P" 列创建 CHECK 约束
ALTER TABLE Persons
ADD CHECK (Id_P>0)

-- 在表已存在的情况下命名 CHECK 约束，以及为多个列定义 CHECK 约束
ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')

-- 撤销 CHECK 约束
ALTER TABLE Persons
DROP CHECK chk_Person

2.1.3.6 default

DEFAULT 约束用于向列中插入默认值。如果没有规定其他的值，那么会将默认值添加到所有的新记录。

应用举例：

-- 在 "Persons" 表创建时为 "City" 列创建 DEFAULT 约束
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)

-- 使用类似 GETDATE() 这样的函数，DEFAULT 约束也可以用于插入系统值
CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
OrderDate date DEFAULT GETDATE()
)

-- 在表已存在的情况下为 "City" 列创建 DEFAULT 约束
ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'

-- 撤销 DEFAULT 约束
ALTER TABLE Persons
ALTER City DROP DEFAULT

以上示例为MySQL数据库，如在SQL Server / Oracle / MS Access数据库，语法稍有变化：

-- 在表已存在的情况下为 "City" 列创建 DEFAULT 约束
ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'SANDNES'

-- 撤销 DEFAULT 约束
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT

2.1.4 create index

在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据。用户无法看到索引，它们只能被用来加速搜索/查询。

更新一个包含索引的表需要比更新一个没有索引的表更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。

语法结构：

-- 在表上创建一个简单的索引。允许使用重复的值：
CREATE INDEX index_name
ON table_name (column_name)

-- 在表上创建一个唯一的索引。唯一的索引意味着两个行不能拥有相同的索引值。
CREATE UNIQUE INDEX index_name
ON table_name (column_name)

应用举例：

-- 创建一个简单的索引，名为 "PersonIndex"，在 Person 表的 LastName 列
CREATE INDEX PersonIndex
ON Person (LastName)

-- 以降序索引某个列中的值，您可以在列名称之后添加保留字 DESC
CREATE INDEX PersonIndex
ON Person (LastName DESC)

-- 希望索引不止一个列，您可以在括号中列出这些列的名称，用逗号隔开
CREATE INDEX PersonIndex
ON Person (LastName, FirstName)

-- 删除索引
ALTER TABLE Person DROP INDEX PersonIndex

2.2 更新表

2.2.1 insert into

语法结构：

INSERT INTO table_name(列1, 列2,...) VALUES (值1, 值2,....);

“Persons” 表：

LastName	FirstName	Address	City
Carter	Thomas	Changan Street	Beijing

应用举例：

-- 全列插入时，值的顺序必须与表中字段的顺序对应
INSERT INTO Persons VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing')

-- 部分列插入时，值的顺序必须与给出的列顺序对应
INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees'),('Simon','malianwa')

结果：

LastName	FirstName	Address	City
Carter	Thomas	Changan Street	Beijing
Gates	Bill	Xuanwumen 10	Beijing
Wilson		Champs-Elysees
Simon		malianwa

主键列是自动增长的，但在全列插入时需要占位，通常使用0或者default或者null来占位，插入成功后以实际数据为准

2.2.2 update

语法结构：

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

应用举例：

Person表:

LastName	FirstName	Address	City
Gates	Bill	Xuanwumen 10	Beijing
Wilson		Champs-Elysees

-- 为 lastname 是 "Wilson" 的人添加 firstname：
UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson' 

-- 更新某一行中的若干列
UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing'
WHERE LastName = 'Wilson'

结果：

LastName	FirstName	Address	City
Gates	Bill	Xuanwumen 10	Beijing
Wilson	Fred	Zhongshan 23	Nanjing

2.2.3 alter

ALTER TABLE 语句用于在已有的表中添加、修改或删除列。

语法结构：

-- 在表中添加列
ALTER TABLE table_name
ADD column_name datatype

-- 删除表中的列
ALTER TABLE table_name 
DROP COLUMN column_name

-- 改变表中列的数据类型
ALTER TABLE table_name
ALTER COLUMN column_name datatype

-- 修改列名
ALTER TABLE table_name
change column_name new_column_name datatype; -- 新字段需要完整定义

-- 修改字段约束
ALTER TABLE table_name
modify 字段名 字段约束;

-- 修改表名
ALTER TABLE table_name
rename new_table_name;

rename和change都是用于修改名称的，rename时修改表名称，change是修改表中字段名称

modify用于修改表中字段的数据长度，数据类型以及字段的约束条件。

应用举例：

原始表 Persons 表:

Id	LastName	FirstName	Address	City
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

-- 在表 "Persons" 中添加一个名为 "Birthday" 的新列
ALTER TABLE Persons
ADD Birthday date

-- 改变 "Persons" 表中 "Birthday" 列的数据类型
ALTER TABLE Persons
ALTER COLUMN Birthday year

-- 改变 "Persons" 表中 "Birthday" 列的约束为非空
ALTER TABLE Persons
modify Birthday date not null

-- 删除 "Person" 表中的 "Birthday" 列
ALTER TABLE Person
DROP COLUMN Birthday

2.3 删除表

2.3.1 delete

DELETE 语句用于删除表中的行。

语法结构：

DELETE FROM 表名称 WHERE 列名称 = 值

应用举例：

Person:

LastName	FirstName	Address	City
Gates	Bill	Xuanwumen 10	Beijing
Wilson	Fred	Zhongshan 23	Nanjing

-- 删除"Fred Wilson"
DELETE FROM Person WHERE LastName = 'Wilson' 

-- 在不删除表的情况下删除所有的行。这意味着表的结构、属性和索引都是完整的：
DELETE FROM table_name
-- 或者使用下面的写法
DELETE * FROM table_name

2.3.2 drop

通过使用 DROP 语句，可以轻松地删除索引、表和数据库。

使用 DROP INDEX 命令删除表格中的索引。

语法结构：

ALTER TABLE table_name DROP INDEX index_name

DROP TABLE 语句用于删除表（表的结构、属性以及索引也会被删除）

语法结构：

DROP TABLE 表名称

DROP DATABASE 语句用于删除数据库：

语法结构：

DROP DATABASE 数据库名称

删库需谨慎，在执行删库命令时，MySQL不会给出任何提示，所以在执行删库命令时，数据库中的数据会被直接删除，如果没有提前备份，则不能恢复

如果我们仅仅需要除去表内的数据，但并不删除表本身，那么我们该如何做呢？

请使用 DELETE 命令（仅仅删除表格中的数据，见上一小节）。

3. 视图操作

视图是可视化的表。

在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。

视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表。

数据库的设计和结构不会受到视图中的函数、where 或 join 语句的影响。

3.1 创建视图

语法结构：

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

可以从某个查询内部、某个存储过程内部，或者从另一个视图内部来使用视图。通过向视图添加函数、join 等等，我们可以向用户精确地提交我们希望提交的数据。

应用举例：

-- 视图 "Current Product List" 会从 Products 表列出所有正在使用的产品
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No;

-- 选取 Products 表中所有单位价格高于平均单位价格的产品
CREATE VIEW [Products Above Average Price] AS
SELECT ProductName,UnitPrice
FROM Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products);

-- 计算在 1997 年每个种类的销售总数
CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName;

3.2 查看/使用视图

视图的查看和表的查看命令一致，show tables 会把所有的表和视图都列出来。

建立视图就是为了使用简化，提高查询效率。

-- 可以像这样查询上面创建的视图
SELECT * FROM [Current Product List];
SELECT * FROM [Products Above Average Price];
SELECT * FROM [Category Sales For 1997];

3.3 更新视图

语法结构：

-- 方法一 create or replace view
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;

-- 方法二 alter view
alter view view_name as
SELECT column_name(s)
FROM table_name
WHERE condition;

-- 重命名视图
rename table old_view_name to new_view_name;

应用举例：

-- 向 "Current Product List" 视图添加 "Category" 列
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No

3.4 删除视图

语法结构：

-- 通过 DROP VIEW 命令来删除视图
DROP VIEW view_name

3.5 更改视图数据

视图是虚拟表，修改视图内的数据即修改基本表的数据，所以如果要修改视图数据就直接修改对应的基本表数据即可。

4. 事务操作

事务是一个最小的不可再分的工作单元，通常来说一个事务对应一个完整的业务。

• MySQL中只有使用innodb这个数据库引擎的数据库或者数据表才能支持事务。
• 事务可以保证成批的SQL语句要么全执行，要么全不执行，以此用来维护数据库的完整性。
• 事务只和DML语句有关。

场景解释：银行转账业务

-- 李四找张三借款5000元
update t_a set balance = 5000 where name = '张三';
update t_a set balance = -5000 where name = '李四';
-- 上面两个语句必须保证同时执行

事务的特性(ACID)

• 原子性(Atomicity)
• 一致性(Consistency)
• 隔离性(Isolation)
• 持久性(Durability)

4.1 事务命令

-- 查看数据库引擎类型
show create table table_name;

-- 开启事务命令
-- 开启事务命令执行后，会在本地缓存中，而不是到数据库表中
begin;
-- 或者
start transaction;

-- 提交事务命令
-- 将缓存中的数据存到数据表中
commit;

-- 回滚命令
-- 放弃缓存中的数据
rollback;

DML命令会自动触发事务，例如：insert，update，delete

当需要多条数据的修改一致进行时，可以使用事务，即如果成功都成功，有一个不成功就回滚到之前。

赏

谢谢你请我吃糖果