1、SQL介绍

结构化查询语言5.7 以后符合SQL92严格模式通过sql_mode参数来控制

2、常用SQL分类

DDL：数据定义语言DCL：数据控制语言DML：数据操作语言DQL：数据的查询语言

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

3.1  数据类型

3.1.1 作用

保证数据的准确性和标准性。

3.1.2 种类

数值类型

image

tinyint  ：-128~127int：-2^31~2^31-1说明：手机号是无法存储到int的。一般是使用char类型来存储收集号

字符类型

need-to-insert-img

image

char(11)：定长 的字符串类型,在存储字符串时，最大字符长度11个，立即分配11个字符长度的存储空间，如果存不满，空格填充。varchar(11):变长的字符串类型看，最大字符长度11个。在存储字符串时，自动判断字符长度，按需分配存储空间。enum('bj','tj','sh')：枚举类型，比较适合于将来此列的值是固定范围内的特点，可以使用enum,可以很大程度的优化我们的索引结构。

时间类型

image

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

unique key：唯一键

列值不能重复

unsigned：无符号

针对数字列，非负数。

其他属性:

key:索引

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

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

二进制类型

image

3.2 表属性

3.2.1 列属性

约束(一般建表时添加):**primary key**：主键约束设置为主键的列，此列的值必须非空且唯一，主键在一个表中只能有一个，但是可以有多个列一起构成。**notnull**：非空约束列值不能为空，也是表设计的规范，尽可能将所有的列设置为非空。可以设置默认值为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.2 库定义

4.2.1 创建

4.2.1 创建数据库

create database school;create schema sch;show charset;show collation;CREATE DATABASE test CHARSET utf8;create database xyz charset utf8mb4 collate utf8mb4_bin;建库规范：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 修改

SHOW CREATE DATABASE school;ALTER DATABASE school  CHARSET utf8;注意：修改字符集，修改后的字符集一定是原字符集的严格超集

4.2.4 查询库相关信息（DQL）

show databases；show create database oldboy；

4.3 表定义

4.3.1 创建

create table stu(列1  属性（数据类型、约束、其他属性） ，列2  属性，列3  属性)

4.3.2 建表

USEschool;CREATETABLEstu(idINTNOTNULLPRIMARYKEYAUTO_INCREMENTCOMMENT'学号',snameVARCHAR(255)NOTNULLCOMMENT'姓名',sageTINYINTUNSIGNEDNOTNULLDEFAULT0COMMENT'年龄',sgenderENUM('m','f','n')NOTNULLDEFAULT'n'COMMENT'性别',sfzCHAR(18)NOTNULLUNIQUECOMMENT'身份证',intimeTIMESTAMPNOTNULLDEFAULTNOW()COMMENT'入学时间')ENGINE=INNODBCHARSET=utf8COMMENT'学生表';

建表规范：

1. 表名小写2. 不能是数字开头3. 注意字符集和存储引擎4. 表名和业务有关5. 选择合适的数据类型6. 每个列都要有注释7. 每个列设置为非空，无法保证非空，用0来填充。

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

drop table t1;

4.3.3 修改

在stu表中添加qq列

DESCstu;ALTERTABLEstuADDqqVARCHAR(20)NOTNULLUNIQUECOMMENT'qq号';

在sname后加微信列

ALTERTABLEstuADDwechatVARCHAR(64)NOTNULLUNIQUECOMMENT'微信号'AFTERsname;

在id列前加一个新列num

ALTER TABLE stu ADD num INT NOT NULL COMMENT'数字'FIRST;DESC stu;

把刚才添加的列都删掉(危险)

ALTER TABLE stu DROP num;ALTER TABLE stu DROP qq;ALTER TABLE stu DROP wechat;

修改sname数据类型的属性

ALTER TABLE stu MODIFY snameVARCHAR(128)NOTNULL;

将sgender 改为 sg  数据类型改为 CHAR 类型

ALTERTABLEstuCHANGEsgender sgCHAR(1)NOTNULLDEFAULT'n';DESCstu;

4.3.4 表属性查询（DQL）

use schoolshow tables；desc stu;show create table stu；CREATE TABLE ceshi LIKE stu;

5. DCL应用 ****

grant revoke

6. DML应用

6.1 作用

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

6.2 insert

---最标准的insert语句INSERTINTOstu(id,sname,sage,sg,sfz,intime)VALUES(1,'zs',18,'m','123456',NOW());SELECT*FROMstu;---省事的写法INSERTINTOstu VALUES(2,'ls',18,'m','1234567',NOW());---针对性的录入数据INSERTINTOstu(sname,sfz)VALUES('w5','34445788');---同时录入多行数据INSERTINTOstu(sname,sfz)VALUES('w55','3444578d8'),('m6','1212313'),('aa','123213123123');SELECT*FROMstu;

6.3 update

DESC stu;SELECT * FROM stu;UPDATE stu SET sname='zhao4' WHERE id=2;注意：update语句必须要加where。

6.4 delete（危险！！）

DELETE FROM stu  WHERE id=3;

全表删除:

DELETE FROM stutruncate table stu;区别:delete:DML操作,是逻辑性质删除,逐行进行删除,速度慢.truncate:DDL操作,对与表段中的数据页进行清空,速度快.

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

1.添加状态列ALTER TABLE stu ADD state TINYINT NOTNULLDEFAULT1;SELECT*FROM stu;2.UPDATE 替代 DELETEUPDATE stu SET state=0WHERE id=6;3.业务语句查询SELECT*FROM stu WHERE state=1;

7. DQL应用(select )

7.1 单独使用

-- select @@xxx查看系统参数SELECT @@port;SELECT @@basedir;SELECT @@datadir;SELECT @@socket;SELECT @@server_id;

-- select 函数();

SELECTNOW();SELECTDATABASE();SELECTUSER();SELECTCONCAT("hello world");SELECTCONCAT(USER,"@",HOST)FROMmysql.user;SELECTGROUP_CONCAT(USER,"@",HOST)FROMmysql.user;https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html?tdsourcetag=s_pcqq_aiomsg

7.2 单表子句-from

SELECT 列1,列2 FROM 表SELECT  *  FROM 表

例子:

-- 查询stu中所有的数据(不要对大表进行操作)

SELECT * FROM stu ;

-- 查询stu表中,学生姓名和入学时间

SELECT sname , intime FROM stu;

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

oldguo带大家学单词：

world===>世界city===>城市country===>国家countrylanguage===>国家语言city:城市表DESC city;ID:城市IDNAME:城市名CountryCode:国家代码，比如中国CHN 美国USADistrict:区域Population:人口SHOW CREATE TABLE city;SELECT*FROM city WHERE id<10;

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

7.3 单表子句-where

SELECT col1,col2 FROM TABLE WHERE colN 条件;

7.3.1 where配合等值查询

例子:

-- 查询中国(CHN)所有城市信息

SELECT * FROM city WHERE countrycode='CHN';

-- 查询北京市的信息

SELECT * FROM city WHERE NAME='peking';

-- 查询甘肃省所有城市信息

SELECT * FROM city WHERE district='gansu';

7.3.2 where配合比较操作符(> < >= <= <>)

例子:

-- 查询世界上少于100人的城市

SELECT * FROM city WHERE population<100;

7.3.3 where配合逻辑运算符(and  or )

例子:

-- 中国人口数量大于500w

SELECT * FROM city WHERE countrycode='CHN' AND population>5000000;

-- 中国或美国城市信息

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

7.3.4 where配合模糊查询

例子:

-- 查询省的名字前面带guang开头的

SELECT*FROMcityWHEREdistrictLIKE'guang%';注意:%不能放在前面,因为不走索引.

7.3.5 where配合in语句

-- 中国或美国城市信息

SELECT*FROMcityWHEREcountrycode IN('CHN','USA');

7.3.6 where配合between and

例子:

-- 查询世界上人口数量大于100w小于200w的城市信息

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

7.4 group by + 常用聚合函数

7.4.1 作用

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

7.4.2 常用聚合函数

**max()**：最大值**min()**：最小值**avg()**：平均值**sum()**：总和**count()**：个数group_concat():列转行

7.4.3 例子：

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

USEworldSELECTcountrycode,SUM(population)FROMcityGROUPBYcountrycode;

例子2： 统计中国各个省的总人口数量(练习)

SELECT district,SUM(Population) FROM city  WHERE countrycode='chn' GROUP BY district;

例子3：统计世界上每个国家的城市数量(练习)

SELECT countrycode,COUNT(id)FROM city GROUP BY countrycode;

7.5 having

where|group|having

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

SELECTdistrict,SUM(Population)FROMcityWHEREcountrycode='chn'GROUPBYdistrictHAVINGSUM(Population)<1000000;

7.6 order by + limit

7.6.1 作用

实现先排序，by后添加条件列

7.6.2 应用案例

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

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

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

SELECTdistrictAS省,SUM(Population)AS总人口FROMcityWHEREcountrycode='chn'GROUPBYdistrictORDERBY总人口DESC;

统计中国,每个省的总人口,找出总人口大于500w的,并按总人口从大到小排序,只显示前三名

SELECTdistrict,SUM(population)FROMcityWHEREcountrycode='CHN'GROUPBYdistrictHAVINGSUM(population)>5000000ORDERBYSUM(population)DESCLIMIT3;LIMITN,M--->跳过N,显示一共M行LIMIT5,5SELECTdistrict,SUM(population)FROMcityWHEREcountrycode='CHN'GROUPBYdistrictHAVINGSUM(population)>5000000ORDERBYSUM(population)DESCLIMIT5,5;

7.7 distinct：去重复

SELECT countrycode FROM city;SELECTDISTINCT(countrycode)FROM city;

7.8 联合查询- union all

--中国或美国城市信息SELECT*FROMcityWHEREcountrycode IN('CHN','USA');SELECT*FROMcityWHEREcountrycode='CHN'UNIONALLSELECT*FROMcityWHEREcountrycode='USA'说明:一般情况下,我们会将 IN 或者 OR 语句 改写成UNIONALL,来提高性能UNION    去重复UNIONALL 不去重复

7.9 join 多表连接查询

7.9.0 案例准备

按需求创建一下表结构:

useschoolstudent ：学生表sno：    学号sname：学生姓名sage： 学生年龄ssex： 学生性别teacher ：教师表tno：    教师编号tname：教师名字course ：课程表cno：  课程编号cname：课程名字tno：  教师编号score  ：成绩表sno：  学号cno：  课程编号score：成绩--项目构建drop database school;CREATEDATABASEschoolCHARSETutf8;USEschoolCREATETABLEstudent(snoINTNOTNULLPRIMARYKEYAUTO_INCREMENTCOMMENT'学号',snameVARCHAR(20)NOTNULLCOMMENT'姓名',sageTINYINTUNSIGNEDNOTNULLCOMMENT'年龄',ssexENUM('f','m')NOTNULLDEFAULT'm'COMMENT'性别')ENGINE=INNODBCHARSET=utf8;CREATETABLEcourse(cnoINTNOTNULLPRIMARYKEYCOMMENT'课程编号',cnameVARCHAR(20)NOTNULLCOMMENT'课程名字',tnoINTNOTNULLCOMMENT'教师编号')ENGINE=INNODBCHARSETutf8;CREATETABLEsc(snoINTNOTNULLCOMMENT'学号',cnoINTNOTNULLCOMMENT'课程编号',scoreINTNOTNULLDEFAULT0COMMENT'成绩')ENGINE=INNODBCHARSET=utf8;CREATETABLEteacher(tnoINTNOTNULLPRIMARYKEYCOMMENT'教师编号',tnameVARCHAR(20)NOTNULLCOMMENT'教师名字')ENGINE=INNODBCHARSETutf8;INSERTINTOstudent(sno,sname,sage,ssex)VALUES(1,'zhang3',18,'m');INSERTINTOstudent(sno,sname,sage,ssex)VALUES(2,'zhang4',18,'m'),(3,'li4',18,'m'),(4,'wang5',19,'f');INSERTINTOstudentVALUES(5,'zh4',18,'m'),(6,'zhao4',18,'m'),(7,'ma6',19,'f');INSERTINTOstudent(sname,sage,ssex)VALUES('oldboy',20,'m'),('oldgirl',20,'f'),('oldp',25,'m');INSERTINTOteacher(tno,tname)VALUES(101,'oldboy'),(102,'hesw'),(103,'oldguo');DESCcourse;INSERTINTOcourse(cno,cname,tno)VALUES(1001,'linux',101),(1002,'python',102),(1003,'mysql',103);DESCsc;INSERTINTOsc(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*FROMstudent;SELECT*FROMteacher;SELECT*FROMcourse;SELECT*FROMsc;

7.9.1 语法

need-to-insert-img

image

查询张三的家庭住址

SELECT A.name,B.address FROMA JOIN  BON A.id=B.idWHERE A.name='zhangsan'

7.9.2 例子：

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

SELECTb.name,a.name,a.populationFROMcityASaJOINcountryASbONb.code=a.countrycodeWHEREa.Population<100

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

SELECTa.name,a.population,b.name,b.SurfaceAreaFROMcityASaJOINcountryASbONa.countrycode=b.codeWHEREa.name='shenyang';

7.9.3 别名

列别名,表别名SELECTa.NameASan,b.nameASbn,b.SurfaceAreaASbs,a.PopulationASbpFROMcityASaJOINcountryASbONa.CountryCode=b.CodeWHEREa.name='shenyang';

7.9.4 多表SQL练习题

统计zhang3,学习了几门课

SELECTst.sname,COUNT(sc.cno)FROMstudentASstJOINscONst.sno=sc.snoWHEREst.sname='zhang3'

查询zhang3,学习的课程名称有哪些?

SELECTst.sname,GROUP_CONCAT(co.cname)FROMstudentASstJOINscONst.sno=sc.snoJOINcourseAScoONsc.cno=co.cnoWHEREst.sname='zhang3'

查询oldguo老师教的学生名.

SELECTte.tname,GROUP_CONCAT(st.sname)FROMstudentASstJOINscONst.sno=sc.snoJOINcourseAScoONsc.cno=co.cnoJOINteacherASteONco.tno=te.tnoWHEREte.tname='oldguo';

查询oldguo所教课程的平均分数

SELECTte.tname,AVG(sc.score)FROMteacherASteJOINcourseAScoONte.tno=co.tnoJOINscONco.cno=sc.cnoWHEREte.tname='oldguo'

4.1 每位老师所教课程的平均分,并按平均分排序

SELECT te.tname,AVG(sc.score)FROM teacher AS teJOIN course AS coON te.tno=co.tnoJOIN scON co.cno=sc.cnoGROUP BY te.tnameORDER BYAVG(sc.score)DESC;

查询oldguo所教的不及格的学生姓名

SELECTte.tname,st.sname,sc.scoreFROMteacherASteJOINcourseAScoONte.tno=co.tnoJOINscONco.cno=sc.cnoJOINstudentASstONsc.sno=st.snoWHEREte.tname='oldguo'ANDsc.score<60;

5.1 查询所有老师所教学生不及格的信息

SELECTte.tname,st.sname,sc.scoreFROMteacherASteJOINcourseAScoONte.tno=co.tnoJOINscONco.cno=sc.cnoJOINstudentASstONsc.sno=st.snoWHEREsc.score<60;

7.9.5 综合练习

1. 查询平均成绩大于60分的同学的学号和平均成绩；2. 查询所有同学的学号、姓名、选课数、总成绩；3. 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分 4. 统计各位老师,所教课程的及格率5. 查询每门课程被选修的学生数6. 查询出只选修了一门课程的全部学生的学号和姓名7. 查询选修课程门数超过1门的学生信息8. 统计每门课程:优秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的学生列表9. 查询平均成绩大于85的所有学生的学号、姓名和平均成绩

8.information_schema.tables视图

DESC information_schema.TABLESTABLE_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数据库下每张表的磁盘空间占用

SELECTtable_name,CONCAT((TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB")ASsize_KBFROMinformation_schema.tablesWHERETABLE_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.sqlSELECTCONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql")FROMinformation_schema.tablesWHEREtable_schemaNOTIN('information_schema','performance_schema','sys')INTOOUTFILE'/tmp/bak.sh';CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql")

107张表，都需要执行以下2条语句

ALTERTABLEworld.cityDISCARDTABLESPACE;ALTERTABLEworld.cityIMPORTTABLESPACE;SELECTCONCAT("alter table ",table_schema,".",table_name," discard tablespace")FROMinformation_schema.tablesWHEREtable_schema='world'INTOOUTFILE'/tmp/dis.sql';

9. show 命令

show  databases;#查看所有数据库show tables;#查看当前库的所有表SHOWTABLESFROM                        #查看某个指定库下的表show create database world                #查看建库语句show create table world.city                #查看建表语句show  grantsforroot@'localhost'#查看用户的权限信息show  charset；                                  #查看字符集show collation                                      #查看校对规则show processlist;#查看数据库连接情况show indexfrom#表的索引情况show status                                        #数据库状态查看SHOWSTATUSLIKE'%lock%';#模糊查询数据库某些状态SHOWVARIABLES                            #查看所有配置信息SHOWvariables LIKE'%lock%';#查看部分配置信息show engines                                      #查看支持的所有的存储引擎show engine innodb status\G              #查看InnoDB引擎相关的状态信息show binary logs                                    #列举所有的二进制日志show master status                                #查看数据库的日志位置信息show binlog evnetsin#查看二进制日志事件show slave status \G                            #查看从库状态SHOWRELAYLOGEVENTS              #查看从库relaylog事件信息desc(show columsfromcity)#查看表的列定义信息http://dev.mysql.com/doc/refman/5.7/en/show.html

作者：MySQL_oldguo

链接：https://www.jianshu.com/p/08c4b78402ff

来源：简书

著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。