MySQL

Introduction

Mysql is:

• Relational database management system
• Open-source
• Runs on server
• Provides multi-user access to databases at a time
• Used by Facebook, Google, Adobe, Alcatel Lucent and Zappos.
• Mostly used in web to store huge information.
• Used in Conjugation with PHP which is a programming language for web.
• Can be used with java, C# and others.
• Able to execute in different platforms.

What is a database?

A database is a collection of tables. Tables contain a number of fields and those fields are assembled together to form some kind of useful information on the object . Information on similar objects are stored together in a table under the different rows.

Installation

Here's an article on how to deploy MySQL server and application to a client machine. This includes the installation procedure of the MySQL server and client.

Setting environment variables for  MySQL  

1. Goto Computer>Properties>Advanced System Settings>Environment Variables
2. Under system variables group, find the row containing Path in variable field.
   
3. Click edit button.
4. Under variable value text box, goto end of text and add a semicolon(;).
5. Now add the path of the mysql here. I am working with the mysql that is installed with wamp server. So my path is
   
6. Click Ok.
7. The environment variable is now set for the path containing mysql.exe
8. Goto command [type cmd in run]
9. now type mysql and press enter
   

This is the basic mysql login and there's no password set.

Starting/Stopping MySQL service manually:

First we need to identify the service name of MySQL. If you install a standalone MySQL then the default service name is MySQL , and if it is with wamp server, then the service name is wampmysqld.

Starting any windows process:

sc start process_Name or net start process_Name
sc start MySQL
sc start wampmysqld

Stopping any windows process:

sc stop process_Name or net stop process_Name
sc stop MySQL
sc stop wampmysqld

Logging in with user as root and no password:

mysql -u root -p
when there is a prompt for password, hit enter
Now you are logged in as a "root" user with no password.

Logging in with user as a user_name and password:

mysql -u user_name -p
when there is a prompt for password, enter your password.
Now you are logged in as "user_name" and password.

Note: The default delimiter of the MySQL command line utility is semicolon(;). So after each command this delimiter. It denotes termination of a command. The command to change the default delimiter is:

delimiter //;

This sets the delimiter to be "//"

Basic MySQL commands:

To select a database:
USE DATABASE_NAME;

To list databases:
SHOW DATABASES;

To list tables in a database:
SHOW TABLES;


Creating Database:
CREATE DATABASE IF NOT EXISTS DATABASE_NAME;
Example: CREATE DATABASE IF NOT EXISTS movies;
This query creates new database if there is no database with name "movies". We can see the database by using the command: SHOW DATABASES;
Now we can see our new database in the database listing.


Dropping or Deleting Database:
DROP DATABASE IF EXISTS DATABASE_NAME;
Example: DROP DATABASE IF EXISTS movies;


MySQL Data Types:
A Database contains a number of tables. A table has a number of rows that fit into the attributes or the columns of the table. A column contains a similar data type. These data types define values the column can have.
Numeric Data Types:
Most frequently used Numeric Data Types are INT, FLOAT and DOUBLE.
These data types have different range of values they can store and different storage size. Based on nature of data, we should determine the data types.
More detailed description can be found here .
String Data Types:
MySQL can hold any data types from plain text to images and other types of files. The string data types are  CHAR , VARCHAR , BINARY , VARBINARY , BLOB , TEXT , ENUM , and SET.
Mostly used types are CHAR, VARCHAR AND TEXT . Although images and other files can be saved in MySQL as data type, it is best practice to store the filename of these files in database for efficiency. The images are saved to disk storage rather than in database and their filenames are stored in the database.  In most web applications, this is a common practice. But this practice can be modified as per the requirements of the system that we are working with and the complexity of the logic to be implemented.
Date &  Time Data Types:
The date and time data types are DATE , TIME , DATETIME , TIMESTAMP , and YEAR.
Date and Time are stored in their respective format like YYYY-MM-DD .