-- 数据库的约束
-- 就是约束用户操作表的行为
-- 针对一些字段设置用户的限制!
-- 默认约束 :default(当用户没有插入指定的这个字段,启用默认约束--->默认值)
-- 创建一个张表emp
CREATE TABLE emp(
id INT , -- 编号
NAME VARCHAR(10) , -- 姓名
gender VARCHAR(3) DEFAULT '男' -- 性别
) ;
INSERT INTO emp VALUES(1,'张三','男'),(2,'文章','男'),(3,'高圆圆','女') ;
-- 插入数据的时候,忘记插入性别这个字段---性别就是null值
INSERT INTO emp(id,NAME) VALUES(4,'张三丰') ;-- 性别 没有值,默认约束给一个默认值
DROP TABLE emp ;
-- 非空约束 not null (限制用户直接插入null值)
CREATE TABLE emp(
id INT,
NAME VARCHAR(10) NOT NULL , -- 非空约束
age INT
) ;
INSERT INTO emp VALUES(1,'高圆圆',41),(2,'赵又廷',45) ;
-- 没有使用约束限制他,null值可以插入 (非法数据)
-- 加入非空约束:不能直接添加null值:
-- 错误码: 1048 Column 'name' cannot be null
INSERT INTO emp VALUES (3,NULL,22) ;
INSERT INTO emp(id,age) VALUES(4,25) ;-- 没有给值,启用默认约束 (1 row(s) affected, 1 warning(s)) 警告
DELETE FROM emp WHERE id = 4 ;
DROP TABLE emp ;
-- 修改表:将非空约束去掉
ALTER TABLE emp MODIFY NAME VARCHAR(10) ;
-- 修改表:添加非空约束
ALTER TABLE emp MODIFY NAME VARCHAR(10) NOT NULL ;
-- 唯一约束 unique (限定某个字段的不能重复,必须唯一的)
CREATE TABLE emp(
id INT UNIQUE, -- 唯一约束
NAME VARCHAR(10) ,
age INT
) ;
INSERT INTO emp VALUES(1,'张三',25),(2,'李四',30) ;
INSERT INTO emp VALUES(2,'王五',35) ;-- Duplicate entry '2' for key 'id' id重复了:值为2
UPDATE emp SET id = 3 WHERE NAME = '王五' ;
-- 删除唯一约束
-- atler table 表名 drop index 唯一约束所作用的字段名称
ALTER TABLE emp DROP INDEX id ;
-- 添加唯一约束
ALTER TABLE emp MODIFY id INT UNIQUE ;
-- 主键约束:primary key
-- 特点:非空+唯一
CREATE TABLE emp(
id INT PRIMARY KEY, -- 主键约束
NAME VARCHAR(10)
);
INSERT INTO emp VALUES(1,'高圆圆'),(2,'赵又廷'),(3,'张飞') ;
INSERT INTO emp VALUES(NULL,'耿明刚') ;-- Column 'id' cannot be null 非空约束限制
INSERT INTO emp VALUES(1,'杨德财') ; -- Duplicate entry '1' for key 'PRIMARY :唯一约束限制
INSERT INTO emp(NAME) VALUES('张佳宁') ;
DELETE FROM emp WHERE id = 0 ;
-- 删除主键约束
-- alter table 表名 drop PRIMARY KEY ;
ALTER TABLE emp DROP PRIMARY KEY ; -- 将唯一约束删除掉
-- 添加主键约束
ALTER TABLE emp MODIFY id INT PRIMARY KEY ;
UPDATE emp SET id = 4 WHERE NAME = '杨德财' ;
-- 自增长约束:auto_increment和主键一块使用
-- 一般都作用于非业务字段(xxid)
-- (默认值:0开始,当前插入数据之后,自增长的主键的值会依次自增1)
CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT,-- 主键+自增长
NAME VARCHAR(10),
address VARCHAR(20)
) ;
INSERT INTO emp(NAME,address) VALUES('高圆圆','北京市'),('赵又廷','西安市') ;
INSERT INTO emp VALUES(5,'文章','西安市') ;
INSERT INTO emp(NAME,address) VALUES('华子','延安市') ;
-- 在mysql 操作数据库:id的值如果是自增长的主键(可以一些sql语句获取主键的id值)
-- LAST_INSERT_ID() mysql的函数 :
SELECT LAST_INSERT_ID() ;-- 查询当前表中最后一次插入的自增主键的id值
SELECT * FROM emp ;
-- 外键约束(foreign key)
-- 创建一个员工表:employee
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号
NAME VARCHAR(10),-- 员工姓名
age INT , -- 员工年龄
gender VARCHAR(3), -- 员工性别
dept_name VARCHAR(10) -- 部门名称
) ;
INSERT INTO employee(NAME,age,gender,dept_name) VALUES('文章',20,'男','开发部'),
('李四',24,'女','测试部') ,('张佳宁',30,'女','开发部'),('赵又廷',35,'男','测试部'),(
'张三',26,'男','运维部') ;
-- 通过设计的这个表:查看员工的全部信息 存在问题
-- 员工所在的部门:存在数据冗余
/*
1 文章 开发部
3 张佳宁 开发部
2 李四 测试部
4 赵又廷 测试部
部门存在很多重复数据:很多都在一个部门中
解决方案:
单独将部门名称----放在一个表: 存储部门的表(部门表)
员工表中 给定字段:dept_id:部门id号
部门表:主键id: 部门编号
*/
DROP TABLE employee;
-- 创建一个部门表
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT, -- 部门编号
NAME VARCHAR(10) -- 部门名称
) ;
-- 插入三个部门
INSERT INTO dept(NAME) VALUES('开发部'),('测试部'),('运维部');
-- 创建员工表
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号
NAME VARCHAR(10),-- 员工姓名
age INT , -- 员工年龄
gender VARCHAR(3), -- 员工性别
dept_id INT, -- 部门编号
-- 声明一个外键约束
CONSTRAINT -- 声明
fk_dept_emp -- 外键名称
FOREIGN KEY (dept_id) -- 作用在从表的指定字段上
REFERENCES -- 关联
dept(id) -- 主表主键id
) ;
INSERT INTO employee(NAME,age,gender,dept_id) VALUES('文章',20,'男',1),
('李四',24,'女',2) ,('张佳宁',30,'女',1),('赵又廷',35,'男',2),(
'张三',26,'男',3) ;
-- 插入员工数据 -- 插入了一个不存在的部门(非法数据)
INSERT INTO employee(NAME,age,gender,dept_id) VALUES('杨德财',23,'男',4) ;
-- 需要使用外键约束
-- 当前员工表中部门dept_id 来自于部门dept的id字段
-- 外键需要添加在员工表dept_id字段 (外键的表---从表),部门表dept(主表)
-- 已经给员工表加入外键约束
INSERT INTO employee(NAME,age,gender,dept_id) VALUES('杨德财',23,'男',4) ;
-- 修改表--->删除外键名称
ALTER TABLE employee DROP FOREIGN KEY fk_dept_emp ;
-- 添加外键
ALTER TABLE employee
ADD
CONSTRAINT
fk_dept_emp
FOREIGN KEY
(dept_id)
REFERENCES
dept(id) ;
-- 有外键的表--->删除和修改都比较麻烦 (操作从表---主表)
-- CASCADE:级联操作
-- on update cascade 级联修改
-- 当修改主表字段,那么跟从表相关的信息一会被更改
-- on delete cascade 级联删除
-- 添加外键的同时--添加级联修改和级联删除
ALTER TABLE employee
ADD
CONSTRAINT
fk_dept_emp
FOREIGN KEY
(dept_id)
REFERENCES
dept(id)
ON UPDATE CASCADE -- 添加级联修改
ON DELETE CASCADE ; -- 添加级联删除
SELECT * FROM dept ;
SELECT * FROM employee;
-- 直接修改主表的数据,从表随之变化
-- 将name="开发部"的部门id = 4
UPDATE dept SET id = 4 WHERE NAME = '开发部' ;
-- 删除 部门表中3号部门
DELETE FROM dept WHERE id = 3 ;
-- 数据库的备份和还原
/*
方式1:
命令行的方式
数据库的备份
mysqldump.exe 指令 :备份指令
进入dos
输入 :mysqldump -uroot -p密码 数据库名 > 路径
mysqldump -uroot -p123456 ee_2104 > D:\JavaEE_2104\EE_day29\code\my.sql
将ee_2104这个库中所有表都存储在D:\JavaEE_2104\EE_day29\code\my.sql文件中
数据库还原
1)通过dos先登录mysql
2)删除原来备份过的数据库
3)创建空数据库
4)source 指定之前备份的路径的sql文件 : 自动的进行创建以及表中数据进行插入...
source D:\JavaEE_2104\EE_day29\code\my.sql
方式2:图形界面化:直观简单
sqlYog /navicat
备份:
鼠标选择指定数据库,右键--->备份--->以sql文件转存数据库---指定路径
还原:
1)删除当前数据库
2)创建新的数据库
3)选择新建的数据库名---右键--->导入--->以sql转储文件数据库
*/
USE myee_2104_;
SELECT * FROM dept ;
SELECT * FROM employee;
CREATE DATABASE ee_2104_mysql;
/*
数据库范式:就是设计关系型数据库使用的一种规范要求!
常用的范式:三大范式
1NF:第一范式
在设计关系数据数据库是,最低要求必须遵循1NF
表中的每一列是不可再拆分的!(独立的原子项)
2NF:第二范式
在1NF的基础上,非主键字段(属性字段)必须完全依赖于主键字段
3NF:第三范式
在第二范式基础上,非主键字段(主键字段和非主键字段)之间不能产生传递依赖!
A字段-->B字段--->C字段
A字段--->C字段 (传递依赖)
*/
-- 多表查询
-- 多个表查询相关字段
DROP TABLE employee ;
DROP TABLE dept ;
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT, -- 部门编号
NAME VARCHAR(10) -- 部门名称
) ;
INSERT INTO dept(NAME) VALUES('开发部'),('财务部'),('市场部') ;
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号
NAME VARCHAR(10), -- 员工名称
gender VARCHAR(3), -- 员工的性别
salary INT, -- 员工工资
join_date DATE,-- 入职日期
dept_id INT, -- 部门id
FOREIGN KEY (dept_id) -- 声明外键: 语法 可以不带constraint 外键名称
REFERENCES dept(id)
) ;
INSERT INTO employee(NAME,gender,salary,join_date,dept_id)
VALUES('文章','男',8000,'2019-11-11',1),
('马伊琍','女',9500,'2020-12-12',2) ,
('姚笛','女',13000,'2020-12-13',2) ,
('刘备','男',15000,'2013-11-11',1) ,
('张飞','男',8500,'2014-10-12',3) ;
-- 多表查询(必须满足一定条件查询)
-- 内连接
-- 需求:查询员工表和部门表所有信息
-- 问题:笛卡尔乘积(A表的记录*B表记录) = 结果集数据
-- 员工表5条,部门表3条--->15条记录 (没有给设置 条件导致的笛卡尔乘积!)
SELECT
*
FROM
employee,
dept ;
-- 内连接之显示内连接:就是通过where 设置条件 (最简单的)
/*
内连接:
1)查询哪个表 查询dept 和employee
2)查询这个表的哪个字段 部门表的部门名称 和员工的所有数据
3)这些表有没有关系条件 员工表的deptid=部门表id
*/
-- 需求:查询员工表的所有数据同时关联查询部门的表名称
SELECT
employee.*,
dept.id,
dept.name
FROM
employee ,-- 员工表
dept -- 部门表
WHERE
employee.dept_id = dept.id;
-- 优化:查询时候,如果表名比较长,使用别名
SELECT
e.*,
d.name
FROM
employee e ,-- 员工表
dept d-- 部门表
WHERE
e.dept_id = d.id ;
-- 隐式内连接
-- 关键字;inner(省略) join on:作为多表的查询条件(连接条件)
-- 需求:查询员工表的(姓名,性别,工资,入职日期)同时关联查询部门的表名称
SELECT
e.name '员工姓名',
e.gender,
e.salary,
e.join_date,
d.name '部门名称'
FROM
employee e
INNER JOIN
dept d
ON
e.dept_id = d.id ;
/*
隐式内连接和显示内连接 :内连接查询
优先采用显示内连接 :where语句 ,然后其次使用隐式内连接
*/
INSERT INTO employee(NAME,gender,salary,join_date)
VALUES('高圆圆','女','18000','2011-9-10') ;
-- 外连接(推荐左外!)
-- 需求:查询员工表的所有信息以及他们所在的部门名称
-- 左外:(推荐)select 字段列表 from 表名1 left outer(可以省略) join 表名2 on 表名1和表名2的连接条件
-- 将左表中的全部数据以及交集(符号连接条件)的数据查询出来
SELECT
t1.name '姓名',
t1.gender,
t1.salary,
t1.join_date,
t2.name '部门'
FROM
employee t1
LEFT OUTER JOIN
dept t2
ON
t1.dept_id = t2.id ;
-- 右外:将右表的数据全部查询以及多个表之间的交集数据查询出来
-- 格式: select 字段列表 from 表名1 right outer join 表名2 on 连接条件;
-- 查询所有的部门表以及关联的员工所有信息
SELECT
*
FROM
dept t1
RIGHT JOIN
employee t2
ON
t1.id = t2.dept_id ;
-- 子查询
-- select语句嵌套select语句
-- 多行单例
-- in语句---使用or 进行或的关系
-- 多行多例
-- 需求:查询最高工资的员工信息
-- 1)查询最高工资是多少
-- select max(salary) from employee ;-- 18000
-- 2)是18000的工资员工信息
-- select * from employee where salary = 18000 ;
-- 带入之后
SELECT
*
FROM
employee
WHERE
salary = (SELECT MAX(salary) FROM employee ) ;
-- 需求:查询小于平均工资的员工信息
-- 1) 查询平均工资
-- select avg(salary) from employee ;
-- 2)查询小于12000的员工信息
-- select * from employee where salary < 12000 ;
SELECT
t.name,
t.salary,
t.gender,
t.join_date
FROM
employee t
WHERE
t.salary < (SELECT AVG(salary) FROM employee) ;
-- 需求:查询员工在财务部或者是市场部的信息
-- 1)查询出财务部和市场部的部门id是多少
SELECT
dept.id
FROM
dept
WHERE
NAME = '财务部' OR NAME = '市场部' ;
-- 2)查询出在 2号部门或者3号部门的员工
/*
select
name,
gender,
salary,
join_date,
dept_id
from
employee
where
dept_id = 2 or dept_id = 3 ; -- dept_id in (2,3) ;
*/
-- 优化
SELECT
NAME,
gender,
salary,
join_date,
dept_id
FROM
employee
WHERE
dept_id
IN (2,3) ;
-- 最终写法:
SELECT
NAME,
gender,
salary,
join_date,
dept_id
FROM
employee
WHERE
dept_id
IN
(
SELECT
dept.id
FROM
dept
WHERE
NAME = '财务部' OR NAME = '市场部'
) ;
UPDATE employee SET dept_id = 3 WHERE id = 6 ;
-- 子查询的第三种情况:
-- 多行多列
/*
将某个查询的结果集当做一张虚表 ,然后使用这个虚表和另外的其他表建立连接查询
*/
-- 需求:查询员工入职日期大于"2011-9-10"后的所有的员工信息以及的部门信息(部门名称)
-- 1)查询大于2011-9-10的员工
SELECT
*
FROM
employee
WHERE
join_date > '2011-09-10' ;
-- 最终的写法:将上面1)查询的结果集当做虚表,给它起别名和部门表建立连接查询 (左外连接)
SELECT
t1.*,
t2.name '部门'
FROM
(SELECT
*
FROM
employee
WHERE
join_date > '2011-09-10') t1
LEFT OUTER JOIN
dept t2
ON
t1.dept_id = t2.id ;
-- 使用传统内连接:显示内连接 多去使用where条件查询(sql优化的一种)
SELECT
t1.name '员工名',
t1.gender '性别',
t1.salary '工资',
t1.join_date '入职时间',
t2.name '部门'
FROM
employee t1,
dept t2
WHERE
t1.join_date > '2011-09-10'
AND
t1.dept_id = t2.id ;
SELECT * FROM dept ;
SELECT * FROM employee ;
Java学习日志Day29_数据库的约束_备份和还原_三大设计范式_多表查询
最新推荐文章于 2022-04-21 21:56:38 发布