Showing posts with label MYSQL. Show all posts
Showing posts with label MYSQL. Show all posts

Tuesday, 25 July 2017

MySQL User defined variables and System variables defined variables

MySQL User defined  variables and System 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 @[email protected][email protected]; //60
select @[email protected][email protected]; //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;

Thursday, 5 January 2017

What is Stored Procedure in MySQL?

What is Stored Procedure in MySQL?

Question: What is Stored Procedure?
It is procedure (written in SQL) stored in a database which can be called by the database engine and connected programming languages.


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="[email protected]";   
    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('[email protected]');



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


Monday, 12 December 2016

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

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

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



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

Output

Get record counts for all tables in MySQL database


Wednesday, 12 October 2016

How to install and Connect to MySQL in NodeJS?

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 instal new package in nodejs?
npm install packagename



Question: How to instal 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:[email protected]/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:[email protected]/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:[email protected]/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:[email protected]/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');
    }
});



Monday, 3 October 2016

What is Super Key, Candidate keys and Alternate Key?

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.



Monday, 22 February 2016

MySQL Query Optimization Tips and Techniques

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:
  9. DESCRIBE SELECT * FROM `users` WHERE username='web-tech' 
  10. Partition your table (MySQL 5.1). Paritioning is a technique for splitting a large table into several smaller ones by a specific (aggregate) key.
  11. Partition can be achieve in three way 1. RANGE, HASH and List/Key.
  12. Build your indexes to match the queries running. For Example:
    ALTER TABLE `users` ADD INDEX `profile_id` (`profile_id`)
    Know about MySQL Indexing
  13. Use concatination indexing, if required (When searching like below).
    SELECT * FROM table_name  WHERE field_name1='text1' AND field_name2='text2';
  14. No indexing on column which not used in search.
  15. Sometime you need to use OPTIMIZE Table. For Example:
    OPTIMIZE TABLE `users`
    Used for defragment tables and update the InnoDB fulltext index
  16. Full Text Search can be used, if required.
    http://www.web-technology-experts-notes.in/2013/05/mysql-fulltext-search.html

Thursday, 18 February 2016

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

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'



Saturday, 28 November 2015

Comparison between MySQL and MongoDB

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

Thursday, 15 October 2015

DBMS Interview Questions and Answers for Freshers


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.



Monday, 6 April 2015

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




Sunday, 22 March 2015

Difference between inner join and outer join in MySQL

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




Wednesday, 18 March 2015

How to get a list of MySQL user accounts?

How to get a list of MySQL user accounts?

In MySQL, you can get list of user account with user details. Just execute following Query of PHPMySQL/SQL Yog/Other MySQL Tools.

List of MySQL users.
SELECT User FROM mysql.user


List of MySQL unique users.
select distinct User from mysql.user;

List of MySQL users with password and host.
SELECT Host,User, Password FROM mysql.user


List of MySQL unique Users with password and host.
SELECT Host,User, Password FROM mysql.user  group by User



List of MySQL users with all permissions.
SELECT * FROM mysql.user WHERE 1






Friday, 6 February 2015

How to delete MySQL Binary logs Files in Wamp Server?

How to delete MySQL Binary logs Files in Wamp Server?



Sometime we find our Wamp Server is talking too much space like 10GB whereas it must take less than the current size. It may be your mysql binary log files are taking too much space in data.
Binary files look like below:
mysql-bin.000001,
mysql-bin.000002,
................,
mysql-bin.000221

Before we delete the mysql binary log files permanently, we should know about these binary log files like what type of files are these and how effect our server.


Question: What is Mysql Binary logs file?
MySQL binary logs are the text file, created by mysql automatically. Default files name are "mysql-bin.000001" which located in data folder of mysqlx.x.


Question: What is purpose of Binary logs file?
It has two purpose
Data Recovery: It is used for data recovery operations.
High availability / replication: It is used on master replication servers as a record of the statements to be sent to slave servers.


Question: What is default location of binary log file?
E:\wamp\bin\mysql\mysql.x.x\data


Question: How can change the Binary log file name?
Open my.ini file under mysql.x.x folder.
In my window system:
E:\wamp\bin\mysql\mysql.x.x\my.ini


Question: Update the following "mysql-bin" name
log-bin=mysql-bin


Question: How to disable the automatic creating binary log files?
Open my.ini file under mysql.x.x folder.
In my window system: E:\wamp\bin\mysql\mysql.x.x\my.ini
Search
log-bin=mysql-bin
and replace with following
#log-bin=mysql-bin



Question: What is dis-advantage of mysql binary log files?
Its takes too much space, some times it take upto 10MB or more space.


Question: How to delete mysql bin files permanently?
It have 3 steps and are following.

Step 1: Disable automatic creating file.
open my.ini file under mysql.x.x folder.
Search
log-bin=mysql-bin
and Replace with following
#log-bin=mysql-bin

Step 2:
Restart Wamp Server

Step3 :
Now you can delete all files start with "mysql-bin.0000xx"

Never delete mysql bin files directly you must follow Step 1






Thursday, 8 January 2015

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 2269548 for key PRIMARY

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 2269548 for key PRIMARY

Following are few reasons with solutions:

Reason 1:
ID is auto-increment and primary key. id is auto filed when an record is insert.
When we try to insert record with same id which is already exist in table, then such type of error comes.
SOLUTION:
So, write SQL in following ways.
INSERT INTO users (name) VALUES ('name1'); //skip the id field which is auto-increment


Reason 2:
When table is used to frequent insertion of records, like we are storing db logs for each action. Sometimes, it have thousand of thousands records, and field(like id) limit cross the limit. SOLUTION: Increase the limit of id field.
ALTER TABLE `users` CHANGE `id` `id` BIGINT(10) UNSIGNED NOT NULL AUTO_INCREMENT; 
Reason 3:
When table is used to frequent insertion of records, like we are storing db logs for each action.Sometimes, it have thousand of thousands records, and table goes to overhead.
SOLUTION: Execute following query
OPTIMIZE TABLE `users`