MYSQL table operations

When a library is created, tables need to be created next. To create a table, you need to know not only the syntax, but also the column types, indexes, etc. The following will only talk about the syntax for creating tables, and will not describe the column types.

Build a table

Build a table syntax:

CREATE TABLE [IF NOT EXISTS] 表名 (
    字段名1 列类型 [属性] [索引],
    字段名2 列类型 [属性] [索引],
    字段名3 列类型 [属性] [索引],
    ......
) [表类型] [表字符集]

There is a point to note here, the field name should not be the same as the key of mysql If you really want to do this, you need to add ` symbols before and after the field name. This symbol is above the tab key.

Now let's create a user table with fields: user ID, user name, user password, mobile phone number, gender, and birthday.

create table if not exists users(
  user_id int(10) unsigned auto_increment primary key,
  username varchar(16) not null default '' collate utf8mb4_bin comment '用户名',
  userpass char(32) not null collate utf8mb4_bin default '',
  mobile char(11) not null default '' unique,
  gender enum('未知', '男', '女') default '未知',
  birth date not null default '1900-01-01',
  index username(username)
) engine=innodb default charset utf8mb4 collate utf8mb4_general_ci;

Usually, we will create a unique identification field for each table, here is user_id, to facilitate future operations. Collate is set for both username and userpass fields here. This is because these two fields are different from others in that they are case-sensitive. In addition, we created two indexes for the table, namely the mobile field and the username field. A unique index is set for mobile, which means that the mobile phone number cannot be repeated. Creating a general index for username is to speed up searches through username.

View the table and table structure

After the table is created, we want to see if it is really created successfully. You can view all tables under the current library through show tables.

mysql> show tables;
+---------------+
| Tables_in_job |
+---------------+
| users      |
+---------------+
1 row in set

The table is indeed generated successfully, but if you want to see what fields and attributes are in the table, you can check it through desc table name.

Clear and delete tables

Note: Deleting a table is a dangerous operation, so operate with caution!

Delete table syntax: DROP TABLE [IF EXISTS] Table name

No demonstration here.

Clear table syntax: TRUNCATE table name

Here we will focus on the difference between truncate users and delete from users.

• truncate is equivalent to deleting the table first and then re-creating the table. All data in the table has been reset.

• While delete only deletes the data of the table, some attribute information of the table, such as the auto-incremented id, will not be reset.

Modify the table

Finally, let’s take a look at how to modify the table. It is placed last because its syntax is the most complicated and it is the most difficult part of table operations. Its syntax is as follows:

ALTER TABLE 数据表名 alter_spec[,alter_spec] ... table options

I have organized common operations. The common syntax and functions are as follows:

• Add new fields: ALTER TABLE table name ADD field name [FISRT|ALTER column name]

• Modify fields: ALTER TABLE table name change|modify list Note: Modify and change The difference is that modify can only modify the column type, while change can change the column name in addition to modifying the column type.

• Delete field: ... DROP column name

• Add index name: ... ADD INDEX [INDEX_NAME] (index_col1,index_col2, ...)

• Delete index: ...DROP INDEX INDEX_NAME

• Delete primary key: ...DROP PRIMARY KEY

• Add primary key: ... ADD PRIMARY KEY (INDEX_COL1,INDEX_COL2,...)

• Add unique index: ... ADD UNIQUE [index_name] (index_col1,index_col2,...)

• Modify the table name: RENAME newName

Let’s practice it

First, add a new field email and place it after userpass.

ALTER TABLE users ADD email VARCHAR(255) NOT NULL DEFAULT '' AFTER userpass;

Modify userpass and change the length to 64 bits

ALTER TABLE users MODIFY userpass CHAR(64) NOT NULL DEFAULT '' COMMENT '用户登录密码';

Modify userpass and change it to auth

ALTER TABLE users CHANGE userpass `auth` char(32) NOT NULL DEFAULT '';

Add a normal index to email

ALTER TABLE users ADD INDEX eamil(email);

Delete email index

ALTER TABLE users DROP INDEX eamil;

Deleting a unique index is the same as deleting a normal index

ALTER TABLE users DROP INDEX mobile;

Add a unique index

ALTER TABLE users ADD UNIQUE mobile(mobile);
或
ALTER TABLE users ADD UNIQUE (mobile);

To delete the primary key, you need to delete aoto_increment before deleting;

ALTER TABLE users MODIFY user_id INT(10) NOT NULL;
ALTER TABLE users DROP PRIMARY KEY;

Add primary key

ALTER TABLE users ADD PRIMARY KEY (user_id);

The above just describes some syntax of table operations, about column types and indexes, etc. If you are interested, you can read the relevant information.

The above is the detailed content of MYSQL table operations. For more information, please follow other related articles on the PHP Chinese website!