HeadFirstSQL学习笔记（二）

1. SELECT进阶

CASE

利用case检查现有列的值和条件，就可以结合所有update语句而不用超大型update语句，如果现有列符合条件才会在新列中填入指定的值。甚至如果没有记录符合条件时该如何处理。

update my_tableset new_column=case when column1=somevalue1then newvalue1 when column2=somevalue2then newvalue2 else newvalue3     end;-- 这里case找出第一个符合的值设定列值然后直接跳到end并推出代码，其他符合的就被忽略了。when子句可以包含AND。

ORDER BY

排序，默认升序（ASC），反转排序：DESC

根据某个列的排序返回查询结构：

select title,categoryfrom movie_tablewheretitle like 'A%'ANDcategory='family'order by title; -- 要求按照title的字母顺序返回数据，数字开头会出现在最前面

SQL排序规则：

NULL-数字-字母（大写-小写）

按照多个列进行排序：

select title,category,purchasedfrom movie_tableorder by category,purchased; -- 按照category排序后才会按照purchased排序，意思是如果第一个排序后两个相同的category,按照purchased排序

SUM( )

select sum(sales)from cookie_saleswhere first_name='Nicole';

利用GROUP BY完成分组加总：

select first_name,sum(sales)from cookie_salesgroup by first_nameorder by sum(sales) DESC;

AVG搭配GROUP BY

select first_name,avg(sales)from cookie_salesgroup by first_name;

MIN和MAX

select first_name,max(sales)from cookie_salesgroup by first_name;

COUNT

返回指定列中的行数：

select count(sale_date)from cookie_sales; -- NULL不纳入计算

DISTINCT

挑出与众不同的值（去重）

select distinct sale_datefrom cookie_salesorder by sale_date;dISTINCT搭配COUNTselect count(distinct sale_date)from cookie_sales

LIMTIT

-- 限制查询结果的数量：select first_name,sum(sales)from cookie_salesgroup by first_nameorder by sum(sales) DESClimit 2;  -- 指定呈现的结果为两行-- 只限第二名出现：select first_name,sum(sales)from cookie_salesgroup by first_nameorder by sum(sales) DESClimit 1,1; -- SQL以0开始计数，1代表第二条记录，从1开始的1个记录也就是第2个

1. 多张表的数据库设计

把一张表变成两张表：

移出兴趣列并把它存储至专属表，新建的兴趣表将存储my_contacts表中所有兴趣数据，每项兴趣为一行；添加足以识别my_contacts表中每个人的兴趣的列，把first_name和last_name列加入兴趣表；

select first_name,last_name from my_contactswhere (a bunch of conditions);select interest from interestswhere first_name='somename'and last_name='lastname';

连接表：

外键是表中的某一列，它引用到另一个表的主键

外键可能与它引用的主键的名称不同；

外键使用的主键也被称为父键，主键所在的表又被称为父表；

外键能用于确认一张表中的行与另一张表中的行相对应；

外键的值可以为NULL，即使主键值不可为NULL；

外键值不需唯一，事实上，外键通常没有唯一性。

 

外键约束：

创建外键作为表的约束提供了明确的优势，如果违反了规则，约束会阻止我们意外破坏表。插入外键列的值必须已经存在于父表的来源列中，这是引用完整性。你可以使用外键来引用父表中某个唯一的值，外键不一定必须是父表的主键，但必须有唯一性。

创建带有外键的表：

create table interests(int_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) -- 这部分指定外键的来源以及外键在父表中的名字)

数据模式（schema）：

一对一：父表只有一行与子表的某行相关

一对多：A表中的某一条记录可以对应到B表中的多条记录，但B表中的某一条记录只能对应到A表中的某一条记录

多对多：不管在哪个表中添加外键，都会造成表中出现重复的数据，所以两个多对多的表之间需要一个中间桥梁junction table(连接表)，用来存储连个相关表的主键。

 

第一范式：

规则一：数据列只包含具有原子性的值

规则二：没有重复的数据组

 

组合键：由多个数据列构成的主键，组合各列后形成具有唯一性的键。

列的依赖性：当某列的数据必须随着另一列的数据的改变而改变的时候，表示第一列函数依赖于第二列。速记符号：T.x ->T.y意思是Y依赖于X

部分函数依赖：非主键的列依赖于组合主键的某个部分（但是不完全依赖于组合主键）。

传递函数依赖：如果改变任何非键列可能造成其他列的改变，即为传递依赖。

 

第二范式：

只要所有列都是主键的一部分或者表中有唯一主键列符合1NF的表也会符合2NF。

规则一：先符合1NF

规则二：没有部分函数依赖性

第三范式：

如果你的表有人工主键且没有组合主键，则符合2NF

规则一：先符合2NF

规则二：没有传递函数依赖性

 

2. 连接与多张表的操作

预填充表：表中有很多重复的值，适合预填充

以子字符串函数分开数据值：

update my_contactsset interest1=substring_index(interests,',',1); -- 要查找的分隔字符为逗号，查找第1个逗号之前的内容update my_contactsset interests=substr(interests,length(interest1)+2);-- 把interests列的值改变为这个查询指定的任何内容，但要去除interest1列存储的值，有逗号与空格；lengh()返回括号中参数字符串的长度（这里为5），再加上2，括号与空格的长度，所以会从原本的ingterests列的左侧（字符串开始处）开始移除7个字符

SUBSTR函数：返回本列内原始字符串的一部分，它接受字符串并切除我们用括号指定的第一部分，然后返回剩下部分。

接下来interests2\interests3\interests4需要做类似处理

完整代码：

update my_contacts setinterest1=substring_index(interests,',',1);interests=substr(interests,length(interest1)+2);interest2=substring_index(interests,',',1);interests=substr(interests,length(interest2)+2);interest3=substring_index(interests,',',1);interests=substr(interests,length(interest3)+2);interest4=interests;

查询的三种乐趣

同时create,select,insert:

1）首先创建（create）profession表，然后填入select的查询结果

create table profession(Id int(11) not null auto_increment primary key,profession varchar(20));insert into profession(profession)select profession from my_contactsgroup by professionorder by profession; -- 以select的查询结果填满profession表的profession列

2）利用select进行create table，然后alter以添加（ADD）主键字段

create table profession asselect profession from my_contactsgroup by professionorder by profession; -- 创建只有一列的profession表并填入select的查询结果alter table professionadd column id int not_null auto_increment first,add primary key(id); -- 然后用alter修改表以添加主键字段

同一时间create,select,insert

3）create的同时设置主键并利用select填入数据

这是只需要一个步骤的方式：创建profession表的同时设置主键列以及另一个varchar类型的列来存储职业，同时还要填入select的查询结果

create table profession(Id int(11) not null auto_increment primary key,profession varchar(20) -- 这里如果没有给新表设计两个列，AS只会创建一列，并采用与select的查询结果相同的列名与数据类型)    asselect profession from my_contactsgroup by profession  -- 后两句都是select的一部分order by profession; -- 创建profession表时一并创建主键与profession列，并以select的查询结果填充profession列 

AS

1）AS能把select的查询结果填入新表中。如果不指定新表具有带有新名称的两列，AS只会创建一列，且该列的列名及数据类型与select的查询结果相同。

2）创建列\表的别名，在查询中首次使用原始列名\表名的地方后接AS并设定要采用的别名。

select profession as mc_proffrom my_contacts as mcgroup by profession order by profession;-- 省略AS是一样的：select profession mc_proffrom my_contacts mcgroup by profession order by profession;

 

3. 表的联接

CROSS JOIN 

交叉联接，返回两张表的每一行相乘的结果

select t.boy,b.boy  --表名.列名from toys as t   -- 先执行fromcross joinboys as b; -- 从toys表里选择toy，从boys表选择boy列，进行交叉连接-- 省略写法，CROSS JOIN可以不写：select toys.boy,boys.boyfrom toys，boys ;

内联接

语法：

select somecolumnsfrom table1inner jointable2on somecondition; -- 这里on可以改用where

相等联接EQUIJOIN

测试相等性的内联接（表之间为一对一关系）：使用内联接加上相等运算符=

select boy.boy,toy.toyfrom boysinner jointoyson boys.toy_id=toys.toy_id 

不等联接non-equijoin

返回任何不相等的记录

select boy.boy,toy.toyfrom boysinner jointoyson boys.toy_id<>toys.toy_idorder by boys.boy;  -- 可以看出每个男孩没有的玩具

自然联接natural join

只有在联接的列在两张表中的名称都相同时才会有用（相同的列名），自然联接会识别出每个表里相同的列名

select boy.boy,toy.toyfrom boysnatural jointoys;   -- 不需要on