SQL 学习

table (test1)

查询哪些行是可以互换的？

SELECT 
    *
FROM
    test1 AS t1,
    test1 AS t2
WHERE
    t1.col1 = t2.col2 AND t1.col2 = t2.col1

执行结果：

| col1 | col2 | col1 | col2 |
| :---: | : ---: | :---: | :---: |
| 16 | 31 | 31 | 16 |
| 22 | 33 | 33 | 22 |
| 31 | 16 | 16 | 31 |
| 33 | 22 | 22 | 33 |
| 55 | 78 | 78 | 55 |
| 78 | 55 | 55 | 78 |

like

like 'xxx%' 匹配所有xxx开头的字符串
like '%xxx%' 匹配包含xxx的所有字符串
like ‘xxxx_’ 匹配xxx开头的长度是4的字符串
like '__' 匹配长度为2的所有字符串

table(test2)

查下每个部门的总工资？

SELECT 
    department, SUM(salary + bonus) AS 工资
FROM
    test.test2
GROUP BY department;

执行结果：

查下每个人的总工资，按高低排序？

SELECT 
    salary + bonus AS 工资, name
FROM
    test.test2
ORDER BY 工资 DESC

执行结果：

查询工资最高者？

 SELECT 
    MAX(salary + bonus), name
FROM
    test.test2;

查询每个部门工资最多的员工？

SELECT 
    name, MAX(salary + bonus) as salary, department
FROM
    test.test2
GROUP BY department;

table(test3)

用一条SQL 语句 查询出每门课都大于80 分的学生姓名?
第一种方式：

SELECT DISTINCT
    name
FROM
    test.test3
WHERE
    name NOT IN (SELECT DISTINCT
            name
        FROM
            test.test3
        WHERE
            score < 80);

第二种方式：

SELECT 
    name
FROM
    test.test3
GROUP BY name
HAVING MIN(score) > 80;

结果：

table(table4)

查询所有的除了id,不重复的数据

SELECT 
    *
FROM
    test.test4 AS t1
WHERE
    EXISTS( SELECT 
            1
        FROM
            (SELECT 
                MIN(id) AS id
            FROM
                test.test4
            GROUP BY number , name , coursenum , coursename , score) AS t2
        WHERE
            t1.id = t2.id);

上面的有点多余嵌套查询：

SELECT 
    MIN(id), number, name, coursenum, coursename, score
FROM
    test.test4
GROUP BY number , name , coursenum , coursename , score;

这2个sql 是同一个结果。

查询所有重复的数据？

SELECT 
    t1.*
FROM
    test.test4 AS t1,
    test.test4 t2
WHERE
    t1.id <> t2.id AND t1.number = t2.number
        AND t1.name = t2.name
        AND t1.coursenum = t2.coursenum
        AND t1.coursename = t2.coursename
        AND t1.score = t2.score;

删除除了自动编号不同, 其他都相同的学生冗余信息?

DELETE FROM test.test4 
WHERE
    id NOT IN (SELECT 
        MIN(id)
    FROM
        test.test4
    GROUP BY number , name , coursenum , coursename , score);

table

一个叫 team 的表，里面只有一个字段name, 一共有4 条纪录，分别是a,b,c,d, 对应四个球对，现在四个球对进行比赛，用一条sql 语句显示所有可能的比赛组合.

select a.name, b.name
from team a, team b
where a.name < b.name 

table(test)

请用SQL语句实现：从test数据表中查询出所有月份的发生额都比10000科目相应月份的发生额高的科目。请注意：test中有很多科目，都有1－12月份的发生额。
AccID：科目代码，Occmonth：发生额月份，DebitOccur：发生额。

SQL语句：

SELECT 
    *
FROM
    test
WHERE
    accid NOT IN (SELECT DISTINCT
            t.accid
        FROM
            test AS t,
            (SELECT 
                *
            FROM
                test
            WHERE
                accID = 10000) AS t1
        WHERE
            t.occmonth = t1.occmonth
                AND t.debitoccur <= t1.debitoccur);

执行结果：