Manipulating data with SQL statements

One. SQL

Structured Query Language

The relationship between 1.t-sql and SQL

T-SQL is an enhanced version

The composition of the 2.SQL

2.1 DML (Data Manipulation language)

Add, modify, delete, etc data operations

2.2 DCL (Data Control Language)

Storage License, storage permissions

2.3 DQL (data query Language)

Querying data

2.4 DDL (data definition language)

Create database, table, delete database, table

Two. Operators

1. Logical operators

Returns true if and only if two Boolean expressions are true.
OR, returns False if and only if two Boolean expressions are false.
The value of the not Boolean expression is reversed

2. Comparison operators

= Equals, for example: age=23
> Greater than, for example: price>100
< less than
<> Not equal to
>= greater than or equal to
<= less than or equal to
! = Not equal to (non-SQL-92 standard)


Three. Inserting data

1. Syntax

INSERT [into] indicates (column list, each column separated by commas) values (value list)


2. Precautions:

2.1 Data types remain consistent
2.2 Value list is consistent with column list order
2.3 Can not insert half a row or a few columns of data, the number of data consistency
2.4 Cannot assign a value to an identity column
2.5 to conform to the CHECK constraint specification
2.6 Data must be inserted when NULL is not allowed
2.7 Columns with default values can be substituted with the default insert


Four. Inserting multiple rows of data

1. insert into table name to insert (column list) SELECT from where to get the column name of the data from where to get the table name of the data

2.SELECT (column name) into < table name > from < source table name >

3.INSERT into < table name > (column name) SELECT < column name > union select < Column name > union

Five. Modify the data

UPDATE table Name set column name = value WHERE condition

1. Change to ensure that the conditions limit, to avoid the loss of valid data, such as where sid=1, if you want to use multiple conditions to limit the use of logical operators splicing, for example: WHERE sid=1 and Sname= ' Chen Gang '

2. When you update multiple columns of data, each column is separated by commas

Six. Delete Data

DELETE from table name WHERE column name = value

1. Ensure that no referential integrity violation error occurs when data is deleted
2. If the table has a reference relationship, delete the data in the child table before you can delete the child table
3. Cascading: If you want to forcibly delete data from the primary table, you can set the cascade when the primary foreign key relationship is set, and the associated data in the child table is deleted when the primary table data is deleted

Seven. Import and Export

When you export, select the database that you want to export, and then choose how to export, either as a exal or flat file, and then select a database table, you can select multiple, and then export

When importing, select the file to import, then select the database to import, then select the table for the database, and then import the data, provided that the table in the database in which you are importing the data already exists

Manipulating data with SQL statements