---------------oracle创建数据库-------------
create tablespace PHMS_SYS datafile 'D:\app\Administrator\oradata\ghw\PHMS_SYS.dbf' size 500M;
create user PHMS_SYS identified by PHMS_SYS default tablespace PHMS_SYS;
grant connect,resource to PHMS_SYS;
grant dba to PHMS_SYS;
-----------------------------------------------------------------
启动mysql
net start mysql
打开mysql命令
mysql -h localhost -u root -p
查看mysql连接数
show processlist;
查看最大连接数:
show variables like '%max_connections%';
修改最大连接数(近本次)
set global max_connections=1000;
查看mysql server超时时间
show global variables like '%timeout%';
设置mysql server超时时间(以秒为单位)
set global wait_timeout=10;
关闭进程锁
kill 进程
-----------------msyql赋予权限-----------------------------
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.100' IDENTIFIED BY '' WITH GRANT OPTION;
----------------------------------------1---------------------------------------
字段设置为空:UPDATE business_data SET DII_CODE=null
----------------------------------------2---------------------------------------
将mysql中字段为空的改为特定的值:
UPDATE 表
SET 列名= CASE WHEN 列名 IS NULL
THEN 改后的值
ELSE REPLACE(列名,原值,改后的值)
END
-------------------age----------------------
UPDATE t_inhosp_register
SET ReadState= CASE WHEN ReadState='R'
THEN 'N'
ELSE REPLACE(ReadState,'R','N')
END
------------------------------------------3-------------------------------------
将某个字段设置为空
UPDATE t_outpat_fee_detail SET ReadState='N' where business_time LIKE '2016-12-15%';
UPDATE t_outpat_fee_detail set dataState=NULL where business_time LIKE '2016-12-15%';
UPDATE `t_outpat_register` SET ReadState='N' where business_time>'2017-03-06 00:00:00' and dataState='wrongdatabase';
UPDATE `t_outpat_register` SET dataState=NULL where business_time>'2017-03-06 00:00:00' and dataState='wrongdatabase';
------------------------------------------4-------------------------------------
将mysql中字段中特定的值改为空:
UPDATE 表
SET 列名= CASE WHEN 列名='原值'
THEN 改后的值
ELSE REPLACE(列名,原值,改后的值)
END
----------------------------------------5--------------------------------------
,mysql数据库修改初始密码。
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');
添加远程权限
USE mysql
SELECT User, Password, Host FROM user
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.102.125' IDENTIFIED BY '' WITH GRANT OPTION
-------------------------------------------------------------------------------
表中添加一个字段
alter table 表名 add column 字段名 varchar(32);
age : alter table t_data_evaluation add column test varchar(32);
age : alter table t_data_evaluation MODIFY column business_no text;
---------------------------------------6--------------------------------------
查询所有不为空的表
select TABLE_NAME from information_schema.tables where TABLE_SCHEMA='数据库名' and table_rows>0;
查询所有表名
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '数据库名'
---------------------------------------7---------------------------------------
将性别代码为空性别名称为男女的字段修改性别代码
UPDATE `t_outhosp_register` SET gender_code='1' WHERE gender_code_dn='男' AND ISNULL(gender_code) and business_time LIKE '2016-12-15%';
UPDATE `t_outhosp_register` SET gender_code='2' WHERE gender_code_dn='女' AND ISNULL(gender_code) and business_time LIKE '2016-12-15%';
--------------------------------------8---------------------------------------
将性别代码为空的字段对应的ReadState改为N
UPDATE t_outhosp_register SET ReadState='N'
WHERE ReadState='R' AND ISNULL(gender_code);
-------------------------------------9----------------------------------------
将性别代码为空和dataState为wrongdatabase的字段对应的dataState改为NULL
UPDATE t_outhosp_register set dataState=NULL WHERE dataState='wrongdatabase' AND ISNULL(gender_code);
-------------------------------------10--------------------------------------
删除语句
DELETE FROM 表名称 WHERE 列名称 = 值
-------------------------------------11--------------------------------------
修改t_patient_card_day_count表的updata_time为最新
UPDATE `t_patient_card_day_count` SET update_time='2016-11-15 16:15:56';
-------------------------------------12-------------------------------------
查询不同的列名
select DISTINCT 列名 from 表 名
---------------------------------------------------------------------------
批量查看某张表某段时间数据
select count(*) as number ,LEFT(insert_datetime,10) from t_outpat_fee_detail p where LEFT(insert_datetime,10)>"2017-03-01"
group by LEFT(insert_datetime,10) desc;
-----------------------------------------------------------------------------------------
查询重复数据
select business_no,count(*) as count from `t_treat_income` group by business_no having count>1;
删除重复数据
1.查找出重复的数据
2.直接删除重复数据。
-------------------------------将一张表中的某个字段添加到另外一张表中------------------
INSERT into t_range_type(id ,RT_CODE_SYSTEM_NAME) select t_range_type_copy.ID, t_range_type_copy.RT_CODE_SYSTEM_NAME from t_range_type_copy
-----------------------------查询某个字段在数据库中的表的所有表名------------------------------------------------------------
//TABLE_SCHEMA字段为db的名称(所属的数据库),字段TABLE_NAME为表的名称。
select table_name from information_schema.columns where TABLE_SCHEMA='processordata' and column_name='org_code' ;
select table_name from information_schema.columns where column_name='organ_Id';
select table_name from information_schema.tables where table_schema='processordata'
-----------------------修改字段数据类型长度----------------------------------
select CONCAT("alter table ",table_name," modify column hash_code VARCHAR(32);") from information_schema.`TABLES` where TABLE_SCHEMA="rightdata_cope";
------------------统计数据--------------------------
-----ishare----------
SELECT * FROM `t_dataquality_sourcetype_statics` WHERE record_Insert_Time>='2017-04-01 00:00:00'
AND record_Insert_Time<='2017-04-10 23:59:59' AND table_Name='t_treat_income';
----rightdatabase-----
select count(*) as number ,LEFT(insert_datetime,10) from t_treat_income p where LEFT(insert_datetime,10)>="2017-04-01"
AND LEFT(insert_datetime,10)<="2017-04-10" group by LEFT(insert_datetime,10) desc;
--------------------------分类统计--------------------------------------------------------------------------
SELECT check_type_code,check_type_name , count(*) FROM t_check_rec GROUP BY check_type_name ;
-----------------------------------------------------
select left(a.business_time,10),sum(a.total_fee)
from t_treat_income a
where a.business_time like '2016-10%'
and a.diag_type_code = '1'
group by left(a.business_time,10)
order by left(a.business_time,10);
-----------------------------------------------
DELETE FROM t_outpat_register WHERE business_time < '2017-03-15 14:22:55';
---------------------------------一卡通四张表------------------------------------------------------
SELECT * FROM t_h_patient_card_reg WHERE business_time LIKE '2016-11%' ORDER BY business_time DESC;
SELECT * FROM t_h_pcr_sync_front_count WHERE business_time LIKE '2016-11%' ORDER BY business_time DESC;
SELECT * FROM t_patient_card_day_count WHERE business_time LIKE '2016-11%' ORDER BY update_time DESC;
SELECT * FROM t_patient_card_reg WHERE business_time LIKE '2016-11%' ORDER BY business_time DESC;
--------------------------------------------------------------------------------------------------------
触发器
DROP TRIGGER IF EXISTS t_trigger_outhosp
-----------------------
CREATE TRIGGER t_trigger_outhosp
BEFORE INSERT ON t_outhosp
FOR EACH ROW
BEGIN
UPDATE t_outhosp SET gender_code='1' WHERE gender_code_dn='男' ;
UPDATE t_outhosp SET gender_code='2' WHERE gender_code_dn='女' ;
END;
------------------------------------------------------------------------------
root使用123从任何主机连接到mysql服务器的话。
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123' WITH GRANT OPTION;
---------------------------------分类统计------------------------------------------------
select doct_ad_proj_type_code ,doct_ad_proj_type_code_dn,sum(doct_ad_proj_type_code_dn) total from t_emr_doct_advice
group by doct_ad_proj_type_code_dn order by doct_ad_proj_type_code_dn
----------------------------------正确库添加hash_code--------------------------------------
select CONCAT('alter table ',table_name,' add hash_code varchar(32);') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='rightdata';
-------------------------添加机构号-----------------------------------
select CONCAT('alter table ',table_name,' add org_code varchar(25) DEFAULT "455769035";') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='processordata';
-------------------------添加INSTOCK_TIME-----------------------------------
select CONCAT('alter table ',table_name,' add INSTOCK_TIME datetime NULL DEFAULT CURRENT_TIMESTAMP;') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='gb_processordata';
------------------------------------ishare中间库全部置为N------------------------------------
select CONCAT('UPDATE ',table_name,' SET ReadState="N" where ReadState="W" and insert_datetime>"2017-07-01 00:00:00";')
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='processordata';
select CONCAT('UPDATE ',table_name,' SET dataState=NULL where insert_datetime>"2017-03-06 00:00:00";')
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='processordata';
------------------------------------删除中间库部分历史数据-------------------------------------
select CONCAT('DELETE FROM ',table_name,' where left(insert_datetime,10)>"2017-10-01" AND ReadState="R" ;')
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='processordata';
-----------------------------------中间库数据部分cope--------------------------------------------
select CONCAT('insert into processordata_copy.',table_name,' select * from processordata.',table_name,' where left(insert_datetime,10)>"2017-05-10";')
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='processordata';
select CONCAT(' UPDATE ',table_name,' SET ReadState="N" where insert_datetime>"2017-03-06 00:00:00";')
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='processordata';
select CONCAT('UPDATE ',table_name,' SET dataState=NULL where insert_datetime>"2017-03-06 00:00:00";')
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='processordata';
----------------------------------------------------------------------------------------------
UPDATE t_term_range_info t, t_range_type r SET t.table_name=r.rt_code_system_name WHERE t.rt_Type_Id=r.id;
UPDATE`t_mapping_detail` m ,t_term_range_info t SET m.item_in_name=t.tri_Code_Name
WHERE m.item_in_value=t.tri_Code AND m.table_in_name=t.table_name AND t.version='BAOAN_PTSYZYDM_V2.0';