Shulou(Shulou.com)06/01 Report--

Preface

The content of this article

1. Several constraints of the database

2. The relationship between tables and tables

Constraints:

Primary key constraint:

Function: in order to ensure the validity and integrity of data, constraints commonly used in mysql: primary key constraints (primary key) unique constraints (unique) non-empty constraints (not null) foreign key constraints (foreign key) primary key constraints: modified fields are unique non-null Note: a table can only have one primary key This primary key can contain multiple fields in mode 1: add constraint format while creating table: field name field type primary key mode 2: add constraints in the constraint area when the table is created. After all the fields have been declared, the format of the constraint area is primary key (field 1). Field 2) create table pk01 (id int, username varchar (20), primary key (id)) Insert into pk01 values (1);-- successful insert into pk01 values (1);-- 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)) Wrong table can only have one primary key mode 3: after the table is created, add the constraint create table pk02 (id int, username varchar (20)) by modifying the table structure Alter table pk02 add primary key (field name 1, field name 2.); alter table pk02 add primary key (id,username); insert into pk02 values (1Magnetom');-- successful insert into pk02 values (1Magnetomcat`) -- successful insert into pk02 values (1);-- failure

Unique constraint

The decorated field is unique and does not work on null. 1: add constraint format while creating the table: field name field type unique create table un (id int unique, username varchar (20) unique); insert into un value (10 ~ (10)) -- successful insert into un value (10 'for key');-- error Duplicate entry'10 'for key' id' insert into un value (null,'jack');-- successful insert into un value (null,'rose') -- successful method 2: add constraints to the constraint area while creating the table. After all the fields are declared, the constraint area is unique (field 1, field value 2...). Method 3: after creating the table, add the constraint alter table table name add unique (field 1, field 2) by modifying the table structure;-- add the joint unique alter table table name add unique (field 1);-- add a unique alter table table name add unique (field 2) -- add a unique / create table un01 to another (id int, username varchar (20)) Alter table un01 add unique (id,username); insert into un01 values (1 recorder tom');-- success insert into un01 values (1);-- success insert into un01 values (1);-- failure Duplicate entry'1 insert into un01 values' for key 'id'

Non-empty constraint

Features: modified field non-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 one-to-many-create user tabl

Create table user (id int primary key auto_increment, username varchar (20));-- create order table create table orders (id int primary key auto_increment, totalprice double, user_id int)

In order to ensure the validity and integrity of the data, add constraints (foreign key constraints).

Add a foreign key constraint on one side of a multi-table

Format:

Alter table multiple table name add foreign key (foreign key name) references one table name (primary key)

For example:

Alter table orders add foreign key (user_id) references user (id)

After adding a foreign key constraint, it has the following characteristics:

1. Data referenced from the slave table cannot be deleted in the master table. Data that does not exist in the master table cannot be added from the table

Handle one-to-many in development:

Add a foreign key to multiple tables. The name is generally the name of the primary table _ id. The field type is generally the same as the primary key type of the primary table.

In order to ensure the validity and integrity of the data, foreign key constraints can be added to the foreign keys of multiple tables.

Case 2 one-to-many-create user tabl

Create merchandise table create table product (id int primary key auto_increment, name varchar (20), price double); create 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)

Deal with many-to-many in development:

An intermediate table is introduced to hold the primary keys of the two tables, and the two fields are generally set as the federated primary key, so that the many-to-many relationship can be split.

It's a pair of two.

In order to ensure the validity and integrity of the data

You need to add two foreign key constraints to the intermediate table.

Case 3-Multi-table query

Cartesian product:

Unconditional federated query of multiple tables. It doesn't mean anything to select a. From.

Internal connection

Format 1: explicit inner join select a. * from a [inner] join b on ab join condition format 2: implicit inner join select A. implicit inner connection b. * from afield b where ab join condition

External connection

Left outer join: select a.outside from a left [outer] join b on connection condition; meaning: first show all the data of table (a) on the left side of join, query the table (b) on the right side of join according to the condition, and show it if it meets the condition, but does not conform to the null value. Right outer join: select a.outside from b right [outer] join an on connection condition; meaning: first show all the data of table (a) on the right side of jion, query the table (b) on the left side of join according to the condition, and show it if it meets the condition, but does not conform to the null value. Subquery: one query depends on another.

The above is the detailed explanation and integration of the basic operation of mysql introduced to you by the editor. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. Thank you very much for your support to the website!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.