SQL Server SQL 笔记：查询语句

查询语句

格式:

select [ ALL | DISTINCT ]
[ TOP n [ PERCENT ]]
字段|* from 表名
where 查询条件
group by 分组条件
order by 排序规则
use pubs


--简单查询
select * from titles where type='business' --使用where子句做筛选，返回类型为商业书的记录

select * from titles where type='business' and pub_id>1000 --使用逻辑操作符进行组合条件的查询

--distinct
select distinct type from titles

-- 使用 order by
select * from titles order by pub_id

select * from titles order by pub_id asc --和上一句相同，升序排列

select * from titles order by pub_id desc --降序排列

-- 在查询中使用常量
select title_id+'-> '+type+' : '+title from titles

-- AS命名列名
select title_id+'-> '+type+' : '+title as '书籍信息' from titles

-- 使用标识列
格式

select identity(int,1,1) as id,title_id into t from titles

-- 使用Top子句限制查询返回行数
select top 3 * from titles
select top 50 percent * from titles

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
--聚合函数和分组查询 - slaes 表

select * from sales

--count
select count(*) as count from sales

select count(distinct(stor_id)) as count from sales

--sum
select sum(qty) as sumqty from sales

select * from sales where qty<10

select sum(qty) as sumqty from sales where qty<10

--avg

select avg(qty) as avgqty from sales

select avg(qty) as sumqty from sales where qty<10

--max
select max(qty) as maxqty from sales

--min
select min(qty) as minqty from sales

select min(qty) as sumqty from sales where qty<10

--group by:按照表的列对行进行分组，返回包含分组汇总信息的结果集，每组一行
--having :在分组或使用聚合函数后对行进行筛选

--选择出定书数目在10～25的记录，按定书数目分组，显示每个组中的记录个数
select * from sales where qty between 10 and 25 order by qty
select qty as '定书数目',count(qty) as '记录个数' from sales group by qty having qty between 10 and 25

--按定书数目分组，每组中定书数目总和大于60，显示每个组中的记录个数
select qty as '定书数目',count(qty) as '记录个数' from sales group by qty having sum(qty) >60

--按书店编号分组，显示每个组中的记录个数，显示每个书店的定书数目
select * from sales where qty<20 order by qty
select stor_id,count(*) as '记录个数',avg(qty) as '平均定书数目' from sales where qty<20 group by stor_id

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
--模糊查询
--上次课讲过

select * from sales

select * from sales where qty between 10 and 30
select * from sales where qty in (10,20,25)
select * from sales where stor_id is not null

=======================================================
课后习题
1.
select * from titles where royalty is not null

2.
select * from titles where advance > 7000

3.
select type,avg(price) from titles group by type

4.
select title_id,max(royaltyper)
from titleauthor where au_id like '[^8]%8%'
group by title_id --having au_id like '%'

select au_id from titleauthor group by title_id

select * from titleauthor

5.
select * from sales where year(ord_date) between 1993 and 1994 order by title_id desc

6.
select 40 percent * from sales

7.
select * from authors
select * from publishers

select * from authors,publishers where authors.city=publishers.city

select * from authors inner join publishers on authors.city=publishers.city

select * from authors cross join publishers where authors.city=publishers.city

select * from authors as a,publishers --as b where a.city=b.city