LC_SQL_基础_DAY2

--------------------------------------

计算特殊奖金

讨论 1

方法一：LIKE +%取余

    select
        employee_id,
        if(
            employee_id%2=1 and name not like 'M%',
            salary,
            0
        ) as bonus
    from employees
    order by employee_id;


方法二:Mod和LEFT函数

    Mod(a,b) 在sql中的意思是 a % b

    Mod基础用法：如果id需要是偶数或者奇数时就可以使用mod。

    mod(id,2)=1 是指id是奇数。
    mod(id,2)=0 是指id是偶数。
    LEFT：返回最左边的n个字符的字符串str，或NULL如果任何参数是NULL。


    SELECT employee_id,
    /*MOD为取余操作*/
    (CASE WHEN MOD(employee_id,2)!=0 AND LEFT(name,1)!='M' THEN salary
         WHEN MOD(employee_id,2)=0 OR LEFT(name,1)='M' THEN 0
    END) bonus
    FROM Employees
    ORDER BY employee_id

方法三：IF判断+MOD取余

	IF第一个参数写条件，第二个参数写条件成立返回的内容，第三个参数写条件不成立返回的内容

    SELECT 
        employee_id,
    IF(MOD(employee_id,2)!=0 AND LEFT(name,1)!='M',salary,0) bonus
    FROM Employees
    ORDER BY employee_id

方法四：CASE函数

    CASE配合WHEN,THEN判断，LEFT和’=‘判断，MOD取余
    CASE配合WHEN,THEN使用，WHEN后接条件，THEN后接符合条件返回的内容 ，有多个条件时使用
    需要用ELSE返回以上条件都不成立时返回的内容，最后以END`结尾

    SELECT 
        employee_id,
        CASE 
            WHEN MOD(employee_id,2)!=0 AND LEFT(name,1)!='M' THEN salary
            WHEN MOD(employee_id,2)=0 OR LEFT(name,1)='M' THEN 0
            ELSE 0 -- 这里ELSE可以省略
        END AS bonus
    FROM Employees
    ORDER BY employee_id
    
方法五：正则匹配

rlike是正则，正则的写法与java一样。‘‘需要使用’\’,例如’\w’需要使用’\w’


    SELECT
        employee_id, 
    CASE WHEN 
        MOD(employee_id, 2) = 1 AND name not rlike '^M' THEN salary ELSE 0 END AS bonus 
    FROM
        Employees 
    ORDER BY 
        employee_id;
        
        
方法六：&运算与 regexp运算

regexp为正则运算符

逻辑与运算，1&1=1；0&1=0

    select
        employee_id,
        if(
            employee_id & 1 and name regexp '^[^M]',
            salary,
            0
        ) as bonus
    from employees
    order by employee_id;

变更性别

讨论 1

方法：使用 UPDATE 和 CASE...WHEN
算法

要想动态地将值设置成列，我们可以在使用 CASE...WHEN... 流程控制语句的同时使用 UPDATE 语句。

    UPDATE salary
    SET
        sex = CASE sex
            WHEN 'm' THEN 'f'
            ELSE 'm'
        END;

讨论 2

update salary set sex = char(ascii('m') + ascii('f') - ascii(sex));

讨论 3

# Write your MySQL query statement below
update salary set sex = if(sex = 'm', 'f', 'm')

讨论 4

ascii():将字符转为ASCII码值；
char():将ASCII码值转为字符；
^运算符：异或运算、即两值的二进制数在同位上相同则为0不同则为1；
在本次运算中：
f的ASCII码为：102、
二进制为：1100110；
m的ASCII吗为：109、
二进制为：1101101；
11的二进制为：1011；
故11^102=109；11^109=102


# Write your MySQL query statement below
update Salary set sex = char(11^ascii(sex))

删除重复的电子邮箱

讨论 1

方法：使用 DELETE 和 WHERE 子句


我们可以使用以下代码，将此表与它自身在电子邮箱列中连接起来。

MySQL

    SELECT p1.*
    FROM Person p1,
        Person p2
    WHERE
        p1.Email = p2.Email;
        
    
然后我们需要找到其他记录中具有相同电子邮件地址的更大 ID。所以我们可以像这样给 WHERE 子句添加一个新的条件。

MySQL

    SELECT p1.*
    FROM Person p1,
        Person p2
    WHERE
        p1.Email = p2.Email AND p1.Id > p2.Id;
        
        
因为我们已经得到了要删除的记录，所以我们最终可以将该语句更改为 DELETE。

MySQL

    DELETE p1 FROM Person p1,
        Person p2
    WHERE
        p1.Email = p2.Email AND p1.Id > p2.Id

讨论 2

# Please write a DELETE statement and DO NOT write a SELECT statement.
# Write your MySQL query statement below
DELETE from Person
WHERE id NOT IN #不在满足条件内的肯定就是不满足的，直接删除
(
    SELECT ID #先把满足条件的找出来
    From
    (
     SELECT  MIN(id) as ID
     From Person
     Group by Email
    )t
)

讨论 3

DELETE p1 FROM Person p1,
    Person p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id ##将表和自身链接起来并将重复的较大id筛选了出来。