MySQL_走位操作

墨蓝 2022-01-22 11:15 148阅读 0赞

#### GROUP BY 关键字 ####

多层嵌套分组  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODI5NDk5OQ_size_16_color_FFFFFF_t_70]

#### update 关键字 ####

Mysql 根据一个表数据更新另外一个表
    update 更新表 set 字段 = (select 参考数据 from 参考表 where  参考表.id = 更新表.id);
    
    update table_2 m  set m.column = (select column from table_1 mp where mp.id= m.id);
    实例可以看  sql46题的联系
    UPDATE sc,(SELECT sc.cno,AVG(score) AS av  FROM sc,course c,teacher t WHERE sc.cno=c.cno AND c.tno=t.tno AND t.tname='谌燕' GROUP BY sc.cno) a
    SET sc.score=a.av 
    WHERE sc.cno=a.cno

-- 把“SC”表中“谌燕”老师教的课的成绩都更改为此课程的平均成绩；
    
    UPDATE SC SC,
    	-- 这个的逻辑是将查询出来的数据结果集放进临时表，update 关键字后面也是可以放几张表的
        (SELECT A.cno, AVG(SC.SCORE) SCORE
            FROM SC SC,
            (
    	--  这个的逻辑是先查出老师教了几门课
    	SELECT cno
                      FROM COURSE 
                     WHERE tno IN (SELECT tno FROM TEACHER WHERE TNAME = "谌燕")
    	) A
             WHERE SC.cno = A.cno
             GROUP BY SC.cno
    	) AA
     SET SC.SCORE = AA.SCORE
     WHERE SC.cno = AA.cno;

##### 关于 sql的 between … and 的边界问题 #####

mysql：
    select * from user where userId between 5 and 7; 
    查询userId为5、6，7的user，userId范围是包含边界值的。
    另外 not between的范围是不包含边界值。
    
    Oracle ：
    Oracle的BETWEEN..AND..前后都是闭区间，

表里面的 比较

-- 8、查询课程编号“c002”的成绩比课程编号 “c001”课程低的所有同学的学号、姓名；
    
    -- 课程号、学号、分数都在一张表里面，属于自比型
    
    SELECT * from student s
    join sc a on a.sno = s.sno
    join sc b on b.sno = s.sno
    where a.cno = 'c001' and b.cno = 'c002' and a.score < b.score;

-- 1、查询“c001”课程比“c002”课程成绩高的所有学生的学号；
    --  自比较
    
    -- 方法 一
    SELECT a.*
    from sc a
    join sc b on a.sno = b.sno
    where a.cno = 'c001' and b.cno = 'c002' and a.score > b.score;
    
    -- 方法 二
    select a.* from
    (select * from sc a where a.cno='c001') a,
    (select * from sc b where b.cno='c002') b
    where a.sno=b.sno and a.score > b.score;
    
    -- 方法 三
    select * from sc a
    where a.cno='c001'
    and  exists(select * from sc b where b.cno='c002' and a.score>b.score
    and a.sno = b.sno)

报错  
You can’t specify target table ‘sc’ for update in FROM clause

解决
    也就是说将select出的结果再通过中间表select一遍，这样就规避了错误。注意，这个问题只出现于mysql，mssql和oracle不会出现此问题。
    
    You can't specify target table for update in FROM clause含义:不能在同一表中查询的数据作为同一表的更新数据。
    例如：
    我想查询t_user_asset的余额加上50000作为更新字段f_cashAmount的值，这样写是不行的。
    
     UPDATE t_user_asset SET f_cashAmount =   
         (  
         SELECT (ua.f_cashAmount+50000) cashAmount FROM t_user_asset ua WHERE ua.f_userId = 290  
       )  
     WHERE f_userId = 290  
    
    
    修改成以下写法就行，意思就是变个方向，在select外边套一层，让数据库认为你不是查同一表的数据作为同一表的更新数据：
    
    
     UPDATE t_user_asset SET f_cashAmount =   
     (  
       SELECT ub.cashAmount FROM  
            (  
                  SELECT (ua.f_cashAmount+50000) cashAmount FROM t_user_asset ua WHERE ua.f_userId = 290  
            ) ub  
     )  
     WHERE f_userId = 290  
    以上问题只针对mysql数据库

复制表数据

SQL INSERT INTO SELECT 语句
    
    通过 SQL，您可以从一个表复制信息到另一个表。
    INSERT INTO SELECT 语句从一个表复制数据，然后把数据插入到一个已存在的表中。
    
    SQL INSERT INTO SELECT 语句
    INSERT INTO SELECT 语句从一个表复制数据，然后把数据插入到一个已存在的表中。目标表中任何已存在的行都不会受影响。
    SQL INSERT INTO SELECT 语法
    我们可以从一个表中复制所有的列插入到另一个已存在的表中：
    INSERT INTO table2
    SELECT * FROM table1;
    或者我们可以只复制希望的列插入到另一个已存在的表中：
    INSERT INTO table2
    (column_name(s))
    SELECT column_name(s)
    FROM table1;

分段统计

-- 统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
    
    -- 在sc 表按 学科分组把每组的数据
    select sc.cno ,c.cname,
    sum(case when sc.score between 70 and 100 then 1 else 0 end) as "[70-100]"
    from sc ,course c
    where sc.cno = c.cno
    group by sc.cno ,c.cname;

sql聚合函数与其他语句（关系，执行过程）

选择列表中的列 'SC.CNO' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。  
             元数据库为Stu_C，含有三个表，S、SC、C。执行的语句如下：
    
    use Stu_C  
    select SNO,cno,sum(grade) sum_grade  
    from SC  
    where GRADE>=60  
    group by SC.SNO  
    having count(CNO)>=4  
    order by sum_grade desc  
    
     目的是检索选修4门课以上课程的学生总成绩(排除不及格的),并按总成绩的降序排列出来。根据提示我们容易知道，select语句中多了cno这个属性列。如果将其去掉执  
     行如下：  
    
    use Stu_C  
    select SNO,sum(grade) sum_grade  
    from SC  
    where GRADE>=60  
    group by SC.SNO  
    having count(CNO)>=4  
    order by sum_grade desc  
    [sql] view plain copy
    
    当使用group by子句时则select中关于表的原始属性名只能是被"by"的属性，如上例中的SNO。
    若想输出其他属性列，则应在select子句后加上相应的聚合函数(count(*),count(列名),sum(列名),avg(列名),max(列名),min(列名))。
    其实这也很容易理解，当参照SNO被分组后，每一个SNO的具体实例与其他任意一个属性都变成一对一或一对多的关系，
    只有通过聚合函数将每组其他想要的属性的多个实例算成一个值，即SNO与其形成一对一的关系后才能列出。
    而且一旦SQL语句中使用了group by子句后聚合函数都变成分别对每组起作用。  
    
    一条SQL语句个子句的先后作用过程：  
     from→where→group by→select（含聚合函数）→order by
    
    所以当select子句中含有sum(grade) sum_grade时，最后的排序子句可以是order by sum_grade desc。

like与 regexp的区别

REGEXP 是sql 正则表达式 函数  
效率  
在 like 的匹配语句的最左边使用统配符的前提下，与 regexp 没什么区别  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODI5NDk5OQ_size_16_color_FFFFFF_t_70 1]

字符串截取  
这个不大懂

需求
    数据里的域名可能是
    http://www.baidu.com/abc.html?eee
    http://www.baidu.com/abc.html#wedf
    http://www.baidu.com/wer/hjk.html#whhhf
    http://www.baidu.com/abc/ggg/
    我需要截取成
    http://www.baidu.com/abc.html
    http://www.baidu.com/abc.html
    http://www.baidu.com/wer/hjk.html
    http://www.baidu.com/abc/ggg
    
    sql
    select left(talk_source,LEN(Talk_source)-CHARINDEX('?',REVERSE(Talk_source))) as Talk_source2,* 
    from Zoosnet_Data 
    where Project_ID=1 and (Zoosnet_Visit_Start_Time between '2013-1-1' and '2013-8-1') 
    and Zoosnet_Data.Talk_Source like '%http://www.baidu.com/abc.html?eee%'
    
    改进  
    Project_ID=1, Visit_Start 和Talk_Source都必须加上索引！
    如果Talk_Source都是以http://打头的，那么like表达式不要以%开头。以%开头会慢很多，而%在结尾的话可以利用到索引。
    
    最上面的sql好像只能处理问号, 处理#，/可以这样写：
    select replace(replace(left(talk_source, patindex('%[?#]%', 
    talk_source+'?')-1)+'$$','/$$',''),'$$','') as Talk_source2,* 
    from Zoosnet_Data 
    where Project_ID=1 and (Zoosnet_Visit_Start_Time between '2013-1-1' and '2013-8-1') 
    and Zoosnet_Data.Talk_Source like 'http://www.baidu.com/abc.html?eee%'

case when then else 的用法

-- 按各科平均成绩从低到高和及格率的百分数从高到低顺序
    
    -- 这个是对sc 表进行一个排序
    -- 及格率的百分数，及格的人数，除于总数，问题的 当一科成绩及格怎么加一
    
    select sc.cno ,avg(sc.score) as 平均成绩 ,  sum(case when score>=60 then 1 else 0 end)/count(*)
    as 及格率
    from sc group by cno
    order by 平均成绩 ,及格率 desc;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODI5NDk5OQ_size_16_color_FFFFFF_t_70]: /images/20220122/18d417e6f4c947b887c9aa1b736a8199.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODI5NDk5OQ_size_16_color_FFFFFF_t_70 1]: /images/20220122/e082dac3f8dc42f4a416902242fdbe63.png