数据库sql语句和重要知识点总结.docx
《数据库sql语句和重要知识点总结.docx》由会员分享，可在线阅读，更多相关《数据库sql语句和重要知识点总结.docx（23页珍藏版）》请在冰豆网上搜索。
数据库sql语句和重要知识点总结
SQLServer2000简单查询
1.查询products表中p_price（商品价格）在800以上的商品详细信息，SQL代码如下所示：
USEeshop
SELECT*
FROMproducts
WHEREm_price>800
2.查询products表中p_quantity（商品数量）在20和50之间的商品编号、商品名称和商品数量，
SQL代码如下所示：
USEeshop
SELECTp_no,p_name,p_quantity
FROMproducts
WHEREp_quantity>=20
ANDp_quantity<=50
或
USEeshop
SELECTp_no,p_name,p_quantity
FROMproducts
WHEREp_quantityBETWEEN20AND50
3.查询orders表中各会员购买商品的总量，并以汉字列标题形式输出会员帐号，商品总额，SQL代
码如下所示：
USEeshop
SELECTm_account会员帐号,o_quantity商品总额
FROMorders
4.查询members表中家庭地址为“湖南”的会员详细信息，SQL代码如下所示：
USEeshop
SELECT*
FROMmembers
WHEREm_addressLIKE'湖南%'
5.查询members表中年龄大于30且性别为“男”的会员详细信息，SQL代码如下所示：
USEeshop
SELECT*
FROMmembers
WHEREDATEDIFF（YY,m_birth,GETDATE（））>30
ANDm_sex='男'
6.查询orders表各商品销售总量前3名的商品编号和销售总量，SQL代码如下所示：
USEeshop
SELECTTOP3p_no,o_quantity
FROMorders
ORDERBYo_quantityDESC
7.查询orders表中购买过商品的会员帐号，要求去掉重复行，SQL代码如下所示：
USEeshop
SELECTDISTINCTm_account
FROMorders
8.查询orders表已确认、已支付和已配送的订单详细信息，SQL代码如下所示：
USEeshop
SELECT*
FROMorders
WHEREo_confirm_state='1'
ANDo_pay_state='1'
ANDo_send_state='1'
SQLServer2000高级查询
1.查询性别为“男”的会员详细信息，查询结果按月薪降序排列，SQL代码如下所示：
USEeshop
SELECT*
FROMmembers
WHEREm_sex='男'
ORDERBYm_salaryDESC
2.查询全体会员的会员帐号，姓名和年龄并按家庭地址升序排列，同一地址中的会员按年龄降序排
列，SQL代码如下所示：
USEeshop
SELECTm_account,m_name,YEAR（GETDATE（））-YEAR（m_birth）年龄
FROMmembers
ORDERBYm_address,m_birth
或
USEeshop
SELECTm_account,m_name,DATEDIFF（YY,m_birth,GETDATE（））年龄
FROMmembers
ORDERBYm_address,m_birth
3.查询会员帐号为’liuzc’所购买的商品号和订购日期，并按订购日期升序排列，SQL代码如下所示：
USEeshop
SELECTp_no,o_date
FROMorders
WHEREm_account='liuzc'
ORDERBYo_date
4.查询购买商品号为’0910810004’总人数，SQL代码如下所示：
USEeshop
SELECTCOUNT（*）
FROMorders
WHEREp_no='0910810004'
5.查询2005年6月6日前，所有商品的订购总量，要求输出商品号和订购总量，SQL代码如下所
示：
USEeshop
SELECTp_no,SUM（o_quantity）
FROMorders
WHEREo_date<'2005-6-6'
GROUPBYp_no
6.查询所有会员的平均月薪，最高月薪和最低月薪之和，SQL代码如下所示：
USEeshop
SELECTAVG（m_salary）+MAX（m_salary）+MIN（m_salary）
FROMmembers
7.查询所有会员购买商品的种类和，要求输出会员号和商品种类和，SQL代码如下所示：
USEeshop
SELECTm_account,COUNT（DISTINCTp_no）
FROMorders
GROUPBYm_account
8.查询各类商品的最高购买数量，要求输出最高数量大于10的商品号和最高数量，SQL代码如下
所示：
USEeshop
SELECTTOP1p_no,SUM（o_quantity）
FROMorders
GROUPBYp_no
HAVINGSUM（o_quantity）>10
ORDERBYSUM（o_quantity）DESC
SQLServer2000联接查询
1.查询购买了商品号为“0910810004”的会员号和姓名，并以汉字标题显示，SQL代码如下所示：
USEeshop
SELECTDISTINCTmembers.m_account会员号,m_name姓名
FROMmembers
JOINorders
ONmembers.m_account=orders.m_account
WHEREp_no='0910810004'
2.查询购买了商品名称为“爱国者MP3”的会员号、姓名和商品价格，SQL代码如下所示：
USEeshop
SELECTmembers.m_account,m_name,p_price
FROMmembers
JOINorders
ONmembers.m_account=orders.m_account
JOINproducts
ONorders.p_no=products.p_no
ANDp_name='爱国者mp3'
3.查询比“张自梁”月薪高的而和他不是同一地址的会员姓名和年龄，SQL代码如下所示：
USEeshop
SELECTA.m_name,YEAR（GETDATE（））-YEAR（A.m_birth）
FROMmembersA
JOINmembersB
ONA.m_account<>B.m_account
ANDB.m_name='张自梁'
ANDA.m_salary>B.m_salary
ANDA.m_address<>B.m_address
4.使用exists查询购买了“0910810004”商品的会员号和姓名，SQL代码如下所示：
USEeshop
SELECTm_account,m_name
FROMmembers
WHEREEXISTS（SELECT*
FROMorders
WHEREmembers.m_account=orders.m_account
ANDp_no='0910810004'）
5.使用in查询与“刘法治”购买至少同一种商品的会员号和商品号，SQL代码如下所示：
USEeshop
SELECTDISTINCTA.m_account,A.p_no
FROMordersA
WHEREp_noIN（SELECTp_no
FROMordersB
WHEREA.m_account<>B.m_account
ANDB.m_accountIN（SELECTm_account
FROMmembers
WHEREB.m_account=members.m_account
ANDm_name='刘法治'））
6.使用简单查询家庭地址为“湖南株洲”的会员以及年龄在30岁以上的会员详细信息，SQL代码如
下所示：
USEeshop
SELECT*
FROMmembers
WHEREm_address='湖南株洲'
SELECT*
FROMmembers
WHERE（YEAR（GETDATE（））-YEAR（m_birth））>30
再按F5或点击工具栏上的运行按钮“
”，查看运行结果，如图7-1所示。
7.使用集合查询家庭地址为“湖南株洲”的会员以及年龄在30岁以上的会员详细信息，并与步骤6
进行比较，SQL代码如下所示：
USEeshop
SELECT*
FROMmembers
WHEREm_address='湖南株洲'
UNION
SELECT*
FROMmembers
WHERE（YEAR（GETDATE（））-YEAR（m_birth））>30
再按F5或点击工具栏上的运行按钮“
”，查看运行结果，如图7-2所示。
8.将members表和orders表之间的左向外联接包括所有会员的信息，包括没有购买商品的会员，SQL
代码如下所示：
USEeshop
SELECTmembers.*,orders.*
FROMmembers
LEFTOUTERJOINorders
ONmembers.m_account=orders.m_account
SQLServer2000的视图操作
1.在members表中创建地址为“湖南株洲”的会员的视图V_addr，SQL代码如下所示：
CREATEVIEWV_addr
AS
SELECT*
FROMmembers
WHEREm_address='湖南株洲'
2.在orders表中创建购买了商品号为“0910810004”商品的视图V_buy，SQL代码如下所示：
CREATEVIEWV_buy
AS
SELECT*
FROMorders
WHEREp_no='0910810004'
3.在members和orders表上创建“湖南株洲”的会员购买了商品号为“0910810004”商品的视图
V_addr_buy，SQL代码如下所示：
CREATEVIEWV_addr_buy
AS
SELECTmembers.*
FROMmembers
JOINorders
ONmembers.m_account=orders.m_account
ANDp_no='0910810004'
ANDm_address='湖南株洲'
4.在视图V_addr上查询性别为“男”的会员信息，SQL代码如下所示：
USEeshop
SELECT*
FROMV_addr
WHEREm_sex='男'
5.在视图V_addr中增加一条记录（内容如下），并查看members表中记录的改变情况。
记录内容如下：
（T-SQL）
‘fengxk’，‘冯向克’，‘男’，‘1978-06-28’，‘北京市’，5000.0，‘fxk0628’
SQL代码如下所示：
USEeshop
INSERTINTOV_addrVALUES（'fengxk','冯向克','男','1978-06-28','北京市',5000.0,'fxk0628'）
6.将视图V_addr中会员号为“liuzc518”的会员的密码修改为“liuzc0518”，并查看members中记录
的改变情况，SQL代码如下所示：
USEeshop
UPDATEV_addr
SETm_password='liuzc0518'
WHEREm_password='liuzc518'
7.在V_addr中删除会员号为“fengxk”的记录，并查看members中记录的改变情况，SQL代码如下
所示：
USEeshop
DELETE
FROMV_addr
WHEREm_account='fengxk'
8.删除视图V_addr_buy、V_buy和V_addr，SQL代码如下所示：
USEeshop
DROPVIEWV_addr_buy,V_buy,V_addr
学生表S，课程表C和学生选课表SC，它们的结构如下，试用SQL完成下列操作。
S（S#，SN，SEX，AGE，DEPT）
C（C#，CN，TEACHER）
SC（S#，C#，GRADE）
学生表S，课程表C和学生选课表SC，它们的结构如下，试用SQL完成下列操作。
S（S#，SN，SEX，AGE，DEPT）
C（C#，CN，TEACHER）
SC（S#，C#，GRADE）
1.建立数据库：
CREATEDATABASEstudent
ONPRIMARY
（
NAME=student_dat,
FILENAME='E:
\mydata\student_dat.mdf',
SIZE=10MB,
MAXSIZE=50MB,
FILEGROWTH=5MB
）
LOGON
（
NAME=student_log,
FILENAME='E:
\mydata\student_log.ldf',
SIZE=10MB,
MAXSIZE=50MB,
FILEGROWTH=5MB
）
GO
2.创建表
USEstudent
CREATETABLES
（
S#VARCHAR（20）,
SNVARCHAR（20）,
SEXCHAR
（2）,
AGEINT,
DEPTVARCHAR（50）
）
GO
USEstudent
CREATETABLEC
（
C#VARCHAR（20）,
CNVARCHAR（20）,
TEACHERVARCHAR（50）
）
GO
USEstudent
CREATETABLESC
（
S#VARCHAR（20）,
C#VARCHAR（20）,
GRADEINT
）
GO
USEstudent
CREATETABLESC_C
（
CNOVARCHAR（20）,
CNAMEVARCHAR（20）,
AVG_GRADEINT
）
GO
⑴用SQL找出选修了“程军”老师教的所有课程的学生姓名。
SELECTDISTINCTSNFROMS
WHERENOTEXISTS（SELECT*FROMC
WHEREC.TEACHER='程军'ANDNOTEXISTS
（SELECT*FROMSC
WHERE
S.S#=SC.S#ANDSC.C#=C.C#））
GO
用SQL找出选修了“程军”老师教的课程的学生姓名。
SELECTDISTINCTSNFROMS,SC,C
WHERES.S#=SC.S#ANDSC.C#=C.C#ANDTEACHER='程军'
GO
⑵用SQL找出“程序设计”课程成绩在90分以上的学生姓名。
SELECTDISTINCTSNFROMS,SC,C
WHERES.S#=SC.S#ANDSC.C#=C.C#ANDCN='程序设计'ANDGRADE>90
GO
⑶检索所有比“王华”年龄大的学生姓名、年龄和性别。
SELECTDISTINCTSN,AGE,SEXFROMS
WHEREAGE>（SELECTAGEFROMS
WHERESN='王华'）
GO
⑷检索选修课程“C2”的学生中成绩最高的学生的学号。
SELECTS#FROMSC
WHEREC#='C2'ANDGRADE>=ALL（SELECTGRADEFROMSC
WHEREC#='C2'）
GO
或
SELECTS#FROMSC
WHEREC#='C2'ANDGRADE=（SELECTMAX（GRADE）FROMSC
WHEREC#='C2'）
GO
⑸检索学生姓名及其所选课程的课程号和成绩
SELECTSN,C#,GRADEFROMS,SC
WHERES.S#=SC.S#
GO
⑹检索选修四门以上课程的学生总成绩（不统计不及格的课程），并要求按总成绩的降序排列出来。
SELECTSUM（GRADE）FROMSC
WHEREGRADE>=60
GROUPBYS#HAVINGCOUNT（*）>=4
ORDERBYSUM（GRADE）DESC
GO
⑺检索全是女同学选修的课程的课程号。
SELECTDISTINCTC#FROMSC,S
WHERESC.S#=S.S#ANDSEX='女'
GO
或
SELECTDISTINCTC#FROMSC
WHERESC.S#IN（SELECTS.S#FROMS
WHERESEX='女'）
GO
⑻检索不学C6课程的男同学的学号和姓名（S#，SN）。
SELECTS#,SNFROMS
WHERESEX='男'ANDS#NOTIN（SELECTS#FROMSC
WHEREC#='C6'）
GO
⑼把SC表中每门课程的平均成绩插入到另一个已存在的表SC_C（CNO,CNAME,AVG_GRADE）中。
INSERTINTOSC_C（CNO,CNAME,AVG_GRADE）
SELECTC.C#,CN,AVG（GRADE）FROMSC,C
WHERESC.C#=C.C#
GROUPBYC.C#,CN
GO
⑽从SC表中把吴老师的女学生选课元组删除。
DELETEFROMSC
WHERES#IN（SELECTS#FROMS
WHERESEX='女'）
ANDC#IN（SELECTC#FROMC
WHERETEACHERLIKE'吴%'）
GO
⑾从SC表中把数学课程中低于数学平均成绩的选课元组删去。
DELETEFROMSC
WHEREGRADE<（SELECTAVG（GRADE）FROMSC,C
WHERESC.C#=C.C#ANDCN='数学'
GROUPBYSC.C#）
GO
⑿把吴老师的女学生选课成绩增加4%。
UPDATESC
SETGRADE=GRADE+GRADE*0.04
WHERES#IN（SELECTS#FROMSWHERESEX='女'）
ANDC#IN（SELECTC#FROMCWHERETEACHERLIKE'吴%'）
GO
课本知识点总结
P86页20题
将下图的教务管理数据库的E-R图，转化为关系模型
P87页23题
P124例5—7由学生、课程和选课3个表，定义一个计算机系的学生成绩视图，其属性包括学号、姓名、课程名和成绩
CREATEVIEW学生成绩（学号，姓名，课程名，成绩）
ASSELECT学生.学号，学生.姓名，课程.课程名，选课.成绩
FORM学生，课程，选课
WHERE学生.学号=选课.学号AND课程.课程号=选课.课程号AND学生.所在系=’计算机系’；
P124例5—8将学生的学号、总成绩、平均成绩定义成一个视图
CREATEVIEW学生成绩统计（学号，总成绩，平均成绩）
ASSELECT学号，SUM（成绩），AVG（成绩）
FROM选课
GROUPBY学号;
P128例5—11求选修C1课程的学生的学号和成绩，并要求对查询结果按成绩的降序排列，如果成绩相同，则按学号的升序排列
SELECT学号，成绩
FROM选课
WHERE课程号=‘C1’
ORDERBY成绩DESC，学号ASC;
P128例5—13求数学系或计算机系姓张的学生的信息
SELECT*
FROM学生
WHERE所在系IN（‘数学系’，‘计算机系’）AND姓名LIKE‘张%’；
P212习题9
P199例6-30设在读者表中增加了“借书册数”字段，现要求统计借书者在1998年~1999年间所借书的册数，并将结果送入读者表中的借书册数字段。
UPDATE读者
SET借书册数=（SELECTCOUNT（*）
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'）
selectdistinct*
from学生where姓名='shenyim'
update学生
set年龄='22'
update学生
set姓名='sdf'
where学号='10228'
删除学生表
delete
from学生
insert
into
学生
values（'10220','sds','20','na','sjuj'）
P239习题9
升级会员 