Mysql 简单查询语句_MySQL简单的查询语句

一、建立teacher和student表

create table teacher(

tid int unsigned auto_increment primary key,

tname varchar(10),

tdesc varchar(10)

)auto_increment = 1;

insert into teacher values(null,'关老师','音乐教授'),(null,'张老师','数学教授'),(null,'李老师','语文教授'),(null,'刘老师','英语教授'),(null,'王老师','计算机教授');

select * from teacher;

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

create table student(

sid int unsigned auto_increment primary key,

sname varchar(10),

sbirthday char(8),

sdept varchar(10),

stid int unsigned,

sscore int unsigned,

constraint tsfk foreign key(stid) references teacher(tid) on delete set null on update cascade        --添加外键

) auto_increment = 15100;

insert into student values(null,'成龙','19660912','计算机科学与技术','1','100'),(null,'李连杰','19630112','美术','3','90'),(null,'刘德华','19611111','互联网工程','2','99'),

(null,'李成林','19660912','数学','4','90'),(null,'张成龙','19850912','计算机科学与技术','1','90'),(null,'刘龙','19770923','计算机科学与技术','4','92'),

(null,'李成龙','19660912','英语','5','91'),(null,'张居正','19550912','数学','2','99'),(null,'王龙','19970616','互联网工程','1','67'),

(null,'李成功','19660912','语文','1','89'),(null,'张居','19620312','音乐','3','100'),(null,'成飞龙','19690928','美术','2','94'),

(null,'李树林','19660912','美术','2','70'),(null,'张正','19630712','美术','4','88'),(null,'关龙','19540113','数学','4','83'),

(null,'李森林','19660912','计算机科学与技术','3','60'),(null,'张良','19621112','物理','5','77'),(null,'关羽','19780827','语文','3','96'),

(null,'李林','19660912','音乐','4','100'),(null,'张飞','19220912','计算机科学与技术','5','89'),(null,'刘备','19790718','计算机科学与技术','1','100'),

(null,'李成','19660912','计算机科学与技术','5','80');

select * from student;

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-- select查询语句

select sid ,sname ,sdept from student;

-- 列别名

select sid 学号,sname 姓名,sdept 专业 from student;

-- 表别名

select s.sid from student s;

-- 查询条件

-- 普通条件 = < > <= >= != <> and or not

select * from student where sdept = '计算机科学与技术' and sscore >= 70;

select * from student where sdept <> '计算机科学与技术';

-- between and not between and

select * from student where sbirthday between '19660101' and '19970909';

select * from student where sbirthday not between '19660101' and '19970909';

-- in()  not in()

select * from student where sdept in('美术','数学');

select * from student where sdept not in ('美术','数学');

-- is null is not null查询是否为空

select * from student where sscore is null;

-- 模糊查询 like _一个任意字符 %0个或多个任意字符

select * from student where sname like '李%';

select *from student where sname like '__';-- 查询名字是两个字的姓名

-- 查询排序

select sscore from student order by sscore asc;

select sname 姓名,ifnull(round(sscore),0) 分数 from student order by sscore desc;

-- 限制结果行 limit 分页使用

select * from student order by sscore asc limit 5;

select * from student order by sscore desc limit 0,3;

-- 分组查询

select distinct sdept from student ;

select count(distinct sdept) from student;

select sdept 专业,count(*) 人数,max(sscore) 最高分,min(sscore) 最低分,avg(sscore) 平均分 from student

group by sdept;

-- where 查询条件 having 是分组条件在 group by 后

select sdept , count(*), max(sscore),min(sscore),avg(sscore)

from student where true

group by sdept having avg(sscore)<80 order by avg(sscore) desc limit 5;

-- 查询显示等级

select sid 学号,sname 姓名,sdept 专业,sscore 成绩,if(sscore>=90,'优秀',if(sscore>=80,'良好',if(sscore>=60,'及格','补考'))) 等级

from student;

-- 子查询

select t.dj 等级,count(*) 人数

from

(select sid,sname,sdept,sscore,if(sscore>=90,'优秀',if(sscore>=80,'良好',if(sscore>=60,'及格','补考'))) dj

from student

) t

group by t.dj;

-- 错误

select * from student where sscore = max(sscore);

-- 利用子查询，实现结果

select * from student where sscore = (select max(sscore) from student);

----------------------------------------------------------------------------------------------------------------------------------------------

-- 高级查询 连接查询

select * from student a,student b;

-- 请查询 学号 姓名 成绩 代课老师名称

select s.sid,s.sname,t.tname

from student s join teacher t on t.`tid` = s.`stid`;

-- select s.sid,s.sname,t.tname from student s left join teacher t on t.tid = s.stid