Jump to content

SQL vs MySQL - any difference?

Merkey

Hi, so I have been programming in C# over past 4 years and now I heard about sql. From what I understand, it is used to create databases, but what are MySQL, SQLite, T-SQL, postgre SQL, mySQLi?

Link to comment
Share on other sites

Link to post
Share on other sites

I just yesterday started my course in databases.

 

As far as I understood, SQL is the language and MySQL, .... is the software which uses this language. Just like programs use C#

 

I am about to go to the library and start learning about SQL, so please slam this comment into the ground if I wrote wrong information but be lenient because it was my first university lecture :)

Link to comment
Share on other sites

Link to post
Share on other sites

SQL (AFAIK) is the language used. MySQL is an application using SQL that needs a server which is hosting the MySQL database. SQLite for example does not require a server.

My Rig: AMD Ryzen 5800x3D | Scythe Fuma 2 | RX6600XT Red Devil | B550M Steel Legend | Fury Renegade 32GB 3600MTs | 980 Pro Gen4 - RAID0 - Kingston A400 480GB x2 RAID1 - Seagate Barracuda 1TB x2 | Fractal Design Integra M 650W | InWin 103 | Mic. - SM57 | Headphones - Sony MDR-1A | Keyboard - Roccat Vulcan 100 AIMO | Mouse - Steelseries Rival 310 | Monitor - Dell S3422DWG

Link to comment
Share on other sites

Link to post
Share on other sites

SQL is short for structured query language.

You have MS SQL made by Microsoft, MySQL (the commercial version owned by oracle or MariaDB which is the open source MySQL) , Postgress SQL, SQLite , Oracle databases etc each has some benefits and some downsides / tradeoffs.

 

SQLite is a lightweight SQL database system which stores everything required to access and query a  database in a single file, so it's great for tiny applications, no need to install separate sql server, very portable etc. For reference, it's used by Firefox to store last accessed urls, bookmarks and so on.

MS SQL is good but if you want to do something commercial you have to pay money to microsoft, licenses etc

MySQL/MariaDB is good, but the often used MyISAM tables trade off reliability  for speed (not transactional, but have fast full text search, stuff like that), the innodb format tables solve that but they're tiny bit slower

Postgress SQL is the more serious  brother of mysql, fully transactional, has a better reputation but it's a bit slower than mysql

 

and so on..

 

mysqli is a sort of different way to talk to the mysql server , with support for more modern things that weren't as easy to work with plain mysql way

 

Link to comment
Share on other sites

Link to post
Share on other sites

@mariushm So all of teese MySQL, SQLite... are programs used to create databases with SQL language, or they are already databases by themselves and you customize them? On MySQL website they say it is a version of open source database, so it is running on my PC? Btw thanks for that detailed explanation :)

Link to comment
Share on other sites

Link to post
Share on other sites

3 hours ago, -iSynthesis said:

MySQL is an application using SQL that needs a server which is hosting the MySQL database.

So if I download it on my PC, MySQL would be a database running on server(my PC) and I can programm it with SQL?

Link to comment
Share on other sites

Link to post
Share on other sites

Traditionally, they are database servers ( a standalone application that runs like a web server), and on Windows it usually runs as a Windows service.

Your application connects to the database server through network connection or pipes or whatever and sends a command in the SQL language and the server software replies with the result of the command.

For example your application says "USE databasename; SELECT * from databasename.tablename WHERE tablename.age=15;" and the database server software processes the query and knows that it has to open the database called "databasename" , then look up in the table "tablename" from that database and return all the columns (because I used * which means all columns)  in the rows that have a value 15 in the column called "age"

The database server software takes care of how data is stored physically on the disk and maintain the relationship between tables of a database based on specific constrains you may set - most database systems use one file for each table and the definition of the database (names of each table , the columns and data types of each column in a table) are either saved as a separate file or files in a folder somewhere on disk, or they're actually stored in a "special" database.

Your application only needs to be aware of the SQL language and maybe some differences between various database servers (for example MS SQL may use some keywords while MySQL may use another keyword to do the same thing) anbd doesn't have to worry about where the data is actually saved, updated, inserted into tables. .. the database software takes care of that.

 

SQLite is a sort of special cornflake .. all the "database management and stuff" is packed into a single DLL file that can be loaded by your application so your application doesn't have to install a separate database server, doesn't have to connect to everything.  The databases are also cleverly stored into single files and data is appended to those files when needed, instead of having multiple files for each table, separate files for the definition of the database and so on.

Basically your application just loads the library and says "use database stored in file x.sqlite" , then calls a function where you simply pass a SQL string like "select * from tablename where x=something and y=somethingelse" and the function returns the results.

 

 

Link to comment
Share on other sites

Link to post
Share on other sites

SQL (called sequel) [Yes this is JUST the language but in discussions is commonplace to interchange this for MS-SQL or SQL Server] was created and is generally considered Microsoft's proprietary server. MySQL (called My Sequel) is just another type of server that is open sourced. They effectively use the same structured query language (lol -> SQL get it xD ). Generaly speaking MySQLi and postgresSQL are iterations that simply expand on the feature set that is native to MySQL. The basics are the same but its the nuance between them that lends to the "differences".

 

Hope that helps!

-Luke

"Talk is cheap. Show me the code."

Link to comment
Share on other sites

Link to post
Share on other sites

SQL is a standard language for relational database management system (RDBMS).

 

MySQL is an open source RDBMS with its own dialect of the language controlled by Oracle.

PostgreSQL is an open source RDBMS with its own dialect of the language.

Sql Server is Microsoft's RDBMS and using the T-SQL proprietary dialect of SQL.

 

SQLite has a SQL dialect but it is flat file based. I think its useful for development or a program that needs a local, light weight database; however, it wouldn't be useful for concurrent users.


When you get started, download a sample database. Sql Server has Adventure Works sample database.

 

When developing you want to use the standard SQL as much as possible. Don't pull out the nifty features out of that particular implementation unless you have to. It makes your code and knowledge more transferable.

Link to comment
Share on other sites

Link to post
Share on other sites

4 hours ago, Merkey said:

So if I download it on my PC, MySQL would be a database running on server(my PC) and I can programm it with SQL?

You need a Server hosting it and can then make SQL Querys (e.g. through java or other programs). 

e.g. 

SELECT VorlNr, Titel
FROM Vorlesung
WHERE Titel = 'ET';

 

My Rig: AMD Ryzen 5800x3D | Scythe Fuma 2 | RX6600XT Red Devil | B550M Steel Legend | Fury Renegade 32GB 3600MTs | 980 Pro Gen4 - RAID0 - Kingston A400 480GB x2 RAID1 - Seagate Barracuda 1TB x2 | Fractal Design Integra M 650W | InWin 103 | Mic. - SM57 | Headphones - Sony MDR-1A | Keyboard - Roccat Vulcan 100 AIMO | Mouse - Steelseries Rival 310 | Monitor - Dell S3422DWG

Link to comment
Share on other sites

Link to post
Share on other sites

7 hours ago, Merkey said:

So if I download it on my PC, MySQL would be a database running on server(my PC) and I can programm it with SQL?

For Windows machines:

 

1. Download and install XAMPP or WAMP (XAMPP is my pref')

2. Once installed you will have Apache, PHP & MySQL installed (typical installation)

3. When server is running (make sure to start apache and mysql services from the xampp window) you can type 127.0.0.1 or localhost in your web browser to reach the web root folder. Here you will have links to PHPMyAdmin which is the standard MySQL web based database management software (PHP).

5. Web root will be in your installation folder of XAMPP / htdocs (make folders for your projects in this folder and browse them via the web browser)

6. For Visual Studio C# VB etc applications just google "C# MySQL connection string" or use visual studio database connection features, for PHP just google "PHP MySQL connection string" and you will be good to go in about 5 minutes.

7. If you want a more professional tool for working with MySQL which is FREE, download and install MySQL Workbench (as good as if not as good as visual database design with SQL Server tools)

 

8. Once you get past simple CRUD functionality, Google "WebServices [language of choice]" and "RESTFUL or REST WebServices"

9. Make sure you never store unencrypted passwords in the database (Google [language your using] password encryption

10. Make sure your not vulnerable to SQL Injection (PDO & MySQLi have methods of handling this, parameter binding etc)

11. Google "Relational Database Design"

12. Google "MySQL Triggers"

13. Google "MySQL Stored Procedures"

14. Google "ORM"

--- on and on and on....

 

 

For Linux - just google "linux [distro your using] lamp install", will give you a list of "sudo apt-get install xxxx" commands to type into terminal

 

 

 

General / Basic SQL for beginners can be found here:

https://www.w3schools.com/sql/sql_select.asp

Link to comment
Share on other sites

Link to post
Share on other sites

@Merkey You don't actually use SQL for programming. SQL is just the language which gives you the ability to store, retrieve, delete and update data which is stored in your database tables. SQL is the standard query language for relation database systems(like MySQL). You can just connect to the server and send him SQL commands, these are getting executed and the server sends the data. 

Udacity offers a free course on relational database systems (in this course you will use Python for programming).
And since this was also part of your question: MySQLi is one of the PHP modules used for communication with a MySQL server.

Link to comment
Share on other sites

Link to post
Share on other sites

SQL is the language your code in written in, MySQL is just one of many clients that can convert your code into a easy user inferface. Basically, its a software.

 

Alternatives to MySQL:

 

  • SQuirreL
  • Firebird
  • DBeaver

MySQL is the most commonly used today.

Link to comment
Share on other sites

Link to post
Share on other sites

On 2017-4-25 at 0:45 PM, Teddy07 said:

I just yesterday started my course in databases.

 

As far as I understood, SQL is the language and MySQL, .... is the software which uses this language. Just like programs use C#

 

I am about to go to the library and start learning about SQL, so please slam this comment into the ground if I wrote wrong information but be lenient because it was my first university lecture :)

 

On 2017-4-25 at 1:03 PM, -iSynthesis said:

SQL (AFAIK) is the language used. MySQL is an application using SQL that needs a server which is hosting the MySQL database. SQLite for example does not require a server.

Note: there are actually some subtle differences in the syntax that you need to watch out for, in particular when it comes to case sensitivity and support for some keywords.

 

But, yeah language wise its pretty much SQL. 

If you want to reply back to me or someone else USE THE QUOTE BUTTON!                                                      
Pascal laptops guide

Link to comment
Share on other sites

Link to post
Share on other sites

  • 1 year later...
On 4/25/2017 at 4:23 PM, Merkey said:

So if I download it on my PC, MySQL would be a database running on server(my PC) and I can programm it with SQL?

For Windows machines:

 

1. Download and install XAMPP or WAMP (XAMPP is my pref')

2. Once installed you will have Apache, PHP & MySQL installed (typical installation)

3. When server is running (make sure to start apache and mysql services from the xampp window) you can type 127.0.0.1 or localhost in your web browser to reach the web root folder. Here you will have links to PHPMyAdmin which is the standard MySQL web based database management software (PHP).

5. Web root will be in your installation folder of XAMPP / htdocs (make folders for your projects in this folder and browse them via the web browser)

6. For Visual Studio C# VB etc applications just google "C# MySQL connection string" or use visual studio database connection features, for PHP just google "PHP MySQL connection string" and you will be good to go in about 5 minutes.

7. If you want a more professional tool for working with MySQL which is FREE, download and install MySQL Workbench (as good as if not as good as visual database design with SQL Server tools)

 

8. Once you get past simple CRUD functionality, Google "WebServices [language of choice]" and "RESTFUL or REST WebServices"

9. Make sure you never store unencrypted passwords in the database (Google [language your using] password encryption

10. Make sure your not vulnerable to SQL Injection (PDO & MySQLi have methods of handling this, parameter binding etc)

11. Google "Relational Database Design"

12. Google "MySQL Triggers"

13. Google "MySQL Stored Procedures"

14. Google "ORM"

--- on and on and on....

 

 

For Linux - just google "linux [distro your using] lamp install", will give you a list of "sudo apt-get install xxxx" commands to type into terminal

Edited by colonel_mortis
Remove ad
Link to comment
Share on other sites

Link to post
Share on other sites

SQL is a language for relational databases. MySQL and others are sql database

mangement system which are softwares that manages database in the SQL language. 

Sudo make me a sandwich 

Link to comment
Share on other sites

Link to post
Share on other sites

If you want to learn and use SQL as simple as possible i suggest SQLite.

 

It's simply one DLL you add to your project reference and the database itself is a single file you can put anywhere on your computer. Ready to code in 5 minute tops.

 

You can also download a SQLite Viewer/Workbench if you want a GUI view (a couple one exist get the one you find the interface the most intuitive to you). You open the database file in the viewer and you and you can easily create tables and add record by hands to jump start your learning. Once you added few tables and data you can get back to C# and start create a SQLite connection object and try to connect and make simple queries.

 

Edit*

For aa simple GUI on SQLite I suggest SQLite Studio which can be found here :

https://sqlitestudio.pl/index.rvt

 

If you download that GUI tool it already include the sqlite3.dll that you need to add to your reference to start working with it.

Link to comment
Share on other sites

Link to post
Share on other sites

On 4/25/2017 at 12:51 PM, prysm said:

SQLite has a SQL dialect but it is flat file based. I think its useful for development or a program that needs a local, light weight database; however, it wouldn't be useful for concurrent users.

this is a unfortunate limitation of SQLite. which i love very much. i had a project though that had multiple client locations and because of the IT department had to be run entirely serverless. SQLite worked but the db file was synced over the network file server. our work around was to open the file, run the command, and then close the connection. this mostly worked. file system and how the OS handles files plays a big role in concurrency. one thing we found was reading the file actually wasnt the issue. it was having more than one person trying to write to it.

Link to comment
Share on other sites

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×