SQL Server 2000简单查询
1. 查询products表中p_price(商品价格)在800以上的商品详细信息,SQL代码如下所示:
USE eshop
SELECT *
FROM products
WHERE m_price > 800
2. 查询products表中p_quantity(商品数量)在20和50之间的商品编号、商品名称和商品数量,
SQL代码如下所示:
USE eshop
SELECT p_no, p_name, p_quantity
FROM products
WHERE p_quantity >= 20
AND p_quantity <= 50
或
USE eshop
SELECT p_no, p_name, p_quantity
FROM products
WHERE p_quantity BETWEEN 20 AND 50
3. 查询orders表中各会员购买商品的总量,并以汉字列标题形式输出会员帐号,商品总额,SQL代
码如下所示:
USE eshop
SELECT m_account 会员帐号, o_quantity 商品总额
FROM orders
4. 查询members表中家庭地址为“湖南”的会员详细信息,SQL代码如下所示:
USE eshop
SELECT *
FROM members
WHERE m_address LIKE '湖南%'
5. 查询members表中年龄大于30且性别为“男”的会员详细信息,SQL代码如下所示:
USE eshop
SELECT *
FROM members
WHERE DATEDIFF(YY,m_birth,GETDATE())>30
AND m_sex = '男'
6. 查询orders表各商品销售总量前3名的商品编号和销售总量,SQL代码如下所示:
USE eshop
SELECT TOP 3 p_no, o_quantity
FROM orders
ORDER BY o_quantity DESC
7. 查询orders表中购买过商品的会员帐号,要求去掉重复行,SQL代码如下所示:
USE eshop
SELECT DISTINCT m_account
FROM orders
8. 查询orders表已确认、已支付和已配送的订单详细信息,SQL代码如下所示:
USE eshop
SELECT *
FROM orders
WHERE o_confirm_state = '1'
AND o_pay_state = '1'
AND o_send_state = '1'
SQL Server 2000高级查询
1. 查询性别为“男”的会员详细信息,查询结果按月薪降序排列,SQL代码如下所示:
USE eshop
SELECT *
FROM members
WHERE m_sex = '男'
ORDER BY m_salary DESC
2. 查询全体会员的会员帐号,姓名和年龄并按家庭地址升序排列,同一地址中的会员按年龄降序排
列,SQL代码如下所示:
USE eshop
SELECT m_account, m_name, YEAR(GETDATE())-YEAR(m_birth) 年龄
FROM members
ORDER BY m_address, m_birth
或
USE eshop
SELECT m_account, m_name, DATEDIFF(YY, m_birth, GETDATE()) 年龄
FROM members
ORDER BY m_address, m_birth
3. 查询会员帐号为’liuzc’所购买的商品号和订购日期,并按订购日期升序排列,SQL代码如下所示:
USE eshop
SELECT p_no, o_date
FROM orders
WHERE m_account = 'liuzc'
ORDER BY o_date
4. 查询购买商品号为’0910810004’总人数,SQL代码如下所示:
USE eshop
SELECT COUNT(*)
FROM orders
WHERE p_no = '0910810004'
5. 查询20##年6月6日前,所有商品的订购总量,要求输出商品号和订购总量,SQL代码如下所
示:
USE eshop
SELECT p_no, SUM(o_quantity)
FROM orders
WHERE o_date < '20##-6-6'
GROUP BY p_no
6. 查询所有会员的平均月薪,最高月薪和最低月薪之和,SQL代码如下所示:
USE eshop
SELECT AVG(m_salary)+MAX(m_salary)+MIN(m_salary)
FROM members
7. 查询所有会员购买商品的种类和,要求输出会员号和商品种类和,SQL代码如下所示:
USE eshop
SELECT m_account, COUNT(DISTINCT p_no)
FROM orders
GROUP BY m_account
8. 查询各类商品的最高购买数量,要求输出最高数量大于10的商品号和最高数量,SQL代码如下
所示:
USE eshop
SELECT TOP 1 p_no, SUM(o_quantity)
FROM orders
GROUP BY p_no
HAVING SUM(o_quantity) > 10
ORDER BY SUM(o_quantity) DESC
SQL Server 2000联接查询
1. 查询购买了商品号为“0910810004”的会员号和姓名,并以汉字标题显示,SQL代码如下所示:
USE eshop
SELECT DISTINCT members.m_account 会员号, m_name 姓名
FROM members
JOIN orders
ON members.m_account = orders.m_account
WHERE p_no = '0910810004'
2. 查询购买了商品名称为“爱国者MP3”的会员号、姓名和商品价格,SQL代码如下所示:
USE eshop
SELECT members.m_account, m_name,p_price
FROM members
JOIN orders
ON members.m_account = orders.m_account
JOIN products
ON orders.p_no = products.p_no
AND p_name = '爱国者mp3'
3. 查询比“张自梁”月薪高的而和他不是同一地址的会员姓名和年龄,SQL代码如下所示:
USE eshop
SELECT A.m_name,YEAR(GETDATE())-YEAR(A.m_birth)
FROM members A
JOIN members B
ON A.m_account <> B.m_account
AND B.m_name = '张自梁'
AND A.m_salary > B.m_salary
AND A.m_address <> B.m_address
4. 使用exists查询购买了“0910810004”商品的会员号和姓名,SQL代码如下所示:
USE eshop
SELECT m_account, m_name
FROM members
WHERE EXISTS ( SELECT *
FROM orders
WHERE members.m_account = orders.m_account
AND p_no = '0910810004')
5. 使用in查询与“刘法治”购买至少同一种商品的会员号和商品号,SQL代码如下所示:
USE eshop
SELECT DISTINCT A.m_account, A.p_no
FROM orders A
WHERE p_no IN ( SELECT p_no
FROM orders B
WHERE A.m_account <> B.m_account
AND B.m_account IN (SELECT m_account
FROM members
WHERE B.m_account = members.m_account
AND m_name='刘法治'))
6. 使用简单查询家庭地址为“湖南株洲”的会员以及年龄在30岁以上的会员详细信息,SQL代码如
下所示:
USE eshop
SELECT *
FROM members
WHERE m_address = '湖南株洲'
SELECT *
FROM members
WHERE (YEAR(GETDATE())-YEAR(m_birth)) > 30
再按F5或点击工具栏上的运行按钮“”,查看运行结果,如图7-1所示。
7. 使用集合查询家庭地址为“湖南株洲”的会员以及年龄在30岁以上的会员详细信息,并与步骤6
进行比较,SQL代码如下所示:
USE eshop
SELECT *
FROM members
WHERE m_address = '湖南株洲'
UNION
SELECT *
FROM members
WHERE (YEAR(GETDATE())-YEAR(m_birth)) > 30
再按F5或点击工具栏上的运行按钮“”,查看运行结果,如图7-2所示。
8. 将members表和orders表之间的左向外联接包括所有会员的信息,包括没有购买商品的会员,SQL
代码如下所示:
USE eshop
SELECT members.*, orders.*
FROM members
LEFT OUTER JOIN orders
ON members.m_account = orders.m_account
SQL Server 2000的视图操作
1. 在members表中创建地址为“湖南株洲”的会员的视图V_addr,SQL代码如下所示:
CREATE VIEW V_addr
AS
SELECT *
FROM members
WHERE m_address = '湖南株洲'
2. 在orders表中创建购买了商品号为“0910810004”商品的视图V_buy,SQL代码如下所示:
CREATE VIEW V_buy
AS
SELECT *
FROM orders
WHERE p_no = '0910810004'
3. 在members和orders表上创建“湖南株洲”的会员购买了商品号为“0910810004”商品的视图
V_addr_buy,SQL代码如下所示:
CREATE VIEW V_addr_buy
AS
SELECT members.*
FROM members
JOIN orders
ON members.m_account = orders.m_account
AND p_no = '0910810004'
AND m_address = '湖南株洲'
4. 在视图V_addr上查询性别为“男”的会员信息,SQL代码如下所示:
USE eshop
SELECT *
FROM V_addr
WHERE m_sex = '男'
5. 在视图V_addr中增加一条记录(内容如下),并查看members表中记录的改变情况。记录内容如下:(T-SQL)
‘fengxk’,‘冯向克’,‘男’,‘1978-06-28’,‘北京市’,5000.0,‘fxk0628’
SQL代码如下所示:
USE eshop
INSERT INTO V_addr VALUES('fengxk', '冯向克', '男', '1978-06-28', '北京市',5000.0, ' fxk0628')
6. 将视图V_addr中会员号为“liuzc518”的会员的密码修改为“liuzc0518”,并查看members中记录
的改变情况,SQL代码如下所示:
USE eshop
UPDATE V_addr
SET m_password = 'liuzc0518'
WHERE m_password = 'liuzc518'
7. 在V_addr中删除会员号为“fengxk”的记录,并查看members中记录的改变情况,SQL代码如下
所示:
USE eshop
DELETE
FROM V_addr
WHERE m_account = 'fengxk'
8. 删除视图V_addr_buy、V_buy和V_addr,SQL代码如下所示:
USE eshop
DROP VIEW V_addr_buy, V_buy, V_addr
学生表S,课程表C和学生选课表SC,它们的结构如下,试用SQL完成下列操作。
S(S#,SN,SEX,AGE,DEPT)
C(C#,CN,TEACHER)
SC(S#,C#,GRADE)
1. 建立数据库:
CREATE DATABASE student
ON PRIMARY
(
NAME=student_dat,
FILENAME='E:\mydata\student_dat.mdf',
SIZE=10MB,
MAXSIZE=50MB,
FILEGROWTH=5MB
)
LOG ON
(
NAME=student_log,
FILENAME='E:\mydata\student_log.ldf',
SIZE=10MB,
MAXSIZE=50MB,
FILEGROWTH=5MB
)
GO
2. 创建表
USE student
CREATE TABLE S
(
S# VARCHAR(20),
SN VARCHAR(20),
SEX CHAR(2),
AGE INT,
DEPT VARCHAR(50)
)
GO
USE student
CREATE TABLE C
(
C# VARCHAR(20),
CN VARCHAR(20),
TEACHER VARCHAR(50)
)
GO
USE student
CREATE TABLE SC
(
S# VARCHAR(20),
C# VARCHAR(20),
GRADE INT
)
GO
USE student
CREATE TABLE SC_C
(
CNO VARCHAR(20),
CNAME VARCHAR(20),
AVG_GRADE INT
)
GO
⑴用SQL找出选修了“程军”老师教的所有课程的学生姓名。
SELECT DISTINCT SN FROM S
WHERE NOT EXISTS (SELECT * FROM C
WHERE C.TEACHER='程军' AND NOT EXISTS
(SELECT * FROM SC
WHERE
S.S#=SC.S# AND SC.C#=C.C#))
GO
用SQL找出选修了“程军”老师教的课程的学生姓名。
SELECT DISTINCT SN FROM S,SC,C
WHERE S.S#=SC.S# AND SC.C#=C.C# AND TEACHER='程军'
GO
⑵用SQL找出“程序设计”课程成绩在90分以上的学生姓名。
SELECT DISTINCT SN FROM S,SC,C
WHERE S.S#=SC.S# AND SC.C#=C.C# AND CN='程序设计' AND GRADE>90
GO
⑶检索所有比“王华”年龄大的学生姓名、年龄和性别。
SELECT DISTINCT SN,AGE,SEX FROM S
WHERE AGE>(SELECT AGE FROM S
WHERE SN='王华')
GO
⑷检索选修课程“C2”的学生中成绩最高的学生的学号。
SELECT S# FROM SC
WHERE C#='C2' AND GRADE>=ALL(SELECT GRADE FROM SC
WHERE C#='C2')
GO
或
SELECT S# FROM SC
WHERE C#='C2' AND GRADE=(SELECT MAX(GRADE) FROM SC
WHERE C#='C2')
GO
⑸检索学生姓名及其所选课程的课程号和成绩
SELECT SN,C#,GRADE FROM S,SC
WHERE S.S#=SC.S#
GO
⑹检索选修四门以上课程的学生总成绩(不统计不及格的课程),并要求按总成绩的降序排列出来。
SELECT SUM(GRADE) FROM SC
WHERE GRADE>=60
GROUP BY S# HAVING COUNT(*)>=4
ORDER BY SUM(GRADE) DESC
GO
⑺检索全是女同学选修的课程的课程号。
SELECT DISTINCT C# FROM SC,S
WHERE SC.S#=S.S# AND SEX='女'
GO
或
SELECT DISTINCT C# FROM SC
WHERE SC.S# IN (SELECT S.S# FROM S
WHERE SEX='女')
GO
⑻检索不学C6课程的男同学的学号和姓名(S#,SN)。
SELECT S#,SN FROM S
WHERE SEX='男' AND S# NOT IN(SELECT S# FROM SC
WHERE C#='C6')
GO
⑼把SC表中每门课程的平均成绩插入到另一个已存在的表SC_C(CNO,CNAME,AVG_GRADE)中。
INSERT INTO SC_C(CNO,CNAME,AVG_GRADE)
SELECT C.C#,CN,AVG(GRADE) FROM SC,C
WHERE SC.C#=C.C#
GROUP BY C.C#,CN
GO
⑽从SC表中把吴老师的女学生选课元组删除。
DELETE FROM SC
WHERE S# IN (SELECT S# FROM S
WHERE SEX='女')
AND C# IN (SELECT C# FROM C
WHERE TEACHER LIKE '吴%')
GO
⑾从SC表中把数学课程中低于数学平均成绩的选课元组删去。
DELETE FROM SC
WHERE GRADE<(SELECT AVG(GRADE) FROM SC,C
WHERE SC.C#=C.C# AND CN='数学'
GROUP BY SC.C#)
GO
⑿把吴老师的女学生选课成绩增加4%。
UPDATE SC
SET GRADE=GRADE+GRADE*0.04
WHERE S# IN(SELECT S# FROM S WHERE SEX='女')
AND C# IN(SELECT C# FROM C WHERE TEACHER LIKE '吴%')
GO
课本知识点总结
P86 页20题
将下图的教务管理数据库的E-R图,转化为关系模型
P87 页23题
P124 例5—7由学生、课程和选课3个表,定义一个计算机系的学生成绩视图,其属性包括学号、姓名、课程名和成绩
CREATE VIEW 学生成绩(学号,姓名,课程名,成绩)
AS SELECT 学生.学号,学生.姓名,课程.课程名,选课.成绩
FORM 学生,课程,选课
WHERE学生.学号=选课. 学号AND课程.课程号=选课.课程号 AND学生.所在系=’计算机系’;
P124 例5—8 将学生的学号、总成绩、平均成绩定义成一个视图
CREATE VIEW 学生成绩统计(学号,总成绩,平均成绩)
AS SELECT 学号,SUM(成绩),AVG(成绩)
FROM 选课
GROUP BY 学号;
P128 例5—11求选修C1课程的学生的学号和成绩,并要求对查询结果按成绩的降序排列,如果成绩相同,则按学号的升序排列
SELECT 学号,成绩
FROM 选课
WHERE 课程号=‘C1’
ORDER BY 成绩DESC,学号 ASC;
P128 例5—13求数学系或计算机系姓张的学生的信息
SELECT *
FROM 学生
WHERE 所在系 IN (‘数学系’,‘计算机系’)AND 姓名 LIKE ‘张%’;
P212 习题9
P199 例6-30 设在读者表中增加了“借书册数”字段,现要求统计借书者在1998年~1999年间所借书的册数,并将结果送入读者表中的借书册数字段。
UPDATE 读者
SET 借书册数=(SELECT COUNT(*)
FROM 读者,借阅
WHERE 借者.书证号=借阅.读者书证号AND借阅日期
BETWEEN ‘1998-01-01’AND‘1999-12-31’)
P199 例6-31删除单位是计算机系的读者借阅记录。
DELETE 借阅
WHERE 读者书证号 IN(SELETE 书证号FROM 读者 WHERE 单位=‘计算机系’)
在选课表中插入学号为98011,课程号为c10,成绩为90的记录
insert
into 选课
values ('98011','c10',null);
insert
into 学生
values('10228','shenyim','20','na','jsj')
select distinct *
from 学生where 姓名='shenyim'
update 学生
set 年龄='22'
update 学生
set 姓名='sdf'
where 学号='10228'
删除学生表
delete
from 学生
insert
into
学生
values('10220','sds','20','na','sjuj')
P239 习题9