《SQL必知必会》专栏学习之语法基础篇

SQL语法都有哪些？该如何操作？mysql与python可以联合使用吗？

《SQL必知必会》专栏学习——基础语法篇

• SQL语言
  • SQL已经无处不在
    • 对于不同职位的人来说都有价值：产品经理、开发人员、运营人员、数据分析师等
    • 对于不同技术来说，掌握SQL也很有价值：OLTP,OLAP,NoSQL，用于XML的SQL，用于JSON的SQL，用于地理位置信息的SQL，用于搜索的SQL，用于时间序列数据的SQL，用于流的SQL等
  • 入门SQL并不难
    • SQL语言的四部分：DDL,DML,DCL,DQL
    • SQL是为数不多的声明性语言，就像学英语语法一样
    • 一旦掌握会让你在日常生活中成就感倍增
    • 在职场上我们的价值取决于别人依赖于我们的程度，而非我们依赖于别人的程度
• DBMS
  • DB,DBS,DBMS区别
    • DB即数据库，它是存储数据的集合，可以理解为多个数据表
    • DBS即数据库系统，它是更大的概念，包括了数据库和数据库管理系统，以及数据库管理人员DBA
    • DBMS即数据库管理系统，它是对多个数据库的管理，DBMS=多个数据库（DB）+管理程序
  • 排名前20的DBMS
    • 关系型数据库绝对是DBMS的主流，其中使用最多的是DBMS分别是Oracle,MySQL和SQLServer
    • NoSQL泛指非关系型数据表
      • 键值型数据库
      • 文档型数据库
      • 搜索引擎
      • 列式数据库
      • 图形数据库
  • SQL阵营与NoSQL阵营
    • NoSQL最早的提出是想远离SQL，但是随着发展却发现越来越离不开SQL
    • NoSQL是对SQL很好的补充
  • SQL阵营
    • Oracle：第一个商用关系型数据库，企业级软件霸主
    • MySQL：开源数据库
    • SQLServer：微软商业关系型数据库
• SQL是如何执行 的
  • Oracle中的SQL执行
    • 流程：语法检查，语义检查，权限检查，共享池检查，优化器，执行器
    • 共享池：Oracle中的术语，包括了库缓存，数据字典缓冲区等
    • 软解析：在共享池中，首先对SQL语言进行Hash运算，然后根据Hash值在库缓存中查找，如果存在SQL语句的执行计划，就直接拿来执行。也就是直接进入到执行器的环节
    • 硬解析：如果没有找到SQL语句和执行计划，就需要自己来创建解析树进行解析，生成执行计划。对应的是优化器这个步骤
  • MySQL中的SQL执行
    • 主要层次：连接层、SQL层、存储引擎层
    • 连接层：客户端和server建立连接，客户端发送SQL到server
    • SQL层：对SQL语句进行查询处理
    • 存储引擎层：负责数据的存储和读取，是与数据库文件打交道
    • 在SQL层中，会进行：解析器->优化器->执行器
    • 存储引擎：InnoDB,MyISAM,Memory,NDB,Archive等
  • 数据库也是一种软件
    • 从软件思维了解执行的流程，比如通过MySQLprofile来分析资源使用情况
    • 不同软件实现方式不同，但都有共性的地方，即：解析器->优化器->执行器
• DDL
  • 基础语法
    • DDL即数据库定义语言，它定义了数据库的结构和数据表的结构
    • 对数据库进行定义：CREATE DATABASE,DROP DATABASE
    • 对数据表进行定义
      • 创建：CREATE TABLE [table_name](字段名 数据类型,...)
      • 修改：ALTER TABLE
    • 可视化管理和设计工具：Navicat
  • 数据表的约束
    • 主键约束：主键起到唯一标识一条记录的作用，不能重复，不能为空
    • 外键约束：外键是确保了表与表之间引用完整性。一个表中的外键对应的另一张表的主键。外键是可以重复的，也可以为空
    • 唯一性约束：唯一性约束表明了字段在表中的数值是唯一的，即使我们已经有了主键，还可以对其他字段进行唯一性约束。
    • NOT NULL约束：字段不应该为空的约束
    • DEFAULT：字段默认值
    • CHECK约束：用来检查特定字段取值范围的有效性
  • 设计数据表的原则
    • 数据表的个数越少越好
    • 数据表中的字段越少越好
    • 数据表中联合主键的字段个数越少越好
    • 使用主键和外键越多越好
    • “三少一多”原则的核心就是“简单可复用”
• SELECT检索数据
  • 基础语法
    • SELECT检索一列，多列，所有列
    • 使用列别名进行检索：AS
    • SELECT查询还可以对常数进行查询
    • 从结果中去掉重复行：DISTINCT
  • 排序检索数据
    • 排序的列名：ORDER BY后面可以有一个或多个列名
    • 排序的顺序：ORDER BY后面可以注明排序规则，ASC代表递增排序，DESC代表递减排序
    • 非选择列排序：ORDER BY是可以使用非选择列进行排序的
    • ORDER BY的位置：ORDER BY通常位于SELECT语句的最后一条子句
  • SELECT查询效率
    • 尽量避免使用SELECT*
    • 在查询过程中，我们可以约束返回结果的数量
    • 不同的DBMS约束返回数量的关键字不同
• WHERE数据过滤
  • 比较运算符
    • 等于，不等于，小于，小于等于，大于，大于等于
    • 在指定的两个数值之间：BETWEEN
    • 为空值：IS NULL
  • 逻辑运算符
    • 如果存在多个WHERE条件子句，就需要使用逻辑运算符
    • 并且：AND
    • 或者：OR
    • 在指定条件范围内：IN
    • 非(否定）：NOT
  • 使用通配符进行过滤
    • LIKE操作符
    • 通配符%：匹配任意字符串出现任意次数
    • 通配符_：匹配单个字符
    • 尽量避免使用通配符，尤其是在LIKE后面就直接跟上通配符的情况
• SQL函数
  • 函数定义
    • 函数可以把我们经常使用的代码封装起来，需要的时候直接调用。SQL函数是在数据上执行的，给我们转换和处理数据提供方便
  • 函数种类
    • 算数函数：ABS,MOD,ROUND,MAX,MIN,SUM,AVG
    • 字符串函数：CONCAT,LENGTH,CHAR_LENGTH,LOWER,UPPER,REPLACE,SUBSTRING
    • 日期函数：CURRENT_DATE,CURRENT_TIME,CURRENT_TIMESTAMP,EXTRACT
    • 转换函数：CAST,COALESCE
  • 为什么使用SQL函数会带来问题
    • 我们直接操作的是DBMS，种类众多
    • DBMS会有自己特定的函数，只有很少的函数是被所有DBMS同时支持的
• 聚集函数&分组统计
  • 聚集函数有哪些
    • COUNT()：总行数
    • MAX()：最大值
    • MIN()：最小值
    • SUM()：求和
    • AVG()：平均值
    • 可以在一条SELECT语句中进行多个聚集函数的查询
    • 可以对数据行中不同的取值进行聚集，使用的是DISTINCT函数。先取不同的数据，然后再使用聚集函数。
  • 如何对数据进行分组
    • GROUP BY：分组，可以使用多个字段进行分组
    • 如果字段为NULL ,也会被列为一个分组
  • 如何使用HAVING过滤分组
    • HAVING：对分组进行条件过滤
    • WHERE针对数据行进行过滤，HAVING针对分组进行过滤
    • 一条SQL查询的顺序：SELECT...FROM...WHERE...GROUP BY...HAVING...ORDER BY...
• 子查询
  • 关联子查询/非关联子查询
    • 关联子查询：子查询的执行与主查询相关，需要执行多次
    • 非关联子查询：子查询的执行与主查询执行无关，只需要执行一次即可
  • 子查询中的关键字
    • EXIST,IN,ANY,ALL,SOME
    • EXIST和IN在某些情况下可以得到相同结果，具体选择哪个效率更高，需要看主表和从表哪个表更大
    • 集合比较中可以使用到IN,ANY,ALL,SOME
    • SOME是ANY的别名，ANY和ALL都需要加上比较操作符
  • 子查询作为计算字段
    • 我们可以把子查询的结果作为主查询的列
• SQL标准&连接表
  • SQL标准
    • SQL92和SQL99是经典的SQL标准，也分别称为SQL-2和SQL-3
  • SQL92中的连接
    • 笛卡尔积：X和Y的笛卡尔积就是X和Y的所有可能组合
    • 等值连接：两张表的等值连接就是用两张表中都存在的列进行等值连接，结果为这两张表的交集部分。
    • 非等值连接：进行多表查询的时候，如果连接多个表的条件是等号时，就是等值连接，其他的运算符连接就是非等值查询
    • 外连接：除了查询满足条件的记录以外，外连接还可以查询某一方不满足条件的记录。
    • 自连接：连接可以对多个表进行操作，也可以对同一个表进行操作，也就是自连接。
  • 案例
• SQL99中如何使用连接
  • SQL92中的连接
    • 交叉连接：笛卡尔积，即table1和table2的所有可能组合
    • 自然连接：NATURAL JOIN,你可以把它理解为是SQL92中的等值连接。实际上它会帮你自动查询两张连接表中所有相同的字段进行等值连接
    • ON连接：用来指定我们想要的连接条件
    • USING连接：可以用USING指定连接的同名字段进行等值连接
    • 外连接：左外连接，右外连接，全外连接
    • 自连接：自连接的原理在SQL92和SQL99中是一样的，只是表述方式不同
  • SQL92和SQL99的区别
    • SQL92中的WHERE和SQL99中的JOIN
    • 多表连接建议使用SQL99标准，因为层次性更强，可读性更强
    • SQL99在SQL92基础上提供了特殊语法，比如NATURAL JOIN和JOIN USING,在实际使用中还是比较常用的
  • 不同DBMS中使用连接需要注意的地方
    • 不是所有的DBMS都支持全外连接
    • Oracle没有表别名AS
    • SQLite的外连接只有左连接
  • 关于连接的性能问题
    • 严格控制连接表的数量
    • 在连接时不要忘记WHERE语句
    • 使用自连接而不是子查询
• 视图
  • 视图操作
    • 视图就是一张虚拟表，它帮我们封装了底层与数据表的接口，我们通过设计视图的字段来定义我们想要从数据表中查询的内容
    • 创建视图：CREATE VIEW
    • 修改视图：ALTER VIEW
    • 删除视图：DROP VIEW
    • 嵌套视图：在视图之上还可以嵌套视图
  • 如何使用视图简化SQL操作
    • 视图的作用：封装SQL查询，提升SQL复用率
    • 利用视图完成复杂的连接
    • 利用视图对数据进行格式化
    • 使用视图和计算字段
  • 视图VS临时表
    • 视图是虚拟表，临时表是真实存在的数据表，但是是临时性的，用完了就会删除
    • 临时表只在当前连接存在，关闭连接后，临时表就会自动释放
• 存储过程
  • 如何创建存储过程
    • 概念：存储过程可以说是由SQL语句和控制流语句构成的语句集合
    • 创建：CREATE PROCEDURE存储过程名称([参数列表])
    • 删除：DROP PROCEDURE
    • 更新：ALTER PROCEDURE
    • 使用DELIMITER定义结束符，Navicat中不需要进行定义
    • 存储过程的三种参数类型：IN,OUT,INOUT
  • 流控制语句
    • BEGIN..END：BEGIN..END中间包含了多个语句，每个语句都是以;号为结束符
    • DECLARE：声明变量
    • SET：赋值语句
    • SELECT..INTO：把我们从数据表中查询的结果存放到变量中
    • IF..THEN..ENDIF：条件判断句
    • CASE：我们在使用CASE的时候有两种方式，分别是CASE语句和CASE表达式
    • LOOP,LEAVE,ITERATE：LOOP是循环语句，LEAVE跳出循环，ITERATE来直接进入下一次循环
    • REPEAT..UNTIL..END REPEAT：循环语句，实现会先执行一次循环，然后在UNTIL中进行表达式的判断，如果满足条件就退出
    • WHILE..DO..END WHILE：循环语句，和REPEAT循环不同的是，我们需要先进行条件判断，如果满足条件就进行循环，如果不满足条件就退出循环
  • 关于存储过程的争议
    • 有些公司对于大型项目要求使用存储过程，而有些则在手册中明确禁止使用存储过程
    • 优点
      • 一次编译多次使用，提升SQL的执行效率
      • 减少开发工作量
      • 安全性强
      • 减少网络传输量
    • 缺点
      • 可移植性差
      • 调试困难
      • 版本管理困难
      • 不适合高并发的场景
• 事务处理
  • 事务的特性
    • 概念：将多个数据库读或写的操作组成一个基本单元，ACID是其四大特性
    • A：原子性，原子的概念就是不可分割，你可以理解它是组成物质的基本单位，也是我们进行数据库操作的基本单元
    • C：一致性，一致性指的就是数据库在进行事务操作后，会由原来的一致状态，变成另一种一致的状态
    • I：隔离性，它指的是每个事务都是彼此独立的，不会受到其他事务的执行影响。也就是说一个事务在commit之前，对其他事务都是不可见的
    • D：持久性，事务提交之后对数据的修改是持久性的。即使在系统出现故障的情况下，数据的修改依然是有效的
  • 事务的控制
    • 事务的两种类型：隐式事务和显式事务
    • MySQL：隐式事务（默认），Oracle：显式事务（默认）
    • START TRANSACTION或者BEGIN，作用是显式的开启一个事务
    • COMMIT，提交事务，当提交事务后，就会对数据库的修改成为永久性的
    • ROLLBACK或者ROLLBACK TO [SAVEPOINT]，回滚事务，就是撤销正在进行的所有没有提交的修改。或者将事务回滚到某个保存点
    • SAVEPOINT，在事务中创建保存点，方便后续针对保存点进行回滚，一个事务中可以存在多个保存点
    • RELEASE SAVEPOINT，删除某个保存点
    • SET TRANSACTION，设置事务的隔离级别
    • 针对MySQL，我们还需要了解autocommit和completition_type的取值
• 事务的隔离级别
  • 三种异常问题
    • 脏读：读到了其他事务还没有提交的数据
    • 不可重复读：对某数据进行读取发现两次读取的结果不同，也就是没有读到相同的内容。这是因为有其他事务对这个数据进行了修改或删除
    • 幻读：事务A根据条件查询得到了N条数据，但是此时事务N更改或增加了M条符合事务A查询条件的数据，这样当事务A再次进行查询的时候发现会有N+M条数据，因此产生了幻读。
  • 四种隔离级别
    • 读未提交，也就是允许读到未提交的数据，这种情况下查询是不会使用锁的，也就是为产生脏读，不可重复读，幻读等情况
    • 读已提交，也就是只能读到已经提交的内容，可以避免脏读的情况方式，但是如果想要避免不可重复读，或者幻读，就需要我们在SQL查询的时候进行显示的加锁
    • 可重复读，保证一个事务在相同查询条件下两次查询得到的数据结果是一致的。避免了“不可重复读”这种异常情况，同时也能避免脏读，但是无法避免幻读。
    • RDBMS默认隔离级别
      • Oracle,SQLServer：读已提交，属于在RDBMS中常见的默认隔离级别
      • MySQ：可重复读
  • 使用MySQL客户端来模拟三种异常
• 游标
  • 什么是游标
    • 在SQL中游标是一种临时的数据库对象，可以指向存储在数据库表中的数据行指针。这里游标充当了指针的作用。
    • 游标是面向过程的编程方式，这与面向集合的编程方式不同。
  • 常用步骤
    • 定义游标：DECLARE cursor_name CURSOR FOR select_statement
    • 打开游标：OPEN cursor_name
    • 从游标中取得数据：FETCH cursor_name INTO var_name
    • 释放指标：DEALLOCATE PREPARE
    • 说明：不同的RDBMS的使用语法略有不同
  • 游标性能
    • 好处：灵活性强，可以解决复杂的数据处理问题，对数据行进行逐行扫描处理
    • 不足：使用游标的过程中会对数据行进行加锁，当业务并发量大的时候，会影响到业务的效率。同时游标是在内存中进行的处理，会消耗系统资源，容易造成内存不足。
    • 建议：通常游标有替代方案的时候，可以采用替代方案，如果实在绕不开有时候还是会用到游标
• 使用python操作MySQL
  • DB API规范
    • 作用：实现多种DBMS的统一访问，切换DBMS的成本低
    • 主要对象：数据库连接对象connection，数据库交互对象cursor，数据库异常类exceptions
    • 使用步骤：引入API模块，与数据库建立连接，执行SQL语句，关闭数据库连接
  • mysql-connector
    • MySQL官方提供的驱动器
    • 安装：pip3 install mysql-connector
    • connection的使用
      • 通过指定host,user,passwd,port等参数来创建数据库连接
      • 关闭数据库连接，使用db.close()
      • 创建游标来操作数据库中的数据，使用db.cursor()
      • 开启事务，使用db.begin()
      • 事务提交及回滚，使用db.commit和db.rollback()
    • cursor的使用
      • 执行数据库查询，使用cursor.execute(query_sql)
      • 取出数据集中的一条数据，使用cursor.fetchone()
      • 取出数据集中的所有行，使用cursor.fetchmany(n)，同样返回一个元组tuples
      • 返回查询结果集中的行数，使用cursor.rowcount，如果没有查询到数据或者还没有查询则结果为-1，否则会返回查询得到的数据行数
      • 关闭游标，使用cursor.close()
  • 对数据表进行增删改查
    • 使用python对数据表进行CRUD操作
    • 在打开数据库连接后，如果不再使用了需要关闭数据库连接，以免造成资源浪费
    • 数据CRUD时，可能会出现异常，需要对异常信息进行捕获
• SQLAIchemy
  • ORM框架
    • 持久化就是将对象数据永久的存储在数据库中，ORM框架就是通过持久化层完成业务逻辑层和数据层之间的转换
    • ORM即对象关系映射，它是RDBMS和业务实体对象之间的一个映射
    • 好处：简单可复用，不用关注底层的数据库访问细节，注意力关注到业务逻辑层
    • 不足：对于复杂的数据查询，采用ORM会力不从心，同时在效率上也会有损耗
  • python中的ORM框架
    • Django：python的WEB应用开发框架，本身是走大而全的方式，通过models实现数据库的增删改查操作
    • SQLALchemy：python中最常用的ORM框架之一，如果你既想支持ORM方式，又想支持原生SQL，SQLALchemy是最好的选择，社区也很活跃
    • peewee，轻量级的ORM框架，API简单，且易于理解和使用，学习成本低
    • ORM与数据库的对应关系
      • 类<=>数据表
      • 类实例对象<=>数据行
      • 属性<=>字段
  • SQLALchemy使用
    • 安装：pip3 install sqlalchemy
    • 初始化数据库连接：engine=create_engine('mysql+mysqlconnector://root:password@localhost:3306/exercise')
    • 查询数据：filter()
    • 增加数据：session.add()，需要使用session.commit()进行提交
    • 删除数据：先进行查询，然后session.delete()，再session.commit()进行提交
    • 修改数据：先进行查询，然后对属性值进行修改，再进行session.commit()即可