day16mysql编程提高.docx
《day16mysql编程提高.docx》由会员分享，可在线阅读，更多相关《day16mysql编程提高.docx（21页珍藏版）》请在冰豆网上搜索。
day16mysql编程提高
1.课程回顾
mysql基础
1）mysql存储结构：
数据库->表->数据sql语句
2）管理数据库：
增加：
createdatabase数据库defaultcharacterutf8;
删除：
dropdatabase数据库;
修改：
alterdatabase数据库defaultcharactergbk;
查询：
showdatabases/showcreatedatabase数据库;
3）管理表：
选择数据库：
use数据库;
增加：
createtable表（字段名1字段类型,字段名2字段类型......）;
删除：
droptable表;
修改：
添加字段：
altertable表add[column]字段名字段类型;
删除字段:
altertable表drop[column]字段名;
修改字段类型：
altertable表modify字段名新的字段类型;
修改字段名称：
altertable表change旧字段名新字段名字段类型;
修改表名称：
altertable表rename[to]新表名;
查询：
showtables/descstudent;
4）管理数据:
增加：
insertinto表（字段1，字段2,。
。
。
）values（值1，值2.。
。
。
。
）;
删除：
deletefrom表where条件;
修改：
update表set字段1=值1，字段2=值2......where条件;
查询：
4.1）所有字段：
select*from表;
4.2）指定字段：
select字段1,字段2....from表;
4.3）指定别名：
select字段1as别名from表;
4.4）合并列：
select（字段1+字段2）from表;
4.5）去重：
selectdistinct字段from表;
4.6）条件查询：
a）逻辑条件：
and（与）or（或）
select*from表where条件1and/or条件2
b）比较条件：
><>=<==<>betweenand（在。
。
。
之间）
select*from表whereservlet>=90;
c）判空条件：
判断null：
isnull/isnotnull
判断空字符串：
=''/<>''
d）模糊条件：
like
%:
替换任意个字符
_:
替换一个字符
4.7分页查询：
limit起始行,查询行数
起始行从0开始
4.8排序：
orderby字段asc/desc
asc:
正序，顺序
desc：
反序，倒序
4.9分组查询：
groupby字段
4.10:
分组后筛选：
having条件
SQL语句的分类：
DDL：
数据定义语言
create/drop/alter
DML：
数据操作语句
insert/delete/update/truncate
DQL：
数据查询语言：
select/show
今天的目标：
大纲：
1）数据约束
2）数据库设计（表设计）
3）存储过程
4）触发器
5）mysql权限问题
2数据约束
2.1什么数据约束
对用户操作表的数据进行约束
2.2默认值
作用：
当用户对使用默认值的字段不插入值的时候，就使用默认值。
注意：
1）对默认值字段插入null是可以的。
2）对默认值字段可以插入非null
--1.1默认值
CREATETABLEstudent（
idINT,
NAMEVARCHAR（20）,
addressVARCHAR（20）DEFAULT'广州天河'--默认值
）
DROPTABLEstudent;
--当字段没有插入值的时候，mysql自动给该字段分配默认值
INSERTINTOstudent（id,NAME）VALUES（1,'张三'）;
--注意：
默认值的字段允许为null
INSERTINTOstudent（id,NAME,address）VALUE（2,'李四',NULL）;
INSERTINTOstudent（id,NAME,address）VALUE（3,'王五','广州番禺'）;
2.3非空
作用：
限制字段必须赋值
注意：
1）非空字符必须赋值
2）非空字符不能赋null
--1.2非空
--需求：
gender字段必须有值（不为null）
CREATETABLEstudent（
idINT,
NAMEVARCHAR（20）,
genderVARCHAR
（2）NOTNULL--非空
）
--非空字段必须赋值
INSERTINTOstudent（id,NAME）VALUES（1,'李四'）;
--非空字符不能插入null
INSERTINTOstudent（id,NAME,gender）VALUES（1,'李四',NULL）;
2.4唯一
作用：
对字段的值不能重复
注意：
1）唯一字段可以插入null
2）唯一字段可以插入多个null
--1.3唯一
CREATETABLEstudent（
idINTUNIQUE,--唯一
NAMEVARCHAR（20）
）
INSERTINTOstudent（id,NAME）VALUES（1,'zs'）;
INSERTINTOstudent（id,NAME）VALUES（1,'lisi'）;--ERROR1062（23000）:
Duplicateentry'1'forkey'id'
INSERTINTOstudent（id,NAME）VALUES（2,'lisi'）;
2.5主键
作用：
非空+唯一
注意：
1）通常情况下，每张表都会设置一个主键字段。
用于标记表中的每条记录的唯一性。
2）建议不要选择表的包含业务含义的字段作为主键，建议给每张表独立设计一个非业务含义的id字段。
--1.4主键（非空+唯一）
DROPTABLEstudent;
CREATETABLEstudent（
idINTPRIMARYKEY,--主键
NAMEVARCHAR（20）
）
INSERTINTOstudent（id,NAME）VALUES（1,'张三'）;
INSERTINTOstudent（id,NAME）VALUES（2,'张三'）;
--INSERTINTOstudent（id,NAME）VALUES（1,'李四'）;--违反唯一约束：
Duplicateentry'1'forkey'PRIMARY'
--insertintostudent（name）value（'李四'）;--违反非空约束：
ERROR1048（23000）:
Column'id'cannotbenull
2.6自增长
作用：
自动递增
--1.5自增长
CREATETABLEstudent（
idINT（4）ZEROFILLPRIMARYKEYAUTO_INCREMENT,--自增长，从0开始ZEROFILL零填充
NAMEVARCHAR（20）
）
--自增长字段可以不赋值，自动递增
INSERTINTOstudent（NAME）VALUES（'张三'）;
INSERTINTOstudent（NAME）VALUES（'李四'）;
INSERTINTOstudent（NAME）VALUES（'王五'）;
SELECT*FROMstudent;
--不能影响自增长约束
DELETEFROMstudent;
--可以影响自增长约束
TRUNCATETABLEstudent;
2.7外键
作用：
约束两种表的数据
出现两种表的情况：
解决数据冗余高问题：
独立出一张表
例如：
员工表和部门表
问题出现：
在插入员工表数据的时候，员工表的部门ID字段可以随便插入！
！
！
！
！
使用外键约束：
约束插入员工表的部门ID字段值
解决办法：
在员工表的部门ID字段添加一个外键约束
--部门表（主表）
CREATETABLEdept（
idINTPRIMARYKEY,
deptNameVARCHAR（20）
）
--修改员工表（副表/从表）
CREATETABLEemployee（
idINTPRIMARYKEY,
empNameVARCHAR（20）,
deptIdINT,--把部门名称改为部门ID
--声明一个外键约束
CONSTRAINTemlyee_dept_fkFOREIGNKEY（deptId）REFERENCESdept（id）
--外键名称外键参考表（参考字段）
）
注意：
1）被约束的表称为副表，约束别人的表称为主表，外键设置在副表上的！
！
！
2）主表的参考字段通用为主键！
3）添加数据：
先添加主表，再添加副表
4）修改数据：
先修改副表，再修改主表
5）删除数据：
先删除副表，再删除主表
--1.6外键约束
--员工表
CREATETABLEemployee（
idINTPRIMARYKEY,
empNameVARCHAR（20）,
deptNameVARCHAR（20）--部门名称
）
INSERTINTOemployeeVALUES（1,'张三','软件开发部'）;
INSERTINTOemployeeVALUES（2,'李四','软件开发部'）;
INSERTINTOemployeeVALUES（3,'王五','应用维护部'）;
SELECT*FROMemployee;
--添加员工，部门名称的数据冗余高
INSERTINTOemployeeVALUES（4,'陈六','软件开发部'）;
--解决数据冗余高的问题：
给冗余的字段放到一张独立表中
--独立设计一张部门表
CREATETABLEdept（
idINTPRIMARYKEY,
deptNameVARCHAR（20）
）
DROPTABLEemployee;
--修改员工表
CREATETABLEemployee（
idINTPRIMARYKEY,
empNameVARCHAR（20）,
deptIdINT,--把部门名称改为部门ID
--声明一个外键约束
CONSTRAINTemlyee_dept_fkFOREIGNKEY（deptId）REFERENCESdept（id）ONUPDATECASCADEONDELETECASCADE--ONCASCADEUPDATE：
级联修改
--外键名称外键参考表（参考字段）
）
INSERTINTOdept（id,deptName）VALUES（1,'软件开发部'）;
INSERTINTOdept（id,deptName）VALUES（2,'应用维护部'）;
INSERTINTOdept（id,deptName）VALUES（3,'秘书部'）;
INSERTINTOemployeeVALUES（1,'张三',1）;
INSERTINTOemployeeVALUES（2,'李四',1）;
INSERTINTOemployeeVALUES（3,'王五',2）;
INSERTINTOemployeeVALUES（4,'陈六',3）;
--问题:
该记录业务上不合法，员工插入了一个不存在的部门数据
INSERTINTOemployeeVALUES（5,'陈六',4）;--违反外键约束：
Cannotaddorupdateachildrow:
aforeignkeyconstraintfails（`day16`.`employee`,CONSTRAINT`emlyee_dept_fk`FOREIGNKEY（`deptId`）REFERENCES`dept`（`id`））
--1）当有了外键约束，添加数据的顺序：
先添加主表，再添加副表数据
--2）当有了外键约束，修改数据的顺序：
先修改副表，再修改主表数据
--3）当有了外键约束，删除数据的顺序：
先删除副表，再删除主表数据
--修改部门（不能直接修改主表）
UPDATEdeptSETid=4WHEREid=3;
--先修改员工表
UPDATEemployeeSETdeptId=2WHEREid=4;
--删除部门
DELETEFROMdeptWHEREid=2;
--先删除员工表
DELETEFROMemployeeWHEREdeptId=2;
SELECT*FROMdept;
SELECT*FROMemployee;
2.8级联操作
问题：
当有了外键约束的时候，必须先修改或删除副表中的所有关联数据，才能修改或删除主表！
但是，我们希望直接修改或删除主表数据，从而影响副表数据。
可以使用级联操作实现！
！
！
级联修改：
ONUPDATECASCADE
级联删除：
ONDELETECASCADE
CREATETABLEemployee（
idINTPRIMARYKEY,
empNameVARCHAR（20）,
deptIdINT,--把部门名称改为部门ID
--声明一个外键约束
CONSTRAINTemlyee_dept_fkFOREIGNKEY（deptId）REFERENCESdept（id）ONUPDATECASCADEONDELETECASCADE--ONCASCADEUPDATE：
级联修改
--外键名称外键参考表（参考字段）
）
注意：
级联操作必须在外键基础上使用
--级联修改（修改）
--直接修改部门
UPDATEdeptSETid=5WHEREid=4;
--级联删除
--直接删除部门
DELETEFROMdeptWHEREid=1;
3数据库设计
3.1引入
需求分析-需求分析师-》原始需求->抽取业务模型
图书模型：
图书名称，版本号，作者
学生模型：
学号，学生姓名手机号码
......
角色：
学生老师，图书管理员
《需求说明书》
需求设计-
概要设计：
抽取实体：
业务模型->实体模型（java类c++类）内存
classBook{name,bookNo,author}
数据库设计:
业务模型/实体模型->数据模型（硬盘）
数据库表设计
问题：
如何设计？
详细设计
类详细，属性和方法
3.2三大范式
设计原则：
建议设计的表尽量遵守三大范式。
第一范式：
要求表的每个字段必须是不可分割的独立单元
student:
name--违反第一范式
张小名|狗娃
sutdent：
nameold_name--符合第一范式
张小名狗娃
第二范式：
在第一范式的基础上，要求每张表只表达一个意思。
表的每个字段都和表的主键有依赖。
employee（员工）:
员工编号员工姓名部门名称订单名称--违反第二范式
员工表：
员工编号员工姓名部门名称
订单表：
订单编号订单名称--符合第二范式
第三范式：
在第二范式基础，要求每张表的主键之外的其他字段都只能和主键有直接决定依赖关系。
员工表：
员工编号（主键）员工姓名部门编号部门名--符合第二范式，违反第三范式（数据冗余高）
员工表：
员工编号（主键）员工姓名部门编号--符合第三范式（降低数据冗余）
部门表：
部门编号部门名
4关联查询（多表查询）
--**************二、关联查询（多表查询）****************----
--需求：
查询员工及其所在部门（显示员工姓名，部门名称）
--2.1交叉连接查询（不推荐。
产生笛卡尔乘积现象：
4*4=16，有些是重复记录）
SELECTempName,deptNameFROMemployee,dept;
--需求：
查询员工及其所在部门（显示员工姓名，部门名称）
--多表查询规则：
1）确定查询哪些表2）确定哪些哪些字段3）表与表之间连接条件（规律：
连接条件数量是表数量-1）
--2.2内连接查询：
只有满足条件的结果才会显示（使用最频繁）
SELECTempName,deptName--2）确定哪些哪些字段
FROMemployee,dept--1）确定查询哪些表
WHEREemployee.deptId=dept.id--3）表与表之间连接条件
--内连接的另一种语法
SELECTempName,deptName
FROMemployee
INNERJOINdept
ONemployee.deptId=dept.id;
--使用别名
SELECTe.empName,d.deptName
FROMemployeee
INNERJOINdeptd
ONe.deptId=d.id;
--需求：
查询每个部门的员工
--预期结果：
--软件开发部张三
--软件开发部李四
--应用维护部王五
--秘书部陈六
--总经办null
--2.2左[外]连接查询：
使用左边表的数据去匹配右边表的数据，如果符合连接条件的结果则显示，如果不符合连接条件则显示null
--（注意：
左外连接：
左表的数据一定会完成显示！
）
SELECTd.deptName,e.empName
FROMdeptd
LEFTOUTERJOINemployeee
ONd.id=e.deptId;
--2.3右[外]连接查询:
使用右边表的数据去匹配左边表的数据，如果符合连接条件的结果则显示，如果不符合连接条件则显示null
--（注意：
右外连接：
右表的数据一定会完成显示！
）
SELECTd.deptName,e.empName
FROMemployeee
RIGHTOUTERJOINdeptd
ONd.id=e.deptId;
--2.4自连接查询
--需求：
查询员工及其上司
--预期结果：
--张三null
--李四张三
--王五李四
--陈六王五
SELECTe.empName,b.empName
FROMemployeee
LEFTOUTERJOINemployeeb
ONe.bossId=b.id;
5存储过程
5.1什么是存储过程
存储过程，带有逻辑的sql语句
之前的sql没有条件判断，没有循环
存储过程带上流程控制语句（ifwhile）
5.2存储过程特点
1）执行效率非常快！
存储过程是在数据库的服务器端执行的！
！
！
2）移植性很差！
不同数据库的存储过程是不能移植。
5.3存储过程语法
--创建存储过程
DELIMITER$--声明存储过程的结束符
CREATEPROCEDUREpro_test（）--存储过程名称（参数列表）
BEGIN--开始
--可以写多个sql语句;--sql语句+流程控制
SELECT*FROMemployee;
END$--结束结束符
--执行存储过程
CALLpro_test（）;--CALL存储过程名称（参数）;
参数：
IN：
表示输入参数，可以携带数据带存储过程中
OUT：
表示输出参数，可以从存储过程中返回结果
INOUT：
表示输入输出参数，既可以输入功能，也可以输出功能
--**************三、存储过程*******************-
--声明结束符
--创建存储过程
DELIMITER$
CREATEPROCEDUREpro_test（）
BEGIN
--可以写多个sql语句;
SELECT*FROMemployee;
END$
--执行存储过程
CALLpro_test（）;
--3.1带有输入参数的存储过程
--需求：
传入一个员工的id，查询员工信息
DELIMITER$
CREATEPROCEDUREpro_findById（INeidINT）--IN:
输入参数
BEGIN
SELECT*FROMemployeeWHEREid=eid;
END$
--调用
CALLpro_findById（4）;
--3.2带有输出参数的存储过程
DELIMITER$
CREATEPR
升级会员 