创建数据表
drop table if exists students;
create table students (
studentNo varchar(10) primary key,
name varchar(10),
sex varchar(1),
hometown varchar(20),
age tinyint(4),
class varchar(10),
card varchar(20)
)
准备数据
insert into students values
('001', '王昭君', '女', '北京', '20', '1班', '121516488645487551'),
('002', '诸葛亮', '男', '上海', '18', '2班', '121516488645487551'),
('003', '张飞', '男', '南京', '24', '3班', '121516488645487551'),
('004', '白起', '男', '安徽', '22', '4班', '121516488645487551'),
('005', '大乔', '女', '天津', '19', '3班', '121516488645487551'),
('006', '孙尚香', '女', '河北', '18', '1班', '121516488645487551'),
('007', '百里玄策', '男', '山西', '20', '2班', '121516488645487551'),
('008', '小乔', '女', '河南', '15', '3班', null),
('009', '百里守约', '男', '湖南', '21', '1班', ''),
('010', '妲己', '女', '广东', '26', '2班', '121516488645487551'),
('011', '李白', '男', '北京', '30', '4班', '121516488645487551'),
('012', '孙膑', '男', '*', '26', '3班', '340322199000297655')
例：查询白学生
select * from students where name like '%白%'
例：查询李白学生
select ** from students where name like *
例：查询小乔的年龄
select age from students where name='小乔'*
例：查询20岁以下的学生
select * from students where age<20
例：查询家乡不在北京的学生
select * from students where hometown!='北京'
例：年龄小于20的女同学
select * from students where age<20 and sex='女'
例：查询家乡是北京或者上海的学生
select * from students where class='1班' or hometown in('北京','上海')
例：查询家乡是北京或上海的学生
select * from students where hometown in('北京','上海')
例：查询年龄为18至20的学生
select * from students where age between 18 and 20
例：查询所有学生信息，按年龄从大到小排序，年龄相同时，再按学号从小到大排序
select * from students order by age desc,studentNo
select * from students order by convert(name using gbk)
例：查询女生的最大年龄
select max(age) from students where sex='女';
例：查询北京学生的年龄总和
select sum(age) from students where hometown='北京';
例：查询女生的平均年龄
select avg(age) from students where sex='女'
例：查询各种性别的人数
select sex,count(*) from students group by sex
例：查询男生总人数
方案一 select count(*) from students where sex='男'
例：查询各种性别的人数
select sex,count(*)from students group by sex
例：查询学生李白的基本信息
select * from students where name ='李白'
例：查询所有学生的信息
select * from students
创建数据表
名人表
drop table if exists celebrity;
create table celebrity(
sid varchar(10) primary key,
sname varchar(10),
ssex varchar(1),
sage int(4)
)
准备数据
insert into students values
('1', '李白', '男', '34' ),
('2', '杜甫', '男', '24'),
('3', '白居易', '男', '31'),
('4', '李商隐', '女', '40'),
('5', '苏轼', '男', '26'),
('6', '辛弃疾', '男', '22')
作品表
drop table if exists works;
create table works(
wid varchar(10) primary key,
wwork varchar(10),
wdynasty varchar(10)
)
准备数据
insert into students values
('1', '将进酒','唐代'),
('2', '蜀道难','唐代'),
('3', '夜雨寄北','唐代'),
('4', '静夜思','唐代'),
('5', '望岳','唐代'),
('6', '钱塘湖春行','南宋'),
('7', '念奴娇赤壁怀古','北宋'),
('8', '水调歌头','唐代')
名人表
drop table if exists summary;
create table summary(
sid varchar(10) primary key,
wid int,
sassess varchar(20)
)
准备数据
insert into students values
('1', '1','天生我材必有用'),
('1', '2','危乎高哉),
('6', '8',"明月几时有'),
('3', '6','乱花渐欲迷人眼'),
('4', '3','却话巴山夜雨时'),
('5', '7','大江东去浪淘金'),
('2', '5','一览纵山小'),
('1', '4','举头望明月')
1.查询任意表中性别是男的作者
select sname from celebrity where ssex='男'
2.查询静夜思的作者
select sname from works inner join celebrity on works.wid=celebrity.sid where='静夜思'
3.查询李白和杜甫的年纪
select sage,sname from celebrity where sname='李白' or sname='杜甫'
4.查询名人表中男女人数
select ssex,count(*) from celebrity group by ssex
5.查询白居易写的作品名称和对于的名句
select sname,wwonk,sassess from summary inner ioin works on works.wid= summary.wid join celebrity on ummary.wid=celebrity.sid where celebrity.sname ='白居易'
6.查询年纪在25到30岁之间的个数
select sage,count(*) from celebrity where sage between 25 and 30;
7.查询名人表中最后两条的数据信息
select * from celebrity order by sid desc limit 2;
8.查询李白的作品名称,名句和年龄
select sname,wwork,sassess from summary inner join works on works.wid = summary.wid inner join celebrity on ummary.sid = celebrity.sid where celebrity.sname ='李白'
9.将作品为望岳的朝代修改为北宋
update works set wdynasty ='北宋' where wwork ='望岳'
10.新增名人表中的一个作者 王维 ,25 ,男
insert into celebrity values (7,'王维','男',25 )