A database is an essential part of most websites. From simple blogs to complex e-commerce websites, everything is being driven by a database.
If you are a web developer, you definitely know a lot about MySQL.
- Is the most popular open source database system
- Uses SQL (Structured Query Language) to manipulate information inside the database
- It’s free, very dependable and fast
- It has several application programming interfaces (APIs)
- Easy to use to develop software or applications
This article is a MySQL guide article that tackles how does the program works and what it can do for both users and programmers.
What is MySQL?
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. 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 House
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 by Paul DuBois. 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 by Russell Dyer. 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, by Guy Harrison, Steven Feuerstein. 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.
In this article, I introduce to you the basic information you need to learn about MySQL. I recommend exploring more books and video tutorials on the web to have a solid foundation on this topic.
Hope you learned something from this article and see you again next time!
No Comments