MySQL Database learning 2(SQLyog)
Please indicate the source of the article
The documents required for this section :
link :https://pan.baidu.com/s/18ZmrHK8nzvG39y7rLz7d0w
Extraction code :2lxu
The documents are all from B Stand still Silicon Valley related learning video , This section of the document is opened by the dolphin
One 、DQL Language learning ( Two )
Learning content :
Other knowledge points
(1) Query the structure of a known table , Use keywords desc
give an example :
DESC employees;
Running results :
Basic query ( Make up a section of knowledge )
# Splicing function concat( Late learning function , I'll also talk about )
(1) and c++ Language is similar to , The key words are used in splicing concat
give an example :
SELECT CONCAT ('a','b','c') AS result ;
SELECT CONCAT (last_name,first_name) AS full name FROM employees;
Running results :
Conditions of the query
grammar :
select
Query list
from
Table name
where
filter
classification :
One 、 Filter by conditional expression
Conditional expression operators :
< 、>、 =、 !=( It's not equal to )、 <>(MySQL Is not equal to )、 >=、 <=
# Case a
Query salary greater than 12000 All employee information for
SELECT
*
FROM
employees
WHERE salary > 12000 ;
Running results :
# Case 2
Query department number is not equal to 90 The employee's name and department number
SELECT
last_name,
department_id
FROM
employees
WHERE department_id <> 90 ;
Running results :
Two 、 Filter by logical expression
Logical operators :
&&、 ||、 !( The same applies )
and 、or 、not( yes MySQL Standard format in )
# Case a
Check salary at 10000 To 20000 Between the employee's name 、 Salary and bonus
SELECT
last_name,
salary,
commission_pct
FROM
employees
WHERE salary >= 10000
AND salary <= 20000 ;
Running results :
# Case 2
The inquiry department number is not in 90 To 110 Between , Or pay more than 15000 And employee information .
SELECT
*
FROM
employees
WHERE NOT (
department_id >= 90
AND department_id <= 110
)
OR salary > 15000 ;
Running results :
3、 ... and 、 Fuzzy screening
like、between and 、in 、is null 、is not null
#1.like
characteristic :
(1) Can be used with wildcards
% Any number of characters
_ Any single character
# Case a
Query employee name contains characters a Employee information
SELECT
*
FROM
employees
WHERE last_name LIKE '%a%' ;
Be careful : Characters must be in single quotation marks , And contains a There may be characters before and after the description , So you have to have %
Running results :
# Case 2
The third character in the query employee name is e, The fifth character is a The name and salary of the employee
SELECT
last_name,
salary
FROM
employees
WHERE '__e_a%' ;
# Case three
The second character in the query employee name is _ Employee name of
SELECT
last_name
FROM
employees
WHERE last_name LIKE '_\_%' ;
Be careful :_ As a wildcard , have access to \ Translate
Running results :
#2.between and
characteristic :
(1) Contains a critical value
(2)and The values at both ends cannot be exchanged
# Case a
Query the employee number in 100 To 120 Employee information between
SELECT
*
FROM
employees
WHERE employee_id BETWEEN 100
AND 120 ;
Running results :
#3.in
# Case a
The job number of the employee is IT_PROG、AD_VP、AD_PRES An employee's name and job number in the
SELECT
last_name,
job_id
FROM
employees
WHERE job_id IN ('IT_PROG', 'AD_VP', 'AD_PRES') ;
Running results :
#4.is null
# Case a
Query the employee name and bonus rate without bonus
SELECT
last_name,
commission_pct
FROM
employees
WHERE commission_pct IS NULL ;
Running results :
Be careful :Tip:<=> For safety, it is equal to the symbol , You can judge null Value can be used to judge the normal value , Equivalent to an equal sign, but less readable .
# Test questions
#1. There is no bonus for the query , And the salary is less than 18000 Salary and employee name
SELECT
salary,
last_name
FROM
employees
WHERE commission_pct IS NULL
AND salary < 18000 ;
#2. Inquire about employees In the table , The job number is not ’IT’ Or the salary is 12000 Employee information
SELECT
*
FROM
employees
WHERE job_id <> 'IT'
OR salary = 12000 ;
#3. Check out the Department departments The structure of the table
DESC departments;
#4. Inquiry Department departments What position numbers are involved in the table
SELECT DISTINCT
location_id
FROM
departments ;