MySQL的基础操作(二)
目录
一.数据库约束
1.主键约束 (Primary Key)
2.唯一约束 (Unique)
3.外键约束 (Foreign Key):
4.检查约束(Check)
5.默认约束 (Default)
二.聚合查询
1.简单聚合函数
2.GROUP BY子句
3.HAVING子句
三.联合查询
1.内连接
2.左连接
3.右连接
4.子查询
5.合并查询
一.数据库约束
数据库约束是用于保证数据完整性和一致性的重要机制,它可以应用在表的列级别或表级别。以下是常见的数据库约束类型:
1.主键约束 (Primary Key)
- 主键约束用于唯一标识表中的每一行数据。
- 每个表只能有一个主键。
- 主键列不允许包含NULL值。
- 主键可以是单列或多列的组合
示例:
CREATE TABLE STUDENT ( id INT PRIMARY KEY, name VARCHAR(20), qq_mail VARCHAR(20) ); //设定学生表中的id字段为外键
2.唯一约束 (Unique)
- 唯一约束确保列中的数据是唯一的,但允许NULL值。
- 每个表可以有多个唯一约束。
- 可以在单列或多列上定义唯一约束。
示例:
CREATE TABLE student ( id INT UNIQUE, name VARCHAR(20), qq_mail VARCHAR(20) ) //将学生表中的id字段设置为unique,表示为唯一的、不重复的
3.外键约束 (Foreign Key):
- 外键约束用于建立表之间的关联关系。
- 它指定一个或多个列作为外键,这些外键值必须存在于另一个表的主键或唯一约束中。
- 外键可以防止对父表的引用数据进行意外的更改或删除。
语法:
foreign key (字段名) references 主表(列)
create table class( id int primary key, class_name varchar(20), ); //先创建一个班级表class,使id为主键 create table class( id int primary key, name varchar(20), classes_id int, foreign key (classes_id) references class(id) ); //再创建一个学生表,使用id为主键,classes_id为外键,关联班级表id
4.检查约束(Check)
- 检查约束用于限制列中允许的值的范围。
- 可以基于表达式或条件来定义检查约束,如果条件为假,则不允许插入或更新数据。
示例:
CREATE TABLE Grades ( StudentID INT, Score INT CHECK (Score >= 0 AND Score 200; //查找总人数超过200的年级
三.联合查询
实际开发中往往数据来自不同的表,所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积:
在介绍之前,先创建好要示例的表:
create table student ( -> id int , -> name varchar(30), -> e_mail varchar(30), -> class_id int -> ); //创建学生表用于储存学生个人信息 create table grade( -> course varchar(20), -> student_id int, -> score int -> ); //创建成绩表用于储存学生的各科成绩
填充数据后为
1.内连接
- 内连接返回两个表中匹配连接条件的行,如果某行在其中一个表中没有匹配的行,则不会包含在结果集中。
- 内连接使用的语法是 INNER JOIN 或简写形式 JOIN。
- 内连接只返回两个表中连接条件匹配的行,不会返回任何一个表中没有匹配的行。
语法:
select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件; select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件; //两种写法都可以
示例:
select student.name, grade.course, grade.score from student, grade where student.id=grade.student_id; //查询学生的姓名与对应的课程名称和成绩 //注意限定条件是学生表的id与成绩表的student_id相等
结果为:
2.左连接
- 左连接返回左表中的所有行,以及右表中匹配连接条件的行。如果右表中没有匹配的行,则返回 NULL 值。
- 左连接使用的语法是 LEFT JOIN。
- 左连接会保留左表中没有匹配的行,并在右表中没有匹配的行处填充 NULL 值。
语法:
select 字段名 from 表名1 left join 表名2 on 连接条件;
示例:
我们先往学生表添加一个新的学生小何,但是成绩表没有添加对应的成绩
insert into student (name,id,class_id) values ('小何',7,2);
接着查询学生表左连接成绩表
select student.name, grade.course, grade.score from student left join grade on student.id=grade.student_id;
结果为:
可以看到,小何明明没有成绩也出现在结果中了,这就是左连接保留左表多余的数据
3.右连接
- 右连接与左连接相反,返回右表中的所有行,以及左表中匹配连接条件的行。如果左表中没有匹配的行,则返回 NULL 值。
- 右连接使用的语法是 RIGHT JOIN。
- 右连接会保留右表中没有匹配的行,并在左表中没有匹配的行处填充 NULL 值。
语法:
select 字段 from 表名1 right join 表名2 on 连接条件;
示例:
与上方一样添加学生小何,但是查询成绩表右连接学生表
select student.name, grade.course, grade.score from grade right join student on student.id=grade.student_id;
结果为:
可以看到,将两表调换后,小何明明没有成绩也出现在结果中了,这就是右连接保留右表多余的数据
4.子查询
子查询是指嵌入在其他sql语句中的select语句,也叫嵌套查询
示例:
如果现在想寻找与小明同班的同学
select * from student where class_id=(select class_id from student where name='小明');
结果为:
5.合并查询
MySQL中的合并查询指的是使用UNION或UNION ALL操作符将多个SELECT语句的结果合并成一个结果集。合并查询通常用于将多个相似的查询结果合并在一起,以便在单个结果集中检索数据
UNION操作符:
UNION操作符用于合并多个SELECT语句的结果,并去除重复的行。
SELECT column1 FROM table1 UNION SELECT column1 FROM table2;
UNION ALL操作符:
UNION ALL操作符用于合并多个SELECT语句的结果,包括重复的行。
SELECT column1 FROM table1 UNION ALL SELECT column1 FROM table2;