/*
学生表(t_student)

create table t_student(
_id int primary key auto_increment,
_name varchar(50), -- 名字
_age int, -- 年龄
_code varchar(50), -- 编号
_sex char(2), -- 性别
_fk int -- 班级编号
);

INSERT INTO t_student(_name,_age,_code,_sex,_fk)
VALUES
(‘凤姐‘,22,‘网络‘,‘女‘,1),
(‘屌丝哥‘,27,‘网络‘,‘男‘,1),
(‘马化腾‘,32,‘IT‘,‘男‘,2),
(‘李咏‘,32,‘央视‘,‘男‘,3),
(‘张朝阳‘,35,‘IT‘,‘男‘,1),
(‘倪萍‘,32,‘央视‘,‘女‘,3),
(‘白岩松‘,32,‘央视‘,‘男‘,2),
(‘崔永元‘,37,‘央视‘,‘男‘,3),
(‘朱军‘,41,‘央视‘,‘男‘,3),
(‘柳传志‘,42,‘IT‘,‘男‘,2),
(‘雷军‘,32,‘IT‘,‘男‘,2),
(‘乔布斯‘,52,‘IT‘,‘男‘,2),
(‘唐骏‘,38,‘IT‘,‘‘,1),
(‘刘强东‘,35,‘IT‘,‘男‘,1),
(‘周鸿祎‘,36,‘网络‘,‘男‘,1);
*/

-- 1、 查询年龄在20 到 36 的学生信息
-- 2、 查询学生的性别为男的人数
-- 3、 学生当中如果有性别为空的，将空值改为男
-- 4、 查询凤姐所在的班级
-- 5、 查询表中有多少个班级
-- 6、 查询每个班级的人数
-- 7、 查询班级中人数最大值为多少
-- 8、 对年龄排序（降序）返回前8条数据
-- 9、 查询一班的平均年龄
-- 10、 查询同凤姐同班级的学生（思考题选作）

use data1;
drop table student;
create table student(
id int primary key auto_increment,
name varchar(50), -- 名字
age int, -- 年龄
code varchar(50), -- 编号
sex char(2), -- 性别
fk int -- 班级编号
);

INSERT INTO student(name,age,code,sex,fk)
VALUES
(‘凤姐‘,22,‘网络‘,‘女‘,1),
(‘屌丝哥‘,27,‘网络‘,‘男‘,1),
(‘马化腾‘,32,‘IT‘,‘男‘,2),
(‘李咏‘,32,‘央视‘,‘男‘,3),
(‘张朝阳‘,35,‘IT‘,‘男‘,1),
(‘倪萍‘,32,‘央视‘,‘女‘,3),
(‘白岩松‘,32,‘央视‘,‘男‘,2),
(‘崔永元‘,37,‘央视‘,‘男‘,3),
(‘朱军‘,41,‘央视‘,‘男‘,3),
(‘柳传志‘,42,‘IT‘,‘男‘,2),
(‘雷军‘,32,‘IT‘,‘男‘,2),
(‘乔布斯‘,52,‘IT‘,‘男‘,2),
(‘唐骏‘,38,‘IT‘,‘‘,1),
(‘刘强东‘,35,‘IT‘,‘男‘,1),
(‘周鸿祎‘,36,‘网络‘,‘男‘,1);

-- select *from student;
-- 1、 查询年龄在20 到 36 的学生信息
-- select name from student where age between 20 and 36;
-- 2、 查询学生的性别为男的人数
-- select count(name) from student where sex=‘男‘;
-- update student set sex=‘男‘ where sex=null;
-- select*from student;
-- 3 学生当中如果有性别为空的，将空值改为男
-- select fk from student where name=‘凤姐‘;
-- 4 查询凤姐所在的班级
-- select count(fk) from student;
-- 5 查询表中有多少个班级
-- 6查询每个班级的人数
-- select count(name),fk from student group by fk;
-- 7查询班级中人数最大值为多少
-- select count(name),fk from student group by fk;
-- 8.对年龄排序（降序）返回前8条数据
-- select age from student order by age desc limit 0,8 ;
-- 9查询一班的平均年龄
-- select avg(age) from student where fk=1;
-- 10、 查询同凤姐同班级的学生
select name from student where fk=1;

use data1;
drop table BBC;
create table BBC(
name varchar(50) not null primary key,
region varchar(60),
area decimal(10),
population decimal(11),
gdp decimal(14)
);
insert into BBC(name,region,population,gdp) values(‘france‘,‘south asia‘,150000000,150000000);
insert into BBC(name,region,population,gdp) values(‘germany‘,‘south asia‘,250000000,250000000);
insert into BBC(name,region,population,gdp) values(‘italy‘,‘south asia‘,350000000,350000000);
insert into BBC(name,region,population,gdp) values(‘china‘,‘Atri‘,550000000,550000000);
insert into BBC(name,region,population,gdp) values(‘Aeicaunited‘,‘Atri‘,250000000,250000000);
insert into BBC(name,region,population,gdp) values(‘japanunited‘,‘Atri‘,12314,50000000);
-- select name from BBC where population>=200000000;
-- select region,gdp from BBC where population>=200000000;
-- select name, round(population/1000000,0)population from BBC where region=‘south asia‘;
-- select name,population from BBC where name=‘france‘;
-- select name,population from BBC where name=‘germany‘;
-- select name,population from BBC where name=‘italy‘;
-- select name from BBC where name like ‘%united%‘;
-- select distinct region from BBC;
-- select name,population from BBC where population>100000000
-- order by population desc;

-- select*from BBC;

实训5.2
use data1;
drop table nobel;
create table nobel(
year int,
subject varchar(15),
winner varchar(50)
);

insert into nobel(year,subject,winner)values(1950,‘物理‘,‘王母‘);
insert into nobel (year,subject,winner)values(1962,‘文学‘,‘可乐‘);
insert into nobel(year,subject,winner)values(1970,‘物理‘,‘流星‘);
insert into nobel (year,subject,winner)values(1980,‘物理‘,‘岁月‘);
insert into nobel(year,subject,winner)values(1990,‘物理‘,‘滔滔‘);
insert into nobel (year,subject,winner)values(2000,‘化学‘,‘花花‘);
insert into nobel (year,subject,winner)values(2010,‘和平‘,‘岁月‘);
insert into nobel(year,subject,winner)values(2011,‘和平‘,‘滔滔‘);
insert into nobel (year,subject,winner)values(2013,‘和平‘,‘花花‘);
-- select winner from nobel where year=1950;
-- select winner from nobel where year=1962 and subject=‘文学‘;
-- select year from nobel where winner=‘流星‘;
-- select winner from nobel where year>=2000;
-- select winner,year from nobel where year between 1980 and 2011;
-- select distinct subject from nobel ;
select winner from nobel where winner like ‘%可%‘;
-- select*from nobel;

简单的sql运用：