[코딩야학] ~ 관계형DB실습 - 17일차

20. 관계형 데이터베이스 이론

https://www.opentutorials.org/course/1688/9427

데이터베이스 ⊃ 관계형 데이터베이스(MySQL, MS-SQL, Oracle), 객체형 데이터베이스, 계층형 데이터베이스, NoSQL

관계형 데이터베이스(Relational Database)

- JOIN과 같은 것을 통해서, 두 개의 테이블이 서로 긴밀하게 관계를 맺고 있음

ex) SELECT title, name FROM topic LEFT JOIN user ON topic.author = user.id

- FROM topic : topic 정보를 조회하겠다.

- JOIN user : topic 테이블과 user 테이블을 결합해라.

- LEFT JOIN : 왼쪽 정보를 기준으로 테이블을 결합하라.

- topic.author=user.id : topic의 author와 user의 id 값을 같게 해라.

- SELECT title, name : title과 name 컬럼만 가져와라.

→ topic과 name 테이블은 물리적으로 존재하는 테이블

   위 SQL문의 결과로 나오는 테이블은 SQL이 시키는대로 조합해서 만든 가상의 테이블(논리적인 테이블)

21. 관계형 데이터베이스 실습

https://www.opentutorials.org/course/1688/9428

1) 관계형 데이터베이스 실습 1

opentutorials.sql

SET FOREIGN_KEY_CHECKS=0;
 
-- ----------------------------
-- Table structure for `topic`
-- ----------------------------
DROP TABLE IF EXISTS `topic`;
CREATE TABLE `topic` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `description` text NOT NULL,
  `author` int(11) NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
 
-- ----------------------------
-- Records of topic
-- ----------------------------
INSERT INTO `topic` VALUES ('1', 'About JavaScript', '<h3>Desctiption</h3>\r\n<p>JavaScript  is a dynamic computer programming language. It is most commonly used as part of web browsers, whose implementations allow client-side scripts to interact with the user, control the browser, communicate asynchronously, and alter the document content that is displayed.</p>\r\n<p>\r\nDespite some naming, syntactic, and standard library similarities, JavaScript and Java are otherwise unrelated and have very different semantics. The syntax of JavaScript is actually derived from C, while the semantics and design are influenced by the Self and Scheme programming languages.\r\n</p>\r\n<h3>See Also</h3>\r\n<ul>\r\n  <li><a href=\"http://en.wikipedia.org/wiki/Dynamic_HTML\">Dynamic HTML and Ajax (programming)</a></li>\r\n  <li><a href=\"http://en.wikipedia.org/wiki/Web_interoperability\">Web interoperability</a></li>\r\n  <li><a href=\"http://en.wikipedia.org/wiki/Web_accessibility\">Web accessibility</a></li>\r\n</ul>\r\n', '1', '2015-03-31 12:14:00');
INSERT INTO `topic` VALUES ('2', 'Variable and Constant', '<h3>Desciption</h3>\r\n\r\nIn computer programming, a variable or scalar is a storage location paired with an associated symbolic name (an identifier), which contains some known or unknown quantity or information referred to as a value. The variable name is the usual way to reference the stored value; this separation of name and content allows the name to be used independently of the exact information it represents. The identifier in computer source code can be bound to a value during run time, and the value of the variable may thus change during the course of program execution.\r\n\r\n<h3>See Also</h3>\r\n<ul>\r\n<li>Non-local variable</li>\r\n<li>Variable interpolation</li>\r\n</ul>\r\n', '3', '2015-05-14 10:04:00');
INSERT INTO `topic` VALUES ('3', 'Opeartor', '<h2>Operator</h2>\r\n<h3>Description</h3>\r\n<p>Programming languages typically support a set of operators: constructs which behave generally like functions, but which differ syntactically or semantically from usual functions</p>\r\n<p>Common simple examples include arithmetic (addition with +, comparison with >) and logical operations (such as AND or &&). </p>\r\n', '1', '2015-06-18 05:00:00');
INSERT INTO `topic` VALUES ('4', 'Conditional', '<h3>Description</h3>\r\n<p>In computer science, conditional statements, conditional expressions and conditional constructs are features of a programming language which perform different computations or actions depending on whether a programmer-specified boolean condition evaluates to true or false. Apart from the case of branch predication, this is always achieved by selectively altering the control flow based on some condition.</p>\r\n<p>In imperative programming languages, the term \"conditional statement\" is usually used, whereas in functional programming, the terms \"conditional expression\" or \"conditional construct\" are preferred, because these terms all have distinct meanings.</p>\r\n<h3>See Also</h3>\r\n<ul>\r\n<li><a href=\"http://en.wikipedia.org/wiki/Branch_(computer_science)\" title=\"Branch (computer science)\">Branch (computer science)</a></li>\r\n<li><a href=\"http://en.wikipedia.org/wiki/Conditional_compilation\" title=\"Conditional compilation\">Conditional compilation</a></li>\r\n<li><a href=\"http://en.wikipedia.org/wiki/Dynamic_dispatch\" title=\"Dynamic dispatch\">Dynamic dispatch</a> for another way to make execution choices</li>\r\n<li><a href=\"http://en.wikipedia.org/wiki/McCarthy_Formalism\" title=\"McCarthy Formalism\">McCarthy Formalism</a> for history and historical references</li>\r\n<li><a href=\"http://en.wikipedia.org/wiki/Named_condition\" title=\"Named condition\" class=\"mw-redirect\">Named condition</a></li>\r\n<li><a href=\"http://en.wikipedia.org/wiki/Test_(Unix)\" title=\"Test (Unix)\">Test (Unix)</a></li>\r\n<li><a href=\"http://en.wikipedia.org/wiki/Yoda_conditions\" title=\"Yoda conditions\">Yoda conditions</a></li>\r\n</ul>', '2', '2015-07-25 00:00:00');
INSERT INTO `topic` VALUES ('5', 'Function', 'A function model or functional model in systems engineering and software engineering is a structured representation of the functions (activities, actions, processes, operations) within the modeled system or subject area.', '2', '0000-00-00 00:00:00');
INSERT INTO `topic` VALUES ('6', 'Object', 'In computer science, an object is a location in memory having a value and possibly referenced by an identifier. An object can be a variable, a data structure, or a function. In the class-based object-oriented programming paradigm, \"object\" refers to a particular instance of a class where the object can be a combination of variables, functions, and data structures. In relational database management, an object can be a table or column, or an association between data and a database entity (such as relating a person\'s age to a specific person)', '3', '0000-00-00 00:00:00');
 
-- ----------------------------
-- Table structure for `user`
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `password` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
 
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'egoing', '111111');
INSERT INTO `user` VALUES ('2', 'jin', '222222');
INSERT INTO `user` VALUES ('3', 'k8805', '333333');
INSERT INTO `user` VALUES ('4', 'sorialgi', '444444');
INSERT INTO `user` VALUES ('5', 'lily', '555555');
INSERT INTO `user` VALUES ('6', 'happydeveloper', '666666');

        

                            

※ index.php에서 부족한 부분 채우기

index.php (line 8 추가)

<article>
    <?php
        if(empty($_GET['id']) === false) {
        $sql='SELECT * FROM topic WHERE id='.$_GET['id'];
        $result = mysqli_query($conn,$sql);
        $row = mysqli_fetch_assoc($result);
        echo '<h2>'.$row['title'].'</h2>';
        echo'<p>'.$row['author'].'</p>';
        echo $row['description'];
        }
    ?>
</article>

 2) 관계형 데이터베이스 실습 2

index.php (<article> 수정)

<?php
  $conn = mysqli_connect('localhost', 'root', '');
  mysqli_select_db($conn, 'opentutorials');
  $result = mysqli_query($conn, 'SELECT * FROM topic');
?>
 
<!DOCTYPE html>
<html>
  <head>
    <meta charset = "utf-8">
    <link rel="stylesheet" type="text/css" href="/style.css">
  </head>
  <body id="target">
    <header>
      <img src="https://s3.ap-northeast-2.amazonaws.com/opentutorials-user-file/course/94.png" alt="생활코딩">
      <h1><a href="/index.php">JavaScript</a></h1>
    </header>
    <nav>
      <ol>
      <?php
        while($row=mysqli_fetch_assoc($result)) {
          echo '<li><a href="/index.php?id='.$row['id'].'">'.$row['title'].'</a></li>'."\n";
        }
      ?>
      </ol>  
    </nav>
    <div id="control">
      <input type="button" value="white" onclick="document.getElementById('target').className='white'"/>
      <input type="button" value="black" onclick="document.getElementById('target').className='black'"/>
      <a href="write.php">쓰기</a> 
    </div>
    <article>
      <?php
        if(empty($_GET['id']) === false) {
          $sql='SELECT topic.id, title, name, description FROM topic LEFT JOIN user ON topic.author = user.id WHERE topic.id='.$_GET['id'];
          $result = mysqli_query($conn,$sql);
          $row = mysqli_fetch_assoc($result);
          echo '<h2>'.$row['title'].'</h2>';
          echo'<p>'.$row['name'].'</p>';
          echo $row['description'];
        }
      ?>
    </article>
  </body>
</html>

3) 관계형 데이터베이스 실습 3

topic 테이블에 데이터를 추가해보자

process.php (author에 따른 id 값 가져오는 부분 추가)

<?php
  $conn = mysqli_connect("localhost", "root","");
  mysqli_select_db($conn, "opentutorials");
  $sql="SELECT * FROM user WHERE name='".$_POST['author']."'";
  $result = mysqli_query($conn, $sql);
  $row = mysqli_fetch_assoc($result);
  $user_id=$row['id'];
  $sql = "INSERT INTO topic (title,description,author,created) VALUES('".$_POST['title']."', '".$_POST['description']."', '".$user_id."', now())";
  $result = mysqli_query($conn, $sql);
  header('Location: /index.php'); // redirection
?>

    →        

위의 코드에서 user 테이블에 등록되어 있지 않은 사용자는 id 값을 가져올 수 없다.

process.php (조건문 추가)

<?php
  $conn = mysqli_connect("localhost", "root","");
  mysqli_select_db($conn, "opentutorials");
  $sql="SELECT * FROM user WHERE name='".$_POST['author']."'";
  $result = mysqli_query($conn, $sql);
 
  if($result->num_rows == 0){
    $sql = "INSERT INTO user (name, password) VALUES('".$_POST['author']."', '111111')";
    mysqli_query($conn, $sql);
    $user_id = mysqli_insert_id($conn);
  } else {
    $row = mysqli_fetch_assoc($result);
    $user_id=$row['id'];
  }
 
  $sql = "INSERT INTO topic (title,description,author,created) VALUES('".$_POST['title']."', '".$_POST['description']."', '".$user_id."', now())";
  $result = mysqli_query($conn, $sql);
  header('Location: /index.php'); // redirection
?>

- num_rows : 데이터베이스에서 쿼리의 결과로 나온 레코드들의 개수 카운트

    →