Mysql introduction to the 10,000 word summary! (Quite complete)

I. Database Overview

1 What is a database

In the narrow sense, Database refers to the collection of a large amount of organized, shareable data stored in a computer for a long time, which exists in the form of files on the hard disk.
The database in the broad sense should be called database system, it includes the database management system (DBMS) and database administrator (DBA) besides the narrow sense database. Together they constitute a system for storing, managing, processing and maintaining data. In future studies, databases usually refer to database systems.

2 Basic concepts of databases

DBMS: (DataBase Management System) refers to a database management system, also known as database software or database products, used to create and manage databases, such as MySQL, Oracle, and SQL Server
DB: (Database) refers to a database, a warehouse where data is stored.
DBA: (DataBase Administrator) refers to a database administrator who operates, designs and runs a database.
DBS: (DataBase System) refers to the database system, which mainly includes DBMS, DBA, DB.
SQL: (Structured Query Language) is a structured query language used to communicate with databases, access data, query, update, and manage relational database systems.
Database management system, the relationship between databases and tables

3 Database classification

Relational database:

MySQL, Oracle, DB2, SQL Server

Non-relational database:

Key and value storage databases: Redis, Memcached, and MemcacheDB Column storage databases: HBase, Cassandra Document-oriented database: MongDB, CouchDB Graphics database: Neo4J

The difference between the two

Relational database: a database that uses a relational model to organize data. A relational model is a two-dimensional table model that is easy to use and maintain, but has poor performance.

Non – relational database: the composition structure is not fixed, the performance is better

Detailed look at this article briefly describes the relational database and relational database: www.jianshu.com/p/fd7b422d5…

4 Classification of SQL language

A quick look at the SQL language

SQL has the functions of data definition, data manipulation and data control.

Simply said is a universal database operation language, can be used in different databases (SQL language in different databases have differences)

DDL :(Data Definition Language) Data Definition Language. Build a library, build a table
DML: Data Manipulation Language (DML). Add, delete and modify the data in the table
DQL: Data Query Language (DQL). Query the table
TCL (Transactional Control Language) Transactional Control Language.
DCL :(Data Control Language) Data Control Language. Setting of user permissions

Two mysql overview

1 Basic understanding of mysql

MySQL is a relational database management system developed by MySQL AB and currently owned by Oracle. MySQL is a relational database management system. Relational databases keep data in different tables instead of putting all data in one large warehouse, which increases speed and flexibility.

2 Advantages of mysql

Mysql is open source
Mysql supports large databases. Can handle large databases with tens of millions of records.
Mysql uses the standard SQL data language format.
Mysql runs on multiple systems and supports multiple languages.
Mysql is customizable. Under the GPL, you can modify the source code to develop your own mysql system.

3 Installing and uninstalling mysql

Blog.csdn.net/weixin_4422…

4 Basic operations of mysql

4.1. Start and stop mysql

Mysql > start mysqlnet start mysql(Run in command line administrator mode.)
Mysql > alter databasenet stop mysql(Run in command line administrator mode.)
Or manually open it yourself

4.2. Mysql Login and logout

Mysql > create mysqlMysql -u User name -p password(Run in common mode)
Mysql -hip(IP address of the connection target) -uroot -p Password of the connection target[log in to other people’s mysql, of course, their own also line, but this is more troublesome]
Mysql --host= IP --user=root --password= connect target passwordMysql > connect to mysql
Mysql > alter databaseexitorctrl +cor\qorquit(Normal mode can be run)

4.3. Check the mysql version

Check the version of mysql when you are not logged in: Enter the command linemysql -V
Log in to mysql and check the version

After login, the mysql version is displayed, but you can also view the select version() command; Or the status;

5. Mysql directory structure analysis

After installing mysql, a mysql installation directory will be generated on disk (as shown below). The following is a brief analysis of mysql directory.

Bin: used to store executable files, such as mysql.exe, mysqlld. Exe,mysqlshow.exe, etc

Docs: Stores some documents

Data: Stores some database and log files

Include: includes header files, such as mysql_ername.h and mysql_ername.h

Lib: Used to place a list of library files

Share: Used to store character set and language information

My.ini: This is the default configuration file used by mysql. Generally, you can configure mysql by modifying the contents of this file

The rest of the.ini configuration files are templates for different database configuration files.

A detailed analysis of my.ini

After opening my.ini with notepad++, there are many comments explaining the configuration in detail, so the following is a brief description of a few configuration items, and other configuration items can be read directly in the file

In addition to the installation directory, mysql also has a data directory, as shown below

This directory is mainly used to store databases and tables in mysql, such as HEima, which I created. Note that this directory is a hidden directory and must be set under Windows to show it.

6. The grammar of the mysql

Each statement ends with a semicolon. If you don’t have to do SQLyog.
SQL statements are case insensitive, and both uppercase and lowercase keywords are considered the same
Three kinds of comments

DDL: Operates databases and tables

1. Operating database (CRUD)

1.1 Create:

Create database Specifies the database name.
Create database if not exists Database name;Create a database, do not create it if it already exists, create it if it does not exist.
Create database name character set name (utf8/ GBK)Create database and specify character set
[Example: create a database db1, check whether it exists and specify character set GBK]create database if not exists db4 character set gbk;

1.2. Retrieve:

show databases;View the names of all databases
Show create database Database name;Query database creation statement and character set;

1.3. Update:

Alter DATABASE Database name character set Name of a character set: Modifies the database character set

1.4 delete (delete)

Drop database Specifies the database name.Deleting a Database
Drop Database If EXISTS Indicates the database name: Check whether the database exists and then delete it.

1.5. Use a database

Use Database name; Use a database.
select database(); : Queries the current database

2. Operation Table (CRUD)

2.1 create:

Grammar:

create tableTable name (column name1The data type1And the column name2The data type2. Column name n Data type n);Copy the code

Note: The last column is not comma (,)

The data type

Create a table:

create table student(
	id int,
	name varchar(32),
	age int,
	score double(4.1),
	birthday date,
	insert_time timestampTimestamp. If null, the value is automatically assigned
	);
Copy the code

2.2. The query

show tables;: Queries all table names in a database
Desc table name;: Queries the table structure of a table
Show create table name;: Queries the SQL statement used to create a table

2.3. Modifications

Alter table name rename to new table name;: Changes the table name
Alter table table_name character set character set name;: Modifies the character set of the table
Alter table name add column name data type;Add a column
Alter table table name modify column name New data type;: Changes the column type.
Alter table table_name change column name new column name new data type;Modify the column name
Alter table table_name drop table_name;: delete columns

2.4. Delete

Drop table table name: delete table
Drop TABLE if EXISTS Indicates the name of a table: Deletes the table. If the table exists, delete it

2.5. Duplicate the table structure

Create table name like the name of the table to be copied;: Replicates the structure of the table

DML: Add, delete and modify the data in the table

1. Add data:

Grammar:

Insert into table name (1, 2.... N) values(1, 2... The value of n);

Note:

In the above statement, the field name can be written in full or in part, but the column name and value must correspond one by one. Null is used for fields with no data added.
If no column name is defined after the table name, values are added to all columns by default, for exampleInsert into values(1, 2... The value of n);
Except for numeric types, all types need to be quoted (odd or even)

Insert multiple pieces of data at once:Insert into table name (1, 2...) Values (1, 2...) ,(value 1, value 2...) ,(value 1, value 2...)

2. Delete data:

Grammar:

Delete from table name [where condition]

Note:

If no condition is added, all records in the table are deleted
If you want to delete all records

Delete from table name – Not recommended, delete as many records as possible. TRUNCATE TABLE name; Delete the table first and then create the same table.

3. Modify data:

Grammar:

Update table_name set table_name 1= 1, table_name 2= 2..... [where condition];

Note:

If no conditions are added, all records in the table are modified.

Five DQL: queries records in a table

1. Basic query

Query all columns:Select * from table_name;
Query a specified column:Select 1, 2, 3... From the name of the table;
Names:Select 1 AS alias, 2 AS alias... FROM the name of the table;
Clear duplicate values:Select distinct from table name;
Query result participation operation:Select * from table_name where table_name = 1 and table_name = 2;

Note that participants must be numeric

2. Query (where)

Basic syntax:Select * from table_name where table_name = 1;
The operator

Logical operator

4. In keyword5. Range query

Like the keyword

Select * from student where name = ma

Select * from student where name like '%';

Select * from student where the second word is class

Select * from student where name like '_ %';

Select * from student where name is 3 characters

select * from student where name like '___';

3. Order by

Basic syntax:Select * from table_name order by table_name order by table_name order by table_name order by table_name order by table_name ;
Sorting methods:ascAscending order, default.desc: descending order.
Note: when there are multiple sorting conditions, the second condition will be judged only if the condition value of the current edge is the same.

Query all data, in descending order by age, if the same age and then in ascending order by math score

select * from student order by age desc,math asc;

4. Aggregate queries (functions)

Basic syntax:Select aggregate function from table name;
Common aggregate functions:

Aggregation function	role
Max (column name)	Find the maximum value of this column
Min (column name)	Let’s minimize this column
Avg (column name)	Take the average of this column
Count (column name)	Count how many records there are in this column
The sum (column name)	Take the sum of this column
3. Note that the aggregate function’s query is a vertical query that evaluates a list of values and returns a result value, but it automatically ignores null; You can use this if you want to count rows that contain null values`Ifnull (column name, 0)`To replace null with a value of 0 (the default value can be set to anything but 0).
4. The ifnull function is of the form ifnull(a,b). If a is not null, the value of A is returned; otherwise, the value of b is returned.

Query total number of people older than 20 (excluding null)

select count(*) from student where age>20;

Query the total number of persons (including null) older than 20 years old, if null, default 21 years old, and count.

select count(*) from student where ifnull(age,21)>20;

5. group query (gruop by[having])

Basic syntax:Select * from 'table_name' group by 'table_name'
The meaning of grouping: The data in the table is divided into different groups by grouping fields before query. Usually used with aggregate functions.
Note: when we use a field group, we also need to query this field when querying, otherwise we can’t see which group the data belongs to

The average math scores of boys and girls were queried separately

select sex,avg(math) from student group by sex;

Query boys’ and girls’ math scores

select sex ,max(math)form student group by sex;

Select * from student where score > 80; select * from student where score > 80

select sex,count(*) from student where score>80 group by sex; More complex, step by step decomposition: first check the score of more than 80 points, and then grouped by gender, finally count the number of people.

Select * from student where score > 80; select * from student where score > 5;

select sex,count(*) from student where score>80 group by sex having count(*)>5; Note that the following statement is incorrectly written: Select * from student where score>80 group by sex where count(*)>5; select * from student where score>80 group by sex where count(*)>5;

Having vs. where

Note that having cannot be followed by a simple property query, as in the example aboveselect sex,count(*) from student group by sex having age>39In thehaving age >39Logically, it is wrong, because having reprocesses the results after Group BY, and Group BY has already grouped the data, and each group has corresponding attributes. Having a common attribute will cause a query error of 1 pair of N, and the system cannot find the specific attributes in the group, thus making an error.

6. Paging query (limit)

Basic syntax:

selectField list [asThe alias]FROMThe name of the table~~[WHEREClause]~~  
~~[GROUP BYClause]~~
~~[HAVINGClause]~~ 
~~[ORDER BYClause]~~[] LIMIT clause;//Limit clause: LimitoffsetLength,//offsetStart index, default if not written0)
//Length (number of queries per page)Copy the code

Query formula:Start index = (current page number -1) * number of lines displayed per page

Suppose there are 19 entries, 5 entries per page

Select * from student limit 0,5; Select * from student limit 5,5; Select * from student where student limit 1 = 1 and student limit 1 = 1; Select * from student limit 15,5; // query page 4, if there are less than 5 items, then display as many as there are

Select * from table_name where table_name = 1;

Why multiple tables are used: When we want to query multiple fields in the same table, we should join the multiple tables involved, treat it as a single table, and then query.
Note: multi-table query must have table join conditions, otherwise cartesian product phenomenon will occur.

select * from table1,table 2;: This statement produces a Cartesian product because no join conditions are specified and a lot of unusable data appears. 3.classification 4. In the connection

Implicit inner join

The JOIN keyword cannot be seen

Grammar:

Select * from (select * from (select * from (select * from (select * from (select * from)))

For example:

Select a.name, b.core from a,b where a.id=b.id; select a.name, b.core from a,b where A.id = B.id;

Display internal connection

Use inner join…. On statement, inner can be omitted

Grammar:

Select * from 'inner' join 'on';

For example,

Select a.name, b.core from a join b on A.id =b.id; select a.name from a join b on A.id =b.id;

Outer join

Left outer connection:

Left outer join… On,outer can be omitted

Grammar:

Select * from dept. left [outer] join dept. on;

Instructions;

The left outer join can be understood as ensuring the data display of the left table on the basis of the inner join

For example:

Select * from ‘a’ where id = ‘b’select * from a left join b on a.id=b.id;

Note: left-outer join is used to match the records of the right table with the records of the left table according to the condition after ON. If the matches of the left table and the right table are displayed, otherwise, only the left table is displayed, and the right table is null. That is, on the basis of the inner join, ensure that all the record data in the left table are displayed.

Right outer connection:

Right outer join… On,outer can be omitted

Grammar:

Select * from 'right' where 'right' [outer] join 'on';

Note: Right outer join can be understood as the inner join on the basis of the right table to ensure that all the data display.

For example:

Select * from ‘a’ where id = ‘b’select * from a right join b on a.id=b.id;

8. Subquery (nested query)

What is a subquery: a subquery is a query nested within a query, the results of one query as a condition of another query.
Subquery classification: subquery is divided into three categories according to the results of the subquery are single-row, single-column, and multi-row, multi-column.
A query that results in a single row, single column:

Grammar:

Select * from table where select * from table where select * from table where select * from table where select * from table where select * from table where select * from table where select *

For example:

Person table stores employee ID,name,date (start date) information, salary table stores employee ID, money (salary), Bonus (bonus), etc. Select * from person where id=(select id from salary where salary=(select Max (money) from salary));

Results in a multi-row, single-column query:

Grammar:

Select * from table where select * from table where select * from table where select *;

For example:

Select * from person where id in (select id from salary where money>5000);

Results in a multi-row, multi-column query:

Grammar:

From the select query field (the subquery) table alias where conditions;

For example:

Select * from person,(select money,bonus from salary) table1 where name=’zhangsan’ and table1.id=person.id ; Or use a multi-table join query: select person.id,person.name,person.date ,salary.money,salary.bonus from salary,person where person.id=salary.id and person.name=’zhangsan’;

9. Summary

The execution order of the query

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

DCL: Manage users and authorize users

We are now using the default root user, super administrator, with full permissions. However, a database server in a company

It is possible to have databases running for many projects at the same time. Therefore, we should be able to set up different users according to different projects, and assign different permissions to manage and maintain the database

1. Manage users

Let’s start with a few ‘keywords’

Mysql > select * from user where username = ‘user’;USE mysql; `

SELECT * FROM USER; To view the `

1.1 Adding a User

Grammar:CREATE USER 'username '@' username' IDENTIFIED BY 'password ';

1.2 Deleting a User

Grammar:DROP USER 'username '@' username ';

1.3 Changing a User Password

UPDATE USER SET PASSWORD = PASSWORD(' new PASSWORD ') WHERE USER = 'username ';
SET PASSWORD FOR 'username '@' username' = PASSWORD(' new PASSWORD ');

1.4 Changing the Password of user root

Mysqladmin -uroot -p password New password
This statement needs to operate without logging in to mysql. The new password is not quoted.

1.5 mysql Forgets the password of user root

cmd –>net stop mysqlStop mysql server (administrator permission)

Start mysql service without authentication:mysqld --skip-grant-tables

Open a new CMD window and enter the command directlymysql, and press Enter to log in successfully

Change the new password:use mysql; 和 Update user set password = password(' your new password ') where user = 'root';

Close both Windows

Open task Manager and end mysqlD. exe manually

Restart mysql and log in with the new password

2. Rights management

2.1 Querying Permission

SHOW GRANTS on 'userid '@' host name ';

2.2 Granting Permission

Grant permission 1, permission 2.... On Database name. Alter table name to 'username '@' hostname ';

2.3 Revoking Permission

Revoke permissions 1, 2... On Database name. Select * from 'username '@' hostname ';

Seven constraints

1. The concept

Qualify the data in the table to ensure the correctness, validity and integrity of the data. If a constraint is added to a table, incorrect data cannot be inserted into the table. Constraints are appropriate to add at table creation time.
Types of constraints:

2. Non-null constraint –not NULL

What is a non-null constraint: A column cannot be null.
Add constraints when creating tables

CERATE TABLE stu(
	id INT,
	NAME VARCHAR(20) NOT NULL- the name is not empty
	);
Copy the code

After the table is created, add non-null constraints

ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
Copy the code

Delete a non-null constraint for name

ALTER TABLE stu MODIFY NAME VARCHAR(20);
Copy the code

The default value

Field Name Field typedefaultThe default valueCopy the code

He is the only one who is unique

What is a unique constraint: A column in a table cannot have duplicate values.
Note: Unique constraints can have null values, but only one record can be null
When you create a table, add a unique constraint

CREATE TABLE stu(
	id INT,
	phone_number VARCHAR(20) UNIQUE-- Unique phone number
	);
Copy the code

Delete unique constraints

ALTER TABLE stu DROP INDEX phone_number;
Copy the code

After the table is created, add unique constraints

ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;
Copy the code

4. Primary key constraint –primary key

Note: A primary key is the unique identification of records in a table. A table has only one field primary key, which is non-empty and unique.
When you create a table, add a primary key constraintprimary key

create table stu(
	id int primary key,Add primary key constraint to id
	name varchar(20));Copy the code

Remove the primary key

alter table stu drop primary ket;
Copy the code

Add the primary key after the table is created

alter table stu modify id int primary key;
Copy the code

Set automatic primary key growth

Concept: If a column is numeric, use Anto_increment to automatically increment the primary key

When you create a table, add a primary key constraint and auto-grow the primary key.
  	create table stu(
  			id int primary key auto_increment,Add primary key constraint to id and increment automatically
  			name varchar(20));Copy the code

Delete automatic growth

  	ALTER TABLE stu MODIFY id INT;
Copy the code

Add automatic growth

  	ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
Copy the code

Effects of DELETE and TRUNCATE on self-growth:

Delete: After all records are deleted, self-growth has no impact.

TRUNCATE: After deletion, the self-growth starts again.

5. Foreign key constraint — Foreign key

Make tables relate to tables to ensure data correctness
When creating a table, add a foreign key

create tableThe name of the table (... A foreign key columnconstraintName of the foreign keyforeignKey (foreign key name)referencesPrimary table name (primary table column name);Copy the code

Remove the foreign key

alter tableThe name of the tabledrop foreignKey Foreign key name.Copy the code

After the table is created, add the foreign key

alter tableThe name of the tableadd constraintName of the foreign keyforeignKey (foreign key field name)referencesPrimary table name (primary table column name);Copy the code

Cascade operation

What is a cascading operation? When the primary key of a primary table is modified or deleted, the foreign key values of secondary tables are updated or deleted.Copy the code

TCL: Transaction control

1. What are transactions

In actual development, we do some business operations, such as transfer (a user buckles money, another user to add money) often need to access the database for many times, by performing multiple SQL statements to complete, but if these SQL statements when executed due to system malfunction caused one of the SQL statement execution fails, So while the other SQL execution succeeded, for this transfer operation, this operation failed.
So we introduce the concept of “transaction”, the main concept is to an operation to be performed in all SQL statements as a whole, they must perform all the success, if you have failed to perform a SQL, so all the SQL to re-execute (rollback), to ensure that incorrect submission, only all SQL execution is successful, Commit to the database

2. Why use transactions

In the actual development of mysql, it is more logical to use transactions to manage executed SQL statements and avoid losses caused by incorrect operations.

3. How are transactions used

There are two ways to perform transactions in mysql. One is to commit transactions manually, the other is to commit transactions automatically.

Commit transactions manually:

The SQL statement to use

start transaction;: Start transactioncommitCommit transactionrollback;2. Use the flow:3. Case demonstration

Create a data table and insert data to demonstrate the transfer
create table account(Create table
   id int primary key auto_increment,
	name varchar(10),
	balance double
);
Add data
insert into account (name,balance) values('zhangsan'.1000), ('lisi'.1000);
Copy the code
Transfer the demonstration

And just to be clear, instart transaction;If there is no problem with the SQL statement, commit the SQL statement. Then the data will be saved to the database. If there is a problem, rollback the data in the database will be restoredstart transaction;When the state. At the same time, when there is no COMMIT, we query the data table and find that the data in the data table has changed. This is only the data in the temporary log file, which can only represent the state at this time, and there is no change in the database. Mysql > alter table; mysql > alter table; mysql > alter table;

Automatic commit transaction:

In mysql, the default DML statement is a single transaction, and each statement automatically starts a transaction. After the statement is executed, the transaction is automatically committed, and mysql starts the automatic transaction submission by default. This can be seen in the previous study of DML.

throughselect @@autocommit;Command to check whether mysql has enabled automatic commit transactions. 1 indicates enabled, 0 indicates disabled.

Through 3.set @@autocommit =0To cancel the automatic commit transaction, we need to commit the DML statement automatically to take effect, otherwise it will not be saved to the database.

4. Principle of transactions

After a transaction is started, all operations are temporarily saved to the transaction log. The transaction log will synchronize the operations to the database only when it receives the COMMIT command. Otherwise, the transaction log will be cleared (for example, ROLLBACK or disconnecting from the database).
Transaction execution steps:

The client connects to the database server and automatically creates a temporary log file for the user upon successful connection

After a transaction is started, all operations are written to a temporary log file first

All queries are queried from the table, but are processed by the log file. Therefore, some operations before submission can be read from the log file.

After the transaction commits, the data in the log file is written to the database and then the log file is emptied. Rolling back or disconnecting directly clears the log file.

Schematic diagram

5. Rollback point (optimization of transactions)

In the above transaction, if the SQL execution fails, we have to execute it again, which is fine if there are fewer SQL statements, but it is inefficient to execute all the SQL statements because one row failed, so we introduced the “rollback point”.
A rollback point is a marker set after a successful operation is performed to prevent subsequent operations from being executed. It is set up after a successful operation and can be returned to this point for re-execution if subsequent operations fail.
SQL statement for rollback point

Note that setting the rollback point only goes back to the rollback point, not to the time when the transaction started.

6. Four Characteristics of Transactions (ACID)

Atomicity: Each transaction is an indivisible whole, and all SQL statements within the transaction either execute successfully or fail.
Consistency: The total amount of data remains the same before and after a transaction
Isolation: Multiple transactions are independent of each other and operate without affecting each other.
Durability: Save data persisted in the database after a transaction is committed.

7. Transaction isolation level

The ideal state of transaction operation is that all transactions remain isolated from each other and can run independently of each other. However, if multiple transactions operate on the same data at the same time, this can cause problems that can be resolved by setting different isolation levels.
Let’s start with the problems:

Dirty read: a transaction reads data that another transaction has not committed

Non-repeatable read (virtual read) : Different data is read twice in the same transaction.

Phantom read: All records in a DML table are added by another transaction, and the first transaction cannot query its changes

Mysql has four isolation levels

View and set transaction isolation level for mysql

select @@tx_isolation;: View the global transaction isolation level

Set Global Transaction Isolation Level Level The value is a character string: Sets the isolation level.

Note: To set the transaction isolation level, you need to exit mysql and log in again to see the isolation level change.

Level string:read uncommitted ,read committed ,repeatable read ,serializable

Log in to mysql again to see the changes

9 restoring and backing up mysql

9.1 Cli Mode

Mysql backup

Run the command in DOS (win+ CMD)Mysqldump -u username -p password Database > SQL file path to be backed up;

Note: If you do not specify the SQL path, an SQL file will be created in the current directory and the backup SQL will be written.

For example:

This command stores the mysql. SQL file in the current C:\Users\coderchen directoryThis command stores the mysql. SQL file in the root directory of drive D

Mysql reduction

Log on to the mysqlmysql -uroot -proot

Create a databaseCreate database Specifies the database name.

Using this database:Use database name;

Restore data using the source command:Source + Backup SQL fileI can just drag it in

If the table is locked and cannot be opened:UNLOCK TABLES;

The import is successful and view the result

9.2 Graphical operations of SQLyog

The backup

restore

Deleting a Database

Database list area right click to execute SQL script, specify the SQL file to execute, execute.

X. Database design

10.1 Relationships between tables

A couple more:

1:n

Table construction principle: Create a field in the slave table (multi-party). The field points to the primary table as a foreign key

Many-to-many:

m:n

Many-to-many table construction principle: Create a third table with at least two fields in the middle table. The two fields are used as foreign keys to point to the primary key of each party.

One to one:

1:1 is not used much in real development because one-to-one can create a table

Two types of table building principles

Foreign key unique: the primary key of the primary table and the foreign key (unique) of the secondary table, forming a primary foreign key relationship

A foreign key is a primary key: the primary key of a primary table and the primary key of a secondary table form a primary foreign key relationship.

10.2 Three paradigms of database

What is the paradigm of database: good database design will have an important impact on data storage performance and later program development. To build a scientific, standardized database, you need to meet some rules to optimize the design and storage of data. These rules are called paradigms.
Three paradigms: There are currently six paradigms for relational databases, the first (1NF), second (2NF), third (3NF), Bus-Coad (BCNF), fourth (4NF) and fifth (5NF, also known as perfect paradigms).
The one that meets the minimum requirements is the first normal form (1NF). On the basis of the first normal form, the one that further meets more specification requirements is called the second normal form (2NF), and the others are so on. In general, a database only needs to satisfy the third normal form.

First normal form: each column of a database table is an indivisible atomic data item, not a set, array and other non-atomic data item. That is, if a column in a table has multiple values, it must be split into different columns. In short, the first normal form, in which each column is irresolvable, is called atomicity.

Second normal form: Under the first normal form, every field in the table is completely dependent on the primary key. Complete dependence means that no column can exist that depends on only part of the primary key. In short, the second normal form is based on the first normal form where all columns are completely dependent on the primary key column. A second normal form violation occurs when there is a compound primary key containing multiple primary key columns. For example, if you have a primary key that has two columns, you can’t have a property that only depends on one of those columns, that’s not second normal form compliant.

Features: A list only describes one thing

Each column in the table is completely dependent on the primary key

Third normal Form:

In the second normal form, each column in the table depends directly on the primary key, rather than indirectly on the primary key through other columns. In short, the third normal form is that all columns are independent of other non-primary key columns, that is, no non-primary column can pass dependent on a primary key unless 2NF is satisfied. The so-called transitive dependence means that C transitive depends on A if there is A “A → B → C” determining relationship. Therefore, database tables that satisfy the third norm should not have the following dependencies: primary key column → non-primary key column X → non-primary key column Y

Mysql introduction to the 10,000 word summary! (Quite complete)

I. Database Overview

1 What is a database

2 Basic concepts of databases

3 Database classification

4 Classification of SQL language

Two mysql overview

1 Basic understanding of mysql

2 Advantages of mysql

3 Installing and uninstalling mysql

4 Basic operations of mysql

4.1. Start and stop mysql

4.2. Mysql Login and logout

4.3. Check the mysql version

5. Mysql directory structure analysis

6. The grammar of the mysql

DDL: Operates databases and tables

1. Operating database (CRUD)

1.1 Create:

1.2. Retrieve:

1.3. Update:

1.4 delete (delete)

1.5. Use a database

2. Operation Table (CRUD)

2.1 create:

2.2. The query

2.3. Modifications

2.4. Delete

2.5. Duplicate the table structure

DML: Add, delete and modify the data in the table

1. Add data:

2. Delete data:

3. Modify data:

Five DQL: queries records in a table

1. Basic query

2. Query (where)

3. Order by

4. Aggregate queries (functions)

5. group query (gruop by[having])

6. Paging query (limit)

Select * from table_name where table_name = 1;

8. Subquery (nested query)

9. Summary

DCL: Manage users and authorize users

1. Manage users

1.1 Adding a User

1.2 Deleting a User

1.3 Changing a User Password

1.4 Changing the Password of user root

1.5 mysql Forgets the password of user root

2. Rights management

2.1 Querying Permission

2.2 Granting Permission

2.3 Revoking Permission

Seven constraints

1. The concept

2. Non-null constraint –not NULL

He is the only one who is unique

4. Primary key constraint –primary key

5. Foreign key constraint — Foreign key

TCL: Transaction control

1. What are transactions

2. Why use transactions

3. How are transactions used

4. Principle of transactions

5. Rollback point (optimization of transactions)

6. Four Characteristics of Transactions (ACID)

7. Transaction isolation level

9 restoring and backing up mysql

9.1 Cli Mode

9.2 Graphical operations of SQLyog

X. Database design

10.1 Relationships between tables

10.2 Three paradigms of database

Related Posts

The method of the go

How to complete seamless data migration quickly and stably

Django uWSGi + nginx