数据库操作自用笔记

1.主表信息拆分到子表

INSERT INTO older_ext_info
(OLDER_ID,PROVIDER_PAYMENTS,ECONOMIC_SOURCES,ID_CARD_PHOTO_URL,PHOTO_URL,ID_CERTIFICATION_LAST_DATE,NOTE)
    (SELECT ID,PROVIDER_PAYMENTS,ECONOMIC_SOURCES,ID_CARD_PHOTO_URL,PHOTO_URL,ID_CERTIFICATION_LAST_DATE,NOTE
     FROM older_info
    )

2.增加字段

alter table  tenant_department_ex add YSY_APP_KEY varchar (50) DEFAULT NULL COMMENT "萤石云key";

3.删除字段

alter table monitoring_circuit drop column point_info;

4.更改字段类型

alter table nursing_arrange modify column EMPLOYEE_NURSE_NAME varchar(255);

5.更改表名

alter table ocpy_older_departure rename to tenant_ocpy_older_departure;

6.增加一条记录

INSERT INTO `data_level`(`ID`, `NAME`, `CODE`, `NAMES`, `TYPE`, `PARENT_ID`, `PARENT_NAME`, `PARENT_CODE`, `OCCUPY_STATUS`, `NOTE`, `TENANT_ID`, `VERSION`, `CREATE_BY`, `CREATE_DATE`, `UPDATE_BY`, `UPDATE_DATE`, `DEL_FLAG`, `TENANT_COMPANY_ID`, `TENANT_DEPT_ID`) VALUES (371, '评估服务', '20209147303651', '评估服务', 1, 0, NULL, NULL, '0', NULL, 82, NULL, 'QDDX75954834', '2020-08-25 07:28:45', '', NULL, '0', 82, NULL);

7.对库integtate_service_center中表的字段值进行操作

select CONCAT('update ',table_name,' set TENANT_COMPANY_ID=TENANT_ID;') from information_schema.tables
where table_schema='integrate_service_center'
and table_name not like 'system%';

8.创建表

-- 新增助餐配置表
create table eatting_config (
`ID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`NAME` varchar(50) DEFAULT NULL COMMENT '简称',
`CODE` varchar(50) DEFAULT NULL COMMENT '编码',
`FULL_NAME` varchar(50) DEFAULT NULL COMMENT '全称',
`TENANT_ID` int(11) DEFAULT NULL COMMENT '租户',
`VERSION` int(11) DEFAULT NULL COMMENT '版本号',
`CREATE_BY` varchar(32) DEFAULT NULL COMMENT '创建人',
`CREATE_DATE` datetime NOT NULL COMMENT '创建时间',
`UPDATE_BY` varchar(32) DEFAULT NULL COMMENT '更新人',
`UPDATE_DATE` datetime DEFAULT NULL COMMENT '更新时间',
`DEL_FLAG` varchar(10) DEFAULT NULL,
`TENANT_COMPANY_ID` int(11) DEFAULT NULL COMMENT '机构ID',
`TENANT_DEPT_ID` int(11) DEFAULT NULL COMMENT '部门ID',
PRIMARY KEY (`ID`) USING BTREE
)ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='助餐配置表';

9.如果存在表则删除

DROP TABLE IF EXISTS `circuit_point`;

9. 数据库中存储日期，查询年龄段

select * from 表名dao whereDATEDIFF ( year, 出生日期字段dao, GETDATE())>=20 and DATEDIFF ( year, 出生日期字段, GETDATE())<=25

10.年龄段

SELECT * FROM evaluate_plan_assess_elderly WHERE (YEAR(NOW()) - SUBSTRING(ID_CARD_NO,7,4)) > 24 AND (YEAR(NOW()) - SUBSTRING(ID_CARD_NO,7,4)) < 28

11.数据库获取字段年份

DATENAME(param,date)