数据库sql语句（count（*）和count（字段））

例题：

创建如下两张表 分别命名为books和persons

 

（1）按照书名，姓名的顺序列出字里包含‘德’字的人物的姓名，书名和字。

select name 姓名,bookname 书名,style 字
from books,persons
where style like '%德%'
and books.bookid = persons.bookid
order by 2,1;

order by 2,1：先按照书名排序，书名相同再按照姓名排序：一般是按照首字母排序

 

（2）哪本书中的人物姓名包含最多‘悟’字？
第一种表示方法：

select bookname 书名
from books,persons
where persons.bookid=books.bookid
and name like '%悟%'
group by bookname
having count(books.bookid)=(select top 1 count(bookid)
from persons
where name like '%悟%'
order by count(bookid) desc)

第二种表示方法：

select bookname 书名 from books
where bookid in(
select bookid from persons
where name like'%悟%’
group by bookid
having count(*) =(
select top (1) count(*) from persons
where name like'%悟%’
group by bookid
order by 1 desc))

count(字段)和count(*)的区别：

count(*) 是统计行数，

count(字段) 是统计字段列非null的行数

在查询字段非null时，两者的查询结果是一样的

补充：

count（1）也是统计行数

对于count（1）和count（*）返回的结果是一样的，但是两者的效率在不同的情况下不同：

如果表中没有主键 ，使用count(1)比count(*)快；

如果有主键，那么count(主键)最快

详细的内容，推荐这篇：http://t.csdn.cn/AiVUl

(3)人物最多的书的男性人物的姓名和字是什么

select name 姓名,style 字
from persons
where sex='男'
and bookid in(select bookid from persons
group by bookid 
having count(bookid) =(select top 1 count(bookid) from persons
group by bookid
order by count(bookid) desc))

 

(4)哪本书的人物姓名都是三个字的

select bookname 书名
from books
where bookid not in(select bookid from persons
where name not in(select name from persons where len(name)=3))

（5）女性人物最多的书是哪个朝代的

select dynasty 朝代
from books
where bookid in (
select bookid from persons
where sex ='女’
group by bookid
having count(*)=(
select top(1) count(*) from persons
where sex='女
group by bookid
order by 1 desc

 

 

（6）与唐僧在同一本书的女性人物是谁？

select name 姓名 from books,persons
where books.bookid in(select books bookid from books,persons
where books.bookid=persons.bookid
and name ='唐僧')
and books.bookid=persons.bookid
and sex='女';

(7) 有女性人物但是女性人物最少的书：

select distinct bookname书名，author作者
from books.persons
where books.bookid=persons.bookid
and books.bookid in 
select bookid from persons
where sex ='女'
group by bookid
having count(*) =(
select top(1) count(*)
from persons
where sex='女'
group by bookid
order by 1 asc))