SQL必知必会摘要

时间：2024-11-05 18:33:53
数据检索 2.2 检索单个列 SELECT prod_name FROM Products; SQL语句不区分大小写
  2.3 检索多个列 SELECT prod_name，prod_id，prod_price FROM Products;  
  2.4 检索所有列 SELECT * FROM Products;  
  2.5 检索不同的值 SELECT DISTINCT vend_id FROM Products;  
  2.6 限制结果 SELECT TOP 5 prod_name FROM Products;(SQL server、Access)
SELECT prod_name FROM Products FETCH FIRST 5 ROWS ONLY;（DB2）
SELECT prod_name FROM Products WHERE ROWNUM <=5;(Oracle)
SELECT prod_name FROM Products LIMIT 5;(MySQL、SQLite等)
SELECT prod_name FROM Products LIMIT 4 OFFSET 3;(LIMIT 3,4)		 从第3行起的4行数据
  2.7 使用注释 SELECT prod_name --这是一条注释（--之后的是注释）
FROM Products;
# 这是一条注释                  （单行注释）
/*这是一条注释，很长            （跨行注释，通常用于代码注释）
很长的注释*/		  
排序检索数据 3.1 排序数据 SELECT prod_name FROM Products ORDER BY prod_name; 字母升序拍；可以通过非检索列进行排序
  3.2 按多个列排序 SELECT prod_name，prod_id，prod_price FROM Products
ORDER BY prod_price，prod_name;		 prod_price相同时才按照prod_name
  3.3 按列位置排序 SELECT prod_name，prod_id，prod_price FROM Products
ORDER BY 2,3;		 表示先按prod_id，再按prod_price排序
  3.4 指定排序方向 SELECT prod_name，prod_id，prod_price FROM Products
ORDER BY prod_price DESC;		 降序排列，大小写字母在排序时是否区分取决于数据库的配置
    SELECT prod_name，prod_id，prod_price FROM Products
ORDER BY prod_price DESC,prod_name;		 prod_name仍为升序，ASC表升序（默认）
过滤数据 4.1 使用WHERR SELECT prod_name，prod_price FROM Products
WHERE prod_price = 3.49;		  
    SELECT prod_name，prod_price FROM Products
WHERE prod_price = 3.49
ORDER BY prod_name;		 WHERE和ORDER BY组合用法
  4.2 WHERE子句操作符 =,<>,!=,<,<=,!<,>,>=,!>,BETWEEN,IS NULL  
  4.2.2 不匹配检查 SELECT  vend_id，prod_name FROM Products
WHERE vend_id<>'DLL01';		 不匹配检查,字符串需要加引号<>与!=等价
  4.2.3 范围值检查 SELECT prod_name，prod_price FROM Products
WHERE prod_price BETWEEN 5 AND 10		 包括5和10
  4.2.4 空值检查 SELECT prod_name，prod_price FROM Products
WHERE prod_price IS NULL;		 NULL与非匹配：空值不会被返回
高级数据过滤 5.1.1 AND操作符 SELECT prod_id，prod_price，prod_name FROM Products
WHERE vend_id = 'DLL01' AND prod_price<=4;		 可用多个AND，增加过滤条件
  5.1.2 OR操作符 SELECT prod_id，prod_price，prod_name FROM Products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';		 第一个条件满足时，不管第二个条件是否满足，相应行都被检索出来
    SELECT prod_id，prod_price，prod_name FROM Products
WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01')AND prod_price<=10;		 组合使用，使用圆括号。AND优先级较高
  5.2 IN操作符 SELECT prod_name，prod_price FROM Products
WHERE vend_id = IN（ 'DLL01','BRS01')
ORDER BY prod_name;		 功能与OR相当
    SELECT prod_name，prod_price FROM Products
WHERE vend_id = 'DLL01'OR vend_id = 'BRS01'
ORDER BY prod_name;		 改写IN语句
  5.3 NOT操作符 SELECT prod_name FROM Products
WHERE NOT vend_id = 'DLL01'
ORDER BY prod_name;		  
    SELECT prod_name FROM Products
WHERE vend_id <> 'DLL01'
ORDER BY prod_name;		 改写NOT语句
用通配符进行过滤 6.1.1 %通配符 SELECT prod_id,prod_name FROM Products
WHERE prod_name LIKE 'Fish%';		 找出以Fish起头的词，Fish之后可以是0个字符；Acess需要使用*非%；大小写是否区分取决于数据库配置
    SELECT prod_id,prod_name FROM Products
WHERE prod_name LIKE '%bean bag%';		 匹配任何包含bean bag的值
    SELECT prod_name FROM Products
WHERE prod_name LIKE 'F%y';		 匹配F开头，y结尾的值（b%@forta.com）
%匹配0个、1个、多个字符
如果字符末尾是空格，则需要用F%y%来匹配
    WHERE prod_name LIKE '%'; 不配匹配prod_name为NULL的行
  6.1.2 _通配符 SELECT prod_id,prod_name FROM Products
WHERE prod_name LIKE '__ inch teddy bear';		 DB2不支持，Access用?非_
_只匹配单个字符，功能与%通，如需匹配2个字符，则需要使用__
  6.1.2 []通配符 SELECT cust_contact FROM Customers
WHERE cust_contract LIKE '[JM]%'
ORDER BY cust_contract;		 找出所有J或M起头的名字
只有Access和SQL server支持
    SELECT cust_contact FROM Customers
WHERE cust_contract LIKE '[^JM]%'
ORDER BY cust_contract;		 找出所有非J或M起头的名字
Access需要用!非^
创建计算字段 7.2 拼接字段 SELECT vend_name + '('+ vend_country +')' FROM Vendors
ORDER BY vend_name;		 Access、SQL server使用+
DB2、Oracle、SQLite使用||
    SELECT Concat（vend_name,'('vend_country,'）')' FROM Vendors
ORDER BY vend_name;		 MySQL使用函数Concat
返回结果两个字段之间有空格
    SELECT RTRIM（vend_name) + '('+ RTRIM(vend_country) + '）'
FROM Vendors ORDER BY vend_name;		 RTRIM()去掉字符串右边的空格
LTRIM()去掉字符串左边的空格
TRIM()去掉字符串左右两边的空格
    SELECT RTRIM（vend_name) + '('+ RTRIM(vend_country) + '）'
AS vend_title
FROM Vendors ORDER BY vend_name;		 使用别名：单词或字符串
  7.3 执行算术计算 SELECT prod_id,quantity,item_price,
quantity*item_price AS expanded_price
FROM OrderItems WHERE order_num = 20008;		 expanded_price是一个计算字段
算术操作符：+、-、*、/
  测试计算 SELECT 3*2
SELECT Trim('abc')
SELECT Now()		 返回6
返回abc
返回当前日期和时间
使用函数处理数据 8.1 函数 MID():Access
SUBSTR():DB2、Oracle、SQLite
SUBSTRING():MySQL、SQL server		 提取字符串组成部分
    使用多种函数:Access、Oracle
CAST():DB2
CONVERT():MySQL、SQL server		 数据类型转换
    NOW():Access
CURRENT_DATE:DB2
CURDATE():MySQL
SYSDATE:Oracle
GETDATE():SQL server
DATE():SQLite		 取当前日期
  8.2.1 文本处理函数 SELECT vend_name UPPER(vend_name) AS vend_name_upcase
FROM Vendors ORDER BY vend_name;		 将文本转化为大写
  常用文本处理函数 LEFT()
LENGTH()  也可使用DATALENGTH()或LEN()
LOWER()    Access使用LCASE()
LTRIM()
RIGHT()
RTTIM()
SOUNDEX()
UPPER()		 返回字符串左边的字符
返回字符串的长度
将字符串转换为小写
去掉字符串左边的空格
返回字符串右边的字符
去掉字符串右边的空格
返回字符串的SOUNDEX值(按照发音过滤)
将字符串转换为大写
  8.2.2 日期和时间处理函数 SELECT order_num FROM Orders
WHERE DATEPART(yy,order_date)=2012;		 SQL server
DATEPART()返回日期的某一部分（年份）
    SELECT order_num FROM Orders
WHERE DATEPART('yyyy',order_date)=2012;		 Access
    SELECT order_num FROM Orders
WHERE to_number(to_char(order_date,'YYYY'))=2012;		 Oracle没有DATEPART()函数
    SELECT order_num FROM Orders
WHERE order_date BETWEENto_date('01-01-2012')
AND to_date('12-31-2012')		 BETWEEN改写
    SELECT order_num FROM Orders
WHERE strftime('%Y',order_date)= '2012';		 SQLite小技巧
    SELECT order_num FROM Orders
WHERE YEAR(order_date)= 2012;		 MySQL、MariaDB
  8.2.3 数值处理函数 ABS()、COS()、PI()、SIN()、SQRT()、TAN()
EXP()返回一个的指数值		  
汇总数据 9.1 聚集函数 AVG()
COUNT()
MAX()
MIN()
SUM()		 返回某列的平均数
返回某列的行数
返回某列的最大值
返回某列的最小值
返回某列数值之和
  9.1.1 AVG()函数 SELECT AVG(prod_price) AS avg_price FROM Products; avg_price是别名，AVG()忽略值值为NULL的行
    SELECT AVG(prod_price) AS avg_price FROM Products
WHERE vend_id = 'DLL01';		 组合过滤
  9.1.2 COUNT()函数 SELECT COUNT(*) AS num_cust FROM Customers; 对表中行的数目进行统计，包括空、非空、NULL
    SELECT COUNT(cust_email) AS num_cust FROM Customers; 只对具有email的客户计数
指定列名时会忽略值为空的行若使用COUNT(*)不忽略
  9.1.3 MAX()函数 SELECT MAX(prod_price) AS max_price FROM Products; MAX()一般用来找出最大数值或日期值。忽略NULL行
MAX()用于文本数据时，返回该列排序后的最后一行
  9.1.4 MAX()函数 SELECT MIN(prod_price) AS min_price FROM Products; MIN()一般用来找出最小数值或日期值。忽略NULL行
MIN()用于文本数据时，返回该列排序后最前面的行
  9.1.5 SUM()函数 SELECT SUM(quantity) AS items_ordered
FROM OrderItems WHERE order_num = 20005;		 返回订单中所有物品数量之和
    SELECT SUM(item_price*quantity) AS total_price
FROM OrderItems WHERE order_num = 20005;		 SUM()忽略NULL行
SUM()也可以用来合计计算值
  9.2 聚集不同值 SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products WHERE vend_id = 'DLL01';		  
  9.3 组合聚集函数 SELECT
COUNT(*) AS num_items,
MIN(prod_price),AS price_min
MAX(prod_price),AS price_max
AVG(prod_price),AS price_avg
FROM Products;		  
分组数据 10.1 数据分组 SELECT COUNT(*) AS num_prods FROM Products
WHERE vend_id = 'DLL01'		  
  10.2 创建分组 SELECT vend_id,COUNT(*) AS num_prods FROM Products
GROUP BY vend_id;		 GROUP子句出现在WHERE后 ORDER BY子句之前
    GROUP BY 2,1; 通过相对位置指定列，先按列2分组，再按列1分组
  10.3 过滤分组 SELECT vend_id,COUNT(*) AS orders FROM Orders
GROUP BY cust_id HAVING COUNT(*)>=2;		 HAVING与GROUP BY结合使用
    SELECT cust_id,COUNT(*) AS num_prods FROM Products
WHERE prod_price >=4
GROUP BY vend_id HAVING COUNT(*)>=2;		 WHERE和HAVING组合
  10.4 分组和排序 SELECT order_num,COUNT(*) AS items FROM OrderItems
GROUP BY order_num HAVING COUNT(*)>=3
ORDER BY items,order_num;		 Access不支持别名排序
  10.5 SELECT子句顺序 SELECT    要返回的列或表达式
FROM      从中检索数据的表
WHERE     行级过滤
GROUP BY  分组说明
HAVING    组级过滤
ORDER BY  输出排序顺序		  
使用子查询 11.2 利用子查询进行过滤 SELECT cust_id FROM Orders
WHERE  order_num IN
(SELECT order_num FROM OrderItmes WHERE prod_id = 'RGAN01')		  
  11.2.1 SELECT cust_name,cust_contact FROM Customers
WHERE cust_id IN(SELECT cust_id FROM Orders WHERE order_num IN
(SELECT order_num FROM OrderItmes WHERE prod_id = 'RGAN01'));		  
  11.3 作为计算字段使用子查询 SELECT cust_name,cust_state,(SELECT COUNT(*) FROM Orders WHERE Order.cust_id = Customers.cust_id) AS orders
FROM Customers ORDER BY cust_name;		 完全限定列名：表明.列名
联结表 12.2 创建联结 SELECT vend_name,prod_name,prod_price
FROM Vendors,Products
WHERE Vendors.vend_id = Product.vend_id;		 列名来自两个不同的表
通过WHERE子句构造关系
  12.2.2 内联结 SELECT vend_name,prod_name,prod_price
FROM Vendors INNER JOIN Products
ONVendors.vend_id = Product.vend_id;		 效果同上
  12.2.3 联结多个表 SELECT prod_name,vend_name,prod_price,quantity
FROM OrderItems,Products,Vendors
WHERE Product.vend_id = Vendors.vend_id
AND OrderItems.prod_id = Product.vend_id
AND order_num = 20007;		 联结的表越多，性能下降的越厉害
    SELECT cust_name,cust_contact
FROM Customers,Orders,OrderItems
Where Customers.cust_id =Orders.cust_id
AND OrderItems.order_num = Orders.order_num
AND prod_num  'RGAN01';		 改写11.2.1
创建高级联结（待补充） 13.1    
组合查询 14.2.1 使用UNION UNION用在两条SELECT语句之间，把输出整合成一个结果集  
  14.2.4 对组合查询结果排序 在最后加ORDER BY，不允许有多种排序方式  
插入数据 15.1 数据插入 插入完整的行、行的一部分、某些查询结果  
  15.1.1 插入完整的行 INSERT INTO Customer VALUES
('1000000006','Yoy Land','123 Any Street','New York','NY','11111','USA',NULL,NULL);		 有些SQL中INTO可以省略
某列没有值，则应使用NULL值（允许空值时）
    INSERT INTO Customer(cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email)
VALUES('1000000006','Yoy Land','123 Any Street','New York','NY','11111','USA',NULL,NULL);		 更为安全,列名顺序可以*调整，与数据匹配即可
  15.1.2 插入部分行 INSERT INTO Customer(cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country)
VALUES('1000000006','Yoy Land','123 Any Street','New York','NY','11111','USA');		 有两列是空值(NULL)，未进行插入
  15.1.3 插入检索出的数据 INSERT INTO Customer(cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country)
SELECT cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country
FROM CustNew;		 检索出多少行就插入多少行（0、1、2...）
INSERT通常只插入一行
  15.2 从一个表复制到另一个表 SELECT * INTO CustCopy FROM Customers;
SELECT cust_id,cust_name INTO CustCopy FROM Customers;
CREATE TABLE CustCopy AS SELECT * FROM Customers;		 创建一个名为CustCopy的新表复制表Customers的全部内容
可以只复制指定的列
MySQL、Oracle、SQLite的语法
更新和删除数据 16.1 更新数据 UPDATE Customers SET cust_email = 'hotpsy@126.com'
WHERE cust_id = '001';		  
    UPDATE Customers
SET cust_cntact = 'hotpsy',
SET cust_email = 'hotpsy@qq.com'
WHERE cust_id = '002';		 更新多个列
    UPDATE Customers
SET cust_email = NULL
WHERE cust_id = '003';		 设置为NULL可以表示删除列的值，如果该列允许设置为NULL
  16.2 删除数据 DELETE FROM Customers
WHERE cust_id = '004';		  
    TRUNCATE Customers  删除整个表
创建和操作表 17.1 创建表（也可以使用工具） CREAT TABLE Products(
prod_id     CHAR(10)       NOT NULL,
vend_id     CHAR(10)       NOT NULL,
prod_name   CHAR(254)      NOT NULL,
prod_price  DECIMAL(8,2)   NOT NULL,
prod_desc   VARCHAR(10)    NULL);		  
  17.1.3 指定默认值 CREAT TABLE Products(
prod_id     CHAR(10)       NOT NULL,
vend_id     CHAR(10)       NOT NULL,
prod_name   CHAR(254)      NOT NULL,
prod_price  DECIMAL(8,2)   NOT NULL  DEFAULT 2,
prod_desc   VARCHAR(10)    NULL);		  
  获得系统日期 Access: NOW()
DB2:   CURRENT_DATE
MySQL: CURRENT_DATE()
Oracle: SYSDATE
PostgreSQL:CURRENT_DATE
SQL Server:GETDATE()
SQLite:date('now')		  
  17.2 更新表 ALTER TABLE Vendors
ADD vend_phone CHAR(20);		 增加一列vend_phone
    ALTER TABLE Vendors
DROP COLUMN vend_phone CHAR(20);		 删除列vend_phone
  17.3 删除表 DROP TABLE CustCopy 删除表没有确认
  17.4 重命名表 Oracle、MySQL: ALTER TABLE 表名 rename to 新表名
SQL server:sp_rename
SQLite:ALTER TABLE		 无标准，各个DB实现不同
使用视图      
使用存储过程 19.3 执行存储过程 EXECUTE AddNewProduct('JTS01','Stuffed Eiffel Tower',6.49,'xxxx')  
  19.3 创建存储过程 CREATE PROCEDURE MailingListCount(ListCount OUT INTEGER) IS
v_rows INTEGER;
BEGIN
 SELECT COUNT(*) INTO v_rows FROM Customers
 WHERE NOT CUST_email IS NULL;
 ListCount := v_rows;
END;		 Oracle版本
    var ReturnValue NUMBER
EXEC MailingListCount（：ReturnValue）;
SELECT ReturnValue;		 调用Oracle例子
管理事务处理 20.1 事务处理 事务、回退、提交、保留点  
  20.2 控制事务处理 SET TRANSACTION
…		 Oracle
    BEGIN TRANSACTION
…
COMMIT TRANSACTION		 SQL server
  20.2.1 使用ROLLBACK DELETE FROM Orders;
ROLLBACK;		 回退（撤销）SQL语句
  20.2.2 使用COMMIT SET TRANSACTION
DELETE OrderItems WHERE order_num = 12345;
DELETE Orders WHERE order_num = 12345;
COMMIT;		  
  20.2.3 使用保留点 ...
SAVEPOINT delete1;
...
ROLLBACK TRANSACTION		  
使用游标      
高级SQL特性      

http://files.cnblogs.com/files/hotpsy/SQL%E5%BF%85%E7%9F%A5%E5%BF%85%E4%BC%9A%E6%91%98%E8%A6%81.rar

相关文章