数据库课堂笔记---查询

最新推荐文章于 2023-03-30 17:03:40 发布

JobYuanPioneer

最新推荐文章于 2023-03-30 17:03:40 发布

阅读量187

点赞数

分类专栏：学习笔记

本文链接：https://blog.csdn.net/qq_41842192/article/details/90942627

版权

学习笔记专栏收录该内容

15 篇文章 0 订阅

订阅专栏

单表查询

格式化查询

select sno,sname from student;
select sname,sno,sdept from student;
select * from student;// * 表示所有属性

select sname,2019-sage from student;//2019要与sage同类型，这里都是int

小写lcase,别名

select sname,‘Your of Birth:’,2019-sage,lcase(sdept) from student;//lcase转换小写字母输出
//输出时可以取别名
select sname NAME,‘Year of Birth:’ BIRTH,2019-sage BIRTHDAY,lcase(sdept) DEPARTMENT from student;

mysql> select sname NAME,'Year of Birth:' BIRTH,2019-sage BIRTHDAY,lcase(sdept) DEPARTMENT from student;
+----------+----------------+----------+------------+
| NAME     | BIRTH          | BIRTHDAY | DEPARTMENT |
+----------+----------------+----------+------------+
| 张三     | Year of Birth: |     1998 | cs         |
| 李四     | Year of Birth: |     1996 | is         |
| 王五     | Year of Birth: |     2000 | ma         |
| 马六     | Year of Birth: |     1999 | cs         |
| 苏三     | Year of Birth: |     1999 | is         |
| 刘七     | Year of Birth: |     2000 | is         |
| 刘三姐   | Year of Birth: |     1996 | is         |
| 欧阳锋   | Year of Birth: |     1995 | ma         |
| 欧阳大侠 | Year of Birth: |     1996 | ma         |
| 陈冬     | Year of Birth: |     2000 | is         |
| 张成民   | Year of Birth: |     2000 | cs         |
+----------+----------------+----------+------------+
11 rows in set (0.00 sec)

select distinct sno from sc;//显示所有sno属性值,distinct把重复项去掉

where关键字,between…and… ,in()

//where后可以加任何条件
select sname,sage from student where sage<20;
select sname,sage from student where not sage>=20;
//between and 在。。。之间的条件
select sname,sdept,sage from student where sage between 20 and 23;
//查询包括’IS’,'MA’或’CS’的记录
select sname,ssex from student where sdept in(‘IS’,‘MA’,‘CS’);

通配符% _和 like

% 匹配多个任意字符
_ 匹配单个任意字符

mysql> select sname,sno,ssex from student where sname like '刘%';
+--------+-------+------+
| sname  | sno   | ssex |
+--------+-------+------+
| 刘七   | 95006 | 女   |
| 刘三姐 | 95007 | 女   |
+--------+-------+------+
2 rows in set (0.00 sec)

mysql> select sname,sno,ssex from student where sname like '刘__';//此处为两个下划线
+--------+-------+------+
| sname  | sno   | ssex |
+--------+-------+------+
| 刘三姐 | 95007 | 女   |
+--------+-------+------+
1 row in set (0.00 sec)

like 匹配通配符关键字
not like

mysql> select sname,sno,ssex from student where sname not like '刘%';
+----------+-------+------+
| sname    | sno   | ssex |
+----------+-------+------+
| 张三     | 95001 | 男   |
| 李四     | 95002 | 男   |
| 王五     | 95003 | 男   |
| 马六     | 95004 | 女   |
| 苏三     | 95005 | 女   |
| 欧阳锋   | 95008 | 男   |
| 欧阳大侠 | 95009 | 男   |
| 陈冬     | 95010 | 男   |
| 张成民   | 95011 | 男   |
+----------+-------+------+
9 rows in set (0.01 sec)

_:表示符号_ 不表示通配符

mysql> select cno,Ccreadit from course where cname like 'DB\_DESIGN';
+-----+----------+
| cno | Ccreadit |
+-----+----------+
| 2   |        2 |
+-----+----------+
1 row in set (0.00 sec)

mysql> select cno,Ccreadit from course where cname like 'DB\_%I__';
+-----+----------+
| cno | Ccreadit |
+-----+----------+
| 2   |        2 |
+-----+----------+
1 row in set (0.00 sec)

mysql> select cname, cno,Ccreadit from course where cname like 'DB\_%I__';
+-----------+-----+----------+
| cname     | cno | Ccreadit |
+-----------+-----+----------+
| DB_DESIGN | 2   |        2 |
+-----------+-----+----------+
1 row in set (0.00 sec)

mysql>

is null

is null 查找数据为空的记录
is not null 查找数据不为空的记录

mysql> select * from sc;
+-------+-----+-------+
| sno   | cno | grade |
+-------+-----+-------+
| 95001 | 1   |    92 |
| 95001 | 2   |    94 |
| 95001 | 3   |    90 |
| 95001 | 4   |    97 |
| 95002 | 2   |    94 |
| 95002 | 3   |    80 |
| 95003 | 2   |  NULL |
| 95004 | 3   |  NULL |
| 95004 | 4   |    87 |
| 95005 | 1   |    90 |
| 95005 | 2   |    98 |
| 95005 | 3   |    90 |
| 95005 | 4   |    89 |
| 95011 | 1   |  NULL |
+-------+-----+-------+
14 rows in set (0.00 sec)

mysql> select sno , cno from sc where grade is null;
+-------+-----+
| sno   | cno |
+-------+-----+
| 95003 | 2   |
| 95004 | 3   |
| 95011 | 1   |
+-------+-----+
3 rows in set (0.00 sec)

mysql> select sno , cno from sc where grade is not null;
+-------+-----+
| sno   | cno |
+-------+-----+
| 95001 | 1   |
| 95001 | 2   |
| 95001 | 3   |
| 95001 | 4   |
| 95002 | 2   |
| 95002 | 3   |
| 95004 | 4   |
| 95005 | 1   |
| 95005 | 2   |
| 95005 | 3   |
| 95005 | 4   |
+-------+-----+
11 rows in set (0.00 sec)

or 和 and

select sname,ssex from student where sdept in(‘is’,‘ma’,‘cs’);
select sname,ssex from student where sdept=‘is’ or sdept=‘ma’ or sdept=‘cs’;

输出排序 order by ; desc , isc;

select sno,grade from sc where cno=‘3’ order by grade desc;//grade降序
mysql> select sno,grade from sc where cno=‘3’ order by grade desc limit 1;//设置显示行数

mysql> select sno,grade from sc where cno='3' order by grade desc limit 1;
+-------+-------+
| sno   | grade |
+-------+-------+
| 95001 |    90 |
+-------+-------+
1 row in set (0.00 sec)

mysql> select sno,grade from sc where cno='3' order by grade desc limit 2;
+-------+-------+
| sno   | grade |
+-------+-------+
| 95001 |    90 |
| 95005 |    90 |
+-------+-------+
2 rows in set (0.00 sec)

mysql> select sno,grade from sc where cno='3' order by grade desc limit 0;
Empty set (0.00 sec)

limit m,n

limit m,n表示取出从m行到n行
select sno,grade from sc where cno=‘3’ order by grade desc limit 0,2;

//先按sdept的升序,再按sage的降序排列,升序标志asc可省略
select * from student order by sdept,sage desc;

select count(*)总人数 from student;//count统计个数

mysql> select cno ,count(sno) from sc group by cno;
+-----+------------+
| cno | count(sno) |
+-----+------------+
| 1   |          3 |
| 2   |          4 |
| 3   |          4 |
| 4   |          3 |
+-----+------------+
4 rows in set (0.01 sec)

mysql> select cno ,count(sno) from sc;
+-----+------------+
| cno | count(sno) |
+-----+------------+
| 1   |         14 |
+-----+------------+
1 row in set (0.00 sec)

mysql> select sno cno ,count(sno) from sc;
+-------+------------+
| cno   | count(sno) |
+-------+------------+
| 95001 |         14 |
+-------+------------+
1 row in set (0.00 sec)

mysql> select sno ,cno ,count(sno) from sc;
+-------+-----+------------+
| sno   | cno | count(sno) |
+-------+-----+------------+
| 95001 | 1   |         14 |
+-------+-----+------------+
1 row in set (0.00 sec)

mysql> select sno ,cno ,count(sno) from sc group by cno;
+-------+-----+------------+
| sno   | cno | count(sno) |
+-------+-----+------------+
| 95001 | 1   |          3 |
| 95001 | 2   |          4 |
| 95001 | 3   |          4 |
| 95001 | 4   |          3 |
+-------+-----+------------+
4 rows in set (0.00 sec)

mysql> select * from sc;
+-------+-----+-------+
| sno   | cno | grade |
+-------+-----+-------+
| 95001 | 1   |    92 |
| 95001 | 2   |    94 |
| 95001 | 3   |    90 |
| 95001 | 4   |    97 |
| 95002 | 2   |    90 |
| 95002 | 3   |    80 |
| 95003 | 2   |  NULL |
| 95004 | 3   |  NULL |
| 95004 | 4   |    87 |
| 95005 | 1   |    90 |
| 95005 | 2   |    98 |
| 95005 | 3   |    90 |
| 95005 | 4   |    89 |
| 95011 | 1   |  NULL |
+-------+-----+-------+
14 rows in set (0.00 sec)

/课堂记录

Microsoft Windows [版本 10.0.17134.765]
(c) 2018 Microsoft Corporation。保留所有权利。

C:\Users\袁一博>mysql -u root -p
Enter password: *************
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 61
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use school
Database changed
mysql> show tables
    -> ;
+------------------+
| Tables_in_school |
+------------------+
| course           |
| sc               |
| student          |
+------------------+
3 rows in set (0.04 sec)

mysql> select * from course;
+-----+-----------+------+----------+
| Cno | Cname     | Cpno | Ccreadit |
+-----+-----------+------+----------+
| 1   | MATH      | NULL |        6 |
| 2   | DB_DESIGN | 3    |        2 |
| 3   | P_DESIGN  | 4    |        3 |
| 4   | OS        | NULL |        2 |
+-----+-----------+------+----------+
4 rows in set (0.01 sec)

mysql> select * from sc;
+-------+-----+-------+
| sno   | cno | grade |
+-------+-----+-------+
| 95001 | 1   |    92 |
| 95001 | 2   |    94 |
| 95001 | 3   |    90 |
| 95001 | 4   |    97 |
| 95002 | 2   |    94 |
| 95002 | 3   |    80 |
| 95003 | 2   |  NULL |
| 95004 | 3   |  NULL |
| 95004 | 4   |    87 |
| 95005 | 1   |    90 |
| 95005 | 2   |    98 |
| 95005 | 3   |    90 |
| 95005 | 4   |    89 |
| 95011 | 1   |  NULL |
+-------+-----+-------+
14 rows in set (0.00 sec)

mysql> select * from student where sno like'95001';
+-------+-------+------+------+-------+
| Sno   | Sname | Ssex | Sage | Sdept |
+-------+-------+------+------+-------+
| 95001 | 张三  | 男   |   21 | CS    |
+-------+-------+------+------+-------+
1 row in set (0.01 sec)

mysql> select sname,sno,ssex from student where sname like '刘%';
+--------+-------+------+
| sname  | sno   | ssex |
+--------+-------+------+
| 刘七   | 95006 | 女   |
| 刘三姐 | 95007 | 女   |
+--------+-------+------+
2 rows in set (0.00 sec)

mysql> select sname,sno,ssex from student where sname like '刘__';
+--------+-------+------+
| sname  | sno   | ssex |
+--------+-------+------+
| 刘三姐 | 95007 | 女   |
+--------+-------+------+
1 row in set (0.00 sec)

mysql> select sname,sno,ssex from student where sname like '欧阳%';
+----------+-------+------+
| sname    | sno   | ssex |
+----------+-------+------+
| 欧阳锋   | 95008 | 男   |
| 欧阳大侠 | 95009 | 男   |
+----------+-------+------+
2 rows in set (0.00 sec)

mysql> describe sc
    -> ;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| sno   | varchar(5) | NO   | PRI | NULL    |       |
| cno   | varchar(3) | NO   | PRI | NULL    |       |
| grade | int(11)    | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> describe student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Sno   | varchar(5)  | NO   | PRI | NULL    |       |
| Sname | varchar(20) | YES  |     | NULL    |       |
| Ssex  | varchar(1)  | YES  |     | NULL    |       |
| Sage  | smallint(6) | YES  |     | NULL    |       |
| Sdept | varchar(15) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> select sname,sno,ssex from student where sname like '_三%';
+--------+-------+------+
| sname  | sno   | ssex |
+--------+-------+------+
| 张三   | 95001 | 男   |
| 苏三   | 95005 | 女   |
| 刘三姐 | 95007 | 女   |
+--------+-------+------+
3 rows in set (0.00 sec)

mysql> select sname,sno,ssex from student where sname not like '刘%';
+----------+-------+------+
| sname    | sno   | ssex |
+----------+-------+------+
| 张三     | 95001 | 男   |
| 李四     | 95002 | 男   |
| 王五     | 95003 | 男   |
| 马六     | 95004 | 女   |
| 苏三     | 95005 | 女   |
| 欧阳锋   | 95008 | 男   |
| 欧阳大侠 | 95009 | 男   |
| 陈冬     | 95010 | 男   |
| 张成民   | 95011 | 男   |
+----------+-------+------+
9 rows in set (0.01 sec)

mysql> select cno,ccredit from course where cname like 'DB\_DESIGN';
ERROR 1054 (42S22): Unknown column 'ccredit' in 'field list'
mysql> select cno,Ccredit from course where cname like 'DB\_DESIGN';
ERROR 1054 (42S22): Unknown column 'Ccredit' in 'field list'
mysql> describe course;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| Cno      | varchar(3)  | NO   | PRI | NULL    |       |
| Cname    | varchar(60) | YES  |     | NULL    |       |
| Cpno     | varchar(3)  | YES  |     | NULL    |       |
| Ccreadit | int(11)     | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> select cno,Ccreadit from course where cname like 'DB\_DESIGN';
+-----+----------+
| cno | Ccreadit |
+-----+----------+
| 2   |        2 |
+-----+----------+
1 row in set (0.00 sec)

mysql> select cno,Ccreadit from course where cname like 'DB\_%I__';
+-----+----------+
| cno | Ccreadit |
+-----+----------+
| 2   |        2 |
+-----+----------+
1 row in set (0.00 sec)

mysql> select cname, cno,Ccreadit from course where cname like 'DB\_%I__';
+-----------+-----+----------+
| cname     | cno | Ccreadit |
+-----------+-----+----------+
| DB_DESIGN | 2   |        2 |
+-----------+-----+----------+
1 row in set (0.00 sec)

mysql> select sno , cno from sc where grade is null;
+-------+-----+
| sno   | cno |
+-------+-----+
| 95003 | 2   |
| 95004 | 3   |
| 95011 | 1   |
+-------+-----+
3 rows in set (0.00 sec)

mysql> select * from sc;
+-------+-----+-------+
| sno   | cno | grade |
+-------+-----+-------+
| 95001 | 1   |    92 |
| 95001 | 2   |    94 |
| 95001 | 3   |    90 |
| 95001 | 4   |    97 |
| 95002 | 2   |    94 |
| 95002 | 3   |    80 |
| 95003 | 2   |  NULL |
| 95004 | 3   |  NULL |
| 95004 | 4   |    87 |
| 95005 | 1   |    90 |
| 95005 | 2   |    98 |
| 95005 | 3   |    90 |
| 95005 | 4   |    89 |
| 95011 | 1   |  NULL |
+-------+-----+-------+
14 rows in set (0.00 sec)

mysql> mysql> select sno , cno from sc where grade is null;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql> select sno , cno from sc where grade is null' at line 1
mysql> +-------+-----+
    -> | sno   | cno |
    -> +-------+-----+
    -> | 95003 | 2   |
    -> | 95004 | 3   |
    -> | 95011 | 1   |
    -> +-------+-----+
    -> 3 rows in set (0.00 sec);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '+-------+-----+
| sno   | cno |
+-------+-----+
| 95003 | 2   |
| 95004 | 3   |
' at line 1
mysql> select * from sc;
+-------+-----+-------+
| sno   | cno | grade |
+-------+-----+-------+
| 95001 | 1   |    92 |
| 95001 | 2   |    94 |
| 95001 | 3   |    90 |
| 95001 | 4   |    97 |
| 95002 | 2   |    94 |
| 95002 | 3   |    80 |
| 95003 | 2   |  NULL |
| 95004 | 3   |  NULL |
| 95004 | 4   |    87 |
| 95005 | 1   |    90 |
| 95005 | 2   |    98 |
| 95005 | 3   |    90 |
| 95005 | 4   |    89 |
| 95011 | 1   |  NULL |
+-------+-----+-------+
14 rows in set (0.00 sec)

mysql> select sno , cno from sc where grade is null;
+-------+-----+
| sno   | cno |
+-------+-----+
| 95003 | 2   |
| 95004 | 3   |
| 95011 | 1   |
+-------+-----+
3 rows in set (0.00 sec)

mysql> select sno , cno from sc where grade is not null;
+-------+-----+
| sno   | cno |
+-------+-----+
| 95001 | 1   |
| 95001 | 2   |
| 95001 | 3   |
| 95001 | 4   |
| 95002 | 2   |
| 95002 | 3   |
| 95004 | 4   |
| 95005 | 1   |
| 95005 | 2   |
| 95005 | 3   |
| 95005 | 4   |
+-------+-----+
11 rows in set (0.00 sec)

mysql> select sname from student where sdept='CS'and sage<20;
+--------+
| sname  |
+--------+
| 张成民 |
+--------+
1 row in set (0.00 sec)

mysql> select * from sc;
+-------+-----+-------+
| sno   | cno | grade |
+-------+-----+-------+
| 95001 | 1   |    92 |
| 95001 | 2   |    94 |
| 95001 | 3   |    90 |
| 95001 | 4   |    97 |
| 95002 | 2   |    94 |
| 95002 | 3   |    80 |
| 95003 | 2   |  NULL |
| 95004 | 3   |  NULL |
| 95004 | 4   |    87 |
| 95005 | 1   |    90 |
| 95005 | 2   |    98 |
| 95005 | 3   |    90 |
| 95005 | 4   |    89 |
| 95011 | 1   |  NULL |
+-------+-----+-------+
14 rows in set (0.00 sec)

mysql> update sc set grade=90 where sno='95002'and cno='2';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from sc;
+-------+-----+-------+
| sno   | cno | grade |
+-------+-----+-------+
| 95001 | 1   |    92 |
| 95001 | 2   |    94 |
| 95001 | 3   |    90 |
| 95001 | 4   |    97 |
| 95002 | 2   |    90 |
| 95002 | 3   |    80 |
| 95003 | 2   |  NULL |
| 95004 | 3   |  NULL |
| 95004 | 4   |    87 |
| 95005 | 1   |    90 |
| 95005 | 2   |    98 |
| 95005 | 3   |    90 |
| 95005 | 4   |    89 |
| 95011 | 1   |  NULL |
+-------+-----+-------+
14 rows in set (0.00 sec)

mysql> select * from student;
+-------+----------+------+------+-------+
| Sno   | Sname    | Ssex | Sage | Sdept |
+-------+----------+------+------+-------+
| 95001 | 张三     | 男   |   21 | CS    |
| 95002 | 李四     | 男   |   23 | IS    |
| 95003 | 王五     | 男   |   19 | MA    |
| 95004 | 马六     | 女   |   20 | CS    |
| 95005 | 苏三     | 女   |   20 | IS    |
| 95006 | 刘七     | 女   |   19 | IS    |
| 95007 | 刘三姐   | 女   |   23 | IS    |
| 95008 | 欧阳锋   | 男   |   24 | MA    |
| 95009 | 欧阳大侠 | 男   |   23 | MA    |
| 95010 | 陈冬     | 男   |   19 | IS    |
| 95011 | 张成民   | 男   |   19 | CS    |
+-------+----------+------+------+-------+
11 rows in set (0.00 sec)

mysql> select sname,ssex from student where sdept in('is','ma','cs');
+----------+------+
| sname    | ssex |
+----------+------+
| 张三     | 男   |
| 李四     | 男   |
| 王五     | 男   |
| 马六     | 女   |
| 苏三     | 女   |
| 刘七     | 女   |
| 刘三姐   | 女   |
| 欧阳锋   | 男   |
| 欧阳大侠 | 男   |
| 陈冬     | 男   |
| 张成民   | 男   |
+----------+------+
11 rows in set (0.01 sec)

mysql> select sname,ssex from student where sdept='is' or sdept='ma
    '> ' or sdept='cs';
+--------+------+
| sname  | ssex |
+--------+------+
| 张三   | 男   |
| 李四   | 男   |
| 马六   | 女   |
| 苏三   | 女   |
| 刘七   | 女   |
| 刘三姐 | 女   |
| 陈冬   | 男   |
| 张成民 | 男   |
+--------+------+
8 rows in set (0.01 sec)

mysql> select sname,ssex from student where sdept='is' or sdept='ma' or sdept='cs';
+----------+------+
| sname    | ssex |
+----------+------+
| 张三     | 男   |
| 李四     | 男   |
| 王五     | 男   |
| 马六     | 女   |
| 苏三     | 女   |
| 刘七     | 女   |
| 刘三姐   | 女   |
| 欧阳锋   | 男   |
| 欧阳大侠 | 男   |
| 陈冬     | 男   |
| 张成民   | 男   |
+----------+------+
11 rows in set (0.00 sec)

mysql> select sno,grade from sc where cno='3' order by grade desc;
+-------+-------+
| sno   | grade |
+-------+-------+
| 95001 |    90 |
| 95005 |    90 |
| 95002 |    80 |
| 95004 |  NULL |
+-------+-------+
4 rows in set (0.00 sec)

mysql> select sno,max(grade) from sc where cno='3';
+-------+------------+
| sno   | max(grade) |
+-------+------------+
| 95001 |         90 |
+-------+------------+
1 row in set (0.01 sec)

mysql> select sno,max(grade) max from sc where cno='3';
+-------+------+
| sno   | max  |
+-------+------+
| 95001 |   90 |
+-------+------+
1 row in set (0.00 sec)

mysql> select sno 姓名,max(grade) max from sc where cno='3';
+-------+------+
| 姓名  | max  |
+-------+------+
| 95001 |   90 |
+-------+------+
1 row in set (0.00 sec)

mysql> select sno 姓名,max(grade) 最高分 from sc where cno='3';
+-------+--------+
| 姓名  | 最高分 |
+-------+--------+
| 95001 |     90 |
+-------+--------+
1 row in set (0.00 sec)

mysql> select sno,grade from sc where cno='3' order by grade desc limit 1;
+-------+-------+
| sno   | grade |
+-------+-------+
| 95001 |    90 |
+-------+-------+
1 row in set (0.00 sec)

mysql> select sno,grade from sc where cno='3' order by grade desc limit 2;
+-------+-------+
| sno   | grade |
+-------+-------+
| 95001 |    90 |
| 95005 |    90 |
+-------+-------+
2 rows in set (0.00 sec)

mysql> select sno,grade from sc where cno='3' order by grade desc limit 0;
Empty set (0.00 sec)

mysql> select sno,grade from sc where cno='3' order by grade desc limit 0,1;
+-------+-------+
| sno   | grade |
+-------+-------+
| 95001 |    90 |
+-------+-------+
1 row in set (0.00 sec)

mysql> select sno,grade from sc where cno='3' order by grade desc limit 1,1;
+-------+-------+
| sno   | grade |
+-------+-------+
| 95005 |    90 |
+-------+-------+
1 row in set (0.00 sec)

mysql> select sno,grade from sc where cno='3' order by grade desc limit 0,2;
+-------+-------+
| sno   | grade |
+-------+-------+
| 95001 |    90 |
| 95005 |    90 |
+-------+-------+
2 rows in set (0.00 sec)

mysql> select sno,max(grade) from sc where cno='3';
+-------+------------+
| sno   | max(grade) |
+-------+------------+
| 95001 |         90 |
+-------+------------+
1 row in set (0.00 sec)

mysql> select sno,grade from sc where cno='3' order by grade desc limit 1,2;
+-------+-------+
| sno   | grade |
+-------+-------+
| 95005 |    90 |
| 95002 |    80 |
+-------+-------+
2 rows in set (0.00 sec)

mysql> select sno,grade from sc where cno='3' order by grade desc limit 2,3;
+-------+-------+
| sno   | grade |
+-------+-------+
| 95002 |    80 |
| 95004 |  NULL |
+-------+-------+
2 rows in set (0.00 sec)

mysql> select * from student order by sdept,sage desc;
+-------+----------+------+------+-------+
| Sno   | Sname    | Ssex | Sage | Sdept |
+-------+----------+------+------+-------+
| 95001 | 张三     | 男   |   21 | CS    |
| 95004 | 马六     | 女   |   20 | CS    |
| 95011 | 张成民   | 男   |   19 | CS    |
| 95002 | 李四     | 男   |   23 | IS    |
| 95007 | 刘三姐   | 女   |   23 | IS    |
| 95005 | 苏三     | 女   |   20 | IS    |
| 95006 | 刘七     | 女   |   19 | IS    |
| 95010 | 陈冬     | 男   |   19 | IS    |
| 95008 | 欧阳锋   | 男   |   24 | MA    |
| 95009 | 欧阳大侠 | 男   |   23 | MA    |
| 95003 | 王五     | 男   |   19 | MA    |
+-------+----------+------+------+-------+
11 rows in set (0.00 sec)

mysql> select sno,grade from sc where cno='3' order by grade sc limit 2,3;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sc limit 2,3' at line 1
mysql> select sno,grade from sc where cno='3' order by grade inc limit 2,3;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'inc limit 2,3' at line 1
mysql> select sno,grade from sc where cno='3' order by grade incr limit 2,3;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'incr limit 2,3' at line 1
mysql> select sno,grade from sc where cno='3' order by grade asc limit 2,3;
+-------+-------+
| sno   | grade |
+-------+-------+
| 95001 |    90 |
| 95005 |    90 |
+-------+-------+
2 rows in set (0.00 sec)

mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
|       11 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*)总人数 from student;
+--------+
| 总人数 |
+--------+
|     11 |
+--------+
1 row in set (0.00 sec)

mysql> select count(distinct sno) from sc;
+---------------------+
| count(distinct sno) |
+---------------------+
|                   6 |
+---------------------+
1 row in set (0.01 sec)

mysql> select count( sno) from sc;
+-------------+
| count( sno) |
+-------------+
|          14 |
+-------------+
1 row in set (0.00 sec)

mysql> select max(g\\\\\\\\\\\\\\\\\\\\\\\\\\\;
ERROR:
Unknown command '\\'.
ERROR:
Unknown command '\\'.
ERROR:
Unknown command '\\'.
ERROR:
Unknown command '\\'.
ERROR:
Unknown command '\\'.
ERROR:
Unknown command '\\'.
ERROR:
Unknown command '\\'.
ERROR:
Unknown command '\\'.
ERROR:
Unknown command '\\'.
ERROR:
Unknown command '\\'.
ERROR:
Unknown command '\\'.
ERROR:
Unknown command '\\'.
ERROR:
Unknown command '\\'.
ERROR:
Unknown command '\;'.
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\\\\\\\\\\\\\\\\\\\\\\\\\\\' at line 1
mysql> select avg(grade) from sc where cno='1';
+------------+
| avg(grade) |
+------------+
|    91.0000 |
+------------+
1 row in set (0.01 sec)

mysql> select max(grade) from sc where cno='1';
+------------+
| max(grade) |
+------------+
|         92 |
+------------+
1 row in set (0.00 sec)

mysql> select cno ,count(sno) from sc group by cno;
+-----+------------+
| cno | count(sno) |
+-----+------------+
| 1   |          3 |
| 2   |          4 |
| 3   |          4 |
| 4   |          3 |
+-----+------------+
4 rows in set (0.01 sec)

mysql> select cno ,count(sno) from sc;
+-----+------------+
| cno | count(sno) |
+-----+------------+
| 1   |         14 |
+-----+------------+
1 row in set (0.00 sec)

mysql> select sno cno ,count(sno) from sc;
+-------+------------+
| cno   | count(sno) |
+-------+------------+
| 95001 |         14 |
+-------+------------+
1 row in set (0.00 sec)

mysql> select sno ,cno ,count(sno) from sc;
+-------+-----+------------+
| sno   | cno | count(sno) |
+-------+-----+------------+
| 95001 | 1   |         14 |
+-------+-----+------------+
1 row in set (0.00 sec)

mysql> select sno ,cno ,count(sno) from sc group by cno;
+-------+-----+------------+
| sno   | cno | count(sno) |
+-------+-----+------------+
| 95001 | 1   |          3 |
| 95001 | 2   |          4 |
| 95001 | 3   |          4 |
| 95001 | 4   |          3 |
+-------+-----+------------+
4 rows in set (0.00 sec)

mysql> select * from sc;
+-------+-----+-------+
| sno   | cno | grade |
+-------+-----+-------+
| 95001 | 1   |    92 |
| 95001 | 2   |    94 |
| 95001 | 3   |    90 |
| 95001 | 4   |    97 |
| 95002 | 2   |    90 |
| 95002 | 3   |    80 |
| 95003 | 2   |  NULL |
| 95004 | 3   |  NULL |
| 95004 | 4   |    87 |
| 95005 | 1   |    90 |
| 95005 | 2   |    98 |
| 95005 | 3   |    90 |
| 95005 | 4   |    89 |
| 95011 | 1   |  NULL |
+-------+-----+-------+
14 rows in set (0.00 sec)

mysql> select sno from sc group by sno having count(*)>=3;
+-------+
| sno   |
+-------+
| 95001 |
| 95005 |
+-------+
2 rows in set (0.00 sec)

mysql> select sno ,count(sno )from sc group by sno having count(*)>=3;
+-------+-------------+
| sno   | count(sno ) |
+-------+-------------+
| 95001 |           4 |
| 95005 |           4 |
+-------+-------------+
2 rows in set (0.00 sec)

mysql> select sno ,count(*) from sc where grade>=90 group by sno having count(*)>=3;
+-------+----------+
| sno   | count(*) |
+-------+----------+
| 95001 |        4 |
| 95005 |        3 |
+-------+----------+
2 rows in set (0.00 sec)

mysql> select sno ,count(sno) from sc where grade>=90 group by sno having count(*)>=3;
+-------+------------+
| sno   | count(sno) |
+-------+------------+
| 95001 |          4 |
| 95005 |          3 |
+-------+------------+
2 rows in set (0.00 sec)

mysql> select sno  from sc where grade>=90 group by sno having count(*)>=3;
+-------+
| sno   |
+-------+
| 95001 |
| 95005 |
+-------+
2 rows in set (0.00 sec)

mysql> select sno ,cno from sc where grade>=90 group by sno having count(*)>=3;
+-------+-----+
| sno   | cno |
+-------+-----+
| 95001 | 1   |
| 95005 | 1   |
+-------+-----+
2 rows in set (0.00 sec)

mysql> select * from sc;
+-------+-----+-------+
| sno   | cno | grade |
+-------+-----+-------+
| 95001 | 1   |    92 |
| 95001 | 2   |    94 |
| 95001 | 3   |    90 |
| 95001 | 4   |    97 |
| 95002 | 2   |    90 |
| 95002 | 3   |    80 |
| 95003 | 2   |  NULL |
| 95004 | 3   |  NULL |
| 95004 | 4   |    87 |
| 95005 | 1   |    90 |
| 95005 | 2   |    98 |
| 95005 | 3   |    90 |
| 95005 | 4   |    89 |
| 95011 | 1   |  NULL |
+-------+-----+-------+
14 rows in set (0.00 sec)

mysql> select sno ,count(sno) from sc where grade>=90 group by sno having count(*)>=3;

等值连接

select student.sno,sname,ssex,sage,sdept,cno,grade from student,sc where student.sno=sc.sno;

自身连接

为每个表取别名以区分
select first.cno,second.cpno from course first,course second where first.cpno=second.cno;

Microsoft Windows [版本 10.0.17134.765]
(c) 2018 Microsoft Corporation。保留所有权利。

C:\Users\袁一博>mysql -u root -p
Enter password: *************
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

C:\Users\袁一博>mysql -u root -p
Enter password: *************
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select sno,sno from sc group by cno,sno;
ERROR 1046 (3D000): No database selected
mysql> use school;
Database changed
mysql> select sno,sno from sc group by cno,sno;
+-------+-------+
| sno   | sno   |
+-------+-------+
| 95001 | 95001 |
| 95005 | 95005 |
| 95011 | 95011 |
| 95001 | 95001 |
| 95002 | 95002 |
| 95003 | 95003 |
| 95005 | 95005 |
| 95001 | 95001 |
| 95002 | 95002 |
| 95004 | 95004 |
| 95005 | 95005 |
| 95001 | 95001 |
| 95004 | 95004 |
| 95005 | 95005 |
+-------+-------+
14 rows in set (0.05 sec)

mysql> select sno,cno from sc group by sno,cno;
+-------+-----+
| sno   | cno |
+-------+-----+
| 95001 | 1   |
| 95005 | 1   |
| 95011 | 1   |
| 95001 | 2   |
| 95002 | 2   |
| 95003 | 2   |
| 95005 | 2   |
| 95001 | 3   |
| 95002 | 3   |
| 95004 | 3   |
| 95005 | 3   |
| 95001 | 4   |
| 95004 | 4   |
| 95005 | 4   |
+-------+-----+
14 rows in set (0.00 sec)

mysql> select cno,sno from sc group by cno,sno;
+-----+-------+
| cno | sno   |
+-----+-------+
| 1   | 95001 |
| 1   | 95005 |
| 1   | 95011 |
| 2   | 95001 |
| 2   | 95002 |
| 2   | 95003 |
| 2   | 95005 |
| 3   | 95001 |
| 3   | 95002 |
| 3   | 95004 |
| 3   | 95005 |
| 4   | 95001 |
| 4   | 95004 |
| 4   | 95005 |
+-----+-------+
14 rows in set (0.00 sec)

mysql> select sage from student group by sage,sdept;
+------+
| sage |
+------+
|   21 |
|   23 |
|   19 |
|   20 |
|   20 |
|   19 |
|   24 |
|   23 |
|   19 |
+------+
9 rows in set (0.01 sec)

mysql> select sdept,ssex,avg(sage) from student group by sdept,ssex;
+-------+------+-----------+
| sdept | ssex | avg(sage) |
+-------+------+-----------+
| CS    | 男   |   20.0000 |
| IS    | 男   |   21.0000 |
| MA    | 男   |   22.0000 |
| CS    | 女   |   20.0000 |
| IS    | 女   |   20.6667 |
+-------+------+-----------+
5 rows in set (0.01 sec)

mysql> select sdept,ssex,avg(sage) from student group by sdept,ssex order by sdept desc;
+-------+------+-----------+
| sdept | ssex | avg(sage) |
+-------+------+-----------+
| MA    | 男   |   22.0000 |
| IS    | 女   |   20.6667 |
| IS    | 男   |   21.0000 |
| CS    | 女   |   20.0000 |
| CS    | 男   |   20.0000 |
+-------+------+-----------+
5 rows in set (0.01 sec)

mysql> select sdept,ssex,avg(sage) from student group by sdept,ssex having avg(sage)>19 order by sdept desc,ssex desc;
+-------+------+-----------+
| sdept | ssex | avg(sage) |
+-------+------+-----------+
| MA    | 男   |   22.0000 |
| IS    | 男   |   21.0000 |
| IS    | 女   |   20.6667 |
| CS    | 男   |   20.0000 |
| CS    | 女   |   20.0000 |
+-------+------+-----------+
5 rows in set (0.01 sec)

mysql> select sdept,ssex,avg(sage) from student group by sdept,ssex order by avg(sage) asc;
+-------+------+-----------+
| sdept | ssex | avg(sage) |
+-------+------+-----------+
| CS    | 女   |   20.0000 |
| CS    | 男   |   20.0000 |
| IS    | 女   |   20.6667 |
| IS    | 男   |   21.0000 |
| MA    | 男   |   22.0000 |
+-------+------+-----------+
5 rows in set (0.01 sec)

mysql> select sdept,ssex,avg(sage) from student group by sdept,ssex having avg(sage)>19 order by sdept desc,ssex asc;
+-------+------+-----------+
| sdept | ssex | avg(sage) |
+-------+------+-----------+
| MA    | 男   |   22.0000 |
| IS    | 女   |   20.6667 |
| IS    | 男   |   21.0000 |
| CS    | 女   |   20.0000 |
| CS    | 男   |   20.0000 |
+-------+------+-----------+
5 rows in set (0.00 sec)

mysql> select student.*, SC.* from student,SC where student.sno=SC.sno;
+-------+--------+------+------+-------+-------+-----+-------+
| Sno   | Sname  | Ssex | Sage | Sdept | sno   | cno | grade |
+-------+--------+------+------+-------+-------+-----+-------+
| 95001 | 张三   | 男   |   21 | CS    | 95001 | 1   |    92 |
| 95001 | 张三   | 男   |   21 | CS    | 95001 | 2   |    94 |
| 95001 | 张三   | 男   |   21 | CS    | 95001 | 3   |    90 |
| 95001 | 张三   | 男   |   21 | CS    | 95001 | 4   |    97 |
| 95002 | 李四   | 男   |   23 | IS    | 95002 | 2   |    90 |
| 95002 | 李四   | 男   |   23 | IS    | 95002 | 3   |    80 |
| 95003 | 王五   | 男   |   19 | MA    | 95003 | 2   |  NULL |
| 95004 | 马六   | 女   |   20 | CS    | 95004 | 3   |  NULL |
| 95004 | 马六   | 女   |   20 | CS    | 95004 | 4   |    87 |
| 95005 | 苏三   | 女   |   20 | IS    | 95005 | 1   |    90 |
| 95005 | 苏三   | 女   |   20 | IS    | 95005 | 2   |    98 |
| 95005 | 苏三   | 女   |   20 | IS    | 95005 | 3   |    90 |
| 95005 | 苏三   | 女   |   20 | IS    | 95005 | 4   |    89 |
| 95011 | 张成民 | 男   |   19 | CS    | 95011 | 1   |  NULL |
+-------+--------+------+------+-------+-------+-----+-------+
14 rows in set (0.01 sec)

mysql> select student.sno,sname,ssex,sage,sdept,cno,grade from student,sc where student.sno=sc.sno
    -> ;
+-------+--------+------+------+-------+-----+-------+
| sno   | sname  | ssex | sage | sdept | cno | grade |
+-------+--------+------+------+-------+-----+-------+
| 95001 | 张三   | 男   |   21 | CS    | 1   |    92 |
| 95001 | 张三   | 男   |   21 | CS    | 2   |    94 |
| 95001 | 张三   | 男   |   21 | CS    | 3   |    90 |
| 95001 | 张三   | 男   |   21 | CS    | 4   |    97 |
| 95002 | 李四   | 男   |   23 | IS    | 2   |    90 |
| 95002 | 李四   | 男   |   23 | IS    | 3   |    80 |
| 95003 | 王五   | 男   |   19 | MA    | 2   |  NULL |
| 95004 | 马六   | 女   |   20 | CS    | 3   |  NULL |
| 95004 | 马六   | 女   |   20 | CS    | 4   |    87 |
| 95005 | 苏三   | 女   |   20 | IS    | 1   |    90 |
| 95005 | 苏三   | 女   |   20 | IS    | 2   |    98 |
| 95005 | 苏三   | 女   |   20 | IS    | 3   |    90 |
| 95005 | 苏三   | 女   |   20 | IS    | 4   |    89 |
| 95011 | 张成民 | 男   |   19 | CS    | 1   |  NULL |
+-------+--------+------+------+-------+-----+-------+
14 rows in set (0.00 sec)

mysql> select first.cno,second.cpno from course first,course second where first.cpno=second.cno;
+-----+------+
| cno | cpno |
+-----+------+
| 2   | 4    |
| 3   | NULL |
+-----+------+
2 rows in set (0.00 sec)

mysql> select first.cno,second.cpno from course first left join course second on (first.cpno=second.cno);
+-----+------+
| cno | cpno |
+-----+------+
| 1   | NULL |
| 2   | 4    |
| 3   | NULL |
| 4   | NULL |
+-----+------+
4 rows in set (0.00 sec)

mysql> select first.cno,second.cpno from course first right join course second on (first.cpno=second.cno);
+------+------+
| cno  | cpno |
+------+------+
| 2    | 4    |
| 3    | NULL |
| NULL | NULL |
| NULL | 3    |
+------+------+
4 rows in set (0.00 sec)

mysql> select student.sno,sname from student,sc where student.sno=sc.cno and cno='2'and grade>90;
Empty set (0.00 sec)

mysql> select student.sno,sname from student,sc ,course where student.sno=sc.cno and cno='2'and grade>90;
ERROR 1052 (23000): Column 'cno' in where clause is ambiguous
mysql> select student.sno,sname from student,sc,course where student.sno=sc.cno and cno='2'and grade>90;
ERROR 1052 (23000): Column 'cno' in where clause is ambiguous
mysql> select student.sno,sname,cname,grade from student,sc ,course where student.sno=sc.sno and sc.sno=course.cno;
Empty set (0.00 sec)

mysql> select sname from student where sno in(select sno from sc where cno='2');
+-------+
| sname |
+-------+
| 张三  |
| 李四  |
| 王五  |
| 苏三  |
+-------+
4 rows in set (0.00 sec)

mysql> select * from student;
+-------+----------+------+------+-------+
| Sno   | Sname    | Ssex | Sage | Sdept |
+-------+----------+------+------+-------+
| 95001 | 张三     | 男   |   21 | CS    |
| 95002 | 李四     | 男   |   23 | IS    |
| 95003 | 王五     | 男   |   19 | MA    |
| 95004 | 马六     | 女   |   20 | CS    |
| 95005 | 苏三     | 女   |   20 | IS    |
| 95006 | 刘七     | 女   |   19 | IS    |
| 95007 | 刘三姐   | 女   |   23 | IS    |
| 95008 | 欧阳锋   | 男   |   24 | MA    |
| 95009 | 欧阳大侠 | 男   |   23 | MA    |
| 95010 | 陈冬     | 男   |   19 | IS    |
| 95011 | 张成民   | 男   |   19 | CS    |
+-------+----------+------+------+-------+
11 rows in set (0.01 sec)

mysql> select student.sno,sname,cname,grade from student,sc ,course where student.sno=sc.sno and sc.cno=course.cno;
+-------+--------+-----------+-------+
| sno   | sname  | cname     | grade |
+-------+--------+-----------+-------+
| 95001 | 张三   | MATH      |    92 |
| 95005 | 苏三   | MATH      |    90 |
| 95011 | 张成民 | MATH      |  NULL |
| 95001 | 张三   | DB_DESIGN |    94 |
| 95002 | 李四   | DB_DESIGN |    90 |
| 95003 | 王五   | DB_DESIGN |  NULL |
| 95005 | 苏三   | DB_DESIGN |    98 |
| 95001 | 张三   | P_DESIGN  |    90 |
| 95002 | 李四   | P_DESIGN  |    80 |
| 95004 | 马六   | P_DESIGN  |  NULL |
| 95005 | 苏三   | P_DESIGN  |    90 |
| 95001 | 张三   | OS        |    97 |
| 95004 | 马六   | OS        |    87 |
| 95005 | 苏三   | OS        |    89 |
+-------+--------+-----------+-------+
14 rows in set (0.00 sec)

mysql> select student.sno,sname from student,sc where student.sno=sc.sno and cno='2'and grade>90;
+-------+-------+
| sno   | sname |
+-------+-------+
| 95001 | 张三  |
| 95005 | 苏三  |
+-------+-------+
2 rows in set (0.00 sec)

mysql> select sno sname,sdept from student where sdept in(select sdept from student where sname='刘三姐');
+-------+-------+
| sname | sdept |
+-------+-------+
| 95002 | IS    |
| 95005 | IS    |
| 95006 | IS    |
| 95007 | IS    |
| 95010 | IS    |
+-------+-------+
5 rows in set (0.01 sec)

mysql> select s1.sno,s1.sname,s1.sdept from studnet s1,studnet s2;
ERROR 1146 (42S02): Table 'school.studnet' doesn't exist
mysql> select s1.sno,s1.sname,s1.sdept from student s1,student s2;
+-------+----------+-------+
| sno   | sname    | sdept |
+-------+----------+-------+
| 95001 | 张三     | CS    |
| 95002 | 李四     | IS    |
| 95003 | 王五     | MA    |
| 95004 | 马六     | CS    |
| 95005 | 苏三     | IS    |
| 95006 | 刘七     | IS    |
| 95007 | 刘三姐   | IS    |
| 95008 | 欧阳锋   | MA    |
| 95009 | 欧阳大侠 | MA    |
| 95010 | 陈冬     | IS    |
| 95011 | 张成民   | CS    |
| 95001 | 张三     | CS    |
| 95002 | 李四     | IS    |
| 95003 | 王五     | MA    |
| 95004 | 马六     | CS    |
| 95005 | 苏三     | IS    |
| 95006 | 刘七     | IS    |
| 95007 | 刘三姐   | IS    |
| 95008 | 欧阳锋   | MA    |
| 95009 | 欧阳大侠 | MA    |
| 95010 | 陈冬     | IS    |
| 95011 | 张成民   | CS    |
| 95001 | 张三     | CS    |
| 95002 | 李四     | IS    |
| 95003 | 王五     | MA    |
| 95004 | 马六     | CS    |
| 95005 | 苏三     | IS    |
| 95006 | 刘七     | IS    |
| 95007 | 刘三姐   | IS    |
| 95008 | 欧阳锋   | MA    |
| 95009 | 欧阳大侠 | MA    |
| 95010 | 陈冬     | IS    |
| 95011 | 张成民   | CS    |
| 95001 | 张三     | CS    |
| 95002 | 李四     | IS    |
| 95003 | 王五     | MA    |
| 95004 | 马六     | CS    |
| 95005 | 苏三     | IS    |
| 95006 | 刘七     | IS    |
| 95007 | 刘三姐   | IS    |
| 95008 | 欧阳锋   | MA    |
| 95009 | 欧阳大侠 | MA    |
| 95010 | 陈冬     | IS    |
| 95011 | 张成民   | CS    |
| 95001 | 张三     | CS    |
| 95002 | 李四     | IS    |
| 95003 | 王五     | MA    |
| 95004 | 马六     | CS    |
| 95005 | 苏三     | IS    |
| 95006 | 刘七     | IS    |
| 95007 | 刘三姐   | IS    |
| 95008 | 欧阳锋   | MA    |
| 95009 | 欧阳大侠 | MA    |
| 95010 | 陈冬     | IS    |
| 95011 | 张成民   | CS    |
| 95001 | 张三     | CS    |
| 95002 | 李四     | IS    |
| 95003 | 王五     | MA    |
| 95004 | 马六     | CS    |
| 95005 | 苏三     | IS    |
| 95006 | 刘七     | IS    |
| 95007 | 刘三姐   | IS    |
| 95008 | 欧阳锋   | MA    |
| 95009 | 欧阳大侠 | MA    |
| 95010 | 陈冬     | IS    |
| 95011 | 张成民   | CS    |
| 95001 | 张三     | CS    |
| 95002 | 李四     | IS    |
| 95003 | 王五     | MA    |
| 95004 | 马六     | CS    |
| 95005 | 苏三     | IS    |
| 95006 | 刘七     | IS    |
| 95007 | 刘三姐   | IS    |
| 95008 | 欧阳锋   | MA    |
| 95009 | 欧阳大侠 | MA    |
| 95010 | 陈冬     | IS    |
| 95011 | 张成民   | CS    |
| 95001 | 张三     | CS    |
| 95002 | 李四     | IS    |
| 95003 | 王五     | MA    |
| 95004 | 马六     | CS    |
| 95005 | 苏三     | IS    |
| 95006 | 刘七     | IS    |
| 95007 | 刘三姐   | IS    |
| 95008 | 欧阳锋   | MA    |
| 95009 | 欧阳大侠 | MA    |
| 95010 | 陈冬     | IS    |
| 95011 | 张成民   | CS    |
| 95001 | 张三     | CS    |
| 95002 | 李四     | IS    |
| 95003 | 王五     | MA    |
| 95004 | 马六     | CS    |
| 95005 | 苏三     | IS    |
| 95006 | 刘七     | IS    |
| 95007 | 刘三姐   | IS    |
| 95008 | 欧阳锋   | MA    |
| 95009 | 欧阳大侠 | MA    |
| 95010 | 陈冬     | IS    |
| 95011 | 张成民   | CS    |
| 95001 | 张三     | CS    |
| 95002 | 李四     | IS    |
| 95003 | 王五     | MA    |
| 95004 | 马六     | CS    |
| 95005 | 苏三     | IS    |
| 95006 | 刘七     | IS    |
| 95007 | 刘三姐   | IS    |
| 95008 | 欧阳锋   | MA    |
| 95009 | 欧阳大侠 | MA    |
| 95010 | 陈冬     | IS    |
| 95011 | 张成民   | CS    |
| 95001 | 张三     | CS    |
| 95002 | 李四     | IS    |
| 95003 | 王五     | MA    |
| 95004 | 马六     | CS    |
| 95005 | 苏三     | IS    |
| 95006 | 刘七     | IS    |
| 95007 | 刘三姐   | IS    |
| 95008 | 欧阳锋   | MA    |
| 95009 | 欧阳大侠 | MA    |
| 95010 | 陈冬     | IS    |
| 95011 | 张成民   | CS    |
+-------+----------+-------+
121 rows in set (0.00 sec)

mysql> select s1.sno,s1.sname,s1.sdept from student s1,student s2 where sname='
    '> 刘三姐';
ERROR 1052 (23000): Column 'sname' in where clause is ambiguous
mysql> select s1.sno,s1.sname,s1.sdept from student s1,student s2 where sdept IN(select sdept from student where snmae='刘三姐');
ERROR 1054 (42S22): Unknown column 'snmae' in 'where clause'
mysql> select s1.sno,s1.sname,s1.sdept from student s1,student s2 where sdept IN(select sdept from student where sname='刘三姐');
ERROR 1052 (23000): Column 'sdept' in IN/ALL/ANY subquery is ambiguous
mysql> select student.sno,sname from student,course ,SC where student.sno=sc.sno and sc.cno=course.cno and course.cname='DB_DESIGN';
+-------+-------+
| sno   | sname |
+-------+-------+
| 95001 | 张三  |
| 95002 | 李四  |
| 95003 | 王五  |
| 95005 | 苏三  |
+-------+-------+
4 rows in set (0.00 sec)

mysql> select sno,cno from sc x where grade>(select avg(grade) from sc y where y.sno=x.sno);
+-------+-----+
| sno   | cno |
+-------+-----+
| 95001 | 2   |
| 95001 | 4   |
| 95002 | 2   |
| 95005 | 2   |
+-------+-----+
4 rows in set (0.00 sec)

mysql> select sname from student where exists(select * from sc where sno=student.sno and cno='1');
+--------+
| sname  |
+--------+
| 张三   |
| 苏三   |
| 张成民 |
+--------+
3 rows in set (0.00 sec)

mysql> select sname from student where not exists(select * from sc where sno=student.sno and cno='1');
+----------+
| sname    |
+----------+
| 李四     |
| 王五     |
| 马六     |
| 刘七     |
| 刘三姐   |
| 欧阳锋   |
| 欧阳大侠 |
| 陈冬     |
+----------+
8 rows in set (0.01 sec)

mysql> select sname from student where not exists(select * from course where not exists (select * from sc where sno=student.sno and cno=course.cno));
+-------+
| sname |
+-------+
| 张三  |
| 苏三  |
+-------+
2 rows in set (0.00 sec)

mysql> select distinct sno from sc scx where not exists (select * from sc scy where scy.sno='95002'and not exists (select * from sc scz where scz.sno=scx.sno and scz.cno=scy.cno));
+-------+
| sno   |
+-------+
| 95001 |
| 95002 |
| 95005 |
+-------+
3 rows in set (0.01 sec)

mysql>