数据库sql语句单表查询

简单的增删改查操作

select count(*) from user where account='admin' and password='123456'

select count(*) from user where account="admin"
insert into user(account,password) values ("admin","777")

update user set password = "666" where account="admin"

delete from student where id = 1

-- 查找

select id,name,age,sex from student

select * from student

where子语句：
-- 运算符
-- =等于
-- >大于
-- <小于
-- >=大于等于
-- <=小于等于
-- !=   <> 不等于

select * from student where age >= 21

select * from student where age <= 21

select * from student where age <> 21

-- 关键字
-- between .. and ...  []   介于..之间
select * from student where id between 2 and 5

-- in  包含
select * from student where id in(1,2,4,6)

-- is null 为null
select * from student where sex is null

-- 逻辑运算符
-- and 并且 
-- or 或者
-- not  非  与 is  in 搭配
select * from student where age >20  and id in(1,2,4,6)

select * from student where age >=20  and age <= 32 and name = "李四"

-- select * from student where age between 20 and 32

select * from student where age >=32 or age<=20  or sex ="女"

select * from student where sex is not null

select * from student where id not in(1,2,4,6)

-- 模糊查找  like  占位符 _代表一位字符 %代表任意位字符

select * from student where name like "_李"//李前边有一位

select * from student where name like "李%"//李后边有多位

select * from student where name like "%李%"//李前后都有多位

limit子语句 限制查询：
-- limit a , b
-- limit b offset a
-- a表示起始索引值，索引从0开始  b代表查询个数
select * from student limit 7,2

从索引为7的位置开始向下查两条

这个索引相当于数组的角标

-- 一页四条
-- 第一页
select * from student limit 0,4
-- 第二页
select * from student limit 4,4
-- 第三页
select * from student limit 8,4

-- 页码page  一页大小 pageSize

select * from student limit (page-1)*pageSize,pageSize

比如浏览器搜索每页有很多条在下边有页码
select * from student where sex = "女" limit 0,4

从性别为女的数据中找出前四条
-- 排序子语句  order by 列名   desc降序|asc升序  （asc可省）

select * from student order by age asc

按照年龄大小降序排序
select * from student where age>21 order by age desc limit 0,4

-- where    order by    limit

写的顺序
-- 分组函数 聚合函数
-- 聚合函数 
-- sum 求和
-- avg 取平均
-- max 取最大值
-- min 取最小值

用法

select min(age) from student min可以用上述函数替换
-- count 取得记录数量  count(字段名) 不统计为null的记录
select count(*) from student
-- group by 分组函数

select class,max(age) from student group by class

select class,max(age) from student where sex = "女" group by class having class = 3

分组之前用where找 分组之后用having找 两者用法完全相同