《MySQL入门学习》(p15~p17)

p15 DDL_操作表_创建

mysql> use db1;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table student(
    -> id int,
    -> name varchar(20),
    -> age int,
    -> score double(4,1),
    -> birthday date,
    -> insert_time timestamp
    -> );
Query OK, 0 rows affected, 1 warning (1.11 sec)

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| student       |
+---------------+
1 row in set (0.00 sec)

mysql> desc student;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id          | int(11)     | YES  |     | NULL    |       |
| name        | varchar(20) | YES  |     | NULL    |       |
| age         | int(11)     | YES  |     | NULL    |       |
| score       | double(4,1) | YES  |     | NULL    |       |
| birthday    | date        | YES  |     | NULL    |       |
| insert_time | timestamp   | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql>

p16 DDL_操作表_修改

mysql> desc student;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id          | int(11)     | YES  |     | NULL    |       |
| name        | varchar(20) | YES  |     | NULL    |       |
| age         | int(11)     | YES  |     | NULL    |       |
| score       | double(4,1) | YES  |     | NULL    |       |
| birthday    | date        | YES  |     | NULL    |       |
| insert_time | timestamp   | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> alter table student rename to stu;
Query OK, 0 rows affected (0.49 sec)

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| stu           |
+---------------+
1 row in set (0.00 sec)

mysql> show create table stu;
+-------+--------------------------------------------------------------------
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
----------------------------------------------------------------+
| Table | Create Table


                                                                |
+-------+--------------------------------------------------------------------
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
----------------------------------------------------------------+
| stu   | CREATE TABLE `stu` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `score` double(4,1) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `insert_time` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+--------------------------------------------------------------------
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
----------------------------------------------------------------+
1 row in set (0.02 sec)

mysql> alter table stu add gender varchar(10);
Query OK, 0 rows affected (0.52 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stu;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id          | int(11)     | YES  |     | NULL    |       |
| name        | varchar(20) | YES  |     | NULL    |       |
| age         | int(11)     | YES  |     | NULL    |       |
| score       | double(4,1) | YES  |     | NULL    |       |
| birthday    | date        | YES  |     | NULL    |       |
| insert_time | timestamp   | YES  |     | NULL    |       |
| gender      | varchar(10) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
7 rows in set (0.05 sec)

mysql> alter table stu change gender sex varchar(20);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stu;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id          | int(11)     | YES  |     | NULL    |       |
| name        | varchar(20) | YES  |     | NULL    |       |
| age         | int(11)     | YES  |     | NULL    |       |
| score       | double(4,1) | YES  |     | NULL    |       |
| birthday    | date        | YES  |     | NULL    |       |
| insert_time | timestamp   | YES  |     | NULL    |       |
| sex         | varchar(20) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

mysql> alter table stu drop sex;
Query OK, 0 rows affected (0.89 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stu;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id          | int(11)     | YES  |     | NULL    |       |
| name        | varchar(20) | YES  |     | NULL    |       |
| age         | int(11)     | YES  |     | NULL    |       |
| score       | double(4,1) | YES  |     | NULL    |       |
| birthday    | date        | YES  |     | NULL    |       |
| insert_time | timestamp   | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql>

p17 图像化界面工具SQLyog