Recently I wanted to refresh my SQL skills and needed a basic database setup on my Macbook Air. I remember from my school days that we could install oracle 6/7 free versions for personal use. Apparently oracle no longer offers free versions. Instead it(Oracle) now gives us
mysql which is essentially the free one thereby eventually leading me to refresh not just "my sql" but "mysql" skills as well.
Download mysql server community edition from MySQL website
To run the SQL developer, you will need java on your machine. You can download JDK from Oracle
Get it from Oracle. You will need a free oracle account to register and download java and sql developer.
Now that we have all the required software, we need to connect our SQL Developer instance to mysql instance. By default SQL dev supports connecting Oracle DB. YOu just need to provide connection details. But for
mysql you need to add support.
Download the mysql connector from Oracle-Base
Perform the steps as mentioned in the article. For easy access, I copied the steps here as well.
Download the latest "JDBC Driver for MySQL (Connector/J)" from here. Click the "Download" link next to the "JDBC Driver for MySQL (Connector/J)", then select the platform independent version and download the zip file.
Unzip the connector. The resulting directory contains a "mysql-connector-java-8.0.23.jar" file.
Open SQL Developer and navigate to "Tools > Preferences > Database > Third Party JDBC Driver".
Click the "Add Entry..." button and highlight the "mysql-connector-java-8.0.23.jar" file and click the "Select" button. You may need to place the driver file in
.sqldeveloperdirectory since it might not have access to other directories. You can always change the access from preferences though.
Click the "OK" button to exit the "Preferences" dialog.
When you create a new connection, the "Database Type" dropdown includes a MySQL option. On older versions of SQL Developer this used to appear as a tab. Enter the connection details and test the connection.
Connect SQL Developer
Now we are ready to connect. Create a new connection and provide below details.
- username - root
- password(as given while setting up mysql)
- connection URL and port(by default localhost:3306)
Just some helpful text from the article
If the user chooses to not start MySQL during startup, then either launchctl from the command line should be used or start MySQL by clicking "Start" with the help of the MySQL preference pane.
Once you are connected, you can now create new sql worksheets and start working with mysql. Do note that not all features are supported. You will find the commit button disabled in SQL Developer.
And a few commands also differ like creating and selecting the databases.
MySQL command line
By default mysql binaries are not added to PATH. So you won't be able to run
mysql in the terminal from anywhere. You could navigate to the mysql folder and run mysql command line from there or you can add it to your PATH.
Goto mysql folder
Execute mysql, enter root password you entered while installing mysql setup
sudo ./mysql -u root -p
Related info available on stackoverflow question here.
Data setup for practice
If you are new to SQL and would like to setup some fake data for practice, read this section.
In mysql you need to create individual database before creating any tables.
Create a database called order_system.
CREATE DATABASE ORDER_SYSTEM;
Select the database to perform operations on it.
Create some tables
create table orders (orderId INT NOT NULL, customerId int, orderDate date); create table customers (customerId INT NOT NULL, customerName VARCHAR(100), contactName VARCHAR(100), country VARCHAR(50));
Insert some data
insert into orders values(1, 4567, CURDATE()); insert into orders values(2, 8887, CURDATE()); insert into orders values(3, 8761, CURDATE()); insert into customers values(4567, 'Gabbar', 'email@example.com', 'IN'); insert into customers values(1234, 'Bhola', 'firstname.lastname@example.org', 'US');
Select some data
select * from orders where orderid = 2;
Use joins to combine data from multiple tables
select orderid, orders.customerId, orderdate, customername from orders join customers on orders.customerid = customers.customerid;
Just a point to note that mysql does not supports full outer join. More info here.
Possibly because its not required as much as other features as pointed out by people.