MySQL读书笔记

2019/11/26 19:08:52

第一章

E-R图设计

实体之间的联系：

1. 1:1
2. 1:n
3. m:n

sql标准：SQL-92

1. 数据定义语言:创建各种数据库对象
2. 数据查询语言:查询sql
3. 数据操纵语言:增删改
4. 数据控制语言:权限控制

配置服务器MySQL server instance config wizard

选项文件my.ini，用于修改MySQL的默认设置

数据目录，用于存放数据库文件

第二章

sql_mode模式

有全局模式和会话模式两种
清除sql模式：
SET sql_mode="";

个人认为有用的几个模式介绍

1.ONLY_FULL_GROUP_BY:不要让group by 语句中的部分查询指向未选择的列。

2.PIPES_AS_CONCAT:将||视为字符串连接操作符。

3.IGNORE_SPACE:允许函数名和“（”之间有空格。强制将所有函数名视为保留字。

第三章 数据库系统规范化设计

数据库设计步骤

• 需求分析
• 概念结构设计
• 逻辑结构设计
• 数据库物理结构设计

第一范式：
所有的属性不可再分
第二范式：
非主键属性必须完全依赖于任一个关键字
第三范式：
所有非主键属性都不传递依赖于主关键字

关系模式规范化基本步骤：

1. 1NF关系进行投影，消除原关系中非主键属性对键的部分依赖，将1NF关系转换为若干个2NF关系。
2. 对2NF关系进行投影，消除原关系中的非主属性对键的传递依赖，将2NF转换成多个3NF属性。
3. 对3NF关系进行投影，消除原关系中主属性对键的部分依赖和传递依赖，也就是使决定因素都包含一个候选键。

关系运算相关知识：https://blog.csdn.net/QuinnNorris/article/details/70739094

数据完整性是指数据的正确性、完备性和一致性。

数据完整性分为三类

域完整性：一个列的输入有效性，是否允许空值。

强制域完整性的三种方法：

1. 类型限制

2. 格式（check约束）

3. 可能的范围（foreign key 约束、check约束、defaule约束、not null定义）

实体完整性：保证表中的所有行都是唯一的.

强制实体完整性的方法:索引、唯一约束、主键约束

参照完整性：保证主关键字和外部关键字的参照关系。

主键约束：
通过定义primaet key 约束来创建主键。MySQL为主键创建唯一性索引，实现数据的唯一性，查询使用主键时，该索引可以对数据进行快速访问。

两种方式定义主键：作为列或表的完整性约束。

列完整性

CREATE TABLE s1(
	xh nvarchar(255) NULL,
	xm nvarchar(255) NOT NULL PRIMARY KEY,
	csrq DATETIME 
);

表完整性

CREATE TABLE s2(
	xh nvarchar(255) NULL,
	xm nvarchar(255) NOT NULL,
	csrq DATETIME,
	PRIMARY KEY(xh,xm)
);

位图法

参考资料：https://blog.csdn.net/hacker_Lees/article/details/85069465

实体完整性

主键：PRIMARY

替代键：UNIQUE

参照完整性

外键：定义是可以指定父表删除或更新是所做的操作

CHECK约束：里面可以写条件和子查询

命名完整性约束：CONSTRAINT 名字（只能给表完整性约束创建名字）

删除完整性约束：drop table 删除表时，所有的约束都删除了

alert table 表 drop PRIMARY KEY;#删除表中的主键约束

数据库的创建于管理

创建数据库

create database if not exists 表名 
CHARACTER SET 字符集
COLLATE 字符集的校验规则

数据库的名字必须符合操作系统的文件夹命名规则。

use 数据库名；#跳转数据库

show databases;#查询所有数据库

修改数据库

alert database 

修改数据库的全局特性，这些特性存储在数据库目录中的db.opt文件中。用户必须又数据库的修改权限。

忽略数据库名称，默认时当前数据库。

删除数据库

drop database if exists db_name

第五章表的创建于管理

2019/12/10 20:01:59

创建数据表

create temporary table  if not exists tb_name

创建临时表 temporary

临时表只对创建它的用户可见，断开数据库连接时自动删除他们。

可以在create table 语句后面添加 select 语句，在一个表的基础上创建一个表。

create   table  if not exists tb_name3 SELECT * FROM ( SELECT  * FROM test LIMIT 100) a 

上面的语句会创建一个表，创建出的表和查出来的数据的注释也是有的，但是索引之类的都没了。

1. 创建出来的临时表里面是没有索引的
2. 创建出来的临时表可以使用 desc 临时表；来查看数据表的信息
3. desc 后面加select 语句可以查看索引信息
4. 创建表的名字小于64个字符，保留字使用单引号

MySQL中有空间类型数据

创建表的 ENGINE 指定表的存储引擎

更改表结构

修改表名

alert table tb1 TO newtb1;

复制表

create  Temporary table if not exists tb_name  () like   old_name   as select表达式

• like 创建一个与旧表相同结构的新表，列名，数据类型，空指定和索引也被复制，不会复制数据。

• like 后面只能是一个表，不能是select语句。

  create table if not exists tb_name4 LIKE tb_name

as 复制表的内容，但是索引和完整性约束不会复制。可以是select语句。

删除表

drop  temporary table if exists tb_name 

第六章MySQL语言结构

MySQL数据类型

数值类型

1. 整数类型：保存整个数字

   TINYINT
   SMALLINT
   MEDIUMINT
   INT
   INTEGER
   BIGINT

2. 浮点类型：保存近似数字

   FLOAT
   DOUBLE

3. 定点类型：保存精确数

   DECIMAL

4. 位类型：保存位字段类型

   BIT

   insert into an_bit values (b’11′);
   select id+0 from test;    //这是可以看到十进制的
   select bin(id+0) from test;  //这是可以看到二进制的
   select oct(id+0) from test; // 这是八进制的
   select hex(id+0) from test; //这是十六进制的

字符串数据类型

char:固定长度字符串(1~255)
varchar:可变长度字符串(0~65535)
text:可变长度字符串

结构化的字符串

enum
set

字符集和校验规则

//显示字符集
show character SET;
//显示某个字符集的排序规则
SHOW COLLATION LIKE 'utf8%'; 

二进制类型

BINARY
VARBINARY

日期和时间类型

TIME
YEAR
DATE
DATETIME
TIMESTAMP

NULL

MYSQL运算符

算数运算符

+-*/%

+和-可以对数字和日期时间值进行运算

select   '2018-01-01' INTERVAL 22 DAY

/0与%0返回null

比较运算符

==
<+

<> !=
<
<+>
>=

运算符用于比较数字和字符串。数字作为浮点值比较，字符串以不区分大小写方式进行比较（除非使用BINARY关键字）

逻辑运算符

not !
or ||
and &&
xor

SELECT !0,0||1, TRUE,1 && 0, 1 XOR 0

位运算符

在两个表达式之间执行二进制位操作，两个表达式的类型可为整型或与整型兼容的数据类型。

&	//位and
~	//位取反
|	//位or
>>  //位右移
^	//位xor
<<	//位左移

其他运算符

between

in

is null

is not null

like

常用函数

数学函数

SELECT GREATEST(1,9,6), LEAST(1,9,6)
GREATEST 获取一组数的最大值
LEAST	获取一组数中的最小值

MySQL不允许函数名和括号之间有空格。

SELECT FLOOR(1.8), CEILING(1.8);
FLOOR 获取小于一个数的最大整数值
CEILING	获取一个大于一个数的整数值
里面是整数的话是直接返回整数

SELECT ROUND(9.6),TRUNCATE(1.553,2);
ROUND 获取四舍五入的整数值
TRUNCATE 截取指定位数的小数


SELECT ABS(-78),SIGN(1),SIGN(-1),SIGN(0),SQRT(25),POW(3,3),SIN(1),COS(1),TAN(45),ASIN(1),ACOS(1),ATAN(45),BIN(2),OCT(12),HEX(80);


ABS 绝对值
SIGN正负0
SQRT平方根
POW一个数作为另一个数的指数
SIN ,COS ,TAN ,ASIN ,ACOS ,ATAN 三角函数
BIN,OCT,HEX2，8，16进制函数

聚合函数

字符串函数

SELECT 
ASCII("zhsng"),  	#返回字符最左边的ascii值
CHAR(44,55,44),	#拼接ascii为字符串
LEFT("你好世界",2),	左截取
RIGHT("计算机",2),	#右截取
TRIM("  zhsng "),		#去除空格
LTRIM(" d "),		#去除空格
RTRIM(' s s '),	
RPAD("S",6,"3"),	#填充字符
LPAD("S",6,'9'),
REPLACE("SHIJIESDFSDFDF",'S','666'), #替换字符
CONCAT('你好','世界'), 		拼接字符
SUBSTRING("社会发展迅速",2,5), 	#截取字符
STRCMP("s","S");		#比较字符大小

时间和日期函数

	SELECT
	NOW(),				#当前时间
	CURTIME(),			#当前时间
	CURDATE(),			#当前日期
	YEAR(NOW()),		#年的部分
	MONTH(NOW()),		#月的部分
   	MONTHNAME(NOW()),	#月的部分(字符串形式)
	DAYOFYEAR(NOW()),	#一年的序数
	DAYOFWEEK(NOW()),	#一周的序数
	DAYOFMONTH(NOW()),#一月的序数
	DAYNAME(NOW()),	#字符串形式返回星期名
	WEEK(NOW()),		#返回一年中的第几个星期
	YEARWEEK(NOW()),	#返回一年中的第几个星期包括年
	HOUR(NOW()),		#返回小时值
	MINUTE(NOW()),		#返回分钟
	SECOND(NOW()),		#返回秒数
	DATE_ADD(NOW(),	 
	INTERVAL 1 DAY),	#对时间进行加
	DATE_SUB(NOW(),
	INTERVAL 1 DAY)	#对时间进行减

2019/12/17 22:17:36

加密函数

SELECT AES_ENCRYPT('计算机技术','2019'), AES_DECRYPT(AES_ENCRYPT('计算机技术', 2019), 2019), CONVERT(AES_DECRYPT(AES_ENCRYPT('计算机技术', 2019), 2019) USING UTF8MB4) ;
SELECT ENCODE('张三','2019'), DECODE(ENCODE('张三','2019'),2019), CONVERT(DECODE(ENCODE('张三','2019'),2019) USING UTF8MB4);
SELECT PASSWORD('新年快乐')；

控制流函数

ifnull(,)
nullif(,)
if(,,)

格式化函数

fromat(,)#用来保留几位小数
date_format();#格式化日期
time_format();#格式化时间

类型转换函数

cast(expr as type  )#类型包括：binary ,char,date,time,datetime,signed,unsigned

系统函数

SELECT 
DATABASE(),#返回当前数据库
BENCHMARK(9, DATABASE()),#对表达式执行n次
CHARSET(''),#返回字符串的字符集
CONNECTION_ID(),#返回当前客户的连接id
FOUND_ROWS(),#返回最后一次select查询的行数
LAST_INSERT_ID(),#返回最后一个自增长值
USER(),#当前登录用户名
SYSTEM_USER(),
VERSION()#数据库版本

2019/12/17 23:26:00

第7章操作表中的数据

向表中插入数据

insert into  tb_name （） values () ;
可向表中插入一行或多行，可以给出部分列，还可以向表中插入其他表中的数据。

上面的语句可以省略into 直接写insert tb_name(验证过)

REPLACE t1
(YEAR, MONTH) VALUES ('2014', 9)

主键重复的值直接替换掉了，其实相当于删除之后插入数据，没插入的数据之前就是有，执行完之后也就成为默认值了

replace具备替换拥有唯一索引或者主键索引重复数据的能力，也就是如果使用replace into插入的数据的唯一索引或者主键索引与之前的数据有重复的情况，将会删除原先的数据，然后再进行添加。 
语法：replace into table( col1, col2, col3 ) values ( val1, val2, val3 ) 

查询结果插入表中

INSERT t1 SELECT  * FROM t1 LIMIT 1

修改表中的数据

update tb_name set col_name=expr 
where
order by
limit

修改多个表中数据

update tab_name_refernces set col_name=expr 
where 

例子：
update s1,s2 set s1.ids='',s2.names=''
where a1.id=s2.id;

删除表中的数据

delete  from tb_name
where
order by
limit  #指定删除行数的最大值

从多个表中删除行

delete tb_name from tb_name where #语法1 

delete from tb_name using tb_name where #语法2

truncate table tb_name

对于参与了索引的表和视图的表不能使用 truncate （实际测试可以删除数据，不过影响的行数是0）

show &describe

SHOW TABLES;#显示所有表
SHOW DATABASES; #显示所有数据库
SHOW COLUMNS  FROM t1 ;#显示表中列的名称 实际效果和DESC t1 相同
SHOW GRANTS  FOR  root ;#显示指定用户的权限
SHOW INDEX FROM t1;#显示指定表的索引情况
SHOW STATUS;#显示系统的资源信息
SHOW  VARIABLES;#显示系统变量的名称和值
SHOW  PROCESSLIST;#显示系统中的进程
SHOW TABLE STATUS;#显示表信息
SHOW PRIVILEGES;#显示服务器所支持的权限
SHOW CREATE DATABASE mysql ;#显示创建数据库的语句
SHOW CREATE TABLE t1;#显示创建表的语句
SHOW  EVENTS;#显示事件列表
SHOW  INNODB STATUS;#显示引擎状态（未执行成功）
SHOW WARNINGS; #显示最后一次执行语句所产生的错误
SHOW  ENGINES;#显示可用的存储引擎
SHOW PROCEDURE STATUS;#显示所有存储过程的基本信息
SHOW   CREATE PROCEDURE  ;#显示存储过程的出

desc

desc tb_name #显示表中各列的信息  相当于  show columns  from 
后面可以加一个字符串'%_' 用来匹配符合条件的列
desc （select * from ） #可以查看语句的执行情况

第8章查询表中的数据

select all distinct 
select_expr
into outfile file_name |into dumpfile file_name 
from tb_name 
where 	
group by   asc desc 
having	
order by asc desc
limit 	

将结果保存在数据库服务器上

SELECT  * FROM tb_name INTO OUTFILE 'test.sql';
SELECT  * FROM tb_name INTO DUMPFILE  'test2.sql';

where子句

AND 
OR
NOT
=
<
<=
>=
<=>
<>
!=
NOT LIKE
ESCAPE
BETWEEN AND
IS NULL
IN
ALL SOME ANY
EXIST

GROUP 子句

group by
asc desc
withrollup

having子句

可以包含聚合函数

order by

order by  asc desc

可以在后面跟一个正整数，按该位置上的列排序

order by 子句还可以包含子查询

limit

替换表中的列

case
where 条件1 then 表达式1
where 条件2 then 表达式2
else 表达式
end

<=>

可以和空值进行比较

in

in关键字最主要的作用是表达式子查询

distinct

去重，对结果集中的重复行选择一个

like regexp

group by

带rollup操作符的group 子句还包括汇总行
产生规则：按列的排序的逆序依次进行汇总

having

对汇总后的数据筛选

order by

order by 子句还可以包含子查询

对null当作最小值对待

limit

限制返回的行数

handler

#打开一个表
HANDLER tb_name  OPEN ;
#读取第一行
HANDLER tb_name READ  FIRST ;
#读取下一行
HANDLER tb_name READ  NEXT  ;
#加条件（条件不能包括子查询，系统内置函数 between like  in ）limit 用来指定获取的数量
HANDLER tb_name READ  FIRST  WHERE 1 LIMIT 20;
#关闭表
HANDLER tb_name CLOSE;

聚合函数

group_concat
all disinct

二进制位的聚合函数

bit_and
bit_or
bit_xor

连接查询

全连接

各表之间，分割

等值连接 ：全连接中加条件

join 连接

inner join #内连接
natural join #自然连接，生成笛卡尔积，去重（好像是）
CROSS JOIN#交叉连接,生成笛卡尔积没有条件
left join 
right join 

自连接：表自身连接

using :连接的列名相同可以使用

外连接查询：

left outer join#左外连接 
right outer join#右外连接
natural join#自然连接
natural left join#自然左外连接
natural righe join#自然右外连接

外连接只能对两个表进行

交叉连接：对两个表进行笛卡尔积运算

子查询

2019/12/23 0:19:25

子查询可以作为查询条件的一部分

all

使用all时需要同时满足所有内层查询的条件。

any或some

满足一个条件

上面的两个运算符跟在比较操作运算符后面。

exists

测试子查询的结果是否为空。
不为空返回true，否则false

内层子查询

内层子查询只处理一次

相关子查询

查询多次

四种子查询

表子查询

行子查询

列子查询

标量子查询

子查询可以用在select 语句的其他子句中，表子查询可以用在from子句中，但必须为子查询产生的表起个别名。

select 关键字后面也可以定义子查询。

where子句

where 子句中可以将一行数据与行子查询中的结果通过比较运算符进行比较。

 SELECT * FROM  表  WHERE (列1,列2)=(6.5,3)

in子查询

in子查询只能返回一列数据。

带比较运算符的子查询

如果子查询的结果集中只返回一行数据时，可以直接通过比较运算符直接比较。

all运算符指定表达式要与子查询结果集中的每个值都进行比较，每个值都符合时返回true.

some和any表示表达式和子查询结果中的某个值满足关系时返回true.

union连接

每个select语句具有相同的数目和类型。

只有最后一个select 语句才能使用into outfile

union自动去除重复行，要得到所有行可以指定关键字all。

第一个select语句中被使用的列名被用做结果中的列名称。

为表和字段取别名

列别名中有空格时需要使用引号将别名括起来。

不允许在where子句中使用列别名。

第9章索引

访问表中的行

1.顺序访问

2.索引访问

使用索引可以提高数据库中特定数据的查询速度

索引的存储类型

BTREE

HASH

MyISAM和InnoDB只支持BTREE索引，memory或heap存储引擎可以支持这两种。

应该还有一种算法
rtree

memsql(和MySQL兼容的内存数据库)

索引空间

索引是保存在文件中的，需要占据物理空间。

更新、删除、插入表时，MySQL会自动更新索引。

按btree形式存储的索引类型

1.普通索引 index
2.唯一索引 unique
3.主键 primary key
4.全文索引 full text

哈希索引

使用哈希索引，不需要建立树结构，所有值都保存在列表中，列表指向相关的行，当根据一个值获取一个特定的行时，哈希索引非常快。

索引设计原则

1.索引并非越多越好。

2.避免对经常更新的表进行过多的索引，对于经常查询的字段建立索引。

3.数据量小的表做好不要建立索引。

4.条件表达式中经常用到的不同值较多的列上创建索引，在不同值少的列上不要建立索引。

5.建立唯一索引，能确保列数据的完整性，提高查询速度。

6.频繁分组和排序的列上建立索引

创建索引

create index ind_name on tb_name（col_name1,col_name2）
col_name(length) asc |desc

使用列的一部分创建索引可以时索引文件大大减小，某些情况下只能对列的前缀进行索引，比如索引列的长度有一个最大上限。
索引还可以按升序或降序排序。

定义索引

1.create index

CREATE INDEX id_name1 ON testsy (NAME(2) desc)

2.alert table

alert table tb_name
add index index_name 

查看表的索引情况

show index from tab_name

创建表时创建索引

create table tb_name(
index ind_name(col_name1,col_name2)
）

删除索引

1.drop index

drop index ind_name on tb_name

2.alert table

alert table tb_name 
drop index ind_name

删除列，该列也会从索引中删除，如果所有组成索引的列删除，索引也会删除。

索引对查询的影响

执行多个表的连接查询时，索引将更有价值。

MySQL利用索引加速where子句中于条件相匹配的行的搜索。

2019/12/24 23:32:52

第10章存储程序

2019/12/29 21:19:16

存储程序分为两种：

存储过程

存储函数

存储过程在服务器端运行，执行速度快，确保数据库安全。

创建存储过程

create procedure 
创建存储过程必须有create rputine 

mysql的proc表中存储定义信息。

information_schema库中也存储了数据库信息。

我们访问mysql.proc的权限是没有保证的,但有访问information_schema视图的权限,每个用户都有隐式的对 information_schema数据库的SELECT权限.

CREATE
[DEFINER = user]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body

CREATE
[DEFINER = user]
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body

proc_parameter:
[ IN | OUT | INOUT ] param_name type

func_parameter:
param_name type

type:
Any valid MySQL data type

characteristic:
COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
Valid SQL routine statement

修改定界符

delimiter //

特征

COMMENT 'string'#注释
LANGUAGE SQL	#编写的语言
[NOT] DETERMINISTIC	#产生确定和不确定的结果
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }	#存储过程不包含读或写数据的语句，存储过程不包含sql，存储过程包含读数据的语句但不包含写数据的语句，包含读写数据的语句
SQL SECURITY { DEFINER | INVOKER } 创建者权限执行、调用者权限执行

/* SQL Error (1436): Thread stack overrun: 9856 bytes used of a 131072 byte stack, and 128000 bytes needed. Use ‘mysqld --thread_stack=#’ to specify a bigger stack. */

show variables like 'thread%';

show status like 'connections';

show status like '%thread%';

存储过程体

局部变量
declare 变量名 type default value

局部变量只能在begin end 语句中块中声明

局部变量只能在存储过程的开头就声明，在begin end 中使用。

存储过程中也可以使用用户变量，局部变量和用户变量的区别是：局部变量前没有@符号，局部变量在begin end语句块处理完之后就消失了。用户变量前面使用@符号，存在整个会话中。
如 set @name=‘zhangsan’

使用Set语句赋值

set  var_name='';

这条语句无法单独运行，只能在存储过程或存储函数中运行。

select into语句

可以把选定的列值直接存储到变量中，因此返回的结果只能有一行。

select col-name1 ,col_name2 into var_name1 ,var_name2 table_expr

流程控制语句

if、case、loop、while、iterate、leave

if

if	判断条件1 then 执行语句1
else if  判断条件2 then 执行语句2
end if;

case语句

case case_value
when when_value1 then 执行语句1
when when_value2 then 执行语句2
else 执行语句
end case;



case when 条件 then 语句1
when 条件 then 语句1
else 语句
end case;

循环语句

WHILE 条件 DO
为真语句
END WHILE 

REPEAT语句

REPEAT 
执行语句
until 判断条件
end  REPEAT 

loop语句

loop
语句
end loop

语句中存在判断条件，否的话执行 leave跳出语句。

iterate再次循环

leave 跳出循环

处理程序和条件

存储过程中处理sql语句可能导致一条错误消息。每一个错误消息都有一个唯一代码和SQLSTATE代码。

处理游标结束的条件就是用的上面的错误消息判断的。

2019/12/29 23:58:04

游标

MySQL支持简单的游标。MySQL中游标一定在存储过程或函数中使用。
不能在单独的查询中使用。

使用游标需要四条语句：

DECLARE CURSOR 	#声明游标
OPEN 	CURSOR	#打开游标
FETCH 	CURSOR	#读取游标
CLOSE 	CURSOR	#关闭游标

DECLARE 游标名 CURSOR FOR select语句

在程序中游标可以打开多次，更新表之后，每次打开的结果可能不同。

FETCH CURSOR 将游标指向一行数据赋值给一些变量。

CREATE PROCEDURE `qh`(
	OUT `s` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY DEFINER
COMMENT ''
BEGIN
declare  a int  ;
declare  b int;
declare found boolean default true;
declare yb cursor for SELECT 1 UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4 UNION
SELECT 5 UNION
SELECT 6 UNION
SELECT 7 UNION
SELECT 8 UNION
SELECT 9 
;
declare continue handler for not found set found=false;
set a=0 ;
set b=0 ;
open yb;
while found do
fetch yb  into a;
IF found
then
 set b=a+b;
END IF;
end while;
close yb;
set s=b;
select b;
END

调用存储过程

CALL `qh`(@num)
SELECT @num

存储过程可以在触发器或存储过程中使用

删除存储过程

drop  procedure  if exists sp_name

修改存储过程

alert procedure

存储函数

存储函数不能有输出参数，其本身就是输出参数。

不能使用call语句来调用存储函数。

存储函数必须有一条return语句，而这条特殊的sql语句不能包含于存储过程中。

创建存储函数

显示有哪些存储函数。

show function status;

创建存储函数

create function sp_name 
returns type 
routine_body

存储函数和存储过程不能有相同的名字

存储函数参数只有名称和类型， returns type 声明函数返回值的数据类型。

routine_body 存储体函数，函数体中必须包含 return value语句。

调用存储函数

select sp_name 

创建存储函数

CREATE FUNCTION `cchs`()
RETURNS INT
LANGUAGE SQL
NOT DETERMINISTIC
NO SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
set @a=2019;
return   @a ;
END

删除存储函数

drop function if exists sp_name

第11章视图

视图：虚拟表
表：基础表

数据库中只存储视图的定义

视图定义之后可以更新删除修改和更新。

视图的优点：

屏蔽数据库的复杂性

简化用户的权限

可以重新组织数据

创建视图

create  or replace#替换同名视图  view view_nama#视图名  column_list#为视图定义明确的列名


algorithm 会影响MySQL处理视图的方式

select 语句的限制

定义视图的用户必须对参照表和视图的权限

不能包含from 子句中的子查询

不能引用系统或用户变量

不能使用预处理语句

在定义中的引用表或视图必须存在

引用的视图名不是当前数据库，必须加上数据库名

视图中定义orderby 对特定视图进行选择会忽略

对select 语句中的其他选项或子句，使用效果未定定义

MySQL 官方将 prepare、execute、deallocate 统称为 PREPARE STATEMENT。翻译也就习惯的称其为预处理语句。

PREPARE stmt from '你的sql语句'; //定义预处理语句
EXECUTE stmt (如果sql有参数的话, USING xxx,xxx);  //执行预处理语句
DEALLOCATE PREPARE stmt;//释放资源

可以通过 max_prepared_stmt_count 变量来控制全局最大的存储的预处理语句。

注意事项

不能把规则、默认值、触发器和视图关联

不能在视图上创建索引

对单表创建视图

CREATE VIEW vn(NAME)
AS SELECT 1;

可以在不是from的语句中使用子查询

查看所有视图

show tables from dn_name

注意：关联的表中添加字段，视图不包含新字段，关联的表或视图删除，视图将不能够使用。

上面实验过：新增字段就算是使用* 新增的字段不包含在*中

删除视图关联表之后
/* SQL Error (1356): View ‘te1.v112’ references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them */

视图的算法

1：merge:合并   
2: temptable 临时表
3：undefined 未定义 由mysql决定用 merge还是 temptable,默认 merge

将表设置为temptable时，视图不可以对其进行操作。

视图 检查选项子句

with check option子句会在更新数据时检查数据是否符合视图定义的where子句条件。 

none
local
cascaded  #默认

local cascaded 需要有表和视图才能创建

local向视图中插入不符合视图的数据会失败

cascaded插入不符合视图的数据会插入基础表中但是不会在视图中显示

插入数据

当视图所依赖的额基本表有多个时，不能向视图中插入数据。

insert 语句还有一个限制：select 语句中必须包含from 子句中指定表的所有不能为空的列。

更新数据

若一个视图依赖于多个基本表，则一次修改该视图只能改动一个基本表的数据。

删除数据

delete 删除数据

对于依赖多个基本表的视图，不能使用delete语句。

修改视图

alert view

删除视图

drop view if exists view_name

可以使用上面的语句删除多个视图。

2019/12/30 23:06:51

第12章 触发器

创建触发器

create  trigger 名称 触发时刻 触发事件

触发器不能返回任何结果到客户端。

在MySQL触发器中的sql语句可以关联表中的任意列。但是不能直接使用列的名称去标识，new.列名 old.列名

对于install语句只有new是合法的

delete中只有old是合法的。

update语句中可以同时使用new和old.

触发器中涉及对触发器自身表更新操作时只能使用before。

触发器中还能调用存储过程。

查看触发器

show triggers  #查看所有触发器

SELECT  * FROM  information_schema.TRIGGERS

删除触发器

drop trigger 触发器名称

事件

一个事件可以只调用一次，也可以周期性启动。

事件和触发器相似，事件时根据调度事件来启动的，事件也被称作时临时性触发器。

事件的主要作用

关闭账户

打开或关闭数据库指示器

使数据库的数据在某个时间间隔后刷新

执行对数据库的复杂的检查工作

创建事件

 create event

创建事件可以指定执行之后删除或保留，执行一次或多次，开始时间和结束时间，时间间隔。

MySQL事件调度器负责调用事件，这个模块是数据库服务的一部分。要创建事件必须打开调度器。可以使用系统变量， SET global event_scheduler =true

事件可以指定是活动的关闭的等。

修改事件

alert event

删除事件

drop  event

2020/1/5 21:20:33

第13章 MySQL用户安全管理

MySQL的用户信息存储在MySQL数据库中，服务器启动后将这些数据表中的数据读取到内存中。

mysql 参数

-e 登录之后执行-e后面的命令或sql语句并退出。

参数和参数后面的字符串之间不能有空格。

添加用户

create  user

查看用户

select * from msyql.user;

删除用户

drop user 

修改用户名

rename	 user old_name to new_name

修改密码

set password for username=pswwword('');

不加for user表示修改当前用户

授予权限

列权限

表权限

数据库权限

用户权限

grant 

查看权限

 show grants  for 用户名

用户权限转移和限制

with

回收权限

revoke

表维护语句

ANALYZE TABLE#更新索引列的可压缩性

CHECK TABLE#检查一个表或多个表是否有错误

CHECKSUM TABLE #对一个表计算检验和

OPTIMIZE TABLE #整理碎片文件

REPAIR TABLE#修复表和索引

第14章 数据的备份与恢复

数据库备份

1. 导出数据或表文件
2. 二进制日志文件
3. 主从复制

使用sql语句备份

select into  outfile

使用mysqldump备份数据

mysqldump

mysqlimport恢复数据

直接复制文件

myisam存储引擎的数据表文件可以直接复制替换

使用二进制文件

启用二进制文件：修改配置文件，增加[mysqld]log-bin

使用mysqlbinlog可以查看和恢复数据

第15章 事务与锁

acid

原子性

一致性

隔离性

持久性

并发事务带来的问题

更新丢失

脏读

不可重复读

幻读

四种隔离级别

可序列化

可重复读（MySQL默认）

已提交读

未提交读

设置隔离级别

set transaction#设置隔离级别，可使用关键字global适用于所有用户，session，只使用当前用户和连接

select @@TX_ISOLATION#查看当前隔离级别

开启事务

set @@autocommit=0;

开始事务

START TRANSACTION

BENGIN WORK

提交事务

commit  

撤销事务

rollback

####回滚事务 ####

savepoint#保存回滚点

锁

三种锁

表级锁

页面锁

行级锁

死锁

myisam表级锁

show status like'table%'#查看表锁定情况

MySQL表级锁的两种模式

表共享读锁

表独占写锁

InnoDB行锁

InnoDB和myisam的最大区别是支持行级锁和事务

show status like 'innodb_row_lock%'#查看系统的行锁争夺情况

共享锁

排他锁

意向共享锁

意向排他锁

MySQLINNODB加行锁是通过给索引上的索引项加锁来实现的。

2020/1/5 23:18:05