SQL statement Overview (1) _ basic knowledge

-- 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
-- Programmatic SQL
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)
-- 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 <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 range of values)
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 stocksex = '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 (/* common functions */)***

---- Statistical functions ----
AVG -- average value
Count -- count
Max -- calculate the maximum value
Min -- Minimum value
Sum -- sum

-- AVG
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