数据库的基本操作（约束与DQL查询）-EW帮帮网

一、约束

约束是在表上强制执行的数据规则，用于确保数据的完整性和一致性

（1）约束类型

MySQL中支持多种约束类型：

①主键约束（PRIMARY KEY） ②自增约束（AUTO_INCREMENT）

③非空约束（NOT NULL） ④唯一约束（UNIQUE）

⑤默认约束（DEFAULT） ⑥零填充约束（ZEROFILL）

⑦外键约束（FOREIGN KEY）

可以简单写一个实例加深对约束类型的记忆

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    age INT DEFAULT 0,
    status TINYINT NOT NULL DEFAULT 1
);

（2）主键约束

主键约束用于唯一标识表中的每一行数据，具有以下特点：

①主键值必须唯一 ②主键值不能为NULL

③一个表只能有一个主键 ④主键可以由单个列或多个列组成

单列主键

-- 方式1
CREATE TABLE table_name (
    column_name data_type PRIMARY KEY,
    ...
);

-- 方式2
CREATE TABLE table_name (
    column_name data_type,
    ...,
    PRIMARY KEY (column_name)
);

联合主键

由多个列组成的主键称为联合主键

CREATE TABLE table_name (
    column1 data_type,
    column2 data_type,
    ...,
    PRIMARY KEY (column1, column2, ...)
);

修改表结构添加主键

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

删除主键

ALTER TABLE table_name DROP PRIMARY KEY;

其他约束

①自增约束

自增约束用于生成唯一的标识符，通常与主键一起使用，自增的列必须是整数类型，默认从1开始

②非空约束

非空约束确保列不能储存NULL值，如果传入NULL值则会报错

③唯一约束

唯一约束确保列中的值唯一，与主键不同，这里允许NULL的存在，一个表可以有多个唯一约束

④默认约束

默认约束为列指定的默认值，当插入数据不指定该列的值，则使用默认值

⑤零填充约束

零填充约束用于数字类型，当数字长度小于指定长度时，前面补0

二、DQL——数据查询语言（单表）

DQL用于从数据库中查询数据，最常用的语句为SELECT

查询查询，那当然是有对象才能查询，那么我在这里创建一个实例，以便后面介绍各个查询语句

-- 创建学生表
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    gender VARCHAR(10),
    age INT,
    score DECIMAL(5,2),
    class_id INT
);

-- 插入测试数据
INSERT INTO students (name, gender, age, score, class_id) VALUES
('张三', '男', 20, 88.5, 1),
('李四', '男', 22, 92.0, 1),
('王五', '女', 21, 95.5, 2),
('赵六', '女', 19, 78.5, 2),
('钱七', '男', 23, 85.0, 1);

简单查询

-- 查询所有列
SELECT * FROM students;

-- 查询指定列
SELECT name, age, score FROM students;

-- 列别名
SELECT name AS 姓名, age AS 年龄, score AS 成绩 FROM students;

-- 去重
SELECT DISTINCT class_id FROM students;

运算符

-- 等于
SELECT * FROM students WHERE age = 20;

-- 不等于
SELECT * FROM students WHERE age != 20;

-- 大于
SELECT * FROM students WHERE score > 80;

-- BETWEEN...AND
SELECT * FROM students WHERE age BETWEEN 20 AND 22;

-- IN
SELECT * FROM students WHERE class_id IN (1, 3);

-- IS NULL
SELECT * FROM students WHERE gender IS NULL;

-- 逻辑运算符
SELECT * FROM students WHERE age > 20 AND score > 85;

在这里给出前三个结果，剩下的可以自行测试

常用查询

①排序查询

排序查询主要有三个：升序、降序和多列排序

-- 升序排序
SELECT * FROM students ORDER BY age ASC;

-- 降序排序
SELECT * FROM students ORDER BY score DESC;

-- 多列排序
SELECT * FROM students ORDER BY class_id ASC, score DESC;

升序排序：

多列排序：

②聚合查询

-- 统计学生总数
SELECT COUNT(*) AS 学生总数 FROM students;

-- 统计男生数量
SELECT COUNT(*) AS 男生数量 FROM students WHERE gender = '男';

-- 计算平均成绩
SELECT AVG(score) AS 平均成绩 FROM students;

-- 计算总成绩
SELECT SUM(score) AS 总成绩 FROM students;

-- 最高分和最低分
SELECT MAX(score) AS 最高分, MIN(score) AS 最低分 FROM students;

③聚合查询——null值处理

（1）COUNT(*) 包含NULL值的行

（2）COUNT(列名) 不包含NULL值的行

（3）SUM、AVG、MAX、MIN 忽略NULL值

④分组查询

分组查询：GROUP BY 列名; 分组后筛选：HAVING 条件;

-- 按班级分组，统计每个班级的学生数量
SELECT class_id, COUNT(*) AS 学生数量 FROM students GROUP BY class_id;

-- 按班级分组，计算每个班级的平均成绩
SELECT class_id, AVG(score) AS 平均成绩 FROM students GROUP BY class_id;

-- 分组后筛选，只显示学生数量大于2的班级
SELECT class_id, COUNT(*) AS 学生数量 FROM students GROUP BY class_id HAVING COUNT(*) >
2;

⑤分页查询

分页查询：LIMIT 偏移量,每页数量；偏移量从0开始

-- 每页显示2条数据，查询第一页
SELECT * FROM students LIMIT 0, 2;

-- 每页显示2条数据，查询第二页
SELECT * FROM students LIMIT 2, 2;

数据库的基本操作（约束与DQL查询）