一、简单的 增、删、改、查
insert into Student (sName,sGender,sAge)values('key','男',19);
delete from Student where sId=1;
update Student set sName='胡章诚',sGender='男',sAge=21 where sId=5
select sName sGender from Student where sId=1;
====================================================
二、各种复杂的查询
1、 单表查询
select sName, 2013-sAge from Student where sName='胡章诚'; 2013-sAge
select distinct sGender from Student; distinct 用于过滤重复数据
where后面可以跟的条件控制的关键字
= > < >= <= != <> !> !< NOT(上述运算符)
(not) between...and (not) in(...) (not) like
is null is not null and or not
select * from Student where sAge<20;
select * from Student where sAge between 18 and 20; 18=< <=20
select * from Student where sId in(1,2,3,4,5);
select * from Student where sName like'左__'; _ 代表任意长度的单个字符
select * from Student where sName like'左%'; % 代表任意长度的字符串
select * from Student where sClass is null;
order by：
分组的目的:细化聚集函数的作用对象,在未分组之前聚集函数作用于整张表,分组以后,作用于having出的每个组
先用where过滤整张表的数据，后面不能使用统计函数
然后用group对过滤后的数据进行分组，
再用having过滤得到的分组，后面可以使用统计函数
分组后的 select 后面只能加 聚合函数得到的值 或者 group by 的时候使用的字段
select sAge from Student where sGender='男' group by sAge having sAge=20
聚集函数:
count(*) 取出又对少条数据
sum() 求和
avg() 求平均值
max() 最大值
min() 最小值
================================================================================================
2、连接查询
: http://www.2cto.com/database/201111/112114.html
==============================================================================================
3、嵌套查询: 将括号里查询出的结果当做参数来使用，父级查询 和 子级查询 可以来自不同的表
select * from Student where sAge
in (select sAge from Student where sGender='男'); 当子查询里面得到的值只有一个的时候，将 in 用 = 代替
select * from Student where sAge
>= ANY(select sAge from Student where sName='胡章诚'); 只要 >= 其中的一个就满足条件
select * from Student where sAge 要比里面所有的都大才满足条件
>= ALL(select sAge from Student where sName='胡章诚');
===============================================================================================
4、集合查询
select sno from Student where sGender='男' “求并集”， 如果将all去掉，就会筛选掉重复数据，否则不会
union all
select sno from Sc where Cno='101';
select sno from Student where sGender='男' 求交集
intersect
select sno from Sc where Cno='101';
select sno from Student where sGender='男' 求差集
except
select sno from Sc where Cno='101';
=======================================================================
三、约束
---添加主键约束 ：
Alter Table stuInfo
Add Constraint PK_stuNO primary Key(stuNo)
---添加唯一约束
Alter Table stuInfo
Add Constraint UQ_stuID unique(stuID)
---添加默认约束
Alter Table stuInfo
Add Constraint DF_stuAddress default('地址不详') for stuAddress
---添加检查约束
Alter Table stuInfo
Add Constraint CK_stuAge check(stuAge between 15 and 40)
---添加外键约束
Alter Table stuMarks
Add Constraint FK_stuNo foreign key(stuNo) references stuInfo(stuNo)
。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。
删除约束的语法如下:
Alter Table 表名
Drop Constraint 约束名
===========================================================================================
四、常用函数: http://blog.csdn.net/hamber_bao/article/details/6504905
================================================================================================
五、视图
create view jsj_student as select * from Student where Sdept='计算机'
create view j2_student(Sno,Sname,Grade,Sage)
as select Student.Sno,Sname,Grade,Sage from Student,Sc
where Sdept='经济系' and Student.Sno=Sc.Sno and Sc.Cno='101'
select Sno,Sage from j2_student