SQL Basic Learning _02_ Query

SELECT statement

1. Select statement Query column (field):

? ? SELECT < column name >
? ? From < table name >;

? ? The statement uses two SQL clauses, the SELECT clause lists the names of the columns that you want to query from the table, and the FROM clause specifies the name of the table for the selected data;

? ? Such as:

? ? SELECT shohin_id, Shohin_mei, Shiire_tanka
? ? From Shohin;?

? ? SELECT *
? ? From Shohin

? ? such as

2. Select statement Query row (record):

? ? SELECT < column name >
? ? From < table name >
? ? WHERE < conditional expressions >;

? ? Note that the WHERE clause must immediately follow the FROM clause, otherwise an error occurs;

? ? The WHERE clause specifies the criteria that the selected record satisfies, such as:

? ? SELECT shohin_id, Shohin_mei, Shiire_tanka
? ? From Shohin
? ? WHERE Shiire_tanka = 320;

3. As keyword set alias:

? ? Use as to display the alias of the column:

? ? SELECT < column names > as < aliases >

? ? Use as to display a column constant:

? ? SELECT < constants >? As < column names >

? ? The above two as keywords can not be used in sockets;

4. Distinct keyword does not display duplicate lines:

? ? SELECT DISTINCT < column names >

? ? The DISTINCT keyword can only be used before the first keyword, and null is also treated as a class of data

Arithmetic operations and comparison operations

1. Using arithmetic operations in SELECT statements

? ? Such as:

? ? Select Shohin_mei,hanbai_tanka,hanbai_tanka * 2 as "hanbai_tanka_x2"
? ? From Shohin;

? ? Arithmetic characters can be used;

? ? Any operation that contains null, the result is null, including null/0, and no error, and the result is null;

2. Use comparison operators in the WHERE clause

? ? When you use an expression in a WHERE clause, you need to use a variety of comparison operators, such as:

? ? = ? ? ? ? ? ? ? ? Equal

? ? <>??????? Not equal

? ? >=??????? Greater than or equal

? ? >???????? Greater than

? ? <=??????? Less than or equal

? ? <???????? Less than

? ? You can also combine arithmetic and comparison operations in the WHERE clause, such as:

? ? SELECT Shohin_mei, Hanbai_tanka, Shiire_tanka
? ? From Shohin
? ? WHERE Hanbai_tanka-shiire_tanka >= 500;?

? ? When comparing strings, note that comparisons are made by regular string comparison methods (in sequence, in order of coding), especially when using strings to represent digital content.

? ? Null cannot use any comparison operation to determine if NULL should be NULL, is not NULL

3. Logical operators

? ? logical operators supported by SQL: And, or, not

? ? Where the calculation priority is: not > and > OR, the use of parentheses can change its priority;

? ? Using logical operators, you can combine multiple conditional expressions;

? ? The value of NULL in the logical operation is: UNKNOWN, where null is present, the logical operation used is three-value logic,

? ? null and TRUE = NULL
? ? NULL and false = False
? ? NULL and NULL = NULL

? ? NULL OR true = True
? ? Null OR FALSE = NULL
? ? NULL and NULL = NULL

Aggregate queries

1. Aggregation functions:

? ? Count: Calculates the number of records in the table;

? ? Sum: Calculates the sum of the data in a column of values in a table;

? ? Avg: Calculates the average value of a column in a table;

? ? Max: Calculates the maximum data value for any column in the table;

? ? Min: Calculates the minimum value of the data in any column in the table;

? ? Use parentheses (< column name >) to invoke these functions:

? ? SELECT COUNT (*)
? ? From Shohin;

? ? In the Count function, if you use *, NULL is also included in the calculation, and if you use a column name, NULL in the column is excluded;

? ? In the SUM function, if NULL is present in the column, it is ignored directly in the calculation, not the result is null;

? ? In the AVG function, if there is null in the column, the row is ignored when evaluated;

? ? Max and Min can be applied to any type of data, including integers, dates, strings, and so on;

2. Using the DISDINCT keyword in an aggregate function

? ? If you want to remove duplicate rows in an aggregate function, you should add the DISDINCT keyword before the column name (that is, within the parentheses of the function)

3. Attention Points

? ? An aggregate function can only be used in a SELECT clause, and an aggregate function cannot be used in a WHERE clause

4. Group the aggregated results:

? ? Use the GROUP BY clause to group tables:

? ? SELECT < column name 1>,< column name 2>,< column name 3>
? ? From < table name >
? ? GROUP by < column name 1>, < column name 2>,< column name 3>,...;

? ? Such as:

? ? SELECT Shohin_bunrui, COUNT (*)
? ? From Shohin
? ? GROUP by Shohin_bunrui;?

? ? Null will be displayed in the result of the grouping.

? ? If there is a WHERE clause, the GROUP by clause should be after the WHERE clause;

? ? The alias defined in the SELECT clause cannot be used in a GROUP by clause;

5. Specify the conditions for the aggregation result:

? ? Use the HAVING clause to specify the conditions for grouping:

? ? SELECT < column name 1>,< column name 2>,< column name 3>,...
? ? From < table name >
? ? GROUP by < column name 1>,< column name 2>,< column name 3>,...
? ? Having < grouping results corresponding conditions >;

? ? Such as:

? ? SELECT Shohin_bunrui,? COUNT (*)
? ? From Shohin
? ? GROUP by Shohin_bunrui
? ? Having COUNT (*) = 2;

? ? Having is used to specify a condition for a group, where (although a similar effect can be achieved with having) if the condition of the specified row corresponds

6. Sort the results of a query

? ? Use the ORDER BY clause to sort the results after the query:

? ?? SELECT < column name 1>,< column name 2>,< column name 3>,...
? ? From < table name >
? ? Order BY < Sort datum column 1>,< sort datum column 2>,...

? ? The default is ascending sort, and to use descending sorting, you should add the DESC keyword at the end of the ORDER BY clause, such as:

? ? SELECT shohin_id, Shohin_mei, Hanbai_tanka, Shiire_tanka
? ? From Shohin
? ? ORDER by Hanbai_tanka DESC;

? ? If the column contains NULL, then NULL is displayed at the beginning or end of the column;

? ? The alias defined in the SELECT clause can be used in the ORDER by clause;

? ? The columns in the ORDER by clause can use columns that exist in the table but do not exist in the SELECT clause;

? ? An ORDER by clause can use an aggregate function;

SQL Basic Learning _02_ Query