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