SQL基础

第1章 SQL介绍

1.1 SQL 标准

1
2
3

(1)结构化查询语言
(2)5.7 以后符合SQL92严格模式  (SQL86、SQL89、SQL92、SQL99)
(3)通过sql_mode参数来控制

1.2 常用SQL分类

DDL(Data definition language)：数据定义语言
DCL(Data control language)：数据控制语言
DML(Data manipulation language)：数据操作语言
DQL(Data query language)：数据的查询语言

1.3 怎么查看帮助

1
2
3

mysql> help contents;
mysql> help Data Definition;
mysql> help  DROP DATABASE;

1.4 工具

1
2
3

sqlyog 
navicat
workbench

1.5 mysql内置的功能

ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.

第2章 SQL-DDL应用

2.1 DDL - 针对库的操作

2.1.2 建库

CREATE DATABASE guifan CHARSET utf8mb4 ;

-- 说明:字符集及校对规则
字符集: 
mysql> SHOW CHARSET;
utf8mb4(4个字节)  utf8(3字节)
校对规则(排序规则):
mysql> SHOW COLLATION;
utf8mb4_general_ci    不敏感(默认)
utf8mb4_bin           敏感

2.1.2 删库(不代表生产操作)

1 2	DROP DATABASE oldguo; DROP DATABASE oldboy;

2.1.3 修改库

1 2	ALTER DATABASE linux CHARSET utf8mb4; 注意: 目标字符集是原字符集的严格超集.

2.1.4 查看库(不属于DDL)

1	SHOW CREATE DATABASE linux;

2.1.5 总结

CREATE DATABASE 
DROP DATABASE 
ALTER DATABASE 
SHOW CREATE DATABASE

2.1.6 建库规范

(1) 库名是小写
(2) 不能是数字开头
(3) 限制在16个字符以内
(5) 库名和业务有关
(6) 必须要加字符集
(7) 不能用预留字符

2.2 DDL - 针对表的管理

2.2.1 数据类型

数值类型

1 2	tinyint : 1字节,-128~127 ,0-255 int : 4字节,-2^31~2^31-1 , 0-2^32-1

字符类型

char(M)    : 
	char(10)最多存储10个字符(任意字符)
	M的值理论上最大255.
	定长字符类型: 不管你存多长字符,立即分配10个字符长度的存储空间
varchar(M)
	varchar(10)最多存储10个字符(任意字符)
	M的值理论上最大65535
	变长字符类型:按需分配磁盘空间.
	每次存储数据时,都要算一下多长,然后分配空间
	并且需要单独1个字节存储字符长度(超过255字符,会占用2个字节)

怎么选择:
	小于255,并且固定 长度的列,选择char
	大于255,或者变长 长度的列,选择varchar

时间类型

列值不能为空，也是表设计的规范，尽可能将所有的列设置为非空。可以设置默认值为0

unique key ：唯一键列值不能重复

unsigned ：无符号针对数字列，非负数

其他属性:

key : 索引可以在某列上建立索引，来优化查询

DATETIME 
范围为从 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999。
TIMESTAMP 
1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999。
timestamp会受到时区的影响

枚举类型

1
2
3

enum('m','f'):枚举类型
enum('广东省','广西省','四川省','湖南省',.....)
       1         2        3         4

二进制类型

2.2.2 约束

primary key  主键 : 非空且唯一,一个表只能有一个主键
not null     非空 : 列值必填
unique key   唯一 : 列值不能重复 
unsigned     无符号: 对于数字列,无符号

2.2.3 其他属性

1
2
3

comment    : 注释 
default    : 默认值
auto_increment : 自动增长

2.2.4 表属性

存储引擎:
	InnoDB（默认的）
字符集和排序规则:
	utf8       
	utf8mb4
表的注释

2.2.5 列属性

约束(一般建表时添加):
**primary key** ：主键约束
设置为主键的列，此列的值必须非空且唯一，主键在一个表中只能有一个，但是可以有多个列一起构成。
**not null**      ：非空约束
列值不能为空，也是表设计的规范，尽可能将所有的列设置为非空。可以设置默认值为0
**unique key** ：唯一键
列值不能重复
**unsigned** ：无符号
针对数字列，非负数。

其他属性:
**key** :索引
可以在某列上建立索引，来优化查询,一般是根据需要后添加
**default**           :默认值
列中，没有录入值时，会自动使用default的值填充
**auto_increment**:自增长
针对数字列，顺序的自动填充数据（默认是从1开始，将来可以设定起始点和偏移量）
**comment ** : 注释

2.3 DDL - 表定义

建表

CREATE TABLE stu(
id   INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
NAME VARCHAR(64) NOT NULL COMMENT '姓名',
age TINYINT UNSIGNED NOT NULL DEFAULT 99 COMMENT '年龄',
gender ENUM('m','f','n') NOT NULL DEFAULT 'n' COMMENT '性别',
telnum CHAR(11) NOT NULL DEFAULT '0' COMMENT '手机号',
intime DATETIME NOT NULL DEFAULT NOW() COMMENT '入学时间'
) ENGINE=INNODB CHARSET=utf8mb4 COMMENT '学生表';

扩展:
create table t1 like stu;                   #只克隆表结构
create table user select * from mysql.user; #表结构和数据都可用(索引不克隆)

2.4 建表规范总结

(1) 表名要和业务有关
(2) 表名不能有大写
(3) 表名不能有数字开头
(4) 表必须设置存储引擎
(5) 表必须设置数据类型
(6) 选择合适并简短的数据类型
(7) 每个表要有主键,一般可以无关列
(8) 每个列设置非空约束,设置默认值
(9) 每个列和表要有注释
(10) 不使用预留字符

— 查看表结构

1 2	desc stu; show create table stu;

— 删除表 (危险!!!)

1	mysql> drop table user;

— 修改表结构

(1) 在stu表中添加qq列
ALTER TABLE stu ADD qq VARCHAR(20) NOT NULL DEFAULT 0 COMMENT 'QQ号';
DESC stu;

注意: 
1. 大表结构变更,会长时间锁表,肯定会影响到线上业务
2. 8.0以后就自动解决了,8.0以前需要业务不繁忙期间或者使用PT-OSC工具

(2) 在gender后加一个wechat列
ALTER TABLE stu ADD wecht  VARCHAR(20) NOT NULL DEFAULT '0'  COMMENT '微信号' AFTER gender ;
DESC stu;

(3) 在首列添加sid列
ALTER TABLE stu ADD sid  VARCHAR(20) NOT NULL DEFAULT '0'  COMMENT 'sid' FIRST;

(4) 删除列
DESC stu;
ALTER TABLE stu DROP sid;
ALTER TABLE stu DROP qq;
ALTER TABLE stu DROP wecht;

(5) 修改列的属性
ALTER TABLE stu MODIFY NAME VARCHAR(100) NOT NULL COMMENT '姓名';
ALTER TABLE stu CHANGE NAME sname VARCHAR(64) NOT NULL COMMENT '姓名';
--- 清空表(危险)
TRUNCATE TABLE stu;

第3章 DCL

1 2	grant revoke

第4章 DML (表中的数据行)

4.1 INSERT

# 语法：INSERT INTO 表 VALUES(v1,v2,v3,....)
(1) 最规范的写法
DESC stu;
INSERT INTO 
stu(sname,age,gender,telnum)
VALUES
('张三',19,'m','110'),
('张四',17,'f','119'),
('张五',18,'m','120');

SELECT * FROM stu;
(2) 简易的写法
INSERT INTO stu
VALUES(4,'李四',20,'m','122','2019-10-10 18:00:00');

4.2 UPDATE

1
2
3

UPDATE 表  SET 列=cc  WHERE 条件
UPDATE stu SET age=29 WHERE sname='张三';
SELECT * FROM stu;

4.3 DELETE

DELETE FROM 表 WHERE 条件
DELETE FROM stu WHERE sname='张五';

DROP TABLE , TRUNCATE TABLE ,DELETE FROM TABLE  区别
DROP TABLE : 表结构+数据全部(物理)删除
TRUNCATE TABLE: 数据全部清空(物理),清空是区
DELETE FROM TABLE :逐行删除表中所有数据,逻辑删除

4.4 伪删除

用状态列标记一个行是否存在.
UPDATE 替代 DELETE
(1) 添加一个状态列(1代表删除,0代表未删除)
ALTER TABLE stu ADD state CHAR(1) NOT NULL DEFAULT '0' COMMENT '状态列:1代表删除,0代表未删除';
SELECT * FROM stu;

(2) 删除张四数据行
原语句:
DELETE FROM stu WHERE sname='张四';
改写为:
UPDATE stu SET state='1' WHERE sname='张四';
(3) 查询数据
原查询语句:
SELECT * FROM stu;
改写为:
SELECT * FROM stu WHERE state='0';

第5章 DQL应用(select )

select : 查询表中的数据行(记录)

5.1 单独使用(MySQL)

5.1.1 SELECT @@参数名

-- select @@参数名
用来查询MySQL中设定的参数.
SELECT @@port;
SELECT @@datadir;
SELECT @@server_id;
SELECT @@innodb_flush_log_at_trx_commit;
SHOW VARIABLES;
SHOW VARIABLES LIKE '%trx%';

5.1.2 – select 函数();

USE world;
SELECT DATABASE();
SELECT NOW();
SELECT CONCAT("hello");

5.1.3 SELECT 计算功能

mysql> select 3+5;
+-----+
| 3+5 |
+-----+
|   8 |
+-----+
1 row in set (0.00 sec)

5.2 SELECT 配合各种”子句”使用

5.2.1 各子句的执行顺序

SELECT 列1,列2,列3 ,..
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

5.2.2 单表子句-from

world : 世界
city  : 城市
	id          : 城市ID
	NAME        : 城市名 
	countrycode : 国家代码(CHN,USA)
	district    : 区域(省,州)
	population  : 人口
	
例子: 
SELECT 列.... FROM  表;
(1) 查询city表中的所有数据 
SELECT  *  FROM city;
(2) 查询city表中 name和population列的所有值
SELECT NAME,population FROM city ;

5.2.3 单表子句-where

SELECT 列…. FROM 表 WHERE 条件;
例子:

--- WHERE 的等值条件查询
(1) 查询city表中,中国所有的城市信息
SELECT * FROM city WHERE countrycode='CHN';
--- WHERE 的 > < >= <= != <>
(2) 查询人口数小于100人的城市.
SELECT * FROM city WHERE Population<100;
--- WHERE 的 AND , OR 
(3) 查询中国,人口数量大于500w的城市
SELECT * FROM city WHERE countrycode='CHN' AND population>5000000;
(4) 查询中国和美国的所有城市信息
SELECT * FROM city WHERE countrycode='CHN' OR countrycode='USA' ;
--- WHERE 配合 LIKE 的模糊查询
(5) 查询CH开头的国家的城市信息
SELECT * FROM city WHERE countrycode LIKE 'CH%';
--- WHERE 配合 IN 子句使用
(6)查询中国和美国的所有城市信息
SELECT * FROM city WHERE countrycode IN ('CHN','USA');
--- WHERE 配合 BETWEEN AND 
(7) 查询世界上人口数量在100w到200w之间的城市
SELECT * FROM city  WHERE population >=1000000 AND population<=2000000;
SELECT * FROM city  WHERE population BETWEEN 1000000 AND 2000000;

5.2.4 SELECT 配合 GROUP BY + 聚合函数应用

— 聚合函数(数据统计类的功能)
平均值 AVG()
最大值 MAX()
最小值 MIN()
总和 SUM()
计数 COUNT()
列转行 GROUP_CONCAT()

— GROUP BY

例子:
--- (1) 统计每个国家的总人口数量
SELECT countrycode,SUM(population)
FROM city
GROUP BY countrycode;

--- (2) 统计中国每个省的总人口数量
SELECT district,SUM(population)
FROM city
WHERE countrycode='CHN'
GROUP BY district;

--- (3) 统计中国每个省的城市个数和城市名称列表
SELECT district , COUNT(NAME),GROUP_CONCAT(NAME)
FROM city
WHERE countrycode='CHN'
GROUP BY district;

5.2.5 select 配合 having 应用

例子:
--- (1) 统计中国每个省的总人口数量大于10000000
SELECT district,SUM(population)
FROM city
WHERE countrycode='CHN' 
GROUP BY district
HAVING  SUM(population)>10000000;

5.2.6 SELECT 配合 ORDER BY 排序应用

例子:
--- (1) 查询中国所有城市,并按人口从小到大(从大到小)
SELECT * FROM city
WHERE countrycode='CHN'
ORDER BY population DESC;

--- (2) 统计中国每个省的总人口数量,并按总人口数从大到小排序
SELECT district,SUM(population)
FROM city
WHERE countrycode='CHN'
GROUP BY district
ORDER BY SUM(population) DESC;

--- (3) 统计中国每个省的总人口数量,并查询总人口数超过500w的信息,按总人口降序排序
SELECT district,SUM(population)
FROM city
WHERE countrycode='CHN' 
GROUP BY district
HAVING  SUM(population)>5000000
ORDER BY SUM(population) DESC ;

5.2.7 SELECT 配合 LIMIT 使用

查看中国所有的城市，并按人口数进行排序(从大到小)

1	SELECT * FROM city WHERE countrycode='CHN' ORDER BY population DESC;

统计中国各个省的总人口数量，按照总人口从大到小排序

SELECT district AS 省 ,SUM(Population) AS 总人口
FROM city
WHERE countrycode='chn'
GROUP BY district
ORDER BY 总人口 DESC ;

统计中国,每个省的总人口,找出总人口大于500w的,并按总人口从大到小排序,只显示前三名

SELECT  district, SUM(population)  FROM  city 
WHERE countrycode='CHN'
GROUP BY district 
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC
LIMIT 3 ;

LIMIT N ,M --->跳过N,显示一共M行
LIMIT 5,5

SELECT  district, SUM(population)  FROM  city 
WHERE countrycode='CHN'
GROUP BY district 
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC
LIMIT 5,5;

5.2.8 distinct：去重复

1 2	SELECT countrycode FROM city ; SELECT DISTINCT(countrycode) FROM city ;

5.2.9 联合查询- union all

-- 中国或美国城市信息

SELECT * FROM city 
WHERE countrycode IN ('CHN' ,'USA');

SELECT * FROM city WHERE countrycode='CHN'
UNION ALL
SELECT * FROM city WHERE countrycode='USA'

说明:一般情况下,我们会将 IN 或者 OR 语句 改写成 UNION ALL,来提高性能
UNION     去重复
UNION ALL 不去重复

第6章多表连接查询

6.1 案例准备

按需求创建一下表结构:

use school
student ：学生表
sno：    学号
sname：学生姓名
sage： 学生年龄
ssex： 学生性别

teacher ：教师表
tno：     教师编号
tname：教师名字

course ：课程表
cno：  课程编号
cname：课程名字
tno：  教师编号

score  ：成绩表
sno：  学号
cno：  课程编号
score：成绩

-- 项目构建
CREATE DATABASE school CHARSET utf8mb4;
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,'haoge'),
(102,'yage'),
(103,'oldguo');

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

DESC sc;
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.2 多表连接应用

1.查询一下世界上人口数量小于100人的城市名和国家名
	SELECT b.name ,a.name ,a.population
	FROM  city  AS a
	JOIN  country AS b
	ON    b.code=a.countrycode
	WHERE  a.Population<100
2.查询一下chengdu城市,人口数,所在的国家名,所在的大洲
	SELECT city.name,city.Population ,country.name,country.Continent
	FROM city 
	JOIN country
	ON city.countrycode=country.code 
	WHERE city.name='chengdu';
3.查询一下chengdu城市,人口数,所在的国家名,所在的大洲
	SELECT city.name,city.Population ,country.name,country.Continent
	FROM city 
	JOIN country
	ON city.countrycode=country.code 
	WHERE city.name='chengdu';
4.oldguo老师教的课程名称
	SELECT teacher.tname,course.cname
	FROM  teacher
	JOIN course
	ON teacher.tno=course.tno 
	WHERE teacher.tname='oldguo';
5.每位老师,所教课程的平均分
=========================
oldguo   mysql   80
yage     python  70 
haoge    linux   75
=========================
	SELECT teacher.tname,course.cname,AVG(sc.score)
	FROM teacher 
	JOIN course
	ON teacher.tno=course.tno
	JOIN sc
	ON course.cno=sc.cno
	GROUP BY teacher.tname,course.cname;
6.统计一下每位老师所教学生个数及学生名字
	SELECT teacher.tname,COUNT(student.sname),
	GROUP_CONCAT(CONCAT(student.sname,"_",student.sno))
	FROM teacher 
	JOIN course
	ON teacher.tno=course.tno
	JOIN sc
	ON course.cno=sc.cno 
	JOIN student
	ON sc.sno=student.sno 
	GROUP BY teacher.tname
	
---------
字符串拼接
SELECT CONCAT(USER,"@",HOST) FROM mysql.user;
SELECT USER,HOST FROM mysql.user;

6.3 别名

--- 表别名 
SELECT teacher.tname,COUNT(student.sname),GROUP_CONCAT(CONCAT(student.sname,"_",student.sno))
FROM teacher 
JOIN course
ON teacher.tno=course.tno
JOIN sc
ON course.cno=sc.cno 
JOIN student
ON sc.sno=student.sno 
GROUP BY teacher.tname

应用别名后:
SELECT a.tname,COUNT(d.sname),GROUP_CONCAT(CONCAT(d.sname,"_",d.sno))
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 
GROUP BY a.tname

--- 列别名
SELECT a.tname AS tname ,
COUNT(d.sname) AS stu_count ,
GROUP_CONCAT(CONCAT(d.sname,"_",d.sno)) AS stu_list
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 
GROUP BY a.tname

说明: 列别名只能在having或者order by中调用

第7章练习

统计zhang3,学习了几门课

mysql> select student.sname,count(sc.cno) 
from student join sc on student.sno=sc.sno 
where student.sname='zhang3' 
group by student.sname;

查询zhang3,学习的课程名称有哪些

mysql> select student.sname,group_concat(course.cname) 
from student 
join sc 
on student.sno=sc.snno 
join course 
on sc.cno=course.cno 
where student.sname='zhang3' 
group by student.sname;

查询oldguo所教课程的平均分数

mysql> select teacher.tname,course.cname,avg(sc.score) 
from teacher 
join course 
on teacher.tno=course.tno 
join sc 
on course.cno=sc.cno 
where teacher.tname='oldguo' 
group by course.cno;

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

mysql> select teacher.tname , student.sname , course.cname , 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 sc.score<60;

查询oldguo所教的不及格的学生姓名

mysql> select teacher.tname,student.sname,course.cname,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='oldguo' and sc.score<60 ;

每位老师所教课程的平均分,并按平均分排序

select teacher.tname , course.cname , avg(sc.score)  
from teacher 
join course 
on teacher.tno=course.tno  
join sc 
on course.cno=sc.cno
group by course.cno ;

查询平均成绩大于60分的同学的学号和平均成绩

select student.sno,avg(sc.score)
from student 
join sc on student.sno=sc.sno 
group by sc.sno
having avg(sc.score)>60;

查询所有同学的学号、姓名、选课数、总成绩

SELECT student.sno,student.sname,COUNT(sc.cno),SUM(sc.score)
FROM student
JOIN sc
ON student.sno=sc.sno
GROUP BY student.sno;

查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

1
2
3

SELECT sc.cno,MAX(sc.score),MIN(sc.score)
FROM sc
GROUP BY sc.cno;

统计各位老师,所教课程的及格率

SELECT teacher.tname,course.cname,CONCAT(COUNT(sc.score>60 OR NULL)/COUNT(sc.score)*100,"%") AS 及格率 
FROM teacher 
JOIN course
ON teacher.tno=course.tno 
JOIN sc
ON course.cno=sc.cno 
GROUP BY sc.cno;

查询每门课程被选修的学生数

SELECT course.cname,COUNT(sc.sno)
FROM course
JOIN sc
ON course.cno=sc.cno 
GROUP BY sc.cno;

查询出只选修了一门课程的全部学生的学号和姓名

SELECT student.sno,student.sname
FROM student
JOIN sc
ON student.sno=sc.sno
GROUP BY sc.sno
HAVING COUNT(sc.cno)=1;

查询选修课程门数超过1门的学生信息

SELECT student.sno,student.sname
FROM student
JOIN sc
ON student.sno=sc.sno
GROUP BY sc.sno
HAVING COUNT(sc.cno)>1;

统计每门课程:优秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的学生列表

SELECT course.cname,GROUP_CONCAT(CASE WHEN sc.score>85 THEN student.sname END) AS 优秀,
GROUP_CONCAT(CASE WHEN sc.score>70 AND sc.score<85 THEN student.sname END) AS 良好,
GROUP_CONCAT(CASE WHEN sc.score>65 AND sc.score<70 THEN student.sname END) AS 一般,
GROUP_CONCAT(CASE WHEN sc.score<65 THEN student.sname END) AS 不及格
FROM student
JOIN sc 
ON student.sno=sc.sno 
JOIN course
ON sc.cno=course.cno 
GROUP BY course.cno;

查询平均成绩大于85的所有学生的学号、姓名和平均成绩

SELECT student.sno,student.sname,AVG(sc.score)
FROM student
JOIN sc
ON student.sno=sc.sno
GROUP BY sc.sno
HAVING AVG(sc.score)>85;

第8章 show

show databases    
show tables 
show tables from world
show create database world 
show create table city 
show create table world.city 
show grants for root@'localhost'
show processlist
show charset
show collation
show variables                   #查看所有参数
show variables like '%trx%'      #模糊查询参数
show engines                     #查询MySQL支持的所有存储引擎
show status                      #查看MySQL所有工作状态信息
show status like '%innodb%'      #模糊查询MySQL的某些状态信息
show engine innodb status \G     #查看InnoDB引擎相关信息
show index from city;            #查看city表的排索引情况
show binary logs;			     #所有二进制日志的列表 
show binlog events in 'xxxx'     #查看二进制日志事件信息 
show master status               #二进制日志当前使用情况
show slave status\G              #查看从库状态信息
show relaylog events in 'xxxx'   #查看从库的中继日志事件信息.

第9章元数据

9.1 元数据介绍

数据字典信息(表结构信息),
表属性(引擎,字符集,校对....),
库属性
权限,状态....

9.2 元数据控制

"基表" 存储
DDL,DCL专用语句进行: 创建create,删除drop,修改alter
show 语句 查询部分元数据
information_schema.xxxx

9.3 information_schema 常用视图

mysql> DESC TABLES;
TABLE_SCHEMA              #表所在的库
TABLE_NAME                #表名
ENGINE                    #表引擎
TABLE_ROWS                #数据行
AVG_ROW_LENGTH            #平均行长度
INDEX_LENGTH 		  	  #索引长度

1.查询world库下所有的表名信息

1
2
3

SELECT table_name FROM information_schema.tables 
WHERE table_schema='world';
SHOW TABLES FROM world;

2.统计所有库下的表个数

1
2
3

SELECT table_schema,COUNT(table_name)
FROM information_schema.tables 
GROUP BY table_schema;

3.统计每个数据库下的表的个数,名称

1
2
3

SELECT table_schema,COUNT(table_name),GROUP_CONCAT(table_name)
FROM information_schema.tables 
GROUP BY table_schema;

4.统计每个数据库(生产库)下的表的个数,名称,要求表名后跟表注释

#提示: NOT IN (sys,information_schema,performance_schema,mysql)

SELECT table_schema,COUNT(table_name),GROUP_CONCAT(CONCAT(table_name,"(",table_comment,")"))
FROM information_schema.tables 
WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql')
GROUP BY table_schema;

5.统计全库数据量

TABLE_ROWS                #数据行
AVG_ROW_LENGTH            #平均行长度
INDEX_LENGTH 		      #索引长度

SELECT SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024/1024 AS total_size_mb
FROM information_schema.tables ;

6.统计每个业务库的总大小

SELECT table_schema,SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024/1024 AS total_size_mb
FROM information_schema.tables
WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql')
GROUP BY table_schema ;

7.统计下city表的大小

1
2
3

SELECT table_name , (TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024/1024 AS mb 
FROM information_schema.tables
WHERE table_schema='world' AND table_name='city';

8.拼接:批量生成命令

mysqldump -uroot -p123 world city >/backup/world_city.sql 
mysqldump -uroot -p123 world country >/backup/world_country.sql 
mysqldump -uroot -p123 school student >/backup/school_student.sql 

SELECT CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name," >/BACKUP/",table_schema,"_",table_name,".sql")
FROM information_schema.tables INTO OUTFILE '/tmp/bak.sh';

columns (数据字典统计)

#统计school数据库下的表的数据字典信息
SELECT table_name,column_name ,data_type,column_comment
FROM information_schema.columns
WHERE table_schema='school';

文章作者: Wu Fei

文章链接: http://linuxwf.com/2020/04/15/3-SQL%E5%9F%BA%E7%A1%80/

mysql

打赏

微信
支付宝