SQL SERVERÓï·¨
1.´´½¨±í
(1)ÓÃCreate Table ÃüÁî´´½¨±í
Óï·¨:
Create Table tabl_name
({
}column_name As computed_column_expression
}
}[£¬...n]
)
[On {fiegroup | Default}]
[Textimage_On {fiegroup | Default}]
Àý×Ó:
´ò¿ªcustÊý¾Ý¿â£¬´´½¨Ò»¸ö±í£¬¸Ã±í°üº¬ÁËѧÉúµÄÓйØÐÅÏ¢£¬¼´ÓÐѧºÅ¡¢ÐÕÃû¡¢ÐԱ𡢳öÉúÈÕÆÚ¡¢¼®¹á¡¢ÁªÏµµç»°¡¢×¡Ö·ºÍ±¸×¢ÐÅÏ¢¡£
Use cust
Create Table students
(
number int not null£¬
name varchar(10) not null£¬
sex char(2) null£¬
birthday datetime null£¬
hometown varchar(30) null£¬
telphone_no varchar(12) null£¬
address varchar(30) null£¬
others varchar(50) null
)
ÔÚÕâ¸ö±íÖÐnumber±íʾѧÉú´úºÅ£¬Êý¾ÝÀàÐÍΪint£¬²»ÔÊÐíΪ¿Õ;name±íʾѧÉúÐÕÃû£¬Êý¾ÝÀàÐÍΪvarchar£¬³¤¶ÈΪ10£¬²»ÔÊÐíΪ¿Õ;sex±íʾѧÉúµÄÐÔ±ð£¬Êý¾ÝÀàÐÍΪchar£¬³¤¶ÈΪ2£¬ÔÊÐíΪ¿Õ;birthday±íʾѧÉúµÄ³öÉúÈÕÆÚ£¬Êý¾ÝÀàÐÍΪdatetime£¬ÔÊÐíΪ¿Õ;hometown±íʾѧÉúµÄ¼®¹á£¬Êý¾ÝÀàÐÍΪvarchar£¬³¤¶ÈΪ30£¬ÔÊÐíΪ¿Õ;telephone_no±íʾѧÉúµÄÁªÏµµçÄÔ£¬Êý¾ÝÀàÐÍΪvarchar£¬³¤¶ÈΪ12£¬ÔÊÐíΪ¿Õ;address±íʾѧÉúµÄסַ£¬Êý¾ÝÀàÐÍΪvarchar£¬³¤¶ÈΪ30£¬ÔÊÐíΪ¿Õ;others±íʾѧÉúµÄ±¸×¢ÐÅÏ¢£¬³¤¶ÈΪ50£¬ÔÊÐíΪ¿Õ¡£
2.Ð޸ıíµÄ½á¹¹
(1)ʹÓÃT-SQLÓï¾äÔö¼ÓºÍɾ³ýÒ»¸öÐÂÁÐ
Óï·¨:
Alter Table table
{
ADO
{[]
|colun_name As computed_column_expression
|[]
}[£¬...n]
|Drop
{Column column
}[£¬...n]
}
Àý×Ó:´ò¿ªcustÊý¾Ý¿â£¬ÐÞ¸ÄÆäÖеıístudentsµÄ½á¹¹£¬Ôö¼ÓÒ»¸öÐÂ×ֶΣ¬×Ö¶ÎÃûΪying£¬Êý¾ÝÀàÐÍÊÇvarchar£¬³¤¶ÈÊÇ10£¬Ã»ÓÐĬÈÏÖµ£¬³äÐíΪ¿Õ¡£
Use cust
Alter Table students Add ying varchar(10) null
´ò¿ªcustÊý¾Ý¿â£¬ÐÞ¸ÄÆäÖеıístudentsµÄ½á¹¹£¬É¾³ýÒ»¸ö×ֶΣ¬×Ö¶ÎÃûΪying¡£
Use cust
Alter Table students Drop Column ying
3.Ïò±íÖвåÈëÊý¾Ý
(1)Óà Insert Óï¾ä
Óï·¨ÈçÏÂ:
Insert [Into]
{table_name|view_name}[(column_list)]
{Values|values_list|select_statement}
×¢Òâ:ÔÚ²åÈëÊý¾Ýʱ£¬×Ö·ûÊý¾ÝºÍÈÕÆÚÊý¾ÝҪʹÓÃÒýºÅÒýÆðÀ´¡£
Àý×Ó:
Use cust
Insert Into students
Values (11£¬"Ó°×Ó"£¬"ÄÐ"£¬"1999-12-12"£¬"ºþ±±"£¬"83779805"£¬"Î人ÊÐÇÅ¿ÚÇø"£¬"VB°®ºÃÕß")
´ò¿ªcustÊý¾Ý¿â£¬Ïòstudents±íÖвåÈëÊý¾Ý
(2)ÓÃDefault Ñ¡Ïî
ÔÚ²åÈëÊý¾Ýʱ£
Ïà¹ØÎĵµ£º
1£¬SqlServer´æ´¢¹ý³ÌµÄÊÂÎñ´¦Àí
Ò»ÖֱȽÏͨÓõijö´í´¦ÀíµÄģʽ´ó¸ÅÈçÏ£º
Create procdure prInsertProducts
(
@intProductId int,
@chvProductName varchar(30),
@intProductCount int
)
AS
Declare @intErrorCode int
Select @intErrorCode=@@Error
Begin transaction
if @intError ......
(1)SEQNAME.NEXTVALÀïÃæµÄÖµÈçºÎ¶Á³öÀ´£¿¿ÉÒÔÖ±½ÓÔÚinsert into test values(SEQNAME.NEXTVAL) ÊÇ¿ÉÒÔÓà ÕâÑù£º
SELECT tmp#_seq.NEXTVAL
INTO id_temp
from DUAL; È»ºó¿ÉÒ ......
±¸·Ý
DECLARE @strPath NVARCHAR(200)
set @strPath = convert(NVARCHAR,getdate(),120)
set @strPath='hq'+rtrim(left(replace(@strPath,'-',''),8))
set @strPath = 'D:\sqlback\mydb\' + @strPath + '.bak'
BACKUP DATABASE [mydb] TO DISK = @strPath WITH NOFORMAT, NOINIT, NAME = N'mydb-ÍêÕû Êý¾Ý¿â ±¸·Ý', ......
½«b±íÖÐcallerÁеÄÖµ²åÈëa±íÖÐcallÁÐÖС£
create table a
(
fid int,
call varchar(20),
age int
)
create table b
(
fid int,
caller varchar(20),
parentId int
)
select * from a
select * from b
insert into a values(1,null,19)
insert into a values(2,n ......
asc °´ÉýÐòÅÅÁÐ
desc °´½µÐòÅÅÁÐ
ÏÂÁÐÓï¾ä²¿·ÖÊÇMssqlÓï¾ä£¬²»¿ÉÒÔÔÚaccessÖÐʹÓá£
SQL·ÖÀࣺ
DDL—Êý¾Ý¶¨ÒåÓïÑÔ(Create£¬Alter£¬Drop£¬DECLARE)
DML—Êý¾Ý²Ù×ÝÓïÑÔ(Select£¬Delete£¬Update£¬Insert)
DCL—Êý¾Ý¿ØÖÆÓïÑÔ(GRANT£¬REVOKE£¬COMMIT£¬ROLLBACK)
Ê×ÏÈ,¼òÒª½éÉÜ»ù´¡Óï¾ä£º
1¡¢ËµÃ÷£º´ ......