MySql数据库约束基础.docx
- 文档编号:26270280
- 上传时间:2023-06-17
- 格式:DOCX
- 页数:17
- 大小:19.63KB
MySql数据库约束基础.docx
《MySql数据库约束基础.docx》由会员分享,可在线阅读,更多相关《MySql数据库约束基础.docx(17页珍藏版)》请在冰豆网上搜索。
MySql数据库约束基础
USEday16;
--*************一、数据约束********************----
--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,'王五','广州番禺');
SELECT*FROMstudent;
--1.2非空
--需求:
gender字段必须有值(不为null)
CREATETABLEstudent(
idINT,
NAMEVARCHAR(20),
genderVARCHAR
(2)NOTNULL--非空
)
--非空字段必须赋值
INSERTINTOstudent(id,NAME)VALUES(1,'李四');
--非空字符不能插入null
INSERTINTOstudent(id,NAME,gender)VALUES(1,'李四',NULL);
SELECT*FROMstudent;
--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');
SELECT*FROMstudent;
--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
--1.5自增长
CREATETABLEstudent(
idINT(4)ZEROFILLPRIMARYKEYAUTO_INCREMENT,--自增长,从0开始ZEROFILL零填充
NAMEVARCHAR(20)
)
--自增长字段可以不赋值,自动递增
INSERTINTOstudent(NAME)VALUES('张三');
INSERTINTOstudent(NAME)VALUES('李四');
INSERTINTOstudent(NAME)VALUES('王五');
SELECT*FROMstudent;
--不能影响自增长约束
DELETEFROMstudent;
--可以影响自增长约束
TRUNCATETABLEstudent;
--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:
aforeignkey
constraintfails(`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;
--级联修改(修改)
--直接修改部门
UPDATEdeptSETid=5WHEREid=4;
--级联删除
--直接删除部门
DELETEFROMdeptWHEREid=1;
--**************二、关联查询(多表查询)****************----
--需求:
查询员工及其所在部门(显示员工姓名,部门名称)
--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;
SELECT*FROMemployee;
SELECT*FROMdept;
--添加上司ID
ALTERTABLEemployeeADDbossIdINT;
UPDATEemployeeSETbossId=1WHEREid=2;
UPDATEemployeeSETbossId=2WHEREid=3;
UPDATEemployeeSETbossId=3WHEREid=4;
--**************三、存储过程*******************-
--声明结束符
--创建存储过程
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$
CREATEPROCEDUREpro_testOut(OUTstrVARCHAR(20))--OUT:
输出参数
BEGIN
--给参数赋值
SETstr='helljava';
END$
--删除存储过程
DROPPROCEDUREpro_testOut;
--调用
--如何接受返回参数的值?
?
--***mysql的变量******
--全局变量(内置变量):
mysql数据库内置的变量(所有连接都起作用)
--查看所有全局变量:
showvariables
--查看某个全局变量:
select@@变量名
--修改全局变量:
set变量名=新值
--character_set_client:
mysql服务器的接收数据的编码
--character_set_results:
mysql服务器输出数据的编码
--会话变量:
只存在于当前客户端与数据库服务器端的一次连接当中。
如果连接断开,那么会话变量全部丢失!
--定义会话变量:
set@变量=值
--查看会话变量:
select@变量
--局部变量:
在存储过程中使用的变量就叫局部变量。
只要存储过程执行完毕,局部变量就丢失!
!
--1)定义一个会话变量name,2)使用name会话变量接收存储过程的返回值
CALLpro_testOut(@NAME);
--查看变量值
SELECT@NAME;
--3.3带有输入输出参数的存储过程
DELIMITER$
CREATEPROCEDUREpro_testInOut(INOUTnINT)--INOUT:
输入输出参数
BEGIN
--查看变量
SELECTn;
SETn=500;
END$
--调用
SET@n=10;
CALLpro_testInOut(@n);
SELECT@n;
--3.4带有条件判断的存储过程
--需求:
输入一个整数,如果1,则返回“星期一”,如果2,返回“星期二”,如果3,返回“星期三”。
其他数字,返回“错
误输入”;
DELIMITER$
CREATEPROCEDUREpro_testIf(INnumINT,OUTstrVARCHAR(20))
BEGIN
IFnum=1THEN
SETstr='星期一';
ELSEIFnum=2THEN
SETstr='星期二';
ELSEIFnum=3THEN
SETstr='星期三';
ELSE
SETstr='输入错误';
ENDIF;
END$
CALLpro_testIf(4,@str);
SELECT@str;
--3.5带有循环功能的存储过程
--需求:
输入一个整数,求和。
例如,输入100,统计1-100的和
DELIMITER$
CREATEPROCEDUREpro_testWhile(INnumINT,OUTresultINT)
BEGIN
--定义一个局部变量
DECLAREiINTDEFAULT1;
DECLAREvsumINTDEFAULT0;
WHILEi<=numDO
SETvsum=vsum+i;
SETi=i+1;
ENDWHILE;
SETresult=vsum;
END$
DROPPROCEDUREpro_testWhile;
CALLpro_testWhile(100,@result);
SELECT@result;
USEday16;
--3.6使用查询的结果赋值给变量(INTO)
DELIMITER$
CREATEPROCEDUREpro_findById2(INeidINT,OUTvnameVARCHAR(20))
BEGIN
SELECTempNameINTOvnameFROMemployeeWHEREid=eid;
END$
CALLpro_findById2(1,@NAME);
SELECT@NAME;
USEday15;
SELECT*FROMstudent2;
--练习:
编写一个存储过程
如果学生的英语平均分小于等于70分,则输出'一般'
如果学生的英语平均分大于70分,且小于等于90分,则输出‘良好’
如果学生的英语平均分大于90分,则输出‘优秀’
DELIMITER$
CREATEPROCEDUREpro_testAvg(OUTstrVARCHAR(20))
BEGIN
--定义局部变量,接收平均分
DECLAREsavgDOUBLE;
--计算英语平方分
SELECTAVG(english)INTOsavgFROMstudent2;
IFsavg<=70THEN
SETstr='一般';
ELSEIFsavg>70ANDsavg<=90THEN
SETstr='良好';
ELSE
SETstr='优秀';
ENDIF;
END$
CALLpro_testAvg(@str);
SELECT@str;
--************四、触发器*****************
SELECT*FROMemployee;
--日志表
CREATETABLEtest_log(
idINTPRIMARYKEYAUTO_INCREMENT,
contentVARCHAR(100)
)
--需求:
当向员工表插入一条记录时,希望mysql自动同时往日志表插入数据
--创建触发器(添加)
CREATETRIGGERtri_empAddAFTERINSERTONemployeeFOREACHROW--当往员工表插入一条记录时
INSERTINTOtest_log(content)VALUES('员工表插入了一条记录');
--插入数据
INSERTINTOemployee(id,empName,deptId)VALUES(7,'扎古斯',1);
INSERTINTOemployee(id,empName,deptId)VALUES(8,'扎古斯2',1);
--创建触发器(修改)
CREATETRIGGERtri_empUpdAFTERUPDATEONemployeeFOREACHROW--当往员工表修改一条记录时
INSERTINTOtest_log(content)VALUES('员工表修改了一条记录');
--修改
UPDATEemployeeSETempName='eric'WHEREid=7;
--创建触发器(删除)
CREATETRIGGERtri_empDelAFTERDELETEONemployeeFOREACHROW--当往员工表删除一条记录时
INSERTINTOtest_log(content)VALUES('员工表删除了一条记录');
--删除
DELETEFROMemployeeWHEREid=7;
SELECT*FROMemployee;
SELECT*FROMtest_log;
--***********五、mysql权限问题****************
--mysql数据库权限问题:
root:
拥有所有权限(可以干任何事情)
--权限账户,只拥有部分权限(CURD)例如,只能操作某个数据库的某张表
--如何修改mysql的用户密码?
--password:
md5加密函数(单向加密)
SELECTPASSWORD('root');--*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B
--mysql数据库,用户配置:
user表
USEmysql;
SELECT*FROMUSER;
--修改密码
UPDATEUSERSETPASSWORD=PASSWORD('123456')WHEREUSER='root';
--分配权限账户
GRANTSELECTONday16.employeeTO'eric'@'localhost'IDENTIFIEDBY'123456';
GRANTDELETEONday16.employeeTO'eric'@'localhost'IDENTIFIEDBY'123456';
--******六,mysql备份和还原********
练习:
编写一个存储过程
如果学生的英语平均分小于等于70分,则输出'一般'
如果学生的英语平均分大于70分,且小于等于90分,则输出‘良好’
如果学生的英语平均分大于90分,则输出‘优秀’
需求:
1)一个工程可以由多个职工负责
2)一个职工可以负责多个工程
3)职工的工资率由职务决定
工程职工工时表
工程号工程名称职工号姓名职务薪水工时
工程表:
工程ID工程号工程名称
1花园酒店
2立交桥
职工表:
职工ID职工号姓名职务ID
11张三
22李四
职务表:
职务ID职务名称薪水
中间表(工程职工表)
职工ID工程ID工时
1113
1220
21
22
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- MySql 数据库 约束 基础