Data table operation of the front-end learning Database

xTable of Contents [1] preparation [2] Create data table [3] View datasheet [4] record operation [5] record constraint [6] column operation [7] constraint action [8] Modify column [9] datasheet rename before

The data in the MySQL database is stored in a database object called a table (tables). A table is a collection of related data items that consists of columns (fields) and rows (records). The data table operations are described in more detail below

Preparatory work

Before you perform a data table operation, you need to log in to the MySQL server, create a database, and use the created database

Create a data table

The following is a data table created in the DB1 database tb1

CREATE TABLE [IF not EXISTS] table_name (column_name data_type,...)

In the following data table TB1, create a username field of the variable-length character type of varchar (20); Create an age field for an unsigned (0-255) integer of tinyint unsigned of one byte; Create a salary FLOAT (8,2 Salary field for unsigned floating-point numbers with a total length of 8 and a scale of 2 for unsigned

View Data Sheet

' pattern ' | WHERE Expr]

View column items for a data table

SHOW COLUMNS FORM Tbl_name

Logging operations

The first step in recording an operation is to write a record

When writing records, it is important to note that if there are no omitted fields, they are assigned directly in the order of the parameters, and if there are omitted fields, the field names and parameter values need to be written out

INSERT [into] tbl_name [(Col_name,...)] VALUES (VAL,...)

Below to find the records

SELECT EXPR,... From Tbl_name

Record constraints

Null value

Null         field value can be null NOT NULL     field value forbidden to null

First, create a data table Tb2,username field is forbidden to be empty, and the age field can be empty. When inserting a record, if the username field is empty, an error is indicated

Primary key

Only one primary key (PRIMARY key) can exist per data table, the primary key guarantees the uniqueness of the record and is not NULL automatically

Primary key can be written as key or primary key

Auto numbering

The AutoNumber (auto_increment) must be combined with the primary key, with a starting value of 1 and a 1 increment per time by default

Only

A unique KEY is a case where the same value cannot be present in the selected record, which guarantees uniqueness of the record, a field with a unique constraint can be null, and multiple unique constraints can exist for each data table

The username field is restricted by a unique constraint in the following record, and after adding username as ' Tom ' record, it is not allowed to add username as ' Tom ' record

Default value

Automatically assigns default values when inserting records without explicitly assigning a value to a field

FOREIGN key

FOREIGN KEY constraints (FOREIGN key) are used to maintain data consistency and integrity and to achieve one-to-one or one-to-many relationships

The foreign key column refers to the column that joins (FOREIGN key), the column referenced by the foreign key column is called the Reference column, and the foreign key column and the parameter column must have similar data types. Where the length of the number or whether the sign bit must be the same, and the length of the character can be different

An index is a special kind of file, and an index on a InnoDB data table is an integral part of a table space that contains reference pointers to all the records in the data table. Foreign key columns and reference columns must be indexed and MySQL will automatically create indexes if the reference column does not exist

A child table refers to a table with a foreign key column, and the table referenced by the child table is called the parent table. The parent and child tables must use the same storage engine and prohibit the use of temporary tables. The data table's storage engine can only be InnoDB

So, first you need to modify the default storage engine in MySQL configuration file My.ini

default-storage-engine = INNODB

Create parent table provices, reference column ID

Create child table users, foreign key column PID

The reference column ID of the parent table provices is the primary key column, and the primary key is created at the same time that the index is created automatically

below to see the index of the parent table provices

below to see the index of the child table users

There are altogether four options in the referential operation of a foreign key constraint. Used to set whether the child table also operates when the parent table is updated

1. CASCADE: Delete or update from parent table and automatically delete or update matching rows in child table

2, SET NULL: Deletes or updates rows from the parent table, and sets the foreign key column in the child table to null. If you use this option, you must ensure that the child table column does not specify not NULL

3. RESTRICT: Deny delete or update to parent table

4. NO ACTION: Standard SQL keyword, same as restrict in MySQL

First, create the parent table provinces

Create child table Users1, and SET options cascade

Inserting records ' A ', ' B ', ' C ' in the parent table

In the child table, insert the name ' A1 ', ' A2 ', ' A3 ', ' A4 '

Delete the record with ID 2 in the parent table and view the results of deleting the stepfather table and the child table

In the actual development process, we rarely use physical foreign key constraints, many to use the logical foreign KEY constraints, because the physical foreign key constraints only innodb this engine will support, like our other engine MyISAM engine is not supported, conversely, if I want to create a data table, Assuming that the storage engine is MyISAM, and you want to use foreign key constraints, it is not possible to implement, so we in the actual project development, we do not define the physical foreign key, the so-called logical foreign Key refers to is that we define the structure of the two tables, we are in accordance with the existence of a certain structure of the way to define , but do not use the keyword foreign key to define

Table-level constraints and column-level constraints

Constraints in addition to the function, divided into the above 5 kinds of constraints, but also according to the scope of the table-level constraints and column-level constraints

Constraints established on a data column, called column-level constraints, are constraints on multiple data columns, called table-level constraints. Column-level constraints can be declared either when a column is defined, or after a column definition. Table-level constraints can only be declared after a column definition

In the actual development, with column-level constraints are more, table-level constraints are seldom used, in addition, in all constraints, it is not said that each constraint has a table-level or column-level constraints, where NOT NULL non-null constraints, the default constraint of the two constraints there is no table-level constraints, they have column-level constraints, For the other three types, such as primary key, unique, foreign key, they can all exist table-level and column-level constraints

Column operations

Add a single column

ALTER TABLE tb!_name Add[column] col_name column_definition [first| After Col_name];

Add a single column with three location selections, at the beginning, after the specified column, and at the end

FISRT is added to the front of the entire table, after is placed after the specified column, not filled in the entire table and finally

Add multiple columns

ALTER TABLE tb1_name Add[column] (col_name column_definition,...);

Adding multiple columns can only be in the last

Delete a single column

ALTER TABLE tb1_name DROP [COLUMN] Col_name

Delete multiple columns

ALTER TABLE tb1_name drop col1_name, drop col2_name, ...

Constraint actions

Add a PRIMARY KEY constraint

ALTER TABLE tb1_name ADD [Constraint[symbol]] PRIMARY KEY [Index_type] (Index_col_name,...)

Table Users2 has ' username ', ' pid ' and ' id ' three field before the primary key is added

Add a PRIMARY KEY constraint to the field ' ID '

Add a UNIQUE Constraint

A unique constraint differs from a PRIMARY key constraint in that a unique constraint can have multiple fields, whereas a PRIMARY KEY constraint has only one

ALTER TABLE tb1_name ADD [Constraint[symbol]] UNIQUE [index| KEY] [index_name] [Index_type]

Add a FOREIGN KEY constraint

ALTER TABLE tb1_name ADD [Constraint[symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition

Add or remove a DEFAULT constraint

Alter TABLE Tb1_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

Delete a PRIMARY KEY constraint

ALTER TABLE tbl_name DROP PRIMARY KEY

Remove UNIQUE Constraint

ALTER TABLE tbl_name DROP {index| KEY} index_name

Delete a FOREIGN KEY constraint

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol

After you delete the foreign KEY constraint, you can then delete the index if the index is not

modifying columns

Modifying a column definition

Modifying a column definition refers to the type or location of the modified column

ALTER talbe tb1_name MODIFY [COLUMN] col_name column_definition [first| After Col_name]

The following adjusts the position of the field ' ID ' to the first

Below, convert the type of the field ' ID ' from smallint to tinyint

[note] Data loss can occur when a large range of types is converted to a small range type

Modify column names

Use the following change syntax, which is more powerful than the modify syntax, and you can modify the column type while modifying the column name

ALTER TABLE tb1_name Change [COLUMN] old_col_name new_col_name column_definition [first| After Col_name]

Data table renaming

Method One

ALTER TABLE tb1_name RENAME [To|as] New_tb1_name

Method Two

Use the Rename method to rename multiple data tables

RENAME TABLE tb1_name to New_tbl_name [, tbl_name2 to New_tbl_name2] ...

[note] Do not arbitrarily change the data column and the data table name

Data table operation of the front-end learning Database