MySQL Administration:
root@host# mysql -u root -p
Enter password:*******
mysql> use mysql;
Database changed
mysql> INSERT INTO user
(host, user, password,
select_priv, insert_priv, update_priv)
VALUES ('localhost', 'guest',
PASSWORD('guest123'), 'Y', 'Y', 'Y');
mysql> FLUSH PRIVILEGES; // This tells the server to reload the grant tables
mysql> SELECT host, user, password FROM user WHERE user = 'guest'; //displays result
Various privileges:
Select_priv,Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv,Reload_priv, Shutdown_priv,Process_priv,File_priv,Grant_priv,References_priv,Index_priv,Alter_priv
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON TUTORIALS.*
-> TO 'zara'@'localhost'
-> IDENTIFIED BY 'zara123'; //grant privileges
important MySQL commands:
USE Databasename : This will be used to select a particular database in MySQL workarea.
SHOW DATABASES: Lists the databases that are accessible by the MySQL DBMS.
SHOW TABLES: Shows the tables in the database once a database has been selected with the use command.
SHOW COLUMNS FROM tablename: Shows the attributes, types of attributes, key information, whether NULL is permitted, defaults, and other information for a table.
SHOW INDEX FROM tablename: Presents the details of all indexes on the table, including the PRIMARY KEY.
SHOW TABLE STATUS LIKE tablename\G: Reports details of the MySQL DBMS performance and statistics.
/etc/my.cnf File Configuration: Contains config related info
MySQL PHP Syntax:
mysql_function(value,value,...);
mysqli_connect($connect);
mysqli_query($connect,"SQL statement");
MySQL Connection:
[root@host]# mysql -u root -p
Enter password:******
mysql> exit
Using PHP:
connection mysql_connect(server,user,passwd,new_link,client_flag);
new_link - If a second call is made to mysql_connect() with the same arguments, no new connection will be established;
client_flags: MYSQL_CLIENT_SSL , MYSQL_CLIENT_COMPRESS, MYSQL_CLIENT_IGNORE_SPACE,MYSQL_CLIENT_INTERACTIVE
bool mysql_close ( resource $link_identifier );
MySQL Create Database:
[root@host]# mysqladmin -u root -p create TUTORIALS
Enter password:******
Using PHP:
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
$sql = 'CREATE DATABASE TUTORIALS';
$retval = mysql_query( $sql, $conn );
mysql_close($conn);
Drop MySQL Database:
[root@host]# mysqladmin -u root -p drop TUTORIALS
Enter password:******
Using PHP:
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
$sql = 'DROP DATABASE TUTORIALS';
$retval = mysql_query( $sql, $conn );
mysql_close($conn);
Selecting MySQL Database:
[root@host]# mysql -u root -p
Enter password:******
mysql> use TUTORIALS;
Using PHP:
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
mysql_select_db( 'TUTORIALS' );
mysql_close($conn);
MySQL Data Types:
Numeric Data Types:
INT , TINYINT,SMALLINT,MEDIUMINT,BIGINT,FLOAT(M,D) (display length (M),number of decimals (D)), DOUBLE(M,D), DECIMAL(M,D)
NUMERIC is a synonym for DECIMAL.
Date and Time Types:
DATE - A date in YYYY-MM-DD format
DATETIME - A date and time combination in YYYY-MM-DD HH:MM:SS format
TIMESTAMP - A timestamp between midnight, January 1, 1970 and sometime in 2037 stored as ( YYYYMMDDHHMMSS ).
TIME - Stores the time in HH:MM:SS format.
YEAR(M) - Stores a year in 2-digit or 4-digit format.
String Types:
CHAR(M) - A fixed-length string between 1 and 255 characters in length right-padded with spaces to the specified length when stored.
VARCHAR(M) - A variable-length string between 1 and 255 characters in length, You must define a length when creating a VARCHAR field.
BLOB or TEXT ,TINYBLOB or TINYTEXT .MEDIUMBLOB or MEDIUMTEXT, LONGBLOB or LONGTEXT
ENUM - a list of items from which the value must be selected (or it can be NULL).
Create MySQL Tables:
CREATE TABLE table_name (column_name column_type);
mysql> CREATE TABLE tutorials_tbl(
-> tutorial_id INT NOT NULL AUTO_INCREMENT,
-> tutorial_title VARCHAR(100) NOT NULL,
-> tutorial_author VARCHAR(40) NOT NULL,
-> submission_date DATE,
-> PRIMARY KEY ( tutorial_id )
-> );
Drop MySQL Tables:
DROP TABLE table_name ;
mysql> DROP TABLE tutorials_tbl
MySQL Insert Query:
INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN );
mysql> INSERT INTO tutorials_tbl
->(tutorial_title, tutorial_author, submission_date)
->VALUES
->("Learn MySQL", "Abdul S", NOW());
PHP SQL Syntax:
if(! get_magic_quotes_gpc() )
{
$tutorial_title = addslashes ($_POST['tutorial_title']);
$tutorial_author = addslashes ($_POST['tutorial_author']);
}
else
{
$tutorial_title = $_POST['tutorial_title'];
$tutorial_author = $_POST['tutorial_author'];
}
$submission_date = $_POST['submission_date'];
$sql = "INSERT INTO tutorials_tbl ".
"(tutorial_title,tutorial_author, submission_date) ".
"VALUES ".
"('$tutorial_title','$tutorial_author','$submission_date')";
For data from forms or user supplied data use get_magic_quotes_gpc() to check if current configuration for magic quote is set or not. If this function returns false, then use function addslashes() to add slashes before quotes.
MySQL Select Query:
SELECT field1, field2,...fieldN table_name1, table_name2...
[WHERE Clause]
[OFFSET M ][LIMIT N]
//OFFSET from where SELECT will start returning records, You can limit the number of returns using LIMIT attribute
mysql> SELECT * from tutorials_tbl
Using PHP:
$retval = mysql_query( $sql, $conn );
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
echo "Tutorial ID :{$row['tutorial_id']} <br> ".
"Title: {$row['tutorial_title']} <br> ".
"Author: {$row['tutorial_author']} <br> ".
"Submission Date : {$row['submission_date']} <br> ".
"--------------------------------<br>";
}
or
while($row = mysql_fetch_assoc($retval))
{
echo "Tutorial ID :{$row['tutorial_id']} <br> ".
"Title: {$row['tutorial_title']} <br> ".
"Author: {$row['tutorial_author']} <br> ".
"Submission Date : {$row['submission_date']} <br> ".
"--------------------------------<br>";
}
or
while($row = mysql_fetch_array($retval, MYSQL_NUM))
{
echo "Tutorial ID :{$row[0]} <br> ".
"Title: {$row[1]} <br> ".
"Author: {$row[2]} <br> ".
"Submission Date : {$row[3]} <br> ".
"--------------------------------<br>";
}
and
mysql_free_result($retval);
MySQL WHERE Clause:
SELECT field1, field2,...fieldN table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....
Operators like =, >, < are used with where clause
You can make your search case sensitive using BINARY keyword
MySQL UPDATE Query
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
mysql> UPDATE tutorials_tbl
-> SET tutorial_title='Learning JAVA'
-> WHERE tutorial_id=3;
MySQL DELETE Query
DELETE FROM table_name [WHERE Clause]
mysql> DELETE FROM tutorials_tbl WHERE tutorial_id=3;
MySQL LIKE Clause
A WHERE clause with equals sign (=) works fine where we want to do an exact match. Like if "tutorial_author = 'Sanjay'". But there may be a requirement where we want to filter out all the results where tutorial_author name should contain "jay". This can be handled using SQL LIKE clause along with WHERE clause.
If SQL LIKE clause is used along with % characters, then it will work like a meta character (*) in UNIX while listing out all the files or directories at command promptWithout a % character, LIKE clause is very similar to equals sign along with WHERE clause.
SELECT field1, field2,...fieldN table_name1, table_name2...
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'
mysql> SELECT * from tutorials_tbl
-> WHERE tutorial_author LIKE '%jay';
MySQL Sorting Results
SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]
mysql> SELECT * from tutorials_tbl ORDER BY tutorial_author ASC
Using MySQl Joins
You can use JOINS in SELECT, UPDATE and DELETE statements to join MySQL tables and return results in a single query
SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
-> FROM tutorials_tbl a, tcount_tbl b
-> WHERE a.tutorial_author = b.tutorial_author;
MySQL LEFT JOIN:
If I do a LEFT JOIN, I get all the records that match in the same way and IN ADDITION I get an extra record for each unmatched record in the left table of the join
mysql> SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
-> FROM tutorials_tbl a LEFT JOIN tcount_tbl b
-> ON a.tutorial_author = b.tutorial_author; //thus ensuring that every AUTHOR gets a mention:
Handling MySQL NULL Values:
Conditions involving NULL are special. You cannot use = NULL or != NULL to look for NULL values in columns. MySQL provides three operators
IS NULL: operator returns true if column value is NULL.
IS NOT NULL: operator returns true if column value is not NULL.
<=>: operator compares values, which (unlike the = operator) is true even for two NULL values.
mysql> create table tcount_tbl
-> (
-> tutorial_author varchar(40) NOT NULL,
-> tutorial_count INT
-> );
mysql> SELECT * FROM tcount_tbl WHERE tutorial_count = NULL;
mysql> SELECT * FROM tcount_tbl WHERE tutorial_count != NULL;
The above doesnt work so we write them as
mysql> SELECT * FROM tcount_tbl
-> WHERE tutorial_count IS NULL;
mysql> SELECT * from tcount_tbl
-> WHERE tutorial_count IS NOT NULL;
You can use if...else condition to prepare a query based on NULL value in php
if( isset($tutorial_count ))
{
$sql = 'SELECT tutorial_author, tutorial_count
FROM tcount_tbl
WHERE tutorial_count = $tutorial_count';
}
else
{
$sql = 'SELECT tutorial_author, tutorial_count
FROM tcount_tbl
WHERE tutorial_count IS $tutorial_count';
}
MySQL Regexps
^ Beginning of string
$ End of string
. Any single character
[...] Any character listed between the square brackets
[^...] Any character not listed between the square brackets
p1|p2|p3 Alternation; matches any of the patterns p1, p2, or p3
* Zero or more instances of preceding element
+ One or more instances of preceding element
{n} n instances of preceding element
{m,n} m through n instances of preceding element
Examples:
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';
MySQL Transactions
ACID:
Atomicity: ensures that all operations within the work unit are completed successfully; otherwise, the transaction is aborted at the point of failure and previous operations are rolled back to their former state.
Consistency: ensures that the database properly changes states upon a successfully committed transaction.
Isolation: enables transactions to operate independently on and transparent to each other.
Durability: ensures that the result or effect of a committed transaction persists in case of a system failure.
In MySQL, transactions begin with the statement BEGIN WORK and end with either a COMMIT or a ROLLBACK statement.
If AUTOCOMMIT is set to 1 (the default), then each SQL statement (within a transaction or not) is considered a complete transaction and committed by default when it finishes. When AUTOCOMMIT is set to 0, by issuing the SET AUTOCOMMIT=0 command, the subsequent series of statements acts like a transaction and no activities are committed until an explicit COMMIT statement is issued.
You can execute these SQL commands in PHP by using mysql_query() function.Begin transaction by issuing SQL command BEGIN WORK.Issue one or more SQL commands like SELECT, INSERT, UPDATE or DELETE.Check if there is no error and everything is according to your requirement.If there is any error, then issue ROLLBACK command, otherwise issue a COMMIT command.
There are many types of tables( GEMINI or BDB), which support transactions but most popular one is InnoDB.
mysql> create table tcount_tbl
-> (
-> tutorial_author varchar(40) NOT NULL,
-> tutorial_count INT
-> ) TYPE=InnoDB;
MySQL ALTER Command
change a name of your table, any table field or if you want to add or delete an existing column in a table.
mysql> ALTER TABLE testalter_tbl DROP i; //existing column i from above MySQL table
mysql> ALTER TABLE testalter_tbl ADD i INT; //add a column
ALTER TABLE testalter_tbl ADD i INT FIRST; //first column in table
ALTER TABLE testalter_tbl ADD i INT AFTER c; //add column afer column c
mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10); //change column c from CHAR(1) to CHAR(10)
ALTER TABLE testalter_tbl CHANGE i j BIGINT; //name the column you want to change, then specify the new definition
When you MODIFY or CHANGE a column, you can also specify whether or not the column can contain NULL values and what its default value is
mysql> ALTER TABLE testalter_tbl MODIFY j BIGINT NOT NULL DEFAULT 100;
mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000; //change default value
mysql> ALTER TABLE testalter_tbl ALTER i DROP DEFAULT; //drop default
mysql> ALTER TABLE testalter_tbl TYPE = MYISAM; //change table type
mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl; //rename table
MySQL INDEXES
indexes are also type of tables, which keep primary key or index field and a pointer to each record into the actual table.INSERT and UPDATE statements take more time on tables having indexes where as SELECT statements become fast on those tables.
CREATE UNIQUE INDEX index_name ON table_name ( column1, column2,...); //A unique index means that two rows cannot have the same index value
mysql> CREATE UNIQUE INDEX AUTHOR_INDEX ON tutorials_tbl (tutorial_author DESC) // index the values in a column in descending order
Just omit UNIQUE keyword from the query to create simple index to allows duplicate values in a table.
ALTER command to add and drop INDEX:
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): indexed values must be unique and cannot be NULL.
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): values must be unique (with the exception of NULL values, which may appear multiple times).
ALTER TABLE tbl_name ADD INDEX index_name (column_list):
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list): FULLTEXT index is used for text-searching purposes.
mysql> ALTER TABLE testalter_tbl DROP INDEX (c);
mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL; //primary key should not be null
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);
mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;
Vertical-format output (specified by \G) often is useful to display index, to avoid long line wraparound
mysql> SHOW INDEX FROM table_name\G
........
MySQL Temporary Tables
temporary tables is will be deleted when the current client session terminates.
mysql> CREATE TEMPORARY TABLE SalesSummary (
-> product_name VARCHAR(50) NOT NULL
-> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
-> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
-> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
mysql> DROP TABLE SalesSummary; //drop temporary tables
Same code can be used in PHP scripts using mysql_query() function.
MySQL Clone Tables
To get exact copy of a table including the same indexes, default values, and so forth:
Use SHOW CREATE TABLE to get a CREATE TABLE statement that specifies the source table's structure, indexes and all.
Modify the statement to change the table name to that of the clone table and execute the statement. This way, you will have exact clone table.
Optionally, if you need the table contents copied as well, issue an INSERT INTO ... SELECT statement, too.
Example Steps:
mysql> SHOW CREATE TABLE tutorials_tbl \G; // Get complete structure of table
mysql> CREATE TABLE `clone_tbl` (
-> `tutorial_id` int(11) NOT NULL auto_increment,
-> `tutorial_title` varchar(100) NOT NULL default '',
-> `tutorial_author` varchar(40) NOT NULL default '',
-> `submission_date` date default NULL,
-> PRIMARY KEY (`tutorial_id`),
-> UNIQUE KEY `AUTHOR_INDEX` (`tutorial_author`)
-> ) TYPE=MyISAM; //Rename this table and create another table.
mysql> INSERT INTO clone_tbl (tutorial_id,
-> tutorial_title,
-> tutorial_author,
-> submission_date)
-> SELECT tutorial_id,tutorial_title,
-> tutorial_author,submission_date,
-> FROM tutorials_tbl; //insert data from old table
Obtaining and Using MySQL Metadata
There are three informations, which you would like to have from MySQL.
Information about the result of queries: This includes number of records affected by any SELECT, UPDATE or DELETE statement.
Information about tables and databases: This includes information pertaining to the structure of tables and databases.
Information about the MySQL server: This includes current status of database server, version number etc.
Getting above info using php:
$result_id = mysql_query ($query, $conn_id); // reportnumber of rows in result
$count = ($result_id ? mysql_affected_rows ($conn_id) : 0);
print ("$count rows were affected\n");
$db_list = mysql_list_dbs($con); //list the tables in db
while ($db = mysql_fetch_object($db_list))
{
echo $db->Database . "<br />";
}
Getting Sertver data is done using following functions
SELECT VERSION( ) Server version string
SELECT DATABASE( ) Current database name (empty if none)
SELECT USER( ) Current username
SHOW STATUS Server status indicators
SHOW VARIABLES Server configuration variables
Using MySQL Sequences
The simplest way in MySQL to use Sequences is to define a column as AUTO_INCREMENT and leave rest of the things to MySQL to take care.
mysql> CREATE TABLE insect
-> (
-> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> PRIMARY KEY (id),
-> name VARCHAR(30) NOT NULL, # type of insect
-> date DATE NOT NULL, # date collected
-> origin VARCHAR(30) NOT NULL # where collected
);
LAST_INSERT_ID( ) is a SQL function to obtain the auto increment value. In php we use
mysql_query ("INSERT INTO insect (name,date,origin)
VALUES('moth','2001-09-14','windowsill')", $conn_id);
$seq = mysql_insert_id ($conn_id);
To resequence:
mysql> ALTER TABLE insect DROP id; //direct resequence may affect joins
mysql> ALTER TABLE insect
-> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
-> ADD PRIMARY KEY (id);
To start a sequence from particular value:
mysql> CREATE TABLE insect
-> (
-> id INT UNSIGNED NOT NULL AUTO_INCREMENT = 100,
-> PRIMARY KEY (id),
-> name VARCHAR(30) NOT NULL, # type of insect
-> date DATE NOT NULL, # date collected
-> origin VARCHAR(30) NOT NULL # where collected
);
mysql> ALTER TABLE t AUTO_INCREMENT = 100;
MySQL Handling Duplicates:
You can use a PRIMARY KEY or UNIQUE Index on a table with appropriate fields to stop duplicate records.
Use INSERT IGNORE rather than INSERT.If the record is a duplicate, the IGNORE keyword tells MySQL to discard it silently without generating an error.
Use REPLACE rather than INSERT. If the record is new, it's inserted just as with INSERT. If it's a duplicate, the new record replaces the old one:
mysql> SELECT COUNT(*) as repetitions, last_name, first_name
-> FROM person_tbl
-> GROUP BY last_name, first_name
-> HAVING repetitions > 1; //counting and identifying duplicates
mysql> SELECT DISTINCT last_name, first_name
-> FROM person_tbl
-> ORDER BY last_name; //eliminate duplicate
mysql> SELECT last_name, first_name
-> FROM person_tbl
-> GROUP BY (last_name, first_name); //eliminate duplicate
mysql> CREATE TABLE tmp SELECT last_name, first_name, sex
-> FROM person_tbl;
-> GROUP BY (last_name, first_name);
mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl; //remove duplicates
mysql> ALTER IGNORE TABLE person_tbl
-> ADD PRIMARY KEY (last_name, first_name); //remove duplicates
MySQL and SQL Injection:
Injection usually occurs when you ask a user for input, like their name and instead of a name they give you a MySQL statement that you will unknowingly run on your database.Never trust user provided data, process this data only after validation; as a rule, this is done by pattern matching. To demonstrate the problem, consider this excerpt:
$name = "Qadir'; DELETE FROM users;";
mysql_query("SELECT * FROM users WHERE name='{$name}'");
Fortunately, if you use MySQL, the mysql_query() function does not permit query stacking or executing multiple queries in a single function call. If you try to stack queries, the call fails.
Preventing injection in mysql:
if (get_magic_quotes_gpc())
{
$name = stripslashes($name);
}
$name = mysql_real_escape_string($name);
mysql_query("SELECT * FROM users WHERE name='{$name}'");
Using like Quandry for prevention
$sub = addcslashes(mysql_real_escape_string("%something_"), "%_");
// $sub == \%something\_
mysql_query("SELECT * FROM messages WHERE subject LIKE '{$sub}%'");
MySQL Database Export - Backup Methods:
mysql> SELECT * FROM tutorials_tbl
-> INTO OUTFILE '/tmp/tutorials.txt'; //tab-delimited, linefeed-terminated file:
mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/tutorials.txt'
-> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
-> LINES TERMINATED BY '\r\n'; //CSV format with CRLF-terminated lines
Exporting Tables as Raw Data:
$ mysqldump -u root -p --no-create-info \
--tab=/tmp TUTORIALS tutorials_tbl
password ****** //dump the tutorials_tbl table from the TUTORIALS database to a file in the /tmp directory
Exporting Table Contents or Definitions in SQL Format:
$ mysqldump -u root -p TUTORIALS tutorials_tbl > dump.txt
password ******
$ mysqldump -u root -p TUTORIALS > database_dump.txt
password ****** //entire database
$ mysqldump -u root -p --all-databases > database_dump.txt
password ****** // all databases in host
MySQL Database Import - Recovery Methods
mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;
If the LOCAL keyword is not present, MySQL looks for the datafile on the server host using looking into absolute pathname fully specifies the location of the file, beginning from the root of the filesystem. By default, LOAD DATA assumes that datafiles contain lines that are terminated by linefeeds (newlines) and that data values within a line are separated by tabs.
mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl
-> FIELDS TERMINATED BY ':'
-> LINES TERMINATED BY '\r\n'; //specify file format
mysql> LOAD DATA LOCAL INFILE 'dump.txt'
-> INTO TABLE mytbl (b, c, a); //change column order
Importing Data with mysqlimport:
$ mysqlimport -u root -p --local database_name dump.txt
password *****
$ mysqlimport -u root -p --local --fields-terminated-by=":" \
--lines-terminated-by="\r\n" database_name dump.txt
password *****
$ mysqlimport -u root -p --local --columns=b,c,a \
database_name dump.txt
password *****
Copying Tables or Databases:
$ mysqldump -u root -p database_name table_name > dump.txt
password *****
$ mysql -u root -p database_name < dump.txt
password *****
$ mysqldump -u root -p database_name \
| mysql -h other-host.com database_name //another host
root@host# mysql -u root -p
Enter password:*******
mysql> use mysql;
Database changed
mysql> INSERT INTO user
(host, user, password,
select_priv, insert_priv, update_priv)
VALUES ('localhost', 'guest',
PASSWORD('guest123'), 'Y', 'Y', 'Y');
mysql> FLUSH PRIVILEGES; // This tells the server to reload the grant tables
mysql> SELECT host, user, password FROM user WHERE user = 'guest'; //displays result
Various privileges:
Select_priv,Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv,Reload_priv, Shutdown_priv,Process_priv,File_priv,Grant_priv,References_priv,Index_priv,Alter_priv
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON TUTORIALS.*
-> TO 'zara'@'localhost'
-> IDENTIFIED BY 'zara123'; //grant privileges
important MySQL commands:
USE Databasename : This will be used to select a particular database in MySQL workarea.
SHOW DATABASES: Lists the databases that are accessible by the MySQL DBMS.
SHOW TABLES: Shows the tables in the database once a database has been selected with the use command.
SHOW COLUMNS FROM tablename: Shows the attributes, types of attributes, key information, whether NULL is permitted, defaults, and other information for a table.
SHOW INDEX FROM tablename: Presents the details of all indexes on the table, including the PRIMARY KEY.
SHOW TABLE STATUS LIKE tablename\G: Reports details of the MySQL DBMS performance and statistics.
/etc/my.cnf File Configuration: Contains config related info
MySQL PHP Syntax:
mysql_function(value,value,...);
mysqli_connect($connect);
mysqli_query($connect,"SQL statement");
MySQL Connection:
[root@host]# mysql -u root -p
Enter password:******
mysql> exit
Using PHP:
connection mysql_connect(server,user,passwd,new_link,client_flag);
new_link - If a second call is made to mysql_connect() with the same arguments, no new connection will be established;
client_flags: MYSQL_CLIENT_SSL , MYSQL_CLIENT_COMPRESS, MYSQL_CLIENT_IGNORE_SPACE,MYSQL_CLIENT_INTERACTIVE
bool mysql_close ( resource $link_identifier );
MySQL Create Database:
[root@host]# mysqladmin -u root -p create TUTORIALS
Enter password:******
Using PHP:
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
$sql = 'CREATE DATABASE TUTORIALS';
$retval = mysql_query( $sql, $conn );
mysql_close($conn);
Drop MySQL Database:
[root@host]# mysqladmin -u root -p drop TUTORIALS
Enter password:******
Using PHP:
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
$sql = 'DROP DATABASE TUTORIALS';
$retval = mysql_query( $sql, $conn );
mysql_close($conn);
Selecting MySQL Database:
[root@host]# mysql -u root -p
Enter password:******
mysql> use TUTORIALS;
Using PHP:
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
mysql_select_db( 'TUTORIALS' );
mysql_close($conn);
MySQL Data Types:
Numeric Data Types:
INT , TINYINT,SMALLINT,MEDIUMINT,BIGINT,FLOAT(M,D) (display length (M),number of decimals (D)), DOUBLE(M,D), DECIMAL(M,D)
NUMERIC is a synonym for DECIMAL.
Date and Time Types:
DATE - A date in YYYY-MM-DD format
DATETIME - A date and time combination in YYYY-MM-DD HH:MM:SS format
TIMESTAMP - A timestamp between midnight, January 1, 1970 and sometime in 2037 stored as ( YYYYMMDDHHMMSS ).
TIME - Stores the time in HH:MM:SS format.
YEAR(M) - Stores a year in 2-digit or 4-digit format.
String Types:
CHAR(M) - A fixed-length string between 1 and 255 characters in length right-padded with spaces to the specified length when stored.
VARCHAR(M) - A variable-length string between 1 and 255 characters in length, You must define a length when creating a VARCHAR field.
BLOB or TEXT ,TINYBLOB or TINYTEXT .MEDIUMBLOB or MEDIUMTEXT, LONGBLOB or LONGTEXT
ENUM - a list of items from which the value must be selected (or it can be NULL).
Create MySQL Tables:
CREATE TABLE table_name (column_name column_type);
mysql> CREATE TABLE tutorials_tbl(
-> tutorial_id INT NOT NULL AUTO_INCREMENT,
-> tutorial_title VARCHAR(100) NOT NULL,
-> tutorial_author VARCHAR(40) NOT NULL,
-> submission_date DATE,
-> PRIMARY KEY ( tutorial_id )
-> );
Drop MySQL Tables:
DROP TABLE table_name ;
mysql> DROP TABLE tutorials_tbl
MySQL Insert Query:
INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN );
mysql> INSERT INTO tutorials_tbl
->(tutorial_title, tutorial_author, submission_date)
->VALUES
->("Learn MySQL", "Abdul S", NOW());
PHP SQL Syntax:
if(! get_magic_quotes_gpc() )
{
$tutorial_title = addslashes ($_POST['tutorial_title']);
$tutorial_author = addslashes ($_POST['tutorial_author']);
}
else
{
$tutorial_title = $_POST['tutorial_title'];
$tutorial_author = $_POST['tutorial_author'];
}
$submission_date = $_POST['submission_date'];
$sql = "INSERT INTO tutorials_tbl ".
"(tutorial_title,tutorial_author, submission_date) ".
"VALUES ".
"('$tutorial_title','$tutorial_author','$submission_date')";
For data from forms or user supplied data use get_magic_quotes_gpc() to check if current configuration for magic quote is set or not. If this function returns false, then use function addslashes() to add slashes before quotes.
MySQL Select Query:
SELECT field1, field2,...fieldN table_name1, table_name2...
[WHERE Clause]
[OFFSET M ][LIMIT N]
//OFFSET from where SELECT will start returning records, You can limit the number of returns using LIMIT attribute
mysql> SELECT * from tutorials_tbl
Using PHP:
$retval = mysql_query( $sql, $conn );
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
echo "Tutorial ID :{$row['tutorial_id']} <br> ".
"Title: {$row['tutorial_title']} <br> ".
"Author: {$row['tutorial_author']} <br> ".
"Submission Date : {$row['submission_date']} <br> ".
"--------------------------------<br>";
}
or
while($row = mysql_fetch_assoc($retval))
{
echo "Tutorial ID :{$row['tutorial_id']} <br> ".
"Title: {$row['tutorial_title']} <br> ".
"Author: {$row['tutorial_author']} <br> ".
"Submission Date : {$row['submission_date']} <br> ".
"--------------------------------<br>";
}
or
while($row = mysql_fetch_array($retval, MYSQL_NUM))
{
echo "Tutorial ID :{$row[0]} <br> ".
"Title: {$row[1]} <br> ".
"Author: {$row[2]} <br> ".
"Submission Date : {$row[3]} <br> ".
"--------------------------------<br>";
}
and
mysql_free_result($retval);
MySQL WHERE Clause:
SELECT field1, field2,...fieldN table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....
Operators like =, >, < are used with where clause
You can make your search case sensitive using BINARY keyword
MySQL UPDATE Query
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
mysql> UPDATE tutorials_tbl
-> SET tutorial_title='Learning JAVA'
-> WHERE tutorial_id=3;
MySQL DELETE Query
DELETE FROM table_name [WHERE Clause]
mysql> DELETE FROM tutorials_tbl WHERE tutorial_id=3;
MySQL LIKE Clause
A WHERE clause with equals sign (=) works fine where we want to do an exact match. Like if "tutorial_author = 'Sanjay'". But there may be a requirement where we want to filter out all the results where tutorial_author name should contain "jay". This can be handled using SQL LIKE clause along with WHERE clause.
If SQL LIKE clause is used along with % characters, then it will work like a meta character (*) in UNIX while listing out all the files or directories at command promptWithout a % character, LIKE clause is very similar to equals sign along with WHERE clause.
SELECT field1, field2,...fieldN table_name1, table_name2...
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'
mysql> SELECT * from tutorials_tbl
-> WHERE tutorial_author LIKE '%jay';
MySQL Sorting Results
SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]
mysql> SELECT * from tutorials_tbl ORDER BY tutorial_author ASC
Using MySQl Joins
You can use JOINS in SELECT, UPDATE and DELETE statements to join MySQL tables and return results in a single query
SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
-> FROM tutorials_tbl a, tcount_tbl b
-> WHERE a.tutorial_author = b.tutorial_author;
MySQL LEFT JOIN:
If I do a LEFT JOIN, I get all the records that match in the same way and IN ADDITION I get an extra record for each unmatched record in the left table of the join
mysql> SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
-> FROM tutorials_tbl a LEFT JOIN tcount_tbl b
-> ON a.tutorial_author = b.tutorial_author; //thus ensuring that every AUTHOR gets a mention:
Handling MySQL NULL Values:
Conditions involving NULL are special. You cannot use = NULL or != NULL to look for NULL values in columns. MySQL provides three operators
IS NULL: operator returns true if column value is NULL.
IS NOT NULL: operator returns true if column value is not NULL.
<=>: operator compares values, which (unlike the = operator) is true even for two NULL values.
mysql> create table tcount_tbl
-> (
-> tutorial_author varchar(40) NOT NULL,
-> tutorial_count INT
-> );
mysql> SELECT * FROM tcount_tbl WHERE tutorial_count = NULL;
mysql> SELECT * FROM tcount_tbl WHERE tutorial_count != NULL;
The above doesnt work so we write them as
mysql> SELECT * FROM tcount_tbl
-> WHERE tutorial_count IS NULL;
mysql> SELECT * from tcount_tbl
-> WHERE tutorial_count IS NOT NULL;
You can use if...else condition to prepare a query based on NULL value in php
if( isset($tutorial_count ))
{
$sql = 'SELECT tutorial_author, tutorial_count
FROM tcount_tbl
WHERE tutorial_count = $tutorial_count';
}
else
{
$sql = 'SELECT tutorial_author, tutorial_count
FROM tcount_tbl
WHERE tutorial_count IS $tutorial_count';
}
MySQL Regexps
^ Beginning of string
$ End of string
. Any single character
[...] Any character listed between the square brackets
[^...] Any character not listed between the square brackets
p1|p2|p3 Alternation; matches any of the patterns p1, p2, or p3
* Zero or more instances of preceding element
+ One or more instances of preceding element
{n} n instances of preceding element
{m,n} m through n instances of preceding element
Examples:
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';
MySQL Transactions
ACID:
Atomicity: ensures that all operations within the work unit are completed successfully; otherwise, the transaction is aborted at the point of failure and previous operations are rolled back to their former state.
Consistency: ensures that the database properly changes states upon a successfully committed transaction.
Isolation: enables transactions to operate independently on and transparent to each other.
Durability: ensures that the result or effect of a committed transaction persists in case of a system failure.
In MySQL, transactions begin with the statement BEGIN WORK and end with either a COMMIT or a ROLLBACK statement.
If AUTOCOMMIT is set to 1 (the default), then each SQL statement (within a transaction or not) is considered a complete transaction and committed by default when it finishes. When AUTOCOMMIT is set to 0, by issuing the SET AUTOCOMMIT=0 command, the subsequent series of statements acts like a transaction and no activities are committed until an explicit COMMIT statement is issued.
You can execute these SQL commands in PHP by using mysql_query() function.Begin transaction by issuing SQL command BEGIN WORK.Issue one or more SQL commands like SELECT, INSERT, UPDATE or DELETE.Check if there is no error and everything is according to your requirement.If there is any error, then issue ROLLBACK command, otherwise issue a COMMIT command.
There are many types of tables( GEMINI or BDB), which support transactions but most popular one is InnoDB.
mysql> create table tcount_tbl
-> (
-> tutorial_author varchar(40) NOT NULL,
-> tutorial_count INT
-> ) TYPE=InnoDB;
MySQL ALTER Command
change a name of your table, any table field or if you want to add or delete an existing column in a table.
mysql> ALTER TABLE testalter_tbl DROP i; //existing column i from above MySQL table
mysql> ALTER TABLE testalter_tbl ADD i INT; //add a column
ALTER TABLE testalter_tbl ADD i INT FIRST; //first column in table
ALTER TABLE testalter_tbl ADD i INT AFTER c; //add column afer column c
mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10); //change column c from CHAR(1) to CHAR(10)
ALTER TABLE testalter_tbl CHANGE i j BIGINT; //name the column you want to change, then specify the new definition
When you MODIFY or CHANGE a column, you can also specify whether or not the column can contain NULL values and what its default value is
mysql> ALTER TABLE testalter_tbl MODIFY j BIGINT NOT NULL DEFAULT 100;
mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000; //change default value
mysql> ALTER TABLE testalter_tbl ALTER i DROP DEFAULT; //drop default
mysql> ALTER TABLE testalter_tbl TYPE = MYISAM; //change table type
mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl; //rename table
MySQL INDEXES
indexes are also type of tables, which keep primary key or index field and a pointer to each record into the actual table.INSERT and UPDATE statements take more time on tables having indexes where as SELECT statements become fast on those tables.
CREATE UNIQUE INDEX index_name ON table_name ( column1, column2,...); //A unique index means that two rows cannot have the same index value
mysql> CREATE UNIQUE INDEX AUTHOR_INDEX ON tutorials_tbl (tutorial_author DESC) // index the values in a column in descending order
Just omit UNIQUE keyword from the query to create simple index to allows duplicate values in a table.
ALTER command to add and drop INDEX:
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): indexed values must be unique and cannot be NULL.
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): values must be unique (with the exception of NULL values, which may appear multiple times).
ALTER TABLE tbl_name ADD INDEX index_name (column_list):
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list): FULLTEXT index is used for text-searching purposes.
mysql> ALTER TABLE testalter_tbl DROP INDEX (c);
mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL; //primary key should not be null
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);
mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;
Vertical-format output (specified by \G) often is useful to display index, to avoid long line wraparound
mysql> SHOW INDEX FROM table_name\G
........
MySQL Temporary Tables
temporary tables is will be deleted when the current client session terminates.
mysql> CREATE TEMPORARY TABLE SalesSummary (
-> product_name VARCHAR(50) NOT NULL
-> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
-> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
-> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
mysql> DROP TABLE SalesSummary; //drop temporary tables
Same code can be used in PHP scripts using mysql_query() function.
MySQL Clone Tables
To get exact copy of a table including the same indexes, default values, and so forth:
Use SHOW CREATE TABLE to get a CREATE TABLE statement that specifies the source table's structure, indexes and all.
Modify the statement to change the table name to that of the clone table and execute the statement. This way, you will have exact clone table.
Optionally, if you need the table contents copied as well, issue an INSERT INTO ... SELECT statement, too.
Example Steps:
mysql> SHOW CREATE TABLE tutorials_tbl \G; // Get complete structure of table
mysql> CREATE TABLE `clone_tbl` (
-> `tutorial_id` int(11) NOT NULL auto_increment,
-> `tutorial_title` varchar(100) NOT NULL default '',
-> `tutorial_author` varchar(40) NOT NULL default '',
-> `submission_date` date default NULL,
-> PRIMARY KEY (`tutorial_id`),
-> UNIQUE KEY `AUTHOR_INDEX` (`tutorial_author`)
-> ) TYPE=MyISAM; //Rename this table and create another table.
mysql> INSERT INTO clone_tbl (tutorial_id,
-> tutorial_title,
-> tutorial_author,
-> submission_date)
-> SELECT tutorial_id,tutorial_title,
-> tutorial_author,submission_date,
-> FROM tutorials_tbl; //insert data from old table
Obtaining and Using MySQL Metadata
There are three informations, which you would like to have from MySQL.
Information about the result of queries: This includes number of records affected by any SELECT, UPDATE or DELETE statement.
Information about tables and databases: This includes information pertaining to the structure of tables and databases.
Information about the MySQL server: This includes current status of database server, version number etc.
Getting above info using php:
$result_id = mysql_query ($query, $conn_id); // reportnumber of rows in result
$count = ($result_id ? mysql_affected_rows ($conn_id) : 0);
print ("$count rows were affected\n");
$db_list = mysql_list_dbs($con); //list the tables in db
while ($db = mysql_fetch_object($db_list))
{
echo $db->Database . "<br />";
}
Getting Sertver data is done using following functions
SELECT VERSION( ) Server version string
SELECT DATABASE( ) Current database name (empty if none)
SELECT USER( ) Current username
SHOW STATUS Server status indicators
SHOW VARIABLES Server configuration variables
Using MySQL Sequences
The simplest way in MySQL to use Sequences is to define a column as AUTO_INCREMENT and leave rest of the things to MySQL to take care.
mysql> CREATE TABLE insect
-> (
-> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> PRIMARY KEY (id),
-> name VARCHAR(30) NOT NULL, # type of insect
-> date DATE NOT NULL, # date collected
-> origin VARCHAR(30) NOT NULL # where collected
);
LAST_INSERT_ID( ) is a SQL function to obtain the auto increment value. In php we use
mysql_query ("INSERT INTO insect (name,date,origin)
VALUES('moth','2001-09-14','windowsill')", $conn_id);
$seq = mysql_insert_id ($conn_id);
To resequence:
mysql> ALTER TABLE insect DROP id; //direct resequence may affect joins
mysql> ALTER TABLE insect
-> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
-> ADD PRIMARY KEY (id);
To start a sequence from particular value:
mysql> CREATE TABLE insect
-> (
-> id INT UNSIGNED NOT NULL AUTO_INCREMENT = 100,
-> PRIMARY KEY (id),
-> name VARCHAR(30) NOT NULL, # type of insect
-> date DATE NOT NULL, # date collected
-> origin VARCHAR(30) NOT NULL # where collected
);
mysql> ALTER TABLE t AUTO_INCREMENT = 100;
MySQL Handling Duplicates:
You can use a PRIMARY KEY or UNIQUE Index on a table with appropriate fields to stop duplicate records.
Use INSERT IGNORE rather than INSERT.If the record is a duplicate, the IGNORE keyword tells MySQL to discard it silently without generating an error.
Use REPLACE rather than INSERT. If the record is new, it's inserted just as with INSERT. If it's a duplicate, the new record replaces the old one:
mysql> SELECT COUNT(*) as repetitions, last_name, first_name
-> FROM person_tbl
-> GROUP BY last_name, first_name
-> HAVING repetitions > 1; //counting and identifying duplicates
mysql> SELECT DISTINCT last_name, first_name
-> FROM person_tbl
-> ORDER BY last_name; //eliminate duplicate
mysql> SELECT last_name, first_name
-> FROM person_tbl
-> GROUP BY (last_name, first_name); //eliminate duplicate
mysql> CREATE TABLE tmp SELECT last_name, first_name, sex
-> FROM person_tbl;
-> GROUP BY (last_name, first_name);
mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl; //remove duplicates
mysql> ALTER IGNORE TABLE person_tbl
-> ADD PRIMARY KEY (last_name, first_name); //remove duplicates
MySQL and SQL Injection:
Injection usually occurs when you ask a user for input, like their name and instead of a name they give you a MySQL statement that you will unknowingly run on your database.Never trust user provided data, process this data only after validation; as a rule, this is done by pattern matching. To demonstrate the problem, consider this excerpt:
$name = "Qadir'; DELETE FROM users;";
mysql_query("SELECT * FROM users WHERE name='{$name}'");
Fortunately, if you use MySQL, the mysql_query() function does not permit query stacking or executing multiple queries in a single function call. If you try to stack queries, the call fails.
Preventing injection in mysql:
if (get_magic_quotes_gpc())
{
$name = stripslashes($name);
}
$name = mysql_real_escape_string($name);
mysql_query("SELECT * FROM users WHERE name='{$name}'");
Using like Quandry for prevention
$sub = addcslashes(mysql_real_escape_string("%something_"), "%_");
// $sub == \%something\_
mysql_query("SELECT * FROM messages WHERE subject LIKE '{$sub}%'");
MySQL Database Export - Backup Methods:
mysql> SELECT * FROM tutorials_tbl
-> INTO OUTFILE '/tmp/tutorials.txt'; //tab-delimited, linefeed-terminated file:
mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/tutorials.txt'
-> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
-> LINES TERMINATED BY '\r\n'; //CSV format with CRLF-terminated lines
Exporting Tables as Raw Data:
$ mysqldump -u root -p --no-create-info \
--tab=/tmp TUTORIALS tutorials_tbl
password ****** //dump the tutorials_tbl table from the TUTORIALS database to a file in the /tmp directory
Exporting Table Contents or Definitions in SQL Format:
$ mysqldump -u root -p TUTORIALS tutorials_tbl > dump.txt
password ******
$ mysqldump -u root -p TUTORIALS > database_dump.txt
password ****** //entire database
$ mysqldump -u root -p --all-databases > database_dump.txt
password ****** // all databases in host
MySQL Database Import - Recovery Methods
mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;
If the LOCAL keyword is not present, MySQL looks for the datafile on the server host using looking into absolute pathname fully specifies the location of the file, beginning from the root of the filesystem. By default, LOAD DATA assumes that datafiles contain lines that are terminated by linefeeds (newlines) and that data values within a line are separated by tabs.
mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl
-> FIELDS TERMINATED BY ':'
-> LINES TERMINATED BY '\r\n'; //specify file format
mysql> LOAD DATA LOCAL INFILE 'dump.txt'
-> INTO TABLE mytbl (b, c, a); //change column order
Importing Data with mysqlimport:
$ mysqlimport -u root -p --local database_name dump.txt
password *****
$ mysqlimport -u root -p --local --fields-terminated-by=":" \
--lines-terminated-by="\r\n" database_name dump.txt
password *****
$ mysqlimport -u root -p --local --columns=b,c,a \
database_name dump.txt
password *****
Copying Tables or Databases:
$ mysqldump -u root -p database_name table_name > dump.txt
password *****
$ mysql -u root -p database_name < dump.txt
password *****
$ mysqldump -u root -p database_name \
| mysql -h other-host.com database_name //another host
No comments:
Post a Comment