3.mysql查询必备sql语句

阿里云国内75折 回扣 微信号：monov8
阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6

文章目录

1.条件查询 where

表内容
在这里插入图片描述
在这里插入图片描述

import pymysql

conn = pymysql.connect(host='127.0.0.1',port=3306,user='root',passwd='root',charset='utf8',db='db26')
cursor = conn.cursor()
# sql2 = """
# create table info(
#     id int not null auto_increment primary key,
#     name varchar(16) not null,
#     email varchar(32) not null,
#     age int,
#     depart_id int
# )default charset=utf8;
# """
# cursor.execute(sql2)
#conn.commit()
#sql = "select * from info where age>30"
# sql = "select * from info where id!=3"
#sql = "select * from info where id between 2 and 4"
#sql = "select * from info where (id between 2 and 4) and age>10"
#sql = "select * from info where id in (1,2,4)"
#sql = "select * from info where id not in (1,2,4)"
# sql = "select * from info where id in (select id from depart)"
# sql = "select * from info where not exists (select * from depart where id=5)"
#sql = "select * from info where exists (select * from depart where id=2)"
#sql = "select * from (select * from info where id>2)as T where age>10"
#sql = "select * from (select * from info where id>2) as T where age>10"


#sql = "select * from info where name like '%wu%'"
#sql = "select * from info where email like '%@live.com'"
sql = "select * from info where email like '__peiqi@live.co_'"
cursor.execute(sql)
result = cursor.fetchall()
#print(result)
for i in result:
    print(i)
cursor.close()
conn.close()

2. 通配符与模糊查询

% 代表任意个任意字符
_ 代表单个任意字符

select * from info where name like "%沛%";
select * from info where name like "%沛";
select * from info where email like "%@live.com";
select * from info where name like "武%齐";
select * from info where name like "k%y";
select * from info where email like "wupeiqi%";


select * from info where email like "_@live.com";
select * from info where email like "_upeiqi@live.com";
select * from info where email like "__peiqi@live.com";
select * from info where email like "__peiqi_live.co_";

import pymysql

conn = pymysql.connect(host='127.0.0.1',port=3306,user='root',passwd='root',charset='utf8',db='db26')
cursor = conn.cursor()
#sql = "select * from info where name like '%wu%'"
#sql = "select * from info where email like '%@live.com'"
sql = "select * from info where email like '__peiqi@live.co_'"
cursor.execute(sql)
result = cursor.fetchall()
#print(result)
for i in result:
    print(i)
cursor.close()
conn.close()

3. 映射

  • 映射是指什么查找的内容单独建一列
select * from info;

select id, name				from info;
select id, name as NM 		from info;
select id, name as NM, 123  from info;
注意少些select * ,自己需求。

select 
	id,
	name,
	666 as num,
	( select max(id) from depart ) as mid, -- max/min/sum  只能返回一个值max(id)是一个值
	( select min(id) from depart) as nid, -- max/min/sum
	age
from info;

select 
	id,
	name,
	( select title from depart where depart.id=info.depart_id) as x1
from info;

# 注意效率很低

select 
	id,
	name,
	( select title from depart where depart.id=info.depart_id) as x1,
	-- 顺序先查表info,取出id,name列再查当列的depart_id是否等于depart.id,AS后是重命名
	( select title from depart where depart.id=info.id) as x2
from info;

select 
	id,
	name,
	case depart_id when 1 then "第1部门" end v1
from info;

select 
	id,
	name,
	case depart_id when 1 then "第1部门" else "其他" end v2
	-- case语句 如果depart_id为1就写为第一部门否则就其他  该列为v2
from info;

select 
	id,
	name,
	case depart_id when 1 then "第1部门" end v1,
	case depart_id when 1 then "第1部门" else "其他" end v2,
	case depart_id when 1 then "第1部门" when 2 then "第2部门" else "其他" end v3,
	-- case语句 如果depart_id为1就写为第一部门如果是2 就是第2部门 否则就其他  该列为v2
	case when age<18 then "少年" end v4,
	case when age<18 then "少年" else "油腻男" end v5,
	case when age<18 then "少年" when age<30 then "青年" else "油腻男" end v6
	-- case语句 如果age<18就是少年age<30就是青年否则就其他 就是油腻 该列为v6
from info;

4. 排序 order_by

select * from info order by age desc; -- 倒序
select * from info order by age asc;  -- 顺序

select * from info order by id desc;
select * from info order by id asc;
select * from info order by age asc,id desc; -- 优先按照age从小到大如果age相同则按照id从大到小。


select * from info where id>10 order by age asc,id desc;
select * from info where id>6 or name like "%y" order by age asc,id desc;

5. 取部分 limit 和offset

select * from info limit 5;   										-- 获取前5条数据
select * from info order by id desc limit 3;						-- 先排序再获取前3条数据
select * from info where id > 4 order by id desc limit 3;			-- 先排序再获取前3条数据

select * from info limit 3 offset 2;	-- 从位置2开始向后获取前3数据

limit 配合offset可以用于分页操作比如在数据表内有1000条数据每页显示10个可以分为100页

- 第一页`select * from info limit 10 offset 0;`
- 第二页`select * from info limit 10 offset 10;`
- 第三页`select * from info limit 10 offset 20;`
- 第四页`select * from info limit 10 offset 30;

6. 分组 group by

sql = "select depart_id,max(age),min(age),avg(age),count(id),sum(age) from info group by depart_id" 
-- 查询 按照depart_id分组后 按照分组后的每组找出聚合函数后的内容
sql = "select depart_id,count(id) from info group by depart_id having count(id) > 2;"
-- 先分组 后having子查询
sql = "select count(id) from info"
sql = "select min(id) from info"
select * from info where id in (select max(id) from info group by age);
-- 先查找每个年龄的最大ID后查询这些ID的信息
select age,count(id) from info group by age having count(id) > 2;
-- 
select age,count(id) from info where id > 4 group by age having count(id) > 2;  
-- 聚合条件放在having后面

-- 一个最复杂的例子
select age,count(id) from info where id > 2 group by age having count(id) > 1 order by age desc limit 1;
- 要查询的表info
- 条件 id>2
- 根据age分组
- 对分组后的数据再根据聚合条件过滤 count(id)>1
- 根据age从大到小排序
- 获取第1条

7.左右连表 left outer join … on

主表 left outer join 从表 on 主表.x = 从表.id 
-- 左外连接时左边的表是主表根据某个条件进行连表利用了一个主表和一个从表
select * from info left outer join depart on info.depart_id = depart.id;
sql = "select info.id,info.name,info.email,info.age,info.depart_id,depart.title from info left outer join depart on info.depart_id=depart.id"
-- 左连接并显示指定字段
sql = "select * from info right outer join depart on info.depart_id=depart.id"
-- 右连接 右边是主表  左边是从表
sql = "select * from info right join depart on info.depart_id=depart.id"
-- 谁是主表 就要展示谁为主没有对应的展示那些元素为null主要使用的是左外连接
select * from info left outer join depart on ....
select * from depart left outer join info on ....
简写  可以去掉outer  默认是外连接

内连接表 inner join 表 on 条件

互相匹配只有两者能关联上的数据才会显示没有关联的数据就不会显示
select * from info inner join depart on info.depart_id=depart.id;

8. 联合查询 union

  • 又称为上下连表 很少用到可以用于多张表联合去重
select id,title from depart 
union
select id,name from info;


select id,title from depart 
union
select email,name from info;
-- 列数需相同
-- 数据类型不一致没问题

select id from depart 
union all
select id from info;

-- 保留所有查询时不会自动去重

到目前为止SQL执行顺序
    join/union
    on 
    where 
    group by
    having 
    order by
    limit
阿里云国内75折 回扣 微信号：monov8
阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6
标签: mysql
返回列表

上一篇：爱普生Epson WF110彩色无线便携式打印机错误E-12维护箱已到使用寿命

下一篇：[HDCTF2019]Maze 题解

“3.mysql查询必备sql语句” 的相关文章

学校图书借阅管理系统(MySQL)1年前 (2023-02-02)
CentOS7安装 c++ +jdk+Tomcat+MySQL虚拟机安装教程附百度云资料1年前 (2023-02-02)
MySQL-慢sql优化思路1年前 (2023-02-02)
MySQL子查询篇（精选20道子查询练习题）1年前 (2023-02-02)
mysql查看、修改数据库字符集1年前 (2023-02-02)
【MySQL】InnoDB存储引擎的行结构1年前 (2023-02-02)
PHP 创建 MySQL 表1年前 (2023-02-02)
MySQL学习之一条SQL更新语句的执行1年前 (2023-02-02)
Sentinel + Redis + Mysql + RabbitMQ 秒杀功能设计及后端代码实现1年前 (2023-02-02)
Mysql1年前 (2023-02-02)

阿里云国际版