PHP PDO用法

PDO含义:PHP data objects。

建立连接和基本Query

<?php
try {
    $dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
    foreach($dbh->query('SELECT * from FOO') as $row) {
        print_r($row);
    }
    $dbh = null;
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}

使用事务

try {  
  $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $dbh->beginTransaction();
  $dbh->exec("insert into staff (id, first, last) values (23, 'Joe', 'Bloggs')");
  $dbh->exec("insert into salarychange (id, amount, changedate) 
      values (23, 50000, NOW())");
  $dbh->commit();

} catch (Exception $e) {
  $dbh->rollBack();
  echo "Failed: " . $e->getMessage();
}

预处理语句insert

$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)");
$stmt->bindParam(1, $name);
$stmt->bindParam(2, $value);

// insert one row
$name = 'one';
$value = 1;
$stmt->execute();

// insert another row with different values
$name = 'two';
$value = 2;
$stmt->execute();

预处理语句query

<?php
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name = ?");
if ($stmt->execute(array($_GET['name']))) {
  while ($row = $stmt->fetch()) {
    print_r($row);
  }
}

mysql having和where的区别

where不能筛选通过函数计算出来的列。

统计消费过100元以上的用户:

错误1

select userid, ordernum, sum(price) from orders group by (userid) where sum(price) > 100;

错误2(设置别名也不行)

select userid, ordernum, sum(price) as spent from orders group by (userid) where spent > 100;

正确:

select userid, ordernum, sum(price) from orders group by (userid) having sum(price) > 100;

MySQL连接查询on和where的区别和顺序

原文连接:https://www.cnblogs.com/jessy/p/3525419.html

left join: 左连接,返回左表中所有的记录以及右表中连接字段相等的记录。
right join: 右连接,返回右表中所有的记录以及左表中连接字段相等的记录。
inner join: 内连接,又叫等值连接,只返回两个表中连接字段相等的行。
full join: 外连接,返回两个表中的行:left join + right join。
cross join: 结果是笛卡尔积,就是第一个表的行数乘以第二个表的行数。

关键字: on

数据库在通过连接两张或多张表来返回记录时,都会生成一张中间的临时表,然后再将这张临时表返回给用户。

在使用left jion时,on和where条件的区别如下:

1、on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。

2、where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。

假设有两张表:

表1:tab1

id size
1 10
2 20
3 30

表2:tab2

size name
10 AAA
20 BBB
20 CCC

两条SQL:

1、select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name='AAA'
2、select * form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name='AAA')
第一条SQL的过程:

1、中间表
on条件:
tab1.size = tab2.size
tab1.id tab1.size tab2.size tab2.name
1 10 10 AAA
2 20 20 BBB
2 20 20 CCC
3 30 (null) (null)
2、再对中间表过滤
where 条件:
tab2.name=’AAA’
tab1.id tab1.size tab2.size tab2.name
1 10 10 AAA
第二条SQL的过程:

1、中间表
on条件:
tab1.size = tab2.size and tab2.name=’AAA’
(条件不为真也会返回左表中的记录)
tab1.id tab1.size tab2.size tab2.name
1 10 10 AAA
2 20 (null) (null)
3 30 (null) (null)

其实以上结果的关键原因就是left join,right join,full join的特殊性,不管on上的条件是否为真都会返回left或right表中的记录,full则具有left和right的特性的并集。 而inner jion没这个特殊性,则条件放在on中和where中,返回的结果集是相同的。

docker-compose搭建apache结合多个php版本同时运行的环境

apache结合php有多种方式,如果需要让apache能同时使用多个php版本,经过自己的了解,应该只能使用apache with php-fpm模式运行。

首先创建两个php的service(service中与本文无关的参数已经去除,方便理解):

// docker-compose.yml
version: '3'

services:
    php56:
      container_name: php56
      build:
        context: php56
      expose:
        - "9000"
    php72:
      container_name: php72
      build:
        context: php72
      expose:
        - "9000"
   apache2:
      container_name: apache2
      build:
        context: ./apache2
      ports:
        - "80:80"
        - "443:443"
      depends_on:
        - php56
        - php72
      networks:
        default:
          aliases:
            - "a.com"
            - "b.com"

然后创建对应php容器的文件夹以及Dokcerfile,每个php一个容器,例如:

// php72/Dockerfile
FROM jakesoft/php:7.2-centos

USER root

EXPOSE 9000

CMD ["php-fpm", "-F"]

php56/Dokcerfile与上面的文件类似

但是需要注意一点的是php-fpm如果想监听非本机环境的请求时,需要修改容器中的/etc/php-fpm.d/www.conf

listen = 0.0.0.0:9000
;listen.allowed_clients = 127.0.0.1 // 这一行前面增加";"注释掉

(所有的php容器都需要做此修改)

现在去修改apache的站点配置:

a.com 使用php5.6

<VirtualHost *:80>
    DocumentRoot "/var/www/a"
    ServerName   a.com

    <FilesMatch .*$>
        SetHandler "proxy:fcgi://php56:9000"
    </FilesMatch>
</VirtualHost>

b.com 使用php7.2

<VirtualHost *:80>
    DocumentRoot "/var/www/b"
    ServerName   b.com

    <FilesMatch .*$>
        SetHandler "proxy:fcgi://php72:9000"
    </FilesMatch>
</VirtualHost>

启动:

docker-compose up --build apache2

大功告成!

Docker常用命令

docker start -ai f9e4244cc6de # 启动一个容器
docker rmi $(docker images -f "dangling=true" -q)  # 删除无用镜像
docker commit 9cfadd971dcb jakesoft/php:7.2-centos # 把容器打包成镜像
docker push jakesoft/php:7.2-centos # 发布镜像到docker hub

docker-compose

docker-compose logs -f [service...] # 查看日志
docker-compose up -d [service...]# 在后台启动一个或者多个服务
docker-compose exec workspace /bin/bash # 进入到一个容器中
docker-compose restart apache2 # 重启apache2

Elasticsearch聚合查询示例2

按不同条件统计数量Request:

curl -X POST \
  http://elasticsearch:9200/devslicejobs/task/_search \
  -H 'Accept: */*' \
  -H 'Accept-Encoding: gzip, deflate' \
  -H 'Authorization: Basic ZWxhc3RpYzpzbGljZWpvYnNfMTIz' \
  -H 'Cache-Control: no-cache' \
  -H 'Connection: keep-alive' \
  -H 'Content-Length: 565' \
  -H 'Content-Type: application/json' \
  -H 'Host: elasticsearch:9200' \
  -H 'cache-control: no-cache' \
  -d '{
    "size": 0,
    "query": {
        "bool": {
            "must": [
                {
                    "term": {
                        "batchid": 4813
                    }
                }
               ]

        }
    },
    "aggs": {
        "tasks": {
            "filters": {
                "filters": {
                    "pending": {"bool": {"must": [{"term": {"status": 1}}]}},
                    "assign": {"bool": {"must": [{"term": {"status": 2}}]}},
                    "total": {"bool": {}}
                }
            }
        }
    }
}'

Response:

{
    "took": 29,
    "timed_out": false,
    "_shards": {
        "total": 5,
        "successful": 5,
        "failed": 0
    },
    "hits": {
        "total": 101,
        "max_score": 0.0,
        "hits": []
    },
    "aggregations": {
        "tasks": {
            "buckets": {
                "assign": {
                    "doc_count": 12
                },
                "pending": {
                    "doc_count": 1
                },
                "total": {
                    "doc_count": 101
                }
            }
        }
    }
}

相关文档:https://www.elastic.co/guide/en/elasticsearch/reference/5.1/search-aggregations-bucket-filters-aggregation.html

Elasticsearch聚合查询示例1

Request:

curl -X POST \
  http://elasticsearch:9200/devorders/order/_search \
  -H 'Accept: */*' \
  -H 'Accept-Encoding: gzip, deflate' \
  -H 'Authorization: Basic ZWxhc3RpYzpzbGljZWpvYnNfMTIz' \
  -H 'Cache-Control: no-cache' \
  -H 'Connection: keep-alive' \
  -H 'Content-Length: 1333' \
  -H 'Content-Type: application/json' \
  -H 'Host: elasticsearch:9200' \
  -H 'cache-control: no-cache' \
  -d '{
    "size": 0,
    "sort": [
        {
            "orderid": {
                "order": "desc"
            }
        }
    ],
    "query": {
        "bool": {
            "must_not": [],
            "must": [
                {
                    "terms": {
                        "provinceid": [
                            "370"
                        ]
                    }
                },
                {
                    "term": {
                        "batchid": 4813
                    }
                },
                {
                    "terms": {
                        "status": [
                            1,
                            2,
                            3,
                            4,
                            40,
                            5
                        ]
                    }
                },
                {
                    "range": {
                        "origin_salary": {
                            "gte": 0
                        }
                    }
                }
            ]
        }
    },
    "aggs": {
        "sum_by_salary": {
            "sum": {
                "field": "salary"
            }
        },
        "sum_by_origin": {
            "sum": {
                "field": "origin_salary"
            }
        }
    }
}'

Response:

{
    "took": 49,
    "timed_out": false,
    "_shards": {
        "total": 5,
        "successful": 5,
        "failed": 0
    },
    "hits": {
        "total": 10,
        "max_score": 0.0,
        "hits": []
    },
    "aggregations": {
        "sum_by_origin": {
            "value": 0.0
        },
        "sum_by_salary": {
            "value": 115.0
        }
    }
}

另附上一个script示例的Request:

{
    "size": 0,
    "aggs": {
        "sum_by_salary": {
            "sum": {
                "script": {
                    "lang": "painless",
                    "inline": "doc['hirenum'].value * doc['salary'].value"
                }
            }
        }
    }
}