模糊查詢
先準(zhǔn)備數(shù)據(jù) 接著用昨天的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);
-- 查詢姓名中帶有王字?jǐn)?shù)據(jù)
select * from heroInfo where heroName like "%王%";
--查詢種族為龍的所有數(shù)據(jù)
sleect * from heroInfo where heroRace like "龍族"
-- 查詢姓名是以王結(jié)尾，并且王字之前有且只能有兩個(gè)字符
-- _表示匹配一個(gè)字符
select * from heroInfo where heroName like "__王";
-- 刪除數(shù)據(jù)表里面的所有數(shù)據(jù)行
delete from heroInfo;
-- 刪除指定ID的數(shù)據(jù)行
delete from heroInfo where heroId = 1;
-- 獲取最大值
select max(stuLevel) as "最大等級(jí)" from heroInfo;
-- 獲取最小值
select min(heroLevel) as "最低等級(jí)" from heroInfo;
-- 平均值
select avg(heroAge) as "平均年齡" from heroInfo;
-- 獲取總數(shù)
select count(*) as "總數(shù)" from heroInfo;
等級(jí)大于30的數(shù)量
select count(*) as "總數(shù)" from heroInfo where heroLevel > 30;
--修改英雄的等級(jí)
update heroInfo set heroLevel = 10 where heroName = "古爾丹";
-- 修改數(shù)據(jù) heroId為2的數(shù)據(jù)
update heroInfo set heroRace = '不詳', heroAge = 28, heroLevel = 77
where heroId = 2;
二，聯(lián)表
首先新建兩張表?
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
);
往里面插入數(shù)據(jù)
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("疾風(fēng)劍豪",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進(jìn)行匹配，展示對(duì)應(yīng)的數(shù)據(jù)
select * from hero, equip where hero.equipID = equip.equipID;
一對(duì)多
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 = "李嘉誠";
-- 查詢一下何鴻燊的兒子都是誰，這里使用了別名來簡(jiǎn)化書寫
select * from father f inner join son s on s.fID = f.fID
where fName = "何鴻燊";
-- 一對(duì)多：父子關(guān)系，用戶和訂單關(guān)系，商鋪和商品
多對(duì)多
create table student (
????????stuID int,
????????stuName char(30)
);
create table course(
????????cID int,
????????cName char(30)
);
create table stuToCourse(
????????scID int, # 中間表的ID號(hào)
????????stuID int, # 學(xué)生ID
????????cID int? # 課程ID
);
插入數(shù)據(jù)
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);
? ? ? ?————————- 看一下王六學(xué)了什么—————————
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 = "王六";
? ?————————- 看一下李四學(xué)了什么—————————
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;
