使用Java實現資料庫編程—03 第三章 高級查詢（一）

（1）修改表名語法：

ALTER TABLE <舊表名> RENAME [ TO ] <新表名>；

註意：其中[TO]為可選參數，使用與否不影響結果，僅修改表名，表結構不變；

（2）添加欄位語法：

ALTER TABLE 表名 ADD 欄位名 數據類型 [屬性]；

（3）修改欄位語法：

ALTER TABLE 表名 CHANGE 原欄位名 新欄位名 數據類型 [屬性]；

其中，”原欄位名“指修改前的欄位名，“ 新欄位名”指修改後的欄位名，“數據類型”指修改後的數據類型，

如果不需要修改數據類型，則和原數據類型保持一致，但“數據類型”不能為空。

註意：由於不同的類型的數據儲存方式和長度不同，修改數據類型可能會影響數據表中已有的數據，因此，此種情況下不應輕易修改數據類型。

（4）刪除欄位語法：

ALTER TABLE 表名 DROP欄位名；

Eg：

DROP TABLE IF EXISTS `demo01`;

#創建表

CREATE TABLE  `demo01`(

       `id` INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,

       `name` VARCHAR(8) NOT NULL

);

#修改表名

ALTER TABLE `demo01` RENAME `demo02`;

#添加欄位

ALTER TABLE ` demo02 ` ADD `password` VARCHAR(32) NOT NULL;

#修改欄位

ALTERTABLE ` demo02 ` CHANGE `name` `username`CHAR(10) NOT NULL;

#刪除欄位

ALTERTABLE ` demo02 ` DROP `password`;

（1）添加主鍵約束語法：

ALTER TABLE 表名 ADD CONSTRAINT 主鍵名 PRIMARY KEY 表名（主鍵欄位）;

Eg：設置grade表中gradeId欄位為主鍵

ALTER TABLE `grade` ADD CONSTRAINT `pk_grade` PRIMARY KEY `grade`(`gradeId`);

（2）添加外鍵約束語法：

ALTER TABLE 表名 ADD CONSTRAINT 外鍵名 FOREIGN KEY （外鍵欄位） REFE RENCES 關聯表名（關聯欄位）；

Eg：將student表的gradeId欄位和grade表的gradeId欄位建立外鍵關聯：

ALTER TABLE `student`  ADD  CONSTRAINT fk_student_grade FOREIGN KEY(`gradeId`) REFERENCES `grade` (`gradeId`);

提示：在MySQL中，InnoDB儲存類型的表支持外鍵，

MyISAM儲存類型的表不支持外鍵，因此對於MySISAM儲存類型的表，也可以通過建立邏輯關聯的方式保證數據的完整性和一致性，

這種關聯實際上是依照設計之初就定義好的固有邏輯，按照這種固有邏輯存取數據即可，不需要在資料庫層面進行“硬綁定”。

（1）插入數據記錄：

（A）插入單行數據記錄語法：

        INSERT INTO 表名 [（欄位名列表）] VALUES（值列表）;

        Eg:

           INSERT INTO `student`(`loginPwd`,`studentName`,`gradeId`,`phone`,`bornDate`)

           VALUES('123','黃小平',1,'13956799999','1996-5-8');

其中：

1.表的欄位名是可選的，如果省略，則依次插入所有欄位。

2.多個列表和多個值之間使用逗號分隔。

3.值列表必須和欄位名列表數量相同，且數據類型相符。

4.如果插入的是表中部分數據，欄位名列表必須填寫。

 （B）插入多行數據語法：在MySQL中INSERT語句支持一次插入多條記錄，插入時可指定多個值列表，每個值列表之間逗號隔開。

          INSERT INTO 新表（欄位名列表）VALUES（值列表1）,（值列表2）,……（值列表n）；

          註意:插入數據時需要指定具體欄位名。

         Eg：

           INSERT INTO `subject`(`subjectNo`,`subjectName`,`classHour`,`gradeID`)

           VALUES(1,'Logic Java',220,1),(2,'HTML',160,1),(3,'Java OOP',230,2);

          註意：為避免表結構發生變化引發的錯誤，建議插入數據時寫明具體欄位名！

          插入時間類型也需要 ‘ ’ 。

（2）將查詢結果插入到新表語法：

        CREATE TABLE 新表（SELECT 欄位1，欄位2，..........FROM 原表）；

         註意：新表無需事先創建，如新表已存在，將會報錯！

（3）更新數據記錄語法：

         UPDATE 表名 SET 欄位1=值，欄位2=值，。。。。欄位n=值[WHERE 條件]；

（4）刪除數據記錄語法：

         DELETE FROM 表名 [WHERE 條件]；

          除此之外，也可以使用TRUNCATE語句刪除表中所有行。語法：

          TRUNCATE TABLE 表名；

          註意：TRUNCATE 語句實現刪除表中所有數據，刪除後將重置自增列，表結構及其欄位、約束、索引保持不變執行速度比DELETE語句快。

SQL Sever資料庫與MySQL資料庫把數據插入新表語法對比：

SQL Service中的數據查詢語法：

SELECT<欄位名列表>

FRMO<表名或視圖>

[WHERE <查詢條件>]

[GROUP BY<分組欄位名>]

[HAVING<條件>]

[OGDER BY<排序的欄位名>[ASC 或 DESC]]

Eg：

SELECT `studentNo`,`studentName`,`phone`,`address`,`bornDate`

FROM `student`

WHERE `gradeId` = 1

ORDER BY studentNo;

其中：

1.欄位名列表為要查詢的欄位名、各欄位之間使用逗號分隔、若查詢表中所有欄位、則使用“* ”號表示。

2.FROM後的表名為要查詢的數據的來源，可以單個可以多個。

3.WHERE子句為可選項，指定查詢的條件。

4.GROUP BY子句表明查詢出來的數據按指定欄位進行分組。

5.HAVING子句用於篩選組。

6.OGDER BY子句指定按什麼順序顯示查詢出來的數據，什麼欄位的升序（ASC）或降序（DESC）

SELECT  <欄位名列表>

FROM  <表名或視圖>

[WHERE  <查詢條件>]

[GROUP BY <分組的欄位名>]

[ORDER BY  <排序的列名> [ASC 或DESC]]

[LIMIT [位置偏移量, ]行數];

其中：

1. 位子偏移量：指從結果集中第幾條數據開始顯示（第一條記錄的位置偏移量是：0，第二條記錄的位置偏移量是：1，............），

此參數可選，當省略是從第一條記錄開始顯示。

2.行數：指顯示記錄的條數。

聚合函數：

AVG（）； 作用：返回某欄位的平均值。

COUNT（）； 作用：返回某欄位的行數。

MAX（）； 作用：返回某欄位的最大值。

MIN（）； 作用：返回某欄位的最小值。

SUM（）； 作用：返回某欄位的和。

數學函數：

CEIL（x）；作用：返回大於或等於數值 x 的最小整數。

舉例：SELECT CEIL （2.3）；返回：3

FLOOR（x）；作用：返回小於或等於數值 x 的最大整數

舉例：SELECT FLOOR（2.3）；返回：2

RAND（）；作用：返回0~1的隨機數。

舉例：SELECT RAND（）；返回：0.59464161

CONCAT（str1,str2,...,strln）；作用：鏈接字元串str1,str2.....strln為一個完整的字元串。

舉例：SELECT CONCAT（"My"，"S"，"QL")；   返回：MySQl

INSERT（str,pos,len,newstr）；作用：將字元串 str 從 pos 位置開始，len 個字元長的子串替換為字元串 newstr

舉例：SELECT INSERT（'這是SQL Server 資料庫'，3，10，'MySQL'）；  返回：這是MySQl資料庫

LOWER（str）;作用：將字元串 str 中所有字元變為小寫

舉例：SELECT LOWER('MySQL');  返回：mysql

UPPER（str）;作用：將字元串 str 中所有字元變為大寫

舉例：SELECT UPPER('MySQL');   返回：MYSQL

SUBSTRING（str，num，len）;作用：返回字元串 str 的第num 個位置開始長度為len 的子字元串

舉例：SELECT SUBSTRING('JavaMySQLOracle'，5，5);  返回：MySQL

CURDATE（）；作用：獲取當前日期

舉例：SELECT CURDTE（）；   返回：2016-08-08

CURTIME（）；作用：獲取當前時間

舉例：SELECT CURTIME（）；  返回：19：19：26

NOW（）；作用：獲取當前日期和時間

舉例：SELECT NOW（）；  返回：2016-08-08 19：19：26

WEEK（date）；作用：返回日期date為一年中的第幾周

舉例：SELECT WEEK（NOW（））；   返回：26

YEAR（date）；作用：返回日期date的年份

舉例：SELECT YEAR（NOW（））； 返回：2016

HOUR（time）；作用：返回時間按 time 的小時值

舉例：SELECT HOUR（NOW（））；返回：9

MINUTE（time）；作用：返回時間 time 的分鐘值

舉例：SELECT MINUTE（NOW（））； 返回：43

DATEDIFF（date1,date2）；

作用：返回日期參數 date1 和 date2 之間相隔的天數。

舉例：SELECT DATEDIFF（NOW（），‘2018-8-8’）；返回：2881

ADDDATE（date，n）；作用：計算日期參數 date 加上 n 天後的日期。

舉例：SELECT ADDDATE（NOW（），5）；

返回：2016-09-02 09：37：07

  eg：

實現方法二：採用子查詢實現：

  1、 子查詢是一個嵌套在 SELECT、INSERT、UPDATE 或 DELETE 語句或其他子查詢中的查詢

   2、子查詢在WHERE語句中的一般用法

子查詢語法：

    SELECT ．．．． FROM 表１　WHERE　欄位１　比較運算符（子查詢）；

其中：

子查詢語句必須放置在一對圓括弧內。

比較運算符包括：＞，＝，＜，＞＝，＜＝。

外面查詢稱為父查詢，圓括弧中嵌入的查詢稱為子查詢。

執行時，先執行子查詢，在執行父查詢，返回返回值。

                    註意;將子查詢和比較運算符聯合使用，必須保證子查詢返回的值不能多於一個

  實現方法一：採用表連接 ：內連接（等值連接）

SELECT `studentName` FROM `student` stu

  INNER JOIN `result` r ON stu.studentNO = r.studentNo          

  INNER JOIN `subject` sub ON r.subjectNo = sub.subjectNo      

WHERE `studentResult` = 60 AND `subjectName` = 'Logic Java';

 實現方法二：採用子查詢

SELECT `studentName` FROM `student` WHERE `studentNo` = (

             SELECT `studentNo` FROM `result`

             INNER JOIN `Subject` ON result.subjectNo= subject.subjectNo

             WHERE `studentResult`=60 AND `subjectName`='Logic Java'

);

  1、子查詢比較靈活、方便，常作為增刪改查的篩選條件，適合於操縱一個表的數據

  2、表連接更適合於查看多表的數據