SQL必知必会（一）

将之前学习的数据库知识在整理下，主要是看的《SQL必知必会》。这本书不愧是经典，入门数据库真的完全足够啦！

image

数据库基础

数据库

保存有组织的数据的容器，通常是一个文件或者一组文件

表

结构化的文件，用来存储某种特定的数据

列和数据类型

列指的是表中的字段。所有的表都是有一个或者多个列组成的。表中的每列存储着某种特定的信息。数据库中的每个列对应着相应的字段类型。

行

表中的数据是按照行存储的。垂直为列，水平为行。行指的是表中的一个记录

主键

表中的每一行都应该有一列或者几列可以唯一标识自己。主键满足的条件是:

任意两行不具有相同的主键值
每行必须有一个主键值
主键列中的值不允许修改或者更新
主键值不能重用

创建表

create table products(
  prod_id char(10) not null,
  vend_id char(20) not null,
  prod_name char(20) not null,
  prod_price decimal(8,2) not null,   -- 指定小数位：2位
  quantity integer not null default 1,   -- 指定默认值
  prod_desc varchar(1000) null
);

插入数据

insert into customers(cust_id,
                      cust_name,
                      cust_city,
                      cust_state,
                      cust_zip,
                      cust_country,
                      cust_contact,
                      cust_email
                     )
values('1000006',
       'Toy Land',
       '123 any street',
       'NY',
       '11111',
       'USA',
       null,
       null);

检索数据select

select prod_id, prod_name   -- 检索部分或者全部字段信息
from products;

select * 
from products; 

select distinct vend_id   -- 检索不同的字段值 distinct
from products;

select top 5 prod_name   -- 限制最多返回5行数据（SQL server）
from products;

select prod_name 
from products
limit 5;    -- mysql中使用limit来限制返回的条数

select prod_name 
from products
limit 5 offset 4   -- 表示从第4行开始（offset），显示5行数据（limit）
limit 4, 5   -- 简化版，逗号之后5表示的是limit限制的条数

排序检索数据order by

主要使用的是order by子句，在select语句中是最后的位置。

按照字段排序

select prod_name
from products 
order by prod_name;   -- 通过单个字段进行排序

select prod_id, prod_price, prod_name
from products
order by prod_price, prod_name  -- 多个字段进行排序
order by 2,3  -- 按照字段的位置进行排序

指定升降序

默认是asc升序，降序是desc。关键字只能够应用到直接作用于其前面的列名上。

select prod_id, prod_name, prod_price
from products
order by prod_price desc;   -- 指定为降序
order by prod_price desc, prod_name;  -- 先对prod_price降序，再对prod_name默认升序

笔记：order by 子句必须是select语句的最后语句

过滤语句where

order by要在where子句之后。

基础过滤

只需要搜索数据指定搜索条件，搜索条件也称之为过滤条件

select prod_name, prod_price
from products
where prod_price <= 50;
where prod_price <> 50;  -- != 和<> 大部分数据库系统可以互换
where prod_price between 50 and 60;
where prod_price is null;

高级过滤

select prod_name, prod_price
from products
where prod_price <= 50 and prod_name='DLL01';  --and

select prod_name, prod_price
from products
where prod_price <= 50 or prod_price >=60;  --or

笔记：and的优先级在or之前；如果同时存在最好加上圆括号来进行明确的分割。

select prod_name, prod_price
from products
where vend_id in ('DLL01', 'BRS01')  -- 等价于如下 or 表示的语句
where vend_id in 'DLL01' or vend_id in 'BRS01')
order by prod_name;  -- order by 一定在 where 之后

笔记：使用in的好处

语法清楚，更直观

求值顺序更容易管理

in子句中可以包含更多其他的select子句，动态地建立where子句

select prod_name, prod_price
from products
where not vend_id = 'DLL01';
where  vend_id <> 'DLL01';   -- <>同样的功能
order by prod_name

通配符使用like

使用通配符能够创建比较特定数据的搜索模式。通配符通常是用来匹配一部分的特殊字符，只能用于文本字段，非文本数据不能使用通配符搜索。

百分号%

匹配的是0个、1个或者多个字符

select prod_id, prod_name
from products
where prod_name like 'Fish%';  -- 实现右、左、左右同时匹配
where prod_name like '%Fish';
where prod_name like '%Fish%';

短横线_

只能匹配单个字符，不能多也不能少

select prod_id, prod_name
from products
where prod_name like '__inch teddy bear'   -- 2个短横线，匹配2个字符

方括号[]

方括号通配符通常是用来指定一个字符集，必须匹配指定位置的一个字符。使用^可以进行取反操作。

-- 找出名字是以J和M开头的人
select cust_contact
from customers
where cust_contact like '[JM]%'   -- 2个通配符的使用，一个是[]，另一个是%
order by cust_contact;

-- 找出名字不是以J和M开头的人：取反操作
select cust_contact
from customers
where cust_contact like '[^JM]%'   -- 使用^进行取反操作
order by cust_contact;

笔记：使用通配符的技巧

不要过度使用

尽量不要把通配符置于开始处，速度慢

注意通配符的位置

计算相关

拼接concatenate

select concat(vend_name, '(',  vend_country, ')')
from vendors
order by vend_name;

笔记：SQL中使用RTRIM()函数来去掉右边的空格；LTRIM去掉左边；TRIM去掉左右两边的空格

使用别名as

select concat(vend_name, '(',  vend_country, ')') 
            as vend_title   -- 别名使用 
from vendors
order by vend_name;

笔记：别名最常用的功能是将多个单词的列名重命名为一个单词的名字

算术运算

对检索出的数据进行算术运算

select prod_price, item_price, quantity,
                quantity * item_price as expanded_price   -- 计算之后再起别名
from orderitems
where order_num = 2008;

使用函数处理数据

去掉空格

RTRIM：去掉右边
LTRIM：去掉左边
TRIM：去掉两边

文本处理

upper：将文本转化为大写
lower：将文本转化为小写
length：返回的是字符串长度
soundex：任何一个文本返回其soundex值（描述发音）

数值处理

ABS：绝对值

COS/SIN：余弦和正弦值

EXP：指数值

TAN：角度正切值

汇总和聚合

常见的聚合函数

函数	功能
avg()	平均值；<br />忽略列值为NULL的行
count()	行数；<br />count(*)：统计所有的行数，包含列是NULL的行<br />count(column)：特定的列进行统计，忽略NULL值
max()	最大值<br />忽略列值为NULL的行
min()	最小值<br />忽略列值为NULL的行
sum()	某列值之和<br />忽略列值为NULL的行

select avg(prod_price) as avg_price
from products
where vend_id = 'DLL01';

select max(prod_price) as max_price   -- max列出最大值
from products

select sum(item_price*quantity) as total_price  -- sum用于合计计算值
from orer_items
where order_num=20005;

上面5个函数的使用方法：

对所有行执行计算，指定all或者不指定参数，因为是默认ALL
只包含不同的列值，指定DISTINCT参数
DISTINCT参数不能用于count(*)，只能用于count(column)指定特定字段

select avg(distinct prod_price) as avg_price
from products
where vend_id = 'DLL01';

组合聚集函数

多个聚集函数同时使用

select count(*) as num_items,
                min(prod_price) as price_min,
                max(prod_price) as price_max,
                avg(prod_price) as price_avg
from products;