SQL
MySQL内置功能
连接数据库
- -u
- -p
- -S
- -h
- -P
- -e
- <
示例：
- mysql -u root -p -S /tmp/mysql.sock
- mysql -u root -p -h 10.0.0.51 -P3306
- -e 免交互执行 sql 语句
[root@db01 ~]# mysql -uroot -p -e "show databases;" - < 导入数据
[root@db01 ~]# mysql -uroot -p123 /root/world.sql
内置命令
- help 帮助
- \c ctrl+c 结束上个命令运行
- \q quit exit ctrl+d 退出
- \G 竖行显示
- source 恢复备份文件
SQL 基础应用
- 介绍
结构化的查询语言
关系型数据库通用的命令
遵循SQL92标准（SQL_MODE）
- 常用种类
DDL 数据定义语言
DCL 数据控制语言
DML 数据操作语言
DQL 数据查询语言
数据库的逻辑结构
库
- 库名
- 库属性(字符集，排序规则)
表
- 表名
- 表属性(存储引擎类型，字符集，排序规则)
- 列名
- 列属性(数据类型，约束，其他属性)
- 数据行
字符集(charset)
查看支持字符集
show charset- utf8 三个字符
- utfmb4 四个字符(支持emoji)
排序规则(collation)
查看排序规则
show collation英文字符串的大小写不敏感
- utf8mb4_general_ci 大小写不敏感
- utf8mb4_bin 大小写敏感(存拼音,日文)
数据类型
- 数字：tinyint int
字符串:
- char(100)
定长字符串类型,不管字符串长度多长,都立即分配100个字符长度的存储空间,未占满的空间使用"空格"填充
- varchar(100)
变长字符串类型,每次存储数据之前,都要先判断一下长度,按需分配此盘空间.
会单独申请一个字符长度的空间存储字符长度(少于255,如果超过255以上,会占用两个存储空间)
如何选择这两个数据类型?
- 少于255个字符串长度,定长的列值,选择char
- 多于255字符长度,变长的字符串,可以选择varchar
- char(100)
枚举
- address enum('sz','sh','bj'.....)
可能会影响到索引的性能
- address enum('sz','sh','bj'.....)
时间
- 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
- datetime
- 二进制
DDL
库的定义
- 创建数据库
CREATE DATABASE zabbix CHARSET utf8mb4 COLLATE utf8mb4_bin;
- 查看库情况
SHOW DATABASES;
SHOW CREATE DATABASE zabbix;
- 删除数据库(不代表生产操作)
DROP DATABASE oldguo;
- 修改数据库字符集
注意: 一定是从小往大了改,比如utf8--->utf8mb4.
目标字符集一定是源字符集的严格超级.
CREATE DATABASE oldguo;
SHOW CREATE DATABASE oldguo;
ALTER DATABASE oldguo CHARSET utf8mb4;
- 创建数据库
库定义规范
- 库名使用小写字符
- 库名不能以数字开头
- 不能为数据库内部关键字
- 必须设置字符集
表的定义
- 建表
表名, 列名, 列属性, 表属性
列属性
- PRIMARY KEY : 主键约束,表中只能有一个,非空且唯一.
- NOT NULL : 非空约束,不允许空值
- UNIQUE KEY : 唯一键约束,不允许重复值
- DEFAULT : 一般配合 NOT NULL 一起使用.
- UNSIGNED : 无符号,一般是配合数字列,非负数
- COMMENT : 注释
- AUTO_INCREMENT : 自增长的列
示例：
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;- 建表
建表规范
- 表名小写字母,不能数字开头
- 不能是保留字符,使用和业务有关的表名
- 选择合适的数据类型及长度
- 每个列设置 NOT NULL + DEFAULT ;对于数据0填充,对于字符使用有效字符串填充
- 每个列设置注释
- 表必须设置存储引擎和字符集
- 主键列尽量是无关列数字列,最好是自增长
- enum类型不要保存数字,只能是字符串类型
- 查询表信息
SHOW TABLES;
SHOW CREATE TABLE stu;
DESC stu; - 创建一个表结构一样的表
CREATE TABLE test LIKE stu;
- 删表(不代表生产操作)
DROP TABLE test;
修改
在stu表中添加qq列
DESC stu; ALTER TABLE stu ADD qq VARCHAR(20) NOT NULL COMMENT 'qq号';在sname后加微信列
ALTER TABLE stu ADD wechat VARCHAR(64) NOT NULL UNIQUE COMMENT '微信号' AFTER sname;在id列前加一个新列num
ALTER TABLE stu ADD num INT NOT NULL UNIQUE COMMENT '身份证' FIRST ; DESC stu;把刚才添加的列都删掉(危险,不代表生产操作
ALTER TABLE stu DROP num; DESC stu; ALTER TABLE stu DROP qq; ALTER TABLE stu DROP wechat;修改sname数据类型的属性
DESC stu; ALTER TABLE stu MODIFY sname VARCHAR(64) NOT NULL COMMENT '姓名';将gender 改为 sex 数据类型改为 CHAR 类型
ALTER TABLE stu CHANGE gender sex CHAR(4) NOT NULL COMMENT '性别';
DCL
grant
revoke
DML
插入数据(insert)
简单
INSERT stu VALUES(1,'zs',18,'m',NOW()); SELECT * FROM stu;规范
NSERT INTO stu(id,sname,age,sex,intime) VALUES (2,'ls',19,'f',NOW());录入多行
INSERT INTO stu(sname,age,sex) VALUES ('aa',11,'m'), ('bb',12,'f'), ('cc',13,'m');update (一定加where条件)
UPDATE stu SET sname='aaa'; SELECT * FROM stu; UPDATE stu SET sname='bb' WHERE id=6;delete (一定要加where条件)
DELETE FROM stu; DELETE FROM stu WHERE id=9;生产中屏蔽delete功能，使用update替代delete
ALTER TABLE stu ADD is_del TINYINT DEFAULT 0 ; UPDATE stu SET is_del=1 WHERE id=7; SELECT * FROM stu WHERE is_del=0;
DQL
DQL介绍
- SELECT
- SHOW
SELECT 语句的应用
- SELECT单独使用的情况
mysql> select @@basedir;
mysql> select @@port;
mysql> select @@innodb_flush_log_at_trx_commit;
mysql> show variables like 'innodb%';
mysql> select database();
mysql> select now(); - SELECT通用语法表(单表)
select 列
from 表
where 条件
group by 条件
having 条件
order by 条件
limit 学习环境说明
world 数据库
- city 城市表
- country 国家表
- countrylanguage 语言表
city表结构
- ID 城市序号
- name 城市名代号
- countrycode 国家
- district 区域
- population 人口
SELECT 配合 FROM 子句使用
SELECT 列 from 表示例：
查询表中所有的信息(生产中几乎是没有这种需求的)
USE world ; SELECT id,NAME ,countrycode ,district,population FROM city; 或者 SELECT * FROM city;查询表中 name 和population的值
SELECT NAME ,population FROM city;
SELECT 配合 WHERE 子句使用
select 列 from 表 where 过滤条件示例:
等值条件查询
查询中国所有的城市名和人口数
SELECT NAME,population FROM city WHERE countrycode='CHN';比较判断查询
世界上小于100人的城市名和人口数
SELECT NAME,population FROM city WHERE population<100;逻辑连接符
查询中国人口数量大于1000w的城市名和人口
SELECT NAME,population FROM city WHERE countrycode='CHN' AND population>8000000;查询中国或美国的城市名和人口数
SELECT NAME,population FROM city WHERE countrycode='CHN' OR countrycode='USA';查询人口数量在500w到600w之间的城市名和人口数
SELECT NAME,population FROM city WHERE population>5000000 AND population<6000000; 或者 SELECT NAME,population FROM city WHERE population BETWEEN 5000000 AND 6000000;模糊查询
查询一下contrycode中带有CH开头,城市信息
SELECT * FROM city WHERE countrycode LIKE 'CH%';TIP:不要出现类似于 %CH%,前后都有百分号的语句,因为不走索引,性能极差。如果业务中有大量需求,我们用"ES"来替代。
in 语句
查询中国或美国的城市信息
SELECT NAME,population FROM city WHERE countrycode='CHN' OR countrycode='USA'; 或者 SELECT NAME,population FROM city WHERE countrycode IN ('CHN' ,'USA');
GROUP BY
将某列中有共同条件的数据行,分成一组,然后在进行聚合函数操作
统计每个国家,城市的个数
SELECT countrycode ,COUNT(id) FROM city GROUP BY countrycode;统计每个国家的总人口数.
SELECT countrycode,SUM(population) FROM city GROUP BY countrycode;统计每个 国家 省 的个数
SELECT countrycode,COUNT(DISTINCT district) FROM city GROUP BY countrycode;统计中国 每个省的总人口数
SELECT district, SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district ;统计中国 每个省城市的个数
SELECT district, COUNT(NAME) FROM city WHERE countrycode='CHN' GROUP BY distric统计中国 每个省城市的名字列表GROUP_CONCAT()
SELECT district, GROUP_CONCAT(NAME) FROM city WHERE countrycode='CHN' GROUP BY district ;anhui : hefei,huaian ....
SELECT CONCAT(district,":" ,GROUP_CONCAT(NAME)) FROM city WHERE countrycode='CHN' GROUP BY district ;ORDER BY
统计所有国家的总人口数量,将总人口数大于5000w的过滤出来,并且按照从大到小顺序排列
SELECT countrycode,SUM(population) FROM city GROUP BY countrycode HAVING SUM(population)>50000000 ORDER BY SUM(population) DESC ;LIMIT
统计所有国家的总人口数量,将总人口数大于5000w的过滤出来,并且按照从大到小顺序排列,只显示前三名
SELECT countrycode,SUM(population) FROM city GROUP BY countrycode HAVING SUM(population)>50000000 ORDER BY SUM(population) DESC LIMIT 3 OFFSET 0;LIMIT M,N :跳过M行,显示一共N行
LIMIT Y OFFSET X: 跳过X行,显示一共Y行小结
select disctrict , count(name) from city where countrycode='CHN' group by district having count(name) >10 order by count(name) desc limit 3;union 和 union all
多个结果集合并查询的功能
查询中或者美国的城市信息
SELECT * FROM city WHERE countrycode='CHN' OR countrycode='USA';改写
SELECT * FROM city WHERE countrycode='CHN' UNION ALL SELECT * FROM city WHERE countrycode='USA';union 和 union all 的区别 ?
union all 不做去重复
union 会做去重操作练习题
统计中国每个省的总人口数，只打印总人口数小于100w的
SELECT district ,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district HAVING SUM(population)<1000000;查看中国所有的城市，并按人口数进行排序(从大到小)
SELECT * FROM city WHERE countrycode='CHN' ORDER BY population DESC;统计中国各个省的总人口数量，按照总人口从大到小排序
SELECT district ,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district ORDER BY SUM(population) DESC ;统计中国,每个省的总人口,找出总人口大于500w的,并按总人口从大到小排序,只显示前三名
SELECT district ,SUM(population) FROM city WHERE countrycode='CHN' GROUP BY district HAVING SUM(population)>5000000 ORDER BY SUM(population) DESC LIMIT 3;
- SELECT单独使用的情况
多表连接查询(内连接)
作用
单表数据不能满足查询需求时
查询世界上小于100人的城市,所在的国家名,国土面积,城市名,人口数
SELECT countrycode,NAME,population FROM city WHERE population<100; PCN Adamstown 42 SELECT NAME ,SurfaceArea FROM country WHERE CODE='PCN';多表连接基本语法
student ：学生表 =============== sno： 学号 sname：学生姓名 sage： 学生年龄 ssex： 学生性别 teacher ：教师表 ================ tno： 教师编号 tname：教师名字 course ：课程表 =============== cno： 课程编号 cname：课程名字 tno： 教师编号 score ：成绩表 ============== sno： 学号 cno： 课程编号 score：成绩多表连接例子
统计zhang3,学习了几门课
SELECT student.sname,COUNT(sc.cno) FROM student JOIN sc ON student.sno=sc.sno WHERE student.sname='zhang3';查询zhang3,学习的课程名称有哪些?
SELECT student.sname,GROUP_CONCAT(course.cname) FROM student JOIN sc ON student.sno=sc.sno JOIN course ON sc.cno=course.cno WHERE student.sname='zhang3' GROUP BY student.sname;查询oldguo老师教的学生名和个数.
SELECT teacher.tname,GROUP_CONCAT(student.sname),COUNT(student.sname) FROM teacher JOIN course ON teacher.tno=course.tno JOIN sc ON course.cno=sc.cno JOIN student ON sc.sno=student.sno WHERE teacher.tname='oldguo' GROUP BY teacher.tname;查询oldguo所教课程的平均分数
SELECT teacher.tname,AVG(sc.score) FROM teacher JOIN course ON teacher.tno=course.tno JOIN sc ON course.cno=sc.cno WHERE teacher.tname='oldguo' GROUP BY sc.cno;每位老师所教课程的平均分,并按平均分排序
SELECT teacher.tname,course.cname,AVG(sc.score) FROM teacher JOIN course ON teacher.tno=course.tno JOIN sc ON course.cno=sc.cno GROUP BY teacher.tname,course.cname ORDER BY AVG(sc.score)查询oldguo所教的不及格的学生姓名
SELECT teacher.tname,student.sname,sc.score FROM teacher JOIN course ON teacher.tno=course.tno JOIN sc ON course.cno=sc.cno JOIN student ON sc.sno=student.sno WHERE teacher.tname='oldguo' AND sc.score<60查询所有老师所教学生不及格的信息(扩展)
SELECT teacher.tname,GROUP_CONCAT(CONCAT(student.sname,":",sc.score)) FROM teacher JOIN course ON teacher.tno=course.tno JOIN sc ON course.cno=sc.cno JOIN student ON sc.sno=student.sno WHERE sc.score<60 GROUP BY teacher.tno别名应用
表别名 (全局调用)
SELECT t.tname,GROUP_CONCAT(CONCAT(st.sname,":",sc.score)) FROM teacher as t JOIN course as c ON t.tno=c.tno JOIN sc ON c.cno=sc.cno JOIN student as st ON sc.sno=st.sno WHERE sc.score<60 GROUP BY t.tno列别名（having 和 order by 调用）
SELECT t.tname as 讲师名 ,GROUP_CONCAT(CONCAT(st.sname,":",sc.score)) as 不及格的 FROM teacher as t JOIN course as c ON t.tno=c.tno JOIN sc ON c.cno=sc.cno JOIN student as st ON sc.sno=st.sno WHERE sc.score<60
扩展类内容-元数据获取
- 元数据介绍及获取
元数据是存储在"基表"中。
通过专用的DDL语句，DCL语句进行修改
通过专用视图和命令进行元数据的查询
information_schema中保存了大量元数据查询的试图
show 命令是封装好功能，提供元数据查询基础功能
information_schema的基本应用
tables 视图的应用
use information_schema; mysql> desc tables;TABLE_SCHEMA 表所在的库名
TABLE_NAME 表名
ENGINE 存储引擎
TABLE_ROWS 数据行
AVG_ROW_LENGTH 平均行长度
INDEX_LENGTH 索引长度示例
USE information_schema; DESC TABLES;显示所有的库和表的信息
SELECT table_schema,table_name FROM information_schema.tables;以以下模式 显示所有的库和表的信息
world city,country,countrylanguage
SELECT table_schema,GROUP_CONCAT(table_name) FROM information_schema.tables GROUP BY table_schema;查询所有innodb引擎的表
SELECT table_schema,table_name ,ENGINE FROM information_schema.tables WHERE ENGINE='innodb';统计world下的city表占用空间大小
表的数据量=平均行长度*行数+索引长度
AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH SELECT table_name,(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024 FROM information_schema.TABLES WHERE table_schema='world' AND table_name='city';统计world库数据量总大小
SELECT table_schema,SUM((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH))/1024 FROM information_schema.TABLES WHERE table_schema='world';统计每个库的数据量大小，并按数据量从大到小排序
SELECT table_schema,SUM((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH))/1024 AS total_KB FROM information_schema.TABLES GROUP BY table_schema ORDER BY total_KB DESC ;
配合concat()函数拼接语句或命令
示例：
模仿以下语句,进行数据库的分库分表备份。
mysqldump -uroot -p123 world city >/bak/world_city.sql
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;
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 '' 查看数据库整体状态信息
- 元数据介绍及获取
