1、SQL介绍 ***

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

2、SQL分类 ***

DDL：数据定义语言     create  创建     drop 删除      alter 修改

DCL：数据控制语言     grant    （给与权限）      revoke（收回权限）

DML：数据操作语言   （表 增删改）  

DQL：数据查询语言   select  show  

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

3.1  数据类型

3.1.1 作用 ：保证数据的准确性和标准性。

3.1.2 种类

数值类型

tinyint  ： -128~127   

int       ：-2^31~2^31-1

说明：手机号是无法存储到int的。一般是使用char类型来存储收集号

字符类型

char(11) ：定长的字符串类型,在存储字符串时，最大字符长度11个，立即分配11个字符长度的存储空间，如果存不满，空格填充。

varchar(11):变长的字符串类型看，最大字符长度11个。在存储字符串时，自动判断字符长度，按需分配存储空间。

enum('bj','tj','sh')：枚举类型，比较适合于将来此列的值是固定范围内的特点，可以使用enum

可以很大程度的优化我们的索引结构。

时间类型

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会受到时区的影响

二进制类型

3.2 表属性

3.2.1 列属性

primary key ：主键约束

设置为主键的列，此列的值必须非空且唯一，主键在一个表中只能有一个，但是可以有多个列一起构成。

not null        ：非空约束

列值不能为空，也是表设计的规范，尽可能将所有的列设置为非空。可以设置默认值为0

unique key   ：唯一键      列值不能重复

unsigned       ：无符号     针对数字列，非负数。

其他属性

key                   :索引

      可以在某列上建立索引，来优化查询

default              :默认值

      列中，没有录入值时，会自动使用default的值填充

auto_increment :自增长

       针对数字列，顺序的自动填充数据（默认是从1开始，将来可以设定起始点和偏移量）

comment           : 注释

3.2.2 表的属性

存储引擎：InnoDB（默认的）

字符集和排序规则

utf8       

utf8mb4

3.3 字符集和校对规则

3.3.1 字符集

utf8       

utf8mb4

3.3.2 校对规则（排序规则）

大小写是否敏感

4、DDL应用 **** (数据定义语言)

4.1 库定义

4.1.1 创建

create database / create schema

mysql> create database xuexiao; 

建库规范：

1.库名不能有大写字母   *****

2.建库要加字符集          *****

3.库名不能有数字开头

4. 库名要和业务相关

建库标准语句

mysql> create database db charset utf8mb4;

mysql> show create database xuexiao;

4.2.2 删除(生产中禁止使用)

mysql> drop database oldboy;

4.2.3 修改

mysql> show create database xuexiao;

mysql> alter database xuexiao charset utf8mb4;

mysql> show create database xuexiao;

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

mysql> show charset;

mysql> show collation;

4.2.4 查询库相关信息（DQL）

show databases；

show create database oldboy；

4.3 表定义

4.3.1 创建

create table  xuesheng (

列1  属性（数据类型、约束、其他属性） ，

列2  属性，

列3  属性

)

建表举例：

USE xuexiao

SELECT DATABASE();

CREATE TABLE xuesheng (

id INT PRIMARY KEY NOT NULL  AUTO_INCREMENT COMMENT '学生学号',

sname VARCHAR(64) NOT NULL COMMENT '学生姓名',

xingbie ENUM('m','f','n') NOT NULL DEFAULT 'n' COMMENT '学生性别',

shouji  CHAR(11) UNIQUE KEY NOT NULL COMMENT '手机号',

age  TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '学生年龄',

ruxue TIMESTAMP NOT NULL DEFAULT NOW() COMMENT '入学时间'

)ENGINE=INNODB CHARSET=utf8mb4;

建表规范：

1. 表名小写

2. 不能是数字开头

3. 注意字符集和存储引擎

4. 表名和业务有关

5. 选择合适的数据类型

6. 每个列都要有注释

7. 每个列设置为非空，无法保证非空，用0来填充。

4.3.2 删除(生产中禁用次命令)

drop table t1;

4.3.3 修改

-- 1. 在xuesheng表中添加qq列（默认添加到最后）

alter table  xuesheng add qq varchar(64)  unique key  not null comment 'qq号';

DESC xuesheng;

-- 2. 在sname后加微信列

alter table  xuexiao.xuesheng add weixin varchar(64)  unique key not null  comment '微信' after  sname;

DESC xuesheng;

-- 3. 在id列前加一个新列num

alter  table  xuesheng add num int first

-- 4. 把刚才添加的列都删掉

ALTER TABLE xuesheng DROP num;

ALTER TABLE xuesheng DROP weixin;

ALTER TABLE xuesheng DROP qq;

-- 5. 修改sname数据类型的属性

alter  table  xuesheng MODIFY sname varchar(32) not null comment 'aa';

DESC xuesheng;

-- 6. 将sname 改为sn 数据类型改为 varchar(64)

alter table xuesheng CHANGE sname sn varcha(64);

4.3.4 表属性查询（DQL）

use xuexiao     进入表

show tables；    查询库结构

desc xuesheng;    查询表结构

show create table xuesheng；

select  *  from xuesheng  where  id <5

create table ceshi like xuesheng; (复制结构一样的空表)

5、DCL应用 ****

grant    （给与）

revoke（收回）

6、DML应用 ***

6.1 作用

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

6.2 insert 

-- 1. 在学生表插入一行数据     （指定插入数据的行）

DESC xuesheng;

INSERT INTO xuesheng(sn,xingbie,shouji,age)       

      VALUES('zs','m',  '110'  ,18);

SELECT * FROM xuesheng;

-- 省事的插入方法（默认每一列内容都要按照该列的规范进行添加）

INSERT INTO xuesheng

VALUES(2,'ls','f','190',18,NOW());

SELECT * FROM xuesheng;

-- 2. 一次性录入多行数据

INSERT INTO xuesheng(sn,xingbie,shouji,age)

VALUES

('w5','f','120',19),

('m6','m','119',20),

('m66','f','1190',27);

SELECT * FROM xuesheng;

INSERT INTO xuesheng(sn,shouji,age)

VALUES

('w55','1200',17);

6.3 update 

UPDATE xuesheng SET sn='zhao4' WHERE id=7;        修改id=7行 sn 的数据

注意：update语句必须要加where。

6.4 delete（危险！！）

delete from xuesheng where id=7;          删除id=7的行

伪删除：用update来替代delete，最终保证业务中查不到（select）即可

1. 添加状态列

Master [xuexiao]>alter table xuesheng add state tinyint not null default 1;

2. 使用update替代delete

Master [xuexiao]>update xuesheng set state=0 where id=6;

Master [xuexiao]>select * from xuesheng;

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

Master [xuexiao]>select * from xuesheng where state=1;

7、DQL应用(select ) *****

7.0 select 单独使用的情况

查看mysql的参数设定情况：

Master [xuexiao]>select @@basedir;

Master [xuexiao]>select @@innodb_flush_log_at_trx_commit;

调用mysql的内置函数：

Master [world]>select database();

Master [(none)]>select user();

Master [(none)]>select now();

Master [xuexiao]>select concat("lisi shi ge da hun dan ");

7.1  from 子句

语法：

select  列1,列2  from 表

select * from 表；

例子：

7.1.1 查询表中所有的数据（生产中避免使用）

SELECT * FROM city;

7.1.2 查询某些列的数据

SELECT NAME,population FROM world.city;

7.2 where

-- 1. 查询中国所有的城市

DESC city;

SELECT * FROM city WHERE countrycode='CHN';

-- 2. 查询CH开头国家代号的城市信息

SELECT * FROM city WHERE countrycode LIKE 'CH%';            模糊查找

注意:%不能加在前面，例如:'%CH%'

-- 3. 查看中国和美国的城市信息

SELECT * FROM city WHERE countrycode='CHN' OR countrycode='USA';

或者：

SELECT * FROM city WHERE countrycode='CHN'

UNION ALL

SELECT * FROM city WHERE countrycode='USA'

注意:  一般我们会将 or或者in 语句改写成union union all 语句（优化方法）

查询人口数100w到200w之前的城市信息

SELECT * FROM city WHERE population>1000000 AND population <2000000

或者：

SELECT * FROM city WHERE population BETWEEN 1000000 AND 2000000;

7.3 group by +常用聚合函数

7.3.1 作用

根据 by后面的条件进行分组，方便统计，by后面跟一个列或多个列

7.3.2 常用聚合函数

max()：最大值

min()：最小值

avg()：平均值

sum()：总和

count()：个数

7.3.3 GROUP BY + 聚合函数公式（类似于组）

1.遇到统计想函数

2.形容词前 GROUP BY

3.函数中央是名词

4.列名select后添加

7.3.4 例子：

例子1：统计世界上每个国家的总人口数.

USE world

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(id)  FROM city GROUP BY countrycode;

7.4 having （比较大小）

where|group|having

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

SELECT district,SUM(Population)

FROM city

WHERE countrycode='chn'

GROUP BY district

HAVING SUM(Population) < 1000000 ;

7.5 order by + limit（实现先排序，by后添加条件列）

7.5.1 应用案例

1. 查看中国所有的城市，并按人口数进行排序(从大到小)

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

 2. 统计中国各个省的总人口数量，按照总人口从大到小排序

SELECT district AS 省 ,SUM(Population) AS 总人口

FROM city

WHERE countrycode='chn'

GROUP BY district

ORDER BY 总人口 DESC ;

-- 3. 统计中国各个省的总人口数量，按照总人口从大到小排序，只取6-10

SELECT district AS 省 ,SUM(Population) AS 总人口

FROM city

WHERE countrycode='chn'

GROUP BY district

ORDER BY 总人口 DESC

LIMIT 5 , 5 ;

7.6 distinct：去重复

SELECT countrycode FROM city  ;

SELECT disyinct(countrycode) FROM city  ;

==============

7.7 join 多表连接查询

7.7.1 语法

查询张三的家庭住址

SELECT A.name,B.address FROM

A JOIN  B

ON A.id=B.id

WHERE A.name='zhangsan'

7.7.2 例子：

-- 1. 查询一下世界上人口数量小于100人的城市名和国家名

SELECT b.name ,a.name ,a.population

FROM   city  AS a

JOIN   country AS b

ON     b.code=a.countrycode

WHERE  a.Population<100

-- 2. 查询城市shenyang，城市人口，所在国家名（name）及国土面积（SurfaceArea）

SELECT a.name,a.population,b.name ,b.SurfaceArea

FROM city  AS a JOIN country AS b

ON a.countrycode=b.code

WHERE a.name='shenyang';

7.7.3 多表连接公式

1.数据来自多张表，优先想到多表连接join ON

2.关联表写join两端

3.on条件写两表的关联列

4.所有查询条件select后，注意表名和别名

5.where过滤条件写最后

 多张表相互关联例子

SELECT te.tname ,GROUP_CONCAT(st.sname)或者  st.name

FROM student AS st

JOIN 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';

7.8 union[all]

主要用来优化  in   和  or 语句

SELECT * FROM city WHERE countrycode='CHN'

UNION ALL

SELECT * FROM city WHERE countrycode='USA'

7.9  子查询 **

-- 查询人口少于100人的城市所在的国家名字

select name，population，countrycode   from city where population <100；

A，B，C

AAA  BBB  CCC

select name from country where code in (select  countrycode   from city where population <100;)

优化思路：

1.改写为join

2.利用临时表

8、information_schema.tables视图

mysql 里自带的整理

DESC information_schema.TABLES

TABLE_SCHEMA      ---->库名            *****

TABLE_NAME          ---->表名             *****

ENGINE                     ---->引擎             *****

TABLE_ROWS          ---->表的行数     *****

AVG_ROW_LENGTH   ---->表中行的平均行（字节）         *****

INDEX_LENGTH     ---->索引的占用空间大小（字节）       *****

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

SELECT table_schema,GROUP_CONCAT(table_name)

FROM  information_schema.tables

GROUP BY table_schema;

-- 2. 统计所有库下的表个数

SELECT table_schema,COUNT(table_name)

FROM information_schema.TABLES

GROUP BY table_schema

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

SELECT table_schema,table_name,ENGINE FROM information_schema.`TABLES`

WHERE ENGINE='innodb';

-- 4. 统计world数据库下每张表的磁盘空间占用

SELECT table_name,CONCAT((TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB")  AS size_KB

FROM information_schema.tables WHERE TABLE_SCHEMA='world';

-- 5. 统计所有数据库的总的磁盘空间占用

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;

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;"

-- 6. 生成整个数据库下的所有表的单独备份语句

模板语句：

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" )

-- 7. 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';

9、show 命令 ***

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                             #查看从库状态

desc  (show colums from city)               #查看表的列定义信息

http://dev.mysql.com/doc/refman/5.7/en/show.html