mysql初步學習

標籤：post   binary   ike   book   new   copy   函數   運算式   port   

1.insert_select 的使用：從一個表複製資料給另一個表

INSERT INTO students(name,sex,LikeBooksNUM,LikesportNUM,average)
SELECT
name,sex,LikeBooksNUM,LikesportNUM,average
FROM students_copy;2.REGEXP :Regex的使用，BINARY 是標明正則的檢索區分大小寫

SELECT students.`name` FROM students WHERE students.`name` REGEXP BINARY "Jack";

3.彙總函式，COUNT（*），AS　表別名，GROUP BY,WITH ROLLUP

SELECT sex ,COUNT(*) AS Sex_num  FROM students GROUP BY sex WITH ROLLUP;

4,左外部連接 LEFT/RIGHT OUTER　JOIN  表列名  ON 連接：選擇左邊/右邊所有的行：例如可以檢索某些值為NULL的列

SELECT students.`name`,students.sex,COUNT(sorce.average) AS average_num
FROM students LEFT OUTER JOIN  sorce ON students.average=sorce.average GROUP BY students.name;5，使用觸發器：CREATE TRIGGER（觸發器）AFTER INSERT ON students_copy 更新的表  FOR EACH ROW UPDATE 執行更新手段 （students_count SET students_count=students_count+1）

CREATE TRIGGER newname AFTER INSERT ON students_copy FOR EACH ROW
UPDATE students_count SET students_count=students_count+1;6，UPDATE 要操作的表 SET 操作指令 WHERE 查詢條件：需要注意的是 列名要避開mysql的關鍵字，例如：name等；

UPDATE students_copy
SET `students_name`="david" WHERE average=100;7，連接表的操作，使操作變得更簡單

SELECT
students.`name`
FROM students,sorce,books
WHERE students.average=sorce.average AND sorce.id=books.id AND books.id=161034168，表的自連接：

SELECT s1.name,s1.average FROM students AS s1,students AS s2 WHERE s1.`name`=s2.`name` AND s1.LikeBooksNUM=3;

 

mysql初步學習