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 listfrom
Table namewhere
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 ;