数据库语法

数据库创建

　　数据库命名遵循T-SQL命名规则，最长为128个字符，且区分大小写。

　　数据库使用的文件包括三类：主数据文件（*.mdf)、辅助数据文件(*.ndf)、日志文件(*.ldf)。

　　命令行方式创建数据库语法：

　　　　Create DataBase DataBase_name

　　　　　　[ 　　　　/*指定数据库文件和文件组属性*/

　　　　　　on [primary][<filespec>[,...n]]

　　　　　　[,<filegroup>[,...n]]

　　　　　　]

　　　　　　[log on {<filespec>[,...n]}] 　　/*指定日志文件属性*/

　　　　　　[collate collection_name]

　　　　　　[with <externa_access_option>]

　　　　　　[ for {attach [with<service_broker_option>]|attach_rebuild_log}]　　　　　　

　　　　　　[;]

　　　　<filespec>::=　　　　　　/*指定数据库文件的属性*/

　　　　｛

　　　　　　（

　　　　　　 name=logical_file_name, /*逻辑文件名*/

　　　　　　 filename='os_file_name',　/操作系统文件名/

　　　　　　 [,size=size[KB|MB|GB|TB]]　　/*初始大小*/

　　　　　　 [,MaxSize={max_Size[KB|MB|GB|TB]|unlimited}] /*最大大小*/

　　　　　　 [,Filegrowth=growth_increatement[KB|MB|GB|TB|%]] /*文件大小增长方式*/

　　　　　　）

　　　　｝

　　　　<filegroup>::=　　　　　　/*指定数据库文件组的属性*/

　　　　{

　　　　　　filegroup filegrout_name[default]

　　　　　　<filespec>[,...n]

　　　　}

　  示例：

create database test1
 on  primary
 (
 name='test1_Data',
 filename='d:\test1.mdf',
 size=3MB,
 filegrowth=10%
 ),
 (
  name='test1_data2',
  filename='d:\test2.ndf',
  size=3MB,
  maxsize=100MB,
  filegrowth=1MB
 )
 log on 
 (
  name='test1_log',
  filename='d:\test1.ldf',
  size=2MB,
  filegrowth=1MB,
  maxsize=5MB
 )


--修改数据库语句
alter database test1 
 modify file
 (
 name='test1_Data',
 maxsize=100MB,
 filegrowth=5MB
 )
go
--增加数据文件
alter database test1 
add file
(
 name='test_bak',
 filename='d:\Test1_bak.ndf',
 size=10MB,
 maxsize=20MB,
 filegrowth=5%
)


--删除数据文件
alter database test1 
 remove file test_bak


--添加文件组
alter database test1
add filegroup FGroup
go
--往文件组中添加数据文件
alter database test1 
add file
(
 name='test_bak',
 filename='d:\Test1_bak.ndf',
 size=10MB,
 maxsize=20MB,
 filegrowth=5%
)
to filegroup FGroup
go
--删除文件组。(删除文件组前，要删除数据文件，文件组删除时候必须为空)
alter database test1
 remove file test_bak
 --先删除数据文件，再删除文件组
alter database test1
 remove filegroup FGroup
--日志文件同上操作。
--数据库重命名。
alter database test1 
 modify name=Just_Test
--删除数据库
drop database Just_Test

表 是SQL Server 中的主要对象，用来存储和操作数据的一各逻辑结构，由行和列组成，也称为二维表。组成表的各列的名称及数据类型统称为表结构。

表是一行数据是一个记录。记录中每一列是一个字段。

　　　　表中 数据类型容易 混淆 char(n) 长度不足时候右边补足 固定长度。 nvarchar(n) n 表示存储的最大长度。

　　建表语法：

--建表语法
Create table [Database_name.[schema_name].|schema_name.]table_name
(
 {
  <column_definition>  /*列定义*/
  |column_name as computed_column_expression [persisted[not null]] /*定义计算列*/
        
 }
 [<table_constraint>][,...n] /*指定表的约束*/ 
)
[on {parition_scheme_name(partition_Column_name)|filegroup|"defaule"}]
 /*指定分区方案和存储表的文件组*/
[{Textimage_on{filegroup|"default"}}] /*指定存储text,ntext和image 类型数据的文件组*/
[;]

--列约束
<column_definition>::=
 column_name  data_type  /*指定列名和类型*/
 [collate collation_name] /*指定排序规则*/
 [null|not null]    /*指定是否为空*/
 [
  [Constraint constraint_name]
  [Default constant_expression] /*指定默认值*/
  |[IDentity[(seed,increment)]][not for replication] /*指定列为标志符*/
 ]
 [RowGuidCol]    /*指定列是否为全局标志符列*/
 [<Column_Constraint>[,...n]] /*指定列的约束*/

示例：

--创建学生表
use test1
go
create table Student
(
ID char(6) not null primary key,
Name nvarchar(20) not null,
Sex bit null default 1,
BirthDay datetime null,
Specialty nvarchar(30) null,
Credit int null,
Comment nvarchar(500) null
)

--创建成绩表
use test1 
go
create table Score
(
 Course char(10) primary key,
 Score real not null,
 StudentCount int not null,
 AvgScroe as Score/StudentCount persisted
)
/*没有persisted 关键字 计算列不能做primary Key、Unique、Default等约束*/

　　修改表结构语法：

--修改表结构
alter Table[Database_name.[Schema_name].|schema_name.]table_name
{
[alter column column_name /*修改已有列的属性*/
{
new_data_type[(precision[,scale])]
[Collate<collation_name>][Null|not null]
|{Add|Drop}[rowguidCol|persisted]
}
]
|[with{check|Nocheck}] Add /*添加列*/
{
<column_definition>|column_name as computed_Column_expression [persisted[not null]]|<table_Constraint>
}[,...n]
|Drop
{
[constraint] contraint_name[with(<drop_clusered_Constraint_Option>[,...n])]|column column_name
}[,...n]
|[with{check|noCheck}]{check|nocheck} constraint {all|constraint_name[,...n]}
|{enable|disable} trigger {all||trigger_name[,...n]}
|switch[partition source_partion_number_express] to [schema_name.]target_table
[partition target_partition_number_expression]
}

示例：

--学生表添加奖学金列
use test1
go 
alter table Student
 add scholarship tinyint null,
     Scholarship_level tinyint null
go

--删除奖学金列
use test1
go 
alter table Student
 drop column scholarship ,Scholarship_level
go

--修改列属性
use test1 
go 
alter table student
 alter column [Name] char(10)
go

alter table student
 alter column [Name] nvarchar(20)
go

　　创建分区表

　删除表语法：

　　 drop talbe talbe_name

　　示例:

　　use test1

　　go

　　drop table student

　　go

操作表数据 

　　插入记录语法

　　 [with<column_talbe_expression>][,...n]　　　　/*指定临时结果集，在Select语句中介绍*/

　　Insert [Top(expression) [percent]]

　　[Into]{talbe_name|View_name|rowset_Function_limited[with(<table_hint_limited>)[,...n]]}/*表名、视图名*/

　　{

　　[(column_list)]　　　　　　　　　　　　/*列列表*/

　　[<output clause>]　　　　　　　　　　/*output 子句*/

　　　　{

　　　　　　values ({default|null|expression}[,...n])|derived_table|exectute_Statement

　　　　}

　　}|default values　

示例：　　　

--插入表示例：

use test1
go
insert into student
values('121011','Tom',1,'1990-12-20','计科',500,null)

INSERT INTO [Student]([ID],[Name],[BirthDay],[Specialty],[Credit]) 
VALUES('121012','John','1991-1-2','计科',400 )
go

 

　　删除记录语法

--删除记录语法：
[with<common_talbe_expression>[,...n]]
delete [top(expression)[percent]]
[from]
{
table_name|view_name|rowset_function_limited [with(<table_hint_limited>[,...n])]
}
[from {<table_source>[,...n]] /*从table_source中删除数据*/
[<output clause>] /*output子句*/
[where {<Search_condition>|{[current of{{[Global] cursor_name}|cursor_variable_name}]}}] /*删除条件*/
[option (<query_hint>[,...n])]

示例：

use test1
go 
delete from student where id='121010'
go

　　delete talbe table_name  和turncate table table_name两者都用来清空表中数据 两者均不改变表结构而能清空表中数据。turncate 词句删除操作较快。 turncate语句会重置表中计数值为初化值。有外键约束引用的表参与了索引视图的表不能使用turncate删除数据。

　　修改记录语法

--修改记录语法
[with<column_table_expression>[,...n]]
update [top(expression)[percent]]
{
table_name|view_name|rowset_function_limited
}
set
{
column_name={expression|default|null}
|@variable=expression
|@variable=column=expression
}[,...n]
{
{[from{<table_source>}[,...][where <searche_conditon>]}
|[where current of {{[global]cursor_name}|cursor_variable_name}]
}
[option(<query_hint>)] /*使用优化程序*/

示例：

--示例
use test1 
go
    update student 
        set comment='A'
    where id='121011'
go

　　查找记录语法(重要)

 

--数据库查询语法
[with<common_table_expression>] /*指定临时命名的结果集*/
select [All|Distinct]
[Top expression[percent][with ties]
<select_list> /*指定要选定的列及其限定*/
[into new_table] /*Into 子句结果存入新表中*/
[from talbe_Source] /*From子句指定表或视图*/
[where search_condition] /*where 子句，指定查询条件*/
[group by group_by_expression] /*Group by子句，指定分组表达式*/
[Having search_Condition] /*Having 子句，指定分组统计条件*/
[order by order_expression][asc|Desc] /*order 子句，指定排序表达式和顺序*/

 

<select_list>::=
{
* /*所有列*/
|{table_name|view_name|talbe_alias}.* /*选择指定的表或视图的所有列*/
|{column_name|[]expression|$IDENTITY|$ROWGUID} /*选择指定的列*/
[[as] column_alias] /*as子句定义列的别名*/
|udt_column_name[{.|::}{{property_name|field_name}|method_name(argument[,...])}]
/*选择指定列并更改列的标题*/
}[,...n]

--示例

--选择所有列
use test1
go
select * from student
go
--选择表中指定列,并定义别名
--列中有空格时候要括起来
use test1
go
select id as '学 号',[name] as 名字
 from student
--不能在where 子句中使用列别名，因为执行where代码时候可能尚未确定列值
--where '名字' ='John'
go

--替换查询中的数据
--case 
-- when 条件1 then 表达式1
-- when 条件2 then 表达式2
-- ...
-- else 表达式
--end

use test1
go 
select [name],sex, 成绩=
    case 
        when credit>450 then '优秀'
        when credit>400 and credit<=450 then '合格'
       else 
        '不及格'
     end
from student

--计算前10 (top expression)列值 并去掉重复。(distinct)
use test1 
go 
select distinct top 10  credit =credit/10
from student
go

--top子句也可用于insert update delete语句

--聚合函数 用于对一组计算返回单个值，通常和group by 一起使用。
--常用的聚合函数 sum/avg/max/min/count/grouping(附加列) 
select sum(credit), avg(credit) from student

--where子句
where <search_condition>
 <search_condition>::={[not]<predicate>|(<search_condition>)}
    [{AND|OR}[not]{<predicate>|(<search_condition>}] [,...n]
 <predicate>::=
{
 expression{=|<|<=|>|>=|<>|!=|!<|!>}expression   /*比较运算符*/
 |match_expression [not] like pattern [escape escape_charactor] /*字符模式匹配*/
 |expression [not] between expression and expression        /*指定范围*/
 |expression is [not] null                        /*是不是空值判断*/
 |contains({column|*},'<contains_search_condition>')   /*包含式查询*/
 |freetext({column|*},'freetext_string')        /*自由式查询*/
  |expression [not] in (subquery|expression[,...n])  /*in子查询*/
 |expression{=|<|<=|>|>=|!=|!>|!<} {all|some|any|}(subquery)  /*比较子查询*/
 |exist(subquery)            /*Exist子查询*/
}

--比较表达式
select * from student
where credit>450 and sex=1

--模式匹配 
--% 代表0个或多个字符
--'_' 代表单个字符
--[]指定范围或集合
--[^]指定不属于范围
use test1
go
select * from student
where specialty like '%计%'

--范围比较 between (expression) and (expression);in (expression)
--空值比较 in [not] null
--contains 谓词 必须要在操作的表上建立全文索引。(见索引部分)
--freetext 谓词 必须要在操作的表上建立全文索引,精度没有contains高。
use test1
go 
select * from student 
 where freetext(*, 'o')
go

--子查询 子查询可以通常和谓词 [not] in/ [not] exist/ 及比较运算符(ALL/Some/any/)结合使用。
use test1
go
select * from student 
where specialty 
in(
 select specialty from student where [name] like '%o_n%'
)
go

--from 子句格式
[from {<table_Source>}[,...n]]

<table_source>::=
    {
table_or_view_name[[AS] table_alias][<tablesample_Clause>]    /*查询表或视图，可指定别名*/
[with<table_hint>[[,]...n]]
|rowset_function[[AS] table_alias]                /*行集函数*/
[(bulk_column_alias[,...n])]
|user_defined_function[[AS]table_alias]            /*指定表值函数*/
--|openXML<openxml_clause>                        /*XML文档*/
|derived_talbe[AS] table_alias [(column_alias)[,...]]    /**/
|<join_talbe>                                    /*连接表*/
|<pivoted_talbe>                                /*将行转换为列*/
|<unpivoted_talbe>                                /*将列转换为行*/ 
}

--table_or_view示例：
select * 
    from student as XSB 
    where XSB.credit>400

--rowset_function是一个行集函数，返回一个表或视图
--user_difined_function 表值函数，见用户自定义函数。
--derived_table 表示由子查询select执行返回的表，必须用AS
--关键字为子查询产生中间表定义别名。
--在为列指定别名时候必须为所有列指定别名。

select * from 
 (select * from student where credit>400) as XSB
 where sex=1

--连接表Join_table,连接查询涉及多个表。连接查询分两种形式1.标准连接谓词2T_SQL关键字Join

--标准连接谓词示例，连接谓词比较符可以是[<|<=|>|>=|!=|=]等。
--注意字段前面加表名区别，除非字段唯一可以不加。
select 学生表.*,成绩表.*
 from 学生表，成绩表
 where 学生表.学号=成绩表.学号

--Join关键字指定的连接
--Join关键字指定连接的表示方式。格式如下：
--<join_table>::=
--{<table_source><join_type><table_source> on <search_condition>
--|<table_source> cross join <table_source>
--left_table_source {cross|outer} apply right_table_source
--[[(]<joined_table>[)]]
--}
--<join_type>::=
--[{inner|{{left|right|Full}[Outer]}}[<join_Hint>]]Join

--内连接，使用inner join，使用ON指定的连接条件。使用内连接后可以指定where子句指定条件。
--返回符合条件的所有字段，不去重。
--示例：
select * 
 from 学生表 inner join 成绩表
  on 学生表.学号=成绩表.学号
  where 学生表.专业='计算机'

--外连接
--1.左外连接 (left outer join) 包含左表的中所有行和满足条件的行。
--2.右外连接 (right outer join)包含右表的中所有行和满足条件的行。
--3.完全外连接(full outer join )包含表的中所有行和满足条件的行。

--交叉连接 是对两个表进行笛卡尔积运算。

--Group by子句主要用于根据字段对行分组。
--语法格式
--[group by [all] group_by_expression[,...n]
--    [with{cube|rollup}]
--group_by_expression用于分组的表达式通常包含字段名，使用Group by 字句后Select子句中的列表中只能包含
--group by 指出的列或聚合函数中指定的列。
--with:cube或rollup操作符与聚合函数一起使用查询结果中增加的附加记录。
--示例：
use test1
go
select specialty,count(*) as '学生总数' from student
group by specialty 
--rollup产生汇总行
--cube对group子句中的各列所有可能组合均产生汇总行。

--Having子句。对group by 子句和聚合函数进行分组后，使用Having子句对分组数据进行进一步筛选。

--order by 子句，用于对查询的结果排序输出。
--order by 子句格式
-- [Order by
--{
--order_by_expression
--[collate collation_name]
--[asc|DEsc]
--}[,...n]
--]
--order_by_expression 是排序表达式，可以是列名、表达式或正整数，正整数时候按表中该 位置上的列排序。

--示例
select * from student
order by ID desc

--查询的其他语法
--1.Into 。使用into子句可以将Select结果保存到一个新建表中。
--[into new_table];

--2.Union。使用Union子句可以将两个或多个结果合并到一个表中，Union的基本规则是：
--(1).所有查询中的列数和列的顺序必须相同。(2).数据类型必须兼容。

 视图是从一个或者多个表中引出来的表，数据库中只存放视图定义不存放数据，数据仍存放在导出视图的基本表中。(也被称为虚表，可以像基本表一样操作)

创建视图语法格式：

　　Create view [Schema_name.]View_name[(column[,...n])]

[with<view_attribute>[,...n]]
AS select_statement[,...n]
[with check option]

<view_attribute>::=
{
[encryption]|[schemabinding]|[view_metadate]
}

示例：

--示例
use test1
go
create view SC_ST
as 
 select Course,score,id,[Name],[Specialty]
from Score,Student
--where 
--with check option

　　由于视图称为虚表，操作和基本表一样，有以下几点需要注意

　　1.更新视图要求，创建视图的Select语句没有聚合函数，且没有top，group by ,Union子句及Distinct关键字，不包含从基本表中通过计算所得的列,from子句中至少 有一个基本表。可以通过instead of 触发器创建的可更新视图。

　　2.Insert操作，当视图依赖多个基本表时候不能插入数据。

　　3.update操作，当视图依赖多个表时候，一次修改视图只能变动一个基本表。

　　4.Delete操作，当视图依赖多个表时候，不能Delete语句删除数据。

　　游标。游标可以对结果集进行逐行处理的能力，可以看做是一种特殊的指针

游标语法格式(T_SQL)
declare cursor_name cursor
[local|Global] /*游标作用域*/
[forword_only|scroll] /*游标移动方向*/
[Static|keyset|dynamic|Fast_forward] /*游标类型*/
[Read_only|scroll_locks|optimistic] /*访问属性*/
[Type_Warning] /*类型转换警告信息*/
[for select_statement] /*Select查询语句*/
[for update[of column_name[,...n]] /*可修改的列*/
[,...n]

示例：

--示例
declare XS_Cur cursor
dynamic
for 
select ID,[Name],Score
for XuShenBiao
 where specifity='Computer'
for update of score

--打开游标
--语法：open{{[Global]cursor_name}|cursor_variable_name}
--示例
open XS_cur
--读取数据
--fetch
--[[next]|prior|First|last|absolute{n|@nvar}|relative{n|@nvar}]
--from]
--{{[Global]cursor_name}|@cursor_variable_name}
--[Into @variable_name[,...n]]

--示例
fetch next from XS_Cur

open XS_Cur
fetch first from XS_Cur

--关闭游标
--语法
--close {{[Global]cursor_name}|@cursor_variable_name}
--示例
close XS_Cur.
--删除游标
--语法
--Deallocate{{[Golbal]cursor_name}|@cursor_variable_name}
--示例
deallocate XS_Cur.

 

 

 

索引是不用扫描整个数据表就可以对表中的数据实现快速访问的途径，是对表中的一列或者多列进行排序的一各结构。索引分为聚集索引(改变表的物理存放方式一个表中唯一)和非聚焦索引(不改变表的物理存放不唯一)。

　　

索引，利用SQL语句建立索引的语法
Create [unique] /*索引是否唯一*/
[clustered|unclustered] /*索引组织方式*/
Index index_name /*索引名称*/
ON{[database_name.[Schema_name].|schema_name.]table_or_view_name}
(column [ASC|DESC][,...n]) /*索引定义的依据*/
[include(column_name[,...n])]
[with(<relation_index_option>[,...n])] /*索引选项*/
[ON {partition_schema_name(column_name) /*指定分区方案*/
|fileGroup_name /*指定索引所在的文件组*/
|default}][,...n]

relation_index_option::=
{
PAD_INDEX={on|off}
|FillFactor=fillfactor
|sort_in_tempdb={on|off}
|Ignore_dup_ke={on|off}
|statistics_Norecompute={on|off}
|Drop_Existing={on|off}
|online={on|off}
|allow_row_locks={on|off}
|allow_page_locks={on|off}
|Maxdop=max_degree_of_parallelism
}

示例：

--示例
use test1
go
create index test_Score_id
on Score(Course)

--如果表中存在同名索引,创建时候要删除原来的索引.
create index test_Score_id
on Score(Course)
with(drop_existing=on)

重建索引,使用原来的列重建索引.
语法格式:
alter index{index_name|all}
ON{[database_name.[Schema_name].|schema_name.]table_or_view_name}
{
rebuild
[[with(<rebuild_index_option>[,...n])]
|[Partition=partion_number[with(<single_partition_rebuild_index_option>[,...n])]]
]
|Disable
|reorganize[Partition=partiton_number][with(Log_Compaction={on|off})]
|set(<set_index_option>[,...n])
}[,...n]

　　示例

　　

--示例：
use test1
go
alter index all on Score rebuild

删除索引
语法:
Drop index
{index_name on table_or_view_name[with(<drop_clustered_index_option>[,...n])][,...n]
|table_or_view_name.index_name[,...n]
}

Drop_clustered_index_option::=
{
MaxDop= max_degree_of_Parallelism
|online={on|off}
|move to{
partition_schema_name(column_name)|filegroup_name|"default"
}
}
删除primary key或unique约束创建的索引必须通过删除约束实现。

存储过程是为了完成特定功能的SQL语句的集合，语句编译后存储在数据库中，可以接受参数、输出参数、返回单个或者多个结果。存储过程独立于表存在。

触发器与表紧密关联，可以实现更加复杂的数据操作，更加有效地保障数据库系统中的数据的完整性和一致性，触发器基于一个表创建，但可以对多个表进行操作。