Mysql第三次作业

– 创建员工表
CREATE TABLE employee(
e_id INT PRIMARY KEY AUTO_INCREMENT, – 员工ID
e_name VARCHAR(50), – 名称
e_sex VARCHAR(4), – 性别
e_age INT, – 年龄
e_province VARCHAR(50), – 省份
e_city VARCHAR(50), – 城市
e_education VARCHAR(50) – 学历
);

– 添加数据
INSERT INTO employee VALUES(NULL,‘张三’,‘男’,20,‘湖南’,‘长沙’,‘本科’);

– 查询各地区有多少人
SELECT COUNT(),e.e_province FROM employee e GROUP BY e.e_province;

– 查询各地区和学历分布的人口
SELECT COUNT(),e.e_province,e.e_education FROM employee e GROUP BY e.e_education,e.e_province;

– 查询各地区的性别和学历分布人口
SELECT COUNT(),e.e_education,e.e_sex FROM employee e GROUP BY e.e_education,e.e_sex;

– 查询性别分布人口
SELECT COUNT(),e.e_sex FROM employee e GROUP BY e.e_sex;

– 在BBS中添加数据
INSERT INTO BBS VALUES(‘美国’,‘北美洲’,937,30000,1752800);
INSERT INTO BBS VALUES(‘日本’,‘亚洲’,37,12500,484600);
INSERT INTO BBS VALUES(‘德国’,‘欧洲’,35,8146,379400);
INSERT INTO BBS VALUES(‘英国’,‘欧洲’,24,5880,282800);

– 查询BBS表
SELECT * FROM BBS;

– 查询BBS表中的所有国家的平均人口和平均GDP
SELECT AVG(b.BBS_population),AVG(b.BBS_gdp),b.BBS_name FROM BBS b GROUP BY b.BBS_name;

– 查询出人口最少的国家的人数
SELECT b.BBS_population,b.BBS_name FROM BBS b GROUP BY b.BBS_population ORDER BY b.BBS_population LIMIT 0,1;

– 查询出GDP最多的国家的GDP
SELECT b.BBS_name,b.BBS_gdp FROM BBS b GROUP BY b.BBS_gdp ORDER BY b.BBS_gdp DESC LIMIT 0,1;

– 查询出各大洲的国家有几个,人口总数是多少，总GDP是多少
SELECT COUNT(*),b.BBS_region,b.BBS_name FROM BBS b GROUP BY b.BBS_region,b.BBS_name;

– 创建noble2 表
CREATE TABLE noble2(
n_id INT PRIMARY KEY AUTO_INCREMENT, – ID
n_yr VARCHAR(8), – 年份
n_subject VARCHAR(50), – 诺贝尔奖项
n_winner VARCHAR(50), – 得奖者
n_area VARCHAR(50) – 获奖国家
);

– 添加数据
INSERT INTO noble2 VALUES(NULL,‘2000’,‘java’,‘张三’,‘中国’);

– 查询noble2 表
SELECT * FROM noble2;

– 查询出每年颁发的诺贝尔奖的个数
SELECT COUNT(),n.n_yr,n.n_subject FROM noble2 n GROUP BY n.n_yr,n.n_subject;

– 查询出得到诺贝尔奖个数超过2次的年份
SELECT COUNT(),n.n_yr,n.n_subject FROM noble2 n GROUP BY n.n_subject ,n.n_yr HAVING COUNT()>=2;

– 查询出各国,各年份获奖次数,并按获奖次数降序
SELECT COUNT(),n.n_area,n.n_yr FROM noble2 n GROUP BY n.n_subject,n.n_yr ORDER BY n.n_subject ;

– 查询出2002年之后获得奖项的国家
SELECT n.n_area,n.n_subject,n.n_yr FROM noble2 n WHERE n.n_yr>=2002 GROUP BY n.n_yr;