SQL面试题

1. 用一条SQL 语句查询出每门课都大于80 分的学生姓名
namekecheng fenshu 
张三语文 81
张三数学 75
李四语文 76
李四数学 90
王五语文 81
王五数学 100
王五英语 90

A: select distinct namefrom table where name not in (select distinct name from table wherefenshu<=80)
  select name from table group by name having min(fenshu)>80

  select name fromtable group by name having count(kecheng)>=3 and min(fenshu)>=80

 

 

2. 学生表如下:
自动编号学号姓名课程编号课程名称分数
12005001 张三 0001 数学 69
22005002 李四 0001 数学 89
32005001 张三 0001 数学 69
删除除了自动编号不同, 其他都相同的学生冗余信息

A: delete tablename where 自动编号 not in(selectmin( 自动编号) from tablename group by 学号, 姓名, 课程编号, 课程名称, 分数)

 

3. 面试题：怎么把这样一个表儿
yearmonth amount
19911 1.1
19912 1.2
19913 1.3
19914 1.4
19921 2.1
19922 2.2
19923 2.3
19924 2.4
查成这样一个结果
yearm1 m2 m3 m4
19911.1 1.2 1.3 1.4
19922.1 2.2 2.3 2.4

答案一、
selectyear, 
(selectamount from aaa m where month=1 and m.year=aaa.year) as m1,
(selectamount from aaa m where month=2 and m.year=aaa.year) as m2,
(selectamount from aaa m where month=3 and m.year=aaa.year) as m3,
(selectamount from aaa m where month=4 and m.year=aaa.year) as m4
fromaaa group by year

 

4. 说明：拷贝表( 拷贝数据, 源表名：a 目标表名：b)

SQL: insert into b(a, b, c)select d,e,f from a;

 

5.有一张表，里面有3个字段：语文，数学，英语。其中有3条记录分别表示语文70分，数学80分，英语58分，请用一条sql语句查询出这三条记录并按以下条件显示出来（并写出您的思路）： 

大于或等于 80 表示优秀，大于或等于 60 表示及格，小于 60 分表示不及格。  
显示格式：  
语文 数学 英语  
及格 优秀 不及格  
------------------------------------------
select
(casewhen 语文 >=80 then ' 优秀 '
when 语文 >=60 then ' 及格 '
else' 不及格 ') as 语文 ,
(casewhen 数学 >=80 then ' 优秀 '
when 数学 >=60 then ' 及格 '
else' 不及格 ') as 数学 ,
(casewhen 英语 >=80 then ' 优秀 '
when 英语 >=60 then ' 及格 '
else' 不及格 ') as 英语 ,
fromtable

 

6、编写SQL语句

1) 创建一张学生表，包含以下信息，学号，姓名，年龄，性别，家庭住址，联系电话

Create table stu (学号 int ,
姓名 varchar(8),
年龄 int,
性别 varchar(4),
家庭地址 varchar(50),
联系电话 int
);

 

2) 修改学生表的结构，添加一列信息，学历

Alter table stu add 学历 varchar(6);

 

3) 修改学生表的结构，删除一列信息，家庭住址

Alter table stu drop column家庭地址

 

4) 向学生表添加如下信息：

学号姓名年龄性别联系电话学历

1A22男123456小学

2B21男119中学

3C23男110高中

4D18女114大学

Insert into stuvalues(1,’A’,22,’男’,123456,’小学’)

Insert into stuvalues(2,’B’,21,’男’,119,’中学’)

Insert into stuvalues(3,’C’,23,’男’,110,’高中’)

Insert into stuvalues(4,’D’,18,’女’,114,’大学’)

 

5) 修改学生表的数据，将电话号码以11开头的学员的学历改为“大专”

Update stu set 学历=’大专’ where 联系电话 like ‘11%’

 

6) 删除学生表的数据，姓名以C开头，性别为‘男’的记录删除

Delect from stu where 性别=’男’ and 姓名 like ‘c%’

 

7) 查询学生表的数据，将所有年龄小于22岁的，学历为“大专”的，学生的姓名和学号示出来

Select 姓名,学号 from stu where 年龄<22 and 学历=’大专’

 

8) 查询学生表的数据，查询所有信息，列出前25%的记录

Select top 25 percent *from stu

 

9) 查询出所有学生的姓名，性别，年龄降序排列

Select 姓名,性别 from stu orderby 年龄 desc

 

10) 按照性别分组查询所有的平均年龄

Select avg(年龄) from stu groupby 性别

 

 

7、查询A(ID,Name)表中第31至40条记录，ID作为主键可能是不是连续增长的列，完整的查询语句如下：

select top 10 * from Awhere ID >(select max(ID) from (select top 30 ID from A order by A ) T)order by A

 

 

8、查询表A中存在ID重复三次以上的记录,完整的查询语句如下：

select* from(select count(ID) as count from table group by ID)T where T.count>3

 

 

9、说出以下聚合数的含义：avg ,sum ,max,min , count ,count(*)

AVG:求平均值

SUM:求和

MAX:求最大值

MIN:求最小值

COUNT(*):返回所有行数

 

*****************************************************************

事务 Transaction 触发器 TRIGGER 继续 continue 唯一 unqiue

主键 primary key 标识列 identity 外键 foreign key 检查 check

约束 constraint

*****************************************************************

 

10、说明：随机取出10条数据

select top 10 * from tablename order by newid()

 

 

11、查询平均成绩大于60分的同学的学号和平均成绩；

    select stuId,avg(score)
    from Scores
    group by stuId having avg(score) >60;

12、请简述项目中优化sql语句执行效率的方法，从哪些方面。sql语句性能如何分析?

    (1)尽量选择较小的列
    (2)将where中用的比较频繁的字段建立索引
    (3)select子句中避免使用‘*’
    (4)避免在索引列上使用计算，not，in和<>等操作
    (5)当只需要一行数据的时候使用limit 1
    (6)保证表单数据不超过200w，适时分割表
    (7)针对查询较慢的语句，可以使用explain来分析该语句具体的执行情况

13、sql语句应该考虑哪些安全性?

(1)防止sql注入，对特殊字符进行转义，过滤或者使用预编译的sql语句绑定变量。
(2)最小权限原则，特别是不要用root账户，为不同的类型的动作或者组建使用不同的账户。

(3)当sql运行出错时，不要把数据库返回的错误信息全部显示给用户，以防止泄漏服务器和数据库相关信息。

14、 什么情况下设置了索引但无法使用 

1、以“%”开头的LIKE语句，模糊匹配
2、OR语句前后没有同时使用索引

3、数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）

15、简单描述mysql中，索引，主键，唯一索引，联合索引的区别，对数据库的性能有什么影响（从读写两方面)

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。
普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。
普通索引允许被索引的数据列包含重复的值。如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。也就是说，唯一索引可以保证数据记录的唯一性。
主键，是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条记录，使用关键字PRIMARY KEY 来创建。
索引可以覆盖多个数据列，如像INDEX(columnA, columnB)索引，这就是联合索引。
索引可以极大的提高数据的查询速度，但是会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件。