mysql基础小测试三_数据库---MySQL(三)
一、視圖
視圖是一個虛擬表,是sql的查詢結果,其內容由查詢定義。同真實的表一樣,視圖包含一系列帶有名稱的列和行數據,在使用視圖時動態生成。視圖的數據變化會影響到基表,基表的數據變化也會影響到視圖[insert update delete ]
--創建視圖
create view view_name as select * fromtable_name;--顯示創建視圖
show create viewview_name;--顯示視圖的字段
descview_name;--查詢視圖數據
select * fromview_name;--顯示視圖
show tables;--修改視圖
alter view view_name as select * fromtable_name;--刪除視圖
drop view view_name;
二、觸發器
觸發器是與表有關的數據庫對象,在滿足定義條件時觸發,并執行觸發器中定義的語句集合。觸發器的這種特性可以協助應用在數據庫端確保數據的完整性。
--創建觸發器
create trigger trigger_name trigger_time trigger_event on table_name foreach rowbegin執行語句1;
執行語句2;end
--刪除觸發器
drop trigger trigger_name;
trigger_time:觸發時機,before/after,在trigger_event之前/之后觸發
trigger_event:觸發事件,insert/update/delete,在插入/修改/刪除時觸發
for each row:每一次滿足觸發器的操作
new/old:new指新數據,insert/update會產生新數據;old指老數據,update/delete會產生老數據
三、函數
--調用函數
select 函數名(參數);
1.常用內置函數
函數名稱
介紹
示例
char_length(str)
返回字符串的長度,單位是字符
select char_length('abcde');返回5? ? select char_length('中文');返回2
length(str)
返回字符串的長度,單位是字節
select length('abcde');返回5? ? select length('中文');返回4
concat(str1,str2,...)
字符串拼接,返回拼接之后的字符串;若有任何一個參數為null,則返回結果為null
select concat_ws('a','','b',null,'c');返回null
congcat_ws(separator,str1,str2,...)
字符串拼接,自定義連接符,返回拼接之后的字符串;不會忽略任何的空字符串,但是會忽略null
select concat_ws(',','a','','b',null,'c');返回a,,b,c
ascii(str)
返回第一個字符的ascii碼;如果str是空字符串,返回0。如果str是NULL,返回NULL
select ascii('ab');返回97
lower(str)/upper(str)
返回字符串的小寫/大寫
instr(str,substr)
返回子串substr在字符串str中第一次出現的位置;如果字符串中不包含子串,返回0
select instr('foobarbar','bar');返回4? ?select instr('foobarbar','br');返回0
left(str,len)/right(str,len)
返回字符串左邊/右邊len長度的字符串
select left('qwerty',2);返回qw
substring(str,pos,len)
返回從pos位置開始的長度為len的字符串;不寫len,返回pos位置開始到結尾的字符串
select substring('abcdefg',2,4);?返回bcde
trim(str)/ltrim(str)/rtrim(str)
返回刪除str首尾/首/尾空格的字符串
replace(str,from_str,to_str)
返回字符串str,其字符串from_str的所有出現由字符串to_str代替
select replace('www.mysql.com','w','ab');返回ababab.mysql.com
repeat(str,count)
返回由重復count次的字符串str組成的一個字符串。如果count <= 0,返回一個空字符串。如果str或count是NULL,返回NULL
reverse(str)
返回顛倒字符順序的字符串str
insert(str,pos,len,newstr)
返回字符串str,在位置pos起始的子串且len個字符長的子串由字符串newstr代替
select insert('abcdefg',3,2,'ooo');返回aboooefg
abs(x)
返回x的絕對值
mod(m,n)/%:
返回m被n除的余數
select mod(5,2);返回1??select 5%4;返回1
ceiling(x)
返回不小于x的最小整數值
select ceiling(-2.33);返回-2
round(x)
返回參數x的四舍五入的一個整數
select format(12345678.11,4); 返回12,345,678.1100
format(x,d)
將x保留小數位d位
now()
返回現在的日期和時間
select now();返回2020-12-15 22:54:12
curdate()
返回現在的日期
select curdate(); 返回2020-12-15
curtime()
返回現在的時間
select curtime();返回22:54:28
year(date)/month(date)/day(date)
返回日期的年/月/日
select year(curdate());返回2020
weekday(date)
返回date的星期索引(0=星期一,1=星期二, ……6= 星期天)
hour(time)/minute(time)/second(time)
返回時間的時/分/秒
select hour('18:18:18');返回18
dayofweek(date)/dayofmonth(date)/dayofyear(date)
返回日期date的星期索引(1=星期天,2=星期一, …7=星期六)/返回date的月份中的日期,在1到31范圍內/返回date在一年中的日數, 在1到366范圍內
date_format(date,format)
返回格式化時間
select date_format(now(),'%Y-%m %H:%i');返回2020-12 17:14
if(expr1,expr2,expr3)
如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL),則返回值為expr2; 否則返回值則為 expr3。IF() 的返回值為數字值或字符串值,具體情況視其所在語境而定
ifnull(value1,value2)
如果value1為空,返回value2,否則返回value1
last_insert_id()
返回最后生成的AUTO_INCREMENT值
strcmp(str1,str2)
如果字符串相同,STRCMP()返回0,如果第一參數根據當前的排序次序小于第二個,返回-1,否則返回1
charset(str)
函數返回字符串str的字符集,一般情況這個字符集就是系統的默認字符集
select charset('abc');返回gbk
collatiion(str)
返回字符串str的字符排列方式
select collation('abc');返回gbk_chinese_ci
version();
返回MySQL的版本
connection_id()
返回服務器的連接數,也就是到現在為止MySQL服務的連接次數
database()/schema()
返回當前數據庫名
user()/current_user()/system_user()/session_user()
返回當前用戶名
2.自定義函數
--自定義函數,自定義函數的函數體內不能包括select語句之類
create function 函數名(參數名 參數類型) --多個參數之間用,隔開
returns返回類型begin函數體end
--刪除函數
drop function 函數名;
log_bin_trust_function_creators:當二進制日志啟用后,這個變量就會啟用。它控制是否可以信任存儲函數創建者,不會創建寫入二進制日志引起不安全事件的存儲函數。如果設置為0(默認值),用戶不得創建或修改存儲函數,除非它們具有除CREATE ROUTINE或ALTER ROUTINE特權之外的SUPER權限。 設置為0還強制使用DETERMINISTIC特性或READS SQL DATA或NO SQL特性聲明函數的限制。 如果變量設置為1,MySQL不會對創建存儲函數實施這些限制。 此變量也適用于觸發器的創建。
四、變量
@變量名:是用戶變量
@@變量名:是會話變量或全局變量
--設置用戶變量
set @變量名 = values;select @變量名 := values;--查詢變量
show session variables; --查詢所有會話變量
show global variables; --查詢所有全局變量
show variables like '%部分變量名%'; --查詢變量
select @變量名; --查詢用戶變量;
select @@session.變量名; --查詢會話變量
select @@global.變量名; --查詢全局變量--設置變量
set session 變量名=value; --設置會話變量
set @@session.變量名=value; --設置會話變量
set global 變量名=value; --設置全局變量
set @@global.變量名=value; --設置全局變量
五、存儲過程
存儲過程是一個SQL語句集合,當主動去調用存儲過程時,其中內部的SQL語句會按照邏輯執行。
--創建存儲過程
createprocedure 存儲過程名稱()beginSQL語句;end
--創建有參數的存儲過程
createprocedure 存儲過程名稱(in參數名稱 參數類型,
inout 參數名稱 參數類型,
out 參數名稱 參數類型)beginSQL語句;end
--調用存儲過程
call 存儲過程名稱(參數);--刪除存儲過程
drop procedure 存儲過程名稱;
1.無參數的存儲過程
2.有參數的存儲過程
3.存儲過程條件語句
if(條件)then...;
elseif(條件)then...;else...;end if;
4.存儲過程循環語句
4.1while循環
while(條件) do
...;end while;
4.2 repeat循環
repeat
...;
until 條件end repeat;
4.3 loop循環
loopname:loop
...;if(條件) thenleave loopname;end if;end loop;
5.存儲過程使用游標
游標是保存查詢結果的臨時區域
delimiter ||
create procedurep13()begin
declare sid int;declare sname char(10);declare cid int;declare flag int defaulttrue;--cursor和handler變量必須在普通變量的后面
declare cur cursor for select * fromstudent;--游標變量保存了查詢的臨時結果,就是結果集
--將游標變量中的結果集都遍歷一遍,到達結尾,將flag設為false
declare continue handler for not found set flag=false;open cur; --打開游標
fetch cur into sid,sname,cid; --游標向前走一步,將結果放到變量中
while(flag) do --游標還沒到達結尾就繼續循環
begin
insert into t1 values(sid,sname,cid);fetch cur into sid,sname,cid; --每次循環游標向前走一步,當結尾continue為not found設置flag為false,結束循環
end;end while;close cur; --關閉游標
end||
6.存儲過程中的事務
6.1 事務
事務的特征
在 MySQL 中只有使用了 Innodb 數據庫引擎的數據庫或表才支持事務。
事務處理可以用來維護數據庫的完整性,保證成批的 SQL 語句要么全部執行,要么全部不執行。
事務用來管理 insert,update,delete 語句
一般來說,事務是必須滿足4個條件(ACID)::原子性(Atomicity,或稱不可分割性)、一致性(Consistency)、隔離性(Isolation,又稱獨立性)、持久性(Durability)。
--開啟事務
start transaction;
SQL語句;--提交
commit;--回滾
rollback;
6.2?存儲過程中的事務
delimiter ||
create procedure p15(out p_return_code tinyint)begin
declare exit handler for sqlexception --不能加;會報語法錯誤
begin
--error
set p_return_code =1;rollback;end;declare exit handler forsqlwarningbegin
--warning
set p_return_code=2;rollback;end;
starttransaction;delete fromt1;delete from t100; --沒有t100這個表
commit;--sucess
set p_return_code=0;end||delimiter ;
7.動態執行存儲過程
8.python執行存儲過程
importpymysql
connect= pymysql.connect("localhost","root","","db1")
cursor=connect.cursor()#cursor.callproc("p1") #執行沒有參數的存儲過程
cursor.callproc("p7", args=(1,1,1)) #執行有參數的存儲過程
cursor.execute("select @_p7_0,@_p7_1,@_p7_2") #獲取存儲過程的第0,1,2個參數,返回元組里套元組
result =cursor.fetchall()
cursor.close()
connect.close()print(result)
六、索引
索引,是數據庫中專門用于幫助用戶快速查詢數據的一種數據結構。類似于字典中的目錄,查找字典內容時可以根據目錄查找到數據的存放位置,然后直接獲取即可。
索引大大提高了查詢速度,但同時會降低更新表的速度,如對表進行INSERT、UPDATE和DELETE。因為更新表時,MySQL不僅要保存數據,還要保存一下索引文件。建立索引會占用磁盤空間的索引文件。
索引分為普通索引、唯一索引、主鍵索引、唯一索引
1.普通索引
普通索引的作用,就是加速查找
--創建普通索引--創建表時,創建索引
create tabletablename(
field type ...,
...,indexindexname(field)
);--表已經存在創建索引--注意:對于創建索引時如果是BLOB?和?TEXT?類型,必須指定length。
create index indexname ontablename(field([length]))--修改表結構增加索引alter table tablename add indexindexname(field);--刪除索引drop index indexname ontablename;
alter table tablename drop index indexname;
--顯示索引
show index from table_name;
python造數據
import pymysql
import random
connect=pymysql.connect("localhost","root","","db1")cursor = connect.cursor()
#如果表已經存在,刪除
sql_d= "drop table if existsstaff;"cursor.execute(sql_d)
connect.commit()
#創建staff表
sql_c= """create tablestaff(
idint unsigned not null auto_increment primary key,
namechar(10) not null,
emailchar(20),
department_idtinyintunsigned
)engine=innodb default charset=utf8;"""cursor.execute(sql_c)
connect.commit()
#插入數據
sql_i= "insert into staff(id,name,email,department_id) values(%s,%s,%s,%s);"for i in range(1,1000000): #因為id是unsigned,不能為0
id=i
name= "Lucy" + str(i)
email= name +"@qq.com"
department_id= random.randint(1,9)cursor.execute(sql_i,(id,name,email,department_id))
connect.commit()cursor.close()
connect.close()
2.唯一索引
--創建唯一索引--創建表時創建唯一索引
create tabletablename(
...,uniqueuniquename (field)
);--表已存在時創建唯一索引
create unique index indexname ontablename(field);--刪除唯一索引
drop index indexname on tablename;
3.組合索引
組合索引是將n個列組合成一個索引,其應用場景為:頻繁的同時使用n列來進行查詢
--創建組合索引
create index indexname on table(field1,field2);
如創建name和email的組合索引之后,查詢:
name and email ?-- 使用索引
name ? ? ? ? ? ? ? ? -- 使用索引
email ? ? ? ? ? ? ? ? -- 不使用索引
注意:對于同時搜索n個條件時,組合索引的性能好于多個單一索引合并。
4.命中索引
正確使用索引才能加快查詢速度,以下方式會降低查詢速度
使用like '%...%'
使用函數
使用or ,當or中有未設置索引的列會降低查詢速度
類型不一致,如果name的類型是char,但是查詢時寫name=999,會降低查詢速度
!=,如果是主鍵還是會走索引
>,如果是主鍵或者索引是整數類型還是會走索引
order by,當排序使用索引,但是映射不是索引時,會降低查詢速度,如果是主鍵還是走索引
5.執行計劃
可通過執行計劃推測查詢語句的時間
explain SQL語句;
select_type列常見的有:
simple:表示不需要union操作或者不包含子查詢的簡單select查詢。有連接查詢時,外層的查詢為simple,且只有一個
primary:一個需要union操作或者含有子查詢的select,位于最外層的單位查詢的select_type即為primary。且只有一個
union:union連接的兩個select查詢,第一個查詢是dervied派生表,除了第一個表外,第二個以后的表select_type都是union
dependent union:與union一樣,出現在union 或union all語句中,但是這個查詢要受到外部查詢的影響
union result:包含union的結果集,在union和union all語句中,因為它不需要參與查詢,所以id字段為null
subquery:除了from字句中包含的子查詢外,其他地方出現的子查詢都可能是subquery
dependent subquery:與dependent union類似,表示這個subquery的查詢要受到外部表查詢的影響
derived:from字句中出現的子查詢,也叫做派生表,其他數據庫中可能叫做內聯視圖或嵌套select
table:顯示的查詢表名,如果查詢使用了別名,那么這里顯示的是別名,如果不涉及對數據表的操作,那么這顯示為null,如果顯示為尖括號括起來的就表示這個是臨時表,后邊的N就是執行計劃中的id,表示結果來自于這個查詢產生。
type:依次從好到差:system,const,eq_ref,ref,fulltext,ref_or_null,unique_subquery,index_subquery,range,index_merge,index,ALL,除了all之外,其他的type都可以使用到索引,除了index_merge之外,其他的type只可以用到一個索引
system:表中只有一行數據或者是空表,且只能用于myisam和memory表。如果是Innodb引擎表,type列在這個情況通常都是all或者index
const:使用唯一索引或者主鍵,返回記錄一定是1行記錄的等值where條件時,通常type是const。其他數據庫也叫做唯一索引掃描
eq_ref:出現在要連接過個表的查詢計劃中,驅動表只返回一行數據,且這行數據是第二個表的主鍵或者唯一索引,且必須為not null,唯一索引和主鍵是多列時,只有所有的列都用作比較時才會出現eq_ref
ref:不像eq_ref那樣要求連接順序,也沒有主鍵和唯一索引的要求,只要使用相等條件檢索時就可能出現,常見與輔助索引的等值查找。或者多列主鍵、唯一索引中,使用第一個列之外的列作為等值查找也會出現,總之,返回數據不唯一的等值查找就可能出現。
fulltext:全文索引檢索,要注意,全文索引的優先級很高,若全文索引和普通索引同時存在時,mysql不管代價,優先選擇使用全文索引
ref_or_null:與ref方法類似,只是增加了null值的比較。實際用的不多。
unique_subquery:用于where中的in形式子查詢,子查詢返回不重復值唯一值
index_subquery:用于in形式子查詢使用到了輔助索引或者in常數列表,子查詢可能返回重復值,可以使用索引將子查詢去重。
range:索引范圍掃描,常見于使用>,
index_merge:表示查詢使用了兩個以上的索引,最后取交集或者并集,常見and ,or的條件使用了不同的索引
index:索引全表掃描
all:全表掃描數據文件
possible_keys:查詢可能使用到的索引都會在這里列出來
key:查詢真正使用到的索引
key_len:用于處理查詢的索引長度
ref:如果是使用的常數等值查詢,這里會顯示const,如果是連接查詢,被驅動表的執行計劃這里會顯示驅動表的關聯字段,如果是條件使用了表達式或者函數,或者條件列發生了內部隱式轉換,這里可能顯示為func
rows:這里是執行計劃中估算的掃描行數,不是精確值
總結
以上是生活随笔為你收集整理的mysql基础小测试三_数据库---MySQL(三)的全部內容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: mysql查询大于等于效率_mysql
- 下一篇: 争对让望对思野葛对山栀注解_笠翁对韵.支