Creator: Created page with "== SQL and MySQL queries == === Select === ==== ROW_COUNT / Affected Rows ==== SELECT ROW_COUNT(); ROW_COUNT() returns the number of rows changed, deleted, or inserted by..."

2019-09-08T19:32:59Z

Created page with "== SQL and MySQL queries == === Select === ==== ROW_COUNT / Affected Rows ==== SELECT ROW_COUNT(); ROW_COUNT() returns the number of rows changed, deleted, or inserted by..."

New page

== 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

<pre>
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 ...
</pre>

<pre>
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
</pre>

=== Sort ===

=== Joins ===

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

Different <code>JOIN</code> 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:

{| cellpadding="2" border="1" style="border-collapse: collapse; float: left;"
! colspan="5" | Persons table
|-
! style="text-align: left;" | P_Id
!Lastname
!Firstname
!Address
!City
|-
|1
|Hansen
|Ola
|Timoteivn 10
| Sandnes
|-
|2
|Svendson
|Tove
|Borgvn 23
| Sandnes
|-
|3
|Pettersen
|Kari
|Storgt 20
|Stavanger
|}

{| cellpadding="2" style="border-collapse: collapse; border: 1px solid #000000; margin-left: 340px;"
! colspan="3" | Orders table
|-
! style="border: 1px solid #000000; text-align: left;" | O_Id
! style="border: 1px solid #000000; text-align: left;" | OrderNo
! style="border: 1px solid #000000; text-align: left;" | P_Id
|-
| style="border: 1px solid #000000; text-align: left;" | 1
| style="border: 1px solid #000000; text-align: left;" | 77895
| style="border: 1px solid #000000; text-align: left;" | 3
|-
| style="border: 1px solid #000000; text-align: left;" | 2
| style="border: 1px solid #000000; text-align: left;" | 44678
| style="border: 1px solid #000000; text-align: left;" | 3
|-
| style="border: 1px solid #000000; text-align: left;" | 3
| style="border: 1px solid #000000; text-align: left;" | 22456
| style="border: 1px solid #000000; text-align: left;" | 1
|-
| style="border: 1px solid #000000; text-align: left;" | 4
| style="border: 1px solid #000000; text-align: left;" | 24562
| style="border: 1px solid #000000; text-align: left;" | 1
|-
| style="border: 1px solid #000000; text-align: left;" | 5
| style="border: 1px solid #000000; text-align: left;" | 34764
| style="border: 1px solid #000000; text-align: left;" | 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.

<pre>
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName
</pre>
The result-set will look like this:
<pre>
LastName FirstName OrderNo
Hansen Ola 24562
Hansen Ola 22456
Pettersen Kari 77895
Pettersen Kari 44678
</pre>

* Other examples:

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

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

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

The result-set will look like this:
<pre>
LastName FirstName OrderNo
Hansen Ola 22456
Hansen Ola 24562
Pettersen Kari 77895
Pettersen Kari 44678
Svendson Tove
</pre>

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

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

The result-set will look like this (all the orders with containing persons - if any):
<pre>
LastName FirstName OrderNo
34764
Hansen Ola 22456
Hansen Ola 24562
Pettersen Kari 77895
Pettersen Kari 44678
</pre>

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

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

Result:
<pre>
LastName FirstName OrderNo
Hansen Ola 22456
Hansen Ola 24562
Pettersen Kari 77895
Pettersen Kari 44678
Svendson Tove
34764
</pre>

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

<pre>
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
</pre>

Result:
<pre>
LastName FirstName OrderNo
Hansen Ola 22456
Hansen Ola 24562
Svendson Tove
Pettersen Kari 77895
Pettersen Kari 44678
34764
</pre>

==== 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
<pre>
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
</pre>

* 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:

{| cellpadding="2" border="1" style="border-collapse: collapse; float: left;"
! colspan="2" | Employees_Norway table
|-
! style="text-align: left;" | E_ID
! align="left" | E_Name
|-
|01
|Hansen, Ola
|-
|02
|Svendson, Tove
|-
|03
|Svendson, Stephen
|-
|04
|Pettersen, Kari
|}

{| cellpadding="2" border="1" style="border-collapse: collapse; margin-left: 190px;"
! colspan="2" | Employees_USA table
|-
! style="text-align: left;" | E_ID
! align="left" | E_Name
|-
|01
|Turner, Sally
|-
|02
|Kent, Clark
|-
|03
|Svendson, Stephen
|-
|04
|Scott, Stephen
|}

* List '''all the different employees''' in Norway and USA
<pre>
SELECT E_Name FROM Employees_Norway
UNION
SELECT E_Name FROM Employees_USA
</pre>

The result-set will look like this:
<pre>
E_Name
Hansen, Ola
Svendson, Tove
Svendson, Stephen
Pettersen, Kari
Turner, Sally
Kent, Clark
Scott, Stephen
</pre>

'''Note''': Only distinct employee names are shown.

* List '''all employees''' in Norway and USA
<pre>
SELECT E_Name FROM Employees_Norway
UNION ALL
SELECT E_Name FROM Employees_USA
</pre>

Result:
<pre>
E_Name
Hansen, Ola
Svendson, Tove
Svendson, Stephen
Pettersen, Kari
Turner, Sally
Kent, Clark
Svendson, Stephen
Scott, Stephen
</pre>

'''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 ===

<pre>
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'
</pre>

=== 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:
<pre>
INSERT INTO `drupal6`.`url_alias` (
`pid` ,
`src` ,
`dst` ,
`language`
)
VALUES (
NULL , 'phpbbforum/index.php', 'forums/index.php', ''
);
</pre>

Insert multiple rows:
<pre>
INSERT INTO url_alias (src, dst)
VALUES
('phpbbforum','forums'),
('phpbbforum/index.php', 'forums/index.php'),
('phpbbforum/viewtopic.php', 'forums/viewtopic.php');
</pre>

Insert all rows from other table after emptying the destination table first:
<pre>
TRUNCATE TABLE drupal622.dr_access;
INSERT INTO drupal622.dr_access SELECT * FROM drupal510.access;
</pre>

Custom insert (there's no language column in source table):
<pre>
INSERT INTO drupal622.dr_url_alias (pid,src,dst,language) SELECT pid,src,dst,'' FROM drupal510.url_alias
</pre>

=== Delete ===

DELETE FROM Persons WHERE LastName = 'Smith'

=== Time and date ===

'''Convert timestamp to date'''
<pre>
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
</pre>

'''Convert date to timestamp'''
<pre>
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')
</pre>

Convert datetime field values (CAST & DATE_FORMAT)
<pre>
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
</pre>

Select current datetime or timestamp
<pre>
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
</pre>

Select something where date is 2012-09-04
<pre>
// 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)
</pre>

Minus 1 day from current time for begintime field and add 20 years to endtime field
<pre>
SELECT DATE_SUB(NOW(), INTERVAL 1 DAY), DATE_ADD(`endtime`, INTERVAL 20 YEAR)
FROM `publishdates`
</pre>

Update rows which don't have publish from value (startpublish_value). new value this time - 2 days.
<pre>
UPDATE `publishdates`
SET `startpublish` = DATE_SUB(NOW(), INTERVAL 2 DAY)
WHERE `startpublish` IS NULL OR `field_startpublish_value` = '0000-00-00 00:00:00'
</pre>

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:
<pre>
$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));
</pre>

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

=== 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:
<pre>
SELECT AUTO_INCREMENT FROM information_schema.tables
WHERE table_name = 'files'
AND table_schema = 'drupal6'
</pre>
or show each tables' auto_increment values:
<pre>
SELECT table_name, AUTO_INCREMENT FROM information_schema.tables
WHERE AUTO_INCREMENT IS NOT NULL AND table_schema = 'drupal6'
</pre>
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 ====

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

==== 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 ===

<pre>
REPLACE('where_to_look', 'what_to_look', 'the_replacement')
</pre>

Select name and age. Convert the age column to number and replace 'years' string and whitespaces.
<pre>
SELECT name, CONVERT(INT, REPLACE(REPLACE(age, 'years', ''), ' ', '')) AS age
FROM persons
ORDER BY age DESC
</pre>

Update (limit to 10) nodes_revisions table's body fields content. Change readfile paths from <code>/var/www/html/</code> to <code>/srv/www/docroot/</code>
<pre>
UPDATE node_revisions
SET body = REPLACE(body, '/var/www/html/', '/srv/www/docroot/')
WHERE body LIKE '%readfile(\'/var/www%'
LIMIT 10
</pre>

=== Convert And Cast ===

==== Convert String Into a Number ====

<pre>
-- 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
</pre>

==== 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 <code>Show BLOB contents</code> 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:
<pre>
SELECT * FROM USERS
WHERE 'middle_name' IS NULL
</pre>

Find values different than NULL:
<pre>
SELECT * FROM USERS
WHERE 'middle_name' IS NOT NULL
</pre>

=== 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:
<pre>
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
</pre>

'''EXPLAIN EXTENDED''' can be used to obtain additional information.

<pre>
EXPLAIN EXTENDED SELECT n.nid, n.title
...
</pre>

* A good way to keep up to date which queries need optimization and further investigation with <code>EXPLAIN</code> 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
{| border="1" cellpadding="2" style="border-collapse: collapse;"
! colspan="4" | attendees table
|-
!attendee_id
!surname
!conference_id
!registration_status
|-
| INTEGER (PK)
| VARCHAR
| INTEGER(FK)
| TINYINT
|}

{| border="1" cellpadding="2" style="border-collapse: collapse;"
! colspan="4" | conferences table
|-
!conference_id
!location_id
!topic_id
!date
|-
| INTEGER (PK)
| INTEGER(FK)
| INTEGER(FK)
| DATE
|}

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

* '''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

{| cellpadding="2" border="1" style="border-collapse: collapse;"
!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

{| cellpadding="2" border="1" style="border-collapse: collapse;"
!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_Server#MySQL_Datatypes | MySQL Datatypes]]

==== Create a Table ====

<pre>
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;
</pre>

==== Create a Temporary Table ====

todo

==== Copy a Table ====

<pre>
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` ;
</pre>

==== Delete/drop Table ====

DROP TABLE dr_imagecache_action
To avoid an error message in case the table don't exist (<code>#1051 - Unknown table 'dr_imagecache_action' </code>) use <code>IF EXISTS</code> 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 <code>SHOW TABLES</code> 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:
<pre>
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 @@"
</pre>
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 <code>xargs</code> which takes each line in the list of table names, and inserts it in the "@@" part in the command
<pre>
cat tablenames.txt |
xargs -I "@@" echo "CREATE TABLE drupal6.dr_@@ LIKE drupal5bu.@@; INSERT INTO drupal6.dr_@@ SELECT * FROM drupal5bu.@@;"
</pre>

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 ====

<pre>
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
</pre>

=== Columns ===

==== Edit column ====

<pre>
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
</pre>

==== Add Column ====

Add it as a first column in table with <code>FIRST</code> 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:
<pre>
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';
</pre>

=== 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)
<pre>
PRIMARY KEY (`pid`),
UNIQUE KEY `dst_language_pid` (`dst`,`language`,`pid`),
KEY `src_language_pid` (`src`,`language`,`pid`)
</pre>

==== 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 Database ===

'''Restore database from a file'''
mysql -u root -p drupal6 < /root/Desktop/drupal6_20100912.sql

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

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

<pre>
SELECT User,Host FROM mysql.user;
</pre>

==== 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
<pre>
SHOW TABLE STATUS
SHOW TABLE STATUS LIKE 'sessions'
</pre>

* More <code>SHOW</code> commands: http://dev.mysql.com/doc/refman/5.1/en/show.html

← Older revision		Revision as of 22:37, 26 March 2021
Line 882:		Line 882:


−	=== Restore Database ===	+	=== Restore / Import Database ===

	'''Restore database from a file'''		'''Restore database from a file'''

SQL - Revision history

Creator at 22:38, 26 March 2021

Creator at 22:37, 26 March 2021

Creator: Created page with "== SQL and MySQL queries == === Select === ==== ROW_COUNT / Affected Rows ==== SELECT ROW_COUNT(); ROW_COUNT() returns the number of rows changed, deleted, or inserted by..."

@@ Line 751: / Line 751: @@
 </pre>
-==== Delete/drop Table ====
+==== Delete / Drop Table ====
   DROP TABLE dr_imagecache_action