MySQL入門

1. mysql是一種常用的資料庫管理軟體，優點有：免費，開源，跨平臺，本文只是介紹一下MySQL的簡單操作

2.資料庫的基本結構

　　可以把資料庫理解成一個文件夾，資料庫中的數據存放的單位是表，可以理解為excel表格，表格的表頭稱為欄位，表中的每一條數據稱為記錄

3.MySQL安裝

　　1.從官網下載 https://www.mysql.com/downloads/

　　2.下載msi格式的文件，雙擊安裝即可

　　3.配置環境變數

　　windows環境安裝比linux簡單的多，所以安裝過程就不贅述

　　4.安裝好以後，打開命令提示行，輸入mysql -uroot -pkey,（key為安裝過程中設置的 密碼），然後回車出現  mysql > ，就表示安裝成功了

4.sql語句規範

　　sql是結構化查詢語言的縮寫。我們通過sql語句來操作資料庫，下麵是sql語句的一些書寫規範

　　1.sql語句不區分大小寫，但是建議sql命令大寫，自定義的庫名錶名之類小寫

　　2.sql語句支持分行，以分號結尾，關鍵詞不能跨行

　　3.用空格和縮進來提高語句可讀性

　　4.單行註釋：--

　　　多行註釋：/*....*/

　　5.中括弧里的內容表示 可有可無

5.資料庫簡單操作

　　1.創建資料庫　　CREATE DATABASE database_name;

　　　可以在創建資料庫的時候指定編碼集  CREATE DATABASE database_name CHARACTER SET utf8;

　　2.刪除資料庫　　DROP DATABASE database_name; 從刪庫到跑路

　　3.查看已創建的資料庫　　SHOW DATABASES;

　　4.查看資料庫創建信息 　　SHOW CREATE DATABASE database_name;

　　5.修改資料庫字元集　　ALTER DATABASE database_name CHARACTER SET utf8;

　　6.使用資料庫　　USE database_name;    進入資料庫後沒有退出名令，但是可以繼續用SHOW查看資料庫並用USE切換到其他資料庫

6.表的簡單操作

　　使用資料庫後執行的操作

　　1.創建表　　CREATE TABLE table_name（欄位名稱 欄位數據類型  [約束]，

　　　　　　　　　　　　　　　　　　　　　   欄位名稱 欄位數據類型  [約束]，　　　　

　　　　　　　　　　　　　　　　　　　　　　欄位名稱 欄位數據類型  [約束]

　　　　　　　　　　　　　　　　　　　　　　）;

　　2.查看所有表　　SHOW TABLES;

　　　查看表的詳細信息　　DESC table_name;

　　　查看表的創建信息　　SHOW CREATE TABLE table_name；

　　3.增加列　　ALTER TABLE table_name ADD 列名 類型 [完整性約束條件] [first|after 欄位名];

　　  增加多列用逗號隔開

　　4.修改 

　　　修改一列類型 　　ALTER TABLE table_name MODIFY 列名 類型  [完整性約束條件];

　　　修改列名　　　　ALTER TABLE table_name CHANGE 列名 新名 類型 [約束條件];

　　　修改表名　　RENAEM TABLE 表名 TO 新名；

　　　修改表的字元集　　ALTER TABLE table_name CHARACTER SET utf8;

　　5.刪除　

　　　刪除一列　　ALTER TABLE table_name DROP 列名;

　　　刪除表　　　DROP TABLE table_name；

　　　刪除主鍵的自增長　　ALTER TABLE table_name CHANGE id id int(10)

7.約束條件

　　創建表的時候，欄位後面要加約束條件。常用的約束條件有：

　　1.非空　　not null

　　2.唯一性　unique

　　3.主鍵　　primary key　　如果一個欄位設置了非空屬性和唯一性約束，那麼自動識別為主鍵

　　4.自增　　auto_increment  只有主鍵可以設置這個約束

8.sql數據類型

數值類型：　　

時間類型：

字元串類型：

9.表記錄操作

　　1.插入數據 insert

　　INSERT [INTO] table_name (欄位名稱，，，) VALUES(值，，，)，

　　　　　　　　　　　　　　　　　　　　　　　　　　　(值，，，)，

　　　　　　　　　　　　　　　　　　　　　　　　　　　　.......　　

　　　　　　　　　　　　　　　　　　　　　　　　　　　(值，，，)；

　　2.修改表記錄

　　　　UPDATE table_name SET 欄位=值，欄位=值....WHERE子句；

　　3.刪除表記錄

　　　　DELETE FROM table_name WHERE子句； 不加where子句的話會把整個表刪除

　　　　TRUNCATE TABLE table_name；　　刪除整個表

　　　　delete和truncate的區別：

　　　　　　1.語法不同

　　　　　　2.delete後面可以加限制條件，選擇刪除某條記錄，而truncate只能刪掉整個表

　　　　　　3.delete 可以返回刪除的數據，但是truncate不能

　　　　　　4.在刪除整個表的時候，delete的原理是一條一條的刪除，而truncate 的原理是把整個表刪掉，再新建一個同樣欄位的表

　　　　　　　所以，如果表中有自增長的欄位，delete刪除之後，新的表是從上個位置開始自增的，但是truncate是從1開始

10.表記錄查詢　　select

這個是最重要，也是最常用的

　　1.單表查詢

SELECT [DISTINCT] *|field1,field,... from table_name where .... group by .... order by...[desc|asc]

　　　　　1.顯示表的全部信息　　SELECT * FROM table_name;　

　　　　　   顯示指定列的信息　　SELECT 列，列.. FROM table_name；

　　　　　   SELECT 後面可以跟 表達式 、distinct（表示去重）

　　　　　2.用WHERE子句進行過濾

　　　　　  WHERE子句中可以使用:

　　　　　　　　1.比較運算符  > < >= <= !=

　　　　　　　　2.between .. and ..   在..和.. 之間

　　　　　　　　3.in (a,b,c)         在a,b,c 這三個值中一個

　　　　　　　　4.like  條件　　eg: like "李%"  表示已“李”開頭

　　　　　　　　　　　　　　　　  like "李_"    表示以“李”開頭，並且後面有一個字元，一個下劃線代表一個字元

　　　　　　　　5.邏輯運算符 and or not

　　　　　3.order by  表示以。。排序，排序的列可以是列名，也可以是設置的別名

　　　　　　　　order語句後面可以跟兩個模式，asc表示升序（預設），desc表示降序

　　　　　4.group by 表示以..分組，分組後只會顯示每個欄位的第一條記錄，分組後的篩選用的關鍵字是having ，作用和 where一樣，與where相比，having後面可以使用聚合函數

　　　　　5.聚合函數：

　　　　　　　　1.avg() 求平均值

　　　　　　　　2.count() 統計個數

　　　　　　　　3.sum() 求和函數

　　　　　　　　4.max(),min()  最大值最小值函數

　　　　　6.limit 關鍵字用來控制顯示的條數，limit 1 表示只顯示第一行，limit 2,5 表示顯示第2到5行

　　　　　7.使用正則表達式

　　2.多表查詢

　　　　1.外鍵

　　　　　　如果兩張表要創建聯繫，就需要使用到外鍵，需要考慮幾種關係：一對一，多對多，和一對多

　　　　　　1.一對多： 這種模型中，有主表和子表的區別，比如一個老師有多個學生，一個學生只能有一個老師，這種情況老師就是主表，學生就是子表，我們需要在子表中創建關聯欄位（外鍵）

　　　　　　　　增加外鍵：[ADD CONSTRAINT name] FOREIGN KEY （欄位名）REFERENCES table_name；

　　　　　　　　刪除外鍵：ALTER TABLE table_name DROP FOREIGN KEY name；

　　　　　　　　　　　　　　（這個name是增加外鍵時給外鍵起的名字，如果沒有設置，從表的創建方式中可以找到）

　　　　　　2.多對多：需要藉助第三張表，在第三張表中創建兩個外鍵

　　　　　　3.一對一：這種實際就是外鍵加一個unique，很少用

　　　　　　4. 一旦兩張表建立了關聯，就不能隨便從表中刪除記錄了，必須解除關聯後才能刪

　　　　2.內連接

　　　　　　我們在查詢表記錄的時候，可以同時顯示多張表的記錄，類似

　　　　　　select * from tableA,tableB 

　　　　　　但是這樣並不是我們想要的結果，當然我們可以在查詢結果後面用where子句進行篩選，但更常用的是用內連接：

　　　　　　select * from tableA inner join tableB on tableA.id = tableB.id

　　　　3.外連接

　　　　　　1.左外連接：在內連接的基礎上增加左邊有右邊沒有的結果

　　　　　　    select * from tableA left join tableB on tableA.id = tableB.id

　　　　　　2.右外連接：在內連接的基礎上增加右邊有左邊沒有的結果

　　　　　　　select * from tableA right join tableB on tableA.id = tableB.id

練習：

  1 -- 1.查詢平均成績大於八十分的同學的姓名和平均成績
  2 -- SELECT sname,avg(num) from student INNER JOIN  score
  3 --                                 on student.sid = score.student_id
  4 --                                 GROUP BY student_id
  5 --                                 HAVING avg(num) >80
  6 
  7 -- 2、查詢所有學生的學號，姓名，選課數，總成績
  8 
  9 -- SELECT student.sid,sname,count(course_id),sum(num) from student
 10 --                     INNER JOIN score
 11 --                     on score.student_id = student.sid
 12 --                     GROUP BY student_id
 13 -- 3、查詢沒有報李平老師課的學生姓名
 14 
 15 -- SELECT sname from student WHERE sid not in (SELECT DISTINCT student_id from score WHERE course_id  in (SELECT cid from teacher
 16 --                                                                                     INNER JOIN course
 17 --                                                                                     on teacher.tid = course.teacher_id
 18 --                                                                                     WHERE tname like '李平%'))
 19 
 20 -- 4、查詢物理課程比生物課程高的學生的學號
 21 -- SELECT * from
 22 --
 23 -- (SELECT * from    score WHERE course_id = (SELECT cid from course WHERE cname="物理")) as A
 24 --
 25 -- INNER JOIN
 26 --
 27 -- (SELECT * from    score WHERE course_id = (SELECT cid from course WHERE cname="生物")) as B
 28 --
 29 -- on A.student_id = B.student_id
 30 --
 31 -- WHERE A.num > B.num
 32 --
 33 -- 5、查詢沒有同時選修物理課程和體育課程的學生姓名
 34 
 35 -- SELECT sname from student where sid not in(SELECT student_id from score
 36 --                 WHERE course_id
 37 --                 in (select cid from course
 38 --                         WHERE cname
 39 --                         like "物理" or cname = "體育")
 40 --                 GROUP BY student_id having count(sid)=2)
 41 --
 42 
 43 -- 6、查詢掛科超過兩門(包括兩門)的學生姓名和班級
 44 
 45 -- SELECT sname,caption from student
 46 --                                         INNER JOIN class
 47 --                                         on student.sid = class.cid
 48 --                                         where sid in
 49 --                                         (SELECT student_id from score
 50 --                                                     where num    <60 GROUP BY student_id
 51 --                                                             HAVING count(num) >=2)
 52 
 53 -- 7、查詢選修了所有課程的學生姓名
 54 -- SELECT sname from student
 55 --                             WHERE sid in (SELECT student_id  from score
 56 --                                                                                             GROUP BY student_id
 57 --                                                                                                 HAVING count(course_id) = (SELECT count(cid) from course)
 58 -- )
 59 
 60 
 61 
 62 
 63 -- 8、查詢不同課程但成績相同的學號，課程號，成績
 64 
 65 SELECT student_id,course_id,num from score WHERE student_id = (SELECT A.student_id from
 66 
 67 (SELECT *,COUNT(DISTINCT num) as C FROM score GROUP BY student_id)as A
 68 
 69 LEFT JOIN
 70 
 71 (SELECT *,count(num) as D FROM score GROUP BY student_id) as B
 72 
 73 on A.sid = B.sid
 74 
 75 where C!=D)
 76 
 77 -- 9、查詢沒學過“李平”老師課程的學生姓名以及選修的課程名稱；
 78 
 79 SELECT a.sname,course.cname from course
 80                     RIGHT JOIN (SELECT * from student
 81                                 WHERE sid not in
 82                                             (select student_id
 83                                                     from score where course_id =3) )as A
 84                     on course.cid = A.class_id
 85 
 86 
 87 
 88 
 89 -- 10、查詢所有選修了學號為1的同學選修過的一門或者多門課程的同學學號和姓名；
 90 
 91 --
 92 SELECT sid,sname
 93         from student
 94         WHERE sid in (SELECT DISTINCT student_id from score 
 95                                                                 WHERE course_id
 96                                                                 in (SELECT course_id
 97                                                                                 from score where student_id = 1))
 98 
 99 
100 
101 
102 -- 11、任課最多的老師中學生單科成績最高的學生姓名
103 
104 SELECT sname from student
105         where sid in (SELECT DISTINCT student_id from score
106                                             WHERE course_id in (SELECT cid from course
107                                                     where teacher_id=(SELECT teacher_id from course
108                                                             GROUP BY teacher_id order by count(cid) desc limit 1))
109                                                                         and num=(SELECT max(num) from score WHERE course_id in
110                                                                                 (SELECT cid from course where teacher_id=(SELECT teacher_id
111                                                                                             from course GROUP BY teacher_id order by count(cid) desc limit 1)) ))-- SELECT * from score  GROUP BY course_id having