文章目录一、Oracle1.1 查询Oracle各项信息1.2 Oracle12c单机服务启动/停止1.3 Oracle 锁表与解锁1.4 Oracle常用函数二、Mysql2.1 Mysql基础2.1.1 数据存储结构2.1.2 索引2.1.3 SQL优化2.1.4 踩坑备忘2.2 查询Mysql5.7各项信息2.2.1 死锁日志分析2.2.2 分析锁等待关系2.3 Mysql5.7常用函数三、SqlServer1 账户创建2 查看外部连接暴露端口四、数据库事务1 事务的ACID属性2 数据库中的并发问题3 数据库事务的隔离级别一、Oracle1.1 查询Oracle各项信息1、查看当前数据库建立的会话情况selectcount(a.sid)当前会话数,max(b.num)当前活跃数,max(c.value)最大会话数fromv$sessionaleftjoin(selectcount(1)numfromv$sessionwherestatusACTIVE)bon11leftjoin(selectvaluefromv$parameterwherenamesessions)con11;2、查看当前数据库建立的连接情况selectcount(a.pid)当前连接数,max(c.value)最大连接数fromv$process aleftjoin(selectvaluefromv$parameterwherenameprocesses)con11;3、查询数据库正在执行Sql信息进程Id、用户、系统、执行语句...selectalter system kill session ||b.sid||,||b.serial#||; 终止进程,b.sid oracleID,b.username Oracle用户,b.serial#,spid 操作系统ID,paddr,sql_text 执行的SQL,b.machine 计算机名fromv$process ainnerjoinv$sessionbona.addrb.paddrandb.sql_hash_value0leftjoinv$sqlarea conb.sql_hash_valuec.hash_value;1.2 Oracle12c单机服务启动/停止# 停止服务su- oracle sqlplus / as sysdbashutdownimmediate;exitlsnrctl stop# 停止监听# 启动服务lsnrctl start# 启动监听sqlplus / as sysdba startup;exit1.3 Oracle 锁表与解锁-- 查看锁表会话、表名、用户名SELECTa.OS_USER_NAME,c.owner,c.object_name,b.sid,b.serial#, logon_timeFROMv$locked_object a,v$sessionb,dba_objects cWHEREa.session_idb.sidANDa.object_idc.object_idORDERBYb.logon_time;-- 杀死锁表进程ALTERSYSTEMKILLSESSIONsid,serial#;1.4 Oracle常用函数正则表达式函数REGEXP_LIKE(column, regexp)column要匹配的列名或字符串regexp正则表达式如匹配是否包含非字母数字的字符REGEXP_LIKE(col, [^A-Za-z0-9])时间截断函数TRUNC(date, dateFormat)date截断的时间sysdate当前数据库服务器带时区的时间current_timestamp at time zone UTC将当前时间转化为对应时区UTC时间dateFormat截断的时间精度-- 截取到当前年第一天 2024-01-01 00:00:00selecttrunc(sysdate,yyyy)fromdual-- 截取到当前小时 2024-08-28 14:00:00selecttrunc(sysdate,hh24)fromdual-- 截取UCT时区当前小时 2024-08-28 06:00:00selecttrunc(current_timestampattimezoneUTC,hh24)fromdual条件运算符EXISTS (select ...)检测子查询是否返回任何行NOT EXISTS与EXISTS相反先执行外查询逐行取出数据传给内查询进行匹配短路求值特性一旦找到第一个匹配项就立即停止执行返回TRUE无需遍历全部数据内存效率高不需要缓存整个子查询结果集采用流式处理方式子查询中必须包含与外查询表的关联条件当查询外层数据量远小于子集时优先使用EXISTS反之使用IN连表二、Mysql2.1 Mysql基础2.1.1 数据存储结构MyISAMInnoDB2.1.2 索引索引类别索引结构索引优化索引失效2.1.3 SQL优化计划字段优化查询2.1.4 踩坑备忘2.2 查询Mysql5.7各项信息-- 所有连接进程showprocessList-- 线程连接统计showstatuslikethreads%;-- 数据库最大连接数showvariableslikemax_connections;-- 最近一次死锁日志showengineinnodbstatus;-- 开启死锁全局日志记录setglobalinnodb_status_outputon;-- 错误日志路径showvariableslikelog_error;-- 慢sql日志路径showvariableslikeslow_query_log_file;-- 通用查询日志路径对性能影响大通常不开showvariableslikegeneral_log%;2.2.1 死锁日志分析查看最近一次死锁日志事务1事务1611699052已活跃 61 秒正在获取行fetching rows在等待一个 X锁排他锁被授予这个锁是在表 tb_ems_fittings_storehouse 的 PRIMARY 索引上space id 250 page no 138 n bits 168 index PRIMARY事务2事务2611699179已活跃 35 秒正在开始索引读取starting index read持有一个在 PRIMARY 索引上的 X锁lock_mode X locks rec but not gap这个锁恰好是事务1正在等待的锁同时在等待另一个在 PRIMARY 索引上的 X锁space id 250 page no 67死锁的处理结果InnoDB 选择回滚了事务2WE ROLL BACK TRANSACTION (2)来解除死锁。被回滚的事务通常被认为是相对“代价较小”的那个例如修改的行数较少或回滚日志较少2.2.2 分析锁等待关系-- 当前锁等待关系SELECTr.trx_idASwaiting_trx_id,r.trx_mysql_thread_idASwaiting_thread,r.trx_queryASwaiting_query,b.trx_idASblocking_trx_id,b.trx_mysql_thread_idASblocking_thread,b.trx_queryASblocking_query,b.trx_startedASblocking_start_time,TIMESTAMPDIFF(SECOND,b.trx_started,NOW())ASblocking_duration_sec,pl.lock_modeASlock_mode,pl.lock_typeASlock_type,pl.lock_tableASlocked_table,pl.lock_indexASlocked_indexFROMinformation_schema.innodb_lock_waits wINNERJOINinformation_schema.innodb_trx bONb.trx_idw.blocking_trx_idINNERJOINinformation_schema.innodb_trx rONr.trx_idw.requesting_trx_idINNERJOINinformation_schema.innodb_locks plONpl.lock_idw.blocking_lock_id;-- 当前运行事务SELECT*FROMinformation_schema.INNODB_TRXORDERBYTRX_STARTEDASC;-- 当前被锁的表SHOWOPENTABLESWHEREIn_use0;waiting_thread等待锁的事务ID受害者blocking_thread持有锁的事务ID凶手waiting_query受害者正在执行的SQLblocking_query凶手正在执行的SQLblocking_duration_sec凶手已经运行了多久时间过长通常有问题locked_table被锁定的表locked_index被锁定的索引2.3 Mysql5.7常用函数相关文档w3schools 教程_Mysql参考手册日期加减DATE_ADD(inputDate, INTERVAL expr unit)inputDate:输入时间合法日期表达式expr:时间间隔正加负减unit:单位是以下值之一MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR等-- 当前时间减三十天selectdate_add(now(),interval-30day)-- 当前时间加24小时selectdate_add(now(),interval30day)字符串转化时间STR_TO_DATE(str, format)str转化字符串format指定转化时间格式符号具体符号含义如下表selectstr_to_date(2025-04-23 12:00:01,%Y-%m-%d %H:%i:%s)时间转化字符串DATE_FROMAT(data, format)date转化时间format指定转化时间格式符号具体符号含义如下表selectdate_format(now(),%Y-%m-%d %H:%i:%s)符号描述%a工作日的缩写名称周日至周六%b缩写月份名称1 月至 12 月%c数字月份名称0 到 12%D以数字形式表示的月份中的日期后跟后缀 (1st, 2nd, 3rd, …)%d以数值表示的月份中的日期01 到 31%e以数字形式表示的月份中的日期0 到 31%f微秒000000 到 999999%H小时00 到 23%h小时00 到 12%I小时00 到 12%i分钟00 到 59%j一年中的某一天001 到 366%k小时0 到 23%l小时1 到 12%M完整的月份名称1 月至 12 月%m数字形式的月份名称01 到 12%p上午或下午%r12 小时 AM 或 PM 格式的时间 (hh:mm:ss AM/PM)%S秒00 到 59%s秒00 到 59%T24 小时制时间 (hh:mm:ss)%U星期天是一周的第一天00 到 53%u星期一是一周的第一天的星期00 到 53%V星期天是一周的第一天01 到 53。与%v星期一是一周的第一天的星期01 到 53。与%W完整的工作日名称周日至周六%w星期天0星期六6%X星期天是一周的第一天的星期。与%x星期一是一周的第一天的一周的年份。与%Y4 位数字形式的年份%y2 位数字形式的年份三、SqlServer1 账户创建右键管理员运行SQL Server Management Studio进入权限界面Security - Logins右键New Login设置账号密码选择权限角色2 查看外部连接暴露端口运行SQL Server Configuration Manager选择SQL Server Network Config右键TCP/IP查看对应属性TCP Dynamic Ports四、数据库事务1 事务的ACID属性原子性(Atomicity)事务是一个不可分割的工作单位事务中的操作要么都发生要么都不发生一致性(Consistency)事务中操作的数据总是随操作从某一个稳定的状态转变为另一个稳定的状态隔离性(Isolation)每个事务之间互不干扰持久性(Durability)事务一旦提交对数据库的改变就是永久性的2 数据库中的并发问题不同事务同时访问相同数据造成并发问题 脏读 不可重复读 幻读脏读两个事务一个事务读取到了另一个事务未提交的数据不可重复读在一个事务内两次先后读的数据不一样因为别的事务在更改这个数据幻读在一个事务内读取到别的事务插入的数据3 数据库事务的隔离级别读未提交(READ UNCOMMITED)级别最低允许事务读取其他事务未提交的数据读已提交(READ COMMITED)解决脏读只允许事务读取其他事务已提交的数据不可避免(不可重复读、幻读)可重复读(REPEATABLE READ)解决不可重复读不可避免(幻读)串行化(SERIALIZABLE)解决所有并发问题直接让每个事务串行运行效率低下Oracle 支持2种事务隔离级别读已提交、串行化Mysql 支持3种事务隔离级别读已提交、可重复读、串行化那些操作会导致数据的自动提交?DDL(Data Definition Language)数据定义语言 操作一旦执行都会自动提交 set autocommit false 无效DML(Data Manager Language)数据管理语言 默认自动提交 set autocommit false 可以取消自动提交连接一关闭数据就会自动提交自动提交关闭用完之后要变回去 数据一旦发生异常记得回滚 conn.rollback();