Web Technology Experts Notes

Showing posts with label MYSQL. Show all posts

Wednesday 18 March 2015

How to get a list of MySQL user accounts?

In MySQL, you can get list of user account with user details. Just execute following Query of PHPMySQL/SQL Yog/Other MySQL Tools.

List of MySQL users.

SELECT User FROM mysql.user

List of MySQL unique users.

select distinct User from mysql.user;

List of MySQL users with password and host.

SELECT Host,User, Password FROM mysql.user

List of MySQL unique Users with password and host.

SELECT Host,User, Password FROM mysql.user  group by User

List of MySQL users with all permissions.

SELECT * FROM mysql.user WHERE 1

Friday 6 February 2015

How to delete MySQL Binary logs Files in Wamp Server?

Sometime we find our Wamp Server is talking too much space like 10GB whereas it must take less than the current size. It may be your mysql binary log files are taking too much space in data.
Binary files look like below:
mysql-bin.000001,
mysql-bin.000002,
................,
mysql-bin.000221

Before we delete the mysql binary log files permanently, we should know about these binary log files like what type of files are these and how effect our server.

Question: What is Mysql Binary logs file?
MySQL binary logs are the text file, created by mysql automatically. Default files name are "mysql-bin.000001" which located in data folder of mysqlx.x.

Question: What is purpose of Binary logs file?
It has two purpose
Data Recovery: It is used for data recovery operations.
High availability / replication: It is used on master replication servers as a record of the statements to be sent to slave servers.

Question: What is default location of binary log file?

E:\wamp\bin\mysql\mysql.x.x\data

Question: How can change the Binary log file name?
Open my.ini file under mysql.x.x folder.
In my window system:
E:\wamp\bin\mysql\mysql.x.x\my.ini

Question: Update the following "mysql-bin" name

log-bin=mysql-bin

Question: How to disable the automatic creating binary log files?
Open my.ini file under mysql.x.x folder.
In my window system: E:\wamp\bin\mysql\mysql.x.x\my.ini
Search

log-bin=mysql-bin

and replace with following

#log-bin=mysql-bin

Question: What is dis-advantage of mysql binary log files?
Its takes too much space, some times it take upto 10MB or more space.

Question: How to delete mysql bin files permanently?
It have 3 steps and are following.

Step 1: Disable automatic creating file.
open my.ini file under mysql.x.x folder.
Search

log-bin=mysql-bin

and Replace with following

#log-bin=mysql-bin

Step 2:
Restart Wamp Server

Step3 :
Now you can delete all files start with "mysql-bin.0000xx"

Never delete mysql bin files directly you must follow Step 1

Thursday 8 January 2015

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 2269548 for key PRIMARY

Following are few reasons with solutions:

Reason 1:
ID is auto-increment and primary key. id is auto filed when an record is insert.
When we try to insert record with same id which is already exist in table, then such type of error comes.
SOLUTION:
So, write SQL in following ways.

INSERT INTO users (name) VALUES ('name1'); //skip the id field which is auto-increment

Reason 2:
When table is used to frequent insertion of records, like we are storing db logs for each action. Sometimes, it have thousand of thousands records, and field(like id) limit cross the limit. SOLUTION: Increase the limit of id field.

ALTER TABLE `users` CHANGE `id` `id` BIGINT(10) UNSIGNED NOT NULL AUTO_INCREMENT;

Reason 3:

When table is used to frequent insertion of records, like we are storing db logs for each action.Sometimes, it have thousand of thousands records, and table goes to overhead.
SOLUTION: Execute following query
OPTIMIZE TABLE `users`

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.

Subscribe to: Posts ( Atom )

Wednesday 18 March 2015

Best Related Posts are Following:

Friday 6 February 2015

Best Related Posts are Following:

Thursday 8 January 2015

Best Related Posts are Following:

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: