Review the basics of SQL

//NOTE: Here are some of the more frequently used SQL statement syntax to query and use later

SQL statements consist of commands, clauses, operators, and statistical functions. I. Order

1. Data definition commands: Create: Create new data tables, fields, and index tables; drop: Delete data tables or indexes from the database; ALTER: Add or modify field properties;

2. Data manipulation command: SELECT: Find the record that satisfies the condition; INSERT: Add record or merge two data tables; update: Updates the record that satisfies the condition; Delete: Delete the record that satisfies the condition;

Two. Clauses

From: Specifies the data table; WHERE: Set conditions; GROUP BY: Sets the grouping; having: sets the group condition; ORDER by: Sets the conditions for the output;

Three. Operators

1. Logical operators: and (with), or (or), not (non), 2. Relational operators: <, <=, >, >=, =, <> (not equal to), 3. Other operators: between (set range), like (wildcard settings), in (Collection settings);

Four. Statistical functions

AVG: averaging; count: sum: sum; Max: Find the maximum; min: Find the lowest value;

Five. Common Select, INSERT, UPDATE, DELETE statements

1. Data query Operations A common SELECT statement consists of 6 parts in the syntax form: SELECT [TOP (value)] [all | distinct] < target column expression; *

From table name WHERE Query statement Group by Group field (attribute name) having grouping condition ORDER by field [ASC | DESC] The entire SELECT statement means that, depending on the query condition expression of the WHERE clause, the base table specified from the FROM clause (separated by commas if there are multiple tables) finds the tuple that satisfies the condition and then presses the property name in the SELECT clause (with multiple property names, separated by commas; When the field name comes from a different table, the field prefix should be preceded by the field name, and the attribute values in the tuple are selected to form the result table, and if there is a group clause, the result is grouped by the value of the attribute, which is a group of equal tuples. Aggregate functions are typically used in each group. If the group clause has a HAVING clause, only the groups that meet the specified criteria are output, and if there is an ORDER BY clause, the result table is also arranged in ascending (ASC) or descending (DESC) Order of the value of the field, which is ascending by default.

where *: denotes all fields of the corresponding table; DISTINCT: Removes duplicate rows from the result table;

2. Data insertion operation

(1) Inserting the cell group: INSERT into base table name [(Column name of table)] values (tuple value) (2) Multiple group inserts: INSERT into base table name [(Column name of table)] values (tuple value), (tuple value),.., (tuple value) (3) Check Insert of query Result: INSERT into base table name [(Column name of table)] This statement can interpolate the results of a SELECT statement into a base table.

3. Modifying Data operations

UPDATE base table Name SET Property Name = property value WHERE Query statement

4. Delete Data operations

DELETE from base table name WHERE query statement

Six. Parameterized SQL statements

In the SQL statement, the global variable name begins with @@ 开头 The local variable begins with @. There are two ways to assign a value to a variable, one is to use a SELECT statement, and the other is to use a set statement. Select @varName = Expression or: DECLARE @varName char SET @varName = "Hello world!"

Review the basics of SQL