Followers

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%';

COMMENTS

Name

Ansible,6,AWS,1,Azure DevOps,1,Containerization with docker,2,DevOps,2,Docker Quiz,1,Docker Swarm,1,DockerCompose,1,ELK,2,git,2,Jira,1,Kubernetes,1,Kubernetes Quiz,5,SAST DAST Security Testing,1,SonarQube,3,Splunk,2,vagrant kubernetes,1,YAML Basics,1,
ltr
item
DevOpsWorld: MySql: Querying data from tables
MySql: Querying data from tables
DevOpsWorld
https://www.devopsworld.co.in/2022/04/mysql-querying-data-from-tables.html
https://www.devopsworld.co.in/
https://www.devopsworld.co.in/
https://www.devopsworld.co.in/2022/04/mysql-querying-data-from-tables.html
true
5997357714110665304
UTF-8
Loaded All Posts Not found any posts VIEW ALL Readmore Reply Cancel reply Delete By Home PAGES POSTS View All RECOMMENDED FOR YOU LABEL ARCHIVE SEARCH ALL POSTS Not found any post match with your request Back Home Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sun Mon Tue Wed Thu Fri Sat January February March April May June July August September October November December Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec just now 1 minute ago $$1$$ minutes ago 1 hour ago $$1$$ hours ago Yesterday $$1$$ days ago $$1$$ weeks ago more than 5 weeks ago Followers Follow THIS PREMIUM CONTENT IS LOCKED STEP 1: Share to a social network STEP 2: Click the link on your social network Copy All Code Select All Code All codes were copied to your clipboard Can not copy the codes / texts, please press [CTRL]+[C] (or CMD+C with Mac) to copy Table of Content