1. 数据库连接
1.1 Oracle
1.1.1 命令行连接
sqlplus /nolog
conn username/password@IP:[1521]/数据库服务名 [as sysdba]
使用tnsname
vi $ORACLE_HOME/NETWORK/ADMIN/tnsnames.ora
ORCL = “orcl服务名”
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 主机IP地址)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ABC)
)
sqlplus /nolog
conn username@orcl #使用配置文件内的名字连接,配置文件需在默认位置
1.1.2 cx_oracle
1.2 mysql
1.2.1 命令行连接
连接数据库mysql -u maria -p
使用数据库 use hangzhou;
1.2.2 pymysql
1.3
df sf
2. 数据库管理
2.1 数据库查询
查看数据库 show databases;
查看当前使用的数据库 select database();
show tables；
查看数据库状态 status;
查看表结构 desc t1;
#连接当前系统连接数
select count(*) from v$process
#查询最大连接数设置
select value from v$parameter where name = 'processes'
2.2 用户管理
创建用户 CREATE USER u1 IDENTIFIED BY ’pass’;
修改用户名 RENAME USER u1 TO u2;
修改密码 SET PASSWORD FOR u1 = Password(‘pass’);
修改自己密码 SET PASSWORD = PASSWORD(‘pass’);
删除用户 DROP USER u1；
查看当前登录用户 SELECT USER();
查看用户表 use mysql;
select user from user;
2.3 权限管理
查看自己的授权 show grants；
查看其他用户权限 show grants for u1;
授权 grant 权限1,权限2,select on 数据库.表 to u1;
更新授权 flush privileges;
在数据库test的所有表授予u1用户查询权限
grant select on test.* to u1;
收回权限 grant revoke 权限1 on 数据库.表 to u1;
创建用户同时授权
grant reload,lock tables,replication client on *.* to 'uback’@’localhost' identified by 'uback';
权限清单
usage 无权限
all 除进行授权本身外的所有权限
2.4 事务
创建保存点a savepoint a;
回滚到保存点 rollback to a;
回滚到上次保存点 rollback;
提交修改 commit;
2.5 定时任务
2.5.1 创建
declare
job number;
BEGIN
DBMS_JOB.SUBMIT(
JOB => job, /*自动生成JOB_ID*/
WHAT => 'testJob;', /*需要执行的存储过程名称或SQL语句*/
NEXT_DATE => sysdate, /*初次执行时间-立即执行*/
INTERVAL => 'trunc(sysdate,''mi'')+1/(24*60)' /*每隔1分钟执行一次*/
);
commit;
end;
2.5.2 查询
select * from all_jobs where what like ‘%存储过程名字%’;
2.5.3 启动
declare
begin
DBMS_JOB.RUN(24); /*24 job的id*/
commit;
end
2.5.4 停止
declare
begin
dbms_job.broken(24,true,sysdate); /*停止一个job,jobId, job的ID,里面参数true也可是false，next_date（某一时刻停止）也可是sysdate（立刻停止）。 */
commit;
end;
2.5.5 删除
declare
begin
dbms_job.remove(24); /*删除自动执行的job,参数是 job的id*/
commit;
end;
2.5.6 修改
修改执行间隔
declare
begin
dbms_job.interval(24,interval => 'TRUNC(SYSDATE)+1'); /*第一个参数为job的ID,第二个参数interval: 计算下一次任务执行的时间表达式*/
commit;
end;
修改下一次执行的时间
declare
begin
dbms_job.next_date(24,to_date('2020-11-9 12:08:00','yyyy-mm-dd hh24:mi:ss')); /*第一个参数:job的ID；第二个参数:要修改后的计算下一次执行的时间表达式*/
commit;
end;
更换要执行的存储过程
declare
begin
dbms_job.what(24,'testJob2();'); /* 第一个参数:job的ID；第二个参数:要更改的新操作名称（操作名必须存在）*/
commit;
end;
2.5.7 时间参数说明
INTERVAL 部分参数值示例：
每天午夜12点: 'TRUNC(SYSDATE + 1)'
每天早上8点30分: 'TRUNC(SYSDATE + 1) + （8*60+30）/(24*60)'
每星期二中午12点: 'NEXT_DAY(TRUNC(SYSDATE ), ''TUESDAY'' ) + 12/24'
每个月第一天的午夜12点: 'TRUNC(LAST_DAY(SYSDATE ) + 1)'
每个季度最后一天的晚上11点: 'TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), 'Q' ) -1/24'
每星期六和日早上6点10分: 'TRUNC(LEAST(NEXT_DAY(SYSDATE, ''SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) + （6×60+10）/（24×60）'
每月25号00：00执行: 'TRUNC(LAST_DAY(SYSDATE ) + 25)'
－－－－－－－－－－－－－－－－－－－－－－－－－－
1:每分钟执行
Interval => TRUNC(sysdate,'mi') + 1/ (24*60)
或
Interval => sysdate+1/1440
2:每天定时执行
例如：每天的凌晨1点执行
Interval => TRUNC(sysdate) + 1 +1/ (24)
3:每周定时执行
例如：每周一凌晨1点执行
Interval => TRUNC(next_day(sysdate,'星期一'))+1/24
4:每月定时执行
例如：每月1日凌晨1点执行
Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24
5:每季度定时执行
例如每季度的第一天凌晨1点执行
Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24
6:每半年定时执行
例如：每年7月1日和1月1日凌晨1点
Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+1/24
7:每年定时执行
例如：每年1月1日凌晨1点执行
Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+1/24
2.5.8 job参数说明
dba_jobs 表中字段含义：
JOB 任务的唯一标识码
LOG_USER 提交任务的用户
PRIV_USER 赋予任务权限的用户
SCHEMA_USER 对用户作语法分析的用户模式
LAST_DATE 最后一次成功执行任务的时间
LAST_SEC 最后一次成功执行任务的时间的时分秒
THIS_DATE 正在执行的任务的开始时间，若没有则为空
THIS_SEC 正在执行的任务的开始时间的时分秒，若没有则为空
NEXT_DATE 下一次执行定时任务的时间
NEXT_SEC 下一次执行定时任务的时间的时分秒
TOTAL_TIME 执行当前任务所需要的时间，单位：秒
BROKEN 标志参数，Y表示任务中断，以后不会再运行
INTERTAL 计算下一次执行定时任务的时间表达式
FAILURES 当前定时任务执行失败的总次数
WHAT 执行任务的PL/SQL代码块
NLS_ENV 任务执行的NLS会话设置
MISC_ENV 定时任务运行的其他一些参数设置
INSTANCE 标识当前任务运行是否受限，0 没有受限
3. 表操作
3.1 建表
创建表空间
create tablespace abc
datafile ‘/u01/app/oracle/oradata/TestDB11/catalog.dbf’ 自定义数据文件位置及名字
size 100M #大小
extent management local autoallocate 扩展自动管理
segment space management auto;
创建表
CREATE TABLE t1 (
c1 varchar2(50) PRIMARY KEY 主键
c2 date REFERENCES t2(c2) 外键关联到t2的c2列
c3 date UNIQUE 唯一约束,该列的值不可以重复
c4 date not null 非空
c5 date )
TABLESPACE abc; 指定表空间
复制创建表
CREATE TABLE test TABLESPACE abc AS SELECT * FROM test2;
仅复制表结构
CREATE TABLE tes AS SELECT * FROM test2 where rownum<1;
3.2 新增
增加列 ALTER TABLE test ADD hobby nvarchar2(20);
添加索引 CREATE INDEX index1 ON t1(c1)
添加唯一索引 CREATE UNIQUE INDEX index1 ON t1(c1)
添加主键 ALTER TABLE t1 ADD PRIMARY KEY(字段名);
添加主键 ALTER TABLE test ADD CONSTRAINT test_pk PRIMARY KEY(ename);
添加外键 ALTER TABLE t ADD CONSTRAINT f3 FOREIGN KEY(c2) REFERENCES t3(id);
//外键条件,一个表的非主键连接另一个表的主键
添加唯一约束 ALTER TABLE test ADD UNIQUE(id);
check约束 ALTER TABLE test ADD CONSTRAINT emp_chk CHECK(empno>7000);
3.3 删除
#删除表空间
drop tablespace abc
including contents 同时删除表空间中的数据
cascade constraints; 同时删除完整性限制
#删除表
DROP TABLE test1
CASCADE CONSTRAINTS PURGE; #同时删除引用及数据文件
删除列 ALTER TABLE test DROP COLUMN hobby;
删除唯一约束 ALTER TABLE test DROP UNIQUE(id); #列名
删除约束 ALTER TABLE test DROP CONSTRAINT emp_chk ; #约束的名字
删除主键 ALTER TABLE t1 DROP PRIMARY KEY ;
删除主键 ALTER TABLE test DROP CONSTRAINT id_pk; #主键名字
删除外键 ALTER TABLE new DROP CONSTRAINT new_test3; #外键名字
3.4 修改
改表名 ALTER TABLE t6 RENAME TO t3;
改列名 ALTER TABLE test RENAME COLUMN id TO id1;
改列数据类型 ALTER TABLE t MODIFY id integer;
设置列非空 ALTER TABLE test MODIFY ename NOT NULL;
设置列可以为空 ALTER TABLE test MODIFY ename NULL;
列设置默认值 ALTER TABLE test MODIFY(id DEFAULT 22);
3.5 查询
获取表的全部字段
select wm_concat(column_name) from user_tab_columns where table_name=upper('tableName')
查询表字段类型
select * from all_tab_cols where table_name=upper('表名')
查询建表语句(可以看字段类型)
select dbms_metadata.get_ddl('TABLE',upper('表名')) from dual;
查询索引
select * from all_ind_columns where table_name=upper('表名')
4. 数据类型
5. 插入行
INSERT INTO test VALUES('smith',7369,5);
INSERT INTO t1(c1,c2,c3) VALUES(’abc’,’123’,’hahaha’);
从另一个表查询数据插入
INSERT INTO test(id,ename) SELECT id,ename FROM emp;
insert into tab1 select * from tab2 #一个表全部数据插入另一个表
Merge into存在更新，不存在插入，
merge into yytbt t1
using yytb_tmp t2
on (t1.编号=t2.编号)
when matched then
update set t1.a=t2.a
when not matched then
insert (c1,c2,c3)
values (t2.a,t2.b,t2.c) '''
不能更新on连接的字段,有时update出错,需带where字句
6. 删除行
删除某条记录 DELETE FROM t1 WHERE id=5;
删除全部行,可撤销 DELETE FROM test;
删除全部行,不可撤销 TRUNCATE TABLE t1;
删除指定列重复的数据
delete from t1 where rowid not in (select max(rowId) from t1 group by user_Id)
7. 更新列
更新单字段
UPDATE t1 SET c2=’123’ WHERE c1=young;
更新多字段
update t1 set (字段1, c2) = (值1, v2) WHERE c1=young;
忽略错误，更新能正常更新的行
UPDATE IGNORE t1 set
使用子查询更新
UPDATE test SET id =(SELECT id FROM t2 WHERE eno=7369) WHERE name=’smith’;
清空列内容 UPDATE test SET id=null;
8. 数据库及表信息查询
8.1 数据库查询
查看数据库 show databases;
查看当前使用的数据库 select database();
show tables；
查看数据库状态 status;
查看表结构 desc t1;
#连接当前系统连接数
select count(*) from v$process
#查询最大连接数设置
select value from v$parameter where name = 'processes'
8.2 表查询
获取表的全部字段
select wm_concat(column_name) from user_tab_columns where table_name=upper('tableName')
查询表字段类型
select * from all_tab_cols where table_name=upper('表名')
查询建表语句(可以看字段类型)
select dbms_metadata.get_ddl('TABLE',upper('表名')) from dual;
查询索引
select * from all_ind_columns where table_name=upper('表名')
select * from all_indexes where table_name = upper('表名') ; --更详细
查询更新时间等
select * from ALL_OBJECTS where object_name =upper('表名')
9. 查询select
9.1 基础查询
9.1.1 去重
单字段 select distinct c1 from t1;
多字段 select distinct(c1,c2) from t1;
查询某个字段不重复的全部记录
SELECT * FROM t1 WHERE rowid IN (SELECT max(rowid) FROM t1 WHERE lng IS NOT NULL GROUP BY channel_id )
9.1.2 模糊查询
like
where c1 like ‘_alo%’; not like
_ 单个字符
% 零或多个字符,无法匹配null
[abc] 其中一个字符
[^abc] 不在其中的字符
[!abc] 同上
regexp_like 正则表达式
where regexp_like(列名c1,'\d+$'); 匹配出0到多个数字结尾的项
9.1.3 限制返回行数
Oracle查询前6行 select c1 from t where rownum<6; 符号只能< ,<= ,!=
Mysql查询前6行 select c1 from t limit 6; 计数从0开始
Mysql查询从第7行开始的6行
select c1 from t1 limit 6,6;
Oracle查询第6行以后的数据
SELECT c1 ,c2 from ( SELECT ROWNUM N, c1 ,c2 FROM t1 ) WHERE N>6
注:内层rownum 一定要用别名, 不然会冲突出错
9.1.4 日期查询
字符串转时间to_date(c1, 'YYYY-MM-DD HH24:MI:SS' )
时间转字符串 to_char(时间列, 'YYYY-MM-DD' )
时间列查询 20210930以后的数据
where to_char(时间列, 'YYYY-MM-DD') > 20210930
9.1.5 表连接与子查询
INNER JOIN
SELECT a.a1,b.b1 FROM a,b WHERE a.a2=b.b2;
SELECT a.a1,b.b1 FROM a JOIN b ON a.a2=b.b2;
full join 全连接
left join,左连接
right join
union
将两个select语句的查询结果合并到一起，表1的数据在上,对应列的数据类型要相同,union得到的结果会去重，
union all
同上,不去重
嵌套子查询
先执行括号内的子查询,再执行父查询
相关子查询
先执行父查询,父查询的结果逐行传递给子查询执行.即父查询的每一行都执行一遍子查询
select c1,c2,c3 from t1 where c3>
(select avg(c4) from t1 where c1=t1.c1)
9.1.6 列转行
把查询出的某列显示在一行 select wm_concat(c1) from t1
根据分组把某列展示为多行 select wm_concat(c1) from t1 group by c2
9.1.7 操作符
IN:
WHERE LastName IN ('Adams','Carter');
WHERE LastName IN (select c2 from t2);
NOT IN
IN 先查询外表（左侧），再查询内表（右侧）
比or快,可以代替or
EXISTS:
WHERE LastName EXISTS (select c2 from t2);
EXISTS 先查询内表（右侧），再查外表
用NOT EXISTS 代替NOT IN ,
BETWEEN区间:
WHERE Name BETWEEN 'Adams' AND 'Carter'
NOT BETWEEN (数值,文本,日期等)
空值 where id is null; is not null
9.2 字符串函数
删除指定字符(删除c1字段中的楼和层, a可以是任意c1中不存在的字符)
select translate(c1 ,'a楼层','a') from t1
获取指定字符的位置,instr
instr(c1,’字符’)
instr(c1,’字符’,3,4)
3起始位置, 缺省1
4匹配次数, 缺省全部
拆分列(以-符号拆分c1列, -之前c2, 之后c3)
select c1,
substr(c1, instr(c1, '-')-1) as c2
substr(c1, instr(c1, '-')+1) as c3
from t1;
正则表达式
1.匹配出c1列开头的数字
select regexp_substr(c1 , '^\d+') from t1;
9.3