Mysql单表查询

mysql 单表查询示例

MariaDB [hellodb]> select * from students;
MariaDB [hellodb]> select name as 姓名,age 年龄,stuid from students;
MariaDB [hellodb]> select name as 姓名,age 年龄,stuid from students as 学生表;
MariaDB [hellodb]> select * from students where stuid >=20;
MariaDB [hellodb]> select * from students where stuid <> 20;
MariaDB [hellodb]> select * from students where name = 'xu zhu';
MariaDB [hellodb]> select * from students where age > 10 and gender = 'f';

-- sql 注入
MariaDB [hellodb]> select * from user where username='admin' and password='' or '1'='1';
MariaDB [hellodb]> select * from user where username='admin'--' and password=''';

MariaDB [hellodb]> select * from students where age >=20 and age <= 30;
MariaDB [hellodb]> select * from students where age between 20 and 30;


MariaDB [hellodb]> select * from students where name like 's%';  // 推荐使用
MariaDB [hellodb]> select * from students where name rlike '^s'; // 工作中不推荐使用
MariaDB [hellodb]> select * from students where name regexp '^s';

MariaDB [hellodb]> select * from students where name like '%yu%';  //工作中不推荐写

MariaDB [hellodb]> select distinct age from students; //去重

MariaDB [hellodb]> select * from students where classid is null;
MariaDB [hellodb]> select * from students where classid is not null;
MariaDB [hellodb]> select count(stuid) from students; //统计 stiid不为空的记录数
MariaDB [hellodb]> select count(stuid) as 行数 from students;
MariaDB [hellodb]> select count(*) from students;
MariaDB [hellodb]> select max(age) from students;
MariaDB [hellodb]> select min(age) from students;
MariaDB [hellodb]> select avg(age) from students;

-- 男生、女生的平均年龄
MariaDB [hellodb]> select gender,avg(age) from students group by gender;

-- 每个班的平均年龄
MariaDB [hellodb]> select classid,avg(age) from students group by classid;

-- 大于3的班级的平均年龄   having分组之后再作过滤
MariaDB [hellodb]> select classid,avg(age) from students group by classid having classid > 3; 

-- 大于3的班级的平均年龄  where分组之前过滤
MariaDB [hellodb]> select classid,avg(age) from students where classid > 3 group by classid;

-- 大于3的班级的平均年龄中年龄大于30的班级和平均年龄
MariaDB [hellodb]> select classid,avg(age) avg from students where classid > 3 group by classid having avg > 30;

-- 每个班级的男生、女生的平均年龄
MariaDB [hellodb]> select classid,gender,avg(age) from students group by classid,gender;


-- 对年龄进行排序，默认升序
MariaDB [hellodb]> select * from students order by age;

-- 对年龄进行排序，降序
MariaDB [hellodb]> select * from students order by age desc;

-- 对班级进行升序排列，空值的优先级最高，排列在最前面
MariaDB [hellodb]> select * from students order by classid;

-- 对班级进行升序排列，空值排列在最后，只针对数字列有效
MariaDB [hellodb]> select * from students order by -classid desc;


-- 对班级进行分组，统计年龄之和，再按班级升序排列，班级为空的排列在最后
MariaDB [hellodb]> select classid,sum(age) from students group by classid order by -classid desc;

-- 对班级进行分组，统计年龄之和，再按班级升序排列，班级为空的过滤掉
MariaDB [hellodb]> select classid,sum(age) from students group by classid having classid is not null order by classid;
MariaDB [hellodb]> select classid,sum(age) from students where classid is not null group by classid order by classid;

-- 对班级进行分组，统计年龄之和，再按班级升序排列，班级为空的过滤掉，只要前3条记录
MariaDB [hellodb]> select classid,sum(age) from students where classid is not null group by classid order by classid limit 3;


-- 对班级进行分组，统计年龄之和，再按班级升序排列，班级为空的过滤掉，跳过前2条记录，取后3条记录
MariaDB [hellodb]> select classid,sum(age) from students where classid is not null group by classid order by classid limit 2,3;


-- 统计 1或3或5 班的信息，按班级升序排列
MariaDB [hellodb]> select * from students where classid=1 or classid=3 or classid=5 order by classid;
MariaDB [hellodb]> select * from students where classid in (1,3,5) order by classid;