MySQL系列:(七)MySQL数据增、删、改、查

admin 2021年01月04日 1,123次浏览

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 [数据库名]

思维导图下载