Taking dump of query result into file

I have users table in  my database and If you want to take all user rows data into file, then execute below query..
SELECT id,user_name, address into OUTFILE  ‘d://userdbdump.txt’ FIELDS TERMINATED BY ‘,’ FROM users

Read Full Post »

Joins In MySql

Create table by using these statements
create table apples (variety char(10) not null primary key, price int not null);
create table oranges (variety char(10) not null primary key, price int not null);
insert into apples(variety, price) values(‘Fuji’,5),(‘Gala’,6);
insert into oranges(variety, price) values(‘Valencia’,4),(‘Navel’,5);

mysql> select * from apples;
+———+——-+
| variety | price |
+———+——-+
| Fuji | 5 [...]

Read Full Post »

Normalization

Normalization is the process of efficiently organizing data in a database. There are two goals of the normalization process: eliminating redundant data (for example, storing the same data in more than one table) and ensuring data dependencies make sense (only storing related data in a table). Both of these are worthy goals as [...]

Read Full Post »

SQL Quick Reference

SQL Statement
Syntax

AND / OR
SELECT column_name(s)
FROM table_name
WHERE condition
AND|OR condition

ALTER TABLE
ALTER TABLE table_name
ADD column_name datatype
or
ALTER TABLE table_name
DROP COLUMN column_name

AS (alias)
SELECT column_name AS column_alias
FROM table_name
or
SELECT column_name
FROM table_name  AS table_alias

BETWEEN
SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2

CREATE DATABASE
CREATE DATABASE database_name

CREATE TABLE
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name2 data_type,
…
)

CREATE INDEX
CREATE INDEX index_name
ON table_name (column_name)
or
CREATE UNIQUE INDEX index_name
ON table_name (column_name)

CREATE VIEW
CREATE VIEW view_name AS
SELECT [...]

Read Full Post »

Finding last inserted autoincrement id value

Suppose you have table like
User Table with fields id(auto increment) and name
address table with field id(auto increment), address, user_id(foreign key)
when u inserted one row in user table then u want the last inserted id value in user table to insert it into address table in the field user_id for that corresponding user address entry
use mysql [...]

Read Full Post »