本节记录数据表的增删改查
查看数据表
SHOW TABLES
1
2
3
4
5
6
7(root@localhost) [book]> show tables;
+----------------+
| Tables_in_book |
+----------------+
| student |
+----------------+
1 row in set (0.00 sec)DESC table
1
2
3
4
5
6
7
8
9(root@localhost) [book]> desc student;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| stu_id | int | NO | PRI | NULL | auto_increment |
| stu_name | varchar(20) | NO | | NULL | |
| stu_gender | char(1) | YES | | F | |
+------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)SHOW COLUMNS FROM table
1
2
3
4
5
6
7
8
9(root@localhost) [book]> show columns from student;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| stu_id | int | NO | PRI | NULL | auto_increment |
| stu_name | varchar(20) | NO | | NULL | |
| stu_gender | char(1) | YES | | F | |
+------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)SHOW CREATE TABLE table
1
2
3
4
5
6
7
8
9
10
11
12(root@localhost) [book]> show create table student;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`stu_id` int NOT NULL AUTO_INCREMENT,
`stu_name` varchar(20) NOT NULL,
`stu_gender` char(1) DEFAULT 'F',
PRIMARY KEY (`stu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
创建数据表
CREATE TABLE table
1
2
3
4
5
6
7
8
9
10
11(root@localhost) [book]> create table student(
-> stu_id int not null auto_increment primary key,
-> stu_name varchar(20) not null,
-> stu_gender char(1) default 'F');
Query OK, 0 rows affected (0.04 sec)
(root@localhost) [book]> create table if not exists brrow(
-> brrow_id int not null auto_increment,
-> stu_id int not null,
-> primary key(brrow_id)); 主键的另一种写法
Query OK, 0 rows affected (0.03 sec)常用数据类型
1
2
3
4
5
6
7
8
9
10
11
12INT : 整型
FLOAT : 浮点型
DOUBLE : 双精度
DATE : 日期
TIME : 时间
DATETIME : 日期时间
TIMESTAMP : YYYY-MM-DD HH：MM：SS
CHAR(M) : 固定字符型，M个字节 0<=M<=255
VARCHAR(M) : 可变长，L+1个字节
BLOB : 二进制
TEXT : 大型文本
DECIMAL : 高准确度小数数据CREATE TABLE table LIKE table 只复制表结构
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18(root@localhost) [book]> create table if not exists brrow(
-> brrow_id int not null auto_increment,
-> stu_id int not null,
-> primary key(brrow_id));
Query OK, 0 rows affected (0.03 sec)
(root@localhost) [book]> create table student_copy like student;
Query OK, 0 rows affected (0.04 sec)
(root@localhost) [book]> desc student_copy;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| stu_id | int | NO | PRI | NULL | auto_increment |
| stu_name | varchar(20) | NO | | NULL | |
| stu_gender | char(1) | YES | | F | |
+------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)CREATE TABLE table AS SELECT ... FROM table 复制表结构和表内容
1
2
3
4
5
6
7
8
9
10
11
12(root@localhost) [book]> create table brrow_copy as select * from brrow;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
(root@localhost) [book]> desc brrow_copy;
+----------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------+------+-----+---------+-------+
| brrow_id | int | NO | | 0 | |
| stu_id | int | NO | | NULL | |
+----------+------+------+-----+---------+-------+
2 rows in set (0.00 sec)
修改数据表
ALTER TABLE table ADD COLUMN ...
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19(root@localhost) [book]> alter table student
-> add column stu_number char(11), 默认加在最后
-> add column stu_address varchar(50) first, 使用FIRST定位第一
-> add column stu_major varchar(50) after stu_name; 使用AFTER定位
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
(root@localhost) [book]> desc student;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| stu_address | varchar(50) | YES | | NULL | |
| stu_id | int | NO | PRI | NULL | auto_increment |
| stu_name | varchar(20) | NO | | NULL | |
| stu_major | varchar(50) | YES | | NULL | |
| stu_gender | char(1) | YES | | F | |
| stu_number | char(11) | YES | | NULL | |
+-------------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)ALTER TABLE table CHANGE COLUMN old_column new_column_info
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17(root@localhost) [book]> alter table student
-> change column stu_name name varchar(10) not null;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
(root@localhost) [book]> desc student;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| stu_address | varchar(50) | YES | | NULL | |
| stu_id | int | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| stu_major | varchar(50) | YES | | NULL | |
| stu_gender | char(1) | YES | | F | |
| stu_number | char(11) | YES | | NULL | |
+-------------+-------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)ALTER TABLE table ALTER COLUMN column SET DEFAULT default 修改默认值
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17(root@localhost) [book]> alter table student
-> alter column stu_gender set default 'M';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
(root@localhost) [book]> desc student;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| stu_address | varchar(50) | YES | | NULL | |
| stu_id | int | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| stu_major | varchar(50) | YES | | NULL | |
| stu_gender | char(1) | YES | | M | |
| stu_number | char(11) | YES | | NULL | |
+-------------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)ALTER TABLE table MODIFY COLUMN column 修改数据类型,支持first和after
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17(root@localhost) [book]> alter table student
-> modify column stu_major char(100) after stu_gender;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
(root@localhost) [book]> desc student;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| stu_address | varchar(50) | YES | | NULL | |
| stu_id | int | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| stu_gender | char(1) | YES | | M | |
| stu_major | char(100) | YES | | NULL | |
| stu_number | char(11) | YES | | NULL | |
+-------------+-------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)ALTER TABLE table DROP COLUMN column 删除列
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16(root@localhost) [book]> alter table student
-> drop column stu_address;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
(root@localhost) [book]> desc student;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| stu_id | int | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| stu_gender | char(1) | YES | | M | |
| stu_major | char(100) | YES | | NULL | |
| stu_number | char(11) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)ALTER TABLE table RENAME TO new_table_name 重命名数据表
1
2
3
4
5
6
7
8
9
10
11
12
13
14(root@localhost) [book]> alter table student_copy
-> rename to student_backup;
Query OK, 0 rows affected (0.02 sec)
(root@localhost) [book]> show tables;
+----------------+
| Tables_in_book |
+----------------+
| brrow |
| brrow_copy |
| student |
| student_backup |
+----------------+
4 rows in set (0.00 sec)RENAME TABLE table to new_table_name
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24(root@localhost) [book]> show tables;
+----------------+
| Tables_in_book |
+----------------+
| brrow |
| brrow_copy |
| student |
| student_backup |
+----------------+
4 rows in set (0.00 sec)
(root@localhost) [book]> rename table brrow_copy to brrow_backup;
Query OK, 0 rows affected (0.03 sec)
(root@localhost) [book]> show tables;
+----------------+
| Tables_in_book |
+----------------+
| brrow |
| brrow_backup |
| student |
| student_backup |
+----------------+
4 rows in set (0.00 sec)ALTER TABLE table CONVERT TO CHARACTER SET character
1
2
3(root@localhost) [book]> alter table student convert to character set gb2312
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0
删除数据表
- DROP TABLE table
1
2
3
4
5
6
7
8
9
10
11
12(root@localhost) [book]> drop table student_backup;
Query OK, 0 rows affected (0.01 sec)
(root@localhost) [book]> show tables;
+----------------+
| Tables_in_book |
+----------------+
| brrow |
| brrow_backup |
| student |
+----------------+
3 rows in set (0.01 sec)