一、CRUD
CRUD操作是指对数据库进行创建（Create）、读取（Read）、更新（Update）和删除（Delete）等操作的过程。
1.1 数据库操作
创建、选择、删除数据库
# 创建 CREATE DATABASE database_name DEFAULT CHARACTER SET utf8; # 选择 USE database_name; # 查看数据库 SHOW database_name; # 删除 DROP DATABASE database_name;
1.2 表操作
1.2.1 五大约束
1）非空约束（Not Null）
非空约束用于限制列中的值不能为空。这样可以确保表中的特定列不包含空值。
2）自增约束（auto_increment）
可以指定初始值，没有指定默认是0；插入节点的时候会自增。
3）唯一约束（Unique）
唯一约束用于确保表中的某个列或一组列的取值是唯一的，但可以包含空值。一个表可以有多个唯一约束。
4）主键约束（Primary Key ）：
主键约束用于标识表中的唯一记录。一个表只能有一个主键，主键列不能包含重复值或空值。
5）外键约束（Foreign Key ）
外键约束用于维护表之间的关联关系。它定义了一个或多个列与另一个表的主键或唯一键之间的关系。外键约束可以防止无效引用和维护数据的完整性。
1.2.2 创建表
# 创建表 CREATE TABLE table_name (column_name column_type); # 显示表的创建过程 SHOW CREATE TABLE table_name ; # 获取指定表的结构信息 DESC table_name ；
例子
CREATE TABLE IF NOT EXISTS `schedule` ( `id` INT AUTO_INCREMENT COMMENT '编号', `course` VARCHAR(100) NOT NULL COMMENT '课程', `teacher` VARCHAR(40) NOT NULL COMMENT '讲师', PRIMARY KEY (`id`) ) ENGINE=innoDB DEFAULT CHARSET=utf8 COMMENT = '课程表'; SELECT * FROM `schedule`;
NOT NULL表示输入字段不能为NULL，ENGINE指定引擎，DEFAULT CHARSET指定默认编码方式
1.2.3 修改表
# ALTER 修改表 # 添加列:table_name是要修改的表名，column_name是要添加的列名，datatype是列的数据类型 ALTER TABLE table_name ADD column_name datatype; # 修改列 ALTER TABLE table_name MODIFY column_name datatype; # 删除列 ALTER TABLE table_name DROP column_name;
例子
ALTER TABLE `schedule` ADD `begin_time` DATE DEFAULT '2022-10-24'; # 用于修改表schedule中的列begin_time的数据类型为DATE，并通过AFTER指定其放置在列id之后。 ALTER TABLE `schedule` MODIFY `begin_time` DATE AFTER `id`; ALTER TABLE `schedule` DROP `begin_time`;
1.2.3 删除表
# 删除表 DROP TABLE table_name ; # 截断表 TRUNCATE TABLE table_name; # 清空表 DELETE FROM table_name;
1.2.4 表数据的增删改查
# 插入数据 INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN ); # 删除数据 DELETE FROM table_name [WHERE condition]; # 修改数据 UPDATE tableName SET field=new_value [WHERE condition] # 查询数据 SELECT field... FROM table_name [WHERE condition]
1.2.5 去重方式
1）GROUP BY
GROUP BY 是一个在 SQL 查询中使用的子句，用于将结果集按照一个或多个列进行分组，并对每个分组执行聚合函数计算。
SELECT 列1, 列2, ... FROM 表名 WHERE 条件 GROUP BY 列1, 列2, ...
例子，假设有一张名为 orders 的表，包含以下几个列：order_id, customer_id, product_id, 和 order_amount。我们想要按照 customer_id 进行分组，并计算每个客户的订单总金额。可以使用 GROUP BY 来实现这个目标：
SELECT customer_id, SUM(order_amount) as total_amount FROM orders GROUP BY customer_id
查询结果将按照 customer_id 进行分组，并计算每个客户的订单总金额。SUM() 函数用于求和 order_amount 列的值。示例输出：
+-------------+--------------+ | customer_id | total_amount | +-------------+--------------+ | 1 | 1500 | | 2 | 2500 | | 3 | 1800 | +-------------+--------------+
2）SELECT DISTINCT
SELECT DISTINCT 语句用于检索指定列中的唯一值，即去除重复的值。它返回列中不同的值，每个值只出现一次。
SELECT DISTINCT 列 FROM 表名;
例如，假设有一张名为 employees 的表，包含以下几个列：employee_id, first_name, last_name, 和 department。我们希望获取所有不同的部门名称。可以使用 SELECT DISTINCT 来实现这个目标：
SELECT DISTINCT department FROM employees;
查询结果将返回 employees 表中去重后的部门名称。示例输出：
+---------------+ | department | +---------------+ | Sales | | Marketing | | HR | | Finance | +---------------+
二、高级查询
结合实例，介绍高级查询
SET FOREIGN_KEY_CHECKS=0;
SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for class -- ---------------------------- DROP TABLE IF EXISTS `class`; CREATE TABLE `class` ( `cid` int(11) NOT NULL AUTO_INCREMENT, `caption` varchar(32) NOT NULL, PRIMARY KEY (`cid`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of class -- ---------------------------- INSERT INTO `class` VALUES ('1', '1908班'); INSERT INTO `class` VALUES ('2', '2004班'); INSERT INTO `class` VALUES ('3', '2101班'); INSERT INTO `class` VALUES ('4', '2109班'); -- ---------------------------- -- Table structure for course -- ---------------------------- DROP TABLE IF EXISTS `course`; CREATE TABLE `course` ( `cid` int(11) NOT NULL AUTO_INCREMENT, `cname` varchar(32) NOT NULL, `teacher_id` int(11) NOT NULL, PRIMARY KEY (`cid`), KEY `fk_course_teacher` (`teacher_id`), CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of course -- ---------------------------- INSERT INTO `course` VALUES ('1', '数学', '1'); INSERT INTO `course` VALUES ('2', '语文', '2'); INSERT INTO `course` VALUES ('3', '英语', '4'); INSERT INTO `course` VALUES ('4', '理综', '2'); INSERT INTO `course` VALUES ('5', '文综', '1'); -- ---------------------------- -- Table structure for score -- ---------------------------- DROP TABLE IF EXISTS `score`; CREATE TABLE `score` ( `sid` int(11) NOT NULL AUTO_INCREMENT, `student_id` int(11) NOT NULL, `course_id` int(11) NOT NULL, `num` int(11) NOT NULL, PRIMARY KEY (`sid`), KEY `fk_score_student` (`student_id`), KEY `fk_score_course` (`course_id`), CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`), CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`) ) ENGINE=InnoDB AUTO_INCREMENT=58 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of score -- ---------------------------- INSERT INTO `score` VALUES ('1', '1', '1', '10'); INSERT INTO `score` VALUES ('2', '1', '2', '59'); INSERT INTO `score` VALUES ('3', '4', '5', '100'); INSERT INTO `score` VALUES ('4', '5', '4', '67'); INSERT INTO `score` VALUES ('5', '2', '2', '66'); INSERT INTO `score` VALUES ('6', '2', '1', '58'); INSERT INTO `score` VALUES ('8', '2', '3', '68'); INSERT INTO `score` VALUES ('9', '2', '4', '99'); INSERT INTO `score` VALUES ('10', '3', '1', '77'); INSERT INTO `score` VALUES ('11', '3', '2', '66'); INSERT INTO `score` VALUES ('12', '3', '3', '87'); INSERT INTO `score` VALUES ('13', '3', '4', '99'); INSERT INTO `score` VALUES ('14', '4', '1', '79'); INSERT INTO `score` VALUES ('15', '4', '2', '11'); INSERT INTO `score` VALUES ('16', '4', '3', '67'); INSERT INTO `score` VALUES ('17', '4', '4', '100'); INSERT INTO `score` VALUES ('18', '5', '1', '79'); INSERT INTO `score` VALUES ('19', '5', '2', '11'); INSERT INTO `score` VALUES ('20', '5', '3', '67'); INSERT INTO `score` VALUES ('21', '5', '5', '100'); INSERT INTO `score` VALUES ('22', '6', '1', '9'); INSERT INTO `score` VALUES ('23', '6', '2', '100'); INSERT INTO `score` VALUES ('24', '6', '3', '67'); INSERT INTO `score` VALUES ('25', '6', '4', '100'); INSERT INTO `score` VALUES ('26', '7', '1', '9'); INSERT INTO `score` VALUES ('27', '7', '2', '100'); INSERT INTO `score` VALUES ('28', '7', '3', '67'); INSERT INTO `score` VALUES ('29', '7', '4', '88'); INSERT INTO `score` VALUES ('30', '8', '1', '49'); INSERT INTO `score` VALUES ('31', '8', '2', '100'); INSERT INTO `score` VALUES ('32', '8', '3', '67'); INSERT INTO `score` VALUES ('33', '8', '4', '88'); INSERT INTO `score` VALUES ('34', '9', '1', '91'); INSERT INTO `score` VALUES ('35', '9', '2', '88'); INSERT INTO `score` VALUES ('36', '9', '5', '67'); INSERT INTO `score` VALUES ('37', '9', '4', '22'); INSERT INTO `score` VALUES ('38', '10', '1', '90'); INSERT INTO `score` VALUES ('39', '10', '2', '77'); INSERT INTO `score` VALUES ('40', '10', '3', '43'); INSERT INTO `score` VALUES ('41', '10', '4', '87'); INSERT INTO `score` VALUES ('42', '11', '1', '90'); INSERT INTO `score` VALUES ('43', '11', '2', '77'); INSERT INTO `score` VALUES ('44', '11', '5', '43'); INSERT INTO `score` VALUES ('45', '11', '4', '87'); INSERT INTO `score` VALUES ('46', '12', '1', '90'); INSERT INTO `score` VALUES ('47', '12', '2', '77'); INSERT INTO `score` VALUES ('48', '12', '3', '43'); INSERT INTO `score` VALUES ('49', '12', '5', '87'); INSERT INTO `score` VALUES ('50', '13', '3', '87'); INSERT INTO `score` VALUES ('51', '14', '2', '33'); INSERT INTO `score` VALUES ('52', '15', '3', '22'); INSERT INTO `score` VALUES ('53', '15', '5', '11'); INSERT INTO `score` VALUES ('54', '13', '1', '99'); INSERT INTO `score` VALUES ('55', '13', '2', '99'); INSERT INTO `score` VALUES ('56', '13', '4', '67'); INSERT INTO `score` VALUES ('57', '13', '5', '87'); -- ---------------------------- -- Table structure for student -- ---------------------------- DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `sid` int(11) NOT NULL AUTO_INCREMENT, `gender` char(1) NOT NULL, `class_id` int(11) NOT NULL, `sname` varchar(32) NOT NULL, PRIMARY KEY (`sid`), KEY `fk_class` (`class_id`), CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`) ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of student -- ---------------------------- INSERT INTO `student` VALUES ('1', '男', '1', '罗谊'); INSERT INTO `student` VALUES ('2', '女', '1', '戴巧'); INSERT INTO `student` VALUES ('3', '男', '1', '叶黎'); INSERT INTO `student` VALUES ('4', '男', '3', '邵柴'); INSERT INTO `student` VALUES ('5', '女', '1', '韩琪'); INSERT INTO `student` VALUES ('6', '男', '3', '尹伸'); INSERT INTO `student` VALUES ('7', '女', '2', '孙燕'); INSERT INTO `student` VALUES ('8', '男', '2', '廖宽'); INSERT INTO `student` VALUES ('9', '男', '2', '孙行'); INSERT INTO `student` VALUES ('10', '女', '2', '宋贤'); INSERT INTO `student` VALUES ('11', '男', '2', '谭国兴'); INSERT INTO `student` VALUES ('12', '女', '3', '于怡瑶'); INSERT INTO `student` VALUES ('13', '男', '4', '文乐逸'); INSERT INTO `student` VALUES ('14', '男', '4', '邹乐和'); INSERT INTO `student` VALUES ('15', '女', '5', '邓洋洋'); INSERT INTO `student` VALUES ('16', '男', '5', '秦永福'); -- ---------------------------- -- Table structure for teacher -- ---------------------------- DROP TABLE IF EXISTS `teacher`; CREATE TABLE `teacher` ( `tid` int(11) NOT NULL AUTO_INCREMENT, `tname` varchar(32) NOT NULL, PRIMARY KEY (`tid`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of teacher -- ---------------------------- INSERT INTO `teacher` VALUES ('1', '王天一老师'); INSERT INTO `teacher` VALUES ('2', '谢小二老师'); INSERT INTO `teacher` VALUES ('3', '廖阿三老师'); INSERT INTO `teacher` VALUES ('4', '吴启四老师'); INSERT INTO `teacher` VALUES ('5', '谢飞五老师');
2.1 基础查询
-- 全部查询 SELECT * FROM student; -- 只查询部分字段 SELECT `sname`, `class_id` FROM student; -- 别名 列明 不要用关键字 SELECT `sname` AS '姓名' , `class_id` AS '班级ID' FROM student; -- 把查询出来的结果的重复记录去掉 SELECT distinct `class_id` FROM student;
2.2 条件查询
-- 查询姓名为 邓洋洋 的学生信息 SELECT * FROM `student` WHERE `name` = '邓洋洋'; -- 查询性别为 男，并且班级为 2 的学生信息 SELECT * FROM `student` WHERE `gender`="男" AND `class_id`=2;
2.3 范围查询
-- 查询班级id 1 到 3 的学生的信息 SELECT * FROM `student` WHERE `class_id` BETWEEN 1 AND 3;
2.4 判空查询
SELECT * FROM `student` WHERE `class_id` IS NOT NULL; #判断不为空 SELECT * FROM `student` WHERE `class_id` IS NULL; #判断为空 SELECT * FROM `student` WHERE `gender` <> ''; #判断不为空字符串 SELECT * FROM `student` WHERE `gender` = ''; #判断为空字符串
2.5 模糊查询
使用 LIKE 关键字，"%"代表任意数量的字符，”_”代表占位符
-- 查询名字为 m 开头的学生的信息 SELECT * FROM `teacher` WHERE `tname` LIKE '谢%'; -- 查询姓名里第二个字为 小 的学生的信息 SELECT * FROM `teacher` WHERE `tname` LIKE '_小%';
2.6 分页查询
分页查询主要用于查看第N条 到 第M条的信息，通常和排序查询一起使用
使用LIMIT 关键字，第一个参数表示从条记录开始显示，第二个参数表示要显示的数目。表中默认第一条记录的参数为0。
-- 查询第二条到第三条内容 SELECT * FROM `student` LIMIT 1,2;
2.7 查询后排序
ORDER BY field
# ASC：升序, DESC：降序 SELECT * FROM `score` ORDER BY `num` ASC; -- 按照多个字段排序 SELECT * FROM `score` ORDER BY `course_id` DESC,`num` DESC;
2.8 聚合查询
聚合函数 描述
| 聚合函数 | 描述 |
| sum() | 计算某列的总和 |
| avg() | 计算某列的平均值 |
| max() | 计算某列的最大值 |
| min() | 计算某列的最小值 |
| count() | 计算某列的行数 |
SELECT sum(`num`) FROM `score`; SELECT avg(`num`) FROM `score`; SELECT max(`num`) FROM `score`; SELECT min(`num`) FROM `score`; SELECT count(`num`) FROM `score`;
2.9 分组查询
-- 可以把查询出来的结果根据某个条件来分组显示 SELECT `gender` FROM `student` GROUP BY `gender`; -- 分组加group_concat SELECT `gender`, group_concat(`sname`) as name FROM `student` GROUP BY `gender`; -- 分组加聚合 SELECT `gender`, count(*) as num FROM `student` GROUP BY `gender`; -- 分组加条件 SELECT `gender`, count(*) as num FROM `student` GROUP BY `gender` HAVING num > 6;
