sql、DB、DBMS分别是什么

DB:

​ DataBase(数据库，数据库实际上在硬盘上以了文件的形式存在)

DBMS

​ DataBase Management System (数据库管理系统)

SQL:

​ 结构化查询语言，是门标准通用的语言，标准的 sql 适用于所有的数据库产品。SQL 语句在执行的时候，实际上内部也会先进行编译，这个编译由 DBMS 完成

DBMS 负责执行 sql 语句，通过执行 sqL 语句来操作 DB 中的数据。

什么是表？

表：table

表：table 是数据库的基本组成的单元，所有的数据都以了表的形式组织，目的是可读性强。

一个表包括行和列。

​ 行：被称为数据/记录（data）

​ 列：被称为字段（column ）

SQL 语句的分类

DQL(数据查询语言) :，凡是 select 语句都是 DQL

DML（data manipulation language）数据操纵语言：: In sert delete updata 对表中的的数据进行增删改。

DDL（data definition language）数据库定义语言：: creat drop alter ，对表结构的增删改

DTL(事务控制语言) ：transaction commit rollback

DCL（Data Control Language）数据库控制语言： grant revoke 撤销权限

关于Mysql语句

开启服务

net start mysql
1

关闭服务

net stop mysql 
1

cd /D D:\Program Files (x86)\mysql-5.7.19-winx64\bin
1

登录

  mysql -uroot -p输入密码
1

显示数据库

show databases
1

显示表

show tables
1

创建库

create (这里为你要创建的库名)
1

切换库

use （你要切换的库名）
1

引用脚本

source 文件路径
1

删除数据库：

drop database 数据库的名字
1

查看表结构

desc 表名
1

查看目前用的是哪个数据库

select database();
1

查看 mysql 版本

select version();
1

查看建表语句

查询语句

sql 语句以 ; 结尾.

sql 语句不区分大小写

select 字段名1,.... form 表名
1

查询员工的年薪(字段可以参与数学运算)

select ename,sal*12 from emp;
1

+--------+----------+
| ename  | sal*12   |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 19200.00 |
| WARD   | 15000.00 |
| JONES  | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
| TURNER | 18000.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| FORD   | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

给查询结果的列重合名 as 可以省略

select ename,sal*12 as yearsal form emp;
+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |  9600.00 |
| ALLEN  | 19200.00 |
| WARD   | 15000.00 |
| JONES  | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| SCOTT  | 36000.00 |
| KING   | 60000.00 |
| TURNER | 18000.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| FORD   | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

注意标准sql 语句中要求字符串使用单引号

查询所有字段

select * from emp; 实际开发中不使用 * 号效率较低

条件查询

select 字段 from 表名 where 条件;
1

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

 select ename from emp where sal=5000;
1

找出工资在1100和3000之间的员工

select ename,sal from emp where sal>=1100 and sal<=3000;
select ename,sal from emp where sal between 1100 and 3000;
//between and 中间的是闭区间 使用 between 的必须保证左小右大
1
2
3

在数据库中 NULL 代表一个值代表什么也没有为空,不能用等号衡量,必须使用 is null 或者 is not null

select ename,sal,deptno from emp where sal>1000 and (deptno=20 or deptno=30);
运算优先级不确定的时候使用小括号
1
2

or(1200,1300,1400);

表示多个or

模糊查询 like

% 代表任意多个字符,_ 代表任意一个字符

找出名字当中含有o 的

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

找出第二个字母是A的

 select ename,sal from emp where ename like'_A%';
1

按照工资的升序进行排序

select ename ,sal from emp order by sal; 升序
select ename ,sal from emp order by sal asc; 升序 asend vi/vt 上升
select ename ,sal from emp order by sal desc; 降序 descend vi/vt 下降

1
2
3
4

按照工资的降序排列,当工资相同的时候再按照名字的升序排序

select ename,sal from emp order by sal desc,ename asc;
1

找出工作岗位是 SALESMAN 的员工,并且要求按照薪资的降序排序

 select ename,job,sal from emp where job='SALESMAN' order by sal desc;
1

执行顺序

select 
	字段 5
from 
	tablename 1
where 
	条件 2
group by 
	... 3
having
	... 4
order by
	...  6
limit
	...7
1
2
3
4
5
6
7
8
9
10
11
12
13
14

分组函数

分组函数自动忽略 NULL

sql 语句中有一个语法规则 分组函数无法直接出现在 where子句当中.

因为 group by 在 where 后执行 所以 分组函数无法在 where 后执行

count 计数
sum 求和
avg 平均值
max 最大值
min 最小值
1
2
3
4
5

找出工资总和

select sum(sal) from emp;
1

最高工资

select min(sal) from emp;
1

最低工资

select min(sal) from emp;
1

平均工资

select avg(sal) from emp;
1

找出总人数

select count(sal) from emp;
14
1
2

找出比平均工资大的人

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

分组函数自动忽略 NULL

select count(comm) from emp;
4
1
2

所有的数据库都是这样规定的,只要有NULL 出现在数学工式中这个结果必为NULL

ifnull() 空处理函数

ifnull (可能为 NULL 的数据,被当做什么处理);
1

计算年薪,算上补贴

 select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;
1

count(*) 和 count (具体的某个字段),他们有什么区别

count(*) 不是统计某个字段中数据的个数,而是统计总条数

count(comm) 表示统计 comm 字段中不为NULL 的元素;

group by 和having

group by :按照某个字段或者某些字段进行分组

having : having 是对分组之后的数据进行再次过渡

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

select max(sal) from 
1

注意:分组函数一般都会和 group by 联合使用,这也是为什么它被称为分组函数的原因,并且任何一个分组函数都是在 group by 这前执行.当一条语句没有 group by 的话,整个表自成一组

当一条 sql 语句有 group by 的话,select 中的语句要不就是分组函数,或者分组字段 不然得到的数据是没有意义的,在其他的数据库直接通不过编译

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

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

找出每个部门的最高薪资,要求显示薪资大于 2500的

 select max(sal),deptno 
 from emp 
 group by deptno
 having max(sal)>2900//  这种效率非常低,因为执行了两次 ma
 
 //这种更好因为 where 先执行 剩下的数据再分组
 select max(sal),deptno from emp where sal >2900 group by deptno;
1
2
3
4
5
6
7

显示每个部门的平均薪资,要求显示薪资大于2000的数据

select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
1

关于结果集合去重

select distinct job from emp;
* 注意distinct 只能出现在所有的sql 语句的最前方
* distinct 出现在最前方表明后面所有的字段联合去重
1
2
3

统计岗位的数量

 select count (distinct job) as jobno form emp;
1

连接查询

一般一个业务会放在多张表,如果放一张表可能会造成数据的冗余.

根据表的连接类型分为

内连接

​ 等值连接

​ 非等值连接

​ 自连接

外连接

​ 左连接

​ 右连接

全连接(少用,不讲)

笛卡尔乘积现象

在表的连接中有一种现象被我们称为笛卡尔乘积现象,如果两张表的的连接没有任何限制两张表的记录将是两张表的乘积.

如何避免这种现象,当然是加条件过滤,避免了笛卡尔乘积现象会减少比较的次数吗?

不会比较的次数依然是两个表的乘积,只不过显示的是有效的记录.

显示每个人的以及所在的部门的名字

 select 
 	e.ename,d.dname 
 from 
 	emp e,dept d 
 where 
 	e.deptno= d.deptno 
 order by 
 	dname;
1
2
3
4
5
6
7
8

内连接

等值连接

最大的特点是等值关系

将员工的姓名与其部门的名字查找出来

select 
	e.ename,d.dname 
from 
	emp e 
join 
	dept d 
on 
	e.deptno = d.deptno;
1
2
3
4
5
6
7
8

非等值连接

最大特点非等值关系

将员工的薪水与其对应的等级显示

 select e.ename,e.sal,s.grade 
 from emp e 
 join salgrade s 
 on e.sal between s.losal and s.hisal;
1
2
3
4

自连接

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

找出每个员工的上级领导,要求显示员工与对应领导的姓名

select 
	a.ename as'员工名',b.ename as '领导名' 
from 
	emp a 
join
	emp b 
on 
	a.mgr = b.empno;
1
2
3
4
5
6
7
8

外连接

什么是外连接,与内连接的区别?

• 内连接:

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

• 外连接

  • 假设 A 和 B 表进行连接,使用外连接的话,AB 两张表中一张是主表一张是副表,主要查询主表的数据,捎带关查询副表,当副表中的数据没有和主表中的数据匹配上,副表自动模拟出 NULL 与之匹配.

  左连接:表示左边的这张表是主表.

  右连接:表示右边的这张表是主表.

内连接

select 
	a.ename as'员工名',b.ename as '领导名' 
from 
	emp a 
join
	emp b 
on 
	a.mgr = b.empno;
1
2
3
4
5
6
7
8

外连接/左连接

select 
	a.ename as'员工名',b.ename as '领导名' 
from 
	emp a 
left join
	emp b 
on 
	a.mgr = b.empno;
1
2
3
4
5
6
7
8

​ 外连接/右连接

select 
	a.ename as'员工名',b.ename as '领导名' 
from 
	emp b 
right join
	emp a 
on 
	a.mgr = b.empno;
1
2
3
4
5
6
7
8

外连接最重要的特点是:主表的数据无条件的全部查询出来,如果没有也不会丢失

案例:找出哪个部门没有员工?

 select 
 	d.* 
 from 
 	emp e 
 right join
 	dept d 
 on 
 	e.deptno = d.deptno 
 where 
 	e.empno is null;
1
2
3
4
5
6
7
8
9
10

三张表怎么连接查询

案例:找出每一个员工的部门名称以及工资等级

select 
	e.ename,s.grade,d.dname
from 
	emp e
join
	salgrade s
on 
	e.sal between losal and hisal
join
	dept d
on
	e.deptno=d.deptno;
1
2
3
4
5
6
7
8
9
10
11
12

salgrade  s
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
emp e
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
dept d
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

案例: 找出每个员工的工资等级、部门名称、上级领导。

select 
	e.ename '员工',s.grade '工资等级',d.dname '部门名称',p.ename '领导名'
from 
	emp e
join
	salgrade s
on 
	e.sal between losal and hisal
join
	dept d
on
	e.deptno=d.deptno
left join 
	emp p
on 	
	e.mgr= p.empno;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

注意解释一下

…

​ A

join

​ B

join

​ C

on

​ …

子查询

概念

select 语句中嵌套select 语句,被嵌套的 select 语句就是子查询.

from 后面嵌套子查询

案例: 找出每个部门平均薪水的薪资等级(按照部门编号分组,求sal 的平均值)

第一步:找出每个部门平均薪水

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

1
2
3
4
5
6
7

select 
	t.*,s.grade 
from 
	(
    select
        deptno,avg(sal) as avgsal 
    from 
        emp 
    group by 
        deptno
    ) as t
left join 
	salgrade s
on
	t.avgsal between s.losal and s.hisal;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

案例:找出每个部门平均的薪水等级

第一步:找出每个员工的薪水等级

 select 
 	e.ename,e.sal,s.grade,e.deptno
 from 
 	emp e 
 join 
 	salgrade s 
 on 
 	e.sal between s.losal and s.hisal;
1
2
3
4
5
6
7
8

第二步:按照部门编号分组求等级的平均值

select 
	e.deptno,avg(s.grade)
from 
	( 
     select 
 	 	e.ename,e.sal,s.grade,e.deptno
     from 
        emp e 
     join 
        salgrade s 
     on 
        e.sal between s.losal and s.hisal
    )
join 
	
group by
	e.deptno;
	
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

在select 后面嵌套子查询

案例:找出每个员工所在的部门名称,要求显示员工名与部门名称

 select 
 e.ename,
 		(
     select 
     	d.dname 
     from 
     	dept d 
     where 
     	e.deptno = d.deptno
 		)
 		as dname
 from 
 	emp e;
1
2
3
4
5
6
7
8
9
10
11
12
13

union(将查询结果集相加)

写法1.使用 in或者用or 
select 
	ename,job 
from 
	emp 
where 
	job 
in 
	('salesman','manager');
写法2,使用union
select 
	ename,job 
from 
	emp 
where 
	job ='salesman'
union 
select 
	ename,job 
from 
	emp 
where 
	job ='manager';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

使用 union 时候列数不能不同

limit(重点)

limit 是MySQL 特有的,limit 取结果集中的部分数据.

语法

limit startIndex , length
startIndex 表示取几个
length 表长度
1
2
3

案例:取出工资前五名的员工

select 
	sal,ename 
from 
	emp 
order by 
	sal desc 
limit 
	0,5;
0 可以省略,如果只写一个 5 前面默认就是0
1
2
3
4
5
6
7
8
9

案例:找出工资是第四到第九名的员工

select
	sal,ename
from
	emp
order by
	sal desc
limit
	3,6;
1
2
3
4
5
6
7
8

建表

语句语法

create table {
字段名 数据类型 约束,
...
};
1
2
3
4

关于 MySQL 数据类型

• int 整型
• Bigint 长整形
• float 浮点型
• car 定长字符串（String）
• varchar 不定字符串(stringBuilder、StringBuffer)
• date 日期类型
• BLOB 二进制大对象(存储图片、视频等流媒体信息)
• CLOB 字符大对象（存储大文本）

表名建议以：t __ 或者tab __ 开始

案例

创建学生表信息包括：Id、姓名、学号、班级、生日、性别

create table t_student (
	no bigint,
	name varchar(255),
	sex char(1),
	classno varchar(255),
	birth char(10)
);
1
2
3
4
5
6
7

插入数据

insert into 表名 （字段名1，字段名2...）values(值1，值2...)
要求字段的数量与值的数量能够匹配，并且数据类型相同
字段可以省略不写，但后面的value 必须对数量与顺序都必须正确。
insert into t_student(no,name,sex,classno,birth) values(1,'linda','1','gaokaoyiban','1990-12-12');

1
2
3
4
5

插入多行

insert into t_student values(2,'jack','1','gaoskaoyiban','1999-03-23'),(3,'rose','1','gaoskaoyiban','2000-03-23');
1

表的复制

语法

create table 表名 as sql语句
1

insert into dept1 select * from dept;
将这张表的数据插入里面
1
2

create table emp1 as select * from emp;
1

修改数据：update

语法格式

update 表名 set 字段名1=值，字段名2=值2.. where 条件
注意如果没有条件整张表全部更新
1
2

案例

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

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

删除

语法格式

delete from 表名 where 条件
1

删除表中的所有记录

delete from dept1;
1

删库跑路

删表

drop table if exists t_student;
1

删除大表（慎用无法恢复）

truncate tab emp1;
1

约束constraint

常见的约束

• 非空约束（not null）
• 唯一约束（unique）
• 主键约束（Primary key）
• 外键约束 (foreign key)
• 检查约束（check） 注意Oracle 数据库中有 check 约束，但Mysql 目前还不支持

非空约束

drop table if exists t_user;
create table t_user(
    id int,
    user varchar(255) not null,
    password varchar(255)
);
insert t_user(id,password) values(1,'123');
1
2
3
4
5
6
7

唯一约束

• 唯一约束修饰的字段具有唯一性，不能重复，但可以为 NULL 。

案例

给某一列添加 unique

drop table if exitsts t_user;
create table t_user(
	id int,
  	username varchar(255) unique
)
insert into t_user values(1,'zhangsan');
insert into t_user values(2,'zhangsan');
1
2
3
4
5
6
7

给两个列或者多个列添加 unique

两个组合在一起不重复
create table t_user(
	id int,
    uname varchar(100),
    ucode varchar(100),
    unique(ucode,uname)
);
两个都不能重复
create table t_user(
    id int,
    usercode varchar(255)unique
    ,username varchar(255)unique
);
1
2
3
4
5
6
7
8
9
10
11
12
13

主键约束

列级

create table t_user(
	id int primary key,
    user varchar(255),
    email varchar(255)
);
insert into t_user(id,username,email) values(1,'lk','3312@qq.com');
insert into t_user(id,username,email) values(1,'lk','wefsda');
1
2
3
4
5
6
7

表级

drop table if exists t_user;
create table t_user(
	id int,
    username varchar(255),
    primary key(id)
);
insert into t_user (id,username) values(1,'23');
insert into t_user (id,username) values(2,'23');
insert into t_user (id,username) values(3,'23');
insert into t_user(id,username) values(3,'23');
1
2
3
4
5
6
7
8
9
10
11

添加主键约束，这个键值就不可以为 Null 或者重复。

每一个表都必须要有一个主键，

一张表的主键约束只能有一个。

根据主键字段的字段数量来划分

单一主键（推荐）和复合主键（多个字段联合起来添加中一个主键约束）。

根据主键性质来划分：

自然主键，业务主键（主键值与系统的业务挂钩，不推荐）

外键约束

关于外键约束的相关术语

外键约束：foreign key

外键字段：添加有外键约束的字段

外键值：外键字段中的每一个值

顺序要求

删除数据的时候先删除子表，再删除父表，

添加数据的时候，先添加父表，再添加子表。

创建表的时候，先创建父表，再创建子表。

删除表的时候，先删子表，再删父表。

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,
    foreign key(classno) references t_class(cno)
);
1
2
3
4
5
6
7
8
9
10
11
12
13

外键字段可以为 NULL

check 约束

create table t23(
	id int primary key,
    'name' varchar(32),
    sex varchar(6) check (sex in ('man','woman')),
    sal double check(sal>1000 and sal < 2000)
);
1
2
3
4
5
6

存储引擎

存储引擎就是数据存储在电脑上的方式

常见的存储引擎

• MYISAM 这是最常用的存储引擎，但是这种引擎不是默认的
  • 使用三个文件来表示一个表
  • 格式文件（mytable.frm）
  • 数据文件(mytable.myd)
  • 索引文件(mytable.myi)
  • 优点可被压缩，节省存储空间，并且可以转换成可读表，提高检索效率。
  • 缺点不支持事务
• InnoDB
  • 优点支持事件，行级锁，外键等，这种引擎数据的案例得到保障。
  • 表的结构存储在xxx.frm 文件数据存储在tablespace 这样的表空间中，无法被压缩无法转换成只读。
  • InnoDB 支持级联删除和级联更新
• MEMORY
  • 缺点：不支持事件，数据容易丢失， 因为所有的数据存储在内存中，
  • 优点查询速度最快

事务

什么是事务

事务是一个完整的业务逻辑，不可再分，比如银行转账，从一个账户转出来，转到另一个账户上，需要执行两条 DMl 语句，两条必须同时成功，或者同时失败。

和事务相关的语句只有 DML 语句（insert updata delete）

因为这三个语句都是和事务相关的，事务的存在是为了保证数据的完整性，安全性。

事务的运行流程

开启事务机制
执行insert 语句记录到操作历史中
执行 update 语句记录到操作历史中
执行 delete 语句记录到操作历史中
提交事务 结束
1
2
3
4
5

事务的特性

ACID

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

C:一致性：事务是必须保证多条DML语句同时成功或者失败

I:隔离性：事务A与事务B这间具有隔离性

D:持久性：最终数据必须保存在硬盘上，事务才算最终的结束。

关于事务之间的隔离性

事务隔离性存在隔离级别，理论隔离级别包括4个

​ 第一级别：读未提交（read uncommitted） 对方的事务还没有提交，我们的事务可以直接读取对方事务未提交的数据。读未提交存在脏读（Dirty read）表示我们读到了脏数据。

​ 第二级别：读已提交（read committed）对方事务提交之后的数据我们可以读取到，读已提交存在的问题是：不可重复读。

​ 第三级别：可重复读（repeatable read） 这种隔离级别解决了不可重复读的问题，存在的问题是读取的数据是幻想

​ 第四级别：序列化读 解决了所有问题，效率低，需要事务排队

mysql 数据库默认的隔离级别是可重复读。

oracle 默认库默认的隔离级别是读已提交。

演示事务

mysql 事务默认情况下是自动提交的，如何关闭自动提交？start transaction;

commit;

rollback;

设置事务的全局隔离级别

set global transaction isolation level read committed;

查看全局的隔离级别

select @@global.tx_isolation
1

代码实现

 Connection connection = null;
        try {
//            默认情况下 connection 默认自动提交,这里将其设置为默认不自动提交
            connection.setAutoCommit(false);
            connection = JDBCUtilsByDruid.getConnect(); 
//            在这里提交事务
            connection.commit();
           return  qr.update(connection, sql, parameters);
           
        } catch (SQLException e) {
            try {
//                如果发生异常就回滚
                connection.rollback();
            } catch (SQLException el) {
                el.printStackTrace();
            }
            throw new RuntimeException(e);
        } finally {
            JDBCUtilsByDruid.close(null, null, connection);
        } 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

索引

什么是索引

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

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

第一种：全表扫描

第二种根据索引检索(效率较高)

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

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

什么时候加索引

• 数据量庞大
• 字段很少的 DML 操作
• 该字段经常出现在 where 语句后面

注意：主键和具有 unique 约束的字段会自动添加索引，根据主键查询效率较高，尽量根据主键查询

怎么加索引

查看加了索引没有

explain select ename,sal from emp where sal = 5000;
1

create index 索引名称 on 表名(字段名);

删除索引对象

drop index 索引名称 on 表名;

底层使用 B-tree

索引什么时候失效

select ename from emp where ename like '%A%';
模糊查询的时候，第一个测试过符使用的是%，这个时候索引是失效的。
1
2

视图

基本概念

1. 视图是一个虚假表，其内容由查询定义，其数据来自于对应的真实表1.

2. 视图也有列，数据来处基表

3. 通过视图可以修改基表的数据

4. 基本的改变，也会影响到视图的数据

创建视图

create view emp_view01 as select empno,ename,job,deptno from emp;
1

查看视图

desc emp_view01;
select * from emp_view01;
1
2

删除视图

drop view emp_view01;
1

套娃

create view emp_view02 as select empno,ename from empview02;
1

视图的作用隐藏表的实现细节

数据库设计三范式

什么是设计范式

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

第一范式

（1NF）： 要求数据达到原子性，使数据不可再分；

第二范式

（2NF)： 使每一行数据具有唯一性，并消除数据之间的“部分依赖”，使一个表中的非主键字段，完全依赖于主键字段

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

有2个方面的要求：
1、每一行数据具有唯一性：只要给表设计主键，就可以保证唯一性。
2、消除数据之间的“部分依赖”（不允许有多个候选键）；

实际的开发中以了满足客户的需求为主，有的时候会全找冗余换执行速度。

第三范式（3NF）

一对多？两张表，多的表加外键

：使每个字段都独立地依赖于主键字段（独立性），而要消除其中部分非主键字段的内部依赖——这种内部依赖会构成“传递依赖”

表的设计经典方案

一对一怎么设计

一对一有两种方案

1. 主键共享
2. 外键唯一

navicat 工具

下载教程

shell 命令 ipconfig 可以查到目前我们的 ip 的一些信息

MySQL 34题

1. 取得每个部门最高薪水的人员名称

   第一步：求出每个部门的最高薪水 t
   select deptno,max(sal)  from emp group by deptno;
   第二部：求出这个最高薪水对应的名字 e
   select 
   	e.ename ,t.deptno,t.msal 
   from 
   	(
           select deptno,max(sal) as msal from emp group by deptno
       ) t
   left join
   	emp e 
   on
   	e.sal = t.msal and e.deptno=t.deptno;
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13

2. 哪些人的薪水在部门的平均薪水之上

   第一步：先求出部门的平均薪水
   select avg(sal),deptno from emp group by deptno;
   第二步：求出哪些人的薪水在部门的薪水之上
   select 
   	e.ename,e.sal,e.deptno
   from 
   	(
           select avg(sal)as avgsal,deptno from emp group by deptno
       )as t
   join 
   	emp e
   on 
   	e.sal>t.avgsal and e.deptno=t.deptno;
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13

3. 取得部门中（所有人的）平均的薪水等级

   第一步：先找出部门所有人的平均薪水等级
   select deptno,avg(sal)
   from emp 
   group by deptno;
   第二步，找出这个薪水在薪水等级表中的等级
   select s.grade,t.deptno
   from (
       	select deptno,avg(sal) as avgsal
   		from emp 
   		group by deptno
   	)as t
   join salgrade s
   on t.avgsal between s.losal and s.hisal; 
   另一种理解
    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;
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14
   15

4. 不准用组函数（Max），取得最高薪水

   1. 使用limit
   select sal,ename from emp order by sal desc limit 1;
   2. 使用自连接
   select sal from emp where sal not in(select e.sal from emp e join emp p on e.sal<p.sal);
   1
   2
   3
   4

5. 取得平均薪水最高的部门的部门编号

   使用分组加排序加 limit
   select deptno,avg(sal) as avgsal from emp group by deptno order by  avgsal desc limit 1;
   1
   2

6. 取得平均薪水最高的部门的部门名称

   select	
   	d.dname,avg(e.sal) as avgsal 
   from 
   	emp e 
   join 
   	dept d
   on   
   	d.deptno = e.deptno
   group by 
   	e.deptno 
   order by  
   	avgsal 
   limit 
   	1 ;
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14

7. 求平均薪水的等级最低的部门的部门名称

   先求出各个部门的平均薪水的排序
   select	
   	d.dname,avg(e.sal) as avgsal 
   from 
   	emp e 
   join 
   	dept d
   on   
   	d.deptno = e.deptno
   group by 
   	e.deptno 
   order by  
   	avgsal ;
   再将这个表中的属于同一个部门的
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14

8. 取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还要高的领导人姓名

   1. 先找出普通员工的最高工资
   select max(sal)as emaxsal from emp where mgr is not null;
   2.找出比最高薪水还高的领导人
   select ename,sal 
   from emp 
   where sal>(select max(sal)
   from emp 
   where empno not in (select distinct mgr from emp where mgr is not null));
   1
   2
   3
   4
   5
   6
   7
   8

9. 取得薪水最高的前五名员工

   select ename,sal from emp order by sal desc limit 4;
   1

10. 取得薪水最高的第六到第十名员工

    select ename,sal from emp order by sal desc limit 5,5;
    1

11. 取得最后入职的5名员工

    select ename,hiredate from emp order by hiredate desc limit 4;
    1

12. 取得每个薪水等级有多少员工

    第一步先求出每个员工的薪水等级
    select s.grade as epsal from emp e left join salgrade s on e.sal between losal and hisal;
    第二在t表上以 grade 分组 count 计数
    select 
    	s.grade ,count(*) 
    from 
    	emp e 
    join 
    	salgrade s 
    on 
    	e.sal between s.losal and s.hisal
    group by 
    	s.grade;
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13

13. 有3个表S(学生表)，C（课程表），SC（学生选课表）

S（SNO，SNAME）代表（学号，姓名） 

C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）

SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）

问题：

1，找出没选过“黎明”老师的所有学生姓名。

2，列出2门以上（含2门）不及格学生姓名及平均成绩。

3，即学过1号课程又学过2号课所有学生的姓名。
1
2
3
4
5
6
7
8
9
10
11
12
13

14. 列出所有员工及领导的姓名

    select a.ename '员工',b.ename'领导' from emp a left join emp b on a.mgr = b.empno;
    1

15. 列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称

    select a.hiredate,a.deptno,a.ename '员工',b.ename'领导' ,b.hiredate from emp a left join emp b on a.mgr = b.empno where  a.hiredate >b.hiredate;
    1

16. 列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门.

    select 
    	d.dname,e.ename 
    from 
    	emp e 
    left join 
    	dept d 
    on 
    	d.deptno = e.deptno 
    order by 
    	d.dname
    ; 
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11

17. 列出至少有5个员工的所有部门

    找出部门员工的数据
    select count(deptno),deptno from emp group by deptno;
    找出大于5个的
    select cdeptno,deptno from (select count(deptno) as cdeptno,deptno from emp group by deptno) t
    where t.cdeptno>=5;
    或者
     select deptno from emp group by deptno having count(*) >=5;
    1
    2
    3
    4
    5
    6
    7

18. 列出薪金比"SMITH"多的所有员工信息.

    select ename,sal from emp where sal>(select sal from emp where ename='smith' );
    1

19. 列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数.

    先找出为 clerk 的的姓名及部门
    select ename,deptno from emp where job='clerk';
    再找出对应的部门名称
    select t.ename,k.cdeptno,d.dname from (select ename,deptno from emp where job='clerk') as t
    join (select count(deptno) as cdeptno,deptno from emp group by deptno) k
    on k.deptno=t.deptno
    join dept d
    on d.deptno  =t.deptno;
    1
    2
    3
    4
    5
    6
    7
    8

20. 列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数

    select job,count(*) from emp group by job having min(sal)>1500;
    1

21. 列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部门编号

    select ename from emp where deptno=(select deptno from dept where dname='sales'); 
    1

22. 列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级.

    先找出公司的平均薪金
    select avg(sal) from emp;
    再找出高于平均薪金的员工信息
    select e.ename,d.dname,p.mgr,s.grade 
    from emp e
    join
    	dept d
    on e.deptno = d.deptno
    left join emp p
    on p.empno=e.mgr
    join salgrade s
    on e.sal between s.losal and s.hisal
    where e.sal>(select avg(sal) from emp);
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13

23. 列出与"SCOTT"从事相同工作的所有员工及部门名称.

    找出scott从事的什么工作
    select distinct job from emp where ename='scott';
    再找相同工作员工的信息
    select e.ename,d.dname
    from emp e
    join dept d
    on d.deptno=e.deptno
    where e.job=(select distinct job from emp where ename='scott') and e.ename<> 'scott';
    1
    2
    3
    4
    5
    6
    7
    8

24. 列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金.

    select sal from emp where deptno=30;
    求出等于这个薪水的其他的部门的名字
    select 
    	ename,sal
    from
    	emp 
    where 
    	sal in (select distinct sal from emp where deptno=30)
    and deptno<>30;
    ;
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10

25. 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金.部门名称

    在30部门最高的薪水
    select max(sal) from emp where deptno=30;
    找出这个薪金
    select e.ename,d.dname,e.sal 
    from emp e
    join dept d
    on d.deptno=e.deptno
    where e.sal>(select max(sal) from emp where deptno=30);
    1
    2
    3
    4
    5
    6
    7
    8

26. 列出在每个部门工作的员工数量,平均工资和平均服务期限.

    对部门进行分组
    select count(ename),ifnull(avg(sal),0),deptno,avg(timestampdiff(YEAR,hiredate,now()))from emp group by deptno;
    1
    2

27. 列出所有员工的姓名、部门名称和工资

    select e.ename,d.dname,e.sal from emp e  join dept d on e.deptno=d.deptno;
    1

28. 列出所有部门的详细信息和人数

    select d.dname,d.loc,count(ename) from emp e left join dept d on e.deptno=d.deptno group by e.deptno;
    1

29. 列出各种工作的最低工资及从事此工作的雇员姓名

    找出最低工资及工作
    select min(sal) as msal,job from emp group by job ;
    找出对应的工作的姓名
    select t.msal,t.job,e.ename 
    from emp e 
    right join (select min(sal) as msal,job from emp group by job) t 
    on e.job=t.job and e.sal=t.msal;
    1
    2
    3
    4
    5
    6
    7

30. 列出各个部门的MANAGER(领导)的最低薪金

    先选出领导
    select min(p.sal),e.deptno from emp e join emp p on e.mgr = p.empno group by e.deptno;
    1
    2

31. 列出所有员工的年工资,按年薪从低到高排序

    select ename,12* (sal+ifnull(comm,0)) as incom from emp order by incom;
    1

32. 求出员工领导的薪水超过3000的员工名称与领导名称

    select e.ename,p.ename 
    from emp e 
    join emp p 
    on e.mgr=p.empno 
    where p.sal>3000;
    1
    2
    3
    4
    5

33. 求出部门名称中,带’S’字符的部门员工的工资合计、部门人数.

    select d.dname,sum(e.sal),count(ename) from emp e join dept d
    on e.deptno=d.deptno
    group by e.deptno
    having d.dname like'%S%';
    1
    2
    3
    4

34. 给任职日期超过30年的员工加薪10%.

    update emp set sal = (sal*1.1) where timestampdiff(YEAR,hiredate,now())>30;
    1

Mysql 管理

https://www.jb51.net/article/65645.htm

Mysql 备份并恢复

# 备份
mysqldump -u root -p -B exercise > d:\\bak.sql
# 恢复
source d:\\bak.sql
1
2
3
4

加密函数和系统函数

查询用户

 select user() from dual;
1

数据库名称

select database() from dual;
1

字符串加密

select md5('hsp') from dual;
或者
select password('hsp') from dual;
1
2
3

mysql.user表示数据库的用户密码加密

流程控制函数

if(expr1,expr2,expr3) 如果expr1为true 就返回 expr2 否则返回 expr3
ifnull(expr1,expr2) 如果expr1不为空，则返回expr1,否则返回expr2
select case when expr1 then expr2 when expr3 then expr4 else expr5 end;
# 如果expr1为true ，则舞台expr2，如果expr3为true 就返回expr4 否则返回
# expr5
1
2
3
4
5

判断空要用 is

jdbc 连接 mysql

1.  先创建一个 directory 命名为 libs
   1

2 . 把当前版本的 jar 包放进这个目录内，然后在这个目录上右击选择 add as library

3. 代码

try {
//          先创建一个 directory
            Driver driver = new Driver();//创建表
//            1 jdbc:mysql:// 固定的
//            2 localhost 主机，可以是ip 地址 然后是端口号
            String url = "jdbc:mysql://localhost:3306/exercise";
            Class.forName("com.mysql.jdbc.Driver");
            Properties properties = new Properties();
            properties.setProperty("user", "root");
            properties.setProperty("password", "123456");

            Connection connect = driver.connect(url,properties);
            String sql = "delete from user where id = 2";
            Statement statement = connect.createStatement();
            int i = statement.executeUpdate(sql);
            System.out.println(i > 0 ? "成功" : "失败");


        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

连接数据库的五种方式

1. driver 与 connect

   try {
   //          先创建一个 directory
               Driver driver = new Driver();//创建表
   //            1 jdbc:mysql:// 固定的
   //            2 localhost 主机，可以是ip 地址 然后是端口号
               String url = "jdbc:mysql://localhost:3306/exercise";
               Class.forName("com.mysql.jdbc.Driver");
               Properties properties = new Properties();
               properties.setProperty("user", "root");
               properties.setProperty("password", "123456");
   
               Connection connect = driver.connect(url,properties);
               String sql = "delete from user where id = 2";
               Statement statement = connect.createStatement();
               int i = statement.executeUpdate(sql);
               System.out.println(i > 0 ? "成功" : "失败");
   
   
           } catch (ClassNotFoundException | SQLException e) {
               e.printStackTrace();
           }
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14
   15
   16
   17
   18
   19
   20
   21

2. 使用反射机制

   //        使用反射机制更加灵活,减少依赖性
           Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
           Driver driver = (Driver) aClass.newInstance();
           String url = "jdbc:mysql://localhost:3306/exercise";
           Class.forName("com.mysql.jdbc.Driver");
           Properties properties = new Properties();
           properties.setProperty("user", "root");
           properties.setProperty("password", "123456");
           Connection connect = driver.connect(url,properties);
   1
   2
   3
   4
   5
   6
   7
   8
   9

3. 使用 DriverManger 替代 Driver 进行统一管理

   //        使用反射机制更加灵活,减少依赖性
           Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
           Driver driver = (Driver)aClass.newInstance();
           String url = "jdbc:mysql://localhost:3306/exercise";
           String user="root";
           String password="123456";
           DriverManager.registerDriver(driver);//注册 Driver 驱动
           Connection connection = DriverManager.getConnection(url, user, password);
           System.out.println(connection);
   1
   2
   3
   4
   5
   6
   7
   8
   9

4. Class.forName 自动完成注册驱动，简化代码推荐使用

   //当Class.forName 加载 Driver 类时，完成注册
   Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
           String url = "jdbc:mysql://localhost:3306/exercise";
           String user="root";
           String password="123456";
           Connection connection = DriverManager.getConnection(url, user, password);
           System.out.println(connection);
   1
   2
   3
   4
   5
   6
   7

5. 可以不写 Class.forName 在 5.1 后可以无需显示调用，建议还是写上，然后将url user password 都写到配置文件中方便操作

查询

ResourceBundle mysql = ResourceBundle.getBundle("mysql");
//绑定 properties 文件

        Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
        String url =mysql.getString("url");
        String user=mysql.getString("user");
        String password=mysql.getString("password");
        Connection connection = DriverManager.getConnection(url, user, password);
        Statement statement = connection.createStatement();
        String sql = "select * from user";
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()){
            int id = resultSet.getInt(1);
            String name = resultSet.getNString(2);
            String address = resultSet.getNString(3);
            System.out.print(id);
            System.out.print(name);
            System.out.println(address);
        }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

修改

 ResourceBundle mysql = ResourceBundle.getBundle("mysql");

        Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
        String url =mysql.getString("url");
        String user=mysql.getString("user");
        String password=mysql.getString("password");
        Connection connection = DriverManager.getConnection(url, user, password);
        Statement statement = connection.createStatement();
        String sql = "update user set name='郭' where id=1";
        int i = statement.executeUpdate(sql);
        System.out.println(i>0?"成功":"失败");

1
2
3
4
5
6
7
8
9
10
11
12

sql 注入

指用一些万能的 sql 来进入系统，

Statement 存在 sql 注入

现在通过 preparedStatement [预处理]

CallableStatement [存储过程]

使用preparedStatement

查询

Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
        ResourceBundle mysql = ResourceBundle.getBundle("mysql");
        String url = mysql.getString("url");
        String user = mysql.getString("user");
        String password = mysql.getString("password");
        Connection connection = DriverManager.getConnection(url, user, password);
        String sql = "select id,name,address from user where id=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1,value);
//        这里在执行 sql 语句的时候里面不用再写 sql 了
        ResultSet resultSet = preparedStatement.executeQuery();
        if(resultSet.next()){
            System.out.println("恭喜登录成功");
            int id = resultSet.getInt(1);
            String name = resultSet.getNString(2);
            String address = resultSet.getNString(3);
            System.out.println(id);
            System.out.println(name);
            System.out.println(address);

        }else{
            System.out.println("登录失败");
        }
        resultSet.close();
        preparedStatement.close();
        connection.close();

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

更新

 Class<?> aClass = Class.forName("com.mysql.jdbc.Driver");
        ResourceBundle mysql = ResourceBundle.getBundle("mysql");
        String url = mysql.getString("url");
        String user = mysql.getString("user");
        String password = mysql.getString("password");
        Connection connection = DriverManager.getConnection(url, user, password);
        String sql = "insert into user values (?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1,value);
        preparedStatement.setString(2,"小黄");
        preparedStatement.setString(3,"南昌理工");
//        这里在执行 sql 语句的时候里面不用再写 sql 了
        int i = preparedStatement.executeUpdate();
       if(i>0){
           System.out.println("插入成功");
       }else{
           System.out.println("插入失败");
       }
        preparedStatement.close();
        connection.close();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

批处理

1. 当需要成批插入或者更新记录时，可以使用 Java 的批量更新机制，这机制允许多条语句一次性提交给数据库处理。
2. 批处理包括下面方法
   • addBatch()添加需要批量处理的 sql 语句或者参数
   • executeBatch() 执行批处理语句
   • clearBatch() 清空批处理包的语句
3. 如果要使用批处理，请再 url 中加参数 ?rewriteBatchedStatements=true

数据库连接池

传统 Connection 分析

1. 传统的 JDBC 数据库连接使用 DriverManager 来获取，每次向数据库建立连接的时候都要将 Connection 加载到内存中，再验证 Ip ,用户与密码，频繁的操作将占用很长的时间
2. 每一次连接用完后都需要断开，如果程序出现异常而未能关闭，将导致内存泄露
3. 解决传统开发中的数据库连接问题，可以采用数据库连接池技术

数据库连接池的基本介绍

1. 预先在缓冲池中放入一定量的连接，当需要建立数据库的连接时，只需要从缓冲池中取出一个，使用完毕后施加
2. 数据连接池负责分配、管理和释放数据连接，它允许应用程序重复使用一个现有的连接，而不是再建立一个连接
3. 当应用程序向连接池请求超过最大数量时，这些请求将被加入到等待队列

druid 德鲁伊数据连接池

  Properties properties = new Properties();
        properties.load(new FileInputStream("jdbc\\src\\mysql.properties"));
//        4. 创建一个指定参数的数据连接池
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        Connection connection = dataSource.getConnection();
        System.out.println("连接成功");
        connection.close();
1
2
3
4
5
6
7

使用封装的 druid 工具类进行操作

 System.out.println("使用 Druid 方式完成");
        Connection connection = null;
        String sql = "select * from admin";
        PreparedStatement preparedStatement  = null;
        ResultSet  resultSet = null;
        try {
            connection = JDBCUtilsByDruid.getConnect();
            preparedStatement =   connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                String name = resultSet.getNString("name");
                String password = resultSet.getNString("password");
                System.out.print(name);
                System.out.println(password);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtilsByDruid.close(resultSet,preparedStatement,connection);
        }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

Apache-DBUtils

1. QueryRunner 类：该类封装了 SQL 的执行，是线程安全的，可以实现增删改查
2. 使用 QueryRunner 类实现查询
3. ResultSetHandler 接口：用于处理 java.sql.ResultSet 接口,将数据按要求转换成另一种形式

查询

返回多行多列

 List<Admin> query = queryRunner.query(connection, sql, new BeanListHandler<>(Admin.class));
1

返回单个对象

 Admin query = queryRunner.query(connection, sql, new BeanHandler<>(Admin.class), 5);
1

返回单行单列

 Object obj = queryRunner.query(connection, sql, new ScalarHandler<>(Admin.class), 5);
1

更改

    1. 得到连接
            connection = JDBCUtilsByDruid.getConnect();
//            2. 使用DBUtils 类和接口，先引用 DBUtils 相关 jar 文件
//            3. 创建 QueryRunner
            QueryRunner queryRunner = new QueryRunner();
//            4. 就可以执行相关的方法，返回单个对象
            String sql = "insert into admin values(11,'张三丰','4321')";
            int affectedRows = queryRunner.update(connection, sql);
            if(affectedRows>0){
                System.out.println("插入成功");
            }else {
                System.out.println("插入失败");
            }
1
2
3
4
5
6
7
8
9
10
11
12
13

BasicDao

分析其需求

apeache-dbUtils+Druid 简化了我们的操作，但还有不足

1. sql 语句固定，不能通过参数传入通用性不好
2. select 语句，如果有返回值，返回值类型不能固定需要使用泛型

mysql 优化

没用索引的几种情况

模型数空运最快

模糊查询%开头会造成索引失效 解决方法将查询的列改成索引相应的列

使用查询时如果有任何字段没有索引的情况，都会回表如何不让索引覆盖，将被查询的字段，建立到联合索引即将没有索引的字段新建一个索引。

型代表数据类型，数据类型错误了也会造成索引失效
数表函数，对索引的字段使用内部函数索引也会失效，
空表null值索引不存储空值，如果索引可以存储空值数据库不会按照索引来计算
运，对索引列进行±*/运算会导致索引失效
最表左原则，在复合索引中索引列的顺序非常重要，如果不是按照索引列最左列开始查找则无法使用索引
快表示数据库认为全表扫描更快数据库就不会使用索引

1. 设置索引
2. 使用 explain 查看你的 select 查询
3. 不要使用表达式作为查询
   • 如 select * from t where id+1<5;
   • 原理大多 mysql 服务器都开启了查询缓存，当多个相同的查询被执行了多次的时候，这些查询结果会被放到一个缓存中。但当使用表达式的时候就不会使用缓存。
4. 尽量使用 in 来代替 or
5. 条件列表值如果连续使用 between 替代 in
6. 无重复记录的结果集使用 union all 合并，而不是使用 union
7. 使用 like 操作符时测试过符要放在右侧
   • select * from t where name like '150_';
   • 原理： 如果通配符放在最左边， sql 优化器会选择效率低的全表扫解析。
8. 能使用 where 就不使用 having
9. 数据库怎么忧化查询效率
   • 住在引擎选择：如果数据表需要事务处理，应该考虑使用 innoDB,因为它完全符合 ACID 特性，如果不需要事务处理使用默认的 MyISAM
   • 对查询进行优化，尽量避免全表扫描，在 查询比较频繁的列上建立索引
   • 避免在 where 子句中对字段进行 null 值进行判断，否则将导致引擎放弃使用索引而进行全表扫描
   • 避免在 where 子句中使用 != 或者 <> 否则将导致引擎放弃使用索引而进行全表扫描
   • 避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描
   • update, 如果只更改1、2个字段，不要 update 全部字段，否则频繁调用会引起明显的性能消耗，同带来大理日志。
   • 对于多张大数据量（这里几百条就算大了）的表 JOIN，要先分页再 JOIN，否则逻辑读会很高，
     性能很差。