9. Mysql advanced

9.1 data addition

Learning objectives: understand some advanced operation skills in new data, and improve the efficiency and security of data insertion

• Batch insert
• Worm replication
• Primary key conflict
  • Conflict update
  • Conflict replacement

9.1.1 batch insertion

concept

Batch insert: it is a way to optimize the insertion of data one by one

• The syntax of batch data insertion is similar to that of simple data insertion

• There are two types of batch inserts

  • Full field batch insert

  insert into Table name values(Value list 1),(Value list 2),...(Value list N);
  

  • Batch insertion of some fields (note the default value of the field)

  insert into Table name (Field list) values (Value list 1),(Value list 2),...(Value list N);
  

step

1. The user's operation involves simultaneous insertion of multiple records (usually batch data import)

2. Assemble into batch insert SQL instructions

• Fields are all fields (no problem if the logical primary key does not exist): insert SQL in batches with all fields
• Partial fields: organize the field list and check whether other fields allow default values

3. Perform batch insert

Example

1. Batch insert student grades (t_30 full fields)

insert into t_30 values(null,'Tom','Computer',90),(null,'Lily','Computer',92);

2. Batch insert student test information (t_30 excluding scores)

insert into t_30 (stu_name,course) values('Tony','English'),('Ray','Math');

Summary

1. Batch insert can solve the business of batch import of data

2. Batch insertion can solve multiple data inserts at one time, effectively reduce the occupation of the client and improve the efficiency of data operation

• After MySQL 8, transaction security will be provided by default, that is, batches will either succeed or fail, and some problems will not occur

9.1.2 worm replication

concept

Worm replication: copy data from an existing table and insert it directly into another table (the same table)

• The goal of worm replication is to quickly increase the data in the table
  • Realize data replication in tables (for data backup or migration)
  • Realize exponential increment of data (mostly used for testing)
• Worm replication syntax

insert into Table name [(Field list)] select Field list from Table name;

• matters needing attention
  • Field list must correspond to
  • Field type must match
  • Data conflicts need to be considered in advance

step

1. Determine a table to insert data (generally consistent with the table structure of the data to be copied by the worm)

2. Determine the source table of data

• The number of fields matches one by one
• Field types match one by one
• There is no conflict in the selected field (the data may be duplicate data. If there is only one key, it will conflict)

3. Using worm replication

Example

1. Create a new table that will t_ The data in table 30 is migrated to a new table

create table t_35(
	id int primary key auto_increment,
    stu_name varchar(20) not null,
    course varchar(20) not null,
    score decimal(5,2)
)charset utf8;

insert into t_35 select * from t_30;

# it's fine too
insert into t_35(stu_name,course,score) select stu_name,course,score from t_30;

2. Quick let t_35. The data in the table reaches more than 100 (repeated Implementation)

insert into t_35 (stu_name,course,score) select stu_name,course,score from t_35;

Summary

1. The goal of worm replication is to enrich table data by using existing data in batches

• Data migration: copy table data at one time
• Data growth: repeat self replication growth (data test use)

9.1.3 primary key conflict

concept

Primary key conflict: the primary key specification is included when inserting data, and the primary key already exists in the data table

• The business with primary key conflict usually occurs on the business primary key (the business primary key itself has business significance)

• Solutions to primary key conflicts

  • Ignore conflict: keep the original record and ignore the newly inserted data

  insert ignore into Table name [(Field list)] values(Value list);
  

  • Conflict update: some fields become updated after conflict

  insert into Table name [(Field list)] values(Value list) on duplicate key update field = New value[,field=New value...];
  # 1. Try adding
  # 2. Renew
  

  • Conflict replacement: delete the original record first, and then add a new record

  replace into Table name [(Field list)] values(Value list); # The efficiency is not as high as that of insert (check whether there is conflict)
  

step

1. Confirm that there may be a primary key conflict in the current business

2. Select a solution to the primary key conflict

Example

1. User registration with user name as primary key (conflict cannot be overwritten): username, password, regtime

create table t_36(
	username varchar(50) primary key,
    password char(32) not null,
    regtime int unsigned not null
)charset utf8;

insert into t_36 values('username','password',12345678);
# Conflict ignore
insert ignore into t_36 values('username','12345678',12345678);

2. The user name is used as the primary key to record user usage information (if there is no addition, the update time will be if there is one): username, logintime

create table t_37(
	username varchar(50) primary key,
    logintime int unsigned
)charset utf8;

insert into t_37 values('username',12345678);	# Current timestamp

# Conflict update (replace some field data)
insert into t_37 values('username',12345678) on duplicate key update logintime = unix_timestamp();	# Current timestamp

• If the primary keys do not conflict: add
• If the primary key conflicts: update the specified field
• The above method is applicable to a large number of fields, but there are few fields with data changes in case of possible conflicts

3. The user name is used as the primary key to record user usage information (if there is no new user, if there is one, all user usage information will be updated): username, logintime, clientinfo

create table t_38(
	username varchar(50) primary key,
    logintime int unsigned,
    clientinfo varchar(255) not null
)charset utf8;

insert into t_38 values('username',unix_timestamp(),'{PC:chrome}');

# Replace insert
replace into t_38 values('username',unix_timestamp(),'{phone:uc}');

• replace will delete the duplicate primary key first and then add it
• If there are many fields that need to be updated: replace is recommended

Summary

1. There are three solutions to primary key conflicts, but you need to select the appropriate method according to the specific business

• Ignore new data: insert ignore
• Update some data: Insert... On duplicate key update
• Replace all: replace into

2. In terms of efficiency, insert into is the most efficient without considering conflicts. The efficiency of the three conflict resolution methods will be reduced (retrieval is required). The efficiency of the three methods themselves is: ignore new data > update some data > replace all

9.2 data query

Learning objectives: understand the complexity and delicacy of SQL query operation, master the key points of query from various dimensions, and use various dimensions to realize the analysis requirements of complex business

• query option
• Alias application
  • field alias
  • Table alias
• data source
  • Single table data source
  • Multi table data source
  • Subquery data source
• where clause
  • Comparison operation
  • Logical operation
  • Null operation
• group by clause
  • Aggregate function
  • Backtracking statistics
• having clause
• order by clause
• limit clause
  • Pagination production

Summary

1. After mastering the quantity of advanced data query, businesses that previously needed multiple operations can basically filter and refine data at one time through the preparation of some complex SQL, so as to meet the requirements of solving problems at one time

9.2.1 Query options

concept

Query options: used for simple data filtering of query results

• The query option has two mutually exclusive values after the select keyword
  • All: by default, all records are retained
  • distinct: duplicate records (all fields are duplicate)
• distinck indicates that if as like as two peas, there is a collection, which is exactly the same tuple.

step

1. The query results need to remove duplicate records

2. Use the distinct query option to remove duplicates (the default is all and keep all)

Example

View the commodity information of all categories in the commodity table: duplicate commodities are retained only once (the name, price and attribute are consistent)

create table t_39(
	id int primary key auto_increment,
    goods_name varchar(50) not null,
    goods_price decimal(10,2) default 0.00,
    goods_color varchar(20),
    goods_weight int unsigned comment 'Weight in grams'
)charset utf8;

insert into t_39 values(null,'mate10',5499.00,'blue',320),
(null,'mate10',5499.00,'gray',320),
(null,'nokia3301',1299,'black',420);

# Consider the de duplication of all fields (excluding logical primary keys)
# Because of the different colors, three were found
select distinct goods_name,goods_price,goods_color,goods_weight from t_39;
select goods_name,goods_price,goods_color,goods_weight from t_39; # Keep all

# Color de duplication is not considered
select distinct goods_name,goods_price,goods_weight from t_39;
select all goods_name,goods_price,goods_weight from t_39;

Summary

1. The select option is mainly used to retain all data or deselect data

• All: all are reserved by default (keywords can be omitted)
• distinct: manual selection and de duplication (for the records composed of the selected fields (filter the checked two-dimensional table again), rather than a field)

2. The distinct option is generally used when making data reports

9.2.2 field selection & alias

concept

Field selection: select the field information to obtain data according to the actual demand

• According to the actual needs, specify the required field names, separated by English commas
• Get all fields and use asterisk * to configure all fields
• The field data may not necessarily come from the data source (as long as there is a result of the select)
  • Data constant: select 1
  • Function or variable: select unix_timestamp(),@@version (@ @ is the prefix of the system variable followed by the variable name)

Field alias: a temporary name given to a field

• Field aliases are implemented using as syntax
  • Field name as alias
  • Field name alias
• The purpose of field aliases is usually to protect data
  • Field conflict: multiple tables operate at the same time and have fields with the same name (overwritten by default). Do you want to keep all
  • Data security: data provided externally does not use real field names

step

1. Specify the field information to be queried

• All:*
• Section: determine field list

2. It is determined that there is data conflict or data protection is required (usually it can be understood as providing external access to other systems)

• Use alias

Example

1. Query commodity information

# Query all
select * from t_39;

# The demand is the product name and price
select goods_name,goods_price from t_39;

# Alias use
select goods_name as gn,goods_price gp from t_39;

2. No need for data acquisition from the data source: the select expression itself can calculate the result)

# Gets the current timestamp and version number
select unix_timestamp() as now,@@version as version,@@version;

Summary

1. For field selection, as long as the data requirements can be realized, use * as little as possible (MySQL optimization)

• Reduce data reading pressure on the server
• Reduce network transmission pressure
• Enable the client to accurately parse the data (without looking for a needle in a haystack)

2. The flexible use of field aliases can not only ensure the security of original data, but also provide convenience for data users

• Problem of overwriting fields with the same name (encountered when learning to connect tables)
• Original field protection
• Simplification of data fields

3. Select is an instruction used to fetch data in SQL. This instruction does not necessarily need to fetch data from the data table. As long as it is an expression that can have data, you can use select to obtain it

9.2.3 data source

concept

Data source: the source of data after the from keyword. As long as the final result is a two-dimensional table, it can be used as a data source

• Single table data source: a data source is a table from table name
• Multi table data source: data sources are multiple tables (comma separated) from table name 1, table name 2,... Table name N
• Sub query data source: the data source is a query result from (select field list from table name) as alias
  • The data source requires a table
  • If it is a query result, you must give a table alias
• Data tables can also specify aliases
  • Table name as alias
  • Table name alias

Example

1. Single table data source: the simplest data source, obtained directly from a data table

select * from t_27;

2. Multi table data source: use one piece of data in one table to match all records in another table. The record result is: number of records = number of records in Table 1 * number of records in Table 2; Number of fields = number of fields in Table 1 + number of fields in Table 2 (Cartesian product)

select * from t_27,t_30;

3. Sub query data source: the data source is the query result corresponding to a select

• Query statements need to be wrapped in parentheses
• Alias needs to be specified for query results

select * from (select * from t_27,t_30) t; # Data conflicts cannot be found. There are two IDs. You can use the field alias to solve them
select * from (select * from t_27) as t;

4. If sometimes the name is long or inconvenient to use, you can use the table alias

select * from t_30 as t;

select t1.*,t2.stu_name from t_27 as t1,t_30 t2;

• Generally, alias settings are set so that aliases can be used directly in subsequent conditions
• For multi table operations, you can use table aliases to explicitly extract table fields

Summary

1. The data source provides data support for query and retrieval, and needs to be clearly specified when using

2. In general, data sources do not use simple multi table data sources (Cartesian product)

3. The alias of the data table is very useful when responsible for SQL query operations, and must be used in some places (such as sub query data source)

9.2.4 where clause

concept

where clause: after the from data source, the data is conditionally matched

• where is to filter after disk reading and before entering memory
  • Unqualified data will not enter memory
• The content of where filtering has not entered the memory, so the data has not been processed
  • Field aliases cannot be used in where
  • Table aliases can be used in where

step

1. Determining the data to query requires conditional filtering

2. Use where for data filtering

Example

1. Query t_ Table 35 grade information of lily for middle school students

select * from t_35 where stu_name = 'Lily';

2. Because where performs conditional filtering when fetching data from the disk, and the data does not enter the memory at this time, the field alias is invalid

# error
select stu_name name,score from t_35 where name = 'Lily';

Summary

1. where is used to filter data by matching criteria

2. where is to filter the data before it is taken out from the disk and entered into the memory: after the filtering, the appropriate data will enter the memory, so the field alias is useless (it can be displayed later)

3. In mature projects, conditional queries are used almost every time a query is executed

9.2.5 operators

concept

Operators: symbols used to perform operations

• Comparison operator
  • >(greater than), < (less than), = (equal), > = (greater than or equal), < = (less than or equal), < > (not equal)= (not equal to)
  • Between A and B: between A and B (A is less than B), including A and B itself (numerical comparison)
  • in (data 1, data 2,... Data N): among the listed data
  • Like 'pattern': like the above, it is used for string comparison
    • _: Single underscore, matching any character at the corresponding position (ab_: ab beginning + one character, matching abc, ab1, but not abcd)
    • %: match any number of any characters in the current position (back) (AB%: ab beginning + any number of any characters, match abc, ab1, abcd)
• Logical operator
  • and (& &), or (|), not (!)
• null operator
  • is null, is not null

step

1. Determine that operators are required for operations

2. Use exact operators according to data requirements

Example

1. Query the information of all students who fail

# Performance conditions: the performance is both numerical value and ratio. You can directly use the comparison operator
select * from t_35 where score < 60;

2. Query the information of students whose grades are between 60-90

# Achievement conditions: the range is 60 to 90, and there can be two solutions

select * from t_35 where score between 60 and 90;
select * from t_35 where score >= 60 and score <= 90;

3. Query students who have no grades

# Score condition: the score is null, so you can't use the comparison symbol to check. You can only use is null
select * from t_35 where score is null;

4. Query students whose scores are 60 or 90 or 95

select * from t_35 where score in(60,90,95);

5. Query students with T in their name

select * from t_35 where stu_name like '%T%';

6. Query students with T in name and score of 80 or id of 1

select * from t_35 where (stu_name like '%T%' and score = 80) or id = 1;

Summary

1. Operator can be used to perform field data operations and filter conditions with where

2. The basic operators are similar to those of other programming languages. There are several special operators in SQL to understand

• between and: a convenient way to write > = and < =
• In: used to make specific choices, similar to the case in switch
• is null/is not null: the determination method of Null field value

3. The premise of skillful application is to continuously skillfully use and master the results and effects of each operator

9.2.6 group by clause

concept

group by clause: group statistics, which classifies all results according to a field and performs data statistical analysis

• The purpose of grouping is not to display data, but to make statistics
• The group by clause must appear after the where clause (if both exist)
• Grouping statistics can be subdivided into large groups first, and then large groups
• Grouping statistics requires the use of statistical functions
  • group_concat(): all the fields in the group are reserved and spliced together and displayed by one character
  • any_value(): the value in any group that does not belong to the grouping field, and the first value is selected for display (this is invalid in MySQL version 5.5.40)
  • count(): find the number of records in the corresponding group
    • Count (field name): count the number of field values (NULL is not counted)
    • count(*): count the quantity of the whole record (more)
  • sum(): find the sum of a field in the corresponding group
  • max()/min(): find the maximum / minimum value of a field in the corresponding group
  • avg(): find the average value of a field in the corresponding group

be careful:

1. sql queries using the group by clause cannot query fields other than statistics functions and grouped fields
2. We can use group_concat() statistics function to get information about other fields
3. Grouping statistics uses data. The query of data can only rely on statistical functions and grouped fields, not other fields (MySQL 7 used to be OK, but the data is meaningless: because the system only retains the first one in the group, it has realized the effect of any_value())

step

1. Are you sure you want to perform data statistics

2. Determine statistics object: grouping field (multiple fields are allowed)

3. Determine the data form to be counted: select the corresponding statistical function

4. Grouping statistics

Example

1. Create a table to store student information

create table t_40(
id int primary key auto_increment,
name varchar(10) not null,
gender enum('male','female','secrecy'),
age tinyint unsigned not null,
class_name varchar(10) not null comment 'Class name'
)charset utf8;

insert into t_40 values(null,'Naruto','male',18,'Muye class 1'),
(null,'Sasuke','male',18,'Muye class 1'),
(null,'Sai ','male',19,'Muye class 2'),
(null,'Big snake pill','male',28,'Muye Class 0'),
(null,'Kakashi','male',29,'Muye Class 0'),
(null,'Sakura','female',18,'Muye class 1'),
(null,'Young field','female',18,'Muye class 1'),
(null,'I love you','male',19,'Muye class 1'),
(null,'Sunflower','female',6,'Muye class 10'),
(null,'Bo Ren','male',8,'Muye class 10'),
(null,'Weasel','male',28,'Muye Class 0');

2. Count the number of people in each class

select count(*),class_name from t_40 group by class_name;

3. Multi grouping: count the number of male and female students in each class

select count(*),class_name,gender from t_40 group by class_name,gender;

4. Count the number of students in each class and record the names of the students in the class

select count(*),group_concat(name),class_name from t_40 group by class_name;
# any_value does not exist in mysql 5.5.40. At this time, mysql implements any_value() function, without adding a function, directly the field name
select count(*),any_value(name),class_name from t_40 group by class_name;
# mysql 5.5.40
select COUNT(*),name,class_name from t_40 group by class_name;

Grouping principle

Take statistics class students as an example

Summary

1. Grouping and statistics are not separated. Statistics must be used for grouping, and once statistics are used, they are actually grouped

2. Grouping statistics uses data. Data queries can only rely on statistical functions and grouped fields, not other fields (MySQL 7 used to be OK, but the data is meaningless: the system only retains the first field in the group)

3. The group by clause has its own explicit position: after where (where can not be)

9.2.7 backtracking statistics

concept

Backtracking statistics: when grouping (usually multi grouping), summary statistics are performed for each backtracking of results

• Backtracking statistics syntax: use with rollup after statistics

Backtracking siblings is to merge siblings for each group, and set the field of the group to null

step

1. Are you sure you want to perform group statistics

2. Determine whether it is multi group statistics

3. The results of each grouping need to be summarized

4. Use backtracking statistics

Example

Count the number of male and female students in each class, and know the total number of classes

# Only the number of male and female students in each class is counted, and there is no class summary
select count(*),class_name,gender,group_concat(name) from t_40 group by class_name,gender;

# Summary statistics: backtracking
select count(*),class_name,gender,group_concat(name) from t_40 group by class_name,gender with rollup;

For the first time, backtrack the gender field, set gender to null, and merge the separated tables

The second backtracking of the gender field and class_name field, set these two fields to null, and merge the separated tables

Retrospective statistical principle

Summary+

1. Backtracking statistics is generally used in multi field grouping to count the summary data of groups at all levels

2. Because the backtracking statistics will set the corresponding grouping fields to null (cannot be merged without null), the backtracking data can only be retrieved after processing in other programming languages

9.2.8 grouping and sorting

concept

Grouping sorting: the data can be displayed in ascending or descending order according to the grouping fields during the statistics results after grouping

• By default, the system will automatically sort the grouping results in ascending order according to the grouping fields
• You can set the sorting method of grouping results and add a sorting keyword after the field name
  • group by field name [ASC]: sort in ascending order (default)
  • group by field name DESC: sort in descending order

step

1. Decide to use group statistics

2. The results need to be sorted in descending order (regardless of ascending order)

3. Sort in descending order using groups

Example

For the grouping results, women are preferred: gender is enumeration, male value is 1 and female value is 2

select count(*),class_name,gender,group_concat(name),any_value(name) from t_40 group by class_name,gender desc;

Summary

1. Grouping sorting is to sort the grouping results by grouping fields

2. In general, grouping sorting is rarely used

9.2.9 having clause

concept

having clause: similar to where clause, it is used to filter data conditionally

• The having clause itself is used for conditional filtering of grouping statistics results

• The having clause must appear after the group by Clause (if both exist)

• The data for having is the data already loaded in memory

• having can do almost everything where can do, but where is not necessarily

  • Field alias (where is for disk data, not yet)
  • Statistical results (where before group by)
  • Group statistics function (having usually exists for group by)

step

1. There are group statistics in front

2. Data filtering is required for the results after grouping statistics

3. Filter using having Organization criteria

Example

1. Get classes with class size less than 3

select count(*) as `count`,class_name,group_concat(name) from t_40 group by class_name having `count` < 3;

select count(*) as `count`,class_name,group_concat(name) from t_40 group by class_name having count(*) < 3; # One more function is used (reduced efficiency)

select class_name,group_concat(name) from t_40 group by class_name having count(*) < 3; # I can't help it. There's no statistics in front. I can only do it myself

Summary

1. Having is also used for data filtering, but its essence is for grouping statistics. If there is no grouping statistics, do not use having for data filtering

2. Never use having where you can solve problems

• where reads data from the disk and solves the problem at the source
• where can restrict invalid data from entering memory, and memory utilization is high, while having is used to filter memory data

9.2.10 order by clause

concept

order by clause: sort, sort in ascending or descending order according to a specified field

• The reference for sorting is the proof set
• order by clause after having clause word (if both exist)
• Sorting is divided into ascending and descending: ascending is the default
  • order by field [ASC]: ascending
  • order by field DESC: descending
• Multi field sorting: sorting can be subdivided after sorting according to a field

step

1. Identify the fields to sort

2. Determines the sorting method: ascending or descending

3. Use sorting

Example

1. Single field sorting: sort all students in ascending order according to their age

select * from t_40 order by age;
select * from t_40 order by age asc;

2. Multi field sorting: sort by sex in descending order, and then sort by age in ascending order

select * from t_40 order by gender desc,age;
select * from t_40 order by gender desc,age asc;

Summary

1. Sorting is to sort the previous results (data that has entered memory)

2. Multi field sorting is to sort in a small range (similar to grouping) without changing the tone of the original sorting when the first field is sorted

3. Sorting is very common in practical development, especially in numerical value and time

9.2.11 limit clause

concept

Limit clause: limit the number of data obtained (number of records)

• The limit clause must follow the order by Clause (if both exist)
• Limit there are two ways to limit the number
  • Limit quantity: limit the quantity obtained (it is not guaranteed that the specified quantity can be obtained)
  • Limit start position and quantity: limit the position and quantity of data acquisition (paging)

step

1. Are you sure you want to limit the number of records

2. How to determine the limit: limit quantity or limit position + limit quantity

Example

1. Get t_40 the first three data in the table

select * from t_40 limit 3;

2. Get t_40 data after item 3 in the table

select * from t_40 limit 3,3;
select * from t_40 limit 6,3;

Summary

1. Limit limit can effectively reduce the pressure on the server and transmission

2. limit is often used to obtain data by paging

9.2.12 summary

1. Query operation is the most used and final operation among all operations

2. The complete syntax of the query operation

select select option(all|distinck) Field list[alias]|* from data source[alias] where clause group by clause having clause order by clause limit clause;

• The order of each position cannot be changed
• Five clauses (where, group by, having, order by, limit) may not be available, but the order must be guaranteed after occurrence
• group by processes the loaded data in the memory until the end

3. The combination of many structures can actually achieve the same effect, but the process and efficiency may be different

9.3 data update

concept

Limit update: limit the number of records to be updated during update

• Limit updates are implemented through limit
• Restricted update is actually a means of local update. Generally, it is updated accurately according to conditions

step

1. Determine batch update: Eligible parts

2. Determine the number of updates

3. Use limit to limit the number of updates

Example

Select 3 members and send a 10 yuan red envelope (add to the account)

create table t_41(
	id int primary key auto_increment,
    username varchar(50) not null unique,
    password char(32) not null,
    account decimal(10,2) default 0.00
)charset utf8;

insert into t_41 values(null,'username1','password',default),
(null,'username2','password',default),
(null,'username3','password',default),(null,'username4','password',default),(null,'username5','password',default);


update t_41 set account = account + 10 limit 3;

Summary

1. Restricted update can realize batch and small-scale operation

2. In the actual development, this kind of operation rarely occurs. Generally, they are willing to operate accurately (use the where condition to specify the update condition)

3. The update operation is irreversible

9.4 data deletion

9.4.1 restricted deletion

Objective: to understand the operation of restricting deletion

concept

Limit deletion: limit the number of records to delete

• Use limit to limit the number of deletes
• In general, restricted deletion is rarely used. It is usually deleted accurately through the where condition

step

1. Are you sure you want to delete data in batch

2. It is determined that the deletion condition cannot be fully met after being qualified by the where condition

3. Use limit to limit the number of deletions to achieve the goal

Example

Delete a user without account balance (the current number of users is small, and generally the number will be larger)

delete from t_41 where account = 0 limit 1;

Summary

1. The essence of restricted deletion is also deletion. The operation is irreversible and should be used with caution

2. Prefer precise deletion

9.4.2 clear data

Objective: understand the syntax of clearing data and the effect of clearing

concept

Clear data: clear all data in the table and return all States of the table to the original state

• The essence of clearing data is to delete the table before creating it
• Clearing the data can return some changed states of the table to the original state
  • Return to initial value from growth
• Empty syntax: truncate table name

step

1. Are you sure you want to delete all data in the table

2. Determine the need to return the table state to its original state

3. truncate clear data

Example

Empty user data table

truncate t_41;

Summary

1. Emptying a data table is a more thorough way to delete data than delete, so you must be careful before using it

2. Generally, this data deletion operation is only used in the development stage, such as table data disorder or business change

10. MySQL multi table operation

Example

No matter large or small projects, there will be N tables in a database, and the tables are also associated through one-to-one, many to one or many to many relationships, such as news management system

Author table: id, user name, password

News table: id, title, content, release time, author id

When displaying news, the author's name must be displayed

• Original method: find news – > find author ID – > find author
  • Many steps
  • It is more troublesome to display news in batches
• Multi table operation: use join query to query an SQL statement

Summary

1. Multi table operation is a solution to the problems often encountered in practical development

2. Multi table operation can realize the combination or filtering of a large amount of data in the database layer

10.1 joint query

Learning objectives: understand the practical significance of joint query and master the practical application of joint query

• Joint query
• Joint query sorting

10.1.1 joint query

Objective: to understand the syntax of joint query and master the function of joint query

concept

union query: union refers to merging multiple query results into one result display

• A union query is a combination of query results (a combination of multiple select statements)
• Joint query syntax

select Query [decision field name]
	union query option
select query
...

• Joint query requirements: joint query is the joint display of results

  • The number of field results of multiple joint queries is the same
  • The fields of the union query are derived from the fields of the first query statement

• Query options: and select options

  • All: keep all records
  • distinct: keep de duplication records (default)

• You can combine unlimited tables for query

• The query results are added vertically, that is, the number of fields remains unchanged, and the number of tuples queried is equivalent to the sum of the number of queries in several tables

• The field name is the field name found in the first select query

step

1. Are you sure you want to join multiple table data

• Consistent table structure
• Data summary

2. Determine the requirements of data: keep all or remove duplicates

3. Use federated queries

Example

1. Create a table with t_40, and insert the data

create table t_42 like t_40;

insert into t_42 values(null,'Inuyasha','male',200,'Divine demon class 1'),
(null,'Twilight Govi','female',16,'Modern class 1'),
(null,'Chinese bellflower','female',88,'Mage class 1'),
(null,'Maitreya','male',28,'Mage class 2'),
(null,'Coral','female',20,'Mage class 2'),
(null,'Seven treasures','secrecy',5,'Pet class 1'),
(null,'Shashengwan','male',220,'Divine demon class 1'),
(null,'bell','female',4,'Modern class 1'),
(null,'Kouga ','male',68,'Divine demon class 1'),
(null,'Nailuo','male',255,'Divine demon class 1'),
(null,'Shenle','female',15,'Divine demon class 2');

• t_42 and t_40 structure is the same. It can be understood that because of the large amount of data, it is split into two tables

2. Join query is used to splice the data of two tables together for display

select * from t_40 
union
select * from t_42;

3. The union query option defaults to distinct

select * from t_40
union 
select * from t_40;

select * from t_40
union all
select * from t_40;

4. Joint query does not require the same field type, but only the same quantity, and the field is related to the first query statement

select name from t_40
union all
select age from t_40;

• Note: if the data cannot correspond, the query is meaningless

5. If you use where to filter the data, where is for the select instruction, not for the union result

select * from t_40 
union all
select * from t_42
where gender = 'female';

• where is only valid for the second select
• To be all valid, you need to use where for all select ions

Summary

1. union is responsible for putting together and displaying the results of multiple queries

• Increase in the number of records
• The number of fields remains unchanged (determined by the first SQL instruction)

2. Common methods of union

• Because of the large amount of data, it is stored in separate tables, and then viewed or counted uniformly
• Filter the data according to different dimensions, and then view or count them uniformly

3. Union is de duplicated by default. If you want to keep all query results, you need to use union all

10.1.2 joint query sorting

concept

Joint query sorting: sort the results of joint queries

• Order by itself sorts the memory results. The priority of union is higher than that of order by, so order by defaults to sorting the union results
• If you want to sort the results of a single select, you need two steps
  • Wrap the select instruction to be sorted in parentheses (order by is used in parentheses)
  • order by must be matched with limit to take effect (limit is a large enough value)

step

1. Determine that the federated query needs to be sorted

2. Determine sort content

• Sorting for union results
• Sort the select results before the union

3. Select the appropriate sorting method

Example

1. Will t_40 and t_ The results of table 42 are sorted in descending order by age

# Reorder the results of the joint query
select * from t_40
union all
select * from t_42
order by age desc; #It is aimed at the results after the whole union

2,t_ Table 40 in descending order of age, t_ Table 42 is sorted in ascending order by age

# Invalid method, no limit specified
(select * from t_40 order by age desc)
union 
(select * from t_42 order by age);

# Correct way
(select * from t_40 order by age desc limit 99999)
union 
(select * from t_42 order by age desc limit 99999);

Summary

1. Joint sorting needs to distinguish whether the sorting content is a select result or a union result

• union result: use sorting at the end
• Select structure: sorting needs to be used for select
  • select must be wrapped in parentheses
  • Sorting in select must be matched with limit to take effect

10.2 connection query

Learning objectives: understand the concept of join query, master the use of key join methods, and use join query to solve the problem of table relationship

• Cross connect

• Inner connection

• External connection

  • Left outer connection
  • Right outer connection

• Natural connection

• using keyword

concept

Join query: join to splice the data of two tables according to a certain condition

• There is a table on the left and right of the join keyword: the table on the left of the join keyword is called the left table, and the table on the right is called the right table
• The results of the join query are all records, and all fields in the left and right tables will be retained (field splicing)
  • The specific field data is determined according to the query requirements
  • Table field conflicts need to be distinguished by table alias and field alias
• Different join tables have different connection methods, and the processing of results is also different
• Join queries do not limit the number of tables. Multiple table joins can be performed, except that table joins need to be joined one by one (A join B join C...)
• We propose to use join query in multi table query, which is more efficient than sub query

Summary

1. Join query is to change the records of two tables into one record through field splicing: the number of fields increases

2. The purpose of join query is to combine data scattered in different tables to facilitate external use of data

10.2.1 cross connection

concept

Cross join: a cross join that does not require a connection condition

• The result of cross connection is Cartesian product
  • Each record in the left table will be connected with all records in the right table and retained
• Cross connection has no actual data value, but enriches the integrity of connection query

Example

Cross connect t_41 and t_ Table 42

select * from t_41 cross join t_42; # t_41,t_42
# You can also use
select * from t_41,t_42;

Summary

1. Cartesian product is meaningless and should be avoided as much as possible

10.2.2 internal connection

concept

Inner join: [inner] join, which connects two tables according to specified conditions and strictly joins them

• Write after from

• Inner join is to match each record of one table to another table according to conditions
  • Matching succeeded: keep the connected data
  • Matching failed: none is reserved
• Inner join syntax: left table [inner] join right table on join condition [inner join other tables on join condition...]
• The inner keyword may not be written
• You can connect unlimited tables
• To prevent conflicts, it is best to alias each table

# The table name must be added to the query id, because you don't know whose id it is (both t_44 and t_43 have IDS)
select t_43.id from t_43 inner join t_44 on t_43.id = t_44.id cross join t_45;

step

1. Determine the data composition records to be obtained from multiple tables

2. Confirm that the requirement for connection is to retain the successful connection and not the unsuccessful data

3. Use inner connection

Example

1. Design student table and major table: many to one relationship between students and major

# Student list
create table t_43(
	id int primary key auto_increment,
    name varchar(50) not null,
    course_no int
)charset utf8;
insert into t_43 values(null,'Student1',1),
(null,'Student2',1),
(null,'Student3',2),
(null,'Student4',3),
(null,'Student5',1),
(null,'Student6',default);

# Professional table
create table t_44(
	id int primary key auto_increment,
    name varchar(50) not null unique
)charset utf8;
insert into t_44 values(null,'Computer'),(null,'Software'),(null,'Network');

2. Obtain the student information of the selected major, including the selected major

# Students and majors are in two tables, so you need to connect the tables
# Students must have a major, and the major must also exist, so it is internal connection
# Connection condition: Discipline number
# The two tables have two conflicting fields: id and name, so you need to use an alias
select t_43.*,t_44.name as course_name from t_43 inner join t_44 on t_43.course_no = t_44.id;

# Aliases can also be used for table names
select s.*,c.name as c_name from t_43 as s inner join t_44 c on s.course_no = c.id;

• If the fields conflict, it will not affect MySQL, but it will exist at the same time, but it is inconvenient to use in other places in the future

3. Connect three tables

select t_43.*,t_44.name,t_45.`s_name` as course_name from t_43 inner join t_44 inner join t_45 on t_43.course_no = t_44.id ;
# perhaps
select t_43.*,t_44.name,t_45.`s_name` as course_name from t_43 inner join t_44 on t_43.course_no = t_44.id inner join t_45  ;

Principle analysis

Summary

1. The internal connection matching rule is to ensure that the left table and the right table store the connection relationship at the same time, so that the data can be retained

2. Extension: if there is no on condition for inner connection, the result is cross connection (Cartesian product), which is meaningless

3. Extension: the on keyword of the inner join can be changed to where. The result is the same, but the essence is different. The execution time of the where clause and the on clause is different. You can refer to the execution order of mysql query clauses

10.2.3 external connection

concept

External connection: external join is a loose connection method

• There are two types of external connections
  • Left outer connection (left connection): left join
  • Right outer connection: right join
• External connection can be divided into master table and slave table
  • Left join: the left table is the main table
  • Right connection: the right table is the main table
• External connection is to match the records of the master table with the records of the slave table
  • Match successfully reserved
  • Matching failed (full table): it is also reserved, but the table field is left blank
• Write after from
• grammar

Left table left|right join Right table [left|right join Other tables...] on Connection conditions

step

1. Confirm to connect the table

2. Be sure to have data protection, that is, if the data in the table fails to match, it should be retained

3. Determine master-slave table

4. Select the corresponding external connection

Example

1. Find out all student information, including the class (left connection)

# The main data is students, and all students: external connection, and the student table is the main table
# t_43 is a student, t_44 is a teacher
select s.*,c.name c_name from t_43 s left join t_44 c on s.course_no = c.id;

2. Find out all students in all classes (right connection)

# The main table is the class
select s.*,c.name c_name from t_43 s right join t_44 c on s.course_no = c.id;

Summary

1. The difference between external connection and internal connection is that when data matching fails, the external connection will keep a record

• Master table data retention
• Empty from table data

2. Whether the external connection is a left connection or a right connection, the order of fields does not affect. The left table data is displayed first, and then the right table data

3. The external connection must use on as the connection condition (it cannot be without or replaced by where)

4. This is the most frequently used operation

10.2.4 natural connection

concept

Natural join: natural join is a kind of connection query that automatically finds connection conditions

• Natural connection is not a special connection, but a connection that automatically matches conditions
• Natural connection includes natural inner connection and natural outer connection
  • natural join
  • natural left/right join
• Natural connection condition matching mode: automatically find the same field name as the connection condition (the field name is the same)
• Natural connection will find the characters with the same name in the two tables, and then compare the values of these characters one by one. If they are the same, they will be retained, and if they are different, they will be discarded

step

1. Need to query the results of linked tables

2. The table fields of linked table query can be directly related (the field names are the same: very high table structure design)

3. Select the appropriate connection mode: internal connection or external connection

4. Use natural connections

Example

1. Natural connection t_43 and t_ Table 44

select  * from t_43 natural join t_44;

2. Natural connection is regardless of whether the fields are related or not, regardless of whether the names are the same: if you want the natural connection to succeed, the design of the fields must be very standardized

create table t_45(
	s_id int primary key auto_increment,
    s_name varchar(50) not null,
    c_id int comment 'curriculum id'
)charset utf8;
insert into t_45 select * from t_43;

create table t_46(
    c_id int primary key auto_increment,
    c_name varchar(50) not null unique
)charset utf8;
insert into t_46 select * from t_44;

# Natural connection: the condition has only one same c_id
select * from t_45 natural join t_46;

• Natural join combines conditions with the same name into one field (same as data)

Summary

1. Natural connection itself is not a special connection, but an automatic condition matching based on inner connection, outer connection and cross connection

• No condition (no field with the same name): cross connect
• Conditional: internal connection / external connection (see keyword usage)

2. Natural joins are rarely used, because in general, the table design is difficult to be completely standard or irrelevant fields with the same name will not appear

10.2.5 using keyword

concept

Using keyword: if a field with the same name is used as the connection condition during connection query, using can replace on (better than on)

• Using is for a field with the same name (using(id) === A.id = B.id)
• After using keyword, the corresponding fields will be automatically merged into one
• using can use multiple fields as conditions at the same time

step

1. Data query with linked tables is required

2. The join condition fields of the two tables have the same name

3. Use the using keyword as the connection condition

Example

Query t_ All student information in 45, including the name of the class

select s.*,c.c_name from t_45 s left join t_46 c using(c_id);
select * from t_45 s left join t_46 c using(c_id);

Summary

1. using keyword is used to simplify the connection condition behavior of condition fields with the same name

2. using keyword is similar to natural connection, but it is more flexible than natural connection. You can specify valid connection conditions with the same name and ignore invalid fields with the same name

10.2.6 summary

1. Join query is the most commonly used query method in the actual development process

• Single table query operations rarely occur
• Entities (tables) are more or less related

2. The efficiency of connection query is certainly not as high as that of single table query

• Inverse normalization can be properly used to improve efficiency

3. Inner join and outer join are often used in join query

10.3 sub query

Learning objectives: understand the application scenarios of sub query and be able to use sub query to solve corresponding requirements

• Sub query classification
• scalar subquery
• Column subquery
• Row subquery
• Table subquery
• exists subquery
• Comparison mode

concept

Sub query: sub query, which uses the select query result as the condition or data source of another select query

Example

Want to find out all the student information of a major

• The target of the query is the student table
• The query criteria are in the specialty table

Based on previous knowledge, two solutions can be generated:

1. Separate query

• Find the specialty id by name from the specialty table

select c_id from t_46 where c_name = 'Professional name';

• Get the student information from the student table through the major id

select * from t_45 where c_id = 'The specialty found out id';

2. Join table query

• Connect the student table with the major table through the major id
• Filter the whole linked table results by the where condition

select s.* from t_45 s right join t_46 c using(c_id) where c.c_name = 'Professional name';

From solution analysis

1. The amount of data queried separately is small, but it is troublesome

2. Connection query is convenient, but the efficiency is not high (connect first and then filter)

If only scheme 1 could be turned into a simple way

select * from t_45 where c_id = (select c_id from t_46 where c_name = 'Professional name');

The above is the sub query

Summary

1. Subquery is to assemble some sequential queries into a query statement, so as to save the operation process and reduce the complexity

10.3.1 sub query classification

Objective: to understand the classification of sub queries and the classification principle

concept

Sub query classification: it is classified according to the location where the sub query appears or the data effect generated

• Location classification
  • From subquery: the subquery appears after from as the data source
  • Where subquery: the data conditions are made after the subquery appears in where
• Classification by results of sub query
  • Scalar subquery: the result returned by the subquery is one row and one column (one data)
  • Column subquery: the result returned by subquery is one column with multiple rows (one column of data)
  • Row subquery: the result returned by the subquery is one row with multiple columns
  • Table subquery: the result returned by the subquery is a two-dimensional table
  • exists subquery: the results returned by the subquery are Boolean results (validation type)
• Subqueries need to be wrapped with parentheses (). If necessary, alias the subquery results (from subquery)

Summary

1. Typically, we use subquery results to define classifications

2. The location partition is a partition that contains sub query results

• from subquery corresponds to table subquery (table subquery)
• where subquery

10.3.2 scalar subquery

concept

Scalar subquery: the result returned by the subquery is one row, one column, and one value

• Scalar subqueries are used for other query conditions

step

1. Are you sure you want to get data from one table (it can be multiple)

2. Confirm that the query criteria cannot be implemented in the current query table, but can be accurately obtained from other tables (only one)

3. Use scalar subqueries

Example

Get all students majoring in Computer

# Data target: student table t_ forty-five
# Condition: professional name, not in t_45, but t_ The specialty id in 45 can be accurately obtained (a value) in another table through the specialty name

select * from t_45 where c_id = (select c_id from t_46 where c_name = 'Computer');

Summary

1. Scalar subqueries usually use simple comparison symbols to make conditional queries

10.3.3 column sub query

concept

Column subquery: the results returned by the subquery are one column and multiple rows

• Column subquery is usually used for query criteria
• Column subquery can only be matched with in, because it is a collection

step

1. Are you sure you want to get data from one table (it can be multiple)

2. Confirm that the query criteria cannot be implemented in the current query table, but can be accurately obtained from other tables (one field has more than one data)

3. Use column subquery

Example

1. Get all class information with students

# The goal of data acquisition is class information
# The data acquisition condition is the class id in the student table, which is multiple

select * from t_46 where c_id in (select distinct c_id from t_45 where c_id is not null);

Summary

1. Column sub query is usually used as the condition of external main query, and in is used for judgment

10.3.4 line sub query

concept

Row subquery: the result returned by the subquery is one row with multiple columns

• The row sub query needs to construct row elements in the criteria (multiple fields form the query matching criteria)
  • (element 1, element 2,... Element N)
• Row subquery is also used as the result condition of the main query
• Row sub query can query the data of a table

step

1. Determine that the condition for obtaining data is not just a field

2. Determine that the source of data conditions is not in the current table (or in the current table), but can be accurately obtained through conditions (one row and multiple columns)

3. Use row subquery

Example

Obtain the information of students whose gender and age are the same as Maitreya in the student table

# There are multiple query criteria: gender and age

# Solution: two scalar subqueries
select * from t_40 where gender = (select gender from t_42 where name = 'Maitreya') and age = (select age from t_42 where name = 'Maitreya');

Problem analysis: the above query solves the problem, but two sub queries are used (efficiency is reduced), and the query statements are the same, but the fields are different. Row sub queries can be used to solve the problem

# Build condition line elements (gender,age)

select * from t_40 where (gender,age) = (select gender,age from t_42 where name = 'Maitreya');

Summary

1. Row subqueries can be replaced by multiple scalar subqueries, but the efficiency of row subqueries is higher than that of multiple scalars. Scalar subqueries are not used to solve row subqueries

• If the data source is not in a table, you can consider using multiple scalar subqueries

10.3.5 sub table query

concept

Table subquery: the results returned by the subquery are multi row and multi column (2D table)

• Table subqueries often appear after from as data sources (from subqueries)
• Table sub query is usually used to process the data once, and then hand it over to the outside for secondary processing

step

1. The data to be queried cannot be directly processed through an SQL query (which may be caused by the sequence relationship)

2. If the results are processed first (multiple rows and columns), then another level of external result query processing can complete the target

3. Use table subquery

Example

Get the oldest student information (name, age, class name) in each class in the student table, and then sort and display it in descending order by age

# Try to solve it directly
select any_value(name),max(age) m_age,class_name from t_42 group by class_name order by m_age desc;

• Any in group statistics_ Value takes the first record data after grouping (Inuyasha), and what we want is the maximum

Solution: if the students in all classes are sorted in descending order before grouping, the first data in the grouping is the data that meets the conditions. But the problem is: order by must appear after group by. How to solve it?

# Order by must be solved before group by: we must find a way to make order by before group by and not in the same select instruction (the same one has no solution)
# Subquery must be used to solve the problem without SQL, and the result of subquery should be all record information, so it should be a table subquery and a data source

select any_value(name),max(age),class_name from 
(select name,age,class_name from t_42 order by age desc) as t
group by class_name;

• Still invalid: the reason is that in order for the order by in the subquery to take effect after MySQL 7, you need to bring the limit to the subquery like the joint query

select any_value(name),max(age),class_name from 
(select name,age,class_name from t_42 order by age desc limit 99999) as t
group by class_name;

• Because the results have been sorted by order by during the sub query, the final results will not be sorted after grouping statistics. If sorting is needed, it can be sorted after the final results
  • If you want to use field sorting, it is recommended to use aliases in the select field of external queries (otherwise statistics are required)

select any_value(name),max(age) m_age,class_name from 
(select name,age,class_name from t_42 order by age desc limit 99999) as t
group by class_name order by m_age;

Summary

1. Table subquery usually solves the problem of providing data source

2. Business in table sub query

• The order of clauses used in a select instruction cannot meet the query conditions
• The source of data may be multiple data tables

3. Special note: after MySQL 7, the order by clause used in the subquery needs to be combined with limit to take effect

10.3.6 exists sub query

concept

exists sub query: substitute query, which substitutes each row of the main table (external query) into the sub table (sub query table) for verification

• The result returned by the subquery is a boolean result
  • Successful return true
  • Failure returns false
• The exists subquery is usually used as a where condition
  • Where exists (subquery)
• Compare the tuples one by one with the sub query, and the returned result is true, otherwise it is false

step

1. Confirm that the data of the query comes from the main table

2. The determination condition is to go to the sub table (other tables) for verification: there is no need to go to the sub table to obtain data

3. Use exists subquery

Example

Get all class information t_ forty-six

# The data obtained is the class table t_ forty-six
# Are there any students in the class who need to be in t_ As confirmed in 45, t is not required_ 45 provide any data display
# Put t_ The data of 46 is put into the sub query one by one. As long as there is a result, it returns true and displays the t_46 data
select * from t_46 c where exists(select c_id from t_45 where c.c_id = c_id);

Summary

1. The exists subquery is usually used to solve the query problem that does not need data but needs to confirm the relationship in the table

• Select as few fields as possible in the exists subquery (it is not recommended to use *), because they are worthless

10.3.7 comparison method

concept

Comparison method: some specific comparison methods can be used in sub queries

• Specific comparison methods are used together based on comparison symbols

• =All (subquery): all the following conditions are met

  • >All (result set): the data should be greater than all the data in the result set

• =Any (subquery): any condition is satisfied

  • =Any (result set): as long as the data is equal to any element in the result set

• =Some (subquery): satisfy any condition (exactly the same as any)

• Result set: it can be direct data or subquery results (usually column subquery)

Example

1. Find t_40 table and t_42 information of the same age in the table

# Data acquisition in t_ Table 40
# The data condition is in t_ Table 42

# Solution 1: use in column subquery
select * from t_40 where age in (select distinct age from t_42);

# Solution 2: use the exists subquery
select * from t_40 t1 where exists(select id from t_42 where t1.age = age);

# Solution 3: match with any or some (column subquery)
select * from t_40 where age = some(select age from t_42);

select * from t_40 where age = any(select age from t_42);

Summary

1. In fact, many comparison methods can be replaced. The more accurate the data matching method, the higher the efficiency

10.3.8 summary

1. Scalar subqueries, column subqueries, and exists subqueries are often used in subqueries

2. The efficiency of sub query is lower than that of join query, so it should be selected and used appropriately

• The subquery is executed once every time the records of the main table match (where subquery)
  • Large primary table data and small sub table data: less impact
  • Small primary table data and large sub table data: great impact
• Since the from sub query is executed only once, it has little impact

3. Theoretically, the nesting of subqueries is not limited, but considering the reduction of efficiency, it is not recommended to use subquery nesting

11. MySQL security management

Learning objectives: understand the security management methods involved in MySQL, as well as the concepts, usage methods and usage scenarios of various security management

• Foreign key constraint
• transaction management
• Pretreatment
• view
• Data backup and restore
• user management

concept

Security management: use various ways to ensure the security of database and data

Example

Ctrip's database was deleted by programmers and ran away

• If there is user management, it can be deleted without permission by permission restriction
• If there is data backup, even if the data is deleted, the data can be restored quickly to reduce the loss
• ...

Summary

1. Security management is an issue that everyone who comes into contact with the database should consider, especially DBA (database administrator)

2. There are many dimensions of database security

• Manage security: users, permissions, backup and restore, etc
• Structural security: foreign keys, views, transactions, etc
• Execution layer: preprocessing

11.1 foreign key constraints

11.1.1 foreign keys

concept

Foreign key: foreign key. The fields in the table that point to the primary key of the external table are defined as foreign keys

• Foreign keys must be specified by syntax to be called foreign keys
  • [constraint foreign key name] foreign key references external table (primary key field)
• Foreign key composition conditions
  • The foreign key field must be consistent with the primary key field type of the corresponding table
  • The foreign key field itself is required to be an index (an index will be generated automatically when a foreign key is created)

step

1. Confirm that the fields in the table are associated with another table

2. Use foreign keys to explicitly associate appearances

3. Foreign key constraint succeeded

Example

1. Create a major table and a student table. The major id in the student table points to the major table id

create table t_47(
	id int primary key auto_increment,
    name varchar(50) not null unique
)charset utf8;

create table t_48(
	id int primary key auto_increment,
    name varchar(50) not null,
    c_id int comment 'point t_46 In table id Primary key',
    constraint `c_id` foreign key(c_id) references t_47(id)
)charset utf8;

2. Foreign keys can be generated automatically without specifying a name

create table t_49(
	id int primary key auto_increment,
    name varchar(50) not null,
    c_id int,
    foreign key(c_id) references t_47(id)
)charset utf8;

show create table t_49;

Summary

1. Foreign keys need to ensure that the fields are consistent with the externally connected primary key fields

2. A table can have multiple foreign keys, but a field can only produce one foreign key

11.1.2 foreign key constraints

concept

Foreign key constraint: when a table establishes a foreign key relationship, the foreign key will have a constraint effect on the data in the main table (the table to which the foreign key points) and the sub table (the table where the foreign key is located)

• The foreign key constrains the write operation (the default operation)
  • Add: the foreign key corresponding to the data inserted in the sub table must exist in the main table
  • Modify: if the records of the primary table exist in the sub table, the primary key of the primary table cannot be modified (the primary key cannot be modified)
  • Delete: if records in the primary table exist in the child table, the primary key of the primary table cannot be deleted
  • Delete: if records in the primary table exist in the child table, the primary key of the primary table cannot be deleted
• Foreign key constraint control: foreign keys can control the constraint effect of foreign keys at the time of definition
  • control type
    • on update: the performance of the child table when the parent table is updated
    • on delete: the performance of the child table when the parent table is deleted
  • control mode
    • Cascade: cascade operation. After the parent table operation, the child table follows the operation
    • set null: empty operation. After the parent table operation, the foreign key fields associated with the child table are empty
    • restrict: strict mode. Parent table operation is not allowed (default)
    • no action: sub table no matter

step

1. Determines the foreign key Association of the table

2. Determine the constraint control of the master table

3. Explicit use of appropriate constraint controls

4. System automatic constraint

Example

1. The sub table cannot insert data that does not exist in the main table

insert into t_48 values(null,'Tony',2);	# error

insert into t_47 values(null,'English');
insert into t_48 values(null,'Peny',1);

2. After the default foreign key is generated, the primary key cannot update the associated primary key field or delete the associated primary key record

# error
update t_47 set id = 2;
#error
delete from t_47 where id = 1;

3. Restrict foreign key constraints, generally use update cascade, delete and empty

• on update cascade: update cascade
• on delete set null: delete set null

create table t_50(
	id int primary key auto_increment,
    name varchar(50) not null unique
)charset utf8;

create table t_51(
	id int primary key auto_increment,
    name varchar(50) not null,
    c_id int, # If you want to allow nulls, you cannot not be null
    foreign key(c_id) references t_50(id) on update cascade on delete set null
)charset utf8;

insert into t_50 values(null,'Chinese'),(null,'Computer');
insert into t_51 values(null,'Tony',1),(null,'Petter',2);

• The child table is still not allowed to insert foreign keys that do not exist in the parent table
• However, data with Null foreign key can be inserted

# error
insert into t_51 values(null,'Lilei',3);

insert into t_51 values(null,'Lilei',NULL); # OK                               

• The update (primary key) of the parent table will automatically cascade the associated foreign keys

update t_50 set id = 3 where id = 1;

• Deleting the parent table will automatically empty the associated foreign key

delete from t_50 where id = 3;

Summary

1. Foreign key constraints have constraints on both child and parent tables

• Child table constraint: a child table cannot insert a foreign key that does not exist in the parent table
• Parent table constraint
  • Update constraints (not allowed by default)
  • Delete constraint (not allowed by default)
• General constraints
  • update cascade
  • Delete empty

2. Foreign key constraints enhance the security and reliability of data, but will increase the uncontrollability of the program for data. Therefore, in the actual development, the program logic control is generally used to ensure the integrity and security of data, and there is less external use

11.1.3 foreign key management

concept

Foreign key management: maintain foreign keys at the later stage of table creation

• New foreign key

alter table Table name add [constraint Foreign key name] foreign key(Foreign key field) references Table name(Primary key) [on Foreign key constraint]

• Delete foreign key

# First, use this statement to view the foreign key name
show create table Table name;
alter table Table name drop foreign key Foreign key name;

• Update foreign key: delete before adding

Example

1. Delete foreign key

alter table t_51 drop foreign key t_51_ibfk_1;	# System generated foreign keys

2. Append foreign key

alter table t_51 add constraint `t_51_50` foreign key(c_id) references t_50(id);
# it's fine too
alter table t_51 add foreign key(c_id) references t_50(id);

• Note: to append a foreign key, you need to ensure that the value in the foreign key field is either Null or can be found in the parent table

Summary

1. The use of foreign keys is best maintained when creating a table structure. Later maintenance requires sub table data

11.2 transaction security

Learning objectives: understand the concept and characteristics of transaction security, master the application of transaction security, and be able to use transaction security to solve corresponding problems

• Transaction concept
• transaction processing
• Transaction characteristics

11.2.1 affairs

concept

Transaction: something to do

• A transaction in a computer refers to a program execution unit (write operation)

• Transaction security: after the transaction is executed, it ensures that the execution of the transaction is effective without causing data disorder

• Transaction security is usually aimed at the unified result of a series of operations (multiple transactions)

• The default write operation in MySQL is direct write

  • Execute write SQL
  • Synchronize to Datasheet

Example

Bank transfer: transfer from account A to account B

Create data table

create table t_52(
	id int primary key auto_increment,
    name varchar(50) not null,
    account decimal(10,2) default 0.00
)charset utf8;

insert into t_52 values(null,'Tom',10000),(null,'Lucy',100);

Transfer: there must be two steps for Tom to transfer money to Lucy

# Tom deducted money
update t_52 set account = account - 1000 where id = 1;

# Lucy takes the money
update t_52 set account = account + 1000 where id = 2;

• The above two steps must be successful in order to be successful
• The two-step operation cannot ensure which step will go wrong (especially the second step)
• In order to ensure the success of both steps, it can be called transaction security

Principles of transaction security

Transaction security is to inform the system before operation that all subsequent operations are not synchronized to the data table, but recorded in the transaction log to guide all subsequent operations to be successful before synchronization; Otherwise, cancel all operations

Take the above transfer as an example

Summary

1. The purpose of transaction is to ensure the consistency of continuous operations and the integrity of results

2. The principle of transaction is to temporarily save the operation results in the transaction log, wait until all the operation results are successful, and then synchronize them to the data table

11.2.2 transaction processing

concept

Transaction processing: realize transaction management automatically or manually

• Automatic transaction processing: by default, the system synchronizes directly to the data table after the operation (transaction closed status)
  • System control: variable autocommit (value ON, auto commit)
• Manual transactions
  • Start transaction: start transaction
  • Close transaction
    • Commit transaction: commit (synchronize to data table while clearing log data)
    • Rollback transaction: rollback (clear log data)
• Transaction rollback: during the execution of a long transaction, a rollback point can be set at a successful node, and subsequent rollback can return to a successful point
  • Set rollback point: savepoint rollback point name
  • Rollback to rollback point: rollback to rollback point name

step

1. Determine the transaction operation required by the operation

2. Open transaction

3. Execute transaction

• If rollback point setting is required: set rollback point
• If rollback is required: rollback to the rollback point

4. End transaction

• Successfully commit the transaction: synchronize to the data table and clear the transaction log
• Failed to rollback transaction: clear transaction log

Example

1. Manual transaction: enable transaction transfer and successfully submit the transaction

# Open transaction
start transaction;

# Tom deducted money
update t_52 set account = account - 1000 where id  = 1;

# Lucy takes the money
update t_52 set account = account + 1000 where id  = 2;

# Commit transaction
commit;

2. Manual transaction: enable transaction transfer and successfully commit the transaction (rollback point)

# Open transaction
start transaction;

# Tom deducted money
update t_52 set account = account - 1000 where id= 1;

# Set rollback point
savepoint sp1;

# Lucy takes the money
update t_52 set account = account + 10000 where id= 2;

# The operation failed and returned to the rollback point
rollback to sp1;

# Lucy takes the money
update t_52 set account = account + 1000 where id= 2;

# Commit transaction
commit;

3. Automatic transaction

• Mysql automatically submits transactions by default: therefore, once a transaction occurs, it will be written to the data table immediately (multiple transactions cannot complete the task together)

show variables like 'autocommit';

• Turn off automatic submission transaction (current setting level user level: current user level connection is valid)

set autocommit = 0;	

• Manually commit transactions

insert into t_52 values(null,'Liu',1000);
commit;

Summary

1. Transaction processing should be applied to large transactions composed of multiple write operations, such as financial security

2. Transaction processing usually uses manual transaction control. There is no need to modify the original automatic submission mechanism to start all transactions

3. Extension: transaction processing support is conditional

• The storage engine needs to be InnoDB

11.2.3 transaction characteristics

concept

Transaction features: transaction processing has four characteristics of ACID

• Atomicity: a transaction operation is a whole and cannot be split. It either succeeds or fails
• Consistency: the transaction must be in a consistency state before and after execution, and the integrity of the data is not destroyed (the accuracy of the transaction logic)
• Isolation: other transactions are invisible during transaction operation
• Durability: once a transaction is committed, the result cannot be changed

Summary

1. The transaction characteristics can only be seen completely and cordially by combining multiple users during the corresponding transaction operation

2. Expand

• Transaction lock: when a transaction is opened, another transaction cannot operate on the data occupied by the current transaction lock
  • Row location: if the current transaction occupies only one row (id accurately retrieves data), other transactions can operate on other rows of data
  • Table: if the current transaction occupies the whole table (like scanning the whole table), other transactions cannot operate on the whole table
• Dirty read: a transaction is operating on some data but has not yet committed, while another transaction reads that the "historical" data has actually been modified

11.3 pretreatment

11.3.1 pretreatment

concept

Preprocessing: prepare statement, a way to precompile SQL instructions (and then execute the command)

• Different from direct processing, preprocessing sends the SQL instructions to be executed to the server for compilation, and then executes through the instructions
  • Send preprocessing: prepare preprocessing name from 'SQL instruction to be executed'
  • Execute preprocessing: execute preprocessing name
• Pretreatment management
  • The preprocessing belongs to the session level: that is, the current user's current connection is valid (the disconnected meeting is cleaned up by the server)
  • Delete preprocessing: deallocate | drop prepare preprocessing name

step

1. The SQL instruction to be executed wants to use preprocessing

• Repeated instructions
• Instructions related to data security

2. Send preprocessing instruction

3. Perform preprocessing

Example

1. The SQL instruction to query students needs to be repeated many times

# Normal operation
select * from t_42;

# Preprocessing: send preprocessing
prepare p1 from 'select * from t_42';

# Preprocessing operations: performing preprocessing
execute p1;

# Delete preprocessing
deallocate  prepare p1;
# perhaps
drop prepare p1;

Pretreatment principle

Comparison between ordinary treatment and pretreatment

Summary

1. Preprocessing is to send the structure (SQL instruction) to be executed to the server in advance. The server compiles but does not execute it. It is not executed until the instruction is executed

2. Role of pretreatment

• performance optimization
  • Efficiency optimization: the same SQL does not need to be compiled every time (compilation takes time)
    • Normal processing: compilation is required every time
    • Preprocessing: compile once
  • Network Transmission Optimization: complex SQL instructions only need to be transmitted once
    • Normal processing: network transmission of SQL instructions is required every time
    • Preprocessing: transmit SQL instructions once and execute them later
• Security: effectively prevent SQL injection (the execution mode of SQL is changed through the special use of external data)
  • Normal processing: send it directly to the server for execution (SQL injection is easy to occur)
  • Preprocessing: the structure is sent, and the data is sent in later (the incoming protocol is different, and the data security is high)

11.3.2 pretreatment and parameter transfer

concept

Preprocessing parameter transfer: variable data required for preprocessing is passed in during preprocessing

• Generally, preprocessing will not be fixed SQL instructions, but has some execution (conditions) with variable data

  • Use placeholders for the location of variable data? seize a seat

  prepare Preprocessing name from `The change part of preprocessing instruction is used?replace`
  

• During preprocessing, the actual data is transferred in to execute SQL instead of placeholders

  • Data is stored in the variable (the value passed in by preprocessing must be saved by the variable)

  set @Variable name = value
  

  • Use the using keyword to pass parameters

  execute Preprocessing name using @Variable name
  

  • The data is passed in the same order as the placeholders in the preprocessing

step

1. The same SQL instruction needs to be executed N times, but the conditions are inconsistent

2. Send preprocessing instructions using preprocessing placeholders

3. Set a variable to save the data to be passed in

4. Execute preprocessing and carry variable parameters

Example

To t_40 insert data into table

# Prepare preprocessing: parameters involved
prepare t_40_insert from 'insert into t_40 values(null,?,?,?,?)';

# Set variables and pass in parameters
set @name = 'Kabuto ';
set @gender = 'male';
set @age = 23;
set @class_name = 'Muye class 1';

# Perform preprocessing
# Note that the order must be right, and? One to one correspondence
execute t_40_insert using @name,@gender,@age,@class_name;

Summary

1. Preprocessing parameter transfer is the most common way to apply preprocessing

2. Preprocessing instructions can be applied to add, delete, modify and query various instructions

3. If the preprocessed instructions are not reused in a connection, the preprocessing will reduce the efficiency. Therefore, if the security factor is not taken into account in the execution of preprocessing, SQL must be executed repeatedly

11.4 view

11.4.1 view

concept

View: view, a virtual table composed of select instructions

• Views are virtual tables that can be managed using table management (Structure Management)
  • The table that provides data for the view is called the base table

# Create view
create view View name as select instructions;

# Access view: generally query
select */Field name from View name;

• Views have structures, but do not store data
  • Structure: select the selected field
  • Data: select instruction executed when accessing a view

step

1. Determine that you need to use the view to provide data

• The data source is multiple tables
• Provide data support for external systems (protect base table data)

2. Use view

Example

1. It is often used to provide data of student details externally: it can be realized by using the view

# When providing external data, protect the security of the data itself
# Need long-term use

# Create view
create view v_student_info as select * from t_45 left join t_46 using(c_id);
# Working with Views: working like tables
select * from v_student_info;

2. Some complex SQL is often used, such as the operation of connecting multiple tables: it can be realized by using the view

# Department list
create table t_53(
	id int primary key auto_increment,
    name varchar(50) not null
)charset utf8;
insert into t_53 values(null,'Language Department'),(null,'Department of Archaeology');

# Professional table
create table t_54(
    id int primary key auto_increment,
    name varchar(50) not null,
    s_id int not null comment 'college id'
)charset utf8;
insert into t_54 values(null,'English',1),(null,'Chinese',1);

# Student list
create table t_55(
	id int primary key auto_increment,
    name varchar(50) not null,
    s_id int not null comment 'major Id'
)charset utf8;
insert into t_55 values(null,'Lilei',2),(null,'Mark',2),(null,'Tony',1);

# Get details of all students
select stu.*,sub.name as sub_name,sub.s_id as sch_id,sch.name as sch_name from t_55 as stu left join t_54 sub on stu.s_id = sub.id left join t_53 sch on sub.s_id = sch.id;

# Save such complex instructions in view, and you can access the view directly later
create view v_student_detail as select stu.*,sub.name as sub_name,sub.s_id as sch_id,sch.name as sch_name from t_55 as stu left join t_54 sub on stu.s_id = sub.id left join t_53 sch on sub.s_id = sch.id;

select * from v_student_detail;

Summary

1. View is used to provide data support. It is a structure composed of select instructions

• existential construction
• There is no data (the data is obtained dynamically by calling the select instruction when in use)

2. Purpose of view

• It is convenient to provide comprehensive data: data can be organized according to requirements without data redundancy in the database
• Data security: views are essentially derived from data base tables, but they can protect basic data structures externally

11.4.2 view management

concept

View management: management of view structure

• View view: displays the view structure and specific view information

show tables;	# View all views
show create table|view View name;	# To view the view creation instructions, you can use table
desc View name;	 # View view structure

• View modification: changing view logic

# change views
alter view View name as New query instruction;
create or replace view View name as New query instruction;	# Create new or replace new

• View delete

drop view View name; 

Example

1. View all views and view details

show tables;	# View all tables, including views
desc v_student_detail;	# View view structure
show create view v_student_detail;	# View view creation details
# perhaps
show create table v_student_detail;

2. Modify view: reset view data logic

alter view v_student_info as select t1.s_name,t2.c_name from t_45 t1 left join t_46 t2 using(c_id);
create or replace view v_student_info as select t1.s_name,t2.c_name from t_45 t1 left join t_46 t2 using(c_id);

3. Delete view

drop view v_student_info;

Summary

1. The view operation is similar to the table operation. Generally, it will not be modified and maintained frequently, but will be maintained at the beginning

2. View management can manage structures like tables

11.4.3 view data operation

concept

View data operation: directly write the view (add, delete and modify), and then change the base table data

• All data operations of the view are the final data operations on the base table
• View operating conditions
  • Multi base table view: operation is not allowed (addition, deletion and modification are not allowed)
  • Single base table view: addition, deletion and modification are allowed
    • New condition: the fields of the view must contain all the fields in the base table that cannot be empty
  • with check option: operation check rule
    • This rule is not required by default (specified when creating a view): the view operation can only meet the above conditions
    • Add this rule: after the data operation of the view, you must ensure that the view can find out the data operated by the view (otherwise it will fail)

step

1. Determine the view you want to use based on your needs

2. Confirm that the view is allowed to perform data operations (usually user permission settings, and it is a single base table view)

3. Determine whether the operation of view data needs operation check (there is a where condition filter, and it only affects the addition and update)

• Required: add with check option
• unwanted

4. Use the view for data operation (the final data write point is the base table)

Example

1. Add a single table view and a multi table view

create view v_student_1 as select s_id,s_name from t_45;
create view v_student_2 as select s.*,c.c_name from t_45 s left join t_46 c using(c_id);
create or replace view v_student_3 as select * from t_45 where c_id is not null with check option;

2. New data

insert into v_student_1 values(null,'student7');	# Correct: the view contains all required fields
insert into v_student_2 values(null,'student8',null,null); # Error: not pluggable
insert into v_student_3 values(null,'student8',null);	# Error: check option because the third field C_ The ID is NULL, which does not meet the view filtering conditions and cannot be found
insert into v_student_3 values(null,'Student9',1);	# correct

3. Update data

update v_student_1 set s_name = 'boy' where s_id = 8;
update v_student_2 set s_name = 'boy' where s_id = 7; # Error: cannot be modified
update v_student_3 set c_id = null where s_id = 1;	  # Error: check option, modified C_ The ID is null and does not meet the view filter criteria
update v_student_3 set s_name = 'boy' where s_id = 1; # correct

4. Delete data

delete from v_student_1 where s_id = 2;
delete from v_student_2 where s_id = 3;	# Error: cannot delete
delete from v_student_3 where s_id = 1;	# It can be deleted, indicating that the with check option does not affect the deletion operation

Summary

1. View data operation is generally not allowed. Generally, the reason why you provide a view externally is to provide a read-only operation of data

2. The view data operation is related to the number of base tables and fields of the view

• The multi base table view does not allow any write operations
• The single base table view can be updated, deleted and added as appropriate (the view contains all fields in the base table that cannot be empty)

3. with check option is valid for the view composition with where conditions. You need to manually select whether to add this option

• After adding and modifying view data, it must be consistent with the original query results (the new data must be visible in the view)
• The deletion of view data is not affected by the with check option
• The addition and modification of view data can be found for the current view. Otherwise, neither error nor effect will be reported
• The with check option can be more complex. If you are interested, you can have an in-depth understanding

11.4.4 view algorithm

concept

View algorithm: refers to the processing of internal select instructions during view execution

• The view algorithm is specified when the view is created

create algorithm = algorithm view View name as select instructions;

• There are three view algorithms
  • undefined: by default, no algorithm is defined, that is, the system automatically selects the algorithm
  • Merge: the merge algorithm is to merge the query statement outside the view with the select statement inside the view and execute it only once, which is highly efficient (the system gives priority to selection)
  • Temptable: temporary table algorithm, that is, the system finds out the select statement of the view, obtains a temporary table, and then queries externally. This algorithm is recommended (the temptable view does not allow write operations)

step

1. OK to use view

2. Determine the view algorithm: consider the clause usage in the SQL instruction inside the view

3. Create and use views

Example

1. Create three different algorithm views

create algorithm = undefined view v_student_4 as select * from t_42 order by age desc;
create algorithm = merge view v_student_5 as select * from t_42 order by age desc;
create algorithm = temptable view v_student_6 as select * from t_42 order by age desc;

2. Use view: in order to reflect the algorithm effect, add grouping effect to the view

select count(*),name,age,class_name,max(age) from v_student_4 group by class_name;
# Due to the combination of two select statements, it is equivalent to
select COUNT(*),name,age,class_name,MAX(age) from t_42 group by class_name order by age desc

select count(*),name,age,class_name,max(age) from v_student_5 group by class_name;
# Due to the combination of two select statements, it is equivalent to
select COUNT(*),name,age,class_name,MAX(age) from t_42 group by class_name order by age desc

The picture effect is consistent with the above figure

select count(*),name,age,class_name,max(age) from v_student_6 group by class_name;
# Because the two select statements are executed separately, it is equivalent to a sub query, which is equivalent to
select COUNT(*),name,age,class_name,MAX(age) from (select * from t_42 order by age desc) t group by class_name;

3. The view of temporary table algorithm cannot be inserted

insert into v_student_6 values(null,'Myoga ','male',100,'Divine demon class 1'); # Error: not pluggable

Summary

1. View algorithm is an optimization idea that combines external query instructions. There are two main optimization methods

• Merge: a merge algorithm that merges a view's select with an external select and executes it once (high efficiency)
• temptable: temporary table algorithm. The view instruction is executed separately to get a two-dimensional table, and then external select is executed (SAFE)
• Undefined: undefined algorithm is an algorithm automatically selected by the system. The system prefers to select merge algorithm

2. Generally, the feasibility of view algorithm should be considered when designing the view. Generally, if order by sorting occurs in the view, temptable algorithm should be considered

• As long as the merge is completed, the data will not be confused due to the order of clauses (the order by and group by are confused, which is easy to cause problems)

11.5 data backup and restore

concept

backup: backup stores data or structure in another file according to a certain format to ensure the integrity and security of stage data

• Save the current correct data
• Backups usually have fixed time nodes

Restore: restore, in case of problems with the current data, replace the previously backed up data with the current data to ensure the continuous and correct operation of the system

• Data restore based on backup
• Backup and restore may not guarantee recovery of all losses

Summary

1. Data backup and restore is a must for a normal database

• Ensure data security
• Minimize the risk of data errors

2. Database backup and restore are the most basic technical requirements of a DBA (developers should also be able to)

11.5.1 table data backup

concept

Table data backup: backup the data in the table separately (data export)

• Find out the data from the table and store it in an external file according to a certain format
  • Field formatting: fields
    • terminated by: the symbol used after the end of field data. The default is space
    • enclosed by: the field data is wrapped. By default, there is nothing
    • escaped by: processing of special characters. Escape is the default
  • Line formatting: lines
    • terminated by: line end symbol. The default is \ n, auto wrap
    • starting by: the line start symbol is not displayed by default

select Field list|*  into outfile External file path 
	[fields terminated by format enclosed by format]
	[lines terminated by format starting by format]
from data sheet;

• Table data backup does not limit whether the data source is one table or multiple tables (tables can be connected)

step

1. Determine that the table data needs to be exported (backed up), and the field name does not need to be considered

2. Determine the processing of exported data

• Field processing (can be default)
• Line processing (can be default)

3. Perform table data export

Example

1. Will t_ The data of table 40 is exported to a file by default

select * into outfile  'D:/t_40.csv' from t_40;

• If the system prompts: Secure File priv problem, it indicates that the configuration does not allow file import and export. This configuration item needs to be configured in the configuration file (my.ini): Secure File priv = data import and export path / no value specified (it takes effect after restarting MySQL)

2. Will t_ The data of table 40 is exported to a file according to the specified format

select name,gender,age,class_name into outfile 'D:/t_40_self.csv'
	fields terminated by '-' enclosed by '"'
	lines starting by 'GO:'
from t_40;

3. Multi table data export: t_45 connection t_ Table 46

select * into outfile 'D:/t_45_46.csv' from t_45 left join t_46 using(c_id);

Summary

1. Table data backup is to export the data in the table to an external file according to a certain format for saving

• After the data is taken out, it is convenient for processing management
• SQL has different syntax, but the data recognition is consistent, so it is convenient to switch between databases

2. Table data backup is usually used to store data back into the table or to other tables after data processing

3. At present, this method is rarely used for data backup

11.5.2 table data restore

Objective: to understand the concept of table data restore and the problems that table data restore can solve

concept

Table data restore: import data conforming to the data table structure into the data table (data import)

• Parse the data in a certain format into data conforming to the table field format according to a certain parsing method, and import it into the data table
  • Field processing
  • Row processing

load data infile 'Path of data file' into table Table name
	[fields terminated by format enclosed by format]
	[lines terminated by format starting by format]
	[(Field list)];	# If it is a partial table field, you must put the field list last

• Data file source
  • Data file for table data backup
  • Data obtained or produced externally that conforms to the format

step

1. The data in the data file meets the field requirements of the data table

• data type
• Corresponding number of fields (except self increasing id and fields that can be empty)

2. The data in the data file can meet the table field requirements through field processing and row processing

3. Using data import

Example

1. Will t_40. Import CSV data into DB_ A and T in 3 Database_ 40 tables with consistent table structure

create table t_40 like db_2.t_40;

load data infile 'D:/t_40.csv' into table t_40; # There may be a problem with the character set           
load data infile 'D:/t_40.csv' into table t_40 charset utf8; 

Note: when loading data, you need to pay attention to the character set of external data. When loading, you need to specify the character set as the external file data format, and add the character set charset external file data character set after the table

2. Will t_ 40_ Import the data in the self file into dB_ 3.t_ In table 40

load data infile 'D:/t_40_self.csv' into table t_40 charset utf8 fields terminated by '-' enclosed by '"' lines starting by 'GO:' (name,gender,age,class_name) ;

Summary

1. Table data restore is actually importing qualified external data into the data table according to certain format requirements

2. Data import can solve the problem of importing data in different formats or between different database products into the corresponding database products

3. At present, this method is rarely used for data import: data generation should be business generation rather than manual participation (destroying the objective validity of data and making the data untrue)

11.5.3 file backup

Objective: to understand the concept and principle of file backup

concept

File backup: directly retain the file of the data table, which belongs to physical backup

• The file backup operation is simple, and the data table (or database folder) is directly saved and migrated
• The files generated by different table storage engines in MySQL are inconsistent, and the saving methods are also inconsistent
  • InnoDB: the table structure file is in the ibd file, and the data and index are stored in the external unified ibdata file (the frm suffix before Mysql7)
  • MyIsam: the data, structure and index of each table are independent files. You can directly find three files and migrate them

step

1. Set backup time node

2. Set backup file storage location

3. Determine the storage engine for the backup table

4. File backup by node: transfer (copy) files to other storage locations

Example

1. File backup of MyIsam table: find three files, copy and migrate

• sdi: table structure file
• MYI: index file
• MYD: data file

2. File backup of InnoDB table: find two files, copy and migrate

• ibd: table structure file
• ibdata: all InnoDB data files

Summary

1. File backup is a simple and crude data backup method, which directly packs and manages data files

• MyIsam storage engine is relatively suitable for file backup because MyIsam storage engine table files are independent and not associated with other tables
• InnoDB is not suitable for file backup because the entire data storage file ibdata (suitable for whole database migration) needs to be backed up no matter one table or all data tables are backed up

2. File backup takes up a lot of disk space

11.5.4 file restore

Objective: to understand the concept of file restore and the method of file restore

concept

File restore: use the backup file to replace the problem file and restore it to the good state before backup

• Directly put the backed up files in the corresponding location

• File restore impact

  • MyIsam storage engine: single table backup and single table restore, without affecting any other data
  • InnoDB storage engine: single table structure, whole database data, only suitable for whole database backup and restore, otherwise other InnoDB storage tables will be affected

step

1. The data file with the problem was found

• MyIsam: three files: table structure, table data and table index (delete them)
• InnoDB: table structure, whole database data table ibdata (deleted)

2. Place the backup data in the corresponding deleted file location

Example

1. Data migration of MyIsam data backup table: single table is migrated to different databases

2. InnoDB data backup completes the migration of the entire database (including database user information)

Summary

1. Restore of file backups is usually less used

• Data backup takes up a lot of space, so this backup method is less
• The backup of InnoDB is for all InnoDB tables in the entire database. Restoring will overwrite all tables that do not need to be restored

2. File backup and restore can usually be used in the case of data migration

• MyISAM: migration of independent tables (rarely used now, MyISAM rarely used)
• InnoDB: migration of the entire database

11.5.5 SQL backup

concept

SQL backup: saving database data to files in the form of SQL instructions is a logical backup

• SQL backup is realized by using Mysqldump.exe client

• SQL backup is to save the backup target (data table) in the form of SQL instructions from the table structure, data and other information to a file

  mysqldump.exe -h -P -u -p [backup options] database name [data table list] > sql file path

  • mysqldump.exe can be written as mysqldump

• There are many backup options. The most common one is the number of database backups

  • Full database backup: - all databases all tables of all databases, and there is no need to specify the database name
  • Single database backup: [-- databases] specify all tables in the database (do not give table names later). If -- databases is not written, there will be no statements to create and use the database in the backup file. It is recommended to add
  • Partial table (single table) backup: database name table 1 [table 2, table N]

step

1. Determine the time of backup: it is usually a regular time backup

2. Determine the target level of backup: full database, single database and data table

3. Backup using mysqldump

Example

1. Full database backup (access mysqldump.exe through cmd under Windows, and the current user uses the root account)

mysqldump.exe -uroot -proot --all-databases > D:/mysql.sql

2. Single library backup

# It is recommended to add -- databases, otherwise you should write the database statement yourself
mysqldump -uroot -proot --databases db_2 > D:/db_2.sql

3. Single table backup (no instruction to create database)

# db_2 is the database, t_40 and t_42 is a data table, and multiple data tables are separated by spaces
mysqldump -uroot -proot db_2 t_40 t_42 > D:/t_40_42.sql

Summary

1. SQL backup is a common backup method for databases with low update frequency

2. SQL backup is to backup data tables (Libraries) in the form of SQL instructions

• Structure instruction: table creation (library creation)
• Data instruction: insert data

3. SQL backup can complete the backup structure and data, and the structure and data are independent, so it is more convenient for backup and restore

• SQL backup is time-consuming and performance intensive. It is recommended to backup in idle time (when the user is not active)
• SQL backup can be differentiated according to the importance of the data table

11.5.6 SQL restore

concept

SQL restore: when SQL backup data is needed, find a way to make SQL execute, so as to restore the backup data

• SQL restore can be performed using Mysql.exe

mysql.exe -h -P -u -p [database name] < SQL file path

• mysql.exe can be written as mysql
• In case of database backup and restore, the database name can not be added, and the table backup and restore must be added

• SQL restore can use SQL instructions to restore after entering the database

source SQL File path;

step

1. Determine that the database (table) needs to be restored

• Data disorder
• Incomplete data

2. Find the SQL backup file of the corresponding node

3. SQL restore

Example

1. Using mysql client to query db_2. Perform single database restore for the data files (usually for the database)

mysql.exe -uroot -proot < D:/db_2.sql
# perhaps
mysql -uroot -proot < D:/db_2.sql

• Note: if it is not a library backup, you need to specify the database to perform it

mysql.exe -uroot -proot db_2 < D:/t_40_42.sql

2. After entering the database, use the source command to restore the SQL backup (usually for tables)

source D:/t_40_42.sql;

Summary

1. SQL restore uses SQL backup files to trigger the execution of SQL instructions, so as to restore the structure and data to a specified point in time

2. SQL Restore cannot guarantee that the database data will not be affected

• SQL backup is usually not real-time (usually intermittent)

11.5.7 summary

1. Data backup and restore is a skill that both database managers and users should master

• Ensure data security
• Ensure the normal operation of the system
• Protect the interests of the company and customers

2. There are many ways of database backup and restore, each of which has its own characteristics and application points, and we need to skillfully distinguish and choose

• Table data backup and restore: applicable to data export and import. The data has structure, but does not contain fields and types
• File backup and restore: simple and convenient, but it is necessary to distinguish between the storage engine InnoDB and MyIsam (InnoDB is not suitable for file backup)
• SQL backup and restore: the storage engine is not limited. You can backup anytime, anywhere, but the efficiency of backup and restore is relatively low (full backup)

3. Database backup and restore is a knowledge, so different enterprises and businesses will choose different backup strategies. It is also possible to use cross strategy backup to ensure the security of data, and generally separate the backup files from the operating environment to ensure the real isolation and security of data.

11.6 user management

11.6.1 account management

concept

Account management: set and manage accounts according to the needs of the project

• An account is an object on which permissions depend. Only an account can have permissions
• The account in MySQL consists of two parts: user name @ host address( root@localhost )
  • The user name is the user's login name
  • Host address: it is the client IP that allows access to the client where the account is located (for example, the above root can only be accessed through the client on the server)
• account management
  • Create account: create user username @ host address identified by 'plaintext password';
  • Delete account: drop user username @ host address

step

1. Create users according to project requirements

2. Delete users according to project requirements

Example

1. Create different accounts with different project groups according to the project situation

# Team A is only allowed to access the server in the company, and the company IP is 163.177.151.110
create user `admin`@`163.177.151.110` identified by 'admin123';

# Team B is not limited to database management and working location
create user `admin` identified by 'admin321';

2. After the development task is completed, the task of team A has been completed and no database operation is required

drop user `admin`@`163.177.151.110`;

Summary

1. Account management is the basis of user management, but account management is only a part of user management

• Account management is to consider the division of data security factors
• Account management is rarely used alone, and generally needs to cooperate with permission control
• Account management is also an important means of DBA for database management: divide accounts according to projects
• For large projects or large database servers, the root account is rarely used for development (the permission is too large)

11.6.2 authority management

Objective: to understand the concept of permission and its association with the account, and master the permission assignment and recovery of the account

concept

Permission management: support and recycle permissions for accounts

• At the beginning of account creation, there are no other operation permissions except login

• Account management usually needs to cooperate with the use of permissions

  • Empowerment: bind corresponding permissions to the account grant permission list on database *. Data table * to user name @ host address
  • Reclaim: reclaim the existing permissions of the account. revoke the permission list on database *. Data table * from user name @ host address
  • Refresh permissions: flush privileges
  • View permissions: show grants for user name @ host address

• Permission list provided by MySQL

Privilege	Grant Table Column	Context
ALL [PRIVILEGES]	Synonym for "all privileges"	Server administration
ALTER	Alter_priv	Tables
ALTER ROUTINE	Alter_routine_priv	Stored routines
CREATE	Create_priv	Databases, tables, or indexes
CREATE ROLE	Create_role_priv	Server administration
CREATE ROUTINE	Create_routine_priv	Stored routines
CREATE TABLESPACE	Create_tablespace_priv	Server administration
CREATE TEMPORARY TABLES	Create_tmp_table_priv	Tables
CREATE USER	Create_user_priv	Server administration
CREATE VIEW	Create_view_priv	Views
DELETE	Delete_priv	Tables
DROP	Drop_priv	Databases, tables, or views
DROP ROLE	Drop_role_priv	Server administration
EVENT	Event_priv	Databases
EXECUTE	Execute_priv	Stored routines
FILE	File_priv	File access on server host
GRANT OPTION	Grant_priv	Databases, tables, or stored routines
INDEX	Index_priv	Tables
INSERT	Insert_priv	Tables or columns
LOCK TABLES	Lock_tables_priv	Databases
PROCESS	Process_priv	Server administration
PROXY	See proxies_priv table	Server administration
REFERENCES	References_priv	Databases or tables
RELOAD	Reload_priv	Server administration
REPLICATION CLIENT	Repl_client_priv	Server administration
REPLICATION SLAVE	Repl_slave_priv	Server administration
SELECT	Select_priv	Tables or columns
SHOW DATABASES	Show_db_priv	Server administration
SHOW VIEW	Show_view_priv	Views
SHUTDOWN	Shutdown_priv	Server administration
SUPER	Super_priv	Server administration
TRIGGER	Trigger_priv	Tables
UPDATE	Update_priv	Tables or columns
USAGE	Synonym for "no privileges"	Server administration

step

1. Create a new user account

2. Grant / reclaim the operation permission of the specified database (generally the whole database) or the specified data table according to the demand

3. Refresh permissions

Example

1. To users admin@localhost Assign permissions: db_2. All permissions of all tables below

create user `admin`@`localhost` identified by '123456';
grant all privileges on db_2.* to `admin`@`localhost`;

2. Assign permissions to user admin: DB_ View permission under 2

grant select on db_2.v_student_1 to `admin1`;
grant select on db_2.v_student_2 to `admin1`;
grant select on db_2.v_student_3 to `admin1`;
grant select on db_2.v_student_4 to `admin1`;
grant select on db_2.v_student_5 to `admin1`;
grant select on db_2.v_student_6 to `admin1`;

3. Recycle permissions

# If the user doesn't want it, you can directly delete the user, keep the permission not given by the user, and recycle all permissions
revoke all on db_2.* from `admin`@`localhost`;

# Permission recycling for individual authorization tables: operations can only be performed on tables
revoke select on db_2.v_student_1 from `admin1`;

Summary

1. Permission management is the core of the whole user management: account can only allow users to connect to the server, and permission management can provide users with various operations

2. The permission operation is based on the needs of the user using the account

• DBA users can usually assign permissions to all databases of the entire database: all on **
• Project management level users can have permissions for the project they are responsible for: all on database. * (multiple projects are assigned multiple times)
• Project developer users can set permissions for the project modules they are responsible for: permission list on database. Table name / * (if it is assigned multiple times across projects)
• Common developer permissions are:
  • create, alter, drop: Library and table structure operations
  • insert, select, update, delete: data operation
  • references: foreign key permissions
  • Index: index

3. Extension: you can directly use empowerment to create new users (this is not allowed for MySQL 7 and above)

grant select on db_2.* to `user`@`localhost` with grant option;

11.6.3 role management

Objective: to understand the concept of role management and grasp the convenience brought by role management

concept

Role management: role, that is, assign permissions according to roles, and then users only need to associate roles (assign roles): only after Mysql8

• The existence of roles makes it easier for users to maintain multiple users with the same permissions (core value)
• Role related operations and syntax
  • Create role: create role role name 1[, role name 2,... Role name N] (can be created in batch)
  • Assign permission: grant permission list on database *. Data table * to role name
  • Binding role: grant role name to user name @ host address
  • Revoke role: revoke role name from user name @ host address
  • Reclaim role permissions: revoke permission list on database *. Data table * from role name
  • Delete role: drop role role name 1[, role name 2,... Role name N]

step

Associated role

1. Create role

2. Determine role permissions: assign permissions to roles

3. Assign the role to the user (it may not be related to step 2)

Take off role

1. Permission too large: Reclaim role permissions

2. Discard role: delete role

Example

1. Create user roles and assign them to users with the same permissions

# Create role (role is similar to user name)
create role developer,app_read,app_write;

# Assign permissions to roles
grant all on db_2.* to developer;
grant select on db_2.* to app_read;
grant insert,update,delete on db_2.* to app_write;

# Create users and assign roles to users
create user 'admin1'@'%' identified by '1234';
create user 'admin2'@'%' identified by '1234';
create user 'admin3'@'%' identified by '1234';

grant developer to 'admin1'@'%';
grant app_read to 'admin2'@'%','admin1'@'%'; # Allow batch assignment of roles to users
grant app_write to 'admin3'@'%';

Note: Although the permission is finally associated with the user, the user cannot really use the permission. The permission assignor needs to activate the role every time he logs in to the server: set default role all to user name @ host address (only one role can be activated at a time)

• After activation, the corresponding user needs to log out and log in again

2. Reclaim role permissions or roles

# Reclaim role permissions
revoke insert,delete on db_2.* from app_write;

# Recycle role
revoke app_read from 'admin2'@'%';

# Delete role
drop role developer;

Summary

1. Role management is to use role and permission association to realize role batch association with users

• Facilitate the reuse of permissions
• It is convenient for batch maintenance of users with the same permissions

2. The role creator (only those with permission) needs to activate the role to use the role (the user associated with the role needs to log in again to take effect)