Transact_SQL Small Manual - Statement Features - Data Operation SELECT - Retrieves Dark Data from Database Table and Column Insert - Add New Data Rows to Database Tables - Remove Data Rows from Database Table - Update Database Table Data - Data Definition CREATE TABLE - Create a database table DROP TABLE - Remove Table from the Database - Modify Database Table Structure Create View - Create a View DROP View - Remove View from the Database Crete Index - - Create an index DROP INDEX for the database table - Remove index Create Procedure from the database - Create a stored procedure Drop Procedure - Remove the stored procedure from the database Create Trigger - Create a trigger DROP TRIGGER - Remove from the database Trigger CREATE Schema - Add a new mode to the database DROP Schema - Remove a mode from the database Create Domain - Create a Data Value ALTER DOMAIN - Change the Domain Definition DOMAIN - Delete a domain from the database - Data Control GRANT - Grant User Access Deny - Reject User Access REVOKE - Unused User Access Right - Transaction Control CommT - End Current Services Rollback - Tone Current Services SET Transaction - Defines Current Transaction Data Access Features - Programming Sqldeclare - Setting the Cursor for Query - To Query Data Access Plan Open - Retrieve Query Result Open a Camper Fetch - Retrieve a line Query Results Close - Close the Cursor Prepare - Prepare SQL Statements for Dynamic SQL Statements Execute - Dynamically perform SQL statement Describe - Describe ready query
--- Local variable declare @id char (10) - set @ID = '10010001'select @ID =' 10010001 '
--- Global variable - must start with @@
- IF Elsedeclare @x int @y int @z @ @X = 1 @Y = 2 @ z = 3if @x> @YPRINT 'X> Y' - Print string 'x> Y'Else if @Y> @zprint 'y> Z'Else Print' Z> Y '
--Caseuset e_wage = Casewhen Job_LAGE = '1' TEN E_WAGE * 1.08When Job_WAGE * 1.08When Job_LEVEL = '2' TEN E_WAGE * 1.07When Job_LEVEL = '3' TEN E_WAGE * 1.06ELSE E_WAGE * 1.05nd
--While Continue BreakDeclare @x int @x = 1 @ y = 1WHILE @X <3beginprint @X - the value of print variable x While @Y <3begInselectr @c = 100 * @ x @Yprint @C - Value of print variable C SELECT @Y = @y 1ndselect @X = @X 1select @Y = 1END
--Waitfor - Example Waiting for 1 hour 2 points after 3 seconds to perform SELECT statement waitfor delay '01: 02: 03'select * from Employee - Case, etc. Behind 11:00, then execute SELECT statement waitfor Time ' 23: 08: 00'Select * from Employee *** SELECT ***
select * (column names) from table_name (table) where column_name operator valueex host) select * from stock_information where stockid = str (nid) stockname = 'str_name' stockname like '% find this%' stockname like '[a-zA- Z]% '----------------- (^ exclude specified range) ------- (^ exclude specified range) ----- ---- You can only use wildcards in WHERE clauses using the LIKE key) or stockpath = 'stock_path'or stocknumber <1000and stockIndex = 24Not Stocksex =' Man'StockNumber Between 20 and 100stockNumber in (10, 20, 30) Order by stockid desc (ASC) --------- Sort, Desc - Descending, ASC-Ascending Order By 1, 2 --------- BY list stockName = (Select stockname from stock_information Where Stockid = 4) -------- subquery --------- Unless you can make sure that the inner SELECT returns only a row of values, -------, otherwise it should be outside where clause with a qualifier in select distinct column_name form table_name --------- distinct specified column values retrieved unique, not repeated select stocknumber, "stocknumber 10" = stocknumber 10 from table_nameselect stockname, "stocknumber" = count (*) from table_name group by stockName --------- Group by Packets the table, specify the same value in the specified column HAVING Count (*) = 2 ------ --- Having Selected Group
Select * from table1, table2 where table1.id * = Table2.ID ------- The left external connection, there is a Table1 in Table1 to indicate Table1.ID = * Table2.ID ---- ---- Right external connection
Select stockname from table1Union [all] ----- UNION merge query result set, all-reserved repeat line Select stockname from table2
*** insert ***
INSERT INTO TABLE_NAME (STOCK_NAME, STOCK_NUMBER) Value ("XXX", "XXXXX") Value (SELECT stockname, stock_table2) --- value is a SELECT statement
*** UPDATE ***
Update table_name set stockName = "xxx" [Where stockid = 3] stockname = defaultStockName = nullstocknumber = stockname 4 *** delete ***
Delete from table_name where stockid = 3truncate table_name ----------- Delete all rows in the table, still keep the integrity of the table DROP TABLE_NAME ------------- complete Delete table
*** alter Table *** --- Modify the database table structure
Alter Table Database.OWNER.TABLE_NAME ADD Column_name Char (2) NULL ..... sp_help table_name ---- Display Table Existing Create Table Table_name (Name Char (20), Age Smallint, Lname Varchar (30)) INSERT INTO TABLE_NAME SELECT ......... --- Implement the method of deleting columns (Create a new table) ALTER TABLE TABLE_NAME DROP CONSTRAINT stockName_DEFAULT ---- Delete Stockname's default constraint
*** Function (/ * Common function * /) ***
---- Statistical Function ---- AVG - Ask the average COUNT - Statistics Max - Search MIN - Summary SUM - Search
--AVGUSE PANGUSELECT AVG (E_WAGE) AS DEPT_AVGWAGEFROM EMPLOYEGROUP BY DEPT_ID
--MAX - Survey the highest payroll Name USE PANGUSELECT E_NAMEFROM EMPLOYEEWHERE E_WAGE = (SELECT MAX (E_WAGE) from EMPLOYEE)
--Stdev () - stdev () function Returns the standard deviation of all data in the expression
--Stdevp () - stdevp () function returns the overall standard difference
--VAR () - VAR () function returns a statistical variation of all values in the expression
--VARP () - VARP () function Returns the total variation
---- Arithmetic function ----
/ *** Triangle function *** / sin (float_expression) - Returns sinusoidal COS (FLOAT_EXPRESSION) in an arc - Returns the cosine TAN (FLOAT_EXPIXPRESSION) in the arc-represented angle - Returns the angle represented by radians Orthodium COT (FLOAT_EXPRESSION) - Returns the remainder of the angle represented / *** Anti-triangular function *** / asin (float_expression) - Returns the sinusoidal is the angle ACOS (float_expression) indicated by the FLOAT value. - Returning the cosine is the angle ATAN (float_expression) of the float value - Return to the angle ATAN2 (float_expression1, float_expression2) of the float value - Return to orthode chitting is FLOAT_EXPRESSION1 / FLOAT_EXPRES-SION2 in the arc. Corner DegRees - Transform the arc into angle returns the same data type as the expression can be the same as the integer / money / real / float type radians (numeric_expression) - convert the angle to the arc return to the same data as the expression Types can be -integer / money / real / float type exp (float_expression) - Return Expression LOG (FLOAT_EXPRESSION) - Returns the natural log of log10 (float_expression) - Returns 10 For the end of the logarian SQRT (FLOAT_EXPRESSION) - Returns the square root of the expression / *** Nearly close to the similar value *** / ceiling (numeric_expression) - Return> = The minimum integer returned by the expression is the same as the expression To - ITEGER / MONEY / REAL / FLOAT Type Floor (Numeric_Expression) - Returns the data type returned by <= the minimum integer returned by the expression is the same as the expression of the same --integer / money / real / float type Round - Returns the data returned by integer_expression as accuracy - Types and expressions can be integer / money / real / float type ABS (Nume) RIC_EXPRESSION) - The data type returned by the error of the expression can be the same as the expression of the expression of -integer / money / real / float type Sign (Numeric_Expression) - the positive number of the test parameters returns 0 zero value 1 positive or -1 negative data type - the same can be used for Integer / Money / REAL / FLOAT type PI () - return value is π 3.1415926535897936rand ([Integer_Expression]) - Do it with optional [integer_expression] Seeds worth 0-1 random floating point numbers
---- String Function ---- ASCII () - Function Returns the ASCII code value of the character express left-end character Char () - Function is used to convert the ASCII code to the character - if not entered 0 ~ 255 The ASCII code value char function returns a NULL value LOWER () - function transforms all the strings to lowercase UPPER () - Function Converts the string to the uppercase STR () -Function to convert numeric data conversion For the character data Ltrim () - function removes the space of the string head removes the RTRIM () - function removes the space of the string of the string to Left (), substring (), substring () - Function Return to some strings Charindex (), Patindex () - Function Returns the start position of a specified substring in the string Soundex () - function Returns a four-bit character code - Soundex function can be used to find sound similar strings, but Soundex functions Return to numbers and Chinese characters to return 0 Values DIFFERENCE () - Functions Returns the difference between the two character expressions returned by the SoundEx function - 0 two SoundEx functions return value different - 1 two SoundEx function returns the first character of the value of the same - 2 Two SoundEx functions return values The first two characters of the value of the same - 3 Two SoundEx functions return values The first two three characters of the value - 4 two SoundEx functions The return value is completely the same quotename () - function returns a string from a specific pen / * select quotename ('abc', '{') quotename ('ABC') running results --------- ------------------------- {{abc} [ABC] * /
Replicate () - Function Returns a string of repeating character_expression specified / * SELECT Replicate ('ABC', 3) Replicate ('ABC', -2) Run the results below ----------- ---------- Abcabcabc null * /
Reverse () - Function Run the specified string Character arrangement reverse the Replace () - function returns the string of the specified substring / * SELECT Replace ('ABC123G', '123', 'DEF') The results are as follows ----------- ----------- Abcdefg * /
The space () - function returns a blank string stuff () - function with a specified length, replacing a string of the position length with another subster
---- Data Type Conversion Function ---- Cast () Function Syntax As next () (
SELECT CAST (100 99 As Char) Convert (VARCHAR (12), getdate ()) running results --------------------------- --- ------------ 199 Jan 15 2000
---- Date Function ---- DAY () - Function Returns the Date_Expression Date () - Function Returns the Month Value Values in Date_Expression Year () - Function Returns the Year Value Dateadd in Date_Expression (
Author Blog: http://blog.9cbs.Net/wtadminxjeri/
Modify Kunming
- Open a single account CREATE TABLE BAK_KM_TS_W_BILL_DETAIL AS (SELECT * from TS_W_BILL_DETAIL T WHERE DELIVERY_ID LIKE 'KM%' AND (MODIFY_USER = 'L537004' or modify_user = '123'))
DELETE from TS_W_BILL_DETAIL T Where Delivery_id Like'KM% 'and (modify_user =' l537004 'or modify_user =' 123 ')
- Begin Financial Account Create Table Bak_KM_TS_W_BILL_FINANCIAL AS (SELECT * from TS_W_BILL_FINANCIAL T where Delivery_id Like'KM% ')
delete from ts_w_bill_financial t where delivery_id like'km% '- warehouse segment table number create table bak_km_ts_w_card_iccid as (select * from ts_w_card_iccid t where warehouse_id =' km ') delete from ts_w_card_iccid t where warehouse_id =' km '
- Number segment table in Kunming warehouse CREATE TABLE BAK_KM_TS_W_CARD_ICCID_DIRE AS (SELECT * from TS_W_CARD_ICCID_DIRE T WARE WAREHOUSE_ID = 'KM')
Delete from ts_w_card_iccid_dire t where warehouse_id = 'km'
- Outlet Single Main Table Create Table Bak_KM_TS_W_DELIVERY AS (SELECT * from TS_W_DELIVERY T WHERE DELIVERY_ID LIKE'KM% 'AND (MODIFY_USER =' L537004 'or modify_user = 123'))))))
Delete from TS_W_DELIVERY T Where Delivery_id Like'KM% 'and (MODIFY_USER =' L537004 'or modify_user =' 123 ')
- out of the warehouse
Create Table Bak_KM_TS_W_DELIVERY_DETAIL AS (SELECT * from TS_W_DELIVERY_DETAIL T where Delivery_id Like'KM% 'AND (MODIFY_USER =' L537004 'or modify_user =' 123 ')))
Delete from ts_w_delivery_detail t where delivery_id Like'KM% 'and (modify_user =' L537004 'or modify_user =' 123 ')
- Introduction financial flow
CREATE TABLE BAK_KM_TS_W_FINANCE_DETAIL AS (SELECT * from TS_W_FINANCE_DETAIL T where
Warehouse_id = 'km' and purchase_id like Like'KM% 'and trunc (finance_time) Delete from ts_w_finance_detail t where warehouse_id = 'km' and purchase_id Like'KM% 'and Trunc (Finance_time) - Printing create table bak_km_ts_w_print as (select * from ts_w_print t where delivery_idlike'km% 'and modify_user =' L537004'and trunc (print_time) Delete from ts_w_print t where delivery_id like'km% 'and modify_user =' l537004'and trunc (print_time) - Intention Single Mode (Contract Number and Supplier Number Does Not Being Foreign Key: The State Separation Center is not necessarily in accordance with the relationship of the contract, supplier's relationship, CREATE TABLE bak_km_ts_w_purchase as (select * from ts_w_purchase t where purchase_id like'km% 'and (modify_user =' L537004 'or modify_user =' 123 ') and trunc (purchase_date) Delete from TS_W_PURCHASE T where Purchase_ID Like'KM% 'and (modify_user =' l537004 'or modify_user =' 123 ') and trunc (purchase_date) < TO_DATE ('2004-07-31 00:00:00', 'YYYY-MM-DD HH24: MI: SS') - Schedule storage create table bak_km_ts_w_purchase_detail as (select * from ts_w_purchase_detail t where purchase_id like'km% 'and (modify_user =' L537004 'or modify_user =' 123 'or modify_user =' L101098 ')) Delete from TS_W_PURCHASE_DETAIL T where PURCHASE_ID LIKE'KM% 'and (MODIFY_USER =' L537004 'or modify_user =' 123 'or modify_user = 'L101098') AND trunc (modify_time) < TO_DATE ('2004-07-31 00:00:00', 'YYYY-MM-DD HH24: MI: SS') - Kunming warehousing single text table create table bak_km_ts_w_purchase_dire as (SELECT * from TS_W_PURE_ID = 'KM' or Warehouse_ID = 'KM') DELETE from TS_W_PURCHASE_DIRE T where card_dire_id = 'km' or warehouse_id = 'km' - Receivables The main table Create Table Bak_km_ts_w_receive as (SELECT * from TS_W_Receive T where modify_user = 'l537004' or modify_user = '123' or modify_user = 'l101098') DELETE from TS_W_RECEIVE T WHERE MODIFY_USER = 'L537004' or modify_user = '123' or modify_user = 'l101098' - Receivables Home Table CREATE TABLE BAK_KM_TS_W_RECVABLE AS (SELECT * from TS_W_RECVABLE T WHERE (Modify_user) = 'L537004' or modify_user = '123' or modify_user = 'l101098')) Delete from ts_w_recvable t where (modify_user = 'l537004' or modify_user = '123' or modify_user = 'l101098') - receivable balance table ??? SELECT * from TS_W_RECVABLE_BEGIN T where Recv_dept_id = '112' - Currency Tube Introduction Current Accounts CREATE TABLE BAK_KM_TS_W_RULER_DETAIL AS (SELECT * from TS_W_RULER_DETAIL T WHERE PURCHASE_ID LIKE'KM% 'and warehouse_id =' km 'and trunc (ruler_time) Delete from ts_w_ruler_detail t where purchase_id Like'KM% 'and warehouse_id =' km 'and trunc (ruler_time) - Kuguan library blotters create table bak_km_ts_w_warehouseman as (select * from ts_w_warehouseman t where delivery_id like'km% 'and (modify_user =' L537004 'or modify_user =' 123 'or modify_user =' L101098 ')) Delete from TS_W_Warehouseman T Where Delivery_id Like'KM% 'and (modify_user =' L537004 'or modify_user =' 123 'or modify_user = 'L101098') - Warehouse summary month table ???????????????? SELECT * from TS_W_WAREHOUSE_MONTH T where warehouse_id = 'km' - Provincial Center issued a summary date ???????????? Select * from ts_w_warehouse_stat t where warehouse_id = 'km' -------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Update ts_wreHouse_amount set card_amount = 'where warehouse_id =' km 'and card_id =' 'and card_spec_id =' ' Update ts_wreHouse_amount set card_temp_amount = '' where warehouse_id = 'km' and card_id = '' and card_spec_id = '--------------------------- -------------------------------------------------- ------------- Modified Intriors Sign Select * from TS_W_DELIVERY T where dePT_ID = '112' AND (modify_user = 'a') AND TRUNC (Delibery_Date) Update ts_w_delivery set Delivery_is_enter = '0' where dept_id = '112' AND (modify_user = 'a') AND trunc (Delibery_Date)
