MySQL约束

最新推荐文章于 2024-04-23 16:46:48 发布
最新推荐文章于 2024-04-23 16:46:48 发布
阅读量163 收藏
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_45370568/article/details/106817800
版权

DQL：查询语句 SQL约束 多表之间的关系 数据库设计范式 数据库的备份和还原

一. DQL：查询语句

1.排序查询

1.语法

  • order by 子句：order by 排序字段1 排序方式1, 排序字段2 排序方式2…….

  • 排序方式：

    • ASC：升序，默认

    • DESC：降序。

      1
      2
      3
      4
      5
      6
      7
      8
      SELECT * FROM student ORDER BY math; -- 升序

      SELECT * FROM student ORDER BY math ASC; -- 升序

      SELECT * FROM student ORDER BY math DESC;-- 降序

      -- 按照数学成绩排名，如果数学成绩一样，则按照英语成绩排名 
      SELECT * FROM student ORDER BY math ASC,english ASC;

  • 注意：如果有多个排序条件，则当前面的条件值英语时，才会判断第二条件。

2.聚合函数：将一列数据作为一共整体，进行纵向的计算。

  1. count：计算个数

    1. 一般选择非空的列：主键
    2. count(*)
    1
    2
    3
    4
    SELECT COUNT(NAME)FROM student;
    SELECT COUNT(english)FROM student;
    SELECT COUNT(IFNULL(english,0)) FROM student;
    SELECT COUNT(*)FROM student;

  2. max：计算最大值

  3. min：计算最小值

    1
    2
    3
    4

    SELECT MAX(math) FROM student;

    SELECT MIN(math) FROM student;

  1. sum：计算和

    1
    SELECT SUM(math)FROM student;

  2. avg：计算平均值

    1
    SELECT AVG(math) FROM student;

注意：聚合函数的计算会排除null值。

解决方案：

1. 选择不包含非空的列进行计算，

 2. ifnull函数

3.分组查询

  1. 语法：group by 分组字段;

    • SELECT 字段 1,字段 2... FROM 表名 GROUP BY 分组字段 [HAVING 条件];
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    -- 按照性别分组，分别查询男，女同学的平均分
    SELECT sex,AVG(math)FROM student GROUP BY sex;
    -- 按照性别分组，分别查询男，女同学的平均分,人数
    SELECT sex,AVG(math),COUNT(id)FROM student GROUP BY sex;
    -- 按照性别分组，分别查询男，女同学的平均分,人数 要求：分数低于70分的不参与分组
    SELECT sex,AVG(math),COUNT(id)FROM student WHERE math>70 GROUP BY sex;
    -- 按照性别分组，分别查询男，女同学的平均分,人数 要求：分数低于70分的不参与分组,分组之后人数要大于2个
    SELECT sex,AVG(math),COUNT(id)FROM student WHERE math>70 GROUP BY sex HAVING COUNT(id) > 2;

    -- 起别名
    SELECT sex,AVG(math),COUNT(id) 人数 FROM student WHERE math>70 GROUP BY sex HAVING 人数 > 2;

注意：

  1. 分组之后查询的字段：分组字段，聚合函数

  2. where 和 having 的区别？

    1. where 在分组之前进行限定，如果不满足这个条件，则不参与分组

      having在分组之后进行限定，如果不满足结果，则不会被查询出来

      2. where 后不可以跟聚合函数，having可以进行聚合函数的判断。

4. 分页查询

  1. 语法 ：limit 开始的索引，每页查询的条数

  2. 公式：开始的索引 = (当前的页码 -1 ) * 每页显示的条数

    1
    2
    3
    4
    -- 每页显示3条记录
    SELECT * FROM student LIMIT 0,3; -- 第1页
    SELECT * FROM student LIMIT 3,3; -- 第2页
    SELECT * FROM student LIMIT 6,3; -- 第3页

  3. limit 是一个MySQL“方言”

二 .约束

1. 非空约束：not null ，某一列的值不能为null

  1. 创建表时添加约束

    1
    2
    3
    4
    5
    -- 创建表添加非空约束
    CREATE TABLE IF NOT EXISTS stu(
    	id INT,
    	NAME VARCHAR(20) NOT NULL -- name为非空
    );

  2. 创建表后，添加数据

    1
    2
    -- 创建表后，添加约束
    ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;

  3. 删除非空约束

    1
    2
    -- 删除name的非空约束
    ALTER TABLE stu MODIFY NAME VARCHAR(20);

2. 唯一约束：unique ，某一列的值不能重复

  1. 注意：

    1. 唯一约束可以有null值，但是只能有一条记录为null。

  2. 创建表时，添加唯一约束

    1
    2
    3
    4
    5
    -- 创建表时，添加唯一约束
    CREATE TABLE stu(
    	id INT,
    	phone_number VARCHAR(20) UNIQUE -- 手机号
    );

  3. 删除唯一约束

    1
    2
    3
    -- 删除唯一约束
    ALTER TABLE stu MODIFY phone_number VARCHAR(20);  -- 错误
    ALTER TABLE stu DROP INDEX phone_number;          -- 正确

  4. 创建表后，添加唯一约束

    1
    2
    -- 创建表后，添加唯一约束
    ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;

3. 主键约束：primary key

  1. 注意：

    1. 含义 ：非空且唯一

    2. 一张表只能有一个字段为主键

    3. 主键就是表中记录的唯一标识

    4. 在创建表时，添加主键约束

      1
      2
      3
      4
      create table stu (
      	id int primary key, -- 给id添加主键约束
          name varchar(20)
      );
      1. 删除主键
      1
      2
      3
      -- 删除主键
      ALTER TABLE stu MODIFY id INT; -- 错误
      ALTER TABLE stu DROP PRIMARY KEY; -- 正确
      1. 创建表后，添加主键约束
      1
      2
      -- 创建表后，添加主键
      ALTER TABLE stu MODIFY id INT PRIMARY KEY;

      1. 自动增长：如果某一列是数值类型的，使用auto_increment 可以来完成值的自动增长。

      2. 在创建表时，添加主键约束，并完成主键自动增长

        1
        2
        3
        4
        5
        6
        7
        8
        create table stu (
        	id int primary key auto_increment, -- 给id添加主键约束
            name varchar(20)
        );
        -- 删除自动增长
        ALTER TABLE stu MODIFY id INT;
        -- 添加自动增长
        ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

4. 外键约束：foreign key ,让表与表产生关系，从而保证数据的正确性

  1. 在创建表时，可以添加外键

    • 语法：

      1
      2
      3
      4
      5
      create  table 表名 (
      	 ......
      	外键列
      	constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
      )；
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      32
      33
      34
      -- 解决方案：分成2张表 
      -- 创建部门表(id,dep_name,dep_location) 
      -- 一方，主表 
      CREATE TABLE department(  
      	id INT PRIMARY KEY AUTO_INCREMENT,  
      	dep_name VARCHAR(20),  
      	dep_location VARCHAR(20) 
      ); 
       
      -- 创建员工表(id,name,age,dep_id) 
      -- 多方，从表 
      CREATE TABLE employee(  
      	id INT PRIMARY KEY AUTO_INCREMENT,  
      	NAME VARCHAR(20),  
      	age INT,  
      	dep_id INT,  -- 外键对应主表的主键 
      	CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id)
      );

      -- 添加2个部门 
      INSERT INTO department VALUES(NULL, '研发部','广州'),(NULL, '销售部', '深圳'); 
      SELECT * FROM department; 

      -- 添加员工,dep_id表示员工所在的部门 
      INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
      INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1); 
      INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1); 
      INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2); 
      INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2); 
      INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2); 
       
       SELECT * FROM employee; 
       
      SELECT * FROM department;

  2. 删除外键

    1
    2
    -- 删除外键
    ALTER TABLE 表名称 DROP FOREIGN KEY 外键名称;

  3. 创建表后，添加外键约束

    1
    2
    -- 添加外键
    ALTER TABLE 表名称 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

  4. 级联操作

    • 级联更新： ON UPDATE CASCADE

    • 级联删除：ON DELETE CASCADE

      1
      2
      3
      4
      5
      6
      -- 添加外键,设置级联更新,
      ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY 
      (dep_id) REFERENCES department(id) ON UPDATE CASCADE;
       -- 添加外键,设置级联更新,设置级联删除
      ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY 
      (dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE

三. 数据库的设计

1. 多表之间的关系

  1. 一对一：

    • 如：人和身份证号码
    • 分析：一个人只有一个身份证号码，一个身份证号码只能对应一个人

  2. 一对多（多对一）：

    • 如：部门和员工
    • 分析：一个部门有多个员工，一个员工只能对应一个部门

  3. 多对多：

    • 如：学生和课程
    • 分析：一个学生可以选择多门课程，一门课程也可以被多名学生选择

2.实现关系

  1. 一对多（多对一）：

    • 如：部门和员工
    • 实现方式：在多的一方建立外键，指向一的一方的主键
    1. 多对多：

    • 如：学生和课程

    • 实现方式：多对多关系的实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键

  1. 一对一：
    • 如：人和身份证号码
    • 实现方式：一对一关系实现，可以在任意一方添加唯一外键指向另一方的主键

3. 案例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
-- 创建旅游线路分类表tab_category 
-- cid旅游线路分类主键，自动增长 
-- cname旅游线路分类名称非空，唯一，字符串100 
CREATE TABLE tab_category (   
	cid INT PRIMARY KEY AUTO_INCREMENT,   
	cname VARCHAR(100) NOT NULL UNIQUE 
);

-- 添加旅游线路分类数据： 
INSERT INTO tab_category (cname) VALUES ('周边游'), ('出境游'), ('国内游'), ('港澳游'); 
SELECT * FROM tab_category; 

-- 创建旅游线路表tab_route 
/* 
rid旅游线路主键，自动增长 
rname旅游线路名称非空，唯一，字符串100 
price价格 rdate 上架时间，
日期类型 cid 外键，
所属分类 */ 
CREATE TABLE tab_route(   
	rid INT PRIMARY KEY AUTO_INCREMENT,   
	rname VARCHAR(100) NOT NULL UNIQUE,   
	price DOUBLE, 
	rdate DATE,   
	cid INT,   
	FOREIGN KEY (cid) 
	REFERENCES tab_category(cid) 
);

-- 添加旅游线路数据 
INSERT INTO tab_route VALUES 
(NULL, '【厦门+鼓浪屿+南普陀寺+曾厝垵 高铁3天 惠贵团】尝味友鸭面线 住1晚鼓浪屿', 1499, '2018-01-27', 1), 
(NULL, '【浪漫桂林 阳朔西街高铁3天纯玩 高级团】城徽象鼻山 兴坪漓江 西山公园', 699, '2018-02-22', 3), 
(NULL, '【爆款￥1699秒杀】泰国 曼谷 芭堤雅 金沙岛 杜拉拉水上市场 双飞六天【含送签费 泰风情 广州 往返 特价团】', 1699, '2018-01-27', 2), 
(NULL, '【经典•狮航 ￥2399秒杀】巴厘岛双飞五天 抵玩【广州往返 特价团】', 2399, '2017-12-23', 2), 
(NULL, '香港迪士尼乐园自由行2天【永东跨境巴士广东至迪士尼去程交通+迪士尼一日门票+香港如心海景酒店 暨会议中心标准房1晚住宿】', 799, '2018-04-10', 4
); 
 SELECT * FROM tab_route;

/*
创建用户表
tab_user uid用户主键，自增长 
username用户名长度100，唯一，非空 
password密码长度30，非空 
name真实姓名长度100 
birthday生日 
sex性别，定长字符串1 
telephone手机号，字符串11 
email邮箱，字符串长度100 
*/ 
CREATE TABLE tab_user (   
uid INT PRIMARY KEY AUTO_INCREMENT,   
username VARCHAR(100) UNIQUE NOT NULL,   
PASSWORD VARCHAR(30) NOT NULL,   
NAME VARCHAR(100),   
birthday DATE,   
sex CHAR(1) DEFAULT '男',   
telephone VARCHAR(11),   
email VARCHAR(100) ) ;
 
-- 添加用户数据 
INSERT INTO tab_user VALUES 
(NULL, 'cz110', 123456, '老王','1977-07-07', '男', '13888888888', '66666@qq.com'), 
(NULL, 'cz119', 654321, '小王','1999-09-09', '男', '13999999999', '99999@qq.com'); 
 SELECT * FROM tab_user; 
 /*  
 创建收藏表tab_favorite 
 rid 旅游线路id，外键 
 date 收藏时间 
 uid用户id，外键 
 rid和uid不能重复，设置复合主键，同一个用户不能收藏同一个线路两次 
 */ 
 CREATE TABLE tab_favorite 
 (rid INT,   
 DATE DATETIME,   
 uid INT,   -- 创建复合主键   
 PRIMARY KEY(rid,uid),   
 FOREIGN KEY (rid) REFERENCES tab_route(rid),   
 FOREIGN KEY(uid) REFERENCES tab_user(uid) ) ;
 
 
-- 增加收藏表数据 
INSERT INTO tab_favorite VALUES 
(1, '2018-01-01', 1), -- 老王选择厦门 
(2, '2018-02-11', 1), -- 老王选择桂林 
(3, '2018-03-21', 1), -- 老王选择泰国 
(2, '2018-04-21', 2), -- 小王选择桂林 
(3, '2018-05-08', 2), -- 小王选择泰国 
(5, '2018-06-02', 2); -- 小王选择迪士尼 
 SELECT * FROM tab_favorite;

4 .数据库设计的范式

  • 概念：设计数据库时，需要遵循一些的规范。要遵循后边的范式，必须先遵循前边所有的范式要求。

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

  • 分类：

    1. 第一范式（1NF）：每一列都是不可分割的原子数据项

    2. 第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）

      几个概念：

      1. 函数依赖：A —->B,如果通过A属性（属性组）的值，可以确定唯一B属性的值。则成B依赖于A

        ​ 例如：学号 —-> 姓名。 （学号，课程名称）—-> 分数

      1. 完全函数依赖：A —->B ，如果A是一个属性组，则B属性值的确定需要依赖于A属性组中所有的属性值。

        例如： （学号，课程名称）—-> 分数

      2. 部分函数依赖：A —->B ，如果A是一个属性组，则B属性值的确定，只需要依赖于A属性组中的某一些值即可。

        例如： （学号，课程） —> 姓名

      3. 传递函数依赖：A —> B，B —> C ，如果通过A属性（属性组）的值，可以确定唯一B属性的值，在通过B属性（属性组）的值可以确定唯一C属性的值。则称 C 传递函数依赖于A

        例如 ：学号—>系名，系名—>系主任

      4. 码：如果在一张表中，如果一个属性或属性组，被其他所有属性所完全依赖，则称这个属性（属性组）为该表的码。

        例如：该表中码为：（学号，课程名称）

    3. 第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

[TOC]

CodeYangSir
关注
mysql约束日期_MySQL约束
weixin_35663823的博客
01-28 2649
约束* 概念： 对表中的数据进行限定，保证数据的正确性、有效性和完整性。* 分类：1. 主键约束：primary key2. 非空约束：not null3. 唯一约束：unique4. 外键约束：foreign key* 非空约束：not null，值不能为null1. 创建表时添加约束CREATE TABLE stu(id INT,NAME VARCHAR(20) NOT NULL -- nam...
第四天项目笔记
风一般的男子·小亮仔
05-25 217
项目第4天-课堂笔记 一、我的收藏 实现的步骤: 1. header.html代码，头部页面登录信息位置“我的收藏”跳转到myfavorite.html页面 2. myfavorite.html代码，我的收藏页面，页面加载时提交异步获取当前登录用户收藏分页数据列表请求到 FavoriteServlet，并获取返回的PageBean数据更新到页面上。 <!-- 排行榜 end--...
MySQL 约束
weixin_46441425的博客
01-29 1761
主键约束 ：primary key、非空约束 : not null、唯一约束 ：unique、外键约束 ： foreign key
MySQL约束constraint
weixin_45856470的博客
10-01 4988
约束canstraint，约束实际上就是表中数据的限制条件。表在设置的时候加入约束的目的是为了保证表中的记录完整性和有效性，比如用户的某些数据不能为空。
MySQL 约束语法
weixin_53395092的博客
02-12 2653
约束的概念 约束是作用于表中列上的规则，用于限制加入表的数据。 例如：我们可以给id列加约束，让其值不能重复，不能为null值。 约束的存在保证了数据库中数据的正确性、有效性和完整性。添加约束可以在添加数据的时候就限制不正确的数据，年龄是3000，数学成绩是-5分这样无效的数据，继而保障数据的 完整性。 通俗的说：就是让数据库表中的数据不出现逻辑上的错误 1、非空约束 非空约束用于保证列中所有数据不能有null值 语法： 创建表时添加约束：CREATE TABLE 表名( 字段名 数据类型 N
MySQL 约束详解
GSON的博客
11-07 2991
一、什么是约束 约束实际上就是表中数据的限制条件 二、约束作用 表在设计的时候加入约束的目的就是为了保证表中的记录完整和有效 比如name字段中要让其用户名不重复，这就需要添加约束。或者必须注册的时候需要添加邮箱等 三、约束种类 非空约束(not null) 唯一性约束(unique) 主键约束(primary key) PK 外键约束(foreign key) FK 检查约束(目前MySQL不支持、Oracle支持) 下面将逐一介绍以上约束 四、非空约束 用not null约束
MySQL 约束篇
Java 技术专栏，从入门到精通，熟悉企业级开发
04-23 7205
此为MySQL专栏文章之一，讲解MySQL约束。分类：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。
mysql 约束_mysql常见约束有哪些
weixin_35337252的博客
01-18 934
mysql常见约束有：1、非空约束“not null”；2、唯一性约束“unique”；3、主键约束“primary key”；4、外键约束“foreign key”；5、默认值约束“Default”等等。约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性。下面本篇文章就来给大家介绍一下6种mysql常见的约束，希望对大家有所帮助。1、非空约束(not null)非空...
mysql约束
TimeFriends
01-13 1716
mysql约束-自动增长约束(auto_increment) 语法:字段名 数据类型 auto_increment 特点: 默认情况下,auto_increment的初始值是1,每新增一条记录,字段值自动加1 一个表只能有一个字段auto_increment约束,且该字段必须有唯一索引,以避免序号重复(即主键或者主键的一部分) auto_increment约束的字段必须具备not null 属性 auto_increment约束的字段只能是整数类型(tinyint,smallint,int,
25.10 MySQL 约束
04-02
MySQL约束是数据库设计中的关键元素，用于确保数据的完整性和一致性。它们定义了表中数据必须遵循的规则，防止不合法或错误的数据被插入或更新。以下是对MySQL约束的详细阐述： 1. **非空约束 (NOT NULL)**：此约束...
MySQL约束类型及举例介绍
01-19
约束类型包括：NOT NULL （非空约束），PRIMARY KEY（主键约束），UNIQUE KEY（唯一约束），DEFAULT（默认约束），FOREIGN（外键约束） 1.主键约束 PRIMARY KEY 每张数据表只能存在一个主键 主键保证记录的唯一...
MySQL约束(三).pdf
05-01
### MySQL约束(三) #### 内容概要 本篇文档深入探讨了MySQL数据库中的多种约束类型及其实际应用场景，作为约束系列教程的第三部分，它不仅涵盖了基础概念，还涉及了高级用法。 #### 主要内容概述 1. **PRIMARY ...
MySql约束
yangao1213192588的博客
10-05 731
mysql约束知识点
S7-1200的运动控制功能
10-07
mysql
闲置物品交易网站设计与实现.ppt
10-07
闲置物品交易网站设计与实现.ppt
2023全国最新区划代码
最新发布
10-07
行政区划代码，地区划分代码，包涵县以下
JAVA源码简单聊天软件CS模式JAVA源码简单聊天软件CS模式
10-07
JAVA源码简单聊天软件CS模式JAVA源码简单聊天软件CS模式
JAVA源码google-api-translate-java(Java语言对Google翻译引擎的封装类库)
10-07
JAVA源码google-api-translate-java(Java语言对Google翻译引擎的封装类库)
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值