在学习数据库之前,我们回忆一下在IO流中所学习的一个序列化流和反序列化流,可以向文件中存入对象,也可以从文件中取出对象,这无疑是存储和取出数据的一种形式。
我们学习数据库,也是为了操作数据,其包括对数据的增加、删除、修改、和查询,那你就会问了,既然二者功能相同,那为什么不能直接用序列化流呢?
那肯定是因为【数据库管理系统】来操作数据更加简单啊,只需要通过简单的sql语句就能完成对数据的操作,不需要向序列化流那样,需要创建对象啊,存入文件啊,关闭流啊等等麻烦的操作。
什么是数据库?
本质上是一个文件夹,先有个概念,往下文看你就知道了。
什么是数据库管理系统(DBMS)呢?
顾名思义,管理数据库的软件,常见的有MySQL(免费,供初学者学习的),Oracle(付费,性能比MySQL好很多,一般是公司中使用的),SQL Server(大学课程中都用这个DBMS来教学)。
为了学习数据库,接下来,我们安装一个数据库管理系统MySQL。安装与配置,我都写在一篇博客里了,很详细,还有百度网盘链接,这里就不花篇幅写了
https://www.cdsy.xyz/computer/soft/database/mysql/230704/cd44498.html
上文提到几个概念DB、DBMS、SQL,来总结一下,看看他们之间的关系。
DB(DataBase):本质是文件夹,用来存储数据
DBMS(DataBase Management System):数据库管理系统,用来管理数据库,也就是对数据库中的数据进行管理
SQL(Structure Query Language):结构化查询语言,DBMS使用SQL来管理DB。SQL是高级语言,计算机不能直接读懂,因此SQL在执行前,内部会先编译,这个编译过程由DBMS完成。
数据库(文件夹)是不能直接存放数据的,需要有文件来存储,我们称数据库中的文件为表。
什么是表?看图。在数据库中,表和excel中设计的表基本一样,是二维表格。【一个表对应一个文件】
也就是说,表是数据库的基本组成单元,其数据以表格形式组织起来,可读性很强。
学习表,那么我们就要学习字段,以及对记录的操作方法。
首先我们来看【字段】。
每一个字段包括哪些属性呢?
字段名、字段类型、相关约束。
字段名就不需要解释了,根据我们的需要进行命名即可;
字段类型的话,从上表可以看出,对于姓名,“张三”和“李四”可以看成是字符串类型;对于年龄,“15”和“17”可以看成是整型。在数据库中,字符串类型用varchar表示(java中用String),整型用 int 表示(和java一样)。
相关约束就是,给字段加一些条件,该字段对应的数据,必须满足这些条件,数据才有效,否则会报错。比如姓名的约束是 not null ,那么当姓名为空的时候,就会报错。
在我们认识了DB、DBMS、表这三个概念之后,还有一个没讲,就是SQL。作为操作数据的语言,SQL自然是很重要的知识点。这里,我们先看看它的分类。
对于其分类,先有个大概的认识。
DDL(Data Definition Language):数据库定义语言。主要关键字:create drop alter,对表的结构的增删改
DML(Data Manipulation Language):数据库操纵语言。主要关键字:insert delete update,对表的数据的增删改
DQL(Data Query Language)【最重点】:数据库查询语言。主要关键字:select,包含select的sql语句都是查询语句。
DCL(Data Control Language):数据库控制语言。主要关键字:grant授权、revoke撤销权限
TCL(Transaction Control Language):事务控制语言。主要关键字:commit提交事务,rollback回滚事务
这里的关键字,先知道就行,后面学习就会了。不过,关于五个分类,DDL、DML、DQL、DCL、TCL以及它们的作用,还是要记忆一下先。
学习完前面一些概念之后,我们先导入一些数据,方便我们后面的练习。
导入初始化数据,有以下几个步骤:
1)打开cmd窗口,登陆我们的MySQL
2)创建一个数据库,来存放我们的练习数据(语句看不懂没关系,先跟着敲)
- create database if not exists bjpowernode;
-
使用数据库
- use bjpowernode;
-
3)导入我们的数据:新建一个文件,命名为 bjpowernode.sql ,打开,将下面的代码复制粘贴进去。然后回到我们的 cmd 窗口,输入以下命令:source 路径\bjpowernode.sql;(注意,这里的“路径”中不能有中文)。这样,我们就把数据导入bjpowernode这个数据库中了。
【注意:下面的代码直接复制粘贴就行,经过后面的学习,就能够看懂了】
- DROP TABLE IF EXISTS EMP;
- DROP TABLE IF EXISTS DEPT;
- DROP TABLE IF EXISTS SALGRADE;
-
- CREATE TABLE DEPT
- (DEPTNO INT(2) NOT NULL ,
- DNAME VARCHAR(14) ,
- LOC VARCHAR(13),
- PRIMARY KEY (DEPTNO)
- );
- CREATE TABLE EMP
- (EMPNO INT(4) NOT NULL ,
- ENAME VARCHAR(10),
- JOB VARCHAR(9),
- MGR INT(4),
- HIREDATE DATE DEFAULT NULL,
- SAL DOUBLE(7,2),
- COMM DOUBLE(7,2),
- PRIMARY KEY (EMPNO),
- DEPTNO INT(2)
- )
- ;
-
- CREATE TABLE SALGRADE
- ( GRADE INT,
- LOSAL INT,
- HISAL INT );
-
-
-
-
- INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
- 1, '财务部', '北京');
- INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
- 2, '研发部', '上海');
- INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
- 3, '销售部', '深圳');
- INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (
- 4, '管理层', '广州');
- COMMIT;
-
- INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
- DEPTNO ) VALUES (
- 1000, '张三', '文员', 1003, '1980-12-17'
- , 800, NULL, 2);
- INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
- DEPTNO ) VALUES (
- 1001, '李四', '销售员', 1005, '1981-02-20'
- , 1600, 300, 3);
- INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
- DEPTNO ) VALUES (
- 1002, '王五', '销售员', 1005, '1981-02-22'
- , 1250, 500, 3);
- INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
- DEPTNO ) VALUES (
- 1003, '赵六', '经理', 1008, '1981-04-02'
- , 2975, NULL, 2);
- INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
- DEPTNO ) VALUES (
- 1004, '刘备', '销售员', 1005, '1981-09-28'
- , 1250, 1400, 3);
- INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
- DEPTNO ) VALUES (
- 1005, '关羽', '经理', 1008, '1981-05-01'
- , 2850, NULL, 3);
- INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
- DEPTNO ) VALUES (
- 1006, '张飞', '经理', 1008, '1981-06-09'
- , 2450, NULL, 1);
- INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
- DEPTNO ) VALUES (
- 1007, '皮卡丘', '研发人员', 1003, '1987-04-19'
- , 3000, NULL, 2);
- INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
- DEPTNO ) VALUES (
- 1008, '小火龙', '董事长', NULL, '1981-11-17'
- , 5000, NULL, 4);
- INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
- DEPTNO ) VALUES (
- 1009, '妙蛙草', '销售员', 1005, '1981-09-08'
- , 1500, 0, 3);
- INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
- DEPTNO ) VALUES (
- 1010, '杰尼龟', '文员', 1006, '1987-05-23'
- , 1100, NULL, 2);
- INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
- DEPTNO ) VALUES (
- 1011, '钢铁侠', '文员', 1005, '1981-12-03'
- , 950, NULL, 3);
- INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
- DEPTNO ) VALUES (
- 1012, '蜘蛛侠', '研发人员', 1003, '1981-12-03'
- , 3000, NULL, 2);
- INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
- DEPTNO ) VALUES (
- 1013, '永强', '文员', 1006, '1982-01-23'
- , 1300, NULL, 1);
- COMMIT;
-
- INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
- 1, 700, 1200);
- INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
- 2, 1201, 1400);
- INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
- 3, 1401, 2000);
- INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
- 4, 2001, 3000);
- INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (
- 5, 3001, 9999);
- COMMIT;
-
-
4)输入 show tables; 发现我们的bjpowernode这个数据库中多了几个表
【关于bjpowernode.sql这个文件】
在上面的操作中,我们已经向数据库导入了数据,且是通过bjpowernode.sql这个文件进行导入的。
那么,什么是sql脚本呢?
(以sql结尾的文件,我称为“sql脚本”。这种文件中,编写了大量的sql语句,通过source命令执行sql脚本,就是将这个脚本中的sql语句一一执行。)
在操作表中的数据之前,先查一下表的结构,是有必要的。
我们先看看,bjpowernode这个数据库中,有哪些表
查看dept表的表结构
- 这里我先介绍一下,让大家能看懂上面的表
-
- Field是字段名称,Type是字段类型,Null、Key、Default、Extra这些是字段约束。
- 字段类型中,int(2)、varvhar(14),后面的数字,表示宽度,但是,int和varchar二者的宽度,含义还不一样。
- 对于int(2),不是说你的数字大小只能是0~99,而是说,当你给字段deptno添加UNSIGNED ZEROFILL这个约束
- 时,如果你的值是1,那么会自动帮你补齐为 01。
- 对于varchar(14),是说在你的字符串中,无论是字母还是符号还是中文,都不能超过14和字符,一旦超过14个,
- 就会报错。
- 字段约束中,null空约束,yes时表示该字段可以为空,no时反之;key是主键约束;default是默认约束,也就是
- 对于该字段的默认值,这些约束在后面会细讲。
-
这里,我们把各个表的字段都介绍一下,顺便了解一下各个字段的含义,方便后面的理解和练习
dept表(部门表):deptno(部门编号)、dname(部门名称)、loc(部门位置)
emp表(员工表):empno(员工编号)、ename(员工姓名)、job(工作岗位)、mgr(上级领导编号)、hiredate(入职日期)、sal(月薪)、comm(津贴)、deptno(部门编号)
salgrade表(薪资等级表):grade(等级)、losal(最低薪资)、hisal(最高薪资)
接下来,查一下我们的表都有哪些数据,命令复制粘贴就行,后面会学
MySQL中有一些常用的命令,但是不难,一下子就记住了
- select database(); -- 查看当前正在使用的数据库
-
- select version(); -- 查看当前MySQL的版本
-
- show create table emp; -- 查看emp这个表的创建语句
-
show create table emp; 这句执行之后,效果如下:
画红框的部分,就是我们创建emp这个表的时候,所需要的sql语句
在开始学习查询之前,我们先了解查询顺序,你可以看不懂,但是在后面学习完它们的含义之后,这个执行顺序你要记得住:
【from、join、on、where、group by、having、select、distinct、order by、limit】
【我的记忆方法】从加上哪组,有选重排限(英语直译取第一个汉字)。十个字,没什么逻辑,但是记住之后,写sql查询语句相当简单,你不用每一次都跑回来,看看这些执行顺序,方法很蠢但有用!
在前面的sql分类中,我们学到过一种,叫做DQL,这就是查询语句。其语法格式很简单,如下:【这是最简单的查询方式,后面会越来越难,先记住这个格式】
- select 字段名1,字段名2,字段名3... from 表名
-
大家可以尝试一下下面的代码,根据我的解释和你实际看到的结果,就很容易理解
- select * from emp; -- 查询所有的员工记录
- select ename,sal from emp; -- 查询员工姓名即对应的薪资
- select ename,sal*2 from emp; -- 在第二句的基础上,查出的结果是原薪资的两倍
- select ename 员工姓名,sal*2 薪资 from emp; -- 在第三局的基础上,给字段起别名
-
有几个注意点,新手比较容易错
1)sql语句是不区分大小写的
2)语句要以 ; 结尾
3)字符串是用单引号 ’ ’ 括起来的
什么是条件查询?
我们在简单查询之后,有一些数据是我们想要的,有些是我们不想要的,那么我们就需要给查询语句加一些条件进行筛选,这就是条件查询。
条件查询的语法格式如下:
- select
- 字段...
- from
- 表
- where
- 条件;
-
既然是条件,那么我们当然需要一些运算符,比如满足 A=B 这个条件时,就把记录查出来, ‘=’ 就是一个运算符。常见的运算符如下:
运算符 | 说明 |
---|---|
= ,!=,>,<,>=,<= | 等于,不等于,大于,小于,大于或等于,小于或等于 |
between…and… | 两个值之间 |
is null | 某个字段对应的数据为空 |
and | 并且 |
or | 或者 |
in | 包含,注意 num in (2,4) 表示的不是将 num 在 2 到 4 这个范围内的记录查出来,而是说 num=2 或者 num=4 时将记录查出来 |
not | 非,常和 is 、 in 连用,即 is not null(不为空)、not in(不在) |
like | 有 like 的查询语句称为模糊查询 |
这里举几个例子,还是老规矩,大家执行sql语句,看看效果,很容易懂的
- select * from emp;
- select * from emp where empno = 1000; -- 这里可以看到,只查询了 empno = 1000 时这条记录
- select * from emp where sal > 1000;
- select * from emp where sal between 1000 and 2000; -- 将薪资在1000到2000范围内的记录查出来
- select * from emp where mgr is null; -- 结果是将董事长查出来了,因为懂事长已经是最高职位,没有上司,为null
- select * from emp where empno = 1000 and empno = 1001;
- select * from emp where empno in (1000,1001,1002);
-
重点讲一下模糊查询,模糊查询一般是根据字符串来对记录进行筛选。其支持两个符号,下划线 _ 和百分号 % 。下划线表示必须是某个字符且不能为空,% 表示任意多个字符且能为空。
sql语句如下:
- select * from emp where job like "%售%"; -- 显然,可以将所有销售员查询出来
- select * from emp where job like "_售_"; -- 查询结果和上一句一样,因为 % 虽然能表示任意多个字符,但是 销售员 只有三个字,% 只指代了一个字符
-
- select * from emp where ename like '%张%'; -- 查出两条记录,因为 % 能表示空
- select * from emp where ename like '_张%'; -- 查出0条记录,因为 _ 必须是具体的字符,不能为空
-
关于上面的几个运算符,还有一些注意点:
1)between…and…,是左闭右闭,及表示范围的这两个数也是包括在内的
2)null 不是一个值,要用 is null 或者 is not null 判断,不能使用等号。比如 where mrg = null 来查询董事长,是错误的。
3)and 的优先级大于 or,所以当 and 和 or 同时出现时,最好加上括号区分优先级
4)模糊查询中_ 和 % 既然有特定的含义,那如果我确实需要 _ 这个符号来查询呢?答案是在 _ 前面加一个 \,这一点和 java 中是一样的。
我们都知道,在我们每一次考完试之后,老师都会根据分数对班里的人进行排序。因为不排序的话,对于杂乱无章的成绩,很难看出谁是第一,谁是第二等等。
在 mysql 中,我们也可以将查询到的结果进行排序,让查询结果更加清晰。
执行以下 sql 语句:
- select ename,sal from emp order by sal asc; -- 根据薪资降序查询
- select ename,sal from emp order by sal desc; -- 根据薪资升序查询
-
回到生活中,当老师发现小明和小红的语文成绩都是90,语数英三科总分也相等,那么怎么排序呢,老师一般会再次比较数学的成绩,进行排序。
- select ename,sal from emp order by sal asc,comm asc; -- 如果两个人的薪资相等,那么久根据津贴comm进行排序
-
【练习题】找出所有销售员,并根据薪资降序排序
- select * from emp where job = '销售员' order by sal desc;
-
在一个班级里,我们不是所有人都挤在一起,老师一般都会为我们分组,考试后通过比较各组的平均分,来判断哪一组成绩比较差,才能更有针对性地对该组进行辅导。
同样的,我们对于一个表,我们可以用 group by 将表分成多个组,然后比较各个组的平均值等。比如,在 emp 员工表中,我们可以根据不同的岗位,对员工进行分组,然后根据薪资的平均值,了解各个岗位的薪资情况。
在了解分组查询之前,我们先了解一下分组函数。
分组函数:sum()、max()、min()、avg()、count()。分组函数又称为多行处理函数,这是因为它是对多行数据进行操作。还有一种单行处理函数,都会进行介绍。
- select sum(sal) from emp; -- 查出薪资的总值
- select max(sal) from emp; -- 查出薪资的最大值
- select min(sal) from emp; -- 查出薪资的最小值
- select avg(sal) from emp; -- 查出薪资的平均值
-
- select count(*) from emp; -- 查出总记录数(14条)
- SELECT COUNT(sal) FROM emp; -- 查出14条记录
- SELECT COUNT(comm) FROM emp; -- 查出4条记录
-
对上述sql的总结:
1)对于上述五种分组函数,我们发现,查询结果都是单行。这是因为,我们还没有对表 emp 进行分组。下面学习完分组,就有多行了。
2)对于 count() 这个分组函数,count() 是用来统计记录条数的,* 表示所有的记录条数,如果是字段,则统计其非空的情况下的记录条数。如 count(sal) 查出了14条记录,但 count(comm) 查出的只有4条,这是因为,comm这个字段只有4条记录中有数据,其他的都为null。
学习完多行处理函数之后,我们再来看一下单行处理函数。较为常用的是:
我们来执行以下的sql:
- -- 假设我们要查出员工表中,员工名以及他们的年薪((每月薪资+每月津贴)*12)
- select ename,(sal+comm)*12 年薪 from emp; -- 查出来发现,年薪有的人变成了null。
-
这是因为,在单行中,有员工是没有津贴的,及津贴是null,所以 sal+null 的结果还是null。【我们可以用单行处理函数 ifnull() 来解决。】sql语句如下:
- select ename,(sal+ifnull(comm,0))*12 年薪 from emp;
-
单行处理函数 ifnull() 的格式是,ifnull(字段,数值),ifnull(comm,0) 对应的意思就是,如果津贴是 null 的话,就把它转换成数字 0 来和 sal 做相加运算。
好了,接下来学习一下分组排序吧。
【由于分组函数经常与group by(分组)连用,因此被称为分组函数】
- select job,avg(sal) from emp group by job; -- 根据职业进行分组,并查出各个职业的平均薪资。
-
就是这么简单,经过上面的sql语句,我们将表通过岗位进行了分组,并且通过分组函数求出了各个岗位对应的平均薪资。
此时,在查出各个岗位的平均薪资的时候,如果我们再加一个条件,需要查出平均薪资大于2999的岗位。显然,大于2999,需要进行条件查询,那么我们试一下下面的sql语句
- select job,avg(sal) from emp group by job where avg(sal) > 2999;
-
发现报错了!这是什么原因呢?回顾我们在开始学查询时,我让大家记住一个查询顺序。【from、join、on、where、group by、having、select、distinct、order、limit】,显然,group by 是在where后面的,而分组函数 avg(sal) 又是在group by 后面执行的,也就是说,由于where是在分组函数之前执行,因此 where avg(sal) > 2999 是错误的,说白了,就是你 avg(sal) 都还不存在,那怎么执行where。就是这个道理。
小总结:这里很重要,分组函数不能写在 where 后面,这是错误的!
为此,我们引入了【另一个关键字 having ,其作用和 where 相同】,都是条件查询。不同点就是 where 在 group by 之前执行,而 having 在 group by 之后执行,也就是分组之后,通过 having 对数据进行再次过滤。
- -- 查出平均薪资大于2999的岗位
- select job,avg(sal) from emp group by job having avg(sal) > 2999;
-
【练习题】找出薪资高于平均薪资的所有员工
- select ename,sal from emp where sal > avg(sal);
-
前面已经说到,由于各关键字的查询顺序,where 和 avg() 这些分组函数不能连用,那该怎么解决这道练习题呢?我们用到【子查询】。
【这里先做个伏笔,子查询后面会学习。这道题只是再次提醒你,where 和 分组函数不能连用】
- -- 分两步查询
- select avg(sal) from emp; -- 查出所有员工的平均薪资
- select ename,sal from emp where sal > 2073.214286; -- 根据上条sql的查询结果,找出薪资高于平均薪资的员工
-
- -- 一步查询
- select ename,sal from emp where sal > (select avg(sal) from emp); -- 这个就是子查询。后面会学。
-
关于分组查询另外三个知识点:
1)多字段问题
我们来看看这个sql语句
- select ename,job,avg(sal) from emp group by job;
-
这个sql语句执行错误,你想想,我们要的是根据岗位进行分组,并显示岗位对应的平均工资,和单个员工ename有啥关系,你写个ename在这里,mysql究竟该显示谁?显然是不合理的。
有个规定:【当一条语句中有group by的话,select后面只能跟分组函数和参与分组的字段】
2)多次分组
在前面的学习中,我们知道 order by 可以在后面加两个字段,在第一个字段相等时,再根据第二个字段进行排序。
类似的,如果我们要求:找出每个部门不同工作岗位的最高薪资。显然,我们需要线根据部门分组一次,再根据岗位再分组一次。
- select deptno,job,max(sal) from emp group by deptno,job;
-
3)having和where的选择
首先,在某些情况下,having 和 where 都是可以使用的。
【练习题】找出最高薪资大于2900的部门
- select deptno,max(sal) from emp group by deptno having max(sal) > 2900;
-
- select deptno,max(sal) from emp where sal > 2900 group by deptno;
-
以上两种方式查出来的结果是一样的。但是having的效率比where低,原因:第二个sql语句中,先将薪资小于2900的员工过滤掉,这样参与分组的员工就少了,效率就高了。
我们先执行一下下面的sql语句
- select deptno from emp;
-
我们发现,数据冗余的太多了,很多都是重复的。那么我们该怎么去掉这些重复的内容呢?很简单
- select distinct deptno from emp;
-
如上,只需要在字段前面加上 distinct 即可。
我们这种情况只有一个字段,那么多个字段时,会是怎样呢?
- select distinct deptno,job from emp;
-
和我们想的不一样,它并不是只对 deptno 这个字段起效果。而是对 deptno、job 这两个字段同时起效果,它要求 deptno 和 job 两个字段对应的值不能够同时重复。
【练习题】统计岗位的数量
- select count(distinct job) from emp;
-
关于连接查询,已经有一篇很详细的文章了,快车直达:https://www.cdsy.xyz/computer/soft/database/mysql/230705/cd44505.html
【练习题】找出每一个员工的部门名称以及工资等级
- 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;
-
【练习题】找出每一个员工的部门名称、工资等级、以及上级领导
- SELECT
- e1.ename '员工',d.dname,s.grade,e2.ename '领导'
- FROM
- emp e1
- LEFT JOIN
- emp e2
- ON
- e1.mgr = e2.empno
- JOIN
- dept d
- ON
- e1.deptno = d.deptno
- JOIN
- salgrade s
- ON
- e1.sal BETWEEN s.losal AND s.hisal;
-
首先我们要知道,什么是子查询?
select语句A中,可以嵌套另一个select语句B,则select语句B称为子查询。
子查询可以出现在什么地方?
- select
- ...(select)
- from
- ...(select)
- where
- ...(select);
-
举例
在where中使用子查询:找出高于平均薪资的员工信息
- select * from emp where sal > (select avg(sal) from emp);
-
在from后面嵌套子查询:找出每个部门平均薪资的薪资等级
- SELECT deptno,AVG(sal) FROM emp GROUP BY deptno; -- 先查出每个部门的平均薪资,将结果作为一个表,和salgrade表连接
-
- 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;
-
在select后面嵌套子查询:找出每个员工所在的部门名称,要求显示员工名和部门名
- select
- e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname
- from
- emp e;
-
所谓联表查询,就是使用 union 关键字,将查询结果集相加。这个过程中,会自动将重复的记录删除。
比如,我们要找出工作岗位是 销售员 和 经理 的员工。
- -- 方式一
- select ename,job from emp where job in ('销售员','经理');
-
- --方式二
- select ename,job from emp where job = '销售员'
- union
- select ename,job from emp where job = '经理';
-
【注意】:
联表查询和连接查询都是多表查询,联表查询是纵向查询,连接查询是横向查询,如何理解呢?
纵向查询,先查出岗位为销售员的所有员工作为表 A ,再查出岗位为经理的所有员工作为表 B ,union 是将 B 从 A 的下方拼凑上去,合成更多条记录,因此称为纵向查询。
横向查询,从上面的学习中我们知道,当我们所需要的字段在不同的表上时,我们就要连接多个表,然后将各个字段拼凑起来,当然,是左右关系的拼凑,因此称为横向查询。
limit 分页查询(是非常重要的知识点,不难,但是很重要!)
在实际查询中,我们查询到的数据可能会很多很多,我们不可能一次性全部查出来,显示在我们的浏览器上。比如,我们百度一下某个问题,假设没有分页的话,那我们要下拉时,不知道要拉多久才能到底。
limit 语法格式:limit startIndex,length。
startIndex是起始位置,从0开始,0是第一条记录。
length表示查多少条记录作为一页。
再次回到我们的查询顺序,可以看到,limit是最后处理的一个关键字。
sql语句如下:
- select * from emp limit 0,5;
- select * from emp limit 5,5;
-
现在,我们来总结一下一个规律:
第一页:0,3
第二页:3,3
第三页:6,3
第四页:9,3
后面的 3 是不变的,不信你百度一下后,去数一数一页有多少条记录,都是固定的。前面的记录下标就一直变化,规律是:【(页码-1)x 每页条数 】
写成java代码
- int pageNo = 1; //页码
- int pageSize = 10; //每页多少条记录
-
- limit (pageNo-1)*pageSize,pageSize
-
在前面的学习中,我们一直使用的数据,是通过 sql 脚本导入的,我们发现,我们都会查询语句了,但是我们自己还不会创建表。现在,就来学习学习吧。
创建表的格式如下:
- create table 表名{
- 字段1 数据类型 约束,
- 字段2 数据类型 约束,
- ....
- };
-
先看看数据类型吧:数据类型有整型、浮点型、日期型、字符串型,常见的有如下
整型
类型 | 字节 | 最小值 | 最大值 |
---|---|---|---|
tinyint | 1 | -128 | 127 |
-128 | 127 | ||
smallint | 2 | -32768 | 32767 |
0 | 65535 | ||
mediumint | 3 | -8388608 | 8388607 |
0 | 16777215 | ||
int | 4 | -2147483648 | 2147483647 |
0 | 4294967295 | ||
bigint | 8 | -9223372036854775808 | 92233720368547758087 |
0 | 18446744073709551615 |
浮点型
类型 | 字节 | 最小值 | 最大值 |
---|---|---|---|
float | 4 | 1.4x10^(-45) | 3.4x10^38 |
double | 8 | 4.9x10^(-324) | 1.7x10^308 |
日期型
类型 | 说明 | 标准格式 | 字节 |
---|---|---|---|
date | 日期 | YYYY-MM-DD | 3 |
time | 时间 | HH:MM:SS | 3 |
datetime | 日期时间 | YYYY-MM-DD HH:MM:SS | 8 |
timestamp | 时间戳,从1970年1月1日0时起到现在的毫秒数 | 4 | |
year | 年份 | YYYY | 1 |
字符串型
类型 | 说明 | 范围 |
---|---|---|
char | 定长度字符串 | char(M),0<=M<=255,也就是最多255个字符。对于char(3),如果你存储 ‘abcde’,就会报错。 |
varchar | 变长度字符串 | varchar(M),0<=M<=65535(约2W-6W个字符,受字符集影响) |
text | 文本串 | 约2W-6W个字符,受字符集影响 |
【思考】char和varchar怎么选择?
char分配的长度是固定长度,会直接分配空间,不需要和varchar一样先判断字符串长度,因此char的效率比varchar高。在实际的开发中,一些不需要很多字符、且长度固定的字段,就选用char,比如性别等;一些长度不固定的字段,就选用varchar,比如简介等。
现在,我们开始建表吧。
注意:表名以 t_ 或者 tbl_ 开头。你就会问了,我们除了创建表,还能创建其他的吗?为什么要加 t_ 或 tbl_ 来区分?有的,后面还会学索引、视图。
【练习题】创建一个 t_student表,要求如下
- 学生信息包括:学号、姓名、性别、班级编号、生日
- 学号:bigint
- 姓名:varchar
- 性别:char
- 班级编号:int
- 生日:char
-
- create table t_student(
- no bigint,
- name varchar(255),
- sex char(1),
- classno int,
- birth char(10)
- );
-
执行以上sql,我们的 t_student 表就创建好了
创建表之后,我们如果要删除表,可执行以下sql
- drop table if exists t_student;
-
【表的复制】:表的复制,是将一个查询结果,作为一张新表,sql语句如下
- create table emp1 as select ename from emp; -- 将emp的查询结果作为新表emp1
-
- select * from emp1; -- 检查我们的emp1表是否创建成功
-
- show create table emp1; -- 查看emp1的创建过程,发现和手动创建的没啥区别
-
在前面的学习中,我们发现,从九到十七,都是在将查询语句,可见查询的重要性。
现在,我们来学插入语句。
插入语句非常简单,语法格式如下:
insert into 表名(字段1,字段2…) values(值1,值2…);
插入语句有两种形式,一种是分开插入,一种是一次性插入多个记录。
- -- 方式一
- insert into t_student(no,name,sex,classno,birth) values(1,'张三','男',1000,'7月1日');
- insert into t_student(no,name,sex,classno,birth) values(2,'李四','男',1001,'7月20日');
-
- -- 方式二
- insert into t_student(no,name,sex,classno,birth) values(1,'张三','男',1000,'7月1日'),(2,'李四','男',1001,'7月20日');
-
【注意】:
1)前面的字段必须和后面的值匹配得上。
如果有的字段没写出来,则会自动视为null,如下
- insert into t_student(name) values('王五');
-
2)前面的字段可以不写,但这就要求后面的值要写全
- insert into t_student values(4,'赵六','男',1003,'9月9日');
-
【插入批量记录】:和表的复制类似,其实我们也可以将一个查询结果,插入到表中,sql语句如下:
- create table emp2 as select ename from emp where ename = '张三';
- select * from emp2;
- insert into emp2 select ename from emp where ename != '张三';
- select * from emp2;
- -- 当然,这要求查询结果和原来表的列数是一样的
-
学习完插入数据后,我们再来学修改数据,修改数据也很简单。
语法格式:update 表名 set 字段名1=值1,字段名2=值2… where 条件;
注意:如果没有条件的话,整张表的对应字段都会更新
【练习题】将部门 1 的所在地址修改成兰州,将部门名称修改为人事部
- UPDATE dept SET loc='兰州',dname='人事部' WHERE deptno = 1;
-
删除数据也一样,很简单
语法格式:delete from 表名 where 条件;
- delete from dept where deptno = 1; -- 删除编号为1的部门
-
如何删除所有记录?
- delete from emp1; -- 删除所有记录
- truncate table emp1; -- 删除所有记录,只剩下表头
- drop table if exists emp1; -- 整张表删除
-
两种删除所有记录有什么不同?
delete效率很低,删除所有记录,没有释放其物理地址,其物理地址还在,也就是只是在地址上将内容抹去。我们可以想象成,在一个用铅笔在纸上填写的表格中,用橡皮檫把内容擦去。这样做的好处,是数据在删除之后,后悔了,还能再回来。
truncate效率高,表被截断,不可回滚,永久丢失。可以想象成纸上的表格,用剪刀将表头以外的其他行都剪掉,我们就不能重新用铅笔写了。
修改表结构的sql语句其实有很多种,比如删除字段,修改字段名,新增字段等等。但是,我们不学!原因如下:
1)因为我们可以在 sqlyog、navicat等MySQL的可视化工具中进行修改。
2)表一旦创建,就很少会出现修改表结构的情况(除非在前期设计表的时候),你想想看,假如一张表中已经有了几千万条记录,此时你修改某个字段,那这些记录对应该字段的值该怎么办,难道要全部修改吗?
3)在java代码中,不会出现修改表结构的语句。出现在java代码中的,一般只有【CRUD】(Create增、Retrieve检索、Update修改、Delete删除,也就是我们在前面学的DML和DQL)操作。
在前面刚刚接触字段的时候,我们就介绍了字段的三个要素,字段名,字段类型,字段约束。那么,什么是约束呢?
在创建表的时候,可以给表的字段添加一些约束,添加约束的目的是为了保证数据的合法性、有效性等。
【常见约束】:
约束 | 说明 |
---|---|
not null | 非空约束。添加 not null 约束的字段,其值不能是 null |
unique | 唯一约束。添加 unique 约束的字段,每条记录对应该字段的值不能重复 |
primary key | 主键约束。是非空约束和唯一约束的结合,其值不能为 null 且不能重复。简称为 PK |
foreign key | 外键约束。和主键约束一样,其值不能为 null 且不能重复,但是其还有另一种用处,后面会学。简称为FK |
default | 默认值约束。可以设置字段的默认值,如果在insert插入记录时,其没有设值,则使用默认值 |
现在,我们来单独讲讲各个约束。
非空约束
- create table t_temp(
- name varchar(255) not null,
- age int(11)
- );
- insert into t_temp(age) values(17); -- 报错,因为name不能为 null ,而这个新增语句没有给name赋值
- drop table if exists t_temp;
-
唯一约束:注意,null 可以不唯一
- create table t_temp(
- name varchar(255) unique
- );
- insert into t_temp(name) values('张三'); -- 顺利插入数据
- insert into t_temp(name) values('张三'); -- 报错,因为'张三'已经存在了,不满足唯一性
- drop table if exists t_temp;
-
- -- 注意,如果是两个写在一起,则是联合之后具有唯一性,如下
- create table t_temp(
- name varchar(255),
- age int(11),
- unique(name,age)
- );
- insert into t_temp(name,age) values('张三',15);
- insert into t_temp(name,age) values('张三',16); -- 不会报错
- insert into t_temp(name,age) values('张三',15); -- 报错
- drop table if exists t_temp;
-
主键约束【重要】:
1)添加了主键约束的字段称为主键字段,主键字段其对应的主键值是这行记录在整张表中的唯一标识,符合表的【设计三范式】(后面讲)。
2)主键约束和唯一约束一样,也可采用复合形式,也就是多个字段联合起来作为主键。但是我们一般不这样做,因为这样做违背了【设计三范式】。
3)一张表中,只能有一个字段作为主键,且只能有一个主键。
先看看sql语句吧
- create table t_temp(
- id int,
- name varchar(255),
- primary key(id)
- );
- insert into t_temp(name) values('张三'); -- 报错,因为 int 是主键,不能没有值,不能为 null
- insert into t_temp(id,name) values(1,'张三'); -- 不会报错
- insert into t_temp(id,name) values(1,'李四'); -- 报错,因为主键具有唯一性
- drop table if exists t_temp;
-
主键值的自增:我们的主键,为了满足其非空和唯一两个条件,我们一般会将其设置为自增。
- create table t_temp(
- id int primary key auto_increment, -- 从1开始,以1递增
- name varchar(255),
- );
- insert into t_temp(name) values('张三');
- insert into t_temp(name) values('李四');
- select * from t_temp; -- 发现 id 已经自动帮我们写好了
- drop table if exists t_temp;
-
【注意】:我们一般不会使用业务主键,而是采用自然主键。什么意思呢?
我们的主键,必须要能代表一条记录,且不能修改,且不能和其他的字段有任何的关系,不要和业务挂钩。假如我们用银行卡号作为主键,当我们更换银行卡时,我们让银行帮我们改卡号,完了,银行说不能改,在底层银行卡号是主键,主键不能修改,改了就可能和别人重复了。
所以我们采用自然主键,我们可以自定义一个字段 id int,然后设 id 为主键。即主键最好是和业务无关的自然数。
外键约束:
在学习外键约束之前,让我们先来看一下下面的sql语句和图片:
【下面的代码直接复制就行了】
- CREATE TABLE t_student(
- id INT PRIMARY KEY AUTO_INCREMENT,
- NAME VARCHAR(255) NOT NULL,
- classno INT NOT NULL,
- classname VARCHAR(255) NOT NULL
- );
- INSERT INTO t_student(NAME,classno,classname) VALUES
- ('张三',100,'阳光小学北京路校区四年级(1)班'),
- ('李四',100,'阳光小学北京路校区四年级(1)班'),
- ('王五',100,'阳光小学北京路校区四年级(1)班'),
- ('赵六',101,'阳光小学北京路校区四年级(2)班'),
- ('孙七',101,'阳光小学北京路校区四年级(2)班');
-
- SELECT * FROM t_student;
- drop table if exists t_student;
-
我们发现,在classname这个字段中,重复的数据太多了。也许你不会觉得多,这是因为这里只有几个人,假设1班和2班,每一个同学的信息就是一条记录,那重复的,冗余的就多了吧?如何解决呢?我们需要用到外键约束。
我们将t_student这个表,分成两个表,再用外键将两个表联系起来,就不会出现冗余了。
【下面的代码直接复制就行了】
- CREATE TABLE t_class(
- cno INT,
- cname VARCHAR(255),
- PRIMARY KEY(cno)
- );
- CREATE TABLE t_student(
- sno INT,
- sname VARCHAR(255),
- classno INT,
- FOREIGN KEY(classno) REFERENCES t_class(cno) -- classno称为外键,关联了t_class这个表的cno字段
- );
- INSERT INTO t_class(cno,cname) VALUES
- (100,'阳光小学北京路校区四年级(1)班'),(101,'阳光小学北京路校区四年级(2)班');
- INSERT INTO t_student(sno,sname,classno) VALUES
- (1,'张三',100),(2,'李四',100),(3,'王五',100),(4,'赵六',101),(5,'孙七',101);
-
- SELECT * FROM t_class;
- SELECT * FROM t_student;
-
- INSERT INTO t_student(sno,sname,classno) VALUES(6,'周八',103); -- 报错,因为classno是外键,其值不能是t_class的cno字段的值中所没有的
-
【注意】:
1)外键的值,可以是 null
2)外键所关联的表的字段,其不一定需要时主键,但是要求其必须是唯一性。你想想,假设其不具有唯一性,那外键关联过去后,它就懵逼了,它不知道关联哪个。
关于外键约束,先学到这里就好了。后面还会学。
在上面的sql语句中,在创建表时,我们发现,唯一约束和主键约束,有两种写法,一种是直接接在字段后面,一种是在所有字段都写完之后,再写约束。第一种其实称为 列级约束,第二种称为 表级约束。
关于存储引擎,这部分知识只是作为一个了解。如果想要深入了解的话,可以看看其他博主的博客。不过我感觉,如果不是想成为DBA的话,其实也没有必要去深入了解。
首先,我们要知道,什么是存储引擎?
就是表的存储方式。比如一样东西,我们把它放到箱子里,我们可以横着放,也可以竖着放,这就是存储方式的不同。【注意存储引擎是MySQL特有的,Oracle中也有类似的机制,但它只是叫做存储方式,没有和MySQL一样,整一些花里胡哨的名字。】
什么时候使用存储引擎?
其实是在建表的时候指定的。来看看我们之前对于dept表的建表sql语句
- CREATE TABLE DEPT(
- DEPTNO INT(2) NOT NULL ,
- DNAME VARCHAR(14) ,
- LOC VARCHAR(13),
- PRIMARY KEY (DEPTNO)
- );
-
其实我们这样写是,是不完整的,我们使用查询建表语句来看一下,键入 show create table dept;
- CREATE TABLE `dept` (
- `DEPTNO` INT(2) NOT NULL,
- `DNAME` VARCHAR(14) DEFAULT NULL,
- `LOC` VARCHAR(13) DEFAULT NULL,
- PRIMARY KEY (`DEPTNO`)
- ) ENGINE=INNODB DEFAULT CHARSET=utf8;
-
且不考虑字段的约束问题,我们来对比一下,两个建表语句有什么不同:
1)首先是发现了 dept、deptno、dname 等标识符,都加了飘号 ` ` ,可能在一些教程中,会要求我们给标识符加上飘号,以和其他的关键字等进行区分,其实不加才更好。 因为这是MySQL特有的,放到Oracle中就崩了,假如你写了上万条sql语句,然后突然要求你使用Oracle这个DBMS,想想你要修改多久呀。
2)发现了后面一句 engine=innodb default charset=utf8; engine就是引擎的意思,charset是字符集的意思。我们建表的时候,没有进行制定;但是在查建表语句的时候,却给我们指定了innodb和utf8,说明innodb是默认的存储引擎,utf8是默认的字符集。
MySQL中,存储引擎有很多种,每一种都有自己的优缺点,需要根据需求进行选择。
我们可以通过 SHOW ENGINES; 这个语句查询MySQL所支持的存储引擎。以下内容可以不看了,知道存储引擎这个东西就行了。
【常见的存储引擎:】(InnoDB和MyISAM这里就不写了,因为视频讲的真的一般,了解一个引擎,又牵扯出了一大堆新的概念,最好是有高质量的文章帮助我们理解,所以你想深入了解存储引擎,就去搜索查看本站其他的文章吧)
MyISAM:
从上面的图,Teacnsaction – NO 我们可以知道这个存储引擎不支持事务(事务在下一部分学习)。
【纠错】:在前面的学习中,我曾说表就是文件,一张表就对应一个文件,那是为了让大家更容易理解。其实一张表,是可以对应多个文件的。
当我们使用 MyISAM 存储引擎创建表的时候,会生成3个文件来对应该表。如下图。.frm(format)是格式文件,存储的是该表的表结构(字段名、字段类型、字段约束这些).MYD(MyISAM data)是数据文件,存储表的数据(记录)。MYI(MyISAM Index)是索引文件,存储表的索引(索引相当于书本的目录,有了目录,我们可以很快找到我们想要看的内容。后面会学习。)。
【事务是重中之重】
一、事务简介
1)什么是事务?
一个事务,就是一个完整的业务逻辑单元,不可再分。
说人话!比如我要在银行进行转账,那我要经过哪些步骤呢?我把钱转过去了,那我这边的钱就变少了,到账的用户那边的前就变多了,是不是就需要两条update修改语句。
假如我不使用事务,我把钱转过去,我这边钱变少了,就在这变少的瞬间,系统崩了,那完了,到账的那边的钱还没有增多,那我岂不是亏了很多钱?所以,我们需要使用事务,事务会将我们的多个sql语句绑定成为一个整体。只有确定了所有的sql语句都是正确的之后,才会提交给DBMS,让它修改数据。如果一个sql语句出现了差错,那么这个事务不会被提交,所有的数据都不会发生变化。
2)事务有什么用?
从(1)问中我们就能够知道。事务是为了保证数据的完整性和安全性。
3)与事务相关的语句有哪些?
只有DML语句,即insert、delete、update这些会让数据发生变化的语句。
4)关于事务这里,我们要知道以下几个单词:
transaction:事务,commit:提交,rollback:回滚
举个例子:假设一件事,需要经过insert、update、delete,然后才能完成。此时我们需要使用事务。
- 开启事务。(开始)
-
- 执行insert。(不会修改数据库的数据,会被记录到缓存中)
- 执行update。(不会修改数据库的数据,会被记录到缓存中)
- 执行delete。(不会修改数据库的数据,会被记录到缓存中)
-
- 提交事务或者回滚事务。(结束) 无论是提交事务还是回滚事务,都是事务结束的标志。
-
-
- 提交事务:根据缓存中的记录,最终修改数据库的数据,然后清空缓存中的记录。
- 回滚事务:不会修改数据库的数据,直接清空缓存中的记录。
-
这是用中文表述的大概意思,接下来,我们来演示事务,用sql语句来演示,更加直观。
二、演示事务
先创建好表
- USE bjpowernode;
- CREATE TABLE t_user(
- id INT PRIMARY KEY AUTO_INCREMENT,
- username VARCHAR(255)
- );
- insert into t_user(username) value('zhangsan');
-
在之前的学习中,当我们执行insert、delete、update这三种DML语句的时候,每执行一句,我们的表中数据就会改变一次。这是因为,MySQL它是默认自动提交事务的,也就是执行一句,就会直接修改数据库的数据。
现在,我们来模拟一下事务的回滚,看下面的sql
- start transaction; -- 这里开始,往下的所有sql,如果没有出现回滚或者提交,都是事务的一部分
-
- insert into t_user(username) value('lisi'); -- 插入一条记录
- update t_user set username = 'wangwu' where username = 'zhangsan';
- select * from t_user; -- 查询,发现lisi已经存在,且zhangsan被修改,我们看到的数据,现在只是存在缓存
- -- 中(或者说内存,我们关闭MySQL后重新打开,是查不到这个数据的),事实上还没有持久化(也就是存到硬盘中)。
- rollback; -- 回滚
- select * from t_user; -- 继续查询,记录不见了。说明因为回滚,将内存中的执行记录清空了。
-
然后我们模拟一下事务的提交,看下面的sql
- start transaction;
- insert into t_user(username) value('lisi');
- update t_user set username = 'wangwu' where username = 'zhangsan';
- select * from t_user;
- commit; -- 回滚
- select * from t_user; -- 继续查询,lisi存在,且zhangsan。说明数据已经存到硬盘中。即已经持久化。
-
总结:所以我们要使用事务,就是先start transaction,然后写各种DML,最后commit或者rollback。
三、事务的四大特性 ACID
在对事务有一定的了解之后,我们来学习一下事务有哪些特性。
这四个,有时候会面试。
A:原子性:事务是最小的工作单元,不可再分。
C:一致性:事务必须保证多条DML语句同时成功或者同时失败
I:隔离性:事务A和事务B之间有隔离,不能互相干扰
D:持久性:事务的最终数据必须持久化(存储到硬盘)
四、事务的隔离性
事务的隔离性这个特性中,有一个概念,叫做隔离级别。我们会根据事务对安全性的要求,来选择不同的隔离级别。就好像我们根据不同程度的台风,政府部门会采取不同的应急措施一样。
有的隔离级别中,存在一些不利于数据安全的现象,级别越低,现象越糟糕;但是,隔离级别越高,效率就会越低。所以对于隔离级别的选择,只能到实际开发中去决定。【这个不要求初级java程序员会选择】
隔离级别包含以下四种:读未提交(第一级别),读已提交(第二级别),可重复度(第三级别),序列化读(第四级别)。每一种隔离级别都是为了解决上一级隔离级别中存在的不好现象。【注意:这四种隔离级别中的“读”字,意思都是指查询。】
在MySQL中,是第三级别起步;在Oracle中,是第二级别起步。也就是现在的DBMS中,基本上已经没有第一级别起步的了。接下来,我们来详细介绍一下这四个隔离级别。然后通过演示,来加深理解和印象。
读未提交(read uncommited):这个隔离级别中,存在“脏读”现象。“脏读”就是事务A还没有执行完,还没有提交,其要访问或修改的数据就被事务B给读到了,事务B读到了“脏”的数据,所以称为“脏读”。
读已提交(read commited):这个隔离级别中,解决了“脏读”现象,存在“不可重复读”现象。“不可重复读”就是假设事务A还没提交,事务B读取数据 1 ,然后事务A提交,事务A的提交导致了数据 1 变成了 2,还没完成提交的事务B再次读取数据,发现两次读到的数据不一样。第一次是 1,第二次是 2,也就是前后两个数据不重复,所以称为“不可重复读”。
可重复读(repeatable read):这个隔离级别中,解决了“不可重复读”现象,存在“幻读”现象。“幻读”就是事务A还没提交,事务B读取初始数据 1,事务A提交后,修改了数据 1 使之变成了数据 2,此时事务B仍未提交,再次读取数据,得到的结果是仍然是 1。硬盘中数据实际上已经被事务A修改成了 2,但是事务B读到的仍然是 1,就像读到了虚幻的数据一样,所以称为“幻读”。
序列化读(serializable):这个隔离级别中,解决了“幻读”现象,存在效率低的现象。序列化读就是让事务A和事务B排队执行,事务A没有执行完,你事务B就不要执行。这就解决了前三个隔离级别的问题,但是由于事务排队执行,因此效率极其低下。
以上就是四种隔离级别的介绍,怎样,已经说的很通俗易懂了吧。现在,我们来演示一下各个隔离级别。
我们需要先打开两个DOS命令窗口,开启MySQL,使用bjpowernode数据库,修改隔离级别,然后查看隔离级别确认一下,接着就可以开始我们的演示了。
- 修改隔离级别:set global transaction isolation level 隔离级别;
- 查询隔离级别:select @@tx_isolation; 不同MySQL版本,有的是:select @@transaction_isolation;
-
具体如下图:
读未提交:
读已提交:
可重复读:
序列化读:
在学习索引之前,我们通过几个问题,自问自答,来了解索引
1)什么是索引?
通俗地讲,索引相当于书本的目录,有了目录我门就能很快定位到我们想要看的内容。如果没有目录,我们就只能从头开始一页一页地翻书,知道找到我们想要的内容。
2)索引有什么用?
超大地提高了检索记录(emmmm,检索的意思自行百度)的效率。
3)为什么有了索引检索效率就会高?
因为索引缩小了扫描的范围。我们想要查询某条记录,有两种方式:1)全表扫描;2)根据索引在小范围内扫描。
4)索引可以乱用吗?
不可以,虽然加了索引之后检索效率提高了,但是索引需要维护,成本高。就比如,《新华字典》改版之后,其中的汉字页码就会变,那前面目录也要发生变化。同样的,表中数据一旦修改,索引需要重新排序,进行维护。
5)什么时候需要给字段添加索引?数据量大(一本书假如只有10页,那还要什么目录);该字段很少有DML操作(也就是该字段对应的数据不会经常发生改变)。该字段经常出现在where后面。(where后面是条件,经常出现在where后面说明是经常需要根据该字段对应的值,来查询到该值所在的记录),看下面
- 添加索引是给某一个字段,或者说某些字段添加索引
- select * from t_user where username = '张三';
- 当username字段上没有添加索引的时候,以上sql语句会进行全表扫描,扫描username字段中所有的值。
- 当username字段上添加索引的时候,以上sql语句会根据索引进行扫描,快速定位
-
【注意】:主键和unique约束自带索引
6)了解了索引之后,我们该怎么为我们的字段创建索引呢?
- 创建索引:create index 索引名 on 表名(字段名);
- 删除索引:drop index 索引名 on 表名;
-
举例:
【索引的底层原理】:跳过吧,这个我也不会,如果有比较好的,能把底层原理讲透,讲通俗化的博客,麻烦告知我一下,十分感谢。
视图这一节的话,和前面索引的关系不大。索引是为了提高检索效率,视图则是为了数据的保密性。
1)什么是视图?
视图就是将一个DQL语句的查询结果作为一张“虚拟表”,可以通过这张虚拟表来 增删改 原表的数据
2)怎么创建视图?怎么删除视图?
- 创建视图:create view 视图名 as select 字段名 from 表名;
- 删除视图:drop view 视图名;
-
3)视图有什么作用?
你可能会疑惑,直接操作原表不可以吗,为什么还要创建一个视图来修改?其实有些表的保密性是比较高的,假如一张表来存储用户信息,其中包括了身份证、银行卡号等重要信息。当银行需要某公司来接手维护他们的用户管理系统时,为了这些用户的隐私信息不被泄漏,就创建视图,把隐私信息去除,留下一些不重要的信息作为一张虚拟表就行。
4)演示视图
- -- 为了不修改emp表,我们先复制emp表
- create table emp1 as select * from emp;
- -- 根据emp1表来创建一个视图
- create view myview as select empno,ename,sal from emp1;
- -- 通过视图修改原表数据
- delete from myview where empno = 1000;
-
- 在windows的dos命令窗口中执行:
-
- 将数据库中的数据导出:
- mysqldump bjpowernode>E:\bjpowernode.sql -uroot -proot; -- 导出整个数据库
- mysqldump bjpowernode emp>E:\bjpowernode.sql -uroot -proot; -- 导出表
-
- 导入数据:
- source E:\bjpowernode.sql
-
mysqldump是导出命令,bjpowernode是数据库,> 表示导出到哪个位置
什么是设计三范式?
设计三范式是一种规范,让我们在设计表的时候,能够避免数据的冗余,减小空间开销
- 第一范式:任何一张表都应该有主键,并且每一个字段原子性不可再分
- 第二范式:在第一范式基础上,所有非主键的字段完全依赖主键,不能产生部分依赖
- 第三范式:在第二范式基础上,所有非主键字段直接依赖主键,不能产生传递依赖
-
接下来,我们来了解各个范式。
第一范式:
我们来看下面这一张表,显然不符合第一范式.首先这张表没有主键;其次,联系方式不具有原子性,因为还可以分为邮箱和手机号。
修改如下,即可满足第一范式
第二范式:
下面这张表,不满足第二范式。原因是主键是复合主键,即由学生编号和教师编号这两个字段构成一个主键,这就产生了部分依赖。
它们共同构成主键,但是学生姓名却只依赖于学生编号,不依赖于教师编号;同样的,教师姓名只依赖于教师编号,不依赖于学生编号。这就是部分依赖。
修改如下,即可满足第二范式。
对于上面三个表,你可能会有点懵逼,你先记住一下口诀:
【多对多,三张表,关系表两个外键】:如何理解呢?为满足第二范式,我们将表拆成三个表,第三个表是关系表。多对多是指字段的关系,多个学生对应多个老师,或者反过来说,多个老师对应多个学生也行。记住了这个口诀,我们以后在设计表的时候,就不需要多加思索。
第三范式:
下面这张表,不满足第三范式。原因是存在传递依赖。班级名称依赖于班级编号,而班级编号依赖于学生编号(因为班级编号属于学生的一个信息),这就是传递依赖。
修改如下,即可满足第三范式:
【一对多,两张表,多的表加外键】:同样是一个口诀,一个班级对应多个学生,这就是一对多的关系,此时需要给多的表(学生表)加外键,关联到班级表。
- 以上就是三个范式,很好理解,主要记住三范式是为了减少数据冗余。
- 另外,在实际开发中,我们不一定会使用三范式来设计表,因为多表查询存在笛卡尔积,查询效率较慢,所以有时候
- 会通过牺牲空间(数据大量冗余),来保证查询效率高。
-
在这些练习题中,每一道题都会有不同的解法。
(8)取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高领导人的姓名
(15)列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
(19)列出所有 “销售员” 的姓名及其部门名称, 部门的人数
(20)列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数
(21)列出在部门"销售部"工作的员工的姓名, 假定不知道销售部的部门编号
(22)列出薪金高于公司平均薪金的所有员工, 所在部门, 上级领导, 雇员的工资等级
(24)列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金
(25)列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金. 部门名称
(26)列出在每个部门工作的员工数量, 平均工资和平均服务期限
(33)求出部门名称中, 带’财’字符的部门员工的工资合计、部门人数
1)取得每个部门最高薪水的人
我们先查出部门及对应最高薪水,将其作为一张表,和emp表连接查询
- SELECT
- e1.ename,t.*
- FROM
- emp e1
- JOIN
- (SELECT
- d.dname,MAX(sal) maxsal
- FROM
- emp e2
- JOIN
- dept d
- ON
- e2.deptno = d.deptno
- GROUP BY
- e2.deptno) t
- ON
- e1.sal = t.maxsal;
-
2)哪些人的薪水在部门的平均薪水之上或等于平均薪水的
与题1类似,先将各部门的平均薪水查出来,作为一张表 t ,再和 emp 表连接
- SELECT
- e.ename,t.*,e.sal
- FROM
- emp e
- JOIN
- (SELECT
- d.deptno,d.dname,AVG(e.sal) avgsal
- FROM
- emp e
- JOIN
- dept d
- ON
- e.deptno = d.deptno
- GROUP BY
- e.deptno) t
- ON
- e.sal >= t.avgsal AND e.deptno = t.deptno;
-
-
3)取得部门中(所有人的)平均的薪水等级
这道题如果你用了前两道题类似的做法,先查出所有人的薪水等级,然后作为一张表,与dept表连接查询的话,那你就中计了,和我一样。我刚开始也是这么写的,如下:
- SELECT
- d.dname,AVG(t.grade) avggrade
- FROM
- dept d
- JOIN
- (SELECT
- e.deptno,e.ename,s.grade
- FROM
- emp e
- JOIN
- salgrade s
- ON
- e.sal BETWEEN s.losal AND s.hisal) t
- ON
- d.deptno = t.deptno
- GROUP BY
- t.deptno;
-
这道题不需要这么麻烦。
- -- 大家先复制上面这一段,看看得到了什么表
- 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;
-
- -- 再连接dept就行
- SELECT
- d.dname,AVG(s.grade) avggrede
- FROM
- emp e
- JOIN
- salgrade s
- ON
- e.sal BETWEEN s.losal AND s.hisal
- JOIN
- dept d
- ON
- e.deptno = d.deptno
- GROUP BY
- e.deptno;
-
4)不准用分组函数(max),取得最高薪水
使用 limit 和 排序
- select ename,sal 最高薪水 from emp order by sal desc limit 0,1;
-
5)取得平均薪水最高的部门的部门编号(两种解决方案)
- -- 方式一
- SELECT deptno FROM emp GROUP BY deptno ORDER BY AVG(sal) DESC LIMIT 0,1;
-
- -- 方式二
- SELECT AVG(sal) avgsal FROM emp GROUP BY deptno; -- 第一步:查出各部门的平均工资
- SELECT MAX(t.avgsal) FROM (SELECT AVG(sal) avgsal FROM emp GROUP BY deptno) t; -- 第二步:将第一步中的结果作为一张表,查出最大的那个平均值
- SELECT deptno FROM emp GROUP BY deptno HAVING AVG(sal) = (SELECT MAX(t.avgsal) FROM
- (SELECT AVG(sal) avgsal FROM emp GROUP BY deptno) t); -- 第三步:根据deptno分组查询emp表,将工资
- -- 平均值等于第二步中查出的最大平均值的那个部门编号查出来
-
6)取得平均薪水最高的部门的部门名称
这道简单,我们先 SELECT AVG(sal) FROM emp e GROUP BY e.deptno ORDER BY AVG(sal) DESC LIMIT 0,1; 得出最大的部门工资平均值,然后去连接 dept 表得到部门名称,稍作修改即可。
注意这道题和第三题一样,没有必要将结果作为一张新表,直接连接dept表即可。
- SELECT
- d.dname
- FROM
- emp e
- JOIN
- dept d
- ON
- e.deptno = d.deptno
- GROUP BY
- e.deptno
- ORDER BY
- AVG(sal) DESC
- LIMIT 0,1;
-
7)求平均薪水的等级最低的部门的部门名称
这道太难,建议跳过
- -- 先找出每个部门的平均薪水
- select deptno,avg(sal) as avgsal from emp group by deptno;
- -- 找出每个部门的平均薪水的等级 (1)
- SELECT
- t.*,s.grade
- FROM
- (SELECT e.deptno,AVG(sal) avgsal FROM emp e GROUP BY e.deptno) t
- JOIN
- salgrade s
- ON
- t.avgsal BETWEEN s.losal AND s.hisal;
- -- 找出每个部门的平均薪水中的最低的等级 (2)
- select grade from salgrade where (select avg(sal) as avgsal from emp group by deptno order by
- avgsal asc limit 1) between losal and hisal;
- -- (1) 和 (2) 联立,再稍作修改
- SELECT
- t.dname,s.grade
- FROM
- (SELECT d.dname,e.deptno,AVG(sal) avgsal FROM emp e JOIN dept d ON e.deptno = d.deptno GROUP BY e.deptno) t
- JOIN
- salgrade s
- ON
- t.avgsal BETWEEN s.losal AND s.hisal
- WHERE
- grade = (SELECT grade FROM salgrade WHERE (SELECT AVG(sal) AS avgsal FROM emp GROUP BY deptno ORDER BY avgsal ASC LIMIT 1) BETWEEN losal AND hisal);
-
-
8)取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高领导人的姓名
- -- 第一步:找出不重复的mgr
- select distinct mgr from emp;
- -- 第二步:找出普通员工中的最高工资
- -- 注意不重复的mgr多了个条件,is not null,这是因为如果 not in 后面的小括号中如果有null,
- -- 那么结果也会是null,大家可以去掉看看效果
- SELECT MAX(sal) FROM emp WHERE empno NOT IN (SELECT DISTINCT mgr
- FROM emp WHERE mgr IS NOT NULL);
- -- 第三步:找出所有领导及对应工资
- SELECT ename,sal FROM emp WHERE empno IN (SELECT DISTINCT mgr
- FROM emp WHERE mgr IS NOT NULL);
- -- 第四步:将第二步和第三步联立
- SELECT ename,sal FROM emp WHERE empno IN
- (SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL)
- HAVING sal > (SELECT MAX(sal) FROM emp WHERE empno NOT IN
- (SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL));
-
-
9)取得薪水最高的前五名员工
这道,送分题
- select ename,sal from emp order by sal desc limit 0,5;
-
10)取得薪水最高的第六到第十名员工
- select enaem,sal from emp order by sal desc limit 5,5;
-
11)取得最后入职的5名员工
- SELECT * FROM emp ORDER BY hiredate DESC LIMIT 0,5;
-
12)取得每个薪水等级有多少员工
- -- 方法一
- SELECT
- t.grade,COUNT(t.grade)
- FROM
- (SELECT e.ename,s.grade FROM emp e JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal) t
- GROUP BY
- t.grade;
-
- -- 方法二
- select
- s.grade,count(*)
- from
- emp e
- join
- salgrade s
- on
- e.sal between s.losal and s.hisal
- group by
- s.grade;
-
13)面试题【这道题不做,等到jdbc中讲】
有3个表s(学生表),c(课程表),sc(学生选课表)
s(sno,sname)代表(学号,姓名)
c(cno,cname,cteacher)代表(课号,课名,教师)
sc(sno,cno,scgrade)代表(学号,课号,成绩)
问题:
1)找出没选过“黎明”老师的所有学生姓名
2)列出2门以上(含2门)不及格学生姓名及平均成绩
3)即学过1号课程又学过2号课程的所有学生的姓名
请用标准 SQL 语言写出答案,方言也行(请说明是使用什么方言)
-
-
14)列出所有员工及领导的姓名
这道题注意左连接就好了,因为懂事长是没有领导的,需要左连接,否则会显示不了董事长这条记录。这一点在连接中都有讲到。
- SELECT
- e1.ename AS 员工,e2.ename AS 领导
- FROM
- emp e1
- LEFT JOIN
- emp e2
- ON
- e1.mgr = e2.empno;
-
15)列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
- SELECT
- e1.ename 员工,e1.hiredate 员工入职日期,e2.ename 领导,e2.hiredate 领导入职日期
- FROM
- emp e1
- JOIN
- dept d
- ON
- e1.deptno = d.deptno
- LEFT JOIN
- emp e2
- ON
- e1.mgr = e2.empno
- WHERE
- e1.hiredate < e2.hiredate;
-
-
16)列出部门名称和这些部门的员工信息
- SELECT
- d.dname,e.ename
- FROM
- dept d
- JOIN
- emp e
- ON
- d.deptno = e.deptno
- ORDER BY
- dname;
-
17)列出至少有 5 个员工的所有部门
- SELECT
- d.dname
- FROM
- dept d
- JOIN
- emp e
- ON
- d.deptno = e.deptno
- GROUP BY
- d.dname
- HAVING
- COUNT(*) >= 5;
-
18)列出薪金比"张飞" 多的所有员工信息
- SELECT
- *
- FROM
- emp
- WHERE
- sal > (SELECT sal FROM emp WHERE ename = '张飞');
-
19)列出所有 “销售员” 的姓名及其部门名称, 部门的人数
由于我的数据和视频有些不同,查询结果不同。这道题如果以下的sql看不懂的话,建议看视频:点我看视频
- SELECT
- e.ename,e.job,d.dname,t.*
- FROM
- emp e
- JOIN
- dept d
- ON
- e.deptno = d.deptno
- JOIN
- (SELECT
- COUNT(*) 销售部人数
- FROM
- emp e
- JOIN
- dept d
- ON
- e.deptno = d.deptno
- WHERE
- d.dname = '销售部') t
- WHERE
- e.job = '销售员';
-
20)列出最低薪金大于 1500 的各种工作及从事此工作的全部雇员人数
- SELECT
- job,COUNT(*)
- FROM
- emp
- GROUP BY
- job
- HAVING
- MIN(sal) > 1500;
-
21)列出在部门"销售部"工作的员工的姓名, 假定不知道销售部的部门编号
- SELECT ename FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE dname = '销售部');
-
22)列出薪金高于公司平均薪金的所有员工, 所在部门, 上级领导, 雇员的工资等级
这道题简单,一步一步写就好了。但是是一道好题,综合性较强。
- -- 公司平均薪金
- SELECT AVG(sal) FROM emp
-
- -- 高于平均薪金的所有员工
- SELECT
- e.ename
- FROM
- emp e
- WHERE
- e.sal > (SELECT AVG(sal) FROM emp);
-
- --所在部门(内连接),上级领导(自连接+左连接),工资等级(内连接)。结果如下
- SELECT
- e1.ename,d.dname,e2.ename,s.grade
- FROM
- emp e1
- JOIN
- dept d
- ON
- e1.deptno = d.deptno
- LEFT JOIN
- emp e2
- ON
- e1.mgr = e2.empno
- JOIN
- salgrade s
- ON
- e1.sal BETWEEN s.losal AND s.hisal
- WHERE
- e1.sal > (SELECT AVG(sal) FROM emp);
-
23)列出与"张三" 从事相同工作的所有员工及部门名称
- SELECT
- e.ename,d.dname
- FROM
- emp e
- JOIN
- dept d
- ON
- e.deptno = d.deptno
- WHERE
- job = (SELECT job FROM emp WHERE ename = '张三') and e.ename != '张三';
-
24)列出薪金等于部门 30 中员工的薪金的其他员工的姓名和薪金
这道题读起来有点拗口,就是说。除部门3之外的员工,如果有薪金等于部门3中某个员工薪金的,就把他的姓名和薪金查出来。
- SELECT
- ename,sal
- FROM
- emp
- WHERE
- sal IN (SELECT DISTINCT sal FROM emp WHERE deptno = 3) AND deptno != 3;
-
25)列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金. 部门名称
- SELECT
- e.ename,e.sal
- FROM
- emp e
- JOIN
- dept d
- ON
- e.deptno = d.deptno
- WHERE
- sal > (SELECT MAX(sal) FROM emp WHERE deptno = 3);
-
26)列出在每个部门工作的员工数量, 平均工资和平均服务期限
这道题重点是掌握如何计算两个日期的“年差”,差了多少年。
函数及格式:timestampdiff(间隔类型,前一个日期,后一个日期)
- -- 前面这句不难,主要是掌握后面的平均服务期限。就是 当前日期-入职日期
- SELECT
- e.deptno,d.dname 部门,d.loc,COUNT(*) 员工数量,AVG(sal) 平均工资
- FROM
- emp e
- JOIN
- dept d
- ON
- e.deptno = d.deptno
- GROUP BY
- e.deptno;
-
- -- 平均服务年限
- select timestampdiff(year,hiredate,now()) 服务年限 from emp;
-
- -- 接下来就简单了
- SELECT
- e.deptno,d.dname 部门,d.loc,COUNT(*) 员工数量,AVG(sal) 平均工资,AVG(TIMESTAMPDIFF(YEAR,hiredate,NOW())) 平均服务年限
- FROM
- emp e
- JOIN
- dept d
- ON
- e.deptno = d.deptno
- GROUP BY
- e.deptno;
-
-
27)列出所有员工的姓名、部门名称和工资
送分题
- select
- e.ename,d.dname,e.sal
- from
- emp e
- join
- dept d
- on
- e.deptno = d.deptno;
-
28)列出所有部门的详细信息和人数
- SELECT
- d.deptno,d.dname,d.loc,COUNT(e.ename)
- FROM
- dept d
- JOIN
- emp e
- ON
- d.deptno = e.deptno
- GROUP BY
- d.deptno;
-
29)列出各种工作的最低工资及从事此工作的雇员姓名
- SELECT
- e.ename,t.*
- FROM
- emp e
- JOIN
- (SELECT job,MIN(sal) minsal FROM emp GROUP BY job) t
- ON
- e.sal = t.minsal and e.job = t.job;
-
30)列出各个部门的领导的最低薪金
各个部门的领导。就把经理算进来就好了。理论上懂事长不属于某个部门。
- select
- deptno,min(sal)
- from
- emp
- where
- job = '经理'
- group by
- deptno;
-
31)列出所有员工的 年工资, 按 年薪从低到高排序
年工资包括津贴,注意使用 ifnull() 函数
- SELECT
- ename,(sal+IFNULL(comm,0))*12 年薪
- FROM
- emp
- ORDER BY
- 年薪 ASC;
-
32)求出员工领导的薪水超过3000的员工名称与领导
- SELECT
- a.ename 员工,b.ename 领导
- FROM
- emp a
- JOIN
- emp b
- ON
- a.mgr = b.empno
- WHERE
- b.sal > 3000;
-
33)求出部门名称中, 带’财’字符的部门员工的工资合计、部门人数
模糊查询 用like
- SELECT
- t.dname,SUM(sal),COUNT(*)
- FROM
- emp e
- JOIN
- (SELECT deptno,dname FROM dept WHERE dname LIKE '%财%') t
- ON
- e.deptno = t.deptno
- GROUP BY
- e.deptno;
-
34)给任职日期超过 30 年的员工加薪 10%.
- UPDATE emp SET sal = sal*1.1 WHERE TIMESTAMPDIFF(YEAR,hiredate,NOW()) > 30;
-