第七周

一、数据库的发展史
(1)手工管理:藏书阁，图书馆。
优点:分类管理，直观性强
缺点:信息流动慢，不方便
(2)文件管理:计算机文件系统，图书管理系统
优点：分类管理，层次分明
缺点: 查找不方便
(3)数据库管理:
优点:存取数据非常方便.
缺点:有数据的安全隐患。
二、数据库概念
数据库(DataBase),简称DB，按照某一特定数据结构来组织，存储
数据的仓库。
数据库管理系统(DataBase Management System),简称DBMS.
是为了建立，操作和维护数据库
而设计的一个程序。介于用户和数据库之间。
PS: 使用SQL语言，通过DBMS来管理DB。
三、关系型数据库
(1)所谓的关系，指的就是表，或者表与表之间。关系模型，就是表的
行与列。比如 教师(教师编号，姓名，年龄，性别，科目)。
(2)常用的关系型数据库：
大型:Oracle–甲骨文(Oracle)公司的产品
SqlServer–Microsoft旗下的产品
DB2–IBM旗下的产品
小型:Access–MicroSoft旗下的产品
Mysql–目前是甲骨文旗下的产品
四、Mysql
1)Mysql是一个关系型数据库，最开始是瑞典的MysqlAB公司的产品，后
来被Sun公司收购，在2009年4月20号Sun公司被Oracle收购.
2)Mysql特点
–mysql是开源的，不会产生额外的费用。
–mysql支持大型数据库，比如可以管理上千万条记录的数据库。
–mysql支持多操作系统，支持多语言连接：C，C++,java,PHP….
–mysql成本比较小，相比较Oracle和DB2。
3)Mysql的安装(略)
服务端的安装：DB和DBMS的安装
客户端的安装：自带的客户端
第三方客户端
4)Mysql的连接和使用
连接方式：
(1)使用命令提示符界面(保证环境变量配置成功)

  第一步：输入命令+参数，发送请求连接
  mysql -u username -p   回车
  enter password:  ......
  第二步：创建数据库
  create database bd1802 default character set utf8;
  第三步:选择数据库
  use  bd1802


(2)使用客户端连接:(选择sqldeveloper).
   第一步:配置第三方jdbc驱动程序
      工具->首选项->数据库->第三方驱动程序->
      添加条目->选中你的jar包->确定
   第二步：新建连接

   连接名：随便起，就是一个连接昵称而已
   用户名：使用什么用户进行连接
   密码：.....
   主机名: 数据库所在的主机IP
   端口号:3306
   选择数据库：如 bd1802

五、表(Table)
表是关系型数据库的基本存储结构。
1)表是二维数据结构，有行和列
2)行(Row)是横排数据，也叫记录(Recond)
3)列(Column)是竖排数据，也叫字段(Field)
4)行与列的交叉点是 字段值
5)表与表之间也存在关系
六、数据库支持的数据类型
MySQL支持多种类型，大致可以分为三类：
数值、日期/时间和字符串(字符)类型。

数值类型：

类型 大小 范围（有符号） 用途
TINYINT 1 字节 (-128，127) 小整数值
SMALLINT 2 字节 (-32 768，32 767) 大整数值
MEDIUMINT 3 字节 (-8 388 608，8 388 607) 大整数值
INT或INTEGER 4 字节 (-2 147 483 648，2 147 483 647) 大整数值
BIGINT 8 字节 (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) 极大整数值
FLOAT 4 字节 单精度
浮点数值
DOUBLE 8 字节 双精度
浮点数值
DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值

日期和时间类型：
类型 大小(字节) 范围 格式 用途
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3 ‘-838:59:59’/’838:59:59’ HH:MM:SS 时间值或持续时间
YEAR 1 1901/2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 8 1970-01-01 00:00:00/2037 年某时 YYYYMMDD HHMMSS 混合日期和时间值，时间戳

字符串类型：
类型 大小 用途
CHAR 0-255字节 定长字符串 char(10)
VARCHAR 0-65535 字节 变长字符串
TINYBLOB 0-255字节 不超过 255 个字符的二进制字符串
TINYTEXT 0-255字节 短文本字符串
BLOB 0-65 535字节 二进制形式的长文本数据
TEXT 0-65 535字节 长文本数据
MEDIUMBLOB 0-16 777 215字节 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215字节 中等长度文本数据
LOGNGBLOB 0-4 294 967 295字节 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295字节 极大文本数据

七、结构化查询语言(SQL)
结构化查询语言(Structured Query Language),简称SQL，是数据库
的标准查询语言。可以通过DBMS对数据库进行定义数据，操纵数据
查询数据，数据控制等
SQL可以分为:
1)数据定义语言(DDL):Data dafinitaon Language
如创建表create
删除表drop
修改表alter
清空表truncate，彻底清空，无法找回。
2)数据操纵语言(DML):Data M……. Language
如插入数据insert
删除数据delete
修改数据update
3)事物控制语言(TCL):Transation Control Language
4)数据查询语言(DQL):Data Query Language
select:
select * from tableName;

5)数据控制语言(DCL):Data Control Language

小知识：
修改命令提示符界面显示编码集：set names GBK;
结构化查询语言(SQL)
是数据库管理系统用于管理数据库的标准查询语言。
用于对数据库进行定义数据，操纵数据，事物管理等

细分五种语言：
DDL:数据定义语言，里面有
create:创建表结构
drop: 删除表
alter: 修改表结构
truncate: 清空表结构,即清空表数据，不能回滚。
DML:数据操纵语言，里面有
insert:向表中插入数据
delete:删除表中的数据
update:修改表中的数据

where 条件: 对表中的数据加条件进行限制，
起到过滤的作用。

格式: where colName 关系运算符 value
[or|and 条件2]

   关系运算符:
      >,>=,<,<=,
  等于：=,
  不等于：!= 或 <>

null值操作:
1：在条件中，不能使用=或者!= 或者<>，而是使用
is或者is not
2:在select子句中，使用关系运算符
DQL:数据查询语言。
–select:选择
一、基本查询语句：至少要有两个子句。
1、
select子句和from子句。
格式1：可以指定字段进行查询
select colName[,colName…..] from tableName
格式2:查询整张表：
select * from tableName;
通配符*:如果是在select字句中，代替的是所有的字段。

2、给查询显示的列，起别名。
格式:
select ename [as] “员工姓名”[,colName as nickName….] from tableName tableNickName

3、where子句：
作用:起到条件限制约束等操作。有过滤的作用。
位置:放在 from子句后。
格式:
select colName from tableName where 条件。

条件:关系运算符：

,>=,<,<=,=,!=,<>。

(1)null在条件中，应该使用is 或者is not

(2)范围查询:针对于某一个字段来说。
1：可以使用>,>=和<,<=来进行范围限制
2: 还可以使用between and来限制。
格式：
colName [not] between val1 and val2;
注意: between val1 and val2 相当于
闭区间[val1,val2]
(3)集合作为条件写法：同一个字段有多个值的情况下使用。
使用:in|not in
格式: colName [not] in (元素1，元素2，….);
注意:in 相当于 or
not in 相当于 and

集合的其他用法：
注意:mysql数据库中，集合元素的来源
只能基于一张表的查询。不能写定值。
all与any
格式:

all(元素1,元素2,元素3……)
:大于集合中最大的元素。

位置：位于group by子句后

5、子句总结：
一个基本的查询语句包含的子句有：
select子句
from子句
where子句
group by子句
having子句
order by子句
一个完整的查询语句：
select..from..[where..][group by..][having..][order by..]

执行顺序：
先执行from子句。
再执行where子句
然后group by子句
再次having子句
之后select子句

最后order by子句

6、SQL语言之TCL:事务控制语言.
有时我们可能需要使用DML进行批量数据的删除，修改，
增加。比如，在一个员工系统中，我们想删除一个人的信息。
除了删除这个人的基本信息外，还应该删除与此人有关的
其他信息，如邮箱，地址等等。那么从开始执行到结束
就会构成一个事务。
对于事务，我们要保证事务的完整性。要么成功。
要么撤回。

一般情况下事务要符合四个条件(ACID)：
原子性(Atomicity)：
事务要么成功，要么撤回。不可切割性。
一致性(Consistency)：
事务开始前和结束后，要保证数据的一致性。
转账前账号A和账号B的钱的总数为10000;
转账后账号A和账号B的前的总数应该还是10000;
隔离性(Isolation):
当涉及到多用户操作同一张表时，数据库为会
每一个用户开启一个事务。那么当其中一个事务正在
进行时，其他事务应该处于等待状态。保证事务之间
不会受影响。
持久性(Durability):
当一个事务被提交后，我们要保证数据库里的
数据是永久改变的。即使数据库崩溃了，我们也要
保证事务的完整性。

commit:提交
rollback:撤回，回滚。
savepoint:保存点

事务:只有DML操作会触发一个事务。
存储引擎(ENGINE):就是指表类型.
当存储引擎为innodb时，才支持事务。
有一个默认的存储引擎为Myisam。不支持事务。

事务的验证：
第一步:start transaction
第二步:savepoint 保存点名称。
第三步:DML

第四步:commit/rollback;

SQL之DCL：数据控制语言
作用是用来创建用户，给用户授权，撤销权限，删除用户。
create user
grant
revoke
drop user

格式:
1：创建用户
create user username@ip identified by newPwd;

2: 显示用户的权限
show grants for username@ip;
3：授权
grant 权限1，权限2… on 数据库名.* to username@ip;
DML权限：insert,delete,update
DQL权限：select
DDL权限：create,alter,drop…

4：撤销权限:
revoke 权限1，权限2..on 数据库名.* from username@ip;

5: 删除用户
drop user username;

使权限立即生效：

flush privileges;

关联查询：
有的时候，我们查询的数据不在一张表中，可能
会涉及两张表，或多张表，此时我们使用关联查询。

格式:
select * from tname1,tname2;

笛卡尔积:在做多张表查询时，出现的记录数目为
这些表的记录数的乘积。
通常，笛卡尔积没有价值(数据冗余)。不需要。

     t1 有10条记录
 t2 有20条记录

 查询的数目为：10*20;

等值查询：两张表的字段有关系的查询,做等值连接
需要使用where条件。

使用表别名:
select e.ename “员工姓名”,e.deptno “部门号” from emp e,dept d where e.deptno=d.deptno;
DQL的基本查询语句

select..from..where..group by..having..order by..
执行逻辑:
1.from
2.where
3.group by
4.having
5.select
6.order by

分组查询：当统计一张表或者按照表内某些字段再进行分组时。
总记录数，或者是最大值，最小值，
平均值，总和等.
一定会与分组函数(聚合函数)连用.
count(field),
sum(field)
max(field)
min(field)

avg(field)–ifnull(field,value)

关联查询的基础
1、概念：
当在查询时，我们所需要的数据不在一张表中，可能在
两张表或多张表中。此时我们需要同时操作这些表。即
关联查询。
2、等值连接:
在做多张表查询时，这些表中应该存在着有关联的两个字段。
我们使用某一张表中的一条记录与另外一张表通过相关联
的两个字段进行匹配，组合成一条记录。
3、笛卡尔积：
在做多张表查询时，我们使用某一张表中的每一条记录都与
另外一张表的所有记录进行组合。比如表A有x条，表B
有y条件，最终组合数为x*y，这个值就是笛卡尔积，通常
没有意义。
4、内连接
只要使用了join on。就是内连接。查询效果与等值连接一样。
用法:
表A [inner] join 表B on 关联条件
5、外连接:
在做多张表查询时，我们所需要的数据，除了满足关联条件
的数据外，还有不满足关联条件的数据。此时需要使
用外连接。会涉及到两个概念：
驱动表(主表):
除了显示满足条件的数据，还需要显示不满足条件
的数据的表
从表(副表):只显示满足关联条件的数据的表

外连接分为三种:
左外连接:
表A left [outer] join 表B on 关联条件。
表A是驱动表，表B是从表

右外连接
表A right [outer] join 表B on 关联条件
表B是驱动表，表A是从表
全外连接：
两张表的数据不管满不满足条件，都做显示。
表A full [outer] join 表B on 关联条件
PS:mysql 不支持全外连接
6、自连接:
在多张表进行关联查询时，这些表的表名是同一个。
即自连接。

高级关联查询：
有的时候，我们要查询的数据， 一个简单的查询语句
满足不了，并且我们使用的数据，表中不能直观体现出来。
而是预先经过一次查询才会有所体现。那么先执行的
查询，我们称之子查询。被子查询嵌入的查询语句称之
为父查询。

比如需求:查询工资大于员工JONES工资的员工的信息。
子查询返回的数据特点：
(1) 可能是单行单列的数据。
(2) 可能是多行单列的数据
(3) 可能是单行多列的数据
(4) 可能是多行多列的数据

1)子查询可以在where子句中
2)子查询可以在from子句中
3)子查询可以在having子句中
4)子查询可以在select字句中，相当于外连接的另外一种写法。

约束Constraint:
对插入表中字段的数据起到一定的条件限制。

约束分五种:
主键约束：primary key
要求作为主键的字段的字段值非空且唯一

非空约束: not null
要求:有非空约束的字段不可以为null值。
唯一性约束:unique
要求有唯一性约束的字段不可以重复，但是可以为null.
检查性约束: check(条件)— check(gender in(‘f’,’m’))
要求在插入数据时必须符合字段的检查条件,但是可以为null
外键约束： foreign key
要求:有外键约束的字段A必须依赖于另外一个字段B，
字段B要有主键约束。
字段A的值，要么是null,
要么必须是字段B里的值。

序列：作为主键的字段，通常不是表中的主要信息，可以用来
当成信息记录的序号。序号最好是有序的序列。

 auto_increment 关键字，用来对有主键约束的字段
 做自增操作。

用法:
create table tname(
tid int primary key auto_increment,
……
);

函数:last_insert_id();
作用：获取序列最后一次的值。
select last_insert_id();