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


select * from stu
select * from cj

select * from  stu where sid not in(
  select sid from cj
  )
 
select * from stu inner join cj as c on stu.sid=c.sid

select * from stu left join cj as c on stu.sid=c.sid
where c.`sid` is null