MySQL 简单查询语句

-- 简单查询
select * from student
-- 多条件查询
select * from student where age = 18 || age=20
-- 模糊查询  %代表多个字符   _代表一个字符
select * from student where sname like '__'
-- 区间查询
select * from student where age between '18' and '25'
-- 空值查询
select * from student where cid is null
-- 删除所有数据 删除符合条件的数据
delete from student where cid is null
delete from student
-- 筛选条件修改 
update student set address='湖北' where  sanme='小甜甜'
update student set address='武汉' ,cid=4 where  sex='m' && age=20
-- 排序 desc降序 asc升序
select * from student where address='武汉' order by age desc, sid desc
-- limit分页 limit 下标从哪里开始,显示几条数据
select * from student limit 0,5
-- select * from limit (当前页-1)*显示条数,显示条数
-- select * from limit 当前页为2,显示3
select * from student limit 3,3
-- 聚合函数 求和sum() 最大值max() 最小值min() 平均值avg() 计数count()
-- 最大值和最小值获取的值只能有一个
-- count(*) 算空值    count(列名) 不算空值
select max(age) from student 
select min(age) from student 
select sum(age) from student 
select avg(age) from student 
select count(*) from student 
-- 分组 select ... from 表名 [where 条件] [group by 类名 [having 条件]] [order by 列名 desc/asc]
-- having 不能单独使用
-- 男女分组计数
select sex,count(*) from student group by sex
-- 取别名   列名/表名 [as] 别名  
select sex as 性别,count(*) 个数 from student group by sex
-- 男女  20岁以上  分组计数
select sex as 性别,count(*) 个数 from student where age >= 20 && sex='m' group by sex 
-- 获取当前年月日时分秒
select now()
-- 获取当前年份
select year (now())
-- 获取当前月份
select month (now())
-- 获取当前日
select day (now())
-- 查询2001年入职员工的信息
select * from emp where year(hiredate)=2001
select * from emp where day(hiredate)=3
-- 两表连查
-- 两表对应关系
-- select ... from 表1 ,表二,表三... where 表1.列名==表二.列名
select s.*,c.cname from student s,classes c where s.cid=c.cid
select c.cname,count(*) from student s,classes c where s.cid=c.cid  group by c.cname
select s.sname,c.cname from student s,classes c where s.cid =c.cid && s.sname like '%小%'
-- 三表
select s.sname,s.sex,c.kname,k.cj from student s,score k,course c where s.sid=k.sid && k.kid=c.kid && c.kname='数学' && s.sex='f'
-- 四表
select s.*,b.cname,c.kname,k.cj from student s,score k,course c,classes b where s.sid=k.sid && k.kid=c.kid && s.cid=b.cid && b.cname='2901班' && c.kname='语文'
-- 子查询
select max(age) from student
select * from student where age=(      select max(age) from student    )
select * from student where cid=(      select cid from student where cname='2901班'   )
-- 子查询两表连查
select * from score where kid=(      select kid from course where kname='语文'   )
行列转换
-- case 列名 when '值1' then '值2' end结束
select sname ,case sex when 'f' then '女' when 'm' then '男' end 性别 from student