mysql對錶中記錄的操作

標籤：接下來   正則表達   desc   指示   values   彙總函式   彙總   方式   輸出   

增:插入記錄

    CREATE table employee_new(
                  id INT PRIMARY KEY auto_increment,
                  name VARCHAR(20) NOT NULL UNIQUE ,
                  birthday VARCHAR(20),
                  salary FLOAT(7,2)
    )CHARACTER SET utf8;

    INSERT INTO employee_new(name, birthday, salary) VALUES (‘yuan‘,‘1990-09-09‘,9000),
                                                                (‘alex‘,‘1989-08-08‘,3000),
                                                                (‘xialv‘,‘1988-07-07‘,1000),
                                                                (‘alvin1‘,‘1993-04-20‘,3000),
                                                                (‘alvin2‘,‘1995-05-12‘,5000);

    INSERT INTO employee_new SET id=12,name="alvin3";
    注意：表中資料三條，id分別為1，2，3，突然插入一個id＝7，那麼下次作為主鍵的字增長的id會從幾開始增長呢？(從7開始)

刪:刪除表記錄
    DELETE from employee_new WHERE id=12;
          如果不跟where語句則刪除整張表中的資料
          delete只能用來刪除一行記錄
          delete語句只能刪除表中的內容,不能刪除表本身,想要刪除表,用drop
          TRUNCATE TABLE也可以刪除表中的所有資料,詞語句首先摧毀表,再建立表.此種方式刪除的資料不能在
          事務中恢複.
    DELETE from employee_new WHERE name="alex";
    DELETE FROM employee_new;刪除表中所有記錄
    truncate employee_new;刪除表中所有記錄
改:修改記錄表

    UPDATE employee_new SET birthday=‘1989-10-24‘ WHERE id=1;
    UPDATE employee_new SET salary=salary+1000 WHERE name="yuan";

        UPDATE 文法可以用新值更新原有表行中的各列
        SET 字句指示要修改哪些列和要給予哪些值
        WHERE 字句指定應更新哪些行,如果沒有 WHERE 字句,則更新所有行


查詢運算式:

    SELECT *|field1,field2,... FROM tab_name
                WHERE 條件
                GROUP BY field
                HAVING 篩選
                ORDER BY field 排序 DESC(遞減) ASC(遞增)
                LIMIT 限制條數

(1)  SELECT [DISTINCT] *|field1,field2,...FROM tab_name
        --其中from指定從哪張表篩選，*表示尋找所有列，也可以指定一個列
        -- 表明確指定要尋找的列，distinct用來剔除重複行。

      --查詢表中所有學生的資訊
      SELECT * FROM ExamResult;
      --查詢表中所有學生的姓名和對應的英語成績
      SELECT name,JS FROM ExamResult;
      --過濾表中重複資料
      SELECT DISTINCT JS,name FROM ExamResult;
(2)   SELECT 也可以使用運算式，並且可以使用:欄位 AS 別名，或者：欄位 別名。
      -- 在所有學生分數上加10分特長分顯示。
      SELECT name,JS+10,Django+10,OpenStack+10 FROM ExamResult;
      -- 統計每個學生的總分。
      SELECT name,JS+Django+OpenStack FROM ExamResult;
      -- 使用別名表示學生總分。
      SELECT name,JS+Django+OpenStack 總分 FROM ExamResult;
(3)   使用 WHERE 字句，進行過濾查詢
     -- 查詢姓名為XXX的學產生績
     SELECT * FROM ExamResult WHERE name=‘yuan‘;
     -- 查詢英語成績大於90分的同學
     SELECT id,name,JS FROM ExamResult WHERE JS>90;
     -- 查詢總分大於200分的所有同學
     select name,JS+Django+OpenStack as 總成績 from
                            ExamResult where JS+Django+OpenStack>200 ;
     -- where字句中可以使用：
        比較子：
        > < >= <= !=
        BETWEEN 80 AND 100 值在80到100之間
        IN(80,90,100) 值是80或90或100
        LIKE ‘yuan%‘ 匹配以yuan開頭的,%表示任意多字元,_ 表示一個字元。兩個字元用兩個底線__
        -- 邏輯運算子
        在多個條件直接可以使用邏輯運算子 AND OR NOT
(4)  ORDER BY 指定排序的列，排序的列即可是表中的列名，也可以是select 語句後指定的別名。
     SELECT *|field1,field2... FROM tab_name order by field [Asc|Desc]
     -- Asc 升序、Desc 降序
     -- 對總分排序按從高到低的順序輸出
      select name ,(ifnull(JS,0)+ifnull(Django,0)+ifnull(Database,0))
           總成績 from ExamResult order by 總成績 desc;
      --ifnull(JS,0) 如果JS是空，就記為0

      -- 對姓李的學產生績排序輸出
      SELECT name,(ifnull(JS,0)+ifnull(Django,0)+ifnull(OpenStack,0)) 總成績 FROM ExamResult
                    WHERE name LIKE ‘a%‘
                    ORDER BY 總成績 DESC ;

(5)  group by 分組查詢:
     -- 注意,按分組條件分組後每一組只會顯示第一條記錄
     -- group by字句，其後可以接多個列名，也可以跟having子句,對group by 的結果進行篩選。

           /* 注意注意注意！
           having 和 where兩者都可以對查詢結果進行進一步的過濾，差別有：
             <1>where語句只能用在分組之前的篩選，having可以用在分組之後的篩選；
             <2>使用where語句的地方都可以用having進行替換
             <3>having中可以用彙總函式，where中就不行。
           */

     --GROUP_CONCAT() 函數
     select id,group_concat(name) from aa group by id; 
     --以id分組，把name欄位的值列印在一行，逗號分隔（分隔）
    ------+--------------+--------+------------+--------------+
    | id   | product_name | price  | born_date  | class        |
    +------+--------------+--------+------------+--------------+
    |    1 | 蘋果         |  20.00 | 2017-06-12 | 水果         |
    |    1 | 香蕉         |  80.00 | 2017-01-02 | 水果         |
    |    2 | 水壺         | 120.00 | 2017-06-12 | 電器         |
    |    0 | 被罩         |  70.00 | 2017-01-12 | 床上用品     |
    |    2 | 音響         | 420.00 | 2017-01-12 | 電器         |
    |    0 | 床單         |  55.00 | 2017-06-12 | 床上用品     |
    |    1 | 草莓         |  34.00 | 2017-01-12 | 水果         |
    +------+--------------+--------+------------+--------------+
    select id,group_concat(name) from aa group by id; 
    +------+----------------------------+
    | id   | group_concat(product_name) |
    +------+----------------------------+
    |    0 | 被罩,床單                  |
    |    1 | 蘋果,香蕉,草莓             |
    |    2 | 水壺,音響                  |
    +------+----------------------------+

    select id,group_concat(product_name),group_concat(born_date) from order_menu group by id;
    +------+----------------------------+----------------------------------+
    | id   | group_concat(product_name) | group_concat(born_date)          |
    +------+----------------------------+----------------------------------+
    |    0 | 被罩,床單                  | 2017-01-12,2017-06-12            |
    |    1 | 蘋果,香蕉,草莓             | 2017-06-12,2017-01-02,2017-01-12 |
    |    2 | 水壺,音響                  | 2017-06-12,2017-01-12            |
    +------+----------------------------+----------------------------------+

(6) 彙總函式:一般和分組查詢配合使用
    <1>統計表中所有記錄:
    -- COUNT(列名)：統計行的個數
        eg:--統計總分數大於280的人數有多少?
        SELECT COUNT(name) FROM student WHERE (ifnull(JS,0)+ifnull(Django,0)+ifnull(Datebase,0))>280;
        --注意：count(*)統計所有的行，count(欄位)不統計null的值

    <2>統計滿足條件的行的內容和
    -- SUM(列名)：統計滿足條件的行的內容和
        -- 統計一個班級各科分別的總成績
                                select sum(JS) as JS總成績,
                                       sum(Django) as Django總成績,
                                       sum(OpenStack) as OpenStack from ExamResult;

                            -- 統計一個班級各科的成績總和
                                select sum(ifnull(JS,0)+ifnull(Django,0)+ifnull(Database,0))
                                                            as 總成績 from ExamResult;
                            -- 統計一個班級JS成績平均分
                                select sum(JS)/count(*) from ExamResult ;

        -- 注意：sum僅對數值起作用，否則會報錯。
    <3>求平均數
    -- AVG(列名)：
        -- 求一個班級JS平均分？先查出所有的JS分，然後用avg包上。
        select avg(ifnull(JS,0)) from ExamResult;
        -- 求一個班級總分平均分
        select avg((ifnull(JS,0)+ifnull(Django,0)+ifnull(Database,0)))

    <4>max() 和 min()
    -- 求班級最高分和最低分（數值範圍在統計中特別有用）
                                  select Max(ifnull(JS,0)+ifnull(Django,0)+ifnull(OpenStack,0)) 最高分 from ExamResult;
                                  select Min(ifnull(JS,0)+ifnull(Django,0)+ifnull(OpenStack,0)) 最低分 from ExamResult;
    --注意：null 和 所有的數計算都得null,所以需要用ifnull將null轉換為0. ifnull(JS,0)

(7)重點:
    SELECT FROM WHERE GROUP BY HAVING ORDER BY
    --mysql在執行sql語句時的執行順序：
    --from where select group by having order by
    -- 分析:
                    select JS as JS成績 from ExamResult where JS成績 >70; ---- 不成功
                    select JS as JS成績 from ExamResult having JS成績 >90; --- 成功
(8) limit
        SELECT * from ExamResult limit 1;
        SELECT * from ExamResult limit 2,5;--跳過前兩條顯示接下來的五條紀錄
        SELECT * from ExamResult limit 2,2;

(9) 使用Regex查詢
        SELECT * FROM employee WHERE emp_name REGEXP ‘^yu‘;

        SELECT * FROM employee WHERE emp_name REGEXP ‘yun$‘;

        SELECT * FROM employee WHERE emp_name REGEXP ‘m{2}‘;

mysql對錶中記錄的操作