选单列数据
select name from totoro;
选两列数据
select id,name from totoro;
选单列中不重复的数据
select distinct name from totoro;
选出name列中名字是pangpang3的数据
select * from totoro where name='pangpang3';
选出age>3的数据
select * from totoro where age>3;
and
select * from totoro where id=1 and age=1;
or
select * from totoro where id=1 or age=3;
order by 排序
select age from totoro order by age;
选两列，按age排序
select id,age from totoro order by age;
DESC，降序
select age,name from totoro order by name DESC;
ASC，升序
select name,id from totoro order by name ASC;
部分插
insert into totoro (id,name) values (10,'feifei');
部分更新
update totoro set id=13,name='mark' where age=9;
删除
delete from totoro where id=1;
选前3行 limit
select * from totoro limit 3;
以i结尾的name
select * from totoro where name like '%i';
含om的name
select * from totoro where name like '%om%';
name第一个字符后是hu的
select * from totoro where name like '_hu';
name第2是a,第4是k
select * from totoro where name like '_a_k';
name是tom，ii
select * from totoro where name in ('tom','ii');
取anan和mary之间的name
select * from totoro where name between 'anan' and 'mary';
as指定别名
select name as n from totoro;
表名totoro as t
select t.id from totoro as t;
两张表中id相等的name
select totoro.id,totoro.name,user.name from totoro,user where totoro.id=user.id;
join on
select totoro.id,totoro.name,user.name from totoro join user on totoro.id=user.id order by totoro.id;
取两张表中id相同的数据
select totoro.id,totoro.name,user.name from totoro,user where totoro.id=user.id;
inner join
select totoro.id,totoro.name,user.id from totoro inner join user on totoro.id=user.id order by totoro.id;
|