Common SQL Syntax Functions

Common SQL Syntax Functions

Code:
Functions of Transact SQL statements
-- 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 = '; 10010001 ';
Select @ id = '; 10010001 ';
--- 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 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
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 value range)
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)
--------- 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 the query result set, and all-retains 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)
------- The 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 Database table Structure
Alter table database. owner. table_name add column_name char (2) null ..
Sp_help table_name -------- display existing table features
Create table table_name (name char (20), age smallint, lname varchar (30 ))
Insert into table_name select -------- delete a column (create a new table)
Alter table table_name drop constraint Stockname_default
--------- Delete the default constraint of Stockname

Common functions)
Conversion functions
Convert (data type, value, Format)
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 the same data type as the expression.
------ INTEGER/MONEY/REAL/FLOAT Type
RADIANS (numeric_expression)
------ Convert the angle to radians and return 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 value 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)
------- Return 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)
------- Return the data returned by rounding the value with integer_expression as the precision
------- The same type and expression can be INTEGER/MONEY/REAL/FLOAT Type
ABS (numeric_expression)
------- Return the absolute value of an expression. The returned data type is the same as that of an expression.
------- INTEGER/MONEY/REAL/FLOAT Type
SIGN (numeric_expression)
------- Positive and negative values of the test parameter return the data type returned by 0 0 0 value 1 positive number or-1 negative number
------- The same as the expression can be of the INTEGER/MONEY/REAL/FLOAT Type
PI () ------- the returned 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 Functions
ASCII () ------ the function returns the ASCII value of the leftmost character in the character expression
CHAR () ------ function is 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 search for sound-similar strings. However, the SOUNDEX function returns only 0 values for numbers and Chinese characters.
DIFFERENCE () ------ DIFFERENCE in the two character expressions returned by the SOUNDEX Function
------ 0 the first character returned by 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 uses another substring to replace the substring with the specified position length of the string.
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 Functions
DAY () ------ function returns the date value in date_expression
MONTH () ------ function returns the MONTH value in date_expression
YEAR () ------ function 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.
The DATENAME (, ------ function returns the specified part of the date in the form of a string
DATEPART (, ------ 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 () ----- the function returns the value of the first non-NULL expression in many expressions.
COL_LENGTH (<'; table_name'; >,< '; column_name';> ---- return the length value of the specified field in the table
COL_NAME (, ---- the name of the specified field in the return table is the column name.
DATALENGTH () ----- the actual length of the data returned by the function
DB_ID (['; database_name';]) ------ the number of the database returned by the function
DB_NAME (database_id) ------ the name of the database returned by the function
HOST_ID () ----- the function returns the name of the server computer.
HOST_NAME () ----- the function 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 () ---- function determines whether the given expression is a reasonable date
ISNULL (, -- the function replaces the NULL value in the expression with the specified value
ISNUMERIC () ---- the function determines whether the given expression is a reasonable value.
NEWID () ---- function returns a value of the UNIQUEIDENTIFIER type.
NULLIF (,
---- The NULLIF function returns the value of xpression1 if expression1 is equal to expression2 and NULL if not equal.

[Database development-classic]
1. sort by strokes of the Last Name:
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as
2. database encryption:
Select encrypt ('; original password ';)
Select pwdencrypt ('; original password ';)
Select pwdcompare ('; original password';,'; encrypted password';) = 1 -- same; otherwise, different encrypt ('; original password ';)
Select pwdencrypt ('; original password ';)
Select pwdcompare ('; original password';,'; encrypted password';) = 1 -- same; otherwise, different
3. Retrieve the fields in the table:
Declare @ list varchar (1000), @ SQL nvarchar (1000)
Select @ list = @ list + ';,'; + B. name from sysobjects a, syscolumns B where. id = B. id and. name = '; Table ';
Set @ SQL = '; select'; + right (@ list, len (@ list)-1) +'; from Table ';
Exec (@ SQL)
4. View hard disk partitions:
EXEC master .. xp_fixeddrives
5. Compare whether tables A and B are equal:
If (select checksum_agg (binary_checksum (*) from)
=
(Select checksum_sum( binary_checksum (*) from B)
Print '; equal ';
Else
Print '; not equal ';
6. Kill all event Inspector processes:
DECLARE hcforeach cursor global for select '; kill'; + RTRIM (spid) FROM master. dbo. sysprocesses
WHERE program_name IN ('; SQL profiler';, n'; SQL event profiler ';)
EXEC sp_msforeach_worker ';? ';
7. Record Search:
Starting with N records
Select Top N * From table
-------------------------------
N to M records (primary index ID required)
Select Top M-N * From table Where ID in (Select Top m id From Table) Order by ID Desc
----------------------------------
N to the end record
Select Top N * From Table Order by ID Desc
8. How to modify the Database Name:
Sp_renamedb '; old_name';, '; new_name ';
9: Get all user tables in the current database
Select Name from sysobjects where xtype = '; U'; and status> = 0
10: Get all fields of a table
Select name from syscolumns where id = object_id ('; Table name ';)
11. View views, stored procedures, and functions related to a table
Select a. * from sysobjects a, syscomments B where a. id = B. id and B. text like '; % table name % ';
12: view all stored procedures in the current database
Select name as stored procedure name from sysobjects where xtype = '';
13: Query all databases created by the user
Select * from master .. sysdatabases D where sid not in (select sid from master .. syslogins where name = '; Sa ';)
Or
Select dbid, name AS DB_NAME from master .. sysdatabases where sid <> 0x01
14: query the fields and Data Types of a table
Select column_name, data_type from information_schema.columns
Where table_name = '; Table name ';
[N]. [title]:
Select * From TableName Order By CustomerName
[N]. [title]:
Select * From TableName Order By CustomerName

1. view the database version
Select @ version
2. view the operating system parameters of the machine where the database is located
Exec master .. xp_msver
3. view database startup parameters
Sp_configure
4. view the database startup time
Select convert (varchar (30), login_time, 120) from master .. sysprocesses where spid = 1
View database server name and Instance name
Print '; Server Name ......:'; + convert (varchar (30), @ SERVERNAME)
Print '; Instance ......:'; + convert (varchar (30), @ SERVICENAME)
5. View All Database names and sizes
Sp_helpdb
SQL statement used to rename a database
Sp_renamedb '; old_dbname';, '; new_dbname ';
6. View logon information of all database users
Sp_helplogins
View the role information of all database users
Sp_helpsrvrolemember
Fixed the fix_orphan_user script or LoneUser process that can be used to isolate users during server migration.
Change the user owner of a Data Object
Sp_changeobjectowner [@ objectname =] '; object';, [@ newowner =]'; owner ';
Note: changing any part of the object name may corrupt the script and stored procedure.
You can use the add_login_to_aserver script to back up the database user logon information on a server.
7. view linked servers
Sp_helplinkedsrvlogin
View remote database user logon information
Sp_helpremotelogin
8. view the size of a data object in a database
Sp_spaceused @ objname
You can also use the sp_toptables process to view the maximum N tables (50 by default ).
View the index information of a data object in a database
Sp_helpindex @ objname
You can also use the SP_NChelpindex process to view more detailed indexes.
SP_NChelpindex @ objname
Clustered indexes sort records in physical order and occupy less space.
We recommend that you use non-clustered indexes and constraints for tables with frequent key value DML operations. The fillfactor parameter uses the default value.
View the constraints of a data object in a database
Sp_helpconstraint @ objname
9. view all stored procedures and functions in the database
Use @ database_name
Sp_stored_procedures
View the source code of stored procedures and functions
Sp_helptext '; @ procedure_name ';
View the Data Object Name containing a string @ str
Select distinct object_name (id) from syscomments where text like '; % @ str % ';
Create an encrypted stored procedure or function WITH the with encryption parameter before the
You can use sp_decrypt to decrypt encrypted stored procedures and functions.
10. View user and process information in the database
Sp_who
View information about active users and processes in the SQL Server database
Sp_who '; activity ';
View the locks in the SQL Server database
Sp_lock
Process No. 1--50 is used internally in the SQL Server system. If the process no. is greater than 50, it is the user's connection process.
Spid is the process number, dbid is the database number, and objid is the data object number
View the SQL statement being executed by the Process
Dbcc inputbuffer ()
We recommend that you use the improved sp_who3 process to directly view the SQL statements run by the process.
Sp_who3
Check the process of using sp_who_lock for deadlocks
Sp_who_lock
11. Methods for shrinking Database Log Files
Shrink database logs in simple recovery mode. The size unit of @ database_name_log after the contraction is M.
Backup log @ database_name with no_log
Dbcc shrinkfile (@ database_name_log, 5)
12. How to analyze SQL Server SQL statements:
Set statistics time {on | off}
Set statistics io {on | off}
Display query execution plan in graphical mode
In the query analyzer-> query-> display estimated evaluation plan (D)-Ctrl-L or click the graph in the toolbar
Display query execution plan in text mode
Set showplan_all {on | off}
Set showplan_text {on | off}
Set statistics profile {on | off}
13. When an inconsistency error occurs, the NT Event Viewer displays error 3624. How to fix the database
Comment out the table referenced in the application with an inconsistent error, recover the table on the backup or other machines, and then perform the repair operation.
Alter database [@ error_database_name] set single_user
Fix inconsistent tables
Dbcc checktable ('; @ error_table_name';, repair_allow_data_loss)
Or, unfortunately, you can fix the name of a small database with an inconsistent error.
Dbcc checkdb ('; @ error_database_name';, repair_allow_data_loss)
Alter database [@ error_database_name] set multi_user
CHECKDB has three parameters:
Repair_allow_data_loss includes allocating and unassigning rows and pages to correct the allocation error, structure row, or page error,
And delete corrupted text objects. These repairs may cause data loss.
The repair operation can be completed under the user transaction to allow the user to roll back the changes.
If the rollback is fixed, the database will still contain errors and the backup should be restored.
If an error fix is missing due to the severity of the severity level provided, it will omit any fix that depends on the fix.
After the restoration is completed, back up the database.
Repair_fast Performs small and time-consuming repair operations, such as fixing additional keys in non-clustered indexes.
These repairs can be completed quickly without the risk of data loss.
Repair_rebuild performs all repairs completed by repair_fast, including repairs that require a long period of time (such as re-indexing ).
There is no risk of data loss when performing these repairs.
Code:
========================================================== ======================================
Random fetch of different numbers
==========================================================
/*
Function: Randomly obtain four distinct numbers from (,).
DESIGN: over
Time: GetDate ()
*/
Declare @ x int
Create table # temp (dd int)
Set @ x = 1
While @ x <= 10
Begin
Insert into # temp select @ x
Set @ x = @ x + 1
End
Select * FROM # temp
Select top 4 * from # temp order by newid ()
Drop table # temp

========================================================== ====================
NOT EXISTS
========================================================== ====================

Create table # Temp (student ID int, name nvarchar (20), subject nvarchar (30), score float)
Insert into # Temp SELECT 1, 'zhang san', 'China', 80
Union select 2, 'Li si', 'China', 83
Union select 3, 'wang wu', 'English ', 99
Union select 4, 'lily', 'mat', 88
Union select 5, 'zhang san', 'English ', 66
Union select 6, 'wang wu', 'mat', 87
Union select 7, 'Li si', 'English ', 69
Union select 8, 'zhang san', 'mat', 63
Union select 9, 'wang 5', 'China', 77

SELECT * FROM # Temp
Select a. * FROM # Temp as a where not exists (SELECT 1 FROM # Temp as B where B. Subject = A. Subject and B. score> A. Score)
Drop table # Temp

========================================================== ======================================
Delete user table data of the current database
========================================================== ======================================
/*
Function: Delete the table data of the current database.
Design:
Time: 2006-05
Note: The script execution risk is★★★★★, Do not execute at will
*/

DECLARE @ tablename varchar (50)
DECLARE @ truncatesql varchar (255)
DECLARE TrCun_Cursor CURSOR
Select [name] from sysobjects where type = 'U'
OPEN TrCun_Cursor
FETCH TrCun_Cursor INTO @ tablename
WHILE (@ fetch_status = 0)
BEGIN
SET @ truncatesql = 'truncate table' + @ tablename
-- Exec (@ truncatesql) -- remove it when you want to delete it --
PRINT @ truncatesql
FETCH TrCun_Cursor INTO @ tablename
END
CLOSE TrCun_Cursor
DEALLOCATE TrCun_Cursor

========================================================== ==============================
Returns the parameter information of all fields, stored procedures, and functions of a table.
========================================================== ==============================
If exists (select * from dbo. sysobjects where id = object_id (n' [dbo]. [FN_GetObjColInfo] ') and xtype in (n'fn', n'if', n'tf '))
Drop function [dbo]. [FN_GetObjColInfo]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

/*
Function: returns all fields, stored procedures, and function parameters of a table.
DESIGN: OK _008
Time: 2006-05
*/
Create function FN_GetObjColInfo
(@ ObjName varchar (50 ))
RETURNS @ Return_Table TABLE (
TName nvarchar (50 ),
TypeName nvarchar (50 ),
TypeLength nvarchar (50 ),
Colstat Bit
)
AS
BEGIN
INSERT @ Return_Table
Select B. name as field name, c. name as field type, B. length/2 as field length, whether B. colstat as automatically increases
From sysobjects
Inner join syscolumns B on a. id = B. id
Inner join policypes c on c. xusertype = B. xtype
Where a. name = @ ObjName
Order by B. ColID
RETURN
END

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
[/Code]