[LEARN] Database [MySQL] commands and merit challenge

As you can see at the bottom part of the forum there is a logo of MySQL which use by the forum as a Database. So what is MySQL this is an open-source database that you can use for your personal projects, business projects and etc.

Today I would like to give an introduction to you about the basic knowledge regarding the MySQL
If you try to run mysql this panel will prompt you to access your database you need to click start in Apache and MySQL

Go to your web browser and type “localhost” and this will be prompt then click phpMyAdmin.

It will direct to the MySQL page that you can access your all databases and create a new one. Also, you can import and export the database through the use of the file.

To create a database click new > and type your database name, here I made one

Also you can manually type to the SQL panel to create a database

Code:

create database db_demo2

Let's say you already created a database now I will show you how does Log in to the forum works. I already created a table for users where we can store the username and password.

Primary serves as the uniqueness of the table. To easily understand. E.g ID number.

Also, you can increment the ID with the command AUTO_INCREMENT

Code:

create table tbl_accounts(
account_id int(10) AUTO_INCREMENT
);

Code:

Result:
id
1 Peanuts1
2 Peanuts1
3 Peanuts1

So how does it works, if the user inputted data is the same as the stored in the database the next action depends on the developer but in the case of our forum it will direct to the home page of the forum. Here is simple chart to understand the log-in system to our forum.

I made this on draw.io but now new domain

Code:

https://app.diagrams.net/

These are the different basic commands you can use in MySQL Database.

Legends
* - means "all"/ all data or information

SELECT COMMAND - used if you want to select a specific data
SELECT * FROM [table name]
Syntax:

Output:

WHERE COMMAND - used if you are looking a data but with condition
SELECT [column name] FROM [table name] WHERE [condition]
Syntax:

Output:

LOGICAL OPERATORS COMMAND

AND - SELECT [column name] FROM [table name] WHERE [condition] AND [condition]
OR - SELECT [column name] FROM [table name] WHERE [condition] OR[condition]
NOT - SELECT [column name] FROM [table name] WHERE [condition] NOT IN [condition]

LIKE COMMAND - LIKE command is commonly used if you are looking for a similar letter or word.

Note :
% - used for looking a data and neglect the number of the character after the letter chosen.
_ - used for a single character

SELECT [column name] FROM [table name] WHERE [condition] LIKE [pattern]
Syntax (%):

Output:

Syntax (_):

Output:

INSERT COMMAND - used if you want to insert a data
INSERT INTO [table name] (column name) VALUES (values1)

Syntax:

Output:

So here comes the challenge anyone can answer but the only one who can receive merits is just member rank and below. I still have the availability of 5 sMerits. I have given data here all you need is to give the output for the corresponding syntax. The data use is just for the challenge not to intended to give any issue.

CHALLENGE #1
SELECT * FROM tbl_accounts WHERE account_id = 2

SELECT account_password FROM tbl_accounts WHERE account_username LIKE '%c%'

SELECT account_password FROM tbl_accounts WHERE account_password LIKE '%n'

SELECT account_username FROM tbl_accounts WHERE account_username LIKE '%a%'

SELECT account_password FROM tbl_accounts WHERE account_password LIKE '%2'

SELECT account_username FROM tbl_accounts WHERE account_username LIKE '%1'

Every time a member answered the given question a new set of question/query/command will be drop on this thread.

You can use these for more information

Code:

https://www.mysqltutorial.org/mysql-cheat-sheet.aspx
https://www.javatpoint.com/mysql-commands-cheat-sheet
https://www.w3schools.com/sql/default.asp