- SQL Create DB – It is used to create a database
- SQL Create Table – It is used to create a table.
- SQL Create Index – It is used to create indexes in tables
- SQL Drop – can be used to delete table,indexes and databases
- SQL Truncate – can be used to delete records in table
- SQL Alter – It is used to add, delete, or modify columns in a table.
Tag Archives: Statement
SQL ROUND()
SQL ROUND() is used to round a numeric field to the number of decimals specified.
Syntaxt
SELECT ROUND(column_name,decimals) FROM table_name
Table – Users
+—–+—–+———–+
| oid | uid | price |
+—–+—–+———–+
| 1 | 1 | 60.345645 |
| 2 | 2 | 30.45564 |
| 3 | 2 | 60.78565 |
| 4 | 1 | 70.96565 |
| 5 | 2 | 80.565645 |
| 6 | 1 | 50.57565 |
+—–+—–+———–+
Example
mysql> SELECT ROUND(price,2) as price FROM orders;
+——-+
| price |
+——-+
| 60.35 |
| 30.46 |
| 60.79 |
| 70.97 |
| 80.57 |
| 50.58 |
+——-+
6 rows in set (0.00 sec)
SQL LEN()
SQL LEN() returns the length of the value in a text field.
Syntaxt
SELECT LEN(column_name) FROM table_name
SQL MID()
SQL MID() is used to extract characters from a text field.
Syntaxt
SELECT MID(column_name,start,length) FROM table_name
Table – Users
+—–+——-+
| uid | name |
+—–+——-+
| 1 | name1 |
| 2 | name2 |
| 3 | name3 |
| 4 | name4 |
+—–+——-+
Example
mysql> SELECT MID(name,1,3) as name FROM users;
+——+
| name |
+——+
| nam |
| nam |
| nam |
| nam |
+——+
4 rows in set (0.00 sec)
mysql> SELECT MID(name,2,4) as name FROM users;
+——+
| name |
+——+
| ame1 |
| ame2 |
| ame3 |
| ame4 |
+——+
4 rows in set (0.00 sec)
SQL LCASE()
SQL LCASE() converts the value of a field to lowercase.
Syntaxt
SELECT LCASE(column_name) FROM table_name
Table – Users
+——+
| name |
+——+
| AAAA |
| BBBB |
| CCCC |
| DDDD |
| EEEE |
+——+
Example
mysql> SELECT LCASE(name) FROM users;
+————-+
| LCASE(name) |
+————-+
| aaaa |
| bbbb |
| cccc |
| dddd |
| eeee |
+————-+
5 rows in set (0.00 sec)
SQL UCASE()
SQL UCASE() converts the value of a field to uppercase.
Syntaxt
SELECT UCASE(column_name) FROM table_name
Table – Users
+—–+——+
| uid | name |
+—–+——+
| 1 | aaaa |
| 2 | bbbb |
| 3 | cccc |
| 4 | dddd |
| 5 | eeee |
+—–+——+
Example
mysql> SELECT UCASE(name) AS name FROM users;
+——+
| name |
+——+
| AAAA |
| BBBB |
| CCCC |
| DDDD |
| EEEE |
+——+
5 rows in set (0.00 sec)
SQL HAVING
SQL HAVING clause is used with aggregate functions.
Syntaxt
SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
Table – Users
+—–+——+
| uid | name |
+—–+——+
| 1 | AAAA |
| 2 | BBBB |
| 3 | CCCC |
| 4 | DDDD |
| 5 | EEEE |
+—–+——+
Table – Orders
+—–+—–+——-+
| oid | uid | price |
+—–+—–+——-+
| 1 | 1 | 60 |
| 2 | 2 | 30 |
| 3 | 2 | 60 |
| 4 | 1 | 70 |
| 5 | 2 | 80 |
| 6 | 1 | 50 |
+—–+—–+——-+
Example
mysql> SELECT SUM(o.price) AS total,u.name FROM users AS u JOIN orders AS o ON u.uid=o.uid GROUP BY o.uid HAVING total >175;
+——-+——+
| total | name |
+——-+——+
| 180 | AAAA |
+——-+——+
1 row in set (0.00 sec)
SQL GROUP BY
SQL GROUP BY should be added with all aggregate functions and it s used in conjunction with the aggregate functions to group the result-set by one or more columns.
Table – Users
+—–+——+
| uid | name |
+—–+——+
| 1 | AAAA |
| 2 | BBBB |
| 3 | CCCC |
| 4 | DDDD |
| 5 | EEEE |
+—–+——+
Table – Orders
+—–+—–+——-+
| oid | uid | price |
+—–+—–+——-+
| 1 | 1 | 60 |
| 2 | 2 | 30 |
| 3 | 2 | 60 |
| 4 | 1 | 70 |
| 5 | 2 | 80 |
| 6 | 1 | 50 |
+—–+—–+——-+
Example
mysql> SELECT SUM(o.price) AS total,u.name FROM users AS u JOIN orders AS o ON u.uid=o.uid GROUP BY o.uid;;
+——-+——+
| total | name |
+——-+——+
| 180 | AAAA |
| 170 | BBBB |
+——-+——+
2 rows in set (0.00 sec)
SQL SUM()
SQL SUM() returns the total sum of a numeric column.
Table – Students
+—–+——+——-+——-+——-+
| sid | name | mark1 | mark2 | mark3 |
+—–+——+——-+——-+——-+
| 1 | AAAA | 30 | 35 | 40 |
| 2 | BBBB | 36 | 37 | 38 |
| 3 | CCCC | 42 | 43 | 44 |
| 4 | DDDD | 43 | 32 | 39 |
+—–+——+——-+——-+——-+
Example
mysql> SELECT SUM(mark1) FROM students ;
+————+
| SUM(mark1) |
+————+
| 151 |
+————+
1 row in set (0.00 sec)
SQL MIN()
SQL MIN() returns the smallest value of the selected column.
Table – Students
+—–+——+——-+——-+——-+
| sid | name | mark1 | mark2 | mark3 |
+—–+——+——-+——-+——-+
| 1 | AAAA | 30 | 35 | 40 |
| 2 | BBBB | 36 | 37 | 38 |
| 3 | CCCC | 42 | 43 | 44 |
| 4 | DDDD | 43 | 32 | 39 |
+—–+——+——-+——-+——-+
Example
mysql> SELECT MIN(mark1) FROM students ;
+————+
| MIN(mark1) |
+————+
| 30 |
+————+
1 row in set (0.00 sec)