文章目录
MySQL中的多表查询是数据库操作中非常常见且重要的一个环节,多表查询是数据库查询中非常强大的功能,能够让你根据需要从多个表中提取和组合数据,它允许你根据需要在多个表之间联合数据,掌握这些基本的连接类型对于进行复杂的数据库操作至关重要。
多表查询
-
准备sql:
-- 创建部门表 CREATE TABLE dept( id INT PRIMARY KEY auto_increment, NAME VARCHAR(20) ); -- 插入数据 INSERT INTO dept(NAME) VALUES ('开发部'),('市场部'),('财务部'); -- 创建员工表 CREATE TABLE emp( id INT PRIMARY KEY auto_increment, NAME VARCHAR(10), gender CHAR(1), -- 性别 salary DOUBLE, -- 工资 join_date DATE, -- 入职日期 dept_id INT, FOREIGN KEY(dept_id) REFERENCES dept(id) -- 外键,关联部门表的主键 ); -- 插入数据 INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES ('孙悟空','男',7200,'2013-02-24',1), ('猪八戒','男',3600,'2010-12-02',2), ('唐僧','男',9000,'2008-08-08',2), ('白骨精','女',5000,'2015-10-07',3), ('蜘蛛精','女',4500,'2011-03-14',1);
-
笛卡尔积:
有两个集合A,B 取这两个集合的所有组成情况。
例如:
A:(a,b,c)
B:(1,2,3)
A与B作笛卡尔积—> a,1 a,2 a,3 b,1 b,2 b,3 c,1 c,2 c,3
要完成多表查询,需要消除无用的数据
-
1.内连接查询:
-
隐式内连接:使用where条件消除无用数据
-- 查询所有员工信息和对应的部门信息 SELECT * FROM emp,dept WHERE emp.dept_id=dept.id; -- 查询员工表的名称,性别。部门表的名称 SELECT emp.`NAME` ,emp.gender ,dept.`NAME` FROM emp,dept WHERE emp.dept_id=dept.id; -- 或者 SELECT t1.`NAME` ,t1.gender ,t2.`NAME` FROM emp t1,dept t2 WHERE t1.dept_id=t2.id;
-
显式内连接:
--语法:select 字段列表 from 表名1 [inner] join 表名2 on 条件 SELECT * FROM emp INNER JOIN dept ON emp.dept_id=dept.id;
-
-
2.外连接查询:
-
左外连接:
--语法:select 字段列表 from 表1 left [outer] join 表2 on 条件; --查询的是左表所有数据以及其交集部分。
-
右外连接:
--语法:select 字段列表 from 表1 right [outer] join 表2 on 条件; --查询的是右表所有数据以及其交集部分。
-
-
3.子查询:
查询中嵌套查询,称嵌套查询为子查询。
-- 查询工资最高的员工信息 -- 1.查询最高的工资是多少 9000 SELECT MAX(salary) FROM emp; -- 2.查询员工信息,并且工资等于9000的 SELECT * FROM emp WHERE emp.salary=9000; -- 一条sql就完成这个操作 SELECT * FROM emp WHERE emp.salary = (SELECT MAX(salary) FROM emp);
子查询不同情况:
-
子查询的结果是单行单列的:
--子查询可以作为条件,使用运算符去判断。 运算符:> >= < <= = --查询员工工资小于平均工资的人 SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);
-
子查询的结果是多行单列的:
--子查询可以作为集合,使用in、not int --查询财务部和市场部所有员工信息 SELECT id FROM dept WHERE `NAME`='财务部' OR `NAME`='市场部'; SELECT * FROM emp WHERE dept_id=3 OR dept_id=2; --使用子查询 SELECT * FROM emp WHERE dept_id in (SELECT id FROM dept WHERE `NAME`='财务部' OR `NAME`='市场部');
-
子查询的结果是多行多列的
--子查询可以作为一张虚拟表参与查询 --查询员工入职日期是2011-11-11日之后的员工信息和部门信息 -- 子查询 select * from dept t1 (select * from emp where emp.join_date > '2011-11-11') t2 where t1.id = t2.dept_id; --普通内连接查询 select * from emp t1,dept t2 where t1.dept_id = t2.id and t1.join_date > '2011-11-11'
-
-
表的拼接
拼接时两张表的结构必须完全一致
- union 对数据进行去重
- union all
MySQL函数
数字函数
-
CEIL(x)/CEILING(x) 向上取整
select ceil(column_name) from table_name;
-
FLOOR(x) 向下取整
select floor(column_name) from table_name;
-
POW(x,y)/POWER(x,y) 返回 x 的 y 次方
select name,age,POW(age,2) from students;
-
RAND() 返回 0 到 1 的随机数
select name,age,rand() from students;
-
ROUND(x) 返回离 x 最近的整数
-- round(x,d) x保留d位小数 select clazz ,round(avg(age),2) as avg_age from students group by clazz having avg(age)>22.5 -- round(x) 返回离x最近的小数 select clazz ,avg(age) ,round(avg(age)) as avg_age from students group by clazz
字符串函数
-
LENGTH 字节数 CHAR_LENGTH(s)/CHARACTER_LENGTH(s)返回字符串 s 的字符数
select clazz,LENGTH(clazz),CHAR_LENGTH(clazz),CHARACTER_LENGTH(clazz) from students; select LENGTH('wyl'),CHAR_LENGTH('wyl');
-
CONCAT(s1,s2…sn)字符串 s1,s2 等多个字符串合并为一个字符串
-- 注意: 如果有一个字符串为空 则结果为空 select *,CONCAT(name,',',email,',',password) from users;
-
CONCAT_WS(separator, str1, str2, …)指定分隔符的拼接
-- 可以指定分割符 -- 遇到为null的值 结果合并会忽略他 select *,CONCAT_ws('#',name,email,password) from users;
-
SUBSTR/SUBSTRING(s, start, length)从字符串 s 的 start 位置截取长度为 length 的子字符串
select *,SUBSTR(clazz,1,2) from students
-
STRCMP(s1,s2)比较字符串 s1 和 s2,如果 s1 与 s2 相等返回 0 ,如果 s1>s2 返回 1,如果 s1<s2 返回 -1
-- 返回值是0 则字符串相等 select *,STRCMP(clazz,'文科六班\r') from students
日期函数
-
CURDATE()/CURRENT_DATE()返回当前日期
SELECT CURRENT_DATE,CURRENT_TIME,CURRENT_TIMESTAMP
-
CURRENT_TIME()/CURTIME()返回当前时间
-
CURRENT_TIMESTAMP()返回当前日期和时间
-
DATEDIFF(d1,d2)计算日期 d1->d2 之间相隔的天数
SELECT DATEDIFF('2024-05-07','1908-12-04')
-
TIMEDIFF(time1, time2)计算时间差值
select TIMEDIFF('10:58:00','09:00:00')
-
UNIX_TIMESTAMP()得到时间戳
SELECT UNIX_TIMESTAMP()
-
FROM_UNIXTIME()时间戳转日期
select FROM_UNIXTIME(UNIX_TIMESTAMP()-10*24*60*60)
-
DATE_ADD(d,INTERVAL expr type)从日期增加指定的时间间隔。
SELECT DATE_ADD(hire_date,interval -10 day) from emp; SELECT DATE_ADD(hire_date,interval 10 month) from emp;
-
DATE_SUB(date,INTERVAL expr type)函数从日期减去指定的时间间隔。
SELECT DATE_SUB(hire_date,interval 10 day) from emp
-
DATE_FORMAT(d,f)表达式 f的要求显示日期 d
select DATE_FORMAT(CURRENT_DATE,'%Y-%m-%d %H:%i:%S'); select DATE_FORMAT(CURRENT_DATE,'%m%d %Y');
-
STR_TO_DATE(string, format_mask)将字符串转变为日期
select STR_TO_DATE('2024王雨龙05朱超07张雪','%Y王雨龙%m朱超%d张雪');
-- 获取 日期的年份 月份 日 过了多少周(从0计数) 周几 过了多少周 select YEAR('2024-05-07') ,MONTH('2024-05-07') ,day('2024-05-07') ,week('2024-05-07') ,WEEKDAY('2024-05-07') ,WEEKOFYEAR('2024-05-07') -- 你出生的那一天是当年的多少周 '2000-07-30' -- 当年的那一周映射到今年是那一天到那一天 select WEEKOFYEAR(DATE_ADD('2000-07-30',INTERVAL 24 year)) ,WEEKDAY(DATE_ADD('2000-07-30',INTERVAL 24 year)) ,DATE_ADD(DATE_ADD('2000-07-30',INTERVAL 24 year),INTERVAL -8 day) ,DATE_ADD(DATE_ADD('2000-07-30',INTERVAL 24 year),INTERVAL -2 day)
高级函数
-
IF(expr,v1,v2)如果表达式 expr 成立,返回结果 v1;否则,返回结果 v2
select * ,if(age=21,'1',if(age=22,'2',if(age=23,'3','4'))) from students;
-
IFNULL(v1,v2) 如果 v1 的值不为 NULL,则返回 v1,否则返回 v2。
select name,IFNULL(email,'[email protected]') from users;
-
**CASE (WHEN THEN) + ELSE END **
-- CASE (WHEN condition THEN val) + ELSE END select * ,case when age=21 then '1' when age=22 then '2' when age=23 then '3' when age=24 then '4' else '5' end from students
-
CAST(x AS type)转换数据类型
-- 支持的转换类型 BINARY、CHAR、DATE、DATETIME、TIME、DECIMAL select CAST(age AS DECIMAL) from students;
使用Python操作MySQL
1、安装第三方模块pymysql
pip install pymysql
2、操作MySQL
import pymysql
# 创建连接
# 需要传入一些参数:
# host mysql所在的主机名或者是域名或者是ip地址
# port mysql运行的端口号
# ps -aux | grep mysql 找到MySQL运行的进程号
# netstat -tnlp | grep mysql的进程号 找到MySQL的端口
# user 用户名
# passwd 密码
# db 指定要操作的数据库
conn = pymysql.connect(host='master', port=3306, user='root', passwd='123456',db='stu_test')
# 创建游标cursor
cur = conn.cursor()
# cur.execute("use stu_test") # 切换数据库
# 准备SQL语句
sql_str1 = '''
SELECT t1.sid
,t1.sname
,t2.score
from (
SELECT sid
,sname
from Student
where sid in (
select t1.sid
from (
SELECT sid
,score
from SC
where cid = '01'
) t1 left join (
SELECT sid
,score
from SC
where cid = '02'
) t2 on t1.sid = t2.sid
where t1.score > ifnull(t2.score,0)
)
) t1 left join SC t2 on t1.sid = t2.sid
'''
# 执行SQL语句
cur.execute(sql_str1)
# 如果有返回值 可以通过cursor进行获取
print(cur.fetchone()) # 获取一条数据
print('#' * 50)
print(cur.fetchall()) # 获取所有数据
print('#' * 50)
print(cur.fetchmany(10)) # 获取指定大小的数据数据
# 如果没有返回值,看后续自己处理
标签:--,基础,查询,dept,详解,emp,MySQL,id,select
From: https://blog.csdn.net/2301_77698138/article/details/141094927