SQL

MySQL内置功能

1. 连接数据库

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

   示例：

   （福利推荐：阿里云、腾讯云、华为云服务器最新限时优惠活动，云服务器1核2G仅88元/年、2核4G仅698元/3年，点击这里立即抢购>>>）

   1. mysql -u root -p -S /tmp/mysql.sock
   2. mysql -u root -p -h 10.0.0.51 -P3306
   3. -e 免交互执行 sql 语句

      [[email protected] ~]# mysql -uroot -p -e "show databases;"

   4. < 导入数据

      [[email protected] ~]# 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 statusG 查看innoDB引擎详细状态信息
      show binary logs 查看二进制日志的列表信息
      show binlog events in ” 查看二进制日志的事件信息
      show master status ; 查看mysql当前使用二进制日志信息
      show slave statusG 查看从库状态信息
      show relaylog events in ” 查看中继日志的事件信息
      show status like ” 查看数据库整体状态信息

你还在原价购买阿里云、腾讯云、华为云、天翼云产品？那就亏大啦！现在申请成为四大品牌云厂商VIP用户，可以3折优惠价购买云服务器等云产品，并且可享四大云服务商产品终身VIP优惠价，还等什么？赶紧点击下面对应链接免费申请VIP客户吧：

1、点击这里立即申请成为腾讯云VIP客户

2、点击这里立即注册成为天翼云VIP客户

3、点击这里立即申请成为华为云VIP客户

4、点击这里立享阿里云产品终身VIP优惠价

喜欢 (0)赏

[[email protected]]

分享 (0)