mysql约束2表参数约束_MySQL_2_约束和多表查询

最新推荐文章于 2022-04-19 22:23:25 发布

weixin_39620273

最新推荐文章于 2022-04-19 22:23:25 发布

阅读量247

点赞数

文章标签： mysql约束2表参数约束

本文链接：https://blog.csdn.net/weixin_39620273/article/details/113463728

版权

MySQL数据库

第四章数据的完整性

作用：保证用户输入的数据保存到数据库中是正确的。

确保数据的完整性=在创建表时给表中添加约束。

完整性的分类：

实体完整性；

域完整性；

引用完整性；

4.1 实体完整性

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

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

约束类型：

主键约束(primary key)

唯一约束(unique)

自动增长列(auto_increment)

4.1.1 主键约束(primary key)

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

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

示例：

第一种添加方式：

CREATE TABLE studdent(

id int primary key,

name varchar(50)

);

第二种添加方式：此种方式优势在于可以创建联合主键

CREATE TABLE student(

id int,

name varchar(50),

primary key(id)

);

CREATE TABLE student(

classid int,

stuid int,

name varchar(50),

primary key(classid,stuid)

);

第三种添加方式：先创建表，然后再添加主键

CREATE TABLE studdent(

id int;

name varchar(50)

);

ALTER TABLE student ADD PRIMARY KEY(id);

4.1.2 唯一约束(unique)

特点：数据不能重复,可以为null

CREATE TABLE student(

id int primary key,

name varchar(50) unique

);

4.1.3 自动增长列(auto_increment)

sqlserver数据库(identity)oracle数据库(sequence)

给主键添加自动增长的数值，列只能是整数类型

CREATE TABLE student(

id int primary key auto_increment,

name varchar(50)

);

INSERT INTO student(name) values('tom');

修改自动增长的初始值：

ALTER TABLE tbl_name AUTO_INCREMENT = n;

4.2 域完整性

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

域代表当前单元格

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

check约束(MySQL不支持)check(sex='男' or sex='女')

4.2.1 数据类型

数值类型

类型

大小

范围(有符号)

范围(无符号)

用途

TINYINT

1字节

(-128，127)

(0，255)

小整数值

SMALLINT

2字节

(-32768，32767)

(0，65535)

大整数值

MEDIUMINT

3字节

(-8388608，8388607)

(0，16777215)

大整数值

INT或INTEGER

4字节

(-2147483648，2147483647)

(0，4294967295)

大整数值

BIGINT

8字节

(-9233372036854775808，9233372036854775807)

(0，18446744073709551615)

极大整数值

FLOAT

4字节

单精度浮点数值

DOUBLE

8字节

双精度浮点数值

DOUBLE(M,D)

8字节，M表示长度，D表示小数位数

同上，受M和D的约束

双精度浮点数

DECIMAL(M,D)

DECIMAL(M，D)，如果M>D,为M+2否则为D+2

依赖于M和D的值，M最大值为65

小数值

日期类型：

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

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

类型

大小(字节)

范围

格式

用途

DATE

1000-01-01/9999-12-31

YYYY-MM-DD

日期值

TIME

‘-838:59:59’/‘838:59:59’

HH:MM;SS

时间值或持续时间

YEAR

1901/2155

YYYY

年份值

DATETIME

1000-01-01 00:00:00/9999-12-31 23:59:59

YYYY-MM-DD HH:MM:SS

混合日期和时间值

TIMESTAMP

1970-01-01 00:00:00/2038结束时间是第2147483647秒，北京时间2038-01-19 11:14:07,格林尼治时间2038年1月19日凌晨03:14:07

YYYYMMDDHHMMSS

混合日期和时间值，时间戳

字符串类型：

类型

大小

用途

CHAR

0-255字符

定长字符串

VARCHAR

0-65536字节

变长字符串

TINYBLOB

0-255字节

不超过255个字符的二进制字符串

TINYTEXT

0-255字节

短文本字符串

BLOB

0-65536字节

二进制形式的长文本数据

TEXT

0-65536字节

长文本数据

MEDIUMBLOB

0-16777215字节

二进制形式的中等长度文本数据

MEDIUMTEXT

0-16777215字节

中等长度文本数据

LONGBLOB

0-4294967295字节

二进制形式的极大文本数据

LONGTEXT

0-4294967295字节

极大文本数据

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

BINARY和VARBINARY类类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型: TINYBLOB、 BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。

有4种TEXT类型: TINYTEXT、 TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的最大长度和存储需求。

4.2.2 非空约束

not null

CREATE TABLE student(

id int primary key,

name varchar(50) not null,

sex varchar(10)

);

INSERT INTO student values(1,'tom',null);

4.2.3 默认值约束

default

CREATE TABLE student(

id int primary key,

name varchar(50) not null,

sex varchar(10) default '男'

);

INSERT INTO student values(1,'marry','女');

INSERT INTO student values(2,'tom',default);

INSERT INTO student(id,name) values(1,'jack');

4.3 引用完整性

(参照完整性)

外键约束：FOREIGN KEY

示例：

#学生表

CREATE TABLE student(

sid int primary key,

name varchar(50) not null,

sex varchar(10) default '男'

);

#成绩表

CREATE TABLE score(

id int primary key,

score int,

sid int,

CONSTRAINT fk_score_sid FOREIGN KEY(sid) REFERENCES student(sid)

);

#第二种写法(简写)

CREATE TABLE score(

id int primary key,

score int,

sid int references student(sid),

);

-- 外键列的数据类型一定要与主键的类型一致

第二种添加外键方式。

#成绩表

CREATE TABLE score(

id int primary key,

score int,

sid int,

);

ALTER TABLE score ADD CONSTRAINT fk_score_sid FOREIGN KEY(sid) REFERENCES student(sid);

第五章多表查询

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

多表约束：外键约束。

5.1 多表的关系

5.1.1 一对多关系

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

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

5.1.2 多对多关系

学生和课程：

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

5.1.3 一对一关系

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

两种建表原则：

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

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

5.2 多表查询

多表查询有如下几种：

合并结果集：UNION、UNION ALL

连接查询

2.1内连接[INNER] JOIN ON

2.2外连接OUTER JOIN ON

左外连接LEFT [OUTER] JOIN

右外连接RIGHT [OUTER] JOIN

全外连接(MySQL不支持)FULL Join

2.3自然连接NATURAL JOIN

子查询

5.2.1 合并结果集

作用：合并结果集就是把两个select语句的查询结果合并到一起。

合并结果集有两种方式：

UNION：去除重复记录，例如

SELECT * FROM t1 UNION SELECT * FROM t2;

11735778.html

UNION ALL：不去除重复记录，例如：

SELECT * FROM t1 UNION ALL SELECT * FROM t2;

11735778.html

注意：被合并的两个结果：列数、列类型必须相同。

5.2.2 连接查询

连接查询就是求出多个表的乘积，例如t1连接t2，那么查询的结果就是t1*t2。

11735778.html

连接查询会产生笛卡尔积，假设集合A={a,b},集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。

那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过条件过滤。通常要查询的多个表之间都存在关联系，那么就通过关联关系去除笛卡尔积。

示例1：现有两张表

emp表：

CREATE TABLE emp(

empno int,

ename varchar(50),

job varchar(50),

mgr int,

hiredate date,

sal decimal(7,2),

deptno int

);

dept表：

CREATE TABLE dept(

deptno int,

dname varchar(14),

loc varchar(13)

);

执行如下语句：

SELECT * FROM emp,dept;

11735778.html

使用主外键关系作为条件来去除无用信息

SELECT * FROM emp,dept WHERE emp.deptno = dept.deptno;

11735778.html

上面查询会把两张表的所有列都查询出来，也许你不需要那么多列，这是就可以指定要查询的列了。

SELECT emp.ename,emp.sal,emp.comn,emp.deptno,emp.dname

FROM emp,dept

WHERE emp.deptno = dept.deptno;

11735778.html

一、内连接

上面的连接语句就是内连接，但它不是SQL标准中的查询方式。

SQL标准的内连接为：

SELECT *

FROM emp e

INNER JOIN dept d

ON e.deptno = d.deptno;

内连接的特点：查询结果必须满足条件。

二、外连接

包括左外连接和右外连接，外连接的特点：查询出的结果存在不满足条件的可能。

a、左外连接

SELECT * FROM emp e

LEFT OUTER JOIN dept d

ON e.deptno = d.deptno;

左连接是先查询出左表(即以左表为主)，然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL。

我们还是用上面的例子来说明。其中emp表中"Bob"这条记录中，部门编号为50, 而dept表中不存在部门编号为50的记录，所以“张三”这条记录，不能满足e.deptno=d.deptno这条件。但在左连接中，因为emp表是左表，所以左表中的记录都会查询出来，即“Bob"这条记录也会查出，但相应的右表部分显示NULL。

11735778.html

b、右外连接

右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。例如在dept表中的40部门并不存在员工，但在右连接中，如果dept表为右表，那么还是会查出40部门，但相应的员工信息为NULL。

SELECT * FROM emp e

RIGHT OUTER JOIN dept d

ON e.deptno = d.deptno;

11735778.html

连接查询心得：

连接不限于两张表，连接查询也可以是三张、四张，甚至N张表的连接查询。通常连接查询不可能需要整个笛卡尔积，而只是需要其中一部分，那么这时就需要使用条件来去除不需要的记录。这个条件大多数情况下都是使用主外键关系去除。

两张表的连接查询一定有一个主外键关系，三张表的连接查询就一定有两个主外键关系，所以在大家不是很熟悉连接查询时，首先要学会去除无用笛卡尔积，那么就是用主外键关系作为条件来处理。如果两张表的查询，那么至少有一个主外键条件，三张表连接至少有两个主外键条件。

5.2.3 子查询

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

子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个或两个以上的SELECT，那么就是子查询语句了。里面的查询叫做子查询，外层的查询叫父查询，一般情况都是先执行子查询再执行父查询。

子查询出现的位置：

where后，作为被查询的条件的一部分；

from后，作表。

当子查询出现在where后作为条件时，还可以使用如下关键字：

any

all

子查询结果集的形式：

单行单列(用于条件)

单行多列(用于条件)

多行单列(用于条件)

多行多列(用于表)

示例：

1、查询工资高于JONES的员工

分析：

查询条件：工资>JONES,其中JONES工资需要一条子查询

第一步：查询JONES的工资

SELECT sal FROM emp WHERE ename = 'JONES';

第二步：查询高于JONES工资的员工

SELECT * FROM emp WHERE sal > (第一步的结果);

结果：

SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = 'JONES');

2、查询与SCOTT同一个部门的员工

子查询作为条件

子查询形式为单行单列

分析：

查询条件：部门=SCOTT的部门编号，其中SCOTT的部门编号需要一条子查询

第一步：查询SCOTT的部门编号

SELECT deptno FROM emp WHERE ename = 'SCOTT';

第二步：查询部门编号等于SCOTT的部门编号的员工

SELECT * FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'SCOTT');

3、工资高于30号部门所有人的员工信息

分析：

SELECT * FROM emp WHERE sal >(SELECT MAX(sal) FROM emp WHERE deptno = 30);

查询条件：工资高于30部门所有人工资，其中30号部门所有人工资是子查询，高于所有需要使用ALL关键字。

第一步：查询30号部门所有人工资

SELECT sal FROM emp WHERE deptno = 30;

第二步：查询高于30号部门所有人工资的员工信息

SELECT * FROM emp WHERE sal > ALL(第一步结果)；

结果：

SELECT * FROM emp WHERE sal > ALL(SELECT sal FROM emp WHERE deptno = 30);

4、工资高于30号部门中任意一个人的员工信息

SELECT * FROM emp WHERE sal > ANY(SELECT sal FROM emp WHERE deptno = 30);

子查询作为条件

子查询形式为多行单列(当子查询结果集形式为多行单列时可以使用ALL或ANY关键字)

5、查询1982年后入职员工中工资最高的员工信息

SELECT *,MAX(sal) FROM (SELECT * FROM emp WHERE hiredate >= '1982-01-01') AS temp;

5.2.4 导出导入数据库

1、使用命令方式

导出数据库表：

mysqldump -u用户名 -p 数据库名 > 导出路径与文件名

导入数据库表：

mysql -u用户名 -p

mysql>use 数据库名

然后使用source命令，后面参数为脚本文件(如这里用到的.sql)

mysql>source 导出路径与文件名

2、使用工具导入导出工具

5.2.5 创建用户和授权

创建用户

CREATE USER 用户名及地址(`zhangsan`@`localhost`) IDENTIFIED BY 密码('123456');

授权

GRANT ALL(权限) ON school.*(数据库名.表名) TO `zhangsan`(用户名)

撤销授权

REVOKE ALL(权限) ON school.*(数据库名.表名) TO `zhangsan`(用户名)；

删除用户

DROP USER `zhangsan`(用户名)；

weixin_39620273

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql约束2表参数约束_MySQL_2_约束和多表查询

MySQL数据库第四章数据的完整性作用：保证用户输入的数据保存到数据库中是正确的。确保数据的完整性=在创建表时给表中添加约束。完整性的分类：实体完整性；域完整性；引用完整性；4.1 实体完整性实体：即表中的一行(一条记录)代表一个实体(entity)。实体完整性的作用：标识每一行数据不重复。约束类型：主键约束(primary key)唯一约束(unique)自动增长列(auto_incremen...
复制链接

扫一扫