Detailed explanation of MySQL basic operations (Part 2)

Function: To ensure the validity and integrity of data, commonly used constraints in MySQL: primary key constraint (primary key) unique constraint (unique) non-empty constraint (not null) foreign key constraint (foreign key)
Primary key constraint: The modified field is unique and not empty. Note: A table can only have one primary key, which can contain multiple fields. Method 1: Add constraints while creating a table. Format: Field name Field type primary key
	Method 2: Add constraints in the constraint area while creating the table. After all the fields are declared, it 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');-- Failed Column 'id' cannot be null
		
		create table pk01(
			id int primary key,
			username varchar(20),
			primary key (id)
		);-- Error A table can only have one primary key		
	Method 3: After creating the table, add 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

The modified field is unique and does not work for null. Method 1: Add constraints while creating the table 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');-- Error Duplicate entry '10' for key 'id'
		insert into un value(null,'jack');-- Success insert into un value(null,'rose');-- Success		
	Method 2: Add constraints in the constraint area while creating the table. After all the fields are declared, it is the constraint area unique(field 1, field value 2...)
	Method 3: After creating the table, add constraints by modifying the table structure alter table table name add unique (field 1, field 2); -- Added joint unique alter table table name add unique (field 1); -- Add unique to one alter table table name add unique (field 2); -- Add unique to another		
		////////////////
			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'

Features: The modified field is not empty. Method:
		create table nn(
			id int not null,
			username varchar(20) not null
		);
		
		insert into nn values(null,'tom');-- Error Column 'id' cannot be null

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

-- Create a product table 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
);

Unconditional joint query of multiple tables. It doesn't make any sense. select a.*,b.* from a,b;

	Format 1: Explicit inner join select a.*,b.* from a [inner] join b on ab join condition Format 2: Implicit inner join select a.*,b.* from a,b where ab join condition

	Left Outer Join:
	select a.*,b.* from a left [outer] join b on join condition;
	mean:
		First, display all the data in the table (a) on the left side of the join, and then query the table (b) on the right side of the join based on the conditions. If the conditions are met, display them. If not, display them as null values.
  Right Outer Join:
	select a.*,b.* from b right [outer] join a on connection condition;
	mean:
		First display all the data in the table (a) on the right side of the join, and then query the table (b) on the left side of the join based on the conditions. If the conditions are met, the data will be displayed, otherwise it will be displayed as a null value.
  Subquery:
One query depends on another query.