一,实验要求:
创建数据库,创建英雄表
添加对应的约束条件
插入80条数据
通过alter添加两个新的字段
通过alter删除一个字段
重命名一张表
二,操作步骤:
1,创建数据库,创建英雄表, 添加对应的约束条件
use db_classics
create database db_classics default charset=utf8mb4;
create table t_hero(
id int primary key,
name varchar(100) not null,
nickname varchar(100) not null,
age int default 50,
address text,
job varchar(20),
weapon varchar(20),
group1 char(100)
);
2, 插入80条数据
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(1, "鲁智深", "花和尚", 40, "渭州","劫富济贫·","戎刀","梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(2, "孙二娘", “母夜叉”, 31, '梁山',“打探军情”,“双刀”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(3, "白胜", “白日鼠”, 30, '梁山',“刺探军情”,“大刀”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(4, "郁保四", “险道神”, 50, '陕西',“探路”,"杖刀”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(5, "王定六", “活闪婆”, 31, '山东',“劫富济贫”,“双剑”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(6, "林冲", “豹子头”, 40, '陕西',“劫富济贫”,“花枪”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(7, "吴用", “狗头军师”, 40, '梁山',“出谋划策”,“扇子”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(8, "宋江", “及时雨”, 50, '梁山',“劫富济贫”,“剑”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(9,"卢俊义","玉麒麟", 47, '陕西',“劫富济贫”,“枪”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(10,"关胜", "大刀", 69, '梁山',“劫富济贫”,“大刀”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(11,"花荣","小李广", 47, '陕西',“劫富济贫”,“剑”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(13,"公孙胜","入云龙", 69, '山东',“劫富济贫”,“剑”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(14,"柴进","小旋风", 37, '梁山',“劫富济贫”,“剑”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(15,"李应","扑天雕", 37, '山东',“劫富济贫”,“大刀”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(16,"朱仝","美髯公", 40, '梁山',“劫富济贫”,“剑”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(17,"武松","行者", 46, '梁山',“劫富济贫”,“棍”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(18,"董平","双枪将", 68, '梁山',“劫富济贫”,“双刀”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(19,"张清","没羽箭", 37, '陕西',“劫富济贫”,“剑”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(20,"杨志","青面兽", 46, '广东',“劫富济贫”,“宝剑”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(21,"徐宁","金枪手", 37, '梁山',“劫富济贫”,“剑”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(22,"索超","急先锋", 26, '山东',“劫富济贫”,“流星锤”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(23,"戴宗","神行太保", 46, '梁山',“劫富济贫”,“剑”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(24,"刘唐","赤发鬼", 48, '广东',“劫富济贫”,“枪”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(25,"李逵","黑旋风", 38, '陕西',“劫富济贫”,“剑”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(26,"史进","九纹龙", 27, '梁山',“劫富济贫”,“枪”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(27,"穆弘","没遮拦", 69, '山东',“劫富济贫”,“双刀”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(29,"雷横","插翅虎", 37, '广东',“劫富济贫”,“枪”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(30,"李俊","混江龙", 48, '陕西',“劫富济贫”,“双刀”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(31,"阮小二","立地太岁", 48, '梁山',“劫富济贫”,“剑”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(32,"张横","船火儿",36, '梁山',“劫富济贫”,“剑”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(33,"阮小五","短命二郎",56, '山东',“劫富济贫”,“剑”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(34,"张顺","浪里白条", 57, '梁山',“劫富济贫”,“枪”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(35,"阮小七","活阎罗", 50, '广东',“劫富济贫”,“剑”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(36,"杨雄","病关索", 50, '山东',“劫富济贫”,“流星锤”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(37,"石秀","拼命三郎", 48, '梁山',“劫富济贫”,“枪”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(38,"解珍","两头蛇", 50, '梁山',“劫富济贫”,“双刀”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(39,"解宝","双尾蝎", 46, '山东',“劫富济贫”,“剑”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(40,"燕青","浪 子", 54, '梁山',“劫富济贫”,“枪”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(41,"朱武","神机军师", 53, '梁山',“劫富济贫”,“剑”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(42,"黄信","镇三山", 50, '梁山',“劫富济贫”,“流星锤”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(43,"孙立","病尉迟", 46, '山东',“劫富济贫”,“双刀”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(44,"宣赞","丑郡马", 50, '梁山',“劫富济贫”,“剑”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(45,"郝思文","井木犴", 68, '梁山',“劫富济贫”,“剑”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(46,"韩滔","百胜将", 50, '梁山',“劫富济贫”,“流星锤”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(47,"彭玘","天目将", 48, '广东',“劫富济贫”,“双刀”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(48,"单廷珪","圣水将", 50, '梁山',“劫富济贫”,“剑”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(49,"魏定国","神火将", 35, '山西',“劫富济贫”,“枪”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(50,"萧让","圣手书生", 43, '梁山',“劫富济贫”,“剑”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(51,"裴宣","铁面孔目", 50, '山西',“劫富济贫”,“枪”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(52,"欧鹏","摩云金翅", 46, '梁山',“劫富济贫”,“棍”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(53,"邓飞","火眼狻猊", 50, '梁山',“劫富济贫”,“剑”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(54,"燕顺","锦毛虎", 50, '山西',“劫富济贫”,“枪”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(55,"杨林","锦豹子", 43, '梁山',“劫富济贫”,“剑”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(56,"凌振","轰天雷", 50, '山西',“劫富济贫”,“枪”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(57,"蒋敬","神算子", 37, '广东',“劫富济贫”,“流星锤”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(58,"吕方","小温侯", 50, '梁山',“劫富济贫”,“剑”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(59,"郭盛","赛仁贵", 67, '山西',“劫富济贫”,“棍”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(60,"安道全","神医", 47, '梁山',“劫富济贫”,“剑”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(61,"王英","矮脚虎", 35, '梁山',“劫富济贫”,“双刀”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(62,"扈三娘","一丈青", 37, '梁山',“劫富济贫”,“枪”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(63,"鲍旭","丧门神", 60, '山西',“劫富济贫”,“剑”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(64,"樊瑞","混世魔王", 56, '梁山',“劫富济贫”,“棍”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(65,"孔明","毛头星", 46, '梁山',“劫富济贫”,“剑”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(66,"项充","八臂哪吒", 24, '山西',“劫富济贫”,“剑”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(67,"李衮","飞天大圣", 44, '梁山',“劫富济贫”,“棍”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(68,"金大坚","玉臂匠", 33, '梁山',“劫富济贫”,“流星锤”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(69,"马麟","铁笛仙", 26, '山西',“劫富济贫”,“剑”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(70,"童威","出洞蛟", 36, '梁山',“劫富济贫”,“枪”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(71,"童猛","翻江蜃", 50, '山西',“劫富济贫”,“双刀”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(72,"孟康","玉幡竿", 45, '梁山',“劫富济贫”,“剑”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(73,"侯健","通臂猿", 46, '山西',“劫富济贫”,“棍”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(74,"陈达","跳涧虎", 35, '梁山',“劫富济贫”,“剑”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(75,"杨春","白花蛇", 55, '梁山',“劫富济贫”,“双刀”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(76,"郑天寿","白面郎君", 35, '梁山',“劫富济贫”,“剑”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(77,"宋清","铁扇子", 46, '梁山',“劫富济贫”,“棍”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(78,"乐和","铁叫子", 45, '山西',“劫富济贫”,“剑”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(79,"龚旺","花项虎", 56, '梁山',“劫富济贫”,“棍”,"梁山好汉");
insert into t_hero(id, name,nickname, age, address,job,weapon,group1) values(80,"穆春","小遮拦", 45, '山西',“劫富济贫”,“双刀”,"梁山好汉");
3, 通过alter添加两个新的字段
alter table t_hero add gender enum('男','女') default '男';
alter table t_hero add telephone_number int ;
4, 通过alter删除一个字段
alter table t_hero drop telephone_number;
5,重命名一张表
alter table t_hero rename txy_hero;