5月21日数据库学习笔记

一、DDL（数据定义语言）

1. 数据库的创建与删除
   创建数据库 ：CREATE DATABASE 语句用于创建新的数据库。
   语法：CREATE DATABASE 数据库名;
   示例：CREATE DATABASE testdb;
   删除数据库 ：DROP DATABASE 语句用于删除已有的数据库。
   语法：DROP DATABASE 数据库名;
   示例：DROP DATABASE testdb;
2. 表的创建、删除与修改
   创建表
   使用 CREATE TABLE 语句创建表，并定义列及其数据类型、约束等。
   语法：
   CREATE TABLE 表名(
   列名1 数据类型 [约束条件],
   列名2 数据类型 [约束条件],
   ...);
   常见数据类型：
   整数类型 ：INT（整数）、BIGINT（大整数）
   字符串类型 ：CHAR(n)（固定长度字符串，n 表示长度）、VARCHAR(n)（可变长度字符串，n 表示最大长度）
   日期类型 ：DATE（日期）、DATETIME（日期和时间）
   数值类型 ：DECIMAL(m, n)（精确小数，m 表示总位数，n 表示小数位数）
   常见约束：
   主键约束 ：PRIMARY KEY，用于唯一标识表中的每一行，不允许为空。
   外键约束 ：FOREIGN KEY (列名) REFERENCES 参照表(参照列)，用于建立表与表之间的关系，确保数据的引用完整性。
   唯一约束 ：UNIQUE，确保列中的值唯一，但允许为空。
   非空约束 ：NOT NULL，指定列必须有值，不能为空。
   示例：
   创建一个学生表，包含学号、姓名、年龄、性别列，其中学号为主键：
   sql
   复制
   CREATE TABLE student (
   id INT PRIMARY KEY,
   name VARCHAR(50) NOT NULL,
   age INT,
   gender CHAR(1)
   );
   删除表 ：DROP TABLE 语句用于删除表。
   语法：DROP TABLE 表名;
   示例：DROP TABLE student;
   修改表结构
   添加列：ALTER TABLE 表名 ADD 列名 数据类型 [约束条件];
   修改列：ALTER TABLE 表名 MODIFY 列名 数据类型 [约束条件];
   删除列：ALTER TABLE 表名 DROP 列名;
   示例：
   为学生表添加一个邮箱列：
   sql

ALTER TABLE student ADD email VARCHAR(100);
修改学生表的年龄列，设置为非空：
sql

ALTER TABLE student MODIFY age INT NOT NULL;
删除学生表的性别列：
sql

ALTER TABLE student DROP gender;
二、DML（数据操作语言）

1. 数据的插入
   插入单行数据
   使用 INSERT INTO 语句向表中插入单行数据。
   语法：
   INSERT INTO 表名 (列名1, 列名2, ...) VALUES (值1, 值2, ...);
   或者，如果插入所有列的数据，可以省略列名部分：
   sql

INSERT INTO 表名 VALUES (值1, 值2, ...);
示例：
向学生表插入一条数据：

INSERT INTO student (id, name, age, gender) VALUES (1, 'Alice', 18, 'F');
插入多行数据
可以使用多次 INSERT INTO 语句，或者使用以下语法一次性插入多行数据：
语法：
INSERT INTO 表名 (列名1, 列名2, ...) VALUES
(值1_1, 值1_2, ...),
(值2_1, 值2_2, ...),
...;
示例：
向学生表插入多条数据：
sql

INSERT INTO student (id, name, age, gender) VALUES
(2, 'Bob', 19, 'M'),
(3, 'Charlie', 20, 'M'),
(4, 'David', 17, 'M');
2. 数据的查询
查询所有列
使用 SELECT * 查询表中的所有列。
示例：
查询学生表的所有数据：
sql

SELECT * FROM student;
查询指定列
使用 SELECT 列名1, 列名2, ... 查询指定的列。
示例：
查询学生表的姓名和年龄列：
sql

SELECT name, age FROM student;
条件查询
使用 WHERE 子句指定查询条件。
示例：
查询年龄大于 18 的学生：
sql

SELECT * FROM student WHERE age > 18;
查询性别为 'M' 且年龄等于 19 的学生：
sql

SELECT * FROM student WHERE gender = 'M' AND age = 19;
查询姓名为 'Alice' 或 'Charlie' 的学生：
sql

SELECT * FROM student WHERE name IN ('Alice', 'Charlie');
查询年龄在 18 到 20 之间的学生：
sql

SELECT * FROM student WHERE age BETWEEN 18 AND 20;
排序查询
使用 ORDER BY 子句对查询结果进行排序，ASC 表示升序（默认），DESC 表示降序。
示例：
按年龄升序查询学生：
sql

SELECT * FROM student ORDER BY age ASC;
按姓名降序查询学生：
sql

SELECT * FROM student ORDER BY name DESC;
3. 数据的更新
使用 UPDATE 语句修改表中的数据。
语法：
UPDATE 表名 SET 列名1 = 值1, 列名2 = 值2, ... [WHERE 条件];
示例：
将学生表中 id 为 2 的学生的年龄更新为 20：
sql

UPDATE student SET age = 20 WHERE id = 2;
将所有学生的年龄增加 1 岁：
sql

UPDATE student SET age = age + 1;
4. 数据的删除
使用 DELETE 语句删除表中的数据。
语法：
DELETE FROM 表名 [WHERE 条件];
示例：
删除学生表中 id 为 4 的学生记录：
sql

DELETE FROM student WHERE id = 4;
删除所有学生记录：
sql

DELETE FROM student;
注：删除所有记录也可以使用 TRUNCATE TABLE 语句，它比 DELETE 更快，但会重置表中的自增主键。
四、案例实践

1. 创建并操作学生管理数据库
   创建数据库和表
   创建学生管理数据库：
   sql

CREATE DATABASE school;
切换到 school 数据库：
sql

USE school;
创建学生表：
sql

CREATE TABLE student (
id INT PRIMARY KEY AUTO_INCREMENT, -- 设置为主键且自动增长
name VARCHAR(50) NOT NULL,
age INT CHECK (age >= 0 AND age <= 150), -- 添加年龄范围检查约束
gender CHAR(1) CHECK (gender IN ('M', 'F')), -- 添加性别检查约束
class_id INT,
email VARCHAR(100) UNIQUE -- 邮箱列设置唯一约束
);
创建班级表：
sql

CREATE TABLE class (
id INT PRIMARY KEY AUTO_INCREMENT,
class_name VARCHAR(50) NOT NULL UNIQUE, -- 班级名称唯一
location VARCHAR(100)
);
为学生表添加外键约束，引用班级表：
sql

ALTER TABLE student
ADD CONSTRAINT fk_class
FOREIGN KEY (class_id) REFERENCES class(id);
插入数据
插入班级数据：
sql

INSERT INTO class (class_name, location) VALUES
('Class 1', 'Building A'),
('Class 2', 'Building B'),
('Class 3', 'Building C');
插入学生数据：
sql

INSERT INTO student (name, age, gender, class_id, email) VALUES
('Alice', 18, 'F', 1, 'alice@example.com'),
('Bob', 19, 'M', 1, 'bob@example.com'),
('Charlie', 20, 'M', 2, 'charlie@example.com'),
('David', 17, 'M', 3, 'david@example.com'),
('Eve', 19, 'F', 2, 'eve@example.com');
查询数据
查询所有学生及其所在班级信息，使用连接查询：
sql

SELECT student.id, student.name, student.age, student.gender, class.class_name, class.location
FROM student
LEFT JOIN class ON student.class_id = class.id;
查询每个班级的学生数量：
sql

SELECT class.class_name, COUNT(student.id) AS student_count
FROM class
LEFT JOIN student ON class.id = student.class_id
GROUP BY class.id, class.class_name;
更新和删除数据
将 Class 2 的位置更新为 'Building D'：
sql

UPDATE class SET location = 'Building D' WHERE class_name = 'Class 2';
删除 id 为 5 的学生记录：
sql

DELETE FROM student WHERE id = 5;
删除 Class 3 班级及其关联的学生记录（注意：由于外键约束，先删除学生记录再删除班级记录）：
sql

DELETE FROM student WHERE class_id = 3;
DELETE FROM class WHERE class_name = 'Class 3';