DEV Community 👩‍💻👨‍💻

Cover image for MySQL
jimenezfede
jimenezfede

Posted on

MySQL

Short for Structured Query Language, is a language for handling data in a relational database management system (RDBMS) like MySQL, Oracle, Postgres, etc. So think of a spreadsheet going super Saiyan by having the ability to sort out all the columns and rows in any combination possible really fast.

SYNTAX
Its common practice to use all capital letters when writing SQL commands. And to end statements with a semicolon. The capital letters are more for readability so that it's easy to distinguish when SQL commands are being used. But the semicolon is required in some RDBMS like MySQL. The reason is, a command may run more than one line, and so a semicolon is used to establish the end of the command.

NAVIGATING
Just navigating through databases is a learning process. Because whenever one of these databases is started, it just shows a blank prompt. There is a help command that lists a lot of helpful commands. The command show databases will list all the databases if any. Then the command use followed by the name of the database name selects that database. Once using a database, show tables will list all the tables. Then another useful command is describe which will show how the table is made.

w3schools has these commands as the most common ones

  • SELECT
  • UPDATE
  • DELETE
  • INSERT INTO
  • CREATE DATABASE
  • ALTER DATABASE
  • CREATE TABLE
  • ALTER TABLE
  • DROP TABLE
  • CREATE INDEX
  • DROP INDEX

CREATING
Creating a database is as simple as just using CREATE DATABASE dc_universe. But creating a table takes a little more. With creating a table, the table's name, column name, and the datatype of the values going into that column. There are a lot of datatypes to choose from. But for this, I will just use int (integer) and varchar(variable character). Varchar takes in a number representing the limit of characters for it. 255 is commonly used.

CREATE TABLE superheroes(
ID int,
Alias varchar(10),
firstName varchar(10),
lastName varchar(10)
);
Enter fullscreen mode Exit fullscreen mode

Creating table

INSERTING
Then to add to the table, use the insert into keyword followed by the name of the table, then the keyword with the information to add.

INSERT INTO superheroes VALUES (1, 'Batman', 'Bruce', 'Wayne');
INSERT INTO superheroes VALUES (2, 'Superman', 'Clark', 'Kent');
INSERT INTO superheroes VALUES (3, 'Wonder Woman', 'Diana', 'Prince');
Enter fullscreen mode Exit fullscreen mode

SELECTING
From here, to see the actual values in the table, the SELECT and FROM keywords are needed. SELECT chooses the column. FROM picks the table. 
SELECT Alias FROM superheroes; will list just the alias name.
SELECT * FROM superheroes; will list all the columns in the table.

Justice League

DELETING
I actually made a mistake adding batman to the table while practicing. I forgot to add to the ID column. Thankfully, deleting is pretty easy to do.

Oops

DELETE FROM superheroes
WHERE ID is NULL;
Enter fullscreen mode Exit fullscreen mode

WHERE filters out the table by the criteria. Here I'm filtering out where the ID doesn't have a value since I forgot to give it a value.

Using SQL can seem tedious when just starting to use it. I myself kept getting error messages from using the wrong syntax to trying to access tables that don't exist. But like anything else, just takes practice.

Sources

Top comments (0)

🌚 Friends don't let friends browse without dark mode.

Good news! You can update to dark mode in your DEV settings.