Mysql笔记

最新推荐文章于 2024-08-07 23:56:32 发布

竹外疏花

最新推荐文章于 2024-08-07 23:56:32 发布

阅读量268

点赞数 2

分类专栏： mysq; 数据库笔记文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/penanut/article/details/115486484

版权

笔记同时被 3 个专栏收录

8 篇文章 0 订阅

订阅专栏

mysq;

2 篇文章 0 订阅

订阅专栏

数据库

2 篇文章 0 订阅

订阅专栏

根据黑马程序员的课程资料整理所得，仅用于学习使用，如有侵权，请联系删除。

MySQL

关系型数据库

如何使用终端操作数据库

如何开启/关闭数据库服务

```
net start mysql
net stop mysql
```

注释

单行注释：-- 注释内容 或 # 注释内容（MySQL特有）
多行注释：/* 注释 */

如何登陆数据库服务器

mysql -uroot -p0209
mysql -h127.0.0.1 -p0209
mysql --host=127.0.0.1 --user=root --password=0209

如何退出数据库服务器

```
exit;
quit;
```

如何查询数据库服务器中所有的数据库

```
show  databases；
```

如何查询创建的数据库的编码格式（使用的字符集）

```
show create database lihewen; 
```

如何修改数据库的字符集

alter database lihewen character set 字符集名称

如何在数据库服务器中创建数据库

create database lihewen; //数据库名称
create database if not exists lihewen; //如果存在即不创建，不存在即创建
create database lihewen character set gbk; //以指定字符集创建数据库

如何在数据库服务器中删除数据库

drop database lihewen;
drop database if exists lihewen; //如果存在即删除

查询当前正在使用的数据库名称

```
select database();
```

如何选中某一个数据库进行操作

选中数据库
- ```
use lihewen
```
查看选中的数据库中都有哪些表
- ```
show tables;  
```

查询选中数据库中的表的所有内容

select * from pet ;
select * from where 如：id = 1； //可以给查询的内容附加条件

如何创建一个数据表

    create table pet (

	name varchar(20),

 	owner varchar(20),

	species varchar(20),

	sex char(1),

	birth date,

	death date);

如何删除表

drop table 表名;
drop table if exists 表名;

truncate table 表名; --删除表，然后创建一个一模一样的表

如何复制表

```
create table stu like student;
```

查看具体数据表的结构

```
describe pet;
```

如何往数据表中添加数据记录

insert into pet(id, name, ....)
values('puffball',  'Diane', 'hamster', 'f', '1999-03-30', NULL);

在添加时间使用系统默认时间时，数据类型应该设置为
timestamp default current_timestamp

如何删除数据

delete  from pet where name = '来福';
如果不加条件则删除所有的数据

如何修改表名

alter table 表名 rename to 新的表名;

修改表的字符集

```
alter table 表名 character set utf8;
```

添加一列

alter table 表名 add 列名 数据类型;

修改列名称类型

alter table 表名 change 旧的列名 新的列名 数据类型; //既改名字，也改类型
alter table 表名 modify 列名 新的数据类型; //只修改数据类型

删除列

```
alter table 表名 drop 列名;
```

如何修改数据

update pet set name='去福' where owner='豆豆';

查询时的去重处理

select distinct address from class; //去除重复的address
select distinct name, address from class; //当name和address都相同时才去重

模糊查询

LIKE
占位符：
- _:单个任意符
- %:多个任意符

select * from class where name like '马%';

条件查询

where子句后跟条件
运算符
	>、<、<=、>=、=、<>
	between...and
	in(集合)
	like
	is null
	and 或 &&
	or 或 ||
	not 或 !

排序查询

语法 ： order by 子句
	order by 排序字段1 排序方式1, 排序字段2 排序方式2
	
	排序方式：
		ASC ： 升序，默认的
			select * from class order by math asc;
		DESC ：降序
			select * from class order by math desc;
     如果有多个排序条件，则当前面的条件值一样时，才会判断第二个条件

聚合函数

将一列数据作为一个整体，进行纵向计算
在组合函数后面可以跟as字符串，实现起别名的作用
- ```
select count(id) as 人数 from class;
```
注意：聚合函数的计算，会排除null值
- 解决方案
  - 选择不包含空的列
    - 主键
      - select count(ifnull(english,0)) from class;
    - count(*)
  - IFNULL函数
    - ```
    select count(ifnull(english,0)) from class;
```

count : 计算个数
	select count(name) from class;
max : 计算最大值
	select max(math) from class;
min : 计算最小值
	select min(math) from class;
sum : 计算和
	select sum(math) from class;
avg : 计算平均值
	select avg(math) from class;

分组查询

语法 ： group by 分组字段
注意 ：
	分组之后，查询的字段：分组字段，聚合函数
	select sex, count(id), avg(math) from class group by sex;
	在分组之前进行判断	where
	select sex, count(id), avg(math) from class where math > 70 group by sex;
	在分组之后进行判断	having
	select sex, count(id), avg(math) from class where math > 70 group by sex having count(id) > 2;

分页查询

公式：开始的索引 = （当前的页码 - 1） * 每页显示的条数

每页显示三条记录
从0开始查3条记录
select * from class limit 0,3; //第一页
select * from class limit 3,3; //第二页

**公式：开始的索引 = （当前的页码 - 1） * 每页显示的条数**

MySQL建表约束条件

对表中的数据进行限定，保证数据的正确性、有效性和完整性
分类：
- 主键约束 primary key
- 非空约束 not null
- 唯一约束 unique(替代键约束)
- 外键约束 foreign key(参照完整性约束)
- 检查约束 check (check完整性约束)
- 命名完整性约束 constraint

主键约束

非空且唯一
一张表只能有一个字段为主键
主键就是表中的唯一标识
他能够唯一确定一张表中的一条记录，也就是我们通过给某个字段添加约束，就可以使得该字段不重复且不为空

create table user (id int primary key, 
                   name varchar(20));
                   
alter table stu modify id primary key;

删除主键
- ```
alter table stu drop primary key;
```

联合主键

只要联合的主键值加起来不重复就可以

create table user2(id int, 
                   name varchar(20), 
                   password varchar(20), 
                   primary key (id, name));

自增约束

auto_increment
一般配合主键一起使用
添加时，默认为null
数量会自动逐一增加

create table user3(id int primary key auto_increment,
                   name varchar(20)
                  );

外键约束

涉及到两个表
- 父表(主表)
- 子表(副标)

create table classes(
   	id int primary key, 
    	name varchar(20)
);

create table students(
    	id int primary key, 
    	name varchar(20), 
    	class_id int, 
    	foreign key(class_id) references classes(id)
);

//alter table student constraint clss_stu_fk foreign key(class_id) references classes(id)
//alter table student drop foreign key class_stu_fk;

insert into classes values (1,'一班');
insert into classes values (2,'二班');
insert into classes values (3,'三班');
insert into classes values (4,'四班');

insert into students values(1001,'张三',1);
insert into students values(1002,'张三',2);
 insert into students values(1003,'张三',3);
 insert into students values(1004,'张三',4);

结论
- 主表 (父表)classes 中没有的数据值，在副表(子表)中，是不可以使用的
- 主表中的记录被副表引用时，是不可以被删除的

设置级联更新，级联删除

alter table studnet add constraint class_stu_fk foreign key (class_id) references clsses(id) on update cascade on delete cascade;

唯一约束
- 约束修饰的字段的值不可以重复
- unique(id, name) 表示两个键在一起不重复就行
- ```
create table user5(id int,
					name varchar(20)
);
alter table user5 add unique(name);
```
- 删除唯一约束
  - ```
  alter table user7 drop index name;
```
- 添加唯一约束（modify）
  - ```
  alter table user7 modify name varchar(20) unique;
```
- 总结
  - 添加约束
    - 可以在建表的时候就添加约束
    - 可以使用alter…add …
    - 可以收用alter…modify…
  - 删除
    - alter…drop…

非空约束

修饰的字段不能为空 NULL
创建表时添加约束

create table user9 (
    id int,
	name varchar(20) not null
);

创建表结束后添加约束

alter table Tea modify name varchar(30) not null;

删除非空约束

alter table Tea modify name varchar(30);

默认约束
- 当我们插入字段值的时候，如果没有传值，就会使用默认值
- 传了值就不会使用默认值了
- ```
create table user10(
	id int,
    name varchar(20),
    age int default 10
);
```

检查约束

当我们在更新表数据的时候，MySQL会检查更新后的数据行是否满足check的条件
语法格式
- ```
check (条件)
```

代码演示

create table student(
	id int not null,
    birthday date not null check(
        birthday > '1990-01-01'
    )
);

create table student2(
	id int not null,
    gender char(1) not not null check(
        gender in (
    	select gender from student
    	)
    )
);

命名完整性约束
- 如果一条insert 、update或delete语句违反了完整性约束，则MySQL返回一条出错消息并且拒绝更新，一个更新可能会导致多个完整性约束的违反，这种情况下，应用程序获取几天出错的消息，为了确切的表示出是违反了哪一个完整性约束，可以为每个完整性约束分配一个名字，随后出错消息包含这个名字，从而使得消息对于应用程序更有意义
- constraint关键字用来指定完整性约束的名字
- 语法格式
  - ```
  constraint [字符串]
```
- 其中，字符串是完整性名字，在完整性约束的前面被定义，在数据库利这个名字必须是唯一的，如果他没有被给出，则MySQL会自动创建这个名字，只能给表完整性约束指定名字，而无法给列完整性约束指定名字
- 代码演示
  - ```
  create table xs1(
  	id varchar(6) null,
      name varchar not null,
      constraint primary_key_xs1 primary key(name)
  )
```

修改表结构

添加主键

create table user4(
	id int,
    name varchar(20),
);
alter table user4 add primary key(id);

删除主键
- ```
alter table user4 drop primary key;
```

使用modify 修改字段，添加约束

alter table user4 modify id int primary key;

多表之间的关系

一对多（多对一）
- 如部门和员工
- 实现方式：
  - 在多的一方建立外键，指一的一方的主键
多对多
- 如学生和课程
- 多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键
一对一
- 一对一关系实现，可以在任意一方添加唯一外键指向另一方的主键

MySQL 数据类型

日期选择按照格式
数值和字符串按照大小
主要分为三类
- 数值
- 日期
- 字符串（字符）类型

数据库的三大设计范式

第一范式

1NF
数据表中的所有字段都是不可分割的原子值
字段值还可以继续拆分的，就不满足第一范式

create table student2(
	name varchar(20),
    age int,
    country varchar(20),
    province varchar(20),
    city varchar(20),
    details varchar(20)
);

 insert into student2 values ('杜金亮', 20, '中国', 'XX省', 'XXX市', 'XXX区');

±-------±-----±--------±---------±-----±--------+
| name | age | country | province | city | details |
±-------±-----±--------±---------±-----±--------+
| 杜金亮 | 20 | 中国 | XX省 | XX市 | XX区 |
±-------±-----±--------±---------±-----±--------+
1 row in set (0.00 sec)
```

总结
- 列不可拆分

第二范式

2NF
必须是满足第一范式的前提下，第二范式要求，除主键外的每一列都必须完全依赖于主键
如果要出现不完全依赖，只可能发生在联合主键的情况下

create table myorder(
	product_id int,
    customer_id int,
    product_name varchar(20),
    customer_nmae varchar(20),
    primary key(product_id, customer_id);
);

//除主键以外的其他列，只依赖于主键的部分字段
//不符合第二范式，所以拆表

create table myorder(
	order_id int primary key,
    product_id int,
    customer_id int
);

create table product(
	id int primary key,
    name varchar(20)
);

create table customer(
	id int primary key,
    name varchar(20)
);

总结
- 一个主键对应明确的一列信息

第三范式
- 3NF
- 必须满足第二范式，除开主键列的其他列之间不能有传递依赖关系

练习

查询表内特定元素
- ```
select sname,ssex,class from student;
```
排除重复
- ```
select distinct depart from teacher;
```

查询区间

select * from score where degree between 60 and 80;

 select * from score where degree > 60 and degree < 80;

查询特定值的元素(in)

select * from score where degree in(76,88,68);

查询特定值的元素(or)

select * from student where class='95031' or ssex = '女';

升序，降序

select * from student order by class; //默认升序

select * from student order by class asc; //升序

select * from student order by class desc; //降序

查询一个表中的数量

select count(*) from student where class = '95031';

查询分数最高的学号和课程号

select sno,cno from score where degree =(select max(degree) from score);

limit

第一个数字表示从多少开始
都二个数字表示查询多少条

select sno,cno,degree from score order by degree desc limit 0,1;

+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 107 | 3-105 |     92 |
+-----+-------+--------+
1 row in set (0.00 sec)

查询平均成绩(avg)

select avg(degree) from score where cno='3-105';

查询每门课的平均成绩(group by 分组)

select cno,avg(degree) from score group by cno;

查询score表中至少有两名学生选修的并以3开头的课程的平均分数

select cno,avg(degree),count(*) from score group by cno having count(cno)>=2 and cno like '3%';

多表联查

select sname,cno,degree from student,score where student.sno=score.sno;

year()
- 截取年份
month()
- 截取月份
union
- 求并集
- 可以把两个查询语句合并
别名
- as

数据库的备份和还原

命令行语法

备份
mysqldump -uroot -p0209 数据库的名称 > 保存的路径
mysqldump -uroot -p0209 test > d://a.sql;
还原
登录数据库
创建数据库
使用数据库
执行文件 source 文件路径
source d://a.sql;

多表查询

内连接

隐式

where

select emp.name, gender, dept.name from emp, dept where emp.dept_id = dept.id;

显示

inner join 或者 join
查询的是交集
内联查询，其实就是两张表中的数据，通过某个字段相对比，查询出相关记录数据

select * from person inner join card on person.cardId=card.id;
//inner可以省略
select * from person join card on person.cardId=card.id;

+------+------+--------+------+--------+
| id   | name | cardID | id   | name   |
+------+------+--------+------+--------+
|    1 | 张三 |      1 |    1 | 饭卡   |
|    2 | 李四 |      3 |    3 | 农行卡 |
+------+------+--------+------+--------+
2 rows in set (0.00 sec)

外连接

左连接 left join 或者 left outer join

查询的是左侧的所有和交集

左外连接，会把左边表里面的所有数据取出来，而右边表中的数据，如果有相等的，就显示出来，如果没有，就会补 NULL

select * from person left join card on person.cardId=card.id;

select * from person left outer join card on person.cardId=card.id;
+------+------+--------+------+--------+
| id   | name | cardID | id   | name   |
+------+------+--------+------+--------+
|    1 | 张三 |      1 |    1 | 饭卡   |
|    2 | 李四 |      3 |    3 | 农行卡 |
|    3 | 王五 |      6 | NULL | NULL   |
+------+------+--------+------+--------+
3 rows in set (0.00 sec)

右连接 right join 或者 right outer join

查询的是右侧所有和交集

右外连接，会把右边表里面的所有数据取出来，而左边表中的数据，如果有相等的，就显示出来，如果没有，就会补 NULL

select * from person right join card on person.cardId=card.id;

select * from person right outer join card on person.cardId=card.id;
+------+------+--------+------+--------+
| id   | name | cardID | id   | name   |
+------+------+--------+------+--------+
|    1 | 张三 |      1 |    1 | 饭卡   |
| NULL | NULL |   NULL |    2 | 建行卡 |
|    2 | 李四 |      3 |    3 | 农行卡 |
| NULL | NULL |   NULL |    4 | 工商卡 |
| NULL | NULL |   NULL |    5 | 邮政卡 |
+------+------+--------+------+--------+
5 rows in set (0.00 sec)

外全外连接 full join 或者 full outer join

select * from person full join card on person.cardId=card.id;
//mysql 不支持 full join
//等价于
select * from person left join card on person.cardId=card.id
union
select * from person right join card on person.cardId=card.id;

+------+------+--------+------+--------+
| id   | name | cardID | id   | name   |
+------+------+--------+------+--------+
|    1 | 张三 |      1 |    1 | 饭卡   |
|    2 | 李四 |      3 |    3 | 农行卡 |
|    3 | 王五 |      6 | NULL | NULL   |
| NULL | NULL |   NULL |    2 | 建行卡 |
| NULL | NULL |   NULL |    4 | 工商卡 |
| NULL | NULL |   NULL |    5 | 邮政卡 |
+------+------+--------+------+--------+
6 rows in set (0.00 sec)

子查询

查询中嵌套查询，称嵌套查询为子查询

select * from emp where salary = (select max(salary) from emp);

子查询的不同情况

子查询的结果是单行单列的
- 子查询可以作为条件，使用运算符去判断
```
select * from emp where salary = (select max(salary) from emp);
```

子查询的结果是多行单列的

子查询可以作为条件，使用运算符in判断

select * from emp where dept_id in (select id from dept where dept.name in ('财务部', '市场部'));

子查询的结果是多行多列的

子查询可以作为虚拟表。做一个表的查询

select dept.id, dept.dname, dept.loc, total
from dept,
     (select dept_id, count(id) total
       from emp
       group by dept_id) t2
where dept.id = t2.dept_id;

MySQL事务

mysql中，事务其实是一个最小的不可分割的工作单元，事物能够保证一个业务的完整性

比如银行转账

a -> -100
update user set money=money-100 where name='a';
b -> +100
update user set money=money+100 where name='a';

实际的程序中，如果只有一条语句执行成功了，而另一条语句没有执行成功，会出现数据前后不一致的情况
多条sql语句，可能会有同时成功的要求，要么就同时失败

mysql 默认是开启事务的(自动提交)

select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

默认事务开启的作用

当我们去执行一个sql语句的时候，效果会立即体现出来，且不能回滚
事务回滚
- 撤销sql语句执行效果
- rollback；
- //设置MySQL自动提交为false,则关闭了MySQL的自动提交
  - set autocommit = 0
  - 手动提交数据
    - commit；
    - 再撤销，是不可以撤销的(持久性)
手动开启一个事务
- begin；
- start transaction
- 写在语句前
set autocommit 是全局控制
begin和start transaction是局部控制

create database bank;

use bank;

create table user (id int primary key, name varchar(20), money int);

insert into user values (1, 'a', 1000);
select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+
1 row in set (0.00 sec)

rollback;

select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+
1 row in set (0.00 sec)


//设置MySQL自动提交为false

set autocommit = 0;

select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

insert into user values(2,'b',1000);

select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+
2 rows in set (0.00 sec)

rollback;

select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+
1 row in set (0.00 sec)

insert into user values(2,'b',1000);

commit；

select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+
2 rows in set (0.00 sec)

rollback;

select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+
2 rows in set (0.00 sec)

总结

事务开启
- 修改默认提交
  - set autocommit = 0；
- begin；
- start transaction；
事务手动提交
- commit;
事务手动回滚
- rollback；

事物的四大特征

A
- 原子性：事务是最小的单位，不可以再分割
C
- 一致性：事务要求，同一事务中的sql语句，必须保证同时成功或者同时失败
I
- 隔离性：事务 1 和事务 2 之间是具有隔离性的
持久性
- 事务一旦结束(commit,rollback),就不可以返回

事务的隔离性

read uncommitted；
- 读未提交的
- 如果有事务a和事务b，a事务对数据进行操作，在操作的过程中，事务没有被提交，但是b可以看见a操作的结果
- 脏读
  - 一个事务读到了另外一个事务没有提交的数据，就叫做脏读
read committed
- 都已经提交的
- 虽然我只能读到另外一个事务提交的数据，但还是会出现问题，就是读取同一个表的数据发现前后不一致
- 不可重复读现象：read committed
repeatable read；
- 可以重复读
- 幻读
  - 事务a和事务b同时操作一张表，事务a提交的数据，也不能被事务b读到，就可以造成幻读
serializable；
- 串行化
  - 当user 表被另一个事务操作的时候，其他事务里面的写操作，是不可以进行的，进入排队状态(串行化)，直到另一个事务结束之后，其他事务的写入操作才会执行，在没有等待超时的情况下
性能
- read-uncommitted > read-committed > repeatable-read > serializable
- 隔离级别越高，性能越差
- MySQL默认隔离级别是 repeatable-read
查看数据库的隔离级别

//系统级别的
select @@global.transaction_isolation;

//会话级别的
select @@transaction_isolation;

+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ                |
+--------------------------------+
1 row in set (0.00 sec)

默认隔离级别
- REPEATABLE-READ

修改隔离级别

//当前会话
set session transaction isolation level read committed;

//全局的
set global transaction isolation level read committed;

管理用户

管理用户_增删查
- 步骤
  1. 切换到mysql数据库
    - use mysql;
  2. 查询user表
    - select * from user;
  3. 创建用户
    - create user ‘用户名’@‘主机名’ indentified by ‘密码’;
  4. 删除用户
    - drop user ‘用户名’@‘主机名’;
  5. 修改密码
    - update user set password = password(‘新密码’) where user = ‘用户名’;
    - 改完需要刷新权限
      - flush privileges;
    - set password for ‘用户名’@‘主机名’ = password(‘新密码’); -----弃用
    - alter user ‘用户名’@‘主机名’ identified with mysql_native_password by ‘新密码’;
    - alter user ‘用户名’@‘主机名’ identified by ‘新密码’;
忘记密码
1. cmd --> net stop mysql 停止mysql服务
  - 需要管理员运行该cmd
2. 使用无验证方式启动mysql服务
  - mysqld --skip-grant-tables;
3. 打开新的cmd窗口，直接输入mysql命令，敲回车就可以登录成功
4. use mysql;
5. alter user ‘用户名’@‘主机名’ identified with mysql_native_password by ‘新密码’;
6. 关闭两个窗口
7. 打开任务管理器手动结束mysqld.exe的进程
8. 启动mysql服务
9. 使用新密码登录
权限管理
- 查询权限
  - show grants for ‘用户名’@‘主机名’;
- 授予权限
  - grant 权限列表 on 数据库名.表名 to ‘用户名’@‘主机名’;
  - 权限列表
    - select
    - delete
    - update
    - 给所有权限
      - grant all on *.* to ‘用户名’@‘主机名’;
- 撤销权限
  - revoke 权限列表 on 数据库名.表名 from ‘用户名’@‘主机名’;

补充

视图

概念

是从一个或多个表（或视图）中导出的表，他是用户使用数据的观点。
例如，对于一个学校，其学生的情况存于数据库的一个或多个表中，而作为学校的不同职能部门，所关心的学生数据的内容是不同的，即使是同样的数据，也可能有不同的操作要求，于是就可以根据他们的不同需求，在物理的数据库上定义他们对数据库所要求的数据结构，这种根据用户观点所定义的数据结构就是视图

视图和表的区别

视图是一个虚表，即它所对应的数据不进行实际存储，数据库中只存储视图的定义
对视图的数据进行操作时，系统根据视图的定义去操作与视图相关联的基本表

优点

为用户集中数据，简化用户的数据查询和处理
屏蔽数据库的复杂性
简化用户权限的管理
便于数据共享
可以重新组织数据以便输出到其他应用程序中

视图的创建

视图在数据中是作为一个对象来存储的
用户创建试图前,要保证自己已被数据库所有者授权使用 create view语句，并且有权操作视图所涉及的表或其他视图
语法格式(可以直接看下面的代码演示)
- ```
create
[or replace]
[algorithm = {undefined | merge | temptable}]
[definer = {user | current_user}]
[sql security {definer | invoker}]
view 视图名 [(列表名)]
as select 语句
[with [cascaded | local] check option]
```
- or replace
  - 给定了该子句，语句能够替换已有的同名视图
- algorithm
  - 一种对mysql拓展的算法，会影响mysql视图的方式
  - undefined，未定义的，默认算法
  - merge,会将引用视图的语句的文本与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分，要求视图中的行和基表（表）中的行具有一对一的关系，如果不具有该关系，必须使用临时表取而代之
  - temptable,视图的结果将被置于临时表中,然后使用他们执行语句
- 列表
  - 有逗号隔开的列明,其名称数据必须等于select语句检索的列数,若使用与源表或试图中相同的列名时可以省略
- select语句用来创建视图，可以在select语句中查询多个表或视图，但对select语句有以下限制
  - 定义视图的用户必须对所参照的表或视图有查询的权限
  - 不能包含from子句的子查询
  - 不能饮用系统或用户变量
  - 不能引用预处理语句参数
  - 在定义中引用的表或视图是必须存在的
  - 若引用的不是当前数据的表或视图，要在表或视图钱加上数据库的名称
  - 在视图定义中允许使用 order by,但是如果从特定视图进行了选择,而该视图使用了具有自己order by的语句,则视图定义中的order by将被忽略’
  - 对于select语句中的其他选项或子句,若视图中也包含了这些选项,'则效果未定义
- with check option,指出在可更新视图上所进行的修改都要符合select_statement所指定的限制条件，这样可以确保数据修改后，仍可通过视图看到修改的数据，当视图是根据另一个视图定义的时候，with check option给出两个参数:local,cascaded，他们定义了检查测试的范围
  - local关键字使with check option只对定义的视图进行检查
  - cascaded则会对所有视图进行检查
  - 如果未给定任一关键字，则默认值为cascaded
使用视图时的注意事项
- 在默认情况下，将在当前数据库创建新视图，要想在给定数据库中明确创建视图，创建时，应将名称指定为db_name.view_name
- 视图的命名必须遵循标识符命名规则，不能与表同名，且每个用户视图名必须是唯一的，即对不同用户，即使是定义相同的视图，也必须使用不同的名字
- 不能把规划、默认值或触发器与视图相关联
- 不能在时涂上建立任何索引，包括全文索引

代码演示

create view cs_kc_avg(num, source_avg)
	as
    select 学号,avg(成绩)
    	from cs_kc
    	group by 学号
    with check option;

视图的查询

视图的查询和表的查询语句一样
- ```
select * from cs_kc_avg where num = 0
```
注意
- 使用视图查询时，若其关联的基本表中添加了新字段，则该视图将不包含新字段，例如
  - 视图cs_kc_avg中的列关联了kc表中所有列，若kc表新增了“生物科学”字段，那么cs_kc_avg视图中将查询不到“生物科学”字段的数据
  - 如果与视图我相关来年的表或视图被删除，则该视图将不能再使用

视图的更新

由于视图是一个虚拟表，索引更新视图数据也就等于更新与其关联的基本表的数据，但不是所有的视图都可以更新，只有对满足可更新条件的视图才能进行更新
可更新视图
- 视图可不可以更新取决于他是不是可更新视图
  - 不可更新视图（下述的任何一种）
    - 视图中的行和基表中的行之间不具有一对一的关系
    - 含有聚合函数
    - 含有distinct关键字
    - 含有group by子句
    - 含有order by子句
    - 含有having子句
    - 含有union运算符
    - 位于选择列表中的子查询
    - from子句中包含多个表
    - select语句中引用了不可更新视图
    - where子句中的子查询,引用from子句中的表
    - algorithm选项指定为temptable(使用临时表总会使视图不可更新)
  - 可更新视图
    - 不含有上述结构的且视图中的表和基表中的行之间具有一对一关系的就是可更新视图
插入数据(创建一个视图,用于之后的插入,添加,修改,删除)
- 创建视图
  - ```
  create or replace view cs_xs
  	as
  	select *
  		from xs
          where 专业名 = '计算机'
      with check option;
```
- 注意：
- 在创建视图的啥时候加上with check option子句，是因为with check option子句会在更新数据的时候检查新数据是否符合视图定义中where子句的条件，with check option子句只能和可更新视图一起使用

添加数据

insert into cs_xs
	values('081255', '李牧', '计算机', 1, '1994-10-14', 50, null, null);

  
- 注意：

    - 当视图所依赖的基本表有多个时，不能像该视图视图插入数据，因为这会影响多个基本表
  - 对insert语句插入试图记录包含源表的所有不能为空的列

修改数据

update cs_xs set 总学分=总学分+9;

  
- 注意：

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

删除数据

delte from cs_xs where 学号 = '081255';

  
- 注意：

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

视图的修改

语法格式

alter
	[algorithm - {undefiened | merge | temptable}]
	[definer = {user | current_user}]
	[sql security {definer | invoker}]
	view 视图[{列表}]
	as select 语句
	[with [cascaded | local] check option]

alter view cs_xs
	as
	select 学号, 姓名, 总分学
		from xs
		where 专业名 = '计算机';

视图的删除

语法格式

drop view [if exists]
	视图名 [视图名]
	[restrict | cascade]

使用drop view欧聪可删除多个视图
- ```
drop voew cs_xs;`
```

索引

概念

无索引的表是一个无顺序的行（记录）集，因此如果要查找某个特定的行，必须从一开始一一查看表中的每一行，分别看他们是否与所需要的值匹配，这回死一个全表扫描，当表中有很多行的时候，显然这个方法会非常浪费时间，如果最终查找结果只有少数的记录和所需值匹配，则效率就非常低了
索引是根据表中一列或若干列按照一定顺序建立的列值与记录行之间的额对应关系表，一个列上的索引包含了该列上的所有列值，冰河列值性蹭了一一对应的关系，在列上创建索引之后，查找数据时可以直接根据该类上的索引找到对应的位置，从而快速的找到数据
例如，如果用户创建了xs表中的学号列的索引，MySQL将在索引对学号列进行排序，对于索引中的每一项，MySQL在内部为他保存一个数据文件中实际记录所在位置的“指针”，因此，如果要查早学号为 081241的学生信息，MySQL能在学号列的索引中找到 081241 的值，然后直接转到数据文件中相应的行，准确的返回该行的数据，在这过程中，MYSQL只需处理一行就可以返回结果，如果没有学号列的索引，MYSQL则要扫描树文件中的所有记录，显然，需要MySQL处理的记录数量越少，他完成任务的速度就越快

作用

快速读取数据
保证数据记录的唯一性
时限表表之间的参照完整性
在使用group by、order by子句进行数据检索时，利用索引可以减少排序和分组的时间
在MYSQL中，所有的MySQL列类型都能被索引，但是要注意以下几点
- 只有当飙泪行为MyISAM、InnoDB或BDB时，才可以向有nul、blob或text的列中添加索引
- 一个表最多可有16个索引，最大索引长度是256字节
- 对于char和varchar列，可以索引列的前缀，这样索引的速度更快并且比索引整个列所需要的磁盘空间少
- MySQL能在多个列上创建索引，索引最多可以由15个列组成（在char和varchar列上，也可以使用猎德前缀作为所以得部分）

分类

目前大部分MySQL都是以B-树（BTREE）方式存储的，BTREE方式构建为包含了多个节点的一棵树，顶部的节点构成了索引的开始点，叫做跟，每个节点中含有索引列的几个值，节点中的每个值又都执行另一个节点或者指向表中的一行，一个节点中的值必须是有序排列的，执行一行的节点叫做叶子页，叶子页本身也是相互连接的，一个叶子页有一个指针指向下一组，这样，表中的每一行都会在索引中有一个对应值，查询的时候皆可以根据索引值直接找到所在的行
索引中的节是存储在文件中的，所以索引也是要占用物理空间的，MYSQL讲一个表的索引都保存在同一个索引文件中
如果更新表中的一个值或向表中添加删除一行，MYSQL会自动的更新索引，因此索引树总是和表的内容保持一致
分类
- 普通索引（index）
  - 这是最基本的索引类型，他没有唯一性之类的限制，创建普通索引的关键字是index
- 唯一性索引(unique)
  - 这种索引和前面的普通索引基本相同,但有一个区别,索引列的所有制都只能出现一次,鸡必须是唯一的,创建唯一性索引的关键字是unique
- 主键(primary key)
  - 主键是一种唯一性索引,他必须指定为primary key,主键一般在创建表的时候指定,也可以通过修改表的方式加入主键,但是每个表只能有一个主键
- 全文索引(fulltext)
  - MySQL支持全文检索和全文索引,在MySQL中,全文索引的索引类型fulltext,全文索引只能在varchar或text类型的列上创建，并且只能在MyISAM表中创建，他可以通过create table命令创建，也可以通过alter table或create index命令创建，对于大规模的数据集，通过alter table（或create index）命令创建全文索引，要比把记录插入带有全文索引的空表更快
- 哈希索引（hash）
  - 当表类型为memory或heap时，除了BTREE索引，MySQL还支持哈希索引，使用哈希索引，不需要建立树结构但是所有值都保存在一个列表中，这个列表指向相关页和行，当根据一个值获取一个特定的行时，哈希索引非常快

索引的创建和删除

使用create index语句创建
- 使用create index语句可以在一个已有表创建索引,一个表可以创建多个索引
- 语法格式
  - ```
  create [unique | fulltext | spatial] index 索引名
  	[索引类型]
  	on 表明(索引列,...)
  	[索引选项]...;
  其中，索引列
  	列名 [(长度)] [asc | desc]
```
- unique | fulltext | spatial
  - unique表示创建的唯一性索引
  - fulltext表示创建的是全文索引
  - spatial表示为空间索引，可以用来索引几何数据类型的列
- 索引名
  - 索引在一个表中的名称必须是唯一的
- 索引类型
  - 部分存储引擎允许在创建索引时指定索引的类型，MySQL支持的索引类型有BTREE和HASH
- 索引列
  - 指定索引列，长度表示使用列的前面长度个字符创建索引，这可使索引文件大大减小，从而节省磁盘空间，在某些情况下，只能对列的前缀进行索引，例如，索引列的长度有一个最大上限255字节，但对于MyISAM和InnoDB表，最长达1000字节
  - 另外，还可以规定索引按升序(asc)或降序(desc)排列，默认为asc，如果一条select语句中的某列按降序排列，那么在盖裂伤定义一个降序索引可加快处理速度
- 代码演示
  - ```
  create index xskc_in
  	on xs_kc(学号(5) asc, 课程号);
```

使用alter table语句创建

语法格式

alter [ignore] table 表名
....
	| add {index | key} [索引名]                 /*添加索引*/
	| add [constraint [symbol]] primary key     /*添加主键*/
			[索引类型] (索引列,...) [索引选项]...
    | add [constraint [symbol]] unique [index|key] [索引名]   /*添加唯一性索引*/
    		[索引类型] (所以列,...) [索引选项]
    | add fulltext [index|key] [索引名]   /*添加全文索引*/
    		(索引列,...) [索引选项]
    | add spatial [index|key] [索引名]    /*添加空间索引*/
    		(索引列,...) [索引选项]
    | add [constraint [symbol]] foreign key [索引名] /*添加外键*/
    		(索引列,...) [reference_definition]
    | disable keys
    | enable keys

索引类型
- 语法格式为 using {BTREE | HASH}，指定为BTREE或者HASH索引
  - 当定义索引是默认索引名，则一个主键的索引叫做primary，其他索引使用第一个索引的第一个列明作为索引名，如果存在多个索引的额名字一某一个列的名字开头，就在列名后面放置一个顺序号码
- constraint[字符串]
  - 字符串为主键、unique建、外键定义一个名字
- disable keys | enable keys
  - 只在MyISAM表中有用，使用alter table ... disable keys可以让MySQL在更新表时停止更新MyISAM表中的非唯一索引，然后使用alter table ... enable keys重新创建爱你丢失的索引，这样可以极大地加快查询速度

代码演示

alter table xs 
	add index xs_xm using btree(姓名);

在建立表时创建索引

语法格式

create [temporary] table [if not exists] 表名
	[([列定义, ... | [索引定义]])]
	[table_option] [select_statment];

其中，索引定义
[constraint [字符串]] primary key [索引类型] (索引列,...)    /*主键*/
| {index | key} [索引名] [索引类型] (索引列, ...)            /*索引*/
| [constraint [字符串]] unique [index|key] [索引名] [索引类型] (索引列, ...)   /*唯一性索引*/
| [fulltext | spatial] [index|key] [索引名] (索引列, ...)  /*全文索引*/
| [constraint [字符串]] foreign key [索引名] (索引列, ...)[reference_definition] /*外键*/

说明
- key通常是index的同义词，在定义类选项的时候，月可以将某列定义为primary key，但是当主键是由多个列组成的多列索引时，定义列时无法定义次主键，必须在语句最后一列后面加上primary key(列名,…)子句

代码演示

create table xs_kc
(
 id int,
 name varchar(20)
 score int,
 primary key(id, name),
    index cj(score)
);

索引的查询

代码演示
- ```
show index from xs;
```

索引的删除

使用drop index删除

语法格式
- ```
drop index 索引名 on表名
```
代码演示
- ```
drop index xh_xs on xs;
```

使用alter table删除

语法格式

alter [ignore] table 表名
...
	| drop primary key           /*删除主键*/
	| drop {index|key} 索引名     /*删除索引*/
	| drop foreign key 外键名     /*删除外键*/

注意
- drop {index|key} 索引名可以删除各种类型的索引
- drop primary key 不需要提供索引名称，因为一个表中只有一个主键
代码演示
- ```
alter table xs
	drop index mark;
```

索引的利弊

利
- 利用索引加速了where子句中与条件相匹配的行的搜索，或者说在执行连接时加快了与其他表中的行匹配的行的搜索
弊
- 索引是以文件存储的，索引文件要占用磁盘空间，如果有大量的索引没索引文件可能会比数据文件更快地达到最大的文件尺寸
- 在更新表中索引列上的数据，对索引也需要更新，这可能需要重新组织一索引，如果表中的索引很多，这是很浪费时间的，也就是说，这样会降低添加、删除、修改和其他写入操作的效率，表中的索引越多，则更新表的时间就会越长
总结，弊端并不妨碍索引的应用，因为索引的好处已经基本掩盖了他的缺陷，在表中有很多行数据的时候，索引通常是不可缺少的

补充主键和唯一索引的区别

主键一定是唯一索引，但是唯一索引不一定是主键
主键不为空，唯一索引可以为空
一个表可以有多个唯一索引，但是主键只能有一个

过程式数据库对象

存储过程

概念

可以定义一段代码存储放在数据库中，这样的程序成为存储过程，他是最重要的数据库对象之一
实质
- 存储过程实质上就是一段代码，他可以由声明式SQL语句（如create、update和select等）和过程式SQL语句（如if-then-else)组成，存储过程可以由程序、触发器或者另一个存储过程来调用，从而激活他，是现代码段中的SQL语句

优点

存储过程在服务器端运行，执行速度快
存储过程执行一次后，其执行规划就驻留在高速缓冲存储器中，在以后的操作中，只需从高速缓冲存储器中调用已编译好的二进制代码即可，提高了系统性能
确保数据库安全，使用存储过程可以完成所有数据库操作，并可以通过编程方式控制上述操作对数据库信息进行访问的权限

存储过程的查看

查看数据库中有哪些存储过程
- ```
show procedure status;
```
查看某个存储存储过程的具体信息
- ```
show create procedure 存储过程名;
```

存储过程的创建

要在MySQL创建存储过程，就必须具有create routine权限
语法格式
- ```
create procedure 存储过程名([参数 [,...]])
	[特征 ...]
[存储过程主体]

其中，参数
[in | out | inout] 参数名 type
```
- 存储过程名(sp_name)
  - 默认在当前数据库中创建存储过程，需要在特定数据中创建存储过程时，则要在名称前面加上数据库的名称，格式为“数据库名.存储过程名”
    - 注意，存储过程名应当避免与MySQL的内置函数的名称相同，否则会发生错误
- 参数
  - 参数名后面的type为参数的类型，当有多个参数的时候后中间用逗号隔开，存储过程可以由0个、1个或多个参数
  - MySQL存储过程支持三种类型的参数：输入参数、输出参数和输入/输出参数，关键字分别是in、out、inout，输入参数使数据可以传递给一个存储过程，当需要返回一个答案或结果的时候，存储过程使用输出参数，输入/输出参数皆可以充当输入参数，也可以充当输出参数，存储过程也可以不加参数，但是名称后面的括号是不可省略的
    - 注意
      - 参数的名字不要和列的名字一样，否则，虽然不会返回出错消息，但是存储过程中的SQL语句会将参数名看作列名，从而引发不可预知的结果
- 特征
  - ```
  language sql
  | [not] deterministic
  | {contains sql | no sql | reads sql data | modifies sql data}
  | sql security {definer | invoker}
  | comment 注释
```
- language sql
  - 表名编写这个存储过程的语言为sql语言，目前来讲，MySQL存储过程还不能用外部编程语言来编写，也就是说，这个选项是可以不指定的，将来会对其扩展
- deterministic
  - 设置为deterministic表示存储过程对童颜的输入参数产生相同的结果，设置为 not deterministic则表示会产生不确定的结果，默认为not deterministic
- contains sql | no sql | reads sql data | modifies sql data
  - contains sql表示存储过程不包含读或写数据的语句
  - no sql表示存储过程不包含sql语句
  - reads sql data表示存储过程包含读数据的语句,但不包含写数据的语句
  - modifies sql data表示存储过程包含写数据的语句
  - 如果这些特征没有给定,默认的是contains sql
- sql security
  - 可以用来指定存储过程式使用创建该存储过程的用户(definer)的许可来执行,还是使用调用者(invoker)的许可来执行,默认值是definer
- comment 'string'
  - 对存储过程的描述，string为描述内容，这个信息可以用show create procedure语句来显示
- 存储过程主体
  - 包含了在过程调用的时候必须执行的若干个语句，这个部分总是以begin开始，以end结束，当然，当存储过程提中只有一个sql语句时，可以省略begin-end标志
注意
- 在MySQL中，服务器处理语句的时候是以分好为结束标志的，但是在创建存储过程的时候，存储过程体中可能包含多个sql语句，每个sql语句都是以分号为结尾的，服务器处理程序时遇到第一个分号就会认为程序结束，这肯定是不行的，所以这里使用delimiter命令，将 mysql语句的结束标志修改为其他符号
- 语法格式
  - ```
  delimiter $$
```
- 说明
  - $$是用户定义的结束符，通常这个符号可以是一些特殊的符号，如两个##，连个￥￥等，当使用delimiter命令时,应该避免使用反斜杠"\"字符,因为他是MySQL的转义字符
- 代码演示
  - ```
  delimiter //
  
  select id from xs // 
```
- 要想恢复使用“；”作为结束符，可以使用这条语句
  - ```
  opdelimiter ;
```

代码演示

delimiter $$
create procedure delete_student(in xh char(6))
begin
	delete from xs where id = xs;
end $$
delimiter ;

存储过程体

在存储过程体中可以使用所有的sql语句类型，包括所有的dll，dcl，dml语句，当然，过程式语句也是允许的，其中也包括变量的定义和赋值
局部变量
- 在存储国测好难过中可以声明局部变量，他们可以用来存储临时结果，要声明局部变量必须使用declare语句,在声明局部变量的同时,也可以对其赋一个初值
- 语法格式
  - ```
  delcare 变量名[...] type [default 值]
```
- type为变量类型，default子句给变量指定一个默认值，如果不指定则默认为null
- 代码演示
  - ```
  declare num int(4)default 4
```
- 说明
  - 局部变量只能在begin…end语句块中声明
  - 局部变量必须在存储过程的开头就声

使用set语句赋值

语法格式

set 变量名 = 表达式 [， 变量名 = 表达式]

代码演示
- ```
set num = 1, str1 = 'hello'
```

select...into语句
- 使用select...into语句可以把选定的列值直接存储到变量中,因此,返回的结果只能有一行
- 语法格式
  - ```
  select 列名[...] into 变量名[...] 表名...
```
- 代码演示
  - ```
  select name, subjectName into name , subject from xs where id = '081101';
```
- 变量name和subject需要在之前经过声明

流程控制语句

if语句

语法格式

if 条件1 then
	语句序列1
[elseif 条件2 then
 	语句序列2]
 	...
[else
 	语句序列0]
 end if

代码演示

delimiter $$
create procedure xscj.compar(in k1 integer, in k2 integer, out k3 char(6))
begin
	if k1>k2 then
		set k3 = '大于';
	elseif k1=k2 then
		set k3 = '等于';
    else
    	set k3 = '小于';
    end if;
end$$
delimiter ;

case语句

语法格式

case 表达式
	when 值1 then 语句序列1
	[when 值2 then 语句序列2]
...
	[else 语句序列0]
end case


-------------------------------------
				或者
-------------------------------------

case
	when 条件1 then 语句序列1
	[when 条件2 then 语句序列2]
...
	[else 语句序列0]
end case                                                                                                                                                       ````````````

代码演示

delimiter $$
create procedure xscj.result(in varcahr(4), out sex varcahr(4))
begin
	case str
		when 'm' then set sex = '男';
		when 'f' then set sex = '女';
		else set sex='无';
	end$$
delimiter;

-------------------------------------------

case
	when str='m' then set sex = '男';
	when str='f' then set sex = '女';
	else set sex = '无'；
end case;

循环语句
- while语句语法格式
  - ```
  [begin_label]
  while 条件 do
  	语句序列
  end while [end_lable]
```
  - begin_label和end_label是while语句的标注，除非begin_label存在，否则end_label不能被给出，并且如果两者都出现，他们的名字必须是相同的
- 代码演示
  - ```
  delimiter $$
  create procedure dowhile()
  begin
  	declare v1 int default 5;
  	while v1 > 0 do
  		set v1 = v1 - 1;
  	end while;
  end$$
  delimiter;
```
- repeat语句语法格式
  - ```
  [begin_label]
  repeat
  	语句序列
  	until 条件
  end repeat [end_lable]
```
- 代码演示
  - ```
  repeat
  	v1 = v1 - 1
  	until v1 < 1
  end repeat;
```
- loop语句语法格式
  - ```
  [begin_label]
  loop
  	语句序列
  end loop [end_lable]
```
- loop允许某种特定的语句或语句群的重复执行，实现一个简单的循环构造，语句序列是需要重复执行的语句，在循环内的语句一直重复至循环被退出，退出时通常伴随着一个leave语句
- leave语句经常和begin…end或循环一起使用，结构如下
  - ```
  leave label;
```
  - label时语句中标注的名字，这个名字是自定义的，加上leave关键字就可以用来退出被标注的循环语句
  - 代码演示
    - ```
    delimiter $$
    create procedure doloop()
    begin
    	set @a = 10;
    	label: loop
    		set @a = @a-1;
    		if @a < 0 then
    			leave label;
    		end f;
    	end loop label;
    end$$;
    delimiter;
```
- 还有一个iterate语句，他只可以出现在loop，repeat，while语句内部，意为再次循环，语法格式为
  - ```
  iterate label
```
- 说明
  - 该语句的语法格式与leave差不多，区别在于：leave语句是离开一个循环，而iterate语句时重新开始一个循环

处理程序和条件
- 在存储过程中，处理sql语句可能导致一条错误消息，例如：向一个表中插入新的行而主键值已经存在，这条insert语句会导致一个出错消息，并且MySQL立即停止对存储过程的处理，每一个错误消息都有一个唯一代码和sqlstate代码,例如，sqlstate 23000属于如下的出错代码
  - ```
  Error 1022, "Can't write; duplicate key in table"
  Error 1048, "Column cannot be null"
  Error 1052, "Column is ambiguous"
  Error 1062, "Duplicate entry for key"
```
- 为了防止MySQL在一条消息产生时就停止处理，需要使用到 declare handler语句,为错误代码声明一个所谓的处理程序,他指明,对一条sql语句的处理如果导致一条错误消息,将会发生什么
- 语法格式
  - ```
  declare 处理程序的类型 handler for condition_value[,...] 程序语句段
```
- 说明
  - 处理程序的类型主要有三种
    - continue
      MySQL不中断存储过程的处理
    - exit
      当前begin…end复合语句的执行被终止
    - undo
      暂时还不被支持
  - condition_value : 给出sqlstate的代码表示
    - sqlstate [value] sqlsate_value | condition_name | sqlwaring | not found | sqlexception | mysql_error_code
    - condition_name
      
      处理条件的名称
    - sqlwaring
      
      是对所有以01开头的sqlstate代码的速记
    - not found
      
      是对所有以02开头的sqlstate代码的速记
    - sqlexception
      
      是对所有没有被sqlwarning或not found捕获的sqlstate代码的速记
    - 当用户不想为每个可能的出错消息都定义一个处理程序时可以使用以上3种形式
    - mysql_error_code
      
      具体的sqlstate代码，出sqlstate值，mysql错误代码也被支持。表示的形式为error=‘xxxx’
- 代码演示
  - ```
  delimiter $$
  create procedure my_insert()
  begin
  	declare continue handler for sqlstate '23000';
  	insert into xs values('091101', '王明');
  end$$
  delimiter;
```
- 注意：
  - 不能为同一个出错消息在同一个begin-end语句块中定义两个或更多处理程序
- 为了提高可读性，可以使用declare condition语句为一个sqlstate或出错代码定义一个名字，并且可以在处理程序中使用者名字
  - 语法格式
    - ```
    declare condition_name condition for condition_value;
```
- 其中，condition_value
  - sql state [value] sqlstate_value | mysql_error_code
- 说明
  - condition_name是处理条件的名称，condition_value为要定义别名的sqlstate或出错代码
- 代码演示
  - ```
  begin
  	declare non_unique condition for sqlstate '23000';
  	declare continue handler for non_unique;
  	insert into xs values('091101', '王明');
  end$$
  delimiter;
```
游标
- 一条select…into语句返回的是带有值得一行，这样可以把数据读取到存储过程，但是常规的select语句返回的是多行数据，如果要处理他需要引入游标这一概念
- MySQL支持简单的游标，在MySQL中，游标一定要在存储过程或函数中使用，不能单独的在查询中使用，使用一个游标需要4条特殊的语句：declare cursor(声明游标)、open cursor(打开游标)、fetch cursor读取游标、close cursor关闭游标
- 如果使用了declare cursor语句声明了一个游标，这样就把他连接到了一个由select语句返回的结果集中，可使用open cursor语句打开这个游标,接着,可以用fetch cursor语句把产生的结果一行一行的读取到存储过程或存储函数中去,游标相当于一个指针,它指向当前的一行数据,使用fetch cursor语句可以把游标移动到下一行,当处理完所有的行时,使用close cursor语句关闭这个游标
- 声明游标
  - 语法格式
    - ```
    declare 游标名 cursor for select 语句
```
- 这个语句声明一个游标，也可以在存储过程定义多个游标，但是一个快中的每一个游标都必须有唯一的名字
- 注意这里的select语句不能有into子句
- 代码演示
  - ```
  declare xs_cur1 cursor for
  	select id, name, birth, score
  	form xs
  	where subject = '计算机';
```
  - 游标只能在存储过程或存储函数中使用，该语句无法单独运行
- 打开游标
  - 语法格式
    - ```
    open 游标名;
```
- 在程序中，一个游标可以打开多次，由于其他的用户或程序本身已经更新了表，所以每次打开的结果可能不同
- 读取数据
  - 语法格式
    - ```
    fetch 游标名 into 变量名 [,变量名]...
```
- fetch…into和select…into语句具有相同的意义，fetch语句是将游指向的一行数据赋给一些变量，子句中变量的数目必须等于声明游标时select子句中列的数目
- 关闭游标
  - ```
  close 游标名;
```
- 代码演示
  - ```
  delimiter $$
  create procedure compute(out number integer)
  begin
  	declare xh char(6);
  	declare found boolean default true;
  	declare number_xs cursor for select id from xs;
  	dec;are continue handler for not found set found = false;
  	set number = 0;
  	open number_xs;
  	fetch number_xs into xh;
  	while found do
  		set number = number + 1;
  		fetch number_xs into xh;
      end while;
      close number_xsl
  end$$
  delimiter;
```

存储过程的调用、删除和修改

调用

语法格式
- ```
call 存储过程名([参数 [,...]])
```
- 说明
  - 如果要调用某个特定数据库的存储过程，则需要在前面加上该数据库的名称，这条语句中的参数个数必须重视等于定义该存储过程的参数的个数

代码演示

delimiter $$
create procedure xscj.compare(in k1 integer, in k2 integer, out k3 char(6))
begin
	if k1 > k2 then
		set k3 = '大于';
	elseif k1 = k2 then
		set k3 = '等于';
	else
		set k3 = '小于';
	end if;
end $$
delimiter;


call compore(3, 6, @k);

select @k;

删除
- 存储过程创建爱你后需要在删除时使用drop procedure语句，在此之前，必须确认该存储过程没有任何依赖关系，否则会导致其他与之关联的存储过程无法运行
- 语法格式
  - ```
  drop procedure [if exists] 存储过程名;
```
- 代码演示
  - ```
  drop procedure if exists dowhile;
```

修改

使用alter procedure语句可以修改存储过程的某些特征

语法格式

alter procedure 存储过程名 [特征...]

其中，特征：

{contains sql | no sql | reads sql data | modifies sql data}
|sql security {defuber | invoker}
|comment 'string'

代码演示

如果要修改存储过程的内容，可以使用先删除再重新定义存储过程的方法

delimiter $$
drop procedure if exists do_query;
create procedure do_query()
begin
	select * from xs;
end$$
delimiter;

delimiter $$
alter procedure do_query()
begin
	select * from xs;
end$$
delimiter;

存储函数

与存储过程的联系和区别

联系
- 存储函数也是过程式对象之一，与存储过程很相似
- 他们都是由sql和过程式语句组成的代码片段，并且可以从应用程序和sql中调用
区别
- 存储函数不能拥有输出参数，因为存储函数本身就是输出参数
- 不能用call语句来调用存储函数
- 存储函数必须包含一条return语句，而这条特殊的sql语句不允许包含于存储过程中

存储函数的创建

create function
- 语法格式
  - ```
  create function 存储函数 ([参数 [,...]])
  	returns type
  	[特征 ...]
  存储函数的主体
```
- 说明
  - 存储函数不能拥有与存储过程相同的名字
  - 参数：
    - 参数只有名称和类型，不能指定in、out和inout
    - returns type 子句声明函数返回值得数据类型
  - 存储函数体
    - 所有在存储过程中使用的sql语句在存储函数中也适用，包括流程控制语句、游标，等，但是存储函数提中必须包含一个return value语句，value为存储函数的返回值，这是存储过程体中所没有的
- 代码演示
  - ```
  delimiter $$
  create function num_of_xs()
  returns integer
  begin
  	return (select count(*) from xs);
  end$$
  delimiter;
```
- return子句中包含select语句时，select语句的返回结果只能时一行且只能有一列值

存储函数的查看

show function statue;

存储函数的调用、删除和修改

调用

语法格式

select 存储函数名 ([参数 [,...]])

代码演示
- ```
select num_of_xs();
```

存储函数中还可以调用另一个存储函数或者存储过程

代码演示

delimiter $$
create function is_stu(xh char(6))
returns char(10)
begin 
	declare name char(8);
	select name_of)stu(xh) into name;
	if name = '王林' then
		return (select birth from xs where id = xh);
	else
		return 'false';
	end if;
end$$
delimiter;

删除

语法格式

drop function [if exists] 存储函数名;

代码演示
- ```
drop function if exists num_of_xs;
```

修改

alter function 存储函数名 [特征 ...]

触发器

概念

触发器是一个被指定关联到一个表的数据对象，触发器是不需要调用的，当对一个表的特别事件出现时它就会被激活，触发器的代码也是由声明式和过程式的sql语句组成的，因此用在存储过程中的语句也可以用在触发器的定义中，在当前mysql中，触发器的功能还不够全面，以后的版本将逐步改进
触发器与表的关系密切，用于保护表中的数据，当由操作影响到触发器保护的数据时，触发器自动执行，例如，通过触发器实现多个表间数据的一致性，当对表执行insert、delete或update操作时，将激活触发器
利用除法器可以方便的实现数据库中的数据的完整性，例如，对于xscj数据库有xs表、xs_kc表和kc表，当要删除xs表中一个学生的数据时，该学生在xs_kc表中对应的记录也相应地被删除了，这样才不会出现不一致的冗余数据，可通过定义delete触发器来实现上述功能

触发器的查看

```
show triggers;
```

触发器的创建

语法格式
- ```
create trigger 触发器名 触发时刻 触发事件
 on 表名 for each row 触发动作
```
- 触发器名
  - 触发器在当前数据中必须具有唯一的名称，如果要在某个人特定数据库中创建，名字前面应该加上数据库的名称
- 触发时刻
  - 有after和before两个选项，以表示触发器时在激活突然地语句之前或之后触发，如果想要在激活触发器的语句执行之后执行几个或更多改变，通常使用afrer选项，如果想要验证新数据是否满足使用的限制，则使用before选项
- 触发事件
  - 指明激活触发程序的语句类型，可以使下述值之一：
    - insert
      - 将新行插入表时激活触发器，例如insert、load data和replace语句
    - update
      - 更改某一行时激活触发器，例如通过update语句
    - delete
      - 从表中删除某一行时激活触发器，例如通过delete和replace语句
- 表名
  - 只有在该表上发生触发事件才会激活触发器，同一个表不能拥有两个具有相同触发时刻和事件的触发器，例如，对于某个表，不能有两个before update触发器，但可以有一个before update触发器和一个before inser触发器，或一个before update触发器和一个after update触发器
- for each row
  - 这个声明用来指定，对于受触发器时间影响的每一行，都要激活触发器的动作，例如，使用一条语句向表中添加一组行，触发器会对每一行执行相应触发器动作
- 触发器动作
  - 包含触发器激活时将要执行的语句，如果要执行多个语句，可使用begin…end复合语句结构，这样，就能使用存储过程中允许的相同语句
  - 注意
    - 触发器不能返回任何结果到客户端，为了组织从触发器返回结果，不要再触发器定义包含select语句，同样，也不能调用将数据返回客户端的存储过程
- 在MySQL触发器中的sql语句可以关联表中的任意列，但不能直接使用裂的名称去标记，否则会使系统混淆，因为激活触发器的语句可能已经修改、删除或添加了新的列名，而列的旧名同时存在，因此必须使用这样的语法来标记：“new.列名”或者“old.列名”，其中“new.列名”用来饮用新行的一列，“old.列名”用来引用更新或删除他之前的已有行的一列，对于insert语句，只有new时合法的，对于delete语句，只有old是合法的，而update语句可以与new或old同时使用

代码演示

delimiter $$
create trigger xs_delete after delete
	on xs fir each riw
begin
	delete from xs_kc where id = old.id;
end$$
delimiter;

在触发器中还可以调用存储过程

delimiter $$
create procedure changes()
begin
	replace into xs1 select * from xs;
end$$
delimiter;

create trigger xs1_changes after insert
	on xs for each row
	call changes();

触发器的删除

语法格式

drop trigger [数据库名.]触发器名

代码演示
- ```
drop trigger xs1_changes();
```

事件

概念

事件是MySQL在相应的时刻调用的过程式数据库对象，一个事件可以只调用一次，例如，在2014年的10月1日下午2点，一个事件也能周期性的启动，例如每周日晚上8点
事件和触发器相似，都是在某些事情发生的时候启动，当在数据库上启动一条触发语句的时候，触发器就启动了，而事件是根据调度事件来启动的，由于他们之间彼此相似，所以事件也称作临时性触发器

作用

关闭账户
打开或关闭数据库指示器
使数据库中的数据在某个间隔后刷新
执行队进入数据的复杂的检查工作

事件的创建

语法格式
- ```
create event [if exists] 事件名
	on schedule 事件描述
	[on completion [not] preserve]
	[enable | disable | disable on slave]
	[comment '注释']
	do sql 语句;
```
- 其中，事件描述
  - ```
  at 时间点 [+ interval 时间间隔]
  | every 时间间隔
  [starts 时间点 [+ interval 时间间隔]]
  [ends 时间点 [+ interval 时间间隔]]
```
- 时间间隔
  - ```
  count {year | quarter | month | day | hour | minute | week | second | year_month | day_hour | day_minute | day_second | hour_minute | hour_second | minute_second}
```
- 事件名
  - 可以使用if not exists 修饰
- 时间描述
  - 表示时间何时发生或者每隔多久发生一次
    - at 子句
      - 表示“时间点”(某个时刻)事件发生，后面可以加上一个“时间间隔”，表示在这个时间间隔后事件发生，由一个数值和单位构成，count时时间间隔的数值单位
    - every子句
      - 表示咋定时间区间内每个多长时间事件发生一次
    - starts子句
      - 指定开始时间
    - ends子句
      - 指定结束时间
- sql语句
  - 包含时间启动时执行的代码，如果包含多条语句，可以使用begin…end复合结构
- 事件的属性
  - 对于每一个事件都可以定义几个属性
  - on completion [not] preserve
    - on completion not preserve表示事件最后一次调用将自动删除该事件
    - on completion preserve 表示事件最后一次调用后将保留该事件
    - 默认为 on completion not preserve
  - enable | disable | disable on slave
    - enable表示该事件是活动的,活动意味着调度器检查事件动作是否是必须调用
    - disable表示改时间是关闭的，关闭意味着事件的声明存储到目录中，但是调度器不会检查他是否应该调用
    - disable on slave表示事件在从机中是关闭的
    - 如果不指定任何选项，在一个事件创建之后，他立即变为活动的
- 一个打开的时间可以执行一次或多次，一个事件的执行称作调用事件，每次调用一个事件，MySQL都会处理事件动作
- MySQL时间调度器负责调用事件，这个模块时MySQL数据库服务器的一部分，他不断的监视一个时间是否需要调用，要创建事件，必须打开调度器，可以使用系统变量，event_scheduler 来打开时间调度器，true为打开，false为关闭
  - ```
  set global event_scheduler = true
```
- 代码演示
  - 立即启动事件
  - ```
  create event direct
  	on schedule at now()
  	do insert into xs value(1, 'tom');
```
- 创建一个30秒后启动的事件
- ```
create event thirty_second
	on schedule at now() + interval 30 second
	do insert into xs value(1, 'tom');
```
  - 创建一个每个月启动一次，开始于下个月并且在2018年的12月31日结束
  - ```
  delimiter $$
  create event startmonth
  	on schedule every 1 month
  		starts curdate() + interval 1 month\
  		ends '2018-12-31'
      do
      begin
      	if year(curdate()) > 2019 then
      		insert into xs value(1, 'tom');
          end if;
      end$$
  delimiter;
```

修改和删除事件

修改
- 语法格式
  - ```
  alter event 事件名
  	[on schedule schedule]
  	[on completion [not] preserve]
  	[rename to 新事件名]
  	[enable | disable | disable on slave]
  	[comment '注释']
  	[do sql语句]
```
- 用户可以使用一条alter event语句让一个事件关闭或再次让他活动，当然，如果一个事件最后一次调用后已经不存在了，就无法修改了，用户还可以使用一条rename to子句修改事件的名称
- 代码演示
  - ```
  alter event startmonth
  	rename to firstmonth;
```

删除

语法格式

drop event [if exists] [数据库名.]事件名;

代码演示
- ```
drop event direct;
```

竹外疏花

关注

2
点赞
踩
3

收藏

觉得还不错? 一键收藏
1
评论
Mysql笔记

根据黑马程序员的课程资料整理所得，仅用于学习使用，如有侵权，请联系删除。MySQL关系型数据库如何使用终端操作数据库如何开启/关闭数据库服务net start mysqlnet stop mysql注释单行注释：-- 注释内容或 # 注释内容（MySQL特有）多行注释：/* 注释 */如何登陆数据库服务器mysql -uroot -p0209mysql -h127.0.0.1 -p0209mysql --host=127.0.0.1 --user=root
复制链接

扫一扫