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

最新推荐文章于 2023-03-30 17:03:40 发布
最新推荐文章于 2023-03-30 17:03:40 发布
阅读量186 收藏
点赞数
分类专栏： 学习笔记
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_41842192/article/details/90942627
版权

目录

单表查询

格式化查询

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>
JobYuanPioneer
关注
专栏目录
数据库课堂笔记---索引
JIENIXIAOWU的博客
09-17 519
1，索引的分类 单值索引：一个索引值只包含三个列，一张表可以有多个单值索引。 唯一索引：索引的列的值必须是唯一的，但允许有空值。 复合索引：一个索引可以包含多个列。 主键索引：id不能重复，并且不允许出现空值 2，索引的含义 索引是一种单独的、物理的，对数据库表中一列或多列的值进行排序的一种存储结构。 为了加速表中数据行的检索而创建的一种分散的数据结构。 3，索引的优缺点 优点： 快速获取数据； 保证数据记录的唯一性； 实现表与表之间的参照完整性； 使用group by 和order by 进行检索时利用索
MySQL-笔记-11.数据库设计
最新发布
爱睡觉的欧包
05-19 714
详细调查现实世界中要处理的对象（如组织、部门、企业等），充分了解该对象当前数据管理系统（手工系统或计算机系统）的工作概况，尽可能多地收集数据，明确用户的各种需求，然后在此基础上确定新系统的功能。与需求分析相反，概念结构设计采用自底向上、逐层设计与合并的方法，先设计底层的局部概念结构（局部 E-R 图），然后将它们逐层合并起来，最终形成完整的全局概念结构（全局 E-R 图）。“学生”实体集中的“院系”、“课程”实体集中的“开课院系”以及“教师”实体集中的“所属部门”实际上是同样的含义，即异名同义。
嵌入式数据库（六）
XuLujunCSDN
01-02 367
一、查询条件 查询所有年龄在20岁以下的学生姓名及其年龄  SELECT Sname, Sage  FROM Student   WHERE Sage 或：SELECT Sname, Sage  FROM Student WHERE NOT Sage >= 20  确定范围  ( BETWEEN…AND) BETWEEN … AND …      包括临
SQL中的模糊查询like
热门推荐
tanxinji的博客
07-03 1万+
首先我们创建一个Person表。 create table Person( cname varchar2(50), cage number(3) ); 插入一些数据: insert into Person (cname,cage) values('张三',19); insert into Person (cname,cage) values('张三丰',20); insert into Person (cname,cage) valu...
课堂笔记 - 数据库设计
chengjiexing7464的博客
06-17 1098
数据库设计 说在前面 项目开发的流程包括哪些环节 需求调研【需求调研报告】-- 公司决策层 根据市场公司需求分析公司是否需要开发软件来辅助日常工作 公司高层市场考察，市场分析，决定做什么软件。 不懂技术的人想象软件应该有什么功能，长...
数据库课堂笔记
lmj275871的博客
08-29 162
数据库
mysql数据库查询笔记_mysql数据库课堂笔记1
weixin_36357900的博客
01-28 102
mysql 常用命令：1.数据库的启动与关闭net start mysqlnet stop mysql2.进入数据库1)通过程序用mysql客户端连接进去数据库2)找到mysql的安装目录，例如C:\Program Files\MySQL\MySQL Server 5.0\bin然后输入 mysql -u root -p 回车后输入密码 root3.进入mysql后的常用命令：1)show dat...
SQL课堂笔记--设计数据库
Shmily13377的博客
11-29 505
2017.11.22 1.良好的数据库设计:1).节省数据的存储空间2).能够保证数据的不完整3).方便进行数据库应用系统的开发 2.糟糕的数据库设计:1).数据冗余,存储空间浪费2).内存空间浪费3).数据更新和插入异常数据冗余:指数据间的重复,也可以说是同一数据存储在不同数据文件中的现象(多个地方重复存储相同数...
3.30数据库课堂笔记
qq_53871862的博客
03-30 110
INSERT INTO student (id, student_name) VALUE (1, "小甜甜");student_gender varchar(255) comment"学生性别",student_name varchar(255) comment "学生名字",student_age varchar(255) comment"学生年龄"创建数据表 student (字段与字段之间用 逗号 隔开)看到 mysql> 提示符， 说明连接成功。1. 字段与字段之间用 逗号 隔开。
SQL课堂笔记--嵌套查询
Shmily13377的博客
11-16 690
2017.11.15六:嵌套查询嵌套查询概述:一个select-from-where 语句称为一个查询块将一个查询块嵌套在另一个查询块的where子句或having短语的条件中的查询称为嵌套查询例1:查询选修了2号课程得学生姓名:内连接:select sname from student,sc where student.sno=sc.sno and cno...
课堂笔记-数据库系统原理（计算机学院专业课）
04-01
### 数据库系统原理知识点概述 #### 一、数据库概述与分类 - **数据库定义**: 数据库(Database,简称DB)是一种组织、存储和处理数据的集合，它能够有效地管理和检索信息。 - **数据库目的**: 主要是为了帮助人们追踪...
MySQL数据库课堂笔记 + MySQL-DEMO
10-31
本资源包含了“MySQL数据库课堂笔记”和一个名为“MySQL-DEMO”的实践项目，旨在帮助学习者深入理解MySQL的基本概念、操作及实际应用。 “MySQL数据库课堂笔记”可能涵盖了以下知识点： 1. **数据库基础**：介绍...
886164823919662MySQL课堂笔记-day01.rar
12-23
MySQL是世界上最受欢迎的关系型数据库管理系统之...这份“MySQL课堂笔记-day01”很可能会涵盖这些内容，帮助初学者逐步掌握这个强大的数据库系统。通过实践和不断学习，可以成为一名熟练的MySQL数据库管理员或开发者。
课堂笔记-第一次课1
08-08
- **超大容量问题**：随着业务发展，单个数据库表的数据量可能达到千万甚至亿级别，这将导致查询效率下降，影响系统响应速度。 - **性能问题**：大量数据会导致索引效率降低，同时写操作可能会阻塞读操作，影响...
MySQL课堂笔记-day02.txt
01-24
主要讲解mysql的具体操作以及部分常用指令，适合想初步了解数据库的人学习
数据库---表的创建与属性修改
qq_41842192的博客
06-03 3178
显示指令 show databases;//显示所有数据库 show tables；//显示当前库下所有的表 show create table tableName;//显示tableName这张表的属性 select * from tableName;//显示tableName这张表所有的元组 describe tableName;//显示tableName这张表的属性 创建表 创建 //创建s...
为什么单片机只有可以整除8的特殊寄存器能够位寻址？
qq_41842192的博客
04-27 2697
这个问题困扰了我很久，上课没有好好听，网上搜别人讲的都是模模糊糊没有给出具体的原因。 我只好大胆猜想了。（以下是我的猜想，如果有错误，请指正） 我有一个猜测，就是这些寄存器就是被设计成可以位寻址的，被8整除只是为了好记。 我想了一下位寻址的实现方式，既然是内部，只有8个地址线，而要精确到位的话，就只能寻址256位。 而位寻址区占了128位，剩下128位，刚好给剩下的80H~FFH中能整除8的16个...
寻找最优初始学习率-Keras实现
qq_41842192的博客
02-04 2015
如何找到最优初始学习率 参考知乎某大牛的回答，原链接为：如何找到最优学习率 经过了大量炼丹的同学都知道，超参数是一个非常玄乎的东西，比如batch size，学习率等，这些东西的设定并没有什么规律和原因，论文中设定的超参数一般都是靠经验决定的。但是超参数往往又特别重要，比如学习率，如果设置了一个太大的学习率，那么loss就爆了，设置的学习率太小，需要等待的时间就特别长，那么我们是否有一个科学的办法...
用Git命令行找回之前的代码
qq_41842192的博客
07-05 1415
git log查看更改历史 复制想要恢复的时间的代码字符串s git reset --hard s(刚才复制的s) git reflog 查看s之前的版本信息
数据库原理与应用详解 - NJU 学长笔记
此外，笔记可能还会涉及到ER图（实体-关系图）在数据库设计中的应用，以及SQL语言用于数据查询和更新的基础知识。 数据库系统还强调了安全性、完整性、并发控制和恢复机制，这些都是DBMS为了保证数据正确性、一致性...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值