Day-04-MySQL数据库查询语句应用
课程知识内容概述:
01 数据库操作数据语句 （insert update delete）
02 数据库查询语句基础 （select）
03 数据库查询语句进阶 （分组查询 连表查询 子查询）
04 数据库获取元数据信息 （批量管理 服务监控）
05 数据库索引概述介绍 （索引构建-原理）
06 数据库索引分类介绍 （主键索引 辅助索引 联合索引 前缀索引 唯一键索引）
07 数据库索引应用配置 （创建索引 删除索引 查看索引） 执行计划
作业:
01 如果创建了多个数据库，或者数据库中创建了多个表，如何批量除?
02 删除表数据的方法 drop truncate delete 三种方式的详细区别?(面试题)
1、数据库操作数据语句
update
语法结构：
update 表名 set 字段=新值,… where 条件；
-- 属于表内容信息变更操作，需要按照表结构预先定义好的字段信息修改，并且按照条件修改，默认全表修改
具体操作：
mysql> update stu1 set name="zhangsan" where id=6;
-- 修改表数据内容标准方式，修改时一定要加条件信息（条件信息建议为主键或具有唯一性信息）
delete:
语法结构：
mysql> delete from 表名 where 表达式；
-- 属于表内容信息变更操作，需要按照表结构预先定义好的字段信息删除，并且按照条件删除，默认全表删除
具体操作：
mysql> delete from stu1 where id=8;
避免update或delete语句对数据产生破坏：
方式一：利用事务机制，对数据进行操作
完成一件事情，可以有多个步骤，要么所有步骤都成功，要么都失败？
交易 – 账户钱减少 – 另一个账户增加 – 事务 ？？？
begin;
update 或者 delete
select xxx;
rollback; 回退
commit; – 落盘
方式二：使用update或delete时，必须加上where，若不加会报错（数据库安全模式设置）
sql_safe_updates=1 – 激活数据库的安全模式
全局生效：对所有用户生效
临时激活此功能：
mysql> set global sql_safe_updates=1;
mysql> select @@sql_safe_updates;
+--------------------+
| @@sql_safe_updates |
+--------------------+
| 1 |
+--------------------+
1 row in set (0.00 sec)
mysql> use oldboy;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
# 修改测试
mysql> select * from stu1;
+----+------------+-----+-------+
| id | name | age | dept |
+----+------------+-----+-------+
| 1 | oldboy | 35 | |
| 2 | oldboy | 35 | net |
| 3 | oldboy | 35 | go |
| 4 | oldgirl | 25 | Linux |
| 5 | littlegirl | 2 | net |
| 6 | littleboy | 1 | Linux |
| 7 | oldboy | 35 | Linux |
| 9 | 老男孩 | 32 | |
+----+------------+-----+-------+
8 rows in set (0.00 sec)
mysql> update stu1 set name='老女孩';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
mysql> update stu1 set name='老女孩' where id=9;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from stu1;
+----+------------+-----+-------+
| id | name | age | dept |
+----+------------+-----+-------+
| 1 | oldboy | 35 | |
| 2 | oldboy | 35 | net |
| 3 | oldboy | 35 | go |
| 4 | oldgirl | 25 | Linux |
| 5 | littlegirl | 2 | net |
| 6 | littleboy | 1 | Linux |
| 7 | oldboy | 35 | Linux |
| 9 | 老女孩 | 32 | |
+----+------------+-----+-------+
8 rows in set (0.00 sec)
# 删除测试
mysql> delete from stu1;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
mysql> delete from stu1 where id=9;
Query OK, 1 row affected (0.01 sec)
mysql> select * from stu1;
+----+------------+-----+-------+
| id | name | age | dept |
+----+------------+-----+-------+
| 1 | oldboy | 35 | |
| 2 | oldboy | 35 | net |
| 3 | oldboy | 35 | go |
| 4 | oldgirl | 25 | Linux |
| 5 | littlegirl | 2 | net |
| 6 | littleboy | 1 | Linux |
| 7 | oldboy | 35 | Linux |
+----+------------+-----+-------+
7 rows in set (0.00 sec)
永久激活此功能：
[root@db01 ~]# echo 'set global sql_safe_updates=1;' >/opt/init.sql
[root@db01 ~]# chmod +x /opt/init.sql
[root@db01 ~]# vim /etc/my80.cnf
[mysqld]
init-file=/opt/init.sql
[root@db01 ~]# systemctl restart mysqld
[root@db01 ~]#
[root@db01 ~]# mysql --defaults-file=/etc/my80.cnf
mysql> select @@sql_safe_updates;
+--------------------+
| @@sql_safe_updates |
+--------------------+
| 1 |
+--------------------+
1 row in set (0.00 sec)
局部配置：只针对部分客户端生效
方法一：把safe_updates=1加入到my.cnf的client标签下
[root@db01 ~]# cat /etc/my80.cnf
[client]
safe_updates=1
[root@db01 ~]# systemctl restart mysqld
[root@db01 ~]# mysql --defaults-file=/etc/my80.cnf
方式二：客户端连接时，利用参数激活安全模式功能
[root@db01 ~]# mysql --defaults-file=/etc/my80.cnf -U
-U, --safe-updates Only allow UPDATE and DELETE that uses keys
-- 表示以安全更新模式登录数据库，并放入/etc/profile永久生效。
方式三：避免删除对数据的损坏（删除–伪删除）
插入状态列信息
ALTER TABLE `oldboy`.`stu1`
ADD COLUMN `state` INT UNSIGNED NOT NULL DEFAULT 1 AFTER `dept`;
-- 在原有表中添加新的状态列
mysql> update stu1 set state=0 where id=6;
-- 将原本删除列信息的状态改为0，实现伪删除效果
mysql> select * from stu1 where state=1;
-- 实现查询时不要获取状态为0的信息，即不查看获取伪删除数据信息
作业：
01 如果创建了多个数据库，或者数据库中创建了多个表，如何批量删除？
02 删除表数据的方法 drop truncate delete 三种方式的详细区别？（面试题）
drop table stu;
truncate table stu;
delete from stu;
数据表环境准备：
source ~/t100w_oldboy.sql;
问题解答分析：
| 区别分析 | drop table stu; | truncate table stu; | delete from stu; |
|---|---|---|---|
| 功能效果 | 删除表结构+数据（存储空间释放） | 删除表数据（释放空间） | 删除表数据（标记删除） |
| 删除逻辑 | 彻底删除 | 物理删除（段区页层面删除） | 逻辑删除（逐行删除） |
| 删除效率 | 效率快（和数据量无关） | 效率快（和数据量无关） | 效率慢（和数据量有关） |
| 自增影响 | 新增自增序列 | 重置自增序列（释放高水位线） | 延续自增序列 |
| 数据恢复? | 利用日志文件恢复 | 利用备份恢复/延时从库恢复 | 利用日志文件恢复（快速） |
知识扩展：自增列信息值调整方法：
mysql> alter table stu auto_increment=10;
2、数据库查询语句基础
1）查看数据库服务配置信息
- 查看数据库变量配置
select @@配置参数信息;
show variables like '%xxx%';
# 查询获取参数配置信息
mysql> select @@port;
+-----------+
| @@port |
+-----------+
| 3306 |
+-----------+
1 row in set (0.00 sec)
-- 查询数据库服务端口配置信息
mysql> select @@socket;
+-----------------+
| @@socket |
+-----------------+
| /tmp/mysql.sock |
+-----------------+
1 row in set (0.00 sec)
-- 查询数据库服务套接字文件信息
mysql> select @@innodb_flush_log_at_trx_commit;
+----------------------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------------------+
| 1 |
+----------------------------------------------+
1 row in set (0.00 sec)
-- 查询数据库服务比较长的配置参数信息
# 查询获取参数配置信息（模糊查找）
mysql> show variables;
-- 查看数据库服务所有配置参数信息
mysql> show variables like 'po%';
-- 查看数据库服务配置信息模糊查找（查找po开头的信息）
mysql> show variables like '%po';
-- 查看数据库服务配置信息模糊查找（查找po结尾的信息）
mysql> show variables like '%po%';
-- 查看数据库服务配置信息模糊查找（查找含有po的信息）
参数变量可以进行设置：
临时设置：
set global
set session
# 数据库配置参数在线调整
mysql > set session innodb_flush_log_at_trx_commit=1;
mysql > set session sql_log_bin='off';
-- 表示在线临时调整配置参数，并且只是当前会话生效（session是默认方式，不是所有配置都可以调整）
mysql > set global innodb_flush_log_at_trx_commit=1;
-- 表示在线临时调整配置参数，并且将会影响所有连接（global是全局方式，可以进行所有配置调整）
说明：数据库服务配置参数在线调整参数，只是临时生效，数据库服务重启后配置会失效，想要永久生效需要修改配置文件信息
永久设置：
服务端进行配置
配置参数/加载配置命令文件（set global）
- 查看数据库函数信息
# 查询获取函数输出信息
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.26 |
+-----------+
1 row in set (0.00 sec)
-- 查询数据库服务函数输出信息，获取服务版本信息
mysql> select now();
+--------------------------+
| now() |
+--------------------------+
| 2022-11-04 09:13:27 |
+--------------------------+
1 row in set (0.00 sec)
-- 查询数据库服务函数输出信息，获取当前日期时间
mysql> select concat(123);
+-----------------+
| concat(123) |
+-----------------+
| 123 |
+-----------------+
1 row in set (0.00 sec)
-- 查询数据库服务函数输出信息，获取拼接函数信息
mysql> select concat(user,"@","'",host,"'") from mysql.user;
+-------------------------------------+
| concat(user,"@","'",host,"'") |
+-------------------------------------+
| user02@'%' |
| blog@'192.168.30.%' |
+-------------------------------------+
-- 拼接函数应用演示
函数或者其他配置指令的官方参考：https://dev.mysql.com/doc/refman/8.4/en/dynindex-function.html
2）查看数据库服务数据信息
单表数据信息查看（基础）
# 数据表数据查询命令语法
mysql> select <字段1，字段2，...> from <表名> [WHERE 条件] group by <字段1，字段2，...> having 条件 order by 字段 limit 限制信息;
-- 属于表内容信息查询操作，可以获取表中数据行信息（子句信息需要按顺序书写）
应用方式一：select+from 结合使用情况
select * from city; -- > 等价于 cat a.txt
select id,name,countrycode,district,population from city;
-- 进入world数据库中，查询数据库中city表所有内容信息（企业应用不要对大表查看所有数据）
select name,population from city; -- > 等价于 awk $1 $2
-- 查询city表中的部分字段信息，显示查看城市名称和人口数量
应用方式二：select+from+where 结合使用情况
方式一：定义等值条件信息进行数据查询
# 查询中国的所有城市信息，中国代码信息 ”CHN”
mysql > SELECT * FROM city WHERE countrycode='CHN';
# 查询中国的所有城市信息，只关注城市名称和人口数量列信息
mysql > SELECT NAME,population FROM city WHERE countrycode='CHN';
方式二：定义区间条件信息进行数据查询
可使用区间条件表示方法：
| 序号 | 符号 | 解释说明 |
|---|---|---|
| 01 | < | 表示小于指定数值的信息作为条件 |
| 02 | > | 表示大于指定数值的信息作为条件 |
| 03 | <= | 表示小于等于指定数值的信息作为条件 |
| 04 | >= | 表示大于等于指定数值的信息作为条件 |
| 05 | != / <> | 表示不等于指定数值的信息作为条件 |
# 查询大于700万人的所有城市信息
mysql > SELECT * FROM city WHERE population>7000000;
# 查询小于等于1000人的所有城市信息
mysql > SELECT * FROM city WHERE population<=1000;
方式三：定义逻辑条件信息进行数据查询
可以使用逻辑判断符号，进行条件设定查找相应数据信息：
| 序号 | 逻辑判断符号 | 解释说明 |
|---|---|---|
| 01 | and（并且）/ && | 表示多个条件均都满足才能被查找出来 |
| 02 | or（或者）/ || | 表示多个条件之一满足就能被查找出来 |
| 03 | not (取反) / ! | 表示查找除过滤查找的信息以外的内容 |
# 查询中国境内，大于520万人口的城市信息
mysql> SELECT * FROM city WHERE countrycode='CHN' AND population>5200000;
# 查询中国和美国的所有城市
mysql> SELECT * FROM city WHERE countrycode='chn' OR countrycode='USA';
# 查询人口数在100w到200w之间的城市信息
mysql> SELECT * FROM city WHERE population>=1000000 AND population<=2000000;
方式四：定义模糊条件信息进行数据查询（like ）
# 查询国家代号是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');
-- in的查询条件方式表示包含意思，实际应用更广泛
# 查询世界上的所有城市信息，但排除中国和美国的城市不查询
mysql> SELECT * FROM city WHERE countrycode not in ('CHN','USA');
-- not in的查询条件方式表示排除意思，实际应用比较少见，因为not in不能走索引扫描，查询检索性能较慢
# 查询人口数量在50w-100w之间的城市信息
mysql> SELECT * FROM city WHERE population between 500000 and 10000000;
-- between and的查询条件方式是包含边界取值信息的，即包含50w人口的城市，也包含100w人口的城市
方式六：查询数据信息取消重复信息（distinct）
mysql> select CountryCode from city where CountryCode='USA';
mysql> select Distinct CountryCode from city where CountryCode='USA';
+-----------------+
| CountryCode |
+-----------------+
| USA |
+-----------------+
1 row in set (0.19 sec)
-- 列字段信息必须完全相同内容，才可以实现去重；
方式七：查询数据信息为空的内容（is null）
mysql> select * from city where CountryCode is null;
-- 查询国家编码字段为空的信息
mysql> select * from city where CountryCode not null;
-- 查询国家编码字段为非空的信息
3、数据库查询语句进阶
查询数据语法：
# 数据表数据查询命令语法
mysql> select <字段1，字段2，...> from <表名> [WHERE 条件] group by <字段1，字段2，...> having 条件 order by 字段 limit 限制信息;
数据库服务中常用的聚合函数（统计函数）：
| 序号 | 函数信息 | 解释说明 |
|---|---|---|
| 01 | count() | 此函数表示对数量信息进行统计 |
| 02 | sum() | 此函数表示对数值信息进行求和 |
| 03 | avg() | 此函数表示对数值信息进行求平均值 |
| 04 | min() | 此函数表示对数值信息进行取最小值 |
| 05 | max() | 此函数表示对数值信息进行取最大值 |
| 06 | group_concat() | 此函数表示输出信息无法匹配分组和聚合函数时，进行拼接整合显示 |
| 07 | distinct | 此指令表示作用是对表中的单个字段或多个字段去重操作 |
group by：主要用于将数据信息进行排序分组；
having： 主要用于对分组后/聚合后的数据进行过滤；
order by：主要用于对指定列数据信息进行排序；（升序/降序）
limit： 主要用于显示部分数据信息；
分组查询数据信息：
1）查询统计每个国家的人口总数
分组机制：
- 先指定某列信息 -> 进行排序 -> 去重合并 实现分组
mysql > select countrycode,sum(population) from world.city group by countrycode;
-- 根据国家信息分组聚合，在将分组后所有城市的人口数量进行sum求和运算，实现国家信息对应人口总数的1对1关系
2）查询统计中国每个省份的城市个数
mysql> select district,count(name) from city where countrycode='chn' group by district;
mysql> select district,count(*) from city where countrycode='chn' group by district;
# 正确查询方式
mysql> select district,count(*),group_concat(name) from city where countrycode='chn' group by district;
# 补充：sql_mode ONLY_FULL_GROUP_BY
# 当分组后，不能实现一对多显示时，就直接报错，而不是只显示部分匹配的信息；
3）查询统计每个省份的城市个数，以及城市名称信息
# 补充说明：分组查询数据经常看到错误信息 -- mysql 8.0 （数据库是一个二维表关系型的数据库）
mysql> select district,count(*),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
-- 由于数据库sql_mode配置了only_full_group_by，由于输出的name信息不能和district信息实现1对1关系，因此报错
mysql> select district,count(*),group_concat(name) from city where countrycode='chn' group by district;
-- 利用group_concat()就可以实现没有出现在分组和聚合函数中的字段，采取拼接整合方式显示，满足分组1对1关系
4）查询统计每个国家的人口总数，只显示人口数量超过1个亿的信息
mysql> select countrycode,sum(population) from world.city group by countrycode having sum(population)>100000000;
5）查询统计每个国家的人口总数，只显示人口数量超过5千万的信息，并且按照国家人口总数排序显示
mysql > select countrycode,sum(population) from world.city group by countrycode having sum(population)>50000000 order by sum(population);
-- 实现了人口数量从小到大排序（升序/正序）
mysql > select countrycode,sum(population) from world.city group by countrycode having sum(population)>50000000 order by sum(population) desc;
-- 实现了人口数量从大到小排序（降序/逆序）
6）查询统计每个国家的人口总数，只显示人口数量超过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;
mysql > select countrycode,sum(population) from world.city group by countrycode having sum(population)>50000000 order by sum(population) desc limit 3 offset 0;
7） 查询统计每个国家的人口总数，只显示人口数量超过5千万的信息，并且按照国家人口总数从大到小排序，只显示三~五名
mysql > select countrycode,sum(population) from world.city group by countrycode having sum(population)>50000000 order by sum(population) desc limit 2,3;
-- 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;
-- 跳过前2名，显示后面的三名数据信息
连表查询（多表查询数据）
准备环境：建立多个相关联的数据表
# 创建多表查询所需模拟数据库和数据表信息
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;
# 4张表关联：
student --（sno）-- sc -- course -- (tno) -- course -- teacher
# 在数据库与数据表中插入模拟数据
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;
连表查询数据方法：
# 笛卡尔乘积连接多表：
select * from t1,t2;
# 内连接查询多表：
select * from t1,t2 where t1.列=t2.列;
select * from t1 [inner] join t2 on t1.列=t2.列;
# 演示：
# 内连接查询的简单描述：两个表中有关联条件的行显示出来；
# 比较传统的SQL 92的内连接标准方式
mysql> select * from teacher,course where teacher.tno=course.tno;
# 比较新颖的SQL 99的内连接使用方式
mysql> select * from teacher [inner] join course on teacher.tno=course.tno;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[inner] join course on teacher.tno=course.tno' at line 1
mysql> select * from teacher join course on teacher.tno=course.tno;
# 外连接查询多表：左外连接
select * from t1 left join t2 on t1.列=t2.列;
-- 以左表作为驱动表，进行连接其他表
# 外连接查询多表：右外连接
select * from t1 right join t2 on t1.列=t2.列;
-- 以右表作为驱动表，进行连接其他表
# 演示
mysql> select * from teacher 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)
mysql> select * from teacher left 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 |
| 104 | xiaoB | NULL | NULL | NULL |
+-----+--------+------+--------+------+
4 rows in set (0.00 sec)
mysql> select * from teacher right 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 |
| NULL | NULL | 1004 | go | 105 |
+------+--------+------+--------+-----+
4 rows in set (0.00 sec)
内连接如何解决查询数据问题？
解题思路：
1）根据需求，获取需要连接表信息
2）将多个表连接成一个大表 join on
3）筛选过滤出需要的数据信息
4张表关联：
student --（sno）-- sc – course – (tno) – course – teacher
多表查询信息练习题目分析：
01 统计zhang3，学习了几门课？
zhang3 --> sno 1
步骤一：
sc course
步骤二：
select * from
sc join
course
on sc.cno = course.cno;
步骤三：
mysql> select count(*) from sc join course on sc.cno = course.cno where sno=1;
mysql> select sname,count(*) from student join sc on student.sno=sc.sno join course on sc.cno=course.cno wherestudent.sno=1;
+--------+----------+
| sname | count(*) |
+--------+----------+
| zhang3 | 2 |
+--------+----------+
1 row in set (0.00 sec)
02 查询zhang3，学习的课程名称有哪些？
步骤一：
student sc course
步骤二：
select * from
student join
sc
on student.sno=sc.sno
join
course
on sc.cno=course.cno;
步骤三：
mysql> select sname,count(*),group_concat(cname) from student join sc on student.sno=sc.sno join course on sc.cn
no=course.cno where student.sno=1;
+--------+----------+---------------------+
| sname | count(*) | group_concat(cname) |
+--------+----------+---------------------+
| zhang3 | 2 | linux,python |
+--------+----------+---------------------+
1 row in set (0.00 sec)
03 查询xiaoA老师教的学生名?
步骤一：
student --（sno）-- sc – course – (tno) – course – teacher
步骤二：
select * from
student
join sc
on student.sno=sc.sno
join course
on sc.cno=course.cno
join teacher
on course.tno=teacher.tno;
步骤三：
mysql> select tname,group_concat(sname) 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.tno=103 group by teacher.tno;
+-------+---------------------------------------------+
| tname | group_concat(sname) |
+-------+---------------------------------------------+
| xiaoA | zhang4,li4,wang5,zh4,zhao4,ma6,oldgirl,oldp |
+-------+---------------------------------------------+
1 row in set (0.00 sec)
04 查询xiaoA老师教课程的平均分数?
步骤一：
sc – course – (tno) – course – teacher
步骤二：
select * from sc
join course
on sc.cno=course.cno
join teacher
on course.tno=teacher.tno;
步骤三：
mysql> select tname,avg(score) from sc join course on sc.cno=course.cno join teacher on course.tno=teacher.tno where teacher.tno=103;
+-------+------------+
| tname | avg(score) |
+-------+------------+
| xiaoA | 76.7500 |
+-------+------------+
1 row in set (0.00 sec)
05 每位老师所教课程的平均分，并按平均分排序?
步骤一：
sc – course – (tno) – course – teacher
步骤二：
select * from sc
join course
on sc.cno=course.cno
join teacher
on course.tno=teacher.tno;
select tname,cname,avg(score) from sc
join course
on sc.cno=course.cno
join teacher
on course.tno=teacher.tno
group by teacher.tno,course.cno;
步骤三：
mysql> select tname,cname,avg(score) from sc join course on sc.cno=course.cno join teacher on course.tno=teacher.tno group by teacher.tno,course.cno order by avg(score);
+--------+--------+------------+
| tname | cname | avg(score) |
+--------+--------+------------+
| xiaoQ | python | 70.0000 |
| xiaoA | mysql | 76.7500 |
| oldboy | linux | 80.6667 |
+--------+--------+------------+
3 rows in set (0.00 sec)
mysql> select tname,cname,avg(score) from sc join course on sc.cno=course.cno join teacher on course.tno=teacher.tno group by teacher.tno,course.cno order by avg(score) desc;
+--------+--------+------------+
| tname | cname | avg(score) |
+--------+--------+------------+
| oldboy | linux | 80.6667 |
| xiaoA | mysql | 76.7500 |
| xiaoQ | python | 70.0000 |
+--------+--------+------------+
3 rows in set (0.00 sec)
06 查询xiaoA老师教的不及格的学生姓名?
步骤一：
student --（sno）-- sc – course – (tno) – course – teacher
步骤二：
select * from
student
join sc
on student.sno=sc.sno
join course
on sc.cno=course.cno
join teacher
on course.tno=teacher.tno;
select tname,sname 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.tno=103 and sc.score<60
步骤三：
mysql> select tname,sname 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.tno=103 and sc.score<60;
+-------+-------+
| tname | sname |
+-------+-------+
| xiaoA | li4 |
| xiaoA | zh4 |
+-------+-------+
2 rows in set (0.00 sec)
mysql> select tname,group_concat(sname) 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.tno=103 and sc.score<60 group by teacher.tno;
+-------+---------------------+
| tname | group_concat(sname) |
+-------+---------------------+
| xiaoA | li4,zh4 |
+-------+---------------------+
1 row in set (0.00 sec)
07 查询所有老师所教学生不及格的信息?
步骤一：
student --（sno）-- sc – course – (tno) – course – teacher
步骤二：
select * from
student
join sc
on student.sno=sc.sno
join course
on sc.cno=course.cno
join teacher
on course.tno=teacher.tno;
select tname,group_concat(sname) from
student
join sc
on student.sno=sc.sno
join course
on sc.cno=course.cno
join teacher
on course.tno=teacher.tno
where sc.score<60
group by teacher.tno,course.cno;
步骤三：
mysql> select tname,group_concat(sname) from student join sc on student.sno=sc.sno join course on sc.cno=course.cno join teacher on course.tno=teacher.tno where sc.score<60 group by teacher.tno,course.cno;
+-------+---------------------+
| tname | group_concat(sname) |
+-------+---------------------+
| xiaoQ | zhang3 |
| xiaoA | li4,zh4 |
+-------+---------------------+
2 rows in set (0.00 sec)
补充说明：多表查询别名应用
在进行数据信息查询时，有些表和有些字段会被经常调用到，而且生成环境中表明和字段名会比较的复杂，在调用时不是很方便；
而且有些时候，查询的SQL语句信息会出现在代码中，在编写代码时也会不太规范，同时也不方便阅读，因此出现了数据库别名概念；
在进行数据库别名应用时，会经常用到两种别名：
表别名应用：
# 举例说明别名作用，在没有使用别名时
select 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;
# 应用别名：AS
select a.tname,group_concat(d.sname)
from teacher as a
join course as b
on a.tno=b.tno
join sc as c
on b.cno=c.cno
join student as d
on c.sno=d.sno
where c.score<60
group by a.tno;
+-------+-----------------------+
| tname | group_concat(d.sname) |
+-------+-----------------------+
| xiaoQ | zhang3 |
| xiaoA | li4,zh4 |
+-------+-----------------------+
2 rows in set (0.00 sec)
列别名应用：
# 举例说明别名作用，在没有使用别名时
select a.tname,group_concat(d.sname)
from teacher as a
join course as b
on a.tno=b.tno
join sc as c
on b.cno=c.cno
join student as d
on c.sno=d.sno
where c.score<60
group by a.tno;
+-------+-----------------------+
| tname | group_concat(d.sname) |
+-------+-----------------------+
| xiaoQ | zhang3 |
| xiaoA | li4,zh4 |
+-------+-----------------------+
2 rows in set (0.00 sec)
# 进行列别名化
select a.tname as '老师名',group_concat(d.sname) as '不及格学生名'
from teacher as a
join course as b
on a.tno=b.tno
join sc as c
on b.cno=c.cno
join student as d
on c.sno=d.sno
where c.score<60
group by a.tno;
+-----------+--------------------+
| 老师名 | 不及格学生名 |
+-----------+--------------------+
| xiaoQ | zhang3 |
| xiaoA | li4,zh4 |
+-----------+--------------------+
2 rows in set (0.00 sec)
-- 列别名信息是可以在group by子句之后进行调用的
子查询应用：（了解）
子查询：实现select语句的嵌套查询（嵌套在where语句后 嵌套在from语句后）
案例演示：
1）01 取出大于Linux课程的平均分学生都有哪些；
第一步：select 获取Linux课程平均分
sc - （cno）-- course
所有信息
select * from
sc as a join
course as b
on a.cno=b.cno
where b.cname='linux';
平均分：-- 80
select avg(a.score) from sc as a join course as b on a.cno=b.cno where b.cname='linux';
第二步：select获取Linux课程大于平均分的学生信息
student --（sno）-- sc – （cno）-- course – (tno)
select * from
student
join sc
on student.sno=sc.sno
join course
on sc.cno=course.cno;
select cname,sname from student join sc on student.sno=sc.sno join course on sc.cno=course.cno where course.cnrse.cname='linux' and sc.score>'80';
两个步骤整合：
mysql> select cname,sname from student join sc on student.sno=sc.sno join course on sc.cno=course.cno where course.cname='linux' and sc.score>(select avg(a.score) from sc as a join course as b on a.cno=b.cno where b.cname='linux');
+-------+-------+
| cname | sname |
+-------+-------+
| linux | li4 |
| linux | zhao4 |
+-------+-------+
2 rows in set (0.00 sec)
2）02 取出Linux课程成绩大于70分的所有男同学信息；多个表查询信息–开发数据分析
第一步：取出Linux课程成绩大于70分
mysql> select * from sc as a join course as b on a.cno=b.cno where b.cname='linux';
第二步：找出男同学大于70分
select name,sc,gender from student where sc >70 and gender='M';
or
select s.name,s.sc,s.gender from (select * from students where gender='M') AS s where s.sc >70;
4、数据库获取元数据信息
1）元数据概念介绍
元数据 == 属性信息
表示在数据库服务中有哪些数据库，库中有哪些表，表中有多少字段，字段是什么类型等等，这样的数据就是数据库的元数据；
2）元数据获取方法
元数据获取方式一：利用命令获取（show）
# 常用SQL语句的show命令查看元数据信息
mysql> show databases;
-- 查询数据库服务中的所有数据库信息（数据库名称-元数据）
mysql> show tables;
mysql> show tables from mysql;
-- 查询数据库服务中的相应数据表信息（数据表名称-元数据）
mysql> show create database <库名>;
-- 查询数据库服务中的建库语句信息 （建库语句参数-元数据 建库语句就是DDL语句，定义建立数据库的属性信息）
mysql> show create table <表名>;
-- 查询数据库服务中的建表语句信息 （建表语句参数-元数据 建表语句就是DDL语句，定义建立数据表的属性信息）
mysql> desc <表名>;
mysql> show columns from <表名>;
-- 查询数据库服务中的数据表的结构（数据表的列定义信息-元数据）
mysql> show table status from <库名>;
-- 查询数据库服务中的相应数据表状态 （数据表的状态信息/统计信息-元数据）
mysql> show table status from world like 'city' \G
*************************** 1. row ***************************
Name: city -- 数据表名称信息
Engine: InnoDB -- 使用的数据库引擎信息
Version: 10
Row_format: Dynamic
Rows: 4046 -- 数据表的行数信息
Avg_row_length: 101 -- 平均行长度
Data_length: 409600
Max_data_length: 0
Index_length: 114688 -- 索引长度信息
Data_free: 0
Auto_increment: 4080 -- 自增列的值计数
Create_time: 2022-11-04 09:13:27 -- 数据表创建时间
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_0900_ai_ci -- 校对规则信息
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
-- 查看数据库服务中的具体数据库表的状态信息（属于单库或单表查询）
mysql> show index from world.city;
-- 查询数据库服务中的相应数据表的索引情况（了解即可）
mysql> show grants for root@'localhost';
-- 查询数据库服务中的用户权限属性配置信息
mysql> show [full] processlist;
-- 查询数据库服务的系统状态信息，表示当前数据库的所有连接情况
mysql> show variables;
mysql> show variables like '%xx%';
-- 查询数据库服务的所有配置信息
mysql> show status;
mysql> show status like '%lock%';
-- 查询数据库服务的系统整体状态，表示当前数据库服务运行的即时状态情况
mysql> show binary logs;
-- 查询数据库服务的所有二进制日志信息（binlog日志）
mysql> show master status;
-- 查询数据库服务正在使用的二进制日志
mysql> show binlog events in 'binlog.000009';
-- 查询数据库服务具体二进制日志内容事件信息
mysql> show engine innodb status \G
-- 查询数据库服务存储引擎相关信息
mysql> show slave hosts;
-- 在数据库服务主库查看从库信息
mysql> show slave status;
-- 查询数据库服务主从状态信息
说明：使用show语句虽然可以快速得到相应的数据库元数据信息，但是查询功能过于单一，想查询全面信息，就需要执行多条语句；
元数据获取方式二：利用库中视图（information_schema）
视图概念：
将查询基表元数据语句信息方法封装在一个变量或别名中，这个封装好的变量或别名就成为视图，视图信息都是存储在内存中的表
元数据信息存储在系统基表中，通过一般的select命令只能查看数据信息，不能查看到系统基表，以免被随意调整篡改；
而查询基表的语句过于复杂，可以将整个查询基表语句定义为一个视图信息（等价于别名/变量），调取视图等价于调取查询基表语句；
information_schema库中的内存表都是每次数据库服务启动时生成的，里面存储了查询元数据基表的视图信息；
# 假设查询基表语句信息如下
select a.tname as '老师名',group_concat(d.sname) as '不及格学生名'
from teacher as a
join course as b
on a.tno=b.tno
join sc as c
on b.cno=c.cno
join student as d
on c.sno=d.sno
where c.score<60
group by a.tno;
mysql> create view oldboy as select a.tname as '老师名',group_concat(d.sname) as '不及格学生名' from teacher as a join course as b on a.tno=b.tno join sc as c on b.cno=c.cno join student as d on c.sno=d.sno where c.score<
<60 group by a.tno;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from oldboy;
+-----------+--------------------+
| 老师名 | 不及格学生名 |
+-----------+--------------------+
| xiaoQ | zhang3 |
| xiaoA | li4,zh4 |
+-----------+--------------------+
2 rows in set (0.01 sec)
视图查询：
# 切换进入information_schema数据库中查看表信息
mysql> use information_schema;
mysql> show tables;
-- 此时看到的所有表信息，其实都是视图信息
# 查看获取视图信息创建语句
mysql> show create view tables;
-- 查看tables这个视图表的创建过程
# 查看视图表信息应用
# 统计数据库资产信息（数据资产），获取每个库中表的个数和名称信息（业务相关）
mysql> desc information_schema.tables;
-- 查看information_scheam中的tables表的结构信息；
mysql> select table_schema,count(*),group_concat(table_name) from information_schema.tables group by table_schema;
-- 获取相应数据库中表的个数，与数据库中拥有的表信息
mysql> select table_schema,count(*),group_concat(table_name) from information_schema.tables where table_schema not in ('mysql','sys','performance_schema','information_') group by table_schema;
# 统计数据库资产信息（数据资产），获取每个数据库数据占用磁盘空间
mysql> 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_') group by table_schema;
# 统计数据库资产信息（数据资产），获取具有碎片信息的表
mysql> select table_schema,table_name,data_free from information_schema.tables where table_schema not in ('mysql','sys','performance_schema','information_') and data_free >0 ;
-- 碎片信息过多会导致索引信息失效，以及统计信息不真实的情况
# 统计数据库资产信息（数据资产），处理具有碎片信息的表
mysql> alter table t1 engine=innodb;
-- 可以对已经是innodb存储引擎的表做操作，实现整理碎片功能
mysql> 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 ;
-- 可以对已经是innodb存储引擎的表做操作，实现批量整理碎片功能
# 统计数据库资产信息（数据资产），获取数据库中非innodb表信息
mysql>select table_schema,table_name,engine from information_schema.tables where table_schema not in ('mysql','sys','performance_schema','information_') and engine!='innodb';
-- 获取非innodb数据库引擎表
mysql> use school;
mysql> create table t1 (id int) engine=myisam;
mysql> create table t2 (id int) engine=myisam;
mysql> create table t3 (id int) engine=myisam;
-- 模拟创建一些myisam引擎数据表
# 统计数据库资产信息（数据资产），修改数据库中非innodb表信息替换成innodb
mysql> alter table world.t1 engine=innodb;
-- 可以对不是innodb存储引擎的表做操作，实现数据表引擎修改
mysql> 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 engine !='innodb';
mysql> 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 engine!='innodb' into outfile '/tmp/alter.sql';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
vim /etc/my.cnf
[mysqld]
secure-file-priv=/tmp
-- 修改配置文件参数信息，实现将数据库操作的数据信息导入到系统文件中，配置完毕重启数据库服务
mysql> source /tmp/alter.sql
-- 可以对不是innodb存储引擎的表做操作，实现数据表批量化引擎修改，调用数据库脚本信息
需求一：统计数据库资产信息（数据资产），获取每个库中表的个数和名称信息
mysql> select table_schema,table_name from information_schema.tables where table_schema not in ('mysql','sys','performance_schema','information_schema') ;
+--------------+-----------------+
| TABLE_SCHEMA | TABLE_NAME |
+--------------+-----------------+
| oldboy | class |
| oldboy | stu1 |
| oldboy | student |
| oldboy | t_foreign |
| school | course |
| school | oldboy |
| school | sc |
| school | student |
| school | teacher |
| world | city |
| world | country |
| world | countrylanguage |
| xiaoB | t1 |
| xiaoB | t2 |
| xiaoQ | stu01 |
| xiaoQ | stu02 |
| xiaoQ | t1 |
| xiaoZ | t1 |
| xiaoZ | t2 |
| xiaoZ | t3 |
+--------------+-----------------+
20 rows in set (0.01 sec)
mysql> 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;
+--------------+----------+----------------------------------+
| TABLE_SCHEMA | count(*) | group_concat(table_name) |
+--------------+----------+----------------------------------+
| oldboy | 4 | stu1,student,t_foreign,class |
| school | 5 | course,oldboy,sc,student,teacher |
| world | 3 | country,countrylanguage,city |
| xiaoB | 2 | t1,t2 |
| xiaoQ | 3 | stu01,stu02,t1 |
| xiaoZ | 3 | t1,t2,t3 |
+--------------+----------+----------------------------------+
6 rows in set (0.00 sec)
需求二：统计数据库资产信息（数据资产），获取每个数据库数据占用磁盘空间
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;
+--------------+-------------------------------------------------------+
| TABLE_SCHEMA | sum(table_rows*avg_row_length+index_length)/1024/1024 |
+--------------+-------------------------------------------------------+
| oldboy | 0.09374619 |
| school | 0.06248379 |
| world | 0.76367092 |
| xiaoB | 0.00000000 |
| xiaoQ | 0.00000000 |
| xiaoZ | 0.04686356 |
+--------------+-------------------------------------------------------+
6 rows in set (0.00 sec)
需求三：统计数据库资产信息（数据资产），获取具有碎片信息的表
mysql> 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 ;
-- 碎片信息过多会导致索引信息失效，以及统计信息不真实的情况
需求四：统计数据库资产信息（数据资产），处理具有碎片信息的表
mysql> alter table t1 engine=innodb;
-- 可以对已经是innodb存储引擎的表做操作，实现整理碎片功能
mysql> select concat("alter table ",table_schema,".",table_name," engine=innodb") from information_schema.tables where table_schema not in ('mysql','sys','performance_schema','information_schema') and data_free >0 ;
-- 可以对已经是innodb存储引擎的表做操作，实现批量整理碎片功能
需求五：统计数据库资产信息（数据资产），获取数据库中非innodb表信息
mysql>select table_schema,table_name,engine from information_schema.tables where table_schema not in ('mysql','sys','performance_schema','information_schema') and engine!='innodb';
-- 获取非innodb数据库引擎表
+--------------+------------+--------+
| TABLE_SCHEMA | TABLE_NAME | ENGINE |
+--------------+------------+--------+
| oldboy | t1 | MyISAM |
| oldboy | t2 | MyISAM |
| oldboy | t3 | MyISAM |
+--------------+------------+--------+
3 rows in set (0.01 sec)
mysql> use school;
mysql> create table t1 (id int) engine=myisam;
mysql> create table t2 (id int) engine=myisam;
mysql> create table t3 (id int) engine=myisam;
-- 模拟创建一些myisam引擎数据表
# 调整：批量操作
# 统计数据库资产信息（数据资产），修改数据库中非innodb表信息替换成innodb
mysql> alter table world.t1 engine=innodb;
-- 可以对不是innodb存储引擎的表做操作，实现数据表引擎修改
mysql> select concat("alter table ",table_schema,".",table_name," engine=innodb;") from information_schema.tables where table_schema not in ('mysql','sys','performance_schema','information_schema') and engine !='innodb';
mysql> select concat("alter table ",table_schema,".",table_name," engine=innodb;") from information_schema.tables where table_schema not in ('mysql','sys','performance_schema','information_schema') and engine!='innodb' into outfile '/tmp/alter.sql';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
vim /etc/my.cnf
[mysqld]
secure-file-priv=/tmp
-- 修改配置文件参数信息，实现将数据库操作的数据信息导入到系统文件中，配置完毕重启数据库服务
# 加载sql文件，实现批量修改存储引擎信息
mysql> source /tmp/alter.sql
-- 可以对不是innodb存储引擎的表做操作，实现数据表批量化引擎修改，调用数据库脚本信息
作业：如果创建了多个数据库，或者数据库中创建了多个表，如何批量除?
找出需要删除的库 以及 表 并拼凑命令
mysql> select table_schema,table_name from information_schema.tables where table_schema not in ('mysql','sys','peerformance_schema','information_schema') ;
+--------------+-----------------+
| TABLE_SCHEMA | TABLE_NAME |
+--------------+-----------------+
| oldboy | class |
| oldboy | stu1 |
| oldboy | student |
| oldboy | t1 |
| oldboy | t2 |
| oldboy | t3 |
| oldboy | t_foreign |
| school | course |
| school | oldboy |
| school | sc |
| school | student |
| school | teacher |
| world | city |
| world | country |
| world | countrylanguage |
| xiaoB | t1 |
| xiaoB | t2 |
| xiaoQ | stu01 |
| xiaoQ | stu02 |
| xiaoQ | t1 |
| xiaoZ | t1 |
| xiaoZ | t2 |
| xiaoZ | t3 |
+--------------+-----------------+
23 rows in set (0.00 sec)
mysql> select concat("drop table ",table_schema,".`",table_name,"`;") from information_schema.tables where table_schema not in ('mysql','sys','performance_schema','information_schema') and table_schema="oldboy" and table_name not in ('t3') into outfile '/tmp/drop_table.sql';
Query OK, 6 rows affected (0.01 sec)
# 执行删除操作
mysql> source /tmp/drop_table.sql;
ERROR 3730 (HY000): Cannot drop table 'class' referenced by a foreign key constraint 'student_ibfk_1' on table 'student'.
Query OK, 0 rows affected (0.03 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> use oldboy;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+------------------+
| Tables_in_oldboy |
+------------------+
| class |
| t3 |
+------------------+
2 rows in set (0.00 sec)
扫一扫
1249
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
