常用SQL语句收集整理之二

1、基础知识：
（1）SQL子句 FROM,WHERE,GROUP BY,HAVING,ORDER BY
（2）SQL运算符 AND,OR,NOT,BETWEEN,LIKE,IN,<,>,<=,>=,=,<>
（3）SQL合计函数 AVG,COUNT,SUM,MAX,MIN

2、数据操作语言（Data Manipulation Language）
（1）SELECT命令（Nwind数据库）
Select * from orders
Select orderid as 编号 ,shipname as 船号 from orders
Select orderid,customerid from orders where orderid<100
Select * from orders order by orderdate desc
Select orderid,freight as 静重,freight*1.1 as 毛重
Select shipname as 船号 from orders where shipname like "d*"
Select shipname + "是来自"+ shipaddress as 货运信息
Select * from orders where shipname like "a*" or shipname like "d*"
（2）Biblio数据库
Select pubid,[company name] from publishers
Select author,2001-[year born] as 年龄 from publishers
Select count(*) as 作者数,avg(2001-[year born]) as 平均年龄 from publishers
Select name,state from p where state in('ca','nj')
Select [year published] from titles where [year published] between 1996 and 1998
Select top 5 * from publishers
Selct top 10 percent * from publishers
Select max([year published]) as 最晚出版年份，min([year published]) as 最早出版年份，avg([year published]) as 平均出版年份
（3）分组与多表查询
select distinct city from publishers where not isnull(city)
select top 10 [year published],count(*) as 年度文章数 from titles group by [year published] order by [year published] desc
select * from titles where pubid in (select pubid from publishers where state in ('ca','tx')
select [year published],count(*) as 年度文章数 from titles group by [year published] having count(*)>50
（4）INSERT命令
Insert into publishers(name) values('kkk')
（5）UPDATE命令
Update employees set salary=salary+1000 where salary<500
（6）DELETE命令
Delete from titles where [year published]<1950
（7）SELECT INTO命令
Select Into [publisher titles] from publishers.name,titles.title From publishers inner join titles On publishers.pubid=titles.pubid
3、数据定义语言（Data Define Language）
CREATE命令，ALTER命令，DROP命令