DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
`id` int(11) NOT NULL,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
Oracle
DROP TABLE "SCOTT"."class";
CREATE TABLE "SCOTT"."class"("id" NUMBER NOT NULL ,"name"VARCHAR2(255 BYTE) NOT NULL
)
2、插入语句
MySQL
INSERT INTO `class` VALUES (1001,'数学1班');
INSERT INTO `class` VALUES (1002,'数学2班');
INSERT INTO `class` VALUES (1003,'数学3班');
Oracle
INSERT INTO "SCOTT"."class" VALUES ('101','数学1班');
INSERT INTO "SCOTT"."class" VALUES ('102','数学2班');
INSERT INTO "SCOTT"."class" VALUES ('103','数学3班');
3、修改语句
MySQL
UPDATE classSET name='计算机1班' WHERE id=1004;
Oracle
UPDATE "SCOTT"."class" SET "class"."name"='数学5班' WHERE "class"."id"=105;
4、删除语句
MySQL
DELETE FROM classWHERE id =1004;
Oracle
DELETE "SCOTT"."class" WHERE "class"."id"=105;
5、查询语句
MySQL
SELECT c.*,COUNT(s.class) count
FROM classc LEFT JOIN student s ON c.id=s.classGROUP BY c.id,c.name
ORDER BY c.id
Oracle(连接可使用join或(+)实现)
SELECT c.*,COUNT(s."class") count
FROM "SCOTT"."class" c,"SCOTT"."student" s
WHERE c."id"= s."class"(+)
GROUP BY c."id",c."name"
ORDER BY c."id"
6、分页查询
MySQL
SELECT * FROM classLIMIT0,2
Oracle
SELECT *
FROM (SELECT ROWNUM r,a.*
FROM(SELECT * FROM "class") a
WHERE ROWNUM <3) b
WHERE b.r >0
7、MySQL与Oracle的区别
7.1 数据类型
MySQL
Oracle
int
number
varchar
varchar2
7.2 delete语句
Oracle可以省略from
delete [from] 表名 where 属性=值
7.3 连接
MySQL使用left join(right join)... on ...实现
Oracle可以使用left join(right join)... on ...和(+)实现
7.4 连接
MySQL直接使用limit实现
Oracle没有limit,实现比MySQL稍微复杂一点:
SELECT *
FROM (SELECT ROWNUM r,a.*
FROM(SELECT * FROM "class") a
WHERE ROWNUM <3) b
WHERE b.r >0
1、建表语句MySQLDROP TABLE IF EXISTS `class`;CREATE TABLE `class` ( `id` int(11) NOT NULL, `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, PRIMARY KEY (`id`) USING BTRE...