mysql 数据库查询与实例。

时间:2024-01-30 22:06:19

资料是从教材弄下来的,加上了我的理解。主要内容是练习实例,在写博文中学习命令行,当然也希望这篇博文能帮助其他人学习mysq数据库命令

 

SELECT 语句可以从一个或多个表中选取特定的行和列

   SELECT                            指定要查询的列名称,列与列之间用逗号隔开。

    [ALL|DISTINCT]             ALL(默认)显示所有行,包括重复行   |   DISTINCT   消除重复行

    [FROM表名[,表名]……]      指定要查询的表,可以指定两个以上的表,表与表之间用逗号隔开。

    [WHERE子句]                   指定要查询的条件。where子句必须紧跟FROM子句之后。

                                             列出了过滤条件类型和用于过滤数据的条件。

                                           

 

 [GROUP BY子句]               子句用于对查询结构进行分组。

    [HAVING子句]                  指定分组的条件,通常在GROUP BY子句之后。

    [ORDER  BY子句]             用于对查询结果进行排序。【asc(升序) | desc(降序)】

    [LIMIT子句]                     限制查询的输出结果行。

 

下面是实例 

1.在学生表(students)查询学生的学号(s_no),姓名(s_name),联系电话(phone)

select s_no,s_name,phone from students;

运行结果:

 

 

2.查询(students)表中学生所在系部(d_no),去掉重复值。SQL语句 如下

select  d_no distinct from  students;

这是加了distinct(去掉重复行)语句的图

 

这是没有加distinct语句的(形成了鲜明的对比)

 

 

3.从students表统计男生(sex)的学生人数,SQL语句如下。

select count(*) as 男生人数 from students where sex="";

运行结果:

count()为计算数量的函数,*号是通配符 , as ** 是注释

 

 

4.查询学生的姓名(students表,s_name)、系部名称(department表,d_name)和联系地址(students表,address),SQL语句如下

select S_NAME as ‘姓名’,D_NAME as ‘系部’,address as‘地址’ from students,departments where students.d_no = departments.d_no;

多表查询,FROM后面的各个表用逗号隔开  WHERE后面指明表与表之间的全连接,因为这是2张表,而他们有相同的列名,必须进行表与表的链接。不然会得到错误数据

(多表连接会在第23例开始介绍)

 

 

5.在成绩表(score)中查询选修了A001课程(c_no)且成绩(report)在80分以上的学生,SQL语句如下。

select * from score where c_no="A001" and report > 80;

有2个或以上的条件时,条件与条件直接用 and链接

 

 

6.在students表中查询出生日期(birthday)在1992年5月出生的学生。

select * from students where birthday between "1992-5-1" and \'1992-5-31\';

between是区间,范围的意思

运行效果如图

 

 

7.在students表中查询院系编号(d_no)为D001或D002的学生。

select * from students where d_no in(\'d001\',\'d002\');

用in去查找 d_no 表中的数据,有几个再加逗号就行了

 

 

8.在students表中查询电话(phone)不为空的学生信息。

select * from students where phone is not null;

查询结果

真正的空值是会显示NULL的,有一个空格都不算null

 

 

9.在students表中查询姓(s_name)李的学生信息。

select * from students where s_name like\'李%\';

 

其中like里面的符号的可能值(有点像正则表达式0.0)

 

 

10.在students表中查询住址(address)在北京路的学生信息。

select * from students where address like\'%北京%\';

 

 

11.在students表中查询姓名(s_name)是两位字符的学生信息

select * from students where s_name like \'__\';

一个\'_\'代表一个字符、

运行结果

这样就能查找到名字是2位的数据了

 

  GROUP BY子句主要根据字段对行分组。例如,根据学生所学的专业对STUDENTS表中的所有行分组,结果是每个专业的学生成为一组。

下面的例子难度稍稍加大了。

 

12.在students表中按系(d_no)统计各系的学生人数,SQL语句如下

select d_no as \'系别\' count(*) as \'各系人数\' from students group by d_no;

 

 

13.在students表中统计各系(d_no)男女生(sex)人数。SQL语句如下。

select d_no as \'系别\', sex as \'性别\' ,count(*) as \'人数\' from students group by d_no,sex;

可以指定多列分组。若指定多列分组,则先按指定的第一列分组再对指定的第二列分组,以此类推。

 

 

14.在score表中求选修的各门课程(c_no)的平均成绩(report)和选修该课程的人数

select c_no, count(c_no) as \'选修人数\', avg(report) as \'各系平均成绩\' from score group by c_no;

运行结果

因为求的是各部门(c_no)的平均成绩,所以以c_no为分组依据group by c_no

 

 

15.在teachers表中统计各系(d_no)教师人数,包括汇总行。SQL语句如下。

 

select d_no as \'系别\', count(*) as \'人数\' from teachers group by d_no with rollup;

 

null 20为人数汇总 教师人数20个

使用带ROLLUP操作符的GROUP BY子句: 在结果集内不仅包含由GROUP BY提供的正常行,还包含汇总行。

 

 

16.在score表中按成绩(report)降序排序列出选修AOO1课程(c_no)的学生学号(s_no)和成绩,SQL语句如下。

select s_no as \'学号\', c_no as \'课程\' ,report as\'成绩\' from score where c_no=\'A001\' order by report desc;

运行结果

默认是升序,当需求是升序时 可以不写order by 列名 asc ,当需求是降序时。加上 order by 列名 desc         

 

17.在students表中按系部(d_id)和出生日期(birthday)排序,SQL语句如下。

select * from students order by 5,4 desc;

运行结果

ORDER BY 5,4中的5表示第5列(d_NO),4表示第4列(birthday)。(偷懒的写法,如果你对表的结构非常熟悉的话);desc 为降序 

 

 认识HAVING子句

  使用HAVING子句的目的与WHERE子句类似,不同的是WHERE子句是用来在FROM子句之后选择行,而HAVING子句用来在GROUP BY子句后选择行。语法格式如同WHERE。

 

18.列出在score表中选修了2门以上课的学生学号(s_no)。

SELECT s_no
FROM score
GROUP BY s_no
HAVING COUNT( * ) >2

having说白了和where 是一个作用,having 用在group by ** 后面 。而where更在 from ** 后面。但具体用法还是有区别的,比如多表连接,只能用where。而用having会报错。我用实例4和其他多表连接实例都试过了,得出了这个结论。(或许可以研究下围绕这个where 和having的区别 再写一篇博文^_^!)

 

认识LIMIT子句

  Limit子句主要用于限制被SELECT语句返回的行数。

 语法格式:LIMIT {【偏移量,】 行数 | 行数OFFSET偏移量}

 

19.在score表中查询课程号(c_no)为‘A101’成绩(report)前五名的学生(s_no)

SELECT S_NO AS \'学号\', REPORT AS \'分数\'
FROM SCORE
WHERE C_NO =  \'A001\'
ORDER BY REPORT DESC ;

 

20.在 score表中查询成绩(report)第5名至第10名的学生(s_no)。

SELECT S_NO AS\'学号\'
FROM SCORE
ORDER BY REPORT
LIMIT 4 , 10

 

 

COUNT(*)计算所有记录的数量,也包括空值所在的行。使用count(*) 会最快返回结果。而CONUT(列名)则只计算列的数量,不计该列中的空值。

  同样,AVG、MAX、MIN和SUM函数也不计空的列值。即不把空值所在行计算在内,只对列中的非空值进行计算。

 
 

21.在score表中求课程(c_no)为A001的最高分、最低分(report)。

select max(report) as \'最高分\', min(report) as \'最低分\' from score group by c_no having c_no =\'a001\';

运行结果

 

 

22.在score表中求各课程(c_no)选修的学生人数

select c_no as \'课程\',count(*) as \'人数\' from score group by c_no

 

 

多表连接查询

数据库的设计原则是精简,通常是每个表尽可能单一,存放不同的数据,最大限度减少数据冗余。而在实际工作,需要从多个表查询出用户需要的数据并生成一个临时结果,这就是连接查询。

当查询的数据来源于2个及以上表时,可用全连接、JOIN连接或子查询来实现

多表查询实际上通过各个表之间的共同列的关联性来查询数据。连接的方式是将各个表用逗号分隔,用WHERE子句设定条件进行等值连接,这样就指定了全连接。

格式如下。

   SELECT表名.列名【,...n】

     FROM表1【,...n】

     WHERE {连接条件AND |OR查询条件}

 

可能会从这开始懵逼 因为你没有我的数据库,而且关联的表太多 看晕 懵逼不要紧 记住一点 要观察表与表之间有什么相同的键 然后把他们关联起来就可以了 。不要纠结我的实例

23.查找course表和score表中所有学生选过的课程名(c_name)和课程号(c_no)

SELECT DISTINCT COURSE.c_no,COURSE.c_name 
FROM COURSE,SCORE 
WHERE COURSE.c_no=SCORE.c_no;

 

24.查询信息学院学生所选修的课程(c_no)和成绩(report)。涉及 students,score,course,departments  这4个表

SELECT course.c_no, score.report
FROM STUDENTS, score, course, departments
WHERE STUDENTS.s_no = score.s_no
AND course.c_no = score.c_no
AND STUDENTS.d_no = departments.d_no
AND departments.d_name =  \'信息学院\'

 

25.查询选修了“陈静”老师课程的学生

SELECT score.s_no, course.c_no, t_name
FROM teachers, teach, course, score
WHERE course.c_no = score.c_no
AND teach.c_no = course.c_no
AND teachers.t_no = teach.t_no
AND teachers.t_name =  \'陈静\'

 

JOIN连接