一. mysql内置的功能
1.连接数据库
u
-p
-S
-h
-P
-e
<
例子:
1. mysql -uroot -p -S /tmp/mysql.sock(完整socket连接方式）(密码是123456)
2. mysql -uroot -p -h10.0.0.51 -P3306（远程连接方式） (密码是123)
3. -e 免交互执行sql语句
[root@db01 ~]# mysql -uroot -p -e "show databases;" (密码是123456)
4. < 恢复数据
[root@db01 ~]# mysql -uroot -p123456 </root/world.sql
2.内置命令
help 打印mysql帮助
\c ctrl+c 结束上个命令运行
\q quit; exit; ctrl+d 退出mysql
\G 将数据竖起来显示
source 恢复备份文件
二.SQL基础应用
1.SQL介绍
结构化的查询语言
关系型数据库通用的命令
遵循SQL92的标准(SQL_MODE)
2.SQL常用种类
DDL 数据定义语言
DCL 数据控制语言
DML 数据操作语言
DQL 数据查询语言
3. SQL引入-数据库的逻辑结构
库
库名字
库属性:字符集,排序规则
表
表名
表属性:存储引擎类型,字符集,排序规则
列名
列属性:数据类型,约束,其他属性
数据行
4. 字符集 (charset)
相当于MySQL的密码本(编码表)
show charset;
utf8 : 3个字节
utf8mb4 (建议): 4个字节,支持emoji
5. 排序规则: collation
mysql> show collation;
对于英文字符串的,大小写的敏感
utf8mb4_general_ci 大小写不敏感
utf8mb4_bin 大小写敏感(存拼音,日文)
6. 数据类型介绍
6.1 数字
整数
tinyint
int
浮点数
略
6.2 字符串
char(100)
定长字符串类型,不管字符串长度多长,都立即分配100个字符长度的存储空间,未占满的空间使用"空格"填充
varchar(100)
变长字符串类型,每次存储数据之前,都要先判断一下长度,按需分配磁盘空间.
会单独申请一个字符长度的空间存储字符长度(少于255,如果超过255以上,会占用两个存储空间)
如何选择这两个数据类型?
1. 少于255个字符串长度,定长的列值,选择char
2. 多于255字符长度,变长的字符串,可以选择varchar
enum 枚举数据类型
address enum('sz','sh','bj'.....)
1 2 3
悬念,以上数据类型可能会影响到索引的性能
6.3 时间
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。
7. DDL的应用
（用软件SQLyog操作）
7.1 库的定义
创建数据库
CREATE DATABASE zabbix CHARSET utf8mb4 COLLATE utf8mb4_bin;
--- 查看库情况
SHOW DATABASES;
SHOW CREATE DATABASE zabbix;
--- 删除数据库(不代表生产操作)
DROP DATABASE zabbix;
--- 修改数据库字符集
--- 注意: 一定是从小往大了改,比如utf8--->utf8mb4.
--- 目标字符集一定是源字符集的严格超级.
CREATE DATABASE lsn;
SHOW CREATE DATABASE lsn;
ALTER DATABASE lsn CHARSET utf8mb4;
7.2 关于库定义规范
--- 1.库名使用小写字符
--- 2.库名不能以数字开头
--- 3.不能是数据库内部的关键字
--- 4.必须设置字符集.
7.3 DDL-表定义
建表
表名,列名,列属性,表属性
--- 列属性
PRIMARY KEY : 主键约束,表中只能有一个,非空且唯一.
NOT NULL : 非空约束,不允许空值
UNIQUE KEY : 唯一键约束,不允许重复值
DEFAULT : 一般配合 NOT NULL 一起使用.
UNSIGNED : 无符号,一般是配合数字列,非负数
COMMENT : 注释
AUTO_INCREMENT : 自增长的列
mysql> use lsn;
Database changed
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;
Query OK, 0 rows affected (0.04 sec)
（字符串要加单引号，数字不用，要能够做到审核）
--- 建表规范 *****
--- 1. 表名小写字母,不能数字开头,
--- 2. 不能是保留字符,使用和业务有关的表名
--- 3. 选择合适的数据类型及长度
--- 4. 每个列设置 NOT NULL + DEFAULT .对于数据0填充,对于字符使用有效字符串填充
--- 5. 每个列设置注释
--- 6. 表必须设置存储引擎和字符集
--- 7. 主键列尽量是无关列数字列,最好是自增长
--- 8. enum类型不要保存数字,只能是字符串类型
--- 查询建表信息
SHOW TABLES;
SHOW CREATE TABLE stu;
DESC stu;（查看列情况）
--- 创建一个表结构一样的表
CREATE TABLE test LIKE stu;
--- 删表(不代表生产操作)
DROP TABLE test;
--- 修改
--- 在stu表中添加qq列 *****
mysql>ALTER TABLE stu ADD qq VARCHAR(20) NOT NULL COMMENT 'qq号';
（对生产的性能影响比较大）
pt-osc（在线DDL时，减少索表情况）
--- 在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 ;
--- 把刚才添加的列都删掉(危险,不代表生产操作) ***
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 '性别';
7.4 DCL
grant
revoke
7.5 DML
insert
DESC stu;
--- 最偷懒
INSERT stu VALUES(1,'zs',18,'m',NOW());
SELECT * FROM stu;
--- 最规范
INSERT INTO stu(id,sname,age,sex,intime)
VALUES (2,'ls',19,'f',NOW());
--- 针对性的录入数据
INSERT INTO stu(sname,age,sex)
VALUES ('w5',11,'m');
--- 一次性录入多行
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;
8.DQL 介绍
select （查看内容）
show （查看属性类）
8.1 select 语句的应用
8.1.1 select单独使用的情况
mysql> select @@basedir;
mysql> select @@port;
mysql> select @@innodb_flush_log_at_trx_commit;
mysql> show variables like 'innodb%';
mysql> use world
mysql> select database();
mysql> select now();
8.1.2 select 通用语法(单表)
select 列
from 表
where 条件
group by 条件
having 条件
order by 条件
limit
三.select练习
1.环境准备
先导入world数据库
[root@web01 ~]# cd /usr
[root@web01 usr]# rz -E #上传 world.sql
mysql> CREATE DATABASE world CHARSET utf8mb4 COLLATE utf8mb4_bin;
mysql> use world
mysql> source /usr/world.sql;
mysql> show tables;
city 城市表
country 国家表
countrylanguage 国家的语言
city表结构
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.01 sec)
ID : 城市序号(1-...)
name : 城市名字
countrycode : 国家代码,例如:CHN,USA
district : 区域: 中国 省 美国 洲
population : 人口数
mysql> show create table city;(查看注释）
2.SELECT 配合 FROM 子句使用
select 列,列,列 from 表
--- 例子:
1）查询表中所有的信息(生产中几乎是没有这种需求的)
USE world ;
SELECT id,NAME ,countrycode ,district,population FROM city;
或者:
SELECT * FROM city;
2）查询表中 name和population的值
SELECT NAME ,population FROM city;
3.SELECT 配合 WHERE 子句使用
select 列,列,列 from 表 where 过滤条件
-- where等值条件查询 *****
例子:
1） 查询中国所有的城市名和人口数
SELECT NAME,population FROM city
WHERE countrycode='CHN';
-- where 配合比较判断查询(> < >= <=) *****
例子:
2）世界上小于100人的城市名和人口数
SELECT NAME,population FROM city
WHERE population<100;
-- where 配合 逻辑连接符(and or)
例子:
3）查询中国人口数量大于1000w的城市名和人口
SELECT NAME,population FROM city
WHERE countrycode='CHN' AND population>8000000;
4）查询中国或美国的城市名和人口数
SELECT NAME,population FROM city
WHERE countrycode='CHN' OR countrycode='USA';
5） 查询人口数量在500w到600w之间的城市名和人口数
SELECT NAME,population FROM city
WHERE population>5000000 AND population<6000000;
或者:
SELECT NAME,population FROM city
WHERE population BETWEEN 5000000 AND 6000000;
-- where 配合 like 子句 模糊查询 *****
例子:
1）查询一下contrycode中带有CH开头,城市信息
SELECT * FROM city
WHERE countrycode LIKE 'CH%';
注意:不要出现类似于 %CH%,前后都有百分号的语句,因为不走索引,性能极差
如果业务中有大量需求,我们用"ES"来替代
-- where 配合 in 语句
例子:
1）查询中国或美国的城市信息.
SELECT NAME,population FROM city
WHERE countrycode='CHN' OR countrycode='USA';
或者:
SELECT NAME,population FROM city
WHERE countrycode IN ('CHN' ,'USA');
4.SELECT 配合GROUP BY+聚合函数应用
4.1 常用聚合函数介绍
MAX(),MIN(),AVG(),COUNT(),SUM()
GROUP_CONCAT()
4.2 GROUP BY
将某列中有共同条件的数据行,分成一组,然后在进行聚合函数操作.
例子:
1. 统计每个国家,城市的个数
SELECT countrycode ,COUNT(id) FROM city
GROUP BY countrycode;
2. 统计每个国家的总人口数.
SELECT countrycode,SUM(population) FROM city
GROUP BY countrycode;
3. 统计每个 国家 省 的个数
SELECT countrycode,COUNT(DISTINCT district) FROM city
GROUP BY countrycode;（DISTINCT去重）
4. 统计中国 每个省的总人口数
SELECT district, SUM(population) FROM city
WHERE countrycode='CHN'
GROUP BY district ;
5. 统计中国 每个省城市的个数
SELECT district, COUNT(NAME) FROM city
WHERE countrycode='CHN'
GROUP BY district ;
6. 统计中国 每个省城市的名字列表GROUP_CONCAT()
guangdong guangzhou,shenzhen,foshan....
SELECT district, GROUP_CONCAT(NAME) FROM city
WHERE countrycode='CHN'
GROUP BY district ;
7. 小扩展
anhui : hefei,huaian ....
SELECT CONCAT(district,":" ,GROUP_CONCAT(NAME)) FROM city
WHERE countrycode='CHN'
GROUP BY district ;
5.SELECT 配合 HAVING 应用ORDER BY 子句
例子:
1. 统计所有国家的总人口数量,
将总人口数大于5000w的过滤出来,
并且按照从大到小顺序排列
SELECT countrycode,SUM(population) FROM city
GROUP BY countrycode
HAVING SUM(population)>50000000
ORDER BY SUM(population) DESC ;
6.SELECT 配合 LIMIT 子句
例子:
1. 统计所有国家的总人口数量,
将总人口数大于5000w的过滤出来,
并且按照从大到小顺序排列,只显示前三名
SELECT countrycode,SUM(population) FROM city
GROUP BY countrycode
HAVING SUM(population)>50000000
ORDER BY SUM(population) DESC
LIMIT 3 OFFSET 0;
SELECT countrycode,SUM(population) FROM city
GROUP BY countrycode
HAVING SUM(population)>50000000
ORDER BY SUM(population) DESC
LIMIT 3 OFFSET 3
LIMIT M,N :跳过M行,显示一共N行
LIMIT Y OFFSET X: 跳过X行,显示一共Y行
7.练习题:
1. 统计中国每个省的总人口数，只打印总人口数小于100w的
SELECT district ,SUM(population) FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)<1000000;
2. 查看中国所有的城市，并按人口数进行排序(从大到小)
SELECT * FROM city WHERE countrycode='CHN'
ORDER BY population DESC;
3. 统计中国各个省的总人口数量，按照总人口从大到小排序
SELECT district ,SUM(population) FROM city
WHERE countrycode='CHN'
GROUP BY district
ORDER BY SUM(population) DESC ;
4. 统计中国,每个省的总人口,找出总人口大于500w的,
并按总人口从大到小排序,只显示前三名
SELECT district ,SUM(population) FROM city
WHERE countrycode='CHN'
GROUP BY district
HAVING SUM(population)>5000000
ORDER BY SUM(population) DESC
LIMIT 3;
8.小结
语句执行顺序
select disctrict , count(name) from city
where countrycode='CHN'
group by district
having count(name) >10
order by count(name) desc
limit 3;
9.union 和 union all
作用: 多个结果集合并查询的功能
需求: 查询中或者美国的城市信息
SELECT * FROM city WHERE countrycode='CHN' OR countrycode='USA';
改写为:（性能比or语句好很多）
SELECT * FROM city WHERE countrycode='CHN'
UNION ALL
SELECT * FROM city WHERE countrycode='USA';
union 和 union all 的区别 ?
union all 不做去重复
union 会做去重操作
11.多表连接查询(内连接)
11.1 作用
单表数据不能满足查询需求时.
例子: 查询世界上小于100人的城市,所在的国家名,国土面积,城市名,人口数
city:
SELECT countrycode,NAME,population FROM city WHERE population<100;
PCN Adamstown 42
country
DESC country;
CODE
NAME
SurfaceArea
SELECT NAME ,SurfaceArea FROM country WHERE CODE='PCN';
Pitcairn
49.00
11.2 多表连接基本语法要求
1.最核心的是，找到多张表之间的关联条件列
2.列书写时，必须是：表名.列
3.所有涉及到的查询列，都放在select后
4.将所有的过滤，分组，排序等条件按顺序写在on的后面
SELECT
country.name,
country.SurfaceArea,
city.name,
city.population,
FROM city
JOIN country
ON city.CountryCode=country.code
WHERE city.population<100
5.多张表
A
JOIN B
ON A.x=B.y
JOIN C
ON B.m=C.n
四.学生管理系统
use school
student ：学生表
sno： 学号
sname：学生姓名
sage： 学生年龄
ssex： 学生性别
teacher ：教师表
tno： 教师编号
tname：教师名字
course ：课程表
cno： 课程编号
cname：课程名字
tno： 教师编号
score ：成绩表
sno： 学号
cno： 课程编号
score：成绩
-- 项目构建
drop database school;
CREATE DATABASE school CHARSET utf8;
USE school
CREATE TABLE student(
sno INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
sname VARCHAR(20) NOT NULL COMMENT '姓名',
sage TINYINT UNSIGNED NOT NULL COMMENT '年龄',
ssex ENUM('f','m') NOT NULL DEFAULT 'm' COMMENT '性别'
)ENGINE=INNODB CHARSET=utf8;
CREATE TABLE course(
cno INT NOT NULL PRIMARY KEY COMMENT '课程编号',
cname VARCHAR(20) NOT NULL COMMENT '课程名字',
tno INT NOT NULL COMMENT '教师编号'
)ENGINE=INNODB CHARSET utf8;
CREATE TABLE sc (
sno INT NOT NULL COMMENT '学号',
cno INT NOT NULL COMMENT '课程编号',
score INT NOT NULL DEFAULT 0 COMMENT '成绩'
)ENGINE=INNODB CHARSET=utf8;
CREATE TABLE teacher(
tno INT NOT NULL PRIMARY KEY COMMENT '教师编号',
tname VARCHAR(20) NOT NULL COMMENT '教师名字'
)ENGINE=INNODB CHARSET utf8;
INSERT INTO student(sno,sname,sage,ssex)
VALUES (1,'zhang3',18,'m');
INSERT INTO student(sno,sname,sage,ssex)
VALUES
(2,'zhang4',18,'m'),
(3,'li4',18,'m'),
(4,'wang5',19,'f');
INSERT INTO student
VALUES
(5,'zh4',18,'m'),
(6,'zhao4',18,'m'),
(7,'ma6',19,'f');
INSERT INTO student(sname,sage,ssex)
VALUES
('oldboy',20,'m'),
('oldgirl',20,'f'),
('oldp',25,'m');
INSERT INTO teacher(tno,tname) VALUES
(101,'oldboy'),
(102,'hesw'),
(103,'oldguo');
DESC course;
INSERT INTO course(cno,cname,tno)
VALUES
(1001,'linux',101),
(1002,'python',102),
(1003,'mysql',103);
DESC sc;
INSERT INTO sc(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 * FROM student;
SELECT * FROM teacher;
SELECT * FROM course;
SELECT * FROM sc;
student ：学生表
===============
sno： 学号
sname：学生姓名
sage： 学生年龄
ssex： 学生性别
teacher ：教师表
================
tno： 教师编号
tname：教师名字
course ：课程表
===============
cno： 课程编号
cname：课程名字
tno： 教师编号
score ：成绩表
==============
sno： 学号
cno： 课程编号
score：成绩
1.多表连接例子
-- 1. 统计zhang3,学习了几门课
SELECT student.sname,COUNT(sc.cno)
FROM student JOIN sc
ON student.sno=sc.sno
WHERE student.sname='zhang3';
-- 2. 查询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;
-- 3. 查询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;
-- 4. 查询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;（有可能不止教一门，所以需要分组）
-- 5. 每位老师所教课程的平均分,并按平均分排序
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)
-- 6. 查询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
-- 7. 查询所有老师所教学生不及格的信息(扩展)
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
-- 8.别名应用
表别名 :
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
表别名是全局调用的.
列别名:
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
GROUP BY t.tno
列别名可以被 having 和 order by 调用
1. 上节回顾
1.1 group by (先排序，再去重）当有统计类需求时使用，不能一对多
1.2 关于group by的sql_mode
only_full_group_by
说明：
1. 在5.7版本中MySQL sql_mode参数中自带，5.6和8.0都没有
2. 在带有group by 字句的select中，select 后的条件列（非主键列），
要么是group by后的列，要么需要在函数中包裹
1.3 group_concat
列转行聚合函数
mysql> select user,group_concat(host) from mysql.user group by user;
1.4 concat
做列值拼接
mysql> select concat(user,"@",host) from mysql.user;
1.5 关于多表连接语法规则
1.首先找涉及到的所有表
2.找到表和表之间的关联列
3.关联条件写在on后面
A join B on 关联列
4. 所有需要查询的信息放在select后
5. 其他的过滤条件where group by having order by limit 往最后放
6.（有关性能的操作）注意：对多表连接中，驱动表（第一张表）选择数据行少的表。后续所有表的关联列尽量是主键或唯一键（在表设计时设置），至少建立一个索引。
1.6 别名
表别名
列别名
count
oldguo linux a,b,c,d
oldguo python x，y,z
oldboy linux 10
hsw python 11
1.7 distinct（去重）
mysql> select count(distinct countrycode) from city;
1.8 select 执行顺序
select user ,count(name) from 表 where 列 group by user having 列 order by 列 ;
2. 扩展类内容-元数据获取 ***
2.0 元数据介绍及获取介绍
元数据是存储在"基表"中。
通过专用的DDL语句，DCL语句进行修改
通过专用视图和命令进行元数据的查询
information_schema中保存了大量元数据查询的试图
show 命令是封装好功能，提供元数据查询基础功能
2.1 information_schema的基本应用 ***
tables 视图的应用
mysql>create view aa as 语句;(创建视图）
mysql>select * from aa; (查询视图）
mysql> use information_schema;
mysql> show tables;
mysql> desc tables;（tables保存了整个数据库范围内所有表的元数据）
TABLE_SCHEMA 表所在的库名
TABLE_NAME 表名
ENGINE 存储引擎
TABLE_ROWS 数据行
AVG_ROW_LENGTH 平均行长度
INDEX_LENGTH 索引长度
例子：(在SQLYOG中进行)（不用记，会用就行）
USE information_schema;
DESC TABLES;
-- 1. 显示所有的库和表的信息
SELECT table_schema,table_name FROM information_schema.tables;
-- 2. 以以下模式 显示所有的库和表的信息
-- world city,country,countrylanguage
SELECT table_schema,GROUP_CONCAT(table_name)
FROM information_schema.tables
GROUP BY table_schema;
-- 3. 查询所有innodb引擎的表
SELECT table_schema,table_name ,ENGINE
FROM information_schema.tables
WHERE ENGINE='innodb';
-- 4. 统计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';
-- 5. 统计world库数据量总大小
SELECT table_schema,SUM((AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH))/1024
FROM information_schema.TABLES
WHERE table_schema='world';
-- 6. 统计每个库的数据量大小，并按数据量从大到小排序
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 ;
2.2 配合concat()函数拼接语句或命令（项目当中用到的语句）
例子：
-- 1. 模仿以下语句,进行数据库的分库分表备份。
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;
-- 2. 模仿以下语句,进行批量生成对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 '' 查看数据库整体状态信息
224
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
