This article is applicable to MySQL 8.0, which is the successor of MySQL 5.7, where 5 was dropped?! Show
Introduction to Relational Database and SQLRelational DatabasesA relational database organizes data in tables. A table has rows (or records) and columns (or fields). Tables are related based on common columns to eliminate data redundancy and ensure data integrity. Popular Relationship Database Management System (RDBMS) includes:
Structure Query Language (SQL)A high-level programming language, called Structure Query Language (SQL), is designed for
interacting with the relational databases. SQL defines a set of commands, such as Edgar F. Codd of IBM proposed the Relational Database Model in 1970. SQL, one of the earlier programming language, was subsequently developed by Donald D. Chamberlin and Raymond F. Boyce at IBM in the early 1970s. Oracle, subsequently, took it to a new height. ANSI (American National Standard Institute) established the first SQL standard in 1986 (SQL-86 or SQL-87) - adopted by ISO/IEC as "ISO/IEC 9075" - followed in 1989 (SQL-89), 1992 (SQL-92 or SQL2), 1999 (SQL-99 or SQL3), 2003 (SQL:2003), 2006 (SQL:2006), 2011 (SQL:2011) and 2016 (SQL:2016). However, most of the database vendors have their own directs, e.g., PL/SQL (Oracle), Transact-SQL (Microsoft, SAP), PL/pgSQL (PostgreSQL). SQL By ExamplesA relational database system organizes data in the following hierarchy:
Suppose we have a database called Database: studentdb Table: class101 +-----------+--------------------+-------------+ | id (INT) | name (VARCHAR(50)) | gpa (FLOAT) | +-----------+--------------------+-------------+ | 1001 | Tan Ah Teck | 4.5 | | 1002 | Mohammed Ali | 4.8 | | 1003 | Kumar | 4.8 | | 1004 | Kevin Jones | 4.6 | +-----------+--------------------+-------------+ SQL (Structure Query Language) defines a set of intuitive commands (such as SELECTSELECT column1, column2, ... FROM tableName WHERE criteria SELECT * FROM tableName WHERE criteria SELECT name, gpa FROM class101 +--------------+------+ | name | gpa | +--------------+------+ | Tan Ah Teck | 4.5 | | Mohammed Ali | 4.8 | | Kumar | 4.8 | | Kevin Jones | 4.6 | +--------------+------+ SELECT * FROM class101 +------+--------------+------+ | id | name | gpa | +------+--------------+------+ | 1001 | Tan Ah Teck | 4.5 | | 1002 | Mohammed Ali | 4.8 | | 1003 | Kumar | 4.8 | | 1004 | Kevin Jones | 4.6 | +------+--------------+------+ SELECT name, gpa FROM class101 WHERE gpa >= 4.7 +--------------+------+ | name | gpa | +--------------+------+ | Mohammed Ali | 4.8 | | Kumar | 4.8 | +--------------+------+ SELECT name, gpa FROM class101 WHERE name = 'Tan Ah Teck' +-------------+------+ | name | gpa | +-------------+------+ | Tan Ah Teck | 4.5 | +-------------+------+ SELECT name FROM class101 WHERE name LIKE 'k%' +-------------+ | name | +-------------+ | Kumar | | Kevin Jones | +-------------+ SELECT * FROM class101 WHERE gpa > 4 AND name LIKE 'k%' ORDER BY gpa DESC, name ASC +------+-------------+------+ | id | name | gpa | +------+-------------+------+ | 1003 | Kumar | 4.8 | | 1004 | Kevin Jones | 4.6 | +------+-------------+------+ DELETEDELETE FROM tableName WHERE criteria
DELETE FROM class101
DELETE FROM class101 WHERE id = 33
INSERTINSERT INTO tableName VALUES (firstColumnValue, ..., lastColumnValue) INSERT INTO tableName (column1, column2, ...) VALUES (value1, value2, ...) INSERT INTO class101 VALUES (1001, 'Tan Ah Teck', 4.5) INSERT INTO class101 (name, gpa) VALUES ('Peter Jones', 4.55) UPDATEUPDATE tableName SET column = value WHERE criteria
UPDATE class101 SET gpa = 5.0
UPDATE class101 SET gpa = gpa + 1.0 WHERE name = 'Tan Ah Teck' CREATE TABLECREATE TABLE tableName (column1Name column1Type, column2Name column2Type, ...)
CREATE TABLE class101 (id INT, name VARCHAR(50), gpa FLOAT) DROP TABLEDROP TABLE tableName
DROP TABLE class101 Notes:
Introduction to MySQL Relational Database Management System (RDBMS)SQL is a programming language for interacting with relational databases. On the other hand, MySQL is a software - a Relational Database Management System. MySQL is one of the most used, industrial-strength, open-source and free Relational Database Management System (RDBMS). MySQL was developed by Michael "Monty" Widenius and David Axmark in 1995. It was owned by a Swedish company called MySQL AB, which was bought over by Sun Microsystems in 2008. Sun Microsystems was acquired by Oracle in 2010. MySQL is successful, not only because it is free and open-source (there are many free and open-source databases, such as PostgreSQL, Apache Derby (Java DB), mSQL (mini SQL), SQLite and Apache OpenOffice's Base), but also for its speed, ease of use, reliability, performance, connectivity (full networking support), portability (run on most OSes, such as Unix, Windows, macOS), security (SSL support), small size, and rich features. MySQL supports all features expected in a high-performance relational database, such as transactions, foreign key, replication, subqueries, stored procedures, views and triggers. MySQL is often deployed in a LAMP (Linux-Apache-MySQL-PHP), WAMP (Windows-Apache-MySQL-PHP), or MAMP (macOS-Apache-MySQL-PHP) environment. All components in LAMP is free and open-source, inclusive of the Operating System. The mother site for MySQL is https://www.mysql.com. The ultimate reference for MySQL is the "MySQL Reference Manual", available at https://dev.mysql.com/doc. The reference manual is huge - the PDF has over 3700 pages!!! MySQL operates as a client-server system over TCP/IP network. The server runs on a machine with an IP address on a chosen TCP port number. The default TCP port number for MySQL is 3306. Users can access the server via a client program, connecting to the server at the given IP address and TCP port number. MariaDBExtracted from Wiki: MariaDB is a community-developed, commercially supported fork of the MySQL relational database management system (RDBMS), intended to remain free and open-source software under the GNU General Public License. Development is led by some of the original developers of MySQL, who forked it due to concerns over its acquisition by Oracle Corporation in 2009. How to Install MySQL 8.0 and Get Started with SQL ProgrammingI want you to install MySQL on your own machine, because I want you to learn how to install, customize and operate complex industrial software system. Installation could be the hardest part in this exercise. Step 0: Create a directory to keep all your worksIMPORTANT: Before getting started, check that you have a few GBs of Free Spaces. Create a directory to keep all your works called:
c: cd \ mkdir myWebProject cd mkdir myWebProject Use your graphical interface, e.g., File Explorer (Windows), or Finder (macOS) to verify this directory. (Of course you can use your graphical interface to create this directory!) For novices: It is important to follow this step. Otherwise, you will be out-of-sync with this article and will not be able to find your files later. Step 1: Download and Install MySQLFor Windows
For macOS Notes: The latest version of MySQL (8.0.28) works with macOS Big Slur (11) and Monterey (12). If you are running older version of macOS, you may need to find an archived version of MySQL @ https://dev.mysql.com/downloads/ ⇒ Archive.
I shall assume that MySQL is installed in directory Step 3: Start the "Server"The MySQL is a client-server system. The database is run as a server application. Users access the database server via a client program, locally or remotely thru the network, as illustrated:
The
programs Startup ServerFor Windows To start the database server, launch a new CMD shell: c: cd \myWebProject\mysql\bin mysqld --console ...... ...... XXXXXX XX:XX:XX [Note] mysqld: ready for connections. Version: '8.0.xx' socket: '' port: 3306 MySQL Community Server (GPL) Note: The For macOS The EASY WAY: Via graphical control. Click "Apple" Icon ⇒ System Preferences ⇒ MySQL ⇒ Start or Stop. The MySQL database server is now started, and ready to handle clients' requests. Anything that can possibly go wrong, does! Read "How to Debug". Shutdown ServerFor Windows The quickest way to shut down the database server is to press Ctrl-C to initiate a normal shutdown. DO NOT KILL the server via the window's CLOSE button. Observe these messages from the MySQL server console: XXXXXX XX:XX:XX [Note] mysqld: Normal shutdown ...... XXXXXX XX:XX:XX InnoDB: Starting shutdown... XXXXXX XX:XX:XX InnoDB: Shutdown completed; log sequence number 0 44233 ...... XXXXXX XX:XX:XX [Note] mysqld: Shutdown complete (You may need to press ENTER to get the command prompt?!) For macOS The EASY WAY: Via the graphical control. Click "Apple" Icon ⇒ System Preferences ⇒ MySQL ⇒ Stop. WARNING: You should properly shutdown the MySQL server. Otherwise, you might corrupt the database and might have problems restarting it. BUT, if you encounter problem shutting down the server normally, you may kill the " Step 4: Start a "Client"Recall that the MySQL is a client-server system. Once the server is started, one or more clients can be connected to the database server. A client could be run on the same machine (local client); or from another machine over the network (remote client). To login to the MySQL server, you need to provide a username and password.
During the installation, MySQL creates a superuser called " The MySQL installation provides a command-line client program called " Let's start a command-line client with the superuser " First, make sure that the server is running. See previous step to re-start the server if it has been shutdown. For Windows Start Another NEW CMD shell to run the client (You need to keep the CMD that run the server): c: cd \myWebProject\mysql\bin mysql -u root -p Enter password: // Enter the root's password set during installation. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 8.0.xx Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> For macOS Open a NEW "Terminal" and issue these commands to start a MySQL client with superuser cd /usr/local/mysql/bin ./mysql -u root -p Enter password: // Enter the root's password given during installation. You will NOT any * for maximum security Welcome to the MySQL monitor. Commands end with ; or \g. ...... mysql> (Skip Unless...) Read "How to Debug". Step 5: Change the Password for the Superuser "root"As mentioned earlier, the MySQL installation creates a superuser called " Notes: If you get stuck entering a command, press Ctrl-C to abort the current command. Changing the Password for "root"Let's continue with our client session started earlier. mysql> alter user 'root'@'localhost' identified by 'xxxx';
Query OK, 0 rows affected (0.00 sec)
mysql> select Host, User, authentication_string from mysql.user;
+-----------+------------------+---------------------------+
| Host | User | authentication_string |
+-----------+------------------+---------------------------+
| localhost | root | $A$005.... |
| .... | .... | ........ |
+-----------+------------------+---------------------------+
mysql> quit
Bye Re-Start a Client as "root" with the New PasswordWe have just changed the password for For Windows c:
cd \myWebProject\mysql\bin
mysql -u root -p
Enter password:
Welcome to the MySQL monitor.
......
mysql>
For macOS cd /usr/local/mysql/bin
./mysql -u root -p
Enter password:
Welcome to the MySQL monitor.
......
mysql>
Step 6: Create a New UserThe superuser "root" is privileged, which is meant for database administration and is not meant for operational. We shall create a new user - let's call it " mysql -u root -p
./mysql -u root -p
mysql> create user 'myuser'@'localhost' identified by 'xxxx';
Query OK (0.01 sec)
mysql> grant all on *.* to 'myuser'@'localhost';
Query OK (0.01 sec)
mysql> quit Explanation
Step 7: Create a new Database, a new Table in the Database, Insert Records, Query and UpdateRecall that the MySQL server organizes data in the following hierarchy:
Let's create a database called " CAUTION: Programmers don't use blank and special characters in NAMES (database names, table names, column names). It is either not supported, or will pose you many more challenges. Tips on Client's Session (Come Back to this Section If You Get Stuck in Running Command)Before we proceed, here are some tips on using the client:
SQL ProgrammingLet's start a client with our newly-created user " mysql -u myuser -p ./mysql -u myuser -p mysql> create database if not exists studentdb; Query OK, 1 row affected (0.08 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | ...... | | studentdb | | ...... | +--------------------+ x rows in set (0.07 sec) mysql> use studentdb; Database changed mysql> drop table if exists class101; Query OK, 0 rows affected, 1 warning (0.15 sec) mysql> create table class101 (id int, name varchar(50), gpa float); Query OK, 0 rows affected (0.15 sec) mysql> show tables; +---------------------+ | Tables_in_studentdb | +---------------------+ | class101 | +---------------------+ 1 row in set (0.00 sec) mysql> describe class101; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | gpa | float | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.04 sec) mysql> insert into class101 values (11, 'Tan Ah Teck', 4.8); Query OK, 1 row affected (0.03 sec) mysql> insert into class101 values (22, 'Mohamed Ali', 4.9); Query OK, 1 row affected (0.03 sec) mysql> select * from class101; +----+-------------+------+ | id | name | gpa | +----+-------------+------+ | 11 | Tan Ah Teck | 4.8 | | 22 | Mohamed Ali | 4.9 | +----+-------------+------+ 2 rows in set (0.00 sec) mysql> select name, gpa from class101 where gpa > 4.85; +-------------+------+ | name | gpa | +-------------+------+ | Mohamed Ali | 4.9 | +-------------+------+ 1 rows in set (0.00 sec) mysql> update class101 set gpa = 4.4 where name = 'Tan Ah Teck'; Query OK, 1 row affected (0.05 sec) Exercises:
(Skip Unless... ) Read "How to Debug". More Exercises
Many-to-many RelationshipIn a bookstore, a book is written by one or more authors; an author may write zero or more books. This is known as a many-to-many relationship. It is IMPOSSIBLE to capture many-to-many relationship in a SINGLE table (or one spreadsheet) with a fixed number of columns, without duplicating any piece of information! For example, if you organize the data in the table below, you will not know how many author columns to be used; and you need to repeat all the data for repeating authors. The many-to-many relationship between books and authors can be modeled with 3 tables, as shown below. A Exercises
(Optional) Backup and Restore DatabasesBackup via "mysqldump" Utility ProgramYou can use the For example, the following command backups the entire " For Windows c: cd \myWebProject\mysql\bin mysqldump -u myuser -p --databases studentdb > "c:\myWebProject\backup_studentdb.sql" For macOS cd /usr/local/mysql/bin ./mysqldump -u myuser -p --databases studentdb > ~/myWebProject/backup_studentdb.sql Study the output file, which contains Restore via "source" command in a mysql clientYou can restore from the backup by running the
" For Windows c: cd \myWebProject\mysql\bin mysql -u myuser -p mysql> drop database if exists studentdb; mysql> source c:\myWebProject\backup_studentdb.sql For macOS cd /usr/local/mysql/bin ./mysql -u myuser -p mysql> drop database if exists studentdb; mysql> source ~/myWebProject/backup_studentdb.sql Summary of Frequently-Used Commands(For Windows) Starting MySQL Server and Clientcd path-to-mysql-bin mysqld --console Ctrl-c cd path-to-mysql-bin mysql -u username -p (For macOS) Starting MySQL Server and Clientcd /usr/local/mysql/bin ./mysql -u username -p Frequently-used MySQL CommandsMySQL commands are NOT case sensitive. ; \c DROP DATABASE databaseName; DROP DATABASE IF EXISTS databaseName; CREATE DATABASE databaseName; CREATE DATABASE IF NOT EXISTS databaseName; SHOW DATABASES; USE databaseName DROP TABLE tableName; DROP TABLE IF EXISTS tableName; CREATE TABLE tableName (column1Definition, column2Definition, ...); CREATE TABLE IF NOT EXISTS tableName (column1Definition, column2Definition, ...); SHOW TABLES; DESCRIBE tableName; DESC tableName; INSERT INTO tableName VALUES (column1Value, column2Value,...); INSERT INTO tableName (column1Name, ..., columnNName) VALUES (column1Value, ..., columnNValue); DELETE FROM tableName WHERE criteria; UPDATE tableName SET columnName = expression WHERE criteria; SELECT column1Name, column2Name, ... FROM tableName WHERE criteria ORDER BY columnAName ASC|DESC, columnBName ASC|DESC, ...; SOURCE full-Path-Filename (Skip Unless...) How to Debug?"Everything that can possibly go wrong will go wrong." The most important thing to do is to find the ERROR MESSAGES!!! "VCRUNTIME140_1.dll was not found" when Running the Initialization for WindowsCAUSE: You do not have the required "Microsoft Visual C++ redistributable runtime". SOLUTION: Goto "The latest supported Visual C++ downloads" @
https://support.microsoft.com/en-gb/help/2977003/the-latest-supported-visual-c-downloads ⇒ Download "x64: Cannot Start the MySQL Server after InstallationFirst of all, check if you have already started an instance of MySQL Server:
SYMPTOM: Cannot start mysql server
ERROR MESSAGES:
xxxxxx [InnoDB] The innodb_system data file 'ibdata1' must be writable
xxxxxx [InnoDB] The innodb_system data file 'ibdata1' must be writable
xxxxxx [Server] Failed to initialize DD Storage Engine
xxxxxx [Server] Data Dictionary initialization failed.
xxxxxx [Server] Aborting
PROBABLE CAUSES: A MySQL server has already started holding on to the databases
POSSIBLE SOLUTIONS: Shutdown or Kill the current server, before starting a new one.
"Permission Denied" in creating temp files under macOSNo more disk space?! Cannot Start the "mysql" ClientSYMPTOM: Cannot start mysql client ERROR MESSAGE: error 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061) PROBABLE CAUSES: 1. MySQL Server is NOT started, or 2. The client was connecting to the wrong port number POSSIBLE SOLUTIONS: 1. Check to make sure that the MySQL server has been started. Note down the server's port number from the server's console. 2. Run a client with command "mysql -u root --port=xxxx" to specify the server's port number manually. 3. Check "my.ini", make sure that you have a [client] section with port=xxxx. SYMPTOM: Cannot start mysql client ERROR MESSAGE: error 2005 (hy000) unknown mysql server host 'localhost' PROBABLE CAUSES: Somehow your localhost is not bound to 127.0.0.1 POSSIBLE SOLUTIONS: 1. Try "ping localhost" to check if "localhost" exists. 2. (Windows) Check "C:\Windows\System32\drivers\etc\hosts" file. There should be an entry: 127.0.0.1 localhost Remove all the other localhost entries, if any. Error Using the "mysql" ClientERROR MESSAGE: Stuck at entering SQL commands PROBABLE CAUSES: Syntax errors in the current command POSSIBLE SOLUTIONS: 1) Abort the current command pressing Ctrl-C 2) Abort the current command using \c a) Enter \c if the prompt is -> or mysql> b) Enter '\c if the prompt is '> c) Enter "\c if the prompt is "> ERROR MESSAGE: error 1046 (3D000): No database selected PROBABLE CAUSES: The default database is not set POSSIBLE SOLUTIONS: 1) Issue command "use database" to set the default database, or 2) Use the fully-qualified name in the form of "databaseName.tableName". ERROR MESSAGE: error 1005 (HY000): Can't create table '...' (errno: 150) PROBABLE CAUSES: A foreign key references a parent table's column which is not indexed. Create index for that column in the parent table. ERROR MESSAGE: ERROR 1396 (HY000): Operation CREATE USER failed for 'myuser'@'localhost' PROBABLE CAUSES: This user already created! SYMPTOM: Logical error in comparing floating point numbers for equality. For example, "SELECT * FROM class101 WHERE gpa = 4.4" yields empty set although there is a record with gpa=4.4. PROBABLE CAUSES: "gpa" has the type of FLOAT. Floating point numbers are not stored "accurately". POSSIBLE SOLUTION: Do not compare two floating point number for equality. Instead, specify a range, e.g., "gpa > 3.9 AND gpa < 4.1" Link to MySQL References & Resources Latest version tested: MySQL Community Server 8.0.28, Windows 10, macOS 11, Ubuntu
18.04LTS How do I run MySQL after installation?Launch the MySQL Command-Line Client. To launch the client, enter the following command in a Command Prompt window: mysql -u root -p . The -p option is needed only if a root password is defined for MySQL. Enter the password when prompted.
How do I start MySQL server after installing Windows?Contact MySQL |. Extracting the Install Archive.. Creating an Option File.. Selecting a MySQL Server Type.. Initializing the Data Directory.. Starting the Server for the First Time.. Starting MySQL from the Windows Command Line.. Customizing the PATH for MySQL Tools.. Starting MySQL as a Windows Service.. How do I start MySQL for the first time?ARCHIVED: MySQL first-time setup guide (old). Log into your MySQL account on mysql.iu.edu . ... . Start your MySQL server. ... . Change the root password. ... . Set privileges for the root user (and other usernames of your choosing) for connections coming from the web servers. ... . Create additional MySQL users:. How do I start MySQL database?In order to access your MySQL database, please follow these steps:. Log into your Linux web server via Secure Shell.. Open the MySQL client program on the server in the /usr/bin directory.. Type in the following syntax to access your database: $ mysql -h {hostname} -u username -p {databasename} Password: {your password}. |