数据库

数据库概述：
- 数据库也称为数据库管理系统
  - 以前在java中要想将java对象存到硬盘和取出到内存，就要序列化和发序列化，
  - ObjectOutputStream——>java对象(张三 20)——>序列化到硬盘文件当中
  - ObjectInputStream——>java对象(张三 20)——>反序列化到内存中
  - 很麻烦，需要new java对象，然后调用readObject()或writeObject()方法
  - 而在使用数据库，可以使用简单的sql语句就完成这些
- 常见的数据库管理系统：
  - Oracle:甲骨文，做数据库起家的
    - 2009以74亿美元收购了SUN公司，从此JAVA语言由Oracle公司，进行升级和维护
    - IBM曾经研发出eclipse开发工具，为了收购SUN公司，但失败了
    - MySql是由MySql AB公司开发的，起初SUN收购了MySql AB公司，后来Oracle收购了SUN，所以MySql并入Oracle的数据库产品线
    - Oracle收费的，Mysql免费的
  - Oracle、MySql、DB2、Sybase、“MS SqlServer 支持标准sql的数据库管理系统”
    - 互联网公司用Mysql居多，政府部分以及银行等安全级别较高的系统用的是Oracle
安装MySql
- 补充：在一台计算机上的端口号不能重复，一个端口对应一个软件，端口是软件的代号，文件下载的端口统一都是21，上网端口统一都是80，mysql端口统一是3306，oracle端口是1521
- 每个软件都有一个服务名称，这里我设置的名字为MySQL5
- 用户名root，密码123456
- 配置path环境变量，bin目录
启动和关闭MySql(要以管理员的身份启动和关闭)
登录MySql
- 查看MySql是否启动：服务中自动启动是开机即运行，手动启动是在需要使用该程序或服务时才运行。
- 登录MySql
  - 密码不希望别人看见
sql、DB、DBMS分别是什么，他们之间的关系？
- DB(DataBase)：数据库，数据库实际上在硬盘上以文件形式存在
- DBMS(DataBase Management System)：数据库管理系统，常见的有MySql、Oracle、DB2、Sybase、SqlServer
- sql(Structured Query Language)：结构化查询语言，是一门标准的语言，标准的sql适用于所有的数据库产品，sql属于高级语言，只要能看懂英语单词，sql语句就可以读懂什么意思，sql语句执行的时候，实际内部也会先编译，然后执行sql，sql语句的编译由DBMS完成，我们看不见
- 总结：DBMS负责执行sql语句，通过执行sql语句来操作DB当中的数据
表(Table)：
- 表是数据库的基本组成单元，所有的数据都以表格的形式组织。目的是可读性强
- 表包括行和列：行被称为数据/记录，列被称为字段
- 每一个字段应该包括哪些属性？
  - 字段名、数据类型(varchar是String、bigint是long)、相关约束(字段不为空)
学习MySql主要还是学习通用的Sql语句，那么Sql语句包括增删改查，Sql语句怎么分类呢？
- DQL(Data Query Language)：数据查询语言，凡是select语句都是DQL
- DML(Data Manipulation Language)：数据操纵语言，insert、delete、update对表当中的数据进行增删改
- DDL(Data Definition Language)：数据定义语言，create、drop、alter，对表结构的增删改，比如年龄字段要删除
- DCL(Data Control Language)：数据控制语言，grant授权、revoke撤销权限等
- TCL(Transaction Control Language)：事务控制语言，commit提交事务、rollback回滚事务
导入数据：
- 登录mysql数据库管理系统 mysql -uroot -p123456进入Mysq命令行
- 查看有哪些数据库 show databases；(这个不是SQL语句，属于Mysql命令)
  - 这三个是Mysql自带的数据库，现在mysql版本是5.0.45，MySQL5.5 开始新增的一个数据库, performance_schema 主要用于收集数据库服务器性能参数
- 创建属于我们自己的数据库create database;(这个不是SQL语句，属于Mysql命令)
- 使用bjpowernode数据库use bjpowernode;(这个不是SQL语句，属于Mysql命令)
- 查看当前使用的数据库中有哪些表？（这个不是SQL语句，属于Mysql命令)
- 初始化数据：
查看表结构：
- dept-->部门表、dmp-->员工表、salgrade-->工资等级表
- dept-->部门表
  - DEPTNO：部门编号
  - DNAME：部门名称
  - LOC：部门位置
- emp-->员工表
  - EMPNO：员工编号
  - ENAME：员工姓名
  - JOB：工作岗位
  - MGR：上级领导编号
  - HIREDATE：入职日期
  - SAL：月薪
  - COMM：补助/津贴
  - DEPTNO：部门编号
- salgrade-->工资等级表
  - GRADE：等级
  - LOSAL：最低薪资
  - HISAL：最高薪资
查看表中数据：

对sql脚本的理解：
- bjpowernode.sql,这个文件以sql结尾，这样的文件被称为sql脚本，什么是sql脚本？
  - 当一个文件的扩展名是sql，并且该文件编写了大量的sql语句，我们称这样的文件为sql脚本
- 注意：直接使用source命令可以执行sql脚本
- sql脚本中数据量太大的时候，无法打开，请使用source命令完成初始化
Mysql常用命令
- 创建数据库：create database bjpowernode；
- 查询当前使用的数据库：select database()；
- 查看mysql版本：select version()；
- \c：结束一条语句，重新开始，不会报错
- 退出mysql：exit
查看创建表的语句
查询语句(DQL)
- 简单查询语句
  - 语法格式：select 字段名1，字段名2，字段名3，....from 表名；
    - 注意：任何一条sql语句以；结尾，并且sql语句不区分大小写,但表中的数据是区分大小写的
  - 字段可以参与数学运算
  - 给查询结果的列重命名(as关键字可以省略)
  - 重命名中有中文，中文字符串用单引号括起来，mysql用单引号括起来也可以，但oracle不支持双引号，所以用单引号最好，通用
  - 查询所有字段
- 条件查询
  - 语法格式：select 字段1，字段2，字段3....from 表名 where 条件；
  - 执行顺序：先from，然后where，最后select
  - 查询工资等于5000的员工姓名
  - 查询SMITH的工资？
  - 找出工资高于3000的员工
  - 找出工资大于等于3000的员工
  - 找出工资不等于3000的员工
  - 找出工资在1100和3000之间的员工(包括1100和3000)(between..and...是闭区间)
    - 这样就变成sal>3000 and sal<1100,between...and...在使用的时候必须左小右大
  - between...and..除了使用在数字方面外，还可以使用在字符串方面方面，用在字符方面是左闭右开
  - 找出哪些人津贴为null
    - 在数据库当中NULL不是一个值，代表什么也没有，为空
    - 空不是一个值，不能用等号衡量
    - 必须使用is null或is not null
  - 找出哪些人津贴不为null
  - 找出哪些人没有津贴
- and和or的优先级
  - 找出工作岗位是MANAGER和SALEMAN的员工？
    - 不能用and，因为不存在工作岗位既是MANAGER又是SALEMAN的员工
    - 补充：
  - and和or联合起来，找出薪资大于3000的并且部门编号是20或30部门的员工
    - 所以以后，当运算符的优先级不确定的时候加小括号，小括号优先级高
  - in等同于or
    - 但in后面的值不是区间，是具体的值
    - not in:表示不在这几个值当中：
- like模糊查询
  - 找出名字当中含有o的(在模糊查询中，必须掌握两个特殊的符合，一个是%，一个是_，%代表任意多个字符，_代表任意一个字符)
  - 找出第二个字母是A的
  - 找出名字中有下划线的
    - 转义字符\,将具有特殊含义的_字符转换成普通的_字符
  - 找出最后一个字符是T的
- 排序(升序、降序)
  - 按照工资升序排，找出员工名和薪资(默认是升序排)
  - 怎么指定升序或者降序呢
    - asc(ascend)表示升序，desc(descend)表示降序
    - 升序：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;
  - 按照工资的降序排，工资一样的，按照名字的降序排
    - 注意：越靠前的字段，越能起到主导作用，只有当前面的字段无法完成排序的时候才会启动后面的字段
  - 找出工作岗位是SALESMAN的员工，并且要求按照薪资的降序排列
    - 先执行from再执行where再执行select最后order ，为什么呢？
      - 肯定是先改名字，才能再执行order by
      - 先执行where，再执行select
- 分组函数
  - count计数、sum求和、avg平均值、max最大值、min最小值，一共5个
  - 记住：所有的分组函数都是对“某一组”数据进行操作的
  - 找出员工的工资总额
  - 找出最高工资
  - 找出最低工资
  - 找出平均工资
  - 找出总人数
  - 分组函数还有另外一个名字：多行处理函数
  - 多行处理函数的特点：输入多行，最终输出的结果是1行
  - 分组函数自动忽略NULL
  - 什么是单行处理函数？
    - 输入一行，输出一行
    - 计算每个员工的年薪？
      - 这种方式不合适，津贴为null年薪就为null
      - ifnull()空处理函数？
        
        ifnull(可能为NULL的数据，被当作什么处理)：属于单行处理函数
  - 分组函数是否真的忽略NULL
    - 真的忽略了，所以后面都不需要加where comm is not null了
  - 找出工资高于平均工资的员工
    - 思考以上的错误信息，无效的使用分组函数，为什么？
    - 原因是sq语句有一个语法规则，分组函数不可直接使用在where子句当中，但是可以出现在子查询中
    - 带小括号的优先级最高，执行完之后再执行前面的select语句
  - count(*)和count(具体的某个字段)的区别
    - count(*)不是统计某个字段中数据个数，而是统计总记录条数
    - count(comm):表示统计comm字段中不为null的数据数量
  - 分组函数也能组合起来使用
- group by和having
  - group by：是按照某个字段或者某些字段进行分组
  - having：是对分组之后的数据进行二次过滤，找出满足条件的组
  - having必须和group by一起使用
  - - 通过什么进行分组，每个组只能得到一个值
  - 找出每一个工作岗位的最高薪资
    - 先执行from再执行where再执行group by再执行select，分组函数一定在group by之后执行
      - 如果select中起别名，则会先执行，这是特例
    - 当一条sql语句没有group by的话，整张表的数据会自成一组
  - select ename,max(sal),job from emp group by job;
    - 以上在mysql当中，查询结果是有的，但是结果没意义，在oracle数据库当中会报错，语法错误，oracle数据库的语法规则比mysql严谨
    - 记住一个规则：当一条语句中有group by的话，select后面只能跟分组函数和参与分组的字段。跟其他字段没意义
  - 每个工资岗位的平均薪资
  - 多个字段能不能联合起来一块分组
    - 找出每个部门不同工作岗位的最高薪资
  - 找出每个部门的最高薪资，要求显示最高薪资大于2900的数据
    - 这种方式效率低
    - 这种方式效率高，将低于2900的提前过滤掉，参与分组的数据就少了
  - 找出每个部门的平均薪资，要求显示平均薪资大于2900的数据
    - 这里不能用where了，因为where后面不能用分组函数
      - select deptno,avg(sal) from emp where avg(sal)>2900 group by deptno;错误的
  - 值得注意的是：having后面跟的条件判断的字段必须是使用了聚合函数返回的结果，否则sql会报错，例如：
- 去除重复记录
- DQL语句可以包含以下子句
  - select子句 5
  - ...
  - from子句 1
  - ...
  - where子句 2
  - ...
  - group by子句 3
  - ...
  - having子句 4
  - ...
  - order by 子句 6
  - ...
  - limit 7
  - ....
    - 其他属于运算符，例如between...and...,like,>,<,<>,!=等
连接查询
- 什么是连接查询
  - 在实际开发中，大部分情况下都不是从单表中查询数据，一般都是多张表联合查询，取出最后的结果
  - 在实际开发中，一般一个业务都会对应多张表，比如学生和班级，对应两张表
    - 如果将学生和班级存储到一张表中，结果就像这样，数据会存在大量的重复，导致数据的冗余
      - stuno stuname classno classname
      - -------------------------------------------------------------------------------------------------------------------
      - 1 zs 1 高三一班
      - 2 zs 1 高三一班
- 连接查询的分类
  - 根据语法出现的年代划分：
    - SQL92(一些老的DBA可能还在使用这种语法，DBA：DataBase Administrator，数据库管理员)
    - SQL99(比较新的语法)
  - 根据表的连接方式来划分，包括：
    - 内连接
      - 等值连接(=)
      - 非等值连接(between...and....)
      - 自连接
    - 外连接
      - 左外连接(左连接)
      - 右外连接(有连接)
    - 全连接(这个不讲，很少用)
- 在表的连接查询方面有一种现象被称为：笛卡尔积现象(笛卡尔乘积现象)：笛卡尔积是指从两个或多个表中获取所有可能的组合
  - 案例：找出每一个员工的部门名称，要求显示员工名和部门名
    - 笛卡尔积现象：当两张表进行连接查询的时候，没有任何条件限制，最终查询结果条数是两张表记录条数的乘积
  - 上面这个案例也可以给表起别名
    - 56次
    - 这样也可以
    - 给表起别名可读性高，并且执行效率高，因为查找字段时候，from后面的表会按顺序查找这个字段，起别名之后，会从指定的表中查找
  - 上述这个案例，怎么避免笛卡尔积现象？当然是加条件进行过滤
    - 思考：避免笛卡尔积现象，会减少记录的匹配次数吗
      - 不会，还是56次，只不过显示的是有效记录
    - 这是SQL92的，以后不会用
- 内连接之等值连接，最大的特点：条件是等量关系
  - 查询每个员工的部门名称，要求显示员工名和部门名
    - SQL92：(太老了，不用)
    - SQL99：(常用的)
      - 语法：
        
        .......
        
        A
        
        join
        
        B
        
        on
        
        连接条件
        
        where
        
        .....
      - SQL99语法结构更清晰一些，表的连接条件和后来的where条件分离了
        
        用SQL92也可以，但不清晰
    - join前面还有个inner，这个inner可以省略，带上inner可读性好一些
- 内连接之非等值连接(最大的特点是：连接条件中的)关系是非等量关系
  - 案例：找出每个员工的工资等级，要求显示员工名、工资、工资等级
- 内连接之自连接(最大的特点是：一张表看作是两张表，自己连自己)(自连接可以是等值连接也可以是非等值连接)
  - 找出每个员工的上级领导
    - emp a员工表，在这个表里，FORD属于员工
    - emp b领导表，在这个表里，FORD属于领导
    - - 一共14个员工，这里输出了13个，因为king的上级领导编号是null，所以找不到对应的上级领导，所以不显示
- 外连接
  - 什么是外连接，和内连接有什么区别
    - 内连接
      - 假设A表和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接
      - AB两张表没有主副之分，两张表是平等的
    - 外连接
      - 假设A表和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副标，主要查询主表中的数据，捎带着查询副表，当副标中的数据没有和主表中的数据匹配上，副表自动模拟出null与之匹配
  - 外连接的分类
    - 左外连接(左连接)：表示左边的这张表是主表
    - 右外连接(右连接)：表示右边的这张表是主表
    - 左连接有右连接的写法，右连接有左连接的写法
  - 找出每个员工的上级领导(所有员工都必须查出来)
    - - outer是可以省略的，所以区分内连接和外连接可以看是否有left和right
      - 外连接最重要的特点就是：主表数据无条件全部查出来
  - 找出哪个部门没有员工
- 三张表怎么连接查询
  - 找出每一个员工的部门名称以及工资等级
- 找出每一个员工的部门名称以及工资等级以及上级领导
什么是子查询？子查询都可以出现在哪里？
- select语句当中嵌套select语句，被嵌套的select语句是子查询
- 子查询可以出现在哪里？
  - select
  - ...(select)
  - from
  - ...(select)
  - where
  - ...(select)
- where子句中使用子查询
  - 案例：找出高于平均工资的员工
- from后面嵌套子查询
  - 找出每个部门平均薪水的薪资等级
  - - 第二个这种方式不对，因为找不到sal的列，所以应该起别名
  - 找出每个部门平均的薪水等级
- select后面嵌套子查询
  - 找出每个员工所在的部门名称
union
- 可以将查询结果集拼接
- 找出工作岗位是salesman和manager的员工
- 只要列数相同，数据可以随意拼接，只不过没意义
- 列数不同不可以用union拼接
limit
- 是mysql特有的，其他数据库没有，不通用(Oracle中有一个相同的机制，叫做rownum)
- 重点中的重点，以后分页查询都靠它
- limit取结果集中的部分数据，这是它的作用
- limit startIndex，length
  - startIndex表示起始位置，从0开始
  - length表示取几个
- 取出工资前五名的员工
  - 不写起始位置，默认从0开始
- limit是sql语句中最后执行的一个环节
- 找出工资排名在第四到第九之间的员工
- 通用的标准的分页sql
  - 每页显示3条数据记录
    - 第1页：0，3
    - 第2页：3，3
    - 第3页：6，3
    - 第4页：9，3
    - 第5页：12，3
  - 每页显示pageSize条记录
  - 第pageNo页：(pageNo-1)*pageSize,pageSize
表的创建
- 建表语句的语法格式：
  - create table 表名(
    - 字段名1 数据类型，
    - 字段名2 数据类型，
    - 字段名3 数据类型，
  - )；
- 关于mysql当中字段的数据类型？以下只说常见的
  - int 整数型(int)
  - bigint 长整型(java中的long)
  - float 浮点型(float)
  - double 浮点型(double)
  - char 定长字符串(String)
  - varchar 可变长字符串 255(StringBuffer/StringBuilder)
  - data 日期类型(对应java中的java.sql.Date类型)
  - BLOB 二进制的大对象(存储图片，视频等流媒体)(Binary Large OBject)(对应JavaObject)
  - CLOB 字符大对象(存储较大的文本)(Character Large Object)(对应JavaObject)
- char和varchar怎么选择？
  - 在实际开发中，当某个字段的数据长度不发生改变的时候，是定长的，例如：性别、生日等，都采用char，例如char(6),该char会直接创建6个字节，不管存进去‘abc’或‘abcdef’都是给6个字节，一旦存进去的数据超过6个字节，就会报错
  - 当存储的数据长度不确定，例如：简介、姓名等都是采用varchar，varchar是存进去多少自己他就分配多少字节长度
  - 但char的效率高一些，直接分配定长，底层不需要判断，varchar需要判断
- BLOB、CLOB不能直接通过insert插入，要通过io流
- 表名在数据库中一般建议以：t_或者tbl开始
- 创建学生表
  - 学生信息包括：学号、姓名、性别、班级编号、生日
  - 学号：bigint、姓名：varchar、性别：char、班级编号：varchar、生日：char
  - create table t_student(
    - no bigint,
    - name varchar(255),
    - sex char(1),
    - classno varchar(255),
    - birth char(10)
  - );
插入数据
- 语法格式：
  - insert into 表名 (字段名1，字段名2，字段名3.......) values(值1，值2，值3........)；
  - 要求：字段的数量和值的数量相同，并且数据类型要对应相同
- - 换位置也可以，只要前后能对应上就可以
- - 字段不写全也可以，别的字段自动插入null
  - 要想把no的null编程3，再插入no为3的不可以，insert语句一旦执行，必然会出现多一条记录，只能改
- 当这个表存在的话删除：drop table if exists t_student;
- 指定性别的默认值为1
- 字段可以不写，但这样后面的value对数量和顺序都有要求
- 一次插入多个数据：
表的复制
- 语法：create table 表名 as select 语句；
- 将查询结果当作一张表创建出来
- 将查询结果插入到一张表中
修改表中数据：update
- 语法格式：update 表名 set 字段名1=值1，字段名2=值2.....where 条件；
  - 注意：没有条件整张表更新
删除表中数据
- 语法格式：delete from 表名 where 条件；
  - 注意没有条件数据全部删除
- 怎么删除大表
  - truncate table t_student;
  - 表被截断，不可回滚
  - 用delete删除，表中数据删了，但格子还在，truncate直接格子也删了，永久丢失
- 删除表(字段也删了)？
  - drop table 表名；//这个是通用的
  - drop table if exist 表名；//oracle不支持这种写法
关于表结构的修改
- 对于表结构得修改，这里不讲了，大家使用工具完成即可，因为在实际的开发中表一旦设计好，对表的修改是很少的，修改表结构就是对之前设计进行否定，即使需要修改表结构，我们也可以直接使用工具操作，修改表结构的语句不会出现在java代码中，出现在java代码中的sql包括：insert、delete、update、select(这些都是表中的数据操作)
- 增删改查有一个术语：CRUD操作
  - Create(增)、Retrieve(检索)、Uodate(修改)、Delete(删除)
约束(Constraint)
- 什么是约束，常见的约束有哪些？
  - 在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是保证数据的合法性、有效性、完整性
  - 常见的约束有哪些：
    - 非空约束(not null)：约束的字段不能为NULL
    - 唯一约束(unique)：约束的字段不能重复
    - 主键字段(primary key)：约束的字段不能为NULL，也不能重复(简称PK)
    - 外键约束(foreign key)：.....(简称PK)
    - 检查约束(check)：注意oracle数据库有check约束，但是mysql没有，目前mysql不支持该约束
- 非空约束：not null
  - drop table if exists t_user;
  - create table t_user(
    - id int,
    - username varchar(255) not null,
    - password varchar(255)
  - );
- 唯一约束
  - 唯一约束修饰的字段具有唯一性，不能重复，但可以为null，null也可以重复
  - 给一个列添加唯一约束
    - create table t_user(
      - id int,
      - username varchar(255) unique,
      - password varchar(255)
      - );
  - 给两个列或者多个列添加唯一约束
    - create table t_user(
      - id int,
      - username varchar(255) unique,//列级约束
      - password varchar(255) unique
      - );
    - create table t_user(
      - id int,
      - username varchar(255),
      - password varchar(255),
      - unique(username,password)//表级约束
      - );
    - 第一种情况是username或password单独都不能重复，第二种情况是username和password两个字段联合起来不能重复
    - 第一种属于列级约束，第二种属于表级约束
- 主键约束
  - 怎么给一张表添加主键约束？
    - create table t_user(
      - id int primary key,
      - username varchar(255),
      - password varchar(255)
      - );
  - id是主键，因为添加了主键约束，主键字段的数据不能为null，也不能重复，主键的特点：不能为NULL，也不能重复
  - 主键的相关术语？
    - 主键约束 primary key
    - 主键字段 id
    - 主键值 1
  - 主键有什么作用
    - 表的设计三范式中有要求，第一范式就要求任何一张表都应该有主键
    - 主键的作用：主键值是这行记录在这张表中的唯一标识(就像一个人的身份证号码一样)
  - 主键的分类？
    - 根据主键字段的字段数量来划分
      - 单一主键(推荐的，常用的)
      - 复合主键(多个字段联合起来添加一个主键约束)(复合主键不建议使用，因为复合主键违背三范式)
    - 根据主键性质来划分
      - 自然主键
      - 业务主键
    - 一张表的主键约束只能有一个(必须记住！)
  - 主键有列级约束和表级约束方式，表级约束是表中字段联合起来为主键
    - create table t_user(
      - id int,
      - username varchar(255),
      - password varchar(255),
      - primary key(id,username)//表级约束
      - );
  - 主键自增
    - create table t_user(
      - id int primary key auto_increment,
      - username varchar(255)
      - );
    - 提示：oracle中也提供了一个自增机制，叫做序列(sequence)
- 外键约束
  - 关于外键约束的相关术语
    - 外键约束：foreign key
    - 外键字段：添加有外键约束的字段
    - 外键值：外键字段中的一个值
  - 业务背景
    - 请设计数据库表，用来维护学生和班级信息
      - 第一种方式，存储所有数据
        
        no name classno classname
        
        --------------------------------------------------------
        
        1 zs 101 一班
        
        2 lisi 101 一班
        
        3 wu 102 二班
        
        缺点：冗余
      - 第二种方式：设计学生表和班级表
        
        学生表t_student
        
        sno(pk) sname cno
        
        -----------------------------------
        
        1 zs 101
        
        2 lisi 101
        
        3 wu 102
        
        班级表t_class
        
        classno(pk) cname
        
        ------------------------------
        
        101 一班
        
        102 二班
        
        t_student中的classno字段引用t_class表中的cno字段，此时t_student表叫做子表，t_class叫做父表
        
        外键中的值必须和父表的主键值一样，不能有其他的值
        
        顺序要求
        
        删除数据的时候，先删除子表，再删除父表
        
        添加数据的时候，先添加父表，再添加子表
        
        创建表的时候，先创建父表，再创建子表
        
        删除表的时候，先删除子表，再删除父表
        
        外键可以为null
        
        外键字段必须引用其他表的某个字段的时候，被引用的字段必须是主键吗？
        
        被引用的字段不一定是主键，但至少一定具有unique约束，不然不合理
存储引擎(engine)
- show create table 表名：不仅可以查看创建表的sql语句，还可以查看表的存储引擎和字符编码等信息
- 完整的建表语句：
  - CREATE TABLE `t_class` (
    `classno` int(11) NOT NULL default '0',
    `cname` varchar(255) default NULL,
    PRIMARY KEY (`classno`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8；
  - 注意：mysql中凡是标识符是可以使用飘号括起来的，最好别用，不通用
  - 建表时候可以指定存储引擎，也可以指定字符集
  - mysql默认使用的存储引擎是InnoDB方式
  - 默认采用的字符集是UTF-8
- 什么是存储引擎呢
  - 存储引擎这个名字只是在mysql中存在的(Oracle中又对应的机制，但是不叫做存储引擎，Oracle中没有特殊的名字，就是“表的存储方式”)
  - mysql支持很多存储引擎，每一个存储引擎都对应了一种不同的存储方式
  - 每一个存储引擎都有自己的优缺点：需要在合适的时机选择合适的存储引擎
- 查看当前mysql支持的存储引擎？
- 常见的存储引擎
  - MyISAM
    - Engine: MyISAM
    - Support: YES
    - Comment: MyISAM storage engine
    - Transactions：NO
    - XA：NO
    - Savepoints：NO
    - -------------------------------------------------------------------------------------------------------------
    - MyISAM是Mysql最常用的存储引擎，但是这种引擎不是默认的
    - MyISAM存储引擎不支持事务
    - MyISAM采用三个文件组织一张表：
      - xxx.frm(存储格式的文件)
      - xxx.MYD(存储表中数据的文件)
      - xxx.MYI(存储表中索引的文件)
    - 优点：可被压缩，节省存储空间，并且可以转换为只读表，提高检索效率
    - 缺点：不支持事务
  - InnoDB
    - Engine: InnoDB
    - Support: DEFAULT
    - Comment: Supports transactions, row-level locking, and foreign keys
    - Transactions：YES
    - XA：YES
    - Savepoints：YES
    - ------------------------------------------------------------------------------------------------------------
    - 优点：支持事务，行级锁，外键等，这种存储引擎最安全，数据的安全得到保障
    - 表的结构存储在xxx.frm文件中
    - 数据存储在tablespace这样的表空间中(逻辑概念)，无法被压缩，无法换成只读
    - 这种InnoDB存储引擎在MySQL数据库崩溃之后提供自动恢复机制
    - InnoDB支持级联删除和级联更新
  - MEMORY
    - Engine: MEMORY
    - Support: YES
    - Comment: Hash based, stored in memory, useful for temporary tables
    - Transactions：NO
    - XA：NO
    - Savepoints：NO
    - ------------------------------------------------------------------------------------------------------------
    - 缺点：
      - 不支持事务
      - 断电数据容易丢失，因为所有的数据和索引都存储在内存当中
    - 优点：因为数据存储在内存，查询速度最快
事务(Transaction)
- 什么是事务
  - 一个事务是一个完整的业务逻辑单元，不可再分
  - 比如：银行账户转账，从A账户转向B账户10000元需要执行两条查询语句
    - update t_act set balance =balance-10000 where actno='act-001';
    - update t_act set balance =balance+10000 where actno='act-002';
    - 以上两条DML语句必须同时成功，或者同时失败，不允许出现一条成功一条失败
    - 要想保证以上两条DML语句同时成功或者同时失败，那么就需要使用数据库的“事务机制”
  - 和事务相关的语句只有：DML语句(insert delete update)
    - 为什么？因为他们这三个语句都是和数据库表当中“数据”想关的
    - 事务的存在是为了保证数据的完整性，安全性
  - 假设所有的业务都能使用一条DML语句完成，还需要事务机制吗？
    - 不需要
    - 但实际情况不是这样的，通常一个事儿(业务)，需要很多条DML语句共同联合完成
- 事务的特性
  - 事务包括四大特性：ACID
    - A：原子性：事务是最小的工作单元，不可再分
    - C：一致性：事务必须保证多条DML语句同时成功或失败
    - I: 隔离性：事务A与事务B之间具有隔离性
    - D: 持久性：持久性说的是最终数据必须持久化到硬盘文件中，事务才算成功
- rollback和commit：
  - rollback表示将操作记录不与硬盘数据接触，硬盘数据没有改变，最后操作记录清除了
  - commit表示操作记录和底层硬盘接触了，硬盘数据修改了，最后操作记录清除
- 事务的隔离性
  - 事务隔离性存在隔离级别：理论上包括4个
    - 第一级别：读未提交(read uncommitted),对方事务未提交，当前事务可以读到对方未提交的数据
    - 第二级别：读已提交(read committed),对方事务提交之后的数据，当前数据才可以读取到
    - 第三级别：可重复读(repeatable read)
    - 第四级别：序列化读/串行读：解决了所有问题，但效率低
    - oracle数据库默认的隔离级别是：读已提交
    - mysql数据库默认的隔离级别是：可重复读
- 演示事务
  - mysql事务默认情况下是自动提交的
    - 什么是自动提交？
      - 只要执行任意一天DML语句则提交一次，怎么关闭自动提交？start transaction
      - 执行insert delete update都是显示提交，执行其它的例如create、drop、select等都是隐式提交
  - 演示读未提交
    - - 设置全局的事务隔离级别是读未提交，以后mysql就都是这种隔离方式，关闭窗口重新打开还是
    - - 查看事务的全局隔离级别
  - 演示读已提交
  - 演示可重复读
  - 演示serializable
索引
- 什么是索引？有什么用？
  - 相当于一本书的目录，通过目录可以快速找到对应的资源
  - 在数据库方面，查询一张表的时候有两种检索方式：
    - 第一种方式：全表扫描
    - 第二种方式：根据索引检索
    - 索引为什么可以提高检索效率呢？
      - 其实最根本的原因是缩小了扫描的范围
      - 索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中的对象，也需要数据库的不断维护，是由维护成本的，比如，表中的数据经常被修改，这样就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护
    - 添加索引是给某一字段，或者某些字段添加索引
      - select ename,sal from emp where ename='SMITH';
      - 当ename字段上没有添加索引的时候，以上sql语句会进行全表扫描，扫描ename字段中所有的值
      - 当ename字段上添加索引的时候，以上sql语句会根据索引扫描，快速定位
- 怎么创建索引对象？怎么删除索引对象？
- 什么时候考虑给字段添加索引？
  - 数据量庞大
  - 该字段很少的DML操作
  - 该字段经常出现在where子句中
- 注意：主键和具有unique约束的字段自动会添加索引
  - 根据主键查询效率较高，尽量根据逐渐检索
- - explain命令可以查看该条sql语句的执行情况
  - type为all表示全表扫描
- 给薪资sal字段添加索引？
  - 创建索引
    - - create index 索引名称 on 表名(字段名);
- 删除所有
  - drop index 索引名称 on 表名
- 索引底层采用的数据结构：B+Tree
  - 学习索引的底层实现原理？？？？？？
- 索引的分类：
  - 单一索引：给单个字段添加索引
  - 复合索引：给多个字段联合起来添加1个索引
  - 主键索引：主键上会自动添加索引
  - 唯一索引：有unique约束的索引的字段上会自动添加索引
  - .......
- 索引什么时候会失效？
  - select ename from emp where ename like '%A%';
  - 因为不知道第一个字母是什么所以索引失效
视图(view)
- 什么是视图
  - 图一张表的数据，通过不同的角度看待
- 怎么创建视图
  - create view myview as select empno,ename from emp;
  - 只有DQL语句才能以使徒的方式创建出来
- 怎么删除视图
  - drop view myview;
- 对视图进行增删改查会影响到原表数据(通过视图影响原表数据，不是直接操作原表)
  - 可以对视图进行CRUD操作
- 面向视图操作
- 创建表备份
- 视图的作用
  - 视图可以隐藏表的实现细节，保密级别较高的系统，数据库只对外提供相关的视图，java程序员只对视图对象进行crud操作
数据库数据的导入导出(DBA命令)
- 将数据库当中的数据导出
  - 在windows的命令窗口中执行
  - mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p123456(导出整个库)
  - mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot -p123456(导出指定数据库当中的指定表)
- 导入数据
  - source D:\bjpowernode.sql
数据库设计三范式
- 三范式有哪些
  - 第一范式
    - 任何一张表都应该有主键，并且每一个字段的原子性不可再分
  - 第二范式
    - 建立在第一范式基础之上，所有非主键字段完全依赖主键(一对一)，不能产生部份依赖(一对多、多对多)
    - 多对多？三张表关系表两个外键
    - t_student学生表
    - sno(pk) sname
    - --------------------------
    - 1 zs
    - 2 lisi
    - 3 wu
    - t_teacher讲师表
    - tno(pk) tname
    - ---------------------------------
    - 1 wang
    - 2 zhang
    - 3 li
    - t_student_teacjer_relation学生讲师关系表
    - id(pk) sno tno
    - ---------------------------------------------
    - 1 1 3
    - 2 1 1
    - 3 2 2
    - 4 2 3
    - 5 3 1
    - 6 3 3
  - 第三范式：
    - 所有非主键字段直接依赖主键字段，不能产生传递依赖
- 根据三范式设计的表不存在数据冗余

posted @ 2023-09-06 09:46 媛~~ 阅读(6) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

卫欣媛

数据库

公告