SQL必知必会笔记(1-6)

生成数据库表和数据的SQL文件

第1课：了解SQL

1、数据库基础

• 数据库(database)：保存有组织的数据的容器

数据库软件也称为数据库管理系统(DBMS)；

• 表(table)：某种特定类型数据的结构化清单

• 列：表中的一个字段。所有表都是由一个或多个列组成

• 数据类型：限定可存储在列中的数据的种类

• 行：表中的一条记录

• 主键：一列(或一组列)，其值能够唯一标识表中每一行

应该总是定义主键，主键满足的条件：

• 任意两行都不具有相同的主键值
• 每一行都必须具有一个主键（主键不允许为NULL）
• 主键列中的值不允许修改或更新
• 主键值不能重用（如果某行被删除，它的主键不能赋给以后的新行）

1.2、什么是SQL

• SQL：Structured Query Language(结构化查询语言)

SQL的优点：

• 几乎所有重要的DBMS都支持SQL；
• SQL简单易学；
• SQL可以进行非常复杂和高级的数据库操作；

第2课：检索数据

SELECT语句

SELECT语句用于从一个或多个表中检索信息

关键字不能用作表或列的名字;

检索单个列：

SELECT prod_name FROM products;

从products表中检索名为prod_name的列，FROM关键字指出从那个表中检索数据。

多条SQL语句必须以分号（即";")分割

SQL语句不区分大小写，一般情况下，为了易于阅读和维护，对关键字使用大写，而对列名和表名使用小写；

检索多个列

SELECT prod_id, prod_name, prod_price FROM products;

多个列之间使用逗号分隔

检索所有列

SELECT * FROM products;

一般而言除非确实需要表中的每一列，否则最好不要使用通配符，使用通配符通常会降低检索和应用程序的性能；

检索不同的值

SELECT DISTINCT vend_id FROM products;

• DISTINCT关键字指示数据库返回不同的值；
• DISTINCT关键字作用于所有的列，不仅仅是紧跟其后的那一列；

限制结果

如果查询的数据有很多，只想返回一定的行数，则可以根据不同的DBMS的关键字来实现

• SQL Server和Access： SELECT TOP 5 prod_name FROM products;
• DB2： SELECT prod_name FROM products FETCH FIRST 5 ROWS ONLY;
• Oracle： SELECT prod_name FROM products WHERE ROWNUM <= 5;
• MySQL / MariaDB / PostgreSQL / SQLite： SELEC prod_name FROM products LIMIT 5;

为了得到后面的5行，可以使用OFFSET关键字： SELECT prod_name FROM products LIMIT 5 OFFSET 5;

指示DBMS从OFFSET指定的第5行起行起，查询LIMIT后的 5 行数据；

SQL注释

使用注释的原因:

• 随着SQL语句的变长，复杂性增加，需要添加一些描述性的文字，以便于以后自己和其他人参考；
• 在文件开头添加一些个人的联系方式、程序描述以及一些说明；
• 暂时停止不需要执行的SQL代码；

• 双连字符注释

  SELECT prod_name    -- 这是一条注释
  
  FROM products；

两个连字符之后的便是注释的内容

• 另一种注释:

    # 这是一条注释
    # 这是一条注释
  

• 多行注释

    /*
     SELECT prod_name, vend_id
     FROM products
     LIMIT 5 OFFSET 10;
    */
  

第3课：排序检索数据

排序顺序

子句：SQL由子句构成，一个子句通常由一个关键字加上所提供的数据组成。

ORDER BY 子句可以对检索出的数据进行排序

SELECT prod_name
FROM products
ORDER BY prod_name;

ORDER BY 子句的位置总是在SELECT语句的最后一条，否则会报错。

按多个列排序

SELECT prod_id, prod_price, prod_name 
FROM products 
ORDER BY prod_price, prod_name;

按排列的位置排序

SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY 2, 3;

指定排序方向

SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price DESC, prod_name;

DESC是降序排列(DESCENDING)，默认是升序排列ASC(ASCENDING)；

DESC关键字只能应用到直接位于其前的列名，如果想对多个列进行降序排列，必须对每一列指定DESC关键字；

第4课：过滤数据

只检索所需数据需要指定搜索条件(search criteria)，搜索条件也称为过滤条件(filter condition)；

使用WHERE子句

SELECT prod_name, prod_price
FROM products
WHERE prod_price = 3.49;

WHERE子句操作符

操作符	说 明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
!<	不小于
>	大于
>=	大于等于
!>	不大于
BETWEEN	在指定的两个值之间
IS NULL	为NULL值

并非所有的DBMS都支持以上所有的操作符，如<>就不是所有的DBMS支持；

实例

查询单个值

查询小于价格小于10的商品

SELECT prod_name, prod_price
FROM products
WHERE prod_price < 10
ORDER BY prod_price DESC, prod_name;

限定查询

SELECT vend_id, prod_name
FROM products
WHERE vend_id != 'DLL01';

范围查询

SELECT prod_name, prod_price
FROM products
WHERE prod_price BETWEEN 3 AND 10;

两个值之间需要 AND 关键字隔开

NULL值查询

SELECT cust_name
FROM customer
WHERE cust_email IS NULL;

第5课：高级数据过滤

5.1、组合WHERE子句

SQL允许给出多个WHERE子句，这些子句以 AND 子句或 OR 子句的方式组合：

• AND操作符：AND指示DBMS检索只满足所有给定所有条件的行。

    SELECT prod_id, prod_price, prod_name
    FROM products
    WHERE vend_id = 'DLL01' AND prod_price <= 4
    ORDER BY prod_name DESC;
  

• OR操作符：用来表示检索匹配任一给定条件的行。

    SELECT prod_name, prod_price
    FROM products
    WHERE vend_id = 'DLL01' OR vend_id = 'BRS01'
    ORDER BY prod_price DESC;
  

求值顺序

AND 操作符的优先级高于 OR ，所以在同时使用时建议加上圆括号加以区别；

实例一:

SELECT prod_name, prod_price
FROM products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01' AND prod_price >= 10
ORDER BY prod_price DESC;

实例二:

SELECT prod_name, prod_price
FROM products
WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01') AND prod_price >= 10 
ORDER BY prod_price DESC;

IN 操作符

IN操作符用来指定的范围，范围中的每个条件都可以匹配。

SELECT prod_id, prod_name, prod_price
FROM products
WHERE vend_id IN ('DLL01', 'BRS01')
ORDER BY prod_id;

使用 IN 操作符的优点：

• 在有很多合法选项时，IN操作符的语法更清楚，更直观；
• 在与AND和OR等其他操作符组合使用时，求值顺序更容易管理；
• IN 操作符一般比一组OR操作符执行的更快；
• IN 操作符的最大优点是可以包含SELECT语句，能够更动态的建立WHERE子句；

NOT操作符

NOT操作符用来否定其后跟的条件；

实例:

SELECT prod_id, prod_name
FROM products
WHERE NOT vend_id = 'DLL01'
ORDER BY prod_id;C

以上实例等同于:

SELECT prod_id, prod_name
FROM products
WHERE vend_id != 'DLL01'
ORDER BY prod_id;

第6课：用通配符进行过滤

LIKE操作符

通配符(wildcard)：用来匹配值的一部分的特殊字符

为在搜索子句中使用通配符，必须使用LIKE操作符。LIKE指示DBMS，后跟的搜索模式利用通配符而不是简单的相等匹配进行比较。


通配符只能用于文本字段(字符串)

百分号(%)通配符

% 表示任何字符出现任意次数；

实例: 找出所有以Fish起头的产品

SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE 'Fish%';

搜索时会区分大小写；

使用多个%通配符：找出所有包含“bean bag”的产品

SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '%bean bag%';

在搜索模式的中间：找出以F开头，以y结尾的所有产品

SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE 'F%y';

在中间使用时搜索电子邮件很有用，如：WHERE email LIKE 'b%@163.com';

%不会匹配NULL

下划线(_)匹配符

下划线通配符是用来匹配单个字符的

实例：匹配两个

SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '__ inch teddy bear';

匹配任意个:

SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '% inch teddy bear';

方括号([])通配符

方括号([])匹配符用来指定一个字符集，它必须匹配指定位置(通配符的位置)的一个字符；

并不是所有的DBMS都支持创建集合的[]，只有微软的Access和SQL Server支持。

SELECT cust_contact
FROM customers
WHERE cust_contact LIKE '[JM]%'
ORDER BY cust_contact;

对[]进行否定，[^JM]%

使用通配符的技巧：

• 不要过度使用通配符，如果能达到相同目的，应该使用其他操作符；
• 在确实需要使用时，尽量不要把它们用在搜索模式的开始处，因为那样是搜索起来最慢的；
• 仔细注意通配符的位置；

....