Mysql_Study(进阶)

Mysql_Study(进阶)

-- 学生表
create table students
(
    id        int unsigned primary key auto_increment not null,
    name      varchar(10),
    age       tinyint unsigned default 0,
    height    decimal(5, 2)    default 0,
    gender    enum ('男', '女', '保密'),
    cls_id    int unsigned     default 0,
    is_delete bit              default 0
);

-- 查询表创建语句
show create table students;

-- 班级表
create table classes
(
    id   int unsigned auto_increment primary key not null,
    name varchar(30)                             not null
);

-- 想students表中插入数据
insert into students values
(0, '小明', 10, 180.00, 2, 1, 0),
(0, '小月月', 23, 180.00, 2, 3, 1),
(0, '彭于晏', 44, 200.00, 2, 2, 1),
(0, '刘德华', 55, 190.00, 2, 4, 1),
(0, '黄蓉', 75, 180.00, 2, 1, 0),
(0, '凤姐', 37, 170.00, 2, 6, 1),
(0, '王祖贤', 79, 160.00, 2, 1, 0),
(0, '周杰伦', 87, 150.00, 2, 4, 0),
(0, '程坤', 65, 140.00, 2, 1, 1),
(0, '刘亦菲', 54, 130.00, 2, 3, 0),
(0, '金星', 33, 180.00, 2, 2, 1),
(0, '静香', 12, 155.00, 2, 4, 0),
(0, '郭靖', 12, 170.00, 2, 1, 1),
(0, '周杰', 34, 176.00, 2, 5, 0);


-- 修改数据
update students set gender=1 where id=1;
update students set gender=3 where id=2;
update students set gender=3 where id=3;
update students set gender=2 where id=4;
update students set gender=2 where id=5;


show create table classes;

-- 向班级表插入数据
insert into classes values (0, 'python01班'), (0, 'python_02班');

-- 查询学生表中的所有数据
select * from students;

-- 通过别名查询数据
select s.id, s.name from students as s;

-- 去重查询
select distinct gender from students;

-- 通过条件查询指定数据  where:条件查询  指定查询
select * from students where name='小月月';
select * from students where age=12;


-- 比较运算符
    -- 大于
select * from students where id > 10;

    -- 小于
select * from students where id < 10;

    -- 不等于
select * from students where id != 10;
-- select * from students where id <> 10;

-- 查询没有被删除的学生
select * from students where is_delete=0;

/*
    并且
    或者
    取反
*/
-- 查询id大于3的女同学
select * from students where id > 2 and gender=2;

-- 查询id小于4或者没有被删除的学生
select * from students where id < 4 or is_delete=0;

-- 查询id不等于4的所有学生
select * from students where not id=4;

-- 模糊查询(重点)
select * from students where name like '小%';
select * from students where name like '小_';   --  一个_代表一个占位符
select * from students where name like '_杰_';   --  一个_代表一个占位符


-- 查询id是1或者3或者8的学生 非连续
select * from students where id in (1, 3, 8);

-- 查询id是3到8的学生 连续
select * from students where id between 3 and 8;   -- between ··· and 是成对出现的

-- 查询编号3到8的男生
select * from students where id between 3 and 8 and gender='男';
# select * from students where (id between 3 and 8) and gender='男';


-- 空查询
select * from students where height is null;

-- 非空查询
select * from students where height is not null;

-- 查询性别保密，且升身高为空
select * from students where gender=3 and height is null;
# select * from students where (gender=3) and height is null;

-- 排序查询
-- 1.查询为删除的男生信息，按学号降序   排序规则一定是在条件规则后面
select * from students where gender='男' and is_delete=0 order by id desc;

-- 2.查询为删除的男生信息，按学号升序
select * from students where gender='男' and is_delete=0 order by id;

-- 3.查询未删除的男生信息，按姓名升序(按字节排序)
select * from students where is_delete=0 order by name;

-- 4.组合排序 查询女生信息 按照年龄升序排序 如果多个数据年龄相同则按照id倒叙
select * from students where gender='女' order by age, id desc;

-- 聚合函数
-- 总数
-- 查询students中的总行数
select count(*) from students;

-- 最大值
-- 查询女生id最大值
select max(id) from students where gender=2;

-- 最小值
-- 查询女生id最小值
select min(id) from students where gender=2;

-- 求和
-- 查询未删除的女生的总年龄
select sum(age) from students where gender='女' and is_delete=0;

-- 平均值
-- 查询未删除的女生的平均值年龄
select avg(age) from students where is_delete=0 and gender='女';

-- 分组查询
-- group by + group_concat()
select gender, group_concat(name) from students group by gender;

-- group by + 聚合函数
-- 1.分别统计性别为男/女同学的平均年龄
select gender as '性别', avg(age) as '平均年龄' from students group by gender;
select * from students;

-- 2.统计男/女同学的人数
select gender, count(*) from students group by gender;

-- group by + having
-- 查询学生表，根据性别进行分组 显示各个性别中的个数 并且每个分组的统计个数必须大于2
select gender, group_concat(name) from students group by gender having count(*) > 2;

-- 分页查询
-- select * from <表名> limit start, count;

-- 1.查询前三名同学
select * from students where gender=1 limit 0, 3;

-- 2.查询学生表中的前两位同学
select * from students limit 2;

-- 3.查询前五条数据
select * from students limit 0, 5;

-- 4.查询id为 6 - 10 的数据  用户设置的开始位置  - 1 第二个参数为当前数据的个数
select * from students limit 5, 5;

-- 5.查询id 11 -14 的数据
select * from students limit 10, 5;  -- 如果数据库中的数据没有这么多 则有多少取多少