数据库操作笔记

数据库操作笔记（MySQL）

1、 数据库添加字段SQL语句

​ MySQL

ALTERTABLEtzbw_bwryyjndzcbADDCOLUMNshrbhVARCHAR(20)DEFAULTNULLCOMMENT'审核人编号'AFTERbtgyy;

2、 修改表中的字段类型

ALTERTABLEzkrc_zwbMODIFYCOLUMNzwmcVARCHAR(64)NOTNULLCOMMENT'职位名称';ALTERTABLEzkrc_zwbMODIFYCOLUMNjljsyxVARCHAR(256)DEFAULTNULLCOMMENT'简历接收邮箱';altertablehdjy_lhjy_rdsqb changecolumnxsys xsysvarchar(2);

3、 查询某个字符之后的数据（MySQL）

-1 之后 1 之前 SELECT SUBSTRING_INDEX(zbsqb.pyyjbh, ',', -1)

4、 将查询数据的结果展示序号

SELECT(@i:=@i+1)xhFROM(SELECT@i:=0)xh

5、 数据库清库语句

-- 数据库内容清除语句。 TRUNCATE table 表名； -- 给数据库表新增注释 comment='表的注释'

6、 修改库的联动（读写分离）

-- 查询主库是否只读，0为正常 select @@read_only; set global read_only=1;

7、 根据身份证计算年龄

SELECT(substring(now(),1,4)-substring(sfz,7,4))-(substring(sfz,11,4)-date_format(now(),'%m%d')>0)FROMTable

8、 赋值全部行政区划代码

-- update rlzyfwjgdjpd_d_xzqh set general_name = null; SELECT * FROM rlzyfwjgdjpd_d_xzqh where parentid = ''; -- update rlzyfwjgdjpd_d_xzqh set general_name = dmmc where parentid = ''; SELECT CONCAT( parentname, ' ', dmmc ) FROM rlzyfwjgdjpd_d_xzqh WHERE parentid LIKE '%0000000000'; -- update rlzyfwjgdjpd_d_xzqh set general_name = CONCAT( parentname, ' ', dmmc ) where parentid like '%0000000000'; SELECT b.dmid, CONCAT(( SELECT parentname FROM rlzyfwjgdjpd_d_xzqh a WHERE a.dmid = b.parentid ) ,' ', parentname, ' ', dmmc) FROM rlzyfwjgdjpd_d_xzqh b WHERE general_name IS NULL AND dmid LIKE '______000000'; -- UPDATE rlzyfwjgdjpd_d_xzqh a , (SELECT b.dmid, CONCAT(( SELECT parentname FROM rlzyfwjgdjpd_d_xzqh a WHERE a.dmid = b.parentid ) ,' ', parentname, ' ', dmmc) mc -- FROM -- rlzyfwjgdjpd_d_xzqh b -- WHERE -- general_name IS NULL AND dmid LIKE '______000000') b set a.general_name = b.mc where a.dmid = b.dmid;

9、 MySQL启动服务

systemctl start mysqld.service

10、 MySQL删除only_full_group_by

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘dxbwry.sqb.zbsqbh’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

-- 暂时性 SHOW VARIABLES LIKE 'sql_mode'; SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; FLUSH PRIVILEGES;

-- 永久性 -- 检索MySQL which mysql -- 查询MySQL配置文件所在地 /usr/bin/mysql --verbose --help | grep -A 1 'Default options' -- 编辑MySQL配置 vim /etc/my.cnf -- 修改配置 sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER" -- 重启MySQL服务 service mysqld restart

11、 MySQL设置最大连接数

SHOW VARIABLES LIKE '%max_connections%'; SHOW VARIABLES LIKE '%max_connect_errors%'; SET GLOBAL max_connections = 1000; SET GLOBAL mysqlx_max_connections = 100; SET GLOBAL max_connect_errors = 100; FLUSH PRIVILEGES;

12、 MySQL设置不区分大小写

lower_case_table_names（1：不区分 0：区分）

SHOW VARIABLES LIKE 'lower%' -- 在MySQL服务中修改配置 -- 检索MySQL which mysql -- 查询MySQL配置文件所在地 /usr/bin/mysql --verbose --help | grep -A 1 'Default options' -- 编辑MySQL配置 vim /etc/my.cnf -- 修改配置 在my.cnf中的[mysqld]后面添加lower_case_table_names=1 -- 重启MySQL服务 service mysqld restart

数据库操作笔记（Oracle）

1、 数据库添加字段SQL语句

​

ALTERTABLEMyTableADDTest VARCHAR2(50);COMMENTONCOLUMNMyTable.TestIS'Demo';

2、 删库恢复语句

flashbacktableygzjzqyrd_qyygxxbtobeforedrop

3、 通过命令导入dmp文件

# 在cmd命令行输入 # syrlzysc（账号） # syrlzysc（密码） # 130.10.7.246（数据库链接地址） # D:\a.dmp（dmp文件存放路径） imp syrlzysc/syrlzysc@130.10.7.246:1521/orcl file=D:\a.dmp full=y ignore=y;

4、 Oracle启动服务

# 切换Oraclesu- oracle# 查看监听lsnrctl status# 启动监听lsnrctl start# 进入服务sqlplus /nolog&sqlplus"/as sysdba"# 切换用户conn /as sysdba# 启动实例startup

# 退出quit Ctrl + D# 关闭实例shutdown# 停止监听lsnrctl stop# 停止服务shutdownimmediate

其它

1、 定时备份数据库

​ 编写（mysqlback.sh）文件

#!/bin/bash db_user="root" db_password="1qaz@WSX" db_name="asahi" db_address="120.120.120.120" db_port="3306" time="$(date +"%Y%m%d_%H%M%S")" backup_dir="/home/backup/mysql" #Docker下的MySQL docker exec -it mysql mysqldump -u$db_user -p$db_password $db_name > $backup_dir/$db_name_$time.sql #mysqldump -u$db_user -p$db_password $db_name > $backup_dir/$db_name_$time.sql #mysqldump -h$db_address -P$db_port -u$db_user -p$db_password $db_name > $backup_dir/$db_name_$time.sql mysqldump -u$db_user -p$db_password $db_name | gzip > $backup_dir/$db_name_$time.sql.gz #rm -rf $backup_dir/$db_name_$time.sql #下面两条指令根据需求添加即可 #删除七天之前的备份 #find $backup_dir -name $db_name"*.sql.gz" -type f -mtime +7 -exec rm -rf {} \; > /dev/null 2>&1 #删除一分钟之前的备份 #find $backup_dir -name $db_name"*.sql.gz" -type f -mmin +1 -exec rm -rf {} \; > /dev/null 2>&1

​ 添加备份文件夹，往backup内添加执行文件（mysqlback.sh）

mkdir/home/backupmkdir/home/backup/mysql

​ 1、添加可执行权限

chmodu+x mysqlback.sh

​ 2、测试能否正常使用

./mysqlback.sh

​ 3、检测或安装 crontab，执行 crontab 命令如果报 command not found，就表明没有安装。

yum-yinstallvixie-cron yum-yinstallcrontabs

​ 4、启动服务

启动：servicecrond start 检测cron定时服务是否自启用 systemctl is-enabled crond.service enable表示已启用自启动 disable标识未启用自启动 在CentOS -6系统中加入开机自动启动chkconfig--level345crond on 在CentOS -7系统中加入开机自动启动 systemctlenablecrond.service 如果已经启用，想要cron关闭自启动 systemctl disable crond.service

​ 5、添加任务

添加计划任务crontab-e重启定时器servicecrond restart 查看定时任务crontab-l每五分钟运行此文件，对数据库进行备份 */5 * * * * /home/backup/mysqlback.sh 前面是分钟后面是小时，控制台回显“crontab：installing new crontab” 表示添加调度任务成功。