數(shù)據(jù)庫(kù)學(xué)習(xí)入門數(shù)據(jù)庫(kù)基礎(chǔ)入門

數(shù)據(jù)庫(kù)學(xué)習(xí)入門數(shù)據(jù)庫(kù)基礎(chǔ)入門 數(shù)據(jù)庫(kù)學(xué)習(xí)入門數(shù)據(jù)庫(kù)基礎(chǔ)入門 - 數(shù)據(jù)庫(kù)的作用： - 數(shù)據(jù)是數(shù)據(jù)庫(kù)中存儲(chǔ)的基本對(duì)象 包括：文字、圖形、圖像、聲音 有組織的、可共享的數(shù)據(jù)集合 - 常用的DBMS - MYSQL（oracle、Sun） oracle（oracle） SQL-Server（Microsoft) DB2（IBM） - .如何學(xué)習(xí)數(shù)據(jù)庫(kù)管理系統(tǒng) - .程序員 1、標(biāo)準(zhǔn)語(yǔ)言：SQL（結(jié)構(gòu)化查詢語(yǔ)言） SQL是用于訪問(wèn) 數(shù)據(jù)庫(kù)的最常用標(biāo)準(zhǔn)化語(yǔ)言，他是由ANSI/ISO SQL標(biāo)準(zhǔn)定義的 SQL 的分類 DML：數(shù)據(jù)操作語(yǔ)言（用于檢索或者修改數(shù)據(jù)） DDL：數(shù)據(jù)定義語(yǔ)言（用于定義數(shù)據(jù)的結(jié)構(gòu)，比如創(chuàng)建、修改或者刪除數(shù)據(jù)庫(kù)的對(duì)象） DCL：數(shù)據(jù)控制語(yǔ)言（用于定義數(shù)據(jù)庫(kù)用戶的權(quán)限） DML: select:用于檢索數(shù)據(jù) insert:用于增加數(shù)據(jù)到數(shù)據(jù)庫(kù) update:用于從數(shù)據(jù)庫(kù)中修改現(xiàn)存的數(shù)據(jù) delete：用于從數(shù)據(jù)庫(kù)中刪除數(shù)據(jù) DDL: create table /創(chuàng)建表 alter /修改表 drop table /刪除表 create index /創(chuàng)建索引 drop index /刪除索引 create view /創(chuàng)建視圖 drop view /刪除視圖 DCL： grant /授權(quán) revoke /撤銷授權(quán) set /設(shè)置 2、不同數(shù)據(jù)庫(kù)的獨(dú)特管理方式 .DBA（Database Administrator） 1、標(biāo)準(zhǔn)語(yǔ)言：SQL（結(jié)構(gòu)化查詢語(yǔ)言） 2、不同數(shù)據(jù)庫(kù)的獨(dú)特管理方式 3、數(shù)據(jù)庫(kù)的調(diào)優(yōu) 4、精通數(shù)據(jù)庫(kù)的備份、回復(fù)機(jī)制 - MYSQL的安裝與配置 - 安裝：選擇普通安裝類型 .配置 1、選擇配置類型：Detailed Configuration 2、選擇服務(wù)器類型：Developer Machine 3、選擇數(shù)據(jù)庫(kù)的是使用情況： -多功能數(shù)據(jù)庫(kù)（Multifunctional Database） -只是事務(wù)處理數(shù)據(jù)庫(kù)（Transactional Database Only） -只是非事務(wù)處理數(shù)據(jù)庫(kù)（Non-Transactional Database Only） 4、InnoDB表空間文件存儲(chǔ)的盤符，默認(rèn)在c： 5、數(shù)據(jù)庫(kù)并發(fā)連接數(shù)配置：Decision support 6、MYSQL服務(wù)器端口：默認(rèn)：3306 7、Enable Strict Mode：是否允許嚴(yán)格樣式檢查：取消 8、選擇字符編碼：手工選擇：gb2312，windows下中文支持 9、服務(wù)器默認(rèn)安裝到windows系統(tǒng)服務(wù)中，默認(rèn)服務(wù)自啟動(dòng)，選擇自動(dòng)配置環(huán)境變量 10、填寫數(shù)據(jù)庫(kù)管理系統(tǒng)的管理員密碼 -在mySQL中數(shù)據(jù)庫(kù)管理員的用戶名為：root 11、是否允許使用root用戶遠(yuǎn)程登錄數(shù)據(jù)庫(kù) 12、測(cè)試配置是否正確c：>c:mysqlbinmysqlshow - MYSQL 的登錄 - .開(kāi)始-運(yùn)行-cmd .輸入：mysql -uroot -p，然后輸入密碼 .或者：mysql -uroot -p密碼 .退出quit或者q -不必全在一個(gè)行內(nèi)給出一個(gè)命令，較長(zhǎng)命令可以輸入到多個(gè)行中。mysql通過(guò)尋找終止分號(hào)而不是輸入行的結(jié)束來(lái)決定語(yǔ)句在哪兒結(jié)束 ->等待多行命令的下一行 >等待下一行，等待以單引號(hào)開(kāi)始的字符串結(jié)束 如果你決定不想執(zhí)行正在輸入過(guò)程中的一個(gè)命令，輸入c取消它 能夠以大小寫輸入關(guān)鍵詞，結(jié)果是等價(jià)的 - 基本的SQL語(yǔ)句 - 創(chuàng)建數(shù)據(jù)庫(kù)-create database school； 查看有哪些數(shù)據(jù)庫(kù)-show databases； 刪除數(shù)據(jù)庫(kù)-drop database school； - 向數(shù)據(jù)庫(kù)中存儲(chǔ)數(shù)據(jù) - 1、首先進(jìn)入數(shù)據(jù)庫(kù) use dbname； use類似quit，不需要一個(gè)分號(hào)，寫也無(wú)礙 use必須在一個(gè)單行上給出 2、查看數(shù)據(jù)庫(kù)中的表 show tables； 3、在數(shù)據(jù)庫(kù)中創(chuàng)建表 create table student （name varchar（20），age int，sex char（2）； 4、向表中插入數(shù)據(jù)-insert into dbname values（張三，18，男）； 5、查看表中所有的數(shù)據(jù)-select * from student； 只查看姓名和年齡-select name，age from student； - 常用的數(shù)據(jù)類型 - char（M）固定長(zhǎng)度字符 用來(lái)表示固定長(zhǎng)度字符串，字符串的長(zhǎng)度是1-255 VARCHAR（M）可變長(zhǎng)度字符 具有靈活性的字符數(shù)據(jù)類型，范圍：1-255 處理char類型的數(shù)據(jù)比varchar類型的數(shù)據(jù)要快，有時(shí)會(huì)快50%（char類型存儲(chǔ)的數(shù)據(jù)的長(zhǎng)度是聲明變量時(shí)的固定長(zhǎng)度，而不管數(shù)據(jù)的實(shí)際長(zhǎng)度，varchar存儲(chǔ)的是按數(shù)據(jù)的實(shí)際長(zhǎng)度，從而減小了數(shù)據(jù)文件的大小） int 整數(shù) 有符合的范圍：-2147483648到2147483647，無(wú)符號(hào)范圍：0到4294967295 - 其他常用的數(shù)據(jù)類型 - DATE-日期類型，顯示YYYY-MM-DD DATETIME-日期和時(shí)間的組合，顯示YYYY-MM-DD HH:MM:SS TEXT/BLOB 文本和大對(duì)象 TEXT可以保存字符串的長(zhǎng)度在255-65535字節(jié)內(nèi)。BLOB是一個(gè)能保存二進(jìn)制數(shù)據(jù)的大對(duì)象，區(qū)別是TEXT不區(qū)分大小寫，而B(niǎo)LOB區(qū)分大小寫。 SQL語(yǔ)句的導(dǎo)入 1、編寫SQL腳本（.sql） 2、導(dǎo)入mysql-uusername -p < *.sql &練習(xí)： &老師表：teacher &姓名，性別，年齡，課程 導(dǎo)出sql腳本-mysqldump stu -uroot -p>abc.sql 查看表的結(jié)構(gòu)-desc tablename 修改表中的數(shù)據(jù)-update tablename set colname=value where條件 刪除數(shù)據(jù)-delete from teacher where age=？ 刪除表中所有數(shù)據(jù)-delete from teacher； 刪除表-drop table tablename 根據(jù)條件進(jìn)行過(guò)濾查找-select *from tablename where 條件 查找出版社為“清華出版社”的所有書籍-select *from books where pub=清華出版社； 查找出庫(kù)存大于50的所有書籍-select *from books where store>50； 查找出“西游記”的庫(kù)存量-select title，store from books where title=西游記； SQL運(yùn)算符 >大于 <小于 >=大于等于 <=小于等于 !=,<>不等于 查看數(shù)據(jù)庫(kù)的信息-s - mysql常用函數(shù) - 1、查看數(shù)據(jù)庫(kù)版本-select versin（）； 計(jì)算機(jī)的時(shí)間是存在BIOS（） 2、查看當(dāng)前數(shù)據(jù)庫(kù)的日期-select current_date（）； 3、查看當(dāng)前數(shù)據(jù)庫(kù)時(shí)間-select now（）； 4、查看當(dāng)前連接數(shù)據(jù)庫(kù)的用戶-select user（）；localhost：代表是本機(jī) create table user(id int,name varchar(20),bir date,dea datetime); insert into user values(1,zhangsan,now(),now(); or和and查詢 or（滿足一個(gè)條件） and（都需要滿足） 查找學(xué)生EQ為80分或90分的學(xué)生 select * from stu where EQ=80 or EQ=90; 查找學(xué)生EQ為90分并且性別為女的學(xué)生 select * from stu where EQ=90 and sex=女; in（x，x）返回條件中的記錄與or作用相似 select * from tablename where 條件 in（，）； between and返回兩者之間的記錄 查詢年齡在20-30之間的所有學(xué)生 select * from stu where age between 20 and 30； like與通配符（%）一起使用，模糊查詢 查找出姓張的學(xué)生 select * from stu where name like 張%； 查詢出使用163郵箱的所有學(xué)生 select * from stu where email like 3.com； 查詢出郵箱里面含有a的所有學(xué)生 select * from stu where email like %a%； order by 實(shí)現(xiàn)排序（從小到大）-asc 將學(xué)生的年齡從高到低排列 select * from stu order by age desc；（降序） 將學(xué)生的年齡從低到高排列 select * from stu order by age asc；（升序） 數(shù)據(jù)默認(rèn)為升序（從低到高） select * from stu order by age ； as為查詢的列起別名 1、查詢所有學(xué)生只列出姓名，年齡，性別 select name as 姓名,age as 年齡,sex as 性別 from stu; group by對(duì)于查詢出的數(shù)據(jù)結(jié)果進(jìn)行分類（分組） 2、將學(xué)生按性別進(jìn)行分類 select * from stu group by sex； 將學(xué)生按年齡進(jìn)行分類 select * from stu group by age； 3、having 子查詢：對(duì)于where查詢出的結(jié)果再次進(jìn)行查詢 查找出年齡大于20歲學(xué)生，并且在其中找出姓名等于xxx的學(xué)生 select * from stu where age >20 having name=xxx或 select * from stu where age>20 and name=xxx 4、distinct 過(guò)濾查詢的重復(fù)型記錄，只顯示唯一的記錄 將學(xué)生性別過(guò)濾 select distinct(sex) from stu; count 查看表中有幾條數(shù)據(jù) select count(*) from stu; select count(distinct(sex) from stu; limit 限制查詢結(jié)果的輸出數(shù)量 同時(shí)也可以實(shí)現(xiàn)數(shù)據(jù)的分頁(yè) 查詢EQ前三名的學(xué)生 select * from stu order by EQ desc limit 3; 實(shí)現(xiàn)查詢記錄的分頁(yè) select * from stu limit 0,3; select * from stu limit 3,3; 約束-定義了表級(jí)的強(qiáng)制規(guī)則、數(shù)據(jù)的完整性 非空約束（not null） create table test(id int not null); insert into test values(); innodb 會(huì)報(bào)錯(cuò) ，myISAM 會(huì)整形默認(rèn)以0填充 唯一約束（UNIQUE) 不允許列中的數(shù)據(jù)重復(fù) create table test(id int,unique(id); insert into test values(1); 默認(rèn)約束(default) create table test(id int not null default111111); insert into test values(); 主鍵約束(primary key)是一個(gè)字段或一組字段（組合鍵），用于唯一標(biāo)識(shí)表中的記錄，它可以 確保每個(gè)記錄是唯一的。 create table test(id int,primary key(id); insert into test valuse(1); id主鍵自增,減 create table test (id int，name varchar（20），primary key（id）)； insert into test values(1,張安)； auto_increment的特點(diǎn)-自增值 當(dāng)刪除某一值時(shí)，他不會(huì)自動(dòng)填充，而是繼續(xù)自增1 create table test(id int not null auto_increment,name varchar(20),primary key(id); insert into test(name)values(xxx)； 創(chuàng)建一個(gè)有合理約束的表 create table people(id int not null auto_increment,name varchar(20) not null,age int not null,sex char(2) not null,pcode varchar(50),tel varchar(50),email varchar(50),primary key(id); insert into people(name,age,sex,email) values(張三，28，男，abc163.com); 查詢出所有學(xué)生的檔案信息 select * from student,school where student.daih=school.id;或 select * from student as a,school as b where a.daih=b.id; 查詢所有學(xué)生檔案信息 只需顯示：學(xué)生姓名，年齡。畢業(yè)學(xué)校，學(xué)校地址 select student.name,student.age,school.name,school.addres s from student, school where student.daih=school.id;或 select a.name,a.age,b.name,b.address from student as a, school as b where a.daih=b.id; 外鍵約束( foreign key) 如何創(chuàng)建外鍵 foreign key:定義子中的列為外鍵 references:標(biāo)記外鍵所要參考的父表和列 on delete cascade:允許在刪除父表的列的同時(shí)，刪除子表的列 /在InnoDB中支持 create datebase fordb; use fordb; 創(chuàng)建父表 create table school(id int not null auto_increment,name varchar(20),primary key(id)engine=innodb; insert into school(name) values(紫瑯學(xué)院); 創(chuàng)建 子表 create table student(id int not null auto_increment,name varchar(20),schoolid int,primary key(id),foreign key(schoolid) references school(id) on delete cascade); insert into student(name,schoolid) values(張三,1); E-R模型 概述：設(shè)計(jì)數(shù)據(jù)庫(kù)時(shí)，通常采用"實(shí)體關(guān)系模型“-E-R模型 軟件開(kāi)發(fā)流程： 需求調(diào)研（設(shè)計(jì)功能，收集數(shù)據(jù)） 與最終用戶進(jìn)行確定 數(shù)據(jù)庫(kù)的設(shè)計(jì) 控制多余數(shù)據(jù) 那么在最后，數(shù)據(jù)庫(kù)設(shè)計(jì)者確定表、不同表之間的關(guān)系以及每個(gè)表之間的關(guān)系，通常使用”E-R模型“，它將整個(gè)系統(tǒng)看作彼此相關(guān)的實(shí)體組成 實(shí)體：通常用于表示 能夠被清晰識(shí)別的人、地點(diǎn)、事物、對(duì)象、事件 實(shí)體關(guān)系圖：如果需要 基于E-R模型建立數(shù)據(jù)庫(kù)模型，需要標(biāo)識(shí)實(shí)體，實(shí)體的屬性、以及實(shí)體之間的關(guān)系。那么通常用E-R圖來(lái)表示 實(shí)體之間的三種類型：1：1 1：N 或N :1 M:N 數(shù)據(jù)庫(kù)設(shè)計(jì)員確定的實(shí)體被轉(zhuǎn)換為表，而其屬性則成為相應(yīng) 表中的字段（列） 如何控制冗余數(shù)據(jù)： 一般來(lái)說(shuō)通過(guò)數(shù)據(jù)庫(kù)的范式理論 設(shè)計(jì)數(shù)據(jù)庫(kù)的范式來(lái) 控制冗余 共有五個(gè)范式，一般達(dá)到第三范式即可 第一范式：對(duì)于表中的每一行，必須且僅僅有唯一的行值，在一行中的每一列僅有唯一的值并且具有原子性 第二范式 ：要求 非主鍵列是主鍵的子集，非 主鍵列活動(dòng)必須完全依賴整個(gè)主鍵。主鍵必須有唯一性的元素，一個(gè)主鍵可以由一個(gè)或更多的組成唯一值的列組成。一旦創(chuàng)建 ，主鍵 無(wú)法改變，外鍵關(guān)聯(lián)一個(gè)表的主鍵。主外鍵關(guān)聯(lián)意味著一對(duì)多的關(guān)系。 第三范式：要求非主鍵列互不依賴 第四范式：禁止主鍵列和非主鍵列一對(duì)多關(guān)系不受 約束 第五 范式 ：將表分割成盡可能 小的塊，為了排除在表中所有的冗余 MYSQL的聚合函數(shù) 1、最大值 找出EQ最高的學(xué)生 select name ,eq from student where eq=(select max（EQ）from student)； SELECT MAX(article) AS article FROM shop; 2、最小值 找出EQ最低的學(xué)生 select name，min（EQ）from student； count（）/統(tǒng)計(jì) 查詢出的記錄總數(shù) 3、查詢出學(xué)生的總條數(shù) select count（name）from student； avg（）/求平均值 4、查詢學(xué)生EQ的平均值 select avg（EQ） from student； sum（）/求和 5、查詢學(xué)生EQ的總和 select sum（EQ） from stu； 修改字段類型 alter table student modify sex char（5）； 增加列 alter table student add address varchar（50）； 刪除列 alter table student drop address； 修改列的名稱 alter table student change name names varchar（20）； 修改表的名字 rename table student to stu； MYSQL 之表連接 內(nèi)鏈接(inner join)：又為等值連接，因?yàn)樗麑蓚€(gè)表中的公共字段有相等的值（記錄）連接起來(lái)，這是最常用的連接。一個(gè)表引用還被稱為 查詢顯示：訂單編號(hào)，顧客姓名，物品名稱 select cases.id,user.name,goods.name from cases,user,goods where cases.user_id=user.id and cases.goods_id=goods.id;或 select cases.id,user.name,goods.name from cases inner join(user,goods) on(user.id=cases.user_id and goods.id=cases.goods_id);或 select cases.id,user.name,goods.name from (cases inner join user on cases.user_id=user.id) inner join goods on cases.goods_id=goods.id;或 select cases.id,user.name,goods.name from user inner join(cases inner join goods on cases.goods_id=goods.id) on user.id=cases.user_id); 左連接:顯示sql語(yǔ)句中l(wèi)eft join 左邊表中的所有記錄，即使在left join 右邊的表中沒(méi)有滿足連接條件的數(shù)據(jù)也被顯示。當(dāng)滿足連接條件時(shí)，left join 右邊的表中的相應(yīng)的記錄與left join左邊表中的相應(yīng)記錄結(jié)合顯示。 查詢出：學(xué)生的編號(hào)，學(xué)生姓名 ，學(xué)生學(xué)校 select student.name,student.id,school.name from student left join school on student.school_id=school.id; 右連接：與左連接相對(duì)應(yīng)，他顯示sql語(yǔ)句中right join 右邊表中的記錄，即使在right join 左邊沒(méi)有的記錄也被顯示。當(dāng)滿足條件時(shí)。right join左邊表中相應(yīng)記錄將與right join右邊的表中相應(yīng)記錄進(jìn)行結(jié)合顯示。通常左連接和右連接顯示的內(nèi)容是一樣的。 select student.name,student.id,school.name from student right join school on student.school_id=school.id; left join 以左邊的表查詢?yōu)橹?right join 以右邊的表查詢?yōu)橹?示例： student left join school 那么student就為左表。school就為由表 MYSQL之視圖 視圖：它可以訪問(wèn)一個(gè)或多個(gè)表中的數(shù)據(jù)，是從一個(gè)或多個(gè)表中派生出的數(shù)據(jù)對(duì)象（虛表） 視圖的特點(diǎn)： 1、將復(fù)雜的查詢簡(jiǎn)單化 2、提供安全機(jī)制，它保證用戶只可以看得到的數(shù)據(jù)，系統(tǒng)中真實(shí)的表是不可被存取的（現(xiàn)都支持更改數(shù)據(jù)） 創(chuàng)建視圖： create view case_view as select cases.id as 訂單編號(hào),user.name as 顧客姓名,goods.name as 物品名稱 from user inner join(cases inner join goods on cases.goods_id=goods.id) on user.id=cases.user_id; 注意點(diǎn)： 視圖屬于數(shù)據(jù)庫(kù)，在默認(rèn)情況下，將在當(dāng)前數(shù)據(jù)庫(kù)創(chuàng)建新視圖。要想在給定數(shù)據(jù)庫(kù)中明確創(chuàng)建視圖。創(chuàng)建時(shí)，應(yīng)將名稱指定為 db_name.view_name mysql>create view test.v as select *from t; 視圖必須具有唯一的列名，不得有重復(fù)，就像基表那樣。 修改視圖:alter view viewname as SQL; 刪除視圖：drop view viewname; alter view case_view as select cases.id as 編號(hào),user.name as 姓名,goods.name as 名稱 from user inner join(cases inner join goods on cases.goods_id=goods.id) on user.id=cases.user_id; 數(shù)據(jù)字典 數(shù)據(jù)庫(kù)的數(shù)據(jù)字典-實(shí)際上是一個(gè)數(shù)據(jù)庫(kù) 它記錄著數(shù)據(jù)庫(kù)管理系統(tǒng)中的其他數(shù)據(jù)庫(kù)的操作 mysql數(shù)據(jù)庫(kù)字典：information_schema use information_schema select * from views; - MYSQL的事務(wù)與引擎 - 什么是 事務(wù)（transaction） 所謂事務(wù)，它是一個(gè)操作序列。這些操作要么都執(zhí)行，要么都不執(zhí)行，他是一個(gè)不可分割的工作單位 事務(wù)是數(shù)據(jù)庫(kù)維護(hù)數(shù)據(jù)一致性的單位，在每個(gè)事務(wù)結(jié)束時(shí)，都能保持?jǐn)?shù)據(jù)一致性 mysql表類型： 在mysql中有多種表的類型，但是分為兩類 : 事務(wù)類型：InnoDB、BDB 非事務(wù)類型：MYISAM、MERGE、MEMORY（HEAP） InnoDB和BDB存儲(chǔ)引擎提供事務(wù)安全表。BDB被包含在mac支持它的操作系統(tǒng)發(fā)布的mysql-max二制分發(fā)版里。InnoDB也默認(rèn)被包括在所有mysql5.1二進(jìn)制分發(fā)版里 InnoDB是為處理巨大數(shù)據(jù)量時(shí)的最大性能設(shè)計(jì)。它的cpu效率可能是任何其他基于磁盤的關(guān)系數(shù)據(jù)庫(kù)引擎所有不能匹敵的。 MYISAM管理非事務(wù)表。他提供高速存儲(chǔ)和檢索，以及全文搜索能力。 ISAM數(shù)據(jù)表是mysql最原始的數(shù)據(jù)表，有三個(gè)文件，分別是： .frm，存放數(shù)據(jù)表的結(jié)構(gòu)定義 .isd，數(shù)據(jù)文件，存放數(shù)據(jù)表中的各個(gè)數(shù)據(jù)行的內(nèi)容 .ism,索引文件，存放數(shù)據(jù)表的所有索引信息。 MYISAM是數(shù)據(jù)表是ISAM數(shù)據(jù)表的繼承者，也是三個(gè)文件，分別是： .frm,結(jié)構(gòu)定義文件； .MYD，數(shù)據(jù)文件； .MYI，索引文件。 BDB數(shù)據(jù)表用兩個(gè)文件 來(lái)表示，分別是; .frm，結(jié)構(gòu)定義文件 .DB，數(shù)據(jù)表數(shù)據(jù)和索引文件 INNODB:由于采用表空間的概念來(lái)管理數(shù)據(jù)表，所以他只有一個(gè)與數(shù)據(jù)表對(duì)應(yīng).frm文件，同一目錄下的其他文件表示為空間，存儲(chǔ)數(shù)據(jù)表的數(shù)據(jù)和索引 HEAP數(shù)據(jù)表是一個(gè)存在與內(nèi)存中的表，所以他的數(shù)據(jù)和索引都存在于內(nèi)存中，文件系統(tǒng)中只有一個(gè).frm文件，及定義結(jié)構(gòu)，當(dāng)mysql關(guān)閉后數(shù)據(jù)都將消失。 定義表的的類型 create table test（id int）engine=heap； 事務(wù)表幣非事務(wù)表有幾大優(yōu)勢(shì)： 更安全。即使mysql崩潰或遇到硬件問(wèn)題 ，要么自動(dòng)恢復(fù)，要么從備份加事務(wù)日志恢復(fù)，你可以取回?cái)?shù)據(jù) 你可以合并許多語(yǔ)句，并用commit語(yǔ)句同時(shí)接受她們?nèi)浚ㄈ绻鸻utocommit被禁止掉） 你可以執(zhí)行rollback來(lái)忽略你的改變（如果autocommit被禁止掉） 如果更新失敗，你的所有改變都變回原來(lái)。（用非事務(wù)安全表，所有發(fā)生的改變都是永久的） 事務(wù)安全存儲(chǔ)引擎可以給那些當(dāng)前用 讀得到許多更新的表提供 更好的部署。 非事務(wù)表自身有幾大優(yōu)點(diǎn)： 非事務(wù)表自身有幾大優(yōu)點(diǎn)，因?yàn)闆](méi)有事務(wù)開(kāi)支，所有優(yōu)點(diǎn)都能出現(xiàn)： 更快 需要更更少的磁盤空間 執(zhí)行更新需要更少的內(nèi)存 你可以在同一個(gè)語(yǔ)句中合并事務(wù)安全和非事務(wù)安全表來(lái)獲得兩者最好的情況。盡管如此，在autocommit被禁止掉的事務(wù)里，變換到非事務(wù)安全表依舊即使提交并且不會(huì)被回滾 如何在innodb中實(shí)現(xiàn)事務(wù)： begin:/告知服務(wù)器我要開(kāi)始一個(gè)事物 rollback:/如果事務(wù)發(fā)生異常，那么數(shù)據(jù)回滾 commit:/事務(wù)執(zhí)行成功，進(jìn)行數(shù)據(jù)提交 mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into test11 values(1,zhangs); Query OK, 1 row affected (0.00 sec) mysql> select * from test11; +-+-+ | id | name | +-+-+ | 1 | zhangs | +-+-+ 1 row in set (0.00 sec) mysql> end; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near end at line 1 mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> select * from test11; Empty set (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from test11; Empty set (0.00 sec) 修改表類型 sql語(yǔ)句：alter table tablename engine=innodb； 注意：不可以將mysql系統(tǒng)表比如user或host轉(zhuǎn)換成innodb類型。系統(tǒng)表必須為myisam類型 對(duì)myisam進(jìn)行事務(wù)處理-鎖 lock table tablename（read，write） lock table 可以對(duì)表進(jìn)行加鎖控制，以保證用戶并發(fā)訪問(wèn)時(shí)非事務(wù)表的數(shù)據(jù)一致性 unlock tables 釋放鎖 mysql 之index（索引） 什么是索引？ 索引被用來(lái)快速找出在一個(gè)列上用一特定值的行。沒(méi)有索引，mysql不得不首先以第一條記錄開(kāi)始并然后讀完整個(gè)表直到他找出相關(guān)的行，表越大，花費(fèi)時(shí)間越多。如果表對(duì)于查詢的列有一個(gè)索引，mysql能快速到達(dá)一個(gè)位置去搜尋到數(shù)據(jù)文件的中間，沒(méi)有必要考慮所有數(shù)據(jù)。 索引的作用： 快速找出匹配一個(gè)where子句的行 當(dāng)執(zhí)行連接時(shí)，從其他表檢索行 對(duì)特定的索引列找出max（）或min（）值 mysql中的索引： 索引實(shí)際上是一個(gè)分離的列表，具有一個(gè)指向全表的指針 myisam表，索引被存儲(chǔ)為分離文件 innodb，索引存儲(chǔ)為表空間的一部分 mysql有四種類型的索引：主鍵（primary key）、唯一索引（unique）、全文索引（full index）、普通索引（index） 創(chuàng)建index 1、create index indexname on tablename（columnname）； indexname（索引名稱） tablename（表名稱） columnname（要?jiǎng)?chuàng)建索引的字段名稱） 2、alter table tablename add index indexname（columnname）； 查看索引-show keys from tablename； 刪除index-alter table tablename drop index indexname； full index（myisam支持） create table chat（id int，chat1 text，chat2 text）； create fulltext index index_text on tablename（col1，col2）； alter table tablename add fulltext（col1，col2）； 高效使用索引： 太多的索引，占用空間 每次進(jìn)行插入或更新時(shí)，索引都必須針對(duì)變化 重新排序，會(huì)導(dǎo)致 很多額外負(fù)擔(dān) 何處使用索引？ 1、where從句中條件匹配的行 select name from student where comment like“a%”； comment域的索引就起到了作用 Explain 分析索引 語(yǔ)法： explain select age from test12; Explain 分析mysql中的索引： table：查詢中涉及的表 type：顯示連接中使用了何種類型 const（最好），eq_ref，fef，range，index和all possible_keys：可能用到的索引 key：實(shí)際使用的索引，可以在select語(yǔ)句中使用use index（indexname）來(lái)強(qiáng)制使用索引，用ignore index（indexname）來(lái)強(qiáng)制忽略索引key_len：使用的索引長(zhǎng)度，越短越好； ref(references)：顯示索引哪一列被使用了 rows：mysql必須檢查用來(lái)返請(qǐng)求數(shù)據(jù)的行數(shù) extra：using temporary（需要臨時(shí)表來(lái)存儲(chǔ)結(jié)果，通常發(fā)生在對(duì)不同的列集進(jìn)行order by），usig filesort 意即mysql根本 不能使用索引 索引的算法：btree bitmap 使用索引注意的事項(xiàng); 1、合理創(chuàng)建索引，反之會(huì)降低數(shù)據(jù)庫(kù)的查詢效率，反之創(chuàng)建的索引會(huì)失去意義。 mysql的備份方式：backup、 restore 、copy/冷備份、mysqldump、select into backup備份myisam表的 示例： >use test; >backup table chat to c:db_backup; 只生成 .frm 和 .myd,可以在數(shù)據(jù)庫(kù)恢復(fù)后重建索引 >drop table chat; >restore table chat from c:db_backup; copy備份 1、停止mysql服務(wù)器一避免 在備份的時(shí)候有用戶進(jìn)行數(shù)據(jù)庫(kù)的訪問(wèn) 1、復(fù)制數(shù)據(jù)庫(kù)的文件夾 用mysqldump備份 備份庫(kù) 1、備份 #mysqldump -uroot -p dbname >xxxname.sql 再打開(kāi)查看（創(chuàng)建表備份的sql語(yǔ)句） 2、恢復(fù) #mysql -uroot -p dbname <> 備份某個(gè)表 1、備份 #mysqldump -uroot -p dbname tablename >xxxname.sql 再打開(kāi)文件進(jìn)行查看（創(chuàng)建表備份的sql語(yǔ)句） 2、恢復(fù) #mysql -uroot -p dbname <> 用select into 備份 備份：mysql>select *from tablename into outfile c:db_backuptable.dat 刪除表： mysql>delete from tablename; 恢復(fù)： mysql>load data infile c:db_backuptable.dat into table tablename; 作業(yè)： 1、唯一索引 2、全文索引 要求：理解她們的意思及用途，以及實(shí)驗(yàn) 3、select into備份 MYSQL其他客戶端工具 查看數(shù)據(jù)庫(kù)中有哪些數(shù)據(jù)庫(kù) mysqlshow -uroot -p 查看數(shù)據(jù)庫(kù)中有哪些表 mysqlshow -uroot -p dbname 查看數(shù)據(jù)庫(kù)中表的結(jié)構(gòu) mysqlshow -uroot -p dbname tablename mysqlcheck客戶端可以檢查和修復(fù)myisam表，他還可以優(yōu)化和分析表 1、mysqlcheck -uroot -p dbname /檢測(cè) 2、mysqlcheck -uroot -p -auto-repair dbname /如碰到有問(wèn)題的表自動(dòng)進(jìn)行修復(fù) 3、mysqlcheck -uroot -p -optimize /優(yōu)化表 MYSQL 管理方式 連接方式-mysql -hlocalhost -uroot 通過(guò)本地主機(jī)，以root用戶訪問(wèn)，無(wú)需密碼驗(yàn)證 mysql -hlocalhost 通過(guò)本地主機(jī)，匿名用戶訪問(wèn)，無(wú)需密碼驗(yàn)證 設(shè)置密碼-mysqladmin 外部： 1、修改密碼-mysqladmin hlocalhost -uroot -p password "newpassword" 2、密碼為空-mysqladmin -hlocalhost -uroot -p password "" 3、設(shè)置root用戶遠(yuǎn)程訪問(wèn)密碼-mysqladmin -hremote -uroot -p password "" 內(nèi)部設(shè)置密碼： 1、設(shè)置root用戶本地登錄密碼 set password for rootlocalhost=password(000000); 2、設(shè)置root用戶遠(yuǎn)程登錄密碼 set password for rootremote=password(newpassword); 加密： mysql> use start Database changed mysql> create table users(username varchar(50),password varchar(50); Query OK, 0 rows affected (0.16 sec) mysql> insert into users values(abcd,123456); Query OK, 1 row affected (0.34 sec) mysql> insert into users values(abcdfg,password(123456); Query OK, 1 row affected (0.00 sec) mysql> select * from users; select user，host，password form user； +-+- -+ | username | password | +-+- -+ | abcd | 123456 | | abcdfg | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +-+- -+ 2 rows in set (0.00 sec) 系統(tǒng)中的權(quán)限表 user表：記錄允許連接到服務(wù)器的用戶帳號(hào)信息，里面的權(quán)限是全局 級(jí)的 db表：記錄各個(gè)帳號(hào)在各個(gè)數(shù)據(jù)庫(kù)上的操作權(quán)限 table_priv記錄數(shù)據(jù)表級(jí)的操作權(quán)限 columns_priv：記錄數(shù)據(jù)列級(jí)的操作權(quán)限 host表：配合db權(quán)限表對(duì)給定主機(jī)上數(shù)據(jù)庫(kù)級(jí)操作權(quán)限作更細(xì)致的控制。這個(gè)權(quán)限表不受grant和revoke語(yǔ)句的影響 - - - 設(shè)置密碼-修改user表 直接修改user表 mysql>use mysql; mysql>update user set password=password(newpassword)where user=root; mysql>flush privilegs;重新加載權(quán)限表，使修改立即生效 添加用戶 mysql> use mysql mysql> insert into user(host,user,password)values(localhost,test, password(222222); mysql> flush privileges; 授權(quán)：grant all on *.* to testlocalhostidentified by 222222 grant select on stus.* to testlocalhostidentified by 121212 grant privileges（columns） privileges表示授予的權(quán)限，columns表示作用的列（可選） on what 設(shè)置權(quán)限級(jí)別：全局級(jí)、數(shù)據(jù)庫(kù)級(jí)、表級(jí)、列級(jí) to account 權(quán)限授予的用戶:用user _namehost_name這種用戶名、主機(jī)名格式、identified by password設(shè)置密碼 privileges有哪些 alter 修改表和索引 create 創(chuàng)建數(shù)據(jù)庫(kù)和表 delete 刪除表中以有的記錄 drop 刪除數(shù)據(jù)庫(kù)和表 inde 創(chuàng)建和刪除索引 insert 向表中插入數(shù)據(jù) select 檢索表中的數(shù)據(jù)、 update 修改表中的記錄 file 讀寫服務(wù)器上的數(shù)據(jù) process 查看服務(wù)器中執(zhí)行的線程信息或殺死線程 reload 重載授權(quán)表或清空日志、主機(jī)緩存或表緩存 shutdown 關(guān)閉服務(wù)器 all 所有權(quán)限 revoke取消授權(quán) mysql>revoke privileges(columns)on what from account; 注：revoke可刪除權(quán)限，但不能刪除帳號(hào)，即使帳號(hào)已經(jīng)沒(méi)有任何權(quán)限。所以u(píng)ser數(shù)據(jù)表里還會(huì)有該帳號(hào)的記錄，要徹底刪除帳號(hào)，需使用delete命令手工刪除user表中的相關(guān)數(shù)據(jù)。 示例： revoke all on *.*fromtestlocalhost; delete from user where user=test; 細(xì)節(jié)權(quán)限的分配與撤銷 grant select,delete,update on student,test.*toerichlocalhostidentified by test 運(yùn)行結(jié)果是，erich用戶只能通過(guò)test密碼從本機(jī)訪問(wèn)student，test數(shù)據(jù)庫(kù)，并且沒(méi)有insert權(quán)限 revoke select,delete,update on student.*fromerichlocalhost; 運(yùn)行結(jié)果是，將erich用戶在student數(shù)據(jù)庫(kù)中的select，delete，update權(quán)限撤銷