MySQL语法

阳光穿透心脏的1/2处 2024-04-01 18:27 73阅读 0赞

[https://blog.csdn.net/weixin\_35795512/article/details/113479802][https_blog.csdn.net_weixin_35795512_article_details_113479802]

游标：[https://www.cnblogs.com/oukele/p/10684639.html][https_www.cnblogs.com_oukele_p_10684639.html]、[https://www.cnblogs.com/loong-hon/p/11003189.html][https_www.cnblogs.com_loong-hon_p_11003189.html]

mysql组内排序：[https://blog.csdn.net/xjh163/article/details/100975697][https_blog.csdn.net_xjh163_article_details_100975697]

select A.comment_id,A.img,A.content,A.date,A.shop_id,A.user_id,B.avatar,B.name
     from A inner join B on A.user_id=B.user_id 
     inner join C  on A.shop_id = C.shop_id 
     where C.shop_id = ?；
     #
     ①、A、B、C分表代表不同的三张表
     ②、 where C.shop_id = ?代表的是依据什么条件查询。

嵌套查询，去重

select 100 - 15*(POW(SUM(E.`折合面积_m2`)/3.75,0.412)) as PCI from 
    (
    	SELECT 
    		 S.`数据年份`,   F.unit_id ,S.`折合面积_m2`,F.seg
    	FROM(
    		SELECT 
    			G.Id as unit_id, A.route_name, G.lane_name, G.begin_mile ,SUBSTRING(CONVERT(G.begin_mile, UNSIGNED), 1, 			LENGTH(CONVERT(G.begin_mile, UNSIGNED))-2 ) as seg
    		FROM
    			rms_d_route_unit G, rms_d_route A 
    		WHERE
    			G.route_id = A.Id 
    	) F LEFT JOIN Raw_DR S ON S.`车道名称` = F.lane_name and (SUBSTRING(CONVERT(S.`起点桩号`, UNSIGNED), 1, LENGTH(CONVERT(S.`起点桩号`, UNSIGNED))-2)) =  F.seg and S.`管养段名称` = F.route_name 	where S.Id <= 6 GROUP BY S.Id 
    ) E GROUP BY E.seg

批量更新+嵌套查询（[https://blog.csdn.net/huanjia\_h/article/details/78087994][https_blog.csdn.net_huanjia_h_article_details_78087994]）

select * from rms_d_route_unit where Id = 59828 or Id = 60035

有两段相同的路元  
![在这里插入图片描述][d7f7c21e30fd4c0dbaab6d2cc06912f5.png]

#### 游标的应用实例 ####

CREATE DEFINER = CURRENT_USER PROCEDURE `PQI`()
    BEGIN
    
    declare id int;
    declare PCI_value double(18,8);
    declare RouteName varchar(32) character set utf8;
    declare unit_id int;
    declare seg int; 
    declare done int default 0;
    
    #创建临时表用于保存数据
    DROP TABLE IF EXISTS PCI_table;
    #临时表存 pci、unit_id、
    create TEMPORARY table PCI_table(
        id int primary key auto_increment,
        PCI_value double(18,8),
        RouteName varchar(32),
        unit_id int,
    		#起点桩号去掉后两位
    		seg int 
    )default charset=utf8;
    
    insert into PCI_table(PCI_value,RouteName,unit_id，seg) values
    
      -- 声明游标
        declare cur cursor for SELECT (SUBSTRING(CONVERT(`起点桩号`, UNSIGNED), 1, LENGTH(CONVERT(`起点桩号`, UNSIGNED))-2)) as seg, 100-15*(POW(SUM(`折合面积_m2`)/3.75,0.412)) as totalDis FROM  Raw_DR GROUP BY seg;
    		
        -- 指定游标循环结束时的返回值 
        declare continue handler for not found set done =1;
        -- 打开游标
        open cur;
        
        -- 初始化 变量
        set total = 0;
        
        -- loop 循环
        xxx:loop
            -- 根据游标当前指向的一条数据  
            fetch cur into id,name,sex,age;
            -- 当 游标的返回值为 1 时 退出 loop循环 
            if done = 1 then
                leave xxx;
            end if;
            -- 累计
            set total = total + 1;
        end loop;
        -- 关闭游标
        close cur;
        -- 输出 累计的结果
        select total;
    end //
    delimiter ;
    
    create temporary table 横向裂缝间距_table(
    		unit_id int,
    		inval int,
        cnt int
    	) ENGINE = InnoDB;
    
    
    END;
    
    
    UPDATE ams_d_green，rms_d_route set route_id = rms_d_route.Id where rms_d_route.route_name = ams_d_green.`路段名称`;
    
    SELECT * FROM `Raw_DR` where `管养段名称` like '京秦高速' and `车道名称` like '第三车道'
    
    
    
    
    
    select * from 
    
    
    select * from  Raw_DR a,(select Id as aid,begin_mile,end_mile,route_id,lane_name from rms_d_route_unit where route_id in (25)) b where CONVERT(a.`起点桩号`,UNSIGNED) >= b.begin_mile 
    and CONVERT(a.`止点桩号`,UNSIGNED) <= b.end_mile 
    and a.route_id = b.route_id
    and a.`车道名称` = b.lane_name 
    and a.route_id = 25
    and a.Id <= 6
    
    select * from rms_d_route_unit where route_id = 25 and begin_mile = 15800
    
    select * from ria_s_disease_type where parent_id in(2)
    
    
    select * from ria_s_unit_analysis where unit_id = 59828 and `year` = 2015;
    select * from ria_s_unit_analysis where unit_id = 59831 and `year` = 2015;
    select * from ria_s_unit_analysis where unit_id = 59834 and `year` = 2015;

#### 联表更新，like子查询 ####

语法：`update 表1, 表2 set 字段1 = 值1, 字段2 = 值2 where 条件语句`

MYSQL 多表更新 UPDATE SET like concat('%',abc,'%');
    SQL语句为：select * from table1 where  `text` like CONCAT('%',(select name from table2 where id =3),'%');
    
    UPDATE ecs_region a,nation b SET a.code = b.code where b.province like concat('%',a.region_name,'%');
    
    UPDATE ecs_region a,nation b SET a.code = b.code where b.city like concat('%',a.region_name,'%');
    
    UPDATE ecs_region a,nation b SET a.code = b.code where b.district like concat('%',a.region_name,'%');

将一个表的某些字段的值插入到另一个表（前提是字段名相同）

insert into rms_d_tran_section (Id, route_id, begin_mile, end_mile, section_discip, subgrade_width, pav_width)  
    SELECT id,route_id, begin_mile, end_mile, section_discip, subgrade_width, pav_width from Sheet1

**LEFT JOIN**两个结果集。  
LEFT JOIN是先连接，后过滤。也就是说，在WHERE条件过滤之前，已经进行了连接。*eg*：

SELECT 
    	S.STUDENT_NAME STUDENT_NAME, S.STUDENT_NO STUDENT_NO, F.ADDRESS ADDRESS
    FROM(
    	SELECT 
    		G.GRADE_NO GRADE_NO, A.ADDRESS
    	FROM
    		GRADE G, ADDRESS A
    	WHERE
    		G.GRADE_NO = A.GRADE_NO
    ) F LEFT JOIN STUDENT S ON F.STUDENT_NO = S.STUDENT_NO

#### 常见函数 ####

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>POW(x，y)</td> 
   <td>计算x的y次方</td> 
  </tr> 
  <tr> 
   <td>ROUND(x，y)</td> 
   <td>将x四舍五入保留y位小数</td> 
  </tr> 
  <tr> 
   <td>SUM(字段名)</td> 
   <td>求和</td> 
  </tr> 
  <tr> 
   <td>EXP(x)</td> 
   <td>e的x次方</td> 
  </tr> 
  <tr> 
   <td>concat(‘%’,abc,‘%’)</td> 
   <td>拼接字符串，eg：%abc%</td> 
  </tr> 
  <tr> 
   <td>abs(-1)</td> 
   <td>绝对值，eg：1</td> 
  </tr> 
  <tr> 
   <td>ceil(小数)</td> 
   <td>返回大于或等于的最小整数</td> 
  </tr> 
  <tr> 
   <td>ceiling(6.0)</td> 
   <td>返回大于或等于的最小整数</td> 
  </tr> 
  <tr> 
   <td>floor (6.6)</td> 
   <td>返回小于或等于的最大整数</td> 
  </tr> 
  <tr> 
   <td>rand ( )</td> 
   <td>返回0到1的随机数</td> 
  </tr> 
  <tr> 
   <td>pi()</td> 
   <td>返回圆周率</td> 
  </tr> 
  <tr> 
   <td>truncate(1.23456789, 3)</td> 
   <td>返回数值x保留到小数点后y位的值</td> 
  </tr> 
  <tr> 
   <td>sqrt(4)</td> 
   <td>返回x的平方根, eg：2</td> 
  </tr> 
 </tbody> 
</table>

#### 截取部分 ####

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>LEFT(str,len);</td> 
   <td>自左向右取len个字符</td> 
  </tr> 
  <tr> 
   <td>RIGHT(str,len);</td> 
   <td>自右向左取len个字符</td> 
  </tr> 
  <tr> 
   <td>SUBSTRING(str,起点,终点);</td> 
   <td>起点从1开始计数</td> 
  </tr> 
 </tbody> 
</table>

#### 大小写转换 ####

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>upper(字段名)</td> 
   <td>转大写</td> 
  </tr> 
  <tr> 
   <td>lower(字段名)</td> 
   <td>转小写</td> 
  </tr> 
  <tr> 
   <td>REGEXP</td> 
   <td>正则表达式</td> 
  </tr> 
  <tr> 
   <td>BINARY</td> 
   <td>强制区分大小写</td> 
  </tr> 
  <tr> 
   <td>UNION</td> 
   <td>连接两个select结果集</td> 
  </tr> 
 </tbody> 
</table>

mysql查询不区别大小写

#           REGEXP 正则表达式,     BINARY:强制区分大小写
    SELECT * FROM 表 WHERE 字段 REGEXP BINARY  '[a-z]';
    #转大写
    update 表名 set 字段名 = upper(字段名) WHERE 字段名 REGEXP BINARY '[a-z]';
    #转小写
    update  表名 set  字段名= lower(字段名);

**REGEXP** 正则表达式  
**BINARY** 强制区分大小写

#### 取笛卡尔积 ####

[SQL中的多表关联查询：笛卡尔积、内连接、外连接][SQL]  
`SELECT * from tmp_A;`  
![在这里插入图片描述][ba66e2f928024615891fb9620031d652.png_pic_center]  
`SELECT * from tmp_B;`  
![在这里插入图片描述][80c54c88c4a34af7a14fadb7cd56eb6d.png_pic_center]

`SELECT * from tmp_B,tmp_A;`下图为笛卡尔积现象，对tmp\_A表有4种选择，对tmp\_B表有4种选择，所以笛卡尔积的结果查询显示16条。  
![在这里插入图片描述][b0c3addb1fe04ffbb5e5fed48744e098.png_pic_center]

[https_blog.csdn.net_weixin_35795512_article_details_113479802]: https://blog.csdn.net/weixin_35795512/article/details/113479802
[https_www.cnblogs.com_oukele_p_10684639.html]: https://www.cnblogs.com/oukele/p/10684639.html
[https_www.cnblogs.com_loong-hon_p_11003189.html]: https://www.cnblogs.com/loong-hon/p/11003189.html
[https_blog.csdn.net_xjh163_article_details_100975697]: https://blog.csdn.net/xjh163/article/details/100975697
[https_blog.csdn.net_huanjia_h_article_details_78087994]: https://blog.csdn.net/huanjia_h/article/details/78087994
[d7f7c21e30fd4c0dbaab6d2cc06912f5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/bc5e64e91798475c888b167497b95f78.png
[SQL]: https://blog.csdn.net/qq_41833455/article/details/110948571?spm=1001.2101.3001.6650.3&utm_medium=distribute.pc_relevant.none-task-blog-2~default~CTRLIST~Rate-3-110948571-blog-80760135.pc_relevant_multi_platform_whitelistv3&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2~default~CTRLIST~Rate-3-110948571-blog-80760135.pc_relevant_multi_platform_whitelistv3&utm_relevant_index=6
[ba66e2f928024615891fb9620031d652.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/4e7514b51f284eab9e82496afadd1ff3.png
[80c54c88c4a34af7a14fadb7cd56eb6d.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/70c8bd82143f48ae94096959eb06334b.png
[b0c3addb1fe04ffbb5e5fed48744e098.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/8cc53952c5f64c699f18c6b20ffce381.png