中国交通建设监理协会网站,合肥智能建站模板,家里笔记本做网站 怎么解析,校园网站建设招标公告例题#xff1a;
创建如下两张表 分别命名为books和persons #xff08;1#xff09;按照书名#xff0c;姓名的顺序列出字里包含‘德’字的人物的姓名#xff0c;书名和字。
select name 姓名,bookname 书名,style 字
from books,persons
where style like %德%
and bo…例题
创建如下两张表 分别命名为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.bookidbooks.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时两者的查询结果是一样的 补充
count1也是统计行数
对于count1和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.bookidpersons.bookid
and name 唐僧)
and books.bookidpersons.bookid
and sex女; (7) 有女性人物但是女性人物最少的书
select distinct bookname书名author作者
from books.persons
where books.bookidpersons.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))
