MySQL
MySQL is a relational database management system (RDBMS) developed by Oracle that is based on structured query language (SQL).
A database is a structured collection of data. It may be anything from a simple shopping list to a picture gallery or a place to hold the vast amounts of information in a corporate network. In particular, a relational database is a digital store collecting data and organizing it according to the relational model. In this model, tables consist of rows and columns, and relationships between data elements all follow a strict logical structure. An RDBMS is simply the set of software tools used to actually implement, manage, and query such a database.
What is difference between DBMS and RDBMS
DBMS and RDBMS both are used to store information in physical database but there are some remarkable differences between them.
DBMS
DBMS applications store data as file.
In DBMS, data is generally stored in either a hierarchical form or a navigational form.
Normalization is not present in DBMS.
DBMS does not apply any security with regards to data manipulation.
DBMS uses file system to store data, so there will be no relation between the tables.
DBMS has to provide some uniform methods to access the stored information.
DBMS does not support distributed database.
DBMS is meant to be for small organization and deal with small data. it supports single user.
Examples of DBMS are file systems, xml etc.
RDBMS
RDBMS applications store data in a tabular form.
In RDBMS, the tables have an identifier called primary key and the data values are stored in the form of tables.
Normalization is present in RDBMS.
RDBMS defines the integrity constraint for the purpose of ACID (Atomocity, Consistency, Isolation and Durability) property.
in RDBMS, data values are stored in the form of tables, so a relationship between these data values will be stored in the form of a table as well.
RDBMS system supports a tabular structure of the data and a relationship between them to access the stored information.
RDBMS supports distributed database.
RDBMS is designed to handle large amount of data. it supports multiple users.
Example of RDBMS are mysql, postgre, sql server, oracle etc.
How to make database connection to mysqli in php
<?php
$con = mysqli_connect("localhost","my_user","my_password","my_db");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
?>
How to make database connection to PHP Data Object in php
<?php
// DB credentials.
define('DB_HOST','localhost');
define('DB_USER','my_user');
define('DB_PASS','my_password');
define('DB_NAME','my_db');
// Establish database connection.
try
{
$dbh = new PDO("mysql:host=".DB_HOST.";dbname=".DB_NAME,DB_USER, DB_PASS,array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));
}
catch (PDOException $e)
{
exit("Error: " . $e->getMessage());
}
?>
What is join in SQL
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
Different Types of SQL JOINs
Here are the different types of the JOINs in SQL:
(INNER) JOIN: Returns records that have matching values in both tables
LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table.
RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table.
FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table.
What is difference between ORDER BY and GROUP BY in SQL
ORDER BY used to sort the result in ascending or descending order. By default records are sort in ascending
Eg- SELECT column1, column2, …FROM tablename ORDER BY column1… ASC|DESC;
GROUP BY used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.
Eg- SELECT columnname FROM tablename WHERE condition GROUP BY columnname
What is difference between FLOAT and DOUBLE
FLOAT and DOUBLE both represents approximate numerical data values. MySql uses four bytes for single precision values and eight bytes for double precision values .
A Float is for single-precision value wheres DOUBLE is for double precision values and DOUBLE allows greater accurcy than FLOAT.
Float saves floating point numbers upto eight places and DOUBLE saves floating point numbers upto 18 places.
How to find second maximum salary
SELECT max(salary) FROM tblname WHERE salary <( SELECT max(salary) FROM tblname )
What is difference between TRUNCATE and DELETE in mysql
1: DELETE is a DML(data manipulation language) command whereas truncate is a DDL(data definition language) command.
2 : Truncate is much faster than Delete.
3 : We can not roll back in truncate but in delete we can rollback.
4 : We can not use where clause in truncate but in delete we can use conditions using where clause
What is difference between PRIMARY key and UNIQUE Key in sql
1: UNIQUE key can be a NULL value but PRIMARY key can not take NULL values.
2:A table can have multiple UNIQUE key but can only one PRIMARY key.
How to concatenate two fields in MySql
In MySQL CONCAT function is used to concatenate two strings to form a single string
Synatx : SELECT CONCAT(column1,column2) FROM tblname.
Ex : SELECT CONCAT(first_name,last_name) FROM employee
What is difference between mysql_connect and mysql_pconnect
1: While using the mysql_pconnect the function would try to find a connection that is already open with same host,username and password ie.(persistant connection) .If connection found an identifier will be returned instead of opening new connection. And with mysql_connect a new connection is always established .
2:While using mysql_pconnect the connection will not closed after the execution of script for future use and in mysql_connect the connection will automatically close when execution of script ends.
3 :mysql_pconnect uses less resources than mysql_connect.
What is difference between CHAR and VARCHAR data types in sql
CHAR used to store fixed length memory storage whereas VARCHAR is used for variable length memory storage .In VARCHAR if we used less space than defined space ,then the remaining space is not wasted , but In CHAR if we use less space than defined space then remaining space is wasted.
CHAR stores only character values whereas VARCHAR stores alphanumeric values
What is difference between MyISAM and InnoDB storage engines in mysql
1 : InnoDB provides us row level locking while MyISAM provides us table level locking.
2 : InnoDB offers foreign key constraints wheres in MyISAM does not have foreign key constraints.
3 : InnoDB does not have full text search wheres MyISAM provides us full text search.
How to get current date in MySql
SELECT CURRENT_DATE();
What type of storage engine mysql support
Below are some storage engines names that a mysql support.
1 : MyISAM.
2 :InnoDB
3 :Memory
4 :CSV
5 :Merge etc.
How to find unique records in MYSQL
SELECT DISTINCT columnname FROM tablename;
How to fetch records sorted in an ascending (asc) or descending (desc)
SELECT col1,col2 FROM tablename ORDER BY col2 DESC;
ELECT col1,col2 FROM tablename ORDER BY col2 ASC;
How to get total number of rows
SELECT COUNT(*) FROM tablename;
How to Delete a column from a table
alter table [table name] drop column
;
How to add a column from a table
alter table [table name] add column [new column name] varchar (20);
How do you return the a hundred items starting from 10th position
SELECT item_name FROM items LIMIT 10, 100.
What is the difference between primary key and candidate key
Every row of a table is identified uniquely by primary key. There is only one primary key for a table.
Primary Key is also a candidate key. By common convention, candidate key can be designated as primary and which can be used for any foreign key references.
Which MySQL Datatype should be used for storing boolean values
For MySQL 5.0.3 and higher, you can use BIT.
For versions lower than 5.0.3 you can use bool and boolean which are at the moment aliases of tinyint(1).
What is the different between NOW() and CURRENT_DATE()
NOW () is used to show current year,month,date, hours,minutes and seconds.
CURRENT_DATE() shows current year,month and date only.
What is command to check table is exist
CHECK TABLE table_name;
Write an SQL query to find names of employee start with ‘A’
SELECT * FROM Employees WHERE EmpName like ‘A%’ ;
What is heap table in MySQL
Tables that are present in memory is known as HEAP tables. When you create a heap table in MySQL,you should need to specify the TYPE as HEAP. These tables are commonly known as memory tables. They are used for high speed storage on temporary basis. They don’t allow BLOB or TEXT fields.