一、聚合函数
mysql数据库中内置的一些数据统计函数;
1. count(*):统计结果条数
统计英语不及格的人数:
select count(*) from stu_tb where en<60;
2. sum(fields_name):对指定字段数据求和
统计语文成绩之和:
select sum(ch) from stu_tb;
3. avg(fields_name):对指定字段数据求平均值
统计语文平均成绩:
select avg(ch) from stu_tb;
4. max(fields_name):对指定字段求最大值
求语文成绩最高的:
select max(ch) from stu_tb;
5. min(fields_name):对指定字段求最小值
求语文成绩最低的:
select min(ch) from stu_tb;
二、分组查询—group by … having
以指定字段为依据,对表中数据进行分组统计。
操作:group by … having
示例:员工表中,包含有:员工姓名。员工岗位,员工薪资。
查询1:公司中都有哪些岗位,每个岗位的最大薪资是多少?最小薪资是多少?平均薪资是多少?
select role, max(salary), min(salary), avg(salary) from emp group by role;
查询2:查找公司中平均薪资大于10000的岗位?
select role, avg(salary) from emp group by role having avg(salary)>10000;
注意事项:
- 分组查询,以表中指定字段对表中数据进行分组统计查询,
但是查询结果中只能有分组依据字段以及聚合函数。 - 分组查询,过滤条件不能使用where子句,而是使用having。
三、键值约束:表的约束
键值约束,起始就是对表中指定字段的描述以及约束。
1. 主键:primary key
约束表中指定字段,值不能为NULL,且数据不能重复。
create table pri2_table(id int primary key, name varchar(32),age int);
主键一张表中只能有一个,但是存在组合主键–以多个字段当做整体作为主键。
create table pri2_table(id int, name varchar(32), age int,
primary key key_name(id, name));
2. 唯一键:unique key
约束表中的指定字段,值不能重复。
create table uni_table(id int primary key,
name varchar(32) unique key, age int unique, sex int, unique key uk(sex));
NULL值并不触发唯一约束。
3. 非空约束:NOT NULL
约束表中指定字段,值不能为NULL。
create table nn_table(id int primary key, name varchar(32) not null);
当表中没有指定主键,但是指定了非空且唯一字段,则这个约束会升级为主键约束。
4. 外键约束:foreign key (…) references ….
限制当前指定字段的数据,必须在另一张表中指定字段数据中存在。
create table class_table(id int primary key, info varchar(32));
create table stu_table(id int primary key, name varchar(32),
class_id int, foreign key(class_id) references class_table(id));
使用外键可以让表中的数据更加严谨,
例如,学生信息中的班级ID,必须在班级表中存在这个班级。
5. 默认值:default
当没有进行主动插入某个字段数据的时候, 则自动以默认值进行新增。
create table def_table(id int primary key, name varchar(32),
sex varchar(1) default "男");
6. 自增属性:auto_increment
只能针对整数的主键字段进行设置, 当不插入数据的时候默认从1开始自增。
create table auto_table(id int primary key auto_increment, name varchar(32));
注:当前面属性缺失后,并不会补全,而是以当前数字继续自增。
四、数据库表的设计
1. ER图-实体关系图
以方形作为实体,以圆形作为属性,以菱形作为关系,描述表与表之间的关系。
图形化表示实体之间的关系,通过不同的实体间的关系,决定如何设计以及关联数据库表。
1.1 一对一:表中必须具有唯一标识作为主键。
1.2 一对多:在多方表中添加少的一方的主键字段。
1.3 多对多:建立一个中间关系实体来关联两个实体。
2. 三大范式
范式:数据库表的设计规范
数据库是存储数据的仓库,但是数据并不是仅仅存储就够了,还有查询,
然而一旦数据库表的设计不够合理,则查询或者插入修改效率大大降低。
2.1 第一范式:表中所有字段必须保证原子特性。
例如:下表中的家庭信息和学校信息不具备原子特性;
当字段不具备原子特性时,则查询效率会大大降低,因为数据的查询需要通过模糊匹配才能实现。
2.2 第二范式:表中所有字段都必须与主键完全相关,而不能部分相关(主要针对组合主键的使用)
例如:下表中的订单信息表设计存在问题:
表中字段并没有和组合主键完全相关,导致表中出现大量的数据冗余。
修改后:
2.3 第三范式:表中字段必须与主键直接相关,不能间接相关。
例如:下表中的班主任信息数据产生了冗余:
修改:
五、多表联查-联合查询
将多张表的数据合并在一起进行查询。
1. 表中数据如何合并:取笛卡尔积
当对两张表中的数据进行取笛卡尔积,会构造一张新表,然后在新表中进行数据查询:
比如:1表中有100w数据,2表中有100w数据,笛卡尔积合并后会有:1w亿的数据量。
这样会导致数据查询效率变得非常低,因此我们通常在进行多表连接的时候进行一些合并规则设置:
例如:student.classes_id=classes.id;
设置这个合并规则后,只有符合规则的数据才会被合并到一起构建成新表。
在这种模式下,合并就有三种不同的方式:
具体sql操作:join … on …
2. 内连接—inner join … on …
对两张表进行连接,但是连接的时候只取出符合合并规则的数据交集。
内连接语句:inner join … on …
select * from classes inner join stu on classes.id=stu.classes_id;
3. 外连接
以两张表中某张表为基表,然后在另一张表中找到符合合并规则的数据,将其合并过来。
3.1 左连接—left join … on …
以左表为基表,在右表中查找符合规则的数据进行合并,没有符合规则的数据则连接数置NULL
左连接sql语句:left join … on …
select * from classes left join stu on classes.id=stu.classes_id;
3.2 右连接—right join … on …
以右表为基表,在左表中查找符合规则的数据进行合并,没有符合规则的数据则连接数置NULL;
右连接sql语句:right join … on …
select * from classes right join stu on classes.id=stu.classes_id;
4. 特殊连接—自连接:同一张表自己连接自己
例子:显示所有“计算机原理”成绩比“Java”成绩高的成绩信息;
思想:将成绩表进行自连接,使用第一张成绩表中的计算机原理成绩,
与第二张成绩表中的Java成绩进行比较。
举例表:
sql语句:
结果:
5. 子查询
也是多表联查的一种,但是跟连接又有所不同,
它是将一条查询语句的结果,当做一张表,再次进行过滤查询。
简单理解:一条查询语句的条件,是另一条查询语句的结果。
5.1 单行子查询:返回一行记录的子查询
示例:
查询与“不想毕业”同学同班的同学:
select * from student where classes_id=(
select classes_id from student where name='不想毕业');
5.2 多行子查询:返回多行记录的子查询
示例:
查询“语文”或“英文”课程的成绩信息:
5.2.1 [not] in 关键字
因为子语句结果有多条,因此不能直接使用等号进行判断,
而要使用包含in,表示只要在子查询结果中就符合条件。
– 使用in
查询“语文”或“英文”课程的成绩信息:
select * from score where course_id in (
select id from course where name='语文' or name='英文');
– 使用 not in
查询不是“语文”和“英文”课程的成绩信息:
select * from score where course_id not in (
select id from course where name!='语文' and name!='英文');
5.2.2 [not] exists 关键字
exists主要判断子语句是否有结果,或者说是一个真或假的判断判断;
查询“语文”或“英文”课程的成绩信息:
(1)先从score表中获取一条成绩信息;
(2)从course表中查询课程信息,课程必须是语文或英文,
前提是这个课程id必须与一个成绩的课程id相同;
(3)判断获取到的成绩的课程id,是否与课程信息的id相同;
(4)判断为真,则表示整条成绩信息可以获取,否则这条成绩信息被丢弃。
语句:
select * from score where exists (
select score.id from course course where (
name='语文' or name='英文') and course.id = score.course_id);
简单理解:
在score表中查询成绩信息,每取出一条,就会通过子查询判断是否是语文或者英文的课程成绩,如果是则取出,不是则丢弃。
5.2.3 in与exists对比
in
:先将子语句执行完毕,得到的结果放到内存中,然后进行主语句执行,然后逐个判断主语句结果是否在子集中,符合条件则取出来。
适用于自语句查询结果不太多的情况。exists
:先将主语句进行执行,取出一条结果,然后拿结果作为子语句的条件进行查询,有结果则为真,则数据符合条件取出,否则丢弃。
适用于子语句查询结果较多的情况。
6. 合并查询—union & union all
将两条查询语句的结果合并到一起。
操作语句:union & union all
union
该操作符用于取得两个结果集的并集。当使用该操作符时, 会自动去掉结果集中的重复行。union all
该操作符用于取得两个结果集的并集。当使用该操作符时, 不会去掉结果集中的重复行。
例子:查询语文和英语的成绩:
英语成绩:
select * from score where course_id =4;
语文成绩:
select * from score where course_id =6;
合并查询:
select * from score where course_id =4;
union
select * from score where course_id =6;