MySQL簡單的查詢語句

標籤：between   avg   sig   table   order by   查詢條件   cad   音樂   外鍵   

 

一、建立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

 

MySQL簡單的查詢語句