SQL

SQL and MySQL queries

Select

ROW_COUNT / Affected Rows

SELECT ROW_COUNT();

ROW_COUNT() returns the number of rows changed, deleted, or inserted by the last statement if it was an UPDATE, DELETE, or INSERT. For other statements, the value may not be meaningful.

Concatenate

SELECT CONCAT('Rows affected: ',ROW_COUNT()) AS 'Affected Rows';

CASE

Case Expression / conditional statement / if statement

SELECT CASE WHEN columnZ <> 0 THEN 4
            WHEN columnY <> 0 THEN 3
            WHEN columnX <> 0 THEN 2
            WHEN columnW <> 0 THEN 1
            ELSE 0
       END AS your_alias_name
FROM ...

SELECT u.name,
  up.points,
  CASE WHEN u.access <> 0 THEN FROM_UNIXTIME(u.access) ELSE '' END AS 'Last activity on site',
FROM userpoints up INNER JOIN users u ON up.uid = u.uid

Joins

MySQL cases (can apply to MS SQL also, but not confirmed yet).

Different JOIN types:

• JOIN or INNER JOIN: Return rows when there is at least one match in both tables
• LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
• RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
• FULL JOIN: Return rows when there is a match in one of the tables

Example data:

Persons table
P_Id	Lastname	Firstname	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Orders table
O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	15

INNER JOIN

INNER JOIN is same as JOIN. As a matter of fact INNER JOIN is a default type of JOIN and that's why you don't have to include INNER keyword. Returns rows when there is at least one match in both tables.

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

The result-set will look like this:

LastName  FirstName   OrderNo
Hansen    Ola     24562
Hansen    Ola     22456
Pettersen   Kari    77895
Pettersen   Kari    44678

• Other examples:

SELECT mes.*, ind.uid
FROM pm_message mes
INNER JOIN pm_index ind ON mes.mid = ind.mid
WHERE ind.uid = 1234

LEFT JOIN

The LEFT JOIN keyword returns all rows from the left table (first table), even if there are no matches in the right table (table to join).

Note! In some databases LEFT JOIN is called LEFT OUTER JOIN. In MySQL LEFT OUTER JOIN is equal to LEFT JOIN.

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

The result-set will look like this:

LastName  FirstName   OrderNo
Hansen    Ola     22456
Hansen    Ola   24562
Pettersen   Kari    77895
Pettersen   Kari    44678
Svendson  Tove    

RIGHT JOIN

The RIGHT JOIN keyword returns all the rows from the right table (second table), even if there are no matches in the left table (first table).

Note! In some databases RIGHT JOIN is called RIGHT OUTER JOIN. In MySQL RIGHT OUTER JOIN is equal to RIGHT JOIN.

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

The result-set will look like this (all the orders with containing persons - if any):

LastName  FirstName   OrderNo
            34764
Hansen    Ola   22456
Hansen    Ola   24562
Pettersen   Kari    77895
Pettersen   Kari    44678

FULL JOIN

The FULL JOIN keyword return rows when there is a match in one of the tables. FULL JOIN = FULL OUTER JOIN.

• Note! MySQL lacks support for FULL OUTER JOIN.

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

Result:

LastName  FirstName   OrderNo
Hansen    Ola   22456
Hansen    Ola     24562
Pettersen   Kari    77895
Pettersen   Kari    44678
Svendson  Tove   
            34764

With MySQL you can mitigate the FULL JOIN by using LEFT JOIN, RIGHT JOIN and UNION together.

SELECT p.LastName, p.FirstName, o.OrderNo
FROM persons p
LEFT JOIN orders o ON p.P_Id = o.P_Id
UNION
SELECT p.LastName, p.FirstName, o.OrderNo
FROM persons p
RIGHT JOIN orders o ON p.P_Id = o.P_Id

Result:

LastName  FirstName   OrderNo
Hansen    Ola   22456
Hansen    Ola     24562
Svendson  Tove    
Pettersen   Kari    77895
Pettersen   Kari    44678
        34764

UNION

The UNION operator is used to combine the result-set of two or more SELECT statements.

Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

SQL UNION Syntax

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

• Note: The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL. The column names in the result-set of a UNION are always equal to the column names in the first SELECT statement in the UNION.

Example data:

Employees_Norway table
E_ID	E_Name
01	Hansen, Ola
02	Svendson, Tove
03	Svendson, Stephen
04	Pettersen, Kari

Employees_USA table
E_ID	E_Name
01	Turner, Sally
02	Kent, Clark
03	Svendson, Stephen
04	Scott, Stephen

• List all the different employees in Norway and USA

SELECT E_Name FROM Employees_Norway
UNION
SELECT E_Name FROM Employees_USA

The result-set will look like this:

E_Name
Hansen, Ola
Svendson, Tove
Svendson, Stephen
Pettersen, Kari
Turner, Sally
Kent, Clark
Scott, Stephen

Note: Only distinct employee names are shown.

• List all employees in Norway and USA

SELECT E_Name FROM Employees_Norway
UNION ALL
SELECT E_Name FROM Employees_USA

Result:

E_Name
Hansen, Ola
Svendson, Tove
Svendson, Stephen
Pettersen, Kari
Turner, Sally
Kent, Clark
Svendson, Stephen
Scott, Stephen

Note: Showing all names (Svendson, Stephen x 2).

GROUP BY

Select how many comments each users have made and sort those descending

SELECT uid, COUNT(uid) AS commentcount FROM comments GROUP BY uid ORDER BY commentcount DESC

IN Clause Order

Result set won't be in the same order than IN-clause's ids:

SELECT topic_id, topic_title from phpbb_topics where topic_id IN(5435, 23, 324)

Result set will be ordered as you like:

SELECT topic_id, topic_title from phpbb_topics where topic_id IN(5435, 23, 324) ORDER BY FIND_IN_SET(topic_id, '23,324,5435')

Substring

SELECT SUBSTRING('Quadratically',5); // prints 'ratically'
SELECT SUBSTRING('foobarbar' FROM 4); // prints 'barbar'
SELECT SUBSTRING('Quadratically',5,6); // prints 'ratica'
SELECT SUBSTRING('Sakila', -3); // prints 'ila'
SELECT SUBSTRING('Sakila', -5, 3); // prints 'aki'
SELECT SUBSTRING('Sakila' FROM -4 FOR 2); // prints 'ki'

Update

Update with replace: Replace url_alias table's dst column's all 'forums' words with 'keskustelu'

UPDATE url_alias SET dst = REPLACE(dst, 'forums', 'keskustelu')

Update with subquery:

UPDATE `gsmvalidation` SET confirmedtime = (SELECT NOW())
WHERE id = (SELECT id FROM (SELECT id,uid,codesended from `gsmvalidation`) AS temp WHERE uid = 1 ORDER BY `codesended` DESC LIMIT 1)

Insert

Insert single row:

INSERT INTO `drupal6`.`url_alias` (
`pid` ,
`src` ,
`dst` ,
`language`
)
VALUES (
NULL , 'phpbbforum/index.php', 'forums/index.php', ''
);

Insert multiple rows:

INSERT INTO url_alias (src, dst)
VALUES
('phpbbforum','forums'),
('phpbbforum/index.php', 'forums/index.php'),
('phpbbforum/viewtopic.php', 'forums/viewtopic.php');

Insert all rows from other table after emptying the destination table first:

TRUNCATE TABLE drupal622.dr_access;
INSERT INTO drupal622.dr_access SELECT * FROM drupal510.access;

Custom insert (there's no language column in source table):

INSERT INTO drupal622.dr_url_alias (pid,src,dst,language) SELECT pid,src,dst,'' FROM drupal510.url_alias

Delete

DELETE FROM Persons WHERE LastName = 'Smith'

Time and date

Convert timestamp to date

SELECT u.created FROM users u // 1346693013
SELECT FROM_UNIXTIME(u.created) as 'User created' FROM users u // 2012-09-03 18:23:33
SELECT DATE(FROM_UNIXTIME(u.created)) as 'User created' FROM users u // 2012-09-03

Convert date to timestamp

SELECT UNIX_TIMESTAMP('2012-08-01') // 1343775600
SELECT UNIX_TIMESTAMP('2012-08-01 01:02:03') //1343779323
// post_time = timestamp
SELECT * FROM phpbb_posts WHERE post_time BETWEEN UNIX_TIMESTAMP('2012-08-01') AND UNIX_TIMESTAMP('2012-09-05')

Convert datetime field values (CAST & DATE_FORMAT)

SELECT confirmedtime FROM gsm WHERE uid = 1 //2010-10-27 14:58:39
SELECT CAST(confirmedtime AS DATE) AS confirmedtime FROM gsm WHERE uid = 1 //2010-10-27
SELECT CAST('2012-09-04' AS DATETIME) // 2012-09-04 00:00:00
SELECT CAST('2012-09-04' AS DATE) // 2012-09-04
SELECT DATE_FORMAT(confirmedtime,'%d.%m.%Y') AS confirmedtime FROM gsm WHERE uid = 1 //27.10.2010

Select current datetime or timestamp

SELECT NOW()     -- 2010-09-09 08:54:53
SELECT CURDATE() -- 2010-09-09
SELECT CURTIME() -- 08:55:35
SELECT UNIX_TIMESTAMP(NOW()) -- 1314961714
SELECT UNIX_TIMESTAMP('2005-03-27 02:00:00') -- 1111885200

Select something where date is 2012-09-04

// userdata is in following format (timestamp-id): 1346746755-193
// two hours is added to timestamp to get Finnish time (GMT+2)
WHERE DATE(FROM_UNIXTIME((SELECT SUBSTRING(userdata, 1,10)+7200))) = CAST('2012-09-04' AS DATE)

Minus 1 day from current time for begintime field and add 20 years to endtime field

SELECT DATE_SUB(NOW(), INTERVAL 1 DAY), DATE_ADD(`endtime`, INTERVAL 20 YEAR)
FROM `publishdates`

Update rows which don't have publish from value (startpublish_value). new value this time - 2 days.

UPDATE `publishdates`
SET `startpublish` = DATE_SUB(NOW(), INTERVAL 2 DAY)
WHERE `startpublish` IS NULL OR `field_startpublish_value` = '0000-00-00 00:00:00'

Insert data to datetime (YYYY-mm-dd H:i:s) and date formatted fields

INSERT INTO logs (datetime,date) VALUES ('2005-06-12 23:56:25', '2005-06-12');

If you are using a DATE_FORMAT function to format date e.g. '%d.%m.%Y' and use variable placeholders e.g. %f, '%s' in same sql, you have to add DATE_FORMAT parameters through variable placeholder. Otherwise day %d takes the first variables value. Right way to do it:

$dateFormat = "%d.%m.%Y";
$gsmValidated = db_fetch_object(db_query("SELECT DATE_FORMAT(confirmedtime, '%s') AS confirmedtime FROM gsmvalidation WHERE uid = %f AND gsmtoconfirm = '%s' AND confirmedtime != '' ORDER BY `confirmedtime` DESC LIMIT 1", $dateFormat, $uid, $gsmnumberStr));

cast and between

// $D1 and $D2 are posted variables in PHP
SELECT * FROM Sales WHERE TransactionDate BETWEEN CAST('$D1' as DATETIME) and CAST($D2 as DATETIME)

Auto Increment Values

Show Next Value

Show next autoincrement value from files-table (check Auto_increment column):

SHOW TABLE STATUS LIKE 'files'

or better way to do it:

SELECT AUTO_INCREMENT FROM information_schema.tables
WHERE table_name = 'files'
AND table_schema = 'drupal6'

or show each tables' auto_increment values:

SELECT table_name, AUTO_INCREMENT FROM information_schema.tables
WHERE AUTO_INCREMENT IS NOT NULL AND table_schema = 'drupal6'

or select highest autoincrement value and add 1 to it:

SELECT MAX(fid)+1 FROM 'files'

• Note: If someone inserted new row after your query the next id is not up to date anymore. That happens also if the row with max id have been deleted.

Show Updated Row's Auto Increment ID Value

INSERT INTO 'files' ('fid', 'filename') VALUES ('','image.jpg');
SELECT LAST_INSERT_ID();

Reset Autoincrement Value

ALTER TABLE visitors AUTO_INCREMENT = 1

If the highest value for the column is already greater than new auto-increment value then it will be set to the highest value plus 1

Replace

REPLACE('where_to_look', 'what_to_look', 'the_replacement')

Select name and age. Convert the age column to number and replace 'years' string and whitespaces.

SELECT name, CONVERT(INT, REPLACE(REPLACE(age, 'years', ''), ' ', '')) AS age
FROM persons
ORDER BY age DESC

Update (limit to 10) nodes_revisions table's body fields content. Change readfile paths from /var/www/html/ to /srv/www/docroot/

UPDATE node_revisions
SET body = REPLACE(body, '/var/www/html/', '/srv/www/docroot/')
WHERE body LIKE '%readfile(\'/var/www%'
LIMIT 10

Convert And Cast

Convert String Into a Number

-- content string is 1346761037-192
SELECT CAST(SUBSTRING(content, 12) AS UNSIGNED INTEGER) AS theID
SELECT CONVERT(SUBSTRING(content, 12), SIGNED INTEGER) AS theID
SELECT SUBSTRING(content, 12) * 1 AS theID

Convert BLOB Into a String

SELECT CONVERT(exampleColumn USING utf8) AS exampleColumn FROM exampleTable

• In PhpMyAdmin you can also:
  • download the blob data, by clicking e.g. [BLOB - 6B] link (activates download), downloading it to your computer and opening it with text editor.
  • select "+ Options" from browse data mode and select Show BLOB contents checkbox.

Search String From BLOB

BLOB data is stored as a binary data thus it's case sensitive. You can make a case sensitive query by (returns only 'The'):

SELECT * FROM `dr_locales_source` WHERE source LIKE '%The%'

If you want to make case-insensitive comparison (e.g. The, the, thE), you can convert BLOB to string using utf8 encoding:

SELECT * FROM `dr_locales_source` WHERE CONVERT(source USING UTF8) LIKE '%The%'

NULL

Find NULL values:

SELECT * FROM USERS
WHERE 'middle_name' IS NULL

Find values different than NULL:

SELECT * FROM USERS
WHERE 'middle_name' IS NOT NULL

Explain

• Good source: http://www.slideshare.net/phpcodemonkey/mysql-explain-explained

Tool to optimize queries. The EXPLAIN statement can be used either as a way to obtain information about how MySQL executes a statement, or as a synonym for DESCRIBE.

Get details of the table:

EXPLAIN drupal7.node_revision
DESCRIBE drupal7.node_revision

Obtain information about the query:

EXPLAIN SELECT n.nid, n.title, n.type, frb.body_value, nr . *
FROM node n
JOIN node_revision nr ON n.nid = nr.nid
JOIN field_revision_body frb ON n.nid = frb.entity_id
ORDER BY n.nid DESC

EXPLAIN EXTENDED can be used to obtain additional information.

EXPLAIN EXTENDED SELECT n.nid, n.title
...

• A good way to keep up to date which queries need optimization and further investigation with EXPLAIN statement is to enable MySQL's Slow Query Log

• Use the mysqladmin command line tool to identify more general problems. The Following query will show mysql server's extended status information and updates it every 10 seconds.

mysqladmin -u dbuser -p -r -i 10 extended-status

Example: Booking application schema

attendees table
attendee_id	surname	conference_id	registration_status
INTEGER (PK)	VARCHAR	INTEGER(FK)	TINYINT

conferences table
conference_id	location_id	topic_id	date
INTEGER (PK)	INTEGER(FK)	INTEGER(FK)	DATE

EXPLAIN SELECT * FROM attendees WHERE conference_id = 123 AND registration_status > 0

• possible_keys - All possible indicies mysql could use. Query will choose the best one of the availeble ones (if any).
• key - The chosen index key for the query. If NULL, no useful index was found --> query needs to be optimized.
• rows - All the rows needed to scan for your query

table	possible_keys	key	rows
attendees	NULL	NULL	14052

Conclusion:

• No suitable indexes found for this query
• Mysql had to do a full table scan (almost always the slowest query)

ALTER TABLE ADD INDEX conf (conference_id);
ALTER TABLE ADD INDEX reg (registration_status);
EXPLAIN SELECT * FROM attendees WHERE conference_id = 123 AND registration_status > 0

table	possible_keys	key	rows
attendees	conf,reg	conf	331

• Mysql chose the more unique index conf and had to scan only 331 rows compared to previous full table scan with 14052 rows

MySQL

Databases

Create a Database

mysql Commandline Tool

First access the MySQL server

mysql -u root -p

Create database

CREATE DATABASE exampledatabase;

If you want to grant permissions also for someone else than root user

GRANT ALL ON exampledatabase.* TO exampleuser@localhost IDENTIFIED BY "examplepassword";

Exit mysql admin

quit

You can now test login with previously specified user

mysql -u exampleuser -p

Check if you see the right databases

SHOW DATABASES;

Select a Database

USE exampledatabase;

Delete / Drop a Database

DROP DATABASE `phpbb3seo`

Note! MySQL won't free the space after database is deleted. Instead of that it will reuse the space later when new data is added. File is probably: /var/lib/mysql/ibdata1.

• To shrink the size you will need to dump all databases and then restore them again.
• You can also do the following: Set innodb_file_per_table in your config file, which will create a separate file per table. That will then allow you to delete these files once a database is dropped and also allow you to use the OPTIMIZE TABLE command to rebuild ibd files if you shrink the data in a particular table.

Tables

MySQL Datatypes

Create a Table

CREATE TABLE IF NOT EXISTS `url_alias` (
  `pid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `src` varchar(128) NOT NULL DEFAULT '',
  `dst` varchar(128) NOT NULL DEFAULT '',
  `language` varchar(12) NOT NULL DEFAULT '',
  PRIMARY KEY (`pid`),
  UNIQUE KEY `dst_language_pid` (`dst`,`language`,`pid`),
  KEY `src_language_pid` (`src`,`language`,`pid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=35;

Create a Temporary Table

todo

Copy a Table

CREATE TABLE `drupal6`.`permission_bak` (
`pid` int( 11 ) NOT NULL AUTO_INCREMENT ,
`rid` int( 10 ) unsigned NOT NULL DEFAULT '0',
`perm` longtext,
`tid` int( 10 ) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY ( `pid` ) ,
KEY `rid` ( `rid` )
) ENGINE = MYISAM DEFAULT CHARSET = utf8;

INSERT INTO `drupal6`.`permission_bak`
SELECT *
FROM `drupal6`.`permission` ;

Delete/drop Table

DROP TABLE dr_imagecache_action

To avoid an error message in case the table don't exist (#1051 - Unknown table 'dr_imagecache_action' ) use IF EXISTS check:

DROP TABLE IF EXISTS dr_imagecache_action

Drop Multiple Tables

If you want to drop multiple tables using wildcard in table name, here's solution. Use SHOW TABLES to get the wanted table list, e.g.

SHOW TABLES LIKE 'phpbb_%';

Then just form your remove query with resulted text:

DROP TABLE phpbb_tbl1,phpbb_tbl2, phpbb_tbl3;

If you want to do this with shell commands:

mysql -u your_user -D your_database_name -e "show tables" -s | 
  egrep "^Whatever_" | 
  xargs -I "@@" echo mysql -u your_user -D your_database_name -e "DROP TABLE @@"

See more from: http://stackoverflow.com/questions/456751/drop-mysql-databases-matching-some-wildcard

Copy Multiple Tables Using Linux Shell Scripting

Execute a SQL query to get the wanted table names, e.g.

SHOW TABLES LIKE 'phpbb_%';

Save table names to text file:

vim tablenames.txt

Execute shell command to get the right queries. Pipe table names through xargs which takes each line in the list of table names, and inserts it in the "@@" part in the command

cat tablenames.txt | 
  xargs -I "@@" echo "CREATE TABLE drupal6.dr_@@ LIKE drupal5bu.@@; INSERT INTO drupal6.dr_@@ SELECT * FROM drupal5bu.@@;"

You can execute sql queries straight to sql-server by removing "echo"-part and adding mysql commands, e.g.

mysql -u root -D database_name -e "sql query;"

Rename Table

RENAME TABLE `drupal6`.`dr_imagecache_action_bak` TO `drupal6`.`dr_imagecache_action` ;

Print Table Names

SHOW TABLES; #print tables from current database
SHOW TABLES FROM drupal622; #show table names from specific database
SELECT table_name FROM information_schema.tables WHERE table_schema = 'drupal622'; #get table names from information_schema database

Columns

Edit column

ALTER TABLE `dr_url_alias` CHANGE `language` `language` VARCHAR( 12 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'exampledefaultvalue'
ALTER TABLE dr_imagecache_preset CHANGE presetid presetid int(10) UNSIGNED NOT NULL AUTO_INCREMENT
ALTER TABLE `dr_content_node_field` CHANGE `db_storage` `db_storage` TINYINT NOT NULL DEFAULT 1

Add Column

Add it as a first column in table with FIRST word at the end of sql.

ALTER TABLE `dr_permission` ADD `pid` INT( 11 ) NOT NULL AUTO_INCREMENT FIRST

Add timestamp column (int) after starttime column

ALTER TABLE `sportlog` ADD `timestamp` INT NOT NULL AFTER `starttime`

Add multiple columns:

ALTER TABLE `content_type_youtube_video`
  ADD COLUMN `field_youtube_video_status` int(10) unsigned NOT NULL DEFAULT '1',
  ADD COLUMN `field_youtube_video_version` int(10) unsigned NOT NULL DEFAULT '0';

Indexes

Index types: primary, index, unique, fulltext

Definition

• Primary key
• Unique key (combination of three columns?)
• Key (Normal key to make data search from the table faster)

PRIMARY KEY (`pid`),
UNIQUE KEY `dst_language_pid` (`dst`,`language`,`pid`),
KEY `src_language_pid` (`src`,`language`,`pid`)

Add Index

ALTER TABLE `dr_users_roles` ADD INDEX ( `rid` )

Add Unique Index

ALTER TABLE `dr_contemplate_files` ADD UNIQUE (`site`) 

Remove Index

You might want to remove one of the duplicate indicies, e.g. if you get following message in MySQLAdmin: "The following indexes appear to be equal and one of them should be removed: site_2, site"

ALTER TABLE `dr_contemplate_files` DROP INDEX `site_2`

Update Index

To rename indexes you have to recreate the index

ALTER TABLE `dr_contemplate_files` DROP INDEX `site_2` ,
ADD UNIQUE `site` ( `site` )

Views

MySQL_Server#Views

Backup database using mysqldump tool

Mysqldump

Backup database using phpMyAdmin web ui

Type e.g. URL http://localhost/phpmyadmin in your browser and choose:

• databases --> export
• Export format: SQL
• Save as file (e.g. gzipped)

Restore / Import Database

Restore database from a file

mysql -u root -p drupal6 < /root/Desktop/drupal6_20100912.sql

If you get following error message: ERROR 1049 (42000): Unknown database 'drupal6'

...you have to create the database before restoring:

mysql -u root -p -e 'CREATE DATABASE drupal6;'

Restore compressed backup file

gunzip < drupal6_bu_20110126.sql.gz | mysql -u root -p drupal6

Restore Table

Restore single database table from a file

mysql -u root -p drupal6 < /root/Desktop/users.sql

Restore compressed backup file (zip or lzip)

gunzip < users.sql.gz | mysql -u root -p drupal6
lzip -d < /var/backups/users.sql.lz | mysql -u root -p drupal6 (note! unpacks the file and removes .lz file!)
lzip -d --keep < /var/backups/users.sql.lz | mysql -u root -p drupal6 (note! keeps .lz file, without leaving uncompressed file)

Manage database users

Show All Users

SELECT User,Host FROM mysql.user;

GRANT / Create User

Create example user (if doesn't exist?) and give all permissions to example database

GRANT ALL PRIVILEGES ON `exampledatabase`.* TO 'exampleuser'@'localhost';

Create example user and give all permissions to example database and assign a password

GRANT ALL ON exampledatabase.* TO exampleuser@localhost IDENTIFIED BY "examplepassword";

Give example user selected permissions to drupal6_bu database

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `drupal6\_bu`.* TO 'exampleuser'@'182.128.10.2';

Related error messages:

Error: SELECT command denied to user 'drupal'@'www.site.com' for table 'url_alias'

REVOKE

Revoke all permissions and grant right from example user to example database

REVOKE ALL PRIVILEGES ON `exampledatabase`.* FROM exampleuser@localhost;
REVOKE GRANT OPTION ON `exampledatabase`.* FROM 'exampleuser'@'localhost';
REVOKE DROP ON `exampledatabase`.* FROM 'exampleuser'@'localhost';

DELETE USER

DROP USER exampleUser@localhost

Check The Size Of Your Databases

Execute the following query to your SQL Server:

SELECT table_schema 'Database name', sum( data_length + index_length ) / 1024 / 1024 'Database size (MB)'
FROM information_schema.TABLES GROUP BY table_schema

Check Database Table Information, e.g. Size (Show Table Info)

• Show table information. In addition to table size it shows also a lot of other information related to tables, e.g. table's mysql engine, update time if MyISAM

columns: Name, Engine, Version, Row_format, Rows, Avg_row_length, Data_length, Max_data_length, Index_length, Data_free, Auto_increment, Create_time, Update_time, Check_time, Collation, Checksum, Create_options, Comment

SHOW TABLE STATUS
SHOW TABLE STATUS LIKE 'sessions'

• More SHOW commands: http://dev.mysql.com/doc/refman/5.1/en/show.html