{"id":197,"date":"2025-05-28T17:44:11","date_gmt":"2025-05-28T17:44:11","guid":{"rendered":"https:\/\/brugbarviden.dk\/?p=197"},"modified":"2025-05-29T13:43:43","modified_gmt":"2025-05-29T13:43:43","slug":"mysql-commands","status":"publish","type":"post","link":"https:\/\/brugbarviden.dk\/?p=197","title":{"rendered":"MySQL Commands"},"content":{"rendered":"\n

MySQL Commands table <\/h2>\n\n\n\n

In this example we create a Database Called Movies. So every time you see the word “Movies” it’s representing a Database. <\/p>\n\n\n\n

The first steps are to create a database and then select the database you created. <\/p>\n\n\n\n

    \n
  1. Create a Database: CREATE DATABASE MOVIES; <\/li>\n\n\n\n
  2. Chose the database you want to perform operation on: USE MOVIES; <\/li>\n\n\n\n
  3. Check that MOVIES is now the selected Database: SELECT DATABASE(); (it should display movies) <\/li>\n\n\n\n
  4. Now you are ready to perform operations on the Database. <\/li>\n<\/ol>\n\n\n\n
    SUBJECT <\/th>MYSQL COMMAND + EXAMPLE <\/th><\/tr><\/thead>
    <\/td><\/td><\/tr>
    <\/td><\/td><\/tr>
    Create DB <\/td>CREATE DATABASE MOVIES;<\/code><\/td><\/tr>
    Drop (delete) DB<\/td>DROP DATABASE MOVIES;<\/code><\/td><\/tr>
    Show all DB<\/td>SHOW DATABASES;<\/code><\/td><\/tr>
    use specific DB <\/td>USE MOVIES <\/code>
    # Selects a specific database<\/td><\/tr>
    Select the used database<\/td>SELECT DATABASE(); <\/code>
    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. <\/td><\/tr>
    Create a Table
    & insert Values into the table <\/td>    		CREATE TABLE MOVIES (
    col 1,
    col 2,
    col 3
    col x
    ) <\/code>

    CREATE TABLE MOVIES (
    \u00a0\u00a0\u00a0\u00a0movie_titles VARCHAR(60),
    \u00a0\u00a0\u00a0\u00a0imdb_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\")
    <\/code>


    <\/td><\/tr>
    INSERT DATA INTO TABLE<\/td>When 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\")<\/code>
    <\/td><\/tr>
    Show Columns from a TABLE_NAME. <\/td>Show all columns from movies table
    SHOW COLUMNS FROM MOVIES; <\/code>

    This does the same as above
    DESC MOVIES;
    DESCRIBE MOVIES; <\/code><\/td><\/tr>
    Select sth from a table<\/td>SELECT movie_titles FROM movies;

    SELECT * FROM movies;

    SELECT movie_titles FROM movies WHERE imdb_ratings > 6;

    SE\u00c6ECT movie_titles, imdb_ratings, year_release FROM movies WHERE imdb_ratings > 6;<\/code><\/td><\/tr>
    SELECT EVERYTHING WHERE <\/td><\/td><\/tr>
    ALIAS:<\/strong> select a column from table and give the column header an alias when you output, <\/td>SELECT movie_title AS movie_name<\/strong> FROM MOVIES; <\/code>
    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;<\/code>

    EXAMPLE 3
    SELECT imdb_ratings AS movie_ratings, title AS movie_name FROM MOVIES WHERE imdb_ratings <6;<\/code><\/td><\/tr>
    DROP TABLE TABLE_NAME<\/td>Deletes the table
    DROP TABLE MOVIES; <\/td><\/tr>
    Update a table <\/td>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) <\/td><\/tr>
    Delete <\/td>DELETE FROM movies
    WHERE id = 1

    This deletes the movie whose id = 1.

    DELETE * FROM movies
    This deletes the entire table (careful!)
    <\/td><\/tr>
    CHAR(10) vs VARCHAR(10) <\/td>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!
    <\/td><\/tr>
    TIMESTAMPS <\/td>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 (
    \u00a0\u00a0id INT AUTO_INCREMENT PRIMARY KEY,
    \u00a0\u00a0username VARCHAR(25),
    \u00a0\u00a0comment_content VARCHAR(255),
    \u00a0\u00a0created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    \u00a0\u00a0updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    );

    So the Default value for the Timestamps is the Current time.
    <\/td><\/tr>
    <\/td><\/td><\/tr>
    <\/td><\/td><\/tr>
    <\/td><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

    Table will be updated. <\/p>\n\n\n\n

    REPLACE <\/td>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. <\/td><\/tr>
    <\/td><\/td><\/tr>
    <\/td><\/td><\/tr>
    <\/td><\/td><\/tr>
    <\/td><\/td><\/tr><\/tbody><\/table><\/figure>\n","protected":false},"excerpt":{"rendered":"

    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. SUBJECT MYSQL COMMAND + EXAMPLE Create DB CREATE DATABASE MOVIES; Drop (delete) DB DROP DATABASE MOVIES; […]<\/p>\n","protected":false},"author":1,"featured_media":201,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[33,6,1],"tags":[32,31],"class_list":["post-197","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-databases","category-it","category-uncategorized","tag-database","tag-mysql"],"jetpack_featured_media_url":"https:\/\/brugbarviden.dk\/wp-content\/uploads\/2025\/05\/mysql-commands.jpg","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/brugbarviden.dk\/index.php?rest_route=\/wp\/v2\/posts\/197","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/brugbarviden.dk\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/brugbarviden.dk\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/brugbarviden.dk\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/brugbarviden.dk\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=197"}],"version-history":[{"count":13,"href":"https:\/\/brugbarviden.dk\/index.php?rest_route=\/wp\/v2\/posts\/197\/revisions"}],"predecessor-version":[{"id":212,"href":"https:\/\/brugbarviden.dk\/index.php?rest_route=\/wp\/v2\/posts\/197\/revisions\/212"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/brugbarviden.dk\/index.php?rest_route=\/wp\/v2\/media\/201"}],"wp:attachment":[{"href":"https:\/\/brugbarviden.dk\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=197"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/brugbarviden.dk\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=197"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/brugbarviden.dk\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=197"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}