Python Learning day 39th: MySQL table related operations

Integrity constraints for tables

Constraints are the same as the width of the data type, which are optional parameters

Role: To ensure the integrity and consistency of data

Mainly divided into:

Not NULL identifies the field cannot be empty

Default defaults for this field

unsigned unsigned

Zerofill using 0 padding

Auto_increment identifies the field's autogrow (integer type, and primary key)

Primary key identifies the field as the primary key of the table and can uniquely identify the record

Foreign key identifies the field as the foreign key of the table

The unique key identifies the value of the field as unique

A, not NULL and default

CREATE TABLE T1 (

ID int primary KEY auto_increment,

Name varchar (+) is not NULL,

Sex enum (' Nale ', ' female ') not null default ' male '

);

Insert into T1 (name) VALUES (' Egon '), (' LXX '), (' Alex ');

Second, unique key

CREATE table t2 (x int unique);

CREATE TABLE t3 (

x int,

Y varchar (5),

Unique key (x)

);

CREATE TABLE t4 (

x int,

Y varchar (5),

Constraint uni_x Unique key (x)

); # give the key a name

CREATE TABLE service (

IP varchar (15),

Port int,

Unique Key (Ip,port)

); # Union Unique

Third, primary key

Primary KEY = NOT NULL unique when standing on a constraint angle

After the establishment of the table, must pay attention to:

1, must have and only one primary key

2, usually the ID field is set as the primary key

CREATE TABLE T5 (

ID int PRIMARY KEY auto_increment

);

# Supplemental Storage Engine:

CREATE table T12 (x int) engine= ' MyISAM '; #会创建三个文件

CREATE table t13 (x int) engine= ' InnoDB '; #一般都用这个, create two, primary key and data in one table

CREATE table t14 (x int) engine= ' memory '; #内存中, service-side shutdown disappears

CREATE table t15 (x int) engine= ' blackhole '; #不会有任何数据信息

Iv. FOREIGN key: Limit association table The value of one field must be from a field in the associated table.

Attention:

1. The associated field must be a key, usually the ID field

2. When creating a table: The associated table must be established before the associated table can be established

3. When inserting a record: You must insert a record into the associated table before you can insert a record into the associated table

4. Delete: You should delete the records in the associated table EMP before deleting the records corresponding to the associated table

CREATE TABLE DEP (

ID int primary KEY auto_increment,

Dname varchar (20),

Info varchar (50)

);

CREATE TABLE EMP (

ID int primary KEY auto_increment,

Name varchar (15),

Age int,

dep_id int,

Foreign KEY (dep_id) references dep (ID)

ON UPDATE cascade

ON DELETE Cascade

);

Insert into DEP (Dname,info) values

(' IT ', ' limited technical capacity '),

(' Sale ', ' non-literate sector '),

(' HR ', ' recruit less than people department ');

INSERT into EMP (NAME,AGE,DEP_ID) values

(' Egon ', 18, 1),

(' Alex ', 28,2),

(' WSJ ', 38,2),

(' LXX ', 30, 1),

(' Xiaohou ', 18, 3);

# Find the two-sheet relationship tips

EMP DEP

#1, stand in the angle of the left table: to find out if multiple records of the left-hand emp can correspond to a record of the right-table dep

Translation: Can multiple employees belong to one department

#2, and then stand at the angle of the right table: Find out if multiple records for the right-hand-table DEP can correspond to a single record of the Left-side EMP

Translation: Can multiple departments have the same employee

# Many to one: the judgment of the result

#1, if only one-way multi-pair set up, then the final relationship is more to one

#2, add a field dep_id in the EMP table, the Field Foreign Key association DEP (ID)

# Many-to-many: the judgment of the result

#1, two-way multi-pair one is many-to-many

#2, need to create a third table with a field value FK left table, a field value of FK right table

Create TABLE author (

ID int primary KEY auto_increment,

Name varchar (16),

Age int

);

CREATE TABLE book (

ID int primary KEY auto_increment,

Bname varchar (20),

Price int

);

CREATE TABLE Author2book (

ID int primary KEY auto_increment,

author_id int,

book_id int,

Foreign KEY (author_id) references author (id) on UPDATE cascade on DELETE CASCADE,

Foreign KEY (book_id) references book (ID) on UPDATE cascade on DELETE CASCADE

);

# One to one: no need for tips, a single piece of data from the left table corresponds to a single record in the right table

Fk+unique

Python Learning day 39th: MySQL table related operations