Years of sorting and collection: A list of very classic SQL statements

The following statements are MsSql statements and cannot be used in access.

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)

First, we will briefly introduce the basic statements:

1. Description: create database database-name

2. Description: Delete the database drop database dbname.

3. Description: Back up SQL server

--- Create a device for the backup data

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

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.

7. Description: add a primary key: Alter table tabname add primary key (col)

Delete a primary key: Alter table tabname drop primary key (col)

8. Description: create an index: create [unique] index idxname on tabname (col ....)

Delete index: drop index idxname

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

9. Description: Create a 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: 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

11. Description: several advanced query Operators

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.

12. Note: Use outer join A and 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 outer join:

Full outer join: includes not only matching rows in the symbolic join table, but also all records in the two join tables.

Next, let's look at some good SQL statements.

1. Description: copy a table (only copy structure, source table name: a new table name: B) (Access available)

Method 1: select * into B from a where 1 <> 1

Method 2: select top 0 * into B from

2. Description: copy a table (copy data, source table name: a target table name: B) (Access available)

Insert into B (a, B, c) select d, e, f from B;

3. Description: Table Copying across databases (absolute path for specific data) (Access is available)

• Three pages in total:
• Previous Page
• 1
• 2
• 3
• Next Page