Basic SQL statements for beginners


SQL is a computer language for manipulating databases. It includes possibilities for retrieving and updating data, creating and modifying shapes and tables, but also controlling access to data.

SQL

If you are new to SQL (Structured Query Language) commands or need a reminder of some basic commands of this powerful tool, then this article will give you the initial commands.

For the most beginners to say that a database means a collection of formatted data, which can be retrieved through search. This data is essentially like a table with columns and rows. Imagine them as a page in excel where each horizontal bar contains a complete record, divided into columns for easy search and retrieval of only the information you need.

A phone book could be a good example of a database. Imagine in Columns the Names, Address, Telephone and in rows the complete details of each customer.

The handling of such bases is done via the SQL language. The first database management system was RDMBS, developed at MIT in the early 1970s. Since then, Relational Software has undergone many changes in 1979, seeing the usefulness of databases and a language of communication with it. introduced the first available SQL application.

But let's look at its basic commands:

MandateΔράση
Query commands
SELECTChoose. It is a basic query command for data recovery
SELECT *Using * with SELECT returns all columns
SELECT columnSpecify exact columns by their name
SELECT table.columnRequest a specific column from a specific table
FROMDetermine where to find the data
ASTemporary alias a table name or column, to a new name
WHEREFilter results with a condition
ANDUse multiple conditions with a WHERE clause. The results must correspond to all conditions.
ORUse multiple conditions with a WHERE clause. The results must correspond to only one condition
ORDER BYSort the results by one column. The database selects the sorting method
ORDER BY column ASCSort the results by column in ascending order
ORDER BY column DESCSort the results by column in descending order
LIMITLimit the number of results returned
OFFSETIgnore the first number of lines. Often used with LIMIT
SUBQUERYRun a data retrieval query for another query
Centralized functions
COUNTCount the number of rows corresponding to the query
MAXShow the highest value in a numeric column
MINShow the lowest value in a numeric column
SUMAdd the values ​​of a numeric column
AVGCalculate the average value for a numeric column
HAVINGUsed with aggregate functions instead of the WHERE clause
GROUP BYUse to improve the overall result
Operators
LIKESearch for identical characters for a specific pattern 
ILIKESearch for identical characters for a specific pattern (either uppercase or lowercase) 
BETWEENLook for a value between two values. Works with dates or numbers
>Look for values ​​greater than one condition
>=Look for values ​​greater than or equal to one condition
<Look for values ​​less than one condition
<=Look for values ​​less than or equal to one condition
=Look for values ​​that exactly match a situation
!=Look for values ​​that are not equal to a condition
UNITYCombine two unique questions (with the same columns) into one result
UNION ALLCombine two queries (with the same columns) into one result. Duplicates are allowed
INWrite with WHERE. Defines multiple OR conditions
NOT INWrite with WHERE. Specifies multiple OR conditions (inverse) or not equal to
IS NULLCheck for blank values
IS NOT NULLCheck that there are no blank values
INTERSECTReturn the results corresponding to two questions
MINUSReturn results to a query not found in another query
Creating and editing tables
CREATE TABLECreate a new table
NULLAllow empty values ​​for this field
NOT NULLDo not allow blank values ​​for this field
DEFAULTA default value to fill in a field (unless one is provided)
ASCreate a new table based on the structure of an existing table. The new table will contain the data from the old table
ALTER TABLE - ADD COLUMNAdd a new column to an existing table
ALTER TABLE - DROP COLUMNRemove a column from an existing table
ALTER TABLE - ALERT COLUMNChange the data type of an existing column
ALTER TABLE - RENAME COLUMNRename an existing column
ALTER TABLE - RENAME TABLERename an existing table
ALTER TABLE - MODIFY NULLAllow zero values ​​for a column
ALTER TABLE - MODIFY NOT NULLPrevent zero values ​​for a column
DROP TABLEDelete a table and all its data
TRUNCATE TABLEDelete all data in a table, but not the table itself
Restrictions
PRIMARY KEYA value that uniquely identifies an entry in a table. A combination of NOT NULL and UNIQUE
FOREIGN KEYIndicates a unique value in another table. Often a primary key on the other board
UNIQUEImpose unique values ​​for this column per table
CHECKMake sure the prices meet a specific condition
INDEX - CREATEOptimize tables and significantly speed up queries by adding an index to a column
INDEX - CREATE UNIQUECreate an index that does not allow duplicate values
INDEX - DROPRemove an index
Data creation and processing
INSERT - SINGLE VALUEAdd a new entry to a table
INSERT - MULTIPLE VALUESAdd several new entries to a table
INSERT - SELECTAdd entries to a table, but get the values ​​from an existing table
UPDATE - ALLModify all existing records in a table
UPDATE - WHEREModify existing records in a table that matches a condition
DELETE - ALLRemove all entries from a table
DELETE - WHERERemove entries from a table that matches a condition

SQL is now considered one of the most powerful languages. There are several "dialects" of SQL that differ depending on the database engine. It's a bit like the difference between HD DVD and Blu-ray. SQL is similar between databases, but an occasional composite command may not work exactly the same in all applications.

 
Most of the SQL statements in this article will work in any database. Once you know SQL, you will love it as much as we do and you can apply it in various uses. After all, it exists in almost every program, from websites set up in wordpress to spreadsheets. 

Read them Technology News from all over the world, with the validity of iGuRu.gr

Follow us on Google News iGuRu.gr at Google news