一、数据库约束
1.1 约束类型
NOT NULL - 指示某列不能存储 NULL 值(非空)。
UNIQUE - 保证某列的每行必须有唯一的值(唯一)。
DEFAULT - 规定没有给列赋值时的默认值(默认)。
PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列有唯一标识,有助于更快地找到表中的一个特定记录(主键)。
FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性(外键)。
1.2 非空约束 not null
创建表时,可以指定某列不为空;
-- 设置 id 列不为空
create table students(id int not null, sn int, name varchar(20));
查看表结构.
一旦插入不符合约束的数据,会插入失败,避免问题进一步扩大。
1.3 唯一约束 unique
插入数据的时候,会先查找该列是否重复。
-- 创建一个 id 列为唯一约束的 stu 表.
create table stu(id int unique, name varchar(20));
插入违反约束的数据,会出现异常。
1.4 默认约束 default
将没有初始化 name 列,默认为 匿名。
当我们忽略插入该列数据,就会出现设置好的默认值啦~
1.5 主键约束 primary key
表示该列非空且唯一 !
create table stu3(id int primary key, name varchar(20));
不能插入 null 数据;不能插入重复数据;不能设置多个主键。
1.6 自增主键 primary key auto_increment
很多场景下,主键都是一个单纯的数字。
我们可以借助 MySQL 提供的自增主键,每次输入数据的时候,不需要用户手动指定 id 的值了。
create table stu4 (id int primary key auto_increment, name varchar(20));
实际操作,可以看到设置自增主键的 id 列自动增加。
1.7 外键约束 foreign key
外键用于关联其它表的主键或唯一键,语法:
foreign key (字段名) references 主表(列)
案例:
创建班级表 classes,id 为主键
-- 创建班级表,有使用MySQL关键字作为字段时,需要使用``来标识
DROP TABLE IF EXISTS classes;
CREATE TABLE classes (
id INT PRIMARY KEY auto_increment,
name VARCHAR(20),
`desc` VARCHAR(100)
);
创建学生表student,一个学生对应一个班级,一个班级对应多个学生。
使用id为主键,classes_id为外键,关联班级表id。
-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
id INT PRIMARY KEY auto_increment,
sn INT UNIQUE,
name VARCHAR(20) DEFAULT 'unkown',
qq_mail VARCHAR(20),
classes_id int,
FOREIGN KEY (classes_id) REFERENCES classes(id)
);
注意事项:
- 如果给 student 插入的数据中,在对应的 classId 在 class 表中不存在,此时会插入失败。
- 有了外键约束,导致两个表不能随便删除。
二、表的设计
学习设计表的通用手段,抓住实体和实体之间的关系。
而实体之间的关系,一般有四种状态:没关系、一对一、一对多、多对多。
一对一
一个人只有一个身份证,一个身份证对应一个人。
一对多
一个班级拥有多个学生,一个学生只能有一个班级。
多对多
一门课程可以被多个学生选择,学生也可以选择多个课程。
三、结合操作,新增查询结果
插入查询结果.
案例:创建一个学生表 stu1,设计有 id,sn,name 字段。将已有的学生数据从 student 表中,复制到新学生表 stu1 中。
student 表的数据.
插入查询.
insert into stu1(id,sn,name) select id,sn,name from student;
最后结果 stu1 表.
四、聚合查询
4.1 聚合函数
常见的统计总数、计算平均值等操作,可以使用聚合函数来实现。
函数 | 说明 |
---|---|
COUNT | 返回查询到的数据总量 |
SUM | 返回查询数据的总和,不是数字无意义 |
AVG | 返回查询数据的平均值,不是数字无意义 |
MAX | 返回查询数据的最大值,不是数字无意义 |
MIN | 返回查询数据的最小值,不是数字无意义 |
COUNT
-- 统计 student 表有几个同学
select count(*) from student;
-- 统计表中有 qq_mail 的同学,qq_mail 为 NULL 的数据不会计入结果
select count(qq_mail) from student;
SUM
-- 统计数学成绩总分
SELECT SUM(math) FROM exam_result;
-- 不及格 < 60 的总分,没有结果,返回 NULL
SELECT SUM(math) FROM exam_result WHERE math < 60;
AVG
-- 统计平均总分
select AVG(chinese + math + english) 平均总分 from exam_result;
MAX
-- 返回英语最高分
select MAX(english) from exam_result;
MIN
-- 返回>70分的数学最低分
select MIN(math) from exam_result where math > 70;
4.2 GROUP BY 子句
select 语句中使用 group by 子句可以对指定列进行分组查询。
案例:准备测试表以及数据,职员表emp。
create table emp(
id int primary key auto_increment,
name varchar(20) not null,
role varchar(20) not null,
salary numeric(11,2)
);
insert into emp(name, role, salary) values
('马云','服务员', 1000.20),
('马化腾','游戏陪玩', 2000.99),
('孙悟空','游戏角色', 999.11),
('猪无能','游戏角色', 333.5),
('沙和尚','游戏角色', 700.33),
('隔壁老王','董事长', 12000.66);
-- 查询每个角色的最高工资、最低工资和平均工资
select role,max(salary),min(salary),avg(salary) from emp group by role;
4.3 HAVING
group by 子句进行分组之后,如果需要对分组结果再进行条件过滤,不能使用 where 语句,而是要使用 having.
-- 显示平均工资低于 1500 的角色和它的平均工资
select role,max(salary),min(salary),avg(salary) from emp group by role having avg(salary)<1500;
4.4 联合查询 - 笛卡尔积
实际开发中往往数据来自不同的表,所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积。
初始化测试数据:一共有四张表,学生表student,课程表course,班级表classes,成绩表score。
drop table if exists classes;
drop table if exists student;
drop table if exists course;
drop table if exists score;
create table classes (id int primary key auto_increment, name varchar(20), `desc` varchar(100));
create table student (id int primary key auto_increment, sn varchar(20), name varchar(20), qq_mail varchar(20) ,
classes_id int);
create table course(id int primary key auto_increment, name varchar(20));
create table score(score decimal(3, 1), student_id int, course_id int);
insert into classes(name, `desc`) values
('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
('中文系2019级3班','学习了中国传统文学'),
('自动化2019级5班','学习了机械自动化');
insert into student(sn, name, qq_mail, classes_id) values
('09982','黑旋风李逵','xuanfeng@qq.com',1),
('00835','菩提老祖',null,1),
('00391','白素贞',null,1),
('00031','许仙','xuxian@qq.com',1),
('00054','不想毕业',null,1),
('51234','好好说话','say@qq.com',2),
('83223','tellme',null,2),
('09527','老外学中文','foreigner@qq.com',2);
insert into course(name) values
('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');
insert into score(score, student_id, course_id) values
-- 黑旋风李逵
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
-- 菩提老祖
(60, 2, 1),(59.5, 2, 5),
-- 白素贞
(33, 3, 1),(68, 3, 3),(99, 3, 5),
-- 许仙
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
-- 不想毕业
(81, 5, 1),(37, 5, 5),
-- 好好说话
(56, 6, 2),(43, 6, 4),(79, 6, 6),
-- tellme
(80, 7, 2),(92, 7, 6);
简单的将两个表进行笛卡尔积
select * from student,score;
部分运行结果:
一共 160 行数据,我们观察可以发现。
笛卡尔积将 student 表和 score 表进行合并了,新表的列数是两个表的列数之和;新表的行数是两张表的行数之积。
有效数据和无效数据都杂糅在一起。我们需要将无效数据剔除。
两个表都有 student 中的 id,我们将两个表的 student 的 id 对应起来。
select * from student, score where student.id=score.student_id;
筛选出来 20 条有效数据,这时候的表的含义是,每个同学的每门课的成绩。
假设我们只需要 “许仙” 的每门课成绩,要如何操作?
我们只需要在刚才的基础上,再加上一个条件,按照名字筛选下这批数据即可。
select * from student,score where student.id=score.student_id and name='许仙';
4.5 小结
多表查询/联合查询,本质就是 “先做加法,再做减法”。
先把所有可能性通过笛卡尔积罗列出来,然后再设定条件筛选,设定条件的时候,一定至少要指定一个连接条件(两个表之间得有一个带关联关系的 id )
一般实际开发中,谨慎使用多表查询,因为多表查询是一个效率较低的操作。