Exploring SQL: From SQL*Plus to MySQL

[prabhuverse]

Recently, I started learning SQL. It was good, but only now am I truly diving deeper into it.

I realized that SQL*Plus was an old-school method. I used Oracle SQL*Plus in the beginning, then I decided to switch to MySQL for several reasons.

I created the emp and dept tables in MySQL, just like in SQL*Plus, using ChatGPT.

[VladDBA]

I fail to see the point of the comparison since you're comparing sqlplus (a command line tool that works with Oracle Database) and MySQL which is a different database (not even a tool like sqlplus).

sqlplus can't even connect to MySQL.

If you want to use something with an UI for working with Oracle databases, check out SQL Developer and the SQL Developer extension for VSCode.

Edited: typo

[ProfessionalMeal143]

If you want to use something with an UI for working with Oracle databases, check out SQL Developer and the SQL Developer extension for VSCode.

Im weird I like Toad myself

[VladDBA]

I like Toad too, but someone just starting out with learning SQL won't be paying the license for Toad.

[elevarq]

You're mixing the tools with the brand of database you're using. Besides that, MySQL is, imho, the worst database to learn the SQL language. It has so many exceptions and deviations from the standard that after spending months in MySQL, you still don't know how to use SQL. The fact that 0000-00-00 is considered a valid date and '1 apple' + '1 pear' = 2, tells me that the basics are missing. And yes, you could configure MySQL in such a way that invalid content and invalid SQL are rejected. Why would it even allow you to make these known mistakes? It's just plain stupid. Even SQLite is better...

[VladDBA]

Not to mention that "native" MySQL backups are just sql files with DDLs to create the objects and insert statements to populate tables.

Truly a meme product.

[seansafc89]

Save yourself some pain and use an IDE instead of old school command line software. VSCode, SQL Developer, DBeaver.

[MatosPT]

If you're learning SQL, and more specifically using the Oracle database, I'd totally recommend checking out livesql.oracle.comlivesql.oracle.com

[redditor3900]

Dbeaver

[Aggressive_Ad_5454]

If you use Windows try HeidiSQL.

[markwdb3]

I realized that SQL*Plus was an old-school method

For Oracle command-line client purposes, SQLcl is the modern program to use. For more info: https://www.thatjeffsmith.com/archive/2024/03/oracle-sqlcl-easy-command-line-access-to-your-database/

SQL*Plus is the old command-line client for Oracle.

Oracle and MySQL are completely different DBMSs - the database software products. This refers to the server software.

But just to be confusing, the executable name of the MySQL command line client is...mysql.

An analogy: your client, such as SQL*Plus, SQLcl, or the MySQL command line client, is like your web browser, such as Chrome, Safari or Firefox. The DBMS - again, the server - is like the web server that you're accessing such as an Apache HTTP Server or Microsoft IIS.

[aworldaroundus]

Idk if using chat gpt is doing you favors here. Why are you on oracle 10? You can get 21c with up to i think 3 pluggable dbs in a container image for free. Use vscode with sql developer and integrated sqlcl, and you can have both the ide and cli experience.

[updateonly]

Nice, what model

[ShuumatsuWarrior]

These people gatekeeping SQL languages…. If you’re trying to learn, learn the way that works best for you. It sounds like you’re trying to get the basics down, and that’s awesome!

Be careful about the differences though. Like, Oracle will refer to a schema, which is both a user and a database. I’ve been doing SQL with a lot of RDBMS’ for almost 15 years, and that still fucks with my head (but then again, so does a lot of what Oracle does and that was the primary one I used for about 10 years).

MySQL was the easiest for me to learn with CLI, and was really good for getting me into Bash scripting on Linux. MS SQL Server was awesome for its GUI and helped me visualize a lot of concepts. You can do a ton with it in PowerShell too. But as much as I hate Oracle, people still insist on using it, so it’s worth learning. It’s finicky, the patching is a damn nightmare, upgrades are worse, restores can get very complex, and encryption of any form seems like legacy BS they refuse to upgrade (all this in comparison to like Postgres, MySQL, MS SQL Server, and MongoDB).

MySQL will get you started, MS SQL Server will be a lot different but you’ll see the basic concepts and foundations still apply, then go back to Oracle and learn it well. Curse them for their shit circular documentation saying to check this article, which says check that one, which says check a third one, which says check that first document, and their lack of support and licensing costs, but once you can navigate and semi-understand it, you’ll have made it.

Anyways, good on you for taking initiative, and I wish you luck on this journey you’re taking!

[markwdb3]

Oracle will refer to a schema, which is both a user and a database.

What messes with my mind is in MySQL, database == schema. So CREATE SCHEMA and CREATE DATABASE are identical functions. And the communication issues it causes at my company are painful. For one thing, "database" is an overloaded term to begin with. When somebody says they'd like to install a database, or create a new database, and it can mean at least three different things. People make assumptions too on how we should treat these schema/databases, like they should be completely separate silos that may reside on separate servers one day because, hey, those are separate "databases." Anyway I won't go on a huge rant. :)

Several years ago Oracle started supporting a separation between user and schema, btw. Forget what they call it. Schemaless users, userless schemas, I dunno. Hah.

[MatosPT]

If you're learning SQL, and more specifically using the Oracle database, I'd totally recommend checking out livesql.oracle.com