DM SQL学习

DM SQL自学

1. 定义：

SQL：Structured Query Language，结构化查询语言。SQL是一个通用的、功能强大的关系数据库语言。

1） 基本功能：

A. 数据定义

通过DDL(Data Definition Language）语言来实现。用来建立数据库对象（如模式、表、索引、序列、视图等），常用DDL语句表现为不同形式的CREATE、ALTER、 DROP命令。

B. 数据操纵

数据操纵功能通过DML（Data  Manipulation Language）语言来实现，DML包括数据查询和数据更新等语句。数据查询指对数据库中的数据进行查询、统计、排序、分组、检索等操作。数据更新指对数据的更新、删除、修改等操作。

C. 数据控制

数据库的数据控制功能指数据的安全性和完整性，通过数据控制语句DCL(Data Control Language)来实现。

D. 数据查询

通过SELECT的查询功能进行数据库的数据内容及功能查询。

2） 基本对象：

表空间	TABLESPACE
用户	USER
模式	SCHEMA
表	TABLE
视图	VIEW
列	COLUMN
存储过程	PROCEDURE
存储函数	FUNCTION
存储包	PACKAGE
触发器	TRIGGER
序列	SEQUENCE
索引	INDEX
数据字典	DICTIONARY

3） 核心动词

SQL功能	动词
数据定义	CREATE、 DROP、 ALTER
数据查询	SELECT
数据操纵	INSERT 、UPDATE 、DELETE
数据控制	GRANT 、REVOKE
SELECT	从一个表或多个表中检索列和行
CREATE	创建数据库对象
DROP	删除数据库对象
ALTER	变更数据库对象，例如对增删表列等
INSERT	向一个表中增加行
UPDATE	更新表中已存在的行的某几列的值
DELETE	从一个表中删除行
GRANT	向数据库中的用户授以操作权限（如修改某个表的权限、删除某个表的权限）
REVOKE	收回以前授予给当前数据库中用户的权限

4） SQL语句作用

※建立数据库的表格。

※改变数据库系统环境设置。

※针对某个数据库或表格，授予用户存取权限。

※对数据库表格建立索引。

※修改数据库表格结构。

※对数据库进行数据的新建。

※对数据库进行数据的删除。

※对数据库进行数据的修改。

※对数据库进行数据的查询。

5） SQL语句基本操作：

创建对象	CREATE TABLE TABNAME
撤销对象	DROP TABLE TABNAME
插入数据	INSERT INTO TABNAME
更新数据	UPDATE TABNAME
删除数据	DELETE FROM TABNAME
查询数据	SELECT * FROM TABNAME
授予权限	GRANT SELECT ON TABNAME TO
回收权限	REVOKE SELECT ON TABNAME FROM

6） SQL基本语法

A. 属性词（Predicates）：在SQL命令中用来指明所要选择的记录的方式，例如ALL、TOP与DISTINCT等等。

B. 条件子句（Clause）：在SQL的查询中，利用一些表达式定义出查询的条件，以缩小寻找的范围，例如WHERE。

C. 运算符（Operator）与操作数（Operation）：在SQL的查询中，与Operation共同组成表达式（Expression），例如BETWEEN....AND 运算符。

D. 函数（Function）：一些SQL常见的函数，像是AVG()、MIN()、MAX()是求算数平均数的函数。

E. SQL语句（Statement）：SQL语句是SQL语法的主体，用来对某一个特定的数据库发出指示，并返回相关的数据，而SQL的语法结构，基本上可以利用下面的式子来表示：

命令＋条件子句

例如：

SELECT*FROM TAB WHERE TAB.NAME='A';

2. 常见SELECT用法

1） SELECT查询语句

语法如下：

SELECT [PREDICATE]{* TABLE.* [TABLE.]FIELD1[,[TABLE.]FIELD2[,...]]}[AS ALIAS1[,ALIAS2[,...]]]

FROM TABLEEXPRESSION [,...]

[WHERE...]

[GROUP BY...]

[HAVING...]

[ORDER BY...];

名称解释如下：

PREDICATE：如前面所述，包括了ALL，DISTINCT与TOP，我们可以利用这样的语句去限制查询后所得的结果。

*：指定查询所有的字段。

TABLE：所指定表格的名称。

FIELD1,FIELD2：想要读取数据的字段名称，如果包含了一个以上的字段，会依照列出的顺序来读取数据。

ALIAS1,ALIAS2：用来替代在表格实际字段名称的别名。

TABLEEXPRESSION：表格名称或包含我们所想要的数据的表格。

2） DISTINCT属性词

DISTINCT，查询不同的数据，查询出来结果将会针对不一样的姓名加以筛选。若把DISTINCT 省略，则这样的查询会显示所有的记录。

示例：

SELECT DISTINCT

        A.课程,

        B.*

FROM

        签到记录 A,

        学生信息 B

WHERE

        A.学号=B.学号

    AND A.课程='数据库原理'

ORDER BY

        A.课程,

        B.学号;

3） TOP属性词

从第一条或最后一条开始（利用ORDER BY条件子句），返回特定条数的数据。

示例：

SELECT

        A.课程,

        A.分数,

        B.*

FROM

        (

                SELECT TOP 3 * FROM 课程成绩 WHERE 课程='数据库原理' ORDER BY 分数 DESC

        )

        A

LEFT JOIN 学生信息 B

ON

        (

                A.学号=B.学号

        )

ORDER BY

        A.分数 DESC;

4） ORDER BY条件语句

通常与SELECT语句合并使用，目的是将查询的结果依照指定字段加以排序。

语法如下：

SELECT FIELDLIST

FROM TABLE

WHERE SELECTCRITERIA

ORDER BY FIELD[ASC DESC][,FIELD2[ASC DESC][,...]];

ASC：递增顺序类别。(默认值) DESC：递减顺序类别。

示例：

SELECT * FROM 学生信息 ORDER BY 生日;

SELECT * FROM 学生信息 ORDER BY 生日 DESC;

SELECT * FROM 学生信息 ORDER BY 生日,学号;

3. 使用ISQL执行SQL语句

定义：Interactive Structrue Query Language，交互结构询问语言。

通常指数据库的一个名为ISQL的应用程序。ISQL允许你执行交互式的SQL查询。ISQL程序启动时，首先会出现一个对话框，要求输入服务器信息和登录信息，包括SQL服务器的名字、用户和密码信息等。登录成功，即可在交互式窗口中执行SQL语句。

如图所示为DM ISQL：

4. 常见操作语法

1） 操作多个表

SQL允许一个SELECT语句同时从多个表中取出数据，只需在SELECT语句的FROM从句中列出要从中取出数据的表名称即可：

示例：

SELECT AU_LNAME,TITLE FROM AUTHORS, TITLES;

2）操作字段

通常，当从一个表中取出字段值时，该值与创建该表时所定义的字段名联系在一起。如果从表AUTHORS中选择所有的作者名字，所有的值将会与字段名AU_LNAME相联系。但是在某些情况下，需要对字段名进行操作。在SELECT语句中，可以在缺省字段名后面仅跟一个新名字来取代它。

示例：用一个更直观易读的名字AUTHOR LAST NAME来代替字段名AU_LNAME：

SELECT AU_LNAME "AUTHOR LAST NAME" FROM AUTHORS;

3） 排序查询结果

SQL表数据没有内在的顺序。例如，从一个表中取第二个记录是没有意义的。从SQL的角度看来，没有一个记录在任何其他记录之前。然而，你可以操纵一个SQL查询结果的顺序。

示例：通过字母对顺序排序：

SELECT AU_LNAME FROM AUTHORS ORDER BY AU_LNAME;

反过来，字母降序示例如下：

SELECT AU_LNAME,AU_FNAME FROM AUTHORS

WHERE AU_LNAME=”RINGER” ORDER BY AU_LNAME ,AU_FNAME DESC;

※注意：不是特别需要时，不要对查询结果进行排序，因为服务器完成排序需要消耗资源，即带有ORDER BY子句的SELECT语句执行起来比一般的SELECT语句花的时间长。

4） 取出互不相同的数据

一个表有可能在同一列中有重复的值。也许希望每个值只被选取一次，则可以使用关键字DISTINCT来做到这一点：

示例：

SELCET DISTINCT AU_LNAME FROM AUTHORS WHERE AU_LNAME=’RINGER’;

※注意：如同ORDER BY子句一样，强制服务器返回互不相同的值也会增加运行开销。

5. 常见CREATE语法

数据库中的所有数据都存储在表中。数据表包括行和列，列决定了表中数据的类型，行包含了实际的数据。

通过定义字段，可以创建一个新表。每个字段有一个名字和一个特定的数据类型，例如字段AU_LNAME存储的是字符型数据。

示例：

CREATE TABLE GUESTBOOK (VISITOR VARCHAR(40),COMMENTS TEXT,ENTRYDATE DATETIME);

不同的字段类型用来存放不同类型的数据,常用的字段类型包括：字符型，文本型，数值型，逻辑性和日期型。

1） 字符型数据

字符型数据是最常用的数据类型之一，当需要存储短的字符串信息时，就可以使用字符型数据类型。主要有以下两种：

CHAR：用于存储固定长度的字符数据

示例：

CREATE TABLE GUESTBOOK (VISITOR CHAR(40),COMMENTS TEXT,ENTRYDATE DATETIME);

VARCHAR：用与存放可变长度的字符串信息

示例：

CREATE TABLE GUESTBOOK (VISITOR VARCHAR(40),COMMENTS TEXT,ENTRYDATE DATETIME);

2） 文本型数据

字符型数据的长度有限。而使用文本型数据，可以存放超长字符串。

示例：

CREATE TABLE GUESTBOOK (VISITOR VARCHAR(40),COMMENTS TEXT,ENTRYDATE DATETIME);

3） 数值型数据

数据库支持许多种不同的数值型数据。可以存储整数、小数和货币数。主要有以下几种：

INT整型

SMALLINT：可以存储从-32768到32768的整数，使用方法与INT相同

TINYINT：一个TINYINT型数据只占用一个字节；而一个INT型数据占用四个字节

NUMERIC：来同时表示一个数的整数部分和小数部分

4） 存储逻辑值

如果你使用复选框（CHECKBOX）从网页中搜集信息，可以把此信息存储在BIT型字段中。BIT型字段只能取两个值：0或1。

示例：

CREATE TABLE OPINION (VISITOR VARCHAR(40),GOOD BIT)

这个表可以用来存放对网点进行民意调查所得的信息。访问者可以投票表示他们是否喜欢网点。如果投YES，就在BIT型字段中存入1。反之，如果投NO，就在字段中存入0。

5） 存储日期和时间

使用DATETIME进行日期和时间的存储。

示例：

CREATE TABLE VISITORLOG(VISITORVARCHAR (40), ARRIVALTIME DATETIME,DEPARTURETIME DATETIME);

6. 字段属性

SQL语法中提供字段属性，允许控制空值，缺省值和标识值

1） 空值

大多数字段可以接受空值（NULL）。当一个字段接受了空值后，如果不改变它，它将一直保持空值。空值（NULL）和零是不同的，严格的说，空值表示没有任何值。

A. 允许空值

在字段定义的后面使用表达式NULL

示例：

CREATE TABLE EMPTY (EMPTY1 CHAR (40) NULL,EMPTY2 INT NULL);

B. 禁止空值

示例：

CREATE TABLE CREDITCARDS (CREDITCARD_NUMBER CHAR(20) NOT NULL,CREDITCARD_EXPIRE DATETIME NOT NULL);

2） 缺省值

使用DEFAULT来创建缺省值

示例：

CREATE TABLE ADDRESSES (STREET VARCHAR(60) NULL,

CITY VARCHAR(40) NULL,

STATE VARCHAR(20) NULL,

ZIP VARCHAR(20) NULL,

COUNTRY VARCHAR(30) DEFAULT ‘USA’);

其中，字段COUNTRY的缺省值被指定为美国。

3）标识字段

每个表可以有一个也只能有一个标识字段。一个标识字段是唯一标识表中每条记录的特殊字段。为了建立一个标识字段，可以在字段定义后面加上表达式IDENTITY即可。

示例：

CREATE TABLE VISITORID (THEID INT IDENTITY,NAME VARCHAR(40));

这个语句所创建的表包含一个名为THEID的标识字段。每当一个新的访问者名字添加到这个表中时，这个字段就被自动赋给一个新值。

7. 插入数据INSERT

向表中添加一个新记录，需要使用SQL INSERT语句

语法如下：

INSERT [INTO] {TABLE_NAME|VIEW_NAME} [(COLUMN_LIST)] {DEFAULT VALUES |VALUES_LIST | SELECT_STATEMENT};

当插入数据时只制定了一个字段会发生什么：

1）如果该字段有一个缺省值，该值会被使用。例如，假设你插入新记录时没有给字段THIRD_COLUMN提供数据，而这个字段有一个缺省值’SOME VALUE’。在这种情况下，当新记录建立时会插入值’SOME VALUE’。

2）如果该字段可以接受空值，而且没有缺省值，则会被插入空值。

3）如果该字段不能接受空值，而且没有缺省值，就会出现错误。

4）最后，如果该字段是一个标识字段，那么它会自动产生一个新值。当你向一个有标识字段的表中插入新记录时，只要忽略该字段，标识字段会给自己赋一个新值。

8. 修改数据UPDATE

数据修改语句用于修改表中已存在的数据。

※注意：

1）SET后的<列名>不能重复出现；

2）WHERE子句也可以包含子查询。如果省略了WHERE子句，则表示要修改所有的元组；

3）如果<列名>为被引用列，只有被引用列中未被引用列引用的数据才能被修改；如果<列名>为引用列，引用列的数据被修改后也必须满足引用完整性。在DM系统中，以上引用完整性由系统自动检查。

4）执行基表的UPDATE语句触发任何与之相联系的UPDATE触发器。

9. 删除数据

1）删除数据DELETE

给DELETE 语句提供WHERE 子句。WHERE子句用来选择要删除的记录。

语法如下：

DELETE [FROM] {TABLE_NAME|VIEW_NAME} [WHERE CLAUSE];

※注意：使用用TRUNCATE TABLE 语句代替DELETE语句，记录的删除是不作记录的。也就是说，这意味着TRUNCATE TABLE要比DELETE快得多。

TRUNCATE语法如下：

TRUNCATE TABLE MYTABLE;

2） 删除表

使用DROP进行删除表操作

※注意：使用DROP TABLE命令时一定要小心。一旦一个表被删除之后，将无法恢复它。

3） 修改表

使用ALTER进行数据替换

示例：

ALTER TABLE MYTABLE ADD MYNEWCOLUMN INT NULL;

这个语句向表MYTABLE中增加了一个新字段MYNEWCOLUMN。当增加新字段时，必须允许它接受空值，因为表中原来可能已经有了许多记录。

10. 建立索引

通过索引，可以较快速的找到需要查询的数据

1） 聚簇索引和非聚簇索引

索引有两种类型：聚簇索引和非聚簇索引。在聚簇索引中，索引树的叶节点包含实际的数据：记录的索引顺序与物理顺序相同。在非聚簇索引中，叶节点存储指向表中记录的指针信息：记录的物理顺序与逻辑顺序没有必然的联系。

聚簇索引与目录表相似，目录表的顺序与实际的页码顺序是一致的。非聚簇索引则更象书的标准索引表，索引表中的顺序通常与实际的页码顺序是不一致的。一本书也许有多个索引，例如，它也许同时有主题索引和作者索引。同样，一个表可以有多个非聚簇索引。

每个表只能有一个聚簇索引，因为一个表中的记录只能以一种物理顺序存放。通常你要对一个表按照标识字段建立聚簇索引。但是，你也可以对其它类型的字段建立聚簇索引，如字符型，数值型和日期时间型字段。

当你需要取出一定范围内的数据时，用聚簇索引也比用非聚簇索引好。例如，假设你用一个表来记录访问者在你网点上的活动。如果你想取出在一定时间段内的登录信息，可以使用这个表的DATETIME型字段建立聚簇索引。

对聚簇索引的主要限制是每个表只能建立一个聚簇索引。但是，一个表可以有不止一个非聚簇索引。非聚簇索引需要大量的硬盘空间和内存。另外，虽然非聚簇索引可以提高从表中取数据的速度，它也会降低向表中插入和更新数据的速度。

2） 索引属性

这两种类型的索引都有两个重要属性：你可以用两者中任一种类型同时对多个字段建立索引（复合索引）；两种类型的索引都可以指定为唯一索引。

你可以对多个字段建立一个复合索引，甚至是复合的聚簇索引。假如有一个表记录了你的网点访问者的姓和名字。如果你希望根据完整姓名从表中取数据，你需要建立一个同时对姓字段和名字字段进行的索引。这和分别对两个字段建立单独的索引是不同的。当你希望同时对不止一个字段进行查询时，你应该建立一个对多个字段的索引。如果你希望对各个字段进行分别查询，你应该对各字段建立独立的索引。

两种类型的索引都可以被指定为唯一索引。如果对一个字段建立了唯一索引，你将不能向这个字段输入重复的值。一个标识字段会自动成为唯一值字段，但你也可以对其它类型的字段建立唯一索引。

3） 用SQL建立索引

示例：

CREATE INDEX MYCOLUMN_INDEX ON MYTABLE (MYCLUMN);

这个语句建立了一个名为MYCOLUMN_INDEX的索引。可以给一个索引起任何名字，但应该在索引名中包含所索引的字段名，这对将来弄清楚建立该索引的意图是有帮助的。

删除索引时使用DROP语句：

DROP INDEXMYCOLUMN_INDEX;

11. 运算相关

1）SQL运算符

SELECT语句中的WHERE可选从句用来规定哪些数据值或哪些行将被作为查询结果返回或显示。在WHERE条件从句中可以使用以下一些运算符来设定查询标准：

=：等于

>：大于

<：小于

>=：大于等于

<=：小于等于

<>：不等于

除了上面所提到的运算符外，LIKE运算符在WHERE条件从句中也非常重要。LIKE运算符的功能非常强大，通过使用LIKE运算符可以设定只选择与用户规定格式相同的记录。

除此之外，可以使用通配符“%”用来代替任何字符串

示例：

SELECT FIRSTNAME, LASTNAME, CITY

FROM EMPLOYEE

WHERE FIRSTNAME LIKE ‘E%’;

上述SQL语句将会查询所有名称以E开头的姓名。

2） 运算函数

A. 使用SUM或AVG这两个分别用于计算总数和平均数的函数

语法如下：

SELECT "FUNCTION TYPE"("COLUMN_NAME") FROM "TABLE_NAME";

B. 使用COUNT函数可进行运算记录数目

语法如下：

SELECT COUNT("COLUMN_NAME")FROM "TABLE_NAME";

C. 集合函数

使用GROUP BY命令可进行分组总和计算

示例：

SELECT STORE_NAME, SUM(SALES) FROM STORE_INFORMATION GROUP BY STORE_NAME;

D. HAVING集合条件

用户在使用SQL语言的过程中可能希望解决的另一个问题就是对由SUM或其它集合函数运算结果的输出进行限制，此时应采用HAVING从句

示例：

SELECT STORE_NAME, SUM(SALES) FROM STORE_INFORMATION GROUP BY STORE_NAME HAVING SUM(SALES) > 1500;

SQL语言中设定集合函数的查询条件时使用HAVING从句而不是WHERE从句。通常情况下，HAVING从句被放置在SQL命令的结尾处。

12. 其他函数

1） ALIAS别名

SQL语言中一般使用两种类型的别名，分别为字段别名和数据表别名。简单的说，使用字段别名可以帮助我们有效的组织查询的输出结果。对于数据表别名，可以通过将别名直接放置在FROM从句中数据表名称的后面设定。

语法如下：

SELECT “TABLE_ALIAS”.”COLUMN_NAME1” “COLUMN_ALIAS”FROM “TABLE_NAME” “TABLE_ALIAS”;

别名都直接放置在各自对应名称的后面，中间用空格分开。

示例：

SELECT A1.STORE_NAME "STORE”, SUM(SALES) “TOTAL SALES” FROM STORE_INFORMATION A1 GROUP BY A1.STORE_NAME;

2）使用EXISTS来相关结果

有时可以通过多种途径来访问相同的数据，而且需要对结果进行匹配（或相关）来得到值的交叉区。

示例：

SELECT * FROM 学生信息 A WHERE EXISTS

        (

                SELECT * FROM 签到记录 WHERE 学号=A.学号

        );

3） JOIN连接查询

JOIN的工作方式与SELECT声明是相同的，它从不同的表格中返回一个带有专栏的结果组。在暗含的JOIN之上使用外部JOIN的优势是对结果组的更好的控制，而且还可能在涉及很多个表格的情况下提升性能表现。

JOIN的类型有几种：LEFT，RIGHT，FULL OUTER，INNER和CROSS。所使用的类型是由想要看到的结果所决定的。例如，使用LEFT OUTER JOIN将会从列出的第一个表格中返回所有有关的行，而同时如果没有信息与第一个表格相关的话将潜在地从所列出的第二个表格中加入行。

在这里INNER JOIN和暗含的JOIN是不同的，INNER JOIN将只返回那些在两个表格中都有数据的行。

对第一个SELECT查询使用如下JOIN声明：

SELECT TABLE1.COLUMN1, TABLE2.COLUMN2 FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.COLUMN1 = TABLE2.COLUMN1;

4） 子查询

子查询，或叫子选择声明，是在一个查询中将结果组作为资源使用的一个途径。经常被用来对结果进行限制或定义，而不是运行多个查询或操纵应用软件之中的数据。有了子查询，即可以参考表格来决定数据的内含，或是在一些情况下，返回一个专栏，而这个专栏是一个子选择的结果。

实例：使用两个表格，一个表格中包含了想要返回的数据，而另一个表格则给出一个比较点来确定什么数据确实感兴趣的

SELECT COLUMN1 FROM TABLE1 WHERE EXISTS ( SELECT COLUMN1 FROM TABLE2 WHERE TABLE1.COLUMN1 = TABLE2.COLUMN1 );

5） 视图

在SQL中，视图是基于SQL语句的结果集的可视化的表。视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加SQL函数、WHERE以及JOIN语句，我们也可以提交数据，就像这些来自于某个单一的表。

※注意：数据库的设计和结构不会受到视图中的函数、WHERE 或 JOIN 语句的影响。

6）序列

序列是一个数据库实体，通过它多个用户可以产生唯一整数值，可以用序列来自动地生成主关键字值。

示例：

CREATE SEQUENCE SEQ_QUANTITY INCREMENT BY 10;

※注意：

A. 一旦序列生成，就可以在SQL语句中用以下伪列来存取序列的值；

※CURRVAL 返回当前的序列值；

※NEXTVAL 如果为升序序列，序列值增加并返回增加后的值；如果为降序序列，序列值减少并返回减少后的值。如果第一次对序列使用该函数，则返回序列当前值；

※用户会话在第一次使用CURRVAL之前应先使用NEXTVAL获取序列当前值；之后除非会话使用NEXTVAL获取序列当前值，否则每次使用CURRVAL返回的值不变。

B. 缺省序列：如果在序列中什么也没有指出则缺省生成序列，一个从1开始增量为1且无限上升(最大值为9223372036854775807)的升序序列；仅指出INCREMENT BY -1，将创建一个从-1开始且无限下降(最小值为-9223372036854775808)的降序序列。

7） 控制事务

DM_SQL语言提供了事务的回滚(ROLLBACK)与提交(COMMIT)语句。用户可以使用显式的提交或回滚语句来结束一个事务，也可以隐式的结束一个事务。

A. 提交语句

语法格式

COMMIT [WORK];

B. 回滚语句

语法格式

ROLLBACK [WORK];

该语句回滚(废除)当前事务工作单元中的所有操作，并结束该事务。

8） GUID

定义：GUID()

功能说明：生成一个唯一编码串（32个字符）

返回值：返回一个唯一编码串。

举例说明：

获取一个唯一编码串。

SELECT GUID();

9） RAND

语法：RAND([N])

功能：返回一个0到1之间的随机浮点数。N为数值类型，为生成随机数的种子，当N省略时，系统自动生成随机数种子。

SELECT RAND();

查询结果为一个随机生成的小数

SELECT RAND(315);

查询结果为：3.247169408246101E-002

10） 获取系统当前时间

语法：NOW(N)

功能：返回系统的当前时间戳。等价于GETDATE()。

参数：N：指定毫秒的精度。取值范围0-6，默认为6。

11） 获取系统支持的数据类型

查询系统字典，获取数据库系统支持的数据类型。

SELECT * FROM SYS.SYSTYPEINFOS;

12） 获取数据库对象信息

SELECT * FROM SYS.SYSOBJECTS;

SELECT TYPE$,SUBTYPE$,COUNT(*)

FROM SYS.SYSOBJECTS

GROUP BY TYPE$,SUBTYPE$

ORDER BY 1,2;

作业：

创建表

STUDENT(学生表)

SNO	SNAME	SEX	DEPT	BIRTH	AGE

约束如下：

学号不能存在相同的

名字为非空

性别的值只能是’男’或’女’

系包括这几个：信息系，计算机科学系，数学系，管理系，中文系，外语系，法学系

出生日期为日期格式

年龄为数值型，且在0~100之间

create table student(     sno dec(15),     sname varchar(10) not null,     sex varchar(4),     dept varchar(15),     birth date,     age number(2),     constraint con_sno primary key(sno),     constraint con_sex check(sex in('男','女') and sex <>null),    constraint con_dept check(dept in ('信息系','计算机科学系','数学系','管理系','中文系','外语系','法学系')),    constraint con_age check(age between 0 and 100));

CS(成绩表)

SNO	CNO	CJ

约束如下：

SNO和CNO分别参照STUDENT和COURSE表中的SNO,CNO的字段

CJ(成绩)只能在0~100之间，可以不输入值

create table cs( sno smallint not null references student(sno), cno smallint not null references course(cno), cj smallint constraint e check(cj between 0 and 100), constraint f primary key(sno,cno));

COURSE（课程表）

CNO	CNAME

其约束如下：

课程号（CNO）不能有重复的

课程名（CNAME）非空

create table course(    cno dec(15),     cname varchar(20) not null,     constraint con_cno unique(cno));

写语句

查询全体学生的学号与姓名。

select sno,sname from student;

查询全体学生的姓名、学号、所在系，并用别名显示出结果。

select sno as "学号",sname as "姓名",dept as "所在地" from student;

查询全体学生的详细记录。

select * from student;

查询选修了课程的学生学号。

select sno from cs where cno is not null;

查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。

select sname,dept,age from student where age between 20 and 23;

查询信息系、数学系和计算机科学系生的姓名和性别。

select sname,sex from student where dept='信息系' or dept='数学系' or dept='计算机科学系';

查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。

select sname,sex from student where dept!='信息系' and dept!='数学系' and dept!='计算机科学系';

查询所有姓刘学生的姓名、学号和性别。

select sname,sno,sex from student where sname like('刘%');

查询所有不姓刘的学生姓名。

select sname,sno from student where sname not like('刘%');

查询信息系、数学系和计算机科学系学生的姓名和性别。

select sname,sex from student where dept='信息系' or dept='数学系' or dept='计算机科学系';

查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。

select sno,cj from cs where cno=3 order by cj desc;

查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。

select * from student order by dept asc,age desc;

查询学生总人数。

select count(*) from student;

查询选修了课程的学生人数。

select count(sno) from cs where cno is not null;

计算1号课程的学生平均成绩。

select avg(cj) from cs where cno=1;

查询选修1号课程的学生最高分数。

select max(cj) from cs where cno=1;

求各个课程号及相应的选课人数。

select cno,count(sno) from cs group by cno;

查询选修了3门以上课程的学生学号。

select sno, count(cno) from cs group by sno having count(sno)>3;

查询有3门以上课程是90分以上的学生的学号及（90分以上的）课程数。

select sno, count(cno) as ‘课程数’from cs where cj>90 group by sno having count(sno)>=3;

查询每个学生选修课程的总学分。

select sno,sum(cj) from cs,course where cs.cno=course.cno group by sno union;

查询每个学生及其选修课程的情况。

select cs.sno, course.* from cs,course where cs.cno=course.cno;

查询所有选修了1号课程的学生姓名。

select sname from student where sno in (select sno from cs where cno=1);

简答题

在SELECT语句中DISTINCT、ORDER BY、GROUP BY和HAVING子句的功能各是什么？

distinct 去重处理

order by 排序处理

group by 分组处理

having 一般和group by共用主要是对结果运算进行限制

在一个SELECT语句中，当WHERE子句、GROUP BY子句和HAVING子句同时出现在一个查询中时，SQL的执行顺序如何？

首先对where子句做筛选，在对结果集使用group by分组处理，最后对结果集进行筛选处理

https://eco.dameng.com