SQL 语句

Acha
2021-04-11 / 0 评论 / 240 阅读 / 正在检测是否收录...
温馨提示:
本文最后更新于2021年04月11日,已超过1133天没有更新,若内容或图片失效,请留言反馈。

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 命令是封装好功能,提供元数据查询基础功能

    1. information_schema的基本应用

    tables 视图的应用

       use information_schema;
       mysql> desc tables;

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

    1. 示例

      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 ;
    2. 配合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';
    3. 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 '' 查看数据库整体状态信息

0

评论

博主关闭了当前页面的评论