SQL Tutorial part :5 – Primary Key

SQL Tutorial part :5 – Primary Key
Image

The primary function of the primary key is to identify each row of a table individually

1. With this, each entries of the table are uniquely identified.

2. You can set one or more columns as the primary key. Whatever you do, this primary key tie will mark each row of your table uniquely.

3. If you set more than one column as a primary key, then all the columns together have one primary key. There will be only one primary key in a table.

4. The primary key will not be empty.

5. Suppose the table I created in the previous tutorial set the first_name as the primary key in that table and if there were a few thousand names then there is a possibility that one name may match another name somewhere then you might make the primary key with first_name and last_name. In this way a PRIMARY KEY can be made with up to 16 columns because everything is unique.

Therefore, it is better to set the id number as PRIMARY KEY, because the id of two people is never the same.

How to set the primary key of a table

=======================

Suppose you did not create the primary key when creating the “test” table in the previous tutorial. Now you need to create the primary key. You can also set this icon by clicking there at the end of each field.

Image

Or the primary key can be set by typing the following SQL statement (click on the SQL tab of PHPMyAdmin then paste this code)

Image

SQL Table Operation :

SHOW TABLES This statement shows all the tables in a database. Clicking on the tables of the database you want to view, going to the SQL tab, typing SHOW TABLES and clicking on “Go” will present all the tables in that database.

DESCRIBE TABLE With this statement you can see the details of the table. For example DESCRIBE test it will show the table below.

Image

To make various changes to the table

In addition to creating tables, you will find many options to modify tables in MYSQL, such as

Image

This allows you to add, delete, remove PRIMARY KEY from any column in the table.

Image

Here you can delete the column from that table with the name of the table instead of table_name and the name of the column instead of column_name.

Image

With it you can add new columns to a table.

SQL SELECT Query :

The SELECT statement is used to retrieve data from the database

Image

This will present all the data (if any) of the whole table named test in front of you.

 

* If you name a column in its place, it will only show those columns

Image

With the SELECT statement, you can pick up a few specific rows of the table and see how much you want to see. Suppose there are 20 rows in the “test” table. Now you want to see the first 5 rows.

Image

Output :

Image

There will be more discussions on the limit in the future. LIMIT is a powerful clause that works a lot with it.

** There has been a lot of discussion in previous tutorials on where to write statements / queries, how to write them, how to write them, etc. “

SQL UPDATE DELETE :

Deleting table data updates is easy with SQL. I am deleting some data updates in this table.

UPDATE

Suppose we update the “number” column of all rows in the “students” table.

Image

The above query “students” will add 2 to the value in the number column of all rows in the table.

This query will add 2 to the number of all rows in the table. If I want to do an update in the first 2 rows then I can use LIMIT e.g.

Image

The above query “students” will add 2 to the value in the number column of the first 2 rows of the table.

If I want to update from row 2 to row 7, then writing a query like above will not work. Then you have to use the following subquery, because LIMIT does not work offset with UPDATE.

Image

The upper query “students” will increase the value of the number column from the first 2 to the 8th row (8 rows) of the table to 20.

DELETE

To delete any row, a column of that row (usually done with id column) can be deleted with WHERE clause. For example, if I want, I will delete the row containing the 5th ID of the “students” table. Then

Image

The above query “students” will delete that row in the table whose id will be 5.

If you want to delete the last 3 rows, you can write as below

Image

In this way, you can filter any data as you wish, delete it or update it with SQL. Of course, these tasks can also be done with software. For example, select any table with phpMyAdmin and it will look like below

Image

You can see here that there are links like “Edit”, “Delete” in each row and you can edit or update any data by clicking on these.

If you don’t know the 3 differences, you will regret it (difference between DELETE, DROP and TRUNCATE).

Leave a Reply

Your email address will not be published. Required fields are marked *