常用SQL命令
uuid
1.1 Mysql select replace(uuid(),'-','') -- 去掉'-' 1.2 SqlServer select newid() 1.3 Oracle SELECT sys_guid() from dual ; SELECT rawtohex(sys_guid()) from dual; -- 处理乱码
返回日期单独部分。
2.1 Mysql SELECT *,MONTH(weight_time) AS d1 FROM ZC_WEIGHT_LIST2.2 SqlServer SELECT DATEPART ( MONTH, weight_time ) AS m1,* FROM ZC_WEIGHT_LIST
mysql参考地址:https://www.runoob.com/mysql/mysql-functions.html
SqlServer参考地址:https://www.cnblogs.com/luna-hehe/p/8191873.html
计算时间差
3.1 Mysql SELECT TIMESTAMPDIFF(MONTH,'2009-10-01','2009-09-01'); select * from zc_weight_list where DATE_FORMAT(weight_time, '%Y') = DATE_FORMAT('2019-01-01', '%Y') select * from zc_weight_list where DATE_FORMAT(weight_time, '%Y-%m') = DATE_FORMAT('2019-08-23', '%Y-%m') select * from zc_weight_list where DATE_FORMAT(weight_time, '%Y-%m-%d') = DATE_FORMAT('2019-08-29', '%Y-%m-%d') 3.2 SqlServer SELECT * FROM ZC_WEIGHT_LIST WHERE datediff( YEAR, '2019-09-23', weight_time ) =0
时间和字符串互相转换
4.1 Mysql 4.1.1 时间转字符串 SELECT DATE_FORMAT(NOW(), '%Y-%m-%d'); 4.1.2 字符串转时间 SELECT STR_TO_DATE('2016-01-02', '%Y-%m-%d %H');
参考地址:https://www.cnblogs.com/wbxk/p/10857342.html
日期格式化
5.1 Mysql SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s') AS now_time -- 日期格式化 5.2 SqlServer
5.2.1. 只获取时分秒,并且格式为:HH:mm:ss Select CONVERT(varchar(100), GETDATE(), 8) Select CONVERT(varchar(100), GETDATE(), 24) Select CONVERT(varchar(100), GETDATE(), 108) 返回结果:23:03:53 5.2.2. 只获取年月日,并且格式为:yyyy-mm-dd Select CONVERT(varchar(100), GETDATE(), 23) 返回结果:2019-03-22 5.2.3. 获取年月日时分秒,并且格式为:yyyy-mm-dd HH:mm:ss Select CONVERT(varchar(100), GETDATE(), 20) Select CONVERT(varchar(100), GETDATE(), 120) 返回结果:2019-03-22 23:08:06 5.2.4. 获取年月日时分秒以及毫秒,并且格式为yyyy-mm-dd HH:mm:ss. 毫秒三位 Select CONVERT(varchar(100), GETDATE(), 121) 返回结果:2019-03-22 23:09:39.533 5.2.5. 年.月.日 Select CONVERT(varchar(100), GETDATE(), 102) 返回结果:2019.03.22 5.2.6. 日/月/年 Select CONVERT(varchar(100), GETDATE(), 103 ) 返回结果:22/03/2019
SqlServer参考地址:https://www.w3school.com.cn/sql/func_convert.asp
修改字段长度
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型(要修改的长度); 比如:ALTER TABLE STUDENT MODIFY COLUMN ID VARCHAR(64);
删除列
alter table 表名 drop column 列名; -- mysql、Oracle、SQL server 三库通用
增加列
alter table 表名 add 列名 数据类型(数据长度); -- 不同数据库的同一数据类型的表现形式可能是不一样的。 比如字符串:mysql是varchar、Oracle是varchar2 -- 比如 alter table ETC_STATUS_BACKINFO add HEATVERSION VARCHAR2(64);
创建表
1)mysql
CREATE TABLE `demo_etc_status_otherinfo` ( `id` varchar(32) NOT NULL COMMENT '业务id', `expend1` varchar(100) DEFAULT NULL COMMENT '扩展字段1', `expend2` varchar(100) DEFAULT NULL COMMENT '扩展字段2', `expend3` varchar(100) DEFAULT NULL COMMENT '扩展字段3', `create_time` datetime DEFAULT NULL COMMENT '创建时间', `update_time` datetime DEFAULT NULL COMMENT '修改时间', `is_void` int(11) DEFAULT NULL COMMENT '是否逻辑删除', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='表名描述';
2)SQL server
create table DEMO_ETC_STATUS_OTHERINFO ( id nvarchar(32) not null constraint DEMO_ETC_STATUS_OTHERINFO_pk primary key nonclustered, expend1 nvarchar(100), expend2 nvarchar(100), expend3 nvarchar(100), create_time datetime, update_time datetime, is_void bit ) go exec sp_addextendedproperty 'MS_Description', '表名描述', 'SCHEMA', 'dbo', 'TABLE', 'DEMO_ETC_STATUS_OTHERINFO' go exec sp_addextendedproperty 'MS_Description', '业务id', 'SCHEMA', 'dbo', 'TABLE', 'DEMO_ETC_STATUS_OTHERINFO', 'COLUMN', 'id' go exec sp_addextendedproperty 'MS_Description', '扩展字段1', 'SCHEMA', 'dbo', 'TABLE', 'DEMO_ETC_STATUS_OTHERINFO', 'COLUMN', 'expend1' go exec sp_addextendedproperty 'MS_Description', '扩展字段2', 'SCHEMA', 'dbo', 'TABLE', 'DEMO_ETC_STATUS_OTHERINFO', 'COLUMN', 'expend2' go exec sp_addextendedproperty 'MS_Description', '扩展字段3', 'SCHEMA', 'dbo', 'TABLE', 'DEMO_ETC_STATUS_OTHERINFO', 'COLUMN', 'expend3' go exec sp_addextendedproperty 'MS_Description', '创建时间', 'SCHEMA', 'dbo', 'TABLE', 'DEMO_ETC_STATUS_OTHERINFO', 'COLUMN', 'create_time' go exec sp_addextendedproperty 'MS_Description', '修改时间', 'SCHEMA', 'dbo', 'TABLE', 'DEMO_ETC_STATUS_OTHERINFO', 'COLUMN', 'update_time' go exec sp_addextendedproperty 'MS_Description', '是否逻辑删除', 'SCHEMA', 'dbo', 'TABLE', 'DEMO_ETC_STATUS_OTHERINFO', 'COLUMN', 'is_void' go
3)Oracle
create table DEMO_ETC_STATUS_OTHERINFO ( ID VARCHAR2(32) not null constraint DEMO_ETC_STATUS_OTHERINFO_PK primary key, EXPEND1 VARCHAR2(100), EXPEND2 VARCHAR2(100), EXPEND3 VARCHAR2(100), CREATE_TIME DATE, UPDATE_TIME DATE, IS_VOID NUMBER ) / comment on table DEMO_ETC_STATUS_OTHERINFO is '表名描述' / comment on column DEMO_ETC_STATUS_OTHERINFO.ID is '业务id' / comment on column DEMO_ETC_STATUS_OTHERINFO.EXPEND1 is '扩展字段1' / comment on column DEMO_ETC_STATUS_OTHERINFO.EXPEND2 is '扩展字段2' / comment on column DEMO_ETC_STATUS_OTHERINFO.EXPEND3 is '扩展字段3' / comment on column DEMO_ETC_STATUS_OTHERINFO.CREATE_TIME is '创建时间' / comment on column DEMO_ETC_STATUS_OTHERINFO.UPDATE_TIME is '修改时间' / comment on column DEMO_ETC_STATUS_OTHERINFO.IS_VOID is '是否逻辑删除' /
数据传输
参考地址:
https://jingyan.baidu.com/article/656db918ca2d3fe380249c4b.html
新建表之后查不到新建的表
8.1 一般常出现在Oracle数据库中:在Oracle中,表名要大写,否则查不到 参考地址:https://blog.csdn.net/SONGCHUNHONG/article/details/77961864
将表字段全部修改为大写
9.1 Oracle BEGIN FOR c IN ( SELECT column_name cn FROM all_tab_columns WHERE table_name = 'ETC_BASEINFO_CAMERA' ) loop BEGIN execute IMMEDIATE 'alter table ETC_BASEINFO_CAMERA rename column "' || c.cn || '" to ' || c.cn; EXCEPTION WHEN others THEN dbms_output.put_line ( 'ETC_BASEINFO_CAMERA' || '.' || c.cn || '已经存在' ); END; END loop; END;
Oracle参考地址:
https://blog.csdn.net/miklechun/article/details/50246217