目录
2.和事务相关的语句只有:DML语句。(insert delete update)
3.假设所有的业务都能使用1条DML语句搞定,还需要事务机制吗?
4.索引底层采用的数据结构是:B + Tree 5.索引的实现原理?
一、导入数据
(1)登录mysql数据库管理系统
mysql -uroot -p333
(2)查看有哪些数据库
show databases;
(3)创建属于我们自己的数据库
create database bjpowernode;
(4)使用bjpowernode数据
use bjpowernode;
(5)查看当前使用的数据库中有哪些表
show tables;
(6)初始化数据
source D:\course\05-MySQL\resources\bjpowernode.sql
二、数据表操作
(1)删除数据库
drop database bjpowernode;
(2)创建表
create table 表名(
字段名1 数据类型,
字段名2 数据类型,
字段名3 数据类型,
....
);
create table t_student(
no bigint,
name varchar(255),
sex char(1),
classno varchar(255),
birth char(10)
);
(3)删除表
drop table if exists t_student;
(4)查看表结构
desc emp;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO | int(4) | NO | PRI | NULL | | 员工编号
| ENAME | varchar(10) | YES | | NULL | | 员工姓名
| JOB | varchar(9) | YES | | NULL | | 工作岗位
| MGR | int(4) | YES | | NULL | | 上级领导编号
| HIREDATE | date | YES | | NULL | | 入职日期
| SAL | double(7,2) | YES | | NULL | | 月薪
| COMM | double(7,2) | YES | | NULL | | 补助/津贴
| DEPTNO | int(2) | YES | | NULL | | 部门编号
+----------+-------------+------+-----+---------+-------+
(5)查看当前使用的是哪个数据库
select database();
(6)查看mysql的版本号
select version();
(7)查看创建表的语句
show create table emp;
\c //结束一条语句
exit //退出mysql
三、数据操作--增删改查
1. 插入数据
insert into 表名(字段名1,字段名2,字段名3,....) values(值1,值2,值3,....)
insert into t_student(no,name,sex,classno,birth) values(1,'zhangsan','1','gaosan1ban', '1950-10-12');
insert into t_student
(no,name,sex,classno,birth)
values
(3,'rose','1','gaosi2ban','1952-12-14'),(4,'laotie','1','gaosi2ban','1955-12-14');// 一次插入多行数据
2.删除数据
delete from 表名 where 条件;
truncate table 表名; // 删除大表数据,表被截断,不可回滚。永久丢失。
3.更新数据
update 表名 set 字段名1=值1,字段名2=值2... where 条件;
update dept1 set loc = 'SHANGHAI', dname = 'RENSHIBU' where deptno = 10;
4.查询数据
(1)条件查询
```sql
select 字段,字段...
from 表名
where 条件;
//找出表中津贴为空的数据(null用is):
select ename,sal,comm from emp where comm is null or comm = 0;
//找出工作岗位是MANAGER和SALESMAN的员工:
select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
select ename,job from emp where job in('SALESMAN', 'MANAGER');
(2)排序
select
字段 3
from
表名 1
where
条件 2
order by
.... 4
```
select ename , sal*12 as yearsal from emp order by yearsal; // 升序
select ename , sal*12 '年薪' from emp order by sal asc; // 升序
select ename , sal from emp order by sal desc; // 降序。
select ename,sal from emp order by sal desc , ename asc; //部分升部分降
(3)分组查询
select 5
..
from 1
..
where 2
..
group by 3 (按照某个字段或者某些字段进行分组)
..
having 4 (对分组之后的数据进行再次过滤)
..
order by 6
..
找出工资总和?
select sum(sal) from emp;
找出最高工资?
select max(sal) from emp;
找出最低工资?
select min(sal) from emp;
找出平均工资?
select avg(sal) from emp;
找出总人数?
select count(*) from emp;
select count(ename) from emp;
重点1: 分组函数自动忽略NULL
重点2: 分组函数不可直接使用在where子句当中,因为group by是在where执行之后才会执行
select ename,sal from emp where sal > avg(sal); //语法错误
重点3: count(*)和count(具体的某个字段),他们有什么区别?
count(*):不是统计某个字段中数据的个数,而是统计总记录条数。(和某个字段无关)
count(comm): 表示统计comm字段中不为NULL的数据总数量
重点4: 当一条语句中有group by的话,select后面只能跟分组函数和参与分组的字段。
//找出每个部门的平均薪资,要求显示薪资大于2000的数据。
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;
(4)查询结果去重
select distinct job from emp;
//distinct只能出现在所有字段的最前面
select distinct deptno,job from emp;(联合去重)
//统计岗位的数量
select count(distinct job) from emp;
(5)单行处理函数
单行处理函数:输入一行,输出一行。
ifnull(可能为NULL的数据,被当做什么处理) : 属于单行处理函数。
select ename,ifnull(comm,0) as comm from emp;
//若comm为NULL,当作0处理,只要有NULL参与的运算结果一定是NULL。
(4)连接查询
内连接:
假设A和B表进行连接,使用内连接的话,凡是A表和B表能够匹配上的记录查询出来,这就是内连接。
AB两张表没有主副之分,两张表是平等的。
外连接:
假设A和B表进行连接,使用外连接的话,AB两张表中有一张表是主表,一张表是副表,主要查询主表中
的数据,捎带着查询副表,当副表中的数据没有和主表中的数据匹配上,副表自动模拟出NULL与之匹配。
- 内连接
1)等值连接
select
e.ename,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno;
where ...
2)非等值连接
select
e.ename,e.sal,s.grade
from
emp e
inner join
salgrade s
on
e.sal between s.losal and s.hisal;
- 外连接
```sql
select
a.ename '员工', b.ename '领导'
from
emp b
right join
emp a
on
a.mgr = b.empno;
- 三张表连接
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;
(5)子查询
select
..(select)
from
..(select)
where
..(select)
1)where子句中使用子查询
select * from emp where sal > (select avg(sal) from emp);
2)from子句中使用子查询
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;
3)select子句中使用子查询
select
e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname
from
emp e;
(6)分页查询
select ename,sal from emp order by sal desc limit 0, 5; //降序取前5个,0表示第一个,5表示取5
select ename,sal from emp order by sal desc limit 5;
四、事务(Transaction)
1.什么是事务?
一个事务是一个完整的业务逻辑单元,不可再分。
比如:银行账户转账,从A账户向B账户转账10000.需要执行两条update语句:
update t_act set balance = balance - 10000 where actno = 'act-001';
update t_act set balance = balance + 10000 where actno = 'act-002';
以上两条DML语句必须同时成功,或者同时失败,不允许出现一条成功,一条失败。
要想保证以上的两条DML语句同时成功或者同时失败,那么就需要使用数据库的“事务机制”。
2.和事务相关的语句只有:DML语句。(insert delete update)
为什么?因为它们这三个语句都是和数据库表当中的“数据”相关的。
事务的存在是为了保证数据的完整性,安全性。
3.假设所有的业务都能使用1条DML语句搞定,还需要事务机制吗?
不需要事务。
但实际情况不是这样的,通常一个“事儿(事务【业务】)”需要多条DML语句共同联合完成。
4.事务的特性
A: 原子性:事务是最小的工作单元,不可再分。
C: 一致性:事务必须保证多条DML语句同时成功或者同时失败。
I:隔离性:事务A与事务B之间具有隔离。
D:持久性:持久性说的是最终数据必须持久化到硬盘文件中,事务才算成功的结束。
5.事务之间的隔离性
事务隔离性存在隔离级别,理论上隔离级别包括4个:
第一级别:读未提交
对方事务还没有提交,我们当前事务可以读取到对方未提交的数据。
读未提交存在脏读现象:表示读到了脏的数据。
第二级别:读已提交
对方事务提交之后的数据我方可以读取到。
这种隔离级别解决了: 脏读现象没有了。
读已提交存在的问题是:不可重复读。
第三级别:可重复读
这种隔离级别解决了:不可重复读问题。
这种隔离级别存在的问题是:读取到的数据是幻象。
第四级别:序列化读/串行化读
解决了所有问题。
效率低。需要事务排队。
oracle数据库默认的隔离级别是:读已提交。
mysql数据库默认的隔离级别是:可重复读。
五、索引
1.什么是索引?有什么用?
索引就相当于一本书的目录,通过目录可以快速的找到对应的资源。
在数据库方面,查询一张表的时候有两种检索方式:
第一种方式:全表扫描
第二种方式:根据索引检索(效率很高)
索引为什么可以提高检索效率呢?
其实最根本的原理是缩小了扫描的范围。
索引虽然可以提高检索效率,但是不能随意的添加索引,因为索引也是数据库当中的对象,也需要数据库不断的维护。是有维护成本的。比如,表中的数据经常被修改,这样就不适合添加索引,因为数据一旦修改,索引需要重新排序,进行维护。
添加索引是给某一个字段,或者说某些字段添加索引。
select ename,sal from emp where ename = 'SMITH';
当ename字段上没有添加索引的时候,以上sql语句会进行全表扫描,扫描ename字段中所有的值。
当ename字段上添加索引的时候,以上sql语句会根据索引扫描,快速定位。
2.怎么创建索引对象?怎么删除索引对象?
创建索引对象:
create index 索引名称 on 表名(字段名);
删除索引对象:
drop index 索引名称 on 表名;
3.什么时候考虑给字段添加索引?(满足什么条件)
* 数据量庞大。(根据客户的需求,根据线上的环境)
* 该字段很少的DML操作。(因为字段进行修改操作,索引也需要维护)
* 该字段经常出现在where子句中。(经常根据哪个字段查询)
4.索引底层采用的数据结构是:B + Tree
5.索引的实现原理?
通过B Tree缩小扫描范围,底层索引进行了排序,分区,索引会携带数据在表中的“物理地址”,最终通过索引检索到数据之后,获取到关联的物理地址,通过物理地址定位表中的数据,效率是最高的。
select ename from emp where ename = 'SMITH';
通过索引转换为:
select ename from emp where 物理地址 = 0x3;
6.索引的分类?
单一索引:给单个字段添加索引
复合索引: 给多个字段联合起来添加1个索引
主键索引:主键上会自动添加索引
唯一索引:有unique约束的字段上会自动添加索引
7.索引什么时候失效?
select ename from emp where ename like '%A%';
模糊查询的时候,第一个通配符使用的是%,这个时候索引是失效的。
六、视图(view)
1.什么是视图?
站在不同的角度去看到数据。(同一张表的数据,通过不同的角度去看待)
2.怎么创建视图?怎么删除视图?
create view myview as select empno,ename from emp;
drop view myview;
注意:只有DQL语句才能以视图对象的方式创建出来。
3.对视图进行增删改查,会影响到原表数据。
update myview1 set ename='hehe',sal=1 where empno = 7369; // 通过视图修改原表数据
delete from myview1 where empno = 7369; // 通过视图删除原表数据。
4.视图的作用?
视图可以隐藏表的实现细节。数据安全,查询快。