01 sql的学习

    这两天没事，看了下sql基本操作这本书，特此来总结下我的学习笔记:

    sql主要帮助你操作数据库中的数据的，无论我们是开发项目还是单纯查数据都和数据库的操作息息相关，包括增删改查等。下面以oracle的操作(oracle关键字是不区分大小写的)基本来讨论。

      首先我们要清楚的是oracle的执行顺序是 from——where——group by——having——select——order by

      当我们要创建一个product表时:  create table tablename (product_id char(4) not null, product_name varchar(100) not null, product_type varchar(32) not null,sale_price integer, regist_date,primary key (product_id)) 

      但是不小心创建错了我们可以使用alter来进行增加和删除列，以及重命名表名。例如:

      alter table tablename rename to product; 

      alter table product drop(sale_price,purchase_prise);

       alter table product add(sale_price integer ,purchase_prise integer);

这里关于类型有个需要注意的点char(25) 表示定长为25，如果其中只填入了4个字符那么剩下用空格补齐，然后变长varchar(25)则不会补齐。

      列已经搞定，接下来是插入数据，关于数据的插入insert into 例如:

      insert into product values(﹉﹉)回家补齐

      insert into product values(<一行所有值>);

     如果插入的数据不小心插入错误，这个时候我们可以通过update 来进行更改，例如:

      update product set product_name ='T恤',sale_price=20 where product_id='0009' ;

     删除整张表的内容: delete from 表名;=truncate table 表名。

     删除满足条件的表的行: delete from 表名 where 条件

     删除表 drop table 表名

     恢复不小心删除的表 flashback table 表名 to before drop;

     and运算符的优先级高于or，在使用count()计算行数的时候count(*)是计算包括null的所有的行数，而count(<列名>)是计算null以外的数据行数

    关于查询时间的:

     select to_char(sysdate,'yyyy–mm–dd hh24:mi:ss) from dual; 这是查询sql执行时的系统时间，其中dual 是虚拟表(临时表)=select sysdate from dual 

    current_date 返回sql执行日期,select current_timestamp from dual ——获取sql执行时间

extract (日期元素 from 日期) 截取日期数据的一小部分。例如:  

select current_timestamp,extract(year from current_timestamp) as year,

extract (month from current_timestamp) as month,

extract(day from current_timestamp) as day,

extract(hour from current_timestamp) as hour,

extract(minute from current_timestamp) as minute,

extract (second from current_timestamp) as second

from dual;

    select可以写常数，例如:select  '商品' as 类型,38 as 数字 ,product_id from product;

    distinct 删除重复值，只能用于第一个列名前，且多列之前使用distinct 的话，例如此处，只有当product_type,regist_date均相同的数据才可以合并，count(distinct type) 计算type的种类数目  例子:

select distinct product_type,regist_date from product;

    where 和having的区别: where 子句=指定行所对应的条件，having 子句=指定组所对应的条件; 本质类似，但是having 多用于聚集函数后面来进行条件选择，而where不能。having 是在分组后再执行条件 例如:

select product_type,avg(sale_price) from product group by product_type having avg(sale_price)>=2500;

   事物处理(由于我们完成一个任务的时候会有一系列的操作，但是如果漏操作都会导致这个任务不完整，所以相当于一个事物就是一个任务，而一个任务是由多个操作构成。)但是在oracle中没有特定的开始语句，最后都是commit;提交处理。其他的数据库有start transation/begin transaction

      rollback 取消处理，即取消事物包含的全部更新处理的结束命令。相当于文件处理中放弃保存。一旦回滚，数据库就会恢复到事务开始前的状态。一个事物的结束不是commit就是rollback为标志。例如:

update product set sale_price =sale_price -1000 where product_name='T恤衫';

update product set sale_price=sale_price+1000 where product_name='T恤';

delete from product ;

rollback; 此处事务结束，数据距上次事务不会有任何更改

     

      视图和表是相同的，两者的区别在于表中保存的是实际的数据，而视图中保存的是select语句，其本身不存储数据。

     尽量避免在视图的基础上创建视图。这是由于多重视图会降低sql的性能。定义视图的时候不能使用order by子句，尽量避免多层嵌套的子查询，多层嵌套，里面可以看到外面但是外面看不到里面。

创建视图 : create view productSum (product_type,cnt_product) as select product_type,count(*) from product from product_type;

删除视图: drop view 视图名称

select product_id ,product_name,sale_price from product group by product_id,product_name,slae_price having sale_price>(select avg(sale_price) from product)

 

    关联查询

① 

select product_type ,product_name,sale_price from product p1 where sale_price >(select avg(sale_price) from product p2 where p1.product_type=p2.product_type group by product_type)

②

select t.product_id ,t.product_name,t.product_type,t.sale_price,y.sale_avg from product t left join (select product_type,avg(sale_price) as sale_avg from product y group by product_type) y on y.product_type=t.product_type order by t.product_id;

③

select product_type,product_name,sale_price,(select avg(sale_price) from product p2 where p1.product_type=p2.product_type group by product_type)

from product p1;   其中②等于③

 

   字符串|| 拼接:

select product_type,product_name,product_type||product_name as str_concat from product;

   字符串replace(对象字符串，替换前字符串，替换后字符串)   substr(对象字符串，截取起始位置，截取字符数)

     类型转换 cast(转换前的值 as 转换后的类型) select cast ('001' as integer ) from dual;

 

     case 搜索case 表达式为 case when <求值表达式> then <表达式> ...else <表达式> end 例如:

    select product_name,case product_type when '衣服' then 'A:' || product_type when '办公用品' then 'B:' || product_type else null end as abc from product;

    或者 select case when product_type='衣服' then 'A:' || product_type when product_type ='办公用品' then 'B:' || product_type else null end as abc from product;

      集合运算(以行做加减) union 做联合运算，一般是去除了重复项，但是union all 则是保留重复项，也就是两张表所有的数据项内容。intersect 取两表的交集，minus 则是从前表中减去含有后表的内容:

  select product_id ,product_name from product minus select product_id,product_name from product2

     联结(增加新的列) 包括了内联结 inner join和外联结 outer join。其中inner join 只能去除同时存在两张表中的数据，对于外连接outer join来说 只要数据存在某一张表中，就能够取出来，而外连接分为left join 和 right join 区别在于左边还是右边作为主表。

    cross join 用的比较少，其是对两张表全部记录进行交叉组合，不需要加on