This a compilation of all the basic MySQL tutorial videos made by mmtuts, a YouTuber and a Multimedia Designer. He has a lot of Programming Tutorials in his YouTube channel, but this compilation will simply focus on Basic MySQL. After watching all the videos, we would be able to understand what MySQL is, the structure of MySQL, learn some basic commands that enable us to create tables, insert values, search the database, update entries, delete entries, and display the database results base on order using order by.
I also wrote some notes for each Tutorial. This will help us remember the basic commands and highlight some important pointers.
Index:
1. How to Create a MySQL Database
2. Create a MySQL Table in Database
3. Insert Data Into Database
4. Select Data From MySQL Database
5. Update Data From MySQL Database
6. Delete Data From MySQL Database
7. Set Data Order From MySQL Database
Video 1: How to Create a MySQL Database
This is an introduction to MySQL. It will teach us how to create a database.
Notes:
*Don’t use space or capital letters on a database name.
Video 2: Create a MySQL Table in Database
This video teaches how to create a MySQL database table.
Notes:
CREATE – is a command that instructs PHPMyAdmin to create a database table.
Syntax:
CREATE table <tablename> (
<column name> <type>(<value>) <attribute>
);
Ex:
CREATE table sampledb (
id int(11) not null PRIMARY KEY AUTO_INCREMENT,
topic varchar(128) not null,
content varchar(1000) not null,
date datetime not null
);
-The instruction means that PHPMyAdmin should create a table named sampledb.
-id is important in a database, it should always be unique. Similar values will result in a conflict.
-PRIMARY KEY makes this column as the main key or sort of a unique identifier.
-AUTO_INCREMENT means that it will add up every time a new entry is made.
-topic is a column name, so are content, and date.
-int stands for integer. It means this type should be whole numbers.
-varchar means this type is a string while (128) and (1000) in the command specifies the maximum number of characters that can be entered.
-not null checks if the entry is empty. If empty, it will not add the values into the database.
-datetime is a type that corresponds to date and time. It will force the entry to use the date and time format.
*It is also important to select a database first before creating a table, otherwise it will throw an error as it doesn’t know where to create a table.
Video 3: Insert Data Into Database
This video teaches how to insert data into a database table.
Notes:
INSERT – This command inserts data into a database table.
Syntax:
INSERT into <tablename> (<columnname>) VALUES ('<datavalue>')
Example:
INSERT into sampledb (topic, content, date) VALUES ('My Subject', 'This is my sample content', '2018-06-03 15:00:00');
-The command will insert data into the database table “sampledb”. It will insert “My Subject” to “topic” column name, and “This is my sample content” for “content” column name and the date and time for “date” column.
-There is no need to insert value for id because it was set to auto increment earlier. However, if not auto incremented then it is important to insert id no. as well.
-The table columns should correspond to the table rows. For instance (topic, content) for values (‘My Subject’, ‘This is my sample content’). It is also important to include ‘ ‘ for string values.
Video 4: Select Data From MySQL Database
This video will teach how to search data from a database.
Notes:
SELECT – A command that searches the database and returns the desired lookup value.
FROM – used along with SELECT. This specifies the table name where it will draw the record from.
Syntax:
SELECT <columnname> FROM <tablename>
Example:
SELECT topic FROM sampledb
or
SELECT * FROM sampledb
* – is a regex (regular expression) for all.
-First example searches for all the content under topic column name from sampledb.
-Second example searches the database table for all column names and their values from sampledb.
WHERE – used along with SELECT. It is sort of a condition that specifies which table column name and what value to look for.
Syntax:
SELECT <columnname> FROM <tablename> WHERE <columnname>='<value>'
Example:
SELECT * FROM sampledb WHERE id='1'
-The command will look for all items from sampledb table that has an id value of 1.
AND – used along with SELECT. It works inside WHERE. It adds another condition. It means both should be true or else it will return a not found result. If both are true, it will return the matched results and highlight the column values that matches with the searched item.
Syntax:
SELECT <columnname> FROM <tablename> WHERE <columnname1>='<value>' AND <columnname2>='<value>'
SELECT * FROM sampledb WHERE topic='My Subject' AND id='1'
-The command will look for all items from sampledb table that has a topic of My Subject and id of 1. If both conditions are met, it will return the found item, otherwise, it will show a not found result.
OR – used along with SELECT and works inside WHERE. It is similar to AND. The only difference is that while in AND, both conditions has to be true, in OR, only one condition has to be met. If either one of the two conditions are true, then it will return the found item result. If both conditions fail, it will return a not found result. If both conditions are true, then it will return the found item result.
Syntax:
SELECT <columnname> FROM <tablename> WHERE <columnname1>='<value>' OR <columnname2>='<value>'
Example:
SELECT * FROM sampledb WHERE topic='My Subject' OR id='300'
-In the example, only topic=’My Subject’ meets the condition. Still, PHPMyAdmin will return the found item result. If two conditions were meet, but matches two different rows, both rows will be displayed, else only the matched column and row will show up.
Video 5: Update Data From MySQL Database
This video teaches how to update or change entry values in a database table.
Notes:
UPDATE – This command changes the contents of the database table. Has to be accompanied by SET command. The target of this command is the database table.
SET – Works along with UPDATE. This is the command that manipulates the data inside the table. It targets the column name and its values. It has to be accompanied by UPDATE as SET has no capability to select a table.
Syntax:
UPDATE <tablename> SET <columnname>='<value>'
Example:
UPDATE sampledb SET topic='My New Subject'
*The above example will change all the values under “topic” column name to “My New Subject”. This will apply all throughout the rows. To specifically target a specific row, WHERE has to be implemented.
Example:
UPDATE sampledb SET topic='My New Subject' WHERE id='1'
-The example above will specifically target only the topic content that has an id of 1. The rest will be spared.
-Multiple columns can be updated by typing another column name and its new value. It has to be separated by a comma (,).
Example:
UPDATE sampledb SET topic='My New Subject', content='New Content' WHERE id='1'
-The above command will update sampledb and change the value of column name “topic” to “My New Subject” then change column name “content” to “New Content” while targeting only the row with the id of 1.
Video 6: Delete Data From MySQL Database
This video will teach how to delete an entry in a database table.
Notes:
DELETE – Deletes a row in the database table.
Syntax:
DELETE FROM <tablename> WHERE <columnname>='<value>'
Example:
DELETE FROM sampledb WHERE id='1'
-The example command will remove the row with the id of 1.
Video 7: Set Data Order From MySQL Database
This video will teach us how to change the order of appearance of the returned database search result items.
Notes:
ORDER BY – Has to be inserted in a SELECT function. It changes the order of the search results.
ASC – Has to be use with ORDER BY. ASC stands for Ascending. It will display the database returned search results in an ascending order (ie. 0-9 or A-Z).
DES – Has to be use with ORDER BY. DESC stands for Descending. It will display the database returned search results in the descending order (ie. 9-0 or Z-A).
Syntax:
SELECT <columnname> FROM <tablename> ORDER BY <columnname> <orderattribute>
Example:
SELECT * FROM sampledb ORDER BY id ASC
-The command will show the returned database search results in an Ascending Order. In this case id=’1-9′.
SELECT * FROM sampledb ORDER BY id DESC
-The command will show the returned database search results in the Descending Order. In this case id=’9-1′.
