一.昨天遗留的不懂的地方
数据类型
char（n）和varchar（n）的区别
共同点：
1.都是字符串数据类型
2.小括号中设置的数字，都是字符的最大长度
不同点：
1.char是定长，存不满用空字符串填充
2.varchar是变长
3.varchar需要单独占用1-2字节存储字符长度
4.char理论上最多255字节，varchar理论上是65533 char（255）
二.SQL常用分类
1.DDL 数据定义语言
1.1库的定义
增
CREATE DATABASE yzg CHARSET utf8mb4;
规范：
01.字符集必须设置。
02.库的名称不要有大写字母。
03.不要有数字开头。
04.库名要和业务有关。
05.库名不要太长
06.库名不能是关键字
删
drop database yzg；
一般只有管理员有权限删除数据库
改
alter database 库名 charset utf8mb4；
查
show databases；
show create database test；
1.2表的定义
增
表属性：
存储引擎类型
字符集
列属性：
数据类型
约束
其他属性
USE yzg;
CREATE TABLE stu (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
sname VARCHAR(32) NOT NULL COMMENT '姓名',
age TINYINT NOT NULL DEFAULT 18 COMMENT '年龄',
sex ENUM('male','female','others')NOT NULL DEFAULT 'others' COMMENT '性别',
intime DATETIME NOT NULL DEFAULT NOW() COMMENT '入学时间'
)ENGINE=INNODB CHARSET=utf8mb4 COMMENT '学生表';
建表规范：
1.表名：小写，业务相关，不能数字开头，不能太长，不能是关键字。
2.表属型：字符集，存储引擎，注释
3.列属性：合适的数据类型（简短，合适的，完整的），必须要有主键（id，自增长数字列）
每个列要非空，并设定默认值
必须要有注释
[https://www.cnblogs.com/xiaoliwang/p/8956736.html]
删
drop table 表名;
truncate table 表名;
区别：
drop：表结构+表行
truncate：表行删除（表段物理清空）保留表结构。
改
例子：
--在stu表中添加手机列；
ALTER TABLE stu ADD shouji BIGINT NOT NULL UNIQUE COMMENT '添加手机号';
-- 在name列后添加dizhi列
ALTER TABLE stu ADD dizhi VARCHAR(100) NOT NULL COMMENT '地址' AFTER sname;
-- 在首列添加qq号列
ALTER TABLE stu ADD qq BIGINT NOT NULL UNIQUE COMMENT 'qq号' FIRST;
-- 删除列
ALTER TABLE stu DROP qq ;
ALTER TABLE stu DROP shouji;
ALTER TABLE stu DROP dizhi;
-- 修改表属性
ALTER TABLE stu ENGINE=INNODB;
-- 查询表属性
SHOW CREATE TABLE stu;`
--修改表名
ALTER TABLE stu RENAME TO xs;
-- 归档表例子
--- 1. RENAME 旧表
ALTER TABLE xs RENAME TO xs_201911;
CREATE TABLE xs LIKE xs_201911;
--- 2. Oracle 物化视图 (了解)
-- 将sex列名改为gender,数据类型改为tinyint,加个注释(0代表nv,1代表男)
ALTER TABLE xs CHANGE sex gender TINYINT NOT NULL DEFAULT 1 COMMENT '0代表nv,1代表男' ;
查
DESC xs;
说明：归档表，日志表
rename老的表为新的名称：xx_201906
create table xxx like xxx_20906;
注意：
所有表结构变更，都会自动锁表，尽量避免大表
建议：
1.最好业务不繁忙做
2.使用第三方工具pt-osc（percona-toolkit）自己扩展
[https://www.percona.com/software/database-tools/percona-toolkit]
3.对于归档表，可以使用pt-archiver 自己扩展
online DDL。
小结：DDL
(1) 建库建表规范
建库规范: *****
1. 字符集必须设置
2. 不要有大写字母
3. 不要有数字开头
5. 库名要和业务有关
6. 库名不要太长
7. 库名不能是关键字
建表规范: *****
1. 表名: 小写,业务相关,不能数字开头,不能太长,不能是关键字.
2. 表属性: 字符集,存储引擎,注释
3. 列属性:
合适的数据类型(简短,合适的,完整的)
必须要有PK(id 自增长数字列).
每个列要非空,并设定默认值.
必须要有注释.
(2) 修改表结构
-- 在stu表中添加shouji列 *****
ALTER TABLE stu ADD shouji BIGINT NOT NULL UNIQUE COMMENT '手机号';
-- 归档表例子 *****
-- RENAME 旧表
ALTER TABLE xs RENAME TO xs_201911;
CREATE TABLE xs LIKE xs_201911;
(3) Online DDL
注意:
所有表结构变更,都会自动锁表.尽量避免大表Online DDL.
2.DCL
grant
revoke
show grants for
3.DML数据操作语言
主要针对表中数据进行操作
insert数据插入
INSERT INTO xs(id,sname,age,gender,intime)
VALUES(1,'zs',18,1,'2019-11-13');
SELECT * FROM xs;
INSERT INTO xs(id,sname,age,gender,intime)
VALUES(2,'ls',18,1,'2019-11-13'),(3,'xh',19,0,'2019-11-13');
delete数据删除
delete from xs where id=4;
select * from xs;
说明：
delete语句数据逻辑删除，磁盘空间不会立即释放，会产生碎片。
上亿行的大表，批量删除5000w（按时间条件）
update数据修改
UPDATE xs SET age=28 WHERE id=5;
伪删除
ALTER TABLE xs ADD state TINYINT NOT NULL DEFAULT 1;
SELECT * FROM xs;
原语句:
DELETE FROM xs WHERE id=5;
替换为:
UPDATE xs SET state=0 WHERE id=5;
原业务语句:
SELECT * FROM xs;
替换为:
SELECT * FROM xs WHERE state=1;
4.DQL 数据查询语言 *****
1.select
01.单独使用
(1)查询参数
SELECT @@datadir;
SELECT @@socket;
SELECT @@port;
SELECT @@basedir;
SELECT @@innodb_flush_log_at_trx_commit;
SHOW VARIABLES LIKE '%trx%';
(2)简易计算
SELECT 1024*1024;
SELECT 1+2;
(3)函数查询
SELECT NOW;
SELECT CONCAT("开心");
02.配合多子句（标准用法）
(1) FROM
(2) WHERE
(3) GROUP BY
(4) HAVING
(5)ORDER BY
(6)LIMIT
FROM
单词:
world 世界
============================
city 城市
DESC city;
============================
id 城市ID
NAME 城市名
countrycode 国家代码(CHN,JPN,USA)
district 区域(省,州)
population 人口
先后顺序 如果出现几个参数必须按照当前顺序
--from 字句应用
--1.查询city表中的所有数据（不代表生产操作）
SELECT * FROM city;
--2.查询city表中所有的name和population
SELECT NAME,population FROM city;
WHERE
--where 字句应用
--where 等值查询
--1.查看中国所有城市信息
SELECT * FROM city
WHERE countrycode='CHN';
--where的不等值查询
--1.城市人口小于100人的城市
SELECT * FROM city
WHERE population<100;
SELECT * FROM city
不是中国的城市信息
WHERE countrycode<>'CHN';
-- where 配合 like语句使用
--- 1. 查询国家代号是CH开头的城市信息.
SELECT * FROM city
WHERE countrycode LIKE 'CH%';
注意: LIKE 语句 百分号不要出现在查询条件前,因为查询不走索引.
-- where 配合逻辑连接符(and or)
--- 1. 中国城市人口大于500w的城市
SELECT * FROM city
WHERE countrycode='CHN' AND population>5000000 ;
--- 2. 中国或者美国城市信息
SELECT * FROM city
WHERE countrycode='CHN' OR countrycode='USA';
--where 配合in
SELECT * FROM city
WHERE countrycode IN ('CHN','USA');
--where配合between AND
--查询 人口数量在100w-200w之间的城市
SELECT * FROM city
WHERE population > 1000000 AND population <= 2000000;
SELECT * FROM city
WHERE population BETWEEN 1000000 AND 2000000;
GROUP BY +聚合函数应用
聚合函数？
max（）最大值
min（）最小值
avg（）平均值
count（）数量
sum()求和
group_concat()列转行
说明：有group by字句，必然会有聚合函数
在业务查询需求中，需要对于表中数据按照数据特点进行分别统计时，GROUP BY +聚合函数来实现
有 GROUP by子句,必然会有聚合函数
在业务查询需求中,需要对于表中数据按照数据特点进行分别统计时,GROUP BY + 聚合函数来实现
--- 1. 统计世界的所有人口总和
SELECT SUM(population) FROM city ;
--- 2. 统计国家的总人口数量
SELECT countrycode, SUM(population) FROM city
GROUP BY countrycode ;
--- 3. 统计中国每个省的总人口数
SELECT countrycode,district , SUM(population)
FROM city
GROUP BY district;
--- 5. 统计中国每个省的城市名列表.
SELECT district,GROUP_CONCAT(NAME)
FROM city
WHERE countrycode='CHN'
GROUP BY district;
--- 6. 关于 GROUP BY 的限制
RROR 1055 (42000): Expression
-- #1 of SELECT list is not in GROUP BY clause and contains nonaggregated
-- column 'world.city.CountryCode' which is not functionally dependent
-- on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
HAVING 后过滤
SELECT district,SUM(population)
FROM city WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population) >10000000;
ORDER BY
--- 查询中国所有的城市信息,并按照人口数排序输出结果
SELECT * FROM city
WHERE countrycode='CHN'
ORDER BY population DESC ;
--- 统计中国每个省的总人口数,只显示总人口大于500w,并按照总人口降序输出
SELECT district , SUM(population)
FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC ;
LIMIT
统计中国每个省的总人口数，只显示人口数大于500W，并按照总人口降序输出前5名
SELECT district , SUM(population)
FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC
LIMIT 10;
SELECT district , SUM(population)
FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC
LIMIT 2,3;
SELECT district , SUM(population)
FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC
LIMIT 4 OFFSET 2;
distinct 去重复
select distinct countrycode from city;
union&&union all
SELECT * FROM city WHERE countycode='CHN'
UNION ALL
SELECT * FROM city WHERE countycode='USA
union:去重复
union all :不去重复
2.show
yearing SQL语句审核平台
图片.png
