reset.cssとnormailze.css

ブラウザに標準として使われているUser Agent Sytlesheetでは各ブラウザでばらばらなので、ブラウザごとのCSSの仕様を合わせるのにreset.cssとnormalize.cssがあります。それぞれ、サイトからコードをひっぱてきましょう。

reset.css
HTML5 doctor reset.css

/* 
html5doctor.com Reset Stylesheet
v1.6.1
Last Updated: 2010-09-17
Author: Richard Clark - http://richclarkdesign.com 
Twitter: @rich_clark
*/

html, body, div, span, object, iframe,
h1, h2, h3, h4, h5, h6, p, blockquote, pre,
abbr, address, cite, code,
del, dfn, em, img, ins, kbd, q, samp,
small, strong, sub, sup, var,
b, i,
dl, dt, dd, ol, ul, li,
fieldset, form, label, legend,
table, caption, tbody, tfoot, thead, tr, th, td,
article, aside, canvas, details, figcaption, figure, 
footer, header, hgroup, menu, nav, section, summary,
time, mark, audio, video {
    margin:0;
    padding:0;
    border:0;
    outline:0;
    font-size:100%;
    vertical-align:baseline;
    background:transparent;
}

body {
    line-height:1;
}

article,aside,details,figcaption,figure,
footer,header,hgroup,menu,nav,section { 
    display:block;
}

nav ul {
    list-style:none;
}

blockquote, q {
    quotes:none;
}

blockquote:before, blockquote:after,
q:before, q:after {
    content:'';
    content:none;
}

a {
    margin:0;
    padding:0;
    font-size:100%;
    vertical-align:baseline;
    background:transparent;
}

/* change colours to suit your needs */
ins {
    background-color:#ff9;
    color:#000;
    text-decoration:none;
}

/* change colours to suit your needs */
mark {
    background-color:#ff9;
    color:#000; 
    font-style:italic;
    font-weight:bold;
}

del {
    text-decoration: line-through;
}

abbr[title], dfn[title] {
    border-bottom:1px dotted;
    cursor:help;
}

table {
    border-collapse:collapse;
    border-spacing:0;
}

/* change border colour to suit your needs */
hr {
    display:block;
    height:1px;
    border:0;   
    border-top:1px solid #cccccc;
    margin:1em 0;
    padding:0;
}

input, select {
    vertical-align:middle;
}

normalize.css
normalize.css

/*! normalize.css v5.0.0 | MIT License | github.com/necolas/normalize.css */

/**
 * 1. Change the default font family in all browsers (opinionated).
 * 2. Correct the line height in all browsers.
 * 3. Prevent adjustments of font size after orientation changes in
 *    IE on Windows Phone and in iOS.
 */

/* Document
   ========================================================================== */

html {
  font-family: sans-serif; /* 1 */
  line-height: 1.15; /* 2 */
  -ms-text-size-adjust: 100%; /* 3 */
  -webkit-text-size-adjust: 100%; /* 3 */
}

/* Sections
   ========================================================================== */

/**
 * Remove the margin in all browsers (opinionated).
 */

body {
  margin: 0;
}

/**
 * Add the correct display in IE 9-.
 */

article,
aside,
footer,
header,
nav,
section {
  display: block;
}

/**
 * Correct the font size and margin on `h1` elements within `section` and
 * `article` contexts in Chrome, Firefox, and Safari.
 */

h1 {
  font-size: 2em;
  margin: 0.67em 0;
}

/* Grouping content
   ========================================================================== */

/**
 * Add the correct display in IE 9-.
 * 1. Add the correct display in IE.
 */

figcaption,
figure,
main { /* 1 */
  display: block;
}

/**
 * Add the correct margin in IE 8.
 */

figure {
  margin: 1em 40px;
}

/**
 * 1. Add the correct box sizing in Firefox.
 * 2. Show the overflow in Edge and IE.
 */

hr {
  box-sizing: content-box; /* 1 */
  height: 0; /* 1 */
  overflow: visible; /* 2 */
}

/**
 * 1. Correct the inheritance and scaling of font size in all browsers.
 * 2. Correct the odd `em` font sizing in all browsers.
 */

pre {
  font-family: monospace, monospace; /* 1 */
  font-size: 1em; /* 2 */
}

/* Text-level semantics
   ========================================================================== */

/**
 * 1. Remove the gray background on active links in IE 10.
 * 2. Remove gaps in links underline in iOS 8+ and Safari 8+.
 */

a {
  background-color: transparent; /* 1 */
  -webkit-text-decoration-skip: objects; /* 2 */
}

/**
 * Remove the outline on focused links when they are also active or hovered
 * in all browsers (opinionated).
 */

a:active,
a:hover {
  outline-width: 0;
}

/**
 * 1. Remove the bottom border in Firefox 39-.
 * 2. Add the correct text decoration in Chrome, Edge, IE, Opera, and Safari.
 */

abbr[title] {
  border-bottom: none; /* 1 */
  text-decoration: underline; /* 2 */
  text-decoration: underline dotted; /* 2 */
}

/**
 * Prevent the duplicate application of `bolder` by the next rule in Safari 6.
 */

b,
strong {
  font-weight: inherit;
}

/**
 * Add the correct font weight in Chrome, Edge, and Safari.
 */

b,
strong {
  font-weight: bolder;
}

/**
 * 1. Correct the inheritance and scaling of font size in all browsers.
 * 2. Correct the odd `em` font sizing in all browsers.
 */

code,
kbd,
samp {
  font-family: monospace, monospace; /* 1 */
  font-size: 1em; /* 2 */
}

/**
 * Add the correct font style in Android 4.3-.
 */

dfn {
  font-style: italic;
}

/**
 * Add the correct background and color in IE 9-.
 */

mark {
  background-color: #ff0;
  color: #000;
}

/**
 * Add the correct font size in all browsers.
 */

small {
  font-size: 80%;
}

/**
 * Prevent `sub` and `sup` elements from affecting the line height in
 * all browsers.
 */

sub,
sup {
  font-size: 75%;
  line-height: 0;
  position: relative;
  vertical-align: baseline;
}

sub {
  bottom: -0.25em;
}

sup {
  top: -0.5em;
}

/* Embedded content
   ========================================================================== */

/**
 * Add the correct display in IE 9-.
 */

audio,
video {
  display: inline-block;
}

/**
 * Add the correct display in iOS 4-7.
 */

audio:not([controls]) {
  display: none;
  height: 0;
}

/**
 * Remove the border on images inside links in IE 10-.
 */

img {
  border-style: none;
}

/**
 * Hide the overflow in IE.
 */

svg:not(:root) {
  overflow: hidden;
}

/* Forms
   ========================================================================== */

/**
 * 1. Change the font styles in all browsers (opinionated).
 * 2. Remove the margin in Firefox and Safari.
 */

button,
input,
optgroup,
select,
textarea {
  font-family: sans-serif; /* 1 */
  font-size: 100%; /* 1 */
  line-height: 1.15; /* 1 */
  margin: 0; /* 2 */
}

/**
 * Show the overflow in IE.
 * 1. Show the overflow in Edge.
 */

button,
input { /* 1 */
  overflow: visible;
}

/**
 * Remove the inheritance of text transform in Edge, Firefox, and IE.
 * 1. Remove the inheritance of text transform in Firefox.
 */

button,
select { /* 1 */
  text-transform: none;
}

/**
 * 1. Prevent a WebKit bug where (2) destroys native `audio` and `video`
 *    controls in Android 4.
 * 2. Correct the inability to style clickable types in iOS and Safari.
 */

button,
html [type="button"], /* 1 */
[type="reset"],
[type="submit"] {
  -webkit-appearance: button; /* 2 */
}

/**
 * Remove the inner border and padding in Firefox.
 */

button::-moz-focus-inner,
[type="button"]::-moz-focus-inner,
[type="reset"]::-moz-focus-inner,
[type="submit"]::-moz-focus-inner {
  border-style: none;
  padding: 0;
}

/**
 * Restore the focus styles unset by the previous rule.
 */

button:-moz-focusring,
[type="button"]:-moz-focusring,
[type="reset"]:-moz-focusring,
[type="submit"]:-moz-focusring {
  outline: 1px dotted ButtonText;
}

/**
 * Change the border, margin, and padding in all browsers (opinionated).
 */

fieldset {
  border: 1px solid #c0c0c0;
  margin: 0 2px;
  padding: 0.35em 0.625em 0.75em;
}

/**
 * 1. Correct the text wrapping in Edge and IE.
 * 2. Correct the color inheritance from `fieldset` elements in IE.
 * 3. Remove the padding so developers are not caught out when they zero out
 *    `fieldset` elements in all browsers.
 */

legend {
  box-sizing: border-box; /* 1 */
  color: inherit; /* 2 */
  display: table; /* 1 */
  max-width: 100%; /* 1 */
  padding: 0; /* 3 */
  white-space: normal; /* 1 */
}

/**
 * 1. Add the correct display in IE 9-.
 * 2. Add the correct vertical alignment in Chrome, Firefox, and Opera.
 */

progress {
  display: inline-block; /* 1 */
  vertical-align: baseline; /* 2 */
}

/**
 * Remove the default vertical scrollbar in IE.
 */

textarea {
  overflow: auto;
}

/**
 * 1. Add the correct box sizing in IE 10-.
 * 2. Remove the padding in IE 10-.
 */

[type="checkbox"],
[type="radio"] {
  box-sizing: border-box; /* 1 */
  padding: 0; /* 2 */
}

/**
 * Correct the cursor style of increment and decrement buttons in Chrome.
 */

[type="number"]::-webkit-inner-spin-button,
[type="number"]::-webkit-outer-spin-button {
  height: auto;
}

/**
 * 1. Correct the odd appearance in Chrome and Safari.
 * 2. Correct the outline style in Safari.
 */

[type="search"] {
  -webkit-appearance: textfield; /* 1 */
  outline-offset: -2px; /* 2 */
}

/**
 * Remove the inner padding and cancel buttons in Chrome and Safari on macOS.
 */

[type="search"]::-webkit-search-cancel-button,
[type="search"]::-webkit-search-decoration {
  -webkit-appearance: none;
}

/**
 * 1. Correct the inability to style clickable types in iOS and Safari.
 * 2. Change font properties to `inherit` in Safari.
 */

::-webkit-file-upload-button {
  -webkit-appearance: button; /* 1 */
  font: inherit; /* 2 */
}

/* Interactive
   ========================================================================== */

/*
 * Add the correct display in IE 9-.
 * 1. Add the correct display in Edge, IE, and Firefox.
 */

details, /* 1 */
menu {
  display: block;
}

/*
 * Add the correct display in all browsers.
 */

summary {
  display: list-item;
}

/* Scripting
   ========================================================================== */

/**
 * Add the correct display in IE 9-.
 */

canvas {
  display: inline-block;
}

/**
 * Add the correct display in IE.
 */

template {
  display: none;
}

/* Hidden
   ========================================================================== */

/**
 * Add the correct display in IE 10-.
 */

[hidden] {
  display: none;
}

HTML

<!DOCTYPE html>
<html lang="ja">
  <head>
   <meta charset="utf-8">
   <title>CSS Layout practice</title>
   <link rel="stylesheet" href="normalize.css">
   <!-- <link rel="stylesheet" href="reset.css"> -->
   <link rel="stylesheet" href="styles.css">
  </head>
<body>
  <h1>Hello</h1>
  <p>world</p>
</body>
</html>

css3

rgba :aで透明度の設定

#box1{
  background: rgba(255, 100, 100, 0.8);
}

hsla :明度の変更

#box1{
  background: hsla(210, 100%, 20%, 0.8);
}

opacity :透明度

body { background: skyblue; }

img{
  border: 7px solid blue;  
  opacity: 0.3;
}

属性セレクタ :^始まる、$終わる、*含む

a[href^="http"]{ color: orange; }

疑似クラス :last-child, nth-child(n) (※odd,evenも可), nth-last-child(n), only-child

li:last-child
{
  background: violet;
}

疑似クラス2 :first-of-type, last-of-type, nth-of-type(n), nth-last-of-type(n), only-of-type

p:first-of-type{
  background: violet;
}

疑似クラス3 :not(.) ,[type=”text”]:enabled, input[type=”checkbox”]:checked + label

li:not(.target)
{
  background:violet;
}

border-radius :角丸, border-radius: 30px / 15px; , order-bottom-right-radius, border-radius: 50%

div {
  width: 200px;
  height: 200px;
  background: skyblue;
  border-radius: 30px;
}

背景切り抜き

div {
  width: 200px;
  height: 200px;
  background: skyblue
  url('orange.jpg');
  border-radius: 50%
}

background-size :cover, contain

body {margin: 0;}

header {
  width: 100%; 
  height: 130px;
  background: url('orange.jpg')
  no-repeat skyblue;
  background-size: 50%;
}

複数の背景画像

body {margin: 0;}

header {
  width: 100%; 
  height: 130px;
  background:
  url('orange.jpg') no-repeat 0 0,
  url('apple.jpg') no-repeat 30px 30px;
}

グラデーション: linear-gradient(),linear-gradient(to bottom right,skyblue, blue);, linear-gradient(lightgreen 10%,skyblue 60%, blue);

body {margin: 0;}

div {
  width: 200px; 
  height: 200px;
  background-image: 
  linear-gradient(skyblue, blue);
}

円形グラデーション :radial-gradient(skyblue, blue),radial-gradient(at 30px 40px,skyblue, blue),radial-gradient(20px 30px at 30px 40px,skyblue, blue)

body {margin: 0;}

div {
  width: 200px; 
  height: 200px;
  background-image: 
  radial-gradient(skyblue, blue)
}

影: box-shadow, text-shadow

body {background: #eee;}

div {
  width: 200px;
  height: 100px;
  background: #fff;
  box-shadow: 10px 20px rgba(0, 0, 0, 4);
}

図形の変更:transform, skew

body {background: #eee;}

div {
  width: 100px;
  height: 100px;
  margin-bottom: 20px;
  position: absolute;
  top: 100px;
  left: 100px;
}

#box1 {
   background: skyblue;
   opacity: .5;
}

#box2 {
   background: orange;
   opacity: .5;
   transform:translate(20px, 40px);
}

ベンダープレフィックス

-webkit-transform:skey(0.5, 1.5);
   transform:skey(0.5, 1.5);

transition

body {background: #eee;}

div {
  width: 100px;
  height: 100px;
  background: skyblue;

  transition-property:all;
  transition-duration:2s;
  transition-timimg-function:ease;
  transition-delay:0.8s;
}

div:hover {
   width: 200px;
   height: 50px;
   background: blue;
}

PHP MySQLからデータを引いて、ページング

<?php

define('DB_HOST', 'localhost');
define('DB_USER', 'dbuser');
define('DB_PASSWORD', 'xxxx');
define('DB_NAME', 'testdb');
define('COMMENTS_PER_PAGE', 5);

if (preg_match('/^&#91;1-9&#93;&#91;0-9&#93;*$/', $_GET&#91;'page'&#93;)){
$page = (int)$_GET&#91;'page'&#93;;
} else {
  $page = 1;
}

error_reporting(E_ALL & ~E_NOTICE);

try {
  $dbh = new PDO
('mysql:host='.DB_HOST.';dbname='.DB_NAME,DB_USER,DB_PASSWORD);
} catch (PDOException $e){
  echo $e->getMessage();
  exit;
}

// select * from comments limit OFFSET,Count
// page offset Count
// 1 0 5
// 2 5 5
// 3 10 5

$offset = COMMENTS_PER_PAGE * ($page -1);
$sql = "select * from comments limit ".$offset.",".COMMENTS_PER_PAGE;
$comments = array();
foreach ($dbh->query($sql) as $row){
 array_push($comments, $row);
}
$total = $dbh->query("select count(*) from comments")->fetchColumn();
$totalPages = ceil($total / COMMENTS_PER_PAGE);

$from = $offset + 1;
$to = ($offset + COMMENTS_PER_PAGE) <  $total ? ($offset + COMMENTS_PER_PAGE): $total;

?>
<!DOCTYPE html>
<html lang="ja">
<head>
    <meta charset="utf-8">
    <title>コメント一覧</title>
</head>
<body>
    <h1>コメント一覧</h1>
    <p>全<?php echo $total; ?>件中、<?php echo $from; ?>件~<?php echo $to; ?>件を表示しています</p>
    <ul>
    <?php foreach ($comments as $comment) : ?>
      <li><?php echo htmlspecialchars($comment&#91;'comment'&#93;,ENT_QUOTES,'UTF-8'); ?></li>
    <?php endforeach; ?>
  </ul>
  <?php if($page > 1) : ?>
  <a href="?page=<?php echo $page-1; ?>">前へ</a>
<?php endif; ?>
  <?php for ($i = 1; $i <= $totalPages; $i++): ?>
    <?php if ($page == $i) : ?>
  <strong><a href="?page=<?php echo $i; ?>"><?php echo $i; ?></a></strong>
<?php else: ?>
    <a href="?page=<?php echo $i; ?>"><?php echo $i; ?></a>
<?php endif; ?>
  <?php endfor; ?>
  <?php if ($page < $totalPages): ?>
  <a href="?page=<?php echo $page+1; ?>">次へ</a>
<?php endif; ?>
</body>
</html>

SQLでデータを作ります。

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

create table comments (
 id int not null auto_increment primary key,
 comment text,
 created datetime,
 modified datetime
);

insert into comments (comment, created, modified) values
('コメント1', now(), now()),
('コメント2', now(), now()),
('コメント3', now(), now()),
('コメント4', now(), now()),
('コメント5', now(), now()),
('コメント6', now(), now()),
('コメント7', now(), now()),
('コメント8', now(), now()),
('コメント9', now(), now()),
('コメント10', now(), now()),
('コメント11', now(), now()),
('コメント12', now(), now()),
('コメント13', now(), now()),
('コメント14', now(), now()),
('コメント15', now(), now()),
('コメント16', now(), now()),
('コメント17', now(), now()),
('コメント18', now(), now());

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

Google Chart 棒グラフ

<!DOCTYPE html>
<html lang="ja" ng-app>
<head>
    <meta charset="UTF-8">
    <title>Google chart</title>
    <script src="https://www.google.com/jsapi"></script>
    <script>
      google.load('visualization', '1.0', {'packages' : ['corechart']});
      google.setOnLoadCallback(drawChart);

      function drawChart(){
        // データを用意
        // グラフのオプション
        // 描画する
        var data = new google.visualization.DataTable();
        data.addColumn('string', '年');
        data.addColumn('number', '売上');
        data.addColumn('number', '利益');
        data.addRows([
            ['2000', 500, 300],
            ['2001', 600, 320],
            ['2002', 800, 200],
            ['2003', 400, 200],
            ['2004', 200, 100],
          ]);

        var options = {
          title: '業績推移',
          width: 500,
          height: 500,
          isStack: true
        }
        var chart = new google.visualization.BarChart(document.getElementById('chart'));
        chart.draw(data, options);
      }
    </script>
<body>
<div id="chart"></div>
<h1></h1>
<p></p>
</body>
</html>

a

bar chartをcolumnにすると縦棒グラフになります。
var chart = new google.visualization.ColumnChart(document.getElementById(‘chart’));
%e7%84%a1%e9%a1%8c

棒グラフはLineです。
var chart = new google.visualization.LineChart(document.getElementById(‘chart’));
%e7%84%a1%e9%a1%8c

Google Charts

google chartsで円グラフを描画します。

<!DOCTYPE html>
<html lang="ja" ng-app>
<head>
    <meta charset="UTF-8">
    <title>Google chart</title>
    <script src="https://www.google.com/jsapi"></script>
    <script>
      google.load('visualization', '1.0', {'packages' : ['corechart']});
      google.setOnLoadCallback(drawChart);

      function drawChart(){
        // データを用意
        // グラフのオプション
        // 描画する
        var data = new google.visualization.DataTable();
        data.addColumn('string', '活動');
        data.addColumn('number', '時間');
        data.addRows([
            ['睡眠', 8],
            ['仕事', 12],
            ['ネット', 4],
          ]);

        var options = {
          title: '一日の内訳',
          width: 500,
          height: 500,
          pieSliceText: 'label',
          is3D: true
        }
        var chart = new google.visualization.PieChart(document.getElementById('chart'));
        chart.draw(data, options);
      }
    </script>
<body>
<div id="chart"></div>
<h1></h1>
<p></p>
</body>
</html>

piechart

PDOで条件付き検索

<?php

define('DB_DATABASE', 'name_db');
define('DB_USERNAME', 'dbuser');
define('DB_PASSWORD', 'xxxx');
define('PDO_DSN', 'mysql:dbhost=localhost;dbname=' . DB_DATABASE);

try {
  $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD);
  $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  // $stmt = $db->prepare("select score from users where score > ?");
  // $stmt->execute([60]);

  //$stmt = $db->prepare("select name from users where name like ?");
  //$stmt->execute(['%t%']);

  $stmt = $db->prepare("select score from users order by score desc limit ?");
  $stmt->bindValue(1, 1, PDO::PARAM_INT);
  $stmt->execute();

  $users = $stmt->fetchAll(PDO::FETCH_ASSOC);
  foreach($users as $user){
    var_dump($user);
  }
  echo $stmt->rowCount() . "records found.";

  /*
  (1) exec(): 結果を返さない、安全なsql
  (2) query(): 結果を返す、安全、何回も実行されない
  (3) prepare(): 結果を返す、安全対策が必要、複数回実行
  */

  // $stmt = $db->prepare("insert into users(name, score) values(?, ?)");
  // $stmt->execute(['tagu', 44]);
  // $stmt = $db->prepare("insert into users(name, score) values(:name, :score)");
  // $stmt->execute(['name'=>'nishikawa', ':score'=>76]);
  // echo "inserted: " . $db->lastInsertId();

  // bindValue 値をbind
  // bindParam 変数への参照をbindValue

  // $stmt = $db->prepare("insert into users(name, score) values(?, ?)");

  // $name = 'tagu';
  // $stmt->bindValue(1, $name, PDO::PARAM_STR);
  // $score= 23;
  // $stmt->bindValue(2, $score, PDO::PARAM_INT);
  // $stmt->execute();
  // $score= 44;
  // $stmt->bindValue(2, $score, PDO::PARAM_INT);
  // $stmt->execute();
  //$stmt->bindParam(2, $score, PDO::PARAM_INT);
  // $score = 52;
  // $stmt->execute();
  // $score = 44;
  // $stmt->execute();
  // $score = 2;
  // $stmt->execute();

} catch (PDOException $e){
  echo $e->getMessage();
  exit;
}

phpでPOD接続

PODとはphp data obujectの略で、データベース接続クラスのことです。 PHPは標準でMySQLやPostgreSQLやSQLiteなど、色々なデータベースに接続するための命令が用意されています。PDOを使うことで、どのデータベースを利用する場合でも同じ関数を使うことができますので、もし将来データベースを変更することがあっても、PDOオブジェクトを作成する時に指定したパラメータだけを変更するばいいことになります。

index.php

<?php

define('DB_DATABASE', 'name_db');
define('DB_USERNAME', 'dbuser');
define('DB_PASSWORD', 'xxxx');
define('PDO_DSN', 'mysql:dbhost=localhost;dbname=' . DB_DATABASE);

try {
  $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD);
  $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  // insert
  $db->exec("insert into users (name, score) values('yamada', 55)");
  echo "user added!";

  // disconnect
  $db = null;

} catch (PDOException $e){
  echo $e->getMessage();
  exit;
}

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

prepared

<?php

define('DB_DATABASE', 'name_db');
define('DB_USERNAME', 'dbuser');
define('DB_PASSWORD', 'xxxx');
define('PDO_DSN', 'mysql:dbhost=localhost;dbname=' . DB_DATABASE);

try {
  $db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD);
  $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  /*
  (1) exec(): 結果を返さない、安全なsql
  (2) query(): 結果を返す、安全、何回も実行されない
  (3) prepare(): 結果を返す、安全対策が必要、複数回実行
  */

  $stmt = $db->prepare("insert into users(name, score) values(?, ?)");
  $stmt->execute(['name', 44]);
  echo "inserted: " . $db->lastInsertId();

} catch (PDOException $e){
  echo $e->getMessage();
  exit;
}

mysqlのバックアップ・復元

バックアップの取り方

[vagrant@localhost mysql]$ mysqldump -u dbuser -p blog_app > blog_app.dump.sql
Enter password:
[vagrant@localhost mysql]$ ls
blog_app.dump.sql  commands.sql

復元方法
不等号記号がバックアップの逆になります。
mysql -u dbuser -p blog_app < blog_app.dump.sql

mysqlで遊ぼう3

レコードの件数表示
select count(*) from users;
レコードのフィールドの値
select distinct team from users;
最大値の抽出
select max(score) from users;
select avg(score) from users;
select sum(score) from users;
チームごとの平均値表示
select avg(score) from users group by team;
ランダム関数も使えます。
select rand();
select * from users order by rand() limit 1;

mysql> select * from users order by rand() limit 1;
+----+--------+------------------+----------+------+-------+---------------------+
| id | name   | email            | password | team | score | created             |
+----+--------+------------------+----------+------+-------+---------------------+
|  3 | kimura | kimura@gmail.com | NULL     | red  |   2.3 | 2016-11-01 09:00:00 |
+----+--------+------------------+----------+------+-------+---------------------+
1 row in set (0.00 sec)

文字列、日付関数
select email, length(email) from users;
連結関数
select concat(name,'(‘,team,’)’) from users;
select concat(name,'(‘,team,’)’) as label from users;
select name, substring(team,1,1) from users;
select now();
select name,month(created) from users;
select name, datediff(now(), created) from users;

レコードの更新は通常updateを使います。

mysql> update users set team = 'red' where id = 5;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from users;
+----+----------+--------------------+----------+--------+-------+---------------------+
| id | name     | email              | password | team   | score | created             |
+----+----------+--------------------+----------+--------+-------+---------------------+
|  1 | yamada   | yamada@gmail.com   | NULL     | blue   |   5.5 | 2016-11-03 11:00:00 |
|  2 | sasaki   | sasaki@gmail.com   | NULL     | yellow |   8.2 | 2016-11-02 10:00:00 |
|  3 | kimura   | kimura@gmail.com   | NULL     | red    |   2.3 | 2016-11-01 09:00:00 |
|  4 | tanaka   | tanaka@gmail.com   | NULL     | blue   |   4.5 | 2016-10-31 08:00:00 |
|  5 | yamamoto | yamamoto@gmail.com | NULL     | red    |   7.4 | 2016-10-30 07:00:00 |
|  6 | yoshida  | yoshida@gmail.com  | NULL     | yellow |   4.2 | 2016-10-29 06:00:00 |
+----+----------+--------------------+----------+--------+-------+---------------------+
6 rows in set (0.00 sec)

削除にはdeleteを使用します。
delete from users where score <= 3.0; データ構造の変更
フィールドの追加
alter table users add full_name varchar(255) after name;
フィールドの変更
alter table users change full_name full_name varchar(100);
フィールドの削除
alter table users drop full_name;
テーブル名変更
alter table users rename blog_users;

テーブルの連結
テーブルを追加して、連結します。

create table posts (
 id int not null auto_increment primary key,
 user_id int not null,
 title varchar(255),
 body text,
 created datetime
);

insert into posts (user_id,title,body,created) values
(1, 'title-1 by yamada','body-1','2016-11-01 14:00:00'),
(1, 'title-2 by yamada','body-2','2016-10-31 13:00:00'),
(2, 'title-3 by sasaki','body-3','2016-10-30 12:00:00'),
(2, 'title-4 by sasaki','body-3','2016-10-29 11:00:00'),
(3, 'title-5 by kimura','body-4','2016-10-28 10:00:00'),
(3, 'title-6 by kimura','body-4','2016-10-27 09:00:00');

mysql> select * from posts;
+----+---------+-------------------+--------+---------------------+
| id | user_id | title             | body   | created             |
+----+---------+-------------------+--------+---------------------+
|  1 |       1 | title-1 by yamada | body-1 | 2016-11-01 14:00:00 |
|  2 |       1 | title-2 by yamada | body-2 | 2016-10-31 13:00:00 |
|  3 |       2 | title-3 by sasaki | body-3 | 2016-10-30 12:00:00 |
|  4 |       2 | title-4 by sasaki | body-3 | 2016-10-29 11:00:00 |
|  5 |       3 | title-5 by kimura | body-4 | 2016-10-28 10:00:00 |
|  6 |       3 | title-6 by kimura | body-4 | 2016-10-27 09:00:00 |
+----+---------+-------------------+--------+---------------------+
6 rows in set (0.00 sec)

mysql> select users.name,posts.title from users,posts where users.id=posts.user_id;
+--------+-------------------+
| name   | title             |
+--------+-------------------+
| yamada | title-1 by yamada |
| yamada | title-2 by yamada |
| sasaki | title-3 by sasaki |
| sasaki | title-4 by sasaki |
+--------+-------------------+
4 rows in set (0.00 sec)

条件指定することも可能です。
select users.name,posts.title,posts.created from users,posts where users.id=posts.user_id order by posts.created desc;

外部のsqlファイルからコマンドを打つことも可能です。

drop table if exists users;
create table users (
 name varchar(255),
 email varchar(255)
);
insert into users (name,email) values ('name','name@gmail.com');

外部コマンドの入力方法は以下の通りです。
[vagrant@localhost mysql]$ mysql -u dbuser -p blog_app < commands.sql