What is a database?

Xoops currently only supports SQL databases, so lets have a short but detailed look at what it's all about ... In general terms, “SQL database” is the common name for Relational Database Management System (RDMS). For some systems, “database” also refers to a group of tables, data, and configuration information that are inherently separate from other, similar constructs. In this case, each SQL database installation can consist of several databases. In other systems, these are referred to as table spaces. A table is a database construct that consists of named columns containing rows of data. Usually tables are constructed to contain related information, and several tables can be created within the same database or table space. A column within a table holds data of one type and should be named something that represents the data. For example, a column called “User” should contain user entries in every row. This premise is what allows a relational database query to return consistent results. A field is the data (or place to hold data) from one row, in one column. Alternatively, a data set can be data from multiple rows and multiple columns, and it generally describes all of the data in your database or table. A result set is the data that is returned from a database query; it can describe anything from a single field to all the data contained in your database. A database query describes instructions sent to the database in SQL that request some kind of function to be performed on your data set or database. Now let's look at the basic types of database queries that are focused on manipulating data within a database. All examples are in standard SQL, and these basic functions should translate to almost any environment. Data query types There are four basic types of data queries in SQL: * SELECT: This statement asks the database to return a specified result set of data; you use this to retrieve information that is stored in your database. * INSERT: You use this type of statement to add a new row of data into a table. * UPDATE: This changes existing data in your database. * DELETE: This removes rows of data from your database. Each of these statements has various qualifiers and functions that you can use to define the data set you would like to affect and that control the way the result set is returned. The SELECT statement has the most options. There are a variety of alternate query types used in conjunction with SELECT, such as JOIN and UNION. For now, we’ll just focus on basic usage. Use the SELECT statement to retrieve stored information To get information stored in the database, you use SELECT. The basic function is limited to a single table, although there are constructs for other scopes. To return all the rows of data for particular columns, you use this statement: SELECT column1, column2 FROM table_name; Alternatively, you can get all the columns from a table by using the wildcard, “*”: SELECT * FROM table_name; While this is useful if you’re planning on parsing the result set in your code, you can further limit it with the handy WHERE clause, which allows you to define a certain condition for selecting data. The following query will return all columns from all rows where “column1” contains the value "3." SELECT * FROM table_name WHERE column1 = 3; Besides the “=” (equal to) condition, you may use any of the following: Table A = Equal <> Not Equal > Greater Than < Less Than >= Greater Than Or Equal To <= Less Than Or Equal To SQL Conditional Statements Additionally, you can use the statements BETWEEN and LIKE for comparison with the WHERE clause, and any combination of comparisons with the operators AND and OR. Note that the OR statement is inclusive. Here’s an example combining all of these concepts: SELECT * FROM table_name WHERE ((Age < 18) AND (LastName BETWEEN ‘Carnuke’ AND ‘Marcan’)) OR Document LIKE ‘%SmartFAQ%’; In English, this statement reads “Select all columns from table_name where either the Age column has a value less than 18 and the LastName column has a value alphabetically between and including ‘Carnuke’ and ‘Marcan’ or the Document column value contains the word ‘SmartFAQl’.” Use the INSERT statement for new rows of data Use the INSERT statement to create a new row of data. While you don’t have to populate every field in the row, if you want to put a value into a row where a field is empty, you must use UPDATE. INSERT syntax is as follows: INSERT INTO table_name (column1, column2, column3) VALUES (‘data1’, ‘data2’, ‘data3’); If you’re going to insert all the values in the same order that the columns of the table exist, you don’t have to specify column names, although for readability this is generally preferred. In addition, if you list the columns, it is not necessary to include them in the order they appear in the database, as long as the values you list correlate. You do not have to list all of the columns if you aren’t entering information for them. Once you have data in your database, changing it is very similar. The UPDATE statement and the WHERE clause UPDATE is used to change an existing value or empty field in a row, so it must both match an existing data set and provide acceptable values. Unless you truly want to change the values in all rows, you must use the WHERE clause. UPDATE table_name SET column1 = ‘data1’, column2 = ‘data2’ WHERE column3 = ‘data3’; You are free to match WHERE to any column, including one that you are changing. This is helpful when you’re looking to change one specific value to another: UPDATE table_name SET FirstName = ‘Horacio’ WHERE FirstName = ‘Horacio’ AND LastName = ‘Salazar’; Be careful! The DELETE statement zaps whole rows The DELETE statement will erase an entire row from a table in your database. If you want to delete just a single field, you should use the UPDATE statement to change it to some value that represents NULL to your application. Be careful to limit the scope of your DELETE statement with a WHERE clause, or you run the risk of emptying your entire table. DELETE FROM table_name WHERE column1 = ‘data1’; Once a row has been removed from your database, it is gone, so generally speaking it may be desirable to include a column called “IsActive,” or something similar, that you can change to a zero to represent disabled data. You should only use DELETE when you are absolutely sure you will no longer need the affected information. Now you know the basics of SQL database commands SQL is the language of databases, and here we have reviewed the most basic command statements used in data queries. There are a number of basic concepts that weren’t covered, such as SUM and COUNT, but the few commands listed above should get you up and running. For a safe and easy way to experiment and learn further why not download and install XSAS and install it on your own computer. You get a complete installation of Xoops and access to phpMyAdmin where you can play and learn without fear of damaging your production site. For more information and learning please refer to SQLCOURSE See also this FAQ here

This Q&A was found on XOOPS Web Application System : https://xoops.org/modules/smartfaq/faq.php?faqid=68