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.