sql（数据库语言） 复习

select * from publishers select country from publishers select distinct country from publishers

Select discounttype,'lowqty+50'=lowqty+50 from discounts   --查询前2行记录 Select top 2 * from discounts --返回前20%个结果 Select top 20 percent * from discounts

Select * from discounts

--显示titles表中price字段的值大于20的记录 select * from titles where price>20 Select title,price from titles where price>20

select * from titles where price>21

select distinct advance from titles select * from titles

--查询价格在10~30之间的图书 Select title,price from titles where price between 10 and 30

--查询在德国和法国的出版社（publishers表中pub_name字段是出版社名称，country字段存放国家信息） --select pub_name from publishers where country = germany    --错误--

Select pub_name,country from publishers where country in('Germany','France') Select pub_name,country from publishers where country = 'France' Select pub_name,country from publishers where country = ('Germany') select * from publishers

--注:以下示例用publishers表中的pub_name(出版社名称)字段 

--以publishing结尾的出版社 select pub_name from publishers where pub_name like '%publishing' --以A开头的出版社 select pub_name from publishers where pub_name like '[A]%' --不以A开头的出版社 select pub_name from publishers where pub_name like '[^A]%' --第二个字母是i的出版社 select pub_name from publishers where pub_name like '_i%'

--使用空值判断查找没有定价的图书（title表中price字段存放价格） select title,price from titles where price is NULL

查询书价低于10或高于15，且销售大于5000的图书(titles表中ytd_sales是存放销售额的字段) select title,price,ytd_sales from titles select title,price,ytd_sales from titles where select title,price,ytd_sales from titles  where (price>15 or price<10) and ytd_sales>5000

--列出titles表中类别(type)为’business’类的图书，结果按价格(price)由高到低、书名(title)由低到高显示 select * from titles select title,type,price from titles where type='business' order by price desc,title asc Select title,price from titles where type='business' Order by price desc,title asc

--从titles表中查出类型（type列）为business的图书的平均价格（price列为价格） select avg(price) from titles where type='business'

--统计各类图书的统计价格 select type,avg(price)  from titles group by type

--统计除undecided以外的各类图书的平均价格 select type,avg(price) from titles group by type having type<>'undecided'

--扩展内容：给统计结果排序 --按图书类别统计平均价格，并按平均价格排序 select type,avg(price)  from titles group by type having type<>'undecided' order by avg(price) desc

--明细统计titles表中popular_comp类图书的平均价格和价格总和 select title,price from titles where type='popular_comp' compute avg(price),sum(price) select * from titles select type,pub_id,price from titles where type like '[a-d]% 'order by type,pub_id compute avg(price) by type,pub_id

--等值内连接 --查询authors表和publishers表中位于同一个城市的作者和出版社 select * from authors as a inner join publishers as p on a.city=p.city

--在选择表中删除authors表和publishers表中的重复列（city和state） select a.*,pub_id,pub_name,country from authors as a inner join publishers as p on a.city=p.city

以下示例使用AdventureWorks库 --在EmployeeAddress表中列出所有住在Bthell城市里的员工的EmployeeID Select EmployeeID from HumanResources.EmplyeeAddress where AddressID in (select AddressID from Person.Address where city='Bothell')

--列出市场部员工（即departmentID=4）的EmplyeeID和Title Select employeeid,title from HumanResources.Emplyee where exists (select *　from HumanResources.EmployeeDepartmentHistory where employeeid= HumanResources.Emplyee.emplyeeid and departmentid=4)

--列出pubs库titles表中书价大于平均值的图书及价格 Select title,price from titles where price>(select avg(price) from titles) order by price

 

--查询一次订购量在50本以上的图书 Select title from titles where title_id=any(select title_id from sales where qty>50)