常用的sql分类

  1. DDL：数据定义语言
  2. DCL：数据控制语言
  3. DML：数据操作语言
  4. DQL：数据的查询语言

数值类型

  1. tinyint  ： -128~127
  2. int       ：-2^31~2^31-1（10位长度，超过的一般都放在字符类型了）
  3. 说明：手机号是无法存储到int的。一般是使用char类型来存储收集号

SQL语法 - 图1

字符类型

  1. char(11)    立即分配11个字符长度的存储空间，如果存不满，空格填充。
  2. varchar(11)    在存储字符串时，自动判断字符长度，按需分配存储空间，最大字符长度11个
  3. varchar会单独申请一个字符长度的空间存字符的长度（255以上会占用2个字符空间）
  5. enum('bj','tj','sh')    枚举类型，存放的是下标，适合已知的固定值列表，可以很大程度的优化我们的索引结构。

SQL语法 - 图2

时间类型

  1. DATETIME 
  2. 范围为从 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999
  3. TIMESTAMP 
  4. 1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999（时间戳）

SQL语法 - 图3

列属性

  1. 约束(一般建表时添加):
  2. primary key    主键约束，设置为主键的列，此列的值必须非空且唯一，主键在一个表中只能有一个，但是可以有多个列一起构成。
  3. not null    非空约束，列值不能为空，也是表设计的规范，尽可能将所有的列设置为非空。可以设置默认值为0。
  4. unique key    唯一键，列值不能重复。
  5. unsigned    无符号，针对数字列，非负数。
  7. 其他属性:
  8. key    索引，可以在某列上建立索引，来优化查询,一般是根据需要后添加。
  9. default    默认值，列中，没有录入值时，会自动使用default的值填充。
  10. auto_increment    自增长，针对数字列，顺序的自动填充数据（默认是从1开始，将来可以设定起始点和偏移量）。
  11. comment    注释。

字符集

  1. utf8    占用3个字节    
  2. utf8mb4    一般常用的，占用4个字节，支持emoji
  4. utf8mb4_general_ci    大小写不敏感
  5. utf8mb4_bin    大小写敏感

DDL应用(数据定义语言)

创建数据库

  1. create database school;
  2. create schema sch;
  3. show charset;
  4. show collation;
  5. CREATE DATABASE test CHARSET utf8;
  6. create database xyz charset utf8mb4 collate utf8mb4_bin;
  8. 建库规范：
  9. 1.库名不能有大写字母   
  10. 2.建库要加字符集         
  11. 3.库名不能有数字开头
  12. 4.库名不能是数据库内部的关键字

删除数据库（生产禁用）

  1. mysql> drop database oldboy;

修改数据库

  1. SHOW CREATE DATABASE school;
  2. ALTER DATABASE school  CHARSET utf8;
  3. 注意：修改字符集，修改后的字符集一定是原字符集的严格超集

查询数据库

  1. show databases；
  2. show create database oldboy；

创建表

  1. use 库名;
  2. create table stu(
  3. 列1  属性（数据类型、约束、其他属性） ，
  4. 列2  属性，
  5. 列3  属性
  6. )
  8. use school;
  9. CREATE TABLE stu(
  10. id      INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
  11. sname   VARCHAR(255) NOT NULL COMMENT '姓名',
  12. sage    TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄',
  13. sgender ENUM('m','f','n') NOT NULL DEFAULT 'n' COMMENT '性别' ,
  14. sfz     CHAR(18) NOT NULL UNIQUE  COMMENT '身份证',
  15. intime  TIMESTAMP NOT NULL DEFAULT NOW() COMMENT '入学时间'
  16. ) ENGINE=INNODB CHARSET=utf8 COMMENT '学生表';
  18. 建表规范：
  19. 1. 表名小写
  20. 2. 不能是数字开头
  21. 3. 注意字符集和存储引擎
  22. 4. 表名和业务有关
  23. 5. 选择合适的数据类型
  24. 6. 每个列都要有注释
  25. 7. 每个列设置为非空，无法保证非空，用0来填充。

删除表（生产禁用）

  1. drop table t1;

修改表

生产中 alter 的话，数据量大的情况下会锁表，所以需要借助 ps-osc 工具 或者手动 copy 一个表 在copy表做完操作给替换掉原来的表

  1. 在stu表中添加qq列
  2. DESC stu;
  3. ALTER TABLE stu ADD qq VARCHAR(20) NOT NULL UNIQUE COMMENT 'qq号';
  5. 在sname后加微信列
  6. ALTER TABLE stu ADD wechat VARCHAR(64) NOT NULL UNIQUE  COMMENT '微信号' AFTER sname ;
  8. 在id列前加一个新列num
  9. ALTER TABLE stu ADD num INT NOT NULL COMMENT '数字' FIRST;
  10. DESC stu;
  12. 删掉列(危险)
  13. ALTER TABLE stu DROP num;
  15. 修改sname数据类型的属性
  16. ALTER TABLE stu MODIFY sname VARCHAR(128)  NOT NULL ;
  18. 将sgender 改为 sg 数据类型改为 CHAR 类型
  19. ALTER TABLE stu CHANGE sgender sg CHAR(1) NOT NULL DEFAULT 'n' ;
  20. DESC stu;

创建一个表结构一样的表

  1. create table test like stu;

DQL应用（查询表）

  1. --快速构建数据库多表逻辑关系：
  2. ER图
  4. --查询表结构：
  5. desc city;
  7. --查询建表信息：
  8. show create table name;
  10. --单独使用：
  11. select variables like '%server%';    #模糊查询关键字的命令有哪些
  12. select @@port;
  13. select @@server_id;
  14. select @@basedir;
  16. --跟函数：
  17. select now();
  18. select databases();

select 单表

查询数据内容

  1. 单表语法：
  2. select 列 from 表 where 条件 group by 条件 having 条件 order by 条件 limit 行数;
  4. group by 常用函数：
  5. MAX()    列的最大值
  6. MIN()    列的最小值
  7. AVG()    列的平均值
  8. COUNT()    列的总行数
  9. SUM()    列值之和
  10. GROUP_CONCAT()    聚合函数，将列转成行，多个值转换到一行上
  11. CONCAT()    将列的值拼接，拼接字符串
  13. group by 条件        #用来把条件值唯一化，过滤掉重复的
  14. having 条件        #等同于where，必须在group by之后
  15. order by 条件        #将条件列从小到大排序，条件后边加上desc是从大到小排序
  16. limit 行数        #用于限制查询结果的行数，limit 3,5 跳过前三行，从第四行开始，显示出来5行
  1. --查询limit表的前5列
  2. select * from city limit 5;    
  4. ##select 配合 where
  5. --具体查询
  6. select name from city where countrycode='CHN';
  7. select name from city where countrycode='CHN' and population > 1000;
  9. --模糊查询
  10. select * from city where countrycode like 'ch%';
  11. 前后后加 % 极大影响性能，因为不走索引，如果数据业务中有大量需求的，用ElasticSearch来替代
  13. ##select 配合 group by + 聚合应用
  14. --统计每个国家，城市的个数
  15. select countrycode,count(id) from city group by countrycode;
  16. --统计每个国家的总人口数
  17. select countrycode,sum(population) from city group by coutrycode;
  18. --统计每个国家 省的个数
  19. select coutrycode,count(distinct district) from city group by countrycode;
  20. --统计中国 每个省的总人口数
  21. select district,sum(population) from city where coutrycode='CHN' group by district;
  22. --统计中国 每个省份下的城市名字列表
  23. select district,group_concat(name) from city where countrycode='CHN' group by district;
  24. select concat(district,":",group_concat(name)) from city where countrycode='CHN' group by district;
  26. ##select 配合 having
  27. --统计所有国家总人口数量，将总人口大于1亿的过滤出来
  28. select countrycode,sum(population) from city group by coutrycode hving sum(population)>100000000;
  30. ##select 配合 order by(默认从小到大，从大到小后边加 desc)
  31. --统计所有国家总人口数量，将总人口大于50w的排序过滤出来
  32. select countrycode,sum(population) from city group by coutrycode hving sum(population)>500000 order by sum(population) desc;
  34. ##select 配合 limit 
  35. ##limit 3,4 跳过前三行，从第四行开始，显示出来4行
  36. --统计所有国家总人口数量，将总人口大于50w的排序过滤出来,只显示前三行
  37. select countrycode,sum(population) from city group by coutrycode hving sum(population)>500000 order by sum(population) desc limit 3;
  39. --统计所有国家总人口数量，将总人口大于50w的排序过滤出来,显示第4到6行
  40. select countrycode,sum(population) from city group by coutrycode hving sum(population)>500000 order by sum(population) desc limit 3,3;
  42. ##union 和 union all（多个结果集合并）
  43. union 会做去重
  44. union all 不会做去重
  45. select * from city where countrycode='CHN' union all select * from city where countrycode='USA';

select 多表

查询多表数据内容

最重要的是找到多张表之间的关联条件列

列书写格式：表名.列名

小技巧：先找好涉及到的所有表 from join on 列出来以后，再写开头select查询信息，最后拼接过滤条件

注意：为性能考虑，驱动表（from后的表）一定要选择数据行最少的表，后续的所有关联列尽量是主键或者唯一键（一般表设计的时候考虑的），至少要建立一个索引

  1. 多表语法：    多表查询中join 和on 是成对出现的  而且不能跨表只能通过关联表 A和B B和C 不能A和C
  2. select 列 
  3. from 表1 
  4. join 表2 
  5. on 表1条件 = 表2条件 
  6. join 表3
  7. on 表2条件 = 表3条件
  8. where 条件 group by 条件 having 条件 order by 条件 limit
  1. --统计zhang3,学习了几门课
  2. select student.name,count(sc.cno) 
  3. from student join sc 
  4. on student.sno = sc.sno 
  5. where student.name="zhang3";
  7. --查询zhang3,学习的课程名称有哪些?
  8. select student.name,group_concat(course.cname)
  9. from student join sc 
  10. on student.sno=sc.sno 
  11. join course 
  12. on course.cno=sc.cno
  13. where student.name="zhang3"
  14. group by student.name;
  16. --查询oldguo老师教的学生名和个数
  17. select teacher.name,group_concat(student.name),count(student.name)
  18. from teacher join course
  19. on teacher.tno = course.tno
  20. join sc
  21. on sc.cno = course.cno
  22. join student
  23. on student.sno = sc.sno
  24. where teacher.name="oldguo"
  25. group by teacher.name;
  27. --查询o1dguo所教课程的平均分数
  28. select teacher.name,AVG(sc.score)
  29. from teacher join course
  30. on course.tno = teacher.tno
  31. join sc
  32. on sc.cno = course.cno
  33. where teacher.name='oldguo'
  34. group by sc.cno;
  36. --每位老师所教课程的平均分,并按平均分排序
  37. select teacher.name,course.name,AVG(sc.score)
  38. from teacher join course
  39. on course.tno = teacher.tno
  40. join sc
  41. on sc.cno = course.cno
  42. group by teacher.name,course.name;
  44. --查询o1dguo所教的不及格的学生姓名
  45. select teacher.name,student.name,sc.score
  46. from teacher join course
  47. on teacher.tno = course.tno
  48. join sc
  49. on sc.cno = course.cno
  50. join student
  51. on student.sno = sc.sno
  52. where teacher.name="oldguo" and sc.score < 60;
  54. --查询所有老师所教学生不及格的信息
  55. select teacher.name,group_concat(concat(student.name,": ",sc.score)) as "不及格的"
  56. from teacher join course
  57. on teacher.tno = course.tno
  58. join sc
  59. on sc.cno = course.cno
  60. join student
  61. on student.sno = sc.sno
  62. where sc.score < 60
  63. group by teacher.name;

查询表后拼接生成sql语句，保存sql文件

  1. into outfile 的使用
  2. mysql> select concat("alter table ",table_schema," ",table_name," discard tablespace;") from information_schema.tables where table_schema='world' into outfile "/tmp/discard.sql"
  4. #整库导出一般是csv格式，也就是能用excel直接解析看的
  5. 导出的路径需要在 my.cnf 指定安全数据限制
  6. secure-file-priv=

as 别名

表别名：将from或者join的表名可以定义一个值，在查询语句中涉及到该表名的可以用定义的值代替

列别名：将列名定义一个值，主要用于结果展示列名，在 order by和 having中可以调用

  1. --表别名
  2. select t.name,group_concat(concat(st.name,": ",sc.score))
  3. from teacher as t
  4. join course as c
  5. on t.tno = c.tno
  6. join sc
  7. on sc.cno = c.cno
  8. join student as st
  9. on st.sno = sc.sno
  10. where sc.score < 60
  11. group by t.name;
  13. --列别名
  14. select t.name as “老师”,group_concat(concat(st.name,": ",sc.score)) as "不及格的"
  15. from teacher as t
  16. join course as c
  17. on t.tno = c.tno
  18. join sc
  19. on sc.cno = c.cno
  20. join student as st
  21. on st.sno = sc.sno
  22. where sc.score < 60
  23. group by t.name;

元数据获取（information_schema）

元数据是存储在“基表”中的

通过专用的DDL或者DCL语句进行元数据查询

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

inofrmation_schema中保存了大量的元数据查询的视图

  1. information_schema下table视图：
  2. TABLE_SCHEMA    表所在的行
  3. TABLE_NAME        表名
  4. ENGINE            存储引擎
  5. TABLE_ROWS        数据行
  6. AVG_ROW_LENGTH    平均行长度
  7. INDEX_LENGTH    索引长度
  9. 表的数据量=平均行长度*数据行+索引长度(默认是字节，/1024是KB)
  1. --查询所有的库和表的信息
  2. select table_schema,table_name from information_schema.tables;
  4. --对table_schema做过滤，并展示出所有的对应结果；group by 的使用
  5. select table_schema,group_concat(table_name) from information_schema.tables group by table_schema;
  7. --查询所有innodb引擎的表
  8. select table_schema,table_name from information_schema.tables where ENGINE="innodb";
  10. --统计world库中city表的数据量总大小（默认是字节，/1024是KB）
  11. select table_schema,TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH from information_schema.tables where table_schema="world" and table_name="city";
  12. --表的数据量=平均行长度*行数+索引长度
  14. --统计每个库的数据量大小，并按从大到小排序
  15. select table_schema,(sum(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH))/1024 as kb 
  16. from information_schema.tables 
  17. GROUP BY table_schema 
  18. ORDER BY kb desc;
  20. --concat拼接做数据库分库分表备份语句生成
  21. select 
  22. concat("mysqldump -uroot -p123 ",table_schema," ",table_name," > /bak/",table_schema,"_",table_name,".sql")
  23. from information_schema.tables;

show

  1. show databases;                查看数据库名
  2. show tables;                查看表名
  3. show create database xx;    查看建库语句
  4. show create table xx;        查看建表语句
  5. show processlist;            查看所有用户连接情况
  6. show charset;                查看支持的字符集
  7. show collation;                查看支持的校队规则
  8. show grants for xx;            查看用户的权限信息
  9. show variables like '%xx%';    查看参数信息
  10. show engines;                查看所支持的存储引擎类型
  11. show index from xxx;        查看表的索引信息
  12. show engine innodb status \G;    查看innod引擎的详细状态信息
  13. show binary logs;            查看二进制日志的列表信息
  14. show binlog events in 'xx';    查看二进制日志的事件信息
  15. show master status;            查看mysql当前使用的二进制日志信息
  16. show slave status \G;        查看从库的状态信息
  17. show relaylog events in 'xx';    查看中继日志的事件信息
  18. show status like 'xx';        查看数据库整体状态信息

常见问题

  1. --group by 操作中 sql_mode 报错 only_full_group_by
  2. ##出现原因：
  3. 1、在mysql 5.7 版本中 sql_mode 自带的严谨模式，在5.6 8.0版本中没有
  4. 2、在有 group by 的 select 语句中，select后的条件列(非主键列)，要么是group by后的列，要么是在group_concat()函数中包裹
  5. ##解决办法：
  6. 查询sql_mode默认值    mysql> select @@sql_mode;
  7. 在mysql主配置文件中(/etc/my.cnf)将mysqld服务器端的配置里定义sql_mode的默认值
  8. sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

mysql的日志及增量恢复

二进制日志（binary log）

主要是用来记录Mysql内部的增删改等对数据库有更新的记录。

日志存放位置：/application/mysql/data/

查询binlog操作记录：mysqlbinlog mysql-bin.00020

  1. 配置文件：
  2.     /etc/my.cnf下mysqld区间里面加入：log-bin=mysql.bin
  3. 主要参数：
  4. mysql> show variables like '%log_bin%';
  5.     +---------------------------------+------------------------------------------+
  6.     | Variable_name                   | Value                                    |
  7.     +---------------------------------+------------------------------------------+
  8.     | log_bin                         | ON             记录binlog                 |
  9.     | sql_log_bin                     | ON              临时不记录binlog           |
  10.     +---------------------------------+------------------------------------------+
  11.     6 rows in set (0.00 sec)
  13. 语法：
  14.     mysqlbinlog 日志文件名 > all.sql
  15. 拆库：
  16.     mysqlbinlog -d cao 日志文件名 > cao.sql
  17. 查询有什么操作：
  18.     less cao.sql
  1. ##增量恢复
  2. --指定位置
  3. mysqlbinlog mysql-bin.000020 --start-position=365 --stop-position=456 -r pos.sql
  6. --指定开始时间和结束时间的
  7. mysqlbinlog mysql-bin.00021 --start-datetime='2021-03-19 02:32:50' --stop-datetime='2021-03-19 04:32:50' -t time.sql

错误日志（error log）

记录mysql再启动或者关闭或者运行中遇到的错误信息

  1. 配置文件：
  2.     /etc/my.cnf下mysqld_safe区间里面加入：log-error=/data/3306/mysql_3306.err

查询日志（query log）

  1. ##普通查询日志（general query log）：记录客户端连接信息和执行sql语句的信息（一般不启用）
  2. mysql> show variables like 'general_log%';
  3.     +------------------+--------------------------------------------+
  4.     | Variable_name    | Value                                      |
  5.     +------------------+--------------------------------------------+
  6.     | general_log      | OFF                                        |
  7.     | general_log_file | /usr/local/mysql-5.7.24/data/localhost.log |
  8.     +------------------+--------------------------------------------+
  9.     2 rows in set (0.00 sec)
  10. mysql> set global general_log=on;
  11.     Query OK, 0 rows affected (0.00 sec)
  13. ##慢查询日志（slow query log）：记录查询时间超出指定值（long_query_time）的sql语句
  14. long_query_time=1
  15. log-slow-queries=/data/3306/slow.log
  16. log_queries_not_using_indexes