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;
将学生信息表(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;
上一篇: SpringMVC拦截器实现登录
下一篇: SQL优化
推荐阅读
-
加载MySQL、Oracle、SQL Server 2000、SQL Server 2005及以上版本 的加载数据库驱动程序
-
数据库查询排序使用随机排序结果示例(Oracle/MySQL/MS SQL Server)
-
常见数据库Sql Server,Oracle和MySQL的分页语句
-
常见数据库Sql Server,Oracle和MySQL的分页语句
-
Oracle,MySQL,SQL Server三种数据库用法差异
-
Linux下通过python访问MySQL、Oracle、SQL Server数据库的方法
-
Oracle数据库的结构和数据迁移到PostgreSql数据库(MYSQL,SQL Server相似)
-
MYSQL、SQL Server、Oracle数据库排序空值null问题及其解决办法
-
MYSQL、SQL Server、Oracle数据库排序空值null问题及其解决办法
-
加载MySQL、Oracle、SQL Server 2000、SQL Server 2005及以上版本 的加载数据库驱动程序