一.單詞部分 ①constraint約束②foreign外鍵③references參考 ④subquery子查詢⑤inner內部的⑥join連接 二.預習部分 1.修改表SQL語句的關鍵字是什麼 RENAME 修改表名CHANGE修改欄位 2.哪個關鍵字可以按指定行數返回查詢結果集 LIMIT 3. ...
一.單詞部分
①constraint約束②foreign外鍵③references參考
④subquery子查詢⑤inner內部的⑥join連接
二.預習部分
1.修改表SQL語句的關鍵字是什麼
RENAME 修改表名CHANGE修改欄位
2.哪個關鍵字可以按指定行數返回查詢結果集
LIMIT
3.表連接都可以用子查詢替換嗎
可以
三.練習部分
1. 創建數據表，並實現對錶的修改操作
#上機1
USE test;
CREATE TABLE person(
number INT(4) AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(50) NOT NULL,
sex CHAR(2),
bornDate DATETIME
);
ALTER TABLE person RENAME tb_person;
ALTER TABLE tb_person DROP `bornDate`;
ALTER TABLE tb_person ADD bornDate DATETIME;
ALTER TABLE tb_person CHANGE number id BIGINT;
2.使用SQL語句為myschool資料庫中的result表添加約束
#上機2
USE myschool;
ALTER TABLE result ADD CONSTRAINT re PRIMARY KEY result(`studentNo`,`subjectNo`,`exameDate`);
ALTER TABLE result ADD CONSTRAINT fk_result_student FOREIGN KEY (studentNo) REFERENCES student (studentNo);
3.為學生表，科目表，成績表添加數據
#上機3
USE myschool;
INSERT INTO `subject` (`subjectName`, `classHour`, `gradeID`) VALUES
('HTML', '160', '1'),
('Java OOP', '230', '2');
4.修改學生表，科目表數據
#上機4
USE myschool;
INSERT INTO `myschool`.`result` (`studentNo`, `subjectNo`, `exameDate`, `studentResult`) VALUES ('1002', '1', '2014-8-8', '78'),
('1003', '2', '2017-7-7', '98'),
('1004', '1', '2015-8-8', '78'),
('1005', '2', '2013-8-8', '78'),
('1006', '1', '2017-8-8', '66'),
('1007', '1', '2014-8-8', '55'),
('1008', '1', '2014-8-8', '13'),
('1009', '1', '2014-8-8', '89');
UPDATE student SET eamil='[email protected]',loginPwd='000' WHERE studentNo='20000';
UPDATE `subject` SET `classHour`=`classHour`-10 WHERE `classHour`>200 AND `subjectNo`=1;
DROP TABLE IF EXISTS student_grade1;
CREATE TABLE student_grade1(SELECT `studentName`,`sex`,`bornDate`,`phone` FROM student WHERE `gradeID`=1);
5.查詢學生信息（查詢2016年2月17日前五名 的學生的學號和分數）
#上機5
SELECT `studentNo`,`studentResult` FROM `result` WHERE `exameDate`<'2016-2-17' ORDER BY studentResult DESC LIMIT 5;
SELECT studentName,(YEAR(NOW())-YEAR(bornDate)) AS age,bornDate,phone FROM student
WHERE sex='女'
ORDER BY bornDate ASC
LIMIT 1,6;
SELECT YEAR(bornDate) AS nian,COUNT(studentNo) AS num FROM student GROUP BY bornDate HAVING COUNT(studentNo)>=2;
SELECT MAX(`studentResult`),MIN(`studentResult`),AVG(`studentResult`) FROM `result` WHERE `exameDate`='2016-02-17' GROUP BY `studentNo`;
6.查詢指定學生的考試成績
#上機6
SELECT MAX(`studentResult`),MIN(`studentResult`) FROM result
WHERE `exameDate`=(SELECT `exameDate` FROM `result` ORDER BY exameDate DESC LIMIT 1) AND
`subjectNo`=(SELECT `subjectNo` FROM `subject` WHERE `subjectName`='Logic Java');
#select max(exameDate) from result
7.查詢某學期開設的課程
#上機7
SELECT subjectName FROM `subject`
WHERE subjectNo IN(SELECT subjectNo FROM `subject`
WHERE gradeId=(SELECT gradeId FROM grade WHERE gradeName='S1'));
8.查詢某課程最近一次考試缺考的學生名單
#上機8
SELECT `studentName` FROM student WHERE `studentNo` IN(SELECT `studentNo` FROM student WHERE studentNo NOT IN(SELECT `studentNo` FROM `result`
WHERE `subjectNo`=(SELECT `subjectNo` FROM `subject` WHERE `subjectName`='HTML') AND
`exameDate`=(SELECT `exameDate` FROM `result` WHERE subjectNo=(SELECT `subjectNo` FROM `subject` WHERE `subjectName`='HTML')
ORDER BY exameDate DESC LIMIT 1)));
五.總結部分
多表聯查實現的兩種方式：
①表連接
②子查詢
歡迎提問，歡迎指錯，歡迎討論學習信息 有需要的私聊 發佈評論即可 都能回覆的
原文在博客園有需要可以聯繫扣扣：2265682997
