MySQL第二次作业

创建数据库，创建英雄表
添加对应的约束条件
插入80条数据
通过alter添加两个新的字段
通过alter删除一个字段
重命名一张表

#创建数据库

create database db_classics;

#进入数据库

use db_classics;

#创建数据库

create table t_hero(

id int primary key auto_increment,

name varchar(50) not null unique,

nickname varchar(100),

age int check(age>=0),

address text,

job varchar(100),

weapon varchar(50),

organization varchar(100)

)engine=innodb;

#插入80条数据

insert into t_hero values(1,"张百川","玉皇大帝",22680000,"九重天","领导","丹书琥","天庭");

insert into t_hero values(2,"孙悟空","斗战胜佛",342,"花果山水帘洞","保镖","金箍棒",null);

insert into t_hero values(3,"唐僧","唐三藏",45,"长安弘福寺","取经",null,"唐朝");

insert into t_hero values(4,"猪八戒","天蓬元帅",235,"高老庄","保镖","九齿钉耙","天庭");

insert into t_hero values(5,"沙僧","卷帘大将",9000,"流沙河","保镖","降妖宝杖","天庭")

insert into t_hero values(6,"哪吒","哪吒三太子",7,"陈塘关",null,"乾坤圈",null);

insert into t_hero values(7,"林黛玉","潇湘妃子",17,"潇湘院",null,null,null);

insert into t_hero values(8,"贾宝玉","怡红公子",18,"怡红院",null,null,null);

insert into t_hero values(9,"薛宝钗","蘅芜君",20,"蘅芜苑",null,null,null)

insert into t_hero values(10,"王熙凤","破落户",24,"荣国府","贾府大管家",null,null);

insert into t_hero values(11,"贾元春",null,30,"皇宫","妃子",null,"贾府")；

insert into t_hero values(12,"贾迎春","二木头",19,"缀锦楼",null,null,null);

insert into t_hero values(13,"晴雯",null,17,"怡红院","丫鬟",null,null);

insert into t_hero values(14,"平儿",null,21,"荣国府","陪房丫头",null,"凤姐");

insert into t_hero values(15,"袭人",null,20,"怡红院","丫鬟",null,null);

insert into t_hero values(16,"贾琏",null,28,"荣国府","同知",null,"贾府");

insert into t_hero values(17,"贾母",null,83,"荣国府",null,null,"贾府");

insert into t_hero values(18,"林如海",null,40,"苏州","巡盐御史",null,"皇帝");

insert into t_hero values(19,"周泰",null,42,"九江下蔡","车前校尉","纯钢的长槊","吴");

insert into t_hero values(20,"孙策","小霸王",26,"寿春","骑都尉","一杆枪","吴");

insert into t_hero values(21,"孙权","紫髯儿",19,"吴郡富春县","讨虏将军","六柄宝剑","吴");

insert into t_hero values(22,"周瑜","美周郎",26,"卢江郡舒县","都督","剑","吴");

insert into t_hero values(23,"宋江","及时雨",35,"梁山","首领","朴刀","梁山");

insert into t_hero values(24,"林冲","豹子头",41,"梁山","五虎将","丈八蛇矛","梁山");

insert into t_hero values(25,"李逵","黑旋风",35,"梁山","步军头领","两柄板斧","梁山");

insert into t_hero values(26,"鲁智深","花和尚",45,"梁山","步军总大将","水磨禅杖","梁山");

insert into t_hero values(27,"卢俊义","玉麒麟",33,"梁山","掌管机密军师","麒麟黄金矛","梁山");

insert into t_hero values(28,"武松","行者",30,"梁山","步军头领","雪花镔铁戒刀","梁山");

insert into t_hero values(29,"柴进","小旋风",41,"梁山","掌管钱粮头领","旋风无影剑","梁山");

insert into t_hero values(30,"张青","菜园子",39,"梁山","打探声息","朴刀","梁山");

insert into t_hero values(31,"晁盖","托塔天王",46,"梁山","保正","朴刀","梁山");

insert into t_hero values(32,"吴用","智多星",43,"梁山","军师","铜链","梁山");

insert into t_hero values(33,"孔亮","独火星",28,"梁山","中军守护营步兵队副队长","犬牙钩镰刀","梁山");

insert into t_hero values(34,"史进","九纹龙",27,"梁山","史进在梁山的职务","青龙棍","梁山");

insert into t_hero values(35,"燕青","浪子",25,"梁山","步军头领","弩","梁山");

insert into t_hero values(36,"呼延灼","双鞭",40,"平江府海盐","御营兵马指挥使","雌雄虎眼鞭","朝廷");

#添加两个新的字段

alter table t_hero add sex enum('男','女') default '男';

mysql> alter table t_hero add tel char(20);

#删除一个字段

alter table t_hero drop tel;

#重命名一张表

rename table t_hero to hero;