查詢資料準備
-- 查詢練習資料準備
-- 學生表
-- 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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/157499.html
標籤：其他
下一篇：database相關的優化