Web Technology Experts Notes

Showing posts with label MYSQL. Show all posts

Tuesday 9 December 2014

Multiple column ordering in Zend Framework

There are different ways to multiple column ordering in Zend. Following are two different ways to do multiple column ordering in Zend Framework. you can use either First way OR Second way.

First Way

->order(array('first_name asc','last_name desc'));

Second Way

->order('first_name asc')->order('last_name desc')

In above code snippet, First It will sort by first_name in ascending order then sort by last_name in descending order. you can also add more than two column in ordering.

Multiple column ordering in MySQL

select * from users order by first_name asc, last_name desc

First It will sort by first_name in ascending order then sort by last_name in descending order.

Monday 24 November 2014

Mysql Privileges - Types of privileages in MySql - How do I grant privileges in MySQL

There are three types of privileges in MySql

Administrative privilege(s) enable users to manage operation of MySQL server. These are global because they are not specific to a particular mysql database.
Database privileges apply to a database and to all objects within it, means apply to specific database.
Privileges for database object(s) such as table(s), indexe(s), stored routine(s), and view(s). These privileges apply table, index, Stored Procedure, views etc. of a single database. It can be granted for specific objects within a database OR for all objects of a given type within a database.

Following are permissible Privileges for GRANT and REVOKE

Privilege	Column	Context
CREATE	Create_priv	databases, tables, or indexes
DROP	Drop_priv	databases, tables, or views
GRANT OPTION	Grant_priv	databases, tables, or stored routines
LOCK TABLES	Lock_tables_priv	databases
REFERENCES	References_priv	databases or tables
EVENT	Event_priv	databases
ALTER	Alter_priv	tables
DELETE	Delete_priv	tables
INDEX	Index_priv	tables
INSERT	Insert_priv	tables or columns
SELECT	Select_priv	tables or columns
UPDATE	Update_priv	tables or columns
CREATE TEMPORARY TABLES	Create_tmp_table_priv	tables
TRIGGER	Trigger_priv	tables
CREATE VIEW	Create_view_priv	views
SHOW VIEW	Show_view_priv	views
ALTER ROUTINE	Alter_routine_priv	stored routines
CREATE ROUTINE	Create_routine_priv	stored routines
EXECUTE	Execute_priv	stored routines
FILE	File_priv	file access on server host
CREATE USER	Create_user_priv	server administration
PROCESS	Process_priv	server administration
RELOAD	Reload_priv	server administration
REPLICATION CLIENT	Repl_client_priv	server administration
REPLICATION SLAVE	Repl_slave_priv	server administration
SHOW DATABASES	Show_db_priv	server administration
SHUTDOWN	Shutdown_priv	server administration
SUPER	Super_priv	server administration

Following are mysql grant permission example:

GRANT SELECT ON databasename.* TO user@'localhost';
/* Give the select permission to user i.e user for database databasename*/

GRANT SELECT ON databasename.* TO user@'localhost' IDENTIFIED BY 'password';
/* Give the select permission to user i.e user for database databasename and create the user*/

GRANT SELECT, INSERT, DELETE ON databasename TO username@'localhost' IDENTIFIED BY 'password';
/* Give the SELECT, INSERT, DELETE permission to user i.e user for database databasename*


select * from mysql.user where User='username';
/*To see a list of the privileges that have been granted to a specific user:*/


GRANT all privileges ON databasename.* TO user@'localhost';
/* Give all permissions to user i.e user for database databasename*/

Monday 18 August 2014

Difference Between Primary Key And Unique Key And Foreign Key And Composite Key

Primary Key

Primary Key can identify a row as uniquely
A table can have only one primary key
It can't be Null
Indexing added automatically to Primary key

Foreign Key

A FOREIGN KEY in one table reference to a Primary Key in another table
A table can have one OR more foreign key.
It can be Null
Indexing not added automatically to Foreign key

Unique Key

Unique Key can identify a row as uniquely.
A table can have one OR more unique key.
It can be Null
Indexing not added automatically to Unique Key

Composite Key

A composite key contains at least one compound key and one more attribute. Composite keys may also include simple keys and non-key attributes.
A table can have one OR more composite Key
It can also be null.

See Example:

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `phone` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `user_profile` (
  `user_id` int(11) NOT NULL,
  `address1` varchar(100) NOT NULL,
  `address2` varchar(100) NOT NULL,
  KEY `user_id` (`user_id`),
FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Primary Key: users.id
Foreign Key: user_profile.user_id
Unique Key: users.email

Wednesday 26 March 2014

Difference Between MyISAM and innoDB - MySQL

MySQL is Open Source database means its free to use and reliable. that's why lot of application use mysql as a database. But when we going to create a new database, lot of doubts comes in mind like what will be database maxsize, Database engine and number of tables in database.

Before you create you databases, you must think below important points.

Why I am creating database.
What are most use of database means going to add/update statement or search the list
What type of record will be stored means in english character or chinse/japense character
Maximum records in table.
What are different types of database engine supported by MySQL.
What tables will use for tractions

Following are list of different database engines types

Name	Vendor	License	Transactional	active development
Archive	Oracle	GPL	No	Yes
Aria	Monty Program	GPL	No	Yes
Berkeley DB	Oracle			No
BlitzDB		GPL	No	Yes
CONNECT	Monty Program	GPL		Yes
CSV	Oracle	GPL	No	Yes
Falcon	Oracle	GPL	Yes	No
InnoDB	Oracle	GPL	Yes	Yes
MyISAM	Oracle	GPL	No	No
NDB	Oracle		Yes	Yes
InfiniDB	Calpont	GPL	Yes	Yes
TokuDB	TokuTek	Modified GPL	Yes	Yes
XtraDB	Percona	GPL	Yes	Yes
Federated	Oracle	GPL		No
FederatedX	Monty Program	GPL		Yes
CassandraSE	Monty Program	GPL	No	Yes
sequence	Monty Program	GPL	Yes	Yes
mroonga	Monty Program	GPL		Yes
SphinxSE	Sphinx Technologies	GPL	No	Yes

Following are Difference between MyISam and InnoDB
MyISAM Advantage
1. Faster than InnoDB for search results
2. support Full-text indexing
3. When use "select" statement, gives faster results as compare to Innodb

InnoDB Advantage
1. Support for transactions (ACID: Atomicity, Consistency, Isolation, Durability).
2. Better "Row-level locking"
3. Foreign key constraints
4. Repair better incase of table corruption
5. Support Rollback abilities
6. Automatic crash recovery

MyISAM Disadvantage
1. Cascading delete/update not support
2. NOt support ACID compliance
3. Row limit 4,284,867,296
4. When we use indexing, insert/update/delete become bit slow
5. Not Support Row Locking

InnoDB Disadvantage
1. Not support full text indexing (<5.6 mysql version)
2. Can't compressed
3. Search Slow as compare to MyISAM

Change table engine to MyISAM

ALTER TABLE `tableName` ENGINE = MYISAM

Change table engine to innodb

ALTER TABLE `tableName` ENGINE = innodb

Monday 13 January 2014

MySQL Functions - MySQL tutorial for beginners

Following are functions which is most command used in mysql

Least: Return Least value amont all

SELECT LEAST(4,3,8,-1,5);

Greatest: Return greatest value amont all

SELECT GREATEST(4,3,8,-1,5);

Interval(): takes a comparison value as its first argument. The remaining arguments should be a set of values in sorted order. INTERVAL() compares the first argument to the others and returns a value to indicate how many of them are less than or equal to it.

SELECT INTERVAL(2,1,2,3,4);

BETWEEN: The BETWEEN operator takes the two endpoint values of the range and returns true if a comparison value lies between them and The comparison is inclusive.

SELECT * FROM `products` WHERE id between 1 and 10

ISNULL(0): return true if value is NULL

select ISNULL(1)

Group By: The MySQL GROUP BY statement is used along with the SQL aggregate functions like SUM to provide means of grouping the result. IN: This is a clause, which can be used along with any MySQL query to specify a condition.

BETWEEN: This is a clause, which can be used along with any MySQL query to specify a condition.

UNION: Use a UNION operation to combine multiple result sets into one.

COUNT: COUNT aggregate function is used to count the number of rows in a database table.

MAX: The MAX aggregate function allows us to select the highest (maximum) value for a certain column.

MIN: The MIN aggregate function allows us to select the lowest (minimum) value for a certain column.

AVG: The AVG aggregate function selects the average value for certain table column.

SUM: The SUM aggregate function allows selecting the total for a numeric column.

SQRT: This is used to generate a square root of a given number.

RAND: This is used to generate a random number using MySQL command.

CONCAT: This is used to concatenate any string inside any MySQL command.

Switch Case in Mysql

CASE  case_expression
   WHEN when_expression_1 THEN execute_statement1
   WHEN when_expression_2 THEN execute_statement1 
   ELSE execute_statement
END CASE;

Example 1

set @variable='10';
select (case when (@variable = '10') then "Equal to 10" else "Not Equal to 10" end)

Example 2

set @variable=10;
select (case when (@variable <=10) then "Less Than Equal to 10" else "Greater Than 10" end)

Example 3

set @variable=10;
select (case
            when (@variable <10) then "Less Than Equal to 10"
            when (@variable =10) then "Equal to 10"
        else "Greater Than 10" end)

mysqlsubqueries examples Scalar Subqueries Row Subqueries Column Subqueries Table Subqueries

It is query very similar to Normal queries in databases like
It may return one column
it may return more column
It may return single rows
It may return multiple rows

OR
any of above combination.

SubQuries are those queries which are used within Normal Queries.

For Example

select * from users where user_id =(select user_id from profile where type='user');

Here queries used within bracket is known as sub queris

There are four types of sub-queries.
a. Scalar Subqueries: return single columan of single row

select * from users where user_id =(select user_id from profile where type='user' limit 1);

b. Row Subqueries: Return single row

select u1.* from users As u1 left join (select user_id,country from profile limit 1) as u2 on u2.user_id=u1.user_id and u2.country=u1.country

c. Column Subqueries: Return one column of one or more rows

select * from users where user_id in(select user_id from profile where type='user');

d. Table Subqueries: return one more col of one/more rows

select u1.* from users As u1 left join (select user_id,country from profile) as u2 on u2.user_id=u1.user_id and u2.country=u1.country

Friday 17 May 2013

Mysql FullText Search Examples

Full-text indexes can be used with MyISAM tables and InnoDB tables (in MySQL 5.6+ only), and can be created only for varchar, char and text columns only.

A FULLTEXT index definition can be given in the CREATE TABLE statement when a table is created, or added later using ALTER TABLE or CREATE INDEX.

/** create table **/
CREATE TABLE products (
 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
 name VARCHAR(75) NOT NULL,
 sku  VARCHAR(75) NOT NULL, 
 description MEDIUMTEXT NOT NULL, 
 PRIMARY KEY(id)
)ENGINE=MyISAM;

/** add full text indexes **/
CREATE FULLTEXT INDEX ft_index_name ON products (name, description);

Full-text searching is performed using MATCH() and AGAINST syntax.

MATCH() takes a comma-separated list that names the columns in which to be searched.

AGAINST takes a string to search for, and an optional modifier that indicates what type of search to perform. The search string must be a string value that is constant during query evaluation. This rules out, for example, a table column because that can differ for each row.

INSERT INTO `abc`.`products` (`id`, `name`, `sku`, `description`) VALUES (NULL, 'watch', 'w2658', 'test watch'), (NULL, 'MOBILE', 'M25744', 'Test mobile')
INSERT INTO `abc`.`products` (`id`, `name`, `sku`, `description`) VALUES (NULL, 'watch1', 'w26581', 'test watch'), (NULL, 'MOBILE1', 'M257441', 'Test mobile');

There are three types of full-text searches:

1. A boolean search interprets the search string using the rules of a special query language. The string contains the words to search for. It can also contain operators that specify requirements such that a word must be present or absent in matching rows, or that it should be weighted higher or lower than usual. Common words such as “some” or “then” are stopwords and do not match if present in the search string. The IN BOOLEAN MODE modifier specifies a Boolean search.

2. A natural language search interprets the search string as a phrase in natural human language (a phrase in free text). There are no special operators. The stopword list applies. In addition, words that are present in 50% or more of the rows are considered common and do not match. Full-text searches are natural language searches if no modifier is given.

3. A query expansion search is a modification of a natural language search. The search string is used to perform a natural language search. Then words from the most relevant rows returned by the search are added to the search string and the search is done again. The query returns the rows from the second search. The WITH QUERY EXPANSION modifier specifies a query expansion search. For more information, see Section 12.9.3, “Full-Text Searches with Query Expansion”.

Example

SELECT * 
FROM products WHERE MATCH (name,description) AGAINST ('mobile')

SELECT * 
FROM products WHERE MATCH (name,description) AGAINST ('mobile' in boolean mode)

select * from products WHERE MATCH(name,description)  AGAINST ('+mobile -computer' IN BOOLEAN MODE);

See following for searching....
'mobile computer'
search mobile or computer

'+mobile +computer'
search for both mobile and computer

'+mobile -computer'
search for mobile without computer

'+mobile +computer'
search for both mobile and computer

‘+mobile ~computer’
Find rows that contain the word “mobile”, but if the row also contains the word “computer”, rate it lower than if row does not.

‘+mobile +(>computer <television)’
Find rows that contain the words “mobile” and “computer”, or “mobile” and “television” (in any order), but rank “mobile computer” higher than “mobile television”.

Operator	Description
+	Include, word must be present.
-	Exclude, word must not be present.
>	Include, and increase ranking value.
<	Include, and decrease ranking value.
()	Group words into sub expressions
~	Negate a word’s ranking value.
*	Wildcard at end of word.
“”	Defines a phrase (as opposed to a list of individual words, the entire phrase is matched for inclusion or exclusion).

Wednesday 23 January 2013

MySQL Constraint Example

Q1: Table restrict for delete, if child table have records.

Q2: Mysql constraint restrict.

Q3. How to prevent from delete a parent record, if child exists.

Above all Questions have only one answer that is below with fine example.

Create a table products

CREATE TABLE products (
    id int(11) unsigned not null primary key AUTO_INCREMENT,
    name varchar(255) default null
)

Create a table reviews, as each products have one or more reviews

CREATE TABLE reviews (
    id int unsigned not null primary key AUTO_INCREMENT,
    review_by int(11) unsigned not null,
    product_id int(11) unsigned not null,
    FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE RESTRICT  ON UPDATE RESTRICT
  )

Now, if you try to delete a product having one or more review(s), you will get an mysql error similar to following.

Error
SQL query:
DELETE FROM `products` WHERE `products`.`id` =1
MySQL said:

#1451 - Cannot delete or update a parent row: a foreign key constraint fails (`abc`.`reviews`, CONSTRAINT `reviews_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`))

You can only delete the product, after deleted its reviews.

You can also add constraints after table creation, Use following query as example.

alter table `reviews` add constraint `FK_reviews_check` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE RESTRICT ON DELETE RESTRICT;

MySQL constraints Requirement
1. Both FOREIGN KEY table and REFERENCE table must have same Storage Engine.
2. Both FOREIGN KEY and Reference Id must have same datatype.
3. Both FOREIGN KEY and Reference Id must have same attributes means either both un-signed OR signed.

Tuesday 22 January 2013

MySQL Cascade Example

Q1: When parent record deleted, child must be deleted automatically. But if child record deleted, parent should not effected.

Q2: I want to understand the mysql constraint cascade.

Q3. How a child record deleted automatically when parent deleted.

Above all Questions have only one answer that is below with fine example.

Create a table products

CREATE TABLE products (
    id int unsigned not null primary key AUTO_INCREMENT,
    name varchar(255) default null 
);

Create a table reviews, as each product have one or more reviews

CREATE TABLE reviews (
    id int unsigned not null primary key AUTO_INCREMENT,
    review_by int(11) unsigned not null,
    product_id int(11) unsigned not null,
    points int(11),
    FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE  ON UPDATE CASCADE
  )

Now, if you delete a product, then its review (product's review) will be automatically deleted. but if you delete the child parent record will be untouched/uneffected.

Friday 19 October 2012

Mysql-Group-Concat

Group_concat: its used to concatenate column values into a single string.

Create a table

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) DEFAULT NULL,
  `country` varchar(255) DEFAULT NULL,
  `company` varchar(150) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Following are examples

select group_concat(name) as all_user where country='india'

List all the name separated by comma (,)
for example: arun,raj,suman,arun,ansh

select group_concat(name SEPARATOR '--') as all_user where country='india'

List all the name separated by double dash(--)
for example: arun--raj--suman--arun--ansh

select group_concat(name order by name asc) as all_user where country='india'

List all the name separated by comma (,) but order by name
for example: arun,arun,raj,suman,ansh

select group_concat(distinct name) as all_user where country='india'

List all the name separated by comma (,) but with distinc name only
for example: arun,raj,suman,ansh

Monday 15 October 2012

MySQL Expressions

Expressions are a common element of SQL statements( statements are select, delete and update).
they often occur in the where clause of select, delete & update.

For Example:
DELETE FROM users WHERE id=5;

Here id=5 are expressions.

Following are the different components of SQL Expression:

Numeric Expressions
Exact value Literal are: 11,12.33, 44.333
Approximate Literal are 1233E2, 44333E3
Operations on decimal value have precision upto 64 digits, currently its 30 character.
In Approximate, mantissa allows upto 53 bit of precision which is about 15 decimal digits.
String Expressions
String like 'sql' or mysql
Temporal Expressions
It include dates, times and datetime values like Select ‘2012-01-01’ + interval 10 day

Functions like current(), version(), avg(), sum()
Regular Express (Regex)

Sunday 14 October 2012

MySQL Delete Table Example

Create a table

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `company` varchar(150) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

Delete record(s) from table

DELETE FROM users WHERE id=5;

It will delete a record where id is 5

Delete record from two table when they are in join.

DELETE users1,users2 FROM users1,users2,users3 WHERE tusers1.id=users2.id AND users2.id=users3.id

Keep in Mind following points..

If we specify the LOW_PRIORITY keyword in query, execution of the DELETE is delayed until no other clients are reading from the table.
For MyISAM tables, if we specify the QUICK keyword in query, then the storage engine will not merge index leaves during delete, which will speed up certain kind of delete.
If we specify the IGNORE causes MySQL to ignore all errors during the process of deleting rows.

Subscribe to: Posts ( Atom )

Tuesday 9 December 2014

Best Related Posts are Following:

Monday 24 November 2014

There are three types of privileges in MySql

Best Related Posts are Following:

Monday 18 August 2014

Best Related Posts are Following:

Wednesday 26 March 2014

Following are list of different database engines types

Best Related Posts are Following:

Monday 13 January 2014

Best Related Posts are Following:

SubQuries are those queries which are used within Normal Queries.

Best Related Posts are Following:

Friday 17 May 2013

Best Related Posts are Following:

Wednesday 23 January 2013

Best Related Posts are Following:

Tuesday 22 January 2013

Best Related Posts are Following:

Friday 19 October 2012

Group_concat: its used to concatenate column values into a single string.

Following are examples

Monday 15 October 2012

Best Related Posts are Following:

Sunday 14 October 2012

Keep in Mind following points..

Best Related Posts are Following: