SQL学习笔记2.0

SELECT进阶

1. 利用CASE检查现有列的值和条件，就可以结合所有UPDATE语句。
   UPDATE my_table
SET new_column =
CASE
WHEN column1 = somevalue1
THEN newvalue1
WHEN column2 = somevalue2
AND rating = 'G'
THEN newvalue2
ELSE newvalue3
END;

   • SET new_column 根据下列条件适当的更新
   • WHEN…THEN….当符合这个条件，把new_column 设定为这个值
   • ELSE任何不符合以上条件，被设置为此值

2. 分类并排序ORDER BY
   SELECT title, category
FROM movie_table
WHERE
title LIKE 'A%'
AND
category = 'family'
ORDER BY title;

   • ORDER BY按title的字母顺序返回数据

   按多列排序
   SELECT * FROM movie_table
ORDER BY category, purchased, title;

   • 先按分类排列，再按时间排列。再按字母排列

3. DESC 反转顺序
   SEKECT title, purchased
FROM movie_table
ORDER BY title ASC, purchased DESC;

   • ASC,加上关键字
   • DESC，从9到1的排列顺序

4. SUM
   SELECT SUM(sales)
FROM cookie_sales
WHERE first_name = 'Nicole';

5. GROUP BY 分组加总
   SELECT first_name, SUM(sales)
FROM cookie_sales
GROUP BY first_name
ORDER BY SUM(sales)DESC;

   • SUM(sales) 加总sales列的所有数据
   • GROUP BY first_name 根据first_name值分组
   • ORDER BY SUM（sales） 必须根据加总所用的列排序
   • DESC由高到低排序

6. 计算平均值后排序 AVG
   SELECT first_name, AVG(sales)
FROM cookie_sales
GROUP BY first_name;

7. MIN和MAX
   SELECT first_name, MAX(sales)
FROM cookie_sales
GROUP BY first_name;

8. COUNT 返回指定列中的行数
   SELECT COUNT(sale_date)
FROM cookie_sales;

   • COUNT 返回sale_date列中的行数

9. 选出与众不同的值
   SELECT DISTINCT sale_date
FROM cookie_sales

   • DISTINCT 是关键字，不需要括号

   DISTINCT 和COUNT 函数一起使用
   SELECT COUNT ( DISTINCT sale_date )
FROM cookie_sales;

10. LIMIT 指定显示查询结果的数量
    SELECT first_name, SUM(sales)
FROM cookie_sales
GROUP BY first_name
ORDER BY SUM(sales) DESC
LIMIT 2;

    • LIMIT 指定呈现结果为两行

    LIMIT 0,4:0表示从0开始计算，4表示返回查询结果的数量
    SELECT first_name, SUM(sales)
FROM cookie_sales
CROUP BY first_name
ORDER BY SUM(sales) DESC
LIMIT 1, 1;

    • 1代表显示第二条记录

拓展你的表

1. SUBSTRING_INDEX 指定筛选的第一个标准
   SELECT * FROM my_contacts
WHERE gender = 'F'
AND status = 'single'
AND state='MA'
AND SUBSTRING_INDEX(interests, ',' , 1) = 'animales';

   • interest这列中，只有第一个逗号前面试animales才能显示
2. • 对数据库内的数据描述（列和表），以及任何相关对象和各种连接方式的描述就称为SCHEMA模式
   • 为表创建图标可以协助我们分别看待表的设计与其中的数据

3. 外键

   • 将一个表中的主键作为另一个表的一列。这种方式称为外键
   • 外键是表中的某一列，它引用到另一个表的主键
   • 外键能用于确认一张表中的行与另一张表中的行相对性
   • 外键的值可以是NULL， 即使主键值不可为NULL。外键为NULL，表示父表中没有相符的主键
   • 外键使用的主键也被称为父键，所在的表称为父表
   • 外键不一定是父表的主键，但必须有唯一性

4. 外键约束

   • 创建在结构内的键被称为约束，表必须遵守的规则

5. 创建带有外键的表
   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
FOREING KEY (contact_id)
REFERENCES my_contacts (contact_id)
);

   • 直接在设计列同一行代码中加入PRJARY KEY 命令是另一种指定主键的方式
   • contact_id INT NOT NULL创建外键和创建索引列一样，把外键设定为INT和NOT NULL
   • CONSTRAINT my_contacts_contact_id，告诉我们键的来源表（my_contacts）,键的名称（contact_id）,说明它是一个外键
   • FOREING KEY(contact_id)代表新表的外键，可以随意命名
   • REFERENCES my_contacts(contact_id)指明外键来源和外键在新表中的名称

6. 表间的关系：数据模式

   • 一对一
   • 一对多
   • 多对多
   • junction table
     两个多对多的表之间需要一个中间桥梁储存两个相关表的主键，即连接表

7. 数据模式：修正篇

   • 第一范式：1NF
     规则一：数据列只包含具有原子性的值
     规则二：没有重复的数据组
   • 部分函数依赖
     非主键的列依赖于组合主键的某个部分
   • 传递依赖：
     任何非键列与另一个非键列有关联
8. 第二范式：2NF
   规则一：先符合1NF
   规则二：没有部分函数依赖性
9. 第三范式：3NF
   规则一：符合2NF
   规则二：没有传递函数依赖性