MySQL資料庫的基礎操作及理解

標籤：engine   建立資料庫   層級   符號   機率   萬用字元   restart   數字   儲存引擎   

使用資料庫的步驟：

1. 連結MySQL伺服器

2. 選擇資料庫

3. 對資料表進行增刪改查

4. 關閉資料庫

 

退出
　　quit
　　exit
　　\q

常見操作
　　\c 取消未完成的操作。
　　\g 代替結束符

資料庫操作：
　　show databases 查看資料庫
　　　　注意：查看所有資料庫，mysql資料庫千萬不要動。

　　建立資料庫
　　　　create database 資料庫名
　　　　注意：庫名不要用中文，不要用數字開頭。
　　　　注意：
　　　　　　1、每建立一個資料庫，會在data目錄下建立一個以資料庫名稱命名的檔案夾。
　　　　　　2、資料庫是唯一的。

　　刪除資料庫
　　　　drop database 資料庫名;

　　進入資料庫,選擇資料庫
　　　　use 資料庫名

　　　　注意：windows下面資料庫名是不區分大小寫，但是在Linux下面嚴格區分。

　　查看已經選擇的資料庫
　　　　select database();

資料表的操作：
　　查看資料庫中的表： show tables;
　　建立資料表
　　　　create table 表名(
　　　　欄位名 欄位類型,
　　　　欄位名1 欄位類型,
　　　　欄位名2 欄位類型
　　　　)
　　　　最後一個欄位名不需要加逗號

　　查看錶結構：desc 表名

　　查看建表語句：show create table 表名
　　\G 讓資料站起來（更直觀的能夠看到結果）
　　　　select * from user \G;
　　刪除資料表：drop table 表名

資料的操作：
　　插入資料

　　　　插入指定欄位：

　　　　　　insert into 表名(欄位名1,欄位名2,欄位名3....) values (值1,值2,值3....);

　　　　　　如果包含空欄位、非空但是有預設值的欄位、自增欄位可以不在insert欄位列表中出現。

　　　　插入所有欄位：

　　　　　　insert into 表名 values (值1,值2,值3.....)

　　　　　　不指定列名的情況下values後面的順序應該和欄位的排列順序一致。

　　　　插入多條語句：　　　　　

　　　　　　insert into 表名 (欄位名1,欄位名2,欄位名3....) values (值1,值2,值3....),(值1,值2,值3),......
　　　　　　如果插入多條資料，使用這種方法比使用第二種或第一種方法要快的多。

　　　　插入單條資料：

　　　　　　insert into 表名 set 欄位名1=值1,欄位名2=值2....

　　　　插入某些查詢的結果

　
　　查看資料 select 欄位名1,欄位名2 from 表名
　　　　問題：
　　　　1、如果欄位太多，會導致流量浪費。
　　　　2、沒有條件會將所有表裡面的資料查詢出來。 怎麼樣加條件。　　　

　　　　　　where從句-----條件查詢
　　　　　　where 要放在表名後面

　　　　　　where 條件中可以使用算數運算子、比較子、邏輯運算子
　　　　　　　　算數運算子【+、-、*、/、%】

　　　　　　　　比較子【>、<、>=、<=、!=、=】

　　　　　　　　邏輯運算子
　　　　　　　　MySQL裡面可以給出多個條件。
　　　　　　　　　　and 邏輯與
　　　　　　　　　　or 邏輯或

　　　　　　　　　注意：
　　　　　　　　　　1、他們可以將小條件組合成大條件，而且可以多次使用。
　　　　　　　　　　2、條件的合理性
　　　　　　　　　　3、SQL標準在處理or操作之前會優先處理and操作

　　　　　　　　其他動作符
　　　　　　　　　　in 操作符:指定條件範圍，範圍中的每個條件都可以進行匹配，in裡面的每個值使用逗號隔開。
　　　　　　　　　　格式：in(值1,值2,值3);

　　　　　　　　between操作符，在指定的兩個值之間
　　　　　　　　　　格式：between 起始值 and 結束值

　　　　　　　　not操作符，否定後面的操作，in和between一起使用。

　　　　　　　　like操作符
　　　　　　　　　　格式：like ‘字串‘
　　　　　　　　　　萬用字元：用來匹配值的一部分的特殊字元。
　　　　　　　　　　%：表示任何字元出現任一次數，可以放在任意位置。

　　　　　　　　　　%值：以值結尾
　　　　　　　　　　值%：值開頭
　　　　　　　　　　%值%:包含了值

　　在SQL標準中沒有經過排序的資料他的順序是不可信的。
　　　　order by從句--對欄位進行排序
　　　　　　格式：order by 欄位名[asc | desc]

　　　　　　注意:
　　　　　　　　1、asc是預設值，是升序排列
　　　　　　　　2、desc是降序排列
　　　　　　　　3、通常很多人認為order by從句中使用的列必須是顯示出來的列，實際上使用不顯示出來的列進行排序也是合法的。

　　　　　　格式：order by 欄位名1[asc|desc],欄位名2[asc|desc]

　　　　　　注意：
　　　　　　　　1、值的是欄位名1的值完全相同的情況下對相同的這些內容按照欄位名2進行排序。

　　　　limit 從句----限制結果集
　　　　　　格式1：limit m
　　　　　　　　注意：m代表返回多少行。　　　　

　　　　　　格式2：limit n,m
　　　　　　　　注意：
　　　　　　　　　　1、n表示從多少開始取值（第一條資料用0表示）,m表示返回多少行

        　　　　　　　　2、如果沒有足夠的行，那麼有多少就給多少。

　　統計查詢
　　　　count()用來進行計數，count(*)用來統計有多少條資料，count(欄位名)表示非null的值的個數。
　　　　min(欄位名) 算出最小值
　　　　max(欄位名) 算出最大值
　　　　sum(欄位名) 和
　　　　avg(欄位名) 平均數　

　　

　　　　group by 從句----分組
　　　　　　格式：group by 欄位名

　　　　　　格式:group by 欄位名 having 條件
　　　　　　　　where和having不一樣。where是查詢出合格資料，having是對合格資料進行查詢。

　　從句的順序：
　　　　　　select => from => where => group by ....having =>order by => limit

　　

　　　　完全限定：
　　　　　　庫名.表名
　　　　　　表名.欄位名

　　　　別名：
　　　　　　欄位名 as 你的別名

　　　　拼接欄位：
　　　　　　concat();//字元或欄位連結

　　　　聯集查詢：
　　　　　　步驟：
　　　　　　1、搞清楚表和表之間的關係
　　　　　　2、select * from 表1,表2
　　　　　　3、加上where條件
　　　　　　　　條件必須要給對了。如果不對你的結果就不對，如果不給條件就會出事。 笛卡爾乘積
　　　　　　4、將select * 替換為你想要的欄位名

　　　　子查詢

　　　　　　前置子查詢

　　　　　　　　mysql> select *,(select count(*) from types as t2 where t2.pid=t1.id) as sCount from types as t1;

　　　　　　後置子查詢 作為條件
　　　　　　　　mysql> select *from types where pid=(select id from types as t1 where t1.name=‘a ‘);

　　更改資料：
　　　　update 表名 欄位名=值,欄位名2=值2... where 條件
　　　　如果不加條件所有的資料表中的指定欄位的值都會更改。
　　刪除資料：
　　　　delete from 表名 where 條件

　　　　注意：
　　　　　　1、如果不加where條件會把表的所有記錄都刪除。
　　　　　　2、如果在使用delete的時候最好先使用select將結果查詢出來。
　　　　　　3、要刪除資料的時候不要使用delete語句建議使用truncate table 表名 因為這個快，而且還乾淨。

　　匯出mysql資料庫(在退出MySQL的時候)
　　　　mysqldump -u 使用者名稱 -p 資料庫名 > 匯出檔案名稱 整個資料庫匯出
　　　　mysqldump -u 使用者名稱 -p 資料庫名 表名 > 匯出檔案名稱 整個資料表匯出
　　匯入mysql資料庫，如果你要匯入必須確定這個庫已經存在。（在退出MySQL的時候）
　　　　mysql -u 使用者名稱 -p 資料庫名 < 資料庫檔案

修改使用者密碼：
　　方法1、 mysqladmin -u 使用者名稱 -p password 新密碼 退出情況下
　　方法2、set password for ‘使用者名稱‘@‘登入主機‘=password(‘新密碼‘); 進入MySQL情況下

　　mysql忘記密碼
　　windows方法：
　　　　1、關閉正在啟動並執行mysql
　　　　2、開啟dos跳轉到mysql的bin目錄下
　　　　　　cd C:\xampp\mysql\bin
　　　　3、輸入
　　　　　　mysqld --skip-grant-tables斷行符號
　　　　4、再開啟一個視窗進入到mysql的bin目錄下面
　　　　　　開啟dos跳轉到mysql的bin目錄下
　　　　　　cd C:\xampp\mysql\bin
　　　　5、進入mysql斷行符號
　　　　6、進入到mysql庫
　　　　7、修改記錄 update user set password=password(‘新密碼‘) where user=‘root‘
　　　　8、重新整理許可權
　　　　flush privileges
　　　　注意：你們現在的MySQL直接使用mysql命令就能進。MySQL在安裝的時候會預設建立一個匿名使用者這個匿名使用者主要用來做測試，這個帳號有test庫的所有許可權。
　　　　show databases;
　　　　為什麼要刪除匿名使用者。(在mysql裡操作)
　　　　drop user ‘‘@‘localhost‘;

　　linux方法：
　　　　MySQL root密碼的恢複方法之一
　　　　如果忘記了MySQL root密碼，可以用以下方法重新設定：
　　　　　　1.KILL掉系統裡的MySQL進程；
　　　　　　　　killall -TERM MySQLd
　　　　　　2.用以下命令啟動MySQL，以不檢查許可權的方式啟動；
　　　　　　　　safe_MySQLd --skip-grant-tables &
　　　　　　3.然後用空密碼方式使用root使用者登入 MySQL；
　　　　　　　　MySQL -u root
　　　　　　4.修改root使用者的密碼；
　　　　　　　　MySQL> update MySQL.user set password=PASSWORD(‘新密碼‘) where User=‘root‘;
　　　　　　　　MySQL> flush privileges;
　　　　　　　　MySQL> quit
　　　　　　　　重新啟動MySQL，就可以使用新密碼登入了。
　　　　MySQLroot密碼的恢複方法二
　　　　有可能你的系統沒有 safe_MySQLd 程式(比如我現在用的 ubuntu作業系統, apt-get安裝的MySQL) , 下面方法可以恢複
　　　　　　1.停止MySQLd；
　　　　　　　　sudo /etc/init.d/MySQL stop
　　　　　　　　(您可能有其它的方法,總之停止MySQLd的運行就可以了)
　　　　　　2.用以下命令啟動MySQL，以不檢查許可權的方式啟動；
　　　　　　　　MySQLd --skip-grant-tables &
　　　　　　3.然後用空密碼方式使用root使用者登入 MySQL；
　　　　　　　　MySQL -u root
　　　　　　4.修改root使用者的密碼；
　　　　　　　　MySQL> update MySQL.user set password=PASSWORD(‘newpassword‘) where User=‘root‘;
　　　　　　　　MySQL> flush privileges;
　　　　　　　　MySQL> quit
　　　　　　　　重新啟動MySQL
　　　　　　　　/etc/init.d/MySQL restart
　　　　　　　　就可以使用新密碼 newpassword 登入了。

欄位類型
　　在定義資料表時，會指定欄位的類型。
　　1、整型資料
　　　　注意：
　　　　　　1、整形設定的長度不是能夠儲存多少位。而是顯示的寬度。什麼是顯示的寬度，先留著一會告訴你。
　　　　　　2、整形能夠儲存多少數值完全是取決於最大值。
　　　　　　3、預設的時候整型建立的是有符號的。{大於最大值時，只顯示到最大值}
　　　　　　問題：顯示寬度、無符號是怎麼回事。
　　2、浮點、定點
　　　　浮點數和定點數:
　　　　　　1、聲明他們的時候，有一個M和D，M說明總共有多少位，D表示小數點後面幾位。
　　　　　　2、浮點數如果不寫精度（m）和標度（d），會按照實際精度值顯示。如果有精度和標度則會自動四捨五入。
　　　　　　3、定點數如果不寫精度和標度，會按照decimal(10,0)來進行操作。如果寫的話如果超過了M和D也將會自動四捨五入
　　　　　　4、進行運算時浮點數將會得到一個近似值，定點數將會得到一個精確值。
　　　　MySQL float用法：
　　　　　　MySQL浮點型和定點型可以用類型名稱後加（M，D）來表示，M表示該值的總共長度，D表示小數點後面的長度，M和D又稱為精度和 標度，如float(7,4)的可顯示為-999.9999，MySQL儲存值時                      進行四捨五入，如果插入999.00009，則結果為999.0001。FLOAT和 DOUBLE在不指定精度時，預設會按照實際的精度來顯示，而DECIMAL在不指定精度時，預設整數為10，小數為0。
　　　　　　總結：浮點數如果不寫精度和標度，則會按照實際顯示，如果有精度和標度，則會將資料四捨五入後插入，系統不報錯，定點數如 果不設定精度和標度，剛按照預設的（10,0）進行操作，如果                                資料超過了精度和標度值，則會報錯。
      　　　　註：：如果float的（M，D）小於要插入的值，則結果為 規定（M，D）能顯示的最大的值。
　　3、字元類型
　　　　注意：
　　　　　　1、text與blob兩者的區別是blob用來儲存位元據，text只能儲存字元資料。
　　　　　　2、char和varchar可以用來儲存MySQL中較短的字串：
　　　　區別：
　　　　　　1、char長度為固定長度。值為0～255，varchar可變長度長度為0～65535。
　　　　　　2、定長（聲明完成後使用固定的位元組），變長（聲明完成後根據你存的東西來調整儲存的位元組數）
　　　　　　　　a char(255) 255
　　　　　　　　a varchar(255) 1
　　　　　　3、char和varchar在超出長度時都會截取字串到指定的長度。
　　　　　　4、定長和變長是建立在已經設定的長度範圍內。
　　4、枚舉類型

　　　　sex enum(‘NAN‘,‘NV‘)　　　　　

　　　　注意：
　　　　　　1、枚舉方式的取值範圍需要在建立表的時候顯式的指定。
　　　　　　2、如果超處了枚舉裡面指定的值，將會插入一個“空”。
　　　　　　3、枚舉類型只能從值的集合中選取一個值，不能一次選取多個值。
　　　　　　4、enum的值不區分大小寫。
　　　　　　5、對於1～255個成員的枚舉需要1個位元組來儲存，如果256～65535個成員需要2個位元組來儲存，但是最多隻能有65535個成員。
　　　　　　6、插入值的時候，包括在聲明這個欄位的時候必須要使用引號將插入的值包起來。如果直接寫1或2那麼表示插入enum這個類型中的第一個或第二個

　　5、集合類型

　　　　aihao set(‘a‘,‘b‘,‘c‘,‘d‘)

　　　　注意：
　　　　1、set類型可以存多個值。
　　　　　　1～8個成員 1個位元組
　　　　　　9～16個成員 2個位元組
　　　　　　17～24個成員 3個位元組
　　　　　　25～32個成員 4個位元組
　　　　　　33～64個成員 8個位元組
　　　　2、set和enum除了儲存以外，主要區別在於set類型一次可以選取多個值，enum只能選取一個。
　　　　3、如果有重複的成員會保留一個。
　　　　4、超出列表的範圍的值會被忽略。

 

約束條件：對欄位進行一些約束
　　公用的約束條件：
　　　　1、null、not null：
　　　　　　null：預設為null，插入值時沒有往該欄位中插入值，預設的時候該欄位的值會為null。
　　　　　　not null：指定了not null的時候必須在插入值的時候在該欄位給定對應的值。否則沒有值。

　　　　注意：約束條件寫在欄位類型的後面。

 　　　　2、default ：在不插入該欄位的時候預設插入的值。　　

　　整型約束條件：
　　　　1、zerofill，0填充。一般和整型後面的設定的寬度一起使用，如果數值長度小於指定的長度那麼前面將使用0進行填充。

　　　　2、unsigned（無符號），如果要在欄位裡面儲存非負數或需要較大的上限的時，可以使用該選項，取值範圍是從0開始。
　　　　　　注意：unsigned必須緊跟著欄位類型

　　　　3、auto_increment，自增，產生唯一標識或順序的值的時候，可以使用該屬性。這個屬性只能用於整型，值一般從1開始，每行+1。插入一個null到一個auto_increment列的時候，也會進行自增。

　　　　　　注意：
　　　　　　　　1、使用auto_increment的時候必須後面緊跟著一個primary key 或unique才能使用。
　　　　　　　　2、出現過的最大的數+1

索引：索引是資料庫中用來提高搜尋效能的，我們在做資料庫最佳化的時候通常先乾的第一件事就是做索引最佳化。
　　注意：他是自動使用的，不需要我們刻意的使用。

　　索引的分類：
　　　　1、常規索引（index），最基本的索引，沒有任何的限制。

　　　　　　index 索引名(欄位名)

　　　　　　index xxoo(name)　

　　　　刪除索引：
　　　　　　drop index 索引名 on 表名;

　　　　建立完表的情況下添加索引：
　　　　　　create index 索引名 on 表名(欄位名)

　　　　2、唯一索引（unique），和常規索引相似，但是索引列的值必須唯一。你可以給多個列加上unique

　　　　　　unique xoxo(name)　　　　　　

　　　　刪除索引：
　　　　　　drop index 索引名 on 表名;

　　　　建立完表的情況下添加索引：
　　　　　　create unique index 索引名 on 表名(欄位名);

　　　　3、主鍵索引（primary key），和唯一索引相似，一個表中只能有一個主鍵索引。

　　　　　　id int unsigned primary key,

　　　　刪除索引：
　　　　　　alter table 表名 drop primary key;

　　　　注意：
　　　　　　如果說沒有auto_increment可以直接刪，如果有auto_increment需要先刪除auto_increment然後在刪除掉primary key

　　　　　　1、刪除主鍵索引的時候如果該欄位有auto_increment那麼需要先將auto_increment刪除掉，然後再刪除primary  key

　　　　

　　建立索引的規則：
　　　　1、最適合建立索引的列通常是出現在where子句中的列。
　　　　2、索引不同列的值不同的越多索引效果越好。
　　　　3、資料越多索引效果越好。
　　　　4、不要過度使用索引，每個額外的索引都需要佔用額外的磁碟空間。降低寫的效能。

儲存引擎：
　　引擎：使用者可以根據不同的需要選擇如何儲存資料。可以根據引擎來提供一些功能，並且提高應用效率。

　　查看所有引擎 show engines

　　　　注意：
　　　　　　1、MyISAM表引擎，不支援事務，優勢就是訪問速度塊。但是他是表鎖，發生衝突的機率比較高。如果對事務完整性沒有要求，或你的應用程式以select、insert為主的時候可以用MyISAM表引擎。

　　　　　　　　每個MyISAM在磁碟上儲存成3個檔案，和表名都一樣。
　　　　　　　　.frm(儲存表的定義)
　　　　　　　　.MYD（儲存資料）
　　　　　　　　.MYI(儲存索引)

　　　　　　2、InnoDB表：InnoDB表寫的處理效果會差一些，並且佔用更多的磁碟空間。但是行鎖，發生衝突的機率比較低。
　　　　　　　　如果在你的應用程式中對事務的完整性有比較高的要求，資料操作除了插入、查詢外還有很多的更新、刪除。那麼可以使用InnoDB

 

字元集
　　MYSQL字元集涉及兩個概念：
　　　　字元集：用來定義MySQL儲存字串的方式。
　　　　校對規則：定義了比較字串的方式。

　　一個字元集有多個校對規則。
　　　　校對規則通常以_ci結尾的表示大小寫不敏感，_cs結尾的表示大小寫敏感。

　　查看系統中的字元集 show character set;

　　　　一定要注意utf8不是utf-8

　　字元集分為4個層級：伺服器級、資料庫級、資料表級、資料欄位

　　如果沒有設定本級的字元集那麼預設使用上一級的字元集

　　設定資料庫的字元集
　　　　mysql> create database lamp129 default character set utf8;

　　　　如果設定了資料庫的字元集那麼，資料表的字元集就會預設使用資料庫的字元集

　　設定資料表的字元集   charset=utf8;

亂碼問題：
　　1、保證你的頁面儲存格式是UTF-8格式。無bom
　　2、保證你的瀏覽器解析的時候是UTF-8
　　3、保證你的資料庫中是utf8格式。
　　4、連結的字元集也要是utf8
　　mysql_set_charset(‘utf8‘);

 

在建立表的時候你沒有設定字元集
　　1、更改表的字元集
　　2、更改欄位的字元集

//獲得auto_increment列最後自增的值。
　　mysql_insert_id();//這裡面不用寫參數，如果要寫也是寫連結的資源。

改表的字元集
　　alter table 表名 default charset=要修改字元集
　　alter table 表名 modify 欄位名 欄位類型 約束條件 character set 你要修改的字元集

 

修改表
　　文法：alter table 表名 動作
　　　　修改表的欄位名並且修改欄位類型:alter table 表名 change 舊欄位名 新欄位名 欄位類型 約束條件
　　　　修改表的欄位類型：alter table 表名 modify 欄位名 欄位類型

　　　　注意：change可以改變欄位名、modify不可以。但是都能改變欄位類型。

添加欄位：alter table 表名 add 欄位名 欄位類型 [first|after 欄位名]
　　mysql> alter table user add height double(3,2);

刪除欄位：alter table 表名 drop 欄位名

修改表名：alter table 舊錶明 rename as 新表明

修改字元集:alter table 表名 default character set 新字元集
　　注意：
　　1、如果真的更改了表的字元集那麼，欄位中的字元集是不會自動更改的

 

MySQL資料庫的基礎操作及理解