1.SQL介绍（结构化查询语言。）
SQL标准：SQL92 SQL99
重点：5.7版本，SQL_MODE>>严格模式
2.SQL作用
SQL：用来管理和操作MySQL内部的对象
对象>>>库、表
库：库名，库属性
表：表名，表属性，列名，记录（行），列属性和约束
3.SQL语句的类型
DDL：数据定义语言（data definition language）
DCL：数据控制语言（data control language）
DML：数据操作语言（data manipulation language）
DQL：数据查询语言（data query language）
4.数据类型
4.1作用
控制数据的规范性，让数据又具体含义，在列上进行控制。
4.2种类
字符串
char(32): 字长长度为32的字符串，存储数据时，一次性提供32字符长度的存储空间，
存不满的话用空格填充。 #最多为255个字符
varchar(32) 可变长度的字符串类型，存数据时，首先进行字符串长度的判断，
比如需存储的字符窜长度是10个字符，只会分配10个字符长度存储空间，并且会单独占用
一个字符长度来记录此次的字符长度，超过255之后，需要两个字节长度记录字符长度。
#最多为65535个字符
面试题：
1.char和varchar的区别？（见上文）
①255 65535
②定长(固定存储空间) 变长（按需）
2.char和varchar如何选择？
①char类型，固定长度的字符串列，比如手机号，身份证号，银行卡号等
②varchar类型，不确定长度的字符串，可以使用。
enum('bj'，'tj'，'sh'，...) 枚举类型
比较适合于将来此列的值是固定范围内的特点，可以使用enum,可以很大程度的优化我们的索引结构。
注：数据较多时，会影响到索引的应用，数字类禁止使用enum类型
数字
tinyint ： -128~127
int ：-2^31~2^31-1
说明：手机号是无法存储到int的。一般是使用char类型来存储收集号
时间
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会受到时区的影响
二进制
5.表属性
存储引擎：engine = InnoDB
字符集：charset = utf8mb4
UTF8 中文 3个字符长度
utf8mb4 中文 4个字符长度
支持emoji字符
排序规则（校对规则）collation
针对英文字符大小写问题
6.列的属性和约束
主键：primary key （Pk）唯一 非空 数字列，整数列 ，无关列
非空：Not NULL 建议对于普通列来讲，尽量设置not null
默认值 default：数字列的默认值使用0，字符串类型，设置为一个nil null
唯一：unique不能重复
自增：auto_increment 针对数字列,自动生成顺序值
无符号 unsigned 针对数字列
注释 comment
7.SQL语句应用
7.1DDL：数据定义语言
库：
（1）建库
mysql> create database munan charset utf8mb4;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| munan |
| mysql |
| performance_schema |
| sys |
| world |
+--------------------+
6 rows in set (0.00 sec)
mysql> show create database munan;
+----------+-------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------+
| munan | CREATE DATABASE `munan` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)
（2）改库
mysql> show create database munan1;
+----------+-------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------+
| munan1 | CREATE DATABASE `munan1` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter database munan1 charset utf8mb4;
Query OK, 1 row affected (0.00 sec)
mysql> show create database munan1;
+----------+--------------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------------+
| munan1 | CREATE DATABASE `munan1` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)
（3）删库（禁止使用！！！）
mysql> drop database munan1;
Query OK, 0 rows affected (0.01 sec)
表：
建表建库规范：
①库名、表名必须是小写字母
因为开发平台和生产平台不同可能会出现问题
②不能以数字开头
③不支持-支持_
④内部函数名不能使用
⑤名字和业务功能有关（his，js，yz，oss，erp）
（1）建表
image.png
CREATE TABLE alibaba (
id INT NOT NULL PRIMARY KEY auto_increment COMMENT '学号',
name VARCHAR(255) NOT NULL COMMENT '姓名',
age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄',
gener enum('n','f','m') NOT NULL DEFAULT 'n' COMMENT '性别'
) charset=utf8mb4 ENGINE=INNODB
（2）改表(alter)
1.改表结构
添加列
删除列
-- 练习：
--在上表中加一个列telnum注释手机号（重点）
ALTER TABLE stu ADD telnum char(11) NOT NULL UNIQUE COMMENT '手机号';
-- 在上表中加一个状态列state,非空，默认值为1
ALTER TABLE alibaba ADD state TINYINT NOT NULL UNSIGNED DEFAULT 1 COMMENT '状态列';
-- 在name后添加 qq 列 varchar(255)
ALTER TABLE alibaba ADD qq VARCHAR(255) NOT NULL UNIQUE COMMENT 'qq' AFTER NAME;
-- 在name 之前添加wechat列
ALTER TABLE alibaba ADD wechat VARCHAR(255) NOT NULL UNIQUE COMMENT 'wechat' AFTER id;
ALTER TABLE alibaba ADD sid VARCHAR(255) NOT NULL UNIQUE COMMENT '号码' FIRST;
-- 修改name数据类型的属性
ALTER TABLE alibaba MODIFY name VARCHAR(128) NOT NULL;
将sgender 改为 gg 数据类型改为 CHAR 类型
（3）删表（危险！！！）
-- 练习：
-- 删除上文刚刚添加的列state
ALTER TABLE alibaba DROP state;
7.2DML：数据操作语言
insert：插入语句（记录）
最简单的方法插入数据
INSERT INTO alibaba VALUES('1','munan','12',18);
最规范的方法插入数据
INSERT INTO alibaba(name,qq,age) VALUES('old','123',20);
-- 查看表数据（不代表生产操作）
SELECT * FROM alibaba;
updata（注意谨慎操作！！！）
标准写法：
UPDATE alibaba SET qq='123456' WHERE id=1;
delete(注意谨慎操作！！！)
DELETE from alibaba WHERE id=1;
需求：将一个大表全部数据清空
DELETE FROM alibaba;
TRUNCATE TABLE alibaba;
DELETE 和 TRUNCATE 区别
- DELETE 逻辑逐行删除，不会降低自增长的起始值，效率很低，碎片较多，会影响将来性能。
- TRUNCATE ,属于物理删除，将表段中的区进行清空，不会产生碎片。性能较高。
需求：使用 UPDATE 替代 DELETE,进行伪删除
1. 添加状态列state （0代表存在，1代表删除）
ALTER TABLE alibaba ADD state TINYINT NOT NULL DEFAULT 0 ;
2. 使用update模拟delete
DELETE FROM oldguo WHERE id=6;
替换为
UPDATE oldguo SET state=1 WHERE id=6;
SELECT * FROM oldguo ;
3. 业务语句修改
SELECT * FROM oldguo ;
改为
SELECT * FROM oldguo WHERE state=0;
7.3DQL：数据查询语言
7.3.1 select
作用
获取MySQL中的数据行
7.3.1.2 单独使用select
--select @@xxxx 获取参数信息
mysql> select @@port;
+--------+
| @@port |
+--------+
| 3306 |
+--------+
1 row in set
Time: 0.014s
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2019-06-18 09:27:12 |
+---------------------+
1 row in set
Time: 0.011s
mysql > select version();
+-----------+
| version() |
+-----------+
| 5.7.26 |
+-----------+
1 row in set
Time: 0.011s
7.3.1.3 SQL92标准的使用语法
select语法执行顺序（单表）
select开始---->
from子句---->
where子句---->
group by子句---->
select后执行条件---->
having子句 --- ->
order by---->limit
from
例子：查询city表中的所有数据
USE world;
SELECT * FROM city; --->适合表数据行较少，生产中使用较少。
例子：查询name和population的所有值
SELECT NAME , population FROM world.city;
SELECT NAME , population FROM city; --->用这个语句要先USE world;
单表查询练习环境：world数据库下表介绍
SHOW TABLES FROM world;
city（城市）
country（国家）
countrylanguage（国家语言）
解析：
id：自增的无关列，数据行的需要
name：城市名字
CountryCode：城市所在的国家代号，CHN,USA,JPN...
district:城市的所在的区域，中国是省的意思，美国是洲的意思
population：城市人口数量
熟悉业务：
刚入职是，DBA的任务
1.搞清楚架构
通过公司架构图,搞清楚数据库的物理架构
逻辑结构：
（1）生产库的信息 <<show databases;>>
（2）库下表的信息
Ⅰ 开发和业务人员，搞好关系
Ⅱ 搞到ER图（PD）
Ⅲ 啥都没有怎么办？
①找到建表语句，如果又注释，读懂注释。如果没有注释，只能根据列名介绍
②找到表中部分数据，分析数据特点，达到了解列功能的目录
where
例子：
--- where 配合 等值查询
----- 查询 city表中，中国的城市信息
SELECT * FROM world.city WHERE CountryCode='CHN';
----- 查询美国的城市信息
SELECT * FROM world.city WHERE CountryCode='USA';
--- where 配合 不等值查询（< > <= >= <>）
----- 查询一下世界上人口小于100人的城市
SELECT * FROM world.city WHERE Population<100;
----- 查询一下世界人口大于1000w的城市
SELECT * FROM world.city WHERE Population>10000000;
--- where 配合 模糊
----- 查询国家代号是C开头的城市
SELECT * FROM world.city where CountryCode LIKE 'C%';
--- where 配合 逻辑连接符（AND OR）
----- 查询一下世界人口在10k到20k之间的城市
SELECT * FROM world.city where Population>10000 AND Population<20000;
----- 查询一下中国或美国的城市信息
SELECT * FROM world.city WHERE CountryCode='CHN' OR CountryCode='USA';
SELECT * FROM world.city WHERE CountryCode IN ('CHN','USA');
4066
group by 配合聚合函数应用
max() ：最大值
min() ：最小值
avg() ：平均值
sum() ：总和
count() ：个数
group_concat() : 列转行
---- 统计每个国家的总人口
SELECT countrycode,SUM(population) FROM world.city GROUP BY countrycode;
---- 统计每个国家的城市个数
SELECT countrycode,COUNT(id) FROM world.city GROUP BY countrycode;
---- 统计中国所有省的所有城市名列表
SELECT District,GROUP_CONCAT(name) FROM world.city WHERE CountryCode='CHN' GROUP BY district;
---- 统计中国所有省的总人口
SELECT district, SUM(population) FROM world.city WHERE CountryCode = 'CHN' GROUP BY District;
---- 统计中国所有省的平均人口
SELECT district, avg(population) FROM world.city WHERE CountryCode = 'CHN' GROUP BY District;
having
说明：having后的条件是不走索引的，可以进行一些优化手段处理
---- 统计中国所有省的总人口数大于1000w的省及人口数
SELECT district,
SUM(population)
FROM world.city
WHERE CountryCode = 'CHN'
GROUP BY District
HAVING SUM(Population)>10000000;
order by
---- 统计中国所有省的总人口从大到小排序
SELECT district,
SUM(population)
FROM world.city
WHERE CountryCode = 'CHN'
GROUP BY District
ORDER BY SUM(Population)DESC;
---- 统计中国所有省的总人口从小到大排序
SELECT district,
SUM(population)
FROM world.city
WHERE CountryCode = 'CHN'
GROUP BY District
ORDER BY SUM(Population);
---- 查询中国所有的城市，并以人口数降序输出
SELECT * FROM city WHERE countrycode='CHN' ORDER BY population DESC;
limit
SELECT *
FROM city
WHERE countrycode='CHN'
ORDER BY population DESC
LIMIT 5,5;
LIMIT M,N 跳过M行显示N行xS
7.3.1.4 多表连接查询
（1）什么时候应用多表？
需要查询的数据是来自于多张表时。
(2)怎么去多表连接查询？
传统的连接：基于where条件（了解）
---> 1.找表之间的关系列
---> 2.排列查询条件
自连接（了解）
内连接（join on）（重点）
---> 1.找表之间的关系列
---> 2.将两表放在join左右
---> 3.将关联条件列放在on后面
---> 4.将所有的查询条件进行罗列
A B
A.x B.y
select A.m,B.n
from
A join B
on A.x=B.y
where
group by
order by
limit
----- 例子
mysql> SELECT course.cno,course.cname,SUM(sc.score)
-> FROM course
-> JOIN sc
-> ON course.cno = sc.cno
-> GROUP BY course.cname;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY
clause and contains nonaggregated column 'school.course.cno' which is
not functionally dependent on columns in GROUP BY clause; this is
incompatible with sql_mode=only_full_group_by
1. 在select后面出现的列，不是分组条件，并且没有在函数中出现。
2. 如果group by 后是主键列或者是唯一条件列。如下：
SELECT
course.cno,course.cname,SUM(sc.score)
FROM course
JOIN sc
ON course.cno = sc.cno
GROUP BY course.cno;
--- 查询oldguo老师教的不及格学生的姓名
SELECT teacher.tname,GROUP_CONCAT(student.sname)
FROM student
JOIN score
ON student.sno=score.sno
JOIN course
ON score.cno=course.cno
JOIN teacher ON
course.tno=teacher.tno
WHERE teacher.tname='oldguo' AND score.score<60
GROUP BY teacher.tno;
--- 统计zhang3,学习了几门课
SELECT student.sname,GROUP_CONCAT(course.cno)
FROM student
JOIN score
ON student.sno=score.sno
JOIN course
ON score.cno=course.cno
WHERE student.sname='zhang3'
GROUP BY student.sno;
--- 查询zhang3,学习的课程名称有哪些?
SELECT student.sname,GROUP_CONCAT(course.cname)
FROM student
JOIN score
ON student.sno=score.sno
JOIN course
ON score.cno=course.cno
JOIN teacher
ON course.tno=teacher.tno
WHERE student.sname='zhang3'
GROUP BY student.sno;
--- 查询oldguo老师教的学生名.
SELECT teacher.tname,GROUP_CONCAT(student.sname)
FROM student
JOIN score
ON student.sno=score.sno
JOIN course
ON score.cno=course.cno
JOIN teacher
ON course.tno=teacher.tno
WHERE teacher.tname='oldguo'
GROUP BY teacher.tno;
--- 查询oldguo所教课程的平均分数
SELECT teacher.tname,AVG(score.score)
FROM teacher
JOIN course
ON course.tno=teacher.tno
JOIN score
ON course.cno=score.cno
WHERE teacher.tname='oldguo'
GROUP BY teacher.tname;
--- 每位老师所教课程的平均分,并按平均分排序
SELECT teacher.tname,AVG(score.score)
FROM teacher
JOIN course
ON teacher.tno=course.tno
JOIN score
ON course.cno=score.cno
GROUP BY teacher.tno
ORDER BY AVG(score.score) DESC ;
--- 查询所有老师所教学生不及格的信息
SELECT teacher.tname,GROUP_CONCAT(student.sname)
FROM student
JOIN score
ON student.sno=score.sno
JOIN course
ON score.cno=course.cno
JOIN teacher ON
course.tno=teacher.tno
GROUP BY teacher.tno;
7.3.1.5 别名
---- 例子
表别名
SELECT te.tname ,GROUP_CONCAT(st.sname)
FROM student AS st
JOIN score AS sc
ON st.sno=sc.sno
JOIN course AS co
ON sc.cno=co.cno
JOIN teacher AS te
ON co.tno=te.tno
WHERE te.tname='oldguo';
列别名
SELECT COUNT(DISTINCT(name)) as 个数 FROM world.city;
7.3.2外连接
left join
7.3.3 Information_schema.tables
元数据
---> 存放在‘基表中’（无法直接查询和修改）
---> DDL进行元数据修改
---> show，desc(show)，information_schema(全局类的统计和);
操作：
use information_schema
desc tables;
TABLE_SCHEMA ---> 表所在的库
TABLE_NAME ---> 表名
ENGINE ---> 表的存储引擎
TABLE_ROWS ---> 表的行数
AVG_ROW_LENGTH ---> 平均行的长度
INDEX_LENGTH ---> 索引的长度
(重点)--- 统计一下每个库的真实数据量
每张表数据量=AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH
SELECT table_schema,sum(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024
FROM information_schema.tables
GROUP BY table_schema;
CONCAT(str1,str2,...)
（以下全是重点）--- 生产需求1
mysqldump -uroot -p123 world city >/tmp/world_city.sql
--- 模仿以上命令，对整个数据库下的1000张表进行单独备份，
--- 排除sys，performance，information_schema
mysqldump -uroot -p123 world city >/tmp/world_city.sql
SELECT CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql")
FROM information_schema.tables
WHERE table_schema NOT IN('sys','performance','information_schema')
INTO OUTFILE '/tmp/bak.sh';
执行上条命令前修改配置文件
vim /etc/my.cnf
secure-file-priv=/tmp
/etc/init.d/mysqld restart
7.3.4 show
show databases; --->查看所有数据库名
show tables; --->查看当前库下的表名
show tables from world; --->查看world数据库下的表名
show create database; --->查看建库语句
show create table; --->查看建表语句
show greants for root@'localhosr'; --->查看用户权限信息
show charset; --->查看所有的字符集
show collation; --->查看校对规则
show full processlist; --->查看数据库的链接状态
show status; --->查看数据库的整体状态
show status like '%lock%'; --->模糊查看数据库的整体状态
show variables; --->查看数据库所有变量情况
show variables like '%innodb%'; --->查看数据库所有变量情况
show engines; --->查看所有支持存储引擎
show engine innodb status; --->查看所有innodb存储引擎状态情况
show binary logs; --->查看二进制日志情况
show binlog events in; --->查看二进制日志事件
show relaylog events in; --->查看relay日志事件
show slave status; --->查看从库状态
show master status; --->查看数据库binlog位置信息
show index from; --->查看表的索引信息
