資料庫MySQL/mariadb知識點——操作篇(4)資料動作陳述式

標籤：顯示   根據   adb   選擇   ddr   gen   平均數   max   ext   

INSERT插入資料

單挑記錄插入

INSERT INTO tb_name (col1,col2,...) VALUES (val1,val2,...);

樣本：

insert into tablename(欄位1名稱,欄位2名稱,......)values(欄位1值,欄位2值,...)

MariaDB [testdb]> INSERT students(id,name,ages,gender) VALUES (1,‘tom‘,26,‘M‘);MariaDB [testdb]> INSERT students(name,ages,gender) VALUES (‘jerry‘,19,‘M‘); MariaDB [testdb]> INSERT students(name,ages,gender) VALUES (‘maria‘,19,‘M‘);MariaDB [testdb]> INSERT students SET name=‘ouyangfeng‘,ages=56,gender=‘M‘;

多條記錄插入

INSERT INTO tb_name (col1,col2,...) VALUES (val1,val2,...)[,(val1,val2,...),...];

MariaDB [testdb]> INSERT students(name,ages,gender) VALUES (‘xiaolongnv‘,18,‘F‘),(‘dongfangbubai‘,28,‘F‘);

MariaDB [testdb]> SELECT * FROM students;+----+---------------+------+--------+| id | name          | ages | gender |+----+---------------+------+--------+|  1 | tom           |   26 | M      ||  2 | jerry         |   19 | M      ||  3 | maria         |   19 | M      ||  4 | xiaolongnv    |   18 | F      ||  5 | dongfangbubai |   28 | F      ||  6 | ouyangfeng    |   56 | M      |+----+---------------+------+--------+

 從其他表查詢資料儲存到此表中

MariaDB [testdb]> ALTER TABLE students ADD address TEXT;  #加個欄位做測試用

MariaDB [testdb]> INSERT students(name,address) SELECT user,host FROM mysql.user;

MariaDB [testdb]> SELECT * FROM students;+----+---------------+------+--------+-----------+| id | name          | ages | gender | address   |+----+---------------+------+--------+-----------+|  1 | tom           |   26 | M      | NULL      ||  2 | jerry         |   19 | M      | NULL      ||  3 | maria         |   19 | M      | NULL      ||  4 | xiaolongnv    |   18 | F      | NULL      ||  5 | dongfangbubai |   28 | F      | NULL      ||  6 | ouyangfeng    |   56 | M      | NULL      ||  7 | root          |    0 | NULL   | 127.0.0.1 ||  8 | root          |    0 | NULL   | ::1       ||  9 |               |    0 | NULL   | centos7   || 10 | root          |    0 | NULL   | centos7   || 11 |               |    0 | NULL   | localhost || 12 | root          |    0 | NULL   | localhost |+----+---------------+------+--------+-----------+

UPDATE 修改資料

UPDATE tbl_name SET col1=value1,col2=value2,... WHERE col=value;

修改資料，主要通過where字句給定修改反而，而where字句的樣本可以參考select常用語句；

樣本：

MariaDB [testdb]> UPDATE students SET gender=‘F‘ WHERE id=3;

DELETE 刪除資料

刪除語句比較簡單，主要是通過where字句給定刪除範圍，而where字句的樣本可以參考select語句，刪除前請確定給出條件沒有任何問題，在不確定的情況下不要隨意刪除資料。

MariaDB [testdb]> DELETE FROM students WHERE name=‘‘;  #刪除名字為空白的記錄MariaDB [testdb]> TRUNCATE TABLE user;  #情況表記錄

注意：一定要有限制條件（WHERE | LIMIT），否則將修改所有行的指定欄位

SELECT 資料查詢基礎查詢1、選擇

樣本：

查詢maria的資訊

MariaDB [testdb]> SELECT * FROM students WHERE name=‘maria‘;

查詢2到5號學生的資訊

MariaDB [testdb]> SELECT * FROM students WHERE id BETWEEN 2 AND 5;

查詢jerry和xiaolongnv的資訊

MariaDB [testdb]> SELECT * FROM students WHERE name IN (‘jerry‘,‘xiaolongnv‘);

查詢年齡不為空白的資訊

MariaDB [testdb]> SELECT * FROM students WHERE gender IS NOT NULL;

查詢姓名中包含‘o‘的資訊

MariaDB [testdb]> SELECT * FROM students WHERE name LIKE ‘%o%‘;　

2、投影

查詢時給欄位添加別名

MariaDB [testdb]> SELECT user AS 使用者,host AS 主機,password AS 密碼 FROM mysql.user;

3、分組

樣本

查詢男生、女生年齡的平均值

MariaDB [testdb]> SELECT gender,AVG(ages) FROM students GROUP BY gender;

只顯示男生的平均年齡資訊

MariaDB [testdb]> SELECT gender,AVG(ages) FROM students GROUP BY gender HAVING gender=‘M‘;

4、排序

樣本

按年齡排序，倒序顯示

MariaDB [testdb]> SELECT * FROM students ORDER BY ages DESC;

按年齡排序，過濾年齡大於0的，正序排序，取前三條記錄

MariaDB [testdb]> SELECT * FROM students WHERE ages > 0 ORDER BY ages LIMIT 3;

5、常用參數

 1 AS：別名 2 WHERE：指明過濾條件以實現“選擇”的功能 3 +, -, *, /, %：算術操作符 4 =, !=, <>, >, <, >=, <=：比較操作符 5 BETWEEN min_num AND max_num：在min_num和max_mun之間 6 IN (element1,element2,...)：在element...中的 7 IS NULL：為空白 8 IS NOT NULL：不為空白 9 LIKE：做匹配，像。。。10     %：任意長度的任一字元11     _：單個任一字元12 RLIKE：Regex，不建議用13 REGEXP：同上14 NOT, AND, OR, XOR：邏輯操作符15 GROUP BY：根據指定的條件把查詢結果進行“分組”以用於做“彙總”運算16 AVG() 平均數17 MAX() 最大數18 MIN() 最小數19 COUNT() 統計20 SUM() 求和21 HAVING ：對分組彙總運算後的結果指定過濾條件。類似WHERE的作用，但只能在分組中使用22 ORDER BY：排序23 ASC：正序，預設24 DESC：倒序25 -KEYWORD：在排序時在關鍵字前加-可以避免把NULL排在前邊26 LIMIT [[offset,]row_count]：對查詢的結果進行輸出行數數量限制

參數

 

多表查詢

在開始多表查詢之前，我們對之前的表進行下手動擴充

MariaDB [testdb]> DELETE FROM students WHERE id BETWEEN 7 AND 12;MariaDB [testdb]> CREATE TABLE score (id TINYINT(2) UNSIGNED AUTO_INCREMENT PRIMARY KEY,score TINYINT(3));MariaDB [testdb]> ALTER TABLE students ADD sid TINYINT(2); MariaDB [testdb]> UPDATE students SET sid=6 WHERE id=6;MariaDB [testdb]> INSERT score SET score=87;MariaDB [testdb]> SELECT * FROM students;+----+---------------+------+--------+---------+------+| id | name          | ages | gender | address | sid  |+----+---------------+------+--------+---------+------+|  1 | tom           |   26 | M      | NULL    |    1 ||  2 | jerry         |   19 | M      | NULL    |    2 ||  3 | maria         |   19 | F      | NULL    |    3 ||  4 | xiaolongnv    |   18 | F      | NULL    |    4 ||  5 | dongfangbubai |   28 | F      | NULL    |    5 ||  6 | ouyangfeng    |   56 | M      | NULL    |    6 |+----+---------------+------+--------+---------+------+MariaDB [testdb]> SELECT * FROM score;   +----+-------+| id | score |+----+-------+|  1 |    99 ||  2 |    98 ||  3 |    88 ||  4 |    68 ||  5 |    78 ||  6 |    87 |+----+-------+

 

 

 

1、倆張表取交集

MariaDB [testdb]> SELECT * FROM students AS s,score AS o WHERE s.sid=o.id;

2、JOIN ON：交叉串連

MariaDB [testdb]> SELECT * FROM students JOIN score;

3、INNER JOIN ON：內串連

MariaDB [testdb]> SELECT t.name,s.score FROM students AS t INNER JOIN score AS s ON t.sid=s.id;+---------------+-------+| name          | score |+---------------+-------+| tom           |    99 || jerry         |    98 || maria         |    88 || xiaolongnv    |    68 || dongfangbubai |    78 || ouyangfeng    |    87 |+---------------+-------+

4、LEFT OUTER JOIN ON：左外串連

MariaDB [testdb]> SELECT t.name,s.score FROM students AS t LEFT JOIN score AS s ON t.sid=s.id;  #左外串連+---------------+-------+| name          | score |+---------------+-------+| tom           |    99 || jerry         |    98 || maria         |    88 || xiaolongnv    |    68 || dongfangbubai |    78 || ouyangfeng    |    87 |+---------------+-------+

5、RIGHT OUTER JOIN ON：右外串連

MariaDB [testdb]> SELECT * FROM students AS t RIGHT JOIN score AS s ON t.sid=s.id;  #右外串連

6、UNION ON：完全外串連

MariaDB [testdb]> SELECT name,address FROM students UNION    -> SELECT user,host FROM mysql.user;+---------------+-----------+| name          | address   |+---------------+-----------+| tom           | NULL      || jerry         | NULL      || maria         | NULL      || xiaolongnv    | NULL      || dongfangbubai | NULL      || ouyangfeng    | NULL      || root          | 127.0.0.1 || root          | ::1       ||               | centos7   || root          | centos7   ||               | localhost || root          | localhost |+---------------+-----------+

7、自串連

MariaDB [testdb]> ALTER TABLE students ADD tid TINYINT(2);  #再加一個tid欄位MariaDB [testdb]> SELECT * FROM students;+----+---------------+------+--------+---------+------+------+| id | name          | ages | gender | address | sid  | tid  |+----+---------------+------+--------+---------+------+------+|  1 | tom           |   26 | M      | NULL    |    1 |    2 ||  2 | jerry         |   19 | M      | NULL    |    2 |    1 ||  3 | maria         |   19 | F      | NULL    |    3 |    4 ||  4 | xiaolongnv    |   18 | F      | NULL    |    4 |    5 ||  5 | dongfangbubai |   28 | F      | NULL    |    5 |    4 ||  6 | ouyangfeng    |   56 | M      | NULL    |    6 |    4 |+----+---------------+------+--------+---------+------+------+

MariaDB [testdb]> SELECT s1.name AS studentname,s2.name AS teachername FROM students AS s1 INNER JOIN students AS s2 ON s1.id=s2.tid;+---------------+---------------+| studentname   | teachername   |+---------------+---------------+| jerry         | tom           || tom           | jerry         || xiaolongnv    | maria         || dongfangbubai | xiaolongnv    || xiaolongnv    | dongfangbubai || xiaolongnv    | ouyangfeng    |+---------------+---------------+

 

子查詢

子查詢：在查詢語句嵌套著查詢語句，效能較差，基於某語句的查詢結果再次進行的查詢

1、用在WHERE子句中的子查詢

• 用於比較運算式中的子查詢；子查詢僅能返回單個值

樣本：

查詢大於平均年齡的同學

MariaDB [testdb]> SELECT name,ages FROM students WHERE ages > (SELECT AVG(ages) FROM students); 

• 用於IN中的子查詢：子查詢應該單鍵查詢並返回一個或多個值從構成列表

2、用於FROM子句中的子查詢

SELECT tb_alias.col1,... FROM (SELECT clause) AS tb_alias WHERE Clause;

資料庫MySQL/mariadb知識點——操作篇(4)資料動作陳述式