Mysql查詢練習

查詢資料準備

-- 查詢練習資料準備

-- 學生表
-- Student
-- 學號
-- 姓名
-- 性別
-- 出生年月日
-- 所在班級
CREATE TABLE Student(
	sno VARCHAR(20) PRIMARY KEY,
	sname VARCHAR(20) not null,
	ssex VARCHAR(10) not null,
	sbirthday datetime,
	class VARCHAR(20)
);

-- 教師表
-- Teacher
-- 教師編號
-- 教師名稱
-- 教師性別
-- 出生年月日
-- 職稱
CREATE TABLE Teacher(
	tno VARCHAR(20) PRIMARY KEY,
	tname VARCHAR(20) not null,
	tsex VARCHAR(10) not null,
	tbirthday datetime,
	prof VARCHAR(20) not null,
	depart VARCHAR(20) not null
);

-- 課程表
-- Course
-- 課程號
-- 課程名稱
-- 教師編號
CREATE TABLE Course(
	cno VARCHAR(20) PRIMARY KEY,
	cname VARCHAR(20) not null,
	tno VARCHAR(20) not null,
	foreign key(tno) references Teacher(tno)
);

-- 成績表
-- Score
-- 學號
-- 課程號
-- 成績
CREATE TABLE Score(
	sno VARCHAR(20) not null,
	cno VARCHAR(20) not null,
	degree decimal,
	foreign key(sno) references Student(sno),
	foreign key(cno) references Course(cno),
	PRIMARY key(sno,cno)
);


-- 新增資料
-- 學生表資料
INSERT INTO student VALUES('101','曾華','男','1977-09-01','95033');
INSERT INTO student VALUES('102','匡明','男','1975-10-02','95031');
INSERT INTO student VALUES('103','王麗','女','1976-01-23','95033');
INSERT INTO student VALUES('104','李軍','男','1976-02-20','95033');
INSERT INTO student VALUES('105','王芳','女','1975-02-10','95031');
INSERT INTO student VALUES('106','陸軍','男','1974-06-03','95031');
INSERT INTO student VALUES('107','王尼瑪','男','1976-02-20','95033');
INSERT INTO student VALUES('108','張全蛋','男','1975-02-10','95031');
INSERT INTO student VALUES('109','趙鐵柱','男','1974-06-03','95031');

-- 教師表資料
INSERT INTO teacher VALUES('804','李誠','男','1958-12-02','副教授','計算機系');
INSERT INTO teacher VALUES('856','張旭','男','1969-03-12','講師','電子工程系');
INSERT INTO teacher VALUES('825','王萍','女','1972-05-05','助教','計算機系');
INSERT INTO teacher VALUES('831','劉冰','女','1977-08-14','助教','電子工程系');
 
-- 新增課程表
INSERT INTO course VALUES('3-105','計算機導論','825');
INSERT INTO course VALUES('3-245','作業系統','804');
INSERT INTO course VALUES('6-166','數位電路','856');
INSERT INTO course VALUES('9-888','高等數學','831');

-- 新增成績表
INSERT INTO score VALUES('103','3-245','86');
INSERT INTO score VALUES('105','3-245','75');
INSERT INTO score VALUES('109','3-245','68');
INSERT INTO score VALUES('103','3-105','92');

INSERT INTO score VALUES('105','3-105','88');
INSERT INTO score VALUES('109','3-105','76');
INSERT INTO score VALUES('103','6-166','85');

INSERT INTO score VALUES('105','6-166','79');
INSERT INTO score VALUES('109','6-166','81');

查詢練習

-- 查詢練習
-- 1.查詢student表中所有的記錄
select * from student;

-- 2.查詢student表中所有記錄的sname,ssex和class列
select sname,ssex,class from student;

-- 3.查詢教師所有的單位但是不重複的depart列
-- distinct 排除重複
select distinct depart from teacher;

-- 4.查詢score表中成績在60-80之間所有的記錄(degree)
select * from score where degree between 60 and 80;

select * from score where degree>60 and degree<80;

-- 5.查詢score表中成績為85, 86, 或者88的記錄(degree)
select * from score where degree in(85,86,88);

-- 6.查詢student表中'95031'班或者性別為'女'的同學記錄
select * from student where class='95031' or ssex='女';

-- 7.以class降序查詢student表中所有的記錄
--升序asc(預設)，降序desc
select * from student order by class desc;

-- 8.以cno升序.degree降序插敘score表中所有的資料
select * from score order by cno asc,degree desc; 

-- 9.查詢'95031'班的學生人數
--統計 count
select count(*) from student where class='95031';

-- 10.查詢score表中的最高分數的學生號和課程號.(子查詢或者排序)
-- (1)找到最高分
-- (2)找最高分sno和cno
select sno,cno from score where degree=(select max(degree) from score);
-- 排序做法
-- limit (從多少開始),(查多少條)
select sno,cno from score order by degree desc limit 0,1;

-- 11.查詢每門課的平均成績
-- avg()
-- select avg(degree) from score where cno='3-105';
-- group by分組
select cno,avg(degree) from score group by cno;

-- 12.查詢score表中至少有2名學生選修的,並且以3開頭的課程的平均分
select cno,avg(degree),count(*) from score group by cno having count(cno)>=2 and cno like '3%';

-- 13.查詢分數大於70但是小於90的sno列
select sno,degree from score where degree>70 and degree<90;
-- between 其實是大於等於和小於等於
select sno,degree from score where degree between 70 and 90;

-- 14.查詢所有的學生 sname , cno, degree列
-- select sno,sname from student;
-- select sno,cno,degree from score;
select sname,cno,degree from student,score where student.sno = score.sno;

-- 15.查詢所有學生的sno, cname, degree列
-- select cno,cname from course;
-- select cno,sno,degree from score;
select sno,cname,degree from course,score where course.cno = score.cno;

-- 16.查詢所有的學生 sname , cname, degree列
-- sname -> student
-- cname -> scoure
-- degree -> score
select sname,cname,degree from student,course,score where student.sno=score.sno and course.cno = score.cno;

-- 17.查詢班級是'95031'班學生每門課的平均分
-- select sno from student where class='95031';
-- select * from score where sno in(select sno from student where class='95031');
select cno,avg(degree) from score where sno in(select sno from student where class='95031') group by cno;

-- 18.查詢選修"3-105"課程的成績高於'109'號同學'3-105'成績 的所有同學的記錄
-- select degree from score where sno='109' and cno='3-105';
select * from score where cno='3-105' and degree>(select degree from score where sno='109' and cno='3-105');

-- 19.查詢成績高於學號為'109',課程號為'3-105'的成績的所有記錄
select * from score where degree>(select degree from score where sno='109' and cno='3-105');

-- 20.查詢所有學號為108、101的同學同年出生的所有學生的sno,sname和sbirthday
-- select year(sbirthday) from student where sno in (108,101);
select sno,sname,sbirthday from student where year(sbirthday) in (select year(sbirthday) from student where sno in (108,101));

-- 21.查詢 "張旭" 教師任課的學生的成績
-- select tno from teacher where tname='張旭';
-- select cno from course where tno=(select tno from teacher where tname='張旭');
select * from score where cno=(select cno from course where tno=(select tno from teacher where tname='張旭'));

-- 22.查詢選修課程的同學人數多於 5 人的教師姓名
-- 為了效果,新增資料:
INSERT INTO score VALUES('101','3-105','90');
INSERT INTO score VALUES('102','3-105','91');
INSERT INTO score VALUES('104','3-105','89');
-- select cno from score group by cno having count(*)>5;
select tname from teacher where tno in(select tno from course where cno in(select cno from score group by cno having count(*)>5));

-- 23.查詢95033班和95031班全體學生的記錄
-- 新增資料
INSERT INTO student VALUES('110','張飛','男','1974-06-03','95038');
select * from student where class in('95031','95033');

-- 24.查詢存在85分以上成績的課程cno
select cno from score where degree>85 group by cno;

-- 25.查出所有'計算機系' 教師所教課程的成績表
-- select * from teacher where depart='計算機系';
-- select * from course where tno in(select tno from teacher where depart='計算機系');
select * from score where cno in (select cno from course where tno in(select tno from teacher where depart='計算機系'));

-- 26.查詢'計算機系'與'電子工程系' 不同職稱的教師的name和prof
-- union求並集
select * from teacher where depart ='計算機系' and prof not in(select prof from teacher where depart='電子工程系')
union
select * from teacher where depart ='電子工程系' and prof not in(select prof from teacher where depart='計算機系');

-- 27, 查詢選修編號為"3-105"課程且成績至少高於選修編號為'3-245'同學的cno,sno和degree,並且按照degree從高到地次序排序
-- any 任意一個,>min也可以
select * from score where cno='3-105' and degree>any(select degree from score where cno='3-245') order by degree desc;

-- 28.查詢選修編號為"3-105"且成績高於選修編號為"3-245"課程的同學cno.sno和degree
-- all 就是所有，>max也可以
select * from score where cno='3-105' and degree>all(select degree from score where cno='3-245');

-- 29. 查詢所有教師和同學的 name ,sex, birthday
-- as取別名，union取並集(這裡第二排預設用第一排別名)
select tname as name,tsex as sex,tbirthday as birthday from teacher
union
select sname,ssex,sbirthday from student;

-- 30.查詢所有'女'教師和'女'學生的name,sex,birthday
select tname as name,tsex as sex,tbirthday as birthday from teacher where tsex = '女'
union
select sname,ssex,sbirthday from student where ssex = '女';

-- 31.查詢成績比該課程平均成績低的同學的成績表
select * from score a where degree< (select avg(degree) from score b where a.cno=b.cno);

-- 32.查詢所有任課教師的tname 和 depart(課程表中安排了課程)
select tname,depart from teacher where tno in(select tno from course);

-- 33.查出至少有2名男生的班號
select class from student where ssex='男' group by class having count(*)>1; 

-- 34.查詢student 表中 不姓"王"的同學的記錄
select * from student where sname not like '王%';

-- 35. 查詢student 中每個學生的姓名和年齡(當前時間 - 出生年份)
-- 當前年份select year(now());
select sname,year(now())-year(sbirthday) as '年齡' from student;

-- 36. 查詢student中最大和最小的 sbirthday的值
-- max與min函數
select max(sbirthday) as 'MAX',min(sbirthday) as 'MIN' from student;

-- 37.以班級號和年齡從大到小的順序查詢student表中的全部記錄
select * from student order by class desc,sbirthday asc;

-- 38.查詢"男"教師 及其所上的課
-- select * from teacher where tsex = '男';
select * from course where tno in (select tno from teacher where tsex = '男');

-- 39.查詢最高分同學的sno cno 和 degree;
-- select max(degree) from score;
select * from score where degree =(select max(degree) from score);

-- 40. 查詢和"李軍"同性別的所有同學的sname
select sname from student where ssex= (select ssex from student where sname='李軍');

-- 41.查詢和"李軍"同性別並且同班的所有同學的sname
select sname from student where ssex= (select ssex from student where sname='李軍') and class=(select class from student where sname='李軍');


-- 42. 查詢所有選修'計算機導論'課程的'男'同學的成績表
-- select * from student where ssex = '男';
-- select * from course where cname ='計算機導論';
select * from score where cno=(select cno from course where cname ='計算機導論') and sno in(select sno from student where ssex = '男');


-- 43. 假設使用了以下命令建立了一個grade表
CREATE TABLE grade(
    low INT(3),
    upp INT(3),
    grade CHAR(1)
);
INSERT INTO grade VALUES(90,100,'A');
INSERT INTO grade VALUES(80,89,'B');
INSERT INTO grade VALUES(70,79,'c');
INSERT INTO grade VALUES(60,69,'D');
INSERT INTO grade VALUES(0,59,'E');

-- 查詢所有同學的sno , cno 和grade列
select sno,cno,grade from score,grade where degree between low and upp;




-- 連線查詢
-- person表 id ,name ,cardId
CREATE TABLE person(
	id int,
	name VARCHAR(20),
	cardId int	
);
-- card表 card,name
CREATE TABLE card(
	id int,
	name VARCHAR(20)
);

INSERT INTO card VALUES (1,'飯卡');
INSERT INTO card VALUES (2,'建行卡');
INSERT INTO card VALUES (3,'農行卡');
INSERT INTO card VALUES (4,'工商卡');
INSERT INTO card VALUES (5,'郵政卡');

INSERT INTO person VALUES (1,'張三',1);
INSERT INTO person VALUES (2,'李四',3);
INSERT INTO person VALUES (3,'王五',6);

-- 內連線(兩張表中資料通過某個欄位相等查詢出相關記錄資料)
-- inner join 或者 join
select * from person inner join card on person.cardId = card.id;

-- 外連線 
-- 1.左連線 left join 或者 left outer join
-- 左邊表裡面的所有資料取出來，右邊表資料如果有則顯示，沒有則顯示NULL
select * from person left join card on person.cardId = card.id;

-- 2.右連線 right join 或者 right outer join
-- 右邊表裡面的所有資料取出來，右邊表資料如果有則顯示，沒有則顯示NULL
select * from person right join card on person.cardId = card.id;

-- 3.完全外連線 full join 或者 full outer join(mysql不支援full join)
-- mysql實現就是左右連線取並集
select * from person left join card on person.cardId = card.id
union
select * from person right join card on person.cardId = card.id;