面试必备1-sql问题
sql语句的基本结构
create database 数据库名;
use 数据库名;
drop database 数据库名;
create table 表名(
字段 属性[(长度)]
[,字段 属性(长度)]
);
drop table `表名`;
select [字段名|*] from [表名|查询字句] [as 表的别名] [inner join 其它表 [on (条件)]] [where [条件]] group by [,字段名] order by [,字段] desc limit x,y;
update 表名 set 字段=值 where 条件;
DELETE FROM 表名 WHERE 条件;
INSERT INTO 表名 (column1,column2,column3,...) VALUES (value1,value2,value3,...)[,value1,value2,value3,...)];
首先必须先弄懂sql各种字句执行的优先顺序
下面的执行顺序只是一种逻辑顺序，由于Mysql优化器的作用，实际情况可能略有不同。使用explain语句可以分析sql语句的执行顺序，但也不保证准确性。
(8) SELECT (9) DISTINCT<select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE|ROLLUP}
(7) HAVING <having_condition>
(10) ORDER BY <order_by_list>
(11) LIMIT <offset,limit_number>
1、from 执行笛卡尔乘积
对from子句中的左表<left_table>和右表<right_table>执行笛卡尔积，得到虚拟表VT1。
2、on 应用On过滤器
对前面生成的虚拟表VT1进行ON筛选，符合条件的会被记录到虚拟表VT2中。
3、join 添加外部行
这一步只有在连接类型为Outer join时才发生，如Left Outer join、Right Outer join、Full Outer join等，left outer join的保留表时左表，right outer join的保留表是右表，full outer join左右两个表都是保留表，添加外部行就是在VT2表的基础上添加添加保留表中被过滤条件过滤掉的数据，非保留表中的的数据被赋予null值，最后生成虚拟表VT3。
4、where 应用where过滤器
对步骤3产生的虚拟表VT3进行过滤，只有符合条件<where_condition>的记录才会插入新的虚拟表VT4。
5、group by 分组
根据group by子句中的列，对步骤4的记录进行分组操作得到虚拟表VT5。
6、with 应用ROLLUP或CUBE
如果指定了ROLLUP选项，将创建一个额外的记录添加到虚拟表VT5的最后，并生成虚拟表VT6。mysql不支持CUBE。
7、应用HAVING过滤器
对上一步产生的虚拟表应用Having过滤器
8、处理Select列表
将Select中指定的列从上一步产生的虚拟表中选出。
9、应用Distinct子句
如果在查询中指定了distinct子句，则会创建一张内存临时表，并对distinct操作的列增加了唯一索引，以此来去除重复数据。
10、应用Order by子句
根据order by指定的列对上一步输出的虚拟表进行排列，返回新的虚拟表。
11、Limit子句
从上一步的虚拟表中选出指定位置开始的指定行数据。
常用函数
1、聚合函数
count([字段|*]),max(),min(),avg(),sum();
2、日期函数
year() -- 获取日期的年份
now()
set @dt = ’2008-09-10 07:15:30.123456′;
select date(@dt); — 2008-09-10
select time(@dt); — 07:15:30.123456
select year(@dt); — 2008
select quarter(@dt); — 3
select month(@dt); — 9
select week(@dt); — 36
select day(@dt); — 10
select hour(@dt); — 7
select minute(@dt); — 15
select second(@dt); — 30
select microsecond(@dt); — 123456
MySQL dayof… 函数：dayofweek(), dayofmonth(), dayofyear()
分别返回日期参数，在一周、一月、一年中的位置。
set @dt = ’2008-08-08′;
select dayofweek(@dt); — 6
select dayofmonth(@dt); — 8
select dayofyear(@dt); — 221
日期 ’2008-08-08′ 是一周中的第 6 天（1 = Sunday, 2 = Monday, …, 7 = Saturday）；一月中的第 8 天；一年中的第 221 天。
MySQL week… 函数：week(), weekofyear(), dayofweek(), weekday(), yearweek()
set @dt = ’2008-08-08′;
select week(@dt); — 31
select week(@dt,3); — 32
select weekofyear(@dt); — 32
MySQL 日期时间计算函数
1. MySQL 为日期增加一个时间间隔：date_add()
set @dt = now();
select date_add(@dt, interval 1 day); — add 1 day
select date_add(@dt, interval 1 hour); — add 1 hour
select date_add(@dt, interval 1 minute); — …
select date_add(@dt, interval 1 second);
select date_add(@dt, interval 1 microsecond);
select date_add(@dt, interval 1 week);
select date_add(@dt, interval 1 month);
select date_add(@dt, interval 1 quarter);
select date_add(@dt, interval 1 year);
select date_add(@dt, interval -1 day); — sub 1 day
MySQL date_sub() 日期时间函数 和 date_add() 用法一致，不再赘述。
3、逻辑函数
IF(expr1,expr2,expr3) -- 如果expr1的值为true，则返回expr2的值，如果expr1的值为false，则返回expr3的值。
常用关键字
any、in、not in、all
子查询就是指在一个select语句中嵌套另一个select语句。
any，in，some，all分别是子查询关键词之一，
any 可以与=、>、>=、<、<=、<>结合起来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的任何一个数据。
all可以与=、>、>=、<、<=、<>结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的所有数据。
他们进行子查询的语法如下：
operand comparison_operator any (subquery);
operand in (subquery);
operand coparison_operator some (subquery);
operand comparison_operator all (subquery);
not in 是 “<>all”的别名，用法相同。
语句in 与“=any”是相同的。
语句some是any的别名，用法相同。
exists、not exists
EXISTS(包括 NOT EXISTS )子句的返回值是一个BOOL值。 EXISTS内部有一个子查询语句(SELECT ... FROM...)， 我将其称为EXIST的内查询语句。其内查询语句返回一个结果集。 EXISTS子句根据其内查询语句的结果集空或者非空，返回一个布尔值。
order by [,字段名] [desc|asc]
group by [,字段名] [having 聚合函数判断]
inner join、left join、right join
表名 inner join 表名 [on 条件]
UNION
分析函数 over([partition by 字段 order by 字段)
分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。
分析函数有：rank()、dense_rank(),row_number()
DISTINCT：去重
总结
1、查询操作
1.排序问题
order by 字段1 [desc],字段2 [desc],...,字段n [desc]
-- 排序规则先按照字段1（升或降）排序，再接着按字段2（升或降）排序，直到字段n
2.查找最高分（附加其它条件）
方法1：使用max函数
方法2：找出最高分的记录作为一个子查询tmp1，然后根据若干个字段使用in判断是否在tmp1中
3.满足“条件1”的“记录集1”比满足“条件2”的“记录集2”满足“条件3”
方法1：依据“条件1”筛选“记录集1”作为“子查询1”，依据“条件2”筛选“记录集2”作为“子查询2”，将“子查询1”和“子查询2”进行连表操作，再通过where、group by等依据“条件3”筛选
经典问题1：
建立如下数据库：
CREATE DATABASE practice_sql;
USE practice_sql;
CREATE TABLE students (sno VARCHAR (3) NOT NULL,sname VARCHAR (4) NOT NULL,ssex VARCHAR (2) NOT NULL,sbirthday DATETIME,class VARCHAR (5));
CREATE TABLE courses (cno VARCHAR (5) NOT NULL,cname VARCHAR (10) NOT NULL,tno VARCHAR (10) NOT NULL);
CREATE TABLE scores (sno VARCHAR (3) NOT NULL,cno VARCHAR (5) NOT NULL,degree NUMERIC (10,1) NOT NULL);
CREATE TABLE teachers (tno VARCHAR (3) NOT NULL,tname VARCHAR (4) NOT NULL,tsex VARCHAR (2) NOT NULL,tbirthday DATETIME NOT NULL,prof VARCHAR (6),depart VARCHAR (10) NOT NULL);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108,'曾华','男','1977-09-01',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105,'匡明','男','1975-10-02',95031);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107,'王丽','女','1976-01-23',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101,'李军','男','1976-02-20',95033);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109,'王芳','女','1975-02-10',95031);
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103,'陆君','男','1974-06-03',95031);
INSERT INTO COURSES (CNO,CNAME,TNO) VALUES ('3-105','计算机导论',825);
INSERT INTO COURSES (CNO,CNAME,TNO) VALUES ('3-245','操作系统',804);
INSERT INTO COURSES (CNO,CNAME,TNO) VALUES ('6-166','数据电路',856);
INSERT INTO COURSES (CNO,CNAME,TNO) VALUES ('9-888','高等数学',100);
INSERT INTO SCORES (SNO,CNO,DEGREE) VALUES (103,'3-245',86);
INSERT INTO SCORES (SNO,CNO,DEGREE) VALUES (105,'3-245',75);
INSERT INTO SCORES (SNO,CNO,DEGREE) VALUES (109,'3-245',68);
INSERT INTO SCORES (SNO,CNO,DEGREE) VALUES (103,'3-105',92);
INSERT INTO SCORES (SNO,CNO,DEGREE) VALUES (105,'3-105',88);
INSERT INTO SCORES (SNO,CNO,DEGREE) VALUES (109,'3-105',76);
INSERT INTO SCORES (SNO,CNO,DEGREE) VALUES (101,'3-105',64);
INSERT INTO SCORES (SNO,CNO,DEGREE) VALUES (107,'3-105',91);
INSERT INTO SCORES (SNO,CNO,DEGREE) VALUES (108,'3-105',78);
INSERT INTO SCORES (SNO,CNO,DEGREE) VALUES (101,'6-166',85);
INSERT INTO SCORES (SNO,CNO,DEGREE) VALUES (107,'6-106',79);
INSERT INTO SCORES (SNO,CNO,DEGREE) VALUES (108,'6-166',81);
INSERT INTO TEACHERS (TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (804,'李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO TEACHERS (TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (856,'张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO TEACHERS (TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (825,'王萍','女','1972-05-05','助教','计算机系');
INSERT INTO TEACHERS (TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (831,'刘冰','女','1977-08-14','助教','电子工程系');
CREATE TABLE grade (low NUMERIC (3,0),upp NUMERIC (3),`rank` VARCHAR (1));
insert into grade values(90,100,'A');
insert into grade values(80,89,'B');
insert into grade values(70,79,'C');
insert into grade values(60,69,'D');
insert into grade values(0,59,'E');
1、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
SELECT cno,AVG(degree) FROM scores WHERE cno LIKE '3%' GROUP BY Cno HAVING COUNT(Sno) ;
2、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
select tmp1.* from (select * from scores where cno='3-105') as tmp1 INNER JOIN (select * from scores where cno='3-105' and sno='109') as tmp2 where tmp1.degree>tmp2.degree;
select s1.Sno,s1.Degree from scores as s1 INNER JOIN scores as s2 where s1.cno=s2.cno and s2.cno='3-105' and s2.sno='109' and s1.degree>s2.degree ORDER BY s1.sno;
SELECT s1.Sno,s1.Degree FROM Scores AS s1 INNER JOIN Scores AS s2 ON (s1.Cno=s2.Cno AND s1.Degree> s2.Degree) WHERE s1.Cno='3-105' AND s2.Sno='109' ORDER BY s1.Sno;
3、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
select sc1.sno,sc1.cno,sc1.degree from scores as sc1,(select Sno from scores GROUP BY Sno HAVING COUNT(Cno)>1) as sc2,(select Cno,MAX(degree) as max_degree from scores GROUP BY Cno) as sc3 where sc1.sno=sc2.sno and sc1.cno=sc3.cno and sc1.degree<sc3.max_degree ORDER BY sc1.sno,sc1.cno;
select sc1.sno,sc1.cno,sc1.degree from scores as sc1 where sc1.sno in (select Sno from scores GROUP BY Sno HAVING COUNT(Cno)>1) and (sc1.cno,sc1.degree) not in (select Cno,MAX(degree) as max_degree from scores GROUP BY Cno) ORDER BY sc1.sno,sc1.cno;
4、查询选修某课程的同学人数多于5人的教师姓名。
SELECT tmp1.tname FROM (SELECT t1.tname AS tname FROM teachers AS t1 INNER JOIN courses AS c1 ON (t1.tno=c1.tno) INNER JOIN scores AS sc1 ON (c1.cno=sc1.cno) GROUP BY t1.tno,t1.tname,c1.cno HAVING COUNT(sc1.sno)> 5) AS tmp1 GROUP BY tmp1.tname ORDER BY tmp1.tname;
SELECT DISTINCT Tname FROM Scores INNER JOIN Courses ON (Scores.Cno=Courses.Cno) INNER JOIN Teachers ON (Courses.Tno=Teachers.Tno) WHERE Courses.Cno IN (SELECT Cno FROM Scores GROUP BY (Cno) HAVING COUNT(Sno)> 5);
5、查询成绩比该课程平均成绩低的同学的成绩表。
select sc1.* from scores as sc1 INNER JOIN (select cno,AVG(degree) as avg_degree from scores GROUP BY cno) as tmp1 on(sc1.cno=tmp1.cno) where sc1.degree<tmp1.avg_degree ORDER BY sc1.sno;
经典问题2：
建立如下数据库：
create database practice_sql2;
use practice_sql2;
create table Student(sid varchar(10),sname varchar(10),sage datetime,ssex nvarchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
create table Course(cid varchar(10),cname varchar(10),tid varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
create table Teacher(tid varchar(10),tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
create table SC(sid varchar(10),cid varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
1、查询没学过“张三”老师课的同学的学号、姓名；
select s1.sid,sname from student as s1 where not exists(select * from sc where s1.sid=sc.sid and sc.cid in(select c1.cid from teacher as t1 INNER JOIN course as c1 on(t1.tid=c1.tid) where t1.tname='张三'));
SELECT sid,sname FROM student WHERE sid NOT IN (SELECT sc.sid FROM teacher LEFT JOIN course ON teacher.tid=course.tid LEFT JOIN sc ON course.cid=sc.cid WHERE teacher.tname='张三')
2、查询所有课程成绩小于60分的同学的学号、姓名；
select sid,sname from student as s1 where 60>all(select sc.score from sc where s1.sid=sc.sid) and exists(select sc.score from sc where s1.sid=sc.sid);
select s1.sid,sname from student as s1 INNER JOIN sc on(s1.sid=sc.sid) GROUP BY s1.sid,s1.sname HAVING count(if(sc.score>=60,sc.score,NULL))=0;
select sid,sname from student as s1 where not EXISTS(select * from sc where s1.sid=sc.sid and sc.score>=60);
SELECT t1.sid,sname FROM (SELECT sid,max(score) FROM sc GROUP BY sid HAVING max(score)< 60) t1 LEFT JOIN student ON t1.sid=student.sid
3、查询和"01"号的同学学习的课程完全相同的其他同学的学号和姓名
SELECT
*
FROM
student
WHERE
sid IN (
SELECT
sid
FROM
sc
WHERE
sid NOT IN ( SELECT sid FROM sc WHERE cid NOT IN ( SELECT cid FROM sc WHERE sid = '01' ) )
GROUP BY
sid
HAVING
count( * ) = ( SELECT count( * ) FROM sc WHERE sid = '01' )
AND sid != '01'
);
SELECT
*
FROM
student
WHERE
sid IN (
SELECT
sid
FROM
(
SELECT
sid,
cid
FROM
sc
WHERE
sid IN (
SELECT
sid
FROM
sc
WHERE
sid != '01'
GROUP BY
sid
HAVING
count( * ) = ( SELECT count( * ) FROM sc WHERE sid = '01' GROUP BY sid )
)
) AS t1
INNER JOIN ( SELECT cid FROM sc WHERE sid = '01' ) AS t2 ON t1.cid = t2.cid
GROUP BY
t1.sid
HAVING
count( * ) = ( SELECT count( * ) FROM sc WHERE sid = '01' )
);
4、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select s1.sid,sname,avg(score) as avg_score from sc INNER JOIN student as s1 on(sc.sid=s1.sid) where s1.sid in (select sid from sc GROUP BY sid HAVING count(if(score<60,true,NULL))>=2) GROUP BY s1.sid,sname;
SELECT t1.sid,sname,avg_score FROM (SELECT sid,count(IF (score< 60,cid,NULL)),avg(score) AS avg_score FROM sc GROUP BY sid HAVING count(IF (score< 60,cid,NULL))>=2) t1 LEFT JOIN student ON t1.sid=student.sid;
5、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率
select sc.cid,c1.cname,max(score) as max_score,min(score) as min_score,avg(score) as avg_score,count(if(score>=60,score,NULL))/count(*) as pass_ratio from sc INNER JOIN course as c1 on(sc.cid=c1.cid) GROUP BY sc.cid,c1.cname;
6、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
SELECT sid,rank_num,score,cid FROM (SELECT dense_rank() over (PARTITION BY cid ORDER BY score DESC) AS rank_num,sid,score,cid FROM sc) t WHERE rank_num IN (2,3);
7、查询各科成绩前三名的记录
select * from (select sid,cid,dense_rank() over(partition by cid ORDER BY score desc) as `rank` from sc) as tmp1 where `rank`<=3 ORDER BY cid,`rank`;
SELECT sid,cid,rank1 FROM (SELECT cid,sid,rank () over (PARTITION BY cid ORDER BY score DESC) AS rank1 FROM sc) t WHERE rank1<=3;
8、查询下周过生日的学生
select * from student where WEEKOFYEAR(sage)=WEEKOFYEAR(date_add(now(),INTERVAL 1 week));
9、查询下月过生日的学生
select * from student where month(sage)=month(date_add(now(),INTERVAL 1 month));
274
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
