mysql基礎(chǔ)知識
發(fā)表時間:2023-07-20 來源:明輝站整理相關(guān)軟件相關(guān)文章人氣:
[摘要]本篇文章主要介紹MySQL基礎(chǔ)知識,數(shù)據(jù)庫它是一個以某種有組織的方式存儲的數(shù)據(jù)集合,保存有組織的數(shù)據(jù)的容器(通常是一個文件或一組文件),感興趣的朋友了解一下。SQL分類SQL 主要語句可以劃分為一下...
本篇文章主要介紹MySQL基礎(chǔ)知識,數(shù)據(jù)庫它是一個以某種有組織的方式存儲的數(shù)據(jù)集合,保存有組織的數(shù)據(jù)的容器(通常是一個文件或一組文件),感興趣的朋友了解一下。
SQL分類
SQL 主要語句可以劃分為一下3類
DDL:數(shù)據(jù)定義語言,這些語句定義不同的數(shù)據(jù)段、數(shù)據(jù)庫、表、列、索引等數(shù)據(jù)庫對象。常用語句關(guān)鍵字主要包括create,drop,alter等
DML:數(shù)據(jù)操作語句,用于添加、刪除、更新和查詢數(shù)據(jù)庫記錄,并檢查數(shù)據(jù)完整性。常用語句關(guān)鍵字主要包括 insert,delete,update和select等。
DCL數(shù)據(jù)控制語句,用于控制不同數(shù)據(jù)段直接的許可和訪問級別的語句。這些語句定義了數(shù)據(jù)庫、表、字段、用戶的訪問權(quán)限和安全級別。主要 的語句包括關(guān)鍵字grant、revoke等
DDL語句
是對數(shù)據(jù)庫內(nèi)部的對象進(jìn)行創(chuàng)建 、刪除、修改等操作語言,它和DML語句最大的區(qū)別是DML只是對表內(nèi)部數(shù)據(jù)操作,而不涉及表的定義、結(jié)構(gòu)的修改,更不會涉及其他對象。DDL更多地由數(shù)據(jù)庫管理員(DBA)使用。
連接mysql服務(wù)器
mysql -uroot -p
創(chuàng)建數(shù)據(jù)庫test1
create database test1;
顯示有哪些數(shù)據(jù)庫
show databases;
<!-- mysql 自動創(chuàng)建的表有
information_schema:主要存儲了系統(tǒng)中的一些數(shù)據(jù)庫信息,比如用戶表信息、列信息、權(quán)限信息、字符集信息、分區(qū)信息等等
cluster:存儲了系統(tǒng)的集群信息
mysql:存儲了系統(tǒng)的用戶權(quán)限信息。
test:系統(tǒng)自動創(chuàng)建的測試數(shù)據(jù)庫,任何用戶都可以訪問 -->
選擇數(shù)據(jù)庫
use test1
顯示test1數(shù)據(jù)庫中創(chuàng)建的所有表
show tables
刪除數(shù)據(jù)庫
drop database test1;
創(chuàng)建表
create table emp(ename varchar(10),hiredata date,sal decimal(10,2),deptno int(2));
查看表定義
desc emp;
查看創(chuàng)建表的定義
show create table emp;
刪除表
drop table emp;
修改表
alter table emp modify ename varchar(20);
增加表字段
alter table emp add column age int(3);
刪除表字段
alter table emp drop column age;
字段改名
alter table emp change age age1 int(4);
<!-- change 和modify都可以修改表的定義,不同的是change后面需要寫兩次列名,不方便,但是change的優(yōu)點(diǎn)是可以修改列名稱,則modify則不能 -->
修改字段排序
alter table emp add birth date after ename;
alter table emp modify age int(3) first;
更改表名
alter table emp rename emp1;
DML語句
是指對數(shù)據(jù)庫中表記錄的操作,主要包括表記錄的插入(insert)、更新(update)、刪除(delete)和查詢(select)。
插入記錄
insert into emp(ename,hiredate,sal,deptno)values('zzx1','2000-01-01','2000',1);
也可以不用指定字段名稱,但是values后面的順序要和字段的排列順序一致
inset into emp('zzx1','2000-01-01','2000',1);
含可空字段、非空但是含有默認(rèn)值的字段、自增字段、可以不用再insert后的字段列表里面出現(xiàn),values后面只寫對應(yīng)字段名稱的value,沒寫的字段可以自動設(shè)置為
null、默認(rèn)值、自增的下一個數(shù)字
批量增加用逗號隔開
insert into dept values(5,'xxx'),(8,'xxx');
更新記錄
update emp set sal=4000 where ename='xxx';
刪除記錄
delete from emp where ename='doney';
查詢記錄select * from emp;
*表示所有記錄,也可以用逗號隔開的字段來選擇查詢
查詢不重復(fù)的記錄select distinct deptno from emp;
條件查詢
用where關(guān)鍵字來實(shí)現(xiàn),可以使用<>!=等多條件可以使用or、and等
排序和限制desc和asc是排序關(guān)鍵字,desc是降序、asc是升序排列 ORDER BY 排序,默認(rèn)是升序select * from emp order by sal;
如果排序字段的值一樣,則值相同的字段按照第二個排序字段進(jìn)行排序,如果只有一個排序字段,則相同字段將會無序排序select * from emp order by deptno,sal desc;
限制select * from emp order by sal limit 3;//前者是起始偏移量,后者是顯示行數(shù)select * from emp order by sal limit 1,3;
limit 和order by 一起使用來做分頁
聚合
用戶做一下些匯總操作
sum(求和),count(*)(記錄數(shù)),max(最大值),min(最小值)
with rollup 是可選語法,表示是否對分類聚合后的結(jié)果進(jìn)行再匯總
having 表示對分類后的結(jié)果在進(jìn)行條件的過濾。
select deptno,count(1) from emp group by deptno having count(1)>=1;
表連接
大類上分為外連接和內(nèi)連接
外鏈接 又分為左連接和右連接
左連接:包含所以的左邊表中的記錄甚至是右邊表中沒有和它匹配的記錄。
右連接:同上
select ename,detname from emp left join dept on emp.deptno=dept.deptno;
左連接和右連接可以相互轉(zhuǎn)換
子查詢
select * from emp where deptno in(select deptno from dept);
如果子查詢記錄唯一,可以使用=替代in
select * from emp where deptno =(select deptno from dept limit 1);
記錄查詢
將兩個表的數(shù)據(jù)按照一定的查詢出來后,將結(jié)果合在一起顯示
union all 是將結(jié)果集合并在一起,而union是將union all后的結(jié)果在進(jìn)行一次distinct,去除重復(fù)
select deptno from emp union all select deptno from dept;
select demtno from emp union select deptno from dept;
? xxx 來查看
如果要查看類別 ? data types 具體的 ? int
查看語法 如 ? create table
數(shù)據(jù)類型
對于整形數(shù)據(jù),MySql還支持在類型名稱后面的小括號設(shè)置寬度,默認(rèn)設(shè)置為int(11),配合zerofill,
當(dāng)數(shù)字位數(shù)不夠的時候,用字符‘0’填充
alter table t1 modify id1 int zerofill
對于小數(shù),MySql 分為兩種,浮點(diǎn)數(shù)和定點(diǎn)數(shù)。浮點(diǎn)數(shù)包括float和double,而定點(diǎn)數(shù)只有decimal,定點(diǎn)數(shù)在Mysql內(nèi)部是以字符串形式存放,比浮點(diǎn)數(shù)更精確,適合用于貨幣等精度高的數(shù)據(jù)
浮點(diǎn)數(shù)和定點(diǎn)數(shù)可以用類型名稱加(M,D) M是幾位,D是位于小數(shù)點(diǎn)后面幾位。
日期類型
TIMESTAMP
創(chuàng)建一個字段為TIMESTAMP類型,系統(tǒng)自動創(chuàng)建了默認(rèn)值為CURRENT_TIMESTAMP(系統(tǒng)日期)。同時MySql規(guī)定TIMESTAMP類型字段一列只能有一個默認(rèn)值current_timestamp。如果修改會報錯.
TIMESTAMP 另一個重要特點(diǎn)是與時區(qū)有關(guān)。當(dāng)插入時間時,先轉(zhuǎn)換為本地時區(qū)后存放,而從數(shù)據(jù)庫取出時,同樣會將日期轉(zhuǎn)換為本地時區(qū)后顯示,這樣兩個時區(qū)的用戶看到同一個時區(qū)可能就不一樣
查看當(dāng)前時區(qū)
show variables like 'time_zone';
修改時區(qū)
set time_zone='+9.00';
DATETIME插入的格式
YYYY-MM-DD HH:MM:SS 或YY-MM-DD HH:MM:SS 的字符串允許任何標(biāo)點(diǎn)符號用來做時間部分的間隔符
如92@12@31 11^30^45
YYYYMMDDHHMMSS 或YYMMDDHHMMSS的格式?jīng)]有間隔符的字符串
字符串類型
CHAR和VARCHAR類型
兩者的主要區(qū)別是存儲方式不同:CHAR列的長度固定為創(chuàng)建表時聲明的長度,長度可以為0-255;二VARCHAR列中的值是可變長度。同時在檢索的時候,CHAR列刪除尾部的空格 ,而VARCHAR保留空格,由于CHAR是固定長度,所以它的處理速度別VARCHAR快很多,但是其缺點(diǎn)是浪費(fèi)內(nèi)存,在使用中VARCHAR被更多的使用
create table vc (v varchar(4),c char(4))
insert into vc values('ab ','ab ');
selelct length(v),length(c) from vc
//4,2
枚舉
create table t(gender enum('m','f'));
insert into t values('m'),('2'),('f'),('null')
//m,f,f,null
set類型
set類型可以一次選取多個成員
create table t2 (col set('a','b','c','d'));
INSERT into t2 VALUE ('a,b'),('a,d,a'),('a,b'),('a,c'),('a');
對于(a,d,a)這個包含重復(fù)成員的集合只取一次 結(jié)果為’a,d'
運(yùn)算符
DIV==/==除法獲取商
MOD==%==除法獲取余數(shù)
=和<=>區(qū)別
不能用于null比較,后者可以
between 使用格式 a between min and max 等價于 a>=min and a<=max
in的使用格式 a in(value1,value2...);
like 使用格式如 a like %123%,當(dāng)字符串含有123則返回1 否則返回0
REGEXP 使用格式 str REGEXP str_pat 當(dāng)str字符串中含有str_pat 相匹配的字符串,則返回1
位運(yùn)算
運(yùn)算符 | 作用 |
& | and |
| or
|
^ | xor |
~ | 位異或
|
> | 位右移
|
<< | 位左移
|
常用函數(shù)
字符串函數(shù)
函數(shù) | 作用 |
CONCAT(s1,s2,s3…)
| 連接s1到sn的字符串(任何字符串和null拼接都是null)
|
insert(str,x,y,instr)
| 將字符串str從x位置開始,y字符長的子串替換為字符串instr
|
lower(str)
| 將字符串str中所有字符變?yōu)樾?br> |
UPPER(str)
| 大寫
|
LEFT(str,x)
| 返回字符串str最左邊x個字符
|
RIGHT(str,x)
| 返回字符串str最右邊的x個字符
|
LPAD(str,n,pad)
| 用字符串pad對str最左邊進(jìn)行填充,直到長度為n個字符串長度
|
PRPAD(str,n,pad)
| 用字符串pad對str最右邊進(jìn)行填充,直到長度為n個字符串長度
|
LTRIM(str)
| 去掉字符串str左側(cè)的空格
|
RIGHT(str)
| 去掉字符串str行尾的空格
|
REPEAT(str,x)
| 返回str重復(fù)x次的結(jié)果
|
REPLACE(Str,a,b)
| 用字符串b替換字符串str中所有出現(xiàn)的字符串a(chǎn)
|
(STRCMPs1,s2)
| 比較字符串s1和s2
|
TRIM(str)
| 去掉行尾和行頭的空格
|
SUBSTRING(str,x,y)
| 返回字符串str x位置起y字符串長度的字串
|
數(shù)字函數(shù)
函數(shù)
| 功能
|
ABS(X)
| 返回x的絕對值
|
CEIL(X)
| 返回大于x的最小整數(shù)值
|
FLOOR(X)
| 返回小于x的最大整數(shù)值
|
MOD(x,y)
| 返回x/y的模
|
RAND()
| 返回0-1內(nèi)的隨機(jī)值
|
ROUND(x,y)
| 返回參數(shù)x的四舍五入的有y位小數(shù)的值
|
TRUNCATE(x,y) | 返回?cái)?shù)值x截?cái)酁閥位小樹的結(jié)果 |
日期和時間函數(shù)
函數(shù)
| 功能
|
CURDATE()
| 返回當(dāng)前日期
|
CURTIME()
| 返回當(dāng)前時間
|
NOW()
| 返回當(dāng)前的日期和時間
|
UNIX_TIMESTAMP(date)
| 返回date的unix時間戳
|
FROM_UNIXTIME
| 返回UNIX時間戳的日期值
|
WEEK(date)
| 返回日期date為一年中的第幾周
|
YEAR(date)
| 返回日期date的年份
|
HOUR(time)
| 返回time的小時值
|
MINUTE(time)
| 返回time的分鐘值
|
MONTHNAME(date)
| 返回date的月份名
|
DATE_FROMATE(date,fmt)
| 返回按字符串fmt格式化日期date值
|
DATE_ADD(date,interval expr type)
| 返回一個日期或時間值加上一個時間間隔的時間值
|
DATEDIFF(expr,expr2)
| 返回起始時間expr和結(jié)束時間expr2之間的天數(shù) |
流程函數(shù)
函數(shù)
| 功能
|
IF(value,t f)
| 如果value是真 返回 t;否則返回f
|
IFNULL(value1,value2)
| 如果value1不為空,返回value1,負(fù)責(zé)返回value2
|
CASE WHEN[value1] THEN[value2]…ELSE[default] END
| 如果value1是真,返回result1否則返回defalut
|
case [expr] WHEN[value1] THEN[value2]…ELSE[default] END
| 如果expr等于value1,返回result1否則返回defalut
|
實(shí)例
create table salary(userid int ,salary decimal(9,2));
insert into salary values(1,1000),(2,2000),(3,3000),(4,4000),(5,5000),(1,null);
select * from salary
select if(salary>2000,'high','low') from salary;
select ifnull(salary,0) from salary;
select case when salary <=2000 then 'low' else 'high' end from salary;
select case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end from salary;
其他函數(shù)
函數(shù)
| 功能
|
DATABASE()
| 返回的確數(shù)據(jù)庫庫名
|
VERSION()
| 返回當(dāng)前數(shù)據(jù)庫版本
|
USER()
| 返回當(dāng)前登錄用戶名
|
INET_ATON(IP)
| 返回ip地址的數(shù)字表示
|
INET_NTOA(num)
| 返回?cái)?shù)字代表的ip地址
|
PASSWORD(str)
| 返回字符串str加密版本
|
MD5()
| 返回字符串的md5值 |
MySql引擎
MySql支持的存儲引擎包括MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等,其中InnoDB和BDB提供事務(wù)安全表,用戶可以選擇不同的數(shù)據(jù)存儲引擎來提高應(yīng)用的效率
創(chuàng)建表如果不指定存儲引擎,系統(tǒng)默認(rèn)使用默認(rèn)存儲引擎,MySql5.5之前的默認(rèn)引擎是MyISAM,5.5之后改為InnoDB。如果要修改默認(rèn)的存儲引擎,可以在參數(shù)文件中設(shè)置default-table-type.
show ENGINES //查看的確支持的存儲引擎
//通過增加engine關(guān)鍵字設(shè)置新建表的儲存引擎z
create table ai(i bigint(20)not null auto_increment,primary key(i))engine=innodb default charset=gbk;
通過alter 來修改一個表的存儲引擎
ALTER TABLE ai ENGINE =MyISAM;
MyISAM
MyISAM 不支持事務(wù)、也不 不支持外鍵,其優(yōu)點(diǎn)是速度快,對事務(wù)完整性沒有要求。以SELECT和INSERT為主的應(yīng)用基本上都就可以使用這個表
InnoDB
InnoDB存儲引擎提供了具有提交、回滾和崩潰恢復(fù)能力的事務(wù)安全。但是對比MyISAM的存儲引擎,InnoDB寫的處理效率差一些,并且會占用更多的磁盤空間以保留數(shù)據(jù)和索引。
create table autoincre_demo (i smallint not null auto_increment,name varchar(10),primary key(i))engine=innodb;
insert into autoincre_demo values(1,'1'),(0,'2'),(null,'3')
如果插入空或者0,則實(shí)際插入的將是自動增長后的值。
可以通過以下語句強(qiáng)制設(shè)置自動增加列的初始值,默認(rèn)從1開始,但是該強(qiáng)制的默認(rèn)值是保留到內(nèi)存中,如果數(shù)據(jù)庫從起,這個強(qiáng)制的默認(rèn)值會丟失,就需要數(shù)據(jù)庫啟動后重新設(shè)置
ALTER TABLE *** auto_increment =n
MEMORY
memory 存儲引擎使用存在于內(nèi)存中的內(nèi)容來創(chuàng)建表,每個MEMORY表實(shí)際對應(yīng)一個磁盤文件,格式是.fm,MEMORY表的訪問非常快,因?yàn)樗臄?shù)據(jù)是放在內(nèi)存中,并且默認(rèn)使用HASH索引,但是一旦服務(wù)關(guān)閉,表中的數(shù)據(jù)就會
alter table t2 engine=memory;
show TABLE status like 't2'
給memory表創(chuàng)建索引?梢灾付╤ash索引還是btree索引
create index mem_hash using hash on tab_memory(city_id);
存儲過程和函數(shù)的相關(guān)操作.
在對儲存過程和函數(shù)操作時,需要首先確認(rèn)用戶是否具有相應(yīng)的權(quán)限。例如,創(chuàng)建存儲過程或者函數(shù)需要CREATE ROUTINE權(quán)限,修改或者刪除存儲過程或者函數(shù)需要ALTER ROUT
INE權(quán)限,執(zhí)行過程或者函數(shù)需要EXECUTE權(quán)限
創(chuàng)建一個新的過程 film_in_stock,該過程用來檢查 film_id和store_id對應(yīng)的inventory是否滿足要求,并且返回滿足的inventory_id 以及滿足要求的記錄數(shù)
CREATE PROCEDURE film_in_stock(in p_fim_id int,in p_store_id int,out p_film_count int)
READS sql data
begin
select inventory_id
from inventory
where film_id =p_film_id
and store_id=p_store_id
and inventory_in_stock(inventory_id);
SELECT found_rows() into p_film_count;
end $$
通常在創(chuàng)建過程和函數(shù)之前,都會通過DELIMITE $$命令將語句的結(jié)束符從';'修改成其他符號,這里使用‘$$’,這樣在過程和函數(shù)中的
';'就不會被MySql,解釋成語句的結(jié)束而錯誤。在存儲過程或者函數(shù)創(chuàng)建完成 通過‘DELIMITER;'命令在將結(jié)束符改回成';'
調(diào)用過程
CALL film_in_stock(2,2,@a);
存儲過程的好處在于處理邏輯都封裝在數(shù)據(jù)庫端,調(diào)用者不需要了解中間的處理邏輯,一旦邏輯改變,只需要修改存儲過程,對調(diào)用者的程序沒有影響
刪除存儲過程或者函數(shù)
一次只能刪除一個存儲過程或者函數(shù),刪除需要ALTER ROUTINE權(quán)限
drop procedure film_in_stock;
查看存儲過程或者函數(shù)狀態(tài)
show procedure status like 'film_in_stock';
查看存儲過程的函數(shù)定義
show create procedure film_in_stock
變量使用
存儲過程和函數(shù)中可以使用變量,在MySql 5.1版本中,變量不區(qū)分大小寫
變量的定義
通過DECLARE可以定義一個局部變量,該變量的作用范圍只能在BEGIN...END中,可以用在嵌套塊中
定義一個DATE類型的變量
DECLARE last_month_start date;
變量賦值 可以直接賦值,或者通過查詢賦值。直接賦值使用set,可以賦常量或者賦表達(dá)式
set var_name=expr [,var_name=expr]...
set last_month_start=date_sub(current_date(),interval month);
select col_name[,...] into var_name[,...] table_expr;
定義條件和處理
delimiter $$create procedure actor_insert()begin
declare continue handler for sqlstate '23000' set @x2=1;
set @x=1;
insert into actor(actor_id,first_name,last_name) values(201,'test','201');
set @x=2;
insert into actor(actor_id,first_name,last_name) values(1,'test','1');
set @x=3;end ;$$
調(diào)用處理函數(shù)時遇到主鍵重的錯誤會按照定義的處理方式去處理,由于定義的是CONTINUE 會繼續(xù)執(zhí)行下面的語句
還支持EXIT表示終止
光標(biāo)使用
聲明光標(biāo)
declare cursor_name cursor for select_statement
open光標(biāo)
open cursor_name
fetch光標(biāo)
fetch cursor_name into var_name[,var_name]...
close光標(biāo)
close cursor_name
delimiter $$
create procedure payment_stat()
begin
declare i_staff_id int;
declare d_amount decimal(5,2);
declare cur_payment cursor for select staff_id,amount from payment;
declare exit handler for not found close cur_payment;
set @x1=0;
set @x2=0;
open cur_payment;
REPEAT
FETCH cur_payment into i_staff_id,d_amount;
if i_staff_id =2 then
set @x1=@x1+d_amount;
else
set @x2=@x2+d_amount;
end if;
until 0 end repeat;
close cur_payment;
end;
$$
變量,條件,處理程序,光標(biāo)都是通過DECLARE定義的,她們之間是有先后順序要求的。
變量和條件必須在最前面聲明,然后才能是光標(biāo)的聲明,最后才可以是處理程序的聲明
控制語句
case
when i_staff_id =2 then
set @x1=@x1+d_amount; else
set @x2=@x2+d_amount;loop 和leave結(jié)合create procedure actor_insert()begin
set @x=0;
ins:loop
set @x=@x+1; if @x=100 then
leave ins; end if;
insert into actor(first_name,last_name) values('Test','201'); end loop ins;end;
$$
inerate 語句作用是跳過當(dāng)前循環(huán)的剩下語句,直接進(jìn)入下一輪循環(huán)create procedure actor_insert()begin
set @x=0;
ins:loop
set @x=@x+1; if @x=10 then
leave ins;
elseif mod(@x,2)=0 then
iterate ins; end if;
insert into actor(actor_id,first_name,last_name) values(@x+200,'test',@x); end loop ins;end;
$$repeat 語句 有條件的循環(huán)控制語句,當(dāng)滿足條件的時候退出循環(huán)repeat
fetch cur_payment into i_staff_id,d_amount; if i_staff_id =2 then
set @x1=@x1+d_amount; else
set @x2=@x2+d_amount; end if; until 0 end repeat;whiledelimiter $$create procedure loop_demo()begin
set @x=1,@x1=1;
repeat
set @x=@x+1; until @x>0 end repeat; while @x<1 do
set @x=@x+1; end while; end;
$$//創(chuàng)建事件調(diào)度器CREATE EVEN test_event_1 ON SCHEDULE
EVERY 5 SECONDDOINSERT INTO dept(deptno,deptname)
VALUES(3,'3');//查看本地調(diào)度器狀態(tài)
show variables like '%scheduler%'; //打開調(diào)度器
set global event_scheduler=1; //查看后臺進(jìn)程
show processlist; //創(chuàng)建一個新的定時器 定時清空表,防止表變大,這類觸發(fā)器非常適合去定期清空臨時表或者日志表
create event trunc_test on schedule every 1 minute do truncate table test;
禁用調(diào)度器或者刪除
alter event test_event_1 disable;
drop event test_event_1;
SQL Mode
在MySql中,SQLMode常用來解決下面幾類問題
通過設(shè)置SQL Mode,可以完成不同嚴(yán)格程度的數(shù)據(jù)校驗(yàn),有效的保障數(shù)據(jù)準(zhǔn)確性。
通過設(shè)置SQL Mode,為ANSI模式,來保證大多數(shù)SQL符合標(biāo)準(zhǔn)的Sql語法,這樣應(yīng)用在不同數(shù)據(jù)庫之間進(jìn)行遷移時,則不需要對業(yè)務(wù)SQL進(jìn)行較大的修改
在不同數(shù)據(jù)庫之間進(jìn)行數(shù)據(jù)遷移之前,通過設(shè)置SQL Mode可以使MySQL上的數(shù)據(jù)更方便地遷移到目標(biāo)數(shù)據(jù)庫中
查看 SQL Mode命令select @@sql_mode
插入一個出國實(shí)際定義值的大小varchar(10)insert into value('123400000000000000000000000000000');//查看warning內(nèi)容show warningsselect * from t 這里對插入的數(shù)據(jù)
進(jìn)行截取前10位
設(shè)置SQL Mode為 嚴(yán)格模式set session sql_mode='STRICT_TRANS_TABLES'再次插入insert into value('123400000000000000000000000000000'); 直接給出ERROR,而不是
warning
SQL Mode常見功能
校驗(yàn)日期是合法性set seesion sql_mode='ANSI'insert into t values('2007-04-31')
結(jié)果是 插入值變成'0000-00-00 00:00:00' 并且系統(tǒng)給出warning 而在TRADITIONAL模式下,直接提示日期非法,拒絕插入,同時Mode(x,0)也會報錯
qidon NO_BACKSLASH_ESCAPES模式,使反斜杠成為普通字符,在導(dǎo)入數(shù)據(jù)時,如果數(shù)據(jù)含有反斜杠字符,你們啟動NO_BACKSLASH_ESCAPES模式,保證數(shù)據(jù)的正確性
啟動PIPES_AS_CONCAT。將 視為字符串連接符,在Oracle等數(shù)據(jù)庫中, 被視為字符串的連接操作符,所以在其他數(shù)據(jù)庫中含有 操作符的sql在MySql將無法執(zhí)行,為了解決這個
問題mysql提供了PIPES_AS_CONCAT模式、
MySql分區(qū)
MySql從5.1版本開始支持分區(qū),分區(qū)是指按照一定的規(guī)則,數(shù)據(jù)庫把一個表分解成多個更小的,更容易管理的部分。就訪問數(shù)據(jù)庫的應(yīng)用而言,邏輯上只有一個表或一個索引,但是實(shí)際上
這個表可能由數(shù)10個物理分區(qū)對象組成,每個分區(qū)都是一個獨(dú)立的對象,可以獨(dú)自處理,可以作為表的一部分進(jìn)行處理。分區(qū)對應(yīng)用而言是完全透明的,不影響應(yīng)用的業(yè)務(wù)邏輯優(yōu)點(diǎn)
和單個磁盤或者文件系統(tǒng)分區(qū)相比,可以存儲更多數(shù)據(jù)
優(yōu)化查詢。在where子句中包含分區(qū)條件,可以只掃描必要的一個或多個分區(qū)來提高查詢效率;同時在涉及SUM()和COUNT()這類聚合函數(shù)的查詢時,可以容易的在每個分區(qū)上并行
處理,最終只需要匯總所有分區(qū)的結(jié)果
對于已經(jīng)過期或者不需要保存的數(shù)據(jù),可以通過刪除與這些數(shù)據(jù)有關(guān)的分區(qū)來快速刪除數(shù)據(jù)
跨多個磁盤來分散數(shù)據(jù)查詢,以獲得更大的查詢吞吐量
分區(qū)有利于管理非常大的表,它采用 分而治之的邏輯,分區(qū)引入分區(qū)鍵的概念,分區(qū)鍵用于根據(jù)某個區(qū)間鍵,特定值列表或者HASH函數(shù)執(zhí)行數(shù)據(jù)的聚集,讓數(shù)據(jù)根據(jù)規(guī)則分布在不同的分區(qū)
中,讓一個大對象變成一些小對象
show VARIABLES like '%partition%' 查看是否支持分區(qū)
Mysql支持大部分存儲引起如MyISAM,INNODb,Memory等存儲引擎,創(chuàng)建分區(qū),在5.1版本中,同一個分區(qū)表的所以分區(qū)必須使用同一個存儲引擎;
在同要給表上,不能對一個分區(qū)使用MyISAM引擎和Innodb引擎,但是在同一個MySQL服務(wù)器服務(wù)器上,甚至同一個數(shù)據(jù)庫中,對于不同的分區(qū)表使用不同的存儲引擎
分區(qū)類型
range分區(qū):基于一個給定連續(xù)區(qū)間范圍,把數(shù)據(jù)分配到不同的分區(qū)。
LIST分區(qū):類似RANGE分區(qū),區(qū)別在LIST分區(qū)是基于枚舉出的值列表分區(qū),RANGE是基于給定的連續(xù)區(qū)間范圍分區(qū)
HASH分區(qū):基于給定的分區(qū)個數(shù),把數(shù)據(jù)分配到不同的分區(qū)
KEY分區(qū):類似HASH分區(qū)
range分區(qū)
CREATE TABLE emp(
id int not null,
ename varchar(30),
hired date not null DEFAULT '1970-01-01',
separated date NOT null DEFAULT '9999-12-21',
job varchar(30) not null,
store_id int not null
)
partition by range(store_id)(
PARTITION p0 VALUES less than (10),
PARTITION p1 VALUES less than (20),
PARTITION p2 VALUES less than (30)
);
//上述的分區(qū)方案將storid,1-9分到p0區(qū),10-19分到p1區(qū),等如果插入大于30,會出現(xiàn)錯誤,因?yàn)闆]有規(guī)則保護(hù)大于30的
INSERT into emp VALUES('2322','milk','1993-12-23','1993-12-23','click',19);//可以
//Table has no partition for value 40
INSERT into emp VALUES('2322','milk','1993-12-23','1993-12-23','click',40);
添加分區(qū)
alter table emp add partition(partition p3 values less than maxvalue);
maxvalue表示最大的可能的整數(shù)值
mysql 支持在values less than 語句中加入表達(dá)式
比如以日期作為分區(qū)
CREATE TABLE emp(
id int not null,
ename varchar(30),
hired date not null DEFAULT '1970-01-01',
separated date NOT null DEFAULT '9999-12-21',
job varchar(30) not null,
store_id int not null
)
partition by range(year(separated ))(
PARTITION p0 VALUES less than (1995),
PARTITION p1 VALUES less than (2000),
PARTITION p2 VALUES less than (2005)
);
MySQl 5.5改進(jìn)了range分區(qū)給你,通過支持非整數(shù)分區(qū),創(chuàng)建日期分區(qū)就不需要通過函數(shù)進(jìn)行轉(zhuǎn)換
partition by range(separated )(
PARTITION p0 VALUES less than ('1996-01-01'),
PARTITION p1 VALUES less than ('2001-01-01'),
PARTITION p2 VALUES less than ('2006-01-01')
);
List分區(qū)
create table expenses(
expense_date date not null,
category int,
amount decimal(10,3)
)partition by list (category)(
partition p0 values in(3,5),
partition p1 values in(1,10),
partition p2 values in(4,9),
partition p3 values in(2),
partition p4 values in(6)
)
LIST分區(qū)不存在類似于VALUES LESS THAN MAXVALUE這樣的值 在MYSQL5.5支持非整數(shù)分區(qū)
Columns分區(qū)
create table rc3(a int,b int)
parition by range columns(a,b)(
parition p01 values less than(0,10),
parition p01 values less than(10,10),
parition p01 values less than(10,20),
parition p01 values less than(maxvalue,maxvalue)
)
Hash分區(qū)
create table emp(id int not null.ename varchar(30),hired date not null default '1907-01-01',sparated date null null default '8888-12-31',job varchar(30) not null,
store_id int not null) partition by hash(store_id)partitions 4;
這里創(chuàng)建了一個常規(guī)的hash 使用 partition by hash(expr)其中expr是某列值或一個整數(shù)值的表達(dá)式返回值。 partition num 對分區(qū)類型,分區(qū)鍵,分區(qū)個數(shù)進(jìn)行定義,上述基于
store_id列hash分區(qū),表被分為4個分區(qū)
我們可以計(jì)算出它被保存在哪個分區(qū)中假設(shè),假設(shè)記錄的分區(qū)編號為N,那么N=MOD(expr,num),例如emp表中有4個分區(qū),插入一個store_id為234的 mod(234,4)=2,倍保存在第二個
分區(qū)
相關(guān)推薦:
關(guān)于mysql 基礎(chǔ)知識的總結(jié)
PHP 和 MySQL 基礎(chǔ)教程(一)
PHP 和 MySQL 基礎(chǔ)教程(二)
PHP 和 MySQL 基礎(chǔ)教程(三)
PHP 和 MySQL 基礎(chǔ)教程(四)
以上就是mysql基礎(chǔ)知識 的詳細(xì)內(nèi)容,更多請關(guān)注php中文網(wǎng)其它相關(guān)文章!
學(xué)習(xí)教程快速掌握從入門到精通的SQL知識。