MySql基础知识笔记

创生之法
449 阅读37分钟

一、数据库背景资料

1.常见数据库

名称特点适用范围备注
IBM
Oracle（全球最强大的数据库管理系统 ）适用于传统企业（政府等）安全性要求高维护使用java
MySQL支持集群互联网企业（京东、阿里）
DB2
Sybase
MS SqlServer支持标准sql的数据库管理系统

2.Mysql端口

Mysql端口号解释
3306同一台机器一个软件只能占用一个端口号

2.1 链接地址实例

例子网站ip地址返回数据的服务器软件端口号
http://220.181.38.150:80/index.html220.181.38.15080

3.sql、DB、DBMS分别是什么，之间关系

名称全拼中文备注
DBData Base数据库数据在硬盘上以文件形式存储
DBMSData Base Management System数据库管理系统
SQL结构化查询语言一门标准通用的语言，适合所有数据库产品；属于高级语言，以英语为基础

  • 关系：

    • 执行时，内部先编译，再执行sql

      • （sql编译由DBMS完成 ）

  • 执行顺序：

    • DBMS（执行）------》 SQL（操作）------》DB

4.什么是表？

表：table是数据库基本组成单元，目的是可读性强。

二、SQL语句分类

分类名称作用作用范围
DQL（数据查询语言）查询语句 Select数据操控
DML（数据操作语言）增删改insert delete update数据操控
DDL（数据定义语言）create drop alter表结构的增删改
TCL（事务控制语言）commit提交事务；rollback回滚事务；T是Transaction事务
DCL（数据控制语言）grant授权；revoke撤销权限等权限

三、数据导入

进入dos命令窗口：

第一步：登录mysql数据库管理系统

    mysql -uroot -pxxxxxx

第二步：查看有哪些数据库

 show databases;//不是SQL语句，属于mysql命令

第三步：创建自己的数据库

create database testnode;

第四步：使用testnode数据库

use testnode；

第五步：查看当前数据库有什么表？

show tables;//不是SQL语句，属于mysql命令

第六步：初始化数据

mysql> 
source F:\RerollWorkspace\StudyingBooks\testnode.sql
//source 后面接文件地址，可以直接拉文件过来
//文件地址不能有中文

第七步：删库跑路

drop database testnode;

四、SQL脚本常用命令

当一个文件扩展名是“.sql”，并且该文件中编写了大量的sql语句，称为sql脚本。

source 执行脚本，批量执行sql；sql脚本数据量太大的时候，用source命令完成初始化。

1.查看表结构

desc 表名;

  查看表结构：
  ``+-----------------------+
  ``| Tables_in_testnode |
  ``+-----------------------+
  ``| dept         |  (部门表)
  ``| emp          |  (员工表)
  ``| salgrade       |  (工资等级表)
  ``+-----------------------+
  
  ``mysql> desc dept;
  ``+--------+-------------+------+-----+---------+-------+
  ``| Field | Type    | Null | Key | Default | Extra |
  ``+--------+-------------+------+-----+---------+-------+
  ``| DEPTNO | int(2)   | NO  | PRI | NULL  |    |   部门编号
  ``| DNAME | varchar(14) | YES |   | NULL  |    |   部门名称
  ``| LOC  | varchar(13) | YES |   | NULL  |    |   部门位置
  ``+--------+-------------+------+-----+---------+-------+
  
    ``mysql> desc emp;
  ``+----------+-------------+------+-----+---------+-------+
  ``| Field  | Type    | Null | Key | Default | Extra |
  ``+----------+-------------+------+-----+---------+-------+
  ``| EMPNO  | int(4)   | NO  | PRI | NULL  |    |  员工编号
  ``| ENAME  | varchar(10) | YES |   | NULL  |    |  员工姓名
  ``| JOB   | varchar(9) | YES |   | NULL  |    |  工作岗位
  ``| MGR   | int(4)   | YES |   | NULL  |    |  上级领导编号
  ``| HIREDATE | date    | YES |   | NULL  |    |  入职日期
  ``| SAL   | double(7,2) | YES |   | NULL  |    |  月薪
  ``| COMM   | double(7,2) | YES |   | NULL  |    |  补助/津贴
  ``| DEPTNO  | int(2)   | YES |   | NULL  |    |  部门编号
  ``+----------+-------------+------+-----+---------+-------+
  
    ``mysql> desc salgrade;
  ``+-------+---------+------+-----+---------+-------+
  ``| Field | Type  | Null | Key | Default | Extra |
  ``+-------+---------+------+-----+---------+-------+
  ``| GRADE | int(11) | YES |   | NULL  |    |   等级
  ``| LOSAL | int(11) | YES |   | NULL  |    |   最低薪资
  ``| HISAL | int(11) | YES |   | NULL  |    |   最高薪资
  ``+-------+---------+------+-----+---------+-------+

2.查看表数据

SELECT * FROM dept;

3.查看当前使用的是哪个数据库

mysql> select database();

4.查看mysql的版本号。

mysql> select version();  

5.命令，结束一条语句。

\c  

6.命令，退出mysql。

exit  

7.查看创建表的语句：

show create table emp;

8.查看员工的月薪和年薪

select ename,sal from emp;
​
select ename,sal*12 AS AnnualSalary from emp;//AS 关键字可省略
select ename,sal*12 AS '年薪' from emp;//中文列名，mysql支持“字符串”，但别的不行；标准SQL要求''

五、数据查询

1、条件查询

运算符说明
=等于
<>或!=不等于
<小于
<=小于等于
大于
>=大于等于
between...and...在二者之间，等同于 >= and <=
is null为null（is not null 不为空）
and并
or或
in包含,相当于多个or(not in 不在其中)
not取非，用在is或in中
like模糊查询，支持%或者匹配；%匹配任意个字符； 只匹配一个字符

1.1 =

1.1.1查询工资等于5000员工姓名

select ename from emp where sal = 5000;

1.1.2查询KING的工资

select sal from emp where  ename= 'KING';//varchar字符串要用''

1.2 不等于<> !=

1.2.1找出工资不等于3000的

select ename,sal from emp where  sal <> 3000;
select ename,sal from emp where  sal != 3000;

1.3大于等于小于

1.3.1找出工资在1000到3000的

select ename,sal from emp where  sal >= 1000 and sal <= 3000;
select ename,sal from emp where  sal between 1000 and 3000;//【1000,3000】闭区间

1.4 between and

1.4.1找出名字在A到C开头的

select ename,sal from emp where  ename between 'A' and 'D';//【A，D）左闭右开

1.5 NULL

1.5.1找出哪些津贴为空NULL

数据库中NULL不是一个值，代表什么也没有，为空

空不是一个值，不能用等号衡量

必须使用is null 或者 is not null

select ename,sal,comm from emp where comm is null;

1.5.2找出哪些没有津贴

select ename,sal,comm from emp where comm is null or comm = 0;

1.6 and是和，or是或

1.6.1找出工作岗位是MANAGER和SALESMAN的员工

select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';

1.7 in等同于or（in后是具体值）：

select ename,job from emp where job in( 'MANAGER' , 'SALESMAN');
select ename,job from emp where sal in( 1000,5000);//in后是具体值

not in 不在这几个值

1.7.1 and和or联合使用，找出薪资大于3000并且部门编号是20或30的员工

select ename,sal,DEPTNO from emp where sal > 1000 and (deptno = 20 or DEPTNO = 30);//and优先级大于or，要括起来使用；优先级不确定加（）

1.8 模糊查询like

模糊查询中，两个特殊符号一个%，一个_

%表示任意多个字符

_表示任意一个字符

1.8.1找出名字中含有o的

select ename from emp where ename like '%o%';

1.8.2找出名字中第二个字母为A的

select ename from emp where ename like '_A%';

1.8.3找出名字中含有_的

select ename from emp where ename like '%_%';
转义字符\

1.8.4找出名字中最后一个字母为T的

select ename from emp where ename like '%T';

2、排序数据

2.1 asc表示升序，desc表示降序

2.1.1按照工资升序找出员工名和工资

select ename,sal from emp order by sal ;
//默认升序

2.1.2按照工资降序排列，工资相同时按照名字升序

select ename,sal from emp order by sal desc , ename asc;
//多个字段同时排序，字段越前作用越大

2.1.3按照字段列排序

select ename,sal from emp order by 2 ；
//2代表根据第二列sal排序

2.1.4 执行顺序

select 3
from 1 
where 2
order by 4 ；

3.分组函数：自动忽略NULL

函数名作用
count计数
sum求和 ，自动忽略NULL
avg平均
max最大
min最小
重点标注：所有的分组函数都是对某一组的数据进行操作的

3.1找出工资总和

select sum(sal) from emp;

3.2最高、最低工资

select max(sal) from emp;
select min(sal) from emp;

3.3平均工资

select avg(sal) from emp;

3.4总人数

select count(*) from emp;

3.5分组函数总结

  • 分组函数一共5个

    • 分组函数可以同时使用
    • 也称：多行处理函数。

  • 特点： 输入多行，最终结果一行。

  • 分组函数自动忽略NULL

  • 单行处理函数: 输入一行，输出一行。

3.6 ifnull（）：

  • ifnull（）空处理函数

    • NULL进行运算，结果为null

  • ifnull（"可能为null的数据"，"指定替换值"）：

    • 属于单行处理函数

3.6.1计算每个员工年薪

select ename,(sal + ifnull(comm,0))*12 AS AnnualSalary from emp;

3.7 count（*）和count（某字段）的区别

count（*）：统计总记录条数

count（某字段）：表示统计某字段中不为null的数据总量

4.分组查询

分组字段作用
group by按照某个字段或者某些字段分组
having对分组之后的数据进行再次过滤

4.1 分组函数不可直接用在where子句中？

因为group by 是在where执行后再执行；相当于还没分组，故不可用分组函数。

4.1.1 找出每个工作岗位的最高薪资

select job,max(sal) from emp group by job;
​
//当一条语句后面有group by，select只能跟分组字段和分组函数
//不然的话，再mysql中有结果无意义（随机读取一条无关数据）；
//oracle中报错，规则更严谨

注意：

  1. 分组函数一般会和group by联合使用，这也是为什么称为分组函数的原因。
  2. 任何一个分组函数（count，sum，AVG，max，min）都是在group by执行结束后才会执行。
  3. 当一条sql语句没有group by 整张表会自成一组。

4.1.2找出高于平均工资的员工工资

select ename,sal 
from emp 
where sal > (select avg(sal) from emp);

4.2多个字段能联合分组

4.2.1 找出每个部门不同工作岗位的最高薪资

select deptno,job,max(sal)
from emp
group by deptno,job;

4.2.2找出每个部门的最高薪资，要求显示薪资大于2900的数据

select deptno,max(sal)
from emp
where sal >2900
group by
deptno;
//先一步过滤掉不需要的sal2900以下的值

select  deptno,max(sal)
from emp
group by deptno
having max(sal) > 2900;
//先求出最大值再过滤，浪费

4.2.3 找出每个部门的平均薪资，要求显示薪资大于2000的数据

select  deptno,avg(sal)
from emp
group by deptno
having avg(sal) > 2000;
//分完组后计算出来的值，要用having

注意： 尽可能在where后过滤数据，再考虑having

4.3总结一个完整的DQL语句怎么写

执行顺序
select 5
from 1 
where 2
group by 3
having 4
order by 6 ；

5、去除重复记录

5.1 查询结果集的重复去重

select distinct job from emp;
//使用distinct关键字去除重复的记录

记住：distinct只能出现在所有字段的最前面

5.2 distinct后面有多个字段代表联合去重

select distinct deptno,job from emp order by deptno;

5.3 统计岗位的数量（去重）

select count(distinct job) from emp;

六、连接查询

1.什么是连接查询

在实际开发中，大部分的情况下都不是从单表中查询数据，一般都是多张表联合查询取出最终的结果。 在实际开发中，一般一个业务都会对应多张表，比如：学生和班级，起码两张表。

    stuno       stuname         classno     classname
    -----------------------------------------------------------------------------------
    1           zs                  1               北京大兴区亦庄经济技术开发区第二中学高三1班
    2           ls                  1               北京大兴区亦庄经济技术开发区第二中学高三1班
    ...

学生和班级信息存储到一张表中，结果就像上面一样，数据会存在大量的重复，导致数据的冗余。

2.连接查询的分类

根据语法出现的年代来划分的话，包括：

SQL92一些老的DBA可能还在使用这种语法。DBA：DataBase Administrator，数据库管理员
SQL99（比较新的语法）

根据表的连接方式来划分，包括：

内连接外连接全连接
等值连接左外连接（左连接）左表为主很少用！
非等值连接右外连接（右连接）右表为主
自连接（同一张表）

3.笛卡尔积现象

笛卡尔积现象（笛卡尔乘积现象）：当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积。

4.关于表的别名：

select e.ename,d.dname from emp e,dept d;

4.1表的别名好处

  1. 执行效率高
  2. 可读性好。

5.思考：避免了笛卡尔积现象，会减少记录的匹配次数吗？

不会，查询次数还是不变的。只不过显示的是有效记录。

5.1找出每一个员工的部门名称，显示员工名和部门名

select
e.ename,d.dname
from
emp e,dept d
where
e.deptno = d.deptno;

6.内连接

6.1 等值连接：

最大特点：条件等量连接

6.1.1 查询每个员工的部门名称，显示员工名和部门名

SQL99：（常用的）
    select 
        e.ename,d.dname
    from
        emp e
    join
        dept d
    on
        e.deptno = d.deptno;
​
    // inner可以省略的，带着inner目的是可读性好一些。
    select 
        e.ename,d.dname
    from
        emp e
    inner join
        dept d
    on
        e.deptno = d.deptno;

语法：
        ...
            A
        join
            B
        on
            连接条件
        where
            ...

6.2 非等值连接：

最大特点：连接关系是非等量关系

6.2.1 案例：

找出每个员工的工资等级，要求显示员工名、工资、工资等级。

select
e.ename,e.sal,s.grade
from
emp e
join
salgrade s
on
e.sal between s.LOSAL and s.HISAL
order by s.GRADE;

6.3 自连接：

最大的特点是：一张表看做两张表。自己连接自己。

6.3.1 案例：

找出每个员工的上级领导，要求显示员工名和对应的领导名。

select
a.ename as '员工名',b.ename as '领导名'
from
emp a
join
emp b
on a.MGR = b.EMPNO;

7.外连接

7.1 什么是外连接，和内连接有什么区别？

  • 内连接：

    • 假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。
    • AB两张表没有主副之分，两张表是平等的。

  • 外连接：

    • 假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，
    • 主要查询主表中的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。
    • 外连接最重要的特点是：主表的数据无条件的全部查询出来。

  • 外连接的分类？

    • 左外连接（左连接）：表示左边的这张表是主表。
    • 右外连接（右连接）：表示右边的这张表是主表。

  • 左连接有右连接的写法，右连接也会有对应的左连接的写法。

7.1.1 找出每一个员工的上级领导（所有员工必须在）

select
    a.ename as '员工名',b.ename as '领导名'
from
    emp a
left join
    emp b
on 
    a.MGR = b.EMPNO;
​
// outer是可以省略的。
select 
    a.ename '员工', b.ename '领导'
from
    emp a
left outer join
    emp b
on
    a.mgr = b.empno;

7.1.2 找出哪个部门没有员工？

select
    d.*
from
    emp e
right join
    dept d
on 
    d.DEPTNO = e.DEPTNO
where 
    e.DEPTNO is null ;
//找出emp中没有用到的部门号

7.2 三表连接

注意，解释一下：
    ....
        A
    join
        B
    join
        C
    on
        ...
    表示：A表和B表先进行表连接，连接之后A表继续和C表进行连接。

7.2.1 找出每一个员工的部门名称以及工资等级。

select
    e.ename,d.dname,e.sal,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
order by 
    s.GRADE;

7.2.2 找出每一个员工的部门名称、工资等级、以及上级领导。

select
    e.ename,d.dname,s.grade,eb.ename as '领导' 
from
    emp e
join
    dept d
on
    e.deptno = d.deptno
join
    salgrade s
on
    e.sal between s.LOSAL and s.HISAL
left join
    emp eb
on
    e.MGR = eb.EMPNO
order by 
    s.GRADE;
//外连接用在最后

8. 子查询

8.1 什么是子查询？子查询都可以出现在哪里？

select语句当中嵌套select语句，被嵌套的select语句是子查询。

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

8.2 where子句中使用子查询

8.2.1 找出高于平均薪资的员工信息。

select 
    * 
from 
    emp 
where 
    sal > (select avg(sal) from emp);

8.3 from后面嵌套子查询

8.3.1 案例：找出每个部门平均薪水的等级。

第一步：找出每个部门平均薪水（按照部门编号分组，求sal的平均值）

select deptno,avg(sal) as avgsal from emp group by deptno;

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

select 
    t.DEPTNO,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
order by 
    t.DEPTNO;

8.3.2 找出每个部门平均的薪水等级

select 
    e.DEPTNO,avg(s.grade)
from
    emp e
join
    salgrade s
on
    e.sal between s.LOSAL and s.HISAL
group by 
    e.DEPTNO
order by 
    e.DEPTNO;

8.4 在select后面嵌套子查询。

8.4.1 案例：找出每个员工所在的部门名称，要求显示员工名和部门名。

select 
    e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname 
from 
    emp e;
//要求必须是嵌套的情况

9.union （可以将查询结果集相加）

9.1 两张不相干的表中的数据拼接在一起显示？

select ename from emp
union
select dname from dept;
//拼接的数据必须两两相对应。不能一张表是一个数据，另一张表是两个数据，这样无法拼接！

9.1.1 找出工作岗位是SALESMAN和MANAGER的员工？

第一种：
select 
    ename,job 
from 
    emp 
where 
    job = 'MANAGER' or job = 'SALESMAN';
​
​
第二种：
select 
    ename,job 
from 
    emp 
where 
    job in('MANAGER','SALESMAN');
    
    
第三种：union
select ename,job from emp where job = 'MANAGER'
union
select ename,job from emp where job = 'SALESMAN';

10. limit (重点中的重点，以后分页查询全靠它了。)

10.1 limit概述

  • limit是mysql特有的

    • 其他数据库中没有，不通用。
    • （Oracle中有一个相同的机制，叫做rownum）

  • limit取结果集中的部分数据，作用。

  • 语法机制：

    • limit startIndex, length
    • startIndex表示起始位置，从0开始，0表示第一条数据。
    • length表示取几个

10.1 .1案例：取出工资前5名的员工（思路：降序取前5个）

select ename,sal from emp order by sal desc;
取前5个：
select ename,sal from emp order by sal desc limit 0, 5;
select ename,sal from emp order by sal desc limit 5;

10.2 limit是sql语句最后执行的一个环节：

    select      5
        ...
    from            1
        ...     
    where           2
        ... 
    group by        3
        ...
    having      4
        ...
    order by        6
        ...
    limit           7
        ...;

10.2.1 找出工资排名在第4到第9名的员工？

select 
    ename,sal
from
    emp
order by
    sal desc
limit 3,6;

10.3 通用的标准分页sql？

每页显示3条记录： 第1页：0, 3 第2页：3, 3 第3页：6, 3 第4页：9, 3 第5页：12, 3

10.3.1 每页显示pageSize条记录：

  • 第pageNo页：

    • (pageNo - 1) * pageSize, pageSize

  • pageSize：是每页显示多少条记录

  • pageNo：显示第几页

//java代码
{
    int pageNo = 2; // 页码是2
    int pageSize = 10; // 每页显示10
    }

limit (pageNo - 1) * pageSize, pageSize

七、表的操纵方法

1.创建表：

建表语句的语法格式：

create table 表名(
        字段名1 数据类型,
        字段名2 数据类型,
        字段名3 数据类型,
        ....
    );

1.1MySQL当中字段，常见的数据类型

数据类型Java中对应类型
int整数型int
bigint长整型long
float浮点型float double
char定长字符串String
varchar（255字符）可变长字符串StringBuffer/StringBuilder
date （对应Java中的）日期类型java.sql.Date类型
BLOB （Binary Large OBject）二进制大对象（存储图片、视频等流媒体信息）Object
CLOB （Character Large OBject）字符大对象（存储较大文本，比如，可以存储4G的字符串。）Object

1.1.1 char和varchar怎么选择？

在实际的开发中，当某个字段中的数据长度不发生改变的时候，是定长的，例如：性别、生日等都是采用char。 ​ 当一个字段的数据长度不确定，例如：简介、姓名等都是采用varchar。

1.1.2 BLOB和CLOB类型的使用？

BLOB使用insert插入不了，必须用java的io流

电影表: t_movie
​
•   id(int) name(varchar)       playtime(date/char)     haibao(BLOB)        history(CLOB)剧情
​
•   1           蜘蛛侠 
•   2

1.2. 表名在数据库当中一般建议以：t或者tbl开始。

创建学生表：

学生信息包括：学号、姓名、性别、班级编号、生日

create table t_student(
    no bigint '学号',
    name varchar(255),
    sex char,
    classno varchar(255) '班级编号',
    birth char(10) '生日'
);
​

2.插入数据：insert

2.1.语法格式：

insert into 表名(字段名1,字段名2,字段名3,....) values(值1,值2,值3,....) ​ 要求：字段的数量和值的数量相同，并且数据类型要对应相同。

insert into t_student(no,name,sex,classno,birth)values(1,'zhangsan','男','大一一班','1999-10-1');
insert into t_student(no,name,sex,classno,birth)values(2,'赵四','男','大一一班','1999-1-1');
insert into t_student(no,name,sex,classno,birth)values(3,'王五','男','大一一班','1999-11-1');
​
insert into t_student(name) values('wangwu'); // 除name字段之外，剩下的所有字段自动插入NULL。

drop table if exists t_student;
//如果表xx存在删除
​
create table t_student(
    no bigint,
    name varchar(255),
    sex char(1) default 1,
    classno varchar(255),
    birth char(10)
);
//default设置默认值（没有插入值的null情况下）

2.2.需要注意的地方：

当一条insert语句执行成功之后，表格当中必然会多一行记录。 ​ 即使多的这一行记录当中某些字段是NULL，后期也没有办法在执行 ​ insert语句插入数据了，只能使用update进行更新。

// 字段可以省略不写，但是后面的value对数量和顺序都有要求。
    insert into t_student values(1,'jack','0','gaosan2ban','1986-10-23');
    
    // 一次插入多行数据
    insert into t_student
        (no,name,sex,classno,birth) 
    values
        (3,'rose','1','gaosi2ban','1952-12-14'),(4,'laotie','1','gaosi2ban','1955-12-14');

3.表的复制

create table 表名 as select语句;将查询结果当做表创建出来。

create table t_student1 as select no ,name,sex from t_student;
//复制要用as连接

4.将查询结果插入到一张表中？

drop table if exists t_student2;
    create table t_student2(
        no bigint,
        name varchar(255),
        sex char(1) default 1,
        classno varchar(255),
        birth char(10)
    );
    
insert into t_student2 select * from t_student;
//可以将查询结果插入表，但列必须匹配
//插入不能用as连接

5.修改数据：update

5.1.语法格式：

update 表名 set 字段名1=值1,字段名2=值2... where 条件;

5.2.注意：没有条件，整张表数据全部更新。

案例：将部门10的LOC修改为SHANGHAI，将部门名称修改为RENSHIBU

update 
    dept1
set
    dname = 'SHANGHAI',
    loc = 'RENSHIBU'
where
    deptno = 10;

6.删除数据？

6.1.语法格式：

delete from 表名 where 条件; ​ 注意：没有条件全部删除。

6.2.注意

delete 数据量大的时候删除很慢，没有释放真实空间，可以回滚。

6.3.案例

删除10部门数据？
    delete from dept1 where deptno = 10;
    
删除所有记录？
    delete from dept1;
​
怎么删除大表中的数据？（重点）
    truncate table 表名; // 表被截断，不可回滚。永久丢失。删的快，需要确认后操作
    
删除表？
  drop table 表名；//通用
  drop table if exists 表名;、、oracle不支持该写法

7.对表结构的修改

在实际开发中表一旦设计好之后，对表结构的修改是很少的，修改表结构就是对之前的设计进行了否定，即使需要修改表结构，直接使用工具操作。修改表结构的语句不会出现在Java代码当中。

出现在java代码当中的sql包括：insert delete update select（这些都是表中的数据操作。）

8.增删改查有一个术语：CRUD操作

Create（增） Retrieve（检索） Update（修改） Delete（删除）

八、约束(Constraint)

1、什么是约束？

在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的合法性、有效性、完整性。

2.常见的约束有哪些呢？

约束名称字段释义
非空约束not nul约束的字段不能为NULL
唯一约束unique约束的字段不能重复
主键约束primary key约束的字段既不能为NULL，也不能重复（简称PK）
外键约束foreign key简称FK
检查约束check注意Oracle数据库有check约束，但是mysql没有，目前mysql不支持该约束。

2.1.非空约束 not null

    drop table if exists t_user;
    create table t_user(
        id int,
        username varchar(255) not null,
        password varchar(255)
    );
    
    insert into t_user(id,password) values(1,'123');  //编译错误，约束username字段不能为空！
    ERROR 1364 (HY000): Field 'username' doesn't have a default value
​
insert into t_user(id,username,password) values(1,'lisi','123');

2.2 唯一性约束(unique)

  • 唯一性约束修饰的字段具有唯一性，不能重复。但可以为null。
  • 案例：给某一列添加unique
drop table if exists t_user;
create table t_user(
    id int unique,
    username varchar(255) unique
);
insert into t_user values(1,'zhang'),(2,'san');
​
insert into t_user values(1,'zhang'),(2,'zhang');
Error Code: 1062. Duplicate entry '1' for key 't_user.id'   0.141 sec
//出现编译错误，唯一性约束，该字段与上一行字段重复，但可以为null！

2.2.1 案例：给两个列或者多个列添加unique

  drop table if exists t_user;
    create table t_user(
        id int,
        usercode varchar(255),
        username varchar(255),
        unique(usercode,username)  
        //多个字段联合起来添加一个约束unique 【表级约束】
        //usercode,username联合起来具有唯一性
      );

注意：not null约束只有列级约束，没有表级约束。

2.3 主键约束

drop table if exists t_user;
create table t_user(
  id int primary key,  //列级约束
  username varchar(255),
  email varchar(255)
);

id是主键，因为添加了主键约束，主键字段中的数据不能为null，也不能重复。 主键的特点：不能为null，也不能重复。

2.3.1 主键相关的术语？

主键约束 ：primary key ​ 主键字段 : id字段添加primary key之后，id叫做主键字段 ​ 主键 值 ：id字段中的每一个值都是主键值。

2.3.2 主键有什么作用？

-表的设计三范式中要求，第一范式就要求任何一张表都应该有主键

-主键的作用，主键值是这行记录在这张表中的唯一标识（类似身份证号）

  • 根据主键字段的字段数量来划分：
    • 单一主键 (推荐的，常用的。)
    • 复合主键(多个字段联合起来添加一个主键约束)
    • (复合主键不建议使用，因为复合主键违背三范式。 )
  • 根据主键性质来划分：

    • 自然主键 ：主键值最好就是一个和业务没有任何关系的自然数。(这种方式是推荐的)

    • 业务主键 : 主键值和系统的业务挂钩，例如：拿着银行卡的卡号做主键、拿着身份证号做为主键。(不推荐使用)

      • 最好不要拿着和业务挂钩的字段做为主键。因为以后的业务一旦发生改变的时候，主键也可能需要随着发生变化，但有的时候没有办法变化，因为变化可能会导致主键重复。

  • 一张表的主键约束只能有1个。(必须记住)

2.3.3 使用表级约束方式定义主键：

drop table if exists t_user;
    create table t_user(
        id int,
        username varchar(255),
        primary key(id)
);

2.3.4 mysql提供主键值自增：(非常重要。)

drop table if exists t_user;
create table t_user(
    id int primary key auto_increment,  //id字段自动维护一个自增的数字，从1开始，以1递增。
  username varchar(255)
  );
insert into t_user(username) values('a'); 
insert into t_user(username) values('b');
insert into t_user(username) values('c');
​

提示：Oracle当中也提供了一个自增机制，叫做：序列(sequence)对象。

2.4 外键约束

  • 关于外键约束的相关术语： 外键约束：foreign key 外键字段：添加有外键约束的字段 外键值：外键字段中的每一个值。
  • 业务背景： 请设计数据库表，用来维护学生和班级的信息？ 第一种方案：一张表存储所有数据

缺点：冗余。【不推荐】

第二种方案：两张表(班级表和学生表)

  • 将以上表的建表语句写出来：

    t_student中的classno字段引用t_class表中的cno字段，此时t_student表叫做子表。t_class表叫做父表。

    删除数据的时候，先删除子表，再删除父表。 添加数据的时候，先添加父表，再添加子表。 创建表的时候，先创建父表，再创建子表。 删除表的时候，先删除子表，再删除父表。

     drop table if exists t_student;
 drop table if exists t_class;
​
 create table t_class(
     cno int,
     cname varchar(255),
     primary key(cno)
   );
​
 create table t_student(
      sno int,
      sname varchar(255),
      classno int,
      primary key(sno),
      foreign key(classno) references t_class(cno)
    );
​
  insert into t_class values(101,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
  insert into t_class values(102,'yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy');
​
  insert into t_student values(1,'zs1',101);
  insert into t_student values(2,'zs2',101);
  insert into t_student values(3,'zs3',102);
  insert into t_student values(4,'zs4',102);
  insert into t_student values(5,'zs5',102);
  insert into t_student values(6,'zs6',102);
  select * from t_class;
  select * from t_student;
​
  insert into t_student values(7,'lisi',103);  //编译错误，引用的103，父表中没有该字段！
  ERROR 1452 (23000) : Cannot add or update a child row :aforeign key constraint fails (bjpowernode INT YT......)

    • 外键值可以为NULL？

      外键可以为null。

    • 外键字段引用其他表的某个字段的时候，被引用的字段必须是主键吗？

      注意：被引用的字段不一定是主键，但至少是具有unique约束，具有唯一性，不可重复！

九、存储引擎？

1、指定存储引擎的建表语句

       CREATE TABLE `t_x` (
      `id` int(11) DEFAULT NULL
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

注意：在MySQL当中，凡是标识符使用飘号括起来的。最好别用，不通用。

建表的时候可以指定存储引擎，也可以指定字符集。

mysql默认使用的存储引擎（ENGINE）是InnoDB方式。 默认采用的字符集（CHARSET）是UTF-8。

2. 什么是存储引擎呢？

  • 存储引擎这个名字只有在mysql中存在。

    • (Oracle中有对应的机制，但不叫做存储引擎。Oracle中没有特殊的名字，就是"表的存储方式" )

  • mysql支持很多存储引擎，每个存储引擎都对应了一种不同的存储方式。

  • 每一个存储引擎都有自己的优缺点，需要在合适的时机选择合适的存储引擎。

3、查看当前mysql支持的存储引擎？

show engines \G

mysql 5.5.36版本支持的存储引擎有9个：

4、常见的存储引擎

EngineSupportCommentTransactionsXASavepoints
MyISAMYESMyISAM storage engineNONONO
InnoDBDEFAULT（默认）Supports transactions, row-level locking, and foreign keys
（支持事务、行级锁定和外键）		YESYESYES
MEMORYYESHash based, stored in memory, useful for temporary tables
（基于哈希，存储在内存中，对临时表很有用）		NONONO

4.1 MyISAM

  • MyISAM存储引擎不支持事务。

  • MyISAM是mysql最常用的存储引擎，但不是默认的。

  • MyISAM采用三个文件组织一个表：

    • xxx.frm(存储格式的文件)
    • xxx.MYD(存储表中数据的文件)
    • xxx.MYI(存储表中索引的文件)

  • 优点：可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率。

  • 缺点:不支持事务。

4.2 InnoDB

优点：

  1. 支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障。

  2. 表的结构存储在xxx.frm文件中

    1. 数据存储在tablespace这样的表空间中(逻辑概念)，无法被压缩，无法转换成只读。

  3. 这种InnoDB存储引擎在MySQL数据库崩溃之后提供自动恢复机制。

  4. InoDB支持级联删除和级联更新。

4.3 MEMORY

  • 缺点：不支持事务。数据容易丢失。因为所有数据和索引都是存储在内存当中的。
  • 优点：查询速度最快。
  • 以前叫做HEPA引擎。

十、事务(Transaction)

1、什么是事务？

一个事务是一个完整的业务逻辑单元，不可再分。

比如：银行账户，从A账户向B账户转账10000元，需要执行两条update语句。

update t_act set balance = balance - 10000 where actno = 'act-001';
update t_act set balance = balance + 10000 where actno = 'act-002';

以上两条DML语句必须同时成功，或者同时失败，不允许出现一条成功，一条失败。

想要保证以上的两条DML语句同时成功或者同时失败，那么就要使用数据库的"事务机制"。

2. 和事务相关的语句只有：DML语句（insert delete update）

为什么？因为他们三个语句都是和数据库表当中的“数据”相关的。

事物的存在是为了保证数据的完整性，安全性。

3.假设所有事务都能使用一条DML语句搞定，还需事务机制吗？

不需要事务机制。

但实际情况不是这样的，通常一个业务需要多条DML语句共同联合完成。

3.3实例：

某一业务，先执行一条insert，再执行一条update，最后执行一条delete。

开启事务机制（开始）：
<-------------历史操作------------------------>
    执行insert语句-->执行成功后，把执行记录到数据库的历史操作中，并不会向文件中保存一条数据，不会真正修改硬盘上的数据。
    执行update语句-->执行成功后，同上。
    执行delete语句-->把执行记录到数据库的历史操作（记录到缓存）中，不会真正修改硬盘上的数据。
    
    可以在操作间设置保存点savepoint，指定回滚位置
<-------------历史操作------------------------>
提交事务或者回滚事务（结束）；
提交事务根据操作记录，修改硬盘文件上数据，清除历史操作
回滚事务清除历史操作

4.事务的四大特性：ACID

A原子性事务是最小的工作单元，不可再分。
C一致性必须保证多条DML语句同时成功或者同时失败。
I隔离性事务A与事务B之间具有隔离性。
D持久性事务的保障，最终数据必须持久化到硬盘文件中，事务才算成功。

5. 事务间的隔离性

隔离级别，理论上存在四个隔离级别：

级别名称name效果解决问题存在问题
第一级别读未提交read uncommited对方事务未提交的数据，我们当前事务可以读取到未提交数据。存在脏读（Dirty Read）现象：表示读到了脏数据，非常不稳定
第二级别读已提交read commited对方事务提交后的数据，我们当前事务可以读取到数据。脏读现象不可重复读（对方一直在提交，存在不同时间读数据不同的现象。
第三级别可重复读repeatable read不可重复读问题读取到的数据是幻象（是数据的备份）
第四级别序列化读/串行化读解决所有问题。需要事务排队

Oracle数据库默认的隔离级别是：第二级别，读已提交。 mysql数据库默认的隔离级别是：第三级别，可重复读。

6.演示事务：

* mysql事务默认情况下是自动提交的。
 (什么是自动提交？只要执行任意一条DML语句则提交一次。)
 
 怎么关闭默认提交？start transaction;

   * 建表：
   drop table if exists t_user;
   create table t_user(
       id int primary key auto_increment,
       username varchar(255)
     );
     
     
   * 演示：mysql中的事务是支持自动提交的，只要执行一条DML语句，则提交一次。
           mysql> insert into t_user(username) values('zs');
       Querk OK, 1 row affected (0.03 sec)
       mysql> select * from t_user;
        +----+----------+
        | id | username |
        +----+----------+
        |  1 | zs       |
        +----+----------+
       mysql> rollback;
       Query OK, 0 rows affected (0.00 sec)
​
           mysql> select * from t_user;
         +----+----------+
         | id | username |
         +----+----------+
         |  1 | zs       |
         +----+----------+
​
​
    * 演示：使用start transaction; 关闭自动提交机制。
        mysql> select * from t_user;
        +----+----------+
        | id | username |
        +----+----------+
        |  1 | zs       |
        +----+----------+
        1 row in set (0.00 sec)
​
        mysql> start transaction;
        Query OK, 0 rows affected (0.00 sec)
​
        mysql> insert into t_user(username) values("lisi");
        Query OK, 1 row affected (0.00 sec)
​
        mysql> select * from t_user;
        +----+----------+
        | id | username |
        +----+----------+
        |  1 | zs       |
        |  2 | lisi     |
        +----+----------+
        2 rows in set (0.00 sec)
​
        mysql> insert into t_user(username) values("wangwu");
        Query OK, 1 row affected (0.00 sec)
​
        mysql> select * from t_user;
        +----+----------+
        | id | username |
        +----+----------+
        |  1 | zs       |
        |  2 | lisi     |
        |  3 | wangwu   |
        +----+----------+
        3 rows in set (0.00 sec)
​
        mysql> rollback;    //回滚
        Query OK, 0 rows affected (0.00 sec)
​
        mysql> select * from t_user;
        +----+----------+
        | id | username |
        +----+----------+
        |  1 | zs       |
        +----+----------+
        1 row in set (0.00 sec)
​
        mysql> start transaction;
        Query OK, 0 rows affected (0.00 sec)
​
        mysql> insert into t_user(username) values("wangwu");
        Query OK, 1 row affected (0.00 sec)
​
        mysql> insert into t_user(username) values("object");
        Query OK, 1 row affected (0.00 sec)
​
        mysql> insert into t_user(username) values("joke");
        Query OK, 1 row affected (0.00 sec)
​
        mysql> insert into t_user(username) values("xiaozhaozhao");
        Query OK, 1 row affected (0.00 sec)
​
        mysql> select * from t_user;
        +----+--------------+
        | id | username     |
        +----+--------------+
        |  1 | zs           |
        |  4 | wangwu       |
        |  5 | object       |
        |  6 | joke         |
        |  7 | xiaozhaozhao |
        +----+--------------+
        5 rows in set (0.00 sec)
​
        mysql> commit;
        Query OK, 0 rows affected (0.00 sec)
​
        mysql> select * from t_user;
        +----+--------------+
        | id | username     |
        +----+--------------+
        |  1 | zs           |
        |  4 | wangwu       |
        |  5 | object       |
        |  6 | joke         |
        |  7 | xiaozhaozhao |
        +----+--------------+
        5 rows in set (0.00 sec)
​
        mysql> rollback;
        Query OK, 0 rows affected (0.00 sec)
​
        mysql> select * from t_user;
        +----+--------------+
        | id | username     |
        +----+--------------+
        |  1 | zs           |
        |  4 | wangwu       |
        |  5 | object       |
        |  6 | joke         |
        |  7 | xiaozhaozhao |
        +----+--------------+
        5 rows in set (0.00 sec)
​
    rollback : 回滚。
    commit ： 提交。
    start transaction : 关闭自动提交机制。
    
    自增用过后即便回滚，也会在之前的基础上自增；

6.1 演示两个事务，假如隔离级别：

 set global transaction isolation level repeatable read;
 //对同一表，一程序操作后并提交后，另一程序访问结果不变，是缓存，读取到的数据是幻象。//第1级别：读未提交        
set global transaction isolation level read uncommitted;
//设置全局的隔离级别read uncommitted
select @@global.tx_isolation；
//查看事务的全局隔离级别
注意：设置后要重新登录使用
//对同一表，一程序操作后未提交，另一程序可访问结果
​
//第二级别；读已提交   
set global transaction isolation level read committed;
//对同一表，一程序操作后已提交，另一程序可访问结果
​
//第三级别:可重复读
set global transaction isolation level repeatable read;
//对同一表，一程序操作后并提交后，另一程序访问结果不变，是缓存，读取到的数据是幻象。
​
//第四级别:序列化读serializable
set global transaction isolation level serializable;
//一程序操作一张表未结束，另一程序访问该表会卡住；程序操作结束，另一程序会立即响应；用户体验感稍差

十一、索引

1、什么是索引？有什么用？

索引相当于一本书的目录，通过目录可以快速的找到对应的资源。

数据库方面，查询一张表的时候有两种检索方式：

第一张方式：全表扫描

第二种方式：根据索引引擎检索（效率很高）

2.索引为什么可以提高扫描效率？

最根本原理是缩小了扫描的范围。

索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库中的对象，也需要不断地维护。是有维护成本。比如，表中数据经常被修改，不适合添加索引，数据一旦修改，索引需要重新排序，进行维护。

添加索引是给某一个字段，或者说某些字段添加索引。

    select ename,sal from emp where ename = 'SMITH';
    当ename字段没有添加索引的时候，以上sql语句会进行全表扫描，扫描ename字段中所有的值。
    当ename字段添加索引的时候，以上sql语句会根据索引扫描，快速定位。

3.怎么创建索引对象？怎么删除？

创建索引对象： ​ create index 索引名称 on 表名(字段名); ​ 删除索引对象： ​ drop index 索引名称 on 表名;

4. 什么时候考虑给字段添加索引？（满足什么条件）

  • 数据量庞大（根据客户需求，根据线上环境）
  • 该字段很少的DML操作（字段修改，索引需要进行维护。）
  • 该字段经常出现在where子句中（经常根据哪个字段维护）

5.主键和具有unique约束的字段自动会添加索引

根据主键查询效率较高，尽量根据主键检索。

6. 查看sql语句的执行计划：

  mysql> explain select ename,sal from emp where sal = 5000;

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+

type：ALL全表扫描

给薪资sal字段添加索引：

     create index emp_sal_index on emp(sal);
​
   mysql> explain select ename,sal from emp where sal = 5000;

+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+ | 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 | +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+

type：ref扫描
rows检索次数减少了

7.索引底层使用的数据结构是：B -Tree

左小右大

B-tree（多路搜索树，并不是二叉的）的数据结构。使用B-tree结构可以显著减少定位记录时所经历的中间过程，从而加快存取速度。按照翻译，B 通常认为是Balance的简称。

8、索引的实现原理？

通过B- Tree缩小扫描范围，底层索引进行了排序，分区（建立索引后自动排序分区，根据排序分区迅速定位找到物理地址，无需对比所有数据），索引会携带数据在表中的"物理地址"，最终通过索引检索到数据之后，获取到关联的物理地址， ​ 通过物理索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。

select ename from emp where ename = 'SMITH';
通过索引转换为：
select ename from emp where  物理地址 = 0x123;

9.索引的分类

单一索引给单个字段添加索引
复合索引给多个字段联合添加1个索引
主键索引主键会自动添加索引
单一索引有unique约束的字段会自动添加索引

10.索引什么时候失效(模糊查询)

模糊查询的时候，第一个通配符使用的是%，这个时候索引是是失效的，不知道第一个字母，无法分区。

select ename from emp where ename like ' %A% ';

十二、视图(view)

1、什么是视图？

站在不同的角度去看到数据。(同一张表的数据，通过不同的角度去看待)

2.怎么创建视图？怎么删除视图？

create view myview as select empno,ename from emp;
drop view myview;

注意：只有DQL语句才能以试图对象的方式创建出来。

3.对视图进行增删改查，会影响原表数据

(通过视图影响原表数据，不是直接操作的原表)

可以对试图进行CRUD操作。

3.1更新table，涉及视图所在字段，视图也会随之更新。

视图view是根据table建立的，当更新table,如果涉及视图所在字段，那么视图也会随之更新。 修改字段名称或者字段长度一类的，改变基表的一些性质的操作，视图会失效，需要重新编译一下视图才可以。

4、面向视图操作？

  create table emp_bak as select * from emp;
  
  create view myview1 as select empno,ename,sal from emp_bak;
  
  update myview1 set ename = 'hehe',sal = 1 where empno 7369;  //通过视图修改原表数据。
  
  delete from myview1 where empno = 7369;  //通过试图删除原表数据。

5、视图的作用？

视图可以隐藏表的实现细节。保密级别较高的系统，数据库只对外提供相关的视图，java程序员只对视图对象进行CRUD。

十三、DBA命令

1、在数据库当中的数据导出

    在windows的DOS命令窗口中执行： (导出整个库)
    
    mysqldump testnode>D:\testnode.sql -uroot -p999
    //导出指定的库testnode  >(到) 相应的位置，需要验明账号密码
    //导出后数据库中就没有了，使用需要重新source
    
    在windows的dos命令窗口中执行：(导出数据库中指定的表)
    mysqldump testnode emp>D:\testnode.sql -uroot -p999

2、导入数据

    create database testnode;
    use testnode;
    source D:\testnode.sql

十四、数据库设计三范式(重点内容，面试经常会问)

1、什么是设计范式？

设计表的依据。按照这三个范式设计的表不会出现数据冗余。

2、三范式都是哪些？

范式解释备注
第一范式任何一张表都应该有主键，并且每一个字段原子性不可再分。
第二范式建立在第一范式的基础上，所有非主键字段完全依赖主键，不能产生部分依赖。多对多？三张表，关系表两个外键
第三范式建立在第二范式的基础上，所有非主键字段直接依赖主键，不能产生传递依赖。一对多？两张表，多的表加外键。

提醒：在实际的开发中，以满足客户需求为主，有的时候会拿冗余换执行速度。

（表连接越多，sql速度越慢，笛卡尔积原因）

3、一对一怎么设计？

3.1 主键共享

t_user_login 用户登陆表

    id(pk)       username        password
    ----------------------------------------
​
•     1             zs                123
•     2             ls                456  

 t_user_detail 用户详细信息表
  id(pk+fk)         realname          tel          ...
 ----------------------------------------------------
    1              张三            11111111112234
    2              李四            12112523432412

3.2外键唯一。

   t_user_login 用户登陆表
​
    id(pk)       username        password
    ----------------------------------------
​
•     1             zs                123
•     2             ls                456

t_user_detail 用户详细信息表

      id(pk)      realname          tel            userid(fk+unique)      
•     ----------------------------------------------------
•       1          张三         111111114          2
•       2          李四         121432412          1

Update：2022/07/07

avatar
文章
阅读
粉丝