Standard SQL statements, learning

Summary of standard SQL statements
Standard SQL statement Summary, the standard SQL language is basically applicable to the database software listed below
-----------------------------------------------------------------------------
Database Software Inventory
A business database software is as follows
1. Microsoft's MS SQL Server and Access
2.IBM of Db2,informax
Large database ASE for 3.Sybase, small to medium database ASA
4. Oracle Company's Oracle8.0,oracle9i series
5.Borland Company's InterBase
b multiple open source free databases

Mysql,postgresql,sqlite, Simplesql, Berkely DB, Minosse, Firebird

(Mysql,postgresql is currently the most widely used)
-----------------------------------------------------------------------------
The most streamlined and short SQL statement
SQL classification:
ddl-Data Definition language (create,alter,drop,declare)
dml-Data Manipulation Language (Select,delete,update,insert)
dcl-Data Control Language (Grant,revoke,commit,rollback)

1, Description: Create DATABASE Database-name
2, Description: Delete database drop databases dbname
3, Description:
Back up SQL Server
use master Br>exec sp_addumpdevice ' disk ', ' testback ', ' C:\mssql7backup\MyNwind_1.dat '
start backup
Backup DATABASE pubs to Testback
4, Description: Creating a new Table
CREATE TABLE tabname (col1 type1 [NOT NULL] [primary key],col2 type2 [NOT NULL],..)
Create a new table from an existing table:
A:create table tab_new like Tab_old (create new table with old table)
B:create table tab_new as Select Col1,col2 ... from TA B_old definition only
5, Description: Delete the new table drop table TabName
6, Description: Add a column ALTER TABLE tabname add column col type
7, description
Add Garther: ALTER TABLE tabname add primary key (COL)
Delete primary key: ALTER TABLE tabname drop PRIMARY key (COL)
8,
CREATE INDEX: Create [ Unique] Index idxname on tabname (col ...)
Delete index: Drop index Idxname

9, Description:
Creating view: Create VIEW viewname AS SELECT statement
Delete view: Drop view viewname
10, Description: Several simple basic SQL statements
Selection: SEL ECT * FROM table1 where range
Insert: INSERT INTO table1 (field1,field2) VALUES (value1,value2)
Remove: Delete from table1 where Scope
Updated: Update table1 set field1=value1 where scope
Lookup: SELECT * FROM table1 where field1 like '%value1% '---the syntax of like is subtle, Check the information!
Sort: SELECT * FROM table1 ORDER by FIELD1,FIELD2 [desc]
Total: Select count as totalcount from table1
sum: select SUM ( field1) as Sumvalue from table1
Average: Select AVG (field1) as Avgvalue from table1
Maximum: Select Max (field1) as MaxValue fr Om table1
min: select min (field1) as MinValue from table1
------------------------------------------------------ -----------------------

--Common data operations
SELECT-Retrieve data from a database table
INSERT--Add new data to a database table
Update-Updates data in a database table
Delete--delete data from the database table
-----------------------------------------------------------------------------
--Database object operation statement
CREATE table--Creating a database table
ALTER TABLE--Modify the database table structure
DROP table--delete tables from the database

Create View-Creates a view
Alter VIEW--Modify a view
Drop View--delete a view

Create index--creates an index for the table
Drop INDEX--Remove the indexes from the table

Create procedure--creating a stored procedure
Drop PROCEDURE--delete stored procedure

Create TRIGGER--creating a trigger
Drop TRIGGER--from deleting triggers
-----------------------------------------------------------------------------
--Data access control
Grant--Granting user access
Deny--Deny user access
REVOKE--Remove user access rights
-----------------------------------------------------------------------------
--Transaction control
COMMIT--End the current transaction
ROLLBACK--Abort the current transaction
SET TRANSACTION--Define the current transactional data access characteristics
-----------------------------------------------------------------------------
--SQL for database functions, procedures, trigger scripts
DECLARE--Setting cursors for queries
Explan--describing data access plans for queries
Open--Retrieving query results opens a cursor
FETCH--Retrieves a row of query results
Close--Closes the cursor
PREPARE-Preparing SQL statements for dynamic execution
Execute--Execute SQL statements Dynamically
DESCRIBE--Describe the prepared query
-----------------------------------------------------------------------------
SELECT-Retrieve data from a database table

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 a range of values)
StockName like ' [^f-m]% '---------(^ exclude specified range)
---------can only use wildcard characters in the WHERE clause using the LIKE keyword)
or Stockpath = ' Stock_path '
or Stocknumber < 1000
and Stockindex = 24
Not stock*** = "man"
Stocknumber between 100
Stocknumber in (10,20,30)
ORDER BY Stockid DESC (ASC)---------Sort, desc-descending, asc-ascending
Order BY----------by column number
StockName = (select StockName from stock_information where Stockid = 4)
---------Sub-query
---------unless you can ensure that the inner select returns only one row of values,
---------Otherwise, an in qualifier should be used 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 + ten" = Stocknumber + from table_name
Select StockName, "stocknumber" = count (*) from table_name GROUP BY StockName
---------GROUP BY grouping tables by rows with the same values in the specified column
Have count (*) = 2---------Having the specified group selected

SELECT *
From Table1, table2
where table1.id *= table2.id----LEFT outer JOIN, Table1 in some and not NULL in table2
Table1.id =* table2.id--------Right external connection

Select StockName from table1
Union [ALL]-----Union merge query result set, all-preserves duplicate rows
Select StockName from Table2

-----------------------------------------------------------------------------
INSERT--Add new data to a database table

INSERT INTO table_name (stock_name,stock_number) value ("XXX", "xxxx")
Value (select StockName, stocknumber from Stock_table2)---value is a SELECT statement

-----------------------------------------------------------------------------
Update-Updates data in a database table

UPDATE table_name SET
StockName = "xxx" [Where Stockid = 3]
StockName = Default
StockName = null
Stocknumber = StockName + 4
WHERE * * *

-----------------------------------------------------------------------------
Delete--delete data from the database table

Delete from table_name where Stockid = 3
Truncate table_name-----------Delete all rows in the table and still maintain the integrity of the table
DROP TABLE table_name---------------completely delete tables

-----------------------------------------------------------------------------
Standard SQL Statistics functions
AVG--averaging
Count--Number of statistics
Max--Max value
Min--Find the minimum value
Sum--sum

Avg code example
Use Pangu
Select AVG (e_wage) as Dept_avgwage from employee GROUP by dept_id

Max code example-the name of the employee with the highest salary
Use Pangu
Select E_name from employee WHERE e_wage = (select Max (e_wage) from employee)
-----------------------------------------------------------------------------
Standard SQL String functions

ASCII ()--function returns the ASCII value of the leftmost character of the character expression
char ()--function is used to convert ASCII code to characters--if no ASCII value between 0 ~ 255 is entered, the CHAR function returns a null
LOWER ()--function converts all strings to lowercase
UPPER ()--function converts all strings to uppercase
STR ()--function converts numeric data to character data
LTRIM ()--function to remove whitespace from the head of a string
RTRIM ()--function to remove whitespace from the trailing of a string
Left (), right (), SUBSTRING ()--function returns a partial string
CHARINDEX (), PATINDEX ()--function returns the beginning of the occurrence of a specified substring in a string
REPLICATE ()--function returns a string that repeats character_expression a specified number of times
Select Replicate (' ABC ', 3) replicate (' abc ',-2) Run the result as follows ABCABCABC NULL
REVERSE ()--function reverses the character arrangement of the specified string
Replace ()--function returns a string replaced by the specified substring
Select replace (' abc123g ', ' 123 ', ' Def ') runs the result as follows ABCDEFG
Space ()--function returns a blank string with a specified length
STUFF ()--function specifies a substring of the position length with another substring substitution string

-----------------------------------------------------------------------------
Standard SQL syntax

Local Variables and variables
---Local variables (start with @)
Format: DECLARE @ variable name type
--set @id = ' 10010001 '
Select @id = ' 10010001 '

---Global variables (must start with @@ 开头
Format: Declare @@ 变量 name type
Code: SELECT @ @id = ' 10010001 '

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

--waitfor
--Wait 1 hours, 2 minutes, 3 seconds before executing the SELECT statement
WAITFOR DELAY ' 01:02:03 '
SELECT * FROM Employee
--Example wait until 11 o'clock 8 minutes after the SELECT statement is executed
waitfor time ' 23:08:00 '
SELECT * FROM Employee

Standard SQL statements, learning