sql中查询出的结果更改列名_SQL常见面试题

数据库含义的理解

数据库：存放数据的地方

相比于EXCEL的优点：可以存放更多的数据，允许许多人同时使用数据

数据库有很多类型，重点学习关系数据库：关系数据=多张表+各个表之间的关系

学会关系数据库需要掌握哪些方面：

• 多张表的结构：即表长什么样子，包含表名，有列名的列，记录数据的行，约束，比如就是约束，主键用来标识数据唯一性
• 各个表之间的关系：关系就是数据能够对应的匹配，通过联结（join)关系将多表的数据对应匹配，联结是关系数据库的核心概念

数据库管理系统的概念：用来实现数据库原理，管理数据库的计算机软件，关系数据库管理软件包括MYSQL, ORACLE,SQL SERVER

SQL的含义：是为了操作数据开发的语言，对数据库中的表操作

知识思维导图

SQL面试题

库/表增改删

库的管理

• 创建库：create database 库名
• 删除库：drop database 库名
• 更改库的字符集：alter database 库名 character set utf8

表的管理

• 创建表：create table 表名【列名 列的的类型（长度&约束)】
• 删除表：drop table 表名
• 修改表名：alter table 表名 rename 新表名
• 修改列名：alter table 表名 change column 旧列名 新列名【列的的类型（长度&约束)】
• 修改列的类型或者约束：alter table 表名 modify column 列名【列的的类型（长度&约束)】
• 添加新列：alter table 表名 add column 列名【列的的类型（长度&约束)】
• 删除列：alter table 列名 drop column 列名
• 复制表的结构：create tabel 表名 like 被复制表名
• 复制部分表结构：create table 表名 select 部分列名 from 被复制的表 where 0(0代表false)
• 复制表结构+（所有）数据：create tabel 表名 select 列名 from 被复制表名

数据的修改

• 插入表中数据：insert into 表名（列名1,列名2,...) values(列名1中的数据,列名2中的数据,...)
• 更改表中数据：update 表名 set 列名=新值 where 列名=旧值
• 删除表中（指定/所有）数据： delete from table 表名 where 列名=某值

实现表和库的管理也可以通过客户端实现，可以右击数据库或者表，对库或者表修改，更新，删除，插入数据等，以下案例仅仅给出实现以上运行的SQL语句

CREATE DATABASE newschool; #创建数据库 
USE newschool;#使用该数据库 
DROP TABLE newstudent;#删除数据库 
CREATE TABLE newstudent(学号 VARCHAR(255) NOT NULL, 姓名 VARCHAR(255), 出生日期 VARCHAR(255), 性别 VARCHAR(255), PRIMARY KEY(学号));#创建表，构建表的结构
INSERT INTO newstudent (学号,姓名,出生日期,性别)  VALUES('01','Anna','1990-1-1','女'),('02','Jack','1991-02-01','男');#插入数据 
ALTER TABLE newstudent CHANGE COLUMN 姓名 studentname VARCHAR(255);#修改列名 
ALTER TABLE newstudent ADD COLUMN hometown VARCHAR(255);#添加列 
ALTER TABLE newstudent DROP COLUMN hometown;#删除列 
CREATE TABLE copy LIKE newstudent;#复制表结构 
ALTER TABLE copy RENAME newstudent1;#修改表名为newstudent1 
CREATE TABLE newstudent2 SELECT 学号,studentname FROM newstudent;#复制学号和姓名到newstudent2中 
CREATE TABLE newstudent3 SELECT 学号,studentname FROM newstudent WHERE 0;#仅仅复制学号和姓名字段到newstudent3中，没有数据 
DROP TABLE newstudent3;#删除表 
DELETE FROM newstudent2 WHERE 学号='02';#删除学号为02的信息 
UPDATE newstudent2 SET 学号='02'  WHERE 学号='01';#将学号01改为02 DROP 
DATABASE newschool;#删除数据库 

根据以上SQL语句练习，创建学校数据库表

简单查询

#1查询姓猴的学生名单 
SELECT * FROM student WHERE 姓名 LIKE '猴%'; 

#2查询姓名中最后一个字是猴的学生名单 
SELECT* FROM student WHERE 姓名='%猴'; 

#3查询姓名中带猴的学生名单 
SELECT* FROM student WHERE 姓名='%猴%';
 
#4查询姓孟的老师的个数 
DESC teacher; SELECT COUNT(teacherid) FROM teacher WHERE teachername LIKE '孟%'; 

汇总分析

#1查询编号为0002的总成绩 
SELECT SUM(成绩)  
FROM score  
WHERE 学号='0002';  

#2查询选了课程的学生人数#（因为学生可以选择多门，因此需要去除重复值） 
SELECT COUNT(DISTINCT 学号) AS 学生人数  
FROM score;  

#3查询各科成绩最高和最低的分数 
SELECT MAX(成绩) AS 最高分数,MIN(成绩) AS 最低分数 
FROM score 
GROUP BY 课程号;  

#4查询每门课程被选修的学生数 
SELECT 课程号,COUNT(学号)  
FROM score 
GROUP BY 课程号;  

#5查询男生和女生的人数 
SELECT COUNT(学号),性别 
FROM student 
GROUP BY 性别;  

#6查询平均成绩大于60分学生的学号和平均成绩 
SELECT AVG(成绩) AS 平均成绩,学号 
FROM score 
GROUP BY 学号 
HAVING AVG(成绩)>60;  

#7查询至少选修两门课程的学号 
SELECT COUNT(课程号) AS 选修课程数目,学号 
FROM score 
GROUP BY 学号 
HAVING COUNT(课程号)>=2;  

#8查询同名同姓的学生名单并统计同名人数 
SELECT COUNT(姓名) AS 同名同姓的人数,姓名 
FROM student 
GROUP BY 姓名 
HAVING COUNT(姓名)>=2;  

#9查询不及格的课程并按课程号从大到小排列 
SELECT 课程号 
FROM score 
WHERE 成绩<60 
ORDER BY 课程号 DESC; 
 
#10查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列 
SELECT AVG(成绩) AS 平均成绩,课程号 
FROM score 
GROUP BY 课程号 
ORDER BY 平均成绩 ASC,课程号 DESC;  

#11检索课程编号为“0004”且分数小于60的学生学号，结果按按分数降序排列 
SELECT 学号 
FROM score 
WHERE 课程号='0004' AND 成绩<60 ORDER BY 成绩 DESC;  

12/*统计每门课程的学生选修人数(超过2人的课程才统计) 要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序*/ 
SELECT COUNT(学号) AS 人数,课程号 
FROM score 
GROUP BY 课程号 
HAVING COUNT(学号)>2 
ORDER BY COUNT(学号) DESC,课程号 ASC;  

#13查询两门以上不及格课程的同学的学号，以及不及格课程的平均成绩 
SELECT 学号,AVG(成绩) AS 平均成绩 
FROM score 
WHERE 成绩<60 
GROUP BY 学号 
HAVING COUNT(课程号)>=2; 

子查询

#1查询所有课程成绩小于60分学生的学号、姓名 
SELECT 学号,姓名 
FROM student 
WHERE 学号 IN ( 
SELECT 学号 
FROM score 
GROUP BY 学号 
HAVING MAX(成绩)<60);
  
#2查询没有学全所有课的学生的学号、姓名 
SELECT 学号,姓名
FROM student 
WHERE 学号 IN ( 
SELECT 学号 
FROM score 
GROUP BY 学号 
HAVING COUNT(课程号)<( SELECT COUNT(课程号) FROM course));  

#3查询出只选修了两门课程的全部学生的学号和姓名 
SELECT 学号,姓名 
FROM student 
WHERE 学号 IN ( 
SELECT 学号 
FROM score 
GROUP BY 学号 
HAVING COUNT(课程号)=2);  

#4 1990年出生的学生名单 
SELECT * FROM student WHERE YEAR(出生日期)=1990;  
#5在成绩表中，按课程号分组取成绩最大值所在行的数据 
SELECT * 
FROM score AS a 
WHERE 成绩=( 
SELECT MAX(成绩) 
FROM score AS b 
WHERE a.`课程号`=b.`课程号`);  

#6在成绩表中，按课程号分组取成绩最小值所在行的数据 
SELECT * 
FROM score AS a 
WHERE 成绩=( 
SELECT MIN(成绩) 
FROM score AS b 
WHERE a.`课程号`=b.`课程号`);  

#7在成绩表中，查询各科成绩前两名的记录 
(SELECT * FROM score WHERE 课程号='0001' ORDER BY 成绩 DESC LIMIT 2) 
UNION ALL 
(SELECT * FROM score WHERE 课程号='0002' ORDER BY 成绩 DESC LIMIT 2) 
UNION ALL 
(SELECT * FROM score WHERE 课程号='0003' ORDER BY 成绩 DESC LIMIT 2); 

多表查询

#1创建近视学生表和新学生表 
CREATE TABLE disablestudent(序号 VARCHAR(255) NOT NULL, 学生学号 VARCHAR(255)); 
INSERT INTO disablestudent VALUES ('1','0001'),('2','0002'),('3','0004'); 
CREATE TABLE newstudent(学号 VARCHAR(255) NOT NULL PRIMARY KEY, 姓名 VARCHAR(255)); 
INSERT INTO newstudent VALUES ('0001','周周'),('0002','李李'),('0003','王王'),('0004','张张'),('0005','猴猴'); 
#请问不是近视眼的学生都有谁？ 
SELECT * 
FROM newstudent AS s LEFT JOIN disablestudent AS d 
ON s.`学号`=d.`学生学号`  
WHERE d.`学生学号` IS NULL; 

#2创建顾客姓名表购买记录 
CREATE TABLE customers(ID VARCHAR(255) NOT NULL, NAME VARCHAR(255)); 
INSERT INTO customers VALUES ('1','Joe'),('2','Henry'),('3','Sam'),('4','Max'); 
CREATE TABLE orders(ID VARCHAR(255) NOT NULL PRIMARY KEY, customerid VARCHAR(255));
INSERT INTO orders VALUES ('1','3'),('2','1');
 #某网站包含两个表，顾客姓名表（表名Customers）和 购买记录表（表名Orders）。找出所有从不订购任何东西的客户。 
#（“顾客姓名表”中的ID与“购买记录”表中的学生学号CustomerId一一对应） 
SELECT *
FROM customers AS c LEFT JOIN orders AS o 
ON c.`ID`=o.`customerid` 
WHERE o.`customerid`IS NULL; 

#3查询所有学生的学号，姓名，选课数，总成绩 
SELECT a.学号,a.姓名,COUNT(b.课程号),SUM(b.成绩) 
FROM student AS a LEFT JOIN score AS b 
ON a.`学号`=b.`学号` 
GROUP BY a.`学号`; 

#4查询平均成绩大于85的所有学生的学号，姓名和平均成绩 
SELECT a.学号,a.姓名,AVG(b.成绩) AS 平均成绩 
FROM student AS a LEFT JOIN score AS b 
ON a.`学号`=b.`学号` 
GROUP BY a.`学号` 
HAVING AVG(成绩)>85; 

#5查询学生的选课情况：学号，姓名，课程号，课程名称 
SELECT a.学号,a.姓名,c.课程号,c.课程名称 
FROM student AS a LEFT JOIN score AS b 
ON a.`学号`=b.`学号` LEFT JOIN course AS c 
ON b.`课程号`=c.`课程号`; 

#6查询出每门课程的及格人数和不及格人数 
SELECT 课程号,
SUM(CASE WHEN 成绩<60 THEN 1 ELSE 0 END) AS 及格人数, 
SUM(CASE WHEN 成绩>60 THEN 1 ELSE 0 END) AS 不及格人数
FROM score GROUP BY 课程号; 

#7使用分段[100-85],[85-70],[70-60],[‹60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称 
SELECT a.课程号,b.课程名称,
SUM(CASE WHEN 成绩<60 THEN 1 ELSE 0 END) AS '[<60]', 
SUM(CASE WHEN 成绩>=60 AND 成绩<70 THEN 1 ELSE 0 END) AS '[70-60]', 
SUM(CASE WHEN 成绩>=70 AND 成绩<=85 THEN 1 ELSE 0 END) AS '[85-70]', 
SUM(CASE WHEN 成绩>=85 AND 成绩<=100 THEN 1 ELSE 0 END) AS '[85-100]' 
FROM score AS a RIGHT JOIN course AS b ON a.`课程号`=b.`课程号` 
GROUP BY a.`课程号`,b.`课程名称`; 

#8查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名 
SELECT a.学号,a.姓名 
FROM student AS a 
INNER JOIN score AS b 
ON a.`学号`=b.`学号` 
WHERE b.`课程号`='0003' AND b.`成绩`>80; 

#9每个学生在不同科目下的成绩(行列互换-行转列) 
SELECT 学号, 
MAX(CASE WHEN 课程号='0001' THEN 成绩 ELSE 0 END) AS '课程号0001', 
MAX(CASE WHEN 课程号='0002' THEN 成绩 ELSE 0 END) AS '课程号0002', 
MAX(CASE WHEN 课程号='0003' THEN 成绩 ELSE 0 END) AS '课程号0003' 
FROM score 
GROUP BY 学号; 

练习 #查询10-50号部门每个员工的工资，显示部门，员工号，工资（行列互换-行转列） 
SELECT employee_id, 
MAX(CASE WHEN department_id=10 THEN salary ELSE 0 END) AS 'department10', 
MAX(CASE WHEN department_id=20 THEN salary ELSE 0 END) AS 'department20', 
MAX(CASE WHEN department_id=30 THEN salary ELSE 0 END) AS 'department30' 
FROM employees 
GROUP BY employee_id; 

关于如何列转行的方法，可以用union实现，具体可参考

youstone：mysql 行列互换​zhuanlan.zhihu.com

一般在写SQL时需要注意哪些问题，可以提高查询的效率？

1.尽量避免使用*：*是选择全部数据的意思，消耗时间较大，建议最后需要哪列数据就提取哪列数据，尤其是再多表联结中，使用*会造成更大的成本开销

2.where子句比较符号左侧避免函数：为了提高效率，where子句中遇到函数或加减乘除的运算，应当将其移到比较符号的右侧，比如 where 成绩>90-5

3.尽量避免使用in 或者 not in: 用in或者not in会导致数据库进行全表搜索，增加运行时间，可以用between...and/=替换

4.尽量避免使用or: or也导致会导致数据库进行全表搜索，比如 select学号 from score where 成绩=88 or 成绩=89，可以替换为 select 学号 from score where成绩=88 union select 学号 from score where 成绩=89；

5.使用limit子句限制返回的数据行数：