CRUD is an acronym that comes from the world of computer programming and refers to the four functions that are considered necessary to implement a persistent storage applic
ation: create, read, update and delete.
-----------------
CREATE TABLE emp
(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(100),
loc VARCHAR(100),
age INT,
sal INT,
PRIMARY KEY (id)
);
--------------
INSERT INTO emp(name, loc, age,sal)
VALUES ('venkat', 'kphb', 35,30000),
('praveen', 'pune', 28,35000),
('mounika', 'hyderabad', 22,39000),
('revathi', 'jntu', 26,20000),
('anji', 'gutta', 28,20000),
('harika', 'lb nagar', 26,30100),
('praveen', 'jntu', 33,100),
('pandu', 'munipeda', 35,30300),
('pandu', 'pune', 23,39300);
----------------
The SQL SELECT Statement
The SELECT statement is used to select data from a database.
The data returned is stored in a result table, called the result-set.
SELECT Syntax
SELECT column1, column2, ...
FROM table_name;
Here, column1, column2, ... are the field names of the table you want to select data from. If you want to select all the fields available in the table, use the following syntax:
SELECT * FROM table_name;
---------------------
The SQL SELECT DISTINCT Statement
The SELECT DISTINCT statement is used to return only distinct (different) values.
Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.
SELECT DISTINCT Syntax
SELECT DISTINCT column1, column2, ...
FROM table_name;
--------------
The SQL WHERE Clause
The WHERE clause is used to filter records.
It is used to extract only those records that fulfill a specified condition.
WHERE Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Note: The WHERE clause is not only used in SELECT statements, it is also used in UPDATE, DELETE, etc.!
-----------------
Text Fields vs. Numeric Fields
SQL requires single quotes around text values (most database systems will also allow double quotes).
However, numeric fields should not be enclosed in quotes:
--------------
Operators in The WHERE Clause
The following operators can be used in the WHERE clause:
Operator Description
= Equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
<> Not equal. Note: In some versions of SQL this operator may be written as !=
BETWEEN Between a certain range( BETWEEN 50 AND 60;)
LIKE Search for a pattern (City LIKE 's%';)
IN To specify multiple possible values for a column )City IN ('Paris','London');)
---------------------
| 8 | pandu | munipeda | 35 | 30300 |
+----+---------+-----------+------+-------+
8 rows in set (0.01 sec)
MariaDB [vlrinst]> select name,id,loc,sal,age from emp;
+---------+----+-----------+-------+------+
| name | id | loc | sal | age |
+---------+----+-----------+-------+------+
| venkat | 1 | kphb | 30000 | 35 |
| praveen | 2 | pune | 35000 | 28 |
| mounika | 3 | hyderabad | 39000 | 22 |
| revathi | 4 | jntu | 20000 | 26 |
| anji | 5 | gutta | 20000 | 28 |
| harika | 6 | lb nagar | 30100 | 26 |
| praveen | 7 | jntu | 100 | 33 |
| pandu | 8 | munipeda | 30300 | 35 |
+---------+----+-----------+-------+------+
8 rows in set (0.00 sec)
MariaDB [vlrinst]> select name from emp;
+---------+
| name |
+---------+
| venkat |
| praveen |
| mounika |
| revathi |
| anji |
| harika |
| praveen |
| pandu |
+---------+
8 rows in set (0.00 sec)
MariaDB [vlrinst]> select name,sal from emp;
+---------+-------+
| name | sal |
+---------+-------+
| venkat | 30000 |
| praveen | 35000 |
| mounika | 39000 |
| revathi | 20000 |
| anji | 20000 |
| harika | 30100 |
| praveen | 100 |
| pandu | 30300 |
+---------+-------+
8 rows in set (0.00 sec)
MariaDB [vlrinst]> select distinct name from emp;\
+---------+
| name |
+---------+
| venkat |
| praveen |
| mounika |
| revathi |
| anji |
| harika |
| pandu |
+---------+
7 rows in set (0.05 sec)
MariaDB [vlrinst]> select distinct name,loc from emp;\
+---------+-----------+
| name | loc |
+---------+-----------+
| venkat | kphb |
| praveen | pune |
| mounika | hyderabad |
| revathi | jntu |
| anji | gutta |
| harika | lb nagar |
| praveen | jntu |
| pandu | munipeda |
+---------+-----------+
8 rows in set (0.00 sec)
MariaDB [vlrinst]> select * from emp where loc='jntu';
+----+---------+------+------+-------+
| id | name | loc | age | sal |
+----+---------+------+------+-------+
| 4 | revathi | jntu | 26 | 20000 |
| 7 | praveen | jntu | 33 | 100 |
+----+---------+------+------+-------+
2 rows in set (0.13 sec)
MariaDB [vlrinst]> select * from emp where age=26;
+----+---------+----------+------+-------+
| id | name | loc | age | sal |
+----+---------+----------+------+-------+
| 4 | revathi | jntu | 26 | 20000 |
| 6 | harika | lb nagar | 26 | 30100 |
+----+---------+----------+------+-------+
2 rows in set (0.00 sec)
MariaDB [vlrinst]> select * from emp where age>26;
+----+---------+----------+------+-------+
| id | name | loc | age | sal |
+----+---------+----------+------+-------+
| 1 | venkat | kphb | 35 | 30000 |
| 2 | praveen | pune | 28 | 35000 |
| 5 | anji | gutta | 28 | 20000 |
| 7 | praveen | jntu | 33 | 100 |
| 8 | pandu | munipeda | 35 | 30300 |
+----+---------+----------+------+-------+
5 rows in set (0.02 sec)
MariaDB [vlrinst]> select * from emp where age<26;
+----+---------+-----------+------+-------+
| id | name | loc | age | sal |
+----+---------+-----------+------+-------+
| 3 | mounika | hyderabad | 22 | 39000 |
+----+---------+-----------+------+-------+
1 row in set (0.00 sec)
MariaDB [vlrinst]> select name from emp where age<26;
+---------+
| name |
+---------+
| mounika |
+---------+
1 row in set (0.00 sec)
MariaDB [vlrinst]> select name from emp where sal<20000;
+---------+
| name |
+---------+
| praveen |
+---------+
1 row in set (0.00 sec)
MariaDB [vlrinst]> select name,sal from emp where sal<20000;
+---------+------+
| name | sal |
+---------+------+
| praveen | 100 |
+---------+------+
1 row in set (0.00 sec)
MariaDB [vlrinst]> select name,sal from emp where sal<>100;
+---------+-------+
| name | sal |
+---------+-------+
| venkat | 30000 |
| praveen | 35000 |
| mounika | 39000 |
| revathi | 20000 |
| anji | 20000 |
| harika | 30100 |
| pandu | 30300 |
+---------+-------+
7 rows in set (0.00 sec)
MariaDB [vlrinst]> select name,sal from emp where sal<>20000;
+---------+-------+
| name | sal |
+---------+-------+
| venkat | 30000 |
| praveen | 35000 |
| mounika | 39000 |
| harika | 30100 |
| praveen | 100 |
| pandu | 30300 |
+---------+-------+
6 rows in set (0.00 sec)
MariaDB [vlrinst]> select name,sal from emp where sal between 20000 and 30300;
+---------+-------+
| name | sal |
+---------+-------+
| venkat | 30000 |
| revathi | 20000 |
| anji | 20000 |
| harika | 30100 |
| pandu | 30300 |
+---------+-------+
5 rows in set (0.00 sec)
MariaDB [vlrinst]> select name from emp where name like 'v%'
-> ;
+--------+
| name |
+--------+
| venkat |
+--------+
1 row in set (0.00 sec)
MariaDB [vlrinst]> select name from emp where name like 'p%';
+---------+
| name |
+---------+
| praveen |
| praveen |
| pandu |
+---------+
3 rows in set (0.00 sec)
MariaDB [vlrinst]> select name from emp where name like '%t';
+--------+
| name |
+--------+
| venkat |
+--------+
1 row in set (0.00 sec)
MariaDB [vlrinst]> select name from emp where name like '%n';
+---------+
| name |
+---------+
| praveen |
| praveen |
+---------+
2 rows in set (0.00 sec)
MariaDB [vlrinst]> select name from emp where name like '%a';
+---------+
| name |
+---------+
| mounika |
| harika |
+---------+
2 rows in set (0.00 sec)
MariaDB [vlrinst]> select * from emp;
+----+---------+-----------+------+-------+
| id | name | loc | age | sal |
+----+---------+-----------+------+-------+
| 1 | venkat | kphb | 35 | 30000 |
| 2 | praveen | pune | 28 | 35000 |
| 3 | mounika | hyderabad | 22 | 39000 |
| 4 | revathi | jntu | 26 | 20000 |
| 5 | anji | gutta | 28 | 20000 |
| 6 | harika | lb nagar | 26 | 30100 |
| 7 | praveen | jntu | 33 | 100 |
| 8 | pandu | munipeda | 35 | 30300 |
+----+---------+-----------+------+-------+
8 rows in set (0.00 sec)
MariaDB [vlrinst]> select * from emp where loc in ('kphb','pune');
+----+---------+------+------+-------+
| id | name | loc | age | sal |
+----+---------+------+------+-------+
| 1 | venkat | kphb | 35 | 30000 |
| 2 | praveen | pune | 28 | 35000 |
+----+---------+------+------+-------+
2 rows in set (0.00 sec)
MariaDB [vlrinst]> select * from emp where loc in ('kphb','pune','munipeda');
+----+---------+----------+------+-------+
| id | name | loc | age | sal |
+----+---------+----------+------+-------+
| 1 | venkat | kphb | 35 | 30000 |
| 2 | praveen | pune | 28 | 35000 |
| 8 | pandu | munipeda | 35 | 30300 |
+----+---------+----------+------+-------+
3 rows in set (0.00 sec)
MariaDB [vlrinst]> select * from emp where loc in ('kphb','Pune','munipeda');
+----+---------+----------+------+-------+
| id | name | loc | age | sal |
+----+---------+----------+------+-------+
| 1 | venkat | kphb | 35 | 30000 |
| 2 | praveen | pune | 28 | 35000 |
| 8 | pandu | munipeda | 35 | 30300 |
+----+---------+----------+------+-------+
3 rows in set (0.00 sec)
MariaDB [vlrinst]> select ID from emp;
+----+
| ID |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
+----+
8 rows in set (0.00 sec)
MariaDB [vlrinst]> select ID from emp where name like 'V%';
+----+
| ID |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
MariaDB [vlrinst]> select ID,name from emp where name like 'V%';
+----+--------+
| ID | name |
+----+--------+
| 1 | venkat |
+----+--------+
1 row in set (0.00 sec)
MariaDB [vlrinst]>
No comments:
Post a Comment
Note: only a member of this blog may post a comment.