NsEZCZGB

1Create / Delete Database
2CREATE DATABASE dbNameYouWant
3CREATE DATABASE dbNameYouWant CHARACTER SET utf8
4DROP DATABASE dbNameYouWant
5ALTER DATABASE dbNameYouWant CHARACTER SET utf8
6Backup Database to SQL File
7mysqldump -u Username -p dbNameYouWant > databasename_backup.sql
8Restore from backup SQL File
9mysql - u Username -p dbNameYouWant < databasename_backup.sql
10Repair Tables After Unclean Shutdown
11mysqlcheck --all-databases
12mysqlcheck --all-databases --fast ### Browsing
13SHOW DATABASES
14SHOW TABLES
15SHOW FIELDS FROM table / DESCRIBE table
16SHOW CREATE TABLE table
17SHOW PROCESSLIST
18KILL process_number
19Select
20SELECT * FROM table
21SELECT * FROM table1, table2, ...
22SELECT field1, field2, ... FROM table1, table2, ...
23SELECT ... FROM ... WHERE condition
24SELECT ... FROM ... WHERE condition GROUPBY field
25SELECT ... FROM ... WHERE condition GROUPBY field HAVING condition2
26SELECT ... FROM ... WHERE condition ORDER BY field1, field2
27SELECT ... FROM ... WHERE condition ORDER BY field1, field2 DESC
28SELECT ... FROM ... WHERE condition LIMIT 10
29SELECT DISTINCT field1 FROM ...
30SELECT DISTINCT field1, field2 FROM ...
31Select - Join
32SELECT ... FROM t1 JOIN t2 ON t1.id1 = t2.id2 WHERE condition
33SELECT ... FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2 WHERE condition
34SELECT ... FROM t1 JOIN (t2 JOIN t3 ON ...) ON ...
35Conditions
36field1 = value1
37field1 <> value1
38field1 LIKE 'value _ %'
39field1 IS NULL
40field1 IS NOT NULL
41field1 IS IN (value1, value2)
42field1 IS NOT IN (value1, value2)
43condition1 AND condition2
44condition1 OR condition2
45Insert
46INSERT INTO table1 (field1, field2, ...) VALUES (value1, value2, ...)
47Delete
48DELETE FROM table1 / TRUNCATE table1
49DELETE FROM table1 WHERE condition
50DELETE FROM table1, table2 FROM table1, table2 WHERE table1.id1 =
51table2.id2 AND condition
52Update
53UPDATE table1 SET field1=new_value1 WHERE condition
54UPDATE table1, table2 SET field1=new_value1, field2=new_value2, ... WHERE
55table1.id1 = table2.id2 AND condition
56Create / Delete / Modify Table
57*Create*
58CREATE TABLE table (field1 type1, field2 type2, ...)
59CREATE TABLE table (field1 type1, field2 type2, ..., INDEX (field))
60CREATE TABLE table (field1 type1, field2 type2, ..., PRIMARY KEY (field1))
61CREATE TABLE table (field1 type1, field2 type2, ..., PRIMARY KEY (field1,
62field2))
63    
64CREATE TABLE table1 (fk_field1 type1, field2 type2, ...,
65  FOREIGN KEY (fk_field1) REFERENCES table2 (t2_fieldA))
66    [ON UPDATE|ON DELETE] [CASCADE|SET NULL]
67  
68CREATE TABLE table1 (fk_field1 type1, fk_field2 type2, ...,
69 FOREIGN KEY (fk_field1, fk_field2) REFERENCES table2 (t2_fieldA, t2_fieldB))
70  
71CREATE TABLE table IF NOT EXISTS (...)
72  
73CREATE TEMPORARY TABLE table (...)
74  
75*Drop*
76DROP TABLE table
77DROP TABLE IF EXISTS table
78DROP TABLE table1, table2, ...
79  
80*Alter*
81ALTER TABLE table MODIFY field1 type1
82ALTER TABLE table MODIFY field1 type1 NOT NULL ...
83ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1
84ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 NOT NULL ...
85ALTER TABLE table ALTER field1 SET DEFAULT ...
86ALTER TABLE table ALTER field1 DROP DEFAULT
87ALTER TABLE table ADD new_name_field1 type1
88ALTER TABLE table ADD new_name_field1 type1 FIRST
89ALTER TABLE table ADD new_name_field1 type1 AFTER another_field
90ALTER TABLE table DROP field1
91ALTER TABLE table ADD INDEX (field);
92  
93*Change field order*
94ALTER TABLE table MODIFY field1 type1 FIRST
95ALTER TABLE table MODIFY field1 type1 AFTER another_field
96ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 FIRST
97ALTER TABLE table CHANGE old_name_field1 new_name_field1 type1 AFTER
98another_field
99Keys
100CREATE TABLE table (..., PRIMARY KEY (field1, field2))
101CREATE TABLE table (..., FOREIGN KEY (field1, field2) REFERENCES table2
102(t2_field1, t2_field2))
103Users and Privileges
104GRANT ALL PRIVILEGES ON base.* TO 'user'@'localhost' IDENTIFIED BY 'password';
105GRANT SELECT, INSERT, DELETE ON base.* TO 'user'@'localhost' IDENTIFIED BY 'password';
106REVOKE ALL PRIVILEGES ON base.* FROM 'user'@'host'; -- one permission only
107REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'host'; -- all permissions
108  
109SET PASSWORD = PASSWORD('new_pass')
110SET PASSWORD FOR 'user'@'host' = PASSWORD('new_pass')
111SET PASSWORD = OLD_PASSWORD('new_pass')
112  
113DROP USER 'user'@'host'
114host ‘%’ indicates any host.
115Main Data Types
116 TINYINT (1o: -217+128) SMALLINT (2o: +-65 000)
117 MEDIUMINT (3o: +-16 000 000) INT (4o: +- 2 000 000 000)
118 BIGINT (8o: +-9.10^18)
119 
120 Precise interval: -(2^(8*N-1)) -> (2^8*N)-1
121 /!\ INT(2) = "2 digits displayed" -- NOT "number with 2 digits max"
122  
123 FLOAT(M,D) DOUBLE(M,D) FLOAT(D=0->53)
124 /!\ 8,3 -> 12345,678 -- NOT 12345678,123!
125  
126 TIME (HH:MM) YEAR (AAAA) DATE (AAAA-MM-JJ) DATETIME (AAAA-MM-JJ HH:MM; années 1000->9999)
127 TIMESTAMP (like DATETIME, but 1970->2038, compatible with Unix)
128 
129 VARCHAR (single-line; explicit size) TEXT (multi-lines; max size=65535) BLOB (binary; max size=65535)
130 Variants for TEXT&BLOB: TINY (max=255) MEDIUM (max=~16000) LONG (max=4Go)
131 Ex: VARCHAR(32), TINYTEXT, LONGBLOB, MEDIUMTEXT
132 
133 ENUM ('value1', 'value2', ...) -- (default NULL, or '' if NOT NULL)
134Reset Root Password
135$ /etc/init.d/mysql stop
136$ mysqld_safe --skip-grant-tables
137$ mysql # on another terminal
138mysql> UPDATE mysql.user SET password=PASSWORD('new_pass') WHERE user='root';
139## Switch back to the mysqld_safe terminal and kill the process using Control + \
140$ /etc/init.d/mysql start</code>