學(xué)習(xí)《MySQL必知必會》筆記四(19章-30章)

一寨躁、用INSERT INTO 插入數(shù)據(jù)

二、更新和刪除數(shù)據(jù) UPDATE DELETE

三瑟啃、創(chuàng)建和操縱表
1.創(chuàng)建表
CREATE TABLE創(chuàng)建表趾徽,必須給出下列信息:
1) 新表的名字,在關(guān)鍵字CREATE TABLE之后
2) 表列的名字和定義翰守,用逗號分隔
CREATE TABLE customers
(
cust_id int NOT NULL AUTO_INCREMENT,
cust_name char(50) NOT NULL,
cust_address char(50) NULL,
cust_city char(50) NULL,
cust_state char(5) NULL,
cust_zip char(10) NULL,
cust_country char(50) NULL,
cust_contact char(50) NULL,
cust_email char(255) NULL,
primaryKEY (cust_id)
) ENGINE = InnoDB;
有以下幾點需要注意:
1)其中主鍵也可以用多個列組成孵奶,如orderitems表中在主鍵表示如下:
PRIMARY KEY(order_num,order_item)
主鍵只能使用不允許NULL值的列。
2)每個表只允許一個AUTO_INCREMENT列蜡峰×嗽可用SELECT last_insert_id()獲取最后一個AUTO_INCREMENT值朗恳。
3)用DEFAULT指定默認(rèn)值
4)引擎類型
InnoDB是一個可靠的事物處理引擎,它不支持全文本搜索
MEMORY在功能上等同于MyISAM,但由于數(shù)據(jù)存儲在內(nèi)存中载绿,速度很快(特適合于臨時表)
MyISAM是一個性能極高的引擎粥诫,它支持全文本搜索,但不支持事物處理
引擎可以混用崭庸,但外鍵不能夸引擎怀浆。
2.更新表
必須提供以下信息:
1)在ALTER TABLE之后給出要更改的表名
2)更改的列表
ALTER TABLE vendors ADD vend_phone CHAR(20);//增加一個新列
ALTER TABLE vendors DROP vend_phone;//刪除一個列
ALTER TABLE vendors MODIFY vend_phone VARCHAR2(15);//修改列寬
定義外鍵:
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCE orders(order_num);
CONSTRAINT fk_orderitems_orders 定義完整性約束名
FOREIGN KEY (order_num) 外鍵
REFERENCE orders(order_num) 參照約束
3.刪除表格
DROP TABLE customers;
4.重命名表
RENAME TABLE backup_customers TO customers;

四、使用視圖
視圖是虛擬的表怕享。與包含數(shù)據(jù)的表不一樣执赡,視圖只包含使用時動態(tài)檢索數(shù)據(jù)的查詢。
視圖不包含表中應(yīng)該有的任何列或數(shù)據(jù)函筋,它包含的是一個SQL查詢沙合。視圖僅僅用來查看存儲在別處數(shù)據(jù)的一種設(shè)施。視圖本身不包含數(shù)據(jù)跌帐,因此它們返回的數(shù)據(jù)是從其他表中檢索出來的首懈。在添加或更改這些表中的數(shù)據(jù)時,視圖將返回改變過的數(shù)據(jù)谨敛。每次使用視圖時究履,都必須處理查詢執(zhí)行時所需要的任一檢索。
視圖不能索引脸狸,也不能有關(guān)聯(lián)的觸發(fā)器或默認(rèn)值最仑。
1.使用視圖
1)視圖用CREATE VIEW來創(chuàng)建
2)使用SHOW CREATE VIEW viewname;來查看創(chuàng)建視圖的語句
3)用DROP刪除視圖,其語法為DROP VIEW viewname;

  1. 更新視圖時肥惭,可以先用DROP再用CREATE,也可以直接用CREATE OR REPLACE VIEW。
    舉個栗子:

    2.更新視圖
    有時紊搪,視圖是可更新的(即可以對它們使用INSERT蜜葱、UPDATE和DELETE),對視圖增加或刪除行耀石,實際上是對其基表增加或刪除行牵囤。
    但是,如果視圖定義中有以下操作滞伟,則不能進(jìn)行更新:
    1) 分組(使用GROUP BY和HAVING)
    2) 聯(lián)結(jié)
    3) 子查詢
    4) 并
    5) 聚集函數(shù)
    6) DISTINCT
    7) 導(dǎo)出(計算)列

五揭鳞、使用存儲過程
存儲過程簡單來說,就是為以后的使用而保存的一條或多條MySQL語句的集合梆奈∫俺纾可將其視為批文件,雖然它們的作用不僅限于批處理亩钟。
存儲過程有3個主要的好處:簡單乓梨、安全鳖轰、高性能。
創(chuàng)建過程


刪除存儲過程
DROP PROCEDURE productpricing;//當(dāng)不存在時報錯
DROP PROCEDURE productpricing IF EXISTS;//僅當(dāng)存在時刪除扶镀,不存在時不報錯
執(zhí)行存儲過程
MySQL稱存儲過程的執(zhí)行為調(diào)用蕴侣,因此MySQL執(zhí)行存儲過程的語句為CALL。CALL接受存儲過程的名字以及需要傳遞給它的任意參數(shù)臭觉。
一般昆雀,存儲過程并不顯示結(jié)果,而是把結(jié)果返回給你指定的變量蝠筑。
變量:內(nèi)存中一個特定的位置狞膘,用來臨時存儲數(shù)據(jù)。所有MySQL變量必須以@開始菱肖。

顯示出檢索的數(shù)據(jù)
SELECT @pricehigh,@pricelow,@priceaverage;

下面看一個完整過程的栗子:

檢查存儲過程
SHOW CREATE PROCEDURE ordertotal;

為了獲得包括何時客冈,由誰創(chuàng)建等詳細(xì)信息的存儲過程列表,使用SHOW PROCEDURE STATUS,并且可用LIKE指定一個過濾模式稳强,例如:
SHOW PROCEDURE STATUS LIKE ‘ordertotal’;

六场仲、使用游標(biāo)
用DECLARE創(chuàng)建游標(biāo),eg:DECLARE ordernumbers cursor
打開/關(guān)閉游標(biāo):OPEN ordernumbers;/CLOSE ordernumbers;
使用游標(biāo)數(shù)據(jù):FETCH ordernumbers INTO o;
請看使用實例(有注釋說明)

七、使用觸發(fā)器
1.觸發(fā)器
觸發(fā)器是MySQL響應(yīng)以下任意語句而自動執(zhí)行的一條MySQL語句(或位于BEGIN和END語句之間的一組語句):
DELETE INSERT UPDATE
2.創(chuàng)建觸發(fā)器
創(chuàng)建觸發(fā)器時退疫,需要給出4條信息:

  1. 唯一的觸發(fā)器名渠缕;
  2. 觸發(fā)器關(guān)聯(lián)的表
  3. 觸發(fā)器應(yīng)該響應(yīng)的活動(DELETE、INSERT或UPDATE)
  4. 觸發(fā)器何時執(zhí)行(處理之前或之后)
    只有表可以支持觸發(fā)器褒繁,視圖不支持(臨時表也不支持)亦鳞。




    這里用CREATE TRIGGER創(chuàng)建一個名為newproduct的新觸發(fā)器。觸發(fā)器可在一個操作發(fā)生之前或之后執(zhí)行棒坏,這里給出了AFTER INSERT燕差,所以觸發(fā)器將在INSERT語句成功后執(zhí)行。這個觸發(fā)器還制定了FOR EACH ROW坝冕,因此代碼對每個插入行執(zhí)行徒探。在這個例子中,文本Product added將對每個插入的行顯示一次喂窟。
    ☆觸發(fā)器按每個表每個時間每次的定義测暗,每個表每個時間每次只能定義一個觸發(fā)器,因此一個表最多支持6個觸發(fā)器(INSERT,UPDATE和DELETE之前或之后)磨澡。
    3.刪除觸發(fā)器:
    DROP TRIGGER newproduct;
    4.1 INSERT觸發(fā)器
    1)在INSERT觸發(fā)器代碼內(nèi)碗啄,可引用一個名為NEW的虛擬表,訪問被插入的行稳摄;
    2)在BEFORE INSERT觸發(fā)器中稚字,NEW中的值可以被更新(允許更改被插入的值)
    3)對于AUTO_INCREMENT列,NEW在INSERRT執(zhí)行之前包含0厦酬,在執(zhí)行之后包含新的自動生成的值尉共。
    CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.ORDER_num;
    4.2.DELETE觸發(fā)器
    1)在DELETE觸發(fā)器代碼內(nèi)褒傅,你可以引用一個名為OLD的虛擬表,訪問被刪除的行
    2)OLD中的值全部都是只讀的袄友,不能更新殿托。



    4.3update觸發(fā)器
    1)可以用OLD的虛擬表訪問以前的值,也可以用名為NEW的虛擬表訪問新更新的值
    2)在BEFFORE UPDATE觸發(fā)器中剧蚣,NEW中的值可能也被更新
    3)OLD中的值全部都是只讀的支竹,不能更新

八、管理事務(wù)處理
事務(wù)處理可以用來維護(hù)數(shù)據(jù)庫的完整性鸠按,它保證成批的MySQL操作要么完全執(zhí)行礼搁,要么完全不執(zhí)行目尖。
事務(wù):指一組SQL語句
回退:指撤銷指定SQL語句的過程
提交:指將未存儲的SQL語句結(jié)果寫入數(shù)據(jù)庫表
保留點:值事務(wù)處理中設(shè)置的臨時占位符馒吴,你可以對它發(fā)布回退。
1.控制事務(wù)處理
管理事務(wù)處理的關(guān)鍵在于將SQL語句組分解為邏輯塊瑟曲,并明確規(guī)定數(shù)據(jù)何時應(yīng)該回退饮戳,何時不應(yīng)該回退。
START TRANSACTION//標(biāo)記是事務(wù)開始
2.使用ROLLBACK用來回退MySQL語句
3.使用COMMIT語句明確提交
eg:


4.使用保留點
簡單的ROLLBACK和COMMIT語句就可以寫入或撤銷整個事務(wù)處理洞拨。但是扯罐,只是對簡單的事務(wù)處理才能這樣做,更復(fù)雜的事務(wù)處理可能需要部分提交或回退烦衣。
為了支持回退部分事務(wù)處理歹河,必須使用保留點,可如下使用SAVEPOINR語句:
SAVEPOINT delete1;
每個保留點都表示它的唯一名字花吟,以便回退時秸歧,MySQL知道要回退到何處。
ROLLBACK TO delete1;

  1. 更改默認(rèn)的提交行為
    為指示MySQL不自動提交更改衅澈,需要使用以下語句:
    SET autocommit = 0;

九键菱、字符集和校對順序
CREATETABLE mytable
(
Column1 INT,
Column2 VARCHAR(0)
)DEAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;
這條語句創(chuàng)建一個表,并且制定一個字符集和一個校對順序
SHOW CHARACTER SET;//可以查看所支持的字符集完整列表
SHOW COLLATION; //查看所支持的校對完整列表

十矾麻、管理用戶
USE mysql;
SELECT user FROM user;
Mysql數(shù)據(jù)庫有一個名為user的表纱耻,它包含所有用戶賬號芭梯。User表有一個user列险耀。
1.創(chuàng)建用戶賬戶
為創(chuàng)建新用戶賬戶,使用CREATE USER語句
CREATE USER ben IDENTIFIED BY ‘P@$$w0rd’;
創(chuàng)建一個用戶名為ben的用戶玖喘,口令為P@$$w0rd甩牺。
RENAME USER ben TO bforta;
2.刪除用戶賬號
DROP USER bforta;
3.設(shè)置訪問權(quán)限
使用GRANT設(shè)置權(quán)限,需要給出以下信息:
1)要授予的權(quán)限
2)被授予訪問權(quán)限的數(shù)據(jù)庫或表
3)用戶名


賦予bforta在crashcourse.*擁有SELECT權(quán)限累奈。
可以用REVOKE取消權(quán)限贬派。

4.更改口令
更新用戶口令
SET PASSWORD FOR bforta = Password(‘n3wp@$$w0rd’);
設(shè)置自己的口令
SET PASSWORD= Password(‘n3w p@$$w0rd’);

十一急但、數(shù)據(jù)庫維護(hù)
ANALYZE TABLE:用來檢查表鍵是否正確
CHECK TABLE:用來針對許多問題對表進(jìn)行檢查

最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末,一起剝皮案震驚了整個濱河市搞乏,隨后出現(xiàn)的幾起案子波桩,更是在濱河造成了極大的恐慌,老刑警劉巖请敦,帶你破解...
    沈念sama閱讀 217,185評論 6 503
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件镐躲,死亡現(xiàn)場離奇詭異,居然都是意外死亡侍筛,警方通過查閱死者的電腦和手機萤皂,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 92,652評論 3 393
  • 文/潘曉璐 我一進(jìn)店門,熙熙樓的掌柜王于貴愁眉苦臉地迎上來匣椰,“玉大人裆熙,你說我怎么就攤上這事∏菪Γ” “怎么了入录?”我有些...
    開封第一講書人閱讀 163,524評論 0 353
  • 文/不壞的土叔 我叫張陵,是天一觀的道長蒲每。 經(jīng)常有香客問我纷跛,道長,這世上最難降的妖魔是什么邀杏? 我笑而不...
    開封第一講書人閱讀 58,339評論 1 293
  • 正文 為了忘掉前任贫奠,我火速辦了婚禮,結(jié)果婚禮上望蜡,老公的妹妹穿的比我還像新娘唤崭。我一直安慰自己,他們只是感情好脖律,可當(dāng)我...
    茶點故事閱讀 67,387評論 6 391
  • 文/花漫 我一把揭開白布谢肾。 她就那樣靜靜地躺著,像睡著了一般小泉。 火紅的嫁衣襯著肌膚如雪芦疏。 梳的紋絲不亂的頭發(fā)上,一...
    開封第一講書人閱讀 51,287評論 1 301
  • 那天微姊,我揣著相機與錄音酸茴,去河邊找鬼。 笑死兢交,一個胖子當(dāng)著我的面吹牛薪捍,可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播,決...
    沈念sama閱讀 40,130評論 3 418
  • 文/蒼蘭香墨 我猛地睜開眼酪穿,長吁一口氣:“原來是場噩夢啊……” “哼凳干!你這毒婦竟也來了?” 一聲冷哼從身側(cè)響起被济,我...
    開封第一講書人閱讀 38,985評論 0 275
  • 序言:老撾萬榮一對情侶失蹤救赐,失蹤者是張志新(化名)和其女友劉穎,沒想到半個月后只磷,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體净响,經(jīng)...
    沈念sama閱讀 45,420評論 1 313
  • 正文 獨居荒郊野嶺守林人離奇死亡,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 37,617評論 3 334
  • 正文 我和宋清朗相戀三年喳瓣,在試婚紗的時候發(fā)現(xiàn)自己被綠了馋贤。 大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點故事閱讀 39,779評論 1 348
  • 序言:一個原本活蹦亂跳的男人離奇死亡畏陕,死狀恐怖配乓,靈堂內(nèi)的尸體忽然破棺而出,到底是詐尸還是另有隱情惠毁,我是刑警寧澤犹芹,帶...
    沈念sama閱讀 35,477評論 5 345
  • 正文 年R本政府宣布,位于F島的核電站鞠绰,受9級特大地震影響腰埂,放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜蜈膨,卻給世界環(huán)境...
    茶點故事閱讀 41,088評論 3 328
  • 文/蒙蒙 一屿笼、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧翁巍,春花似錦驴一、人聲如沸。這莊子的主人今日做“春日...
    開封第一講書人閱讀 31,716評論 0 22
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽。三九已至驰凛,卻和暖如春胸懈,著一層夾襖步出監(jiān)牢的瞬間,已是汗流浹背恰响。 一陣腳步聲響...
    開封第一講書人閱讀 32,857評論 1 269
  • 我被黑心中介騙來泰國打工趣钱, 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留,地道東北人渔隶。 一個月前我還...
    沈念sama閱讀 47,876評論 2 370
  • 正文 我出身青樓羔挡,卻偏偏與公主長得像,于是被迫代替她去往敵國和親间唉。 傳聞我的和親對象是個殘疾皇子绞灼,可洞房花燭夜當(dāng)晚...
    茶點故事閱讀 44,700評論 2 354

推薦閱讀更多精彩內(nèi)容