Summary of MySQL

Time:2021-5-3

Basics

1. What are SQL, DB and DBMS and their relationship?

DB:
Database (a database that actually exists as a file on the hard disk)
DBMS:
Database management system (database management system, common are: MySQL, Oracle, DB2, Sybase, sqlserver…)
SQL:
Structured query language is a standard general language. Standard SQL statements are suitable for all databases

DBMS is responsible for executing SQL statements to operate the data in dB. DBMS – (execution) – > sql statement – (operation) – > dB

2. What is a watch?

Table is the basic unit of database, all data are organized in the form of a table, including row and column: row: called data / record, column: called field

  • What information does each field name contain: field name, data type, and related constraints

3. Learn common SQL statements, SQL statement classification:

DQL (database query language): query statements, all select statements are DQL, DML (data operation language): insert / delete / update to add, delete and modify the data in the table, DDL (data definition language): create drop alter, TCL (transaction control language): commit to commit the transaction, DCL (data control language): grant authorization, revoke authorization, etc

4. Import data from database

  • Step 1. Login to MySQL management system MySQL – U user name – P password
  • Step 2. Check which databases show databases exist( This is not a SQL statement, but a command of MySQL.)

    mysql> show databases;
    +——————–+
    | Database           |
    +——————–+
    | information_schema |
    | mysql             |
    | performance_schema |
    | sakila             |
    | sys               |
    | world             |
    +——————–+

  • Step 3. Create our own database create database bjpowernode (database name)

    mysql> show databases;
    +——————–+
    | Database           |
    +——————–+
    | information_schema |
    | bjpowernode       |
    | mysql             |
    | performance_schema |
    | sakila             |
    | sys               |
    | world             |
    +——————–+

  • Step 4. Use the database use bjpowernode;
  • Step 5. Check which tables are in the current database? show tables;
  • Step 6. Initialize the data source C:: (users, Qin, desktop, bjpowernode, SQL)
  • Step 7. After initialization, view the database

    mysql> show tables;
    +———————–+
    | Tables_in_bjpowernode |
    +———————–+
    | dept                 |
    | emp                   |
    | salgrade             |
    +———————–+
    3 rows in set (0.00 sec)

5. What is SQL script?

When the extension of a file is.sqlAnd the question gradually wrote a large number of SQL statements, we call such a file SQL script

Note: you can directly use the SQL script in a path of source to execute an SQL file and import data to a table. Before importing, you must first create a table

6. Delete database

drop database bjpowernode;

mysql> drop database bjpowernode;
Query OK, 3 rows affected (0.02 sec)

7. View the table structure

Desc table name;

mysql> desc emp;
+———-+————-+——+—–+———+——-+
| Field   | Type       | Null | Key | Default | Extra |
+———-+————-+——+—–+———+——-+
| EMPNO   | int(4)     | NO   | PRI | NULL   |       |
| ENAME   | varchar(10) | YES |     | NULL   |       |
| JOB     | varchar(9) | YES |     | NULL   |       |
| MGR     | int(4)     | YES |     | NULL   |       |
| HIREDATE | date       | YES |     | NULL   |       |
| SAL     | double(7,2) | YES |     | NULL   |       |
| COMM     | double(7,2) | YES |     | NULL   |       |
| DEPTNO   | int(2)     | YES |     | NULL   |       |
+———-+————-+——+—–+———+——-+

8. Common commands?

  • Which database are you currently using

    mysql> select database();

  • View MySQL version

    mysql> select version();

  • \C command to end an SQL statement
  • Exit command to exit mysql

9. View the SQL statement when creating the table:

Show create table name

<!– SQL statement view — >

SQL statement start

Simple query statement (DQL)

Syntax format: select field name 1, field name 2,… From table name;

Tips:

  1. Any SQL statement is marked with ‘;’ ending.
  2. SQL statements are not case sensitive.
  • Check the employee’s new year( Fields can participate in mathematical 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 |
+——–+———-+

  • Duplicate column names for query results

    select ename, sal * 12 as sal from emp;

mysql> select ename, sal * 12 as sal from emp;

  • Chinese in alias?

MySQL > select ename, Sal * 12 as’ annual salary ‘from EMP;

Note: standard SQL statements are required to be enclosed in single quotation marks instead of double quotation marks

  • Query all fields– Do not write like this in actual development*Convert to all the field names, the speed is relatively slow

    select * from emp;

Conditional query

Syntax format:

Select field 1, field 2,…. from table name where condition

Execution order: first from, then where, and finally select

  1. Query the name of the employee whose salary is equal to 5000?

select ename from emp where sal=5000;

  1. Query Smith’s salary?

select sal from emp where ename=’SMITH’;

  1. Find out the employees whose salary is greater than, equal to, less than or equal to 3000?

select ename from emp where sal>3000;
select ename from emp where sal>=3000;
select ename from emp where sal<3000;
select ename from emp where sal<=3000;

  1. Find out the employees whose salary is not equal to 3000?

select ename from emp where sal<>3000;
select ename from emp where sal!=3000;

  1. Find out the employees whose wages are between 1100 and 3000, including 1100 and 3000 and between

select ename,sal from emp where sal>=1100 and sal<=3000;
select ename,sal from emp where sal between 1100 and 3000;

Note: between… And can be used not only for numbers, but also for strings

select ename,sal from emp where ename between ‘A’ and ‘B’;

  1. Find out who doesn’t have an allowance? In the database, null is not a value, which means nothing and is empty. Null is not a value. It cannot be measured by equal sign. It must be usedis nullperhapsis not nul

–Find out who’s paying for null?
select ename,sal,comm from emp where comm is null;
–Find out who doesn’t pay for null?
select ename,sal,comm from emp where comm is not null;

  1. Find out who doesn’t have an allowance (null and 0)

select ename,sal,comm from emp where comm is  null or comm=0;

  1. Find out if the employee is manager or salesman

select ename, job from emp where job=’MANAGER’ or job=’SALESMAN’;

  1. Combined use of and and or: find out the employees whose salary is more than 1000 and whose department number is 20 or 30

select ename, sal, deptno from emp where sal>1000 and (deptno=20 or deptno=30);

Add parentheses when the priority of the operator is uncertain

  1. In operator (equivalent to or), find out the employees whose jobs are manager and salesman?

select ename, job from emp where job in(‘MANAGER’, ‘SALESMAN’);
–Equivalent to or below
select ename, job from emp where job=’MANAGER’ or job=’SALESMAN’;

Note: the two values after in represent a specific value, not an interval

  1. Not in is not one of these values, which is the opposite of in
  2. In fuzzy query, you must master two symbols, one is%One is_%Represents multiple characters,_Represents any character
  • Find the one with O in the name?

select ename from emp where ename like ‘%o%’;

  • Find out if the second letter in the name is a?

select ename from emp where ename like ‘_A%’;

  • Find the underlined (use escape character) in the name?

select ename from emp where ename like ‘%\_%’;

  • Find out that the last letter is t

select ename from emp where ename like ‘%T’;

Sort (ascending, descending)

Syntax format:

  • Select field from table name order by a field ASC (ascending) | desc (descending)

Note: the database is in ascending order by default

  1. According to the ascending order of salary, find out the employees and salary

select ename, sal from emp order by sal;

  1. Specify ascending or descending order? ASC is in ascending order and desc is in descending order.

select ename, sal from emp order by sal asc;
select ename, sal from emp order by sal desc;

  1. Case: in descending order of salary, in ascending order of name when the salary is the same?Note: when sorting by multiple fields, the higher the priority of who is in the front, the higher the priority of who is in the back. Only when the current side cannot complete the specific sorting, the sorting condition of the back will be executed

select ename, sal from emp order by sal asc;
select ename, sal from emp order by sal desc,ename asc;

  1. You can also sort by column (directly use the serial number of the column, not recommended)

select ename, sal from emp order by 1;

  1. Where mixed sort using
  • Find out the employees whose jobs are salesman and arrange them in descending order of salary

select ename, sal from emp where job=’SALESMAN’ order by sal desc;

Execution order: from — where — select — order by

Grouping function (multi line processing)

  • Count count
  • Sum
  • AVG average
  • Max Max
  • Min MinAll grouping functions operate on a set of dataThere are five grouping functions. It’s another name: multi line processing function. The feature of multi line processing function is to process multiple lines, and the final output is one line
  1. Sum a field

–The sum of wages?
select sum(sal) from emp;
–Find out the maximum wage
select max(sal) from emp;
–Find out the minimum wage
select min(sal) from emp;
–Find out the average wage
select avg(sal) from emp;
–Find out the total number of people
select count(*) from emp;

be careful:

  1. The grouping function automatically ignores that the field isNUllData for

Question: find employees who are paid more than average?

select ename,sal from emp where sal>avg(sal);

<!– Error 1111 (HY000): invalid use of group function — >

Thinking about why to report an error: invalid use of the grouping function, the reason is: in SQL statements, the grouping function can not be directly used in where sentences. Why? How to explain: because group by is executed after where

<!– The execution order is — >

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

So how to deal with this demand: find out the employees whose wages are higher than the average?

–Step 1: find out the average wage
select avg(sal) from emp;
–Step 2: find out the employees whose salary is higher than the average
Select enable, Sal from EMP where Sal > the result of the previous step

Merge the above two steps and use subquery

select ename,sal from emp where sal > (select avg(sal) from emp);

<!– ======== Split line = = = = — >

  • What's the difference between count (*) and count (a specific field)?

    • Count (*): is the total number of records
    • Count: counts the total number of data whose current field is not null
  • Grouping functions can also be used in combination

select count(*),sum(sal),avg(sal) from emp;

Group by and having

  • Group by: group by a field or fields.
  • Having: filtering the grouped data again.

Case 1. Find out the highest salary for each job

select max(sal),job from emp group by job
+———-+———–+
| max(sal) | job       |
+———-+———–+
|  3000.00 | ANALYST   |
|  1300.00 | CLERK     |
|  2975.00 | MANAGER   |
|  5000.00 | PRESIDENT |

 1600.00 SALESMAN

Case 2. Find out the highest salary of each job and sort it

mysql> select max(sal),job from emp group by job order by max(sal) desc;
+———-+———–+
| max(sal) | job       |
+———-+———–+
|  5000.00 | PRESIDENT |
|  3000.00 | ANALYST   |
|  2975.00 | MANAGER   |
|  1600.00 | SALESMAN |

 1300.00 CLERK    

Note: grouping functions are usually used in combination with group by, which is why they are called grouping functions. Moreover, any grouping function (min, AVG, Max, sum, count) will be executed only after the group by statement is executed. If there is no group by in an SQL statement, the data of the whole table will be grouped by itself

be careful:select ename,max(sal),job group by job;When select and group by are used together, the search field must be the field and grouping function following group by grouping, otherwise the above syntax will report an error again

Rule: when there is group by in a query statement, select can only be followed by grouping functions and fields participating in grouping

  1. Case 3: average salary per position

mysql> select job,avg(sal) from emp group by job;
+———–+————-+
| job       | avg(sal)   |
+———–+————-+
| ANALYST   | 3000.000000 |
| CLERK     | 1037.500000 |
| MANAGER   | 2758.333333 |
| PRESIDENT | 5000.000000 |

SALESMAN 1400.000000
  1. Can multiple fields be grouped together?

Case 4: find out the highest salary for different positions in each department.

mysql> select deptno,job, avg(sal) from emp group by deptno,job;
+——–+———–+————-+
| deptno | job       | avg(sal)   |
+——–+———–+————-+
|     10 | CLERK     | 1300.000000 |
|     10 | MANAGER   | 2450.000000 |
|     10 | PRESIDENT | 5000.000000 |
|     20 | ANALYST   | 3000.000000 |
|     20 | CLERK     |  950.000000 |
|     20 | MANAGER   | 2975.000000 |
|     30 | CLERK     |  950.000000 |
|     30 | MANAGER   | 2850.000000 |

    30 SALESMAN 1400.000000

Case 5 finds out the highest salary of each department, and requires to display data with salary greater than 2900

–Step 1: find out the highest salary for each department of Mei
mysql> select max(sal),deptno from emp group by deptno;
+———-+——–+
| max(sal) | deptno |
+———-+——–+
|  5000.00 |     10 |
|  3000.00 |     20 |
|  2850.00 |     30 |
+———-+——–+
–Step 2: find out the employees whose salary is more than 2900 (the following SQL is inefficient)
mysql> select max(sal),deptno from emp group by deptno having max(sal)>2900 ;
+———-+——–+
| max(sal) | deptno |
+———-+——–+
|  5000.00 |     10 |
|  3000.00 |     20 |
+———-+——–+
–To solve the problem of inefficient SQL in the second step, use where to filter it out first. If you can use where to solve it, don’t use having
mysql> select max(sal),deptno from emp where sal>2900 group by deptno;
+———-+——–+
| max(sal) | deptno |
+———-+——–+
|  5000.00 |     10 |

 3000.00     20

In case 6, find out the average salary of each department, and require to display the data of salary more than 2000

mysql> select max(sal),deptno from emp group by deptno having avg(sal)>2000;
+———-+——–+
| max(sal) | deptno |
+———-+——–+
|  5000.00 |     10 |

 3000.00     20

How to write a complete DQL statement?

select

from

where

group by
….
having
….
order by
….

Distinct de duplication

    1. On the de duplication of query result set?

–Distinct removes duplicate records and can be associated with multiple fields
select distinct job from emp;

  1. Count the number of Posts

–Distinct removes duplicate records and can be associated with multiple fields
select count(distinct job) from emp;
+———————+
| count(distinct job) |
+———————+

                  5

join query

<!– Understanding — >

What is connection query?

In the actual development, most of the cases do not query data from tables. Generally, multiple tables are jointly queried to get the final result. In the actual development, a business usually corresponds to multiple tables, such as students and classes, with at least two tables. That is, relational database table

Table 2 division of connection mode:

Internal connection:
Equivalent connection
Equivalent connection
Self connect
External connection
Left outer link
Right outer link

There is a phenomenon called Cartesian product in join query of table

Cartesian product phenomenon: when two tables are connected to query, there is no restriction, and the final query result is the product of two tablesCase: find out the Department name of each employee, require to display the employee name and department <– Table aliases — what are the benefits of table aliases? First, high efficiency of execution; second, good readability

select e.ename, d.dname from emp e,dept d;

How to avoid Cartesian product? Of course, it’s home conditions.

Thinking: will avoiding Cartesian product reduce the number of records matching? No, it’s 56 times. It’s just a valid record.

Case: find out the Department name of each employee, and require the employee name and department to be displayed

mysql> select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;
+——–+————+
| 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

Equivalent connection of 5 internal connections

The most important feature is that the condition is equivalent.

Case: query the Department name of each employee and display the employee name and department name.

//Inner can be omitted. With the purpose of inner, the readability is better.

select 
    e.ename,d.dname
from
    emp e
\[inner\] join
    dept d
on
    e.deptno = d.deptno;

Grammar:
    ...
        A
    join
        B
    on
        Connection conditions
    where
        ...
        
+--------+------------+
| 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      |
+--------+------------+

The above writing method is sql99. The syntax structure of sql99 is clearerThe join condition of the table is separated from the later where condition.

6. Non equivalent connection of internal connection

The most important feature is that the relationship in the connection condition isIt’s not equal.

Case: find out the salary grade of each employee, and require to display the name, salary and salary grade of the employee.

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

7 self connect

The main features are as follows:A table is regarded as two tables, connecting itself.

Case: find out the superior leader of each employee, and ask to display the employee name and corresponding leader name.

Analysis: the following table is the employee table

empno ename mgr
7369 SMITH 7902
7499 ALLEN 7698
7521 WARD 7698
7566 JONES 7839
7654 MARTIN 7698
7698 BLAKE 7839
7782 CLARK 7839
7788 SCOTT 7566
7839 KING NULL
7844 TURNER 7698
7876 ADAMS 7788
7900 JAMES 7698
7902 FORD 7566
7934 MILLER 7782

Mgr is the leader of the employee. We need to find the name corresponding to the job number through the job number. At this time, one table is regarded as two tables.

Employee’s leader number = leader’s employee number

select

a. Ename as' employee name ', B. ename as' leader name'

from

emp a

inner join

emp b

on

a.mgr = b.empno;
Employee name Leader’s name
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

8 external connection

The most important feature of outer join is that all the data in the main table can be queried unconditionally.

What is external connection and what is the difference between internal connection and external connection?

Internal connection:

Suppose tables a and B are connected. If inner connection is used, if table a and table B can match the records on the table, it is inner connection. ​There is no difference between the two tables. The two tables are equal.

External connection:

Suppose tables a and B are connected. If external connection is used, tables AB and B are connectedThere is a table that is the main table and a table that is the secondary table. It mainly queries the data in the main table, with the secondary tableWhen the data in the secondary table does not match the data in the primary table, the secondary table automatically simulates null matching.

What is the classification of external connections?

Left outer join (left join): indicates that the table on the left is the main table. Right outer join (right join): indicates that the table on the right is the main table.

Case: find out the superior leader of each employee( All employees must find out)

Analysis: compared with the previous question, the previous question also inquired about the leaders of employees, but only 13 records were inquired. Why? Because King’s is the highest leader, and the superior leader is null, but we don’t want to do this. As an employee, we also want him to show it, which requires external connection.

External connection: (left external connection / left connection)
select

a. Ename 'employee', B. ename 'leader'

from

emp a

left join

emp b

on

a.mgr = b.empno;
staff leader
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

We noticed that there were 14 records, and King’s leadership was null

How to connect three tables for query?

Case: find out the Department name and salary grade of each employee.

EMP e
empno ename sal deptno
7369 SMITH 800.00 20
7499 ALLEN 1600.00 30
7521 WARD 1250.00 30
7566 JONES 2975.00 20
7654 MARTIN 1250.00 30
7698 BLAKE 2850.00 30
7782 CLARK 2450.00 10
7788 SCOTT 3000.00 20
7839 KING 5000.00 10
7844 TURNER 1500.00 30
7876 ADAMS 1100.00 20
7900 JAMES 950.00 30
7902 FORD 3000.00 20
7934 MILLER 1300.00 10
DEPT d
DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SALGRADE s
GRADE LOSAL HISAL
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999

A join b join C onTable a and table B are connected first, and then table a continues to connect with table C.

select 
    e.ename,d.dname,s.grade
from
    emp e
join
    dept d
on
    e.deptno = d.deptno
join
    salgrade s
on
    e.sal between s.losal and s.hisal;

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

Subquery

1. What is a subquery? Where can subqueries appear?

Select statements are nested in select statements, and the nested select statements are subqueries. Where can subqueries appear?

select
    ..(select).
from
    ..(select).
where
    ..(select).

2. Using subqueries in where clause

Case study: find out the information of employees with higher than average salary

select * from emp where sal > avg(sal);
//Wrong way to write. You can’t use grouping function directly after where.

select * from emp where sal > (select avg(sal) from emp);

3. Nested subquery after from

Case study: find out the average salary level of each department

Step 1: find out the average salary of each department (group by department number, find the average value of SAL)

select deptno,avg(sal) as avgsal from emp group by deptno;

deptno avgsal
10 2916.666667
20 2175.000000
30 1566.666667

Step 2: take the above query result as a temporary table t, and let the T table join the salgrade s table with the condition of t.avgsal between s.losal and s.hisal

select

t.\*,s.grade

from

(select deptno,avg(sal) as avgsal from emp group by deptno) t

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

4. Nested subquery after select.

Case: find out the name of each employee’s Department, and ask to display the employee name and department name.

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

There is a better solution to this problem

select

e.ename,d.dname

from

emp e

join

dept d

on

e.deptno = d.deptno;

Union (can add query result sets)

Case: find out the employees whose jobs are salesman and manager?

The first one is the first one

select ename,job from emp where job = ‘MANAGER’ or job = ‘SALESMAN’;

Second:

select ename,job from emp where job in(‘MANAGER’,’SALESMAN’);

Third: Union connection

select ename,job from emp where job = ‘MANAGER’
union
select ename,job from emp where job = ‘SALESMAN’;

ename job
JONES MANAGER
BLAKE MANAGER
CLARK MANAGER
ALLEN SALESMAN
WARD SALESMAN
MARTIN SALESMAN
TURNER SALESMAN

Data in two unrelated tables are displayed together?

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

Here is to mention: if the length of the query field is the same, it will be divided into two columns. If not, it will be combined into one column, as shown in the figure above.

mysql> select ename,sal from emp

-> union
-> select dname from dept;

ERROR 1222 (21000): The used SELECT statements have a different number of columns

If the number of two fields synthesized by union is different, an error will be reported directly.

Limit (for paging)

(it’s the key point in the key points, and it’s all for paging queries in the future.)

5.1 the role of limit

Take part of the data in the result set

5.2. Syntax mechanism: limit StartIndex, length StartIndex represents the starting position, starting from 0, and 0 represents the first data. Length indicates how many to take

Case: take out the top 5 employees (idea: take the top 5 employees in descending order)

select ename,sal from emp order by sal desc;
Take the first five:
    select ename,sal from emp order by sal desc limit 0, 5;
    select ename,sal from emp order by sal desc limit 5;

5.3 limit is the last step of SQL statement execution

select 5

...

from 1

...        

where 2

...    

group by 3

...

having 4

...

order by 6

...

limit 7

...;

5.4. Case: find out the employees whose salary ranks from the 4th to the 9th?

select ename,sal from emp order by sal desc limit 3,6;
+--------+---------+
| ename  | sal     |
+--------+---------+
| JONES  | 2975.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
+--------+---------+

5.6. General standard paging SQL?

3 records per page:
Page 1: 0, 3
Page 2: 3, 3
Page 3: 6, 3
Page 4: 9, 3
Page 5: 12, 3

PageSize records are displayed on each page
Page pageno: (pageno – 1) * PageSize, PageSize

What is PageSize? How many records are displayed on each page
What is pageno? What page is displayed

Table creation

1. Syntax format of table creation statement:

Create table name(

Field name 1 data type,
        Field name 2 data type,
        Field name 3, data type,
        ....
    );

2. About the data types of fields in MySQL? Here are just the common ones

Int integer type (int in Java)
    Bigint long integer (long in Java)
    Float floating point (float double in Java)
    Char fixed length string (string)
    Varchar variable length string (StringBuffer / StringBuilder)
    Date date type (corresponding to Java. SQL. Date type in Java)
    Blob binary large object (storing image, video and other streaming media information)
    CLOB character large object (store large text, for example, 4G string) Character large object (corresponding to object in Java)
    ......

How to choose char and varchar?

  • In the actual development, when the data length in a field does not change, it is fixed. For example, char is used for gender, birthday, etc.
  • When the data length of a field is uncertain, such as introduction, name, etc., varchar is used.

Case: create student table: student information includes: student number, name, gender, class number, birthday student number: bigint Name: varchar gender: char class number: int birthday: char

create table t_student(

        no bigint,
        name varchar(255),
        sex char(1),
        classno varchar(255),
        birth char(10)
    );

Addition, deletion and modification of tables

Insert statement inserts data:

Syntax format:

Insert into table name (field name 1, field name 2, field name 3,….) values (value 1, value 2, value 3,….)

Requirements: the number of fields is the same as the number of values, and the data type should be the same.

Note: when an insert statement is executed successfully, there will be one more row of records in the table. Even if some of the fields in this row of records are null, there is no way to insert data in the insert statement in the later stage, so you can only use update to update.

Fields can be omittedBut the following values require both quantity and order.

insert into t_student values(1,’jack’,’0′,’gaosan2ban’,’1986-10-23′);

Insert multiple rows of data at once

insert into t_student

    (no,name,sex,classno,birth) 
values        
    (3,'rose','1','gaosi2ban','1952-12-14'),                     (4,'laotie','1','gaosi2ban','1955-12-14');

Replication of tables

Syntax: create table name as select statement; Create the query result as a table.

Modify data: update

Syntax format:

Update table name, set field name 1 = value 1, field name 2 = value 2… Where condition;

Note: there is no condition to update all the data in the whole table.

Case: change the LOC of department 10 to Shanghai and the name of department to renshibu

update dept1 set loc = 'SHANGHAI', dname = 'RENSHIBU' where deptno = 10;
mysql> select \* from dept1;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | RENSHIBU   | SHANGHAI |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     10 | RENSHIBU   | SHANGHAI |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

Delete data

Syntax format:

Delete from table name where condition;

Note: there is no condition to delete all.

Delete all records?

delete from dept1;

How to delete data in a large table( Key points)

Truncate table name// The table is truncated and cannot be rolled back. Permanent loss.

Delete table

Drop table name// This is universal.

There is a term for adding, deleting, modifying and querying: crud operation Create retrieve update delete

constraint

1. What are constraints? What are the common constraints?

When creating a table, you can add corresponding constraints to the fields of the table. The purpose of adding constraints is to ensure the validity, validity and integrity of the data in the table. What are the common constraints? Non NULL constraint: the constrained field cannot be null; unique constraint: the constrained field cannot be duplicate; primary key constraint: the constrained field cannot be null or duplicate (PK for short); foreign key constraint: (FK for short) check constraint: note that Oracle database has check constraint, but MySQL does not, Currently, MySQL does not support this constraint.

2. Non NULL constraint (not null)

create table t_user(

    id int,
    username varchar(255) not null,
    password varchar(255)
);

3. Uniqueness constraint

The field modified by the unique constraint is unique,It can’t be repeated. But it can be null

Case: add unique to a column

drop table if exists t\_user;
    create table t\_user(
        id int,
        Username varchar (255) unique // column level constraint
    );
    insert into t\_user values(1,'zhangsan');
    insert into t\_user values(2,'zhangsan');
    ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'username'

Case study: adding unique to two or more columns

drop table if exists t\_user;
    create table t\_user(
        id int, 
        usercode varchar(255),
        username varchar(255),
        Unique (usercode, username) // join multiple fields to add a constraint unique [table level constraint]
    );

In this case, the two fields are not unique.

Note: the not NULL constraint has only column level constraints. There are no table level constraints.

4. Primary key constraint

create table t_user(

Id int primary key, // column level constraint
        username varchar(255),
        email varchar(255)
    );
    insert into t\_user(id,username,email) values(1,'zs','[email protected]');
    insert into t\_user(id,username,email) values(2,'ls','[email protected]');
    insert into t\_user(id,username,email) values(3,'ww','[email protected]');
    select \* from t\_user;
    +----+----------+------------+
    | id | username | email      |
    +----+----------+------------+
    |  1 | zs       | [email protected] |
    |  2 | ls       | [email protected] |
    |  3 | ww       | [email protected] |
    +----+----------+------------+

Primary key related terms

Primary key constraint: primary key primary key field: after adding primary key to ID field, ID is called primary key field primary key value: every value in ID field is primary key value.

What’s the use of primary keys?

There are requirements in the three paradigms of table design. The first paradigm requires that any table should have a primary key.

The primary key value is the unique identifier of this row of records in this table

Classification of primary key

It is divided according to the number of primary key fields

Single primary key(Recommended, commonly used)Composite primary key (multiple fields are combined to add a primary key constraint) (composite primary key is not recommended because it violates three normal forms.)

According to the nature of the primary key

Natural primary key: the primary key value should be a natural number that has nothing to do with the business(This is recommendedBusiness key: the primary key value is linked to the system’s business. For example, the key card is the number of the bank card, and the ID number is the primary key. Not recommended)It’s better not to take the field linked to the business as the primary key. Because once the business changes in the future, the primary key value may also need to change, but sometimes there is no way to change, because the change may cause the primary key value to repeat.

Primary key auto increment

MySQL provides self incrementing primary key values: (very important.)

drop table if exists t\_user;
    create table t\_user(
        id int primary key auto\_ The increment, // ID field automatically maintains a self incrementing number, starting from 1 and incrementing by 1.
        username varchar(255)
    );

Foreign key constraint

Writing format

Foreign key (field name) references parent table (field name)

Terms related to foreign key constraints

Foreign key constraint: foreign key field: add the field with foreign key constraint; foreign key value: each value in the foreign key field.

Background: please design a database table to maintain the information of students and classes? The first solution: a table stores all the data

no(pk)

name

classno

classname

1

zs1

101

Class 1, grade 3, Yizhuang No.2 Middle School, Daxing Economic and Technological Development Zone, Beijing

2

zs2

101

Class 1, grade 3, Yizhuang No.2 Middle School, Daxing Economic and Technological Development Zone, Beijing

3

zs3

102

Class 2, grade 3, Yizhuang No.2 Middle School, Daxing Economic and Technological Development Zone, Beijing

4

zs4

102

Class 2, grade 3, Yizhuang No.2 Middle School, Daxing Economic and Technological Development Zone, Beijing

Disadvantages: redundancy【 [not recommended]

The second scheme: two tables (class table and student table) t_ Class table CNO (PK) CNAME


101 Beijing Daxing District Economic and Technological Development Zone Yizhuang No.2 High School No.3 class 1 102 Beijing Daxing District Economic and Technological Development Zone Yizhuang No.2 High School No.2 class 2

t_ Student table “SnO (PK) sname classno”


1 zs1 101 ​ 2 zs2 101 ​ 3 zs3 102 ​ 4 zs4 102 ​ 5 zs5 102

Sequence requirements:When deleting data, delete the child table first, and then delete the parent table. When adding data, first add the parent table, then add the child table. When creating a table, the parent table is created first, and then the child table is created. When deleting a table, delete the child table first, and then delete the parent table.

drop table if exists t_student;

    drop table if exists t\_class;

    create table t\_class(
        cno int,
        cname varchar(255),
        primary key(cno)
    );

    create table t\_student(
        sno int,
        sname varchar(255),
        classno int,
        primary key(sno),
        foreign key(classno) references t\_class(cno)
    );

The foreign key can be null, and the referenced field is not necessarily a primary key, but at least has a unique constraint.

Storage engine

<!– Understanding — >

MyISAM

MyISAM does not support transactions. MyISAM is the most commonly used storage engine of MySQL, but this engine is not the default. MyISAM uses three files to organize a table: xxx.frm (file in storage format) xxx.myd (file to store data in the table) xxx.myi (file to store index in the table)It can be compressed to save storage space. And it can be converted to read-only table to improve retrieval efficiency.Disadvantages:Transaction is not supported.

InnoDB

Advantages: support transaction, row level lock, foreign key, etc. The data security of this storage engine is guaranteed.

The structure of the table is stored in the xxx.frm file, and the data is stored in the table space (logical concept), which cannot be compressed and converted to read-only. This kind of InnoDB storage engine is provided after MySQL database crashAutomatic recovery mechanism. InnoDB supportCascade delete and update

Transaction

What is a business?

A transaction is a complete business logic unit and cannot be further divided.

To ensure that several DML statements succeed or fail at the same time, we need to use the “transaction mechanism” of the database.

Transaction related statements

DML statement( insert delete update)

Why? Because these three statements are related to the “data” in the database table. Transactions exist forEnsure the integrity and security of data

Characteristics of transactions

Transaction includes four characteristics: acid A: atomicity: transaction is the smallest unit of work and cannot be further divided. C: Consistency: a transaction must ensure that multiple DML statements succeed or fail at the same time. 1: Isolation: there is isolation between transaction a and transaction B. D: Persistence: persistence means that the final data must be persisted to the hard disk file before the transaction can be considered successful.

On the isolation between transactions

There are four isolation levels for transaction isolation. Theoretically, there are four isolation levels: the first level: read uncommitted. The other party’s transaction has not been submitted, and our current transaction can read the uncommitted data. Dirty read exists when the read is not submitted: it indicates that dirty data has been read. Level 2: read committed: we can read the data after the other party’s transaction is committed. This isolation level is solved: dirty reading is gone. The problem with read committed is that it cannot be read repeatedly. The third level: repeatable read. This isolation level solves the problem of non repeatable read. The problem with this isolation level is that the data read is phantom. Level 4: serializable / serializable solves all the problems. Low efficiency. Transaction queuing is required.

The default isolation level of Oracle database is read submitted. The default isolation level of MySQL database is: repeatable read.

Indexes

What is an index

Abbreviate the scope of scanning to improve retrieval efficiency.

Note: Although the index can improve the retrieval efficiency, you can’t add the index at will, because the index is also the object in the database, and it also needs the continuous maintenance of the database. There are maintenance costs. For example, the data in the table is often modified, so it is not suitable to add an index, because once the data is modified, the index needs to be reordered and maintained.

How to create index objects? How to delete index objects?

Create index object:
        Create index index name on table name (segment name);
    Delete index object:
        Drop index index name on table name;

When to consider adding an index to a field?

  • There is a huge amount of data( According to the needs of customers, according to the online environment)
  • This field has few DML operations( Because the field is modified, the index also needs to be maintained.)
  • This field often appears in the where clause( According to which field do you often query)

    Primary keys and fields with unique constraints are automatically indexed

The realization principle of index

Through B tree to narrow the scanning range, the underlying index is sorted and partitioned. The index will carry the “physical address” of the data in the table. Finally, after the data is retrieved through the index, the associated physical address is obtained, and the data in the table is located through the physical address. The efficiency is the highest. select ename from emp where ename = ‘SMITH’; Through index conversion: select enable from EMP where physical address = 0x3;

In fuzzy query, the first wildcard is% and the index is invalid.

For example:

select ename from emp where ename like ‘%A%’;

Three paradigms of database design

a key

The first paradigm: any table should have a primary key, and each field is atomic.

The second paradigm: Based on the first paradigm, all non primary key fields are completely dependent on the primary key, and can not produce partial dependence.

The third paradigm: Based on the second paradigm, all non primary key fields directly depend on the primary key, and cannot produce transitive dependency.

Many to many? Three tables, relation table and two foreign keys.

One to many? Two tables, more tables with foreign keys.