HeadFirstSQL学习笔记（二）

1. SELECT进阶

CASE

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

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

ORDER BY

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

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

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

SQL排序规则：

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

按照多个列进行排序：

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

SUM( )

select sum(sales)
from cookie_sales
where first_name='Nicole';

利用GROUP BY完成分组加总：

select first_name,sum(sales)
from cookie_sales
group by first_name
order by sum(sales) DESC;

AVG搭配GROUP BY

select first_name,avg(sales)
from cookie_sales
group by first_name;

MIN和MAX

select first_name,max(sales)
from cookie_sales
group by first_name;

COUNT

返回指定列中的行数：

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

DISTINCT

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

select distinct sale_date
from cookie_sales
order by sale_date;
dISTINCT搭配COUNT
select count(distinct sale_date)
from cookie_sales

LIMTIT

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

1. 多张表的数据库设计

把一张表变成两张表：

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

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

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

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

完整代码：

update my_contacts set
interest1=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_contacts
group by profession
order by profession; -- 以select的查询结果填满profession表的profession列

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

create table profession as
select profession from my_contacts
group by profession
order by profession; -- 创建只有一列的profession表并填入select的查询结果
alter table profession
add 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的查询结果相同的列名与数据类型
)    as
select profession from my_contacts
group by profession  -- 后两句都是select的一部分
order by profession; -- 创建profession表时一并创建主键与profession列，并以select的查询结果填充profession列 

AS

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

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

select profession as mc_prof
from my_contacts as mc
group by profession 
order by profession;
-- 省略AS是一样的：
select profession mc_prof
from my_contacts mc
group by profession 
order by profession;

 

3. 表的联接

CROSS JOIN 

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

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

内联接

语法：

select somecolumns
from table1
inner join
table2
on somecondition; -- 这里on可以改用where

相等联接EQUIJOIN

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

select boy.boy,toy.toy
from boys
inner join
toys
on boys.toy_id=toys.toy_id 

不等联接non-equijoin

返回任何不相等的记录

select boy.boy,toy.toy
from boys
inner join
toys
on boys.toy_id<>toys.toy_id
order by boys.boy;  -- 可以看出每个男孩没有的玩具

自然联接natural join

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

select boy.boy,toy.toy
from boys
natural join
toys;   -- 不需要on