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
关闭服务
net stop mysql
cd /D D:\Program Files (x86)\mysql-5.7.19-winx64\bin
登录
mysql -uroot -p输入密码
显示数据库
show databases
显示表
show tables
创建库
create (这里为你要创建的库名)
切换库
use （你要切换的库名）
引用脚本
source 文件路径
删除数据库：
drop database 数据库的名字
查看表结构
desc 表名
查看目前用的是哪个数据库
select database();
查看 mysql 版本
select version();
查看建表语句
查询语句
sql 语句以 ; 结尾.
sql 语句不区分大小写
select 字段名1,.... form 表名
查询员工的年薪(字段可以参与数学运算)
select ename,sal*12 from emp;
+--------+----------+
| 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 |
+--------+----------+
给查询结果的列重合名 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 |
+--------+----------+
注意标准sql 语句中要求字符串使用单引号
查询所有字段
select * from emp; 实际开发中不使用 * 号效率较低
条件查询
select 字段 from 表名 where 条件;
查询工资等于 5000 的员工姓名
select ename from emp where sal=5000;
找出工资在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 的必须保证左小右大
在数据库中 NULL 代表一个值代表什么也没有为空,不能用等号衡量,必须使用 is null 或者 is not null
select ename,sal,deptno from emp where sal>1000 and (deptno=20 or deptno=30);
运算优先级不确定的时候使用小括号
or(1200,1300,1400);
表示多个or
模糊查询 like
% 代表任意多个字符,_ 代表任意一个字符
找出名字当中含有o 的
select ename from emp where ename like'%o%';
找出第二个字母是A的
select ename,sal from emp where ename like'_A%';
按照工资的升序进行排序
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 下降
按照工资的降序排列,当工资相同的时候再按照名字的升序排序
select ename,sal from emp order by sal desc,ename asc;
找出工作岗位是 SALESMAN 的员工,并且要求按照薪资的降序排序
select ename,job,sal from emp where job='SALESMAN' order by sal desc;
执行顺序
select
字段 5
from
tablename 1
where
条件 2
group by
... 3
having
... 4
order by
... 6
limit
...7
分组函数
分组函数自动忽略 NULL
sql 语句中有一个语法规则 分组函数无法直接出现在 where子句当中.
因为 group by 在 where 后执行 所以 分组函数无法在 where 后执行
count 计数
sum 求和
avg 平均值
max 最大值
min 最小值
找出工资总和
select sum(sal) from emp;
最高工资
select min(sal) from emp;
最低工资
select min(sal) from emp;
平均工资
select avg(sal) from emp;
找出总人数
select count(sal) from emp;
14
找出比平均工资大的人
select ename,sal from emp where sal >(select avg(sal)from emp);
分组函数自动忽略 NULL
select count(comm) from emp;
4
所有的数据库都是这样规定的,只要有NULL 出现在数学工式中这个结果必为NULL
ifnull() 空处理函数
ifnull (可能为 NULL 的数据,被当做什么处理);
计算年薪,算上补贴
select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;
count(*) 和 count (具体的某个字段),他们有什么区别
count(*) 不是统计某个字段中数据的个数,而是统计总条数
count(comm) 表示统计 comm 字段中不为NULL 的元素;
group by 和having
group by :按照某个字段或者某些字段进行分组
having : having 是对分组之后的数据进行再次过渡
找出每个工作岗位的最高薪资
select max(sal) from
注意:分组函数一般都会和 group by 联合使用,这也是为什么它被称为分组函数的原因,并且任何一个分组函数都是在 group by 这前执行.当一条语句没有 group by 的话,整个表自成一组
当一条 sql 语句有 group by 的话,select 中的语句要不就是分组函数,或者分组字段 不然得到的数据是没有意义的,在其他的数据库直接通不过编译
找出每个岗位不同工作的最高薪资
select deptno,job,max(sal) from emp group by deptno,job order by deptno;
找出每个部门的最高薪资,要求显示薪资大于 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;
显示每个部门的平均薪资,要求显示薪资大于2000的数据
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
关于结果集合去重
select distinct job from emp;
* 注意distinct 只能出现在所有的sql 语句的最前方
* distinct 出现在最前方表明后面所有的字段联合去重
统计岗位的数量
select count (distinct job) as jobno form emp;
连接查询
一般一个业务会放在多张表,如果放一张表可能会造成数据的冗余.
根据表的连接类型分为
内连接
? 等值连接
? 非等值连接
? 自连接
外连接
? 左连接
? 右连接
全连接(少用,不讲)
笛卡尔乘积现象
在表的连接中有一种现象被我们称为笛卡尔乘积现象,如果两张表的的连接没有任何限制两张表的记录将是两张表的乘积.
如何避免这种现象,当然是加条件过滤,避免了笛卡尔乘积现象会减少比较的次数吗?
不会比较的次数依然是两个表的乘积,只不过显示的是有效的记录.
显示每个人的以及所在的部门的名字
select
e.ename,d.dname
from
emp e,dept d
where
e.deptno= d.deptno
order by
dname;
内连接
等值连接
最大的特点是等值关系
将员工的姓名与其部门的名字查找出来
select
e.ename,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno;
非等值连接
最大特点非等值关系
将员工的薪水与其对应的等级显示
select e.ename,e.sal,s.grade
from emp e
join salgrade s
on e.sal between s.losal and s.hisal;
自连接
最大的特点是:一张表看做两张表,自己连接自己
找出每个员工的上级领导,要求显示员工与对应领导的姓名
select
a.ename as'员工名',b.ename as '领导名'
from
emp a
join
emp b
on
a.mgr = b.empno;
外连接
什么是外连接,与内连接的区别?
-
内连接:
- 假设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;
外连接/左连接
select
a.ename as'员工名',b.ename as '领导名'
from
emp a
left join
emp b
on
a.mgr = b.empno;
? 外连接/右连接
select
a.ename as'员工名',b.ename as '领导名'
from
emp b
right join
emp a
on
a.mgr = b.empno;
外连接最重要的特点是:主表的数据无条件的全部查询出来,如果没有也不会丢失
案例:找出哪个部门没有员工?
select
d.*
from
emp e
right join
dept d
on
e.deptno = d.deptno
where
e.empno is null;
三张表怎么连接查询
案例:找出每一个员工的部门名称以及工资等级
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;
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 |
+--------+------------+----------+
案例: 找出每个员工的工资等级、部门名称、上级领导。
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;
注意解释一下
…
? A
join
? B
join
? C
on
? …
子查询
概念
select 语句中嵌套select 语句,被嵌套的 select 语句就是子查询.
from 后面嵌套子查询
案例: 找出每个部门平均薪水的薪资等级(按照部门编号分组,求sal 的平均值)
第一步:找出每个部门平均薪水
select
deptno,avg(sal) as avgsal
from
emp
group by
deptno;
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;
案例:找出每个部门平均的薪水等级
第一步:找出每个员工的薪水等级
select
e.ename,e.sal,s.grade,e.deptno
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal;
第二步:按照部门编号分组求等级的平均值
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;
在select 后面嵌套子查询
案例:找出每个员工所在的部门名称,要求显示员工名与部门名称
select
e.ename,
(
select
d.dname
from
dept d
where
e.deptno = d.deptno
)
as dname
from
emp e;
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';
使用 union 时候列数不能不同
limit(重点)
limit 是MySQL 特有的,limit 取结果集中的部分数据.
语法
limit startIndex , length
startIndex 表示取几个
length 表长度
案例:取出工资前五名的员工
select
sal,ename
from
emp
order by
sal desc
limit
0,5;
0 可以省略,如果只写一个 5 前面默认就是0
案例:找出工资是第四到第九名的员工
select
sal,ename
from
emp
order by
sal desc
limit
3,6;
建表
语句语法
create table {
字段名 数据类型 约束,
...
};
关于 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)
);
插入数据
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');
插入多行
insert into t_student values(2,'jack','1','gaoskaoyiban','1999-03-23'),(3,'rose','1','gaoskaoyiban','2000-03-23');
表的复制
语法
create table 表名 as sql语句
insert into dept1 select * from dept;
将这张表的数据插入里面
create table emp1 as select * from emp;
修改数据：update
语法格式
update 表名 set 字段名1=值，字段名2=值2.. where 条件
注意如果没有条件整张表全部更新
案例
将部门 10 的 LOC 修改为 SHANGHAI ，将部门名称修改为 RENSHIBU
update dep1 set loc='SHANGHAI',dname='RENSHIBU' where deptno='10';
删除
语法格式
delete from 表名 where 条件
删除表中的所有记录
delete from dept1;
删库跑路
删表
drop table if exists t_student;
删除大表（慎用无法恢复）
truncate tab emp1;
约束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');
唯一约束
- 唯一约束修饰的字段具有唯一性，不能重复，但可以为 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');
给两个列或者多个列添加 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
);
主键约束
列级
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');
表级
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');
添加主键约束，这个键值就不可以为 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)
);
外键字段可以为 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)
);
存储引擎
存储引擎就是数据存储在电脑上的方式
常见的存储引擎
- MYISAM 这是最常用的存储引擎，但是这种引擎不是默认的
- 使用三个文件来表示一个表
- 格式文件（mytable.frm）
- 数据文件(mytable.myd)
- 索引文件(mytable.myi)
- 优点可被压缩，节省存储空间，并且可以转换成可读表，提高检索效率。
- 缺点不支持事务
- InnoDB
- 优点支持事件，行级锁，外键等，这种引擎数据的案例得到保障。
- 表的结构存储在xxx.frm 文件数据存储在tablespace 这样的表空间中，无法被压缩无法转换成只读。
- InnoDB 支持级联删除和级联更新
- MEMORY
- 缺点：不支持事件，数据容易丢失， 因为所有的数据存储在内存中，
- 优点查询速度最快
事务
什么是事务
事务是一个完整的业务逻辑，不可再分，比如银行转账，从一个账户转出来，转到另一个账户上，需要执行两条 DMl 语句，两条必须同时成功，或者同时失败。
和事务相关的语句只有 DML 语句（insert updata delete）
因为这三个语句都是和事务相关的，事务的存在是为了保证数据的完整性，安全性。
事务的运行流程
开启事务机制
执行insert 语句记录到操作历史中
执行 update 语句记录到操作历史中
执行 delete 语句记录到操作历史中
提交事务 结束
事务的特性
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
代码实现
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);
}
索引
什么是索引
索引就相当于一本书的目录，通过目录，我们可以很快的找到对应的资源。
在数据库方面，查询一张表的时候有两种检索方式，
第一种：全表扫描
第二种根据索引检索(效率较高)
索引虽然可以提高检索效率，但是不能随意添加索引，因为索引也是数据库中的对象，也需要数据库不断的维护，是有维护成本的。如果一张表经常的被修改，这样就不适合添加索引，因为数据一旦修改，索引就需要重新排序，进行维护。
添加索引是给某一个字段，或者某些字段添加索引
什么时候加索引
- 数据量庞大
- 字段很少的 DML 操作
- 该字段经常出现在 where 语句后面
注意：主键和具有 unique 约束的字段会自动添加索引，根据主键查询效率较高，尽量根据主键查询
怎么加索引
查看加了索引没有
explain select ename,sal from emp where sal = 5000;
create index 索引名称 on 表名(字段名);
删除索引对象
drop index 索引名称 on 表名;
底层使用 B-tree
索引什么时候失效
select ename from emp where ename like '%A%';
模糊查询的时候，第一个测试过符使用的是%，这个时候索引是失效的。
视图
基本概念
-
视图是一个虚假表，其内容由查询定义，其数据来自于对应的真实表1.
-
视图也有列，数据来处基表
-
通过视图可以修改基表的数据
-
基本的改变，也会影响到视图的数据
创建视图
create view emp_view01 as select empno,ename,job,deptno from emp;
查看视图
desc emp_view01;
select * from emp_view01;
删除视图
drop view emp_view01;
套娃
create view emp_view02 as select empno,ename from empview02;
视图的作用隐藏表的实现细节
数据库设计三范式
什么是设计范式
设计表的依据，按照这个三范式设计的表不会出现数据冗余
第一范式
（1NF）： 要求数据达到原子性，使数据不可再分；
第二范式
（2NF)： 使每一行数据具有唯一性，并消除数据之间的“部分依赖”，使一个表中的非主键字段，完全依赖于主键字段
多对多？三张表，关系表两个外键
有2个方面的要求：
1、每一行数据具有唯一性：只要给表设计主键，就可以保证唯一性。
2、消除数据之间的“部分依赖”（不允许有多个候选键）；
实际的开发中以了满足客户的需求为主，有的时候会全找冗余换执行速度。
第三范式（3NF）
一对多？两张表，多的表加外键
：使每个字段都独立地依赖于主键字段（独立性），而要消除其中部分非主键字段的内部依赖——这种内部依赖会构成“传递依赖”
表的设计经典方案
一对一怎么设计
一对一有两种方案
- 主键共享
- 外键唯一
navicat 工具
shell 命令 ipconfig 可以查到目前我们的 ip 的一些信息
MySQL 34题
-
取得每个部门最高薪水的人员名称
第一步：求出每个部门的最高薪水 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; -
哪些人的薪水在部门的平均薪水之上
第一步：先求出部门的平均薪水 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; -
取得部门中（所有人的）平均的薪水等级
第一步：先找出部门所有人的平均薪水等级 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; -
不准用组函数（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); -
取得平均薪水最高的部门的部门编号
使用分组加排序加 limit select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1; -
取得平均薪水最高的部门的部门名称
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 ; -
求平均薪水的等级最低的部门的部门名称
先求出各个部门的平均薪水的排序 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 ; 再将这个表中的属于同一个部门的 -
取得比普通员工(员工代码没有在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)); -
取得薪水最高的前五名员工
select ename,sal from emp order by sal desc limit 4; -
取得薪水最高的第六到第十名员工
select ename,sal from emp order by sal desc limit 5,5; -
取得最后入职的5名员工
select ename,hiredate from emp order by hiredate desc limit 4; -
取得每个薪水等级有多少员工
第一步先求出每个员工的薪水等级 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; -
有3个表S(学生表)，C（课程表），SC（学生选课表）
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号，成绩）
问题：
1，找出没选过“黎明”老师的所有学生姓名。
2，列出2门以上（含2门）不及格学生姓名及平均成绩。
3，即学过1号课程又学过2号课所有学生的姓名。
-
列出所有员工及领导的姓名
select a.ename '员工',b.ename'领导' from emp a left join emp b on a.mgr = b.empno; -
列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
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; -
列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门.
select d.dname,e.ename from emp e left join dept d on d.deptno = e.deptno order by d.dname ; -
列出至少有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; -
列出薪金比"SMITH"多的所有员工信息.
select ename,sal from emp where sal>(select sal from emp where ename='smith' ); -
列出所有"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; -
列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数
select job,count(*) from emp group by job having min(sal)>1500; -
列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部门编号
select ename from emp where deptno=(select deptno from dept where dname='sales'); -
列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级.
先找出公司的平均薪金 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); -
列出与"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'; -
列出薪金等于部门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; ; -
列出薪金高于在部门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); -
列出在每个部门工作的员工数量,平均工资和平均服务期限.
对部门进行分组 select count(ename),ifnull(avg(sal),0),deptno,avg(timestampdiff(YEAR,hiredate,now()))from emp group by deptno; -
列出所有员工的姓名、部门名称和工资
select e.ename,d.dname,e.sal from emp e join dept d on e.deptno=d.deptno; -
列出所有部门的详细信息和人数
select d.dname,d.loc,count(ename) from emp e left join dept d on e.deptno=d.deptno group by e.deptno; -
列出各种工作的最低工资及从事此工作的雇员姓名
找出最低工资及工作 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; -
列出各个部门的MANAGER(领导)的最低薪金
先选出领导 select min(p.sal),e.deptno from emp e join emp p on e.mgr = p.empno group by e.deptno; -
列出所有员工的年工资,按年薪从低到高排序
select ename,12* (sal+ifnull(comm,0)) as incom from emp order by incom; -
求出员工领导的薪水超过3000的员工名称与领导名称
select e.ename,p.ename from emp e join emp p on e.mgr=p.empno where p.sal>3000; -
求出部门名称中,带’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%'; -
给任职日期超过30年的员工加薪10%.
update emp set sal = (sal*1.1) where timestampdiff(YEAR,hiredate,now())>30;
Mysql 管理
https://www.jb51.net/article/65645.htm
Mysql 备份并恢复
# 备份
mysqldump -u root -p -B exercise > d:\\bak.sql
# 恢复
source d:\\bak.sql
加密函数和系统函数
查询用户
select user() from dual;
数据库名称
select database() from dual;
字符串加密
select md5('hsp') from dual;
或者
select password('hsp') from dual;
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
判断空要用 is
jdbc 连接 mysql
-
先创建一个 directory 命名为 libs
2 . 把当前版本的 jar 包放进这个目录内，然后在这个目录上右击选择 add as library
- 代码
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();
}
连接数据库的五种方式
-
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(); } -
使用反射机制
// 使用反射机制更加灵活,减少依赖性 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); -
使用 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); -
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); -
可以不写 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);
}
修改
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?"成功":"失败");
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();
更新
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();
批处理
- 当需要成批插入或者更新记录时，可以使用 Java 的批量更新机制，这机制允许多条语句一次性提交给数据库处理。
- 批处理包括下面方法
- addBatch()添加需要批量处理的 sql 语句或者参数
- executeBatch() 执行批处理语句
- clearBatch() 清空批处理包的语句
- 如果要使用批处理，请再 url 中加参数 ?rewriteBatchedStatements=true
数据库连接池
传统 Connection 分析
- 传统的 JDBC 数据库连接使用 DriverManager 来获取，每次向数据库建立连接的时候都要将 Connection 加载到内存中，再验证 Ip ,用户与密码，频繁的操作将占用很长的时间
- 每一次连接用完后都需要断开，如果程序出现异常而未能关闭，将导致内存泄露
- 解决传统开发中的数据库连接问题，可以采用数据库连接池技术
数据库连接池的基本介绍
- 预先在缓冲池中放入一定量的连接，当需要建立数据库的连接时，只需要从缓冲池中取出一个，使用完毕后施加
- 数据连接池负责分配、管理和释放数据连接，它允许应用程序重复使用一个现有的连接，而不是再建立一个连接
- 当应用程序向连接池请求超过最大数量时，这些请求将被加入到等待队列
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();
使用封装的 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);
}
Apache-DBUtils
- QueryRunner 类：该类封装了 SQL 的执行，是线程安全的，可以实现增删改查
- 使用 QueryRunner 类实现查询
- ResultSetHandler 接口：用于处理 java.sql.ResultSet 接口,将数据按要求转换成另一种形式
查询
返回多行多列
List<Admin> query = queryRunner.query(connection, sql, new BeanListHandler<>(Admin.class));
返回单个对象
Admin query = queryRunner.query(connection, sql, new BeanHandler<>(Admin.class), 5);
返回单行单列
Object obj = queryRunner.query(connection, sql, new ScalarHandler<>(Admin.class), 5);
更改
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("插入失败");
}
BasicDao
分析其需求
apeache-dbUtils+Druid 简化了我们的操作，但还有不足
- sql 语句固定，不能通过参数传入通用性不好
- select 语句，如果有返回值，返回值类型不能固定需要使用泛型
mysql 优化
没用索引的几种情况
模型数空运最快
模糊查询%开头会造成索引失效 解决方法将查询的列改成索引相应的列
使用查询时如果有任何字段没有索引的情况，都会回表如何不让索引覆盖，将被查询的字段，建立到联合索引即将没有索引的字段新建一个索引。
型代表数据类型，数据类型错误了也会造成索引失效
数表函数，对索引的字段使用内部函数索引也会失效，
空表null值索引不存储空值，如果索引可以存储空值数据库不会按照索引来计算
运，对索引列进行±*/运算会导致索引失效
最表左原则，在复合索引中索引列的顺序非常重要，如果不是按照索引列最左列开始查找则无法使用索引
快表示数据库认为全表扫描更快数据库就不会使用索引
- 设置索引
- 使用 explain 查看你的 select 查询
- 不要使用表达式作为查询
- 如
select * from t where id+1<5; - 原理大多 mysql 服务器都开启了查询缓存，当多个相同的查询被执行了多次的时候，这些查询结果会被放到一个缓存中。但当使用表达式的时候就不会使用缓存。
- 如
- 尽量使用 in 来代替 or
- 条件列表值如果连续使用 between 替代 in
- 无重复记录的结果集使用 union all 合并，而不是使用 union
- 使用 like 操作符时测试过符要放在右侧
select * from t where name like '150_';- 原理： 如果通配符放在最左边， sql 优化器会选择效率低的全表扫解析。
- 能使用 where 就不使用 having
- 数据库怎么忧化查询效率
- 住在引擎选择：如果数据表需要事务处理，应该考虑使用 innoDB,因为它完全符合 ACID 特性，如果不需要事务处理使用默认的 MyISAM
- 对查询进行优化，尽量避免全表扫描，在 查询比较频繁的列上建立索引
- 避免在 where 子句中对字段进行 null 值进行判断，否则将导致引擎放弃使用索引而进行全表扫描
- 避免在 where 子句中使用 != 或者 <> 否则将导致引擎放弃使用索引而进行全表扫描
- 避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描
- update, 如果只更改1、2个字段，不要 update 全部字段，否则频繁调用会引起明显的性能消耗，同带来大理日志。
- 对于多张大数据量（这里几百条就算大了）的表 JOIN，要先分页再 JOIN，否则逻辑读会很高，
性能很差。