mysql第二次作业

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

 

 use db_hero

 create table if not exists t_hero(

id int primary key auto_increment,

name varchar(50) not null unique,

nickname varchar(50) not null unique,

age int check(age>0),

address varchar(20) default "梁山",

weapon varchar(50));

 

 

mysql> insert into t_hero(name,nickname,age,weapon) values("宋江","及时雨","32","混元河双剑");
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_hero(name,nickname,age,weapon) values("卢俊义","玉麒麟","31","麒麟黄金盾");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_hero(name,nickname,age,weapon) values("吴用","智多星","38","孔明宴传羽扇");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_hero(name,nickname,age,weapon) values("公孙胜","入云龙","32","松纹古铜七星剑");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_hero(name,nickname,age,weapon) values("关胜","天勇星","31","青龙偃月刀");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_hero(name,nickname,age,weapon) values("林冲","豹子头","36","丈八蛇矛");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_hero(name,nickname,age,weapon) values("秦明","霹雳火","31","狼牙烽火棒");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_hero(name,nickname,age,weapon) values("呼延灼","天威星","36","乌油七星链");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_hero(name,nickname,age,weapon) values("花荣","小李广","27","雪山飞龙枪");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_hero(name,nickname,age,weapon) values("柴进","小旋风","29","旋风无影剑");

mysql> insert into t_hero(name,nickname,age,weapon) values("李应","扑天雕","32","铁嘴雕头枪");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("朱仝","美髯公","35","九龙朝阳刀");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("鲁智深 ","花和尚","37","疯魔月牙禅");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("武松 ","天伤星","32","青龙白虎双刀");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("董平 ","双枪将","30","双龙戏水枪");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("张清 ","没羽箭","31","凤尾火枪");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("杨志 ","青面兽","33","流星碎岩刀");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("徐宁 ","金枪手","31","金钩闪电枪");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("索超 ","急先锋","28","战神斧");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("戴宗 ","神行太保","28","疾风剑");

Query OK, 1 row affected (0.00 sec)

 mysql> insert into t_hero(name,nickname,age,weapon) values("刘唐 ","赤发鬼","32","阎罗烈火刀");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("李逵 ","黒旋风","32","鬼王斧");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("史进","九纹龙","30","青龙棍");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("穆宏","没遮拦","27","铁背鳌龙刀");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("雷横 ","插翅虎","31","咆哮虎牙刀");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("李俊 ","混江龙","27","七宝蟠龙槊");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values(" 阮小二 ","立地太岁","25","玄铁霸王刀");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values(" 张横 ","船火儿","28","火焰鱼王刀");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values(" 阮小五 "," 短命二郎","24","鳄鱼瓜");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("张顺 "," 浪里白条","26","鱼肠剑");

Query OK, 1 row affected (0.00 sec)

mysql> insert into t_hero(name,nickname,age,weapon) values("阮小七"," 活阎罗","23","龙王刺");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("杨雄"," 病关索","25","冷艳锯齿刀");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("石秀"," 拼命三郎","27","七彩雁翎刀");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("解珍"," 两头蛇","29","解家流双蛇铛");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("解宝 "," 双尾蝎","26","解家流蝎尾叉");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values(" 燕青 "," 天巧星","30","飞燕弩");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("朱武","神机军师","38","妖刀");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("黄信","镇三山","37","镇山鞭");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("孙立","病尉迟","35","乌龙九刃予");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("宣赞 ","丑郡马","33","西域火焰刀");

Query OK, 1 row affected (0.00 sec)

mysql> insert into t_hero(name,nickname,age,weapon) values("郝思文","井木犴","29","七星龙鳞枪");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("韩滔","百胜将","26","灵山枣木槊");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("彭玘","天目将","29","三尖两刃刀");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("单廷圭","圣水将","34","圣光水纹枪");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("魏定国","神火将","26","暗黑神火镰");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("萧让","圣手书生","31","天地如意笔");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("裴宣","铁面孔目","35","生死阴阳剑");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("欧鹏","摩云金翅","35","金顶冲天槊");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("邓飞","火眼狻猊","34","火尖枪");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("燕顺","锦毛虎","36","九齿虎牙刀");

Query OK, 1 row affected (0.00 sec)

mysql> insert into t_hero(name,nickname,age,weapon) values("杨林","锦豹子","36","豹尾霹雳枪");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("凌振","轰天雷","32","风火子母炮");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("蒋敬","神算子","30","铁算盘");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("吕方","小温侯","29","方天画戟");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("郭盛","赛仁贵","31","方天画戟");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("安道全","神医","35","八脉神针");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("皇甫端","紫髯伯","38","镇马鞭");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("王英","矮脚虎","33","如意风雷棍");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("扈三娘","一丈青","26","如意霜刀");

Query OK, 1 row affected (0.01 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("鲍旭","丧门神","32","三尸丧门剑");

Query OK, 1 row affected (0.00 sec)

mysql> insert into t_hero(name,nickname,age,weapon) values("樊瑞","混世魔王","36","冥界魔王剑");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("孔明","毛头星王","31","狼牙风雷锤");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("孔亮","独火星","30","犬牙钩镰刀");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("项充","八臂哪吒","29","百兽金刚盾");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("李衮","飞天大圣","28","秋风剑");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("金大坚","玉臂匠","30","天锤");

Query OK, 1 row affected (0.01 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("马麟","铁笛仙","34","消魂无双笛");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("童威","出洞蛟","30","蛟王刀");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("童猛","翻江蜃","27","蟹王叉刀");

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into t_hero(name,nickname,age,weapon) values("孟康","玉幡竿","27","回旋锚");

Query OK, 1 row affected (0.00 sec)

mysql> insert into t_hero(name,nickname,age,weapon) values("侯健","通臂猿","30","天蚕丝");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_hero(name,nickname,age,weapon) values("陈达","跳涧虎","31","丈八虎矛枪");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_hero(name,nickname,age,weapon) values("杨春","白花蛇","29","碧血白蛇刀");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_hero(name,nickname,age,weapon) values("郑天寿","白面郎君","26","吴钩剑");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_hero(name,nickname,age,weapon) values("陶宗旺","九尾龟","30","神龟铁背锹");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_hero(name,nickname,age,weapon) values("宋清","铁扇子","32","逍遥扇");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_hero(name,nickname,age,weapon) values("乐和","铁叫子","29","玄铁箫");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_hero(name,nickname,age,weapon) values("龚旺","花项虎","29","虎爪刀");
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_hero(name,nickname,age,weapon) values("丁得孙","中箭虎","30","白虎叉");
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_hero(name,nickname,age,weapon) values("穆春","小遮拦","31","翻江蛟龙枪");
Query OK, 1 row affected (0.00 sec)

 

 alter table t_hero add sex varchar(20) default "男";

 alter table t_hero add job varchar(20) default "打架";

update t_hero set sex="女" where id=59;

alter table t_hero drop job;

 alter table t_hero rename t_yingxiong;