SQL lets you access and manipulate databases with ease and quickness. Therefore, understanding the basics of SQL database programming may make differences for you as a web developer, as you have to frequently encounter with various databases and SQL.
Web programmers understand that SQL (Structured Query Language) is vital to access and manipulate databases – since databases are an inevitable part of our modern web. Moreover, our personalization approaches tend to lead to more interactions with databases in highly complex manners. Therefore, learning the language that can drives our database programming is a must – and this post is a part of giving you a basic understanding of it.
Here are some examples of basic operations we can perform on a database:
- Create tables with predefined columns
- Insert or retrieve desired records from a databases
- Manipulate/update/delete records in a database
- Perform calculations with information in the database
What is RDBMS Databases
There are various kinds of databases, such as MySQL, MS SQL Server, IBM DB2, Oracle, and Microsoft Access. They are all based on tables and records, pretty much like an Excel sheet.
Since PHP has is highly popular for powering websites, MySQL as a highly popular partner of PHP has a huge share of the database driven projects today. MySQL basically often comes hand in hand with PHP, and has a similar importance. Therefore, we will go through the SQL database programming of MySQL, which is relevant for most web developers nowadays. Other SQL variations are similar, but not identical.
In technical terms, MySQL is a relational database – and you may have questions to what “relational” means here: Normally, databases don’t have a single table, but consist of multiple tables. Each table has certain information in a certain schema, and the “relational” means that these tables have relationships with each other. Hence data in one table references data in another table.
Understanding the Table
Tables have rows with data. Each row has the same columns, as defined by the table schema. However, not every row necessarily has data in each column – this entirely depends on the column settings. Usually each row as an id which is used to identify a row when changing or deleting data – and when referencing rows in other tables.
Some Key SQL Commands
In order to interact with database tables we have to select rows of data from the tables. Depending the queries, you can decide to select individual rows with all columns or just single column values. You can perform calculations with the data, to get a result based on some dynamic data. Essentially there is pretty much no limit as to what you can accomplish with MySQL – but there are definitely performance implications to watch out for (both when designing the table schemas and when designing the queries to perform – but unfortunately this blows the scope of this basic introductory article).
To get started with SQL, you should look into how to create your first table, and how to use the SELECT, INSERT, UPDATE and DELETE statements. They are the foundation for pretty much all database related queries you wish to work with in your project.
References & More Reading
No comments yet (leave a comment)