数据库操作和sql 常用语法

视图

查询结果放在视图里，使用的时候查询视图就可以了（select * from V_USER_INFO_VIEW ）

CREATE OR REPLACE VIEW V_USER_INFO_VIEW AS
select id,name,age from db_user;

函数

case：

case when ‘条件’ then ‘符合条件’ else ‘不符合条件’ end

select id，
    (case when user.name = '小明' and user.status = '95' or user.age in ('18','20','24') then '学习中' else '出差中' end) as type from db_user user;

多重case when

select id，
    (case when user.name = '小明'  then '小明学习中'
    when user.name = '小兰'  then '小兰学习中'
    when user.name = '小黑'  then '小黑学习中'
     end) as type from db_user user;

可重复执行语句

oracle：

重复插入数据

-- 申明变量
DECLARE
VN_COUNT    NUMBER;
BEGIN
-- 查询该表是否存在该id
SELECT COUNT(*)
INTO VN_COUNT
FROM db_user where id='一键三连';
-- 如果不存在则执行语句，then后不仅可以接inset语句，还可以接update和delete
IF VN_COUNT <1 THEN
    INSERT INTO db_user
    (id, name, age, status)
    VALUES('一键三连', '小米', '18', '95');
commit;
END IF;
END;

重复创建字段

-- 申明变量
DECLARE
VN_COUNT     NUMBER;
-- 可以不要创建VC_STR
VC_STR       VARCHAR2(1000);
BEGIN
-- 查询该表中是否存在该字段
SELECT COUNT(1)
INTO VN_COUNT
-- USER_TAB_COLUMNS oracle系统表，存了所有的表名和字段名
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'db_user' AND COLUMN_NAME = 'live_type';
-- 如果不存在则执行语句，和上面一样，可以写成一样的，只是展示另一种方法
IF VN_COUNT < 1 THEN
	-- 将sql赋值给VC_STR并且执行
    VC_STR := 'ALTER TABLE db_user ADD live_type VARCHAR2(8)';
EXECUTE IMMEDIATE VC_STR;
END IF;
END;

MySql：

与oracle基本一样，只备注需要改的字段

重复插入数据

## 表名和字段
INSERT INTO db_user (id, name, age, status)
## 插入类容
SELECT '一键三连', '小米', '18', '95'  FROM DUAL
WHERE NOT EXISTS (
		## 根据条件查询是否存在对应的值，存在就不执行
        SELECT 1 FROM db_user WHERE id='一键三连' AND name= '小米' LIMIT 1
);

重复创建字段

DROP PROCEDURE IF EXISTS PRO_ADD_COL;
DELIMITER $$
CREATE PROCEDURE PRO_ADD_COL()
  BEGIN
    IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA =
    ## db_user表名，live_type准备添加的字段名
    DATABASE() AND TABLE_NAME = 'db_user' AND COLUMN_NAME = 'live_type') THEN
    	## 不存在就添加
      ALTER TABLE db_user ADD COLUMN live_type varchar(8) null comment '生活类型';
    ELSE
    	## 存在就修改
      ALTER TABLE db_user MODIFY COLUMN live_type varchar(8) null comment '生活类型';
    END IF;
  END$$
DELIMITER ;
CALL PRO_ADD_COL();
DROP PROCEDURE IF EXISTS PRO_ADD_COL;
COMMIT;
##GOGO

数据库基本操作

oracle

版本查询

select * from product_component_version;  

获取当前时间

-- 显示当前时间
select sysdate from dual;
-- 修改为当前时间
update DB_DATE set date = sysdate；

删除用户（cmd 窗口直接执行）

sqlplus  system/system  --enter 管理员用户名/密码 登录oracle
drop user DB_USER CASCADE; -- enter 删除用户，DB_USER 为用户名

用户密码修改（cmd 窗口直接执行）

sqlplus /nolog   --enter 登录
connect / as sysdba -- enter
alter user DB_USER identified by 123456;  --enter  DB_USER 为用户，123456为新密码

创建用户且授权（cmd 窗口直接执行）

sqlplus  system/system   --enter 管理员用户名/密码 登录oracle
create user DB_USER identified by 123456; -- enter DB_USER 为新用户，123456为用户密码
grant dba to JL; -- enter 授权crud

DMP导出于导入（cmd 窗口直接执行）

** 很重要

select * from dba_directories  --enter 检查对于变量于路径的对应关系

DMP导入

system/system导入所使用的用户名密码；orcl ：实例名；DATA_PUMP_DIR 为导入的dmp文件的路径变量，对应图片上第三条路径，提前把的dmp文件放入此路径的文件夹里；DE21123001.dmp 为文件名；DB_UAT:DB_NAME【dmp文件原用户名：新用户名（目标用户）】，原用户名需要与给文件的人确认，不然导不进去；DBUSER:USERS 【原表空间：临时表空间】表空间不一定需要，与文件保持一致，我这里有表空间，导入完成后如果原先不存在目标用户他会自动创建，自动创建的没有密码，参考上述修改密码方法

impdp system/system@orcl DIRECTORY=DATA_PUMP_DIR  DUMPFILE=DE21123001.dmp remap_schema=DB_UAT:DB_NAME remap_tablespace=DBUSER:USERS

DMP导出

与上一致，system/system导出所使用的用户名密码；DB_NAME 需要导出的用户，告知导入人；11.1.0.6.0 oracle

expdp system/system@orcl dumpfile=xxx.DMP  DIRECTORY=DATA_PUMP_DIR  schemas=DB_NAME  version=11.1.0.6.0

mysql

时间操作

select curdate(); --获取当前日期
select last_day(curdate()); --获取当月最后一天。
select DATE_ADD(curdate(),interval -day(curdate())+1 day); --获取本月第一天
select date_add(curdate()-day(curdate())+1,interval 1 month); -- 获取下个月的第一天
select DATEDIFF(date_add(curdate()-day(curdate())+1,interval 1 month ),DATE_ADD(curdate(),interval -day(curdate())+1 day)) from dual; --获取当前月的天数
SELECT DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY); --当年第一天：
SELECT concat(YEAR(now()),'-12-31'); --当年最后一天
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 1 DAY); --当前week的第一天
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) - 5 DAY); --当前week的最后一天
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 8 DAY); --前一week的第一天
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 2 DAY); --前一week的最后一天
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 15 DAY); --前两week的第一天
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 9 DAY); --前两week的最后一天
SELECT concat(date_format(LAST_DAY(now()),'%Y-%m-'),'01'); --当前month的第一天
SELECT LAST_DAY(now()); --当前month的最后一天
SELECT concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01'); --前一month的第一天
SELECT LAST_DAY(now() - interval 1 month); --前一month的最后一天
SELECT concat(date_format(LAST_DAY(now() - interval 2 month),'%Y-%m-'),'01'); --前两month的第一天
SELECT LAST_DAY(now() - interval 2 month); --前两month的最后一天
select concat(date_format(LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) + interval QUARTER(CURDATE())*3-3 month),'%Y-%m-'),'01'); --当前quarter的第一天
select LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) + interval QUARTER(CURDATE())*3-1 month); --当前quarter的最后一天
select concat(date_format(LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) + interval QUARTER(CURDATE())*3-6 month),'%Y-%m-'),'01'); --前一quarter的第一天
select LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) + interval QUARTER(CURDATE())*3-4 month);--前一quarter的最后一天
select concat(date_format(LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) + interval QUARTER(CURDATE())*3-9 month),'%Y-%m-'),'01'); -- 前两quarter的第一天
select LAST_DAY(MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) + interval QUARTER(CURDATE())*3-7 month); --前两quarter的最后一天

持续更新…