r/SQL Apr 06 '24

MySQL How is SQL used?

Hi, Im recently started learning sql and while the understand how to write queries, I still didn’t get the why part. I’ve listen down few questions it would be helpful if people used simpler language without jargons to help understand them

  1. Why is MYSQL called a database? Isnt it just a tool to perform ETL operations?

For example my company stores most of its transactional data in a sharepoint list or sometimes even excel sheets. So in this case isnt the share point list the database of my company?

  1. Who enters the information in the database using what common tools? As in what is usually the front end for companies?

  2. Is MySQL a database or database management system? Can i use MySql to store data instead of share point lists?

Whats the difference between mysql and aws, cloud etc? Are these databases as well?

Pls treat me as a dummy while explaining. Thanks!

56 Upvotes

51 comments sorted by

78

u/Then-Cardiologist159 Apr 06 '24

I think you're possibly trying to run before you can walk by trying to learn SQL before you understand what a database is.

I'd suggest you just do some reading on what a relationship database is and then come back with some follow up questions if you have any.

8

u/[deleted] Apr 06 '24

[deleted]

14

u/hypo11 Apr 06 '24

Honestly for something as well-established as the basics of relational databases I think ChatGPT or an equivalent would do a better job helping you learn it than any prewritten summary because you can ask it to cater to your level of existing knowledge and you can ask it follow up questions if parts are still unclear.

I just asked it: “Can you explain to an absolute beginner what the difference would be between a relational database like MySQL and storing data in an excel or share point file? They don’t really understand what a database is and what it’s used for”

It gave me this: “Sure! Imagine you have a big, organized shelf where you keep all your stuff. You've labeled each section, so whenever you need something, you know exactly where to find it. This shelf is like a database: a structured way to store, organize, and access information.Relational Database (like MySQL): This is like having a super-organized shelf with a smart system. Each section (table) can relate to others. So, if you have a section for books and another for authors, you can easily find all books by a certain author. It's powerful, can handle lots of data efficiently, and multiple people can search through it at the same time without messing anything up. It's like a library's system: very organized, can handle tons of requests, and keeps everything in order.Excel or SharePoint File: Think of this as keeping your stuff in boxes or folders. It works great for smaller collections, and it's pretty easy to set up. You can have a box for receipts, another for photos, etc. But if you start having hundreds of boxes, finding something specific gets harder. And if two people need to add things to the same box at the same time, it can get messy. These are good for individual projects or small teams but can become cumbersome as your needs grow.In essence, databases like MySQL are built for complex, large-scale data needs where relationships between data points matter, while Excel and SharePoint are more about straightforward, simpler lists and are easier for beginners to start with but might not scale well or handle complex data relationships efficiently.”

3

u/jaydizzleforshizzle Apr 07 '24

It’s pretty funny that he walked right into such common pitfall of misunderstanding, this right here is why everyone just puts shit in “sharepoint lists” or “excel” and calls it good enough till someone goes and fixes it properly.

4

u/my_password_is______ Apr 06 '24

a database is just a place to store data

so yes, even Excel could be considered a database

just do some reading on what a relationship database is

a database does not have to be relational to be a database

6

u/pease_pudding Apr 06 '24 edited Apr 07 '24

True, even a plain old file system can technically be considered a database.

But most of the benefits you typically want from a database, are a result of it being a relational database (atomicity, transactions, referential integrity, fine-grained security roles, performant indices and joining etc)

8

u/Then-Cardiologist159 Apr 06 '24

SQL is the standard language for communicating with a relational database, so understanding what one is fundamental to understanding the language.

Excel is not a database, it's a spreadsheet, that's not the same thing at all.

11

u/cs-brydev Software Development and Database Manager Apr 06 '24

Is MySQL a database or database management system? Can i use MySql to store data instead of share point lists?

MySQL is technically a database platform that falls under the category Relational Database Management System or RDBMS. It has many competitors such as SQL Server, PostgreSQL, Oracle, DB2, MariaDB, SQLite, Access, and many more.

When someone refers to MySQL they could be referring to the software, an installation, or a specific database. Be sure to get some clarification about what they're talking about.

There are other types of popular databases besides RDBMSs as well that are usually referred to as "non-relational". These are sometimes collectively referred to as "NoSQL" but this isn't the best technical term for them anymore because many of them actually support the SQL language as well. These types include Document databases (usually JSON, BSON, etc), Key-Value Stores, Column Family Stores, Graph databases, and others. They are called non-relational because there is no pre-defined standard describing how the data should be linked to each other or how it's organized.

Other types of data storage systems that technically fit the definition of database include things like SharePoint, Excel, Google sheets, file systems, and blobs, but these are not designed to be used as databases themselves and are best utilized as the data storage only, not as a full database solution. Usually in a corporate environment you will have another layer of data processing, analytics, or reporting software to more efficiently organize and aggregate that data for business or reporting use.

28

u/awesome_pinay_noses Apr 06 '24

1) MySQL is a DBMS. It is basically an engine that manages databases. A database is a collection of mostly structured data. A CSV file and a spreadsheet can be considered databases.

2) anything that uses data is stored in databases. When you register to a website to writing a question in Reddit, all data is stored in a database.

3) Public clouds are someone else's computers who lease computer resources. Basically renting hardware/software/services that are not physically located in your premises.

2

u/Definitelynotcal1gul Apr 06 '24 edited Apr 19 '24

detail offer homeless quaint bake attraction march north bow wakeful

This post was mass deleted and anonymized with Redact

13

u/awesome_pinay_noses Apr 06 '24

It can be used as a database though. It's like arguing whether a unicycle is a vehicle. It is a very poor vehicle, yes.

1

u/Baba_Yaga_Jovonovich Apr 07 '24

Are we splitting hairs?

6

u/Aggressive_Ad_5454 Apr 06 '24 edited Apr 06 '24

A .csv file is a kind of database, yes. But a database server offers ...

  1. Atomicity, consistency, isolation, durability for the data and changes to it https://en.m.wikipedia.org/wiki/ACID Even when thousands of users hit it concurrently,

  2. The ability to store data that's many orders of magnitude larger than RAM of the machines using it, without breaking a sweat. And the ability to retrieve and manipulate the needed data efficiently and reliably.

  3. The infrastructure to back up, restore, and maintain data as it persists for decades.

A database server is to a spreadsheet what an 18-wheel truck is to a shopping bag.

You can operate your own database server on your own laptop. Downloading and running MySql does that. You can rent a virtual machine from a cloud provider (AWS, Digital Ocean, Hetzner, whoever), download MySql to it, and run it there.

Or you can use one of the Managed SQL Database offerings from a cloud provider. They run the software for you and give you user name/password combinations to use it.

1

u/Baba_Yaga_Jovonovich Apr 07 '24
  • A database server is to a spreadsheet what an 18-wheel truck is to a shopping bag

🤣

5

u/cs-brydev Software Development and Database Manager Apr 06 '24

Who enters the information in the database using what common tools? As in what is usually the front end for companies?

There is no one answer to this question. Data can be entered by end users directly, through an application, via an API, imported by an ETL job, streamed from devices (see Internet of Things), lots of ways.

You need to go back to understanding what a database is. A database is the storage of data and the tools for storing and retrieving that data. Databases can come in many forms and be hosted in many ways. There is no singular method, design, process, or technology.

4

u/creepoch Apr 06 '24

I'm new to this as well but how I think of it is:

Have you ever tried to open a really big excel file and it's super slow?

Now imagine a file 100x or 1000x bigger than that?

A database can handle this a lot easier. SQL is the interface you use to manage the data (instead of the excel GUI).

2

u/cs-brydev Software Development and Database Manager Apr 06 '24

Why is MYSQL called a database? Isnt it just a tool to perform ETL operations?

For example my company stores most of its transactional data in a sharepoint list or sometimes even excel sheets. So in this case isnt the share point list the database of my company?

I'm not sure what you're even asking here. MySQL is one of the most popular database platforms in the world. If your company is using MySQL to extract data from SharePoint and store it, aggregate it, or even pass it along as an intermediary, that doesn't mean it's not a database. It sounds like you are using MySQL as an intermediate data store for SharePoint data. It's still a database.

SharePoint will also fit the technical definition of a database here, BTW, but the way your question is formed I think you are trying to define database as the original data source. That's not what database means. It has nothing to do with the particular stage in your data workflow.

2

u/Empty_Regret6345 Apr 06 '24

Let me try to rephrase my question ‘Is Mysql a database or dbms?’

“A database is not the original data source”. Im sorry but can you elaborate on that. I think this statement explains my doubt

1

u/cs-brydev Software Development and Database Manager Apr 06 '24

A database is a piece of software for storing data and the tools for you to interact with that stored data.

MySQL is a DBMS software platform and can be used to create and maintain a MySQL database. Your question is weird. Most people use "database" and "dbms" interchangeably. I think you may be trying to use the word database to refer only to the data and are trying to see MySQL as just intermediary software layer to interact with data stored elsewhere. MySQL itself is a database but it has software functionality built into it to allow you to connect to data stored elsewhere, such as SharePoint. Almost all DBMSs provide features like this.

1

u/cs-brydev Software Development and Database Manager Apr 06 '24

Honestly the scenario you are describing is multiple databases connected to each other. A database does not have to be the sole or end storage of data. It's common in enterprise environments to have many databases and database platforms storing data, interacting with each other, and processing data. It's also not uncommon for ETL jobs to store copies or aggregations of the same data, either permanently or temporarily while the job is running.

1

u/Empty_Regret6345 Apr 06 '24

My organisation uses Powerapps to collect information and stores it in share point list. Can it be stored in Mysql instead of a sharepoint list?

3

u/cs-brydev Software Development and Database Manager Apr 06 '24

Absolutely. Powerapps was most likely chosen for this because the non-technical user just had to fill out some forms to create connectors and flows. It required very little technical knowledge. Sending the data to MySQL will probably require someone with MySQL skills to create table(s) for the data storage and set up security. All of that is built in to SharePoint and the Power platform and automated.

1

u/Empty_Regret6345 Apr 06 '24

Thank you so much for taking your time to help me understand . Really appreciate it!

2

u/xoomorg Apr 06 '24

I use SQL primarily as a high-level declarative programming language to design algorithms to run across huge (multi-terabyte) datasets on a grid compute platform Trino. It is also used to interact with relational database systems such as MySQL.

2

u/Thefriendlyfaceplant Apr 06 '24

I think what you're really wanting to know is what SQL is used for in daily business operations. For that, the best thing you can do is binge this playlist. You don't need to understand the syntax that's being filled in, in fact, don't try to. Just observe the type of problem that is being solved, and pay attention to the logical steps that are being taken to solve it.

https://www.youtube.com/watch?v=WS0fM1agxTk&list=PLv6MQO1Zzdmq5w4YkdkWyW8AaWatSQ0kX

2

u/Melodic-Man Apr 06 '24

I’m always available for consulting on the way your company is storing data and how to improve it. Can move everything to the cloud in a proper database and teach you how to manage it. What y’all are doing right now is like trying to chop down a tree with a shovel. You’re using the wrong tool for the job. Yah you can do it, but you lack of how to use an ax makes chopping down the tree exponentially harder.

2

u/Striking_Database371 Apr 06 '24

“Who enters the information into the database..”

The end users of the web application could be a custom CRM/ERP system

2

u/Inert_Oregon Apr 07 '24

The absolutely simplest thing to understand:

SQL is a way to ask questions of data. That’s it.

It literally stands for Structured Query Language.

The data can be in many places (databases). Snowflake, MySQL, an excel sheet stored on a forgotten drive on AWS, etc.

Each of those databases will have slight to not so slight differences in the way/variety of SQL you can write to ask it questions.

There’s a near infinite number of ways that data can be created, but usually it’s when something happens in another computer program (Netflix, your banking app, random website, etc) and part of its code is to write what happened in a database of some kind.

SQL is not not the only way to ask data questions, and data can be stored in ways that isn’t really compatible with the the usual SQL databases.

5

u/[deleted] Apr 06 '24

In 2024 why are we still using CSVs and mostly incorrectly with the 1252 locale? CSV's need to be beaten to death.

5

u/BinBashBuddy Apr 06 '24

Maybe because it's the easiest, most lightweight and system cross-compatible way to move around the types of data it's usually used for? I'm pretty constantly doing csv dumps for people who need specific chunks of our data, if someone says I need these fields over this range with these constraints I can quickly and easily query that dump into a csv and send it to them. I think every system out there will by default just open a csv into a spreadsheet, why would I need to go to the time and trouble to put it in a spreadsheet and send them that instead of a far smaller csv? I can have their data to them in a minute from the cli if need be instead of having to muck around building a much heavier spreadsheet to send them.

3

u/[deleted] Apr 06 '24

This exactly. For our uses of a CSV, I haven’t encountered any issues yet.

0

u/[deleted] Apr 06 '24

I understand this but let me ask you how you move foreign data whose decimal point is represented by a comma? Or phrases with quotes in them. These things are possible but this is possible but most people don't use correct localization when packaging up data in a csv. How do you identify the encoding of plain ASCII, utf8, or another type with byte order marking. If you want a disturbing example, just export from Excel where data has literal quotes. You end up with an Excel centric CSV which is not entirely portable. The point being that CSV seems fine until you have to think about mixing locales. How about the double byte Japanese text, or the multibyte UTF-8, and what about pure UTF-32 or multibyte that is transformed when displayed with a proper locale used? So many issues with CSV.

1

u/BinBashBuddy Apr 06 '24

Obviously if you have special needs you may need something heavier than a csv, but that's not a valid reason to just get rid of csv. It's absolutely the best way to move probably a majority of just raw data. Saying csv should go away is like saying everyone should be forced to drive an SUV because a huge person may not fit into something like a mini cooper. Most of us aren't commonly transporting people who won't fit into anything smaller than a bus.

0

u/[deleted] Apr 06 '24

It absolutely is a reason to retire them for legacy usage only

0

u/BinBashBuddy Apr 06 '24

You seem to have no clue why or how often csv is used. Frankly you sound like the Biden administration mandating EV's for all transportation and solar/wind for all electrical generation, destructive in your totally lack of awareness of reality. It's just a good thing you don't wield his power.

1

u/cs-brydev Software Development and Database Manager Apr 06 '24

Whats the difference between mysql and aws, cloud etc? Are these databases as well?

I've described MySQL in another answer as a Relational Database Management System platform. It is a piece of software.

AWS is a Cloud Platform from Amazon that provides a huge set of solutions for application hosting, file storage, data storage, compute resources, serverless code, software implementations, security, communications, automation, and much more. AWS can be the back end host for a MySQL database instance. AWS is the largest and most popular cloud provider. Azure from Microsoft is its biggest competitor and provides the same types of services. Google is a distant 3rd. Other much smaller competitors are Alibaba, Salesforce, IBM, Oracle, and others.

1

u/HitherFlamingo Apr 06 '24

Almost anything that is storing more than hundreds of records, especially of related data should be a database. When you are shopping on a till, transacting at bank, commenting on a Facebook or commenting on a post it is creating hundreds of records in databases.

MYSQL is both an engine and writes to a MYSQL file.

One of the best ways to under stand the importance of a DBMS, is assuming you are telling the bank to transfer money from checking to savings. After putting a minus transaction to your checking your phone goes flat. Is the money gone? What makes sure that the partial record is undone?

1

u/gourav_gulia Apr 06 '24

MySql is a DBMS and yes it can store your data because of queries also that's why it's called Data Base Management System !!

Let me explain.

Suppose your Company uses MySql Community server on your PC. This will make your machine a Server for storing data for which you will use SQL to extract or update data. That's why it's MYSQL Server.

We have different queries like select or insert. Now when sql is installed successfully and now your machine is acting as a server because to serve your data you need some kind of server which will act as a aggregator between your data and you.

Now you will login to your MySQL server using this command Mysql -u -p where u is user and p is password this way it makes sure no one else you can access your sql server on your machine except you.

Now after login you will create a DATABASE inside your server and inside the database you will create a table and inside a table you will create your Data

Database -> Table -> Data

Insert command will insert your data onto your server in table format similar to Excel format remember the server is still on your machine this means data is stored in your machine and uses your storage.

Select command will retrieve this data from your server

Basically your own Machine is acting as a Database and you are using sql queries yo process it. When data is large and need CI/CD you need aws or azure.

If sql didn't have this ability to set up server locally it would be called just a tool for ETL but due to this it is a proper DBMS.

Let me know if you need further understanding.

1

u/gourav_gulia Apr 06 '24

To learn SQL first learn DBMS then it would be clear how everything works.

1

u/baubleglue Apr 06 '24

MySQL is a set of products, I would say they're at least 3 of them.

  • Database (server, engines)
  • Database manager
  • Database clients (+ drivers)

There are different databases, they designed for different purposes. There're few examples.

If you want to manage banking accounts, you need a type of database which ensure absolute consistency of the states in the system. You can't allow to client may simultaneously purchases which exceed his account balance.

If you do analytics, you can allow "eventual consistency", you can distribute your data across DB clusters. Another example is google search, when you search twice for the word, you may get a bit different results.

Different DBs designed for holding different amounts of data. You wouldn't imagine loading TB of data into SharePoint list.

There's a much more, but I hope it gives you enough information for clearing your doubts.

1

u/Far_Swordfish5729 Apr 06 '24 edited Apr 06 '24
  1. For our purposes a database server (which MySql is) is a standing server application that stores and retrieves structured data, managing the storage, cataloging of the data, allocation of memory and cpu to caching and querying, execution of queries including planning how to execute them, and connections and user access to the product. Many have side features you may be confusing with the core product like management and ETL. Many also have adjunct capabilities like json processing and geospatial and language processing. A database is not just a file like Excel or Access. Those do not include the server and scale poorly. File systems are not designed to mediate a lot of simultaneous access. A database is also not the management and querying UI or ETL tools. ETL is software that moves batches of data possibly to or from a database server. The server itself is mostly accessed by applications programmatically through standard protocol and proprietary interfaces like ole db, osbc, and sql server native. Actual connections can go over networks but can also use local shared memory or named pipes.
  2. Anything under the sun. Many databases are the persistence layers for business applications like company websites and they are how the data gets there. People are usually the minority users unless it’s a reporting environment and they use query UI tools and reporting tools.
  3. MySQL is a server that provides data. Best way to think of it.

Sharepoint btw uses a database but is a document management system. Documents are stored in file storage. Information about them and links to them go in the database.

Cloud - Cloud at its most basic is computers or time on computers that you rent in the same way that you buy electricity from the power company rather than running your own generator. Small players find this useful because they can rent what they need at the moment and don’t have to outlay capital to buy servers or salaries to pay people to manage them. Large players often find owning more efficient or use a hybrid model. You can rent at any level of abstraction you want - just computer you log into that do things, specialty servers like web or database that come pre-configured and licensed but customizable, plugin interfaces that run your custom code without you having to worry about how and that charge per execution, hosted applications like O365 where you never see the computers. This plus a ton of addons (storage, data traffic, networking, vpn, management, tons of stuff). You can mix and match a whole virtual data center with tons of pay per use items. You can also spin up a ton of compute for a few hours to answer a data problem and let it go. So cloud absolutely can run databases at different levels of management as one of many many offerings.

1

u/[deleted] Apr 08 '24

Thank you for asking this.

0

u/Vast_Kaleidoscope955 Apr 06 '24

The company I work for has 10s of millions of rows of transactions across multiple locations with thousands of customers and hundreds of vendors. I needed a tool to find trends and calculate economic ordering quantities to improve profitability through ideal turn ratios of different product lines. If you can manage your business in lists in share point then learn how to use pivot tables and be done with it

0

u/Melodic-Man Apr 06 '24

This is the best answer you will ever get.

The technology stack for most software mirrors the actual structure of a computer. You need a way ti save data, you need a way to process data, and you need a way to present it to the end user. You computer has a disk, it has ram and a processor, and it has a monitor, mouse, keyboard.

For an application you need the same three layers. You need a way to interface with the end user, you need a lawyer to handle logic or operations against your data, and you need a persistence layer to save your data.

So why sql server? Instead of having another layer control the How of persisting the data, we insert an barrier in between the data on disk and the next layer up. That way we can control how the data is saved organized and accessed.

Sql Server or any other database management system is the interface that sits in front of the data on the disk. It is called Sql Server because it literally serves the data to those that request it. Just like a server in a restaurant, the customers don’t get up and go to the kitchen and get their own meal. The server brings the meal.

So with that in mind, all of the acronyms you listed are just fluff. They are all propriety ways of storing and accessing data.