Mysql数据库

一.sql语言分类

DQL(数据查询语言query):select

DML(数据操作语言Manipulation):insert,delete,update，对表格数据操作

DDL(数据定义语言Definit):create,drop,alter,对表格操作

DCL(数据控制语言control):grant授权，revoke撤销

TCL(事务控制语言Transaction):commit事务提交，rollback事务回滚

二.mysql常用命令

登录mysql：mysql -uroot -p123456

隐藏密码登录：mysql -uroot -p

退出mysql：exit

查看mysql数据库的版本号：select version();

查看mysql中的数据库: show databases; 

选择使用某个数据库： use 库名;

创建数据库：create database 库名;

查看某个数据库下有哪些表：show tables;

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

 select database();

 导入一下某个库的数据：

 source D:\course\xxx.sql；

 (文件路径不能有中文名)

三.DQL

3.1查询

3.1.1简单查询

查看表中数据：

select * from 表名;

(*表示所有)

查询一个字段：

select 字段名 from 表名;

select name from Student;

查询两个字段或者多个字段：

select 字段名1,字段名2 from 表名;

select name,age from s;

查询所有字段：

select a,b,c,d,e,f... from tablename;

或者select * from 表名; 

查看表结构：

desc 表名；

Describe描述 

给查询的列起别名：

select a,b as bb from c;

as关键字可以省略：

select a,b bb from c;

别名里面有空格->使用引号：

select a,b 'b b' from c; //加单引号（标准） 

计算年薪：select name,sal*12 from emp; //字段可以使用数学表达式

计算年薪并且起别名： select name,sal*12 as ysal from emp; 

计算年薪并且起别名（中文）：select name,sal*12 as '年薪' from emp; 

3.1.2条件查询

select
            字段1,字段2,字段3....
from 
            表名
where
            条件;
条件：>,<,>=,<=,and,or,is null,is not null,between...and..,and or,in,not in

 例：

= 等于
查询薪资等于800的员工姓名和编号：select empno,ename from emp where sal = 800;

查询a的编号和薪资：select nom,sal from emp where name = 'a'; //字符串使用单引号

<>或!= 不等于
    查询薪资不等于800的员工姓名和编号:

        select empno,ename from emp where sal != 800;
        select empno,ename from emp where sal <> 800; // 小于号和大于号组成的不等号

< 小于
    查询薪资小于2000的员工姓名和编号:select empno,ename from emp where sal < 2000;

<= 小于等于
    查询薪资小于等于3000的员工姓名和编号:

        select empno,ename,sal from emp where sal <= 3000;

> 大于
    查询薪资大于3000的员工姓名和编号:
        select empno,ename,sal from emp where sal > 3000;

between … and …. 两个值之间, 等同于 >= and <=
    查询薪资在[2450,3000]之间的员工信息:
       1. select empno,ename,sal from emp where sal >= 2450 and sal <= 3000;

       2. select 
                empno,ename,sal 
            from 
                emp 
            where 
                sal between 2450 and 3000;

is null为空 

     查询哪些员工的津贴为null:
        select empno,ename,sal,comm from emp where comm = null;//错误，不能用等号
        select empno,ename,sal,comm from emp where comm is null;

is not null 不为空

     查询哪些员工的津贴/补助不为null？
         select empno,ename,sal,comm from emp where comm is not null;

and 并且
    查询工作岗位是teacher并且工资大于2500的员工信息：
        select 
            empno,ename,job,sal 
        from 
            emp 
        where 
            job = 'teacher' and sal > 2500;

or 或者
    查询工作岗位是MANAGER和SALESMAN的员工？

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

and和or优先级：

     查询工资大于2500，并且部门编号为10或20部门的员工：

        select 
            *
        from
            emp
        where
            sal > 2500 and (deptno = 10 or deptno = 20);

in 包含，相当于多个 or

    查询工作岗位是teacher和manger的员工:
         select no,name,job from emp where job = 'teacher' or job = 'manger';

         select no,name,job from emp where job in(teacher', 'manger');

not in 表示不在这几个值当中的数据:

    查询工资不是800，5000，3000
         select ename,sal from emp where sal not in(800, 5000, 3000); 

3.1.3模糊查询

%百分号：匹配任意多个字符
_下划线：任意一个字符

找出名字中含有O的:select ename from emp where ename like '%O%';

找出名字以T结尾的:select ename from emp where ename like '%T'; 
找出名字以K开始的:select ename from emp where ename like 'K%';

找出第二个字每是A的:select ename from emp where ename like '_A%';
找出第三个字母是R的:select ename from emp where ename like '_ _R%';

找出名字中有“_”的： select name from t_student where name like '%\_%'; // \转义字符。

3.1.4排序

默认升序

查询所有员工薪资，排序：
    select 
        ename,sal
    from
        emp
    order by
        sal; // 默认是升序

降序desc 

select 
        ename,sal
from
        emp
order by
        sal desc;

升序asc 

select 
        ename,sal
from
        emp
order by
        sal asc;

2个字段排序

查询员工名字和薪资，要求按照薪资升序，如果薪资一样的话，再按照名字升序排列：
    select 
        ename,sal
    from
        emp
    order by
        sal asc, ename asc; // sal在前，起主导，只有sal相等的时候，才会启用ename排序

找出工资在1250到3000之间的员工信息，要求按照薪资降序排列：
    select 
        ename,sal
    from
        emp
    where
        sal between 1250 and 3000
    order by
        sal desc;

3.1.5数据处理函数 

单行处理函数的特点：一个输入对应一个输出。

多行处理函数:多行处理函数特点：多个输入，对应1个输出！

单行处理函数

lower(字段) 转换小写

upper (字段)转换大写

substr (字段)取子串

concat(字段)进行字符串的拼接

length (字段)取长度

trim()去空格

round(数据,n)四舍五入(n表示保留到几位)

rand()生成【0，1）随机数

select ‘abc’ from emp;

select 1000 from emp;

注意：select后面可以跟某个表的字段名（可以等同看做变量名），也可以跟字面量/字面值（数据）。

+-----+
| abc |
+-----+
| abc |
| abc |
| abc |
| abc |
| abc |
| abc |
+-----+

+------+
| 1000 |
+------+
| 1000 |
| 1000 |
| 1000 |
| 1000 |
| 1000 |
+------+

lower 转换小写：
        select lower(name) as name from emp;

upper 转换大写
        select * from t_student;

substr 取子串( 被截取的字符串, 起始下标,截取的长度)）
        select substr(ename, 1, 1) as ename from emp;

注意：起始下标从1开始，没有0.

例

找出员工名字第一个字母是A的员工信息？
            第一种方式：模糊查询
                select ename from emp where ename like 'A%';
            第二种方式：substr函数
                select 
                    ename 
                from 
                    emp 
                where 
                    substr(ename,1,1) = 'A';

concat函数进行字符串的拼接
        select concat(empno,ename) from emp;

length 取长度
        select length(ename) as enamelength from emp;

as可以省略

trim 去空格

         select * from emp where ename = trim('   KING');//正确

        select * from emp where ename = '  KING';//错误
        Empty set (0.00 sec)      

round()四舍五入

select round(1236.567, 0) as result from emp; //保留整数位1237

select round(1236.567, 1) as result from emp; //保留1个小数1236.6
select round(1236.567, 2) as result from emp; //保留2个小数1236.56
select round(1236.567, -1) as result from emp; // 保留到十位1240

select round(1236.567, -2) as result from emp;//保留到百位1200

rand() 生成随机数
        select round(rand()*100,0) from emp; // 100以内的随机数

ifnull(com,0) 可以将com列的null数组转换成0
        ifnull是空处理函数。专门处理空的。
        在所有数据库当中，只要有NULL参与的数学运算，最终结果就是NULL。
       select ename, (sal + ifnull(comm, 0)) * 12 as yearsal from emp;//补助为NULL的时候，将补助当做0

分组函数

count    计数
sum    求和
avg    平均值
max    最大值
min    最小值

注意：
        (1).分组函数在使用的时候必须先进行分组，然后才能用。
        (2).如果你没有对数据进行分组，整张表默认为一组。

        (3).分组函数自动忽略NULL，你不需要提前对NULL进行处理。

        (4).分组函数中count(*)表示统计表当中的总行数。

        (5).分组函数中count(具体字段)表示统计该字段下所有不为NULL的元素的总数。

        (6).分组函数不能够直接使用在where子句中。select ename,sal from emp where sal > min(sal);是错误的

        (7).所有的分组函数可以组合起来一起用。

select sum(sal),min(sal),max(sal),avg(sal),count(*) from emp;

select max(sal) from emp;//查询最高工资

select min(sal) from emp;//查询最低工资

select count(sal) from emp;//查询工资之和

select avg(sal) from emp;//查询平均工资

3.1.6分组查询

通过group by分组

执行顺序不能变：

select
...(参加分组的字段)和(分组函数)
from
...(表)
where
...(不能使用分组函数)条件
group by
...(按照什么分组)

having

...(对分组的内容继续筛选)
order by

...(排序)

在一条select语句当中，如果有group by语句的话，select后面只能跟：参加分组的字段，以及分组函数。其它的一律不能跟。 

按照工作岗位分组，然后对工资求和：
select 
        job,sum(sal)
from
        emp
group by
        job;

 找出每个部门的最高薪资：

select

        depno,max(sal)

from

        emp

group by

        depno;

找出“每个部门，不同工作岗位”的最高薪资:

技巧：两个字段联合成1个字段看.(两个字段联合分组）

select 

        depno,job,max(sal)

from

        emp

group by

        depno,job;

使用having可以对分完组之后的数据进一步过滤。
    having不能单独使用，having不能代替where，having必须
    和group by联合使用。

找出每个部门最高薪资，要求显示最高薪资大于3000的:

select

        deptno,max(sal)

from

        emp

group by

        deptno

having

        max(sal)>3000;//效率低

select

        deptno,max(sal)

from

        emp

where

        sal>3000

group by

        depno;//效率高

优化策略：
where和having，优先选择where，where实在完成不了了，再选择having。

找出每个部门平均薪资，要求显示平均薪资高于2500的：
sleect

        depno,avg(sal)

from

        demp

group by

        depno

having

        avg(sal)>2500;

case..when..then..when..then..else..end
        当员工的工作岗位是MANAGER的时候，工资上调10%，当工作岗位是SALESMAN的时候，工资上调50%,其它正常。
        （注意：不修改数据库，只是将查询结果显示为工资上调）
        select 
            ename,
            job, 
            sal as oldsal,
            (case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal 
        from 
            emp;

找出每个岗位的平均薪资，要求显示平均薪资大于1500的，除MANAGER岗位之外，要求按照平均薪资降序排。

select

        job，avg(sal) as avgsal

from emp

where

         job <> 'manager‘

group by

        job

having

        avg(sal)>1500

order by

        avgsal desc;

3.1.7细节 

顺序不能变：
        select..
        from...
        where...

        group by...

        having...
        order by...

注意：只是将显示的查询结果列名显示为bb，原表列名还是叫：b
记住：select语句是永远都不会进行修改操作的。（因为只负责查询）

3.1.8查询去重

查询去重：distinct

 select distinct job from emp;

select ename,distinct job from emp;//错误

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

统计一下员工的工作岗位数量：

        select count(distinct job) from emp;

3.1.9连接查询

定义

什么是连接查询？
从一张表中单独查询，称为单表查询。emp表和dept表联合起来查询数据，从emp表中取员工名字，从dept表中取部门名字。这种跨表查询，多张表联合起来查询数据，被称为连接查询。

分类

内连接：等值连接，非等值连接，自连接

外连接：左外连接（左连接），右外连接（右连接）

全连接（不讲）

限制

当两张表进行连接查询，没有任何条件限制的时候，最终查询结果条数，是两张表条数的乘积。

怎么避免？

连接时加条件，满足这个条件的记录被筛选出来

 内连接--等值连接

查询每个员工所在部门名称,显示员工名和部门名:

//inner可以省略

select

     e.ename,d.dname

      //ename是员工名字，dname是部门名字

from

        emp e

inner join

        dept d

on 

         e.deptno=d.deptno;

好处：表连接的条件是独立的，连接之后，如果还需要进一步筛选，再往后继续where

select 
        ...
from
            a
join//省略inner
            b
on
            a和b的连接条件
where
            筛选条件

内连接--非等值连接

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

(emp表中有工资，salgrade表中有工资范围对应的等级，2个表没有关系)

select

        e.ename,e.sal,s.grade

from

         emp e

join

        salgrade s

on        

        e.sal between s.losal and s.hisal;

        //条件不是一个变量关系，称为非等量连接

内连接--自连接

自连接，技巧：一张表看做两张表。

查询员工的上级领导，要求显示员工名和对应的领导名：

select

        a.ename as '员工名'，b.ename as '领导名'

from

         emp a

join

        emp b

on

        a.mgr=b.empno;

        //员工的领导编号 = 领导的员工编号

外连接--右外连接

select e.ename,d.name

from emp e

right outer join//outer可以省略

dept d

on e.depton=d.depton;

right代表什么：表示将join关键字右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表。
在外连接当中，两张表连接，产生了主次关系。

外连接--左外连接

select

        e.ename,d.dname

from

        dept d

left outer join

        emp e

on

        e.depno=d.deptno;

查询每个员工的上级领导，要求显示所有员工的名字和领导名：

select

        a.ename as'员工名' ，b.ename as '领导名'

from

         emp a

left join

         emp b

on

        a.mgr=b.empno;

 三、四张表连接

语法：

select 

        ...

from

        a

join

        b

on

        a,b的连接条件

join 

        c

on

        a,c的连接条件

right join

        d

on

        a,d的连接条件;

找到每个员工的部门名称以及工资等级，要求显示员工名，部门名，薪资，薪资等级

select

        e.ename,e.sal,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;

找出每个员工的部门名称以及工资等级，和上级领导，要求显示员工名，领导名，部门名，薪资，薪资等级

select

        e.ename,l.ename,e.sal,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

left join

        emp l

on

         e.mgr=l.empon;//e领导编号=l的领导编号

//dname 是部门名

3.1.10子查询

select语句中嵌套select语句，被嵌套的select语句称为子查询

子查询都可以出现在哪里呢？

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

where子句中的子查询

找出比最低工资高的员工的姓名和工资：

(1).先找出最低工资

select min(sal) from emp//800;

(2).找出大于800的

select ename,sal from emp where sal>800;

(3).合并

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

select ename,sal

from emp

where sal>min(sal);

//错误，where子句不能直接使用分组函数

from子句中的查询

找出每个岗位的平均工资的薪资等级：

技巧：可以将子查询的查询结果当做一张临时表

(1).先找出每个岗位的平均工资

select job,avg(sal) from emp group by job;//表t

(2).把以上查询结果当作一张真实存在的表t

select * from salgrade;//s表

(3).把t表和s表进行连接

条件：t表的avg(sal) between s.losal and s.hisal;

(4).合并

select

        t.*,s.grade

from

        (select job,avg(sal) as avgsal from emp group by job) t

join

        salgrade s

on         

        t.avgsal between s.losal and s.hisal;

s表

s表和t表连接：

select子句的子查询(了解)

找出每个员工的部门名称，要求显示员工名，部门名：

select 
        e.ename,e.deptno,(select dname from dept) as dname
from
        emp e;

//上面的错误：注意：对于select后面的子查询来说，这个子查询只能一次返回1条结果，
    多于1条，就报错了

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

3.2union合并查询结果集

查询工作岗位是MANAGER和SALESMAN的员工：

select ename,job from emp where job='manager' or job='salesman';

等价于

select ename,job from emp where job in('manager' , 'salesman');

等价于 

select ename,job from emp where job='manager'

union

select ename,job from emp where job='salsman';

union可以减少匹配的次数，可以完成两个结果集的拼接。

注：union在进行结果集合并的时候，要求两个结果集的列数相同。

oracle语法严格结果集合并时列和列的数据类型也要一致。

3.3limit(非常重要)

limit作用

将查询结果集的一部分取出来。通常使用在分页查询当中。

limit用法

完整用法：

limit startIndex ,length(startIndex是起始下标，length是长度)

省略用法：

limit 5;//取前五

起始下标默认0

按照工资降序，取出排在前五的员工：

select ename,sal

from emp

order by sal desc

limit 0,5;

或

select ename ,sal

from emp

order by sal desc

limit 5;

limit在order by之后执行

取出工资排在【3，5】的员工：

select ename,sal

from emp

order by sal desc

limit 2,3;

2表示起始位置，3表示长度

取出工资排在【5-9】的员工：

select ename,sal

from emp

order by sal

limit 4,5;

分页

公式：

int pageNo=5//第五页

int pageSize=10;//每页显示10条

limit (pageNo-1)*Size , Size

3.4DQL总结

select...

from...

where...

group by...

having...

order by...

limit....

执行顺序：

1.from

2.where

3.group by

4.having

5.select

6.order by

7.limit

四.DDL数据定义语言

数据定义语言包括：create，drop，alter

4.1表的创建

语法：

create table 表名（字段1 数据类型，字段2 数据类型，...）;

或

create table 表名（

        字段1 数据类型，

        字段2 数据类型,

        ...

）；

表名：建议t_或者tbl_

字段名：见名知意

表名和字段名都属于标识符

表的快速创建：

4.2mysql数据类型

varchar(最长255)
1.可变长度的字符串
2.比较智能，节省空间。
3.会根据实际的数据长度动态分配空间。

char(最长255)
1.定长字符串
2.不管实际的数据长度是多少。
3.分配固定长度的空间去存储数据。
4.使用不恰当的时候，可能会导致空间的浪费。

选择：

姓名每个人长度不一样，选varchar

性别长度一样，选char

int(最长11)整形

bigint长整型，相当于long

 float单精度浮点数据

double双精度浮点型数据

date短日期类型：年月日

datetime长日期类型：年月日时分秒

clob字符大对象

存储文章，说明

blob二进制大对象

存储图片、声音、视频等媒体数据

4.3表的创建

创建一个学生表：学号，姓名，年龄，性别，邮箱地址：

create table t_student(

        no int,

        name varchar(32),

        sex char(1),

        age int(3),

        email varchar(255)

);

删除表：

drop table t_student;//如果表不存在就会报错

drop table if exists t_student;//如果这张表存在的话，删除

修改表：

alter： 修改表结构的操作不经常使用

五.DML数据操作语言

5.1插入数据insert

语法格式：

insert into 表名（字段1，字段2，字段3...） values （值1，值2，值3...）;

注意：字段名和值要一一对应

insert into t_student(no,name,sex,age,email) values (1,'qwq'，‘m’,20,'qwq.com');

insert into t_student(email,name,age,sex,no) value ('zhansan.com','zhansan',10,'m',2);

insert into t_student(name) value ('lisi');

//没有给其他字段指定值时，默认null

//字段名可以省略，相当于所有值都要写上

insert into t_student values(2,'lisi','f',20,'lisi.com');

可以插入多条数据：

语法：

insert into 表名 （字段1，字段2）values（),(),(); 

insert into t_user(id,name,brith,creat_time) values

(1,'zs' ,'1999-10-10' , now()),

(2,'ls' , '2001-01-10' , now());

5.2插入日期insert

格式化数字：format

语法：format(数字，‘格式’)

select ename,format(sal, '$999,999') as sal from emp;//每三位

str_to_date:将字符串varchar类型转为date类型

srt_to_date('字符串日期' ，‘日期格式’)

date_format:将date类型转为具有一定格式的varchar字符串类型

date_format(日期类型数据，‘指定日期格式’)

通常使用在查询

命名规范：表的名字和属性和属性类型都是小写的，单词之间用下划线连接

应用：

创建一个表生日属性是字符串的

create table t_user(

        id int,

        name varchar(32),

        birth char(10)

);

要求插入数据生日是date类型的：

insert into t_user values (1,'qwq',str_to_date('10-01-2024', '%d-%m-%Y'));

应用：

查询的时候指定格式展示：

select id,name,date_format(birth, '%m/%d/%Y') as birth from t_user;

自动将数据库中的date类型转换成varchar类型。并且采用的格式是mysql默认的日期格式：'%Y-%m-%d'

mysql短日期默认格式：%Y-%m-%d
mysql长日期默认格式：%Y-%m-%d %h:%i:%s

在mysql当中获取系统当前时间：

now():获取长日期类型数据

insert into t_user values (2,'lisi','2002-02-02' , now());

5.3修改update

语法格式：

update 表名 set name='jack' ,birth='2002-02-20' where id=2;

注意：不加条件会更新所有的数据

5.4删除数据delect

语法格式：

delect from 表名 where 条件；

注意：没有条件，整个表都会被删除

delect from t_user where id=2;

快速删除

truncate table 表名；(DDL)

delete和truncate比较：

(1).delect表中的数据被删除了，但是数据在磁盘上的存储空间不会被释放，缺点是效率低，优点是支持回滚，可以恢复数据

(2).truncate优点，效率高，表被一次截断，物理删除，缺点不支持回滚

六.约束(重要)

6.1.约束有哪些

主要学习下面四个：

非空约束：not null

唯一性约束：unique

主键约束：primary key

外键约束：foreign key

6.2.非空约束not null

被约束的字段不能为空

create table t_vip(

        id int,

        name varchar(255) not null

);

insert into t_vip(id,name) values (1,'qwq');正确

insert into t_vip(id) values(2)//错误，姓名不能为空

6.3唯一性约束unique

被约束的字段不能重复，但可以是null

drop table if exists t_vip;

create table t_vip(

        id int,

        name varchar(255) unique,

        email varchar(255)

);

insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');//正确

insert into t_vip(id,name,email) values(4,'wangwu','wangwu@sina.com');//正确

insert into t_vip(id,name,email) values(4,'wangwu','wangwu@sina.com');//错误，已经有一个王五了

insert into t_vip(id) values(4);//正确，可以为空

表级约束 

新需求：name和email两个字段联合起来具有唯一性

错误的创建：

drop table if exists t_vip;
        create table t_vip(
            id int,
            name varchar(255) unique,  // 约束直接添加到列后面的，叫做列级约束。
            email varchar(255) unique
        );//这样是名字不能重复或邮件都不能重复

正确：

drop table if exists t_vip;

create tabel t_vip(

        id int,

        name varchar(255),

        email varchar(255),

        unique(name,email)// 约束没有添加在列的后面，这种约束被称为表级约束。

);//这样是名字和邮箱联合起来不唯一

unique和not null联合

drop table if exists t_vip;
        create table t_vip(
            id int,
            name varchar(255) not null unique
        );

//在mysql中，如果一个字段同时被not null和unique约束的话，该字段自动变成主键字段。

//oracle中不一样

6.4主键约束primary key(pk)

主键：not null+unique(主键不能为空，也不能重复)

一个表只能有一个主键

主键值建议使用：

int

bigint

char

等类型 

//列级约束

drop table if exists t_vip;

create table t_vip(

        id int primary key,//id不能重复

        name varchar(255)

);

insert into t_vip(id ,name) values(1,'zhangsan');

insert into t_vip(id,name) values(2,'san');

//表级约束：给多个字段联合起来添加约束

drop table if exists t_vip;

creat table t_vip(

        id int primary key,

        name varchar(255),

        primary key(id,name)

);

//在实际开发中不建议使用：复合主键。建议使用单一主键！
因为主键值存在的意义就是这行记录的身份证号，只要意义达到即可，单一主键可以做到。
复合主键比较复杂，不建议使用 

6.5外键约束foreign key(fk)

设计一张班级和学生表

如果把班级和学生表存储到一张表中，会出现数据冗余，很多重复的数据。

所有，创建一张班级表和一张学生表

 t_class 班级表

classno(pk)班级号	classname班级名
101	北京市大兴区亦庄镇第二中学高三1班
102	北京市大兴区亦庄镇第二中学高三2班

t_student 学生表

no(pk)学生编号	name	cno(fk)引用t_class表的classno
1	qwq	101
2	o.o	101
3	zhansan	102
4	lisi	102

当cno字段没有任何约束的时候，可能会导致数据无效。可能出现一个103，但是103班级不存在。
所以为了保证cno字段中的值都是101和102，需要给cno字段添加外键约束。
那么：cno字段就是外键字段。cno字段中的每一个值都是外键值。

子表中的外键引用的父表中的某个字段，被引用的这个字段必须是主键吗？
不一定是主键，但至少具有unique（唯一）约束。 

外键可以为null

注意：

t_class是父表
t_student是子表

 删除表的顺序？
         先删子，再删父。

 创建表的顺序？
         先创建父，再创建子。

 删除数据的顺序？
          先删子，再删父。

 插入数据的顺序？
        先插入父，再插入子。

七.存储引擎

7.1概念

存储引擎就是把表存储起来的一种方式。

7.2添加

可以在建表的时候在最后；后面加engine指定引擎。

drop table if exists t_vip;
create table t_vip(
        id int primary key auto_increment, //auto_increment表示自增，从1开始，以1递增！
        name varchar(255)
);engine=innoDB auto_increment=11 charset=utf-8

mysql默认的存储引擎是：InnoDB
mysql默认的字符编码方式是：utf8   

7.3查看mysql支持的存储引擎

show engines \G

7.4常用引擎

(1).Myisam存储引擎

把一张表存储为以下三个文件：

格式文件 — 存储表结构的定义（mytable.frm）
数据文件 — 存储表行的内容（mytable.MYD）
索引文件 — 存储表上索引（mytable.MYI）：索引是一本书的目录，缩小扫描范围，提高查询效率的一种机制。

提示一下：
            对于一张表来说，只要是主键，
            或者加有unique约束的字段上会自动创建索引。

优点：
        可被转换为压缩、只读表来节省空间
        这是这种存储引擎的优势！！！！
 缺点：       
        MyISAM不支持事务机制，安全性低。

(2).innoDB存储引擎

把表存储为.frm格式

提供一组用来记录事务性活动的日志文件
用 COMMIT(提交)、SAVEPOINT 及ROLLBACK(回滚)支持事务处理

这是mysql默认的存储引擎，同时也是一个重量级的存储引擎。

优点：

        InnoDB支持事务，支持数据库崩溃后自动恢复机制。
        InnoDB存储引擎最主要的特点是：非常安全。

缺点：

        效率不是很高，并且也不能压缩，不能很好的节省存储空间。

(3).memory 存储引擎

在数据库目录内，每个表均以.frm 格式的文件表示。

表数据及索引被存储在内存中。

优点：

        查询效率是最高的。不需要和硬盘交互。
缺点：

        不安全，关机之后数据消失。因为数据和索引都是在内存当中。

八.事务

8.1什么是事务

一个事务其实就是多条DML语句同时成功，或者同时失败

只有DML语句才会有事务这一说，其它语句和事务无关
insert，delete，update，只有以上的三个语句和事务有关系，其它都没有关系。

8.2事务执行过程

innoDB存储引擎：

事务开始，

insert

insert

delect

update

update

事务结束。

在事务的执行过程中，每一条DML的操作都会记录到“事务性活动的日志文件”中。
在事务的执行过程中，我们可以提交事务，也可以回滚事务。

提交事务？

清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中。
提交事务标志着，事务的结束。并且是一种全部成功的结束。

回滚事务？

将之前所有的DML操作全部撤销，并且清空事务性活动的日志文件
回滚事务标志着，事务的结束。并且是一种全部失败的结束。

8.3提交和回滚

提交事务：commit; 语句
回滚事务：rollback; 语句（回滚永远都是只能回滚到上一次的提交点！）

mysql默认是自动提交事务的，不符合实际情况。如何关闭自动提交？

start transaction;

#回滚例子：

use bjpowernode;#使用这个数据库
select * from dept_bak;#查询数据库中的bak表
start transaction;#开始事务
insert into dept_bak values(10,'abc', 'tj');
insert into dept_bak values(10,'abc', 'tj');
rollback;#回滚
select * from dept_bak;#查询不到任何数据，返回到事务开始的地方

#提交事务
use bjpowernode;
select * from dept_bak;#没有数据
start transaction;
insert into dept_bak values(20,'abc','bj');
insert into dept_bak values(20,'abc','bj');
insert into dept_bak values(20,'abc','bj');
commit;
select * from dept_bak;#查询到3个数据
roolback;#回滚，只能回到上次提交的地方
select* from dept_bak;#还是3个数据

8.4事务特性

A：原子性
        说明事务是最小的工作单元。不可再分。

C：一致性
        所有事务要求，在同一个事务当中，所有操作必须同时成功，或者同时失败，
        以保证数据的一致性。

I：隔离性
        A事务和B事务之间具有一定的隔离。
        教室A和教室B之间有一道墙，这道墙就是隔离性。

D：持久性
        事务最终结束的一个保障。事务提交，就相当于将没有保存到硬盘上的数据
        保存到硬盘上。

8.5事务隔离性

事务和事务隔离有四个级别：读未提取，读已提取，可重复读，序列化

(1).读未提取：(read uncommitted)

1.最低级别隔离，事务a可以读取到事务b未提交的数据。

2.缺点：脏读现象

(2).读已提取：(read committed)

1.事务a只能读取到事务b提交之后的数据。

2.优点：解决了藏独现象。

3.缺点：不可重复读取数据。(oracle默认的隔离级别)

(3).可重复读：(repeatable read)

1.提交之后也读不到，永远读取的都是刚开启事务时的数据。

2.优点：解决了不可重复读取数据。

3.缺点：可以会出现幻影读。每一次读取到的数据都是幻象。不够真实

(4).序列化/串行化：(serializable)最高级别隔离

1.这种隔离级别表示事务排队并且是synchronized，线程同步（事务同步）

2.优点：每一次读取到的数据都是最真实的

3.缺点：效率是最低的

每种隔离演示视频

九.索引

9.1什么是索引

索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制

主要分为2种：全表扫描和根据索引检索

9.2索引实现原理

1.主键会自动添加索引对象(在mysql中如果字段被unique约束，也会自动创建索引对象)

2.每一张表的任何一条数据在硬盘上都有一个硬盘物理存储编号

3.在mysql当中，索引是一个单独的对象，不同的存储引擎以不同的形式存在。

在MyISAM存储引擎中，索引存储在一个.MYI文件中。

在InnoDB存储引擎中索引存储在一个逻辑名称叫做tablespace的当中。

在MEMORY存储引擎当中索引被存储在内存当中。

不管索引存储在哪里，索引在mysql当中都是一个树的形式存在。

在什么条件下给字段添加索引？

条件1：数据量庞大（到底有多么庞大算庞大，这个需要测试，因为每一个硬件环境不同）
条件2：该字段经常出现在where的后面，以条件的形式存在，这个字段总是被扫描。
条件3：该字段很少的DML(insert delete update)操作。（因为DML之后，索引需要重新排序。）

建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能。
建议通过主键查询，建议通过unique约束的字段进行查询，效率是比较高的。

9.3索引的使用

创建索引：

create index emp_ename_index on emp(ename);

给emp表的ename字段添加索引，起名：emp_ename_index

删除索引：

drop index emp_ename_index on emp(ename);

将emp表上的emp_ename_index索引对象删除。

查看是否使用索引：

explain  select * from emp where ename='king';

看扫描记录rows为1，使用了索引；不为1，没有使用索引。

9.4索引失效

情况1：

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

因为模糊匹配当中以“%”开头了，要尽量避免以%开始。

情况2：

explain select * from emp where ename='king' or job='manager';

使用or的时候会失效，如果使用or那么要求or两边的条件字段都要有索引，才会走索引，如果其中一边有一个字段没有索引，那么另一个字段上的索引也会实现。所以这就是为什么不建议使用or的原因。

情况3：

使用复合索引的时候，索引失效。

create index emp_job_sal_index on emp(job,sal);

explain select * from emp where job = 'MANAGER';

情况4：

在where当中索引列参加了运算，索引失效

create index emp_sal_index on emp(sal);
explain select * from emp where sal = 800;
#不失效

explain selecr *from emp where sal+1=800;
#失效

情况5：

在where当中索引列使用了函数

explain select * from emp where lower(ename) = 'smith';

等等等等

9.5索引分类

单一索引：一个字段上添加索引。
复合索引：两个字段或者更多的字段上添加索引。

主键索引：主键上添加索引。
唯一性索引：具有unique约束的字段上添加索引。
    .....

注意：唯一性比较弱的字段上添加索引用处不大。

十.视图view 

10.1概念

视图：就是把表复制一份，然后改视图里的数据，表里的数据也会跟着变。

注意：只有DQL语句才能以view的形式创建

           create view view_name as (DQL语句)；增删改查cdur

        假设有一条非常复杂的SQL语句，而这条SQL语句需要在不同的位置上反复使用。每一次使用这个sql语句的时候都需要重新编写，很长，很麻烦，怎么办？可以把这条复杂的SQL语句以视图对象的形式新建。在需要编写这条SQL语句的位置直接使用视图对象，可以大大简化开发。并且利于后期的维护，因为修改的时候也只需要修改一个位置就行，只需要
修改视图对象所映射的SQL语句。

作用：简化开发，便于后期维护

10.2视图使用

#创建视图对象：
create view dept2_view as select * from dept;

//创建视图对象
create view
    emp_dept_view
as
    select
        e.name,e.sal,d.dname
    from
        emp e
    join
        dept d
    on
        e.deptno=d.deptno;

//查询视图对象
select *from emp_dept_view;

#删除视图对象：
drop view dept2_view;

注意：只有DQL语句才能以view的形式创建。
           create view view_name as 这里的语句必须是DQL语句;

#面向视图查询
select * from dept2_view;

//面向视图插入
insert into dept2_view(deptno,dname,loc) values(60,'sales','tianjin')；

#面向视图更新
update emp_dept_view set sal=1000 where danem='accounting';

#面向视图删除
delect from dept2_view;

十一.DBA常用命令

11.1数据导入导出

掌握导入导出，其他命令查文档

数据导出：

在doc窗口中执行：

mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p123456
#bjwernode是数据库名，把这个数据库导出到D盘下面

#导出指定表
mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot -p123456

数据导入：

注意：先登录到mysql数据库服务器上，cmd-->mysql -uroot -p123456
        然后创建数据库：create database bjpowernode;
        使用数据库：use bjpowernode
        然后初始化数据库：source D:\bjpowernode.sql

十二.数据库设计三范式

第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分。

第二范式：建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不要产生部分依赖。

第三范式：建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖。
 

第一范式

必须有主键，并且每一个字段都是原子性不可再分。

不是第一范式，修改

第二范式

建立在第一范式的基础之上，
要求所有非主键字段必须完全依赖主键，不要产生部分依赖。

多对多，三张表，关系表两个外键。

上面的表是多对多

不满足第一范式，修改

满足了第一范式,不满足第二，“张三”依赖1001，“王老师”依赖001，显然产生了部分依赖。

修改，设计为三张表学生表，教师表，学生教师关系表

学生表(学生编号，学生姓名)

教师表(教师编号，教师姓名)

学生教师关系表（id，学生编号，教师编号）

第三范式

要求所有非主键字典必须直接依赖主键，不要产生传递依赖。

这张表是1对多，一年一班依赖01，01依赖1001，产生了传递依赖

一对多口诀：一对多，两张表，多的表加外键

口诀：一对一，外键唯一

原文地址：https://blog.csdn.net/m0_71369720/article/details/142147421

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！