课堂演示.sql

主要介绍了sql的基础教程

--HAVING与WHERE的区别
--查询得到本年度截止到目前的销售额超过 $40,000 的出版商
SELECT pub_id,SUM(ytd_sales) as total 
FROM titles
GROUP BY pub_id
where SUM(ytd_sales) > 40000
SELECT pub_id,SUM(ytd_sales) as total 
FROM titles
GROUP BY pub_id
HAVING SUM(ytd_sales) > 40000
--================================================================
--Group by中all选项的含义
SELECT type, AVG(price) as 平均价格
FROM titles
WHERE royalty = 10
GROUP BY type
SELECT type, AVG(price) as 平均价格
FROM titles
WHERE royalty = 10
GROUP BY ALL type
--======================================================================
--Group by 中的Cube与rollup选项
create database sample
go
use sample
go
create table Inventory
(
	Item varchar(15),
	Color varchar(15),
	Quantity  int
) 
go
insert into Inventory values('table','blue',124)
insert into Inventory values('table','red',224)
insert into Inventory values('chair','blue',201)
insert into Inventory values('chair','red',310)
--按Item和Color分组计算Quantity的和
SELECT Item, Color, SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color
--使用with cube
SELECT Item, Color, SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE
--使用with rollup
SELECT Item, Color, SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH ROLLUP
--===============================================================================
--Compute与compute by
--统计出所有书的总价格和总的预付款项
SELECT type, price, advance FROM titles 
ORDER BY type 
COMPUTE SUM(price), SUM(advance)
--统计出每类图书的总价格和总的预付款项
SELECT type, price, advance FROM titles
ORDER BY type
COMPUTE SUM(price), SUM(advance) BY type
--================================================================================
--between和in的用法
--查询出版日期在1991-1-1到1991-12-31之间的书名和出版日期
--写法一：
select title,pubdate from titles where pubdate>='1991-1-1' and pubdate<='1991-12-31'
--写法二：
select title,pubdate from titles where pubdate between '1991-1-1' and '1991-12-31'
--选择名称和州的列表，列表中列出所有居住在CA、IN或MD州的作者。
select * from authors where state in('CA','IN','MD')
--=================================================================================
--通配符和转义字符的用法
--查找authors表中所有以415开头的电话号码。
select phone from authors where phone like '415%'
--查找名字为Chery1或Shery1的作者 
select au_fname from authors where au_fname like '[CS]heryl'
--查找名称以A~F以外的字符开头的出版社名。
--写法一：
select * from publishers where pub_name like '[^A-F]%'
--写法二：
select * from publishers where pub_name like '[^ABCDEF]%'
--查找以‘MA_’开头的作者名称
select au_fname from authors where au_fname like 'MA\_%' ESCAPE '\'
--=======================================================================
--联合查询
use pubs
go
select pub_id as ID,pub_name as name
from publishers
where pub_name  like '[a-d]%'
union
select stor_id,stor_name
from stores
where stor_name like '[e-z]%'
order by ID
--=======================================================================
--联接查询
--先切换库
use sample
go
--在样例库中创建表student备用
create table student
(
	stu_id int primary key,
	name varchar(20),
	sex char(2),
	age int check (age between 0 and 150)
)
go
--在样例库中创建表score备用
create table score
(
	stu_id int,
	score int,
	course varchar(50)
)
go
--在两表中插入数据备用
insert into student values (1001,'张三','男',21)
insert into student values (1002,'李四','男',22)
insert into student values (1003,'李文','女',20)
insert into student values (1004,'赵云','女',21)
insert into student values (1005,'吴用','男',23)
insert into score values (1001,80,'SQL')
insert into score values (1001,72,'VB')
insert into score values (1005,68,'SQL')
insert into score values (1008,90,'java')
--交叉联接
select * from student
cross join score
select * from student,score
--内联接
select * from student a
inner join score b
on a.stu_id=b.stu_id
select * from student a,score b
where a.stu_id=b.stu_id
--左外联接
select * from student a
left outer join score b
on a.stu_id=b.stu_id
--右外联接
select * from student a
right outer join score b
on a.stu_id=b.stu_id
--全外联接
select * from student a
full outer join score b
on a.stu_id=b.stu_id
--====================================================================
--自联接
--使用自联接列出同在一个州内的所有书店名称及其地址
select distinct a.stor_name,a.city,a.stor_address
from stores a inner join stores b
on a.state=b.state
where a.stor_id<>b.stor_id
order by 1
--=====================================================================
--子查询
--嵌套子查询：查找至少曾参与编写一本热门计算机书的作者姓名。
USE pubs
SELECT au_lname, au_fname
FROM authors
WHERE au_id IN
(SELECT au_id
FROM titleauthor
WHERE title_id IN
(SELECT title_id
FROM titles
WHERE type = 'popular_comp'))
--[NOT]IN查询：查询列出Oakland籍作者所著图书
select distinct title
from titles,titleauthor
where au_id in 
(
	select au_id
	from authors
	where city='Oakland'
)
and titles.title_id=titleauthor.title_id
--[NOT]exists查询：查询检索New Moon Books出版社出版的图书
select title
from titles
where exists
(select * from publishers
where pub_name = 'New Moon Books'
and titles.pub_id = publishers.pub_id)
--exists与in
select title from titles
where pub_id in
(
	select pub_id from publishers
	where pub_name = 'New Moon Books'
)
--比较子查询：查找价格大于所有商业类图书价格的图书，列出其名称和价格
select title,price from titles
where price >all
(
	select price from titles
	where type='business'
)
--相关子查询：查找获得某本书 100 % 共享版税 (royaltyper) 的所有作者名。
 SELECT au_lname, au_fname FROM authors 
WHERE 100 IN 
(
	SELECT royaltyper FROM titleauthor 
	WHERE titleauthor.au_ID = authors.au_id
)