mysql之DML(SELECT DELETE INSERT UPDATE)

標籤：mysql   insert   update   delete   select   

DML：資料操作語言
    INSERT
    DELETE
    SELECT
    UPDATE

SELECT：
    SELECT SELECT-LIST FROM TBNAME|TBNAMES|SELECT ACTION QUALIFICATION

        SELECT-LIST 搜尋清單
            DISTINCT 去重，相同的值只顯示一次。
            AS ALIAS  欄位別名
            * 所有內容
            FIELD 欄位名
            彙總計算函數：
                AVG(FIELD) 平均值
                MIN(FIELD) 最小值
                MAX(FIELD) 最大值
                SUM(FIELD) 求和
                COUNT(FIELD) 個數

        
        FROM子句：要查詢的關係，可以是單個表，多個表，或其他SELECT語句。

        QUALIFICATION 搜尋碼，搜尋索引鍵。

        ACTION 指定對於搜尋碼的限定或操作：
            WHERE子句：指定一個布爾關係的運算式。
                算數操作：+、-、*、/、%（取餘）
                比較操作：=、>、<、>=、<=、<>（不等於）、!=（不等於）、<=>（NULLsafe NULL的安全等於，即便是NULL也能進行安全的相等比較）
                邏輯操作：AND（&&）、OR（||）、NOT（！）、XOR（異或）
                特殊操作：
                    BETWEEN  介於2者之間
                    LIKE ‘PATTERN’  比較操作，符合PATTERN的。
                        % 任意長度任一字元
                        _ 任意單個字元
                    REGEXP|RLIKE ‘PATTERN’ 支援Regex的LIKE，注意使用這個就不能用索引
                    [NOT] IN (LIST) 是否符合列表中的
                    IS [NOT] NULL  跟NULL比較

            ORDER BY  FIELDNAME [ASC|DESC] 將查詢後的結果根據某個欄位進行排序，預設是升序（ASC）排列。

            LIMIT [OFFSET,] COUNT 只顯示COUNT個結果，或從OFFSET（位移量）個開始，顯示
            CONUT個結果。

            GROUP BY QUALIFICATION
                根據搜尋碼進行分組，主要的目的是為了求彙總函式。
                HAVING  QUALIFICATION 過濾，用於將GROUP BY的結果再次進行過濾。只能和GROUP BY一同使用。

    查詢執行順序：
        start --> FROM --> WHERE --> GROUP BY --> HAVING --> ORDER BY --> LIMIT

    單表查詢：
        SELECT * FROM TBNAME 顯示所有內容
        SELECT FIELD1,FIELD2,... FROM TBNAME  投影
        SELECT * FROM TBNAME WHERE QUALIFICATION 選擇

    多表查詢：
        表的串連關係：
            交叉串連：笛卡爾乘積
            自然串連：表中欄位建立等值關係

            內串連：
                TBNAME1 [NEER] JOIN TBNAME2 ON QUALIFICATION
                兩個表中至少存在一個匹配，返回所在行。

            外串連：
                左外串連：TBNAME1 LEFT JOIN TBNAME2 ON QUALIFICATION
                返回左側表中指定的行，無論右側表中是否有匹配。

                右外串連：TBNAME1 RIGHT JOIN
                TBNAME2 ON QUALIFICATION 返回右側表中指定的行，無論左側表中是否有匹配。

                全串連：TBNAME1 FULL JOIN TBNAME2 ON QUALIFICATION
                兩個表中只要有匹配，就返回對應的行。


                自串連：自己和自己串連，通過AS ALIAS的方式來實現。

    子查詢（巢狀查詢）:
        比較操作：只能返回單行單值
        在IN()中使用
        在FROM中使用

    聯集查詢：
        UNION：
            將多個SELECT語句的結果集進行合并。

            注意：欄位的資料類型必須一致。

            預設只顯示不同值，若有重複值想全部顯示使用UNION ALL。
            (SELECT語句1) UNION (SELECT語句2)
            或
            (SELECT語句1) UNION ALL (SELECT語句2)



執行個體：
    1.顯示test庫中courese表的所有內容：
        #mysql -uroot -p
        mysql>USE test;
        mysql>SELECT * FROM courses;

    2.使用投影方式顯示學生表中的名字和年齡：
        mysql>SELECT * FROM studnets;
        mysql>SELECT Name,Age FROM students;

    3.使用選擇方式顯示學生表中年齡大於20的
        mysql>SELECT * FROM students WHERE Age>=20;

    4.綜合使用投影和選擇。顯示students表中性別為男的名字和年齡。
        mysql>SELECT Name,Age FROM students WHERE Gender=‘M’;

    5.顯示students表中的性別：
        mysql>SELECT DISINCT Gender FROM students;

    6.顯示students中年齡+1大於20的名字和年齡：
        注意這樣直接使用可能無法使用索引。
        mysql>SELECT Name,Age FROM students WHERE Age+1>20;

    7.顯示students中年齡大於20的男性：
        mysql>SELECT Name,Age FROM students WHERE Age>20 AND Gender=‘M‘;

    8.顯示students中年齡不大於20，且性別不為男性：
        mysql>SELECT Name,Age,Gender FROM students WHERE NOT (Age>20 OR Gender=‘M‘);

    9.顯示students中年齡在20-25之間的同學：
        mysql>SELECT  Name,Age,Gender FROM students WHERE Age>=20 AND AGE<=25;
        msyql>SELECT  Name,Age,Gender FROM students WHERE Age BETWEEN 20 AND 25;

    10.顯示students中姓名以Y開頭的：
        mysql>SELECT Name,Age FROM students WHERE Name LIKE ‘Y%‘;

    11.顯示students中姓名以Y開頭，總長5個字元的：
        mysql>SELECT Name,Age FROM students WHERE Name LIKE ‘Y____‘;

    12.顯示students中姓名裡包含ing的：
        mysql>SELECT Name,Age FROM students WHERE Name LIKE ‘%ing%‘;

    13.顯示students中姓名以M或N或Y開頭的：
        mysql>SELECT Name,Age FROM students WHERE Name RLIKE ‘^[MNY].*’;

    14.顯示students中年齡為18 20 25 的：
        mysql>SELECT Name,Age FROM students WHERE Age IN (18,20,25);

    15.顯示students中CID2為空白的名字：
        mysql>SELECT Name FROM students WHERE CID2 IS NULL;

    15.顯示使用者的姓名並排序：
        mysql>SELECT Name FROM students ORDER BY Name;

    16.別名顯示：
        mysql>SELECT Name AS StudName FROM students;

    17.僅顯示前3個結果：
        mysql>SELECT Name FROM students LIMIT 3;

    18.從第2個結果以後開始顯示，共顯示3個：
        mysql>SELECT Name FROM students LIMIT 2,3;

    19.彙總函式：
        mysql>SELECET AVG(Age) FROM sutdents WHERE Gender=‘M‘ 男同學年齡的平均值
        mysql>SELECET COUNT(Age) FROM sutdents; 同學年齡的個數

    20.根據性別分組計算年齡的平均值：
        mysql>SELECT AVG(Age) FROM students GROUP BY Gender;

    21.根據CID求同學選修課的個數：
        mysql>SELECT COUNT(CID) AS Persons,CID FROM students GROUP BY CID;

    22.顯示選修課個數大於2個的：
        mysql>SELECT COUNT(CID) AS Persons,CID FROM students GROUP BY CID HAVING Persons>=2;

    23.多表查詢（交叉串連）：
        mysql>SELECT * FROM students,courses;

    24.多表查詢（自然串連）：
        假設有students表和courses表，從2張表中查詢學生名稱和對應的課程名稱：
        mysql>SELECT students.Name,courses.Cname FROM sutdents,course WHERE sutdents.CID=courses.CID;

    25.使用別名：
        mysql>SELECT s.Name,c.Cname FORM studnets AS s,courses AS c WHERE s.CID=c.CID;


    26.多表查詢（外串連）顯示同學選擇的課程，有名字顯示名字，沒有顯示為空白。CID1為選擇的第一門課。
        mysql>SELECT s.Name,c.Cname FROM students AS s LEFT JOIN courses AS c ON s.CID1=c.CID

    27.接上題，顯示課程對應的同學名字，有名字顯示名字，沒有顯示為空白。
        mysql>SELECT s.Name,c.Cname FROM students AS s RIGHT JOINcourses AS c ON s.CID1=c.CID;

    28.自串連，假設students表中有TID，表示該課程的導師編號，導師編號就是SID。顯示同學名字和對應的導師名字
        mysql>SELECT s.Name AS stu,c.Name AS teacher FROM students AS s，students AS c WHERE  s.SID=c.SID;

    29.顯示表中年齡大於平均年齡的同學
        mysql>SELECT Name FROM students WHERE Age> (SELECT AVG(Age) FROM students);

    30.顯示學生中年齡和老師一樣的
        mysql>SELECT Name FROM students WHERE Age IN (SELECT Age FROM Tutor);

    31.顯示所有同學的名字和年齡並顯示年齡大於等於20的
        mysql>SELECT Name,Age FROM (SELECT Name,Age FROM students) AS t WHERE t.Age >=20;

    32.將students表和tutor表中查詢到的Name和Age進行合并
        mysql>(SELECT Name,Age FROM students) UNION (SELECT Tname,Age FROM tutor) ;

    33.顯示courses表中沒有被students表中出現在CID2的課程的名稱：
        mysql>SELECT Cname FROM courses WHERE CID NOT IN(SELECT DISTINCT CID2 FROM students WHERE CID2 IS NOT NULL);

    34.顯示在courses表中的沒有代課的老師名字，CID課程編號 Cname課程名稱 和TID教師編號，tutors表中為老師名字Tname和TID
        mysql>SELECT Tname FROM tutors WHRER TID NOT IN (SELECT DISTINCT TID FROM courses);

    35.顯示students表中CID1有2個或2個以上同學學習的額課程的名稱
        mysql>SELECT Cname FROM courses WHRER CID IN (SELECT  CID1 FROM students GROUP BY CID1 HAVING COUNT(CID1)>=2 );

    36.顯示每個老師和其教授的課程，沒有教授的課程保持為NULL
        mysql>SELECT t.Tname,c.Cname FROM tutors AS t LEFT JOIN courses AS c ON t.TID=c.TID;

    37.顯示每個課程及其相關的老師，沒有老師教授的課程將其老師顯示為空白
        mysql>SELECT t.Tname,c.Cname FROM tutors AS t RIGHT JOIN courses AS c ON t.TID=c.TID;

    38.顯示每位同學的CID1的課程及其老師名稱
        mysql>SELECT Name,Cname,Tname FROM stutdents,courses,tutors WHERE students.CID=courses.CID AND courses.TID=tutors.TID

    39.顯示tutors中最後一行的資料：
        mysql>SELECT * FROM tutors ORDER BY TID DESC LIMIT 1;

INSERT：     
        
    INSERT INTO TBNAME (COL1,COL2,...) VALUES (VAL1,VAL
        2...)[,(VAL3,VAL4),...] 可以插入多行

    INSERT INTO TBNAME SET COL1=VAL1,...; 插入一行

    INSERT INTO  TBNAME (COL1,...) SELECT STATMENT  將搜尋到的內容插入，注意格式要完全相同。


    REPLACE INTO 和INSERT類似，提供替換功能。


    插入的資料類型：
        字元型：單引號
        數值型：
        日期時間型
        空值：NULL
        空串：‘’

    執行個體：
        1.向tutors表中插入新資料：
            mysql>INSERT INTO tutors (Tname,Gerder,Age) VALUES (tom,‘M‘,40),(jerry,‘F‘,33);

        2.向totors表中插入單行資料：
            mysql>INSERT INTO tutors SET Tname=‘mike‘,Gender=‘F‘,Age=22;

        3.將students表中年齡大於20的插入tutors表中：
            mysql>INSERT INTO tutors (Tname,Gernder,Age) SELECT Nname,Gender,Age FORM students WHERE Age>20;


DELETE：
    DELETE FROM TBNAME1,TBNAME2,... [WHERE]|[ORDER BY] |[LIMIT] 刪除表

    TRUNCATE TBNAME 清空表並重設AUTO_INCREMENT計數器。

    執行個體：
        1.刪除students表
            mysql>DELETE FROM students;

        2.刪除students表中年齡不大於18的行：
            msyql>DELETE FROM students WHERE Age<=18;

        3.顯示並清空students表並重設計數器

            mysql>SHOW TABLE STATUS LIKE ‘students‘\G;
            mysql>SHOW VARIABLES LIKE ‘%last_insert_id%
            mysql>TRUNCATE students;


UPDATE
    UPDATE TBNMAE SET COL=VALUE [WHERE]|[ORDER BY] |[LIMIT]

    執行個體：
        1,修改students表中tom的年齡和性別：
            mysql>UPDTAE students SET Age=30,Gernder=‘F‘ WHERE Name=‘tom‘;

本文出自 “小私的blog” 部落格，請務必保留此出處http://ggvylf.blog.51cto.com/784661/1680849

mysql之DML(SELECT DELETE INSERT UPDATE)