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

字符串：

时间

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。

二进制

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 status\G  #查看innoDB引擎详细状态信息
show binary logs                        #查看二进制日志的列表信息
show binlog events in \'\'      #查看二进制日志的事件信息
show master status ;            #查看mysql当前使用二进制日志信息
show slave status\G             #查看从库状态信息
show relaylog events in \'\'    #查看中继日志的事件信息
show status like \'%\'                  #查看数据库整体状态信息