检索数据
SELECT 语句
检索单个列:
SELECT prod_name
FROM Products;
检索多个列:
SELECT prod_id, prod_name, prod_price
FROM Products;
检索所有列:
SELECT *
FROM Products;
(* 是通配符)
排序检索数据
排序数据:
SELECT prod_name
FROM Products
ORDER BY prod_name;
按多个列排序:
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;
(DESC 是降序,默认 ASC 升序)
SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC, prod_name;
过滤数据
使用 WHERE 子句
SELECT prod_name, prod_price
FROM Products
WHERE prod_price = 3.49
WHERE 子句操作符
=,<>,!=,<,<=,!<,>,>=,!>,BETWEEN,IS NULL
检查单个值:
SELECT prod_name, prod_price
FROM Products
WHERE prod_price < 10;
不匹配检查:
SELECT vend_id, prod_name
FROM Products
WHERE vend_id <> ‘DLL01’;
范围值检查:
SELECT prod_name, prod_price
FROM Products
WHERE prod_price BETWEEN 5 AND 10;
空值检查:
SELECT prod_name
FROM Products
WHERE prod_price IS NULL;
高级数据过滤
组合 WHERE 子句
AND 操作符:
SELECT prod_id, prod_price, prod_name
FROM Products
WHERE vend_id = ‘DLL01’ AND prod_price <= 4;
OR 操作符:
SELECT prod_name, prod_price
FROM Products
WHERE vend_id = ‘DLL01’ OR vend_id = ‘BRS01’;
操作优先顺序:AND>OR
IN 操作符:
SELECT prod_name, prod_price
FROM Products
WHERE vend_id IN (‘DLL01’, ‘BRS01’)
ORDER BY prod_name;
NOT 操作符:
SELECT prod_name
FROM Products
WHERE NOT vend_id = ‘DLL01’
ORDER BY prod_name;
用通配符进行过滤
LIKE 操作符:
LIKE 指示 DBMS,后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。
百分号%通配符:
%表示任何字符出现任意次数。
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE ‘Fish%’;
将检索任意以Fish起头的词。
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE ‘%bean bag%’;
表示匹配任何位置包含文本 bean bag 的值。
下划线_通配符:
用途与%一样,但一个_只匹配单个字符。
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE ‘__ inch teddy bear’;
方括号[]通配符:
指定一个字符集,必须匹配指定通配符位置包含的一个字符。
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE ‘[JM]%’
ORDER BY cust_contact;
加前缀字符^可否定,即返回不匹配[]中的字符的值。
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE ‘[^JM]%’
ORDER BY cust_contact;
创建计算字段
拼接:将值联结到一起构成单个值,用+或||表示。
SELECT vend_name + ‘(’ + ven_country + ‘)’
FROM Vendors
ORDER BY vend_name;
以下作用相同
SELECT vend_name || ‘(’ || ven_country || ‘)’
FROM Vendors
ORDER BY vend_name;
去掉拼接后自动填充的多余空格可用 TRIM() 去掉左右两边空格,RTRIM() 去掉右边空格,LTRIM() 去掉左边空格。
SELECT vend_name + ‘(’ + RTRIM(ven_country) + ‘)’
FROM Vendors
ORDER BY vend_name;
使用别名:
别名是一个字段或值的替换名,相当于给字段一个新的名字。用关键字 AS 赋予别名。
SELECT vend_name + ‘(’ + ven_country + ‘)’ AS vend_title
FROM Vendors
ORDER BY vend_name;
执行算术计算:
SELECT prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;
SQL 算术操作符:±*/
使用数据处理函数
DBMS 函数的差异
函数
语法
提取串的组成部分
Access 使用 MID();DB2、Oracle 和 PostgreSQL 使用 SUBSTR();MySQL、SQL Server 和 Sybase 使用 SUBSTRING()
数据类型转换
Access 和 Oracle 使用多个函数,每种类型的转换有一个函数;DB2 和 PostgreSQL 使用 CAST();MySQL、SQL Se