数据分析充电1-sql必知必会第四版学习笔记总结2017/12/19--2017/12/19

SQL必知必会学习笔记


===============================基础知识===============================================


----------------------一般情况下情况，一条sql语句以；结尾------------------------------


-----------------合并两列成一列---------------
方法：使用+
举例： select bend_name + bend_count as vend_title
   from Vendors
   order by vend_name;
   
   
------------------like和通配符使用----------------------------
用法：where 列名 like '%123'
举例：select prod_id,prod_name
      from Products
  where prod_name like 'Fish%';
%表示任何字符出现任意次数，但是无法匹配null




------------------常用文本处理函数---------------------------------------
LEFT() 返回字符串左边的字符
length(),datalength() len(),返回字符串的长度
lower() ,字符串转小写
upper(),字符串转大写
ltrim(), 去掉左边空格
rtrim(),去掉右边空格
right(),返回字符串右边字符
substring(,),截取字符串
to_date ,字符串转日期


-------------------常用数值处理函数------------------
abs()，绝对值
cos()，余弦
exp()，指数值
pi()，圆周率
sin()，正弦
sqrt()，平方根
tan(),正切
   
-------------------常见聚合函数----------------
AVG
max
min
count
sum


------------------group by --------------------
作用，对聚合函数进行限定
规则：如果在select 中使用表达式，则必须在group by 字句中指定相同的表达式，不能使用别名
group by 在按组计算聚类时候使用，不使用会报错


------------------having------------------------ 
作用：过滤分组


------------------where-----------------------------
作用：过滤行级


----------------- select子句顺序----------------------
select 
from 
where 
group by 
having 
order by


--------------------as--------------------------------
as 可以给字段，给表，取别名




===============================多表联结查询=============================================


------------------多表查询1：使用子查询where in ，子查询效率低，不建议使用------------
作为子查询的select语句只能查询单个列，企图检索多个列将返回错误
方法：在where in (子查询语句)
举例：select * 
      from customers 
      where cust_id in (select cust_id 
                     from Orders 
where order_num in (select order_num 
                      from orderItems
  where prod_id ='rgan01'));
  
---------------多表查询2：内连接的两种方式，内连接是一种等值连接----------------------
---------------------方式1：from 多表，where 主键限定----------------------
举例：select vend_name,prod_name ,prod_price 
      from Vendors,Products
  where Vendors.vend_id = Product.vend_id;
举例：select * 
       from customers, Orders,orderItems
   where customers.cust_id =Orders.cust_id
   and Order.order_num = orderItems.order_num
   and orderItems.prod_id = 'rgan01';
  
----------------------方式2：inner join内连接-------------------------------
方法： from 表1 inner join 表2 on 表1.列名 = 表2.列名
举例：select vend_name,prod_name,prod_price
      from Vendors inner join Product
  on Vendors.vend_id = Product.vend_id;
  
-----------------------自然连接，内连接一种：排除查询出的重复列--------------------------




------------------------外连接：左关联 left outer join-------------------------------
以左表为基准，选出左表所有行
用法 from 表1 left outer join 表2
举例：
select Customers.cust_id, count(Orders.order_num) as num_ord
from Customers left outer join Orders 
  on Customers.cust_id= Orders.cust_id
group by Customers.cust_id;




------------------------内连接和外连接区别----------------------------------------
外连结包含没有关联行的行，显示为null




-------------------------使用带聚集函数的外关联，记得加group by-------------------
select customers.cust_id,count(Orders.order_num) as num_ord
from Customers left outer join orderd
on Customers.cust_id =Orders.cust_id
group by Customers.cust_id;


-------------------------一般可以使用内连接的，使用外连接也会有效----------------------




================组合查询,也叫(并，union),也叫(复合查询，compound query)==========


----------------union，直接连接组合select语句即可--------------------------------
用法：select语句1 union select语句2
规则：每个查询语句包含相同列，表达式或者聚集函数，对应列数据类型要兼容
扩展：union 会默认去重，使用union all可保留重复项；order by 只能在最后出现
理解：union 完成的基本都可以用多个where语句实现




=================插入语句==============================


----------------插入一行数据---------------------------
用法1：insert into 表1(列1，列2) 
      values (值1，值2)；


----------------查询结果插入到现有表中------------------
用法2：insert into 表1(列1，列2)
       select 列1，列2
   from 表2


-----------------查询结果插入到新表中-------------------
用法3：create table 新表名 as
       select 列1，列2 from 表1
   
=================更新数据update===============================
用法：
update 表1
set 列1 =值1，
    列2 =值1
----------------可以指定where 条件，不指定则更新所有行




=====================delete删除表中的数据，不删除表====================
用法：
delete
from 表1
-------------可以指定where条件，不指定则更新所有行
  
注：truncate table ，也是只删除数据，但速度更快，不记录表变动






=====================创建表================================================
用法：
create table 表名
(
     列名1     字段类型   是否可为null   default 值,
列名1     字段类型   是否可为null,
列名1     字段类型   是否可为null
     ......
);


举例：
create table OrderItems
(
    order_num   integer           not null,    primary key
order_item  integer           not null,
prod_id     char(10)          not null,
    quantity    integer           not null      default 1,
item_price   decimal(18,2)
);


规则：某些需要指定创建表的路径；默认可为空，列名和类型是必须的，primary key,null和default列可选填写


=======================修改表结构================================


-----------------增加列---------------------------
用法：alter table 已存在表名
add 新列名  字段类型
举例： 
alter table Vendors
add vend_phone char(20);




-----------------删除列----------------------------
用法 alter table 已存在表名
drop column 已存在列名
举例：
alter table Vendors
drop column vend_phone;




========================删除表和表结构============================
drop table 表名




=======================创建视图(虚拟表)===================================
create view 视图名称 as
select查询语句


=======================存储过程：执行多条sql语句，===================================
作用：类似编程中函数，方法等概念
create procedure MailingListCount(
  ListCount OUT integer           -----out，存储过程将返回一个值
 )
 is
 v_rows integer                 ---声明变量v_rows
 begin                            ---begin end 中写存储过程要实现的功能
    select count(*) into v_rows
from Customers
where not cust_email is null; --功能语句，把取得的结果赋值给声明的变量v_rows

ListCount :=v_rows;               --把v_rows赋值给ListCount
 
 end;
 
 执行存储过程，execute 存储过程名字(参数)
 
 
 
 ====================事务处理：保证不出错的执行多条sql语句----------------
 举例：
 begin transaction
 delete OrdeItems where order_num =12345
 delete Orders where order_num =12345
 commit transaction
 
 
=====================游标：用于浏览查询结果集中上下行=================================
declare cursor 游标名  -------------声明游标
is
select 语句


open cursor 游标名     ---------------打开游标


fetch 游标名 into 列    ---------------使用游标
 
close 游标名         -------------------关闭游标


======================触发器：特殊的存储过程===================================
在特定的数据库活动时候执行，与单个表相关联
存储过程，只是存储多个sql语句，不执行，执行需要写execute 




=====================速查======================================================
ALTER TABLE
ALTER TABLE用来更新现存表的模式。可以用CREATE TABLE来创建一个新表。详情可参见第17课。
COMMIT
COMMIT用来将事务写入数据库。详情可参见第20课。
CREATE INDEX
CREATE INDEX用来为一列或多列创建索引。详情可参见第22课。
CREATE TABLE
CREATE TABLE用来创建新的数据库表。可以用ALTER TABLE来更新一个现存表的模式。详情可参见第17课。
CREATE VIEW
CREATE VIEW用来创建一个或多个表的视图。详情可参见第18课。
DELETE
DELETE用来从表中删除一行或多行。详情可参见第16课。
DROP
DROP用来永久性地删除数据库对象（表、视图、索引等）。详情可参见第17课和第18课。
INSERT
INSERT用来对表添加一个新行。详情可参见第15课。
INSERT SELECT
INSERT SELECT用来将SELECT的结果插入到表中。详情可参见第15课。
ROLLBACK
ROLLBACK用来撤销事务块。详情可参见第20课。
SELECT
SELECT用来从一个或多个表（或视图）中检索数据。详情可参见第2课、第3课和第4课（第2课到第14课从不同方面涉及了SELECT）。
UPDATE
UPDATE用来对表中的一行或多行进行更新。详情可参见第16课。