SQL Classic Statement _mssql

Copy Code code as follows:

drop table datasheet name (permanently deletes a data table)

Delete Record: Delete from table name where condition (rqjs_id=***)

Update all records: Update table name set field name = value

Update by Condition: Update table name set field name = value where Condition (rqjs_id=***)

(5) The establishment and deletion of the data table:
CREATE Table datasheet Name (field 1 type 1 (length), Field 2 type 2 (length) ...)
Example: CREATE TABLE tab01 (name varchar, datetime default Now ())
drop table datasheet name (permanently deletes a data table)


--Statement function
--Data manipulation
SELECT-Retrieve rows and columns of data from a database table
INSERT-Add new data rows to a database table
Delete-Deletes rows of data from a database table
Update--updating data in a database table
--Data definition
CREATE table--Creating a database table
DROP table--Deletes a table from the database
ALTER TABLE--modifying database table structure
Create View--Creates a view
Drop view-Removing views from the database
Create index--creates an index for a database table
Drop INDEX--deletes an index from the database
Create PROCEDURE--Creating a stored procedure
Drop PROCEDURE--removing stored procedures from the database
Create TRIGGER--creating a trigger
Drop TRIGGER--removing triggers from the database
CREATE schema--Adding a new schema to the database
Drop schema--Deletes a schema from the database
Create domain--creating a data domain
Alter domain--changing field definitions
Drop domain--deletes a field from the database
--Data control
Grant-Granting User access
Deny--Deny user access
REVOKE--Unlock user access rights
--Transaction control
COMMIT--End current transaction
ROLLBACK--Abort current transaction
SET TRANSACTION--Defining the current transaction data access characteristics
--Programmed SQL
DECLARE--Set cursors for queries
Explan--Describes a data access plan for a query
Open--Retrieving query results opens a cursor
FETCH--Retrieves a row of query results
Close--Closing cursors
PREPARE-Preparing SQL statements for dynamic execution
Execute-Execute SQL statements Dynamically
DESCRIBE--Describe a prepared query
---local variables
DECLARE @id char (10)
--set @id = ' 10010001 '
Select @id = ' 10010001 '
---global variables
---must begin with @@

--if ELSE
DECLARE @x int @y int @z int
Select @x = 1 @y = 2 @z=3
If @x > @y
print ' x > y '--printing 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--Prints the value of the variable x
While @y < 3
Begin
Select @c = 100*@x + @y
Print @c--printing the value of variable C
Select @y = @y + 1
End
Select @x = @x + 1
Select @y = 1
End

--waitfor
--An example waits 1 hours 2 minutes 3 seconds before executing the SELECT statement
WAITFOR DELAY ' 01:02:03 '
SELECT * FROM Employee
--for example, wait until 11 o'clock at 8 minutes before executing the SELECT statement
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]% '---------([] Specify range of values)
StockName like ' [^f-m]% '---------(^ exclusion specified range)
---------only use wildcard characters in WHERE clauses that use the LIKE keyword)
or Stockpath = ' Stock_path '
or Stocknumber < 1000
and Stockindex = 24
Not stock*** = ' mans '
Stocknumber between 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 you can ensure that the inner select returns only the values of one row,
---------Otherwise, you should use an in qualifier in the outer WHERE clause
SELECT DISTINCT COLUMN_NAME form table_name---------
DISTINCT specifies that unique column values are retrieved and not duplicated
Select Stocknumber, "Stocknumber +" = Stocknumber + from
table_name
Select StockName, "stocknumber" = count (*) from table_name
GROUP BY StockName
---------GROUP by groups the table by row, specifying that the column has the same value
Having count (*) = 2---------have the specified group selected

SELECT *
From Table1, table2
where Table1.id *= table2.id--------
Left outer joins, some in the Table1 and table2 are not represented in null
Table1.id =* table2.id--------Right outer connection

Select StockName from table1
Union [ALL]-----Union merge query result set, all-keep 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 still preserve the integrity of the table
DROP TABLE table_name---------------completely delete table

ALTER table***---Modify database table structure

ALTER TABLE Database.owner.table_name add column_name char (2)
Null.....
SP_HELP table_name----Display table has features
CREATE TABLE table_name (name char), age smallint, lname
varchar (30))
INSERT INTO table_name SELECT ...-----implement method to delete a column (Create a new table)
ALTER TABLE table_name DROP CONSTRAINT Stockname_default----
Delete a stockname default constraint

function (///* Common functions * *) * * *

----Statistical function----
AVG--averaging
Count--Number of statistics
Max--Find the maximum value
Min--Find the minimum value
Sum--sum

--avg
Use Pangu
Select AVG (e_wage) as Dept_avgwage
From employee
GROUP BY dept_id

--max
--the highest-paid employee name
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 an expression

--STDEVP ()
--STDEVP () function returns the overall standard deviation

--var ()
--var () function returns the statistical variance of all values in an expression

--varp ()
--varp () function returns the total variance number

----Arithmetic functions----

/*** Trigonometric ***/
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 Functions ***/
ASIN (float_expression)--Returns the angle that the sine is the float value in radians
ACOS (float_expression)--Returns the angle in radians that the cosine is the float value
Atan (float_expression)--Returns the angle in radians that the tangent is a float value
ATAN2 (Float_expression1,float_expression2)
--Returns the tangent is the angle of the float_expression1/float_expres-sion2 in radians
DEGREES (numeric_expression)
--converting radians to angles returns the same data type as the expression can be
--integer/money/real/float type
RADIANS (numeric_expression)--Converts the angle to radians returns the same data type as the expression can be
--integer/money/real/float type
EXP (float_expression)--Returns the exponential value of an expression
LOG (float_expression)--Returns the natural value of an expression
LOG10 (float_expression)--Returns the 10-based pair value of an expression
SQRT (float_expression)--Returns the square root of an expression
/*** Approximate value function ***/
CEILING (numeric_expression)--the smallest integer that returns a >= expression returns the same data type as an expression
--integer/money/real/float type
FLOOR (numeric_expression)--the smallest integer that returns a <= expression returns the same data type as an expression
--integer/money/real/float type
ROUND (numeric_expression)--Return to Integer_expression
Data returned for rounding values of precision
--the type is the same as an expression and can be integer/money/real/float type
ABS (numeric_expression)--Returns the absolute value of an expression that returns the same type of data as an expression
--integer/money/real/float type
SIGN (numeric_expression)--The positive sign of the test parameter returns the data type returned by a 00-value 1 positive or-1 negative number
--the same as an expression can be integer/money/real/float type
Pi ()--The return value is pi that is 3.1415926535897936
RAND ([integer_expression])--seed with an optional [integer_expression] is worth 0-1
Random floating-point numbers between

----String function----
ASCII ()--function returns the ASCII code value of the leftmost character of a character expression
CHAR ()--function is used to convert ASCII code to characters
--If you do not enter the ASCII code value between 0 ~ 255 The CHAR function returns a null value
LOWER ()--function converts a string to lowercase
UPPER ()--function converts a string to uppercase
STR ()--function converts numeric data to character data
LTRIM ()--function removes the space of the string header
RTRIM ()--function to remove the trailing space of the string
Left (), right (), SUBSTRING ()--function returns a partial string
CHARINDEX (), PATINDEX ()--function returns the starting position of a specified substring in a string
SOUNDEX ()--function returns a four-bit character code
The--soundex function can be used to find strings with similar sounds but the SOUNDEX function returns only 0 values for both numbers and characters
Difference ()--function returns the difference of the value of the two-character expression returned by the SOUNDEX function
--2 Soundex function returns the first character of the value is different
--12 Soundex function returns the same first character of the value
--22 Soundex function returns the same 12th character of the value
--32 Soundex function returns the same 123th character of the value
--42 Soundex function return values are exactly the same

QuoteName ()--function returns a string enclosed by a particular character
/*select QuoteName (' abc ', ' {') QuoteName (' abc ')
The results of the operation are as follows
----------------------------------{
{ABC} [abc]*/

REPLICATE ()--function returns a string that repeats character_expression a specified number of times
/*select replicate (' abc ', 3) replicate (' abc ',-2)
The results of the operation are as follows
----------- -----------
ABCABCABC null*/

REVERSE ()--the function reverses the character order of the specified string
Replace ()--function returns a string that is replaced with a specified substring
/*select replace (' abc123g ', ' 123 ', ' Def ')
The results of the operation are as follows
----------- -----------
abcdefg*/

Space ()--the function returns a blank string with a specified length
STUFF ()--the function replaces a substring with another substring at the specified position length

----data type conversion function----
The CAST () function syntax 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 (), GETDATE ())
The results of the operation are as follows
------------------------------ ------------
199 15 2000.

----Date function----
Day ()--function returns the date value in Date_expression
MONTH ()--function returns the month value in Date_expression
Year ()--function returns the value of years in Date_expression
DATEADD (<datepart>,<number>,<date>)
--function returns a date with the specified date plus the specified extra date interval number
DATEDIFF (<datepart>,<number>,<date>)
--function returns the difference in datepart between two specified dates
Datename (<datepart> <date>)--function returns the specified part of a date as a string
DATEPART (<datepart> <date>)--function returns the specified part of a date as an integer value
GETDATE ()--function returns the current date and time of the system in datetime default format

----System Functions----
App_name ()--function returns the name of the currently executing application
COALESCE ()--function returns the value of the first non-null expression in many expressions
Col_length (< ' table_name ', < ' column_name ' >)--function returns the length value of the specified field in the table
Col_name (&LT;TABLE_ID&GT; <column_id>)--function returns the name of the specified field in the table, which is the column name
Datalength ()--function returns the actual length of data in a data expression
DB_ID ([' database_name '])--function returns the number of the database
Db_name (database_id)--function returns the name of the database
HOST_ID ()--function returns the name of the server-side computer
HOST_NAME ()--function returns the name of the server-side computer
IDENTITY (<data_type>[, Seed increment]) [as column_name])
The--identity () function is used to insert an IDENTITY column column into a new table only in the SELECT INTO statement
/*select identity (int, 1, 1) as column_name
Into newtable
From oldtable*/
ISDATE ()--function to determine whether the given expression is a reasonable date
ISNULL (&LT;CHECK_EXPRESSION&GT; <replacement_value>)--the function converts null in an expression
Value is replaced with the specified value
IsNumeric ()--function to determine whether the given expression is a reasonable value
NEWID ()--function returns a value of uniqueidentifier type
Nullif (&LT;EXPRESSION1&GT; <expression2>)
The--NULLIF function returns NULL when expression1 is equal to Expression2
Returns the value of a expression1 if the value is not equal

Reserved words in SQL

Action Add aggregate All
Alter after and AS
ASC AVG Avg_row_length Auto_increment
Between bigint bit binary
BLOB bool both by
Cascade Case Char Character
Change Check checksum column
Columns Comment Constraint create
Cross Current_date Current_time Current_timestamp
Data database Databases Date
DateTime Day Day_hour Day_minute
Day_second DayOfMonth DayOfWeek DayOfYear
Dec decimal Default delayed
Delay_key_write Delete desc describe
Distinct Distinctrow Double drop
End Else Escape escaped
Enclosed enum explain exists
Fields file a float
FLOAT4 FLOAT8 Flush Foreign
From as full function
Global Grant Grants Group
Having heap high_priority hour
Hour_minute Hour_second hosts identified
Ignore in index infile
Inner Insert insert_id int
Integer interval int1 int2
Int3 int4 int8 into
If is ISAM join
Key keys Kill last_insert_id
Leading left length like
Lines Limit load Local
Lock logs Long Longblob
Longtext low_priority Max Max_Rows
Match Mediumblob Mediumtext Mediumint
Middleint min_rows minute Minute_second
Modify Month MonthName MyISAM
Natural Numeric No
Null on Optimize option
Optionally or order outer
outfile Pack_keys Partial password
Precision Primary Procedure Process
Processlist Privileges Read Real
References Reload RegExp Rename
Replace restrict returns revoke
Rlike Row Rows Second
Select Set Show shutdown
smallint Soname Sql_big_tables sql_big_selects
Sql_low_priority_updates Sql_log_off Sql_log_update
Sql_select_limit
Sql_small_result Sql_big_result sql_warnings Straight_join
Starting Status String table
Tables temporary terminated text
Then time timestamp Tinyblob
Tinytext tinyint trailing to
Type using unique
Unlock unsigned update usage
Values varchar variables varying
varbinary with Write when
Where year Year_month Zerofill