基础知识 - 数据库

1.SQL语句优化有哪些？

答：1、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

2、应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描

3、应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。

2.SQL Server和MySQL区别

答：一、本质区别是它们所遵循的基本原则

SQL服务器的狭隘的、保守的存储引擎，与MySQL服务器的开放的、可扩展的存储引擎是不同的。你虽然可以使用SQL的Sybase引擎，但MySQL可以提供更多的选择，如MyISAM，Heap， InnoDB等。MySQL并不完全支持陌生的关键词，所以它就比SQL服务器少一些相关的数据库。同时，MySQL也缺乏某些存储程序的功能，如MyISAM引擎联支持交换的功能。

二、发行费用上：MySQL不全是免费，但很便宜

对于SQL服务器，获取一个免费的开发费用，最常的方式是购买微软的Office或者Visual Studio的费用。但是，如果你想用于商业产品的开发，你必须还要购买SQL Server Standard EdiTIon。学校或非赢利的企业可以不考虑这一附加的费用。

三、性能方面：先进的MySQL

MySQL包含一个缺省桌面格式MyISAM。MyISAM 数据库与磁盘非常地兼容而不占用过多的CPU和内存。MySQL可以运行于Windows系统而不会发生冲突，在UNIX或类似UNIX系统上运行则更好。SQL服务器的稳定性要比它的竞争对手强很多，但是也必须增加额外复杂操作，磁盘存储，内存损耗等等。

3.Union和Union All到底有什么区别

答：Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

Union All：对两个结果集进行并集操作，包括重复行，不进行排序；

4.怎么写存储过程

答：定义问题，分析问题整理需求，流程设计，代码编写，测试。

5.MySql数据库有哪几种索引类型？简单说明一下数据库索引的原理及常用索引实现。

答：MYSQL数据索引类型包括：普通索引、唯一索引、主键索引、全文索引。

唯一/非唯一，聚集/非聚集。

6.MongoDB和MySQL的区别

答：MongoDB基于内存，数据是存储在硬盘上的，只不过需要经常读取的数据会被加载到内存中，将数据存储在物理内存中，从而达到高速读写；不支持事务操作。数据没有耦合性，容易扩展；

MySql复杂查询可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询；事务支持使得对于安全性能很高的数据访问要求得以实现。

关系型数据库的不足：1. 大量数据的写入处理；2. 为有数据更新的表做索引或表结构（schema）变更；3. 字段不固定时应用；4. 对简单查询需要快速返回结果的处理

NoSQL数据库：临时性键值存储（memcached、Redis）、永久性键值存储（ROMA、Redis）、面向文档的数据库（MongoDB、CouchDB）、面向列的数据库（Cassandra、HBase）。

Redis有些特殊，临时性和永久性兼具。Redis首先把数据保存在内存中，在满足特定条件（默认是 15分钟一次以上，5分钟内10个以上，1分钟内10000个以上的键发生变更）的时候将数据写入到硬盘中，这样既确保了内存中数据的处理速度，又可以通过写入硬盘来保证数据的永久性，这种类型的数据库特别适合处理数组类型的数据。总结来说：

。同时在内存和硬盘上保存数据

。可以进行非常快速的保存和读取处理

。保存在硬盘上的数据不会消失（可以恢复）

。适合于处理数组类型的数据

7.动态参数拼接Sql时后面条件不确定的情况下以往会先加上select * from table where 1=1 ... 然后使用and id=xx and name=yy 这种方式追加where条件。但是这种方法会先检索全表，除此之外还有什么处理方式？

答：先使用List<SqlParameter>判断有没有值，再重写where条件的构造。可以用一个布尔值来做限定，为真就拼接where条件，否则就不拼接。

8.A表1条数据，左连接B表4条数据，连接都能匹配结果是多小条数据

答：4条

9.什么是sql注入？有什么防范措施。

答：所谓SQL注入，就是通过把SQL命令插入到Web表单提交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。

1.永远不要信任用户的输入。对用户的输入进行校验，可以通过正则表达式，或限制长度；对单引号和双"-"进行转换等。

2.永远不要使用动态拼装sql，可以使用参数化的sql或者直接使用存储过程进行数据查询存取。

数据库语句练习

1.请参照下表结构写出一条SQL，读出100行记录中的50到60行记录。

ID	ColumnName
1	A
3	B
6	D
7	C
11	D

2.请参照上表，写出一条SQL，筛选出列Name出现次数超过2次的字母。

;with t_page as
(
    select ROW_NUMBER()over( order by ID) as rowNum,ID from Table_1 
)
select top 10 * from Table_1 t1 
where  exists (select  ID from t_page tp 
where rowNum>50 and tp.ID=t1.ID)

;with t_c as
(
select ColumnName from Table_1  group by ColumnName having count(ColumnName)>2
)
select * from Table_1 t1 where exists 
(select ColumnNamefrom t_c where t_c.ColumnName=t1.ColumnName)

ID	ColumnStatus
1	0
3	1
4	2
8	0
11	1
15	2
17	1
..	...

3.请写出一条查询语句，统计出ColumnStatus=0和ColumnStatus=2出现的次数，查询结果类似如下：

Status0	Status2
7	3

;with t_sc as
(
   select [ColumnStatus],count(1) as status_count from Table_2 group by [ColumnStatus]
)
select max(case when [ColumnStatus]=0 then status_count else 0 end) as Status0,
max(case when [ColumnStatus]=2 then status_count else 0 end) as Status2   from t_sc 

 学生表Student，结构如下，Id为唯一标识列

Id	StudentNo	Name	Age	Sex	CreateTIme
1	20170817	Jack	18	男	2016-10-12
2	20170818	Eric	16	女	2016-10-13

课程表Course，结构如下，id为唯一标识列

Id	Code	Name	CreateTIme
1	1001	语文	2016-10-12
2	1002	数学	2016-10-13

成绩表Score，结构如下，id为唯一标识列

Id	StudentID	CourseId	Score	CreateTIme
1	1	2	80	2016-10-12
2	2	1	90	2016-10-13

--1.请写SQL查询平均成绩大于60分的同学的学号和平均成绩
;with t_avg as
(
select studentid,avg(score) avg_score from scs_score  group by studentid
)
select * from t_avg where avg_score>60

--2.请写SQL查询每个学生最高成绩和最低成绩
select studentid,max(score) max_score,min(score) min_score from scs_score  group by studentid

--3.请写SQL查询所有同学的学号、姓名、选课数、总成绩。
;with t_sum as
(
select studentid,sum(score) sum_score,count(CourseId) count_course from scs_score  group by studentid
)
select t.*,s.[Name] from t_sum t left join SCS_Student s on t.studentid=s.id

 4.List the names and the average score of the students who have failed two or more courses.

select student_no,student_name from student;

select course_no,course_name from course;

select student_no,course_no,score from score;

with f_table as(
select student_no,count(1) as failed_count from #score where score<60 
group by student_no
)
,avg_table as(
select f.student_no,avg(s.score) avg_score from f_table f 
left join #score s on f.student_no=s.student_no where failed_count>=2 
group by f.student_no
)
select a.*,s.student_name from avg_table a left join #student s 
on a.student_no=s.student_no

5.Please get the “Result” from table “Score” by SQL.

Name	Subject_name	score
Li Lei	Chinese	80
Li Lei	Math	60
Li Lei	English	60
Lin Tao	Physics	60

结果：

name	Chinese	Math	English	Physics
Li Lei	80	60	60	0
Lin Tao	0	0	0	60

select student_name
,sum(case course_name when 'Chinese' then score else 0 end) Chinese 
,sum(case course_name when 'Math' then score else 0 end) Chinese 
,sum(case course_name when 'English' then score else 0 end) Chinese 
,sum(case course_name when 'Physics' then score else 0 end) Chinese 
from #score2 group by student_name

--或者

select * from #score2 
pivot (sum(score) for course_name in (English,Chinese,Math,Physics)) as tt

5.用一条SQL语句 查询出每门课都大于80分的学生姓名

Name	Kecheng	fenshu
张三	语文	81
张三	数学	75
李四	语文	76
李四	数学	90
王五	语文	81
王五	数学	100
王五	英语	90

;with t_temp as
(
select Name,sum(fenshu)/count(kecheng) p_course from T_Course group by Name
)
select Name from t_temp t where p_course>80