1.DQL 数据查询语言

  在上一篇博文中,我们已经讲述了部分数据查询语句,在此我们再次对其进行补充。

1.1 排序

       通过ORDAR BY 语句,可以将查询出来的结果进行排序。(排除只是一种现实的方式,不会影响数据库中的数据顺序)

   

  (1)单列排序:只按照某一个字段进行排序。
SELECT * FROM 表名 WHERE 字段=值 ORDER BY 字段名[ASC/DESC];
        ASC:代表升序     DESC:降序。

 
-- 查询所有数据,使用年龄降序排序 SELECT * FROM student ORDER BY age DESC;
  (2)组合排列:同时对多个字段进行排列,如果第一个字段相等,就按照第二个字段排列。
SELECT * FROM 表名 字段=值 ORDER BY 字段名1[ASC][DESC],字段名2[ASC][DESC]; -- --
查询所有数据,在年龄降序排序的基础上,如果年龄相同再以数学成绩升序排序 SELECT * FROM student ORDER BY age
DESC,math ASC;
 1.2 聚合函数

SQL中的聚合函数作用
max(列名)求这一列的最大值
min(列名)求这一列的最小值
avg(列名)求这一列的平均值
count(列名)统计这一列有多少条记录
sum(列名)对这一列求总和
  语法格式:
SELECT 聚合函数(列名) FROM 表名; -- 查询学生总数 SELECT COUNT(id) AS 总人数 FROM student;
SELECT COUNT(english) AS 总人数 FROM student;
        
从上述两行代码我们可以发现,统计的总人数不一致,原因是有一个人没有英语成绩,其英语成绩为NULL,因此在统计个数的时候,不要使用可能存在NULL值的列,但是如果我们要把NULL也统计进去应该怎么做?
IFNULL(列名,默认值);
     如果列名不为空,返回这列的值,如果为NULL,就返回默认值。
-- 查询id字段,如果为null,则使用0代替 select ifnull(id,0) from student;
        因此我们可以使用IFNULL()函数,如果列中的数据存在NULl,就会给NULL赋值一个默认值,这样就不会出现统计数据不一致的情况了。
SELECT COUNT(IFNULL(english,0)) AS 总人数 FROM student;
       此时,统计的人数就一直了。
-- 查询年龄大于20的总数 SELECT COUNT(*) FROM student WHERE age>20; -- 查询数学成绩总分(AS可省略)
SELECT SUM(math) 总分 FROM student; -- 查询数学成绩平均分 SELECT AVG(math) 平均分 FROM
student; -- 查询数学成绩最高分 SELECT MAX(math) 总高分 FROM student; -- 查询数学成绩最低分 SELECT
MIN(math) 总低分 FROM student;
1.3 分组

     分组查询是用GROUP BY语句对查询信息进行分组,相同数据作为一组。
SELECT 字段1,字段2...FROM 表名 GROUP BY 分组字段 [WHERE 条件];
       把分组字段结果中相同内容的分为一组,比如按照性别将学生分成2组。

     GROUP BY 将分组字段结果中相同的内容作为一组,并返回每组的第一条数据,所以单独分组没有什么用。

分组的目的就是为了统计,因此分组会和聚合函数一起使用。
-- 按性别进行分组,求男生和女生数学的平均分 SELECT sex,AVG(math) FROM student GROUP BY sex;
   执行结果:

    

  实际上是将每组的math求了平均数,返回每组统计的结果。

  注:当我们使用某个字段进行分组时,在查询语句的时候也需要将这个字段查询出来,否则看不到属于哪组数据。

   

 查询男女各有多少人:

           (1)查询所有数据,按性别分组,

           (2)统计每组个数。
SELECT sex,COUNT(*) FROM student GROUP BY sex;
   查询年龄大于25岁的人,按性别分组,统计每组的人数:

                 (1)先过滤掉年龄小于25岁的人。

                  (2)再分组。

                  (3)最后统计每组的人数
-- 查询年龄大于25岁的人,按性别分组,统计每组的人数 SELECT sex,COUNT(id) FROM student WHERE age>25
GROUP BY sex;

查询年龄大于25岁的人,按性别分组,统计每组的人数,并只显示性别人数大于2的数据:
-- 对分组查询的结果再进行过滤 SELECT sex,COUNT(id) FROM student WHERE age>25 GROUP BY sex
HAVING COUNT(id)>2;
       只有分组后人数大于2才显示。

 having 和where 之间的区别:

  

子名作用
where 子句1)对查询结果进行分组前,将不符合where条件的行去掉,即在分组之前过滤数据,即先过滤再分组。
2)where后面不可以使用聚合函数
having子句1)having 子句的作用是筛选满足条件的组,即在分组之后过滤数据,即先分组再过滤。
2)having后面可以使用聚合函数
  1.4 limit 语句

(1)准备数据
INSERT INTO student(id,NAME,age,sex,address,math,english) VALUES
(9,'唐僧',25,'男','长安',87,78), (10,'孙悟空',18,'男','花果山',100,66),
(11,'猪八戒',22,'男','高老庄',58,78), (12,'沙僧',50,'男','流沙河',77,88),
(13,'白骨精',22,'女','白虎岭',66,66), (14,'蜘蛛精',23,'女','盘丝洞',88,88);
(2)limit的作用:

     LIMIT是限制的意思,所以LIMIT的作用就是限制查询记录的条数。
SELECT *| 字段列表[as 别名] FROM 表名 [WHERE子句] [GROUP BY子句] [HAVING子句][ORDER
BY子句][LIMIT子句];
  (3)limit 语法格式:
LIMIT offset,length;
       offset:起始行数,从0开始计数,如果省略,默认就是0。length: 返回的行数。
-- 查询学生表中数据,从第3条开始显示,显示6条。 SELECT * FROM student LIMIT 2,6;

 (4)LIMIT的使用场景

        分页:比如我们登录京东,淘宝,返回的商品信息可能有几万条,不是一次全部显示出来。是一页显示固定的条数。 假设我们每页显示5条记录的方式来分页。

-- 如果第一个参数是0可以省略写: SELECT * FROM student LIMIT 5; -- 最后如果不够5条,有多少显示多少 SELECT *
FROM student LIMIT 10,5;
2.数据库的备份和还原.

       
在服务器进行数据传输、数据存储和数据交换,就有可能产生数据故障。比如发生意外停机或存储介质损坏。这时,如果没有采取数据备份和数据恢复手段与措施,就会导致数据的丢失,造成的损失是无法弥补与估量的。

2.1 备份和还原的语句

    备份格式:DOS命令下,未登陆的时候。这是一个可执行的文件exe,在bin文件夹
mysqldump -u 用户名 -p 密码 数据库 > 文件的路径
   还原格式:MySQL中的命令,需要登陆后才能操作。
USE 数据库; SOURCE 导入文件的路径;
备份具体操作:
-- 备份day21数据库中的数据到d:\day21.sql文件中 mysqldum -uroot -proot day21 >d:/day21.sql
         导出结果:数据库中的所有表和数据都会导出成SQL语句

还原具体操作:

         还原day21数据库中的数据,注意:还原的时候需要先登录MySQL,并选中对应的数据库.

  (1)删除day21数据库中的所有表

  (2)登录MySQL

  (3)选中数据库

   (4)使用SOURCE命令还原数据

   (5)查看还原结果
use day21; source d:/day21.sql;
2.2  图形化界面备份和还原

   备份数据库中的数据:

  (1)选中数据库,右键 ”备份/导出”

  (2)指定导出路径,保存成.sql文件即可。

 还原数据路中的数据:

  (1)删除数据库

  (2)数据库列表区域右键“执行SQL脚本”, 指定要执行的SQL文件,执行即可

3.数据库表的约束

   对表中的数据进行限制,保证数据的正确性、有效性和完整性。一个表如果添加了约束,不正确的数据将无法插入到表中。约束在创建表的时候添加比较合适。

3.1 约束的种类

 

约束名约束关键字
主键primary key
唯一unique
非空not null
外键foreign key
检查约束check 注:mysql不支持
3.2 主键约束

   主键约束的作用:用来唯一标识数据库中每一条记录。

   通常不用业务字段作为主键,单独给每张表设计一个ID的字段,把ID作为主键。
主键是给数据库和程序使用的,不是给最终的客户使用的,因此主键没有任何含义也没有关系,只要主键不重复,非空就行。

 创建主键:

         主键关键字:primary key

         主键的特点:(1)非空 not NULl  (2)唯一

 创建主键的方式:

            (1)在创建表时,给字段添加主键
字段名 字段类型 PRIMARY KEY
     
-- 创建表学生表st5, 包含字段(id, name, age)将id做为主键 create table st5 ( id int primary
key, -- id为主键 name varchar(20), age int ) desc st5;
  

          (2)在已有的表中添加主键。
ALTER TABLE 表名 ADD PRIMARY KEY (字段名); -- 插入重复的主键值 INSERT INTO st5 VALUES (1,
'关羽', 30); INSERT INTO st5 VALUES (1, '关羽', 20); -- 查看表中的数据 SELECT * FROM st5;
-- 插入NULL的主键值, Column 'id' cannot be null INSERT INTO st5 VALUES (NULL, '关云长',
20);
删除主键:
-- 删除st5表的主键 ALTER TABLE st5 DROP PRIMARY KEY; -- 添加主键 ALTER TABLE st5 ADD
PRIMARY KEY(id);
主键的自增:

     主要如果让我们自己添加很有可能会重复,因此我们在添加数据的时候,希望数据库可以自动生成主键的字段的值。
CREATE TABLE st6 ( id INT PRIMARY KEY AUTO_INCREMENT,-- id为主键,自增长为1 NAME
VARCHAR(20), age INT ) DESC st6; -- 插入数据 INSERT INTO st6 (NAME,age)
VALUES('小乔',18); INSERT INTO st6 (NAME,age) VALUES('大乔',19); -- 另一种写法 INSERT
INTO st6 VALUES(NULL,'周瑜',25); -- 查询st6的数据 SELECT * FROM st6;
  

    (1)修改自增长的起始值。

    默认地AUTO_INCREMENT 的开始值是1,我们可以自定义起始值,语法如下:
CREATE TABLE 表名( 列名 int primary key auto_increment )auto_increment=起始值; --
定义起始值为100 CREATE TABLE st4 ( id INT PRIMARY KEY AUTO_INCREMENT, NAME
VARCHAR(20) ) AUTO_INCREMENT = 100; INSERT INTO st4 VALUES (NULL, '孔明');
   创建好表以后,还可以修改起始值。
ALTER TABLE 表名 auto_increment=起始值; ALTER TABLE st4 AUTO_INCREMENT = 2000;
INSERT INTO st4 VALUES (NULL, '刘备'); SELECT * FROM st4;
 

 (2)DELETE 和TRUNCATE 对自增长的影响

        DELETE:删除所有记录之后,在向表中添加数据,自增长没有影响。
-- 删除大乔,之后查看表中的数据主键值的变化。 DELETE FROM st6 WHERE NAME='大乔'; -- 查询st6的数据 SELECT *
FROM st6;
  

     TRUNCATE:删除所有记录后,在向表中添加数据,主键自增长重新排列。
-- 删除大乔,之后查看表中的数据主键值的变化。 TRUNCATE TABLE st6; -- 查询st6的数据 SELECT * FROM st6;

3.3 唯一约束

      唯一约束,就是表中的某一列不能出现重复的值。

(1)唯一约束的语法格式
字段名 字段类型 UNIQUE
   具体实现:
-- 创建学生表st7, name这一列设置唯一约束,不能出现同名的学生 CREATE TABLE st7 ( id INT, NAME
VARCHAR(20) UNIQUE ) -- 添加一个同名的学生 INSERT INTO st7 VALUES (1, '张三'); SELECT *
FROM st7; -- 就会报错 Duplicate entry '张三' for key 'name' INSERT INTO st7 VALUES
(2, '张三'); -- 重复插入多个null会怎样 INSERT INTO st7 VALUES (2, NULL); INSERT INTO st7
VALUES (3, NULL);
   

null 没有数据,不存在重复的问题,因此可以添加。

3.4 非空约束

   非空约束:某一列不能为NULL

(1)非空约束的语法格式:
字段名 字段类型 NOT NULL -- 创建表学生表st8, 包含字段(id,name,gender)其中name不能为NULL CREATE TABLE
st8 ( id INT, NAME VARCHAR(20) NOT NULL, gender CHAR(1) ) -- 添加一条记录其中姓名不赋值
INSERT INTO st8 VALUES (1,'张三疯','男'); SELECT * FROM st8; -- Column 'name'
cannot be null INSERT INTO st8 VALUES (2,NULL,'男');
 (2)默认值
字段名 字段类型 DEFAULT 默认值 -- 创建一个学生表 st9,包含字段(id,name,address), 地址默认值是广州 CREATE
TABLE st9 ( id INT, NAME VARCHAR(20), address VARCHAR(20) DEFAULT '广州' ) --
添加一条记录,使用默认地址 INSERT INTO st9 VALUES(1,'赵四',DEFAULT); INSERT INTO st9 (id,NAME)
VALUES (2, '李白'); -- 添加一条记录,不使用默认地址 INSERT INTO st9 VALUES (3, '李四光', '深圳');
SELECT * FROM st9;
           如果一个字段设置了非空与唯一约束,该字段与主键的区别?

   答:主键数在一个表中,只能有一个。不能出现多个主键。主键可以是单列,可以是多列。

          自增长只能用在主键上。

3.5 外键约束

  (1)创建一个员工表包含如下列(id, name, age, dep_name, dep_location),id主键并自动增长,添加5条数据
-- 创建一个员工表包含如下列(id, name, age, dep_name, dep_location),id主键并自动增长,添加5条数据 CREATE
TABLE emp( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20), age INT,
dep_name VARCHAR(20), dep_location VARCHAR(20) ) -- 添加数据 INSERT INTO emp (NAME,
age, dep_name, dep_location) VALUES ('张三', 20, '研发部', '广州'); INSERT INTO emp
(NAME, age, dep_name, dep_location) VALUES ('李四', 21, '研发部', '广州'); INSERT INTO
emp (NAME, age, dep_name, dep_location) VALUES ('王五', 20, '研发部', '广州'); INSERT
INTO emp (NAME, age, dep_name, dep_location) VALUES ('老王', 20, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('大王', 22, '销售部',
'深圳'); INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('小王', 18,
'销售部', '深圳'); SELECT * FROM emp;
      以上数据表中存在缺点:

            第一,数据冗余

            第二,后期还会出现删减改的问题。

(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
VARCHAR(20) ) -- 添加两个部门 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;
     (3) 
问题:当我们在employee的dep_id里面输入不存在的部分,数据依然可以添加,但是并没有对应的部门,实际应用中不能出现这样的情况。employee的dep_id中的数据只能是department表中存在的id。

    (4)目标:需要约束dep_id 只能是department表中已经存在的id 。

    (5)解决方式:使用外键约束

    (6)外键约束的概念:

             外键:在从表中与主表主键对应的那一列。比如,employee表中的dep_id

             主表:一方,用来约束别人的表。

            从表:多方,被被人约束的表。

 (7)外键约束的语法

        新建表时增加外键约束
[CONSTRAINI] [外键约束名称] FOREIGN KEY(外键字段名)REFERENCES 主表名(主键字段名)
       已有表增加外键:
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES
主表(主键字段名);
   具体操作:
-- 1 删除副表/从表 employee DROP TABLE employee; -- 创建从表employee 并添加外键约束emp_depid_fx
-- 多方,从表 CREATE TABLE employee( id INT PRIMARY KEY AUTO_INCREMENT, NAME
VARCHAR(20), age INT, dep_id INT,-- 外键对应主表的主键 -- 创建外键约束 CONSTRAINT emp_depid_fx
FOREIGN KEY (dep_id) REFERENCES department(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; --
部门错误的数据添加失败,插入不存在的部门 -- Cannot add or update a child row: a foreign key
constraint fails INSERT INTO employee (NAME, age, dep_id) VALUES ('老张', 18, 6);

(8)删除外键 
ALTER TABLE 从表 DROP foreign key 外键名称; -- 删除employee表的emp_depid_fk外键 ALTER
TABLE employee DROP FOREIGN KEY emp_depid_fx; -- 在employee表存在的情况下添加外键 ALTER
TABLE employee ADD CONSTRAINT emp_depid_fk FOREIGN KEY (dep_id) REFERENCES
department(id);
  (9)外键的级联

        出现新的问题:
SELECT * FROM employee; SELECT * FROM department; -- 要把部门表中的id值2,改成5,能不能直接更新呢?
-- Cannot delete or update a parent row: a foreign key constraint fails UPDATE
department SET id=5 WHERE id=2; -- 要删除部门id等于1的部门, 能不能直接删除呢? -- Cannot delete or
update a parent row: a foreign key constraint fails DELETE FROM department
WHERE id=1;
   级联操作:修改和删除主表的主键时,同时更新或者删除副表的外键值,称为级联操作。

级联操作语法描述
on update cascade级联更新,只能在创建表的时候创建级联关系。更新主表的主键,从表的外键也自动更新
on delete cascade级联删除 -- 删除employee表,重新创建employee表,添加级联更新和级联删除 DROP TABLE
employee; CREATE TABLE employee( id INT PRIMARY KEY AUTO_INCREMENT, NAME
VARCHAR(20), age INT, dep_id INT,-- 外键对应主键 CONSTRAINT emp_depid_fk FOREIGN KEY
(dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE ) --
再次添加数据到员工表和部门表 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); -- 删除部门表?能不能直接删除? -- Cannot delete or update a parent row: a foreign
key constraint fails DROP TABLE department; -- 把部门表中id等于1的部门改成id等于10 UPDATE
department SET id=10 WHERE id=1; SELECT * FROM employee; SELECT * FROM
department; -- 删除部门号是2的部门 DELETE FROM department WHERE id=2;
3.6 数据约束小结

约束名关键字说明
主键primary key唯一、非空
默认default如果这一列没有值,使用默认值
非空NOT NULL这一列必须有值
唯一unique这一列不能有重复的值
外键foreign key主表中主键列,在从表中外键列
4.表与表之间的关系

 4.1 表关系的概念:

       

表与表之间的三种关系
一对多:最常用的关系 部门和员工
多对多:学生选课表 和 学生表, 一门课程可以有多个学生选择,一个学生选择多门课程
一对一:相对使用比较少。员工表 简历表, 公民表 护照表
4.2 一对多

      一对多(1:n):例如:班级和学生,部门和员工,客户和订单,分类和商品

      一对多建表原则:在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键。

4.3 多对多

     多对多(n:n):例如:老师和学生,学生和课程,用户和角色

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

   

4.4 一对一

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

     一对一的两种建表原则:

      

一对一的建表原则说明
外键唯一主表的主键和从表的外键(唯一),形成主外键关系,外键唯一UNIQUE
外键是主键主表的主键和从表的主键,形成主外键关系

4.5 一对多关系案例分析:

    需求:一个旅游线路分类中有多个旅游线路。

   表与表之间的关系:

 

具体操作:
-- 创建旅游线路分类表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;
4.6 多对多案例分析:

     需求:一个用户收藏多个线路,一个线路被多个用户收藏。

    对于多对多的关系我们需要增加一张中间表来维护他们之间的关系。

/* 创建用户表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.7 表与表之间的关系

     

表与表的关系关系的维护
一对多主外键的关系
多对多中间表,两个一对多
一对一
1)特殊一对多,从表中的外键设置为唯一;

2)从表中的主键又是外键

5.数据库设计

  5.1 数据规范化

      (1)范式的概念:

            
好的数据库设计对数据的存储性能和后期的程序开发,都会产生重要的影响。建立科学的,规范的数据库就需要满足一些规则来优化数据的设计和存储,这些规则就称为范式。

       (2)三大范式:

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

                 
 满足最低要求的范式是第一范式(1NF)。在第一范式的基础上进一步满足更多规范要求的称为第二范式(2NF)其余范式以次类推。一般说来,数据库只需满足第三范式(3NF)就行了。

5.2 1NF

       (1)1NF的概念:

             数据库表的每一列都是不可分割的原子数据项,不能是集合、数组等非原子数据项。即表中的某个列有多个值时,必须拆分为不同的列。
简而言之,第一范式每一列不可再拆分,称为原子性。

        (2)班级表

          

学号姓名班级
1张三一年三班
2李四一年二级
3王五二年三班
5.3    2NF

    (1) 2NF的概念:

        在满足第一范式的前提下,表中的每一个字段都完全依赖于主键。所谓完全依赖是指不能存在仅依赖主键一部分的列。简
而言之,第二范式就是在第一范式的基础上所有列完全依赖于主键列。
当存在一个复合主键包含多个主键列的时候,才会发生不符合第二范式的情况。比如有一个主键有两个列,不能存在这样的属性,它只依赖于其中一个列,这就是不符合第二范式。

     (2)第二范式的特点:

                 第一:一张表只描述一件事情。

                 第二:表中的每一列都完全依赖于主键

   (3)示例:

          

5.4 3NF

     (1)3NF的概念:

           在满足第二范式的前提下,表中的每一列都直接依赖于主键,而不是通过其它的列来间接依赖于主键。

           简而言之,第三范式就是所有列不依赖于其它非主键列,也就是在满足2NF的基础上,任何非主列不得传递依赖于主键。
所谓传递依赖,指的是如果存在"A → B → C"的决定关系,则C传递依赖于A。因此,满足第三范式的数据库表应该不存在如下依赖关系:主键列 → 非主键列x →
非主键列y。

     (2)示例:

     

     (3) 三大范式小结

   

范式特点
1NF原子性:表中每列不可再拆分。
2NF不产生局部依赖,一张表只描述一件事情
3NF不产生传递依赖,表中每一列都直接依赖于主键。而不是通过其它列间接依赖于主键。
 

技术
下载桌面版
GitHub
百度网盘(提取码:draw)
Gitee
云服务器优惠
阿里云优惠券
腾讯云优惠券
华为云优惠券
站点信息
问题反馈
邮箱:ixiaoyang8@qq.com
QQ群:766591547
关注微信