Summary of standard SQL statements

Summary of standard SQL statements, the standard SQL language is basically applicable to the database software listed below
-----------------------------------------------------------------------------
Database Software List
A commercial database software is as follows:
1. Microsoft's ms SQL Server and access
2. IBM DB2, informax
3. Sybase large database ase, Small and Medium databases ASA
4. Oracle's oracle8.0 and Oracle9i Series
5. Borland's InterBase
B Multiple Open Source Free Databases

MySQL, PostgreSQL, SQLite, simplesql, Berkely dB, minosse, Firebird

-----------------------------------------------------------------------------
The simplest and most 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. delete database drop database dbname
3. Description:
back up SQL server
use master
exec sp_addumpdevice 'disk', 'testback', 'c: \ mssql7backup \ mynwind_1.dat '
Start backup
backup database pubs to testback
4. Description: create a new table
Create Table tabname (col1 type1 [not null] [primary key], col2 type2 [not null],...)
Create a new table based on an existing table:
A: Create Table tab_new like tab_old (Use the old table to create a new table)
B: Create Table tab_new as select col1, col2... From tab_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 primary key: alter table tabname add primary key (COL)
Delete primary key: alter table tabname drop primary key (COL)
8.
Create an index: Create [unique] index idxname on tabname (COL ....)
delete an index: drop index idxname

9. Description:
Create view: Create view viewname as select statement
Delete view: Drop view viewname
10. Description: several simple basic SQL statements
select: Select * From Table1 where range
insert: insert into Table1 (field1, field2) values (value1, value2)
Delete: delete from Table1 where range
Update Table1 set field1 = value1 where range
Search: select * From Table1 where field1 like '% value1 %' --- the like syntax is very subtle, query 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 from Table1
minimum: select min (field1) as minvalue from Table1
-------------------------------------------------------------------------

-- Common Data Operations
Select -- retrieve data from a database table
Insert -- add new data to the database table
Update -- update data in the database table
Delete -- delete data from a database table
-----------------------------------------------------------------------------
-- Database object operation statement
Create Table -- create a database table
Alter table -- modify the database table structure
Drop table -- delete a table from a database

Create View -- create a view
Alter View -- modify a view
Drop View -- delete a view

Create index -- create an index for the table
Drop index -- delete an index from a table

Create procedure -- create a stored procedure
Drop procedure -- delete a stored procedure

Create trigger -- create a trigger
drop trigger -- delete a trigger
revoke
-- Data permission Control
Grant -- Grant the user access permission
deny -- deny user access
revoke -- revoke User Access Permissions
allow
-- transaction control
commit -- end the current transaction
rollback -- stop the current transaction transaction
SET transaction -- define the data access features of the current transaction
-----------------------------------------------------------------
-- database function, process, SQL of the trigger script
declare -- set the cursor for the query
explain -- open the query description Data Access Plan
open -- open a cursor for the query result
fetch -- retrieve a row of query results
close -- close the cursor
prepare -- prepare SQL statements for dynamic execution
execute -- execute SQL statements dynamically
describe -- describe prepared query
-----------------------------------------------------------------------------
select -- retrieve data from the 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] %' --------- ([] 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 stock *** = '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 query result sets, all-retained duplicate rows
Select stockname from Table2

-----------------------------------------------------------------------------
Insert -- add new data to the 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 -- update data in the database table

Update table_name set
Stockname = "XXX" [where stockid = 3]
Stockname = default
Stockname = NULL
Stocknumber = stockname + 4
Where ***

-----------------------------------------------------------------------------
Delete -- delete data from a database table

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

-----------------------------------------------------------------------------
Standard SQL statistical functions
AVG -- average value
Count -- count
Max -- calculate the maximum value
Min -- Minimum value
Sum -- sum

AVGCodeExample
Use pangu
Select AVG (e_wage) as dept_avgwage from employee group by dept_id

Max code example -- 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 () -- returns the ASCII value of the leftmost character of a character expression.
Char () -- function is used to convert ASCII code to character -- if no input 0 ~ The Char function returns a null value for the ASCII code 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.
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') run the following result: 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.

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

Local variables and local variables
--- Local variable (starting)
Format: declare @ variable name Type
-- Set @ ID = '20140901'
Select @ ID = '000000'

--- Global variable (must start)
Format: declare @ variable name Type
Code: Select @ ID = '20140901'

-- 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
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

From: http://dilantaya20070723211817.iteye.com/blog/245450