Content Summary

• constraint condition
• Relationships between tables (constraints)
• Modify the full syntax of the table
• Copy Table

constraint condition

Default default

"""
# Supplement: You can specify fields when inserting data
create table t1 (
    id int, 
    name varchar(16)
);

insert into t1 (name, id) values ('samuel', 1);

create table t2 (
    id int, 
    name varchar(16),
    gender enum('male', 'female', 'others') default 'male'
);

insert into t2 (id, name) values (1, 'samuel');
insert into t2 values (2, 'nagase', 'female');
"""

Unique unique

"""
# Single Column Unique
create table t3 (
    id int unique,
    name varchar(16)
);

insert into t3 values (1, 'samuel'), (1, 'nagase');
insert into t3 values (1, 'samuel'), (2, 'nagase');

# Union Unique
# ip and port, individual can repeat, but together must be unique
create table t4 (
    id int, 
    ip varchar(16),
    port int,
    unique (ip, port)
);

insert into t4 values (1, '192.168.1.1', 8000);
insert into t4 values (2, '192.168.1.1', 8001);
insert into t4 values (3, '192.168.1.2', 8000);
insert into t4 values (4, '192.168.1.1', 8000);
"""

Primary key primary key

"""
# In terms of constraints alone, primary key is equivalent to not null + unique
# Not empty and unique!!!
create table t5 (
    id int primary key
);

insert into t5 values (null);
insert into t5 values (1), (1);
insert into t5 values (1), (2);

# In addition to its constraining effect, it is also the basis for the Innodb storage engine to organize data.
# Innodb storage engine must have primary key when creating tables
# Because it is similar to a Book catalog, it helps improve query efficiency and is the basis for building tables

# 1. There is only one primary key in a table. If no primary key is set, it will be searched from top to bottom until a non-empty and unique field is encountered and upgraded to primary key
create table t6 (
    id int,
    name varchar(16), 
    age int not null unique, 
    addr varchar(32) not null unique
);
# 2. If there is no primary key or any other non-empty and unique field in the table, Innodb uses a hidden field provided internally as the primary key. Hiding means that you cannot use it and therefore cannot improve query efficiency.

# 3. There should always be a primary key field in a table, and the id is usually used as the primary key
# Single Field Primary Key
create table t7 (
    id int primary key,
    name varchar(16)
);
# Joint primary key (multiple fields joined as the primary key of a table, or essentially a primary key)
create table t7 (
    ip varchar(16),
    port int,
    primary key (ip, port)
);
"""

auto_increment self-increasing

"""
# When numbers are especially large, manual maintenance is too cumbersome, so it involves self-increasing
create table t8 (
    id int primary key auto_increment,
    name varchar(16)
);

insert into t8 (name) values ('samuel'), ('nagase'), ('matthew');
# Note: auto_Increments are usually added to the primary key key key, not to normal fields
"""

Supplement: Two ways to delete tables

"""
delete from table name

truncate table name
"""

The difference between the two methods: delete from table name This method, when deleting data from a table, does not stop the auto-growth of the primary key and continues numbering

However, truncate table names, in addition to emptying the data table, reset the auto-growth of the primary key to restart from 1

Relationships between tables

"""
# There are many fields defined in an employee table
# id name gender dep_name dep_desc

# Analyze the problems with the current tables:
# 1. The organizational structure of the table is not very clear
# 2. Wasting disk space
# 3. Extensibility of data is very poor

# So how to optimize?Writing so many fields all in one table is equivalent to writing all the code in one file, which is a big problem.
# So we should break it up into: the employee table and the Department table
"""

Foreign Key Constraints

# Foreign keys are used to help us establish relationships between tables
foreign key

Relationship between tables

There are only four relationships between #tables and tables

#One-to-many relationships
    #Note: In MySQL, there is no many-to-one relationship where one-to-many, many-to-one are all one-to-many
 #Many-to-many relationships
 #One-to-one relationships
 #That's OK

One-to-many relationship

"""
When judging the relationship between tables and tables, we must make judgments step by step, using the correct method, that is
 "Transposition Thinking" considers problems from the perspective of two tables

Examples: employee and department tables
 Stand on the employee list first
    Think about an employee being able to work in multiple departments --- No!!!
Stand on the Department table again
    Think about whether a department can contain more than one employee --- yes!!!
Come to conclusion:
    The employee and department tables represent one-to-many, so the table relationship is one-to-many.
"""

foreign key
[Principle 1) Which side of the table foreign key field of a one-to-many relationship should be based on?
[Principle 2) When creating a table, be sure to first create the party associated with it

# SQL statement
create table dep (
    id int primary key auto_increment,
    dep_name varchar(16),
    dep_desc varchar(32)
);

create table emp (
    id int primary key auto_increment,
    name varchar(16), 
    gender enum('male', 'female', 'others') default 'male',
    dep_id int,
    foreign key (dep_id) references dep (id)
);

insert into dep (dep_name, dep_desc) values ('Department of Education', 'Educate people by teaching'), ('Technology Department', 'Limited technical capacity'), ('Ministry of Foreign Affairs', 'International Relations');

insert into emp (name, dep_id) values ('samuel', 2), ('nagase', 1), ('matthew', 1), ('garnet', 3);

# Modify the id field of the dep table
update dep set id = 200 where id = 2;    operation failed

# Delete data from dep table
delete from dep;    operation failed

//Both statements failed because the dep table is referenced by the emp table, so the dep table cannot be deleted.The solutions are as follows:
# 1,Delete the employee data for the Department before deleting the Department
 //It's too cumbersome

# 2,Really achieve the relationship between data, update synchronizes update, delete synchronizes delete
# That means cascading updates, cascading deletions, so how do you do that???
# Solution: Add additional constraints when creating tables
create table dep (
    id int primary key auto_increment,
    dep_name varchar(16),
    dep_desc varchar(32)
);

create table emp (
    id int primary key auto_increment,
    name varchar(16),
    gender enum('male', 'female', 'others') default 'male',
    dep_id int,
    foreign key(dep_id) references dep(id) on update cascade on delete cascade
);

Many-to-many relationships

"""
When judging the relationship between tables, we must use the correct method, which is "transposition thinking", and stand in the perspective of two tables.

For example, book and author tables
 Start with a book list and think about whether a book can have more than one author --- Yes!!!
Standing on the author table, think about whether an author can publish more than one book - yes!!!
Come to conclusion:
The book table and the author table are one-to-many in both directions, so the table relationship is "many-to-many" relationship
"""

create table book (
    id int primary key auto_increment,
    title varchar(32),
    price int, 
    author_id int,
    foreign key(author_id) references author(id) on update cascade on delete cascade
);

create table author (
    id int primary key auto_increment,
    name varchar(32), 
    age int, 
    book_id int, 
    foreign key(book_id) references book(id) on update cascade on delete cascade
);

# If the table is created according to the above statement, the result is:: Failed!!!
//Recall our two main principles:
[Principle 1) For one-to-many tables, which side of the many foreign key fields should be based on
[Principle 2) When creating a table, be sure to first create the party associated with it
//For a book table and an author table, a book table refers to the id of the author table. For a Book table, the author table is the party that is associated. For an author table, the book table is the party that is associated. Thus, both parties are the party that is associated, then,,, what to do..The two sides are just dragging.So the creation of both tables failed!!!

# Actually, we just want to record the relationship between the book and the author
# Foreign keys cannot be created in two original tables for many-to-many relationships
# We need to create a separate table to store the relationship between the two tables
create table book (
    id int primary key auto_increment,
    title varchar(16),
    price int
);

create table author (
    id int primary key auto_increment, 
    name varchar(16), 
    age int
);

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

One-to-one relationship

"""
id name age gender addr phone hobby email...
If there are so many fields in a table that not all of them can be used in each query, we divide the table into two parts
 User table
    id name age
 User Details Table
    id addr phone hobby email...

The method of "transposition thinking":
Start with the user table: can a user correspond to multiple user details - no!!!
Consider again from the perspective of user details: whether a user detail belongs to multiple users - no!!!
The conclusion is that one-way one-to-many is not valid, then the relationship between the two is either one-to-one or not.
Note that for a one-to-one relationship, foreign key fields are built on the table with the highest query frequency
"""

create table user_detail (
    id int primary key auto_increment,
    phone int,
    addr varchar(64)
);

create table user (
    id int primary key auto_increment,
    name varchar(16),
    age int,
    user_detail_id int unique,
    foreign key(user_detail_id) references user_detail(id) on update cascade on delete cascade 
);

summary

"""
foreign key is required to create table relationships
    One-to-many
        Foreign key fields are built on more than one side
    Many-to-many
        Open a third table by yourself
    One-on-one
        It's okay to set it up in any table, but it's recommended that you set it up in tables with high query frequency
        
How to establish relationships between tables
    Transpositional consideration!!!
        Employees and Departments
        Books and Authors
        User and user details
"""

Modify Table

# MySQL is case insensitive
 # 1, modify table name
 alter table table name rename new table name;

# 2, Add Field
 alter table name add field name field type (width) constraint; #defaults to appended at the end of the table
 alter table name add field name field type (width) constraint first; #appended at the beginning of table
 alter table name addfield name field type (width) restriction after field name; #manual description of where to add

# 3, Delete Field
 alter table table table name drop field name;

# 4, Modify Field
 alter table name modify field name field type (width) constraint;
alter table name change old field name new field name field type (width) constraint;

Copy Table

#When we execute an SQL statement, the result is a virtual table
 create table new table name select * from table name; # but primary keys, foreign keys, indexes cannot be copied... Just structure and data are copied