01_mysql dql_ Basic Query

Dql,data query Language, data query

# Basic Query
/*
Grammar:
Select query list from table name;

Characteristics:
1. The query list can be: A field in a table, a constant value, an expression, a function, or multiple combinations
2. The query result is 1 virtual tables for viewing
*/
Use MyEmployees;

#1. Querying a single field in a table
SELECT last_name from Employees;

#2. Querying multiple fields in a table
SELECT Last_name,salary,email from Employees;

#3. Querying all fields in a table
SELECT * FROM Employees;

#4. Query constant values, field names also displayed as constant values (no eggs)
SELECT 100;
SELECT ' Zhangjin ';
Note: Character and date constants must use single quotes, and numeric types do not require

#5. Query expression: Field name is an expression and field value is the result of the calculation
SELECT 100*98;
SELECT 100%98;
Note: complex operations, + +--not supported

#6. query function: Field name is function (), field value is function execution result
SELECT VERSION ();
SELECT DATABASE ();
Note: Select must write, or select to trigger function execution
The function in MySQL must have a return value

#7. Field aliases:
/*
1) field names in query results are displayed as aliases for easy identification
2) Simplified field names that are too long
3) distinguish the names of the fields, using aliases (multi-table query)
*/

#方式一: Using as
SELECT VERSION () as sqlversion;
SELECT Last_Name as surname, first_name as name from employees;

#方式二: Omit as, use spaces
SELECT last_name Surname, first_name name from employees;

#案例: Query salary, show results as out put
#别名中有特殊字符 (Space, #), use ""
Select salary as "out put" from employees;

#8. Go Heavy
#案例: Query All department numbers involved in the employee table
SELECT DISTINCT department_id from employees;
Note: Multi-field de-emphasis is not supported because there are irregular conditions in the table after multiple fields are gone (a,10 line, b 20 line)

The role of the #9. + number
/*
+ in Java: Connection string, numeric operation
MySQL +: Only numeric operations (attempts to convert characters to numbers, and then add; conversion fails, then the character is converted to 0)
As long as a party is null, the + result is also null

Select null+123;
*/

#案例: Query employee and last name, connect as 1 fields, and display as name
#字符拼接函数: concat (str1,str2,...)
SELECT CONCAT (last_name,first_name) as name from employees;

#10 replace NULL

# Replace the null commission_pct with 0
SELECT ifnull (commission_pct,0) as commission from employees;

# Use the replaced commission_pct for the result stitching (otherwise the concat result is also null)
Select CONCAT (employee_id, ', ', last_name, ', ', Ifnull (commission_pct,0)) as "Out_put"
From employees;

#11 determine null
IsNull (commission_pct)
Determines whether a field is null, returns 1, otherwise returns 0

01_mysql dql_ Basic Query