数据库操作

– 创建两个数据表

-- 创建students表(id, name, age, high, gender, cls_id)
create table students(
    id int unsigned not null auto_increment primary key,   -->整形无符号
    name varchar(30),
    age tinyint unsigned default 18,
    high decimal(5,2),    -->一共五位两个小数
    gender enum("男", "女", "保密") default "保密",
    cls_id int unsigned,
    is_delete bit default 0
);

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

-- 向students表中插入数据
insert into students values
(0,"小明",18,180.00,1,1,0),
(0,"彭于晏",29,185.00,1,1,0),
(0,"刘德华",59,175.00,1,2,1),
(0,"黄蓉",38,160.00,2,1,0),
(0,"王祖贤",18,172.00,2,3,1),
(0,"刘亦菲",25,166.00,2,2,0),
(0,"静香",12,180.00,2,4,0),
(0,"金星",33,180.00,3,3,0);

-- 向classes表中插入
insert into classes values
(0,"前端"),
(0,"测试");

– 查询

-- 查询所有字段
-- select * from 表名;
select * from students;

-- 查询指定字段
-- select 列1,列2,.. from 表名;
select name, age from students;
-- 通过 as 给字段起别名
select name as 姓名, age as 年龄 from students;

-- 通过 as 给表起别名
-- select 别名.字段 .... from 表名 as 别名;
select s.name, s.age from students as s;

-- 消除重复行
-- disrinct 字段
select distinct gender from students;


-- 条件查询
    -- 比较运算符
    -- select ... from 表名 where ...
    -- 查询大于18岁的信息
    select * from students where age>18;

    -- 查询年龄为18的学生的名字
    select name from students where age=18;

-- 逻辑运算符
    -- and
    -- 年龄18到28之间的所有学生信息
    select * from students where age>18 and age<28;
    -- 18岁以上的女性
    select * from students where age>18 and gender="女";

    -- or
    -- 18岁以上或者上高超过180(包含)以上
    select * from students where age>18 or high>=180;

    -- not
    -- 不在18岁以上的女性 这个范围的信息
    select * from students where not (age>18 and gender=2);

    -- 年龄不是小于或者等于18 并且是女性
    select * from students where (not age<=18) and gender=2;

-- 模糊查询
    -- like
    -- % 替换1个或者多个
    -- _ 替换一个
    -- 查询姓名中以“小”开始的名字
    select name from students where name like "小%";

    -- 查询有2个字的名字
    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 "^刘.*菲$";

-- 范围查询
    -- in (1, 3, 8)表示一个菲连续的范围内
    -- 查询年龄为18、33的姓名
    select name,age from students where age=18 or age=33;
    select name,age from students where age in (12, 18, 33);

    -- not in 不是非连续范围内的
    -- 年龄不是12、18、33的
    select name,age from students where age not in (12, 18, 33);

    -- between ... and ... 表示在一个连续范围内的
    -- 查询 年龄在18到33之间的信息
    select name,age from students where age between 18 and 33;

    -- not between ... and ... 表示不在一个连续的范围内
    -- 查询年龄不在18到33之间的信息
    select * from students where age not between 18 and 33;
    select * from students where not age between 18 and 33;

-- 空判断
    -- 判空 is null
    -- 查询班级为空的信息
    select * from students where cls_id is null;

    -- 判非空 is not null
    select * from students where cls_id is not null;

– 排序

-- order by 字段，默认升序
-- asc 从小到大排序，升序
-- desc 从大到小排序，降序

-- 查询年龄在18到34岁之间的男性，按照年龄从小到大排序
select * from students where (age between 18 and 34) and gender=1 order by age;

-- 查询年龄在18到34之间的女性，身高从高到矮排序
select * from students where (age between 18 and 34) and gender=2 order by high desc;

-- order by 多个字段
-- 查询年龄在18到34岁之间的女性，身高从高到矮排序，如果升高相同情况下主键从大到小排序
 select * from students where (age between 18 and 34) and gender=2 order by high desc, id desc;

-- 按照年龄从小到大、身高从高到矮排序
select * from students order by age, high desc;

– 聚合函数

-- 总数
-- count
-- 查询男性有多少人，女性有多少人
select * from students where gender=1;
select count(*) from students where gender=1;
select count(*) as 男性人数 from students where gender=1;

-- 最大值
-- max
-- 查询最大的年龄
select max(age) from students;

-- 查询女性的最高身高
select max(high) from students where gender=2;

-- 最小值
-- min
select min(high) from students where gender=2;

-- 求和
-- sum
-- 计算所有人的年龄总和
select sum(age) from students;

-- 平均值
-- avg
-- 计算平均年龄
select avg(age) from students;

-- 四舍五入 round(123.23， 1) 保留1位小数
-- 计算所有人的平均年龄，保留2位小数
select round(sum(age)/count(*), 2) from students;

-- 计算男性的平均身高 保留2位小数
select round(sum(high)/count(*), 2) from students where gender=1;
select round(avg(high), 2) from students where gender=1;

– 分组

-- group by
-- 按照性别分组，查询所有的性别
-- 错误 select name from students group by gender;
-- 错误 select * from students group by gender;
select gender from students group by gender;

-- 计算每种性别中的人数
select gender,count(*) from students group by gender;

-- 计算男性的人数
select gender,count(*) from students where gender=1 group by gender;

-- group_concat(...)
-- 查询同种性别中的姓名
select gender,group_concat(name,"_",gender) from students group by gender;

-- having
-- 查询平均年龄超过30岁的性别，以及姓名 having avg(age) > 30;
select gender, group_concat(name), avg(age) from students group by gender having avg(age)>30;

-- 查询每种性别中人数多于2个的信息
select gender, group_concat(name), count(*) from students group by gender having count(*)>2;

– 连接查询

-- inner join ... on
-- select ... from 表A inner join 表B;
select * from students inner join classes;

-- 查询能对得上的学生的信息
select * from students inner join classes on students.cls_id=classes.id;

-- 按照要求显示名字、班级
select students.*, classes.name from students inner join classes on students.cls_id=classes.id;

-- 给表起名字
select s.name, c.name from students as s inner join classes as c on s.cls_id=c.id;

-- 查询有能够对应班级的学生及班级信息，按照班级进行排序
select c.name, s.* from students as s inner join classes as c on s.cls_id=c.id order by c.name, s.id;

-- left join
-- 查询每位学生对应的班级信息
select * from students  as s left join classes as c on s.cls_id=c.id;

-- 查询没有对应班级信息的学生
select * from students as s left join classes as c on s.cls_id=c.id having c.id is null;

– 子查询

-- 标量子查询
-- 查询出高于平均身高的信息

-- 查询身高的男生信息
select * from students where high = 188;
select * from students where high = (select max(high) from students);