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.
- Data types
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'.
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.
- String types
- Date time
Now let's take a look at numeric data types in SQL
|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.|
|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)
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.
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.
VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535.
In contrast to
VARCHAR 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.
|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
|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 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 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 */
|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.
To see a list of databases we need to use the show databases command
syntax: 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>;
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));
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>;