MySQL系列:(三)SQL基础应用

admin 2020年11月19日 1,216次浏览

MySQL内置功能

连接数据库

-u  :  用户名
-p  :  密码
-S  :  socket文件
-h  :  主机host
-P  :  端口号
-e  :  免交互执行SQL语句
<   :  恢复数据

# 示例
[root@localhost]# mysql -uroot -p -S /tmp/mysql.sock
[root@localhost]# mysql -uroot -p -h192.168.1.1 -P3306
[root@localhost]# mysql -uroot -p -e "show databasees"
[root@localhost]# mysql -uroot -p123 < /root/test.mysql

内置命令

  • help :打印MySQL帮助
  • ^c : 取消本条命令的执行
  • ^d : 退出mysql
  • \G : 格式化输出
  • source : 恢复备份文件

SQL基础应用

SQL种类

  • DDL: 数据定义语言
  • DCL:数据控制语言
  • DML:数据操作语言
  • DQL:数据查询语言

字符集(charset)

  • 查看字符集

    mysql> show charset;
    
  • 常用字符集
    utf8:3个字节
    utf8mb4(建议):4个字节,支持emoji

校对规则(collation)

  • 查看校对规则(对于英文字符串大小写敏感)

    mysql> show collation;
    其中:带ci说明对大小写敏感,不带ci明对大小写不敏感
    

数据类型

  • 数字类型
    • 整数
      tinyint:极小数数据类型(0 ~ 255)
      int:常规整数(-231 ~ 232-1)
    • 浮点数
  • 字符串类型
    • char:定长字符串类型
      不管字符串长度多长,都一次性分配指定存储空间,未使用空间使用空格填充
    • varchar:变长字符串类型
      每次存储前都要先判断以下长度,然后按需分配存储空间
      会单独申请一个字符长度的空间存储字符长度(少于255,超出255,则需要申请两个字符长度的空间,以此类推)
      • 如何选择两个数据类型
        少于255个字符串长度,定长的列值,选择chart
        多余255个字符串长度,变长的字符串,选择varchar
    • enum:枚举数据类型(对常用字符类型数据进行预定义,不建议数字类型的数据使用该类型)
  • 时间类型
    • datetime:
      格式:YYYY-MM-DD hh:mm:ss[.uuuuuu]
      范围:1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999
      不会自动根据时区调整时间
    • timestamp:
      格式:YYYY-MM-DD hh:mm:ss[.uuuuuu]
      范围:1970-01-01 00:00:00.000000 到 2038-01-19 03:14:07.999999
      会自动根据时区调整时间
  • 二进制类型

DDL数据定义语言应用

数据库操作

  • 库定义的规范

    • 库名使用小写字符
    • 库名不能以数字开头
    • 库名不能是数据库关键字
    • 必须设置字符集
  • 创建库

    • 语法
      CREATE DATABASE [数据库名称] CHARSET [字符集] COLLATE [校对规则]

    • 示例

      CREATE DATABASE test_db CHARSET utf8mb4 COLLATE utf8mb4_bin;
      
  • 删除库

    • 语法
      DROP DATABASE [数据库名称]

    • 示例

      DROP DATABASE test_db;
      
  • 修改库
    说明:修改数据库一般修改数据库的字符集、校对规则等。在修改字符集时,目标字符集一定要是原字符集的超集,否则会出现乱码

    • 语法:
      ALTER DATABASE [数据库名称] CHARSET [字符集]

    • 示例:

      ALTER DATABASE test8 CHARSET utf8mb4;
      

表的操作

  • 建表规范

    • 表名小写
    • 不能以数字开头,不能使用保留字符
    • 表名要见其名知其义
    • 选择合适的数据类型及长度
    • 每个列设置NOT NULL、DEFAULT属性,对于数字类数据默认使用“0”填充,对于字符类数据使用有效字符串填充
    • 每个列设置注释
    • 表必须设置存储引擎和字符集
    • 主键列尽量是无关数字列,最好是自增长
    • enum类型不要保存数字,只能是字符串类型
  • 建表

    • 列属性
      PRIMARY KEY    :主键约束,表中只能有一个,非空且唯一
      NOT NULL     :非空约束,不允许空值
      UNIQUE       :唯一键约束,不允许重复值
      DEFAULT      :默认值,一般配合NOT NULL使用
      UNSIGNED     :无符号,一般配合数字列,非负数
      AUTO_INCREMEN  :自增
      COMMENT     :注释

    • 语法
      CREATE TABLE [[数据库.]表名] (
      [列名] [列属性1 列属性2 ...],
      [列名] [列属性1 列属性2 ...],
      ...
      [列名] [列属性1 列属性2 ...]
      )ENGINE [存储引擎] CHARSET [字符集]

    • 示例

      CREATE TABLE stu(
      id INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT "学号",
      s_name VARCHAR(255) NOT NULL COMMENT "姓名",
      age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT "年龄",
      sex ENUM('男','女','保密') NOT NULL DEFAULT '保密' COMMENT '性别',
      intime DATETIME NOT NULL DEFAULT NOW()) COMMENT '进入时间'
      )ENGINE INNODB CHARSET utf8mb4;
      
    • 创建一个结构与现有表相同的表

      • 语法

      CREATE TABLE [新表] LIKE [源表]

      • 示例

        CREATE TABLE stu_copy LIKE stu;
        
  • 删表

    • 语法
      drop TABLE [表名]

    • 示例

      drop TABLE stu_copy;
      
  • 改表
    注意:在线修改表列时,会造成锁表

    • 在所有列后面添加列

      • 语法

        ALTER TABLE [表名] ADD [列名] [列属性 列属性1 ...]

      • 示例

        ALTER TABLE stu ADD ph_num VARCHAR(15) NOT NULL COMMENT '手机号码';
        
    • 在指定列后面添加列

      • 语法

        ALTER TABLE [表名] ADD [列名] [列属性 列属性1 ...] AFTER [列名]

      • 示例

        ALTER TABLE stu ADD address VARCHAR(255) NOT NULL COMMENT '住址' AFTER s_name;
        
    • 在列最前面添加列

      • 语法

        ALTER TABLE [表名] ADD [列名] [列属性 列属性1 ...] FIRST

      • 示例

        ALTER TABLE stu ADD num VARCHAR(10) NOT NULL COMMENT '序号' FIRST;
        
    • 删除指定列
      注意:删除数据是直接物理删除整列数据,不可恢复

      • 语法

        ALTER TABLE [表名] DROP [列名]

      • 示例

        ALTER TABLE stu DROP num;
        
    • 修改指定列的数据属性(不修改列名)
      注意:修改列属性时,修改后的属性为最终属性,因此在修改属性时需要输入所有列属性

      • 语法

        ALTER TABLE [表名] MODIFY [列名] [列属性 列属性1 ...]

      • 示例

        ALTER TABLE stu MODIFY ph_num INT NOT NULL COMMENT '手机号码';
        
    • 同时修改列名与列属性

      • 语法

        ALTER TABLE [表名] CHANGE [原列名] [新列名] [列属性 列属性1 ...]

      • 示例

        ALTER TABLE stu CHANGE sex gender CHAR(6) NOT NULL DEFAULT '保密' COMMENT '性别';
        
  • 查看表结构

    • 语法

      DESC [表名]

    • 示例

      DESC student;
      

DML数据操作语言应用

insert(插入数据)

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

  • 作用:
    插入数据

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

  • 示例

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

update(修改数据)

  • 作用
    修改表中数据

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

  • 示例

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

delete(删除数据)

  • 作用
    删除一行或多行数据

  • 按条件删除

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

    • 示例

      DELETE FROM stu WHERE id = 4;
      
  • 删除全部数据

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

    • 示例

      DELETE FROM stu
      truncate table stu;
      # 二者间的区别:
      delete: 属于DML操作, 是逻辑性质删除,逐行进行删除,速度慢.
      truncate: 属于DDL操作,对与表段中的数据页进行清空,速度快.
      
  • 伪删除
    说明:用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;
      

DQL数据查询语言应用

select(查询语句)

  • select单独使用

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

    • 总体语法
      select [列] from [表] where [条件] group by [条件] having [条件] order by [条件] limit [条件]

    • select配合from子句使用

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

      • 示例

        # 查询表中所有信息
        SELECT * FROM city;
        
    • 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%';
        
    • 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;
          
    • select配合having子句应用

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

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

      • 示例

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

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

      • 示例

        # 统计所有国家人口总数并显示总人口大于5KW的国家 ,并按照从大到小排列
        SELECT countrycode,SUM(Population) FROM city GROUP BY CountryCode HAVING SUM(Population)>50000000 ORDER BY SUM(Population) DESC;
        
    • 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;
        
    • unionunion all子句

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

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

      • 示例

        # 查询中国和美国的城市信息 
        SELECT * FROM city WHERE Countrycode='CHN' UNION ALL SELECT * FROM city WHERE Countrycode='USA';
        
  • 多表链接查询(内连接)

    • 作用
      单表数据不能满足查询需求时,需使用多表连接查询

    • 语法要求

      • 多表操作的关键点是找到多张表之间的关联条件列
      • 列书写时,必须是:表名.列
      • 所有涉及到的查询列都放到SELECT后
      • 表间的关联条件写在ON后面
      • 将所有的过滤、分组、排序的条件按顺序写在ON后面
    • 单表连接语法
      SELECT [表1.列],[表2.列],... FROM [表1] JOIN [表2] ON [表1.列] = [表2.列] WHERE [过滤条件]

    • 多表连接语法
      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;
      
    • 别名应用

      • 表别名
        在表名后面添加as [别名],作用范围是全局
        • 语法
          [表名] as [别名]
      • 列别名
        在列名后面添加as [别名],列别名可以在having和order by中调用
        • 语法
          [列名] as [别名]

information_schema基础应用

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

  • information_schema.tables表的常用字段介绍

    • TABLE_SCHEMA:表所在的库名
    • TABLE_NAME:表名
    • ENGINE:存储引擎
    • TABLE_ROWS:数据行
    • AVG_ROW_LENGTH:平均行长度
    • INDEX_LENGTH:索引长度
  • 基础使用示例

    # 显示所有的库和表的信息
    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 databasees
  • 查看建库使用的命令
    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 [数据库名]