![\"http:\/\/kern.comelio.com\/downloads\/comelio_kurzref_sql_mittel.jpg\"](\"https:\/\/www.mobiehl.de\/images\/comelio_kurzref_sql_mittel.jpg\" "\\"http:\/\/kern.comelio.com\/downloads\/comelio_kurzref_sql_mittel.jpg\\"")
<\/a><\/p>\nCodefragmente<\/h2>\n
MySQL Shell<\/b><\/p>\n
:\/usr\/local\/mysql\/bin root# .\/mysql -u root -p<\/p>\n
mysql>_<\/p>\n
Standart-Queries<\/strong><\/p>\n select insert update delete <\/p>\n doppelte Datens\u00e4tze finden: SELECT COUNT(*) AS COUNT, spalte1, id FROM tabellenname GROUP BY spalte1 HAVING COUNT(*)>1;<\/p>\n Spalte nachtr\u00e4glich unique machen: es d\u00fcrfen keine Dubletten existieren! Character set einstellen:<\/strong><\/p>\n show variables like ‘char%’;
\nSELECT eid, name FROM event WHERE eid = $entry<\/p>\n
\nINSERT INTO event(name,eid) VALUES(‘Pizzaparty’,12332323442)<\/p>\n
\nUPDATE event SET name=’Pizzasuperparty’,location=’Am Dorfteich’ WHERE id=12332323442<\/p>\n
\nDELETE FROM event WHERE id=12332323442<\/p>\nQuelle: Neal Parikh<\/a><\/pre>\n
Selecting a database:<\/b>\r\nmysql> USE database;\r\n\r\nListing databases:<\/b>\r\nmysql> SHOW DATABASES;\r\n\r\nListing tables in a db:<\/b>\r\nmysql> SHOW TABLES;\r\n\r\nDescribing the format of a table:<\/b>\r\nmysql> DESCRIBE table;\r\n\r\nCreating a database:<\/b>\r\nmysql> CREATE DATABASE db_name;<\/pre>\n
Deleting a database:<\/b>\r\nmysql> DROP DATABASE db_name;\r\n\r\nCreating a table:<\/b>\r\nmysql> CREATE TABLE table_name (field1_name TYPE(SIZE), field2_name TYPE(SIZE));\r\nEx: mysql> CREATE TABLE pet (name VARCHAR(20), sex CHAR(1), birth DATE);\r\n\r\nLoad tab-delimited data into a table:<\/b>\r\nmysql> LOAD DATA LOCAL INFILE \"infile.txt\" INTO TABLE table_name;\r\n(Use \\n for NULL)\r\n\r\nInserting one row at a time:<\/b>\r\nmysql> INSERT INTO table_name VALUES ('MyName', 'MyOwner', '2002-08-31');\r\n(Use NULL for NULL)\r\n\r\nRetrieving information (general):<\/b>\r\nmysql> SELECT from_columns FROM table WHERE conditions;\r\nAll values: SELECT * FROM table;\r\nSome values: SELECT * FROM table WHERE rec_name = \"value\";\r\nMultiple critera: SELECT * FROM TABLE WHERE rec1 = \"value1\" AND rec2 = \"value2\";\r\n\r\nReloading a new data set into existing table:<\/b>\r\nmysql> SET AUTOCOMMIT=1; # used for quick recreation of table\r\nmysql> DELETE FROM pet;\r\nmysql> LOAD DATA LOCAL INFILE \"infile.txt\" INTO TABLE table;\r\n\r\nFixing all records with a certain value:<\/b>\r\nmysql> UPDATE table SET column_name = \"new_value\" WHERE record_name = \"value\";\r\n\r\nSelecting specific columns:<\/b>\r\nmysql> SELECT column_name FROM table;\r\n\r\nRetrieving unique output records:<\/b>\r\nmysql> SELECT DISTINCT column_name FROM table;\r\n\r\nSorting:<\/b>\r\nmysql> SELECT col1, col2 FROM table ORDER BY col2;\r\nBackwards: SELECT col1, col2 FROM table ORDER BY col2 DESC;\r\n\r\nDate calculations:<\/b>\r\nmysql> SELECT CURRENT_DATE, (YEAR(CURRENT_DATE)-YEAR(date_col)) AS time_diff [FROM table];\r\nMONTH(some_date) extracts the month value and DAYOFMONTH() extracts day.\r\n\r\nPattern Matching:<\/b>\r\nmysql> SELECT * FROM table WHERE rec LIKE \"blah%\";\r\n(% is wildcard - arbitrary # of chars)\r\nFind 5-char values: SELECT * FROM table WHERE rec like \"_____\";\r\n(_ is any single character)\r\n\r\nExtended Regular Expression Matching:<\/b>\r\nmysql> SELECT * FROM table WHERE rec RLIKE \"^b$\";\r\n(. for char, [...] for char class, * for 0 or more instances\r\n^ for beginning, {n} for repeat n times, and $ for end)\r\n(RLIKE or REGEXP)\r\nTo force case-sensitivity, use \"REGEXP BINARY\"\r\n\r\nCounting Rows:<\/b>\r\nmysql> SELECT COUNT(*) FROM table;\r\n\r\nGrouping with Counting:<\/b>\r\nmysql> SELECT owner, COUNT(*) FROM table GROUP BY owner;\r\n(GROUP BY groups together all records for each 'owner')\r\n\r\nSelecting from multiple tables:<\/b>\r\n(Example)\r\nmysql> SELECT pet.name, comment FROM pet, event WHERE pet.name = event.name;\r\n(You can join a table to itself to compare by using 'AS')\r\n\r\nCurrently selected database:<\/b>\r\nmysql> SELECT DATABASE();\r\n\r\nMaximum value:<\/b>\r\nmysql> SELECT MAX(col_name) AS label FROM table;\r\n\r\nAuto-incrementing rows:<\/b>\r\nmysql> CREATE TABLE table (number INT NOT NULL AUTO_INCREMENT, name CHAR(10) NOT NULL);\r\nmysql> INSERT INTO table (name) VALUES (\"tom\"),(\"dick\"),(\"harry\");<\/pre>\nmysql> insert into city(name, country, latitude, longitude, radius) values(\"Dresden\",\"Germany\", \"51.0416908\", \"13.7314196\", 9059); \r\n\r\nAdding a column to an already-created table:<\/b><\/pre>\n
mysql> ALTER TABLE tbl ADD COLUMN [column_create syntax] AFTER col_name;<\/pre>\n
mysql> ALTER TABLE location add column eventcounter int(11) NULL after name;<\/pre>\n
Removing a column:<\/b> mysql> ALTER TABLE tbl DROP COLUMN col; (Full ALTER TABLE<\/a> syntax available at mysql.com.) Batch mode (feeding in a script):<\/b> # 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:<\/b> # 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<\/a>.)<\/pre>\n
Backup+Restore mit Mysqldump:\r\nbackup:<\/strong> # mysqldump -u root -p[root_password] [database_name] [table_name] > dumpfilename.sql\r\n\r\nrestore:<\/strong># mysql -u root -p[root_password] [database_name] < dumpfilename.sql\r\n->tablename is not nessessary even if only a table should be restored\/added<\/pre>\n
\nmysql> ALTER TABLE Employee ADD UNIQUE (ID, EmpID);<\/p>\nNeuen User erstellen<\/strong>\r\nCREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';\r\nGRANT ALL ON db1.* TO 'jeffrey'@'localhost';<\/pre>\n
\nset character_set_server=utf8;
\nset character_set_results=utf8;
\nset character_set_client=utf8;
\nset character_set_connection=utf8;<\/p>\n