Mysql数据库笔记整理（五）

回顾：
子查询：
非关联子查询：
先执行子查询（独立语句，仅执行一次）->
返回子查询结果（单值、多值）->
再执行主查询（依赖子查询结果）

 关联子查询：
    先执行主查询（将参数传递给子查询）->
	执行子查询（依赖主查询的数据，执行多次）->
	返回子查询的结果->再执行主查询
	
	exists：判断有没有数据返回（是否满足某些关系）

表间关联查询
内连接：表1 inner join 表2 on 条件
遍历驱动表在匹配表中找匹配记录，匹配上的记录保留，匹配不上的记录丢掉

外连接：表1 left  outer join 表2 on 条件
        表1 right outer join 表2 on 条件
    遍历驱动表在匹配表中找匹配记录，匹配上的记录保留，匹配不上的记录匹配空行之后也保留

    ps：
	外连接的结果集：包含驱动表中所有数据
	严格区分那个表驱动表

组合查询：union（自动去重） 两个结果结果相同

分页查询：
select 字段 from 表名 limit 开始行,数量;
ps：开始行：每页的开始，从0开始
int begin=(page-1)*pageSize);

===============================================================================================================

一、约束条件
1）主键约束（Primary key 简称PK）
主键约束=不能重复+不能为空
一张表中只能有一个主键，主键可以是一列或多列组合。

列级：auto_increment自增
create table stu_zhang(
id int primary key auto_increment,
name varchar(10)
);

ps：primary key auto_increment：MySQL支持主键自增

insert into stu_zhang values(1001,‘张三’);
insert into stu_zhang values(1001,‘李四’);//id不能重复
insert into stu_zhang values(null,‘张三’);//id不能为空

表级(了解):
drop table stu_zhang;

create table stu_zhang(
id int,
name varchar(10),
constraint stu_zhang_id_pk primary key(id)
);

ps：
约束命名：表名_列名_约束类型

insert into stu_zhang values(1001,‘张三’);
insert into stu_zhang values(1001,‘李四’);//id不能重复
insert into stu_zhang values(null,‘张三’);//id不能为空

测试：
primary key auto_increment：MySQL支持主键自增

insert into stu_zhang values(1001,‘张三’);
insert into stu_zhang values(null,‘李四’);
insert into stu_zhang(name) values(‘张三’);

mysql> insert into stu_zhang values(1001,‘张三’);
Query OK, 1 row affected (0.01 sec)

mysql> insert into stu_zhang values(null,‘李四’);
Query OK, 1 row affected (0.00 sec)

mysql> insert into stu_zhang(name) values(‘张三’);
Query OK, 1 row affected (0.02 sec)

mysql> select * from stu_zhang;
±-----±-----+
| id | name |
±-----±-----+
| 1001 | 张三 |
| 1002 | 李四 |
| 1003 | 张三 |
±-----±-----+
3 rows in set (0.00 sec)

2）非空约束（not null 简称NN）
非空约束定义方式只有一种：列级
drop table stu_zhang;

create table stu_zhang(
id int primary key auto_increment,
name varchar(10) not null
);

insert into stu_zhang values(1001,‘张三’);
insert into stu_zhang values(1002,null);

3）唯一约束（Unique 简称UK）
列级：
drop table stu_zhang;

create table stu_zhang(
id int primary key auto_increment,
name varchar(10) not null,
email varchar(20) unique
);

insert into stu_zhang values(1001,‘张三’,‘1720975767@qq.com’);
insert into stu_zhang values(1002,‘李四’,‘1720975767@qq.com’);//数据不能重复
insert into stu_zhang values(1003,‘张三’,null);
insert into stu_zhang values(1004,‘李四’,null);//空值可以重复，空值对唯一约束没有影响的

表级：
drop table stu_zhang;

create table stu_zhang(
id int,
name varchar(10),
email varchar(20),
constraint stu_zhang_id_pk primary key(id),
constraint stu_zhang_email_uk unique(email)
);

insert into stu_zhang values(1001,‘张三’,‘1720975767@qq.com’);
insert into stu_zhang values(1002,‘李四’,‘1720975767@qq.com’);//数据不能重复
insert into stu_zhang values(1003,‘张三’,null);
insert into stu_zhang values(1004,‘李四’,null);//空值可以重复，空值对唯一约束没有影响的

4）外键约束（Foreign key 简称FK）
外键约束定义在两个表两个字段上，用来保证这两个字段的关系。

//创建部门表
create table temp_dept(
deptno primary key auto_increment,
dname varchar(10) not null
);

//创建员工表
create table temp_emp(
empno primary key auto_increment,
ename varchar(10) not null,
deptno int,
constraint temp_emp_deptno_fk foreign key(deptno) references temp_dept(deptno)
);

//往部门表插入数据
insert into temp_dept values(10,‘研发部’);

//往员工表中插入记录
insert into temp_emp values(1001,‘张三’,100);//报错，部门表中没有100号部门

ps：
外键约束主要用于保证数据的完整性和一致性

添加外键约束，两个表在创建和插入数据的时候，
都有一定的先后顺序（先有部门然后才能将员工分给该部门）

二、事务
事务是一组DML操作的逻辑单元，用于保证数据的一致性，
在事务内部组成事务的DML操作，要么一起成功提交，要么被一起撤销。

事务处理语言：TPL
commit：提交
rollback：回滚
savepoint：保存点

事务的开始和终止：
开始：事务开始于上一个事物的结束或第一条DML的操作
终止：事务终止于显示操作（commit/rollback）

事务中的数据状态：
如果有多个会话操作同一张表数据，当用户和服务器连接成功后，
服务器和客户端建立一个会话（Session），客户端和服务器的交互都在此会话中进行。

演示：
步骤1：打开会话A，创建表，插入数据不提交
create table tep(
id int
);

set autocommit=0;//不自动提交

insert into tep values(1);

步骤2：A会话中更新数据不提交
update tep set id=2;

步骤3：A会话更新数据更新数据不提交，B会话删除数据不提交
A会话：update tep set id=3;

B会话：set autocommit=0;
delete from tep;

步骤4：A会话继续更新数据不提交，回滚，之后在查询
update tep set id=4;
rollback;
select * from tep;

总结：
a.事务内部数据的改变如果没有提交，只能在自己的会话中看到数据改变，其他会话看不到数据改变的。
b.事务将会对操作的数据进行加锁，不允许其他事务进行操作（A会话更新未提交，B会话删除会发生阻塞）。
c.如果事务提交，数据的改变得到确认，其他会话可以看到数据改变，数据上锁被释放；保存数据的临时空间被释放。
d.如果事务回滚，数据的改变被取消；数据上的锁被释放，保存数据的临时空间被释放。

演示：保存点
drop table tep;

create table temp(
id int
);

set autocommit=0;
insert into temp values(1);
savepoint A;//保存点
insert into temp values(2);
savepoint B;
insert into temp values(3);
rollback to A;//回滚到保存点A
select * from temp;//1

ps：
回滚到保存点A，A之后的保存点B也会被自动取消。
如果进行commit提交，回滚是无效的。

三、数据库中常用对象
1）表（Table）
表是关系型数据库基本存储结构。
表是一个二维结构，由行和列组成。
横向为行也叫记录，纵为列也叫字段

ps：
表-实体对象
记录-具体实例对象
字段-对象的属性

2）视图（View）
视图是一个虚表（没有数据），其内容由查询定义。
视图是对应一条select语句，此语句得到的结果集赋予一个名字，即视图的名字。
可以像操作表一样操作视图。

//查询20号部门员工信息
select empno,ename
from emp_zhang
where deptno=20;

//创建视图
create view view_emp as
select empno,ename
from emp_zhang
where deptno=20;

//查询视图
select * from view_emp;

说明：
视图的使用和表相同的，视图的好处在于能够简化查询，隐藏数据表中部分列，
视图是不包含任何数据的，视图（view_emp）是基表（emp_zhang）的投影。

//更新基表，查看视图
update emp_zhang set ename=‘郭靖1’ where empno=1004;

select * from view_emp;

//更新视图，查询基表
update view_emp set ename=‘郭靖’ where empno=1004;

select empno,ename from emp_zhang where deptno=20;

ps：基表进行DML操作会改变视图的显示，对视图进行DML操作同样会改变基表的显示。视图只是基表的投影。

//删除视图
drop view view_emp;

3）索引（Index）
索引用于在数据库中加速表查询的数据库对象。
通过快速访问路径的方式定位数据，可以减少磁盘的I/O操作，提高查询效率。

//利用空间换取时间，使用索引占用空间
索引的结构：数据+地址

自动创建索引：主键约束和唯一约束，自动创建索引

手动创建索引：
语法：
create index 索引名 on 表名（列名）;

//根据员工号查询员工信息
select ename
from emp_zhang
where empno=1004;

ps：
show variables like ‘%pro%’;

set profiling=1;//profiling设置开启

show profiles;//查看语句执行时间

//给员工表中empno添加索引
create index empno_index on emp_zhang(empno);

ps：
通过前后时间对比（加不加索引），演示效果不明显，由于数据量比较小。

4）存储流程(Procedure)
存储流程是在大型数据库中，一组完成特定功能的SQL语句集。

存储过程存储在数据库中，经过一次编译后再次调用不需要再次编译，
用户通过指定存储过程名字并给参数（如果存储过程中带有参数）来执行。

好处：
a.把复杂的操作存放在存储过程中简化用户操作
b.简化变动时的修改
c.保证数据的完整性
d.提高应用程序性能

//创建存储过程
create procedure 存储过程名()
begin
…
end

//1、创建存储：查询员工表中最高薪水
delimiter //
create procedure maxSalary()
begin
select max(salary) from emp_zhang;
end //
delimiter ;

//调用存储过程
call maxSalary();

//删除存储过程
drop procedure maxSalary;

ps：
声明分隔符：delimiter //
归还分隔符：delimiter ;

因为Mysql中默认以“;”为分隔符，如果没有声明分隔符，
那么编译器把存储过程当初SQL语句进行处理，则存储过程编译会报错。
所以要事先声明delimiter关键字声明分隔符，这样Mysql才会把“;”当做存储过程代码，不会执行这些代码。

//2、创建存储过程：带有输出参数
查询员工表中最高薪水、最低薪水、平均薪水
delimiter //
create procedure getEmp(
out max_salary double(7,2),
out min_salary double(7,2),
out avg_salary double(7,2)
)
begin
select max(salary) from into max_salary emp_zhang;
select min(salary) from into min_salary emp_zhang;
select avg(ifnull(salary,0)) from into avg_salary emp_zhang;
end //
delimiter ;

//调用存储过程
call getEmp(@max_salary,@min_salary,@avg_salary);

//查看数据
select @max_salary,@min_salary,@avg_salary;

//3、创建存储过程：带输入、输出参数
根据员工号查询员工姓名
delimiter //
create procedure getEname(
in id int,
out name varchar(10)
)
begin
select ename into name from emp_zhang where empno=id;
end //
delimiter ;

//调用存储过程
call getEname(1004,@name);

//查看数据
select @name;

四、用户管理
mysql>use mysql;//mysql数据库下用户管理

//创建用户
create user ‘u1’@‘localhost’ identified by ‘1234’;

//授予权限
grant select,insert,update,delete on jsd.* to ‘u1’@‘localhost’;

//cmd远程登录
mysql -u u1 -p1234

//查看用户
select user()；//u1

//回收权限
revoke select on jsd.* from ‘u1’@‘localhost’;

//删除
drop user u1@localhost;