基础查询上机实验
1、从表employees中查询所有雇员的employeeid、lastname、firstname和title字段的值
use northwind
select EmployeeID,LastName,FirstName,Title
from employees
go
2、从表employees中查询字段employeeid、lastname、firstname和title,
查询条件为employeid等于5的雇员
use northwind
select EmployeeID,LastName,FirstName,Title from employees
where EmployeeID=5
go
3、查询产品表里的所有信息
use northwind
select * from products
go
4、查询学生成绩数据(xscj)库学生情况表(xs)中的所有列。
use xscj
select * from xs
go
5、在xscj数据库中,查询课程表课程号、课程名、学时、学分
use xscj
select 课程号,课程名,学时,学分
from kc
go
6、在xscj数据库中,从学生表(xs)中查询所有学生的学号、姓名和专业名
use xscj
select 学号,姓名,专业名
from xs
go
7、在xscj数据库中,查询xs表中姓名为李明的相关信息
use xscj
select * from xs
where 姓名='李明'
go
8、在xscj数据库中,查询xs表中出生日期在1978-3-26以后的学生
use xscj
select * from xs
where 出生时间>'1978-3-26'
go
---------------------------------------------------
***使用比较操作符
9、查询表employees中所有居住在美国的职员的姓和居住的城市
use northwind
select FirstName,LastName,City
from employees
where Country='usa'
go
10、从表orders中,查询订购日期在1996年8月1日后的所有订单的orderid和customerid
use northwind
select orderid,customerid
from orders
where OrderDate>'1996-8-1'
go
11、在xscj数据库中,查找成绩大于85分的所有人的信息
use xscj
select * from xs_kc
where 成绩>85
go
use xscj
select * from xs_kc
join xs
on xs_kc.学号=xs.学号
where 成绩>85
go
-----------------------------------------------------
***使用字符串比较符(模糊查找)
下面的表中演示了几个在LIKE搜索条件中使用通配符的例子。
表达式 返回值
‘BR%’ 一个以BR开头的字符串
LIKE ‘Br%’ 一个以Br开头的字符串
LIKE ‘%een’ 一个以een结尾的字符串
LIKE ‘%en%’ 一个包含en的字符串
11、查找产品名中含有‘tofu’的产品的所有信息
use northwind
select * from products
where productname like '%tofu%'
go
12、在xscj数据库中,查找学生姓名以‘李’开头的所有学生信息
use xscj
select * from xs
where 姓名 like '李%'
go
13、在xscj数据库中,查找学生姓名以’王’或’刘’或’ 李’开头的学生信息
use xscj
select * from xs
where 姓名 like '[王刘李]%'
go
14、在xscj数据库中,查找 姓名不以’刘’或’李’ 开头的学生信息
use xscj
select * from xs
where 姓名 like '[^刘李]%'
go
--------------------------------------
***使用逻辑操作符
使用逻辑操作符时,遵循下列指导原则:
使用AND返回满足所有条件的行;
使用OR返回满足任一条件的行:
15、查找产品名以字母“T”开头或产品标识为46的产品且产品印价格大于16美元
use northwind
select * from dbo.Products
where productname like 't%' or productid=46 and UnitPrice>16
go
16、在xscj数据库中,求姓名以’王’开头或总学分为50,且专业名为计算机的学生姓名
use xscj
select * from xs
where (姓名 like '王%' or 总学分=50) and 专业名='计算机'
go
17、在xscj数据库中,求姓名以’王’开头,或总学分为50且专业名为计算机的学生姓名
use xscj
select * from xs
where 姓名 like '王%' or 总学分=50 and 专业名='计算机'
go
-------------------------------------------
***查询在一定范围内的值
Between … and …的用法
18、 将查询所有的产品中的产品名字和单价,且此产品的单价在10美元到20美元之间
use northwind
select ProductName,UnitPrice from dbo.Products
where UnitPrice between 10 and 20
go
19、在xscj数据库中,查找总学分为60和70之间的学生所有信息
use xscj
select * from xs
where 总学分 between 60 and 70
go
20、在xscj数据库中,查找出生日期不在1980-8-26到1979-1-29的学生姓名(用你所知道的时间表示方法,
不少与4种)
use xscj
select 姓名, 出生时间 from xs
where 出生时间 not between '1979-1-29' and '1980-8-26'
go
--我尝试了其余几种总是查询结果总是不对,我用到了子查询一种,in,exists
--包括我去掉''和不去掉'',查询的结果都不一定,请老师解答为什么会是这样?
---------------------------------------------------
****In(结果集)的用法
21、 表suppliers中搜索位于日本和意大利的所有公司
use northwind
select CompanyName
from dbo.Suppliers
where Country in ('Japan','Italy')
go
22、 把第18题改为逻辑运算
将查询所有的产品中的产品名字和单价,且此产品的单价在10美元到20美元之间
use northwind
select ProductName,UnitPrice from dbo.Products
where UnitPrice>=10 and unitprice<=20
go
---------------------------------------------------
***查询未知的值
使用IS NULL搜索条件来查询某指定列没有任何信息的行
23、 查询表suppliers中字段fax含有空值的所有公司
use northwind
select * from suppliers
where fax is null
go
24、在xscj数据库中,查询备注为空的学生姓名
use xscj
select * from xs
where 备注 is null
go
-----------------------------------------------------
***给数据排序
ORDER BY子句为结果集中的行排序时,有升序(ASC)和降序(DESC)两种
25 、将查询表products中每个产品的产品标识、产品名、类别(categoryid)和单价(unitprice
)。默认情况下,结果集按照类别的升序进行排列,类别相同时,行按照单价的降序进行排列。
use northwind
select ProductID,ProductName,CategoryID,UnitPrice
from products
order by CategoryID,UnitPrice desc
go
26、在xscj数据库中,查找学生的总学分以升序排列,出生日期以降序排列的学生姓名和学号
use xscj
select * from xs
order by 总学分,出生时间 desc
go
---------------------------------------------------------
***消除重复的行
如果你需要结果集列出唯一的值,可以用DISTINCT消除重复的行
27、 将查询表suppliers中国家,但每个国家名字只显示一次。
use northwind
select distinct Country
from suppliers
go
28、查询xscj数据库中xs表中的总学分,但是不能有重复的
use xscj
select distinct 总学分 from xs
go
----------------------------------------------------
****改变字段的名字
关键字AS可以把默认的字段名用别名代替
29、 从表employees中查找一些雇员。给字段firstname、lastname和employeeid指定了各自的别名
use northwind
select FirstName fn,LastName ln,EmployeeID eid
from employees
go
30、在xscj数据库中的xs表中对姓名取别名为name,专业名为speciality,总学分为total
use xscj
select 姓名 name,专业名 as speciality,总学分 total
from xs
go
31、在xscj数据库中的xs表中对姓名取别名为name,专业名为speciality,并在补一列为性别,
并取别名为sex
use xscj
select 姓名 name,专业名 speciality ,性别 sex
from xs
go
-----------------------------------------------------------
****用TOPn列出前n个记录
32、订购数量最多的前5个记录
使用了关键字TOPn和WITH TIES子句返回与第N条记录并列的
use northwind
select top 5 orderid
from orders
go
use northwind
select top 5 with ties 5 orderid
from orders
order by orderid
go
33、它们列出了一次订购中,订购数量最多的5个产品
use northwind
select top 6 with ties o.quantity,p.productname
from [order details] o
join products p
on o.ProductID=p.ProductID
order by quantity desc
go
34、查询在xscj库中的xs表中总学分最高的前5项的学生姓名
use xscj
select top 5 总学分
from xs
go
35、求学分最高的5位学生的信息,并且返回与第5并列的学生信息
use xscj
select top 5 with ties 总学分 from xs
order by 总学分 desc
go
-------------------------------------------------------------
****使用聚合函数
36、计算表products中所有产品单价的平均值
use northwind
select avg(UnitPrice) 所有产品平均值 from products
go
37、计算表orderdetails中所有行的quantity字段的总和
use northwind
select sum(quantity) from dbo.[Order Details]
go
38、 列出表employees中的记录数
use northwind
select count(*)
from dbo.Employees
go
39、 列出了表employees中字段region不为空的记录数
use northwind
select count(region)
from dbo.Employees
go
40、在xscj数据库中,求选修206课程的学生的总成绩
use xscj
select sum(成绩)
from xs_kc
where 课程号='206'
go
41、在xscj数据库中,求选修101课程的学生的平均成绩
use xscj
select avg(成绩)
from xs_kc
where 课程号='101'
go
42、在xscj数据库中,求学生的总人数
use xscj
select count(学号)
from xs
go
43、在xscj数据库中,求选修了课程的学生总人数
use xscj
select count(x.学号)
from xs_kc x
go
--不明白这句话的意思
44、在xscj数据库中,求选修206课程的学生的最高分
use xscj
select max(成绩) from xs_kc
where 课程号='206'
go
45、在xscj数据库中,按照字母顺序,返回xs表中排在最后面的学生姓名
use xscj
select min(姓名)
from xs
--order by
select * from xs
46、在xscj数据库中,求选修206课程的学生的最低分
use xscj
select min(成绩)
from xs_kc
where 课程号='206'
go
1.在northwind中查找所在城市为Seattle或Redmond 的员工(employees表)lastname,
firstname,把lastname 和firstname组合后重命名以姓名显示
use northwind
select LastName+' '+FirstName
from dbo.Employees
where city in ('Seattle','London','Redmond')
--where city='Seattle' or city='London' or city='Redmond'
go
2.在customers表中查找公司名(companyname)中含有单词‘restaurant’的公司名(companyname)
use northwind
select CompanyName
from dbo.Customers
where CompanyName like '%restaurant%'
go
3.查找出生年月(birthdate)在1948-1-1和1960-12-1之间的员工的姓名(lastname+firstname),
雇佣时间(hiredate),地址(address)和所在的城市(city)
use northwind
select LastName+' '+FirstName,HireDate,Address,City
from dbo.Employees
where BirthDate between '1948-1-1' and '1960-12-1'
go
4.查找国家(country)是美国(usa)或欧洲(uk)的员工的所有信息(employees)
use northwind
select * from dbo.Employees
where Country in ('usa','uk')
go
5.找出产品表中单价(unitprice)在18以上,并且库存(unitinstock)在48以下,
或者产品名称(productname)以a结尾的相关信息,并将库存(unitinstock)按照升序排序。
use northwind
select * from dbo.Products
where (UnitPrice>18 and UnitsInStock<48) or ProductName like '%a'
order by UnitsInStock asc
go
6.查找产品表(products)中单价(unitprice)在18到25之间的产品名(productname),
单价(unitprice)信息,并将所查信息按单价以降序排序。
use northwind
select * from products
where UnitPrice between 18 and 25
order by UnitPrice desc
go
7.查找供应商表(suppliers)中传真号(fax)为空的所有信息。
use northwind
select * from dbo.Suppliers
where fax is null
go
8.查找产品表(products)中的产品名(ProductName),种类id(categoryid),单价(unitprice),
供应商id(supplierid)的信息,并将查询结果按照首选项种类id(categoryid)升序排列,
次选项单价(unitprice)降序排列。
use northwind
select ProductName,CategoryID,UnitPrice,SupplierID
from products
order by CategoryID,UnitPrice desc
go
9.查找供应商表(suppliers)中的国家(country)列,并消除重复记录,并将查询结果按降序排序。
use northwind
select distinct Country
from dbo.Suppliers
order by Country desc
go
1、从表employees中查询所有雇员的employeeid、lastname、firstname和title字段的值
use northwind
select EmployeeID,LastName,FirstName,Title
from employees
go
2、从表employees中查询字段employeeid、lastname、firstname和title,
查询条件为employeid等于5的雇员
use northwind
select EmployeeID,LastName,FirstName,Title from employees
where EmployeeID=5
go
3、查询产品表里的所有信息
use northwind
select * from products
go
4、查询学生成绩数据(xscj)库学生情况表(xs)中的所有列。
use xscj
select * from xs
go
5、在xscj数据库中,查询课程表课程号、课程名、学时、学分
use xscj
select 课程号,课程名,学时,学分
from kc
go
6、在xscj数据库中,从学生表(xs)中查询所有学生的学号、姓名和专业名
use xscj
select 学号,姓名,专业名
from xs
go
7、在xscj数据库中,查询xs表中姓名为李明的相关信息
use xscj
select * from xs
where 姓名='李明'
go
8、在xscj数据库中,查询xs表中出生日期在1978-3-26以后的学生
use xscj
select * from xs
where 出生时间>'1978-3-26'
go
---------------------------------------------------
***使用比较操作符
9、查询表employees中所有居住在美国的职员的姓和居住的城市
use northwind
select FirstName,LastName,City
from employees
where Country='usa'
go
10、从表orders中,查询订购日期在1996年8月1日后的所有订单的orderid和customerid
use northwind
select orderid,customerid
from orders
where OrderDate>'1996-8-1'
go
11、在xscj数据库中,查找成绩大于85分的所有人的信息
use xscj
select * from xs_kc
where 成绩>85
go
use xscj
select * from xs_kc
join xs
on xs_kc.学号=xs.学号
where 成绩>85
go
-----------------------------------------------------
***使用字符串比较符(模糊查找)
下面的表中演示了几个在LIKE搜索条件中使用通配符的例子。
表达式 返回值
‘BR%’ 一个以BR开头的字符串
LIKE ‘Br%’ 一个以Br开头的字符串
LIKE ‘%een’ 一个以een结尾的字符串
LIKE ‘%en%’ 一个包含en的字符串
11、查找产品名中含有‘tofu’的产品的所有信息
use northwind
select * from products
where productname like '%tofu%'
go
12、在xscj数据库中,查找学生姓名以‘李’开头的所有学生信息
use xscj
select * from xs
where 姓名 like '李%'
go
13、在xscj数据库中,查找学生姓名以’王’或’刘’或’ 李’开头的学生信息
use xscj
select * from xs
where 姓名 like '[王刘李]%'
go
14、在xscj数据库中,查找 姓名不以’刘’或’李’ 开头的学生信息
use xscj
select * from xs
where 姓名 like '[^刘李]%'
go
--------------------------------------
***使用逻辑操作符
使用逻辑操作符时,遵循下列指导原则:
使用AND返回满足所有条件的行;
使用OR返回满足任一条件的行:
15、查找产品名以字母“T”开头或产品标识为46的产品且产品印价格大于16美元
use northwind
select * from dbo.Products
where productname like 't%' or productid=46 and UnitPrice>16
go
16、在xscj数据库中,求姓名以’王’开头或总学分为50,且专业名为计算机的学生姓名
use xscj
select * from xs
where (姓名 like '王%' or 总学分=50) and 专业名='计算机'
go
17、在xscj数据库中,求姓名以’王’开头,或总学分为50且专业名为计算机的学生姓名
use xscj
select * from xs
where 姓名 like '王%' or 总学分=50 and 专业名='计算机'
go
-------------------------------------------
***查询在一定范围内的值
Between … and …的用法
18、 将查询所有的产品中的产品名字和单价,且此产品的单价在10美元到20美元之间
use northwind
select ProductName,UnitPrice from dbo.Products
where UnitPrice between 10 and 20
go
19、在xscj数据库中,查找总学分为60和70之间的学生所有信息
use xscj
select * from xs
where 总学分 between 60 and 70
go
20、在xscj数据库中,查找出生日期不在1980-8-26到1979-1-29的学生姓名(用你所知道的时间表示方法,
不少与4种)
use xscj
select 姓名, 出生时间 from xs
where 出生时间 not between '1979-1-29' and '1980-8-26'
go
--我尝试了其余几种总是查询结果总是不对,我用到了子查询一种,in,exists
--包括我去掉''和不去掉'',查询的结果都不一定,请老师解答为什么会是这样?
---------------------------------------------------
****In(结果集)的用法
21、 表suppliers中搜索位于日本和意大利的所有公司
use northwind
select CompanyName
from dbo.Suppliers
where Country in ('Japan','Italy')
go
22、 把第18题改为逻辑运算
将查询所有的产品中的产品名字和单价,且此产品的单价在10美元到20美元之间
use northwind
select ProductName,UnitPrice from dbo.Products
where UnitPrice>=10 and unitprice<=20
go
---------------------------------------------------
***查询未知的值
使用IS NULL搜索条件来查询某指定列没有任何信息的行
23、 查询表suppliers中字段fax含有空值的所有公司
use northwind
select * from suppliers
where fax is null
go
24、在xscj数据库中,查询备注为空的学生姓名
use xscj
select * from xs
where 备注 is null
go
-----------------------------------------------------
***给数据排序
ORDER BY子句为结果集中的行排序时,有升序(ASC)和降序(DESC)两种
25 、将查询表products中每个产品的产品标识、产品名、类别(categoryid)和单价(unitprice
)。默认情况下,结果集按照类别的升序进行排列,类别相同时,行按照单价的降序进行排列。
use northwind
select ProductID,ProductName,CategoryID,UnitPrice
from products
order by CategoryID,UnitPrice desc
go
26、在xscj数据库中,查找学生的总学分以升序排列,出生日期以降序排列的学生姓名和学号
use xscj
select * from xs
order by 总学分,出生时间 desc
go
---------------------------------------------------------
***消除重复的行
如果你需要结果集列出唯一的值,可以用DISTINCT消除重复的行
27、 将查询表suppliers中国家,但每个国家名字只显示一次。
use northwind
select distinct Country
from suppliers
go
28、查询xscj数据库中xs表中的总学分,但是不能有重复的
use xscj
select distinct 总学分 from xs
go
----------------------------------------------------
****改变字段的名字
关键字AS可以把默认的字段名用别名代替
29、 从表employees中查找一些雇员。给字段firstname、lastname和employeeid指定了各自的别名
use northwind
select FirstName fn,LastName ln,EmployeeID eid
from employees
go
30、在xscj数据库中的xs表中对姓名取别名为name,专业名为speciality,总学分为total
use xscj
select 姓名 name,专业名 as speciality,总学分 total
from xs
go
31、在xscj数据库中的xs表中对姓名取别名为name,专业名为speciality,并在补一列为性别,
并取别名为sex
use xscj
select 姓名 name,专业名 speciality ,性别 sex
from xs
go
-----------------------------------------------------------
****用TOPn列出前n个记录
32、订购数量最多的前5个记录
使用了关键字TOPn和WITH TIES子句返回与第N条记录并列的
use northwind
select top 5 orderid
from orders
go
use northwind
select top 5 with ties 5 orderid
from orders
order by orderid
go
33、它们列出了一次订购中,订购数量最多的5个产品
use northwind
select top 6 with ties o.quantity,p.productname
from [order details] o
join products p
on o.ProductID=p.ProductID
order by quantity desc
go
34、查询在xscj库中的xs表中总学分最高的前5项的学生姓名
use xscj
select top 5 总学分
from xs
go
35、求学分最高的5位学生的信息,并且返回与第5并列的学生信息
use xscj
select top 5 with ties 总学分 from xs
order by 总学分 desc
go
-------------------------------------------------------------
****使用聚合函数
36、计算表products中所有产品单价的平均值
use northwind
select avg(UnitPrice) 所有产品平均值 from products
go
37、计算表orderdetails中所有行的quantity字段的总和
use northwind
select sum(quantity) from dbo.[Order Details]
go
38、 列出表employees中的记录数
use northwind
select count(*)
from dbo.Employees
go
39、 列出了表employees中字段region不为空的记录数
use northwind
select count(region)
from dbo.Employees
go
40、在xscj数据库中,求选修206课程的学生的总成绩
use xscj
select sum(成绩)
from xs_kc
where 课程号='206'
go
41、在xscj数据库中,求选修101课程的学生的平均成绩
use xscj
select avg(成绩)
from xs_kc
where 课程号='101'
go
42、在xscj数据库中,求学生的总人数
use xscj
select count(学号)
from xs
go
43、在xscj数据库中,求选修了课程的学生总人数
use xscj
select count(x.学号)
from xs_kc x
go
--不明白这句话的意思
44、在xscj数据库中,求选修206课程的学生的最高分
use xscj
select max(成绩) from xs_kc
where 课程号='206'
go
45、在xscj数据库中,按照字母顺序,返回xs表中排在最后面的学生姓名
use xscj
select min(姓名)
from xs
--order by
select * from xs
46、在xscj数据库中,求选修206课程的学生的最低分
use xscj
select min(成绩)
from xs_kc
where 课程号='206'
go
1.在northwind中查找所在城市为Seattle或Redmond 的员工(employees表)lastname,
firstname,把lastname 和firstname组合后重命名以姓名显示
use northwind
select LastName+' '+FirstName
from dbo.Employees
where city in ('Seattle','London','Redmond')
--where city='Seattle' or city='London' or city='Redmond'
go
2.在customers表中查找公司名(companyname)中含有单词‘restaurant’的公司名(companyname)
use northwind
select CompanyName
from dbo.Customers
where CompanyName like '%restaurant%'
go
3.查找出生年月(birthdate)在1948-1-1和1960-12-1之间的员工的姓名(lastname+firstname),
雇佣时间(hiredate),地址(address)和所在的城市(city)
use northwind
select LastName+' '+FirstName,HireDate,Address,City
from dbo.Employees
where BirthDate between '1948-1-1' and '1960-12-1'
go
4.查找国家(country)是美国(usa)或欧洲(uk)的员工的所有信息(employees)
use northwind
select * from dbo.Employees
where Country in ('usa','uk')
go
5.找出产品表中单价(unitprice)在18以上,并且库存(unitinstock)在48以下,
或者产品名称(productname)以a结尾的相关信息,并将库存(unitinstock)按照升序排序。
use northwind
select * from dbo.Products
where (UnitPrice>18 and UnitsInStock<48) or ProductName like '%a'
order by UnitsInStock asc
go
6.查找产品表(products)中单价(unitprice)在18到25之间的产品名(productname),
单价(unitprice)信息,并将所查信息按单价以降序排序。
use northwind
select * from products
where UnitPrice between 18 and 25
order by UnitPrice desc
go
7.查找供应商表(suppliers)中传真号(fax)为空的所有信息。
use northwind
select * from dbo.Suppliers
where fax is null
go
8.查找产品表(products)中的产品名(ProductName),种类id(categoryid),单价(unitprice),
供应商id(supplierid)的信息,并将查询结果按照首选项种类id(categoryid)升序排列,
次选项单价(unitprice)降序排列。
use northwind
select ProductName,CategoryID,UnitPrice,SupplierID
from products
order by CategoryID,UnitPrice desc
go
9.查找供应商表(suppliers)中的国家(country)列,并消除重复记录,并将查询结果按降序排序。
use northwind
select distinct Country
from dbo.Suppliers
order by Country desc
go