01_mysql dql_ Basic Query

Source: Internet
Author: User
Tags aliases

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.