SQLServer与MySQL实战对比-优快云博客

本文链接：https://blog.youkuaiyun.com/Remoa_Dengqinyi/article/details/53183550

MySQL学习（三）

本MySQL学习系列博客共七篇，此为第三篇。

目录：

1、如何在SQL Server中输入年月日的数据类型？

2、实现内容：

3、源代码：

4、运行结果：

1、如何在SQL Server中输入年月日的数据类型？

答：SQL Server中没有date类型，但可以将datetime类型进行转换，对于日期的处理，两种数据库管理系统各提供了相应的方法。

①MySQL的date函数：

图1.1 MySQL的date函数

②SQL Server的date函数：

图1.2 SQL Server的date函数

③Sql Server日期函数中的convert函数：

图1.3 convert函数

④mysql日期函数中的extract函数：

图1.4 MySQL的extract函数

2、实现内容：

图2.1 实现内容

3、源代码：

/*（1）查询全部学生的学号、姓名、性别和年龄*/
select student_id, student_name, student_sex, 
2016 - year(student_birthday) as student_age
from v_student;

/*（2)查询外国语学院的全部学生的学号，姓名，性别和出生年月日*/
/*sql server代码*/
select student_id, student_name, student_sex, 
convert(varchar(10), student_birthday, 111) as student_birth
from v_student, v_department
where v_student.department_id = v_department.department_id
and v_department.department_name = '外国语学院';
/*mysql代码*/
select student_id, student_name, student_sex, student_birthday
from v_student, v_department
where v_student.department_id = v_department.department_id
and v_department.department_name = '外国语学院';

/*（3）查询教材中含有“教材”的教材信息*/
select *
from v_textbook
where textbook_name like '%教材%';

/*（4）查询在1997年以后出生的计算机学院的学生的学号，姓名，性别和出生年份*/
/*sql server代码*/
select student_id, student_name, student_sex, 
datepart(yyyy, student_birthday) as s_birth_year
from v_student 
where datepart(yyyy, student_birthday) >= 1997;
/*mysql代码*/
select student_id, student_name, student_sex, 
extract(year from student_birthday) as s_birth_year
from v_student 
where extract(year from student_birthday) >= 1997;

/*（5）查询年龄不在19到20之间的学生的学号，院系编号和姓名*/
/*sql server代码*/
select student_id, department_id, student_name
from v_student 
where 2016 - datepart(yyyy, student_birthday)
	not between 19 and 20;
/*mysql代码*/
select student_id, department_id, student_name
from v_student 
where 2016 - extract(year from student_birthday)
	not between 19 and 20;

/*（6）查询全部学生的学号，姓名，性别，所在院系名称和选课信息*/
select v_student.student_id, student_name, student_sex, department_name, course_name, 
course_credit, course_hour
from v_student, v_student_score, v_department, v_course
where v_student.student_id = v_student_score.student_id
and v_student_score.course_id = v_course.course_id
and v_student.department_id = v_department.department_id;

/*（7）查询学生不及格的情况列出不及格学生的学号、姓名和不及格的课程名称*/
select v_student.student_id, student_name, course_name
from v_student, v_course, v_student_score
where grade < 60
and v_student_score.student_id = v_student.student_id
and v_student_score.course_id = v_course.course_id;


/*（8)通过查询为教师生成课程信息，包括：教师院系、教师姓名，任课名称，上课地点以及使用的教材名称*/
select distinct teac.teacher_name, depa.department_name, 
cour.course_name, clas.classroom_location, text.textbook_name
from v_teacher
as teac
left join(
	select department_name, department_id
	from v_department
)
as depa
on(teac.department_id = depa.department_id)
left join(
	select course_name, teacher_name
	from v_course, v_teacher_course_task, v_teacher, v_department
	where v_teacher_course_task.teacher_id = v_teacher.teacher_id
	and v_teacher_course_task.course_id = v_course.course_id
)
as cour
on(teac.teacher_name = cour.teacher_name)
left join(
	select classroom_location, department_name, teacher_name, course_name
	from v_teacher, v_course_develop, v_classroom, v_department, v_course
	where v_classroom.classroom_id = v_course_develop.classroom_id
	and v_course_develop.teacher_id = v_teacher.teacher_id
	and v_teacher.department_id = v_department.department_id
	and v_course_develop.course_id = v_course.course_id
)
as clas
on(clas.department_name = depa.department_name 
and clas.teacher_name = cour.teacher_name 
and clas.course_name = cour.course_name)
left join(
	select textbook_name, course_name, teacher_name
	from v_textbook, v_textbook_selection, v_teacher_course_task, v_teacher, v_course
	where v_textbook.textbook_id = v_textbook_selection.textbook_id
	and v_textbook_selection.course_id = v_teacher_course_task.course_id
	and v_teacher_course_task.teacher_id = v_teacher.teacher_id
	and v_course.course_id = v_teacher_course_task.course_id
)
as text
on(text.course_name = cour.course_name 
and text.teacher_name = clas.teacher_name)
;

/*（9）查询没有选修课程名中包含“数据”课程的学生，列出学生的学号和姓名*/
select distinct v_student.student_id, student_name
from v_student
where student_name not in(
	select student_name
	from v_student, v_course, v_student_score 
	where v_student.student_id = v_student_score.student_id
	and v_course.course_id = v_student_score.course_id 
	and course_name not in(
		select course_name
		from v_course
		where course_name not like '%数据%'
	)
);

/*（10）检索年龄为20岁，同时至少选修了一门学分3分以上的课程的学生的姓名和院系名称*/
select student_name, department_name
from v_student, v_department, v_course, v_student_score
where course_credit >= 3
and (2016 - extract(year from student_birthday)) = 20
and v_student_score.student_id = v_student.student_id
and v_course.course_id = v_student_score.course_id
and v_student.department_id = v_department.department_id;

/*（11）通过信息数据库得到元数据，key_column_usage表描述了具有约束的键列*/
select * 
from information_schema.key_column_usage 
where referenced_table_name='v_student';

/*（12）将张三的院系编号改为122*/
update v_student
set department_id = '122'
where student_id = '3114005847';

4、运行结果：

（1）查询全部学生的学号、姓名、性别和年龄