##數據表的引擎對比
MySQL支持MyISAM ,InnoDB, HEAP, BDB, ARCHIVE, CSV等多種數據表引擎類型
1.InnoDB 是較新的事務安全型存儲引擎，用于事務處理應用程序，支持BDB的幾乎所有特性，并具有眾多新特性，包括ACID事務支持。
InnoDB表類型
特點: InnoDB給MySQL提供了具有提交,回滾和崩潰回復能力的事務安全存儲引擎.InnoDB也支持外鍵機制,InnoDB類型的表與去他MySQL的表類型混合起來,在同一查詢中也可以混合
缺點: InnoDB數據表的空間占用量要比同樣內容的MyISAM數據表大很多,另外,該類型不支持全文索引
特性：
1.事務處理機制
2.支持外鏈
3.崩潰后能立即恢復
4.支持外鍵功能，級聯(lián)刪除
5.支持并發(fā)能力
6.在硬盤上的存儲方式：InnoBDB frm
7.最新版本的Mysql已經計劃移除對BDB的支持，轉而全力發(fā)展InnoDB。InnoDB對Mysql有更好的特性支持，而且開發(fā)社區(qū)活躍。
2.MyISAM 是默認的MySQL插件式存儲引擎，它是基于ISAM類型，但它增加了許多有用的擴展，它是在Web、數據倉儲和其他應用環(huán)境下最常使用的存儲引擎之一。注意，通過更改STORAGE_ENGINE配置變量，能夠方便地更改MySQL服務器的默認存儲引擎
MyISAM表類型
優(yōu)點：
1.比ISAM表更小，所占資源更少
2.可以在不同平臺間二進制移植表的類型在創(chuàng)建表時指定。
特點: 成熟,穩(wěn)定,和易于管理,他使用一種表格鎖定的機制來優(yōu)化多個并發(fā)的讀/寫操作,MyISAM強調了快速的讀寫操作,所以在web前端會有大量的數據要進行讀取操作
MYISAM和InnoDB的總結: 如果希望以最節(jié)約空間和時間揮著響應速度快的方式來管理數據表,MyISAM數據表就應該是首選,如果應用程序需要用到事務,使用外鍵或需要更高的安全性,以及需要允許很多用戶同時修改某張數據表的數據,那么InnoDB更值得考慮
3.ISAM 是MyISAM類型出現(xiàn)之前MySQL表使用的默認類型，現(xiàn)在已經被MyISAM代替。
4.Memory （HEAP） 是MySQL表中訪問最快的表，將所有數據保存在RAM中，在需要快速查找引用和其他類似數據的環(huán)境下，可提供極快的訪問。注意，這種類型下數據是非持久化設計的，它一般適應于臨時表，如果MySQL或者服務器崩潰，表中數據全部丟失。
5.Merge 是一種值得關注的新式表，它是由一組MyISAM表組成，之所合并主要出于性能上考慮，因為它能夠 提高搜索速度，提高修復效率，節(jié)省磁盤空 間。允許MySQL DBA或開發(fā)人員將一系列等同的MyISAM表以邏輯方式組合在一起，并作為1個對象引用它們。對于諸如數據倉儲等VLDB環(huán)境十分適合。有時它以 MRG_MYISAM 名稱出現(xiàn)。
6.Archive 為大量很少引用的歷史、歸檔、或安全審計信息的存儲和檢索提供了完美的解決方案。
7.Federated 能夠將多個分離的MySQL服務器鏈接起來，從多個物理服務器創(chuàng)建一個邏輯數據庫。十分適合于分布式環(huán)境或數據集市環(huán)境。
8.NDBCluster/Cluster/NDB MySQL的簇式數據庫引擎，尤其適合于具有高性能查找要求的應用程序，這類查找需求還要求具有最高的正常工作時間和可用性。
9.CSV 使用標準的CSV格式，由逗號隔開的文本文件，適應于外部數據交換
10.Blackhole 它會接受數據但不保存，而且對任何檢索請求返回一個空集。它一般適應于數據會被自動復制并不進行本地保存的分布式數據庫設計。（不確定的）似乎也可以用于臨時禁止/忽略對數據庫的應用程序輸入。
11.Example 這是一個測試引擎，你可以創(chuàng)建一個這樣的表，但既不能寫入數據，也不能檢索數據。它似乎是一個針對MySql開發(fā)者提供的示例引擎。
查看默認的引擎
show variables like 'default_storage_engine';
查看支持的引擎
show engines
查看當前庫里面所有的表引擎
show table status from 庫名
修改表引擎
alter table 表名 engine = 新的表引擎
查看當前選擇的數據庫
select database()
查看當前的數據庫的版本號
select version()
創(chuàng)建一個表結構和另一個表一樣的結構
create table t3 like t1;
那么現(xiàn)在t3表的結構和t1表的結構是一樣的,但是要注意數據是沒有的
## 數據表創(chuàng)建索引
索引在數據庫開發(fā)中起著非常重要的作用,通過在表字段中建立索引可以優(yōu)化查詢,確保數據的唯一性.并且可以對任何全文索引字段中大量文本的所搜進行優(yōu)化,在MySQL中有4類索引:主鍵索引(PRIMARY KEY)、唯一索引(UNIQUE)、常規(guī)索引(INDEX)、全文索引(FULLTEXT).
查看表中都有哪些索引
show index from 表名\G
如果在創(chuàng)建索引的時候,不添加索引名的話 默認會把字段名當做索引名
###常規(guī)索引
添加表字段的常規(guī)索引
create index 索引名 on 表名(字段名)
alter table 表名 add index 索引名(字段名)
在創(chuàng)建表的時候也可以進行添加
create table t2(
id int(10) unsigned auto_increment primary key,
name varchar(255), index 索引名(字段名)
);
刪除表字段常規(guī)索引
drop index 索引名 on 表名
alter table 表名 drop index 索引名
###唯一索引
添加表字段的唯一索引
create unique index 索引名 on 表名(字段名)
alter table 表名 add unique (字段名)
在創(chuàng)建表的時候也可以進行添加
create table t2(
id int(10) unsigned auto_increment primary key,
name varchar(255), unique 索引名(字段名)
);
刪除唯一索引
drop index 索引名 on 表名
###主鍵索引
添加表字段的主鍵索引
alter table 表名 add primary key (字段名)
添加自增
alter table 表名 modify id int(4) auto_increment
刪除主鍵索引
如果字段有了auto_increment 和 primary key的時候,需要刪除主鍵的話,
先刪除自增,然后再刪除主鍵
刪除自增
alter table 表名 change 字段名 字段名 類名
刪除主鍵
alter table 表名 drop primary key
在數據庫中唯一索引和主鍵索引的區(qū)別
唯一索引是可以給每個字段進行添加的,添加完了之后字段里面的值就不可以重復了,主鍵索引和唯一索引類似,但是數據表里的主鍵索引只能加在一個字段里(一般都加在id上),id是自增的,索引不會有重復的時候出現(xiàn)
###全文索引
###全文索引
添加表字段的全文索引
alter table 表名 add fulltext (字段名)
刪除全文索引
drop index (索引名) on 表名
alter table 表名 drop index 索引名
##修改表的字段信息
修改表的字符集
alter table 表名 character set utf8
修改字段的類型
alter table 表名 modify 字段名 類型
修改字段的名字并同時修改字段類型
alter table 表名 change 舊字段名 新字段名 字段類型
修改字段的字符集
alter table 表名 modify 字段名 類型 character set utf8
添加新字段
alter table 表名 add 字段名 類型
刪除字段
alter table 表名 drop 字段名
修改表名
alter table 舊表名 rename as 新表名
##left join、right join、inner join
left join(左聯(lián)接) 返回包括左表中的所有記錄和右表中聯(lián)結字段相等的記錄
right join(右聯(lián)接) 返回包括右表中的所有記錄和左表中聯(lián)結字段相等的記錄
inner join(等值連接) 只返回兩個表中聯(lián)結字段相等的行
舉例:
left join
select * from p1 left join p2 on p1.id = p2.id;
right join
select * from p1 right join p2 on p1.id = p2.id;
inner join
select * from p1 inner join p2 on p1.id = p2.id;
##子查詢
1.單行子查詢
select name,age,score
from user
where age=(select age from stu where id = 1)；
2.多行子查詢
select name,age,class
from user
where age in (select age from stu where id < 10);
select name,age.class
from user
where age in (select age from stu where name like '王%');
##插入數據
1. 插入指定字段
insert into t1(字段1,字段2,字段3...) values ('值1','值2','值3',....);
2. 插入所有的字段
insert into t1 values ('值1','值2','值3',....);
3. 插入多條數據
insert into t1(字段1,字段2,字段3...) values ('值1','值2'),('值1','值2')....;
4. 插入結果
insert into t1(字段1,字段2,字段3...) select 字段1,字段2,字段3... from t1;
insert into t1(name,age) select name,age from t1;
5. 插入單條數據
insert into t1 set 字段1=值1,字段2=值2....;
##分組 group by
1. 查詢各個班級中年齡大于20，性別為男的人數姓名和班級 (數據表: stu)
為了防止班級里面有重名的現(xiàn)象發(fā)生(例如 兩個男生都是20歲 以上的) 所以會有count(*)
select group_concat(name),classid,count(name) from stu where sex = "男" and age > 20 group by classid;
2. 學校評選先進學生,要求平均成績大于等于90分的學生,并且語文課必須在95分以上，請列出有資格的學生 (數據表: score)
select classid,name from sc where (yw+sx+en)/3 >= 90 and yw>=95 group by classid,name;
select classid,group_concat(name) from sc where (yw+sx+en)/3>=90 and yw>=95 group by classid;
3. 用一條sql語句查詢出每門課都大于80分的學生姓名 (數據表: courseinfo)
select name from courseinfo group by name having min(score) > 80;
select DISTINCT name from courseinfo where name not in (select distinct name from courseinfo where score < 80);
##創(chuàng)建視圖
視圖是一個虛擬表，其內容由查詢定義。同真實的表一樣，視圖包含一系列帶有名稱的列和行數據。但是，視圖并不在數據庫中以存儲的數據值集形式存在。行和列數據來自由定義視圖的查詢所引用的表，并且在引用視圖時動態(tài)生成。
如果你想創(chuàng)建視圖的話 首先你的數據庫要有創(chuàng)建視圖的權限,不然的話是創(chuàng)建不成功的
create view v1 as select * from t1 where id > 1;
創(chuàng)建v1的視圖 把查詢t1表里面的符合條件的數據放到v1里面
注意:如果主表不存在 那么視圖表失效
刪除視圖表
drop view v1;
##mysql內置函數
字符串鏈接 concat('name','id');
最大值 max('price')
最小值 min('price')
平均值 avg('price')
定義變量 set @a = 10
獲取變量的值 select @a
計算 select @a + @b; select 10 + 20
字符串轉換成大寫和小寫 select ucase('name') select lcase('NAME')
計算字符串的長度 select length('namesss') as len;
select * from user where length(name) > 5; 名字大于5的長度找出來
去除兩側的空白 select trim(' pass ');
select length(trim(' over '));
隨機數 從0-1之間的隨機數 select rand();
獲取0-10之間的隨機整數向上取整 select ceil(rand()*10)
##mysql預處理
設置一個占位符
prepare 名字 from "select * from t2 where id > ?";
設置變量
set @id = 2;
執(zhí)行
execute 名字 using @id;
設置多個占位符
prepare 名字 from "select * from t2 where id > ? and id < ?";
設置變量
set @id = 2;
set @id1 = 5;
執(zhí)行
execute 名字 using @id,$id1;
##mysql存儲過程
```
簡單的說，就是一組SQL語句集，功能強大，可以實現(xiàn)一些比較復雜的邏輯功能
MySQL存儲過程的創(chuàng)建
創(chuàng)建的格式：CREATE PROCEDURE 過程名 ([過程參數[,...]])
[特性 ...]過程體
舉例說明：
修改結束符
mysql-> delimiter //
創(chuàng)建
mysql-> create procedure proc()
-> begin
-> select * from user;
->end//
使用
mysql-> call proc()//
修改結束符
mysql-> delimiter ;
這里需要注意的是DELIMITER //和DELIMITER ;兩句，DELIMITER是分割符的意思，因為MySQL默認以";"為分隔符，如果我們沒有聲明分割符，那么編譯器會把存儲過程當成SQL語句進行處理，則存儲過程的編譯過程會報錯，所以要事先用DELIMITER關鍵字申明當前段分隔符，這樣MySQL才會將";"當做存儲過程中的代碼，不會執(zhí)行這些代碼，用完了之后要把分隔符還原。
格式:
\d // 把結束符的分號改成 //結束本條sql語句
\d 和 delimiter 是一樣的 都可以修改mysql的結束符
（2）過程體的開始與結束使用BEGIN與END進行標識
（3）存儲過程根據需要可能會有輸入輸出參數，輸入參數x 類型int型 ,輸出參數num 類型是int型，如果有多個參數用","分割開。
舉例說明:
->create procedure lamp172(in x int,out num int)
-> begin
-> set num = x + 20;
-> end//
調用
->call lamp172(10,@s)//
->select @s as num//
使用循環(huán)插入數據
->create procedure total(num int(10))
->begin
->set @i = 0;
->while @i<num do
->insesrt into t1 values (null,'www',20);
->set @i = @i+1;
->end while;
->end//
注意 : 寫存儲過程名字的時候 不要使用關鍵字 和 數字
例如:
create procedure 111() 數字錯誤
create procedure add() 關鍵字錯誤
刪除存儲名
drop procedure if exists 存儲名
```
##mysql觸發(fā)器 trigger
```
在t2表插入數據的同時使用trigger 修改其他表的信息
例如:
create trigger tg_1 before insert on t2 for each row
begin
insert into t3(name) values (new.name);
end//
插入數據
insert into t3(name) values ('wwww')//
create trigger tg_2 after update on t1 for each row
begin
update t2 set t2.name= new.name where t2.id = old.id;
end//
update t1 set name = 'xxoo' where id = 2//
create trigger tg_3 after delete on t1 for each row
begin
delete from t2 where id = old.gid; // old.gid 這里面的gid是t1表與t2表相關的id號
end//
delete from t1 where gid = 2// gid=2 ====> old.gid
//查看觸發(fā)器
show triggers;
//刪除觸發(fā)器
drop trigger 名字;
```
##mysql 讀寫鎖
```
讀鎖: 所有的終端都可以讀取數據 但是不能操作數據(增 刪 改)
lock table t2 read
解鎖
unlock tables;
寫鎖: 當前的終端對表進行寫鎖之后,代表本終端可以進行讀寫操作,但別的終端不可以進行讀寫操作
lock table t2 write;
解鎖
unlock tables;
```
##mysql的權限管理
格式:
grant 權限 on 數據庫.數據表 to 用戶名@登錄主機 identified by '密碼'
舉例:
grant select,insert,update,delete on \*.\* to wjs@'%' identified by "12345"
----> %的問題
如果這樣設置權限的話是非常不安全的,所用的用戶都可以進入到你的數據庫,對里面的所有的數據表進行增刪改查
安全的做法
grant select,insert,update,delete on pass.* to root@localhost identified by '12345'
只針對localhost主機里面的 pass數據庫里面的數據表進行增刪該查
##mysql表數據的備份與恢復操作
```
使用SQL語句備份和恢復
你可以使用SELECT INTO OUTFILE語句備份數據，并用LOAD DATA INFILE語句恢復數據。
這種方法只能導出數據的內容，不包括表的結構，如果表的結構文件損壞，你必須要先恢復原來的表的結構。
格式:
備份操作
SELECT * FROM tbl_name INTO {OUTFILE | DUMPFILE} 'file_name.txt' ;
恢復操作
LOAD DATA [LOW_PRIORITY][LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name;
舉例:
導出
select * from t2 into outfile '../lamp185.txt'; //相對于mysql.exe文件的路徑
清空
truncate table t2;
導入
load data infile '../lamp185.txt' into table t2;
```
##開啟慢查詢記錄
需要進行配置 在mysql中的my.ini中進行設置
slow-query-log = 1 # 開啟慢查詢操作
slow-query-log-file = slow.log # 文件默認存放在mysql的data目錄下 (文件名自定義)
long-query-time = 1 # 超時時間 如果執(zhí)行的sql語句超過1秒鐘就把語句存放到slow.log文件中 (不能使用小數)
Copyright 2014-2025 http://ipnx.cn/ All Rights Reserved | php.cn | 湘ICP備2023035733號
收藏
