SQL基础应用

SQL基础应用一

1.内置命令功能介绍
2.字符集、数据类型
3.数据定义语言DDL、数据操作语言DML

1.mysql内置的功能

1.1.连接数据库
-u 登录用户名
-p 登录密码
-S socket位置
-h 登录IP
-P 登录端口
-e 不进入mysql内部执行mysql命令
< 重定向输入

重定向输出

mysql -u root -p123456 -e ‘show databases;’

1.2.内置命令（常用的）
help 打印mysql帮助
\c ctrl+c 结束上个命令运行
\q quit; exit; ctrl+d 退出mysql
\G 将数据竖起来显示
mysql> source /root/world.sql 恢复备份文件

select * from mysql.user\G;

2. SQL基础应用

2.1 SQL介绍

结构化的查询语言
关系型数据库通用的命令
遵循SQL92的标准(SQL_MODE)

2.2 SQL常用种类

DDL 数据定义语言
CREATE、ALTER、DROP
DCL 数据控制语言
GRANT、ROLLBACK、COMMIT
DML 数据操作语言
INSERT、UPDATE、DELETE
DQL 数据查询语言
SELECT、FROM、WHERE

3. 数据库的逻辑结构

库
库名字
库属性:字符集,排序规则
（字符集尽量选择统一的）
表
表名
表属性:存储引擎类型,字符集,排序规则
列名
列属性:数据类型,约束,其他属性
数据行

4. 字符集(charset)

相当于MySQL的密码本(编码表)
数据在硬盘中存取的表达方式，字符编码的合集。
mysql>show charset; //查看当前系统支持的字符集
utf8 : 3个字节
utf8mb4 (建议，也是mysql 8.0默认字符集) : 4个字节,支持emoji

查看系统默认默认字符集：
status;
或者：
SHOW VARIABLES LIKE ‘character%’;
mysql 5.7默认字符集？
character_set_database latin1

character_set_client 为客户端编码方式
character_set_connection 为建立连接使用的编码
character_set_database 数据库的编码
character_set_results 结果集的编码，返回查询结果集或者错误信息到客户端时，使用的编码字符集
character_set_server 数据库服务器的编码，如果创建数据库时，不指定字符集，那么就会默认使用服务器的编码字符集。
character_set_system 系统元数据字符集，它是系统元数据(表名、字段名等)存储时使用的编码字符集，该字段和具体存储的数据无关。总是固定不变的UTF8字符集。

查看某一个数据库的字符集：
mysql> show create database test;
查看某一个表的字符集：
mysql> show create table test.users;

MySQL出现乱码的原因
为什么会出现乱码呢? 这个是我们经常遇到的问题，如图：
对于数据输入而言：

1. 在客户端对相关数据进行编码。
2. MySQL接收到请求时，它会询问客户端通过什么方式对字符编码：客户端通过character_set_client系统变量告知MySQL客户端的编码方式，当MySQL发现客户端的client所传输的字符集与自己的connection不一样时，它会将请求数据从character_set_client转换为character_set_connection；
3. 进行内部操作前会将请求数据从character_set_connection转换为内部操作字符集：在存储的时候会判断编码是否与内部存储字符集（按照优先级判断字符集类型，如下所示）上的编码一致，如果不一致需要转换，其流程如下：
   • 使用每个数据字段的CHARACTER SET设定值；
   • 若上述值不存在，则使用对应数据表的DEFAULT CHARACTER SET设定值(MySQL扩 展，非SQL标准)；
   • 若上述值不存在，则使用对应数据库的DEFAULT CHARACTER SET设定值；
   • 若上述值不存在，则使用character_set_server设定值。

对于数据输出而言：
　　客户端使用的字符集必须通过character_set_results来体现，服务器询问客户端字符集，通过character_set_results将结果转换为与客户端相同的字符集传递给客户端。(character_set_results默认等于character_set_client)

下面我们以某一个汉字来说明如何产生乱码的,例如“华”字，它的不同编码如下：
Unicode编码：0000534E 十进制：21326
UTF8编码 ：E58D8E
UTF16编码：FEFF534E
UTF32编码：0000FEFF0000534E
GBK编码： BBAA
如果“华“字是以UTF8编码存储的，值为E58D8E， 占3个字节，但是转换为latin1编码的时候（latin1编码是1个字节一个字符），就会乱码了：

mysql> SHOW VARIABLES LIKE ‘character%’;
±-------------------------±-----------------------------------+
| Variable_name | Value |
±-------------------------±-----------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /application/mysql/share/charsets/ |
±-------------------------±-----------------------------------+

修改character_set_database字符集：
alter database <数据库名> character set utf8; 或在创建数据库时设置字符集编码

mysql> create database db01 default character set utf8;
mysql> create table db01.test(name varchar(12));
mysql> insert into db01.test value(‘华’);
mysql> select * from db01.test;
±-----+
| name |
±-----+
| 华 |
±-----+

修改配置文件，设置客户端的字符集为latin1，当前db01数据库的字符集为utf8：
[client]
default-character-set=latin1
重启mysql服务，客户端查看时将会乱码。

通常情况下，会在my.cnf中修改服务端与客户端的字符集为一致：
[mysqld]
default-character-set=utf8
[client]
default-character-set=utf8

图像化界面查看没有乱码。。。

思考：
既然MySQL支持编码转换，那为什么还会出现乱码？
如:客户端所使用的字符集大于服务端，刚好客户端的数据字节大于服务端字符集支持字节，则转换时将会丢失数据，产生乱码。

一定要保证connection字符集大于等于client字符集，不然就会信息丢失，比如： latin1 < gb2312 < gbk < utf8，若设置set character_set_client = gb2312，那么至少connection的字符集要大于等于gb2312，否则就会丢失信息
MySQL字符集选择：
一般而言，我们可能选择utf8mb4这个字符集，而不选择utf8. 这个是因为MySQL的utf8并不是真正的UTF8字符集，MySQL的utf8字符编码只有三个字节，节省空间但不能表达全部的UTF-8，只能支持“基本多文种平面”，而utf8mb4才是真正的支持UTF8编码。

5. 排序规则: collation

mysql> show collation;
utf8mb4_general_ci 大小写不敏感
utf8mb4_bin 大小写敏感(存拼音,日文)

6. 数据类型介绍

数据类型是数据的一种属性，用于指定对象可保存的数据的类型，如整数数据、字符数据、货币数据、日期和时间数据等

6.1 数字
整数
tinyint：存储0-255范围的正整数，占用1字节
int：存储负2的31次方（-2147483648）到2的31次方（2147483647）范围之间的所有正负整数，占用4字节

6.2 字符串
char(100)
定长字符串类型,不管字符串长度多长,都立即分配100个字符长度的存储空间,未占满的空间使用"空格"填充
varchar(100)
变长字符串类型,每次存储数据之前,都要先判断一下长度,按需分配此盘空间.
会单独申请一个字符长度的空间存储字符长度(少于255,如果超过255以上,会占用两个存储空间)，所以一个100字符长度的数据实际会占用101字符。

char和varchar的区别：
char是固长字符串类型，varchar是变长字符串类型。也就是说如果字段类型为char(10)，那么即使你输入的字符为’abc’，在数据库中也会保存成’ abc’，字段前面会被自动补上7个空格。而使用varchar(10)则前面不会补空格。

如何选择这两个数据类型?

1. 少于255个字符串长度,定长的列值,选择char
2. 多于255字符长度,变长的字符串,可以选择varchar

enum 枚举数据类型 （约束，提高查询效率）
address enum(‘sz’,‘sh’,‘bj’…)
1 2 3

6.3 时间
datetime
范围为从 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999。
timestamp（支持时区）（可以根据当地的时区自动更新时间）（UTC+8为中国的时间）
范围为从 1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999。

7. DDL的应用

– 7.1 库的定义

— 创建数据库
CREATE DATABASE zabbix CHARSET utf8mb4 COLLATE utf8mb4_bin;
— 查看库情况
SHOW DATABASES;
SHOW CREATE DATABASE zabbix;
— 删除数据库(不代表生产操作)
DROP DATABASE test;
— 修改数据库字符集
— 注意: 一定是从小往大了改,比如utf8—>utf8mb4.
— 目标字符集一定是源字符集的严格超级.
CREATE DATABASE test;
SHOW CREATE DATABASE test;
ALTER DATABASE test CHARSET utf8mb4;

– 7.2 关于库定义规范 *****

— 1.库名使用小写字符
— 2.库名不能以数字开头
— 3.不能是数据库内部的关键字
— 4.必须设置字符集.

– 7.3 DDL-表定义

— 建表
表名,列名,列属性,表属性(先通过SQLyog实现创建)

— 列属性
PRIMARY KEY : 主键约束,表中只能有一个,非空且唯一.
NOT NULL : 非空约束,不允许空值
UNIQUE KEY : 唯一键约束,不允许重复值
DEFAULT : 一般配合 NOT NULL 一起使用.
UNSIGNED : 无符号,一般是配合数字列,非负数
COMMENT : 注释
AUTO_INCREMENT : 自增长的列

创建用户admin %
CREATE TABLE test.stu (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT ‘学号’,
sname VARCHAR(255) NOT NULL COMMENT ‘姓名’,
age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT ‘年龄’,
gender ENUM(‘girl’,‘boy’) NOT NULL DEFAULT ‘girl’ COMMENT ‘性别’,
intime DATETIME NOT NULL DEFAULT NOW() COMMENT ‘入学时间’
)ENGINE INNODB CHARSET utf8mb4;（括号外的为表的属性，表的引擎，表的字符集）

不带符号位的整数(unsigned integer,也称为无符号整数)
NOW()系统时间
select * from 学员信息表;

— 查询建表信息
SHOW TABLES; （查看数据库创建了哪些表）
SHOW CREATE TABLE stu; （查看表属性，如表的字符集）
DESC stu; （查看当前表的列）
unsigned 无符号
CURRENT_TIMESTAMP 当前系统时间

— 创建一个表结构一样的表
CREATE TABLE test LIKE stu; （相同数据库）
create table db01.stu like test.stu; （不同数据库）

— 删表(不代表生产操作)
DROP TABLE test; （相对路径，选中再执行）
drop table db01.stu1;（绝对路径）

— 修改
— 在stu表中添加qq列 *****（添加一个列，默认添加为最后一列）
DESC stu;
ALTER TABLE stu ADD qq VARCHAR(20) NOT NULL COMMENT ‘qq号’;

— 在sname后加微信列 ***（在某一列后面添加一个列）
ALTER TABLE stu ADD wechat VARCHAR(64) NOT NULL UNIQUE COMMENT ‘微信号’ AFTER sname;

— 在id列前加一个新列num ***（添加为第一列）
ALTER TABLE stu ADD num INT NOT NULL UNIQUE COMMENT ‘身份证’ FIRST ;
DESC stu;

— 修改sname数据类型的属性 ***（只能修改数据类型属性）
DESC stu;
ALTER TABLE stu MODIFY sname VARCHAR(64) NOT NULL COMMENT ‘姓名’;

— 将gender 改为 sex 数据类型改为 CHAR 类型 ***（修改列名和列属性）
ALTER TABLE stu CHANGE gender sex CHAR(4) NOT NULL COMMENT ‘性别’;

alter table stu change gender gender char(8);

— 把刚才添加的列都删掉 ***（删除列）
ALTER TABLE stu DROP num;
DESC stu;
ALTER TABLE stu DROP qq;
ALTER TABLE stu DROP wechat;

drop 删库，删表
delete 删行（删表的所有行）

7.4 DCL
grant 授权
revoke 权限回收

7.5 DML
– insert 插入（写入数据），针对表的行插入数据
DESC stu;

— 最偷懒
INSERT stu VALUES(1,‘zs’,18,‘m’,NOW()); （针对整个表的所有列插入数据，大多数情况不适用）
SELECT * FROM stu;

— 最规范
INSERT INTO stu(id,sname,age,sex,intime)
VALUES (2,‘ls’,19,‘f’,NOW()); （针对表的部分列插入数据）
INSERT INTO stu(sname,age,sex,intime)
VALUES (‘ls’,19,‘f’,NOW()); （id主键设置为自增列，插入是可以不插入id值）

— 针对性的录入数据
INSERT INTO stu(sname,age,sex)
VALUES (‘w5’,11,‘m’);

— 一次性录入多行
INSERT INTO stu(sname,age,sex)
VALUES
(‘aa’,11,‘m’),
(‘bb’,12,‘f’),
(‘cc’,13,‘m’);

故障思考：已经添加了数据的表，如果新添加列并设置该列属性为非空，则会报错。（有问题）

– update(一定要加where条件) （对某一个单元格进行操作）
（不要执行）UPDATE stu SET sname=‘aaa’; （该操作会让所有的sname都变为aaa了，要交where）
SELECT * FROM stu;
UPDATE stu SET sname=‘bb’ WHERE id=6;（where后一般跟唯一值）

– delete (一定要有where条件) （删除行）
DELETE FROM stu; （会删除所有行）
DELETE FROM stu WHERE id=9; （删除某一行）

– 生产中屏蔽delete功能
— 使用update替代delete
ALTER TABLE stu ADD is_del TINYINT DEFAULT 0 ;
UPDATE stu SET is_del=1 WHERE id=7;
SELECT * FROM stu WHERE is_del=0;

7.6 练习
use school

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

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

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

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

❤SQL语句设置主外键约束：
alter table 外键表名 add constraint 约束名称 foreign key (外键字段) references 主键表名(约束列名)
ALTER TABLE school.course ADD CONSTRAINT f_key1 FOREIGN KEY(tno) REFERENCES school.teacher(tno);

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,‘test’);

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;

linux导入sql文件（world.sql）：
#cd /root
#rz
上传服务器
导入mysql
mysql -uroot -p < /root/world.sql
登录mysql，执行source /root/world.sql

sqlyog导入sql文件：
直接将本机的SQL文件拖入软件中就能打开

SQL基础应用二

数据查询语言DQL
show语句

1. DQL 介绍
select
show

查询不会改变数据库内容

2.select 语句的应用
（1）获取数据库本身的信息（2）获取表的信息

2.1 select单独使用的情况***

mysql> select @@basedir;
mysql> select @@port;
mysql> select database();
mysql> select now();

2.2 select 通用语法(单表) *****
select 列
from 表
where 条件
group by 条件
having 条件
order by 条件
limit 限制

2.3 学习环境的说明
mysql -uroot -p < /root/world.sql
登录mysql，执行source /root/world.sql
world数据库
city 城市表
country 国家表
countrylanguage 国家的语言

city表结构
mysql> desc city;
±------------±---------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±------------±---------±-----±----±--------±---------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | MUL | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | | 0 | |
±------------±---------±-----±----±--------±---------------+

5 rows in set (0.00 sec)
mysql>

ID : 城市序号(1-…)
name : 城市名字
countrycode : 国家代码,例如:CHN,USA
district : 区域: 中国 省 美国 州
population : 人口数

2.4 SELECT 配合 FROM 子句使用
– select 列,列,列 from 表
— 例子:
1. 查询表中所有的信息(生产中几乎是没有这种需求的)
USE world;
SELECT id,NAME,countrycode,district,population FROM city;
或者:
SELECT * FROM city;

2. 查询表中 name和population的值
SELECT NAME,population FROM city;

2.5 SELECT 配合 WHERE 子句使用
– select 列,列,列 from 表 where 过滤条件

– where等值条件查询 *****
例子:

1. 查询中国所有的城市名和人口数
   SELECT NAME,population FROM city WHERE countrycode=‘CHN’;

– where 配合比较判断查询(> < >= <=) *****
例子:

1. 世界上小于100人的城市名和人口数
   SELECT NAME,population FROM city WHERE population<100;

思考：
查询国家人口数大于10亿人的国家并显示其名称与人数

– where 配合 逻辑连接符(and or)
例子:

1. 查询中国人口数量大于800w的城市名和人口
   SELECT NAME,population FROM city
   WHERE countrycode=‘CHN’ AND population>8000000;

2. 查询中国或美国的城市名和人口数
   SELECT NAME,population FROM city
   WHERE countrycode=‘CHN’ OR countrycode=‘USA’;

3. 查询人口数量在500w到600w之间的城市名和人口数
   SELECT NAME,population FROM city
   WHERE population>5000000 AND population<6000000;
   或者:
   SELECT NAME,population FROM city
   WHERE population BETWEEN 5000000 AND 6000000;

– where 配合 like 子句 模糊查询 *****
例子:

1. 查询一下contrycode中带有CH开头,城市信息

SELECT * FROM city
WHERE countrycode LIKE ‘CH%’;

注意:不要出现类似于 %CH%,前后都有百分号的语句,因为不走索引,性能极差
如果业务中有大量需求,我们用"ES"来替代（ES为非关系型数据库的一种）

– where 配合 in 语句

例子:

1. 查询中国或美国的城市信息.
   SELECT NAME,population FROM city
   WHERE countrycode=‘CHN’ OR countrycode=‘USA’;
   或者:
   SELECT NAME,population FROM city
   WHERE countrycode IN (‘CHN’ ,‘USA’);

❤2.4 SELECT 配合 GROUP BY + 聚合函数应用
MAX(), 最大值
MIN(), 最小值
AVG(), 平均值
COUNT(), 计数
SUM() 求和
GROUP_CONCAT()

2.5.2 GROUP BY
将某列中有共同条件的数据行,分成一组,然后再进行聚合函数操作.
例子:

1. 统计每个国家的城市个数
   SELECT countrycode,COUNT(id) FROM city
   GROUP BY countrycode; （分组查询）

2. 统计每个国家的总人口数.
   SELECT countrycode,SUM(population) FROM city
   GROUP BY countrycode;

3. 统计每个国家省的个数
   SELECT countrycode,COUNT(DISTINCT district) FROM city
   GROUP BY countrycode; （DISTINCT去除重复的值）

4. 统计中国每个省的总人口数
   SELECT district, SUM(population) FROM city
   WHERE countrycode=‘CHN’
   GROUP BY district;

5. 统计中国每个省城市的个数
   SELECT district,COUNT(NAME) FROM city
   WHERE countrycode=‘CHN’
   GROUP BY district;

6. 统计中国每个省城市的名字列表GROUP_CONCAT()
   guangdong guangzhou,shenzhen,foshan…

SELECT district, GROUP_CONCAT(NAME) FROM city
WHERE countrycode=‘CHN’
GROUP BY district;

7. 小扩展
   anhui : hefei,huaian …
   SELECT CONCAT(district,“:”,GROUP_CONCAT(NAME)) FROM city
   WHERE countrycode=‘CHN’
   GROUP BY district;

CONCAT将查询到的内容整合到一个单元格，可添加分隔符

2.6 SELECT 配合 HAVING 子句

（where放在group by前面（不能基于聚合函数使用，如where sum(population)>50000000），having放在group by后面）（where对结果进行处理，group by生成结果）

统计所有国家的总人口数量,将总人口数大于5000w的过滤出来
SELECT countrycode,SUM(population) FROM city
GROUP BY countrycode
HAVING SUM(population)>50000000

2.7 SELECT 配合 ORDER BY 子句

统计所有国家的总人口数量,将总人口数大于5000w的过滤出来,
并且按照从大到小顺序排列
SELECT countrycode,SUM(population) FROM city
GROUP BY countrycode
HAVING SUM(population)>50000000
ORDER BY SUM(population) DESC ; //DESC降序排列，ASC升序排列(数字、英文排列)

2.8 SELECT 配合 LIMIT 子句

例子:

1. 统计所有国家的总人口数量,
   将总人口数大于5000w的过滤出来,
   并且按照从大到小顺序排列,只显示前三名

SELECT countrycode,SUM(population) FROM city
GROUP BY countrycode
HAVING SUM(population)>50000000
ORDER BY SUM(population) DESC
LIMIT 3 OFFSET 0;（从第0行开始往后查看3行）
或
select countrycode,sum(population) from city
group by countrycode
having sum(population)>50000000
order by sum(population) desc
limit 0,3;

通过汉字实现排序？？
mysql汉字排序的规则：
MySQL默认只支持对日期、时间和英文字符串进行排序，如果对中文进行order by很可能得不到想要的结果，如下面的查询并不会按我们所想的根据汉字的拼音进行排序：
SELECT from user order by user_name;
如果相对中文进行排序的话，可以使用CONVERT（coloum_name USING GBK）将中文转为GBK编码形式，然后再排序，就可以实现根据汉子的拼音进行排序：
SELECT * from user order by CONVERT(user_name USING GBK);

SELECT countrycode,SUM(population) FROM city
GROUP BY countrycode
HAVING SUM(population)>50000000
ORDER BY SUM(population) DESC
LIMIT 3 OFFSET 3

LIMIT M,N :跳过M行,显示一共N行
LIMIT Y OFFSET X: 跳过X行,显示一共Y行

2.9 练习题:

1. 统计中国每个省的总人口数，只打印总人口数小于100w的
   SELECT district ,SUM(population) FROM city
   WHERE countrycode=‘CHN’
   GROUP BY district
   HAVING SUM(population)<1000000;

2. 查看中国所有的城市，并按人口数进行排序(从大到小)
   SELECT * FROM city
   WHERE countrycode=‘CHN’
   ORDER BY population DESC;

3. 统计中国各个省的总人口数量，按照总人口从大到小排序
   SELECT district ,SUM(population) FROM city
   WHERE countrycode=‘CHN’
   GROUP BY district
   ORDER BY SUM(population) DESC ;

4. 统计中国,每个省的总人口,找出总人口大于500w的,
   并按总人口从大到小排序,只显示前三名
   SELECT district ,SUM(population) FROM city
   WHERE countrycode=‘CHN’
   GROUP BY district
   HAVING SUM(population)>5000000
   ORDER BY SUM(population) DESC
   LIMIT 3;

2.10 小结
select disctrict, count(name) from city
where countrycode=‘CHN’
group by district
having count(name) >10
order by count(name) desc
limit 3;

2.11 union 和 union all
作用: UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
注意：UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

需求: 查询中或者美国的城市信息
SELECT * FROM city WHERE countrycode=‘CHN’ OR countrycode=‘USA’;

改写为:
SELECT * FROM city WHERE countrycode=‘CHN’
UNION ALL
SELECT * FROM city WHERE countrycode=‘USA’;

多表合并查询
SELECT * FROM student
UNION
SELECT * FROM student_copy;

面试题: union 和 union all 的区别 ?
union all 不做去重复（在查询结果集中体现）
union 会做去重操作

3. 多表连接查询(内连接)
多表查询相对于单表，在其他功能保持一致的情况下，可以选择多张表的内容进行查询。
这多张表一定有相关列是有关联的。
3.1 作用

3.2 多表连接基本语法
1.最核心的是，找到多张表之间的关联条件列
2.列书写时，必须是：表名.列
3.所有涉及到的查询列，都要放在select后
4.将所有的过滤、分组、排序等条件安装顺序写在on后面

–项目构建
CREATE DATABASE school CHARSET utf8;
USE school
CREATE TABLE 学员信息表(
学号 INT PRIMARY KEY AUTO_INCREMENT NOT NULL COMMENT ‘学号’,
学员姓名 VARCHAR(20) NOT NULL COMMENT ‘姓名’,
学员年龄 TINYINT UNSIGNED NOT NULL COMMENT ‘年龄’,
学员性别 ENUM(‘男’,‘女’) NOT NULL DEFAULT ‘男’ COMMENT ‘性别’
)ENGINE=INNODB CHARSET=utf8;

CREATE TABLE 课程表(
课程编号 INT NOT NULL PRIMARY KEY COMMENT ‘课程编号’,
课程名称 VARCHAR(20) NOT NULL COMMENT ‘课程名字’,
老师编号 INT NOT NULL COMMENT ‘教师编号’
)ENGINE=INNODB CHARSET utf8;

CREATE TABLE 成绩表 (
学号 INT NOT NULL COMMENT ‘学号’,
课程编号 INT NOT NULL COMMENT ‘课程编号’,
分数 INT NOT NULL DEFAULT 0 COMMENT ‘成绩’
)ENGINE=INNODB CHARSET=utf8;

CREATE TABLE 老师信息表(
老师编号 INT NOT NULL PRIMARY KEY COMMENT ‘教师编号’,
老师姓名 VARCHAR(20) NOT NULL COMMENT ‘教师名字’
)ENGINE=INNODB CHARSET utf8;

INSERT INTO 学员信息表(学号,学员姓名,学员年龄,学员性别)
VALUES (1,‘zhangsan’,18,‘男’);

INSERT INTO 学员信息表(学号,学员姓名,学员年龄,学员性别)
VALUES
(2,‘zhangsi’,18,‘男’),
(3,‘lisi’,18,‘男’),
(4,‘wangwu’,19,‘女’);

INSERT INTO 学员信息表
VALUES
(5,‘zh4’,18,‘男’),
(6,‘zhaosi’,18,‘男’),
(7,‘maliu’,19,‘女’);

INSERT INTO 学员信息表(学员姓名,学员年龄,学员性别)
VALUES
(‘pony’,20,‘男’),
(‘jack’,20,‘女’),
(‘ben’,25,‘男’);

INSERT INTO 老师信息表(老师编号,老师姓名) VALUES
(101,‘pony’),
(102,‘Tony’),
(103,‘bill’);

INSERT INTO 课程表(课程编号,课程名称,老师编号)
VALUES
(1001,‘linux’,101),
(1002,‘python’,102),
(1003,‘mysql’,103);

INSERT INTO 成绩表(学号,课程编号,分数)
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 学员信息表;
SELECT * FROM 老师信息表;
SELECT * FROM 课程表;
SELECT * FROM 成绩表;

1 SELECT * from user order by CONVERT(user_name USING GBK);

学员信息表：
学号
学员姓名
学员年龄
学员性别

老师信息表：
老师编号
老师姓名

课程表：
课程编号
课程名称
老师编号

成绩表：
学号
课程编号
分数

3.3 多表连接例子（多表查询）

– 1. 统计zhangsan,学习了几门课
SELECT 学员信息表.学员姓名,成绩表.课程编号
FROM 学员信息表 JOIN 成绩表
ON 学员信息表.学号**=**成绩表.学号
WHERE 学员信息表.学员姓名=‘zhangsan’;

JOIN //关联其他表

SELECT 学员信息表.学员姓名,COUNT(成绩表.课程编号)
FROM 学员信息表 JOIN 成绩表
ON 学员信息表.学号=成绩表.学号
WHERE 学员信息表.学员姓名=‘zhangsan’;

– 2. 查询zhangsan,学习的课程名称有哪些?
SELECT 学员信息表.学员姓名,课程表.课程名称
FROM 学员信息表 JOIN 成绩表
ON 学员信息表.学号=成绩表.学号
JOIN 课程表
ON 成绩表.课程编号=课程表.课程编号
WHERE 学员信息表.学员姓名=‘zhangsan’

SELECT 学员信息表.学员姓名,GROUP_CONCAT(课程表.课程名称)
FROM 学员信息表 JOIN 成绩表
ON 学员信息表.学号=成绩表.学号
JOIN 课程表
ON 成绩表.课程编号=课程表.课程编号
WHERE 学员信息表.学员姓名=‘zhangsan’
GROUP BY 学员信息表.学员姓名;

– 3. 查询bill老师和他教的学生名和学生数量.
SELECT 老师信息表.老师姓名,学员信息表.学员姓名,学员信息表.学员姓名
FROM 老师信息表 JOIN 课程表
ON 老师信息表.老师编号=课程表.老师编号
JOIN 成绩表
ON 课程表.课程编号=成绩表.课程编号
JOIN 学员信息表
ON 成绩表.学号=学员信息表.学号
WHERE 老师信息表.老师姓名=‘bill’

SELECT 老师信息表.老师姓名,GROUP_CONCAT(学员信息表.学员姓名),COUNT(学员信息表.学员姓名)
FROM 老师信息表 JOIN 课程表
ON 老师信息表.老师编号=课程表.老师编号
JOIN 成绩表
ON 课程表.课程编号=成绩表.课程编号
JOIN 学员信息表
ON 成绩表.学号=学员信息表.学号
WHERE 老师信息表.老师姓名=‘bill’
GROUP BY 老师信息表.老师姓名;

– 4. 查询bill老师所教课程的平均分数
SELECT 老师信息表.老师姓名,AVG(成绩表.分数)
FROM 老师信息表 JOIN 课程表
ON 老师信息表.老师编号=课程表.老师编号
JOIN 成绩表
ON 课程表.课程编号=成绩表.课程编号
WHERE 老师信息表.老师姓名=‘bill’
GROUP BY 成绩表.课程编号; （一个老师教多门课程时有效）
– 5. 每位老师所教课程的平均分,并按平均分排序
SELECT 老师信息表.老师姓名,课程表.课程名称,AVG(成绩表.分数)
FROM 老师信息表 JOIN 课程表
ON 老师信息表.老师编号=课程表.老师编号
JOIN 成绩表
ON 课程表.课程编号=成绩表.课程编号
GROUP BY 老师信息表.老师姓名,课程表.课程名称
ORDER BY AVG(成绩表.分数) //不指定升降序默认为升序排列

– 6. 查询bill所教的不及格的学生姓名
SELECT 老师信息表.老师姓名,学员信息表.学员姓名,成绩表.分数
FROM 老师信息表 JOIN 课程表
ON 老师信息表.老师编号=课程表.老师编号
JOIN 成绩表
ON 课程表.课程编号=成绩表.课程编号
JOIN 学员信息表
ON 成绩表.学号=学员信息表.学号
WHERE 老师信息表.老师姓名=‘bill’ AND 成绩表.分数<60

– 7. 查询所有老师所教学生不及格的信息(扩展)

SELECT 老师信息表.老师姓名,GROUP_CONCAT(CONCAT(学员信息表.学员姓名,“:”,成绩表.分数))
FROM 老师信息表
JOIN 课程表
ON 老师信息表.老师编号=课程表.老师编号
JOIN 成绩表
ON 课程表.课程编号=成绩表.课程编号
JOIN 学员信息表
ON 成绩表.学号=学员信息表.学号
WHERE 成绩表.分数<60
GROUP BY 老师信息表.老师编号

– 8.别名应用
表别名 :
SELECT t.老师姓名,GROUP_CONCAT(CONCAT(st.学员姓名,“:”,成绩表.分数))
FROM 老师信息表 AS t
JOIN 课程表 AS c
ON t.老师编号=c.老师编号
JOIN 成绩表
ON c.课程编号=成绩表.课程编号
JOIN 学员信息表 AS st
ON 成绩表.学号=st.学号
WHERE 成绩表.分数<60
GROUP BY t.老师编号

表别名是全局调用的.

列别名:
SELECT t.老师姓名 AS 老师名 ,GROUP_CONCAT(CONCAT(st.学员姓名,“:”,成绩表.分数)) AS 不及格的
FROM 老师信息表 AS t
JOIN 课程表 AS c
ON t.老师编号=c.老师编号
JOIN 成绩表
ON c.课程编号=成绩表.课程编号
JOIN 学员信息表 AS st
ON 成绩表.学号=st.学号
WHERE 成绩表.分数<60
GROUP BY t.老师编号

列别名可以被 having 和 order by 调用

表名列别名不会改变表的内容

show介绍*****
show databases; 查看数据库名
show tables; 查看表名
show create database xx; 查看建库语句
show create table xx; 查看建表语句
show processlist; 查看所有用户连接情况
show charset; 查看支持的字符集
show collation; 查看所有支持的校对规则
show grants for xx; 查看用户的权限信息
show variables like ‘%xx%’ 查看参数信息
show engines; 查看所有支持的存储引擎类型
show index from xxx 查看表的索引信息
show engine innodb status\G 查看innoDB引擎详细状态信息
show binary logs 查看二进制日志的列表信息
show binlog events in ‘’ 查看二进制日志的事件信息
show master status ; 查看mysql当前使用二进制日志信息
show slave status\G 查看从库状态信息 //主从复制分离看到的
show relaylog events in ‘’ 查看中继日志的事件信息
show status like ‘’ 查看数据库整体状态信息