SQL常用语法整理

一、数据定义

1、Create（创建表）

create table <表名> (<列名> <数据类型> [列级完整性约束][表级完整性约束])

<必须项> [可写项]

列级完整性约束：

• NOT NULL:限制列取值非空。
• DEFAULT:指定列的默认值。
• UNIOUE:限制列取值不能重复。
• CHECK:限制列的取值范围。
• PRIMARY KEY:定义主码。
• FOREIGN KEY:定义外码。

除了NOT NULL，DEFAULT之外，其他均可以作为表级完整性约束

例如：
create table student(
sid int primary key AUTO_INCREMENT,
sname nvarchar(20) not null,
ssex nchar(2),
sage tinyint, 
tel char(11) unique not null,
sdept nvarchar(20) null
)

create table course(
cno char(6) not null,
cname nvarchar(20) not null,
credit tinyint, 
semester tinyint,
primary key(cno)
)

create table SC(
sno char(10) not null,
cno char(6) not null,
grade smallint,
primary key(sno,cno),
foreign key(sno) references student(sno),
foreign key(cno) references course(cno)
)

2、Drop（删除表）

drop table <表名>

3、Alert（修改表）

alert table <表名>
{alert column <列名> <新数据类型>	--修改列定义
| add <列名> <数据类型> <约束>		--添加新列
| drop column <列名>			--删除列
}

二、数据查询（select）

详细介绍:

SQL常用查询语法整理_凌木LSJ ：FLY的博客-CSDN博客

三、数据操纵

1、INSERT

insert [into] <表名>[<列名表>,<列名表n>] values (值列表)

其中：

<列名表>中的列名必须是表定义中已有的列名,值列表中的值可以是常量也可以是NULL值,各值之间用逗号分隔。
INSERT 语句用来新增一个符合表结构的数据行,将值列表数据按表中列定义顺序(或<列名表>中指定的顺序)逐一赋给对应的列名。

使用插入语句时应注意:
值列表中的值与列名表中的列按位置顺序对应,它们的数据类型必须一致。
如果<表名>后边没有指明列名,则新插入记录的值的顺序必须与表中列的定义顺序一致,且每一个列均有值(可以为空)。

【例1】将新生记录(1521104.陈冬,男,18岁,信息系)插入Student表中。语句如下:
INSERT INTO Student VALUES ('1521104','陈冬','男',18,·信息系`)

【例2】在SC表中插入一条新记录,学号为“1521104”,选的课程号为“c001”,成绩暂缺。语句如下:
INSERT INTO SC (Sno, Cno) VALUES('1521104','c001')

2、UPDATE

UPDATE <表名> SET <列名> = 表达式[,……n]
[WHERE 更新条件]

参数说明如下:
· <表名>给出了需要修改数据的表的名称。
·SET 子句指定要修改的列,表达式指定修改后的新值。
· WHERE 子句用于指定只修改表中满足 WHERE 子句条件的记录的相应列值。
省略WHERE子句,则是无条件更新表中的全部记录的某列值。UPDATE语句中
WHERE 子句的作用和写法同 SELECT 语句中的 WHERE 子句一样。

【例3】无条件更新
将所有学生年龄加1
update Student set Sage =Sage + 1

【例4】有条件更新
将10号学生的年龄改为22
update Student set Sage =22 where Sno = '10'

３、DELETE

DELETE [FROM] <表名> [where删除条件]

【例5】无条件删除
删除所有学生选课记录
delete from SC

【例6】有条件删除
删除学号为10的学生选课记录
delete from SC where Sno = '10'

四、数据控制

１、权限管理

1)授权语句

GRANT 对象权限名 【,… 】TO { 数据库用户名 | 用户角色名 } [ , ··· ]

2)收权语句

REVOKE 对象权限名 [ ,... 】 ON (表名 1 视图名) FROM(数据库用户名|用户角色名)[ , ··· ]

3)拒绝权限

DENY 对象权限名 [ , . ] ON (表名 1 视图名) TO (数据库用户名|用户角色名![ , ··· ]

其中“对象权限名”可以是:INSERT、DELETE、UPDATE和SELEC

【例7】为用户userl授予Student表的查询权限。语句为:
GRANT SELECT ON Student TO user

【例8】为用户user1授予SC表的查询权和插入权限。语句为:
GRANT SELECT , INSERT ON SC TO userl

【例9】收回用户userl对Student表的查询权限。语句为:
REVOKE SELECT ON Student FROM userl

【例10】拒绝user1用户具有SC表的更改权限。语句为:
DENY UPDATE ON SC TO userl

五、数据库备份与恢复

1、创建备份设备

语法格式：

sp_addumpdevice [设备类型] , [设备的逻辑名称], [设备的物理文件名] 

各参数含义为:
设备的类型：无默认值,可以是下列值之一：
·Disk:备份文件建立在磁盘上。
·Type:备份文件建立在Windows支持的任何磁带设备上。

设备的逻辑名称：无默认值,且不能为NULL。

设备的物理文件名：物理文件名必须遵从操作系统文件名规则或网络设备的通用命名约定,并且必须包含完整路径。无默认值,且不能为NULL。

【例11】添加本地网络磁盘备份设备。建立一个名为bk2的磁盘备份设备,其物理存位置及文件名为D:\dump\bk2.bak(假设D:\dump文件夹已存在)。其语句如下:
EXEC sp addumpdevice 'disk','bk2','D:\dump\bk2.bak'

2、备份

数据库备份

BACKUP DATABASE 数据库名 to disk = [path]

日志备份

BACKUP LOG 数据库名 to disk = [path]

参数说明：
path：保存备份文件的磁盘物理路径

【例12】对“学生数据库”进行一次完整备份,备份到MyBK_1备份设备上(假设此备份设备已创建好),并覆盖掉该备份设备上已有的内容。语句为:
BACKUP DATABASE 学生数据库 TO MyBK_1 WITH INIT

【例13】对“学生数据库”进行一次差异备份,也备份到MyBK_1备份设备上,并保留该备份设备上已有的内容。语句为:
BACKUP DATABASE 学生数据库 TO MyBK_1 WITH DIFFERENTIAL, NOINIT

【例14】 对“testDB”进行一次事务日志备份,直接备份到D:LogBack文件夹下(假设此文件夹已存在)的testDB_log.bak文件上。语句为:
BACKUP LOG testDB TO DISK = 'D:\LogBack\testDB_log.bak'

3、恢复

数据库恢复

RESTORE DATABASE 数据库名 from 备份设备名

日志恢复

RESTORE LOG 数据库名 from 备份设备名

六、视图

1、定义

CREATE VIEW <视图名> [<列名n>]
As
 SELECT语句

1)定义视图的查询语句中通常不包含ORDERBY和DISTINCT子句,这些语句可放在通过视图查询数据的语句中。

2)在定义视图时要么指定全部视图列,要么全部省略不写,不能只写视图的部分属性列。如果省略了视图的属性列名,则视图的列名与查询语句的列名相同。但在如下三种情况下必须明确指定组成视图的所有列名:
·SELECT语句中的查询列不是单纯的属性名,而是函数或表达式等无列名的列。
·多表连接时选出了几个同名列作为视图的字段。
·需要在视图中为列选用新的更合适的列名。

定义单源表视图
CREATE VIEW IS_Student
AS
SELECT Sno, Sname, Sage FROM Student WHERE Sdept ='信息系'

定义多源表视图
CREATE VIEW V_IS_S1 (Sno, Sname, Grade)
AS
SELECT Student.Sno , Sname , Sage FROM Student JOIN SC ON Student.Sno = SC.Sno
WHERE Sdept = '信息系' 

在已有视图上定义新视图
CREATE VIEW V_IS_S2
AS
SELECT Sno,Sname FROM V_IS_S1

定义带表达式的视图
CREATE VIEW V_BirthYear (Sno, Sname, BirthYear)
AS
SELECT Sno, Sname, 2015-Sage FROM Student

含分组统计信息的视图
CREATE VIEW S_G (Sno, AvgGrade)
AS
SELECT Sno, AVG (Grade) FROM SC GROUP BY Sno

2、修改

ALERT VIEW 视图名[<列名n>]
AS
    SELECT语句

3、删除

DROP VIEW <视图名>

4、查询

当成基本数据库一样使用