201312051721


SQL:

  1  DDL data definition language
    CREATE
    ALTER
    DROP
  2  DML data manipulation language
    INSERT
    UPDATE
    DELETE
    
    SELECT
  3  DCL data control language
    GRANT
    REVOKE


Names:
  ID    123
  name  Ilja
  
People
  ID    1
  PID   123
  money 111
  ID    2
  PID   123
  money 55

Transactions

  BEGIN
  COMMIT
  ROLLBACK

View



Confidentiality:
  SQL server + Client in DMZ

Integrity
  Relations
  Transactions
  View
  
Access
MySQL 3306 user / password

[C:\xampp\mysql\bin]mysql -u root

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.27 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE pv12
Database created

mysql> USE pv12;
Database changed

mysql> SHOW TABLES;
Empty set (0.00 sec)

mysql> CREATE TABLE NAMES (
    -> ID INT,
    -> name varchar(30)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW TABLES;
+----------------+
| Tables_in_pv12 |
+----------------+
| names          |
+----------------+
1 row in set (0.00 sec)

mysql> use pv12;
Database changed
mysql> CREATE TABLE people (ID INT, PID INT, money INT);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO PEOPLE (ID, PID, money) VALUES (1, 123, 111);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO people (ID, PID, money) VALUES (2, 123, 55);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM people;
+------+------+-------+
| ID   | PID  | money |
+------+------+-------+
|    1 |  123 |   100 |
|    2 |  123 |    55 |
+------+------+-------+
2 rows in set (0.00 sec)

mysql> SELECT names.name, people.ID, people.money 
    -> FROM names, people 
    -> WHERE names.ID=people.pid;
    
+------+------+-------+
| name | ID   | money |
+------+------+-------+
| Ilja |    1 |   100 |
| Ilja |    2 |    55 |
+------+------+-------+
2 rows in set (0.00 sec)

mysql> UPDATE names SET name="Ilya";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT names.name, people.ID, people.money 
    -> FROM names, people WHERE 
    -> names.ID=people.pid;
    
+------+------+-------+
| name | ID   | money |
+------+------+-------+
| Ilya |    1 |   111 |
| Tlya |    2 |    55 |
+------+------+-------+
2 rows in set (0.00 sec)

mysql> CREATE VIEW `doublevision` AS 
    -> SELECT names.name, people.ID, people.money 
    -> FROM names, people 
    -> WHERE names.ID=people.pid;
