1.数据库类型

1.1 整数类型

• 选择数据类型时，需要注意准确性 合理性
• https://www.php.cn/faq/460317.html

举例

char(10) ：老男孩 -- utf8mb3 （10*3=30字节）
varchar(10) ：老男孩 -- utf8mb3 （3*3 =9字节）

2. 数据库约束属性

2.1 数据表的约束信息（对表中写入的数据做限制）

2.1.1 语句说明

create table class(
id int primary key auto_increment,   #主键--主表(父表)
name varchar(10) not null comment "班级名字，不能为空",
room varchar(10) comment '教室：允许为空'  
) charset utf8;

create table student(
id int primary key auto_increment,  
number char(10) not null unique comment "学号：不能重复",   #外键--附表(子表)
name varchar(10) not null comment "姓名",
c_id int,                                                  
foreign key(c_id) references class(id) 
) charset utf8;

• 外键约束一：当主表没有进行数据录入时，子表不能先录入数据

  mysql> insert into student values (1,'20249601','李明','96');
  ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`new_schema`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`c_id`) REFERENCES `class` (`id`))
  
  

• 外键约束二：当子表中数据没有删除时，不能先删除主表中数据

  mysql> insert into student values (1,'20249601','李明','96');
  ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`new_schema`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`c_id`) REFERENCES `class` (`id`))
  

2.2 数据表的属性信息(便于管理数据表 对约束信息的补充)

3. 数据库服务模式

3.1 数据库服务模式概念

mysql> select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+

• 说明

  ONLY_FULL_GROUP_BY            --- 禁止一行信息对应多行信息显示输出（在表进行分组处理时）
  STRICT_TRANS_TABLES           --- 录入的数据信息，超过数据类型的限制后，会自动录入失败
  NO_ZERO_IN_DATE,NO_ZERO_DATE  --- 录入的日期信息，不可能出现0000-00-00 
  ERROR_FOR_DIVISION_BY_ZERO    --- 当出现数值运算，不能出现除数为0的运算  
  

3.2 错误案例

案例说明：低版本数据库服务数据迁移高版本数据库时，迁移失败；

原因：低版本数据库中，sql_mode配置信息更简单；db01 -- mysql 5.6 0000-00-00 db02 -- mysql8.0

mysql> select @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+

#学生表：学生出生信息 0000-00-00

#将低版本数据迁移到高版本数据库中（逻辑迁移）  -- 调整为物理迁移
insert into student values (0000-00-00)  ---> mysql 8.0 执行 报错

解决方法：将高版本数据库服务的sql_mode功能关闭，在进行数据迁移；

set global sql_mode='';

4. 数据库服务定义语句（DDL 库 表 索引 创建/查看/修改/删除）

4.1 利用帮助信息

help/？ 命令信息

Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ...

{} 				-- 可挑选的内容（必须有一个选择）
[] 				-- 可选设置
大写  			-- 必须要写正确的指令信息
小写  			-- 是用户可以自定义（不要定义一些关键字符）
结尾的中括号 	-- 补充设置信息 

create_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

4.2 库相关操作命令

4.2.1 创建create

mysql > create database oldboy;
mysql > create schema oldboy;

mysql > create database oldboy character set utf8mb4;
mysql > create database oldboy charset utf8 collate utf8_general_mysql500_ci; （show collation;）

4.2.2 查看show

mysql > show databases;    -- 查看所有数据库信息
mysql > show databases like '%xiao%';    -- 检索查看指定的数据库信息
mysql > show create database oldboy;   -- 查看创建库的语句信息

4.2.3 修改 alter

mysql > alter database test charset utf8mb4;
mysql > alter database test charset utf8 collate utf8_general_mysql500_ci;     -- 修改数据库服务字符集编码信息与字符编码排序规则信息

4.2.4 删除drop（慎用）

mysql > drop database test;
mysql > drop schema	test;    -- 删除数据库信息（在生产环境一定慎用）

4.2.5 切换

mysql > use xiaoq              -- cd 目录   -- 在已有数据库之间进行切换
mysql > select database();     -- pwd     -- 调用函数信息，获取切换后的数据库名称

4.3 表相关操作命令

4.3.1 创建create

• 格式

create table 库名.表名 (
  列名01 数据类型 约束信息 属性信息,
  列名02 数据类型 约束信息 属性信息,
  列名03 数据类型 约束信息 属性信息,
  索引信息 主键索引 唯一索引 联合索引 二级索引
) 引擎信息 字符集 校对规则 注释信息


CREATE TABLE student (
  id int NOT NULL COMMENT '学号信息',
  name varchar(45) NOT NULL COMMENT '学生名',
  age tinyint unsigned NOT NULL COMMENT '学生年龄',
  gender enum('M','F','N') NOT NULL DEFAULT 'N' COMMENT '学生性别',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学生表' ;

• 数据库引擎 innnodb myisam==操作系统中的文件系统 ext4 xfs fat32 ntfs

4.3.2 查看show，desc

mysql> show tables;
mysql> show tables like '%xiao%';    -- 查看数据表信息是否存在

mysql> desc student;   -- ll
+--------+-------------------+------+-----+---------+-------+
| Field  | Type              | Null | Key | Default | Extra |
+--------+-------------------+------+-----+---------+-------+
| id     | int               | NO   | PRI | NULL    |       |
| name   | varchar(45)       | NO   |     | NULL    |       |
| age    | tinyint unsigned  | NO   |     | NULL    |       |
| gender | enum('M','F','N') | NO   |     | N       |       |
+--------+-------------------+------+-----+---------+-------+      -- 查看表结构信息

mysql> show create table student;
| student | CREATE TABLE `student` (
  `id` int NOT NULL COMMENT '学号信息',
  `name` varchar(45) NOT NULL COMMENT '学生名',
  `age` tinyint unsigned NOT NULL COMMENT '学生年龄',
  `gender` enum('M','F','N') NOT NULL DEFAULT 'N' COMMENT '学生性别',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学生表'       -- 查看数据表创建过程


show index from student\G    -- 查看表索引信息

4.3.3 修改rename，alter

mysql > rename table student to student02;
或者
mysql > alter table student02 rename student;          -- 修改表名称

mysql > alter table stu1 charset utf8mb4;
mysql > alter table stu1 charset utf8mb4 COLLATE=utf8mb4_0900_ai_ci;     -- 修改表字符集

# 添加字段：
alter table student add column telno char(11) not null unique key comment '手机号';   -- 添加字段到最后一列
alter table student add column wechat varchar(64) not null unique key comment '微信号' after name;   -- 插入字段到指定列之后
alter table student add column sid int not null unique key comment '学号' first;    -- 添加字段到首行

# 删除字段：
alter table student drop column sid;

# 修改字段：修改字段名称 修改字段数据类型 修改字段约束和属性
ALTER TABLE `xiaoA`.`student`  CHANGE COLUMN `telno` `mobilenum` CHAR(11) NOT NULL unique key COMMENT '手机号' ;  -- 修改名称
ALTER TABLE `xiaoA`.`student`  CHANGE COLUMN mobilenum mobilenum VARCHAR(12) NOT NULL unique key COMMENT '手机号' ;  -- 修改数据类型
ALTER TABLE `xiaoA`.`student`  CHANGE COLUMN `mobilenum` `mobilenum` VARCHAR(12) NULL DEFAULT '110' COMMENT '手机号' ;   -- 修改字段约束属性信息   -- 修改表的字段信息

4.3.4 删除delete，drop

#删除表中数据：
delete from student;
truncate table student;

#删除数据表： 
mysql> drop table student;

4.4 数据定义语句定义数据库规范说明

• 创建数据库名称规范：要和业务有关，不要有大写字母（为了多平台兼容），不要数字开头，不要含有系统关键字信息
• 创建数据库明确字符：创建数据库时明确（显示）的设置字符集信息，为了避免跨平台兼容性与不同版本兼容性问题
• 删除数据库操作慎用：在对数据库进行删除操作时，一定要经过严格审计后再进行操作，并且数据库普通用户不能有drop权限

4.5 数据定义语句定义数据表规范说明

• 创建数据表名称规范：要和业务有关（含库前缀），不要有大写字母，不要数字开头，不要含有系统关键字信息，名称不要太长
• 创建数据表属性规范：属性信息显示设置，引擎选择InnoDB，字符集选择utf8/utf8mb4，表信息添加注释
• 创建数据列属性规范：名称要有意义，不要含有系统关键字信息，名称不要太长
• 创建数据类型的规范：数据类型选择合适的、足够的、简短的
• 创建数据约束的规范：每个表中必须都要有主键，最好是和业务无关列，实现自增功能，建议每个列都非空（避免索引失效）/加注释
• 删除数据表操作规范：对于普通用户不能具有删表操作，需要操作删表时需要严格审核
• 修改数据表结构规范：在数据库8.0之前，修改数据表结构需要在业务不繁忙时进行，否则会产生严重的锁 19：00 。如果出现紧急修改表结构信息需求时，可以使用工具进行调整，比如使用：pt-osc、gh-ost，从而降低对业务影响

5. 数据库服务操作语句（DML表）

5.1 增（insert）

insert into student(表名) (列名01,列名02,列名03.....)  values (数值01,数据值02,数值03,.....);   -- 插入数据标准写法
insert into student(表名) (必须填写的列信息) values (必须填写的列值);
insert into student(表名) values (数值01,数据值02,数值03,.....)   -- 插入数据简化写法（不用填写列值信息--自增列 空值列 默认值列）
insert into student(表名) values (数值01,数据值02,数值03,.....),(数值01,数据值02,数值03,.....),(数值01,数据值02,数值03,.....)....

mysql> insert into student values (2,'oldgirl',22,'f'),(3,'oldbaby',1,'f'),(4,'olddog',3,'M') ;   -- 批量插入数据

5.2 删（delete）（慎重）

delete from 表名 where 删除数据条件（唯一的）;
mysql> delete from student where name='oldboy';

5.3 改（update）（慎重）

update 表名 set 修改的列='修改值' where 修改数据条件（唯一的）;

6. 数据库服务查询语句 （DQL 查询数据 数据库变量信息查询 数据库函数信息查询 基础单表查询）

6.1 获取数据库服务配置信息(变量信息)

• Status Variable Index：状态变量 监控数据库状态情况
• System Variable Index：系统变量 数据库服务功能设置 read_only

数据库中存在两种类型的变量：

• 功能变量：可以修改调整数据库服务功能设置
  https://dev.mysql.com/doc/refman/8.4/en/dynindex-sysvar.html#sysvar-index-L
• 状态变量：可以获取数据库服务运行状态信息
  https://dev.mysql.com/doc/refman/8.4/en/dynindex-statvar.html

6.1.1 查看变量方法

show variables like '%变量名%';
select @@log_bin_index;
select @@port;
select @@socket;
select @@innodb_flush_log_at_trx_commit; （功能变量-影响数据存储安全性-双一配置）

6.1.2 设置功能变量

• 永久设置（数据库服务重启依然生效 不会立即生效）

vim /etc/my.cnf 
[mysqld]
变量信息=变量值
innodb_flush_log_at_trx_commit=2

• 临时设置 (数据库服务重启失效 立即临时生效)

set global innodb_flush_log_at_trx_commit=2  -- 全局级别临时设置（会话断开依然生效 会影响所有用户） 环境变量 -- /etc/profile /etc/bashrc
set session sql_log_bin=0;                   -- 会话级别临时设置（会话断开依然失效 会影响当前用户） 环境变量 -- ~/.bashrc ~.bash_profile

6.2 调取数据库服务函数信息

• 数据库函数信息：
  https://dev.mysql.com/doc/refman/8.4/en/dynindex-function.html

select version();
select now();
select concat(); 
sum() count() avg() max() min() group_concat()....

6.3 获取数据表中数据信息

• 单表数据信息查看：select + from + where + group by + having + order by + limit;
• 创建测试数据：
  https://dev.mysql.com/doc/index-other.html
  mysql -uroot -S /tmp/mysql.sock <world.sql -- 导入测试数据

6.3.1 单表查询基础

select * from 表;                    -- 企业中小心使用
select 字段名01,字段名02 from 表名;

select + from + where;


方式一：定义等值条件信息进行数据查询
# 查询中国的所有城市信息，中国代码信息 ”CHN”
select name from city where countrycode='CHN';
# 查询中国的所有城市信息，只关注城市名称和人口数量列信息
mysql > SELECT NAME,population FROM city WHERE countrycode='CHN';

方式二：定义区间条件信息进行数据查询
> < >= <= <>/!=
# 查询大于700万人的所有城市信息
select name,countrycode,population from city where population>7000000;
# 查询小于等于1000人的所有城市信息
SELECT * FROM city WHERE population<=1000;

方式三：定义逻辑条件信息进行数据查询
and -- 并且关系 先进行一次过滤，然后在过滤之后的数据中再次进行过滤
or  -- 或者关系 在数据表中过滤指定信息后，重新在数据表再次过滤新的信息，最后将多次信息做合并
# 查询中国境内，大于520万人口的城市信息
SELECT * FROM city WHERE countrycode='CHN' and population>5200000;

# 查询中国和美国的所有城市 
SELECT * FROM city WHERE countrycode='chn' OR countrycode='USA';

# 查询人口数在100w到200w之间的城市信息
mysql> SELECT * FROM city WHERE population>=1000000 and population<=2000000;
方式四：定义模糊条件信息进行数据查询（like）（小心使用--全表查询--FULLTEXT-索引）
# 查询国家代号是CH开头的城市信息
mysql> SELECT * FROM city WHERE countrycode LIKE 'CH%';

# 查询国家代号含US内容的城市信息
mysql> SELECT * FROM city WHERE countrycode LIKE '%US%';

方式五：特殊查询条件组合进行数据查询（配合in， not in， between and ）
# 查询中国和美国的所有城市 
mysql> SELECT * FROM city WHERE countrycode in ('CHN','USA');  
mysql> SELECT * FROM city WHERE countrycode='CHN' OR countrycode='USA'

# 查询世界上的所有城市信息，但排除中国和美国的城市不查询
mysql> SELECT * FROM city WHERE countrycode not in ('CHN','USA');

# 查询人口数量在50w-100w之间的城市信息
mysql> SELECT * FROM city WHERE population between 500000 and 10000000;
mysql> SELECT * FROM city WHERE population>=500000 and population<=10000000;

方式六：查询数据信息取消重复信息(distinct)
SELECT distinct District,countrycode FROM city WHERE countrycode='CHN';

方式七：查询数据信息为空的内容
mysql> select * from city where population is null;
+------+--------+-------------+----------+------------+
| ID   | Name   | CountryCode | District | Population |
+------+--------+-------------+----------+------------+
| 4081 | oldboy | USA         | beijing  |       NULL |
+------+--------+-------------+----------+------------+
mysql> select * from city where population is not null;

6.3.2 单表查询进阶 （分组查询）select + from + where + group by

• group by 分组列
  • 将分组列的信息进行排序
  • 将分组列的信息进行合并

聚合函数：

• count() -- 统计函数
• sum() -- 求和函数
• avg() -- 求平均值
• max() -- 求最大值
• mix() -- 求最小值
• group_concat -- 将多行信息拼接为一行

分组查询题目解题思路：

• 步骤一：确认是否需要进行分组查询数据（需求中 -- 每个 各个）
• 步骤二：确认进行分组查询的列信息 （countrycode）
• 步骤三：根据需求将指定列信息做聚合处理 sum(population)

1. 标准使用

举例：

• 查询统计每个国家的人口总数

select countrycode,sum(population) from city group by countrycode;

步骤一：确认是否需要进行分组查询数据（需求中 -- 每个 各个）
步骤二：确认进行分组查询的列信息    （countrycode）
select * from city group by countrycode;
步骤三：根据需求将指定列信息做聚合处理 sum(population) 
select countrycode,sum(population) from city group by countrycode;

• 查询中国每个省份中城市数量

步骤一：是否需要进行分组处理 group by 
步骤二：根据哪个列进行分组   group by District 
步骤三：如何进行聚合处理     count(name)
select  District,count(name) from city where countrycode='CHN' group by District;

• 查询中国每个省份中城市数量,并且显示每个省份的城市名称

select  District,count(name),name from city where countrycode='CHN' group by District;
ERROR 1055 (42000): 
Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'world.city.Name' which is not functionally dependent on columns in GROUP BY clause; 
this is incompatible with sql_mode=only_full_group_by

#分组过程会首先进行排序，然后将排序的列进行合并~
#关系型数据库--execl表信息---二维表关系--一行信息要一一对应
select  District,count(name),group_concat(name) from city where countrycode='CHN' group by District;

2. 分组过滤数据信息 having

• where：过滤数据信息 根据原有表中数据进行过滤
• having：过滤数据信息 根据分组聚合之后数据过滤

举例：

• 过滤中国每个省份中，城市数量超过20的省份信息

mysql> select  District,count(name) from city where countrycode='CHN' group by District having count(name)>20;
+--------------+-------------+
| District     | count(name) |
+--------------+-------------+
| Hubei        |          22 |
| Heilongjiang |          21 |
| Liaoning     |          21 |
| Sichuan      |          21 |
| Jiangsu      |          25 |
| Shandong     |          32 |
+--------------+-------------+

• 查询统计每个国家的人口总数，只显示人口数量超过1个亿的信息

select countrycode,sum(population) from city group by countrycode having sum(population)>100000000;

3. 数据信息排序显示 order by

举例：

• 查询统计每个国家的人口总数，只显示人口数量超过5千万的信息，并且按照国家人口总数排序显示

select countrycode,sum(population) from city group by countrycode having sum(population)>50000000;

mysql> select countrycode,sum(population) from city group by countrycode having sum(population)>50000000 order by sum(population);
+-------------+-----------------+
| countrycode | sum(population) |
+-------------+-----------------+
| MEX         |        59752521 |
| RUS         |        69150700 |
| JPN         |        77965107 |
| USA         |        78625774 |
| BRA         |        85876862 |
| IND         |       123298526 |
| CHN         |       175953614 |
+-------------+-----------------+
7 rows in set (0.00 sec)

mysql> select countrycode,sum(population) from city group by countrycode having sum(population)>50000000 order by sum(population) desc;
+-------------+-----------------+
| countrycode | sum(population) |
+-------------+-----------------+
| CHN         |       175953614 |
| IND         |       123298526 |
| BRA         |        85876862 |
| USA         |        78625774 |
| JPN         |        77965107 |
| RUS         |        69150700 |
| MEX         |        59752521 |
+-------------+-----------------+
ascending order   -- asc  --升序
descending order  -- desc --降序

4. 数据信息截取处理 -- limit

举例：

• 查询统计每个国家的人口总数，只显示人口数量超过5千万的信息，并且按照国家人口总数从大到小排序，只显示前三名

mysql > select countrycode,sum(population) from world.city group by countrycode having sum(population)>50000000 order by sum(population) desc limit 3;
或者
mysql > select countrycode,sum(population) from world.city group by countrycode having sum(population)>50000000 order by sum(population) desc limit 0,3;  
limit n,m         -- n表示从第几行开始截取，m向下截取几行
mysql > select countrycode,sum(population) from world.city group by countrycode having sum(population)>50000000 order by sum(population) desc limit 3 offset 0;
limit m offset n  -- n表示从第几行开始截取，m向下截取几行

• 查询统计每个国家的人口总数，只显示人口数量超过5千万的信息，并且按照国家人口总数从大到小排序，只显示三~五名

mysql > select countrycode,sum(population) from world.city group by countrycode having sum(population)>50000000 order by sum(population) desc limit 2,3;
或者
mysql > select countrycode,sum(population) from world.city group by countrycode having sum(population)>50000000 order by sum(population) desc limit 3 offset 2;

6.3.3 连表查询方法

6.3.3.1 创建测试环境

# 创建多表查询所需模拟数据库和数据表信息
CREATE DATABASE school CHARSET utf8;
USE school;

CREATE TABLE student (
    sno INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
    sname VARCHAR(20) NOT NULL COMMENT '姓名',
    sage TINYINT UNSIGNED NOT NULL COMMENT '年龄',
    ssex ENUM('f','m') NOT NULL DEFAULT 'm' COMMENT '性别'
) ENGINE=INNODB CHARSET=utf8;

CREATE TABLE course (
    cno INT NOT NULL PRIMARY KEY COMMENT '课程编号',
    cname VARCHAR(20) NOT NULL COMMENT '课程名字',
    tno INT NOT NULL COMMENT '教师编号'
) ENGINE=INNODB CHARSET=utf8;

CREATE TABLE sc (
    sno INT NOT NULL COMMENT '学号',
    cno INT NOT NULL COMMENT '课程编号',
    score INT NOT NULL DEFAULT 0 COMMENT '成绩'
) ENGINE=INNODB CHARSET=utf8;

CREATE TABLE teacher (
    tno INT NOT NULL PRIMARY KEY COMMENT '教师编号',
    tname VARCHAR(20) NOT NULL COMMENT '教师名字'
) ENGINE=INNODB CHARSET=utf8;

# 在数据库与数据表中插入模拟数据
INSERT INTO student(sno,sname,sage,ssex)
VALUES
(1,'zhang3',18,'m'),
(2,'zhang4',18,'m'),
(3,'li4',18,'m'),
(4,'wang5',19,'f'),
(5,'zh4',18,'m'),
(6,'zhao4',18,'m'),
(7,'ma6',19,'f'),
(8,'oldboy',20,'m'),
(9,'oldgirl',20,'f'),
(10,'oldp',25,'m');

INSERT INTO teacher(tno,tname)
VALUES
(101,'oldboy'),
(102,'xiaoQ'),
(103,'xiaoA'),
(104,'xiaoB');

INSERT INTO course(cno,cname,tno)
VALUES
(1001,'linux',101),
(1002,'python',102),
(1003,'mysql',103),
(1004,'go',105);

INSERT INTO sc(sno,cno,score)
VALUES
(1,1001,80),
(1,1002,59),
(2,1002,90),
(2,1003,100),
(3,1001,99),
(3,1003,40),
(4,1001,79),
(4,1002,61),
(4,1003,99),
(5,1003,40),
(6,1001,89),
(6,1003,77),
(7,1001,67),
(7,1003,82),
(8,1001,70),
(9,1003,80),
(10,1003,96);

SELECT * FROM student;
SELECT * FROM teacher;
SELECT * FROM course;
SELECT * FROM sc;

6.3.3.2 连表查询方法

1. 笛卡尔乘积 （不是常用）

mysql> select * from teacher,course;
+-----+--------+------+--------+-----+
| tno | tname  | cno  | cname  | tno |
+-----+--------+------+--------+-----+
| 104 | xiaoB  | 1001 | linux  | 101 |
| 103 | xiaoA  | 1001 | linux  | 101 |
| 102 | xiaoQ  | 1001 | linux  | 101 |
| 101 | oldboy | 1001 | linux  | 101 |
| 104 | xiaoB  | 1002 | python | 102 |
| 103 | xiaoA  | 1002 | python | 102 |
| 102 | xiaoQ  | 1002 | python | 102 |
| 101 | oldboy | 1002 | python | 102 |
| 104 | xiaoB  | 1003 | mysql  | 103 |
| 103 | xiaoA  | 1003 | mysql  | 103 |
| 102 | xiaoQ  | 1003 | mysql  | 103 |
| 101 | oldboy | 1003 | mysql  | 103 |
| 104 | xiaoB  | 1004 | go     | 105 |
| 103 | xiaoA  | 1004 | go     | 105 |
| 102 | xiaoQ  | 1004 | go     | 105 |
| 101 | oldboy | 1004 | go     | 105 |
+-----+--------+------+--------+-----+
16 rows in set (0.00 sec)

2. 内连接方式 （常用）

mysql> select * from teacher,course where teacher.tno=course.tno;
+-----+--------+------+--------+-----+
| tno | tname  | cno  | cname  | tno |
+-----+--------+------+--------+-----+
| 101 | oldboy | 1001 | linux  | 101 |
| 102 | xiaoQ  | 1002 | python | 102 |
| 103 | xiaoA  | 1003 | mysql  | 103 |
+-----+--------+------+--------+-----+

mysql> select * from teacher inner join course on teacher.tno=course.tno;
+-----+--------+------+--------+-----+
| tno | tname  | cno  | cname  | tno |
+-----+--------+------+--------+-----+
| 101 | oldboy | 1001 | linux  | 101 |
| 102 | xiaoQ  | 1002 | python | 102 |
| 103 | xiaoA  | 1003 | mysql  | 103 |
+-----+--------+------+--------+-----+
3 rows in set (0.00 sec)

3. 外连接方式 （SQL select 优化查询）

• 左外连接

mysql> select * from teacher left join course on teacher.tno=course.tn
no;
+-----+--------+------+--------+------+
| tno | tname  | cno  | cname  | tno  |
+-----+--------+------+--------+------+
| 101 | oldboy | 1001 | linux  |  101 |
| 102 | xiaoQ  | 1002 | python |  102 |
| 103 | xiaoA  | 1003 | mysql  |  103 |
| 104 | xiaoB  | NULL | NULL   | NULL |
+-----+--------+------+--------+------+
4 rows in set (0.00 sec)

• 右外连接

mysql> select * from teacher left join course on teacher.tno=course.tn
no;
+-----+--------+------+--------+------+
| tno | tname  | cno  | cname  | tno  |
+-----+--------+------+--------+------+
| 101 | oldboy | 1001 | linux  |  101 |
| 102 | xiaoQ  | 1002 | python |  102 |
| 103 | xiaoA  | 1003 | mysql  |  103 |
| 104 | xiaoB  | NULL | NULL   | NULL |
+-----+--------+------+--------+------+
4 rows in set (0.00 sec)

6.3.3.3 实例操作

• 利用连接查询方式，解决问题：
  • 连表查询思路步骤：
    1）需要将多个表进行关联 （构建ER模型图）
    2）根据需求确认查询数据需要的表信息

01. 统计zhang3，学习了几门课？

1）需要将多个表进行关联 （构建ER模型图）
2）根据需求确认查询数据需要的表信息

需要：student sc course

3）将多个表进行进行整合拼接（内连接）

select * from student 
join sc 
on student.sno=sc.sno 
join course 
on sc.cno=course.cno; 

4)基于拼接后的大表(单表)过滤分析查询数据

select student.sname,count(course.cname) from student 
join sc 
on student.sno=sc.sno 
join course 
on sc.cno=course.cno
where student.sname='zhang3'; 

02.查询zhang3，学习的课程名称有哪些？

1）需要将多个表进行关联 （构建ER模型图）
2) 根据题目确认查询数据需要的表信息
student sc course
3）将多个数据表进行连表拼接（内连接）

select * from student 
join sc 
on student.sno=sc.sno 
join course 
on sc.cno=course.cno;

4. 基于拼接后的大表(单表)过滤分析查询数据

select student.sname,course.cname from student 
join sc 
on student.sno=sc.sno 
join course 
on sc.cno=course.cno
where student.sname='zhang3';

03.查询xiaoA老师教的学生名？

1. 根据题目确认查询数据需要的表信息
   teacher course sc student
2. 将多个数据表进行连表拼接（内连接）

select * from teacher 
join course 
on teacher.tno=course.tno 
join sc 
on course.cno=sc.cno 
join student 
on sc.sno=student.sno;

3. 基于拼接后的大表(单表)过滤分析查询数据

select teacher.tno,teacher.tname,group_concat(student.sname) from teacher 
join course 
on teacher.tno=course.tno 
join sc 
on course.cno=sc.cno 
join student 
on sc.sno=student.sno
where teacher.tname='xiaoA'
group by teacher.tno;

04.查询xiaoA老师教课程的平均分数？

mysql> select teacher.tno,teacher.tname,course.cname,avg(score) from student 
    -> join sc 
    -> on student.sno=sc.sno 
    -> join course 
    -> on sc.cno=course.cno 
    -> join teacher 
    -> on course.tno=teacher.tno 
    -> where teacher.tname='xiaoA'
    -> group by teacher.tno,course.cno;
+-----+-------+-------+------------+
| tno | tname | cname | avg(score) |
+-----+-------+-------+------------+
| 103 | xiaoA | mysql |    76.7500 |
| 105 | xiaoA | go    |    72.5000 |
| 103 | xiaoA | c++   |    25.0000 |
+-----+-------+-------+------------+

05.每位老师所教课程的平均分，并按平均分排序？

select teacher.tno,teacher.tname,course.cname,avg(sc.score) from student 
join sc 
on student.sno=sc.sno 
join course 
on sc.cno=course.cno 
join teacher 
on course.tno=teacher.tno
group by teacher.tno,course.cno
order by avg(sc.score) desc;
+-----+--------+--------+---------------+
| tno | tname  | cname  | avg(sc.score) |
+-----+--------+--------+---------------+
| 103 | xiaoA  | c++    |       25.0000 |
| 102 | xiaoQ  | python |       70.0000 |
| 105 | xiaoA  | go     |       72.5000 |
| 103 | xiaoA  | mysql  |       76.7500 |
| 101 | oldboy | linux  |       80.6667 |
+-----+--------+--------+---------------+

06.查询xiaoA老师教的不及格的学生姓名？

select  teacher.tno,teacher.tname,student.sname,sc.score from teacher
join course
on teacher.tno=course.tno
join sc
on course.cno=sc.cno
join student
on sc.sno=student.sno
where teacher.tname='xiaoA' and sc.score<60;

07.查询所有老师所教学生不及格的信息？

select teacher.tno,teacher.tname,group_concat(student.sname) from teacher
join course
on teacher.tno=course.tno
join sc
on course.cno=sc.cno
join student
on sc.sno=student.sno
where sc.score<60
group by teacher.tno;

6.3.3.4 多表查询过程别名应用

• 表别名应用

select a.tno,a.tname,group_concat(d.sname) from teacher as a 
join course as b 
on a.tno=b.tno 
join sc c 
on b.cno=c.cno 
join student d 
on c.sno=d.sno
where c.score<60
group by a.tno;

• 列别名应用

select a.tno as '讲师编号',a.tname as'讲师名称',group_concat(d.sname) as '不及格学生姓名' from teacher as a 
join course as b 
on a.tno=b.tno 
join sc c 
on b.cno=c.cno 
join student d 
on c.sno=d.sno
where c.score<60
group by a.tno;

PS:以上应用的内连接 外连接多表连接方式，都可以理解为是将多个表横向拼接在一起

6.3.3.5 利用union/ union all 将相同数据信息的表做整合拼接

select * from student  union  select * from student02；    -- 默认将相同行信息进行合拼
select * from student  union all  select * from student02  -- 将相同行信息不进行合拼

PS：以上应用的拼接方式，可以理解为将多个表进行纵向拼接；

6.3.4 数据库元数据查询

元数据：数据库中非业务表中的数据，都可以理解为是数据库的元数据

show databases  -- 显示数据库的名称信息  元数据信息
show tablees    -- 显示数据表的名称信息  元数据信息 
show create database  -- 显示数据库的字符集 校对规则信息   元数据信息 
show create tables    -- 显示数据表的结构信息 约束信息 属性信息 引擎信息 字符集 校对规则 元数据信息 
show grants for xxx   -- 显示用户权限信息    元数据信息 
select * from mysql.user  -- 显示用户信息    元数据信息 
slave_master_info         -- 显示主从建立的信息    元数据信息 
slave_relay_log_info      -- 显示主从建立的信息    元数据信息 

• 调取元数据信息方法
  • 方法一：利用各种shwo命令获取元数据信息
  • 方法二：利用各种select命令查看视图信息获取元数据信息
  • 方法三：利用各种mysql数据库中的各种函数获取元数据信息

利用select获取元数据信息，需要借助视图表进行查看元数据信息；

• 什么是试图表：可以理解为是对复杂的查询语句的别名化设置，别名化生成表就称为视图表

mysql> create view student_sc_info as select teacher.tno,teacher.tname,group_concat(student.sname) from teacher
    -> join course
    -> on teacher.tno=course.tno
    -> join sc
    -> on course.cno=sc.cno
    -> join student
    -> on sc.sno=student.sno
    -> where sc.score<60
    -> group by teacher.tno;
Query OK, 0 rows affected (0.04 sec)

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| course           |
| sc               |
| student          |
| student02        |
| student_sc_info  |
| teacher          |
+------------------+
6 rows in set (0.00 sec)

mysql> select * from student_sc_info;
+-----+-------+-----------------------------+
| tno | tname | group_concat(student.sname) |
+-----+-------+-----------------------------+
| 102 | xiaoQ | zhang3                      |
| 103 | xiaoA | li4,zh4,olde,oldf           |
| 105 | xiaoA | olda                        |
+-----+-------+-----------------------------+
3 rows in set (0.00 sec)

利用视图表数据信息解决问题：

• 统计数据库资产信息（数据资产），获取每个库中表的个数和名称信息（业务相关）

select table_schema,count(*),group_concat(table_name) 
from information_schema.tables 
where table_schema not in ('mysql','sys','performance_schema','information_schema') 
group by table_schema;

• 统计数据库资产信息（数据资产），获取每个数据库数据占用磁盘空间

select table_schema,sum(table_rows*avg_row_length+index_length)/1024/1024 
from information_schema.tables 
where table_schema not in ('mysql','sys','performance_schema','information_schema') 
group by table_schema; 

• 统计数据库资产信息（数据资产），获取具有碎片信息的表

select table_schema,table_name,data_free 
from information_schema.tables 
where table_schema not in ('mysql','sys','performance_schema','information__schema') and data_free >0 ;

• 碎片整理

alter table 表名称 engine=innodb;  -- 碎片整理命令

select concat("alter table ",table_schema,".",table_name," engine=innodb",";") 
from information_schema.tables 
where table_schema not in ('mysql','sys','performance_schema','information_') and data_free >0 ;

alter table  www.user  engine=innodb; 
alter table  www.order engine=innodb;
alter table  game.user engine=innodb;
....