SQL Server basic syntax example (1)

I. Basics 1. Description: Create a database

CREATE DATABASE database-name 

2. Description: delete a database.

DROP  DATABASE database-name

3. Description: Back up the database

USE master -- create deviceEXEC sp_addumpdevice 'disk', 'CC _ jz', 'd: \ cc_jz.dat 'for BACKUP data -- start backup database cc_jz TO cc_jz

4. Description: Create a new table.

Create table tabname (col1 type1 [not null] [primary key], col2 type2 [not null],...) --> test data: [a] if object_id ('[a]') is not null drop table [a] go create table [a] ([ID] int, [product name] varchar (6), [import quantity] int, [import time] datetime) insert [a] select 1, 'Mineral water ', 100, '2014-01-02 'union allselect 2, 'taobao', 60, '2014-01-03' union allselect 3, 'taobao', 50, '2014-01-03 'union allselect 4, 'Mineral Water', 80, '2017-01-04 'union allselect 5, 'Instant noodle', 50, '2017-01-05 'select * from a/* ID item name warehouse receiving time ----------- ------ ------------------------------- 1 mineral water 2013 100 00:00:00. 0002 instant noodles 60 00:00:00. 0003 instant noodles 50 00:00:00. 0004 mineral water 80 00:00:00. 0005 instant noodles 50 00:00:00. 000 (5 rows affected )*/

5. Description: delete a new table.

drop table tabname 

6. Description: Add a column.

Alter table tabname add column col typeAlter table a add col intselect * from a/* ID item name warehouse receiving time col ----------- ------ ----------- ----------------------- --------- 1 mineral water 100 00:00:00. 000 NULL2 instant noodles 60 00:00:00. 000 NULL3 instant noodles 50 00:00:00. 000 NULL4 mineral water 80 00:00:00. 000 NULL5 instant noodles 50 00:00:00. 000 NULL (5 rows affected )*/

7. Description: Add a primary key:

 Alter table tabname add primary key(col) 

Note: To delete a primary key:

Alter table tabname drop primary key(col) 

8. Description: Create an index:

create [unique] index idxname on tabname(col….) 

Delete An 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 -- insert: insert into table1 (field1, field2) values (value1, value2) -- delete: delete from table1 -- where range -- Update: update table1 set field1 = value1 -- where range -- Query: select * from table1 where field1 like '% value1 %' -- 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

11. Description: several advanced query Operators

A: UNION/union all 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.

--> Test data: [a] if object_id ('[a]') is not null drop table [a] go create table [a] ([ID] int) insert [a] select 1 union allselect 1 union allselect 2 union allselect 3 union allselect null select * from a/* (5 rows affected) ID-----------1123NULL (5 rows affected) */--> test data: [B] if object_id ('[B]') is not null drop table [B] go create table [B] ([ID] int) insert [B] select 1 union allselect 2 union allselect 2 union allselect 4 union allselect null select * from B/* (5 rows affected) ID-----------1224NULL (5 rows affected) */-- merge distinct select * from a unionselect * from B/* ID-----------NULL1234 (5 rows affected) */-- merge distinct select * from a union allselect * from B/* ID-----------1123NULL1224NULL (10 rows affected )*/

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.

Note: There is no limit t ALL usage at ALL. Many articles on the Internet contain limit t all, which is actually incorrect. (Test SQL Server 2000 2005 2008R2 2012 is not applicable)

--> Test data: [a] if object_id ('[a]') is not null drop table [a] go create table [a] ([ID] int) insert [a] select 1 union allselect 1 union allselect 2 union allselect 3 union allselect null select * from a/* (5 rows affected) ID-----------1123NULL (5 rows affected) */--> test data: [B] if object_id ('[B]') is not null drop table [B] go create table [B] ([ID] int) insert [B] select 1 union allselect 2 union allselect 2 union allselect 4 union allselect null select * from B/* (5 rows affected) ID-----------1224NULL (5 rows affected) */-- take different data from two tables and deduplicate select * from a distinct T select * from B/* ID---3 (1 row is affected) */

C: INTERSECT Operator

The INTERSECT operator derives a result table by only including the rows in TABLE1 and TABLE2 and eliminating all repeated rows.

Note: There is no intersect all usage at ALL. Many articles on the Internet contain intersect all, which is actually incorrect. (Test SQL Server 2000 2005 2008R2 2012 is not applicable)

--> Test data: [a] if object_id ('[a]') is not null drop table [a] go create table [a] ([ID] int) insert [a] select 1 union allselect 1 union allselect 2 union allselect 3 union allselect null select * from a/* (5 rows affected) ID-----------1123NULL (5 rows affected) */--> test data: [B] if object_id ('[B]') is not null drop table [B] go create table [B] ([ID] int) insert [B] select 1 union allselect 2 union allselect 2 union allselect 4 union allselect null select * from B/* (5 rows affected) ID-----------1224NULL (5 rows affected) */-- Take the same data from two tables and de-duplicate select * from a INTERSECT select * from B/* ID-----------NULL12 (3 rows affected )*/

 

12. Note: use external connections
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

--> Test data: [a] if object_id ('[a]') is not null drop table [a] go create table [a] ([ID] int) insert [a] select 1 union allselect 1 union allselect 2 union allselect 3 union allselect null select * from a/* (5 rows affected) ID-----------1123NULL (5 rows affected) */--> test data: [B] if object_id ('[B]') is not null drop table [B] go create table [B] ([ID] int) insert [B] select 1 union allselect 2 union allselect 2 union allselect 4 union allselect null select * from B/* (5 rows affected) ID-----------1224NULL (5 rows affected) */select. *, B. * from a left join B ON. id = B. id/* ID ----------- --------- 1 11 12 22 23 nullnull null (6 rows affected )*/

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.

--> Test data: [a] if object_id ('[a]') is not null drop table [a] go create table [a] ([ID] int) insert [a] select 1 union allselect 1 union allselect 2 union allselect 3 union allselect null select * from a/* (5 rows affected) ID-----------1123NULL (5 rows affected) */--> test data: [B] if object_id ('[B]') is not null drop table [B] go create table [B] ([ID] int) insert [B] select 1 union allselect 2 union allselect 2 union allselect 4 union allselect null select * from B/* (5 rows affected) ID-----------1224NULL (5 rows affected) */select. *, B. * from a right join B ON. id = B. id/* ID ----------- --------- 1 11 12 22 2 NULL 4 NULL (6 rows affected )*/

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.

--> Test data: [a] if object_id ('[a]') is not null drop table [a] go create table [a] ([ID] int) insert [a] select 1 union allselect 1 union allselect 2 union allselect 3 union allselect null select * from a/* (5 rows affected) ID-----------1123NULL (5 rows affected) */--> test data: [B] if object_id ('[B]') is not null drop table [B] go create table [B] ([ID] int) insert [B] select 1 union allselect 2 union allselect 2 union allselect 4 union allselect null select * from B/* (5 rows affected) ID-----------1224NULL (5 rows affected) */select. *, B. * from a full join B ON. id = B. id/* ID ----------- --------- 1 11 12 22 23 NULLNULL 4 NULL (8 rows affected )*/

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

14. perform operations on the database:

Detaching a database: sp_detach_db;

Attached Database: sp_attach_db indicates that the complete path name is required for appending.

15. How to modify the Database Name:

sp_renamedb 'old_name', 'new_name'