Table Operations
Creating Tables
Syntax:
CREATE TABLE table_name (
field1 datatype,
field2 datatype,
field3 datatype
) character set charset collate collation engine storage_engine;
Explanation:
- field represents column name
- datatype represents column type
- character set charset, if not specified, uses the database's character set
- collate collation, if not specified, uses the database's collation
Table Creation Example
create table users (
id int,
name varchar(20) comment 'username',
password char(32) comment 'password is 32-digit md5 value',
birthday date comment 'birthday'
) character set utf8 engine MyISAM;
Explanation:
- Different storage engines create different table files. The users table uses MyISAM engine, which creates three separate files in the data directory:
- users.frm: table structure
- users.MYD: table data
- users.MYI: table index
InnoDB Storage Directory
To create a database with InnoDB engine and observe the storage directory:
- First, log in to MySQL and execute the following comamnds to create a database (InnoDB is the default engine in MySQL 5.5+, but can be explicitly specified):
-- Create database with specified character set (engine defaults or table-level specified)
mysql> create database innodb_demo character set utf8mb4 collate utf8mb4_general_ci;
-- Switch to the database
mysql> use innodb_demo;
-- Create a table explicitly specifying InnoDB engine
mysql> create table test_innodb(
-> id int primary key auto_increment,
-> name varchar(50)
-> )engine=InnoDB;
Query OK, 0 rows affected (0.04 sec)
- Find the MySQL data storage directory. InnoDB database files are typically stored in the MySQL data directory, which can be queried with:
Method 1: Using MySQL command
mysql> show variables like 'datadir';
Execution returns similar results (path varies by system):
mysql> show variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| datadir | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.06 sec)
- View InnoDB database storage files. Enter the queried datadir directory (requires system admin privileges) and view files for the innodb_demo database:
# Switch to data directory (replace with your actual path)
cd /var/lib/mysql/
# View database directory
ls -ld innodb_demo/
Enter the database directory to view files:
cd innodb_demo/
ls -l
InnoDB storage file explanation:
- Table structure file: test_innodb.frm (stores table structure definition, merged into .ibd file in MySQL 8.0+)
- Table data and index file: test_innodb.ibd (InnoDB independent tablespace file containing data and index)
- Database directory: innodb_demo/ folder itself organizes all table files for that database
- Additional informatino
- InnoDB defaults to "independent tablespace" (one .ibd file per table), configuration can be checked with the innodb_file_per_table variable:
SHOW VARIABLES LIKE 'innodb_file_per_table'; -- Usually ON
- If using "system tablespace", data is stored in ibdata1 file under datadir (not recommended,不利于管理).
Viewing Table Structure
desc table_name;
Example:
mysql> desc users;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| password | char(32) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| assets | varchar(100) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
Modifying Tables
In actual project development, it's often necessary to modify a table's structure, such as field names, field sizes, field types, table character sets, table storage engines, etc. We may also need to add or delete fields. This requires table modification.
ALTER TABLE tablename ADD (column datatype [DEFAULT expr][,column datatype]...);
ALTER TABLE tablename MODIFY (column datatype [DEFAULT expr][,column datatype]...);
ALTER TABLE tablename DROP (column);
Case: Add two records to the users table
mysql> insert into users values(1,'a','b','1982-01-04'),(2,'b','c','1984-01-04');
mysql> select * from users;
+------+------+----------+------------+
| id | name | password | birthday |
+------+------+----------+------------+
| 1 | a | b | 1982-01-04 |
| 2 | b | c | 1984-01-04 |
+------+------+----------+------------+
2 rows in set (0.00 sec)
Add a field to the users table for storing image paths
mysql> alter table users add assets varchar(100) comment 'image path' after birthday;
mysql> alter table users modify name varchar(60);
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> desc users;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(60) | YES | | NULL | |
| password | char(32) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| assets | varchar(100) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
After inserting a new field, data in the original table is unaffected:
mysql> select * from users;
+------+------+----------+------------+
| id | name | password | birthday |
+------+------+----------+------------+
| 1 | a | b | 1982-01-04 |
| 2 | b | c | 1984-01-04 |
+------+------+----------+------------+
2 rows in set (0.00 sec)
- Modify name, changing its length to 60
mysql> alter table users modify name varchar(60);
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> desc users;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(60) | YES | | NULL | |
| password | char(32) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| assets | varchar(100) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
- Delete the password column. Note: Be careful when deleting fields, as both the field and its corresponding data will be permanently removed
mysql> alter table users drop password;
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> desc users;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(60) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| assets | varchar(100) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
- The 'to' keyword can be omitted
- Change the name column to xingming
mysql> alter table employee change name xingming varchar(60);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc employee;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| xingming | varchar(60) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| assets | varchar(100) | YES | | NULL | |
| password | char(20) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
Dropping Tables
Syntax format:
DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...
Example:
drop table t1;