first day

1. What is a database? What is a database management system? What is sql? What is the relationship between them?

Database:
The English word DataBase is called DB for short. A combination of files that store data in a format.
As the name suggests: a warehouse for storing data is actually a pile of files. These files store data in a specific format.

Database management system:
DataBaseManagement, or DBMS for short
The database management system is used to add and delete data in the database management system.

Common database management systems:
Mysql, Oracle, MS SqlServer, DB2, sybase, etc

SQL: Structured Query Language
Programmers need to learn SQL statements. Programmers write SQL statements, then DBMS is responsible for executing SQL statements, and finally complete the operation of adding, deleting, modifying and querying data in the database
SQL is a set of standards. Programmers mainly learn SQL statements. This SQL can be used in mysql, Oracle and DB2.
The relationship between the three?
DBMS – execute – > sql – operate – > DB

First install the database management system MySQL, and then learn how to write SQL statements. After writing SQL statements, the DBMS executes the SQL statements, and finally completes the data management of the database.

2. Install MySQL database management system

matters needing attention:
Port number:
Port number is the only representative of any software / application. The port number is usually together with the ip address. The ip address is used to locate the computer. The port number port is used to locate a service / Application on the computer!
On the same computer, the port number cannot be repeated and is unique.
When the Mysql database is started, the default port number occupied by this service is 3306.
Character encoding method:
Set the character encoding method of mysql database to utf8
Service Name:
The default is Mysql, which does not need to be changed.
Select the configuration environment variable path, or configure it manually.
While setting the password, you can activate the remote access of the root account.
Activate: indicates that the root account can log in from other places.
Inactive: indicates that the root account can only be used on this machine.

Perfect uninstall of Mysql database

1. Double click the installation package to delete it.
2. Delete the directory, the Mysql folder of Program (X86) under Disk C, and the Mysql directory under ProgramData.

Mysql services

Start, pause, automatic, default configuration.

In the windows operating system, how to use commands to start and close mysql services?

net stop service name (MySQL) (stop service)
net start service name (MySQL) (start service)
The above commands can be used for the start and stop of other services.

Sign in

1. (display password form)
   cmd command line: mysql -uroot -p123456
2. (hidden password form)
   cmd command line: mysql -uroot -p

Common commands (case insensitive)

1. Exit mysql: exit
2. Check the databases in mysql: show databases;
   (mysql comes with four databases by default)
3. Select a database to use: use database name;
4. Create database: create database database name;
5. View the tables under a database: show tables;
6. Don't look at the table data, just look at the table structure: desc table name;
7. String char in Java
8. Do not execute without semicolon.
9. Termination: \ c
10. View Mysql database version number: select version();
11. View the current database: select database;

What is a table? Why use tables to store data?

1. The most basic unit in the database is the table: table
2. The data in the database is represented in the form of tables. Because the table is more intuitive.
3. Any table has row s and columns. Rows are called data / records. column: called a field.
4. Each field has attributes such as field name, data type and constraint.
5. Data type: string, number, date....
6. Constraints: there are many constraints, one of which is called uniqueness constraint. After this constraint is added, the data in this field cannot be repeated.

About the classification of SQL statements?

There are many sql statements. It's best to classify them so that they are easy to remember.

• DQL: Data Query Language (all query statements with select keyword) select
• DML: data operation language (DML is used for adding, deleting and modifying the data in the table) insert add delete update modify
• DDL: Data Definition Language (all with create, drop and alter are DDL). DDL mainly operates on the structure of the table, not the data in the table. Create: create, which is equivalent to add drop: delete alter: modify. This addition, deletion and modification is different from DML. This is mainly to operate the table structure.
• TCL: transaction control language, including transaction submission: commit; Transaction rollback: rollback;
• DCL: data control language; For example: grant authorization, revoke permission, revoke

Data import

• (import data) input command mode: source path (Chinese is not allowed in the path)

1. dept is the Department table
2. emp is the employee table
3. salgrade is the salary scale

• How to view data in a table?
  • select * from table name;

Simple query

• Query a field select field name from table name;
• Note:
• select and from are both keywords.
• Field names and table names are identifiers.
• Stress: sql statements are not case sensitive.
• Query two or more fields: separated by commas.
• To query all fields, you can write each field or use the * sign

Alias query columns

• You can use the as keyword

SELECT DEPTNO,DNAME AS DEPTNAME FROM DEPT;

• You can use spaces instead
• You can add single / double quotation marks
• Note: in all databases, strings are enclosed in single quotation marks. Single quotation marks are standard. Double quotation marks are not used in oracle database, but they can be used in Mysql.
• The alias is Chinese, enclosed in single quotation marks.

Condition query

• select field 1, field 2, field 3....
• from table name
• where conditions;

1. Use between... And... Follow small left and large right
2. between... and... Closed interval
3. Is not null is not null
4. Null cannot be measured with an equal sign in the database. To use is null, because null in the database represents nothing. It is not a value, so it cannot be measured by the equal sign.

• and also
• Or or
• And and or appear at the same time, and has higher priority. If you want or to execute first, you need to add parentheses. If you are not sure about the priority, you need to add parentheses.
• In contains, which is equivalent to multiple and or (not in is not in this range)
• Note: in is not an interval. In is followed by a specific value.
• not can take non and is mainly used in is or in
• like appellation fuzzy query, support% or underscore matching
• %: match any number of characters
• Underscore: any character
• Find the underlined name. Because the underline represents specific content, you need to add a slash \, to escape.

sort

• order by (default ascending order)
• desc is in descending order, which specifies the sort by, such as salary desc;
• asc is in ascending order, as above.

Sorting multiple fields

• For example: when querying employee name and salary, it is required to be arranged in ascending order of salary. If the salary is the same, it will be arranged in ascending order of name.

select ename,sal
from emp
order by sal asc,ename asc;
//sal comes first and plays the leading role. Enable enable ename sorting only when sal is equal

Understanding: sorting by field position

select ename,sal 
from emp
order by 2;
//2 indicates the second column. The second column is currently sal, which is sorted according to the second column sal of the query result.
//Just learn about it. It is not recommended to write this in development because it is not robust.
</br>
//The order of columns is easy to change. After the order of columns is changed, 2 will cost

format

select 
    ...
from 
    ...
where 
    ...
order by 
    ...;
    
Step 1: from
 Step 2: where
 Step 3: select
 Step 4: order by(Sorting is always performed last!)

Data processing function (single line processing function)

• Data processing function is also called single line processing function
  • Characteristics of single line processing function: one input corresponds to one output.
  • The opposite of single line processing function is multi line processing function. (multi line processing function features: multiple inputs, corresponding to one output!)
  • How many inputs, and finally how many outputs, are the characteristics of single line processing functions.
• What are the common single line processing functions
  • lower: convert to lowercase
  • upper: convert to uppercase
  • substr (intercepted string, starting subscript, intercepted length): take substring (starting subscript starts from 1)
  • concat: function to splice strings
  • Length: take the length
  • trim: remove spaces
  • Round: round
  • rand: generate random number
  • ifnull: null handling function, which is specially used to handle null. (null can be converted to a specific value)
  • case...when...then...when...then...else...end
  • str_to_date: converts a string to a date
  • date_format: format date
  • format: set the thousandth

Example:
    1. Convert to lowercase: lower
    select lower(ename) as ename from emp;
    
    2. Convert to capital: upper
    select upper(ename) as ename from emp;
    
    3. Substring: substr
    Find out what the first letter of the employee's name is A Employee information?
    3.1 The first way(Fuzzy query):
    select ename
    from emp
    where ename like 'A%';
    3.2 The second way(substr function):
    select ename
    from emp 
    where substr(ename,1,1) = 'A';
    
    4. Capitalize?
    4.1. select name from t_student;
    4.2. select upper(substr(name,1,1)) form t_student;
    4.3. select substr(name,2,length(name)-1) from t_student;
    4.4. select concat(upper(substr(name,1,1)),substr(name,2,length(name)-1)) as result from t_student;
    
    5. String splicing: concat
    select concat(empno,ename) from emp'

    6. Take length: length
    select length(ename) enamelength from emp;

    7. Remove spaces: trim
    7.1 select * 
        from emp
        where ename = '     KING';
    7.2 select *
        from emp
        where ename = trim('     KING');
    
    8. rounding: round
    8.1 select field from Table name;
        select ename from emp；
    8.2 select 'abc' from emp；//select is directly followed by "literal amount / literal value" 
        select abc from emp；//This will definitely report an error, because you will take abc as the name of a field and look for the abc field in the emp table
        select 1000 as num from emp；//1000 is also regarded as a literal quantity / literal value.
    8.3 Conclusion: select It can be followed by the field name of a table (which can be regarded as variable name) or literal quantity/Literal value (data).
    8.4 round
    8.5 select round(1236.567,0) as result from emp;//Keep 0 decimal places
        select round(1236.567,1) as result from emp;//Keep 1 decimal place
        select round(1236.567,-1) as result from emp;//Retain - 1 decimal place, and the result is 1240
        select round(1236.567,-2) as result from emp;//Retain - 2 decimal places, and the result is 1200;
        
    9. Generate random number:rand()
    9.1 select round(rand()*100,0) from emp;//Random number within 100
    
    10. Empty processing function: ifnull
    10.1 In all databases, as long as there is null Participate in the mathematical operation, and the final result is null. To avoid this phenomenon, you need to use ifnull Function.
        ifnull Usage of function: ifnull(Data, as which value)//If the 'data' is null, which value should the data be treated as.
    10.2 select ename,sal + comm as salcomm from emp;//With null
    10.3 select ename,(sal+comm)*12 as yearsal from emp;//Calculating the annual salary of each employee is still null
    10.4 select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;
    
    11. case..when..then..when..then..elas..end
    11.1 When an employee's job is MANAGER At the time of, the salary was raised by 10%，When the job is SALESMAN At the time of, wages were raised by 50%,Other normal.
        (be careful:Do not modify the database, but display the query result as salary increase)
    11.2 select ename,job,sal from emp;
         select 
            ename,
            job,
            sal as oldsal,
            (case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal
         from 
            emp;

Grouping function (multiline processing function)

• Characteristics of multi line processing function: input multiple lines and finally output one line.
• Note: grouping functions must be grouped before they can be used.
  If there is no group of data, the default is to group the whole table.
• Multiline processing function:
  • Count: count
  • sum: sum
  • avg: average value
  • max: maximum
  • min: minimum

1. Maximum wage: max
    select max(sal) from emp;
2. Minimum wage: min
    select min(sal) from emp;
3. Salary and: sum
    select sum(sal) from emp;
4. Average salary: avg
    select avg(sal) from emp;
5. Statistical quantity: count
    select count(ename) from emp;

• The grouping function automatically ignores null. You don't need to deal with null in advance.
• The difference between the specific grouping (count) and the function (count) (*)?
  • count (specific field): indicates the total number of non null elements in this field.
  • count (*): the total number of rows in the statistical table.
    (each row of records cannot be null. If one column in a row of data is not null, this row of data is valid)
• Grouping functions cannot be used directly in the where clause
  • select ename,sal from emp where sal > min(sal);// Error reporting method
• All grouping functions can be combined and used together.
  • select sum(sal),min(sal),max(sal),avg(sal),count(sal) from emp;

Grouping query

• What is group query?
  • In practical applications, there may be such a demand. It is necessary to group first, and then operate each group of data. At this time, we need to use group query. How to do group query?

1. Combine all the previous keywords together to see their execution order?
select
    ...
from
    ...
where 
    ...
group by//grouping
    ...
order by//sort
    ...
    
2. The order of the above keywords cannot be reversed and needs to be remembered.
    2.1 Execution sequence.
        1. from
        2. where
        3. group by
        4. select
        5. order by

3. Why can't grouping functions be used directly in where behind?
    3.1 select ename,sal from emp where sal > min(sal);//report errors
    </br>
    3.2 Because the grouping function can only be used after grouping.
        where At the time of execution, there is no grouping, so where Grouping function cannot appear after.
    3.3 select sum(sal) from emp;
        This is not grouped. Why sum()Function can be used?
        because select stay group by Then execute.

• ※ key conclusions ※:
  • In a select statement, if there is a group by statement, the select can only be followed by the fields participating in the grouping and the grouping function.
  • Using having can further filter the data after grouping.
  • having cannot be used alone. having cannot replace where. having must be used in combination with group by.
  • Optimization strategy: where and having. Give priority to where. Where can't be completed. I'm choosing having.

Summary

• The single table query is finished here

1. ※Emphasize again※
    select ...
    from ...
    where ...
    group by ...
    having ...
    order by ...
    The above keywords can only be in this order and cannot be reversed.

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

3. Query data from a table through where Filter out valuable data by conditions, and group these valuable data, which can be used after grouping having Continue filtering, select Find it and sort it out at last.

4.example:
    Find out the average salary of each position. It is required to display the average salary greater than 1500, except MANAGER In addition to the position, it is required to be arranged in descending order according to the average salary.
    select
        job,avg(sal) as avgsal
    from
        emp 
    where 
        job <> 'MANAGER'
    group by
        job
    having
        avg(sal) > 1500
    order by
        avgsal desc;

the second day

Remove duplicate records from query results: distinct

• The original table data will not be modified, but the query results will be de duplicated.
• Duplicate removal requires a keyword: distinct
• distinct appears before the two fields of job and deptno, indicating that the two fields are combined to remove duplication.

1. select distinct job,deptno from emp;
2. select count(distinct job) from emp;

※ connection query ※

What is connection query?

• A separate query from a table is called a single table query.
• The emp table and Dept table are combined to query the data. The employee name is taken from the emp table and the Department name is taken from the dept table. This cross table query, in which multiple tables are combined to query data, is called join query.

Classification of connection query?

• According to the chronological classification of grammar:
  • sql92: syntax that appeared in 1992
  • sql99: syntax that appeared in 1999
  • (focus on sql99)
• Classification according to the way of table connection:
  • Internal connection:
    • Equivalent connection
    • Non equivalent connection
    • Self connection
  • External connection:
    • Left outer connection (left connection)
    • Right outer connection (right connection)
  • Full connection: (less used, not introduced here)

Cartesian product phenomenon

What happens when two tables are connected and queried without any restrictions?

Case: query the Department name of each employee?
    There are no restrictions on the connection of two tables:
    select ename,dname from emp,dept;
    When two tables are connected for query without any restrictions, the number of final query results is the product of the number of two tables. This phenomenon is called Cartesian product phenomenon. (a mathematical phenomenon discovered by Descartes)

How to avoid Cartesian product?

• Add a condition when connecting, and the records that meet this condition will be filtered out!

select ename,dname
from emp,dept
where emp.deptno = dept.deptno;

optimization(Aliasing, efficiency issues sql92 grammar)

select e.ename,d.dname
from emp e,dept d
where e.deptno = d.deptno

• Thinking: the number of results of the final query is 14, but in the process of matching, has the number of matches been reduced?
• No, it's still 56 times. It's just one out of four, and the number has not decreased.
• Note: according to the Cartesian product phenomenon, the more the connection times of the table, the lower the efficiency. Try to avoid the connection times of the table.

Inner connection

• Features: the data matching this condition can be queried completely.
• a. b the two tables have no primary and secondary relationship and are equal.

Internal connection - equivalent connection

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

SQL92 Syntax:
select e.ename,d.dname
from emp e,dept d
where e.deptno = d.deptno;
SQL92 Disadvantages: the structure is not clear, and the connection conditions of the table and the conditions for further screening in the later stage are put into the table where Back.

SQL99 Syntax:
select e.ename,d.dname
from emp e
inner join dept d
on e.deptno = d.deptno;//The condition is an equal quantity relationship, so it is called equivalent connection.
//inner can be omitted
SQL99 Advantages: the conditions of table connection are independent. After connection, if further filtering is needed, it can be added later where Conditions.

SQL99 Syntax:
select ...
from a 
join b 
on a and b Connection conditions
where Screening conditions

Internal connection - non equivalent connection

Case: 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;//The condition is not an equal quantity relationship, which is called non equivalent connection.

Internal connection - self connection

Case: query the superior leader of an employee. The employee name and the corresponding leader name are required to be displayed?
Tip: think of one table as two

select a.ename,b.ename
from emp a
join emp b
on a.mgr = b.empno;

External connection

• Any right connection has the writing method of left connection.
• Any left connection has a right connection.
• outer can be omitted with strong readability. outer is in front of join.
• Thinking: the number of query results of external connection must be > = the number of query results of internal connection? (√)

Right (right)

• Right: indicates that the table on the right side of the join keyword is regarded as the main table, mainly to query all the data of this table and carry the table on the left side of the associated query.
• In the external link, two tables are connected to produce a primary secondary relationship.

select e.ename,d.dname
from emp e
right outer join dept d
on e.deptno = d.deptno

left outer connection

• Left: indicates that the table on the left of the join keyword is regarded as the main table. It is mainly used to query all the data of this table, with the table on the right of the associated query.
• In the external link, two tables are connected to produce a primary secondary relationship.

select e.ename,d.dname
from emp e
left join dept d
on e.deptno = d.deptno

case

• If the leader of this person is not found in the external connection, null will be displayed

Question: query the superior leaders of each employee, and it is required to display the names of all employees and leaders.
select a.ename 'Employee name',b.ename 'Leader name'
from emp a
left join emp b
on a.mgr = b.empno;

Multi meter connection (three, four)

Syntax:
select  ...
from    a
join    b
on      a and b Connection conditions
join    c
on      a and c Connection conditions
right join  d
on      a and d Connection conditions

One sql Both inner and outer links can be mixed and can appear!

Case:
Find out the Department name and salary grade of each employee,
Employee name, department name, salary and salary grade are required to be displayed?
select 
    e.ename,e.sal,d.dname,s.grade
from 
    emp e
join 
    dept d
on 
    e.deptno = d.deptno
join 
    salgrade s
on 
    e.sal betten s.losal and s.hisal;

Upgrade case:
Find out the Department name and salary grade of each employee, as well as the superior leaders. It is required to display the employee name, leader name, department name, salary and salary grade?
select e.ename,e.sal,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
left join 
    emp l
on 
    e.mgr = l.empno;

Subquery

What is a subquery?

• Select statements are nested in select statements, and the nested select statements are called subqueries.

Where can subqueries appear?

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

Nested subquery in where clause

• Review: grouping functions cannot be used directly in the where clause.

Case: find out the name and salary of employees with high minimum wage?
Step 1: what is the minimum wage
    select min(sal) from emp;
Step 2: find out>800 of
    select ename,sal from emp where sal > 800;
Step 3: Merge
    select ename,sal from emp where sal > (select min(sal) from emp);

Subquery in from clause

be careful: from For the subsequent sub query, the query result of the sub query can be regarded as a temporary table.

Case: find out the salary grade of the average salary of each position.
Step 1: find out the average salary of each position (average by position grouping)
    select job,avg(sal) from emp group by job;
Step 2: find out the salary grade table
    select * from salgrade;
Step 3: Connect
    select
        t.*,s.grade
    from
        (sleect job,avg(sal) as avgsal from emp group by job) t
    join
        salgrade s
    on
        t.avgsal between s.losal and s.hisal;

Subquery after select (understand)

Case: find out the Department name of each employee and display the employee name and department name
select e.ename,e.deptno, (select d.dname from dept d where e.deptno = d.deptno)as dname
from emp e

Note: for select For the following sub query, this sub query can only return one result at a time. If there is more than one result, it will report an error!

union merge query result set

Case: query job position MANAGER and SALESMAN Employees
select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
select ename,job from emp where job in('MANAGER','SALESMAN');

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

union For table connection, each time a new table is connected, the matching times meet the Cartesian product and double
 and union The number of matching can be reduced. In the case of reducing the number of matching, the splicing of two result sets can also be completed.

a connect b connect c
a 10 Records
b 10 Records
c 10 Records
 Matching times: 1000 times

a connect b One result: 10*10 --> 100 second
a connect c One result: 10*10 --> 100 second
 use union In other words: 100 times+100 second = 200 Times. ( union Turn multiplication into addition)

union What are the precautions when using?
//Wrong writing: union requires the same number of columns in the two result sets when merging the result sets.
select ename,job from emp where job = 'MANAGER'
union
select ename from emp where job ='SALESMAN';

//MYSQL is OK, oracle syntax is strict, not OK, and an error is reported. Requirement: the data types of columns and columns are the same when the result set is merged.
select ename,job from emp where job = 'MANAGER'
union
select ename,sal from emp where job = 'SALESMAN';

limit

• limit is a part of the query result set, which is usually used in paging queries.

limit usage

• Complete hair: limit startIndex, length
• startIndex is the starting subscript and length is the length.
• The starting subscript starts at 0.
• Default usage: limit 5; This is the top five.
• Note: in mysql, limit is executed after order by

Example: take out the top 5 employees in descending salary order
select 
    ename,sal
from
    emp
order by
    sal desc
limit 5;//Top five

select 
    ename,sal
from
    emp
order by
    sal desc
limit 0,5;//Top five

Example: take out the salary ranking at 3-5 Employees
select ename,sal
from emp
order by sal desc
limit 2,3;

2 Indicates that the starting position starts from subscript 2, which is the third record.
3 Represents the length

paging

3 records per page
 first page: limit 0，3 [0 1 2]
Page 2: limit 3，3 [3 4 5]
Page 3: limit 6，3 [6 7 8]

Display per page pageSize Records
 The first pageNo Page: limit(pageNo - 1)* pageSize，pageSize

Example:
public static void main(String[] args){
    //The user submits a page number and the number of records displayed on each page
    int pageNo = 5;//Page 5
    int pageSize = 10;//10 items per page
    int startIndex = (pageNo-1)*pageSize;
    String sql = "select ...limit" + startIndex + "," + pageSize;
}
Formula:
    limit(pageNo-1)*pageSize,pageSize

DQL statement summary:

select
    ...
from
    ...
where
    ...
group by
    ...
having
    ...
order by
    ...
limit
    ...
    
Execution sequence:
1.from
2.where
3.group by
4.having
5.select
6.order by 
7.limit

on the third day

Table creation (table creation)

Syntax format of table creation: (table creation belongs to DDL statement, and DDL includes: create drop alter)

creat table Table name (field name 1 data type, field name 2 data type, field name 3 data type)
creat table Table name(
    Field name 1 data type,
    Field name 2 data type,
    Field name 3 data type
    );
    
    Table name: it is suggested to t_perhaps tbl_At first, it is readable. See the name and know the meaning
    Field name: see
    Both table and field names belong to identifiers

About data types in Mysql?

There are many data types. We just need to master some common data types.
varchar(Maximum 255)
    Variable length string
    It is intelligent and saves space.
    The space will be allocated dynamically according to the actual data length.
    Advantages: space saving
    Disadvantages: it needs to allocate space dynamically and the speed is slow

char(Maximum 255)
    Fixed length string
    No matter what the actual data length is.
    Allocate a fixed length of space to store data.
    Improper use may lead to a waste of space.
    Advantages: there is no need to dynamically allocate space and the speed is fast.
    Disadvantages: improper use may lead to a waste of space.

varchar and char How should we choose?
    Example:
    What do you choose for the gender field?Because gender is a fixed length string, select char
    What do you choose for the name field? Everyone's name is different in length, so choose varchar. 
    Select according to the actual situation
    
int(Up to 11)
    An integer in a number, equivalent to java of int. 
    
bigint
    A long integer in a number. Equivalent to java of long. 
    
float
    Single precision floating point data
    
double
    Double precision floating point data
    
date
    Short date type
    
datetime
    Long date type
    
clob
    Character large object
    Up to 4 can be stored G String of
    For example: store an article and a description.
    Those with more than 255 characters shall be adopted CLOB Characters are stored as large objects.
    Character Large OBject: CLOB
    
blob
    Binary large object
    Binary Large OBject
    It is specially used to store streaming media data such as pictures, sounds and videos.
    to BLOB When inserting data into a field of type, such as inserting a picture, video, etc.
    Need to use IO Flow.

example

t_movie Movie table (dedicated to storing movie information)

number        name        Description information        Release date        duration
no(bigint)  name(valchar)


| number | name | storyline | Release date | duration | poster | type |
| no(bigint) | name(varchar) | history(clob) | playtime(date) | time(double) | image(blob) | type(char) |
| 1000 | repay the country with supreme loyalty -- patriotism | ....... | 2 01 9-10-11 | 2.5 | ... | 1  |
| 1 001 | Romance of the Three Kingdoms | ....... | 2012-12-11 | 1.5 | ... | 2  |

Create a student table
 Name, sex, email address
create table t_student(
    no int,
    name varchar(32),
    sex char(1),
    age int(3),
    email varchar(255)
);

Delete table
drop table t_student;//When this table does not exist, an error will be reported for deletion
drop table if exists t_student;//If this table exists, it will be deleted and no error will be reported

insert data insert(DML)
Syntax format:
    insert into Table name (field name 1, field name 2, field name 3)...) values(Value 1, value 2, value 3);
Note: field names and values should correspond one by one. What is one-to-one correspondence?
Quantity and data type should correspond.

insert into t_student(no,name,sex,age,email)values(1,'Zhang San','m',20,'zhangsan@123.com');
insert into t_student(email,name,sex,age,no)values('zhangsan@123.com','Li Si','f',20,2);

insert into t_student(no) values(3);
insert into t_student(name) values('wangwu');
be careful: insert As long as the statement is executed successfully, there must be one more record.
If no value is specified for other fields, the default value is null. 

drop table if exists t_student;
creat table t_student(
    no int,
    name varchar(32),
    sex char(1) default 'm',
    age int(3),
    email varchar(255)
);
//Default: the default value.

insert Can the field name in the statement be omitted? sure
insert into t_student values(2);//FALSE
insert into t_sutdent values(2,'lisi','f',20,'lisi@123.com');//correct
 Note: if omitted, write all the parameters

Insert insert date

Number formatting: format
select ename,sal from emp;
Format number: format(number,'format');
select ename,format(sal,'$999.999') as sal from emp;

String to date: str_to_date
 String varchar Type conversion to date type

Convert date to string: date_format
 take date Type to a formatted varchar type

drop table if exists t_user;
creat table t_user(
    id int,
    name varchar(32),
    birth date/birth char(10)//either-or
);
Note: there is a naming convention in the database
    All identifiers are all lowercase, and words are connected with underscores.
    
insert data
    insert into t_user(id,name,birth) values(1,'zhangsan','01-10-1990');
    //If the date in the database is of date type, the varcahr type (i.e. "what is written here") will be used here, and an error will be reported because the type does not match. The database birth is of date type, and a string is given here
    
    How to solve it?
    have access to str_to_date Function for type conversion.
    str_to_date The function converts a string to a date type date
    
    Syntax format
        str_to_date('String date','Date format')
    Mysql Date format
        %Y  year
        %m  month
        %d  day
        %h  Time
        %i  branch
        %s  second
    insert into t_user(id,name,birth)values(1,'zhangsan',str_to_date('01-10-1990','%d-%m-%Y'));
    
    str_to_date Function can convert a string varchar Convert to date date Type data, usually used in inserting insert Because a date type data is required during insertion, the string needs to be converted into date. 
    
    Note: if you date The date string written in is%Y-%m-%d This format does not need to be used str_to_date Yes
    insert into t_user(id,name,birth)values(2,'lisi','1990-10-01');
    
    Can the query be displayed in a specific date format?
    date_format
    This function converts the date type to a string in a specific format
    select id,name,date_format(birth,'%m/%d/%Y') as birth from t_user;
    
    date_format How does the function work?
    date_format(Date type data,'Date format')
    This function is usually used to set the date format of the display in terms of query date.
    
    select id,name,birth from t_user;
    Above sql The statement is actually formatted with the default date,
    Automatically convert data in the database date Type conversion to varchar Type.
    And the format is mysql Default date format:'%Y-%m-%d'
    
    Review: Java What is the date format in?
    YYYY-MM-dd HH:mm:ss 

    date and datetime The difference between the two types?
    date Is a short date: only the date information is included
    datetime It is a long-term day: including the information of hour, minute and second of month, day and year
    drop table if exists t_suer;
    create table t_user(
        id int,
        name varchar(32),
        birth date,
        create_time datetime
    );
    id Is an integer
    name Is a string
    birth It's a short-term day
    create_time This record is of type: long creation date
    
    mysql Default format for short term days:%Y-%m-%d
    mysql Default format of long-term day:%Y-%m-%d %h:%i:%s
    insert into t_user(id,name,birth,create_time)values(1,'zhangsan','1990-10-01','2 02 0-03-18 15:49:50');
    
    stay mysql How to get the current time of the system?
    now() function,And the obtained time has the information of hour, minute and second, which is datetime Type.
    insert into t_user(id,name,birth,create_time)values(2,'lisi','1990-10-01',now());

Modify update(DML)

Syntax format:
    update Table name set Field name 1=Value 1, field name 2=Value 2, field name 3=Value 3... where Conditions;
    update t_user set name = 'jack',birth = '2000-10-11',create_time = now() where id=2;
    Note: no restrictions will cause all data to be updated.
    Example: update t_user set name = 'abc';//This will cause all the name attributes to be changed to abc

Delete data delete (DML)

delete from t_user where id =2;
Note: no restrictions will cause all data to be deleted.
delete from t_user;//Delete all
insert into t_user(id) values(2);//Insert a record

Insert multiple records at a time

insert into t_ User (field name 1, field name 2)values(), (), (), ();

Quick table creation (understand)

• create table emp2 as select * from emp;
• create table mytable as select empno,ename from emp where job = 'MANAGER';
• Principle:
• Create a new query result as a table
• This can complete the quick copy of the table
• The table is created, and the data in the table also exists

Insert query results into a table

create table dept_bak as select * from dept;//Query dept_bak and create the same table named dept
select * from dept_bak;//Query dept_bak this form
insert into dept_bak select * from dept;//Query the dept table of and insert it into dept_bak middle

Quickly delete data in the table [truncate is important and must be mastered]

//Delete Dept_ Data in bak table
delete from dept_bak;//This method of deleting data is relatively slow

delete Principle of deleting data by statement:( delete data DML Statement)
1. The data in the table has been deleted, but the storage space of this data on the hard disk will not be released
2. The disadvantage of this deletion is that the deletion efficiency is relatively low
3. The advantage of this deletion is that it supports rollback and can recover data after regret

truncate Principle of deleting data by statement:
1. This deletion efficiency is relatively high. The table is truncated at one time and physically deleted
2. The disadvantage of this deletion is that rollback is not supported
3. The advantages of this deletion are: fast
 Usage: truncate table dept_bak；(This operation belongs to DDL Operation)

Note: use truncate You must carefully ask the customer if you really want to delete it and warn that it cannot be recovered after deletion.

truncate Delete the data in the table. The table is still there

Delete table operation:
drop table Table name;//This is not to delete the data in the table, but to delete the table

Add, delete and modify table structure?

• Modification of table structure: add a field, delete a field and modify a field
• To modify the table structure, you need to use: alter, which is a DDL statement
• DDL includes: create drop alter
• First: in the actual development, once the requirements are determined and the table is designed, the table structure is rarely modified. Because when the development is in progress, the cost of modifying the table structure is relatively high. To modify the structure of the table, the corresponding java code needs to be modified a lot, and the cost is relatively high. This responsibility should be borne by the designer
• Second: since there are few operations to modify the table structure, we don't need to master it. If we want to modify the table structure one day, we can use tools
• The operation of modifying the table structure does not need to be written into the java program. In fact, it is not the category of java programmers.

constraint

What are constraints?

• English word corresponding to constraint: constraint
• When creating a table, we can add some constraints to the fields in the table to ensure the integrity and effectiveness of the data in the table.
• The function of constraints is to ensure that the data in the table is valid

What are the constraints?

• Non NULL constraint: not null
• Uniqueness constraint: unique
• Primary key constraint: primary key (PK for short)
• Foreign key constraint: foreign key (FK for short)
• Check constraint: check (not supported by mysql, supported by oracle)
• Here we focus on four constraints:
• Non NULL constraint, unique constraint. primary key constraint, foreign key constraint.

Non NULL constraint (not null)

• Field of non NULL constraint not null constraint cannot be null

drop table if exists t_vip;
create table t_vip(
    id int,
    name varchar(255) not null//not null only has column level constraints, but no table level constraints
);
insert into t_vip(id,name)values(1,'zhangsan');
insert into t_vip(id,name)values(2,'lisi');
insert into t_vip(id)values(3);//name cannot be empty. An error will be reported
 Episode:
    xxxx.sql Such documents are called sql Script file.
    sql A large number of scripts are written in the script file sql sentence
    We execute sql When a script file is, all the sql The statement is executed in its entirety
    Batch execution sql Statement, you can use sql Script file
    
    stay mysql How to execute in sql What about the script?
    By command: source route
    
    When you arrive at the company on the first day of your actual work, the project manager will give you an opportunity xxx.sql File, you execute this script file, and you have the database data on your computer

Uniqueness constraint: unique

• The unique ness constraint of the field can not be null

drop table if exists t_vip;
create table t_vip(
    id int,
    name varchar(255) unique,
    email varchar(255)
);
insert into t_vip(id,name,email)values(1,'zhangsan','zhangsan@123.com');
t_vip(id,name,email)values(2,'lisi','lisi@123.com');
t_vip(id,name,email)values(3,'wangwu','wangwu@123.com');
select * from t_vip;

insert into t_vip(id,name,email)values(4,'wangwu','wangwu@sina.com');//At this time, the data will be erased and imported. Because the name is unique, an error will be reported

insert into t_vip(id) values(4);
insert into t_vip(id) values(5);
name Although the field is unique Constraints, but can be null. 

New requirements: name and email The two fields are unique when combined
drop table if exists t_vip;
create table t_vip(
    id int,
    name varchar(255) unique,//Adding constraints directly after columns is called column level constraints.
    email varchar(255) unique
);
The creation of this table does not meet the above requirements'New demand'of
 This creates a representation: name Unique, email Unique, each unique.

The following data are consistent with'New demand'
insert into t_vip(id,name,email)values(1,'zhangsan','zhangsan@123.com')
insert into t_vip(id,name,email)values(2,'zhangsan','zhangsan@sina.com')

How to create such a table to meet the new requirements?
drop table if exists t_vip;
create table t_vip(
    id int,
    name varchar(255),
    email varchar(255),
    unique(name,email)//Constraints are not added after columns, which are called table level constraints.
);
insert into t_vip(id,name,email)values(1,'zhangsan','zhangsan@123.com')
insert into t_vip(id,name,email)values(2,'zhangsan','zhangsan@sina.com')
select * from t_vip;
name and email The two fields are unique when combined
insert into t_vip(id,name,email)values(3,'zhangsan','zhangsan@sina.com')//At this time, an error will be reported when adding this record

When to use table level constraints?
    When you need to combine multiple fields to add a constraint, you need to use table level constraints.

unique and not null Can we unite?
    drop table if exists t_vip;
    create table t_vip(
        id int,
        name varchar(255) not null unique
    );
    desc t_vip;//At this time, let's look at the table structure. The field key with not null and unique constraints will display the primary key

stay mysql If a field is simultaneously not null and unique If constrained, this field will automatically become a primary key field. (Note: oracle (not the same in China!)

insert into t_vip(id,name)values(1,'zhangsan');
insert into t_vip(id,name)values(2,'zhangsan');//Error, name cannot be repeated
insert into t_vip(id)values(2);//Error, name cannot be null

primary key constraint (PK for short)

Terms related to primary key constraints

• Primary key constraint: a constraint
• Primary key field: a primary key constraint is added to this field. Such a field is called a primary key field
• Primary key value: each value in the primary key field is called a primary key value

What is a primary key? What's the usage?

• The primary key value is the unique identification of each row of records
• The primary key value is the ID number of each row.
• Any table should have a primary key. Without a primary key, the table is invalid
• Primary key characteristics: not null + unique (the primary key value cannot be null and cannot be repeated)

How to add a primary key constraint to a table?

 drop table if exists t_vip;
 create table t_vip(
    id int primary key,//Column level constraint
    name varchar(255)
 );
 insert into t_vip(id,name) values(1,'zhangsan');
 insert into t_vip(id,name) values(2,'lisi');
 //Error: primary key cannot be duplicate
insert into t_vip(id,name) values(2,'wangwu');
 //Error: primary key cannot be NULL
insert into t_vip(name) values('zhaoliu');

Can I use table level constraints to add primary keys?
 drop table if exists t_vip;
 create table t_vip(
    id int,
    name varchar(255),
    primary key(id)//Table level constraint
 );
 Answer: Yes

Table level constraints are mainly used to add constraints to the combination of multiple fields?

drop table if exists t_vip;
//id and name are combined as primary keys: composite primary keys
create table t_vip(
    id int,
    name varchar(255),
    email varchar(255),
    primary key(id,name)
);
insert into t_vip(id,name,email)values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email)values(1,'lisi','lisi@123.com');
first day
1. What is a database? What is a database management system? What is? sql？What is the relationship between them?
Database:
English words DataBase，abbreviation DB. A combination of files that store data in a format.
As the name suggests: a warehouse for storing data is actually a pile of files. These files store data in a specific format.

Database management system:
DataBaseManagement,abbreviation DBMS
 The database management system is used to add and delete data in the database management system.

Common database management systems:
Mysql,Oracle,MS SqlServer,DB2,sybase etc......

SQL: Structured query language
 Programmers need to learn SQL Statement, programmers write SQL Statement, and then DBMS Responsible for implementation SQL Statement to complete the addition, deletion, modification and query of data in the database.
SQL Is a set of standards, programmers mainly learn is SQL Statement, this SQL stay mysql Can be used in Oracle Can also be used in DB2 Can also be used in.
The relationship between the three?
DBMS--implement-->SQL--operation-->DB

First install the database management system MySQL，Then learn SQL How to write and write statements SQL After the statement, DBMS yes SQL Statement to complete the data management of the database.

2. install MySQL Database management system
 matters needing attention:
Port number:
Port number port Is any software/There will be applications, and the port number is the only representative of the application. The port number is usually the same as ip The address is together, ip Address is used to locate the port number of the computer port Is used to locate a service on the computer/Of an application!
On the same computer, the port number cannot be repeated and is unique.
Mysql When the database is started, the default port number occupied by this service is 3306.
Character encoding method:
set up mysql The character encoding method of the database is utf8
 Service Name:
Default is Mysql，No need to change.
Select the configuration environment variable path，It can also be configured manually.
It can be activated while setting the password root Account remote access.
Active: indicates root The account can be logged in in other places.
Inactive: indicates root The account can only be used on this machine.

Mysql Perfect uninstall of database
 Double click the installation package to delete it.
Delete directory, in C Under disk Program(X86)of Mysql Folders, and ProgramData Lower Mysql Directory deletion.
Mysql Services
 Start, pause, automatic, default configuration.

stay windows In the operating system, how to use commands to start and shut down mysql Service?
net stop Service name( MySQL)((stop service)
net start Service name( MySQL)((open service)
The above commands can be used for the start and stop of other services.

Sign in
(Show password form)
cmd Enter the command on the command line: mysql -uroot -p123456
(Hide password form)
cmd Enter the command on the command line: mysql -uroot -p
 Common commands(Case insensitive)
sign out mysql: exit
 see mysql Which databases are available in: show databases；
(mysql Four databases by default)
Select a database to use: use Database name;
Create database: create database Database name;
View the tables under a database: show tables;
Instead of looking at the table data, just look at the table structure: desc Table name;
varchar namely Java Medium String
 Do not execute without semicolon.
Termination:\c
 see Mysql Database version number: select version();
To view the current database: select database;
What is a table table？Why use tables to store data?
The most basic unit in the database is the table: table
 The data in the database is represented in the form of tables. Because the table is more intuitive.
Any table has rows and columns( row): Called data/record. Column( column): Is called a field.
Each field has attributes such as field name, data type and constraint.
Data type: string, number, date....
Constraints: there are many constraints, one of which is called uniqueness constraint. After this constraint is added, the data in this field cannot be repeated.
about SQL Classification of statements?
sql There are many sentences. It's best to classify them, so it's easy to remember.

DQL: Data query language (usually with select Keywords are all query statements) select...
DML: Data operation language (it is used to add, delete and modify the data in the table DML)insert increase delete Delete update change
DDL: Data definition language (usually with create,drop,alter All of them DDL)DDL The main operation is the structure of the table, not the data in the table. create:New, equal to increase drop: delete alter: Modify this addition, deletion and modification DML Different, this mainly operates on the table structure.
TCL: Transaction control language, including transaction submission: commit； Transaction rollback: rollback；
DCL: Data control language; For example: authorization grant,Revoke permission revoke......
Data import
((import data) input command mode: source Path (Chinese is not allowed in the path)
dept It's a department table
emp Employee table
salgrade It's a salary scale
 How to view data in a table?
select * from Table name;
Simple query
 Query a field select Field name from Table name;
Note:
select and from Are keywords.
Field names and table names are identifiers.
emphasize: sql Statements are not case sensitive.
Query two or more fields: separated by commas.
Query all fields, you can write each field, or use*number
 Alias query columns
 Can use as keyword
SELECT DEPTNO,DNAME AS DEPTNAME FROM DEPT;
You can use spaces instead
 You can add an order/Enclosed in double quotation marks
 Note: in all databases, strings are enclosed in single quotation marks. Single quotation marks are standard and double quotation marks are in oracle Not in the database, but in Mysql Can be used in.
The alias is Chinese, enclosed in single quotation marks.
Condition query
select Field 1, field 2, field 3....
from Table name
where Conditions;
use between...and...Follow the small left and large right
between...and...Closed interval
is null by null(is not null (not null)
In the database null The equal sign cannot be used for measurement. To use is null，Because in the database null It means nothing. It's not a value, so it can't be measured by the equal sign.
and also
or perhaps
and and or At the same time, and Higher priority. If you want to or To execute first, you need to add parentheses. If you are not sure about the priority, you need to add parentheses.
in Contains, equivalent to multiple and or(not in (not in this range)
be careful: in Not an interval, in Followed by the specific value.
not Can take non, mainly used in is or in in
like Appellation, fuzzy query, support%Or underline matching
%: Match any number of characters
 Underscore: any character
 Find names that are underlined. Because the underline represents specific content, use a slash \，Escape.
sort
order by(Default (ascending)
desc It is in descending order. It specifies the sort by, such as salary desc；
asc In ascending order, as above.
Sorting multiple fields
 For example: when querying employee name and salary, it is required to be arranged in ascending order of salary. If the salary is the same, it will be arranged in ascending order of name.
select ename,sal
from emp
order by sal asc,ename asc;
//sal comes first and plays the leading role. Enable enable ename sorting only when sal is equal
 Understanding: sorting by field position
select ename,sal 
from emp
order by 2;
//2 indicates the second column. The second column is currently sal, which is sorted according to the second column sal of the query result.
//Just learn about it. It is not recommended to write this in development because it is not robust.
</br>
//The order of columns is easy to change. After the order of columns is changed, 2 will cost
 format
select 
    ...
from 
    ...
where 
    ...
order by 
    ...;
    
Step 1: from
 Step 2: where
 Step 3: select
 Step 4: order by(Sorting is always performed last!)
Data processing function (single line processing function)
Data processing function is also called single line processing function
 Characteristics of single line processing function: one input corresponds to one output.
The opposite of single line processing function is multi line processing function. (multi line processing function features: multiple inputs, corresponding to one output!)
How many inputs, and finally how many outputs, are the characteristics of single line processing functions.
What are the common single line processing functions
lower: Turn lowercase
upper: Capitalize
substr(Intercepted string, starting subscript, intercepted length): take substring (starting subscript starts from 1)
concat: Function to splice strings
length: Take length
trim: Go to space
round: rounding
rand: Generate random number
ifnull: Empty handling function, which is specially used to handle empty.(Can null Convert to a specific value)
case..when..then..when..then..else..end
str_to_date: Convert string to date
date_format: format date
format: Set the thousandth
 Example:
    1. Convert to lowercase: lower
    select lower(ename) as ename from emp;
    
    2. Convert to capital: upper
    select upper(ename) as ename from emp;
    
    3. Substring: substr
 Find out what the first letter of the employee's name is A Employee information?
    3.1 The first way(Fuzzy query):
    select ename
    from emp
    where ename like 'A%';
    3.2 The second way(substr function):
    select ename
    from emp 
    where substr(ename,1,1) = 'A';
    
    4. Capitalize?
    4.1. select name from t_student;
    4.2. select upper(substr(name,1,1)) form t_student;
    4.3. select substr(name,2,length(name)-1) from t_student;
    4.4. select concat(upper(substr(name,1,1)),substr(name,2,length(name)-1)) as result from t_student;
    
    5. String splicing: concat
    select concat(empno,ename) from emp'

    6. Take length: length
    select length(ename) enamelength from emp;

    7. Remove spaces: trim
    7.1 select * 
        from emp
        where ename = '     KING';
    7.2 select *
        from emp
        where ename = trim('     KING');
    
    8. rounding: round
    8.1 select field from Table name;
        select ename from emp；
    8.2 select 'abc' from emp；//select is directly followed by "literal amount / literal value" 
        select abc from emp；//This will definitely report an error, because you will take abc as the name of a field and look for the abc field in the emp table
        select 1000 as num from emp；//1000 is also regarded as a literal quantity / literal value.
    8.3 Conclusion: select It can be followed by the field name of a table (which can be regarded as variable name) or literal quantity/Literal value (data).
    8.4 round
    8.5 select round(1236.567,0) as result from emp;//Keep 0 decimal places
        select round(1236.567,1) as result from emp;//Keep 1 decimal place
        select round(1236.567,-1) as result from emp;//Retain - 1 decimal place, and the result is 1240
        select round(1236.567,-2) as result from emp;//Retain - 2 decimal places, and the result is 1200;
        
    9. Generate random number:rand()
    9.1 select round(rand()*100,0) from emp;//Random number within 100
    
    10. Empty processing function: ifnull
    10.1 In all databases, as long as there is null Participate in the mathematical operation, and the final result is null. To avoid this phenomenon, you need to use ifnull Function.
        ifnull Usage of function: ifnull(Data, as which value)//If the 'data' is null, which value should the data be treated as.
    10.2 select ename,sal + comm as salcomm from emp;//With null
    10.3 select ename,(sal+comm)*12 as yearsal from emp;//Calculating the annual salary of each employee is still null
    10.4 select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;
    
    11. case..when..then..when..then..elas..end
    11.1 When an employee's job is MANAGER At the time of, the salary was raised by 10%，When the job is SALESMAN At the time of, wages were raised by 50%,Other normal.
 (be careful:Do not modify the database, but display the query result as salary increase)
    11.2 select ename,job,sal from emp;
         select 
            ename,
            job,
            sal as oldsal,
            (case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal
         from 
            emp;
Grouping function (multiline processing function)
Characteristics of multi line processing function: input multiple lines and finally output one line.
Note: grouping functions must be grouped before they can be used.
If there is no group of data, the default is to group the whole table.
Multiline processing function:
count: count
sum: Sum
avg: average value
max: Maximum
min: minimum value
1. Maximum wage: max
    select max(sal) from emp;
2. Minimum wage: min
    select min(sal) from emp;
3. Salary and: sum
    select sum(sal) from emp;
4. Average salary: avg
    select avg(sal) from emp;
5. Statistical quantity: count
    select count(ename) from emp;
Grouping functions are ignored automatically null，You don't need to be right in advance null Handle.
In grouping function count(*)and count(What is the difference between specific fields?
count(Specific field): indicates that statistics are made for all fields that are not in this field null The total number of elements.
count(*): The total number of rows in the statistical table.
(Each line of record cannot be null，One column in a row of data is not null，Then this line of data is valid)
Grouping functions cannot be used directly in where In Clause
select ename,sal from emp where sal > min(sal);//Error reporting method
 All grouping functions can be combined and used together.
select sum(sal),min(sal),max(sal),avg(sal),count(sal) from emp;
Grouping query
 What is group query?
In practical applications, there may be such a demand. It is necessary to group first, and then operate each group of data. At this time, we need to use group query. How to do group query?
1. Combine all the previous keywords together to see their execution order?
select
    ...
from
    ...
where 
    ...
group by//grouping
    ...
order by//sort
    ...
    
2. The order of the above keywords cannot be reversed and needs to be remembered.
    2.1 Execution sequence.
        1. from
        2. where
        3. group by
        4. select
        5. order by

3. Why can't grouping functions be used directly in where behind?
    3.1 select ename,sal from emp where sal > min(sal);//report errors
    </br>
    3.2 Because the grouping function can only be used after grouping.
        where At the time of execution, there is no grouping, so where Grouping function cannot appear after.
    3.3 select sum(sal) from emp;
 This is not grouped. Why sum()Function can be used?
 because select stay group by Then execute.
※Key conclusions※: 
In one select Statement, if any group by Statement, select Only fields participating in grouping and grouping functions can be followed.
use having You can further filter the data after grouping.
having Cannot be used alone, having Cannot replace where，having Must and group by Combined use.
Optimization strategy: where and having，Preference where，where I really can't finish it. I'm choosing having. 
Summary
 The single table query is finished here
1. ※Emphasize again※
    select ...
    from ...
    where ...
    group by ...
    having ...
    order by ...
 The above keywords can only be in this order and cannot be reversed.

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

3. Query data from a table through where Filter out valuable data by conditions, and group these valuable data, which can be used after grouping having Continue filtering, select Find it and sort it out at last.

4.example:
 Find out the average salary of each position. It is required to display the average salary greater than 1500, except MANAGER In addition to the position, it is required to be arranged in descending order according to the average salary.
    select
        job,avg(sal) as avgsal
    from
        emp 
    where 
        job <> 'MANAGER'
    group by
        job
    having
        avg(sal) > 1500
    order by
        avgsal desc;
the second day
 Remove duplicate records from query results: distinct
 The original table data will not be modified, but the query results will be de duplicated.
A keyword is required for de duplication: distinct
distinct Appear in job，deptno Before two fields, it means that the two fields are combined to remove duplication.
1. select distinct job,deptno from emp;
2. select count(distinct job) from emp;
※join query※
What is connection query?
A separate query from a table is called a single table query.
emp Table and dept Join tables to query data from emp Take the employee's name from the table dept Take the Department name from the table. This cross table query, in which multiple tables are combined to query data, is called join query.
Classification of connection query?
According to the chronological classification of grammar:
sql92: 1992 Grammar appeared in
sql99: 1999 Grammar appeared in
(Here we focus on learning sql99)
Classification according to the way of table connection:
Internal connection:
Equivalent connection
 Non equivalent connection
 Self connection
 External connection:
Left outer connection (left connection)
Right outer connection (right connection)
Full connection: (less used, not introduced here)
Cartesian product phenomenon
 What happens when two tables are connected and queried without any restrictions?
Case: query the Department name of each employee?
    There are no restrictions on the connection of two tables:
    select ename,dname from emp,dept;
    When two tables are connected for query without any restrictions, the number of final query results is the product of the number of two tables. This phenomenon is called Cartesian product phenomenon. (a mathematical phenomenon discovered by Descartes)
How to avoid Cartesian product?
Add a condition when connecting, and the records that meet this condition will be filtered out!
select ename,dname
from emp,dept
where emp.deptno = dept.deptno;

optimization(Aliasing, efficiency issues sql92 grammar)

select e.ename,d.dname
from emp e,dept d
where e.deptno = d.deptno
 Thinking: the number of results of the final query is 14, but in the process of matching, has the number of matches been reduced?
No, it's still 56 times. It's just one out of four, and the number has not decreased.
Note: according to the Cartesian product phenomenon, the more the connection times of the table, the lower the efficiency. Try to avoid the connection times of the table.
Inner connection
 Features: the data matching this condition can be queried completely.
a，b The two tables have no primary and secondary relationship and are equal.
Internal connection - equivalent connection
 Case: query the Department name of each employee, and display the employee name and department name?

SQL92 Syntax:
select e.ename,d.dname
from emp e,dept d
where e.deptno = d.deptno;
SQL92 Disadvantages: the structure is not clear, and the connection conditions of the table and the conditions for further screening in the later stage are put into the table where Back.

SQL99 Syntax:
select e.ename,d.dname
from emp e
inner join dept d
on e.deptno = d.deptno;//The condition is an equal quantity relationship, so it is called equivalent connection.
//inner can be omitted
SQL99 Advantages: the conditions of table connection are independent. After connection, if further filtering is needed, it can be added later where Conditions.

SQL99 Syntax:
select ...
from a 
join b 
on a and b Connection conditions
where Screening conditions
 Internal connection - non equivalent connection
 Case: 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;//The condition is not an equal quantity relationship, which is called non equivalent connection.
Internal connection - self connection
 Case: query the superior leader of an employee. The employee name and the corresponding leader name are required to be displayed?
Tip: think of one table as two

select a.ename,b.ename
from emp a
join emp b
on a.mgr = b.empno;
External connection
 Any right connection has the writing method of left connection.
Any left connection has a right connection.
outer It can be omitted with strong readability. outer stay join in front.
The result of the query is a certain number of external connections>=How many query results are connected in? (√)
Right outer connection( right，Right connection)
right:Indicates that it will join The table on the right side of the keyword is regarded as the main table, which is mainly used to query all the data of this table and carry the table on the left side of the associated query.
In the external link, two tables are connected to produce a primary secondary relationship.
select e.ename,d.dname
from emp e
right outer join dept d
on e.deptno = d.deptno
 Left outer connection( left，Left (connected)
left:Indicates that it will join The table on the left of the keyword is regarded as the main table, which is mainly used to query all the data of this table and carry the table on the right of the associated query.
In the external link, two tables are connected to produce a primary secondary relationship.
select e.ename,d.dname
from emp e
left join dept d
on e.deptno = d.deptno
 case
 If the leader of this person is not found in the external connection, it will be displayed null
 Question: query the superior leaders of each employee, and it is required to display the names of all employees and leaders.
select a.ename 'Employee name',b.ename 'Leader name'
from emp a
left join emp b
on a.mgr = b.empno;
Multi meter connection (three, four)
Syntax:
select  ...
from    a
join    b
on      a and b Connection conditions
join    c
on      a and c Connection conditions
right join  d
on      a and d Connection conditions

One sql Both inner and outer links can be mixed and can appear!

Case:
Find out the Department name and salary grade of each employee,
Employee name, department name, salary and salary grade are required to be displayed?
select 
    e.ename,e.sal,d.dname,s.grade
from 
    emp e
join 
    dept d
on 
    e.deptno = d.deptno
join 
    salgrade s
on 
    e.sal betten s.losal and s.hisal;

Upgrade case:
Find out the Department name and salary grade of each employee, as well as the superior leaders. It is required to display the employee name, leader name, department name, salary and salary grade?
select e.ename,e.sal,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
left join 
    emp l
on 
    e.mgr = l.empno;
Subquery
 What is a subquery?
select Nested in statement select Statement, nested select Statements are called subqueries.
Where can subqueries appear?
select 
    ..(select)..
from 
    ..(select)..
where
    ..(select)..
where Nested subquery in Clause
 review: where Grouping functions cannot be used directly in clauses.
Case: find out the name and salary of employees with high minimum wage?
Step 1: what is the minimum wage
    select min(sal) from emp;
Step 2: find out>800 of
    select ename,sal from emp where sal > 800;
Step 3: Merge
    select ename,sal from emp where sal > (select min(sal) from emp);
from Subquery in Clause
 be careful: from For the subsequent sub query, the query result of the sub query can be regarded as a temporary table.

Case: find out the salary grade of the average salary of each position.
Step 1: find out the average salary of each position (average by position grouping)
    select job,avg(sal) from emp group by job;
Step 2: find out the salary grade table
    select * from salgrade;
Step 3: Connect
    select
        t.*,s.grade
    from
        (sleect job,avg(sal) as avgsal from emp group by job) t
    join
        salgrade s
    on
        t.avgsal between s.losal and s.hisal;
select The following sub query (understanding)
Case: find out the Department name of each employee and display the employee name and department name
select e.ename,e.deptno, (select d.dname from dept d where e.deptno = d.deptno)as dname
from emp e

Note: for select For the following sub query, this sub query can only return one result at a time. If there is more than one result, it will report an error!
union Merge query result set
 Case: query job position MANAGER and SALESMAN Employees
select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
select ename,job from emp where job in('MANAGER','SALESMAN');

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

union For table connection, each time a new table is connected, the matching times meet the Cartesian product and double
 and union The number of matching can be reduced. In the case of reducing the number of matching, the splicing of two result sets can also be completed.

a connect b connect c
a 10 Records
b 10 Records
c 10 Records
 Matching times: 1000 times

a connect b One result: 10*10 --> 100 second
a connect c One result: 10*10 --> 100 second
 use union In other words: 100 times+100 second = 200 Times. ( union Turn multiplication into addition)

union What are the precautions when using?
//Wrong writing: union requires the same number of columns in the two result sets when merging the result sets.
select ename,job from emp where job = 'MANAGER'
union
select ename from emp where job ='SALESMAN';

//MYSQL is OK, oracle syntax is strict, not OK, and an error is reported. Requirement: the data types of columns and columns are the same when the result set is merged.
select ename,job from emp where job = 'MANAGER'
union
select ename,sal from emp where job = 'SALESMAN';
limit
limit It is to take out part of the query result set, which is usually used in paging query.
limit usage
 Complete hair: limit startIndex，length
startIndex Is the starting subscript, length Is the length.
The starting subscript starts at 0.
Default usage: limit 5；This is the top five.
be careful: mysql among limit stay order by After execution
 Example: take out the top 5 employees in descending salary order
select 
    ename,sal
from
    emp
order by
    sal desc
limit 5;//Top five

select 
    ename,sal
from
    emp
order by
    sal desc
limit 0,5;//Top five

Example: take out the salary ranking at 3-5 Employees
select ename,sal
from emp
order by sal desc
limit 2,3;

2 Indicates that the starting position starts from subscript 2, which is the third record.
3 Represents the length
 paging
 3 records per page
 first page: limit 0，3 [0 1 2]
Page 2: limit 3，3 [3 4 5]
Page 3: limit 6，3 [6 7 8]

Display per page pageSize Records
 The first pageNo Page: limit(pageNo - 1)* pageSize，pageSize

Example:
public static void main(String[] args){
    //The user submits a page number and the number of records displayed on each page
    int pageNo = 5;//Page 5
    int pageSize = 10;//10 items per page
    int startIndex = (pageNo-1)*pageSize;
    String sql = "select ...limit" + startIndex + "," + pageSize;
}
Formula:
    limit(pageNo-1)*pageSize,pageSize

DQL Statement summary:
select
    ...
from
    ...
where
    ...
group by
    ...
having
    ...
order by
    ...
limit
    ...
    
Execution sequence:
1.from
2.where
3.group by
4.having
5.select
6.order by 
7.limit
 Table creation (table creation)
Syntax format of table creation: (table creation belongs to DDL sentence, DDL include: creat drop alter)
creat table Table name (field name 1 data type, field name 2 data type, field name 3 data type)
creat table Table name(
    Field name 1 data type,
    Field name 2 data type,
    Field name 3 data type
    );
    
    Table name: it is suggested to t_perhaps tbl_At first, it is readable. See the name and know the meaning
    Field name: see
    Both table and field names belong to identifiers
 about Mysql Data types in?
There are many data types. We just need to master some common data types.
varchar(Maximum 255)
    Variable length string
    It is intelligent and saves space.
    The space will be allocated dynamically according to the actual data length.
    Advantages: space saving
    Disadvantages: it needs to allocate space dynamically and the speed is slow

char(Maximum 255)
    Fixed length string
    No matter what the actual data length is.
    Allocate a fixed length of space to store data.
    Improper use may lead to a waste of space.
    Advantages: there is no need to dynamically allocate space and the speed is fast.
    Disadvantages: improper use may lead to a waste of space.

varchar and char How should we choose?
    Example:
    What do you choose for the gender field?Because gender is a fixed length string, select char
    What do you choose for the name field? Everyone's name is different in length, so choose varchar. 
    Select according to the actual situation
    
int(Up to 11)
    An integer in a number, equivalent to java of int. 
    
bigint
    A long integer in a number. Equivalent to java of long. 
    
float
    Single precision floating point data
    
double
    Double precision floating point data
    
date
    Short date type
    
datetime
    Long date type
    
clob
    Character large object
    Up to 4 can be stored G String of
    For example: store an article and a description.
    Those with more than 255 characters shall be adopted CLOB Characters are stored as large objects.
    Character Large OBject: CLOB
    
blob
    Binary large object
    Binary Large OBject
    It is specially used to store streaming media data such as pictures, sounds and videos.
    to BLOB When inserting data into a field of type, such as inserting a picture, video, etc.
    Need to use IO Flow.
example
t_movie Movie table (dedicated to storing movie information)

number        name        Description information        Release date        duration
no(bigint)  name(valchar)


| number | name | storyline | Release date | duration | poster | type |
| no(bigint) | name(varchar) | history(clob) | playtime(date) | time(double) | image(blob) | type(char) |
| 1000 | repay the country with supreme loyalty -- patriotism | ....... | 2019-10-11 | 2.5 | ... | 1  |
| 1001 | Romance of the Three Kingdoms | ....... | 2012-12-11 | 1.5 | ... | 2  |

Create a student table
 Name, sex, email address
create table t_student(
    no int,
    name varchar(32),
    sex char(1),
    age int(3),
    email varchar(255)
);

Delete table
drop table t_student;//When this table does not exist, an error will be reported for deletion
drop table if exists t_student;//If this table exists, it will be deleted and no error will be reported

insert data insert(DML)
Syntax format:
    insert into Table name (field name 1, field name 2, field name 3)...) values(Value 1, value 2, value 3);
Note: field names and values should correspond one by one. What is one-to-one correspondence?
Quantity and data type should correspond.

insert into t_student(no,name,sex,age,email)values(1,'Zhang San','m',20,'zhangsan@123.com');
insert into t_student(email,name,sex,age,no)values('zhangsan@123.com','Li Si','f',20,2);

insert into t_student(no) values(3);
insert into t_student(name) values('wangwu');
be careful: insert As long as the statement is executed successfully, there must be one more record.
If no value is specified for other fields, the default value is null. 

drop table if exists t_student;
creat table t_student(
    no int,
    name varchar(32),
    sex char(1) default 'm',
    age int(3),
    email varchar(255)
);
//Default: the default value.

insert Can the field name in the statement be omitted? sure
insert into t_student values(2);//FALSE
insert into t_sutdent values(2,'lisi','f',20,'lisi@123.com');//correct
 Note: if omitted, write all the parameters
insert Insert date
 Number formatting: format
select ename,sal from emp;
Format number: format(number,'format');
select ename,format(sal,'$999.999') as sal from emp;

String to date: str_to_date
 String varchar Type conversion to date type

Convert date to string: date_format
 take date Type to a formatted varchar type

drop table if exists t_user;
creat table t_user(
    id int,
    name varchar(32),
    birth date/birth char(10)//either-or
);
Note: there is a naming convention in the database
    All identifiers are all lowercase, and words are connected with underscores.
    
insert data
    insert into t_user(id,name,birth) values(1,'zhangsan','01-10-1990');
    //If the date in the database is of date type, the varcahr type (i.e. "what is written here") will be used here, and an error will be reported because the type does not match. The database birth is of date type, and a string is given here
    
    How to solve it?
    have access to str_to_date Function for type conversion.
    str_to_date The function converts a string to a date type date
    
    Syntax format
        str_to_date('String date','Date format')
    Mysql Date format
        %Y  year
        %m  month
        %d  day
        %h  Time
        %i  branch
        %s  second
    insert into t_user(id,name,birth)values(1,'zhangsan',str_to_date('01-10-1990','%d-%m-%Y'));
    
    str_to_date Function can convert a string varchar Convert to date date Type data, usually used in inserting insert Because a date type data is required during insertion, the string needs to be converted into date. 
    
    Note: if you date The date string written in is%Y-%m-%d This format does not need to be used str_to_date Yes
    insert into t_user(id,name,birth)values(2,'lisi','1990-10-01');
    
    Can the query be displayed in a specific date format?
    date_format
    This function converts the date type to a string in a specific format
    select id,name,date_format(birth,'%m/%d/%Y') as birth from t_user;
    
    date_format How does the function work?
    date_format(Date type data,'Date format')
    This function is usually used to set the date format of the display in terms of query date.
    
    select id,name,birth from t_user;
    Above sql The statement is actually formatted with the default date,
    Automatically convert data in the database date Type conversion to varchar Type.
    And the format is mysql Default date format:'%Y-%m-%d'
    
    Review: Java What is the date format in?
    YYYY-MM-dd HH:mm:ss 

    date and datetime The difference between the two types?
    date Is a short date: only the date information is included
    datetime It is a long-term day: including the information of hour, minute and second of month, day and year
    drop table if exists t_suer;
    create table t_user(
        id int,
        name varchar(32),
        birth date,
        create_time datetime
    );
    id Is an integer
    name Is a string
    birth It's a short-term day
    create_time This record is of type: long creation date
    
    mysql Default format for short term days:%Y-%m-%d
    mysql Default format of long-term day:%Y-%m-%d %h:%i:%s
    insert into t_user(id,name,birth,create_time)values(1,'zhangsan','1990-10-01','2020-03-18 15:49:50');
    
    stay mysql How to get the current time of the system?
    now() function,And the obtained time has the information of hour, minute and second, which is datetime Type.
    insert into t_user(id,name,birth,create_time)values(2,'lisi','1990-10-01',now());
modify update(DML)
Syntax format:
    update Table name set Field name 1=Value 1, field name 2=Value 2, field name 3=Value 3... where Conditions;
    update t_user set name = 'jack',birth = '2000-10-11',create_time = now() where id=2;
    Note: no restrictions will cause all data to be updated.
    Example: update t_user set name = 'abc';//This will cause all the name attributes to be changed to abc
 Delete data delete(DML)
delete from t_user where id =2;
Note: no restrictions will cause all data to be deleted.
delete from t_user;//Delete all
insert into t_user(id) values(2);//Insert a record
 Insert multiple records at a time
insert into t_user(Field name 1, field name 2)values(),(),(),()；

Quick table creation(understand)
create table emp2 as select * from emp;
create table mytable as select empno,ename from emp where job = 'MANAGER';
Principle:
Create a new query result as a table
 This can complete the quick copy of the table
 The table is created, and the data in the table also exists
 Insert query results into a table
create table dept_bak as select * from dept;//Query dept_bak and create the same table named dept
select * from dept_bak;//Query dept_bak this form
insert into dept_bak select * from dept;//Query the dept table of and insert it into dept_bak middle
 Quickly delete data in a table[truncate More important, must master]
//Delete Dept_ Data in bak table
delete from dept_bak;//This method of deleting data is relatively slow

delete Principle of deleting data by statement:( delete data DML Statement)
1. The data in the table has been deleted, but the storage space of this data on the hard disk will not be released
2. The disadvantage of this deletion is that the deletion efficiency is relatively low
3. The advantage of this deletion is that it supports rollback and can recover data after regret

truncate Principle of deleting data by statement:
1. This deletion efficiency is relatively high. The table is truncated at one time and physically deleted
2. The disadvantage of this deletion is that rollback is not supported
3. The advantages of this deletion are: fast
 Usage: truncate table dept_bak；(This operation belongs to DDL Operation)

Note: use truncate You must carefully ask the customer if you really want to delete it and warn that it cannot be recovered after deletion.

truncate Delete the data in the table. The table is still there

Delete table operation:
drop table Table name;//This is not to delete the data in the table, but to delete the table
 Add, delete and modify table structure?
Modification of table structure: add a field, delete a field and modify a field
 To modify the table structure, you need to use: alter，belong to DDL sentence
DDL include: create drop alter
 First: in the actual development, once the requirements are determined and the table is designed, the table structure is rarely modified. Because when the development is in progress, the cost of modifying the table structure is relatively high. Modify the structure of the table and the corresponding java The code needs a lot of modification, and the cost is relatively high. This responsibility should be borne by the designer
 Second: since there are few operations to modify the table structure, we don't need to master it. If we want to modify the table structure one day, we can use tools
 When modifying the table structure, you do not need to write java In the program, in fact, it is not java The category of programmers.
constraint
 What are constraints?
English words corresponding to constraints: constraint
 When creating a table, we can add some constraints to the fields in the table to ensure the integrity and effectiveness of the data in the table.
The function of constraints is to ensure that the data in the table is valid
 What are the constraints?
Non empty constraint: not null
 Uniqueness constraint: unique
 Primary key constraint: primary key (abbreviation PK)
Foreign key constraints: foreign key (abbreviation FK)
Check constraints: check(mysql I won't support it, oracle (supported)
Here we focus on four constraints:
Non NULL constraint( not null)，Uniqueness constraint( unique). Primary key constraint( primary key)，Foreign key constraint( foreign key). 
Non NULL constraint( not null)
Non NULL constraint not null The field of the constraint cannot be null null
drop table if exists t_vip;
create table t_vip(
    id int,
    name varchar(255) not null//not null only has column level constraints, but no table level constraints
);
insert into t_vip(id,name)values(1,'zhangsan');
insert into t_vip(id,name)values(2,'lisi');
insert into t_vip(id)values(3);//name cannot be empty. An error will be reported
 Episode:
    xxxx.sql Such documents are called sql Script file.
    sql A large number of scripts are written in the script file sql sentence
    We execute sql When a script file is, all the sql The statement is executed in its entirety
    Batch execution sql Statement, you can use sql Script file
    
    stay mysql How to execute in sql What about the script?
    By command: source route
    
    When you arrive at the company on the first day of your actual work, the project manager will give you an opportunity xxx.sql File, you execute this script file, and you have the database data on your computer
 Uniqueness constraint: unique
 Uniqueness constraint unique Constraint fields cannot be duplicate, but can be null
drop table if exists t_vip;
create table t_vip(
    id int,
    name varchar(255) unique,
    email varchar(255)
);
insert into t_vip(id,name,email)values(1,'zhangsan','zhangsan@123.com');
t_vip(id,name,email)values(2,'lisi','lisi@123.com');
t_vip(id,name,email)values(3,'wangwu','wangwu@123.com');
select * from t_vip;

insert into t_vip(id,name,email)values(4,'wangwu','wangwu@sina.com');//At this time, the data will be erased and imported. Because the name is unique, an error will be reported

insert into t_vip(id) values(4);
insert into t_vip(id) values(5);
name Although the field is unique Constraints, but can be null. 

New requirements: name and email The two fields are unique when combined
drop table if exists t_vip;
create table t_vip(
    id int,
    name varchar(255) unique,//Adding constraints directly after columns is called column level constraints.
    email varchar(255) unique
);
The creation of this table does not meet the above requirements'New demand'of
 This creates a representation: name Unique, email Unique, each unique.

The following data are consistent with'New demand'
insert into t_vip(id,name,email)values(1,'zhangsan','zhangsan@123.com')
insert into t_vip(id,name,email)values(2,'zhangsan','zhangsan@sina.com')

How to create such a table to meet the new requirements?
drop table if exists t_vip;
create table t_vip(
    id int,
    name varchar(255),
    email varchar(255),
    unique(name,email)//Constraints are not added after columns, which are called table level constraints.
);
insert into t_vip(id,name,email)values(1,'zhangsan','zhangsan@123.com')
insert into t_vip(id,name,email)values(2,'zhangsan','zhangsan@sina.com')
select * from t_vip;
name and email The two fields are unique when combined
insert into t_vip(id,name,email)values(3,'zhangsan','zhangsan@sina.com')//At this time, an error will be reported when adding this record

When to use table level constraints?
    When you need to combine multiple fields to add a constraint, you need to use table level constraints.

unique and not null Can we unite?
    drop table if exists t_vip;
    create table t_vip(
        id int,
        name varchar(255) not null unique
    );
    desc t_vip;//At this time, let's look at the table structure. The field key with not null and unique constraints will display the primary key

stay mysql If a field is simultaneously not null and unique If constrained, this field will automatically become a primary key field. (Note: oracle (not the same in China!)

insert into t_vip(id,name)values(1,'zhangsan');
insert into t_vip(id,name)values(2,'zhangsan');//Error, name cannot be repeated
insert into t_vip(id)values(2);//Error, name cannot be null
 Primary key constraint( primary key，abbreviation PK)
Terms related to primary key constraints
 Primary key constraint: a constraint
 Primary key field: a primary key constraint is added to this field. Such a field is called a primary key field
 Primary key value: each value in the primary key field is called a primary key value
 What is a primary key? What's the usage?
The primary key value is the unique identification of each row of records
 The primary key value is the ID number of each row.
Any table should have a primary key. Without a primary key, the table is invalid
 Primary key characteristics: not null + unique (Primary key value cannot be null，At the same time, it cannot be repeated)
How to add a primary key constraint to a table?
 drop table if exists t_vip;
 create table t_vip(
    id int primary key,//Column level constraint
    name varchar(255)
 );
 insert into t_vip(id,name) values(1,'zhangsan');
 insert into t_vip(id,name) values(2,'lisi');
 //Error: primary key cannot be duplicate
insert into t_vip(id,name) values(2,'wangwu');
 //Error: primary key cannot be NULL
insert into t_vip(name) values('zhaoliu');

Can I use table level constraints to add primary keys?
 drop table if exists t_vip;
 create table t_vip(
    id int,
    name varchar(255),
    primary key(id)//Table level constraint
 );
 Answer: Yes
 Table level constraints are mainly used to add constraints to the combination of multiple fields?
drop table if exists t_vip;
//id and name are combined as primary keys: composite primary keys
create table t_vip(
    id int,
    name varchar(255),
    email varchar(255),
    primary key(id,name)
);
insert into t_vip(id,name,email)values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email)values(1,'lisi','lisi@123.com');

# It is not recommended to use composite primary key in actual development. It is recommended to use single primary key
# Because the significance of the primary key value is the ID number of the row, so long as the meaning is reached, a single primary key can be achieved. The composite primary key is more complex and is not recommended.

Can I add two primary key constraints to a table?

drop table if exists t_vip;
create table t_vip(
    id int primary key,
    name varchar(255) primary key
);
ERROR 1068 (42000):Multiple primary key defined
 Conclusion: only one primary key constraint can be added to a table

• It is recommended to use int, bigint, char and other types for primary key values
• It is not recommended to use varchar as the primary key. The primary key value is generally a number and is generally of fixed length.

In addition to single primary keys and composite primary keys, they can also be divided into natural primary keys and business primary keys.

• Natural primary key: the primary key value is a natural number and has nothing to do with business.
• Business primary key: the primary key value is closely related to the business. For example, take the bank card account number as the primary key value, which is the business primary key.

Do you use more business primary keys or more natural primary keys in actual development?

• Natural primary keys are often used, because primary keys need not be repeated and need not be meaningful.
• The business primary key is not good. Once the primary key is linked to the business, the primary key value may be affected when the business changes. Therefore, the business primary key is not recommended. Try to use the natural primary key.

In mysql, there is a mechanism to help us automatically maintain a primary key value

drop table if exists t_vip;
create table t_vip(
    id int primary key auto_increment,//auto_increment means self increment, starting from 1 and increasing by 1
    name varchar(255)
);
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
select * from t_vip;

foreign key constraint (FK for short)

• Terms related to foreign key constraints:
• Foreign key constraint: a foreign key constraint
• Foreign key field: a foreign key constraint is added to this field
• Foreign key value: each value in the foreign key field

Business background:

• Please design a database table to describe the information of 'class and student'

The first scheme: classes and students are stored in a table
 Disadvantages: redundant data and waste of space
 The second scheme: one table for class and one table for students

t_class Class schedule          
classno(pk)                 classname
-------------------------------------------------------
100                         Class 1, senior 3, No. 1 senior high school, a city in Henan Province
101                         Class 2, senior 3, No. 1 senior middle school, a city in Henan Province

t_student Student table
no(pk)          name            cno(FK quote t_class This watch is classno)
----------------------------------------------------------------
1               jack            100
2               lucy            100
3               lilei           100
4               hanmeimei       100
5               zhangsan        100
6               lisi            100
7               wangwu          100
8               zhaoliu         100

When cno When the field has no constraints, the data may be invalid. There may be 102, but class 102 does not exist, so in order to ensure cno The values in the field are 100 and 101, which need to be given cno Add a foreign key constraint to the field.
Then: cno Fields are foreign key fields, cno Each value in the field is a foreign key value.

be careful: t_class Is the parent table, t_student Is a sub table
      Order of deleting table: delete child first, then delete parent
      Order of creating table: create parent first, and then create child
      Order of deleting data: delete child first, then delete parent
      Order of inserting data: insert parent first, and then insert child

//Delete the son first, then the father
drop table if exists t_student;
drop table if exists t_class;

//Create the parent first, and then create the child
create table t_class(
    classno int primary key,
    classname varchar(255)
);
create table t_student(
    no int primary key auto_increment,
    name varchar(255),
    cno int,
    foreign key(cno) references t_class(classno)//Foreign key constraint
);

reflection:
1. The foreign key in the child table refers to a field in the parent table. Must the referenced field be a primary key?
A: not necessarily a primary key, but at least it has unique Constraints (uniqueness constraints)

2. Foreign keys can be NULL Are you?
A: the foreign key value can be NULL

Storage engine (understand)

What is a storage engine and what is its use?

• Storage engine is a unique term in Mysql, which is not available in other database types. (in Oracle, but not by this name)
• The storage engine is actually a way for a table to store / organize data.
• Different storage engines store data in different ways.

How do I add / assign a 'storage engine' to a table?

• Specify the storage engine when creating a table.
• ENGINE to specify the storage ENGINE.
• CHARSET to specify the character encoding of this table.
• The default Mysql storage engine is InnoDB
• The default character encoding method of Mysql is utf8

Example:
create table t_product(
    id int primary key,
    name varchar(255)
)ENGINE=InnoDB default charset=gbk;

How to view the storage engines and Mysql version viewing methods supported by Mysql?

• Use the command to view the Mysql storage engine: show engines \G
• Use the command to view the Mysql version: select version();
• Mysql supports nine storage engines, with different versions and different number of supported engines.

Introduction to common Mysql storage engines

MyISAM storage engine features

• Each table is represented by three files:
  • Format file - definition of storage table structure (mytable.frm)
  • Data file - stores the contents of table rows (mytable.MYD)
  • Index file - index on storage table (mytable.MYI): index is the directory of a book, a mechanism to narrow the scanning range and improve the query efficiency.
  • Can be converted to compressed, read-only tables to save space
  • MyISAM does not support transaction mechanism and has low security.
• Tips:
  • For a table, as long as it is a primary key.
  • Indexes are automatically created on fields with unique constraints.

InnoDB storage engine

• This is the default Mysql storage engine, and it is also a heavyweight storage engine.
• InnoDB supports transaction and automatic recovery mechanism after database crash.
• The main feature of InnoDB storage engine is that it is very secure.

InnoDB storage engine features

• Each InnoDB table is displayed in the database directory as frm format file representation
• The InnoDB tablespace is used to store the contents of the table (the tablespace is a logical name, and the tablespace stores data + index)
• Provides a set of log files used to record transactional activities
• Support transaction processing with COMMIT, SAVEPOINT and ROLLBACK
• Provide full ACID compatibility
• Provide automatic recovery after Mysql server crash
• Multi Version (MVCC) and row level locking
• Support the integrity of foreign keys and references, including cascade deletion and update
• The biggest feature of InnoDB is to support transactions to ensure data security. The efficiency is not very high, and it cannot be compressed or converted to read-only.

MEMORY storage engine

• The data of the table using the MEMORY storage engine is stored in MEMORY, and the length of the row is fixed. These two characteristics make the MEMORY storage engine very fast.

The tables managed by the MEMORY storage engine have the following characteristics:

• Within the database directory, each table is represented by File representation in frm format.
• Table data and indexes are stored in memory. (the purpose is fast, fast query)
• Table level locking mechanism.
• Cannot contain TEXT or BLOB fields.
• The MEMORY storage engine was formerly known as the HEAP engine.
• MEMORY engine advantages: the query efficiency is the highest, and there is no need to interact with the hard disk.
• MEMORY engine disadvantages: unsafe. The data disappears after shutdown because the data and index are in MEMORY.

affair

What is a transaction?

• A transaction is actually a complete business logic.

What is a complete business logic

• Assuming transfer, transfer from account A to account B 1w
• Subtract 1w from account A (update statement)
• Add 1w to the money in account B (update statement)
• This is a complete business logic.
• The above operations are the smallest unit of work. They either succeed at the same time or fail at the same time. They cannot be divided.
• These two update statements must succeed or fail at the same time to ensure that the money is correct.

Only DML statements have transactions, and other statements have nothing to do with transactions

• insert
• delete
• update
• Only the above three statements are related to transactions, and the others are irrelevant
• Because as long as the above three statements are added, deleted and modified by the data in the database table.
• As long as your operation involves the addition, deletion and modification of data, you must consider security issues.
• Data security first
• reflection
• Q: assuming that all businesses can be completed with only one DML statement, is it necessary to have a transaction mechanism?
• A: it's not necessary. It's precisely because when doing something, multiple DML statements need to be combined together to complete it, so transactions need to exist. If any complex thing can be solved with a DML statement, the transaction has no value.
• A transaction actually means that multiple DML statements succeed or fail at the same time.
• Transaction: batch DML statements succeed or fail at the same time

How can a transaction succeed and fail multiple DML statements at the same time?

• InnoDB storage engine: provides a set of log files used to record transactional activities

Transaction started:
insert
insert
insert
delete
update
update
update
 The business is over!

• During the execution of a transaction, the operation of each DML statement will be recorded in the "log file of transactional activities".
• During the execution of a transaction, we can commit the transaction or roll back the transaction.
• Commit transaction:
• Clear the log file of transactional activities and completely persist all data into the database table.
• Committing a transaction marks the end of the transaction and is an all successful end.
• Rollback transaction:
• Undo all previous DML operations and empty the log files of transactional activities
• Rolling back a transaction marks the end of the transaction and is the end of all failures.

How to commit a transaction and roll back a transaction?

• Commit transaction: commit statement
• Rollback transaction: rollback statement
• English word corresponding to transaction: transaction
• Mysql supports automatic transaction submission by default.
• Automatic submission: each time a DML statement is executed, it is submitted once.
• In fact, this kind of automatic submission is not in line with our development habit, because a business usually needs multiple DML statements to be executed together. In order to ensure the security of data, it must be submitted after successful submission at the same time, so it can't be submitted once.

How to turn off Mysql automatic submission mechanism

• Before the operation is executed: start transaction// Turn off automatic submission mechanism
• After all operations are completed, enter: commit to commit the transaction

Four characteristics of transaction

• A: Atomicity: it shows that things are the smallest working unit and cannot be divided again.
• C: Consistency: all things require that in the same transaction, all operations must succeed or fail at the same time to ensure data consistency.
• 1: I solation: A transaction and B transaction have certain isolation.
  • For example, there is A wall between classroom A and classroom B, which is isolation. When transaction A operates A table, what happens when transaction B also operates the table
• D: Persistence: a guarantee for the final end of a transaction. Transaction submission is equivalent to saving data not saved to the hard disk.

Transaction isolation

Example:
    A Classroom and B There is a wall in the middle of the classroom, which can be very thick or thin. This is the isolation level of things. The thicker the wall, the higher the isolation level.

What are the isolation levels between transactions and transactions? Four levels
1. Read uncommitted: read uncommitted(Minimum isolation level) (not submitted)
    What is read uncommitted?
        affair A Transactions can be read B Uncommitted data.
        Problems with this isolation level:
            Dirty reading phenomenon( Dirty Read)
            We said we read dirty data.
            This isolation level is generally theoretical, and most database isolation levels start at the second level!
            
2. Read submitted: read committed((read after submission)
    2.1 What is read submitted?
            affair A Only transactions can be read B Data after submission.
            This isolation level resolves dirty reads.
            Problems with this isolation level:
            Data cannot be read repeatedly.
    2.2 What is non repeatable data?
            After the transaction is started, three pieces of data are read for the first time. The current transaction has not ended yet. It is possible that four pieces of data are read for the second time. If 3 is not equal to 4, it is called non repeatable reading.
        This isolation level is relatively real data, and the data read every time is absolutely real.
        Oracle Database default isolation level: read committed

3. Repeatable: repeatable read(It can't be read even after the transaction is committed. What is always read is the data when the transaction was just started)
        3.1 What is readability?
            affair A After opening, no matter how long, every time in the transaction A The data read in is consistent, even if the transaction B The data has been modified and the transaction has been committed A The read data has not changed, which is called repeatable reading.
        3.2 What problem does repeatable reading solve?
            It solves the problem of non repeatable data reading.
        3.3 What are the problems with repeatable reading?
            Phantom reading will appear.
            The data read every time is fantasy and not real enough
            The transaction was started at 9 o'clock in the morning. As long as the transaction is not over, the data read will be the same at 9 o'clock in the evening.
            Mysql This is the default transaction isolation level in
        3.4 give an example
            The bank general ledger may need to execute a select Statement, this select The statement may be executed from 1 pm to 3 pm. Is there any possibility of deposit and withdrawal from 1 pm to 3 pm? In order to ensure that the deposit and withdrawal has no impact on their own data, it is necessary to use repeatable reading repeatable，That is, the third level
            1 Start at 3 o'clock - end at 3 o'clock. This result should be the result of 1 o'clock
            
4. serialize/Serialization: serializable(Maximum isolation level)
    This is the highest isolation level, the lowest efficiency, and solves all problems.
    This isolation level indicates that transactions are queued and cannot be concurrent. (you stop when I operate, I stop when you operate, and you can't operate until I finish)
    synchronized，Thread synchronization (transaction synchronization)
    The data read every time is the most real and the efficiency is the lowest.

Verify various isolation levels

• View the current transaction isolation level: select @@tx_isolation;
• set global transaction isolation level read uncommitted;

1. Verify (read uncommitted): read uncommited,Set the global isolation level first
 affair A                       affair B
use bjpowernode;
                            use bjpowernode;
start transaction;
select * from t_user;
                            seart transaction;
                            insert into t_user values('zhangsan');
select * from t_user;
//When no transaction is committed, the uncommitted transaction data on the right can be queried on the left.

2. Verify (read committed): read commited,Set the global isolation level first
 affair A                       affair B
use bjpowernode;
                            use bjpowernode;
start transaction;
                            seart transaction;
select * from t_user;
                            insert into t_user values('zhangsan');
select * from t_user;
                            commit;
select * from t_user;

3. Verification (repeatable): repeatable read,Set the global isolation level first
 affair A                       affair B
use bjpowernode;
                            use bjpowernode;
start transaction;
                            seart transaction;
select * from t_user;
                            insert into t_user values('zhangsan');
                            insert into t_user values('lisi');
                            commit;
select * from t_user;

4. Validation (serialization): serializable，Set the global isolation level first
 affair A                       affair B
use bjpowernode;
                            use bjpowernode;
start transaction;
                            seart transaction;
select * from t_user;
insert into t_user values('abc');
                            select * from t_user;

Indexes

What is an index?

• Index is added to the fields of database table. It is a mechanism to improve query efficiency.
• An index can be added to a field of a table, or an index can be added to multiple fields.
• The index is equivalent to the directory of a book. It is a mechanism to narrow the scanning range.

give an example:
For a dictionary, there are two ways to find a Chinese character:
The first way: search page by page until it is found. This search method belongs to full dictionary scanning, and its efficiency is relatively low.
The second way: first locate an approximate location through the directory (index), and then directly locate it, do local scanning, narrow the scanning range and find quickly. This search method belongs to index retrieval, which has high efficiency.

t_user
id(idIndex)     name(nameIndex)
---------------------------------
1               zhangsan
2               lisi
3               wangwu
4               zhaoliu
select * from t_user where name ='zhaoliu';

This one above SQL Statement will go name Scan on field
 Because the query criteria are: name='zhaoliu';

If name There is no index (directory) added to the field, or there is no name Create an index for the field, Mysql Will perform a full scan and will name Each value on the field is compared again, which is inefficient.

Mysql There are two main ways to query:
First: full table scanning
 The second method is to search by index

Note: in practice, the contents in front of the Chinese dictionary are sorted according to abcd....
Why sort? Because there is a saying that only after sorting can there be interval search (narrowing the scanning range is actually scanning an interval)

stay mysql The indexes in the database also need to be sorted, and the sorting and TreeSet The data structure is the same. TreeSet(TreeMap)The bottom layer is a self balanced binary tree! stay mysql The index is a B-Tree Data structure.

Follow the principle of small on the left and large on the right, and use the medium order traversal method to traverse and get the data.

Implementation principle of index

t_user
id(PK)          name
-------------------------
100             zhangsan
200             lisi
35              wangwu
46              zhaoliu

Reminder 1: in any database, index objects will be automatically added to the primary key, id The field is automatically indexed because id yes PK. In addition mysql If there is any in one field unique If the constraint is, the index object will also be created automatically.
Reminder 2: in any database, any record of any table has a physical storage number of the hard disk on the hard disk storage.
Reminder 3: in mysql Among them, the index is a single object, and different storage engines exist in different ways MyISAM In the storage engine, the index is stored in a.MYI File. stay InnoDB In the storage engine, the index is stored in a logical name called tablespace In the middle of MEMORy In the storage engine, the index is stored in memory. No matter where the index is stored, the index is mysql They all exist in the form of a tree. (self balancing binary tree: B-Tree)

[external chain picture transfer failed. The source station may have anti-theft chain mechanism. It is recommended to save the picture and upload it directly (IMG sdpiynon-1646562887361)( https://note.youdao.com/yws/res/22414/WEBRESOURCEdc4af16f220925323aac2c48eb2eb4cc )]

• The implementation principle of index is to narrow the scanning range and avoid full table scanning.
• The fields in the table will not move, and the index objects will be sorted
• In mysql, indexes are automatically added to the primary key and unique fields.

Under what circumstances would we consider adding an index to a field?

• Condition 1: huge amount of data (how huge is it? This needs to be tested because each hardware environment is different)
• The condition always appears in the form of "where", that is to say, the condition always appears in the form of "where".
• Condition 3: this field has few DML operations (insert delete update). (because the index needs to be reordered after DML)
• It is recommended not to add indexes at will, because indexes also need to be maintained. Too many indexes will reduce the performance of the system.
• It is recommended to query through the primary key and unique constrained fields. The efficiency is relatively high.

How to create and delete an index

Example:
to name Add index
create index emp_ename_index on emp(ename);
//Add an index to the ename field of the emp table and name it emp_ename_index

Delete index
drop index emp_ename_index on emp;
take emp On the table emp_ename_index Index deletion

How to view a SQL Does the statement use an index for retrieval?
explain select * from emp where ename ='KING';
//Scan 14 records: it indicates that the index is not used. type=ALL

create index emp_ename_index on emp(ename);
//Scan a record, type=ref, indicating that an index is added

When the index fails, when does the index fail?

• Index is an important means to optimize various databases,
• When optimizing, the limited consideration is index.

The first case of failure:
select * from emp where ename like '%T';
//Even if an index is added to ename, the index will not be used
//Because fuzzy matching starts with '%', there is no way to index and retrieve
//Try to avoid starting with '%' in fuzzy query, which is an optimization method / strategy.
explain select * from emp where ename like '%T';

The second case of failure:
explain select * from emp where ename = 'KING' or job = 'MANAGER';
use or It will fail when used or So ask or The condition fields on both sides must have indexes before they can be indexed. If one field has no index, the index on the other field will also become invalid. So that's why it's not recommended or The reason for this.

The third case of failure:
create index emp_job_sal_index on emp(job,sal);
explain select * from emp where job = 'MANAGER';
explain select * from emp where sal = '800';

When using a composite index, the left column is not used for searching, and the index becomes invalid
 Composite index: two or more fields are combined to add an index, which is called composite index

The fourth case of failure:
create index emp_sal_index on emp(sal);
explain select * from emp where sal = 800;
explain select * from emp where sal+1 = 800;
stay where Middle index column(field)Participated in the operation, and the index is invalid.

The fifth case of failure:
explain select * from emp where lower(ename) = 'smith';
stay where The index column uses the function,Index failure

.......

• Indexes are divided into many categories in the database

Index classification

• Single index: add an index to a field.
• Composite index: add an index to two or more fields.
• Primary key index: add an index to the primary key.
• Unique index: add an index to a field with unique constraint.
• ...
• Note: adding indexes to fields with weak uniqueness is of little use.

view

What is a view?

• view: look at the same data from different angles

How to create and delete view objects? What is the function of object view?

• Create view object: create view emp_view as select * from emp;
  //Create this query result as a view

1.Create view:
Table replication: create table dept2 sa select * from dept;
Query replicated tables: select * from dept2;
To create a view object: create view dept2_view as select * from dept2;

2.Delete view:
drop view dept2_view;

Note: only DQL Statement can be view Created in the form of.
create view view_name as The statement here must be DQL sentence

3.What can a view do?
We can add, delete, modify and query the view object. Adding, deleting, modifying and querying the view object will lead to the operation of the original table.
//Features of view: the operation of view will affect the original table data.
//View oriented query
select * from dept2_view;
//View oriented insertion
insert into dept2_view(deptno,dname,loc)values(60,'SALES','BEIJING');
//Query original table data
select * from dept2;
//View oriented deletion
delete from dept2_view;
//Query original table data
select * from dept2;

//Create view object
create view
    emp_dept_view
as
    select
        e.ename,e.sal,d.dnam
    from
        emp e
    join 
        dept d
    on 
        e.deptno = d.deptno;
//Query view object
select * from emp_dept_view;
//View oriented update
update emp_dept_view set sal = 1000 where dname = 'ACCOUNTING';
//The original table data is updated
select * from emp;

4.What is the use of view objects in practical development?
<Convenient, simplified development and easy maintenance
 Suppose there is a very complex sql Statement, and this sql Statements need to be used repeatedly in different locations.
Every time you use this sql Statements need to be rewritten when they are very long and troublesome. What should we do?
You can put this complex sql Statement to create a new in the form of a view object.
Write this article when necessary sql The position of the statement directly uses the view object, which can greatly simplify the development.
It is also conducive to later maintenance, because only one location needs to be modified when modifying, and only the location mapped by the view object needs to be modified sql sentence.

In view oriented development, we can use views like table Same.
You can add, delete, modify and query views. The view is not in memory, and the view object is also stored on the hard disk and will not disappear.

• The statements corresponding to the view can only be DQL statements (statements after as)
• However, after the view object is created, you can add, delete, modify and query the view.
• Addition, deletion, modification and query, also known as CRUD
• CRUD is a term used to communicate between programmers in the company. Generally, we rarely say adding, deleting, modifying and checking. Generally speaking, CRUD.
• C: Create (add)
• R: Retrieve: retrieve
• U: Update (modified)
• D: Delete

DBA common commands

• Focus on data import and export (data backup)
• Other commands can be understood separately

Data export:
mysqldump Database name >D:\Database name.sql -uroot -p123456

Delete database:
drop database Database name;

Import database:
source D:\Database name.sql

Data export note: in windows of dos In the command window

Export the specified table?
mysqldump Database name emp>D:\Database name.sql -uroot -p123456

Data import note:
You need to log in to mysql On the database server.
Then create the database: create database Database name.
Use database: use Database name
 Then initialize the database: source D:\Database name.sql

Three paradigms of database design

What is the database design paradigm?

• The design basis of database table teaches you how to design database table.

What do database design paradigms share?

• The first paradigm: it is required that any table must have a primary key, and each field is atomic and cannot be further divided.
• The second paradigm: Based on the first paradigm, all non primary key fields are required to be completely dependent on the primary key without partial dependence.
• The third paradigm: Based on the second paradigm, all non primary key fields are required to directly rely on the primary key without transitive dependency.
• Statement: three paradigms are often asked by interviewers, so you must remember them by heart
• When designing database tables, follow the above paradigm to avoid data redundancy and space waste in the tables.

First paradigm

• The core and most important paradigm is that the design of all tables needs to be met. There must be a primary key, and each field is atomic and can not be divided.

Student number    Student name    contact information
---------------------------------------------
1001        Zhang San        zs@gmail.com,135999999      
1 002        Li Si        ls@gmail.com,135999999   
1001        Wang Wu        ww@gmail.com,135999999   
The above is the student list. Does it meet the first paradigm?
Answer: not satisfied
 First: no primary key
 Second: contact information can be divided into email address and telephone number

The second paradigm:

• Based on the first paradigm.
• It is required that all non primary key fields must be completely dependent on the primary key without partial dependence.
• This table describes the relationship between students and teachers: a student may have multiple teachers, and a teacher may have multiple students
• This is a very typical many to many relationship

Student number    Student name    Teacher number    Teacher name
---------------------------------------
1001        Zhang San        001       Miss Wang
1002        Li Si        002       Miss Zhao
1003        Wang Wu        001       Miss Wang
1001        Zhang San        002       Miss Zhao
 Does the above table meet the first paradigm?
If not, it can be modified

Student number+Teacher number( PK)  Student name    Teacher name
---------------------------------------
1001        001         Zhang San        Miss Wang
1002        002         Li Si        Miss Zhao
1003        001         Wang Wu        Miss Wang
1001        002         Zhang San        Miss Zhao
 Student number, teacher number, two fields are combined as primary key and compound primary key( PK: Student number+(teacher number)
After modification, the above table meets the first paradigm, but does it meet the second paradigm?
dissatisfaction,'Zhang San'Relying on 1001,'Miss Wang'Dependency 001, obviously produces partial dependency
 What are the disadvantages of partial dependence?
Data redundancy and space waste.'Zhang San'I repeat,'Miss Wang'Repeat

In order to make the above table meet the second paradigm, it needs to be designed as follows:
Use three tables to represent many to many relationships
 Student table
 Student number( pk)      Student name
------------------------------
1001                Zhang San
1002                Li Si
1003                Wang Wu

Teacher table
 Teacher number( pk)      Teacher name
------------------------------
001                 Miss Wang
002                 Miss Zhao

Student teacher relationship form
id(pk)        Student number( fk)       Teacher number( fk)
-------------------------------------------------
1               1001                    001
2               1002                    002
3               1003                    001
4               1001                    002

Pithy formula
 How to design many to many: many to many, three tables, relationship table, two foreign keys

Third paradigm

• The third paradigm is based on the second paradigm, which requires that all non primary key fields must directly rely on the primary key without transitive dependency

Student number( PK)      Student name        Class number       Class name
-----------------------------------------------------------------
1001                Li Si           01             Once a year
1002                Zhang San           02             Two classes a year
1003                Wang Wu           03             Three classes a year
1004                Zhao Liu           03             Three classes a year

Analyze whether the above table meets the first paradigm?
Satisfy the first normal form and have a primary key

Analyze whether the above table meets the second paradigm?
It meets the second paradigm because the primary key is not a composite primary key and does not generate partial dependencies. The primary key is a single primary key.

Analyze whether the above table meets the third paradigm?
The third paradigm requires: do not generate transitive dependency!
One shift a year depends on 01, and 01 depends on 1001, resulting in transmission dependence
 Therefore, it does not meet the requirements of the third paradigm, resulting in data redundancy.
So how to design one to many?

Class table:
Class number( PK)          Class name
----------------------------------
01                      Once a year
02                      Two classes a year
03                      Three classes a year

Student form:
Student number( PK)          Student name            Class number( fk)
------------------------------------------------------
1001                    Zhang San               01
1002                    Li Si               02
1003                    Wang Wu               03
1004                    Zhao Liu               03

Pithy formula:
    One to many, two tables, many tables plus foreign keys

Summary table design

• One to many: one to many, two tables, many tables plus foreign keys
• Many to many: many to many, three tables, relation table, two foreign keys
• One to one: one to one, unique foreign key

Don't you just put one-on-one in one table? Why dismantle the watch?
In the actual development, there may be too many fields and too large in a table. At this time, the table should be split. How to design one-on-one?
one-on-one:
    Before splitting: a table
t_user
id      login_name      login_pwd       real_name       email   address...
----------------------------------------------------------------------------
1       zhangsan        123             Zhang San            zhangsan@xxx
2       lisi            123             Li Si            lisi@xxx
....

This huge table is recommended to be split into two tables:
t_login Login information table
id(pk)      login_name      login_pwd
---------------------------------
1             zhangsan        123
2             lisi            123

t_user User details table
id(pk)          real_name       email          address......     login_id(fk_unique) 
--------------------------------------------------------------------------------------
100             Zhang San            zhangsan@xxx                        1
200             Li Si            lisi@xxx                            2

• The three paradigms of database design are theoretical, and sometimes there are deviations between practice and theory.
• The ultimate goal is to meet the needs of customers. Sometimes redundancy will be exchanged for execution speed.
• Because in sql, the more connections between tables, the lower the efficiency. (Cartesian product)
• Sometimes there may be redundancy, but in order to reduce the number of table connections, this is also reasonable, and for developers, the difficulty of writing and executing sql statements will be reduced.