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 '%' #查看数据库整体状态信息