分组聚合查询两门以上MySQL_MySQL语法外键、分组、聚合、子查询

条件语句的写法

在sql中可以通过 ‘where 条件语句’ 来对操作对象进行筛选 - 筛选

a. 比较运算符：= ，<> , > ,< ,<=,>=

注意： 判断一个字段一个字段的值是否为空不能使用=和<>，而是使用 ‘is null’ 和 ‘is not null’。判断是否是空串– 字段名=’’

b. 逻辑运算符： and or not

c. where 字段名 between 值1 and 值2 —筛选出指定字段的值在值1和值2之间1select stuname,birth from t_student where birth between '1990-1-1' and '1999-12-31';

d. where 字段名 in 集合; — 筛选出字段的值是集合的元素。集合是用()括起来，里面有多个值1select * from t_student where stuname in ('小花','小明','路飞');

e. like操作数据类型

– varchar(size):不定长字符串，字符串最长为size的值

– char(size) : 定长字符串，

– text: 不限长度，最长255个字符。

– int/tinyint(-128~127) 、bigint

– float(size,d)/double(size,d) — size是数字长度，d是小数总位数

– bit : 只有0和1 两个值

– date/datetime/time: 值可以是时间函数的结果，也可以是时间字符串;计算或比较的时候内部是按时间处理的

3.去重

select distinct 字段名 from 表名; 对查询到的字段名去掉重复的

– ==========添加约束

– 1.创建表的时候添加约束1

2

3

4

5

6create table if not exists t_college(collid int,

collname varchar(20) not null, #创建表的时候添加约束

website varchar(300),

intro varchar(200),

primary key(collid)

);

– 2.通过添加约束索引的方式添加约束

– alter table 表名 add constraint 索引名 约束名 (字段名);

– 说明：索引名是自己随便命名，用来指向当前添加的约束。

alter table t_college add constraint con_website unique (website); – 给t_college表中的website添加unique约束，约束索引名con_website

– 2.1删除约束

– alter table 表名 drop index 约束索引名;

alter table t_college drop index con_website;外键和E.R图

– 1.什么是外键：表中某个字段的值是根据其他表中主键的值来确定的。那么这个字段就是外键

– 1.1多对1外键的添加，将外键添加到多的一方对应的表中

– 一对一的外键添加：将外键随便添加到哪一方，并添加值唯一约束

– 多对多的外键添加： 关系型数据中，两张表无法实现多对多的关系，需要一个中间表。(中间表有两个外键,分别参照多对多的两个表的主键)

– 1.2怎么添加外键：

– a.添加外键对应的字段

alter table 表名 add column 字段名 约束;

– b.给设计好的外键对应的字段添加外键约束

– alter table 表1 add constraint 索引名 foreign key (外键字段名1) references 表2 (被参照的字段名2); — 索引名： 字段1名索引名字段2名，自己取

– - 将表1中的字段1设置为外键，并且让这个外键的值参照表2中的字段2

– c.删除外键约束

alter table 表1 drop foreign key 索引名; – 可以删除外键约束，但是外键索引还在，需要再删除索引

– 注意：删除外键约束的时候，直接删除约束的索引无效，必须先删除约束，然后再删除索引。

alter table 表1 drop index 索引名; – 删除索引

– d. 多对多的外键约束1

2

3

4

5

6

7

8

9create table if not exists tb_score(

scoreid int not null auto_increment,

mark float(4,1),

stuid int comment '学生外键',

couid int comment '课程外键',

primary key(scoreid)

);

INSERT INTO tb_score (mark) VALUES (89),(45.5),(100),(95),(60),(77),(90),

(97),(49),(66),(56),(71),(80.5),(33),(87),(56),(74),(68),(80),(92),(34),(75);

– 添加学生的外键约束alter table tb_score add constraint fk_stuid_sco

foreign key(stuid)

references tb_student (stuid);

alter table tb_score add constraint fk_couid_sco

foreign key(couid)

references tb_course (couid);

=======查询的高级操作

– 1.聚合：max(),min(),sum()，avg()– 求平均值 ,count() –求个数

– select 聚合函数(字段) from 表名 where 条件; –按条件对表中指定的字段进行查询，然后将查询结果做相应的聚合运算。聚合运算结果是最终结果。

select mark from tb_score; – 获取表中所有的分数值

select max(mark) as maxmark from tb_score; – 获取这个表中所有分数的最大值

select avg(mark) as avgmark from tb_score;

select count(mark) as countmark from tb_score where mark>90; – 统计大于90的个数

– 计算平均值如果参与运算的对象值为null,那这个数据不会参与运算。求和同样。

– 2.分组

– select 聚合操作(字段1) from 表名 where 条件 group by (字段2); – 将指定表中满足条件的记录按照字段2的值进行分组(值是一样的在一个组里),然后再将每个分组作为整体按照指定的字段进行指定的聚合操作。

– 求每个学生的平均成绩

select stuid,avg(mark) from tb_score group by (stuid);

– 注意：a.字段操作的位置除了分组字段不用聚合，其他字段都必须聚合

– b. 分组的时候where要放到分组前对需要分组的数据进行筛选。

– having – 在分组后用having代替where来对分组后的数据进行筛选

– 获取平均分数大于60的学生id

select stuid from tb_score group by (stuid) having avg(mark)>60;

– 3.子查询:将一个查询操作的结果作为另一个查询的数据源。

select stuid from tb_score where mark>90 and stuid is not null; – 获取成绩大于90分的学生id

select stuname from tb_student where stuid in

(select stuid from tb_score where mark>90 and stuid is not null;);

– 获取成绩大于90分的学生的名字。

– 将一个查询结果作为查询对象给另一个查询，第一个查询需要重命名

select mark from (一个查询结果) as t2;

– =============1.学生表===============

CREATE TABLE IF NOT EXISTS tb_student

(

stuid int not NULL auto_increment,

stuname VARCHAR(20) not NULL,

tel CHAR(11) not NULL,

birth date DEFAULT ‘2019-10-21’,

addr VARCHAR(100),

genter bit,

PRIMARY key(stuid)

);

alter table tb_student add column collid int;

alter table tb_student add constraint tb_stu_coll foreign key (collid) references tb_college (collid);

– ===============2.课程表================

CREATE TABLE IF NOT EXISTS tb_course

(

couid int NOT NULL auto_increment,

couname VARCHAR(20) NOT NULL,

startdate date NOT NULL DEFAULT ‘2019-10-21’,

intro VARCHAR(200),

credit int NOT NULL,

PRIMARY key(couid)

);

alter table tb_course add column teaid int;

alter table tb_course add constraint tb_cour_tea foreign key (teaid) references tb_teacher (teaid);

– =================3.学院表====================

CREATE TABLE IF NOT EXISTS tb_college

(

collid int auto_increment,

website VARCHAR(200) UNIQUE,

collname VARCHAR(50),

PRIMARY KEY(collid)

);

– =================4.老师表====================

CREATE TABLE IF NOT EXISTS tb_teacher

(

teaid int NOT NULL auto_increment,

teaname VARCHAR(20) NOT NULL,

teaage INT,

tel char(11),

PRIMARY KEY(teaid)

);

alter table tb_teacher add column collid int;

alter table tb_teacher add constraint tb_teac_coll foreign key (collid) references tb_college (collid);