Introduction to MySQL Notes Class 12

MySQL is a Relational Database Management System. It was originally founded and developed in Sweden by David Axmark, Allan Larsson and Michael Widenius.

Characteristics of MySQL:

1. Cost: It is released under an open–source license and hence required no cost or payment for its usage.

2. Speed: It has superior speed. It is easy to use and is reliable.

3. Ease of Use: It is a simple database system. It can be easily managed from the command line. Several graphical interfaces are also available.

4. Query Language Support: Standard SQL commands are understandable to MySQL.

5. Data Types: It supports many data types to support different types of data. It also supports fixed–length and variable–length records.

6. Security: It offers privilege and password system that is very flexible and secure. Passwords are secure because all password traffic is encrypted at the time of connecting to a server.

7. Scalability and Limits: It can handle large databases. Some real life MySQL databases contain 50 million records, some have up to 60,000 tables and about 500,00,00,000 rows.

8. Connectivity: Clients can connect to MySQL server easily

Introduction to MySQL Notes Class 12

Creating and using Database:

To create a database the CREATE DATABASE command is used, as follows.

Syntax : CREATE DATABASE <Database_Name>;

Ex : CREATE DATABASE School;

The above statement creates a database with the name School. To work with this database this database should be opened using USE statement, as follows

Syntax : USE <Database_Name>;

Ex : USE School;

Viewing Tables of a Database:

To see the tables present in a database, the statement

SHOW TABLES; can be used.

Data Types:

Commonly used data types available in MySQL are: 

1. INT:  A normal–sized integer that can be signed or unsigned. If signed, the allowable range is from –2147483648 to 2147483647. If unsigned, the allowable range is from 0 to 4294967295. Each INT value occupies 4 bytes of storage

2. DECIMAL(M, D): Represents a floating point number. M is the total number of digits, and D is the number of digits after decimal point.

3. FLOAT: Holds numbers with decimal points. Each float value occupies 4 bytes

4. DATE: A date in YYYY–MM–DD format, between 1000–01–01 and 9999–12–31

5. CHAR(M): A fixed–length string between 0 and 255 characters in length. M is the length. Providing M is optional and its default value is 1

6. VARCHAR(M): A variable–length string between 0 and 65535 characters in length. Providing M is compulsory

Difference between CHAR and VARCHAR:

The difference between CHAR and VARCHAR is that CHAR is of fixed–length and VARCHAR is of variable length.

When a column is given data type as CHAR(n), then all values stored in that column have this length, i.e. n bytes. If a value is shorter than this length n then blanks are added, but the size of the value remains n bytes.

VARCHAR specifies a variable length string. When a column is given data type as VARCHAR(n), then the maximum size of a value is n bytes. Each value that is stored in this column stores exactly as given, no blanks are added if the length is shorter than maximum length. However, if the maximum length is exceeded than an error message will be displayed.

Constraints:

Constraints are certain types of restrictions on the data values that an attribute can have. They are used to ensure the accuracy and reliability of data.

1. NOT NULL: Ensures that a column cannot have NULL values where NULL means missing/ unknown/not applicable value.

2. UNIQUE: Ensures that all the values in a column are distinct / unique.

3. DEFAULT: A default value specified for the column if no value is provided.

4. PRIMARY KEY: The column which can uniquely identify each row or record in a table.

5. FOREIGN KEY: The column which refers to value of an attribute defined as primary key in another table.

Creating a Table:

A table in the database can be created using CREATE TABLE command, as follows

Syntax : CREATE TABLE <TableName> (<ColumnName1> <DataType1>, <ColumnName2>

<DataType2>, ……., <ColumnNameN> <DataTypeN>);

Ex : CREATE TABLE STUDENT (RollNo INTEGER, Name VARCHAR(15), Class CHAR(3), DOB DATE);

Introduction to MySQL Notes Class 12

Inserting Data into a Table:

INSERT INTO command can be used to insert rows of data into a table. Its usage is as follows.

Syntax : INSERT INTO <TableName> (<ColumnName1>, <ColumnName2>, ….., <ColumnNameN>)

VALUES (<Value1>, <Value2>, <Value3>, ………<ValueN>);

Ex : INSERT INTO STUDENT (RollNo, Name, Class, DOB) VALUES(154, ‘Rajesh’, ‘11c’, ‘1996–04–05’);

Retrieving Data of a Table:

The SELECT command is used to display data of a table. It is also possible to display the filtered data from the table.

To Display all the rows and columns of Table:

Syntax : SELECT * FROM <TableName>;

Ex : SELECT * FROM Student;

To Display selected Columns and all Rows:

Syntax : SELECT <ColumnName1>, <ColumnName2>, …. FROM <TableName>;

Ex : SELECT Name, DOB FROM Student;

To Display selected Columns and selected Rows:

Syntax : SELECT <ColumnName1>, <ColumnName2>, …. FROM <TableName> WHERE Condition:

Ex : SELECT Name, DOB FROM Student WHERE Class = ‘11C’;

Eliminating Redundant Data with DISTINCT Keyword:

Syntax : SELECT DISTINCT <ColumnName1>, <ColumnName2>,… FROM <TableName>;

Ex : SELECT DISTINCT Class FROM Student;

Introduction to MySQL Notes Class 12

Displaying Current Database:

To display the name of the present working database, the following statement can be used

SELECT DATABASE( );

Categories of SQL Commands:

Data Definition Language (DDL) Commands:

The SQL commands used for creating a database, deleting a database, providing keys such as primary key, foreign key etc on tables are known as DDL commands.

A few examples for DDL commands in SQL are:

1. CREATE DATABASE – Creates a new database

2. CREATE TABLE – Creates a new table

3. ALTER TABLE – Modifies a table

4. DROP TABLE – Deletes a table

These commands will work with table structure not on table data directly (indirectly may act on table data)

Data Manipulation Language (DML) Commands:

The Query and Update commands on tables are referred as DML commands. A few examples for DML commands in SQL are:

1. SELECT – Extracts data from a table

2. UPDATE – Updates data in a table

3. DELETE – Deletes data from a table

4. INSERT INTO – Inserts new data into a table

Data Control Language (DCL) / Transaction Control Language (TCL) Commands:

The commands which are used to control the access to databases and tables are referred as Data Control Language or Transaction Control Language commands. A few examples for TCL commands in SQL are:

1. GRANT – To give rights of transaction to a user

2. REVOKE – To take back rights of transaction from a user

3. COMMIT – To make changes permanent

4. ROLLBACK – To undo changes of transaction

Introduction to MySQL Notes Class 12

Viewing Structure of a Table:

To see the structure of a table that is already created, the DESCRIBE or DESC command can be used as follows

Syntax : DESCRIBE <TableName>;

(Or)

DESC <TableName>;

Ex : DESC Student;

Changing Structure of a Table Using ALTER Command:

An existing table’s structure can be changed by using ALTER TABLE command.

Adding an attribute to a Table:

Syntax : ALTER TABLE <TableName> ADD <ColumnName> DataType;

Ex : ALTER TABLE Student ADD Grade CHAR(1);

Removing an attribute of a Table:

Syntax : ALTER TABLE <TableName> DROP <ColumnName>;

Ex : ALTER TABLE Student DROP DOB;

Modifying datatype of an attribute of a Table:

Syntax : ALTER TABLE <TableName> MODIFY <ColumnName> <New_Definition>;

Ex : ALTER TABLE Student Class VARCHAR(4);

Add Primary Key constraint to a Table:

Syntax : ALTER TABLE <TableName> ADD PRIMARY KEY(ColumnName);

Ex : ALTER TABLE Student ADD PRIMARY KEY(RollNo);

Removing Primary Key from a table:

Syntax : ALTER TABLE <TableName> DROP PRIMARY KEY;

Ex : ALTER TABLE Student DROP PRIMARY KEY;

Adding Foreign Key constraint to a Table:

Syntax : ALTER TABLE <TableName> ADD FOREIGN KEY(ColumnName) REFERENCES

<Referenced_TableName>(ColumnName);

Ex : ALTER TABLE Student ADD FOREIGN KEY(RollNo) REFERENCES Marks(RollNo);

Adding UNIQUE constraint to a Table:

Syntax : ALTER TABLE <TableName> ADD UNIQUE(ColumnName);

Ex : ALTER TABLE Student ADD UNIQUE(RollNo);

Adding DEFAULT constraint to a Table:

Syntax : ALTER TABLE <TableName> MODIFY ColumnName DATATYPE DEFAULT

Default_Value;

Ex : ALTER TABLE Student MODIFY Gender VARCHAR(10) DEFAULT ‘Male’;

Adding NOT NULL constraint to a Table:

Syntax : ALTER TABLE <TableName> MODIFY ColumnName DATATYPE NOT NULL;

Ex : ALTER TABLE Student MODIFY Gender VARCHAR(10) NOT NULL;