0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

SQL

0
Last updated at Posted at 2019-10-24
sql
-- 【DB】
-- データを保存するソフト。
-- Excelのイメージ。
-- データベースがExcelファイル、
-- テーブルがExcelのワークシート、
-- カラムがExcelの列、
-- レコードはExcelの行。
-- SQLという言語で登録、取得、更新、削除ができる。

C:\xampp\mysql\bin\mysql -u root -p

DROP DATABASE IF EXISTS test;

CREATE DATABASE test CHARACTER SET utf8;

USE test;

DROP TABLE IF EXISTS users;

CREATE TABLE users (
    id int NOT NULL AUTO_INCREMENT,
    name varchar(255), -- 255文字まで登録できる
    gender_id INT NOT NULL,
    created_at datetime  NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

INSERT INTO users (name, gender_id) VALUES ('りんご', 1);
INSERT INTO users (name, gender_id) VALUES ('みかん', 2);

SELECT * FROM users;

UPDATE users SET name='ばなな' WHERE id = 1;

SELECT * FROM users WHERE id = 1;
SELECT id,name,created_at FROM users WHERE id = 1;

DELETE FROM users WHERE id = 1;

INNER JOIN

sql
DROP TABLE IF EXISTS gender;

CREATE TABLE gender (
    id int NOT NULL,
    name varchar(255),
    PRIMARY KEY (id)
);

INSERT INTO gender (id, name) VALUES (1, '男');
INSERT INTO gender (id, name) VALUES (2, '女');

SELECT A.id,A.name,B.name FROM users A INNER JOIN gender B ON A.gender_id = B.id;


PHPで接続

php
<?php

$mysqli = new mysqli('localhost', 'root', null, 'test');
if ($mysqli->connect_errno) {
  exit();
}

// $query = 'SELECT * FROM users;';
$query = 'SELECT A.id, A.name, B.name AS gender FROM users A INNER JOIN gender B ON A.gender_id = B.id;';
if ($items = $mysqli->query($query, MYSQLI_USE_RESULT)) {
  foreach ($items as $item) {
    echo $item['id'] . ' : ' . $item['name'] . ' : ' . $item['gender'] . '<br />';
  }
}
$mysqli->close();
?>

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?