Wednesday, June 8, 2016

Some Useful My Sql Queries for beginners


My Sql is a popularly used Relational Database Management System.  Database is used store and retrieve data efficiently with the proven algorithms. Here i'm providing some sql commands which are useful for beginners in my sql queries.

Here information_schema is the default database comes with Mysql installation.

>To check the engine used for the table - mytable
SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_NAME='mytable' AND TABLE_SCHEMA='mydatabase';

>To see the database create statement
SHOW CREATE DATABASE mydatabase;

>TO see the current database name
SELECT DATABASE();

>To see the table structure (create table statement)
SHOW CREATE TABLE mydatabase.mytable;

> Describes the all the columns and datatypes for the table
DESCRIBE mytable (or) DESC mytable;

>To see the warnings of the previous command
SHOW WARNINGS;
>To concatenate (Combine) and show the column vaues
SELECT CONCAT(firstname, ' . ', lastname) AS `Full Name`, marks FROM mytable;

>To Select rows in random order every time
SELECT * FROM mytable ORDER BY RAND();

>  To retrieve multiple rows with the given values, for example to fetch 3 records data with column "uid" values as 5,6,8
SELECT * FROM mytable WHERE uid IN (5,6,8);

> To get summary report for the groups use GROUP BY aggregate functions(COUNT, MIN, MAX, AVG, SUM...) on the groups, for example to get the total number of students for each branch
SELECT COUNT(*) from mytable GROUP BY branchname;
  • To get the average marks in subject-wise
 SELECT AVG(marks) from classtable GROUP BY subjectname;
  • To get the sum of the items in the stock
 SELECT SUM(items) from stock GROUP BY itemname;

>To combine the results of two or more tables(identical) use UNION, for example to get the highest marks in the particular subject from class9table, class10table

SELECT MAX(science) FROM (SELECT MAX(science) from class9table UNION SELECT MAX(science) from class10table) as t1






No comments:

Post a Comment