Sql Basic functions

Today i want to share basic functions of sql. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database. For getting database data we use many type sql functions is there follow this tutorials learn more about sql. 

What you need

     SQL queries to perform to phpMyAdmin as a visual interface to MySQLI on your local machine. For suggestion to best on beginners XMPP or WAMP.

Basic functions in SQL

             - Create table

             - Insert

             - View

             - Update

             - Delete 

     

Create table in sql

     With this query we can create table in database. for phpmyadmin select your database go to sql paste below code and execute query.

CREATE TABLE IF NOT EXISTS `user` (
`id` int(11) AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(150) NOT NULL,
  `email` varchar(150) NOT NULL,
  `address` varchar(100) NOT NULL
)

Insert query

      Users table we already create name, email,address based on that column add new data.

INSERT INTO `user`(`name`, `email`, `address`) VALUES ('mostlikers','ms@msd.com','india')

View query

     View query using too fetch database table data, 

SELECT * - All table data.

SELECT `id` - Fetching only id value column data.

SELECT WHERE - Particular data based on id or name etc.

All data - SELECT * FROM `user`
column data - SELECT `id`, `name`, `email`, `address` FROM `user`
Particular data - SELECT * FROM `user` WHERE 1

Update query

    update query changing database row data value to new value in same row. 

UPDATE `user` SET `name`='mostlikers',`email`='mostlikers@gmail.com',`address`='nt str' WHERE id='1'

Delete query

Delete query to delete database data particular column or all data. 

All data delete(Truncate) - DELETE FROM `user`
Delete single row - DELETE FROM `user` WHERE id='1'

Primary key in sql

      The primary key constraint uniquely identifies each record in a database table. its should create each rows have one unique id.

CREATE TABLE `users` (
`id` int(11) AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(150) NOT NULL,
  `email` varchar(150) NOT NULL
)

Foreign key in sql (Relational database)

      Foreign key in one table points to a Primary key in another table. Relational data to connect with one table to another table. if you delete primary key value foreign data also will delete.

CREATE TABLE `users` (
`id` int(11) AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(150) NOT NULL
)

CREATE TABLE Orders
(
    id int NOT NULL,
    OrderNo int NOT NULL,
    user_id int,
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES users(id)
)

SQL Order by

      Sql order function its sort the data based on ascending & descending. Order by default it will take Ascending.

ORDER BY - default it will take ascending.
ORDER BY ASC  - data value order by ascending based on id.
ORDER BY DESC - data value order by descending based on id.
ORDER BY name DESC  - data value order by descending based on column name.

SELECT * FROM users ORDER BY id;
SELECT * FROM users ORDER BY name ASC;
SELECT * FROM users ORDER BY name DESC;
SELECT * FROM users ORDER BY name,id DESC;

  .

SQL JOINS

Sql join clause is used to combine two table based on combine data. sql query related on  parent categories to child categories table value data fetching, customers and order table etc.

Join table code

CREATE TABLE `users` (
`id` int(11) AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(150) NOT NULL
)

CREATE TABLE Order
(
    id int NOT NULL,
    OrderNo int NOT NULL,
    user_id int,
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES users(id)
)

Join query
    Table1-users combine value to Table2 -orders value data if both rows combine data result only get.

SELECT  us.*,o.* FROM `users` us JOIN order o ON us.id = o.user_id 

Left join query
    It will get all rows in left table(users) and matched rows value to the right table(order).

SELECT  us.*,o.* FROM `users` us LEFT JOIN order o ON us.id = o.user_id 

Right join
   It will get all right table(orders) rows value and matched values to the left table(users).

SELECT  us.*,o.* FROM `users` us JOIN order o ON us.id=o.user_id 

Above i have posted for basic learning sql functions. i will update more sql function my next post. Thank you for visiting.

Recent post