MySQL系列：（七）MySQL数据增、删、改、查

1、数据的增、删、改

数据的增删改属于数据操作语言应用（DML）

1.1、insert（插入数据）

说明：有默认值的列可以不录入

• 作用：
  插入数据

• 语法
  INSERT INTO [表名] (列1,列2,...)
  VALUES (值1，值2,...)

• 示例

  INSERT INTO stu(id,s_name,age,sex,intime) VALUES (1,'张三',20,'男',NOW());
  

1.2、update（修改数据）

• 作用
  修改表中数据

• 语法
  UPDATE [表名] SET 字段1=值 1 [,字段2=值2… ] [WHERE 子句 ] [ORDER BY 子句] [LIMIT 子句]

• 示例

  UPDATE stu SET s_name = 'zhang3' WHERE id = 1;
  

1.3、delete（删除数据）

1.3.1、作用

删除一行或多行数据

1.3.2、按条件删除

• 语法
  DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]

• 示例

  DELETE FROM stu WHERE id = 4;
  

1.3.3、删除全部数据

• 语法
  DELETE FROM [表名]
  truncate table [表名]

• 示例

  DELETE FROM stu
  truncate table stu;
  # 二者间的区别:
  delete: 属于DML操作, 是逻辑性质删除,逐行进行删除，速度慢
  truncate: 属于DDL操作,对与表段中的数据页进行清空，速度快
  

1.3.4、伪删除

伪删除是指：用update来替代delete，最终保证业务中查不到（select）即可

• 操作流程

  # 添加状态列
  ALTER TABLE stu ADD state TINYINT NOT NULL DEFAULT 1;
  # UPDATE 替代 DELETE
  UPDATE stu SET state=0 WHERE id=6;
  # 业务语句查询
  SELECT * FROM stu WHERE state=1;
  

2、数据查询

2.1、select（查询语句）

2..1.1、select单独使用

# 查看当前数据库
  SELECT database();
# 查看数据库端口
  SELECT @@port;
# 查看数据库软件安装目录
  SELECT @@basedir;
# 查看数据库目录
  SELECT @@datadir;
# 查看错误日志路径
  SELECT @@log_error;
# 查看数据库版本
  SELECT @@version;
# 查看server_id
  SELECT @@server_id;
 # 查看默认密码过期策略
 select @@default_password_lifetime;

2..1.2、单表操作

（1）、总体语法

select [列] from [表] where [条件] group by [条件] having [条件] order by [条件] limit [条件]

（2）、SELECT 配合 FROM 子句使用

• 语法
  select [列1],[列1],... from [表]

• 示例

  # 查询表中所有信息
  SELECT * FROM city;
  

（3）、SELECT 配合 WHERE 子句使用

• 语法
  select [列1],[列1],... from [表] where [过滤条件]

• 示例

  # 等值查询
  SELECT Name,Population from city WHERE CountryCode='CHN';
  # 不等值查询
  SELECT Name,Population from city WHERE Population<10000;
  # 配合逻辑运算符（and、or）
  SELECT Name,Population from city WHERE CountryCode='CHN' AND Population>1000000;
  SELECT Name,Population from city WHERE Population>1000000 OR Population<900000;
  # 配合like子句实现模糊查询
  SELECT * FROM city WHERE Name LIKE 'Sh%';
  

（4）、SELECT 配合 GROUP BY 与 "统计函数" 应用

• 常用聚合函数介绍
  最大值：MAX()
  最小值：MIN()
  平均值：AVG()
  计数 ：COUNT()
  求和 ：SUM()
  列转行：GROUP_CONCAT()
  列数据拼接：CONCAT()

• group by

  • group by作用
    将某列中有共同条件的数据行分成一组，然后再进行聚合函数操作，使用 DISTINCT 参数实现去重复

  • 示例

    # 统计每个国家的总人口数
    SELECT countrycode,SUM(Population) FROM city GROUP BY CountryCode;
    
    # 统计每个国家的城市数量 
    SELECT countrycode,COUNT(id) 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(id) FROM city WHERE Countrycode='CHN' GROUP BY District;
    
    # 统计中国每个省的城市名称列表 
    SELECT District,GROUP_CONCAT(Name) FROM city WHERE Countrycode='CHN' GROUP BY District;
    

（5）、SELECT 配合 HAVING 子句应用

• 作用
  having主要用于二次过滤，功能与where相同

• 说明
  having属于全表操作，不使用索引，因此在性能上有一定的影响

• 示例

  # 统计所有国家人口总数并显示总人口大于1亿的国家 
  SELECT countrycode,SUM(Population) FROM city GROUP BY CountryCode HAVING SUM(Population) > 100000000;
  

（6）、SELECT 配合 ORDER BY 子句

• 作用
  用于排序，倒序排列使用 DESC 参数

• 示例

  # 统计所有国家人口总数并显示总人口大于5KW的国家 ，并按照从大到小排列
  SELECT countrycode,SUM(Population) FROM city GROUP BY CountryCode HAVING SUM(Population)>50000000 ORDER BY SUM(Population) DESC;
  

（7）、SELECT 配合 LIMIT 子语句

• 作用
  主要用于分页显示

• 用法
  LIMIT m,n

  说明：跳过 m 行显示 n 行

• 示例

  # 统计所有国家人口总数并显示总人口大于5KW的国家 ，并按照从大到小排列，且只显示前三名
  SELECT countrycode,SUM(Population) FROM city GROUP BY CountryCode HAVING SUM(Population)>50000000 ORDER BY SUM(Population) DESC LIMIT 3;
  

（8）、SELECT 配合 UNION 和UNION ALL 子句

• 作用
  多个结果集合并查询，性能优于逻辑操作

• union和union all的区别
  union会执行去重操作，union all不执行去重操作

• 示例

  # 查询中国和美国的城市信息 
  SELECT * FROM city WHERE Countrycode='CHN' UNION ALL SELECT * FROM city WHERE Countrycode='USA';
  

2.1.3、多表链接查询（内连接）

（1）、作用

​ 单表数据不能满足查询需求时，需使用多表连接查询

（2）、语法要求

• 多表操作的关键点是找到多张表之间的关联条件列
• 列书写时，必须是：表名.列
• 所有涉及到的查询列都放到SELECT后
• 表间的关联条件写在ON后面
• 将所有的过滤、分组、排序的条件按顺序写在ON后面

（3）、单表连接语法

​ SELECT [表1.列],[表2.列],... FROM [表1] JOIN [表2] ON [表1.列] = [表2.列] WHERE [过滤条件]

（4）、多表连接语法

​ SELECT [表1.列],[表2.列],... FROM [表1] JOIN [表2] ON [表1.列] = [表2.列] JOIN [表3] ON [表2.列] = [表3.列] WHERE [过滤条件]

• 示例

  # 查询世界上人口小于1000人的城市，并列出其所在的国家、国土面积、城市名、人口数量
  SELECT city.CountryCode,city.Name,city.Population,country.Name,country.SurfaceArea FROM city JOIN country ON city.countryCode = country.Code WHERE city.population < 1000;
  

（5）、别名应用

• 表别名
  在表名后面添加as [别名]，作用范围是全局

  • 语法
    [表名] as [别名]

  • 示例

    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
    

• 列别名
  在列名后面添加as [别名]，列别名可以在having和order by中调用

  • 语法
    [列名] as [别名]

  • 示例

    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
    

2.2、information_schema基础应用

​ information_schema数据库是 MySQL自带的信息数据库，information_schema用于存储数据库元数据，例如：数据库名、表名、存储引擎等信息

2.2.1、information_schema.tables表的常用字段介绍

• TABLE_SCHEMA：表所在的库名
• TABLE_NAME：表名
• ENGINE：存储引擎
• TABLE_ROWS：数据行
• AVG_ROW_LENGTH：平均行长度
• INDEX_LENGTH：索引长度

2.2.2、基础使用示例

# 显示所有的库和表的信息
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 ;

2.2.3、配合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';

2.3、show命令介绍

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

思维导图下载