SQL必知必会


SQL必知必会

笔记目录

  1. 数据库选型

    • NoSQL vs SQL
    • 各数据存储系统的适应场景
    • 构建Mysql开发环境
  2. 数据库建模

    • 业务分析
    • 逻辑设计 范式化 vs 反范式化
    • 物理设计 根据mysql特点进行设计
  3. 数据库访问

    • Mysql客户端
    • 常用GUI工具
    • Mysql驱动
    • 访问故障处理
  4. SQL开发

    • DCL、DDL、DML
    • 常用函数
    • CTE及窗口函数
  5. 实战

  6. SQL优化

    • 分析执行计划
    • 索引和SQL改写
    • 排查性能瓶颈
  7. 事务和高并发

    • 什么是事务
    • 高并发的隐患
    • 事务隔离级别
    • 阻塞和死锁

数据库选型

SQL vs NoSQL

  • SQL

    • MySQL、Oracle、SQLServer、PostGreSQL
    • 特点
      1. 数据结构化存储在二维表中
      2. 支持事务特性
        • 原子性、一致性、隔离性、持久性
      3. 支持使用SQL语言对存储在其中的数据进行操作
    • 适用场景
      • 数据之间存在一定关系,需要关联查询数据的场景
      • 需要事务支持的业务场景
      • 需要使用SQL语言灵活操作数据的场景
  • NoSQL

    • HBase、MongoDB、Redis、Hadoop

    • 特点

      • 存储结构灵活,没有固定结构
      • 对事务的支持比较弱,但对数据的并发处理性能高
      • 大多不使用SQL语言操作数据
    • 使用场景

      • 数据结构不固定的场景
      • 对事务要求不高,但读写并发比较大的场景
      • 对数据的处理操作比较简单的场景

关系型数据库选型原则

  • 数据库使用广泛性
  • 数据库的可扩展性
  • 数据库的安全性和稳定性
  • 数据库所支持的系统
  • 数据库的使用成本

MySQL

  • 使用广泛性

  • 可扩展性

    • 支持基于二进制日志的逻辑复制
    • 存在多种第三方数据库中间件,支持读写分离及分库分表
  • 安全性和稳定性

    • MySQL主从复制集群可达99%的可用性
    • 配合主从复制高可用架构可以达到99.99%的可用性
    • 支持对存储在MySQL的数据进行分级安全控制
  • 支持的系统

    • Linux
    • Windows
  • MySQL的使用成本

    • 社区版免费
    • 使用人员多,方便获取技术支持

环境部署

TODO

数据库建模

数据库结构设计步骤

  1. 业务分析
  2. 逻辑设计
  3. 数据类型
  4. 对象命名
  5. 建立库表

慕课网门户为例

业务分析

我们可以看到慕课网站有供我们学习的课程,课程详情页里有课程列表、讲师信息、问答评论、笔记、评价等,我们来进行一个简单的业务分析

  • 课程属性
    • 主标题、副标题、方向、分类、难度、最新、最热、时长、简介、人数、需知、收获、讲师名、讲师职位、课程图片、综合评分、内容实用、简洁易懂、逻辑清晰
  • 课程列表属性
    • 章名、小结名、说明、小节时长、章节URL、视频格式
  • 讲师属性
    • 讲师昵称、密码、说明、性别、省、市、职位、说明、经验、积分、关注认数、粉丝人数
  • 问答评论属性
    • 类型、标题、内容、关联章节、浏览量、发布时间、用户昵称
  • 笔记属性
    • 用户昵称、关联章节、笔记标题、笔记内容、发布时间
  • 用户属性
    • 用户昵称、密码、说明、性别、省、市、职位、说明、经验、积分、关注人数、粉丝人数
  • 评价属性
    • 用户、课程主标题、内容、综合评分、内容实用、简洁易懂、逻辑清晰、发布时间

逻辑设计

宽表模式
  • 课程属性

    • 主标题、副标题、方向、分类、难度、最新、最热、时长、简介、人数、需知、收获、讲师名、讲师职位、课程图片、综合评分、内容实用、简洁易懂、逻辑清晰

    1586689899382

存在问题

  • 数据冗余、数据更新异常、数据插入异常、数据删除异常

应用场景

  • 配合列存储的数据报表应用
设计范式
  • 第一范式:表中所有字段都是不可再分的
  • 第二范式:表中必须存在业务主键,并且非主键依赖于全部业务主键
  • 第三范式:表中的非主键列之间不能相互依赖
面向对象设计
  • 原课程属性逻辑设计

    主标题、副标题、方向、分类、难度、最新、最热、时长、简介、人数、需知、收获、讲师名、讲师职位、课程图片、综合评分、内容实用、简洁易懂、逻辑清晰

    • 课程表
      • 主标题(业务主键)**、副标题、方向、分类、难度、最新、最热、时长、简介、人数、需知、收获、讲师昵称**、课程图片、综合评分、内容实用、简洁易懂、逻辑清晰
    • 讲师表
      • 讲师昵称、职位
    • 课程方向表
      • 课程方向名称、填加时间
    • 课程分类表
      • 课程分类名称、填加时间
    • 课程难度表
      • 课程难度、填加时间
  • 课程列表属性

    章名、小结名、说明、小节时长、章节URL、视频格式

    • 课程章节表
      • 章名、说明、章节编号
    • 课程章节关联表
      • 课程主标题、课程章节名
    • 课程小节表
      • 小节名称、小节视频url、小节时长、小节编号、视频格式
    • 课程小节关联表
      • 课程主标题、课程章节名、小节名称
  • 讲师属性 + 用户属性

    讲师属性 : 讲师昵称、密码、说明、性别、省、市、职位、说明、经验、积分、关注认数、粉丝人数

    用户属性: 用户昵称、密码、说明、性别、省、市、职位、说明、经验、积分、关注人数、粉丝人数

    • 用户表:用户昵称、密码、说明、性别、省、市、职位、说明、经验、积分、关注人数、粉丝人数、讲师标识
  • 问答评论属性

    • 类型、标题、内容、关联章节、浏览量、发布时间、用户昵称
  • 笔记属性

    • 用户昵称、关联章节、笔记标题、笔记内容、发布时间
  • 用户属性

    • 用户昵称、密码、说明、性别、省、市、职位、说明、经验、积分、关注人数、粉丝人数
  • 评价属性

    • 用户、课程主标题、内容、综合评分、内容实用、简洁易懂、逻辑清晰、发布时间

1586349892919

1586349892919

1586349892919

逻辑设计表总结

1586349892919

1586349892919

1586349892919

1586349892919

范式化设计存在的问题
  • 多表联查影响性能
反范式化设计
  • 空间换时间

物理设计

MySQL常见的存储引擎

  • MYISAM
    • 不支持事务
    • MySQL5.6之前默认引擎,最常见的非事务存储引擎
    • 不推荐 锁 阻塞、影响整体性能
    • 支持全文和空间索引
  • CSV
    • 不支持事务
    • 以CSV格式存储
    • 锁 阻塞、影响整体性能
    • 应用于 不同系统的数据交换
  • Archive
    • 不支持事务
    • 只允许查和新增数据而不允许秀才的非事务型存储引擎
    • 特殊场景(归档、日志)
  • Memory
    • 不支持事务
    • 内存存储
    • 是一种易失性非事务型存储引擎
    • 应用mysql内部
  • INNODB
    • 支持事务
    • 最常用的事务型存储引擎
InnoDB存储引擎特点
  • 事务型存储引擎支持ACID
  • 数据按主键聚集存储
    • 业务主键唯一 加自增主键
  • 支持行级锁及MVCC
    • 对查询的行数据进行加锁
    • MVCC多版本的并发控制
  • 支持Btree和自适应Hash索引
  • 支持全文和空间索引(5.7之后)

根据Innodb特性优化后的表逻辑结构

给字段匹配合适的数据类型

常用的整数类型

常见的浮点类型

常见的时间类型

常用的字符串类型

给字段匹配合适的数据类型

  • 优先选择符合存储数据类型需求的最小数据类型
  • 谨慎使用ENUM、TEXT字符串类型
  • 同财务相关的数值型数据,必须使用decimal类型

为表和列命名
  • 小写字母(命名)和下划线(分割)组成

  • 禁止使用Mysql保留关键字

  • 见名识义、不超过32个字

  • 临时库表以tmp为前缀并以日期为后缀

  • 备份库表,必须以bak为前缀并以日期为后缀

  • 所有存储相同数据的列名和列类型必须一致

最终表设计

访问MySQL

  • 客户端
    • 命令行工具:mysql
    • 图形化管理工具:Navicat、SQLyog
    • MySQL连接器:Connector/ODBC(Excel)、Connector/JDBC(Java)

命令行工具

  1. 连接

    mysql -uroot -p -hlocalhost
  2. 直接执行命令

    mysql -uroot -p -hlocalhost -e"select user()"

SQLyog

安装

TODO

使用

TODO

安装

TODO

使用

TODO

Python 访问 MySQL

  • 环境准备
    • Python3
    • PyMySQL

环境配置和软件安装需自行解决

Python访问MySQL

import pymysql  # mysql 连接驱动
db_host='localhost'
db_user='root'
db_password='mysql123456'
db_database='mysql'
db_port=3306

if __name__ == '__main__':
    conn = pymysql.connect(host=db_host,
                          port=db_port,
                          user=db_user,
                          passwd=db_password,
                          db=db_database)
    sqlstr="""
        select user,host from user
    """
    cursor = conn.cursor()
    cursor.execute(sqlstr)
    result = cursor.fetchall()
    for i in result:
        print userid:%d host:%s"%(i[0],i[1]))
    conn.close()   

Java中访问MySQL

TODO

常见访问故障和处理

SQL语言开发

  • 初始SQL
    • 什么是SQL:一种描述性语言
    • SQL的作用:对存储在RDBMS中的数据进行增删改查操作
    • 常用SQL语言分类:DCL、DDL、DML、TCL

DCL

DCL(Data Control Language)

创建账户

  • 建立数据库账户:create user

    CREATE USER mc_class@'192.168.1.%'
    IDENTIFIED WITH 'mysql_native_password' by '1234567'
    WITH max_user_connections 1; -- 最多一个用户与其建立连接
    help create user; -- 查看帮助文档
    show plugins; -- 通过查看插件的方式查看认证方式
    • mysql_native_password 5.7 版本默认
    • caching_sha2_password 8.0 版本默认

用户授权

  • 对用户授权:grant

    • 常用权限

      • Insert、Delete、Update、Select、Execute(执行存储过程)
      show privileges; --查看权限列表
      GRANT select(user ,host) on mysql.user to mc_class@'192.168.1.%';
      -- 给用户mc_calss mysql.user表上的user和host列表的查询权限
      GRANT select on mysql.user to mc_class@'192.168.1.%';
      GRANT select on mysql.* to mc_class@'192.168.1.%';
    • mysql 8.0 以后用户必须存在才能够授权

      select user,host from mysql.user;
      -- mysql.user user host不同就是不同用户
    • 用户使用grant命令授权必须具有grant option的权限

    • 帮助命令 `\h grant`

权限回收

  • 收回用户权限:revoke

    grant select,delete,insert,update on mysql.* to mc_class@'192.168.1.%'
    revoke delete,insert,update on mysql.* from mc_class@'192.168.1.%'

DDL

DDL(Data Definition language)

  • 建立/修改/删除数据库:create/alter/drop database
  • 建立/修改/删除表:create/alter/drop table
  • 建立/删除索引:create/drop index
  • 清空表:truncate table
  • 重命名表:rename tbale
  • 建立/修改/删除视图:create/alter/drop view

数据库操作

  • 创建数据库

    create database imc_db;

表操作

  • 创建表

      • NOT NULL 才可以使用索引
    • 联合唯一索引:只有两个同时相同时算重复

    • 默认时间

索引

清空表

无法根据日志恢复数据

重命名表

视图操作

TODO

DML

DML(Data Mainpulation language)

  • 新增表中数据:insert into
  • 删除表中数据:delete
  • 修改表中数据:update
  • 查询表中数据:select

insert

  • 确认要把数据插入到那个表中
  • 确认表的数据库结构,哪些列不能为NULL,那些列可以为NULL,对于不能为NULL的列的是否有默认值
  • 确认对应插入列的插入值的清单
use imc_db;

insert into imc_class(class_name) 
values('mysql'),('redis'),('mongodb');
ON DUPLICATE KEY UPDATE
add_time=CURRENT_TIME

create unique index uqx_classname 
on imc_class(classname);

select

  • 首先确定我们要获取的数据存在哪些表中:FROM
  • 其次是确定我们要取表中哪些列: SELECT
  • 确定是否需要对表中数据进行过滤 : WHERE
select 'mysql','java',1+1
select *
from imc_db.imc_class

select class_id,class_name
from imc_db.imc_class

select  title
from imc_course
where title like '%mysql%'

where

  • 比较运算符
    • = 、>、<、>=、<=、<>、!=
    • BETWEEN min and max (包含min 、max )
    • IS NULL、IS NOT NULL
    • like 、 not like (% 表示n个任意字符 _ 表示一个任意字符)
    • in、not in
  • 逻辑运算符
    • AND 、&&
    • OR、||
    • XOR
  • 任何运算符和NULL运算结果都是NULL

联合查询

  • 表与表之间要存在一定的关联关系

  • JOIN

    • INNER JOIN(交集)

      select a.course_id, a.title,b.chapter_name
      from imc_course a 
      join imc_chapter b on b.course_id=a.course_id
  • OUTER JOIN

    • LEFT JOIN

``` mysql
select a.course_id, a.title
from imc_course a 
left join imc_chapter b on b.course_id=a.course_id
where b.course_id IS NULL
```

- RIGHT JOIN

  ![](join2.png)

GROUP BY

HAVING

  • 把结果集按某列分成不同的组,并对分组后的数据进行聚合操作
  • 可以通过可选的Having字句对聚合后的数据进行过滤

聚合函数

  • count(*)/count(col)
  • sum(col)
  • avg(col)
  • max(col)
  • min(col)

ORDER BY

  • 是对查询结果进行排序的最安全方法
  • ASC 、DESC
  • 可以使用select 字句中未出现的列或是函数

Limit

  • 分页
  • 一定和order by字句配合使用
  • limit 起始偏移量,结果集的行数

视图

  • 查询视图同查询普通表

DELETE

  • 确定要删除的数据存储在那张表中 FROM
  • 确定删除数据的过滤条件 WHERE
  • 确认是否只删除有限条数据 ORDER BY…..LIMIT

UPDATE

  • 确定要更新的数据存储在那张表中
  • 确定要更新的列及值
  • 确定更新数据条件
  • 批量更新时 使用order by + limit 配合更新

系统函数

常用日期函数
  • curdate()/curtime()
  • Now()
  • date_format(date,fmt) 按照fmt的格式,对日期date进行格式化 ‘%Y-%m-%d %H-%i-%s’
  • SEC_TO_TIME(seconds)
  • TIME_TO_SEC(time)
  • datediff(date1,date2) 两个日期相差天数
  • date_add(date,interval expr unit) 增加和减少指定时间单元(unit:DAY/HOUR/MINUTES/SECOND)
  • EXTRACT(UNIT FROM DATE)
  • unix_timestamp() 返回unix时间戳
  • FROM_UNIXTIME() 把unix时间戳转换为日期时间
常用字符串函数
  • concat(str1,str2)
  • concat_ws(sep,str1,str2,…..) 合并两个字符串用sep分割
  • char_length 字符个数
  • length 字节个数
  • format(x,d,[,locale])将123456.789 分割成123,456.7890
  • left(str,len)/right(str,len) 从左/右起返回len长度的子字符串
  • substring(str,pos,[len]) 从字符串str的pos位置起返回长度为len的子串
  • substring_index(str,delim,count) 返回字符串str按delim分割的前count个子字符串
  • locate(substr,str) 在字符串str中返回子串substr第一次出现的位置
  • trim([remstr FROM] str) 从字符串str两端删除不需要的字符remstr
其它常用函数
  • Round(x,d) 对数值X进行四舍五入保留D位小数

  • rand() 返回一个在0和1的随机数

  • case when 提供数据流控制

    select user_nick
        ,case when sex=1 then '男'
              when sex=0 then '女'
              else '未知'
         end as '性别'
    from imc_user
  • MD5(str)

SQL 高级特性

  • 公用表表达式CTE(Common Table Expressions)
    • mysql8.0之后的版本可以使用
    • CTE生成一个命名临时表,并且只在查询间有效
    • CTE临时表在一个查询中可以多次引用及自引用
with cte as(
select title,study_cnt,class_id
from imc_course
where study_cnt>2000)

select * from cte
-- CTE递归生成序列

With recursive test as(
select 1 as n
union all
select 1+n from test where n<10)

select * from test

1586497582368

  • 窗口函数

    1586497695891

    1586504298840

TODO

易犯错误

15865044725691586504572458

SQL优化

优化步骤

1586504783751

问题发现渠道

1586504934988

慢查询日志

1586505102591

1586505161169

监控发现问题

1586505640846

分析执行计划

1586505680917

如何获取执行计划

1586505746200

1586505794443

1586505831249

1586505979749

1586568584004

1586568678122

1586568707513

1586569189481

1586570150693

filtered 越高说明性能越好

1586570219143

SQL优化的手段

1586570744004

  • 索引优化

    1586570779477

索引优化

1586571021757

1586571076201

1586571135571

1586571200496

1586571279687

1586571328222

1586571629447

1586571717407

1586571800737

SQL改写优化

1586572071022

1586572111372

事务和高并发

事务

什么是事务?

1586572619048

1586572652566

事务的特性

1586572761847

并发到来的问题

脏读

1586572825717

不可重复读

1586572871364

幻读

1586572922435

事务隔离性

1586573540819

1586573074300

设置隔离级别

1586573121775

1586574079522

阻塞

都会产生

1586574173137

1586574190797

1586574451484

死锁

1586574544469

1586574598679

1586574882083


文章作者: 韩思远
版权声明: 本博客所有文章除特別声明外,均采用 CC BY 4.0 许可协议。转载请注明来源 韩思远 !
评论
 上一篇
JQuery基础 JQuery基础
JQuery基础 一. jQuery 简介什么是 jQuery jQuery 是一个轻量级, 简洁的 JavaScript 库, 是继 Prototype 之后的一个优秀的 JavaScript 库. jQuery 的理念: Write
2020-08-01
下一篇 
SQL基础 SQL基础
SQL基础SQL基础语句SQL 语法数据库表一个数据库通常包含一个或多个表。每个表由一个名字标识(例如“客户”或者“订单”)。表包含带有数据的记录(行)。下面的例子是一个名为 “Persons” 的表: Id LastName Fir
2019-09-05 韩思远
  目录