3、SQL基础应用

1 SQL介绍

结构化查询语言
5.7以后符合SQL_92标准的严格模式
通过sql_mode来控制

2 SQL分类

DDL：数据定义语言
DCL：数据控制语言
DML：数据操作语言
DQL：数据查询语言

3 数据类型、表属性、字符集

3.1 数据类型

3.1.1 作用

保证数据的准确性和标准性

3.1.2 种类

数值类型

手机号码用int存不下，一般用字符串类型

字符串类型

char(11)：
定长的字符串类型，在存储字符串时，最大字符长度11个，立即分配11个字符长度的存储空间，如果存不满用空格填充
varchar(11)：
变长的字符串类型，最大字符长度11个。在存储字符串时，自动判断字符串长度，按需分配存储空间
enum('x','y','z')：
      1   2   3
枚举类型，比较适合于将来此列的值是固定范围内的特点，可以很大程度的优化索引结构

时间类型

timestamp受到时区的影响

3.2 表属性

3.2.1 列属性

约束（一般建表时加入）

primary key 
主键约束：设置为主键的列，此列的值必须非空且唯一，主键在一个表中只能有一个，但是可以有多个列一起构成
not null
非空约束：列值不能为空，也是表的设计规范，尽可能将所有的列设置为非空约束。可以设置默认值为0
unique key
唯一键：列值不能重复
unsigned
无符号：针对数字列，非负数，必须紧跟在数据类型后面

其他属性

key
索引：可以在某列上建立索引，优化查询。一般根据需要后添加
default
默认值：列中，没有录入数值时，会自动使用default的值填充
auto_increment
自增长：针对数字列，顺序的自动填充数值（默认是从1开始，也可设置开始值和步长），一般配合主键使用
comment
注释
CONSTRAINT
外键约束，语法为CONSTRAINT 外键名 FOREIGN KEY 字段名 [，字段名2，…]
REFERENCES <主表名> 主键列1 [，主键列2，…]

3.2.1.1 外键约束

MySQL外键约束（FOREIGN KEY）是表的一个特殊字段，经常与主键约束一起使用。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。

外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。主表删除某条记录时，从表中与之对应的记录也必须有相应的改变。一个表可以有一个或多个外键，外键可以为空值，若不为空值，则每一个外键的值必须等于主表中主键的某个值。

定义外键时，需要遵守下列规则：

主表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则主表与从表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。
必须为主表定义主键。
主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。
在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键。
外键中列的数目必须和主表的主键中列的数目相同。
外键中列的数据类型必须和主表主键中对应列的数据类型相同。

建表时添加外键：

CONSTRAINT 外键名 FOREIGN KEY 列名 [，列名2，…]
REFERENCES 主表名 主键列1 [，主键列2，…]

修改表时添加外键：

ALTER TABLE 数据表名 ADD CONSTRAINT 外键名
FOREIGN KEY 列名 REFERENCES 主表名 主键列;

删除外键约束：

ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名;

3.2.2 表的属性

存储引擎：
InnoDB（默认的）

字符集：
utf8：中文占3个字符
utf8mb4：中文占4个字符，支持emoji表情

排序（校对）规则：
大小写是否敏感

4 DDL应用

4.1 数据定义语言

4.2 库定义

4.2.1 创建

创建数据库
create database 库名；
create schema 库名；

mysql>create database stu；

创建数据库并指定字符集
create database 库名 charset 字符集；

mysql>create database stu charset utf8；

创建数据库并指定字符集和校对规则
create database 库名 charset 字符集 collate 校对规则；

mysql>create database stu charset utf8 collate utf8_bin；

建库的规则

库名不能大写
库名不能以数字开头
建库时要加字符集，因为默认是latin字符集
库名要和业务相关

4.2.2 删除（生产中禁用）

drop database 库名；

mysql>drop database stu;

4.2.3 修改

修改数据库的字符集
alter database 库名 charset 字符集；

alter database stu charset utf8mb4；

注意：修改字符集，新字符集一定是原字符集的严格超集。

4.2.4 查询库相关信息（DQL）

查询所有数据库
show databases;

mysql>  show databases ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| world              |
+--------------------+

查看建库时的详细配置
show create database库名；

[(none)]>  show create database world ;
+----------+-------------------------------------------------------------------+
| Database | Create Database                                                   |
+----------+-------------------------------------------------------------------+
| world    | CREATE DATABASE `world` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+-------------------------------------------------------------------+

show charset查看字符集，第三列表示校对规则，ci结尾的表示大小写不敏感

mysql>show charset ;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |

show collation查看校对规则，以bin结尾的是大小写敏感的校对规则

mysql>show collation ;
+--------------------------+----------+-----+---------+----------+---------+
| Collation                | Charset  | Id  | Default | Compiled | Sortlen |
+--------------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci          | big5     |   1 | Yes     | Yes      |       1 |
| big5_bin                 | big5     |  84 |         | Yes      |       1 |
| dec8_swedish_ci          | dec8     |   3 | Yes     | Yes      |       1 |
| dec8_bin                 | dec8     |  69 |         | Yes      |       1 |
| cp850_general_ci         | cp850    |   4 | Yes     | Yes      |       1 |
| cp850_bin                | cp850    |  80 |         | Yes      |       1 |
| hp8_english_ci           | hp8      |   6 | Yes     | Yes      |       1 |

4.3 表定义

4.3.1 创建

create table 表名(
列1 属性(数据类型、约束、其他属性),
列2 属性,
列3 属性
);

建表举例：

CREATE TABLE stu(
id      INT PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
sname   VARCHAR(255) NOT NULL COMMENT '姓名',
sage    TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄',
sgender ENUM('m','f','n') NOT NULL DEFAULT 'n' COMMENT '性别',
sid     CHAR(18) NOT NULL UNIQUE COMMENT '身份证',
intime  TIMESTAMP NOT NULL DEFAULT NOW() COMMENT '入学时间'
)ENGINE=INNODB CHARSET=utf8 COMMENT '学生表';

参照表1创建属性一样的表2
create table 表1 like 表2 ;

CREATE TABLE ceshi LIKE stu;

建表规范

表名小写
不能是数字开头
注意字符集和存储引擎
表名和业务相关
选择合适的数据类型
每个列要有注释
每个列设置为非空，无法保证非空，用0来填充

4.3.2 删除（生产中禁用）

use 库名；
drop table 表名；

mysql>use school;
mysql>drop table t1;

4.3.3 修改

8.0版本以前，在线的DDL操作会锁表，需要借助pt-osc工具。

4.3.3.1 在表尾添加列

alter table 表名 add 列名列属性;

ALTER TABLE stu ADD qq VARCHAR(20) UNIQUE NOT NULL COMMENT 'qq号';

4.3.3.2 在指定列后添加列

alter table 表名 add 列名列属性 after 前列名;

ALTER TABLE stu ADD wechat VARCHAR(64) NOT NULL UNIQUE COMMENT '微信' AFTER sname ;

4.3.3.3 在表头添加列

alter table 表名 add 列名列属性 first;

ALTER TABLE stu ADD num INT NOT NULL COMMENT '数字' FIRST ;

4.3.3.4 删除列

alter table 表名 drop 列名;

ALTER TABLE stu DROP num ;

4.3.3.5 修改数据类型、列属性

修改相当于替换列属性，需要将所有希望设定的列属性都写上
alter table 表名 modify 列名列属性;

ALTER TABLE stu MODIFY sname VARCHAR(128) NOT NULL COMMENT '名字';

4.3.3.6 修改列名、数据类型、列属性

alter table 表名 change 列名新列名属性;

ALTER TABLE stu CHANGE sgender sg CHAR(1) NOT NULL DEFAULT 'n' COMMENT '性别';

4.3.4 表属性查询（DQL）

查询库中的所有表名
use 库名;
show tables;

mysql>use school;
Database changed
mysql>show tables ;
+------------------+
| Tables_in_school |
+------------------+
| stu              |
+------------------+
1 row in set (0.00 sec)

查询表的所有列信息
desc 表名;

[school]>desc stu;
+---------+---------------------+------+-----+-------------------+----------------+
| Field   | Type                | Null | Key | Default           | Extra          |
+---------+---------------------+------+-----+-------------------+----------------+
| id      | int(11)             | NO   | PRI | NULL              | auto_increment |
| sname   | varchar(255)        | NO   |     | NULL              |                |
| sage    | tinyint(3) unsigned | NO   |     | 0                 |                |
| sgender | enum('m','f','n')   | NO   |     | n                 |                |
| sid     | char(18)            | NO   | UNI | NULL              |                |
| intime  | timestamp           | NO   |     | CURRENT_TIMESTAMP |                |
+---------+---------------------+------+-----+-------------------+----------------+
6 rows in set (0.00 sec)

查询建表的详细信息
show create table 表名;

[school]>show create table stu;

5 DCL应用

权限类操作
grant、revoke

6 DML应用

6.1 作用

对表中数据进行增、删、改

6.2 insert

在所有列插入数据
insert into 表名values(值1,值2...);

INSERT INTO stu
VALUES
(1,'zs',18,'m','123456',NOW());

在指定列插入数据，其他列使用默认值
insert into 表名(列名1,列名2...)values(值1,值2...);

INSERT INTO stu(sname,sid)
VALUES
('ls','234567');

同时录入多行数据
insert into 表名(列名1,列名2...)
values
(值1,值2...),
(值1,值2...),
(值1,值2...);

INSERT INTO stu(sname,sid)
VALUES
('s1','1'),
('s2','2'),
('s3','3');

6.3 update

改变某列的全部数据，很少用到
update 表名 set 列名=数据;

update stu set sname='zhao4';

配合where语句过滤更改某行数据，常用!
update 表名 set 列名=数据 where 过滤条件;

update stu set sname='zhao4' where id=2;

6.4 delete、truncate

delete
只是逻辑上的删除，不会删除数据所占的物理空间页。会造成表出现物理缝隙。
逻辑删除表的全部数据行，一行一行删除，速度慢
delete from 表名；

delete from stu

配合where删除特定行
delete from 表名 where 过滤条件；

delete from stu where sname='ls';

truncate
物理上删除表的数据页，相当于清空表的存储空间，速度快
truncate table 表名;

truncate table stu;

6.5 伪删除

用update代替delete，保证业务查询（select）不到即可

添加状态列

alter table stu add state tinyint not null default 1;

使用update代替delete

update stu set state=0 where id=6;

业务查询时，加入状态判断

select * from stu where state=1;

7 DQL应用（select）

单表查询语句顺序
select from、where、group by、having、order by、limit
多表查询语句顺序
select from、join on、where、group by、having、order by、limit
用于做例子的city表字段说明

表city
ID             编号
Name           城市名字
CountryCode    国家编码
District       省
Population     人口数

表country
Code           国家编码
Name           国家名字
population     国际人口
surfacearea    国土面积
...

7.1 查看系统参数

select @@XXX ; 精确查询某属性

select @@basedir;
select @@datadir;
select @@port;

show variables like '单词开头%';模糊查询某属性，%表示通配符

show variables like 'innodb%';

7.2 select配合函数

select now();显示当前时间
select database();显示当前数据库
select user();显示当前用户
select concat('字符串');打印字符串

# 打印user@host的显示效果
[school]>select concat(user,'@',host) from mysql.user;
+-------------------------+
| concat(user,'@',host)   |
+-------------------------+
| bigsky@10.0.1.%         |
| mysql.session@localhost |
| mysql.sys@localhost     |
| root@localhost          |
+-------------------------+

select group_concat('字符串');将多列打印的字符串转为一行，用,分割


[school]>select group_concat(user,'@',host) from mysql.user; 
+----------------------------------------------------------------------------+
| group_concat(user,'@',host)                                                |
+----------------------------------------------------------------------------+
| bigsky@10.0.1.%,mysql.session@localhost,mysql.sys@localhost,root@localhost |
+----------------------------------------------------------------------------+
1 row in set (0.02 sec)

7.3 单表子句-from

查询表中列1、列2的数据
select 列1,列2 from 表;

查询表中所有列数据（不要对大表进行操作）
select * from 表;

7.4 单表子句-where

前置过滤，select后的第一个过滤子句，where后的条件不可以使用聚合函数（avg，sum，count，min）
用法：select 列1,列2 from 表 where 列条件;

7.4.1 where配合等值查询

查询city表，中国所有城市的信息

SELECT * FROM city WHERE countrycode='chn';

7.4.2 where配合比较操作符（>、<、>=、<=）

查询city表，人口小于等于100的城市信息

SELECT * FROM city WHERE population<=100;

7.4.3 where配合逻辑运算符（and、or）

查询city表，中国人口大于500万的城市信息

SELECT * FROM city WHERE countrycode='chn' AND population>5000000;

查询city表，中国或美国城市的信息

SELECT * FROM city WHERE countrycode='chn' OR countrycode='usa';

7.4.4 where配合like模糊查询

查询city表，省的名字以guang开头的城市信息

SELECT * FROM city WHERE district LIKE 'guang%';

注意：通配符%不能放前面，因为不走索引

7.4.5 where配合in语句枚举查询

查询city表，中国或美国城市的信息

SELECT * FROM city WHERE countrycode IN ('chn','usa');

7.4.6 where配合between and

查询city表，人口大于100万小于200万的城市信息

SELECT * FROM city WHERE population BETWEEN 1000000 AND 2000000;

7.4.7 where配合having复合判断

查询city表，中国或美国，人口大于100万小于200万的城市信息

SELECT * FROM city WHERE countrycode IN ('chn','usa') HAVING population BETWEEN 1000000 AND 2000000 ;

7.5 group by +常用聚合函数

7.5.1 作用

接在where之后，根据by后面的条件进行分组聚合显示，by后面接一个或多个列名
group by一般配合聚合函数使用：

(1)出现在select后面的字段,要么是聚合函数中的,要么是group by中的.
(2)要筛选结果,可以先使用where再用group by或者先用group by再用having

7.5.2 常用聚合函数

函数()中间的参数室列名
max()最大值
min()最小值
avg()平均值
sum()总和
count()个数
group_concat()列转行显示

7.5.3 例子

1.统计city表每个国家的总人口数

SELECT countrycode,SUM(population)  FROM city 
GROUP BY countrycode;

2.统计中国，每个省的总人口数

SELECT district,SUM(population)  FROM city 
WHERE countrycode='chn' 
GROUP BY district;

3.统计世界上，每个国家的城市数

SELECT countrycode,COUNT(NAME)  FROM city 
GROUP BY countrycode;

4.统计中国每个省的城市列表

SELECT district,GROUP_CONCAT(NAME) FROM city
WHERE countrycode='chn'
GROUP BY district;

7.6 单表子句-having

接在group by之后，根据条件做后置过滤(<、>、<=、>=、=)，having的列名需要出现在select中
注意：having的条件不走索引，一般可以用临时表来解决

例子：
统计中国每个省的总人口数，只打印总人口数小于100w的省

SELECT district,SUM(population) FROM city
WHERE countrycode='chn'
GROUP BY district
HAVING SUM(population)<1000000;

7.7 单表子句-order by

接在having之后，以一定顺序显示结果。
order by 列名 desc|asc(默认)，desc是descend 降序、asc是ascend 升序，默认是asc 升序排列。

例子：
1.查询中国的城市信息，并按人口数从大到小排列

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

2.统计中国，每个省的总人口数，找出大于500w的，并按总人口从大到小排序

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

7.8 单表子句-limit

接在order by之后
limit N,M：跳过N行显示M行，不写N时默认为0。

例子：
统计中国，每个省的总人口数，找出大于500w的，并按总人口从大到小排序，只显示前3名

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

7.9 去重复-distinct()

distinct(列名)，使distinct()作用的列名，重复的只显示一次。

例子：
直接查询所有国家(countrycode)的信息，会有重复项，使用distinct()可以去重复

select distinct(countrycode) from city

7.10 联合查询-union all、union

将多个查询语句竖向合并显示，查询性能优于IN 或 OR语句，一般会将IN 或 OR改写成UNION ALL
union和union all的区别：

union 去除重复行，性能不如union all
union all 不去重
例子：
查询中国或美国的城市信息

select * from city where countrycode='chn'
union all
select * from city where countrycode='usa';

7.11 join-多表连接查询

用作例子的4张表说明

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

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

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

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

-- 项目构建
drop database school;
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 score (
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');

INSERT INTO student(sno,sname,sage,ssex)
VALUES
(2,'zhang4',18,'m'),
(3,'li4',18,'m'),
(4,'wang5',19,'f');

INSERT INTO student
VALUES
(5,'zh4',18,'m'),
(6,'zhao4',18,'m'),
(7,'ma6',19,'f');

INSERT INTO student(sname,sage,ssex)
VALUES
('oldboy',20,'m'),
('oldgirl',20,'f'),
('oldp',25,'m');


INSERT INTO teacher(tno,tname) VALUES
(101,'oldboy'),
(102,'hesw'),
(103,'oldguo');

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

DESC score;
INSERT INTO score(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 score;

7.11.1 语法

表a join 表b：横向拼成一行

#a、b表ID列是关联的，a表是学生编号+学生姓名，b表是学生编号+地址
表a
+----+---------+ 
| ID |  name   | 
+----+---------+ 
|  1 |  jack   | 
|  2 |  rose   | 
+----+---------+ 
表b
+----+---------+ 
| ID | addr   | 
+----+---------+ 
|  1 | street  | 
|  2 |  road   | 
+----+---------+ 
表a join 表b
+----+---------+---------+  
| ID | name    |  addr   | 
+----+---------+---------+  
|  1 |  jack   | street  |  
|  2 |  rose   |  road   |  
+----+---------+---------+

用法：
select a表.列1,b表.列2,c表.列3 from
a表 join b表
on ab表关联列
join c表
on bc表关联列
where...；

#查询jack家地址
select a.name,b.addr from
a join b
on a.id=b.id
where a.id='jack';

7.11.2 例子

1.查询世界上，人口数量小于100人的城市名、国家名、国土面积

SELECT city.name,country.name,country.surfacearea 
FROM city JOIN country
ON city.countrycode=country.code
WHERE city.population<100;

2.查询城市shenyang，城市人口，所在国家名(name)及国土面积(surfacearea)

SELECT city.name,city.population,country.name,country.surfacearea
FROM city JOIN country
ON city.countrycode=country.code
WHERE city.name='shenyang';

3.统计zhang3，学习了几门课

SELECT a.sname,COUNT(b.cno)
FROM student AS a JOIN score AS b
ON a.sno=b.sno
WHERE a.sname='zhang3';

4.查询zhang3，学习的课程名称有哪些？(3表关联)

SELECT a.sname,GROUP_CONCAT(c.cname)
FROM student AS a JOIN score AS b
ON a.sno=b.sno
JOIN course AS c
ON b.cno=c.cno
WHERE a.sname='zhang3' ;

5.查询oldguo老师教的学生名字(3表关联)

SELECT a.tname,d.sname
FROM teacher AS a JOIN course AS b
ON a.tno=b.tno
JOIN score AS c
ON b.cno=c.cno
JOIN student AS d
ON c.sno=d.sno
WHERE a.tname='oldguo';

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

SELECT a.tname,AVG(c.score)
FROM teacher AS a JOIN course AS b
ON a.tno=b.tno
JOIN score AS c
ON b.cno=c.cno
WHERE a.tname='oldguo';

7.每位老师所教课程的平均分,并按平均分排序(3表关联+group by)

SELECT a.tname,AVG(c.score)
FROM teacher AS a JOIN course AS b
ON a.tno=b.tno
JOIN score AS c
ON b.cno=c.cno
GROUP BY a.tname
ORDER BY AVG(c.score) DESC;

8.查询oldguo所教的不及格的学生姓名(3表关联+where and)

SELECT a.tname, d.sname
FROM teacher AS a JOIN course AS b 
ON a.tno=b.tno
JOIN score AS c 
ON b.cno=c.cno
JOIN student AS d
ON c.sno=d.sno
WHERE a.tname='oldguo' AND c.score<60 ;

9.查询平均成绩大于60分的同学的学号和平均成绩(group by+having)

SELECT a.sno,AVG(a.score) FROM
score AS a
GROUP BY a.sno
HAVING AVG(a.score)>60;

7.11.3 别名 as

列别名：用于输出时，列名显示替换
表别名：用于写sql语句时，简化表名

SELECT a.name AS '城市名',a.population AS '人口数',b.name AS '国家名',b.surfacearea AS '国土面积'
FROM city AS a JOIN country AS b
ON a.countrycode=b.code
WHERE a.name='shenyang';

+-----------+-----------+-----------+--------------+
| 城市名    | 人口数    | 国家名    | 国土面积     |
+-----------+-----------+-----------+--------------+
| Shenyang  |   4265200 | China     |   9572900.00 |
+-----------+-----------+-----------+--------------+
1 row in set (0.00 sec)

7.11.4 case语句

简单case语句
检查表达式的值与一组唯一值的匹配。

CASE  case_expression
   WHEN when_expression_1 THEN commands
   WHEN when_expression_2 THEN commands
   ...
   ELSE commands
END

case_expression可以是任何有效的表达式。我们将case_expression的值与每个WHEN子句中的when_expression进行比较，例如when_expression_1，when_expression_2等。如果case_expression和when_expression_n的值相等，则执行相应的WHEN分支中的命令commands（SQL语句或返回值）。

如果WHEN子句中的when_expression与case_expression的值不匹配，则ELSE子句中的命令将被执行。ELSE子句是可选的。如果省略ELSE子句，并且找不到匹配项，MySQL将引发错误。

可搜索case语句
简单CASE语句仅允许您将表达式的值与一组不同的值进行匹配。为了执行更复杂的匹配，如范围，使用可搜索CASE语句。

CASE
    WHEN condition_1 THEN commands
    WHEN condition_2 THEN commands
    ...
    ELSE commands
END

MySQL评估求值WHEN子句中的每个条件，直到找到一个值为TRUE的条件，然后执行THEN子句中的相应命令commands（SQL语句或返回值）。

如果没有一个条件为TRUE，则执行ELSE子句中的命令commands。如果不指定ELSE子句，并且没有一个条件为TRUE，MySQL将发出错误消息。

MySQL不允许在THEN或ELSE子句中使用空的命令。如果您不想处理ELSE子句中的逻辑，同时又要防止MySQL引发错误，则可以在ELSE子句中放置一个空的BEGIN END块。

例子：
统计各位老师所教课程的及格率

SELECT teacher.tname AS '教师姓名',
CONCAT(
       COUNT(
             CASE 
                  WHEN ifnull(score.score,0)>=60 THEN 1
             END
             )
             /COUNT(score.score)*100,'%'
       ) AS '及格率'
FROM teacher JOIN course 
ON teacher.tno=course.tno
JOIN score
ON course.cno=score.cno
GROUP BY teacher.tname;

+--------------+-----------+
| 教师姓名     | 及格率    |
+--------------+-----------+
| hesw         | 66.6667%  |
| oldboy       | 100.0000% |
| oldguo       | 75.0000%  |
+--------------+-----------+
3 rows in set (0.00 sec)

统计每门课优秀(100-85)，良好(85-70)，一般(70-60)，不及格(60以下)的学生列表

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

+--------+-------------------+---------------------+------------+-----------+
| cname  | 优秀              | 良好                | 一般       | 不及格    |
+--------+-------------------+---------------------+------------+-----------+
| linux  | zhao4,li4         | zhang3,oldboy,wang5 | oldboy,ma6 | NULL      |
| python | zhang4            | NULL                | wang5      | zhang3    |
| mysql  | oldp,zhang4,wang5 | oldgirl,zhao4,ma6   | NULL       | zh4,li4   |
+--------+-------------------+---------------------+------------+-----------+
3 rows in set (0.00 sec)

7.12 select ... for update

for update是一种行级锁，又叫排它锁。一旦用户对某个行施加了行级加锁，则该用户可以查询也可以更新被加锁的数据行，其它用户只能查询但不能更新被加锁的数据行。

select … for update 语句是我们经常使用手工加锁语句。在数据库中执行select … for update ,会对数据库中的表或某些行数据进行锁表，在mysql中，如果查询条件带有主键，会锁行数据，如果没有，会锁表。

FOR UPDATE仅适用于InnoDB，且必须在事务处理模块(BEGIN/COMMIT)中才能生效。

使用场景：
借助for update语句，我们可以在应用程序的层面手工实现数据加锁保护操作。就是那些需要业务层面数据独占时，可以考虑使用for update。
场景上，比如火车票订票，在屏幕上显示有票，而真正进行出票时，需要重新确定一下这个数据没有被其他客户端修改。所以，在这个确认过程中，可以使用for update。

7.13 select ... into outfile

将查询结果导出到文件，需要首先配置安全目录，文件只能导出到安全目录中

vim /etc/my.cnf
#[mysqld]中添加如下配置
[mysqld]
...
secure-file-priv=/tmp

用法：
SELECT 列名 FROM table [WHERE 语句] INTO OUTFILE '目标文件'[OPTIONS]

[OPTIONS] 为可选参数选项，OPTIONS 部分的语法包括 FIELDS 和 LINES 子句，其常用的取值有：

FIELDS TERMINATED BY '字符串'                   #设置字符串为字段之间的分隔符，可以为单个或多个字符，默认情况下为制表符‘\t’。
FIELDS [OPTIONALLY] ENCLOSED BY '字符'          #设置字符来括上 CHAR、VARCHAR 和 TEXT 等字符型字段。如果使用了 OPTIONALLY 则只能用来括上 CHAR 和 VARCHAR 等字符型字段。
FIELDS ESCAPED BY '字符'                        #设置如何写入或读取特殊字符，只能为单个字符，即设置转义字符，默认值为‘\’。
LINES STARTING BY '字符串'                      #设置每行开头的字符，可以为单个或多个字符，默认情况下不使用任何字符。
LINES TERMINATED BY '字符串'                    #设置每行结尾的字符，可以为单个或多个字符，默认值为‘\n’ 。

注意：FIELDS 和 LINES 两个子句都是自选的，但是如果两个都被指定了，FIELDS 必须位于 LINES的前面。

例子：
将导出的字段以,分隔

select id,sname,sage from test.t1 into outfile '/tmp/backup.csv' fields terminated by ',';

$ cat /tmp/backup.csv 
1,aaa,10
2,bbb,11
3,ccc,12

8 information_schema视图库（虚拟库）

8.1 视图概念

视图不是表，可以将自定义的查询方法语句定义为视图，之后调用查询视图，简化查询语句。
定义视图：
use 要查询的库名;
create view 视图名 as 自定义的sql查询语法;

USE school;
CREATE VIEW score_cx AS 
SELECT a.sno,AVG(a.score) FROM
score AS a
GROUP BY a.sno
HAVING AVG(a.score)>60;

使用视图查询：
select * from 视图名;

SELECT * FROM score_cx;

8.2 information_schema的作用

与Linux文件系统中的文件类似，表由数据行+元数据组成，元数据存储在'基表中'，无法直接访问。
MySQL提供了DDL、DCL来进行对原数据的修改，提供了information_schema库和show语句查询元数据。

8.3 information_schema.tables视图

记录了系统中所有表的元数据，常用的查询属性如下

DESC information_schema.TABLES

TABLE_SCHEMA    ---->库名
TABLE_NAME      ---->表名
ENGINE          ---->引擎
TABLE_ROWS      ---->表的行数
AVG_ROW_LENGTH  ---->表中行的平均长度（字节）
INDEX_LENGTH    ---->索引的占用空间大小（字节）

8.4 用法例子

1.查询整个数据库中所有库和所对应的表信息

SELECT TABLE_SCHEMA ,GROUP_CONCAT(TABLE_NAME) 
FROM information_schema.tables
GROUP BY TABLE_SCHEMA;

2.查询所有innodb引擎的表及所在的库

SELECT table_name,table_schema FROM information_schema.tables
WHERE ENGINE='innodb';

3.统计world数据库下每张表的磁盘空间占用（concat()+数学计算）

SELECT table_name,CONCAT((TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024,'KB')
FROM information_schema.tables
WHERE TABLE_SCHEMA='world';

8.5 生成备份脚本

原始的备份脚本
mysqldump -uroot -p123 库名表名>/bak/库名_表名.sql

下面使用concat()函数生成这个样式的备份脚本，并生成可执行文件
1.首先配置安全目录，用于脚本文件导出，本例中指定的是/tmp目录

vim /etc/my.cnf
#[mysqld]中添加如下配置
[mysqld]
...
secure-file-priv=/tmp

2.查询语句加入into outfile '文件详细路径'导出可执行文件

SELECT CONCAT('mysqldump -uroot -p123 ',TABLE_SCHEMA,' ',TABLE_NAME,'>/bak/',TABLE_SCHEMA,'_',TABLE_NAME,'.sql')
FROM information_schema.tables
INTO OUTFILE '/tmp/backup.sh';

9 show命令

调用information_schema来查询元数据的命令，常用的命令如下

show  databases;                          #查看所有数据库
show tables;                              #查看当前库的所有表
SHOW TABLES FROM                          #查看某个指定库下的表
show create database world                #查看建库语句
show create table world.city              #查看建表语句
show  grants for  root@'localhost'        #查看用户的权限信息
show  charset；                           #查看字符集
show collation                            #查看校对规则
show processlist;                         #查看数据库连接情况
show index from                           #表的索引情况
show status                               #数据库状态查看
SHOW STATUS LIKE '%lock%';                #模糊查询数据库某些状态
SHOW VARIABLES                            #查看所有配置信息
SHOW variables LIKE '%lock%';             #查看部分配置信息
show engines                              #查看支持的所有的存储引擎
show engine innodb status\G               #查看InnoDB引擎相关的状态信息
show binary logs                          #列举所有的二进制日志
show master status                        #查看数据库的日志位置信息
show binlog evnets in                     #查看二进制日志事件
show slave status \G                      #查看从库状态
SHOW RELAYLOG EVENTS                      #查看从库relaylog事件信息
desc  (show colums from city)             #查看表的列定义信息

最后编辑于：2021.05.24 15:10:03

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 203,456评论 5赞 477
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,370评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 150,337评论 0赞 337
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,583评论 1赞 273
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,596评论 5赞 365
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,572评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,936评论 3赞 395
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,595评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,850评论 1赞 297
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,601评论 2赞 321
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,685评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,371评论 4赞 318
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,951评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,934评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,167评论 1赞 259
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 43,636评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,411评论 2赞 342