Content added Content deleted
imported>mutante |
imported>DrOwl No edit summary |
||
| (2 intermediate revisions by one other user not shown) | |||
| Line 1: | Line 1: | ||
Also see: [[MySQL]] |
|||
The world's most popular open source database. |
|||
<man>mysql</man> |
|||
* [[http://www.mysql.com/ MySQL Homepage]] |
|||
{{manpage}} |
|||
* [[http://dev.mysql.com/doc/ MySQL Documentation]] |
|||
* [[http://dev.mysql.com/doc/mysql/en/index.html Searchable Reference Manual]] |
|||
== Usefull hints == |
|||
---- |
|||
Just some useful notes from different locations on the internet |
|||
=== Sort By IP address === |
|||
From O'Reillys MySQL Cookbook: |
|||
SELECT ip FROM hostip |
|||
=== Examples === |
|||
ORDER BY |
|||
SUBSTRING_INDEX(ip, '.', 1) + 0, |
|||
SUBSTRING_INDEX(SUBSTRING_INDEX(ip, '.', -3), '.', 1) + 0, |
|||
SUBSTRING_INDEX(SUBSTRING_INDEX(ip, '.', -2), '.', 1) + 0, |
|||
SUBSTRING_INDEX(ip, '.', -1) + 0; |
|||
or (MySQL 3.23.15 an above) |
|||
The structure from top to bottom is: server->database->table->field->content |
|||
SELECT ip FROM hostip |
|||
So get to the place you want in this order: |
|||
ORDER BY INET_ATON(ip); |
|||
=== Connecting to mysql server from the [[shell]]. === |
|||
<pre> |
|||
shell> mysql -u root -p |
|||
Enter password: |
|||
Welcome to the MySQL monitor. |
|||
mysql> |
|||
</pre> |
|||
=== Switching into a database. === |
|||
<pre> |
|||
mysql> use wikidb; |
|||
Database changed |
|||
mysql> |
|||
</pre> |
|||
=== Showing tables === |
|||
<pre> |
|||
mysql> show tables; |
|||
+------------------+ |
|||
| Tables_in_wikidb | |
|||
+------------------+ |
|||
| archive | |
|||
| blobs | |
|||
... |
|||
| imagelinks | |
|||
| interwiki | |
|||
... |
|||
| watchlist | |
|||
+------------------+ |
|||
23 rows in set (0.00 sec) |
|||
</pre> |
|||
=== Getting field names === |
|||
<pre> |
|||
mysql> describe interwiki; |
|||
+-----------+------------+------+-----+---------+-------+ |
|||
| Field | Type | Null | Key | Default | Extra | |
|||
+-----------+------------+------+-----+---------+-------+ |
|||
| iw_prefix | char(32) | | PRI | | | |
|||
| iw_url | char(127) | | | | | |
|||
| iw_local | tinyint(1) | | | 0 | | |
|||
+-----------+------------+------+-----+---------+-------+ |
|||
3 rows in set (0.00 sec) |
|||
</pre> |
|||
=== Selecting content === |
|||
<pre> |
|||
mysql> select iw_prefix from interwiki; |
|||
+-------------------+ |
|||
| iw_prefix | |
|||
+-------------------+ |
|||
| AbbeNormal | |
|||
| AcadWiki | |
|||
| Acronym | |
|||
| Advogato | |
|||
| AIWiki | |
|||
... |
|||
| Wiktionary | |
|||
| YpsiEyeball | |
|||
| ZWiki | |
|||
+-------------------+ |
|||
107 rows in set (0.00 sec) |
|||
</pre> |
|||
=== Using wildcards === |
|||
You can use [[wildcards]] like in: |
|||
<pre>mysql> select * from interwiki;</pre> |
|||
=== Conditions (WHERE-clause) === |
|||
You can combine with conditions like in: |
|||
<pre> |
|||
mysql> select iw_url from interwiki where iw_prefix="UseMod"; |
|||
+------------------------------------------+ |
|||
| iw_url | |
|||
+------------------------------------------+ |
|||
| http://www.usemod.com/cgi-bin/wiki.pl?$1 | |
|||
+------------------------------------------+ |
|||
1 row in set (0.00 sec) |
|||
</pre> |
|||
.. to be extended ... |
|||
=== External Links === |
|||
* [[http://www.mysql.com/ MySQL Homepage]] |
|||
* [[http://dev.mysql.com/doc/ MySQL Documentation]] |
|||
* [[http://dev.mysql.com/doc/mysql/en/index.html Searchable Reference Manual]] |
|||
Latest revision as of 14:25, 30 November 2011
Also see: MySQL
<man>mysql</man>
Usefull hints[edit]
Just some useful notes from different locations on the internet
Sort By IP address[edit]
From O'Reillys MySQL Cookbook:
SELECT ip FROM hostip ORDER BY SUBSTRING_INDEX(ip, '.', 1) + 0, SUBSTRING_INDEX(SUBSTRING_INDEX(ip, '.', -3), '.', 1) + 0, SUBSTRING_INDEX(SUBSTRING_INDEX(ip, '.', -2), '.', 1) + 0, SUBSTRING_INDEX(ip, '.', -1) + 0;
or (MySQL 3.23.15 an above)
SELECT ip FROM hostip ORDER BY INET_ATON(ip);