Structured Query Language (SQL) tutorial
Introduction
When you have finished this chapter, you will
- Understand how SQL is used
- Identify course boundaries
- Understand why databases are useful
- Know how to run SQL queries within a MySQL database.
- Know how to set up a database
- Be able to open to a specific database
- Be able to create basic tables within your selected database
- Be able to choose appropriate table column types
- Be able to check table structures
What you may need for this chapter:
- Familiarity with the Windows operating system
This chapter’s task
Today you will locate the important aspects of the editor you will use to learn SQL for MySQL databases.
This chapter then leads you through database selection procedures, as well as commands or procedures to create both databases and their component tables.
What is SQL?
Structured Query Language (SQL) is the scripting language that is used by database developers to interact with relational database management systems (RDBMS). That is, any database that consists of relational tables that are linked in some way can use SQL to
- create and delete the database,
- set up and delete tables,
- enter information and
- retrieve information in a meaningful way.
SQL can be used with any RDBMS such as MySQL, mSQL, PostgresSQL, Oracle, Microsoft SQL Server, Access, Sybase, Ingres and many others. All the important and common SQL statements are supported by these RDBMS. However, each has its own set of proprietary statements and extensions. SQL has the full support of ANSI (American National Standards Institute), which has laid down rules for the language.
SQL can also be used to manage database users, data storage details and the database server. This is the province of the Database Administrator, and will not be considered further within this course.
SQL is a general query language for several different kinds of databases. The SQL tutorial in this course is has been built around MySQL, which is an open source relational database management system available for download at MySQL.com . However, I have generally tried to avoid the commands specific to MySQL in this course.
This course will lead you through SQL syntax for simple operations such as creating and deleting basic databases, tables and queries using one table. Once you have explored these SQL basics you will extend the principles you learnt to develop more sophisticated SQL queries. You will then be shown how to extract information from several related tables using SQL.
This course aims to build a strong foundation in the SQL language. You can then extend this knowledge to the specific RDBMS you plan to use.
Why MySQL ?
Choosing a database system depends on three main factors; the platform on which you work, your finances and what you want to achieve.
I chose MySQL for this course is because MySQL is free for non-commercial use, and therefore available to all students. My SQL is also compliant with ANSI SQL standards and has fewer proprietary SQL commands than many of the other generally-available RDBMS.
What are databases and why do we need them?
In simplest terms, databases are storehouses of data. You probably have an address book that contains the names of your friends and family, and their email addresses. This is technically a database. You can add, update and delete data from this file. If this addressbook is stored on your computer, you could also write a small program to extract, sort and display data on the basis of some search criterion.
Data storage and retrieval has been known since the earliest recorded history. For example, early civilisations charted the seasons over the years, thereby helping with planning the best times to sow or to migrate. The collection of data is important, but only because we can then access and use the data to extract some Information.