sql查询

语句查询
# 查询指定字段
# 只查询name和age两个字段
    语法：>select 字段名1,字段名2 from 表名;
    案例：>select name,age from stu;
    案例：>select stu.name,stu. age from stu;

# 起别名
# 将字段名改成中文名 仅对当前一条语句起作用，不会改变原数据库
    语法：>select 字段名 as 别名,字段名 as 别名 from 表名;
    案例：>select name as 姓名,age as 年龄 from stu;

# 给表起别名
语法：>select 字段 from 表名 as 别名;
    案例：>select s.name,s.age from stu as s;
    案例：>select name,age from stu as s; 

# 查看所有性别（实现去重）
    语法：>select distinct 性别 from 表名;
    案例：>select distinct gender from stu;


条件查询
# >  查询大于18岁的信息
    select * from stu where age>18;
# <  查询小于18岁的信息
    select * from stu where age<18;
# =  查询年龄等于18岁的信息
    select * from stu where age=18;
# >=  查询大于等于18岁的信息
    select * from stu where age >=18;
# <=  查询小于等于18岁的信息
    select * from stu where age <=18;
# !=或者<>  查询不等于18岁的信息
    select * from stu where age != 18;
    select * from stu where age <> 18;


逻辑运算符
# and 查询18-28之间的信息
    select * from stu where age>18 and age <28; 
# 查询18岁以上的女性
    select * from stu where age>18 and gender = "女";
    select * from stu where age>18 and gender = 2;
# or 18岁以上或者身高超过180的
    select * from stu where age>18 or height>180.00;
# not   查询不是18岁以上的女性
    select * from stu where not (age>18 and gender=2);
# 年龄不是小于等于18的并且是女性
    select * from stu where not age<=18 and gender =2;


几种常见查询
1).等值查询:
    select * from stu where id=1;
    查询id为1的学生的所有字段信息
2).条件比较查询:
   select * from stu where id > 3;
    查询id大于3的所有学生的所有字段信息
3).比较运算与逻辑运算查询:
   select * from stu where id > 5 and height >170;
    查询id大于5并且身高大于170的所有学生的信息
    

模糊查询  like
--like
%可以匹配0个或无数个
# 查询姓名中以"小"开始的名字
    select * from stu where name like"小%";
# 查询姓名中有"小"的名字
    select * from stu where name like"%小%";
# 查询两个字的名字 _匹配1个
    select * from stu where name like"__";
# 查询有三个字的名字
    select * from stu where name like"___";
# 查询至少有2个字的名字
    select * from stu where name like"__%";


正则查询
# 查询以周开始的
    select * from stu where name rlike"^周.*$";
# 查询以周开始的 以伦结束的
    select * from stu where name rlike"^周.*伦$";


范围查询
in 在……里  查询不连续的数据
not in 不在……里
between a and b 查找a到b之间的范围 包含a和b
not between a and b 查找不在a到b之间的范围

# 查询年龄为18、34之间的信息
    select * from stu where age in(18,34);
# 查询年龄不在18、34之间的信息
    select * from stu where age not in(18,34);
# 查询年龄在18-34之间的信息
    select * from stu where age between 18 and 34;
# 查询年龄不在18-34之间的信息
    select * from stu where age not between 18 and 34;


空判断
# 查询身高为空的信息
    select * from stu where height is null;

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


常用查询（二）
4).between...and的使用
   select * from stu where height between 170 and 180;
    # 注意: between  a  and  b: 包含a和b
    查询身高在170都180之间的所有学生信息
5).成员运算:
    select * from stu where id in (1, 2, 3, 4);
    查询id值为1, 2, 3, 4的所有学生信息
6).空与非空判断:
    select * from stu where height is null;
    查询身高数据为空的所有学生信息
    select * from stu where height is not null;    
    查询身高数据不为空的所有学生信息
    
    
排序   order by
order by 不写默认升序
asc 从小到大即升序
desc 从大到小即降序

使用多个字段来进行排序，只需要用逗号隔开，不需要写多个order by

# 查询年龄在18-34岁之间的男性
    select * from stu where (age between 18 and 34) and gender=1;

# 查询18-34岁之间的女性并且身高按照由高到低排序
    select * from stu where (age between 18 and 34) and gender=2 order by age desc;

# 查询年龄在18-34之间的女性，身高从高到低排序，如果身高相同的情况下按照年龄从小到大排序
    select * from stu where (age between 18 and 34) and gender=2 order by height desc,age asc;
# 查询年龄在18-34之间的女性，身高从高到低排序，如果身高相同的情况下按照年龄从小到大排序，如果年龄相同按照id从大到小排序
    select *  from stu where (age between 18 and 34) and gender=2 order by height desc,age asc,id desc;
# 年龄从小到大 身高从大到小排序
    select * from stu order by age asc,height desc;


CONCAT_WS()是 MySQL 数据库中的一个字符串函数，用于将多个字符串按照指定的分隔符连接起来，返回一个新的字符串。
GREATEST()  是 MySQL 中的一个聚合函数，它可以用于返回一组给定数值中的最大值。

IF(condition, value_if_true, value_if_false)

其中，condition是一个条件表达式，如果为真，函数返回value_if_true，否则返回value_if_false。

在这个语句中，condition的值是：

a=GREATEST(a,b,c,d)

这个条件表达式的含义是：如果a的值等于a、b、c、d中的最大值，则为真；否则为假。

SELECT id, GREATEST(a, b, c, d) AS max_value,
  CONCAT_WS(',', 
    IF(a=GREATEST(a,b,c,d),'a',NULL), 
    IF(b=GREATEST(a,b,c,d),'b',NULL), 
    IF(c=GREATEST(a,b,c,d),'c',NULL), 
    IF(d=GREATEST(a,b,c,d),'d',NULL)
  ) AS max_fields
FROM test2;



聚合函数的使用
# max()   
# 查询学生中最大的年龄
    select max(age) from stu; 

# min()  
# 查询学生中最小的年龄
    select min(age) from stu;

# count()
# 查询学生总数
    select count(*) from stu;

# sum()  
# 查询学生年龄总和
    select sum(age) from stu;

# avg()
# 查询所有学生年龄的平均值
    select avg(age) from stu;

# round(小数,保留位数)
# 保留位数写0就是没有小数 写1就是1为小数，写2就是保留两位小数
# 计算平均身高保留2位小数
    select round(avg(height),2) from stu;

# 查找平均身高和所有人的名字
    select name,round(avg(height),2) from stu;
# 报错：因为平均身高只有一个但是姓名有很多个，无法匹配
# 来引出分组
 



分组与过滤
分组  group by
分组就是和聚合函数一起用，否则没有任何意义

# 以性别分组
    select gender from stu group by gender;
# 计算每种性别的人数
    select count(*),gender from stu group by gender;
# # 获取每种性别都有谁，统计名字
    select gender,group_concat(name) from stu group by gender;
# 获取每种性别的名字和id都展示出来
    select gender,group_concat(id,name) from stu group by gender;
    select gender,group_concat(id,"-",age,"-",name) from stu group by gender;
# 计算男性的人数
    select count(*) from stu where gender=1;
# 统计男性的人数和姓名
    select gender,count(*),group_concat(name) from stu where gender=1;
# 每种性别的平均年龄
    select gender,avg(age) from stu group by gender;
    
    group_concat():将分组的结果连接在一起,但不是一定要跟分组一起用

过滤    having
对分组的结果进行过滤  所以用在group by 后面

# 查询每种性别的平均年龄
    select gender,avg(age) from stu group by gender;
# 查询平均年龄  查询超过30岁的性别及姓名
    select gender,avg(age),group_concat(name) from stu group by gender having avg(age)>30;
# 查询每种性别人数多2个的信息
    select gender,count(*) from stu group by gender having count(*)>2;


分页 limit
# limit num  代表展示num条数据
    select * from stu limit 5;

# 展示前三条性别为男的信息
    select * from stu where gender=1 limit 3;

# limit start,count
# start代表起始值  count代表每一页要展示的数据个数
    select * from stu limit 0,5;    # id 从1-5
    select * from stu limit 5,5;    # id 从6-10
    select * from stu limit 10,5;   # id 从11-15

# 起始值的规律
    (n-1)*每页的个数
    第一页  (1-1)*5=0
    第二页  (2-1)*5=5
    第三页  (3-1)*5=10


# 每一页展示3个数据  查询第三页的所有的数据  按照年龄从小到大排序 
(3-1)*3=6
    select * from stu order by age asc limit 6,3;
  
# 关键字之间是有优先级的存在  order by > limit 


关键字排序
    select:查找数据
    from:从哪一张表里获取数据
    where:查找数据时需要的条件

# 不能改变的关键字
    select from where order by group by having limit

# 关键字执行顺序
    from > where > group by > having > select > order by >limit
    
    from:找到表
    where:选择条件
    group by:分组
    having:对分组后的结果进行筛选
    select:获取数据
    order by:对数据进行排序
    limit:先排序 在进行最后一次的取值


# 写sql语句的顺序
    select * from 表名 where 条件 order by 排序 group by 分组 having 筛选 limit 分页




## 连接查询

连接查询

概念：当查询的字段来源于多张表时我们使用的就是连接查询

分类：

内连接

外连接

​  左外连接

​  右外连接

交叉连接



##### 内连接

概念

内连接查询出两张表中交叉重合的数据,共有的部分

表1的字段放在左边

语法：select 字段 from 表1 inner join 表名2 on 连接条件;

连接条件：两张表产生关系的字段

案例：

让学生表和班级表进行内连接查询的所有的字段

select * from student inner join class on student.cls_id=class.id;

 select * from class inner join student on class.id = student.cls_id;

查询指定字段数据

案例：

如果两张表里有相同的字段 ，查询的时候需要标明清楚具体是哪一张表里的字段 表名.字段名

select name,cls_name from student inner join class on student.cls_id=class.id;

给表起别名 表名 as 别名

如果起了别名就要用到别名

 select a.name,b.cls_name from student as a inner join class as b on a.cls_id = b.id;

案例：

查询班级名称 学生id 学生姓名 学生年龄 学生性别，按照从大到小排序,如果年纪相同按照id从小到大排序;

注意：

多表联查不影响分组 排序 过滤 聚合函数 分页 关键字的使用

select a.id,a.name,age,gender,b.cls_name from student as a inner join class as b on a.cls_id = b.id order by age desc,id;

案例：

查询不同性别的人数，姓名以及对应的班级名称

select gender,count(*),group_concat(a.name,"-",b.cls_name) from student as a  inner join class as b on +a.cls_id=b.id group by gender;

##### 自关联

概念

一张表   自己关联自己

案例

 insert into area values(1,"河南省",null),
    -> (2,"山东省",null),
    -> (3,"山西省",null),
    -> (4,"郑州市",1),
    -> (5,"洛阳市",1),
    -> (6,"菏泽市",2),
    -> (7,"运城市",3),
    -> (8,"中牟县",4),
    -> (9,"汝阳县",5),
    -> (10,"曹县",6);

先找河南的id

select id from area where name="河南省";

再用河南的id 匹配 下面市的pid

 select * from area where pid=1;

自关联  本质就是内连接  一定要用到别名

select * from area as a  inner join area  as b  on a.id = b.pid  where a.name="河南省";

##### 外连接

###### 左外连接

作用：

left join on

把表1作为主表 如果关联表没有对应的信息就用null填充

语法：select 字段 名 from 表1 left join  表2 on 连接条件;

把学生表当作表1 学生表里的数据都存在  如果班级表没有对应值  会用null填充

案例：select * from student left join class on student.cls_id=class.id;

把班级表当作表1 班级表的数据都存在 如果学生表中没有对应的值 会用null填充

案例：select * from class left join student  on class.id=student.cls_id;

###### 右外连接

作用

right join on

表2当作主表 如果关联表没有对应的信息就用null填充



语法案例：

语法：select 字段 from 表1 right join 表2 on 连接条件;

表2是主表  表1不对应的数据用null对应

案例：select * from student right join class  on student.cls_id =class.id;

select * from class right join student on class.id=student.cls_id;

## 外键约束

###### 作用：

外键约束：对外键字段的值进行更新和插入数据时会引用表中字段的数据进行验证，如果数据不合法则更新或插入失败，保证数据的有效性

外键约束在哪一张表里，哪一张表就是从表

因为有外键约束，导致数据不能随便插入，要按照规则添加

###### 添加外键方式：

在创建表的时直接添加外键约束

语法：

foreign key(字段名)  references  表名(字段名)

给已经创建好的表增加外键约束

语法：alter table 表名 add foreign key(字段名)  references 表名(字段名)

案例：alter table b add foreign key(a_id) references a(id);

注意：

如果表中已经存在不合法数据那么就会添加外键失败

被约束和约束字段的类型要保持一致

否则报3780错误

前面被约束后面是谁来进行约束,

约束谁就在谁那里添加外键约束



###### 删除外键

找到表中的外键



查看创建表结构：

语法：show create table 表名;

案例：show create tabl    e  b;

找到系统随机生 成外键字段名(b_ibfk_1)双引号里的

删除语法：

语法：alter table 表名 drop foreign key 字段名;

案例：alter table b drop foreign key b_ibfk_1;

## 子查询

概念

在一个select语句中在嵌入另一个select语句

select  * from student;

标量子查询：返回的结果是一个数据(一行一列)

列子查询：返回的结果是一列(一列多行)

行子查询：返回的结果是一行(一行多列)

查询班级身高最高的信息

select * from stu where height=(select max(height) from stu);

查询年龄最大的信息

select * from stu where age=(select max(age) from stu);

#查询所有有学生的班级 显示班级名称和学生姓名
select cls_name,name from class inner join student on class.id=student.cls_id;

#子查询   也能用其他关键字

###### in

​  #找到有学生的班级班级的名字和学生的名字
​  select cls_name from class where id in (select cls_id from student);

select name from student where cls_id in (select id from  class);

###### some、any     作用一样   任一

查询年龄大于  身高大于175的人的年龄  的人的信息#身高大于175 的人的年龄

select age from student where height>175;

#报错  more than 1

#需要给予查询语句增加一个any条件查询出  年龄比(身高大于175的人的年龄) 中 任意一个大的都可以

select * from student where age>any(select age from student where height>175);

select * from student where age>some(select age from student where height>175);

###### all 所有

#查询年龄比  身高大于175的人的年龄  中所有年龄的人的信息
select age from student where height>175;
select * from student where age>all(select age from student where height>175);

#查询  身高大于   (年龄小于30岁的身高) 中所有人的身高才行
select * from student where height>all(select height from student where age<30);

###### exists

只要子查询语句有结果  就会执行主查询语句
   如果子查询语句没有结果 就不会执行主查询语句
#只要表中有身高有高于185的人，那就执行查询出表中所有人的信息
select * from stu where exists(select * from stu where height>185);

#年龄大于50岁的人的信息   查找出来是所有人的信息
select name,age from stu where exists(select * from stu where age>50);

## 索引

概念

是一种特殊文件，它包含着对数据库中所有记录的引用指针，好比一本书的目录，加快查询速度

索引原理以及作用

原理：通过不断缩小范围来筛选结果

作用：加快查询速度

###### 索引分类

mysql常见索引

普通索引   index

唯一索引

unique index

primary key

联合索引

unique(id,name)

全文索引

fulltext  ---用于搜索一篇很长的文章

空间索引

spatial  了解即可

###### 索引创建

如果给字符串指定长度，这时候如果索引创建在这个字符串上，索引也需要加长度

第一种方式  在创建表时直接创建索引(索引名跟字段名一样)

语法：create table 表名(字段名 类型 [约束],索引(字段()))

案例：create table test_01(title varchar(20),unique(title(10)));

第二种方式  在已经创建好的表中创建索引(可以自己指定索引名)

语法：create 索引 index 索引名  on 表名(字段名())

案例：create unique index t2  on test_01(title(10));

创建普通索引

案例：create index  t1 on test_01(title(10));

第三种方式  在已经创建好的表中创建索引(索引名跟字段名一样)

语法：alter table 表名 add 索引(字段());

案例：alter table test_01 add unique(title(10));

altere table test_01  add index(title(10));

###### 查询索引

语法：show index from 表名;

案例：show index from test_01;

key name 对应的值就是索引名

###### 删除索引

语法：drop index 索引名 on 表名;

案例：drop index title on test_01;

###### 百万数据实战

开启时间检测

set profiling=1;

查看执行时间

show profiles;

## 封装

###### 思路



目的：封装一个模块

功能：可以快捷的操作mysql中的功能

分析：

确定单独的方法---(想要实现的功能)

确定公共的内容---(连接数据库、创建游标对象)

更进一步

初始化

pymysql使用之前

1. 先连接mysql，获取连接对象(数据库对象)

2. 通过连接对象，获取游标对象

具体的方法中如何执行

1. 直接执行

2. 先执行，再提交 (对数据进行增删改操作的时候，不仅仅是游标对象执行sql语句，连接对象还得向上提交才行)