盒子
盒子
文章目录
  1. SQL概述
  2. 什么是数据库
  3. MySql概述
  4. 命令开启MySql服务
  5. 命令停止MySql服务
  6. 表
  7. SQL的分类
  8. 导入演示数据
  9. 命令中出现短路问题(出现—>)
  10. 表描述
  11. 常用命令
  12. 简单查询
  13. 条件查询
  14. 排序
  15. 数据处理函数/单行处理函数
  16. 日期处理_str_to_date
  17. 日期处理_date_format
  18. 分组函数/聚合函数/多行处理函数
  19. distinct
  20. 分组查询
  21. DQL语句的总结
  22. 连接查询
    1. 内连接
    2. 外连接【只掌握SQL99语法】
  23. 子查询
  24. union
  25. limit
  26. 创建表
  27. 约束
  28. 级联更新和删除
  29. 存储引擎
  30. 事务
    1. 概述
    2. 事务的提交与回滚演示
    3. 事务的四个特性之一：隔离性(isolation)
    4. 设置事务的隔离级别
    5. 查看隔离级别
    6. 并发事务与隔离级别示例
  • 索引【了解】
  • 视图
  • DBA命令
  • 数据库设计三范式

    • MySql笔记

    2019.06.26 Jzh 数据库  热度 ℃

    SQL概述

    • SQL全称是Structured Query Language（关系查询语句），SQL用来完成和数据库的通信，SQL是一套标准。
    • 每个数据库都有自己的特性别的数据库没有，当使用这个数据库特性相关的功能，这时SQL语句可能就不是标准了.（90%以上的SQL都是通用的）

    什么是数据库

    • 数据库
      • 通常是一个或一组文件，保存了一些符合特定规格的数据
      • 数据库对应的单词是DataBase，简称：DB.
    • 数据库管理系统
      • 数据库软件称为数据库管理系统，全称为DataBase Management System，简称：DBMS.
      • 如Oracle、SQL Server、MySql、DB2等.
    • SQL、DB、DBMS三者关系
      • 开发者书写SQL语句，DBMS执行SQL语句，操作DB中的数据.

    MySql概述

    • MySQL最初是由“MySQL AB”公司开发的一套关系型数据库管理系统.
    • 在2008年初，Sun Microsystems收购了MySQL AB公司.
    • 在2009年，Oracle收购了Sun公司，使MySQL并入Oracle的数据库产品线.

    命令开启MySql服务

    1
    net start mysql

    命令停止MySql服务

    1
    net stop mysql

    表

    • 表（table）是一种结构化的文件，可以用来存储特定类型的数据.

    • 表有特定的名称，而且不能重复.

    • 表中有几个概念：列、行、主键.列叫做字段（Column），行叫做表中的记录，每一个字段都有：字段名称/字段数据类型/字段约束/字段长度.

      学号(主键) 姓名 性别 年龄
      001 张三 男 20
      002 李四 女 20

    • 表格是数据库的最基本组成单元.

    SQL的分类

    • 数据查询语言（DQL-Data Query Language）

      • 代表关键字：select

    • 数据操纵语言（DML-Data Manipulation Language）

      • 代表关键字：insert，delete，update
      • 这里的增删改是针对表中记录.

    • 数据定义语言（DDL-Data Definition Language）

      • 代表关键字：create，drop，alter
      • 这里的增删改是针对表的结构.

    • 事务控制语言（TCL-Transactional Control Language）

      • 代表关键字：commit，rollback

    • 数据控制语言（DCL-Data Control Language）

      • 代表关键字：grant，revoke

    导入演示数据

    1. 进入数据库
      mysql -uroot -proot
    2. 创建一个数据库
      create database jzhdb;
    3. 使用数据库
      use jzhdb;
    4. 导入数据库文件 jzhdb.sql
      source 将数据库文件直接拖拽到此处，将路径两边的引号删除再回车

    命令中出现短路问题(出现—>)

    \c 回车或ctrl+c

    表描述

    1. 查看当前数据库中表格
      show tables;
    2. 查看某个表的表结构
      desc 表名;

    常用命令

    • 查看mysql版本

      1
      2
      数据库管理系统外部查看：mysql --version/mysql -v
      数据库管理系统内部查看：select version();

    • 查询当前使用的数据库

      1
      select database();

    • 终止一条语句

      1
      如果想要终止一条正在编写的语句，可键入\c,比如出现了—>时.

    • 退出mysql

      1
      exit/quit/\q/ctrl+c

    • 创建数据库

      1
      create database [if not exists] 数据库名;

    • 删除数据库

      1
      drop database [if exists] 数据库名;

    • 查看数据库系统中有多少数据库

      1
      show databases;

    • 使用某个数据库

      1
      use 数据库名;

    • 查看当前数据库中多少表(先use使用某个数据库后)

      1
      show tables;

    • 在别的数据库中查看别的数据库的表

      1
      show tables from 另一个数据库名;

    • 查看表的创建语句

      1
      show create table 表名;

    • 查看表结构

      1
      desc 表名;

    简单查询

    mysql中sql语句不分大小写，语句以分号结尾，引号只用单引号，但是mysql数据库中支持双引号

    • 查询字段

      • 当前数据库中查询其他数据库中表的字段:select 字段名 from 数据库名.表名;
      • 当前数据库中查询其中表的字段：
        • 单个字段：select 字段名 from 表名;
        • 多个字段：select 字段1,字段2..字段n from 表名;
        • 所有字段：查询所有不建议用*，可读性差，并且编译时*会转换为所有字段名，效率差.
          1
          2
          select 全部字段名 from 表名;
          select * from 表名;

    • 查询字段时做计算

      • 直接计算：select empno,sal*12 from emp;
      • as重命名做计算的字段：select empno,sal*12 as yearsal from emp;
      • as可以省略

    • 记住，select时进行的sal*12等类似的操作，不会改变数据库中的信息值，只是显示出来的形式被改变了

    条件查询

    • 条件查询需要用到where语句，where必须放到from语句表的后面
      支持如下运算符

      运算符 说明
      = 等于
      <>或!= 不等于
      < 小于
      <= 小于等于
      > 大于
      >= 大于等于
      between…and… 两个值之间，等同于 >=and<=
      is null 为null(is not null不为空)
      and 并且
      or 或者
      in 包含，相当于多个or(not in 不在这个范围中)
      not not可以取非，主要用来is或in中
      like like称为模糊查询，支持%或下划线_匹配
      %匹配任意个字符
      下划线，一个下划线只匹配一个字符

    • 查找emp表中工资等于5000的工号，工名，工资(=,>=,<=,<,>,!=用法)
      select empno,ename,sal from emp where sal = 5000;
      执行顺序：from emp —— where sal = 5000 —— select empno，ename，sal

    • 找出工资大于等于3000并且小于等于5000(between…and…用法)

      • 应用在数字上面
        select empno,ename,sal from emp where sal >= 3000 and sal <= 5000;
        select empno,ename,sal from emp where sal between 3000 and 5000;
        用between的话效果等同，两边都是闭区间.
      • 应用在字符上面(了解)
        select ename from emp where ename between 'A' and 'L';
        用在字符上的话是左闭右开.

    • 找出员工补助是null的工名(is,not用法)

      • 错误写法
        select ename from emp where comm = null;
        前面说过，null不是一个值，他代表啥都没有，=后面是跟值的，不可行
        正确写法：select ename from emp where comm is null;
        不为空则：select ename from emp where comm is not null;

    • 找出工作岗位是java和mysql的员工(or用法)
      select ename from emp where job = 'java' or job = 'mysql';

    • 找到工资大于1800的员工,要求部门编号是20或者30(and用法以及and和or的优先级)

      • 错误写法
        select empno,ename,sal from emp where sal>1800 and depto = 20 or deptno = 30;
        and优先级大于or，所以这里是找到了sal>1800 and depto = 20的员工或deptno = 30的员工
      • 正确写法
        select empno,ename,sal from emp where sal>1800 and (depto = 20 or deptno = 30);

    • 找出工作岗位和manger和工作岗位是salesman的员工(in,not用法)
      select ename,job from emp where job in('manger','salesman');
      括号中不是区间，而是选项.
      select ename,job from emp where job not in('manger','salesman');
      加not，排除这两个岗位.

    • 找出所有名字里含有o的(like用法）
      select ename from emp where ename like '%o%';

    排序

    排序采用order by子句，order by后面跟上排序字段，排序字段可以放多个，多个采用逗号间隔，order by默认采用升序，如果存在where子句那么order by必须放到where语句的后面

    • 示例
      • select ename,sal from emp order by sal;
        查找ename和sal从emp表中，按照sal排序，默认asc升序排序.
      • select ename,sal from emp order by sal asc;
        查找ename和sal从emp表中，按照sal进行升序排序.
      • select ename,sal from emp order by sal desc;
        查找ename和sal从emp表中，按照sal进行降序排序.
      • select ename,sal from emp order by sal desc，ename asc;
        如果按sal降序排序，出现sal相同的，则按ename升序排序.

    数据处理函数/单行处理函数

    lower 转换小写
    upper 转换大写
    substr 取子串(substr(被截取的字符串，起始下标，截取的长度))
    length 取长度
    trim 去空格
    str_to_date(后面讲) 将字符串转换成日期
    date_format(后面讲) 格式化日期
    format(几乎不用) 设置千分位
    round 四舍五入
    rand() 生成随机数
    ifnull 可以将null转换成一个具体值

    • 转小写：select lower(ename) as ename from emp;
      如果数据库中存放的ename都是大写，这样的话查找显示出来就是小写了，但注意，数据库中还是大写，只是改变了显示方式，前面说过.
      还有这里一定要用as取个别名，不然显示出来是lower(ename),不好看

    • 转大写：select upper(ename) as ename from emp;

    • 截取字符串：select substr(ename,2,3) from emp;
      截取查询出来的ename值的字符串，从下标2开始截取3个显示.
      查找员工名字第二个字符串为A的：select ename from emp where substr(ename,2,1) = 'A';

    • 获取字符串长度：select length(ename) as enamelength from emp;

    • 去空格：select ename from emp where ename = trim(' SMITH ');
      有时候查找ename等于SMITH的时候，多输入了空格查不到，所以用trim

    • round(被四舍五入的数，四舍五入到哪一位)

      • 默认:select round(123.456);
        显示123，默认0位，只留整数位
      • select round(123.456,1);
        显示123.5，保留一位小数
      • select round(123.456,-1);
        显示120，0位是123，-1位则去掉一位，去掉的地方变为0，所以120

    • rand():select rand();
      生成随机数

    • ifnull空值处理函数，比如客户需要让员工补助是null的显示为0
      select ename ,ifnull(comm,0) as comm from emp;
      因为在数据库中null参加运算的话，结果都为null，所以用这个函数处理
      select sal + ifnull(comm,100) from emp;
      比如计算年薪：select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;

    日期处理_str_to_date

    1. 数据库中的常用数据类型

      • int 整数型
      • varchar 可变长度字符串
      • char 定长字符串
      • date 日期类型
      • double 浮点型
      • ……

    2. 关于MYSQL中的日期处理

      • 每一个数据库处理日期的时候，采用的机制都是不同的，日期处理都有自己的一套机制.所以在实际的开发中，表中的字段定义为DATE类型，这种情况很少.因为一旦使用日期类型，那么java程序将不能够通用。所以在实际开发中，一般都会使用”日期字符串”来表示日期。
      • 日期是数据库本身的特色，也是数据库本身机制中的一个重要内容，所以还是需要掌握。

    3. MYSQL数据库管理系统中对日期的处理提供了两个重要的函数

      • str_to_date
      • date_format

    4. str_to_date

      • 该函数的作用是：将”日期字符串”转换成”日期类型”数据.【varchar ————> date】

      • 该函数的执行结果是DATE类型

      • 该函数的使用格式：

        str_to_date('日期字符串','日期格式')

      • 关于MYSQL中的日期格式：

        回顾java中的日期格式：
        yyyy    年
        MM         月
        dd         日
        HH         时
        mm         分
        ss         秒
        sss     毫秒

java中将字符串转换成日期类型：
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Date date = sdf.parse("1970-10-10");

MYSQL的日期格式：
        %Y         年
        %m         月
        %d         日
        %H         时
        %i         分
        %s         秒

        • 案例：查询出1981-12-17入职的员工,创建该表时设置的字段hiredate是DATE类型的

          `select ename,hiredate from emp where hiredate = '1981-12-17';`
这样本该不可以的，因为hiredate是DATE类型，而我们写的是字符串，但是他自动转型成DATE类型了.因为MYSQL默认的日期格式是：%Y-%m_%d,以上的日期字符串'1981-12-17'正好和默认的日期格式一样，存在了自动类型转换，自动将日期字符串转换成了日期类型，所以以上查询可以查询出结果.

`select ename,hiredate from emp where hiredate = '12-17-1980';`
这样会有警告【错误】发生，因为hiredate是date类型，'12-17-1980'是一个字符串，类型不匹配，而且他的格式和mysql默认的格式不同无法自动转型.

纠正以上SQL语句的错误：
`select ename,hiredate from emp where hiredate = str_to_date('12-17-1980','%m-%d_%Y');`

        • 案例

          + 创建学生表
create table t_student(
    id int(10),
    name varchar(32),
    birth date
);

+ 插入数据
insert into t_student(id,name,birth) values(1,'jhh','1996-10-20');

+ 以上可以执行，'1996-10-20'虽然是一个varchar类型，但是由于格式和MYSQL数据库默认的日期格式相同，所以存在自动类型转换.

+ 插入数据
insert into t_student(id,name,birth) values(2,'jzh','10-20-1996');
以上发生错误：
        第一：'10-20-1996'日期字符串格式和mysql默认格式不同.
        第二：'10-20-1996'是varchar类型，birth字段需要date类型，类型不匹配.

纠正以上错误：
        insert int t_student(id,name,birth) values(2,'jzh',str_to_date('10-20-1996','%m-%d-%Y'));

        • str_to_date函数通常使用在插入操作中，字段是date类型，不接受字符串varchar类型，需要先通过该函数将varchar变成date再插入数据，才能成功.

    日期处理_date_format

    1. 该函数的作用是：将日期类型date转换成具有特定格式的日期字符串varchar【date ——————> varchar】

    2. 该函数的运算结果是：varchar类型【具有特定格式的】

    3. 该函数的语法格式：date_format(日期类型数据,’日期格式’)

    4. 回顾java中的日期格式化：
      SimpleDateFormat sdf = new SimpleDateFormat(“yyyy-MM-dd HH:mm:ss”);
      Date nowTime = new Date();
      String strTime = sdf.format(nowTime);

    5. 案例：查询员工的入职日期，以’10-20-1996’的格式显示到窗口中.
      select ename,date_format(hiredate,'%m-%d-%Y') as hiredate from emp;

    6. 案例：查询员工的入职日期，以’10/20/1996’的格式显示到窗口中.
      select ename,date_format(hiredate,'%m/%d/%Y') as hiredate from emp;

    7. 以下两个DQL语句的执行结果相同：
      select ename,date_format(hiredate,'%Y-%m-%d') as hiredate from emp;
      select ename,hiredate from emp;【hiredate自动转换成varchar类型，采用’%Y-%m-%d’格式】
      打印出来一样的，因为mysql帮我们做了自动类型转换，显示给我们的date类型自动转换成了字符串.

    8. 结论：date_format函数主要使用在查询操作中，客户需要日期以特定格式展示的时候，需要使用该函数.

    分组函数/聚合函数/多行处理函数
    count 取得记录数
    sum 求和
    avg 取平均
    max 取最大的数
    min 取最小的数

    • 单行处理函数和多行处理函数的区别
      select lower(ename) from emp;:显示的是ename字段的所有记录的小写形式，lower函数一次只能针对一个记录将它处理返回一个小写显示出来，多少条记录就处理多少次.
      select sum(sal) from emp;:显示的是sal字段的所有记录相加的总和，一共就一行记录，该函数一次将所有人的工资相加求和返回一个总数，用到了sal字段的所有记录.

    • 用法示例

      • select avg(sal) from emp;
        将sal字段所有记录取平均数显示出来
      • select count(ename) from emp;
        统计ename字段多少的记录.
      • select max(sal) from emp;
        找出sal最大的那一个
      • select min(sal) from emp;
        找出sal最小的那一个

    • count(字段名)和count(*)的区别

      • count(字段名)表示当前字段记录不为null的总数
      • select count(*) from emp where ename is null;   符合条件的所有记录总数(包含null).
      • select count(*) from emp；   emp表有多少行记录就显示多少.

    • 分组函数不能使用在where关键字后面(不解释记住)

    • 注意：分组函数自动忽略空值，不需要手动的加where条件排除空值.
      select count(*) from emp where xxx;   符合条件的所有记录总数.

    distinct

    • select distinct job from emp；

      • 去除job字段重复记录
      • 只是将查询结果去重显示，表中记录不会被更改

    • select ename,distinct job from emp;

      • 该语句不可行，会报错
      • distinct只能出现在所有字段的最前方，他的前方不能有任何字段
      • distinct后面的字段不论有几个，都视为一组，比如：
        • select distinct job,ename from emp;中job和ename视为一组，意思是这两个人的job和ename必须都是一样才算重复，只有一个一样另一个不一样不算重复.

    分组查询

    • 分组查询主要涉及到两个子句，分别是：group by和having

    • group by

      • 含义

        order by：表示通过哪个或者哪些字段进行排序.
group by：表示通过哪个或者哪些字段进行分组.

      • 案例：找出每个工作岗位的最高薪水【先按照工作岗位分组，使用max函数求每一组的最高薪水】

        1
        2
        3
        4
        5
        6
        7
        8
        9
        10
        11
        12
        13
        14
        15
        select
        	max(sal)
        from 
        	emp
        group by
        	job;
        以上的SQL语句表示，先按照job分组，然后对每一组使用max(sal)求最高薪水.

        select
        	job,max(sal)
        from 
        	emp
        group by
        	job;
        job写在select后面，在这里可以的，job被分组了，每个job的最高薪水都能对应上，写别的字段就不行了，虽然不会报错，但是对不上.
        • 重点：若一条DQL语句当中有group by子句，那么select关键字后面只能跟参与分组的字段和分组函数.
        • 案例：计算每个部门的平均薪水【按照部门编号分组，对每一组求平均薪水】
          select deptno,avg(sal) as avgsal from emp group by deptno;
        • 案例：计算不同工作岗位的最高薪水
          select job,max(sal) from emp group by job;
        • 案例：计算不同部门中不同工作岗位的最高薪水
          select deptno,job,max(sal) group by deptno,job;
          这里和distinct差不多，后面的deptno,job两个字段表示一组，联合起来进行分组，比如工作岗位一样，但是部门不同，则不算是一组.
        • 找出每个工作岗位的最高薪水，除MANAGER岗位之外.
          select job,max(sal) from emp where job<> 'MANAGER' group by job;
        • 找出每个工作岗位的平均薪水，要求显示平均薪水大于1500.
          select job,avg(sal) from emp where avg(sal)>1500 group by job;
          注意，之前说过where后面不能跟分组函数，所以这是错误的，之前没有解释为啥不能跟分组函数，这里就可以明白了，这里sql语句的顺序是from emp————>where avg(sal)>1500———>group by job,因为分组函数首先得先分组，还没有group by呢，所以不能在where后面用分组函数.这里我们就要用到having了.

    • having

      • having和where都是为了完成数据过滤的.

      • where和having后面都是添加条件.

      • where在group by之前完成过滤.(执行顺序先where在group by)

      • having在group by之后完成过滤.(执行顺序先group by再having)

      • 原则：能用where尽量不用having,效率高低不同，where无法过滤的数据通常都是先分组之后再过滤的，这个时候可以选择having.

      • 刚刚那个案例找出每个工作岗位的平均薪水，要求显示平均薪水大于1500.
        select job,avg(sal) from emp group by job having avg(sal)>1500;

    DQL语句的总结

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    select
    		...
    from
    		...
    where
    		...
    group by
    		...
    having
    		...
    order by
    		...

    第一：以上的关键字顺序不能变，严格遵守
    第二：执行顺序：
    		1. from 		从某张表中检索数据
    		2. where 		经过某条件进行过滤
    		3. group by 	然后分组
    		4. having 		分组之后不满意再过滤
    		5. select 		查询出来
    		6. order by 	排序输出
    第三：这里我们只用到了单表查询，后面开始多表查询(表的连接查询).

    连接查询

    1. 什么是连接查询？

      • 查询的时候只从一张表检索数据，被称为单表查询.
      • 在实际的开发中，数据并不是存储在一张表中的，是同时存储在多张表中，这些表和表之间存在关系，我们在检索的时候通常是需要将多张表联合起来取得有效数据，这种多表查询被称为连接查询或者叫做跨表查询.

    2. 连接查询根据出现年代分类：

      • SQL92【1992】
      • SQL99【1999：更新的语法，我们主要掌握这种语法】

    3. 连接查询根据连接方式可以分为：

      • 内连接(掌握)
        • 等值连接
        • 非等值连接
        • 自连接
      • 外连接(掌握)
        • 左外连接(左连接)
        • 右外连接(右连接)
      • 全连接【几乎不用】

    内连接

    1. 当多张表进行连接查询，若没有任何条件进行限制，会发生什么现象？

    提示：当我们多张表连接查询时，最好给表起别名，防止两个表有相同字段

    案例：查询每一个员工所在的部门名称，要求最终显示员工名和对应的部门名.(员工表emp中只有员工姓名和部门所在编号，部门表dept只有部门名和对应部门编号)

    主要分析：多张表连接查询，若没有条件限制，会发生什么？
    select e.ename,d.dname from emp e,dept d;
    结果：e.ename的每一条记录都和d.dname中每一条记录组合成了一条记录.

    ename dname
    SMITH ACCOUNTING
    SMITH RESEARCH
    SMITH SALES
    SMITH OPERATIONS
    WARD ACCOUNTING
    WARD RESEARCH
    WARD SALES
    WARD OPERATIONS
    JONES ACCOUNTING
    JONES RESEARCH
    JONES SALES
    JONES OPERATIONS
    … …

    结论：若两张表进行连接查询的时候，没有任何条件限制，最终的查询结果总数是两张表记录条数乘积，这种现象被称为笛卡尔积现象.为了避免这种现象发生，必须在进行表连接的时候添加限制条件.

    1. 案例：查询每一个员工所在的部门名称，要求最终显示员工名和对应的部门名.(员工表emp中只有员工姓名和部门所在编号，部门表dept只有部门名和对应部门编号)

      • SQL92语法：内连接中的等值连接
        select e.ename,d.dname from emp e, dept d where e.deptno = d.deptno;

      • SQL99语法：内连接中的等值连接(内连接此处的inner可写可不写，on后面写过滤条件)
        select e.ename,d.dname from emp e inner join dept d on e.deptno = d.deptno;

      • 更多用SQL99，SQL92中把表连接的部分(e.deptno = d.deptno)放在了where后面，这样再进行过滤的话只能在后面继续加and等其他了，而SQL99直接把表连接部分从where中分离出来(join dept d on e.deptno = d.deptno)，结构更清晰了，对表连接不满意的话可以再追加where进行过滤——select e.ename,d.dname from emp e 表连接 where xxx;

    2. 案例：找出每一个员工对应的工资等级，要求显示员工名，工资，工资等级(emp员工表有ename和sal，salgrade工资等级表有grade，losal<最低工资>，hisal<最高工资>)

    如果不限制条件，则发生笛卡尔积现象.

    SQL92语法：内连接中的非等值连接
    select e.ename,e.sal,s.grade from emp e，salgrade s where e.sal between s.losal and s.hisal;

    SQL99语法：内连接中的非等值连接(内连接此处的inner可以省略)
    select e.ename,e.sal,s.grade from emp e inner join salgrade s on e.sal between s.losal and s.hisal;

    1. 案例：找出每一个员工的上级领导，要求显示员工名以及对应的领导名

    首先我们先查看一下emp表中每个员工对应的领导编号：select empno,ename,mgr from emp;

    empno ename mgr
    7369 SMITH 7902
    7499 ALLEN 7698
    7521 WARD 7698
    7566 JONES 7839
    7654 MARTIN 7698
    7698 BLAKE 7839
    7782 CLARK 7839
    7788 SCOTT 7566
    7839 KING NULL
    7844 TURNER 7698
    7876 ADAMS 7788
    7900 JAMES 7698
    7902 FORD 7566
    7934 MILLER 7782

    发现：该表中员工对应的领导编号，直接就可以通过该表的员工号找到对应的领导.比如SMITH的领导编号是7902，而7902是FORD的员工编号，直接就可以在自己的这个表中找到了.

    那么我们可以把这一张emp表看成两张表，先看成员工表a，再看成领导表b

    emp<员工表a>

    empno ename mgr
    7369 SMITH 7902
    7499 ALLEN 7698
    7521 WARD 7698
    7566 JONES 7839
    7654 MARTIN 7698
    7698 BLAKE 7839
    7782 CLARK 7839
    7788 SCOTT 7566
    7839 KING NULL
    7844 TURNER 7698
    7876 ADAMS 7788
    7900 JAMES 7698
    7902 FORD 7566
    7934 MILLER 7782

    emp<领导表b>
    这里我只列出是领导的人的员工号和名字,其他的我省略了没列出来.为了更清晰.

    empno ename
    7566 JONES
    7698 BLAKE
    7782 CLARK
    7788 SCOTT
    7839 KING
    7902 FORD

    SQL92语法：内连接中的自连接(一张表看成两张表)
    select a.ename empname,b.ename leadername from emp a，emp b where a.mgr = b.empno;

    SQL99语法：内连接中的自连接(一张表看成两张表，内连接中的inner可以省略)
    select a.ename empname,b.ename leadername from emp a join emp b on a.mgr = b.empno;

    结果：

    empname leadername
    SMITH FORD
    ALLEN BLAKE
    WARD BLAKE
    JONES KING
    MARTIN BLAKE
    BLAKE KING
    CLARK KING
    SCOTT JONES
    TURNER BLAKE
    ADAMS SCOTT
    JAMES BLAKE
    FORD JONES
    MILLER CLARK

    外连接【只掌握SQL99语法】

    1. 案例：找出每一个员工对应的部门名称，要求部门名称全部显示

    • 内连接：
      a表和b表连接的时候，如果说连接的记录能够百分百匹配出来，也就是说比如查询a表的姓名与b表中该姓名对应的年龄有具体对应，而不是在b表中没有这个人的对应的年龄记录，如果没有对应记录的话用内连接那么没有对应记录的不会显示出来，只会显示有对应记录的，所以我们在能百分百匹配的查询连接中用内连接.

    • 外连接：
      a表和b表连接的时候，除了能百分百匹配的，还有一些没有匹配的，比如上面的a表的姓名对应b表该姓名对应的年龄如果没有的话，用内连接查询会不显示出来，而我们也想让这些没有匹配的显示出来，那就只能用外连接.没有匹配的一般都是对应一个null.所以外连接的查询结果记录条数大于内连接的查询结果记录条数.

    • 明白了外连接，那么可以理解左外连接就是将左边表的数据全部显示出来，右外连接就是将右边表的数据全部显示出来.

    • 该案例sql语句写法：
      SQL99语法：外连接中的右外连接(外连接的outer可以省略)
      select e.ename,d.dname from emp e right outer join dept d on e.deptno = d.deptno;
      这个案例是将部门名称全部显示，部门名称在dept表中，这个dept表在右边，所以我们用右连接，显示所有部门名称，其中部门名称没有对应的员工的话以null代替.

    SQL99语法：外连接中的左外连接(外连接的outer可以省略)
    select e.ename,d.dname from dept d left outer join emp e on e.deptno = d.deptno;
    这个案例是将部门名称全部显示，部门名称在dept表中，这个dept表在右边，所以我们用右连接，显示所有部门名称，其中部门名称没有对应的员工的话以null代替.

    结论：任何一个右外连接都可以写成左外连接，任何一个左外连接也同样可以写成右外连接

    • 为什么inner和outer可以省略，加上去有什么好处：
      可以省略，因为区分内连接和外连接依靠的不是这些关键字，而是看SQL语句中是否存在left/right
      加上去的好处是增强可读性.
    1. 找出每一个员工对应的领导名，要求显示所有的员工

    emp<员工表a>

    empno ename mgr
    7369 SMITH 7902
    7499 ALLEN 7698
    7521 WARD 7698
    7566 JONES 7839
    7654 MARTIN 7698
    7698 BLAKE 7839
    7782 CLARK 7839
    7788 SCOTT 7566
    7839 KING NULL
    7844 TURNER 7698
    7876 ADAMS 7788
    7900 JAMES 7698
    7902 FORD 7566
    7934 MILLER 7782

    emp<领导表b>
    这里我只列出是领导的人的员工号和名字,其他的我省略了没列出来.为了更清晰.

    empno ename
    7566 JONES
    7698 BLAKE
    7782 CLARK
    7788 SCOTT
    7839 KING
    7902 FORD

    select a.ename empname,b.ename leadername from emp a left join emp b on a.mgr = b.empno;

    1. 以上都是两张表，现在挑战多张表

    案例：找出每一个员工对应的部门名称，以及该员工对应的工资等级.要求显示员工名，部门名，工资等级.

    先查看员工对应的部门编号：select ename,deptno,sal from emp;

    ename deptno sal
    SMITH 20 800.00
    ALLEN 30 1600.00
    WARD 30 1250.00
    JONES 20 2975.00
    MARTIN 30 1250.00
    BLAKE 30 2850.00
    CLARK 10 2450.00
    SCOTT 20 3000.00
    KING 10 5000.00
    TURNER 30 1500.00
    ADAMS 20 1100.00
    JAMES 30 950.00
    FORD 20 3000.00
    MILLER 10 1300.00

    再查看每一个编号对应的部门名称：select deptno,dname from dept;

    deptno dname
    10 ACCOUNTING
    20 RESEARCH
    30 SALES
    40 OPERATIONS

    再查看工资等级：select * from salgrade;

    GRADE LOSAL HISAL
    1 700 1200
    2 1201 1400
    3 1401 2000
    4 2001 3000
    5 3001 9999

    多张表进行表连接的语法格式：

    select
    xxx
    from
    a
    join
    b
    on
    条件
    join
    c
    on
    条件;

    原理：并不是a和b连接后的结果去和c连接，而是a先和b进行表连接，然后再和c进行表连接.

    该案例sql写法：

    emp a
    dept d
    salgrade s

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    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;

    子查询

    1. 什么是子查询？

      子查询就是嵌套的 select 语句，可以理解为子查询是一张表

    2. 子查询可以出现在哪儿？

      select..(select).
      from..(select).
      where..(select).

    3. where后面使用select子查询

    案例：找出薪水比公司平均薪水高的员工，要求显示员工名和薪水.

    select ename,sal from emp where sal > avg(sal);
    以上语句执行报错，分组函数不能直接使用在where后面

    第一步：找出公司的平均薪水
    select avg(sal) from emp;

    avg(sal)
    2073.214286

    第二步：找出薪水大于平均薪水的员工信息
    select ename,sal from emp where sal > (select avg(sal) from emp);

    ename sal
    JONES 2975.00
    BLAKE 2850.00
    CLARK 2450.00
    1. from后面使用子查询【小窍门：将查询结果当做临时表】

    案例：找出每一个部门的平均薪水，并且要求显示平均薪水的薪水等级.

    第一步：找出每个部门的平均薪水
    select deptno,avg(sal) as avgsal from emp group by deptno;

    deptno avgsak
    10 2916.666667
    20 2175.000000
    30 1566.666667

    第二步:将上面的查询结果当做临时表t，t表和salgrade s表进行连接，条件：t.avgsal between s.losal and s.hisal

    1
    2
    3
    4
    5
    6
    7
    8
    select
    		t.deptno,t.avgsal,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;

    这里将t表的所有字段都取出来了，可以简写：

    1
    2
    3
    4
    5
    6
    7
    8
    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;
    1. 在select后面使用子查询【了解】

    select e.ename,(select d.dname from dept d where e.deptno=d.deptno) as dname from emp e;
    这里匹配的是每个员工对应的部门名称.

    union

    union可以合并查询结果集，就是将两条语句查询出来的结果显示相加到一起.

    比如：查询job为java和mysql，显示ename和job

    一种是：select ename,job from emp where job = 'java' or job = 'mysql';

    还有一种:select ename,job from emp where job in ('java','mysql');

    我们也可以单个单个的查：
    先select ename,job from emp where job = 'java';
    再select ename,job from emp where job = 'mysql';
    但是这样的话，显示出来是一个一个的，所以可以通过union将这两条语句合并：select ename,job from emp where job = 'java' union select ename,job from emp where job = 'mysql';然后查询，结果和前两种是一样的.

    注意：用union的话，前后两条select语句，他们的字段个数要相同

    limit

    1. limit用来获取一张表中的某部分数据

    2. limit只有在MYSQL数据库中存在，不通用，是MYSQL数据库管理系统的特色

    3. 案例：找出员工表中前5条记录
      select ename from emp limit 5;
      该SQL语句的”limit 5”中的5表示从表中记录下标0开始，取5条记录
      等同于：select ename from emp limit 0,5;

    limit的使用语法格式：
    limit 起始下标，长度
    起始下标没有指定，默认从0开始，0表示表中的第一条记录.

    1. 案例：找出公司中工资排名在前5名的员工【limit出现在SQL语句的最后位置上】
      思路：按照工资降序排列取前5个
      select ename,sal from emp order by sal desc limit 5;

    2. 案例：找出工资排名在【3-9】名的员工
      select ename,sal from emp order by sal desc limit 2，7;

    3. MYSQL中通用的分页SQL语句：
      每页显示3条记录
      第1页：0,3
      第2页：3,3
      第3页：6,3
      第4页：9,3
      ……
      每页显示pageSize条记录
      第pageNo页：(pageSize-1)*pageSize,pageSize

    通用的分页SQL【只适合于MYSQL数据库管理系统】

    1
    2
    3
    4
    5
    6
    7
    8
    select
    	t.*
    from
    	t
    order by
    	t.xxx desc/asc
    limit
    	(pageNo-1)*pageSize,pageSize;

    创建表

    • 表格(table)，用来存储数据，表格是一种结构化文件。

    • 表格行被称为记录(表中的数据)，表格列被称为字段。

    • 表格的字段属性包括：字段名称、字段数据类型、字段长度、字段约束

    • 不带约束的简单建表语法

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      create table tableName(
      	columnName dataType(length),
      	... ...
      	columnName dataType(length),
      );

      示例：
      	CREATE TABLE students (
        		sid char(10),
        		sname varchar(50),
        		sex char(1),
        		dob date,
        		phone varchar(30)
        	);

    • 关于MYSQL数据库中的数据类型

      • varchar
        可变长度字符串
varchar(3) 表示存储的数据不能超过3个字符长度
      • char
        定长字符串
varchar和char对比：
        都是字符串
        varchar比较智能，可以根据实际的数据长度分配空间，比较节省空间，但是在分配的时候需要执行相关的判断程序，效率较低
        char不需要动态分配空间，所以执行效率很高，但是可能会导致空间浪费。
        若字段中的数据不具备伸缩性，建议采用char类型存储。
        若字段中的数据具备很强伸缩性，建议采用varchar类型存储。
char(3) 表示存储的数据不能超过3个字符长度
      • int
        整数型
int(3) 表示最大可以存储999
      • bigint
        长整型
对应的java程序中的long类型
      • float
        浮点型单精度
      • double
        浮点型双精度
double(7,2) 7表示7个有效数字，2个小数位
      • date
        日期类型
在实际开发中为了通用，所以日期类型一般不用，采用字符串代替日期比较多
      • blob
        Binary Large OBject  二进制大对象
专门存储图片声音视频等数据
数据库表中存储一个图片是很常见的，但是存储一个比较大的视频是很少的，一般都是提供一个视频的链接地址
      • clob
        Character Large OBject  字符大对象
可以存储比较大的文本，4G+的字符串可以存储
      • 其他…

    • 创建表格【学生表】

      设计：建立学生信息表，字段包括：
    学号【int】
    姓名【varchar】
    性别【char】
    出生日期【date】
    邮箱地址【varchar】
建表语句：
create table t_student(
    no int(10),
    name varchar(32),
    sex char(1),
    birth date,
    email varchar(128)
);
注意：
    表格的名字最好以t_ 或者tbl_开始，增强可读性
    varchar长度最好是2的倍数，方便存储中文

+ 删除表格：
    drop table t_student;【这种删除方式，若数据库中没有这样的表格，报错】
    drop table if exists t_student;【最好采用这种方式，这是MYSQL的特色，其他数据库没有】

    • 向t_student表格中插入数据

      • 向表格插入数据必须使用insert语句，这属于DML语句
      • DML语句包括：insert,update,delete【增删改表中的数据，和表结构无关】
      • insert语句的语法格式：
        insert into tablename(columnname1,columnname2,columnname3...) values(value1,value2,value3...);
字段和值必须一一对应，个数必须相同，数据类型必须一致
      • 向t_student表格中插入数据
        insert into tablename(no,name,sex,birth,email) values(1,'jhh','m','1996-10-20','jhh.com');
insert into tablename(no,name,sex,birth,email) values(2,'jhh1','m','1996-10-20','jhh1.com');
      • 插入值时可不写任何字段，但是这样等于所有字段都加上，后面的values中的值要对应上所有字段，少了的话报错。

    • 插入中文试试？【在dos窗口中】
      insert into t_student(no,name) values(6,’王五’);
      插入失败，原因是dos窗口是GBK的编码方式，但是数据库表只能接受utf8
      怎么解决？

      使用navicat工具插入数据
使用navicat工具插入数据成功，在dos窗口中使用select语句查询的时候出现乱码，
怎么解决？
      修改查询结果集的显示编码方式，这里修改的不是dos窗口：
      set character_set_results = 'GBK';【只对当前会话有效果，就是指关掉dos窗口再重新开一个dos窗口进入查看就没有效果了】
查看MYSQL的相关字符编码方式：show variables like '%char%';

    • 关于创建表格的时候给字段添加默认值
      drop table if exists t_student;
      create table t_student(
      no int(10),
      name varchar(32),
      sex char(1) default ‘m’
      );
      insert into t_student(no,name) values(1,’jack’);
      insert into t_student(no,name) values(2,’lucy’);
      select * from t_student;

    • 获取系统当前时间
      使用now()函数可以获取系统当前时间
      select now()
      创建表：
      create table t_organizeation(
      code char(10),
      name varchar(32),
      createTime date
      );
      insert into t_organizeation(code,name,createTime) values(‘111111’,’江苏外汇局’,now());
      insert into t_organizeation(code,name,createTime) values(‘111112’,’河南外汇局’,now());
      insert into t_organizeation(code,name,createTime) values(‘111113’,’安徽外汇局’,now());
      insert into t_organizeation(code,name,createTime) values(‘111114’,’北京外汇局’,now());

    • 表的复制【快速创建表，并插入数据】
      create table emp1 as select * from emp;【创建的表和emp表一样】
      create table emp2 as select empno,ename from emp;【创建的表只有emp表的empno和ename字段的记录】
      语法结构：

      create table tablename as select columnname,... from tablename;

    • 将查询结果插入到某张表中
      insert into emp2 select * from emp2;(插入了查询出来的记录，前期两个表字段对应的，否则插入不进去)
      语法结构：

      insert into tablename select columnname,... from tablename;

    • 增/删/改 表结构【create，drop，alter属于DDL语句】
      drop table if exists t_student;
      create table t_student(
      no int(10),
      name varchar(32)
      );
      desc t_student;

    给t_student表格添加一个联系电话字段【增】
    alter table t_student add tel varchar(10);
    desc t_student;

    将t_student表格中的tel字段长度扩展到20个长度【改】
    alter table t_student modify tel varchar(20);
    desc t_student;

    将t_student表格中的tel字段删除【删】
    alter table t_student drop tel;
    desc t_student;

    • 增/删/改 表中的数据【insert，update，delete属于DML语句】
      • insert语法格式
        insert into 表名(字段…) values(值…);
      • update
        语法格式：update tablename set 字段名=字段值，字段名=字段值，字段名=字段值 where 条件;
        注意：update语句没有条件，会将一张表中所有的数据全部更新。
      • delete
        语法格式：delete from tablename where 条件;
        注意：delete语句没有条件，会将一张表中所有的数据全部删除。

    约束

    • 什么是约束，为什么要用约束

      • 约束对应的英语单词：constraint
      • 约束实际上就是表中数据的限制条件
      • 表在设计的时候加入约束的目的就是为了保证表中的记录完整和有效

    • 约束包括哪些

      • 非空约束 （not null）
      • 唯一性约束 （unique）
      • 主键约束 （primary key） 简称PK
      • 外键约束 （foreign key） 简称FK

    • 非空约束

      • not null约束字段，不能为null值，必须给定具体的数据
        创建表，给字段添加非空约束【创建用户表，用户名不能为空】
        drop table if exists t_user;
        create table t_user(
        id int(10),
        name varchar(32) not null,
        email varchar(128)
        );

    • 唯一性约束

      • unique约束的字段具有唯一性，不可重复
        创建用户，保证邮箱地址唯一
        create table t_user(
        id int(10),
        name varchar(32) not null,
        email varchar(128) unique
        );

        unique约束：约束的字段不能重复，但是可以为null，null不是一个值。
        以上都是列级约束，就是写在了字段后面的约束，还有表级约束：
        create table t_user(
        id int(10),
        name varchar(32) not null,
        email varchar(128)，
        unique(email)
        );

        //使用表级约束给多个字段联合添加约束【以下程序表示name和email两个字段联合唯一，就是说必须email相同的同时name也要相同，只有一个相同不算唯一】
        create table t_user(
        id int(10),
        name varchar(32) not null,
        email varchar(128)，
        unique(email，name)
        );
        //表级约束还可以给约束起名字
        //为啥要起名字？因为以后要通过这个名字来删除这个约束。
        create table t_user(
        id int(10),
        name varchar(32) not null,
        email varchar(128)，
        constraint t_user_email_unique unique(email)
        );
        //查询约束的名字
        use information_schema;
        show tables;
        desc table_constraints;【该表格专门用来存储约束信息的】
        select constraint_name from table_constraints where table_name = ‘t_user’;

    • not null 和 unique可以联合使用吗？

      • 可以联合使用
      • 被not null unique约束的字段，既不能为空，也不能重复

    • 主键约束-primary key-简称PK

      • 主键涉及到的术语：
        主键约束
        主键字段
        主键值

      • 以上的主键约束，主键字段，主键值的关系
        表中的某个字段添加主键约束之后，
        该字段被称为主键字段，主键字段中
        出现的每一个数据都被称为主键值

      • 给某个字段添加主键约束primary key之后，该字段不能重复，并且也不能为空，效果和”not null unique”约束相同，但是本质不同，主键约束除了可以做到”not null unique”之外，主键字段还会默认添加”索引-index”

      • 一张表应该有主键字段，若没有，表示这张表是无效的，”主键值”是当前行数据的唯一标识，”主键值”是当前行数据的身份证号，即使表中的两行记录相关的数据是相同的，但是由于主键值不同，我们认为这两行是完全不同的数据。

      • 给一个字段添加主键约束，被称为单一主键。
        //单一主键
        //列级约束
        drop table if exists t_user;
        create table t_user(
        id int(10) primary key,
        name varchar(32)
        );
        //单一主键
        //表级约束
        //起名
        drop table if exists t_user;
        create table t_user(
        id int(10),
        name varchar(32)，
        constraint t_user_id_pk primary key(id)
        );

      • 给多个字段联合添加一个主键约束，被称为复合主键(了解)
        //复合主键
        //表级约束
        drop table if exists t_user;
        create table t_user(
        id int(10),
        name varchar(32),
        email varchar(128),
        primary key(id,name)
        );

        //复合主键
        //表级约束
        //起名
        drop table if exists t_user;
        create table t_user(
        id int(10),
        name varchar(32)，
        email varchar(128),
        constraint t_user_id_name_pk primary key(id,name)
        );

      • 无论是单一主键还是复合主键，一张表主键约束只能有一个

    • 自增约束

    在MYSQL数据库管理系统中提供了一个自增的数字，专门用来自动生成主键值。主键值不需要用户维护，也不需要用户提供了，自动生成的，这个自增的数字默认从1开始，以1递增：1,2,3,4,5,6….

    drop table if exists t_user;
    create table t_user(
    id int(10) primary key auto_increment,
    name varchar(32)
    );
    insert into t_user(name) values(‘jzh’);
    insert into t_user(name) values(‘jch’);
    insert into t_user(name) values(‘jhh’);
    insert into t_user(name) values(‘jjh’);
    insert into t_user(name) values(‘jkh’);
    select * from t_user;

    • 外键约束 - foreigh key - 简称FK

      • 外键涉及到的术语：

        外键约束
外键字段
外键值

      • 外键约束、外键字段、外键值之间的关系？

        某个字段添加外键约束之后，该字段称为外键字段。外键字段中的每一个数据都是外键值。

      • 外键也分为：单一外键【给一个字段添加外键约束】和复合外键【给多个字段联合添加一个外键约束】

      • 一张表中可以有多个外键字段。

      • 分析场景：
        设计数据库表用来存储学生和班级信息，给出两种解决方案：
        学生信息和班级信息之间的关系，一个班级对应多个学生，这是典型的一对多的关系。

        第一种设计方案：将学生信息和班级信息存储到一张表中。
        学生信息表：t_student

        sno(pk) sname classno cname
        1 jack 100 高三1班
        2 lucy 100 高三1班
        3 king 200 高三2班
        4 ford 200 高三2班
        5 lisa 300 高三3班
        5 jam 300 高三3班

        以上设计的缺点：数据冗余

        第二种解决方案：将学生信息和班级信息分开两张表存储，学生表 + 班级表

        学生表：t_student

        sno(pk) sname classno(fk)
        1 jack 100
        2 lucy 100
        3 king 200
        4 ford 200
        5 lisa 300
        5 jam 300

        班级表：t_class

        cno(pk) cname
        100 高三1班
        200 高三2班
        300 高三3班

      结论：为了保证t_student表中的classno字段中的数据必须来自于t_class表中cno字段中的数据，有必要给t_student表中的classno字段添加外键约束，classno字段被称为外键字段，该字段中的100 200 300被称为为外键值。classno这里是一个单一外键字段

      注：外键值可以为null。

      注：外键字段去引用一张表的某个字段的时候，被引用的字段可以不是主键，但是必须要具有unique约束。

      注：有了外键引用之后，表分为父表和子表，以上父表是班级表，字表是学生表，创建表先创建父表，再创建子表，删除数据的时候先删除子表中的数据，再删除父表中的数据，插入数据的时候先插入父表中的数据，再插入子表中的数据。
      ——————————————————————SQL文———————————————————————

    1. 在MYSQL数据库中外键约束没有列级约束，只有表级约束
    2. 被引用的字段数据长度多少，外键字段长度也要一样

    drop table if exists t_student;
    drop table if exists t_class;
    create table t_class(
    cno int(3) primary key,
    cname varchar(128) not null unique
    );
    create table t_student(
    sno int(3) primary key,
    sname varchar(32) not null,
    classno int(3),
    constraint t_student_classno_fk foreign key(classno) reference t_class(cno)
    insert into t_class(cno,cname) values(100,’高三1班’);
    insert into t_class(cno,cname) values(200,’高三2班’);
    insert into t_class(cno,cname) values(300,’高三3班’);
    insert into t_student(sno,sname,classno) values(1,’jack’,100);
    insert into t_student(sno,sname,classno) values(2,’lucy’,100);
    insert into t_student(sno,sname,classno) values(3,’king’,200);
    insert into t_student(sno,sname,classno) values(4,’ford’,200);
    insert into t_student(sno,sname,classno) values(5,’lisa’,300);
    insert into t_student(sno,sname,classno) values(6,’jam’,300);
    select * from t_student;
    select * from t_class;

    insert into t_student(sno,sname,classno) values(7,’jimmy’,500);//添加失败，500不存在
    );
    ———————————————————————SQL文———————————————————————

    重点：典型的一对多的设计是，在多的一方加外键。

    级联更新和删除

    • 什么叫级联更新
      比如我们把父表t_class中的100全部改成了400，那么子表t_student中的100也会跟着全部改成400.
    • 什么叫级联删除
      比如我们把父表t_class中的100的记录全部删了，那么子表t_student中的100的记录也会跟着全部删了.
    • 谨慎使用级联更新和删除，因为级联操作会将数据改变或者删除【数据无价】
    • 添加级联更新和级联删除的时候需要在外键约束后面添加
    • 在删除父表中数据的时候，级联删除子表中的数据 on delete cascade
      \* 删除外键约束
      alter table t_student drop foreign key t_student_classno_fk;
\* 添加外键约束
      alter table t_student add constraint t_student_classno_fk foreign key(classno) references t_class(cno) on delete cascade;
    • 在更新父表中数据的时候，级联更新子表中的数据 on update cascade
      \* 删除外键约束
      alter table t_student drop foreign key t_student_classno_fk;
\* 添加外键约束
      alter table t_student add constraint t_student_classno_fk foreign key(classno) references t_class(cno) on update cascade;

    存储引擎

    • 存储引擎的使用
      • 数据库中的各表均被(在创建表时)指定的存储引擎来处理
      • 服务器可用的引用依赖以下因素
        • MySQL的版本
        • 服务器在开发时如何被配置
        • 启动选项
      • 为了解当前服务器中有哪些存储引擎可用，可使用show engines语句查看
    • 常用的存储引擎
      • MyISAM存储引擎
        MyISAM存储引擎是MySQL最常用的引擎，
        他管理的表具有以下特征：
        使用三个文件表示每个表：
   + 格式文件——存储表结构的定义(mytable.frm)
   + 数据文件——存储表行的内容(mytable.MYD)
   + 索引文件——存储表上索引(mytable.MYI)
灵活的AUTO_INCREMENT字段处理
可被转换为压缩，只读来节省空间
      • InnoDB存储引擎
        InnoDB存储引擎是MySQL的缺省引擎
        他管理的表具有下列主要特征：
        每个InnoDB表在数据库目录中以.frm格式文件表示
InnoDB表空间tablespace被用于存储表的内容
提供一组用来记录事务性活动的日志文件
用COMMIT(提交)、SAVEPOINT及ROLLBACK(回滚)支持事务处理
提供全ACID兼容
再MySQL服务器崩溃后提供自动恢复
多版本(MVCC)和行级锁定
支持外键及引用的完整性，包括级联删除和更新
      • MEMORY存储引擎
    • 选择合适的存储引擎
      • 当创建表时，应根据表的应用场景选择适合的存储引擎
      • MyISAM表最适合于大量的数据读而少量数据更新的混合操作
        MyISAM表的另一种适用情形是使用压缩的只读表
      • 如果查询中包含较多的数据更新操作，应使用InnoDB，其行级锁机制和多版本的支持为数据读取和更新的混合操作提供了良好的并发机制
      • 可使用MEMORY存储引擎来存储非永久需要的数据，或者是能够从基于磁盘的表中重新生成的数据

    事务

    概述

    1. 什么是事务

      • 事务对应的英语单词：Transaction
      • 事务：
        * 一个”最小”的”不可再分”的工作单元
        * 通常一个事务对应了一个完整的业务。【例如：银行账户转账业务，该业务就是一个最小的工作单元，就是从开始到结束不可再分步骤了，要么转账成功，要么失败，不存在你转钱过来我没收到钱然后你钱还没了不知道去哪了】
        * 而一个完整的业务需要批量的DML语句(insert，update，delete)共同联合完成
        * 事务只和DML语句有关系，或者说DML语句才有事务
        * 以上所描述的”批量的DML语句”共有多少条DML语句，这个和业务逻辑有关系.业务逻辑不同DML语句的个数不同。

    2. 关于银行账户转账操作

      • 账户转账是一个完整的业务，最小的单元，不可再分。也就是说银行账户转账是一个事务。
        t_act 账户表

      actno balance
      ——————————————————————
      act-001 50000.0
      act-002 10000.0

      执行转账操作(10000):
      update t_act set balance= 40000.0 where actno=’act-001’;
      update t_act set balance=20000.0 where actno=’act-002’;
      以上的两条DML语句要求必须同时成功或者同时失败，最小单元，不可再分。
      当第一条DML语句执行成功之后，并不能将底层数据库中的第一个账户的数据修改，
      只是将操作记录了一下，这个记录是在内存中完成的，当第二条DML语句执行
      成功之后，和底层数据库文件中的数据完成同步，若第二条DML语句执行失败，
      清空所有的历史操作记录，要完成以上的功能，必须借助事务。

    3. 事务的四个特性(ACID)：

      • 原子性(A)
        \* 事务是最小的工作单元，不可再分
      • 一致性(C)
        \* 事务要求所有的DML语句操作的时候，必须保证同时成功或者同时失败
      • 隔离性(I)
        \* 事务A和事务B之间具有隔离
      • 持久性(D)
        \* 是事务的保证，事务终结的标志【内存中的数据持久到硬盘文件中】

    4. 关于一些术语

      • 开启事务：Start Transaction
      • 事务结束：End Transaction
      • 提交事务：Commit Transaction
      • 回滚事务：Rollback Transaction

    5. 和事务有关的两条重要的SQL语句【TCL】

      • commit; 提交
      • rollback; 回滚

    6. 事务开启的标志是什么？事务结束的标志是什么？

      • 开启的标志：
        任何一条DML语句(insert,update,delete)执行，标志事务的开启。
      • 结束的标志：
        提交或者回滚
提交：成功的结束，将所有的DML语句操作历史记录和底层硬盘文件中的数据来一次同步。
回滚：失败的结束，将所有的DML语句操作历史记录全部清空。

    7. 重点

      • 在事务进行过程中，未结束之前，DML语句是不会更改底层数据库文件中的数据，只是将历史操作记录一下，在内存中完成记录，只有在事务结束的时候，而且是成功的结束的时候才会修改底层硬盘文件中的数据。
    事务的提交与回滚演示
    1. 在MYSQL数据库管理系统中，事务的提交和回滚的演示

    我们打开一个dos窗口进入newdb数据库中查看t_transaction表中的数据

    查看表数据

    然后我们插入一条数据

    插入数据

    然后我们再开启一个会话(重新开一个dos窗口)，进入newdb数据库下

    再开一个会话

    进入后再次查看t_transaction表中数据，发现插入刚才插入的数据成功了

    插入成功

    • 而我们说过，事务开启是一条DML语句执行，结束是提交或者回滚，然而回滚表示失败，数据不会被修改，而我们这里数据被修改了，说明该语句执行完就被提交了，那么我们得出，我们MYSQL数据库管理系统中事务是支持默认自动提交事务的。也就是说，只要执行一条DML语句，就是开启了事务，并且提交了事务。

    • 为什么我们要再开一个窗口看才算成功呢？因为我们说过，事务没提交前的操作都是操作历史记录，可以理解为在模拟真实数据，但他不是真实数据，只有提交后，才会按照我们的模拟效果修改真实数据，而我们如果同一个窗口查看的话，查看的只是历史记录，只是数据库模拟出来的数据，因为他觉得我们插入数据成功的话你显示数据肯定就有了，这都是模拟的历史记录，只有提交了才会成真，而我们重开一个窗口查看，如果有该记录的话，这才能说明算是提交成功了，因为重开的窗口不是之前的模拟记录了，是真实的了，真实的存在了该数据就说明提交成功了。

    • 这种自动提交机制是可以关闭的：【以下关闭自动提交，这只是方式其一】

      start transaction;

      关闭自动提交

      我们发现，start transaction 后，我们插入了数据，在历史记录中能查看有插入，但是重开窗口，真实的记录就没有，说明我们关闭了自动提交。

      手动提交

      然后我们只能进行手动提交，commit，提交后，再在第二个窗口查看，发现有了，说明提交成功。
      • 关闭的第一种方式
        start transaction;      手动开启事务
DML语句....
DML语句....
DML语句....
DML语句....
DML语句....
commit;                 手动提交事务【事务成功的结束】
    start transaction;      手动开启事务
DML语句....
DML语句....
DML语句....
DML语句....
DML语句....
rollback;                 手动提交事务【事务失败的结束】
    • 关闭自动提交的第二种方式
      查看当前是自动提交还是手动提交：show variables like '%commit%';
关闭自动提交：set autocommit = off; 或者 set session autocommit = off;
打开自动提价：set autocommit = on; 或者 set session autocommit = on;
以上打开和关闭自动提交机制，只对当前会话有效。
    事务的四个特性之一：隔离性(isolation)
    • 事务A和事务B之间具有一定的隔离性
    • 隔离性有隔离级别(4个)
      read uncommitted 读未提交
read committed   读已提交
repeatable read  可重复读
serializable     串行化
    • read uncommitted 读未提交
      事务A和事务B，事务A未提交的数据，事务B可以读取到。
这里读取到的数据可以叫做'脏数据'或者叫做'Dirty Read'
这种隔离级别是最低级别，这种级别一般都是在理论上存在的，数据库默认的隔离级别，一般都是高于该隔离级别的。
    • read committed 读已提交
      事务A和事务B，事务A提交的数据，事务B才能读取到
这种隔离级别高于上面的读未提交
换句话说：对方事务提交之后的数据，我当前事务才能够读到。
这种隔离级别可以避免脏数据。
这种隔离级别会导致"不可重复读取"(每次都是读取事务A提交的，所以每次读取都是新的数据，都不一样)
    • repeatable read 可重复读
      事务A和事务B，事务A提交之后的数据，事务B读取不到
事务B是可重复读取数据的
这种隔离级别高于读已提交
换句话说：对方提交之后的数据我还是读取不到
这种隔离级别可以避免"脏读和不可重复读"，达到可重复读取(比如银行查询总账，一般select语句执行半小时都不会结束，但是这半小时内肯定有客户存钱转钱，总账会发生变化，但是我们这半小时内查询的总账肯定不会算上这半小时内的金额变动了，所以我们这里读到的是之前的缓存，并没有从硬盘上读取实时数据)
MYSQL数据库管理系统默认的隔离级别就是：可重复读
虽然可以达到"可重复读"的效果，但是会导致:"幻想读"(读到的不是实时数据，而是缓存中的)
    • serializable 串行化
      事务A和事务B，事务A在操作数据库表中数据的时候，事务B只能排队等待。
这种事务隔离级别一般很少使用，吞吐量太低，用户体验不好
这种隔离级别可以避免"幻想读"，每一次读取的都是数据库表中真实的记录
事务A和事务B不在并发。
    设置事务的隔离级别

    1. 第一种方式：通过修改my.ini配置文件
      找到[mysqld]，在他的下面添加：transaction-isolation = READ-COMMITTED
      =后面可选值：READ-UNCOMMITTED/READ-COMMITTED/REPEATABLE-READ/SERIALIZABLE

    2. 第二种方式：使用命令方式设置事务的隔离级别
      <isolation-level>可选值：READ UNCOMMITTED/READ COMMITTED/REPEATABLE READ/SERIALIZABLE
      命令格式：set [无/session/global] transaction isolation level <isolation-level>

      设置事务的隔离级别作用于全局：

      set global transaction isolation level `<isolation-level>`

      设置事务的隔离级别作用于当前会话：

         set session transaction isolation level `<isolation-level>`
或：set transaction isolation level `<isolation-level>`
    查看隔离级别
    • 查看当前会话的隔离级别
      • select @@tx isolation;
      • select @@session.tx isolation;
    • 查看全局的事务隔离级别
      • select @@global.tx isolation;
    并发事务与隔离级别示例

    • 读未提交 read uncommitted

      会话1 会话2
      set global transaction isolation level read uncommitted;
      退出dos窗口
      重新开启dos窗口进入数据库系统select @@global.tx isolation;查看全局事务隔离级别，已经是读未提交了
      use bjpowernode
      start transaction; use bjpowernode
      insert into t_user(name) values(‘king’); start transaction;
      select * from t_user;(会话1未提交，而这里读取到了)

    • 读已提交 repeatable read

      会话1 会话2
      set global transaction isolation level read committed;
      退出dos窗口
      重新开启dos窗口进入数据库系统select @@global.tx isolation;查看全局事务隔离级别，已经是读已提交了
      use bjpowernode
      start transaction; use bjpowernode
      insert into t_user(name) values(‘king’); start transaction;
      select * from t_user;(会话1未提交，这里读取不到)
      commit;
      select * from t_user;(会话1提交了，这里读取到了)

    • 可重复读 repeatable read

      会话1 会话2
      set global transaction isolation level repeatable read;
      退出dos窗口
      重新开启dos窗口进入数据库系统select @@global.tx isolation;查看全局事务隔离级别，已经是可重复读了
      use bjpowernode
      start transaction; use bjpowernode
      select * from t_user; start transaction;
      insert into t_user(name) values(‘king’);
      commit;
      select * from t_user;(会话1提交了，这里读取到了)
      drop table t_user;commit；(这里把表删了，提交，按道理读取不到了)
      select * from t_user;(这里还是能读取到，意味着我们读到的是缓存，每次读到的都是一样的缓存，在重复读)

    索引【了解】

    1. 什么是索引

      • 索引对应的英语单词：index
      • 索引相当于一本字典的目录，索引的作用是提高程序的检索【查询】效率。

    2. 主键自动添加索引，所以能够通过主键查询尽量通过主键查询，效率较高。

    3. 索引和表相同，都是一个对象，表示存储在硬盘文件中的，那么索引是表的一部分，索引也是存储在硬盘文件中

    4. 在MYSQL数据库管理系统中，对表中记录进行检索的时候，通常包括几种检索方式：

      • 第一种方式：全表扫描
      • 第二种方式：通过索引进行检索【提高查询效率】

    5. 一张表中有多个字段，每一个字段都是可以添加索引的。

    6. 什么情况下适合给表中的某个字段添加索引呢？

      • 该字段数据量庞大
      • 该字段很少的DML操作【DML操作很多的话，索引也是需要不断的维护，效率反而降低】
      • 该字段经常出现在where条件当中

    7. 怎么创建索引？

      • create index dept_dname_index on dept(dname);
      • create unique index dept_dname_index on dept(dname);//添加unique表示dept表中的dname字段添加一个唯一性约束

    8. 怎么删除索引？

      • drop index dept dname index on dept;

    视图

    • 什么是视图
      如果，客户有一个业务，内容是，让我们将他给我们提供的一张数据表中的数据拿出来填充到网页上面，但是又不想让我们知道这张表的数据内容，那怎么办？

      这时，客户那边的数据库管理员就可以创建一个视图表，这个表对应了原来表上的任何数据，不过，他的显示出来的内容不再是原来的数据，而是客户自己设置的，把这个表给我们告诉我们增删改哪些数据就行了，最重要的是视图表被修改了，原来的那个表也会相应的被修改。可以理解为映射，比如原来表中有一个id字段，第一个值是1，然后客户就可以把id改成dd，1改成10，我们看到的就是dd和10，但是我们操作了dd和10之后，对应的id和1也会被修改。

    • 创建视图
      语法：create view 视图名 as DQL语句;
      注意，as后面只能跟DQL查询语句，因为我们是将我们需要创建视图的表中数据拿出来创建视图，所以先将数据查询出来再放到视图中
      比如:create view t_userview as select * from t_user;
      这条语句就会创建一个和t_user表一样的视图表.

      当然，如果不想让别人看到我们t_user表中真实的字段：
      select view t_userview as select name as viewname,age as viewage from t_user;
      这样视图中的字段就变成viewname和viewage了

    DBA命令

    1. 新建用户

      • create user 用户名 identified by ‘密码’;
        密码如果为空，则直接回车就进入数据库

    2. 给用户赋予指定数据库的权限(在root超级管理员权限下)

      • 第一种必须先创建用户，然后执行该语句
        grant all on 数据库名.数据库下的表名或者*代表所有 to 用户名;
      • 第二种给权限的时候直接创建用户
        grant all on 数据库名.数据库下的表名或者*代表所有 to 用户名 identified by ‘密码’;

    3. 删除用户
      drop user 用户名;

    4. 导出导入数据库

      • 导出整个数据库
        打开dos窗口，进入mysql的bin目录下(不用登陆数据库)，执行：mysqldump 数据库名>D:\路径\导出文件名.sql -uroot -p123
      • 导出指定库下的指定表
        打开dos窗口，进入mysql的bin目录下(不用登陆数据库)，执行：mysqldump 数据库名 表名>D:\路径\导出文件名.sql -uroot -p123
      • 导入
        如果导入数据库，就进入mysql：source .sql文件绝对路径 回车
        如果导入表，进入mysql，选择一个数据库：source .sql文件绝对路径 回车

    数据库设计三范式

    1. 数据库设计三范式

      • 设计数据库表的时候所依据的规范，共三个规范

    2. 第一范式

      • 要求有主键，并且要求每一个字段原子性不可再分，比如字段：联系方式，他的值是29481785@qq.com，138457991145,这个可以单独分为email字段和phone字段，所以不是不可再分，不满足第一范式。

    3. 第二范式

      • 前提先满足第一范式
      • 要求素有非主键字段完全依赖主键，不能产生部分依赖，主要体现在联合主键上面，比如联合主键id和name，如果另外一个字段只对id产生依赖，而和name没关系，则不行。

    4. 第三范式

      • 所有非主键字段和主键字段之间不能产生传递依赖

    5. 几个比较经典的设计

      • 一对一
      • 一对多
      • 多对多
    联系我
    扫一扫，添加JzhBetter
    • 微信扫一扫
    • qq扫一扫