数据库常用语句

最新推荐文章于 2024-05-11 16:13:24 发布

GHW_Himmel

最新推荐文章于 2024-05-11 16:13:24 发布

阅读量140

点赞数

本文链接：https://blog.csdn.net/qq_24422109/article/details/82967269

版权

---------------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";')