Category: sqlite / MySql / psql

sql commands

drop table if exists users;
create table if not exists users (
  id integer primary key,
  name varchar(255),
  score integer,
  email text unique
);

insert into users (email) values ('yamada@gmail.com');
insert into users (email) values ('yamada@gmail.com');

select * from users;

データの制約

drop table if exists users;
create table if not exists users (
  id integer primary key,
  name varchar(255) not null,
  score integer default 10 check(score >= 0),
  email text unique
);

insert into users (name, score) values ('yamada', -30);

select * from users;

select文

.headers on 
select id, name as user_name from users;
.mode line
select * from users;
select * from users where score > 60;
select * from users where score is null;
select * from users where name = 'ito';
select * from users where name like '%a%';
select * from users where name like '%@%' escape '@';
select * from users where name glob '*i*';
select * from users where name glob '[ts]*';

mode .html, mode.columなどもあります。

order by, limit, offset, view

select * from users where score is not null order by score;
select * from users where score is not null order by score desc;
select * from users where score is not null order by score desc limit 3;
select * from users where score is not null order by score desc limit 3 offset 2;
create view hiscore as select * from users order by score desc limit 5;
select * from hiscore;
drop view if exists hiscore;

演算

.headers on
.mode column
select id, name, score + 10 from users;
select count(id), max(score), min(score), avg(score) from users;
select name, length(name), upper(name), substr(name, 2, 3) from users;
select last_insert_rowid();
select random();
select * from users order by random() limit 1;

insert into users (name, score, team) values ('yamada', 30, 'tema-A');
insert into users (name, score, team) values ('yoshimoto', 55, 'tema-B');
insert into users (name, score, team) values ('ito', 22, 'tema-D');
insert into users (name, score, team) values ('sato', 18, 'tema-D');
insert into users (name, score, team) values ('kimura', 66, 'tema-A');
insert into users (name, score, team) values ('hayashi', 88, 'tema-B');
insert into users (name, score, team) values ('seto', 22, 'tema-C');
insert into users (name, score, team) values ('mimura', null, 'tema-C');

.headers on
.mode column
select team, avg(score) from users group by team;
select team, avg(score) from users group by team having avg(score) > 40;
select distinct team from users;

.headers on
.mode column
select
  id, name, score,
  case
   when score > 70 then 'A'
   when score > 50 then 'B'
   else 'C'
  end as rank
from users;

sqlite3

[vagrant@localhost sqlite]$ sqlite3 myapp.db
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .hlp

テーブルの作成

sqlite> create table posts (title, body);
sqlite> .exit
[vagrant@localhost sqlite]$ ls
myapp.db

ファイルの削除

[vagrant@localhost sqlite]$ rm myapp.db

外部ファイル読み込み

sqlite> .read commands.sql
[vagrant@localhost sqlite]$ sqlite3 myapp.db < commands.sql

テーブルの作成

create table if not exists posts (
  id integer primary key,
  title varchar(255),
  body varchar(255)
);

-- lists
.tables

-- table structure
.schema

データ型:integer, tex, real, blob, null

テーブル削除

drop table if exists posts;

テーブルの変更、カラムの追加

--list talbes
alter table posts rename to articles;

--add column
alter table articles add column email text;

レコードの挿入

insert into posts (title, body) values ('title1', 'body1');
insert into posts (id, title, body) values (null, 'title2', 'body2');
insert into posts (title, body) values ('title3', 'body3');
insert into posts (title, body) values ('title4', 'it''s body4');

select * from posts;

投票システム　(1日1回)

jqueryのclick functionでopacityを変更し、更にformのvalueにidを入れて、ボタンが押されたら、MySQLにIDを格納します。そして、Mysqlからsql文でデータを読み込んで、集計結果を表示させます。また、IPアドレス、ユーザーエージェント、データで、複数投票を制限しています。

-reference
bindValue:Binds a value to a corresponding named or question mark placeholder in the SQL statement that was used to prepare the statement.
SQL INSERT INTO Statement:INSERT INTO table_name VALUES (value1,value2,value3,…);
SQLデータ取得：SELECT col_name1, col_name2, … FROM db_name.tbl_name;
SQL GROUP BY Statement:The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.
user agent:is software (a software agent) that is acting on behalf of a user.

index.php

<?php

require_once(__DIR__ . '/config.php');
require_once(__DIR__ . '/Poll.php');

try {
  $poll = new \MyApp\Poll();
} catch (Exception $e){
  echo $e->getMessage();
  exit;
}

if ($_SERVER['REQUEST_METHOD'] === 'POST'){
  $poll->post();
}

$err = $poll->getError();

?>
<!DOCTYPE html>
<html lang="en">
<head>
	<meta charset="UTF-8">
	<title>Poll</title>
	<meta name="viewport" content="width=device-width, initial-scale=1">
	<link rel="stylesheet" href="styles.css">
</head>
<body>
  <?php if (isset($err)) : ?>
  <div class="error"><?= h($err); ?></div>
<?php endif; ?>
  <h1>Which do you like best?</h1>
  <form action="" method="post">
    <div class="row">
      <div class="box" id="box_0" data-id="0"></div>
      <div class="box" id="box_1" data-id="1"></div>
      <div class="box" id="box_2" data-id="2"></div>
      <input type="hidden" id="answer" name="answer" value="">
      <input type="hidden" name="token" value="<?= h($_SESSION&#91;'token'&#93;); ?>">
    </div>
    <div id="btn">Vote and See Results</div>
  </form>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
	<script>
  $(function(){
    'use strict';

    $('.box').on('click', function(){
      $('.box').removeClass('selected');
      $(this).addClass('selected');
      $('#answer').val($(this).data('id'));
    });

    $('#btn').on('click', function(){
      if ($('#answer').val() === ''){
        alert('Choose one!');
      } else {
        $('form').submit();
      }
    });
    $('.error').fadeOut(3000);
  });
  </script>
</body>
</html>

Poll.php

<?php

namespace MyApp;

class Poll{
  private $_db;

  public function __construct(){
    $this->_connectDB();
    $this->_createToken();
  }

  private function _createToken(){
    if(!isset($_SESSION['token'])){
      $_SESSION['token'] = bin2hex(openssl_random_pseudo_bytes(16));
    }
  }

  private function _validateToken(){
    if(
      !isset($_SESSION['token']) ||
      !isset($_POST['token']) ||
      $_SESSION['token'] !== $_POST['token']
    ){
      throw new \Exception('invalid token!');
    }
  }

  public function post(){
    try {
      $this->_validateToken();
      $this->_validateAnswer();
      $this->_save();
      //redirect to result.php
      header('Location: http://' . $_SERVER['HTTP_HOST'] . '/result.php');
    } catch(\Exception $e){
      // set error
      $_SESSION['err'] = $e->getMessage();
      // ridirect to index.php
      header('Location: http://' . $_SERVER['HTTP_HOST']);
    }
    exit;
  }

  public function getResults(){
    $data = array_fill(0, 3, 0);

    $sql = "select answer, count(id) as c from answers group by answer";
    foreach ($this->_db->query($sql) as $row){
      $data[$row['answer']] = (int)$row['c'];
    }
    return $data;
  }

  public function getError(){
    $err = null;
    if (isset($_SESSION['err'])){
      $err = $_SESSION['err'];
      unset($_SESSION['err']);
    }
    return $err;
  }

  private function _validateAnswer(){
    // var_dump($_POST);
    // exit;
    if (
      !isset($_POST['answer']) ||
      !in_array($_POST['answer'], [0, 1, 2])
    ){
      throw new \Exception('invalid answer!');
    }
  }

  private function _save(){
    $sql = "insert into answers
          (answer, created, remote_addr, user_agent, answer_date)
          values (:answer, now(), :remote_addr, :user_agent, now())";
    $stmt = $this->_db->prepare($sql);
    $stmt->bindValue(':answer', (int)$_POST['answer'], \PDO::PARAM_INT);
    $stmt->bindValue(':remote_addr', $_SERVER['REMOTE_ADDR'], \PDO::PARAM_STR);
    $stmt->bindValue(':user_agent', $_SERVER['HTTP_USER_AGENT'], \PDO::PARAM_STR);

    try {
      $stmt->execute();
    } catch(\PDOException $e){
      throw new \Exception('No more vote for today!');
    }
    // exit;
  }

  private function _connectDB(){
    try {
      $this->_db = new \PDO(DSN, DB_USERNAME, DB_PASSWORD);
      $this->_db->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
    } catch(\PDOException $e){
      throw new \Exception('Failed to connect DB');
    }
  }

}

result.php

<?php

require_once(__DIR__ . '/config.php');
require_once(__DIR__ . '/Poll.php');

try {
  $poll = new \MyApp\Poll();
} catch (Exception $e){
  echo $e->getMessage();
  exit;
}

$results = $poll->getResults();
// var_dump($results);
// exit;
// $results = [
//   0 => 12,
//   1 => 32,
//   2 => 44
// ];

?>
<!DOCTYPE html>
<html lang="en">
<head>
	<meta charset="UTF-8">
	<title>Poll Result</title>
	<meta name="viewport" content="width=device-width, initial-scale=1">
	<link rel="stylesheet" href="styles.css">
</head>
<body>
  <h1>Result ...</h1>
    <div class="row">
      <?php for($i = 0; $i < 3; $i++) : ?>
      <div class="box" id="box_<?= h($i); ?>"><?= h($results&#91;$i&#93;); ?></div>
    <?php endfor; ?>
    </div>
    <a href="/"><div id="btn">Go Back</div></a>
</body>
</html>

styles.css

body {
  font-size: 16px;
  font-family: Arial, sans-serif;
  text-align: center;
  margin: 0;
  padding: 0;
}

h1 {
  font-size: 22px;
  margin: 30px 0;
}

.row {
  margin-bottom: 15px;
}

.box {
  width: 150px;
  height: 150px;
  cursor: pointer;
  display: inline-block;
  opacity: 0.5;
  color: #fff;
  font-size: 48px;
  font-weight: bold;
  line-height: 150px;
}
.box + .box {
  margin-left: 10px;
}

#box_0 { background: url('photo_0.jpg'); }
#box_1 { background: url('photo_1.jpg'); }
#box_2 { background: url('photo_2.jpg'); }

.selected {
  opacity: 1.0;
}

#btn {
  display: inline-block;
  width: 150px;
  padding: 7px;
  font-size: 14px;
  cursor: pointer;
  border-radius: 5px;
  background: #00aaff;
  box-shadow: 0 4px 0 #0088cc;
  color: #fff;
}

#btn:hover{
  opacity: 0.8;
}

.error{
  background: orange;
  padding: 7px;
  color: #fff;
}

mySQL

create database poll_php;
grant all on poll_php.* to dbuser@localhost identified by 'xxxx';
use poll_php
create table answers (
	id int not null auto_increment primary key,
	answer int not null,
	created datetime,
	remote_addr varchar(15),
	user_agent varchar(255),
	answer_date date,
	unique unique_answer(remote_addr, user_agent, answer_date)
);

functions, config

%e7%84%a1%e9%a1%8c

<?php

function connectDb(){
  try {
    return new PDO(DSN, DB_USER, DB_PASSWORD);
  } catch (PDOException $e){
    echo $e->getMessage();
    exit;
  }
}

function h($s){
  return htmlspecialchars($s, ENT_QUOTES, "UTF-8");
}

function setToken(){
  if (!isset($_SESSION['token'])){
    $_SESSION['token'] = sha1(uniqid(mt_rand(), true));
  }
}

function checkToken(){
  if (empty($_POST['token']) || $_POST['token'] != $_SESSION['token']){
    echo "不正な処理です！";
    exit;
  }
}

/*

create database contact_php;
grant all on contact_php.* to dbuser@localhost identified by 'xxxx';

use contact_php

create table entries (
  id int not null auto_increment primary key,
  name varchar(255),
  email varchar(255),
  memo text,
  created datetime,
  modified datetime
);

alter table entries add status enum('active', 'deleted') default 'active' after memo;
*/

define('DSN','mysql:host=localhost;dbname=contact_php');
define('DB_USER','dbuser');
define('DB_PASSWORD','xxxx');

define('SITE_URL','http://192.168.33.10:8000');
define('ADMIN_URL', SITE_URL.'/admin/');

error_reporting(E_ALL & ~E_NOTICE);
session_set_cookie_params(0, '');

edit, delete

<?php

require_once('../config.php');
require_once('../functions.php');

session_start();
$dbh = connectDb();

if (preg_match('/^&#91;1-9&#93;&#91;0-9&#93;*$/', $_GET&#91;'id'&#93;)){
    $id = (int)$_GET&#91;'id'&#93;;
} else{
    echo "不正なIDです!";
    exit;
}

if ($_SERVER&#91;'REQUEST_METHOD'&#93; != "POST"){
  setToken();

  $stmt = $dbh->prepare("select * from entries where id = :id limit 1");
  $stmt->execute(array(":id" => $id));
  $entry = $stmt->fetch() or die("no one found!");
  $name = $entry['name'];
  $email = $entry['email'];
  $memo = $entry['memo'];

} else {
  checkToken();

  $name = $_POST['name'];
  $email = $_POST['email'];
  $memo = $_POST['memo'];

  $error = array();

  if(!filter_var($email, FILTER_VALIDATE_EMAIL)){
    $error['email'] = 'メールアドレスの形式が正しくありません';
  }
  if($email == ''){
    $error['email'] = 'メールアドレスを入力してください';
  }
  if($memo == ''){
    $error['memo'] = '内容を入力してください';
  }
  if(empty($error)){

    $sql = "update entries set
            name = :name,
            email = :email,
            memo = :memo,
            modified = now()
            where id = :id";
     $stmt = $dbh->prepare($sql);
     $params = array(
       ":name" => $name,
       ":email" => $email,
       ":memo" => $memo,
       ":id" => $id
     );
     $stmt->execute($params);

    header('Location: '.ADMIN_URL);
    exit;
  }
}

?>
<!DOCTYPE html>
<html lang="ja">
<head>
  <meta charset="utf-8">
  <title>データの編集</title>
</head>
<body>
  <h1>データの編集</h1>
  <form method="POST" action="">
    <p>お名前:<input type="text" name="name" value="<?php echo h($name); ?>"></p>
    <p>メールアドレス*:<input type="text" name="email" value="<?php echo h($email); ?>">
    <?php if($error&#91;'email'&#93;){ echo h($error&#91;'email'&#93;); } ?></p>
    <p>内容*:</p>
    <p><textarea name="memo" cols="40" rows="5"><?php echo h($memo); ?></textarea>
    <?php if($error&#91;'memo'&#93;){ echo h($error&#91;'memo'&#93;); } ?></p>
    <p><input type="submit" value="更新"></p>
    <input type="hidden" name="token" value="<?php echo h($_SESSION&#91;'token'&#93;); ?>">
  </form>
  <p><a href="<?php echo ADMIN_URL; ?>">戻る</a>
</body>
</html>

delete

<?php

require_once('../config.php');
require_once('../functions.php');

$dbh = connectDb();

$id = (int)$_POST&#91;'id'&#93;;

$dbh->query("update entries set status = 'deleted' where id = $id");

echo $id;

thanks

<!DOCTYPE html>
<html lang="ja">
<head>
  <meta charset="utf-8">
  <title>ありがとうございました！</title>
</head>
<body>
  <h1>ありがとうございました！</h1>
  <p></p>
  <p><a href="index.php">お問い合わせフォームに戻る</a></p>
</body>
</html>

問い合わせ管理システム

index.php

<?php

require_once('config.php');
require_once('functions.php');

session_start();
if ($_SERVER&#91;'REQUEST_METHOD'&#93; != "POST"){
  setToken();
} else {
  checkToken();

  $name = $_POST&#91;'name'&#93;;
  $email = $_POST&#91;'email'&#93;;
  $memo = $_POST&#91;'memo'&#93;;

  $error = array();

  if(!filter_var($email, FILTER_VALIDATE_EMAIL)){
    $error&#91;'email'&#93; = 'メールアドレスの形式が正しくありません';
  }
  if($email == ''){
    $error&#91;'email'&#93; = 'メールアドレスを入力してください';
  }
  if($memo == ''){
    $error&#91;'memo'&#93; = '内容を入力してください';
  }
  if(empty($error)){
    $dbh = connectDb();

    $sql = "insert into entries
            (name, email, memo, created, modified)
            values
            (:name, :email, :memo, now(), now())";
    $stmt = $dbh->prepare($sql);
    $params = array(
      ":name" => $name,
      ":email" => $email,
      ":memo" => $memo
    );
    $stmt->execute($params);

    header('Location: '.SITE_URL.'/thanks.html');
    exit;
  }
}

?>
<!DOCTYPE html>
<html lang="ja">
<head>
  <meta charset="utf-8">
  <title></title>
</head>
<body>
  <h1>お問い合わせフォーム</h1>
  <form method="POST" action="">
    <p>お名前:<input type="text" name="name" value="<?php echo h($name); ?>"></p>
    <p>メールアドレス*:<input type="text" name="email" value="<?php echo h($email); ?>">
    <?php if($error&#91;'email'&#93;){ echo h($error&#91;'email'&#93;); } ?></p>
    <p>内容*:</p>
    <p><textarea name="memo" cols="40" rows="5"><?php echo h($memo); ?></textarea>
    <?php if($error&#91;'memo'&#93;){ echo h($error&#91;'memo'&#93;); } ?></p>
    <p><input type="submit" value="送信"></p>
    <input type="hidden" name="token" value="<?php echo h($_SESSION&#91;'token'&#93;); ?>">
  </form>
  <p><a href="<?php echo ADMIN_URL; ?>">管理者ページ</a>
</body>
</html>

contact

<?php

require_once('../config.php');
require_once('../functions.php');

$dbh = connectDb();

$entries = array();

$sql = "select * from entries where status = 'active' order by created desc";

foreach($dbh->query($sql) as $row){
  array_push($entries, $row);
}

// var_dump($entries);
// exits;

?>
<!DOCTYPE html>
<html lang="ja">
<head>
  <meta charset="utf-8">
  <title>お問い合わせ一覧</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
</head>
<body>
  <h1>一覧データ</h1>
  <p><span id="num"><?php echo count($entries); ?></span>件あります。</p>
  <ul>
    <?php foreach($entries as $entry): ?>
      <li id="entry_<?php echo h($entry&#91;'id'&#93;); ?>"><?php echo h($entry&#91;'email'&#93;); ?>
        <a href="edit.php?id=<?php echo h($entry&#91;'id'&#93;); ?>">[編集]</a>
        <span class="deleteLink" data-id="<?php echo h($entry&#91;'id'&#93;); ?>">[削除]</span>
      </li>
    <?php endforeach; ?>
  </ul>
  <style>
  .deleteLink{
    color: blue;
    cursor: pointer;
  }
  </style>
  <p><a href="<?php echo SITE_URL; ?>">お問い合わせフォームに戻る</a></p>
  <script>
  $(function(){
    $('.deleteLink').click(function(){
      if (confirm("削除してもよろしいですか？")){
        var num = $('#num').text();
        num--;
        $.post('./delete.php', {
           id: $(this).data('id')
          }, function(rs){
            $('#entry_' + rs).fadeOut(800);
            $('#num').text(num);
          });
      }
      });
    });
  </script>
</body>
</html>

Basic認証の作り方

コマンドラインから、htpasswd -c .htpass　loginnameと打ちます。

[vagrant@localhost admin]$ htpasswd -c .htpasswd password
New password:
Re-type new password:
Adding password for user password
[vagrant@localhost admin]$ ls -la
合計 12
drwxr-xr-x. 2 vagrant vagrant 4096 11月 15 00:33 2016 .
drwxrwxr-x. 3 vagrant vagrant 4096 11月 15 00:31 2016 ..
-rw-rw-r--. 1 vagrant vagrant   23 11月 15 00:33 2016 .htpasswd

続いて、.htaccessを作ります。

[vagrant@localhost admin]$ vi .htaccess
AuthType Basic
AuthName "Enter your ID & Password"
AuthUserFile /home/vagrant/contact/admin/.htpasswd
require valid-user

[vagrant@localhost admin]$ ls -la
合計 16
drwxr-xr-x. 2 vagrant vagrant 4096 11月 15 00:39 2016 .
drwxrwxr-x. 3 vagrant vagrant 4096 11月 15 00:31 2016 ..
-rw-rw-r--. 1 vagrant vagrant  122 11月 15 00:39 2016 .htaccess
-rw-rw-r--. 1 vagrant vagrant   23 11月 15 00:33 2016 .htpasswd

Mysql, config.php, functions.phpの設定

[vagrant@localhost ~]mysql -u root
create database contact_php;
grant all on contact_php.* to dbuser@localhost identified by 'hogehoge';

use contact_php

create table entries (
  id int not null auto_increment primary key,
  name varchar(255),
  email varchar(255),
  memo text,
  created datetime,
  modified datetime
);
[vagrant@localhost ~]$ mysql -u dbuser -p contact_php

config.php

<?php

define('DSN','mysql:host=localhost;dbname=contact_php');
define('DB_USER','dbuser');
define('DB_PASSWORD','hogehoge');

define('SITE_URL','http://192.168.33.10:8000/');
define('ADMIN_URL', SITE_URL.'admin/');

error_reporting(E_ALL & ~E_NOTICE);
session_set_cookie_params(0, '/contacts_php');
&#91;/php&#93;

function.php
&#91;php&#93;
<?php

function connectDb(){
  try {
    return new PDO(DSN, DB_USER, DB_PASSWORD);
  } catch (PDOException $e){
    echo $e->getMessage();
    exit;
  }
}

function h($s){
  return htmlspecialchars($s, ENT_QUOTES, "UTF-8");
}

psql document

Postgresには独自の命令文が多数用意されています。

PostgreSQL document

blogapp=# select name, length(name) from users;
  name  | length
--------+--------
 yamada |      6
 satou  |      5
 sasaki |      6
 yamada |      6
 satou  |      5
 sasaki |      6
 yamada |      6
(7 行)

抽選システム

blogapp=# select * from users order by random() limit 1;
 id |  name  | score | team
----+--------+-------+-------
  6 | sasaki |   7.6 | green
(1 行)

更新

blogapp=# update users set score = 5.8 where name = 'yamada';
UPDATE 3

削除

blogapp=# delete from users where id = 7;
DELETE 1

テーブルの変更

blogapp=# alter table users add fullname varchar(255);
ALTER TABLE
blogapp=# select * from users;
 id |  name  | score | team  | fullname
----+--------+-------+-------+----------
  2 | satou  |   4.3 | green |
  3 | sasaki |   8.2 | green |
  5 | satou  |   4.2 | blue  |
  6 | sasaki |   7.6 | green |
  1 | yamada |   5.8 | red   |
  4 | yamada |   5.8 | red   |
(6 行)

blogapp=# alter table users alter name type varchar(32);
ALTER TABLE

複数のユーザを突き合わせ

select users.name, post.title from users, posts wher users.id = posts.id;

複雑なselect文はviewを作成

blogapp=# create view yamada_posts as
blogapp-# select users.name, post.title from users, posts wher users.id = posts.id;

beginとcommitで囲むと、トランザクションという処理ができるようになります。変更の取り消しはrollbackです。

psql　

条件検索

blogapp=# select * from users where score > 4.0;
 id |  name  | score |  team
----+--------+-------+--------
  1 | yamada |   6.5 | red
  2 | satou  |   4.3 | green
  3 | sasaki |   8.2 | green
  5 | satou  |   4.2 | blue
  6 | sasaki |   7.6 | green
  7 | yamada |   5.7 | yellow
(6 行)

blogapp=# select * from users where name = 'yamada';
 id |  name  | score |  team
----+--------+-------+--------
  1 | yamada |   6.5 | red
  4 | yamada |   3.6 | red
  7 | yamada |   5.7 | yellow
(3 行)

並び順

blogapp=# select * from users order by score;
 id |  name  | score |  team
----+--------+-------+--------
  4 | yamada |   3.6 | red
  5 | satou  |   4.2 | blue
  2 | satou  |   4.3 | green
  7 | yamada |   5.7 | yellow
  1 | yamada |   6.5 | red
  6 | sasaki |   7.6 | green
  3 | sasaki |   8.2 | green
(7 行)

blogapp=# select * from users order by score desc;
 id |  name  | score |  team
----+--------+-------+--------
  3 | sasaki |   8.2 | green
  6 | sasaki |   7.6 | green
  1 | yamada |   6.5 | red
  7 | yamada |   5.7 | yellow
  2 | satou  |   4.3 | green
  5 | satou  |   4.2 | blue
  4 | yamada |   3.6 | red

offset

blogapp=# select * from users limit3 offset 3;
 id |  name  | score |  team
----+--------+-------+--------
  4 | yamada |   3.6 | red
  5 | satou  |   4.2 | blue
  6 | sasaki |   7.6 | green
  7 | yamada |   5.7 | yellow
(4 行)

レコード集計

blogapp=# select count(*) from users;
 count
-------
     7
(1 行)