1 use Test01
2 select * from bm go
3 select count(* ) as '列数'from bm --指定别名
4 select distinct bmname ,bmid from bm --去重复(后面一起筛选，两个字段加起来没重复就ok)
5 select count( distinct bmname) from bm --计算去重后的行数
6 select * from bm order by bmname desc,bmid desc --取降序排名前三
7
8 select count(*)as '列数',bmname,sum(bmid)as 'id和' from bm group by bmname --根据bmname分组并统计id和
9 --（如果在返回集字段中，这些字段要么就要包含在Group By语句的后面，作为分组的依据；要么就要被包含在聚合函数中。）
10 select booker from book group by bookname,booker --分组要同时满足,号前后
11 select sum(bookid),bbmid from book group by bbmid having sum(bookid)=4 --having相当于where，唯一不同where子句不能包含聚合函数
12
13 select top 4 *from (select top 3 *from bm order by bmid desc) a where bmid between 3 and 4-- 注意from 和 where后的参数
14 select top 4 *from (select top 3 *from bm order by bmid desc) a where bmid in (4,3) --between 后面的参数要先小后大，ex：3 and 4. 4，3就查不出。
15
16 select *from bm full join book on bm.bmid = book.bbmid--查两张表全部信息(完全外联)
17 select bmname as '图书室',bookid as '图书编号',bookname as '图书名',booker as '作者'from bm inner join book on bm.bmid = book.bbmid --内联接(显示符合条件的行)
18 select bmname as '图书室',bookid as '图书编号',bookname as '图书名',booker as '作者'from bm left join book on bm.bmid = book.bbmid where bmid<2 --左外联接(显示符合条件的行，和JION左边表的所有行)只显示了一行
19 select bmname as '图书室',bookid as '图书编号',bookname as '图书名',booker as '作者'from bm right join book on bm.bmid = book.bbmid --右外联接(显示符合条件的行，和JION右边表的所有行)当然显示所有的右边行要满足 where 条件,必须是右边表的字段，否则返回内联结果。
20 select bmname as '图书室',bookid as '图书编号',bookname as '图书名',booker as '作者'from bm left join book on bm.bmid = book.bbmid and bm.bmname='理科图书室' --可以用AND避免上面的情况，结果和每条件的外联一样。
21
22 insert into bm(bmid,bmname) values (9,'综合图书室') --增
23 update bm set bmname='计算机图书室' where bmid=8 --修
24 delete from bm where bmid=9 --删
