day 2 数据库查 06- 08

day2

-- 如果存在名为school的数据库就删除它 drop database if exists school;

-- 创建名为school的数据库并设置默认的字符集和排序方式 create database school default charset utf8mb4;

-- 切换到school数据库上下文环境 use school;

-- 创建学院表 create table tb_college ( col_id int unsigned auto_increment comment '编号', col_name varchar(50) not null comment '名称', col_intro varchar(5000) default '' comment '介绍', primary key (col_id) ) engine=innodb comment '学院表';

-- 创建学生表 create table tb_student ( stu_id int unsigned not null comment '学号', stu_name varchar(20) not null comment '姓名', stu_sex boolean default 1 comment '性别', stu_birth date not null comment '出生日期', stu_addr varchar(255) default '' comment '籍贯', col_id int unsigned not null comment '所属学院', primary key (stu_id), foreign key (col_id) references tb_college (col_id) ) engine=innodb comment '学生表';

-- 创建教师表 create table tb_teacher ( tea_id int unsigned not null comment '工号', tea_name varchar(20) not null comment '姓名', tea_title varchar(10) default '助教' comment '职称', col_id int unsigned not null comment '所属学院', primary key (tea_id), foreign key (col_id) references tb_college (col_id) ) engine=innodb comment '老师表';

-- 创建课程表 create table tb_course ( cou_id int unsigned not null comment '编号', cou_name varchar(50) not null comment '名称', cou_credit int unsigned not null comment '学分', tea_id int unsigned not null comment '授课老师', primary key (cou_id), foreign key (tea_id) references tb_teacher (tea_id) ) engine=innodb comment '课程表';

-- 创建选课记录表 create table tb_record ( rec_id bigint unsigned auto_increment comment '选课记录号', sid int unsigned not null comment '学号', cid int unsigned not null comment '课程编号', sel_date date not null comment '选课日期', score decimal(4,1) comment '考试成绩', primary key (rec_id), foreign key (sid) references tb_student (stu_id), foreign key (cid) references tb_course (cou_id), unique (sid, cid) ) engine=innodb comment '选课记录表';

 

 

use school;

-- select ..., ..., ... -- from ..., ... -- where ... and ... or ... -- group by ..., ... -- having ... -- order by ... asc, ... desc

-- 查询所有学生的所有信息 select * from tb_student; -- * 实际做了两次查询 不允许写* 要一个一个摆出来alter select stu_id,stu_name,stu_sex,stu_birth,stu_addr,col_id from tb_student;

-- 查询所有课程名称及学分(投影和别名) select cou_name as 课程名称,cou_credit as 学分 from tb_course; -- elias?

-- 查询所有女学生的姓名和出生日期(筛选) select stu_name,stu_birth from tb_student where stu_sex = 0;

-- 查询所有80后学生的姓名、性别和出生日期(筛选) select stu_name ,stu_sex,stu_birth from tb_student where stu_birth between '1980-01-01' and '1989-12-31';

-- 在通用 对列进行分支 select stu_name as 姓名, case stu_sex when 1 then '男' else '女' end as 性别, stu_birth as 生日 from tb_student where stu_birth >= '1980-01-01' and stu_birth <= '1989-12-31';

-- SQL 方言 select stu_name as 姓名, if( stu_sex , '男', '女' ) as 性别, stu_birth as 生日 from tb_student where stu_birth >= '1980-01-01' and stu_birth <= '1989-12-31';

-- 查询所有80后女学生的姓名、性别和出生日期(筛选) select stu_name ,stu_sex,stu_birth from tb_student where stu_birth between '1980-01-01' and '1989-12-31' and stu_sex = 0;-- 查询所有80后女学生的姓名、性别和出生日期(筛选)

-- 查询所有80后或女学生的姓名、性别和出生日期(筛选) select stu_name ,stu_sex,stu_birth from tb_student where stu_birth between '1980-01-01' and '1989-12-31' or stu_sex = 0;

-- 查询姓”杨“的学生姓名和性别(模糊) -- sql 中通配符 % 可以匹配零个或者任意多个字符 select stu_name , stu_sex from tb_student where stu_name like'杨%'; -- 正则表达式写法 很重要 regular expression select stu_name , stu_sex from tb_student where stu_name regexp'杨.'; select stu_name , stu_sex from tb_student where stu_name regexp'^杨.$'; select stu_name , stu_sex from tb_student where stu_name regexp'^杨.{1,2}$';

-- 查询姓”杨“名字两个字的学生姓名和性别(模糊) select stu_name,stu_sex from tb_student where stu_name like'杨_';

-- 查询姓”杨“名字三个字的学生姓名和性别(模糊) select stu_name,stu_sex from tb_student where stu_name like'杨__';

-- 查询名字中有”不“字或“嫣”字的学生的姓名(模糊) -- 提示前面带%的模糊查询 性能很差 匹配起来很慢 ,放后面都还好 select stu_name from tb_student where stu_name like '%不%' or stu_name like'%嫣%';

-- 并集 union 会自动去掉重复的值 select stu_name from tb_student where stu_name like '%不%' union select stu_name from tb_student where stu_name like '%不%' ;

-- 并集 union all 会保留重复的值 select stu_name from tb_student where stu_name like '%不%' union all select stu_name from tb_student where stu_name like '%不%' ;

-- 查询没有录入家庭住址的学生姓名(空值) -- null作任何运算,结果都是null值 ,null相当于条件不成立 select stu_name from tb_student where stu_addr is null; select stu_name from tb_student where stu_addr <=> null;

-- 查询录入了家庭住址的学生姓名(空值) -- 不等号 <> !=(用的很少) select stu_name from tb_student where stu_addr is not null;

-- 查询学生选课的所有日期(去重) select distinct sel_date from tb_record;

-- 查询学生的家庭住址(去重) select distinct stu_addr from tb_student where stu_addr is not null;

-- 查询男学生的姓名和生日按年龄从大到小排列(排序) -- order by 后面可以跟多个列用,隔开. select stu_name, stu_birth, stu_id from tb_student where stu_sex = 1 order by stu_birth asc,stu_id desc;

-- 从大到小 降序 select stu_name, stu_birth from tb_student where stu_sex = 1 order by stu_birth desc;

-- 统计学: -- -- 描述型统计:能拿到全部数据 -- - 集中趋势:均值 中位数 众数 -- - 离散趋势:最大值 最小值 极差(ptp) 方差 标准差 -- - 相关性:协方差,相关系数(Spearman Pearson Kindall) -- -- 推断型统计:用样本推断总体 -- - T检验和F检验 :样本的均值和方差能不能代表总体的均值和方差

-- 方差分析:检查数据的改变是否是随机波动造成的,是否具有显著性

-- SQL获取数据的描述性统计信息 的函数 -- -- sum avg min max count stddev (标准差)variance (方差) -- 查询年龄最大的学生的出生日期(聚合函数) select now() from dual; -- dual是一个伪表 select now() ; -- '2021-06-08 16:28:39' select curdate() ; -- '2021-06-08 ' select curdate()+0 ; -- 2021-06-08 -- datediff(data1,data2) -- 前面日期 减后面日期

select min(stu_birth) as 生日, floor(datediff(curdate(),min(stu_birth))/365) as 年龄 from tb_student;

-- 查询年龄最小的学生的出生日期(聚合函数) select max(stu_birth) from tb_student;

-- 查询所有考试平均成绩 -- ju聚合函数 遇到空值会做忽略处理 -- 做计数使用,建议使用count() 这样才不会漏掉空值 select avg(score) from tb_record; select sum(score) / count(score) from tb_record; select sum(score) / count() from tb_record;

-- 查询男女学生的人数(分组和聚合函数) -- SAC (Split - Aggregate --Combine) select if(stu_sex,'男','女') as 性别, count(*) from tb_student group by stu_sex;

-- 统计每个学院男女的人数 select if(stu_sex,'男','女') as 性别, count(*) from tb_student group by col_id,stu_sex;

-- 查询课程编号为1111的课程的平均成绩(筛选和聚合函数) select avg(score) as 平均成绩 from tb_record where cid = 1111;

-- 查询学号为1001的学生所有课程的平均分(筛选和聚合函数) select avg(score) from tb_record where sid = 1001;

-- 查询每个学生的学号和平均成绩(分组和聚合函数) select sid as 学号, round(avg(score),1) as 平均分 from tb_record group by sid;

-- 查询平均成绩大于等于90分的学生的学号和平均成绩 -- 分组以前的数据筛选 用where 分组以后的赛选用having select sid as 学号, round(avg(score),1) as 平均分 from tb_record group by sid having 平均分 >=90;

-- 查询年龄最大的学生的姓名(子查询) select stu_name from tb_student where stu_birth = (select min(stu_birth) from tb_student);

-- 查询年龄最大的学生姓名和年龄(子查询+运算) select stu_name as 姓名, floor(datediff(curdate(),stu_birth)/365) as 年龄 from tb_student where stu_birth = (select min(stu_birth) from tb_student); -- 查询选了两门以上的课程的学生姓名(子查询/分组条件/集合运算) select stu_name from tb_student where stu_id = any( select sid from tb_record group by sid having count(cid) >= 2);