MySQL: an Open-Source, Fast, Common Relational DBMS

Installing MySQL

Starting, Stopping the MySQL Server on Linux

Depending on your Linux distribution, you can change the state of MySQL using the service command.

To start MySQL server:

sudo service mysqld start

To stop MySQL server:

sudo service mysqld stop

To restart MySQL server:

sudo service mysqld restart

If you don't have the service command available or would prefer to make changes to MySQL using a different method, you can also use the init.d command to start/stop your MySQL server.

To start MySQL server:

sudo /etc/init.d/mysqld start

To stop MySQL server:

sudo /etc/init.d/mysqld stop

To restart MySQL server:

sudo /etc/init.d/mysqld restart

Lastly, you can also use the systemctl command to start, stop, and restart applications on Linux, including MySQL.

To start MySQL server:

sudo systemctl start mysqld

To stop MySQL server:

sudo systemctl stop mysqld

To restart MySQL server:

sudo systemctl restart mysqld

Starting, Stopping the MySQL Server on Windows

To start, you'll first need to open a terminal window. If you don't have this somewhere easily accessible, you can find it quickly using the Windows Run dialog. To open the Run dialog, just press the Windows Key + R.

Next, type in cmd and press the Enter key. This will open a new terminal window.

Once you've opened a terminal window, just type the following commands to start or stop MySQL server:

To start MySQL server:

mysqld start

To stop MySQL server:

mysqld stop

Logging In and Learning about Existing DataBases

First I log into mysql as superuser:

sudo mysql

Once I have logged in, I inquire about existing databases:

SHOW DATABASES;

Deleting, Creating and Using a Schema/DataBase

I drop a possibly existing books database, then I do create my database and get into it:

DROP SCHEMA IF EXISTS books;
CREATE SCHEMA books;
USE books;

My First Table

I create my first table:

CREATE TABLE books (
  name      TINYTEXT NOT NULL,
  rel_path  MEDIUMTEXT,
  base_path TINYTEXT,
  comment   MEDIUMTEXT
);

Then I insert a couple of books:

INSERT INTO books VALUES
  ('AreTherePeopleWithoutASelf_OnTheMysteryOfTheEgoAndTheAppearanceInThePresentDayOfEgolessIndividuals_ErdmuthJohannesGrosse_TempleLodgePublishing2022.epub',
  'humanities/RudolfSteiner/',
  '~/audiobooks/ePUB_mobi_PDF/ebooks_sda8/',
  ''),
  ('TheCompassionateUniverse_ThePowerOfTheIndividualToHealTheEnvironment_EknathEaswaran_NilgiriPress2022.epub',
  'unclassified',
  '~/audiobooks/ePUB_mobi_PDF/ebooks_sda8/',
'');

Loading Rows into Existing Tables From an External File

To load the rows in a file named acquaintances.csv into table acquaintances, we would run:

LOAD DATA INFILE '/var/lib/mysql-files/acquaintances.csv' INTO TABLE acquaintances FIELDS TERMINATED BY ',' IGNORE 1 LINES;

where the extension .csv denotes a comma-separated file format.

The following two sections explain how to write an input file, e.g. acquaintances.csv. We can do it the simple or the thorough way. Essentially, the thorough way temporarily disables some checks on consistency so that bulk insertion doesn't fail.

Writing a Very Simple Data File

This format resembles quite closely what we would write at the mysql prompt. I'll use some made-up short fields...

INSERT INTO acquaintances VALUES ('Chris', 'Ariston'), ('Emily', 'Dickinson');

Then we need to find out where our particular instance of MySQL stores such files. So we run:

SELECT @@secure_file_priv;

to learn the contents of variable secure_file_priv.

Writing a Data File the Thorough Way

This is borrowed from sakila-data.sql. After a prolog presumably meant to save some settings (note in particular that uniqueness and foreign key checks are suspended), later to be restored:

SET NAMES utf8mb4;
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
SET @old_autocommit=@@autocommit;

USE <database_name>;

Then the commands for insertions, which seem to have been printed by a dump, go like this:

--
-- Dumping data for table <mytable>
--

SET AUTOCOMMIT=0;
INSERT INTO <mytable> VALUES
  (<val>, <val>, ...),
  (<val>, <val>, ...),
  ...
  (<val>, <val>, ...);
COMMIT;

After several such table insertions, former settings are restored:

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SET autocommit=@old_autocommit;

CREATE-ing Tables

In MySQL the template is:

CREATE TABLE [IF NOT EXISTS] table_name( column_1_definition, column_2_definition, ..., table_constraints ) ENGINE=storage_engine;

The ENGINE constraint helps to define the storage engine. This is an optional constraint; if one does not know or doesn't want to specify, one can easily skip this constraint.

Widely popular storage engines are InnoDB and the MyISAM storage engine. The InnoDB storage engine is the default when a user does not explicitly declare a storage engine.

Various processes offered by the relational database management system are executed by the InnoDB storage engine, such as referential integrity, ACID transaction, and crash recovery.

CREATE TABLE my_table IF NOT EXISTS

Many RDBMSs support the IF NOT EXISTS clause of the CREATE TABLE statement, which makes it easy to create a table only when it doesn't already exist.

Example:

CREATE TABLE IF NOT EXISTS a_table ( var1 INT, var2 VARCHAR(10) );

The CREATE TABLE IF NOT EXISTS statement can be used with RDBMSs such as MySQL, MariaDB, PostgreSQL, and SQLite.

The statement was introduced in Oracle Database 23c, but it still isn't supported by SQL Server (at least not the latest version at the time of writing – SQL Server 2022) or Oracle 21c and lower.

An example:
CREATE TABLE IF NOT EXISTS bucket_list (
    bucket_id INT AUTO_INCREMENT PRIMARY KEY,
    bucket_activity_name VARCHAR(255) NOT NULL,
    start_activity_date DATE,
    due_activity_date DATE,
    current_status TINYINT NOT NULL,
    set_priority TINYINT NOT NULL,
    bucket_description TEXT,
    bucket_created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)  ENGINE=INNODB;

MySQL CREATE TABLE with a Foreign Key [and a] Primary Key

Let us dive into this second example, where we shall be CREATE TABLE with a foreign key [and a] primary key and learn how to create a table if not exists mysql.

CREATE TABLE IF NOT EXISTS assignment_list (
    assignment_id INT AUTO_INCREMENT,
    assignment_tobe_id INT,
    assignment_tobe VARCHAR(255) NOT NULL,
    assignment_is_completed BOOLEAN NOT NULL DEFAULT FALSE,
    PRIMARY KEY (assignment_id),
    FOREIGN KEY (bucket_id)
        REFERENCES bucket_list (bucket_id)
        ON UPDATE RESTRICT ON DELETE CASCADE
);