Transact-SQL是一种定义、操作并控制数据的语言

Transcription

1 SQL 1 SQL Transact-SQL Transact-SQL Transact-SQL SQL Query Analyzer SQL Query Analyzer SQL Query Analyzer osql Transact-SQL SELECT WHERE osql 1.1 Transact-SQL Transact-SQL SQL Server (ANSI) (ISO) SQL SQL-92 ANSI ISO 1992 SQL Transact-SQL SQLServer2000 SQL-92 ANSI-SQL Transact-SQL ANSI-SQL Transact-SQL 1.2 Transact-SQL SQL Server SELECT SELECT

2 2 MCSE 2000 SQL 2000 Transact-SQL SQL Server Transact-SQL (DDL) (DCL) (DML) (DML) (DDL) (DDL) DDL DDL CREATE ALTER DROP sysadmin dbcreator db_owner db_ddladmin role DDL DDL Northwind customer cust_id, company contact phone CTRATE TABLE customer (cust_id int, company varchar(40), contact varchar(30), phone char(12)) (DCL) (DCL) GRANT DENY REVOKE Transact-SQL

3 SQL 3 sysadmin dbcreator db_owner db_secudtyadmin public products USE nothwind GRANT SELECT ON products TO public (DML) (DML) DML SELECT INSERT UPDATE DELETE sysadmin dbcreator db_owner db_datawriter Northwind category ID product name product ID unitprice SELECT category ID, product name, product ID, unitprice FROM products 1.3 Transact-SQL DML Transact-SQL directives Comments Identifiers Variables SQL Server Transact-SQL SQL Server SQL Server SQL Server SQL Server Transact-SQL SQL Server Transact-SQL Transact-SQL ad hoc Transact-SQL

4 4 MCSE 2000 SQL 2000, Transact-SQL ( ) Transact-SQL EXEC EXEC Transact-SQL EXEC SQL 1 (--) Transact-SQL SELECT productname (unitsinstock - unitsonorder) -- Calculates inventory supplierid FROM products SELECT productname (unitsinstock - unitsonorder) -- Calculates inventory -- supplierid FROM products

5 SQL 5 2 (/*) (*/) SQL * This code retrieves all rows of the products table and displays the unit price the unit price increased by 10 percent and the name of the product * SELECT unitprice (unitprice * 1.1) productname FROM products SQL /* int */ BEGIN SQL Server 1

6 6 MCSE 2000 SQL #) 2 Transact-SQL ([]) SELECT * FROM [Blanks In Table Name] SET QUOTED_IDENTIFER ( ) SELECT * FROM Blanks in Table Name SET QUOTED_IDENTIFIER on Transact-SQL SQL SQL Server ( ) 1

7 SQL 7 int tinyint smallint bigint numeric decimal money smallmoney float real 2 datetime smalldatetime 1 3 char nchar varchar nvarchar 4 binary varbinary bit (Unique Identifiers) uniqueidentifier (GUID) SQL SQL Server text ntext rowversion Sql_variant 7 (image&text) (BLOB) Unicode Unicode image text ntext 8 Tables 9 (Cursors) (DDL)

8 8 MCSE 2000 SQL (Nullability) SQL Transact-SQL DECLARE Northwind@EmpID char(20) Dodsworth FROM employees WHERE LastName=@vlname AS EmployeeID EmployeeID 9 (1row(s)affected) SELECT Transact-SQL Transact-SQL

9 SQL 9 1 (Aggregate) products unitprice SELECT AVG(unitprice) AS AvgPrice FROM products AvgPrice (1row(S)affected) 2 (scalar) (char varchar ) SQL Server SELECT DB_NAME() AS database database

10 10 MCSE 2000 SQL 2000 Northwind (1row(s)affected) 3 (Rowset) Transact-SQL (table reference) titles SELECT * FROM OPENQUERY(OracleSvr, SELECT name id FROM owner titles ) SELECT 'ANSI:', CONVERT(varchar(30), GETDATE(), 102) AS Style UNION SELECT 'Japanese:', CONVERT(varchar(30), GETDATE(), 111) UNION SELECT 'European:', CONVERT(varchar(30), GETDATE(), 113) {constant/column_name/function/ (subquery)} [{arithmetic_operator/string_operator/ AND OR NOT} {constant/column_name/function/ (subquery)} } 1 SQL Server Transact-SQL (+) (-) (*) (/) ( )( )

11 SQL 11 = < >= <= <> (+) (AND) (OR) (NOT) WHERE 2 ( ) SQL Server ( ) () * -+ + NOT AND OR SQL Server SQL Server 1.3.9

12 12 MCSE 2000 SQL SELECT OrderID, ProductID, (UnitPrice * Quantity) AS ExtendedAmount FROM [Order Details] WHERE (UnitPrice * Quantity) > OrderlD ProductID ExtendedAmount (6 row(s) affected) Transact-SQL CASE SELECT UPDATE 1Statement level BEGIN END, Transact-SQL IF ELSE, SQL Server IF ELSE WHILE, WHILE BREAK CONTINUE WHILE IF EXISTS (SELECT * FROM orders

13 SQL 13 WHERE customerid = fran ) PRINT *** Customer cannot be deleted *** ELSE BEGIN DELETE customers WHERE customerid = frank PRINT *** Customer deleted *** END 2 Row level CASE true CASE WHEN false ELSE SQL Server ELSE CASE CASE expression END {WHEN expression THEN result} [ n] [ELSE result] WHILE tinyint = 5 IF (@n BETWEEN 4 and 6) BEGIN WHILE (@n > 0) BEGIN END AS Number CASE WHEN (@n 2) = 1 THEN ELSE EVEN END AS Type - 1 ODD

14 14 MCSE 2000 SQL 2000 ELSE END PRINT NO ANALYSIS Number Type 5 EVEN (1 row(s) affected) Number Type 4 ODD (1 row(s) affected) Number Type 3 EVEN (1 row(s) affected) Number Type 2 ODD (1 row(s) affected) Number Type 1 EVEN (1 row(s) affected) (BEGIN END) SQL Server osql SQL Query Analyzer DUMP BACKUP SQL Server Transact-SQL SQL Server Transact-SQL Transact-SQL

15 SQL SQL Query Analyzer(SQL ) SQL Server SQL Query Analyzer, Transact-SQL SQL Query Analyzer SQL Server Transact-SQL SQL Query Analyzer SQL Query Analyzer SQL SQL Server SQL Server SQL 1.5 SQL Query Analyzer SQL Query Analyzer SELECT CREATE INSERT

16 16 MCSE 2000 SQL 2000 ALTER Transact-SQL Transact-SQL 1.6 SQL Query Analyzer SQL Query Analyzer SQL Server Templates SQL Query Analyzer SQL Query Analyzer <parameter_name, data_type, value> parameter_name data_type value Replace Template Parameters 1.7 osql osql Transact-SQL osql Transact-SQL osql SQL Server ODBC( ) ODBC (APl) osql Transact-SQL SQL Server osql 1 (Interactive Mode)

17 SQL 17 osql Transact-SQL SQL Server Transact-SQL Qtilt EXIT osql 2 (Script Execution Mode) Transact-SQL osql osql Transact-SQL ( * txt) Osql osql [-Sserver_name] [-E] [-U login_id] [-P password] [-I input_file] [-O output_file][-?] osql -S server_name SQL Server server_name SQL Server osql -E -U login_id ID -U-P SQL Server -P password -P osql -P osql NULL -P -U SQL Server - i input=file Transact-SQL -o output_file osql -o Unicode Unicode 1.8 Transact-SQI Transact-SQL Transact-SQL

18 18 MCSE 2000 SQL Transact-SQL Transact-SQL Transact-SQL EXECUTE SQL EXECUTE tsql tsql_string'} ])} northwind SQL USE library varchar(30) = northwind = products EXECUTE ( USE +@dbname+ SELECT productname FROM productname Chaj Chang Anise Syrup EXECUTE{ SET NOCOUNTON + SELECT lastname reportsto FROM employees

19 SQL 19 WHERE reportsto IS NULL ) Iastname Fuller reportsto NULL (Batches) 1 Transact-SQL Transact-SQL 2 Transact-SQL SQL Query Analyzer osql ODBC ODBCAPI 3 SQL Server SQL Server 4 AS CREATE PROCEDURE CREATE VIEW CREATE TRIGGER CREATE RULE AS CREATE DEFAULT

20 20 MCSE 2000 SQL 2000 CREATE VIEW CREATE DATABASE CREATE TABLE CREATE VIEW CREATE VIEW CREATE DATABASE CREATE TABLE CREATE VIEW CREATE VIEW Transact-SQL Transact-SQL SQL Query Analyzer.sql SQL Query Analyzer osql 1.9 SELECT SELECT SELECT SELECT SELECT [ALL DISTINCT]<select_list>

21 SQL 21 FROM{<table_source>}[ n) [WHERE<search_condition>] select_list WHERE WHERE FROM (select list) ( ) <select_list> = { * {table_name view_name table_alias}.* {column_name expression IDENTITYCOL ROWGUIDCOL} [[AS]column_alias] column_alias=expression } [ n] *, employees employeeid lastname firstname title SELECT employeeid lastname firstname title FROM employees employeeid lastname firstname title 1 Davolio Nancy SalesRepresentative 2 Fuller Andrew VicePresident Sales 3 Leverling Janet SalesRepresentative

22 22 MCSE 2000 SQL Peacock Margaret SalesRepresentative 5 Buchanan Steven SalesManager 6 Suyama Michael SalesRepresentative 7 King Robert SalesRepresentative 8 Callahan Laura lnsidesalescoordinator 9 Dodsworth Anne SalesRepresentative (9 row(s) affected) WHERE WHERE WHERE <search_condition>::= {[NOT]<predicate> (<search_condition>)} [{AND OR}[NOT]{<predicate> (<search_condition>)}] }[ n] WHERE char nchar varchar nvarchar text datetime smalldatetime ( ) SELECT WHERE employees employeeid lastname firstname title employeid 5 SELECT employeeid lastname firstname title FROM employees WHERE employeeid = 5 employeeid lastname firstname title 5 Buchanan Steven SalesManager (1 row(s) affected) 1.10 WHERE

23 SQL 23 SELECT > < >= <= and<> = LIKE NOTLIKE AND OR NOT NOTBETWEEN NOTIN IS NULL NOT NULL = > < >= <= <> NOT employees SELECT lastname city FROM employees WHERE country= USA Iastname city

24 24 MCSE 2000 SQL 2000 Davolio Fuller Leverling Peacock Callahan (5 row(s) affected) Seattle Tacoma Kirkland Redmond Seattle orders orderid customerid SELECT orderid customerid FROM orders WHERE orderdate < orderid VINET TOMSP HANAR customerid VICTE SUPRD HANAR... (22 row(s) affected) LIKE LIKE LIKE char nchar varchar nvarchar datetime LIKE

25 SQL 25 LIKE BR LIKE Br% LIKE een BR Br een LIKE en en LIKE _en LIKE [CK] LIKE [S-V]ing en C K ing S-V Customers Res SELECT companyname FROM customers WHERE companyname LIKE Restaurant companyname GROSELLA-Restaurante LonesomePineRestaurant TortugaRestaurante (3 row(s) affected) AND OR NOT AND OR NOT 1 (parentheses)

26 26 MCSE 2000 SQL SQLServer2000 NOT AND OR T SELECT productid productname supplierid unitprice FROM products WHERE(productname LIKE T OR productid=46) AND(unitprice>16 00) productid productname supplierid unitprice 14 Tofu ThoringerRostbratwurst Tarteausucre (3 row(s) affected) T SELECT productid productname supplierid unitprice FROM products WHERE(productname LIKE T ) OR (productid=46 AND unitprice>16 00) productid productname qsuppl ierid unitprice 54 Tourtiere Tarteausucre

27 SQL Tunnbr'6d Teatime Chocolate Biscuits Tofu Thoringer Rostbratwurst (6 row(s) affected) WHERE BETWEEN BETWEEN SQL Server BETWEEN AND (>=x AND<=y) (>x AND<y) NOT BETWEEN NOT SELECT productname unitprice FROM products WHERE unitprice BETWEEN 10 AND 20 productname UnitpriCO Chai 18 Chang 19 AniseedSyrup 10 GenenShouyu Pavlova 15 5 SirRodney'sScones 10.. (29 row(s) affected) WHERE IN IN

28 28 MCSE 2000 SQL 2000 IN OR SQL Server (NULL) = NULL NOT IN NOT suppliers SELECT companyname country FROM suppliers WHERE country IN( Japan Italy ) companyname Tokyo Traders Mayumi's Formaggi Fortini s.r.l. Pasta Buttini s.r.l. (4 row(s) affected) country Japan Japan Italy Italy suppliers IN OR 1 SELECT companyname country FROM suppliers WHERE country= Japan OR country= Italy companyname TOkyOTraders Mayumi's FormaggiFortini s Pasta Buttini s.r.l. country Japan Japan Italy Italy

29 SQL 29 ( 4row(s) affected) (NULL) 0( ) ( ) IS NULL CREATETABLE IS NOT NULL suppliers fax SELECT companyname fax FROM suppliers WHERE fax IS NULL companyname ExoticLiquids New Orleans Cajun Delights Tokyo Traders fax NULL NULL NULL Cooperativa de Quesos LasCabras NULL ORDER BY (ASC) (DESC) ORDER BY SQL Server sp_helpsort

30 30 MCSE 2000 SQL 2000 ORDERBY SQL Server SQL Server ORDER BY ORDERBY ORDERBY text image ORDER BY products SELECT productid productname categoryid unitprice FROM products ORDER BY categoryid unitprice DESC productid productname categoryid Unitprice 38 C6tedeBlare pohCoffee Chang Chai SteeleveStout Chartreuseverte Lakkalik60ri OutbackLager SasquatchAle LaughingLumbejackLager RhOnbr?iuKlosterbier GuaranfiFantAstica NorthwoodsCranberrySauce Siropd'erable Grandma'sBoysenberrySpread

31 SQL 31 (77 row(s) affected) ORDERBY SQLServer SELECT productid productname categoryid unitprice FROM products ORDER BY 3 4 DESC DISTINCT DISTINCT DISTINCT SELECT ORDER BY DISTINCT DISTINCT ORDER BY ORDER BY suppliers SELECT DISTINCT country FROM suppliers ORDER BY country country Australia Brazil Canada

32 32 MCSE 2000 SQL 2000 Denm~k Finland France Germany ltaly Japan Netherlands Norway Singapore Spain Sweden UK USA (16row(s)affected) DISTINCT suppliers SELECT Country FROM Suppliers ORDER BY COuntry country Australia Australia Brazil Canada Canada Denmark Finland France France France Germany

33 SQL 33 Germany Germany ltaly ltaly Japan Japan Netherlands Norway Singpaore Spain (29row(s)affected) AS SELECT column_namelexpression AS column_heading FROM table_name CREATE TABLE SQL Server 128 employees firstname lastname employeeid EmployeeID SELECT firstname AS First lastname AS Last employeeid AS Employee ID FROM employees First Last Employee ID Nancy Davolio 1

34 34 MCSE 2000 SQL 2000 Andrew Fuller 2 Janet Leverling 3 Margaret Peacock 4 Steven Buchanan 5 Michael Suyama 6 Robert King 7 Laura Callahan 8 Anne Dodsworth 9 (9row(s)affected) Literals, SELECT column_name 'string literal' [, column_name 'string_literal'...] FROM table_name employees employeeid Identification " number SELECT firstname lastname Identification number employeeid FROM employees firstname lastname employeeid Nancy Davolio ldentificationnumber 1 Andrew Fuller ldentificationnumber 2 Janet Leverling ldentificationnumber 3 Margaret Peacock ldentificationnumber 4 Steven Buchanan ldentificationnumber 5 Michael Suyama ldentificationnumber 6 Robert King ldentificationnumber 7 Laura Callahan ldentificationnumber 8 Anne Dodsworth ldentificationnumber 9 (9row(s)affected) 1.12

35 SQL 35 SQLServer (Ad Hoc) SELECT (join) (Form) SQLServer, ad hoc batches (auto-parameterization) 1 SQL Server SQL Server $ SELECT * FROM products WHERE unitprice = $12 5 SELECT * FROM products WHERE unitprice = 12 5 SELECT * FROM products WHERE unitprice = $12 5

36 36 MCSE 2000 SQL SQL Server USE library SELECT*FROM member WHERE member_no = 7890 SELECT*FROM member WHERE member_no = 1234 SELECT*FROM member WHERE member_no = SQLServer ( NO TBETWEEN NOT IN NOT NULL) LIKE LIKE ORDER BY SQL Server