MySql: Querying data from tables

  DML (Data Manipulation Language) Statements The commands in MySql to insert/update/delete the records are DML Statements. Insert into, Upd...

 

DML (Data Manipulation Language) Statements

The commands in MySql to insert/update/delete the records are DML Statements. Insert into, Update, Delete command is the DML statements.

DDL (Data Definition Language) Statements

 The commands which are related to the structure of the table like Create table,Alter table, Alter Constraint.

DCL (Data Control Language) Statements

The commands which are related to provide the privileges to the users on mysql objects like tables/sp/functions etc.Grant,Revoke are the DCL Statements.

Some DCL Statements

1. CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'password';
2. GRANT ALL PRIVILEGES ON *.* TO 'sammy'@'localhost' WITH GRANT OPTION;
3. FLUSH PRIVILEGES;
4. SHOW GRANTS FOR 'sammy'@'localhost';
5. REVOKE type_of_permission ON database_name.table_name FROM 'username'@'host';
6. DROP USER 'sammy'@'localhost';

Constraints in Mysql

NOT NULL - Ensures that a column cannot have a NULL value

UNIQUE - Ensures that all values in a column are different

PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table

FOREIGN KEY - Prevents actions that would destroy links between tables

CHECK - Ensures that the values in a column satisfies a specific condition

DEFAULT - Sets a default value for a column if no value is specified

Example: Create a table Person with Primary key and not null constraint

CREATE TABLE Persons (

    ID int NOT NULL,

    LastName varchar(255) NOT NULL,

    FirstName varchar(255),

    Age int,

    PRIMARY KEY (ID)

);

Let's try to insert duplicate value to Id field

    Insert into Persons values(1,'Sharma','Raman',40);

    Insert into Persons values(1,'Sharma','Manoj',40);

ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

Example: To Create Persons table with multiple primary key.

CREATE TABLE Persons (

    ID int NOT NULL,

    LastName varchar(255) NOT NULL,

    FirstName varchar(255),

    Age int,

    CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)

);

    Insert into Persons values(1,'Sharma','Raman',40);

    Insert into Persons values(1,'Sharma','Manoj',40);x

ERROR 1062 (23000): Duplicate entry '1-Sharma' for key 'PRIMARY'

You can't insert Duplicate ID and Name together

Foreign key Example

Create a table student

    create table student ( id int Primary key, name varchar(20));

Create table sports which is referring to students.

    create table sports( spotsid int Primary key, sportsname varchar(20), id int, FOREIGN KEY id References student(id));

Now you cannot insert in sports table a record which is not in the student table.

Querying data from tables

Query all data from a table:

SELECT * FROM table_name;
Code language: SQL (Structured Query Language) (sql)

Example:

1.     select * from student;
2.      select * from student where marks>80;
3.      select * from student where name='raman';
4.      select * from student where name in ('Rohit','Shaik');
5.     select * from student where name not in ('Rohit','Shaik');
6.     select * from student where name like 'S%';
7.      select * from student where name like '%t';
8.      select * from student where name like '_____'; -- 5 underscore
9.      select * from student where name!='Rohit';
10.      select distinct Grade from student;
11.      select * from student where marks between 80 and 85;
12.     select min(Marks) as Min from student where Grade='A';
13.     select count(*) as Max from student where Grade='A';
14.     select count(distinct grade) from student;
15.    select max(Marks) as Max from student where Grade='A';
16.    select sum(Marks) as Total from student where Grade='A';
17.    select avg (Marks) as Average from student where Grade='A';
18.    select name, marks + 10 as Marks from student;
19.    insert into student (id,name) values (6,'Shyam'); -- inserts only id and name column values
20.    insert into student  values (6,'Shyam',90,'A'); -- inserts a complete row.
21.    update student set marks=85 where id=6;
22.   delete from student where grade is null;
23.    

SubQuery ( nested query)

   select * from student where marks > (select marks from student where name='Rohit');

   select * from student where marks < (select marks from student where name='Rohit');

   select * from student where id in (select id from sports where sportsname='Hockey');

Joins

For joins at least one matching key between the join tables

Inner join 

It displays all the matching records between join tables.

Example:- To find out all the students names who are participating in sports.

select name,sportsname from student s join sports sp on s.id=sp.id;

Example:- To findout all the student names who are participating in Hockey sports.

select name,sports name from student s join sports sp on s.id=sp.id where sportsname='Hockey';

Left Outer Join

It will display all the records of left table and matching records from the right table.

Example: To display all the names of students along with the names of their sports. All the names should be displayed even though they are not participating in any sports.

select name,sportsname from student s left join sports sp on s.id=sp.id;

Right Outer join

It will display all the records of right-hand side table and the matching records from Left-hand side table.

Cross Join

It displays the cartesian product of join tables

select * from student, sports;

Group By Clause

It groups on the basis of a group column and It always used with aggregate funciton(sum,count,avg,min,max)

Example: - To find the count of student grade wise.

select Grade,count(*) from student group by Grade;

Example:- To find the count of students grade-wise but for those students whose count is more than 1

select Grade,count(*) from student group by Grade Having count(*)>1;

Query data from one or more column of a table:

SELECT 
    column1, column2, ...
FROM 
    table_name;
Code language: SQL (Structured Query Language) (sql)

Remove duplicate rows from the result of a query:

SELECT 
    DISTINCT (column)
FROM 
   table_name;
Code language: SQL (Structured Query Language) (sql)

Query data with a filter using a WHERE clause:

SELECT select_list
FROM table_name
WHERE condition;
Code language: SQL (Structured Query Language) (sql)

Change the output of the column name using column alias:

SELECT 
    column1 AS alias_name,
    expression AS alias,
    ...
FROM 
    table_name;
Code language: SQL (Structured Query Language) (sql)

Query data from multiple tables using inner join:

SELECT select_list
FROM table1
INNER JOIN table2 ON condition;
Code language: SQL (Structured Query Language) (sql)

Query data from multiple tables using left join:

SELECT select_list
FROM table1 
LEFT JOIN table2 ON condition;
Code language: SQL (Structured Query Language) (sql)

Query data from multiple tables using right join:

SELECT select_list 
FROM table1 
RIGHT JOIN table2 ON condition;
Code language: SQL (Structured Query Language) (sql)

Make a Cartesian product of rows:

SELECT select_list
FROM table1
CROSS JOIN table2;
Code language: SQL (Structured Query Language) (sql)

Counting rows in a table.

SELECT COUNT(*)
FROM table_name;
Code language: SQL (Structured Query Language) (sql)

Sorting a result set:

SELECT 
    select_list
FROM 
    table_name
ORDER BY 
    column1 ASC [DESC], 
    column2 ASC [DESC];
Code language: SQL (Structured Query Language) (sql)

Group rows using the GROUP BY clause.

SELECT select_list
FROM table_name
GROUP BY column_1, column_2, ...;
Code language: SQL (Structured Query Language) (sql)

Filter group using the HAVING clause:

SELECT select_list
FROM table_name
GROUP BY column1
HAVING condition;
Code language: SQL (Structured Query Language) (sql)

Modifying data in tables

Insert a new row into a table:

INSERT INTO table_name(column_list)
VALUES(value_list);
Code language: SQL (Structured Query Language) (sql)

Insert multiple rows into a table:

INSERT INTO table_name(column_list)
VALUES(value_list1),
      (value_list2),
      (value_list3),
      ...;
Code language: SQL (Structured Query Language) (sql)

Update all rows in a table:

UPDATE table_name
SET column1 = value1,
    ...;
Code language: SQL (Structured Query Language) (sql)

Update data for a set of rows specified by a condition in WHERE clause.

UPDATE table_name
SET column_1 = value_1,
    ...
WHERE conditionDelete all rows in a table
DELETE FROM table_name;
Code language: SQL (Structured Query Language) (sql)
Delete rows specified by a condition:
DELETE FROM table_name
WHERE condition;
Searching
Search for data using the LIKE operator:
SELECT select_list
FROM table_name
WHERE column LIKE '%pattern%';