【例6.13】从样例数据库pubs的titles表中查询书价(price列)大于15的所有图书。
USE pubs
SELECT title,price
FROM titles
WHERE price>15
运行结果:
title price
--------------------------------------------------------------------------------
The Busy Executive's Database Guide 19.9900
Straight Talk About Computers 19.9900
Silicon Valley Gastronomic Treats 19.9900
But Is It User Friendly? 22.9500
Secrets of Silicon Valley 20.0000
Computer Phobic AND Non-Phobic Individuals,Behavior Variations 21.5900
Prolonged Data Deprivation,Four Case Studies 19.9900
Onions,Leeks,and Garlic,Cooking Secrets of the Mediterranean 20.9500
(所影响的行数为 8 行)
查询结果显示表titles中书价大于15的所有的图书。
【例6.14】从样例数据库pubs的titles表中查询书籍类型(type列)为business的所有图书。
USE pubs
SELECT title,type
FROM titles
WHERE type='business'
运行结果:
title type
-------------------------------------------------------------------
The Busy Executive's Database Guide business
Cooking with Computers,Surreptitious Balance Sheets business
You Can Combat Computer Stress! business
Straight Talk About Computers business
(所影响的行数为 4 行)
查询结果显示表titles中书籍类型(type列)为business的所有图书。
【例6.15】从样例数据库pubs的titles表中查询书价(price列)在15与20之间的所有图书。
USE pubs
SELECT title,price
FROM titles
WHERE price BETWEEN 15 AND 20
运行结果
title price
-----------------------------------------------------------
The Busy Executive's Database Guide 19.9900
Straight Talk About Computers 19.9900
Silicon Valley Gastronomic Treats 19.9900
Secrets of Silicon Valley 20.0000
Prolonged Data Deprivation,Four Case Studies 19.9900
(所影响的行数为 5 行)
查询结果显示表titles中书价在15与20之间的所有图书。
【例6.16】从样例数据库pubs的titles表中查询书价(price列)不在15与20之间的所有图书。
USE pubs
SELECT title,price
FROM titles
WHERE price NOT BETWEEN 15 AND 20
title price
------------------------------------------------------------------------
Cooking with Computers,Surreptitious Balance Sheets 11.9500
You Can Combat Computer Stress! 2.9900
The Gourmet Microwave 2.9900
...
...
Fifty Years in Buckingham Palace Kitchens 11.9500
Sushi,Anyone? 14.9900
(所影响的行数为 11 行)
查询结果显示表titles中书价不在15与20之间的所有图书。
【例6.17】从样例数据库pubs的titles表中查询书籍类型为business和psychology的所有图书。
USE pubs
SELECT title,type
FROM titles
WHERE type IN ('business','psychology')
运行结果:
title type
-----------------------------------------------------------------------------
The Busy Executive's Database Guide business
Cooking with Computers,Surreptitious Balance Sheets business
You Can Combat Computer Stress! business
...
...
Prolonged Data Deprivation,Four Case Studies psychology
Emotional Security,A New Algorithm psychology
(所影响的行数为 9 行)
查询结果显示表titles中书籍类型为business和psychology的所有图书。
【例6.18】从样例数据库pubs的titles表中查询书籍类型不为business和psychology的所有图书。
USE pubs
SELECT title,type
FROM titles
WHERE type NOT IN ('business','psychology')
运行结果:
title type
-----------------------------------------------------------
Silicon Valley Gastronomic Treats mod_cook
The Gourmet Microwave mod_cook
The Psychology of Computer Cooking UNDECIDED
...
...
Fifty Years in Buckingham Palace Kitchens trad_cook
Sushi,Anyone? trad_cook
(所影响的行数为 9 行)
查询结果显示表titles中书籍类型不为business和psychology的所有图书。
【例6.19】从样例数据库pubs的titles表中查询书名(title列)以S开头的的所有图书。
USE pubs
SELECT title,type
FROM titles
WHERE title LIKE 'S%'
运行结果:
title type
---------------------------------------------------------
Secrets of Silicon Valley popular_comp
Silicon Valley Gastronomic Treats mod_cook
Straight Talk About Computers business
Sushi,Anyone? trad_cook
(所影响的行数为 4 行)
查询结果显示表titles中书名以S开头的的所有图书。
【例6.20】从样例数据库pubs的titles表中查询书名(title列)以S开头,书名的第2个字符为u或i的所有图书。
USE pubs
SELECT title,type
FROM titles
WHERE title LIKE 'S[iu]%'
运行结果:
title type
-----------------------------------------------------
Silicon Valley Gastronomic Treats mod_cook
Sushi,Anyone? trad_cook
(所影响的行数为 2 行)
查询结果显示表titles中书名以S开头,书名的第2个字符为u或i的所有图书。
【例6.21】从样例数据库pubs的titles表中查询书名(title列)以S开头,书名的第3个字符为l的所有图书。
USE pubs
SELECT title,type
FROM titles
WHERE title LIKE 'S_l%'
运行结果
title type
-------------------------------------------------
Silicon Valley Gastronomic Treats mod_cook
(所影响的行数为 1 行)
查询结果显示表titles中书名以S开头,书名的第3个字符为l的所有图书。
【例6.22】从样例数据库pubs的titles表中查询书名(title列)不是以S开头的的所有图书。
USE pubs
SELECT title,type
FROM titles
WHERE title NOT LIKE 'S%'
运行结果:
title type
----------------------------------------------------------------------------
The Busy Executive's Database Guide business
Cooking with Computers,Surreptitious Balance Sheets business
You Can Combat Computer Stress! business
...
...
Onions,Leeks,and Garlic,Cooking Secrets of the Mediterranean trad_cook
Fifty Years in Buckingham Palace Kitchens trad_cook
(所影响的行数为 14 行)
查询结果显示表titles中书名不是以S开头的的所有图书。