第一章：数据库
1.1 数据库概述
1.1.1 数据库（DataBase，DB）
数据库就是存储数据的仓库，其本质是一个文件系统，数据按照特定的格式将数据存储起来，用户可以对数据库中的数据进行增加，修改，删除及查询操作。
1.1.2 数据库管理系统(DataBase Management System,DBMS)
数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中表内的数据。
常见的数据库管理系统
- MYSQL ：开源免费的数据库，小型的数据库.已经被Oracle收购了.MySQL6.x版本也开始收费。
- Oracle ：收费的大型数据库，Oracle公司的产品。Oracle收购SUN公司，收购MYSQL。
- DB2 ：IBM公司的数据库产品,收费的。常应用在银行系统中.
- SQLServer：MicroSoft 公司收费的中型的数据库。C#、.net等语言常使用。
- SyBase ：已经淡出历史舞台。提供了一个非常专业数据建模的工具PowerDesigner。
- SQLite : 嵌入式的小型数据库，应用在手机端。
- Java相关的数据库：MYSQL，Oracle．
数据库与数据库管理系统的关系??
DBMS -(执行)-> SQL -(操作)-> DB
1.2 数据库表
-
表：table是数据库的基本组成单元，所有的数据都以表格的形式组织，目的是可读性强。
-
一个表包括行和列：
行：被称为数据/记录(data)
列：被称为字段(column)学号(int) 姓名(varchar) 年龄(int) ------------------------------------ 110 张三 20 120 李四 21 -
每一个字段应该包括哪些属性？
字段名、数据类型、相关的约束。
第二章：MySQL数据库
2.1 登录MySQL数据库
MySQL是一个需要账户名密码登录的数据库，登陆后使用，它提供了一个默认的root账号，使用安装时设置的密码即可登录。
dos命令窗口：
格式1：mysql –u用户名 –p密码
例如：mysql -uroot –proot
格式2：cmd> mysql --host=ip地址 --user=用户名 --password=密码
例如：mysql --host=127.0.0.1 --user=root --password=root
2.2 数据库操作语句
-
查看msyql版本
mysql --version -
查询当前使用的数据库
select database(); -
创建数据库
格式:
create database 数据库名;
create database 数据库名 character set 字符集; -
查看数据库
show databases; -
使用某个数据库;
use (数据库名);
如：use bjpowernode; -
查看当前使用的数据库中有哪些表？
show tables; -
初始化数据
source 数据库存储根目录和数据库名
如：mysql> source D:\course\05-MySQL\resources\bjpowernode.sql -
终止一条语句
如果想要终止一条正在编写的语句，可键入\c。 -
退出mysql
可使用\q、QUIT或EXIT：
如：
mysql> \q (ctrl+c)
2.3 查看“演示数据”的表结构
- 查看和指定现有的数据库
- 使用某个数据库
- 查看当前使用的库
- 查看当前库中的表
- 查看其他库中的表
show tables from（database name）
如查看exam库中的表
- 查看表的结构
desc （table name）；
如：
- 查看表的创建语句
第三章：SQL语句
数据库是不认识JAVA语言的，但是我们同样要与数据库交互，这时需要使用到数据库认识的语言SQL语句，它是数据库的代码。
结构化查询语言(Structured Query Language)简称SQL，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。
创建数据库、创建数据表、向数据表中添加一条条数据信息均需要使用SQL语句。
3.1 SQL语句分类
- DQL（Data Query Language 数据查询语言）: 查询语句，凡是select语句都是DQL。
数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE
子句组成的查询块：
SELECT <字段名表>
FROM <表或视图名>
WHERE <查询条件> - DML（Data Manipulation Language 数据操作语言）：对表当中的数据进行增删改。数据操纵语言DML主要有三种形式：
插入：INSERT
更新：UPDATE
删除：DELETE - DDL（Data Definition Language 数据定义语言）：create drop alter，对表结构的增删改。
- TCL（事务控制语言）：commit提交事务，rollback回滚事务。(TCL中的T是Transaction)
- DCL（数据控制语言）: grant授权、revoke撤销权限等。
第四章：DQL(数据库查询语句)的使用
注意：MySQL在windows下是不区分大小写的
MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的：
1、数据库名与表名是严格区分大小写的；
2、表的别名是严格区分大小写的；
3、列名与列的别名在所有的情况下均是忽略大小写的；
4、变量名也是严格区分大小写的； MySQL在Windows下都不区分大小写
4.1 简单查询
语法格式：
select 字段名1,字段名2,字段名3,… from 表名;
提示：
1、任何一条sql语句以“;”结尾。
2、sql语句不区分大小写。
注意：标准sql语句中要求字符串使用单引号括起来。虽然mysql支持双引号，尽量别用。
- 查询一个字段
select ename from emp;
- 查询多个字段
select empno, ename from emp;
- 查询全部字段
select * from emp;
- 计算员工的年薪
列出员工的编号，姓名和年薪
select empno, ename, sal*12 from emp;
- 利用as语句将查询出来的字段显示为中文
select empno as ‘员工编号’, ename as ‘员工姓名’, sal12 as ‘年薪’ from emp;
注意:字符串必须添加单引号 | 双引号
可以采用as关键字重命名表字段，其实as也可以省略，如：
select empno “员工编号”, ename “员工姓名”, sal12 “年薪” from emp;
4.2 条件查询
语法格式：
select
字段1,字段2...
from
表名
where
条件;
执行顺序：先from，然后where，最后select
支持如下运算符
| 运算符 | 说明 |
|---|---|
| = | 等于 |
| <>或!= | 不等于 |
| < | 小于 |
| <= | 小于等于 |
| > | 大于 |
| >= | 大于等于 |
| between … and …. | 两个值之间,等同于 >= and <= |
| is null | 为null（is not null 不为空） |
| and | 并且 |
| or | 或者 |
| in | 包含，相当于多个or（not in不在这个范围中） |
| not | not可以取非，主要用在is 或in中 |
| like | like称为模糊查询，支持%或下划线匹配%匹配任意个字符下划线，一个下划线只匹配一个字符 |
4.2.1 "="操作符的使用实例
- 查询薪水为5000的员工
select empno, ename, sal from emp where sal=5000;
- 查询job为MANAGER的员工
select empno, ename from emp where job=“manager”;
select empno, ename from emp where job=’manager’;
select empno, ename from emp where job=‘MANAGER’;
4.2.2 “<>或!=” 操作符的使用实例
- 查询薪水不等于5000的员工
select empno, ename, sal from emp where sal <> 5000;
select empno, ename, sal from emp where sal != 5000;
4.2.3 “between … and …” 操作符的使用实例
关于between … and …，它是包含最大值和最小值的
-
查询薪水为1600到3000的员工(第一种方式，采用>=和<=)
select empno, ename, sal from emp where sal >= 1600 and sal <= 3000;
-
查询薪水为1600到3000的员工(第二种方式，采用between … and …)
select empno, ename, sal from emp where sal between 1600 and 3000;
4.2.4 “is null” 操作符的使用实例
在数据库当中NULL不是一个值，代表什么也没有，为空。空不是一个值，不能用等号衡量。必须使用 is null或者is not null
- 查询津贴为空的员工
select * from emp where comm=null;
以上也无法查询出符合条件的数据，因为null类型比较特殊，必须使用 is来比较
select * from emp where comm is null;
4.2.5 “and” 操作符的使用实例
and表示并且的含义，表示所有的条件必须满足
- 工作岗位为MANAGER,薪水大于2500的员工
select * from emp where job=‘MANAGER’ and sal > 2500;
4.2.6 “or” 操作符的使用实例
or，只要满足条件即可,相当于包含
- 查询出job为manager或者job为salesman的员工
select * from emp where job=‘MANAGER’ or job=‘SALESMAN’;
4.2.7 表达式的优先级
- 查询薪水大于1800，并且部门代码为20或30的员工（错误的写法）
select * from emp where sal > 1800 and deptno = 20 or deptno = 30;
以上输出不是预期结果，薪水小于1800的数据也被查询上来了，原因是表达式的优先级导致的，首先过滤sal > 1800 and deptno = 20，然后再将deptno = 30员工合并过来，所以是不对的 - 查询薪水大于1800，并且部门代码为20或30的（正确的写法）
select * from emp where sal > 1800 and (deptno = 20 or deptno = 30);
4.2.8 “in” 操作符的使用实例
in表示包含的意思，完全可以采用or来表示，采用in会更简洁一些
- 查询出job为manager或者job为salesman的员工
- 查询出薪水包含1600和薪水包含3000的员工
select * from emp where sal in(1600, 3000);
4.2.9 “not” 操作符的使用实例
- 查询出薪水不包含1600和薪水不包含3000的员工
select * from emp where sal <> 1600 and sal <> 3000;（第一种写法）
select * from emp where not (sal = 1600 or sal = 3000);（第二种写法）
select * from emp where sal not in (1600, 3000);（第三种写法）
- 查询出津贴不为null的所有员工
select * from emp where comm is not null;
4.2.10 “like” 操作符的使用实例
Like可以实现模糊查询，like支持%和下划线匹配
Like中%和下划线的差别？
%匹配任意字符出现的个数
下划线只匹配一个字符
Like 中的表达式必须放到单引号中|双引号中，以下写法是错误的：
select * from emp where ename like _A%
- 查询姓名以M开头所有的员工
select * from emp where ename like ‘M%’;
- 查询姓名以N结尾的所有的员工
select * from emp where ename like ‘%N’;
- 查询姓名中包含O的所有的员工
select * from emp where ename like ‘%O%’;
- 查询姓名中第二个字符为A的所有员工
select * from emp where ename like ‘_A%’;
4.3 查询排序
排序采用order by子句，order by后面跟上排序字段，排序字段可以放多个，多个采用逗号间隔，order by默认采用升序，如果存在where子句那么order by必须放到where语句的后面。
格式：
select
字段 3
from
表名 1
where
条件 2
order by
.... 4
order by是最后执行的。
4.3.1单一字段排序
- 按照薪水由小到大排序(系统默认由小到大)
select * from emp order by sal;
- 取得job为MANAGER的员工，按照薪水由小到大排序(系统默认由小到大)
select * from emp where job=‘MANAGER’ order by sal;
4.3.2 手动指定排序顺序
- 手动指定按照薪水由小到大排序
select * from emp order by sal asc;
- 手动指定按照薪水由大到小排序
select * from emp order by sal desc;
4.3.3 多个字段排序
如果采用多个字段排序，如果根据第一个字段排序重复了，会根据第二个字段排序
- 按照job和薪水倒序
select * from emp order by job desc, sal desc;
4.3.4 使用字段的位置来排序
- 按照薪水升序
select * from emp order by 6;
4.4 分组函数/聚合函数/多行处理函数
- 所包括的函数
| 函数 | 说明 |
|---|---|
| count | 取得记录数 |
| sum | 求和 |
| avg | 取平均 |
| max | 取最大的数 |
| min | 取最小的数 |
注意：分组函数自动忽略空值，不需要手动的加where条件排除空值。
select count(*) from emp where xxx; 符合条件的所有记录总数。
select count(comm) from emp; comm这个字段中不为空的元素总数。
注意：分组函数不能直接使用在where关键字后面。
mysql> select ename,sal from emp where sal > avg(sal);
ERROR 1111 (HY000): Invalid use of group function
记住：所有的分组函数都是对“某一组”数据进行操作的。
找出工资总和？
select sum(sal) from emp;
找出最高工资？
select max(sal) from emp;
找出最低工资？
select min(sal) from emp;
找出平均工资？
select avg(sal) from emp;
找出总人数？
select count(*) from emp;
select count(ename) from emp;
分组函数一共5个。
分组函数还有另一个名字：多行处理函数。
多行处理函数的特点：输入多行，最终输出的结果是1行。
分组函数自动忽略NULL。
select count(comm) from emp;
+-------------+
| count(comm) |
+-------------+
| 4 |
+-------------+
select sum(comm) from emp;
+-----------+
| sum(comm) |
+-----------+
| 2200.00 |
+-----------+
// 不需要额外添加这个过滤条件。sum函数自动忽略NULL。
select sum(comm) from emp where comm is not null;
4.4.1 Count函数
- 取得所有的员工数(记录数)
select count(*) from emp;
- 取得津贴不为null员工数
select count(comm) from emp;
- 取得工作岗位的个数
select count(distinct job ) from emp;
4.4.2 Sum函数
Sum可以取得某一个列的和，null会被忽略
- 取得薪水的合计
select sum(sal) from emp;
- 取得津贴的合计
select sum(comm) from emp;
- 取得薪水的合计（sal+comm）
select sum(sal+comm) from emp;
从以上结果来看，不正确，原因在于comm字段有null值，所以无法计算，sum会忽略掉，正确的做法是将comm字段中的null 转换成0或者其他数。
select sum(sal+IFNULL(comm, 0)) from emp;
4.4.3 avg函数
取得某一列的平均值
- 取得平均薪水
select avg(sal) from emp;
4.4.4 max函数
取得某个一列的最大值
- 取得最高薪水
select max(sal) from emp;
4.4.5 min函数
- 取得某个一列的最小值
取得最低薪水
4.4.6 组合聚合函数
可以将这些聚合函数都放到select中一起使用
select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;
-
找出工资高于平均工资的员工？
第一步：找出平均工资：select avg(sal) from emp;+-------------+ | avg(sal) | +-------------+ | 2073.214286 | +-------------+第二步：找出高于平均工资的员工：select ename,sal from emp where sal > 2073.214286;
组合使用：select ename,sal from emp where sal > (select avg(sal) from emp);
4.5 单行处理函数
什么是单行处理函数？？？
- 输入一行，输出一行。
计算每个员工的年薪？
select ename,(sal+comm)*12 as yearsal from emp;
重点：所有数据库都是这样规定的，只要有NULL参与的运算结果一定是NULL。
使用ifnull函数：
select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;
ifnull() 空处理函数？
ifnull(可能为NULL的数据,被当做什么处理) ： 属于单行处理函数。
select ename,ifnull(comm,0) as comm from emp;
4.6 分组查询
分组查询主要涉及到两个子句，分别是：group by和having
group by：按照某个字段或者某些字段进行分组。
having : having是对分组之后的数据进行再次过滤。
**注意：**分组函数一般都会和group by联合使用，这也是为什么它被称为分组函数的原因。
并且任何一个分组函数（count sum avg max min）都是在group by语句执行结束之后才会执行的。
当一条sql语句没有group by的话，整张表的数据会自成一组。
mysql> select empno,deptno,avg(sal) from emp group by deptno;
+-------+--------+-------------+
| empno | deptno | avg(sal) |
+-------+--------+-------------+
| 7782 | 10 | 2916.666667 |
| 7369 | 20 | 2175.000000 |
| 7499 | 30 | 1566.666667 |
+-------+--------+-------------+
以上SQL语句在Oracle数据库中无法执行，执行报错。
以上SQL语句在Mysql数据库中可以执行，但是执行结果矛盾。
在SQL语句中若有group by 语句，那么在select语句后面只能跟分组函数+参与分组的字段。
记住一个规则：当一条语句中有group by的话，select后面只能跟分组函数和参与分组的字段。
4.6.1 group by
group by：按照某个字段或者某些字段进行分组。
-
取得每个工作岗位的工资合计，要求显示岗位名称和工资合计。
select job, sum(sal) from emp group by job;
-
按照工作岗位和部门编码分组，取得的工资合计
select job,deptno,sum(sal) from emp group by job,deptno;
先按照工作岗位分组，然后按照部门编码分组，最后查询工资合计
4.6.2 having
having : having是对分组之后的数据进行再次过滤。
- 取得每个岗位的平均工资大于2000
select job, avg(sal) from emp group by job having avg(sal) >2000;
4.6.3 select语句总结
一个完整的select语句格式如下
select 5
..
from 1
..
where 2
..
group by 3
..
having 4
..
order by 6
..
以上语句的执行顺序
1.先从from中找到所要查找的表格
2.执行where进行初次过滤
3.执行group by进行分组
4.执行having对分组数据进行操作
5.执行select选出数据
6.执行order by排序
原则：能在where中过滤的数据，尽量在where中过滤，效率较高。having的过滤是专门对分组之后的数据进行过滤的。
4.7 连接查询
4.7.1 什么是连接查询？为什么要使用连接查询？？
在实际开发中，大部分的情况下都不是从单表中查询数据，一般都是多张表联合查询取出最终的结果。
在实际开发中，一般一个业务都会对应多张表，比如：学生和班级，起码两张表。
如果学生和班级信息存储到一张表中，数据会存在大量的重复，导致数据的冗余。
4.7.2 连接查询的分类
-
根据语法出现的年代来划分的话，包括：
SQL92（一些老的DBA可能还在使用这种语法。DataBase Administrator，数据库管理员）
SQL99（比较新的语法） -
根据表的连接方式来划分，包括：
内连接： 等值连接 非等值连接 自连接 外连接： 左外连接（左连接） 右外连接（右连接） 全连接
4.7.3 笛卡尔积现象
笛卡尔积现象：当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积。
关于表的别名：
select e.ename,d.dname from emp e,dept d;
表的别名有什么好处？
第一：执行效率高。
第二：可读性好。
案例：找出每一个员工的部门名称，要求显示员工名和部门名。
EMP表 DEPT表
+--------+--------+ +--------+------------+----------+
| ename | deptno | | DEPTNO | DNAME | LOC |
+--------+--------+ +--------+------------+----------+
| SMITH | 20 | | 10 | ACCOUNTING | NEW YORK |
| ALLEN | 30 | | 20 | RESEARCH | DALLAS |
| WARD | 30 | | 30 | SALES | CHICAGO |
| JONES | 20 | | 40 | OPERATIONS | BOSTON |
| MARTIN | 30 | +--------+------------+----------+
| BLAKE | 30 |
| CLARK | 10 |
| SCOTT | 20 |
| KING | 10 |
| TURNER | 30 |
| ADAMS | 20 |
| JAMES | 30 |
| FORD | 20 |
| MILLER | 10 |
+--------+--------+
select ename,dname from emp,dept;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | ACCOUNTING |
| SMITH | RESEARCH |
| SMITH | SALES |
| SMITH | OPERATIONS |
| ALLEN | ACCOUNTING |
| ALLEN | RESEARCH |
| ALLEN | SALES |
| ALLEN | OPERATIONS |
............
56 rows in set (0.00 sec)
如上所示：当查询时， EMP表 中的每一条数据将会与 DEPT表中的每一条数据匹配，故产生笛卡尔积的现象。
4.7.4 如何避免笛卡尔积现象
方法：使用条件过滤，将多个表中相同的数据作为过滤条件。
- 避免了笛卡尔积现象，会减少记录的匹配次数吗？
不会，查找时任然会出现笛卡尔积现象，但在显示数据时会过滤掉无用数据。
案例：找出每一个员工的部门名称，要求显示员工名和部门名。
select
e.ename,d.dname
from
emp e , dept d
where
e.deptno = d.deptno; //SQL92，以后不用。
+--------+------------+
| ename | dname |
+--------+------------+
| CLARK | ACCOUNTING |
| KING | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH | RESEARCH |
| JONES | RESEARCH |
| SCOTT | RESEARCH |
| ADAMS | RESEARCH |
| FORD | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| MARTIN | SALES |
| BLAKE | SALES |
| TURNER | SALES |
| JAMES | SALES |
+--------+------------+
4.7.5 内连接
内连接主要包含：等值连接、非等值、连接自连接。
注意：查询时采用join…on语法结构，用on作为过滤条件，替代了老版本的where.
-
内连接之等值连接
特点：连接条件是等量关系。
案例：查询每个员工的部门名称，要求显示员工名和部门名。分析：可以看出需要两张表，一张表为员工表，一张为部门表， 连接过滤条件：员工表的部门号与部门表的部门号相同。 SQL92:（太老，不用了） select e.ename,d.dname from emp e, dept d where e.deptno = d.deptno; SQL99：（常用的） select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno; //其中inner 可以省略 -
内连接之非等值连接
特点：连接条件中的关系是非等量关系。
案例：找出每个员工的工资等级，要求显示员工名、工资、工资等级。分析：可以看出需要两张表，一张表为员工表，一张为工资等级表， 连接过滤条件：员工的薪资在薪资表中某一条数据的最大值与最小值之间。 select e.ename,e.sal,s.grade from emp e inner join salgrade s on e.sal between s.losal and s.hisal; //其中inner 可以省略 -
内连接之自连接
特点：一张表看做两张表。自己连接自己。
案例：找出每个员工的上级领导，要求显示员工名和对应的领导名。分析：可以看出需要两张表，一张表为员工表，一张为领导表。其中，领导也是员工， 员工也可能是领导，因此，可将员工表看成两张表。 连接过滤条件：由员工表得到员工的上级领导编号，这个标号与领导编号做等值连接。 员工的领导编号 = 领导的员工编号 select a.ename as '员工名',b.ename as '领导名' from emp a inner join emp b on a.mgr = b.empno;
4.7.6 外连接
什么是外连接，和内连接有什么区别？
内连接：
假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。
AB两张表没有主副之分，两张表是平等的。
外连接：
假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，主要查询主表中
的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。
外连接的分类？
左外连接（左连接）：表示左边的这张表是主表。
右外连接（右连接）：表示右边的这张表是主表。
左连接有右连接的写法，右连接也会有对应的左连接的写法。
上一个例子中：“找出每个员工的上级领导，要求显示员工名和对应的领导名。”虽然查了出来，但是没有查出员工KING的领导名，应为King是最终领导，他无上级领导，因此应该为null，但是内连接查询会自动过滤空值，所以应该采用外连接。
案例：找出每个员工的上级领导？（所有员工必须全部查询出来。）
内连接：
select
a.ename '员工', b.ename '领导'
from
emp a
join
emp b
on
a.mgr = b.empno;
外连接：（左外连接/左连接）
select
a.ename '员工', b.ename '领导'
from
emp a
left join
emp b
on
a.mgr = b.empno;
外连接：（右外连接/右连接）
select
a.ename '员工', b.ename '领导'
from
emp b
right join
emp a
on
a.mgr = b.empno;
案例：找出哪个部门没有员工？
EMP表（员工）
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
DEPT表（部门）
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
分析：找出哪个部门没有员工？需要两张表，员工表，和部门表。
将部门表作为主表，用部门表的部门编号，与员工表的部门编号做等值连接，
从上表可以看出部门40没有员工，此时系统会为部门40的员工添加null，
因此采用where过滤其中员工编号为null的就是索要查找的。
select
d.*
from
emp e
right join
dept d
on
e.deptno = d.deptno
where
e.empno is null;
三张表怎么连接查询？
案例：找出每一个员工的部门名称以及工资等级。
注意，解释一下：
....
A
join
B
join
C
on
...
表示：A表和B表先进行表连接，连接之后A表继续和C表进行连接。
select
e.ename,d.dname,s.grade
from
emp e
join
dept d
on
e.deptno = d.deptno
join
salgrade s
on
e.sal between s.losal and s.hisal;
案例：找出每一个员工的部门名称、工资等级、以及上级领导。
select
e.ename '员工',d.dname,s.grade,e1.ename '领导'
from
emp e
join
dept d
on
e.deptno = d.deptno
join
salgrade s
on
e.sal between s.losal and s.hisal
left join
emp e1
on
e.mgr = e1.empno;
4.8 子查询
4.8.1 什么是子查询
select语句当中嵌套select语句，被嵌套的select语句是子查询。
子查询可以出现在哪里？
select
..(select).
from
..(select).
where
..(select).
4.8.2 where子句中使用子查询
案例：找出高于平均薪资的员工信息。
第一步：找出平均薪资：select avg(sal) from emp;
第二步：where过滤：select * from emp where sal > 2073.214286;
第一步和第二步合并：select * from emp where sal > (select avg(sal) from emp);
4.8.2 from后面嵌套子查询
案例：找出每个部门平均薪水的等级。
第一步：找出每个部门平均薪水（按照部门编号分组，求sal的平均值）
select deptno,avg(sal) as avgsal from emp group by deptno;
第二步：将以上的查询结果当做临时表t，让t表和salgrade s表连接，条件是：t.avgsal between s.losal and s.hisal
select
t.*,s.grade
from
(select deptno,avg(sal) as avgsal from emp group by deptno) t
join
salgrade s
on
t.avgsal between s.losal and s.hisal;
案例：找出每个部门平均的薪水等级。
第一步：找出每个员工的薪水等级。
select e.ename,e.sal,e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
第二步：基于以上结果，继续按照deptno分组，求grade平均值。
select
e.deptno,avg(s.grade)
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal
group by
e.deptno;
4.9 union（可以将查询结果集相加）
案例：找出工作岗位是SALESMAN和MANAGER的员工？
第一种：select ename,job from emp where job = ‘MANAGER’ or job = ‘SALESMAN’;
第二种：select ename,job from emp where job in(‘MANAGER’,‘SALESMAN’);
第三种：union
select ename,job from emp where job = ‘MANAGER’
union
select ename,job from emp where job = ‘SALESMAN’;
4.10 limit 的使用
注意：limit是mysql特有的，其他数据库中没有，不通用。（Oracle中有一个相同的机制，叫做rownum）
limit取结果集中的部分数据，这时它的作用。
语法机制：
limit startIndex, length
startIndex表示起始位置，从0开始，0表示第一条数据。
length表示取几个
案例：取出工资前5名的员工（思路：降序取前5个）
select ename,sal from emp order by sal desc;
取前5个：
select ename,sal from emp order by sal desc limit 0, 5;
select ename,sal from emp order by sal desc limit 5;
limit是sql语句最后执行的一个环节：
select 5
...
from 1
...
where 2
...
group by 3
...
having 4
...
order by 6
...
limit 7
第五章：DML和DDL语言
DML（Data Manipulation Language 数据操作语言）：对表数据进行增删改。数据操纵语言DML主要有三种形式：
- 插入：INSERT
- 更新：UPDATE
- 删除：DELETE
DDL（Data Definition Language 数据定义语言）：create drop alter，对表结构的增删改。数据操纵语言DML主要有三种形式：
- 创建：CREATE
- 修改：ALTER
- 删除：DROP
5.1 DDL语句
- create(创建) drop(删除) alter(修改)
- MySql常用数据类型
| 类型 | 描述 |
|---|---|
| Char(长度) | 定长字符串，存储空间大小固定，适合作为主键或外键 |
| Varchar(长度) | 可变长字符串，存储空间等于实际数据空间 |
| double(有效数字位数，小数位) | 定长字符串，存储空间大小固定，适合作为主键或外键 |
| Float(有效数字位数，小数位) | 数值型 |
| Int( 长度) | 整数型(java中的int) |
| bigint | 长整型(java中的long) |
| Date | 日期型 年月日 |
| DateTime | 日期型 年月日 时分秒 毫秒 |
| time | 日期型 时分秒 |
| BLOB | Binary Large OBject（二进制大对象） |
| CLOB | Character Large OBject（字符大对象） |
5.1.1 库的DDL语句
-
库的创建(create)
语法格式： create database [if not exists] 库名 案例：创建库Books create database [if not exists] Books; -
库的修改(alter)
语法格式： alter database 库名 ..... 案例：修改库的字符集 alter database 库名 character set gbk; -
库的删除(drop)
语法格式： drop database if exists 库名
5.1.2 表的DDL语句
-
表的创建(create)
语法格式： create table 表名( 字段名1 数据类型[长度，约束], 字段名2 数据类型[长度，约束], 字段名3 数据类型[长度，约束], .... ); 范例：创建一个Book表。 表信息信息包括： 编号、图书名、价格、作者编号、出版日期 create table book( id int,# 标号 bname varchar(20),# 图书名 price double,# 价格 authorId int(255), #作者编号 publishDate DateTime #出版日期 ); 范例：创建一个author表。 create table author( id int, au_name varchar(20), nation varchar(10) ); -
表的修改(alter)
语法格式： alter table 表名 add|drop|modify|change column 列名[列类型，约束].... 关于表修改的5个方面 1 . 修改列名 语法格式： alter table 表名 change column 旧列名 新列名 类型； 范例：将book表中的列publishDate名字修改为pubDate alter table book column publishDate pubDate DateTime; 2. 修改列的类型或约束 语法格式： alter table 表名 modify column 列名 类型； 范例：将book表中的列publishDate的类型修改为TimeStamp alter table book modify column publishDate TimeStamp ; 3. 添加新列 语法格式： alter table 表名 modify add column 列名 类型； 范例：为author表添加列annual 类型为double alter table author add column annual double ; 4. 删除列 语法格式： alter table 表名 drop column 列名； 范例：删除author表中的annual列删除 alter table author drop column annual ; 5. 修改表明 语法格式： alter table 表名 rename to 新表名； 范例：将表author名字修改为book_author alter table author rename to book_author; -
表的删除(drop)
语法格式： drop table if exists 表名 范例：删除表book_author drop table if exists book_author
5.2 DML语句
DML（Data Manipulation Language 数据操作语言）：对表数据进行增删改。数据操纵语言DML主要有三种形式：
- 插入：INSERT
- 更新：UPDATE
- 删除：DELETE
5.2.1 DML之插入(insert)
-
语法格式
方式一： insert into 表名(列名，....) values(值1，....); 方式二： insert into 表名 set 列名 = 值，列名 = 值...; 注意： 1. 插入值得类型要与列的类型一致或者兼容 2. 不为null的列必须插入值 3. 插入值时，其顺序必须与列出的列严格匹配 4. 插入值时，罗列出的列数个值得个数必须一致 5. 可以省略列名(但不建议使用)，省略时默认所有的列使用，但插入值时顺序必须一致。 -
实例
1. 向表emp中插入数据 insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (9999,'zhangsan','MANAGER', null, null,3000, 500, 10); -
总结
建议使用方式一 两种插入方式的区别？？ 1.方式一支持插入多行，方式二不支持 如： insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values (9991,'zhangsan1','MANAGER', null, null,3000, 500, 10)， (9992,'zhangsan2','MANAGER', null, null,3000, 500, 10)， (9993,'zhangsan3','MANAGER', null, null,3000, 500, 10); 2.方式一支持子查询(讲一个表的查询记录作为插入数据)，方式二不支持 如： insert into emp_bak select * from emp where sal=3000;
5.2.2 DML之修改(update)
-
语法格式
1. 修改单表的记录 update 表名 set 列名=新值，... where 筛选条件 2. 修改多表的记录(级联更新) SQL92: update 表1 别名，表2 别名 set 列 = 值，... where 连接条件 and 筛选条件; SQL99； update 表1 别名 连接类型(inner,right,left) join 表2 别名 on 连接条件 set 列 = 值，... where 筛选条件； -
实例
1. 修改单表的记录 将job为manager的员工的工资上涨10% update emp set sal=sal+sal*0.1 where job='MANAGER'; 2. 修改多表的记录(级联更新) 修改张无忌的女朋友的手机号为114 update boys bo inner join beauty b on bo.id = b.boyforiend_id set b.phone = "114" where bo.boyName = "张无忌";
5.2.3 DML之删除(delete)
-
语法格式
方式一：delete(有条件的删除表中记录) 方式二：truncate table 表名(直接删除表中的所有记录) 1. 删除单表的记录 delete from 表名 where 筛选条件 1. 删除多表的记录(级联删除) SQL92: delete 要删除表的别名 from 表1 别名，表2 别名 where 连接条件 and 筛选条件； SQL99: delete 要删除表的别名 from 表1 别名 连接类型(inner,right,left) join 表2 别名 on 连接条件 where 筛选条件； -
案例
1. 删除单表的记录 将beauty表中手机号以9结尾的数据 delete from beauty where phone like "%9"; 2. 删除多表的记录(级联删除) 删除张无忌的女朋友信息 delete b from beauty b inner join boys bo on b.boyfriend = bo.id where bo.boyName = "张无忌"; 删除黄晓明及其女朋友的信息 delete b ，bo from beauty b inner join boys bo on b.boyfriend = bo.id where bo.boyName = "黄晓明"; -
总结
delete和truncate的区别？？ 1. delete 可以加where条件，truncate不能加 2. truncate 删除效率比较高 3. 假如要删除的表中有自增长列，如果使用delete删除后，再插入数据，自增长列的值从 断点开始，而truncate 删除后，再插入数据，自增长的列的值从1开始。 4. truncate 删除没有返回值，delete删除有返回值(能返回几条记录受影响) 5. truncate 删除不能回滚，delete删除可以回滚(事务内容)
5.3 约束
什么是约束：限制表中的数据，保证添加到数据表中的数据准确和可靠性！凡是不符合约束的数据，插入时就会失败！约束条件在创建表时可以使用， 也可以修改表的时候添加约束条件
六大约束条件：
- 非空约束，not null
- 唯一约束，unique
- 主键约束，primary key
- 外键约束，foreign key
- 默认约束，default，保证字段总会有值，即使没有插入值，都会有默认值！
- 自定义检查约束，check（不建议使用）(在mysql中现在还不支持)
5.3.1 创建表时如何添加约束
添加约束时有两种添加约束的方式：列级约束、表级约束
列级约束：六大约束语法上都支持，但外键约束没有效果
表级约束：除了非空，默认，其他都支持
-
使用列级约束
语法格式： 直接在字段名和类型后面追加约束类型即可。 注意：只支持：默认、非空、主键、唯一 create table stuinfo( id int primary key, #主键约束 stuName vatchar(20) not null, #非空约束 seat int default 18,#默认约束 ); -
使用表级约束
语法格式：在字段创建完后添加 【constrain 约束名】约束类型（字段名） 注意：【constrain 约束名】可以省略 create table stuinfo( id int , stuName vatchar(20) , gender char(1), seat int, age int, majorid int constraint pk primary key(id),#主键约束 constraint uq unique(seat),#唯一约束 constraint ck check(gender = '男' or gender ='女')，#检查约束 constraint fk_stuinfo_major foreign(majorid) key references major(id) #外键 ); create table major( id int primary key, majorName varchar(20) ) -
通用写法
主键、唯一、非空、默认、检查约束都建议使用列级约束，外键建议使用表级约束create table stuinfo( id int primary key, stuName vatchar(20) not null, sex char(1), age int default 18, seat int unique, majorid int, constraint fk_stuinfo_major foreign key(major ) references major(id) #外键 );
5.3.2 非空约束（not null）
什么是非空约束：用not null约束的字段不能为null值，必须给定具体的数据
-
案例
mysql> create table t_user( -> id int(10), -> name varchar(32) not null -> ); Query OK, 0 rows affected (0.08 sec) 如果没有插入name字段数据，则会报错 mysql> insert into t_user (id) values(1); ERROR 1364 (HY000): Field 'name' doesn't have a default value
5.3.3 唯一性约束（unique）
什么是唯一性约束： 唯一约束修饰的字段具有唯一性，不能重复。但可以为NULL。
注意：not null约束只有列级约束。没有表级约束。
-
案例1：使用列级约束添加
create table t_user( id int, username varchar(255) unique // 列级约束 ); 向表中添加数据 insert into t_user values(1,'zhangsan'); insert into t_user values(2,'zhangsan'); 当username相同时插入错误，此时表中username字段存在唯一性约束 ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'username' 虽然存在唯一约束，但是username可以为null insert into t_user(id) values(2); insert into t_user(id) values(3); insert into t_user(id) values(4)； -
案例2：添加表级约束
create table t_user( id int, usercode varchar(255), username varchar(255), unique(usercode,username) // 多个字段联合起来添加1个约束unique 【表级约束】 ); 插入数据 insert into t_user values(1,'111','zs'); insert into t_user values(2,'111','ls'); insert into t_user values(3,'222','zs'); insert into t_user values(4,'111','zs'); ERROR 1062 (23000): Duplicate entry '111-zs' for key 'usercode' 当多个字段联合使用unique时，只有当两个字段同时重复才能出错。（只出现一个重复不会错） drop table if exists t_user; create table t_user( id int, usercode varchar(255) unique, username varchar(255) unique ); insert into t_user values(1,'111','zs'); insert into t_user values(2,'111','ls'); ERROR 1062 (23000): Duplicate entry '111' for key 'usercode'
5.3.4 主键约束（primary key）
主键约束相关术语：
-
主键约束 : primary key
-
主键字段 : id字段添加primary key之后，id叫做主键字段
-
主键值 : id字段中的每一个值都是主键值。
主键有什么作用：
- 表的设计三范式中有要求，第一范式就要求任何一张表都应该有主键。(没有主键的表是无效的)
- 主键的作用：主键值是这行记录在这张表当中的唯一标识。（就像一个人的身份证号码一样。）
主键的分类：
根据主键字段的字段数量来划分：
- 单一主键（推荐的，常用的。）
- 复合主键(多个字段联合起来添加一个主键约束)（复合主键不建议使用，因为复合主键违背三范式。）
根据主键性质来划分
- 自然主键：主键值最好就是一个和业务没有任何关系的自然数。（这种方式是推荐的）
- 业务主键：主键值和系统的业务挂钩，例如：拿着银行卡的卡号做主键，拿着身份证号码作为主键。（不推荐用） 最好不要拿着和业务挂钩的字段作为主键。因为以后的业务一旦发生改变的时候，主键值可能也需要 随着发生变化，但有的时候没有办法变化，因为变化可能会导致主键值重复。
注意： 一张表的主键约束只能有1个。（必须记住）
主键和唯一性约束的区别：主键必须保证唯一性且不为空，唯一性：必须保证唯一性，但可以为空
| 保证唯一性 | 是否允许为空 | 一张表是否可以有多个 | |
|---|---|---|---|
| 主键 | 保证 | 否 | 否 |
| 唯一 | 保证 | 是 | 是 |
如何给一张表添加主键
使用列级约束添加主键：
create table t_user(
id int primary key, // 列级约束
username varchar(255),
email varchar(255)
);
insert into t_user(id,username,email) values(1,'zs','zs@123.com');
insert into t_user(id,username,email) values(2,'ls','ls@123.com');
insert into t_user(id,username,email) values(3,'ww','ww@123.com');
select * from t_user;
+----+----------+------------+
| id | username | email |
+----+----------+------------+
| 1 | zs | zs@123.com |
| 2 | ls | ls@123.com |
| 3 | ww | ww@123.com |
+----+----------+------------+
insert into t_user(id,username,email) values(1,'jack','jack@123.com');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
insert into t_user(username,email) values('jack','jack@123.com');
ERROR 1364 (HY000): Field 'id' doesn't have a default value
根据以上的测试得出：id是主键，因为添加了主键约束，主键字段中的数据不能为NULL，
也不能重复。
主键的特点：不能为NULL，也不能重复。
使用表级约束方式定义主键：
create table t_user(
id int,
username varchar(255),
primary key(id)
);
insert into t_user(id,username) values(1,'zs');
insert into t_user(id,username) values(2,'ls');
insert into t_user(id,username) values(3,'ws');
insert into t_user(id,username) values(4,'cs');
select * from t_user;
insert into t_user(id,username) values(4,'cx');
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'
以下内容是演示以下复合主键，不需要掌握：
drop table if exists t_user;
create table t_user(
id int,
username varchar(255),
password varchar(255),
primary key(id,username)
);
insert .......
如何设计自增长主键：在主键后添加auto_increment关键字
create table t_user(
id int primary key auto_increment, // id字段自动维护一个自增的数字，从1开始，以1递增
username varchar(255)
);
insert into t_user(username) values('a');
insert into t_user(username) values('b');
insert into t_user(username) values('c');
insert into t_user(username) values('d');
insert into t_user(username) values('e');
insert into t_user(username) values('f');
5.3.5 外键约束（foreign key）
外键约束的相关术语：
- 外键约束: foreign key
- 外键字段：添加有外键约束的字段
- 外键值：外键字段中的每一个值。
什么是外键 :若有两个表A、B，id是A的主键，而B中也有id字段，则id就是表B的外键，外键约束主要用来维护两个表之间数据的一致性。A为基本表，B为信息表
外键值可以为NULL:外键可以为NULL
外键字段引用其他表的某个字段的时候，被引用的字段必须是主键吗？
注意：被引用的字段不一定是主键，但至少具有unique约束。
注意：外键约束只有表级定义，但一张表可以有多个外键字段
实例：请设计数据库表，用来维护学生和班级的信息？
-
方案一：一张表存储所有数据
no(pk) name classno classname ---------------------------------------------------- 1 zs1 101 高三1班 2 zs2 101 高三1班 3 zs3 102 高三2班 4 zs4 102 高三2班 5 zs5 102 高三2班 缺点：表中有较多重复数据(数据冗余) -
方案二：两张表（班级表和学生表）
t_class 班级表 cno(pk) cname --------------------- 101 高三1班 102 高三2班 t_student 学生表 sno(pk) sname classno(该字段添加外键约束fk) -------------------------------- 1 zs1 101 2 zs2 101 3 zs3 102 4 zs4 102 5 zs5 102 t_student中的classno字段引用t_class表中的cno字段，此时t_student表叫做子表。 t_class表叫做父表。 建表语句： 先创建父表t_class create table t_class( cno int, cname varchar(255), primary key(cno) ); 在创建字表t_student create table t_student( sno int, sname varchar(255), classno int, primary key(sno), foreign key(classno) references t_class(cno) ); 先向父表中插入数据 insert into t_class values(101,'xxxxxxxx'); insert into t_class values(102,'yyyyyyyy'); 再向子表中插入数据 insert into t_student values(1,'zs1',101); insert into t_student values(2,'zs2',101); insert into t_student values(3,'zs3',102); insert into t_student values(4,'zs4',102); insert into t_student values(5,'zs5',102); insert into t_student values(6,'zs6',102); 当父表中不存在此数据，但在子表中插入时会出现如下错误 insert into t_student values(7,'lisi',103); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bjpowernode`.INT `t_student_ibfk_1` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))
注意：当设计存在外键关系的两张表时，应存在如下关系
- 删除数据的时候，先删除子表，再删除父表。
添加数据的时候，先添加父表，在添加子表。
创建表的时候，先创建父表，再创建子表。
删除表的时候，先删除子表，在删除父表。
第六章：事务(TCL语言)
事务：一个事务是一个完整的业务逻辑单元，不可再分。事务由单独单元的一个或多个SQL语句组成，在整个单元中，每个SQL语句都是相互依赖的。而整个单独单元作为一个不可分割的整体，如果单元中某条SQL语句一旦执行失败或产生错误，整个单元将会回滚。所有收到影响的数据将返回到事务开始之前的状态；若果单元中的所有SQL语句执行成功，则事务被顺利执行。
比如：银行账户转账，从A账户向B账户转账10000.需要执行两条update语句：
update t_act set balance = balance - 10000 where actno = 'act-001';
update t_act set balance = balance + 10000 where actno = 'act-002';
以上两条DML语句必须同时成功，或者同时失败，不允许出现一条成功，一条失败。
要想保证以上的两条DML语句同时成功或者同时失败，那么就需要使用数据库的“事务机制”
事务的属性 ACID:
- A: 原子性：一个事务是一个不可分割的工作单位。
- C: 一致性：事务必须使数据库从一个一致性状态变换到另一个一致性状态(事务必须保证多条DML语句同时成功或者同时失败。)
- I：隔离性：一个事务的执行不能被其他事务感染，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
- D：持久性：一个事务一旦被提交他对数据库中的数据的改变就是永久性的接下来的其他操作和数据库故障不应该对其有任何影响
6.1 事务的创建
事务包含两种方式：隐式事务、显示事务。
注意：mysql的事务是默认自动提交的
mysql事务默认情况下是自动提交的。
什么是自动提交？只要执行任意一条DML语句则提交一次。
怎么关闭自动提交？start transaction;
准备表：
drop table if exists t_user;
create table t_user(
id int primary key auto_increment,
username varchar(255)
);
* 演示：mysql中的事务是支持自动提交的，只要执行一条DML，则提交一次。
mysql> insert into t_user(username) values('zs');
Query OK, 1 row affected (0.03 sec)
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | zs |
+----+----------+
1 row in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t_user;
+----+----------+
| id | username |
+----+----------+
| 1 | zs |
+----+----------+
1 row in set (0.00 sec)
上述事例可以发现，mysql事务是自动提交的，默认执行一条DML语句就提交一次事务，无法采用rollback回滚
- 隐式事务：事务并没有明显的开启和结束的标记
如：insert，update、delete语句(当执行DML语句是其实就是开启一个事务) - 显式事务：事务有明显的开启和结束的标记
前提：必须先设置自动提交功能为禁用
Mysql开启事务的两种方式
- 方式一：采用begin或者start transcation开启事务。配套的提交语句是commit，回滚语句为rollback。
Begin或者start transcation：显式的开启一个事务
Commit：提交事务，将对数据库进行的所有的修改变成永久性
Rollback：结束用户的事务，并撤销现在正在进行的未提交的修改
注意：rollback，或者commit后事务就结束了。
语法格式：
步骤一：开启事务：begin或start transcation
步骤二：编写sql语句(select,insert update delete)
步骤三：提交事务：commit
事例1：
mysql> start transaction; #开启事务
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t_user(username) values('wangwu'); #插入数据
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_user(username) values('rose'); #插入数据
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_user(username) values('jack'); #插入数据
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_user; #查看数据
+----+----------+
| id | username |
+----+----------+
| 1 | zs |
| 4 | wangwu |
| 5 | rose |
| 6 | jack |
+----+----------+
4 rows in set (0.00 sec)
mysql> rollback; #回滚数据
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t_user; #查看数据
+----+----------+
| id | username |
+----+----------+
| 1 | zs |
+----+----------+
4 rows in set (0.00 sec)
-
方式二： SET 来改变 MySQL 的自动提交模式:
SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交语法格式： 步骤一： SET AUTOCOMMIT=0 步骤二：编写sql语句(select,insert update delete) 步骤三：提交事务：commit
两种创建事务方式的优缺点：
当你用 set autocommit=0 的时候，你以后所有的SQL都将做为事务处理，直到你用commit确认或rollback结束，注意当你结束这个事务的同时也开启了个新的事务！按第一种方法只将当前的作为一个事务！
6.2 事务的隔离级别
什么是事务的隔离：对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题。
理论上事务的隔离存在四个级别，分别对应了四个并发问题
- 读未提交(脏读，read uncommitted)：对于两个事务A,B，在两个事务下事务A读取到了已经被B更新但还没有提交的字段。此时当B回滚时，A读取到的内容就是临时且无效的。（对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。）
- 读已提交（不可重复读，read committed）：对于两个事务A,B，A读取了一个字段，然后B更新了该字段。此时当A再次读取到同一字段是，值就不同了。（对方事务提交之后的数据我方可以读取到）
- 可重复读(幻读，repeatable read)：对于两个事务A,N，A事务读取了一个字段，然后B更新了该字段，此时若A再次读取到统一字段是，值没有更新。(确保一个事务可以多次从一个字段中读取相同的值，在这个事务持续时间内，禁止其他事务对这个字段更新)
- 序列化读/串行化读（serializable）：确保事务可以从一个表中读取到相容的数据，在这个事务执行期间，禁止其他事务对该表执行插入，更新和删除操作，所有并发问题可以避免，但性能十分低下。
注意：1. 数据库系统必须具有隔离并发运行各个事务的能力，使他们不会互相影响，避免各种并发影响。2. 数据库规定了多种事务隔离级别，不同隔离级别对应不同干扰程度，隔离级别越高，数据一致性就越好，但并发性越弱。
oracle数据库默认的隔离级别是：读已提交。
mysql数据库默认的隔离级别是：可重复读。
oracle中支持：读已提交和串行读
mysql数据库支持：4中隔离级别都支持。
6.3 事务隔离级别的演示
6.3.1 读未提交（read uncommitted）
- 步骤1：分别准备两个窗口，登录mysql，并使用数据库bjpowernode
- 步骤2：设置事务的全局隔离级别：set global transaction isolation level read 隔离级别;
此时可以使用：select @@transaction_isolation;查看事务的全局隔离级别
- 步骤3：启动事务（使用begin,star transaction等）
- 步骤4：如下图所示：在事务A中查看表t_user中的数据，在事务B中插入数据 insert into t_user(username) values(‘SMITH’);，此时再在事务A中查看表t_user中的数据，发现可以查看到事务B没有提交后的内容。
第七章：数据库设计三范式
什么是数据库设计三范式：关系数据库中的关系必须满足一定的要求。满足不同程度要求的为不同范式。数据库的设计范式是数据库设计所需要满足的规范。只有理解数据库的设计范式，才能设计出高效率、优雅的数据库，否则可能会设计出错误的数据库.
第一范式(1NF)：当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。（任何一张表都应该有主键，并且每一个字段原子性不可再分。）
第二范式(2NF)：如果关系模式R是1NF，且每个非主属性完全函数依赖于候选键，那么就称R是第二范式。（建立在第一范式的基础之上，所有非主键字段完全依赖主键，不能产生部分依赖。）
第三范式(3NF)：如果关系模式R是2NF，且关系模式R（U，F）中的所有非主属性对任何候选关键字都不存在传递依赖，则称关系R是属于第三范式。（建立在第二范式的基础之上，所有非主键字段直接依赖主键，不能产生传递依赖）
7.1 三范式实例
第一范式：每一个表都需要有主键，每一个字段的具有原子性,不可再分。
如下图所示：该设计不符合1NF，存在以下几个问题
-
最后一条记录和第一条重复（不唯一，没有主键）
-
联系方式字段可以再分，不是原子性的
根据第一范式的设计要求，可以将上表改为：
关于第一范式，每一行必须唯一，也就是每个表必须有主键，这是我们数据库设计的最基本要求
第二范式：有主键，非主键字段完全依赖主键，不能产生部分依赖。
不符合第二范式的示例：
-
虽然满足第一范式，但是产生了部分依赖
上述表中满足了1NF设计要求(有主键，且所有非主键字段不可再分)，
不建议使用复合主键的原因就在此，因为无法确定是否产生部分依赖
这张表不符合第二范式，这是因为学生姓名字段依赖了学生编号主键，但是没有依赖教师编号主键，教师姓名依赖了教师编号主键，没有依赖学生编号主键，产生了部分依赖根据第二范式的要求，可以对上表做如下处理：由于学生和老师之间的关系是多对多关系，我们可以使用三张表结构。
-
第一张表：学生表
-
第二张表：教师表
-
第三张表：关系表
第三范式：建立在第二范式的基础之上，所有非主键字段直接依赖主键，不能产生传递依赖。
不符合第三范式的示例：
上表满足第一和第二范式，但不满足第三范式。其班级名称字段存在冗余，因为班级名称字段没有直接依赖于主键，班级名称字段依赖于班级编号，而班级编号则依赖于学生编号，传递依赖。
解决方案：一对多？两张表，多的表加外键。
提醒：在实际的开发中，以满足客户的需求为主，有的时候会拿冗余换执行速度。
扫一扫
8056
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
