Some common knowledge of databases are important operation statements.
Some common knowledge of databases are important operation statements.
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)
--------- Child query http://www.acnow.net/oYIpsxSPAIC
--------- 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 */)***
Author: 60.55.32 .*
Reply to this speech
3
Reply: FAQs and solutions for beginners of programming _ ASP _ Asp
1 SQL statement Collection
---- 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 = C
(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 C.
-- 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
Programming _ ASP _ Asp FAQs for beginners and solutions
-- 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 ()( 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:
------------------------------------------
199 Jan 15 2000
---- 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
======================================================= Please! You should also consider readability when posting a post.
How to keep the data retrieved from the database in the original format?
Many of you have mentioned the answer here. First define a function:
<%
Function HTMLEncode (fString)
FString = replace (fString, >,>)
FString = replace (fString, <, <)
FString = Replace (fString, CHR (32 ),)
FString = Replace (fString, CHR (13 ),)
FString = Replace (fString, CHR (10) & CHR (10 ),
)
FString = Replace (fString, CHR (10 ),
)
HTMLEncode = fString
End function
%>
Call <% = HTMLEncode (strstring) %>
