[SQL] SQL statement Daquan

MySQL creates databases that support UTF-8 Encoding

GBK: Create Database 'test1' default Character Set GBK collate gbk_chinese_ci;
UTF-8: Create Database 'test2' default Character Set utf8 collate utf8_general_ci;

Http://bbs.csdn.net/topics/360210527

-- 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 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 (/* 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 ()
-- VAR () function returns the statistical variation of all values in the expression.
-- Varp ()
-- Varp () function returns the total number of variations
---- Arithmetic functions ----
/*** Trigonometric function ***/
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
/*** Inverse trigonometric function ***/
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.
-- Integer/money/real/Float Type
Radians (numeric_expression) -- converts degrees to radians and returns the same data type as the expression.
-- Integer/money/real/Float Type
Exp (float_expression) -- returns the exponent value of the expression.
Log (float_expression) -- returns the natural logarithm of the expression.
Log10 (float_expression) -- returns the base-10 logarithm of the expression.
SQRT (float_expression) -- returns the square root of the expression
/***** Approximate function ***/
Ceiling (numeric_expression) -- Return> = the minimum integer of the expression. The returned data type is the same as that of the expression.
-- Integer/money/real/Float Type
Floor (numeric_expression) -- returns the minimum integer of the <= expression. The returned data type is the same as that of the expression.
-- Integer/money/real/Float Type
Round (numeric_expression) -- returns the data returned from a rounding value with integer_expression as the precision.
-- The same type and expression can be integer/money/real/Float Type
ABS (numeric_expression) -- returns the absolute value of the expression. The returned data type is the same as that of the expression.
-- Integer/money/real/Float Type
Sign (numeric_expression) -- the positive and negative values of the test parameter return the data type returned by 0, zero, 1, positive, or-1 negative.
-- Same as the expression, it can be of the integer, money, real, or float type.
Pi () -- the return value is π, that is, 3.1415926535897936
Rand ([integer_expression]) -- use the optional [integer_expression] As the seed value to obtain a random floating point number between 0 and 1.
---- String function ----
ASCII () -- returns the ASCII value of the leftmost character of a character expression.
Char () -- function used to convert ASCII code to character
-- If no value is entered ~ The Char function returns a null value for the ASCII value between 255.
Lower () -- function converts all strings to lowercase letters
Upper () -- function converts all strings to uppercase
STR () -- function converts numeric data to numeric data
Ltrim () -- function removes spaces in the string Header
Rtrim () -- function removes spaces at the end of the string
Left (), right (), substring () -- The function returns some strings.
Charindex (), patindex () -- The function returns the starting position of a specified substring in the string.
Soundex () -- The function returns a four-digit escape code.
-- The soundex function can be used to find strings with similar sound. However, the soundex function returns only 0 values for numbers and Chinese characters.
Difference () -- the difference between the two character expressions returned by the soundex Function
-- 0 the first character returned by the two soundex functions is different
-- 1 the first character returned by the two soundex functions is the same
-- 2 two soundex functions return the same first and second characters.
-- 3 two soundex functions return the same first, second, and third characters.
-- 4 two soundex functions return identical values
Quotename () -- The function returns a string enclosed by a specific character.
/* Select quotename ('abc', '{') quotename ('abc ')
The running result is as follows:
----------------------------------{
{ABC} [ABC] */
Replicate () -- The function returns a string that repeats character_expression for a specified number of times.
/* Select replicate ('abc', 3) replicate ('abc',-2)
The running result is as follows:
----------------------
Abcabcabc null */
Reverse () -- The function reverses the character arrangement order of the specified string
Replace () -- returns the string with the specified substring replaced by the function.
/* Select Replace ('abc123g ', '123', 'def ')
The running result is as follows:
----------------------
Abcdefg */
Space () -- The function returns a blank string with a specified length.
Stuff () -- The function replaces a substring of the specified position length with another substring.
---- Data type conversion function ----
The syntax of the cast () function is as follows:
Cast () (<expression> as <data _ type> [length])
The convert () function syntax is as follows:
Convert () (<data _ type> [length], <expression> [, style])
Select cast (100 + 99 as char) convert (varchar (12), getdate ())