Mysql - 02 mysql数据库"查询"select相关命令

数据库的准备:

-- 创建一个数据库
create database python_test charset=utf8;

-- 使用一个数据库
use python_test;

-- 显示使用的当前数据是哪个?
select database();

-- 创建一个数据表
-- students表
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
);

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

添加数据:

insert into students values(0,"小明",18,188,"男",1,0)
insert into classes values(0,一班)
...
-- 添加若干个

普通查询:

-- 查询所有:
select * from students;
-- ---------------------------------------
-- 查询指定:
select id,name from classes;
-- ---------------------------------------
-- 起别名:
select name as 名字,age as 年龄 from classes;
select students.name,students.age from students;
select s.name,s.age from students as s;
-- ---------------------------------------
-- 消除重复行:
select distinct gender from students;

条件查询

-- 比较运算符(>,<,>=,<=,=,!=,<>):
select * from students where age>18;
select name from students where age=18;
-- ---------------------------------------
-- 逻辑运算符(and,or,not):
select * from students where age>18 and age<28;
select * from students where age>18 or heigh>=170;
select * from students where not (age>18 and gender=2);
select * from students where (not age>18) and gebder=2;
-- ---------------------------------------
-- 模糊查询:
-- like:
-- %替换0个或多个
-- _替换1个
select * from students where name like "小%";
select name from students where name like "__";

-- relike正则:
select name from students where name relike "^周.*";
select name from students where name relike "^周.*伦";
-- ---------------------------------------
-- 范围查询(in,not in,between...and...,not between...and...):
select name,age from students age in (18,20,25);
select name,age from students age not in (18,20,25);
select name,age from students age between 18 and 30;
select name,age from students age not between 18 and 30;
-- ---------------------------------------
-- 空判断:
select name,height from students height is null;
select name,height from students height is not null;

排序

-- 生序,从小到大(asc):
select * from students where (age between 18 and 34) and gender=2 order by age asc;
-- 降序,从大到小(desc):
select * from students where (age between 18 and 34) and gender=2 order by age desc;
-- ---------------------------------------
-- order by 多字段(第一个相同,按第二个...):
select * from students where (age between 18 and 34) and gender=2 order by age desc,id desc,height asc;
select * from students order by age asc,height desc;

聚合函数

-- 总数count:
select count(*) from students where gender=1;
select count(*) as 男性人数 from students where gender=1;
-- ---------------------------------------
-- 最大值max:
select max(age) from students;
select max(age) from students where gender=1;
-- ---------------------------------------
-- 最小值min:
select min(age) from students;
-- ---------------------------------------
-- 求和sum:
select sum(age) from students;
-- ---------------------------------------
-- 平均值avg:
select avg(age) from students;
select sum(age)/count(*) from students;
-- ---------------------------------------
-- 四舍五入round(123.456,1):123.456保留1位小数,四舍五入
select round(avg(age),2) from students;

分组

-- group by:
-- 按性别分组:
select gender from students group by gender;
-- 对分组结果计算数目:
select gender,count(*) from students group by gender;
-- 对分组前的数据进行过滤(where):
select gender,count(*) from students where gender=1 group by gender;
-- ---------------------------------------
-- group_concat():
-- 按gender分组后,各组name成员有谁:
select gender,group_concat(name) from students group by gender;
-- 组合显示name,age,id:
select gender,group_concat(name,age,id) from students group by gender;
-- 组合显示name,"_",age, ,id:
select gender,group_concat(name,"_",age, ,id) from students group by gender;
-- ---------------------------------------
-- having对分组后的各组按条件过滤:
select gender,group_concat(name),avg(age) from students group by gender having avg(age)>30;

分页

-- limit:
-- 查询前五个数据:
select * from students limit 5;
-- 查询从第1个开始共5数据(计数从0开始):
select * from students limit 0,5;
-- 复杂代码limit位置在最后:
select * from students where (age between 18 and 34) and gender=2 order by age desc,id desc,height asc limit 0,5;

-- 使用limit分页(每页显示5个):
-- 第一页:
select * from students limit 0,5;
-- 第二页:
select * from students limit 5,5;
-- 第三页:
select * from students limit 10,5;
-- 第四页:
select * from students limit 15,5;
-- 第五页:
select * from students limit 20,5;
-- 第六页:
select * from students limit 25,5;

连接查询

内连接(只保留两表交集部分)
-- 内连接inner join ... on:
-- select ... from 表A inner join 表B
-- 将两个表连接在一起:
select * from students inner join classes
-- 加入条件students.cls_id=classes.id过滤:
select * from students inner join classes on students.cls_id=classes.id;
-- 对上述结果优化,不显示clesses.id
select students.*,clesses.name from students inner join classes on students.cls_id=classes.id;
-- 只显示学生名字,班级名字:
select students.name,clesses.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 students.cls_id=classes.id;

-- 结果排序:
select s.name,c.name from students as s inner join classes as c on students.cls_id=classes.id order by c.name;
外连接(以偏向表为基础连接,无数据部分为null)
-- 左连接left join ... on:以students为基准
select * from students left join classes on students.cls_id=classes.id;
-- 查看没有班级的学生:having和where都可以
select * from students left join classes on students.cls_id=classes.id having classes.name is null;
select * from students left join classes on students.cls_id=classes.id where classes.name is null;
-- ---------------------------------------
-- 右连接right join ... on:以classes为基准
select * from students right join classes on students.cls_id=classes.id;
自连接(一张表的值关联的也是自己的表)
-- 省级联动
-- 表结构:三个列为aid,atitle,pid;pid与aid关联
-- 自关联查询:
select * from areas as province inner join areas as city on province.aid=city.pid having province.atitle="河北省"

子查询

-- 下列命令:
select max(height) from students;
select * from students where height=188;

-- 等价于:
select * from students where (select max(height) from students)
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值