SQL Language Basics

I. SQL statement WRITING rules

The 1.SQL statement is made up of English words, and each English word is a keyword, such as SELECT.
2. Many DBMS require SQL statements to end, multiple SQL statements must be separated by;
3.SQL statements are case insensitive, but the table or column names involved in the statement are case-sensitive, depending on the DBMS used and its associated configuration
Strings in 4.SQL are enclosed in single quotation marks.
5. Partitioning SQL statements into multiple lines is easier to debug
6. Use the--symbol to annotate
Order of 7.SELECT Statements:
SELECT column name from table WHERE condition GROUP by column name having condition ORDER by column name

Second, query and filter

1. Querying all data in a few columns
SELECT column name 1, column name 2,.... from table;
Separate column names with commas

2. Querying data for all columns
SELECT * FROM table

3. Querying a column for non-repeating data
SELECT DISTINCT column name from table;
The DISTINCT keyword is placed in front of the column name and can only be queried in a single row, not multiple columns

4. Query the first few rows of data
SELECT TOP 5 column names from table;
SQL Server and access use the top keyword, which differs from other DBMS
If you do not use ORDER by ordering, the top order depends only on the physical order in the table

5. Querying a few columns of data and sorting
SELECT column name from table ORDER by column name (DESC);
The order by is guaranteed to be the last word of all statements, and the order by default is sorted in a A-Z order, if Z-a order is required, and the DESC keyword is required, if more than one column is in z-a order, each column must be prefixed with the DESC keyword.
When you use order by for multiple columns, column names are separated by commas, such as order by column 1, column 2,......, the sort is not sorted by 1, then 2, but column 1 is ordered when column 2 has the same value, and column 1 is not sorted if column 2 is a unique value. Ascending and descending can be forced to change by setting the DBMS.

6. Search by condition
SELECT column name from table WHERE condition 1 and Condition 2 ....
Conditions are usually set according to operators such as <,>, and different DBMS-supported operators or operators are different in their notation. strings are enclosed in single or double quotation marks. Where the between operator contains all the values between the start and end, is fully inclusive.

You can set more conditions by using logical operators.

If possible, use the in operator instead of or, because in is faster and better suited to building dynamic queries than or.

7. Null value query
Null NULL means that the record does not contain any values, unlike 0, spaces, and empty strings, so you cannot use where = null to query, but to use the IS NULL clause.

8. Fuzzy Query
With wildcard characters for fuzzy queries, wildcards must be used with the LIKE operator, and wildcards can be used only for text characters.

9. Use of aggregate functions
AVG (), COUNT (), MAX (), AIN (), SUM (), the column to query after select cannot contain both the use aggregate function and the column that does not use an aggregate function.

Third, data collation

1. Create an alias with the AS keyword
SELECT column name as alias from table

2. Grouping Query Results
SELECT column name from table guoup by column name
Each column in the GROUP by sentence must be a retrieval column or a valid expression, not an aggregate function, and cannot use an alias
Column order in the GROUP by clause does not affect query results
If the grouping column contains null, NULL is separated as a set of
The GROUP by sentence must precede the WHERE clause, before the ORDER by clause

3. GROUP BY conditional query
SELECT column name from table GROUP by column name having condition

The difference between where and having
Where the filter is for an entire column, the filter result is the entire record in the source data, having the filter for grouping, and the filter result is the entire record of the data after grouping.
Where is filtered before grouping, having is grouped after filtering

Iv. junction tables and subqueries
1. The SELECT statement as a subquery can only query a single column, and querying multiple columns will return an error

2. To query for results that are not associated, use an outer join.

3. Two queries that use union connections must contain the same query columns

4. Sort the results of the query for the Union connection, using the ORDER BY clause only for the last SELECT statement.

SQL Language Basics