《MySQL必知必會(huì)》筆記 （附Sqlite基本語(yǔ)句)

mac如何在命令行中進(jìn)入默認(rèn)的MySQL數(shù)據(jù)庫(kù)
  • mysql.server start 開(kāi)啟數(shù)據(jù)庫(kù)服務(wù)器，因?yàn)镸ySQL是客戶－服務(wù)的數(shù)據(jù)庫(kù)操作系統(tǒng)。
  • mysql -u root 使用無(wú)密碼的root用戶名登陸。
mac使用SequelPro連接本地MySQL
  • host:127.0.0.1
  • name:root
  • port:3306(默認(rèn))
  • 其他不用填

一：使用數(shù)據(jù)庫(kù)

一般來(lái)說(shuō)，我們會(huì)將關(guān)鍵字大寫，表名，列名小寫。關(guān)鍵字也可以小寫。mysql語(yǔ)句不區(qū)分大小寫關(guān)鍵字。

(一) 查看當(dāng)前MySQL當(dāng)中的所有數(shù)據(jù)庫(kù)
SHOW DATABASES;
（二）使用某一個(gè)數(shù)據(jù)庫(kù)前，必須指明要使用的數(shù)據(jù)庫(kù)名字
USE databaseName;

查看當(dāng)前數(shù)據(jù)庫(kù)中的所有表

SHOW TABLES;
(三)查看當(dāng)前表中的所有列名
SHOW COLUMNS FROM tableName;

或者

DESCRIBE tableName;

二：檢索數(shù)據(jù)

(一)SELECT
  • 單個(gè)列查詢
SELECT age FROM user;
  • 多列查詢
SELECT name,age FROM user;
  • 查詢所有列
SELECT * FROM user;
  • 過(guò)濾相同的行。比如當(dāng)年齡為20的有五個(gè)人時(shí)，只會(huì)返回一個(gè)20
SELECT DISTINCT age FROM user;
  • 限制結(jié)果。從第四行，開(kāi)始檢索五行。因此如果僅僅是LIMIT 4就表示從第零行，檢索4行。
SELECT age FROM user LIMIT 3,5;
(二)排序檢索數(shù)據(jù)
  • 單個(gè)列排序。也可以使用非檢索的列來(lái)排序。
SELECT age FROM user ORDER BY age;
  • 多個(gè)列排序。先按年齡排序，如果年齡相等，再按照姓名排序。默認(rèn)是ASC升序排列。
SELECT age,name FROM user ORDER BY age,name;
  • 多列排序。先按年齡降序排列，如果年齡相等，就會(huì)再按姓名排序。
SELECT age,name FROM user ORDER BY age DESC,name;
  • 尋找年齡最大的人
SELECT age FROM user ORDER BY age DESC LIMIT 1;

注意上面的LIMIT需要再ORDER BY之后，不然就變成了查詢數(shù)據(jù)庫(kù)的限制，而不是查詢數(shù)據(jù)庫(kù)結(jié)果之后的限制了。

(三)過(guò)濾數(shù)據(jù)

1: WHERE關(guān)鍵字。會(huì)返回所有滿足條件的結(jié)果。ORDER BY應(yīng)該用在WHERE之后。

SELECT age,name FROM user WHERE age=20;
  • WHERE支持的操作符
操作符 說(shuō)明
= 等于
<> 不等于
!= 不等于
< 小于
> 大于
<= 小于等于
>= 大于等于
BETWEEN 在指定的兩個(gè)值之間
  • 使用限定引號(hào)
SELECT age,name FROM user WHERE name <> 'android';
  • 范圍值。在開(kāi)始值和結(jié)束值之間還需要使用AND。
SELECT age,name FROM user WHERE age BETWEEN 18 AND 30;
  • 檢查NULL
SELECT age,name FROM user WHERE name IS NULL;

上面的語(yǔ)句會(huì)返回名字為NULL的行。

  • AND操作符,可以多個(gè)AND聯(lián)合使用
SELECT age,name,address FROM user WHERE address = 'beijing' AND age<=30;

上面這條語(yǔ)句表時(shí)返回地址是beijing，并且年齡小于等于30行。

  • OR操作符。AND優(yōu)先級(jí)高于OR的優(yōu)先級(jí)
SELECT age,name,address FROM user WHERE age=20 OR age=25;

返回年齡是20或者25的行。

  • 使用圓括號(hào)
SELECT age,name,address FROM user WHERE (age=20 OR age = 25) AND address="beijing";

2：IN操作符

SELECT age,name,address FROM user WHERE age IN (20,25,18) ORDER BY name;

返回年齡在20，25，18這三個(gè)歲數(shù)的行，并且按照名字排序。

3：NOT操作符
否定NOT后所跟的條件

SELECT age,name,address FROM user WHERE age NOT IN(50,40);

返回年齡不是50，40的所有行。

4：通配符過(guò)濾
LIKE指示后跟的搜索模式使用通配符而不是直接相等匹配。

SELECT age,name FROM user WHERE name LIKE 'xia%';

上面標(biāo)示返回所有name以xia開(kāi)頭的行。

%標(biāo)示任何字符出現(xiàn)任意次數(shù)。xia%標(biāo)示，不管xia后面跟的是啥，跟了多少個(gè)。
而%xia％,則表示只要包含著xia就會(huì)返回。%不會(huì)匹配NULL。如果這里使用了WHERE age LIKE '20'那么并不會(huì)返回年齡是20的行。但是把LIKE換成REGEXP就可以。

SELECT age,name FROM user WHERE name LIKE 'x_a';

_下劃線只匹配一個(gè)字符。不能匹配0個(gè)或多個(gè)。

5：正則表達(dá)式
使用關(guān)鍵字REGEXP

SELECT age FROM user WHERE name REGEXP 'xia|nie'

返回匹配xia或者nie的行。

SELECT age FROM user WHERE name REGEXP '[xyz]ia'

返回匹配xia或者yia或者zia的行。其中’[xyz]xia‘也可以寫'[x-z]ia';

SELECT age FROM user WHERE name REGEXP '[^xyz]ia'

返回匹配除xia或者yia或者zia的所有的行。

用“\\”加在所有特殊符號(hào)之前進(jìn)行特殊符號(hào)匹配。之所以需要兩個(gè)是因?yàn)镸ySQL解釋一個(gè)，正則表達(dá)式解釋一個(gè)。


^用在集合中否定，否則表示開(kāi)始位置。

不使用數(shù)據(jù)庫(kù)測(cè)試正則表達(dá)式

SELECT 'hello' REGEXP '[0-9]'
(四)在SELECT中創(chuàng)建計(jì)算字段
  • 拼接兩個(gè)列。MySql使用Concat()，其他sql或許可以使用+或者用||來(lái)拼接。
SELECT Concat(name,'(',age,')') FROM user ;

上面就回以name(age)的方式輸出。實(shí)際上Concat是將name，（，age,)一共四個(gè)部分組合到一起的。

SELECT Concat(RTrim(name),'(',age,')') FROM user ;

在上面的基礎(chǔ)上，通過(guò)函數(shù)Trim(name)去掉了name中的左右兩邊空格。

SELECT Concat(RTrim(name),'(',age,')') AS title FROM user ;

將輸出的name(age)以title字段作為列名。

SELECT name,height*weight AS volume FROM user;

將height和weight相乘，然后以別名volum作為字段輸出.

(五)使用函數(shù)
  • Upper()
SELECT Upper(name) AS upName FROM user;

原有的name就會(huì)變成大寫，并以u(píng)pName字段輸出。



假如有字段birthday的存儲(chǔ)為2015-01-02 10:34:23那么我們僅僅知道她是2015-01-02那天生日，那么該怎么匹配呢？

SELECT name,birthday FROM user WHERE Date(birthday) = '2015-01-02';

因?yàn)镈ate()函數(shù)返回日期部分，不返回時(shí)間。

(六)匯總數(shù)據(jù)
SELECT AVG(age) AS avg_age FROM user where address= ‘beijing’;

返回beijing地區(qū)的年齡平均值。

SELECT AVG(DISTINCT age) AS avg_age FROM user where address= ‘beijing’;

返回beijing地區(qū)的不同年齡(當(dāng)有多個(gè)20時(shí)，只取一個(gè)用來(lái)計(jì)算)平均值。

  • COUNT()兩種用法。一種是COUNT(*),總行數(shù)包括NULL行，一種是COUNT(address)，只對(duì)address非NULL的行數(shù)計(jì)數(shù)。
SELECT COUNT(*) AS num_count FROM user;
  • SUM()不僅可以用來(lái)計(jì)算指定列值的和，還可以用來(lái)合計(jì)計(jì)算值。
(七)分組數(shù)據(jù)
SELECT address,COUNT(*) AS count_address FROM user GROUP BY address;

返回按照地址分組的，每個(gè)地址的總行數(shù)。

GROUP BY語(yǔ)句需要在WHERE語(yǔ)句之后，出現(xiàn)在ORDER BY語(yǔ)句之前。

  • 過(guò)濾分組
SELECT address,COUNT(*) AS count_address FROM user GROUP BY address HAVING COUNT(*)>=2;

返回按照地址分組的，每個(gè)地址至少有兩行的每個(gè)地址的總行數(shù)。

  • WHERE 與 HAVING的區(qū)別

where過(guò)濾實(shí)在分組之前，HAVING是在分組后過(guò)濾。where過(guò)濾掉行不在分組中。

SELECT address,COUNT(*) AS count_address FROM user WHERE age >20 GROUP BY address HAVING COUNT(*)>=2;

首先去掉那些年齡小于21的行

然后按照地址分組

最后顯示分組中超過(guò)兩行的



(八) 子查詢

將第一次查詢的結(jié)果，作為第二次查詢的where條件。


將用戶表中的用戶id，作為訂單表中的用戶id進(jìn)行總數(shù)查詢計(jì)算，然后顯示。


(九) 聯(lián)結(jié)表

聯(lián)結(jié)是利用SQL的SELECT能執(zhí)行的最重要的操作。
1:簡(jiǎn)單聯(lián)結(jié)／內(nèi)部聯(lián)結(jié)／等值聯(lián)結(jié)
假設(shè)供應(yīng)商表只有供應(yīng)商名稱和id，產(chǎn)品表有產(chǎn)品信息及產(chǎn)品對(duì)應(yīng)的供應(yīng)商id。那么我們想要所有供應(yīng)商對(duì)應(yīng)的他的產(chǎn)品信息表如下：

SELECT vent_name,prod_name,prod_price FROM vent,product where vent.vent_id=product.vent_id ORDER BY vent_name;

上面的語(yǔ)句也可以轉(zhuǎn)換為

SELECT vernt_name,prod_name,prod_price FROM vent INNER JOIN product ON vent.vent_id=product.vent_id;

當(dāng)需要同時(shí)查閱多個(gè)表時(shí)，既可以使用子查詢，也可以使用聯(lián)結(jié)查詢

字段別名是會(huì)返回到客戶端的，但是表別名則不會(huì)

2:自聯(lián)結(jié)
相同表中多次查詢可以使用自聯(lián)結(jié)。我們?cè)贔ROM中使用表別名。假設(shè)我們要在同一個(gè)產(chǎn)品表中，查看keyboard供應(yīng)商的其它產(chǎn)品。因?yàn)檫@里要先根據(jù)keyboard查處供應(yīng)商，再根據(jù)供應(yīng)商來(lái)查產(chǎn)品表中的其它產(chǎn)品。

SELECT p1.prod_name FROM product AS p1,product AS p2 WHERE p1.vent_id=p2.vent_id AND p2.prod_name='keyboard' ;

3:自然聯(lián)結(jié)
4:外部聯(lián)結(jié)
對(duì)每個(gè)客戶下多少訂單進(jìn)行統(tǒng)計(jì)，同時(shí)還要包括哪些尚未下過(guò)訂單的客戶。

SELECT custom.cust_id,order.order_num FROM custom LEFT OUTER JOIN order ON custom.cust_id=order.cust_id;

OUT JOIN ,必須使用關(guān)鍵字RIGHT 或LEFT關(guān)鍵字指定包括其所在行的表。左右是以O(shè)UT JOIN來(lái)劃分的。

(十)組合查詢

將多個(gè)SELECT語(yǔ)句的結(jié)果，合并為一個(gè)結(jié)果返回。多數(shù)情況下多個(gè)查詢語(yǔ)句組合和多個(gè)where查詢條件完成結(jié)果相同。我們可以直接用UNION關(guān)鍵字將連兩個(gè)查詢語(yǔ)句聯(lián)合起來(lái)。

SELECT name,age,address FROM user WHERE age=20 UNION SELECT name,age,address FROM user WHERE address="beijing";

使用UNION ALL不會(huì)去掉多次查詢出現(xiàn)的相同行，否則自動(dòng)去重。要想排序的話，ORDER BY語(yǔ)句出現(xiàn)在最后一條查詢語(yǔ)句后面。

(十一)全文本搜索

常用的引擎如MyISAM支持全文本搜索，但是InnoDB不支持。

創(chuàng)建索引，可以在創(chuàng)建表時(shí)創(chuàng)建，也可以在之后修改。

INDEX,FULLTEXT,UNIQUE

SELECT describe FROM user WHERE Match(describe) Against ('love');

Match()可以指定多個(gè)列。Against()指定匹配內(nèi)容。這里是在創(chuàng)建表帶時(shí)候，就指定了FULLTEXT(describe)建立了索引。

三：插入數(shù)據(jù)

(一）依賴列的順序以及列的信息來(lái)插入行

這里假定第一列為自增量用戶id，第二列住址可以為NULL,第三列姓名，第四列年齡。如果不指定列名，就要把所有列的值都給出。因?yàn)榈谝涣惺亲栽龅模虼瞬荒苤付ㄖ怠?/p> 

INSERT INTO user VALUES(NULL,NULL,'xiaguangcheng',25);
(二）安全的INSERT語(yǔ)句,可以不依賴列在表中的順序指定列名，但是值必須按照指定的列名順序給出。對(duì)于省略的列，要么在表定義中有默認(rèn)值，要么允許NULL。
INSERT INTO user(name,address,age) VALUES('xiaguangcheng','beijing',25);
(三）降低更新／刪除／添加的優(yōu)先級(jí)

因?yàn)楦滤饕臅r(shí)，會(huì)影響查詢語(yǔ)句，所以可以降低其優(yōu)先級(jí)

INSERT LOW_PRIORITY INTO ...
(四）插入多行

插入多行，可以將兩條插入語(yǔ)句中間用分號(hào)隔開(kāi)。也可以在列名和次序相同的情況下，使用多個(gè)VALUES(),(),();后面的括號(hào)來(lái)添加。

INSERT INTO user(name,address,age) VALUES('xiaguangcheng','beijing',25),('xiaxuele','china',20);
(五）將舊表數(shù)據(jù)導(dǎo)入新表
INSERT INTO user(name,address,age) SELECT name,address,age FROM user_old;

上面并沒(méi)有導(dǎo)入作為自增量的userid，因?yàn)橐Ｕ蟯serid是唯一的。所以導(dǎo)入新數(shù)據(jù)，mysql會(huì)自動(dòng)增加。如果能夠確保舊表與新表的userid不會(huì)重復(fù)，那么也可以導(dǎo)入。當(dāng)然SELECT查詢的列名和INSERT INTO插入的列名可以不一樣。

四：更新刪除數(shù)據(jù)

(一）更新指定行
UPDATE user SET address='henan' WHERE name='xiaguangcheng';

將用戶xiaguangcheng的地址更新為henan。如果沒(méi)有WHERE就會(huì)更新所有列。

UPDATE user SET address='hubei',age=20 WHERE name='xiaguangcheng';

更新多個(gè)列用逗號(hào)隔開(kāi)。當(dāng)更新多個(gè)列時(shí)，如果某個(gè)更新出錯(cuò)，那么這一次操作的所有更新都會(huì)取消。如果在更新出錯(cuò)時(shí)，想要出錯(cuò)之前的更新也生效，就使用IGNORE

UPDATE IGNORE user...

刪除某個(gè)列的值，可以將其設(shè)置為NULL，如果允許為NULL值的話。

(二）刪除數(shù)據(jù)
  • 刪除某行
DELETE FROM user WHERE name='xiaguangcheng';

刪除某列可以使用UPDATE。如果沒(méi)有WHERE就會(huì)刪除所有行。而刪除所有行，還可以使用

TRUNCATE TABLE user

上面的語(yǔ)句實(shí)際上是刪除了原來(lái)的表，重新建了一個(gè)表。

五：創(chuàng)建和操作表

CREATE TABLE user (
user_id int NOT NULL AUTO_INCREMENT,
name char(50) NOT NULL,
address char(100) NULL,
age int NOT NULL DEFAULT 10,
PRIMARY KEY(user_id)
)ENGINE=InnoDB;

數(shù)據(jù)庫(kù)中的表需要手動(dòng)刪除，因此不用擔(dān)心會(huì)覆蓋掉。下面的語(yǔ)句是先檢查是否有同名表，但不檢查結(jié)構(gòu)是否一樣。如果沒(méi)有再創(chuàng)建新表，如果有就不創(chuàng)建。

CREATE TABLE IF NOT EXISTS user (
user_id int NOT NULL AUTO_INCREMENT,
name char(50) NOT NULL,
address char(100) NULL,
age int NOT NULL,
PRIMARY KEY(user_id)
)ENGINE=InnoDB;
  • 主鍵即可以是單列，也可以是多列.不能為NULL值。
PRIMARY KEY(user_id,name)

  • 每個(gè)表只能有一個(gè)AUTO_INCREMENT列。如果該列被手動(dòng)插入了一個(gè)唯一值，那么后續(xù)的增量將開(kāi)始使用該手工插入的值。

  • 如何獲取自動(dòng)增量的值

SELECT last_insert_id();

返回最后一個(gè)AUTO_INCREMENT值。

  • 默認(rèn)值
age int NOT NULL DEFAULT 10

如果沒(méi)有指定，就會(huì)默認(rèn)為10

  • ENGINE 默認(rèn)引擎，如果不顯示指定，會(huì)有默認(rèn)值。

InnoDB支持事務(wù)，不支持全文本搜索。

MyISAM支持全文本搜索，不支持事務(wù)。

  • 更改表
ALTER TABLE user ADD height int NOT NULL DEFAULT 160;

給user表新增一個(gè)height字段。

ALTER TABLE user DROP COLUMN height;

刪除user表中的height字段。

ALTER常用來(lái)定義外鍵。不過(guò)這個(gè)語(yǔ)句在sqlite中并不好用。

ALTER TABLE student ADD CONSTRAINT student_id FOREIGN KEY (user_id) REFERENCES user(user_id);

上面的語(yǔ)句中，student_id是約束名稱，第一個(gè)出現(xiàn)的user_id是student表中的字段，第二個(gè)user_id是user表中的字段。

所以在sqlite中定義外鍵，最好在創(chuàng)建表的時(shí)候就指定。而且需要在命令行中打開(kāi)支持開(kāi)關(guān)。

PRAGMA foreign_keys = ON;

創(chuàng)建：

CREATE TABLE album(
  albumartist TEXT,
  albumname TEXT,
  albumcover BINARY,
  PRIMARY KEY(albumartist, albumname)
);

CREATE TABLE song(
  songid     INTEGER,
  songartist TEXT,
  songalbum TEXT,
  songname   TEXT,
  FOREIGN KEY(songartist, songalbum) REFERENCES album(albumartist, albumname)
);

復(fù)雜表結(jié)構(gòu)更改為了安全起見(jiàn)，一般步驟如下：

1:用希望的所有列創(chuàng)建一個(gè)新表

2:使用INSERT SELECT 語(yǔ)句復(fù)制內(nèi)容到新表

3:檢驗(yàn)包含所需數(shù)據(jù)的新表

4:重命名舊表

5:重命名新表

6:根據(jù)需要，重新創(chuàng)建觸發(fā)器，存儲(chǔ)過(guò)程，索引，外鍵。

  • 刪除表
DROP TABLE user;
  • 重命名表名
RENAME TABLE user TO new_user;

六：視圖

視圖能夠簡(jiǎn)化復(fù)雜的聯(lián)結(jié)。視圖是虛擬表。

  • 創(chuàng)建視圖使用CREATE VIEW
  • 查看創(chuàng)建視圖的語(yǔ)句SHOW CREATE VIEW viewname
  • 刪除視圖DROP VIEW viewname
  • 更新視圖可以先DROP再CREATE。也可以使用CREATE OR REPLACE VIEW
CREATE VIEW virtual_user AS SELECT name,age FROM user,student WHERE user.user_id=student.user_id;

檢索20歲還是學(xué)生的用戶

SELECT name,age FROM virtual_user WHERE age=20;

再比如要顯示name(age)

CREATE VIEW temp_user AS SELECT Concat(name,'(',age,')') as temp_title FROM user;

再這個(gè)temp_user視圖上，查詢

SELECT * FROM temp_user;

視圖更新收到分組，聯(lián)結(jié)，子查詢，并，聚集，DISTINCT等限制，主要用在檢索(select)上，而不用于更新(update,insert,delete);

七：使用存儲(chǔ)過(guò)程

存儲(chǔ)過(guò)程：就是為以后的使用而保存的一條或者多條有步驟的MySQL語(yǔ)句的集合。簡(jiǎn)單，安全，高性能。
創(chuàng)建存儲(chǔ)過(guò)程

CREATE PROCEDURE xyz() 
BEGIN 
SELECT Avg(age) AS avg_age FROM user;
END;

如果是在命令行中執(zhí)行上面這段語(yǔ)句會(huì)出錯(cuò)，需要修改為

DELIMITER //

CREATE PROCEDURE xyz()
BEGIN
SELECT Avg(age) AS avg_age FROM user;
END //

DELIMITER ;

調(diào)用上面的存儲(chǔ)過(guò)程

CALL xyz();

刪除上面的存儲(chǔ)過(guò)程

DROP PROCEDURE xyz;

刪除的時(shí)候不需要括號(hào)。
上面這個(gè)例子是不使用參數(shù)的存儲(chǔ)過(guò)程，還有使用參數(shù)的存儲(chǔ)過(guò)程，略復(fù)雜。

八：使用游標(biāo)

游標(biāo)(cursor)是一個(gè)存儲(chǔ)在MySQL服務(wù)器上的數(shù)據(jù)庫(kù)查詢, 它不是一條SELECT語(yǔ)句,而是被該語(yǔ)句檢索出來(lái)的結(jié)果集。不像多數(shù)DBMS,MySQL游標(biāo)只能用于 存儲(chǔ)過(guò)程(和函數(shù))。

CREATE PROCEDURE xyz()
BEGIN
DECLARE abc CURSOR
FOR
SELECT name FROM user;
END;

DECLARE定義了一個(gè)abc的游標(biāo)。使用游標(biāo)需要

OPEN abc;

關(guān)閉游標(biāo)

CLOSE abc;

當(dāng)游標(biāo)打開(kāi)之后可以使用FETCH語(yǔ)句分別訪問(wèn)他的每一行。

九：觸發(fā)器

當(dāng)某個(gè)表發(fā)生更改時(shí)自動(dòng)處理。創(chuàng)建觸發(fā)器

  • 唯一的觸發(fā)器名
  • 觸發(fā)器關(guān)聯(lián)的表
  • 響應(yīng)的活動(dòng)(DELETE,INSERT,UPDATE)
  • 何時(shí)執(zhí)行活動(dòng)
CREATE TRIGGER newuser AFTER INSERT ON user FOR EACH ROW SELECT 'user add';

觸發(fā)器僅支持表，不支持視圖和臨時(shí)表。每個(gè)表最多支持六個(gè)觸發(fā)器，之前之后的插入，更新和刪除。所以六個(gè)。

刪除觸發(fā)器

DROP TRIGGER newuser;

例子

CREATE TRIGGER newuser AFTER INSERT ON user FOR EACH ROW SELECT NEW.user_id;

每次插入一個(gè)用戶，都會(huì)返回插入用戶的自增id。假設(shè)user_id是自增的。這里面的NEW虛擬表是系統(tǒng)維護(hù)的，不用操心。

例子

將刪除的行保存到一個(gè)存檔表中

CREATE TRIGGER deleteuser BEFORE DELETE ON user FOR EACH ROW 
BEGIN
    INSERT INTO archive_user(user_id,name,address,age) VALUES(OLD.user_id,OLD.name,OLD.address,OLD.age);
END;

其中archive_user是存檔表。OLD是系統(tǒng)維護(hù)的虛擬表。

當(dāng)我們使用UPDATE觸發(fā)器時(shí)

  • 可以使用OLD訪問(wèn)更新之前的值，使用NEW訪問(wèn)更新之后的值。
CREATE TRIGGER updateuser BEFORE UPDATE ON user FOR EACH ROW SET NEW.name=Upper(NEW.name);

每次更新的時(shí)候，名字都是大寫。

十：事務(wù)管理

  • transaction 一組sql語(yǔ)句

  • rollback 撤銷指定sql語(yǔ)句

  • commit 將未存儲(chǔ)的sql語(yǔ)句結(jié)果寫入數(shù)據(jù)庫(kù)表

  • savepoint 事務(wù)中的臨時(shí)占位符，可以對(duì)它發(fā)布會(huì)退。

  • 開(kāi)始事務(wù)

START TRANSACTION;

事務(wù)不能回退SELECT語(yǔ)句，也不能回退CREATE或者DROP操作。

SELECT * FROM user;
START TRANSACTION;
DELETE FROM user;
SELECT * FROM user;
ROLLBACK;
SELECT * FROM user;

1:顯示user表非空

2:開(kāi)啟事務(wù)

3:刪除user表所有行

4:顯示user表已經(jīng)為空

5:回退事務(wù)

6:顯示user表非空

事務(wù)要么被顯示提交關(guān)閉，要么被回退關(guān)閉。

START TRANSACTION;
DELETE FROM user WHERE user_id=100;
DELETE FROM student WHERE user_id=100;
COMMIT;

如果第二條delete沒(méi)有執(zhí)行成功，那么第一條也會(huì)被自動(dòng)撤銷。否則兩條都執(zhí)行成功，就提交。

  • 部分回退使用savepoint。
START TRANSACTION;
DELETE FROM user WHERE user_id=100;
SAVEPOINT delete1;
DELETE FROM student WHERE user_id=100;
SAVEPOINT delete2;
DELETE FROM student1 WHERE user_id=100;
ROLLBACK TO delete1;

當(dāng)三行都正確刪除之后，回退到指定點(diǎn)delete1;那么user表就回保存完好。

十一：全球化

SHOW CHARACTER SET;

顯示可用字符集。

SHOW COLLATION;

顯示完整字符集。

SELECT * FROM user ORDER BY name COLLATE latin1_general_cs;

使用COLLATE指定一個(gè)備用的校對(duì)順序。

十二：訪問(wèn)控制

為不同的用戶，設(shè)置不同的操作權(quán)限。MySQL上的所有用戶被保存在一個(gè)名為mysql的數(shù)據(jù)庫(kù)中

use mysql;
SELECT user FROM user;

即可查看所有的用戶。

  • 創(chuàng)建一個(gè)用戶，這里給了密碼，也可以不設(shè)置密碼。
CREATE USER xia IDENTIFIED BY 'xiapassword';
  • 重命名一個(gè)用戶
RENAME USER xia TO xiaguangcheng;
  • 刪除一個(gè)用戶
DROP USER xiaguangcheng;
  • 查看用戶權(quán)限
SHOW GRANTS FOR xiaguangcheng;

權(quán)限用用戶名和主機(jī)名結(jié)合定義。

  • 更改權(quán)限
GRANT SELECT ON school.* TO xia;

給用戶xia在school數(shù)據(jù)庫(kù)中所有表的SELECT查詢權(quán)限。

  • 撤銷權(quán)限
REVOKE SELECT,INSERT ON school.* FROM xia;

權(quán)限包括：

  • 整個(gè)服務(wù)器中所有數(shù)據(jù)庫(kù)

  • 整個(gè)數(shù)據(jù)庫(kù)中所有表

  • 整個(gè)表

  • 指定列

  • 指定存儲(chǔ)過(guò)程

  • 更改用戶密碼

SET PASSWORD FOR xia=Password('abc23rdf&4');

優(yōu)化

  • 優(yōu)化查詢語(yǔ)句，實(shí)驗(yàn)聯(lián)結(jié)，并，子查詢等。
  • 使用EXPLAIN讓mysql解釋它如何執(zhí)行一跳查詢語(yǔ)句。
  • 一般來(lái)說(shuō)，存儲(chǔ)過(guò)程要比一條一條執(zhí)行要快。
  • 盡量不要使用通配符*
  • 在導(dǎo)入數(shù)據(jù)時(shí)，關(guān)閉自動(dòng)體檢。導(dǎo)入數(shù)據(jù)后，再建立索引。
  • or條件太多，可以用多條select語(yǔ)句用union連接。
  • fulltext替換like

MySQL數(shù)據(jù)類型

附

  • 郭霖的博客。
  • 菜鳥教程。
  • 如果想在模擬器中查看App的數(shù)據(jù)庫(kù)，可以如此操作
adb root    //確保當(dāng)前打開(kāi)的模擬器是以root身份打開(kāi)的。
cd /data/user/0/packageName/databases   // 進(jìn)入databases目錄就可以ls，查看dbName了。
sqlite3 dbName.db  //打開(kāi)dbName.db數(shù)據(jù)庫(kù)。
.table  //可以列出打開(kāi)的這個(gè)數(shù)據(jù)庫(kù)的所有表名。
pragma table_info(tableName)  // 可以查看表結(jié)構(gòu)  
.schema tableName  //可以查看建表語(yǔ)句
select * from sqlite_master;   //由于每個(gè)數(shù)據(jù)庫(kù)都有一個(gè)sqlite_master表用來(lái)保存建表信息。因此可以查看所有表的創(chuàng)建信息。 
.header on // 輸出時(shí)附帶列名
.timer on //輸出時(shí)附帶cpu執(zhí)行時(shí)間
.mode column // 左對(duì)齊輸出
.quit // 退出
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容