top of page

MySQL

Wha is it?

  • MySQL is an open source Relational Database Management System (RDBMS). 

  • Developed by Oracle Corporation.

  • Used for developing various web-based software applications.​​

  • MySQL is used by smaller, more Web-oriented databases. 

  • Developed as freeware.

  • Based on Structured Query Language.

  • MySQL is very popular for Web-hosting applications.

  • It is part of the Linux, Apache, MySQL, PHP (LAMP) architecture.

  • MySQL runs the back-end databases of some famous websites, including Wikipedia, Google, and Facebook.

  • MySQL does not terminate a command until you give a semicolon (;) at the end of the SQL command.

Installation Process

  • These are the steps for downloading & installing the MySQL software in PC.

  • Go to the browser, and search for download MySQL.

  • Click on the Download button to which is a 370 Mb installer.

  • Click on the Download button to which is a 370 Mb installer.

  • Click on the download in My

MySQL Datatypes 

  • A Data Type specifies a particular type of data, like integer, String, Floating points, Boolean etc.

  • These type of fields (or columns) are also referred to as data types.

  • We should use only the type and size of the field.

  • MySQL supports a lot number of SQL standard data types in various categories.

  • It uses many different data types broken into mainly three categories:

  • Numeric

  • Date and Time

  • String types

MySQL

Datatype

Numeric

Data and Time

String

Numeric Data Types

  • The following list shows the common numeric data types and their descriptions.

Date and Time Types

  • The MySQL date and time datatypes are as follows:

String Types

  • The following list shows the common string data types and their descriptions.

Large Object Data Types (LOB) Data Types:

Setting Up Mysql User Account

Open MySQL Command Line

  • Search for MySQL command line client, and select to open.

  • Or we can open the MySQL installation path and open the MySQL in the "bin" folder.

Login as administrator into DB

  • Type the password as "root" > Enter. 

  • Type "show databases" to see all the databased presented in the DB.

  • Type "use databasename"  to go into the databases.

Type "root" as password

To see all the Databases in MySQL server.

Result

use database;

To access a specific database.

Create a User Account

  • For adding a new user to MySQL, you just need to add a new entry to the user table in the database mysql.

  • Login as root.

  • Type "use mysql".

  • Type a query as: INSERT INTO user (host, user, password, select_priv, insert_priv, update_priv) VALUES ('localhost', 'birenDB', PASSWORD('birenDB'), 'Y', 'Y', 'Y');

  • You can also specify other privileges to a new user by setting the values to 'Y'.

Administrative MySQL Command

  • Here is the list of the important MySQL commands, which works with only MySQL database.​

MySQL Database

Create Database

  • You can create a MySQL database by using MySQL Command Line Client.

  • Open the MySQL console and write down the password, if you set one while installation. 

  • Create a database by the following syntax.

  • Syntax: CREATE DATABASE Customers;  

To create a new Customer database.

Customer database created

SELECT Database

  • SELECT Database is used in MySQL to select a particular database to work with. 

  • We can use SQL command "USE" to select a particular database.

  • Syntax: USE Customers;

To select a database.

Drop Database

  • We can drop/delete/remove a MySQL database easily with the MySQL command. 

  • Syntax: DROP DATABASE Customers;

To delete a database.

Customer database deleted.

To create a table

To see the tables created

To see the table structure.

To add a column

To modify column definition

To drop a column.

To rename a column.

To rename a table.

To remove the data, not structure.

To remove both the data and structure.

MySQL Table

Create Table

  • CREATE TABLE command is used to create a new table into the database.

  • A table creation command requires three things:

  • Name of the table

  • Names of fields

  • Definitions for each field

  • Syntax: CREATE TABLE table_name (column_name column_type...);   

  • Use the following command to see the table already created.

  • Use the following command to see the table already created with table structure.

Alter Table

  • ALTER statement is used when you want to change the name of your table or any table field.

  • It is also used to add or delete an existing column in a table.

  • To add a column in a table, syntax is:​ALTER TABLE table_name  ADD new_column_name column_definition  [ FIRST | AFTER column_name ]; 

  • To modify column definition in the table, syntax is:​​​​​ â€‹

  • ALTER TABLE table_name  MODIFY column_name column_definition  [ FIRST | AFTER column_name ];

  • To drop column in the table, the syntax is:  

  • ALTER TABLE table_name  DROP COLUMN column_name;  

  • To rename a column in the table, the syntax is:  

  • ALTER TABLE table_name  CHANGE COLUMN old_name new_name  column_definition  [ FIRST | AFTER column_name ]  

  • To rename a table, the syntax is:  

  • ALTER TABLE table_name  RENAME TO new_table_name;  

Truncate vs Drop Table

  • TRUNCATE TABLE: It removes only table data, not structure.

  • DROP TABLE: It will remove complete table data and structure both.

MySQL Queries

Inserting partial fields

Inserting all fiels.

Inseting multiple records.

To update existing data.

Data changed.

INSERT Record

  • INSERT statement is used to insert data in MySQL table within the database. 

  • We can insert single or multiple records using a single query in MySQL.​​

  • For partial field, syntax: INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN );  

  • Syntax for all fields: INSERT INTO table_name VALUES ( value1, value2,...valueN );  

  • For inserting multiple records.

  • Syntax: INSERT INTO cus_tbl  (cus_id, cus_firstname, cus_surname)  VALUES  (5, 'Ajeet', 'Maurya'),  (6, 'Deepika', 'Chopra'), (7, 'Vimal', 'Jaiswal');  

UPDATE Record

  • UPDATE statement is used to update data of the MySQL table within the database.

  • It is used when you need to modify the table.

  • Syntax: UPDATE table_name SET field1=new-value1, field2=new-value2  [WHERE Clause] 

DELETE Record

  • DELETE statement is used to delete data from the MySQL table within the database.

  • We can delete records on the basis of conditions.

  • Syntax: DELETE FROM table cust_table WHERE (Condition specified).

To delete data.

Data to be deleted.

Data deleted.

SELECT Record

  • SELECT statement is used to fetch data from the one or more tables in MySQL.

  • We can retrieve records of all fields or specified fields.

  • The syntax for specified fields: SELECT expressions FROM tables [WHERE condition]

To select specified field.

  • For selecting all field, systax: SELECT * FROM table_name;

To select all fields

  • From multiple tables, we can use JOIN statement.

  • Here we will take 2 tables; Cust_table and sub table.

  • The syntax for getting data from both tables: â€‹SELECT officers.officer_id, students.student_name  FROM students  INNER JOIN officers  ON students.student_id = officers. officer_id  ORDER BY student_id;  

To select data from multiple tables

(JOIN Statement)

Table 1

Table 2

Result table

History

  • Released in 1995.

  • Is sponsored by the Swedish company MySQL AB.

  • Owned by Oracle Corp.

Pros & Cons

Pros

  • Is an open source.

  • MySQL runs on all platform, including Linux, Unix, and Windows.

  • It supports languages such as Java, PHP, and Python.

Cons

  • MySQL is an open source Relational Database Management System (RDBMS). 

  • It can not integrate with MS Visual Studio.

  • MySQL version less than 5.0 doesn't support ROLE, COMMIT and stored procedure.

bottom of page