记的是MySQL的语法，但是应该大差不差

数据库的规范化

第一范式（1NF）

第一范式是指数据库的每一列都是不可分割的基本数据项，而下面这样的就存在可分割的情况：

  • 学生（姓名，电话号码）

电话号码实际上包括了家用座机电话和移动电话，因此它可以被拆分为：

  • 学生（姓名，座机号码，手机号码）

满足第一范式是关系型数据库最基本的要求！

第二范式（2NF）

第二范式要求表中必须存在主键，且其他的属性必须完全依赖于主键，比如：

  • 学生（学号，姓名，性别）

学号是每个学生的唯一标识，每个学生都有着不同的学号，因此此表中存在一个主键，并且每个学生的所有属性都依赖于学号，学号发生改变就代表学生发生改变，姓名和性别都会因此发生改变，所有此表满足第二范式。

第三范式（3NF）

在第二范式的基础上，要求一个数据库表中不包含已在其他表中已包含的非主属性信息，也就是说，非主键列必须直接依赖于主键，不能依赖于其他非主键列

  • 学生借书情况（借阅编号，学生学号，书籍编号，书籍名称，书籍作者）

实际上书籍编号依赖于借阅编号，而书籍名称和书籍作者依赖于书籍编号，因此存在传递依赖的情况，我们可以将书籍信息进行单独拆分为另一张表：

  • 学生借书情况（借阅编号，学生学号，书籍编号）
  • 书籍（书籍编号，书籍名称，书籍作者）

这样就消除了传递依赖，从而满足第三范式。

BCNF

BCNF作为第三范式的补充，假设仓库管理关系表为StorehouseManage(仓库ID, 存储物品ID, 管理员ID, 数量)，且有一个管理员只在一个仓库工作；一个仓库可以存储多种物品。这个数据库表中存在如下决定关系：

(仓库ID, 存储物品ID) →(管理员ID, 数量)

(管理员ID, 存储物品ID) → (仓库ID, 数量)

所以，(仓库ID, 存储物品ID)和(管理员ID, 存储物品ID)都是StorehouseManage的候选关键字，表中的唯一非关键字段为数量，它是符合第三范式的。但是，由于存在如下决定关系：

(仓库ID) → (管理员ID)

(管理员ID) → (仓库ID)

即存在关键字段决定关键字段的情况，如果修改管理员ID，那么就必须逐一进行修改，所以其不符合BCNF范式。
也就是出现了 管理员ID 和 仓库ID 都能够确定一个仓库 即 出现了两个唯一值相互确定。

数据库定义语言（DDL）

写命令时 要大写就都大写 小写就都小写

数据库操作

  1. 创建一个数据库：
1
create database 数据库名

为了能够支持中文，我们在创建时可以设定编码格式：

1
CREATE DATABASE IF NOT EXISTS 数据库名称 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

IF NOT EXISTS表示 只有当指定的数据库名不存在时才创建对应的数据库

  1. 删除一个数据库：
1
drop database 数据库名

表操作

[]里的内容代表可以省略

数据类型

（1）以下的数据类型用于字符串存储：

  • char(n)可以存储任意字符串，但是是固定长度为n，如果插入的长度小于定义长度时，则用空格填充。
  • varchar(n)也可以存储任意数量字符串，长度不固定，但不能超过n，不会用空格填充。

（2）以下数据类型用于存储数字：

  • smallint用于存储小的整数，范围在 (-32768，32767)
  • int用于存储一般的整数，范围在 (-2147483648，2147483647)
  • bigint用于存储大型整数，范围在 (-9,223,372,036,854,775,808，9,223,372,036,854,775,807)
  • float用于存储单精度小数
  • double用于存储双精度的小数

（3）以下数据类型用于存储时间：

  • date存储日期
  • time存储时间
  • year存储年份
  • datetime用于混合存储日期+时间

列级约束条件

一个列可以有多个约束

列级约束有六种：主键Primary key、外键foreign key 、唯一 unique、检查 check（CHECK约束用于在数据库中对表的数据进行验证和限制。它允许你定义一个条件，该条件必须在插入或更新数据时满足才能成功执行操作。） 、默认default 、非空/空值 not null/ null

表级约束条件

表级约束有四种：主键、外键、唯一、检查

创建表

1
2
3
4
create table 表名(列名 数据类型[列级约束条件],
             列名 数据类型[列级约束条件],
             ...
             [,表级约束条件])

如果需要在创建的时候 添加外键，下面是一个例子：

1
2
3
4
5
6
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    CONSTRAINT [FK_CustomerID] FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID)
);

在这个例子中，创建了一个名为”Orders”的表，它有三个列：OrderID、CustomerID和OrderDate。然后，通过FOREIGN KEY约束，将CustomerID列设置为外键，参照了另一个名为”Customers”的表中的CustomerID列作为主键。
通过定义外键约束，我们可以确保在”Orders”表中的CustomerID列中的值必须存在于”Customers”表的CustomerID列中，从而维护了表之间的关系完整性。

删除表

1
DROP TABLE <table_name>;

修改表

1
2
3
ALTER TABLE 表名 /*添加列*/[ADD 新列名 数据类型[列级约束条件]]
                /*删除列*/[DROP COLUMN 列名[restrict|cascade]]
                /*修改列*/[ALTER COLUMN 列名 新数据类型]

我们可以通过 ADD 来添加一个新的列，通过 DROP 来删除一个列，不过我们可以添加restrict或cascade，默认是restrict，表示如果此列作为其他表的约束或视图引用到此列时，将无法删除，而cascade会强制连带引用此列的约束、视图一起删除。还可以通过 ALTER 来修改此列的属性。

表创建后 对列追加约束

你也可以在创建表之后，使用ALTER TABLE语句来添加约束：

1
2
ALTER TABLE Employees
MODIFY ID INT NOT NULL UNIQUE;

这个语句会修改”Employees”表，使得”ID”列不能有NULL值，并且所有的值必须是唯一的。

表创建后 对列追加外键约束

1
2
3
4
5
6
7
8
9
-- 创建表
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE
);
-- 添加外键约束
ALTER TABLE Orders
ADD CONSTRAINT FK_CustomerID FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID);

重命名表

注意：并非所有的数据库系统都支持RENAME TABLE语句例如MySQL支持

1
RENAME TABLE <old_table_name> TO <new_table_name>;

删除外键约束

首先使用DESCRIBE语句查看表的结构，确定要删除外键约束的表和列名：

1
DESCRIBE table_name;

（将table_name替换为实际的表名）

使用ALTER TABLE语句和DROP FOREIGN KEY子句删除外键约束：

1
ALTER TABLE table_name DROP FOREIGN KEY constraint_name;

（将table_name替换为实际的表名，constraint_name替换为实际的外键约束名称）。

数据库操纵语言（DML）

插入数据

1
INSERT INTO 表名 VALUES(值1, 值2, 值3)

如果插入的数据与列一一对应，那么可以省略列名，但是如果希望向指定列上插入数据，就需要给出列名：

1
INSERT INTO 表名(列名1, 列名2) VALUES(值1, 值2)

我们也可以一次性向数据库中插入多条数据：

1
INSERT INTO 表名(列名1, 列名2) VALUES(值1, 值2), (值1, 值2), (值1, 值2)

修改数据

我们可以通过update语句来更新表中的数据：

1
UPDATE 表名 SET 列名=值,... WHERE 条件

注意，SQL语句中的等于判断是=

警告：如果忘记添加WHERE字句来限定条件，将使得整个表中此列的所有数据都被修改！

删除数据

我们可以通过使用delete来删除表中的数据：

1
DELETE FROM 表名

不加条件限制会把表中数据全部删除

通过这种方式，将删除表中全部数据，我们也可以使用where来添加条件，只删除指定的数据：

1
DELETE FROM 表名 WHERE 条件

数据库查询语言（DQL）

单表查询

单表查询

1
2
3
4
5
6
7
8
-- 指定查询某一列数据
SELECT 列名[,列名] FROM 表名
-- 会以别名显示此列
SELECT 列名 别名 FROM 表名
-- 查询所有的列数据
SELECT * FROM 表名
-- 只查询不重复的值
SELECT DISTINCT 列名 FROM 表名

我们也可以添加where字句来限定查询目标：

1
SELECT * FROM 表名 WHERE 条件

常用查询条件：

  • 一般的比较运算符，包括=、>、<、>=、<=、!=等。
  • 是否在集合中：in、not in
  • 字符模糊匹配：like，not like
  • 多重条件连接查询：and、or、not
  • 空值和非空值：is null、is not null
    模糊查询举例：
  1. 匹配以特定字符开头的值：
    示例：SELECT * FROM 表名 WHERE 列名 LIKE 'abc%';
    这将返回以”abc”开头的所有值，如”abc123”、”abcdef”等。
  2. 匹配以特定字符结尾的值：
    示例：SELECT * FROM 表名 WHERE 列名 LIKE '%xyz';
    这将返回以”xyz”结尾的所有值，如”abcxyz”、”123xyz”等。
  3. 匹配包含特定字符的值：
    示例：SELECT * FROM 表名 WHERE 列名 LIKE '%def%';
    这将返回包含”def”的所有值，如”abcdef”、”defghi”等。
  4. 匹配任意单个字符：
    示例：SELECT * FROM 表名 WHERE 列名 LIKE '_bc';
    这将返回第一个字符为任意字符，后面跟着”bc”的值，如”abc”、”1bc”等。
  5. 匹配指定数量的任意字符：
    示例：SELECT * FROM 表名 WHERE 列名 LIKE 'a__';
    这将返回以”a”开头，后面跟着任意两个字符的值，如”abc”、”axy”等。
  6. 使用IN条件：
    假设您有一个名为”users”的表，其中包含一个名为”country”的列。您想要查询所有来自”美国”和”英国”的用户。您可以使用IN条件来实现这个查询：
1
SELECT * FROM users WHERE country IN ('美国', '英国');
  1. 使用NOT IN条件：
    假设您有一个名为”products”的表，其中包含一个名为”category”的列。您想要查询不属于”电子产品”和”家居用品”类别的产品。您可以使用NOT IN条件来实现这个查询：
1
SELECT * FROM products WHERE category NOT IN ('电子产品', '家居用品');

排序查询

我们可以通过order by来将查询结果进行排序：

1
SELECT * FROM 表名 WHERE 条件 ORDER BY 列名 ASC|DESC

使用ASC表示升序排序，使用DESC表示降序排序，默认为升序。

我们也可以可以同时添加多个排序：

1
SELECT * FROM 表名 WHERE 条件 ORDER BY 列名1 ASC|DESC, 列名2 ASC|DESC

这样会先按照列名1进行排序，每组列名1相同的数据再按照列名2排序。

聚集函数

聚集函数一般用作统计，包括：

  • count([distinct]*)统计所有的行数（distinct表示去重再统计，下同）
  • count([distinct]列名)统计某列的值总和
  • sum([distinct]列名)求一列的和（注意必须是数字类型的）
  • avg([distinct]列名)求一列的平均值（注意必须是数字类型）
  • max([distinct]列名)求一列的最大值
  • min([distinct]列名)求一列的最小值

一般聚集函数是这样使用的：

1
SELECT count(distinct 列名) FROM 表名 WHERE 条件

当你使用 COUNT(*) 时，它会计算表中的所有行，无论列值是否为NULL。这包括了所有的记录，无论它们的列值是否为空。
换句话说，COUNT(*) 会返回表中的总行数，而不仅仅是那些至少有一列非NULL值的行数
如果你想要计算至少有一列非NULL值的行数，你需要指定那一列，像这样：COUNT(列名)。这样，只有当指定列的值非NULL时，那一行才会被计数。

分组查询

通过使用group by来对查询结果进行分组，它需要结合聚合函数一起使用， 聚合函数会对组中的数据进行计算统计：

1
SELECT sum(*) FROM 表名 WHERE 条件 GROUP BY 列名

我们还可以添加having来限制分组条件：
HAVING子句主要是用来对聚合函数（如COUNT, SUM, AVG, MAX, MIN等）的结果进行过滤或条件判断。

1
SELECT sum(*) FROM 表名 WHERE 条件 GROUP BY 列名 HAVING 约束条件

分组的依据：在分组查询中，具有相同值的数据会被分到同一组中

group by就是把一列通过某个条件分成不同的部分进行聚合函数计算

分页查询

我们可以逐页获取数据，而不是一次性获取全部数据。

1
SELECT * FROM 表名 LIMIT 起始位置,数量

多表查询

多表查询

多表查询是同时查询的两个或两个以上的表，多表查询会提通过连接转换为单表查询。

1
SELECT * FROM 表1, 表2

直接这样查询会得到两张表的笛卡尔积，也就是每一项数据和另一张表的每一项数据都结合一次，会产生庞大的数据。

1
SELECT * FROM 表1, 表2 WHERE 条件

这样，只会从笛卡尔积的结果中得到满足条件的数据。
注意： 如果两个表中都带有此属性，需要添加表名前缀来指明是哪一个表的数据。

以下是一个示例，假设您有两个表： employee 和 departments ，它们都具有一个名为”name”的属性。您想要查询每个员工所属的部门名称以及员工的姓名。

1
2
SELECT employees.name AS employee_name, departments.name AS department_name
FROM employees;

在上面的示例中，我们使用AS关键字为每个属性指定别名。employees.name``被重命名为employee_name，departments.name被重命名为department_name。这样，我们可以明确地引用每个属性，并避免冲突。

自身连接查询

自身连接，就是将表本身和表进行笛卡尔积计算，得到结果，但是由于表名相同，因此要先起一个别名：

1
SELECT * FROM 表名 别名1, 表名 别名2

其实自身连接查询和前面的是一样的，只是连接对象变成自己和自己了。

以下是别名的其他应用场景：

  1. 列别名：可以为查询结果中的列指定一个更具描述性的名称。
    示例：SELECT 列名 AS 别名 FROM 表名;
    例如，可以使用别名将”salary”列的名称更改为”工资”：
    SELECT salary AS 工资 FROM employees;
  2. 表别名：可以为查询中的表指定一个简短的别名，以减少重复输入。
    示例：SELECT 列名 FROM 表名 AS 别名;
    例如，可以使用别名将”employees”表的名称更改为”e”：

外连接查询

外连接就是专门用于联合查询情景的，比如现在有一个存储所有用户的表，还有一张用户详细信息的表，我希望将这两张表结合到一起来查看完整的数据，我们就可以通过使用外连接来进行查询，外连接有三种方式：

1
SELECT * FROM 左表名 <连接方式> 右表名 ON 连接条件;
  • 通过使用inner join进行内连接，只会返回两个表满足条件的交集部分：
  • 通过使用left join进行左连接，不仅会返回两个表满足条件的交集部分，也会返回左边表中的全部数据，而在右表中缺失的数据会使用null来代替（右连接right join同理，只是反过来而已，这里就不再介绍了）
  • 我们还可以将三张表（甚至更多外连接到一起）

嵌套查询

我们可以将查询的结果作为另一个查询的条件，比如

1
SELECT * FROM 表名 WHERE 列名1 = (SELECT 列名2 FROM 表名 WHERE 条件)

也就是 查询到了 列1 和 列2 相等部分的 重合的数据

使用嵌套查询进行子查询：
假设您有一个名为”orders”的表，其中包含订单信息，包括订单号（order_id）和订单金额（amount）。您想要查询所有订单金额大于平均订单金额的订单。您可以使用嵌套查询来实现这个查询：

1
2
3
SELECT order_id, amount
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

在上述示例中，内部查询(SELECT AVG(amount) FROM orders)计算了订单金额的平均值，并将其用于外部查询的筛选条件。

使用嵌套查询进行连接查询：
假设您有两个表：”customers”和”orders”，其中”customers”表包含客户信息，”orders”表包含订单信息。您想要查询每个客户的订单数量。您可以使用嵌套查询和连接查询来实现这个查询：

1
2
SELECT c.customer_id, c.customer_name, (SELECT COUNT(*) FROM orders WHERE customer_id = c.customer_id) AS order_count
FROM customers AS c;

在上述示例中，内部查询(SELECT COUNT(*) FROM orders WHERE customer_id = c.customer_id)计算了每个客户的订单数量，并将其作为外部查询的一个列。

数据库控制语言（DCL）

创建用户：

在MySQL中，你可以使用以下语法来创建一个用户并为其设置密码：

1
CREATE USER 'username' IDENTIFIED BY 'password';

这个命令将创建一个名为 ‘username’ 的用户，并将其密码设置为 ‘password’。

如果你希望用户能够从任何主机连接到MySQL服务器，可以将 'localhost' 替换为 '%'：

1
CREATE USER 'username'@'%' IDENTIFIED BY 'password';

我们也可以在创建时不设置密码：

1
CREATE USER 用户名;

登陆用户

首先需要添加一个环境变量，然后我们通过cmd去登陆mysql：

1
login -u 用户名 -p

输入密码后即可登陆此用户

用户授权

我们可以通过使用grant来为一个数据库用户进行授权：

1
GRANT all [权限1,权限2...(列1,...)] on 数据库.表 to 用户 [with grant option]

其中all代表授予所有权限，当数据库和表为*，代表为所有的数据库和表都授权。如果在最后添加了with grant option，那么被授权的用户还能将已获得的授权继续授权给其他用户。

授予权限的语法如下：

1
GRANT privileges ON database.table TO 'username'@'host';

其中，’privileges’是你要授予的权限，可以是单个权限或多个权限的组合，用逗号分隔。’database.table’指定了你要授予权限的数据库和表。‘username‘@’host’指定了你要授予权限的用户和主机。

例如，如果要将SELECT和INSERT权限授予用户’john’，并且限制他只能在数据库’exampledb’中的表’table1’上执行这些操作，可以使用以下语句：

1
GRANT SELECT, INSERT ON exampledb.table1 TO 'john'@'localhost';
  1. ALL PRIVILEGES：授予用户对指定数据库或表的所有权限，包括 SELECT 、 INSERT 、 UPDATE 、 DELETE 、 CREATE 、 DROP 等。
  2. SELECT：授予用户对指定表的SELECT权限，允许其查询表中的数据。
  3. INSERT：授予用户对指定表的INSERT权限，允许其向表中插入新的数据。
  4. UPDATE：授予用户对指定表的UPDATE权限，允许其修改表中已有的数据。
  5. DELETE：授予用户对指定表的DELETE权限，允许其删除表中的数据。
  6. CREATE：授予用户创建新表、数据库或索引的权限。
  7. DROP：授予用户删除表、数据库或索引的权限。
  8. ALTER：授予用户修改表结构的权限，包括添加、修改和删除表的列。
  9. GRANT OPTION：授予用户将自己拥有的权限授予其他用户的权限。

我们可以使用revoke来收回一个权限：

1
revoke all [权限1,权限2...(列1,...)] on 数据库.表 from 用户

索引

创建索引

创建单列索引

1
CREATE INDEX index_name ON table_name (column_name);

创建多列索引

1
CREATE INDEX index_name ON table_name (column1, column2, ...);

创建唯一索引

1
CREATE UNIQUE INDEX index_name ON table_name (column_name);

创建全文索引

1
CREATE FULLTEXT INDEX index_name ON table_name (column_name);

查看索引

查看表的索引

1
SHOW INDEX FROM table_name;

查看索引的定义

1
SHOW CREATE TABLE table_name;

删除索引

删除表的索引

1
ALTER TABLE table_name DROP INDEX index_name;

删除表的所有索引

1
ALTER TABLE table_name DROP INDEX ALL;

请注意，创建索引可以提高查询效率，但也会增加插入、更新和删除操作的开销。因此，在创建索引时需要权衡查询性能和修改性能之间的平衡。

无论是否使用索引，最终都能查询到相应的内容。索引只是一种优化技术，它可以加快查询的速度，但并不影响查询结果的准确性。

在没有索引的情况下，数据库会对整个表进行全表扫描，逐行比对查询条件，最终找到符合条件的数据。这种方式可以保证查询结果的准确性，但在大型表或者有大量数据的情况下，查询可能会较慢。

而使用索引后，数据库可以利用索引的数据结构，直接定位到符合查询条件的数据行，避免了全表扫描的开销，从而提高了查询的速度。索引可以加快查询的效率，但不会改变查询结果。

需要注意的是，索引的创建和维护也会带来一定的开销，特别是在频繁进行插入、更新和删除操作的情况下。因此，在设计数据库时，需要根据具体的查询需求和数据特点，选择合适的列创建索引，以获得最佳的查询性能。

视图

视图本质就是一个查询的结果，不过我们每次都可以通过打开视图来按照我们想要的样子查看数据。既然视图本质就是一个查询的结果，那么它本身就是一个虚表，并不是真实存在的，数据实际上还是存放在原来的表中。

如何创建视图

我们可以通过create view来创建视图;

我们可以把 创建出的视图 理解为是对原表部分列的引用 因此当我们对某个可修改的视图进行修改时 原表对应的部分也会被修改

1
CREATE VIEW 视图名称(列名) as 子查询语句 [WITH CHECK OPTION];

（WITH CHECK OPTION是指当创建后，如果更新视图中的数据，是否要满足子查询中的条件表达式，不满足将无法插入，创建后，我们就可以使用select语句来直接查询视图上的数据了，因此，还能在视图的基础上，导出其他的视图。）

视图需要遵守的规则

  • 若视图是由两个以上基本表导出的，则此视图不允许更新。

  • 若视图的字段来自字段表达式或常数，则不允许对此视图执行INSERT和UPDATE操作，但允许执行DELETE操作。

  • 若视图的字段来自集函数，则此视图不允许更新。

  • 若视图定义中含有GROUP BY子句，则此视图不允许更新。

  • 若视图定义中含有DISTINCT短语，则此视图不允许更新。

  • 若视图定义中有嵌套查询，并且内层查询的FROM子句中涉及的表也是导出该视图的基本表，则此视图不允许更新。例如将成绩在平均成绩之上的元组定义成一个视图GOOD_SC：
    CREATE VIEW GOOD_SC AS SELECT Sno, Cno, Grade FROM SC WHERE Grade > (SELECT AVG(Grade) FROM SC); 　　
    导出视图GOOD_SC的基本表是SC，内层查询中涉及的表也是SC，所以视图GOOD_SC是不允许更新的。

  • 一个不允许更新的视图上定义的视图也不允许更新

如何删除一个视图：

通过drop来删除一个视图：

1
drop view apptest

事务

当我们要进行的操作非常多时，比如要依次删除很多个表的数据，我们就需要执行大量的SQL语句来完成，这些数据库操作语句就可以构成一个事务！只有Innodb引擎支持事务

我们通过以下例子来探究以下事务：

1
2
3
4
5
6
7
8
9
10
11
12
13
begin;   #开始事务

...

rollback;  #回滚事务
savepoint 回滚点;  #添加回滚点

rollback to 回滚点; #回滚到指定回滚点
...

commit; #提交事务
-- 一旦提交，就无法再进行回滚了！

  • 如果事务中的所有操作都执行成功，可以使用COMMIT提交事务，将结果永久保存到数据库中。
  • 如果事务中的任何操作失败或出现错误，可以使用ROLLBACK回滚事务，将所有操作都撤销到事务开始之前的状态。

触发器

触发器就像其名字一样，在某种条件下会自动触发，在select/update/delete时，会自动执行我们预先设定的内容，触发器通常用于检查内容的安全性，相比直接添加约束，触发器显得更加灵活。

  • insert操作时，新的内容会被插入到new表中；
  • 在delete操作时，旧的内容会被移到old表中，我们仍可在old表中拿到被删除的数据；
  • 在update操作时，旧的内容会被移到old表中，新的内容会出现在new表中。

创建触发器

1
2
3
4
5
6
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
FOR EACH ROW
   
# 触发器的操作逻辑

修改触发器

1
2
3
4
5
6
ALTER TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
FOR EACH ROW
   
# 修改后的触发器的操作逻辑

触发器的操作逻辑：是一条或多条 SQL 语句的列表，列表内的每条语句都必须用分号 ; 来结尾。

删除触发器

1
DROP TRIGGER [IF EXISTS] trigger_name;

查看触发器

1
SHOW TRIGGERS

—end—