测试用的SQL例子

前言

在准备一些测试数据, 整理了一些数据库操作的SQL例子

实验

mysql

drop table tbl_test;
	
CREATE TABLE `tbl_test` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `txt_value` VARCHAR(500) NOT NULL,
  `enum_value` ENUM('w','l','m') NOT NULL,
  `time_stamp_value` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE(time_stamp_value)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

	drop table tbl_comp_index_files;
		
	CREATE TABLE `tbl_comp_index_files` (
			`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			`time_stamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
			`probe_id` VARCHAR(64) NOT NULL,
			`file_path_name` varchar(1024) NOT NULL,
			PRIMARY KEY (`id`)
			) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

	insert into tbl_comp_index_files(probe_id, file_path_name)values('01', '/var/log/test1.dat');
	insert into tbl_comp_index_files(probe_id, file_path_name)values('01', '/var/log/test2.dat');
	insert into tbl_comp_index_files(probe_id, file_path_name)values('01', '/var/log/test3.dat');
	insert into tbl_comp_index_files(probe_id, file_path_name)values('01', '/var/log/test4.dat');
	insert into tbl_comp_index_files(probe_id, file_path_name)values('01', '/var/log/test5.dat');
	insert into tbl_comp_index_files(probe_id, file_path_name)values('01', '/var/log/test6.dat');
	select * from tbl_comp_index_files where probe_id = '01' order by time_stamp asc, id asc limit 100;

oracle

--------------------------------------------------------
-- 表删除
--------------------------------------------------------
DROP TABLE "SYSTEM"."tbl_test";

--------------------------------------------------------
-- 表建立
--------------------------------------------------------
CREATE TABLE "SYSTEM"."tbl_test"
  (
    "COL_DATE" DATE,
    "COL_NUMBER" NUMBER，
    "COL_RAW" RAW(20),
    "COL_VARCHAR" VARCHAR(20 BYTE),
    "COL_LONG" LONG,
    "COL_ROWID" ROWID,
    "COL_TIMESTAMP" TIMESTAMP (6)
  );
  
--------------------------------------------------------
-- 插入行数据
--------------------------------------------------------
INSERT INTO "SYSTEM"."tbl_test" (COL_DATE, COL_NUMBER, COL_VARCHAR, COL_LONG, COL_TIMESTAMP) 
VALUES (
TO_DATE('15-3月 -17', 'DD-MON-RR'), 
'12345', 
'hello world', 
'111222333', 
TO_TIMESTAMP('15-3月 -17 09.30.12.345000000 上午', 'DD-MON-RR HH.MI.SSXFF AM'))

--------------------------------------------------------
-- note
-- RAW 和 ROW_ID 自己输入不进去
--------------------------------------------------------

--------------------------------------------------------
-- 查询
--------------------------------------------------------
select * from "SYSTEM"."tbl_test"