🌟 data storage

1. Manual management stage
2. File management phase (. TXT. Doc. XLS)
3. Database management stage: the data organization structure reduces the redundancy, improves the efficiency of addition, deletion, modification and query, and is easy to expand and convenient for program call and processing

🌟 Basic concepts

• Database: a warehouse that stores and manages data according to a certain data structure. Database is a data set on a certain medium under the management and control of database management system.
• Database management system: software for managing database, which is used to establish and maintain database. Like MySql

🌟 Relational database and non relational database

• Relational: a database that uses a relational model (two-dimensional table) to organize data structures, such as Oracle and SQL_Server, MySQL
• Non relational: databases that do not use relational models to organize data structures, such as MongoDB and Redis. MangoDB is a tree structure similar to Jason

🌟MySQL

Official website address: https://www.mysql.com/

MySQL features

1. Is an open source database, written in C and C + +
2. Able to work on many different platforms
3. It provides API s for C, C + +, Python, Java, Perl, PHP and Ruby
4. Excellent storage structure and fast running speed
5. Comprehensive and rich functions

🌟 MySQL installation

Install MySQL service for Ubuntu

• Terminal execution: sudo apt install MySQL server
• Configuration file: / etc/mysql
• Database storage directory: / var/lib/mysql

Installing MySQL for Windows/MacOS

• Download MySQL installation package https://dev.mysql.com/downloads/windows/installer/8.0.html
• Run the installation file directly

🌟 Startup and connection of MySQL service

Server start

• View MySQL status: sudo service MySQL status
• Start / stop / restart service: sudo service mysql start / stop / restart

Connect database

mysql    -h  Host address   -u  user name    -p  

1. Enter the database password after entering (we set 123456)
2. If you link your own host database, you can omit the - h option h

Close connection

ctrl-D
exit

🌟 MySQL database structure

Data element -- > record -- > data table -- > Database

• Data table: a table in which data is stored
• Column: each column is used to indicate the meaning of the data in this column
• Row: each row represents a complete set of data

🌟 SQL language

SQL structured query language, a special purpose programming language, is a database query and programming language, which is used to access data and query, update and manage relational database systems.

SQL language features

• SQL language is basically independent of the database itself
• There are slight differences in the support and standards of SQL language in different databases
• Each command is in; ending
• SQL command (except database name and table name) keywords and strings can be case insensitive

🌟 Database management

1. View existing databases: show databases;
2. Create database: create database database name [character set utf8];
3. Library name: switch;
4. View the current database: select database();
5. Delete Library: drop database library name;

Note: naming of library name

1. Numbers, letters and underscores, but pure numbers cannot be used
2. Library names are case sensitive. This must be noted because SQL is case insensitive.
3. Do not use special characters and mysql keywords

🌟 Data sheet management

Basic thinking process:

1. Determine storage content
2. Clear field composition
3. Determine field data type

⭐ Basic data type

✨ Number type

• Integer type: INT, SMALLINT, TINYINT, mediamint, BIGINT
• Floating point type: FLOAT, DOUBLE, DECIMAL
• BIT value type: BIT

1. For things requiring high accuracy, such as money, use the decimal type to reduce the storage error. The declaration syntax is DECIMAL(M,D). M is the maximum number of digits and D is the number of digits to the right of the decimal point. For example, DECIMAL(6,2) can store up to 6 digits, with 2 digits after the decimal point. The value range is - 9999.99 to 9999.99.
2. Bit value type refers to 0 and 1 values, which represent two situations, such as true and false

✨ String type

• Common string: CHAR, VARCHAR
• Storing TEXT: TEXT
• Storing binary data: BLOB
• Storage option data: ENUM, SET

1. char: fixed length, that is, after specifying the number of bytes to be stored, no matter how many bytes of data are actually stored, they will eventually occupy the specified byte size. By default, only 1 byte of data can be saved. High access efficiency.
2. Varchar: variable length, low efficiency, but space saving. The actual occupied space depends on the actual size of stored data. The storage size varchar(50) must be specified
3. Enum is used to store one of the given values, that is, single choice, enum('A ',' B ',' C ')
4. Set is used to store one or more of the given values, that is, multiple selection, set('A ',' B ',' C ')

✨ Time type data

• Date: DATE YYYY-MM-DD
• Date and time: DATETIME YYYY-MM-DD HH:MM:SS, TIMESTAMP YYYY-MM-DD HH:MM:SS
• Time: TIME HH:MM:SS
• YEAR: YEAR

1. Datetime function: now() returns the current date and time of the server. The format corresponds to the datetime type
2. Time operation: time type data can be compared and sorted. When writing time string, try to write it in standard format.

e.g.
select * from marathon where birthday>='2000-01-01';
select * from marathon where birthday>="2000-07-01" and performance<="2:30:00";

⭐ Basic operation of table

Create table

create table Table name(
  Field name data type field constraint,
  Field name data type field constraint,
  ...,
  Field name data type field constraint
  );

supplement

• View data table: show tables;
• View table structure: desc table name;
• View data table creation information: show create table name;
• Delete table: drop table name;

🌟 Basic operation of table data

⭐ Insert

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

⭐ Query (select)

select * from Table name [where condition];
select Field 1,Field 2 from Table name [where condition];

e.g. Find boys in the class who score 90 to 100 and are older than 9
select * from class where sex='m' and score between 90 and 100 and age>9 ;

where clause where clause plays an important role in sql statements. It is mainly used to filter data through certain operation conditions. It is used in query, deletion and modification. Arithmetic operator

Comparison operator

Logical operator

Operator priority

⭐ Update table record (update)

update Table name set Field 1=Value 1,Field 2=Value 2,... [where condition];

e.g.
update class set age=18,score=91 where name="Abby";
update class set sex='m' where sex is null;
update class set age=age+1;

Note: if the where condition is not added after the update statement, it means that all records are updated. This operation is rare.

⭐ Delete table record (delete)

delete from Table name where condition;

e.g.
delete from class where score=0 and sex='m';

Note: if the where condition is not added after the delete statement, all records will be cleared

⭐ Table field operation (alter)

Syntax: alter table Table name execution action;

Add field

alter table Table name add Field name data type;
alter table Table name add Field name data type first;
alter table Table name add Field name data type after Field name;

e.g. 
alter table hobby add phone char(10) after price;

Delete field (drop)

alter table Table name drop Field name;

e.g. 
alter table hobby drop level;

Modify data type

alter table Table name modify Field name new data type;

e.g. 
alter table hobby modify phone char(16);

Modify field name (change)

Modify field name (change)

⭐ Advanced query statement

Fuzzy query LIKE is used to perform fuzzy queries in the where clause. The SQL LIKE clause uses the percent sign% to represent any 0 or more characters and underscores_ Represents any character.

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1

e.g. 
select * from class where name like "T%";
select * from class where name like "____";
select * from hobby where hobby like "%draw%";

as usage In sql statements, as is used to rename fields or tables

select name as full name,score as fraction from class;

e.g.
select cls.name,cls.score from class as cls where cls.score>80;

sort ORDER BY clause to set which field and how you want to sort, and then return the search results. Use the ORDER BY clause to sort the query data before returning the data:

SELECT field1, field2,...fieldN from table_name1 where field1
ORDER BY field1 [ASC [DESC]]

By default, ASC indicates ascending order and DESC indicates descending order

select * from class order by score desc;
select * from class where sex='m' order by score;

Compound sort: sort multiple fields, that is, when the first sort item is the same, sort according to the second sort item

select * from class order by age,score desc;

limit The LIMIT clause is used to LIMIT the number of data returned by the SELECT statement or the number of operations of the update and delete statements The basic syntax of a SELECT statement with a LIMIT clause is as follows:

SELECT column1, column2, columnN 
FROM table_name
WHERE field
LIMIT [num] [OFFSET num]

e.g.
update class set score=83 limit 1;
--Boys first
select * from class where sex='m' order by score desc limit 1;
--Second place for boys
select * from class where sex='m' order by score desc limit 1 offset 1;

De duplication statement distinct statement that does not display duplicate values of fields

eg1 : Which countries are in the table
  select distinct country from sanguo;
eg2 : Calculate the total number of countries
  select count(distinct country) from sanguo;

Note: all fields between distinct and from are the same before de duplication

Joint query The UNION operator is used to connect the results of more than two SELECT statements and combine them into a result set. Multiple SELECT Statement deletes duplicate data. UNION operator syntax format:

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

The default UNION DISTINCT means to delete duplicate data in the result set. If ALL is used, ALL result sets are returned, including duplicate data.

--Different fields can be queried, but the number of fields must be the same
select name,age,score from class where score>80
union
select name,hobby,price from hobby;

--order by Can only be added at the end to indicate yes union Sort results together
select * from class where sex='m'
union all
select * from class where score>80
order by score;

Subquery Definition: when a statement contains another select query statement, it is called a statement with sub query Subquery usage location

1. After from, the content of the sub query is treated as a new table content, and then the outer select query is performed

select * from (select * from class where sex='m') as man 
where score > 80;

1. In the where clause, the content found in the select query is used as the condition value of the outer query

 --Query and tom Students of the same age
select * from class
where age=(select age from class where name='Tom');
  
**be careful**
1.  You need to rename the sub query result set for convenience where Reference operation in Clause
2. When the clause result is used as a value, the returned result needs an explicit value, which cannot be multiple rows or columns.
3. If the clause result is used as a collection, i.e where Yes in Clause in Operation, the result can be multiple records of one field.

Query process Through the previous study, we can see that a complete select statement is rich in content. Let's take a look at the execution process of select:

(5) SELECT DISTINCT <select_list>                     
(1) FROM <left_table> <join_type> JOIN <right_table> ON <on_predicate>
(2) WHERE <where_predicate>
(3) GROUP BY <group_by_specification>
(4) HAVING <having_predicate>
(6) ORDER BY <order_by_list>
(7) LIMIT <limit_number>

⭐ Aggregation operation

Aggregation operation refers to the further sorting and filtering of data based on data search. In fact, aggregation operation also belongs to the scope of data query and filtering.

✨ Aggregate group: group by

Group the results of the query

e.g.: calculate the average attack power of each country

select country,avg(attack) from sanguo group by country;

Note: when using grouping, the fields after select are the fields and fields of group by grouping Aggregate function , cannot contain anything else. Group by can also be grouped according to multiple fields at the same time. For example, group by A and B must have the same values of a and B before they can be counted as a group.

✨ Aggregate function

Note: only aggregate functions can be written after the select of aggregate group, and other fields cannot be found unless the values of the fields are all the same

✨ Aggregate filtering: having

Further screen the results after grouping aggregation

e.g.: count the number of heroes in countries with average attack power greater than 250

select country,count(*) from sanguo
group by country
having avg(attack)>250;

be careful

1. The having statement must be used in conjunction with group by.
2. The existence of having statement makes up for the deficiency that the where keyword cannot be used in combination with the aggregate function. Where can only operate the actual fields in the table.

🌟 Index operation

⭐ Overview

An index is a structure that sorts the values of one or more columns in a database table. Using an index can quickly access specific information in a database table. It can speed up data retrieval and improve search efficiency. However, it will occupy the physical storage space of the database. When updating the data in the table, the index needs dynamic maintenance to reduce the efficiency of data writing.

be careful

1. Usually, we only create indexes on fields that are frequently queried
2. It is not suitable to create an index for a table with a small amount of data or a table that often performs write operations rather than query operations

⭐ Index classification

• General index (MUL): the field value is unconstrained, and the KEY flag is MUL
• Unique index (UNI): field values are not allowed to be repeated, but can be null, and the key flag is uni
• Primary KEY index (PRI): there can only be one primary KEY field in a table. The primary KEY field cannot be duplicate and cannot be NULL. The KEY flag is pri. The record number field id is usually set to uniquely lock a record

⭐ Index creation

Create indexes when creating tables

create table Table name(
Field name, data type,
primary key(Field name),
index Index name(Field name),
unique Index name(Field name)
);
 

Note: you can also write the primary key when declaring the data type

Create indexes for fields in existing tables

create [unique] index Index name on Table name(Field name);

Add primary key index

alter table Table name add
primary key(id);

⭐ View index

desc Table name;  --> KEY The sign is: MUL ,UNI. 
show index from Table name;

⭐ Delete index

drop index Index name on Table name;

alter table Table name
drop primary key;

Note: you can remember in this way: the primary key has no name, so there is no way to use the first method

🌟 Foreign key constraints and table associations

⭐ Foreign key constraint: foreign key

Establish a constraint relationship between tables. Due to the existence of this relationship, the data between tables can be more complete and more related. In order to specify, the following department tables and personnel tables are created.

Note: constraint is a kind of restriction. It restricts the data of rows or columns of the table to ensure the integrity and relevance of the data of the table

Foreign key constraints are divided into master table and slave table: if the foreign key of table B corresponds to the primary key of table A in the same database, table A is the master table and table B is the slave table.

✨ Create foreign key

[CONSTRAINT Foreign key name] 
FOREIGN KEY Foreign key from table
REFERENCES Main table name(Primary table primary key) -- The data type of the foreign key field of the slave table should be the same as the primary key of the specified primary table.
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

This syntax can be used when creating table and ALTER TABLE

e.g.
-- Create foreign keys directly when creating tables
CREATE TABLE person (
id int PRIMARY KEY AUTO_INCREMENT,
name varchar(32) NOT NULL,
age tinyint unsigned,
salary decimal(10,2),
dept_id int ,
constraint dept_fk 
foreign key(dept_id) 
references dept(id)
);

-- Add foreign key after creating table
alter table person add
constraint dept_fk
foreign key(dept_id)
references dept(id);

Note: not all table relationships need to be constrained by foreign keys. If there is no constraint relationship similar to the above, it can not be established.

✨ Release foreign key constraint

Release foreign key constraint by foreign key name

alter table Table name
drop foreign key External chain name;

drop index Index name on Table name

Note: after deleting the foreign key, it is found that the MUL index flag of desc is still there. In fact, the foreign key is also an index. You need to delete the index of the foreign key name.

✨ Cascade action: on delete / on update

• Restrict (default): when deleting or modifying records in the primary table, if there are associated records in the secondary table, the primary table cannot be changed
• Cascade: when the master table deletes a record or changes the value of the referenced field, the slave table will cascade updates
• set null: when the primary table record changes, the value of the foreign key field changes to null
• no action: no cascading operation

⭐ Table Association

When we deal with complex data relationships, the design of data table is particularly important. Understanding the dependency between data is the premise of creating data table relevance more reasonably. One to many and many to many are common table data relationships:

One to many relationship One record in one table can correspond to multiple records in another table; But conversely, a record from another table It can only correspond to one record in the first table. This relationship is one to many or many to one For example: a person can own multiple cars, and each car has only one registered owner.

Many to many relationship One record in A pair of tables (A) can correspond to multiple records in another table (b); At the same time, A record in table B It can also correspond to multiple records in table A For example: an athlete can apply for multiple events, and multiple athletes will participate in each event. At this time, in order to express the many to many relationship, you need to create a separate relationship table.

✨ E-R model diagram

E-R model (entry relationship) is the entity relationship data model, which is used in database design. It uses a simple diagram (E-R diagram) to reflect the things or data existing in the real world and the relationship between them

E-R model diagram includes: entity, attribute and relationship

entity

1. Describe the concept of objective things
2. Representation method: rectangular box
3. Example: a person, a book, a cup of coffee, a student

attribute

1. A property of an entity
2. Representation: Oval
3. Examples Student attributes: student number, name, age, gender, major Feeling attributes: sadness, joy, stimulation, anger

relationship

1. Linkages between entities
2. One to many Association (1:n)
3. Many to many Association (m:n)

✨ Drawing of E-R diagram

Rectangular boxes represent entities, diamond boxes represent relationships, and ellipses represent attributes

⭐ Table Association query

If multiple tables have a certain association relationship, you can query multiple tables together. In fact, there is no inevitable relationship between the association arrangement of tables and foreign key constraints, but the related tables designed based on foreign key constraints tend to use more association queries to find data.

Simple multi table query Multiple table data can be queried jointly. The syntax format is as follows:

select  Field 1,Field 2... 
from Table 1,Table 2... [where condition]

e.g.
select name,salary,dname
from person,dept 
where person.dept_id = dept.id and salary>=20000;

Cartesian product phenomenon is to forcibly put together each record of table A and table B. Therefore, if table A has n records and table B has m records, the result of Cartesian product will produce n*m records.

Inner connection Internal connection query will only find qualified records. In fact, the result is the same as table Association query. The official recommends using internal connection query.

select Field list
from Table 1 inner join  Table 2
on Table 1.field = Table 2.field;

Left connection The left table displays all the items in the right table that match the left table

select Field list
from Table 1 left join  Table 2
on Table 1.field = Table 2.field;

Right connection The right table displays all the items that match the right table in the left table

select Field list
from Table 1 left join  Table 2
on Table 1.field = Table 2.field;

Note: according to the official statement, we use the table with large amount of data as the benchmark, which is placed in front of left or right.

🌟 supplement

Performance view

set  profiling = 1； Open function (generally not open when the project is online)
show profiles  View statement execution information