MYSQL BASICS
MYSQL
Database Concepts
Mysql Basics
MYSQL DDLs
MYSQL DMLs
MYSQL Joins
MYSQL Functions
Introduction to MySQL
MySQL is an Open Source, Fast and Reliable Relational Database Management System (RDBMS) software like Oracle, Sybase, MS SQL Server etc. It was developed by Michael Widenius and AKA Monty and is
alternative to many of the commercial RDBMS.
The main features of MySQL are-
Open Source & Free of Cost:
It is Open Source and available at free of cost.
Portability:
It can be installed and run on any types of Hardware and OS like Linux,
MS Windows or Mac etc.
Security :
It creates secured database protected with password.
Connectivity
It may connect various types of Network client using different protocols
and Programming Languages
Query Language
It uses SQL (Structured Query Language) for handling database.
MySQL & SQL
In order to access data from the MySQL database, all program and user must use SQL (Structured Query Language). SQL is a set of commands that are recognized by all the RDBMSs and has become a standard language for database handling.
SQL is a language that enables you to create and manage a relational database, in which all the information are kept in tables.
There are numerous version of SQL. The original version was developed at IBM’s San Jose Research Laboratory with a name of Sequel, as a part of System R project in 1970s. It was standardized by ANSI in 1986 by the name of SQL.
SQL is a Standard Query language whereas MySQL is a DBMS Software based on SQL.
Types of SQL Commands
SQL command or statement is a special kind of sentence that contains clauses and all end with a semicolon(;) just as a sentence ends with a period. MySQL follows SQL specifications for its commands . These SQL commands can be categorized as –
Data Definition Language (DDL)
These SQL commands are used to create, alter and delete database objects like table, views, index etc.
Example : CREATE , ALTER , DROP etc.
(i) CREATE used to create new table in the database.
(ii) DROP used to delete tables from the database.
(iii) ALTER used to change the structure of the database table. This statement can add up additional column, drop existing, and even change the data type of columns involved in a database table.
(iv) RENAME used to rename a table.
Data Manipulation Language (DML)
These commands are used to insert, delete, update and retrieve the stored records from the table. It provides statements for manipulating the database objects.
It is used to query the databases for information retrieval.
Ex. SELECT…., INSERT…, DELETE…, UPDATE…. etc.
(i) INSERT used to insert data into a table.
(ii) SELECT used to retrieve data from a database.
(iii) UPDATE used to update existing data within a table.
(iv) DELETE used to delete all records from a table.
Transaction Control Language (TCL)
These commands are used to control the transaction. These are also used to manage the changes made by DML.
Ex. COMMIT, ROLLBACK, SAVEPOINT etc.
Some TCL statements are as follows
(i) COMMIT used to save the work done.
(ii) SAVEPOINT used to identify a point in a transaction to which you can later rollback.
(iii) ROLLBACK used to restore database to original since the last COMMIT.
(iv) SET TRANSACTION establishes properties for the current transactions
Data Control Language (DCL)
These commands are used to manipulate permissions or access rights to the tables etc.
Ex. GRANT , REVOKE etc.
(i) GRANT used to give user’s access privileges to database.
(ii) REVOKE used to withdraw access privileges given
with grant command.
Rules for SQL commands
(i) SQL statements can be typed in lowercase or uppercase letter. SQL statements are not case sensitive.
(ii) The statements can be typed in single line or multiple lines.
(iii) A semicolon (;) is used to terminate the SQL statements.
(iv) The statements may be distributed across the line but keywords cannot be.
(v) A comma (,) is used to separate parameters without a clause.
(vi) Characters and date constants or literals must be enclosed in single quotes (‘A’).
(vii) A command can be typed either full or first four characters.
SQL Data Types
Data types are declared to identify the type of data that will be stored in a particular field or variable.
The following list of general SQL data types are given below
Numeric Data Types:
INTEGER or INT – up to 11 digit number without decimal. A 32-bit signed integer value and its range from −2147483648 to 2147483647
SMALLINT – up to 5 digit number without decimal. A 16-bit signed integer value and its range from −32768 to 32767.
FLOAT (M,D) or DECIMAL(M,D) or NUMERIC(M,D) Stores Real numbers upto M digit length (including .) with D decimal places. e.g. Float (10,2) can store 1234567.89
Date & Time Data Types:
DATE – Stores date in YYYY-MM-DD format.
TIME – Stores time in HH:MM:SS format.
String or Text Data Type:
CHARACTER (fixed length)
CHAR(Size) A fixed length string up to 255 characters. (default is 1) It will occupy space for NULL values. It can hold atmost 2000 characters and used in ANSI standard.
CHARACTER (Varying length)
VARCHAR(x) Where, x is the number of characters to be stored. A variable length string up to 255 characters. Where, x is the number of characters to be stored. It can hold 4000 bytes of characters and used only in Oracle.
SQL Commands
Creating a Database.
The following command will create School database in MySQL.
mysql> CREATE DATABASE [IF NOT EXISTS] Name_of_database;
IF NOT EXISTS is an optional part of this statement which prevents you from an error if there exists a database with the given name in the database catalog.
Opening a database
To open an existing database, following command is used.
mysql> USE school ; Getting listings of database and tables
mysql> SHOW DATABASES;
mysql> SHOW TABLES;
Deleting a Database and Table
mysql> DROP DATABASE School;
mysql> DROP TABLE Student;
Viewing Table Structure
mysql> DESCRIBE Student;