Showing posts with label MYSQL. Show all posts
Showing posts with label MYSQL. Show all posts

Wednesday, 18 March 2015

How to get a list of MySQL user accounts?

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?

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

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

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

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

There are three types of privileges in MySql

  1. Administrative privilege(s) enable users to manage operation of MySQL server. These are global because they are not specific to a particular mysql database. 
  2. Database privileges apply to a database and to all objects within it, means apply to specific database. 
  3. 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
PrivilegeColumnContext
CREATECreate_privdatabases, tables, or indexes
DROPDrop_privdatabases, tables, or views
GRANT OPTIONGrant_privdatabases, tables, or stored routines
LOCK TABLESLock_tables_privdatabases
REFERENCESReferences_privdatabases or tables
EVENTEvent_privdatabases
ALTERAlter_privtables
DELETEDelete_privtables
INDEXIndex_privtables
INSERTInsert_privtables or columns
SELECTSelect_privtables or columns
UPDATEUpdate_privtables or columns
CREATE TEMPORARY TABLESCreate_tmp_table_privtables
TRIGGERTrigger_privtables
CREATE VIEWCreate_view_privviews
SHOW VIEWShow_view_privviews
ALTER ROUTINEAlter_routine_privstored routines
CREATE ROUTINECreate_routine_privstored routines
EXECUTEExecute_privstored routines
FILEFile_privfile access on server host
CREATE USERCreate_user_privserver administration
PROCESSProcess_privserver administration
RELOADReload_privserver administration
REPLICATION CLIENTRepl_client_privserver administration
REPLICATION SLAVERepl_slave_privserver administration
SHOW DATABASESShow_db_privserver administration
SHUTDOWNShutdown_privserver administration
SUPERSuper_privserver 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

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

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


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


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


Composite Key
  1. A composite key contains at least one compound key and one more attribute. Composite keys may also include simple keys and non-key attributes.
  2. A table can have one OR more composite Key
  3. 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

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

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

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

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”.


OperatorDescription
+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.