SQL基础教程（V2）

1. 聚合函数

聚合函数除了count(*)之外排除null进行计算的

2.group by

- group by后面不能加别名，原因是DBMS内部执行顺序，并没有执行到group by语句.

- group by语句的顺序是随机的

- group by的条件写在having中

3.having

- having子句的三要素：常数、聚合函数、group by中的聚合键

- 聚合键对应的条件可以同时写在where和having中

4.order by

order by 中允许使用别名查询

使用having语句时的select语句的执行顺序

• from->where->group by ->having->select->order by 重点：select 在group
  by子句之后，order by子句之前
• order by 语句可以使用存在于表中，但是不包含在select 子句中的列
• order by语句中可以使用select 子句中的列按照，即按照select子句后面的字段进行，从左到右的顺序（1，2，3。。。）进行排序是所对应的编号，但是介于两个原因不推荐使用
• 代码阅读起来比较难，只看order by的子句无法知道是按照哪一列进行
• 最根本的原因是，在sql-92中已经明确指出，该排序功能将来会被删除，所以将来随着DBMS版本升级，可能原本能够正常使用的sql会突然报错

5.INSERT

• 什么是INSERT
  
  • 创建表-格式
    
    • CREATE TABLE p{production_id char(4) NOT NULL}
• INSERT语句的基本用法
  
  • INSERT INTO<表名>（列1，列2，列3，…）VALUES(值1，值2…)
  • 例子：INSERT INTO Production(production_id,production_name)
    values(1,’衣服’) 按照数据库中的字段要求进行填写
• 列清单的省略
  -清单： 将列值和值用逗号隔开，分别放在括号()中
  
  • 例子中 清单包含两种清单，即
    
    • 列清单(production_id,production_name)
    • 值清单(1,’衣服’)
  • 列数不一致的时候，会报错，无法插入数据，但是支持使用默认值插入，即列清单和值清单应该一一对应
  • 原则上,INSERT语句y一般每次执行一行数据的插入。需要插入多行的时候，通常需要循环执行相应的次数的INSER语句
  • 多行INSERT(oracle除外)，使用与db2,sql,sql sever,PostgreSQL和mysql
    
    • insert into Production(production_id,production_name)
      values(1,’衣服’)
      (2,’鞋子’)
      (3.’袜子’)
      。。。
    • 多行插入的问题：当发现问题时，需要找出是哪行出错的时候，会十分困难
    • oracle语法-插入多行：
      insert into Production(production_id,production_name)
      into Production values(1,’衣服’)
      into Production values (2,’鞋子’)
      into Production values (3.’袜子’) select * from DUAL。。。其中DUAL表是一个没有任何实际意义，不保存任何实际数据，也不能作为INSERT和UPDATE语句的对象
• 插入null
  
  • INSERT插入表中的null数据可以直接写在值清单中，但是要插入的NULL的列一定不能设置为NOT NULL的约束，否则会出过，导致数据插入失败。
  • 插入失败是指：INSERT语句插入的数据无法正常插入到表中，但之前已经插入的数据并不会被破坏。UPDATE语句一样。
• 插入默认值
  
  • 显式方法
    
    • 在VALUES中指定DEFAULE关键字
    • 例子： insert into Production(production_id,production_name)
      into Production values(1,DEFAULT,’衣服’)
  • 隐式方法
    
    • 当创建表的时候就同事赋值默认值，那么插入的时候，那个列没有填的情况下会时候默认值 ，没有设定，值为Null(列没有Not null约束)
    • 可以直接省略列名
• 从其他表中复制数据
  -语法:INSERT …..SELECT
  -例子：insert into productionCopy（prouduct_id,prouduct_name,product_type,sale_price,purchase_price,regist_date）
  select product_id,product_name,product_type,sale_price,purchase_price,regist_date FROM Product
• 讲product的数据拷贝到producitnCopy
• 多种多样的SELECt语句
  
  • insert into productionCopy（prouduct_id,prouduct_name,regist_date）
    select product_id,SUM(product_name),sale_price,regist_date FROM Product
• insert 语句中的select 语句中，可以使用where子句或者group by子句等任何sql语法（重点:使用order by并不会产生任何效果）

6.DELETE

• DROP TABLE语句和DELETE语句
  
  • DROP TABLE是删除整张表，如果删除之后再想插入需要使用creat table语句重新创建一张表
  • DELETE语句会留下表（容器），同时删除
• DELETE语句的基本语法
  
  • delete from 表名
  • 如果忘记了表名，可以直接写delete +表名或者添加多余的列名，执行会报错。
• 指定删除对象的DELETE语句（搜索性DELETE）
  
  • 语法：delete from <表名> where <条件>
  • 注意点：在delete语句中不能使用group by,having 和order by三种语句，原因是，group by和having是从表中选取数据时用来改变抽取数据形式的，而order by是用来指定去的结果显示顺序的，这三种语句在删除表数据中起不到作用。
  • 区分：TRUNCATE<表名> 语法支持Oracle\sql sever\postgresql\mysql\db2,只能删除表中的全部数据，不能通用where子句条件删除部分数据，正因为这用语法不能具体的控制删除对象，所以处理速度比delete块很多，，

7.UPDATE

• UPDATE 的基本语法
  -update <表名> set <列名>=<表达式>
• 指定条件的UPDATE语句（搜索型UPDATE）
  
  • -update <表名> set <列名1> = <表达式1> , <列名2> = <表达式2> where <条件>；
• 使用null进行更新
  -update语句可以将null作为一个值来使用
• 多列更新
  
  • 例子： update product set sale_price=sale_price*10,purchase_price=purchase_price/2 where product_type=’厨房用具’；
  • update product set （sale_price，purchase_price）=（sale_price*10，purchase_price/2） where product_type=’厨房用具’；这种方法在某种dbms中不能使用

8. 事务

• 事务：需要在同一个处理单元中执行的一系列更新处理的集合
• 例子：将运动T恤的单价下调1000日元，T恤衫的销售单价上浮1000日元
• 步骤1：更新的两条局域
  
  • update product set sale_price=sale_price-1000 where product_name=’运动t恤’
  • update product set sale_price=sale_price-1000 where product_name=’运动t恤’
• 步骤2：合并为一个事务
  
  • 步骤2-1：创建事务
    - 事务开始语句：
    DML 语句1
    DML 语句2
    DML 语句3
    ；；；
    事务结束语句(commit或者rollback)
    
    • 事务 开始语句根据不同的DBMS定义
      - sql sever 、postgreSQL——-BEGIN TEANSACTION
      - mysql ———– START TRANSACTION
      - oracle,db2 ———无
• 步骤3：更新商品信息的事务
  
  • SQL SEVER 、postgresql
    
    • BEGIN TEANSACTION;
      update product set sale_price=sale_price-1000 where product_name=’运动t恤’;
      update product set sale_price=sale_price-1000 where product_name=’运动t恤’;
      commit;
  • MYSQL
    
    • START TRANSCATION
      update product set sale_price=sale_price-1000 where product_name=’运动t恤’;
      update product set sale_price=sale_price-1000 where product_name=’运动t恤’;
      commit;
  • ORACLE 、db2
    
    • update product set sale_price=sale_price-1000 where product_name=’运动t恤’;
      update product set sale_price=sale_price-1000 where product_name=’运动t恤’;
      commit;
      
      • <标准sql手册>事务默认开始的时间点为当前时间，没有begin transaction 这样明确的开始标志，mysql中可以设置事务开始时间。
• COMMIT–提交处理
  
  • commit是提交事务包含的全部处理的技术指令，相当于文件处理中覆盖保存。一旦提交，就无法恢复事务开始前的状态了。
  • COMMIT的流程=直线进行
  • 如果由于误操作提交了包含错误更新的事务，就只能回到重新剪标，重新插入数据这样繁琐的步骤中。
• ROLLBACK–取消处理，事务回滚
  
  • rollback是取消事务包含的全部更新处理的结束指令，相当于文件处理中的放弃保存。
  • 区分事务：
    
    • 每条SQL语句就是一个事务(自动提交模式)
    • 查到用户执行COMMIT或者ROLLBACK为止算作一个事务
    • 如果不小心在自动提交模式下执行了delete操作，即使回滚也没用。
      
      • ACID特性-DBMS事务的特性
      • 原子性（Atomicity）-事务结束是，所包含的更新处理要么全部执行，要么完全不执行。
      • 一致性（Consistency）-事务中包含的处理要满足数据库提前设置的约束，例如，如果设置了not null约束的列是不能更新为null的，试图插入违反主键约束的记录都会出错，无法执行。
      • 隔离性(Isolation)-指的是保证不同事务之间互不干扰的特性，该特性保证事务不会互相嵌套。
      • 持久性（Durability）-指的是事务（不论提交还是胡滚）结束后，DBMS能够保证改时间点的数据状态会被保存的特性。
        -保证持久的最常见的方法就是日志，即将事务的执行记录保存到硬盘中等存储介质中，当发生故障，可以通过日志回复到故障发生前的状态。

9.复杂查询

• 视图
  
  • 视图和表
    
    • 从SQL的角度来看视图就是一张表，在select 语句执行并不需要特别在意表和视图的区别
    • 区别：视图创建的时候，并不会讲数据保存到存储设备中，视图本质存储的是select 语句，视图会在内部执行select语句并创建一张临时表。
    • 视图的优点
      
      • 节省存储设备的容量
      • 避免重复书写，提高效率
      • 视图中的数据会随着原表的变化而自动更新，应该讲经常使用的select 语句做成视图
  • 创建视图的方法
    
    • CREATE VIEW 视图名称（<视图列表1>，<视图列表2>） AS

10. 函数、谓词、CASE表达式

• 各种各样的函数
  
  • 函数的种类-种类超过200种
    
    • 算术函数（用来进行述职计算的函数）
    • 字符串函数（用来进行字符串操作的函数）
    • 日期函数（用来进行日期操作的函数）
    • 转换函数（用来转换数据类型和值的函数）
    • 聚合函数（用来进行数据聚合的函数）
  • 算术函数
    
    • • • */
  • 字符串函数
    
    • 字符串1 || 字符串2—这样的语法在sql sever 和mysql中无法使用
      
      • 例子：ab || cd || e=abcde
      • 例子：sql sever 中： ab+cd=abcd
      • 例子：mysql ：CONCAT（a,b,c）=abc
        
        • 如果其中包含null，则得到的结果也是null
    • LENGTH(字符串)–该函数无法在哎sql sever中使用，返回的单位是字节
    • LOWER(字符串)-只能针对英文字母使用，它会将参数中的字符串全部转换为小写
    • REPLACE（对象字符串，替换字符串，替换后的字符串）
    • SUBSTRING-字符串的截取（PostgreSQL/MYSQL专用语法）
    • UPPER-转为大写
  • 日期函数
    
    • CURRENT_DATE–当前日期
      - 例子:select cuuuent_date
    • CURRENT_TIME–当前时间
    • EXTRACT–截取日期元素，截取日期数据的一部分，返回值不是日期类型而是数值类型
  • 转换函数
    
    • 意思是，类型转换和值转换
    • 类型转换-CAST
      - CAST（转换前的值 AS转换后的数据类型)
    • COALESCE-讲null转换为其他值
      
      • 语法：COALESCE(数据1，数据2，数据3。。。)
• 谓词
  
  • 概念
  • LIKE谓词-字符串的部分一致查询
  • BETWEEN谓词-范围查询
  • IS NULL 、IS NOT NULL–判断是否为Null
  • IN谓词-OR的简便用法
    
    • or表示或的关系
    • IN可以使用子查询为其参数
  • 使用子查询作为IN谓词的参数
  • EXIST谓词-判断是否存在满足某种条件的记录
  • exist 是只有一个参数的谓词
  • 使用exist来替换in,exist只关心是否存在某种记录。
• case表达式
  
  • 概念
  • case 表达式的语法
    
    • case when <求职表达式> then <表达式>
      when <求职表达式> then <表达式>
      when <求职表达式> then <表达式>
      。。。
      else<表达式>
      end
  • case表达式的使用方法

11.聚合运算

• 表的加减法
  
  • 什么是集合运算
    
    • 表示记录的集合，具体来说，就是表、视图、查询的执行结果都是记录的集合。
  • 表的加法-UNION
    
    • 例子：SELECT product_id,product_name from product
      union select product_id,product_name from product
  • 集合运算的注意事项
    
    • 集合运算是去掉重复行的
    • 作为运算的对象的记录的列表必须相同
    • 作为运算对象的记录中列的类型必须一致
    • 可以使用任何select语句，但order by子句只能在最后使用一次
  • 包含重复行的集合运算-ALL选项
    
    • • 例子：SELECT product_id,product_name from product
        union all select product_id,product_name from product
  • 选取表中公共部分 -INTERSECT
    
    • SELECT product_id,product_name from product INTERSECT
      union select product_id,product_name from product
      
      • 取两个查询的交集
  • 记录的减法-EXCEPT
    
    • SELECT product_id,product_name from product EXCEPT
      union select product_id,product_name from product
      
      • 1表出去2表中的剩余部分，这个减法的函数要注意被减数和减数的位置不同，得到的记过也不相同。
• 联结
  
  • 联结的概念
    
    • 以列为单位对表进行联结
    • 联结是从列的角度对表进行操作，而union 是行的角度
  • 内联结–INNER JOIN
    
    • 注意点：from子句，from子句后面只有一张表
    • on 子句后面的条件，就是联结键，专门用来指定联结条件的，能起到where相同的作用，需要指定多个键时，同样可以使用and or。在进行内联结时，on是必不可少的，写在from 和where之间
    • 使用联结是，select子句中的列需要按照<表的别名>.<列名>的格式进行书写
  • 外联结–OUTRT JOIN
    
    • 要点1：选取出单张表中全部的信息，内连接值选取出存在于两张表中的数据，外联结，只要数据训在于某一张表当中，就能读取出来，使用外联结会的到固定行数的结果。外联结的结果包含原表中不存在的信息，内连接值包含表内的联结。
    • left 时左边是主表，right时右边是主表
  • 三张以上的表的联结
  • 交叉联结–CROSS JOIN
    
    • 交叉联结就是表的乘法集合
    • 进行交叉联结时无法 使用内连接和外联结中的On子句，这是因为交叉联结是对两张表中的全部记录进行交叉组合。
    • 交叉组合没有应用到实际业务中的原因，是1其结果没有实用价值，2由于其结果行数太多，需要花费大量的运算时间和高性能设备的支持。
  • 联结的特定语法和过时语法
    
    • 过时语法
    • 例子：select sp.shop_id , sp.shop_name,sp.product_id,p.product_name,p.sale_price from shopproduct sp,product p
      where sp.product_id=p.product_id
      and sp.shop_id=’000A’
  • 上面例子的书写方式不推荐，原因有3
    - 使用这样的语法无法马上判断出到底是内联结还是外联结（又或者是其他种类的联结）
    - 由于联结条件写在where子句中，无法在短时间内判断出哪部分是联结条件，哪部分是用来选择记录的限制条件
    - 这种语法已经过程，很可能有一天dbms不能使用
  • 关系除法

12.窗口函数和grouping 运算符

• 窗口函数
  
  • 什么是窗口函数
    
    • 窗口函数可以进行排序，生成序列号等一般的聚合函数无法实现的高级操作
    • 窗口函数也称为OLAP（OnLine Analytical Processing）函数,,意思是对数据库数据进行实时分析处理。例如：市场分析、创建财务报表、创建计划等染厂性商务工作。
    • 除了Mysql其他数据库都支持这种功能
  • 窗口函数的语法
    
    • <窗口函数> OVER {[PARTITON BY<列清单>] order by <排序用例清单>}
    • 能够作为窗口函数使用的函数
      
      • 能够作为窗口函数的聚合函数（SUM\AVG\count\max\min）
      • RANK . DENSE_RANK . ROW_NUMBER等专用窗口函数
  • 语法的基本使用方法-使用RANK函数
  • 无需指定 PARTITON BY
    
    • 窗口函数具有分组和排序两种功能
    • 通过 PARTITON BY分组后的记录集合成为窗口
    • -
  • 专用窗口函数的种类
  • 窗口函数的 适用范围
  • 作为窗口函数使用的聚合函数
  • 计算移动平均
  • 两个order by
• grouping 运算符
  
  • 通知计算出合计值
  • ROLLUP—同时得出和合计和小计
  • GROUPING 函数—让null 更加容易分辨
  • CUBE—–用数据来搭积木
  • GROUPING SETS —取得期望的积木
    ### 13.通过应用程序链接数据库
  • 驱动的种类