Please enable JavaScript.

Coggle requires JavaScript to display documents.

SQL - Coggle Diagram

- - - - DROP DATABASE name_of_DB;
      - BACK UP DATABASE
        
        BACK UP DATABASE name_of_DB TO DISK = 'disk_name';
        
        BACKUP DATABASE name_of_DB TO DISK = 'disk_name' WITH DIFFERENTIAL;
      - SHOW DATABASES name_of_DB
    - - CREATE TABLE table_name ( column_1 datatype, column_2 datatype, column_3 datatype);
        
        Column parameters = names of the column
        
        Datatype parameters = the data type the column holds
        
        SQL INSERT INTO STATEMENTS for filling the empty tables
      - CREATE TABLE USING ANOTHER TABLE
        
        CREATE TABLE new_table_name AS SELECT column_1, column_2 FROM existing table;
      - DROP TABLE STATEMENTS
        
        DROP TABLE table_name;
        
        Drops an existing table in the database
        
        TRUNCATE TABLE table_name;
        
        Used to delete the data inside the table but not the table itself
      - ALTER TABLE STATEMENTS
        
        ADD COLUMN
        
        ALTER TABLE table_name ADD column_name datatype;
        
        DROP COLUMN
        
        ALTER TABLE table_name DROP COLUMN
        
        RENAME COLUMN
        
        ALTER TABLE table_name RENAME COLUMN new_name TO old_name;
        
        ALTER/MODIFY DATATYPE
        
        Syntax depends on which SQL is being used
      - SQL CONSTRAINTS
        
        Constrains can be done on table or column level.
        
        Constraints can be created with CREATE TABLE statements, MANUALLY in the server, or during ALTER TABLE statements
        
        Common constraints
        
        NOT NULL
        
        Forces the column to contain values and not accept null values
        
        UNIQUE
        
        Forces the column to have unique values (no repetition)
        
        One column
        
        Multiple columns or
        
        Constraint can be dropped
        
        PRIMARY KEY
        
        Must be UNIQUE values and cannot be NULL values
        
        Table can have only one
        
        Constraint can be dropped
        
        FOREIGN KEY
        
        Is a field or collection of fields which refers to the primary key in another table
        
        Constraint can be dropped
        
        CHECK
        
        It only only allow certain values for this column
        
        Constraint can be dropped
        
        DEFAULT
        
        Used to set default value for a column
        
        Will be added to the new records if not specified
        
        Can be used to put system values with GETDATE()
        
        Constraint can be dropped
    - - SELECT
        
        SELECT * FROM table_name;
        
        SELECT column_1, column_2 … FROM table_name;
      - SELECT DISTINCT
        
        Returns only different values
      - INSERT INTO
        
        All columns
        
        INSERT INTO table_name VALUES (value_1, value_2);
        
        Specified columns
        
        INSERT INTO table_name (column_1, column_2) VALUES (value_1, value_2);
      - UPDATE
        
        Modifies existing records in a table
        
        UPDATE table_name SET column_1 = value_1, column_2 = value_2 WHERE condition;
        
        REMEMBER THE WHERE CLAUSE OR ELSE IT WILL MODIFY THE ENTIRE TABLE
      - DELETE
        
        Deletes already existing records in a table
        
        DELETE FROM table_name WHERE condition;
        
        REMEMBER THE WHERE CLAUSE OR ELSE IT WILL DELETE THE ENTIRE TABLE
        
        You can delete the table records without removing the table structure, attributes and indexes
        
        DELETE FROM table_name;
    - - WHERE
        
        SELECT column_1, column_2 FROM table_name WHERE condition;
        
        OPERATORS
        
        LIKE OPERATOR (Used with WHERE clause to look for patterns)
        
        Wildcards
        
        BETWEEN (Returns range requested)
        
        NOT BETWEEN
        
        IN OPERATOR
        
        NOT IN
        
        UNION OPERATOR
        
        This operator is used to combine the result-set of two or more SELECT statements
        
        UNION
        
        1 more item...
        
        UNION ALL
        
        1 more item...
        
        Can be used with select operators
      - LIMIT
        
        Limit how many records to return for performance reasons
        
        SELECT column_name FROM table_name WHERE condition LIMIT number;
        
        OFFSET
        
        Aids you to choose where to start the limitation
        
        SELECT FROM table_name LIMIT number OFFSET number;
      - JOINS
        
        KEYWORD
        
        INNER JOIN
        
        Selects the record that match in tables
        
        SELECT column_name FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
        
        LEFT JOIN
        
        Selects all the records from the right table and matching ones in left table
        
        SELECT column_name FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;
        
        RIGHT JOIN
        
        Returns all the records from the right table (table 2) and matching ones from the left table (table 1)
        
        SELECT column_name FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;
        
        CROSS JOIN
        
        Returns all the records from both tables whether the records match to not
        
        SELECT column_name FROM table1 CROSS JOIN table2;
    - - ORDER BY
        
        Default sorts in ascending order
        
        SELECT column_1, column_2 FROM table_name ORDER BY column_1, column_2
        
        Results can be sorted in descending order
        
        SELECT column_1, column_2 FROM table_name ORDER BY column_1, column_2 DESC
    - - MIN()
      - MAX()
      - AVG()
      - SUM()
      - COUNT()