sql常用查询语句

select AVG(price) as 价格 from Book group by bookName having bookName='泰戈尔诗集'/*根据书名分组 查询书名为'泰戈尔诗集'的平均价格 */
select top 1 bookName from Book where price=56/*选价格为56的前1条记录  */
select top 5 * from Book order by NEWID()/*随机取5条记录*/
select * from Book union(select * from dbo.Books)/*两表相同的数据只显示一条 加ALL全显示*/
select * from Book  except(select * from dbo.Books)/*去除前一张表与后一张表相同的数据 显示前一张表不相同部分 */
select * from Book intersect(select * from dbo.Books)/*显示两表相同的数据*/
select SUM(price) from Book/*表中的价格总和*/
select bookName from Book where price between 50 and 100/*查询表中50到100的书*/
SELECT bookName,totalPrice FROM Book INNER JOIN Item ON Book.bid=Item.bid/*查询两表相同ID的书*/
SELECT * FROM Book left JOIN Item ON Book.bid=Item.bid/*左(右right)外连接查询*/
Select bookName from Book where price>(select AVG(price) from Book)/*查询单价大于所有均价的书*/
select COUNT(*) from Book group by bookName  /*用书名分组 反回相同的书的数量 可用having加查询条件*/
select top 3 * from book  where bid not in(select top 4 bid from book  )order by price,bookName desc /*子查询  分页 并排序*/
select b.bid,i.iid,b.bookname from book as b join item as i on i.bid=b.bid/*连接查询 外键表查主键表*/
insert into book values('小说',29,'image',33);select @@IDENTITY/*显示新插入的ID值*/
insert into book values('乔布斯',45,'images',44)
 select DISTINCT @@IDENTITY  from book/*同上*/
insert into book values('java',33,'images',33)
 select DISTINCT @@IDENTITY  from book
 create view bo as select price from book where price>=40/*创建视图*/
 select * from bo/*查看视图*/
 drop view bo/*删除视图*/
backup database mydb to disk='d:..' with init/*数据备份  导出*/
restore database mydb from disk='d:..'/*数据导入*/

delete from book where bid=24

/*触发器实例  修改触发器 alter */

alter  trigger aa on book AFTER  insert,delete
as
declare
@str varchar(20)
select @str=bookname from book
print @str
print @@identity

/*触发器实例*/
 create   trigger  inserttag  on dbo.favorite  after insert
as
declare 
@str varchar(10),
@str2  varchar(10),
@index int,
@count int
select  @str2  = f_tags  from  inserted  where  f_id=@@identity
while(1=1)
begin
select @str= left( @str2,charindex(',', @str2)-1)  from  inserted  where f_id  = @@identity
select @count = count(*)  from  dbo.tag where   t_name=@str
if(@count=0)
insert into  tag  values(@str,1)
else
update tag  set  t_count = t_count+1  where  t_name=@str
select @str2= substring(@str2,charindex(',', @str2)+1,len(f_tags))  from inserted where  f_id= @@identity
select  @index  =  charindex(',',@str2)
if(@index  < 1)
begin
set  @count=0
select @count = count(*)  from  dbo.tag where   t_name=@str2
if(@count=0)
insert into  tag  values(@str2,1)
else
update tag  set  t_count = t_count+1  where  t_name=@str2
break
end
end