MySQL基础详解(2)

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,'888@qq.com') 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))  # 获取指定大小的数据数据

# 如果没有返回值，看后续自己处理