mysql.md

   1 ---
   2 title: MySQL
   3 updated: 2020-07-05
   4 layout: 2017/sheet
   5 category: Databases
   6 ---
   7
   8 ### Browsing
   9
  10 ```sql
  11 SHOW DATABASES;
  12 SHOW TABLES;
  13 SHOW FIELDS FROM table / DESCRIBE table;
  14 SHOW CREATE TABLE table;
  15 SHOW PROCESSLIST;
  16 KILL process_number;
  17 ```
  18
  19 ### Select
  20
  21 ```sql
  22 SELECT * FROM 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 ...
  33 ```
  34
  35 ### Select - Join
  36
  37 ```sql
  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 ...
  41 ```
  42
  43 ### Conditions
  44
  45 ```sql
  46 field1 = value1
  47 field1 <> value1
  48 field1 LIKE 'value _ %'
  49 field1 IS NULL
  50 field1 IS NOT NULL
  51 field1 IS IN (value1, value2)
  52 field1 IS NOT IN (value1, value2)
  53 condition1 AND condition2
  54 condition1 OR condition2
  55 ```
  56
  57 ### Create / Open / Delete Database
  58
  59 ```sql
  60 CREATE DATABASE DatabaseName;
  61 CREATE DATABASE DatabaseName CHARACTER SET utf8;
  62 USE DatabaseName;
  63 DROP DATABASE DatabaseName;
  64 ALTER DATABASE DatabaseName CHARACTER SET utf8;
  65 ```
  66
  67 ### Backup Database to SQL File
  68
  69 ```bash
  70 mysqldump -u Username -p dbNameYouWant > databasename_backup.sql
  71 ```
  72
  73 ### Restore from backup SQL File
  74
  75 ```bash
  76 mysql -u Username -p dbNameYouWant < databasename_backup.sql;
  77 ```
  78
  79 ### Repair Tables After Unclean Shutdown
  80
  81 ```bash
  82 mysqlcheck --all-databases;
  83 mysqlcheck --all-databases --fast;
  84 ```
  85
  86 ### Insert
  87
  88 ```sql
  89 INSERT INTO table1 (field1, field2) VALUES (value1, value2);
  90 ```
  91
  92 ### Delete
  93
  94 ```sql
  95 DELETE FROM table1 / TRUNCATE table1
  96 DELETE FROM table1 WHERE condition
  97 DELETE FROM table1, table2 FROM table1, table2 WHERE table1.id1 =
  98   table2.id2 AND condition
  99 ```
 100
 101 ### Update
 102
 103 ```sql
 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;
 107 ```
 108
 109 ### Create / Delete / Modify Table
 110
 111 #### Create
 112
 113 ```sql
 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));
 118 ```
 119
 120 ```sql
 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]
 124 ```
 125
 126 ```sql
 127 CREATE TABLE table1 (fk_field1 type1, fk_field2 type2, ...,
 128  FOREIGN KEY (fk_field1, fk_field2) REFERENCES table2 (t2_fieldA, t2_fieldB))
 129 ```
 130
 131 ```sql
 132 CREATE TABLE table IF NOT EXISTS;
 133 ```
 134
 135 ```sql
 136 CREATE TEMPORARY TABLE table;
 137 ```
 138
 139 #### Drop
 140
 141 ```sql
 142 DROP TABLE table;
 143 DROP TABLE IF EXISTS table;
 144 DROP TABLE table1, table2, ...
 145 ```
 146
 147 #### Alter
 148
 149 ```sql
 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);
 161 ```
 162
 163 #### Change field order
 164
 165 ```sql
 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
 170   another_field
 171 ```
 172
 173 ### Keys
 174
 175 ```sql
 176 CREATE TABLE table (..., PRIMARY KEY (field1, field2))
 177 CREATE TABLE table (..., FOREIGN KEY (field1, field2) REFERENCES table2
 178 (t2_field1, t2_field2))
 179 ```
 180
 181 ### Users and Privileges
 182
 183 ```sql
 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
 189 FLUSH PRIVILEGES;
 190 ```
 191
 192 ```sql
 193 SET PASSWORD = PASSWORD('new_pass');
 194 SET PASSWORD FOR 'user'@'host' = PASSWORD('new_pass');
 195 SET PASSWORD = OLD_PASSWORD('new_pass');
 196 ```
 197
 198 ```sql
 199 DROP USER 'user'@'host';
 200 ```
 201
 202 Host ‘%’ indicates any host.
 203
 204 ### Main Data Types
 205
 206 ```sql
 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)
 212 ```
 213
 214 ```sql
 215 Precise interval: -(2^(8*N-1)) -> (2^8*N)-1
 216 ```
 217
 218 ⚠ INT(2) = "2 digits displayed" -- NOT "number with 2 digits max"
 219
 220 ```sql
 221 FLOAT(M,D)
 222 DOUBLE(M,D)
 223 FLOAT(D=0->53)
 224 ```
 225
 226 ⚠ 8,3 -> 12345,678 -- NOT 12345678,123!
 227
 228 ```sql
 229 TIME (HH:MM)
 230 YEAR (AAAA)
 231 DATE (AAAA-MM-JJ)
 232 DATETIME (AAAA-MM-JJ HH:MM; années 1000->9999)
 233 TIMESTAMP (like DATETIME, but 1970->2038, compatible with Unix)
 234 ```
 235
 236 ```sql
 237 VARCHAR (single-line; explicit size)
 238 TEXT (multi-lines; max size=65535)
 239 BLOB (binary; max size=65535)
 240 ```
 241
 242 Variants for TEXT&BLOB: `TINY` (max=255), `MEDIUM` (max=~16000), and `LONG` (max=4Go). Ex: `VARCHAR(32)`, `TINYTEXT`, `LONGBLOB`, `MEDIUMTEXT`
 243
 244 ```sql
 245 ENUM ('value1', 'value2', ...) -- (default NULL, or '' if NOT NULL)
 246 ```
 247
 248 ### Reset Root Password
 249
 250 ```bash
 251 $ /etc/init.d/mysql stop
 252 ```
 253
 254 ```bash
 255 $ mysqld_safe --skip-grant-tables
 256 ```
 257
 258 ```bash
 259 $ mysql # on another terminal
 260 mysql> UPDATE mysql.user SET password=PASSWORD('new_pass') WHERE user='root';
 261 ```
 262
 263 ```bash
 264 ## Switch back to the mysqld_safe terminal and kill the process using Control + \
 265 $ /etc/init.d/mysql start
 266 ```
 267
 268 Your commands may vary depending on your OS.