MySql

mysql Tool, admin queries

Zum Server verbinden:

mysql -h localhost -u root -p

SQL Skript ausführen

mysql -h localhost -u someuser < myfile.sql

Datenbanken anzeigen:

mysql> show databases;

Database erstellen:

mysql> CREATE DATABASE menagerie;

Datenbank brauchen:

mysql> USE menagerie

Alle Tabellen einer DB anzeigen:

 mysql> SHOW TABLES; 

Tabelle erstellen:

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

Tabelle mit Primary Key und Autoincrement erstellen:

mysql> CREATE TABLE PETS2 
 (ID BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR(50));

Tabelle beschreiben:

mysql> DESCRIBE pet;

User erstellen:

mysql> CREATE USER 'monty'@'localhost' IDENTIFIED BY 'userpassword';
mysql> CREATE USER 'username'@'%' IDENTIFIED BY 'userpassword';
mysql> GRANT ALL ON pets.* TO 'claude'@'localhost';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' WITH GRANT OPTION;

Alle user auflisten:

mysql> select User from  mysql.user;

Export und Import

Export in SQL Skripte:

mysqldump dbname > dumpfile.sql

Mit User und Pwd:

mysqldump --add-drop-table -u root -p[password] -h [hostname] mydb > /home/john/mydb.sql

Import:

mysql -p dbname -u USER -p < dumpfile.sql

Wie kann ich in einem Skript ein anderes inlcuden?

SOURCE create-triggers.sql;
SOURCE create-sampledata.sql;

Siehe auch http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

UTF8

Default Characterset ist latin1. Um auf utf8 zu wechseln, die Datei my.cnf im Verzeichnis /etc/mysql anpassen:

[client]
default-character-set = utf8

[mysql]
default-character-set=utf8

[mysqld]
character_set_server = utf8
character_set_client = utf8

Collation: Reihenfolge der Zeichen (z.B. bei Orderby).

So kanns geprüft werden:

SHOW VARIABLES LIKE 'character_set%';

http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html

Leider ist der default latin1. So kann man die Datenbank in unicode umwandeln:

• 1) dump

mysqldump -u root -p dbname > dbname.sql

• 2) replace latin1 mit utf8

sed  -e 's/latin1/utf8/g' -i ./dbname.sql

• 3) daten laden

mysql -u root -p dbname < dbname.sql

http://tlug.dnho.net/?q=node/276

Wichtig: latin1 braucht genau 1 byte pro Zeichen. UTF-8 ist variabel, maximal 4 bytes pro Zeichen. Ein Textfeld mit 4 Zeichen muss also mit 4 multipliziert werden, also 16 bytes (für den Worst-case).

mysql ddl

Einfache Tabelle anlegen

CREATE TABLE USER (ID BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
                   NAME VARCHAR(50) NOT NULL UNIQUE KEY,
                   PASSWORD VARCHAR(50) NOT NULL,
                   EMAIL VARCHAR(50) NOT NULL);

Index anlegen

Siehe create-index referenz 5.1

CREATE TABLE lookup (id INT) ENGINE = MEMORY;
CREATE INDEX id_index USING BTREE ON lookup (id);

Index direkt im CREATE TABLE Statement:

CREATE TABLE USER (ID SERIAL PRIMARY KEY,  
                   NAME VARCHAR(50) NOT NULL UNIQUE KEY,                 
                   EMAIL VARCHAR(50) NOT NULL, 
                   INDEX INDEX_NAME (NAME),
                   INDEX INDEX_EMAIL(EMAIL));

Trigger anlegen

DELIMITER |
CREATE TRIGGER TR_USER_INSERT BEFORE INSERT ON USER
  FOR EACH ROW BEGIN
    SET NEW.CREATED_AT = NOW();
  END;|
DELIMITER ;

Foreign Key Constraints

Es ist mit einigen Engines möglich, Foreign Keys zu brauchen:

CREATE TABLE parent (id INT NOT NULL,
                     PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (id INT, parent_id INT,
                    INDEX par_ind (parent_id),
                    FOREIGN KEY (parent_id) REFERENCES parent(id)
                      ON DELETE CASCADE
) ENGINE=INNODB;

Alter Table

Spalte hinzufügen:

ALTER TABLE TESTRESULT ADD COLUMN
   CLIENTTYPE SMALLINT NOT NULL DEFAULT 1;

Spalte entfernen:

ALTER TABLE TESTRESULT DROP COLUMN CLIENTTYPE

Spalte ändern:

ALTER TABLE TAG MODIFY ID_TESTCATEGORY BIGINT UNSIGNED NULL;

Foreign Key entfernen:

ALTER TABLE PARAGRAPH DROP FOREIGN KEY PARAGRAPH_ibfk_1;

Foreign Key Namen herausfinden:

show create table PARAGRAPH;

mysql queires

Datenlimitierungen:

 SELECT * FROM pet p LIMIT 0,1000

Vergebene id holen (auto_increment):

 select LAST_INSERT_ID();

Insert Into Sample:

INSERT INTO TEST_PARAGRAPH (ID_TEST, ID_PARAGRAPH, NUMQUESTIONS, MAIN)
SELECT P.ID_TEST AS ID_TEST, P.ID AS ID_PARAGRAPH, P.NUMQUESTIONS AS NUMQUESTIONS, TRUE AS MAIN FROM `PARAGRAPH` AS P

Datentypen

• SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.
• BOOL, BOOLEAN
• TINYINT, SMALLINT, MEDIUMINT, INT

Siehe http://dev.mysql.com/doc/refman/6.0/en/data-type-overview.html

Tabelle reparieren, z.B. bei Fehlernummer 145

• repair table mytable
• check table mytable

Foreign keys auflisten

SELECT

    concat(table_name, '.', column_name) as 'foreign key', 
    concat(referenced_table_name, '.', referenced_column_name) as 'references',
    CONSTRAINT_NAME AS 'name'
FROM
    information_schema.key_column_usage
WHERE
    referenced_table_name is not null;

Und so kann der Foreign Key gelöscht werden:

ALTER TABLE TAG DROP FOREIGN KEY tag_ibfk_1;
ALTER TABLE TAG ADD FOREIGN KEY (ID_TESTCATEGORY) REFERENCES TESTCATEGORY(ID);

.net Clients

Das Assembly mit dem Treiber heisst MySql.Data.dll. Hier ein kleines Sample:

using System;
using System.Data;
using MySql.Data;
using MySql.Data.MySqlClient;

namespace BackgroundWorkerSample
{
    public  class SampleForm 
    {
        public SampleForm()
        {
            
        }

        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        [STAThread]
        static void Main()
        {
  		Console.WriteLine("the app");

        	string connStr = "server=localhost;user=claude;database=pets;port=3306;";
        	using(MySqlConnection conn = new MySqlConnection(connStr)){
    	        	try
        		{
            			Console.WriteLine("Connecting to MySQL...");
            			conn.Open();
  				MySqlCommand command = conn.CreateCommand();
  				command.CommandType = CommandType.Text;
  				command.CommandText = "select name, owner, sex from pet";
  				MySqlDataReader rdr = command.ExecuteReader();
  				DataTable table = new DataTable();
  				table.Load(rdr);
  				foreach (DataRow row in table.Rows)
  				{
    					
    					string name = row[0] as string;
 					Console.WriteLine(name);
  				}                                            			
            			conn.Close();
        		}
        		catch (Exception ex)
        		{
            			Console.WriteLine(ex.ToString());
        		}
        		Console.WriteLine("Done.");
		}
   

        } //End Main            
    } //SampleForm
}

Resourcen

• ADO.NET
• Klassendoku 5.1

java clients

• jar heisst mysql-connector-java-[ver]-bin.jar
• Driverclass: com.mysql.jdbc.Driver
• URL: jdbc:mysql://localhost/test?user=monty&password=greatsqldb

Resourcen

• 5.1 reference manual
• 5.1 Error Codes