MySQL
文章目录
1、概述
什么是数据库?什么是数据库管理系统?什么是SQL?它们之间的关系是什么?
-
数据库:
英文单词DataBase,简称DB,按照一定格式存储数据的一些文件的组合。
顾名思义:存储数据的仓库,实际上就是一堆文件。这些文件中储存了具有特定格式的数据。
-
数据库管理系统:
DataBaseManagement,简称DBMS。
数据库管理系统是专门来管理数据库中的数据的,数据库管理系统可以对数据库当中的数据进行增删改查。
常见的数据库管理系统:
MySQ、Oracle、MS SQL sever、DB2、Sybase等…
-
SQL:结构化查询语言
程序员需要学习SQL语句,程序员需要通过编写SQL语句,然后DBMS负责执行SQL语句,最终来完成数据库中的数据的增删改查操作。
SQL是一套标准,程序员主要学习的就是SQL语句,这个SQL语句可以在大多DBMS中使用。
-
三者之间的关系?
DBMS --执行–> SQL --操作–> DB
2、MySQL常用命令
2.1注意
1.SQL命令以英文分号结尾;
2.SQL不见分号 “ ; ”不会执行命令,分号 “ ; ” 代表语句结束!;
3.SQL语句不区分大小写;
4.在所有数据库中,字符串用单引号 “ ’ ’ ” 括起,单引号是标准的,双引号在Oracle数据库中无法使用;
5.字段名可以使用数学表达式 + - * /;
6.多个字段(查询、排序)分号 “ ; ” 隔开;
7.多个字段排序(第一个条件排序起主导作用,当第一个条件元素相等时,才会考虑第二个条件)
命令 | 作用 |
---|---|
mysql -u用户名 -p密码 | 登录MySQL |
exit | 退出数据库 |
show databases; | 查询MySQL中的所有数据库 |
use 数据库名; | 使用某个数据库 |
create database 数据库名; | 创建数据库 |
show tables; | 查询数据库下的所有表 |
source 文件路径 | 导入数据 |
select * from 表名 | 查询表的所有字段数据(缺点可读性低,效率低,不建议使用) |
desc 表名; | 查询表的结构 |
select database(); | 查看当前正在使用的数据库 |
select 字段名 from 表名; | 查询一个字段的数据 |
select 字段名,字段名,… from 表名; | 查询两个或多个字段以逗号 “ , ” 隔开(用于查询所有字段数据,可读性高,效率高) |
select 字段名 as 别名 from 表名; | 查询的字段取别名(通过 as 关键字修改,只是查询时显示别名,实际字段名并未修改) |
select 字段名 from 表名 where 字段名 条件 | 条件查询 |
select 字段名 from 表名 where 字段名 like ‘%条件%’; | 模糊搜索 |
select 字段名 from 表名 where 字段名 like ‘条件%’; | 以“条件”结尾的 进行模糊搜索 |
select 字段名 from 表名 where 字段名 like ‘%条件’; | 以“条件”开始的 进行模糊搜索 |
select 字段名 from 表名 where 字段名 like ‘%_条件%’; | 以第二个元素开始,包含“条件”的模糊搜索(以第三个元素开始的再加下划线,以此类推) |
select 字段名 from 表名 order by 字段名; | 排序(默认升序)关键字(order by) |
select 字段名 from 表名 order by 字段名 desc; | 指定降序 关键字(order by 、desc) |
select 字段名 from 表名 order by 字段名 asc; | 指定升序 关键字(order by、asc) |
select 字段名 from 表名 order by 字段名 asc,字段 asc; | 多字段排序(以分号给开字段后加排序条件(desc、asc))详细查看 2.1注意:7. |
select distinct 字段名 from 表名; | 关键字(distinct)去除字段元素数据重复内容 |
drop table 表名; | 删除表 |
select version(); | 查看数据库的版本号 |
\c | 结束一条命令的终止 |
show engines \G | 查看MySQL存储引擎 |
2.2条件运算符
条件查询关键字where;
注意:在数据库中null不能使用等号来进行比较运算。需要使用 is null 因为数据库中的null表示什么都没有,他不是一个值;
and和or同时出现时and优先级比or高,如果想要or先执行,需要加小括号“ () ” 在实际开发中不确定优先级都可加入小括号“ () ”;
运算符 | 作用 |
---|---|
= | 等于 |
<>或!= | 不等于 |
<= | 小于等于 |
>= | 大于等于 |
< | 小于 |
> | 大于 |
and | 与 |
between | between … and… 选取介于两个值之间的数据范围(必须左小右大) |
is | 判断类型是否为null |
or | 或 |
in | 相当于多个or(用法:where in (字段,字段,…) ) |
not | 非(用于is 或 in 中) |
like | 模糊查询 |
% | 匹配任意多个字符 |
_ | 匹配任意一个字符 |
3、表
数据库中最基本的单元是表:table
什么是表table?为什么用表来存储数据?
/-----------------------------------------------------------------------
name(列:字段) sex(列:字段) age(列:字段)
/-----------------------------------------------------------------------
张三 男 23 -------->行(记录)
李四 女 20 -------->行(记录)
王五 男 25 -------->行(记录)
/------------------------------------------------------------------------
数据库当中是以表格的形式表示数据的。
因为表比较直观。
任何一张表都有行和列:
行(row):被称为数据/记录
列(column):被称为字段。
姓名字段、性别字段、年龄字段
每个字段都有:字段名、数据类型、约束等属性。
4、关于SQL语句的分类的
分为:
- DQL:
数据查询语句(凡是带有select关键字的都是查询语句,只查询不对数据做任何修改)
select…
-
DML:
数据操作语言(凡是对表当中的数据进行增删改查的都是DML)
insert delete update
insert 增
delete 删
update 改
操作表中的数据 -
DDL:
数据定义语言
凡是带create、drop、alter的都是DDL。
DDL操作的是表的结构,是表的数据
create 新建
drop 删除
alter 修改
与DML不同,DDL操作的是表的结构,DML是表的数据 -
TCL:
事务控制语言
包括:
事务的提交:commit
事务的回滚:rollback -
DCL:
数据的控制语言
例如:授权grant、撤销权限revoke…
5、数据处理函数
1.数据处理函数又被称为单行处理函数,单行处理函数的特点:一个输入对应一个输出。
和单行处理函数相对应的是:多行处理函数(特点:多个输入对应一个输出!)
2.单行处理函数常见有哪些
3.注意:
3.1在所有数据库中只要有null参与的数学运算,最终结果都是null,为了避免这个现象,需要使用ifnull函数。
关键字 | 作用 | 用法(可结合实际需求修改SQL组合使用) |
---|---|---|
lower | 转换小写 | select lower(字段名) from 表名; |
upper | 转换大写 | select upper(字段名) from 表名; |
substr | 取子串(截取元素内容) | select substr(字段名,起始位置,截取长度) from 表名; 截取下标最小值是“1” |
length | 取长度 | select length(字段名) from emp; |
trim | 去空格 | |
str_to_date | 将字符串转换为日期 | |
date_format | 格式化日期 | |
concat | 字符串拼接 | select concat(字段名,字段名) from 表名; |
round | 四舍五入,参数:保留十位(-1)、保留整数(0)、保留一位小数(1)保留多位小数以此类推,详细内容百度 | select round(字段名,参数) from 表名; |
ifnull | 是空处理函数,专门处理空的 | select ifnull(字段名,null替换值) from 表名; (如果字段的数据有null”替换值“将null替换为别的参数 ) |
6、分组函数(多行处理函数)
多行处理函数特点:输入多行,最终输出一行。
1.注意:
1.分组函数在使用时必须先进行分组,然后才能使用,如果没有对数据进行分组,整张表默认为一组。
2.分组函数自动忽略null,不需要自己手动处理。
3.count(具体字段):表示统计该字段下所有不为null的元素的总数。
count(*):统计表的总行数。(只要有一行数据count则++)。
因为每一行的数据都不可能为null,一行数据中的一列不为null,则这行数据有效。
4.分组函数不能够直接使用在where子句中。
5.所有分组函数可以结合使用。
关键字 | 作用 | 用法**(可结合实际需求修改SQL组合使用)** |
---|---|---|
count | 计数 | select count(字段名) from 表名; |
sum | 求和 | select sum(字段名) from 表名; |
avg | 平均值 | select avg(字段名) from 表名; |
max | 最大值 | select max(字段名) from 表名; |
min | 最小值 | select min(字段名) from 表名; |
7、分组查询
1.什么是分组查询?
在实际的应用中,可能有这样的需求,需要进一步分组,然后对每一个分组的数据进行操作。
这个时候我们需要使用分组查询
2.单表分组排序查询
关键字顺序不能改变
1.from
2.where
3.group by
4.select
5.order by
示例:
select
job,sum(sal)
from
emp
group by
job;
以上这个语句的执行顺序
先从emp表中查询薮据-根据job字段进行分组
然后对每一组的数据进行surn (sal)
结论 :
在一条select语句当中,如果有group by语句的话,select后面只能跟:参与分组的字段,以及分组函数。其他一律不能跟
关键字 | 作用 | 使用(结合实际需求修改SQL组合使用) |
---|---|---|
group by | 对字段进行分组 | select 字段名 分组函数(字段名) from 表名 group by 字段名; |
having | 对已分组的字段再次过滤(必须和group by联合使用) | select 字段名 分组函数(字段名) from 表名 group by 字段名 having …; |
优化策略:where和having,优先选择where,where无法实现需求再选择having。
8、连接查询
关键字 | 作用 |
---|---|
inner | 内连接(可省略书写) |
outer | 外连接(可省略书写) |
left | 左外连接 |
right | 右外连接 |
join | 表的表连接 |
on | 连接条件 |
使用示例:
select
字段,字段
from
表
连接关键字
表
on
字段 条件运算符 字段;
1.什么是连接查询?
从一张表中单独查询,称为单表查询。
A表和B表联合查询数据,从A表中取出员工名字,从B表中取部门名字。这种跨表查询,多张表联合起来查询数据,被称为连接查询。
2.连接查询分类?
根据语法的年代分类:
SQL92:1992年时出现的语法
SQL99:1999年时出现的语法
根据表连接方式分类:
内连接:(内连接的特点:完成能够匹配上条件的数据查询出来)
等值连接
非值连接
自连接
外连接:
左外连接(左连接)
右外连接(右连接)
全连接:
3.笛卡尔积现象
当两张表进行连接查询,没有任何条件限制的时候,最终查询结果条数,是两张表条数的乘机,这种现象被称为:笛卡尔积现象。(笛卡尔发现的,是一种数学现象)
4.怎么避免笛卡尔积现象
连接时加入条件,满足这个条件的记录筛选出来!
示例:
select
ename,dname -------------------------------------->>>字段名
from
emp,dept --------------------------------------------->>>表名
where
emp.deptno = dept.deptno;------------------->>>表名.字段名筛选条件
5.注意
通过笛卡尔积现象得出,表的连接次数越多效率越低,尽量避免多表连接次数
6.内连接之等值连接
释义:条件是等量关系,被称为等值连接
案例:查询每个员工所在部门名称,显示员工名和部门名?enmp e和dept d表进行连接-条件是: e.deptno = d.deptno
92语法:
select
e.ename,d.dname
from
emp e,dept d
where
e.deptno = d.deptno;
**SQL92缺点:**结构不清晰,连接表的条件,和后期进一步筛选的条件,放到了where后面。
99语法:
select
e.ename,d.dname------------------>>>字段
from
emp e------------------------------------>>>表
inner join----------------------------------->>>连接关键字(inner外连接关键字 (可以省略) 带着inner可读性更好)
dept d----------------------------------->>>表
on-------------------------------------------->>>连接条件关键字
e.deptno = d.deptno;------------->>>条件是等量关系,被称为等值连接
where
…------------------------------------>>>进一步筛选条件
**SQL99优点:**表的连接条件是独立的,连接之后,如果还需要进一步筛选,再继续添加where。
#emp表
+-------+--------+-----------+------+------------+---------+---------+--------+
| 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表
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
7.内连接之非等值连接
释义:条件不是一个等量关系,称为非等值连接
案例:找出每个员工的薪资等绒,要求显示员工名、薪资、薪资等级?
select
e.ename,e.sal,s.grade
from
emp e
inner join
salgrade s
on
e.sal between s.losal and s.hisal; #---------条件:满足losal字段 或 hisal之间
#查询结果
+--------+---------+-------+
| ename | sal | grade |
+--------+---------+-------+
| SMITH | 800.00 | 1 |
| ALLEN | 1600.00 | 3 |
| WARD | 1250.00 | 2 |
| JONES | 2975.00 | 4 |
| MARTIN | 1250.00 | 2 |
| BLAKE | 2850.00 | 4 |
| CLARK | 2450.00 | 4 |
| SCOTT | 3000.00 | 4 |
| KING | 5000.00 | 5 |
| TURNER | 1500.00 | 3 |
| ADAMS | 1100.00 | 1 |
| JAMES | 950.00 | 1 |
| FORD | 3000.00 | 4 |
| MILLER | 1300.00 | 2 |
+--------+---------+-------+
#emp表
+-------+--------+-----------+------+------------+---------+---------+--------+
| 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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
#salgrade表
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
+-------+-------+-------+
8.内连接之自连接
技巧:把一张表看成两张表
案例:案例:查询员工的上级领导,要求显示员工名和对应的领导名?
select
a.ename as '员工',b.ename as '领导'
from
emp a
join
emp b
on
a.mgr = b.empno; # 员工的领导编号 = 领导的员工号
#查询结果---13条数据 KING无上级领导
+--------+--------+
| 员工 | 领导 |
+--------+--------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+--------+--------+
#emp表
mysql> select empno,ename,mgr from emp;
+-------+--------+------+
| empno | ename | mgr |
+-------+--------+------+
| 7369 | SMITH | 7902 |
| 7499 | ALLEN | 7698 |
| 7521 | WARD | 7698 |
| 7566 | JONES | 7839 |
| 7654 | MARTIN | 7698 |
| 7698 | BLAKE | 7839 |
| 7782 | CLARK | 7839 |
| 7788 | SCOTT | 7566 |
| 7839 | KING | NULL |
| 7844 | TURNER | 7698 |
| 7876 | ADAMS | 7788 |
| 7900 | JAMES | 7698 |
| 7902 | FORD | 7566 |
| 7934 | MILLER | 7782 |
+-------+--------+------+
9.外连接(右外连接)
关键字right代表着将join关键字右边的这张表看成主表,主要是为了将这张表的数据查询出来,捎带着关联查询左边的的表(左外连接写法相同right替换left成左外连接);
在外连接中,两张表连接,产生了主次关系。
带有right的是右外连接,又叫做右连接。
带有left的是左外连接,又叫做左连接。
任何一个右连接都有左连接的写法。
任何一个左连接都有右连接的写法。
显示员工名和 “所有部门名称!”,从员工表查询员工的部门编号,获取部门表的部门的名称
select
e.ename,d.dname
from
emp e
right join
dept d
on
e.deptno = d.deptno;
#查询结果
+--------+------------+
| ename | dname |
+--------+------------+
| MILLER | ACCOUNTING |
| KING | ACCOUNTING |
| CLARK | ACCOUNTING |
| FORD | RESEARCH |
| ADAMS | RESEARCH |
| SCOTT | RESEARCH |
| JONES | RESEARCH |
| SMITH | RESEARCH |
| JAMES | SALES |
| TURNER | SALES |
| BLAKE | SALES |
| MARTIN | SALES |
| WARD | SALES |
| ALLEN | SALES |
| NULL | OPERATIONS |
+--------+------------+
#emp表
+-------+--------+-----------+------+------------+---------+---------+--------+
| 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表
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
案例:查询每个员工的上级领导,要求显示所有员工的名字和领导名?
select
e.ename as '员工',d.ename as '领导'
from
emp e
left join
emp d
on
e.mgr = d.empno;
#查询结果
+--------+--------+
| 员工 | 领导 |
+--------+--------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| KING | NULL |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+--------+--------+
#emp表
mysql> select empno,ename,mgr from emp;
+-------+--------+------+
| empno | ename | mgr |
+-------+--------+------+
| 7369 | SMITH | 7902 |
| 7499 | ALLEN | 7698 |
| 7521 | WARD | 7698 |
| 7566 | JONES | 7839 |
| 7654 | MARTIN | 7698 |
| 7698 | BLAKE | 7839 |
| 7782 | CLARK | 7839 |
| 7788 | SCOTT | 7566 |
| 7839 | KING | NULL |
| 7844 | TURNER | 7698 |
| 7876 | ADAMS | 7788 |
| 7900 | JAMES | 7698 |
| 7902 | FORD | 7566 |
| 7934 | MILLER | 7782 |
+-------+--------+------+
10.多表联查
1.一条SQL语句中内连接和外连接可以混合使用
#语法
select
...
from
A
join
B
on
A和B的连接条件
join
C
on
A和C的连接条件
join
......
#一条SQL语句中内连接和外连接可以混合使用
案例1:找出每个员工的部门名称以及工资等级,要求显示员工名、部门名、薪资、薪资等级?
案例2:找出每个员工的部门名称 以及工资等级 还要上级领导,要求显示员工名、部门名、薪资、薪资等级 上级领导
#案例一
select
a.ename,a.sal as '员工',d.dname as '部门名',s.grade as '工资等级'
from
emp a
join
dept d
on
a.deptno = d.deptno--------------------->>>#emp a与dept d匹配部门编号 查询输出显示d.dname字段
join
salgrade s
on
a.sal between s.losal and s.hisal;------>>>#emp a与salgrade s判断工资等级是否在losal与hisal之间 查询输出显示s.grade字段
#查询结果
+--------+---------+------------+--------------+
| ename | 员工 | 部门名 | 工资等级 |
+--------+---------+------------+--------------+
| SMITH | 800.00 | RESEARCH | 1 |
| ALLEN | 1600.00 | SALES | 3 |
| WARD | 1250.00 | SALES | 2 |
| JONES | 2975.00 | RESEARCH | 4 |
| MARTIN | 1250.00 | SALES | 2 |
| BLAKE | 2850.00 | SALES | 4 |
| CLARK | 2450.00 | ACCOUNTING | 4 |
| SCOTT | 3000.00 | RESEARCH | 4 |
| KING | 5000.00 | ACCOUNTING | 5 |
| TURNER | 1500.00 | SALES | 3 |
| ADAMS | 1100.00 | RESEARCH | 1 |
| JAMES | 950.00 | SALES | 1 |
| FORD | 3000.00 | RESEARCH | 4 |
| MILLER | 1300.00 | ACCOUNTING | 2 |
+--------+---------+------------+--------------+
#案例二
select
a.ename,a.sal as '员工',d.dname as '部门名',s.grade as '工资等级',l.ename as '领导'
from
emp a
join-------------------------------------------->>>#内连接
dept d
on
a.deptno = d.deptno
join
salgrade s
on
a.sal between s.losal and s.hisal
left join--------------------------------------->>>#左外连接
emp l
on
a.mgr = l.empno;
+--------+---------+------------+--------------+--------+
| ename | 员工 | 部门名 | 工资等级 | 领导 |
+--------+---------+------------+--------------+--------+
| SMITH | 800.00 | RESEARCH | 1 | FORD |
| ALLEN | 1600.00 | SALES | 3 | BLAKE |
| WARD | 1250.00 | SALES | 2 | BLAKE |
| JONES | 2975.00 | RESEARCH | 4 | KING |
| MARTIN | 1250.00 | SALES | 2 | BLAKE |
| BLAKE | 2850.00 | SALES | 4 | KING |
| CLARK | 2450.00 | ACCOUNTING | 4 | KING |
| SCOTT | 3000.00 | RESEARCH | 4 | JONES |
| KING | 5000.00 | ACCOUNTING | 5 | NULL |
| TURNER | 1500.00 | SALES | 3 | BLAKE |
| ADAMS | 1100.00 | RESEARCH | 1 | SCOTT |
| JAMES | 950.00 | SALES | 1 | BLAKE |
| FORD | 3000.00 | RESEARCH | 4 | JONES |
| MILLER | 1300.00 | ACCOUNTING | 2 | CLARK |
+--------+---------+------------+--------------+--------+
#emp表 即 a l
+-------+--------+-----------+------+------------+---------+---------+--------+
| 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 |
+--------+------------+----------+
#salgrade表 s
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
+-------+-------+-------+
9、子查询
1.什么是子查询
select语句中嵌套select语句,被嵌套的select语句被称为子查询。
2.子查询可以在哪里出现?
select
…(select)
from
…(select)
where
…(select)
3.where子句中的子查询
案例:找出比最低工资高的员工姓名和工资?
#实现思路:
#第一步
select min(sal) from emp;
+----------+
| min(sal) |
+----------+
| 800.00 |
+----------+
#第二步
select ename,sal from emp where sal > 800;
+--------+---------+
| ename | sal |
+--------+---------+
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| JONES | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| TURNER | 1500.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| FORD | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
#第三步 合并sql语句
select ename,sal from emp where sal > (select min(sal) from emp);
+--------+---------+
| ename | sal |
+--------+---------+
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| JONES | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| TURNER | 1500.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| FORD | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
4.from子句中的子查询
**注意:**from后面的子查询,可以将子查询的查询结果看做一张临时的表。(技巧)
**案例:**找出每个岗位的平均工资的薪资等级
#第一步:找出每个岗位的平均工资(按照岗位分组求平均值)
select job,avg(sal) from emp group by job;
+-----------+-------------+
| job | avg(sal) |
+-----------+-------------+
| CLERK | 1037.500000 |
| SALESMAN | 1400.000000 |
| MANAGER | 2758.333333 |
| ANALYST | 3000.000000 |
| PRESIDENT | 5000.000000 |
+-----------+-------------+ #t表
#第二步把以上的结果就当作一张真实存在的表t
select * from salgrade;
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
+-------+-------+-------+ #s表
#第三步t表和s表进行表连接,条件:t表avg(sal) between s.losal and hisal;
select
t.*,s.grade
from
(select job,avg(sal) as avgsal from emp group by job) t
join
salgrade s
on
t.avgsal between s.losal and s.hisal;
+-----------+-------------+-------+
| job | avgsal | grade |
+-----------+-------------+-------+
| CLERK | 1037.500000 | 1 |
| SALESMAN | 1400.000000 | 2 |
| MANAGER | 2758.333333 | 4 |
| ANALYST | 3000.000000 | 4 |
| PRESIDENT | 5000.000000 | 5 |
+-----------+-------------+-------+
10、union合并查询结果集
uniou的效率要高一些,对于连接来说,每一次连接新表,则匹配的次数满足笛卡尔积,成倍的翻…,但是union可以减少匹配次数,在减少匹配次数的情况下,还可以完成两个结果集的拼接。
A 连接 B 连接 C
A:10条记录
B:10条记录
C:10条记录
匹配次数是:1000
A 连接 B 一个结果:10 * 10 = 100
A 连接 C 一个结果:10 * 10 = 100
使用union的话是:100 + 100 = 200 次 (union把乘法变加法运算)
1.注意:
**1.1、**union在进行结果集合并的时候要求两个结果集的列数相同
select ename,job from emp where job = ‘manager’
union
select ename,sal from emp where job = ‘salesman’;
**1.2、**MySQL:结果集合并时列和列的数据类型可以不一致,Oracle:不支持,报SQL语句错误
**案例:**查询工作岗位是MANAGER和SALE.SMAN的员工?
#语法:关键字 unino
select
ename,job
from
emp
where job = 'manager'
union#-------------------------------->>>合并查询结果集
select
ename,job
from
emp
where job = 'salesman';
#emp表
+-------+--------+-----------+------+------------+---------+---------+--------+
| 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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
11、limit
1.limint作用
将查询的结果集的一部分取出,通常使用在分页查询当中。
百度默认:一个页面显示十条记录。
分页的作用是为了提高用户的体验,因为一次查询出所有结果在一个页面中,用户体验差。
2.limit的使用
**完整语句:**limit startIndex,length
startIndex 下标的起始位置 length 取出的长度
**缺省语句:**limit length
length 取出的长度
**案例:**按照薪资降序,取出前五名
3.注意
MySQL当中 limit 在 order by 之后执行
#语法 关键字limint
select
ename,sal
from
emp
order by
sal desc
limit 0,3;
#查询结果
+-------+---------+
| ename | sal |
+-------+---------+
| KING | 5000.00 |
| FORD | 3000.00 |
| SCOTT | 3000.00 |
+-------+---------+
select
ename,sal
from
emp
order by
sal desc
limit 5;
#查询结果
+-------+---------+
| ename | sal |
+-------+---------+
| KING | 5000.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
#emp表
+-------+--------+-----------+------+------------+---------+---------+--------+
| 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 |
+-------+--------+-----------+------+------------+---------+---------+--------+
4.分页
每页显示3条记录
第1页:limit 0,3 [0,1,2]
第2页:limit 3,3 [3,4,5]
第3页:limit 6,3 [6,7,8]
第4页:limit 9,3 [9,10,11]
每页显示pagesize条记录
第pageNo页: limit (pageNo - 1) * pagesize , pagesize
public static void main (string [] args) {
//用户提交过来一个页码,以及每页显示的记录条数
int pageNo = 5; //第5页
int pagesize = 10; //每页显示10条
int startIndex = (pageNo - 1) * pagesize;
//形成sql语句
string sql = "select ...limit " + startIndex + ", " + pagesize;
}
记公式:
limit (pageNo-1) * pagesize , pagesize
12、DQL查询语句的总结
#执行顺序改变
select
...
from #------>>查询
...
where #------>>条件
...
group by #-->>分组
...
having #------>>过滤
...
order by #-->>排序
...
limit #------>>取段
...
13、表的创建(建表)
1.建表的语法格式:
建表属于(DDL语句,DDL语句包括:create、drop、alter)
create table 表名 (字段名 数据类型,字段名 数据类型,......);
#建议分行隔开书写 可读性高 更直观
create table 表名 (字段名1 数据类型,
字段名2 数据类型,
字段名3 数据类型,
);
**表名:**建议以 t_ 或 tbl_ 开始,可读性高 见名知意
**字段名:**见名知意
表名和字段名都属于标识符
注意:
数据库中的命名规范:
所有的标识符都是小写,单词和单词之间用 " _ " 分隔衔接。
2.MySQL常用数据类型
数据类型 | |
---|---|
varchar | 可变长度字符串(比较智能,节省空间,会根据实际数据长度分配空间) |
char | 定长字符串(不管实际数据长度,分配固定空间存储数据) |
int | 数字中整数型 |
bigint | 数字中长整型 |
float | 单精度浮点型 |
double | 双精度浮点型 |
date | 短日期类型 |
datetime | 长日期类型 |
clob | 字符大对象(最多存储4g字符串) |
blob | 二进制大对象(专门存储图片、声音、视频、等流媒体数据) |
**注意:**往BLOB类型类型的字段上插入数据的时候,例如插入图片或视频等需要使用IO流
#模拟建表
#t_movie电影表
#编号 名字 描述信息 上映日期 时常 海报 类型
no(bigint) name(varchar) description(clob) palytime(date) time(double) image(blob) type(char)
-----------------------------------------------------------------------------------------------------------
1000 哪吒 ............... 2021-12-1 2.6 ...... '1'
1001 白蛇 ............... 2021-12-1 2.6 ...... '2'
..........
3.创建一个学生表
#学号、姓名、年龄、邮箱地址
create table t_student(
no int,
name varchar(32),
sex char(1),
age int(3),
email varchar(255)
);
4.删除表
drop table 表名; #当这张表不存在时会报错
drop table if exists 表名; #判断这张表是否存在 存在则删除
5.插入数据insert(DML)
语法格式:
insert into 表名 (字段名1,字段名2,...) values(值1,值2,....)
注意:
1.字段名和值要一一对应,数量对应,数据类型对应
2.insert一旦执行成功,必然会多一条记录没有给其他字段指定值的话,默认值null
**default:**字段的值添加指定默认值
create table t_student(
no int,
name varchar(32),
sex char(1) default '男',#---->>>添加指定默认值
age int(3),
email varchar(255)
);
insert语句中的“字段名”可以省略 但是所有值都必须对应写上
语法格式:
insert into 表名 values(值1,值2,....)
#例如t_student表插入数据
insert into t_student values(03,'莎迪','女',23,'15302@qq.com');
+------+--------+------+------+-------------------+
| no | name | sex | age | email |
+------+--------+------+------+-------------------+
| 1 | 张三 | 男 | 21 | 1530569405 |
| 2 | 李四 | 男 | 21 | 1530569405@qq.com |
| 3 | 莎迪 | 女 | 23 | 15302@qq.com |
+------+--------+------+------+-------------------+
6.insert插入日期
函数 | 作用 | 语法格式 |
---|---|---|
str_to_date | 将字符串varchar类型转换成date类型 | str_to_date(‘字符串日期’,‘日期格式’) |
date_format | 将date类型转换成具有一定格式的varchar字符串类型 | date_format(日期类型数据,‘日期格式’) |
MySQL的日期格式:
标识符 | 含义 |
---|---|
%Y | 年 |
%m | 月 |
%d | 日 |
%h | 时 |
%i | 分 |
%s | 秒 |
#创建员工生日表
create table t_user(
id int,
name varchar(32),
birth date
);
create table t_user(
id int,
name varchar(32),
birth char(10) #-------->>>生日也可以使用char类型
);
#t_user表结构
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(32) | YES | | NULL | |
| birth | date | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
#插入date日期str_to_date()函数使用方法
insert into t_user (id,name,birth) values(06,'兰熹',str_to_date('11-12-1992','%d-%m-%Y'));
+------+-----------+------------+
| id | name | birth |
+------+-----------+------------+
| 1 | 张育源 | 1990-10-01 |
| 2 | 李海涛 | 1990-12-12 |
| 3 | DJ磊 | 1990-12-12 |
| 4 | 罗翔 | 1990-10-01 |
| 5 | 秦家浩 | 1992-12-11 |
+------+-----------+------------+
**str_to_date()**函数可以把字符串varchar转换成date类型数据,通常使用在插入insert方面,因为插入的时候需要一个日期类型的数据,需要通过该函数将字符串转换成date。
如果提供的日期字符串是这个格式,str_to_date()函数就不需要了
%Y-%m-%n ---------- >>>‘年-月-日’ 如下
insert into t_user (id,name,birth) values(07,'曾展','1998-12-21');
+------+-----------+------------+
| id | name | birth |
+------+-----------+------------+
| 1 | 张育源 | 1990-10-01 |
| 2 | 李海涛 | 1990-12-12 |
| 3 | DJ磊 | 1990-12-12 |
| 4 | 罗翔 | 1990-10-01 |
| 5 | 秦家浩 | 1992-12-11 |
| 6 | 兰熹 | 1992-12-11 |
| 7 | 曾展 | 1998-12-21 |
+------+-----------+------------+
查询的时候以某个特定的日期格式展示
date_format()
这个函数可以将日期类型转换成特定格式的字符串。
select name,date_format(birth,'%d*%Y*%m') as '特定格式日期' from t_user;
#查询显示结果
+-----------+--------------------+
| name | 特定格式日期 |
+-----------+--------------------+
| 张育源 | 01*1990*10 |
| 李海涛 | 12*1990*12 |
| DJ磊 | 12*1990*12 |
| 罗翔 | 01*1990*10 |
| 秦家浩 | 11*1992*12 |
| 兰熹 | 11*1992*12 |
| 曾展 | 21*1998*12 |
+-----------+--------------------+
#date_format函数怎么用?
#date fornat(日期类型数据,·日期格式)
#这个函数通常使用在查询日期方面。设置展示的日期格式。
select id,name,birth 'mysql默认日期格式' from t_user;
+------+-----------+-------------------------+
| id | name | mysql默认日期格式 |
+------+-----------+-------------------------+
| 1 | 张育源 | 1990-10-01 |
| 2 | 李海涛 | 1990-12-12 |
| 3 | DJ磊 | 1990-12-12 |
| 4 | 罗翔 | 1990-10-01 |
| 5 | 秦家浩 | 1992-12-11 |
| 6 | 兰熹 | 1992-12-11 |
| 7 | 曾展 | 1998-12-21 |
+------+-----------+-------------------------+
#以上的sQL语句实际上是进行了默认的日期格式化,自动将薇据库中的date类型转换成varahar类型。
#并且采用的格式是nmysql默认的日期格式:'%Y一%m-%d'
7.date和datetime两个类型的区别
date:短日期类型只包括 年,月,日 信息
datetime:长日期类型包括 年,月,日,时,分,秒 信息
MySQL短日期默认格式:%Y-%m-%d
MySQL长日期默认格式:%Y-%m-%d %h:%i:%s
create table t_usern(
id int,
name varchar(5),
birth date,
create_time datetime
)
#id是整数
#nane是字符串birth是短日期
#create_time是这条记录的创建时间:长日期类型
insert into t_usern (id,name,birth,create_time) values(01,'亚瑟摩根','1866-7-14','1899-11-2 06:49:59');
+------+--------------+------------+---------------------+
| id | name | birth | create_time |
+------+--------------+------------+---------------------+
| 1 | 亚瑟摩根 | 1866-07-14 | 1899-11-02 06:49:59 |
+------+--------------+------------+---------------------+
在MySQL当中怎么获取系统当前时间?
now() 函数,并且获取的时间带有:时分秒信息 是datetime类型的。
insert into t_usern (id,name,birth,create_time) values(01,'玛丽灵顿','1866-7-14',now());
+------+--------------+------------+---------------------+
| id | name | birth | create_time |
+------+--------------+------------+---------------------+
| 1 | 亚瑟摩根 | 1866-07-14 | 1899-11-02 06:49:59 |
| 1 | 玛丽灵顿 | 1866-07-14 | 2021-12-03 19:50:33 |
+------+--------------+------------+---------------------+
8.修改(update)
语法格式:
update 表名 set 字段1 = 值1 , 字段2 = 值2 , 字段3 = 值3 where 条件;
**注意:**没有条件限制会导致所有数据全部更新
update t_usern set id = 2,birth = '1868-05-21' where name = '玛丽灵顿';
+------+--------------+------------+---------------------+
| id | name | birth | create_time |
+------+--------------+------------+---------------------+
| 1 | 亚瑟摩根 | 1866-07-14 | 1899-11-02 06:49:59 |
| 2 | 玛丽灵顿 | 1868-05-21 | 2021-12-03 19:50:33 |
+------+--------------+------------+---------------------+
9.删除数据delete (DML)
语法格式:
delete from 表名 where 条件;
**注意:**没有条件,整张表的数据会全部删除!
delete from t_usern where id = 2;
+------+--------------+------------+---------------------+
| id | name | birth | create_time |
+------+--------------+------------+---------------------+
| 1 | 亚瑟摩根 | 1866-07-14 | 1899-11-02 06:49:59 |
+------+--------------+------------+---------------------+
10.insert插入多条数据
语法格式:
insert into 表名 (字段1,字段2,字段3,…) values(值1,值2,值3,…),(值1,值2,值3,…),(值1,值2,值3,…),…
insert into t_usern (id,name,birth,create_time)
values(2,'玛丽灵顿','1869-05-21',now()),
(3,'艾尔比盖','1868-12-01',now()),
(4,'约翰马斯顿','1867-08-29',now()),
(5,'何阿西','1848-10-31',now()),
(6,'查尔斯','1864-07-27',now());
+------+-----------------+------------+---------------------+
| id | name | birth | create_time |
+------+-----------------+------------+---------------------+
| 1 | 亚瑟摩根 | 1866-07-14 | 1899-11-02 06:49:59 |
| 2 | 玛丽灵顿 | 1869-05-21 | 2021-12-03 20:41:00 |
| 3 | 艾尔比盖 | 1868-12-01 | 2021-12-03 20:41:00 |
| 4 | 约翰马斯顿 | 1867-08-29 | 2021-12-03 20:41:00 |
| 5 | 何阿西 | 1848-10-31 | 2021-12-03 20:41:00 |
| 6 | 查尔斯 | 1864-07-27 | 2021-12-03 20:41:00 |
+------+-----------------+------------+---------------------+
11.快速创建表(了解内容)
语法格式:
create table 表名 as select * from 要复制的表名;
原理:
将查询结果当作一张表新建
这个可以完成表的快速复制
表创建出来,同时表的数据也存在了
12.快速删除表中的数据
**语法:**delete from 表名; -------->>>(这种属于DML操作)
delete 语句删除数据的原理:
表中的数据被删除了,但是这个数据在硬盘上的真实空间不会被释放!
这种删除的却点是:删除效率比较低。
这种删除的优点是:可以回滚数据!
truncate语句删除数据的原理:
这种删除的效率比较高,表被一次截断,物理删除!
缺点:不支持回滚数据。
优点:快速。
**语法:**truncate table 表名; -------->>>(这种属于DDL操作)
14、对表结构的增删改?
什么是对表结构的修政?
添加一个字段,删除一个字段,修改一个字段
对表结构的修改需要使用:alter
属于DDL语句
DDL包括:create drop alter
第一:在实际的开发中,需求一旦确定之后,表一旦设计好之后,很少的进行表结构的修改。因为开发进行中的时候,修改表结构,成本比较高.修改表的结构,对应的java代码就需要进行大量的修改。成本是比较高的。这个责任应该由设计人员来承担!
第二:由于修改表结构的操作很少,所以我们不需要掌握,如果有一天真的要修改表结构,你可以使用工具
修改表结构的操作是不需要写到java程序中的。实际上也不是java程序员的范畴。
15、约束
1.什么是约束
约束对应的英语单词:constraint
在创建表时,可以对表的字段加上一些约束,来保证表中的数据完整性、有效性
约束的作用就是为了保证:表中的数据有效
2.约束包括那些
约束 | |
---|---|
非空约束 | not null |
唯一约束 | unique |
主键约束 | primary key(简称PK) |
外键约束 | foreign key(简称KF) |
检查约束 | check (MySQL不支持,Oracle支持) |
重点学习四个约束:
not null
unique
primary key
foreign key
3.非空约束:not null
非空约束not null 约束的字段不能为null
not null 只有列级约束 没有表级约束
#创建t_vip表
drop table t_vip(
id int,
name varchar(5) not null
);
insert into t_vip (id,name) values(1,'张三');
insert into t_vip (id,name) values(2,'王五');
#插入空name
insert into t_vip (id) values(3);
#插入id值 无name值报错
ERROR 1364 (HY000): Field 'name' doesn't have a default value
#错误1364(HY000):字段“name”没有默认值
sql文件
xxxx.sql这种文件被称为sql脚本文件sql脚本文件中编写了大量的sql语句。
我们执行sql脚本文件的时候,该文件中所有的sql语句会全部执行! 批量的执行SQL语句,可以使用sql脚本文件。
在mysql当中怎么执行sql脚本呢?
mysql> source I:\Mymysql\vip.sql
你在实际的工作中,第一天到了公司,项目经理会给你一个xxx.sql文件,你执行这个脚本文件,你电脑上的薮据库薮据就有了!
4.唯一约束:unique
唯一约束unique的字段不能重复,但可以为null
drop table if exists t_vip;
create table t_vip(
id int,
name varchar(5) unique,
email varchar(17)
);
insert into t_vip (id,name,email) values(1,'张三','153056945@qq.com'),(2,'李四','253059945@qq.com'),(3,'王五','123456945@qq.com');
#插入重复name
insert into t_vip (id,name,email) values(1,'张三','153056945@qq.com');
ERROR 1062 (23000): Duplicate entry '张三' for key 't_vip.name'
#错误1062(23000):重复输入'张三' 钥匙“t_vip.name”的密码
#唯一约束unique的name字段不能重复,但可以为nul
insert into t_vip (id) values(4);
insert into t_vip (id) values(5);
+------+--------+------------------+
| id | name | email |
+------+--------+------------------+
| 1 | 张三 | 153056945@qq.com |
| 2 | 李四 | 253059945@qq.com |
| 3 | 王五 | 123456945@qq.com |
| 4 | NULL | NULL |
| 5 | NULL | NULL |
+------+--------+------------------+
4.1字段联合唯一
新需求:name和email两个字段联合起来具有唯一性!!!!
drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255) unique, # 约束直接添加到列后面的,叫做列级约束。
email varchar(255) unique
);
这张表这样创建是不符合我以上“新需求”的。
这样创建表示:name具有唯一性,email具有唯一性。各自唯一。
以下这样的数据是符合我“新需求”的。
但如果采用以上方式创建表的话,肯定创建失败,因为'zhangsan'和'zhangsan'重复了。
insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(2,'zhangsan','zhangsan@sina.com');
怎么创建这样的表,才能符合新需求呢?
drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255),
email varchar(255),
unique(name,email) # 约束没有添加在列的后面,这种约束被称为表级约束。
);
insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(2,'zhangsan','zhangsan@sina.com');
select * from t_vip;
name和email两个字段联合起来唯一!!!
insert into t_vip(id,name,email) values(3,'zhangsan','zhangsan@sina.com');
ERROR 1062 (23000): Duplicate entry 'zhangsan-zhangsan@sina.com' for key 'name'
什么时候使用表级约束
需要给多个字段联合起来添加某一个约束的时候,需要使用表级约束。
5.unique和not null联合使用
drop table if exists t_vip;
create table t_vip(
id int,
name varchar(5) unique not null
);
desc t_vip;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(5) | NO | PRI | NULL | |
+-------+------------+------+-----+---------+-------+
#在MySQL中,如果一个字段同时间 not null 和 unique约束的话,该字段自动变成主键字段。(Oracle中不一样)
insert into t_vip (id,name) values(1,'张三');
insert into t_vip (id,name) values(2,'张三'); #错误name不能重复
insert into t_vip (id) values(3); #错误name不能为null
6.主键约束:primary key(简称PK)
主键约束相关术语
-
主键约束:就是一种约束
-
主键字段:该字段添加了主键约束,这样的字段叫做:主键字段
-
主键值:主键字段中的每个值叫做:主键值
什么是主键?作用?
主键值是每一行记录的唯一标识符
主键值是每一行记录的身份证号
**注意:**任何一张表都应该有主键,没有主键,表无效
**主键的特征:**not null + unique (唯一,不能重复)
#判断表是否存在 存在则删除
drop table if exists t_vip;
#建表设置id字段主键
create table t_vip(
id int primary key,
name varchar(5) not null
);
#查询表结构
desc t_vip;
#插入数据
insert into t_vip (id,name) values(1,'张三');
insert into t_vip (id,name) values(2,'李四');
insert into t_vip (id,name) values(3,'王五');
select * from t_vip;
#t_vip表结构
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(5) | NO | | NULL | |
+-------+------------+------+-----+---------+-------+
#t_vip表
+----+--------+
| id | name |
+----+--------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
+----+--------+
表级约束主要是给多个字段联合起来添加约束?
drop table if exists t_vip;
# id和name联合起来做主键:复合主键!!!!
create table t_vip(
id int,
name varchar(255),
email varchar(255),
primary key(id,name)
);
insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(1,'lisi','lisi@123.com');
#错误:不能重复
insert into t_vip(id,name,email) values(1,'lisi','lisi@123.com');
ERROR 1062 (23000): Duplicate entry '1-lisi' for key 'PRIMARY'
在实际开发中不建议使用:复合主键。建议使用单一主键!
因为主键值存在的意义就是这行记录的身份证号,只要意义达到即可,单一主键可以做到。
复合主键比较复杂,不建议使用!!!
一个表可以添加两个主键?
drop table if exists t_vip;
create table t_vip(
id int primary key,
name varchar(5) primary key
);
ERROR 1068 (42000): Multiple primary key defined
#错误1068(42000):定义了多个主键
**结论:**一张表,主键约束只能添加一个(主键只能有一个)
主键值建议使用:
建议值类型 | 不建议值类型 |
---|---|
int | varchar |
bigint | |
char |
主键一般都是数字,一般都是定长的
主键除了:
单一主键和符合主键外,还可以这样进行分类
**自然主键:**主键是个自然数和,业务没有关系
**业务主键:**主键值和业务紧密联系,例如拿银行卡作为主键值,这就是业务主键
在实际开发中使用业务主键多,还是使用自然主键多一些?
- 自然主键使用比较多,因为主键只要做到不重复就行,不需要有意义。
- 业务主键不好,因为主键一旦和业务挂钩,那么当业务发生变动的时候,
- 可能会影响到主键值,所以业务主键不建议使用。尽量使用自然主键。
在MySQL当中,有一种机制,可以帮助我们自动维护一个主键值
关键字 | 作用 |
---|---|
auto_increment | 表示自增从1开始 以1递增 |
只有主键才能使用 “auto_increment” 一个自动列,必须将其定义为键
drop table if exists t_vip;
create table t_vip(
id int primary key auto_increment, #auto_increment------>>>表示自增从1开始 以1递增
name varchar(5)
);
insert into t_vip (name) values('张三'),('李四'),('王五'),('赵六'),('李七'),('赵八'),('命名'),('都是'),('但是');
select * from t_vip;
16、外键约束(foreign key,简称FK)
外键约束涉及到的相关术语:
- 外键约束:一种约束(foreign key)
- 外键字段:该字段上添加了外键约束
- 外键值:外键字段当中的每一个值。
外键使用语法格式:
foreign key(外键约束字段) references 要引入的表名(字段);
约束关键字:foreign key
引入表关键字:references
示例在下方的 t_student表中
- 外键约束:一种约束(foreign key)
- 外键字段:该字段上添加了外键约束
- 外键值:外键字段当中的每一个值。
业务背景:
请设计数据库表,来描述“班级和学生”的信息
第一种方案:班级和学生存储在一张表中
#t_student
no(pk) name classno classname
----------------------------------------------------------------------------------
1 jack 100 北京市大兴区亦庄镇第二中学高三1班
2 lucy 100 北京市大兴区亦庄镇第二中学高三1班
3 lilei 100 北京市大兴区亦庄镇第二中学高三1班
4 hanmeimei 100 北京市大兴区亦庄镇第二中学高三1班
5 zhangsan 101 北京市大兴区亦庄镇第二中学高三2班
6 lisi 101 北京市大兴区亦庄镇第二中学高三2班
7 wangwu 101 北京市大兴区亦庄镇第二中学高三2班
8 zhaoliu 101 北京市大兴区亦庄镇第二中学高三2班
分析以上方案的缺点:
数据冗余,空间浪费!!!!
这个设计是比较失败的!
第二种方案:班级一张表、学生一张表
#t_class 班级表
classno(pk) classname
------------------------------------------------------
100 北京市大兴区亦庄镇第二中学高三1班
101 北京市大兴区亦庄镇第二中学高三1班
#t_student 学生表
no(pk) name cno(FK引用t_class这张表的classno)
----------------------------------------------------------------
1 jack 100
2 lucy 100
3 lilei 100
4 hanmeimei 100
5 zhangsan 101
6 lisi 101
7 wangwu 101
8 zhaoliu 101
当cno字段没有任何约束的时候,可能会导致数据无效。可能出现一个102,但是102班级不存在。
所以为了保证cno字段中的值都是100和101,需要给cno字段添加外键约束。
那么:cno字段就是外键字段。cno字段中的每一个值都是外键值。
DROP TABLE IF EXISTS t_student;
DROP TABLE IF EXISTS t_class;
#创建t_class表
CREATE TABLE t_class(
classno INT PRIMARY KEY,
classname varchar(255)
);
#创建t_student表 使用外键约束
CREATE TABLE t_student(
no int PRIMARY KEY auto_increment,
name VARCHAR(255),
cno int,
FOREIGN KEY(cno) REFERENCES t_class(classno) #----------->>>foreign key(约束的字段) references 要引入的表名(字段)
);
INSERT INTO t_class (classno,classname) VALUES(100,'北京市大兴区亦庄镇第二中学高三1班'),(101,'北京市大兴区亦庄镇第二中学高三2班');
INSERT INTO t_student (name,cno) VALUES('张三',100),
('李四',100),
('王五',100),
('赵六',100),
('李七',101),
('赵八',101),
('李海涛',101),
('DJ磊',101),
('张育源',101);
select * from t_student;
select * from t_class;
注意:
t_class是父表
t_student是子表
删除表的顺序:
先删子,后删父
建表顺序:
先建父,再建子
删除数据的顺序:
先删子,后删父
插入数据的顺序:
先插入父,再插入子
子表中的外键引用的父表中的某个字段,被引用的这个字段必须是主键吗?
不一定是主键,但至少具有unique约束。
测试:外键可以为NULL吗?
外键值可以为NULL。
17、存储引擎(了解内容)
1.什么是存储引擎,有什么用呢?
存储引擎是MySQL中特有的一个术语,其它数据库中没有。(Oracle中有,但是不叫这个名字)
存储引擎这个名字高端大气上档次。
实际上存储引擎是一个表存储/组织数据的方式。
不同的存储引擎,表存储数据的方式不同。
2.怎么给表添加/指定“存储引擎”呢?
show create table t_student;
#可以在建表的时候给表指定存储引擎。
CREATE TABLE `t_student` (
`no` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`cno` int(11) DEFAULT NULL,
PRIMARY KEY (`no`),
KEY `cno` (`cno`),
CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `t_class` (`classno`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
在建表的时候可以在最后小括号的")"的右边使用:
ENGINE来指定存储引擎。
CHARSET来指定这张表的字符编码方式。
结论:
mysql默认的存储引擎是:InnoDB
mysql默认的字符编码方式是:utf8
建表时指定存储引擎,以及字符编码方式。
create table t_product(
id int primary key,
name varchar(255)
)engine=InnoDB default charset=gbk;
3.查看MySQL支持那些引擎
命令:
show engines \G
*************************** 1. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
*************************** 5. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 8. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 9. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
MySQL支持九大储存引擎,版本不同支持不同
4.MySQL常用存储引擎
4.1MyISAM存储引擎
它管理的表具有以下特征:
使用三个文件表示每个表:
格式文件 — 存储表结构的定义(mytable.frm)
数据文件 — 存储表行的内容(mytable.MYD)
索引文件 — 存储表上索引(mytable.MYI):索引是一本书的目录,缩小扫描范围,提高查询效率的一种机制。
可被转换为压缩、只读表来节省空间
提示一下:
对于一张表来说,只要是主键,
或者加有unique约束的字段上会自动创建索引。
MyISAM存储引擎特点:
可被转换为压缩、只读表来节省空间
这是这种存储引擎的优势!!!!
MyISAM不支持事务机制,安全性低。
4.2InnoDB存储引擎
这是mysql默认的存储引擎,同时也是一个重量级的存储引擎。
InnoDB支持事务,支持数据库崩溃后自动恢复机制。
InnoDB存储引擎最主要的特点是:非常安全。
它管理的表具有下列主要特征:
– 每个 InnoDB 表在数据库目录中以.frm 格式文件表示
– InnoDB 表空间 tablespace 被用于存储表的内容(表空间是一个逻辑名称。表空间存储数据+索引。)
– 提供一组用来记录事务性活动的日志文件
– 用 COMMIT(提交)、SAVEPOINT 及ROLLBACK(回滚)支持事务处理
– 提供全 ACID 兼容
– 在 MySQL 服务器崩溃后提供自动恢复
– 多版本(MVCC)和行级锁定
– 支持外键及引用的完整性,包括级联删除和更新
InnoDB最大的特点就是支持事务:
以保证数据的安全。效率不是很高,并且也不能压缩,不能转换为只读,
不能很好的节省存储空间。
4.3MEMORY存储引擎
使用 MEMORY 存储引擎的表,其数据存储在内存中,且行的长度固定,
这两个特点使得 MEMORY 存储引擎非常快。
MEMORY 存储引擎管理的表具有下列特征:
– 在数据库目录内,每个表均以.frm 格式的文件表示。
– 表数据及索引被存储在内存中。(目的就是快,查询快!)
– 表级锁机制。
– 不能包含 TEXT 或 BLOB 字段。
MEMORY 存储引擎以前被称为HEAP 引擎。
**MEMORY引擎优点:**查询效率是最高的。不需要和硬盘交互。
**MEMORY引擎缺点:**不安全,关机之后数据消失。因为数据和索引都是在内存当中。
18、事务
1.事务概述
什么是事务?
一个事务其实是一个完整的业务逻辑。
最小的工作单元,不可再分
什么是完整的业务逻辑?
假设转账,从A账户向B账户中转账10000.
将A账户的钱减去10000(update语句)
将B账户的钱加上10000(update语句)
这就是一个完整的业务逻辑。
以上的操作是一个最小的工作单元,要么同时成功,要么同时失败,不可再分。
这连个update语句要求必须同时成功或失败,这样才能保证数据是正确的。
2.只有DML语句才有事务这一说,其他语句都没有关系
insert
delete
update只有以上的三个语句和事务有关系,其他都没有关系
因为 只有以上的三个语句是数据库表中数据进行 增 删 改 的。
只要涉及到 增 删 改 ,那么就一定要考虑安全问题。
数据安全第一位
3.假设
假设所有事务,只要一条DML语句就能完成,还有必要存在事务机制吗?
正是因为做某件事的时候,需要多条DML语句共同联合起来完成,所以需要事务的存在。如果任何一件复杂的事都能一条DML语句搞定,那么事务则没有存在的价值。
到底什么是事务?
说到底,本质上就是,一个事务其实就是多条DML语句同时成功或者失败!
**事务:**就是批量的DML语句同时成功,或者同时失败!
4.事务是怎么做到多条DML语句同时成功和同时失败的
**InnoDB存储引擎:**提供一组用来记录事务性活动的日志文件
事务开启了:
insert
insert
insert
delete
update
update
update
事务结束了!
在事务的执行过程中,每一条DML操作都会记录到“事务性活动的日志文件”中。
在事务的执行过程中,我们可以提交事务,也可以回滚事务。
提交事务:
清空事务性活动日志文件,将数据全部彻底持久化到数据库表中
提交事务标志着,事务的结束。并且是一种全部成功的结束
回滚事务:
将之前的所有的DML操作全部撤销,并且清空事务性活动日志文件
回滚事务标志着,事务的结束,并且是一种全部失败的结束
5.提交事务、回滚事务
**提交事务:**commit;
**回滚事务:**rollback;
事务对应的单词是:transaction
在MySQL中默认的事务行为是怎么的
MySQL默认支持情况下是支持自动提交事务的(自动提交)
什么是自动提交
每执行一条DML语句,则提交一次!
这种自动提交实际上是不符合我们的开发习惯,因为一个业务通常是需要多条DML语句共同执行才能完成的,为了保证数据的安全,必须要求同时成功之后再提交,所以不能执行一条就提交。
怎么取消MySQL的自动提交事务机制
执行命令:start transaction;
演示事务:
---------------------------------回滚事务----------------------------------------
mysql> use bjpowernode;
Database changed
mysql> select * from dept_bak;
Empty set (0.00 sec)
mysql> start transaction; #------------------>>>取消MySQL的自动提交事务机制
Query OK, 0 rows affected (0.00 sec)
mysql> insert into dept_bak values(10,'abc', 'tj'); #----->>>插入数据
Query OK, 1 row affected (0.00 sec)
mysql> insert into dept_bak values(10,'abc', 'tj'); #----->>>插入数据
Query OK, 1 row affected (0.00 sec)
mysql> select * from dept_bak; #-------------->>>查询数据
+--------+-------+------+
| DEPTNO | DNAME | LOC |
+--------+-------+------+
| 10 | abc | tj |
| 10 | abc | tj |
+--------+-------+------+
2 rows in set (0.00 sec) #-------------->>>查询结果数据2行
mysql> rollback; #---------------------------->>>回滚事务
Query OK, 0 rows affected (0.00 sec)
mysql> select * from dept_bak; #----------------->>>回滚事务后查询结果0行
Empty set (0.00 sec)
---------------------------------提交事务----------------------------------------
mysql> use bjpowernode;
Database changed
mysql> select * from dept_bak;
+--------+-------+------+
| DEPTNO | DNAME | LOC |
+--------+-------+------+
| 10 | abc | bj |
+--------+-------+------+
1 row in set (0.00 sec)
mysql> start transaction; #------------------>>>取消MySQL的自动提交事务机制
Query OK, 0 rows affected (0.00 sec)
mysql> insert into dept_bak values(20,'abc','tj'); #----->>>插入数据
Query OK, 1 row affected (0.00 sec)
mysql> insert into dept_bak values(20,'abc','tj');
Query OK, 1 row affected (0.00 sec)
mysql> insert into dept_bak values(20,'abc','tj');
Query OK, 1 row affected (0.00 sec)
mysql> commit; #---------------------------->>>提交事务
Query OK, 0 rows affected (0.01 sec)
mysql> select * from dept_bak; #-------------->>>提交事务后查询结果4行
+--------+-------+------+
| DEPTNO | DNAME | LOC |
+--------+-------+------+
| 10 | abc | bj |
| 20 | abc | tj |
| 20 | abc | tj |
| 20 | abc | tj |
+--------+-------+------+
4 rows in set (0.00 sec)
mysql> rollback; #---------------------------->>>回滚事务
Query OK, 0 rows affected (0.00 sec)
mysql> select * from dept_bak;#-------------->>>回滚事务后查询结果4行
+--------+-------+------+
| DEPTNO | DNAME | LOC |
+--------+-------+------+
| 10 | abc | bj |
| 20 | abc | tj |
| 20 | abc | tj |
| 20 | abc | tj |
+--------+-------+------+
4 rows in set (0.00 sec)
6.事务包括四个特性
- A:原子性
- 说明事务是最小的工作单元,不可再分
- C:一致性
- 所有事务要求,在同一个事务当中,所有操作必须同时成功或者同时失败,以保证数据的一致性。
- I:隔离性
- A事务和B事务之间就有一定的隔离。
- 教室A和教室B之间有一堵墙就是隔离性。
- A事务在操作一张表的时候,另外一个事务B也操作这张表会怎么样?
- D:持久性
- 事务最终结束的一个保障,事务提交,就相当于将没有保存到硬盘的数据,保存到硬盘上。
7.事务的隔离性
A教室和B教室中间有一道墙,这墙可以很厚,也可以很薄,这就是事务的隔离级别
这道墙越厚,表示隔离级别越高
事务与事务之间的隔离级别有哪些?有4个级别
- **读未提交:**read uncommitted(隔离级别1)《没有提交就读到了》
- 什么是读未提交
- 事务A可以读到事务B未提交的数据
- 这种隔离级别存在的问题就是:
- 脏读现象!(Dirty Read)
- 称读到了脏数据
- 这种隔离级别一般是理论上的,大多数的数据库隔离级别都是二档起步
- 什么是读未提交
- **读已提交:**read committed(隔离级别2)《提交后才能读到了》
- 什么是读已提交
- 事务A只能读取到,事务B已提交之后的数据
- 这种隔离级别解决了说明问题
- 解决了脏读现象
- 这种隔离级别存在什么问题
- 不可重复读取数据
- 什么是不可重复读取数据
- 在事务开启之后,第一次读取到的数据是3条,当事务还没结束,可能第二次读取的时候,读到的数据是4条,3不等于4
- 称为不可重复读
- 这种隔离级别是比较真实的数据,每一次读到的数据是绝对真实的。
- Oracle数据库默认的隔离级别是
- 什么是读已提交
- **可重复的:**repeatable read(隔离级别3)《提交之后也读不到,永远读取的都是刚开启事务时的数据》
- 什么是可重复读取
- 事务A开启之后,不管多久,每一次在事务A读取到的数据都是一致的。即使事务B将数据修改,并且提交了,事务A读取到的数据还是没有改变,这就是可重复读。
- 可重复读解决了什么问题
- 解决了不可重复读取数据
- 可重复读存在什么问题
- 会出现幻影读
- 每一次读到的都是幻象,不够真实
- 早晨9点开始开启了事务,只要事务不结束,到晚上9点,读到的数据还是那样!读到的是假象。不够绝对的真实。
- mysql中默认的事务隔离级别就是这个
- 什么是可重复读取
- **序列化/串行化:**serializable(隔离级别4)《提交后才能读到了》
- 这是最高的隔离级别,效率最低,解决了所有问题
- 这种隔离级别表示事务排队,不能并发!
- synchronized,线程同步(事务同步)
- 每一次读取到的数据都是最真实的,并且效率是最低的。
8.验证事务隔离
查看MySQL事务隔离级别:select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ |
+-------------------------+
8.1验证:read uncommitted
mysql> set global transaction isolation level read uncommitted;
事务A 事务B
--------------------------------------------------------------------------------
use bjpowernode;
use bjpowernode;
start transaction;
select * from t_user;
start transaction;
insert into t_user values('zhangsan');
select * from t_user;
8.2验证:read committed
mysql> set global transaction isolation level read committed;
事务A 事务B
--------------------------------------------------------------------------------
use bjpowernode;
use bjpowernode;
start transaction;
start transaction;
select * from t_user;
insert into t_user values('zhangsan');
select * from t_user;
commit;
select * from t_user;
8.3验证:repeatable read
mysql> set global transaction isolation level repeatable read;
事务A 事务B
--------------------------------------------------------------------------------
use bjpowernode;
use bjpowernode;
start transaction;
start transaction;
select * from t_user;
insert into t_user values('lisi');
insert into t_user values('wangwu');
commit;
select * from t_user;
8.4验证:serializable
mysql> set global transaction isolation level serializable;
事务A 事务B
--------------------------------------------------------------------------------
use bjpowernode;
use bjpowernode;
start transaction;
start transaction;
select * from t_user;
insert into t_user values('abc');
select * from t_user;
19、索引
1.什么是索引
索引是在数据库表字段上添加的,是为了提高查询效率存在的一种机制。
一张表的一个字段可以添加一个索引,当然,多个字段联合起来也可以添加索引。
索引相当于一本书的目录,是为了缩小扫描范围而存在的一种机制
对于一本字典来说,查找某一个汉字有两种方式
第一种方式:一页一页挨着找,直到找到为止,这种查找方式属于全字典扫描,效率比较低。
第二种方式:先通过目录(索引)去定位大概位置,然后直接定位到这个位置,做局域扫描,缩小扫描范围,快速查找,这种方式属于通过索引检索,效率高
例如:
#t_user表
id(IdIndex) name(nameIndex) Email(EmailIndex) address(addressIdIndex)
----------------------------------------------------------------------------------------
1 zs 1236@m.com .....
2 zs 1236@m.com .....
3 lisi 1236@m.com .....
4 zs 1236@m.com .....
5 zs 1236@m.com .....
select * from t_user where name = 'lisi';
#以上的这条SQL语句会去name字段,为什么?因为查询条件是:name='lisi'
#如果name字段上没有添加索引(目录),或者说没有给name字段创建索引,
#MySQL会进行全扫描,会将name字段上的每一个值都比对一遍。效率比较低。
MySQL在查询方面主要就是两种方式:
- 第一种方式:全表扫描
- 第二种方式:根据索引检索。
注意:
在实际中,汉语字典前面的目录是排序的,按照a b c d e f…排序,为什么排序呢?因为只有排序了才会有区间查找这一说!(缩小扫描范围其实就是扫描某个区间罢了!)
在mysql数据库当中索引也是需要排序的,并且这个所以的排序和TreeSet数据结构相同。TreeSet(TreeMap)底层是一个自平衡的二叉树!在mysql当中索引是一个B-Tree数据结构。遵循左小又大原则存放。采用中序遍历方式遍历取数据。
2.索引的实现原理
假设有一张用户表:t_user
id(PK) name 每一行记录在硬盘上都有物理存储编号
----------------------------------------------------------------------------------
100 zhangsan 0x1111
120 lisi 0x2222
99 wangwu 0x8888
88 zhaoliu 0x9999
101 jack 0x6666
55 lucy 0x5555
130 tom 0x7777
-
提醒1:
- 在任何数据库当中主键都会自动添加索引对象,id字段上自动有索引,因为id是PK。另外MySQL当中,一个字段上unique约束的话,也会自动创建索引对象
-
提醒2:
- 在任何数据库当中,任何一张表任何一条记录在硬盘上都有物理存储编号。
-
提醒3:
- 在MySQL当中,索引是一个单独的对象,不同的存储引擎以不同形式存在,在MyISAM存储引擎中,索引存在一个.MYI文件中。在InnoDB存储引擎中索引存储在一个逻辑名称叫做tablespace的当中。在MEMORY存储引擎当中索引被存储在内存当中。不管索引存储在哪里,索引在mysql当中都是一个树的形式存在。(自平衡二叉树:B-Tree)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5vupx2Nb-1647143138616)(C:\Users\admin\AppData\Roaming\Typora\typora-user-images\image-20211205155618213.png)]
3.在MySQL当中,主键上,以及unique字段上都会自动添加索引
什么条件下,我们会考虑添加索引
- 条件1:
- 数据量庞大(到底有多么庞大算庞大,这个需要测试,因为每一个硬件环境不同)
- 条件2:
- 该字段经常出现在where的后面,以条件的形式存在,也就是说该字段经常被扫描
- 条件3:
- 该字段很少的DML(insert delete update)操作。(因为DMl后,索引需要重新排序)
建议不要随意添加索引,因为索引也需要维护,太多的索引反而降低系统性能
建议通过主键查询,建议通过unique约束的字段进行查询,效率是比较高的。
4.创建、删除索引
创建索引语法格式:
create index 索引名 on 表名(字段)
删除索引语法格式:
drop index 索引名 on 表名;
在mysql当中,怎么查看一个SQL语句是否使用了索引进行检索?
**关键字:**explain
explain select * from emp where ename = 'KING';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 14 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
扫描14条记录:说明没有使用索引。type=ALL
create index emp_ename_index on emp(ename);
explain select * from emp where ename = 'KING';
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | emp | NULL | ref | emp_ename_index | emp_ename_index | 43 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
5.索引失效
失效第一种情况:
select * from emp where ename like '%k';
ename 上即使添加了索引,也不会走索引查询,为什么?
原因是模糊查询匹配当中以“%”开头了?
尽量避免模糊查询匹配当中的以“%”开始
这是一种优化的策略手段
explain select * from emp where ename like '%T';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 14 | 11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
失效第二种情况:
使用or的时候会失效,如果使用or那么要求or两边的字段都要有索引,才会走索引,如果其中一边有一个字段没有索引,那么另一个字段的索引也不会实现,所以这就是为什么不建议使用or的原因。
explain select * from emp where ename = 'KING' or job = 'MANAGER';
+----+-------------+-------+------------+------+-----------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | emp | NULL | ALL | emp_ename_index | NULL | NULL | NULL | 14 | 16.43 | Using where |
+----+-------------+-------+------------+------+-----------------+------+---------+------+------+----------+-------------+
失效第三种情况:
使用复合索引的时候,没用使用左侧的列查找,索引失效
什么叫复合索引?
两个字段或多个字段联合添加一个索引的称为联合索引
explain select * from emp where sal = 800;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 14 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
失效第四种情况:
在where当中索引列参加了运算,索引失效。
explain select * from emp where sal+1 = 800;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 14 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
失效第五种情况:
在where当中索引列使用了函数
explain select * from emp where lower(ename) = 'smith';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
失效第六种情况:
…
6.索引的分类
索引是各种数据库进行优化的重要手段。优化的时候优先考虑的因素就是索引。
索引在数据库当中分了很多类
- 单一索引:一个字段上添加索引。
- 复合索引:两个字段或者更多的字段上添加索引。
- 主键索引:主键上添加索引。
- 唯一性索引:具有unique约束的字段上添加索引。
…
**注意:**唯一性比较弱的字段上添加索引用处不大。
20、视图
1.什么是视图
view:站在不同的角度去看待一份数据
2.创建、删除视图对象
创建视图对象语法格式:
create view 视图名 as select * from 表名;
删除视图对象语法格式:
drop view 视图名;
3.视图作用
我们可以面向视图对象进行增删改查,对视图对象增删改查会导致原表被操作!(视图特点:通过对视图的操作,会影响原表数据)
面向视图查询:
select * from 视图名;
面向视图插入:
insert into 视图名 (字段1,字段2,...) values(值1,值2,....)
面向视图查询:
delete from 表名 where 条件;
无条件删除全视图数据
创建视图对象示例:
create view
emp_dept_view
as
select
e.ename,e.sal,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno;
select * from emp_dept_view;
+--------+---------+------------+
| ename | sal | dname |
+--------+---------+------------+
| SMITH | 800.00 | RESEARCH |
| ALLEN | 1600.00 | SALES |
| WARD | 1250.00 | SALES |
| JONES | 2975.00 | RESEARCH |
| MARTIN | 1250.00 | SALES |
| BLAKE | 2850.00 | SALES |
| CLARK | 2450.00 | ACCOUNTING |
| SCOTT | 3000.00 | RESEARCH |
| KING | 5000.00 | ACCOUNTING |
| TURNER | 1500.00 | SALES |
| ADAMS | 1100.00 | RESEARCH |
| JAMES | 950.00 | SALES |
| FORD | 3000.00 | RESEARCH |
| MILLER | 1300.00 | ACCOUNTING |
+--------+---------+------------+
面向视图更新示例:
#将ACCOUNTING部门的员工工资修改为1000
update emp_dept_view set sal = 1000 where dname = 'ACCOUNTING';
select * from emp;
#emp表
+-------+--------+-----------+------+------------+---------+---------+--------+
| 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 | 1000.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 1000.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 | 1000.00 | NULL | 10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
4.视图对象在实际开中的作用
方便,简化开发,利于维护
create view
emp_dept_view
as
select
e.ename,e.sal,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno;
假设有一条非常复杂的sql语句,二这条语句需要在不同的位置上反复使用。
每一次使用这个sql语句的时候都要重新编写,很长、麻烦,怎么办?
可以把这条复杂的sql语句以视图的形式新建。
在需要的时候编写这条sql语句的位置直接使用视图对象,可以大大的简化开发,并且利于后期的维护,因为修改的时候也只需要修改删除一个位置就行,只需要修改视图对象所映射的sql语句
我们以后面向视图开发的时候,使用视图的时候可以像使用table一样。
可以对视图进行增删改查等操作。视图不是在内存当中,视图对象也是
存储在硬盘上的,不会消失。
再提醒一下:
视图对应的语句只能是DQL语句。
但是视图对象创建完成之后,可以对视图进行增删改查等操作。
小插曲:
增删改查,又叫做:CRUD。
CRUD是在公司中程序员之间沟通的术语。一般我们很少说增删改查。
一般都说CRUD。
- C:Create(增)
- R:Retrive(查:检索)
- U:Update(改)
- D:Delete(删)
21、DBA常用命令
重点掌握:
数据的导出导入(数据的备份)
数据库导出语法格式:
**注意:**实在Windows的dos命令窗口中执行,命令后没有“;”符
mysqldump 数据库名>盘符:\路径\导出的文件名.sql -u用户名 -p密码
导出指定表语法格式:
mysqldump 数据库名 表名>盘符:\路径\导出的文件名.sql -u用户名 -p密码
数据导入格式:
**注意:**需要先登录MySQL
1.创建数据库 ----->> create database 数据库名;
2.使用数据库 ----->> use 数据库名;
3.初始化数据库 ---->> source 脚本文件.sql
22、数据库设计三范式
1.什么是数据库设计范式
数据库表的设计依据,教你怎么进行数据库表的设计。
2.数据库的设计范式共有三式
- 第一范式:要求任何一张表都要有主键,每一个字段原子性不可再分。
- 第二范式:建立在第一范式的基础上,要求所有非主键字段完全依赖主键。
- 第三范式:建立在第二范式的基础上,要求要求所有非主键字段直接依赖主键,不要会产生传递依赖
设计数据库表的时候,按照以上范式进行,可以避免数据冗余,空间的浪费。
3.第一范式
最核心,最重要的范式,所有表的设计都需要满足,必须有主键,并且每个字段都有原子性不可再分。
学生编号 学生姓名 联系方式
------------------------------------------
1001 张三 zs@gmail.com,1359999999
1002 李四 ls@gmail.com,13699999999
1001 王五 ww@163.net,13488888888
以上是学生表,不满足第一范式
一、没有主键
二、联系方式可分为邮箱地址和电话
学生编号(pk) 学生姓名 邮箱地址 联系电话
----------------------------------------------------
1001 张三 zs@gmail.com 1359999999
1002 李四 ls@gmail.com 13699999999
1003 王五 ww@163.net 13488888888
4.第二范式
建立在第一范式的基础之上,要求所有非主键字段必须完全依赖主键,不要产生部分依赖。
学生编号 学生姓名 教师编号 教师姓名
----------------------------------------------------
1001 张三 001 王老师
1002 李四 002 赵老师
1003 王五 001 王老师
1001 张三 002 赵老师
这张表描述了学生和老师的关系:(一个学生可能会有多个老师,一个老师可能会有多个学生)
**典型的:**多对多的关系
分析以上的表是否满足第一范式?
怎么满足第一范式呢?修改
学生编号+教师编号(pk) 学生姓名 教师姓名
----------------------------------------------------
1001 001 张三 王老师
1002 002 李四 赵老师
1003 001 王五 王老师
1001 002 张三 赵老师
学生编号 教师编号,两个字段联合做主键,复合主键(PK: 学生编号+教师编号)
经过修改之后,以上的表满足了第一范式。但是满足第二范式吗?
不满足,“张三”依赖1001,“王老师”依赖001,显然产生了部分依赖。
产生部分依赖有什么缺点?
数据冗余了。空间浪费了。“张三”重复了,“王老师”重复了。
为了让以上的表满足第二范式,你需要这样设计:
使用三张表来表示多对多的关系!!!!
学生表
学生编号(pk) 学生名字
------------------------------------
1001 张三
1002 李四
1003 王五
教师表
教师编号(pk) 教师姓名
--------------------------------------
001 王老师
002 赵老师
学生教师关系表
id(pk) 学生编号(fk) 教师编号(fk)
------------------------------------------------------
1 1001 001
2 1002 002
3 1003 001
4 1001 002
多对多怎么设计?
多对多,三张表,关系表两个外键
5.第三范式
第三范式建立在第二范式的基础之上 要求所有非主键字典必须直接依赖主键,不要产生传递依赖。
学生编号(PK) 学生姓名 班级编号 班级名称
---------------------------------------------------------
1001 张三 01 一年一班
1002 李四 02 一年二班
1003 王五 03 一年三班
1004 赵六 03 一年三班
以上表的设计是描述:班级和学生的关系。很显然是1对多关系!一个教室中有多个学生。
分析以上表是否满足第一范式?
满足第一范式,有主键。
分析以上表是否满足第二范式?
满足第二范式,因为主键不是复合主键,没有产生部分依赖。主键是单一主键。
分析以上表是否满足第三范式?
第三范式要求:不要产生传递依赖!
一年一班依赖01,01依赖1001,产生了传递依赖。
不符合第三范式的要求。产生了数据的冗余。
那么应该怎么设计一对多呢?
班级表:一
班级编号(pk) 班级名称
----------------------------------------
01 一年一班
02 一年二班
03 一年三班
学生表:多
学生编号(PK) 学生姓名 班级编号(fk)
-------------------------------------------
1001 张三 01
1002 李四 02
1003 王五 03
1004 赵六 03
一对多,两张表,多的表加外键
6.总结表的设计
- 一对多:
- 一对多,两张表,多的表加外键
- 多对多:
- 多对多,三张表,关系表两个外键
- 一对一:
- 一对一放到一张表中不就行了吗?为啥还要拆分表?
在实际的开发中,可能存在一张表字段太多,太庞大。这个时候要拆分表。
一对一怎么设计? - 一对一,外键唯一
- 一对一放到一张表中不就行了吗?为啥还要拆分表?
#没有拆分表之前:一张表
t_user
id login_name login_pwd real_name email address........
-------------------------------------------------------------------------------------------
1 zhangsan 123 张三 zhangsan@xxx .......
2 lisi 123 李四 lisi@xxx .......
这种庞大的表建议拆分为两张:
#t_login 登录信息表
id(pk) login_name login_pwd
---------------------------------
1 zhangsan 123
2 lisi 123
#t_user 用户详细信息表
id(pk) real_name email address........ login_id(fk+unique)
-----------------------------------------------------------------------------------------
100 张三 zhangsan@xxx .... 1
200 李四 lisi@xxx .... 2
注意:
数据库设计三范式是理论上的。
实践和理论有的时候有偏差。
最终的目的都是为了满足客户的需求,有的时候会拿冗余换执行速度。
因为在sql当中,表和表之间连接次数越多,效率越低。(笛卡尔积)
有的时候可能会存在冗余,但是为了减少表的连接次数,这样做也是合理的,并且对于开发人员来说,sql语句的编写难度也会降低。
22、悲观锁(行级锁)和乐观锁的概念
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vnj4JRuG-1647143138617)(C:\Users\admin\AppData\Roaming\Typora\typora-user-images\image-20211210205632312.png)]
----------------------------------------------------
1001 张三 001 王老师
1002 李四 002 赵老师
1003 王五 001 王老师
1001 张三 002 赵老师
这张表描述了学生和老师的关系:(一个学生可能会有多个老师,一个老师可能会有多个学生)
**典型的:**多对多的关系
分析以上的表是否满足第一范式?
怎么满足第一范式呢?修改
```sql
学生编号+教师编号(pk) 学生姓名 教师姓名
----------------------------------------------------
1001 001 张三 王老师
1002 002 李四 赵老师
1003 001 王五 王老师
1001 002 张三 赵老师
学生编号 教师编号,两个字段联合做主键,复合主键(PK: 学生编号+教师编号)
经过修改之后,以上的表满足了第一范式。但是满足第二范式吗?
不满足,“张三”依赖1001,“王老师”依赖001,显然产生了部分依赖。
产生部分依赖有什么缺点?
数据冗余了。空间浪费了。“张三”重复了,“王老师”重复了。
为了让以上的表满足第二范式,你需要这样设计:
使用三张表来表示多对多的关系!!!!
学生表
学生编号(pk) 学生名字
------------------------------------
1001 张三
1002 李四
1003 王五
教师表
教师编号(pk) 教师姓名
--------------------------------------
001 王老师
002 赵老师
学生教师关系表
id(pk) 学生编号(fk) 教师编号(fk)
------------------------------------------------------
1 1001 001
2 1002 002
3 1003 001
4 1001 002
多对多怎么设计?
多对多,三张表,关系表两个外键
5.第三范式
第三范式建立在第二范式的基础之上 要求所有非主键字典必须直接依赖主键,不要产生传递依赖。
学生编号(PK) 学生姓名 班级编号 班级名称
---------------------------------------------------------
1001 张三 01 一年一班
1002 李四 02 一年二班
1003 王五 03 一年三班
1004 赵六 03 一年三班
以上表的设计是描述:班级和学生的关系。很显然是1对多关系!一个教室中有多个学生。
分析以上表是否满足第一范式?
满足第一范式,有主键。
分析以上表是否满足第二范式?
满足第二范式,因为主键不是复合主键,没有产生部分依赖。主键是单一主键。
分析以上表是否满足第三范式?
第三范式要求:不要产生传递依赖!
一年一班依赖01,01依赖1001,产生了传递依赖。
不符合第三范式的要求。产生了数据的冗余。
那么应该怎么设计一对多呢?
班级表:一
班级编号(pk) 班级名称
----------------------------------------
01 一年一班
02 一年二班
03 一年三班
学生表:多
学生编号(PK) 学生姓名 班级编号(fk)
-------------------------------------------
1001 张三 01
1002 李四 02
1003 王五 03
1004 赵六 03
一对多,两张表,多的表加外键
6.总结表的设计
- 一对多:
- 一对多,两张表,多的表加外键
- 多对多:
- 多对多,三张表,关系表两个外键
- 一对一:
- 一对一放到一张表中不就行了吗?为啥还要拆分表?
在实际的开发中,可能存在一张表字段太多,太庞大。这个时候要拆分表。
一对一怎么设计? - 一对一,外键唯一
- 一对一放到一张表中不就行了吗?为啥还要拆分表?
#没有拆分表之前:一张表
t_user
id login_name login_pwd real_name email address........
-------------------------------------------------------------------------------------------
1 zhangsan 123 张三 zhangsan@xxx .......
2 lisi 123 李四 lisi@xxx .......
这种庞大的表建议拆分为两张:
#t_login 登录信息表
id(pk) login_name login_pwd
---------------------------------
1 zhangsan 123
2 lisi 123
#t_user 用户详细信息表
id(pk) real_name email address........ login_id(fk+unique)
-----------------------------------------------------------------------------------------
100 张三 zhangsan@xxx .... 1
200 李四 lisi@xxx .... 2
注意:
数据库设计三范式是理论上的。
实践和理论有的时候有偏差。
最终的目的都是为了满足客户的需求,有的时候会拿冗余换执行速度。
因为在sql当中,表和表之间连接次数越多,效率越低。(笛卡尔积)
有的时候可能会存在冗余,但是为了减少表的连接次数,这样做也是合理的,并且对于开发人员来说,sql语句的编写难度也会降低。
22、悲观锁(行级锁)和乐观锁的概念
[外链图片转存中…(img-vnj4JRuG-1647143138617)]