| 标题 | HeadFirstSQL:学习笔记2/2 |
| 范文 | 第七章 多表设计 1.外键是表中的某一列，它引用到另一个表的具有唯一性的键，一般是主键。外键用于确认一张表中的行与另一张表中的行相对应。 2.外键引用的主键称之为父键，父键所在的表称之为父表。 3.外键不唯一，可以为null，为null表示在父表中没有相应地主键。 4.引用完整性：插入外键列的值必须在父表的来源列中。外键能够保证引用完整性，这是外键重要的功能。 5.约束定义了表中的某些规则，防止表结构遭到破坏。 6.定义外键： create table nickname( id int not null auto_increment primary key, nname varchar(30) not null， player_id int); mysql> alter table nickname add constraint players_id_fk foreign key(player_id) references players(id); 这里注意：外键的类型要和主键的类型相同，否则会出现errno150错误。 7.表之间的关系 一对一：用的很少 一对多：通过外键连接 多对多：复杂，通过连接表junction table存储两张表的主键 组合键：多个键构成主键 8.范式： 第一范式(1NF)。规则1：数据列只包含有原子性的值(1列同时存储多个信息字段)。规则2：没有重复的数据组(多列存储相同类型字段，如color1列，color2列)。 第二范式(2NF)。规则1：先符合1NF。规则2：没有部分函数依赖性(列1、2是组合主键，如列3根据列1的数据产生，但与列2无关，称为列3部分依赖)。 u 函数依赖：一列的值根据另一列的值的改变而改变。 u 部分函数依赖：非主键的列依赖于主键的一部分。 u 传递函数依赖：任意非键列与另一非键列有关联。 满足1NF，并且主键列只有一列，一定符合2NF。 第三范式(3NF)。规则1：首先符合2NF。规则2：没有传递函数依赖性。 关于三个范式的理解(以下内容引用自： http://blog.csdn.net/famousdt/article/details/6921622)： ◆ 第一范式(1NF)：强调的是列的原子性，即列不能够再分成其他几列。 考虑这样一个表：【联系人】(姓名，性别，电话) 如果在实际场景中，一个联系人有家庭电话和公司电话，那么这种表结构设计就没有达到 1NF。要符合 1NF 我们只需把列(电话)拆分，即：【联系人】(姓名，性别，家庭电话，公司电话)。1NF 很好辨别，但是 2NF 和 3NF 就容易搞混淆。 ◆ 第二范式(2NF)：首先是 1NF，另外包含两部分内容，一是表必须有一个主键;二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。 考虑一个订单明细表：【OrderDetail】(OrderID，ProductID，UnitPrice，Discount，Quantity，ProductName)。 因为我们知道在一个订单中可以订购多种产品，所以单单一个 OrderID 是不足以成为主键的，主键应该是(OrderID，ProductID)。显而易见 Discount(折扣)，Quantity(数量)完全依赖(取决)于主键(OderID，ProductID)，而 UnitPrice，ProductName 只依赖于 ProductID。所以 OrderDetail 表不符合 2NF。不符合 2NF 的设计容易产生冗余数据。 可以把【OrderDetail】表拆分为【OrderDetail】(OrderID，ProductID，Discount，Quantity)和【Product】(ProductID，UnitPrice，ProductName)来消除原订单表中UnitPrice，ProductName多次重复的情况。 ◆ 第三范式(3NF)：首先是 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。 考虑一个订单表【Order】(OrderID，OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity)主键是(OrderID)。 其中 OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity 等非主键列都完全依赖于主键(OrderID)，所以符合 2NF。不过问题是 CustomerName，CustomerAddr，CustomerCity 直接依赖的是 CustomerID(非主键列)，而不是直接依赖于主键，它是通过传递才依赖于主键，所以不符合 3NF。 通过拆分【Order】为【Order】(OrderID，OrderDate，CustomerID)和【Customer】(CustomerID，CustomerName，CustomerAddr，CustomerCity)从而达到 3NF。 第二范式(2NF)和第三范式(3NF)的概念很容易混淆，区分它们的关键点在于，2NF：非主键列是否完全依赖于主键，还是依赖于主键的一部分;3NF：非主键列是直接依赖于主键，还是直接依赖于非主键列。 第八章 联接与多张表的操作 1.使用AS将查询结果作为数据插入新表。Create table new (id int not null auto_increment primary key, profession varchar) as select profession from mytable group by profession order by profession; 2.使用AS添加别名。Select profession AS my_profes from my_contacts;AS可以省略。 3.交叉联接： Select t.toy, b.boy from toys as t cross join boys as b; cross join 返回两张表的每一行相乘(笛卡尔乘)的结果。 4.inner join利用条件式里的比较运算符结合两张表; Select boys.boy, toys.toy from boys inner join toys on boys.toy_id = toys.toy_id;相等联接 Select boys.boy, toys.toy from boys inner join toys on boys.toy_id<>toys.toy_id;不等联接 Select boys.boy, toys.toy from boys nature join toys;自然联接(两张表中含有相同列名的相等内连接) 第九章 子查询 1.子查询：被另一个查询包围的查询，也可称之为内层查询。 select interest from interest as inte where inte.playerID in (select play erID from interest); 2.子查询通常与as和连接一起使用，提高查询效率。 3.In/notin与exists/not exists比较： 摘自：http://blog.csdn.net/ldl22847/article/details/7800572 in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。 如果查询的两个表大小相当，那么用in和exists差别不大。 如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in： 例如：表A(小表)，表B(大表) a： select * from A where cc in (select cc from B) 效率低，用到了A表上cc列的索引; select * from A where exists(select cc from B where cc=A.cc) 效率高，用到了B表上cc列的索引。 相反的 b： select * from B where cc in (select cc from A) 效率高，用到了B表上cc列的索引; select * from B where exists(select cc from A where cc=B.cc) 效率低，用到了A表上cc列的索引。 not in 和not exists如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引;而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。 in 与 =的区别 select name from student where name in ('zhang','wang','li','zhao'); 与 select name from student where name='zhang' or name='li' or name='wang' or name='zhao' 的结果是相同的。 第十章 外联接、内联接与联合 1.左外联接：匹配左表中的每一行及右表中符合要求的行。 Select g.girl,b.boy from girls as g left out join toys as t on g.toy_id = t.toy_id; 2.外联接与内联接的区别是：外联接一定能够返回结果集数据行(找不到相符合的返回NULL)，结果行数等于右表行数。 3.右外联接：right out join，结果行数等于左表函数。 4.自引用外键：self-referencing foreign key，出于其他目的而引用同一张表的主键。 5.自联接：适用于含有子引用外键的表，将单一表当成两张具有完全相同信息的表进行查询。使用inner join完成查询。表cc的boss_id自引用外键id。 Select c1.name,c2.name as boss from cc c1 inner join cc c2 on c1.boss_id = c2.id; 6.union：联合，组合查询结果集; Select profession from A union select profession from B;相同profession只出现一次 Select profession from A union all select profession from B;相同profession出现多次 7.使用union创建新表：任何select都可以创建新表 Create table test as select profession from A union select sex from B; 8.intersect:交集 9.Except:差集 第十一章 约束、视图和事务 1.约束：例如check(限定允许插入某个列的值，coin char(1) check in(‘A’,’B’,’C’)),not null, primary key, foreign key, unique等。 添加约束：alter table mytable add constraint check gender in (‘M’,’F’); 2.视图：虚拟数据表 创建视图:create view webdesign AS select name,sex from table1 nature join table2 where table1.id = table2.id; 查看视图：select * from webdesign; 删除视图：drip view webdesign; 3.事务：transaction完成一组工作的sql，所有步骤必须能够全部完成，否则不完成任何一项任务。 事务过程：start transaction->执行sql语句->commit/rollback 显示创作数据表代码：show create table players; 必须使用支持事务的存储引擎：InnoDB和BDB. 改变存储引擎：alter table yourtable TYPE=InnoDB; 第十二章 安全性 1.设定用户密码：set password for ‘root’@’localhost’ = password(‘aaa’); 2.添加新用户：create user conan identified by ‘conanswp’; 3.授权：grant select on table1 to conan 4.撤销权限：revoke select on table1 from conan 5.创建角色:create role data_entry; 6.授权：grant select,update,insert on table1 to data_entry; 7.使用角色：grant data_entry to conan; 8.删除角色：drop role data_entry; |
| 随便看 |
|
在线学习网范文大全提供好词好句、学习总结、工作总结、演讲稿等写作素材及范文模板，是学习及工作的有利工具。