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