Common select statements

-- Sentence skill
-- Data Operations
Select -- retrieve data rows and columns from database tables
Insert -- add new data rows to the database table
Delete -- delete data rows from a database table
Update -- update data in the database table
-- Data Definition
Create Table -- create a database table
Drop table -- delete a table from a database
Alter table -- modify the database table structure
Create View -- create a view
Drop View -- delete a view from a database
Create index -- create an index for the database table
Drop index -- delete an index from a database
Create procedure -- create a stored procedure
Drop procedure -- delete a stored procedure from a database
Create trigger -- create a trigger
Drop trigger -- delete a trigger from a database
Create schema -- Add a new schema to the database
Drop schema -- delete a schema from the database
Create Domain -- create a Data Value Domain
Alter Domain -- change domain definition
Drop Domain -- delete a domain from the database
-- Data Control
Grant -- Grant the user access permission
Deny-Deny User Access
Revoke -- revoke User Access Permissions
-- Transaction control
Commit -- end the current transaction
Rollback -- abort the current transaction
SET transaction -- define the data access features of the current transaction
-- ProgrammaticSQL
Declare -- set the cursor for the query
Explain -- describe the data access plan for query
Open -- open a cursor for retrieving query results
Fetch -- retrieve a row of query results
Close -- close the cursor
Prepare -- prepare for dynamic executionSQLStatement
Execute -- dynamically ExecuteSQLStatement
Describe -- describe the prepared Query
--- Local variables
Declare @ ID char (10)
-- Set @ ID = '20140901'
Select @ ID = '000000'
--- Global variables
--- It must start @
-- If else
Declare @ x int @ Y int @ Z int
Select @ x = 1 @ Y = 2 @ z = 3
If @ x> @ Y
Print 'x> y' -- print the string 'x> y'
Else if @ Y> @ Z
Print 'y> Z'
Else print 'z> y'
-- Case
Use pangu
Update employee
Set e_wage =
Case
When job_level = '1' then e_wage * 1.08
When job_level = '2' then e_wage * 1.07
When job_level = '3' then e_wage * 1.06
Else e_wage * 1.05
End
-- While continue break
Declare @ x int @ Y int @ C int
Select @ x = 1 @ Y = 1
While @ x = the minimum integer of the expression returns the same data type as the expression.
-- Integer/money/real/Float Type
Floor (numeric_expression) -- Return,) -- function returns the length value of the specified field in the table
Col_name (,) -- Name of the specified field in the function return table, that is, the column name.
Datalength () -- the actual length of the data returned by the function
Db_id (['database _ name']) -- number of the database returned by the function
Db_name (database_id) -- Name of the database returned by the function
Host_id () -- The function returns the name of the server computer.
Host_name () -- returns the name of the server computer.
Identity ([, seed increment]) [as column_name])
-- The identity () function is used only in the select into statement to insert an identity column to the new table.
/* Select Identity (INT, 1, 1) as column_name
Into newtable
From oldtable */
Isdate () -- determines whether the given expression is a reasonable date.
Isnull (,) -- The function replaces the null value in the expression with the specified value.
Isnumeric () -- The function determines whether the given expression is a reasonable value.
Newid () -- The function returns a value of the uniqueidentifier type.
Nullif (,)
-- The nullif function returns the value of expression1 if expression1 is equal to expression2. If the value is not equal, the return value is expression1.
ExquisiteSQLStatement
Note: copy a table (only copy structure, source table name: a new table name: B)
SQL: Select * into B from a where 11
Description: copy a table (copy data, source table name: A target table name: B)
SQL: Insert into B (a, B, c) Select D, E, F from B;
Description: displays the article, Submitter, and last reply time.
SQL: Select a. Title, A. username, B. adddate from Table A, (select max (adddate) adddate from table where table. Title = A. Title) B
Description: External join query (table name 1: Table A name 2: B)
SQL: Select a. a, a. B, A. C, B. C, B. D, B. F from a left out join B on A. A = B. C
Note: Five minutes in advance of the schedule
SQL: Select * from Schedule where datediff ('minute ', F Start Time, getdate ()> 5
Note: Two associated tables are used to delete information that is already unavailable to the primary table in the secondary table.
SQL:
Delete from info where not exists (select * From infobz where info. INFID = infobz. INFID)
Note :--
SQL:
Select a. Num, A. Name, B. upd_date, B. prev_upd_date
From Table1,
(Select X. Num, X. upd_date, Y. upd_date prev_upd_date
From (select num, upd_date, inbound_qty, stock_onhand
From Table2
Where to_char (upd_date, 'yyyy/mm') = to_char (sysdate, 'yyyy/mm') X,
(Select num, upd_date, stock_onhand
From Table2
Where to_char (upd_date, 'yyyy/mm') =
To_char (to_date (to_char (sysdate, 'yyyy/mm') | '/01', 'yyyy/MM/dd')-1, 'yyyy/mm ')) y,
Where X. num = Y. Num (+)
And X. inbound_qty + nvl (Y. stock_onhand, 0) X. stock_onhand) B
Where a. num = B. Num
Note :--
SQL:
Select * From studentinfo where not exists (select * from student where studentinfo. id = student. ID) and Department name = '"& strdepartmentname &"' and major name = '"& strinclusionname &" 'order by gender, student source, total score of the College Entrance Examination
Note:
Go to the telephone fee statistics of each unit from the database for one year (two sources of the fixed telephone fee congratulation fertilizer List)
SQL:
Select a. userper, A. Tel, A. standalone, to_char (A. telfeedate, 'yyyy') as telyear,
Sum (decode (to_char (A. telfeedate, 'mm'), '01', A. factration) as Jan,
Sum (decode (to_char (A. telfeedate, 'mm'), '02', A. factration) as Fri,
Sum (decode (to_char (A. telfeedate, 'mm'), '03', A. factration) as MAR,
Sum (decode (to_char (A. telfeedate, 'mm'), '04 ', A. factration) as Apr,
Sum (decode (to_char (A. telfeedate, 'mm'), '05 ', A. factration) as may,
Sum (decode (to_char (A. telfeedate, 'mm'), '06', A. factration) as Jue,
Sum (decode (to_char (A. telfeedate, 'mm'), '07 ', A. factration) as Jul,
Sum (decode (to_char (A. telfeedate, 'mm'), '08 ', A. factration) as Agu,
Sum (decode (to_char (A. telfeedate, 'mm'), '09', A. factration) as SEP,
Sum (decode (to_char (A. telfeedate, 'mm'), '10', A. factration) as Oct,
Sum (decode (to_char (A. telfeedate, 'mm'), '11', A. factration) as Nov,
Sum (decode (to_char (A. telfeedate, 'mm'), '12', A. factration) as Dec
From (select a. userper, A. Tel, A. standalone, B. telfeedate, B. factration
From telfeestand A, telstmb
Where a. Tel = B. telfax)
Group by A. userper, A. Tel, A. standalone, to_char (A. telfeedate, 'yyyy ')
Notes: Four-table join query problems:
SQL: Select * from a left inner join B on. A = B. B right inner join C on. A = C. C inner join D on. A = D. d Where .....
Note: Obtain the smallest unused ID number in the table.
SQL:
Select (case when exists (select * From handle B where B. handleid = 1) Then min (handleid) + 1 else 1 end) as handleid
From handle
Where not handleid in (select a. handleid-1 from handle)
: I am in MSSQLA table is created, but some records are repeated for various reasons.
: The records are exactly the same.
: Now I want to delete all the duplicates and only keep the first record.
: I see Oracle introduction in the database,
Select distinct * into # table_name from table_name
Delete from table_name
Select * into table_name from # table_name
Drop table # table_name
This is related to the "select into" option, which can be in the database attribute
In the dialog box, check this item, or execute it in query analyzer.
Execute sp_dboption 'db _ name', 'select into', 'true'
Enable. The default value is disabled.