MySQL資料庫_數據

1、增加數據

　普通的插入

INSERT INTO info1 VALUES(1,'愛因斯坦','相對論')

有一種情況是只插入部分數據，如下：

INSERT INTO info1(id,name) VALUES(2,'愛迪生')

一次插入多條數據

INSERT INTO info1 VALUES(3,'圖靈','人工智慧'),(4,'達芬奇','美學'),(5,'尼采','美學');

查詢數據

SELECT * FROM info1 WHERE id=2;

查詢出來的數據可以作為數據添加到列表中

2、刪除數據

　　刪除部分數據

DELETE FROM info1 WHERE name='小澤瑪利亞';

　　刪除表中全部的數據1，表的結構不變，但不刪除表的自增主鍵

DELETE FROM info1；

　　刪除表中全部的數據2，表的結構不變。刪除表的自增主鍵。它不像delete那樣還要逐條看看再刪，而是直接清空掉，可以聯想到磁碟的格式化。如下：

TRUNCATE info1;

3、修改數據

　　有選擇的修改1

UPDATE info1 SET contribution='哲學、美學' WHERE id=5;

 　　多列時以逗號隔開

UPDATE info1 SET named='達·芬奇',contribution='藝術家、發明家、醫學家' WHERE id=4;

4、查詢數據

4.1、簡單查詢

4.1.1查詢全部

SELECT * FROM info1  

但是*的效率通常比寫表頭慢，正常開發一般不寫*

4.1.2查詢指定項

SELECT named,country FROM info1 

使用別名

SELECT named,contribution AS '貢獻' FROM info1;

 可以進行數據列運算

SELECT named,lifetime-20 FROM info1;

去重覆查詢

SELECT DISTINCT contribution FROM info1;

4.2、條件查詢

4.2.1比較運算符　　>　　<　　>=　　<=　　=　　<>不等於

SELECT * FROM info1 WHERE lifetime>60;

4.2.2NULL關鍵字 查詢

註意這裡不能用=或<>來判斷是否為空

SELECT * FROM info1 WHERE country IS NOT NULL;

SELECT * FROM info1 WHERE country IS NULL;

 查詢空字元串

SELECT * FROM info1 WHERE named='';

 4.2.3邏輯運算符

SELECT * FROM info1 WHERE lifetime=67 AND country='義大利';

SELECT * FROM info1 WHERE lifetime=67 OR country='英國';

SELECT * FROM info1 WHERE NOT(lifetime=67 AND country='義大利');

 4.3區間查詢

SELECT * FROM info1 WHERE lifetime BETWEEN 50 and 60;

4.4、集合查詢

SELECT * FROM info1 WHERE id in(1,3,5);

  4.5 模糊查詢

SELECT * FROM info1 WHERE named LIKE '%愛%'     #包含什麼

SELECT * FROM info1 WHERE named LIKE '愛%'    #以什麼開頭

SELECT * FROM info1 WHERE named LIKE '%愛'    #以什麼結尾

SELECT * FROM info1 WHERE named LIKE '__愛%'    #單個下劃線表示一個字元

4.6 排序查詢

SELECT * FROM info1 ORDER BY lifetime ASC; #ASC可以省略，預設正序

SELECT * FROM info1 ORDER BY lifetime DESC;    #倒序

SELECT * FROM info1 ORDER BY CONVERT(named USING gbk) DESC; #中文排序

#　註意：utf8編碼中，中文不能作為排序依據；gbk支持中文排序

 　　附：查詢編碼集

SHOW VARIABLES LIKE 'char%';

 4.7分組查詢 

SELECT SUM(income),country FROM info1 GROUP BY country;

SELECT SUM(income) AS sums,country FROM info1 GROUP BY country HAVING sums>100000;    #加入條件

SELECT MAX(income),country FROM info1 GROUP BY country;    #最大值

#SELECT AVG(income),country,GROUP_CONCAT(expr) FROM info1 GROUP BY country;    #平均,並且看看跟income與avg數相同的還有誰

WHERE 與 HAVING區別：

　　執行優先順序從高到低：WHERE>GROUP BY>HAVING

4.8分頁查詢　　

SELECT * FROM info1 LIMIT 2,2;

4.9多表聯合查詢

SELECT * FROM person p,dept d WHERE p.dept_id=d.did;

　　如果不符合後面的條件，就不會顯示出來。

4.10多表連接查詢

SELECT * FROM person LEFT JOIN dept ON person.dept_id=dept.did;#左連接查詢：左邊表的數據都出來，即使跟右邊不匹配

#右連接查詢：LEFT換成RIGHT

#內連接查詢：LEFT換成INNER，類似於多表聯合查詢

全連接查詢

SELECT * FROM person LEFT JOIN dept ON person.dept_id=dept.did
UNION
SELECT * FROM person RIGHT JOIN dept ON person.dept_id=dept.did;
#龍洋與夢想部都出來了

 例題

#查詢每個部門中最高工資和最低工資是多少，顯示部門名稱
SELECT MAX(salary),MIN(salary),dname FROM person LEFT JOIN dept on person.dept_id=dept.did GROUP BY dept_id;

 4.11子語句查詢

1、用結果集作為表名來查詢的形式

SELECT * FROM (SELECT * FROM person) AS aaa;

 2、求最大值行的其他列，例如求最大工資的那個人的姓名和薪水

SELECT * FROM person WHERE salary=(SELECT MAX(salary) FROM person);

3、求工資大於平均工資的人員

SELECT * FROM person WHERE salary>(SELECT AVG(salary) FROM person);

4、關鍵字

　　ANY

假設any內部的查詢語句返回的結果個數是三個，如:result1,result2,result3,那麼，

SELECT ...FROM ... WHERE a > ANY(...);
->
SELECT...FROM ... WHERE a > result1 OR a > result2 OR a > result3;

　　ALL

ALL關鍵字與any關鍵字類似，只不過上面的or改成and。即:

SELECT ...FROM ... WHERE a > ALL(...);
->
SELECT ...FROM ... WHERE a > result1 AND a > result2 AND a > result3;

　　EXISTS

SELECT ... FROM table WHERE  EXISTS (subquery);
該語法可以理解為：主查詢(外部查詢)會根據子查詢驗證結果（TRUE 或 FALSE）來決定主查詢是否得以執行。

 4.12其他查詢

　　1、臨時表查詢

　　#查詢高於本部門平均工資的人員

SELECT * FROM person p1,
(SELECT dept_id,AVG(salary) as '平均工資' FROM person GROUP BY dept_id) as p2
WHERE p1.dept_id=p2.dept_id AND p2.`平均工資`<p1.salary; 

　　2、判斷查詢

#根據工資高低，將人員劃分為兩個級別，分別為高端人群和低端人群
#顯示效果：姓名、年齡、性別、工資、級別

SELECT p.name,p.age,p.sex,p.salary, 
IF(salary>10000,'高端人群','低端人群') AS '級別'
FROM person p;

多條件判斷

語法如下

#語法一:
SELECT
    CASE WHEN STATE = '1' THEN '成功'
         WHEN STATE = '2' THEN '失敗'
         ELSE '其他' END 
FROM 表;
 
#語法二:
SELECT CASE age
           WHEN 23 THEN '23歲'
           WHEN 27 THEN '27歲'
           WHEN 30 THEN '30歲'
        ELSE '其他歲' END
FROM person;

#根據工資高低，統計每個部門人員收入情況，劃分為富人、小資、平民、屌絲 四個級別，要求統計四個級別分別有多少人？

SELECT dname,
    SUM(CASE WHEN person.salary>10000 THEN 1 ELSE 0 END) AS '富人',
    SUM(CASE WHEN person.salary BETWEEN 5000 AND 10000 THEN 1 ELSE 0 END) AS '小資',
    SUM(CASE WHEN person.salary BETWEEN 3000 AND 5000 THEN 1 ELSE 0 END) AS '平民',
    SUM(CASE WHEN person.salary<3000 THEN 1 ELSE 0 END) AS '屌絲'
FROM dept,person WHERE dept.did=person.dept_id GROUP BY dept.did;