数据库(四)——数据查询

数据库的重点就在于数据的查询:

SQL查询语句基本格式:

select 属性列表 from 表名 [where 条件表达式]
group by 属性名 [Having 条件表达式2]
[order by 属性 [ASC][DESC]]

一、单表查询:

  • 带in的子查询:
select 属性列表 from 表名 where [not] in (元素1,元素2...);

示例:select * from Student where SID not in(1,2,3);
  • 带between and 的范围查询:
select 属性列表 from 表名 where [not] between 取值1 and 取值2;
实例:select * from Student where Sage between 20 and 21;
  • 带like的通配符匹配(模糊查询)
select 属性列表 from 表名 where like '字符串'  //字符串包括字符和通配符
      通配符:
      % : 表示0个或任意长度的字符串
      _ : 表示单个字符
实例:select * from Student where Sname like '%n%';
  • 空值查询
select 属性列表 from 表名 where is [not] null;
实例:select * from Student where Sname is not null;
  • 带and的多条件查询
select 属性列表 from 表名 where 条件表达式1 and 条件表达式2;
实例:select * from Student where Ssex like 'nan' and Sage=20;  
  • 带or的多条件查询
select 属性列表 from 表名 where 条件表达式1 or 条件表达式2;
实例:select * from Student where SID=5 or Sname like 'zhaolei';
  • 去重
select distinct 属性名 from 表名
  • 对结果排序
select 属性列表 from 表名 order by 属性名 ASC|DESC   //默认升序排序;
实例:select * from Student order by Sage DESC;
  • 分组查询
select * from 表名 group by 属性名 [having 条件表达式];
实例: select avg(sc.score) as '平均成绩' from sc sc group by sid;
  • 分页查询
limit 数据        //只有一个参数,表示不指定起始位置为limit数据
limit 数据1,数据2    //数据1表示起始位置,数据2表示偏移量

二、SQL提供的函数:

几个常用的:

  • count(); 计数使用
  • Sum();求和;
  • max();最大值;
  • min();最小值;
  • avg();平均值;

其它数据库函数

三、取别名:

属性取别名:对应的参数后面直接跟一个别名即可,或者再对应的参数后面as然后跟别名;

属性名 [as] 别名

表取别名:

表名 [as] 别名

四、多表联合查询

㈠ 内查询

内连接:把两个表中同时存在的数据打印出来;

第一种写法:

select * from 表1 inner join 表2 on 表1.属性 = 表2.属性

第二种写法:

例:

select * from 表1 inner join 表2 on 表1.属性 = 表2.属性 where 表名.属性 = 查询条件

㈡ 外查询

外连接

①左连接:以左边的表为准,效果参看右连接;

select * from 表1 left join 表2 on 表1.属性 = 表2.属性

②右连接:以右边表为准,把右边表的所有数据都显示出来,对应的左边的如果没有数据打印null,如果有就将对应的数据直接打印,其他的左表而右表没有的数据就丢失了;

select * from 表1 right join 表2 on 表1.属性 = 表2.属性

五、实际问题

1、已知数据:

1、学生表
Student(SID,Sname,Sage,Ssex) 
--SID 学生编号,Sname 学生姓名,Sage 年龄,Ssex 学生性别

2.课程表
Course(CID,Cname,TID) 
--CID --课程编号,Cname 课程名称,TID 教师编号

3.教师表
Teacher(TID,Tname) 
--TID 教师编号,Tname 教师姓名

4.成绩表
SC(SID,CID,score) 
--SID 学生编号,CID 课程编号,score 分数

学生表:
编号  姓名  年龄  性别
1    赵雷  20    男
2    钱电  20    男
3    孙风  21    男
4    吴兰  18    女
5    孙兰  17    女
课程表:
课程号  课程名称  教师编号
1       语文     2
2       数学     1
3       英语     3
教师表:
教师编号   教师名
1         张三
2         李四
3         王五
成绩表:
学生编号   课程编号   分数
1         1         80
1         2         71
1         3         87
2         1         88
2         2         70
2         3         89
3         1         68
3         2         78
3         3         87
4         1         67
4         2         58
4         3         89
5         1         56
5         2         89
6         3         38

按照上面已知条件创建的表如下:

mysql> show tables;
+-----------------+
| Tables_in_test4 |
+-----------------+
| course          |
| sc              |
| student         |
| teacher         |
+-----------------+
4 rows in set (0.37 sec)

mysql> select * from student;
+-----+-------+------+------+
| sid | sname | sage | ssex |
+-----+-------+------+------+
| 1   | 赵雷  |   20 | 男   |
| 2   | 前殿  |   20 | 男   |
| 3   | 孙风  |   21 | 男   |
| 4   | 吴兰  |   18 | 女   |
| 5   | 孙兰  |   17 | 女   |
+-----+-------+------+------+
5 rows in set (0.06 sec)

mysql> select * from teacher;
+-----+-------+
| tid | tname |
+-----+-------+
| 1   | 张三  |
| 2   | 李四  |
| 3   | 王五  |
+-----+-------+
3 rows in set (0.00 sec)

mysql> select * from course;
+-----+-------+-----+
| cid | cname | tid |
+-----+-------+-----+
| 1   | 语文  | 2   |
| 2   | 数学  | 1   |
| 3   | 英语  | 3   |
+-----+-------+-----+
3 rows in set (0.00 sec)

mysql> select * from sc;
+-----+------+-------+
| sid | cid  | score |
+-----+------+-------+
| 1   | 1    | 80    |
| 1   | 2    | 71    |
| 1   | 3    | 87    |
| 2   | 1    | 88    |
| 2   | 2    | 70    |
| 2   | 3    | 89    |
| 3   | 1    | 68    |
| 3   | 2    | 78    |
| 3   | 3    | 87    |
| 4   | 1    | 67    |
| 4   | 2    | 58    |
| 4   | 3    | 89    |
| 5   | 1    | 56    |
| 5   | 2    | 89    |
| 5   | 3    | 38    |
+-----+------+-------+
15 rows in set (0.00 sec)

mysql>

常见问题及解决SQL语句:

  • 查询"01"课程比"02"课程成绩高的学生的信息及课程分数
select a.*, b.score, c.score
from student a, sc b,sc c
where a.sid = b.sid and a.sid = c.sid
and b.cid ='1' and c.cid = '2'
and b.score > c.score;
  • 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
//这是取得同一个学生的所有成绩
select a.sid,a.sname,sc.score from student a,sc sc where a.sid=sc.sid;

select stu.sid,stu.sname,avg(sc.score) from student stu,sc sc
where stu.sid = sc.sid group by sc.sid having avg(sc.score) >= 60;
  • 查询在sc表中不存在成绩的学生信息。
select * from student where sid not in (select distinct sid from sc);
  • 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select a.sid,a.sname,count(b.cid),sum(b.score) from student a,
sc b where a.sid = b.sid group by a.sid;
  • 查询"李"姓老师的数量
select count(tid)
from teacher
where tname like '李%';
  • 查询学过"张三"老师授课的同学的信息
select stu.* from student stu,sc sc,teacher t where
stu.sid = sc.sid and sc.cid = t.tid and sc.cid in(
select c.cid from teacher t,course c where t.tid = c.tid and tname = '张三');

或者:
select * from student where sid in(
select distinct sid from sc where cid in(
select distinct cid from sc where tid in(
select tid from teacher where tname like '张三')));
  • 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select stu.* from
student stu,sc a,sc b
where stu.sid = a.sid
and stu.sid = b.sid
and a.cid='1'
and b.cid='2';
  • 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
select s.sid,s.sname,avg(score) from student s,sc where s.sid = sc.sid 
and s.sid in(select sid from sc where score < 60 group by sid having
count(sid) >= 2) group by s.sid;
展开阅读全文

没有更多推荐了,返回首页