MySQL（二）

MySQL - 常用命令

- 1. 常用命令
- 2. 操作数据库
- - 2.1 操作数据库
  - 2.2 创建数据库表
  - 2.3 数据库存储引擎
  - 2.4 修改数据库(ALTER<改变>)
- 3. MySQL数据管理
- - 3.1 外键
  - 3.2 DML语言
  - - 1. 添加 insert
    - 2. 修改 update
    - 3. 删除 delete
- 4. DQL查询数据
- - 4.1 基础查询
  - 4.2 条件查询
  - 4.3 分组查询
  - 4.4 连接查询
  - 4.5 排序和分页
  - 4.6 子查询
  - 4.7 MySQL函数

先安装一个数据库可视化工具，笔者下载的是：
https://www.heidisql.com/download.php

选择这款软件是由于笔者的win机器无法联网，这个软件不需要额外的依赖，安装直接使用
在这里插入图片描述

1. 常用命令

所有的语句都要以分号;结尾

show databases;	--查看当前所有的数据库
use 数据库名;	--打开指定的数据库
show tables;	--查看所有的表
describe/desc 表名;	--显示表的信息
create database 数据库名;	--创建一个数据库
exit	--退出连接

--		--单行注释
#		--单行注释
/*...*/		--多行注释

2. 操作数据库

2.1 操作数据库

创建数据库

CREATE DATABASE [IF NOT EXISTS] 数据库名;

删除数据库

DROP DATABASE [if EXISTS] 数据库名;

使用数据库

--如果表名或者字段名是特殊字符，则需要带``
use 数据库名;

查看数据库

SHOW DATABASES;

2.2 创建数据库表

CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4)	NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4

CREATE TABLE if not EXISTS `teacher` (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '编号',
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

注意点：

表名和字段尽量使用``括起来
AUTO_INCREMENT 代表自增
所有的语句后面加逗号，最后一个不加
字符串使用单引号括起来
主键的声明一般放在最后，便于查看

格式：

CREATE TABLE IF NOT EXISTS `student`(
	'字段名' 列类型 [属性] [索引] [注释],
    '字段名' 列类型 [属性] [索引] [注释],
    ......
    '字段名' 列类型 [属性] [索引] [注释]
)[表的类型][字符集设置][注释]

常用命令：

SHOW CREATE DATABASE 数据库名;-- 查看创建数据库的语句
SHOW CREATE TABLE 表名;-- 查看表的定义语句
DESC 表名;-- 显示表的具体结构

2.3 数据库存储引擎

INNODB

默认使用，安全性高，支持事务的处理，多表多用户操作

MYISAM

早些年使用，节约空间，速度较快

在这里插入图片描述

数据库文件存在的物理空间位置：

MySQL数据表以文件方式存放在磁盘中
- 包括表文件 , 数据文件 , 以及数据库的选项文件
- 位置 : Mysql安装目录/var/lib/mysql（目录名对应数据库名 , 该目录下文件名对应数据表）

[root@node-251 opt]# ll /var/lib/mysql
total 90572
-rw-r----- 1 mysql mysql       56 Apr 21 21:31 auto.cnf
-rw-r----- 1 mysql mysql     6715 Apr 23 22:55 binlog.000001
-rw-r----- 1 mysql mysql       16 Apr 21 21:31 binlog.index
-rw------- 1 mysql mysql     1680 Apr 21 21:31 ca-key.pem
-rw-r--r-- 1 mysql mysql     1112 Apr 21 21:31 ca.pem
-rw-r--r-- 1 mysql mysql     1112 Apr 21 21:31 client-cert.pem
-rw------- 1 mysql mysql     1676 Apr 21 21:31 client-key.pem
-rw-r----- 1 mysql mysql   196608 Apr 23 22:56 #ib_16384_0.dblwr
-rw-r----- 1 mysql mysql  8585216 Apr 21 21:31 #ib_16384_1.dblwr
-rw-r----- 1 mysql mysql     5955 Apr 21 21:31 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Apr 23 22:56 ibdata1
-rw-r----- 1 mysql mysql 12582912 Apr 21 21:31 ibtmp1
drwxr-x--- 2 mysql mysql     4096 Apr 21 21:31 #innodb_redo
drwxr-x--- 2 mysql mysql      187 Apr 21 21:31 #innodb_temp
drwxr-x--- 2 mysql mysql      143 Apr 21 21:31 mysql
-rw-r----- 1 mysql mysql 25165824 Apr 23 22:56 mysql.ibd
srwxrwxrwx 1 mysql mysql        0 Apr 21 21:31 mysql.sock
-rw------- 1 mysql mysql        5 Apr 21 21:31 mysql.sock.lock
drwxr-x--- 2 mysql mysql       25 Apr 23 22:54 mytest
drwxr-x--- 2 mysql mysql     8192 Apr 21 21:31 performance_schema
-rw------- 1 mysql mysql     1680 Apr 21 21:31 private_key.pem
-rw-r--r-- 1 mysql mysql      452 Apr 21 21:31 public_key.pem
-rw-r--r-- 1 mysql mysql     1112 Apr 21 21:31 server-cert.pem
-rw------- 1 mysql mysql     1676 Apr 21 21:31 server-key.pem
drwxr-x--- 2 mysql mysql       28 Apr 21 21:31 sys
-rw-r----- 1 mysql mysql 16777216 Apr 23 22:56 undo_001
-rw-r----- 1 mysql mysql 16777216 Apr 23 22:56 undo_002
[root@node-251 opt]# ll /var/lib/mysql/mytest/
total 112
-rw-r----- 1 mysql mysql 114688 Apr 23 22:54 student.ibd

MySQL在文件引擎上区别：

INNODB数据库文件类型就包括.frm、.ibd以及在上一级目录的ibdata1文件
MYISAM存储引擎，数据库文件类型就包括
.frm：表结构定义文件
.MYD：数据文件
.MYI：索引文件

2.4 修改数据库(ALTER<改变>)

修改表名

ALTER TABLE 旧表名 RENAME AS 新表名

添加字段

ALTER TABLE 表名 ADD 字段名 列属性[属性]

修改字段

ALTER TABLE 表名 MODIFY 字段名 列类型[属性]

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性]

删除字段

ALTER TABLE 表名 DROP 字段名

示例：

-- 修改表名
-- ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teachers;

-- 增加表的字段
-- ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teachers ADD age INT(11);

-- 修改表的字段(重命名，修改约束)
-- ALTER TABLE 表名 MODIFY 字段名 [列属性];
ALTER TABLE teachers MODIFY age VARCHAR(11);-- 修改约束
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 [列属性];
ALTER TABLE teachers CHANGE age age1 INT(1);-- 字段重命名

-- 删除表的字段
-- ALTER TABLE 表名 DROP 字段名
ALTER TABLE teachers DROP age1;

删除表

DROP TABLE [IF EXISTS] 表名

IF EXISTS		#为可选 , 判断是否存在该数据表

如删除不存在的数据表会抛出错误

-- 删除表(如果存在再删除)
DROP TABLE IF EXISTS teachers;

所有的创建和删除尽量加上判断，以免报错

3. MySQL数据管理

3.1 外键

外键概念

如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。

在实际操作中，将一个表的值放入第二个表来表示关联，所使用的值是第一个表的主键值(在必要时可包括复合主键值)。此时，第二个表中保存这些值的属性称为外键(foreign key)。
外键作用：

保持数据一致性，完整性，主要目的是控制存储在外键表中的数据约束。使两张表形成关联，外键只能引用外表中的列的值或使用空值。

目标：学生表（student）的gradeid字段要去引用年级表（grade）的 gradeid字段

创建外键
在这里插入图片描述

方式一：在创建表的时候增加约束

/*
1. 定义外键key
2. 给外键添加约束（执行引用）references 引用
*/
-- 创建年级表
CREATE TABLE `grade`(
	`gradeid` INT(10) NOT NULL COMMENT '年级id',
	`gradename` VARCHAR(50) NOT NULL COMMENT '年纪名称',
	PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4

CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2)	NOT NULL DEFAULT '女' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
	PRIMARY KEY (`id`),
	KEY `FK_gradeid` (`gradeid`),
	CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4

删除有外键关系的表的时候，必须要先删除引用别人的表（从表），再删除被引用的表（主表）

方法二：创建表成功后，添加外键约束

/*
1. 定义外键key
2. 给外键添加约束（执行引用）references 引用
*/
CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2)	NOT NULL DEFAULT '女' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	`gradeid` INT(10) NOT NULL COMMENT '学生的年级',
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4

-- 创建年级表
CREATE TABLE `grade`(
	`gradeid` INT(10) NOT NULL COMMENT '年级id',
	`gradename` VARCHAR(50) NOT NULL COMMENT '年纪名称',
	PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 创建表的时候没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);

以上的操作都是物理外键，数据库级别的外键，不建议使用！避免数据库过多造成困扰！

最佳实践

数据库就是用来单纯的表，只用来存数据，只有行（数据）和列（属性）

我们想使用多张表的数据，使用外键，用程序去实现

3.2 DML语言

数据库的意义：数据存储，数据管理

Data Manipulation Luaguge：数据操作语言

1. 添加 insert

	-- 普通用法
	INSERT INTO `student`(`name`) VALUES ('zsr');
	
	-- 插入多条数据
	INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES ('zsr','200024','男'),('gcc','000421','女');
	
	-- 省略字段
	INSERT INTO `student` VALUES (5,'Bareth','123456','男','2000-02-04','武汉','1412@qq.com',1); 
	```
	语法：
	```sql
	INSERT INTO 表名([字段1,字段2..])VALUES('值1','值2'..),[('值1','值2'..)..];

注意：

- 字段和字段之间使用英文逗号隔开字段是可以省略的，
- 但是值必须完整且一一对应可以同时插入多条数据，
- VALUES后面的值需要使用逗号隔开

2. 修改 update

	-- 修改学员名字,指定条件
	UPDATE `student` SET `name`='zsr204' WHERE id=1;
	
	-- 不指定条件的情况,会改动所有表
	UPDATE `student` SET `name`='zsr204';
	
	-- 修改多个属性
	UPDATE `student` SET `name`='zsr',`address`='湖北' WHERE id=1;
	
	-- 通过多个条件定位数据
	UPDATE `student` SET `name`='zsr204' WHERE `name`='zsr' AND `pwd`='200024';
	```
	语法：
	```sql
	UPDATE 表名 SET 字段1=值1,[字段2=值2...] WHERE 条件[];

关于WHERE条件语句：
![在这里插入图片描述](https://img-blog.csdnimg.cn/43482dfe57f548ce8347976de0fce76d.png)

3. 删除 delete

	-- 删除数据(避免这样写,会全部删除)
	DELETE FROM `student`;
	
	-- 删除指定数据
	DELETE FROM `student` WHERE id=1;
	```
	语法：
	```sql
	DELETE FROM 表名 [WHERE 条件]

关于DELETE删除的问题，重启数据库现象：

	INNODB 自增列会从1开始（存在内存当中，断电即失）
	MYISAM 继续从上一个子增量开始（存在内存当中，不会丢失）

`TRUNCATE`

	作用：完全清空一个数据库表，表的结构和索引约束不会变！

DELETE和TRUNCATE 的区别：
	
	- DELETE可以条件删除（where子句），而TRUNCATE只能删除整个表
	- TRUNCATE 重新设置自增列，计数器会归零，而DELETE不会影响自增
	- DELETE是数据操作语言（DML - Data Manipulation Language），操作时原数据会被放到 rollback segment中，可以被回滚；而TRUNCATE是数据定义语言（DDL - Data Definition Language)，操作时不会进行存储，不能进行回滚。

![在这里插入图片描述](https://img-blog.csdnimg.cn/aa576d347e554263b7c5428a0b3af3f2.png)

	CREATE TABLE `test`(
	`id` INT(4) NOT NULL AUTO_INCREMENT,
	`coll` VARCHAR(20) NOT NULL,
	PRIMARY KEY (`id`)
	)ENGINE=INNODB DEFAULT CHARSET=utf8;
	
	INSERT INTO `test`(`coll`) VALUES('1'),('2'),('3');
	
	-- 不会影响自增
	DELETE FROM `test`;
	
	-- 会影响自增
	TRUNCATE TABLE `test`;

DELETE										和TRUNCATE
![在这里插入图片描述](https://img-blog.csdnimg.cn/a7f45a951d3145a29556872a20ff7810.png)

4. DQL查询数据

Data QueryLanguage 数据查询语言

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
  [left | right | inner join table_name2]  -- 联合查询
  [WHERE ...]  -- 指定结果需满足的条件
  [GROUP BY ...]  -- 指定结果按照哪几个字段来分组
  [HAVING]  -- 过滤分组的记录必须满足的次要条件
  [ORDER BY ...]  -- 指定查询记录按一个或多个条件排序
  [LIMIT {[offset,]row_count | row_countOFFSET offset}]; -- 指定查询的记录从哪条至哪条

查询数据库数据 ,如SELECT语句
简单的单表查询或多表的复杂查询和嵌套查询
是数据库语言中最核心,最重要的语句
使用频率最高的语句

前提配置：

-- 创建学校数据库
CREATE DATABASE IF NOT EXISTS `school`;

-- 用school数据库
USE `school`;

-- 创建年级表grade表
CREATE TABLE `grade`(
	`GradeID` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
	`GradeName` VARCHAR(50) NOT NULL COMMENT '年纪名称',
	PRIMARY KEY	(`GradeID`)
)ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- 给grade表插入数据
INSERT INTO `grade`(`GradeID`,`GradeName`) 
VALUES (1,'大一'),(2,'大二'),(3,'大三'),(4,'大四');

-- 创建成绩result表
CREATE TABLE `result`(
	`StudentNo` INT(4) NOT NULL COMMENT '学号',
	`SubjectNo` INT(4) NOT NULL COMMENT '考试编号',
	`ExamDate` DATETIME NOT NULL COMMENT '考试日期',
	`StudentResult` INT(4) NOT NULL COMMENT '考试成绩',
	KEY `SubjectNo` (`SubjectNo`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 给result表插入数据
INSERT INTO `result`(`StudentNo`,`SubjectNo`,`ExamDate`,`StudentResult`) 
VALUES (1000,1,'2019-10-21 16:00:00',97),(1001,1,'2019-10-21 16:00:00',96),
(1000,2,'2019-10-21 16:00:00',87),(1001,3,'2019-10-21 16:00:00',98);

-- 创建学生表student
CREATE TABLE `student`(	
	`StudentNo` INT(4) NOT NULL COMMENT '学号',
	`LoginPwd` VARCHAR(20) DEFAULT NULL,
	`StudentName` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
	`Sex` TINYINT(1) DEFAULT NULL COMMENT '性别,取值0或1',
	`GradeID` INT(11) DEFAULT NULL COMMENT '年级编号',
	`Phone` VARCHAR(50) NOT NULL COMMENT '联系电话,允许为空,即可选输入',
	`Adress` VARCHAR(255) NOT NULL COMMENT '地址,允许为空,即可选输入',
	`BornDate` DATETIME DEFAULT NULL COMMENT '出生时间',
	`Email` VARCHAR(50) NOT NULL COMMENT '邮箱账号,允许为空,即可选输入',
	`IdentityCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
	PRIMARY KEY (`StudentNo`),
	UNIQUE KEY `IdentityCard` (`IdentityCard`),
	KEY `Email` (`Email`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;

-- 给学生表插入数据
INSERT INTO `student`(`StudentNo`,`LoginPwd`,`StudentName`,`Sex`,`GradeID`,`Phone`,`Adress`,`BornDate`,`Email`,`IdentityCard`) 
VALUES (1000,'1241','dsaf',1,2,'24357','unknow','2000-09-16 00:00:00','1231@qq.com','809809'),
(1001,'1321','dfdj',0,2,'89900','unknow','2000-10-16 00:00:00','5971@qq.com','908697');

-- 创建科目表
CREATE TABLE `subject`(
	`SubjectNo` INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
	`SubjectName` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
	`ClassHour` INT(4) DEFAULT NULL COMMENT '学时',
	`GradeID` INT(4) DEFAULT NULL COMMENT '年级编号',
	PRIMARY KEY (`SubjectNo`)
)ENGINE=INNODB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

-- 给科目表subject插入数据
INSERT INTO `subject`(`SubjectNo`,`SubjectName`,`ClassHour`,`GradeID`) 
VALUES(1,'高数','96',2),(2,'大物','112',2),(3,'程序设计',64,3);

SELECT 字段 FROM 表;

4.1 基础查询

语法：

SELECT 查询列表 FROM 表名;

查询列表可以是：表中的（一个或多个）字段，常量，变量，表达式，函数
查询结果是一个虚拟的表格

-- 查询全部学生
SELECT * FROM student;

-- 查询指定的字段
SELECT `LoginPwd`,`StudentName` FROM student;

-- 别名 AS(可以给字段起别名,也可以给表起别名)
SELECT `StudentNo` AS 学号,`StudentName` AS 学生姓名 FROM student AS 学生表;

-- 函数 CONCAT(str1,str2,...)
SELECT CONCAT('姓名',`StudentName`) AS 新名字 FROM student;

-- 查询系统版本(函数)
SELECT VERSION();

-- 用来计算(计算表达式)
SELECT 100*53-90 AS 计算结果;

-- 查询自增步长(变量)
SELECT @@auto_increment_increment;

-- 查询有哪写同学参加了考试,重复数据要去重
SELECT DISTINCT `StudentNo` FROM result;

4.2 条件查询

where 条件字句：检索数据中符合条件的值

语法：

select 查询列表 from 表名 where 筛选条件;

-- 查询考试成绩在95~100之间的
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult`>=95 AND `StudentResult`<=100;
-- &&
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult`>=95 && `StudentResult`<=100;
-- BETWEEN AND
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentResult`BETWEEN 95 AND 100;

-- 查询除了1000号以外的学生
SELECT `StudentNo`,`StudentResult` FROM result
WHERE `StudentNo`!=1000;
-- NOT
SELECT `StudentNo`,`StudentResult` FROM result
WHERE NOT `StudentNo`=1000;

-- 查询名字含d的同学
SELECT `StudentNo`,`StudentName` FROM student
WHERE `StudentName` LIKE '%d%';

-- 查询名字倒数第二个为d的同学
SELECT `StudentNo`,`StudentName` FROM student
WHERE `StudentName` LIKE '%d_';

-- 查询1000,1001学员
SELECT `StudentNo`,`StudentName` FROM student
WHERE `StudentNo` IN (1000,1001);

4.3 分组查询

语法：

select 分组函数，分组后的字段
from 表
【where 筛选条件】
group by 分组的字段
【having 分组后的筛选】
【order by 排序列表】

区别：
在这里插入图片描述
需要注意having和where的用法区别：

1.having只能用在group by之后，对分组后的结果进行筛选(即使用having的前提条件是分组)。

2.where肯定在group by 之前

3.where后的条件表达式里不允许使用聚合函数，而having可以。

-- 查询不同科目的平均分、最高分、最低分且平均分大于90
-- 核心：根据不同的课程进行分组
SELECT SubjectName,AVG(StudentResult),MAX(`StudentResult`),MIN(`StudentResult`)
FROM result r
INNER JOIN `subject` s
on r.SubjectNo=s.SubjectNo
GROUP BY r.SubjectNo
HAVING AVG(StudentResult)>90
ORDER BY AVG(StudentResult) desc;

当一个查询语句同时出现了where,group by,having,order by的时候，执行顺序和编写顺序是：

1.执行where xx对全表数据做筛选，返回第1个结果集。

2.针对第1个结果集使用group by分组，返回第2个结果集。

3.针对第2个结果集中的每1组数据执行select xx，有几组就执行几次，返回第3个结果集。

4.针对第3个结集执行having xx进行筛选，返回第4个结果集。

5.针对第4个结果集排序。

示例：
按由高到低的顺序显示个人平均分在70分以上的学生姓名和平均分，为了尽可能地提高平均分，在计算平均分前不包括分数在60分以下的成绩，并且也不计算（jr）的成绩。

分析：
1．要求显示学生姓名和平均分

		select s_name,avg(score) from student
```　　
	2．计算平均分前不包括分数在60分以下的成绩，并且也不计算（jr）的成绩
```sql
	where score>=60 and s_name !=’jr’

3．显示个人平均分
相同名字的学生（同一个学生）考了多门科目 因此按姓名分组

	group by s_name

4．显示个人平均分在70分以上

	having avg(s_score)>=70

5．按由高到低的顺序

	order by avg(s_score) desc

汇总：

SELECT studentno,AVG(studentresult) FROM result
WHERE studentresult>=60 AND studentno != 100000
group BY studentno
HAVING AVG(studentresult)>=70
ORDER BY AVG(studentresult) DESC;

4.4 连接查询

在这里插入图片描述

-- 查询学员所属的年级（学号，学生姓名，年级名称）
SELECT `StudentNo`,`StudentName`,`GradeName`
FROM student s
INNER JOIN grade g
ON s.GradeID=g.GradeID;

-- 查询科目所属的年级
SELECT `SubjectName`,`GradeName`
FROM `subject` s
INNER JOIN `grade` g
ON s.GradeID=g.GradeID;

-- 查询列参加程序设计考试的同学信息（学号，姓名，科目名，分数）
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM student s
INNER JOIN result r
on s.StudentNo=r.StudentNo
INNER JOIN `subject` sub
on r.SubjectNo=sub.SubjectNo
where SubjectName='程序设计';

自连接
自己的表和自己的表连接，核心：一张表拆为两张一样的表即可

-- 创建一个表
CREATE TABLE `course` (
`courseid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '课程id',
`pid` INT(10) NOT NULL COMMENT '父课程id',
`courseName` VARCHAR(50) NOT NULL COMMENT '课程名',
PRIMARY KEY (`courseid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

-- 插入数据
INSERT INTO `course` (`courseid`, `pid`, `courseName`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');

将该表进行拆分：
在这里插入图片描述

SELECT a.`courseid` AS '父课程',b.`courseid` AS '子课程'
FROM course AS a,course AS b
WHERE a.`courseid`=b.`pid`;

在这里插入图片描述

4.5 排序和分页

排序

语法：

select 查询列表
from 表
where 筛选条件
order by 排序列表 asc/desc

order by的位置一般放在查询语句的最后（除limit语句之外）
在这里插入图片描述

SELECT `StudentNo`,`StudentName`,`GradeName`
FROM student s
INNER JOIN grade g
ON s.GradeID=g.GradeID
ORDER BY `StudentNo` DESC;

分页

语法：

select 查询列表
from 表
limit offset,pagesize;

    - offset代表的是起始的条目索引，默认从0开始
    - size代表的是显示的条目数
    - offset=(n-1) * pagesize

-- 第一页 limit 0 5
-- 第二页 limit 5,5
-- 第三页 limit 10,5
-- 第n页  limit (n-1)*pagesize,pagesize
-- pagesize:当前页面大小
-- (n-1)*pagesize:起始值
-- n:当前页面
-- 数据总数/页面大小=总页面数
-- limit n 表示从0到n的页面

SELECT `StudentNo`,`studentresult`
FROM result
LIMIT 0,2

4.6 子查询

本质：在 where子句中嵌套一个子查询语句

-- 查询‘课程设计’的所有考试结果（学号，科目编号，成绩）降序排列

-- 方式一:使用连接查询
SELECT `StudentNo`,r.`SubjectNo`,`StudentResult`
FROM result r
INNER JOIN `subject` s
on r.SubjectNo=s.SubjectNo
WHERE SubjectName='程序设计'
ORDER BY StudentResult DESC;


-- 方式二:使用子查询（由里到外）
SELECT StudentNo,SubjectNo,StudentResult
from result
WHERE SubjectNo=(
	SELECT SubjectNo FROM `subject`
	WHERE SubjectName='程序设计'
)

4.7 MySQL函数

常用函数

-- 数学运算
SELECT ABS(-8); -- 绝对值
SELECT CEIL(5.1); -- 向上取整
SELECT CEILING(5.1); -- 向上取整
SELECT RAND(); -- 返回0~1之间的一个随机数
SELECT SIGN(-10); -- 返回一个数的符号;0返回0;正数返回1;负数返回-1

-- 字符串函数
SELECT CHAR_LENGTH('我喜欢你'); -- 字符串长度
SELECT CONCAT('我','喜欢','你'); -- 拼接字符串
SELECT INSERT('我喜欢',1,1,'超级') -- INSERT(str,pos,len,newstr) 从str的pos位置开始替换为长度为len的newstr
SELECT UPPER('zsr'); -- 转大写
SELECT LOWER('ZSR'); -- 转小写
SELECT INSTR('zsrs','s'); -- 返回第一次出现字串索引的位置
SELECT REPLACE('加油就能胜利','加油','坚持'); -- 替换出现的指定字符串
SELECT SUBSTR('坚持就是胜利',3,6); -- 返回指定的字符串(源字符串,截取位置,截取长度)
SELECT REVERSE('rsz'); -- 反转字符串

-- 时间日期函数
SELECT CURRENT_DATE(); -- 获取当前日期
SELECT CURDATE(); -- 获取当前日期
SELECT now(); -- 获取当前时间
SELECT LOCALTIME(); -- 本地时间
SELECT SYSDATE(); -- 系统时间

SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());

-- 系统信息
SELECT SYSTEM_USER();
SELECT USER();
SELECT VERSION();

聚合函数

函数	描述
max	最大值
min	最小值
sum	求和
avg	平均数
count	计算个数

SELECT COUNT(StudentName) FROM student; 
SELECT COUNT(*) FROM student;
SELECT COUNT(1) FROM student;

SELECT SUM(`StudentResult`) FROM result;
SELECT AVG(`StudentResult`) FROM result;
SELECT MAX(`StudentResult`) FROM result;
SELECT MIN(`StudentResult`) FROM result;