MySQL

Aus xinux.net
Zur Navigation springen Zur Suche springen

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.

Siehe auch

Abgerufen von „https://xinux.net/index.php?title=MySQL&oldid=7265“