MySQL Guide for Beginners: What You Must Know
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.
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
- CREATE DATABASE
- CREATE INDEX
- CREATE TABLE
- CREATE VIEW
- DROP DATABASE
- DROP INDEX
- DROP TABLE
- DROP USER
- DROP VIEW
- 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
- RESET MASTER
- RESET SLAVE
- RESTORE TABLE
- ROLLBACK TO SAVEPOINT
- 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
- UNLOCK TABLES
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.
- MATCH AGAINST
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.
Who Uses MySQL?
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.
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 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.
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.
It is training library teaches computer skills in video format to members through monthly and annually.
A site designed to teach beginner web programmers how to use HTML, CSS, PHP and MySQL. It’s a great website to check out.
- 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
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
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!