Preface
This article class capacity
1. Several major constraints of database 2. Relationship between tables
Constraints:
Primary key constraint:
Action : In order to ensure the validity and integrity of data,
mysql Constraints commonly used in : Primary key constraint (primary key) Only 1 Constraint (unique) Non-null constraint (not null) Foreign key constraint (foreign key)
Primary key constraint : Decorated fields only 1 Non-empty
Attention :1 A table can only have 1 Primary keys , This primary key can contain multiple fields
Mode 1: Add constraints while building tables Format : Field name Field type primary key
Mode 2: Add constraints in the constraint area while building a table
After all the field declarations are completed, , Is the constraint area
Format : primary key( Field 1, Field 2)
create table pk01(
id int,
username varchar(20),
primary key (id)
);
insert into pk01 values(1,'tom');-- Success
insert into pk01 values(1,'tom');-- Failure Duplicate entry '1' for key 'PRIMARY'
insert into pk01 values(null,'tom');-- Failure Column 'id' cannot be null
create table pk01(
id int primary key,
username varchar(20),
primary key (id)
);-- Wrong 1 A table can only have 1 Primary keys
Mode 3: After building the table, , Adding constraints by modifying the table structure
create table pk02(
id int,
username varchar(20)
);
alter table pk02 add primary key( Field name 1, Field name 2..);
alter table pk02 add primary key(id,username);
insert into pk02 values(1,'tom');-- Success
insert into pk02 values(1,'tomcat');-- Success
insert into pk02 values(1,'tomcat');-- FailureUniquely constraint
Decorated fields only 1, Right null Doesn't work
Mode 1: Add constraints while building tables Format : Field name Field type unique
create table un(
id int unique,
username varchar(20) unique
);
insert into un value(10,'tom');-- Success
insert into un value(10,'jack');-- Errors Duplicate entry '10' for key 'id'
insert into un value(null,'jack');-- Success
insert into un value(null,'rose');-- Success
Mode 2: Add constraints in the constraint area while building a table
After all the field declarations are completed, , Is the constraint area
unique( Field 1, Field value 2...)
Mode 3: After building the table, , Adding constraints by modifying the table structure
alter table Table name add unique( Field 1, Field 2);-- Added union only 1
alter table Table name add unique( Field 1);-- To 1 Add only 1
alter table Table name add unique( Field 2);-- To another 1 Add only 1
////////////////
create table un01(
id int,
username varchar(20)
);
alter table un01 add unique(id,username);
insert into un01 values(1,'tom');-- Success
insert into un01 values(1,'jack');-- Success
insert into un01 values(1,'tom');-- Failure Duplicate entry '1-tom' for key 'id'Non-null constraint
Characteristic : The decorated field is not empty
Mode :
create table nn(
id int not null,
username varchar(20) not null
);
insert into nn values(null,'tom');-- Wrong Column 'id' cannot be nullCase 1: Create a user table for many
create table user(
id int primary key auto_increment,
username varchar(20)
);
-- Create an order table
create table orders(
id int primary key auto_increment,
totalprice double,
user_id int
);To ensure the validity and integrity of data, add constraints (foreign key constraints). Add a foreign key constraint on the 1 side of multiple tables
Format: alter table Multiple Table Name add foreign key (foreign key name) references 1 Table Name (primary key);
For example: alter table orders add foreign key(user_id) references user(id);
After adding foreign key constraints, there are the following characteristics:
1. Data referenced from a slave table cannot be deleted from the main table 2. You cannot add data from a slave table that does not exist in the master table
Dealing with 1-to-many in development: Add a foreign key to multiple tables, the name 1 is like the name of the main table _ id, the field type 1 is like keeping the type of the primary key of the main table 1, In order to ensure the validity and integrity of data, foreign key constraints can be added to the foreign keys of multiple tables.
Case 2 1 Create a user table for many
-- Create a list of items
create table product(
id int primary key auto_increment,
name varchar(20),
price double
);
-- Create an intermediate table
create table orderitem(
oid int,
pid int
);Add a foreign key constraint alter table orderitem add foreign key(oid) references orders(id); alter table orderitem add foreign key(pid) references product(id);
Dealing with many-to-many in development: Introduce an intermediate table to store the primary keys of two tables, and generally set these two fields as joint primary keys, so that the many-to-many relationship can be split In two, one to more In order to ensure the validity and integrity of data, You need to add two foreign key constraints on the intermediate table.
Case 3-Multi-table query
Cartesian product:
Unconditional joint query of multiple tables . It doesn't mean anything
select a.*,b.* from a,b;Internal connection
Format 1: Explicit inner connection
select a.*,b.* from a [inner] join b on ab Connection conditions of
Format 2: Implicit inner connection
select a.*,b.* from a,b where ab Connection conditions ofExternal connection
Left outer connection :
select a.*,b.* from a left [outer] join b on Connection condition ;
Meaning :
Show first join Left (a) All data of the table , Associate queries according to conditions join Table on the right (b), If it meets the requirements, it will be displayed , Do not conform to the null Value display .
Right lateral junction :
select a.*,b.* from b right [outer] join a on Connection condition ;
Meaning :
Show first jion Table on the right (a) All data of the table , Associate queries according to conditions join The watch on the left (b), If it meets the requirements, it will be displayed , Do not conform to the null Value display .
Subquery :
1 Queries depend on another 1 Queries .