MySQL

MySQL logotyp MySQL logotyp MySQLi logotyp

Webbplats

MySQL.com Ladda ner Workbench

Förkortningar

  • MySQLi - MySQL Improved
  • SQL - Structured Query Language
  • SAS - Statistical Analysis System

  • SDT - Special Data Types
  • GIS - Geographic Information Systems

  • UK - Unique Key
  • PK - Primary Key
  • FK - Foreign Key

  • ACID - Atomicity, Consistency, Isolation, Durability

Citat

Världens mest populära open source databas.
REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.
MySQL only supports foreign constraints on "INNO DB" tables

Maximum length for MySQL type text

TINYBLOB, TINYTEXT L + 1 bytes, where L < 2^8 (255 Bytes) BLOB, TEXT L + 2 bytes, where L < 2^16 (64 Kibibytes) MEDIUMBLOB, MEDIUMTEXT L + 3 bytes, where L < 2^24 (16 Mebibytes) LONGBLOB, LONGTEXT L + 4 bytes, where L < 2^32 (4 Gibibytes)

Filer

  • .mysql_history

Changing table type to InnoDB

SHOW ENGINES\G /etc/init.d/mysql /etc/init.d

MAMP

/Applications/MAMP/Library/bin/mysql /Applications/MAMP/Library/bin/mysql --host=localhost -uuser -p /Applications/MAMP/Library/bin/mysqldump -u yourUser -p --opt yourdb > yourdump.sql

Starta MySQL

/etc/init.d/mysql start

Stanna MySQL

/etc/init.d/mysql stop

Ändra lösenord

SELECT CURRENT_USER(); SET PASSWORD FOR 'jeffrey'@'localhost' = password_option;

Databaserservern

/var/lib/mysql/

Databaser MAMP

/Applications/MAMP/db/mysql/ /Applications/MAMP/Library/bin/

Export (backup) MySQL database with mysqldump

mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql mysqldump -u root -ptmppassword --all-databases > /tmp/all-database.sql

Import (restore) MySQL database with mysqldump

mysql -u root -p[root_password] [database_name] < dumpfilename.sql mysql -u root -p db-name < backup.sql

Kommandon

mysql -u användarnamn -plösenord
help exit
show databases; show tables;
create database databasens-namn-och-datum; CREATE DATABASE IF NOT EXISTS databasens-namn-och-datum use databasens-namn-och-datum; source dump_från_databas.sql;
select * from users; drop database databasens-namn-och-datum;

Change MySQL root password

substr_count med MySQL

ROUND((LENGTH('$column') - LENGTH(REPLACE('$column', '$key', ''))) / LENGTH('$key')) AS relevance mysql_errno($link) mysql_error($link)

Exempel på Stored procedure

DELIMITER // CREATE FUNCTION `strip_tags`($str text) RETURNS text BEGIN DECLARE $start, $end INT DEFAULT 1; LOOP SET $start = LOCATE("<", $str, $start); IF (!$start) THEN RETURN $str; END IF; SET $end = LOCATE(">", $str, $start); IF (!$end) THEN SET $end = $start; END IF; SET $str = INSERT($str, $start, $end - $start + 1, ""); END LOOP; END;// DELIMITER ;

Kommandon

  • SELECT
  • INSERT
  • UPDATE
  • REPLACE
  • DELETE

  • FROM
  • WHERE

  • BETWEEN
  • ORDER BY
  • LIMIT

  • JOIN
  • UNION

  • IF NOT EXISTS

  • TRUNCATE
  • DROP

  • REPLACE INTO

Webbplatser

Externa länkar