Understanding Database, SQL and it’s commands
Database is a collection of data which is organized efficiently in order to access, retrieve and update data quickly. Data is stored in a structured format in a table in the form of rows and columns. Table has specified number of columns or fields but can have any number of rows. In this post we are going to learn about types of keys, structured query language (SQL) and its commands.
Types of Keys
- Primary Key: Primary key is a field which cannot be null as well as unique for each row. There is a single primary key for each table and a single table cannot have two primary keys.
- Super Key: It is mixture of different fields such that one of the field is uniquely identifies i.e. one of the key must be unique for all the records or rows in the table. It can be a single field or group of keys.
- Candidate Key: It is a category of super key such that all fields that makes it must be unique for all records in the database. It can be single or group of keys.
- Foreign Key: If the primary key of one table is used in another table, then in another table it is termed as foreign key. Foreign key can be the same for two records and also can be null.
SQL
SQL is an acronym for Structured Query Language. It is used to access as well as manipulate databases. Using SQL one can create database, tables, add new records to the table inside the database or remove them. For the users to perform these action efficiently one needs tools or interface which is provided by database systems. Different database systems based on SQL language are Microsoft SQL, MySQL, PostgreSQL and many more.
Basic SQL commands
- SHOW : It shows information contained like if command show databases; is executed inside MySQL server, it will list databases managed by the server. Have a look at the below screenshot
Another show command is show tables. To show tables of the database first select database using the command use *database_name* and then type show tables. Suppose, I have selected information_schema database which is by default listed in MySQL Server and typed the below command
show tables;
Output for such is as below
Another show command is show columns. To execute this command one needs to select the table from the database. Suppose I would like to see the field’s description of the table CHARACTER_SETS from information_schema database. I will execute the below command
show columns from CHARACTER_SETS;
Following is the output of the above execution
- SELECT : It selects the data from the database. For example, I need to get all the values associated with a particular field in a table, I will execute the below command
select column_field from table;
Have a look at the below screenshot
Point to note is that SQL is case-insensitive. It does not matter whether you write in capital letters or small. But statements ends with a semicolon. White spaces and multiple lines are ignored in SQL.
If one writes select * from table_name, it will fetch all the records from a table created inside the database. Have a look at below screenshot
select command can be used with distinct keyword which retrieves only unique records.
Key thing to understand here is when multiple columns fields are used to retrieve information using select with distinct, then only values repeated in all selected multiple column fields would be ignored.
select can also be used with limit keyword which will limit the records to be returned. One can use offset with limit keyword. Offset means not taking into consideration the number of rows mentioned along with offset. For example if I say offset 3, it means that the first 3 rows are ignored and results would be displayed starting from row 4th.
select can also be used with order by keyword which sorts the result based on the selected column field. By default, sorting is done in the ascending order.
Learn about filtering the records from the database using sql commands and operators.
Originally published at https://satinderhunt.com on November 26, 2020.