目录
1、数据库DB？数据库管理系统DBMS？结构化查询语言SQL？他们之间的关系是什么？
2、SQL语句分类： DQL/DML/DDL/TCL/DCL （通用的，不区分大小写，以;结尾）
6、子查询：语句中嵌套select语句，被嵌套的select语句称为子查询
10、存储引擎：表存储/组织数据的方式，即表的不同存储方式（了解）
11、事务：适用于DML语句，处理数据insert、 delete、 update
12、索引：索引是在数据库表的字段上添加的，提高查询效率的一种机制。
13、视图view:站在不同的角度去看待同一份数据。通过对视图的操作，会影响到原表数据。
15、数据库设计三范式——数据库表的设计依据，可以避免表中数据冗余，空间浪费。
1、数据库DB？数据库管理系统DBMS？结构化查询语言SQL？他们之间的关系是什么？
数据库：
英文单词DataBase，简称DB。存储具有特定格式的数据的文件的组合。
数据库当中最基本的单元是表：table；数据库当中是以表格的形式表示数据的。
任何一张表都有行和列：
行（row）：被称为数据/记录。
列（column）：被称为字段。
数据库管理系统：
DataBaseManagement，简称DBMS。
数据库管理系统是专门用来管理数据库中数据的，可以对数据进行增删改查。
常见的数据库管理系统：
MySQL、Oracle、MS SqlServer、DB2、sybase等....
SQL：结构化查询语言
SQL是一套标准，适用于所有的DBMS
程序员通过编写SQL语句，然后DBMS负责执行SQL语句，最终来完成数据库中数据的增删改查操作。
三者之间的关系？
DBMS--执行--> SQL --操作--> DB
2、SQL语句分类： DQL/DML/DDL/TCL/DCL （通用的，不区分大小写，以;结尾）
DQL：数据查询语言（Data Query Language）
（凡是带有select关键字的都是查询语句）
select...
DML： 数据操作语言 （Data Manipulation Language）
（凡是对表当中的数据进行增删改的都是DML）
insert 增
delete 删
update 改
DDL：数据定义语言 （Data Definition Language）
（ DDL主要操作的是表的结构）
凡是带有create、drop、alter的都是DDL。
create：新建，等同于增
drop：删除
alter：修改
TCL：事务控制语言 （Transactions Contorl language）
包括：
事务提交：commit;
事务回滚：rollback;
DCL：数据控制语言（Data Control Language）
例如：授权grant、撤销权限revoke....
3、Mysql的常用命令：
登录mysql: 命令行输入mysql -uroot -p密码 或 mysql -uroot -p 然后输入密码
退出mysql ：exit
查看mysql数据库的版本号：mysql> select version();
/c 用来终止一条命令的输入。
查看所有数据库：mysql> show databases；
使用某个数据库： mysql> use 数据库名;
创建数据库：mysql> create database 数据库名；
查看当前使用的是哪个数据库：mysql> select database();
查看某个数据库下的所有表：mysql> show tables;
导入sql文件中的数据：mysql> source 路径\文件名.sql
批量的执行SQL语句，可以使用sql脚本文件。在mysql当中怎么执行sql脚本呢？
mysql> source D:\course\03-MySQL\document\vip.sql
4、DQL语句:数据查询语言 select
查看表中的数据（所有字段）：select * from 表名;
查看表的结构：desc 表名;
（包括属性，类型，是否可为空，关键字，默认值等，describe缩写为：desc）
查询一个或几个字段：select 字段名1,字段名2,…… from 表名;
（select和from都是关键字。字段名和表名都是标识符。）
给查询的列起别名：select 字段1,字段2 as '字段2的别名' from 表名;
注意：只是将显示的查询结果列名改变，原列名不变。select只能查询不能修改
别名需用' '包住，数据库中的字符串都是采用单引号括起来；as关键字可省略
字段可以使用数学表达式：select ename,sal*12 from emp;
条件查询：select 字段1,字段2,字段3.... from 表名 where 条件;
条件包括：=，<>即!=，< ,>,<=,>=,between…and…（闭区间= >= and <= ）, is null, and, or(and优先级较高), in（包含，相当于多个 or），not， like（模糊查询：%匹配任意多个字符，下划线_：任意一个字符。字符串中如果含有_ 用\_表示，转义符\）
排序： select 字段1,字段2,字段3.... from 表名 order by 待排序的字段名；（默认：升序 ）
select 字段1,字段2,字段3.... from 表名 order by 字段名1 asc，字段名2 desc;（降序）
select 字段1,字段2,字段3.... from 表名 order by 字段名1 asc，字段名2 asc;（升序）
按 多个字段名 排序，按优先级顺序和排序方式，写在 order by后面 ：字段名 升降序 越靠前越优先
select ... from ... where ... order by ...
以上语句的执行顺序必须掌握：
第一步：from
第二步：where
第三步：select
第四步：order by（排序总是在最后执行！）
数据处理函数：
单行处理函数：一个输入对应一个输出。
- lower 转换小写
- upper 转换大写
- substr 取子串（substr( 被截取的字符串, 起始下标,截取的长度)）
- concat函数进行字符串的拼接
- length 取长度
- trim 去空格
- round 四舍五入（round(数值，保留的小数位数)）
- ifnull 可以将 null 转换成一个具体值，ifnull(数据, 被当做哪个值) 所有数据库中，只要有NULL参与的数学运算，结果为NULL。
多行处理函数 = 分组函数：多个输入，对应1个输出。分组函数在使用的时候必须先进行分组group by，然后才能用。不能用在where中。分组函数自动忽略NULL，你不需要提前对NULL进行处理。
- count (字段名） 计数
- sum(字段名） 求和
- avg (字段名） 平均值
- max(字段名） 最大值
- min (字段名） 最小值
-
计算两个日期的时间差？TimeStampDiff(间隔类型, 前一个日期, 后一个日期)
eg: timestampdiff(YEAR, 日期的字段名, now())
间隔类型：
SECOND 秒，
MINUTE 分钟，
HOUR 小时，
DAY 天，
WEEK 星期
MONTH 月，
QUARTER 季度，
YEAR 年
分组查询：先进行分组group by ，然后对每一组的数据进行操作:分组函数。
- group by 缺省，默认所有元素自成一组
- 在一条select语句当中，如果有group by语句的话，select后面只能跟：参加分组的字段，以及分组函数。
- 联合分组：两个字段联合成1个字段看
select
deptno, job, max(sal)
from
emp
group by
deptno, job;
数据过滤：having可以对分完组之后的数据进一步过滤。
- having必须和group by联合使用。
- having不能代替where，where和having，优先选择where，效率高
- where实在完成不了 eg:where 后的判断条件为分组函数的结果，不可用，只能选择having。
distinct 数据去重 关键字distinct+字段名：把查询结果去除重复记录，
注意：原表数据不会被修改，只是查询结果去重。如果distinct在所有字段的前面，则后面的多个字段 字段联合后去重
union 合并查询结果集:
- 两个毫不相关的表查询结果合并。要求两个结果集的列数和列的数据类型一致。
- 减少了匹配次数，union把多次连接的匹配次数的乘法量级变成了加法运算
limit 分页查询（非常重要），在sql语句中最后执行
- limit作用：将查询结果集的一部分取出来，作用：提高用户的体验
- 完整用法：limit startIndex, length； startIndex是起始下标，length是长度。起始下标从0开始。 缺省用法：limit 5; 这是取前5.
- mysql当中limit在order by之后执行
- 通用的标准分页SQL: 每页显示pageSize条记录
第pageNo页：limit (pageNo - 1) * pageSize , pageSize
大总结（单表的查询学完了）
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 分页查询
5、连接查询：内连接、外连接
单表查询： 从一张表中单独查询，上述查询语句
连接查询： 多张表联合起来查询数据。多表存储数据，目的：避免数据重复、去冗余
当两张表进行连接查询，没有任何条件限制的时候，最终查询结果条数，是两张表条数的乘积，这种现象被称为：笛卡尔积现象。
怎么避免笛卡尔积现象？
连接时加条件，满足这个条件的记录被筛选出来！但匹配次数不会减少，只显示有效结果
根据表连接的方式分类：
外连接使用较多,内连接会数据丢失
内连接：AB两张表没有主次关系，平等的。只有能够匹配上这个条件的数据才能查询出来。
等值连接 ： //SQL92语法。inner可以省略（带着inner可读性更好,表示内连接）
select
e.ename,d.dname
from
emp e //连接表a
inner join
dept d //连接表b
on
e.deptno = d.deptno; //a和b连接条件
where
筛选条件 非等值连接 ： on 后面的条件为 between…and… ， > ，< , in， like等
自连接： 一张表看成两张表。将该表的不同字段连接
select
a.ename as '员工名', b.ename as '领导名'
from
emp a
join
emp b
on
a.mgr = b.empno; //员工的领导编号 = 领导的员工编号外连接：两张表连接，产生了主次关系。无条件的查询主表，附带副表信息，若无副表信息则副 表信息设为null。 outer可以省略（带着outer可读性更好,表示外连接）left，right不可省
左外连接（左连接）：右边是主表 from b right outer join a on … （主表a,副表b ）
右外连接（右连接）：左边是主表 from a left outer join b on …（主表a,副表b ）
多张表怎么连接？语法：一条SQL中内连接和外连接可以混合使用
select
...
from
a
join
b
on
a和b的连接条件
join
c
on
a和c的连接条件
right join
d
on
a和d的连接条件6、子查询：语句中嵌套select语句，被嵌套的select语句称为子查询
select后面出现的子查询：将查询出来的数据作为一个字段，这个子查询只能一次返回1条结果
where子句中的子查询：where子句中不能直接使用分组函数。（使用情形）
from子句中的子查询：将子查询的查询结果当做一张临时表
7、DDL：数据定义语言(创建、删除表，操作表的结构）
表的创建（建表）：
create table 表名(
字段名1 数据类型 default 默认值,
字段名2 数据类型,
字段名3 数据类型
);- 表名：建议以t_ 或者 tbl_开始，可读性强。见名知意。
- Mysql 常用数据类型：
- varchar(最长255): 可变长度的字符串, 节省空间 。
优点：节省空间，会根据实际的数据长度动态分配空间
缺点：需要动态分配空间，速度慢 - char(最长255)： 定长字符串
优点：不需要动态分配空间，速度快。
缺点：使用不当可能会导致空间的浪费，分配固定长度的空间去存储数据。 - 字符串长度大于255时 用CLOB字符大对象存储
删除表：drop table 表名; // 当这张表不存在的时候会报错！
drop table if exists 表名; // 如果这张表存在的话，删除
8、DML：数据操作语言，操作表中数据
对表结构的增删改：可使用工具操作完成，修改表结构的sql语句不会出在Java代码中
增删改查的术语：CRUD操作：create 增 retrieve 检索 update 修改 delete 删除
插入数据insert：
语法格式：
insert into 表名(字段名1,字段名2,字段名3...) values(值1,值2,值3...),(值1,值2,值3...);
注意：
- 字段名和值要一一对应。数量要对应，数据类型要对应。
- 没有给其它字段指定值的话，默认值是NULL。
- 前面的字段名省略的话，等于都写上了！所以值也要都对应写上！
- 可插入多行数据，用，隔开
表的复制：
1、将查询结构当作表创建
create table 表名 as （select 选取的字段名1,字段名2 from 被复制的表名）
2、将查询结果插入到一张表中（表的字段数与查询结果相同）
insert into 表名 select 选取的字段名1,字段名2 from 被复制的表名
快速删除表中的数据：delete from 表名; //这种删除数据的方式比较慢。
truncate table 表名; //（这种操作属于DDL操作。）用于快速删除大表数据，表还在
delete语句删除数据的原理？（delete属于DML语句！！！）
表中的数据被删除了，但是这个数据在硬盘上的真实存储空间不会被释放！！！
这种删除缺点是：删除效率比较低。
这种删除优点是：支持回滚，后悔了可以再恢复数据！！！
truncate语句删除数据的原理？
这种删除效率比较高，表被一次截断，物理删除。
这种删除缺点：不支持回滚。
这种删除优点：快速。
9、约束：（5种）保证这个表中数据的完整性、有效性
字段名 数据类型 约束，// 约束直接添加到列后面的，叫做列级约束。
什么时候使用表级约束呢？
需要给多个字段联合起来添加某一个约束的时候，需要使用表级约束。
9.1 非空约束：not null 约束的字段不能为NULL
9.2 唯一性约束: unique 约束的字段不能重复，但是可以为NULL。
9.3 主键约束: primary key （简称PK）约束的字段不能重复也不能为NULL
在mysql当中，如果一个字段同时被not null和unique约束的话,该字段自动变成主键字段。（注意：oracle中不一样！）
主键约束的相关术语？
主键约束：就是一种约束。
主键字段：该字段上添加了主键约束，这样的字段叫做：主键字段
主键值：主键字段中的每一个值都叫做：主键值。主键值是每一行记录的唯一标识。 主键值建议使用： int，bigint，char等定长的数据类型。
记住：表的设计三范式之一：任何一张表都必须有且仅有一个主键，没有主键，表无效！！主键除了：单一主键和复合主键之外，还可以这样进行分类？
单一主键 和 复合主键 ：主键字段是一个，还是多个字段联合
自然主键 和 业务主键：区别主键值是一个自然数，还是和业务紧密关联
drop table if exists t_vip;
create table t_vip(
//列级约束
id int primary key auto_increment, //主键，auto_increment表示自增，从1开始，以1递增！
name varchar(255) not null, // not null只有列级约束，没有表级约束！
email varchar(255) unique, //唯一性约束
/*
//表级约束：约束没有添加在列的后面。
unique(name,email) //联合约束:联合后具有唯一性
primary key(id) // 单一主键 推荐使用
primary key(id,name) // 复合主键：id和name联合起来做主键。不建议使用、违背表的三范式
*/
);
// insert into t_vip(id) values(3);
// 为空报错： ERROR 1364 (HY000): Field 'name' doesn't have a default value
insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(1,'lisi','lisi@123.com');
//insert into t_vip(id,name,email) values(1,'lisi','lisi@123.com');
//错误：不能重复ERROR 1062 (23000): Duplicate entry '1-lisi' for key 'PRIMARY'9.4 外键约束：foreign key（简称FK）
外键约束涉及到的相关术语：
外键约束：一种约束（foreign key）
外键字段：该字段上添加了外键约束
外键值：外键字段当中的每一个值。外键值可以为NULL。
连接两张表，给子表某字段（该字段在父表中有主键约束或唯一约束）添加外键约束，该字段的值只能引用父表中的字段值。foreign key(子表字段名) reference 父表名(对应的父表字段名)
9.5 检查约束：check（mysql不支持，oracle支持）
10、存储引擎：表存储/组织数据的方式，即表的不同存储方式（了解）
不同的存储引擎，表存储数据的方式不同。
Mysql支持哪些存储引擎: MyISAM存储引擎,InnoDB存储引擎,
建表时指定存储引擎，以及字符编码方式。
create table t_product(
id int primary key,
name varchar(255)
)engine=InnoDB default charset=gbk;
10.1 MyISAM存储引擎:
优点：可被转换为压缩、只读表来节省空间。
缺点：MyISAM不支持事务机制，安全性低。
它管理的表具有以下特征：
使用三个文件表示每个表：
格式文件 — 存储表结构的定义（mytable.frm）
数据文件 — 存储表行的内容（mytable.MYD）
索引文件 — 存储表上索引（mytable.MYI）：索引是一本书的目录，缩小扫描范围，提高查询效率的一种机制。对于一张表来说，只要是主键 或 有unique约束的字段 上会自动创建索引。
10.2 InnoDB存储引擎:
这是mysql默认的存储引擎，同时也是一个重量级的存储引擎。
优点：非常安全。 支持事务处理，支持数据库崩溃后自动恢复机制，支持联机删除和更新。
缺点：效率不是很高，并且也不能压缩，不能转换为只读，不能很好的节省存储空间。
表的主要特征：
– 每个 InnoDB 表在数据库目录中以.frm 格式文件表示
– InnoDB 表空间 tablespace 被用于存储表的内容和（表空间是一个逻辑名称。表空间存储数据+索引。）
10.3 MEMORY存储引擎：
优点：数据+索引在内存中且行的长度固定，使得查询速度快。
缺点：不支持事务处理，不安全，关机后数据丢失。
11、事务：适用于DML语句，处理数据insert、 delete、 update
11.1 一个事务其实就是一个完整的业务逻辑即多条DML语句，是一个最小的工作单元。要么同时成功，要么同时失败，不可再分。
InnoDB存储引擎：提供一组用来记录事务性活动的日志文件
在事务的执行过程中，每一条DML的操作都会记录到“事务性活动的日志文件”中。
在事务的执行过程中，我们可以提交事务，也可以回滚事务。
提交事务：commit; 语句
清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中。
提交事务标志着，事务的结束。并且是一种全部成功的结束。
mysql默认情况下是支持自动提交事务的。（自动提交）
执行这个命令：start transaction; 关闭mysql的自动提交机制
回滚事务：rollback; 语句（回滚永远都是只能回滚到上一次的提交点！）
将之前所有的DML操作全部撤销，并且清空事务性活动的日志文件
回滚事务标志着，事务的结束。并且是一种全部失败的结束。
11.2 事务包括4个特性:ACID
A：原子性 说明事务是最小的工作单元。不可再分。
C：一致性 所有事务要求，在同一个事务当中，所有操作必须同时成功或失败，以保证数据的一致性。
I：隔离性 A事务和B事务之间具有一定的隔离。
SQL语句：设置隔离级别：set global transaction isolation level serializable;
获取当前隔离级别：select @@global.tx_isolation;
事务间的隔离级别：4个级别（低->高）
- 读未提交：read uncommitted 脏读现象：事务A可以读取到事务B未提交的数据。
- 读已提交：read committed 事务A只能读取到事务B提交之后的数据，解决了脏读现象。但不可重复读取数据即事务A期间每次读到数据回应事务B的操作而不同，每一次读到的数据是绝对的真实。
- oracle数据库默认的隔离级别是：read committed
- 可重复读：repeatable read 事务A开启之后，不管是多久，每一次在事务A中读取到的数据都是一致的，解决了不可重复读取数据。但 每一次读取到的数据都是幻象，不是真实数据 。
- mysql中默认的事务隔离级别就是：repeatable read
- 序列化/串行化：serializable 事务排队，不能并发，效率最低。解决了所有的问题。
- synchronized，线程同步（事务同步）每一次读取到的数据都是最真实的，并且效率是最低的。
D：持久性 事务最终结束的保障。事务提交，就相当于将未保存到硬盘上的数据保存到硬盘上！
12、索引：索引是在数据库表的字段上添加的，提高查询效率的一种机制。
- 索引可以添加在一个字段或联合多个字段上，
- 索引会自动排序=数据结构中的TreeSet（TreeMap）底层是一个自平衡的二叉树。在mysql当中索引是一个B-Tree数据结构。
- 在任何数据库当中主键或unique字段上都会自动添加索引对象
查找的两种方式：全表扫描（该字段没有索引），索引检索(通过目录定位大致位置，在局部扫描)
添加索引的条件/情形：
条件1：数据量庞大（根据需求）
条件2：该字段经常出现在where的后面，以条件的形式存在，也就是说这个字段总是被扫描。
条件3：该字段很少的DML(insert delete update)操作。（因为DML之后，索引需要重新序。）
创建索引：
mysql> create index emp_ename_index on emp(ename);
给emp表的ename字段添加索引，起名：emp_ename_index
删除索引：
mysql> drop index emp_ename_index on emp;
将emp表上的emp_ename_index索引对象删除。
查看是否使用了索引检索：
explain select * from 表名 where 字段 = '字段值';
索引失效的情况：模糊查询
select * from emp where ename like '%T';
13、视图view:站在不同的角度去看待同一份数据。通过对视图的操作，会影响到原表数据。
我们可以面向视图对象进行增删改查，对视图对象的增删改查，会导致原表被操作！（视图的特点：通过对视图的操作，会影响到原表数据。）
用途：视图可以隐藏表的实现细节。保密级别高的数据库只对外提供相应的视图并且可以重命名字段名，java程序员只能对视图进行CRUD操作，改变原表数据。
创建视图对象：create view dept2_view as select * from dept2;
删除视图对象：drop view dept2_view; //dept2_view视图名
面向视图查询数据：select * from dept2_view;
面向视图插入数据：insert into 视图名(deptno,dname,字段名) values(60,'SALES', 字段值);
面向视图删除数据： delete from dept2_view;
面向视图更新数据：update emp_dept_view set sal = 1000 where dname = 'ACCOUNTING';
14、DBA命令：数据的导入和导出（数据的备份）
数据导出：
注意：在windows的dos命令窗口中：
mysqldump 数据库名>D:\数据库名.sql -uroot -p登录密码
可以导出指定的表吗？
mysqldump 数据库名 表名>D:\数据库表名.sql -uroot -p登录密码
数据导入：
注意：需要先登录到mysql数据库服务器上：mysql -uroot -p密码
然后创建数据库：create database数据库名;
使用数据库：use 数据库名
然后初始化数据库：source D:\数据库名.sql
15、数据库设计三范式——数据库表的设计依据，可以避免表中数据冗余，空间浪费。
第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分。
第二范式：建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不要产生部分依赖。
即要求：主键不是复合主键，没有产生部分依赖。主键是单一主键。
多对多关系：建立三张表，一个关系表+两个外键（两个数据表）
第三范式：建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖。
一对多关系：建立两张表，多对应的表加外键
一对一关系：一张表字段太多，将表拆开，
主键共享（两张表，同一字段一张表作为主键，一张表为主键+外键），
外键唯一（两张表，同一字段一张表作为主键，一张表为唯一约束+外键）
注意：表联查会牺牲执行速度，实际开发中，有时拿冗余换速度
360
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
