Mysql database Chinese and English table _ MySQL

Mysql database Chinese-English comparison table bitsCN.com

-- 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
Whenjob_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 @ yint @ 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.
Waitfordelay '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'
Orstocknumber: <1000
And stockindex = 24
Not stocksex = 'man'
Stocknumber between 20 and 100
Stocknumber in (10, 20, 30)
Order bystockid 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 (*) fromtable_name group by stockname
--------- Group by groups the table by row. the specified column has the same value.
Havingcount (*) = 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 fromStock_table2) --- value is a select statement
* ** Update ***
Update table_name setStockname = "xxx" [where Stockid = 3]
Stockname = default
Stockname = null
Stocknumber = Stockname + 4
* ** Delete ***
Delete from table_namewhere Stockid = 3
Truncate table_name ----------- delete all rows in the table and maintain table integrity
Droptable 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 constraintStockname_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
---- 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:
----------------------
AbcabcabcNULL */
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 ()( AS [Length])
The CONVERT () function syntax is as follows:
CONVERT ()( [Length], [, Style])
Select cast (100 + 99 as char) convert (varchar (12), getdate ())
The running result is as follows:
---- Date function ----
DAY () -- the function returns the date value in date_expression.
MONTH () -- the function returns the MONTH value in date_expression.
YEAR () -- returns the YEAR value in date_expression.
DATEADD ( , , )
-- The function returns the new date generated by the specified date plus the specified additional date interval number.
DATEDIFF ( , , )
-- The function returns the datepart differences between two specified dates.
DATENAME ( , ) -- The function returns the specified part of the date in the form of a string.
DATEPART ( , ) -- The function returns the specified part of the date in the form of an integer.
GETDATE () -- the function returns the current date and time of the system in the default format of DATETIME.
---- System functions ----
APP_NAME () -- function returns the name of the currently executed application
COALESCE () -- function returns the value of the first non-NULL expression among multiple expressions.
COL_LENGTH (<'Table _ name' >,< 'column _ name'>) -- returns the length value of the specified field in the table.
COL_NAME ( , ) -- The name of the specified field in the function return table 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 ( , ) -- Replace 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.

Address: http://superman.php100.com/apps-htm-q-diary-a-detail-did-7151.html

BitsCN.com