Spickzettel

Codefragmente

MySQL Shell

:/usr/local/mysql/bin root# ./mysql -u root -p

mysql>_

Standart-Queries

select
SELECT eid, name FROM event WHERE eid = $entry

insert
INSERT INTO event(name,eid) VALUES(‘Pizzaparty’,12332323442)

update
UPDATE event SET name=’Pizzasuperparty’,location=’Am Dorfteich’ WHERE id=12332323442

delete
DELETE FROM event WHERE id=12332323442

Quelle: Neal Parikh

Selecting a database:
mysql> USE database;

Listing databases:
mysql> SHOW DATABASES;

Listing tables in a db:
mysql> SHOW TABLES;

Describing the format of a table:
mysql> DESCRIBE table;

Creating a database:
mysql> CREATE DATABASE db_name;

Deleting a database:
mysql> DROP DATABASE db_name;

Creating a table:
mysql> CREATE TABLE table_name (field1_name TYPE(SIZE), field2_name TYPE(SIZE));
Ex: mysql> CREATE TABLE pet (name VARCHAR(20), sex CHAR(1), birth DATE);

Load tab-delimited data into a table:
mysql> LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table_name;
(Use \n for NULL)

Inserting one row at a time:
mysql> INSERT INTO table_name VALUES ('MyName', 'MyOwner', '2002-08-31');
(Use NULL for NULL)

Retrieving information (general):
mysql> SELECT from_columns FROM table WHERE conditions;
All values: SELECT * FROM table;
Some values: SELECT * FROM table WHERE rec_name = "value";
Multiple critera: SELECT * FROM TABLE WHERE rec1 = "value1" AND rec2 = "value2";

Reloading a new data set into existing table:
mysql> SET AUTOCOMMIT=1; # used for quick recreation of table
mysql> DELETE FROM pet;
mysql> LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table;

Fixing all records with a certain value:
mysql> UPDATE table SET column_name = "new_value" WHERE record_name = "value";

Selecting specific columns:
mysql> SELECT column_name FROM table;

Retrieving unique output records:
mysql> SELECT DISTINCT column_name FROM table;

Sorting:
mysql> SELECT col1, col2 FROM table ORDER BY col2;
Backwards: SELECT col1, col2 FROM table ORDER BY col2 DESC;

Date calculations:
mysql> SELECT CURRENT_DATE, (YEAR(CURRENT_DATE)-YEAR(date_col)) AS time_diff [FROM table];
MONTH(some_date) extracts the month value and DAYOFMONTH() extracts day.

Pattern Matching:
mysql> SELECT * FROM table WHERE rec LIKE "blah%";
(% is wildcard - arbitrary # of chars)
Find 5-char values: SELECT * FROM table WHERE rec like "_____";
(_ is any single character)

Extended Regular Expression Matching:
mysql> SELECT * FROM table WHERE rec RLIKE "^b$";
(. for char, [...] for char class, * for 0 or more instances
^ for beginning, {n} for repeat n times, and $ for end)
(RLIKE or REGEXP)
To force case-sensitivity, use "REGEXP BINARY"

Counting Rows:
mysql> SELECT COUNT(*) FROM table;

Grouping with Counting:
mysql> SELECT owner, COUNT(*) FROM table GROUP BY owner;
(GROUP BY groups together all records for each 'owner')

Selecting from multiple tables:
(Example)
mysql> SELECT pet.name, comment FROM pet, event WHERE pet.name = event.name;
(You can join a table to itself to compare by using 'AS')

Currently selected database:
mysql> SELECT DATABASE();

Maximum value:
mysql> SELECT MAX(col_name) AS label FROM table;

Auto-incrementing rows:
mysql> CREATE TABLE table (number INT NOT NULL AUTO_INCREMENT, name CHAR(10) NOT NULL);
mysql> INSERT INTO table (name) VALUES ("tom"),("dick"),("harry");

mysql> insert into city(name, country, latitude, longitude, radius) values("Dresden","Germany", "51.0416908", "13.7314196", 9059); 

Adding a column to an already-created table:

mysql> ALTER TABLE tbl ADD COLUMN [column_create syntax] AFTER col_name;

mysql> ALTER TABLE location add column eventcounter int(11) NULL after name;

Removing a column: mysql> ALTER TABLE tbl DROP COLUMN col; (Full ALTER TABLE syntax available at mysql.com.) Batch mode (feeding in a script): # mysql -u user -p < batch_file (Use -t for nice table layout and -vvv for command echoing.) Alternatively: mysql> source batch_file; Backing up a database with mysqldump: # mysqldump --opt -u username -p database > database_backup.sql (Use 'mysqldump --opt --all-databases > all_backup.sql' to backup everything.) (More info at MySQL's docs.)

Backup+Restore mit Mysqldump:
backup: # mysqldump -u root -p[root_password] [database_name] [table_name] > dumpfilename.sql

restore:# mysql -u root -p[root_password] [database_name] < dumpfilename.sql
->tablename is not nessessary even if only a table should be restored/added

doppelte Datensätze finden: SELECT COUNT(*) AS COUNT, spalte1, id FROM tabellenname GROUP BY spalte1 HAVING COUNT(*)>1;

Spalte nachträglich unique machen: es dürfen keine Dubletten existieren!
mysql> ALTER TABLE Employee ADD UNIQUE (ID, EmpID);

Neuen User erstellen
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';

Character set einstellen:

show variables like ‘char%’;
set character_set_server=utf8;
set character_set_results=utf8;
set character_set_client=utf8;
set character_set_connection=utf8;

Datum: unixtimestap umwandeln:

SELECT *, FROM_UNIXTIME(time) as realtime
FROM `jos_session`
ORDER BY `time` DESC
LIMIT 30
Ausführliche Beschreibung der Möglichkeiten.