MySQL

MySQL Shell Guide

Connecting to and Disconnecting from the Server

1

shell> mysql -h host -u user -p

1

mysql> QUIT

User Account Management

Adding User Accounts

1
2


shell> mysql --user=root --password mysql
shell> mysql --u root -p mysql

1
2
3
4
5
6
7
8
9


mysql> CREATE USER 'custom'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'finley'@'localhost'
    ->     WITH GRANT OPTION;
mysql> CREATE USER 'custom'@'%' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'finley'@'%'
    ->     WITH GRANT OPTION;
mysql> CREATE USER 'admin'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
mysql> CREATE USER 'dummy'@'localhost';

Two accounts have a user name of custom. Both are superuser accounts with full privileges to do anything. The 'custom'@'localhost' account can be used only when connecting from the local host. The 'custom'@'%' account uses the '%' wildcard for the host part, so it can be used to connect from any host.
The 'admin'@'localhost'account can be used only by admin to connect from the local host. It is granted the RELOAD and PROCESS administrative privileges. These privileges enable the admin user to execute the mysqladmin reload, mysqladmin refresh, and mysqladmin flush-xxx commands, as well as mysqladmin processlist . No privileges are granted for accessing any databases.
The 'dummy'@'localhost' account has no password (which is insecure and not recommended).

Removing User Accounts

1

mysql> DROP USER 'jeffrey'@'localhost';

Assigning Account Passwords

1

SET PASSWORD FOR 'jeffrey'@'localhost' = PASSWORD('password');

1

GRANT USAGE ON *.* TO 'jeffrey'@'localhost' IDENTIFIED BY 'password';

1
2


# the client host from which you connect in the Host column.
mysqladmin -u user_name -h host_name password "password"

MySQl Shell Code Execution

1

shell> echo "show databases;" | mysqlsh --sql --uri root@198.51.100.141:33060

Active Language

1
2
3


shell> mysqlsh -h host -u user -p --sql < code.sql > mysql.out
shell> mysqlsh -h host -u user -p < code.js > mysql.out
shell> mysqlsh -h host -u user -p --py < code.py > mysql.out

Multiple-line Support

When in Python or JavaScript mode, multiple-line mode is automatically enabled.

In SQL mode multiple line mode starts when the command \ is issued.

1
2
3
4
5
6
7


mysql-sql> \
... create procedure get_actors()
... begin
...   select first_name from sakila.actor;
... end
...
mysql-sql>

MySQL Shell Commands

Command	Alias/Shortcut	Description
`\help`	`\h` or `\?`	Prints help about MySQL Shell commands.
`\quit`	`\q` or `\exit`	Exit MySQL Shell.
`\`		In SQL mode, begin multiple-line mode. Code is cached and executed when an empty line is entered.
`\status`	`\s`	Show the current MySQL Shell status.
`\js`		Switch execution mode to JavaScript.
`\py`		Switch execution mode to Python.
`\sql`		Switch execution mode to SQL.
`\connect`	`\c`	Connect to a MySQL Server
`\reconnect`		Reconnect to the same MySQL Server
`\use`	`\u`	Specify the schema to use.
`\source`	`\.`	Execute a script file using the active language.
`\warnings`	`\W`	Show any warnings generated by a statement.
`\nowarnings`	`\w`	Do not show any warnings generated by a statement.
`\history`		View and edit command line history.
`\rehash`		Manually update the autocomplete name cache

Example

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


\help connect
\connect root@localhost:3306
;;Use the --mysqlx(-mx) option to create a session using the X Protocol 
\connect --mysqlx root@localhost:33060 
;;Use the --mysql(-mc) option to create a ClassicSession,
\connect --mysql root@localhost:3306
\status
\source /tmp/mydata.sql
\use schema_name
\history save
\history clear
\rehash

Queries

1
2
3
4
5
6
7


mysql> SELECT VERSION(), CURRENT_DATE; SELECT NOW();
mysql> CREATE DATABASE menagerie;
mysql> USE menagerie
mysql> SHOW TABLES;
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
    -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
mysql> DESCRIBE pet;

1
2


mysql> INSERT INTO pet
    -> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

1

mysql> DELETE FROM table_name;

1

mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser' OR species = 'bird';

1

mysql> SELECT * FROM pet WHERE name = 'Bowser' AND birth >= '1998-1-1';

1

mysql> SELECT DISTINCT owner FROM pet;

1
2


mysql> SELECT name, species, birth FROM pet
    -> ORDER BY species, birth DESC;

Maximum

1

SELECT MAX(article) AS article FROM shop;

1
2
3


SELECT article, MAX(price) AS price
FROM   shop
GROUP BY article;

Date Calculations

1
2
3
4


# TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
mysql> SELECT name, birth, CURDATE(),
    -> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
    -> FROM pet WHERE death IS NOT NULL ORDER BY age;

1
2


# YEAR(date), MONTH(date), DAYOFMONTH(date)
mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;

1
2
3


# DATE_ADD(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit)
mysql> SELECT name, birth FROM pet
    -> WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));

1
2
3


# MOD(N,M) is N % M
mysql> SELECT name, birth FROM pet
    -> WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;

NULL Values

1

mysql> SELECT 1 IS NOT NULL, '' IS NOT NULL;

When doing an ORDER BY, NULL values are presented first if you do ORDER BY ... ASC and last if you do ORDER BY ... DESC.

Pattern Matching

Counting Rows

1
2
3
4


SELECT owner, COUNT(*) FROM pet GROUP BY owner;
mysql> SELECT species, sex, COUNT(*) FROM pet
    -> WHERE species = 'dog' OR species = 'cat'
    -> GROUP BY species, sex;

Using More Than one Table

1
2
3
4
5
6


mysql> SELECT pet.name,
    -> TIMESTAMPDIFF(YEAR,birth,date) AS age,
    -> remark
    -> FROM pet INNER JOIN event
    ->   ON pet.name = event.name
    -> WHERE event.type = 'litter';

1
2
3


mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
    -> FROM pet AS p1 INNER JOIN pet AS p2
    ->   ON p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';

1
2
3
4


SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.price < s2.price
WHERE s2.article IS NULL;

Loading Data into a Table

1
2
3
4


mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;
# File on Windows with an editor that uses \r\n as a line terminator
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
    -> LINES TERMINATED BY '\r\n';

Searching on Two Keys

1
2


SELECT field1_index, field2_index FROM test_table
WHERE field1_index = '1' OR  field2_index = '1'

1
2
3
4
5


SELECT field1_index, field2_index
    FROM test_table WHERE field1_index = '1'
UNION
SELECT field1_index, field2_index
    FROM test_table WHERE field2_index = '1';

subquery

1
2
3
4
5


SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
              FROM shop s2
              WHERE s1.article = s2.article);

1
2
3
4
5
6
7


SELECT s1.article, dealer, s1.price
FROM shop s1
JOIN (
  SELECT article, MAX(price) AS price
  FROM shop
  GROUP BY article) AS s2
  ON s1.article = s2.article AND s1.price = s2.price;

LEFT JOIN

1
2
3
4


SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
WHERE s2.article IS NULL;

Table of Contents