1、數(shù)據(jù)庫基礎(chǔ)操作語句

鏈接數(shù)據(jù)庫：mysql -u root -p 123456;

選擇數(shù)據(jù)庫：USE database_name;

顯示可以數(shù)據(jù)庫：SHOW DATABASES;

顯示數(shù)據(jù)庫所有表：SHOW TABLES;

顯示表的列：SHOW COLUMNS FROM table_name;

2、檢索數(shù)據(jù)

檢索單個列：SELECT prod_name FROM table_name;

檢索多個列：SELECT prod_id,prod_name,prod_price FROM table_name;

檢索所有列：SELECT * FROM table_name;

檢索不同的行：SELECT DISTINCT prod_id FROM table_name;

現(xiàn)在結(jié)果：SELECT prod_name FROM table_name LIMIT 5; 至多返回前5行

SELECT prod_name FROM table_name LIMIT 5,5; 返回從5行開始的5行

使用完全限定的表面：SELECT products.prod_nam FROM products;

3、排序檢索數(shù)據(jù)

排序數(shù)據(jù)：SELECT prod_name FROM products ORDER BY prod_name;

按多個列排序：SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price,prod_name;

指定排序方向：SELECT prod_name FROM products ORDER BY prod_name DESC; 默認(rèn)升序排序，DESC降序排序

SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price DESC,prod_name;

4、過濾數(shù)據(jù)

使用WHERE字句：SELECT prod_name FROM products WHERE prod_price = 2.5;

WHERE字句操作符：= ?<>(不等于) != ? < ? > ?<= ?>= ?BETWEEN

范圍值檢查：SELECT prod_name FROM products WHERE prod_price BETWEEN 5 AND 10;

空值檢查：SELECT prod_name FROM products WHERE prod_price IS NULL;

5、數(shù)據(jù)過濾

AND操作符：SELECT prod_name FROM products WHERE prod_price < 10 AND prod_id = 100;

OR操作符：SELECT prod_name FROM products WHERE prod_price = 10 OR prod_price = 20;

IN操作符：SELECT prod_name FROM products WHERE prod_price IN (10,20,30);

NOT操作符：SELECT prod_name FROM products WHERE prod_price NOT?IN (10,20,30);

6、用通配符進(jìn)行過濾

百分號(%)通配符：SELECT prod_name FROM products WHERE prod_name LIKE '%jet%'; ?%匹配任何個數(shù)字符

下滑先(_)通配符：SELECT prod_name FROM products WHERE prod_name LIKE '_ ton anvil'; ?_匹配一個字符

7、用正則表達(dá)式進(jìn)行搜索

基本字符匹配：SELECT prod_name FROM products WHERE prod_name REGEXP '1000';

正則特殊字符：.(任意匹配一個字符) |(或) []

8、計算字段

拼接字段：SELECT Concat(ventor_name, '(', ?ventor_country, ')') FROM ventors ORDER BY ventor_name;

去掉有邊的所有空格：SELECT?Concat(RTrim(ventor_name), '(', ?RTirm(ventor_country), ')') FROM ventors ORDER BY ventor_name; ?(LTrim去掉左邊空格，Trim去掉左右邊空格)

使用別名：SELECT?Concat(ventor_name, '(', ?ventor_country, ')') AS ventor_title FROM ventors ORDER BY ventor_name;

執(zhí)行算數(shù)運(yùn)算：SELECT prod_id,quantity,item_price,quantity*item_price AS expanded_price FROM orderitems WHERE order_num = 20005; ?(支持 + - * /)

9、使用數(shù)據(jù)處理函數(shù)

去除右邊列值右邊空格：RTirm()

去除左邊列值右邊空格：LTirm()

去除左右邊列值右邊空格：Tirm()

將文本轉(zhuǎn)換為大寫：Upper()

將穩(wěn)步轉(zhuǎn)換為小寫：Lower()

返回串的長度：Length()

返回串左邊的字符：Left()

返回串右邊的字符：Right()

將任何文本串轉(zhuǎn)為描述其語言表示的字母數(shù)字模式算法：Soundex()

日期和時間處理函數(shù)

增加一個日期(天、周等)：AddDate()

增加一個時間(時、分等)：AddTime()

返回當(dāng)前日期：CurDate()

返回當(dāng)前時間：CurTime()

返回日期時間的日期部分：Date()

計算連個日期之差：DateDiff()

高度靈活的日期運(yùn)算函數(shù)：Date_Add()

返回一個格式化的日期或時間串：Date_Format()

返回一個日期的年數(shù)部分：Year()

返回一個日期的月數(shù)部分：Month()

返回一個日期的天數(shù)部分：Day()

對于一個日期，返回對應(yīng)的星期幾：DayOfWeek()

返回一個時間的小時部分：Hour()

返回一個時間的分鐘部分：Minute()

返回一個時間的秒鐘部分：Second()

返回一個日期時間的時間部分：Time()

返回當(dāng)前日期和時間：Now()

10、匯總數(shù)據(jù)-聚集函數(shù)

返回某列的平均值：AVG()

SELECT AVG(prod_price) AS avg_price FROM products;

返回某列的行數(shù)：COUNT()

SELECT COUNT(*) AS prod_count FROM products;

返回指定列的最大值：MAX()

SELECT MAX(prod_price) AS max_price FROM products;

返回指定列的最小值：MIN()

SELECT MIN(prod_price) AS min_price FROM products;

返回某列之和：SUM()

SELECT SUM(prod_price) AS sum_price FROM products;

聚集不同值：DISTINCT

SELECT AVG(DISTINCT prod_price) AS avg_price FROM products;

11、分組數(shù)據(jù)

創(chuàng)建分組：GROUP BY

SELECT ventor_id,COUNT(*) AS num_prods FROM products GROUP BY ventor_id;

過濾分組：HAVING

SELECT ventor_id,COUNT(*) AS num_prods FROM products?GROUP BY?ventor_id HAVING COUNT(*)>2;

SELECT字句順序

SELECT ... FROM ?... ?WHERE ?... ?GROUP BY ?... ?HAVING ?... ?ORDER BY ?... ?LIMIT

12、使用子查詢

利用子查詢進(jìn)行過濾

SELECT cust_name FROM customers WHERE cust_id IN (

SELECT cust_id FROM orders ?WHERE order_num IN (

SELECT order_num FROM orderitems WHERE order_id = 'TNT2'

)

);

作為計算字段使用子查詢

SELECT cust_name,(

SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id

) AS orders FROM customers ORDER BY cust_name;

13、聯(lián)結(jié)表

等值聯(lián)結(jié)

SELECT ventor_name,prod_name,prod_price FROM ventors,products WHERE ventors.ventor_id = products.ventor_id ORDER BY ventor_name,prod_name;

SELECT ventor_name,prod_name,prod_price FROM ventors INNER JOIN products ON ventors.ventor_id= products.ventor_id;

自聯(lián)結(jié)

SELECT prod_id,prod_name FROM products WHERE vend_id = (

SELECT vend_id FROM products WHERE prod_id = 'DTNTR'

);

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 = 'DTNTR';

外部聯(lián)結(jié)

SELECT customers.cust_id,orders.order_num FROM customers RIGHT OUTER JOIN orders IN customers.cust_id=orders.cust_id;

SELECT customers.cust_id,orders.order_num FROM customers LEFT OUTER JOIN orders IN customers.cust_id=orders.cust_id;

14、組合查詢

創(chuàng)建組合查詢

SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price <= 5

UNION

SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN (1001,1002);

15、插入數(shù)據(jù)

插入完整的行

INSERT INTO customers(cust_id,cust_name_cust_age)?VALUES(10002,'xxx',28);

插入多行

INSERT INTO?customers(cust_id,cust_name_cust_age)?VALUES(10002,'xxx',28),(10003,'yyy',18);

插入檢索出的數(shù)據(jù)

INSERT INTO customers(cust_id,cust_name_cust_age) SELECT?cust_id,cust_name_cust_age FROM custnew;

16、更新和刪除數(shù)據(jù)

UPDATE customers SET cust_name='ddd',age='40 WHERE cust_id = 100001;

DELETE FROM customers WHERE cust_id = 100001;

17、創(chuàng)建和操縱表

創(chuàng)建

CREATE TABLE customers(

cust_id ?int ?NOT NULL ?AUTO_INCREMENT,

cust_name ?char(50) ?NOT NULL,

cust_age ?int NOT NULL DEFAULT 10,

PRIMARY KEY(cust_id)

)ENGINE=InnoDB;

更新

給表插入一個列：ALTER TABLE vendors ADD vend_phone CHAR(20);

刪除一個列：ALTER TABLE vendors DROP vend_phone;

刪除表：DROP TABLE vendors;

重命名表：RENAME TABLE vendors TO vendors1;

18、使用視圖

利用視圖簡化復(fù)雜的聯(lián)結(jié)

CREATE VIEW productcustomers AS

SELECT cust_name,cust_contact,pro_id FROM customers, orders, orderitems WHERE customers.cust_id=orders.cust_id AND orders.order_num=orderitems.order_num;

SELECT cust_name,cust_contact FROM productcustomers WHERE prod_id='100002';

19、使用存儲過程

執(zhí)行存儲過程

CALL productpricing(@pricelow,@prichigh,@priceaverage);

創(chuàng)建存儲過程

DELIMITER //

CREATE PROCEDURE productpricing()

BEGIN

SELECT AVG(prod_price) AS priceaverage FROM products;

END //

DELIMITER ;

刪除存儲過程：DROP PROCEDURE?productpricing;

傳入返回參數(shù)

DELIMITER?//

CREATE PROCEDURE productpricing(OUT p1 DECIMAL(8,2),OUT p2 DECIMAL(8,2),OUT p3?DECIMAL(8,2))

BEGIN

SELECT MIN(prod_price)

INTO p1

FROM products;

SELECT MAX(prod_price)

INTO p2

FROM products;

SELECT AVG(prod_price)

INTO p3

FROM products;

END//

DELIMITER ;

CALL?productpricing(@max,@min,@avg);

SELECT @max,@min,@avg;

傳入使用參數(shù)

DELIMITER?//

CREATE PROCEDURE ordertotal(IN onmuber INT, OUT ototal DECIMAL(8,2))

BEGIN

SELECT SUM(item_price*quantity) FROM orderitems WHERE order_num = onumber INTO ototal;

END//

DELIMITER ;

CALL ordertotal(2005,@ototal);

檢測存儲過程：SHO CREATE PROCEDURE ordertotal;

20、使用觸發(fā)器

只支持DELETE/INSERT/UPDATE語句，BEFORE和AFTER事件

創(chuàng)建觸發(fā)器：CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'prodcuct add';

刪除觸發(fā)器：DROP TRIGGER?newproduct;

INSERT觸發(fā)器：可引用一個名為NEW的虛擬表，訪問被插入的值，在BEFORE INSERT 觸發(fā)器中，NEW中的值也可以被更新，對于AUTO_INCREMENT列，NEW在INSERT執(zhí)行之前包含0，在INSERT執(zhí)行之后包含新的自動生成值

CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num;

DELETE觸發(fā)器：可引用一個名為OLD的虛擬表，訪問被刪除的行，OLD中的值全都是只讀的，不能更新

CREATE TRIGGER deleteorder BEFORE DELETE ON orders FOR EACH ROW

BEGIN

INSERT INTO archive_order(order_num,order_date,cust_id) VALUE (OLD.order_num,OLD.order_date,OLD.cust_id);

END;

由于某種原因，BEGIN END 內(nèi)執(zhí)行失敗，DELETE本身將被放棄。

UPDATE觸發(fā)器：可引用一個名為OLD的虛擬表訪問以前的值，引用一個名為NEW的虛擬表訪問更新的值；在BEFORE UPDATE觸發(fā)器中，NEW中的值允許被修改；OLD中的值全都是只讀

CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors FOR EACH ROW SET NEW.vend_state = UPPER(NEW.vend_state);

21、管理事務(wù)處理

關(guān)鍵詞：

事務(wù)(transaction)指一組SQL語句

回退(rollback)指撤銷指定SQL語句的過程

提交(commit)指將未存儲的SQL語句結(jié)果寫入數(shù)據(jù)庫

保留點(diǎn)(savepoint)指事務(wù)處理中設(shè)置的臨時占位符，你可以對它發(fā)布回退(與回退整個事務(wù)處理不同)

事務(wù)的開始：START TRANSACTION

事務(wù)的回退：ROLLBACK

START TRANSACTION;

DELETE FROM orders;

ROLLBACK;

事務(wù)的提交：COMMIT

START TRANSACTION;

DELETE FROM orders;

COMMIT;

設(shè)置保留點(diǎn)：SAVEPOINT delete1;

ROLLBACK TO delete1;

22、安全管理

MySQL用戶賬號和信息存儲在名為mysql的數(shù)據(jù)庫中

USE mysql;

SELECT user FROM user;

創(chuàng)建用戶賬號

CREATE USER benIDENTIFIED BY 'xxxxx';

重命名用戶名

RENAME USER ben TO bforta;

刪除用戶賬號

DROP USER bforta;

設(shè)置訪問權(quán)限

查看用戶權(quán)限：SHOW GRANTS FOR bforta;

設(shè)置權(quán)限：GRANT SELECT ON crashcourse.* TO bforta;

撤銷權(quán)限：REVOKE SELECT ON?crashcourse.* FROM bforta;

GRANT和REVOKE可在幾個層次上控制訪問權(quán)限：

整個服務(wù)器，使用GRANT ALL和REVOKE ALL

整個數(shù)據(jù)庫：使用ON database.*

特定的表：使用ON database.table

權(quán)限：

ALL：除GRANT OPTION外所有權(quán)限

ALTER：使用ALTER TABLE

ALTER ROUTINE：使用ALTER PROCEDURE和DROP PROCEDURE

CREATE：使用CREATE TABLE

CREATE ROUTINE：使用CREATE PROCEDURE

CREATE TEMPORARY：使用CREATE TEMPORARY TABLE

CREATE USER：使用CREATE USER/DROP USER/RENAME USER/REVOKE ALL PRIVILEGES

CREATE VIEW：使用CREATE VIEW

DELETE：使用DELETE

DROP：使用DROP TABLE

EXECUTE：使用CALL和存儲過程

FILE：使用SELECT INTO OUTFILE和LOAD DATA INFILE

GRANT OPITION：使用GRANT和REVOKE

INDEX：使用CREATE INDEX和DROP INDEX

INSERT：使用INSERT

LOCK TABLES：使用LOCK TABLES

PROCESS：使用SHOW FULL PROCESSLIST

RELOAD：使用FLUSH

REPLICATION CLIENT：服務(wù)器位置的訪問

REPLICATION SLAVE：由復(fù)制從屬使用

SELECT：使用SELECT

SHOW DATABASES：使用SHOW DATABASES

SHOW VIEW：使用SHOW CREATE VIEW

SHUTDOWN：使用mysqladmin shutdown(用來關(guān)閉MySQL)

SUPER：使用CHANGE MASTER/KILL/LOGS/PURGE/MASTER/SET GLOBAL，

UPDATE：使用UPDATE

USAGE：無訪問權(quán)限

總結(jié)

以上是生活随笔為你收集整理的mysql组件化_MySql笔记的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。