`

第三部分 2NF 子查询 联接

    博客分类:
  • SQL
阅读更多

一、外键

        表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位小数

分享到:
评论

相关推荐

    SQLServer数据库设计和高级查询4_2

    常见的规范化形式包括第一范式(1NF)、第二范式(2NF)和第三范式(3NF)等。 - **第一范式**:确保表中的每一列都是不可分割的基本数据项。 - **第二范式**:在满足1NF的基础上,确保所有非主键字段完全依赖于主键。 ...

    北大青鸟SQL第三章

    了解数据库设计原则,包括第一范式(1NF)、第二范式(2NF)和第三范式(3NF)。 3. **SQL语句分类**:包括DML(Data Manipulation Language)用于数据操作,如SELECT、INSERT、UPDATE和DELETE;DDL(Data ...

    数据库方面注意事项及连接数据库

    你需要遵循范式理论,如第一范式(1NF)、第二范式(2NF)和第三范式(3NF),以减少数据冗余和提高数据一致性。此外,考虑使用实体关系模型(ER模型)来清晰地定义数据结构。 2. 安全性:保护数据库免受未经授权的...

    SqlServer数据库设计和高级查询

    在"04第四章.ppt"中,可能详细介绍了如何进行数据库规范化,包括第一范式(1NF)、第二范式(2NF)和第三范式(3NF),以及更高级别的BCNF和4NF,以减少数据冗余和提高数据一致性。 高级查询是Sql Server的重要组成...

    SQL规范化查询语句练习题

    3. 第三范式(3NF):在2NF基础上,消除传递依赖,即非主属性独立于其他非主属性。 4. BCNF(巴斯-科德范式):在3NF基础上,任何非平凡的函数依赖的左部都包含候选键。 5. 第四范式(4NF):消除多值依赖,即消除非...

    SQLServer数据库设计和高级查询6_1

    - **第三范式(3NF)**:进一步要求非主键列之间没有函数依赖,消除传递依赖。 #### 2. **实体关系模型** 实体关系模型(ER Model)是一种用于表示实体类型及其之间关系的图形化方法。在设计SQLServer数据库时,...

    MySQL数据库基础实例教程(第2版)(微课版)-教学课件.zip

    第二章 数据库设计:这一章深入讨论了数据库设计的原理,包括需求分析、实体关系模型(ER模型)、范式理论(1NF, 2NF, 3NF等),以及如何创建数据库表结构。通过实例,学习者将学会如何设计符合业务需求的数据模型。...

    北大青天鸟 S2 数据库的设计和高级查询 的全部答案

    2. **范式理论**:数据库设计中遵循的一系列规范,如第一范式(1NF)、第二范式(2NF)、第三范式(3NF)和BCNF(巴斯-科德范式)。这些范式旨在减少数据冗余,提高数据一致性。 3. **数据库模式与视图**:模式是...

    db2常用命令大全

    3. 第三范式(3NF):非主键列只依赖于主键,不依赖其他非主键列。 4. 第四范式(4NF):消除多值依赖,确保单一事实对应单一值。 三、数据类型 DB2支持多种数据类型,如: - CHAR(n):固定长度字符串,最大长度...

    数据库系统教程(施伯乐,第三版)课后习题答案,自己拍的照片

    第四章至第八章可能涉及更高级的主题,如关系数据库的规范化理论、数据库设计的范式(1NF, 2NF, 3NF, BCNF等)、事务处理、并发控制和恢复机制。这些章节的习题会帮助读者理解和应用数据库设计的最佳实践,以避免...

    SQL Server数据库设计和高级查询内测

    3. **范式理论**:遵循第一范式(1NF)、第二范式(2NF)、第三范式(3NF)和BCNF(博科斯范式),以减少数据冗余和提高数据独立性。 4. **索引设计**:合理使用索引可以显著提升查询性能。非聚簇索引和聚簇索引各...

    数据库测试_答案(2).docx

    10. 第二范式(2NF): - 27. 提到的关系模式可能不满足2NF,因为存在非主属性对候选码的部分函数依赖。 11. 关系模式分解(Database Normalization): - 28. 如果将关系模式R分解为`R1(A,B,E)`和`R2(B,C,D)`,...

    数据库的查询优化技术

    遵循第一范式(1NF)和第三范式(3NF)的设计原则,可以减少冗余,提高数据的一致性和查询效率。 7. **数据库参数调整**: 深入了解数据库的配置选项,适时调整内存分配、缓冲区大小、并发连接数等参数,能够根据...

    数据库学习总结——小白篇

    - **第三范式**(3NF):在满足2NF的基础上,进一步要求非主属性不传递依赖于主码。也就是说,所有非主属性都应该直接依赖于主码,而不是通过其他非主属性间接依赖。 通过理解这些概念和技术,初学者可以更好地掌握...

    精通SQL—结构化查询语言详解

    - 关系模型:SQL基于的关系数据库模型,包括实体、属性和关系的概念,以及第一范式(1NF)、第二范式(2NF)和第三范式(3NF)等规范化理论。 2. **SQL基本语法**: - DDL(Data Definition Language):用于创建...

    一到六章sql实验报告

    第五章可能探讨了数据库设计,包括范式理论,如第一范式(1NF)、第二范式(2NF)和第三范式(3NF)。理解这些范式有助于创建有效且无冗余的数据模型,提高数据的一致性和完整性。 第六章提到的查询优化是数据库...

    SQL必知必会(第三版)

    11. 数据库设计:良好的数据库设计涉及范式理论,如第一范式(1NF)、第二范式(2NF)和第三范式(3NF),旨在减少数据冗余和提高数据一致性。 12. 安全性:SQL也提供了权限管理功能,如用户账户、角色和权限设置,...

    数据库系统概论(第三版)

    2. 关系设计:将ER模型转换为关系模式,包括函数依赖、范式理论,如第一范式(1NF)、第二范式(2NF)和第三范式(3NF)。 3. 数据库规范化:通过消除冗余和异常,提高数据的一致性和完整性。 四、数据库查询优化 1...

    DB2 认证准备教程

    1. 规范化理论:理解第一范式(1NF)、第二范式(2NF)和第三范式(3NF),以及BCNF和4NF等更高级的范式。 2. E-R模型:使用实体-关系模型进行数据库设计。 3. 物理设计:理解表空间、分区、缓冲池等物理结构对性能...

Global site tag (gtag.js) - Google Analytics