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

How to add comment to a column in MySQL?

Sometimes wee need to add a comment to a column for understanding purpose means to easily identify what is the use of that particular column. We can add comment to a column by two ways- at the time of table creation or by ALTER command after ...

How to make a column as first column in MySQL?

Sometimes we need to make existing column as first column. For example you created a table and added column "id" after creating the column. Now you want to make "id" column as first column, then you can do this easily by using ALTER command as be...

How to add foreign key contraint to a column in MySql?

In many cases we need to create foreign key in a table. We can do this at the time of table creation and after the table creation by ALTER command. 1- At the time of table creation: CREATE TABLE user_device ( id int(11) NOT NULL AUTO...

How to add comment on a table in MySQL?

Sometimes wee need to add a comment on a table for more readability means to define what is the use of that table. We can do this at the time of table creation or by ALTER command after creating the table. At the time of table creation: ...

How to create date column in MySQL to take current date automatically when row inserted

The TIMESTAMP data type is the only data type which is used to have MySQL automatically set the time when a row is inserted and/or updated. DATETIME columns cant do this. We can define more than one TIMESTAMP column in a table, but only one TI...

Find where field is not null in cakephp

Hello Readers! if you have a database table "users" which has a field "email". And you are looking for a query that fetch out every email field only which are not null in cakephp Here is the following code below you can use, and its working ...

How to list table names using primary key of a table

Hello all, I have approx 100 number database tables in db from that I want to list all the tables which is using primary key of a table as a reference in MySQL InnoDB?

Read and Write JSON data in MySQL

Store JSON in MYSQL:- Here below snippet will show you how we can store json data in mysql and read the json data from mysql and convert into a java object. Remember that the field where you can store the JSON data in mysql to set data type of t...

MySQL Storage Engines

MySQL comes with a variety of storage engines. Each and every storage engine has some particular behavior and traits. It is very much important to know about these engines in order to create a well designed database. MySQL stores each database(a...

Indexed full text search in PHP-MySQL (PART - 2)

We already discussed Natural full text search in Indexed full text search in PHP-MySQL (PART - 1). Lets look into Boolean Full Text Search. In this we can use various operators to change the behavior of search. To specify a search to be b...

Stored Procedures and Functions in MySQL- Part 6

Loops A lot many times, situation arises that a set of commands have to be repeatedly executed. This is achievable by using looping constructs. MySQL provides us three types of looping constructs : Simple Loops Repeat Loops While L...

Stored Procedures and Functions in MySQL- Part 4

Conditional Constructs - CASE As in any programming constructs even in stored routines we might need to execute different commands based on what the variable or expression evaluates to. For the same we have CASE.... END CASE and IF...ELSEIF......

Connection string for connecting Android(java) app to MariaDB

Here is the simple connection string to connect the android with Maria DB on linux. you need to have MariaDB configured to the default port:3306 try { Class.forName("org.mariadb.jdbc.Driver"); Connection cntn = DriverManager.getConne...

Exporting table from Amazon RDS into a csv file

A simple command to export tables from Amazon RDS into a csv file mysql -uroot -ptest123 --database=test -e "select concat(id,',',login) FROM users" > userdata.csv This command will simply connect to mysql from terminal using root as u...

Stored Procedures and Functions in MySQL - Part 2

How to manage stored routines? In the second part of the series we will see basic commands used to manage the Stored Routines As these are also Database objects so the basic DDL commands hold true here also Create Alter Drop CRE...

Stored Procedures and Functions in MySQL- Part 1

Why do we need stored routines? In this multi-part series I will explain the need of stored routines commonly know as stored procedures and functions , how to manage them, how to declare variables, programming blocks (loops, how to perform con...

How to create a trigger in MySQL

Many a times, we have come across a need that execution of DML statement on one database table should lead to modification in another table with some specific set of values. Whats the novice way to do? Simple, fire the first modification query th...

Top 10 MySQL queries every developer should know

As developers of any technology which uses MySQL as backend we always need to know some basic queries to work on. The list may vary as per the development need but a basic set still remains the same. The very first query that comes into use...

How to optimize and repair all databases and tabels in mysql?

Optimization of database tables in mysql is a methodology which must be done frequently to make your database healthy. As per mysql manual, OPTIMIZE TABLE should be used if you have deleted a large part of a table or if you have made ...

Change the collation of database, tables and columns

If we have added wrong collation name of the database and its columns the we can get some problem with other languages and special character. All other languages and characters will appear as ????? So we can alter collation of database and tabl...

How to test My SQL Injection

SQL injection attack can access the sensitive data from the database. The attacker can Insert, Update, Delete and execute the administration operation. Detection: Authentication Forms : When user enter the web form, chances are that the user...

Mysql Function To Rounding number to nearest 1,10,100,1000

Below is the Mysql Function To Rounding number to nearest 1,10,100,1000 DELIMITER $$ DROP FUNCTION IF EXISTS `rounding_to_nearest`$$ CREATE FUNCTION `rounding_to_nearest`(num decimal(15,2),round_to...

ERROR: Error 1005: Can't create table (errno: 121)

Sometimes we get this error on creating table in MySQL.This error will come when we use the constraint with the same name that is already used somewhere else. If the table you're trying to create includes a foreign key constraint, and you've p...

Indexes in MySQL

Indexes in MySQL can improve performance of specific queries Like as below Examples : Syntax for creating index on one field: CREATE INDEX indx_magrno ON EmplyeTable (EmplyeID); These two indexes is supposed to hugely improve performan...

How to enable slow query logs

For this we need to edit my.cnf file (This should be in /etc directory) we need to enter following lines :- log-slow-queries=/logs/mysql/slowQueries.log long_query_time=30 This time is in seconds , so if any query wi...

How to use regular expression in mysql query?

By using REGEXP function of mysql.Below is the example via a MySQL Query select empId,empName from tbl_employee where empName REGEXP [1-4]$; This will select the data of all employees whose name ends with either 1 or 2 or 3 or 4

Not able to add Foreign key on table column

Error Code : 1005 Can't create table 'DatabaseName.#sql-9cc_1' (errno: 150) (0 ms taken) After searching a lot i have got solution for the above problem while i was adding FK to a column. The error was because of different MySQL engine. The...

Difference between Decimal,Float,Double in terms of exact calculation

Difference between float,double and decimal in terms of exact calculation(Tested in mysql) I was creating an application where I need exact values from mysql procedure so I have to find better data type which gives me exact value so I tested i...
1 3 next
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: