MYSQL一站式学习,看完即学完

发布时间:2024-03-19 08:01

数据库

数据库:存储数据的仓库,这个仓库是我们将数据的整合形式的一种比喻。实际上就是一堆文件,这些文件存储了具有特定格式的数据。

有了数据库就需要一种管理工具,让数据符合我们的预期为我们服务,我们将这种管理工具称为数据库管理系统DBMS(DatabaseManagement)。DBMS是专门提供了用来管理操作数据库中的数据的方法,对数据库的管理操作有增删改查(CURD)。

常见的数据库管理系统有:

Mysql、Oracle、MS SqlServer、DB2等

SQL语言的分类

DQL、DML、DDL、TCL、DCL

1.DQL数据查询语言(凡是语句中带有select关键字的都是查询语言)

select.......

2.DML数据库操作语言(凡是对表中的数据进行增删改的都是DML)

DML主要操作的是表中的数据

insert 增、delete删、update改

3.DDL数据定义语言(凡是语句中带有create、drop、alter的都是DDL)

DDL主要操作的是表结构 ,不是表中的数据

create新建、drop删除、alter修改

4.TCL事物控制语言,主要有:

事物提交:commit

事物回滚:rollback

上面的这些语言在后面的文章中都会学到,所以请一定要看到最后面。

mysql常用基本命令

这里先介绍一些最常用,最基本的命令

1.show databases ;查询当前所有的数据库

MYSQL一站式学习,看完即学完_第1张图片

现在看到的数据库都是系统自带的

2.create if not exists 数据库名;创建数据库

3.use 数据库名称 ;使用数据库

当看到Database changed时表示正在使用当前数据库 

4.cource 文件路径名;插入sql文件 到指定数据库中 

注意在插入sql文件的时候一定要完成前面第二步和第三步 

链接:https://pan.baidu.com/s/1hi_kaXgAtQdBdex4NMgzEg?pwd=tt1a 
提取码:tt1a

这里要声明一下这个sql文件的来源,这个sql文件是我在B站上面学习杜老师的MYSQL中,他课程里面的质料,我这里拿来引用一下。这里推荐大家多去看看杜老师的视频,讲的非常好。

(1)首先找到sql文件的目录位置:

(2)在DS窗口中,将桌面上的sql文件直接拖入DS窗口 

 这里需要注意sql文件路径不能有中文。当拖入以后他会自动生成文件所在的路径,这时我们只需要回车就能将sql文件批量导入mysql中。

只要没看到error 就说明导入成功

5.show tables;查看数据库当中的所有表  

MYSQL一站式学习,看完即学完_第2张图片

这里就我们刚才导入sql文件中的所有表。

6.select database();查看当前属于哪个数据库 

单表查询

SQL里面的查询 

一:简单查询 

1.desc 表名;查询表结构 

MYSQL一站式学习,看完即学完_第3张图片

可以看到上面就是dept表中的表信息,里面有字段deptno——部门编号、dname——部门名称、loc——部门地理位置;type表示数据类型(具体后面了解)int——整形,字符长度为2、varchar——字符串类型,长度为14。

2.select * from 表名;或者select 字段1,字段2...表名;查询整张表 

MYSQL一站式学习,看完即学完_第4张图片

3.select 字段 from 表名;查询单个字段,若要查找多个字段 ,字段与字段之间要用英文逗号隔开

MYSQL一站式学习,看完即学完_第5张图片

 4.select 字段 as 别名;给查询的字段起别名 MYSQL一站式学习,看完即学完_第6张图片

 注意,若要起中文的别名,或者别名之间有空格,则要用英文单引号将别名括起来。as可以省略。

 MYSQL一站式学习,看完即学完_第7张图片

注意:字段可以使用数学表达式

例如:查询员工表,显示员工姓名和对应的工资,要求员工工资都扩大12倍。

MYSQL一站式学习,看完即学完_第8张图片

上面是全表的信息,注意看sal——薪资这里列的信息,下面会在它的基础上发生改变。

下面是按照要求查询出来的表。

MYSQL一站式学习,看完即学完_第9张图片

 二:条件查询

条件查询:不是将表中的数据全部查出来,而是根据条件查询出符合调教的数据。

语法格式:

select
    字段1,字段2,字段3...
from
    表名
where
    条件;

1.=  等于

 例如:查询员工姓名为KING的员工信息

MYSQL一站式学习,看完即学完_第10张图片

2.<>或!=  不等于 

 例如:查询薪资不等于5000的员工的姓名和薪资

MYSQL一站式学习,看完即学完_第11张图片

3.<  小于  >  d大于 

例如:查询薪资小于2500的员工的姓名和部门编号

MYSQL一站式学习,看完即学完_第12张图片

例如:查询薪资大于3000的员工姓名和入职时间

4.<=  小于等于  >=大于等于 

5.and  并且 

例如:查询工资大于等于1500并且工作部门编号为30的员工的姓名、薪资、部门编号

6.or  或者 

例如:查询工作岗位为manager和salesman的员工的员工编号、姓名、工作

MYSQL一站式学习,看完即学完_第13张图片

注意:and和or同时出现的时候,and的优先级高于or 

例如:查询工资大于2500,并且部门编号为10或20的部门的员工的姓名、薪资、部门编号。

MYSQL一站式学习,看完即学完_第14张图片

像这样写就是错误的,因为and的优先级比or大,上面这条语句的意思就是将把工资大于2500并且部门编号为10的员工的信息查出来,或者把部门编号为20的员工的信息查出来。显然这不符合我们的要求,若要按要求查询出结果,只需要将后面的语句用括号括起来。向下面这样。

7.in  包含  相当于多个or

例如:查询工作岗位是manager和salesman的员工的信息 MYSQL一站式学习,看完即学完_第15张图片

注意括号里面不是区间,而是将括号里面条件查询出来; 

8.not  可以取非,只要用在is或in中

例如:查询津贴不为空的员工的姓名和津贴

9.between...and...两个值之间,等同于>= and <=; 

例如:查询工资在1000到2000之间的员工的姓名、工作、薪资

MYSQL一站式学习,看完即学完_第16张图片

三:模糊查找 

模糊查询的关键字like,支持%或下划线匹配 

% :匹配任意多个字符

例如:查找处名字中含有O的员工姓名

_:匹配任意一个字符 

例如:查找第二个字母是A的员工姓名

 SQL里面的排序

一:升序 

语法:

select
    字段1,字段2...
from
    表名
order by
    排序字段 asc;

例如:查询所有员工的薪资,升序(mqsql默认是按升序排序)

MYSQL一站式学习,看完即学完_第17张图片

二:降序 

 语法:

select
    字段1,字段2...
from
    表名
order by
    排序字段 desc;

例如:将员工的薪资按照降序排序并输出员工的姓名

MYSQL一站式学习,看完即学完_第18张图片

三:多个字段排序 

语法:

select
    字段1,字段2...
from
    表名
order by
    排序字段 asc/desc,排序字段 asc/desc...;//第一个排序字段在前起主导作用,只有第一个排序字段相等的时候,才会考虑启用第二个排序字段以及后面的字段

 例如:查询员工名字和薪资,要求按照薪资升序,如果薪资一样按照姓名降序

MYSQL一站式学习,看完即学完_第19张图片

从开始到现在,我们主要学习了以下4个关键字,关键字的执行顺序非常重要

select
    ...
from
    ...
where
    ...
order by
    ...

它们的执行顺序是1.from——2.where——3.select——4.order by(排序总是在最后面在执行)

可以理解为从一张表中,根据条件将数据选出来然后进行排序;

SQL中的函数 

一:数据处理函数 

数据处理函数又叫做单行处理函数,单行处理函数的特点:一个输入对应一个输出。

常见的单行处理函数有以下几种:

1.lower  转换小写 

例如:将员工表中的姓名全部用小写代替

MYSQL一站式学习,看完即学完_第20张图片

2.upper  转换大写 

例如:将学生的姓名转换为小写

MYSQL一站式学习,看完即学完_第21张图片

3.substr  取子串(substr(被截取的字符串,起始下标,截取的长度)) 

例如:截取员工姓名的前两个字母

MYSQL一站式学习,看完即学完_第22张图片

4.length  取长度 

例如:计算员工姓名的长度

MYSQL一站式学习,看完即学完_第23张图片

5.trim  去空格 

例如:去除字符‘    lisi   ’的空格

MYSQL一站式学习,看完即学完_第24张图片

6.case...when...then...when...then...else...end;在一则事例中,当条件1满足是,采取什么行动,当条件2满足时,采取什么行动,其他情况则结束(了解类容,实际编写中很少遇到这种需求)

例如:当员工的工作岗位是manager的时候,工资上调10%,当做工岗位是salesman的时候,工资上调50%,其他正常。

select
ename,job,sal old_sal,
(case job when 'manager' then sal*1.1 when 'salesman' then sal*1.5 else sal end)
as new_sal
from emp;

MYSQL一站式学习,看完即学完_第25张图片

7.select 后面跟字面量/字面值;如果在一份表中查询某个字段,select后面跟的是字面量/字面值的话,查询出来的结果都是该字面量/之面值

例如:查询员工表中姓名为king的员工

MYSQL一站式学习,看完即学完_第26张图片

正常的写法是向上面这样,但是一些人不小心他可能会写成下面这样。

MYSQL一站式学习,看完即学完_第27张图片

这样写就错了。

8.round   四舍五入

语法:

select
    字段1,字段2... round(被操作的字段,保留位数)
from
    表名

例如:对员工的工资取整

MYSQL一站式学习,看完即学完_第28张图片

例如:对员工的工资保留到十位 

MYSQL一站式学习,看完即学完_第29张图片

9.rand()  生成随机数 

例如:生成100以内的随机数

MYSQL一站式学习,看完即学完_第30张图片

10. ifnull(数据,被当做哪个值)  可以将null转换成一个具体的值

ifnull是空处理函数,专门用来处理空的。在所有数据库当中,只要有null参与的数学运算,最终的结果都是null 

例如:计算每个员工的年薪(年薪 = (月薪+月补助)*12)

MYSQL一站式学习,看完即学完_第31张图片

如果向上面这样写,津贴为null的员工,计算出来的年薪就会为null。所以为了避免这种状况,就卡可以采用ifnull函数 

MYSQL一站式学习,看完即学完_第32张图片

二:分组函数

分组函数又叫做多行处理函数:多行处理函数是指输入多行,最终输出一行

注意在使用分组函数的时候必须先进性分组,然后才能使用。如果没有对数据进行分组,整张表默认为一组。 

1.count  计数 

例如:计算员工的数量

也可以这样写:select count(*) from emp;结果和上面一样。

2.sum  求和

例如:计算员工工资和

3.avg  求平均值 

例如:计算员工的平均工资

4.max  求最大值        min求最小值 

例如:求员工工资的最大值和最小值 

分组函数需要注意的事项:

1.分组函数自动忽略null,你不需要对null进行处理

例如:统计津贴不为null的员工数量

2.分组函数中count(*)和count(具体字段)的区别:

count(具体字段):表示统计该字段下所有不为null的元素的总数,count(*):统计表中总行数

3.分组函数不能直接使用在where的子句当中

例如:找出比最低工资高的员工信息

不能像下面这样写:

原因是因为在使用分组函数之前必须先分组,而在执行where语句的时候并没有分组。

4.分组函数可以一起使用

 

三:日期函数 

1.format  数字格式化  

语法:

format(数字, '格式')

例如:将员工表中的工资格式化 

MYSQL一站式学习,看完即学完_第33张图片

2.str_to_date:将字符串varchar类型转换成date类型 

语法: 

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

新建一个表(新建表和插入字段在后面详细说)

drop table if exists t_student;
create table t_student(
id int,
name varchar(255),
graduate_time date
);

 插入学生信息

insert into t_student(id,name,graduate_time) values(1,'zhangsan','21-06-2021');

上面直接报错了,说插入的日期值错误。说明我们插入的日期格式不符合mysql的标准,在mysql中日期的格式为:'%Y-%m-%d' 

为了解决上面的问题我们可以使用字符串转日期函数

MYSQL一站式学习,看完即学完_第34张图片

当然我们在插入数据的时候按照mysql的标准来插入数据就不会报错,例如像下面这样。

MYSQL一站式学习,看完即学完_第35张图片

3.date_format;将日期转化成指定的字符串

语法:date_format(日期类型数据, '日期格式')

这个函数通常使用在查询日期方面,设置展示的日期格式。 

例如:将学生毕业的时间按照日——月——年的格式输出

分组查询

分组查询:对需求进行分组,然后对每组的数据进行操作

分组查询语法:

select
    ...
from
    ...
group by
    ...

例如:计算每个部门的工资和

思路:先按照工作岗位分组,然后对工资求和

MYSQL一站式学习,看完即学完_第36张图片 上面的程序的解释为:先从emp表中查询数据,根据job分组,然后对每组的数据进行sum(sal) 

例如:计算每个工作岗位的平均工资

MYSQL一站式学习,看完即学完_第37张图片  

 例如:计算每个工作岗位的最高薪资

MYSQL一站式学习,看完即学完_第38张图片

select ename,job,sum(sal) from emp group by job;

MYSQL一站式学习,看完即学完_第39张图片

可以看到上面的ename字段和后面的查询信息一点关联都没有,所以是多余的,这种语法在oracle当中就是错误的。 

这里有一个结论:在一条select语句当中,如果有group by语句的话,select后面只能跟:参加分组的字段,以及分组函数。其他一律不跟。 

联合分组查询 

 例如:找出每个部门,不同工作岗位的最高薪资

MYSQL一站式学习,看完即学完_第40张图片

将前面学过的关键字全部组合在一起,看看他们的执行顺序

select
    ...
from
    ...
where
    ...
group by
    ...
order by
    ...

执行顺序
1.from
2.where
3.group by
4.select
5.order by

上面关键字的执行顺序非常重要,必须要记住

having

 having的功能和where一样,都是筛选条件。

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

例如:找出每个工作岗位最高薪资,要求显示最高薪资大于3000的

第一步:找出每个工作岗位的最高薪资

MYSQL一站式学习,看完即学完_第41张图片

然后只要根据条件筛选出工资大于3000的即可,这里用到having

 MYSQL一站式学习,看完即学完_第42张图片

其实上面的问题可以优化:

先将工资大于3000的都选出来,然后再分组

MYSQL一站式学习,看完即学完_第43张图片

优化策略:where和having,优先选择where,where实在完成不了了,再选择having 

例如:找出每个部门平均薪资,要求显示平均薪资高于2500(这个案列就不能用到where) 

如果你像下面这样操作,结果是错误的。

MYSQL一站式学习,看完即学完_第44张图片

因为根据我们一再强调的关键字执行顺序,上面语句翻译出来就是:从emp表中现将sal>2500的都选出来,然后根据 部门编号分组,然后再对每组sal求平均值,最终显示出来。显然这样的逻辑不满足我们的要求。

正确的做法是像下面这样:

总结(单表的查询讲完了) :

select
    ...
from
    ...
where
    ...
group by
    ...
having
    ...
order by
    ...


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

 上面的语句解释为:

从某张表中查询数据,先经过where条件筛选出有价值的数据,对这些数据进行分组,分组之后可以使用having继续筛选,select查询出来,最后排序输出。

看一个综合案例:

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

MYSQL一站式学习,看完即学完_第45张图片

distinct  对查询出来的结果去重

例如:对员工表中的工作岗位去重

MYSQL一站式学习,看完即学完_第46张图片

多个字段联合起来去重:要求dintinct关键字必须在多个字段的前面

连接查询

连接查询就是多张表联合起来查询数据

连接查询的分类 

根据语法年代分类

SQL92:1992年的时候出现的语法

SQL99:1999年的时候出现的语法

根据表连接的方式分类

内连接包括等值连接、非等值连接、自连接 

外连接包括左外连接、右外连接

当两张表进行连接查询时,若果没有任何条件限制会发生笛卡尔积现象

所谓笛卡尔积现象就是在多张表查询的时候,最终查询结果条数是多张表记录条数的乘积 ,像下面这种情况。

查询每个员工所在的部门名称:

...

因为员工表有14条记录,部门表有4条记录,所以乘起来就是54条记录。显然这不符合实际开发需求。

为了避免笛卡尔积现象,连接的时候就要家条件,满足条件的才筛选出来。

这里有两种语法:92语法和99语法

92语法

select
    字段1,字段2...
from
    表1,表2...
where
    ...

 以上面的案例为例演示92语法:
MYSQL一站式学习,看完即学完_第47张图片

当然上面的语句还可以简化,就是给表取别名:

MYSQL一站式学习,看完即学完_第48张图片

99语法 

select
    字段1,字段2...
from
    表1
inner join
    表2
on
    连接条件

同样根据上面的案例演示99语法: MYSQL一站式学习,看完即学完_第49张图片

虽然上面查询结果只有14条,但是表与表之间的匹配次数并没有变少,还是54次匹配。

注意:通过笛卡尔积现象,表的连接次数越多效率越低,所以尽量避免表的连接次数

内连接之等值连接 

例如:查询每个员工所在的部门地理位置,显示员工名和部门地理位置

MYSQL一站式学习,看完即学完_第50张图片

内连接之非等值连接 (条件不是一个等量关系所以称为非等值连接)

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

MYSQL一站式学习,看完即学完_第51张图片

内连接之自连接 

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

首先先将员工编号和领导编号都查出来:

MYSQL一站式学习,看完即学完_第52张图片

然后将这样表想象成两张表,这两张表分别是员工表和领导表,然后对这两张表进行连接。

MYSQL一站式学习,看完即学完_第53张图片

内连接的时候两张表的关系是平等的,没有主次关系。 

但是内连接不能解决所有的问题,例如查询每个员工的上级领导,要求显示所有员工的名字和领导名。如果用内连接:上面查询出来的结果只有13条记录,并没有将所有的员工都查询出来,显然不符合要求。这个时候就要用到外连接了。

外连接之左外连接和右外连接

 右外连接语法:

select
    字段1,字段2...
from
    表1
right outer join
    表2
on
    连接条件

左外连接语法

select
    字段1,字段2...
from
    表1
left outer join
    表2
on
    连接条件

右外连接right代表将join关键字右边的这张表看成主表,主要是为了将这张表的数据全部查询出来,捎带着关联查询左边的表,左外连接恰恰相反。

任何一个右连接都有左连接的写法,任何一个左连接都有右连接的写法

在外连接当中,两张表连接,产生了主次关系。

根据上面的案列演示外连接:

MYSQL一站式学习,看完即学完_第54张图片

可以看到king是这个公司的老大,所以他没有boss,现在查出来的记录就是14条。

多张表的连接 

语法:

select
    ...
from
    表1
join
    表2
on
    表1和表2的连接条件
join
    表3
on
    表1和表3的连接条件
left/right outer join
    表4
on
表1和表4的连接条件

 上面的一条sql语句中内连接和外连接可以混合使用

例如:找出每个员工的部门名称和工资等级,要求显示员工名、部门名、薪资、薪资等级

MYSQL一站式学习,看完即学完_第55张图片

子查询 

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

子查询可以出现在where子句中、from语句中、select语句中

where子句中的子查询 

例如:找出比最低工资高的员工姓名和工资

第一步:查询最低工资是多少

第二步:查询大于最低工资的员工姓名和工资

MYSQL一站式学习,看完即学完_第56张图片

第三步合并:

MYSQL一站式学习,看完即学完_第57张图片

from子句中的子查询

 注意:from后面的子查询,可以将子查询的查询结果当做一张临时表(技巧)

例如:找出每个岗位的平均工资的薪资等级

MYSQL一站式学习,看完即学完_第58张图片

select后面出现的子查询(了解即可)

例如:找出每个员工的部门名称,要求显示员工名,部门名

MYSQL一站式学习,看完即学完_第59张图片 

union  合并查询结果集 

对于表的连接来说,每连接一次新表,则匹配的次数满足笛卡尔积,成倍增加。但是使用union可以减少匹配的次数。在减少匹配次数的情况下,还可以完成两个结果的拼接。

例如:查询员工工作岗位是manager和salesman的员工

MYSQL一站式学习,看完即学完_第60张图片 

比如a连接b连接c,a、b、c都是10条记录,则匹配次数为1000次。

使用union:a连接b一个结果:10*10 = 100次;a连接c一个结果:10*10 = 100次,加起来就是200次。显然匹配次数少了很多。

使用union的注意事项:

1.union在进行结果集合合并的时候,要求两个结果集的列数相同。

 

2.结果集合合并时列和列的数据类型也要一致。

MYSQL一站式学习,看完即学完_第61张图片

上面在mysql中语法没问题,结果集合的列数相同,但是列和列的数据类型不一致。

limit的作用:将查询结果集的一部分取出来。通常在分页查询当中,分页查询是为了提高用户体验。

limit的语法:limit startIndex,length

startIndex是起始下标,起始下标从0开始。length是长度 

缺省用法:limit 5——这是取前5个

例如:按照薪资降序,取出排名在前5名的员工

MYSQL一站式学习,看完即学完_第62张图片

注意:mysql当中limit在order by之后执行 

例如:取出工资排名在[3-5]名的员工

MYSQL一站式学习,看完即学完_第63张图片

下标从2开始,就是第3条记录

分页 

每页显示pageSize条记录,第pageNo页:

公式:limit (pageNo-1)*pageSize,pageSize

 

关于DQL语句的大总结:

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

 

mysql中的数据类型

我们在这里只需要掌握一些基本的数据类型

varchar(最长255):可变长度的字符串,比较智能,节省空间。会根据实际的数据长度动态分配空间。优点:节省空间,缺点:需要动态分配空间,速度慢。

char(最长255):定长字符串,不管实际的数据长度是多少。分配固定长度的空间去存储数据。使用不恰当的时候,可能会导致空间的浪费。优点:不需要动态分配空间,速度快。缺点:使用不当可能会导致空间的浪费。

int(最长11):数字中的整数型。等同于java的int。

bigint:数字中的长整型。等同于java中的long。

float:单精度浮点型数据

double:双精度浮点型数据

date:短日期类型只包括年月日信息

datetime:长日期类型包括年月日时分秒信息

clob:字符大对象,最多可以存储4G的字符串。比如:存储一篇文章,存储一个说明。超过255个字符的都要采用CLOB字符大对象来存储。Character Large OBject:CLOB

blob:二进制大对象,Binary Large OBject,专门用来存储图片、声音、视频等流媒体数据。
往BLOB类型的字段上插入数据的时候,例如插入一个图片、视频等,你需要使用IO流才行。

 

建表的语法格式:(建表属于DDL语句,DDL语句包括create、drop、alter)

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

删除表语法格式:drop table if exists 表名

例如:创建一个学生表,表中含有学生的编号、姓名、性别、年龄

MYSQL一站式学习,看完即学完_第64张图片 

快速建表之将一张表的结果查询结果插入到一张表中(了解内容) 

例如:创建一张表,要求表的字段和数据类型都和部门表一样

MYSQL一站式学习,看完即学完_第65张图片

 

向表中插入数据 

插入数据insert (DML)

语法格式:insert into 表名(字段1,字段2,字段3...)values(值1,值2,值3...);

注意:字段名和值要一一对应,即数量要对应,数据类型要对应。 

例如:向学生表中插入学生的信息

MYSQL一站式学习,看完即学完_第66张图片 

删除表中的数据

删除数据  delete (DML) 

语法格式:delete from 表名 where 条件; 

注意:没有条件,整张表的数据都会被全部删除

例如:删除编号为2的学生信息

MYSQL一站式学习,看完即学完_第67张图片

例如:删除整张的信息

 

删除表中的数据之快速删除表中的数据 

delete from 表名;这种删除数据的方式比较慢

truncate table 表名;这种删除表的速度快 

delete语句删除数据的原理?(delete属于DML语句)
表中的数据被删除了,但是这个数据在硬盘上的真实存储空间不会被释放!!!
这种删除缺点是:删除效率比较低。
这种删除优点是:支持回滚,后悔了可以再恢复数据!!!
    
truncate语句删除数据的原理?
这种删除效率比较高,表被一次截断,物理删除。
这种删除缺点:不支持回滚。
这种删除优点:快速。

用法:truncate table dept_bak; (这种操作属于DDL操作。)

大表非常大,上亿条记录????
删除的时候,使用delete,也许需要执行1个小时才能删除完!效率较低。
可以选择使用truncate删除表中的数据。只需要不到1秒钟的时间就删除结束。效率较高。
但是使用truncate之前,必须仔细询问客户是否真的要删除,并警告删除之后不可恢复!

truncate是删除表中的数据,表还在!

 

约束

约束:在创建表的时候,我们可以给表中的字段加上一些约束,来保证这个表中的数据的完整性、有效性。约束的作用就是为了保证表中的数据有效。 

约束包括那些:

非空约束:not null

唯一性约束:unique

主键约束:primary key(简称PK) 

外键约束:foreign key(简称FK)

非空约束:not null

非空约束not null约束的字段不能为null

例如:创建一个学生类,要求学生的姓名不能为空

MYSQL一站式学习,看完即学完_第68张图片

 

如果在列的后面加非空约束,则将这种约束称为列级约束

唯一性约束:unique 

唯一性约束的字段不能重复,但可以为null

例如:创建学生表要求学生姓名不能重复

MYSQL一站式学习,看完即学完_第69张图片 

多个字段联合具有唯一性

例如:创建一个学生表,里面包含学生编号、姓名、邮箱,要求姓名和邮箱两个字段联合起来具有唯一性

MYSQL一站式学习,看完即学完_第70张图片

但是当我们一旦插入的邮箱一样的时候就会报错

 

上面的约束没有添加在列的后面,这种约束称为表级约束,当需要给多个字段联合起来添加某一个约束的时候,需要使用标记约束。

 unique和not null可以联合使用,当unique和not null联合时就变成了主键约束

主键约束:primary key 

任何一张表如果没有主键约束,表无效 

主键约束是一种约束,字段上面添加主键约束,该字段称为主键字段。主键字段上面的值称为主键值,主键值是每一行记录的唯一标识,主键值是每一行的身份证号。 

例如:创建一张学生表,学生表中包含编号、姓名、年龄,要求编号和姓名不能为空也不能重复

MYSQL一站式学习,看完即学完_第71张图片 

 

 

注意:

1.在实际开发中不建议使用复合主键,建议使用单一主键,因为主键值存在的意义就是这行记录的身份证号码,只要达到意义即可,单一主键可以做到。

2.一个表中主键约束不能多于1个 

在mysql中,有一种机制,可以帮助我们自动维护一个主键值

auto_increment表示自增,从1开始,一1递增 

MYSQL一站式学习,看完即学完_第72张图片 

外键约束:foreign key 

外键约束是一种约束,字段上面添加外键约束称为外键字段,外键字段上面的每一个值称为外键值。

现在有一个需求:请设计数据库表,来描述“班级和学生”的信息


		第一种方案:班级和学生存储在一张表中
		t_student
		no(pk)			name		classno			classname
		--------------------------------------------------------------------------------------------------------------
		1			    jack		100			北京市大兴区亦庄镇第二中学高三1班
		2			    lucy		100			北京市大兴区亦庄镇第二中学高三1班
		3			    lilei		100			北京市大兴区亦庄镇第二中学高三1班
		4			    hanmeimei	100			北京市大兴区亦庄镇第二中学高三1班
		5			    zhangsan	101			北京市大兴区亦庄镇第二中学高三2班
		6			    lisi		101			北京市大兴区亦庄镇第二中学高三2班
		7			    wangwu		101			北京市大兴区亦庄镇第二中学高三2班
		8			    zhaoliu		101			北京市大兴区亦庄镇第二中学高三2班
		分析以上方案的缺点:
		数据冗余,空间浪费!
		这个设计是比较失败的!
		
		第二种方案:班级一张表、学生一张表
		
		t_class 班级表
		classno(pk)		classname
		------------------------------------------------------
		100			北京市大兴区亦庄镇第二中学高三1班
		101			北京市大兴区亦庄镇第二中学高三1班
	
		t_student 学生表
		no(pk)			name			cno(FK引用t_class这张表的classno)
		-------------------------------------------------------------------------------------------------
		1			jack			100
		2			lucy			100
		3			lilei			100
		4			hanmeimei		100
		5			zhangsan		101
		6			lisi			101
		7			wangwu			101
		8			zhaoliu			101

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

		注意:
			t_class是父表
			t_student是子表

			删除表的顺序:先删子,再删父。

			创建表的顺序:先创建父,再创建子。

			删除数据的顺序:先删子,再删父。

			插入数据的顺序:先插入父,再插入子。

		思考:子表中的外键引用的父表中的某个字段,被引用的这个字段必须是主键吗?
			不一定是主键,但至少具有unique约束。

			外键值可以为NULL。

 

drop table if exists t_student;
drop table if exists t_class;

create table t_class(
	classno int primary key,
	classname varchar(255)
);
create table t_student(
	no int primary key auto_increment,
	name varchar(255),
	cno int,
	foreign key(cno) references t_class(classno)
);

insert into t_class(classno, classname) values(100, '北京市大兴区亦庄镇第二中学高三1班');
insert into t_class(classno, classname) values(101, '北京市大兴区亦庄镇第二中学高三2班');

insert into t_student(name,cno) values('jack', 100);
insert into t_student(name,cno) values('lucy', 100);
insert into t_student(name,cno) values('lilei', 100);
insert into t_student(name,cno) values('hanmeimei', 100);
insert into t_student(name,cno) values('zhangsan', 101);
insert into t_student(name,cno) values('lisi', 101);
insert into t_student(name,cno) values('wangwu', 101);
insert into t_student(name,cno) values('zhaoliu', 101);

select * from t_student;
select * from t_class;

MYSQL一站式学习,看完即学完_第73张图片

存储引擎 (了解内容)

 存储引擎就是一个表组织/存储数据的方式,不同的存储引擎,表存储数据的方式不同。

查看masql支持哪些存储引擎

show engines; 

MYSQL一站式学习,看完即学完_第74张图片

MySQL中主要的引擎

MyISAM存储引擎
它管理的表具有以下特征:
使用三个文件表示每个表:
格式文件 — 存储表结构的定义(mytable.frm)
数据文件 — 存储表行的内容(mytable.MYD)
索引文件 — 存储表上索引(mytable.MYI):索引是一本书的目录,缩小扫描范围,提高查询效率的一种机制。
可被转换为压缩、只读表来节省空间

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

InnoDB存储引擎
这是mysql默认的存储引擎,同时也是一个重量级的存储引擎。
InnoDB支持事务,支持数据库崩溃后自动恢复机制。
InnoDB存储引擎最主要的特点是:非常安全。

它管理的表具有下列主要特征:
 – 每个 InnoDB 表在数据库目录中以.frm 格式文件表示
– InnoDB 表空间 tablespace 被用于存储表的内容(表空间是一个逻辑名称。表空间存储数据+索引。)

 – 提供一组用来记录事务性活动的日志文件
– 用 COMMIT(提交)、SAVEPOINT 及ROLLBACK(回滚)支持事务处理
 – 提供全 ACID 兼容
– 在 MySQL 服务器崩溃后提供自动恢复
– 多版本(MVCC)和行级锁定
– 支持外键及引用的完整性,包括级联删除和更新
    
InnoDB最大的特点就是支持事务:
以保证数据的安全。效率不是很高,并且也不能压缩,不能转换为只读,
不能很好的节省存储空间。

MEMORY存储引擎
使用 MEMORY 存储引擎的表,其数据存储在内存中,且行的长度固定,
这两个特点使得 MEMORY 存储引擎非常快。

MEMORY 存储引擎管理的表具有下列特征:
 – 在数据库目录内,每个表均以.frm 格式的文件表示。
– 表数据及索引被存储在内存中。(目的就是快,查询快!)
– 表级锁机制。
– 不能包含 TEXT 或 BLOB 字段。

MEMORY 存储引擎以前被称为HEAP 引擎。

MEMORY引擎优点:查询效率是最高的。不需要和硬盘交互。
MEMORY引擎缺点:不安全,关机之后数据消失。因为数据和索引都是在内存当中。

建表的时候可以指定存储引擎,以及字符编码方式

查看表的存储引擎show create table 表名;

MYSQL一站式学习,看完即学完_第75张图片

 

事务(概念很多)

事务就是一个完整的业务逻辑,是一个最小的工作单元,不可再分。事务是批量的DML语句同时成功或者同时失败。

只有DML语句才会有事务一说insert、delete、update,其他语句和事务无关。因为只有DML语句才会对数据库中的数据进行增、删、改等操作,这关乎数据的安全,所以非常重要。 

1.事务的执行机制

InnoDB存储引擎:提供一组用来记录事务性活动的日志文件

事务开启了:
insert
delete
update
事务结束了!

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

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

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

2.提交事务和回滚事务 

提交事务:commit; 语句
回滚事务:rollback; 语句(回滚永远都是只能回滚到上一次的提交点!)

测试一下,在mysql当中默认的事务行为是怎样的?
mysql默认情况下是支持自动提交事务的。(自动提交)
什么是自动提交?
每执行一条DML语句,则提交一次!

MYSQL一站式学习,看完即学完_第76张图片

我们以上面的表操作为例,来演示mysql的自动提交机制:

MYSQL一站式学习,看完即学完_第77张图片 当我们在删除表中的数据的时候我们并没有提交,也就是没有commit,但是mysql自动就帮我们提交了,而且我们此时回滚也回不去。 

这种自动提交实际上是不符合我们的开发习惯,因为一个业务,通常是需要多条DML语句共同执行才能完成的,为了保证数据的安全,必须要求同时成功之后再提交,所以不能执行一条,就提交一条。

 

怎么将mysql的自动提交机制关闭掉呢?先执行这个命令:start transaction;然后添加手动提交命令commit

还是以上面的案列为例:

MYSQL一站式学习,看完即学完_第78张图片

MYSQL一站式学习,看完即学完_第79张图片 

3.事务的ACID特性 

 A:原子性

说明事务是最小的工作单元,不可再分。

C:一致性 

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

I:隔离性 

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

D:持久性 

事务最终结束的一个保障,事务提交以后就相当于将没有保存到硬盘上的数据保存到硬盘上

I:隔离性 

A教室和B教室中间有一道墙,这道墙可以很厚,也可以很薄。这就是事务的隔离级别。
这道墙越厚,表示隔离级别就越高。

事务和事务之间的隔离级别有哪些呢?4个级别

读未提交:read uncommitted(最低的隔离级别)《没有提交就读到了》

什么是读未提交,读未提交就是事务A可以读取到事务B未提交的数据。
这种隔离级别存在的问题就是:脏读现象!(Dirty Read),我们称读到了脏数据。这种隔离级别一般都是理论上的,大多数的数据库隔离级别都是二档起步!

读已提交:read committed《提交之后才能读到》

什么是读已提交?事务A只能读取到事务B提交之后的数据。
这种隔离级别解决了脏读的现象。
这种隔离级别存在什么问题是不可重复读取数据,什么是不可重复读取数据呢?不可重复读取数据就是在事务开启之后,第一次读到的数据是3条,当前事务还没有结束,可能第二次再读取的时候,读到的数据是4条,3不等于4。

这种隔离级别是比较真实的数据,每一次读到的数据是绝对的真实。
oracle数据库默认的隔离级别是:read committed

可重复读:repeatable read《提交之后也读不到,永远读取的都是刚开启事务时的数据》

什么是可重复读取?重复读取就是,事务A开启之后,不管是多久,每一次在事务A中读取到的数据都是一致的。即使事务B将数据已经修改,并且提交了,事务A读取到的数据还是没有发生改变,这就是可重复读。
可重复读解决不可重复读取数据。可重复读存在的问题是可以会出现幻影读。每一次读取到的数据都是幻象。不够真实!
例如:早晨9点开始开启了事务,只要事务不结束,到晚上9点,读到的数据还是那样!读到的是假象。不够绝对的真实。mysql中默认的事务隔离级别就是这个!

序列化/串行化:serializable(最高的隔离级别)

 这是最高隔离级别,效率最低。解决了所有的问题。
这种隔离级别表示事务排队,不能并发!每一次读取到的数据都是最真实的,并且效率是最低的。

4.验证各种隔离级别

 被测试的表为t_product;

MYSQL一站式学习,看完即学完_第80张图片

注意在每一次验证的时候,都需要将mysql的隔离级别更改,命令是:

set global transaction isolation level 不同的隔离级别

修改以后退出mysql,然后重写打开mysql数据库。 

下面是mysql的默认隔离级别:

查看当前mysql的隔离级别命令:select @@tx_isolation 

 

为了演示四种隔离级别,需要开两个mysql窗口

1.演示读未提交read uncommitted

首先修改隔离级别,然后退出mysql

然后开两个mysql窗口,(这里有点多线程的感觉 )各自登录到mysql数据库中打开bjpowernode数据库

MYSQL一站式学习,看完即学完_第81张图片

然后1:事务A、B各自开启事务 、2:事务A查询表t_product、3:事务B向t_product中插入数据、4:事务A查询表t_product

MYSQL一站式学习,看完即学完_第82张图片

可以看到事务B还没有提交事务即还没有commit,事务A中就已经能够查到数据了。 

2.演示读已提交read committed

不要忘了修改隔离级别

步骤还是和上面的一样

 MYSQL一站式学习,看完即学完_第83张图片

可以看到事务A只有在事务B提交事务以后,才能查看到表中的数据 

 

3.演示可重复读 repeatable read

 MYSQL一站式学习,看完即学完_第84张图片

可以看到即使事务B提交了事务,但是事务A还是只能查询到事务A提交之间的记录 

只有当事务A提交以后再次查询,才能查询到事务B提交的事务,像下面这样:

MYSQL一站式学习,看完即学完_第85张图片 

4.验证 序列化/串行化:serializable

 这个就和两个线程占用一个资源一样了,只有当一个事务结束操作以后,两一个事务才能操作

MYSQL一站式学习,看完即学完_第86张图片

可以看到只要事务A不提交事务,事务B就会一直卡着,直到事务B的请求超时,向下面这样:

MYSQL一站式学习,看完即学完_第87张图片 

当事务B再次请求,然后事务A提交事务以后,事务B马上就能查看到数据,像下面这样:

MYSQL一站式学习,看完即学完_第88张图片 

索引

索引是为了提高查询效率存在的一种机制,添加在字段上面。一张表的一个字段可以添加一个索引,多个字段联合起来也可以添加索引。索引相当于一本书的目录,是为了缩小扫描范围而存在的一种机制。

Mysql在查询方面主要有两种方式:第一种方式:全表扫描、第二种方式:根据索引检索

索引的实现原理

    假设有一张用户表:t_user

    id(PK)            name            每一行记录在硬盘上都有物理存储编号
    ----------------------------------------------------------------------------------
    100               zhangsan            0x1111
    120               lisi                0x2222
    99                wangwu              0x8888
    88                zhaoliu             0x9999
    101               jack                0x6666
    55                lucy                0x5555
    130               tom                 0x7777

    提醒1:在任何数据库当中主键上都会自动添加索引对象,id字段上自动有索引,
    因为id是PK。另外在mysql当中,一个字段上如果有unique约束的话,也会自动
    创建索引对象。

    提醒2:在任何数据库当中,任何一张表的任何一条记录在硬盘存储上都有
    一个硬盘的物理存储编号。

    提醒3:在mysql当中,索引是一个单独的对象,不同的存储引擎以不同的形式
    存在,在MyISAM存储引擎中,索引存储在一个.MYI文件中。在InnoDB存储引擎中
    索引存储在一个逻辑名称叫做tablespace的当中。在MEMORY存储引擎当中索引
    被存储在内存当中。不管索引存储在哪里,索引在mysql当中都是一个树的形式
    存在

下列情况可以给给字段添加索引:

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

索引的创建和删除 

 创建索引

create index 索引名 on 表名(被加上索引的字段);

例如:create index emp_ename_index on emp(ename);

给emp表的ename字段添加索引,起名为:emp_ename_index

索引的删除

drop index 索引名 on 表名

例如:drop index emp_ename_index on emp;

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

查看字段上面是否有索引 

explain select * from 表名 where 条件;

MYSQL一站式学习,看完即学完_第89张图片 

索引失效 

索引也有失效的时候

失效的第一种情况,在模糊查询的时候会失效:

MYSQL一站式学习,看完即学完_第90张图片 

失效的第二种情况,使用or的时候会失效。如果使用or那么要求or两边的条件字段都要有索引,才会走索引,如果其中一边有一个字段没有索引,那么另一个字段上的索引也会失效。

索引失效的第三种情况,使用复合索引的时候,没有使用左侧的列查找,索引失效。

MYSQL一站式学习,看完即学完_第91张图片 

视图 

视图是站在不同的角度去看待同一份数据

创建视图 

create view 视图名 as select * from 被复制的表;

删除视图 

drop view 视图名;

注意:只有DQL语句才能以view的形式创建,也就是create view 视图名 as (这里的语句必须是DQL语句) 

MYSQL一站式学习,看完即学完_第92张图片

视图的作用 

我们可以面向视图对象进行增删改查,对视图对象进行增删改查会导致原表被操作(视图的特点就是通过视图能够影响到原表的数据) 

MYSQL一站式学习,看完即学完_第93张图片

视图在开发中的作用:

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

DBA常用命令 

作为java程序员,只需要掌握导入导出命令即可:

    数据导出
        注意:在windows的dos命令窗口中:
            mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p123456
        
        可以导出指定的表吗?
            mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot -p123456

    数据导入
        注意:需要先登录到mysql数据库服务器上。
        然后创建数据库:create database bjpowernode;
        使用数据库:use bjpowernode
        然后初始化数据库:source D:\bjpowernode.sql

数据库设计三范式 

数据库设计三范式是数据库表设计的依据

数据库三范式共有一下三种:

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

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

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

第一范式


    最核心,最重要的范式,所有表的设计都需要满足。
    必须有主键,并且每一个字段都是原子性不可再分。

    学生编号 学生姓名 联系方式
    ------------------------------------------
    1001        张三        zs@gmail.com,1359999999
    1002        李四        ls@gmail.com,13699999999
    1001        王五        ww@163.net,13488888888


 以上不满足第一范式,第一:没有主键。第二:联系方式可以分为邮箱地址和电话,修改以后像下面这样。
    
    学生编号(pk) 学生姓名    邮箱地址            联系电话
    ----------------------------------------------------
    1001                张三        zs@gmail.com    1359999999
    1002                李四        ls@gmail.com    13699999999
    1003                王五        ww@163.net        13488888888

第二范式


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

    学生编号 学生姓名 教师编号 教师姓名
    ----------------------------------------------------
    1001            张三        001        王老师
    1002            李四        002        赵老师
    1003            王五        001        王老师
    1001            张三        002        赵老师

    这张表描述了学生和老师的关系:(1个学生可能有多个老师,1个老师有多个学生)
    这是非常典型的:多对多关系!

    分析以上的表是否满足第一范式?
        不满足第一范式。
    
    怎么满足第一范式呢?修改

    学生编号+教师编号(pk)        学生姓名  教师姓名
    ----------------------------------------------------
    1001            001                张三            王老师
    1002            002                李四            赵老师
    1003            001                王五            王老师
    1001            002                张三            赵老师

    学生编号 教师编号,两个字段联合做主键,复合主键(PK: 学生编号+教师编号)
    经过修改之后,以上的表满足了第一范式。但是满足第二范式吗?
        不满足,“张三”依赖1001,“王老师”依赖001,显然产生了部分依赖。
        产生部分依赖有什么缺点?
        数据冗余了。空间浪费了。“张三”重复了,“王老师”重复了。
    
    为了让以上的表满足第二范式,你需要这样设计:
        使用三张表来表示多对多的关系!!!!
        学生表
        学生编号(pk)        学生名字
        ------------------------------------
        1001                    张三
        1002                    李四
        1003                    王五
        
        教师表
        教师编号(pk)        教师姓名
        --------------------------------------
        001                    王老师
        002                    赵老师

        学生教师关系表
        id(pk)            学生编号(fk)            教师编号(fk)
        ------------------------------------------------------
        1                        1001                        001
        2                        1002                        002
        3                        1003                        001
        4                        1001                        002
    

    背口诀:
        多对多怎么设计?
            多对多,三张表,关系表两个外键!


第三范式


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

    学生编号(PK) 学生姓名 班级编号  班级名称
    ---------------------------------------------------------
        1001                张三        01            一年一班
        1002                李四        02            一年二班
        1003                王五        03            一年三班
        1004                赵六        03            一年三班
    
    以上表的设计是描述:班级和学生的关系。很显然是1对多关系!
    一个教室中有多个学生。

    分析以上表是否满足第一范式?
        满足第一范式,有主键。
    
    分析以上表是否满足第二范式?
        满足第二范式,因为主键不是复合主键,没有产生部分依赖。主键是单一主键。
    
    分析以上表是否满足第三范式?
        第三范式要求:不要产生传递依赖!
        一年一班依赖01,01依赖1001,产生了传递依赖。
        不符合第三范式的要求。产生了数据的冗余。
    
    那么应该怎么设计一对多呢?

        班级表:一
        班级编号(pk)                班级名称
        ----------------------------------------
        01                                一年一班
        02                                一年二班
        03                                一年三班

        学生表:多

        学生编号(PK) 学生姓名 班级编号(fk)
        -------------------------------------------
        1001                张三            01            
        1002                李四            02            
        1003                王五            03            
        1004                赵六            03        
        
        背口诀:
            一对多,两张表,多的表加外键!

 

ItVuer - 免责声明 - 关于我们 - 联系我们

本网站信息来源于互联网,如有侵权请联系:561261067@qq.com

桂ICP备16001015号