SQL

MySQL内置功能

1. 连接数据库

   • -u
   • -p
   • -S
   • -h
   • -P
   • -e
   • <

   示例：

   1. mysql -u root -p -S /tmp/mysql.sock

   2. mysql -u root -p -h 10.0.0.51 -P3306

   3. -e 免交互执行 sql 语句

      [root@db01 ~]# mysql -uroot -p -e "show databases;"

   4. < 导入数据

      [root@db01 ~]# mysql -uroot -p123 /root/world.sql

2. 内置命令

   • help 帮助
   • \c ctrl+c 结束上个命令运行
   • \q quit exit ctrl+d 退出
   • \G 竖行显示
   • source 恢复备份文件

SQL 基础应用

1. 介绍

   结构化的查询语言

   关系型数据库通用的命令

   遵循SQL92标准（SQL_MODE）

2. 常用种类

   DDL 数据定义语言

   DCL 数据控制语言

   DML 数据操作语言

   DQL 数据查询语言

3. 数据库的逻辑结构

   • 库
     • 库名
     • 库属性(字符集，排序规则)
   • 表
     • 表名
     • 表属性(存储引擎类型，字符集，排序规则)
     • 列名
     • 列属性(数据类型，约束，其他属性)
     • 数据行

4. 字符集(charset)

   查看支持字符集 show charset

   • utf8 三个字符
   • utfmb4 四个字符(支持emoji)

5. 排序规则(collation)

   查看排序规则 show collation

   英文字符串的大小写不敏感

   • utf8mb4_general_ci 大小写不敏感
   • utf8mb4_bin 大小写敏感(存拼音,日文)

6. 数据类型

   • 数字：tinyint int

   • 字符串:

     • char(100)

       定长字符串类型,不管字符串长度多长,都立即分配100个字符长度的存储空间,未占满的空间使用"空格"填充

     • varchar(100)

       变长字符串类型,每次存储数据之前,都要先判断一下长度,按需分配此盘空间.

       会单独申请一个字符长度的空间存储字符长度(少于255,如果超过255以上,会占用两个存储空间)

     如何选择这两个数据类型?

     1. 少于255个字符串长度,定长的列值,选择char

     2. 多于255字符长度,变长的字符串,可以选择varchar

   • 枚举

     • 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

   • 二进制

DDL

1. 库的定义

   • 创建数据库

     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;

2. 库定义规范

   • 库名使用小写字符
   • 库名不能以数字开头
   • 不能为数据库内部关键字
   • 必须设置字符集

3. 表的定义

   • 建表

     表名, 列名, 列属性, 表属性

   • 列属性

     • 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;
   

4. 建表规范

   1. 表名小写字母,不能数字开头

   2. 不能是保留字符,使用和业务有关的表名

   3. 选择合适的数据类型及长度

   4. 每个列设置 NOT NULL + DEFAULT ;对于数据0填充,对于字符使用有效字符串填充

   5. 每个列设置注释

   6. 表必须设置存储引擎和字符集

   7. 主键列尽量是无关列数字列,最好是自增长

   8. enum类型不要保存数字,只能是字符串类型

5. 查询表信息

   SHOW TABLES;
   SHOW CREATE TABLE stu;
   DESC stu;

6. 创建一个表结构一样的表

   CREATE TABLE test LIKE stu;

7. 删表(不代表生产操作)

   DROP TABLE test;

8. 修改

   • 在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

1. DQL介绍

   • SELECT
   • SHOW

2. SELECT 语句的应用

   1. 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();

   2. SELECT通用语法表(单表)

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

   3. 学习环境说明

      • world 数据库

        • city 城市表
        • country 国家表
        • countrylanguage 语言表

      • city表结构

        • ID 城市序号
        • name 城市名代号
        • countrycode 国家
        • district 区域
        • population 人口

   4. SELECT 配合 FROM 子句使用

      SELECT 列 from 表

      示例：

      • 查询表中所有的信息(生产中几乎是没有这种需求的)

        USE world ;
        SELECT  id,NAME ,countrycode ,district,population   FROM  city;
        
        或者
        
        SELECT  *   FROM city;

      • 查询表中 name 和population的值

        SELECT  NAME ,population   FROM  city;

   5. 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');

   6. 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 ;

   7. ORDER BY

      统计所有国家的总人口数量,将总人口数大于5000w的过滤出来,并且按照从大到小顺序排列

        SELECT countrycode,SUM(population) FROM city
        GROUP BY countrycode
        HAVING SUM(population)>50000000
        ORDER BY SUM(population) DESC ;

   8. 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行

   9. 小结

        select disctrict , count(name) from    city  
        where countrycode='CHN'
        group by  district 
        having  count(name) >10
        order by  count(name) desc 
        limit 3;

   10. 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 会做去重操作

   11. 练习题

       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;

3. 多表连接查询(内连接)

   1. 作用

      单表数据不能满足查询需求时

      查询世界上小于100人的城市,所在的国家名,国土面积,城市名,人口数

      SELECT  countrycode,NAME,population FROM city WHERE population<100;
      PCN     Adamstown   42 
      
      SELECT NAME ,SurfaceArea FROM country WHERE CODE='PCN';

   2. 多表连接基本语法

      student ：学生表
      ===============
      sno：    学号
      sname：学生姓名
      sage： 学生年龄
      ssex： 学生性别
      
      teacher ：教师表
      ================
      tno：     教师编号
      tname：教师名字
      
      course ：课程表
      ===============
      cno：  课程编号
      cname：课程名字
      tno：  教师编号
      
      score  ：成绩表
      ==============
      sno：  学号
      cno：  课程编号
      score：成绩

   3. 多表连接例子

      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;

         列别名（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;

4. 扩展类内容-元数据获取

   1. 元数据介绍及获取

   元数据是存储在"基表"中。

   通过专用的DDL语句，DCL语句进行修改

   通过专用视图和命令进行元数据的查询

   information_schema中保存了大量元数据查询的试图

   show 命令是封装好功能，提供元数据查询基础功能

   2. information_schema的基本应用

   tables 视图的应用

      use information_schema;
      mysql> desc tables;

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

   3. 示例

      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 ;

   4. 配合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';

   5. 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 '' 查看数据库整体状态信息