Shulou(Shulou.com)06/01 Report--

Insert data

INSERT grammatical structure

INSERT [INTO] [column name] VALUES

Parameter interpretation

[INTO] and [column name] are optional

Is necessary.

If [column name] is omitted, it is consistent with the order of the fields in the table.

Multiple column names and multiple values lists are separated by commas

Insert a row of data into the student table

Insert into student (name, × ×, class, grade)

Values ('Wang Lan', '126125199008222446)

Update data

UPDATE grammatical structure

UPDATE SET [WHERE]

Parameter interpretation

The WHERE clause is optional and is used to restrict conditions

If the WHERE sentence is omitted, all data rows in the table will be updated

Change the score of Wang Lan in the student table to 95, and the note is "studious"

Update student set score ='95', remarks = 'studious' where name = 'Wang Lan'

Delete data

DELETE grammatical structure

DELETE FROM [WHERE]

Parameter interpretation

The WHERE clause is optional to restrict deletion conditions

If the WHERE sentence is omitted, all data rows in the table will be deleted

Truncate Table grammatical structure

Truncate table

Delete all rows in the table

Conditional deletion

Keep a log of things

Reset the identifier column

Foreign key constraint

Delete

Delete conditionally using the where clause

Yes, the data can be recovered.

No

Can be used for tables with foreign key constraints

Truncate table

You can only empty the entire table.

No, the data cannot be recovered

Reset identifier column 0

Truncate Table executes faster and is used to empty the big data scale.

Make sure the data can be deleted before executing the Truncate Table

SQL and T-SQL

SQL (structured query language)

Standard language of Relational Database

SQL Server 、 Oracle 、 DB2

Non-procedural language

There is no need to specify the method of storing the data

A unified language

Create, modify, and delete data objects (databases, tables, etc.)

T-SQL

An enhanced version of SQL that provides basic functions similar to programming languages

Variable description, process control, function

The composition of T-SQL

DML: data manipulation language

Query, insert, delete, and modify data

SELECT 、 INSERT 、 UPDATE 、 DELETE

DDL: data definition language

Create databases, database objects, and define their columns

CREATE 、 ALTER 、 DROP

DCL: data control language

Control access permissions, access permissions, etc., of database components

GRANT 、 REVOKE

SELECT grammatical structure

SELECT select_list

[INTO new_table_name]

FROM table_name

[WHERE search_conditions]

[GROUP BY group_by_expression] [HAVING search_conditions]

[ORDER BY order_expression [ASC | DESC]]

Parameter interpretation

SELECT clause: specify the content of the query

INTO clause: store the query results in a new table

FROM clause: specify query source

WHERE clause: query condition

GROUP BY clause: specifies the grouping conditions for query results

HAVING clause: specifies grouping search criteria, used with the GROUP BY clause

ORDER BY clause: specifies how query results are sorted

Expression.

Conditional expression

Constant: a symbol that represents a single specified data value

Letters, numbers, or symbols.

Column name: the name of the column in the table

Unary operator: an operator with only one Operand

"+" represents a positive number and "-" represents a negative number.

Binary operator: an operator that combines two operands to perform an operation

Arithmetic operator, bit operator, logical operator, comparison operator

Operator meaning

= equal to

> greater than

= greater than or equal to

20, which satisfies a premium value greater than 20

PRICE 20, which satisfies a premium value that is not equal to 20

PRICE Between 10 And 20, which satisfies a premium value greater than or equal to 10 and less than or equal to 20

NAME Like'Li%', which satisfies all the first names surnamed Li in the NAME name field

Logical expression

Connect the conditions with logical operators, and the result is a logical value.

TRUE or FALSE

Logical operator

Operator meaning

AND combines two conditions, and the value is True when both conditions are True.

OR combines two conditions, and the value is True when one of the two conditions is True

NOT is used with other operators to reverse the operation.

Examples of logical expressions

The method of payment is credit card, and can only be peony card, golden spike card or dragon card.

Payment method = 'credit card' AND credit card in ('peony card', 'Jinsui card', 'dragon card')

Query example-query column

Query all columns in the table

SELECT * FROM table_name

Query all student information in the student table

SELECT * FROM student

Query specific columns in the table

SELECT column_name_1,column_name_2,... FROM table_name

Query the contents of name, class and score column in student table

SELECT name, class, grade FROM student

Query example-query specific lines

Grammatical structure

SELECT select_list FROM table_name WHERE search_conditions

Query the student named Liu Ting in Class 2 in the student table.

SELECT * FROM student WHERE name = 'Liu Ting' AND class = 2

Query all students with scores from 90 to 100 in the student table

SELECT * FROM student WHERE score BETWEEN 90 AND 100

Query all students surnamed Liu in the student table

SELECT * FROM student WHERE name LIKE 'Liu%'

Query all students whose comments are not empty in the student table

SELECT * FROM student WHERE remarks is NOT NULL

Query example-the query returns a limited number of rows

Grammatical structure

SELECT TOP n select_list FROM table_name

Parameter interpretation

The TOP keyword is used to limit the number of rows returned by the query. N is the number of rows to be returned.

Query the data of the first five rows in the student table

SELECT top 5 * FROM student

Query example-change the column name of the query result set

Grammatical structure

SELECT column_name AS column_alias FROM table_name

Parameter interpretation

The AS clause is used to change the name of the result set column

Query the two columns of name and × × in the student table

SELECT name AS name, × × number as idcard FROM student

Query example-- sorting of query results

Grammatical structure

SELECT select_list FROM table_name ORDER BY column_name [ASC | DESC]

Parameter interpretation

ASC: ascending order

DESC: descending order

Query the information of middle school students in student table, and display the results according to the scores from high to low.

SELECT * FROM student order by score DESC

Query example-aggregate function

Used to perform calculations on a set of values and return a single value

Such as summation, average, maximum or minimum, etc.

SUM: summation

Query the total scores of all students in the student table

SELECT SUM (grade) total score FROM student

AVG: find the average

Query the average scores of all students in the student table

SELECT AVG grade point averages FROM student MAX and MIN: return maximum and minimum

Query the highest and lowest scores of all students in the student table

The highest score of SELECT MAX and the lowest score of FROM student of MIN

COUNT: returns a count of non-null values

Query the number of rows in the student table

SELECT COUNT (*) Total number of rows FROM student

Query example-grouping query

Grammatical structure

SELECT select_list FROM table_name GROUP BY column_name

HAVING search_conditions

Parameter interpretation

The GROUP BY clause groups the query results

The HAVING clause specifies the grouping search criteria

Query the total scores of each class in the student table

SELECT class, SUM (grade) AS total score FROM student GROUP BY class

Query the classes with a total score of more than 200 in the student table

SELECT class, SUM (grade) AS total score FROM student GROUP BY class HAVING SUM (grade) > 200

The difference between WHERE clause and HAVING clause

The WHERE clause filters the data before grouping, and the condition cannot contain aggregate functions

The HAVING clause filters data after grouping, and the condition often contains aggregate functions

Query example-insert data

Grammatical structure

SELECT select_list INTO new_table_name FROM table_name

Parameter interpretation

The INTO clause inserts data from one table into another table after filtering

Insert data from the student table into the new table student_bak

SELECT * INTO student_bak FROM student

Save the student information of Class 2 in student table to table student_ 2.

SELECT * INTO student_2 FROM student WHERE class = 2

The connection type of the table

Internal connection (INNER JOIN)

Returns only rows that match between two data sets

External connection

Left external connection (LEFT JOIN)

The result set includes all rows of the left table

Right external connection (RIGHT JOIN)

The result set includes all rows of the right table

Full external connection (FULL JOIN)

Returns all rows in the left and right tables

Cross connection

Returns all rows that are connected one by one from the left table to the right table

Internal connection

Method 1: specify the connection condition in the Where clause

SELECT A.name name A Magi A. school A Magi B. name B B Magi B. job occupation B FROM A Magi B WHERE A.name=B.name

Method 2: use INNER JOIN in the FROM clause. ON clause

SELECT A.name name A Magi A. school A Magi B.name name B Magi B. job Professional B FROM An inner join B on A.name=B.name

Left outer connection and right outer connection

Left outer join query

SELECT A.name name A _ Magi A. school A _ Magi B. name name B _ Department job occupation B FROM A left join B on A.name=B.name

Right outer join query

SELECT A.name name A _ Magi A. school A _ Magi B. name name B _ Department job occupation B FROM A right join B on A.name=B.name

Full outer join query

SELECT A.name name A _ Magi A. school A _ Magi B. name name B _ Department job occupation B FROM A full join B on A.name=B.name

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.