MySQL Installation and Operation Basic Instructions
MySQL is an open-source database server that follows the relational database model and was created by the Swedish company MySQL AB, now owned by Oracle Corporation. MySQL is available under both a free GPL license and a commercial paid license.
This guide will provide you with a basic introduction to MySQL.
Installing MySQL
If you don't have MySQL installed on your server yet, you can install it with the following command:
Ubuntu / Debian
sudo apt-get install mysql-server
CentOS
sudo yum install mysql-server
/etc/init.d/mysqld start
Accessing MySQL
Once MySQL is installed on your server, you can access it using the following command:
mysql -u root -p
After entering your password, you will be able to start building your MySQL database.
All MySQL commands must end with a semicolon (;). If a command doesn't end with a semicolon, it will not be executed.
MySQL commands are usually written in uppercase, while database names, table names, and text are usually written in lowercase for better clarity. However, the MySQL command-line is not case-sensitive.
Creating and Deleting a Database
MySQL organizes information into databases, and each database contains tables with specific data.
You can view a list of currently available databases using:
mysql> SHOW DATABASES;
Let's create a database and name it "example" using:
mysql> create database example;
If you no longer need a database, you can delete it using:
mysql> drop database example;
Accessing a MySQL Database
Before working with tables in the database, you must first select which database you want to work with using:
mysql> use example;
Working with MySQL Tables
You can view the available tables using:
mysql> show tables;
Since we've created a brand new database, we currently have no tables, so nothing will be displayed.
Let's create a simple table for users, with columns for ID, name, last name, and age:
create table users (id int NOT NULL PRIMARY KEY AUTO_INCREMENT, name varchar(20), last_name varchar(30), age int(3));
Now, if we check for available tables, we'll see our newly created "users" table.
You can also view the structure of individual tables using:
describe users;
Let's not leave our table empty and insert data into it using:
INSERT INTO users VALUES (1, "Adam", "Nový", 52);
INSERT INTO users VALUES (2, "Kateřina", "Vítězná", 24);
INSERT INTO users VALUES (3, "Pepa", "Pracovitý", 33);
We've added 3 new users to our table.
You can display the data from the table using:
mysql> Select id, name, last_name, age FROM users;
When you see the listed data, you can update the "age" column value for the user "Adam" using:
UPDATE users SET age=53 WHERE name="Adam";
You can also use this command to add data to empty columns.
Deleting Individual Rows from the Database
If you need to delete a user from the table, you can easily do so with:
DELETE FROM users WHERE name="Pepa";
Adding Another Column to the Table
We already have a table for users, but we forgot to add a column for usernames. You can rectify this using:
ALTER TABLE users ADD login varchar(20);