MySQL的SQL基础应用

MySQL的SQL基础应用

结构化的查询语言
关系型数据库通用的命令
遵循SQL92的标准(SQL_MODE)

sql常用种类

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

数据库的逻辑结构

库
	库名字
	库属性:字符集,排序规则

表
	表名
	表属性:存储引擎类型,字符集,排序规则
	列名
	列属性:数据类型,约束,其他属性
	数据行

字符集

相当于MySQL的密码本(编码表)

mysql默认的字符集是latin1

常见的字符集:utf8、utf8mb4、LATIN1、GBK

#查询字符集
mysql> show charset;

注意:修改字符集一定要从小往大改，比如utf8-->utf8mb4

排序规则

又称为：校对规则

#查询排序规则
mysql> show collation;

utf8mb4_general_ci 			大小写不敏感
utf8mb4_bin  				大小写敏感

数据类型

数字：整数、浮点数、定点数、BIT

类	类型	说明
整数	TINYINT	极小整数数据类型（0-255）
整数	SMALLINT	较小整数数据类型（-2^15 到2^15-1）
整数	MEDIUMINT	中型整数数据类型
整数	INT	常规（平均）大小的整数数据类型（-2^31 到2^31-1）
整数	BIGINT	较大整数数据类型（-263到263-1）
浮点数	FLOAT	小型单精度（四个字节）浮点数
浮点数	DOUBLE	常规双精度（八个字节）浮点数
定点数	DECIMAL	包含整数部分、小数部分或同时包括二者的精确值数值
BIT	BIT	位字段值

字符串：

类	类型	说明
文本	CHAR	固定长度字符串，最多为255 个字符
文本	VARCHAR	可变长度字符串，最多为65,535 个字符
文本	TINYTEXT	可变长度字符串，最多为255 个字符
文本	TEXT	可变长度字符串，最多为65,535 个字符
文本	MEDIUMTEXT	可变长度字符串，最多为16,777,215 个字符
文本	LONGTEXT	可变长度字符串，最多为4,294,967,295 个字符
整数	ENUM	由一组固定的合法值组成的枚举
整数	SET	由一组固定的合法值组成的集

时间

类型	格式	示例
DATE	YYYY-MM-DD	2019-09-03
TIME	hh:mm:ss[.uuuuuu]	12:59:02.123456
DATETIME	YYYY-MM-DD hh:mm:ss[.uuuuuu]	2019-09-03 12:59:02.123
TIMESTAMP	YYYY-MM-DD hh:mm:ss[.uuuuuu]	2019-09-03 12:59:02.12
YEAR	YYYY	2019

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。

二进制

类	类型	说明
二进制	BINARY	类似于 CHAR（固定长度）类型，但存储的是二进制字节字符串，而不是非二进制字符串
二进制	VARBINARY	类似于 VARCHAR（可变长度）类型，但存储的是二进制字节字符串，而不是非二进制字符串
BLOB	TINYBLOB	最大长度为255 个字节的 BLOB 列
BLOB	BLOB	最大长度为65,535 个字节的 BLOB 列
BLOB	MEDIUDMBLOB	最大长度为16,777,215 个字节的 BLOB 列
BLOB	LONGBLOB	最大长度为4,294,967,295 个字节的 BLOB 列

DDL的应用

#DDL语句库的定义
库 ：名字、特性
#关于库定义规范
1.库名使用小写字符
2.库名不能以数字开头
3.不能是数据库内部的关键字
4.必须设置字符集.
#创建库
mysql> create database opesn charset utf8mb4 collate utf8mb4_bin;
#删除库
mysql> drop database opesn;
#修改库
mysql> alter database opesn charset utf8mb4;
#查询库信息
mysql> show create database opesn;
mysql> show databases;

#DDL语句库的定义
表：表名字、列属性、表属性
#列属性
PRIMARY KEY : 主键约束,表中只能有一个,非空且唯一.
NOT NULL    : 非空约束,不允许空值
UNIQUE KEY  : 唯一键约束,不允许重复值
DEFAULT     : 一般配合 NOT NULL 一起使用，默认值
UNSIGNED    : 无符号,一般是配合数字列,非负数
COMMENT     : 注释
AUTO_INCREMENT : 自增长的列

建表规范
1. 表名小写字母,不能数字开头,
2. 不能是保留字符,使用和业务有关的表名
3. 选择合适的数据类型及长度
4. 每个列设置 NOT NULL + DEFAULT .对于数据0填充,对于字符使用有效字符串填充
5. 每个列设置注释
6. 表必须设置存储引擎和字符集
7. 主键列尽量是无关列数字列,最好是自增长
8. enum类型不要保存数字,只能是字符串类型

#建表
mysql> create table stu (
     id INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT "编号",
     sname VARCHAR(255) NOT NULL COMMENT "姓名",
     age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT "年龄",
     gender ENUM('m','f','n') NOT NULL DEFAULT 'n' COMMENT "性别",
     intime DATETIME NOT NULL DEFAULT NOW() COMMENT "时间"
     )ENGINE INNODB CHARSET utf8mb4;

#查询建表信息
mysql> show tables;
mysql> show create tables stu;
mysql> desc stu;

#创建一个表结构一样的表
mysql> create table test like stu;
mysql> create table user select * from mysql.user; #可以连数据以前创建但没有索引

#删表
mysql> drop table test;

#修改表
#在stu表中加qq列
mysql> ALTER TABLE stu ADD qq VARCHAR(20) NOT NULL COMMENT 'QQ';
#在sname后加微信列
mysql> alter table stu add wecha VARCHAR(64) NOT NULL UNIQUE COMMENT '微信' AFTER sname;
#在id列前加一个新列
mysql> alter table stu add num INT NOT NULL UNIQUE COMMENT '身份证' FIRST;
#把刚才添加的列都删除
mysql> alter table stu drop num;
mysql> alter table stu drop wecha;
mysql> alter table stu drop qq;
#修改sname数据类型的属性
mysql> alter table stu modify sname VARCHAR(64) NOT NULL COMMENT '姓名';
#将gender改为sex,数据类型改为char类型
mysql> alter table stu change gender sex CHAR(1) NOT NULL COMMENT '性别';

#清空表(危险)
mysql> truncate table stu;

DCL的应用

grant 	#授权
revoke  #回收权限

DML的应用

#insert(插入)
#最偷懒
mysql> insert stu VALUES(1,'china','18','m',NOW());
mysql> select * from stu;
+----+-------+-----+-----+---------------------+
| id | sname | age | sex | intime              |
+----+-------+-----+-----+---------------------+
|  1 | china |  18 | m   | 2019-09-04 10:29:37 |
+----+-------+-----+-----+---------------------+
1 row in set (0.00 sec)
mysql> 

#最规范
mysql> insert INTO stu(id,sname,age,sex,intime)
    -> VALUES (2,'ls',19,'f',NOW());

#针对性得到录入数据
mysql> insert into stu(sname,age,sex)
    -> VALUES ('w5',11,'m');

#一次性录入多行
mysql> insert into stu(sname,age,sex)
    -> VALUES
    -> ('aa',20,'m'),
    -> ('bb',21,'m'),
    -> ('cc',22,'m');

#update(一定要加where条件)
mysql> update stu set sname='bbb' where id=6;

#delete(一定要加where条件)
mysql> delete from stu where id=1;

#生产中屏蔽delete功能
#使用update替代delete 
mysql> alter table stu add is_del TINYINT DEFAULT 0;
mysql> update stu set is_del=1 where id=2
mysql> select * from stu where is_del=0;

DQL的应用

select语句的应用

#select单独使用的情况
mysql> select @@port;
+--------+
| @@port |
+--------+
|   3306 |
+--------+
1 row in set (0.00 sec)

mysql> select @@basedir;
+----------------------------+
| @@basedir                  |
+----------------------------+
| /application/mysql-5.7.26/ |
+----------------------------+
1 row in set (0.00 sec)

mysql> select @@innodb_flush_log_at_trx_commit;
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
|                                1 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select database();
+------------+
| database() |
+------------+
| opesn      |
+------------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-09-04 20:07:45 |
+---------------------+
1 row in set (0.00 sec)

mysql> 

#select的通用语法
select		#列
from		#表
where		#条件
group by	#条件
having		#条件
order by	#条件
limit		#条件

练习实例:https://downloads.mysql.com/docs/world.sql.zip

mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)

#city           	 城市表
#country         	 国家表  
#countrylanguage  	 国家的语言

mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> 

#ID  		  	城市序号(1-...)
#name		 	城市名字
#countrycode     国家代码,例如:CHN,USA
#district        区域: 中国 省  美国 洲
#population      人口数

select配合from子句

select 列,列,列 from 表

#查询city表中所有的信息
mysql> use world;
mysql> select  id,name,countrycode,district,population from city;
mysql> select * from city;
mysql> select name,population from city;

SELECT 配合 WHERE 子句

select 列,列,列 from 表 where 过滤条件
#where等值条件查询
#查询中国所有的城市名和人口数
mysql> select name,population from city where countrycode='CHN';

#where配合不等值查询(> < >= <=)
#查询小于100人的城市名和人口数
mysql> select name,population from city where population<100;

#where配合逻辑连接符(and or)
#查询中国人口数量大于800w的城市名和人口
mysql> select name,population from city where countrycode='CHN' and population>8000000;
#查询中国或美国的城市名和人口数
mysql> select name,population from city where countrycode='CHN' or countrycode='USA';
#查询人口数量在500w到600w之间的城市名和人口数
mysql> select name,population from city where population>5000000 and population<6000000;
mysql> select name,population from city where population BETWEEN 5000000 and 6000000;

where 配合 like 子句

#模糊查询
#查询一下contrycode中带有CH开头,城市信息
mysql> select * from city where countrycode like 'CH%';

where 配合 in 子句

#查询中国或美国的城市信息
mysql> select name,population from city where countrycode in ('CHN','USA');

select配合group by + 聚合函数应用

#常用聚合函数介绍
MAX()			#最大值
MIN()			#最小值
AVG()			#平均值
COUNT()			#计数
SUM()			#求和
GROUP_CONCAT()	 #列转行的聚合函数
concat()		#做列值拼接

#group by
将某列中有共同条件的数据行,分成一组,然后在进行聚合函数操作

#统计每个国家,城市的个数
mysql> select countrycode,count(id) from city group by countrycode;

#统计每个国家的总人口数
mysql> select countrycode,sum(population) from city group by countrycode;

#统计每个国家省的个数
mysql> select countrycode,COUNT(district) from city group by countrycode;

#统计中国每个省的总人口数
mysql> select district,sum(population) from city where countrycode='CHN' group by district;

#统计中国每个省城市的个数
mysql> select district,count(name) from city where countrycode='CHN' group by district;

#统计中国 每个省城市的名字列表
mysql> select district,group_concat(name) from city where countrycode='CHN' group by district;
#拼接
mysql> select CONCAT(district,":",group_concat(name)) from city where countrycode='CHN' group by district;

select配合having子句

#统计所有国家的总人口数量,将总人口数大于1亿的过滤出来
mysql> select countrycode,sum(population) from city group by countrycode having sum(population)>100000000;

select配合order by子句

#最后的结果集进行排序
#统计所有国家的总人口数量,将总人口数大于5千万的过滤出来,并且按照从大到小顺序排序
mysql> select countrycode,sum(population) from city group by countrycode having sum(population)>50000000 order by sum(population) desc;

select配合limit子句

#分页显示
#统计所有国家的总人口数量,将总人口数大于5千万的过滤出来,并且按照从大到小顺序排序,只显示前3名
mysql> select countrycode,sum(population) from city group by countrycode having sum(population)>50000000 order by sum(population) desc limit 3;

#limit 3,3	跳过3行,显示一共3行
mysql> select countrycode,sum(population) from city group by countrycode having sum(population)>50000000 order by sum(population) desc limit 3,3;

#limit 3 offset 3	跳过3行,显示一共3行
mysql> select countrycode,sum(population) from city group by countrycode having sum(population)>50000000 order by sum(population) desc limit 3 offset 3;

union和union all

作用:做多个结果集合并查询的功能

#查询中国或者美国的城市信息
mysql> select * from city where countrycode='CHN' union all select * from city where countrycode='USA';

union 和 union all 的区别 ?
union all   不做去重复
union 		会做去重操作

多表连接查询(内连接)

作用:单表数据不能满足查询需求时
多表连接基本语法
1.找到多张表之前的关联条件列
2.列书写时,必须是:表名.列
3. 所有涉及到的查询列,都放在select后
4.将所有的过滤,分组,排序等条件按顺序写在on后面
	A join B  on 关联列

#查询世界上小于100人的城市,所在的国家名,国土面积,城市名,人口数
mysql> select country.name,country.surfacearea,city.name,city.population from city join country on city.countrycode=country.code where city.population<100;

元数据

元数据是存储在"基表"中
通过专用的DDL语句,DCL语句进行修改
通过专用视图和命令进行元数据的查询
information_schema中保存了大量元数据查询的视图
show命令是封装好的功能,提供元数据的查询基础的功能

information_schema的基本应用

tables视图的应用

mysql> use information_schema
mysql> desc tables
TABLE_SCHEMA	#表所在的库名
TABLE_NAME		#表名
ENGINE			#存储引擎
TABLE_ROWS		#数据行
AVG_ROW_LENGTH	#平均行长度
INDEX_LENGTH	#索引长度

#显示所有的库和表的信息
mysql> select table_schema,table_name from information_schema.tables;

#查询所有innodb引擎的表
mysql> select table_schema,table_name,engine from information_schema.tables where ENGINE='innodb';

#统计world下的city表占用空间大小
平均行长度*行数+索引长度
mysql> select table_name,(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH) from information_schema.tables where table_schema='world' and table_name='city';

#统计world库数据量总大小
mysql> select table_schema,sum((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH))/1024 from information_schema.tables where table_schema='world';

#统计每个库的数据量大小，并按数据量从大到小排序
mysql> select table_schema,sum((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH))/1024 as kb from information_schema.tables group by table_schema order by kb desc;

配合concat()函数拼接语句或命令

#模仿以下语句,进行数据库的分库分表备份。
mysqldump -uroot -p123 world city >/bak/world_city.sql

mysql> SELECT CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name ," >/bak/",table_schema,"_",table_name,".sql") FROM information_schema.tables;


#模仿以下语句,进行批量生成对world库下所有表进行操作
ALTER TABLE world.city DISCARD TABLESPACE;

mysql> SELECT  CONCAT("ALTER TABLE ",table_schema,".",table_name," DISCARD TABLESPACE;") FROM information_schema.tables WHERE table_schema='world';

show介绍

show databases;        		#查看数据库名
show tables; 		   		#查看表名
show create database xx;  	#查看建库语句
show create table xx;		#查看建表语句
show processlist;			#查看所有用户连接情况
show charset;				#查看支持的字符集
show collation;				#查看所有支持的校对规则
show grants for xx;			#查看用户的权限信息
show variables like '%xx%'  #查看参数信息
show engines;				#查看所有支持的存储引擎类型
show index from xxx			#查看表的索引信息
show engine innodb statusG  #查看innoDB引擎详细状态信息
show binary logs 			#查看二进制日志的列表信息
show binlog events in ''	#查看二进制日志的事件信息
show master status ;		#查看mysql当前使用二进制日志信息
show slave statusG 		#查看从库状态信息
show relaylog events in ''	#查看中继日志的事件信息
show status like '%'			#查看数据库整体状态信息