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.
- Create a Database: CREATE DATABASE MOVIES;
- Chose the database you want to perform operation on: USE MOVIES;
- Check that MOVIES is now the selected Database: SELECT DATABASE(); (it should display movies)
- Now you are ready to perform operations on the Database.
| SUBJECT | MYSQL COMMAND + EXAMPLE |
|---|---|
| Create DB | CREATE DATABASE MOVIES; |
| Drop (delete) DB | DROP DATABASE MOVIES; |
| Show all DB | SHOW DATABASES; |
| use specific DB | USE MOVIES # Selects a specific database |
| Select the used database | SELECT 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 (CREATE TABLE MOVIES ( |
| INSERT DATA INTO TABLE | 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 ( |
| Show Columns from a TABLE_NAME. | Show all columns from movies tableSHOW COLUMNS FROM MOVIES; This does the same as above DESC MOVIES; |
| Select sth from a table | SELECT movie_titles FROM movies; |
| 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_NAME | Deletes 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. |
Leave a Reply