Kela Casey Software Engineer Share







MySQL is a fast, easy to use relational database. It is currently the most popular open-source database. It is commonly used in conjunction with PHP scripts to create powerful and dynamic server-side applications.



MySQL is used for many small and big businesses. It is developed, marketed and supported by MySQL AB, a Swedish company. It is written in C and C++.

This language is sporting a barrier of entry low enough to attract novice developers yet strong enough to power some of the world’s most popular websites, among them Yahoo!, BBC News, the US Census Bureau, and Craigslist.



This reference is created to help you quickly navigate some of MySQL’s most popular features. Covering topics such as configuration, administration software, backup procedures, SQL features, and user management, this reference will serve as a handy desk for countless projects to come.

Reasons For Popularity



MySQL became so popular because of these following reasons:

MySQL is an open-source database so you don’t have to pay a single penny to use it.



MySQL is a very powerful program so it can handle a large set of functionalities of the most expensive and powerful database packages.



MySQL is customizable because it is an open-source database and the open-source GPL license facilities, programmers, to modify the SQL software according to their own specific environment.



MySQL is quicker than other databases so it can work well even with the large data set.



MySQL supports many operating systems with many languages like PHP, PERL, C, C++, JAVA, etc.



MySQL uses a standard form of the well-known SQL data language.



MySQL is very friendly with PHP, the most popular language for web development.



Configuration



MySQL supports over 260 configuration parameters, capable of controlling behavior regarding memory, logging error reporting and much more. While it’s possible to tweak these parameters by passing them as flags when starting the MySQL server, typically you’ll want to ensure they’re always set at server startup, done by adding them to my.cnf file.

my.cnf File

my.cnf file’s range of impact on the MySQL server is location-dependent:





my.cnf File Synatax



my.cnf file is a text file broken into several sections. Each section defines the context of the parameters defined within, the context being specific to a particular MySQL client. For example:



# All clients [client] port = 3306 # The mysql client [mysql] safe-updates # The mysqldump client [mysqldump] quick

Viewing Configuration Parameters



You can view MySQL’s configuration parameters and their current values using one of the following commands:

From the mysqladmin client:

%>mysqladmin -u root -p variables

From inside the MySQL client:

mysql>SHOW VARIABLES;

You can find a specific parameter using the LIKE operator :

mysql>SHOW VARIABLES LIKE "key%";

Storage Engines

How to install MySQL

Download MySQL



Follow these steps:-



Step 1– Go to MySQL official website

https://www.mysql.com/downloads/



Step 2- Choose the version number for MySQL community server which you want.

Installing MySQL on Windows



Your downloaded MySQL is neatly packaged with an installer. Download the installer package, unzip it anywhere and run setup.exe .



By default, this process will install everything under C:\mysql .



Verify MySQL installation

Once MySQL has been successfully installed, the base tables have been initialized, and the server has been started, you can verify its working via some simple tests.



Open your MySQL Command Line Client, it should appear with a mysql> prompt . If you have set any password, write your password here. Now, you are connected to the MySQL server and you can execute all the SQL command at mysql> prompt as follows:



Data Types

MySQL supports a rich set of data types capable of representing nearly every conceivable data formate, ranging across dates and times, currency, strings, integers, and floats. This section defines each type and its respective range.

Date and Time Types

Note:- MySQL is fairly flexible in terms of how it accepts date and time type values. For instance, DATE, DATETIME, and TIMESTAMP will all accept ‘2008-09-02’, ‘2008/09/02’, and ‘2008*09*02’ as valid date values.



Numeric Types





String Types







Popular Administration Services



Web frameworks help the programmer to embrace best practices, simultaneously decreasing errors and eliminating redundant code. If you haven’t yet settled upon a framework, consider checking out one or several of the following popular solutions:-







MySQL’s Clients



MySQL is bundled with quite a few clients capable of doing everything from performing backups, managing the MySQL server, converting table formats, and stress-testing the database. In this section, I’ll briefly introduce the most commonly used clients.



Key Administration Task



Logging into MySQL server

To login into the MySQL server using the MySQL client, you’ll typically provide your MySQL username and password:-



%>mysql -u username -p Enter password: ***** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.1.22-rc-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>

Once you’re logged in, you can select a database or begin carrying out other administrative tasks. To save some time you can pass the desired database along on the command line when logging in:

%>mysql -u username -p database_name

If you are connecting to a remote database, pass the hostname or IP address along using the -h option:-



%>mysql -h hostname -u username -p database_name

To log out of the MySQL server, use quit or the \q flag:-

mysql>quit Bye %>

Modify the MySQL Prompt



MySQL’s default prompt is enough to remind you are currently logged into MySQL rather than into an operating system shell. However, like most shells, you can modify MySQL’s prompt to your liking. For instance, when logged into the MySQL client execute the following command to change your prompt to MySQL ([email protected])



Databases







Tables







Managing Users





MySQL is packaged with a powerful security model, capable of controlling practically every conceivable user action, ranging from which commands can execute to how many queries can execute in an hour. This model works in a two-step sequence:

Although covering the nuances surrounding MySQL’s privilege tables is beyond the scope of this document, the remainder of this section should give you ample reminders regarding commonplace tasks. You are, however, encouraged to carefully review the privilege table documentation found in the MySQL manual, as it’s easy to make a mistake when using this powerful feature.



To grant a user all privileges on all databases, use *.* in place of the database name.

Note:- Be sure to include the IDENTIFIED BY clause when creating new users! Neglecting to include this information results in a user being created without a required password.

Key SQL Tasks



While executing standard SQL statements is likely old hat for most users, it may be more difficult to recall the syntax pertinent to some of MySQL’s relatively new SQL features, namely Stored Routines, Views, and Triggers.



Stored Routines



MySQL collectively refers to stored procedures and stored functions as stored routines. Stored procedures are executed using the CALL statement, and can return values as MySQL variables, whereas stored functions can be called directly from within a MySQL like any other standard MySQL function.

In this section, a brief refresher is provided regarding managing what is arguably the more useful of the two, namely stored functions.



Creating a Stored Function

A stored function is created using the CREATE FUNCTION command. A simple example follows:



mysql>DELIMITER $ mysql>CREATE FUNCTION calculate_bonus ->(employee_id INTEGER) RETURNS DECIMAL(5,2) ->BEGIN ->DECLARE article_count INTEGER; ->DECLARE bonus DECIMAL(10,2); ->SELECT count(id) AS article_count FROM articles ->WHERE author_id = employee_id; ->SET bonus = article_count * 10; ->RETURN bonus; ->END; ->$ mysql>DELIMITER ;



Once created you can call calculate_bonus() from within a query: mysql>SELECT name, phone, calculate_bonus(id) FROM authors;



Note:- Stored procedures and functions support complex logical syntax features, including conditionals and looping statements.

Altering a Stored Function



To modify an existing function, use the ALTER FUNCTION command:

mysql>DELIMITER $ mysql>ALTER FUNCTION calculate_bonus ->MODIFIED FUNCTION BODY... ->$ mysql>DELIMITER $

Deleting a Stored Function

To delete a stored function, use the DROP FUNCTION command:

mysql>DROP FUNCTION calculate_bonus;

Views



Views can greatly simplify the execution and management of an otherwise complex query by assigning an alias to it, allowing the developers to execute the query by its alias rather than repeatedly entering the query in its entirety.



Triggers



Triggers are automatically activated in accordance with a specific table-related event. They are useful for automating table updates which should occur when another table is modified in some way.



Performing Backups



Performing regular backups is an essential part of even the smallest database project. Fortunately, MySQL makes this very easy by offering several backup solutions.



Copying Files



If your tables use the MyISAM storage engine, you can backup the database simply by copying the files used to store the tables and data. To do so, you’ll need to first execute the LOCK TABLES command (only a read lock is required), followed by FLUSH TABLES. Once executed, copy the files, and when the copy is complete, execute UNLOCK TABLES.



Creating Delimited Backups

To backup the table data in delimited format, use the SELECT INTO OUTFILE command. For instance, to backup the author’s table used in previous examples, execute:

mysql>SELECT * INTO OUTFILE 'authors090308.sql' FROM authors;

Using mysqldump



The mysqldump client is convenient because it supports creating backups of all databases using any MySQL storage engine, not to mention that it automatically takes care of important details such as locking the tables during the backup.



The mysqldump client supports an enormous number of options, and it’s recommended you take some time to review them in the MySQL manual, however, this section will give you enough to at least remind you of what’s required to perform a variety of different backups.



Backing Up a Specific Database



To backup a single database, just pass the database name to the mysqldump client, piping the output to a text file:



%>mysqldump [options] database_name > backup0903.sql

Of course, you’ll require proper permissions to execute mysqldump in conjunction with a specific database ( namely the SELECT and LOCK privileges), therefore you’ll typically also need to pass along your username and password. In this case, this command typically looks similar to:

%>mysqldump -u root -p database_name > backup0903.sql

Backing Up Specific Tables



To backup specific tables, you will need to identify the database, followed by each specific table name you’d like to backup:



%>mysqldump [options] database_name table_name [table_ name2...] > backupfile.sql



Backing Up All Databases



To backup all databases, pass the –all-databases option:

%>mysqldump [options] --all-databases > backupfile.sql



Using mysqlhotcopy



If all of your backup tables use the MyISAM storage engine, and you are able to log into the server where the tables are stored, the mysqlhotcopy might be the ideal solution due to its speed advantage.



To backup the codersera database to a directory located at/home/jason/backups using mysqlhotcopy, execute:



%>mysqlhotcopy -u root -p codersera /home/jason/backups

To copy multiple databases, just string each database name together:

%>mysqlhotcopy -u root -p codersera wjgilmore /home/jason/backups

Note:- MySQL’s replication features make it possible to maintain a consistently synchronized version of the live database. Replication is out of the scope of this reference card, but be sure to visit the MySQL documentation (http://dev.mysql.com/doc/) if replication is more suitable to your needs.



MySQL’s Date and Time Features



In addition to temporal data types, MySQL supports over 50 functions intended to facilitate the retrieval and manipulation of date-related data. Rather than exhaustively itemize these functions, it seems useful to instead offer a number of common examples that may help jumpstart your search for the appropriate approach to carry out date and time-related queries.



SQL, Lisp and Haskell are the only programming languages that i’ve seen where one spends more time thinking than typing. – Philip Greenspun

Also Try Reading Most Common SQL Questions & Answers You Must Know For Your Next Interview

How useful was this post? How useful was this post? Click on a star to rate it!







Submit Rating Average rating 0 / 5. Vote count: 0 No votes so far! Be the first to rate this post. Please do Rate Us and Share!





Related Blogs Adam Davidson Data science 8 Best Examples of Data Science in Finance Data science in finance is aimed at extracting knowledge from a huge amount of data by employing mathematics and statistics. And many different techniques are employed to achieve this goal as good research leads to better outcomes leading to a profit for financial institutions. Data science has become extremely relevant in finance sector, which... Continue Reading

Kela Casey Angular Best Angular Projects for Beginners 2020 Presenting the best angular projects for beginners list that will prepare you well with the basics and practical needs in angular development. Mentioning your experience in Angular projects can make your resume stand apart from other candidates. Angular Projects for Beginners Soundnode Notepad application Data binding in forms Customer service manager Angular Bare bones project Angular... Continue Reading

Kela Casey Programming , Python Best Python IDEs & Code Editors for 2020 In this post, we’ll discuss what is an IDE/ Code editor, the difference between IDE & Code editors, and some of the best Python IDEs & code editors, along with their best features. Python is a multi-faceted programming language that has been embraced globally with open arms. Python comes with innumerable useful features of... Continue Reading



About The Author Kela has 7+ years of experience in JavaScript, Python, C++, and Java. She’s worked as a software engineer at Google on the Maps JavaScript API, at Biarri automating and optimizing Australia’s fiber network designs.

Feedback Please leave a comment here and share your valuable feedback with us!

Try our One-Week Risk Free Trial for Hiring a Coder Know more Hire a Coder