mysql-explained

<?xml version="1.0" encoding="UTF-8"?>

<Books>
    <ID>1</ID>
    <Title>Stormy Weather</Title>
    <Author>Carl Hiassen</Author>
    <Year>1995</Year>
    <Pages>335</Pages>
</Books>

{
  { "Title": "Stormy Weather",
    "Author": "Carl Hiassen",
    "Year": "1995",
    "Pages": "335"
  },
  { "Title": "Dune",
    "Author": "Frank Herbert",
    "Year": "1965",
    "Pages": "528"
  }
}

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.17 Homebrew

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

mysql> SELECT CURRENT_DATE() as "Current Date";
SELECT CURRENT_DATE() as "Current Date";
+--------------+
| Current Date |
+--------------+
| 2017-03-20   |
+--------------+
1 row in set (0.01 sec)

mysql> quit
quit
Bye

mysql> SELECT CURRENT_DATE() AS 'Today\'s Date';
SELECT CURRENT_DATE() AS 'Today\'s Date';
+--------------+
| Today's Date |
+--------------+
| 2017-03-20   |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT CURRENT_DATE() AS "Today's Date";
SELECT CURRENT_DATE() AS "Today's Date";
+--------------+
| Today's Date |
+--------------+
| 2017-03-20   |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT CURRENT_DATE() AS `Today's Date`;
SELECT CURRENT_DATE() AS `Today's Date`;
+--------------+
| Today's Date |
+--------------+
| 2017-03-20   |
+--------------+
1 row in set (0.00 sec)

SELECT CURRENT_DATE() AS `Today's
    `> Date`;
+---------------+
| Today's
Date |
+---------------+
| 2017-03-20    |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT NOW() AS "The current date and time is - "; SELECT CONCAT(USER(), ' is currently logged in.') as "Current User";
+---------------------------------+
| The current date and time is -  |
+---------------------------------+
| 2017-03-20 17:14:57             |
+---------------------------------+
1 row in set (0.00 sec)

+----------------------------------------+
| Current User                           |
+----------------------------------------+
| root@localhost is currently logged in. |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CURRENT_DATE() AS "Today's Date", ADDDATE(CURRENT_DATE(), 30) AS "30 Days", ADDDATE(CURRENT_DATE(), 60) AS "60 Days";
SELECT CURRENT_DATE() AS "Today's Date", ADDDATE(CURRENT_DATE(), 30) AS "30 Days", ADDDATE(CURRENT_DATE(), 60) AS "60 Days";
+--------------+------------+------------+
| Today's Date | 30 Days    | 60 Days    |
+--------------+------------+------------+
| 2017-03-20   | 2017-04-19 | 2017-05-19 |
+--------------+------------+------------+
1 row in set (0.00 sec)

mysql> SELECT CURRENT_DATE() AS "Today's Date", DATEDIFF('2017-08-30', CURRENT_DATE()) AS "Days Remaining";
SELECT CURRENT_DATE() AS "Today's Date", DATEDIFF('2017-08-30', CURRENT_DATE()) AS "Days Remaining";
+--------------+----------------+
| Today's Date | Days Remaining |
+--------------+----------------+
| 2017-03-20   |            163 |
+--------------+----------------+
1 row in set (0.00 sec)

mysql> SELECT 25 + 3 / 4 * (3 - 1) + (( 10 + 15 + 20) / 3) as Result;
SELECT 25 + 3 / 4 * (3 - 1) + (( 10 + 15 + 20) / 3) as Result;
+---------+
| Result  |
+---------+
| 41.5000 |
+---------+
1 row in set (0.00 sec)

mysql> SHOW DATABASES;
SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> USE information_schema;
USE information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW TABLES;
SHOW TABLES;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| ....                                  |

mysql> SELECT COUNT(*) FROM COLUMNS;
SELECT COUNT(*) FROM COLUMNS;
+----------+
| COUNT(*) |
+----------+
|     3104 |
+----------+
1 row in set (0.04 sec)

mysql> SELECT COUNT(*) FROM information_schema.COLUMNS;
SELECT COUNT(*) FROM information_schema.COLUMNS;
+----------+
| COUNT(*) |
+----------+
|     3104 |
+----------+
1 row in set (0.03 sec)

mysql> SHOW COLUMNS IN CHARACTER_SETS;
SHOW COLUMNS IN CHARACTER_SETS;
+----------------------+-------------+------+-----+---------+-------+
| Field                | Type        | Null | Key | Default | Extra |
+----------------------+-------------+------+-----+---------+-------+
| CHARACTER_SET_NAME   | varchar(32) | NO   |     |         |       |
| DEFAULT_COLLATE_NAME | varchar(32) | NO   |     |         |       |
| DESCRIPTION          | varchar(60) | NO   |     |         |       |
| MAXLEN               | bigint(3)   | NO   |     | 0       |       |
+----------------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> SHOW COLUMNS IN CHARACTER_SETS\G
SHOW COLUMNS IN CHARACTER_SETS\G
*************************** 1. row ***************************
  Field: CHARACTER_SET_NAME
   Type: varchar(32)
   Null: NO
    Key:
Default:
  Extra:
*************************** 2. row ***************************
  Field: DEFAULT_COLLATE_NAME
   Type: varchar(32)
   Null: NO
    Key:
Default:
  Extra:
*************************** 3. row ***************************
  Field: DESCRIPTION
   Type: varchar(60)
   Null: NO
    Key:
Default:
  Extra:
*************************** 4. row ***************************
  Field: MAXLEN
   Type: bigint(3)
   Null: NO
    Key:
Default: 0
  Extra:
4 rows in set (0.00 sec)

mysql> CREATE DATABASE JobSearchPlus;
CREATE DATABASE JobSearchPlus;
Query OK, 1 row affected (0.00 sec)

mysql> SHOW DATABASES;
SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| JobSearchPlus      |
| mysql              |
| performance_schema |
| sys                |
| toy_development    |
| toy_test           |
| vwops_development  |
| vwops_test         |
+--------------------+
9 rows in set (0.00 sec)

mysql> CREATE DATABASE IF NOT EXISTS JobSearchPlus;
CREATE DATABASE IF NOT EXISTS JobSearchPlus;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SELECT * FROM information_schema.SCHEMATA WHERE schema_name = "JobSearchPlus";
SELECT * FROM information_schema.SCHEMATA WHERE schema_name = "JobSearchPlus";
+--------------+---------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME   | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+---------------+----------------------------+------------------------+----------+
| def          | jobsearchplus | utf8                       | utf8_general_ci        | NULL     |
+--------------+---------------+----------------------------+------------------------+----------+
1 row in set (0.00 sec)

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

mysql> SHOW COLLATION WHERE Charset = 'utf8mb4';
SHOW COLLATION WHERE Charset = 'utf8mb4';
+------------------------+---------+-----+---------+----------+---------+
| Collation              | Charset | Id  | Default | Compiled | Sortlen |
+------------------------+---------+-----+---------+----------+---------+
| utf8mb4_general_ci     | utf8mb4 |  45 | Yes     | Yes      |       1 |
| utf8mb4_bin            | utf8mb4 |  46 |         | Yes      |       1 |
| utf8mb4_unicode_ci     | utf8mb4 | 224 |         | Yes      |       8 |
| utf8mb4_icelandic_ci   | utf8mb4 | 225 |         | Yes      |       8 |
| utf8mb4_latvian_ci     | utf8mb4 | 226 |         | Yes      |       8 |
| utf8mb4_romanian_ci    | utf8mb4 | 227 |         | Yes      |       8 |
| utf8mb4_slovenian_ci   | utf8mb4 | 228 |         | Yes      |       8 |
| utf8mb4_polish_ci      | utf8mb4 | 229 |         | Yes      |       8 |
| utf8mb4_estonian_ci    | utf8mb4 | 230 |         | Yes      |       8 |
| utf8mb4_spanish_ci     | utf8mb4 | 231 |         | Yes      |       8 |
| utf8mb4_swedish_ci     | utf8mb4 | 232 |         | Yes      |       8 |
| utf8mb4_turkish_ci     | utf8mb4 | 233 |         | Yes      |       8 |
| utf8mb4_czech_ci       | utf8mb4 | 234 |         | Yes      |       8 |
| utf8mb4_danish_ci      | utf8mb4 | 235 |         | Yes      |       8 |
| utf8mb4_lithuanian_ci  | utf8mb4 | 236 |         | Yes      |       8 |
| utf8mb4_slovak_ci      | utf8mb4 | 237 |         | Yes      |       8 |
| utf8mb4_spanish2_ci    | utf8mb4 | 238 |         | Yes      |       8 |
| utf8mb4_roman_ci       | utf8mb4 | 239 |         | Yes      |       8 |
| utf8mb4_persian_ci     | utf8mb4 | 240 |         | Yes      |       8 |
| utf8mb4_esperanto_ci   | utf8mb4 | 241 |         | Yes      |       8 |
| utf8mb4_hungarian_ci   | utf8mb4 | 242 |         | Yes      |       8 |
| utf8mb4_sinhala_ci     | utf8mb4 | 243 |         | Yes      |       8 |
| utf8mb4_german2_ci     | utf8mb4 | 244 |         | Yes      |       8 |
| utf8mb4_croatian_ci    | utf8mb4 | 245 |         | Yes      |       8 |
| utf8mb4_unicode_520_ci | utf8mb4 | 246 |         | Yes      |       8 |
| utf8mb4_vietnamese_ci  | utf8mb4 | 247 |         | Yes      |       8 |
+------------------------+---------+-----+---------+----------+---------+
26 rows in set (0.00 sec)

mysql> CREATE DATABASE JobSearchPlus CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
CREATE DATABASE JobSearchPlus CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM information_schema.SCHEMATA WHERE schema_name = "JobSearchPlus";
SELECT * FROM information_schema.SCHEMATA WHERE schema_name = "JobSearchPlus";
+--------------+---------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME   | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+---------------+----------------------------+------------------------+----------+
| def          | jobsearchplus | utf8mb4                    | utf8mb4_unicode_ci     | NULL     |
+--------------+---------------+----------------------------+------------------------+----------+
1 row in set (0.00 sec)

mysql> SELECT user, host from mysql.user;
SELECT user, host from mysql.user;
+-----------+-----------+
| user      | host      |
+-----------+-----------+
| mysql.sys | localhost |
| root      | localhost |
+-----------+-----------+
2 rows in set (0.00 sec)

mysql> SHOW COLUMNS FROM mysql.user;
SHOW COLUMNS FROM mysql.user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(32)                          | NO   | PRI |                       |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| File_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| References_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N                     |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N                     |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N                     |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       |
| ssl_cipher             | blob                              | NO   |     | NULL                  |       |
| x509_issuer            | blob                              | NO   |     | NULL                  |       |
| x509_subject           | blob                              | NO   |     | NULL                  |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0                     |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0                     |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0                     |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0                     |       |
| plugin                 | char(64)                          | NO   |     | mysql_native_password |       |
| authentication_string  | text                              | YES  |     | NULL                  |       |
| password_expired       | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed  | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime      | smallint(5) unsigned              | YES  |     | NULL                  |       |
| account_locked         | enum('N','Y')                     | NO   |     | N                     |       |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)

mysql -uroot

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpassword');
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpassword');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> exit;
exit;
Bye
hfeng@ mysql (master) $ mysql -uroot
mysql -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
hfeng@ mysql (master) $ mysql -uroot -pnewpassword
mysql -uroot -pnewpassword
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 5.7.17 Homebrew

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

mysql> RENAME USER 'root'@'localhost' to 'primary'@'localhost';

mysql> SELECT Host, Db, User, Delete_priv FROM mysql.db ;
SELECT Host, Db, User, Delete_priv FROM mysql.db ;
+-----------+---------------+-----------+-------------+
| Host      | Db            | User      | Delete_priv |
+-----------+---------------+-----------+-------------+
| localhost | sys           | mysql.sys | N           |
| localhost | jobsearchplus | martin    | Y           |
+-----------+---------------+-----------+-------------+
2 rows in set (0.00 sec)

mysql> REVOKE DELETE ON JobSearchPlus.* FROM 'martin'@'localhost';
REVOKE DELETE ON jobsearchplus.* FROM 'martin'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT Host, Db, User, Delete_priv FROM mysql.db ;
SELECT Host, Db, User, Delete_priv FROM mysql.db ;
+-----------+---------------+-----------+-------------+
| Host      | Db            | User      | Delete_priv |
+-----------+---------------+-----------+-------------+
| localhost | sys           | mysql.sys | N           |
| localhost | jobsearchplus | martin    | N           |
+-----------+---------------+-----------+-------------+
2 rows in set (0.00 sec)

mysql> SHOW GRANTS FOR 'martin'@'localhost';
SHOW GRANTS FOR 'martin'@'localhost';
+---------------------------------------------------------------------------+
| Grants for martin@localhost                                               |
+---------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'martin'@'localhost'                                |
| GRANT SELECT, INSERT, UPDATE ON `jobsearchplus`.* TO 'martin'@'localhost' |
+---------------------------------------------------------------------------+
2 rows in set (0.00 sec)

USE jobsearch_plus;