sql 語句 二，sql語句

sql 語句 二，sql語句
選單列資料
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;