)
Create database NOT NULL
NULL
Drop database
default “” Alter database
sp_helpdb
Alter table
Alter table table_name sp_helpdb Add column_name data_type
ADD
Create global | local temporary table
on commit (presservr rows | delete rows)
Alter table (column _ name data type [(length)][NULL | NOT
Alter column address char(50) NULL], …..n)
Address50 | [LINK]
Alter table | [table_constraint] [,……n]
Alter column address ntext Column:
Address Datatype:
Alter table
Drop column duty
duty |
Drop table table_name restrict|cascade Global temporary:
Local temporary :
RESTRICT On commit preserve rows : COMMIT
DBMSCASCADE
On commit delete rows : COMMIT
SP_RENAME „‟‟‟ Create table table_name
( 1
Sp_rename „.‟column 2
UNIQUE, 3
CLUSTERED,
NONCLUSTERED,
Create UNIQUE[CLUSTERED | NONCLUSTERED]
Index index_name on table_name |
view_name(column_name[1,….,n])
Index_name
table_name | View_name
Column_name
no
Drop index table_name.index_name
Table_nameindex_name
primary keyuniquesql
server
Create view address_view Createview[schema_name.]view_name [(column[,….n])] As select * from employee_view
Where address=‟‟ [with
As select_statement[;]
[with check option] Createviewemp_view(view_sname,view_phone,view_address)
[encryption]
Createviewemp_view) [schemabinding]
[view_metadata] As select sname,phone,address } From employee schema_name: vIew_name: Column: Sp_rename „employee_view‟,‟view_emp‟ Select_statement:select check optionAlter view employee_view
select_statement As select sname,sex,phone,address Encryption:sys.syscommentscreate viewFrom employee
Schemabinding: Where sex =‟‟ View_metadata: Drop view employee_view
sql serverDB-LibraryODBCOLEDBAPI ()
Create view emppay_view
As select no,sname , basicpay , subsidy Create view employee_view From employee , pays As Where employee.no=pays.sno Select sname,phone,address
From employee Create view tables_view
As select student.stno,student.stname,achievement.suname, Create view employee_view achievement.achieve,class.cname As Select sname,phone,address from student,achievement,class From employee where achievement.stno=student.stno Where address=‟‟ and student.stpno=class.cld
From employee
Create view pays_view Select no,sname,phone As select no,sname , basicpay , subsidy From employee
From employee , pays order by no asc
Where employee.no=pays.sno ascDESC And subsidy>100 order by sex,birthday,sex
pays_view,employeepaysbirthday
100Order by sex asc , birthday desc
Select * from pays_view order by no
order by Select suname,sum(achieve) as „‟
pays_view100From achievement
1200 Group by suname
Create view basicpays_view Having sum(achieve)<200 As select * from pays_view Sum Where basicpay>1200
Having sum(achieve)<200200
GROUP BY NULL NULL Select [All|distinct]select_list Select address , count(*) as „‟ From table_name[,.,table_namen] From employee [join join_condition] Group by address [where search_condition] Count() [group by group_by_expression] WHERE [having search_condition] Select column from table_name [order by order_expression[asc | desc]] Where column condition value DISTINCT ConditionVALUE Select distinct no,sname,phone Where column condition value--- From employee order by no Condition Select no as „‟ Between Sname as „‟ Select column from table_name Phone as „‟ Where column between value1 and value2
TOP Select column from table_name Select top n [percent] select_list Where column in („vaule_list‟,‟vaule_list‟)
vaule_listcolumn From table_name
NPERCENTNot in
N
NOTBETWEEN
NULL Select * from depart
IS NOT NULL Where not emp_depart=‟‟
emp Select * from employee
Where address is not null
Order by no Select * from employee
Where address is not null Where address is null Where birthday not between „1979-01-01‟ and „1982-01-01‟
Order by no
1979-01-011982-01-01birthday LINK
CHARvarchartextLINK
+-*/% _
% Select no,name,nvalues/12 as [] From sellvalue
[^] Nvalues/12 / Link „%‟NOT LINK no1/(no2+no3) as
SQLUNIONINTERSECT
(AND)OR Select no,sname from employee
[where sname link „%‟] SELECT * from employee
Where sex=‟‟ Union INTERSECT And (OR) address link „%%‟ Select stno,stname from student
employee And (OR) birthday>‟1980-01-01‟
INTERSFCT Where condition1 on (condition2 and condition3)
IN
CASTCASTdatetime Getdate()
CONVERT
CASESelect no,sname,sex,cast(birthday as char(11)) as „‟
From employee stle
Order by no 0 Mon dd yyyy hh:mi am/pm
char() 9 Mon dd yyyy hh:mi:ss:mmmm am/pm
13 Dd mon yyyy hh:mm:ss:mmm
Case 20 Yyyy-mm-dd hh:mi:ss
Select no,sname,sex,birthday,= 21 Yyyy-mm-dd hh:mi:ss:mmm
Case
When birthday>‟1981-01-01‟ and „1989-01-01‟ then „‟ 8 hh:mm:ss
When birthday>‟1970-01-01‟ and „1981-01-01‟ then „‟ 14 Hh:mm:ss:mmm When birthday>‟1955-01-01‟ and „1970-01-01‟ then „‟ End 1 Mm/dd/yy From employee 2 yy.mm.dd Order by no 3 Dd/mm/yy 4 dd.mm.yy SQL 5 dd-mm-yy Substring() 6 Dd mon yy Convert() 7 Mon dd,yy Getdate() 10 mm-dd-yy 11 Yy/mm/dd Day() 12 yymmdd Month() Select convert(varchar,getdate(),20) as „ Year() ‟ Dateadd(day,3,indate) as „Dateadd() indateday3Select na,sname,sex,birthday, indate Conwert(varchar,birthday,11) as „‟ 12Datediff() From employees
Datename()
,day,1980-01-01 01
Datepart()
ABC(numeric_expression) ASCII(
ASCII Ceiling(numeric_expression)
ASCII char(
Floor(numeric_expression) lower(
Round(numeric_expression, upper(
ltrim(
Sin(float_expression) rtrim(
Asin(float_expression) len(
left(
Cos(float_expression)
integer_expressionAcos(float_expression )
Tan(float_expression) right(
integer_expression
Exp(float_expression)
Substring(
start_positionlength Sqrt(float_expression)
Sign(numeric_expression)
Charindex 10-1
(
substring_expression
3.1415926
expression0~1Rand()
replicate(char_expression,
char_expressioninteger_expression)
reverse(
Select 12 表示用char_expression3替 Replace(
From 12 换在char_expression1中的
Where 1.stno=2.stno 子串char_expression2
And 3.stno=2.stno UPPERLOWER
UNION JON Upper
Select top 10 supplierid ,upper(companyname) as
„companyname‟
Upper(contactname) as „contactname‟
From suppliers
supplierssupplierid ,
companyname ,contactname
companyname ,contactname
Select distinct a1.studname,a1.studdname,a1.studmark
From studachieve as a1,studachieve as a2
Where a1.studid=a2.studid Sum()
And a2.studmark<60
Order by a1.studname Max()
Min()
NATURAL JOIN
Avg()
Select tno,tname,tage,gname,gmark
From teachers NATURAL JOIN groups Count()
where
Select s.tno,s.tname,s.tage,t.gid,t.gname,t.gmark Select sum() as „‟
From teachers as s, From
Groups as t
Where s.gid=t.gid
INNER JOIN CROSS JOIN
=
Select s.tno,s.tname,s.tage,t.gid,t.gname,t.gmark Select s.tno,s.tname,s.tage,t.gid,t.gname,t.gmark From teachers as s From teachers as s
Inner join groups as t Cross join groups as t
On s.gid=t.gid Order by tno
Order by tno
=
on s.gid {=}t.gid 1selectfromwhere \having
Group by.where
(left outer join) 2
3INselect
select
NULL where
(right outer join) 4
Select s.tno,s.tname,s.tage,t.gid,t.gname,t.gmark 5EXISTS selectFrom teachers as s * left outer join(right outer join) groups as t 6order by On s.gid=t.gid 7255 Order by tno
NULLSelect studid,studname,studdname,studmark
From studachieve
Select s.tno,s.tname,s.tage,t.gid,t.gname,t.gmark Where studname=(select stname from student where stname From teachers as s =‟‟)
full outer join groups as t
On s.gid=t.gid
Order by tno
()1001003
subjects
ANY >\>=\!< ALL
EXISTS All ANY
Selecvt * from subjects as T IN = ANY
Where subid!=‟1001003‟ NOT IN <> ANY
or Exists (select * from studachieve <\<=\!> ALL
where studdname=t.subname) ANY
order by subid Select * from teacher
Where tage>any(select tage from teacher)
NOT EXISTS
NOT EXISTSSQL
NOT EXISTS Create view showstud
Selecvt * from subjects as T As select studid,studname,studdname,studmark
Where not Exists (select * from studachieve From studachieve
where studdname=t.subname) Where studmark>(select avg(studmark) from studachieve)
order by subid IN
Select column_name from table_ame
HAVING Where expression [not] in
(select expression from table_name
where column_name2<60 )
EXISTS
Flase
Select * from subjects as t
Where exists(select * from studachieve
UNIQUE Where studdname=t.subname)
Order by subid
EXISTS
Selecvt * from subjects as T
Where subid!=‟1001003‟
And Exists (select * from studachieve
where studdname=t.subname)
order by subid