Getting started with MySQL

MySQL is an open-source relational database management system based on SQL — Structured Query Language. The application is used for a wide range of purposes, including data warehousing, e-commerce, and logging applications. The most common use for MySQL however, is for the purpose of a web database.

Database

show

To view all the existing databases you can use the below syntax

show databases;

create

We can create a new database in MySQL by using the CREATE DATABASE statement with the below syntax:

create database db_name;

after creating a new database

delete

For deleting a database we can use the below syntax

drop database db_name;

after deleting the database

select

To select a database we have to use the “use” keyword and then it will return a statement as “database changed”

use studentdb;

Tables

A table is used to organize data in the form of rows and columns and used for both storing and displaying records in the structure format. It is similar to worksheets in the spreadsheet application.

create

For creating a table we have to create it by specifying the columns and what type of data that the column going to store.

create table student_table(
stud_id int NOT NULL AUTO_INCREMENT,
name varchar(30) NOT NULL,
age int NOT NULL,
PRIMARY KEY(stud_id));

Here the auto-increment is used for specifying the id and it will increment automatically, it is also the primary key which means that we cannot have any duplicate values in that column.

Describe

To know the info and the datatypes of the column of the table we use describe keyword.

Describe student_table;

View

Like the database, we can also use the show tables command to view the tables, here the only table is student_table.

show tables;

Insert record

We can insert any record to the database using the insert into command, the datatype of our value should match with the column's datatype.

We can also insert many values at one time the syntax is given below.

insert into student_table(stud_id,name,age)
values (1101,'tom',23);
#multiple values
insert into student_table values
(1104,'sam',22),
(1106,'jack',21),
(1107,'john',21);

View records

For viewing the records that we inserted we can use the below given command.

select * from student_table;

Replace

For replacing any records we have to use where clause

update student_table
set age = 24,name = 'raj'
where stud_id = 1101;

After replacing

Delete record

We can delete a single row or entire table or by limiting the order using the below commands.

delete from student_table where id=1102;#single row
delete from student_table;#entire table
delete from student_table order by stud_id limit 2; #limiting

Select column

For selecting the whole table

select * from student_table;

Specific rows and columns mean use where clause to filter.

select name,age from student_table;

Add new column

We can add one or multiple columns at the same time using the alter keyword.

alter table student_table add column city varchar(30) NOT NULL;
alter table student_table
add column stud_class varchar(30) default null after age,
add column stud_marks int default null after age;

After adding new columns

Drop a column:

Here we are deleting the stud_marks and stud_class from the table.

alter table student_table
drop column stud_marks,
drop column stud_city;

After deleting the columns

Indexing

Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records.

There are two types of indexing - clustered index and non-clustered index

create index stud_index ON student_table(stud_name);

Group By :

Group by statement is used to group the rows that have the same value. It is often used with aggregate functions for example:AVG(), MAX(), COUNT(), MIN() etc. One thing to remember about the group by clause is that the tuples are grouped based on the similarity between the attribute values of tuples.

select name,count(age) as n_age from student_table GROUP By name;

select count(name),stud_id from student_table GROUP BY stud_id;

Order By

Order by keyword sort the result-set either in ascending or in descending order. This clause sorts the result-set in ascending order by default. In order to sort the result-set in descending order DESC keyword is used.

select stud_id,name from student_table order by age asc;

select stud_id,name,age from student_table where stud_id>1103 order by age desc;

Insert on duplicate key update

The INSERT ON DUPLICATE KEY UPDATE is MySQL’s extension to the SQL standard’s INSERT statement. When you insert a new row into a table if the row causes a duplicate in the UNIQUE index or PRIMARY KEY, MySQL will issue an error. If the existing row is updated using its current values, the number of affected rows is 0.