https://blog.zhoz.com/index.php zh-cn https://blog.zhoz.com/read.php?779 <> Mon, 25 Sep 2017 11:24:21 +0000 https://blog.zhoz.com/read.php?779
sql如何查找出number中第一位为1的数据：
标准写法：
select substring(cast(adv_id as varchar) from 1 for 1) as a1 from zhoz_sub_dat_6_3;
常规写法：
select substring(cast(adv_id as varchar), 1,1) as a1 from zhoz_sub_dat_6_3;

INSERT INTO zhoz_sub_dat_6_3
(media_id, adv_id, course_id, serial_code, os, carrier, media_referrer) SELECT media_id, adv_id, course_id, serial_code, os, carrier, media_referrer FROM zhoz_total_dat WHERE delete_flg=false AND substring(cast(adv_id as varchar), 1,1) = '2' AND substring(cast(media_id as varchar), 1,1) = '6';

这样执行多次就可以比较快速的Copy数据。
以上为PostgreSQL数据库，其它原理类似。
Tags - 网站运营 , 效率优化 , pgsql/postgresql ]]> https://blog.zhoz.com/read.php?776 <> Sat, 07 May 2016 09:26:04 +0000 https://blog.zhoz.com/read.php?776 我就简单查询了一下：
[root@oyasai /]# du -h --max-depth=1 |sort
6.2G    /usr
19G     /var

继续查看，定位到了mysql目录：
-rw-rw----. 1 mysql mysql 3844079616 May  7 11:00 ibdata1
-rw-rw----  1 mysql mysql  371028567 May  2 13:10 slow_query.log

原来是索引文件和（一堆）日志文件引起的。


1、彻底不需要：
关闭mysql主从，关闭binlog
/etc/mysql/my.cnf。把里面的log-bin这一行注释掉。

# binlog_format=mixed
然后重启数据库

2、重启mysql,开启mysql主从，设置expire_logs_days
> show binary logs;
> show variables like '%log%';
> set global expire_logs_days = 10;

3、手动清除binlog文件
# /usr/local/mysql/bin/mysql -u root -p
> PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY);  
删除7天前的MySQL binlog日志
Tags - sql , mysql ]]> https://blog.zhoz.com/read.php?772 <> Thu, 19 Mar 2015 04:37:40 +0000 https://blog.zhoz.com/read.php?772 ①查询Pg的状态方法：
# ps axwf | grep 'postgres'
20152 ?        S     34:54 /usr/local/pgsql/bin/postgres
20154 ?        Ss    54:20  \_ postgres: writer process
20155 ?        Ss    41:50  \_ postgres: wal writer process
20156 ?        Ss   192:31  \_ postgres: autovacuum launcher process
20157 ?        Ss   465:52  \_ postgres: stats collector process
8493 ?        Ss     0:00  \_ postgres: postgres WildCard ::1(42985) SELECT
8495 pts/3    S+     0:00          |   \_ grep postgres

②table lock waiting, select waiting产生测试方法，postgres select 执行延迟：
select pg_sleep(50),(select id from zhoz_table) as zhoz_id;

③解决方法：
lock部分mode变换
LOCK TABLE %s IN ACCESS EXCLUSIVE MODE
↓
LOCK TABLE %s IN ROW EXCLUSIVE MODE


Tags - sql , 数据库连接 , pgsql/postgresql ]]> https://blog.zhoz.com/read.php?765 <> Thu, 20 Feb 2014 10:05:49 +0000 https://blog.zhoz.com/read.php?765
根据此条件取出所有年龄超过18，并且如果性别是FEMALE>28的数据。

    $criteria_sql = "";
    if ($criteria_flg) {
      $user_sql = "";
      // OVER_18, FEMALE_28
      $param_list = explode(",", CRITERIA_TOTAL_TYPE_ARRAY);
      $over_age = explode("_", $param_list[0]);
      if (isset($over_age[1])) {
        $user_sql .= " and date_trunc('day', birthday) < '" . date("Y-m-d", strtotime("-" . $over_age[1] . " year")) . "'";
      }
      if (isset($param_list[1])) {
        $sex_age = explode("_", $param_list[1]);
        $sex = trim($sex_age[0]);
        $user_sql .= " and (sex !='" . $sex . "' or (sex='" . $sex . "'  and date_trunc('day', birthday) < '" . date("Y-m-d", strtotime("-" . $sex_age[1] . " year")) . "'))";
      }
      if (!empty($user_sql)) {
        $criteria_sql = " inner join (select * from user_mst where delete_flg=false " . $user_sql . ") as u on (u.id=pad.user_id)";
      }
    }


这么漂亮的想法，后来客户更改需求了，但我要记录一下。 ]]> https://blog.zhoz.com/read.php?747 <> Fri, 17 May 2013 08:58:16 +0000 https://blog.zhoz.com/read.php?747 方法有两种：

update adv_dat set adv_type=adv_mst.adv_type, adv_target=adv_mst.adv_target
from adv_mst where adv_media_total_day_dat.adv_id=adv_mst.id;

另外一种就是用 left join来实现
不过，开始测试的时候，好像不大对，就没去研究了。
Tags - 调研报告 , sql ]]> https://blog.zhoz.com/read.php?737 <> Wed, 06 Feb 2013 09:37:13 +0000 https://blog.zhoz.com/read.php?737 其实，应该是这样
TIMESTAMP  not null default now()
Date或DateTime类型是不能使用函数作为默认值的
Tags - mysql , sql ]]> https://blog.zhoz.com/read.php?690 <> Fri, 07 May 2010 04:32:12 +0000 https://blog.zhoz.com/read.php?690
[oracle@sv2 ~]$ rm -rf /opt/oracle/oradata/*
[oracle@sv2 ~]$ rm -rf /opt/oracle/product/10.2.0/db_1/*
这里需要自定义安装，但当走到
Select the database creation options这里的
Adding Oracle JVM
一动不动，等了半小时还是34%，试了2次还不行，最终原来是需要修改配置文件，如下：
[root@sv2 zhouz]# vi /etc/security/limits.conf
增加以下几行：
default:
        fsize = -1
        data = -1
        stack = -1


试试看，奇迹般的过去了。
Tags - oracle , 主机配置 ]]> https://blog.zhoz.com/read.php?689 <> Thu, 06 May 2010 08:52:36 +0000 https://blog.zhoz.com/read.php?689 select userenv('language') from dual;

USERENV('LANGUAGE')
-----------------------------------------------------------
SIMPLIFIED CHINESE_CHINA.WE8ISO8859P1
从NLS_LANG的组成我们可以看出，真正影响数据库字符集的其实是第三部分。

所以两个数据库之间的字符集只要第三部分一样就可以相互导入导出数据，前面影响的只是提示信息是中文还是英文。


1、查看字符集

       数据库服务器字符集 select * from nls_database_parameters，其来源于props$，是表示数据库的字符集。

    
　　
　　客户端字符集环境 select * from nls_instance_parameters,其来源于v$parameter，表示客户端的字符集的设置，可能是参数文件，环境变量或者是注册表
　　
　　会话字符集环境 select * from nls_session_parameters，其来源于v$nls_parameters，表示会话自己的设置，可能是会话的环境变量或者是alter session完成，如果会话没有特殊的设置，将与nls_instance_parameters一致。
　　
　　客户端的字符集要求与服务器一致，才能正确显示数据库的非Ascii字符。如果多个设置存在的时候，alter session>环境变量>注册表>参数文件
　　
　　字符集要求一致，但是语言设置却可以不同，语言设置建议用英文。如字符集是zhs16gbk，则nls_lang可以是American_America.zhs16gbk。

2、修改数据库的字从字符集

SQL> ALTER DATABASE NATIONAL CHARACTER SET JAPANSE_JAPAN JA16SJIS;
ALTER DATABASE NATIONAL CHARACTER SET JAPANSE_JAPAN JA16SJIS
                                                    *
第 1 行出现错误:
ORA-00933: SQL command not properly ended

ALTER DATABASE NATIONAL CHARACTER SET 'JAPANSE_JAPAN JA16SJIS';

SQL> ALTER DATABASE NATIONAL CHARACTER SET 'JAPANSE_JAPAN JA16SJIS';
ALTER DATABASE NATIONAL CHARACTER SET 'JAPANSE_JAPAN JA16SJIS'
                                      *
第 1 行出现错误:
ORA-24329: invalid character set identifier

SQL> select * from V$NLS_VALID_VALUES where parameter ='CHARACTERSET';

PARAMETER                                                        VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
CHARACTERSET                                                     AR8MUSSAD768
CHARACTERSET                                                     AR8ADOS710
CHARACTERSET                                                     AR8ADOS720
CHARACTERSET                                                     AR8APTEC715
CHARACTERSET                                                     AR8MSWIN1256
CHARACTERSET                                                     AR8MSAWIN
CHARACTERSET                                                     AR8NAFITHA721
CHARACTERSET                                                     AR8SAKHR706
CHARACTERSET                                                     AR8ARABICMAC
CHARACTERSET                                                     AR8ARABICMACS
CHARACTERSET                                                     AR8ARABICMACT
CHARACTERSET                                                     LA8ISO6937
CHARACTERSET                                                     WE8DECTST
CHARACTERSET                                                     JA16VMS
CHARACTERSET                                                     JA16EUC
CHARACTERSET                                                     JA16EUCYEN
CHARACTERSET                                                     JA16SJIS
CHARACTERSET                                                     JA16DBCS
CHARACTERSET                                                     JA16SJISYEN
CHARACTERSET                                                     JA16EBCDIC930
CHARACTERSET                                                     JA16MACSJIS

ALTER DATABASE NATIONAL CHARACTER SET 'JA16SJIS';

SQL文修改编码方法：
shutdown immediate;
startup mount;
alter system  enable restricted session;
alter system set JOB_QUEUE_PROCESSES=0;
alter system set AQ_TM_PROCESSES=0;
alter database open;
alter database character set internal_use GB2312;
shutdown immediate;
startup;

我没有测试成功，唉！重安装库吧……
Tags - oracle , 调研报告 , sql ]]> https://blog.zhoz.com/read.php?688 <> Wed, 05 May 2010 16:38:12 +0000 https://blog.zhoz.com/read.php?688 Password:
[oracle@sv2 tkdn]$ cd
[oracle@sv2 ~]$ source  .bash_profile
[oracle@sv2 ~]$ sqlplus tkdn/tkdn as sysdba

SQL> select table_name from user_tables;
select table_name from user_tables
                       *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only

困了，想睡觉了……
Tags - 调研报告 , oracle ]]> https://blog.zhoz.com/read.php?687 <> Fri, 30 Apr 2010 10:51:54 +0000 https://blog.zhoz.com/read.php?687 select table_name from user_tables;  //当前用户的表  

select table_name from dba_tables;  //包括系统表

select table_name from all_tables;  //所有用户的表  

在Oracle中查看所有的表:
select * from tab/dba_tables/dba_objects/cat;

查看表结构
desc   tablename;

关于表的删除与恢复sql

1.删除表中数据两种方法
      a. delete * from My_Table;
         b. truncate table My_Table;

2.删除整个表
         drop table My_Table;


如何恢复不小心 Drop 掉的表呢,其实 Oracle 中也有类似的 "回收站"

比如不小心删除了表 My_Table
         drop table My_Table;
这个时候再用SELECT语句查询此表时，将会提示表或视图不存在.

但可以用如下语句查询到这个表还在Oracle回收站中：
SELECT * FROM user_recyclebin WHERE original_name='My_Table';

那么现在就可以用如下语句进行恢复：
FLASHBACK TABLE My_Table TO BEFORE DROP;


明天就5.1劳动节了，打算一家北上劳动一下。
Tags - oracle ]]>