Basic SQL statements and SQL statements

Basic SQL statements and SQL statements

Some commonly used SQL statement reference: http://www.cnblogs.com/acpe/p/4970765.html

This blog post is well organized and I will extract some of the most commonly used ones.

Create a database
Create database database-name

Delete Database
Drop database dbname

Create a new table
Create table tabname (col1 type1 [not null] [primary key], col2 type2 [notnull],...)
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... Fromtab_old definition only

Delete a new table
Drop table tabname
Add a column
Alter table tabname add column col type
Note: Columns cannot be deleted after they are added. After columns are added to DB2, the data type cannot be changed. The only change is to increase the length of the varchar type.
Add primary key

Alter table tabname add primary key (col)
Delete primary key

Alter table tabname drop primary key (col)
Create an index

Create [unique] index idxname ontabname (col ....)
Delete Index

Drop index idxname
Note: The index cannot be changed. To change the index, you must delete it and recreate it.
Create View

Create view viewname as select statement
Delete View

Drop view viewname
Select: select * from table1 where range
Insert: insert into table1 (field1, field2) values (value1, value2)
Delete: delete from table1 where range
Update: 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
Max: select max (field1) as maxvalue from table1
Min: select min (field1) as minvalue from table1

Group: Group:
A table can only obtain group-related information after the query.
Group-related information: (Statistical Information) Standard of count, sum, max, min, and avg groups)
When grouping in SQLServer: fields of the text, ntext, and image types cannot be used as grouping bases.
Fields in the selecte statistical function cannot be put together with common fields;

Modify the Database Name:
Sp_renamedb 'old _ name', 'new _ name'

A: UNION operator
The UNION operator combines two other result tables (such as TABLE1 and TABLE2) and removes any duplicate rows from the table to generate a result table. When ALL is used together with UNION (that is, union all), duplicate rows are not eliminated. In either case, each row of the derived table is from either TABLE1 or table2.
B: Random t operator
The distinct t operator derives a result table by including all rows in Table 1 but not in table 2 and eliminating all repeated rows. When ALL is used with distinct T (distinct t all), duplicate rows are not eliminated.
C: INTERSECT Operator
The INTERSECT operator derives a result table by only including the rows in TABLE1 and TABLE2 and eliminating all repeated rows. When ALL is used with INTERSECT (intersect all), duplicate rows are not eliminated.
Note: The query results of several computation words must be consistent.

 

Use external connection
A. left (outer) join:
Left Outer Join (left join): the result set contains the matched rows in the connected table, and all rows in the left connected table.
SQL: select a. a, a. B, a. c, B. c, B. d, B. f from a left out join B ON a. a = B. c
B: right (outer) join:
Right Outer Join (right join): the result set includes both matched join rows in the connection table and all rows in the right join table.
C: full/cross (outer) join:
Full outer join: includes not only matching rows in the symbolic join table, but also all records in the two join tables.

 

Subquery (table name 1: a table name 2: B)
Select a, B, c from a where a IN (select d from B) or: select a, B, c from a where a IN (1, 2, 3)

 

How to use in
Select * from table1 where a [not] in ('value 1', 'value 2', 'value 4', 'value 6 ')

 

Select (Select) field from (Table Name) where (filtering condition)
Group by (field name, by which group) having (condition, filtering in each group)
Order)