SQL 学习

table (test1)

col1	col2
22	33
33	22
12	33
55	78
78	55
16	31
31	16

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

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)

id	name	sex	salary	bonus	department
1	王五	男	1000	23	开发一部
2	李四	男	300	400	开发二部
3	张三	女	3000	45	开发一部
4	大国	男	345	67	人事部门
5	小妞	女	499	99	人事部门

查下每个部门的总工资？

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

执行结果：

department	工资
人事部门	1010
开发一部	4068
开发二部	700

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

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

执行结果：

工资	name
3045	张三
1023	王五
700	李四
598	小妞
412	大国

查询工资最高者？

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

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

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

table(test3)

name	Course	score
张三	语文	81
张三	数学	79
李四	英语	88
李四	物理	90
李四	语文	81
王五	化学	78
王五	语文	90
王五	英语	87

用一条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;

结果：

name
李四

table(table4)

id	number	name	coursenum	coursename	score
1	200501	张三	0001	数学	89
2	200502	李四	0001	数学	79
3	200501	张三	0001	数学	89

查询所有的除了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：发生额。

AccID	Occmonth	DebitOccur
10000	01	100
10000	01	100
10000	01	100
10000	02	300
10000	03	150
10000	04	130
10000	05	200
10000	06	800
10000	07	900
10000	10	110
10000	11	90
10000	12	103
10000	08	120
10000	09	130
10001	01	140
10001	02	350
10001	03	160
10001	04	530
10001	05	240
10001	06	860
10001	07	970
10001	10	150
10001	11	970
10001	12	163
10001	08	127
10001	09	131
10002	01	10
10002	02	350
10002	03	16
10002	04	530
10002	05	20
10002	06	860
10002	07	90
10002	10	150
10002	11	970
10002	12	163
10002	08	127
10002	09	131

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);

执行结果：

AccID	Occmonth	DebitOccur
10001	01	140
10001	02	350
10001	03	160
10001	04	530
10001	05	240
10001	06	860
10001	07	970
10001	10	150
10001	11	970
10001	12	163
10001	08	127
10001	09	131