Mysql

From eBabel
Jump to navigationJump to search

Initial mysql setup

Install mysql on MacOS

 brew install mysql

Start mysql after install

 brew services start mysql

Secure mysql after first start

Creates a password for root

 mysql_secure_installation

Connect to mysql with default root user in Terminal

 mysql -u root -p

List all databases

 show databases;

Default result

 +--------------------+
 | Database           |
 +--------------------+
 | information_schema |
 | mysql              |
 | performance_schema |
 | sys                |
 +--------------------+
 4 rows in set (0.02 sec)

New database

List all users

 SELECT host, user FROM mysql.user;

Create a new user

Start by creating a new user that will later be used to create the database.

 mysql -u root -p
 CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';
 GRANT ALL ON databasename.* TO 'jeffrey'@'localhost';
 \q

Create a new database

Now login as the newly create user 'jeffrey' with the password 'password' (of course, change the values of jeffrey and password to something else).

 mysql -u jeffrey -p
 CREATE DATABASE IF NOT EXISTS databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ENCRYPTION 'N';

It's now possible to switch to that database, to create tables for examples.

 USE databasename;

Create a new table

 CREATE TABLE IF NOT EXISTS table_name (
   table_name_id INT AUTO_INCREMENT PRIMARY KEY,
   title VARCHAR(255) NOT NULL,
   start_date DATE,
   status TINYINT NOT NULL,
   description TEXT,
   created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 );

Show columns in a table

 SHOW COLUMNS FROM table_name;

or

 DESC table_name;

CRUD statements

Create

 INSERT INTO table_name (col1, col2, col3) VALUES ('value1', 4, 'value3');

Read

 SELECT * FROM table_name WHERE col2 = 4;

Update

 UPDATE table_name SET col1 = 'value1', col3 = 'value3' WHERE col2 = 5;

Delete

 DELETE FROM table_name WHERE col2 = 4;

Misc commands

Rename column

This works in mysql 8+. Older versions of mysql use a different syntax.

 ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

Update data type of a column

To change the column_name type from VARCHAR(2) to CHAR(2), for example:

 ALTER TABLE table_name MODIFY COLUMN column_name CHAR(2);

Add a new column

 ALTER TABLE table_name ADD column_name data_type;

Analyse a query execution

To find out how mysql will run a query, use EXPLAIN:

 EXPLAIN query;

For example:

 EXPLAIN SELECT col1, col2 FROM table_name WHERE col3 = 4;

Show password requirements

 SHOW VARIABLES LIKE 'validate_password%';

Update password requirements

 SET GLOBAL validate_password.length = 6;

Annexes

Character Sets on mysql 8

List all the possible character sets, to be used in the options when creating a database.

 SHOW CHARACTER SET;
 +----------+---------------------------------+---------------------+--------+
 | Charset  | Description                     | Default collation   | Maxlen |
 +----------+---------------------------------+---------------------+--------+
 | armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
 | ascii    | US ASCII                        | ascii_general_ci    |      1 |
 | big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
 | binary   | Binary pseudo charset           | binary              |      1 |
 | cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
 | cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
 | cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
 | cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
 | cp850    | DOS West European               | cp850_general_ci    |      1 |
 | cp852    | DOS Central European            | cp852_general_ci    |      1 |
 | cp866    | DOS Russian                     | cp866_general_ci    |      1 |
 | cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
 | dec8     | DEC West European               | dec8_swedish_ci     |      1 |
 | eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
 | euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
 | gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
 | gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
 | gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
 | geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
 | greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
 | hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
 | hp8      | HP West European                | hp8_english_ci      |      1 |
 | keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
 | koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
 | koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
 | latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
 | latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
 | latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
 | latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
 | macce    | Mac Central European            | macce_general_ci    |      1 |
 | macroman | Mac West European               | macroman_general_ci |      1 |
 | sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
 | swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
 | tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
 | ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
 | ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
 | utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
 | utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
 | utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
 | utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
 | utf8mb4  | UTF-8 Unicode                   | utf8mb4_0900_ai_ci  |      4 |
 +----------+---------------------------------+---------------------+--------+
 41 rows in set (0.00 sec)

The most comprehensive set for a Western centric application to support multiple languages would be utf8mb4, not utf8. The maximum length for encoding characters with utf8 is only 3, which isn't enough to encode something like an emoticon character.