Week8学习总结-数据库

Week8学习总结-数据库

一. 关系数据库

1. 关系数据库特点

• 理论基础：关系代数，关系运算，一阶谓词逻辑。
• 具体表象：用二维表（有行和列）组织数据。
• 编程语言：结构化查询语言（SQL - Structured Query Language）。
• 注意：数据库中的数据尽量不要为空值

2. ER模型（实体关系模型）和概念模型图

ER模型，全称为实体关系模型（Entity-Relationship Model），由美籍华裔计算机科学家陈品山先生提出，是概念数据模型的高层描述方式，如下图所示。

• 实体（表） - 矩形框
• 属性（表中的字段或列） - 椭圆框
• 关系（连接线上标注关系的重数） - 菱形框
• 重数 - 1:1（一对一） / 1:N（一对多） / M:N（多对多）

3. 数据库建模工具

实际项目开发中，我们可以利用数据库建模工具（如：PowerDesigner）来绘制概念数据模型（其本质就是 ER 模型），然后再设置好目标数据库系统，将概念模型转换成物理模型，最终生成创建二维表的 SQL（很多工具都可以根据我们设计的物理模型图以及设定的目标数据库来导出 SQL 或直接生成数据表）。

PowerDesigner —> 概念模型图 – 数据库 --> 物理模型图 – 正向工程 --> SQL方言

4. 关系数据库产品

• Oracle - 目前世界上使用最为广泛的数据库管理系统，作为一个通用的数据库系统，它具有完整的数据管理功能；作为一个关系数据库，它是一个完备关系的产品；作为分布式数据库，它实现了分布式处理的功能。在 Oracle 最新的 12c 版本中，还引入了多承租方架构，使用该架构可轻松部署和管理数据库云。
• DB2 - IBM 公司开发的、主要运行于 Unix（包括 IBM 自家的 AIX）、Linux、以及 Windows 服务器版等系统的关系数据库产品。DB2 历史悠久且被认为是最早使用 SQL 的数据库产品，它拥有较为强大的商业智能功能。
• SQL Server - 由 Microsoft 开发和推广的关系型数据库产品，最初适用于中小企业的数据管理，但是近年来它的应用范围有所扩展，部分大企业甚至是跨国公司也开始基于它来构建自己的数据管理系统。
• MySQL - MySQL 是开放源代码的，任何人都可以在 GPL（General Public License）的许可下下载并根据个性化的需要对其进行修改。MySQL 因为其速度、可靠性和适应性而备受关注。
• PostgreSQL - 在 BSD 许可证下发行的开放源代码的关系数据库产品。

5. 关系数据库模型术语

正式的关系模型术语	非正式的日常用语
关系（relation）	表（table）
元组（tuple）	行（row）/ 记录（record）
势（cardinality）	行数（number of rows）
属性（attribute）	列（column）/ 字段（field）
度（degree）	列数（number of columns）
定义域（domain）	列的取值集合（pool of legal values）

二. MySQL

1. 常用命令

注意：每行命令结束后要写 ;

• 查看所有数据库。

  show databases;
  

• 查看所有字符集（编码方式）。

  show character set;
  

• 查看所有的校对规则（排序）。

  show collation;
  

• 查看所有的引擎。

  show engines;
  

• 查看所有日志文件。

  show binary logs;
  

• 查看数据库下所有表。

  show tables;
  

• 获取帮助。

  1. 查看show命令的帮助。

     ? show
     

  2. 查看有哪些帮助内容。

     ? contents
     

  3. 获取函数的帮助。

     ? functions
     

  4. 获取数据类型的帮助。

     ? data types ---> ? decimal
     

     • 整数(integer)：tinyint / smallint / mediumint / int / bigint

       tinyint	smallint	mediumint	int	bigint
       1	2		4
       -128 to 127	-32768 to 32767		-2^31 to 2^31 -1

       • tinyint unsigned - 无符号的整数 —> 0 ~ 255
       • int unsigned - —> 0 ~ 2^32 -1

     • 字符串：char(10) - 定长字符串 / varchar(20) - 变长字符串 / tinytext / mediumtext / longtext

     • 小数：（float / double）都不能用，将来的版本会被移除 / decimal(10,2)

     • 时间日期：year / date / time / datetime / （timestamp / int） —> 时间戳 —> 距离1970-1-1 0:0:0过了多长时间

     • 二进制数据：大的二进制数据，我们通常直接保存文件路径或者一个URL

       • blob —> binary large object 二进制大对象 —> tinyblob / mediumblob / blob / longblob

2. 启动MySQL

Win + R —> services.msc —> 找到MySQL右键启动

三. SQL详解（是结构化查询语言 Structured Query Language）

我们通常可以将SQL分为四类，分别是 DDL（数据定义语言）、DML（数据操作语言）、DQL（数据查询语言）和 DCL（数据控制语言）。DDL 主要用于创建、删除、修改数据库中的对象，比如创建、删除和修改二维表，核心的关键字包括create、drop和alter；DML 主要负责插入数据、删除数据和更新数据，核心的关键词包括insert、delete和update；DQL 负责查询，最重要的一个关键词是select；DCL通常用于授予权限和召回权限，核心关键词是grant和revoke。

说明：SQL 是不区分大小写的语言，为了书写和识别方便，下面的 SQL 都使用了小写字母来书写。

1. DDL：数据定义语言

三个关键词：create / drop / alter

1. create

1. 语法

   create database 数据库名 default charset 字符集;
   create schema 数据库名;
   create table 表名(..., ..., ...)
   

2. 示例

   -- 显示已有的数据库
   show databases; 
   
   -- 切换到mysql数据库
   use mysql; 
   
   -- 显示数据库下的表
   show tables;
   
   -- 显示show命令下的帮助
   ？show
   
   -- 创建名为school的数据库，并设置默认编码方式为utf8mb4(MySQL 8 默认的字符集（编码方式） utf8mb4 - 最大允许4字节的字符)
   create database `school` default charset utf8mb4; 
   
   -- 显示所有字符集
   show charset; 
   
   
   -- 创建表
   -- var(20)：长度可变字符（变长字符串），最大长度20个字符
   -- primary key主键约束，stu_id是独一无二的
   create table `tb_student`(
   `stu_id` int not null,
   `stu_name` varchar(20)
   `stu_sex` boolean not null default 1,
   `stu_birth` date,
   primary key(`stu_id`)
   );  
   

2. drop

1. 语法

   drop database if exists 数据库名;
   drop table if exists 表名;
   

2. 示例

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

3. alter

1. 语法

   -- 改变表设定的字符集
   alter table 表名 convert to 字符集;
   
   -- 增加列
   alter table 表名 add column;
   
   -- 删除列
   alter table 表名 drop column;
   
   -- 修改列
   alter table 表名 modify column;
   
   -- 增加约束
   alter table 表名 add constraint;
   
   -- 删除约束
   alter table 表名 drop constraint;
   

2. 示例

   -- 如果存在名为school的数据库就删除它
   drop database if exists `school`;
   
   -- 创建名为school的数据库并指定默认的字符集
   create database `school` default charset utf8mb4;
   
   -- 切换数据库上下文环境
   use `school`;
   
   drop table if exists `tb_school`;
   
   -- 创建名为tb_student的表
   create table `tb_student`
   (
   `stu_id` int not null comment '学号',
   `stu_name` varchar(20) not null comment '姓名',
   `stu_sex` boolean not null default 1 comment '性别',
   `stu_birth` date comment '出生日期',
   primary key (`stu_id`)
   ) engine=innodb comment '学生表';
   
   -- 创建名为tb_college的表
   -- engine底层保存数据的方式
   create table `tb_college`
   (
   `col_id` int not null comment '学院编号',
   `col_name` varchar(20) not null comment '学院名',
   `col_intro` longtext not null comment '学院介绍',
   primary key (`col_id`)
   ) engine=innodb comment '学院表';
   
   -- 创建名为tb_professor的表
   create table `tb_professor`
   (
   `pro_id` int not null comment '老师编号',
   `pro_name` varchar(20) not null comment '老师姓名',
   `pro_gender` char(1) not null default '男' comment '老师性别' 
   constraint `ck_pro_gender`
   check (`pro_gender` = '男' or `pro_gender` = '女'),
   `pro_birth` date comment '出生日期',
   `pro_title` varchar(20) not null comment '职称',
   `pro_field` varchar(20) not null comment '研究领域'
   ) engine=innodb comment '老师表';
   
   -- 创建名为tb_course的表
   create table `tb_course`
   (
   `course_id` int not null comment '课程编号',
   `course_name` varchar(20) not null comment '课程名',
   `course_time` int not null comment '课时',
   `course_credit` int not null comment '学分',
   primary key (`course_id`)
   ) engine=innodb comment '课程表';
   

• 删除列

  alter table `tb_student` drop column `stu_addr`;
  

• 添加列

  alter table `tb_student` add column 
  `stu_addr` varchar(200) not null default '' comment '家庭住址';
  

• 修改列（不修改列名）- modify

  alter table `tb_student` modify column
  `stu_sex` char(1) not null default '男';
  

• 修改列（修改列名）- change

  alter table `tb_student` change column
  `stu_sex` `stu_gender` char(1) not null default '男';
  

• 添加检查约束 - check

  alter table `tb_student` add constraint `ck_student_sex`
  check (`stu_sex` = '男' or `stu_sex` = '女');
  
  alter table `tb_student` add constraint `ck_student_sex`
  check (`stu_gender` = '男' or `stu_gender` = '女');
  

• 删除检查约束

  alter table `tb_student` drop constraint `ck_student_sex`;
  

• 添加主键约束 - primary key

  alter table `tb_professor` add primary key (`pro_id`);
  

• 删除主键约束

  alter table `tb_professor` drop primary key (`pro_id`);
  

• 添加外键约束 - foreign key … references…

  alter table `tb_student` add constraint `fk_student_col_id`
  foreign key(`col_id`) references `tb_college`(`col_id`);
  

• 删除外键约束

  alter table `tb_student` drop constraint `fk_student_col_id`;
  

1. 主键（primary key）：唯一标识一条记录的列（唯一标识一个元组的属性）
2. 外键（foreign key）：外来的主键（其他表的主键）
   1. 如果需要维护两张表的多对一关系，可以在多的一方添加外键

• 添加名字唯一性约束 - unique

  alter table `tb_college`
  add constraint `uk_college_name` unique (`col_name`);
  

• 删除名字唯一性约束

  alter table `tb_college`
  drop constraint `uk_college_name`;
  

drop database if exists `school`;

create database `school` default charset utf8mb4;

use `school`;

-- auto_increment 自动增长
create table `tb_college`
(
`col_id` bigint unsigned not null auto_increment comment '编号',
`col_name` varchar(50) not null comment '名称',
`col_intro` varchar(1000) not null default '' comment '介绍',
primary key (`col_id`)
) engine=innodb auto_increment=10 comment '学院表';

alter table `tb_college`
add constraint `uk_college_name` unique (`col_name`);

create table `tb_student`
(
`stu_id` int unsigned not null comment '学号',
`stu_name` varchar(20) not null comment '姓名',
`stu_sex` char(1) not null default 'M' comment '性别',
`stu_birth` date comment '出生日期',
`col_id` bigint unsigned not null comment '所属学院',
primary key (`stu_id`)
)engine=InnoDB comment '学生表';

alter table `tb_student` add constraint `ck_student_sex` 
check (`stu_sex`='M' or `stu_sex`='F');

alter table `tb_student` add constraint `fk_student_col_id`
foreign key(`col_id`) references `tb_college`(`col_id`);

create table `tb_teacher`
(
`tea_id` int unsigned not null comment '工号',
`tea_name` varchar(20) not null comment '姓名',
`tea_title` varchar(10) not null default '助教' comment '职称',
`tea_interest` varchar(200) not null default '' comment '研究领域',
`col_id` bigint unsigned not null comment '所属学院',
primary key(`tea_id`),
constraint `fk_teacher_col_id` foreign key(`col_id`) references `tb_college`(`col_id`)
)engine=InnoDB comment '老师表';

create table `tb_course`
(
`course_id` int not null comment '课程编号',
`course_name` varchar(20) not null comment '课程名',
`course_time` int not null comment '课时',
`course_credit` int not null comment '学分',
`tea_id` int unsigned not null comment '老师工号',
primary key (`course_id`),
constraint `fk_course_tea_id` foreign key(`tea_id`) references `tb_teacher`(`tea_id`)
) engine=innodb comment '课程表';

create table `tb_record`
(
`rec_id` bigint unsigned not null auto_increment comment '记录编号',
`stu_id` int unsigned not null comment '学号',
`course_id` int not null comment '课程编号',
`sel_date` datetime not null default current_timestamp comment '选课日期',
`score` decimal(4, 1) comment '成绩',
primary key (`rec_id`),
constraint `uk_record_stu_course` unique(`stu_id`, `course_id`)
) engine=innodb comment '选课记录';

alter table `tb_record` add constraint `fk_record_stu_id`
foreign key(`stu_id`) references `tb_student`(`stu_id`);

alter table `tb_record` add constraint `fk_record_course_id`
foreign key(`course_id`) references `tb_course`(`course_id`);

2. DML：数据操作语言

三个关键词：insert / delete / update

1. insert

1. 语法

insert into 表名(列名, 列名, 列名) values(),(),();

2. 示例

use `school`;

insert into `tb_college` values
(default, '计算机学院', '学习计算机哪家强，山东技校找蓝翔！');

insert into `tb_college` (`col_name`, `col_intro`)
values('外国语学院', '学习外语哪家强，还是山东技校蓝翔！');

insert into `tb_college` (`col_name`, `col_intro`) 
values
('经济管理学院', '666'),
('文学与新闻学院', '欢迎收看新闻联播！'),
('体育学院', '强身健体');

3. 常见错误

-- Duplicate entry '10' for key 'tb_college.PRIMARY'
insert into `tb_college` values (10, '生命科学学院', '探索生命的起源！');

-- Duplicate entry '计算机学院' for key 'tb_college.uk_college_name'
insert into `tb_college` values (99, '计算机学院', '探索生命的起源！');

-- Column count doesn't match value count at row 1
insert into `tb_college` values ('计算机学院', '探索生命的起源！');


insert into `tb_student` values
(1001, '骆昊', 'M', '1980-11-28', 10);

-- Check constraint 'ck_student_sex' is violated.
insert into `tb_student` values
(1002, '骆昊', '男', '1980-11-28', 10);

-- 性别的小写m不影响，字符不区分大小写
insert into `tb_student` values
(1002, '骆昊', 'm', '1980-11-28', 10);

-- Cannot add or update a child row: a foreign key constraint fails (`school`.`tb_student`, CONSTRAINT `fk_student_col_id` FOREIGN KEY (`col_id`) REFERENCES `tb_college` (`col_id`))
insert into `tb_student` values
(1003, '骆昊', 'm', '1980-11-28', 20);

-- Field 'stu_name' doesn't have a default value
insert into `tb_student` (`stu_id`, `col_id`) 
values (1004, 11);

2. update

1. 语法

   update 表名 set 列名=修改的内容 where `primary key` = values;
   
   update 表名 set 列名=修改的内容, 列名=修改的内容, 列名=修改的内容 where `primary key` = values;
   

2. 示例

   insert into `tb_student` (`stu_id`, `stu_name`, `col_id`) 
   values (1004, '王大锤', 11);
   
   
   insert into `tb_student` (`stu_id`, `stu_name`, `col_id`) 
   values (1003, '大牛', 11);
   
   -- 更新tb_student表中stu_id为1003学生的出生日期
   update `tb_student` set `stu_birth` = '1996-08-02' where `stu_id` = 1003;
   
   update `tb_student` set `stu_name` = '小翠', `stu_sex` = 'F', `stu_birth` = '1998-03-05', `col_id` = 13
   where `stu_id` = 1002;
   
   insert into `tb_student` values(1004, '王大锤', 'M', '1995-02-14', '14');
   
   update `tb_student` set `stu_birth` = '1990-04-01' where `stu_id` = 1004;
   
   insert into `tb_student` values(1005, '大司马', 'M', '1980-04-1', '14');
   delete from `tb_student` where `stu_id` = 1005;
   
   update `tb_student` set `stu_name` = '王小锤', `stu_birth` = '1990-1-1', `col_id` = 10
   where `stu_id` = 1004;
   

3. delete

1. 语法

   delete from 表名 where `primary key` = value;
   
   delete from 表名 where `primary key` in (value, value...);
   
   delete from 表名 where `primary key` = value or `primary key` = value;
   

2. 示例

   -- 从tb_student表中删除学号为1004的学生信息
   delete from `tb_student` where `stu_id` = 1004; 
   
   delete from `tb_college` where `col_id` in (12, 13);
   
   delete from `tb_college` where `col_id` = 12 or `col_id` = 13;
   

3. DQL：数据查询语言

1. select

1. 语法

   

2. 示例

   -- 查询所有学生的所有信息(投影)
   select * from `tb_student`;
   select 
   `stu_id`, `stu_name`, `stu_sex`, `stu_birth`, `stu_addr`, `col_id` 
   from `tb_student`;
   
   -- 查询学生的学号、姓名和籍贯(投影)
   select `stu_id`, `stu_name`, `stu_addr` from `tb_student`;
   
   -- 查询所有课程的名称及学分(投影和别名，as 可以省略)
   select `cou_name` as 课程名称, `cou_credit` as 学分 from `tb_course`;
   
   -- 查询所有女学生的姓名和出生日期(筛选)
   select `stu_name`, `stu_birth` from `tb_student` where `stu_sex` = 0;
   
   -- 查询籍贯为“四川成都”的女学生的姓名和出生日期(筛选)
   select `stu_name`, `stu_birth` from `tb_student` 
   where `stu_sex`=0 and `stu_addr`='四川成都';
   
   -- 查询籍贯为“四川成都”的或者性别是女学生的姓名和出生日期(筛选)
   select `stu_name`, `stu_birth` from `tb_student` 
   where `stu_sex`=0 or `stu_addr`='四川成都';
   
   -- 查询所有80后学生的姓名、性别和出生日期(筛选)
   select `stu_name`, `stu_sex`, `stu_birth` from `tb_student` 
   where `stu_birth` >= '1990-1-1' and `stu_birth` <= '1999-12-31';
   
   select `stu_name`, `stu_sex`, `stu_birth` from `tb_student` 
   where `stu_birth` between '1980-1-1' and '1989-12-31';
   
   -- 补充：将表示性别的 1 和 0 处理成 “男” 和 “女”
   -- MySQL加一个内置if函数 / Oracle - decode - SQL方言
   select 
   `stu_name` as 姓名, 
   if(`stu_sex`, '男', '女') as 性别, 
   `stu_birth` as 出生日期 
   from `tb_student` 
   where `stu_birth` between '1980-1-1' and '1989-12-31';
   
   -- SQL标准语法，适用于所有数据库
   select 
   `stu_name` as 姓名, 
   case `stu_sex` when 1 then'男' else'女' end as 性别, 
   `stu_birth` as 出生日期 
   from `tb_student` 
   where `stu_birth` between '1980-1-1' and '1989-12-31';
   
   -- 查询学分大于2的课程的名称和学分(筛选)
   select `cou_name`, `cou_credit` from `tb_course` where `cou_credit` > 2;
   
   -- 查询学分是奇数的课程的名称和学分(筛选)
   select `cou_name`, `cou_credit` from `tb_course` where `cou_credit` % 2 <> 0;
   
   select `cou_name`, `cou_credit` from `tb_course` where `cou_credit` mod 2 <> 0;
   
   -- 查询选择选了1111的课程考试成绩在90分以上的学生学号(筛选)
   select `stu_id` from `tb_record` where `cou_id` = 1111 and `score` > 90;
       
   -- 查询姓“杨”的学生姓名和性别(模糊)
   -- %（通配符wildcard）：匹配0个或任意多个字符
   select `stu_name` as 姓名, 
   case `stu_sex` when 1 then '男' else '女' end as 性别 from `tb_student`
   where `stu_name` like '杨%';
   
   -- 查询姓“杨”名字两个字的学生姓名和性别(模糊)
   -- _(通配符)：精确匹配一个字符
   select `stu_name` as 姓名, 
   case `stu_sex` when 1 then '男' else '女' end as 性别 from `tb_student`
   where `stu_name` like '杨_';
   
   
   -- 查询姓“杨”名字三个字的学生姓名和性别(模糊)
   select `stu_name` as 姓名, 
   case `stu_sex` when 1 then '男' else '女' end as 性别 from `tb_student`
   where `stu_name` like '杨__';
   
   -- 查询名字中有“不”字或“嫣”字的学生的姓名(模糊)
   select `stu_name` as 姓名 from `tb_student`
   where `stu_name` like '%不%' or `stu_name` like '%嫣%';
   
   update `tb_student` set `stu_name` = '岳不嫣' where `stu_id` = 1572;
   
   -- union只会保留一个，union all会保留重复的
   select `stu_name` as 姓名 from `tb_student` where `stu_name` like '%不%'
   union
   select `stu_name` as 姓名 from `tb_student` where `stu_name` like '%嫣%';
   
   -- 查询姓“杨”或姓“林”名字三个字的学生的姓名(正则表达式模糊查询)
   select `stu_name` from `tb_student` 
   where `stu_name` regexp '[杨林].{2}'; 
   
   -- 查询没有录入籍贯的学生姓名(空值处理) is - <=>
   select `stu_name` from `tb_student` where `stu_addr` is 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;
   
   -- 查询男学生的姓名和生日按年龄从大到小排列(排序)
   -- 升序：从小到大 - asc - ascending
   -- 降序：从小到大 - desc - descending
   select `stu_id`, `stu_name`, `stu_birth` from `tb_student` 
   where `stu_sex`=1 order by `stu_birth` asc, `stu_id` desc;
   
   update `tb_student` set `stu_birth` = '1993-1-25' where `stu_id` = 1572;
   
   
   -- select curdate()：获取当前时间的年月日
   -- select now()：获取当前的具体时间
   -- select datediff('2021-11-12', '2021-10-25') - 返回两个日期相差的天数
   -- select floor(3.14) - 向小取整
   -- select ceil(3.14) - 向大取整
   -- select round(3.14) - 四舍五入
   
   -- 补充：将生日换算成年龄(日期函数、数值函数)
   select 
   `stu_id` as 学号, `stu_name` as 姓名, 
   floor(datediff(curdate(), `stu_birth`)/365) as 年龄
   from `tb_student` 
   where `stu_sex`=1 order by 年龄 desc, 学号 desc;
   
   -- 查询年龄最大的学生的出生日期(聚合函数)
   select min(`stu_birth`) from `tb_student`;
   
   -- 查询年龄最小的学生的出生日期(聚合函数，数据库通用, 且会忽略空值)
   select max(`stu_birth`) from `tb_student`;
   
   select datediff((`stu_birth`), min(`stu_birth`)) from `tb_student`;
   
   -- 查询编号为1111的课程考试成绩的最高分
   select max(`score`) from `tb_record` where `cou_id` =1111;
   
   -- 查询学号为1001的学生考试成绩的最低分
   select min(`score`) from `tb_record` where `stu_id`=1001;
   
   -- 查询学号为1001的学生考试成绩的平均分，如果有空值会忽略
   select avg(`score`) from `tb_record` where `stu_id`=1001;
   
   
   -- 查询学号为1001的学生考试成绩的平均分，如果有null值，null值算0分（聚合函数）
   select sum(`score`) / count(*) from `tb_record` where `stu_id`=1001;
   
   -- ifnull函数是MySQL的方言；nvl - Oracle
   select avg(ifnull(`score`, 0)) as 平均分 from `tb_record` where `stu_id`=1001;
   
   -- 查询学号为1001的学生考试成绩的标准差
   select std(`score`) as 标准差, variance(`score`) as 方差 
   from `tb_record` where `stu_id`=1001;
   
   
   -- 查询男女学生的人数(分组和聚合函数)
   select case `stu_sex` when 1 then '男' else '女' end as 性别, 
   count(*) as 人数 from `tb_student` group by (`stu_sex`);
   
   -- 查询每个学院的人数(rollup - 总计)
   select 
   `col_id` as 学院, 
   count(*) as 人数 from `tb_student` group by (`col_id`) with rollup;
   
   -- 查询每个学院男女学生人数
   select 
   `col_id` as 学院, 
   if(`stu_sex`, '男', '女') as 性别,
   count(*) as 人数 from `tb_student` group by `col_id`, `stu_sex`;
   
   -- 查询每个学生的学号和平均成绩(分组和聚合函数)
   select `stu_id` as 学号, 
   round(avg(ifnull(`score`, 0)), 1) as 平均成绩
   from `tb_record` group by `stu_id`;
   
   -- 查询平均成绩大于等于90分的学生的学号和平均成绩
   -- 分组以前的筛选使用where子句，分组以后的筛选使用having子句
   select `stu_id` as 学号, 
   round(avg(`score`), 1) as 平均成绩
   from `tb_record` group by `stu_id` having 平均成绩>=90;
   
   -- 查询1111、2222、3333三门课程平均成绩大于等于90分的学生的学号和平均成绩
   select `stu_id`, 
   round(avg(`score`), 1) as 平均成绩
   from `tb_record` where `cou_id` in (1111, 2222, 3333) 
   group by `stu_id` having 平均成绩>=90;
   
   -- 查询年龄最大的学生的姓名(子查询/嵌套查询)
   -- 嵌套查询：把一个select的结果作为另外一个select的一部分来使用
   -- set @a=(select min(`stu_birth`) from `tb_student`);
   
   -- select @a;
   
   select `stu_name` from `tb_student` 
   where `stu_birth`=(
   select min(`stu_birth`) from `tb_student`
   );
   
   
   -- 查询选了两门以上的课程的学生姓名(子查询/分组条件/集合运算)
   select `stu_name` from `tb_student`
   where `stu_id` in (
   select `stu_id` from `tb_record` group by `stu_id` having count(*)>2
   );
   
   
   -- 查询学生的姓名、生日和所在学院名称
   select `stu_name`, `stu_birth`, `col_name` 
   from `tb_student`, `tb_college`
   where `tb_student`.`col_id` = `tb_college`.`col_id` ;
   
   select `stu_name`, `stu_birth`, `col_name` 
   from `tb_student` inner join `tb_college`
   on `tb_student`.`col_id` = `tb_college`.`col_id` ;
   
   select `stu_name`, `stu_birth`, `col_name` 
   from `tb_student` natural join `tb_college`;
   
   
   -- 查询学生姓名、课程名称以及成绩(连接查询/联结查询)
   select `stu_name`, `cou_name`, `score`
   from `tb_student` natural join `tb_course` natural join `tb_record`
   where `score` is not null;
   
   
   -- 分页查询(前5条数据)
   select * from `tb_student` limit 5;
   
   -- 分页查询(6-10条数据)
   select * from `tb_student` limit 5, 5;
   
   -- 分页查询(11-15条数据)
   select * from `tb_record` limit 10, 5;
   
   -- 查询选课学生的姓名和平均成绩(子查询和连接查询)
   select `stu_id`, 
   round(avg(`score`), 1) as avg_score
   from `tb_record` group by `stu_id`;
   
   select `stu_name`, round(avg(`score`), 1) as avg_score
   from `tb_student` natural join
   `tb_record` group by `stu_id`;
   
   -- 查询学生的姓名和选课的数量
   select `stu_name`, 
   count(*)
   from `tb_student` natural join
   `tb_record` group by `stu_id`;
   
   -- 查询每个学生的姓名和选课数量(左外连接和子查询)
   select stu_name, ifnull(total, 0) from tb_student t1 
   left join (select stu_id, count(stu_id) as total from tb_record group by stu_id) t2 
   on t1.stu_id=t2.stu_id;