SQL commands-Comparison between Chinese and English
-- 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