2019/04/08 09:01
SQL介绍
==结构化查询语言(Structured Query Language)==
简称SQL(发音：/ˈes kjuː ˈel/ "S-Q-L")，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。
- MySQL-5.7版本后符合SQL-92标准的严格模式。
- 数字不用加双引号，字符串要加
数据类型、表属性、字符集
数据类型
作用：保证数据的准确性和标准性。
种类
- 数值类型
TINYINT ：-128~127
INT ：-2^31~2^31-1
说明：手机号是无法存储到INT的,一般是使用char类型来存储收集号。
数值类型
- 字符类型
#char
固定长度字符串，存储效率高，不需要判断字符长度，直接分配空间，浪费不必要的资源。
#varchar
相比前者，存储效率低，需要判断字符长度，按需分配空间。
如何选择？
#大量插入（insert）操作时，推荐使用char去代替varchar。
#业务中，大量是查询类操作，数据量级又比较大的情况下，变长长度数据类型，可以考虑采用varchar，一方面节省空间，可以有效的减少索引树的高度，从而提高索引的优化查询的效果。
字符类型
- 时间类型
#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会受到时区的影响。
时间类型
-
二进制类型
二进制类型
表属性
列属性
primary key 主键
#非空且唯一
not null 非空
#可以设置默认值
unique key 唯一键
#列值不能重复
unsigned 无符号
#针对数字列（非负数等等）
其他属性
key 索引
#建立索引，优化查询
default 默认值
#列中，无录入值时，默认使用default值
auto_increment 自增长
#针对数字列（默认从1开始，可以设定起始点与偏移量）
comment 注释
字符集校对规则
字符集
- utf-8
- utf8mb4
utf8mb4是utf8的超集，专门用来兼容四字节的unicode（emoji表情等等）
校对规则
大小写是否敏感
SQL分类
DDL：数据定义语言
DCL：数据控制语言
DML：数据操作语言
DQL：数据查询语言 #MySQL查询语句类型
DDL语句
库定义
- 创建
#创建数据库
>create database school;
#查看创建库的命令内容
>show create database school;
规范
- 库名大小写统一（Windows不区分大小写，Linux区分大小写）
- 建库时指定字符集
>create database db charset utf8mb4;
- 库名不能以数字开头
- 见名知其意
- 删除(禁止删库)
>drop database db;
- 修改
#修改字符集为utf8（默认为latin1）
>alter database school charset utf8;
#查看字符集
>show charset;
#查看校对集
> show collation;
表定义
表规范
- 表名小写
- 不能是数字开头
- 注意字符集和存储引擎
- 表名和业务有关
- 选择合适的数据类型
- 每列要有注释
- 每列设置为非空，否则设置默认值（0）
创建表
create table table_name (
列1 属性(数据类型、约束、其他属性) ，
列2 属性，
列3 属性
)
#建表举例
USE db_name;
CREATE TABLE student_info (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '学生学号',
sname VARCHAR(64) NOT NULL COMMENT '学生姓名',
gender ENUM('m','f','n') NOT NULL DEFAULT 'n' COMMENT '学生性别',
phone CHAR(11) UNIQUE KEY NOT NULL COMMENT '手机号',
age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '学生年龄',
time TIMESTAMP NOT NULL DEFAULT NOW() COMMENT '入学时间'
)ENGINE=INNODB CHARSET=utf8mb4;
删除表（禁止使用）
drop table t1;
修改表属性
#在student表的最后加上QQ列
ALTER TABLE school.student ADD qq VARCHAR ( 64 ) UNIQUE KEY NOT NULL COMMENT 'QQ';
DESC stutdent;
#在sname后加微信列
ALTER TABLE school.student ADD Wechat VARCHAR ( 64 ) UNIQUE KEY NOT NULL COMMENT '微信' AFTER sname;
DESC student;
#插入在第一列
ALTER TABLE school.student ADD num INT FIRST;
DESC student;
#修改sname数据类型
ALTER TABLE student MODIFY sname VARCHAR ( 32 );
DESC student;
#修改sname列名改为sn,数据类型改为varchar(50)
ALTER TABLE student CHANGE sname sn VARCHAR ( 50 );
DESC student;
删除列
#删除num列
ALTER TABLE student DROP num;
表属性查询
#查看表内容
show table_name;
#
desc table_name;
#查看表所有内容的前4行
select * from table_name where id<5;
#复制source表结构，并创建新的空表
create table source_table_name like new_table_name;
DCL语句
#授予权限
grant
#回收权限
revoke
DML语句
对表中数据进行增、删、改。
INSERT语句
#在学生表插入一行数据
DESC student;
INSERT INTO student(sn,gender,phone,age)
VALUES('zs','m', '110' ,18);
SELECT * FROM student;
#省事的插入方法
INSERT INTO student
VALUES(2,'ls','f','190',18,NOW());
SELECT * FROM student;
#一次性录入多行数据
INSERT INTO student(sn,gender,phone,age)
VALUES('w5','f','120',19),('m6','m','119',20),('m66','f','1190',27);
SELECT * FROM student;
INSERT INTO student(sn,phone,age)
VALUES('w55','1200',17);
UPDATE语句
#更改第7行'sn'列为'zhao4'
UPDATE student SET sn='zhao4' WHERE id=7;
注意：update语句必须要加where。
DELETE语句 (删除)
#删除第7行信息
delete from student where id=7;
伪删除：用update来替代delete，最终保证业务中查不到（select）即可。
#添加状态列
alter table student add state tinyint not null default 1;
#使用update替代delete
update student set state=0 where id=6;
select * from xuesheng;
#业务查询时,加入状态判断
select * from student where state=1;
DQL语句
SELECT语句
- MySQL独有参数
#查看配置文件信息
select @@basedir;
select @@socket;
- MySQL内置函数
#查看系统当前时间
select now();
#查看MySQL当前登陆用户
select user();
#查看当前所在数据库
select database();
- from
#查看city表中某一列信息
SELECT NAME,population FROM world.city;
#查看student表的所有信息
select * from student;
- where
#查询中国所有城市
select * from city where countrycode='CHN';
#查询以'US'开头的列（%不可以在最前方）
select * from city where countrycode like 'US%';
#查看中国和美国的城市信息
select * from city where countrycode='CHN' or countrycode='USA';
#同时模糊查找
select * from city where countrycode like 'US%' or countrycode like 'CH%';
group by + 常用聚合函数
作用：根据by后面的条件进行分组，方便统计，by后面跟单列或者多列。
- 常用聚合函数
max() #最大值
min() #最小值
avg() #平均值
sum() #总和
count() #个数
- GROUP BY + 聚合函数口诀与实例
遇到统计想函数
形容词前 GROUP BY
函数中央是名词
列名select后添加
#统计世界上每个国家的总人口数.
USE world
SELECT countrycode ,SUM(population) FROM city GROUP BY countrycode;
#统计中国各个省的总人口数量
SELECT district,SUM(Population) FROM city WHERE countrycode='chn' GROUP BY district;
#统计世界上每个国家的城市数量
SELECT countrycode,COUNT(id) FROM city GROUP BY countrycode;
- having
where|group|having
#统计中国每个省的总人口数，将总人口数小于100w
SELECT district,SUM(Population)FROM cityWHERE countrycode='chn' GROUP BY districtHAVING SUM(Population) < 1000000 ;
order by + limit
#查看中国所有城市，并按人口数进行排序（从大到小）
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;
#取第3名到第7名
select district as 省份,sum(population) as 总人口
from city
where countrycode='chn'
group by district
order by 总人口 DESC
limit 2,5;
select district as 省份,sum(population) as 总人口
from city
where countrycode='chn'
group by district
order by 总人口 DESC
limit 5 offset 2;
- distinct （去重）
SELECT countrycode FROM city ;
SELECT DISTINCT(countrycode) FROM city ;
join （多表连接查询）
#查询一下世界上人口数量小于100人的城市名和国家名
SELECT b.name ,a.name ,a.populationFROM city AS aJOIN country AS bON b.code=a.countrycodeWHERE a.Population<100
#查询城市shenyang，城市人口，所在国家名（name）及国土面积（SurfaceArea）
SELECT a.name,a.population,b.name ,b.SurfaceAreaFROM city AS a JOIN country AS bON a.countrycode=b.codeWHERE a.name='shenyang';
#多表连接口诀
数据来自多张表，优先想到多表连接join ON
关联表写join两端
on条件写两表的关联列
所有查询条件select后，注意表名和别名
where过滤条件写最后
union（all）
主要用来优化 in 和 or 语句
SELECT * FROM city WHERE countrycode='CHN'
UNION ALL
SELECT * FROM city WHERE countrycode='USA'
子查询
#查询人口少于100人的城市所在的国家名字
select name,population,countrycode from city where population <100；
A，B，CAAA BBB CCCselect name from country where code in (select countrycode from city where population <100;)
#优化思路
1.改写为join
2.利用临时表
information_schema tables
DESC information_schema.TABLES
TABLE_SCHEMA ---->库名
TABLE_NAME ---->表名
ENGINE ---->引擎
TABLE_ROWS ---->表的行数
AVG_ROW_LENGTH ---->表中行的平均行（字节）
INDEX_LENGTH ---->索引的占用空间大小（字节）
#查询整个数据库中所有库和所对应的表信息
SELECT table_schema,GROUP_CONCAT(table_name) FROM information_schema.tablesGROUP BY table_schema;
#统计所有库下的表个数
SELECT table_schema,COUNT(table_name)FROM information_schema.TABLESGROUP BY table_schema
#查询所有innodb引擎的表及所在的库
SELECT table_schema,table_name,ENGINE FROM information_schema.`TABLES`WHERE ENGINE='innodb';
#统计world数据库下每张表的磁盘空间占用
SELECT table_name,CONCAT((TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB") AS size_KBFROM information_schema.tables WHERE TABLE_SCHEMA='world';
#统计所有数据库的总的磁盘空间占用
SELECTTABLE_SCHEMA,CONCAT(SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB") AS Total_KBFROM information_schema.tablesGROUP BY table_schema;
mysql -uroot -p123 -e "SELECT TABLE_SCHEMA,CONCAT(SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024,' KB') AS Total_KB FROM information_schema.tables GROUP BY table_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('information_schema','performance_schema','sys')
INTO OUTFILE '/tmp/bak.sh' ;
CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql" )
#107张表，都需要执行以下2条语句
ALTER TABLE world.city DISCARD TABLESPACE;
ALTER TABLE world.city IMPORT TABLESPACE;
SELECT CONCAT("alter table ",table_schema,".",table_name," discard tablespace")
FROM information_schema.tables
WHERE table_schema='world'INTO OUTFILE '/tmp/dis.sql';
show 命令
#查看数据库
show databases;
#查看表
show tables;
#查看某个数据库里面的表
show tables from db_name;
#查看建库语句
show create db_name;
#查看建表语句
show create table db_name.table_name;
#查看用户授权信息
show grants for root@'localhost';
#查看字符集
show charset;
#查看校对集
show collation;
#查看数据库连接情况
show processlist;
#查看表索引
show index from table_name;
#查看所有配置信息
show variables;
#查看数据库状态
show status;
#查看所有存储引擎
show engines;
#查看所有二进制日志
show binary logs;
#查看部分配置信息
SHOW variables LIKE '%lock%';
#查看InnoDB引擎相关的状态信息
show engine innodb status\G
#查看主库状态
show master status
#查看从库状态
show slave status \G
#查看表的列定义信息
desc (show colums from city)
