MySQL 基本操作

標籤：檢索   between   子查詢   顯示   regex   div   使用   w命令   更新   

1.串連資料庫

查看可用資料庫使用show命令：

SHOW DATABASES;

串連資料庫使用use命令：

USE test;

擷取資料庫內的所有表：

SHOW TABLES;

查看錶結構：

SHOW COLUMNS FROM test;

2.檢索資料

檢索某一列或某幾列：

SELECT name FROM products;

檢索所有列：

SELECT * FROM products;

如果只想顯示一列中的不同數值，可以用distinct關鍵字：

SELECT DISTINCT name FROM products;

限定結果的數量，用LIMIT關鍵字：

SELECT DISTINCT name FROM products LIMIT 5;SELECT DISTINCT name FROM products LIMIT 0, 5; 從第1行開始的5個（從0開始計數）

3.排序

排序使用order by子句：

SELECT name, id, price FROM products ORDER BY name, id;SELECT name, id, price FROM products ORDER BY name, id DESC; 預設是升序排列，加上DESC設定為對id降序排列

4.過濾

過濾資料使用where子句：

SELECT id FROM products WHERE id = 2;

也可以過濾一個範圍：

SELECT name, price FROM products WHERE price BETWEEN 5 AND 10;

尋找值存在的記錄：

SELECT id FROM products WHERE name IS NULL;

也可以使用多重where子句，中間用and或者or相連。

如果要查詢的記錄有一個明確的範圍，則可以用in關鍵字或者not in關鍵字。

SELECT name FROM products WHERE id IN (1, 2, 3);SELECT name FROM products WHERE id NOT IN (1, 2, 3);

5.萬用字元和Regex

使用LIKE關鍵字來進行萬用字元匹配。%表示多個字元，_表示單個字元。

SELECT id, name FROM products WHERE name LIKE ‘s%e‘; 尋找以s開頭、e結尾的name記錄SELECT id, name FROM products WHERE name LIKE ‘_s‘; 尋找兩個單詞且後一個字母是s的記錄

使用REGEXP關鍵字來進行正則匹配。 

SELECT id, name FROM products WHERE name REGEXP ‘.00‘; 輸出所有包含00的記錄

‘1|2‘表示匹配1或2，[123]表示匹配1、2、3之一，[1-9]表示匹配1-9之一，.表示匹配一個字元，*表示0個或多個匹配，+表示1個或多個匹配，？表示0個或1個匹配。

{n}表示匹配n個字元，{n,}表示匹配n個以上字元，{n,m}表示匹配n-m個字元。

\\表示轉義符。

^表示文本的開始，$表示文本的結尾。而[^1-3]表示匹配1-3以外的字元。

萬用字元匹配整個欄位，而Regex匹配欄位中的一部分。

6.拼接

拼接將兩個欄位的內容組合起來，用concat方法：

SELECT Concat(name, ‘(‘, country, ‘)‘) AS title FROM product;

也可以對兩個欄位的值進行計算：

SELECT price, number, price*number AS expense FROM products;

7.函數

常用函數包括Date()、Time()、Length()、LTrim()、RTrim()、Upper()、Lower()、AVG()、SUM()、COUNT()、MIN()、MAX()等。

SELECT COUNT(*) AS num, MIN(price) AS min_price, MAX(price) AS max_price, AVG(price) AS avg_price FROM products;

8.分組

分組用group by方法實現。group by應位於where之後，order by之前。

SELECT id, COUNT(*) AS num FROM products GROUP BY id;

對於資料行，用where進行過濾，而對於分組則用having進行過濾，即where在資料分組前進行過濾，而having在資料分組後進行過濾：

SELECT id, COUNT(*) AS num FROM products GROUP BY id HAVING COUNT(*) > 10;

SELECT id, SUM(num*price) AS expense FROM products WHERE id > 10 GROUP BY id HAVING SUM(num*price) > 50 ORDER BY expense; 

9.子查詢

將先後進行的兩個查詢寫在一起：

SELECT id FROM customers WHERE num IN (SELECT num FROM products WHERE price = 10);

或者將子查詢作為一個新欄位：

SELECT name, id, (SELECT COUNT(*) FROM products WHERE products.id = customers.id) AS product FROM customers;

10.連接表

即將兩張表進行連接查詢。連接查詢的兩張表一定要具有連接關係，否則輸出的結果為兩張表的笛卡爾積：

SELECT vend_name, prod_name, prod_price FROM vendors, products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name;

也可以寫作：

SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;

上述連接方式稱為內部連接。此外，還有自連接和外部連接。

自連接即在select語句中引用自身不止一次。如下所示：

SELECT prod_id, prod_name FROM products WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = 5);

上面為子查詢，找出所有prod_id為5的商品的供應商id，再尋找這一供應商的商品id和name。寫成連接查詢如下：

SELECT p1.prod_id, p1.prod_name FROM products AS p1, products AS p2 WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 5;

 11.組合查詢

使用union將兩個或以上select查詢語句聯合起來。兩條select語句查詢的列必須相同：

SELECT id, name, price FROM products WHERE price >= 5 UNION SELECT id, name, price FROM products WHERE id > 5;

12.資料插入

資料插入使用insert into方法。如果插入的資料按照列的次序排列，也可以省略(id, name, price)，但最好寫出：

INSERT INTO products (id, name, price) VALUES (9, ‘new‘, 300);

如需插入多條，直接在(9, ‘new‘, 300)後面加上新的條目，用逗號隔開。

也可以將select查詢出的資料插入。

INSERT INTO products (id, name, price) SELECT id, name, price FROM newproducts;

13.更新和刪除

更新用update方法：

UPDATE products SET price = 100 WHERE id = 8;

也可以用update刪除某一條目的部分資料：

UPDATE products SET name = NULL WHERE id = 6;

刪除某條資料用delete方法：

DELETE FROM products WHERE id = 7;

想刪除整張表，可以用trucate方法：

TRUNCATE TABLE test;

14.建立表

CREATE TABLE products(id  int  NOT NULL AUTO_INCREMENT,name  char(50)  NOT NULL,price  float  NOT NULL,PRIMARY KEY (id))ENGINE = InnoDB;

表中主鍵的值必須唯一，但是主鍵可以由多列共同確定。

可以設定預設值：DEFAULT 0之類。

引擎主要有InnoDB、MyISAM、MEMORY三種。InnoDB支援事務，不支援全文本搜尋；MyISAM支援全文本搜尋，不支援事務；MEMORY與MyISAM相同，但是資料存放區於記憶體中，用於暫存資料表。

15.更新和刪除表

更新表結構用alter方法：

ALTER TABLE products ADD vend_id int;ALTER TABLE products DROP COLUMN vend_id;

也可以用alter方法更新外鍵：

ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (prod_id) REFERENCES vendors (prod_id);

刪除表用drop方法：

DROP TABLE products;

重新命名用rename方法：

RENAME TABLE products TO newproducts;

 

MySQL 基本操作