SQL语句高级(一)

一、
mysql> select cno,avg(degree),count(*)   //新表字段名 
from score 							//原始表
group by cno 						//以cno字段分组
having count(cno) >= 2       //该字段的数据总数大于2
and 										//条件与
cno like '3%';                       //模糊查询
+-------+-------------+----------+
| cno   | avg(degree) | count(*) |
+-------+-------------+----------+
| 3-105 |     86.0000 |        3 |
| 3-245 |     86.2000 |        5 |
+-------+-------------+----------+

二、
mysql> select sno,degree from score where degree between 70 and 90;
也可以写成:
mysql> select sno,degree from score where degree > 70 and degree < 90;
+-----+--------+
| sno | degree |
+-----+--------+
| 103 |     89 |
| 104 |     89 |
| 105 |     89 |
| 105 |     89 |
+-----+--------+

三、
mysql> select sno,sname from student;
+-----+--------+
| sno | sname  |
+-----+--------+
| 100 | Java   |
| 101 | C      |
| 102 | C++    |
| 103 | C#     |
| 104 | Python |
| 105 | JS     |
+-----+--------+
6 rows in set (0.00 sec)

mysql> select sno,cno,degree from score;
+-----+--------+--------+
| sno | cno    | degree |
+-----+--------+--------+
| 100 | 3-105  |    100 |
| 100 | 3-245  |     55 |
| 101 | 6-1166 |     59 |
| 102 | 3-245  |    100 |
| 103 | 3-105  |     99 |
| 103 | 3-245  |     89 |
| 103 | 6-1166 |     59 |
| 104 | 3-105  |     59 |
| 104 | 3-245  |     98 |
| 104 | 6-1166 |     89 |
| 105 | 3-245  |     89 |
| 105 | 6-1166 |     89 |
+-----+--------+--------+
12 rows in set (0.00 sec)
上面两条的综合查询(多表查询!!):

mysql> select sname,cno,degree from student,score where student.sno = score.sno;
+--------+--------+--------+
| sname  | cno    | degree |
+--------+--------+--------+
| Java   | 3-105  |    100 |
| Java   | 3-245  |     55 |
| C      | 6-1166 |     59 |
| C++    | 3-245  |    100 |
| C#     | 3-105  |     99 |
| C#     | 3-245  |     89 |
| C#     | 6-1166 |     59 |
| Python | 3-105  |     59 |
| Python | 3-245  |     98 |
| Python | 6-1166 |     89 |
| JS     | 3-245  |     89 |
| JS     | 6-1166 |     89 |
+--------+--------+--------+

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值