mysql数据库(2)
模糊查询
先准备数据 接着用昨天的heroInfo表
insert into heroInfo values(5, "麦迪文", '人族', 38, 29);
insert into heroInfo values(6, "山丘之王", '矮人', 28, 39);
insert into heroInfo values(7, "潜行者", '矮人', 22, 39);
insert into heroInfo values(8, "伊瑟拉", '龙族', 00, 34);
insert into heroInfo values(9, "死亡之翼", '龙族', 00, 44);
insert into heroInfo values(10, "巫妖王", '兽族', 98, 49);
insert into heroInfo values(11, "伊利丹", '兽族', 28, 49);
insert into heroInfo values(12, "熊猫人", '不详', 33, 50);
-- 查询姓名中带有王字数据
select * from heroInfo where heroName like "%王%";
--查询种族为龙的所有数据
sleect * from heroInfo where heroRace like "龙族"
-- 查询姓名是以王结尾，并且王字之前有且只能有两个字符
-- _表示匹配一个字符
select * from heroInfo where heroName like "__王";
-- 删除数据表里面的所有数据行
delete from heroInfo;
-- 删除指定ID的数据行
delete from heroInfo where heroId = 1;
-- 获取最大值
select max(stuLevel) as "最大等级" from heroInfo;
-- 获取最小值
select min(heroLevel) as "最低等级" from heroInfo;
-- 平均值
select avg(heroAge) as "平均年龄" from heroInfo;
-- 获取总数
select count(*) as "总数" from heroInfo;
等级大于30的数量
select count(*) as "总数" from heroInfo where heroLevel > 30;
--修改英雄的等级
update heroInfo set heroLevel = 10 where heroName = "古尔丹";
-- 修改数据 heroId为2的数据
update heroInfo set heroRace = '不详', heroAge = 28, heroLevel = 77
where heroId = 2;
二，联表
首先新建两张表
create table hero(
heroID int(4) not null primary key auto_increment,
heroName char(30),
herolevel tinyint,
equipID int # 英雄装备的id
);
create table equip(
equipID int(4) not null primary key auto_increment,
equipName char(30),
equipMoney tinyint
);
往里面插入数据
insert into hero(heroName, heroLevel, equipID) values("无极剑圣", 2, 1);
insert into hero(heroName, heroLevel, equipID) values("无双剑姬",5, 2);
insert into hero(heroName, heroLevel, equipID) values("暗裔剑魔",6, 3);
insert into hero(heroName, heroLevel, equipID) values("疾风剑豪",11, 4);
insert into equip(equipName, equipMoney) values('无尽', 90);
insert into equip(equipName, equipMoney) values('黑切', 30);
insert into equip(equipName, equipMoney) values('破败', 11);
insert into equip(equipName, equipMoney) values('电刃', 88);
-- 连表查询 用在英雄表中的英雄ID和武器表中的武器ID进行匹配，展示对应的数据
select * from hero, equip where hero.equipID = equip.equipID;
一对多
create table father(
fID int,
fName char(30)
);
create table son(
sID int,
sName char(30),
fID int
);
insert into father values(1, "何鸿燊");
insert into father values(2, "李嘉诚");
insert into son values(1, "何酋君", 1);
insert into son values(2, "何酋亨", 1);
insert into son values(3, "李泽楷", 2);
insert into son values(4, "李泽钜", 2);
-- 查询一下李嘉诚的儿子都是谁
select * from father inner join son on son.fID = father.fID
where fName = "李嘉诚";
-- 查询一下何鸿燊的儿子都是谁，这里使用了别名来简化书写
select * from father f inner join son s on s.fID = f.fID
where fName = "何鸿燊";
-- 一对多：父子关系，用户和订单关系，商铺和商品
多对多
create table student (
stuID int,
stuName char(30)
);
create table course(
cID int,
cName char(30)
);
create table stuToCourse(
scID int, # 中间表的ID号
stuID int, # 学生ID
cID int # 课程ID
);
插入数据
insert into student values(1, "张三");
insert into student values(2, "李四");
insert into student values(3, "赵五");
insert into student values(4, "王六");
insert into course values(1, "Java从入门到放弃");
insert into course values(2, "C语言从入门到出家");
insert into course values(3, "MySQL从删库到跑路");
insert into course values(4, "C++从入门到跳楼");
insert into course values(5, "PHP是世界上最好的语言");
insert into stuToCourse values(1, 1, 1);
insert into stuToCourse values(2, 1, 3);
insert into stuToCourse values(3, 1, 2);
insert into stuToCourse values(4, 2, 3);
insert into stuToCourse values(5, 2, 4);
insert into stuToCourse values(6, 2, 5);
insert into stuToCourse values(7, 3, 1);
insert into stuToCourse values(8, 3, 3);
insert into stuToCourse values(9, 3, 5);
insert into stuToCourse values(10, 4, 2);
insert into stuToCourse values(11, 4, 1);
insert into stuToCourse values(12, 4, 3);
————————- 看一下王六学了什么—————————
select * from student s
inner join stuToCourse sc on s.stuID = sc.stuID
inner join course c on sc.cID = c.cID
where s.stuName = "王六";
————————- 看一下李四学了什么—————————
select * from student s
inner join stuToCourse sc on s.stuID = sc.stuID
inner join course c on sc.cID = c.cID
where s.stuName = "李四";
—————————— 能否看一下C语言从入门到出家————————————
select * from course c
inner join stuToCourse sc on c.cId = sc.cID
inner join student s on sc.stuId = s.stuID
where c.cName = "C语言从入门到出家";
select * from course c
—————————— MySQL从删库到跑路————————————
inner join stuToCourse sc on c.cId = sc.cID
inner join student s on sc.stuId = s.stuID
where c.cName = "MySQL从删库到跑路";
update stuToCourse set cID = 4 where scID = 3;
