firefox mysql_mysql的示例及练习

示例及练习

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;