Sql整理记录一！

--本文主要对以下内容进行简单的整理(Oracle数据库中)  

--1,学习基本的Sql语句，创建表，对表进行删除，修改，相关约束等学习

--2，对连接表查询的学习，group by,having

--3,相关函数的学习

--4，一些有用的功能，和实际开发中常用到的一些例子！

 

 

--1,基本创建表、删除表、增加列，修改列

--关键字 create==创建 dorp==删除 alter==修改(对他的理解一般思路是‘修改某某干什么’)

--创建表，不同数据库

createtableT_ChA_Student

(

 IDnumber(10)  notnull,

 SNAME varchar2(50char)notnull,

 Amount number(12,2)notnull,

 STime date     notnull

)

--删除表

droptableT_ChA_Student

--增加列（修改table增加一个班级列）

altertableT_ChA_Studentadd(ClssIDnumber(10));

--删除列 （修改table删除一个列）

altertableT_ChA_Studentdropcolumn ClssID;

--增加主键

altertableT_ChA_Studentaddprimarykey(ID);

--删除主键

altertableT_ChA_Studentdropprimarykey(ID);

--创建索引

create index indexSnameonT_ChA_Student(SName);

--删除索引

dropindex indexSname;

--备注:索引不能够更改

--创建视图

createview viewStudentasselect *from T_ChA_Student;

--删除视图

dropview viewStudent;

 

--约束虽然现在在数据库中使用越来越少(把对数据的约束，转移到程序中去)，但还是做一个了解。

--约束关键词(constraint)

--主键约束 (主键约束跟唯一性约束区别在于主键不能为空，唯一性约束可以为空。共同点，都是不能重复)

altertableT_ChA_Studentaddconstraint PK primarykey(ID);

--唯一性约束

altertableT_ChA_Studentaddconstraint PK_name unique(STime,Amount);

--外键约束（默认模式，外键约束有几种--默认、检查、外键）

altertableT_ChA_Studentaddconstraint Class_ID foreignkey(ClassID)referencesClass(ID)

--默认约束

altertableT_ChA_Studentaddconstraint DF_Amount check(Amount>=10);

--检查约束

altertableT_ChA_Studentaddconstraint DF_SName default'zhenglianghui'from SName;

--删除约束

altertableT_ChA_Studentdropconstraint DF_Amount;

 -创建表、视图、索引都使用到create，删除都用到drop，其他的都通过'修改表某某干什么’来解决。

 

--2，对连接表查询的学习

--表连接查询

--左连接 left join 右连接==right join 完全连接(inner join )

[sql]  view plain  copy

1. 1.select T_ChA_Student.*,T_class.* from T_ChA_Student left join T_class on T_ChA_Student.ClssID=T_class.ID and T_ChA_Student.clssID=2  
2.   
3. 2.select T_ChA_Student.*,T_class.* from T_ChA_Student left join T_class on T_ChA_Student.ClssID=T_class.ID where T_ChA_Student.clssID=2  

结果分别为

1.

2.

注意上面2个sql语句，使用相同的条件，但前面使用一个加and，一个加where。

--先理解left join,right join,inner join,full join区别。

--理解他们，只要理解以那张表的数据记录为主的概念就行。谁的表数据记录为主，谁的表数据记录就都得显示出来。

--比如：

--left，就以左边数据记录为主，左边表数据必须都显示出来.

--right，就以右边数据记录为主，右边表记录都必须显示出来。

--inner，左右2张表记录数据为主，就是都得显示出来，如果任意一边都没有数据，那么就去掉该数据

--full, 左右2张表记录数据为主，就是都得显示出来，如果任意一边都没有数据，那么就显示为null

--其次

--我们知道数据库在通过2张表或者多张表查询时候，会生成一张临时表，并把临时表返回给用户。

而在我们使用左连接、右连接时候(完全连接不存在这个问题)，里面On后面直接加and条件和Where条件区别在于。

情况1，直接在on后面加and条件

[sql]  view plain  copy

1. on T_ChA_Student.ClssID=T_class.ID and T_ChA_Student.clssID=2  

on条件(T_ChA_Student.ClssID=T_class.ID ) 是在生成临时表使用的，

我对如何生成这个临时表的理解有

1，使用迪科尔乘积后，列举所有情况后，在根据每条记录里面的条件classID=ID，为true保留，为false删除。

2，通过游标首先获得T_ChA_Student第一条记录（A）里面的classID，在去遍历表T_class里面的所有ID，如果存在条件classID=ID为ture的记录B或者B和C，那么就把记录A和记录B或者A、B，A、C拼接一下到一个临时表中，之后，在循环T_ChA_Student第二条记录，在查找T_class，查找条件classID=ID为ture，直到循环完毕。

这2个方法都可以生成满足条件的临时表给用户，我也没搞清楚数据库到底使用哪种方式，或者根本就不是我所想到的这2种方式，如果有知道的同学，麻烦告知一声。

         在生成临时表后，其后面直接加and条件，会进一步对数据进行帅选，and后面对那张表的条件，那么就会

        对临时表中那张表进行帅选（and T_ChA_Student.clssID=2），条件T_ChA_Student.clssID=2为ture的就保留该条记录数据，为false的就把该条表数据变为null(注意不是删除啊)。

情况2，直接在on后面加where条件

               在临时生成后，其后面直接加where条件，会进一步对数据进行帅选，where后面的条件（where T_ChA_Student.clssID=2 ）也是对临时表中那张表表数据记录进行帅选，当条件T_ChA_Student.clssID=2为ture，就保留该条记录，当为false，就删除该条记录（注意是删除整条记录）

之后，情况1和情况2，把过滤掉的数据返回给用户。

小结,on后面直接加and和where的区别，满足条件都保留，不满足条件，情况1变null，情况2删除。

--这个在实际开发中用到的蛮多，很容易弄混淆！

--group by,having

--共同准则对select后面的字段，必须要么包含在group by中，要么包含在having后的聚合函数里。

--group by

--使用到group by，则一般使用到聚合函数(最常用的avg==求平均,count==求数量,max==求最大值,min==求最小值,sum==求和)

--group by 有一个原则,就是 select后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面

--例如:查询班级平均分数

select clssID,avg(Amount)fromT_ChA_Studentgroupby clssID

--having

--where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。

--having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having条件显示特定的组，也可以使用多个分组标准进行分组。

--having 子句被限制子已经在SELECT语句中定义的列和聚合表达式上。通常，你需要通过在HAVING子句中重复聚合函数表达式来引用聚合值，就如你在SELECT语句中做的那样

--例如：查询平均分数在62的班级

select clssID,avg(Amount)fromT_ChA_Studentgroupby clssID havingavg(Amount)>62;

 

 

--3,相关函数的学习（不同的数据库中有些函数不一样）

--sql中函数种类有点多，这里只是列举常用到的一些函数,聚合函数，转换函数,日期和时间函数，数学函数。

--聚合函数

--最常用的avg==求平均,count==求数量,max==求最大值,min==求最小值,sum==求和这不进行列举

--转换函数(不同的数据库中，有不同的转换函数)

--cast和convert

CAST('123'ASint);convert(int,'123');

--日期和时间函数

--DATEADD(year,10,'2012-12-12')==日期增。DATEDIFF(Month,''2012-12-12,''2013-12-12)==日期直接的差,DAy(),YEAR(),MONTH()

selectadd_months(stime,1)fromT_ChA_Student

--RAND()  返回以随机数算法算出的一个小数，可以接收一个可选的种子值

--ROUND()  对一个小数进行四舍五入运算，使其具备特定的精度

 

decode--decode(性别,男,1,0)其实就是做一个判断,当性别为男的时候,把男替换成1,否则就把性别字段中的内容替换成0

 

--4，一些有用的功能，和实际开发中常用到的一些例子！

--2表之间数据结构操作

--Oracle 中复制表结构和数据，只要表结构的，在后面添加一个条件 where 1=2

createtableT_ChA_Student_threeasselect * fromT_ChA_Studentwhere1=2

--Oracle 2表结构一样，复制数据（同理要什么数据，加条件,比如where clssID=1）

insertintoT_ChA_Student_threeselect * fromT_ChA_Student

--Oracle 2表结构不一样,（同理要什么数据，加条件,比如where clssID=1）

insertintoT_ChA_Student_three(ID,SName)selectID,SNamefromT_ChA_Student

--纵转横以前总是死记硬背，觉得蛮复杂，在写这个文档的时候，突然发现，就是那么回事sum(加条件处理)

--显示1,2月份的成绩

selectName,months,

sum(case coursewhen'seo培训'then score else0end)as seo培训,

sum(case coursewhen'网站框架培训'then score else0end) 网站框架培训,

sum(case coursewhen'演讲培训'then score else0end) 演讲培训

fromEmployegroupbyname,months

--求一二月份培训科目平均值,并且三门培训科目都必须及格(大于60分)

declare @ccnumber(10)

set @cc=selectcount(*)from(selectdistinct months fromemploye)

print @cc --@cc=2

selectName,

sum(case coursewhen'seo培训'then score else0end)/@ccas seo培训,

sum(case coursewhen'网站框架培训'then score else0end)/@cc 网站框架培训,

sum(case coursewhen'演讲培训'then score else0end)/@cc 演讲培训

fromEmployegroupbyname

having 

sum(case coursewhen'seo培训'then score else0end)/2>60

and sum(case coursewhen'网站框架培训'then score else0end)/2>60

and sum(case coursewhen'演讲培训'then score else0end)/2>60

--可以直接写2

--1,2月份单科成绩排序

selectnameas"姓名",sum(case course when'seo培训'then score else0end) "seo培训"

fromemployegroupbynameorderby"seo培训"desc

 

--1月份单科成绩排序

selectnameas"姓名",scoreas"分数",rownum"排名"fromemployewhere course='seo培训'and months=1

 

 --插入图片

 

--参考引用：

--:http://www.cnblogs.com/gaiyang/archive/2011/04/01/2002452.html

--http://www.cnblogs.com/moss_tan_jun/archive/2010/08/23/1806861.html

--http://www.cnblogs.com/yubinfeng/archive/2010/11/02/1867386.html

--提供表结构和数据

createtableEmploye

(

  Namevarchar2(10char)notnull,

  Course varchar2(10char)notnull,

  Score number(5)notnull,

  Months number(5)

)

 

insertintoEmploye(Name,course,score,Months)values('郑良辉','seo培训',60,2);

insertintoEmploye(Name,course,score,Months)values('郑良辉','网站框架培训',75,2);

insertintoEmploye(Name,course,score,Months)values('郑良辉','演讲培训',80,2);

 

insertintoEmploye(Name,course,score,Months)values('邓俊','seo培训',75,2);

insertintoEmploye(Name,course,score,Months)values('邓俊','网站框架培训',50,2);

insertintoEmploye(Name,course,score,Months)values('邓俊','演讲培训',80,2);

 

insertintoEmploye(Name,course,score,Months)values('余桥','seo培训',90,2);

insertintoEmploye(Name,course,score,Months)values('余桥','网站框架培训',60,2);

insertintoEmploye(Name,course,score,Months)values('余桥','演讲培训',50,2);

 

备注:后续继续整理！