3. Table related operations

1. Introduction to storage engine

1.1. What is a storage engine

Database = = = folder created in mysql

Table = = = file created in Library

In real life, the files we use to store data have different types, and each file type corresponds to its own different processing mechanism: for example, txt type for text processing, excel for table processing, png for picture processing, etc

Tables in the database should also have different types. Different types of tables will correspond to different access mechanisms of mysql. Table types are also called storage engines

Storage engine is the implementation method of how to store data, how to index the stored data, and how to update and query data. Because the storage of data in relational database is in the form of table, the storage engine can also be called table type (that is, the type of storing and operating this table)

There is only one storage engine in databases such as Oracle and SQL Server, and all data storage management mechanisms are the same. MySQL database provides a variety of storage engines. Users can choose different storage engines for data tables according to different needs, and users can also write their own storage engines according to their own needs.

SQL parser, SQL optimizer, buffer pool, storage engine and other components exist in every database, but not every database has so many storage engines. MySQL's plug-in storage engine allows developers of the storage engine layer to design the storage layer they want. For example, some applications need to meet the requirements of things, some applications do not need such strong requirements for transactions, and some applications do not need such strong requirements for transactions; Some hope that the data can be stored permanently, while others just want to put it in memory to provide temporary and rapid query of data.

The storage engine is the way to store data

1.2. MySQL supported storage engine

InnoDB

It's MySQL 5 5 and later default storage engines

More secure data storage

Function:

​ 1. Support things

​ 2. Row level lock

​ 3. Foreign key

MyISAM

It's MySQL 5 Default storage engine before version 5

The speed is faster than innodb, but we pay more attention to data security

Function:

​ 1. Unsupported things

​ 2. Table lock

MEMORY

Memory engine (all data stored in memory) power failure and data loss

BLACKHOLE

Whatever exists, it disappears immediately (black hole)

"""
# View all storage engines
show engines;

# Different storage engines have similarities and differences in storing tables
create table t2(id int) engine=innodb;
innodb
t2.frm	Table structure
t2.idb   Table data

create table t3(id int) engine=myisam;
myisam	
t.frm	Table structure
t.MYD	Table data
t.MYI	Index( index)Similar to the catalog of books, it is faster to find the corresponding data based on the catalog

create table t4(id int) engine=memory;
blackhole	
t.frm

create table t5(id int) engine=blackhole;
memory	
t.frm	Data is stored in memory without files

# Save data
insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1);
insert into t5 values(1);
"""

2. Table introduction

A table is equivalent to a file, and a record in the table is equivalent to a line of content in the file. The difference is that a record in the table has a corresponding table title, which is called a table field

id	name
1	egon
2	wyz

id and name are called fields, and the rest of a line is called a record

3. Create table

# grammar
create table Table name(
    Field name 1 type(width) constraint condition,
    Field name 2 type(width) constraint condition,
    Field name 3 type(width) constraint condition
)

# be careful
1 Field names cannot be duplicate in the same table
2 Width and constraints are optional, while field names and types are required
  If constraints are written, multiple constraints can also be written
    Field name 1 type(width) Constraint 1 constraint 2
    create table t1(id); report errors
3 The last line cannot have a comma
	 create table t1(id int,name char,);  # report errors
"""supplement"""
# width
	In general, it refers to the restrictions on stored data
    create table t1(name char);  # The default width is 1
	insert into t1 values('wyzdsb');
    insert into t1 values(null);
    	Different effects will appear for different versions
        5.6 The strict default mode is not enabled by default. It is stipulated that only one character can be saved. If you give more than one word, I will automatically intercept one character for you
        5.7 If the version is above or the strict mode is enabled, it is specified that only a few can be saved, and an error cannot be reported immediately beyond the range
 Guidelines for using databases:
	Let the database work as little as possible, and don't add additional pressure to the database
    
# Constraint null, not null (cannot be null)
create table t8(id int,name char not null);
insert into t8 values(1,null);
report errors: ERROR 1048 (23000): Column 'name' cannot be null 
    
"""
What is the relationship between width and constraints
	Width is used to limit the storage of data
	Constraints are additional constraints added to the width
"""
    

4. Basic data type

The database storage type is as follows:

[external chain picture transfer failed. The source station may have anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-b5SQgtzQ-1627731764069)(%E6%95%B0%E6%8D%AE%E5%BA%9303%E2%80%94%E2%80%94%E8%A1%A8%E7%9B%B8%E5%85%B3%E6%93%8D%E4%BD%9C.assets/%E6%95%B0%E6%8D%AE%E5%BA%93%E7%B1%BB%E5%9E%8B.jpg)]

4.1. integer

• classification

  TINYINT， smallint， mediumint， int， bigint

• effect

  Store age, grade, number, etc

"""
with tinyint Shaping is signed by default
	Is there a symbol
		It is signed by default
	What happens if you exceed
		Beyond the limit, only the maximum acceptable value is stored
"""
create table t6(id tinyint);
insert into t6 values(-129),(129);

# unsigned of constraints
create table t9(id tinyint unsigned)

# What exactly is the width in integer brackets
create table t10(id int(8));
insert into t10 values(123456789);
"""
Special case: only the number in the integer bracket does not represent the limit number of digits
id int(8)
	If the number does not exceed 8 digits, it is filled to 8 digits by default
	If the number exceeds 8 digits, several digits are saved (but the maximum range is still followed)
"""
# zerofill of the constraint is filled from 0 to 8 bits
create table t11(id int(8) unsigned zerofill);

# Summary:
For the shaping field, there is no need to specify the width in parentheses, and the default width is used, because the width cannot be limited even if the limit is written

4.2. float

• classification

  float，double，decimal

• effect

  Save height, weight, salary

# Storage limit
float(255,30)  # 255 digits in total, 30 decimal places
double(255,30)  # 255 digits in total, 30 decimal places
decimal(255,30)  # 65 digits in total and 30 decimal places

# Accuracy verification
create table t13(id float(255,30));
create table t14(id double(255,30));
create table t15(id double(65,30));

insert into t13 values(1.111111111111111111111111111111);
insert into t14 values(1.111111111111111111111111111111);
insert into t15 values(1.111111111111111111111111111111);

# accuracy
float < double < decimal

4.3. character

• classification

"""
char
	Fixed length
	char(4)  If the data exceeds four characters, an error will be reported directly, and the space is not enough to complete the four characters
	Lengthen
	varchar(4)	If the data exceeds four characters, an error will be reported directly. There are not enough data to save
	
"""
create table t16(name char(4));
create table t17(name varchar(4));

insert into t16 values('a');
insert into t17 values('a');

# Small method char_length statistics field length
select char_length(name) from t16;
select char_length(name) from t17;
"""
The first thing is for sure char The data stored on the hard disk is absolutely real data with spaces, but when displayed MySQL Extra spaces will be automatically eliminated
"""

# Modify sql_mode prevents MySQL from performing automatic culling
set global sql_mode = 'STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';

Comparison between char and varchar

"""
char
	Disadvantages: waste of space
	advantage: Access is simple
		 Data can be accessed directly according to fixed characters
		 Save according to five characters, and retrieve directly according to five characters
		  
varchar
	Advantages: space saving
	Disadvantages: access is troublesome
		1 bytes+jason 1bytes+egon
		 You need to make a header when saving
		 When fetching, you also need to read the header before you can read the real data
 It was basically used before char Actually, it's used now varchar There are many
"""

Supplement:
	After you come to the company, you don't need to consider the field type and field name at all
    Because the email sent to you by the product manager has indicated it all

4.4. Time type

• classification

  date: mm / DD / yy

  datetime: mm / DD / yyyy, H / min / S

  time: hour, minute and second

  Year: year

  create table student(
      id int,
      
  )
  

4.5. Enumerations and collection types

• classification

"""
enumeration(enum)	Choose one more
 aggregate(set)     Multiple choice
"""

• Specific use

create table user(
	id int,
    name char(16),
    gender enum('male','female','others')
);
insert into user values(1,'jason','male');
issert into user values(2,'egon','xxxoo');
# The enumeration field can only be selected from American dramas when saving data later

create table teacher(
    id int,
    name char(16),
    gender enum('male','female','others'),
    hobby set('read','DBJ','basketball')
    
);
insert into teacher values(1,'jason','male','read');
insert into teacher values(2,'tank','male','read,DBJ');
insert into teacher values(3,'egon','male','dog shit');
# A collection can write only one, but it cannot write a collection without enumeration

5. Strict mode

5.6 Strict default mode is not enabled by default
# View strict mode
show variables like "%mode";
sql_mode  Display database schema
 Fuzzy matching/query
	keyword like
    	%:Match any number of characters
        _:Match any single character
# Modify strict mode
	set session   Valid only in the current window
    set global	  Globally valid
    set global sql_mode = 'STRICT_TRANS_TABLES';
    After modification, re-enter the server

6. Table integrity constraints

6.1. introduce

Constraints, like the width of data types, are optional parameters

Function: it is used to ensure the integrity and consistency of data, mainly including:

primary key		Identify this field is the primary key of the table, which can uniquely identify records
foreign key		Identify the field as the foreign key of the table
not null		The identification field cannot be empty
unique 			Indicates that the value of this field is unique
auto_increment	The value identifying this field is automatically increased (integer type and primary key)
default		    Set the default value for this field
unsigned		Unsigned
zerofill		Fill with 0

6.2. constraint condition

# Supplementary knowledge points you can specify fields when inserting data
create table t1(
    id int,
    name char(16)
);
insert into t1(name,id) values('jason',1);

Unique unique

# Single column unique
create table t3(id int unique,name char(10));
insert into t3 values(1,'egon');  # correct
insert into t3 values(1,'wyz');  # error

# United only
'''
ip and port
 Individual can be repeated, but the sum must be unique

'''
create table t4(
    id int,
    ip char(16),
    port int,
    unique(ip,port)
);
insert into t4 values(1,'127.0.0.1',8080);
insert into t4 values(2,'127.0.0.2',8080);
insert into t4 values(2,'127.0.0.2',8081);
insert into t4 values(2,'127.0.0.2',8081);  # report errors

Primary key primary key

'''Only from the perspective of constraint effect primary key Equivalent to not null + unique Non empty and unique'''create table t5(id int primary key);insert into t5 values(null);insert into t5 values(1);'''In addition to its constraint effect, it is also Innodb Storage engines organize data by Innodb When creating tables, the storage engine must have primary key Because it is similar to the book directory, it can help the group prompt the query efficiency, and it is also the basis of the document object'''# 1. There is only one primary key in a table. If you do not set a primary key, you will search from top to bottom until you encounter a non empty and unique field, which will be automatically upgraded to a primary key create table t7(    			id int,                name char(16),                age int unsigned not null unique,                addr char(32) not null unique);+-------+------------------+------+-----+---------+-------+| Field | Type             | Null | Key | Default | Extra |+-------+------------------+------+-----+---------+-------+| id    | int(11)          | YES  |     | NULL    |       || name  | char(16)         | YES  |     | NULL    |       || age   | int(10) unsigned | NO   | PRI | NULL    |       || addr  | char(32)         | NO   | UNI | NULL    |       |+-------+------------------+------+-----+---------+-------+age Automatic upgrade to primary key# 2. If there is no primary key or any other non empty and unique field in the table, then Innodb A hidden field provided internally will be used as the primary key. Hiding means that you can't use it, so you can't prompt the query speed# 3. A table should usually have a primary key field and usually id/uid/sid Field as primary key# Single primary key create table t8(id int primary key);# Joint primary key (multiple fields are combined as the primary key of the table, which is essentially a primary key) create table T9 (IP char (16), port int, primary key (IP, port));

auto_increment self increment

create table t10(    id int primary key auto_increment,    name char(16)    );insert into t10(name) values('jason'),('wyz'),('kevin');# Note auto_increment can only be added to the primary key, not to ordinary fields

conclusion

'''After creating a table id(Unique identification of the data id，uid，sid)Field'''

supplement

delete from When deleting data in a table, the auto increment of the primary key will not stop
truncate table t1 Clear table data and reset primary key
binlog  

Foreign key

'''
Foreign keys are used to help us establish the relationship between tables
foregin key

'''

7. Table relationship

'''
One to many relationship
 Many to many relationship
 One to one relationship
 It doesn't matter.
'''

7.1. One to many relationship

'''
When judging the relationship between tables, if you are not familiar with them in the early stage, you must transpose and think from the perspective of two tables according to my suggestions

Take employee table and department table as an example
	First stand on the employee table
		Think about whether an employee can correspond to multiple departments (whether an employee data can correspond to multiple Department data). Conclusion: No
		Think about whether a department can correspond to multiple employees (whether a department data can correspond to multiple employee data)
	come to conclusion
		Employee table and department table are one-way one to many
		So the table relationship is one to many
'''
foreign key
1. The foreign key field of one to many table relationship is built on one side of many tables
2. When creating a table, you must first create an associated table
3. When entering data, you must also enter the associated table first
# Establishing table relationships with SQL statements
create table department(
    id int primary key auto_increment,
    name varchar(20) not null
);
create table employee(
    id int auto_increment primary key,
    name varchar(20) not null,
    gender enum('male','female','others') default 'male',
    dep_id int,
    foreign key(dep_id) references department(id)
);

insert into department(name) values('ministry of education'),('engineering department'),('Fire Department'),('Information Department');
insert into employee(name,dep_id) values('wyz',1),('djf',1),('egon',2),('tom',3),('bob',4);

# Modify the id field in the department table
update department set id=20 where id=2;
No, report an error
# Delete the data in the department table
delete from department; no way

# 1 delete the employee data corresponding to the teaching department before deleting the Department
	The operation is too cumbersome
# 2. Truly realize the relationship between data
	update cascade 
    cascading deletion
create table department(
    id int primary key auto_increment,
    name varchar(20) not null
);
create table employee(
    id int auto_increment primary key,
    name varchar(20) not null,
    gender enum('male','female','others') default 'male',
    dep_id int,
    foreign key(dep_id) references department(id)
    on update cascade
    on delete cascade
);
 

7.2. Many to many

"""
Books and authors
"""
create table book(
    id int primary key auto_increment,
    title varchar(32),
    price int
);

create table author(
    id int auto_increment primary key,
    name varchar(20) not null
);

create table author2book(
    id int primary key auto_increment,
    author_id int not null,
    book_id int not null,
    foreign key(author_id) references author(id)
    on update cascade
    on delete cascade,
    foreign key(book_id) references book(id)
    on update cascade
    on delete cascade
);

insert into book(title,price) values('python',20),('go',20),('linux',20);
+----+--------+-------+
| id | title  | price |
+----+--------+-------+
|  1 | python |    20 |
|  2 | go     |    20 |
|  3 | linux  |    20 |
+----+--------+-------+

insert into author(name) values('egon'),('tank'),('tom');
+----+------+
| id | name |
+----+------+
|  1 | egon |
|  2 | tank |
|  3 | tom  |
+----+------+

insert into author2book(author_id,book_id) values(1,1),(1,2),(1,3),(2,1),(2,3),(3,1);
+----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
|  1 |         1 |       1 |
|  2 |         1 |       2 |
|  3 |         1 |       3 |
|  4 |         2 |       1 |
|  5 |         2 |       3 |
|  6 |         3 |       1 |
+----+-----------+---------+

7.3 one to one

"""
id,name,age,addr phone hobby email
 If a table has many fields, not all fields can be used for each query
 Divide the watch into two
	User table
		id name age
	User details table
		addr phone hobby email

"""

8. Modification table

# MySQL is not case sensitive
'''
1 Modify table name
	alter table Table name rename New table name;
	
2 Add field
alter table Table name add Field name field type (width) constraint;
alter table Table name add Field name field type (width) constraint first;
alter table Table name add Field name field type (width) constraint after Field name;

3 Delete field
	alter table Table name drop Field name;
4 Modify field
	alter table Table name modify Field name field type(width) constraint condition;
	alter table Table name change Old field name new field name field type(width) constraint condition;
'''

9. Copy table

sql The organization of statement query is actually a virtual table

create table Table name select * from Old table;   Primary and foreign keys cannot be copied