分类： Mysql/postgreSQL
2011-07-12 21:24:02
DML
select insert delete update
select查询语句
格式：
select 要显示的字段
from 表名[可以写多个表名]
where 查询的条件
select * from tablename
“*”表示把tablename表的所有字段全部查询显示
select * from person
select id,name,*** from person;
select id id1,name name1,*** ***1 from person;
select id as id1,name name1,*** ***1 from person;
select p.id,p.name,p.*** from person p;
select id,name,*** from person where id=2;
如果查询需要多个条件
在where把条件用and 或 or 连接
and:所有条件必须为真
or：只要有一个满足条件
and 优先级高于or
select id,name,*** from person where id=2 and name='aaa' or ***=1;
select id,name,*** from person where id=2 or name='aaa' and ***=2;
select id,name,*** from person where (id=2 or name='aaa') and ***=2;
运算符
+ - * / %
select id*100 ,name,*** from person;
select 9+8;
比较符号
> >= < <= != <>
select * from person where id>1;
select * from person where id>=1;
select * from person where id<1;
select * from person where id<=1;
select * from person where id!=1;
select * from person where id<>1;
select * from person where id>=1 and id<=3;
between and
select * from person where id between 1 and 2;
in
select * from person where id in(1,2,3,4);
select * from person where id in (select id from person);
not in
select * from person where id not in(1,2,3,4);
select * from person where id not in (select id from person);
is null
select * from person where name is null;
select * from person where name='';
is not null
select * from person where name is not null;
like模糊查找
%value
value%
%value%
select * from person where name like 'a%';
//以a开头
select * from person where name like '%a%';
//只要包含a
select * from person where name like '%a';
//以a结尾
select * from person where name like '%';
多表关联查询
存在主外键关系的表才能关联查询
多表关联三种不同的方式
1.内连接
select * from student,class where student.cid=class.cid;
select * from student s,class c where s.cid=c.cid;
查找出两个表中公有的记录(根据CID判断)
2.左连接
select * from class left join student on student.cid=class.cid;
//class表为主，首先把class表的所有记录显示出来，然后在根据CID关联到student表中查询
select * from student left join class on student.cid=class.cid;
//student表为主，首先把student表的所有记录显示出来，然后在根据CID关联到class表中查询
3.右连接
select * from student right join class on student.cid=class.cid;
//class表为主，首先把class表的所有记录显示出来，然后在根据CID关联到student表中查询
备份表(表的结构和全部数据)
create table tablename2
as
select *
from tablename
备份表(只备份了表的结构)
create table tablename2
as
select *
from tablename
where 1=2
insert
插入数据到表中
insert into tablename
values(value1,value2.......)
insert into class values(7,'cc');
insert into tablename(field1,field2,....) values(value1,value2,,,,,);
insert into student(id,name) values(9,'bbbb');
把某个表的所有记录插入到一个表中
insert into student3 select * from student;
update
修改记录语句
update tablename
set field1=value1,field2=value2
where
update student3 set name='bbbb';
update student3 set name='ccc' where id=3;
delete
删除表中的数据语句
delete from tablename
where
delete from student3 where id=3;
delete from student3;
排序
升序：asc
降序: desc
默认为升序
order by fieldname asc|desc,fieldname
select * from student order by name ,id desc;
select * from student where id>=1 and id<=6 order by id asc;
分组
根据具有相同值的字段分组
select * from student group by cid;
分组一般是与聚合函数一起使用
max 最大值
min 最小值
sum 求和
avg 求平均值
count 求记录数
select max(id) from student;
select min(id) from student;
select avg(id) from student;
select sum(id) from student;
select count(*) from student;
select cid,max(id),min(id),avg(id),sum(id),count(*) from student
group by cid;
对分组后的记录增加条件
只能用having增加条件，不能使用where
select cid,max(id),min(id),avg(id),sum(id),count(*) from student group by cid having avg(id)>=5;
limit
limit n
取前面第n笔记录
select * from student limit 3;
limit n,m
从第n+1笔开始取m条记录
select * from student limit 3,2;
