数据库
- 数据库：
英文单词DataBase，简称DB。按照一定格式存储数据的一些文件的组合。
顾名思义：存储数据的仓库，实际上就是一堆文件。这些文件中存储了具有特定格式的数据。 - 数据库管理系统：
DataBaseManagement，简称DBMS。
数据库管理系统是专门用来管理数据库中数据的，数据库管理系统可以对数据库当中的数据进行增删改查。 - 常见的数据库管理系统：
MySQL、Oracle、MS SqlServer、DB2、sybase等…
SQL：结构化查询语言
Structured Query Language，SQL语句不区分大小写，所有的SQL语句以“;”结尾。
SQL语句的分类
- DQL：
数据查询语言（凡是带有select关键字的都是查询语句）
select… - DML：
数据操作语言（凡是对表当中的数据进行增删改的都是DML）
insert delete update
insert 增
delete 删
update 改
这个主要是操作表中的数据data。 - DDL：
数据定义语言
凡是带有create、drop、alter的都是DDL。
DDL主要操作的是表的结构。不是表中的数据。
create：新建，等同于增
drop：删除
alter：修改
这个增删改和DML不同，这个主要是对表结构进行操作。 - TCL：
是事务控制语言
包括：
事务提交：commit;
事务回滚：rollback; - DCL：
是数据控制语言。
例如：授权grant、撤销权限revoke…
MySQL常用命令
-
退出mysql ：exit;
-
查看mysql中有哪些数据库 : show databases; #注意：以分号结尾，分号是英文的分号。
mysql> show databases; -
选择使用某个数据库:
mysql> use test;
Database changed
表示正在使用一个名字叫做test的数据库。 -
创建数据库:
mysql> create database bjpowernode;
Query OK, 1 row affected (0.00 sec) -
查看某个数据库下有哪些表:
mysql> show tables; -
查看mysql数据库的版本号：
mysql> select version(); -
查看当前使用的是哪个数据库:
mysql> select database(); +-------------+ | database() | +-------------+ | bjpowernode | +-------------+ -
将sql文件中的数据导入
mysql> source xxxx.sql #注意：路径中不要有中文！ -
查看表的结构：
mysql> desc dept; # describe缩写为：desc
数据库表
-
数据库当中最基本的单元是表：table
-
什么是表table？为什么用表来存储数据呢？
姓名 性别 年龄(列：字段) --------------------------- 张三 男 20 ------->行（记录） 李四 女 21 ------->行（记录） 王五 男 22 ------->行（记录）数据库当中是以表格的形式表示数据的。因为表比较直观。
-
任何一张表都有行和列：
行（row）：被称为数据/记录。
列（column）：被称为字段。 -
每一个字段都有：字段名、数据类型、约束等属性。字段名可以理解，是一个普通的名字，见名知意就行。
数据类型：字符串，数字，日期等，后期讲。
约束：约束也有很多，其中一个叫做唯一性约束，这种约束添加之后，该字段中的数据不能重复。
查询
-
简单查询
-
select 字段名 from 表名;
-
给查询的列起别名：使用as关键字起别名，as可以省略，别名有空格的话，使用引号；
select deptno,dname as deptname from dept;
-
-
条件查询
- 查询语法格式： select 字段1,字段2,字段3… from 表名 where 条件;
- 都有哪些条件？
- = 等于
- <> 或!= 不等于
- < 小于
- <= 小于等于
- > 大于
- >= 大于等于
- between … and …. 两个值之间, 等同于 >= and <=
- is null 为 null（is not null 不为空），在数据库当中null不能使用等号进行判断。需要使用is null因为数据库中的null代表什么也没有，它不是一个值，所以不能使用等号判断。
- and 并且
- or 或者，and和or同时出现，and优先级较高。如果想让or先执行，需要加“小括号”。
- in 包含，相当于多个 or （not in 不在这个范围中）
- not 可以取非，主要用在 is 或 in 中
- like 称为模糊查询，支持%或下划线匹配 ， %匹配任意多个字符 ，下划线：任意一个字符。 （%是一个特殊的符号，_ 也是一个特殊符号）
分组查询
在实际的应用中，可能有这样的需求，需要先进行分组，然后对每一组的数据进行操作，这个时候我们需要使用分组查询。
-
语法格式： select … from … group by …
-
在一条select语句当中，如果有group by语句的话，select后面只能跟：参加分组的字段，以及分组函数。其它的一律不能跟。
-
找出“每个部门，不同工作岗位”的最高薪资？ 两个字段联合成1个字段看。（两个字段联合分组）
select deptno, job, max(sal) from emp group by deptno, job; -
使用having可以对分完组之后的数据进一步过滤。
- having不能单独使用，having不能代替where，having必须和group by联合使用。
- where和having，优先选择where，where实在完成不了了，再选择having。
排序
-
语法格式：
select
ename,sal
from
emp
order by
sal; // 默认是升序！
指定降序： desc
指定升序： asc -
以两个字段排序吗？或者说按照多个字段排序？
查询员工名字和薪资，要求按照薪资升序，如果薪资一样的话，再按照名字升序排列。
select
ename,sal
from
emp
order by
sal asc, ename asc; // sal在前，起主导，只有sal相等的时候，才会考虑启用ename排序。
单行处理函数
-
一个输入对应一个输出。
-
单行处理函数常见:
-
lower 转换小写
mysql> select lower(ename) as ename from emp; -
upper 转换大写
mysql> select upper(name) as name from t_student; -
substr 取子串（substr(被截取的字符串, 起始下标,截取的长度)）
mysql> select substr(ename, 1, 1) as ename from emp; -
concat函数进行字符串的拼接
mysql> select concat(empno,ename) from emp; -
length 取长度
mysql> select length(ename) enamelength from emp; -
trim 去空格
mysql> select * from emp where ename = trim(' KING'); -
str_to_date 将字符串转换成日期
-
date_format 格式化日期
-
format 设置千分位case…when…then…when…then…else…end
当员工的工作岗位是MANAGER的时候，工资上调10%，当工作岗位是SALESMAN的时候，工资上调50%,其它正常。 （注意：不修改数据库，只是将查询结果显示为工资上调）select ename,job, sal as oldsal, (case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal from emp; -
case…when…then…when…then…else…end
当员工的工作岗位是MANAGER的时候，工资上调10%，当工作岗位是SALESMAN的时候，工资上调50%,其它正常。 （注意：不修改数据库，只是将查询结果显示为工资上调）select ename,job, sal as oldsal, (case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal from emp; -
round 四舍五入
mysql> select round(1236.567, 0) as result from emp; //保留整数位。 mysql> select round(1236.567, 1) as result from emp; //保留1个小数 mysql> select round(1236.567, 2) as result from emp; //保留2个小数 mysql> select round(1236.567, -1) as result from emp; // 保留到十位。 -
rand() 生成随机数
mysql> select round(rand()*100,0) from emp; // 100以内的随机数 -
ifnull 可以将 null 转换成一个具体值
ifnull是空处理函数。专门处理空的。在所有数据库当中，只要有NULL参与的数学运算，最终结果就是NULL。
注意：NULL只要参与运算，最终结果一定是NULL。为了避免这个现象，需要使用ifnull函数。ifnull函数用法：ifnull(数据, 被当做哪个值)。如果“数据”为NULL的时候，把这个数据结构当做哪个值。
-
多行处理函数
count # 计数
sum # 求和
avg # 平均值
max # 最大值
min # 最小值
- 分组函数在使用的时候必须先进行分组，然后才能用。如果你没有对数据进行分组，整张表默认为一组。
- 分组函数自动忽略NULL，你不需要提前对NULL进行处理。
- 分组函数中count()和count(具体字段)有什么区别？
count(具体字段)：表示统计该字段下所有不为NULL的元素的总数。
count()：统计表当中的总行数。（只要有一行数据count则++）
因为每一行记录不可能都为NULL，一行数据中有一列不为NULL，则这行数据就是有效的。 - 分组函数不能够直接使用在where子句中。
- 所有的分组函数可以组合起来一起用。
去重
distinct
distinct只能出现在所有字段的最前方。
mysql> select distinct job from emp;
distinct出现在job,deptno两个字段之前，表示两个字段联合起来去重。
mysql> select distinct job,deptno from emp;
连接查询
多张表联合起来查询数据，被称为连接查询。
-
内连接
-
等值连接
select ... from a inner join b on a和b的连接条件 where 筛选条件 # inner可以省略 -
非等值连接
select e.ename, e.sal, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal; // 条件不是一个等量关系，称为非等值连接。 -
自连接
select a.ename as '员工名', b.ename as '领导名' from emp a join emp b on a.mgr = b.empno; //员工的领导编号 = 领导的员工编号 以上就是内连接中的：自连接，技巧：一张表看做两张表
-
-
外连接
// outer是可以省略的 select e.ename,d.dname from emp e right outer join dept d on e.deptno = d.deptno; right表示将join关键字右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表。在外连接当中，两张表连接，产生了主次关系。 三张表，四张表怎么连接？ 语法： select ... from a join b on a和b的连接条件 join c on a和c的连接条件 right join d on a和d的连接条件 一条SQL中内连接和外连接可以混合。都可以出现！
子查询
select语句中嵌套select语句，被嵌套的select语句称为子查询。
-
子查询都可以出现在哪里呢？
select
…(select).
from
…(select).
where
…(select). -
where子句中的子查询
第一步：查询最低工资是多少 select min(sal) from emp; +----------+ | min(sal) | +----------+ | 800.00 | +----------+第二步：找出>800的 select ename,sal from emp where sal > 800;第三步：合并 select ename,sal from emp where sal > (select min(sal) from emp); -
from子句中的子查询
from后面的子查询，可以将子查询的查询结果当做一张临时表。
-
对于select后面的子查询来说，这个子查询只能一次返回1条结果
union合并
案例：查询工作岗位是MANAGER和SALESMAN的员工？
select ename,job from emp where job = 'MANAGER'
union
select ename,job from emp where job = 'SALESMAN';
对于表连接来说，每连接一次新表，
则匹配的次数满足笛卡尔积，成倍的翻
但是union可以减少匹配的次数。在减少匹配次数的情况下，
还可以完成两个结果集的拼接。
a 连接 b 连接 c
a 10条记录
b 10条记录
c 10条记录
匹配次数是：1000
a 连接 b一个结果：10 * 10 --> 100次
a 连接 c一个结果：10 * 10 --> 100次
使用union的话是：100次 + 100次 = 200次。（union把乘法变成了加法运算）
注意：
1. union在进行结果集合并的时候，要求两个结果集的列数相同。
2. 结果集合并时列和列的数据类型也要一致。
limit
-
limit startIndex, length
startIndex是起始下标，length是长度。 起始下标从0开始。
-
翻页：limit (pageNo-1)*pageSize , pageSize
DQL语句的执行顺序问题
select
...
from
...
where
...
group by
...
having
...
order by
...
limit
...
执行顺序？
1.from
2.where
3.group by
4.having
5.select
6.order by
7.limit..
表的创建
-
建表的语法格式：(建表属于DDL语句，DDL包括：create drop alter)
-
create
create table 表名( 字段名1 数据类型, 字段名2 数据类型, 字段名3 数据类型 ); 创建一个学生表？ 学号、姓名、年龄、性别、邮箱地址 create table t_student( no int, name varchar(32), sex char(1), age int(3), email varchar(255) ); -
mySQL数据类型
-
varchar(最长255) 可变长度的字符串 比较智能，节省空间。 会根据实际的数据长度动态分配空间。
优点：节省空间 缺点：需要动态分配空间，速度慢。
-
char(最长255) 定长字符串 不管实际的数据长度是多少。 分配固定长度的空间去存储数据。 使用不恰当的时候，可能会导致空间的浪费。
优点：不需要动态分配空间，速度快。 缺点：使用不当可能会导致空间的浪费。
-
int(最长11) 数字中的整数型。等同于java的int。
-
bigint 数字中的长整型。等同于java中的long。
-
float 单精度浮点型数据。
-
double 双精度浮点型数据。
-
date 短日期类型。
-
datetime 长日期类型。
-
clob 字符大对象 最多可以存储4G的字符串。 比如：存储一篇文章，存储一个说明。 超过255个字符的都要采用CLOB字符大对象来存储。 Character Large OBject:CLOB。
-
blob 二进制大对象 Binary Large OBject 专门用来存储图片、声音、视频等流媒体数据。 往BLOB类型的字段上插入数据的时候，例如插入一个图片、视频等， 你需要使用IO流才行。
-
-
删除表：
drop table t_student; // 当这张表不存在的时候会报错！// 如果这张表存在的话，删除
drop table if exists t_student;
插入数据
-
插入数据insert （DML）
-
语法格式：
insert into 表名(字段名1,字段名2,字段名3…) values(值1,值2,值3); -
注意：字段名和值要一一对应。什么是一一对应？
数量要对应。数据类型要对应。 -
insert语句中的“字段名”可以省略吗？可以
insert into t_student values(2); //错误的 #注意：前面的字段名省略的话，等于都写上了！所以值也要都写上！ insert into t_student values(2, 'lisi', 'f', 20, 'lisi@123.com'); -
一次可以插入多条记录：
# 语法：insert into t_user(字段名1,字段名2) values(),(),(),(); insert into t_user(id,name,birth,create_time) values (1,'zs','1980-10-11',now()), (2,'lisi','1981-10-11',now()), (3,'wangwu','1982-10-11',now());
-
格式化：format
-
格式化数字：format(数字, ‘格式’)
select ename,format(sal, '$999,999') as sal from emp; -
格式化日期：
str_to_date：将字符串varchar类型转换成date类型
date_format：将date类型转换成具有一定格式的varchar字符串类型。insert into t_user(id,name,birth) values(1, 'zhangsan', str_to_date('01-10-1990','%d-%m-%Y'));如果你提供的日期字符串是mysql默认的日期格式：’%Y-%m-%d’，则不需要使用这个函数；
insert into t_user(id,name,birth) values(2, 'lisi', '1990-10-01');mysql的日期格式：
%Y 年
%m 月
%d 日
%h 时
%i 分
%s 秒 -
查询的时候可以以某个特定的日期格式展示吗？
date_format
这个函数可以将日期类型转换成特定格式的字符串。select id,name,date_format(birth, '%m/%d/%Y') as birth from t_user; -
java中的日期格式？
yyyy-MM-dd HH:mm:ss SSS -
mysql短日期默认格式：%Y-%m-%d
mysql长日期默认格式：%Y-%m-%d %h:%i:%s
-
-
修改
-
语法格式：
update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3… where 条件; -
没有条件限制会导致所有数据全部更新。
update t_user set name = 'jack', birth = '2000-10-11' where id = 2; update t_user set name = 'jack', birth = '2000-10-11', create_time = now() where id = 2; -
更新所有？
update t_user set name = ‘abc’;
删除数据
-
语法格式？
delete from 表名 where 条件; -
没有条件，整张表的数据会全部删除！
delete from t_user where id = 2; insert into t_user(id) values(2); delete from t_user; # 删除所有！ -
快速删除表中的数据？【truncate比较重要，必须掌握】
//删除dept_bak表中的数据 delete from dept_bak; //这种删除数据的方式比较慢。 mysql> select * from dept_bak; Empty set (0.00 sec) -
delete语句删除数据的原理？（delete属于DML语句！）
表中的数据被删除了，但是这个数据在硬盘上的真实存储空间不会被释放！
这种删除缺点是：删除效率比较低。
这种删除优点是：支持回滚，后悔了可以再恢复数据！ -
truncate语句删除数据的原理？
这种删除效率比较高，表被一次截断，物理删除。
这种删除缺点：不支持回滚。
这种删除优点：快速。 -
用法：truncate table dept_bak; （这种操作属于DDL操作。）
-
大表非常大，上亿条记录？？？？
删除的时候，使用delete，也许需要执行1个小时才能删除完！效率较低。
可以选择使用truncate删除表中的数据。只需要不到1秒钟的时间就删除结束。效率较高。
但是使用truncate之前，必须仔细询问客户是否真的要删除，并警告删除之后不可恢复！ -
truncate是删除表中的数据，表还在！
-
删除表操作？
drop table 表名; // 这不是删除表中的数据，这是把表删除
约束
-
什么是约束？
约束对应的英语单词： constraint
在创建表的时候，我们可以给表中的字段加上一些约束，来保证这个表中数据的完整性、有效性！ -
约束的作用就是为了保证：表中的数据有效！
-
约束包括哪些？
-
非空约束：not null
drop table if exists t_vip; create table t_vip( id int, name varchar(255) not null // not null只有列级约束，没有表级约束！ ); -
唯一性约束: unique
唯一性约束unique约束的字段不能重复，但是可以为NULL。
drop table if exists t_vip; create table t_vip( id int, name varchar(255) unique, email varchar(255) ); 如何做到联合唯一约束 drop table if exists t_vip; create table t_vip( id int, name varchar(255), email varchar(255), unique(name,email) // 约束没有添加在列的后面，这种约束被称为表级约束。 ); name和email两个字段联合起来唯一 在mysql当中，如果一个字段同时被not null和unique约束的话，该字段自动变成主键字段。 -
主键约束: primary key （简称PK）
-
主键值是每一行记录的唯一标识。
-
任何一张表都应该有主键，没有主键，表无效。
-
主键的特征：not null + unique（主键值不能是NULL，同时也不能重复！）
-
给一张表添加主键约束
drop table if exists t_vip; // 1个字段做主键，叫做：单一主键 create table t_vip( id int primary key, //列级约束 name varchar(255) ); -
表级约束主要是给多个字段联合起来添加约束
drop table if exists t_vip; // id和name联合起来做主键：复合主键！ create table t_vip( id int, name varchar(255), email varchar(255), primary key(id,name) ); -
主键值建议使用： int bigint char 等类型。
-
主键只要做到不重复就行，不需要有意义。 业务主键不好，因为主键一旦和业务挂钩，那么当业务发生变动的时候， 可能会影响到主键值，所以业务主键不建议使用。尽量使用自然主键。
-
在mysql当中，有一种机制，可以帮助我们自动维护一个主键值
drop table if exists t_vip; create table t_vip( id int primary key auto_increment, //auto_increment表示自增，从1开始，以1递增！ name varchar(255) );
-
-
外键约束：foreign key（简称FK）
-
检查约束：check（mysql不支持，oracle支持）
-
存储引擎
-
存储引擎是MySQL中特有的一个术语，其它数据库中没有。（Oracle中有，但是不叫这个名字）
-
存储引擎是一个表存储/组织数据的方式。不同的存储引擎，表存储数据的方式不同。
-
怎么给表添加/指定“存储引擎”呢？
show create table t_student; -
在建表的时候给表指定存储引擎
CREATE TABLE `t_student` ( `no` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `cno` int(11) DEFAULT NULL, PRIMARY KEY (`no`), KEY `cno` (`cno`), CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `t_class` (`classno`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 -
在建表的时候可以在最后小括号的")"的右边使用
ENGINE来指定存储引擎。 CHARSET来指定这张表的字符编码方式。
-
mysql默认的存储引擎是：InnoDB
mysql默认的字符编码方式是：utf8
-
怎么查看mysql支持哪些存储引擎
show engines; -
MyISAM存储引擎
它管理的表具有以下特征：
使用三个文件表示每个表：
格式文件 — 存储表结构的定义（mytable.frm）
数据文件 — 存储表行的内容（mytable.MYD）
索引文件 — 存储表上索引（mytable.MYI）：索引是一本书的目录，缩小扫描范围，提高查询效率的一种机制。提示一下：
对于一张表来说，只要是主键，
或者加有unique约束的字段上会自动创建索引。MyISAM存储引擎特点：
可被转换为压缩、只读表来节省空间
这是这种存储引擎的优势！MyISAM不支持事务机制，安全性低。
-
InnoDB存储引擎
这是mysql默认的存储引擎，同时也是一个重量级的存储引擎。
InnoDB支持事务，支持数据库崩溃后自动恢复机制。
InnoDB存储引擎最主要的特点是：非常安全。它管理的表具有下列主要特征：
- 每个 InnoDB 表在数据库目录中以.frm 格式文件表示
- InnoDB 表空间 tablespace 被用于存储表的内容（表空间是一个逻辑名称。表空间存储数据+索引。）
- 提供一组用来记录事务性活动的日志文件
- 用 COMMIT(提交)、SAVEPOINT 及ROLLBACK(回滚)支持事务处理
- 提供全 ACID 兼容
- 在 MySQL 服务器崩溃后提供自动恢复
- 多版本（MVCC）和行级锁定
- 支持外键及引用的完整性，包括级联删除和更新
InnoDB最大的特点就是支持事务：
以保证数据的安全。效率不是很高，并且也不能压缩，不能转换为只读，
不能很好的节省存储空间。 -
MEMORY存储引擎
使用 MEMORY 存储引擎的表，其数据存储在内存中，且行的长度固定，
这两个特点使得 MEMORY 存储引擎非常快。MEMORY 存储引擎管理的表具有下列特征：
– 在数据库目录内，每个表均以.frm 格式的文件表示。
– 表数据及索引被存储在内存中。（目的就是快，查询快！）
– 表级锁机制。
– 不能包含 TEXT 或 BLOB 字段。MEMORY 存储引擎以前被称为HEAP 引擎。
MEMORY引擎优点：查询效率是最高的。不需要和硬盘交互。
MEMORY引擎缺点：不安全，关机之后数据消失。因为数据和索引都是在内存当中。
事务
-
一个事务其实就是一个完整的业务逻辑。 是一个最小的工作单元。不可再分。
-
只有DML语句才会有事务这一说，其它语句和事务无关 insert delete update。
-
事务是如何保证多条DML语句同时成功，同时失败的？
- 在事务的执行过程中，每一条DML的操作都会记录到“事务性活动的日志文件”中；
- 在事务的执行过程中，可以提交事务，也可以回滚事务；
-
提交事务 commit；
- 清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中。
- 提交事务标志着，事务的结束。并且是一种全部成功的结束。
-
回滚事务 rollback；
- 将之前所有的DML操作全部撤销，并且清空事务性活动的日志文件 。
- 回滚事务标志着，事务的结束。并且是一种全部失败的结束。
-
mysql默认情况下是支持自动提交事务的。（自动提交）
每执行一条DML语句，则提交一次！
-
代码演示
---------------------------------回滚事务---------------------------------------- mysql> use bjpowernode; Database changed mysql> select * from dept_bak; Empty set (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into dept_bak values(10,'abc', 'tj'); Query OK, 1 row affected (0.00 sec) mysql> insert into dept_bak values(10,'abc', 'tj'); Query OK, 1 row affected (0.00 sec) mysql> select * from dept_bak; +--------+-------+------+ | DEPTNO | DNAME | LOC | +--------+-------+------+ | 10 | abc | tj | | 10 | abc | tj | +--------+-------+------+ 2 rows in set (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> select * from dept_bak; Empty set (0.00 sec) ---------------------------------提交事务---------------------------------------- mysql> use bjpowernode; Database changed mysql> select * from dept_bak; +--------+-------+------+ | DEPTNO | DNAME | LOC | +--------+-------+------+ | 10 | abc | bj | +--------+-------+------+ 1 row in set (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into dept_bak values(20,'abc Query OK, 1 row affected (0.00 sec) mysql> insert into dept_bak values(20,'abc Query OK, 1 row affected (0.00 sec) mysql> insert into dept_bak values(20,'abc Query OK, 1 row affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.01 sec) mysql> select * from dept_bak; +--------+-------+------+ | DEPTNO | DNAME | LOC | +--------+-------+------+ | 10 | abc | bj | | 20 | abc | tj | | 20 | abc | tj | | 20 | abc | tj | +--------+-------+------+ 4 rows in set (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> select * from dept_bak; +--------+-------+------+ | DEPTNO | DNAME | LOC | +--------+-------+------+ | 10 | abc | bj | | 20 | abc | tj | | 20 | abc | tj | | 20 | abc | tj | +--------+-------+------+ 4 rows in set (0.00 sec) -
事务四个特性
A：原子性
说明事务是最小的工作单元。不可再分。C：一致性
所有事务要求，在同一个事务当中，所有操作必须同时成功，或者同时失败，以保证数据的一致性。I：隔离性
A事务和B事务之间具有一定的隔离。
教室A和教室B之间有一道墙，这道墙就是隔离性。
A事务在操作一张表的时候，另一个事务B也操作这张表会那样？？？D：持久性
事务最终结束的一个保障。事务提交，就相当于将没有保存到硬盘上的数据
保存到硬盘上！ -
事务的隔离性
-
读未提交：read uncommitted;
set global transaction isolation level read uncommitted;事务A可以读到事务B未提交的数据，脏读；
-
读已提交：read committed
set global transaction isolation level read committed;解决了脏读，但不可重复读取数据；每次读到的都是真实的数据；Oracle数据库默认的；
-
可重复读：repeatable read
set global transaction isolation level repeatable read;事务A开启之后，不管多久，每一次在事务A中读到数据都是一致的；即使事务B将数据修改了并提交了，事务A读到的还是之前的数据；
出现的问题，幻读；
-
串性化：serializable （最高隔离级别）
事务排队执行；
set global transaction isolation level serializable;
-
索引
-
索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。
-
一张表的一个字段可以添加一个索引，当然，多个字段联合起来也可以添加索引。
-
索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。
-
举例：
如果name字段上没有添加索引（目录），或者说没有给name字段创建索引，MySQL会进行全扫描，会将name字段上的每一个值都比对一遍。效率比较低。
select * from t_user where name = 'jack'; -
MySQL在查询方面主要就是两种方式：
第一种方式：全表扫描
第二种方式：根据索引检索。 -
在mysql数据库当中索引也是需要排序的，并且这个排序和TreeSet 数据结构相同。TreeSet（TreeMap）底层是一个自平衡的二叉树！在mysql 当中索引是一个B-Tree数据结构。 遵循左小右大原则存放。采用中序遍历方式遍历取数据。
-
实现原理
# 假设有一张用户表：t_user id(PK) name 每一行记录在硬盘上都有物理存储编号 ---------------------------------------------------------------------------------- 100 zhangsan 0x1111 120 lisi 0x2222 99 wangwu 0x8888 88 zhaoliu 0x9999 101 jack 0x6666 55 lucy 0x5555 130 tom 0x7777
-
在任何数据库当中主键上都会自动添加索引对象，id字段上自动有索引，因为id是PK。另外在mysql当中，一个字段上如果有unique约束的话，也会自动创建索引对象。
-
在任何数据库当中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号。
-
在mysql当中，索引是一个单独的对象，不同的存储引擎以不同的形式存在，在MyISAM存储引擎中，索引存储在一个.MYI文件中。在InnoDB存储引擎中索引存储在一个逻辑名称叫做tablespace的表当中。在MEMORY存储引擎当中索引被存储在内存当中。不管索引存储在哪里，索引在mysql当中都是一个树的形式存在。（自平衡二叉树：B-Tree）
-
-
添加索引的条件
- 数据量庞大
- 该字段经常出现在where的后面，以条件的形式存在，也就是说这个字段总是被扫描。
- 该字段很少的DML(insert delete update)操作。（因为DML之后，索引需要重新排序。）
建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能。
建议通过主键查询，建议通过unique约束的字段进行查询，效率是比较高的。 -
索引的创建和删除
-
创建索引
给emp表的ename字段添加索引，起名：emp_ename_index
mysql> create index emp_ename_index on emp(ename); -
删除索引
将emp表上的emp_ename_index索引对象删除。
mysql> drop index emp_ename_index on emp;
-
-
在mysql当中，怎么查看一个SQL语句是否使用了索引进行检索？
mysql> explain select * from emp where ename = 'KING';+----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+扫描14条记录：说明没有使用索引。type=ALL
mysql> create index emp_ename_index on emp(ename); mysql> explain select * from emp where ename = 'KING'; +----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+ | 1 | SIMPLE | emp | ref | emp_ename_index | emp_ename_index | 33 | const | 1 | Using where | +----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+扫描11条记录：说明使用索引。type=ref
-
索引失效的情况
-
失效的第1种情况
select * from emp where ename like '%T';ename上即使添加了索引，也不会走索引，为什么？
原因是因为模糊匹配当中以“%”开头了！
尽量避免模糊查询的时候以“%”开始。
这是一种优化的手段/策略。mysql> explain select * from emp where ename like '%T'; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ -
失效的第2种情况
使用or的时候会失效，如果使用or那么要求or两边的条件字段都要有索引，才会走索引，如果其中一边有一个字段没有索引，那么另一个字段上的索引也会实现。所以这就是为什么不建议使用or的原因。
mysql> explain select * from emp where ename = 'KING' or job = 'MANAGER'; +----+-------------+-------+------+-----------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+-----------------+------+---------+------+------+-------------+ | 1 | SIMPLE | emp | ALL | emp_ename_index | NULL | NULL | NULL | 14 | Using where | +----+-------------+-------+------+-----------------+------+---------+------+------+-------------+ -
失效的第3种情况
使用复合索引的时候，没有使用左侧的列查找，索引失效；
复合索引：两个字段，或者更多字段联合起来添加一个索引，叫做复合索引；
create index emp_job_sal_index on emp(job,sal); mysql> explain select * from emp where job = 'MANAGER'; +----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+ | 1 | SIMPLE | emp | ref | emp_job_sal_index | emp_job_sal_index | 30 | const | 3 | Using where | +----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+ mysql> explain select * from emp where sal = 800; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ -
失效的第4种情况
在where当中索引列参加了运算，索引失效。
mysql> create index emp_sal_index on emp(sal); explain select * from emp where sal = 800; +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+ | 1 | SIMPLE | emp | ref | emp_sal_index | emp_sal_index | 9 | const | 1 | Using where | +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+ mysql> explain select * from emp where sal+1 = 800; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ -
失效的第5种情况
在where当中索引列使用了函数
mysql> explain select * from emp where lower(ename) = 'smith'; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
-
-
索引分类
-
单一索引：一个字段上添加索引。
-
复合索引：两个字段或者更多的字段上添加索引。
-
主键索引：主键上添加索引。
-
唯一性索引：具有unique约束的字段上添加索引。
注意：唯一性比较弱的字段上添加索引用处不大。如果索引字段存在大量重复数据，索引起不到太大作用；
-
-
索引是各种数据库进行优化的重要手段，优化的时候优先考虑的因素就是索引；
视图 view
站在不同的角度去看待同一份数据。
-
视图创建和删除
-
创建视图对象
create view dept2_view as select * from dept2; -
删除视图对象
drop view dept2_view; -
注意：只有DQL语句才能以view的形式创建。
create view view_name as 这里的语句必须是DQL语句;
-
-
视图的用途
-
我们可以面向视图对象进行增删改查，对视图对象的增删改查，会导致原表被操作！（视图的特点：通过对视图的操作，会影响到原表数据。）
//面向视图查询 select * from dept2_view; // 面向视图插入 insert into dept2_view(deptno,dname,loc) values(60,'SALES', 'BEIJING'); // 查询原表数据 mysql> select * from dept2; +--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | | 60 | SALES | BEIJING | +--------+------------+----------+ // 面向视图删除 mysql> delete from dept2_view; // 查询原表数据 mysql> select * from dept2; Empty set (0.00 sec) -
实际开发中的用途
假设有一条非常复杂的SQL语句，而这条SQL语句需要在不同的位置上反复使用。每一次使用这个sql语句的时候都需要重新编写，很长，很麻烦，怎么办？
- 可以把这条复杂的SQL语句以视图对象的形式新建。
- 在需要编写这条SQL语句的位置直接使用视图对象，可以大大简化开发。
- 并且利于后期的维护，因为修改的时候也只需要修改一个位置就行，只需要修改视图对象所映射的SQL语句。
- 我们以后面向视图开发的时候，使用视图的时候可以像使用table一样。可以对视图进行增删改查等操作。视图不是在内存当中，视图对象也是存储在硬盘上的，不会消失。
- 再提醒一下：
视图对应的语句只能是DQL语句。
但是视图对象创建完成之后，可以对视图进行增删改查等操作。
小插曲： 增删改查，又叫做：CRUD。 CRUD是在公司中程序员之间沟通的术语。一般我们很少说增删改查。 一般都说CRUD。 C:Create（增） R:Retrive（查：检索） U:Update（改） D:Delete（删）
-
DBA常用命令
-
数据导出
注意：在windows的dos命令窗口中：
mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p123456导出指定的表
mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot -p123456 -
数据导入？
注意：需要先登录到mysql数据库服务器上。
然后创建数据库：create database bjpowernode;
使用数据库：use bjpowernode
然后初始化数据库：source D:\bjpowernode.sql
数据库设计三范式
-
什么是数据库设计范式？
数据库表的设计依据。教你怎么进行数据库表的设计。
-
数据库设计范式
-
第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分。
-
第二范式：建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不要产生部分依赖。
-
第三范式：建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖。
设计数据库表的时候，按照以上的范式进行，可以避免表中数据的冗余，空间的浪费。三范式是面试官经常问的，所以一定要熟记在心！
-
-
第一范式
最核心，最重要的范式，所有表的设计都需要满足。必须有主键，并且每一个字段都是原子性不可再分。
学生编号 学生姓名 联系方式 ------------------------------------------ 1001 张三 zs@gmail.com,1359999999 1002 李四 ls@gmail.com,13699999999 1001 王五 ww@163.net,13488888888 以上是学生表，满足第一范式吗？ 不满足，第一：没有主键。第二：联系方式可以分为邮箱地址和电话 学生编号(pk) 学生姓名 邮箱地址 联系电话 ---------------------------------------------------- 1001 张三 zs@gmail.com 1359999999 1002 李四 ls@gmail.com 13699999999 1003 王五 ww@163.net 13488888888 -
第二范式
建立在第一范式的基础之上，要求所有非主键字段必须完全依赖主键，不要产生部分依赖。
学生编号 学生姓名 教师编号 教师姓名 ---------------------------------------------------- 1001 张三 001 王老师 1002 李四 002 赵老师 1003 王五 001 王老师 1001 张三 002 赵老师 这张表描述了学生和老师的关系：（1个学生可能有多个老师，1个老师有多个学生） 这是非常典型的：多对多关系！ 分析以上的表是否满足第一范式？ 不满足第一范式。 怎么满足第一范式呢？修改 学生编号+教师编号(pk) 学生姓名 教师姓名 ---------------------------------------------------- 1001 001 张三 王老师 1002 002 李四 赵老师 1003 001 王五 王老师 1001 002 张三 赵老师 学生编号 教师编号，两个字段联合做主键，复合主键（PK: 学生编号+教师编号） 经过修改之后，以上的表满足了第一范式。但是满足第二范式吗？ 不满足，“张三”依赖1001，“王老师”依赖001，显然产生了部分依赖。 产生部分依赖有什么缺点？ 数据冗余了。空间浪费了。“张三”重复了，“王老师”重复了。 为了让以上的表满足第二范式，你需要这样设计： 使用三张表来表示多对多的关系！ 学生表 学生编号(pk) 学生名字 ------------------------------------ 1001 张三 1002 李四 1003 王五 教师表 教师编号(pk) 教师姓名 -------------------------------------- 001 王老师 002 赵老师 学生教师关系表 id(pk) 学生编号(fk) 教师编号(fk) ------------------------------------------------------ 1 1001 001 2 1002 002 3 1003 001 4 1001 002 背口诀： 多对多怎么设计？ 多对多，三张表，关系表两个外键！ -
第三范式
第三范式建立在第二范式的基础之上要求所有非主键字典必须直接依赖主键，不要产生传递依赖。
学生编号（PK） 学生姓名 班级编号 班级名称 --------------------------------------------------------- 1001 张三 01 一年一班 1002 李四 02 一年二班 1003 王五 03 一年三班 1004 赵六 03 一年三班 以上表的设计是描述：班级和学生的关系。很显然是1对多关系！ 一个教室中有多个学生。 分析以上表是否满足第一范式？ 满足第一范式，有主键。 分析以上表是否满足第二范式？ 满足第二范式，因为主键不是复合主键，没有产生部分依赖。主键是单一主键。 分析以上表是否满足第三范式？ 第三范式要求：不要产生传递依赖！ 一年一班依赖01，01依赖1001，产生了传递依赖。 不符合第三范式的要求。产生了数据的冗余。 那么应该怎么设计一对多呢？ 班级表：一 班级编号(pk) 班级名称 ---------------------------------------- 01 一年一班 02 一年二班 03 一年三班 学生表：多 学生编号（PK） 学生姓名 班级编号(fk) ------------------------------------------- 1001 张三 01 1002 李四 02 1003 王五 03 1004 赵六 03 背口诀： 一对多，两张表，多的表加外键！
总结表的设计
一对多：
一对多，两张表，多的表加外键！
多对多：
多对多，三张表，关系表两个外键！
一对一：
一对一放到一张表中不就行了吗？为啥还要拆分表？
在实际的开发中，可能存在一张表字段太多，太庞大。这个时候要拆分表。
一对一怎么设计？
没有拆分表之前：一张表
t_user
id login_name login_pwd real_name email address........
---------------------------------------------------------------------------
1 zhangsan 123 张三 zhangsan@xxx
2 lisi 123 李四 lisi@xxx
...
这种庞大的表建议拆分为两张：
t_login 登录信息表
id(pk) login_name login_pwd
---------------------------------
1 zhangsan 123
2 lisi 123
t_user 用户详细信息表
id(pk) real_name email address........ login_id(fk+unique)
-----------------------------------------------------------------------------------------
100 张三 zhangsan@xxx 1
200 李四 lisi@xxx 2
口诀：一对一，外键唯一
883
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
