The article directories

• DQL language learning
• Step 1: Basic query
• • 1. Query a single field in the table
  • 2. Query multiple fields in the table
  • 3. Query all fields in the table
  • 4. Query constant values
  • 5. Query the expression
  • 6. Query functions
  • 7. Names
  • • Method 1: Use AS
    • Method 2: Use Spaces
    • Example: Query salary, display the result as out put, special symbol, alias in double quotes
  • 8. To heavy
  • • Example: Query all department numbers involved in the employee table, but some department numbers are duplicate
  • 9. The function of the + sign
  • • Example: Query employee first name and last name join into one field and display as first name

• The following uses the figure database as an example to write a query case

DQL language learning

Step 1: Basic query

Grammar:selectQuery listfromThe name of the table. Something like: system.out.println (to print something);Copy the code

USE myemployees;
Copy the code

1. Query a single field in the table

select last_name 
from employees;
Copy the code

2. Query multiple fields in the table

select last_name,salary,email 
from employees;
Copy the code

3. Query all fields in the table

• A:

select
    `employee_id`,
    `first_name`,
    `last_name`,
    `phone_number`,
    `last_name`,
    `job_id`,
    `phone_number`,
    `job_id`,
    `salary`,
    `commission_pct`,
    `manager_id`,
    `department_id`,
    `hiredate` 
from employees;
Copy the code

• Method 2:

select * from employees;
Copy the code

4. Query constant values

select 100;
select 'mike';
Copy the code

5. Query the expression

select 100*98;
select 100%98;
Copy the code

6. Query functions

select VERSION();
Copy the code

7. Names

• ① Easy to understand
• ② If the fields to be queried have the same name, use an alias to distinguish them

Method 1: Use AS

SELECT 100%98 ASResults;SELECT last_name ASSurname, first_nameAS 名 FROM employees;
Copy the code

Method 2: Use Spaces

SELECTLast_name, first_nameFROM employees;
Copy the code

Example: Query salary, display the result as out put, special symbol, alias in double quotes

SELECT salary AS "out put" FROM employees;
Copy the code

8. To heavy

Example: Query all department numbers involved in the employee table, but some department numbers are duplicate

SELECT DISTINCT department_id FROM employees;
Copy the code

9. The function of the + sign

In Java+Mysql > select * from mysql.mysql > select * from mysql.mysql > select * from mysql.mysql+Number: has only one function: operatorselect 100+90; If both operands are numeric, the result of addition is:190
select '123'+90; As long as one of the parties is a character, try to convert a character value to a numeric value. If the conversion succeeds, continue with the result of addition:213
select 'john'+90; If the conversion fails, the character value is converted to0Results:90

select null+10; As long as one of them isnull, then the result must benullResults:null
Copy the code

Example: Query employee first name and last name join into one field and display as first name

SELECT CONCAT('a'.'b'.'c') ASResults;Copy the code

SELECT  CONCAT(last_name,first_name) ASThe nameFROM employees;
Copy the code