示例及练习
1-MOSHOU.hero.txt
create database MOSHOU;
use MOSHOU;
create table hero(
id int,
name char(15),
sex enum("男","女"),
country char(10)
);
insert into hero values
(1,"曹操","男","魏国"),
(2,"小乔","女","吴国"),
(3,"诸葛亮","男","蜀国"),
(4,"貂蝉","女","东汉"),
(5,"赵子龙","男","蜀国"),
(6,"张飞","男","蜀国");
table:
+------+-----------+------+---------+
| id | name | sex | country |
+------+-----------+------+---------+
| 1 | 曹操 | 男 | 魏国 |
| 2 | 小乔 | 女 | 吴国 |
| 3 | 诸葛亮 | 男 | 蜀国 |
| 4 | 貂蝉 | 女 | 东汉 |
| 5 | 赵子龙 | 男 | 蜀国 |
| 6 | 张飞 | 男 | 蜀国 |
+------+-----------+------+---------+
1.查找所有蜀国人信息
select * from hero where country="蜀国"
2.查找女英雄的信息,只显示姓名,性别和国家
select name,sex,country from hero where sex="女"
3.把曹操的国籍改为蜀国
update hero set country="蜀国" where name="曹操";
4.把张飞的性别改为女,国籍改为泰国
update hero set sex="女",country="泰国" where name="张飞";
5.把id为2的记录的姓名改为司马懿,性别改为男,国家改为魏国
update hero set name="司马懿",sex="男",country="魏国" where id=2;
6.删除所有泰国人
delete from hero where country="泰国";
7.删除所有的英雄
delete from hero;
==============================================
2-MOSHOU.sanguo.txt
use MOSHOU;
create table sanguo(
id int,
name char(20),
gongji int,
fangyu tinyint unsigned,
sex enum("男","女"),
country varchar(20)
);
insert into sanguo values
(1,"诸葛亮",120,20,"男","蜀国"),
(2,"司马懿",119,25,"男","魏国"),
(3,"关6羽",188,60,"男","蜀国"),
(4,"赵云666",200,66,"男","魏国"),
(5,"8孙权",110,20,"男","吴国"),
(6,"貂蝉",666,10,"女","魏国"),
(7,null,1000,99,"男","蜀国"),
(8,"",1005,88,"女","蜀国");
table:
+------+-----------+--------+--------+------+---------+
| id | name | gongji | fangyu | sex | country |
+------+-----------+--------+--------+------+---------+
| 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 |
| 2 | 司马懿 | 119 | 25 | 男 | 魏国 |
| 3 | 关6羽 | 188 | 60 | 男 | 蜀国 |
| 4 | 赵云666 | 200 | 66 | 男 | 魏国 |
| 5 | 8孙权 | 100 | 60 | 男 | 吴国 |
| 6 | 貂蝉 | 666 | 10 | 女 | 魏国 |
| 7 | NULL | 1000 | 99 | 男 | 蜀国 |
| 8 | | 1005 | 88 | 女 | 蜀国 |
+------+-----------+--------+--------+------+---------+
===========================================================
1.找出攻击力高于150的英雄的名字和攻击力
select name,gongji from sanguo where gongji>150;
2.找出防御力不等于100的英雄信息
select * from sanguo where fangyu!=100;
3.找出攻击力大于200的蜀国英雄的名字和攻击力
select name,gongji from sanguo where country="蜀国" and gongji>200;
4.查找蜀国和魏国的英雄信息
select * from sanguo where country in ("蜀国","魏国");
5.将吴国英雄中攻击值为110的英雄的攻击值设置为100,防御值设置为60
update sanguo set gongji=100,fangyu=60 where country="吴国" and gongji=110;
6.查找攻击值在100-200之间的蜀国英雄信息
select * from sanguo where country="蜀国" and (gongji between 100 and 200);
7.查找除去蜀国和吴国的女英雄信息
select * from sanguo where country not in("蜀国","吴国") and sex="女";
8.查找(id为1,3,5的蜀国英雄和貂蝉) 的编号,姓名,国家
select id,name,country from sanguo where name="貂蝉" or (country="蜀国" and id in (1,3,5) );
9.查找姓名为null的蜀国男英雄信息
select * from sanguo where name is null and country="蜀国" and sex="男";
10.查找姓名为""的英雄的id,姓名,国家
select id,name,country from sanguo where name="";
11.将英雄的按防御值从低到高排序
select * from sanguo order by fangyu;
12.将蜀国英雄按攻击值从高到低排序
select * from sanguo where country="蜀国" order by gongji desc;
13.将魏蜀两国的男英雄中名字为三个字的英雄按防御值升序排列
select * from sanguo where
country in ("魏国","蜀国") and sex="男" and name like "___" order by fangyu;
14.查找防御值第2名到第4名的蜀国英雄记录
select * from sanguo where country="蜀国" order by fangyu desc limit 1,3;
15.查找攻击值前三名且名字不为空值的蜀国英雄的记录
select * from sanguo where country="蜀国" and name is not null order by gongji desc limit 3;
16.攻击力最强值是多少
select max(gongji) as best from sanguo; 用best取代max(gongji)在表格内显示字段名
17.统计id,name两个字段分别有多少条记录
select count(id),count(name) from sanguo; count不统计null,统计""
18.统计蜀国英雄中攻击值大于200的英雄数量
select count(*) as "蜀国攻击力>200的英雄数量" from sanguo where country="蜀国" and gongji>200;
19.统计sanguo表中一共有几个国家
select country from sanguo group by country;
select distinct country from sanguo; distinct的实现
select distinct name,country from sanguo; 这句把name,country当成整体,记录必须完全一样才能去重,distinct后面可以跟n个字段
20.计算所有国家的平均攻击力
select country,avg(gongji) from sanguo group by country;
21.查找所有国家中 英雄数量最多的 前2名国家名称及英雄数量
select country,count(*) as "numbers(英雄)" from sanguo group by country order by count(*) desc limit 2;
22.找出平均攻击力大于105的国家的前2名,显示国家名称和平均攻击力
select country,avg(gongji) as "平均攻击力" from sanguo group by country having avg(gongji)>105 order by avg(gongji) desc limit 2;
23.计算蜀国一共有多少个英雄
select count(distinct name) from sanguo where country="蜀国";
24.查询时显示所有英雄的攻击力 * 10
select name,gongji*10,country from sanguo;
25找出每个国家攻击力最高的英雄的名字和攻击值(子查询)
select name,gongji from sanguo where (country,gongji) in(select country,max(gongji) from sanguo group by country);
==================================================
示例3
table province
+----+--------+-----------+
| id | pid | pname |
+----+--------+-----------+
| 1 | 130000 | 河北省 |
| 2 | 140000 | 陕西省 |
| 3 | 150000 | 四川省 |
| 4 | 160000 | 广东省 |
| 5 | 170000 | 山东省 |
| 6 | 180000 | 湖北省 |
| 7 | 190000 | 河南省 |
| 8 | 200000 | 海南省 |
| 9 | 200001 | 云南省 |
| 10 | 200002 | 山西省 |
+----+--------+-----------+
table city
+----+--------+--------------+--------+
| id | cid | cname | cp_id |
+----+--------+--------------+--------+
| 1 | 131100 | 石家庄市 | 130000 |
| 2 | 131101 | 沧州市 | 130000 |
| 3 | 131102 | 廊坊市 | 130000 |
| 4 | 131103 | 西安市 | 140000 |
| 5 | 131104 | 成都市 | 150000 |
| 6 | 131105 | 重庆市 | 150000 |
| 7 | 131106 | 广州市 | 160000 |
| 8 | 131107 | 济南市 | 170000 |
| 9 | 131108 | 武汉市 | 180000 |
| 10 | 131109 | 郑州市 | 190000 |
| 11 | 131110 | 北京市 | 320000 |
| 12 | 131111 | 天津市 | 320000 |
| 13 | 131112 | 上海市 | 320000 |
| 14 | 131113 | 哈尔滨 | 320001 |
| 15 | 131114 | 雄安新区 | 320002 |
+----+--------+--------------+--------+
table county
+----+--------+--------------+--------+
| id | coid | coname | copid |
+----+--------+--------------+--------+
| 1 | 132100 | 正定县 | 131100 |
| 2 | 132102 | 浦东新区 | 131112 |
| 3 | 132103 | 武昌区 | 131108 |
| 4 | 132104 | 哈哈 | 131115 |
| 5 | 132105 | 安新县 | 131114 |
| 6 | 132106 | 容城县 | 131114 |
| 7 | 132107 | 雄县 | 131114 |
| 8 | 132108 | 嘎嘎 | 131115 |
+----+--------+--------------+--------+
3.1.内连接
1.从省表和市表中提取省名和市名一一对应
select province.pname,city.cname from province
inner join city
on province.pid = city.cp_id; #只显示完全匹配的值
2.从省市县表中提取省市县名一一对应
select province.pname,city.cname,county.coname from province
inner join city on province.pid = city.cp_id
inner join county on county.copid = city.cid; #连接的是前面生成的结果集
3.用左连接以省表为主显示省市详细信息
select province.pname,city.cname from province
left join city on province.pid = city.cp_id; #省表显示所有,匹配的市表有显示,未匹配显示null
4.用左连接以省表为主显示省市县详细信息
select province.pname,city.cname,county.coname from province
left join city on province.pid = city.cp_id
left join county on county.copid = city.cid;
5.显示省市县详细信息,要求县全部显示
select province.pname,city.cname,county.coname from province
left join city on province.pid = city.cp_id
right join county on county.copid = city.cid;
6.显示省市区详细信息,要求 市 全部显示
select province.pname,city.cname,county.coname from province
right join city on province.pid = city.cp_id
left join county on county.copid = city.cid;