数据库笔记(2)SQL查询语句大全

一、数据的准备

--创建一个数据库
create database python_test charset=utf8;
--使用一个数据库
use python_test;
--显示使用的当前数据是哪个？
select databases();  #报错，因为多加了s
select database(); 
--创建一个数据表
create table students(
    id int unsigned not null auto_increment primary key,
    name varchar(20) default '',
    age tinyint unsigned default 0,
    high decimal(5,2),
    gender enum("男","女","中性","保密") default "保密",
    cls_id int unsigned default 0,
    is_delete bit default 0
       );

create table  classes(
     id int unsigned auto_increment primary key not null,
     name (30)
     );
--查看一下是否创建成功
show tables;
show create table students;

--向students表中插入数据
insert into students values
(0,"小明",18,180.00,2,1,0),
(0,"小月月",18,180.00,2,2,1),
(0,"彭于晏",29,185.00,1,1,0),
(0,"刘德华",59,175.00,1,2,1),
(0,"黄蓉",38,160.00,2,1,0),
(0,"凤姐",28,150.00,4,2,1),
(0,"王祖贤",18,172.00,2,1,1),
(0,"周杰伦",36,null,1,1,0),
(0,"程坤",27,181.00,1,2,0),
(0,"刘亦菲",25,166.00,2,2,0),
(0,"金兴",33,162.00,3,3,1),
(0,"静香",12,180.00,2,4,0),
(0,"郭靖",12,170.00,1,4,0),
(0,"周杰",34,176.00,2,5,0);
--向classes中插入数据
insert into classes values (0,"python_01级"),(0,"python_02级"),(0,"python_03级");

二、查询

2.1普通 查询

--查询所有字段
--select * from 表名;
select * from students;
select * from classes;
select  id,name from classes;

--查询指定字段
--select 列1，列2，...from 表名;
select name,age from students;

--使用as为字段指定别名
--select 字段 as 名字 from 表名;
select name as 姓名,age as 年龄 from students;
 
 --select 表名.字段 ... from 表名;
 select students.name,students.age from students;
 
 --通过as 给表名起字段
  --select 别名.字段 ... from 表名 as 别名;
 select s.name,s.age from students as s;
 -失败的select students.name,students.age from students as s;
 
--消除重复行
可以用于显示唯一值
--distinct 字段
select distinct gender from students;

2.2条件查询

比较运算符
--select...from 表名 where .....
-->=大于等于；    <=小于等于;   !=不等于
select * from students where age>=18;
select  id,name,gender from students where age>=18;

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

-or
--18以上或身高过180(包含)以上的
select * from students where age>18 or height>=180;

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

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

模糊查询 like
-%替换一个或多个
-_替换一个
--查询姓名中   以“小”开始的名字
select name from students where name like "小%";
--查询姓名中   有“小”的所有的名字
select name from students where name like "%小%";
--查询是两个字的名字
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)表示在一个非连接的范围内
--查询年龄为12,18、34的姓名
select name,age from students where age=18 or age=34 or age=12;
select name,age from students where age in (12,18,34);

--not in不非连续的范围之内
--年龄不是12,18,34岁时间的信息
select name,age from students where age not in (12,18,34);

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

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

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

2.3 排序

-order by 字段
-asc从小到大排列，升序,默认升序
-desc从大到小排序，降序
--查询年龄18到34之间的男性，按年龄从小到大排序
select * from students where (age between 18 and 34) and gender=1;
select * from students where (age between 18 and 34) and gender=1 order by age asc;

--查询年龄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,age asc;
--查询年龄在18到34之间的女性，按身高从高到矮排序，如果身高相同情况下按照年龄从小到大排序,如果年龄也相同按id从大到小排序
select * from students where (age between 18 and 34) and gender=2 order by high desc,age asc,id desc;

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

2.4聚合函数

2.4.1 聚合

-总数  count
--查询男性有多少人，女性有多少人
select count(*) from students where gender=1;
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(high) from students where gender=2;
-求和  sum
--计算所有人的年龄总和
select sum(age) from students;
-平均值  avg
--计算平均年龄
select avg(age) from students;
--计算平均年龄  sum(age)/count(*)
select sum(age)/count(*) from students;
-四舍五入 round(123.23,1)--> 保留一位小数
--计算所有人的平均年龄，保留2位小数
select round(sum(age)/count(*),2) from students;
--计算男性的平均身高，  保留两位小数
select round(svg(high),2) from students where gender=1;

2.4.2 分组 （group by）

--按照性别分组，查询所有的性别
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) from students 
where gender=1 group by gender;
select gender,group_concat(name,age,id) from students 
where gender=1 group by gender;
select gender,group_concat(name,"_",age,"_",id) from students 
where gender=1 group by gender;

-having!!!!是对分组进行条件判断!!!where是对原表进行条件判断
--查询平均年龄超过30岁的性别，以及姓名   having avg(age)>30
select gender,group_concat(name) from students 
group by gender having avg(age)>30;

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

group_concat()用法

2.5分页

-limit start,count

--限制查询出来的数据个数
select * from students where gender=1 limit 2;
--查询前五个数据
select  * from students limit 0,5;
--查询 id6-10（包含）的书序
select  * from students limit 5,5;

so！--->>limit (第N页-1)*每页的个数,每页的个数;

--每页显示2个，显示第6页的信息，按照年龄从小到大排序
--失败select * from students limit 2*(6-1),2;--->因为limit 2*(6-1)不可
--失败select * from students limit 10,2 order by age asc;--->因为limit要放最后
select * from students order by age asc limit 10,2;

2.6连接查询

-内连接  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;
--按照要求显示姓名，班级##上面c.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 s.*,c.name from students as s 
inner join classes as c on s.cls_id=c.id;
--在以上查询中，将班级姓名显示在第1列
select c.name,s.* from students as s 
inner join classes as c on s.cls_id=c.id;

查询 有能够对应班级的学生以及班级信息，按照班级进行排序
--select c.xxx  s.xxx from student as s 
--inner join classes as c on ... order by ...;
select c.name,  s.* from students as s 
inner join classes as c on s.cls_id=c.id 
order by c.name;
--当是同一个班级的时候，按照学生的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 xxx  left join xxx  on ...where ...
--select  ... from xxx  left join xxx  on ...having ...
select * from students as s 
left join classes as c on s.cls_id=c.id 
having c.id is null;
select * from students as s 
left join classes as c on s.cls_id=c.id 
where  c.id is null;

2.7 自关联

首先创建一个表t_d_areainfo，导入全国省市区行政编码，下面进行自关联查询

创建表：
CREATE TABLE `t_d_areainfo` (
 `id` int(11) NOT NULL,
 `name` varchar(48) NOT NULL DEFAULT '' COMMENT '名称',
 `arealevel` tinyint(2) NOT NULL DEFAULT '0' 
 COMMENT '层级标识： 1  省份， 2  市， 3  区县',
 `p_id` int(11) DEFAULT NULL COMMENT '父节点',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='区县行政编码字典表';

--查询所有省份
select * from t_d_areainfo where p_id is null ;
-查询安徽省--下属市--下属区
select id from t_d_areainfo where name="安徽省"; 
--得到安徽省的id 为340000,继续查询得到下属市
select * from t_d_areainfo where p_id=340000;
--得到芜湖市的id为 340200，继续查询得到下属区
 select * from t_d_areainfo where p_id=340000;

直接查询安徽省的市
select province.name,city.name from  t_d_areainfo as province 
inner join t_d_areainfo as city on city.p_id=province.id 
having province.name="安徽省";
也可以
select province.name,city.name from  t_d_areainfo as province 
inner join t_d_areainfo as city on city.p_id=province.id 
having province.name="芜湖市";

2.8子查询

在一个select语句中，嵌入另外一个select语句，那么被嵌入的select语句称为子查询语句

主查询：主要查询的对象，第一条select语句

主查询和子查询的关系

• 子查询是嵌入到主查询中
• 子查询是辅助主查询的，要么充当条件，要么充当数据源
• 子查询可以是独立存在的语句，也可以是一条完整的select语句

select * from students where high =(select max(high) from students);

pandas