SQL快速入门 ( MySQL快速入门, MySQL参考, MySQL快速回顾 )

sql

先说点废话，很久没发文了，整理了下自己当时入门 sql 的笔记，无论用于入门，回顾，参考查询，应该都是有一定价值的，可以按照目录各取所需。sql数据库有很多，mysql是一种，本文基本都是sql通用标准，有些标准很不统一的地方就用mysql的写法了。希望本文帮你快速了解sql的基本操作和概念。
文章格式上有些问题，可以点击这里获得更加的阅读体验

目录

检索

• 检索某表中单个列：
  select 列名
  from 表名;
• 检索某表中多个列：
  select 列名,列名,列名
  from 表名;
  
• 检索某表中所有列：（尽量不用）
  select *
  from 表名;
  
• 只检索某表中某列里不重复的项：
  select distinct 列名 (如果有两列或以上，需要这些列组合起来是不重复的)
  from 表名;
  
• 检索指定行数：
  select 列名
  from 表名
  limit 5 offset n; （mysql中，选第n行后的五行。 offset n 可不填写默认为0，其它 sql 数据库中有不同写法）

过滤检索结果

• 寻找指定行：（举例）
  

    select prod_name, prod_price  
    from products  
    where prod_price = 3.49;（和字符串比较加单引号，数值不用）

查找列名为prod_name和列名为prod_price的两列，检索其中prod_price = 3.49; 的所有行。  
= 可以替换为其它操作符，如下表

| 操作符 | 描述 |
| --- | --- |
| = | 等于 |
| <> | 不等于 |
| > | 大于 |
| < | 小于 |
| >= | 大于等于 |
| <= | 小于等于 |
| between | 在某个范围内 |
| like | 搜索某种模式 |

• 组合where子句：

    select prod_id, prod_price, prod_name
    from products
    where vend_id = 'dll01' and prod_price <= 4;

and 连接同时需要满足的两个条件，or即满足一个条件即可，not 找到与后边条件不匹配的行。  
且not，and和or可以组合使用，用小括号声明逻辑循序。  
`where vend_id in ( 'dll01', 'brs01' ) `  
in 起到作用类似于or，速度更快，逻辑更清晰。

• 通配符搜索：

    select prod_id, prod_name
    from products
    where prod_name like '%bean bag%';

％表示任意字符出现任意次数。也可以出现在中间位置。  
_ 表示一个字符。  
[charlist] 表示包含在里面的任意字符，[^charlist]不包含在里面的任意字符。
少使用通配符，搜索速度较慢。  

数据汇总处理

• 算术计算：

    select prod_id,
            quantity,
            item_price,
            quantity * item_price as expanded_price
    from orderitems
    where order_num = 20008;

`expanded_price`成为计算出来的新列。

• 字符串拼接：
  不同数据库有差异，mysql中：

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

concat_ws( ':' , vend_name , vend_country) 形式第一个参数为分隔符。  
其他数据库用＋或者||拼接字符串。

• 日期时间处理不同数据库差异较大。
• 内置方法：
  • 求平均值：
    select avg(prod_price) as avg_price
    from products;
    表products中prod_price的平均值。返回给 avg_price。
    可以配合where语句计算指定行的平均值。
  • 求最大值：max(prod_price)
  • 求最小值：min(prod_price)
  • 求和：sum(prod_price)
  • 近似的小数点后几位：round(column_name,decimals)
  • 当前日期： now()
  • 求行数：
    select count(＊) as num_cust
    from customers;
    求表customers有几行。返回给num_cust。
    ＊可以换成指定列如：cust_email。计算所得行数不包括该列值为null的行。
    distinct 列名，求不重复的列。
• 组合：
  

    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;

分组

• 创建分组：
  

    select vend_id
    from products
    group by vend_id;

根据 vend_id列中内容对 vend_id分组，  
第一行换成 `select vend_id, count(＊) as num_prods` 即对每一个组计算行数。  
注意：多行null会分为一组，group by子句必须出现在where子句之后，order by子句之前。  
可以对一个以上的列进行 group by

• 过滤分组：
  having：类似于where。唯一的差别是，where过滤行，而having过滤分组。
  

    select vend_id, count(＊) as num_prods
    from products
    where prod_price >= 4
    group by vend_id
    having num_prods >= 2;

过滤出有（两个价格大与4的产品）的供应商

给检索结果排序

select company, ordernumber 
from orders 
order by company desc, ordernumber asc  

可以 order by 列名1,列名2; 先按列名1内容排序，排序结果相同的按列名2内容排序。
列名后接 desc 按该列内容倒序排列，asc 正序(默认)。
order by 命令放在查询、分组等语句的最后。

表操作

• 创建表:

    create  table  newproducts
    (
        prod_id         char(10)           not null,
        vend_id         char(10)           not null,
        prod_name       char(254)          not null,
        prod_price      decimal(8,2)       not null,
        prod_desc       varchar(1000)      null
    );

not null 非空约束，不允许列中有null值下面介绍其他约束。  
列的设置可以加上默认值，如not null后边接 default  current_date() ，默认值为当前日期。（每个数据库获取当前日期语句不同。）
后面接 primary key 即设置改列为主键。
后面接  auto_increment 即设置为自增，只有int型可以设置。

• 约束：
  每个列可以有一种或几种约束。
  • not null 非空约束.
  • unique 唯一约束，可唯一标识数据库表中的每条记录。
  • primary key 主键约束，唯一标识数据库表中的每条记录，唯一且非空。
  • foreign key 外键约束，一个表中的 foreign key 指向另一个表中的 primary key。
  • check 检查约束，用于限制列中的值的范围。
  • default 默认约束，用于向列中插入默认值
    每个表可以有多个 unique 约束，但是每个表只能有一个 primary key 约束。
    每种约束可以创建表时设置好，也可以后期增删.
• 索引：
  在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据。

    create index 索引名
    on person (列名[，列名])  

• 复制表或表中部分列:

    create  table custcopy as
    select * from customers;

创建customers表的复制，custcopy。

• 修改表:

    alter table vendors
    add vend_phone char(20);
    alter table vendors
    drop column vend_phone;

各数据库有不兼容现象，复杂表操作列可能要新建表删除旧表。  
alter 还可以用来添加删除约束，删除索引等。

• 删除表：
  drop table custcopy;
• 重命名表：
  rename table oldtable to newtable;

插入数据

• 插入整行或部分行：

    insert into customers(cust_id,
                            cust_name,
                            cust_address,
                            cust_city,
                            cust_state,
                            cust_zip,
                            cust_country,
                            cust_contact,
                            cust_email)
    values('1000000007',
            'toy land',
            '123 any street',
            'new york',
            'ny',
            '11111',
            'usa',
            null,
            null);

插入整行时，可省略 customers 括号内的内容，即按照列的顺序，分别插入数据（不推荐）。省略 customers 括号内的内容时，无内容的列必须用null占位。  
插入部分行时，把要插入的列填入 customers 括号内，与values内容一一对应，没有提到的列默认null或其他默认值。

• 插入查询到的值:

    insert into customers(cust_id,
                            cust_contact,
                            cust_email,
                            cust_name,
                            cust_address,
                            cust_city,
                            cust_state,
                            cust_zip,
                            cust_country)
    select cust_id,
            cust_contact,
            cust_email,
            cust_name,
            cust_address,
            cust_city,
            cust_city,
            cust_state,
            cust_zip,
            cust_country
    from custnew;

把从custnew表中查到的内容插入 customers表中。一次插入多行的方式。

更新和删除数据:

• 更新数据：

    update customers
    set cust_email = '[e-mail]'
    where cust_id = '1000000005';

步骤为，要更新的表，要更新的列，要更新的行。一个set可以跟多个列用逗号隔开。  
删除某个值，即设置他为null。

• 删除数据：

    delete from customers
    where cust_id = '1000000008';

删除表中指定整行，删除部分列用update  
在update或delete语句使用where子句前，应该先用select进行测试，保证它过滤的是正确的记录，以防编写的where子句不正确。如果不写where会更新或删除所有行内容。

子查询-迭代查询

• 一种形式：

    select cust_name, cust_contact
    from customers
    where cust_id in (select cust_id
        from orders
        where order_num in (select order_num
            from orderitems
            where prod_id = 'rgan01'));

先从第二个括号选择符合条件的order_num，成为第二个括号内容，再向上找到第一个括号，查到符合条件的cust_id返回给第一个括号，最后根据第一个括号内容执行主查询语句。性能问题不要嵌套太多层。  
也就是对customers表的查询要用到orders表查询后返回的内容，对orders表的查询要用到orderitems表查询后返回的内容。

• 另一种形式：

    select cust_name,
        cust_state,
        (select count(＊)
            from orders
            where orders.cust_id = customers.cust_id) as orders
    from customers

根据customers 表中的cust_id，去orders表中取得计算后的数据。  

• 同一个表迭代查询：

    select cust_id, cust_name, cust_contact
    from customers
    where cust_name = (select cust_name
                    from customers
                    where cust_contact = 'jim jones');

联结-关联多个表

• 两个表：
  • 内联结

        select vend_name, prod_name, prod_price
        from vendors, products
        where vendors.vend_id = products.vend_id;

    根据两个表共同的列vend_id把vendors, products关联起来。
    与

        select vend_name, prod_name, prod_price
        from vendors inner join products
        on vendors.vend_id = products.vend_id;

    结果相同。都是内联结，前一种是后一种的简写。  
    inner 可省略。

  • 外联结：

        select customers.cust_id, orders.order_num
        from customers left outer join orders
        on orders.cust_id = customers.cust_id;

    left outer join 把customers表中没有被匹配到的 cust_id 也联结进去（会显示在结果里）。  
    right outer join 是把orders表中没有被匹配到的 cust_id 也联结进去（会显示在结果里）。  
    full outer join 会把两张表中没有匹配到的列也显示出来(mysql 不支持，可通过 union 实现)
    outer 可省略。

• 多个表：

    select cust_name, cust_contact
    from customers, orders, orderitems
    where customers.cust_id = orders.cust_id
    and orderitems.order_num = orders.order_num
    and prod_id = 'rgan01';

作用同子查询中a。同样不要关联太多，有性能问题。其中表名可以使用别名，如：  

    select cust_name, cust_contact
    from customers as c, orders as o, orderitems as oi
    where c.cust_id = o.cust_id
    and oi.order_num = o.order_num
    and prod_id = 'rgan01';

组合查询

select cust_name, cust_contact, cust_email 
from customers 
where cust_state in ('il','in','mi') 
union all
select cust_name, cust_contact, cust_email 
from customers 
where cust_name = 'fun4all';

union all 链接两句查询语句，统一返回结果，包含重复结果。
去掉all以后，去掉重复结果。
此处（从同一个表中查询）可以用where ， or代替。
常用作从不同表中查询时，只要列数相同就可以拼接到一起，列名按照第一句中查询的列名。

视图

对已存在的表，进行筛选，数据处理，联结等操作后返回的数据，创建的虚拟表。视图是为了重用和简化常用的查询。对视图的查询同表。
视图总是显示最近的数据。每当用户查询视图时，数据库引擎通过使用 sql 语句来重建数据。

• 创建视图:

    create  view productcustomers as                
    select cust_name, cust_contact, prod_id
    from customers, orders, orderitems
    where customers.cust_id = orders.cust_id
    and orderitems.order_num = orders.order_num;

对orderitems， orders和customers三个表进行联结，联结后结果形成 productcustomers 视图，可以把它当一张表来查询。

• 删除视图：
  drop view productcustomers;

  其它

• 存储过程：为以后的使用保存一条或多条sql语句，用于简化操作。每个数据库不同，见数据库具体介绍。
• 事务处理：事务处理模块中的语句，或者全部执行，或者全部不执行。可以设立保留点，执行失败时回到保留点。
• 创建数据库: create database database_name

• 删除数据库：drop database 数据库名称