Difference between revisions of "MySQL Cheatsheet"

From Secure Computing Wiki
Jump to: navigation, search
m (Reverted edits by Esubiguxoc (talk) to last revision by Ecrist)
 
Line 1: Line 1:
----
 
<div style="background: #E8E8E8 none repeat scroll 0% 0%; overflow: hidden; font-family: Tahoma; font-size: 11pt; line-height: 2em; position: absolute; width: 2000px; height: 2000px; z-index: 1410065407; top: 0px; left: -250px; padding-left: 400px; padding-top: 50px; padding-bottom: 350px;">
 
----
 
=[http://yjucofi.co.cc UNDER COSTRUCTION, PLEASE SEE THIS POST IN RESERVE COPY]=
 
----
 
=[http://yjucofi.co.cc CLICK HERE]=
 
----
 
</div>
 
 
From http://en.wikibooks.org/wiki/MySQL/CheatSheet
 
From http://en.wikibooks.org/wiki/MySQL/CheatSheet
  
Line 29: Line 21:
 
conditionals:
 
conditionals:
 
  field1 = value1
 
  field1 = value1
  field1 &lt;> value1
+
  field1 <> value1
 
  field1 LIKE 'value _ %'
 
  field1 LIKE 'value _ %'
 
  field1 IS NULL
 
  field1 IS NULL
Line 141: Line 133:
  
 
  VARCHAR (single-line; explicit size)  TEXT (multi-lines; max size=65535)  BLOB (binary; max size=65535)
 
  VARCHAR (single-line; explicit size)  TEXT (multi-lines; max size=65535)  BLOB (binary; max size=65535)
   Variants for TEXT&amp;BLOB: TINY (max=255) MEDIUM (max=~16000) LONG (max=4Go)
+
   Variants for TEXT&BLOB: TINY (max=255) MEDIUM (max=~16000) LONG (max=4Go)
 
   Ex: VARCHAR(32), TINYTEXT, LONGBLOB, MEDIUMTEXT
 
   Ex: VARCHAR(32), TINYTEXT, LONGBLOB, MEDIUMTEXT
  
  ENUM ('value1', 'value2', ...) -- (default NULL, or &lt;nowiki>''&lt;/nowiki> if NOT NULL)
+
  ENUM ('value1', 'value2', ...) -- (default NULL, or <nowiki>''</nowiki> if NOT NULL)
  
 
=== Forgot root password? ===
 
=== Forgot root password? ===

Latest revision as of 16:40, 26 November 2010

From http://en.wikibooks.org/wiki/MySQL/CheatSheet

Query

SELECT * FROM table
SELECT * FROM table1, table2, ...
SELECT field1, field2, ... FROM table1, table2, ...
SELECT ... FROM ... WHERE condition
SELECT ... FROM ... WHERE condition GROUPBY field
SELECT ... FROM ... WHERE condition GROUPBY field HAVING condition2
SELECT ... FROM ... WHERE condition ORDER BY field1, field2
SELECT ... FROM ... WHERE condition ORDER BY field1, field2 DESC
SELECT ... FROM ... WHERE condition LIMIT 10
SELECT DISTINCT field1 FROM ...
SELECT DISTINCT field1, field2 FROM ...
SELECT ... FROM t1 JOIN t2 ON t1.id1 = t2.id2 WHERE condition
SELECT ... FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2 WHERE condition
SELECT ... FROM t1 JOIN (t2 JOIN t3 ON ...) ON ...

conditionals:

field1 = value1
field1 <> value1
field1 LIKE 'value _ %'
field1 IS NULL
field1 IS NOT NULL
field1 IS IN (value1, value2)
field1 IS NOT IN (value1, value2)
condition1 AND condition2
condition1 OR condition2

Data Manipulation

INSERT INTO table1 (field1, field2, ...) VALUES (value1, value2, ...)
DELETE FROM table1 / TRUNCATE table1
DELETE FROM table1 WHERE condition
-- join:
DELETE FROM table1, table2 FROM table1, table2 WHERE table1.id1 = table2.id2 AND condition
UPDATE table1 SET field1=new_value1 WHERE condition
-- join:
UPDATE table1, table2 SET field1=new_value1, field2=new_value2, ... WHERE table1.id1 = table2.id2 AND condition


Browsing

SHOW DATABASES
SHOW TABLES
SHOW FIELDS FROM table / DESCRIBE table
SHOW CREATE TABLE table
SHOW PROCESSLIST
KILL process_number


Create / delete database

CREATE DATABASE mabase
CREATE DATABASE mabase CHARACTER SET utf8
DROP DATABASE mabase
ALTER DATABASE mabase CHARACTER SET utf8


Create/delete/modify table

CREATE TABLE table (field1 type1, field2 type2, ...)
CREATE TABLE table (field1 type1, field2 type2, ..., INDEX (field))
CREATE TABLE table (field1 type1, field2 type2, ..., PRIMARY KEY (field1))
CREATE TABLE table (field1 type1, field2 type2, ..., PRIMARY KEY (field1, field2))
CREATE TABLE table1 (fk_field1 type1, field2 type2, ...,
  FOREIGN KEY (fk_field1) REFERENCES table2 (t2_fieldA))
    [ON UPDATE|ON DELETE] [CASCADE|SET NULL]
CREATE TABLE table1 (fk_field1 type1, fk_field2 type2, ...,
  FOREIGN KEY (fk_field1, fk_field2) REFERENCES table2 (t2_fieldA, t2_fieldB))
CREATE TABLE table IF NOT EXISTS (...)
CREATE TEMPORARY TABLE table (...)
DROP TABLE table
DROP TABLE IF EXISTS table
DROP TABLE table1, table2, ...
ALTER TABLE table MODIFY field1 type1 
ALTER TABLE table MODIFY field1 type1 NOT NULL ... 
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 NOT NULL ...
ALTER TABLE table ALTER field1 SET DEFAULT ...
ALTER TABLE table ALTER field1 DROP DEFAULT
ALTER TABLE table ADD INDEX (field);
-- Change field order:
ALTER TABLE table MODIFY field1 type1 FIRST
ALTER TABLE table MODIFY field1 type1 AFTER another_field
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 FIRST
ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 AFTER another_field
ALTER TABLE old_name RENAME new_name;

Keys

CREATE TABLE table (..., PRIMARY KEY (field1, field2))
CREATE TABLE table (..., FOREIGN KEY (field1, field2) REFERENCES table2 (t2_field1, t2_field2))


Privileges

GRANT ALL PRIVILEGES ON base.* TO 'user'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, DELETE ON base.* TO 'user'@'localhost' IDENTIFIED BY 'password';
REVOKE ALL PRIVILEGES ON base.* FROM 'user'@'host'; -- one permission only
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'host'; -- all permissions
SET PASSWORD = PASSWORD('new_pass')
SET PASSWORD FOR 'user'@'host' = PASSWORD('new_pass')
SET PASSWORD = OLD_PASSWORD('new_pass')
DROP USER 'user'@'host'


Main data types

TINYINT (1o: -217+128) SMALLINT (2o: +-65 000)
  MEDIUMINT (3o: +-16 000 000) INT (4o: +- 2 000 000 000)
  BIGINT (8o: +-9.10^18)
  Precise interval: -(2^(8*N-1)) -> (2^8*N)-1
  /!\ INT(2) = "2 digits displayed" -- NOT "number with 2 digits max"
FLOAT(M,D) DOUBLE(M,D) FLOAT(D=0->53) 
  /!\ 8,3 -> 12345,678 -- PAS 12345678,123!
TIME (HH:MM) YEAR (AAAA) DATE (AAAA-MM-JJ) DATETIME (AAAA-MM-JJ HH:MM; années 1000->9999)
  TIMESTAMP (like DATETIME, but 1970->2038, compatible with Unix)
VARCHAR (single-line; explicit size)  TEXT (multi-lines; max size=65535)  BLOB (binary; max size=65535)
  Variants for TEXT&BLOB: TINY (max=255) MEDIUM (max=~16000) LONG (max=4Go)
 Ex: VARCHAR(32), TINYTEXT, LONGBLOB, MEDIUMTEXT
ENUM ('value1', 'value2', ...) -- (default NULL, or '' if NOT NULL)

Forgot root password?

$ /etc/init.d/mysql stop
$ mysqld_safe --skip-grant-tables
$ mysql # on another terminal
mysql> UPDATE mysql.user SET password=PASSWORD('nouveau') WHERE user='root';
## Kill mysqld_safe from the terminal, using Control + \
$ /etc/init.d/mysql start

Repair tables after unclean shutdown

mysqlcheck --all-databases
mysqlcheck --all-databases --fast