MySQL: Unterschied zwischen den Versionen
Zur Navigation springen
Zur Suche springen
Thomas (Diskussion | Beiträge) |
Thomas (Diskussion | Beiträge) |
||
Zeile 88: | Zeile 88: | ||
+--------------------+ | +--------------------+ | ||
3 rows in set (0.00 sec) | 3 rows in set (0.00 sec) | ||
− | |||
− | |||
− | |||
− | |||
===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 | ||
oder bei problemen hier | oder bei problemen hier | ||
− | [[ | + | [[Media:world.sql.gz]] |
root@asuka:~# gunzip world.sql.gz | root@asuka:~# gunzip world.sql.gz | ||
+ | |||
+ | ==Datenbank mit Daten von Backup füllen== | ||
+ | root@asuka:~# mysql world < world.sql -p | ||
+ | Enter password: | ||
==Datenbank wechseln== | ==Datenbank wechseln== |
Version vom 22. September 2015, 09:18 Uhr
Inbetriebnahme
Installation
root@asuka:~# aptitude install mysql-server mysql-client
Schnellübersicht
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
Suchen und anzeigen
Datenbanken anzeigen
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | +--------------------+ 2 rows in set (0.00 sec)
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)
Download von Beispieldaten
root@asuka:~# wget http://downloads.mysql.com/docs/world.sql.gz oder bei problemen hier Media:world.sql.gz
root@asuka:~# gunzip world.sql.gz
Datenbank mit Daten von Backup füllen
root@asuka:~# mysql world < world.sql -p Enter password:
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)
Erstellen von Datenbanken
Datenbank erstellen und auswählen
mysql> create database telefonbuch; Query OK, 1 row affected (0.00 sec) mysql> use telefonbuch; Database changed
Tabelle erstellen
mysql> create table rufnummern ( -> id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY, -> vorname VARCHAR(128), -> nachname VARCHAR(128), -> vorwahl VARCHAR(32), -> rufnummer VARCHAR(32) -> ); Query OK, 0 rows affected (0.03 sec)
Datensätze in die Tabelle eintragen
mysql> insert into -> rufnummern (id,vorname,nachname,vorwahl,rufnummer) -> values -> (1, -> 'Karl' -> , -> 'Heinz', -> '001', -> '123456' -> ); Query OK, 1 row affected (0.00 sec)
Datensatz anzeigen
mysql> select * from rufnummern; +----+---------+----------+---------+-----------+ | id | vorname | nachname | vorwahl | rufnummer | +----+---------+----------+---------+-----------+ | 1 | Karl | Heinz | 001 | 123456 | +----+---------+----------+---------+-----------+ 1 row in set (0.00 sec)
Weitere Datensätze eintragen
mysql> insert into rufnummern (vorname,nachname,vorwahl,rufnummer) values ('xinux' , 'tux', '002', '654321' ); Query OK, 1 row affected (0.00 sec)
Die id wird durch Auto increment eigenständig erhöht.
Erweiterter Datensatz anzeigen
mysql> select * from rufnummern; +----+---------+----------+---------+-----------+ | id | vorname | nachname | vorwahl | rufnummer | +----+---------+----------+---------+-----------+ | 1 | Karl | Heinz | 001 | 123456 | | 2 | xinux | tux | 002 | 654321 | +----+---------+----------+---------+-----------+ 2 rows in set (0.00 sec)
Aktualisierung von Datensätzen
mysql> update rufnummern set rufnummer = '777444' where vorname = 'Karl'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
Aktualisierter Datensatz anzeigen
mysql> select * from rufnummern; +----+---------+----------+---------+-----------+ | id | vorname | nachname | vorwahl | rufnummer | +----+---------+----------+---------+-----------+ | 1 | Karl | Heinz | 001 | 777444 | | 2 | xinux | tux | 002 | 654321 | +----+---------+----------+---------+-----------+ 2 rows in set (0.00 sec)
MySQL Root Passwort neu setzen
Alternativ können Sie das neue Passwort auf jeder Plattform auch mit dem Client mysql einstellen (aber diese Methode ist nicht so sicher):
1. Halten Sie mysqld an und starten Sie ihn erneut mit den Optionen --skip-grant-tables --user=root (Windows-Nutzer lassen --user=root weg).
root@asuka:~# /etc/init.d/mysql stop root@asuka:~# mysqld --skip-grant-tables --user=root
2. Verbinden Sie sich mit folgendem Befehl mit dem mysqld-Server:
shell> mysql -u root
3. Geben Sie im mysql-Client folgende Anweisungen:
mysql> UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root'; mysql> FLUSH PRIVILEGES;
Ersetzen Sie „newpwd“ durch das root-Passwort, das Sie in Wirklichkeit benutzen möchten.
mysql> quit Bye root@asuka:/etc# /etc/init.d/mysql restart * Stopping MySQL database server mysqld * Starting MySQL database server mysqld
4. Das neue Passwort sollte nun funktionieren.