[Recommendation] common SQL statements

Last Update:2018-12-07 Source: Internet

Author: User

Tags natural logarithm create domain

Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

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

--ProgramSQL

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 SQL statements for dynamic execution

Execute -- dynamically Execute SQL statements

Describe -- describe the prepared Query

--- Local variables

Declare @ ID char (10)

-- Two methods of value assignment

Set @ ID = ''10010001''

Select @ ID = ''10010001''

--- 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 <3
Begin
Print @ X -- print the value of variable X
While @ Y <3
Begin
Select @ C = 100 * @ x + @ Y
Print @ C -- print the value of variable C
Select @ Y = @ Y + 1
End
Select @ x = @ x + 1
Select @ Y = 1
End

-- Waitfor
-- For example, the SELECT statement is executed after 1 hour, 2 minutes, and 3 seconds.

Waitfor delay '01: 02: 03'
Select * from employee

-- For example, the SELECT statement will not be executed until PM.

Waitfor time '23: 08: 00'
Select * from employee

* ** Select ***

Select * (column name) from table_name (Table Name) Where column_name operator Value
Ex: (host)
Select * From stock_information where stockid = STR (NID)
Stockname = ''str _ name''
Stockname like ''% find this %''
Stockname like ''[A-Za-Z] %'' --------- ([] specifies the value range)
Stockname like ''[^ F-M] %'' --------- (^ exclude specified range)
--------- Only wildcard characters can be used in the WHERE clause that uses the like keyword)
Or stockpath = ''stock _ path''
Or stocknumber: <1000
And stockindex = 24
Not stock *** = ''man''
Stocknumber between 20 and 100
Stocknumber in (10, 20, 30)
Order by stockid DESC (ASC) --------- sort, desc-descending, ASC-ascending
Order by 1, 2 --------- by column number
Stockname = (select stockname from stock_information where stockid = 4)

--------- Subquery

--------- Unless the inner select clause can only return the value of one row,

--------- Otherwise, an in qualifier should be used in the outer WHERE clause.

Select distinct column_name form table_name --------- distinct specifies the unique column value to be retrieved, not repeated
Select stocknumber, stocknumber + 10 = stocknumber + 10 from table_name
Select stockname, stocknumber = count (*) from table_name group by stockname

--------- Group by groups the table by row. The specified column has the same value.

Having count (*) = 2 --------- having: select the specified group.
Select *
From Table1, Table2
Where table1.id * = table2.id -------- left external connection, which exists in Table1 but not in Table2.
Table1.id = * table2.id -------- right external connection
Select stockname from Table1
Union [all] ----- Union merges query result sets, all-retained duplicate rows
Select stockname from Table2

* ** Insert ***
Insert into table_name (stock_name, stock_number) value (XXX, xxxx)
Value (select stockname, stocknumber from stock_table2) --- value is a SELECT statement

* ** Update ***

Update table_name set stockname = xxx [where stockid = 3]
Stockname = default
Stockname = NULL
Stocknumber = stockname + 4

* ** Delete ***

Delete from table_name where stockid = 3
Truncate table_name ----------- delete all rows in the table and maintain table integrity
Drop table table_name --------------- delete a table completely

* ** Alter table ** --- modify the database table structure

Alter table database. Owner. table_name add column_name char (2) null .....
Sp_help table_name ---- display existing features of a table
Create Table table_name (name char (20), age smallint, lname varchar (30 ))
Insert into table_name select ...... ----- to delete a column (create a new table)
Alter table table_name drop constraint stockname_default ---- Delete the default constraint of stockname

* ** Function ()***

---- Statistical functions ----

* ** G -- calculate the average value

Count -- count

Max -- calculate the maximum value

Min -- Minimum value

Sum -- sum

-- *** G

Use pangu
Select AVG (e_wage) as dept_avgwage
From employee
Group by dept_id

-- Max
-- Name of the employee with the highest salary

Use pangu
Select e_name
From employee
Where e_wage =
(Select max (e_wage)
From employee)

-- STDev ()

-- STDev () function returns the standard deviation of all data in the expression.

-- Stdevp ()

-- The stdevp () function returns the population standard deviation.

-- VAR ()

-- The VaR () function returns the statistical variation of all values in the expression.

-- Varp ()

-- Varp () function returns the total number of variations

---- Arithmetic functions ----

Sin (float_expression) -- returns the sine of the angle in radians

Cos (float_expression) -- returns the cosine of the angle in radians

Tan (float_expression) -- returns the tangent of the angle in radians

Cot (float_expression) -- returns the cotangent of the angle in radians

Asin (float_expression) -- returns the angle in radians where the sine is the float value.

ACOs (float_expression) -- returns the angle in radians where the cosine is the float value.

Atan (float_expression) -- returns the angle in radians of the float value.

Atan2 (float_expression1, float_expression2)

-- Returns the radians of float_expression1/float_expres-sion2.

Degrees (numeric_expression)

-- Converts radians to degrees and returns data of the same type as the expressions.