MySQL常用的查詢語句回顧

標籤：

讓你快速複習語句的筆記寶典。

create table users(
   username varchar(20) primary key,
   userpwd varchar(20)
)

alter table users add age int


insert into stu (sname)values(‘sdfdsfdsfeeeeee‘)
update cj set cj=60 where sid=1

delete from cj where sid=2

--所有行所有列

select * from stu
--某一些行所有列
select * from stu where sid>3
--某一些行某一些列
select sname from stu where sid>=3
--某一列所有行
select sname from stu

--mysql中限制m stu limit 3

select * from stu limit 3,2

--排序
select * from stu order by sid asc

--模糊尋找
select * from stu where sname like ‘%aa%‘

--彙總函式使用
select  sum(c.cj) from cj as c

select count(*) from stu

select count(userpwd) from users

--分組
select count(*),age from users group by age
--對分組之後查詢使用having
select count(*),age from users group by age having count(*)>=2

---null查詢
select * from users where userpwd is not null

select * from users where age between 20 and 30
select * from users where username between ‘a‘ and ‘l‘

-- in子查詢
select * from stu where sid in(1,2,3,4,7,6,8,9)

--內串連
select * from stu inner join cj as c on stu.sid=c.`sid`
where stu.sid=1

--等值串連
select * from stu ,cj as c where stu.sid=c.`sid`

--外串連
select * from cj as c right join stu  on stu.sid=c.sid

---子查詢
select * from stu where age>(
   select age from stu where sname=‘lisi‘
   )

轉載自：http://blog.csdn.net/supera_li/article/details/9303709

MySQL常用的查詢語句回顧