MySQL: Unterschied zwischen den Versionen
Zur Navigation springen
Zur Suche springen
Zeile 1: | Zeile 1: | ||
− | =Installation= | + | =Inbetriebnahme= |
+ | ==Installation== | ||
root@asuka:~# aptitude install mysql-server mysql-client | root@asuka:~# aptitude install mysql-server mysql-client | ||
− | =Login= | + | ==Login== |
root@asuka:~# mysql --user root -h localhost -p | root@asuka:~# mysql --user root -h localhost -p | ||
Enter password: XXXXXX | Enter password: XXXXXX | ||
Zeile 13: | Zeile 14: | ||
mysql> | mysql> | ||
− | =Status überprüfen= | + | ==Status überprüfen== |
mysql> status; | mysql> status; | ||
-------------- | -------------- | ||
Zeile 34: | Zeile 35: | ||
-------------- | -------------- | ||
− | =Hilfe aufrufen= | + | ==Hilfe aufrufen== |
mysql> help contents; | mysql> help contents; | ||
You asked for help about help category: "Contents" | You asked for help about help category: "Contents" | ||
Zeile 55: | Zeile 56: | ||
Utility | Utility | ||
− | =Datenbanken anzeigen= | + | ==Datenbanken anzeigen== |
mysql> show databases; | mysql> show databases; | ||
+--------------------+ | +--------------------+ | ||
Zeile 65: | Zeile 66: | ||
2 rows in set (0.00 sec) | 2 rows in set (0.00 sec) | ||
− | =Datenbank auswählen= | + | =Suchen und anzeigen= |
+ | ==Datenbank auswählen== | ||
mysql> use mysql; | mysql> use mysql; | ||
Reading table information for completion of table and column names | Reading table information for completion of table and column names | ||
Zeile 72: | Zeile 74: | ||
Database changed | Database changed | ||
− | =Datenbank erstellen= | + | ==Datenbank erstellen== |
mysql> create database world; | mysql> create database world; | ||
Query OK, 1 row affected (0.00 sec) | Query OK, 1 row affected (0.00 sec) | ||
Zeile 85: | Zeile 87: | ||
3 rows in set (0.00 sec) | 3 rows in set (0.00 sec) | ||
− | =Datenbank mit Daten von Backup füllen= | + | ==Datenbank mit Daten von Backup füllen== |
root@asuka:~# mysql world < world.sql -p | root@asuka:~# mysql world < world.sql -p | ||
Enter password: | Enter password: | ||
− | ==Download von Beispieldaten== | + | ===Download von Beispieldaten=== |
root@asuka:~# wget http://downloads.mysql.com/docs/world.sql.gz | root@asuka:~# wget http://downloads.mysql.com/docs/world.sql.gz | ||
root@asuka:~# gunzip world.sql.gz | root@asuka:~# gunzip world.sql.gz | ||
− | =Datenbank wechseln= | + | ==Datenbank wechseln== |
mysql> use world | mysql> use world | ||
Reading table information for completion of table and column names | Reading table information for completion of table and column names | ||
Zeile 100: | Zeile 102: | ||
Database changed | Database changed | ||
− | =Tabellen einer Datenbank anzeigen= | + | ==Tabellen einer Datenbank anzeigen== |
mysql> show tables; | mysql> show tables; | ||
+-----------------+ | +-----------------+ | ||
Zeile 111: | Zeile 113: | ||
3 rows in set (0.00 sec) | 3 rows in set (0.00 sec) | ||
− | =Tabellenbeschreibung anzeigen= | + | ==Tabellenbeschreibung anzeigen== |
mysql> describe City; | mysql> describe City; | ||
+-------------+----------+------+-----+---------+----------------+ | +-------------+----------+------+-----+---------+----------------+ | ||
Zeile 124: | Zeile 126: | ||
5 rows in set (0.00 sec) | 5 rows in set (0.00 sec) | ||
− | =Einträge anzeigen= | + | ==Einträge anzeigen== |
mysql> select * from City; | mysql> select * from City; | ||
+----+------------------+-------------+---------------+------------+ | +----+------------------+-------------+---------------+------------+ | ||
Zeile 136: | Zeile 138: | ||
(...) | (...) | ||
− | =Nur die ersten 10 Einträge anzeigen= | + | ==Nur die ersten 10 Einträge anzeigen== |
mysql> select * from City limit 10; | mysql> select * from City limit 10; | ||
+----+----------------+-------------+---------------+------------+ | +----+----------------+-------------+---------------+------------+ | ||
Zeile 154: | Zeile 156: | ||
10 rows in set (0.00 sec) | 10 rows in set (0.00 sec) | ||
− | =Anzeigen von Einträgen mit einer Bedingung= | + | ==Anzeigen von Einträgen mit einer Bedingung== |
mysql> select * from City where Population > 5000000; | mysql> select * from City where Population > 5000000; | ||
+------+-------------------+-------------+-------------------+------------+ | +------+-------------------+-------------+-------------------+------------+ | ||
Zeile 166: | Zeile 168: | ||
(...) | (...) | ||
− | =Umstellen der Zeichenkodierung= | + | ==Umstellen der Zeichenkodierung== |
mysql> SET NAMES 'utf8'; | mysql> SET NAMES 'utf8'; | ||
Query OK, 0 rows affected (0.00 sec) | Query OK, 0 rows affected (0.00 sec) | ||
Zeile 179: | Zeile 181: | ||
(...) | (...) | ||
− | =Bestimmte Spalten anzeigen aufgrund einer Bedingung= | + | ==Bestimmte Spalten anzeigen aufgrund einer Bedingung== |
mysql> select Name,Population from City where Population > 7000000; | mysql> select Name,Population from City where Population > 7000000; | ||
+-------------------+------------+ | +-------------------+------------+ | ||
Zeile 201: | Zeile 203: | ||
14 rows in set (0.00 sec) | 14 rows in set (0.00 sec) | ||
− | =Bestimmte Spalten anzeigen aufgrund mehrerer Bedingungen= | + | ==Bestimmte Spalten anzeigen aufgrund mehrerer Bedingungen== |
mysql> select Name from Country where Continent='Europe' AND Population > 30000000; | mysql> select Name from Country where Continent='Europe' AND Population > 30000000; | ||
+--------------------+ | +--------------------+ | ||
Zeile 217: | Zeile 219: | ||
8 rows in set (0.00 sec) | 8 rows in set (0.00 sec) | ||
− | =Ergebnis anzeigen mit Sortierung= | + | ==Ergebnis anzeigen mit Sortierung== |
mysql> select Name,Population from Country where Continent='Europe' AND Population > 30000000 Order by Population; | mysql> select Name,Population from Country where Continent='Europe' AND Population > 30000000 Order by Population; | ||
+--------------------+------------+ | +--------------------+------------+ | ||
Zeile 228: | Zeile 230: | ||
− | =Ergebnis anzeigen mit absteigender Sortierung= | + | ==Ergebnis anzeigen mit absteigender Sortierung== |
mysql> select Name,Population from Country where Continent='Europe' AND Population > 30000000 Order by Population desc; | mysql> select Name,Population from Country where Continent='Europe' AND Population > 30000000 Order by Population desc; | ||
+--------------------+------------+ | +--------------------+------------+ |
Version vom 19. Mai 2010, 09:44 Uhr
Inbetriebnahme
Installation
root@asuka:~# aptitude install mysql-server mysql-client
Login
root@asuka:~# mysql --user root -h localhost -p Enter password: XXXXXX Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 48 Server version: 5.1.41-3ubuntu12 (Ubuntu) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
Status überprüfen
mysql> status;
-------------- mysql Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486) using readline 6.1 Connection id: 48 SSL: Not in use Current pager: stdout Using outfile: Using delimiter: ; Server version: 5.1.41-3ubuntu12 (Ubuntu) Protocol version: 10 Connection: Localhost via UNIX socket Client characterset: latin1 Server characterset: latin1 UNIX socket: /var/run/mysqld/mysqld.sock Uptime: 41 min 4 sec Threads: 1 Questions: 207 Slow queries: 0 Opens: 476 Flush tables: 1 Open tables: 64 Queries per second avg: 0.84 --------------
Hilfe aufrufen
mysql> help contents; You asked for help about help category: "Contents" For more information, type 'help <item>', where <item> is one of the following categories: Account Management Administration Compound Statements Data Definition Data Manipulation Data Types Functions Functions and Modifiers for Use with GROUP BY Geographic Features Language Structure Plugins Table Maintenance Transactions User-Defined Functions Utility
Datenbanken anzeigen
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | +--------------------+ 2 rows in set (0.00 sec)
Suchen und anzeigen
Datenbank auswählen
mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed
Datenbank erstellen
mysql> create database world; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | world | +--------------------+ 3 rows in set (0.00 sec)
Datenbank mit Daten von Backup füllen
root@asuka:~# mysql world < world.sql -p Enter password:
Download von Beispieldaten
root@asuka:~# wget http://downloads.mysql.com/docs/world.sql.gz root@asuka:~# gunzip world.sql.gz
Datenbank wechseln
mysql> use world Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed
Tabellen einer Datenbank anzeigen
mysql> show tables; +-----------------+ | Tables_in_world | +-----------------+ | City | | Country | | CountryLanguage | +-----------------+ 3 rows in set (0.00 sec)
Tabellenbeschreibung anzeigen
mysql> describe City; +-------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------+------+-----+---------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | Name | char(35) | NO | | | | | CountryCode | char(3) | NO | | | | | District | char(20) | NO | | | | | Population | int(11) | NO | | 0 | | +-------------+----------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)
Einträge anzeigen
mysql> select * from City; +----+------------------+-------------+---------------+------------+ | ID | Name | CountryCode | District | Population | +----+------------------+-------------+---------------+------------+ | 1 | Kabul | AFG | Kabol | 1780000 | | 2 | Qandahar | AFG | Qandahar | 237500 | | 3 | Herat | AFG | Herat | 186800 | | 4 | Mazar-e-Sharif | AFG | Balkh | 127800 | | 5 | Amsterdam | NLD | Noord-Holland | 731200 | (...)
Nur die ersten 10 Einträge anzeigen
mysql> select * from City limit 10; +----+----------------+-------------+---------------+------------+ | ID | Name | CountryCode | District | Population | +----+----------------+-------------+---------------+------------+ | 1 | Kabul | AFG | Kabol | 1780000 | | 2 | Qandahar | AFG | Qandahar | 237500 | | 3 | Herat | AFG | Herat | 186800 | | 4 | Mazar-e-Sharif | AFG | Balkh | 127800 | | 5 | Amsterdam | NLD | Noord-Holland | 731200 | | 6 | Rotterdam | NLD | Zuid-Holland | 593321 | | 7 | Haag | NLD | Zuid-Holland | 440900 | | 8 | Utrecht | NLD | Utrecht | 234323 | | 9 | Eindhoven | NLD | Noord-Brabant | 201843 | | 10 | Tilburg | NLD | Noord-Brabant | 193238 | +----+----------------+-------------+---------------+------------+ 10 rows in set (0.00 sec)
Anzeigen von Einträgen mit einer Bedingung
mysql> select * from City where Population > 5000000; +------+-------------------+-------------+-------------------+------------+ | ID | Name | CountryCode | District | Population | +------+-------------------+-------------+-------------------+------------+ | 206 | S�o Paulo | BRA | S�o Paulo | 9968485 | | 207 | Rio de Janeiro | BRA | Rio de Janeiro | 5598953 | | 456 | London | GBR | England | 7285000 | | 608 | Cairo | EGY | Kairo | 6789479 | | 939 | Jakarta | IDN | Jakarta Raya | 9604900 | (...)
Umstellen der Zeichenkodierung
mysql> SET NAMES 'utf8'; Query OK, 0 rows affected (0.00 sec)
Weil:
mysql> select * from City where Population > 5000000; +------+---------------------+-------------+---------------------+------------+ | ID | Name | CountryCode | District | Population | +------+---------------------+-------------+---------------------+------------+ | 206 | São Paulo | BRA | São Paulo | 9968485 | | 207 | Rio de Janeiro | BRA | Rio de Janeiro | 5598953 | (...)
Bestimmte Spalten anzeigen aufgrund einer Bedingung
mysql> select Name,Population from City where Population > 7000000; +-------------------+------------+ | Name | Population | +-------------------+------------+ | São Paulo | 9968485 | | London | 7285000 | | Jakarta | 9604900 | | Mumbai (Bombay) | 10500000 | | Delhi | 7206704 | | Tokyo | 7980230 | | Shanghai | 9696300 | | Peking | 7472000 | | Seoul | 9981619 | | Ciudad de México | 8591309 | | Karachi | 9269265 | | Istanbul | 8787958 | | Moscow | 8389200 | | New York | 8008278 | +-------------------+------------+ 14 rows in set (0.00 sec)
Bestimmte Spalten anzeigen aufgrund mehrerer Bedingungen
mysql> select Name from Country where Continent='Europe' AND Population > 30000000; +--------------------+ | Name | +--------------------+ | United Kingdom | | Spain | | Italy | | Poland | | France | | Germany | | Ukraine | | Russian Federation | +--------------------+ 8 rows in set (0.00 sec)
Ergebnis anzeigen mit Sortierung
mysql> select Name,Population from Country where Continent='Europe' AND Population > 30000000 Order by Population; +--------------------+------------+ | Name | Population | +--------------------+------------+ | Poland | 38653600 | | Spain | 39441700 | | Ukraine | 50456000 | (...)
Ergebnis anzeigen mit absteigender Sortierung
mysql> select Name,Population from Country where Continent='Europe' AND Population > 30000000 Order by Population desc; +--------------------+------------+ | Name | Population | +--------------------+------------+ | Russian Federation | 146934000 | | Germany | 82164700 | | United Kingdom | 59623400 | | France | 59225700 | | Italy | 57680000 | | Ukraine | 50456000 | | Spain | 39441700 | | Poland | 38653600 | +--------------------+------------+ 8 rows in set (0.00 sec)