Brugbarviden

Tag: Database

  • MySQL Commands

    MySQL Commands

    MySQL Commands table

    In this example we create a Database Called Movies. So every time you see the word “Movies” it’s representing a Database.

    The first steps are to create a database and then select the database you created.

    1. Create a Database: CREATE DATABASE MOVIES;
    2. Chose the database you want to perform operation on: USE MOVIES;
    3. Check that MOVIES is now the selected Database: SELECT DATABASE(); (it should display movies)
    4. Now you are ready to perform operations on the Database.
    SUBJECT MYSQL COMMAND + EXAMPLE
    Create DB CREATE DATABASE MOVIES;
    Drop (delete) DBDROP DATABASE MOVIES;
    Show all DBSHOW DATABASES;
    use specific DB USE MOVIES
    # Selects a specific database
    Select the used databaseSELECT DATABASE();
    If you typed USE MOVIES, it will return that database. It returns the Database you decided to “USE”. When you type commands, they will apply to the database you use.
    Create a Table
    & insert Values into the table     		CREATE TABLE MOVIES (
    col 1,
    col 2,
    col 3
    col x
    )

    CREATE TABLE MOVIES (
        movie_titles VARCHAR(60),
        imdb_ratings INT
    year_release INT,
    genre VARCHAR(30),
    );

    INSERT INTO MOVIES (
    id INT PRIMARY KEY AUTO_INCREMENT,
    movie_titles,
    imdb_ratings,
    year_release,
    genre

    )
    VALUES ('WALL-E', 6,2008,'Animation'),
    ('Titanic',8, 1997, 'Historical'),
    ("Top Gun". 6, 1987, "action")



    INSERT DATA INTO TABLEWhen inserting data into a table you write 2 functions: First you specify which columns (col_name) you want to insert data into. Then below you Specify which Values you want to insert into those columns)

    INSERT INTO MOVIES (
    id INT PRIMARY KEY AUTO_INCREMENT,
    movie_titles,
    imdb_ratings,
    year_release,
    genre

    )
    VALUES ('WALL-E', 6,2008,'Animation'),
    ('Titanic',8, 1997, 'Historical'),
    ("Top Gun". 6, 1987, "action")
    Show Columns from a TABLE_NAME. Show all columns from movies table
    SHOW COLUMNS FROM MOVIES;

    This does the same as above
    DESC MOVIES;
    DESCRIBE MOVIES;
    Select sth from a tableSELECT movie_titles FROM movies;

    SELECT * FROM movies;

    SELECT movie_titles FROM movies WHERE imdb_ratings > 6;

    SEÆECT movie_titles, imdb_ratings, year_release FROM movies WHERE imdb_ratings > 6;
    SELECT EVERYTHING WHERE
    ALIAS: select a column from table and give the column header an alias when you output, SELECT movie_title AS movie_name FROM MOVIES;
    So this selects the column called “movie_title” but in the displayed result the column header gets named “movie_name” instead of “movie_title”

    EXAMPLE 2
    SELECT id AS movie_id FROM MOVIES;

    EXAMPLE 3
    SELECT imdb_ratings AS movie_ratings, title AS movie_name FROM MOVIES WHERE imdb_ratings <6;
    DROP TABLE TABLE_NAMEDeletes the table
    DROP TABLE MOVIES;
    Update a table Update the table called Movies

    UPDATE MOVIES
    SET imdb_ratings = 8
    WHERE movie_titles = “WALL-E”

    NOTE! Make sure you didn’t leave an empty space in the name when you inserted it into the TABLE. If you did, you have to write “WALL-E ” instead of “WALL-E”

    Example: Update password for users
    UPDATE users
    SET password = “New_Password”
    Where ID = 102;

    (ID is better than name, because 2 people could have the same name, but each user only has only 1 unique ID)
    Delete DELETE FROM movies
    WHERE id = 1

    This deletes the movie whose id = 1.

    DELETE * FROM movies
    This deletes the entire table (careful!)
    CHAR(10) vs VARCHAR(10) If you create a table
    CREATE TABLE users (
    name CHAR(10)
    username VARCHAR(10)
    )
    and you insert the values into

    INSERT INTO users(name, username)
    VALUES (“Thom”,”Thom123″);

    VARCHAR(10) means “up to 10 characters”
    CHAR(10) means “exactly 10 characters” so if a user types a username less that 10 characters e.g Thom123 it will be stored with 3 blank spaces “Thom123 “
    So use VARCHAR when possible!
    TIMESTAMPS If a user makes a comment or updates the comment, you need to set a timestamp for when the comment was created and when it was updated
    CREATE TABLE user_comments (
      id INT AUTO_INCREMENT PRIMARY KEY,
      username VARCHAR(25),
      comment_content VARCHAR(255),
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    );

    So the Default value for the Timestamps is the Current time.

    Table will be updated.

    REPLACE SELECT REPLACE (“Big dog”, “Big”, “Small”

    Result
    “Small dog”

    SELECT REPLACE(username, “Willie”, “James”) FROM USERS;

    This replaces any username that is Willie, with James in the table USERS.