Data Table operations for front-end learning databases, front-end database data tables

Data Table operations for front-end learning databases, front-end database data tables
* Directory [1] Preparation [2] creating a data table [3] viewing a data table [4] record operations [5] record constraints [6] Column Operations [7] constraint operations [8] modify column [9] Before renaming a data table

Data in mysql databases is stored in database objects called tables. A table is a collection of related data items. It consists of columns (fields) and rows (records. The following describes data table operations in detail.

 

Preparations

Before performing data table operations, You need to log on to the mysql server, create a database, and use the created database

Create a data table

The following table creates a data table tb1 in the db1 database.

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

In the following table tb1, create a variable-length username field of VARCHAR (20); Create a tinyint unsigned one-byte length UNSIGNED (0-255) create a salary FLOAT (8, 2) UNSIGNED number with a total length of 8 and a salary field with 2 decimal places.

View data tables

SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr]

View the column items of a data table

SHOW COLUMNS FORM tbl_name

Record operations

The first step of record operations is to write records.

When writing a record, note that if no field is omitted, the value can be directly assigned in the order of parameters. If any field is omitted, both the field name and parameter value need to be written.

INSERT [INTO] tbl_name [(col_name,...)] VALUES(VAL,...)

Next we will look for records

SELECT EXPR,...FROM tbl_name

Record Constraints

Null Value

The NULL field value can be NULL. The not null field value cannot be blank.

First, create a data table tb2. The username field cannot be blank, and the age field can be blank. If the username field is empty during record insertion, an error is prompted.

Primary Key

Each data table can have only one primary key. The primary key ensures the uniqueness of the record and is not null automatically.

The primary key can be written as a KEY or a primary key.

Automatic ID

AUTO_INCREMENT must be used in combination with the primary key. By default, the start value is 1 and each increment is 1.

Unique

The UNIQUE constraint (unique key) indicates that the selected record cannot have the same value. This ensures the uniqueness of the record. The field with the UNIQUE constraint can be NULL, each data table can have multiple unique constraints.

The following record imposes unique constraints on the username field. After a username is added as 'Tom 'record, it is not allowed to add a record whose username is 'Tom '.

Default Value

When a record is inserted, if the field is not explicitly assigned a value, the DEFAULT value (DEFAULT) is automatically assigned)

Foreign key

The foreign key constraint (foreign key) is used to maintain data consistency and integrity and implement one-to-one or one-to-many relationships.

A foreign key column is a column added with a (foreign key). The reference column of the foreign key column is a reference column. The foreign key column and parameter column must have similar data types. The length of the number or whether the symbols must be the same, and the length of the characters can be different.

An index is a special file. An index on an InnoDB data table is an integral part of a tablespace. It contains reference pointers to all records in the data table. Indexes must be created for foreign key columns and reference columns. If no index exists for the reference column, MySQL automatically creates an index.

A sub-table is a table with a foreign key column. The table referenced by the sub-table is a parent table. The Parent and Child tables must use the same storage engine and temporary tables are not allowed. The data table storage engine can only be InnoDB

Therefore, you must first modify the default storage engine in the mysql configuration file my. ini.

default-storage-engine = INNODB

Create the parent table provices. The reference column is id.

Create a sub-Table users. The foreign key column is pid.

The id of the reference column of the parent table provices is the primary key column. When the primary key is created, the index is automatically created.

Next, we will view the index of the parent table provices.

The following describes the users index of the sub-table.

There are four options in reference operations for foreign key constraints. Used to set whether the Sub-Table performs operations when updating the parent table

1. CASCADE: deletes or updates matched rows from the parent table and automatically deletes or updates the matched rows in the child table.

2. set null: delete or update rows from the parent table, and SET the foreign key column in the child table as NULL. If this option is used, make sure that not null is NOT specified for the sub-table column.

3. RESTRICT: The operation to delete or update the parent table is denied.

4. no action: keyword of standard SQL, which is the same as RESTRICT in MYSQL.

First, create the parent table provinces.

Create a sub-Table users1 and set the option cascade

Insert records 'A', 'B', and 'C' into the parent table'

In the sub-table, insert the names 'a1', 'a2 ', 'a3', and 'a4'

Delete the record with id 2 in the parent table, and view the results of the deleted parent table and child table.

In actual development, we seldom use physical foreign key constraints, many of which use logical foreign key constraints, because physical foreign key constraints are only supported by INNODB engines, for example, the MYISAM engine of another engine is not supported. In other words, if I want to create a data table, assume that the storage engine is MYISAM and want to use foreign key constraints, in fact, it is impossible to implement it. Therefore, in actual project development, we do not define physical Foreign keys, the so-called logical foreign key means that when we define the structure of two tables, we define them according to the existing structure, however, the foreign key keyword is not used for definition.

Table-level constraints and column-level constraints]

In addition to the five types of constraints described above, the constraints can be divided into table-level constraints and column-level constraints according to the function scope.

A constraint created for a data column is a column-level constraint. A constraint created for multiple data columns is a table-level constraint. Column-level constraints can be declared either when a column is defined or after a column is defined. Table-level constraints can only be declared after column Definition

In actual development, there are many column-level constraints and few table-level constraints. In addition, among all the constraints, it does NOT mean that each constraint has a table-level or column-level constraint. not null is NOT a NULL constraint, and DEFAULT constraints do NOT have table-level constraints. They only have column-level constraints, for the other three types, such as primary keys, unique keys, and foreign keys, they can all have 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];

You can add a single column at the starting position, behind the specified column, and at the end of the specified column.

Fisrt is placed at the beginning of the entire table, and after is placed behind the specified column. If not specified, it is placed at the end of the entire table.

Add multiple columns

ALTER TABLE tb1_name ADD[COLUMN] (col_name column_definition,...);

You can add multiple columns only at the end.

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 operation

Add primary key constraints

ALTER TABLE tb1_name ADD [CONSTRAINT[symbol]] PRIMARY KEY [index_type](index_col_name,...)

Before a primary key is added, table users2 has three fields: 'username', 'pid ', and 'id'.

Add a primary key constraint to the field 'id'

Add unique constraint

The difference between the unique constraint and the primary key constraint is that the unique constraint can have multiple fields, and the primary key constraint can have only one

ALTER TABLE tb1_name ADD [CONSTRAINT[symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type]

Add foreign key constraints

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

Add or delete default Constraints

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

Delete primary key constraints

ALTER TABLE tbl_name DROP PRIMARY KEY

Delete unique constraint

ALTER TABLE tbl_name DROP {INDEX|KEY} index_name

Delete foreign key constraints

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol

If no index is required after the foreign key constraint is deleted, you can delete the index.

Modify columns

Modify column Definitions

Modifying a column definition is to modify the column type or position.

ALTER TALBE tb1_name MODIFY [COLUMN] col_name column_definition [FIRST|AFTER col_name]

Next, adjust the position of the field 'id' to the first one.

Next, convert the field 'id' type from smallint to tinyint

[Note] data loss may occur when the data type is converted from a large range to a small range.

Modify column name

The following CHANGE syntax is more powerful than the MODIFY syntax. 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]

Rename a data table

Method 1

ALTER TABLE tb1_name RENAME [TO|AS] new_tb1_name

Method 2

RENAME can be used 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 names of Data columns and data tables.