Foundation for a SQL statement, article
1, note: create a database
The CREATE DATABASE DATABASE - name
2, description: delete database
The drop database dbname
3, note: backup SQL server
- to create backup data device
USE the master
EXEC sp_addumpdevice 'disk', 'testBack', 'c: \ mssql7backup \ MyNwind_1 dat'
- start backup
BACKUP the DATABASE pubs TO testBack
4, note: creating a new table
The create table tabname (col1 type1 [not null] (primary key), col2 type2 [not null],.. )
According to the existing table to create a new table:
A: create table (tab_new) like tab_old (use the old table to create A new table)
B: create table (tab_new) as the select col1, col2... The from tab_old definition only
5, note: delete the new table
Drop table tabname
6, note: adding a column
The Alter table tabname add the column col type
Note: the column increase will not be able to delete, the DB2 columns and data types cannot be changed, the only way to change is to increase the length of the varchar type,
7, note: add a primary key
The Alter table tabname add primary key (col)
Note: delete the primary key
The Alter table tabname drop primary key (col)
8, description: create indexes
The create/unique index idxname on tabname (col... .)
Delete index
Drop index idxname
Note: the index is not to change, want to change must be removed to build,
9, note: create a view
The create view viewname as a select statement
Delete the view
Drop the view viewname
10, explanation: a few simple basic SQL statement
Scope of choice: select * from table1 where
Insert: insert into table1 (field1, field2) values (value1, value2)
Delete: delete from table1 range where
Update: update table1 set field1=value1 range where
Find: select * from table1 where field1 value1 likes' % % '- like grammar is very subtle, check information!
Sorting: 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
Biggest: select Max (field1) as maxvalue from table1
Minimum: select min (field1) as minvalue from table1
11, explain: a few advanced query arithmetic word
A: the UNION operator
The UNION operator by combining two other result table (such as TABLE1 and TABLE2) and eliminate any duplicate rows in the table and derive a result table, when ALL be used with the UNION (UNION ALL), don't eliminate duplicate rows, in both cases, the derived tables of each line is not from TABLE1 is from TABLE2,
B: EXCEPT operator
EXCEPT the operator by including ALL but not in the TABLE1 lines in TABLE2 and eliminate ALL repeat and derive a result table, when ALL (EXCEPT ALL), when used with the EXCEPT not eliminate duplicate rows,
C: intersects operator
Intersects the operator by including only in TABLE1 and TABLE2 line and remove ALL duplicate and derive a result table, when ALL with intersects (intersects ALL) when using, don't eliminate duplicate rows,
Note: use the operation a few query result row of words must be consistent,
12: use outer join
A, the left (outer) join:
Left outer join (left), the result set a few including matching line connection table, also include all rows in the left connection table,
SQL: select a.a, a., a.c, biggest, b.d. B. the from a LEFT OUT the JOIN ON a.a b=biggest
B: right (outer) join:
Right connection (right) : the result set includes matching connection line of the join table and also include the right connection table all rows,
C: full/cross (outer) join:
Full outer join: not only includes link table of matching lines, including all records in the two connection table,
13, Group: Group by:
After the search, after the completion of a table, once the group can only get a set of related information,
Group of related information: (statistics) count, sum, Max, min, avg grouping standard)
In essentially a grouping: not to text, ntext, image type field as a group on the basis of
In the fields in the selecte statistics function, cannot put together and ordinary field;
14, to manipulate
the database
Separate database: sp_detach_db; Additional database: sp_attach_db followed by shows that the additional need complete path name
15, how to change the database name
Sp_renamedb 'old_name', 'new_name'
SQL statements ascension article
Second, the promotion article
1, note: the replication table (only copy structure, source table name: a new table name: b) (Access available)
Method one: select * into b from a where clause 1 & lt;> (1) only for used to
Method 2: select top 0 * into b from a
2, description: copy list (copy data, source table name: a target table name: b) (Access available)
Insert into b (a, b, c) select d, e, f from a;
3, note: copies of cross between database tables (specific data using absolute path), the Access is available)
Insert into b (a, b, c) select d, e, f from b 'specific database in the where condition
Example:.. In the from b '" & amp; Server. The MapPath (". ") & amp;" \ data. MDB & amp; "" 'where..
4, note: sub-query (name of the table 1: a table name: 2 b)
Select a, b, c from a where clause IN (select d from b) or a: select a, b, c from a where a IN (1, 2, 3)
5, note: according to the article, submission and final recovery time
Select a.t itle, a.u sername, b.a dddate from table a, (select Max (adddate) adddate from the table where the table. The title=a.t itle) b
6, note: outer join query (name of the table 1: a table name: 2 b)
The select a.a, a., a.c, biggest, b.d. B. the from a LEFT OUT the JOIN ON a.a b=biggest
7, note: online view query (name of the table 1: a)
Select * from (select a, b, c from a) T where t.a & gt; 1;
8, description: between usage, when the limit between query data including the boundary value, not between, not including
Select * from table1 the where the time between time1 and time2
Select a, b, c, the from table1 where a not between number 1 and number 2
9, note: in the use of
Select * from table1 where a [not] in (' 1 ', 'value 2', 4 'value', '6' value)
10, description: two associative table, delete the main table in the side tables have no information in the
The delete from table1 where not the exists (select * from table2 where table1. The field1=table2. Field1)
11: four tables LianZha problems
Select * from a left inner join on a.a b=b.b right inner join on a.a c=Arthur c. inner join d on a.a=d.d where...
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull
