MySQL

数据库概述

sql、DB、DBMS 是什么

DB

DataBase 数据库，数据库实际上在硬盘上以文件的形式存在

DBMS

DataBase Management System（数据库管理系统，常见的有：MySQL Oracle DB2 Sybase SqlServer…）

SQL

结构化查询语言，是一门标准通用的语言。标准的 sql 适合于所有的数据库产品。SQL 属于高级语言，在执行时，实际上内部也会先进行编译，然后再执行 sql （sql 语句的编译由 DBMS 完成）

三者关系

DBMS 负责执行 sql 语句，通过执行 sql 语句来操作 DB 当中的数据

DBMS –（执行）–> SQL – (操作) —> DB

什么是表

表 table

是数据库的基本组成单元，所有的数据都以表格的形式组织，目的是可读性强

一个表包括行列

行

被称为数据/记录（data）
列

被称为字段（column）

学号(int) 姓名(varchar) 年龄(int)

-———————————–

110 张三 20

120 李四 21

每一个字段应该包括哪些属性？

字段名
数据类型
相关的约束

SQL 语句分类

DQL

数据查询语言:查询语句，凡是 select 语句都是 DQL

DML

数据操作语言：insert 、delete、update 对b表中的数据进行增删改

DDL

事务控制语言：commit 提交事务，rollback 回滚业务

DCL

数据控制语言：grant 授权，revoke 撤销权限

导入演示数据

第一步：登录 MySQL 数据库管理系统

dos 命令窗口

mysql -uroot -p123456

第二步：查看有哪些数据库

show databases；（这个不是SQL语句，属于MySQL的命令。）

第三步：创建属于我们自己的数据库

create databases bjpowernode; （这个不是SQL语句，属于MySQL的命令。）

第四步：使用 bjpowernode 数据

use bjpowernode;(这个不是 SQL 语言，是MySQL的命令 )

第五步：查看当前使用的数据库中有哪些表

show tables;(这个不是SQL语句，属于MySQL的命令。)

第六步：初始化数据

source D:\C\Java_EE\bjpowernode.sql；

bjpowernode.sql，这个文件以sql结尾，这样的文件被称为“sql脚本”。

什么是 sql 脚本呢？

当一个文件的扩展名是.sql，并且该文件中编写了大量的sql语句，我们称这样的文件为sql脚本。

注意：直接使用source命令可以执行sql脚本。

sql脚本中的数据量太大的时候，无法打开，请使用source命令完成初始化。

删除数据库

drop database bjpowernode;

查看表结构

mysql> desc dept ;

表中的数据

mysql> select * from emp;

常用命令

查看版本

mysql> select version();

查看当前使用的是哪个数据库

mysql> select database();

结束一条语句

退出 MySQL

exit

查看创建表的语句

mysql> show create table emp;

简单的查询语句（DQL）

语法格式

select 字段名1，字段名2，字段名3， … from 表名；

提示

任何一条 sql 语句都以 “ ； ” 结尾
sql 语句不区分大小写

演示

查询员工年薪

mysql> select ename,sal * 12 from emp;

字段可以参与数学运算

给查询结果重命名

mysql> select ename,sal * 12 as '年薪' from emp;

别名中有中文？

select ename,sal * 12 as 年薪 from emp; // 错误

select ename,sal * 12 as '年薪' from emp;

注意：标准sql语句中要求字符串使用单引号括起来。虽然mysql支持双引号，尽量别用。as关键字可以省略

mysql> select empno,ename,sal * 12 yearsal from emp;

查询所有字段

mysql> select * from emp;

实际开发不建议使用，效率太低

条件查询

语法格式

select

字段,字段…

from

表名

where

条件;

执行顺序

先是 select 再是 from 再是 where

演示

<>!=

查询工资等于 5000 的员工

mysql> select ename from emp where sal = 5000;

查询SMITH的工资？

select sal from emp where ename = 'SMITH'; // 字符串使用单引号括起来。

找出工资高于3000的员工

select ename,sal from emp where sal > 3000;

select ename,sal from emp where sal >= 3000;

select ename,sal from emp where sal < 3000;

select ename,sal from emp where sal <= 3000;

找出工资不等于3000的？

select ename,sal from emp where sal <> 3000;

select ename,sal from emp where sal != 3000;

between and

找出工资在1100和3000之间的员工，包括1100和3000？

select ename,sal from emp where sal >= 1100 and sal <= 3000;

select ename,sal from emp where sal between 1100 and 3000; // between…and…是闭区间 [1100 ~ 3000]

select ename,sal from emp where sal between 3000 and 1100; // 查询不到任何数据

小结

between and在使用的时候必须左小右大。
between and除了可以使用在数字方面之外，还可以使用在字符串方面。
- select ename from emp where ename between 'A' and 'C';
- select ename from emp where ename between 'A' and 'D'; // 左闭右开。

is null

找出哪些人津贴为NULL？

在数据库当中NULL不是一个值，代表什么也没有，为空。空不是一个值，不能用等号衡量。

必须使用 is null或者is not null

select ename,sal,comm from emp where comm is null;

找出哪些人津贴不为NULL？

select ename,sal,comm from emp where comm is not null;

or

找出哪些人没有津贴？

select ename,sal,comm from emp where comm is null or comm = 0;

找出工作岗位是MANAGER和SALESMAN的员工？

select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';

and和or联合起来用

找出薪资大于1000的并且部门编号是20或30部门的员工。

mysql> select ename,sal,deptno from emp where sal > 1000 and (deptno = 20 or deptno = 30);

注意

当运算符的优先级不确定的时候加小括号。

in等同于or

找出工作岗位是MANAGER和SALESMAN的员工？

select ename,job from emp where job = 'SALESMAN' or job = 'MANAGER';

select ename,job from emp where job in('SALESMAN', 'MANAGER');

select ename,job from emp where sal in(800, 5000); // in后面的值不是区间，是具体的值。

not in

不在这几个值当中。

select ename,job from emp where sal not in(800, 5000);

模糊查询like

找出名字当中含有O的？（在模糊查询当中，必须掌握两个特殊的符号，一个是%，一个是_）

%代表任意多个字符，_代表任意1个字符。

select ename from emp where ename like '%O%';

找出名字中第二个字母是A的？

select ename from emp where ename like '_A%';

找出名字中有下划线的？

mysql> select * from t_user;

select name from t_user where name like '%_%';

找出名字中最后一个字母是T的？

select ename from emp where ename like '%T';

排序数据

语法格式

select

    ename,sal

from

emp

order by

    sal;

注意

默认是升序。怎么指定升序或者降序呢？

asc表示升序
desc表示降序。

select ename , sal from emp order by sal; // 升序

select ename , sal from emp order by sal asc; // 升序

select ename , sal from emp order by sal desc; // 降序。

按照工资的降序排列，当工资相同的时候再按照名字的升序排列。

select ename,sal from emp order by sal desc;

select ename,sal from emp order by sal desc , ename asc;

细节

越靠前的字段越能起到主导作用。只有当前面的字段无法完成排序的时候，才会启用后面的字段。

找出工作岗位是SALESMAN的员工，并且要求按照薪资的降序排列。

  select 

​    ename,job,sal

  from

​    emp

  where 

​    job = 'SALESMAN'

  order by

​    sal desc;

select

字段 3

from

表名 1

where

条件 2

order by

…. 4

order by是最后执行的。

数据处理函数

单行处理函数

特点

一个‘输入对应一个输出

与单行函数相对的是：多行处理函数。（多行函数特点：多个输入，对应一个输出）

常见的单行处理函数

lower

转换小写

mysql> select lower(ename) from emp;

mysql> select lower(ename) as ename from emp;

upper

转换大写

substr

取子串（substr（被截取的字符串，起始下标，截取的长度）

mysql> select substr(ename,1,2) as sub from emp;

注意

起始下标从 1 开始

示例

mysql> select ename from emp where substr(ename,1,1) = 'A';

查询 emp 中名字首字母为 A 的人员

concat

进行字符串的拼接

mysql> select concat(ename,empno) from emp;

示例

首字母小写

mysql> select concat(lower(substr(ename,1,1)),substr(ename,2,length(ename)-1)) from emp;

length

取长度

mysql> select length(ename) as enamelength from emp;

trim

去空格

`mysql> select ename from emp where ename = ‘ KING’;

mysql> select * from emp where ename = trim(' KING');

str_to_date

将字符串转换成日期

date_format

格式化日期

format

设置千分位

round

四舍五入

mysql> select abs from emp;
ERROR 1054 (42S22): Unknown column 'abs' in 'field list'

这样肯定报错，因为会把 abc 当作一个字段的名字，去 emp 表中去找 abc 字段

引入

mysql> select 'csq' from emp;

select 后面可以跟某个表的字段名（可以等同看作变量名），也可以跟字面量/字面值（数据）

mysql> select round(12524.365,0) from emp;

mysql> select round(12524.365,1) from emp; 保留一位小数

mysql> select round(12524.365,-1) from emp;保留十位

mysql> select round(rand() * 100,1) from emp; 100 以内的随机数

iffull

可以将 null 转换为一个具体值

ifnull 是空处理函数，专门处理空的。

在所有数据库当中，只要有 null 参与的数学运算，最终结果就是 null

mysql> select ename ,sal + comm as salcomm from emp;

示例

计算每个员工的年薪

年薪 = （月薪 + 月补助）* 12

mysql> select ename , (sal + comm) * 12 as yearsal from emp; // error

null 只要参与运算，最终结果一定是 null ,为了避免这个现象，需要使用 ifnull 函数.

用法

ifnull(数据，被当作哪个值)

mysql> select ename,(sal + ifnull(comm,0)) * 12 yearsal from emp;

rand

生成随机数

mysql> select rand() from emp;

mysql> select round(rand() * 100,1) from emp; 100 以内的随机数

case..when..then..when..then..else..end

案例

当员工的工作岗位是 MANAGER时，工资上调 10 % 。当员工的工作岗位是 SALESMAN 时，工资上调 50 % 。（注意：不修改数据库，只是将查询结果显示为工资上调）

mysql> select
    -> ename,
    -> job,
    -> (case job when 'MANAGER' then sal * 1.1
    -> when 'SALESMAN' then sal * 1.5
    -> else sal end)
    -> as newsal
    -> from
    -> 		emp;

分组函数

多行处理函数

特点

输入多行，最终输出一行
分组函数在使用的时候，必须先进行分组，然后才能用
如果不进行分组，整张表默认为一组

常用函数

count

计数

sum

求和

avg

求平均值

max

最大值

select max(sal) from emp;

min

最小值

mysql> select min(sal) from emp;

注意事项

分组函数自动忽略 null，不需要提前对 bull 进行处理
count (*) 和 count(字段) 有什么区别
- count（具体字段) ：表示统计该字段下所有不为 null 的元素总数
- count（*）：统计表中的总行数（只要有一行数据，count++)【因为每一行记录不可能全为 null.数据中有一列不为 null ,则这行数据就是有效的)
分组函数不能直接使用在 where 子句中
所有的分组函数可以组合起来一起用

分组查询

分组查询主要涉及到两个子句，分别是 group by 和 having

group by

取得每个工作岗位的工资合计，要求显示岗位名称和工资合计

select job,sum(sal) from emp group by job;

如果使用了 order by,order by 必须放在 group by 后面
按照工作岗位和部门编码分组，取得的工资合计

mysql> select job,sum(sal) from emp group by deptno,job;

在SQL语句中若有group by 语句，那么在select语句后面只能跟分组函数 + 参与分组的字段。

having

如果想对分组数据再进行过滤需要使用having子句

取得每个岗位的平均工资大于两千

mysql> select job,avg(sal) from emp group by job having avg(sal) > 2000;

分组函数的执行顺序

根据条件查询数据
分组
采用having过滤，取得正确的数据

select 语句总结

完整的 select 语句

select 字段
from 表名
where …….
group by ……..
having …….(就是为了过滤分组后的数据而存在的—不可以单独的出现)
order by ……..

执行顺序

首先执行where语句过滤原始数据
执行group by进行分组
执行having对分组数据进行操作
执行select选出数据
执行order by排序

原则

能在where中过滤的数据，尽量在where中过滤，效率较高。having的过滤是专门对分组之后的数据进行过滤的。

连接查询

SQL92 语法

也可以叫跨表查询，需要关联多个表进行查询

显示每个员工信息，并显示所属的部门名称

select ename, dname from emp, dept;

以上语句不正确，会输出56条语句，其实就是两张表记录的成绩，这种情况我们称为笛卡尔积现象，出现错误的原因是没有指定连接条件

指定连接条件

mysql> select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;

以上查询也称为 “内连接”，只查询相等的数据（连接条件相等的数据）
取得员工和所属的领导的姓名

mysql> select e.ename,m.ename from emp e,emp m where e.mgr = m.empno;

以上称为“自连接”，只有一张表连接，具体的查询方法，把一张表看作两张表即可，如以上示例：第一个表emp e代码了员工表，emp m代表了领导表，相当于员工表和部门表一样

SQL99 语法

（内连接）显示薪水大于2000的员工信息，并显示所属的部门名称
- 采用SQL92语法：
  
  select e.ename, e.sal, d.dname from emp e, dept d where e.deptno=d.deptno and e.sal > 2000;
- 采用SQL99语法：
  
  select e.ename, e.sal, d.dname from emp e join dept d on e.deptno=d.deptno where e.sal>2000;
  
  select e.ename, e.sal, d.dname from emp e inner join dept d on e.deptno=d.deptno where e.sal>2000;在实际中一般不加inner关键字
Sql92语法和sql99语法的区别：99语法可以做到表的连接和查询条件分离，特别是多个表进行连接的时候，会比sql92更清晰
（外连接）显示员工信息，并显示所属的部门名称，如果某一个部门没有员工，那么该部门也必须显示出来
- 左连接
  
  select e.ename, e.sal, d.dname from emp e right join dept d on e.deptno=d.deptno;
- 右连接
  
  select e.ename, e.sal, d.dname from dept d left join emp e on e.deptno=d.deptno;

连接分类

内连接

表1 inner join 表2 on 关联条件
做连接查询的时候一定要写上关联条件
inner 可以省略

外连接

左外连接

表1 left outer join 表2 on 关联条件

做连接查询的时候一定要写上关联条件

右外连接.

outer 可以省略*右外连接

表1 right outer join 表2 on 关联条件

做连接查询的时候一定要写上关联条件

outer 可以省略

左外连接（左连接）和右外连接（右连接）的区别：

左连接以左面的表为准和右边的表比较，和左表相等的不相等都会显示出来，右表符合条件的显示,不符合条件的不显示
右连接恰恰相反，以上左连接和右连接也可以加入outer关键字，但一般不建议这种写法，

select e.ename, e.sal, d.dname from emp e right outer join dept d on e.deptno=d.deptno;
select e.ename, e.sal, d.dname from dept d left outer join emp e on e.deptno=d.deptno;

子查询

子查询就是嵌套的select语句，可以理解为子查询是一张表

在where语句中使用子查询，也就是在where语句中加入select语句

查询员工信息，查询哪些人是管理者，要求显示出其员工编号和员工姓名
- 首先取得管理者的编号，去除重复的
  
  mysql> select distinct mgr from emp where mgr is not null;
- 查询员工编号包含管理者编号的
  
  mysql> select empno,ename from emp where empno in(select mgr from emp where mgr is not null);
- 查询哪些人的薪水高于员工的平均薪水，需要显示员工编号，员工姓名，薪水
  
  实现思路
  1. 取得平均薪水
    
    mysql> select avg(sal) from emp;
  2. 取得薪水大于平均薪水的员工
    
    mysql> select empno,ename,sal from emp where sal > (select avg(sal) from emp);

在from语句中使用子查询，可以将该子查询看做一张表

查询员工信息，查询哪些人是管理者，要求显示出其员工编号和员工姓名

首先取得管理者的编号，去除重复的

mysql> select distinct mgr from emp where mgr is not null;

将以上查询作为一张表，放到from语句的后面

使用92语法：
select e.empno, e.ename from emp e, (select distinct mgr from emp where mgr is not null) m where e.empno=m.mgr;
使用99语法：
select e.empno, e.ename from emp e join (select distinct mgr from emp where mgr is not null) m on e.empno=m.mgr;

查询各个部门的平均薪水所属等级，需要显示部门编号，平均薪水，等级编号实现思路
1. 首先取得各个部门的平均薪水
  
  mysql> select deptno ,avg(sal) avg_sal from emp group by deptno;
2. 将部门的平均薪水作为一张表与薪水等级表建立连接，取得等级
  
  select a.deptno,a.avg_sal,g.grade from (select deptno,avg(sal) avg_sal from emp group by deptno ) a join salgrade g on a.avg_sal between g.losal and hisal;

在select语句中使用子查询

查询员工信息，并显示出员工所属的部门名称

第一种做法，将员工表和部门表连接
select e.ename, d.dname from emp e, dept d where e.deptno=d.deptno;
第二种做法，在select语句中再次嵌套select语句完成部分名称的查询
select e.ename, (select d.dname from dept d where e.deptno=d.deptno) as dname from emp e;

Union

union可以合并集合（相加）

查询job包含MANAGER和包含SALESMAN的员工

mysql> select * from emp where job in ('MANAGER','SALESMAN');

使用 union 来合并

mysql> select * from emp where job in('MANAGER')
    -> union
    -> select * from emp where job in('SALESMAN');

合并结果集的时候，需要查询字段对应个数相同。在Oracle中更严格，不但要求个数相同，而且还要求类型对应相同。

limit

mySql提供了limit ，主要用于提取前几条或者中间某几行数据

select * from table limit m,n
其中m是指记录开始的index，从0开始，表示第一条记录
n是指从第m+1条开始，取n条。
select * from tablename limit 2,4
即取出第3条至第6条，4条记录

取得前5条数据

mysql> select * from emp limit 5;

从第二条开始取两条数据

mysql> select * from emp limit 1,2;

取得薪水最高的前5名

mysql> select * from emp e order by e.sal desc limit 5;

表

创建表

语法格式

create table tableName(
   columnName dataType(length),
   ………………..
   columnName dataType(length)
);
set character_set_results='gbk';

show variables like '%char%';

创建表的时候，表中有字段，每一个字段有：

字段名
字段数据类型
字段长度限制
字段约束

MySQL 常用数据类型

Char(长度)	定长字符串，存储空间大小固定，适合作为主键或外键
Varchar(长度)	变长字符串，存储空间等于实际数据空间
double(有效数字位数，小数位)	数值型
Float(有效数字位数，小数位)	数值型
Int( 长度)	整型
bigint(长度)	长整型
Date	日期型年月日
DateTime	日期型年月日时分秒毫秒
time	日期型时分秒
BLOB	Binary Large OBject（二进制大对象）
CLOB	Character Large OBject（字符大对象）

建立学生信息表，字段包括：学号、姓名、性别、出生日期、email、班级标识

create table t_student(
	student_id  	int(10),
	student_name 	varchar(20),
	sex		char(2),
	birthday	date,
	email		varchar(30),
	classes_id	int(3)	
)

向t_student表中加入数据,（必须使用客户端软件，我们的cmd默认是GBK编码,数据中设置的编码是UTF-8）

insert into t_student(student_id, student_name, sex, birthday, email, classes_id) values(1001, 'zhangsan', 'm', '1988-01-01', 'qqq@163.com', 10)

向t_student表中加入数据（使用默认值）

drop table if exists t_student; 
create table t_student(
	student_id  	int(10),
	student_name 	varchar(20),
	sex		char(2)  default  'm',
	birthday	date, 
	email		varchar(30),
	classes_id	int(3)	
)

增加/删除/修改表结构

采用alter table来增加/删除/修改表结构，不影响表中的数据

添加字段

如：需求发生改变，需要向t_student中加入联系电话字段，字段名称为：contatct_tel 类型为varchar(40)

alter table t_student add contact_tel varchar(40);

修改字段

如：student_name无法满足需求，长度需要更改为100

alter table t_student modify student_name varchar(100) ;

添加、修改和删除

insert

添加、修改和删出都属于DML，主要包含的语句：insert、update、delete

Insert 语法格式

Insert into 表名(字段，。。。。) values(值,………..)
l 省略字段的插入

insert into emp values(9999,'zhangsan','MANAGER', null, null,3000, 500, 10);

不建议使用此种方式，因为当数据库表中的字段位置发生改变的时候会影响到insert语句
l 指定字段的插入(建议使用此种方式)

insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values(9999,'zhangsan','MANAGER', null, null,3000, 500, 10);

如何插入日期

插入的日期格式和显示的日期格式一致

insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(9997,'zhangsan','MANAGER', null, '1981-06-12',3000, 500, 10);

采用str_to_date

insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(9996,'zhangsan','MANAGER',null,str_to_date('1981-06-12','%Y-%m-%d'),3000, 500, 10);

添加系统日期（now()）

insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) valu
es(9996,'zhangsan','MANAGER',null,str_to_date('1981-06-12','%Y-%m-%d'),3000, 500, 10);

Update

可以修改数据，可以根据条件修改数据

语法格式

update 表名 set 字段名称1=需要修改的值1, 字段名称2=需要修改的值2 where …….
将job为manager的员工的工资上涨10%

update emp set sal=sal+sal*0.1 where job='MANAGER';

Delete

可以删除数据，可以根据条件删除数据

语法格式

Delete from表名 where 。。。。。
删除津贴为500的员工

delete from emp where comm=500;
删除津贴为null的员工

delete from emp where comm is null;

表复制

create table emp_bak as select empno,ename,sal from emp;

以上方式，会自动创建表，将符合查询条件的数据自动复制到创建的表中

创建表加入约束

常见的约束

非空约束，not null
唯一约束，unique
主键约束，primary key
外键约束，foreign key
自定义检查约束，check（不建议使用）(在mysql中现在还不支持)

非空约束

非空约束，针对某个字段设置其值不为空，如：学生的姓名不能为空

drop table if exists t_student; 
create table t_student(
	student_id  	int(10),
	student_name 	varchar(20) not null,
	sex		char(2)  default  'm',
	birthday	date, 
	email		varchar(30),
	classes_id	int(3)	
)

insert into t_student(student_id, birthday, email, classes_id) 
values
(1002, '1988-01-01', 'qqq@163.com', 10)

以上错误为加入的学生名字为空

唯一约束

唯一性约束，它可以使某个字段的值不能重复，如：email不能重复：

drop table if exists t_student; 
create table t_student(
	student_id  	int(10),
	student_name 	varchar(20) not null,
	sex		char(2)  default  'm',
	birthday	date, 
	email		varchar(30)  unique,
	classes_id	int(3)	
)
insert into t_student(student_id, student_name , sex, birthday, email, classes_id) 
values
(1001,'zhangsan','m', '1988-01-01', 'qqq@163.com', 10)

以上插入了重复的email，所以出现了“违反唯一约束错误”，所以unique起作用了同样可以为唯一约束起个约束名

主键约束

每个表应该具有主键，主键可以标识记录的唯一性，主键分为单一主键和复合（联合）主键，单一主键是由一个字段构成的，复合（联合）主键是由多个字段构成的

drop table if exists t_student; 
create table t_student()
	student_id  	int(10)  primary key,/*列级约束*/
	student_name 	varchar(20) not null,
	sex		char(2)  default  'm',
	birthday	date, 
	email		varchar(30)  ,
	classes_id	int(3)	
)
insert into t_student(student_id, student_name , sex, birthday, email, classes_id) 
values
(1001,'zhangsan','m', '1988-01-01', 'qqq@163.com', 10)

向以上表中加入学号为1001的两条记录，出现如下错误，因为加入了主键约束

我们也可以通过表级约束为约束起个名称：

drop table if exists t_student; 
create table t_student(
	student_id  	int(10),
	student_name 	varchar(20) not null,
	sex		char(2)  default  'm',
	birthday	date, 
	email		varchar(30)  ,
	classes_id	int(3),
    CONSTRAINT p_id PRIMARY key (student_id)
)
insert into t_student(student_id, student_name , sex, birthday, email, classes_id) 
values
(1001,'zhangsan','m', '1988-01-01', 'qqq@163.com', 10)

外键约束 foreign key

外键主要是维护表之间的关系的，主要是为了保证参照完整性，如果表中的某个字段为外键字段，那么该字段的值必须来源于参照的表的主键，如：emp中的deptno值必须来源于dept表中的deptno字段值。

建立学生和班级表之间的连接

首先建立班级表t_classes

drop table if exists t_classes;
create table t_classes(
	classes_id int(3),
	classes_name varchar(40),
	constraint pk_classes_id primary key(classes_id)
)

在t_student中加入外键约束

drop table if exists t_student;
create table t_student(
	student_id  	int(10),
	student_name 	varchar(20),
	sex		char(2),
	birthday	date,
	email		varchar(30),
	classes_id	int(3),
	constraint      student_id_pk primary key(student_id),
constraint	fk_classes_id foreign key(classes_id) references t_classes(classes_id)	   
)

向t_student中加入数据
1
insert into t_student(student_id, student_name, sex, birthday, email, classes_id) values(1001, 'zhangsan', 'm', '1988-01-01', 'qqq@163.com', 10)
出现错误，因为在班级表中不存在班级编号为10班级，外键约束起到了作用

存在外键的表就是子表，参照的表就是父表，所以存在一个父子关系，也就是主从关系，主表就是班级表，从表就是学生表

以上成功的插入了学生信息，当时classes_id没有值，这样会影响参照完整性，所以我们建议将外键字段设置为非空

drop table if exists t_student;create table t_student( student_id int(10), student_name varchar(20), sex char(2), birthday date, email varchar(30), classes_id int (3) not null, constraint student_id_pk primary key(student_id), constraint fk_classes_id foreign key(classes_id) references t_classes(classes_id) )insert into t_student(student_id, student_name, sex, birthday, email, classes_id) values(1001, ‘zhangsan’, ‘m’, ‘1988-01-01’, ‘qqq@163.com ‘, null);

再次插入班级编号为null的数据

添加数据到班级表，添加数据到学生表，删除班级数据，将会出现如下错误：

insert into t_classes (classes_id,classes_name) values (10,’366’); insert into t_student(student_id, student_name, sex, birthday, email, classes_id) values(1001, ‘zhangsan’, ‘m’, ‘1988-01-01’, ‘qqq@163.com ‘, 10) mysql> update t_classes set classes_id = 20 where classes_name = ‘366’; 因为子表（t_student）存在一个外键classes_id，它参照了父表（t_classes）中的主键，所以先删除子表中的引用记录，再修改父表中的数据。我们也可以采取以下措施级联更新。mysql> delete from t_classes where classes_id = 10; 因为子表（t_student）存在一个外键classes_id，它参照了父表（t_classes）中的主键，所以先删除父表，那么将会影响子表的参照完整性，所以正确的做法是，先删除子表中的数据，再删除父表中的数据，采用drop table也不行，必须先drop子表，再drop父表我们也可以采取以下措施级联删除。

级联更新与级联删除

on update cascade;

mysql对有些约束的修改比较麻烦，所以我们可以先删除，再添加

alter table t_student drop foreign key fk_classes_id;

alter table t_student add constraint fk_classes_id_1 foreign key(classes_id) references t_classes(classes_id)  on update cascade;

我们只修改了父表中的数据，但是子表中的数据也会跟着变动。

on delete cascade

mysql对有些约束的修改时不支持的，所以我们可以先删除，再添加

alter table t_student drop foreign key fk_classes_id;

alter table t_student add constraint fk_classes_id_1 foreign key(classes_id) references t_classes(classes_id)  on delete cascade;
delete from t_classes where classes_id = 20;

t_student和t_classes完整示例

drop table if exists t_classes;
create table t_classes(
	classes_id	int (3),
	classes_name	varchar(30)	not null,
	constraint	pk_classes_id	primary key(classes_id) 
)

drop table if exists t_student;
create table t_student(
	student_id	int(10),
	student_name 	varchar(50) not null,
	sex		char(2) not null,
	birthday	date not null,
	email		varchar(30) unique,
	classes_id	int (3) not null,
	constraint	pk_student_id	primary key(student_id),
	constraint	fk_classes_id	foreign key(classes_id) references t_classes(classes_id) 
)

存储引擎

存储引擎的使用

数据库中的各表均被（在创建表时）指定的存储引擎来处理。
服务器可用的引擎依赖于以下因素：
MySQL的版本
服务器在开发时如何被配置
启动选项
为了解当前服务器中有哪些存储引擎可用，可使用SHOW ENGINES语句： mysql> SHOW ENGINES\G

在创建表时，可使用ENGINE选项为CREATE TABLE语句显式指定存储引擎。

CREATE TABLE TABLENAME (NO INT) ENGINE = MyISAM;
如果在创建表时没有显式指定存储引擎，则该表使用当前默认的存储引擎
默认的存储引擎可在my.ini配置文件中使用default-storage-engine选项指定。
现有表的存储引擎可使用ALTER TABLE语句来改变：ALTER TABLE TABLENAME ENGINE = INNODB;
为确定某表所使用的存储引擎，可以使用SHOW CREATE TABLE或SHOW TABLE STATUS语句：

mysql> SHOW CREATE TABLE emp\G

mysql> SHOW TABLE STATUS LIKE ‘emp’ \G

常用的存储引擎

MyISAM存储引擎

MyISAM存储引擎是MySQL最常用的引擎。
它管理的表具有以下特征：
使用三个文件表示每个表：
格式文件 — 存储表结构的定义（mytable.frm）
数据文件 — 存储表行的内容（mytable.MYD）
索引文件 — 存储表上索引（mytable.MYI）
- – 灵活的AUTO_INCREMENT字段处理
- – 可被转换为压缩、只读表来节省空间

InnoDB存储引擎

InnoDB存储引擎是MySQL的缺省引擎。
它管理的表具有下列主要特征：
- – 每个InnoDB表在数据库目录中以.frm格式文件表示
- – InnoDB表空间tablespace被用于存储表的内容
- – 提供一组用来记录事务性活动的日志文件
- – 用COMMIT(提交)、SAVEPOINT及ROLLBACK(回滚)支持事务处理
- – 提供全ACID兼容
- – 在MySQL服务器崩溃后提供自动恢复
- – 多版本（MVCC）和行级锁定
- – 支持外键及引用的完整性，包括级联删除和更新

MEMORY存储引擎

使用MEMORY存储引擎的表，其数据存储在内存中，且行的长度固定，这两个特点使得MEMORY存储引擎非常快。
MEMORY存储引擎管理的表具有下列特征：
- 在数据库目录内，每个表均以.frm格式的文件表示。
- 表数据及索引被存储在内存中。
- 表级锁机制。
- 不能包含TEXT或BLOB字段
MEMORY存储引擎以前被称为HEAP引擎。

选择合适的存储引擎

MyISAM表最适合于大量的数据读而少量数据更新的混合操作。MyISAM表的另一种适用情形是使用压缩的只读表。
如果查询中包含较多的数据更新操作，应使用InnoDB。其行级锁机制和多版本的支持为数据读取和更新的混合操作提供了良好的并发机制。
可使用MEMORY存储引擎来存储非永久需要的数据，或者是能够从基于磁盘的表中重新生成的数据。

事务

概述

事务可以保证多个操作原子性，要么全成功，要么全失败。对于数据库来说事务保证批量的DML要么全成功，要么全失败。事务具有四个特征ACID

四个特征

原子性（Atomicity）
- 整个事务中的所有操作，必须作为一个单元全部完成（或全部取消）。
一致性（Consistency）
- 在事务开始之前与结束之后，数据库都保持一致状态。
隔离性(Isolation)
- 一个事务不会影响其他事务的运行。
持久性(Durability)
- 在事务完成以后，该事务对数据库所作的更改将持久地保存在数据库之中，并不会被回滚。

事务中存在一些概念

事务（Transaction）：一批操作（一组DML）
开启事务（Start Transaction）
回滚事务（rollback）
提交事务（commit）
SET AUTOCOMMIT：禁用或启用事务的自动提交模式

当执行DML语句是其实就是开启一个事务

关于事务的回滚需要注意：只能回滚insert、delete和update语句，不能回滚select（回滚select没有任何意义），对于create、drop、alter这些无法回滚.

事务只对DML有效果。

注意：rollback，或者commit后事务就结束了。

事务的提交与回滚演示

\1) 创建表

create table user(id int (11) primary key not null auto_increment , username varchar(30),password varchar(30)) ENGINE=InnoDB DEFAULT CHARSET=utf8

\2) 查询表中数据

\3) 开启事务START TRANSACTION;

\4) 插入数据

insert into user (username,password) values ('zhangsan','123');

\5) 查看数据

\6) 修改数据

\7) 查看数据

\8) 回滚事务

\9) 查看数据

自动提交模式

自动提交模式用于决定新事务如何及何时启动。
启用自动提交模式：
- 如果自动提交模式被启用，则单条DML语句将缺省地开始一个新的事务。
- 如果该语句执行成功，事务将自动提交，并永久地保存该语句的执行结果。
- 如果语句执行失败，事务将自动回滚，并取消该语句的结果。
- 在自动提交模式下，仍可使用START TRANSACTION语句来显式地启动事务。这时，一个事务仍可包含多条语句，直到这些语句被统一提交或回滚。
• 禁用自动提交模式：
- 如果禁用自动提交，事务可以跨越多条语句。
- 在这种情况下，事务可以用COMMIT和ROLLBACK语句来显式地提交或回滚。
自动提交模式可以通过服务器变量AUTOCOMMIT来控制。
例如：
- mysql> SET AUTOCOMMIT = OFF；
- mysql> SET AUTOCOMMIT = ON；
- mysql> SET SESSION AUTOCOMMIT = OFF；
- mysql> SET SESSION AUTOCOMMIT = ON；
- show variables like ‘%auto%’; – 查看变量状态

事务的隔离级别

隔离级别

事务的隔离级别决定了事务之间可见的级别。
当多个客户端并发地访问同一个表时，可能出现下面的一致性问题：
- 脏读取（Dirty Read）
  - 一个事务开始读取了某行数据，但是另外一个事务已经更新了此数据但没有能够及时提交，这就出现了脏读取。
- 不可重复读（Non-repeatable Read）
  - 在同一个事务中，同一个读操作对同一个数据的前后两次读取产生了不同的结果，这就是不可重复读。
- 幻像读（Phantom Read）
  - 幻像读是指在同一个事务中以前没有的行，由于其他事务的提交而出现的新行。

四个隔离级别

InnoDB 实现了四个隔离级别，用以控制事务所做的修改，并将修改通告至其它并发的事务：
- 读未提交（READ UMCOMMITTED）
  - 允许一个事务可以看到其他事务未提交的修改。
- 读已提交（READ COMMITTED）
  - 允许一个事务只能看到其他事务已经提交的修改，未提交的修改是不可见的。
- 可重复读（REPEATABLE READ）
  - 确保如果在一个事务中执行两次相同的SELECT语句，都能得到相同的结果，不管其他事务是否提交这些修改。（银行总账）
  - 该隔离级别为InnoDB的缺省设置。
- 串行化（SERIALIZABLE）【序列化】
  - 将一个事务与其他事务完全地隔离。

例:A可以开启事物,B也可以开启事物

A在事物中执行DML语句时,未提交

B不以执行DML,DQL语句

隔离级别与一致性问题的关系

设置服务器缺省隔离级别

通过修改配置文件设置

可以在my.ini文件中使用transaction-isolation选项来设置服务器的缺省事务隔离级别。
该选项值可以是：
- READ-UNCOMMITTED
- READ-COMMITTED
- REPEATABLE-READ
- SERIALIZABLE
例如：
- [mysqld]
- transaction-isolation = READ-COMMITTED

通过命令动态设置隔离级别

隔离级别也可以在运行的服务器中动态设置，应使用SET TRANSACTION ISOLATION LEVEL语句。
其语法模式为：

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
- 其中的
  可以是：
  - – READ UNCOMMITTED
  - – READ COMMITTED
  - – REPEATABLE READ
  - – SERIALIZABLE
例如： SET TRANSACTION ISOLATION LEVEL *REPEATABLE READ*;

隔离级别的作用范围

事务隔离级别的作用范围分为两种：
- 全局级：对所有的会话有效
- 会话级：只对当前的会话有效
例如，设置会话级隔离级别为READ COMMITTED ：

mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED；

或：

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED；
设置全局级隔离级别为READ COMMITTED ：

mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED；

查看隔离级别

服务器变量tx_isolation（包括会话级和全局级两个变量）中保存着当前的会话隔离级别。
为了查看当前隔离级别，可访问tx_isolation变量：
- 查看会话级的当前隔离级别：
  
  mysql> SELECT @@tx_isolation;
  
  或：
  
  mysql> SELECT @@session.tx_isolation;
- 查看全局级的当前隔离级别：
  
  mysql> SELECT @@global.tx_isolation;

并发事务与隔离级别示例

read uncommitted(未提交读) –脏读(Drity Read)

会话一	会话二
mysql> prompt s1>	mysql> use bjpowernode
s1>use bjpowernode	mysql> prompt s2>
s1>create table tx (id int(11),num int (10));
s1>set global transaction isolation level read uncommitted;
s1>start transaction;
	s2>start transaction;
s1>insert into tx values (1,10);
	s2>select * from tx;
s1>rollback;
	s2>select * from tx;

read committed(已提交读)

会话一	会话二
s1> set global transaction isolation level read committed;
s1>start transaction;
	s2>start transaction;
s1>insert into tx values (1,10);
s1>select * from tx;
	s2>select * from tx;
s1>commit;
	s2>select * from tx;

repeatable read(可重复读)

会话一	会话二
s1> set global transaction isolation level repeatable read;
s1>start transaction;	s2>start transaction;
s1>select * from tx;
s1>insert into tx values (1,10);
	s2>select * from tx;
s1>commit;
	s2>select * from tx;

索引

索引原理

索引被用来快速找出在一个列上用一特定值的行。没有索引，MySQL不得不首先以第一条记录开始，然后读完整个表直到它找出相关的行。表越大，花费时间越多。对于一个有序字段，可以运用二分查找（Binary Search），这就是为什么性能能得到本质上的提高。MYISAM和INNODB都是用B+Tree作为索引结构

（主键，unique 都会默认的添加索引）

索引的应用

创建索引

如果未使用索引，我们查询工资大于 1500的会执行全表扫描

什么时候需要给字段添加索引：

-表中该字段中的数据量庞大

-经常被检索，经常出现在where子句中的字段

-经常被DML操作的字段不建议添加索引

索引等同于一本书的目录

主键会自动添加索引，所以尽量根据主键查询效率较高。

如经常根据sal进行查询，并且遇到了性能瓶颈，首先查看程序是否存算法问题，再考虑对sal建立索引，建立索引如下：

1、create unique index 索引名 on 表名(列名);

create unique index u_ename on emp(ename);
2、alter table 表名 add unique index 索引名 (列名);

create index test_index on emp (sal);

查看索引

show index from emp;

使用索引

注意一定不可以用select * … 可以看到type!=all了，说明使用了索引

explain select sal from emp where sal > 1500;

条件中的sal使用了索引

如下图：假如我们要查找sal大于1500的所有行，那么可以扫描索引，索引时排序的，结果得出7行，我们知道不会再有匹配的记录，可以退出了。
如果查找一个值，它在索引表中某个中间点以前不会出现，那么也有找到其第一个匹配索引项的定位算法，而不用进行表的顺序扫描（如二分查找法）。
这样，可以快速定位到第一个匹配的值，以节省大量搜索时间。数据库利用了各种各样的快速定位索引值的技术，通常这些技术都属于DBA的工作。

删除索引

DROP INDEX index_name ON talbe_nameALTER TABLE table_name DROP INDEX index_nameALTER TABLE table_name DROP PRIMARY KEY其中，前两条语句是等价的，删除掉table_name中的索引index_name。第3条语句只在删除PRIMARY KEY索引时使用，因为一个表只可能有一个PRIMARY KEY索引， mysql> ALTER TABLE EMP DROP INDEX test_index; 删除后就不再使用索引了，查询会执行全表扫描。

视图

什么是视图

视图是一种根据查询（也就是SELECT表达式）定义的数据库对象，用于获取想要看到和使用的局部数据。
视图有时也被成为“虚拟表”。
视图可以被用来从常规表（称为“基表”）或其他视图中查询数据。
相对于从基表中直接获取数据，视图有以下好处：
- 访问数据变得简单
- 可被用来对不同用户显示不同的表的内容
用来协助适配表的结构以适应前端现有的应用程序
视图作用：
- 提高检索效率
- 隐藏表的实现细节【面向视图检索】

创建视图

如下示例：查询员工的姓名，部门，工资入职信息等信息。

select ename,dname,sal,hiredate,e.deptno from emp e,dept d where e.deptno = e.deptno and e.deptno = 10;

为什么使用视图？因为需求决定以上语句需要在多个地方使用，如果频繁的拷贝以上代码，会给维护带来成本，视图可以解决这个问题

create view v_dept_emp as select ename,dname,sal,hiredate,e.deptno from emp e,dept d where e.deptno = e.deptno and e.deptno = 10;

create view v_dept_avg_sal_grade as select a.deptno, a.avg_sal, b.grade from (select deptno, avg(sal) avg_sal from emp group by deptno) a, salgrade b where a.avg_sal between b.losal and b.hisal; /*注意mysql不支持子查询创建视图*/

修改视图

alter view v_dept_emp as select ename,dname,sal,hiredate,e.deptno from emp e,dept d where e.deptno = 20;

删除视图

drop view if exists v_dept_emp;

DBA命令

新建用户

CREATE USER username IDENTIFIED BY ‘password’;说明:username——你将创建的用户名, password——该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器.例如：create user p361 identified by ‘123’;–可以登录但是只可以看见一个库 information_schema

授权

命令详解

mysql> grant all privileges on dbname.tbname to 'username'@'login ip' identified by 'password' with grant option;

dbname=表示所有数据库
tbname=*表示所有表
login ip=%表示任何ip
password为空，表示不需要密码即可登录
with grant option; 表示该用户还可以授权给其他用户
细粒度授权

首先以root用户进入mysql，然后键入命令：grant select,insert,update,delete on . to p361 @localhost Identified by “123”; 如果希望该用户能够在任何机器上登陆mysql，则将localhost改为 “%”
粗粒度授权

我们测试用户一般使用该命令授权，GRANT ALL PRIVILEGES ON . TO ‘p361‘@’%’ Identified by “123”; 注意:用以上命令授权的用户不能给其它用户授权,如果想让该用户可以授权,用以下命令: GRANT ALL PRIVILEGES ON . TO ‘p361‘@’%’ Identified by “123” WITH GRANT OPTION;

privileges包括

alter：修改数据库的表
create：创建新的数据库或表
delete：删除表数据
drop：删除数据库/表
index：创建/删除索引
insert：添加表数据
select：查询表数据
update：更新表数据
all：允许任何操作
usage：只允许登录

回收权限

命令详解

revoke privileges on dbname[.tbname] from username;

revoke all privileges on *.* from p361;

use mysql

select * from user

进入 mysql库中修改密码;update user set password = password(‘qwe’) where user = ‘p646’;

刷新权限;flush privileges

导出导入

导出

导出整个数据库

在windows的dos命令窗口中执行：mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p123

导出指定库下的指定表

在windows的dos命令窗口中执行：mysqldump bjpowernode emp> D:\ bjpowernode.sql -uroot –p123

导入

登录MYSQL数据库管理系统之后执行：source D:\ bjpowernode.sql

数据库设计的三范式

第一范式

数据库表中不能出现重复记录，每个字段是原子性的不能再分

不符合第一范式的示例

学生编号	学生姓名	联系方式
1001	张三	zs@gmail.com ,1359999999
1002	李四	ls@gmail.com ,13699999999
1001	王五	ww@163.net ,13488888888

存在问题：

n 最后一条记录和第一条重复（不唯一，没有主键）

n 联系方式字段可以再分，不是原子性的

学生编号(pk)	学生姓名	email	联系电话
1001	张三	zs@gmail.com	1359999999
1002	李四	ls@gmail.com	13699999999
1003	王五	ww@163.net	13488888888

关于第一范式，每一行必须唯一，也就是每个表必须有主键，这是我们数据库设计的最基本要求，主要通常采用数值型或定长字符串表示，关于列不可再分，应该根据具体的情况来决定。如联系方式，为了开发上的便利行可能就采用一个字段了。

第二范式

第二范式是建立在第一范式基础上的，另外要求所有非主键字段完全依赖主键，不能产生部分依赖

示例：

学生编号	学生姓名	教师编号	教师姓名
1001	张三	001	王老师
1002	李四	002	赵老师
1003	王五	001	王老师
1001	张三	002	赵老师

确定主键：

学生编号(PK)	教师编号(PK)	学生姓名	教师姓名
1001	001	张三	王老师
1002	002	李四	赵老师
1003	001	王五	王老师
1001	002	张三	赵老师

以上虽然确定了主键，但此表会出现大量的冗余，主要涉及到的冗余字段为“学生姓名”和“教师姓名”，出现冗余的原因在于，学生姓名部分依赖了主键的一个字段学生编号，而没有依赖教师编号，而教师姓名部门依赖了主键的一个字段教师编号，这就是第二范式部分依赖。

解决方案如下：

学生信息表

学生编号（PK）	学生姓名
1001	张三
1002	李四
1003	王五

教师信息表

教师编号（PK）	教师姓名
001	王老师
002	赵老师

教师和学生的关系表

学生编号(PK) fkà学生表的学生编号	教师编号(PK) fkà教师表的教师编号
1001	001
1002	002
1003	001
1001	002

如果一个表是单一主键，那么它就复合第二范式，部分依赖和主键有关系

以上是一种典型的“多对多”的设计

第三范式

建立在第二范式基础上的，非主键字段不能传递依赖于主键字段。（不要产生传递依赖）

学生编号（PK）	学生姓名	班级编号	班级名称
1001	张三	01	一年一班
1002	李四	02	一年二班
1003	王五	03	一年三班
1004	六	03	一年三班

从上表可以看出，班级名称字段存在冗余，因为班级名称字段没有直接依赖于主键，班级名称字段依赖于班级编号，班级编号依赖于学生编号，那么这就是传递依赖，解决的办法是将冗余字段单独拿出来建立表，如：

学生信息表

学生编号（PK）	学生姓名	班级编号（FK）
1001	张三	01
1002	李四	02
1003	王五	03
1004	六	03

班级信息表

班级编号（PK）	班级名称
01	一年一班
02	一年二班
03	一年三班

以上设计是一种典型的一对多的设计，一存储在一张表中，多存储在一张表中，在多的那张表中添加外键指向一的一方的主键

三范式总结

第一范式：有主键，具有原子性，字段不可分割
第二范式：完全依赖，没有部分依赖
第三范式：没有传递依赖.

数据库设计尽量遵循三范式，但是还是根据实际情况进行取舍，有时可能会拿冗余换速度，最终用目的要满足客户需求。

一对一设计，有两种设计方案：
- 第一种设计方案：主键共享
- 第二种设计方案：外键唯一