MySQL高階查詢之理解與使用實例

一.單字部份

①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 Date 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#EY (studentNo) REFENNoudent; 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='stu200000@163.com',loginPwd='000' WHERE studentNo='20000';

UPDATE `subject` SET `classHour`=`classHour`-10 WHERE `classHour`> =1;
DROP TABLE IF EXISTS student_grade1;
CREATE TABLE student_grade1(SELECT `studentName`,`sex`,`bornDate`,`phone` FROM student WHERE `gradeID`=1);
#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 HCOUNT(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' );
#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(SROMECT `exameDate` FROM `result` WHERE subjectNo`(SROMECT `subject` WHERE `subjectName`='HTML')

ORDER BY exameDate DESC LIMIT 1)));

五.總結部分######多表聯查實作的兩種方式：######①表連接######②子查詢###

以上是MySQL高階查詢之理解與使用實例的詳細內容。更多資訊請關注PHP中文網其他相關文章！