blogging

MySQL For Class 12

MySQL is one of the most important topics in the class 12 computer science syllabus. Thus students should have a good knowledge about MySQL if they want to score good marks in the board exam

RahulVk June 8 2020 read
Share this

What is SQL and MySQL?

SQL stands for Structured Query Language and it is used to communicate with the database.SQL commands are used to create, transform and retrieve data from a Relational Database Management System(eg. MySQL) . SQL creates an interface between the user and the database using which the user can obtain , insert or perform other functions with the data in the database.

It is important to understand that SQL and MySQL are different. SQL is a language where as MySQL is an open source Relational Database Management System (RDBMS) that uses Structured Query Language. It allows us to create, store and access data in the form of databases and their respective tables.

In order to score marks in SQL, you need to practice and practice. Installing MySQL can be every tricky. Watch the below video made by us showing how to install MySQL on Windows.

We are not going to talk more about the advantages and features of SQL. You should learn it from your text books. We are going to focus on doing problems. Like creating a database, inserting and manipulating data and other things required for class 12. We are going to show you all functions and commands that you need to learn with their application. But before we get into that it is very important to learn about elements and data types in MySQL.

MySQL Elements

  • Literals
  • Data types
  • Nulls
  • Comments

Literals

Literals means fixed or constant value. It can be of numeric , character or date time type.  Character and date time literals should always be enclosed in single quotation mark. There is no need for single quotes in the case of numeric literals . If you want to include a special character like a quote or apostrophe, it should be wriiten only after a back-slash.

For example , if you want to write Rahul's Blog in sql, you should write it like this 'Rahul\'s Blog'.

Datatypes

It means the type of value and the type of operation that you can perform on the data. 

For example, Arithmetic operations can only be performed on numeric data types. SQL supports the following data types. 

  • Numeric
  • String types
  • Date time

Now let's take a look at numeric data types in SQL

Numeric Datatypes

Datatype Description
INT Stores up to 11 digits. No decimals
TINYINT Stores up to 4 digits. 0 - 255
SMALLINT Stores up to 5 digits . -32768 to 32767
MEDIUMINT Stores up to 9 digits
BIGINT Stores up to 11 digits
FLOAT(size,d) Allows small numbers with floating decimal point. The size parameter is used to specify the maximum number of digits, and the d parameter is used to specify the maximum number of digits to the right of the decimal.
DOUBLE(size,d) Allows large numbers with floating decimal point. The size parameter is used to specify the maximum number of digits, and the d parameter is used to specify the maximum number of digits to the right of the decimal.
DECIMAL(size,d) Allows storing DOUBLE as a string, so that there is a fixed decimal point. The size parameter is used to specify the maximum number of digits, and the d parameter is used to specify the maximum number of digits to the right of the decimal.

 

String Datatype

Datatype Description
CHAR Length is fixed as you declare while creating a table. When stored, they are right-padded with spaces to the specified length.
VARCHAR  Columns are variable-length strings.

 

 CHAR and VARCHAR (very important)

The CHAR and VARCHAR types are similar, but differ in the way they are stored and retrieved. They also differ in maximum length and in whether trailing spaces are retained.

The CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters you want to store. For example, CHAR(30) can hold up to 30 characters.

The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. When CHAR values are stored, they are right-padded with spaces to the specified length. 

Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535. 

In contrast to CHARVARCHAR values are stored as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value.

CHAR

VARCHAR
It stores values in fixed lengths and are padded with space characters to match the specified length VARCHAR stores values in variable length along with 1-byte or 2-byte length prefix and are not padded with any characters
It can hold maximum 255 characters It can hold maximum 65535 characters

It uses static memory allocation.

It uses dynamic memory allocation.
Fast, no memory allocation every time Slow, as it takes size according to the data stored, so every time memory allocation is done

 

Date and Time Datatype

Datatype Description
DATE Holds the date values in the format: YYYY-MM-DD, where the supported range is  (1000-01-01) to (9999-12-31)
DATETIME A combination of date and time values in the format: YYYY-MM-DD HH:MI:SS, where the supported range is from ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’
TIMESTAMP Similar to DATETIME , but it is written without hyphen. eg: 20200608153000
TIME To store time in the format HH:MM:SS
YEAR(M) To store only year part of data where M may be 2 or 4. for example, year in 2 digit like 19 or 4 digit like 2019

 

Null Value

  • NULL means missing information
  • NULL can appear in any column , unless restricted my NOT NULL or PRIMARY KEY
  • PRIMARY KEY can never be NULL
  • NULL means nothing not 0 or space.

Comments

  • Comments are simply text and they are not executed
  • They are for documentation purposes

Comments in MySQL can be written as follows:

  • begin with /* and */
  • begin with -(followed by space)
  • begin with #

For example, /* select * from students */

SQL Syntax

Command Description
Keywords Words that have special meaning in SQL. 
Clause Used to support mysql commands. Example: where, from
Arguments Arguments are the values passed to clause like table name  to FROM clause

 

Finally after the little boring theory we have reached the interesting part. Now let's learn about different commands and how we can use them.

I will give you the command, its syntax and a screen shot. 

Creating and Using a Database

To create a database , we need to use the create database command

syntax: CREATE DATABASE <DATABASE NAME>;

SQL commands are not case sensitive. So you can write the same in lower case also.

create database

 

To see a list of databases we need to use the show databases command

syntax: SHOW DATABASES;

show databases

Now in order to actually get started we need to select a database. Let's select the class12 database we created earlier. For that we will use the use command.

Syntax: USE <DATABASE NAME>;

use database

Creating and Inserting Data into a Table

To create a table we use the create table command.

Syntax: CREATE TABLE <TABLE NAME> (<COLUMN NAME> datatype(size));

create table

We created a table having fields RollNo, Name and admno

Now to see the structure of the table we can use the describe table command.

Syntax: describe <table name>; or desc <table name>;

describe table command

Comments

Let me know your thoughts, suggestions and doubts here in the comments

by RahulVk

Add your Comment

Read next

Netlify vs Github Pages Speedtest

Github pages and Netlify are among the most popular choices for hosting static sites. Here we compare them on page load times.

RahulVk April 2 2020 · 10 min read

Mi Home Security Camera 360 review

by RahulVk
April 10 · 10 min read

Why you should use a static site generator ?

by RahulVk
March 27 2020 · 15 min read