mysql(windows)第二次作业

1：创建一个数据库：db_classics

mysql> create database db_classics;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db_classics        |
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

2:创建英雄表（）t_hero， 添加对应的约束条件

mysql> create table t_hero(
    -> id int primary key auto_increment,
    -> name varchar(20),
    -> nickname varchar(20),
    -> age varchar(20),
    -> address varchar(20),
    -> job varchar(20),
    -> weapon varchar(20)
    -> );
Query OK, 0 rows affected (0.01 sec)

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

mysql> desc t_hero;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int         | NO   | PRI | NULL    | auto_increment |
| name     | varchar(20) | YES  |     | NULL    |                |
| nickname | varchar(20) | YES  |     | NULL    |                |
| age      | varchar(20) | YES  |     | NULL    |                |
| address  | varchar(20) | YES  |     | NULL    |                |
| job      | varchar(20) | YES  |     | NULL    |                |
| weapon   | varchar(20) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

3：插入80条数据

mysql> insert into t_hero(id,name,nickname,age,address,job,weapon)
    -> values(1,"宋江","呼保义","100","山东","小吏","嘴");
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_hero(id,name,nickname,age,address,job,weapon)
    -> values(2,"晁盖","托塔天王","100","东溪村","英雄","武器");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_hero(name,nickname,age,address,job,weapon)
    -> values("唐僧","唐三藏","100","河南","高僧","嘴");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_hero(name,nickname,age,address,job,weapon)
    -> values("孙悟空","美猴王","100","东胜神州","取经","武器");
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_hero(name,nickname,age,address,job,weapon)
    -> values("猪八戒","悟能","100","天界","取经","武器");
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_hero(name,nickname,age,address,job,weapon)
    -> values("沙悟净","沙僧","100","天庭","取经","武器");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_hero(name,nickname,age,address,job,weapon)
    -> values("白龙马","马","100","西海","马","嘴");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_hero(name,nickname,age,address,job,weapon)
    -> values("林黛玉","潇湘妃子","100","贾府","女主","嘴");
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_hero;
+----+-----------+--------------+------+--------------+--------+--------+
| id | name      | nickname     | age  | address      | job    | weapon |
+----+-----------+--------------+------+--------------+--------+--------+
|  1 | 宋江      | 呼保义       | 100  | 山东         | 小吏   | 嘴     |
|  2 | 晁盖      | 托塔天王     | 100  | 东溪村       | 英雄   | 武器   |
|  3 | 唐僧      | 唐三藏       | 100  | 河南         | 高僧   | 嘴     |
|  4 | 孙悟空    | 美猴王       | 100  | 东胜神州     | 取经   | 武器   |
|  5 | 猪八戒    | 悟能         | 100  | 天界         | 取经   | 武器   |
|  6 | 沙悟净    | 沙僧         | 100  | 天庭         | 取经   | 武器   |
|  7 | 白龙马    | 马           | 100  | 西海         | 马     | 嘴     |
|  8 | 林黛玉    | 潇湘妃子     | 100  | 贾府         | 女主   | 嘴     |
+----+-----------+--------------+------+--------------+--------+--------+
8 rows in set (0.00 sec)

4： 通过alter添加两个新的字段

mysql> alter table t_hero add book varchar(30);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t_hero add sex varchar(30);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t_hero;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int         | NO   | PRI | NULL    | auto_increment |
| name     | varchar(20) | YES  |     | NULL    |                |
| nickname | varchar(20) | YES  |     | NULL    |                |
| age      | varchar(20) | YES  |     | NULL    |                |
| address  | varchar(20) | YES  |     | NULL    |                |
| job      | varchar(20) | YES  |     | NULL    |                |
| weapon   | varchar(20) | YES  |     | NULL    |                |
| book     | varchar(30) | YES  |     | NULL    |                |
| sex      | varchar(30) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+

5：通过alter删除一个字段

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

mysql> desc t_hero;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | int         | NO   | PRI | NULL    | auto_increment |
| name     | varchar(20) | YES  |     | NULL    |                |
| nickname | varchar(20) | YES  |     | NULL    |                |
| age      | varchar(20) | YES  |     | NULL    |                |
| address  | varchar(20) | YES  |     | NULL    |                |
| job      | varchar(20) | YES  |     | NULL    |                |
| weapon   | varchar(20) | YES  |     | NULL    |                |
| book     | varchar(30) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

6： 重命名一张表

mysql> alter table t_hero rename t_rename;
Query OK, 0 rows affected (0.01 sec)

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