一起学mysql 01.mysql创建、修改和删除表-CSDN博客

本文链接：https://blog.csdn.net/tangwei0928/article/details/96899892

mysql创建、修改和删除表

创建数据库example

mysql> create database example;
Query OK, 1 row affected (0.00 sec)

创建表

创建表example0

mysql> create table example0 (id INT, name VARCHAR(20), sex BOOLEAN);
Query OK, 0 rows affected (0.36 sec)

mysql> desc example0;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| sex   | tinyint(1)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.14 sec)

创建表example1

mysql> create table example1 (stu_id INT PRIMARY KEY, stu_name VARCHAR(20), stu_sex BOOLEAN);
Query OK, 0 rows affected (0.10 sec)

mysql> desc example1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| stu_id   | int(11)     | NO   | PRI | NULL    |       |
| stu_name | varchar(20) | YES  |     | NULL    |       |
| stu_sex  | tinyint(1)  | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

创建表example2

mysql> create table example2 (stu_id INT, course_id INT, grade FLOAT, PRIMARY KEY(stu_id,course_id) );
Query OK, 0 rows affected (0.01 sec)

mysql> desc example2;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| stu_id    | int(11) | NO   | PRI | NULL    |       |
| course_id | int(11) | NO   | PRI | NULL    |       |
| grade     | float   | YES  |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

创建表example3

mysql> create table example3 (id INT PRIMARY KEY, stu_id INT, course_id INT, CONSTRAINT c_fk FOREIGN KEY(stu_id,course_id) REFERENCES example2(stu_id,course_id) );
Query OK, 0 rows affected (0.09 sec)

mysql> desc example3;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| id        | int(11) | NO   | PRI | NULL    |       |
| stu_id    | int(11) | YES  | MUL | NULL    |       |
| course_id | int(11) | YES  |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

创建表example4

mysql> create table example4 (id INT NOT NULL PRIMARY KEY,name VARCHAR(20)NOT NULL , stu_id INT, course_id INT, CONSTRAINT d_fk FOREIGN KEY(stu_id) REFERENCES example1(stu_id) );
Query OK, 0 rows affected (0.35 sec)

mysql> desc example4;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int(11)     | NO   | PRI | NULL    |       |
| name      | varchar(20) | NO   |     | NULL    |       |
| stu_id    | int(11)     | YES  | MUL | NULL    |       |
| course_id | int(11)     | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

创建表example5

mysql>  create table example5 (id INT PRIMARY KEY,stu_id INT UNIQUE, name VARCHAR(20) NOT NULL);
Query OK, 0 rows affected (0.34 sec)

mysql> desc example5;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| stu_id | int(11)     | YES  | UNI | NULL    |       |
| name   | varchar(20) | NO   |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

创建表example6

mysql>  create table example6 (id INT PRIMARY KEY AUTO_INCREMENT,stu_id INT UNIQUE, name VARCHAR(20) NOT NULL);
Query OK, 0 rows affected (0.00 sec)

mysql> desc example6;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| stu_id | int(11)     | YES  | UNI | NULL    |                |
| name   | varchar(20) | NO   |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

创建表example7

mysql>  create table example7 (id INT PRIMARY KEY AUTO_INCREMENT,stu_id INT UNIQUE, name VARCHAR(20) NOT NULL, English VARCHAR(20) DEFAULT 'zero', Math FLOAT DEFAULT 0, Computer FLOAT DEFAULT 0);
Query OK, 0 rows affected (0.33 sec)


mysql> desc example7;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int(11)     | NO   | PRI | NULL    | auto_increment |
| stu_id   | int(11)     | YES  | UNI | NULL    |                |
| name     | varchar(20) | NO   |     | NULL    |                |
| English  | varchar(20) | YES  |     | zero    |                |
| Math     | float       | YES  |     | 0       |                |
| Computer | float       | YES  |     | 0       |                |
+----------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

查看example1 建表语句

mysql> show create table example1 \G
*************************** 1. row ***************************
       Table: example1
Create Table: CREATE TABLE `example1` (
  `stu_id` int(11) NOT NULL,
  `stu_name` varchar(20) DEFAULT NULL,
  `stu_sex` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`stu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

修改表

修改表名

mysql> show tables;
+-------------------+
| Tables_in_example |
+-------------------+
| example0          |
| example1          |
| example2          |
| example3          |
| example4          |
| example5          |
| example6          |
| example7          |
+-------------------+
8 rows in set (0.00 sec)

mysql> alter table example0 rename user;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+-------------------+
| Tables_in_example |
+-------------------+
| example1          |
| example2          |
| example3          |
| example4          |
| example5          |
| example6          |
| example7          |
| user              |
+-------------------+
8 rows in set (0.00 sec)

修改字段的数据类型

mysql> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| sex   | tinyint(1)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table user modify name VARCHAR(30);
Query OK, 0 rows affected (0.45 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| sex   | tinyint(1)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

修改字段名

只修改字段名

mysql> desc example1;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| stu_id   | int(11)     | NO   | PRI | NULL    |       |
| stu_name | varchar(20) | YES  |     | NULL    |       |
| stu_sex  | tinyint(1)  | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table example1 change stu_name name VARCHAR(20);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc example1;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| stu_id  | int(11)     | NO   | PRI | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| stu_sex | tinyint(1)  | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

修改字段名和字段类型

mysql> desc example1;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| stu_id  | int(11)     | NO   | PRI | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| stu_sex | tinyint(1)  | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table example1 change stu_sex sex  int(2);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc example1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| stu_id | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| sex    | int(2)      | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

增加字段

增加无完整性约束条件的字段

mysql> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| sex   | tinyint(1)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table user ADD phone VARCHAR(20);
Query OK, 0 rows affected (0.35 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| sex   | tinyint(1)  | YES  |     | NULL    |       |
| phone | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

增加有完整性约束条件的字段

mysql> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| sex   | tinyint(1)  | YES  |     | NULL    |       |
| phone | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table user add age INT(4) NOT NULL;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| sex   | tinyint(1)  | YES  |     | NULL    |       |
| phone | varchar(20) | YES  |     | NULL    |       |
| age   | int(4)      | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

表的第一个位置增加字段

mysql> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| sex   | tinyint(1)  | YES  |     | NULL    |       |
| phone | varchar(20) | YES  |     | NULL    |       |
| age   | int(4)      | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table user ADD num INT(8) PRIMARY KEY FIRST;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| num   | int(8)      | NO   | PRI | NULL    |       |
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| sex   | tinyint(1)  | YES  |     | NULL    |       |
| phone | varchar(20) | YES  |     | NULL    |       |
| age   | int(4)      | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

删除字段

mysql> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| num   | int(8)      | NO   | PRI | NULL    |       |
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| sex   | tinyint(1)  | YES  |     | NULL    |       |
| phone | varchar(20) | YES  |     | NULL    |       |
| age   | int(4)      | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> alter table user DROP id;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| num   | int(8)      | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| sex   | tinyint(1)  | YES  |     | NULL    |       |
| phone | varchar(20) | YES  |     | NULL    |       |
| age   | int(4)      | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

修改字段位置

字段修改到第一个位置

mysql> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| num   | int(8)      | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| sex   | tinyint(1)  | YES  |     | NULL    |       |
| phone | varchar(20) | YES  |     | NULL    |       |
| age   | int(4)      | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table  user  modify name VARCHAR(30) FIRST;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(30) | YES  |     | NULL    |       |
| num   | int(8)      | NO   | PRI | NULL    |       |
| sex   | tinyint(1)  | YES  |     | NULL    |       |
| phone | varchar(20) | YES  |     | NULL    |       |
| age   | int(4)      | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

字段修改到指定位置

mysql> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(30) | YES  |     | NULL    |       |
| num   | int(8)      | NO   | PRI | NULL    |       |
| sex   | tinyint(1)  | YES  |     | NULL    |       |
| phone | varchar(20) | YES  |     | NULL    |       |
| age   | int(4)      | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table user modify sex TINYINT(1) AFTER age;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(30) | YES  |     | NULL    |       |
| num   | int(8)      | NO   | PRI | NULL    |       |
| phone | varchar(20) | YES  |     | NULL    |       |
| age   | int(4)      | NO   |     | NULL    |       |
| sex   | tinyint(1)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

更改表的存储引擎

mysql> show create table user\G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `name` varchar(30) DEFAULT NULL,
  `num` int(8) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `age` int(4) NOT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table user ENGINE=MyISAM;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table user\G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `name` varchar(30) DEFAULT NULL,
  `num` int(8) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `age` int(4) NOT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`num`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

删除表的外键约束

mysql> show create table example3\G
*************************** 1. row ***************************
       Table: example3
Create Table: CREATE TABLE `example3` (
  `id` int(11) NOT NULL,
  `stu_id` int(11) DEFAULT NULL,
  `course_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c_fk` (`stu_id`,`course_id`),
  CONSTRAINT `c_fk` FOREIGN KEY (`stu_id`, `course_id`) REFERENCES `example2` (`stu_id`, `course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table example3 drop foreign key c_fk;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table example3\G
*************************** 1. row ***************************
       Table: example3
Create Table: CREATE TABLE `example3` (
  `id` int(11) NOT NULL,
  `stu_id` int(11) DEFAULT NULL,
  `course_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c_fk` (`stu_id`,`course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

删除表

删除没有被关联的普通表

mysql> desc example5;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| stu_id | int(11)     | YES  | UNI | NULL    |       |
| name   | varchar(20) | NO   |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> drop table example5;
Query OK, 0 rows affected (0.00 sec)

删除被其他关联表关联的父表

mysql> drop table example1;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
mysql> show create table example4\G
*************************** 1. row ***************************
       Table: example4
Create Table: CREATE TABLE `example4` (
  `id` int(11) NOT NULL,
  `name` varchar(20) NOT NULL,
  `stu_id` int(11) DEFAULT NULL,
  `course_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `d_fk` (`stu_id`),
  CONSTRAINT `d_fk` FOREIGN KEY (`stu_id`) REFERENCES `example1` (`stu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table example4 drop foreign key d_fk;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop  table example1;
Query OK, 0 rows affected (0.00 sec)