SQL数据库基础语法总结

SQL数据库基础操作语法总结

说明:

用{ }表示括号里的内容是必写的，用[ ]表示括号里的内容可写可不写；用<>表示在实际编写语句时可以用相应的内容代替；用A|B表示A或B的意思（只能选一个）；用[,...n]表示重复前面的内容。

-------------------------------------------------------------------------------------------

一、对数据库操作

1.创建数据库

基本语法格式：

CREATE DATABASE 数据库名

[ON

{[PRIMARY ]

(NAME=逻辑文件名,

FILENAME=’操作系统下的文件名和路径’

[,SIZE=文件初始容量]

[,MAXSIZE={文件最大容量|UNLIMITED}]

[,FILEGROWTH=文件的增量])

}[ ,...n ]

]

[LOG ON 

{(NAME=逻辑文件名,

FILENAME=’操作系统下的文件名和路径’

[,SIZE=文件初始容量]

[,MAXSIZE={文件最大容量|UNLIMITED}]

[,FILEGROWTH=文件的增量])

}[ ,...n ]

]

示例:

create database StuInfo

on primary 

(

name = StuInfo,filename='E:/作业练习/SQL Server/第2周/StuInfo.mdf',

size =3MB,

maxsize =10MB,

filegrowth=1MB

)

log on 

(

name = StuInfo_Log,

filename = 'E:/作业练习/SQL Server/第2周/StuInfo_Log.Ldf',

size = 1MB,

maxsize = 5MB,

filegrowth=1MB

)

Go

2.显示数据库信息

基本语法格式：

SP_HELPDB 数据库名

3.扩充数据库容量

基本语法格式:

ALTER DATABASE 数据库名

MODIFY FILE

(NAME=逻辑文件名,

[,SIZE=文件初始容量]

[,MAXSIZE={文件最大容量|UNLIMITED} ]

[,FILEGROWTH=文件增长幅度]

)

示例:

ALTER DATABASE NewSales

MODIFY FILE

(NAME=’NewSales’,SIZE=8MB)

GO

4.重命名数据库

语法格式:

SP_RENAMEDB ‘数据库原名’,’数据库新名’

5.删除数据库

语法格式:

DROP DATABASE 数据库名{,...n}

6.分离数据库

语法格式:

SP_DETACH_DB ‘数据库名’[,[@skipchecks=]’skipchecks’]

示例:

EXEC SP_DETACH_DB ‘NewSales’,’true’

-----------------------------------------------------------------------------------------------------------------------------------------------------

二、对数据表操作

1.创建表

语法格式:

CREATE TABLE[[数据库名.]表所有者.]表名

(列名  列的属性[,...n])

示例:

USE Sales

CREATE TABLE Sell

(

销售编号 int not null primary key IDENTITY(1,2),--主键,自动增长,1开始2增量

商品编号 int not null references Goods(商品编号),--设外键(方法一)

数量 int not null check( 数量>0 and 数量<0 ),--核查约束

售出时间 DateTime not null Default(getdate()),--默认时间

售货员工编号 char(6) not null,

--设外键(方法二)

Constraint FK_Sell_Employees FOREIGN KEY (售货员工编号) REFERENCES Employees(编号)

)

GO

2.禁(启)用约束

禁/启用约束语法格式:

ALTER TABLE 表名

CHECK/NOCHECK CONSTRAINT 约束名

3.查看表属性

语法格式:

EXECUTE SP_HELP 表名

4.添加列:

示例:

alter table employees

add 邮箱 varchar(30)

5.修改列:

示例:

alter table employees

alter column 邮箱 varchar(20)

6.删除列:

示例:

alter table employees

drop column 邮箱

7.重新命名表

语法格式:

EXECUTE SP_RENAME ‘旧表名’,’新表名’

8.删除表

语法格式:

DROP TABLE employees

-----------------------------------------------------------------------------------------------------------------------------------------------------

9.插入数据

语法格式:

INSERT [INTO] 目标表名

[(字段列表)]

{VALUES({DEFAULT | NULL | 表达式} [,...n] ) | 执行语句}

示例:

insert into employees (编号,姓名,性别)  --当只插入部分字段的值时,像这样标明字段标识出来.

values ('1304','李明',1)                --假如编号为自动增加,则应省去输入'1304',

--------------------------------------------------------------------------------------

--或者这样插入全部字段数据.假如还缺一个字段"销售部"

insert into employees values ('1304','李明',1,'销售部')

10.修改数据

语法格式:

UPDATE 目标表名

SET{列名={表达式 | DEFAULT | NULL }[,...n] }

{ [FROM]{<源表名>}[,...n]}

[WHERE <搜索条件> ]

示例1:

UPDATE Employees 

SET 电话='07713836386'

WHERE 编号='1304'

示例2:

UPDATE Goods 

SET Goods.零售价=Goods.零售价*0.95

FROM Goods,Employees

WHERE Goods.进货时间=’2005-5-20’AND Employees.姓名=’李明’
AND Employees .编号=Goods.进货员工编号

11.删除数据

语法格式:

DELETE [FROM] 目标表名

[FROM 源表名]

[WHERE {搜索条件}]

示例:

DELETE FROM 电话 where 编号='1304'

-----------------------------------------------------------------------------------------------------------------------------------------------------

三、设置表约束

//当需要添加多个约束时,只需要一个add关键字就可以了.

--添加主键约束

use sale 

go 

alter table goods

add constraint PK_GoodsNo primary key (商品编号)

--添加外键约束

use 数据库名 

go 

alter table 表名

add constraint 外键约束名 foreign key (列名)

references 被关联的表名(列名)

--设置唯一值.

use sale

alter table employees

add constraint IX_EmployeesName2 unique (姓名)

--设置核查约束.

use sale 

alter table employees

add constraint CK_EmployeesDep check (部门='财务部'or 部门='库存部'or 部门='销售部'or 部门='采购部')

--设置默认值约束,使表格自动记录下输入数据时的时间.

use sale 

alter table goods 

add constraint DF_GoodsDate Default (getdate ()) for 进货时间

--自动编号的方法(1是始量,2是增量)???

use sale 

alter table goods 

alter column 商品编号 char(8) not null identity(1,2)

--查看约束(sp_helpconstraint 不是随意设置的)

use sale 

go 

exec sp_helpconstraint employees

go

-----------------------------------------------------------------------------------------------------------------------------------------------------

三、数据库的查询

1.Select 语句基本格式:

Select [ All | Distinct ]

   [ Top n [Percent] [ With Ties ] ]

   列名

<列名>::=

{  *

   | { 表名 | 视图名 | 表的别名 }.*

   | { 列名 | 表达式 | Identitycol | Rowguidcol }

   | 别名 = 表达式

}

[,...n]

2.使用通配符“*”，返回所有列值

  示例：

Select  *  From employees

3.使用Distinct关键字消除重复记录

  示例：

Select Distinct 生产厂商 From goods

4.使用Top n( Percent )指定返回查询结果的前n行(个百分比)记录

  示例1：

Select Top 4 商品名称,进货价,数量 From goods

  示例2:

Select Top 30 Percent商品名称,进货价,数量 From goods

5.使用列别名改变查询结果中的的列名(三种方法)

  示例:

Select 编号 AS number, name=姓名, 电话 telephone From employees

6.使用表达式

  示例:

Select 商品名称,进货价*数量 AS 总金额 From goods

7.Like关键字

  示例:

Select * From employees Where 姓名 Like ‘李%’

  说明:

_ 代表任意一个字符(有些数据库使用*)

% 代表任意长度的字符串

[ ] 代表某个字符的取值范围,如:[a-e]指集合[abcde]内的任何一个字符

[^] 取非,与上相反.

8.Between关键字

  示例:

Select 商品名称,零售价 From goods Where 零售价 Between 2000 And 3000

9.In 关键字

  示例:

Select 商品名称,零售价 From goods Where 商品名称 In(‘打印机’,’摄像机’,’照相机’)

10.Null 关键字

  示例:

Select * Form employees Where 电话 Is Not Null

11.Order By 子句

  语法格式:

Order By 列名列表 [ Asc | DESC ]

  示例:

Select 商品名称,进货价 From goods Order By 进货价 Desc

  说明:当Order By 后什么都不写的时候,默认选择Asc

12.Into 子句

  语法格式:

Into 新表名

  示例:

Select Top 15 Percent 商品名称,进货价*数量 AS 总金额 Into 金额表

13.聚合函数

（暂略）

14．多表联接查询

（暂略）

-----------------------------------------------------------------------------------------------------------------------------------------------------

四、视图

1．语法格式：

Create View [<数据库名>.][<所有者>.] 视图名 [ (列名 [,...n]) ]

[ With { Encryption | Schemabinding | Veiw_Metadata } ]

AS

Select 查询语句

[ With Check Option ]

  说明:

(暂略)

示例1:

Create View v_sales1

As

Select 销售编号,sell.商品编号,sell.数量,商品名称,编号,姓名

From sell,goods,employees

Where goods.商品编号=sell.商品编号

And   goods.进货员工编号=employees.编号

And   goods.姓名=’赵飞燕’

-----------------------------------------------------------------------------------------------------------------------------------------------------

五、存储过程

1.语法格式：

Create Proc[edure] [所有者.] 存储过程名 [;整数]

[ {@参数 数据类型} [Varying] [=默认值] [Output] ][,...n]

[ With { Recompile | encryption | Recompile,Encryption | Execute_As_Clause } ]

[For Replication]

As

{ SQL语句[,...n] | External Name assembly_name.class_name.method_name }

  说明:

(暂略) 

示例1:

在Sales数据库中建立一个名为proc_find的存储过程，如果查询到指定的商品，则用RETURN语句返回1，否则返回0。

--创建存储过程

create proc proc_find

@spmc char(20)

as

if exists (select * from goods

where 商品名称=@spmc)

  return 1

 else

  return 0

--执行存储过程

declare @i int

exec @i=proc_find '打印机'

select @i as 返回值

示例2:

在Sales数据库中建立一个名为date_to_date_sales的存储过程，该存储过程将返回在两个指定日期之间的所有销售记录。

create proc date_to_date_sales

@d1 datetime ,@d2 datetime

as

select * from sell

where 售出时间 between @d1 and @d2

exec date_to_date_sales '2004-1-1','2005-1-1'

-----------------------------------------------------------------------------------------------------------------------------------------------------

六、触发器

1.语法格式：

Create Trigger 触发器名称

On { All Server | Database }

[ With Encryption | Execute As Clause [,...n] ]

{ For | After } { 事件名称 | 事件分组名称 } [,...n]

As { SQL语句 [ ; ] [,...n] | External Name assembly_name.class_name.method_name [ ; ] }

示例1:

在Sales数据库中，创建触发器tri_ReportGoods，当商品库存低于5件时发出库存量少请求进货的提示信息。

create trigger tri_ReportGoods

on sell

for insert

as

declare @sbh char(6),@smc char(20),@sysl int

select @sbh=inserted.商品编号,@smc=商品名称

  from inserted join goods on inserted.商品编号=goods.商品编号

Select @sysl=goods.数量-isnull(sum(sell.数量),0)

  from goods left join sell on goods.商品编号= sell.商品编号

  where goods.商品编号=@sbh

  GROUP BY goods.商品编号,goods.数量

if @sysl<5

  print @smc+'的库存少于5件，请及时进货'

--测试触发器

insert into sell

values('2',2,'2009-12-1','1301')

示例2:

在Sales数据库中，创建触发器tri_GoodsCount，当商品销售之后，相应的库存要有所变化。

create trigger tri_GoodsCount

on sell

for insert

as

declare @sbh char(6),@sl int

select @sbh=inserted.商品编号,@sl=inserted.数量

  from inserted join sell on inserted.商品编号=sell.商品编号

update goods

set 数量=数量-@sl

where goods.商品编号=@sbh

--测试触发器

insert into sell

values('2',2,'2009-12-2','1301')

select * from goods

示例3:

更改tri_ReportGoods触发器，当商品库存低于10件时才发出库存量少请求进货的提示信息，并对触发器定义文本进行加密。

alter trigger tri_ReportGoods

on sell

WITH ENCRYPTION

for insert

as

declare @sbh char(6),@smc char(20),@sysl int

select @sbh=inserted.商品编号,@smc=商品名称

  from inserted join goods on inserted.商品编号=goods.商品编号

Select @sysl=goods.数量-isnull(sum(sell.数量),0)

  from goods left join sell on goods.商品编号= sell.商品编号

  where goods.商品编号=@sbh

  GROUP BY goods.商品编号,goods.数量

if @sysl<10

  print @smc+'的库存少于10件，请及时进货'

示例4:

在Sales数据库中，创建一个DDL触发器，以防止表的删除。

create trigger tri_ddl

on database 

for drop_table

as

  print '不能删除该数据库中的表。'

  rollback

--测试触发器

drop table dbo.aa

示例5:

创建一个DDL触发器，保护当前服务器中的所有数据库不能被修改。

create trigger tri_alter_database

on all server 

for alter_database

as

  print '不能修改该数据库。'

  rollback