SQL Common Commands

SQL Common Commands

http://blog.csdn.net/jenny0107/article/details/1332815

Here are some common SQL statements, although very basic, but it is worth collecting, very useful for beginners

How to use SQL Common commands:
(1) Data record filter:
Sql= "SELECT * from data table where field name = field value order BY field name [desc]"
Sql= "SELECT * from data table where field name like '% field value% ' Order by field name [desc]"
Sql= "SELECT Top ten * from data table where field name order by field name [desc]"
Sql= "SELECT * from data table where field name in (' Value 1 ', ' Value 2 ', ' Value 3 ')"
Sql= "SELECT * from data table where field name between value 1 and value 2"
(2) Update data record:
sql= "Update data table set field name = field value where Condition expression"
sql= "Update data Table set field 1= value 1, field 2= value 2 ... field n= value n Where Condition expression "
(3) Delete data record:
Sql= "Delete from data table where conditional expression"
Sql= "Delete from data table" (delete all records of the datasheet)
(4) Add data record:
Sql= "INSERT into Data table (field 1, Field 2, Field 3 ...) Values (value 1, value 2, value 3 ...) "
Sql= "INSERT INTO Target data table select * From source data table" (Add records from source data table to target datasheet)
(5) Data record statistic function:
AVG (field name) results in a table bar average
Count (*| field name) statistics on the number of data rows or the number of rows with values for a column
Max (field name) gets the maximum value of a table column
Min (field name) gets the lowest value of a table bar
sum (field name) adds the value of the data bar
Methods that refer to the above functions:
Sql= "Select sum (field name) as Alias from data table where conditional expression"
Set Rs=conn.excute (SQL)
Use RS ("Alias") to obtain the value of the system, the other functions used ibid.
(6) Creation and deletion of data sheets:
CREATE table Data table name (field 1 type 1 (length), Field 2 type 2 (length) ...)
Example 1:create TABLE tab01 (Namevarchar (), datetime default Now ())

Example 2:create TABLE table_name (
Column1 DATATYPE [NOT NULL] [NOT NULL PRIMARY KEY],
Column2 DATATYPE [Not NULL],
... ）
Description
DATATYPE--is the format of the data, see table.
NUT null-allows data to be available (not yet filled in).
PRIMARY Key--is the primary key for this table.
drop table data table name (permanently delete a data table)
(7) method of Recordset object:
Rs.movenext moves the record pointer down one line from the current position
Rs.moveprevious moves the record pointer up one line from the current position
Rs.movefirst move the record pointer to the first row of the data table
Rs.movelast move the record pointer to the last row of the data table
Rs.absoluteposition=n move the record pointer to the nth row of the data table
Rs.absolutepage=n move the record pointer to the first row of page N
Rs.pagesize=n set to N records per page
Rs.pagecount returns the total number of pages based on pagesize settings
Rs.recordcount returns the total number of records
RS.BOF returns whether the record pointer is above the first end of the data table, True indicates Yes, False no
Rs.eof returns whether the record pointer is outside the end of the data table, True indicates Yes, False no
Rs.delete deletes the current record, but the record pointer does not move down
Rs.addnew adding records to the end of the data table
rs.update Updating Data Sheet Records

(8) Change of form
ALTER TABLE table_name
ADD COLUMN column_name DATATYPE
Description: Adds a field (no syntax for deleting a field).
ALTER TABLE table_name
ADD PRIMARY KEY (column_name)
Description: Change the definition of a table to set a field as the primary key.
ALTER TABLE table_name
DROP PRIMARY KEY (column_name)
Description: Deletes the definition of the primary key.
(9) Index, delete index
CREATE INDEX index_name on table_name (column_name)
Description: Indexes The fields of a table to increase the speed of the query.
DROP index_name

(10) data type  datatypes
smallint An integer of 16 bits. An integer of the
Interger 32-bit.
Decimal (p,s)
p exact value and S-Size decimal integer, the exact value p is the total number (digits) size value, and s refers to the number of digits after the decimal point. If there is no special designation, the system will be set to p=5; S=0. The
float 32-bit real number. A
Double 64-bit real number.
Char (n) n-length string, n cannot exceed 254. The
varchar (n) length is not fixed and its maximum length is n, and N cannot exceed 4000.
Graphic (n)   is the same as char (n), but its unit is two characters double-bytes,n cannot exceed 127. This pattern is designed to support two character-length fonts, such as Chinese characters.
Vargraphic (n) A variable-length double-character string with a maximum length of n, and N cannot exceed 2000. The
Date contains the year, month, and date.
time contains hours, minutes, seconds. The
timestamp contains years, months, days, hours, minutes, seconds, and 1 per thousand seconds.

(11) Inquiry Information:
Basic Query
SELECT Column1,columns2,...
From table_name
Description: List the specific field information of table_name
SELECT *
From table_name
WHERE column1 = xxx
[and Column2 > YYY] [OR column3 <> zzz]
Description
1. ' * ' indicates that all fields are listed.
The 2.WHERE is then a conditional type, which lists the eligible data.
SELECT Column1,column2
From table_name
ORDER by Column2 [DESC]
Description: ORDER BY is specified as a field to sort, [DESC] refers to a large to small arrangement, if not specified, is from small to large arrangement

Combination Query
A combination query refers to the data source that is queried is not a single table, but a union of more than one
The form will be able to get results.
SELECT *
From Table1,table2
WHERE Table1.colum1=table2.column1
Description
1. Query two tables where the column1 values are the same.
2. Of course two tables are compared to each other, and the data form must be the same.
3. A complex query may be used in a number of forms.

Integrated Query:
SELECT COUNT (*)
From table_name
WHERE column_name = xxx
Description
There are a few strokes to find the eligible information.
SELECT SUM (Column1)
From table_name
Description
1. Calculate the sum, the selected field must be a number of numerical patterns.
2. In addition, AVG () is an integrated query that calculates the average, Max (), Min () to calculate the maximum minimum value.
SELECT Column1,avg (Column2)
From table_name
GROUP by Column1
Having AVG (column2) > XXX
Description
1.GROUP by: The average value of column1 for a set of computed column2 must be used with the keyword of an integrated query such as AVG, Sum.
2.HAVING: Must be used together with GROUP by as an integration restriction.
Complex queries
SELECT *
From table_name1
WHERE EXISTS (
SELECT *
From Table_name2
WHERE conditions)
Description
The conditions of 1.WHERE can be a different query.
2.EXISTS here refers to the existence or not.
SELECT *
From table_name1
WHERE Column1 in (
SELECT Column1
From Table_name2
WHERE conditions)
Description
1. In the back side is a set that represents the Column1 existence set inside.
2. SELECT the data form must conform to column1.
Other queries
SELECT *
From table_name1
WHERE column1 like ' X-percent '
Description: Like must correspond to the ' X x ' in the back to denote a string that begins with X.
SELECT *
From table_name1
WHERE column1 in (' xxx ', ' yyy ',..)
Description: In the back face is a collection that represents the Column1 existence of the collection.
SELECT *
From table_name1
WHERE column1 between XX and yy
Description: Between indicates that the value of Column1 is between XX and yy.

(12) Connection query
The connection syntax format for the FROM clause defined by the SQL-92 standard is:
From Join_table Join_type join_table
[On (Join_condition)]
Where join_table indicates the name of the table participating in the JOIN operation, the connection can operate on the same table, or on a multi-table operation, the connection to the same table operation is also called self-connected.
Join_type indicates the type of connection, which can be divided into three types: inner, outer, and cross-connected.

An inner JOIN (Innerjoin) uses comparison operators to compare the data between tables in a column (or columns), and lists the data rows in those tables that match the join criteria. According to the comparison method used, the inner connection is divided into equivalent connection, natural connection and unequal connection three kinds.
Outer joins are left-OUTER join or Leftjoin, right-to-outer-join (R OUTER join or Rightjoin)
And all-out connections (full OUTER join or fulljoin) three kinds. Unlike an inner join, an outer join lists not only the rows that match the join criteria, but all the data rows in the left table (when the left outer join), the right table (when the right outer joins), or two tables (when you are connected on the outside), all of which match the search criteria.
A cross join has no WHERE clause, which returns the Cartesian product of all data rows in the join table, with the number of rows in the result set equal to the number of data rows in the first table that match the query criteria multiplied by the number of rows in the second table that meet the query criteria.
The ON (join_condition) clause in the JOIN operation indicates the join condition, which consists of columns and comparison operators, logical operators, and so on in the connected table.
Either connection cannot directly connect to the text, ntext, and image data type columns, but you can indirectly connect the three types of columns. For example:
SELECT P1.pub_id,p2.pub_id,p1.pr_info
From pub_info as P1 INNER JOIN pub_info as P2
On datalength (p1.pr_info) =datalength (p2.pr_info)
(i) Internal connection
The INNER JOIN query operation lists the data rows that match the join criteria, and it uses comparison operators to compare the column values of the concatenated columns. There are three types of internal connections:
1. Equivalent connection: Use the equals sign (=) operator in the join condition to compare the column values of the connected columns, which are listed in the query results that are connected
All the columns in the table, including the repeating columns.
2, unequal connections: Use a comparison operator other than the equals operator in the join condition to compare the column values of the connected columns. These
Operators include >, >=, <=, <,!>,!<, and <>.
3. Natural connection: Use the Equals (=) operator in the join condition to compare the column values of the connected column, but it uses the selection list to indicate which columns are included in the query result collection and to delete the duplicate columns in the Join table.
example, the following uses the equivalent connection to list authors and publishers in the same city in the authors and Publishers tables:
SELECT *
From authors as a INNER JOIN publishers as P
On a.city=p.city
If you use a natural connection, delete the authors and Publishers tables in the select list (city and State):
SELECT A.*,p.pub_id,p.pub_name,p.country
From authors as a

INNER JOIN Publishers as P
On a.city=p.city
(ii) External connection
In the query results collection, only the rows that meet the query criteria (WHERE search condition or having condition) and join conditions are returned. In the case of an outer join, it returns to the query result collection not only the rows that meet the join criteria, but also all data rows in the left table (when left outer joins), the right table (when the right outer joins), or two edge tables (full outer joins).
Use the left OUTER join to connect the forum content and author information as follows:
SELECT a.*,b.* from Luntan left JOIN usertable as B
On A.username=b.username
All the authors in the city table and all the authors in the user table, and the cities in which they are located, are used with the full outer join:
SELECT a.*,b.*
From city as a full OUTER JOIN user as B
On A.username=b.username

III) Cross-connect
The cross join does not take a WHERE clause, it returns the Cartesian product of all data rows of the connected two tables, returning to the number in the result set
The number of rows is equal to the number of rows in the first table that meet the query criteria multiplied by the data rows in the second table that meet the query criteria.
example, there are 6 categories of books in the titles table, while the publishers table has 8 publishers, the number of records retrieved by the following cross-connect will be
On the 6*8=48 line.
SELECT Type,pub_name
From the titles cross JOIN publishers
ORDER by Type

union operator can merge the query result collection of two or more than two SELECT statements into a single result set display, That is, execute a federated query. The syntax format for union is:
select_statement
Union [all] Selectstatement
[UNION [All] SELECTSTATEMENT][...N]
Where Selectstatement is the SELECT query statement to be federated. The
All option means that all rows are merged into the result collection. When this item is not specified, only one
row is reserved for duplicate rows in the result collection of the union query. When you
federate a query, the column headings of the query result are the column headings of the first query statement. Therefore, to define a column header, you must define it in the first query language
sentence. To sort the results of a union query, you must also use the column name, column header, or column ordinal in the first query statement.
When using the union operator, you should ensure that you have the same number of expressions in the select list for each union query statement, and that each query select
Alternative expression should have the same data type, or you can automatically convert them to the same data type. When auto-converting, for numeric types, the system converts low-precision data types to high-precision data types. The
in the Union statement, which includes multiple queries, is executed from left to right, using parentheses to change the order of execution. For example:
Query 1 Union (query 2 Union query 3)

SELECT statement

SELECT Branch_office from EMPLOYEES;

SELECT DISTINCT branch_office from EMPLOYEES;

SELECT DISTINCT Branch_office
From EMPLOYEES
ORDER by Branch_office ASC;

Let's say we want to get a list sorted by department and from the highest-paid employees to the lowest-paid employees. In addition to the content in the payroll brackets, we would also like to see a list of employees starting with the most recently hired employee based on their employment time. Here's the statement you're going to use:
SELECT Branch_office,first_name,
Last_name,salary,hire_date
From EMPLOYEES
ORDER by SALARY DESC,
Hire_date DESC;

(13) Stored procedures

--1. Adding descriptive information to a field in a table
Create   TableT2 (id int , name char ( -))
GO
EXECsp_addextendedproperty' Ms_description ', ' Employee ID ', ' user ', dbo,' table ', T2,' column ', ID

EXECSp_updateextendedproperty' Ms_description ', ' This is a test ', ' user ', dbo,' table ', T2,' column ', ID

--2. Modifying the database name
EXECSp_renamedb' Old_db_name ', ' New_db_name '

--3. Modifying the data table name and field name
EXECsp_rename' Old_table_name ', ' New_table_name '--Modify the data table name
EXECsp_rename' table_name. [Old_column_name] ', ' New_column_name ', ' COLUMN '--Modify field names

--4. Get the contents of the stored procedure given the name of the stored procedure
execsp_helptext sp_name

This article comes from the "Ricky's blog" blog, please be sure to keep this source http://57388.blog.51cto.com/47388/1984142

SQL Common Commands