13 SHOW FIELDS FROM table / DESCRIBE table;
14 SHOW CREATE TABLE table;
23 SELECT * FROM table1, table2;
24 SELECT field1, field2 FROM table1, table2;
25 SELECT ... FROM ... WHERE condition
26 SELECT ... FROM ... WHERE condition GROUP BY field;
27 SELECT ... FROM ... WHERE condition GROUP BY field HAVING condition2;
28 SELECT ... FROM ... WHERE condition ORDER BY field1, field2;
29 SELECT ... FROM ... WHERE condition ORDER BY field1, field2 DESC;
30 SELECT ... FROM ... WHERE condition LIMIT 10;
31 SELECT DISTINCT field1 FROM ...
32 SELECT DISTINCT field1, field2 FROM ...
38 SELECT ... FROM t1 JOIN t2 ON t1.id1 = t2.id2 WHERE condition;
39 SELECT ... FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2 WHERE condition;
40 SELECT ... FROM t1 JOIN (t2 JOIN t3 ON ...) ON ...
48 field1 LIKE 'value _ %'
51 field1 IS IN (value1, value2)
52 field1 IS NOT IN (value1, value2)
53 condition1 AND condition2
54 condition1 OR condition2
57 ### Create / Open / Delete Database
60 CREATE DATABASE DatabaseName;
61 CREATE DATABASE DatabaseName CHARACTER SET utf8;
63 DROP DATABASE DatabaseName;
64 ALTER DATABASE DatabaseName CHARACTER SET utf8;
67 ### Backup Database to SQL File
70 mysqldump -u Username -p dbNameYouWant > databasename_backup.sql
73 ### Restore from backup SQL File
76 mysql -u Username -p dbNameYouWant < databasename_backup.sql;
79 ### Repair Tables After Unclean Shutdown
82 mysqlcheck --all-databases;
83 mysqlcheck --all-databases --fast;
89 INSERT INTO table1 (field1, field2) VALUES (value1, value2);
95 DELETE FROM table1 / TRUNCATE table1
96 DELETE FROM table1 WHERE condition
97 DELETE FROM table1, table2 WHERE table1.id1 =
98 table2.id2 AND condition
104 UPDATE table1 SET field1=new_value1 WHERE condition;
105 UPDATE table1, table2 SET field1=new_value1, field2=new_value2, ... WHERE
106 table1.id1 = table2.id2 AND condition;
109 ### Create / Delete / Modify Table
114 CREATE TABLE table (field1 type1, field2 type2);
115 CREATE TABLE table (field1 type1, field2 type2, INDEX (field));
116 CREATE TABLE table (field1 type1, field2 type2, PRIMARY KEY (field1));
117 CREATE TABLE table (field1 type1, field2 type2, PRIMARY KEY (field1,field2));
121 CREATE TABLE table1 (fk_field1 type1, field2 type2, ...,
122 FOREIGN KEY (fk_field1) REFERENCES table2 (t2_fieldA))
123 [ON UPDATE|ON DELETE] [CASCADE|SET NULL]
127 CREATE TABLE table1 (fk_field1 type1, fk_field2 type2, ...,
128 FOREIGN KEY (fk_field1, fk_field2) REFERENCES table2 (t2_fieldA, t2_fieldB))
132 CREATE TABLE table IF NOT EXISTS;
136 CREATE TEMPORARY TABLE table;
143 DROP TABLE IF EXISTS table;
144 DROP TABLE table1, table2, ...
150 ALTER TABLE table MODIFY field1 type1
151 ALTER TABLE table MODIFY field1 type1 NOT NULL ...
152 ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1
153 ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 NOT NULL ...
154 ALTER TABLE table ALTER field1 SET DEFAULT ...
155 ALTER TABLE table ALTER field1 DROP DEFAULT
156 ALTER TABLE table ADD new_name_field1 type1
157 ALTER TABLE table ADD new_name_field1 type1 FIRST
158 ALTER TABLE table ADD new_name_field1 type1 AFTER another_field
159 ALTER TABLE table DROP field1
160 ALTER TABLE table ADD INDEX (field);
163 #### Change field order
166 ALTER TABLE table MODIFY field1 type1 FIRST
167 ALTER TABLE table MODIFY field1 type1 AFTER another_field
168 ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 FIRST
169 ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 AFTER
176 CREATE TABLE table (..., PRIMARY KEY (field1, field2))
177 CREATE TABLE table (..., FOREIGN KEY (field1, field2) REFERENCES table2
178 (t2_field1, t2_field2))
181 ### Users and Privileges
184 CREATE USER 'user'@'localhost';
185 GRANT ALL PRIVILEGES ON base.* TO 'user'@'localhost' IDENTIFIED BY 'password';
186 GRANT SELECT, INSERT, DELETE ON base.* TO 'user'@'localhost' IDENTIFIED BY 'password';
187 REVOKE ALL PRIVILEGES ON base.* FROM 'user'@'host'; -- one permission only
188 REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'host'; -- all permissions
193 SET PASSWORD = PASSWORD('new_pass');
194 SET PASSWORD FOR 'user'@'host' = PASSWORD('new_pass');
195 SET PASSWORD = OLD_PASSWORD('new_pass');
199 DROP USER 'user'@'host';
202 Host ‘%’ indicates any host.
207 TINYINT (1o: -128 to +127)
208 SMALLINT (2o: +-65 000)
209 MEDIUMINT (3o: +-16 000 000)
210 INT (4o: +- 2 000 000 000)
211 BIGINT (8o: +-9.10^18)
215 Precise interval: -(2^(8*N-1)) -> (2^8*N)-1
218 ⚠ INT(2) = "2 digits displayed" -- NOT "number with 2 digits max"
226 ⚠ 8,3 -> 12345,678 -- NOT 12345678,123!
232 DATETIME (AAAA-MM-JJ HH:MM; années 1000->9999)
233 TIMESTAMP (like DATETIME, but 1970->2038, compatible with Unix)
237 VARCHAR (single-line; explicit size)
238 TEXT (multi-lines; max size=65535)
239 BLOB (binary; max size=65535)
242 Variants for TEXT&BLOB: `TINY` (max=255), `MEDIUM` (max=~16000), and `LONG` (max=4Go). Ex: `VARCHAR(32)`, `TINYTEXT`, `LONGBLOB`, `MEDIUMTEXT`
245 ENUM ('value1', 'value2', ...) -- (default NULL, or '' if NOT NULL)
248 ### Reset Root Password
251 $ /etc/init.d/mysql stop
255 $ mysqld_safe --skip-grant-tables
259 $ mysql # on another terminal
260 mysql> UPDATE mysql.user SET password=PASSWORD('new_pass') WHERE user='root';
264 ## Switch back to the mysqld_safe terminal and kill the process using Control + \
265 $ /etc/init.d/mysql start
268 Your commands may vary depending on your OS.