mysql.md

   1 ---
   2 title: MySQL
   3 updated: 2018-12-25
   4 layout: 2017/sheet
   5 category: Databases
   6 ---
   7
   8 ### Create / Delete Database
   9
  10 ```sql
  11 CREATE DATABASE dbNameYouWant
  12 CREATE DATABASE dbNameYouWant CHARACTER SET utf8
  13 DROP DATABASE dbNameYouWant
  14 ALTER DATABASE dbNameYouWant CHARACTER SET utf8
  15 ```
  16
  17 ### Backup Database to SQL File
  18
  19 ```bash
  20 mysqldump -u Username -p dbNameYouWant > databasename_backup.sql
  21 ```
  22
  23 ### Restore from backup SQL File
  24
  25 ```bash
  26 mysql - u Username -p dbNameYouWant < databasename_backup.sql
  27 ```
  28
  29 ### Repair Tables After Unclean Shutdown
  30
  31 ```bash
  32 mysqlcheck --all-databases
  33 mysqlcheck --all-databases --fast
  34 ```
  35
  36 ### Browsing
  37
  38 ```sql
  39 SHOW DATABASES
  40 SHOW TABLES
  41 SHOW FIELDS FROM table / DESCRIBE table
  42 SHOW CREATE TABLE table
  43 SHOW PROCESSLIST
  44 KILL process_number
  45 ```
  46
  47 ### Select
  48
  49 ```sql
  50 SELECT * FROM table
  51 SELECT * FROM table1, table2, ...
  52 SELECT field1, field2, ... FROM table1, table2, ...
  53 SELECT ... FROM ... WHERE condition
  54 SELECT ... FROM ... WHERE condition GROUPBY field
  55 SELECT ... FROM ... WHERE condition GROUPBY field HAVING condition2
  56 SELECT ... FROM ... WHERE condition ORDER BY field1, field2
  57 SELECT ... FROM ... WHERE condition ORDER BY field1, field2 DESC
  58 SELECT ... FROM ... WHERE condition LIMIT 10
  59 SELECT DISTINCT field1 FROM ...
  60 SELECT DISTINCT field1, field2 FROM ...
  61 ```
  62
  63 ### Select - Join
  64
  65 ```sql
  66 SELECT ... FROM t1 JOIN t2 ON t1.id1 = t2.id2 WHERE condition
  67 SELECT ... FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2 WHERE condition
  68 SELECT ... FROM t1 JOIN (t2 JOIN t3 ON ...) ON ...
  69 ```
  70
  71 ### Conditions
  72
  73 ```sql
  74 field1 = value1
  75 field1 <> value1
  76 field1 LIKE 'value _ %'
  77 field1 IS NULL
  78 field1 IS NOT NULL
  79 field1 IS IN (value1, value2)
  80 field1 IS NOT IN (value1, value2)
  81 condition1 AND condition2
  82 condition1 OR condition2
  83 ```
  84
  85 ### Insert
  86
  87 ```sql
  88 INSERT INTO table1 (field1, field2, ...) VALUES (value1, value2, ...)
  89 ```
  90
  91 ### Delete
  92
  93 ```sql
  94 DELETE FROM table1 / TRUNCATE table1
  95 DELETE FROM table1 WHERE condition
  96 DELETE FROM table1, table2 FROM table1, table2 WHERE table1.id1 =
  97   table2.id2 AND condition
  98 ```
  99
 100 ### Update
 101
 102 ```sql
 103 UPDATE table1 SET field1=new_value1 WHERE condition
 104 UPDATE table1, table2 SET field1=new_value1, field2=new_value2, ... WHERE
 105   table1.id1 = table2.id2 AND condition
 106 ```
 107
 108 ### Create / Delete / Modify Table
 109
 110 #### Create
 111
 112 ```sql
 113 CREATE TABLE table (field1 type1, field2 type2, ...)
 114 CREATE TABLE table (field1 type1, field2 type2, ..., INDEX (field))
 115 CREATE TABLE table (field1 type1, field2 type2, ..., PRIMARY KEY (field1))
 116 CREATE TABLE table (field1 type1, field2 type2, ..., PRIMARY KEY (field1,
 117 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 GRANT ALL PRIVILEGES ON base.* TO 'user'@'localhost' IDENTIFIED BY 'password';
 185 GRANT SELECT, INSERT, DELETE ON base.* TO 'user'@'localhost' IDENTIFIED BY 'password';
 186 REVOKE ALL PRIVILEGES ON base.* FROM 'user'@'host'; -- one permission only
 187 REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'host'; -- all permissions
 188 ```
 189
 190 ```sql
 191 SET PASSWORD = PASSWORD('new_pass')
 192 SET PASSWORD FOR 'user'@'host' = PASSWORD('new_pass')
 193 SET PASSWORD = OLD_PASSWORD('new_pass')
 194 ```
 195
 196 ```sql
 197 DROP USER 'user'@'host'
 198 ```
 199
 200 Host ‘%’ indicates any host.
 201
 202 ### Main Data Types
 203
 204 ```sql
 205 TINYINT (1o: -217+128)
 206 SMALLINT (2o: +-65 000)
 207 MEDIUMINT (3o: +-16 000 000)
 208 INT (4o: +- 2 000 000 000)
 209 BIGINT (8o: +-9.10^18)
 210 ```
 211
 212 ```sql
 213 Precise interval: -(2^(8*N-1)) -> (2^8*N)-1
 214 ```
 215
 216 ⚠ INT(2) = "2 digits displayed" -- NOT "number with 2 digits max"
 217
 218 ```sql
 219 FLOAT(M,D)
 220 DOUBLE(M,D)
 221 FLOAT(D=0->53)
 222 ```
 223
 224 ⚠ 8,3 -> 12345,678 -- NOT 12345678,123!
 225
 226 ```sql
 227 TIME (HH:MM)
 228 YEAR (AAAA)
 229 DATE (AAAA-MM-JJ)
 230 DATETIME (AAAA-MM-JJ HH:MM; années 1000->9999)
 231 TIMESTAMP (like DATETIME, but 1970->2038, compatible with Unix)
 232 ```
 233
 234 ```sql
 235 VARCHAR (single-line; explicit size)
 236 TEXT (multi-lines; max size=65535)
 237 BLOB (binary; max size=65535)
 238 ```
 239
 240 Variants for TEXT&BLOB: `TINY` (max=255), `MEDIUM` (max=~16000), and `LONG` (max=4Go). Ex: `VARCHAR(32)`, `TINYTEXT`, `LONGBLOB`, `MEDIUMTEXT`
 241
 242 ```sql
 243 ENUM ('value1', 'value2', ...) -- (default NULL, or '' if NOT NULL)
 244 ```
 245
 246 ### Reset Root Password
 247
 248 ```bash
 249 $ /etc/init.d/mysql stop
 250 ```
 251
 252 ```bash
 253 $ mysqld_safe --skip-grant-tables
 254 ```
 255
 256 ```bash
 257 $ mysql # on another terminal
 258 mysql> UPDATE mysql.user SET password=PASSWORD('new_pass') WHERE user='root';
 259 ```
 260
 261 ```bash
 262 ## Switch back to the mysqld_safe terminal and kill the process using Control + \
 263 $ /etc/init.d/mysql start
 264 ```
 265
 266 Your commands may vary depending on your OS.