SQL是1981年由IBM公司推出
根据SQL指令完成的数据库操作的不同，可以将SQL指令分为四类
DDL Data Defintion language 数据库定义语言
用于完成对数据库对象(数据表,数据库,视图,索引)的创建，删除，修改
DML Data Manipulation language 数据操作语言
用于完成对数据表中的数据添加，删除，修改
DQL Data Query language 数据查询语言
用于将数据表中的数据查询出来
DCL Data Control Laguage 数据控制语言
用于完成事务管理等控制型操作
SQL 基本语法
SQL指令不区分大小写
每条SQL表达式结束之后都以;结束
SQL关键字之间都以 空格进行分割
SQL之间可以不限制换行(可以有空格的地方就可以有换行)
使用DDL语句可以创建数据库，删除数据库，修改数据库
查询数据库
##显示mysql中所有是数据库
show databases;
##显示指定数据库的创建的SQL指令
show create database ;
创建数据库
##在mysql中创建一个数据库 dbName表示数据库名称
create database ;
##创建数据库，指定名称不存在的时候创建
create database if not exists ;
##在创建数据库的同时指定数据库的字符集(字符集：数据存储在数据库)
create database character set 字符集;
修改数据库
##修改数据库的字符集
alter database character set 字符集;
删除数据库
删除数据库时会删除数据库中所有的表以及数据库中的所有的数据
##删除数据库
drop database ;
##如果数据库存在则删除数据库
drop database if exists ;
使用/切换数据库
use ;
创建数据表
查询数据表
show tables;
查询表结构
desc ;
删除数据表
##删除数据表
drop table ;
##删除数据表如果表存在
drop table if exists ;
修改数据表
##修改表名
alter table rename to ;
##数据表也是有字符集的,默认字符集和数据库一致
alter table character set 字符集;
##添加字段(列)
alter table add ;
##修改的列表名和类型
alter table change ;
##只修改字段类型
alter table modify ;
##删除字段(列)
alter table drop ;
在MySQL中有多种数据类型可以有存放数值，不同类型存放的数值范围或者形式不同的
存储字符序列的类型
在创建数据表的时候,指定的对数据的列的数据限制性的要求(对表的列中的数据进行限制)
为什么给表中的数据添加数据呢?
保证数据的有效性
保证数据的完整性
保证数据的正确性
字段常见的约束有那些呢？
非空约束(not null):限制此列的值必须提供,不能null
唯一约束(unique):在表中的多条数据,此列的值不能重复
主键约束(primary key):非空+唯一,能够唯一标示数据表中的一条数据.
外键约束(foreign key):建立不同表之间的关联关系.
主键–就是数据表中的记录中的唯一标识，在一张表中只能有一个主键(主键可以是一个字段,也可以是多个列组合)
此字段数据不能为null;
此字段数据不能重复;
在我们创建一张数据表时，如果数据表中有可以作为主键(列如:学生表的学号,图书表的isbn)我们可以直接设为这个字段为主键,
当有些数据没有合适的字段作为主键的时候，我们可以定义一个与记录无关的列(ID)作为主键
,此数据无具体含义主要作为标识唯一,在mysql中我们可以将此定义为int ，同事设置为自动增长
当我们想数据表中新曾一条数据时，无需提供ID列的值，它会自动增长.
auto_increment
注意：自动增长从1开始，每次添加一条数据，自动增长的列会+1，但是如果把某条数据删除，自动增长的数据也不会重复生产(自动增长的数据不会保证连续)
注意:在实际企业项目的数据库设计中，联合主键使用频率不高，当一个数据表中没有明确的字段可以作为主键时，我们可以额外添加一个ID字段作为主键
pass
用于完成对数据表中的数据中插入，删除，修改
### 删除数据
从数据表中删除满足特定条件(所有)的数据
对数据表中已经添加的记录进行修改
示例
从数据表中提取满足特定条件的记录
单表查询
多表联合查询
在删除，修改及修改的语句后都可以添加where子句(conditions)，用于筛选出满足特定的数据进行，删除，修改和查询操作．
在where子句中，可以将多个条件通过逻辑预算(and or not)进行连接,通过多个条件来筛选需要的操作
在where子句中可以使用like关键字来实现模糊查询
在like关键字后的reg表达式中
%表示任意多字符［%o%表示含有o］
_表示任意一个字符
设置查询的列
对从数据表中查询的记录的列进行一定的运算之后显示出来.
我们可以为查询的结果的字段 取一个语义更强的别名
as 字段别名
distinct
从查询的结果中将重复的记录消除
将查询到的满足条件的记录按照指定的列的值升序/降序排列
order by columnName 表示将查询结果按照指定的列排序
asc 按照指定的列升序排序
desc 按照指定的列降序排序和查看表结构是同一个缩写(纯属巧合)
sql中提供了一些可以对查询记录进行列计算的的函数–聚合函数
count() 统计函数,统计满足条件的指定字段的个数(记录数)
max()记录指定列中的最大值
min()记录中指定列的最小值
sum()记录中指定列的和
avg()记录中指定记录的平均值
当我们向日期类型的列添加数据时,可以通过字符串类型(字符串格式必须是 yyyy-MM-dd hh:mm:ss)
select 后使用*显示对查询的结果进行分组之后，显示每一组的第一条记录(这种显示通常是无意义的)
select 后通常显示分组字段和聚合函数(对分组后的数据进行统计,求和,平均值等)
当数据表中的记录比较多的时候,如果一次性全部查询出来的显示给用户,用户的可读性/体验性就不太好,因为此我们可以将这些数据分页进行展示.
param1 int 获取查询语句的结果的第一条数据的索引(索引从0开始)
param2 int 获取查询语句的结果的条数(如果剩下的数据条数<pram2,则返回剩下的记录)
对数据表中的学生信息进行分页显示,总共59条数据,我们每页显示20条
总记录数: count 59
每页显示 page 20
总页数 pageCount = cpimt%page ?count/page:count/page +1;
MySQL是一个关系型数据库,不仅可以存储数据,还可以维护数据与数据之间的关系–通过数据表中的数据字段的外键约束
一对一关联
一对多关联
多对一关联
多对多关联
人–身份证
学生–学籍
用户–用户详情
方案一:主键关联–两张数据表中的主键相同的数据互相对应的数据
方案二:唯一外键–在任意一张表中添加外键约束与另一张表主键关联,并且将外键列添加唯一约束
班级—学生(一对多)一个班级可以包含多个学生
学生–班级(多对一) 多个学生可以属于同一个班级
图书–分类 商品–商品类别
学生–课程 一个学生可以选择多门课,一门课也可以由多个学生选择
会员–社团 一个会员
外键约束–将一个列添加外键约束与另一张表的主键(唯一列)进行关联之后,这个外键约束添加的数据必须要在关联的主键字段中存在
案例:学生表 与 班级表(在学生表中添加外键与班级表的主键进行关联)
先创建班级表
创建学生表(在学生表中添加外键与班级表主键进关联)
向班级表添加班级信息
向学生表中添加学生信息
当学生表中存在的学生信息关联班级的某条记录时,就不能对班级表的这条记录进行修改ID和删除操作
如果一定要修改Java2104的班级ID,该如何实现?
将应用Python班级的id的学生记录的cid修改为null
在修改班级信息中的Python记录的class_id
讲学生表中cid为NULL的记录设重新修改为 Python这个班级的class_id
在添加外键时,设置级联修改和级联删除
通过DQL的学习,我们可以很轻松的从一张数据表中查询出需要的数据:在企业的应用开发,我们经常需要从多张表中查询数据(列如:我们查询学生信息的时候),可以通过连接查询从多张数据表提取数据:
在MySQL中可以使用join实现多表的联合查询–链接查询,
inner join 内连接
left join 左连接
right join 右连接
笛卡尔积(A集合&B集合):使用A中的每个记录依次关联B中每个记录,笛卡尔集的总数=A总数*B总数
如果直接执行select … from tableNmae1 inner join tableName2; 会获取两张数据表中的数据集合的笛卡尔积(依次使用tableName1表中的每条数据 去 匹配tableName2的每条数据)
两张表同时用inner join连接查询之后产生笛卡尔积数据很多是无意义的,我们如何消除无意义的数据----添加两张进行连接的查询时的条件
使用on设置两张表连接查询的匹配条件
结果:只取两种表中匹配条件成立的数据,任何一张表在另一张表如果没有找到对应匹配则不会出现在查询结果中(例如:小红和小明没有对应的班级信息 python课没有被人选)
需求:查询出所有的学生信息,如果学生有对应的班级信息,则将对应的班级信息也查询出来.
左连接:显示左表中的所有数据,如果在右表中满足条件的数据,则进行匹配;如果右表中不存在匹配数据,则显示为null
右连接:示右表中的所有数据,如果在右表中满足条件的数据,则进行匹配;如果左表表中不存在匹配数据,则显示为null
如果连接查询的多张表中存在相同名字的字段,我们可以使用表名.字段名来分区,如果表名太长则不便于SQL语句的编写,我们可以使用数据表别名
子查询–先进行一次查询,第一次查询的结果作为第二次查询的/条件(源) (第二次查询是基于第一次查询的结果进行的)
传统方式
子查询单行单列
子查询:
传统方式
子查询多行单列
子查询
子查询返回多个值,多行多列
从SQL执行的流程中我们分析存在的问题
如果我们需要重复多次执行相同的SQL,SQL执行需要通过连接传递到MySQL,并且经过编译和执行的步骤
如果我们需要连续执行多个SQL指令,并且第二个SQL指令需要使用第一个SQL指令执行的结果为参数;
SQL指令无需客户端编写,通过 网络传送,可以节省网络开销,同事避免SQL指令在网络传输过程中被恶意篡改保证安全性;
存储过程经过编译创建并保存在数据库中,执行过程无需重复的进行编译操作,对SQL指令的执行过程进行了性能提升;
存储过程中多个SQL指令之间存在逻辑关系,支持流程控制语句(分支,循环),可以实现更为复杂的的业务;
存储过程是根据不同的数据库进行编译.创建并存储在数据库中;当我们需要切换到其他的数据库产品时,需要重写针对于新数据库的存储过程
存储过程受限于数据库产品,如果需要提高性能的优化会成为一个问题
存储过程中的变量分为2种,局部变量 和 用户变量
局部变量:定义在存储过程中的变量,只能存储过程内部使用
局部变量定义语法
用户变量:相当于全家变量,定义的用户变量可以通过 select @attrName dual进行查询
– 用户变量会存储在mysql数据库的字典中(dual)
– 用户变量定义使用set关键字直接定义,变量名以@开头
set @n=1;
无论是局部变量还是用户变量,都使用set关键字修改值
将存储过程中使用select … into …给变量赋值
因为用户变量相当于全局变量,可以使用SQL指令以及多个存储过程中共享,在开发中建议尽量少使用用户变量,用户变量过多会导致程序不易理解,难以维护
MySQL存储过程的参数一共三种: IN \ OUT \ INOUT
输入参数 – 在调用过程中传递数据存储过程的参数(在调用过程必须具有实际值的变量 或者 字面值)
输出参数 – 将存储过程中产生的数据返回给过程的调用者,相当于Java方法中的返回值,单不同的是一个存储过程可以有多个输出参数
在存储过程中支持流程控制语句用于实现显示实现逻辑的控制
if-then-else
case
while
repeat
loop
存储过程是隶属于某个数据库的,也就是说我们存储过程创建在某个数据库之后,只能在数据库中调用此存储过程
查询存储过程:查询某个数据库中,有哪些存储过程
修改存储过程是指修改存储过程的特性
存储过程的特征参数:
CONTAINS SQL 表示子查询包含SQL语句,但不包含读或者写数据的语句
NO SQL 表示子程序中不包含SQL语句
READS SQL DATA 表示子程序中包含读数据的语句
MODIFIES SQL DATA 表示子程序中包含读写数据的语句
SQL SECURITY{DEFINER|INVOKER}指明谁有权限来执行
DEFINER 表示只有定义自己能够执行
INVOKER 表示调用者可以执行
COMMENT 'string’ 表示注释信息
使用存储过程解决企业项目中开发过程中的问题
案例:使用存储过程完成借书操作
业务分析
创建一个存储过程实现借书的操作:哪个学生借哪本书,借了多少本?
操作:
保存借书记录
修改图书库存
条件:
判断学生是否存在?
判断图书是否存在,库存是否存在?
问题:如果我们要创建一个存储过程,需要返回查询语句查询到的多条数据,该如何实现呢?
游标可以用来依次取出查询结果集中的每一条数据–逐条读取查询结果集中的记录
示例
打开游标
使用游标
关闭游标
案例
触发器,就是一种特殊的存储过程.触发器和存储过程一样是一个能够完成特定功能,存储在数据库服务器上的SQL片段,但是触发器无需调用,当对数据表中执行DML操作时自动触发SQL片段的执行,无需手动调用.
在MySQL,只有执行insert\delete\update操作才能触发触发器的执行
语法:
触发器多用于监听对数据表中的数据insert ,delete,update操作,在触发时通常处理一些DML的关联操作;我们可以使用NEW和OLD关键字在触发器中获取这个触发器的DML操作的数据
NEW:在触发器用于获取insert操作添加的数据,update操作修改后的记录
OLD:在触发器中用于获取delete操作删除前的数据,update操作修改前的数据
insert操作:NEW表示添加的新记录
update操作中:NEW表示修改后是数据
delete:OLD表示删除的记录
update: OLD表示修改前的记录
触发器可以实现数据表中的级联操作(关联操作),有利于保证数据的完整性;
触发器可以对DML操作的数据进行更为分组的合法性校验
使用触发器实现的业务逻辑如果出现问题将难以定位,后期维护困难
大量使用触发器容易导致代码结构杂乱 ,增加了程序的复杂性
当触发器操作的数据量比较打时,执行效率会大大降低
在互联网项目中,应避免使用触发器
在并发量不大的项目可以使用存储过程,但是互联网项目中不提倡使用存储过程(原因:存储过程是将实现业务的逻辑交给数据库处理,一则增减了数据库的负载,二则不利于数据库的迁移)
视图,就是由数据库中的一张表或者多张表特定的条件查询出数据构造得到 得到虚拟表
安全性:如果将数据表授权给用户操作,那么用户可以CRUD数据表中所有数据,加入我们想要堆数据表中的部分数据进行保护,可以将公开的数据生成数据视图,授权用户访问视图:用户通过查询视图可以获取数据表中公开的数据,从而达到奖数据表中的部分数据堆用户隐藏
简单性:如果我们需要查询的数据源于多张数据表,可以使用多表连接查询来实现:我们通过视图将这些表查询的结果对用户开放,用户则可以直接通过视图获取多表数据,操作更便捷.
示例1
示例2
视图是虚拟表,查询视图的数据是源于数据表的,当对视图进行操作时,对原数据表中的数据是否有影响呢?
**查询操作:**如果在数据表中添加了新的数据,而且这个数据满足创建视图时查询语句的条件,通过查询视图也可以查询出新增数据,当删除原表中满足条件的数据时,也会葱视图中删除
新增数据:如果在视图中新增数据,数据会被添加到原始数据表
删除数据:如果视图删除数据,数据也会从原表中删除
修改操作:如果通过修改数据,则也将修改原数据表中的数据
删除数据表时会同时删除数据表中的数据,删除视图时不会影响数据表中的数据
数据库是用来存储数据的,在互联网应用中存储的数据可能会很多{大数据},数据表中的查询速度会随着数据量的增长逐渐变慢,从而导致响应用户请求的速度变慢–用户体验差 我们如何提高数据库的查询效率呢?
索引,就是用来提高数据表中数据的查询效率的.
索引,就是将数据表中的某一列/某几列的值取出来构造成便于查找的结构进行存储,生成数据表的目录 当前我们进行数据查询的时候,则先在目录 中进行查找得到对于的数据地址,然后在到数据表中根据地址快速的获取数据记录,避免全表扫描
MySQL中的索引,根据创建的所有的列不同,可以分为:
主键索引:在数据表的主键字段创建索引,这个字段必须被 primary key修饰,每张表只能有一个主键
唯一索引:在数据表中的唯一列创建的索引(unique), 此列的索引值只能出现一次,可以为null
普通索引:在普通字段上创建的索引,没有唯一性的限制
组合索引:两个及以上字段联合创建的索引
说明:
在创建数据表时,将字段设置为主键(添加主键约束),会自动在主键字段创建主键索引
MySQL 5.6 版本新增加的索引,可以通过此索引进行全文检索操作,因为MySQL全文检索不支持中文,因此这个全文索引不被开发者关注,在应用开发中通常是通过搜索引擎(数据库中间件)实现全文检索
索引创建完成之后无需调用,当根据创建索引的列进行数据查询的时候,会自动使用索引;
组合索引需要根据创建索引的索引字段进行查询时触发
在命令行窗口中可以查看查询语句的查询规划
优点
索引大大降低了数据服务器在执行查询操作时扫描的数据量,提高查询效率
索引可以避免服务器排序,将随机IO编程顺序IO
缺点
索引是根据数据表的列进行创建的,当数据表中的数据发生DML操作时,索引也需要更新
索引文件也会占用磁盘空间(小问题)
注意事项
如果数据表中的数据不多时,全表扫描可能更加快,不要使用索引;
数据量很大但是DML操作很频繁时,不建议使用索引;
不要在数据重复度高的列上创建索引(性别);
创建索引之后,要注意SQL语句的编写,避免索引失效
我们吧完成特定的业务的多个数据的DML步骤称之为一个事务
事务,就是完成一个业务的多个DML
ACID特性,高频面试题
一致性(Consistency):事务执行之前和事务执行之后,数据库中的数据是一致的,完整性和一致性不能被破坏
隔离性:(lsolation):数据库允许多个事务同时执行(张三借Java书同时允许李四借Java书),多个并行的事务之间不能相互影响
持久性(Durabikity):事务完成之后,对数据的操作是永久的
在MySQL中,默认DML指令的执行是自动提交的,当我们执行一个DML指令之后,自动同步到数据库中
开启事务,就是关闭自动提交
在事务开始操作之前,执行 start transaction开始事务
依次执行事务中的每个DML操作
如果执行的过程的任何位置出现异常,则执行rollback回滚事务
如果事务中所有的DML操作都执行成功,则最后执行commit提交事务
数据库允许多个事务并行,多个事务之间是隔离的,相互独立的;如果事务之间不相互隔离并且操作同一数据时,数据的一致性可能被破坏
MySQL数据库事务隔离级别:
读未提交(read uncommitted):T2可以读取T1执行单未提交的数据;可能会导致出现脏读
脏读:一个事务读取到另一个事务中未提交的数据
读已提交(read committed):T2只能读取T1已经提交的数据;避免了脏读但可能会导致不可重复读(虚读)
不可重复读(虚读):在同一个事务中2次查询操作读取到的数据不一致
例如:T2进行一次查询之后在第二次查询之前,T1修改并提交了数据,T2进行了第二次查询读取到的数据和第一次读取到数据不一致
可重复读(repeatable read):T2执行第一次查询之后,在事务结束之前其他事务不能修改对应的数据;避免了不可重复读(虚读);但可能会导致幻读
幻读,T2对数据表中的数据进行修改然后查询,在查询之前向数据表中新增了一条数据,就导致T2以为修改了所有数据,但却查询出了与修改不一致的数据(T1事务新增的数据)
串行化(serializable):同时只允许一个事务对数据表进行操作;避免了脏读,虚读(不可重复读),幻读问题
我们可以通过设置数据库默认的事务的隔离界别来控制事务之间的隔离性;
也可以通过客户端与数据库的连接设置事务间的事务之间的隔离性;(在应用程序中设置–Spring)
MySQL数据库的默认的隔离级别为**'可重复读’**
查看MySQL数据库默认的隔离级别
设置MySQL默认隔离级别
MySQL数据库作为数据存储的介质为应用系统提供数据存储的服务,我们如何设计出合理的数据库,数据表以满足应用系统的数据存储需求呢?
数据库是为应用系统服务的,数据库存储什么样的数据也是由应用系统来决定的.
当我们进行应用系统开发时,我们首先要明确应用应用系统功能需求------软件系统的需求分析
三线图进行数据表设计
PowerDesigner
PDMan
如果实体的数据项不满足三范式,可能导致数据的冗余,从而引起数据维护困难,破坏数据一致性等问题
商品(商品名称,商品图片,商品描述…)
电商系统:商品,用户,订单…
教务管理系统:学生,课程,成绩…
车库:是用来存放车辆的,车库都需要划分车位,如果不划分车位,车子杂乱无章的存放可能导致车辆堵塞,同时也可能导致场地的浪费--------优先的场地能通停放最多的车辆,同时方便每一辆车的出入
数据库,是用来存放数据的,我们需要设计合理的数据表--------能够完成数据的存储,同时能够方便的提取我们应用系统所需的数据
根据应用系统的功能,分析数据实体(实体,就是要存储的数据对象)
提取实体的数据项(数据项,就是实体的属性)
根据数据库设计三范式规范我们数据项:检查实体的数据项是否满足数据库设计三范式
绘制E-R图(实体关系图,直观的展示实体之间的关系)
数据库建模:
建库建表:编写SQL指令创建数据库,数据表
添加测试数据,SQL测试
学校图书管理系统(借书)
要求数据表的字段不可再分
以下表不满足第一范式(在数据库中创建不出不满足第一范式的表)
将细分的列作为单独的一列
不存在非关键字段对关键字段部分依赖
以下表不满足第二范式
将每个关键字段列出来\关键字段的组合也列出来,依次检查每个非关键字段
不存在非关键字段的之间的传递依赖
以下数据表不满足第三范式
将关键字段和被依赖的关键字段分贝作为主键,依次检查所有非关键字段的依赖关系
E-R(Entity_Relationship) 实体关系图,用于直观的体现实体与实体之间的关联关系(一对一,一对多,多对一,多对多)
E-R图基本图例
每个实体创建一张数据表
多堆多关联:额外创建一个数据表维护关系,关系表分别创建外键与两张表关联
一对多,多对一关联:在多的一端添加外键与一的一端主键建立外键约束
一对多关联:在任意一端创建外键与另一端关联主键关联,并将外键设置# 基本SQL语句(一篇就够了)
SQL(Structured Query Language)结构化查询语言，用于存取，查询，更新数据以及管理关系型数据库系统
SQL是1981年由IBM公司推出
根据SQL指令完成的数据库操作的不同，可以将SQL指令分为四类
DDL Data Defintion language 数据库定义语言
用于完成对数据库对象(数据表,数据库,视图,索引)的创建，删除，修改
DML Data Manipulation language 数据操作语言
用于完成对数据表中的数据添加，删除，修改
DQL Data Query language 数据查询语言
用于将数据表中的数据查询出来
DCL Data Control Laguage 数据控制语言
用于完成事务管理等控制型操作
SQL 基本语法
SQL指令不区分大小写
每条SQL表达式结束之后都以;结束
SQL关键字之间都以 空格进行分割
SQL之间可以不限制换行(可以有空格的地方就可以有换行)
使用DDL语句可以创建数据库，删除数据库，修改数据库
查询数据库
##显示mysql中所有是数据库
show databases;
##显示指定数据库的创建的SQL指令
show create database ;
创建数据库
##在mysql中创建一个数据库 dbName表示数据库名称
create database ;
##创建数据库，指定名称不存在的时候创建
create database if not exists ;
##在创建数据库的同时指定数据库的字符集(字符集：数据存储在数据库)
create database character set 字符集;
修改数据库
##修改数据库的字符集
alter database character set 字符集;
删除数据库
删除数据库时会删除数据库中所有的表以及数据库中的所有的数据
##删除数据库
drop database ;
##如果数据库存在则删除数据库
drop database if exists ;
使用/切换数据库
use ;
创建数据表
查询数据表
show tables;
查询表结构
desc ;
删除数据表
##删除数据表
drop table ;
##删除数据表如果表存在
drop table if exists ;
修改数据表
##修改表名
alter table rename to ;
##数据表也是有字符集的,默认字符集和数据库一致
alter table character set 字符集;
##添加字段(列)
alter table add ;
##修改的列表名和类型
alter table change ;
##只修改字段类型
alter table modify ;
##删除字段(列)
alter table drop ;
在MySQL中有多种数据类型可以有存放数值，不同类型存放的数值范围或者形式不同的
存储字符序列的类型
在创建数据表的时候,指定的对数据的列的数据限制性的要求(对表的列中的数据进行限制)
为什么给表中的数据添加数据呢?
保证数据的有效性
保证数据的完整性
保证数据的正确性
字段常见的约束有那些呢？
非空约束(not null):限制此列的值必须提供,不能null
唯一约束(unique):在表中的多条数据,此列的值不能重复
主键约束(primary key):非空+唯一,能够唯一标示数据表中的一条数据.
外键约束(foreign key):建立不同表之间的关联关系.
主键–就是数据表中的记录中的唯一标识，在一张表中只能有一个主键(主键可以是一个字段,也可以是多个列组合)
此字段数据不能为null;
此字段数据不能重复;
在我们创建一张数据表时，如果数据表中有可以作为主键(列如:学生表的学号,图书表的isbn)我们可以直接设为这个字段为主键,
当有些数据没有合适的字段作为主键的时候，我们可以定义一个与记录无关的列(ID)作为主键
,此数据无具体含义主要作为标识唯一,在mysql中我们可以将此定义为int ，同事设置为自动增长
当我们想数据表中新曾一条数据时，无需提供ID列的值，它会自动增长.
auto_increment
注意：自动增长从1开始，每次添加一条数据，自动增长的列会+1，但是如果把某条数据删除，自动增长的数据也不会重复生产(自动增长的数据不会保证连续)
注意:在实际企业项目的数据库设计中，联合主键使用频率不高，当一个数据表中没有明确的字段可以作为主键时，我们可以额外添加一个ID字段作为主键
pass
用于完成对数据表中的数据中插入，删除，修改
### 删除数据
从数据表中删除满足特定条件(所有)的数据
对数据表中已经添加的记录进行修改
示例
从数据表中提取满足特定条件的记录
单表查询
多表联合查询
在删除，修改及修改的语句后都可以添加where子句(conditions)，用于筛选出满足特定的数据进行，删除，修改和查询操作．
在where子句中，可以将多个条件通过逻辑预算(and or not)进行连接,通过多个条件来筛选需要的操作
在where子句中可以使用like关键字来实现模糊查询
在like关键字后的reg表达式中
%表示任意多字符［%o%表示含有o］
_表示任意一个字符
设置查询的列
对从数据表中查询的记录的列进行一定的运算之后显示出来.
我们可以为查询的结果的字段 取一个语义更强的别名
as 字段别名
distinct
从查询的结果中将重复的记录消除
将查询到的满足条件的记录按照指定的列的值升序/降序排列
order by columnName 表示将查询结果按照指定的列排序
asc 按照指定的列升序排序
desc 按照指定的列降序排序和查看表结构是同一个缩写(纯属巧合)
sql中提供了一些可以对查询记录进行列计算的的函数–聚合函数
count() 统计函数,统计满足条件的指定字段的个数(记录数)
max()记录指定列中的最大值
min()记录中指定列的最小值
sum()记录中指定列的和
avg()记录中指定记录的平均值
当我们向日期类型的列添加数据时,可以通过字符串类型(字符串格式必须是 yyyy-MM-dd hh:mm:ss)
select 后使用*显示对查询的结果进行分组之后，显示每一组的第一条记录(这种显示通常是无意义的)
select 后通常显示分组字段和聚合函数(对分组后的数据进行统计,求和,平均值等)
当数据表中的记录比较多的时候,如果一次性全部查询出来的显示给用户,用户的可读性/体验性就不太好,因为此我们可以将这些数据分页进行展示.
param1 int 获取查询语句的结果的第一条数据的索引(索引从0开始)
param2 int 获取查询语句的结果的条数(如果剩下的数据条数<pram2,则返回剩下的记录)
对数据表中的学生信息进行分页显示,总共59条数据,我们每页显示20条
总记录数: count 59
每页显示 page 20
总页数 pageCount = cpimt%page ?count/page:count/page +1;
MySQL是一个关系型数据库,不仅可以存储数据,还可以维护数据与数据之间的关系–通过数据表中的数据字段的外键约束
一对一关联
一对多关联
多对一关联
多对多关联
人–身份证
学生–学籍
用户–用户详情
方案一:主键关联–两张数据表中的主键相同的数据互相对应的数据
方案二:唯一外键–在任意一张表中添加外键约束与另一张表主键关联,并且将外键列添加唯一约束
班级—学生(一对多)一个班级可以包含多个学生
学生–班级(多对一) 多个学生可以属于同一个班级
图书–分类 商品–商品类别
学生–课程 一个学生可以选择多门课,一门课也可以由多个学生选择
会员–社团 一个会员
外键约束–将一个列添加外键约束与另一张表的主键(唯一列)进行关联之后,这个外键约束添加的数据必须要在关联的主键字段中存在
案例:学生表 与 班级表(在学生表中添加外键与班级表的主键进行关联)
先创建班级表
创建学生表(在学生表中添加外键与班级表主键进关联)
向班级表添加班级信息
向学生表中添加学生信息
当学生表中存在的学生信息关联班级的某条记录时,就不能对班级表的这条记录进行修改ID和删除操作
如果一定要修改Java2104的班级ID,该如何实现?
将应用Python班级的id的学生记录的cid修改为null
在修改班级信息中的Python记录的class_id
讲学生表中cid为NULL的记录设重新修改为 Python这个班级的class_id
在添加外键时,设置级联修改和级联删除
通过DQL的学习,我们可以很轻松的从一张数据表中查询出需要的数据:在企业的应用开发,我们经常需要从多张表中查询数据(列如:我们查询学生信息的时候),可以通过连接查询从多张数据表提取数据:
在MySQL中可以使用join实现多表的联合查询–链接查询,
inner join 内连接
left join 左连接
right join 右连接
笛卡尔积(A集合&B集合):使用A中的每个记录依次关联B中每个记录,笛卡尔集的总数=A总数*B总数
如果直接执行select … from tableNmae1 inner join tableName2; 会获取两张数据表中的数据集合的笛卡尔积(依次使用tableName1表中的每条数据 去 匹配tableName2的每条数据)
两张表同时用inner join连接查询之后产生笛卡尔积数据很多是无意义的,我们如何消除无意义的数据----添加两张进行连接的查询时的条件
使用on设置两张表连接查询的匹配条件
结果:只取两种表中匹配条件成立的数据,任何一张表在另一张表如果没有找到对应匹配则不会出现在查询结果中(例如:小红和小明没有对应的班级信息 python课没有被人选)
需求:查询出所有的学生信息,如果学生有对应的班级信息,则将对应的班级信息也查询出来.
左连接:显示左表中的所有数据,如果在右表中满足条件的数据,则进行匹配;如果右表中不存在匹配数据,则显示为null
右连接:示右表中的所有数据,如果在右表中满足条件的数据,则进行匹配;如果左表表中不存在匹配数据,则显示为null
如果连接查询的多张表中存在相同名字的字段,我们可以使用表名.字段名来分区,如果表名太长则不便于SQL语句的编写,我们可以使用数据表别名
子查询–先进行一次查询,第一次查询的结果作为第二次查询的/条件(源) (第二次查询是基于第一次查询的结果进行的)
传统方式
子查询单行单列
子查询:
传统方式
子查询多行单列
子查询
子查询返回多个值,多行多列
从SQL执行的流程中我们分析存在的问题
如果我们需要重复多次执行相同的SQL,SQL执行需要通过连接传递到MySQL,并且经过编译和执行的步骤
如果我们需要连续执行多个SQL指令,并且第二个SQL指令需要使用第一个SQL指令执行的结果为参数;
SQL指令无需客户端编写,通过 网络传送,可以节省网络开销,同事避免SQL指令在网络传输过程中被恶意篡改保证安全性;
存储过程经过编译创建并保存在数据库中,执行过程无需重复的进行编译操作,对SQL指令的执行过程进行了性能提升;
存储过程中多个SQL指令之间存在逻辑关系,支持流程控制语句(分支,循环),可以实现更为复杂的的业务;
存储过程是根据不同的数据库进行编译.创建并存储在数据库中;当我们需要切换到其他的数据库产品时,需要重写针对于新数据库的存储过程
存储过程受限于数据库产品,如果需要提高性能的优化会成为一个问题
存储过程中的变量分为2种,局部变量 和 用户变量
局部变量:定义在存储过程中的变量,只能存储过程内部使用
局部变量定义语法
用户变量:相当于全家变量,定义的用户变量可以通过 select @attrName dual进行查询
– 用户变量会存储在mysql数据库的字典中(dual)
– 用户变量定义使用set关键字直接定义,变量名以@开头
set @n=1;
无论是局部变量还是用户变量,都使用set关键字修改值
将存储过程中使用select … into …给变量赋值
因为用户变量相当于全局变量,可以使用SQL指令以及多个存储过程中共享,在开发中建议尽量少使用用户变量,用户变量过多会导致程序不易理解,难以维护
MySQL存储过程的参数一共三种: IN \ OUT \ INOUT
输入参数 – 在调用过程中传递数据存储过程的参数(在调用过程必须具有实际值的变量 或者 字面值)
输出参数 – 将存储过程中产生的数据返回给过程的调用者,相当于Java方法中的返回值,单不同的是一个存储过程可以有多个输出参数
在存储过程中支持流程控制语句用于实现显示实现逻辑的控制
if-then-else
case
while
repeat
loop
存储过程是隶属于某个数据库的,也就是说我们存储过程创建在某个数据库之后,只能在数据库中调用此存储过程
查询存储过程:查询某个数据库中,有哪些存储过程
修改存储过程是指修改存储过程的特性
存储过程的特征参数:
CONTAINS SQL 表示子查询包含SQL语句,但不包含读或者写数据的语句
NO SQL 表示子程序中不包含SQL语句
READS SQL DATA 表示子程序中包含读数据的语句
MODIFIES SQL DATA 表示子程序中包含读写数据的语句
SQL SECURITY{DEFINER|INVOKER}指明谁有权限来执行
DEFINER 表示只有定义自己能够执行
INVOKER 表示调用者可以执行
COMMENT 'string’ 表示注释信息
使用存储过程解决企业项目中开发过程中的问题
案例:使用存储过程完成借书操作
业务分析
创建一个存储过程实现借书的操作:哪个学生借哪本书,借了多少本?
操作:
保存借书记录
修改图书库存
条件:
判断学生是否存在?
判断图书是否存在,库存是否存在?
问题:如果我们要创建一个存储过程,需要返回查询语句查询到的多条数据,该如何实现呢?
游标可以用来依次取出查询结果集中的每一条数据–逐条读取查询结果集中的记录
示例
打开游标
使用游标
关闭游标
案例
触发器,就是一种特殊的存储过程.触发器和存储过程一样是一个能够完成特定功能,存储在数据库服务器上的SQL片段,但是触发器无需调用,当对数据表中执行DML操作时自动触发SQL片段的执行,无需手动调用.
在MySQL,只有执行insert\delete\update操作才能触发触发器的执行
语法:
触发器多用于监听对数据表中的数据insert ,delete,update操作,在触发时通常处理一些DML的关联操作;我们可以使用NEW和OLD关键字在触发器中获取这个触发器的DML操作的数据
NEW:在触发器用于获取insert操作添加的数据,update操作修改后的记录
OLD:在触发器中用于获取delete操作删除前的数据,update操作修改前的数据
insert操作:NEW表示添加的新记录
update操作中:NEW表示修改后是数据
delete:OLD表示删除的记录
update: OLD表示修改前的记录
触发器可以实现数据表中的级联操作(关联操作),有利于保证数据的完整性;
触发器可以对DML操作的数据进行更为分组的合法性校验
使用触发器实现的业务逻辑如果出现问题将难以定位,后期维护困难
大量使用触发器容易导致代码结构杂乱 ,增加了程序的复杂性
当触发器操作的数据量比较打时,执行效率会大大降低
在互联网项目中,应避免使用触发器
在并发量不大的项目可以使用存储过程,但是互联网项目中不提倡使用存储过程(原因:存储过程是将实现业务的逻辑交给数据库处理,一则增减了数据库的负载,二则不利于数据库的迁移)
视图,就是由数据库中的一张表或者多张表特定的条件查询出数据构造得到 得到虚拟表
安全性:如果将数据表授权给用户操作,那么用户可以CRUD数据表中所有数据,加入我们想要堆数据表中的部分数据进行保护,可以将公开的数据生成数据视图,授权用户访问视图:用户通过查询视图可以获取数据表中公开的数据,从而达到奖数据表中的部分数据堆用户隐藏
简单性:如果我们需要查询的数据源于多张数据表,可以使用多表连接查询来实现:我们通过视图将这些表查询的结果对用户开放,用户则可以直接通过视图获取多表数据,操作更便捷.
示例1
示例2
视图是虚拟表,查询视图的数据是源于数据表的,当对视图进行操作时,对原数据表中的数据是否有影响呢?
**查询操作:**如果在数据表中添加了新的数据,而且这个数据满足创建视图时查询语句的条件,通过查询视图也可以查询出新增数据,当删除原表中满足条件的数据时,也会葱视图中删除
新增数据:如果在视图中新增数据,数据会被添加到原始数据表
删除数据:如果视图删除数据,数据也会从原表中删除
修改操作:如果通过修改数据,则也将修改原数据表中的数据
删除数据表时会同时删除数据表中的数据,删除视图时不会影响数据表中的数据
数据库是用来存储数据的,在互联网应用中存储的数据可能会很多{大数据},数据表中的查询速度会随着数据量的增长逐渐变慢,从而导致响应用户请求的速度变慢–用户体验差 我们如何提高数据库的查询效率呢?
索引,就是用来提高数据表中数据的查询效率的.
索引,就是将数据表中的某一列/某几列的值取出来构造成便于查找的结构进行存储,生成数据表的目录 当前我们进行数据查询的时候,则先在目录 中进行查找得到对于的数据地址,然后在到数据表中根据地址快速的获取数据记录,避免全表扫描
MySQL中的索引,根据创建的所有的列不同,可以分为:
主键索引:在数据表的主键字段创建索引,这个字段必须被 primary key修饰,每张表只能有一个主键
唯一索引:在数据表中的唯一列创建的索引(unique), 此列的索引值只能出现一次,可以为null
普通索引:在普通字段上创建的索引,没有唯一性的限制
组合索引:两个及以上字段联合创建的索引
说明:
在创建数据表时,将字段设置为主键(添加主键约束),会自动在主键字段创建主键索引
MySQL 5.6 版本新增加的索引,可以通过此索引进行全文检索操作,因为MySQL全文检索不支持中文,因此这个全文索引不被开发者关注,在应用开发中通常是通过搜索引擎(数据库中间件)实现全文检索
索引创建完成之后无需调用,当根据创建索引的列进行数据查询的时候,会自动使用索引;
组合索引需要根据创建索引的索引字段进行查询时触发
在命令行窗口中可以查看查询语句的查询规划
优点
索引大大降低了数据服务器在执行查询操作时扫描的数据量,提高查询效率
索引可以避免服务器排序,将随机IO编程顺序IO
缺点
索引是根据数据表的列进行创建的,当数据表中的数据发生DML操作时,索引也需要更新
索引文件也会占用磁盘空间(小问题)
注意事项
如果数据表中的数据不多时,全表扫描可能更加快,不要使用索引;
数据量很大但是DML操作很频繁时,不建议使用索引;
不要在数据重复度高的列上创建索引(性别);
创建索引之后,要注意SQL语句的编写,避免索引失效
我们吧完成特定的业务的多个数据的DML步骤称之为一个事务
事务,就是完成一个业务的多个DML
ACID特性,高频面试题
一致性(Consistency):事务执行之前和事务执行之后,数据库中的数据是一致的,完整性和一致性不能被破坏
隔离性:(lsolation):数据库允许多个事务同时执行(张三借Java书同时允许李四借Java书),多个并行的事务之间不能相互影响
持久性(Durabikity):事务完成之后,对数据的操作是永久的
在MySQL中,默认DML指令的执行是自动提交的,当我们执行一个DML指令之后,自动同步到数据库中
开启事务,就是关闭自动提交
在事务开始操作之前,执行 start transaction开始事务
依次执行事务中的每个DML操作
如果执行的过程的任何位置出现异常,则执行rollback回滚事务
如果事务中所有的DML操作都执行成功,则最后执行commit提交事务
数据库允许多个事务并行,多个事务之间是隔离的,相互独立的;如果事务之间不相互隔离并且操作同一数据时,数据的一致性可能被破坏
MySQL数据库事务隔离级别:
读未提交(read uncommitted):T2可以读取T1执行单未提交的数据;可能会导致出现脏读
脏读:一个事务读取到另一个事务中未提交的数据
读已提交(read committed):T2只能读取T1已经提交的数据;避免了脏读但可能会导致不可重复读(虚读)
不可重复读(虚读):在同一个事务中2次查询操作读取到的数据不一致
例如:T2进行一次查询之后在第二次查询之前,T1修改并提交了数据,T2进行了第二次查询读取到的数据和第一次读取到数据不一致
可重复读(repeatable read):T2执行第一次查询之后,在事务结束之前其他事务不能修改对应的数据;避免了不可重复读(虚读);但可能会导致幻读
幻读,T2对数据表中的数据进行修改然后查询,在查询之前向数据表中新增了一条数据,就导致T2以为修改了所有数据,但却查询出了与修改不一致的数据(T1事务新增的数据)
串行化(serializable):同时只允许一个事务对数据表进行操作;避免了脏读,虚读(不可重复读),幻读问题
我们可以通过设置数据库默认的事务的隔离界别来控制事务之间的隔离性;
也可以通过客户端与数据库的连接设置事务间的事务之间的隔离性;(在应用程序中设置–Spring)
MySQL数据库的默认的隔离级别为**'可重复读’**
查看MySQL数据库默认的隔离级别
设置MySQL默认隔离级别
MySQL数据库作为数据存储的介质为应用系统提供数据存储的服务,我们如何设计出合理的数据库,数据表以满足应用系统的数据存储需求呢?
数据库是为应用系统服务的,数据库存储什么样的数据也是由应用系统来决定的.
当我们进行应用系统开发时,我们首先要明确应用应用系统功能需求------软件系统的需求分析
三线图进行数据表设计
PowerDesigner
PDMan
如果实体的数据项不满足三范式,可能导致数据的冗余,从而引起数据维护困难,破坏数据一致性等问题
商品(商品名称,商品图片,商品描述…)
电商系统:商品,用户,订单…
教务管理系统:学生,课程,成绩…
车库:是用来存放车辆的,车库都需要划分车位,如果不划分车位,车子杂乱无章的存放可能导致车辆堵塞,同时也可能导致场地的浪费--------优先的场地能通停放最多的车辆,同时方便每一辆车的出入
数据库,是用来存放数据的,我们需要设计合理的数据表--------能够完成数据的存储,同时能够方便的提取我们应用系统所需的数据
根据应用系统的功能,分析数据实体(实体,就是要存储的数据对象)
提取实体的数据项(数据项,就是实体的属性)
根据数据库设计三范式规范我们数据项:检查实体的数据项是否满足数据库设计三范式
绘制E-R图(实体关系图,直观的展示实体之间的关系)
数据库建模:
建库建表:编写SQL指令创建数据库,数据表
添加测试数据,SQL测试
学校图书管理系统(借书)
要求数据表的字段不可再分
以下表不满足第一范式(在数据库中创建不出不满足第一范式的表)
将细分的列作为单独的一列
不存在非关键字段对关键字段部分依赖
以下表不满足第二范式
将每个关键字段列出来\关键字段的组合也列出来,依次检查每个非关键字段
不存在非关键字段的之间的传递依赖
以下数据表不满足第三范式
将关键字段和被依赖的关键字段分贝作为主键,依次检查所有非关键字段的依赖关系
E-R(Entity_Relationship) 实体关系图,用于直观的体现实体与实体之间的关联关系(一对一,一对多,多对一,多对多)
E-R图基本图例
每个实体创建一张数据表
多堆多关联:额外创建一个数据表维护关系,关系表分别创建外键与两张表关联