mysql表操作之資料操作

標籤：格式   方式   單條件   source   mysql   判斷   update   辦公   dml   

一、介紹

在MySQL管理軟體中，可以通過SQL語句中的DML語言來實現資料的操作，包括

使用INSERT實現資料的插入
UPDATE實現資料的更新
使用DELETE實現資料的刪除
使用SELECT查詢資料以及。

二、插入資料INSERT

1. 插入完整資料（順序插入）    文法一：    INSERT INTO 表名(欄位1,欄位2,欄位3…欄位n) VALUES(值1,值2,值3…值n);    文法二：    INSERT INTO 表名 VALUES (值1,值2,值3…值n);2. 指定欄位插入資料    文法：    INSERT INTO 表名(欄位1,欄位2,欄位3…) VALUES (值1,值2,值3…);3. 插入多條記錄    文法：    INSERT INTO 表名 VALUES        (值1,值2,值3…值n),        (值1,值2,值3…值n),        (值1,值2,值3…值n);        4. **插入查詢結果**    文法：    INSERT INTO 表名(欄位1,欄位2,欄位3…欄位n)                     SELECT (欄位1,欄位2,欄位3…欄位n) FROM 表2                    WHERE …;

三、更新資料UPDATE

文法：    UPDATE 表名 SET        欄位1=值1,        欄位2=值2 WHERE CONDITION;樣本：    UPDATE mysql.user SET password=password(‘123’)         where user=’root’ and host=’localhost’;

四、刪除資料DELETE

文法：    DELETE FROM 表名         WHERE CONITION;樣本：    DELETE FROM mysql.user         WHERE password=’’;練習：    更新MySQL root使用者密碼為mysql123    刪除除從本地登入的root使用者以外的所有使用者    

查詢資料1. 單表查詢的文法

SELECT 欄位1,欄位2... FROM 表名                  WHERE 條件                  GROUP BY field                  HAVING 篩選                  ORDER BY field                  LIMIT 限制條數

2. 關鍵字的執行優先順序(重點)

重點中的重點：關鍵字的執行優先順序fromwhere 約束條件group by  分組having  過濾selectdistinctorder bylimit

1.找到表:from

2.拿著where指定的約束條件，去檔案/表中取出一條條記錄3.將取出的一條條記錄進行分組group by，如果沒有group by，則整體作為一組4.將分組的結果進行having過濾5.執行select6.去重7.將結果按條件排序：order by8.限制結果的顯示條數

準備表和記錄

company.employee    員工id      id                  int                 姓名        emp_name            varchar    性別        sex                 enum    年齡        age                 int    入職日期     hire_date           date    崗位        post                varchar    職位描述     post_comment        varchar    薪水        salary              double    辦公室       office              int    部門編號     depart_id           int#建立表create table employee(id int not null unique auto_increment,name varchar(20) not null,sex enum(‘male‘,‘female‘) not null default ‘male‘, #大部分是男的age int(3) unsigned not null default 28,hire_date date not null,post varchar(50),post_comment varchar(100),salary double(15,2),office int, #一個部門一個屋子depart_id int);#查看錶結構mysql> desc employee;+--------------+-----------------------+------+-----+---------+----------------+| Field        | Type                  | Null | Key | Default | Extra          |+--------------+-----------------------+------+-----+---------+----------------+| id           | int(11)               | NO   | PRI | NULL    | auto_increment || name         | varchar(20)           | NO   |     | NULL    |                || sex          | enum(‘male‘,‘female‘) | NO   |     | male    |                || age          | int(3) unsigned       | NO   |     | 28      |                || hire_date    | date                  | NO   |     | NULL    |                || post         | varchar(50)           | YES  |     | NULL    |                || post_comment | varchar(100)          | YES  |     | NULL    |                || salary       | double(15,2)          | YES  |     | NULL    |                || office       | int(11)               | YES  |     | NULL    |                || depart_id    | int(11)               | YES  |     | NULL    |                |+--------------+-----------------------+------+-----+---------+----------------+#插入記錄#三個部門：教學，銷售，運營insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values(‘egon‘,‘male‘,18,‘20170301‘,‘老男孩駐沙河辦事處外交大使‘,7300.33,401,1),(‘alex‘,‘male‘,78,‘20150302‘,‘teacher‘,1000000.31,401,1),(‘wupeiqi‘,‘male‘,81,‘20130305‘,‘teacher‘,8300,401,1),(‘yuanhao‘,‘male‘,73,‘20140701‘,‘teacher‘,3500,401,1),(‘liwenzhou‘,‘male‘,28,‘20121101‘,‘teacher‘,2100,401,1),(‘jingliyang‘,‘female‘,18,‘20110211‘,‘teacher‘,9000,401,1),(‘jinxin‘,‘male‘,18,‘19000301‘,‘teacher‘,30000,401,1),(‘成龍‘,‘male‘,48,‘20101111‘,‘teacher‘,10000,401,1),(‘歪歪‘,‘female‘,48,‘20150311‘,‘sale‘,3000.13,402,2)，(‘丫丫‘,‘female‘,38,‘20101101‘,‘sale‘,2000.35,402,2),(‘丁丁‘,‘female‘,18,‘20110312‘,‘sale‘,1000.37,402,2),(‘星星‘,‘female‘,18,‘20160513‘,‘sale‘,3000.29,402,2),(‘格格‘,‘female‘,28,‘20170127‘,‘sale‘,4000.33,402,2),(‘張野‘,‘male‘,28,‘20160311‘,‘operation‘,10000.13,403,3),(‘程咬金‘,‘male‘,18,‘19970312‘,‘operation‘,20000,403,3),(‘程咬銀‘,‘female‘,18,‘20130311‘,‘operation‘,19000,403,3),(‘程咬銅‘,‘male‘,18,‘20150411‘,‘operation‘,18000,403,3),(‘程咬鐵‘,‘female‘,18,‘20140512‘,‘operation‘,17000,403,3);

#簡單查詢    SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id     FROM employee;    SELECT * FROM employee;    SELECT name,salary FROM employee;#避免重複DISTINCT    SELECT DISTINCT post FROM employee;    #通過四則運算查詢    SELECT name, salary*12 FROM employee;    SELECT name, salary*12 AS Annual_salary FROM employee;    SELECT name, salary*12 Annual_salary FROM employee;#定義顯示格式   CONCAT() 函數用於連接字串   SELECT CONCAT(‘姓名: ‘,name,‘  年薪: ‘, salary*12)  AS Annual_salary    FROM employee;      CONCAT_WS() 第一個參數為分隔字元   SELECT CONCAT_WS(‘:‘,name,salary*12)  AS Annual_salary    FROM employee;

• 小練習

1 查出所有員工的名字，薪資,格式為    <名字:egon>    <薪資:3000>2 查出所有的崗位（去掉重複）3 查出所有員工名字，以及他們的年薪,年薪的欄位名為annual_year

WHERE約束

where字句中可以使用：

1. 比較子：> < >= <= <> !=2. between 80 and 100 值在10到20之間3. in(80,90,100) 值是10或20或304. like ‘egon%‘    pattern可以是%或_，    %表示任意多字元    _表示一個字元 5. 邏輯運算子：在多個條件直接可以使用邏輯運算子 and or not

#1:單條件查詢    SELECT name FROM employee        WHERE post=‘sale‘;        #2:多條件查詢    SELECT name,salary FROM employee        WHERE post=‘teacher‘ AND salary>10000;#3:關鍵字BETWEEN AND    SELECT name,salary FROM employee         WHERE salary BETWEEN 10000 AND 20000;    SELECT name,salary FROM employee         WHERE salary NOT BETWEEN 10000 AND 20000;    #4:關鍵字IS NULL(判斷某個欄位是否為NULL不能用等號，需要用IS)    SELECT name,post_comment FROM employee         WHERE post_comment IS NULL;    SELECT name,post_comment FROM employee         WHERE post_comment IS NOT NULL;            SELECT name,post_comment FROM employee         WHERE post_comment=‘‘; 注意‘‘是Null 字元串，不是null    ps：        執行        update employee set post_comment=‘‘ where id=2;        再用上條查看，就會有結果了#5:關鍵字IN集合查詢    SELECT name,salary FROM employee         WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;        SELECT name,salary FROM employee         WHERE salary IN (3000,3500,4000,9000) ;    SELECT name,salary FROM employee         WHERE salary NOT IN (3000,3500,4000,9000) ;#6:關鍵字LIKE模糊查詢    萬用字元’%’    SELECT * FROM employee             WHERE name LIKE ‘eg%‘;    萬用字元’_’    SELECT * FROM employee             WHERE name LIKE ‘al__‘;

• 小練習

1. 查看崗位是teacher的員工姓名、年齡2. 查看崗位是teacher且年齡大於30歲的員工姓名、年齡3. 查看崗位是teacher且薪資在9000-10000範圍內的員工姓名、年齡、薪資4. 查看崗位描述不為NULL的員工資訊5. 查看崗位是teacher且薪資是10000或9000或30000的員工姓名、年齡、薪資6. 查看崗位是teacher且薪資不是10000或9000或30000的員工姓名、年齡、薪資7. 查看崗位是teacher且名字是jin開頭的員工姓名、年薪

分組查詢:GROUP BY

1. 什麼是分組？為什麼要分組？

#1、首先明確一點：分組發生在where之後，即分組是基於where之後得到的記錄而進行的#2、分組指的是：將所有記錄按照某個相同欄位進行歸類，比如針對員工資訊表的職位分組，或者按照性別進行分組等#3、為何要分組呢？    取每個部門的最高工資    取每個部門的員工數    取男人數和女人數小竅門：‘每’這個字後面的欄位，就是我們分組的依據#4、大前提：    可以按照任意欄位分組，但是分組完畢後，比如group by post，只能查看post欄位，如果想查看組內資訊，需要藉助於彙總函式

1. GROUP_BY

• 先確定分組的條件，然後再利用函數將其他欄位的記錄整合成一行即可;

單獨使用GROUP BY關鍵字分組    SELECT post FROM employee GROUP BY post;    注意：我們按照post欄位分組，那麼select查詢的欄位只能是post，想要擷取組內的其他相關資訊，需要藉助函數GROUP BY關鍵字和GROUP_CONCAT()函數一起使用    SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;#按照崗位分組，並查看組內成員名    SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post;GROUP BY與彙總函式一起使用    select post,count(id) as count from employee group by post;#按照崗位分組，並查看每個組有多少人# 求每個分類的記錄個數mysql> select depart_id,count(name) from employee group by depart_id;+-----------+-------------+| depart_id | count(name) |+-----------+-------------+|         1 |           8 ||         2 |           5 ||         3 |           5 |+-----------+-------------+3 rows in set (0.00 sec)# 將符合類別的所有記錄的名字組合成一項mysql> select depart_id, group_concat(name) from employee group by depart_id;+-----------+------------------------------------------------------------+| depart_id | group_concat(name)                                         |+-----------+------------------------------------------------------------+|         1 | egon,alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,成龍 ||         2 | 歪歪,丫丫,丁丁,星星,星星                                   ||         3 | 張野,程咬金,程咬銀,程咬銅,程咬鐵                           |+-----------+------------------------------------------------------------+3 rows in set (0.00 sec)# 求每個類別中的最大值mysql> select depart_id, max(salary) from employee group by depart_id;+-----------+-------------+| depart_id | max(salary) |+-----------+-------------+|         1 |  1000000.31 ||         2 |     3000.29 ||         3 |    20000.00 |+-----------+-------------+3 rows in set (0.00 sec)

1. 彙總函式

#強調：彙總函式彙總的是組的內容，若是沒有分組，則預設一組樣本：    SELECT COUNT(*) FROM employee;    SELECT COUNT(*) FROM employee WHERE depart_id=1;    SELECT MAX(salary) FROM employee;    SELECT MIN(salary) FROM employee;    SELECT AVG(salary) FROM employee;    SELECT SUM(salary) FROM employee;    SELECT SUM(salary) FROM employee WHERE depart_id=3;

• 小練習

1. 查詢崗位名以及崗位包含的所有員工名字mysql> select post, group_concat(name) from emp group by post;2. 查詢崗位名以及各崗位內包含的員工個數mysql> select post, count(id) from emp group by post;3. 查詢公司內男員工和女員工的個數mysql> select sex, count(id) from emp group by sex;4. 查詢崗位名以及各崗位的平均薪資mysql> select post, avg(salary) as ‘平均薪資‘ from emp group by post;5. 查詢崗位名以及各崗位的最高薪資select post, max(salary) as ‘最高薪資‘ from emp group by post;6. 查詢崗位名以及各崗位的最低薪資select post, max(salary) as ‘最高薪資‘ from emp group by post;7. 查詢男員工與男員工的平均薪資，女員工與女員工的平均薪資select sex, avg(salary) as ‘平均薪資‘ from emp group by sex;

1. HAVING過濾

#！！！執行優先順序從高到低：where > group by > having #1. Where 發生在分組group by之前，因而Where中可以有任意欄位，但是絕對不能使用彙總函式。#2. Having發生在分組group by之後，因而Having中可以使用分組的欄位，無法直接取到其他欄位,可以使用彙總函式

• 小練習

1. 查詢各崗位內包含的員工個數小於2的崗位名、崗位內包含員工名字、個數select post, group_concat(name) as "員工姓名", count(id) as "員工數量" from emp group by post having count(id) <2;3. 查詢各崗位平均薪資大於10000的崗位名、平均工資select post, avg(salary) from emp group by post having avg(salary)>10000 ;4. 查詢各崗位平均薪資大於10000且小於20000的崗位名、平均工資select post, avg(salary) from emp group by post;select post, avg(salary) from emp group by post having avg(salary) in (10000, 20000) ;

1. 查詢排序:ORDER BY

按單列排序    SELECT * FROM employee ORDER BY salary;    SELECT * FROM employee ORDER BY salary ASC;    SELECT * FROM employee ORDER BY salary DESC;按多列排序:先按照age排序，如果年紀相同，則按照薪資排序    SELECT * from employee        ORDER BY age,        salary DESC;

• 小練習

1. 查詢所有員工資訊，先按照age升序排序，如果age相同則按照hire_date降序排序select * from emp order by age desc, hire_date desc;2. 查詢各崗位平均薪資大於10000的崗位名、平均工資,結果按平均薪資升序排列# 1.對post進行分組;2.having篩選工資;3.工資排序;select post, avg(salary) as ‘平均工資‘ from emp group by post having avg(salary)>10000 order by avg(salary);3. 查詢各崗位平均薪資大於10000的崗位名、平均工資,結果按平均薪資降序排列

1. 限制查詢的記錄數:LIMIT

樣本：    SELECT * FROM employee ORDER BY salary DESC         LIMIT 3;                    #預設初始位置為0         SELECT * FROM employee ORDER BY salary DESC        LIMIT 0,5; #從第0開始，即先查詢出第一條，然後包含這一條在內往後查5條    SELECT * FROM employee ORDER BY salary DESC        LIMIT 5,5; #從第5開始，即先查詢出第6條，然後包含這一條在內往後查5條

• 小練習

1. 分頁顯示，每頁5條select * from emp limit 5;select * from emp limit 5, 5;select * from emp limit 10, 5;

1. 使用Regex查詢

SELECT * FROM employee WHERE name REGEXP ‘^ale‘;SELECT * FROM employee WHERE name REGEXP ‘on$‘;SELECT * FROM employee WHERE name REGEXP ‘m{2}‘;小結：對字串匹配的方式WHERE name = ‘egon‘;  # 準確查詢WHERE name LIKE ‘yua%‘;  # 模糊查詢WHERE name REGEXP ‘on$‘;  # 模糊查詢

• 小練習

查看所有員工中名字是jin開頭，n或者g結果的員工資訊select * from emp where name regexp "^jin.*[ng]$";

mysql表操作之資料操作