MySQL数据库查询语句入门

准备工作：

• 创建数库

  create database python_test charset=utf8;

• 使用数据库

  use python_test;

• 查看当前正在用的数据库

  select database();

• 创建学生数据表

  create table 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("男","女","保密") default "保密",
cls_id int unsigned default 0,
is_delete bit default 0
);

• 创建班级数据表

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

• 单数据插入
  insert into students values(0,"小月月",18,160.00,2,2,0);

• 向students表中插入数据

  insert into students values
(0,"彭于晏",29,185.00,1,1,0),
(0,"刘德华",59,175.00,1,1,0),
(0,"黄蓉",38,160.00,2,1,0);

• 查询所有字段

  select * from students;
select * from classes;
select id,name from classes;

• 查询指定字段

  select name,age from students;

• 其别名

  select name as 姓名,age as 年龄 from students;

  select s.name s.age from students as s;

• 取消重复

  select distinct gender from students;

• 条件查询

--比较运算符
-- > <  >= <= = !=
--查询大于18岁信息
select * from students where age >= 18;

--逻辑运算符
-- and  or not 
-- 查看18到28岁之间的所有学生的信息
select * from students where age>18 and age<28;

--年龄小于或等于18 并且是女性
select * from students where age<=18 and gender="女";

• 模糊查询

-- like  
-- % 替换1个或者多个
-- _ 替换1个
-- 查询姓名中以“小”字开头的名字
select * 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)表示在一个非连续的范围内
-- not in (1,3,8)
-- 查询年龄为18,34 的姓名
select name,age from students where age in (18,34);

-- between ... and ... 表示在一个连续的范围
-- not between ... and ...
-- 查看年龄在18到34之间的姓名，年龄
select name,age from students where age between 18 and 34;

-- 判断
    -- 判空 is null
    -- 判非空 is not null
    -- 查询身高为空的信息
    select * from students where height is null;

• 排序

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

--查询年龄子啊18到34岁之间的男性，按照年龄从小到大
-- select * from 表名 where 条件 order by 字段 asc/           desc,字段 asc/desc;
select * from students where (age between 18 and 34) and gender="男" order by age asc;
select * from students where (age between 18 and 34) and gender="男" order by age asc , height asc;

• 聚合函数

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

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

-- 查询女性最高升高
select max(height) from students where gender=2;

-- 求和 sum
-- avg 平均值
-- 计算所有人的年龄总和
select sum(age) from students;
-- 计算平均年龄 avg   sun(age)/count(*)
select avg(age) from students;

-- round(123.23 ,1) 保留1位小数
-- 计算所有人的平均年龄，保留2位小数
select round(avg(age),2) from students;
-- 计算男性的平均身高，保留2位小数
select round(avg(height,2)) from students where gender=1;

• 分组

-- group by
-- 按照性别分组，查询所有性别
select gender from students group by gender;

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

-- 计算男性人数   
select gender,count(*) from students where gender=1 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) from students group by gender having count(*)>2;

• 分页

-- limit start,count   start 开始的位置，不写默认从0开始， count 个数
-- 限制查询出来的数据的个数
select * from students limit 5;
-- 查询id6-10
select * from students limit 5,5

-- 显示第N页，每页显示5个  limit (第N页-1)*5,5 不能这样写

• 多表查询

-- 链接查询
-- 内连接 ...inner join ... on ...
-- 查询 有能够对应班级的学生以及班级信息
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.*,c.name from students as s inner join classes as c on s.cls_id = c.id;

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

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

• 自关联

  • 子查询 比自关联慢

  • 查询最高的男生信息
    select * from students where height=(select max(height) from students);