Posted By

beneberle on 02/15/11


Tagged

mysql unix


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

jeremydouglass


mySQL UNIX cheat sheet


 / Published in: MySQL
 

URL: http://www.nparikh.org/unix/mysql.php

  1. Selecting a database:
  2.  
  3. mysql> USE database;
  4.  
  5. Listing DATABASES:
  6.  
  7. mysql> SHOW DATABASES;
  8.  
  9. Listing tables IN a db:
  10.  
  11. mysql> SHOW TABLES;
  12.  
  13. Describing the FORMAT of a table:
  14.  
  15. mysql> DESCRIBE table;
  16.  
  17. Creating a database:
  18.  
  19. mysql> CREATE DATABASE db_name;
  20.  
  21. Creating a table:
  22.  
  23. mysql> CREATE TABLE table_name (field1_name TYPE(SIZE), field2_name TYPE(SIZE));
  24. Ex: mysql> CREATE TABLE pet (name VARCHAR(20), sex CHAR(1), birth DATE);
  25.  
  26. Load tab-delimited data INTO a table:
  27.  
  28. mysql> LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table_name;
  29. (USE \n for NULL)
  30.  
  31. Inserting one row at a TIME:
  32.  
  33. mysql> INSERT INTO table_name VALUES ('MyName', 'MyOwner', '2002-08-31');
  34. (USE NULL for NULL)
  35.  
  36. Retrieving information (general):
  37.  
  38. mysql> SELECT from_columns FROM table WHERE conditions;
  39. All VALUES: SELECT * FROM table;
  40. SOME VALUES: SELECT * FROM table WHERE rec_name = "value";
  41. Multiple critera: SELECT * FROM TABLE WHERE rec1 = "value1" AND rec2 = "value2";
  42.  
  43. Reloading a new data SET INTO existing table:
  44.  
  45. mysql> SET AUTOCOMMIT=1; # used for quick recreation of table
  46. mysql> DELETE FROM pet;
  47. mysql> LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table;
  48.  
  49. Fixing all records with a certain value:
  50.  
  51. mysql> UPDATE table SET column_name = "new_value" WHERE record_name = "value";
  52.  
  53. Selecting specific COLUMNS:
  54.  
  55. mysql> SELECT column_name FROM table;
  56.  
  57. Retrieving UNIQUE output records:
  58.  
  59. mysql> SELECT DISTINCT column_name FROM table;
  60.  
  61. Sorting:
  62.  
  63. mysql> SELECT col1, col2 FROM table ORDER BY col2;
  64. Backwards: SELECT col1, col2 FROM table ORDER BY col2 DESC;
  65.  
  66. DATE calculations:
  67.  
  68. mysql> SELECT CURRENT_DATE, (YEAR(CURRENT_DATE)-YEAR(date_col)) AS time_diff [FROM table];
  69. MONTH(some_date) extracts the MONTH value AND DAYOFMONTH() extracts DAY.
  70.  
  71. Pattern Matching:
  72.  
  73. mysql> SELECT * FROM table WHERE rec LIKE "blah%";
  74. (% IS wildcard - arbitrary # of chars)
  75. Find 5-CHAR VALUES: SELECT * FROM table WHERE rec LIKE "_____";
  76. (_ IS any single character)
  77.  
  78. Extended Regular Expression Matching:
  79.  
  80. mysql> SELECT * FROM table WHERE rec RLIKE "^b$";
  81. (. for CHAR, [...] for CHAR class, * for 0 OR more instances
  82. ^ for beginning, {n} for REPEAT n times, AND $ for END)
  83. (RLIKE OR REGEXP)
  84. To FORCE case-sensitivity, USE "REGEXP BINARY"
  85.  
  86. Counting Rows:
  87.  
  88. mysql> SELECT COUNT(*) FROM table;
  89.  
  90. Grouping with Counting:
  91.  
  92. mysql> SELECT owner, COUNT(*) FROM table GROUP BY owner;
  93. (GROUP BY groups together all records for each 'owner')
  94.  
  95. Selecting FROM multiple tables:
  96.  
  97. (Example)
  98. mysql> SELECT pet.name, comment FROM pet, event WHERE pet.name = event.name;
  99. (You can JOIN a table to itself to compare BY USING 'AS')
  100.  
  101. Currently selected database:
  102.  
  103. mysql> SELECT DATABASE();
  104.  
  105. Maximum value:
  106.  
  107. mysql> SELECT MAX(col_name) AS label FROM table;
  108.  
  109. Auto-incrementing rows:
  110.  
  111. mysql> CREATE TABLE table (number INT NOT NULL AUTO_INCREMENT, name CHAR(10) NOT NULL);
  112. mysql> INSERT INTO table (name) VALUES ("tom"),("dick"),("harry");
  113.  
  114. Adding a COLUMN to an already-created table:
  115.  
  116. mysql> ALTER TABLE tbl ADD COLUMN [column_create syntax] AFTER col_name;
  117.  
  118. Removing a COLUMN:
  119.  
  120. mysql> ALTER TABLE tbl DROP COLUMN col;
  121. (Full ALTER TABLE syntax available at mysql.com.)
  122.  
  123. Batch mode (feeding IN a script):
  124.  
  125. # mysql -u user -p < batch_file
  126. (USE -t for nice table layout AND -vvv for command echoing.)
  127. Alternatively: mysql> source batch_file;
  128.  
  129. Backing up a database with mysqldump:
  130.  
  131. # mysqldump --opt -u username -p database > database_backup.sql
  132. (USE 'mysqldump --opt --all-databases > all_backup.sql' to backup everything.)
  133. (More info at MySQL's docs.)

Report this snippet  

You need to login to post a comment.