欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Oracle,MySQL,SQL Server三种数据库用法差异

程序员文章站 2022-07-01 18:59:30
...
1、使用连接符连接字段
将学生信息表(t_student)中学生姓名和性别连接起来查询学生信息
Oracle:
SELECT stuId, stuName||sex FROM t_student;
MySQL:
SELECT stuId, CONCAT(stuName,sex) FROM t_student;
SQL Server:
SELECT stuId, stuName+sex FROM t_student;

2、使用ROLLUP关键字统计数据
对教师信息表中的院系和教师职称进行分组,并对分组后的教师工资进行统计
Oracle:
SELECT dept,profession,SUM(salary) FROM t_teacher GROUP BY ROLLUP(dept,profession);
MySQL:
SELECT dept,profession,SUM(salary) FROM t_teacher GROUP BY dept,profession WHIT ROLLUP;
SQL Server:
SELECT dept,profession,SUM(salary) FROM t_teacher GROUP BY dept,profession WHIT ROLLUP;

3、限制结果集行数
查询教师信息表中的教师信息,只显示按照教师编号升序排序后的第4条到第6条记录
Oracle:
SELECT teaId,teaName,dept,profession FROM (SELECT ROWNUM AS rn,teaId,teaName,dept,profession FROM t_teacher WHERE ROWNUM<=6) WHERE rn>=4;
MySQL:
SELECT teaId,teaName,dept,profession FROM t_teacher ORDER BY teaId LIMIT 3,3;
SQL Server:
SELECT teaId,teaName,dept,profession FROM (SELECT TOP 6 teaId AS r,teaName,dept,profession FROM t_teacher) WHERE r>=4;