A SQL statement that cannot be underestimated

In front of us to learn through the mouse to the data table interpolation data, although this method is very reliable, but there are some shortcomings, is more cumbersome and inefficient.
So now our good studious SQL statement, to make up for such a loophole, can improve the efficiency of our work.

What can SQL statements do?

The database requires a set of instruction sets (SQL language) that can identify instructions and perform appropriate operations.

Operators in 1.SQL:

01: Arithmetic Operator:

+,-,*,/,%

02. Assignment Operators:

=

03. Comparison Operators:

=,>,<,<>,>=,<=,!=

04. Logical operators:

And,or,not

One: What is the purpose of SQL statements?

Parsing: "Adding, modifying, and deleting data"

SQL Statement Writing specification:

01. In SQL Sever, SQL statements are case insensitive (database name, table name, column name, keyword), and SQL compiler recognizes

02, in the operation of the table, be sure to make use of the keyword to switch to the corresponding database

03. Self-increment column can not be assigned value

1. Ways to add Data:

Insert into table name (column name)

VALUES (data)

Eg:insert into Goodperson (stuname,stuage)

Values (' Xiao Ling ', 20)

Attention:

01. If all columns are added, the table name may not be followed by the column name, but the value of all columns is provided, unless the current

The default keyword is also given.

02. If you want to add only a subset of the columns to a table, follow the above name in the table name and make sure that you have

column, the other columns are allowed to be empty or have default values.

2. A solution for inserting multiple data into a single table at once:

01. Syntax:

SELECT (column name)

into < table name >

From < source table name >

eg

INSERT INTO GoodPerson2

Select Stuname, Stuage, stuaddress, Stulike, stuwishes from Goodperson

Delete from Goodperson2

Note: You cannot use the * number if there are self-increment columns in the target.

02. Syntax:

SELECT * into target table (not present)

From original table

eg

SELECT * Into Goodpersonbak

From Goodperson

Note:

This method retains only the data and the child increment columns, but the constraints such as the primary key are all gone.

03. Syntax

INSERT into < table name > (column name)

SELECT < column name > UNION

SELECT < column name > UNION

eg

SELECT * FROM Grade

INSERT INTO Grade

Select ' High class one or two '

Union

Select ' High Class 15 '

3. How to update data:

Grammar:

Update table name set column name = Update value

[WHERE Update condition]

eg

UPDATE Students

SET saddress = ' Beijing women's vocational and technical school housekeeping class '

WHERE saddress = ' Embroidery class of Beijing women's Vocational Technical school '

UPDATE Scores

SET Scores = Scores + 5

WHERE Scores <= 95

Note: Updating multi-column data is separated using commas

Do not forget the condition limit to prevent the loss of valid data

4. Delete data:

01: Delete Data rows with delete

Grammar:

delete [from] table name [WHERE < delete condition;]

eg

DELETE from Students

WHERE SName = ' Zhang Qing cut '

02. Delete Data rows using truncate

Grammar:

TRUNCATE Table Name

eg

TRUNCATE TABLE Students

Attention:

Table structure, columns, constraints, etc. are not

Changes

cannot be used with a table with a FOREIGN KEY constraint reference

Identity column Restart numbering

Experience:

Use truncate TABLE as sparingly as possible in actual work, because the data it deletes cannot be recovered

5. What is the difference between delete and truncate?

parsing: 01.delete can be followed where conditions, and truncate not

02.delete logs are logged when data is deleted, and truncate does not

03.delete Delete all databases in the table, the ID number will not start at 1, and truncate will.

Expert analysis: Because truncate is also called "TRUNCATE TABLE". Numbering starts from 1.

6. How to query data:

Query for data in a row data table, you must first locate the database that contains the table

eg

Use MySchool--switch database

Select Stuid, Stuname, Stuage, Gradeid from Student

--I only want information about students younger than age.

SELECT * FROM Student where stuage<=22

7. Several points of attention

01. See the UPDATE statement, be sure to follow the where condition, which is the command.

02. The null representation in SQL Server does not know, so if the after-where qualification cannot be compared with = and NULL, the is null must be used

8. Import data:

Attention:

Check the legitimacy of imported data based on constraints, primary foreign key relationships, etc. set in database tables before importing data

Import and export of data can exchange data with text files, Excel files

I am very glad that after reading carefully, we can get the harvest.

A SQL statement that cannot be underestimated