2019-7-29 [MySQL] 面试题x4 上机查询题x18

1. 主观题

1) 请说出连接查询的分类

1. 内连接
   内连接（inner join 或 join）
   内连接是等值连接，它使用“=、>、<、<>”等运算符根据每个表共有的列的值匹配两个表中的行

   查询语句：

select * from T_Person inner join T_Card on T_Person.CardId = T_Card.CardId

2. 外连接
   1.左连接（left join 或 left outer join）
   左连接又称左向外连接，查询的结果集包括SQL语句中左表的所有行，右表中匹配的行。如果左表的某行在右表中没有匹配行，则用空值表示

   查询语句：

select * from T_Person left join T_Card on T_Person.CardId = T_Card.CardId

3. 右连接（right join 或 right outer join）
   右连接也成右向外连接，查询的结果集包括SQL语句中右表的所有行，左表中匹配的行。如果右表的某行在左表中没有匹配的行，则用空值表示

   查询语句：

select * from T_Person right join T_Card on T_Person.CardId = T_Card.CardId

4. 完全外连接（full join 或 full outer join）
   完全外连接，查询的结果集包括SQL语句中左表和右表的所有行。如果某行在另一个表中没有匹配行时，则用空值表示。

   查询语句：

 select * from T_Person full join T_Card on T_Person.CardId = T_Card.CardId

2) 请说出连接查询与子查询的区别

1. 表关联是可以利用两个表的索引的，如果是用子查询，至少第二次查询是没有办法使用索引的。

2. 在where子句中指定连接条件

3. SQL中优化中有一条：将子查询转换为连接查询。

   原因：子查询会多次运算查询，连接查询利于优化器优化，而且可以使用多线程查询各个连接子句。

4. 子查询
   在某些情况下，当进行查询的时候，需要的条件是另外一个select语句的结果，这个时候，就要用到子查询
   为了给主查询提供数据而首先执行的查询被叫做子查询
   用于子查询的关键字主要包括in、not in、=、<>等

3) 请谈谈你对视图的理解

视图是由一个或者多个表组成的虚拟表；

那些用于产生视图的表叫做该视图的基表。

视图不占用物理空间，这个也是相对概念，因为视图本身的定义语句还是要存储在数据字典里的。

视图只有逻辑定义。

每次使用的时候只是重新执行SQL。一个视图也可以从另一个视图中产生。

视图没有存储真正的数据，真正的数据还是存储在基表中。

一般出于对基本的安全性和常用的查询语句会建立视图；并一般情况下不对视图进行新增、更新操作。

4) 请谈谈你对索引的理解

面试题（一）- 谈谈你对数据库中索引的理解

2. 上机题

2.1 创建student和score表

CREATE  TABLE  student (
id  INT(10)  NOT NULL  UNIQUE  PRIMARY KEY  ,
name  VARCHAR(20)  NOT NULL ,
sex  VARCHAR(4)  ,
birth  YEAR,
department  VARCHAR(20) ,
address  VARCHAR(50)
);

创建score表

CREATE  TABLE  score (
id  INT(10)  NOT NULL  UNIQUE  PRIMARY KEY  AUTO_INCREMENT ,
stu_id  INT(10)  NOT NULL,
c_name  VARCHAR(20) ,
grade  INT(10)
);

2.2. 为student表和score表增加记录

向student表插入记录的INSERT语句如下：

INSERT INTO student VALUES( 901,'张老大', '男',1985,'计算机系', '北京市海淀区');
INSERT INTO student VALUES( 902,'张老二', '男',1986,'中文系', '北京市昌平区');
INSERT INTO student VALUES( 903,'张三', '女',1990,'中文系', '湖南省永州市');
INSERT INTO student VALUES( 904,'李四', '男',1990,'英语系', '辽宁省阜新市');
INSERT INTO student VALUES( 905,'王五', '女',1991,'英语系', '福建省厦门市');
INSERT INTO student VALUES( 906,'王六', '男',1988,'计算机系', '湖南省衡阳市');

向score表插入记录的INSERT语句如下：

INSERT INTO score VALUES(NULL,901, '计算机',98);
INSERT INTO score VALUES(NULL,901, '英语', 80);
INSERT INTO score VALUES(NULL,902, '计算机',65);
INSERT INTO score VALUES(NULL,902, '中文',88);
INSERT INTO score VALUES(NULL,903, '中文',95);
INSERT INTO score VALUES(NULL,904, '计算机',70);
INSERT INTO score VALUES(NULL,904, '英语',92);
INSERT INTO score VALUES(NULL,905, '英语',94);
INSERT INTO score VALUES(NULL,906, '计算机',90);
INSERT INTO score VALUES(NULL,906, '英语',85);

2.3. 查询student表的所有记录

答案:

SELECT * FROM student;

2.4 查询student表的第2条到4条记录

答案 :

SELECT * FROM student LIMIT 1,3;

2.5 从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息

答案 :

SELECT id,name,department FROM student;

2.6 从student表中查询计算机系和英语系的学生的信息

答案 :

SELECT * FROM student WHERE department IN ('计算机系','英语系');

2.7 从student表中查询年龄25~30岁的学生信息

答案:

SELECT id,name,sex,2018-birth AS age,department,address FROM student WHERE 2018-birth BETWEEN 25 AND 30;

2.8 从student表中查询每个院系有多少人

答案:

SELECT department, COUNT(id) FROM student GROUP BY department;

2.9 从score表中查询每个科目的最高分

答案:

SELECT c_name,MAX(grade) FROM score GROUP BY c_name;

2.10 查询李四的考试科目（c_name）和考试成绩（grade）

答案:

SELECT c_name, grade FROM score WHERE stu_id= (SELECT id FROM student WHERE name= '李四' );

2.11 用连接的方式查询所有学生的信息和考试信息

答案：

SELECT st.*,s.c_name,s.grade FROM student st INNER JOIN score s;

2.12 计算每个学生的总成绩

答案 :

SELECT student.id,name,SUM(grade) FROM student,score WHERE student.id=score.stu_id GROUP BY id;

2.13 计算每个考试科目的平均成绩

答案 :

SELECT c_name,round(AVG(grade)) pjf FROM score GROUP BY c_name;

2.14 查询计算机成绩低于95的学生信息

答案:

SELECT * FROM student WHERE id IN (SELECT stu_id FROM score WHERE c_name="计算机" and grade<95);

2.15 查询同时参加计算机和英语考试的学生的信息

答案：

SELECT st.* from student st,score s1,score s2 WHERE  st.id = s1.stu_id AND s1.c_name ='计算机' AND st.id = s2.stu_id AND s2.c_name ='英语'

2.16 将计算机考试成绩按从高到低进行排序

答案:

SELECT stu_id, grade FROM score WHERE c_name= '计算机' ORDER BY grade DESC;

2.17 查询姓张或者姓王的同学的姓名、院系和考试科目及成绩

效果：

答案：

SELECT st.*,c_name,grade from student st, score s WHERE(`name` LIKE '张%' OR `name` LIKE '张%')AND st.id = s.stu_id;

2.18 查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩

SELECT student.id, name,sex,birth,department, address, c_name,grade FROM student, score WHERE address LIKE '湖南%' AND student.id=score.stu_id;