Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 

What is the use of HAVING Clause in MySQL?

The HAVING clause is used with aggregate functions as the WHERE clause can not be used with them. HAVING Syntax SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HA...

what is the use of FORMAT() function in SQL?

In SQL, FORMAT() function is used to format a column value means how a field will to be displayed. FORMAT() Syntax SELECT FORMAT(column_name,format) FROM table_name; In the above syntax parameters are as below: Parameter Descri...

what is the use of NOW() function in SQL?

In SQL, NOW() function is used to return the current system date and time. NOW() Syntax SELECT NOW() FROM table_name; We have a table "employee" as below: employee id first_name last_name salary ........

what is the use of LEN() function in MySQL?

In MySQL, LEN() function is used to return the length of the value in a column (text field) in a table. LEN() Syntax SELECT LEN(column_name) FROM table_name; We have a table "employee" as below: employee id first_name ...

How to use SUM() function in MySQL?

In MySQL, SUM() function is used to return the total sum of a numeric column in a table. SUM() Syntax SELECT SUM(column_name) FROM table_name; We have a table "employee" as below: employee id first_name last_nam...

Difference between TRUNCATE and DROP in MySQL.

TRUNCATE TRUNCATE comes under DDL(Data Definition Language). TRUNCATE removes only rows from the table but the structure of the table remains same. Data can not be roll backed if we use "TRUNCATE" command to delete data. Can not use WHERE...

What is DEFAULT Constraint in MySQL?

In MySQL, the DEFAULT constraint is used to insert value into a column in a table. If there is no value is specified for a column then the default value will be added to column for all new records. DEFAULT Constraint on CREATE TABLE The fol...

What is CHECK Constraint in MySQL?

In MySQL, the CHECK constraint is used to put limit on value range on value inside a column. CHECK Constraint on CREATE TABLE The following statement creates a CHECK constraint on the "id" column when the "employee" table is created. The CH...

What is FOREIGN KEY Constraint in MySQL?

Note that the "P_Id" column in the "Orders" table points to the "P_Id" column in the "Persons" table. In MySQL, the FOREIGN KEY on one table is used to point a PRIMARY KEY in another table. We have two tables with the following values. ...

What is PRIMARY KEY Constraint in MySQL?

In MySQL, the PRIMARY KEY constraint is used to uniquely identify each record in a table. Primary key contains UNIQUE values only and the column defined as Primary key column can not be NULL. A table can have only one PRIMARY KEY. PRIMARY K...

SQL Tutorial -> Sql Inner Join

<-- Chapter 25: SQL Join Chapter 26 SQL Inner Join SQL Inner Join clause is used to fetch all the rows from more than two tables in which there is a match between the columns. Inner Join is similar like Join clause. Lets see an ...

What is UNIQUE Constraint in MySQL?

In MySQL, the UNIQUE constraint is used to uniquely identify each record in a table. UNIQUE Constraint on CREATE TABLE The following statement creates a UNIQUE constraint on the "id" column when the "employee" table is created: Example ...

What is NOT NULL Constraint in MySQL?

In MySQL, the NOT NULL constraint is used to restrict a column to NOT accept NULL values. The NOT NULL constraint allows a column to always contains a value which means you cant not insert/update a record in a table without passing value for t...

Difference between DELETE and TRUNCATE in MySQL.

In MySQL, DELETE and TRUNCATE both are used for deleting data from table. DELETE DELETE comes under DML(Data Manipulation Language). DELETE can be used to delete a particular row by using WHERE clause. It is slower than TRUNCATE as...

How to use MIN() function in MySQL?

In MySQL, MIN() function is used to return the smallest value of the specified column in a table. MIN() Syntax SELECT MIN(column_name) FROM table_name; We have a table "employee" as below: employee id first_name ...

How to use AVG() function in MySQL?

What is the use of AVG() function in MySQL? The AVG() function is used to return the average of the numeric column in a table. AVG() Syntax SELECT AVG(column_name) FROM table_name; We have a table "employee" as below: employe...

How to use MAX() function in MySQL?

In MySQL, the MAX() function used to return the largest value of the specified column in a table. MAX() Syntax SELECT MAX(column_name) FROM table_name; We have a table "employee" as below: employee id first_name l...

What is the use of COUNT() function in MySQL?

In MySQL, COUNT() function used to return the number of rows that matches with a specified criteria. Syntax: COUNT(column_name) When we use COUNT(column_name) function in select query it returns the number of rows for the specified colum...

What is NULL value in MySQL?

When we don't define any data for a column then the column holds the NULL value. In MySQL, NULL values are used to represent data that is missing. NULL Values If you have created a table with optional columns then you can insert and update ...

What is Auto-increment keyword in MySQL?

Auto-increment is used to allow a unique number when a new record inserted into a table. Generally we use Auto-increment to create value for primary key field automatically when a new record is inserted into a table. Syntax: The below stat...

Laravel MySql RAW Update, Delete or Select

Hi All, Recently I needed to update, delete and select using RAW query in Laravel 5.0. When I searched the internet the only query I was able to find out is the select query but I need to accomplish both update and delete also.So after little ...

How can we prevent SQL-injection in PHP?

Hello Readers , For preventing SQL injection we can do by two ways : 1- > Escaping the special characters in your post or get variables , or 2-> By using a parameterized query. Both would protect you from SQL injection. Ex...

How to delete data from a table in MySQL?

To delete data from a table we can use either DELETE or TRUNCATE. DELETE If we want to delete a record from table then we use DELETE FROM statement. Syntax: Below statement deletes rows from table on the basis of where condition, we...

How to delete table/database in MySQL?

We can drop table or database by using DROP keyword. Syntax: The DROP TABLE statement is used to delete a table. DROP TABLE table_name; The DROP DATABASE statement is used to delete a database. DROP DATABASE database_name; E...

How to delete an INDEX in a table in MySQL?

We can drop INDEX from table by using DROP INDEX statement, it is used to delete an index form a table. Syntax: ALTER TABLE table_name DROP INDEX index_name; Example: Suppose you have created an INDEX named "person_user_name_idx" on th...

How to create INDEXES in tables in MySQL?

We create INDEX in a table to find data data fast and efficiently. It allows the database to find data without reading all the records of the table. When we apply search and queries to table INDEX are used to make them fast. To create indexes i...

How to add a column into a table in MySQL?

Sometimes we need to add a column that we require into the table. We can do this by using ADD keyword with ALTER command. Syntax: To add a column into a table, use the following syntax: ALTER TABLE table_name ADD COLUMN column_name data...

How to drop a column from table in MySQL?

Sometimes we need to drop a column that we don't need further from the table. We can do this by using DROP keyword with ALTER command. Syntax: To drop/delete the column from a table, use the following syntax: ALTER TABLE table_name DROP...

How to change the datatype of a column in MySQL?

Sometimes we need to change the data type of a column in a table. We can do this by using Modify keyword with ALTER command. Syntax: To change the data type of a column in a table, use the following syntax: ALTER TABLE table_name MODIFY...

Difference between UNION and UNION ALL operators in MySQL.

In MySQL UNION operator used to combine the result of multiple SELECT statements. When we apply the UNION operator then it selects only distinct values by default (No duplicate values are allowed). UNION Operator When we apply UNION operato...

How to insert data from one table to another in MySQL?

We can insert data from one table to another in MySQL. For this we use the INSERT INTO SELECT statement that selects the data from on table and inserts that data into another table. INSERT INTO SELECT Syntax We can select and insert all the...

How to use Order By in Zend Framework

Hello Friends, If you are looking to use order by clause in Zend Framework. Please use the below code for the same:: $dbObj = Zend_Db_Table::getDefaultAdapter(); $selectObj = $this->select(); $selectObj->from(array('tbl_users'),arra...

Difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN in MySQL

In MySQL join is used to combine records from 2 or more tables based on a common column between them. INNER JOIN: It returns all rows when there is a match in both tables. LEFT JOIN: It returns all rows from the left table and matched rows ...

How to get count of duplicate values from table in mysql?

Sometimes we need to check how many duplicate values a column has in MySQL table. We can do this by using "group by" and "having". Example: Suppose you have a table "user" with "fist_name" column from which you want to find all the records w...

How to count unique values from table in mysql?

Sometimes we have the requirement to count how many unique values are in the table. We can do this by using "Distinct" and count(). SELECT COUNT(DISTINCT column_name) FROM table_name; Example: Suppose you have a table user from which you ...

How to check indexes for a database or table in mysql ?

There are chances that you want to check for the indexes that you created on the tables in past because there can be a large number of indexes on numerous of tables depending on the size of the database If you want to see the indexes for a par...

Mysql order by values within IN() function

Hello guys, Most of time we face the problem order by within IN() values . Mysql provides the function FIELD(), It will sort fetched IN() data according to requirment . The FIELD function returns the position of the first string in the remaining...

Function in SQL

Function in SQL There are two type of function in sql SQL aggregate function 1) AVG() - Returns the average value SELECT AVG(column_name) FROM table_name 2) COUNT() - Returns the number of rows SELECT COUNT(column_name...

Save MySQL query results into a text or CSV file

Hello Readers! MySQL provides an easy mechanism for writing the results of a select statement into a text file on the server. Given a query such as SELECT order_id,product_name,qty FROM orders which returns three columns of data, t...

Cloning a MySQL database on the same MySql instance

Hi Guys, There are chances when we need to have a backup of the old database as a separate database so that if we make some experiments we should have the old database as a safe database which we can use again in the same state. So in mysql we...

How to use GROUP_CONCAT in a CONCAT in mysql

GROUP_CONCAT will always return a string value . It can be easily explain by the example. Example: bookid | bookname | isbnno | cateid | autid | pubid | BK001 | Introduction to Electrodynamics | 0000979001 |...

Laravel 5.0 Writing Static Query In Laravel

Many times we needs to write static query in Laravel. Some time due to the complexity of the query or sometime due to the MYSQL build in functions not supported in the Query Builder or in Eloquent in Laravel example LPAD. Thus at that time Larave...

MYSQL, PHP :- Query Concatenating Fields To Make Date And Running Between Query On It

Recently, I faced a problem when the date was saved in different fields month, year and date. I need to club all these fields together and run a between query on it. Another issue which was there was that Date has '0' contacted if the value ...

query roll back

hey ci experts can you help me plz where im making mistake.... my query is not roll backed although im setting the first parameter in the $this->db->trans_start(TRUE); as TRUE the queries are only rollback when i remove the $this->db-...

How to import mysql database using command line?

Sometimes when you have large databases files and you are unable to import the database manually. Then the easiest (and fastest) way is to use command line. Import:-- 1. Run the cmd (DOS) and get into the mysql folder, which in my case wo...

android google map is showing only one marker using volley library

I am using Google Map inside the "Fragment" using sqlite database connection through volley library. I have 4 location, but my map is showing only one marker. I don't know where exactly getting wrong. Where do I need to change in my code to...

Mysql and Sql injections

SQL Injection: If we are trying to save data into the database from webpage inputs than we have forget wide open security issue is known as SQL injection. Now the Question is how to prevent it and help to secure your script and MYSQL statement. ...

How to change default Character set of Schema?

Sometimes we need to change default Character set of Schema. We can do this easily by using ALTER command. Command: ALTER SCHEMA database-name DEFAULT CHARACTER SET utf8 ; Example: ALTER SCHEMA `jeeyoh` DEFAULT CHARACTER SET utf8 ;...

How to export Schema without data in MySQL?

Sometimes we need to export Schema without data means table structure only. We can do this easily by providing "--no-data" option in mysqldump command. Command: mysqldump -u root -p --no-data database&#95;name > database.sql Exa...

How to add a column after a particular column in MySQL?

Sometimes we need to add a column after a particular column. We can do this easily by using ALTER command with AFTER attribute. Example: in the below command I'm adding "latitude" column after "address" column where "address" column already ex...
Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: