Table is the main form of data storage in database. It is composed of rows and columns, which is similar to common spreadsheet.
The biggest difference between tables in MySQL and other databases is that they can use different storage engines. Storage Engine is a component used to manage, access and modify physical data in MySQL. Different storage engines provide different functions and characteristics.
Starting from MySQL 5.5, InnoDB storage engine is used by default; Support transaction processing (ACID), row level locking, fault recovery, multi version concurrency control (MVCC) and foreign key constraints. In most cases, the default InnoDB storage engine is recommended.
4.1 creating tables
The CREATE TABLE statement is used to create tables. The basic syntax is as follows:
CREATE TABLE [IF NOT EXISTS] table_name( column1 data_type column_constraint, column2 data_type, ..., table_constraints ) ENGINE=storage_engine;
Where, table_name specifies the name of the new table. The table name must be unique in the database. If the table already exists, the system will prompt an error; At this time, you can use the IF NOT EXISTS option to check whether a table with the same name already exists before creation to avoid error messages.
Then, specify the field name, data type and field constraint in parentheses; Multiple fields are separated by commas. Common data types include numbers (INT, NUMERIC, etc.), characters (VARCHAR, CHAR, etc.) and DATE and TIME (DATE, TIME, etc.).
Field constraints include NOT NULL constraints, UNIQUE constraints, PRIMARY KEY constraints, FOREIGN KEY constraints, CHECK constraints, and DEFAULT values. At the end of the field definition, you can define the constraints of the table. Table level constraints include UNIQUE constraints, PRIMARY KEY constraints, FOREIGN KEY constraints, and CHECK constraints.
π For a detailed introduction to MySQL constraints, please refer to This article.
Finally, use the ENGINE option to specify a storage ENGINE for the table. For example, InnoDB, MyISAM, MEMORY, ARCHIVE, etc. if not specified, it defaults to InnoDB.
π Use show engines; Command to view the storage engines supported by the current MySQL database.
The following statement is used to create a user table named users:
CREATE TABLE users( user_id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) UNIQUE NOT NULL, password VARCHAR(50) NOT NULL, email VARCHAR(256) NOT NULL, status TINYINT NOT NULL, created_on TIMESTAMP NOT NULL, last_login TIMESTAMP, CONSTRAINT uk_email UNIQUE (email) );
Where, user_id is an automatically growing integer and is the primary key of the table. AUTO_INCREMENT indicates the automatic growth column. When inserting data, you do not need to provide the value of this field. MySQL automatically generates a numeric sequence value starting from 1. Each table can only have one automatically growing field.
Username is a variable length string field with a maximum length of 50; UNIQUE defines a UNIQUE constraint, which means that username cannot have duplicate values; NOT NULL defines a non NULL constraint, which means that username cannot be null.
password and email are variable length string fields and cannot be empty. status is a very small number. The value range of TINYINT is - 128 to 127.
created_on and last_login is a TIMESTAMP field. TIMESTAMP includes year, month, day, hour, minute and second.
uk_email is a unique constraint, which means that email cannot have duplicate values.
After executing the above statement to create the users table, you can use the DESC command to view the table structure:
mysql> DESC users; +------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | user_id | int(11) | NO | PRI | NULL | auto_increment | | username | varchar(50) | NO | UNI | NULL | | | password | varchar(50) | NO | | NULL | | | email | varchar(256) | NO | UNI | NULL | | | status | tinyint(4) | NO | | NULL | | | created_on | timestamp | NO | | NULL | | | last_login | timestamp | YES | | NULL | | +------------+--------------+------+-----+---------+----------------+ 7 rows in set (0.00 sec)
π Use show create table users; Command to view the definition statement of the users table.
The following statement creates two new tables, roles and user_roles:
CREATE TABLE roles(
role_id INT AUTO_INCREMENT PRIMARY KEY,
role_name VARCHAR(256) UNIQUE NOT NULL
);
CREATE TABLE user_role
(
user_id INT NOT NULL,
role_id INT NOT NULL,
grant_on TIMESTAMP NOT NULL,
PRIMARY KEY (user_id, role_id),
CONSTRAINT fk_user_role_userid FOREIGN KEY (user_id)
REFERENCES users(user_id),
CONSTRAINT fk_user_role_roleid FOREIGN KEY (role_id)
REFERENCES roles(role_id)
);The roles table is used to define roles, which is relatively simple. user_role is the role information corresponding to the user; PRIMARY KEY defines a composite PRIMARY KEY. In this case, only table level primary keys can be used; In addition, two foreign keys are defined, which refer to the PRIMARY KEY fields of the users and roles tables respectively. The structure diagram of these three tables is as follows:
4.2 modification table
MySQL supports ALTER TABLE statement, which is used to modify the definition of a table. Common modification operations include adding columns, modifying columns, renaming columns, deleting columns, and renaming tables.
4.2.1 add column
The alter table... Add... Statement is used to add a new field to the table:
ALTER TABLE table_name ADD column_name data_type column_constraint
[ FIRST | AFTER other_column];The options for adding fields are the same as those for creating tables; FIRST indicates that the newly added field is the FIRST field in the table. AFTER indicates that a new field is added AFTER a field. By default, a new field is added at the end of the table.
The following statement adds a field login to the users table_ failed_ Times, indicating the number of login password errors:
ALTER TABLE users ADD login_failed_times TINYINT NOT NULL DEFAULT 0;
π MySQL supports adding multiple fields at a time, separated by commas, similar to defining multiple fields when creating a table.
4.2.2 modify column
The alter table... Modify... Statement can modify the properties of an existing field:
ALTER TABLE table_name MODIFY column_name data_type column_constraint
[ FIRST | AFTER other_column]; The options for modifying fields are the same as those for adding new fields. The following statement modifies the length of the email field in the users table to 512:
ALTER TABLE users MODIFY email VARCHAR(512) NOT NULL;
π MySQL supports modifying multiple fields at a time, separated by commas, similar to adding multiple fields.
4.2.3 heavy life
The alter table... Rename... Statement modifies the name of a field:
ALTER TABLE table_name RENAME COLUMN old_name TO new_name;
The following statement will the login of the users table_ failed_ Rename the times field to login_failed_number:
ALTER TABLE users RENAME COLUMN login_failed_times TO login_failed_number;
In addition, the alter table... Change... Statement can modify the name and properties of the field at the same time:
ALTER TABLE table_name CHANGE COLUMN old_name new_name data_type column_constraint
[FIRST | AFTER column_name];4.2.4 delete columns
The alter table... Drop... Statement is used to delete fields in a table:
ALTER TABLE table_name DROP COLUMN column_name;
The following statement is used to delete the login of the users table_ failed_ Number field:
ALTER TABLE users DROP COLUMN login_failed_number;
π MySQL supports deleting multiple fields at a time and separating multiple drop columns with commas.
If a foreign key constraint exists on the deleted field, you need to delete the foreign key constraint first and then delete the field, otherwise an error will be generated:
mysql> alter table user_role drop column user_id; ERROR 1828 (HY000): Cannot drop column 'user_id': needed in a foreign key constraint 'fk_user_role_userid'
4.2.5 rename table
The alter table... Rename... Statement modifies the name of the table:
ALTER TABLE table_name RENAME [TO|AS] new_nameοΌ
The following statement renames the users table to accounts and the user_ Rename the role table to account_role:
ALTER TABLE users RENAME TO accounts; ALTER TABLE user_role RENAME AS account_role;
In addition, MySQL also supports RENAME TABLE statement to rename the table:
RENAME TABLE table_name TO new_name;
4.3 delete table
DROP TABLE statement is used to delete an existing table. The syntax is as follows:
DROP TABLE [IF EXISTS] table_name [, table_name] ...;
DROP TABLE will delete the data in the table at the same time. When deleting multiple tables, use commas to separate them; If the IF EXISTS option is specified, there is no error when deleting a table that does not exist. Deleting a table does not delete access rights on the table.
The following statement is used to delete the roles table:
mysql> drop table roles; ERROR 3730 (HY000): Cannot drop table 'roles' referenced by a foreign key constraint 'fk_user_role_roleid' on table 'account_role'.
Because roles is account_ The parent table of the role table cannot be deleted directly. You can delete the account first_ Role, or delete both tables at the same time:
mysql> drop table roles, account_role; Query OK, 0 rows affected (0.06 sec)