This blog is equipped with the learning video of Lao Du in station B to make learning records for later learning and review. It is not used for other purposes.
Video link: https://www.bilibili.com/video/BV1Vy4y1z7EX?spm_id_from=pageDriver

Chapter 1 MySQL overview

1.1 Preface

1. DB: database, warehouse for storing data
2. DBMS: database management system, also known as database software or database products, which is used to create and manage databases. The common ones are MySQL, Oracle and SQL Server
3. DBS: database system. Database system is a general term, including database, database management system, database managers, etc. it is the largest category
4. SQL: structured query language, which is used to communicate with the database. It is not unique to a database software, but a common language for almost all mainstream database software

1.2 MySQL installation

[1] How to obtain mysql5.5 software:
Link: https://pan.baidu.com/s/1ocwLwFxfPVXUb4jfKyJsSQ
Extraction code: 11ee

[2] See the video to explain the specific installation and uninstallation tutorials. The mapping is not explained here. Installation video link of station B: https://www.bilibili.com/video/BV1Vy4y1z7EX?p=3&spm_id_from=pageDriver

[3] If you have the same problems as me during installation, you can refer to another blog: [solution] mysql5.5 installation failed: the last step did not respond

1.3 common instructions

View mysql services
Computer – > right click – > management – > services and applications – > services – > find mysql services
MySQL service is in the "start" status by default. It can only be used after MySQL is started. By default, it is started automatically. Automatic startup means that the service will be started automatically the next time the operating system is restarted.

(1) Startup and shutdown of MySQL

You can use the command to turn on and off the mysql service

net stop MySQL
net start MySQL

Note: you need to open cmd as an administrator before you can use it!

Other services can also be stopped and started through the above net instructions

(2) MySQL login

Use the mysql.exe command under the bin directory to connect to the MySQL database server

mysql -uroot  -t password

If the following information is displayed, it will be successful!

(3) MySQL exit: exit

Exit exit mysql

You can hide password login in the following ways:

View database command: Note: there is a semicolon

show databases;

Four databases are provided by default

(4) Use a specific database

use Database name;

(5) Create database

create Database name;

(6) View all tables under a specific database

show tables;

Note: the above commands are not case sensitive

Note: table rows are called records; Columns are called fields. Each field has attributes such as field name, data type, constraint, etc.
Data type: string, data, date, etc
Constraints: there are many kinds of constraints, such as uniqueness constraints.

(7) View MySQL version number

select version();

(8) View the database currently in use

select database();

Note: mysql does not execute when it is missing ";"; "indicates the end!

Note: \ c is used to terminate the input of a command.

1.4 classification of SQL statements

1. DQL: Data Query Language: select, from, where
2. DML: data operation language: insert, update, delete. It is mainly the operation of data in the operation table
3. DDL: Data Definition Language: create, drop, alter, truncate. It mainly operates on the table structure.
4. DCL: data control language: grant, revoke
5. TCL: transaction control language: commit (transaction commit), rollback (transaction rollback)

Chapter 2 DQL language

2.1 basic query

(1) Import of data

Bjpwernode.sql is the database table I prepared for you in advance.
How to import data from sql files?

source Pathname.sql

Note: do not have Chinese in the path!!!!

There are three tables in the imported data:
dept is the Department table
emp is the employee table
salgrade is the salary scale

(2) View data in table

​ select * from Table name; 

(3) View table structure

desc Table name;

(4) Simple query DQL

Query a field

select Field name from Table name;

Note: select and from are keywords. Field names and table names are identifiers.

Emphasis: all SQL statements end with ";". In addition, SQL statements are not case sensitive.

1. Query department name

Query multiple fields

Use commas to separate ","

2. Query department number and department name

Query all fields

Method 1: write each field, separated by commas

select a,b,c,d,e from tablename;

Method 2:

select * from tablename;

Disadvantages of method 2: low efficiency and poor readability; It is not recommended in actual development

(5) Alias query columns

select Field name as field alias from Table name;

Note: only the column name of the displayed query result is displayed as deptname, and the original table column name is still called: dname
Remember: select statements will never be modified. (because it is only responsible for query)

Can the as keyword be omitted? tolerable

select deptno,dname deptname from dept;

Suppose there is a space in the alias when you start the alias. What should I do?

​ mysql> select deptno,dname dept name from dept;

When the DBMS sees such a statement, it compiles the SQL statement. If it does not conform to the syntax, it will report an error. How?

​ select deptno,dname 'dept name' from dept; //Charizing Operator 
​ select deptno,dname "dept name" from dept; //quotation marks 

Note: in all databases, strings are enclosed in single quotation marks. Single quotation marks are standard, and double quotation marks are not used in oracle database. But it can be used in mysql.

Calculate employee annual salary
1. Check employee name and salary: sal * 12

Conclusion: fields can use mathematical expressions!

2. Chinese alias with single quotation marks

2.2 query criteria: where

Not all the data in the table can be found. The query results are qualified.
Syntax format:

select
	Field 1,Field 2,Field 3....
from 
	Table name
where
	condition;

1. = equal sign

Query the name and number of an employee whose salary is equal to 800

2. < > or= Not equal to

Query the name and number of employees whose salary is not equal to 800

3. < less than

Query the name and number of employees whose salary is less than 2000

4. < = less than or equal to

Query the name and number of employees whose salary is less than or equal to 3000

5. > greater than

Query the name and number of employees whose salary is greater than 3000

6. > = greater than or equal to

Query the name and number of employees whose salary is greater than or equal to 3000?

Query SMITH number and salary?

select empno,ename,sal from emp where ename='SMITH'; //Use single quotes for Strings

7. between... And... Is equal to > = and<=

Query employee information with salary between 2450 and 3000? Including 2450 and 3000
The first way: > = and < = (and means and)

Second:

When using between and, you must follow the principle of small left and large right; Between and is a closed interval

8. is null (is not null )

Query which employees' allowances / subsidies are null

Note: null cannot be measured with an equal sign in the database. You need to use is null because null in the database represents nothing and it is not a value.

9. and

Query the information of employees whose position is MANAGER and whose salary is greater than 2500

10. or

Query employees whose jobs are MANAGER and SALESMAN

and has higher priority than or.

select * from emp where sal > 2500 and deptno = 10 or deptno = 20;    //The functions of the two statements are the same
select * from emp where (sal > 2500 and deptno = 10) or deptno = 20;

select * from emp where sal > 2500 and (deptno = 10 or deptno = 20);   //This sentence is different from the above 

11. in (not in)

Equivalent to multiple or (not in not in this range)

Query employees whose job position is MANAGER or SALESMAN

Query employee information with salary of 800 and 5000

Query employee information whose salary is not 80050003000

12. like

It is called fuzzy query and supports% or underscore matching
%: matches any number of characters
Underscore: any character
(% is a special symbol, is also a special symbol)

1. Find the one whose name contains O

2. Find the one whose name ends in T

3. Find the one whose name begins with K

4. Find out that the second word is A

5. Find out that the third letter is R

6. Find the one with "" in the name
Escape character implementation

mysql> select name from t_student where name like '%\_%'; // \Escape character.
		+----------+
		| name     |
		+----------+
		| jack_son |
		+----------+

2.3 Sorting Query: order by

(1) Default ascending order

Query and sort all employee salaries. Default ascending order

select ename,sal from emp order by sal;

(2) Specify descending order

select ename,sal from emp order by sal desc;

(3) Specify ascending order

select ename,sal from emp order by sal asc;

(4) Sorting of multiple fields

When querying employee names and salaries, they are required to be sorted in ascending order by salary. If the salaries are the same, they are sorted in ascending order by name.
It is divided into primary and secondary. sal comes first and plays a leading role. Enable enable ename sorting only when sal is equal.

(5) The position of the field can also be sorted

Understand that this sort is not recommended. Because it is not robust, it is easy to change the column order.

select ename,sal from emp order by 2;   //Sort by second column

Comprehensive training

The execution sequence of the above statements must be mastered:
	Step 1: from
	Step 2: where
	Step 3: select
	Step 4: order by(Sorting is always performed last!)

2.4 single line processing function

Data processing function. Of course, there are multiline processing functions
Characteristics of single line processing function: one input corresponds to one output
Features of multi line processing function: multiple inputs correspond to one output

(1) lower(): convert to lowercase

select lower(ename) as ename from emp;

(2) upper(): convert to uppercase

select upper(ename) as ename from emp;

(3)substr()

Note: the starting index starts from 1

Pick the name of the person whose initials are A

(4)length()

(5) concat(): String splicing

(6) trim(): remove spaces

str_to_date converts a string to a date
date_format format date
format sets the thousandth
These three will be discussed later

(7)case...when...then...when...then...else...end

When the employee's job is MANAGER, the salary will be increased by 10%. When the job is SALESMAN, the salary will be increased by 50%. Others are normal.
(Note: the database is not modified, but the query result is displayed as salary increase)

select field from table name;
​ select ename from emp;
Examples of choosing literals:

​ select 'abc' from emp; 

Conclusion: select can be followed by the field name of a table (which can be regarded as variable name) or literal value / literal value (data).

(8) round(): round

Reserved integer:

Keep one decimal place:

Keep to ten:

Keep to hundreds:

(9) rand() generates a random number

Generate random numbers within 100

(10) ifnull(): null can be converted to a concrete value

ifnull is an empty handler. Dedicated to empty.
In all databases, as long as there is a mathematical operation involving NULL, the final result is NULL.

Calculate the annual salary of each employee
Annual salary = (monthly salary + monthly subsidy) * 12

Note: as long as NULL participates in the operation, the final result must be NULL. To avoid this, you need to use the ifnull function.

Ifnull function usage: ifnull (data, which value is treated as)
If the "data" is NULL, which value should the data structure be treated as.

Example: when the subsidy is NULL, the subsidy is regarded as 0

2.5 multiline processing function

Grouping function

Note: grouping functions must be grouped before they can be used. If you do not group the data, the whole table defaults to one group.

1. Five grouping functions

1. count(): count
2. sum(): Sum
3. avg(): average value
4. max(): maximum
5. min(): minimum

2. Precautions when using grouping functions

First point: the grouping function automatically ignores NULL. You don't need to deal with NULL in advance.

Second point: what is the difference between count(*) and count (specific field) in grouping functions?

Count (specific field): indicates the total number of non NULL elements in this field.
count(*): the total number of rows in the statistics table.

Third point: grouping functions cannot be used directly in the where clause
Example: find out the employee information higher than the minimum wage

Point 4: all grouping functions can be combined and used together

2.6 group query: group by

What is group query?
In practical applications, there may be such a need to group first, and then operate on each group of data.
For example:
Calculate the wages and salaries of each department
Calculate the average salary for each job
Find out the highest salary for each job

At this time, we need to use group query. How to perform group query?

Execution order of keywords

select
	...
from
	...
where
	...
group by
	...
order by
	...

Key conclusion: in a select statement, if there is a group by statement, the select can only be followed by the fields participating in the grouping and the grouping function, and the others cannot be followed.

Find out the salary and salary of each job

mysql> select job, sum(sal) from emp group by job order by sal;
+-----------+----------+
| job       | sum(sal) |
+-----------+----------+
| CLERK     |  4150.00 |
| SALESMAN  |  5600.00 |
| MANAGER   |  8275.00 |
| ANALYST   |  6000.00 |
| PRESIDENT |  5000.00 |
+-----------+----------+
5 rows in set (0.00 sec)

mysql> select job, sum(sal) from emp group by job order by sum(sal);
+-----------+----------+
| job       | sum(sal) |
+-----------+----------+
| CLERK     |  4150.00 |
| PRESIDENT |  5000.00 |
| SALESMAN  |  5600.00 |
| ANALYST   |  6000.00 |
| MANAGER   |  8275.00 |
+-----------+----------+
5 rows in set (0.00 sec)

Find out the highest salary for each department

mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

mysql> select * from emp order by deptno;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.00 sec)

mysql> select deptno,max(sal) from emp group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
|     20 |  3000.00 |
|     30 |  2850.00 |
+--------+----------+
3 rows in set (0.00 sec)

Find out the maximum salary of "each department, different jobs"

mysql> select deptno,job,max(sal) from emp group by deptno,job;
+--------+-----------+----------+
| deptno | job       | max(sal) |
+--------+-----------+----------+
|     10 | CLERK     |  1300.00 |
|     10 | MANAGER   |  2450.00 |
|     10 | PRESIDENT |  5000.00 |
|     20 | ANALYST   |  3000.00 |
|     20 | CLERK     |  1100.00 |
|     20 | MANAGER   |  2975.00 |
|     30 | CLERK     |   950.00 |
|     30 | MANAGER   |  2850.00 |
|     30 | SALESMAN  |  1600.00 |
+--------+-----------+----------+
9 rows in set (0.00 sec)

Find out the highest salary of each department and display the information with the highest salary greater than 3000
Using having can further filter the data after grouping. Having cannot be used alone. Having cannot replace where. Having must be used in combination with group by.
Optimization strategy: where and having. Give priority to where. Where can't be completed, and then choose having.

mysql> select deptno,max(sal) from emp group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
|     20 |  3000.00 |
|     30 |  2850.00 |
+--------+----------+
3 rows in set (0.00 sec)

mysql> select deptno,max(sal) from emp where sal >3000 group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
+--------+----------+
1 row in set (0.00 sec)

mysql> select deptno,max(sal) from emp group by deptno having max(sal)>3000;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|     10 |  5000.00 |
+--------+----------+
1 row in set (0.00 sec)

where cannot be used: find out the average salary of each department, and it is required to display the average salary higher than 2500.

mysql> select deptno,avg(sal) from emp group by deptno;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
|     20 | 2175.000000 |
|     30 | 1566.666667 |
+--------+-------------+
3 rows in set (0.00 sec)

mysql> select deptno,avg(sal) from emp group by deptno having avg(sal)>2500;
+--------+-------------+
| deptno | avg(sal)    |
+--------+-------------+
|     10 | 2916.666667 |
+--------+-------------+
1 row in set (0.00 sec)

summary

select 
	...
from
	...
where
	...
group by
	...
having
	...
order by
	...

The above keywords can only be in this order and cannot be reversed.

Execution sequence?

1. from
2. where
3. group by
4. having
5. select
6. order by

Find out the average salary of each position. If the average salary is greater than 1500, it is required to be arranged in descending order according to the average salary except for the MANAGER position.

mysql>  select job,avg(sal) from emp where job <> 'MANAGER' group by job having avg(sal) >1500 order by avg(sal) desc;
+-----------+-------------+
| job       | avg(sal)    |
+-----------+-------------+
| PRESIDENT | 5000.000000 |
| ANALYST   | 3000.000000 |
+-----------+-------------+

mysql>  select job,avg(sal) from emp where job not in ('MANAGER') group by job having avg(sal) >1500 order by avg(sal) desc;
+-----------+-------------+
| job       | avg(sal)    |
+-----------+-------------+
| PRESIDENT | 5000.000000 |
| ANALYST   | 3000.000000 |
+-----------+-------------+

mysql> select job,avg(sal) from emp where job != 'MANAGER' group by job having avg(sal)>1500 order by avg(sal) desc;
+-----------+-------------+
| job       | avg(sal)    |
+-----------+-------------+
| PRESIDENT | 5000.000000 |
| ANALYST   | 3000.000000 |
+-----------+-------------+
2 rows in set (0.00 sec)