数据库--MYSQL高级（多表），数据库的完整性，约束，数据类型，多表实现 CRUD 操作

一敲代码就头疼

已于 2022-04-07 18:06:20 修改

阅读量1.2k

点赞数 6

文章标签：数据库开发 mysql

于 2022-04-07 00:12:52 首次发布

本文链接：https://blog.csdn.net/weixin_51426897/article/details/124003378

版权

数据库的完整性

用来保证存放到数据库中的数据是有效的 , 即数据的有效性和准确性确保数据的完整性 = 在创建表时给表中添加约束。

完整性的分类：

- 实体完整性 ( 行完整性 ):

- 域完整性 ( 列完整性 ):

- 引用完整性 ( 关联表完整性 ):

主键约束： primary key

唯一约束： unique [key]

非空约束： not null

默认约束： default

自动增长： auto_increment

外键约束 : foreign key

建议这些约束应该在创建表的时候设置， 多个约束条件之间使用空格间隔

实体完整性

实体：即表中的一行 ( 一条记录 ) 代表一个实体（ entity ）

实体完整性的作用：标识每一行数据不重复。

约束类型：

主键约束（ primary key ）

唯一约束 (unique)

自动增长列(auto_increment)

主键约束（primary key）

注：每个表中要有一个主键。

特点：数据唯一，且不能为 null

唯一约束(unique)

特点：数据不能重复。

自动增长列(auto_increment)

自增长数据类型为数值的字段，可以通过使用auto_increment 来完成自增长的设置

主键自增长，当数据插入时，不进行主键的数据插入，则主键的内容会通过自动增长变量来赋值

在主键约束关键字后面加入AUTO_INCREMENT

约束的作用是对表中的数据进行限制，可以更好的保证数据的准确性，有效性和完整性

-- 主键约束 primary key

-- 不允许空且不能重复

-- 非空约束 not null

-- 限制一个字段的数据不能为空

-- 唯一约束 unique

-- 这一列的数据不能重复，前提是不为空

-- 创建person表，三个字段，id 主键自增长，name 唯一，age 非空
CREATE TABLE PERSON(
	ID INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(5) UNIQUE,
	AGE INT NOT NULL
);

数据类型

常用数据类型

int 整型数字

bigint 大型整型数字

double 浮点型数字 score double(5,2);小数点前5位，小数点后2位

date 日期只有年月日 yyyy-MM-dd

datetime 日期加时间年月日时分秒 yyyy-MM-dd HH:mm:ss

timestamp 时间戳 13位的数字从1970年1-1 到现在的毫秒数

varchar 可变长度字符串 4000是数据库存储最大的值 name varchar(20); name 最大使用20个字符

如果有很大的数据不会存储数据库里，把它作为文件存在一个位置，把文件的地址存在数据库里

数值类型

MySQL 支持所有标准 SQL 数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC)，以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持 MyISAM、MEMORY、InnoDB 和 BDB表。

作为 SQL 标准的扩展，MySQL 也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

注意：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

域完整性

域完整性的作用：限制此单元格的数据正确，不对照此列的其它单元格比较

域代表当前单元格

域完整性约束：数据类型非空约束（ not null ）默认值约束 (default)

列级约束

-- 非空约束 not null

-- 创建表格或修改表格时，在列名声明的后边加入约束关键字

-- 默认值

-- 创建表格或修改表格时，在列名声明的后边加入default 值

CREATE TABLE PEOPLE(
	ID INT NOT NULL,
	NAME VARCHAR(20) DEFAULT "嘿嘿"
);

引用完整性

外键约束：FOREIGN KEY

creat table(
	字段列表，
   外键字段是存别的表的主键值的列
	constraint 约束名 foreign key(外键字段) references 主表名(字段)
);

CREATE TABLE SA_DEPT(
	ID INT,
	NAME VARCHAR(20),
	CONSTRAINT SA_DEPT_ID PRIMARY KEY(ID)
);
INSERT INTO SA_DEPT VALUES(1,"研发部"),(2,"测试部"),(3,"公关部");
-- ---------------------------------------
CREATE TABLE SA_EMP(
	ID INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	SALARY INT,
	DEPT_ID INT,	
	CONSTRAINT S_EMP_DEPT_ID_FK FOREIGN KEY(DEPT_ID) REFERENCES SA_DEPT(ID)
);
ALTER TABLE SA_EMP DROP FOREIGN KEY S_EMP_DEPT_ID_FK;
DROP TABLE SA_EMP;
DROP TABLE SA_DEPT;
INSERT INTO SA_EMP(NAME,SALARY,DEPT_ID) VALUES
("张三",10000,3),("李四",90000,2),("王五",18000,3),("赵一",16000,12);
TRUNCATE TABLE S_EMP;
SELECT * FROM SA_DEPT;
SELECT * FROM SA_EMP;

多表查询

多个表之间是有关系的，那么关系靠谁来维护 ?

多表约束：外键列

多表的关系

一对多 / 多对一关系

客户和订单，分类和商品，部门和员工 .

一对多建表原则：在多的一方创建一个字段，字段作为外键指向一的一方的主键 .

多对多关系

学生和课程

多对多关系建表原则：需要创建第三张表 , 中间表中至少两个字段，这两个字段分别作为外键指向各自一 方的主键 .

一对一关系

在实际的开发中应用不多 . 因为一对一可以创建成一张表 .

两种建表原则：

唯一外键对应：假设一对一是一个一对多的关系，在多的一方创建一个外键指向一的一方的主键，将外 键设置为 unique.

主键对应：让一对一的双方的主键进行建立关系 .

SELECT 列名列表 FROM 列名列表;

笛卡尔积：当进行多表查询时，多表之间的行数据会自然匹配，查询的结果是自然匹配的乘积

多表查询必须去除笛卡尔积产生的无用数据

因为多表查询是通过where条件消除无用数据，所以当迪科尔基过大时会对查询性能有较大的影响，尽量避免三表或三表以上的多表查询

隐式内连接：通过where条件完成筛出笛卡尔积

显示内连接使用固定的语法格式

SELECT * FROM S_EMP,S_DEPT ;
SELECT * FROM S_EMP,S_DEPT WHERE S_EMP.DEPT_ID=S_DEPT.ID;
-- 查询员工姓名，工资，部门名称
-- 隐式内连接
SELECT S_EMP.NAME,SALARY,S_DEPT.NAME FROM S_EMP, S_DEPT WHERE S_EMP.DEPT_ID=S_DEPT.ID;
SELECT E.NAME,SALARY,D.NAME FROM S_EMP E, S_DEPT D WHERE E.DEPT_ID=D.ID;
-- 显式内连接 select 字段列表 from 表名1 [inner] join 表名2 ON 消除笛卡尔积的条件;
SELECT S_EMP.NAME,SALARY,S_DEPT.NAME FROM S_EMP INNER JOIN S_DEPT ON S_EMP.DEPT_ID=S_DEPT.ID;
-- 通过内连接，实现自连接
-- 有时候存在一些特殊的表格，包含至少两层含义
-- select 列名列表 from 表名 别名1, 表名 别名2 where 条件；
-- 查询管理者的姓名，工资，职务，通过员工的管理者和管理者的编号
SELECT DISTINCT M.ENAME,M.SAL,M.JOB FROM EMP E,EMP M WHERE E.MGR=M.EMPNO;
SELECT * FROM salgrade;
-- 以上都是等值连接，有些条件是需要使用非等值连接的
-- 查询员工姓名，薪资，职务，薪资等级
SELECT ENAME,SAL,JOB,GRADE FROM EMP,SALGRADE WHERE SAL>LOSAL AND SAL<HISAL ORDER BY GRADE;

外连接左外连接/右外连接

左外连接语法

select 字段列表 from 表名1(基表) left [outer] join 表名2(参考表) on 条件;

右外连接语法

select 字段列表 from 表名1(参考表) right [outer] join 表名2(基表) on 条件;

基表

-- 基表的内容会全部查询到

-- 参考表

-- 在查询时，只有多表查询满足查询条件的数据才会被查询到，不满足条件的使用null数据来跟基表数据匹配

普通查询和外连接查询的区别

SELECT * FROM S_EMP,S_DEPT;
SELECT * FROM S_EMP,S_DEPT WHERE S_EMP.`DEPT_ID`=S_DEPT.`ID`;

左外连接语法 
select 字段列表 from 表名1(基表) left [outer] join 表名2(参考表) on 条件;
SELECT * FROM S_EMP LEFT OUTER JOIN S_DEPT ON S_EMP.`DEPT_ID`=S_DEPT.`ID`;

右外连接语法 
select 字段列表 from 表名1(参考表) right [outer] join 表名2(基表) on 条件;
SELECT * FROM S_DEPT RIGHT OUTER JOIN S_EMP ON S_EMP.`DEPT_ID`=S_DEPT.`ID`;

子查询

一个select语句中包含另一个完整的select语句。

子查询就是嵌套查询，即 SELECT 中包含 SELECT ，如果一条语句中存在两个，或两个以上 SELECT ，那么就是子查询语句了。

子查询有两种不同的查询结果，一是查询结果是单行的，二是查询结果是多行的

-- 如果查询结果是单行单列的，可以用于查询条件

-- 将子查询嵌套在查询条件中

-- 格式：

select 字段列表 from 表名 where 条件(SQL语句);

SELECT ENAME FROM EMP WHERE sal=(SELECT MAX(SAL) FROM EMP);
-- 查询部门编号最大的部门的所有员工信息（ename，sal）
SELECT ENAME,SAL FROM EMP WHERE DEPTNO=(SELECT MAX(DEPTNO) FROM EMP);
-- 查询大于公司平均薪资的人的ename，sal
SELECT ENAME,SAL FROM EMP WHERE sal>(SELECT AVG(SAL) FROM EMP);

-- 子查询的结果是多行单列。可以用作查询条件，用in使用
SELECT * FROM S_EMP WHERE ID IN (SELECT ID FROM S_DEPT WHERE NAME="研发部" OR NAME="公关部");

-- 子查询的结果是多行多列的。子查询可以作为一张虚拟表参与查询
SELECT * FROM (SELECT * FROM EMP WHERE HIREDATE>"1985-1-1") TABLE1,DEPT WHERE TABLE1.`DEPTNO`=DEPT.`DEPTNO`;

练习题

数据表:

雇员表(employee):雇员编号(empid,主键)，姓名(name)，性别 (sex)，职称(title)，出生日期(birthday)，所属部(depid)

部门表(department):部门编号(depid,主键)，部门名称(depname)

工资表(salary):雇员编号(empid)，基本工资(basesalary)，职务工资(titlesalary)，扣除(deduction)

需求:

1. 修改表结构，在部门表中添加部门简介字段

2. 将李四的职称改为“工程师”,并将她的基本工资改成 2000，职务工资为 700

3. 删除人事部门的部门记录

4. 查询出每个雇员的雇员编号，实发工资，应发工资

5. 查询姓张且年龄小于 40 的员工记录

6. 查询雇员的雇员编号，姓名，职称，部门名称，实发工资

7. 查询销售部门的雇员姓名，工资

8. 统计各职称的人数

9. 统计各部门的部门名称，实发工资总和，平均工资

10. 查询比销售部门所有员工基本工资都高的雇员姓名

-- 雇员表
CREATE TABLE employee(
	empid INT PRIMARY KEY AUTO_INCREMENT, #雇员编号
	NAME VARCHAR(10),#雇员姓名
	sex VARCHAR(5),#雇员性别
	title VARCHAR(10),#雇员职称
	birthday DATE,#雇员出生日期
	depid INT #雇员部门id
);
INSERT INTO employee(NAME,sex,title,birthday,depid) VALUES
('张三','男','研究员','2000-01-01',1),
('李四','女','工程师','2001-01-01',1),
('王五','男','经理','2000-12-01',2),
('赵六','男','讲师','1990-12-01',3),
('周七','女','助理','1980-12-01',4);
INSERT INTO employee(NAME,sex,title,birthday,depid) VALUES
('张飞','男','助理','1990-12-01',4);

CREATE TABLE department(
	depid INT PRIMARY KEY AUTO_INCREMENT,#部门编号
	depname VARCHAR(20)#部门名称
);
INSERT INTO department(depname) VALUES
('人事部'),('技术部'),('销售部'),('公关部');

CREATE TABLE salary(
	empid INT,#雇员id
	basesalary DOUBLE,#基本工资
	titlesalary DOUBLE,#职务工资
	deduction DOUBLE #扣除
);
INSERT INTO salary(empid,basesalary,titlesalary,deduction) VALUES 
(1,1000,300,100),
(2,1300,200,100),
(3,3000,100,100),
(4,2400,400,100),
(5,4000,100,200);
INSERT INTO salary(empid,basesalary,titlesalary,deduction) VALUES 
(6,7000,200,900);

-- 1. 修改表结构，在部门表中添加部门简介字段
ALTER TABLE department ADD info TINYTEXT;
ALTER TABLE department CHANGE info info VARCHAR(50);
SELECT * FROM department

-- 2. 将李四的职称改为“工程师”,并将她的基本工资改成 2000，职务工资为 700
UPDATE employee,salary SET title='工程师',basesalary=2000,titlesalary=700
WHERE employee.`empid`=salary.`empid` AND NAME='李四';
SELECT * FROM employee,salary WHERE employee.`empid`=salary.`empid`
AND NAME='李四';

-- 3. 删除人事部门的部门记录
DELETE department,employee,salary FROM department,employee,salary
WHERE department.`depid`=employee.`depid` AND salary.`empid`=employee.`empid`
AND department.`depname`='人事部';
SELECT * FROM department,employee,salary WHERE 
department.`depid`=employee.`depid` AND salary.`empid`=employee.`empid`

-- 4. 查询出每个雇员的雇员编号，实发工资，应发工资
SELECT empid,  basesalary+titlesalary '实发工资',
 basesalary+titlesalary-deduction '应发工资' FROM salary

-- 5. 查询姓张且年龄小于 40 的员工记录
SELECT * FROM employee
SELECT * FROM employee WHERE  employee.`name` LIKE '张%' AND birthday >
 DATE_ADD(NOW(),INTERVAL -40 YEAR);

-- 6. 查询雇员的雇员编号，姓名，职称，部门名称，实发工资
SELECT e.`empid`,e.`name`,e.`title`,d.`depname`,s.`basesalary`+s.`titlesalary` '实发工资'
FROM employee e,department d,salary s 
WHERE e.`depid`=d.`depid`AND s.`empid`=e.`empid`

-- 7. 查询销售部门的雇员姓名，工资
SELECT department.`depname`,employee.`name`,salary.`basesalary`,salary.`titlesalary`
 FROM department,employee,salary WHERE
department.`depid`=employee.`depid` AND employee.`empid`=salary.`empid`
AND department.`depname`='销售部'

-- 8. 统计各职称的人数
SELECT title ,COUNT(*) FROM employee GROUP BY title

-- 9. 统计各部门的部门名称，实发工资总和，平均工资
SELECT d.depname,SUM(s.`basesalary`+s.`titlesalary`-s.deduction) '实发工资总和',
AVG(s.`basesalary`+s.`titlesalary`-s.deduction) '平均工资'
FROM employee e,department d,salary s
WHERE e.`depid`=d.`depid`AND s.`empid`=e.`empid`
GROUP BY d.depname 
SELECT * FROM employee

-- 10. 查询比销售部门所有员工基本工资都高的雇员姓名
SELECT NAME FROM employee e , salary s 
WHERE  s.`empid`=e.`empid` AND
s.basesalary>
(SELECT  MAX(basesalary) FROM salary s,department d ,employee e WHERE 
e.`depid`=d.`depid`AND s.`empid`=e.`empid`
AND d.depname='销售部')

一敲代码就头疼

关注

6
点赞
踩
5

收藏

觉得还不错? 一键收藏
2
评论
数据库--MYSQL高级（多表），数据库的完整性，约束，数据类型，多表实现 CRUD 操作

数据库的完整性用来保证存放到数据库中的数据是有效的,即数据的有效性和准确性确保数据的完整性 = 在创建表时给表中添加约束。完整性的分类：- 实体完整性(行完整性): - 域完整性(列完整性): - 引用完整性(关联表完整性): 主键约束：primary key 唯一约束：unique [key] 非空约束：not null 默认约束：default 自动增长：auto_increment 外键约束: foreign key建议这些约束应该在创建表的时候设
复制链接

扫一扫