SQL必须知必会（第5版）用到的SQL脚本命令

第二课
11.select prod_name from products;
13.select prod_id,prod_name,prod_price from products;
14.select * from products;
15.select vend_id from products;
16.select distinct vend_id from products;
18.select prod_name from products limit 5;
19.select prod_name from products limit 5 offset 5;
第三课
23。select prod_name from products;
24.select prod_name from products order by prod_name;
25.select prod_id,prod_price,prod_name from products order by prod_price,prod_name;
26.select prod_id,prod_price,prod_name from products order by 2,3;
28.select prod_id,prod_price,prod_name from products order by prod_price desc,prod_name;
第四课
31.select prod_name,prod_price from products where prod_price=3.49;
32.select prod_name,prod_price from products where prod_price<10;
34.select prod_name,prod_price from products where prod_price<=10;
34.select vend_id,prod_name from products where vend_id <> ‘DLL01’;
35.select vend_id,prod_name from products where vend_id != ‘DLL01’;
35.select prod_name,prod_price from products where prod_price between 5 and 10;
36.select prod_name from products where prod_price is null;
37.select cust_name from customers where cust_email is null;
第五课
39.select prod_id,prod_price,prod_name from products where vend_id=‘DLL01’ and prod_price <=4;
40.select prod_id,prod_price,prod_name from products where vend_id=‘DLL01’ or vend_id=‘BRS01’;
42.select prod_name,prod_price from products where vend_id =‘DLL01’ OR vend_id =‘BRS01’ and prod_price >=10;
43.select prod_name,prod_price from products where (vend_id =‘DLL01’ OR vend_id =‘BRS01’) and prod_price >=10;
44.select prod_name,prod_price from products where vend_id in(‘DLL01’,‘BRS01’) order by prod_name;
44.select prod_name,prod_price from products where vend_id=‘DLL01’ or vend_id=‘BRS01’ order by prod_name;
46.select prod_name from products where not vend_id =‘DLL01’ order by prod_name;
46.select prod_name from products where vend_id <> ‘DLL01’ order by prod_name;
第六课
50.select prod_id,prod_name from products WHERE prod_name like ‘Fish%’;
51.select prod_name from products where prod_name LIKE ‘F%y’;
51.select prod_id,prod_name from products where prod_name like ‘%bean bag%’;
53.select prod_id,prod_name from products where prod_name like ‘__ inch teddy bear’;
53.select prod_id,prod_name from products where prod_name like ‘% inch teddy bear’;
54.select cust_contact from customers where cust_contact like ‘[JM]%’ order by cust_contact;
55.select cust_contact from customers where cust_contact like ‘[^JM]%’ order by cust_contact;(mysql中不可用，SQL server中可用)
55.select cust_contact from customers where not cust_contact like ‘[JM]%’ order by cust_contact;(mysql中可用)
第7课
60.select vend_name + ‘(’ +vend_country + ‘)’ from vendors order by vend_name;(SQL server可用)
60.select vend_name || ‘(’ || vend_country || ‘)’ from vendors order by vend_name;（sqlite,oracle,DB2可用）
61.select concat(vend_name,’(’,vend_country,’)’) from vendors order by vend_name;
62.select rtrim(vend_name) + ’ (’ +rtrim(vend_country) + ‘)’ from vendors order by vend_name;(SQL server可用)
62.select rtrim(vend_name) || ’ (’ || rtrim(vend_country) || ‘)’ from vendors order by vend_name;（sqlite,oracle,DB2可用）
63.select rtrim(vend_name) + ’ (’ +rtrim(vend_country) + ‘)’ as vend_title from vendors order by vend_name;(SQL server可用)
64.select rtrim(vend_name) || ’ (’ || rtrim(vend_country) || ‘)’ from vendors as vend_title order by vend_name;（sqlite,oracle,DB2可用）
64.select concat(rtrim(vend_name),’(’,rtrim(vend_country),’)’) as vend_title from vendors order by vend_name;
65.select prod_id,quantity,item_price from orderitems where order_num=20008;
66.select prod_id,quantity,item_price,quantityitem_price as expanded_price from orderitems where order_num=20008;
第8课
70.select vend_name,upper(vend_name) as vend_name_upcase from vendors order by vend_name;
72.select cust_name,cust_contact from customers where cust_contact = ‘Michael Green’;
73.select cust_name,cust_contact from customers where soundex(cust_contact) =soundex(‘Michael Green’);
74.select order_num from orders where datepart(yy,order_date)=2020;(sql server可用)
74.select order_num from orders where DATE_PART(‘year’,order_date)=2020;
75. select order_num from orders where extract(year from order_date) =2020;(mysql中可用)
75.select order_num from orders where order_date between to_date(‘2020-01-01’,‘yyyy-mm-dd’) and to_date(‘2020-12-31’,‘yyyy-mm-dd’);(oracle)
76.select order_num from orders where year(order_date)=2020;(mysql,mariaDB)
16.select order_num from orders where strftime(’%Y’,order_date)=‘2020’;(sqlite)
第9课
79.select avg(prod_price) as avg_price from products;
80.select avg(prod_price) as avg_price from products where vend_id=‘DLL01’;
81.select count() as num_cust from customers;
81.select count(cust_email) as num_cust from customers;
82.select max(prod_price) as max_price from products;
83.select min(prod_price) as min_price from products;
84.select sum(quantity) as items_ordered from orderitems where order_num=20005;
84.select sum(item_pricequantity) as total_price from orderItems where order_num =20005;
85.select avg(distinct prod_price) as avg_price from products where vend_id=‘DLL01’;
87.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课
89.select count() as num_prods from products where vend_id=‘DLL01’;
90.select vend_id,count() as num_prods from products group by vend_id;
93.select cust_id,count() as orders from orders group by cust_id having count()>=2;
94.select vend_id,count() as num_prods from products where prod_price>=4 group by vend_id having count()>=2;
94.select vend_id,count() as num_prods from products group by vend_id having count()>=2;
96.select order_num,count() as items from orderitems group by order_num having count()>=3;
96.select order_num,count() as items from orderitems group by order_num having count()>=3 order by items,order_num;
第11课
100.select order_num from orderitems where prod_id=‘RGAN01’;
100.select cust_id from orders where order_num in (20007,20008);
101.select cust_id from orders where order_num in (select order_num from orderitems where prod_id=‘RGAN01’);
102.select cust_name,cust_contact from customers where cust_id in(1000000004,1000000005);
102.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’));
104.select count() as orders from orders where cust_id = 1000000001;
104.select cust_name,cust_state,(select count() from orders where orders.cust_id=customers.cust_id) as orders
from customers order by cust_name;
105.select cust_name,cust_state,(select count() from orders where orders.cust_id=cust_id) as orders
from customers order by cust_name;
第12课
111.select vend_name,prod_name,prod_price from vendors,products where vendors.vend_id=products.vend_id;
116.select prod_name,vend_name,prod_price,quantity from orderitems,products,vendors where products.vend_id=vendors.vend_id and orderitems.prod_id=products.prod_id and order_num=20007;
113.select vend_name,prod_name,prod_price from vendors,products;
117.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’));
117.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’;
第13课
120.select rtrim(vend_name)+’(’+rtrim(vend_country)+’)’ as vend_title from vendors order by vend_name;
121.select cust_name,cust_contact from customers as c,orders as o,orderitems as oi where c.cust_id=o.cust_id and oi.order_num=o.order_num and prod_id=‘RGAN01’;
122.select cust_id,cust_name,cust_contact from customers where cust_name=(select cust_name from customers where cust_contact=‘Jim Jones’);
122.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’;
124.select c.,o.order_num,o.order_date,oi.prod_id,oi.quantity,oi.item_price from customers as c,orders as o,orderItems as oi where c.cust_id =o.cust_id and oi.order_num =o.order_num and prod_id =‘RGAN01’;
125.select customers.cust_id,orders.order_num from customers inner join orders on customers.cust_id=orders.cust_id;
125.select customers.cust_id,orders.order_num from customers left outer join orders on customers.cust_id=orders.cust_id;
127.select customers.cust_id,orders.order_num from customers full outer join orders on customers.cust_id =orders.cust_id;(mysql,sqlite不可用,sql server可用)
127.select customers.cust_id,count(orders.order_num) as num_ord from customers inner join orders on customers.cust_id=orders.cust_id group by customers.cust_id;
128.select customers.cust_id,count(orders.order_num) as num_ord from customers left outer join orders on customers.cust_id=orders.cust_id group by customers.cust_id;
第14课
132.select cust_name,cust_contact,cust_email from customers where cust_state in(‘IL’,‘IN’,‘MI’);
132.select cust_name,cust_contact,cust_email from customers where cust_name=‘Fun4All’;
133.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’;
134.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’;
136.select cust_name,cust_contact,cust_email from customers where cust_state in(‘IL’,‘IN’,‘MI’) union all select cust_name,cust_contact,cust_email from customers where cust_name=‘Fun4All’;
137.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’ order by cust_name,cust_contact;
第15课
141.insert into customers values(1000000006,‘Toy Land’,‘123 Any Street’,‘New York’,‘NY’,‘11111’,‘USA’,null,null);
142.insert into customers(cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email) values(1000000007,‘Toy Land’,‘123 Any Street’,‘New York’,‘NY’,‘11111’,‘USA’,null,null);
143.insert into customers(cust_id,cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip) values(1000000006,null,null,‘Toy Land’,‘123 Any street’,‘New York’,‘NY’,‘11111’);
144.insert into customers(cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country) values(1000000006,‘Toy Land’,‘123 Any Street’,‘New York’,‘NY’,‘11111’,‘USA’);
145.insert into customers(cust_id,cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country) select cust_id, cust_contact,cust_email,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country from custnew;
147.create table custcopy as select * from customers;
151.update customers set cust_email=‘kim@thetoystore.com’ where cust_id=1000000005;
151.update customers set cust_contact=‘sam roberts’,cust_email=‘sam@toyland.com’ where cust_id=1000000006;
152.update customers set cust_email=null where cust_id=1000000005;
153.delete from customers where cust_id=1000000006;
第17课
158.create 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(1000) null);
160.CREATE TABLE orders (order_num int(11) NOT NULL,order_date datetime NOT NULL,cust_id char(10) NOT NULL);
160. create table vendors (vend_id char(10) NOT NULL,vend_name char(50) NOT NULL,vend_address char(50),vend_city char(50) ,vend_state char(5) ,vend_zip char(10) ,vend_country char(50));
161. create table orderitems (order_num int(11) NOT NULL,order_item int(11) NOT NULL,prod_id char(10) NOT NULL,quantity int(11) NOT NULL default 1,item_price decimal(8,2) NOT NULL);
163.alter table vendors add vend_phone char(20);
164.alter table vendors drop column vend_phone;
165.drop table custcopy;
第18课
167.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’;
171.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;
select cust_name,cust_contact from productcustomers where prod_id=‘RGAN01’;
172.select rtrim(vend_name)+’ (’+rtrim(vend_country)+’)’ as vend_title from vendors order by vend_name;
173.create view vendorlocations as select rtrim(vend_name)+’ (’+rtrim(vend_country)+’)’ as vend_title from vendors; （SQL server语法）
173.create view vendorlocations as select concat(rtrim(vend_name),’ (’,rtrim(vend_country),’)’) as vend_title from vendors; （mysql语法）
175.create view customerEMailList as select cust_id,cust_name,cust_email from customers where cust_email is not null;
select * from customeremaillist;
174.select * from vendorlocations;
176.select prod_id,quantity,item_price, quantityitem_price as expanded_price from orderitems where order_num=20008;
176.create view orderitemsexpanded as select order_num, prod_id,quantity,item_price,quantityitem_price as expanded_price from orderitems;
select * from orderitemsexpanded where order_num=20008;

第19课
183.create procedure mailinglistcount(ListCount out integer) (oracle语法)
is
v_rows integer;
begin
select count() into v_rows from customers
where not cust_email is null;
listcount :=v_rows;
END;
183. var returnvalue number
exec mailinglistcount(:returnvalue);
select returnvalue;
184.（SQl server的T-sql语法）
create procedure mailinglistcount as
declare @cnt integer
select @cnt=count() from customers where not cust_email is null;
return @cnt;
184.（SQl server的T-sql语法）
declare @returnvalue int
execute @returnvalue=mailinglistcount;
select @returnvalue;
185.(带输入参数，输出参数的T-SQL存储过程)
create procedure neworder @cust_id char(10) 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;

declare @returnvalue int
execute @returnvalue=neworder 1000000005;
select @returnvalue;
186.（SQl server的T-sql语法）
create procedure neworder2 @cust_id char(10) as
insert into orders(order_date,cust_id) values(‘2021-10-27 16:15:37.493’,@cust_id)
select order_num=@@identity;
在我的SQL server中右击刚刚建立好的存储过程名称，点击修改在编辑框内展示出来的SQL语句如下：
USE [mytest]
GO
/****** Object: StoredProcedure [dbo].[neworder2] Script Date: 2021/10/27 17:16:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[neworder2] @cust_id char(10) as
insert into orders(order_date,cust_id) values(‘2021-10-27 16:15:37.493’,@cust_id)
select order_num=@@identity;

execute neworder2 1000000005;
第20课
193.set transaction delete orderitems where order_num=12345; delete orders where order_num=12345; commit;（该语句在命令行界面中必须放在一条语句中执行）
194.(SQL server内可执行)
begin transaction
insert into customers(cust_id,cust_name) values(‘1000000011’,‘Toys Emporium’);
save transaction startorder;
insert into orders(order_num,order_date,cust_id) values(20100,‘2001/12/1’,‘1000000011’);
if @@ERROR <>0 rollback transaction startorder;
insert into orderitems(order_num,order_item,prod_id,quantity,item_price) values(20100,1,‘BR01’,100,5.49);
if @@ERROR <> 0 rollback transaction startorder;
insert into orderItems(order_num,order_item,prod_id,quantity,item_price) values(20100,2,‘BR03’,100,10.99);
if @@ERROR <> 0 rollback transaction startorder;
commit transaction
第21课（只有SQL server的测试了,oracle的未测试）
sql server部分：
198.
declare custcursor cursor for
select * from customers where cust_email is null;
201.
declare @cust_id char(10),
@cust_name char(50),
@cust_address char(50),
@cust_city char(50),
@cust_state char(50),
@cust_zip char(10),
@cust_country char(50),
@cust_contact char(50),
@cust_email char(255);
open custcursor
fetch next from custcursor into
@cust_id ,@cust_name ,@cust_address ,@cust_city ,@cust_state ,
@cust_zip ,@cust_country ,@cust_contact ,@cust_email ;
select @cust_id ,@cust_name ,@cust_address ,@cust_city ,@cust_state ,@cust_zip ,@cust_country ,@cust_contact ,@cust_email ;
while @@FETCH_STATUS =0
begin
fetch next from custcursor into
@cust_id ,@cust_name ,@cust_address ,@cust_city ,@cust_state ,
@cust_zip ,@cust_country ,@cust_contact ,@cust_email;
select @cust_id ,@cust_name ,@cust_address ,@cust_city ,@cust_state ,@cust_zip ,@cust_country ,@cust_contact ,@cust_email ;
end
close custcursor
deallocate custcursor
oracle部分:
199.
declare custcursor cursor is
select * from customers where cust_email is null;
使用部分oracle部分:
declare type custcursor is ref cursor
return customers%ROWTYPE;
declare custrecord customers%ROWTYPE
begin
open custcursor;
fetch custcursor into custrecord;
close custcursor;
END;
200.
declare type custcursor is ref cursor
return customers%ROWTYPE;
declare custrecord customers%ROWTYPE
begin
open custcursor;
LOOP
fetch custcursor into custrecord;
exit when customers%NOTFOUND;
END LOOP;
close custcursor;
END;

第22课
205.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);
205.alter table vendors add constraint primary key(vend_id);
206.CREATE TABLE orders (order_num int(11) NOT NULL primary key, order_date datetime NOT NULL,cust_id char(10) NOT NULL references customers(cust_id));
207. alter table orders add constraint foreign key(cust_id) references customers(cust_id);
209.CREATE TABLE orderitems (order_num int(11) NOT NULL,order_item int(11) NOT NULL,prod_id char(10) NOT NULL,quantity int(11) NOT NULL check(quantity >0),item_price decimal(8,2) NOT NULL);
209.alter table customers add constraint check(gender like ‘[MF]’);
211.create index prod_name_ind on products(prod_name);