数据库表

数据库中表的相关内容

表的创建

CREATE TABLE table_name (
field1 datatype comment `comment：为列添加注释，说明该列的用途。`,
field2 datatype,
field3 datatype
) character set 字符集 collate 校验规则 engine 存储引擎;

• field 表示列名。

• datatype 表示列的类型。

• character set 字符集，如果没有指定字符集，则以所在数据库的字符集为准。

• collate 校验规则，如果没有指定校验规则，则以所在数据库的校验规则为准。

表结构

表结构组成：

查看表结构

desc 表名;

修改表

ALTER TABLE tablename

• ALTER TABLE：这是用于修改现有表结构的 SQL 命令。通过它，可以对表进行多种修改操作，例如添加或删除列、修改列的数据类型、更改表的约束等。

• tablename：想要修改的表的名称。需要将这个占位符替换为实际的表名。

注意事项

1. 列的顺序：默认情况下，新列会被添加到表的末尾。也可以使用AFTER来改变。例如：

   • AFTER day：
     • 这部分指定新列 assets 的位置，将它添加到 day 列之后。也就是说，在表结构中，新列 assets 会紧跟在 day 列之后出现。

2. 表锁：在大多数情况下，ALTER TABLE 操作会锁定表，导致表在操作期间不可用。因此，使用 ALTER TABLE 时应注意对生产环境的影响

3. 列默认值：如果没有指定 DEFAULT 值，那么新列在插入数据时如果没有值，通常会默认为 NULL（除非数据类型不允许 NULL）。

关键字	用途	说明
MODIFY	修改现有列的属性，如数据类型、默认值等	用法示例：MODIFY column_name new_datatype [DEFAULT value]
DROP	删除表中的列或约束	用法示例：DROP column_name 或 DROP CONSTRAINT constraint_name
CHANGE	修改列名和列的数据类型	用法示例：alter table student change name gg varchar(64);
FIRST	将新列或修改的列移到表中的第一列位置	用法示例：alter table student add No. int unsigned default 1;
RENAME	重命名表或列	用法示例：RENAME TO new_table_name 或 RENAME COLUMN old_name TO new_name
ALTER COLUMN	修改列的某些属性（通常与其他关键字结合使用）	用法示例：ALTER COLUMN column_name SET DEFAULT value 或 ALTER COLUMN column_name DROP DEFAULT
ENGINE	更改表的存储引擎	用法示例：ENGINE = new_engine_name
CONVERT TO	将表的字符集转换为指定的字符集	用法示例：CONVERT TO CHARACTER SET utf8mb4
AUTO_INCREMENT	更改 AUTO_INCREMENT 的初始值或步长	用法示例：AUTO_INCREMENT = value
DROP PRIMARY KEY	删除主键约束	用法示例：DROP PRIMARY KEY
ADD PRIMARY KEY	添加主键约束	用法示例：ADD PRIMARY KEY (column_name)

• UNSIGNED：

  • 这表示该列的整数值不允许为负数，因此范围从 0 到正数最大值。如果没有 UNSIGNED。

• ZEROFILL：

  • 表示显示宽度，用前导零填充。
    • 例如，如果 a 列的值为 42，在应用了 ZEROFILL 和 INT(5) 后，它将显示为 00042。
    • 使用 ZEROFILL 时，MySQL 会自动将列设为 UNSIGNED，即使未明确指定 UNSIGNED，因为负数不能用零填充。

替换(REPLACE)

1. “主键或者唯一键没有冲突，则直接插入”：

• 如果插入的新数据（在 REPLACE INTO 语句中指定的数据）在表中不存在相同的主键或者唯一键值，那么该数据将直接插入表中。
• 这种情况下，影响的行数是 1，因为只进行了插入操作。

2. “主键或者唯一键如果冲突，则删除后再插入”：

• 如果要插入的新数据中的主键或唯一键值在表中已经存在，则会发生冲突。
• 在这种情况下，REPLACE INTO 会先删除表中已有的那一行（或多行，如果存在多个冲突的唯一键），然后再将新数据插入。
• 因此，影响的行数会是 2：一行被删除，一行被插入。

3. 示例解释：

• REPLACE INTO students (sn, name) VALUES (20001, '曹阿瞒');
• 这条语句表示要向 students 表中插入一条数据，sn 是学生编号，name 是学生姓名。
• 如果 sn 为 20001 的记录在表中不存在，则这条记录会直接插入表中，影响的行数是 1。
• 如果 sn 为 20001 的记录已经存在（也就是冲突了），那么数据库会先删除现有的记录，再插入这条新的记录，影响的行数是 2。

注意：

• REPLACE INTO 是一种原子操作，确保要么插入新记录，要么用新记录替换冲突的旧记录。

检索（Retrieve）

在MySQL中，”Retrieve” 通常指的是从数据库中检索数据的操作。也就是说，”Retrieve” 代表从数据库中查询数据的过程，通常是通过 SELECT 语句来实现的。

SELECT 语句用于从一个或多个表中获取数据，例如：

SELECT * FROM users;

上面的 SQL 语句就是在检索 users 表中的所有记录。你可以通过指定条件来检索特定的记录，如：

SELECT * FROM users WHERE age > 30;

这个语句将检索 users 表中所有 age 大于 30 的用户记录。

所以，”Retrieve” 在 MySQL 的上下文中，就是指使用 SQL 查询语句从数据库中提取数据的过程。

全列查询

例如：

SELECT * FROM exam_result;

• 查询的列越多，意味着需要传输的数据量越大。

• 可能会影响到索引的使用。

指定列查询(不需要按表中定义的顺序)

例如：

SELECT id, name, english FROM exam_result

表达式查询和去重

1. 为查询结果指定别名

为查询结果指定别名是指给查询中的某个字段、表达式或整个表的查询结果赋予一个新的名字（别名）。

示例：

SELECT price * quantity AS total_price FROM orders;

• total_price 是别名。原本 price * quantity 生成的结果列会显示为 total_price。别名可以让查询结果更具可读性，特别是当表达式较为复杂时。

SELECT o.total_price, c.customer_name 
FROM (SELECT price * quantity AS total_price, customer_id FROM orders) o 
JOIN customers c ON o.customer_id = c.id;

• 这里 o 是子查询的别名，方便在外层查询中引用。

2. 结果去重

结果去重是指在查询结果中删除重复的行，使得每一行在结果集中都是唯一的。为了实现结果去重，可以使用 DISTINCT 关键字。

示例：

SELECT DISTINCT country FROM customers;

• 这条语句将返回 customers 表中所有不同的 country（国家）值。如果有多个客户来自同一个国家，结果中该国家只会出现一次。

也可以对多个字段去重：

SELECT DISTINCT first_name, last_name FROM customers;

• 这将确保结果中的每一行由唯一的 first_name 和 last_name 组合构成。

WHERE条件

• WHERE子句用于筛选数据，以限制查询返回的结果集。允许指定一个或多个条件，只有满足这些条件的行才会包含在查询的结果中。

• WHERE支持多种操作符，没有WHERE子句，则更新全表。

比较操作符

操作符	说明	示例
=	等于	age = 30
<>	不等于	age <> 30
>	大于	salary > 5000
<	小于	salary < 5000
>=	大于等于	age >= 30
<=	小于等于	age <= 30
BETWEEN	在某个范围内	age BETWEEN 20 AND 30
IN	在多个值中任意一个	department IN ('Sales', 'HR')
LIKE	模糊匹配	name LIKE 'J%'
IS NULL	值为NULL	address IS NULL
IS NOT NULL	值不为NULL	address IS NOT NULL

逻辑操作符

操作符	说明	示例
AND	所有条件为真	age > 30 AND salary > 5000
OR	任一条件为真	age > 30 OR department = 'HR'
NOT	取反	NOT (age < 30)

这些操作符可以组合使用，来实现更加复杂的查询条件。

结果排序

ORDER BY可以按照指定的列或表达式对结果集进行升序或降序排列。

• 例如，按照 age 升序并且 salary 降序排序：

  SELECT * FROM employees
  ORDER BY age ASC, salary DESC;

• ASC（升序）：默认排序方式，从小到大排列（如数字从小到大，字母按字典顺序）。

• DESC（降序）：从大到小排列。

截断表（TRUNCATE TABLE）

• 是一种用于快速删除表中所有数据的操作。与 DELETE 操作不同，TRUNCATE 操作不会逐行删除数据，而是直接清空表内容，同时保留表结构及其所有索引，只能对整表操作。

TRUNCATE TABLE 特性：

1. 速度快：由于它不生成单行的删除日志记录，因此比 DELETE 更快。
2. 不能回滚：TRUNCATE 操作通常是不可回滚的（视数据库的存储引擎而定）。
3. 自增列重置：如果表有自增列，TRUNCATE 会将该列的计数器重置为初始值。
4. 外键约束：在有外键约束的表中，不能直接使用 TRUNCATE，需要先禁用外键检查。

语法：

TRUNCATE TABLE table_name;

聚合函数

函数	说明
COUNT([DISTINCT] expr)	返回查询到的数据的数量
SUM([DISTINCT] expr)	返回查询到的数据的总和，不是数字没有意义
AVG([DISTINCT] expr)	返回查询到的数据的平均值，不是数字没有意义
MAX([DISTINCT] expr)	返回查询到的数据的最大值，不是数字没有意义
MIN([DISTINCT] expr)	返回查询到的数据的最小值，不是数字没有意义

例如：返回 > 70 分以上的数学最低分

SELECT MIN(math) FROM exam_result WHERE math > 70;

表的约束

表的约束是数据库的一种机制，用于确保数据的完整性和一致性。通过合理使用这些约束，可以避免数据错误和维护数据之间的关系，确保数据库的高效和可靠性。

1. PRIMARY KEY（主键约束）

• 作用：用于唯一标识表中的每一行数据。一个表只能有一个主键，但主键可以由多列组成（称为复合主键）。key 中 pri表示主键。
  • 复合主键

• 特点：
  • 每个主键值必须是唯一的。
  • 主键列不能包含 NULL 值。
  • 主键通常被用于建立表与表之间的关系。

2. FOREIGN KEY（外键约束）

• 作用：用于在两个表之间建立关系，确保一个表中的数据与另一个表中的数据保持一致性。

• 如果两张表在业务上是有相关性的，但是在业务上没有建立约束关系，那么就可能出现问题。通常就是通过外键约束完成的。

• 建立外键的本质其实就是把相关性交给mysql去审核，提前告诉mysql表之间的约束关系，那么当用户插入不符合业务逻辑的数据的时候，mysql将不允许插入。

• 特点：

  • 外键指向另一个表的主键或唯一键。
  • 外键约束可以防止在引用表中删除或更新主键值，从而保证数据的完整性。

使用案例：

这段代码展示了如何使用外键约束来维护两个表之间的关系，以及在插入数据时外键约束如何确保数据的完整性。

1. 创建 myclass 表

CREATE TABLE myclass (
    id INT PRIMARY KEY,
    name VARCHAR(30) NOT NULL COMMENT '班级名'
);

• **id INT PRIMARY KEY**：id 列是主键，用来唯一标识每个班级。
• **name VARCHAR(30) NOT NULL COMMENT '班级名'**：name 列是班级名称，数据类型是可变长度的字符串（最多 30 个字符），且不能为空。COMMENT '班级名' 表示该列的注释是 “班级名”。

2. 创建 stu 表

CREATE TABLE stu (
    id INT PRIMARY KEY,
    name VARCHAR(30) NOT NULL COMMENT '学生名',
    class_id INT,
    FOREIGN KEY (class_id) REFERENCES myclass(id)
);

• **id INT PRIMARY KEY**：id 列是主键，用来唯一标识每个学生。
• **name VARCHAR(30) NOT NULL COMMENT '学生名'**：name 列是学生名称，数据类型是可变长度的字符串（最多 30 个字符），且不能为空。COMMENT '学生名' 表示该列的注释是 “学生名”。
• **class_id INT**：class_id 列用于存储学生所属班级的 ID，数据类型是整数。
• **FOREIGN KEY (class_id) REFERENCES myclass(id)**：class_id 列被设置为外键，引用 myclass 表中的 id 列。外键约束确保 class_id 的值必须是 myclass 表中存在的 id，或为 NULL。

3. 向 myclass 表插入数据

INSERT INTO myclass VALUES(10, 'C++大牛班'),(20, 'java大神班');

• 插入两条记录：
  • id 为 10，name 为 “C++大牛班”。
  • id 为 20，name 为 “java大神班”。

4. 向 stu 表插入数据

INSERT INTO stu VALUES(100, '张三', 10), (101, '李四', 20);

• 插入两条记录：
  • 第一条记录中，id 为 100，name 为 “张三”，class_id 为 10。这表示张三属于 myclass 表中 id 为 10 的班级，即 “C++大牛班”。
  • 第二条记录中，id 为 101，name 为 “李四”，class_id 为 20。这表示李四属于 myclass 表中 id 为 20 的班级，即 “java大神班”。

此时，这两条插入操作成功，因为 class_id 值 10 和 20 都在 myclass 表中存在。

5. 插入违反外键约束的数据

INSERT INTO stu VALUES(102, 'wangwu', 30);

• 试图插入一条记录，id 为 102，name 为 “wangwu”，class_id 为 30。
• 由于 myclass 表中没有 id 为 30 的记录，所以这条插入语句违反了外键约束，导致插入失败。数据库返回错误 ERROR 1452 (23000)，说明无法插入或更新子表（stu）中的记录，因为没有找到匹配的父键（myclass 表中的 id）。

6. 插入 class_id 为 NULL 的数据

INSERT INTO stu VALUES(102, 'wangwu', NULL);

• 试图插入一条记录，id 为 102，name 为 “wangwu”，class_id 为 NULL。
• 由于外键允许 NULL 值，插入操作成功。

3. UNIQUE（唯一约束）

• 作用：确保一列或多列中的数据在表中是唯一的。
• 特点：
  • 可以在表中设置多个唯一约束。
  • 唯一约束列可以包含 NULL 值（但某些数据库中不允许多行 NULL）。
  • 用于防止重复数据的插入。

4. NOT NULL（非空约束）

• 作用：确保列中的数据不能为 NULL。
• 特点：
  • 适用于必须有值的字段，如用户名、密码等。
  • NOT NULL 约束可与其他约束一起使用（如 PRIMARY KEY）。

5. CHECK（检查约束）

• 作用：用于限定列中的数据必须满足的条件。
• 特点：
  • 例如，确保年龄列的值必须大于 18：CHECK (age >= 18)。
  • 某些数据库不完全支持 CHECK 约束（如早期版本的 MySQL）。

6. DEFAULT（默认值约束）

• 作用：为列设置一个默认值，当插入数据时如果没有提供该列的值，则使用默认值。
• 特点：
  • 例如：DEFAULT CURRENT_TIMESTAMP 可以用于自动设置创建时间。
  • 可以减少插入数据时的手动输入，确保有意义的默认值。

7.AUTO_INCREMENT（自增约束）

• 作用：用于自动生成唯一的递增值，通常用于主键列。
• 特点：
  • 每插入一行，数值自动加 1（默认情况下）。
  • 仅适用于整数类型的列。
  • 一张表最多只能有一个自增长。

8. 唯一键

• 作用：一张表中有往往有很多字段需要唯一性，即数据不能重复。
• 但是一张表中只能有一个主键，但唯一键就可以解决表中有多个字段需要唯一性约束的问题。唯一键允许为空，而且可以多个为空，空字段不做唯一性比较。

约束的使用示例

以下是一个创建表时使用多种约束的示例：

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE DEFAULT CURRENT_DATE,
    salary DECIMAL(10, 2) CHECK (salary > 0),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

解释

• id 列是主键，并且值会自动递增。
• name 列不能为 NULL。
• email 列中的值必须唯一。
• hire_date 列如果未指定值，则默认为当前日期。
• salary 列必须大于 0。
• department_id 列是外键，引用 departments 表的 id 列。