Menu

Subscribe

SQL基础教程

本书是畅销书《SQL基础教程》第2版，介绍了关系数据库以及用来操作关系数据库的SQL语言的使用方法。书中通过丰富的图示、大量示例程和详实的操作步骤说明，让读者循序渐进地掌握SQL的基础知识和使用技巧，切实提高编程能力。每章结尾设置有练习题，帮助读者检验对各章内容的理解程度。另外，本书还将重要知识点总结为“法则”，方便读者随时查阅。第2版除了将示例程序更新为对应新版本的DB的SQL之外，还新增了一章，介绍如何从应用程序执行SQL。

书香节技术书打折，从当当买了三本SQL相关的书，MICK的《SQL基础教程》和《SQL进阶教程》，还有一本经典的《SQL从入门到精通》。

《SQL基础教程》这本书难度不高，目录如下，争取能在两周内完成阅读和读书笔记整理。

搭建SQL学习环境

文中使用的数据库环境为PostgreSQL，但是较好的是如果语句存在不一致，在文中会将不同语句均列出来。

下文中的所有案例我均会使用SQL Server中能使用的案例，我们目前主要的使用环境为SQL Server 2012 。

同样的表ALTER语句，在DB2, PostgreSQL和MySQL是这样的: 

ALTER TABLE Product ADD COLUMN Product_Name VARCHAR(100);

ORACLE是这样的: 

ALTER TABLE Product ADD (Product_Name VARCHAR(100));

SQL Server是这样的: 

ALTER TABLE Product ADD Product_Name VARCHAR(100);

数据库和SQL

SQL概要

SQL语句及其种类

DDL(Data Definition Language, 数据定义语言) 

CREATE: 创建数据库和表等对象
DROP: 删除数据库和表等对象
ALTER: 修改数据库和表等对象

DML(Data Maniplation Language, 数据操作语言) 

SELECT: 查询表中的数据
INSERT: 向表中插入新数据
UPDATE: 更新表中的数据
DELETE: 删除表中的数据

DCL(Data Control Language, 数据控制语言) 

COMMIT: 确认对数据库中的数据进行的变更
ROLLBACK: 取消对数据库中的数据进行的变更
GRNAT: 赋予用户操作权限
REVOKE: 取消用户操作权限

实际使用过程中90%均为DML语言

SQL基本书写法则

在实际使用过程中，自己特别喜欢将列名用大写，以后需要纠正这样的写法。

  • 关键字大写
  • 表名首字母大写
  • 其余(列名等)小写

数据库名称、表名和列名可以使用如下三种字符:

  • 半角英文字母
  • 半角数字
  • 下划线(_)

建立用到的数据表并插入数据:

切记在使用中文字符时，数据类型使用NCHAR和NVARCHAR，数值之前要加N 

CREATE TABLE Product
(
  product_id     NCHAR(4)      NOT NULL,
  product_name   NVARCHAR(100) NOT NULL,
  product_type   NVARCHAR(32)  NOT NULL,
  sale_price     INTEGER,
  purchase_price INTEGER,
  regist_date    DATE,
  PRIMARY KEY (product_id)
);

CHAR与VARCHAR的区别在于，CHAR是固定长度字符串，VARCHAR是可变长度字符串。 

INSERT INTO Product VALUES ('0001', N'T恤', N'衣服', 1000, 500, '2009-09-20');
INSERT INTO Product VALUES ('0002', N'打孔器', N'办公用品', 500, 320, '2009-09-11');
INSERT INTO Product VALUES ('0003', N'运动T恤', N'衣服', 4000, 2800, NULL);
INSERT INTO Product VALUES ('0004', N'菜刀', N'厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO Product VALUES ('0005', N'高压锅', N'厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO Product VALUES ('0006', N'叉子', N'厨房用具', 500, NULL, '2009-09-20');
INSERT INTO Product VALUES ('0007', N'擦菜板', N'厨房用具', 880, 790, '2008-04-28');
INSERT INTO Product VALUES ('0008', N'圆珠笔', N'办公用品', 100, NULL, '2009-11-11');

查询基础

SELECT 语句基础

在SELECT中使用DISTINCT可以删除重复行，NULL会被保留。DISTINCT也可以在多列之前使用，此时会将多列数据进行组合，将重复的记录合并为一条。 

-- 单行注释

/* 多行
注释 */

算数运算符和比较运算符

SELECT字句中可以使用常数或表达式

所有包含NULL的计算，结果均为NULL
 有的时候如果我们希望NULL能像0一样参与计算，我们可以通过函数解决这个问题，具体信息会在函数章节中有所提及。

在比较运算符中，如果想要拿到价格为NULL的数据记录，如果使用Price = NULL这种写法，是返回不了结果的，应该使用IS NULL运算符，希望选取结果不是NULL的记录时，应该使用IS NOT NULL运算符。

我们在写不等于时，经常使用 != 来表示，这是不符合标准SQL规定的写法，最好使用<>，但是大多数RDBMS均支持!=写法。 

-- != 不建议使用，建议使用 <>
SELECT * FROM Product WHERE sale_price != 500;

字符串类型的数据原则上按照字典顺序进行排序，不能与数字的大小顺序混淆

如果数据库字符集为拉丁字符，那么SELECT语句中如果有中文，需要加N
 这个问题在SQL Server 2012版本中存在 

SELECT *
FROM Product
WHERE product_type = N'厨房用具' AND sale_price >= 3000;

逻辑运算符

AND运算符的优先级高于OR运算符 

SELECT *
FROM Product
WHERE product_type = N'办公用品'
    AND regist_date = '2009-09-11'
    OR regist_date = '2009-9-20'

以上语句会先判断是办公用品和登记日期满足条件的，然后再选取登记日期满足第二个条件。

聚合与排序

聚合函数

COUNT: 计算表中记录数
SUM: 计算表中数值列总和
AVG: 计算表中数值列均值
MIN: 计算表中任意列最小值
MAX: 计算表中任意列最大值

对于COUNT()函数来说，参数列不同，计算结果也不同

使用SUM()函数计算时，我们发现，NULL列不被包含在计算范围内，使我们有等同为0的感觉。但是实际上，聚合函数会将NULL列排除在外，除了COUNT(*)。

使用AVG()函数计算时，聚合结果仍然会排除NULL值，如果我们需要将NULL值作为0计算，可以在函数、谓词、CASE表达式章节找到相关方法。

MAX()和MIN()适用于所有数据类型，SUM()和AVG()只适用于数值类型的值

使用关键字DISTINCT可以删除聚合结果重复值

对表进行分组

在GROUP BY子句中指定的列称为聚合键或者分组列，GROUP BY就是切分表的刀。

SELECT --> FROM --> WHERE --> GROUP BY，SQL子句的顺序不能变，也不能相互替换。

聚合键中包含NULL时，在结果中会以空行形式表现出来。

GROUP BY与WHERE并用时，SELECT语句的执行顺序为:

FROM --> WHERE --> GROUP BY --> SELECT

可以看出语句执行顺序与书写顺序完全不一致。

使用聚合函数时，SELECT子句中只能存在以下元素:

  • 常数
  • 聚合函数
  • GROUP BY子句中指定的列名(聚合键)

使用GROUP BY子句时，SELECT子句中不能出现聚合键之外的列名

同时，GROUP BY子句中不能使用列的别名，这是由于DBMS解析顺序决定的，SELECT子句的执行顺序在GROUP BY子句之后，在GROUP BY执行时，DBMS并不知道别名指定的列。

PostgreSQL和MYSQL在执行使用别名的语句时能够解析，但是其他DBMS无法成功解析，MSSQL会报无效列名的错误

GROUP BY子句的结果是随机排列的，并无顺序。

只能在SELECT子句和HAVING子句中使用聚合函数。

为聚合结果指定条件

  • 使用聚合函数对表中数据进行操作，为其指定条件的是HAVING子句，并非WHERE子句
  • 聚合函数可以用在SELECT, HAVING和ORDER BY子句之后
  • HAVING子句需要写在GROUP BY子句之后
  • WHERE子句用来指定数据行的条件，HAVING子句用来指定分组的条件 
SELECT
  product_type PT,
  COUNT(*)     CT
FROM Product
GROUP BY product_type
HAVING COUNT(*) = 2;

WHERE子句 --> 指定行对应的条件
 HAVING子句 --> 指定组对应的条件

对查询结果进行排序

ORDER BY子句中书写的列名称为排序键，子句的书写顺序为:

SELECT --> FROM --> WHERE --> GROUP BY --> HAVING --> ORDER BY

排序键中包含NULL时，会在开头或末尾进行汇总，MSSQL会在开头汇总，PostgreSQL会在末尾汇总

DBMS中SELECT子句执行顺序:

FROM --> WHERE --> GROUP BY --> HAVING --> SELECT --> ORDER BY

因此ORDER BY子句要出现在SELECT子句的末尾，对返回结果进行排序。ORDER BY子句中可以出现SELECT子句中未出现的列和聚合函数。 

SELECT
  product_type PT,
  COUNT(*)     CT
FROM Product
GROUP BY product_type
ORDER BY COUNT(*);

数据更新

数据插入(INSERT语句)

INSERT INTO <表名> (列1， 列2， 列3， ...) VALUES (值1， 值2， 值3， ...);

从其他表中拷贝数据可以使用INSERT ... SELECT语句，该INSERT语句中的SELECT语句，可以使用WHERE子句或者GROUP BY子句。

数据删除(DELETE语句)

DELETE FROM <表名> WHERE <条件>；

数据更新(UPDATE语句)

UPDARTE <表名> SET <列名> = <表达式> WHERE <条件>;

复杂查询

视图

使用视图时，数据不会保存在存储设备中，视图实际保存的是SELECT语句 

CREATE VIEW ProductSum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
  FROM Product
 GROUP BY product_type;

视图的两个限制:

  1. 定义视图不能使用ORDER BY子句
  2. 视图更新需要满足如下条件:
    • SELECT子句中未使用DISTINCT
    • FROM子句中只有一张表
    • 未使用GROUP BY子句
    • 未使用HAVING子句

视图和表需要同时进行更新， 因此通过汇总得到的视图无法进行更新

对视图所做的更新实际上是对表进行的更新，当视图和表能够产生唯一对应关系时，此时我更新视图就是更新表。

子查询

子查询就是一次性的视图。

关联子查询

函数、谓词、CASE 表达式

集合运算

SQL高级处理

通过应用程序连接数据库

未完待续

Comments
Write a Comment