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:这里是执行计划中估算的扫描行数，不是精确值