×

  PHP

Introduction Getting Started Data Types Operators Conditional Statements Loops Functions Arrays Include Files GET and POST

  Mysql

Data Base Concepts Mysql Talking to Mysql

  PHP & Mysql

Connect DB Mysql insert Mysql select Mysql update Mysql delete
XAMPP Lite S/W
☰




Talking to MySQL Databases with SQL

SQL is the language that provides command to interact with the data in the database. SQL consists of three components - DDL, DML & DCL. With SQL you can perform any database-related task, such as creating databases and tables, saving data in database tables, query a database for specific records, deleting and updating data in databases.

Data Definition Language (DDL): DDL comprises commands you can use to create and modify the database structure. DDL is a subset of SQL that is used to define tables (database structure) and other metadata related things.

Basic DDL commands:

CREATE DATABASE

CREATE TABLE

DROP TABLE

ALTER TABLE

Data Manipulation Language (DML): DML comprises commands you can use to add, modify, delete and query data in the database. Standard subset of SQL that is used for data manipulation.

Basic DML commands:

INSERT

SELECT

UPDATE

DELETE

Data Control Language (DCL): DCL comprises commands you can use to control the user access to the database.

Basic DCL commands:

• GRANT - gives user's access privileges to database

• REVOKE - withdraw access privileges given with the GRANT command

Transaction Control Language (TCL): TCL statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

Basic TCL commands:

COMMIT - save work done

SAVEPOINT - identify a point in a transaction to which you can later roll back

ROLLBACK - restore database to original since the last COMMIT

SQL INSERT Statement

The INSERT Statement is used to add new rows of data to a table. We can insert data to a table in two ways.

1) Inserting the data directly to a table. Syntax for SQL INSERT is:

INSERT INTO TABLE_NAME [(col1, col2, col3,...colN)] VALUES
(value1, value2, value3,...valueN);

col1, col2,...colN -- the names of the columns in the table into which you want to insert data.

2) Inserting the data without specifing the column(s) name.

While inserting a row, if you are adding value for all the columns of the table you need not specify the column(s) name in the sql query. But you need to make sure the order of the values is in the same order as the columns in the table. The sql insert query will be as follows:

INSERT INTO TABLE_NAME VALUES (value1, value2, value3,...valueN);

For Example: If you want to insert a row to the employee table,

the query would be like, 
INSERT INTO employee (id, name, dept, age, salary location) VALUES
(105, 'Srinath', 'Aeronautics', 27, 33000);

NOTE: When adding a row, only the characters or date values should be enclosed with single quotes. If you are inserting data to all the columns, the column names can be omitted. The above insert statement can also be written as,

INSERT INTO employee VALUES (105, 'Srinath', 'Aeronautics', 27, 33000);

SQL UPDATE Statement

The UPDATE Statement is used to modify the existing rows in a table.

The Syntax for SQL UPDATE Command is:

UPDATE table_name SET column_name1 = value1, column_name2 = value2, ... [WHERE condition]

• table_name - the table name which has to be updated.

• column_name1, column_name2.. - the columns that gets changed.

• value1, value2... - are the new values.

NOTE: In the Update statement, WHERE clause identifies the rows that get affected. If you do not include the WHERE clause, column values for all the rows get affected.

For Example: To update the location of an employee, the sql update query would be like:

UPDATE employee SET location ='Mysore' WHERE id = 101;

To change the salaries of all the employees, the query would be:

UPDATE employee SET salary = salary + (salary * 0.2);

SQL Delete Statement

The DELETE Statement is used to delete rows from a table.

The Syntax of a SQL DELETE statement is:

DELETE FROM table_name [WHERE condition];

• table_name -- the table name which has to be updated.

NOTE: The WHERE clause in the sql delete command is optional and it identifies the rows in the column that gets deleted. If you do not include the WHERE clause all the rows in the table is deleted, so be careful while writing a DELETE query without WHERE clause.

For Example: To delete an employee with id 100 from the employee table, the sql delete query would be like:

DELETE FROM employee WHERE id = 100;

To delete all the rows from the employee table, the query would be like:

DELETE FROM employee;

SQL TRUNCATE Statement

The SQL TRUNCATE command is used to delete all the rows from the table and free the space containing the table.

Syntax to TRUNCATE a table:

TRUNCATE TABLE table_name;

For Example: To delete all the rows from employee table, the query would be like:

 
TRUNCATE TABLE employee;

Difference between DELETE and TRUNCATE Statements:

DELETE Statement: This command deletes only the rows from the table based on the condition given in the where clause or deletes all the rows from the table if no condition is specified. But it does not free the space containing the table.

TRUNCATE statement is used to delete all the rows from the table and free the space containing the table.

SQL DROP Statement:

The SQL DROP command is used to remove an object from the database. If you drop a table, all the rows in the table is deleted and the table structure is removed from the database. Once a table is dropped we cannot get it back, so be careful while using RENAME command. When a table is dropped all the references to the table will not be valid.

Syntax to drop a sql table structure:

DROP TABLE table_name;

For Example: To drop the table employee, the query would be like:

DROP TABLE employee;

Difference between DROP and TRUNCATE Statement:

If a table is dropped, all the relationships with other tables will no longer be valid, the integrity constraints will be dropped, grant or access privileges on the table will also be dropped, if want use the table again it has to be recreated with the integrity constraints, access privileges and the relationships with other tables should be established again. But, if a table is truncated, the table structure remains the same, therefore any of the above problems will not exist.

SQL Subquery

Subquery or Inner query or Nested query is a query in a query.

A subquery is usually added in the WHERE Clause of the sql statement.

Most of the time, a subquery is used when you know how to search for a value using a SELECT statement, but do not know the exact value.

Subqueries are an alternate way of returning data from multiple tables.

Subqueries can be used with the following sql statements along with the comparision operators like =, <, >, >=, <= etc.

For Example:

1) Usually, a subquery should return only one record, but sometimes it can also return multiple records when used with operators like IN, NOT IN in the where clause. The query would be like:

 
SELECT first_name, last_name, subject FROM student_details WHERE
games NOT IN ('Cricket', 'Football');

2) Lets consider the student_details table which we have used earlier. If you know the name of the students who are studying science subject, you can get their id's by using this query below:

SELECT id, first_name FROM student_details WHERE first_name IN
('Rahul', 'Stephen');
but, if you do not know their names, then to get their id's you need to write the query in this manner: 
 
SELECT id, first_name FROM student_details WHERE first_name IN
(SELECT first_name FROM student_details WHERE subject= 'Science');

In the above sql statement, first the inner query is processed first and then the outer query is processed.

3) Subquery can be used with INSERT statement to add rows of data from one or more tables to another table. Lets try to group all the students who study Maths in a table 'maths_group'. 

 
INSERT INTO maths_group(id, name) SELECT id, first_name 
|| ' ' || last_name FROM student_details WHERE subject = 'Maths'

4) A subquery can be used in the SELECT statement as follows. Lets use the product and order_items table defined in the sql_joins section.

select p.product_name, p.supplier_name, (select order_id from order_items where product_id = 101) as order_id from product p where p.product_id = 101
Correlated Subquery

A query is called correlated subquery when both the inner query and the outer query are interdependent. For every row processed by the inner query, the outer query is processed as well. The inner query depends on the outer query before it can be processed.

SELECT p.product_name FROM product p WHERE p.product_id = 
(SELECT o.product_id FROM order_items o WHERE o.product_id 
= p.product_id);