select name as 姓名,age as 年龄 from students;select s.name s.age from students as s;
去重
select name as 姓名,age as 年龄 from students;select s.name s.age from students as s;
条件查询
--比较运算符.. > < >=<==!=
查询大于18岁信息
slet*from students where age >=18;
逻辑运算符
--and or not
查看18到28岁之间的所有学生的信息
select*from students
where age>18and age<28;
年龄小于或等于18并且是女性
select*from students where age<=18and gender-°女:
模糊查询
-like-%替换1个或者多个
-_替换1个
一查询姓名中以小开头的名字
select*from students where name like “小%”
-查询有2个字的名字
select name from students where name like" .
一查询至少有2个字的名字
selet name from students where name like" _%"
-- rlike正则-查询以周开始的姓名
seles name from students where name rlike“周.*”;
一查询以周开头以伦结尾
selet name from students where name rike “^周.*伦$;
范围查询
--in(1,3,8)表示在一个非连续的范围内notin(1,3,8)--查询年龄为18,34的姓名select name,age from students where age in(18,34);--between..and..表示在一个连续的范围notbetween...and..--查看年龄在18到34之间的姓名,年龄select name,age from students where age between18and34;--判空is null
判非空isnotnull--查询身高为空的信息select*from students where height isnull;
排序
--orderby字段-- asc从小到大,升序 --desc从大到小,降序-- 默认升序--查询年龄子啊18到34岁之间的男性,按照年龄从小到大-- select * from表名where条件order by字段asc/desc字段asc/desc;select*from students where(age between18and34)and gender="男"orderby age asc;select*from students where(age between18and34)and gender='男”orderby age asc, height asc;
聚合函数
--总数count--查询男性有多少人,女性有多少入selectcount(*)as男性人数from students where gender=1;selectcount(*)as女性人数from students where gender=2;--最大值max-- 最小值min--查询最大的年龄selectmax(age)from students;--查询女性最高升高selectmax(height)from students where gender=2;--求和sum-- avg平均值-计算所有人的年龄总和
selectsum(age)from students;--计算平均年龄avg sun(agel/count()selectavg(age)from students;-round(123.23,1)保留1位小数
-- 计算所有人的平均年龄,保留2位小数selectround(avg(age),2)from students;--计算男性的平均身高,保留2位小数selectround(avg(height,2))from students where gender=1;
分组查询
-- group by--按照性别分组,查询所有性别select gender from students groupby gender;-- group concat0--计算每种性别中的人数select gender,count(*)from students groupby gender;select gender,group concat(name,age)from students groupby gender;--计算男性人数select gender,count(*)from students where gender=1groupby gender;-- having--查询平均年龄超过30岁的性别,以及姓名having avg(age)>30select gender,group concat(name),avg(age)from students groupby gender havingavg(age)>30;--查询每种性别当中的人数大于2的信息select gender,group concat(name)from students groupby gender havingcount(*)>2;
分页查询
-- limit start, count start 开始的位置,不写默认从开始,count 个数-- 限制查询出来的数据的个数select ★from students limit5;-- 查询id6-10select*from students limit5,5--显示第N页,每页显示5个limit (第N页-1)*5,5不能这样写
多表查询
--链接查询--内连接.inner join ... on ..--查询有能够对应班级的学生以及班级信息select*from students innerjoin classes on students.cls_ id = classes.id;-按照要求显示姓名、班级
select students.*,classes.name from students innerjoin classes on students.cIs id = classes.id;--给数据表起名select s.*,c.name from students as s innerjoin classes as c on s.cls_ id = c.id;-左链接leftjoin...--查询每位学生对应的班级select*from students as s leftjoin classes as C on s.cIs_ id = c.id;-查询没有对应班级信息的学生where/havingselect*from students as s leftjoin classes as C on s.cls_ id = c.id having c.name isnull;select*from students as s leftjoin classes as C on s.cIs_ id = c.id where c.name isnull;
查询指定字段select name,age from students;起别名select name as 姓名,age as 年龄 from students;select s.name s.age from students as s;去重select name as 姓名,age as 年龄 from students;select s.name s.age ...