记录复习片刻之MySQL-05------数据的查询

数据的准备

--创建一个数据库

create database Stu;

--使用数据库

use Stu;

--创建一个学生表

create table if not exists students(

        id int unsigned primary key auto_increment not null,

        name varchar(20) default ' ',

        age tinyint unsigned default 0,

        height decimal(5,2),

        gender enum('男','女','中性','保密')

        cls_id int unsigned default 0,

        is_delete bit default 0

);

--创建classes表

create table if not exists(

        id int unsigned auto_increment primary key not null,

        name varchar(30) not null

);

--向students表中插入数据

insert into students values

(0,'小明',18,180.00,1,2,0),

(0,'小x',18,180.00,2,2,0),

(0,'小a',18,180.00,1,2,0),

(0,'小b',18,180.00,1,1,0),

(0,'小c',18,180.00,2,2,0),

(0,'小d',18,180.00,1,1,0),

(0,'小e',18,180.00,2,2,0),

(0,'小f',18,180.00,1,3,0);

--普通查询

        --查询所有字段

                select * from students;

                select * from classes;

        --查询指定字段

                select id,name from classes;

        --使用as 给字段起别名

                select  name as 姓名,age as 性别 from students;

        --select  表名.字段 ....from 表名；

                select students.name,students.age from students;

        --可以通过 as 给表起别名

                select stu.name,stu.age from students as stu;

        --查询消除重复行

                select distinct name,gender from students;

 --条件查询（and not or）

        --逻辑运算符

        --18到28之间的所有学生信息

                select * from students where age>=18 and age<=28;

        --18岁以上的的女性

                select * from students where age >= 18 and gender = "女";

        --不在 18岁以上的女性 这个范围内的信息

                select * from students where not (age > 18 and gender = "女");

        --年龄不是小于或者等于18 并且是女性

                select * from students where not (age<=18) and gender = "女";

--模糊查询

        --like 

        --rlike 正则

        -- “%替换1个或多个”

        --“_”替换一个

        --查询姓名中，以“小"开头的名字

        select name from students where name lie "小%";

        --查询姓名中，包含"小的"所有名字

        select name from students where name like "%小%";

        --查询有2个字的名字

        select name from students where name like "__”；

        --查询有3个字的名字

        select name from students where name like "___”；

        --查询至少有2个字的名字

        select name from students where name like "__%";

        --rlike 正则

        --查询以 周开始的名字

        select name from students where name rlike "^周.*";

        --查询 以周开始 伦结尾的名字

        select name from students where name rlike "^周.*伦$"

--条件查询

        --范围查询

        查询年龄为18、34的姓名

        select name from students where age = 18 or age = 34;

        select name from students where age in (18,24);

        查询年龄不是18、34的姓名

        select name from students where age not in (18,24);

        select name from students where age != 18 or age != 34;

        查询年龄不在18到34岁之间的信息

        select * from students where age between 18 and 34;

--范围查询

        查询年龄为18、34的姓名

        select name from students where age = 18 or age = 34;

        select name from students where age in (18,24);

        查询年龄不是18、34的姓名

        select name from students where age not in (18,24);

        select name from students where age != 18 or age != 34;

        查询年龄不在18到34岁之间的信息

        select * from students where age between 18 and 34;

--空判断

        --查询身为空值的信息；

        select * from students where height is null;

        --查询身不为空值的信息；

        select * from students where height is not null;

--排序查询

        --查询年龄在18到34岁之间的男性，按照年龄从小到大排序

        select * from students where (age between 18 and 34)  and gender = "男" order by age asc;

        --查询年龄在18到34岁之间的女性，按照年龄从小到大排序

        select * from students where (age between 18 and 34)  and gender = "女" order by age desc;

        --查询年龄在18到34岁之间的女性，按照年龄从小到大排序，如果年龄相同再按照就按照身高从小到大排序

        select * from students where (age between 18 and 34) and gender = "女" order by age asc,height asc;

--聚合

        --求总数(count(*))

        --查询男性有多少人，女性有多少人

        select count(*) from students where gender = "男";

        select count(*) from students where gender = "女";

        --最大值

        查询最大的年龄

        select  max(age) from students;

        --最小值

        查询女性最高的身高

        select max(height) from students where gender="女";

        --平均值

        查询平均年龄

        select avg(age) from students;

        select sum(age)/count(*) from students;

        --四舍五入 round(123.23,1) 保留一位小数

        --计算所有人的平均年龄，保留2位小数

        select round(avg，2) from students;

--分组

        --group by

        --按照性别分组，查询所有的性别

        seelct * from students group by gender;

        --计算每种性别中的人数

        select gender,count(*) from students group by gender;

        --计算男性人数

        select gender,count(*) from students group by gender having gender = "男";

        select gender,count(*) from students where gender = "男" group by gender;

        --查询男性性别中的姓名

        select gender,group_concat(name,"-",age," ",id) from students where gender =1 group by gender;

        

        --having 分组后筛选

        --查询平均年龄超过30的性别，以及姓名 having avg(age) > 30

        select gender,group_concat(name) from students group by gender having avg(age) > 30;

        --查询每种性别中的人数多于2的信息

        select gender,group_concat(name) from students group by gender having count(*) > 2;

--分页查询

        --limit start,count

        --查询前五个数据

        select * from students limit 5;

        select * from students limit 0,5;             （不包含0）

        --查询id6-10 的信息

        select * from students limit 5,5;           （不包含5）

        

        ---------------------limit (第N页-1)*每个页面的个数，每页的个数---------------

        --每页查询2个，第一个页面

        select * from students limit 0,2;

        --每页查询2个，第二个页面

        select * from students limit 2,2;

        --每页查询2个，第三个页面

        select * from students limit 4,2;

       --每页查询2个，第四个页面

        select * from students limit 6,2;   

连接查询

        内连接

select * from students inner join classes on student.cls_id = classes.id;

select * from students s inner join classes as c on s.cls_id = c.id;

        左连接(以左表为基准进行连接，只要左表有的都显示)

select * from students s left join classes as c on s.cls_id = c.id;

        右连接

select * from students s right join classes as c on s.cls_id = c.id;