SQL供能十分强大,但由于设计巧妙,语言简洁,完成核心功能只用了9个动词,如下所示:

SQL功能 动词
数据查询 SELECT
数据定义 CREATE, DROP, ALTER
数据操纵 INSERT, UPDATE, DELETE
数据控制 GRANT, REVOKE

一. 数据定义

SQL Server支持三级模式结构,其模式、外模式和内模式中的基本对象有表、视图和索引,因此SQL的数据定义功能包括模式定义、视图和索引定义。

操作对象 创建 删除 修改
模式 CREATE SCHEMA DROP SCHEMA
CREATE TABLE DROP TABLE ALTER TABLE
视图 CREATE VIEW DROP VIEW
索引 CREATE INDEX DROP INDEX

1.1 创建模式

定义模式实际上定义了一个命名空间,在这个命名空间下可以进一步定义该模式包含的数据库对象,例如基本表、视图和索引等。

创建模式的语句如下:

CREATE SCHEMA <模式名> AUTHORIZATION <用户名>

如果没有指定<模式名>,那么模式名隐含为<用户名>

例如:

CREATE SCHEMA "S-T" AUTHORIZATION deng; // 为用户deng定义了一个模式S-T;

CREATE SCHEMA AUTHORIZATION deng; // 为用户deng定义了一个模式deng;

也可以在定义模式的同时定义进一步创建的基本表、视图、定义授权。如:

CREATE SCHEMA <模式名> AUTHORIZATION <用户名> [<表定义子句> | <视图定义子句> | <授权定义子句>]

1.2 删除模式

删除模式语句如下:

DROP SCHEMA <模式名> <CASCADE | RESTRICT>

其中,CASCADE和RESTRICT必选其一。CASCADE(级联)表示在删除模式的同时把该模式中所有的数据库对象全部删除;RESTRICT表示该模式中没有任何下属(如表、视图等)才能执行删除操作。

2.1 创建基本表

创建基本表的基本语法如下:

CREATE TABLE <表名> (<列名><数据类型>[列级完整性约束条件]
    [, <列名><数据类型>[列级完整性约束条件]]
    ...);

其中,列级完整性约束条件的属性值有PRIMARY KEY(主键)、UNIQUE(唯一值);

实例:

CREATE TABLE student
    (Sno CHAR(9) PRIMARY KEY,
    Sname CHAR(20) UNIQUE,
    Ssex CHAR(2),
    Sage SMALLINT);

2.2 修改基本表

SQL语言用ALTER TABLE语句修改基本表,其一般格式为:

ALTER TABLE <表名>
[ADD <新列名> <数据类型> [完整性约束] ]
[DROP <完整性约束名>]
[ALTER COLUMN <列名> <数据类型>];

实例:

// 向student表中增加“入学时间”,其数据类型为日期型
ALTER TABLE student ADD enterDate DATE;

// 将student表中年龄的数据类型由字符型改为整型
ALTER TABLE student ALTER COLUMN age INT;

// 增加课程名称必须取唯一值的约束条件
ALTER TABLE course ADD UNIQUE(cName);

2.3 删除基本表

删除基本表的一般格式:

DROP TABLE <表名> <CASCADE | RESTRICT>;

实例:

// 删除student表
DROP TABLE student CASCADE;

3.1 建立索引

创建索引的一般格式:

CREATE [UNIQUE] [CLUSTER] INDEX <索引名>
ON <表名> (<列名> <次序> [, <列名> <次序> ... ] );

其中,<次序>的属性值取值有:ASC(升序)和DESC(降序)。

UNIQUE:表示索引的每一个索引值对应唯一的数据记录;

CLUSTER:表示要建立的索引是聚簇索引,指索引项的顺序与表中记录的物理顺序一致的索引组织。

3.2 删除索引

DROP INDEX <索引名>;

二. 数据查询

1. 数据查询的一般格式:

SELECT [ALL | DISTINT] <目标列表达式> [, <目标列表达式>] ...
FROM <表名或视图名> [, <表名或视图名>] ...
[WHERE <条件表达式>]
[GROUP BY <列名1> [HAVING <条件表达式>]]
[ORDER BY <列名2> [ASC | DESC] ];

实例:

// 查询全体学生的学号和姓名
SELECT sno, Sname 
FROM student;

// 查询全体学生的详细记录
SELECT * 
FROM student;

// 查询全体学生的姓名、出生年份和所在院系,并要求用小写字母表示所有系名
SELECT Sname, 2015-Sage, LOWER(Sdept) 
FROM Student;

// 查询全体学生的学号,并取消重复行
SELECT DISTINCT sno 
FROM student;

2. 常用查询条件

查询条件 谓词
比较 =, >, <, >=, <=, !=, <>, !>, !< ; NOT + 前面的比较运算符
确定范围 BETWEEN AND, NOT BETWEEN AND
确定集合 IN, NOT IN
字符匹配 LIKE, NOT LIKE
空值 IS NULL, IS NOT NULL
多重条件 AND, OR, NOT

实例:

// 查询所有年龄在20岁以下的学生姓名及其年龄
SELECT Sname, Sage 
FROM student 
WHERE Sage<20;

// 查询年龄在20~23岁之间的学生姓名和年龄
SELECT Sname, Sage 
FROM student 
WHERE Sage BETWEEN 20 AND 23;

// 查询计算机科学系(CS)、数学系(MA)的学生姓名和年龄
SELECT Sname, Sage 
FROM student 
WHERE Sdept IN ("CS", "MA");

字符匹配:

进行字符串匹配可以用LIKE谓词。其一般语法如下:

[NOT] LIKE "<匹配字符串>" [ESCAPE "<换码字符>"];

ESCAPE "\" :表示"\"为换码字符。

常用通配符有以下两种:

通配符 描述
%(百分号) 代表任意长度的字符串
_(下划线) 代表任意单个字符

实例:

// 查询学号为123的学生的详细情况
SELECT * 
FROM student 
WHERE sno LIKE "123";

// 查询所有姓刘的学生的详细情况
SELECT * 
FROM student 
WHERE Sname LIKE "刘%";

// 查询所有姓刘且全名为两个字的学生的详细情况
SELECT * 
FROM student 
WHERE Sname LIKE "刘_";

ORDER BY子句:

实例:

// 查询选修了3号课程的学生的学号及其成绩,查询结果按分数的降序排列
SELECT Sno, Grade 
FROM SC 
WHERE Cno="3" 
ORDER BY Grade DESC;

GROUP BY 子句:

// 求各个课程号及相应的选课人数
SELECT Cno, COUNT(Sno) FROM SC GROUP BY Cno;

聚集函数:

  • COUNT([DISTINCT | ALL] *) // 统计元组个数;
  • COUNT([DISTINCT | ALL] <列名>) // 统计一列中值得个数;
  • SUM([DISTINCT | ALL] <列名>) // 计算一列值的总和;
  • AVG([DISTINCT | ALL] <列名>) // 计算一列值的平均值;
  • MAX([DISTINCT | ALL] <列名>) // 求一列值中的最大值;
  • MIN([DISTINCT | ALL] <列名>) // 求一列值中的最小值;

实例:

// 查询学生总人数
SELECT COUNT(*) 
FROM student;

// 查询学生123选修课程的总学分数
SELECT SUM(Credit) 
FROM SC, Cource 
WHERE Sno = "123" AND SC.Cno=Course.Cno;

嵌套查询:

在SQL中,一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。

实例:

SELECT Sname 
FROM student
WHERE Sno IN (SELECT Sno
              FROM SC
              WHERE Cno = "2");

集合查询:

集合操作主要包括并操作UNION、交操作INTERSECT和差操作EXCEPT。

实例:

// 查询计算机科学系的学生及年龄不大于19岁的学生
SELECT *
FROM student
WHERE Sdept="CS"
UNION
SELECT * 
FROM student
WHERE Sage <= 19;

三. 数据更新

1. 插入数据

1)插入元组:

插入元组的INSERT语句的格式为:

INSERT 
INTO <表名> [(<属性列1> [, <属性列2>] ...)]
VALUES (<常量1> [, <常量2>] ...);

实例:

// 将一个新学生元组(学号:123;姓名:deng;性别:female;)
INSERT
INTO student (Sno, Sname, Ssex, Sdept, Sage)
VALUES ("123", "deng", "female", "IS", 22);

2)插入子查询结果:

插入子查询结果的INSERT语句的格式如下:

INSERT 
INTO <表名> [(<属性列1> [, <属性列2> ...])]

2. 修改数据

修改数据的一般语句格式如下:

UPDATE <表名>
SET <列名>=<表达式> [, <列名>=<表达式>] ...
[WHERE <条件>];

实例:

// 将学生123的年龄修改为22岁
UPDATE student
SET Sage=22
WHERE Sno="123";

// 将所有学生的年龄增加1岁
UPDATE student
SET Sage=Sage + 1;

3. 删除数据

删除数据的语句一般格式为:

DELETE
FROM <表名>
[WHERE <条件>];

四. 视图

视图是从一个或几个基本表(或视图)导出的表。它与基本表不同的是,它是一个虚表。数据库中只存放视图的定义,而不存放视图对应的数据,这些数据仍存放在原来的基本表中。

1. 创建视图

创建视图的一般格式为:

CREATE VIEW <视图名> [(<列名>, [<列名>] ... )]
AS <子查询>
[WITH CHECK OPTION];

WITH CHECK OPTION表示对视图进行增删改操作时,要保证增删改的行满足视图定义的子查询的条件表达式。

// 建立信息系学生的视图
CREATE VIEW IS_Student
AS
SELECT Sno, Sname, Sage
FROM student
WHERE Sdept="IS";

2. 删除视图

DROP VIEW <视图名> [CASCADE];
本文作者:子匠_Zijor,转载请注明出处:http://www.dengzhr.com/others/backend/sql/626