MySql learning notes

1, SQL, DB, DBMS

DB: the database actually exists as a file on the hard disk
DBMS: database management system, software
SQL: Structured Query Language

2, Watch

Table - table is the basic unit of database. All data is stored in the table
A table includes rows and columns:
- Line: called data and record
- Columns: called fields
Attributes that a field should have: field name, data type, related constraints (can it be empty...)

3, SQL statement

DQL (data query language): query statements. All select statements are DQL
DML (data operation language): insert, delete and update to add, delete and modify the data in the table
DDL (data definition language): create, drop, alter, add, delete and modify the table structure
TCL (transaction control language): commit (commit transaction), rollback (rollback transaction)
DCL (data control language): grant (authorization), revoke (revocation of authority)

4, Basic operation of doc window

mysql -uroot -p654321  #Sign in
show databases;#Show all databases
create database bjpowernode;#New database
use bjpowernode;#Use a database
show tables;#Show the tables in the database
drop database bjpowernode;#Delete database
desc dept;#View table structure

5, SQL script

The file extension is sql, and a large number of sql statements are written in this file. When the amount of data in the sql script is too large, use the source command to complete the initialization

source D:\Everything folder\Mathematics Competition\bjpowernode\bjpowernode.sql

6, View table structure

 show tables;
+-----------------------+
| Tables_in_bjpowernode |
+-----------------------+
| dept                  |  Department table
| emp                   |  Employee table
| salgrade              |  Wage scale
+-----------------------+

 desc dept;
 +--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DEPTNO | int(2)      | NO   | PRI | NULL    |       |  Department number
| DNAME  | varchar(14) | YES  |     | NULL    |       |  Department name
| LOC    | varchar(13) | YES  |     | NULL    |       |  Department location
+--------+-------------+------+-----+---------+-------+
 
 mysql> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO    | int(4)      | NO   | PRI | NULL    |       |  Employee number
| ENAME    | varchar(10) | YES  |     | NULL    |       |  Employee name
| JOB      | varchar(9)  | YES  |     | NULL    |       |  post
| MGR      | int(4)      | YES  |     | NULL    |       |  Superior leader No
| HIREDATE | date        | YES  |     | NULL    |       |  Entry date
| SAL      | double(7,2) | YES  |     | NULL    |       |  wages
| COMM     | double(7,2) | YES  |     | NULL    |       |  subsidy/allowance
| DEPTNO   | int(2)      | YES  |     | NULL    |       |  Department number
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.02 sec)

mysql> desc salgrade;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| GRADE | int(11) | YES  |     | NULL    |       |  Grade
| LOSAL | int(11) | YES  |     | NULL    |       |  minimum wage 
| HISAL | int(11) | YES  |     | NULL    |       |  Maximum salary
+-------+---------+------+-----+---------+-------+
3 rows in set (0.01 sec)

7, View data in table

mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

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 salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)

8, MySql common commands

#View what database is currently being used
mysql> select database();
+-------------+
| database()  |
+-------------+
| bjpowernode |
+-------------+
1 row in set (0.00 sec)

#View database version
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.36    |
+-----------+
1 row in set (0.00 sec)

#End a statement
mysql> select *
    ->
    ->
    ->
    -> \c
mysql>

#Exit database
exit

#View the statement that created the table
show create table emp;

| emp   | CREATE TABLE `emp` (
  `EMPNO` int(4) NOT NULL,
  `ENAME` varchar(10) DEFAULT NULL,
  `JOB` varchar(9) DEFAULT NULL,
  `MGR` int(4) DEFAULT NULL,
  `HIREDATE` date DEFAULT NULL,
  `SAL` double(7,2) DEFAULT NULL,
  `COMM` double(7,2) DEFAULT NULL,
  `DEPTNO` int(2) DEFAULT NULL,
  PRIMARY KEY (`EMPNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

9, Query (DQL)

1. Simple query statement

1) , syntax format

select Field name 1, field name 2...Field name n
from Table name;#Any statement ends with a semicolon and is not case sensitive

#For example:
mysql> select ENAME from emp;
+--------+
| ENAME  |
+--------+
| SMITH  |
| ALLEN  |
| WARD   |
| JONES  |
| MARTIN |
| BLAKE  |
| CLARK  |
| SCOTT  |
| KING   |
| TURNER |
| ADAMS  |
| JAMES  |
| FORD   |
| MILLER |
+--------+

#Query all, not recommended in actual development, low efficiency
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)

2) . fields can participate in operation

#Fields can participate in operations
mysql> select ename, sal * 12 from emp;
+--------+----------+
| ename  | sal * 12 |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 19200.00 |
| WARD   | 15000.00 |
| JONES  | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
| TURNER | 18000.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| FORD   | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
14 rows in set (0.00 sec)

3) . alias

#Alias the columns of query results
mysql> select ename, sal * 12 as yearsal from emp;
+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 19200.00 |
| WARD   | 15000.00 |
| JONES  | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
| TURNER | 18000.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| FORD   | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
14 rows in set (0.00 sec)


# as can be omitted
select ename, sal * 12 yearsal from emp;
+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 19200.00 |
| WARD   | 15000.00 |
| JONES  | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
| TURNER | 18000.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| FORD   | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
14 rows in set (0.00 sec)

#In Chinese, use single quotation marks
select ename, sal * 12 as 'Annual salary' from emp;
+--------+----------+
| ename  | Annual salary        |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 19200.00 |
| WARD   | 15000.00 |
| JONES  | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
| TURNER | 18000.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| FORD   | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
14 rows in set (0.00 sec)

2. Condition query

1) , syntax format

select field...
from Table name
where condition

#Example:
mysql> select ename from emp where sal = 800;
+-------+
| ename |
+-------+
| SMITH |
+-------+
1 row in set (0.01 sec)

2) , not equal to

#Query the name and salary of employees whose salary is not equal to 3000
mysql> select ename, sal from emp where sal <> 3000;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| MILLER | 1300.00 |
+--------+---------+
12 rows in set (0.01 sec)

3),between ... and ...

#Query the name and salary of employees whose salary is between 1200 and 3000
#Mode 1
mysql> select ename,sal from emp where sal >= 1200 and sal <= 3000;
+--------+---------+
| ename  | sal     |
+--------+---------+
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| TURNER | 1500.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
10 rows in set (0.00 sec)

#Method 2 -- description between and... The query is a closed interval
mysql> select ename, sal from emp where sal between 1200 and 3000;
+--------+---------+
| ename  | sal     |
+--------+---------+
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| TURNER | 1500.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
10 rows in set (0.00 sec)

#It can also be used in the string. The range of the initial letter. Note that it is closed on the left and open on the right
mysql> select ename from emp where ename between 'A' and 'C';
+-------+
| ename |
+-------+
| ALLEN |
| BLAKE |
| ADAMS |
+-------+

4),is null , is not null

#Find out who's allowance is empty
mysql> select ename, comm from emp where comm is null;
+--------+------+
| ename  | comm |
+--------+------+
| SMITH  | NULL |
| JONES  | NULL |
| BLAKE  | NULL |
| CLARK  | NULL |
| SCOTT  | NULL |
| KING   | NULL |
| ADAMS  | NULL |
| JAMES  | NULL |
| FORD   | NULL |
| MILLER | NULL |
+--------+------+
10 rows in set (0.00 sec)


#Find out who's allowance is not empty
mysql> select ename, comm from emp where comm is not null;
+--------+---------+
| ename  | comm    |
+--------+---------+
| ALLEN  |  300.00 |
| WARD   |  500.00 |
| MARTIN | 1400.00 |
| TURNER |    0.00 |
+--------+---------+
4 rows in set (0.00 sec)

5) Priority of, and and or

# Query the name, salary and department number of employees whose salary is greater than 1000 and department number is 20 or 30
#Error: (and has priority over or)
mysql> select ename,sal,deptno from emp where sal > 1000 and deptno = 20 or deptno = 30;
+--------+---------+--------+
| ename  | sal     | deptno |
+--------+---------+--------+
| ALLEN  | 1600.00 |     30 |
| WARD   | 1250.00 |     30 |
| JONES  | 2975.00 |     20 |
| MARTIN | 1250.00 |     30 |
| BLAKE  | 2850.00 |     30 |
| SCOTT  | 3000.00 |     20 |
| TURNER | 1500.00 |     30 |
| ADAMS  | 1100.00 |     20 |
| JAMES  |  950.00 |     30 |
| FORD   | 3000.00 |     20 |
+--------+---------+--------+
10 rows in set (0.00 sec)

#Correct: (bracketed)
mysql> select ename,sal,deptno from emp where sal > 1000 and( deptno = 20 or deptno = 30);
+--------+---------+--------+
| ename  | sal     | deptno |
+--------+---------+--------+
| ALLEN  | 1600.00 |     30 |
| WARD   | 1250.00 |     30 |
| JONES  | 2975.00 |     20 |
| MARTIN | 1250.00 |     30 |
| BLAKE  | 2850.00 |     30 |
| SCOTT  | 3000.00 |     20 |
| TURNER | 1500.00 |     30 |
| ADAMS  | 1100.00 |     20 |
| FORD   | 3000.00 |     20 |
+--------+---------+--------+
9 rows in set (0.00 sec)

6),in ,or,not in

#Find out the name and job of the employee whose job is salesman or manager
#Use or:
mysql> select ename,job from emp where job = 'SALESMAN' or job = 'MANAGER';
+--------+----------+
| ename  | job      |
+--------+----------+
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| JONES  | MANAGER  |
| MARTIN | SALESMAN |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.00 sec)

#in:
mysql> select ename, job from emp where job in('SALESMAN', 'MANAGER');
#Note that if numbers are in parentheses, they represent only a single value, not an interval
+--------+----------+
| ename  | job      |
+--------+----------+
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| JONES  | MANAGER  |
| MARTIN | SALESMAN |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| TURNER | SALESMAN |
+--------+----------+
7 rows in set (0.00 sec)


#not in
mysql> select ename, job from emp where job not in('SALESMAN', 'MANAGER');
+--------+-----------+
| ename  | job       |
+--------+-----------+
| SMITH  | CLERK     |
| SCOTT  | ANALYST   |
| KING   | PRESIDENT |
| ADAMS  | CLERK     |
| JAMES  | CLERK     |
| FORD   | ANALYST   |
| MILLER | CLERK     |
+--------+-----------+
7 rows in set (0.00 sec)

3. Fuzzy query

Two key symbols:%_

#Find out MySQL with O in the name > select ename from EMP where ename like '% o%'+-------+| ename |+-------+| JONES || SCOTT || FORD  |+-------+

#MySQL > select ename from EMP where ename like '_ a%';+--------+|  ename  |+--------+| WARD   || MARTIN || JAMES  |+--------+

#Find the underlined in the name select ename from emp where ename like '%\_%';#Using escape characters

#Select ename from EMP where ename like '% t'+-------+| ename |+-------+| SCOTT |+-------+

4. order by

asc ascending order
desc descending order
Default ascending sort
Multiple fields are sorted together. Note that the higher the field, the greater the effect

#Sort by salary ascending order
mysql> select ename, sal from emp order by sal;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| JAMES  |  950.00 |
| ADAMS  | 1100.00 |
| WARD   | 1250.00 |
| MARTIN | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN  | 1600.00 |
| CLARK  | 2450.00 |
| BLAKE  | 2850.00 |
| JONES  | 2975.00 |
| FORD   | 3000.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
+--------+---------+

#Descending order
mysql> select ename, sal from emp order by sal desc;
+--------+---------+
| ename  | sal     |
+--------+---------+
| KING   | 5000.00 |
| SCOTT  | 3000.00 |
| FORD   | 3000.00 |
| JONES  | 2975.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| MARTIN | 1250.00 |
| WARD   | 1250.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| SMITH  |  800.00 |
+--------+---------+

#It is arranged in descending order of salary. When the salary is the same, it is arranged in ascending order of name
mysql> select ename, sal from emp order by sal desc, ename asc;
+--------+---------+
| ename  | sal     |
+--------+---------+
| KING   | 5000.00 |
| FORD   | 3000.00 |
| SCOTT  | 3000.00 |
| JONES  | 2975.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| MARTIN | 1250.00 |
| WARD   | 1250.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| SMITH  |  800.00 |
+--------+---------+

#Query jobs that are salesman and are listed in descending order of salary
mysql> select ename, job, sal from emp where job = 'SALESMAN' order by sal desc;
+--------+----------+---------+
| ename  | job      | sal     |
+--------+----------+---------+
| ALLEN  | SALESMAN | 1600.00 |
| TURNER | SALESMAN | 1500.00 |
| WARD   | SALESMAN | 1250.00 |
| MARTIN | SALESMAN | 1250.00 |
+--------+----------+---------+

summary

[the external chain image transfer fails, and the source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-jzchfujh-1624260351984) (C: \ users \ Dell \ appdata \ roaming \ typora user images \ image-20210618160005417. PNG)]

5. Single line processing function

Input one line, output one line
Each database has this provision. As long as there is null, the result of the operation must be null
Null value processing function: ifnull (data that may be null is treated as what)

mysql> select ename, (sal + comm) *12 as yearal from emp;
+--------+----------+
| ename  | yearal   |
+--------+----------+
| SMITH  |     NULL |#Smith's allowance is null, and his annual salary calculated here is null
| ALLEN  | 22800.00 |
| WARD   | 21000.00 |
| JONES  |     NULL |
| MARTIN | 31800.00 |
| BLAKE  |     NULL |
| CLARK  |     NULL |
| SCOTT  |     NULL |
| KING   |     NULL |
| TURNER | 18000.00 |
| ADAMS  |     NULL |
| JAMES  |     NULL |
| FORD   |     NULL |
| MILLER |     NULL |
+--------+----------+
14 rows in set (0.00 sec)

#Change with ifnull() function
mysql> select ename, (sal + ifnull(comm,0)) *12 as yearal from emp;
+--------+----------+
| ename  | yearal   |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 22800.00 |
| WARD   | 21000.00 |
| JONES  | 35700.00 |
| MARTIN | 31800.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
| TURNER | 18000.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| FORD   | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+

6. Grouping function (multiline processing function)

Count count
sum
avg average
max
min
All grouping functions sum a set of data
characteristic:
- Input one line and output multiple lines
- Grouping function automatically ignores null
- The grouping function cannot appear directly in the where clause, because the group by statement is executed after where, and the grouping function is executed after grouping, while the group by statement is executed before where, so it cannot be executed
- ```
mysql> select ename,sal from emp where sal > avg(sal);
#ERROR 1111 (HY000): Invalid use of group function
```
  - count * counts the total number of records
  - count (a field) counts the number of elements that are not empty in a field
  - Grouping functions are generally used in conjunction with group by sentences. Grouping functions are executed after the execution of group by sentences

#Find the sum of wages
mysql> select sum(sal) from emp;
+----------+
| sum(sal) |
+----------+
| 29025.00 |
+----------+
1 row in set (0.01 sec)


#Find the maximum wage
mysql> select ename,max(sal) from emp;
+-------+----------+
| ename | max(sal) |
+-------+----------+
| SMITH |  5000.00 |
+-------+----------+
1 row in set (0.00 sec)

#Grouping functions can also be combined
mysql> select avg(sal),sum(comm) from emp;
+-------------+-----------+
| avg(sal)    | sum(comm) |
+-------------+-----------+
| 2073.214286 |   2200.00 |
+-------------+-----------+
1 row in set (0.00 sec)

7,GROUP BY,HAVING

1)GROUP BY:

Group according to a field or some fields. When there is no group by after an SQL statement, the data of the whole table will form a group
When a statement is followed by group by, select can only be followed by grouping functions and fields involved in grouping. Even if other fields can be added, the query result is meaningless and syntax errors will occur in Oracle

#Find out the highest salary for each job
#Idea: group first and then filter (operate)
mysql> select job, max(sal) from emp group by job;
+-----------+----------+
| job       | max(sal) |
+-----------+----------+
| ANALYST   |  3000.00 |
| CLERK     |  1300.00 |
| MANAGER   |  2975.00 |
| PRESIDENT |  5000.00 |
| SALESMAN  |  1600.00 |
+-----------+----------+
5 rows in set (0.00 sec)

Joint grouping of multiple fields

#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)

2)HAVING

Filter the grouped data again

3. Two examples

#1. Find out the maximum salary of each department and display the part greater than 2900
    #Using having
        mysql> select deptno,max(sal) from emp group by deptno having max(sal) > 2900;
        +--------+----------+
        | deptno | max(sal) |
        +--------+----------+
        |     10 |  5000.00 |
        |     20 |  3000.00 |
        +--------+----------+
        #Disadvantages: the efficiency is low. First divide the group into groups. After the query, make a choice if the check symbol does not meet the conditions after having
    #where
        mysql> select deptno,max(sal) from emp where sal > 2900 group by deptno;#Grouping function cannot be used after where
        +--------+----------+
        | deptno | max(sal) |
        +--------+----------+
        |     10 |  5000.00 |
        |     20 |  3000.00 |
        +--------+----------+
        #Advantages of comparison: filter out those whose salary is less than or equal to 2900, and then group them

#2. Find out the average salary of each department and display the part greater than 2000 (because the query is the average salary, which is calculated after grouping, you can't use where to filter first (and you can't use grouping function after where), so use having)
        mysql> select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;
        +--------+-------------+
        | deptno | avg(sal)    |
        +--------+-------------+
        |     10 | 2916.666667 |
        |     20 | 2175.000000 |
        +--------+-------------+
        #Note: screen after grouping

8. On de duplication of query result set - distinct

The distinct keyword can only appear at the top of all fields
Appearing at the front means that the following fields are combined to remove duplicates

mysql> select distinct job, deptno from emp;
+-----------+--------+
| job       | deptno |
+-----------+--------+
| CLERK     |     20 |
| SALESMAN  |     30 |
| MANAGER   |     20 |
| MANAGER   |     30 |
| MANAGER   |     10 |
| ANALYST   |     20 |
| PRESIDENT |     10 |
| CLERK     |     30 |
| CLERK     |     10 |
+-----------+--------+

#I want to see what jobs there are. If I don't pay attention to them
select job from emp;
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| SALESMAN  |
| MANAGER   |
| SALESMAN  |
| MANAGER   |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
| SALESMAN  |
| CLERK     |
| CLERK     |
| ANALYST   |
| CLERK     |
+-----------+

#After weight removal
mysql> select distinct job from emp;
+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
+-----------+

#Case: count the number of Posts
mysql> select count(distinct job) from emp;
+---------------------+
| count(distinct job) |
+---------------------+
|                   5 |
+---------------------+

8. Summarize the execution order of DQL statements

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

9. ※ connection query

1) Classification

According to the connection mode of the table
- Inner connection
  - Equivalent connection
  - Non equivalent connection
  - Self connection
- External connection
  - Left (outer) connection
  - Right (outer) connection
- Full connection (less use)

2) Cartesian product phenomenon

When two tables are connected, there is no limit. The final query result is the product of the number of records in the two tables

mysql> select  e.ename,dname d from emp e,dept d;
#56 rows in set (0.00 sec)

Avoid Cartesian phenomenon (it is still carried out so many times, the efficiency is not improved, but the effective records are shown)

#Look at the principle. The following syntax is version 92, which is not used now
mysql> select  e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| CLARK  | ACCOUNTING |
| KING   | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH  | RESEARCH   |
| JONES  | RESEARCH   |
| SCOTT  | RESEARCH   |
| ADAMS  | RESEARCH   |
| FORD   | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| TURNER | SALES      |
| JAMES  | SALES      |
+--------+------------+
14 rows in set (0.00 sec)

3) Table alias

#High execution efficiency and good readability
mysql> select  e.ename,dname d from emp e,dept d;

4) Internal connection - equivalent connection

The biggest feature is that the condition is equivalent relationship
join on

/*Syntax (99):
select ...
from A((alias)
(inner) join B((alias)
on Equivalent condition
where...
*/
#Query the employee's name and the name of the employee's department
mysql> select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| CLARK  | ACCOUNTING |
| KING   | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH  | RESEARCH   |
| JONES  | RESEARCH   |
| SCOTT  | RESEARCH   |
| ADAMS  | RESEARCH   |
| FORD   | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| TURNER | SALES      |
| JAMES  | SALES      |
+--------+------------+

5) Internal connection - non equivalent connection

The biggest feature is that the connection relationship in the connection condition is unequal

#Find out the salary grade of each employee and display the employee name, salary and salary grade
select 
e.ename, e.sal, s.grade 
from 
emp e 
join 
salgrade s 
on 
e.sal between s.losal and s.hisal;
+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| WARD   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
| MARTIN | 1250.00 |     2 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+

6) Internal connection - self connection

The biggest feature is that one table is regarded as two tables and connects itself

#Find out the superior leader of each employee and display the employee name and corresponding leader name
emp surface
mysql> select a.ename, b.ename from emp a join emp b on a.mgr = b.empno;#The leader number of the employee is equal to the employee number of the leader
+--------+-------+
| ename  | ename |
+--------+-------+
| SMITH  | FORD  |
| ALLEN  | BLAKE |
| WARD   | BLAKE |
| JONES  | KING  |
| MARTIN | BLAKE |
| BLAKE  | KING  |
| CLARK  | KING  |
| SCOTT  | JONES |
| TURNER | BLAKE |
| ADAMS  | SCOTT |
| JAMES  | BLAKE |
| FORD   | JONES |
| MILLER | CLARK |
+--------+-------+
13 rows in set (0.00 sec)
#King has no leadership

7) External connection (mostly used)

The most important feature: the data of the main table can be queried unconditionally

Differences between the two:

Internal connection: if table a and table B are connected, all data records on table a and table B that can be matched can be queried. This is internal connection. There is no difference between primary and secondary tables
External connection: one of the two tables a and B is the primary table and the other is the secondary table. It mainly queries the data in the primary table and carries the data in the secondary table. When the data in the secondary table does not match the data in the primary table, the secondary table automatically simulates null to match with the primary table, that is, if it does not match, the data in the primary table will not be lost

① Left and right

The left outer connection means that the table on the left is the main table, the right outer connection means that the table on the right is the main table, the left outer connection has the writing method of the right outer connection, and the right outer connection has the writing method of the left outer connection

② Grammar

select...
from A
right(left) (outer) join B
on...

③ Examples

#Find out the superior leader of each employee and display the employee name and corresponding leader name
#King doesn't have a leader, but he is also an employee. The employee table is dominant. The Deputy table is required to automatically match a null for king
    #Left outer connection
        mysql> select a.ename, b.ename from emp a left join emp b on a.mgr = b.empno;
        +--------+-------+
        | ename  | ename |
        +--------+-------+
        | SMITH  | FORD  |
        | ALLEN  | BLAKE |
        | WARD   | BLAKE |
        | JONES  | KING  |
        | MARTIN | BLAKE |
        | BLAKE  | KING  |
        | CLARK  | KING  |
        | SCOTT  | JONES |
        | KING   | NULL  |
        | TURNER | BLAKE |
        | ADAMS  | SCOTT |
        | JAMES  | BLAKE |
        | FORD   | JONES |
        | MILLER | CLARK |
        +--------+-------+
        14 rows in set (0.00 sec)
    #Write right outer connection
        mysql> select a.ename, b.ename from emp b right join emp a on a.mgr = b.empno;
        +--------+-------+
        | ename  | ename |
        +--------+-------+
        | SMITH  | FORD  |
        | ALLEN  | BLAKE |
        | WARD   | BLAKE |
        | JONES  | KING  |
        | MARTIN | BLAKE |
        | BLAKE  | KING  |
        | CLARK  | KING  |
        | SCOTT  | JONES |
        | KING   | NULL  |
        | TURNER | BLAKE |
        | ADAMS  | SCOTT |
        | JAMES  | BLAKE |
        | FORD   | JONES |
        | MILLER | CLARK |
        +--------+-------+
        14 rows in set (0.00 sec)

#Query which department has no employees
#Think carefully! First step by step, look up the table and see how to change it to get the desired result
mysql> select d.*
    -> from emp e
    -> right join
    -> dept d
    -> on e.deptno = d.deptno
    -> where e.deptno is null;
+--------+------------+--------+
| DEPTNO | DNAME      | LOC    |
+--------+------------+--------+
|     40 | OPERATIONS | BOSTON |
+--------+------------+--------+
1 row in set (0.01 sec)

8) Connection query of more than three tables

#Find out the Department name and salary grade of each employee, as well as the leader
    /*Syntax:
    from A
    join B
    join C
    ...
    on...
    */

    select e.ename 'staff', s.grade, d.dname, e1. ename 'boss'
    from emp e
    join dept d
    on e.deptno = d.deptno
    join salgrade s
    on e.sal between s.losal and s.hisal
    left join emp e1
    on e.mgr = e1.empno;

    +--------+-------+------------+-------+
    | staff    | grade | dname      |  boss |
    +--------+-------+------------+-------+
    | SMITH  |     1 | RESEARCH   | FORD  |
    | ALLEN  |     3 | SALES      | BLAKE |
    | WARD   |     2 | SALES      | BLAKE |
    | JONES  |     4 | RESEARCH   | KING  |
    | MARTIN |     2 | SALES      | BLAKE |
    | BLAKE  |     4 | SALES      | KING  |
    | CLARK  |     4 | ACCOUNTING | KING  |
    | SCOTT  |     4 | RESEARCH   | JONES |
    | KING   |     5 | ACCOUNTING | NULL  |
    | TURNER |     3 | SALES      | BLAKE |
    | ADAMS  |     1 | RESEARCH   | SCOTT |
    | JAMES  |     1 | SALES      | BLAKE |
    | FORD   |     4 | RESEARCH   | JONES |
    | MILLER |     2 | ACCOUNTING | CLARK |
    +--------+-------+------------+-------+

10. Subquery

The nested select statement is a subquery
Where can it appear?
- After select
- After from
- After where

1) Use subqueries in the where clause

#Find out the information of employees with higher than average salary
mysql> select * from emp where sal > (select avg(sal) from emp);
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  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 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+-----------+------+------------+---------+------+--------+

2) Use subquery after from

#Find out the salary scale of the average salary of each department
mysql> select t.*, s.grade
    -> from(select deptno,avg(sal) as avgsal from emp group by deptno) t #cursor
    -> join salgrade s
    -> on t.avgsal between s.losal and s.hisal;
+--------+-------------+-------+
| deptno | avgsal      | grade |
+--------+-------------+-------+
|     30 | 1566.666667 |     3 |
|     10 | 2916.666667 |     4 |
|     20 | 2175.000000 |     4 |
+--------+-------------+-------+
3 rows in set (0.01 sec)

#Find the average value of salary grade of each department √
mysql>  select e.deptno, avg(s.grade)
    -> from emp e
    ->  join salgrade s
    -> on e.sal between s.losal and s.hisal
    ->  group by e.deptno;
    +--------+--------------+
    | deptno | avg(s.grade) |
    +--------+--------------+
    |     10 |       3.6667 |
    |     20 |       2.8000 |
    |     30 |       2.5000 |
    +--------+--------------+
    3 rows in set (0.00 sec)

3) Use subquery after select

#Find out the name of the Department where each employee belongs, and it is required to display the employee name and department name
mysql>  select e.ename,(select d.dname from dept d where e.deptno = d.deptno)as dname from emp e;
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+

11. Usage of union

You can add query result sets
When using, the number of columns queried must be consistent

#Identify employees whose jobs are salesman and manager
    mysql> select ename,job from emp where job = 'SALESMAN' union select ename, job from emp where job = 'MANAGER';
    +--------+----------+
    | ename  | job      |
    +--------+----------+
    | ALLEN  | SALESMAN |
    | WARD   | SALESMAN |
    | MARTIN | SALESMAN |
    | TURNER | SALESMAN |
    | JONES  | MANAGER  |
    | BLAKE  | MANAGER  |
    | CLARK  | MANAGER  |
    +--------+----------+

#The data of two unrelated tables are spliced together for display (the following case is meaningless, but it shows that the union can do this)
select ename from emp union select dname from dept;
+------------+
| ename      |
+------------+
| SMITH      |
| ALLEN      |
| WARD       |
| JONES      |
| MARTIN     |
| BLAKE      |
| CLARK      |
| SCOTT      |
| KING       |
| TURNER     |
| ADAMS      |
| JAMES      |
| FORD       |
| MILLER     |
| ACCOUNTING |
| RESEARCH   |
| SALES      |
| OPERATIONS |
+------------+
18 rows in set (0.00 sec)

12. Usage of limit (MySql specific)

The key point is to rely on it for paging query in the future
limit gets part of the data in the result set
Syntax: limit, StartIndex, length
startIndex: starting position, starting from 0, representing the first piece of data
length: take several

limit is the last step of SQL statement execution

#Take out the top 5 employees
mysql> select ename, sal from emp order by sal desc limit 0, 5;
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
5 rows in set (0.00 sec)

#The second way to write
mysql> select ename, sal from emp order by sal desc limit 5;#Default 0
+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
5 rows in set (0.01 sec)

#Find employees with wages of 4-9
mysql> select ename,sal from emp order by sal desc limit 3,6;#The initial subscript is 4-1 and the length is 9-4 + 1
+--------+---------+
| ename  | sal     |
+--------+---------+
| JONES  | 2975.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
+--------+---------+
6 rows in set (0.00 sec)

Summary: sql statement execution order

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

9, Build table

1. Syntax format

create table Table name(
Field name data type (constraint)
...(ditto)
)

2. Common data types

int Integer type
bigint Long integer
float float 
char Fixed length string
varchar Variable length string
DATE Date type
BLOB Binary large object(Pictures, videos and other media information)(Binary Large Object)
CLOB Character large object (large text)(Character Large Object)

3. How to choose between char and varchar

char is suitable when the data length in a field does not change, that is, when the length is fixed
When the length of a field is uncertain, such as introduction, name... varchar is suitable

4. Use of BLOB and CLOB

5. Naming

Generally in t_ Or tal_ start

6. Build student list

mysql> create table t_student(
    -> no bigint,
    -> name varchar(255), #Maximum
    -> sex char(1),
    -> classno varchar(255),
    -> birth char(10)

You can use default to set the default value
```
sex char(1) default 1,
...
```

7. Insert data into a table

#Syntax format
#Requirement: the number of field values and data types should be the same
insert into Table name (field name 1, field name 2, from field name 3)....) values(Value 1, value 2, value 3...);
#Fields can be omitted and not written. When not written, the following values must correspond to the number and type of fields in the original table

mysql> insert into t_student(no, name, sex, classno, birth) values(1,'rose', '1', 'G1', '20010808');
+------+------+------+---------+----------+
| no   | name | sex  | classno | birth    |
+------+------+------+---------+----------+
|    1 | rose | 1    | G1      | 20010808 |
+------+------+------+---------+----------+

#Multiple rows of data can be inserted at a time
mysql> insert into t_student
    -> values(2,'jack','0','G2', '20010919'),
    -> (3, 'cam', '0', 'md1', '19740924');
    
+------+------+------+---------+----------+
| no   | name | sex  | classno | birth    |
+------+------+------+---------+----------+
|    1 | rose | 1    | G1      | 20010808 |
|    2 | jack | 0    | G2      | 20010919 |
|    3 | cam  | 0    | md1     | 19740924 |
+------+------+------+---------+----------+

8. Delete table

drop table if exists t_student; #Delete if table exists

9. Replication of tables

Create the query results as a table

create table emp1 as select ename,empno,sal from emp;
mysql> select * from emp1;
+--------+-------+---------+
| ename  | empno | sal     |
+--------+-------+---------+
| SMITH  |  7369 |  800.00 |
| ALLEN  |  7499 | 1600.00 |
| WARD   |  7521 | 1250.00 |
| JONES  |  7566 | 2975.00 |
| MARTIN |  7654 | 1250.00 |
| BLAKE  |  7698 | 2850.00 |
| CLARK  |  7782 | 2450.00 |
| SCOTT  |  7788 | 3000.00 |
| KING   |  7839 | 5000.00 |
| TURNER |  7844 | 1500.00 |
| ADAMS  |  7876 | 1100.00 |
| JAMES  |  7900 |  950.00 |
| FORD   |  7902 | 3000.00 |
| MILLER |  7934 | 1300.00 |
+--------+-------+---------+

10. Insert query results into a table

 #The table structure must be the same
 insert into dept1 select * from dept;

11. Modify data in table

#grammar
update Table name set Field name 1 = Value 1, field name 2 = Value 2...where condition
#All data in the condition table is not updated

update t_student set sex = '0' where name = 'rose';
+------+------+------+---------+----------+
| no   | name | sex  | classno | birth    |
+------+------+------+---------+----------+
|    1 | rose | 0    | G1      | 20010808 |
|    2 | jack | 0    | G2      | 20010919 |
|    3 | cam  | 0    | md1     | 19740924 |
+------+------+------+---------+----------+

12. Delete data in table

#Syntax:
delete from Table name where Conditions;#Delete all without conditions

#Delete large tables and small tables with delete
truncate table Table name; #The table is truncated, cannot be rolled back, and is permanently lost

13. Modify table structure

Using tools rarely happens in actual development

14. Constraint

The role of constraints? In order to ensure the legitimacy, integrity and effectiveness of data
Common constraints?
- Non NULL constraint not null: the field of the constraint cannot be empty
- unique constraint: the fields of the constraint cannot be duplicate
- primary key constraint: cannot be empty or duplicate
- Foreign key constraint foriegn key
- (check constraint check) mysql does not support

1) Non NULL constraint

There is no table level definition method, which can only be added after the field

mysql> create table t_user(
    -> id int,
    -> username varchar(255) not null,
    -> password varchar(255)
    -> );
Query OK, 0 rows affected (0.01 sec)

#Test:
mysql> insert into t_user(id, password) values(1,'123');
ERROR 1364 (HY000): Field 'username' doesn't have a default value #Error, cannot be empty

2) Uniqueness constraint

The modified field is unique and cannot be repeated. It can be empty

create table t_user(
id int,
usercode varchar(255),
username varchar(255),
unique(usercode, username) #The combination of the two cannot be repeated, and the combination adds a constraint
);

Multiple fields are combined to add a constraint, that is, a table level constraint

3) Primary key constraint

The fields modified by the primary key cannot be empty or duplicate
There is only one primary key in a table
Related terms
- Primary key constraint
- Primary key field
- Primary key value (the value inserted in the primary key field)

mysql> create table t_user(
    -> id int primary key,
    -> username varchar(255) not null,
    -> password varchar(255)
    -> );
    
    mysql> desc t_user;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int(11)      | NO   | PRI | NULL    |       |
| username | varchar(255) | NO   |     | NULL    |       |
| password | varchar(255) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+

Function of primary key: the primary key value is the unique identifier (important) of this row record in this table
Classification of primary keys
- Single primary key (common), compound primary key (multiple fields are combined to add a primary key constraint) (not recommended, contrary to the three paradigms)
- Natural primary key and business primary key (the primary key value is linked to the business) (not recommended, because once the business changes, the primary key value may also need to change. Sometimes if it does not change, the primary key may be repeated)
Self increment of primary key value

mysql> create table t_user(
    -> id int primary key auto_increment, #Automatically maintain a self increasing primary key value, which has nothing to do with business
    -> username varchar(255)
    -> );
    
    insert into t_user(username) values('s');
    insert into t_user(username) values('a');
    insert into t_user(username) values('v');
    
    mysql> select * from t_user;
    +----+----------+
    | id | username |
    +----+----------+
    |  1 | s        |
    |  2 | a        |
    |  3 | v        |
    +----+----------+

4) Foreign key constraints (notes need to be supplemented)

The referenced field is not necessarily a primary key, but it must have a unique constraint
Can the foreign key value be null? sure

10, Storage engine*

11, Transaction

1. Concept

A transaction is a complete logical unit and cannot be subdivided

2. Principle

Suppose a thing needs to be carried out insert,update,delete The statement is completed, successful and failed at the same time
 Turn on transaction mechanism (start)
    implement insert sentence
    implement update sentence
    implement delete sentence
    (After the above three statements are executed successfully, the execution will be recorded in the operation history of the database,; The data will not be saved to the file and will not affect the data in the hard disk)
Commit transaction( commit)Or rollback transaction( rollback)(end)

3. Four features ACID

Atomicity: transactions are the smallest unit of work and cannot be subdivided
Consistency: the transaction must ensure that multiple dml statements succeed and fail at the same time
Isolation: transaction a and transaction b remain isolated
Persistence: the final data must be persisted to the hard disk file before the transaction ends

4. Transaction isolation

Theoretically, there are four isolation levels (starting with MySq 3, the default isolation level of Oracle database is 2)

Read uncommitted: if the other party's transaction has not been committed, we can read its data. Dirty Read indicates that dirty data has been read, which is extremely unstable
Read committed: the problem is that it cannot be read repeatedly (because it may be read differently every time), which solves the phenomenon of dirty reading
repeatable read - solves the problem of non repeatable reading. The problem is that the read data is an illusion (it has been deleted there, but it can still be read here)
Serialized read (serialized read) - serializable: all problems are solved. The problem is that the efficiency is low and transactions need to be queued

5. Presentation transaction

Note: MySQL transactions are automatically committed by default (once by executing a DML statement)

Turn off automatic submission: start transaction;

#Sets the global isolation level for transactions
	set global transaction isolation level ...;
#View the global isolation level of a transaction
    select @@global.tx_isolation;
    +-----------------------+
    | @@global.tx_isolation |
    +-----------------------+
    | READ-COMMITTED        |
    +-----------------------+

12, Index

1. What is an index? What's the usage?

Is to add to a field or some fields
Equivalent to a directory, you can quickly find the desired resources through the directory
In the database, there are two retrieval methods for querying a table: full table scanning and index scanning (high efficiency)
The fundamental principle of improving efficiency: reducing the scanning range
Although the efficiency is high, it cannot be added casually, because it is also an object and has maintenance costs. Once the data is modified, the index needs to be reordered and maintained

2. How do I create an index? How to delete index objects?

create index Index name on Table name(Field name); #establish
drop index Index name on Table name; #delete

3. When to consider adding an index to a field?

Huge amount of data (depending on the environment)
There are few DML operations on this field (field update and index update)
This field often appears in the where clause (often queried based on a field)

4. Attention

The primary key and fields with unique keyword will be automatically indexed, so the query efficiency based on the primary key is high
View the execution plan of sql statement: explain SQL statement; You can see where it was scanned

#type all -- full table scan
mysql> explain select ename,sal from emp where sal = 5000;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | emp   | ALL  | emp_sal_index | NULL | NULL    | NULL |   14 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

#Add index to sal field
mysql> create index emp_sal_index on emp(sal);

#After adding index
mysql> explain select ename, sal from emp where sal = 5000;
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
|  1 | SIMPLE      | emp   | ref  | emp_sal_index | emp_sal_index | 9  | const |    1 | Using where |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+

5. Implementation principle of index

At the bottom of the index is the b + tree
Narrow the search scope through the b-tree, sort and partition the bottom layer, the index will carry the physical address of the data in the table, and locate the data in the table through the physical address to improve the efficiency

6. Classification of indexes

Single index: adds an index to a field
Compound index: add an index to several fields combined
primary key
unique index
(primary key index and unique index are the most efficient)

7. When does the index expire?

In fuzzy query, the whole table will be queried, because the first wildcard% cannot be retrieved and what it is

select ename from emp where ename like '%j%';

13, View view

1. What is a view?

Look at the data from different angles

2. How do I create a view? How do I delete a view?

create view myview as select empno, ename from emp; #establish
drop view myview; #delete

3. Attention

Adding, deleting, modifying and querying the view will affect the original table data
Views can only be created through DQL statements

mysql> select * from myview;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+

4. Function

It can hide the implementation details of the table. For systems with high confidentiality level, the database only provides relevant views externally, which will not improve efficiency, because the final operation is also on the original table

14, DBA command

1. Export data from the database

2. Import

create database bjpowernode;#Build database first
use bjpowernode;
sourse File path;

3. Create user*

4. Authorization*

5. Withdraw permission*

15, Three paradigms of database design

1. What is the design paradigm?

The basis of designing the table. The table designed according to these three paradigms will not have data redundancy

2. What are they?

The first paradigm: each database table must have a primary key, and each field is atomic and cannot be subdivided
The second paradigm: Based on the first paradigm, all non primary key fields completely depend on the primary key and cannot produce partial dependence (many to many solution: design three tables, relational tables and two foreign keys)
The third paradigm: Based on the second paradigm, all non primary key fields completely depend on the primary key and cannot generate transitive dependency (one to many solution: design two tables and add foreign keys to many tables)

3. Design of one-to-one meter

Primary key sharing
Foreign key unique
| rows | Extra |
±—±------------±------±-----±--------------±---------±--------±------±-----±------------+
| 1 | SIMPLE | emp | ref | emp_sal_index | emp_sal_index | 9 | const | 1 | Using where |
±—±------------±------±-----±--------------±---------±--------±------±-----±------------+



### 5. Implementation principle of index

- The underlying index is b+tree
- adopt b The tree reduces the search scope, and the bottom layer is sorted and partitioned. The index will carry the physical address of the data in the table, and locate the data in the table through the physical address to improve the efficiency

### 6. Classification of indexes

- Single index: adds an index to a field
- Compound index: add an index to several fields combined
- primary key 
- unique index
- (Primary key index and unique index are the most efficient)

### 7. When does the index expire?

In fuzzy query, the whole table will be queried because it is the first wildcard%，I can't retrieve it. I don't know what it is

```sql
select ename from emp where ename like '%j%';

13, View view

1. What is a view?

Look at the data from different angles

2. How do I create a view? How do I delete a view?

create view myview as select empno, ename from emp; #establish
drop view myview; #delete

3. Attention

Adding, deleting, modifying and querying the view will affect the original table data
Views can only be created through DQL statements

mysql> select * from myview;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+

4. Function

14, DBA command

1. Export data from the database

[external chain picture transferring... (img-zwa3Zfh8-1624260351990)]

2. Import

create database bjpowernode;#Build database first
use bjpowernode;
sourse File path;

3. Create user*

4. Authorization*

5. Withdraw permission*

15, Three paradigms of database design

1. What is the design paradigm?

The basis of designing the table. The table designed according to these three paradigms will not have data redundancy

2. What are they?

The first paradigm: each database table must have a primary key, and each field is atomic and cannot be subdivided
The second paradigm: Based on the first paradigm, all non primary key fields completely depend on the primary key and cannot produce partial dependence (many to many solution: design three tables, relational tables and two foreign keys)
The third paradigm: Based on the second paradigm, all non primary key fields completely depend on the primary key and cannot generate transitive dependency (one to many solution: design two tables and add foreign keys to many tables)

3. Design of one-to-one meter

Primary key sharing
Foreign key unique

Keywords: Database MySQL

Added by khaldryck on Fri, 28 Jan 2022 03:08:30 +0200

Programming VIP

MySql learning notes

MySql learning notes

1, SQL, DB, DBMS

2, Watch

3, SQL statement

4, Basic operation of doc window

5, SQL script

6, View table structure

7, View data in table

8, MySql common commands

9, Query (DQL)

1. Simple query statement

1) , syntax format

2) . fields can participate in operation

3) . alias

2. Condition query

1) , syntax format

2) , not equal to

3),between ... and ...

4),is null , is not null

5) Priority of, and and or

6),in ,or,not in

3. Fuzzy query

4. order by

summary

5. Single line processing function

6. Grouping function (multiline processing function)

7,GROUP BY,HAVING

1)GROUP BY:

2)HAVING

3. Two examples

8. On de duplication of query result set - distinct

8. Summarize the execution order of DQL statements

9. ※ connection query

1) Classification

2) Cartesian product phenomenon

3) Table alias

4) Internal connection - equivalent connection

5) Internal connection - non equivalent connection

6) Internal connection - self connection

7) External connection (mostly used)

① Left and right

② Grammar

③ Examples

8) Connection query of more than three tables

10. Subquery

1) Use subqueries in the where clause

2) Use subquery after from

3) Use subquery after select

11. Usage of union

12. Usage of limit (MySql specific)

Summary: sql statement execution order

9, Build table

1. Syntax format

2. Common data types

3. How to choose between char and varchar

4. Use of BLOB and CLOB

5. Naming

6. Build student list

7. Insert data into a table

8. Delete table

9. Replication of tables

10. Insert query results into a table

11. Modify data in table

12. Delete data in table

13. Modify table structure

14. Constraint

1) Non NULL constraint

2) Uniqueness constraint

3) Primary key constraint

4) Foreign key constraints (notes need to be supplemented)

10, Storage engine*

11, Transaction

1. Concept

2. Principle

3. Four features ACID

4. Transaction isolation

5. Presentation transaction

12, Index