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'
)
