数据库sql语句和重要知识点总结

时间:2024.3.27

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完成下列操作

SS#SNSEXAGEDEPT

CC#CNTEACHER

SCS#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

更多相关推荐:
数据库知识点总结

二、名词解释1.数据冗余定义:同一数据存储在不同的数据文件中的现象。2.DBA数据库管理员3.事务(unit)。4.数据字典:数据库中所有对象及其关系的信息集合。5.数据独立性包括数据的物理独立性和逻辑独立性。…

数据库知识点总结

1、数据(Data)——是数据库中存储的基本对象;2、数据库(Database,简称DB)3、数据库数据具有永久存储、有组织、可共享三个基本特点。4、数据库管理系统(DatabaseManagementSyst…

数据库知识点总结

第一章绪论1.数据库系统概述(1)数据、数据库、数据库管理系统、数据库系统(2)数据管理技术的产生和发展(3)数据库系统的特点2.数据模型(1)两大类数据模型(2)数据模型的组成要素(3)概念模型(4)最常用的…

数据库知识点总结

第一章(1解决多用户,多个应用进程共享数据的需求计算机系统在引入数据库后的所构成的系(3描述和存储,具有较小的冗余度,据库中的同一模式可以有任意多个外模式,对于每一个外模式,都存在一个外模式/模式映像,它确定了…

数据库知识点总结

题型:选择,填空,简答,综合题:1.SQL语句2.会画ER图3.ER图与关系模式的转换4.视图的创建,更新,修改,删除5.关系代数表达式6.模式分解(第四章的P60-70页要好好看看,这里会出题,然后就是把P6…

access数据库知识点总结

SELECT查询包括条件项、内连接、分组汇总(含HAVING)、排序、简单子查询(不考EXISTS)及一些输出选项。数据库管理系统(DBMS)特点(1)数据结构化(2)数据共享性好、冗余度低、(3)数据独立性强…

数据库知识点总结

文件处理系统存在问题:数据的冗余和不一致,数据访问困难,数据孤立,完整性问题,原子性问题,并发访问异常,安全性问题数据库三级抽象物理层:最低层次的抽象,描述数据实际上是怎样存储的。逻辑层:比物理层层次稍高的抽象…

【数据库原理】重点总结

1试述数据模型的概念数据模型的作用和数据模型的三个要素2定义并解释概念模型中以下术语实体实体型实体集属性码实体联系图3试述关系模型的三个组成部分4试述关系模型的完整性规则在参照完整性中为什么外部码属性的值也可以...

数据库期末考试复习知识点总结

数据库结课考试知识点小结第一章111四个基本概念及关系1数据描述数据是数据库中存储的基本对象数据的定义描述事物的符号记录数据的种类数字字符串日期文字图形图像声音数据的特点数据与语义是不可分的2数据库是存放数据的...

数据库考试重点总结

第一章1试述数据数据库数据库系统数据库管理系统的概念答l数据Data描述事物的符号记录称为数据数据的种类有数字文字图形图像声音正文等数据与其语义是不可分的解析在现代计算机系统中数据的概念是广义的早期的计算机系统...

SQL Server 数据库基本知识点

SQLServer数据库基本知识点一数据类型二常用语句用到的数据库Northwind查询语句简单的TransactSQL查询只包括选择列表FROM子句和WHERE子句它们分别说明所查询列查询的表或视图以及搜索条...

分布式数据库知识点整理

局部数据只提供本站点的局部应用所需要的数据全局数据虽然物理上存储在个站点上但是参与全局应用局部用户一个用户或一个应用如果只访问他注册的那个站点上的数据称为本地或局部用户或本地应用全局用户如果访问涉及两个或两个以...

数据库知识点总结(35篇)