One 、 Presentation table introduction

1.employees The employee table

Field name	Field meaning
employee_id	Employee number
first_name	name
last_name	surname
email	mailbox
phone_number	Phone number
job_id	Job number
salary	Wages
commission_pct	Bonus rate
manager_id	The number of the superior leader
department_id	Department number
hiredate	Date of entry

2.departments Departmental table

Field name	Field meaning
department_id	Department number
department_name	Department name
manager_id	Department leaders id
location_id	Location number

3.location Location table

Field name	Field meaning
location_id	Location number
street_address	The street
postal_code	On the right
city	It is
state_province	state / province
country_id	Country number

4.jobs Type of work table

Field name	Field meaning
job_id	Job number
job_title	Type of work
min_salary	minimum wage
max_salary	Maximum wage

3、 ... and 、 Basic query

 grammar ：
select  Query list  from  The name of the table ;

1. Query all fields

mysql> use myemployees;
mysql> show tables;
+-----------------------+
| Tables_in_myemployees |
+-----------------------+
| departments           |
| employees             |
| jobs                  |
| locations             |
+-----------------------+
4 rows in set (0.00 sec)

mysql> select * from jobs;
+------------+---------------------------------+------------+------------+
| job_id     | job_title                       | min_salary | max_salary |
+------------+---------------------------------+------------+------------+
| AC_ACCOUNT | Public Accountant               |       4200 |       9000 |
| AC_MGR     | Accounting Manager              |       8200 |      16000 |
| AD_ASST    | Administration Assistant        |       3000 |       6000 |
| AD_PRES    | President                       |      20000 |      40000 |
| AD_VP      | Administration Vice President   |      15000 |      30000 |
| FI_ACCOUNT | Accountant                      |       4200 |       9000 |
| FI_MGR     | Finance Manager                 |       8200 |      16000 |
| HR_REP     | Human Resources Representative  |       4000 |       9000 |
| IT_PROG    | Programmer                      |       4000 |      10000 |
| MK_MAN     | Marketing Manager               |       9000 |      15000 |
| MK_REP     | Marketing Representative        |       4000 |       9000 |
| PR_REP     | Public Relations Representative |       4500 |      10500 |
| PU_CLERK   | Purchasing Clerk                |       2500 |       5500 |
| PU_MAN     | Purchasing Manager              |       8000 |      15000 |
| SA_MAN     | Sales Manager                   |      10000 |      20000 |
| SA_REP     | Sales Representative            |       6000 |      12000 |
| SH_CLERK   | Shipping Clerk                  |       2500 |       5500 |
| ST_CLERK   | Stock Clerk                     |       2000 |       5000 |
| ST_MAN     | Stock Manager                   |       5500 |       8500 |
+------------+---------------------------------+------------+------------+
19 rows in set (0.00 sec)

2. Query a single field

mysql> select job_id from jobs;
+------------+
| job_id     |
+------------+
| AC_ACCOUNT |
| AC_MGR     |
| AD_ASST    |
| AD_PRES    |
| AD_VP      |
| FI_ACCOUNT |
| FI_MGR     |
| HR_REP     |
| IT_PROG    |
| MK_MAN     |
| MK_REP     |
| PR_REP     |
| PU_CLERK   |
| PU_MAN     |
| SA_MAN     |
| SA_REP     |
| SH_CLERK   |
| ST_CLERK   |
| ST_MAN     |
+------------+
19 rows in set (0.00 sec)

3. Query multiple fields

mysql> select job_id,job_title from jobs;
+------------+---------------------------------+
| job_id     | job_title                       |
+------------+---------------------------------+
| AC_ACCOUNT | Public Accountant               |
| AC_MGR     | Accounting Manager              |
| AD_ASST    | Administration Assistant        |
| AD_PRES    | President                       |
| AD_VP      | Administration Vice President   |
| FI_ACCOUNT | Accountant                      |
| FI_MGR     | Finance Manager                 |
| HR_REP     | Human Resources Representative  |
| IT_PROG    | Programmer                      |
| MK_MAN     | Marketing Manager               |
| MK_REP     | Marketing Representative        |
| PR_REP     | Public Relations Representative |
| PU_CLERK   | Purchasing Clerk                |
| PU_MAN     | Purchasing Manager              |
| SA_MAN     | Sales Manager                   |
| SA_REP     | Sales Representative            |
| SH_CLERK   | Shipping Clerk                  |
| ST_CLERK   | Stock Clerk                     |
| ST_MAN     | Stock Manager                   |
+------------+---------------------------------+
19 rows in set (0.00 sec)

4. Query constants

mysql> select 10010;
+-------+
| 10010 |
+-------+
| 10010 |
+-------+
1 row in set (0.00 sec)

mysql> select "hello world";
+-------------+
| hello world |
+-------------+
| hello world |
+-------------+
1 row in set (0.00 sec)

mysql> select 'mysql very good';
+-----------------+
| mysql very good |
+-----------------+
| mysql very good |
+-----------------+
1 row in set (0.00 sec)

5. Query expression

mysql> select 100 / 3;
+---------+
| 100 / 3 |
+---------+
| 33.3333 |
+---------+
1 row in set (0.00 sec)

6. Query function

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.36    |
+-----------+
1 row in set (0.00 sec)

mysql> select database();
+-------------+
| database()  |
+-------------+
| myemployees |
+-------------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-12-20 11:14:42 |
+---------------------+
1 row in set (0.00 sec)

7. names

mysql> select now() as  Time ;
+---------------------+
|  Time                 |
+---------------------+
| 2020-12-20 11:22:30 |
+---------------------+
1 row in set (0.00 sec)

mysql> select version() as " edition ";
+--------+
|  edition    |
+--------+
| 5.6.36 |
+--------+
1 row in set (0.00 sec)

Alias the fields in the table , What's more as You can omit it

mysql> select min_salary as  The minimum wage ,max_salary as  Maximum wage  from jobs;
+--------------+--------------+
|  The minimum wage      |  Maximum wage      |
+--------------+--------------+
|         4200 |         9000 |
|         8200 |        16000 |
|         3000 |         6000 |


mysql> select min_salary   The minimum wage ,max_salary   Maximum wage  from jobs;
+--------------+--------------+
|  The minimum wage      |  Maximum wage      |
+--------------+--------------+
|         4200 |         9000 |
|         8200 |        16000 |
|         3000 |         6000 |
|        20000 |        40000 |

Alias a table , Generally, it is used more in multi table query .

mysql> select * from employees as e;

8. duplicate removal distinct

Query all department numbers involved in the employee table .

mysql> select department_id from employees limit 15;
+---------------+
| department_id |
+---------------+
|          NULL |
|            10 |
|            20 |
|            20 |
|            30 |
|            30 |
|            30 |
|            30 |
|            30 |
|            30 |
|            40 |
|            50 |
|            50 |
|            50 |
|            50 |
+---------------+
15 rows in set (0.00 sec)

The data queried here is duplicate , The way to remove duplicates is to add duplicate keywords in front of fields distinct

mysql> select distinct department_id from employees limit 15;
+---------------+
| department_id |
+---------------+
|          NULL |
|            10 |
|            20 |
|            30 |
|            40 |
|            50 |
|            60 |
|            70 |
|            80 |
|            90 |
|           100 |
|           110 |
+---------------+
12 rows in set (0.00 sec)

Four 、 Conditions of the query

 grammar ：
	select  Query list  from  Table name  where  filter ;

1. Filter by conditional expression

Conditional operation symbols ： > < = != <> >= <=

Example 1： Query salary >12000 Employee information

mysql> mysql> select * from employees where salary > 12000;

Example 2： Query department number is not equal to 90 Employee and department number of

select last_name,department_id from employees where department_id != 90;
 perhaps 
select last_name,department_id from employees where department_id <> 90;

2. Filter by logical expression

Logical operators : && || !
and or not

Logical operator action : Join multiple expressions

Example 1： 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;

3. Fuzzy query

(1)like

Example 1： The query employee name contains characters a Employee information

# "%" Stands for the wildcard , Represents any character of any length .
# "_"  Represents any single character 
select 
	* 
from 
	employees 
where
	last_name like '%a%';

(2)between and

Example ： Query the employee number in 120 To 120 Employee information between

select
	*
from 
	employees
where
	employee_id between 100 and 120;

(3)in

Example 1： 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');

(4)is null

Example 1： Query the employee name and bonus rate without bonus

# stay sql in  "="  Can't judge null Worth , So use is ,is It's cooperation NULL Used 
select
	last_name,
	commission_pct
from
	employees
where
	commission_pct is null;

 conversely 
select
	last_name,
	commission_pct
from
	employees
where
	commission_pct is not null;

5、 ... and 、 Sort query

 grammar ：
select 
	*
from
	 Table name 
where 
	 Query criteria 
	order by  Sort field  [asc | desc];

#asc It's in ascending order  desc It's in descending order   The default is ascending 

Example 1; Query employee's salary Sort from low to high

mysql> select * from employees order by salary limit 10;

6、 ... and 、 Common function

1. Character functions

(1) length（）: Count the byte size of characters

​ If it's an English string , Character length = Size of space occupied by all characters
​ If it's Chinese , It depends on the character set settings , stay UTF-8 One of the Chinese characters occupies 3 Bytes

mysql> select length('hello');
+-----------------+
| length('hello') |
+-----------------+
|               5 |
+-----------------+
1 row in set (0.00 sec)

mysql> select length(' Hello, China ');
+------------------------+
| length(' Hello, China ')     |
+------------------------+
|                     12 |
+------------------------+
1 row in set (0.00 sec)

(2) concat（）: String concatenation

mysql> select concat('hello',' ','world') as ' String splicing result ';
+-----------------------+
|  String splicing result         |
+-----------------------+
| hello world           |
+-----------------------+
1 row in set (0.00 sec)

The same is true for spliced fields

mysql> select concat(last_name,'_',first_name) from employees limit 3;
+----------------------------------+
| concat(last_name,'_',first_name) |
+----------------------------------+
| K_ing_Steven                     |
| Kochhar_Neena                    |
| De Haan_Lex                      |
+----------------------------------+
3 rows in set (0.00 sec)

(3)upper（）: Convert to uppercase

mysql> select upper('hello');
+----------------+
| upper('hello') |
+----------------+
| HELLO          |
+----------------+
1 row in set (0.00 sec)

(4)lower（）: Convert to lowercase

mysql> select  lower('HELLO World');
+----------------------+
| lower('HELLO World') |
+----------------------+
| hello world          |
+----------------------+
1 row in set (0.00 sec)

(5)substr（）: String interception

​ substr( data , Start index , The length truncated backward )
​ Be careful ： The index is from 1 Start with

mysql> select phone_number from employees limit 3;
+--------------+
| phone_number |
+--------------+
| 515.123.4567 |
| 515.123.4568 |
| 515.123.4569 |
+--------------+
3 rows in set (0.00 sec)

mysql> select substr(phone_number,5) from employees limit 3;
+------------------------+
| substr(phone_number,5) |
+------------------------+
| 123.4567               |
| 123.4568               |
| 123.4569               |
+------------------------+
3 rows in set (0.00 sec)

mysql> select substr(phone_number,5,3) from employees limit 3;
+--------------------------+
| substr(phone_number,5,3) |
+--------------------------+
| 123                      |
| 123                      |
| 123                      |
+--------------------------+
3 rows in set (0.00 sec)

(6)instr()： Take out the index of the string

​ If we can't find a way back 0

mysql> select instr(' Zhang Wuji loves Zhou Zhiruo ',' Zhang ') as ret;
+-----+
| ret |
+-----+
|   1 |
+-----+
1 row in set (0.00 sec)

mysql> select instr(' Zhang Wuji loves Zhou Zhiruo ',' Zhou Zhiruo ') as ret;
+-----+
| ret |
+-----+
|   5 |
+-----+
1 row in set (0.00 sec)

mysql> select instr(' Zhang Wuji loves Zhou Zhiruo ',' Small ') as ret;
+-----+
| ret |
+-----+
|   0 |
+-----+
1 row in set (0.00 sec)

(7)trim()： De space function

mysql> select length(' hello ') as ret;
+-----+
| ret |
+-----+
|  11 |
+-----+
1 row in set (0.00 sec)

mysql> select length(trim(' hello ')) as ret;
+------+
| ret  |
+------+
|    5 |
+------+
1 row in set (0.00 sec)

(8)replace(): Character substitution

mysql> select replace(' Zhang Wuji likes Zhou Zhiruo , Zhou Zhiruo also loves Zhang Wuji ',' Zhou Zhiruo ',' Zhao Min ') as ret;
+-----------------------------------------------+
| ret                                           |
+-----------------------------------------------+
|  Zhang Wuji likes Zhao min , Zhao Min also loves Zhang Wuji                 |
+-----------------------------------------------+
1 row in set (0.00 sec)

2. Mathematical functions

(1)round()： Round function

mysql> select round(3.14);
+-------------+
| round(3.14) |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)

mysql> select round(3.64);
+-------------+
| round(3.64) |
+-------------+
|           4 |
+-------------+
1 row in set (0.00 sec)

# Keep the decimal places , Then round it off 
mysql> select round(3.64,1);
+---------------+
| round(3.64,1) |
+---------------+
|           3.6 |
+---------------+
1 row in set (0.00 sec)

mysql> select round(3.66,1);
+---------------+
| round(3.66,1) |
+---------------+
|           3.7 |
+---------------+
1 row in set (0.00 sec)

(2)truncate()： Keep the decimal places directly , No rounding

mysql> select truncate(3.1415,2);
+--------------------+
| truncate(3.1415,2) |
+--------------------+
|               3.14 |
+--------------------+
1 row in set (0.00 sec)

mysql> select truncate(3.1415,3);
+--------------------+
| truncate(3.1415,3) |
+--------------------+
|              3.141 |
+--------------------+
1 row in set (0.00 sec)

3. Date function

(1)now(): system date + Time

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-12-20 16:08:07 |
+---------------------+
1 row in set (0.00 sec)

(2)curdate(): Show dates only

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2020-12-20 |
+------------+
1 row in set (0.00 sec)

(3)curtime()： Show only time

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 16:09:47  |
+-----------+
1 row in set (0.00 sec)

(4)str_to_date(): String to date function

Serial number	Format symbol	function
1	%Y	Four months
2	%y	Two months
3	%m	month （01,02…11,12）
4	%c	month （1,2…11,12）
5	%d	Japan （01,02）
6	%H	Hours （24 hourly ）
7	%h	Hours （12 hourly ）
8	%i	minute （00,01…59）
9	%s	second (00,01…59)

Example ： At the time of transformation , The position to be converted in the back corresponds to the data position in the front , Finally, the system will automatically follow ’ year month Japan ’ The order of is displayed

mysql> select str_to_date('6-7 1997','%m-%d %Y') as 'date';
+------------+
| date       |
+------------+
| 1997-06-07 |
+------------+
1 row in set (0.00 sec)

mysql> select str_to_date('3/1/2020','%m/%d/%Y') as 'date';
+------------+
| date       |
+------------+
| 2020-03-01 |
+------------+
1 row in set (0.00 sec)

(5) Convert date to character

mysql> select date_format(now(),'%Y year %m month %d Japan ') as ret;
+-------------------+
| ret               |
+-------------------+
| 2020 year 12 month 20 Japan     |
+-------------------+
1 row in set (0.01 sec)

mysql> select date_format(now(),' A.D. %Y year  %m month  %d Japan ') as ret;
+---------------------------+
| ret                       |
+---------------------------+
|  A.D. 2020 year  12 month  20 Japan       |
+---------------------------+
1 row in set (0.01 sec)

4. Other functions

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.36    |
+-----------+
1 row in set (0.00 sec)

mysql> select database();
+-------------+
| database()  |
+-------------+
| myemployees |
+-------------+
1 row in set (0.00 sec)

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

5. Process control functions

7、 ... and 、 Group function

function ： For statistical use , Also known as aggregate function or statistical function

1.sum(): Sum up

mysql> select sum(salary) from employees;
+-------------+
| sum(salary) |
+-------------+
|   691400.00 |
+-------------+
1 row in set (0.00 sec)

2.avg(): Average

mysql> select avg(salary) from employees;
+-------------+
| avg(salary) |
+-------------+
| 6461.682243 |
+-------------+
1 row in set (0.00 sec)

3.max(): Maximum

mysql> select max(salary) from employees;
+-------------+
| max(salary) |
+-------------+
|    24000.00 |
+-------------+
1 row in set (0.00 sec)

4.min(): minimum value

mysql> select min(salary) from employees;
+-------------+
| min(salary) |
+-------------+
|     2100.00 |
+-------------+
1 row in set (0.00 sec)

5.count(): Calculate the number of

usage 1： count( Field name ) ： How many rows are there in the statistics field

mysql> select count(salary) from employees;
+---------------+
| count(salary) |
+---------------+
|           107 |
+---------------+
1 row in set (0.00 sec)

usage 2：count(*) : Only one field in each row has data , Even one line

6. Be careful ：

1.sum avg   Generally, it is only used to process numeric data 
2.max min count  Can handle any type of data 
3. All the above functions ignore null

8、 ... and 、 Group query

 grammar ：
select 
	 Group function , Field name ( This field name also appears in group by Behind )
from
	 Table name 
where
	 filter 
	group by  The field name above （ That is, the fields to be grouped ）

1. Simple group query

Example 1： Query the maximum wage of each type of work

mysql> select max(salary),job_id from employees group by job_id;
+-------------+------------+
| max(salary) | job_id     |
+-------------+------------+
|     8300.00 | AC_ACCOUNT |
|    12000.00 | AC_MGR     |
|     4400.00 | AD_ASST    |
|    24000.00 | AD_PRES    |
|    17000.00 | AD_VP      |
|     9000.00 | FI_ACCOUNT |
|    12000.00 | FI_MGR     |
|     6500.00 | HR_REP     |
|     9000.00 | IT_PROG    |
|    13000.00 | MK_MAN     |
|     6000.00 | MK_REP     |
|    10000.00 | PR_REP     |
|     3100.00 | PU_CLERK   |
|    11000.00 | PU_MAN     |
|    14000.00 | SA_MAN     |
|    11500.00 | SA_REP     |
|     4200.00 | SH_CLERK   |
|     3600.00 | ST_CLERK   |
|     8200.00 | ST_MAN     |
+-------------+------------+
19 rows in set (0.00 sec)

2. Grouping query with judgment

Example 1： Query mailbox contains a Character , Average wage per department

mysql> select avg(salary),department_id,email from employees where email like '%a%' group by department_id;
+--------------+---------------+----------+
| avg(salary)  | department_id | email    |
+--------------+---------------+----------+
|  7000.000000 |          NULL | KGRANT   |
|  4400.000000 |            10 | JWHALEN  |
|  9500.000000 |            20 | MHARTSTE |
|  4460.000000 |            30 | DRAPHEAL |
|  6500.000000 |            40 | SMAVRIS  |
|  3496.153846 |            50 | AFRIPP   |
|  6200.000000 |            60 | AHUNOLD  |
| 10000.000000 |            70 | HBAER    |
|  8535.294118 |            80 | KPARTNER |
| 17000.000000 |            90 | NKOCHHAR |
|  8166.666667 |           100 | DFAVIET  |
+--------------+---------------+----------+
11 rows in set (0.00 sec)