SQL必知必会(第3版)

1. 排序检索数据

// 按多列排序
select prod_id, prod_price, prod_name from products order by prod_price, prod_name;
如果多行具有相同的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; //升序(从A到Z)
select prod_id, prod_price, prod_name from products order by prod_price desc; //降序(从Z到A)
如果对每列都要降序，那么每列都必须用desc

 

2. 过滤数据

// 不等于
<> 或 != : 值与字符串比较，需要单引号，与数值比较，不用引号 

// 范围值between
select prod_name from products where prod_price between 5 and 10;

// 空值检查
is null 或 is not null

// and or
select prod_name from products where (vend_id = 'DLL01' or vend_id = 'BRS01') and prod_price >= 10;

// in, 功能同 or
select prod_name from products where vend_id in ('DLL01', 'BRS01');

// not
select prod_name from products where not vend_id = 'DLL01';

// like - 通配符只用于字符串
// 以Fish开始的名称，区分大小写
select prod_name from products where prod_name like 'Fish%'; 
select prod_name from products where prod_name like '%bean bag%'; //位于两端
select prod_name from products where prod_name like 'F%y%'; //F开始包含y字符

// 下划线 - 匹配单个字符
select prod_name from products where prod_name like '__ inch teddy bear';

// 方括号 - 指定字符集
// 找出所有名字以J或M起头的联系人
select cust_contact from customers where cust_contact like '[JM]%'; 
select cust_contact from customers where cust_contact like '[^JM]%'; // 否定 

 

3. 函数与数据

// 拼接字段
MySQL: select concat(vend_name, ' (', vend_country, ')') from vendors;
Oracle: select vend_name || ' (' || vend_country || ')' from vendors;
SQLServer: select vend_name + ' (' + vend_country + ')' from vendors;
输出: Bear Emporium (USA)

// 去掉空格
rtrim(): 去掉右空格, ltrim(): 去掉左空格, trim(): 去掉两边空格

// 别名
select concat(vend_name, ' (', vend_country, ')') as vend_title from vendors;

// 计算乘积
select prod_id, quantity, item_price*item_price as expanded_price from orderitems where order_num = 20008;

// 函数
提取字串: Access使用MID()，Oracle使用SUBSTR()，MySQL和SQLServer使用SUBSTRING()
数据类型转换: MySQL和SQLServer使用CONVERT()
取当前日期: Access使用NOW()，Oracle使用SYSDATE，MySQL使用CURDATE()，SQLServer使用GETDATE()
left(): 返回串左边字符, length(): 串长度, lower():转小写, upper(): 转大写

// SOUNDEX() - 发音类似的单词
select cust_name, cust_contact form customers where soundex(cust_contact) = soundex('Michael Green');

// 检索时间段 - 2004年所有订单
MySQL: select order_num from orders where year(order_date) = 2004;
Oracle: select order_num from orders where to_number(to_char(order_date, 'YY')) = 2004;
SQLServer: select order_num from orders where datepart(yy, order_date) = 2004;
另一种方法: select order_num from orders where order_date between to_date('01-JAN-2004) and to_date('31-DEC-2004');

// AVG() - 返回某列平均值
select avg(prod_price) as avg_price from products;

// COUNT() - 计数
count(*): 对表中行进行计数
count(cust_email): 对特定行计数，忽略null值

// MAX() - 指定列的最大值
select max(prod_price) as max_price from products;
如果列是文本，那么返回最后一行

// MIN() - 指定列的最小值
select min(prod_price) as max_price from products;
如果列是文本，那么返回最前面一行

// SUM() - 指定列的总计
select sum(quantity) as items_ordered from orderitems;

// distinct - 对不同的值进行计算，如果相同，则忽略
select avg(distinct prod_price) as avg_price from products;

// group by - 数据分组
返回每个供应商的产品
select vend_id, count(*) as num_prods from products group by vend_id;

// having - 过滤分组
返回销售2个以上、价格为4以上的供应商
select vend_id, count(*) as num_prods from products where prod_price >= 4 group by vend_id having count(*) >= 2

 

4. 查询

// 子查询进行过滤
查询订购物品RGAN01的所有客户
select cust_name, cust_contact from customers where cust_id in 
(select cust_id from orders where order_num in 
(select order_num from orderitems where prod_id = 'RGAN01'))

// 用计算结果作为子查询
查询customers表中每个客户的订单总数
select cust_name, cust_contact, (select count(*) from orders 
where orders.cust_id = customers.cust_id) as orders from customers;

// 联接表 - 内部联接或等值连接
select vend_name, prod_name, prod_price from vendors, products 
where vendors.vend_id = products.vend_id;
或者
select vend_name, prod_name, prod_price from vendors inner join products 
where vendors.vend_id = products.vend_id;
使用联接改写：查询订购物品RGAN01的所有客户
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_id = 'RGAN01';

// 自联接
查询Jim Jones所有公司工作的所有客户
select c1.cust_id, c1.cust_name, c1.cust_contact from customers as c1, 
customers as c2 where c1.cust_name = c2.cust_name 
and c2.cust_contact = 'Jim Jones';

// 外部联接
检索所有客户，包括没有订单的客户
select customers.cust_id, orders.order_num from customers left outer join
orders on customers.cust_id = orders.cust_id; // 左联接
或者select customers.cust_id, orders.order_num from customers, orders
where customers.cust_id *= orders.cust_id;

select customers.cust_id, orders.order_num from customers right outer join
orders on orders.cust_id = customers.cust_id; // 右联接
或者select customers.cust_id, orders.order_num from customers, orders
where orders.cust_id =* customers.cust_id;

select customers.cust_id, orders.order_num from orders full outer join
customers on orders.cust_id = customers.cust_id; // 全联接

// UNION - 组合查询
select cust_name, cust_contact, cust_email from customers where 
cust_state in ('IL', 'IN', 'MI') union select cust_name, cust_contact, 
cust_email from customers where cust_name = 'Fun4All';

union all: 返回所有记录，不会过滤相同记录

// Oracle使用别名不需要as

 

5. 插入

// 插入检索出的数据
insert into customers(cust_name, cust_email) select cust_name, cust_email from custnew;

// select into
创建一个custcopy新表，并把customers表的整个内容复制到新表中
select * into custcopy from customers;

MySQL和Oracle不同
create table custcopy as select * from customers;

 

6. 操作表  

// 创建表
CREATE TABLE OrderItems (
  order_num  int          NOT NULL,
  order_item int          NOT NULL,
  prod_id    char(10)     NOT NULL,
  quantity   int          NOT NULL default 1,
  item_price decimal(8,2) NOT NULL,
  createTime timestamp    NOT NULL default sysdate  //Oracle使用 
);

// 更新表
alter table vendors add vend_phone char(20); //给vendors表增加一个char类型字段
alter table vendors drop column vend_phone; //删除vend_phone列

// 删除表
drop table custcopy;

// 重命名表
rename

 

7. 视图

视图是把复杂查询的语句作为一个表，但表中不会有数据，可以重复使用
// 创建视图
查询订购任意产品的所有客户
create view productcustomers as select cust_name, cust_contact, prod_id 
from customers, orders, orderitems where customers.cust_id = orders.cust_id
and orderitems.order_num = orders.order_num;
使用，检索订购了产品RGAN01的所有客户
select * from productcustomers where prod_id = 'RGAN01';

 

8. 存储过程

储存过程保存一条或多条重复使用的SQL语句，类似批文件
// 创建存储过程
对有email的客户进行统计
Oracle版本
create procedure MailingListCount
(ListCount OUT NUMBER)
IS
BEGIN
    select * from customers where not cust_email is null;
    ListCount := SQL%ROWCOUNT;
END;
说明: ListCount返回数据，OUT表示从存储过程返回数据，IN表示传值给存储过程，
INOUT表示既传递值给存储过程，又返回值

SQLServer版本
create procedure MailingListCount
AS
DECLARE @cnt INTEGER
select @cnt = count(*) from customers where not cust_email is null;
RETURN @cnt;

给orders表插入一个新订单
create procedure NewOrder @cust_id char(10) //客户ID号
AS
declare @order_num integer
select @order_num = max(order_num) from orders //自动产生订单号
select @order_num = @order_num+1
insert into orders(order_num, order_date, cust_id)
values(@order_num, getdate(), @cust_id)
return @order_num;

另一版本
create procedure NewOrder @cust_id char(10) //客户ID号
AS
insert into orders(cust_id) values(@cust_id)
select order_num = @@IDENTITY;
由数据库表自动生成订单号，IDENTITY为SQLServer自动递增，产生订单号

// 执行存储过程
EXECUTE AddNewProduct('JTS01', 'Stuffed Eiffel Tower', 6.49)
执行一个名为AddNewProduct的存储过程，将一个新产品添加到Product表中

 

9. 事务

事务保留点(savepoint): 事务处理中设置的临时占位符，可以对它发布回退。
不能回退select, create, drop操作
// 控制事务
SQLServer:
BEGIN TRANSACTION
...
COMMIT TRANSACTION

MySQL:
START TRANSACTION

// Rollback
delete from orders;
ROLLBACK;

// commit
Oracle:
delete orderitems where order_num = 12345;
delete orders where order_num = 12345;
commit;

// 使用保留点
begin transaction
insert into customers...;
savepoint startorder;
insert into orders...;

 

10. 游标

// 创建游标
DB2, SQL Server, Sybase
declare custcursor CURSOR
for 
select * from customers where cust_email is null

Oracle
declare CURSOR custcursor
is
select * from customers where cust_email is null

// 使用游标
OPEN CURSOR CustCursor
从第一行检索出数据
declare type custcursor is ref cursor return customers%ROWTYPE;
declare custrecord customers%ROWTYPE
begin
    open custcursor;
    fetch custcursor into custrecord;  //fetch指出要检索的行
    close custcursor;
end;

循环游标
declare type custcursor is ref cursor return customers%ROWTYPE;
declare custrecord customers%ROWTYPE
begin
    open custcursor;
    loop
    fetch custcursor into custrecord;  //fetch指出要检索的行
    exit when custcursor%notfound;
    ...
    end loop;
    close custcursor;
end;

 

11. 高级SQL特性 

// 主键
CREATE TABLE Vendors (
  vend_id      char(10) NOT NULL PRIMARY KEY,
  vend_name    char(50) NOT NULL,
  vend_address char(50) NULL,
  vend_city    char(50) NULL,
  vend_state   char(5)  NULL,
  vend_zip     char(10) NULL,
  vend_country char(50) NULL,
  vend_ser     char(10) UNIQUE //唯一性约束 
);
另一种定义方法：
alter table vendors add constraint primary key (vend_id);

// 外键
CREATE TABLE Orders (
  order_num  int      NOT NULL PRIMARY KEY,
  order_date datetime NOT NULL ,
  cust_id    char(10) NOT NULL REFERENCES Customers(cust_id)
);
另一种定义方法：
alter table customers add constraint foreign key (vend_id) references Customers (cust_id);

// 检查约束
CREATE TABLE OrderItems (
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL CHECK(quantity > 0), //检查数量大于0
  item_price decimal(8,2) NOT NULL 
);
alter table orderitems add constraint check (gendar LIKE '[MF]'); //检查性别为M或F

// 索引
索引用来排序数据以加快搜素和排序操作的速度，但索引降低数据插入、修改和删除的性能
create index prod_name_ind on products (prod_name); //索引必须唯一命名

// 触发器
触发器是特殊的存储过程，在执行特定操作时会被触发。
例如对Orders表进行insert操作，在insert执行之后，对数据进行验证
Oracle版本
create trigger customer_state after insert or update
for each row
being
update customers set cust_state = Upper(cust_state)
where customers.cust_id = :OLD.cust_id
end;

 

12. 数据类型

数据类型 - 不同数据库支持不同类型
字符串: char, nchar, long, memo, varchar
数值: bit, decimal, numeric, float, int, integer, real, samllint, tinyint
日期: date, datetime, smalldatetime, time
二进制: binary, logn raw, raw, varbinary