Detailed explanation of mysql basic operation details of II

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');--  Failure 

Uniquely 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 null

Case 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 of 

External 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 .