Please enable JavaScript.

Coggle requires JavaScript to display documents.

DB databases - Coggle Diagram

- - - - attributes
        column of which has a relation
        e.g. in relation to a student:
        
        last-name
        
        first-name
        
        date-of-birth
        
        student-number
      - tuples
        row: collection of specific attribute values
      - one-to-one (1-1)
        one tuple of the first relation
        ---can be associated with no more than---
        one tuple of another relation.
        The opposite is also true.
        e.g.:
        passport-citizen
        (one person can have only one passport
        and a passport refers to one person).
        
        one-to-many (1-M)
        a tuple of the first relation
        ---can be associated with one or more---
        tuples of the second relation
        Opposite NOT true.
        Any tuple of the second relation
        ---can be associated with only one---
        tuple of the first relation.
        e.g.:,
        student-group
        (there can be many students in a group,
        but each student belongs to only one group).
        
        many-to-many (M-M)
        any tuple of the first relation
        --- can be associated with one or more---
        tuples of another relation.
        The opposite is also true.
        e.g.:,
        student-discipline
        (each student studies many disciplines,
        and each discipline can be studied by many students).
        
        do not exist in RDM
        
        split into two one-to-many relationships,
        so that an intermediate associative relation appears,
        which has two foreign keys as attributes
        that indicate the primary keys of the original relations.
        
        identifying
        foreign key of a subordinate relationship goes into (or becomes part of) its primary key.
        For example, the order number (PK of order relation) in the store
        is generated based on the customer number and the order date.
        The customer number is the PK for the customer relation.
        The order number is a composite PK of two attributes:
        customer number (also a foreign key) and order date.
        
        non-identifying
        the foreign key relationship does NOT go to the primary key,
        for example, the Student-Group relationship discussed above.
  - - - primary key (PK)
        field / set of fields
        uniquely identify each record
    - - foreign key (FK)
        
        an attribute of a relation (subordinate/dependent)
        ...pointing to...
        the primary key of another relation (main/master)
        
        FK is used to create a many-to-one (one-to-many) relationship.
  - - - INSERT INTO
        
        implicit
        ordering known
        INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
        
        explicit
        may omit values not known; default value or NULL will be set instead
        INSERT INTO weather (date, city, temp_hi, temp_lo) VALUES ('1994-11-29', 'Hayward', 54, 37);
        
        multiple rows
        
        INSERT INTO customers (name, surname, zip_code, city) VALUES ('Mary', 'West', 75201, 'Dallas'), ('Steve', 'Palmer', 33107, 'Miami');
        
        SELECT
        
        SELECT CAST(1 AS DECIMAL(20,3));
        SELECT 'Alice', 170, 170*0.393701;
        SELECT 'Alice' AS name, 170 AS height_in_centimeters, 170*0.393701 AS "height in inches" ;
        SELECT id, title, rating FROM books WHERE author IN ('Charles Dickens', 'Mark Twain') ;
        
        1 more item...
        
        DELETE
        
        all rows
        DELETE FROM books;
        
        specific
        DELETE FROM books WHERE quantity = 0;
        
        Special WHERE operators
        
        3 more items...
        
        2 more items...
        
        2 more items...
        
        CREATE TABLE characters ( character_id INT, character_name VARCHAR(30), universe_id INT ); INSERT INTO characters (character_id, character_name, universe_id) VALUES (1, 'Harry Potter', 1), (2, 'Severus Snape', 1), (3, 'Voldemort', 1), (4, 'Hermione Granger', 1), (5, 'Tyrion Lannister', 2), (6, 'Daenerys Targaryen', 2), (7, 'Argus Filch', 1);
        +--------------+--------------------+-------------+ | character_id | character_name | universe_id | +--------------+--------------------+-------------+ | 1 | Harry Potter | 1 | | 2 | Severus Snape | 1 | | 3 | Voldemort | 1 | | 4 | Hermione Granger | 1 | | 5 | Tyrion Lannister | 2 | | 6 | Daenerys Targaryen | 2 | | 7 | Argus Filch | 1 | +--------------+--------------------+-------------+
        
        JOIN
        
        [INNER] JOIN:
        
        FOR-EACH record from the left table,
        ALL records from the right table that satisfy the merge condition will be merged with the original record and added to the resulting table.
        
        IF no matching records are found in the right table,
        THEN the original record is also not added to the resulting table
        
        (using keyword INNER is optional).
        
        LEFT/RIGHT JOIN:
        
        RETURNs all the records from the left/right table and the matching values from the right/left table.
        
        IF some records in one of the tables (for example, the left one) do not have a match in the other (for example, the right one) table,
        THEN the corresponding columns in the output of this right table will be assigned NULLs.
        
        FULL [OUTER] JOIN:
        
        returns all records when there is a match in either the left or the right table.
        
        Records that do not have a match are assigned NULLs
        
        (using keyword OUTER is optional).
        
        CROSS JOIN:
        
        joins up every record from the left table and every record in the right table
        
        Note that we don’t specify the condition on which we join the two tables.
        
        UPDATE
        
        UPDATE table_name SET col1 = expr1, col2 = expr2, …, colN = expr WHERE logical_expression;
        Pay attention: during the execution of UPDATE, every row of a table is considered individually. If we want to use old value(s) to compute a new value for a cell, only cell(s) from the same row will be taken into account.
        
        2 more items...
        
        Aggregate functions
        
        INSERT INTO and SELECT
        
        INSERT INTO table1 (column_1, column_2, ..., column_n) SELECT column_1, column_2, ..., column_n FROM table2 WHERE condition;
      - DATABASE
        
        create
        
        CREATE DATABASE mydb;
        
        show all
        PostgreSQL:
        SELECT datname FROM pg_database;
        MySQL
        SHOW DATABASES;
        Microsoft:
        SELECT name FROM sys.databases;
        
        show selected
        
        SELECT current_database();
        MySQL
        SELECT DATABASE();
        
        select
        MySQL
        USE mydb;
        
        delete
        
        DROP DATABASE mydb;
        
        TABLE
        
        create
        
        CREATE TABLE weather ( city varchar(80), temp_lo int, -- low temperature temp_hi int, -- high temperature prcp real, -- precipitation date date );
        
        delete
        
        DROP TABLE weather;
        
        show all tables
        MySQL
        SHOW TABLES;
        
        Primary-Key / Primary-Key-constraint
        
        PK must be UNIQUE and NOT NULL
        
        PK-constraint specifies a set of columns with values that can help identify any table record
        
        CREATE TABLE chefs ( chef_id INT PRIMARY KEY, first_name VARCHAR(20), last_name VARCHAR(20) );
        CREATE TABLE employees ( department_id INT NOT NULL, employee_id INT NOT NULL, name varchar(50) NOT NULL, CONSTRAINT pk_employee PRIMARY KEY (department_id, employee_id) );
        
        Add PK to an existing table
        
        CREATE TABLE countries ( country_name VARCHAR(40) NOT NULL UNIQUE, population INT CHECK (population > 0), area REAL NOT NULL );
        ALTER TABLE countries ADD PRIMARY KEY (country_name);
        
        using PK-constraint
        
        ALTER TABLE students ADD CONSTRAINT pk_student PRIMARY KEY (name,birth_date);
        
        Drop PK
        
        ALTER TABLE students DROP PRIMARY KEY;
        
        Foreign-Key / Foreign-Key-constraint
        
        Note that the FOREIGN KEY constraints can only reference tables within the same database.
        
        The structure and data type of the primary key and the foreign key must be the same
        
        parent table (e.g.)
        
        CREATE TABLE departments ( department_id INT PRIMARY KEY, department_name VARCHAR(20) NOT NULL ); INSERT INTO departments (department_id, department_name) VALUES (1, 'IT'), (2, 'HR'), (3, 'PR');
        
        child table
        named
        CREATE TABLE employees ( employee_id INT PRIMARY KEY, name VARCHAR(60) NOT NULL, department_id INT, CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(department_id) );
        unnamed FK (bad practice)
        CREATE TABLE employees ( employee_id int PRIMARY KEY, name VARCHAR(60) NOT NULL, department_id INT, FOREIGN KEY (department_id) REFERENCES departments(department_id) );
        
        Referential actions
        using:
        
        ON DELETE
        
        ON UPDATE
        
        specifying different actions:
        
        CASCADE:
        
        if a row in the parent table is deleted or updated,
        all matching rows will be deleted or updated automatically;
        
        SET NULL:
        
        if a row in the parent table is deleted or updated,
        all matching foreign key values in the child table will be set to NULL;
        
        RESTRICT:
        
        if we try to update or delete a row in the parent table,
        the operation will be rejected;
        
        SET DEFAULT:
        
        if a row with the corresponding value is deleted or updated,
        the FK value in the child table will be set to the default value;
        
        NO ACTION:
        
        this keyword can mean different actions depending on a dialect.
        
        In MySQL, it is equivalent to the RESTRICT keyword,
        so if we create the table employees with one of the queries above,
        delete and update actions in the table departments will be forbidden.
        
        CREATE TABLE employees ( employee_id int PRIMARY KEY, name VARCHAR(60) NOT NULL, department_id INT, CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(department_id) ON DELETE SET NULL ON UPDATE CASCADE );
        
        Add FOREIGN KEY to the existing table
        unnamed
        ALTER TABLE employees ADD FOREIGN KEY (department_id) REFERENCES departments(department_id);
        named or FK-constraint to multiple columns
        ALTER TABLE employees ADD CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(department_id);
        If you want to create a table with multiple foreign key columns, just specify multiple columns in the parentheses. You can also add ON UPDATE and ON DELETE actions to these queries if you want to specify them.
        
        Drop foreign key
        
        ALTER TABLE employees DROP FOREIGN KEY fk_department;
        In MySQL, we have to know the name of the foreign key to delete it.
        This is one of the reasons why using named foreign keys is good practice,
        but even if you create an unnamed foreign key constraint,
        DBMS will generate the name automatically.
        To get the foreign key name in this case, use the following syntax:
        SHOW CREATE TABLE table_name;
        This query will show the autogenerated CREATE TABLE query for your table, and the foreign key name will be a part of this query.
    - - systemctl status mysql
        mysql -u root -p