MySQL on OSX 10.13.6

Preamble

I needed to install/run and ply with SQL on High Sierra. Well… it is certainly not ‘out-of-the-box’…

 

I wanted an IDE, rather than command line, so I chose an archived MySQLWorkbench 6.3.10. It doesn’t come with MySQL server (see Does MySQLWorkbench include MySQL server?), so that needs to be installed with brew.

However, before I installed the server (using brew) I was playing around with the Workbench and it created a /etc/my.cnf with permissions rw-------, when they should be rw-r--r-- (644) – see answer. Use sudo chmod 644 /etc/my.cnfto fix this.

then install brew install mysql

Secure config:

mysql_secure_installation

Start server:

mysql.server start
mysql.server restart

To access mysql without a password

mysql -uroot

or with password

mysql -uroot -p
# Then prompted for password

or

mysql --user=root --password

No password by default, set with this answer in mysql

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '<password>';

From this answer I added for my.cnf:

[mysqld]

default_authentication_plugin=mysql_native_password

But it is contraversial, apparently.

 

In Workbench I also checked under Manage Server Connections > Connection>Advanced>Enable Cleartext Authentication plugin. But I am not sure it this is required.

After that I was able to connect to the server using Workbench.

Initially, each time I hit test connection, under Manage Server Connections, I was getting:

Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/mysql/lib/plugin/caching_sha2_password.so, 2): image not found

 

 

 

Maybe useful

https://www.guru99.com/introduction-to-mysql-workbench.html

https://www.macworld.co.uk/how-to/how-set-up-learn-sql-in-mac-os-x-3638150/

 

Tutorial

https://www.w3schools.com/sql/default.asp. Uses Northwind, see Is there a “Northwind” type database available for MySQL? I used MyWind, which worked, although:

  • CustomerID is id
  • Country is Country_Region.
+-----------------------------+
| Tables_in_northwind         |
+-----------------------------+
| customers                   |
| employee_privileges         |
| employees                   |
| inventory_transaction_types |
| inventory_transactions      |
| invoices                    |
| order_details               |
| order_details_status        |
| orders                      |
| orders_status               |
| orders_tax_status           |
| privileges                  |
| products                    |
| purchase_order_details      |
| purchase_order_status       |
| purchase_orders             |
| sales_reports               |
| shippers                    |
| strings                     |
| suppliers                   |
+-----------------------------+
20 rows in set (0.00 sec)

The SQL5 version here caused errors:

$ mysql -uroot -p < ~/Documents/SQL/Northwind.MySQL5.sql 
Enter password: 
ERROR 1418 (HY000) at line 4502: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

but the database seems more complete, and the fields are more compatible, except:

  • CustomerName is CompanyName.
  • CustomerID is not an int, but a string
+--------------------------------+
| Tables_in_northwind            |
+--------------------------------+
| alphabetical list of products  |
| Categories                     |
| category sales for 1997        |
| current product list           |
| customer and suppliers by city |
| CustomerCustomerDemo           |
| CustomerDemographics           |
| Customers                      |
| Employees                      |
| EmployeeTerritories            |
| invoices                       |
| Order Details                  |
| order details extended         |
| order subtotals                |
| Orders                         |
| orders qry                     |
| product sales for 1997         |
| Products                       |
| products above average price   |
| products by category           |
| quarterly orders               |
| Region                         |
| sales by category              |
| sales totals by amount         |
| Shippers                       |
| summary of sales by quarter    |
| summary of sales by year       |
| Suppliers                      |
| Territories                    |
+--------------------------------+
29 rows in set (0.00 sec)

This is a useful script for recreating the database tables (without populating them):

http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1_appe.html#11-2

Notes from tutorial

-- Tutorial w3schools
# From https://www.w3schools.com/sql/sql_join.asp

select * from customers;
SELECT Country FROM Customers;
SELECT Distinct Country FROM Customers;
SELECT count(Distinct Country) FROM Customers;

-- For m$ Access
SELECT Count(*) AS DistinctCountries
FROM (SELECT DISTINCT Country FROM Customers);
-- For m$ Access

SELECT * FROM Customers
WHERE Country='Mexico';

SELECT * FROM Customers
WHERE CustomerID=1;

SELECT * FROM Customers
WHERE Country='Germany' AND City='Berlin';

SELECT * FROM Customers
WHERE City='Berlin' OR City='München';

SELECT * FROM Customers
WHERE NOT Country='Germany';

SELECT * FROM Customers
WHERE Country='Germany' AND (City='Berlin' OR City='München');

SELECT * FROM Customers
WHERE NOT Country='Germany' AND NOT Country='USA';

SELECT * FROM Customers
ORDER BY Country;

SELECT * FROM Customers
ORDER BY Country DESC;

SELECT * FROM Customers
ORDER BY Country, CustomerName;

SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');


INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');

SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NULL;

UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;

UPDATE Customers
SET ContactName='Juan'
WHERE Country='Mexico';

-- Rename all!!!!
UPDATE Customers
SET ContactName='Juan';

DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';

-- Delete all!!!!
DELETE FROM table_name;

/*
-- Format:
-- SQL
SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;

-- MySQL
SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;

-- Oracle
SELECT column_name(s)
FROM table_name
ORDER BY column_name(s)
FETCH FIRST number ROWS ONLY;
*/

/*
-- SQL
SELECT TOP 3 * FROM Customers;
*/

-- MySQL
SELECT * FROM Customers
LIMIT 3;

/*
-- Oracle
SELECT * FROM Customers
FETCH FIRST 3 ROWS ONLY;
*/

/*
-- SQL
SELECT TOP 50 PERCENT * FROM Customers;
*/

-- MySQL
-- How to get 50% in MySQL?????
select count(country) as total from customers;
select count(*) as total from customers;
SELECT * FROM Customers
LIMIT total;
LIMIT (total/2);

SELECT * FROM Customers
LIMIT (select count(*) from customers);

/*
-- Oracle 
SELECT * FROM Customers
FETCH FIRST 50 PERCENT ROWS ONLY;
*/

SELECT * FROM Customers
WHERE Country='Germany'
LIMIT 3;

SELECT MIN(Price) AS SmallestPrice
FROM Products;

SELECT MAX(Price) AS LargestPrice
FROM Products;

SELECT COUNT(ProductID)
FROM Products;

SELECT AVG(Price)
FROM Products;

SELECT SUM(Quantity)
FROM OrderDetails;

SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';

SELECT * FROM Customers
WHERE CustomerName LIKE '%a';

SELECT * FROM Customers
WHERE CustomerName LIKE '%or%';

SELECT * FROM Customers
WHERE CustomerName LIKE '_r%';

SELECT * FROM Customers
WHERE CustomerName LIKE 'a__%';

SELECT * FROM Customers
WHERE ContactName LIKE 'a%o';

SELECT * FROM Customers
WHERE CustomerName NOT LIKE 'a%';

SELECT * FROM Customers
WHERE City LIKE 'ber%';

SELECT * FROM Customers
WHERE City LIKE '%es%';

SELECT * FROM Customers
WHERE City LIKE '_ondon';

SELECT * FROM Customers
WHERE City LIKE 'L_n_on';

SELECT * FROM Customers
WHERE City LIKE '[bsp]%';

SELECT * FROM Customers
WHERE City LIKE '[a-c]%';

SELECT * FROM Customers
WHERE City LIKE '[!bsp]%';

SELECT * FROM Customers
WHERE City NOT LIKE '[bsp]%';

SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');

SELECT * FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'UK');

SELECT * FROM Customers
WHERE Country IN (SELECT Country FROM Suppliers);


SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;

SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;

SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20
AND CategoryID NOT IN (1,2,3);

SELECT * FROM Products
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;

SELECT * FROM Products
WHERE ProductName BETWEEN "Carnarvon Tigers" AND "Chef Anton's Cajun Seasoning"
ORDER BY ProductName;

SELECT * FROM Products
WHERE ProductName NOT BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;


SELECT * FROM Orders
WHERE OrderDate BETWEEN #07/01/1996# AND #07/31/1996#;

SELECT * FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';

SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;

/*
-- SQL
SELECT CustomerName AS Customer, ContactName AS [Contact Person]
FROM Customers;

SELECT CustomerName AS Customer, ContactName AS "Contact Person"
FROM Customers;
*/

SELECT CustomerName AS Customer, ContactName AS 'Contact Person'
FROM Customers;

SELECT CustomerName, Address + ', ' + PostalCode + ' ' + City + ', ' + Country AS Address
FROM Customers;

SELECT CustomerName, CONCAT(Address,', ',PostalCode,', ',City,', ',Country) AS Address
FROM Customers;

-- Alias for tables
SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c, Orders AS o
WHERE c.CustomerName='Around the Horn' AND c.CustomerID=o.CustomerID;

SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName
FROM Customers, Orders
WHERE Customers.CustomerName='Around the Horn' AND Customers.CustomerID=Orders.CustomerID;

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM ((Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL  JOIN Orders ON Customers.CustomerID=Orders.CustomerID
-- FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.City = B.City
ORDER BY A.City;

-- UNION returns DISTINCT values
SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;

SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;

SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;

SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION ALL
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;

SELECT 'Customer' AS Type, ContactName, City, Country
FROM Customers
UNION
SELECT 'Supplier', ContactName, City, Country
FROM Suppliers;

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;

SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
GROUP BY ShipperName;

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM (Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID)
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 10;

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders
FROM Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
WHERE LastName = 'Davolio' OR LastName = 'Fuller'
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 25;

SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20); SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price = 22); SELECT ProductName FROM Products WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 10); SELECT ProductName FROM Products WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity > 99);

SELECT ALL ProductName
FROM Products
WHERE TRUE;

-- This is probably false and returns nothing as the condition is too strict.
SELECT ProductName
FROM Products
WHERE ProductID = ALL
  (SELECT ProductID
  FROM OrderDetails
  WHERE Quantity = 10);
  
  SELECT * INTO CustomersBackup2017
FROM Customers;

SELECT * INTO CustomersBackup2017 IN 'Backup.mdb'
FROM Customers;

SELECT CustomerName, ContactName INTO CustomersBackup2017
FROM Customers;

SELECT * INTO CustomersGermany
FROM Customers
WHERE Country = 'Germany';


SELECT Customers.CustomerName, Orders.OrderID
INTO CustomersOrderBackup2017
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

SELECT * INTO newtable
FROM oldtable
WHERE 1 = 0;


INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers;

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
SELECT SupplierName, ContactName, Address, City, PostalCode, Country FROM Suppliers;

INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers
WHERE Country='Germany';

SELECT OrderID, Quantity,
CASE WHEN Quantity > 30 THEN 'The quantity is greater than 30'
WHEN Quantity = 30 THEN 'The quantity is 30'
ELSE 'The quantity is under 30'
END AS QuantityText
FROM OrderDetails;

SELECT CustomerName, City, Country
FROM Customers
ORDER BY
(CASE
    WHEN City IS NULL THEN Country
    ELSE City
END);

-- This is no good if there is a NULL entry
SELECT ProductName, UnitPrice * (UnitsInStock + UnitsOnOrder)
FROM Products;

-- So, use ifnull
SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0))
FROM Products;

-- or colalese
SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0))
FROM Products;

/*
-- SQL
SELECT ProductName, UnitPrice * (UnitsInStock + ISNULL(UnitsOnOrder, 0))
FROM Products;
*/

/*
-- Oracle
SELECT ProductName, UnitPrice * (UnitsInStock + NVL(UnitsOnOrder, 0))
FROM Products;
*/

/*
-- MSAccess
SELECT ProductName, UnitPrice * (UnitsInStock + IIF(IsNull(UnitsOnOrder), 0, UnitsOnOrder))
FROM Products;
*/

/*
-- SQL
CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;

EXEC SelectAllCustomers;

-- Single parameter
CREATE PROCEDURE SelectAllCustomers @City nvarchar(30)
AS
SELECT * FROM Customers WHERE City = @City
GO;

EXEC SelectAllCustomers @City = 'London';

-- Multiple parameters
CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10)
AS
SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode
GO;

EXEC SelectAllCustomers @City = 'London', @PostalCode = 'WA1 1DP';
*/


 

And

mysql -uroot -p
show databases;
use <dbname>;
show tables;

From How do I import an SQL file using the command line in MySQL?

mysql -u username -p database_name < file.sql

So

$ mysql -uroot -p < ~/Documents/SQL/Northwind.MySQL5.sql 
$ mysql -uroot -p < ~/Documents/SQL/mywind-master/northwind.sql

To allow insert into with fields with empty values, update /etc/my.cnf, as per this answer.

#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
sql_mode=NO_ENGINE_SUBSTITUTION

However, this doesn’t fix the autoincrement

1 row(s) affected, 1 warning(s): 1364 Field 'CustomerID' doesn't have a default value

and then upon a duplication of the insert into

Error Code: 1062. Duplicate entry '' for key 'customers.PRIMARY'

For error 1364, see this answer.

For auto increment try:

-- https://stackoverflow.com/a/42941784/4424636
-- ALTER TABLE Customers MODIFY CustomerID int NOT NULL AUTO_INCREMENT;
-- https://stackoverflow.com/a/55592675/4424636
-- ALTER TABLE `Customers`  CHANGE COLUMN `CustomerID` `CustomerID` INT(11) NOT NULL AUTO_INCREMENT ;
-- https://stackoverflow.com/a/34293778/4424636
-- ALTER TABLE `Customers` CHANGE COLUMN `CustomerID` `CustomerID` INT(11) NOT NULL DEFAULT 0 ;

But they all gave me this error:

Error Code: 3780. Referencing column 'CustomerID' and referenced column 'CustomerID' in foreign key constraint 'FK_CustomerCustomerDemo_Customers' are incompatible.

 

Useful links

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s