This article is a MySQL guide article that tackles how the program works and what it can do for both users and programmers.
MySQL is an open source multi-threaded, relational database management system (RDBMS) created by Michael “Mondy” Widenius back in 1995. It was released under GNU GPL (General Public License) due to its popularity in 2000.
Your Designer Toolbox Unlimited Downloads: 500,000+ Web Templates, Icon Sets, Themes & Design Assets
MySQL AB is the company that owns and develops MySQL, which is presently a subsidiary of Sun Microsystems. The success of MySQL as a leading database is due to:
Its cost effectiveness
Open-source database
It’s reliability, performance and features
The following list shows the most important features of MySQL
It is Relational Database System
It is a client/server system
Supports as its database language — as its name suggests – SQL (Structured Query Language)
Remarkably scalable, and able to handle tens of thousands of tables and billions of rows of data
Designed to be fully multi-threaded using kernel threads, to easily use multiple CPUs if they are available
Executes very fast joins using an optimized nested-loop join
A privilege and password system that is very flexible and secure, and enables host-based verification
All data is saved in the chosen character set
Tested with a broad range of different compilers
Two Components of MySQL
There are two main components to MySQL:
1. The MySQL database server, mysqlId – It runs all the time in the background, accepting connections from client programs and so on.
2. Various client and utility programs – Includes mysql command-line MySQL Monitor Client.
Ways to install MySQL and its associated programs
1. Official MySQL installation package – MySQL official website has different prebuilt packages available for different platforms. Simply download the package file, extract it, and run the installer.
2. Linux Package Manager – Using Linux package manager like Ubuntu Sotware Centre comes with a installation of MySQL along with Apache and PHP and some other softwares.
3. XAMPP, LAMP, WAMP package – This is the easiest way to install a complete MySQL-base development setup on your computer. It includes Apache web server, MySQL, PHP and Perl on the installation.
MySQL Statement and Clauses
Below are the list of the basic MySQL statements that should prove useful for basic CRUD operations (create, replace, update, delete, select) and performing some user authentication process.
ALTER DATABASE
ALTER TABLE
ALTER VIEW
ANALYZE TABLE
BACKUP TABLE
CACHE INDEX
CHANGE MASTER TO
CHECK TABLE
CHECKSUM TABLE
COMMIT
CREATE DATABASE
CREATE INDEX
CREATE TABLE
CREATE VIEW
DELETE
DESCRIBE
DO
DROP DATABASE
DROP INDEX
DROP TABLE
DROP USER
DROP VIEW
EXPLAIN
FLUSH
GRANT
HANDLER
INSERT
JOIN
KILL
LOAD DATA FROM MASTER
LOAD DATA INFILE
LOAD INDEX INTO CACHE
LOAD TABLE…FROM MASTER
LOCK TABLES
OPTIMIZE TABLE
PURGE MASTER LOGS
RENAME TABLE
REPAIR TABLE
REPLACE
RESET
RESET MASTER
RESET SLAVE
RESTORE TABLE
REVOKE
ROLLBACK
ROLLBACK TO SAVEPOINT
SAVEPOINT
SELECT
SET
SET PASSWORD
SET SQL_LOG_BIN
SET TRANSACTION
SHOW BINLOG EVENTS
SHOW CHARACTER SET
SHOW COLLATION
SHOW COLUMNS
SHOW CREATE DATABASE
SHOW CREATE TABLE
SHOW CREATE VIEW
SHOW DATABASES
SHOW ENGINES
SHOW ERRORS
SHOW GRANTS
SHOW INDEX
SHOW INNODB STATUS
SHOW LOGS
SHOW MASTER LOGS
SHOW MASTER STATUS
SHOW PRIVILEGES
SHOW PROCESSLIST
SHOW SLAVE HOSTS
SHOW SLAVE STATUS
SHOW STATUS
SHOW TABLE STATUS
SHOW TABLES
SHOW VARIABLES
SHOW WARNINGS
START SLAVE
START TRANSACTION
STOP SLAVE
TRUNCATE TABLE
UNION
UNLOCK TABLES
USE
MySQL String Functions
Below are the list of MySQL String Functions that are mostly used to create conditional statements for strings and some other associate elements.
AES_DECRYPT
AES_ENCRYPT
ASCII
BIN
BINARY
BIT_LENGTH
CHAR
CHAR_LENGTH
CHARACTER_LENGTH
COMPRESS
CONCAT
CONCAT_WS
CONV
DECODE
DES_DECRYPT
DES_ENCRYPT
ELT
ENCODE
ENCRYPT
EXPORT_SET
FIELD
FIND_IN_SET
HEX
INET_ATON
INET_NTOA
INSERT
INSTR
LCASE
LEFT
LENGTH
LOAD_FILE
LOCATE
LOWER
LPAD
LTRIM
MAKE_SET
MATCH AGAINST
MD5
MID
OCT
OCTET_LENGTH
OLD_PASSWORD
ORD
PASSWORD
POSITION
QUOTE
REPEAT
REPLACE
REVERSE
RIGHT
RPAD
RTRIM
SHA
SHA1
SOUNDEX
SPACE
STRCMP
SUBSTRING
SUBSTRING_INDEX
TRIM
UCASE
UNCOMPRESS
UNCOMPRESSED_LENGTH
UNHEX
UPPER
MySQL Date and Time Functions
Below are the list of MySQL Date and Time functions as the name suggest these commands are use for manipulating date and time.
ADDDATE
ADDTIME
CONVERT_TZ
CURDATE
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURTIME
DATE
DATE_ADD
DATE_FORMAT
DATE_SUB
DATEDIFF
DAY
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
EXTRACT
FROM_DAYS
FROM_UNIXTIME
GET_FORMAT
HOUR
LAST_DAY
LOCALTIME
LOCALTIMESTAMP
MAKEDATE
MAKETIME
MICROSECOND
MINUTE
MONTH
MONTHNAME
NOW
PERIOD_ADD
PERIOD_DIFF
QUARTER
SEC_TO_TIME
SECOND
STR_TO_DATE
SUBDATE
SUBTIME
SYSDATE
TIME
TIMEDIFF
TIMESTAMP
TIMESTAMPDIFF
TIMESTAMPADD
TIME_FORMAT
TIME_TO_SEC
TO_DAYS
UNIX_TIMESTAMP
UTC_DATE
UTC_TIME
UTC_TIMESTAMP
WEEK
WEEKDAY
WEEKOFYEAR
YEAR
YEARWEEK
Who Uses MySQL?
Bank of Finland
The Bank of Finland developed a MySQL-based versatile Payment and Settlement System Simulator for making payments and settlement system simulations. Running on Windows, the application has been widely adopted by Central Bank economists worldwide.
The White Housea>
A programming overhaul of the White House’s web site has set the tech world abuzz. The White House website database operates using a database foundation like MySQL.
Adobe
Adobe Systems is one of the largest software companies and the leading provider of creative tools for print, web, interactive, mobile, video, and film. Adobe embeds MySQL into several Adobe Creative Suite 3 components including Adobe Acrobat CS3, Adobe® Bridge CS3, and Adobe® Version CueR CS3 so that workgroups can work more efficiently on complex projects.
iStockphoto
Istockphoto.com is the biggest royalty-free stock photo community in the world, and its sister company, istockpro.com, is home to a host of illustrious professional photographers.
Every week, approximately 5,000 photographers upload more than 2,500 photos (2.5 GB) to MySQL® — the world’s most popular open source database — and approximately 1,250(1.25 GB) are accepted and posted to istock Web sites.
Where to Learn MySQL?
Although there are plenty of Websites where you can learn MySQL, I listed down some free and premium sites that can help you to have a strong foundation on this subject.
1. MySQL Documentation MySQL documentation contains all the full list of commands and updates you want to learn everything on this topic.
2. Lynda
It is training library teaches computer skills in video format to members through monthly and annually.
3. Tizag A site designed to teach beginner web programmers how to use HTML, CSS, PHP and MySQL. It’s a great website to check out.
MySQL Books
1. MySQL Cookbook
If you are a developer who has to store and retrieve data from MySQL database, this book is a must.
This book explains the following:
How to use mySQL client
How to write MySQL program that connects to a DB, selects a DB, and queries data
Explains table management, strings, date and time, query sorting, and summary report generation
How to import and export data, and create stored procedures
2. MYSQL in a Nutshell
If you’ve used other “In a Nutshell” series from O’Reilly, you would love this book. Please keep in mind that this is a reference manual to MySQL statements, functions, and administrative utilities.
This book contains the following:
MySQL programming language API for PHP, Perl, and C. MySQL
Explains replication, triggers, and stored procedures
With different examples
3. MySQL Stored Procedure Programming
Oracle developers never took MySQL seriously, until the stored procedures were implemented in MySQL 5.
This book explains the following:
How to implement stored procedures in MySQL
Contains a lot of practical examples
Covers the stored procedure fundamentals, including language fundamentals, blocks, conditional statements, and error handling
Wrapping Up
MySQL databases are a big topic to discuss. There are a lot more important areas to explore like normalization, grouping tables, updating and deleting records on the database.
You might also like:
This post may contain affiliate links. See our disclosure about affiliate links here.