Database MySQL Learning (iii)

Delete a database table

drop table [if exists] table I, table two ...;

Table partitioning: There are 10 million books in the book Information table, how to optimize him, one of the ways is table partitioning. is to divide the data of a table into chunks that can be on the same disk or on a different disk.

The basic operations are as follows:

The BookInfo table is created first, and then the partition "Partition by range" is partitioned by XX.

It was divided into three districts, P1,p2,p3.20109999 before, 20159999 before, and others.

Insert data into table "INSERT into XX values (xx, ' xx ')"

View the contents of each partition "select * from XX partition (XX)"

The concept of a primary key:

Each row in the table should have a column (or set of columns) that uniquely identifies itself. The columns that assume the role of identity are called primary keys .
without a primary key, the management of the data will be very confusing . For example, there are many identical records, and it is difficult to delete and modify specific rows.

FOREIGN key

In layman's words, two tables are associated, and the field that joins the two tables is the foreign key

Constraints

Ensure data integrity and uniqueness by restricting data on rows and columns of a table.

There are generally non-null constraints, PRIMARY KEY constraints, UNIQUE constraints, default constraints, foreign KEY constraints.

The main key constraint, the category number is the foreign KEY constraint, the book title is NOT NULL constraints and the unique constraints.

Non-null constraint

Table creation defaults to null (NULL), and if no value is specified when data is added, an error is made.

The creation is set.

You can also add a non-null constraint by modifying the table's methods.

This will cause an error when inserting the data

You can change the constraint by modifying the data type

PRIMARY KEY constraint

Requires that the data for the primary key column be unique and not empty, to uniquely identify a record of the table.

One table with up to one PRIMARY KEY constraint

Add primary key after creating column name first

or specify constraint XX primary key (XX) pk_id is your own defined primary key name.

You can also add a primary key by modifying it

1.alter table Bookinfo2 Add primary key (BOOK_ID);

2.alter table Bookinfo2 Modify book_id int primary key;

3.alter table Bookinfo2 Add constraint pk_id primary key (BOOK_ID);

Delete primary key

Direct ALTER TABLE BOOKINFO2 drop PRIMARY key;

You can also have a multiple-field federated primary key or a composite primary key

CREATE TABLE Bookinfo2 (
book_id int,
Book_name Char (20),
Primary KEY (Book_id,book_name)
);

Unique constraint

The method is the same as before with a unique or constraint Uk_banme unique (XX) directly after the column name.

Modify Add words

Default Constraints

FOREIGN KEY constraints

Database MySQL Learning (iii)