SQL Server Foundation

1. Structure language classification

DDL (data definition Language) Create drop alter creates deletes and modifies databases, tables, stored procedures, triggers, indexes ....

DML (Data manipulation language) Insert Delete update to manipulate data in the database

DQL (data Query language) Select is used to query data in the database

DCL (Data Control Language) grant (AUTHORIZATION) revoke (revocation)

2. Logical operators

&& and
or | | Or
Not! Non -

The identity column is the self-increment column!

The primary key is unique!

Federated primary Key

Set multiple columns to primary key!

3. Inserting data
(Column and value one by one correspond)

Insert into table name (column)

VALUES (value)

3.1 Copying more than one piece of data

Insert INTO ... select ... from

(two table column name one by one corresponds, there is a table)

Insert into new table name (each column name)

Select column names from the original table name

3.2 Copying more than one piece of data

Select ... into ... from

(No table)

Select each column name

into new table name

From the original table name

3.3 Inserting multiple rows of data at once

There are three ways to insert multiple rows of data at one time:

(1) Adding data through the Insert SELECT statement Phase table

INSERT into AddressList (name, address, email)

SELECT Sname,saddress,semail

From Student

Note: 1, the number of data to be queried, the order, the data type, etc., must be consistent with the inserted item. 2,addresslist tables must be pre-created and have names, addresses, e-mail boxes of these three categories.

(2) Add existing data to the table by using the SELECT INTO statement

Example: SELECT Students.sname,students.saddress,students.semali

Into AddressList

From Students

When inserting data into a new table, there is a new question, how do i insert an identity column?

Syntax: SELECT identity (data type, identity seed, identity growth) as column name

into new table

From original table

(3) Insert by Union Key Sub-merge data

Example: INSERT Students (Sname,sgrade,ssex)

SELECT ' Zhang Ke ', 7,1union

SELECT ' Zhang Ke ', 7,1union

SELECT ' Zhang Ke ', 7,1

4. Updating data

Update table name

Set the columns that need to be updated (, separated)

where owned conditions (and or or are separated)

is empty is null or ""

5. Delete data

Specify criteria (when a foreign key is included, deleting the data in the primary table must delete the child table data)

Delect from table name

where data

Delete data using TRUNCATE TABLE

Truncae TABLE studnts

Delete all data in a table

SQL Server Foundation