一、外键
表my_contacts(contact_id,phone,email,gender,interests),由于interests可以有多个值,不具备1NF,因此可以将interests单独作为一个表,interests(interest_id,interest,contact_id),contact_id用来表示“兴趣”是谁的,它的值与my——contacts中的contact_id一致。像interests表中的contact_id列称为外键(foreign key)。
外键用于确认一张表中的行与另外一张表中的行对应。主键不能为null,但是外键可以是null,外键为null时表示父表中的行没有对应。外键的值并没有唯一性。外键不一定是父表中的主键,但是它在父表中必须有唯一性。插入外键的值必须已经在父表中的行里面出现过的值(引用完整性)。
CREARE TABLE interests ( interest_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, interest VARCHAR(50) NOT NULL, contact_id INT NOT NULL, CONSTRAINT my_contacts_contact_id_fk #键的来源 FOREIGN KEY(contact_id) REFERENCES my_contacts(contact_id) );
有外键约束时,如果试图删除父表中的行或者改变值时,而其值为另个表的外键,那么这时会受到警告。如果真的需要如此操作,可以先移除外键,再做修改。
二、表间关系
表间关系有三种:一对一(不常见)、一对多、多对多。对于多对多,可以用junction table来避免表中过多的重复组。
woman(woman_id,woman,shoes_is)、shoes(shoes_id,name)
woman(woman_id,woman)、shoes(shoes_id,name,woman_id)
woman(woman_id,woman)、shoes(shoes_id,name)、junctiontable(woman_id,shoes_id)
三、组合键 、依赖 、2NF、3NF
组合键是用多个数据列构成的主键,组合后具有唯一性,起主键的作用。例如:表中某两列均不是主键,但是组合后就是主键了。
依赖 :列x的值改变那么列y的值必须改变,则称列y函数依赖与列x,记为T.y->T.x。例如:名字简写依赖于名字,城市依赖于国家等。部分函数依赖:T.y->T.x,列x与其他列组合成主键。传递函数依赖:两个非主键列形成的函数依赖。2NF:符合1NF,没有部分函数依赖。如果表中有人工主键且没有组合主键,则符合2NF。3NF:符合2NF,没有传递函数依赖。
四、AS
AS能把SELECT查询的内容插入到一个新表中,不过,要求查询的列名与表的列名一致。
CREATE TABLE profession ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY; profession VARCHAR(20); ); INSERT INTO profession (profession) SELECT profession FROM my_contacts GROUP BY profession ORDER BY profession; CREATE TABLE profession AS SELECT profession FROM my_contacts GROUP BY profession ORDER BY profession; ALTER TABLE profession ADD COLUMN id INT NOT NULL AUTO_INCREMENT FIRST; ADD PRIMARY KEY (id); CREATE TABLE profession ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY; profession VARCHAR(20); )AS SELECT profession FROM my_contacts GROUP BY profession ORDER BY profession;
AS还能将列取别名,只需在查询中首次出现列的地方接AS即可。(此时,AS可以省略)
CREATE TABLE profession ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY; prof VARCHAR(20); )AS SELECT profession AS prof FROM my_contacts GROUP BY prof ORDER BY prof;
五、 内联接
交叉联接CROSS JOIN会将两张表的所有行相乘,再从相乘得到的表中进行查询等。
有表toys(toy_id,toy),有30行值和表boys(boy_id,boy),有40行值。
SELECT t.toy b.boy FROM toys AS t CROSS JOIN boys AS b; SELECT toys.toy ,boys.boy FROM toys ,boys;#与上等价 SELECT * FROM toys CROSS JOIN boys ;#返回1200行值,4列,表很大时最好不交叉联接
内联接的格式是:
SELECT somecolumns FROM table1 INNER JOIN table2 ON conditions;
有表toys(toy_id,toy),有30行值和表boys(boy_id,boy,toy_id),有40行值。
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 ORDER BY boys.boy; #不相等联接,查询男孩没有拥有的玩具 SELECT boys.boy toys.toy FROM boys NATURAL JOIN toys; #自然联接,两个相同名的列合到一起,查询男孩拥有的玩具
六、子查询
子查询就是查询中的查询,它有两部分:内层查询和外层查询。
SELECT mc.name,mc.phone,jc.title FROM my_contacts AS mc NATURAL JOIN job_current AS jc WHERE jc.title IN ('Cook','Waiter','Web Designer'); #外层查询 SELECT title FROM job_listings GROUP BY title ORDER title; #内层查询 SELECT mc.name,mc.phone,jc.title FROM my_contacts AS mc NATURAL JOIN job_current AS jc WHERE jc.title IN (SELECT title FROM job_listings); #可以用IN\NOT IN,单个值用等于 #子查询就是内层查询加上外层查询
子查询的结构为:
SELECT some_columns FROM table WHERE column=(SELECT column FROM table);
SELECT name FROM my_contacts WHERE zip_code=(SELECT zip_code FROM zip WHERE city='Menphis' AND state='TN'); #子查询也可以用联接实现 SELECT name FROM my_contacts NATURAL JOIN zip WHERE zip.city='Menphis' AND state='TN';
在我的联系人里,谁赚的钱最多?
SELECT mc.name,jc.salary FROM my_contacts AS mc NATURAL JOIN job_current AS jc WHERE jc.salary=(SELECT MAX(jc.salary) FROM job_current jc);
子查询可以放在SELECT子句中用来表示某次欲选的值,这时一次只能返回一个值。
SELECT mc.name (SELECT state FROM zip WHERE mc.zip=zip.zip) AS state FROM my_contacts mc;
非关联子查询:如果子查询可以独立运行且不会引用外层查询的任何结果。
比Andy工资高的人的信息:
SELECT mc.name,jc.salary FROM my_contacts AS mc NATURAL JOIN job_current AS jc WHERE jc.salary> (SELECT jc.salary FROM my_contacts mc NATURAL JOIN job_current jc WHERE email='andy@weathererorama.com');
关联子查询:内层查询的解析需要依赖外层查询的结果。
联系人中有三项兴趣爱好的人的信息:
SELECT mc.name FROM my_contacts AS mc WHERE 3= (SELECT COUNT(*) FROM interets WHERE contact_id=mc.contact_id);
哪些联系人还没有在job_current表中出现:
SELECT mc.name FROM my_contacts AS mc WHERE NOT EXIST (SELECT COUNT(*) FROM job_current jc WHERE mc.contact_id=jc.contact_id);
要查找哪些人在job_current中出现,只需要将上面的NOT EXIST改为Exist即可。
七、外联接
外联接是返回某一张表的所有行,并且带有另外一张表符合条件的行。内联接只是返回有意义的行。外联接会返回值中如果后一张表没有对应就返回NULL,如果有多个值会返回每一组符合条件的值。
做外联接以左表(第一个表)为标准,又联接以右表为标准。
toys表中有值(1,dog)、(2,cat);boys表中有值(1,Lili,1)、(2,Xiaohong,1),那么如下操作:
SELECT b.boy,t.toy FROM toys t LEFT OUTER JOIN boys b ON t.toy_id=b.toy_id;
那么会得到结果:(Lili,dog),(Xiaohong,dog),(NULL,cat)
八、自联接
自联接把一张表当作两张表进行联接操作。
SELECT p1.name,p2.name AS boss #自联接 FROM personal p1 INNER JOIN personal p2 ON p1.id=p2.boss_id; SELECT p.name, #自联接转换成子查询 (SELECT name FROM personal WHERE p.boss_id=id )AS boss FROM personal p;
九、UNION
SELECT title FROM job_current UNION SELECT title FROM job_desired UNION SELECT title FROM job_listings ORDER BY title;
查询结果只有一列title,它将只要每个查询中出现的title都显示出来,并且重复的只显示一次。如果将UNION改为UNION ALL会将重复的也显示出来。
CREATE TABLE job AS SELECT title FROM job_current UNION SELECT title FROM job_desired UNION SELECT title FROM job_listings ORDER BY title;
出来UNION外,还可以用INTERSECT和EXCEPT来联合,INTERSECT表示将每个查询结果相交,EXCEPT表示将前一个结果减去后一个结果。
十、其它
SELECT name,rating FROM restaurant_rating WHERE rating>ALL (SELECT rating FROM restaurant_rating WHERE name IN('Jack','Tom','Harry');
大于ALL表示大于集合中的最大值的所有值;小于ALL表示小于集合中的最小值的所有值。
SOME和ANY也有相同的用法,SOME和ANY等价。
大于SOME表示大于集合中的最小值的所有值;小于SOME表示小于集合中的最大值的所有值。
这里还可以用>=、<=。
MySQL中能够规定日期的格式:
SELECT DATe_FORMAT(date,'%M %Y') FROM table; #以某月某年显示
MySQL中创建临时表很简单,只需要在table前面加一个temporary:
CREATE TEMPORARY TABLE temp ( some_int INT; some_data VARCHAR(50); ); CREATE TEMPORARY TABLE temp AS SELECT *FROM table;
数据转换需要用到函数:CAST(column,TYPE)
SELECT CAST('2005-02-06' AS DATE); SELECT CAST(2 AS DECIMAL);
不适用CAST的场合有:浮点数转换为整数;TIME、Date、DATETIME、CHAR转换为DECIMAL或INTEGER。
添加索引能够联接多张表并强化数据的完整性。对于很大的表,索引能够加快查询速度,添加索引的命令如下:
ALTER TABLE my_contacts ADD INDEX (name);
查看当前用户,当前日期,当前时间 :
SELECT CURRENT_USER; SELECT CURRENT_DATE; SELECT CURRENT_TIME;
一些有用的数学函数:
SIN(x),ASIN(x),ABS(x),EXP(x),LN(x),LOG(x),LOG(x,y),SIGN(x),SQRT(x),POWER(x,y),PI(),RAND()
CEIL(x)比x大的最小整数,ROUND(x)四舍五入至整数,FORMAT(x,y)四舍五入保留y位小数得到文本,
ROUND(x,y)四舍五入保留y位小数得到数字,MOD(x,y)x除以y的余数,TRUNCATE(x,y)截断至y位小数
相关推荐
常见的规范化形式包括第一范式(1NF)、第二范式(2NF)和第三范式(3NF)等。 - **第一范式**:确保表中的每一列都是不可分割的基本数据项。 - **第二范式**:在满足1NF的基础上,确保所有非主键字段完全依赖于主键。 ...
了解数据库设计原则,包括第一范式(1NF)、第二范式(2NF)和第三范式(3NF)。 3. **SQL语句分类**:包括DML(Data Manipulation Language)用于数据操作,如SELECT、INSERT、UPDATE和DELETE;DDL(Data ...
你需要遵循范式理论,如第一范式(1NF)、第二范式(2NF)和第三范式(3NF),以减少数据冗余和提高数据一致性。此外,考虑使用实体关系模型(ER模型)来清晰地定义数据结构。 2. 安全性:保护数据库免受未经授权的...
在"04第四章.ppt"中,可能详细介绍了如何进行数据库规范化,包括第一范式(1NF)、第二范式(2NF)和第三范式(3NF),以及更高级别的BCNF和4NF,以减少数据冗余和提高数据一致性。 高级查询是Sql Server的重要组成...
3. 第三范式(3NF):在2NF基础上,消除传递依赖,即非主属性独立于其他非主属性。 4. BCNF(巴斯-科德范式):在3NF基础上,任何非平凡的函数依赖的左部都包含候选键。 5. 第四范式(4NF):消除多值依赖,即消除非...
- **第三范式(3NF)**:进一步要求非主键列之间没有函数依赖,消除传递依赖。 #### 2. **实体关系模型** 实体关系模型(ER Model)是一种用于表示实体类型及其之间关系的图形化方法。在设计SQLServer数据库时,...
第二章 数据库设计:这一章深入讨论了数据库设计的原理,包括需求分析、实体关系模型(ER模型)、范式理论(1NF, 2NF, 3NF等),以及如何创建数据库表结构。通过实例,学习者将学会如何设计符合业务需求的数据模型。...
2. **范式理论**:数据库设计中遵循的一系列规范,如第一范式(1NF)、第二范式(2NF)、第三范式(3NF)和BCNF(巴斯-科德范式)。这些范式旨在减少数据冗余,提高数据一致性。 3. **数据库模式与视图**:模式是...
3. 第三范式(3NF):非主键列只依赖于主键,不依赖其他非主键列。 4. 第四范式(4NF):消除多值依赖,确保单一事实对应单一值。 三、数据类型 DB2支持多种数据类型,如: - CHAR(n):固定长度字符串,最大长度...
第四章至第八章可能涉及更高级的主题,如关系数据库的规范化理论、数据库设计的范式(1NF, 2NF, 3NF, BCNF等)、事务处理、并发控制和恢复机制。这些章节的习题会帮助读者理解和应用数据库设计的最佳实践,以避免...
3. **范式理论**:遵循第一范式(1NF)、第二范式(2NF)、第三范式(3NF)和BCNF(博科斯范式),以减少数据冗余和提高数据独立性。 4. **索引设计**:合理使用索引可以显著提升查询性能。非聚簇索引和聚簇索引各...
10. 第二范式(2NF): - 27. 提到的关系模式可能不满足2NF,因为存在非主属性对候选码的部分函数依赖。 11. 关系模式分解(Database Normalization): - 28. 如果将关系模式R分解为`R1(A,B,E)`和`R2(B,C,D)`,...
遵循第一范式(1NF)和第三范式(3NF)的设计原则,可以减少冗余,提高数据的一致性和查询效率。 7. **数据库参数调整**: 深入了解数据库的配置选项,适时调整内存分配、缓冲区大小、并发连接数等参数,能够根据...
- **第三范式**(3NF):在满足2NF的基础上,进一步要求非主属性不传递依赖于主码。也就是说,所有非主属性都应该直接依赖于主码,而不是通过其他非主属性间接依赖。 通过理解这些概念和技术,初学者可以更好地掌握...
- 关系模型:SQL基于的关系数据库模型,包括实体、属性和关系的概念,以及第一范式(1NF)、第二范式(2NF)和第三范式(3NF)等规范化理论。 2. **SQL基本语法**: - DDL(Data Definition Language):用于创建...
第五章可能探讨了数据库设计,包括范式理论,如第一范式(1NF)、第二范式(2NF)和第三范式(3NF)。理解这些范式有助于创建有效且无冗余的数据模型,提高数据的一致性和完整性。 第六章提到的查询优化是数据库...
11. 数据库设计:良好的数据库设计涉及范式理论,如第一范式(1NF)、第二范式(2NF)和第三范式(3NF),旨在减少数据冗余和提高数据一致性。 12. 安全性:SQL也提供了权限管理功能,如用户账户、角色和权限设置,...
2. 关系设计:将ER模型转换为关系模式,包括函数依赖、范式理论,如第一范式(1NF)、第二范式(2NF)和第三范式(3NF)。 3. 数据库规范化:通过消除冗余和异常,提高数据的一致性和完整性。 四、数据库查询优化 1...
1. 规范化理论:理解第一范式(1NF)、第二范式(2NF)和第三范式(3NF),以及BCNF和4NF等更高级的范式。 2. E-R模型:使用实体-关系模型进行数据库设计。 3. 物理设计:理解表空间、分区、缓冲池等物理结构对性能...