[MySQL] 2.DQL language - query

Language classification of SQL

DQL (data query language): Data Query Language
select
DML (data manipulation language): Data Manipulation Language
insert ,update,delete
DDL (data definition language): Data Definition Language
create,drop,alter
TCL (Transaction Control Language): Transaction Control Language
commit,rollback

DQL language

1, Basic query

Advanced 1: basic query

Syntax:

select query list from table name;
Similar to: system. Out. Println (printing things);

Execution sequence:

① from clause
② select clause

characteristic:

1. The query list can be fields, constant values, expressions and functions in the table
2. The result of the query is a virtual table

#1. Single field in query table
SELECT last_name FROM employees;

#2. Query multiple fields in the table
SELECT last_name,salary,email FROM employees;

#3. Query all fields in the table

#Method 1: F12 alignment
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 ;
    
#Mode 2:  
 SELECT * FROM employees;
 
#4. Query constant value
 SELECT 100;
 SELECT 'john';
 
#5. Query expression
 SELECT 100%98;
 
#6. Query function (call the function to get the return value)
 SELECT DATABASE();
 SELECT VERSION();
 
#7. Alias
 /*
 ①Easy to understand
 ②If the fields to be queried have duplicate names, aliases can be used to distinguish them
 */
 
 #Method 1: use as
SELECT 100%98 AS result;
SELECT last_name AS lastname,first_name AS name FROM employees;

#Method 2: use spaces
SELECT last_name lastname,first_name name FROM employees;

#Case: query salary, and the display result is out put
SELECT salary AS "out put" FROM employees;

#8.DISTINCT weight removal
#Case: query all department numbers involved in the employee table
SELECT DISTINCT department_id FROM employees;

#9. + function
/*
Function: add
select Value + value; Direct operation
select Character + value; First, try to convert characters into numerical values. If the conversion is successful, continue the operation; Otherwise, it is converted to 0 and then calculated
select null+Value; The results are null

java + sign in:
①Operator, both operands are numeric
②Connector, as long as one operand is a string

mysql + sign in:
There is only one function: operator

select 100+90; If both operands are numeric, add
select '123'+90;As long as one of them is character type, try to convert character type data to numeric type
			If the conversion is successful, continue the addition operation
select 'john'+90;--->90	If the conversion fails, the character value is converted to 0
select null+10;---->null As long as one of them is null, the result must be null

*/

#10. View the structure of the table
DESC employees;
SHOW COLUMNS FROM employees;

#Case: query the employee's first name and last name, connect them into a field, and display them as names
SELECT CONCAT('a','b','c') AS result;

SELECT CONCAT(last_name,first_name) AS "full name" 
FROM employees;
10,[[supplementary] concat function
 Function: splicing characters
select concat(Character 1, character 2, character 3,...);

11,[[supplementary] ifnull(Expression 1, expression 2)
Expression 1: may be null Field or expression for
 Expression 2: if expression 1 is null,The value displayed in the final result
 Function: if expression 1 is null,Expression 2 is displayed, otherwise expression 1 is displayed
#Case query employee table
select ifnull(commission_pct,0) from employees;


12,[[supplementary] isnull function
 Function: judge whether a field or expression is null,If yes, it returns 1, otherwise it returns 0

2, Condition query

Advanced 2: condition query

Syntax:

select Query list
from Table name
where Screening conditions;

Execution sequence:

① from clause
② where clause
③ select clause

Classification:

1, Filter by conditional expression

Simple conditional operator: > < =! = < > =<=

#Case 1: query employee information with salary > 12000

SELECT *
FROM employees
WHERE salary>12000;
	
	
#Case 2: query the employee name and department number whose department number is not equal to 90
SELECT 
	last_name,
	department_id
FROM employees
WHERE department_id<>90;
2, Filter by logical expression

Logical operator: and or not
Action: used to join conditional expressions
Can be used: & & ||||! But not recommended

  • &&And and: both conditions are true, and the result is true; otherwise, it is false
  • ||Or or: as long as one condition is true, the result is true, otherwise it is false
  • ! Or not: if the connection condition itself is false, the result is true; otherwise, it is false
#Case 1: query employee name, salary and bonus with salary z between 10000 and 20000
SELECT
	last_name,
	salary,
	commission_pct
FROM employees
WHERE salary>=10000 AND salary<=20000;

#Case 2: query the information of employees whose department number is not between 90 and 110 or whose salary is higher than 15000
SELECT * FROM employees
WHERE NOT(department_id>=90 AND  department_id<=110) OR salary>15000;
Three, fuzzy query

like
between and
in
is null | is not null

1.like

Features: generally used with wildcards
Wildcard:

  • %Any number of characters, including 0 characters, supporting 0-more characters
  • _ Any single character
#Case 1: query employee information with character a in employee name

SELECT *
FROM employees
WHERE last_name LIKE '%a%';   #abc

#Case 2: query the employee name and salary with the third character e and the fifth character a in the employee name
SELECT
	last_name,
	salary
FROM
	employees
WHERE
	last_name LIKE '_ _e_a%';


#Case 3: the second character in the employee name is_ Employee name

SELECT
	last_name
FROM
	employees
WHERE
	last_name LIKE '_$_%' ESCAPE '$'; # "_\_%"
	
	
#Case 4: query employee information whose last character is e in employee name
SELECT *
FROM employees
WHERE last_name LIKE '%e';

#Case 5: query employee information whose first character is e in employee name
SELECT *
FROM employees
WHERE last_name LIKE 'e%';

#Case 6: query employee information whose third character is e in employee name
SELECT *
FROM employees
WHERE last_name LIKE '_ _ _ e%';
2.between and

① Using between and can improve the brevity of statements
② Including critical value
③ Do not change the order of the two critical values

#Case 1: query employee information with employee number between 100 and 120

#-----Mode 1
SELECT *
FROM employees
WHERE employee_id >= 100 AND employee_id<=120;
#------Mode II
SELECT *
FROM employees
WHERE employee_id BETWEEN 100 AND 200;

#Case 2: query the name, salary and annual salary of employees whose annual salary is not between 10000-20000
SELECT `last_name`,`salary`,`salary`*12*(1+IFNULL(`commission_pct`,0)) Annual salary
FROM ``employees``
WHERE `salary`*12*(1+IFNULL(`commission_pct`,0)) < 10000 OR `salary`*12*(1+IFNULL(`commission_pct`,0)) > 20000;

SELECT `last_name`,`salary`,`salary`*12*(1+IFNULL(`commission_pct`,0)) Annual salary
FROM `employees`
WHERE `salary`*12*(1+IFNULL(`commission_pct`,0)) NOT BETWEEN 100000 AND 200000;
3.in

Meaning: judge (query) whether the value of a field belongs to an item in the in (specified) list

  • A in (constant value 1, constant value 2, constant value 3,...)
  • A not in (constant value 1, constant value 2, constant value 3,...)

characteristic:
① Using in to improve statement conciseness
② The value types of the in list must be consistent or compatible
③ Wildcards are not supported in the in list

#Case: the job number of the employee queried is IT_PROG,AD_VP,AD_ An employee name and job number in Pres

#Logical expression - mode 1:
SELECT
	last_name,
	job_id
FROM
	employees
WHERE
	job_id = 'IT_PROT' OR job_id = 'AD_VP' OR JOB_ID ='AD_PRES';


#-------------Mode 2: (concise)

SELECT
	last_name,
	job_id
FROM
	employees
WHERE
	job_id IN( 'IT_PROT' ,'AD_VP','AD_PRES');
4,is null /is not null
  • =Or < > cannot be used to determine null values

  • is null or is not null determines the null value

#Case 1: query employee name and bonus rate without bonus
SELECT last_name,commission_pct
FROM employees
WHERE commission_pct IS NULL;


#Case 2: query employee name and bonus rate with bonus
SELECT last_name,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;

#----------The following is ×
SELECT last_name,commission_pct
FROM employees
WHERE salary IS 12000;
	
	
#Safety equals ----- < = >

#Case 1: query employee name and bonus rate without bonus
SELECT last_name,commission_pct
FROM employees
WHERE commission_pct <=>NULL;
	
#Case 2: query employee information with salary of 12000
SELECT last_name,salary
FROM employees
WHERE salary <=> 12000;
is null pk <=>

IS NULL: you can only judge the NULL value, which is highly readable. It is recommended to use
< = >: it can judge both NULL value and ordinary value, with low readability


3, Sort query

Advanced 3: sort query

Syntax:

select query list
from table name
[where screening criteria]
order by sorted field or expression (sorted list);

Execution sequence:

① from
② where
③ select
④ order by

characteristic:

  • asc represents ascending order and can be omitted; desc represents descending order
  • The order by clause can support a single field, alias, expression, function, multiple fields
  • The order by clause is at the end of the query statement, except for the limit clause
1. Sort by single field
#In case 1, the employee information with employee number > 120 is used to increase the salary
SELECT * 
FROM employees
WHERE `employee_id` > 120 
ORDER BY salary ASC;

#In case 2, the employee information with employee No. > 120 is sorted in descending order
SELECT * 
FROM employees
WHERE `employee_id` > 120 
ORDER BY salary DESC;
2. Add filter criteria and then sort
#Case: query the employee information with department No. > = 90 in descending order by employee No

SELECT *
FROM employees
WHERE department_id>=90
ORDER BY employee_id DESC;
3. Sort by expression
#Case: for employees with bonuses, the annual salary is in descending order

SELECT *,salary*12*(1+IFNULL(commission_pct,0)) Annual salary
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
4. Sort by alias
#Case: query employee information in ascending order of annual salary

SELECT *,salary*12*(1+IFNULL(commission_pct,0)) Annual salary
FROM employees
ORDER BY Annual salary ASC;
5. Sort by result of function
#Case: query employee names in descending order by name length

SELECT LENGTH(last_name),last_name 
FROM employees
ORDER BY LENGTH(last_name) DESC;

SELECT last_name 
FROM employees
ORDER BY LENGTH(last_name) DESC;
6. Sort by multiple fields
#Case: when querying employee information, it is required to first press salary descending order, and then press employee_id ascending order
SELECT *
FROM employees
ORDER BY salary DESC,employee_id ASC;
7. Sort by the number of columns (less used)
SELECT *
FROM `employees`
ORDER BY 2 DESC;

SELECT *
FROM `employees`
ORDER BY `first_name` DESC;

Tags: Database MySQL

Posted on Wed, 22 Sep 2021 11:48:03 -0400 by pluto