4.1 一般查询 4.2 高级查询 4.3 视 图
description
Transcript of 4.1 一般查询 4.2 高级查询 4.3 视 图
-
4.14.24.3
-
SQL ServerSELECT4.1
-
SQL Server T-SQL
-
SQL 4-1
-
T-SQL 4-1 T-SQL
-
+-~1*/%2++-3=>=
-
T-SQLAVGSUMCOUNTMAXMIN
-
GETDATEYEARMONTHDAYSUBSTRINGLEN6
-
SQL Server
-
mastermasterUSEUSEUSE database_nametrafficUSE traffic
-
SELECTSELECTSELECTSELECT select_list/**/FROM table_source/*
-
*/[WHERE search_condition]/**/[ORDER BY order_expression[ASC | DESC]]/**/
-
select_listselect_list ::= [ALL | DISTINCT][TOP n [PERCENT][WITH TIES]]FROM
-
WHEREORDER BYSELECT
-
SELECTcol_listSELECTSELECT col_list
-
col_list ::= { * /**/| {table_name |view_name |table_alias}.* /**/ | {column_name | expression | IDENTITYCOL | ROWGUIDCOL}
-
[[AS] column_alias]/**/| column_alias=expression /**/ }[,n]
-
SELECTSELECT column_name[,n]
-
FROM table_name*4-1trafficjsy
-
USE trafficSELECT FROM jsy4.1
-
4-2clSELECT * FROM cl4.2
-
4-3cl
-
SELECT AS FROM cl4.3
-
4-4jsySELECT number, name, markFROM jsy4.4
-
SELECT
-
4-5clSELECT =*0.8FROM cl4.5
-
SELECTSELECTSELECT[ALL | DISTINCT][TOP n [PERCENT]]
-
ALLDISTINCTTOP nnnSELECT
-
TOP n TOP n PERCENTn%4-6clSELECT FROM cl4.6
-
4-7clSELECT DISTINCT AS FROM cl4.7
-
4-8xcSELECT DISTINCT ,FROM xc4.8
-
4-9jsy 5SELECT TOP 5FROM jsy4.9
-
4-10jsy 5%SELECT TOP 5 PERCENTFROM jsy4.10
-
GOGOGOGO
-
WHERESELECT select_listFROMtable_name
-
WHERE
search_conditionWHERE
-
ANDORANDORNOTANDOR
-
NOTWHEREFROM(
-
expression operator expressionexpression textntextimageoperator
- =>!=
-
4-11 jsySELECTFROMjsyWHERE=4.11
-
4-12 cl10000SELECTFROMclWHERE= AND 100004.12
-
4-13 jsy2530SELECT,,FROMjsyWHERE!304.13
-
2T-SQLLIKELIKE
-
expression [NOT] LIKE pattern [ESCAPE escape_character]patternSQL Server
-
%_[][^]4-14 jsy
-
002SELECT,,FROMjsyWHERE LIKE 002%4.14
-
4-15 jsy002SELECT,,FROMjsyWHERE LIKE 002% AND LIKE %4.15
-
4-16 ddy7245 1SELECT,FROMjsWHERE LIKE 72_-1%4.16
-
4-17 ddy723234SELECT,FROMddyWHERE LIKE 72[2,3,4]%4.17
-
4-18 jsy12SELECT,,,FROMjsyWHERELIKE _ _[^1,2]4.18
-
ESCAPEESCAPE cl
-
89_202_102_12002189_2198924-19 cl2002
-
SELECT*FROMclWHERELIKE 02t_ _ ESCAPE t4.19t1_ 2_
-
3BETWEENBETWEENexpression [NOT] BETWEEN begin_expression
-
AND end_expressionbegin_expressionend_expressionbegin_expressionend_expressionBETWEENbegin_expression
- end_expressionNOT BETWEENBETWEEN>=
-
TRUEFALSE4-20 cl50%20003000
-
SELECT,,=*(1+0.5)FROMclWHERE*(1+0.5) BETWEEN 2000 AND 30004.20
-
ININexpression [NOT] IN ( subquery | expression[,n] )sbuquery
-
expression[,n]INNOT IN4-21 jsy
-
SELECTFROMjsy WHERE IN ,,4.21
-
WHEREWHERE NOT IN ,,
-
4IS NULLexpression IS [NOT] NULLNOT
-
expressionTRUEFALSENOT4-22 xc
-
SELECT,,,FROMxcWHEREIS NULL4.22
-
SELECTSELECTORDERBY
-
ORDER BY {expression [ASC | DESC]}[,n]expressionASCDESCORDERBY
-
ASCDESCnNtexttextimageORDER BY
-
4-23jsySELECT ,,,FROM jsyWHERE =ORDER BY DESC4.23
-
4SELECT ,,,FROM jsy
-
WHERE =ORDER BY 4 DESCORDER BYexpression
-
4-24clSELECT,,,=round(/*100,2)
-
FROM clORDER BY DESC4.24
-
112
-
4-25jsySELECT ,,,FROM jsORDER BY ,DESC4.25
-
SELECTxccd
-
clWHEREFROMJOIN
-
SELECTWHERExccd
-
SELECT *FROM xc,cdWHEREnmn*m
-
WHERE
-
4-26SELECT xc.*,cd.*FROM xc,cdWHERE xc.=cd.4.26
-
SELECT xc.*,cd.,cd.,cd.,cd.FROM xc,cdWHERE xc.=cd.
-
WHERE4-270112AX1320
-
SELECT jsy.,jsy.,jsy.,jsy.FROM xc,jsyWHERE jsy.=xc. AND xc.=0112AND xc.=AX13204.27
-
4-28jsyddyddy
- SELECT jsy., jsy., ddy., ddy.FROM jsy, ddyWHERE jsy.
-
SELECTWHERE
-
SQL ServerSELECT6458SELECT8
-
4-29SELECT t1., t3.,t4.,t3., t2.FROM jsy t1, ddy t2, cd t3, xc t4WHERE t4.=t1.
-
AND t4.=t.AND t4.=t.4.29FROM t1t2t3 t4jsy ddycd xc
-
JOIN FROMJOINFROM joined_tablejoined_talbeON
-
|CROSS JOIN |table_sourcejoin_typeON
-
search_conditionCROSS JOINjoined_tablejoin_type[INNER | {LEFT | RIGHT | FULL}][OUTER][]JOIN
-
INNEROUTER
-
LEFT OUTER RIGHT OUTERFULL OUTER
-
join_hint4-30
-
SELECT jsy., , FROM jsy INNER JOIN xc ON jsy.=xc. 4.30INNER
-
FROMWHERE4-310111AX1320
-
SELECT , FROM jsy JOIN xc ON jsy.=xc. WHERE =AX1320 AND=01114.31
-
4-32SELECT , , FROM ddy LEFT OUTER JOIN xc ON xc.=ddy.4.32
-
4-33SELECT xc.*, cl.FROM xc RIGHT JOIN cl ON xc.=cl.4.33
-
SUM ( )AVG ( )4.2
-
MIN ( )MAX ( )COUNT ( )
-
SUM/AVG/MIN/MAX ([ALL | DISTINCT]expression)COUNT({[ALL | DISTINCT]expression} | * )1ALLDISTINCT
-
NULL ALL2COUNT*3SUMAVGMINMAX4MINMAX
-
5COUNT unique identifiertextimagentext
-
4-34clSELECT =SUM ()FROM clWHERE =4.34
-
4-35jsySELECT =MAX ()FROM jsyWHERE =4.35
-
4-36SELECT COUNT(DISTINCT ) AS FROM xc4.36
-
4-37clSELECT COUNT(*) AS FROM cl4.37
-
GROUP BY GROUP BY[GROUP BY [ALL] | expression[,n][WITH{CUBE | ROLLUP}]]
-
expressionALLCUBEROLLUP
-
4-39clSELECT , COUNT(*) AS FROM clGROUP BY 4.39
-
GROUP BY SELECT GROUP BYGROUP BY4-40cl
-
SELECT , , =AVG(), =SUM()
-
FROM clGROUP BY , ORDER BY 4.40
-
4-41SELECT , , =SUM ()FROM clGROUP BY, WITH CUBE
-
4.41CUBE7
-
HAVING GROUPBYHAVINGHAVINGWHERE
-
HAVING[HAVING]search_condition4-42jsy
-
25SELECT , =AVG()FROM jsyWHERE =GROUP BYHAVING AVG()>25
-
WHEREGROUPBYHAVINGWHEREFROMGROUPBYWHEREHAVINGGROUPBY
-
4-430111SELECT FROM xcWHERE =0111GROUP BYHAVING COUNT(*)>=24.43
-
COMPUTE GROUPBYCOMPUTE
-
COMPUTE[COMPUTE(expression)[,n][BY colume_name[,n]]expression
-
BY4-44
-
SELECT, , FROM jsyWHERE IN (,)COMPUTE AVG()4.44
-
4-45SELECTFROM jsyWHERE IN (,)ORDER BYCOMPUTE AVG() BY4.45
-
4-444-45COMPUTE COMPUTE BYCOMPUTE BYCOMPUTE BYORDER BY4-454-38
-
COMPUTE BYGROUP BYGROUP BY COMPUTE BY
-
1GROUP BYSELECTSELECTGROUP BY2COMPUTE BYCOMPUTE
-
SELECT3COMPUTE BYORDER BYCOMPUTE BYORDER BYGROUP BYORDER BY
-
SELECT
-
T-SQLSELECT INEXISTSIN IN
-
expression [NOT] IN (subquery )subqueryexpressionINTRUEFALSE
-
NOT4-46AX1320
-
SELECT , , FROM ddy WHERE IN (SELECT DISTINCTFROM xcWHERE =AX1320)4.46
-
SELECT DISTINCT FROM xcWHERE =AX1320
-
011101120113ddy
-
ddy IN
-
ddy
-
IN2EXISTS IN
-
EXISTSEXISTS
-
[NOT]EXISTS (subquery)subqueryEXISTSTRUEFALSENOT4-477013
-
SELECT , , FROM jsy WHERE EXISTS (SELECT FROM xcWHERE =jsy. AND =7013)4.47
-
jsy
-
FALSEjsyjsy
-
TRUEjsy
-
EXISTSIN INEXISTSIN
-
EXISTS*4-48
-
SELECT DISTINCT FROM xc t1 WHERE EXISTS (SELECT *FROM xc t2WHERE t1.=t2. AND t1.!=t2.)4.48
-
4-48SELECT DISTINCT FROM xc t1 WHERE EXISTS (SELECT *FROM xc t2WHERE t1.=t2. AND t1.!=t2.)
-
t1t2t1t1t2t1
-
expression{< | > | = | = | | !> | !< | != } | {ALL | SOME | ANY }(subquery)
-
ALLSOMEANYALLTUREFALSESOMEANY
-
TUREFALSE4-49 SELECT ,FROM jsy
-
WHERE > ALL (SELECT FROM jsy WHERE IN (,,))4.49
-
4-500122SELECT , , FROM cd
-
WHERE = (SELECT FROM xc WHERE =0122)4.50
-
=INWHERE
-
SELECT , FROM cl WHERE > AVG()
-
4-51SELECT , FROM cl WHERE > (SELECT AVG() FROM cl)4.51
-
4-52SELECT , , FROM jsyWHERE IN
-
(SELECT DISTINCTFROM xcWHERE NOT IN SELECT FROM ddy WHERE =4.52
-
CASESELECTjsy
-
bit10
-
T-SQLCASECASECASECASECASECASECASE
-
input_expression{WHEN when_expression THEN result_rxpression}[,n][ELSE else_result_expression]END
-
input_expressionwhen_expressionresult_rxpressionelse_result_expression
-
4-53SELECT , , =CASE
-
WHEN 1989 THEN WHEN 2000 THEN WHEN 2001 THEN ENDFROM cl4.53
-
CASECASECASE WHEN boolean_expression THEN result_rxpression}[,n][ELSE
-
else_result_expression]ENDboolean_expressionWHENboolean_expressionTRUEWHEN
-
result_rxpressionTRUEboolean_expressionELSEelse_result_expressionELSENULL
-
4-54AX1320SELECT xc., , =CASE WHEN >=200
- THEN WHEN>=100 AND
-
ENDFROM xc, cdWHERE xc.=AX1320 AND xc.=cd.4.54
-
INTOSELECTINTO[INTO new_table]new_tableSELECT
-
SELECT SELECTCOMPUTEINTO
-
4-55jsySELECT, , INTO jsy_zhFROM jsyWHERE = 4.55
-
SQL Server4.3
-
SQL Server
-
Base Table
-
1
-
2
-
3
-
4
-
SQL Server
-
CREATE VIEWT-SQLCREATEVIEWCREATE
-
VIEW[.][.]view_name[(column_name[,n])]AS select_statement[WITH CHECK OPTION]select_staement
-
SELECTSELECTSELECTCOMPUTECOMPUTE BYORDER BY
-
INTOWITHCHECKOPTIONselect_sataemen
-
4.56CREATEVIEWjsy_01vAS
-
SELECT , , , , , FROM jsyWHERE=WITHCHECKOPTION4.56
-
jsy_01vjsyjsy_01vjsyjsy_01v
-
4-57CREATEVIEWjsy_xcvAS
-
SELECT jsy., jsy., xc., xc.FROM jsy, xcWHERE jsy.=xc. 4.57
-
4-58CREATEVIEWjsyxc_01vASSELECT jsy_01v., cd., cd., cd.,xc., xc.
-
FROM jsy_01v, cd, xcWHERE jsy_01v.=xc. AND xc.=cd..4.58
-
4-59CREATEVIEWjsy_avgASSELECT , , =AVG()
-
FROM jsy GROUP BY, 4.59
-
4-57 1traffic
-
4-604-61SQLSELECT
-
24-62
-
3jsyxc4-63
-
4jsyxcxc.SELECT
-
4-64SELECT
-
5SELECT4-65
-
6
-
4.60jsy_01v1980-1-1
-
SELECT ,FROM jsy_01vWHERE >'1980-01-01'4.66
-
4.61jsyxc_01v200SELECT , , FROM jsyxc_01vWHERE>2004.67
-
1SELECTTOPGROUP BYUNIONDISTINCT2UNION ALL
-
CHECKCREATE TABLE jsyxc1
-
( char(8), char(8), int NULL)CREATE TABLE jsyxc2( char(8), char(8),int NULL)jsyxc1jsyxc2
-
CHECKBETWEEN 0020100 AND 0020500 BETWEEN 0010100 AND 0010500CREATEjsyxc_v
-
CREATE VIEW jsyxc_vASSELETE *FROM jsyxc1UNION ALLSELECT *
-
FROM jsyxc2
-
4.62jsy_01vINSERT INTO jsy_01vVALUES(0020109, , , , 1980-02-14, 30)
-
4.63jsy_01vUPDATE jsy_01vSET =-2
-
DELETE4.64jsy_01v0020103DELETE FROM jsy_01vWHERE =0020103
-
traffic/4-68
-
4-69
-
ALTER VIEWALTER VIEW[.][.]view_name
-
[(column_name[,n])]AS select_statement[WITH CHECK OPTION]CREATE VIEW4-65jsyxc_01v
-
ALTERVIEWjsyxc_01v AS SELECT jsy_01. ,jsy_01., cd., cd.,cd.
-
FROM jsy_01v, xc, cdWHERE jsy_01.=xc. AND cd.=xc.
-
1traffic
-
2SQLSELECT
-
DROP VIEW{view_name}[,n]view_name
-
jsy_01vDROP VIEW jsy_01v
-
SQL ServerSELECT
-
SELECT
-
JIONGROUPBYHAVINGCOMPUTE
-
INEXISTSCASEINTO