数据库sql语句和重要知识点总结Word格式文档下载.docx

数据库sql语句和重要知识点总结Word格式文档下载.docx

《数据库sql语句和重要知识点总结Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《数据库sql语句和重要知识点总结Word格式文档下载.docx（23页珍藏版）》请在冰豆网上搜索。

WHEREo_confirm_state='

1'

ANDo_pay_state='

ANDo_send_state='

SQLServer2000高级查询

1.查询性别为“男”的会员详细信息，查询结果按月薪降序排列，SQL代码如下所示：

WHEREm_sex='

ORDERBYm_salaryDESC

2.查询全体会员的会员帐号，姓名和年龄并按家庭地址升序排列，同一地址中的会员按年龄降序排

列，SQL代码如下所示：

SELECTm_account,m_name,YEAR（GETDATE（））-YEAR（m_birth）年龄

ORDERBYm_address,m_birth

SELECTm_account,m_name,DATEDIFF（YY,m_birth,GETDATE（））年龄

3.查询会员帐号为’liuzc’所购买的商品号和订购日期，并按订购日期升序排列，SQL代码如下所示：

SELECTp_no,o_date

WHEREm_account='

liuzc'

ORDERBYo_date

4.查询购买商品号为’0910810004’总人数，SQL代码如下所示：

SELECTCOUNT（*）

WHEREp_no='

0910810004'

5.查询2005年6月6日前，所有商品的订购总量，要求输出商品号和订购总量，SQL代码如下所

示：

SELECTp_no,SUM（o_quantity）

WHEREo_date<

'

2005-6-6'

GROUPBYp_no

6.查询所有会员的平均月薪，最高月薪和最低月薪之和，SQL代码如下所示：

SELECTAVG（m_salary）+MAX（m_salary）+MIN（m_salary）

7.查询所有会员购买商品的种类和，要求输出会员号和商品种类和，SQL代码如下所示：

SELECTm_account,COUNT（DISTINCTp_no）

GROUPBYm_account

8.查询各类商品的最高购买数量，要求输出最高数量大于10的商品号和最高数量，SQL代码如下

所示：

SELECTTOP1p_no,SUM（o_quantity）

HAVINGSUM（o_quantity）>

10

ORDERBYSUM（o_quantity）DESC

SQLServer2000联接查询

1.查询购买了商品号为“0910810004”的会员号和姓名，并以汉字标题显示，SQL代码如下所示：

SELECTDISTINCTmembers.m_account会员号,m_name姓名

JOINorders

ONmembers.m_account=orders.m_account

2.查询购买了商品名称为“爱国者MP3”的会员号、姓名和商品价格，SQL代码如下所示：

SELECTmembers.m_account,m_name,p_price

JOINproducts

ONorders.p_no=products.p_no

ANDp_name='

爱国者mp3'

3.查询比“张自梁”月薪高的而和他不是同一地址的会员姓名和年龄，SQL代码如下所示：

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代码如下所示：

SELECTm_account,m_name

WHEREEXISTS（SELECT*

FROMorders

WHEREmembers.m_account=orders.m_account

ANDp_no='

）

5.使用in查询与“刘法治”购买至少同一种商品的会员号和商品号，SQL代码如下所示：

SELECTDISTINCTA.m_account,A.p_no

FROMordersA

WHEREp_noIN（SELECTp_no

FROMordersB

WHEREA.m_account<

ANDB.m_accountIN（SELECTm_account

FROMmembers

WHEREB.m_account=members.m_account

ANDm_name='

刘法治'

））

6.使用简单查询家庭地址为“湖南株洲”的会员以及年龄在30岁以上的会员详细信息，SQL代码如

下所示：

WHEREm_address='

湖南株洲'

WHERE（YEAR（GETDATE（））-YEAR（m_birth））>

30

再按F5或点击工具栏上的运行按钮“

”，查看运行结果，如图7-1所示。

7.使用集合查询家庭地址为“湖南株洲”的会员以及年龄在30岁以上的会员详细信息，并与步骤6

进行比较，SQL代码如下所示：

UNION

”，查看运行结果，如图7-2所示。

8.将members表和orders表之间的左向外联接包括所有会员的信息，包括没有购买商品的会员，SQL

代码如下所示：

SELECTmembers.*,orders.*

LEFTOUTERJOINorders

SQLServer2000的视图操作

1.在members表中创建地址为“湖南株洲”的会员的视图V_addr，SQL代码如下所示：

CREATEVIEWV_addr

AS

SELECT*

FROMmembers

WHEREm_address='

2.在orders表中创建购买了商品号为“0910810004”商品的视图V_buy，SQL代码如下所示：

CREATEVIEWV_buy

FROMorders

WHEREp_no='

3.在members和orders表上创建“湖南株洲”的会员购买了商品号为“0910810004”商品的视图

V_addr_buy，SQL代码如下所示：

CREATEVIEWV_addr_buy

SELECTmembers.*

FROMmembers

JOINorders

ONmembers.m_account=orders.m_account

ANDp_no='

ANDm_address='

4.在视图V_addr上查询性别为“男”的会员信息，SQL代码如下所示：

FROMV_addr

5.在视图V_addr中增加一条记录（内容如下），并查看members表中记录的改变情况。

记录内容如下：

（T-SQL）

‘fengxk’，‘冯向克’，‘男’，‘1978-06-28’，‘北京市’，5000.0，‘fxk0628’

INSERTINTOV_addrVALUES（'

fengxk'

'

冯向克'

1978-06-28'

北京市'

5000.0,'

fxk0628'

6.将视图V_addr中会员号为“liuzc518”的会员的密码修改为“liuzc0518”，并查看members中记录

的改变情况，SQL代码如下所示：

UPDATEV_addr

SETm_password='

liuzc0518'

WHEREm_password='

liuzc518'

7.在V_addr中删除会员号为“fengxk”的记录，并查看members中记录的改变情况，SQL代码如下

DELETE

8.删除视图V_addr_buy、V_buy和V_addr，SQL代码如下所示：

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）

1.建立数据库：

CREATEDATABASEstudent

ONPRIMARY

（

NAME=student_dat,

FILENAME='

E:

\mydata\student_dat.mdf'

SIZE=10MB,

MAXSIZE=50MB,

FILEGROWTH=5MB

LOGON

NAME=student_log,

\mydata\student_log.ldf'

GO

2.创建表

USEstudent

CREATETABLES

S#VARCHAR（20）,

SNVARCHAR（20）,

SEXCHAR

（2）,

AGEINT,

DEPTVARCHAR（50）

CREATETABLEC

C#VARCHAR（20）,

CNVARCHAR（20）,

TEACHERVARCHAR（50）

CREATETABLESC

GRADEINT

CREATETABLESC_C

CNOVARCHAR（20）,

CNAMEVARCHAR（20）,

AVG_GRADEINT

⑴用SQL找出选修了“程军”老师教的所有课程的学生姓名。

SELECTDISTINCTSNFROMS

WHERENOTEXISTS（SELECT*FROMC

WHEREC.TEACHER='

程军'

ANDNOTEXISTS

（SELECT*FROMSC

WHERE

S.S#=SC.S#ANDSC.C#=C.C#））

用SQL找出选修了“程军”老师教的课程的学生姓名。

SELECTDISTINCTSNFROMS,SC,C

WHERES.S#=SC.S#ANDSC.C#=C.C#ANDTEACHER='

⑵用SQL找出“程序设计”课程成绩在90分以上的学生姓名。

WHERES.S#=SC.S#ANDSC.C#=C.C#ANDCN='

程序设计'

ANDGRADE>

90

⑶检索所有比“王华”年龄大的学生姓名、年龄和性别。

SELECTDISTINCTSN,AGE,SEXFROMS

WHEREAGE>

（SELECTAGEFROMS

WHERESN='

王华'

⑷检索选修课程“C2”的学生中成绩最高的学生的学号。

SELECTS#FROMSC

WHEREC#='

C2'

=ALL（SELECTGRADEFROMSC

WHEREC#='

ANDGRADE=（SELECTMAX（GRADE）FROMSC

⑸检索学生姓名及其所选课程的课程号和成绩

SELECTSN,C#,GRADEFROMS,SC

WHERES.S#=SC.S#

⑹检索选修四门以上课程的学生总成绩（不统计不及格的课程），并要求按总成绩的降序排列出来。

SELECTSUM（GRADE）FROMSC

WHEREGRADE>

=60

GROUPBYS#HAVINGCOUNT（*）>

=4

ORDERBYSUM（GRADE）DESC

⑺检索全是女同学选修的课程的课程号。

SELECTDISTINCTC#FROMSC,S

WHERESC.S#=S.S#ANDSEX='

女'

SELECTDISTINCTC#FROMSC

WHERESC.S#IN（SELECTS.S#FROMS

WHERESEX='

⑻检索不学C6课程的男同学的学号和姓名（S#，SN）。

SELECTS#,SNFROMS

WHERESEX='

ANDS#NOTIN（SELECTS#FROMSC

C6'

）

⑼把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

⑽从SC表中把吴老师的女学生选课元组删除。

DELETEFROMSC

WHERES#IN（SELECTS#FROMS

ANDC#IN（SELECTC#FROMC

WHERETEACHERLIKE'

吴%'

⑾从SC表中把数学课程中低于数学平均成绩的选课元组删去。

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'

课本知识点总结

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求数学系或计算机系姓张的学生的信息

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）;

into学生

values（'

10228'

shenyim'

20'

na'

jsj'

selectdistinct*

from学生where姓名='

update学生

set年龄='

22'

update学生

set姓名='

sdf'

where学号='

删除学生表

delete

from学生

into

学生

10220'

sds'

sjuj'

P239习题9