HeidiSQL: sql commands and functions

Today we did a further dive on HeidiSQLand learnt more of its commands and functions

 

Did you know SQL is text-based? it will export all of its files in .txt format!! radical :3

---

How SQL reads and processes commands

this is the order the DB reads its SQL commands:

 

          3                4              1                2

   command  selection    location  table name

    SELECT         *              FROM     employees;

 

 

     3              4               4             1            2            5      

SELECT first_name, hire_date FROM employees LIMIT 0,10; > starting number, length : paging

---

Useful methods in SQL:

COUNT(): brings the total number of value selected in brackets
> SELECT COUNT(*) FROM employees : brings total(*) number of employees

> SELECT COUNT(last_name) FROM employees : inputting column title is also applicable

 

> SELECT first_name, COUNT(*) FROM employees :brings first value in first_name and total count,

comma separates values 

> SELECT COUNT(gender) FROM employees WHERE gender='M' > number of ppl who are gender = M

> SELECT COUNT(*) FROM salaries WHERE salary >= 60000 > we can use relational operators

> SELECT COUNT(*) FROM dept_emp WHERE YEAR(to_date)= 9999 > combination of relational operator and YEAR()

---

DISTINCT: gets rid of any duplicate values

> SELECT DISTINCT title FROM titles 

 

WHERE: can use to set a filter

SELECT * FROM employees WHERE gender = 'F' > only selecting rows that has gender = 'F'

 

AND: can add an additional clause to the filter

 SELECT * FROM employees WHERE gender = 'F' AND first_name = 'Lillian'

 

LIKE: used specifically to find string values

> SELECT * FROM employees WHERE first_name LIKE 'L%' > find all first_name value that start with L

> SELECT * FROM employees WHERE first_name LIKE '%n'  > ends with n

> SELECT * FROM employees WHERE first_name LIKE '%il%' > has 'il' in the middle

 

we can also use:

SELECT * FROM employees WHERE first_Name LIKE '_ _jia' if we know there are two characters in front

 

or 

CONCAT: similar to like but deconstructed

> SELECT * FROM employees WHERE last_name LIKE CONCAT('%','oo','%') > has 'oo' in middle

 

BETWEEN: selecting information between two parameters

> SELECT * FROM employees WHERE YEAR(hire_date) between 1999 and 2000

 

IN: selecting information that adheads to given parameters

> SELECT * FROM employees WHERE YEAR(hire_date) IN (1998,2000,2002);

> SELECT * FROM employees WHERE first_name IN ('Berni', 'Elvis', 'Kwee');

 

GROUP BY: grouping information by given value

> SELECT first_name, COUNT(*) FROM employees GROUP BY first_name;

 

> SELECT title, COUNT(*) FROM titles WHERE YEAR(to_date) = 9999 GROUP BY title

 

 

ORDER BY:  arranging order results by ASC (default) or DESC (descending)
SELECT * FROM salaries WHERE YEAR(to_date) = 9999 ORDER BY salary DESC LIMIT 0, 1
> ordered by descending to start from highest salary then used limit (0,1) to find the first = highest

 

 

AS: setting a name for the sorted column

> SELECT gender, COUNT(*) AS '명' FROM employees GROUP BY gender;

 

 

HAVING: a way to add a filter without using WHERE

> SELECT dept_no, COUNT(*) AS 'cnt' FROM dept_emp WHERE YEAR(to_Date) = 9999 

GROUP BY dept_no HAVING cnt > 50000;

 

 

SUBSTRING: similar to Java substring() method

---

Functions in SQL

here are some basic functions in SQL: count(), sum(), avg(), max(), min()

 

• SUM() : gets total sum
  SELECT SUM(salary) FROM salaries WHERE YEAR(to_date) = 9999;
  > total sum of salary for current employees
  
  
• AVG(): gets average
  > SELECT AVG(salary) FROM salaries WHERE YEAR(to_date) = 9999;
  
  > SELECT * FROM salaries 
  WHERE YEAR(to_date) = 9999 AND salary < (
  SELECT AVG(salary) FROM salaries WHERE YEAR(to_date) = 9999) LIMIT 0,10
  > finding 10 people who are paid less than average

---

tenerary statements and CASE

• if( ): like java, we can use ternary statements in SQL
  > SELECT if(gender= 'M', '남','여') AS '성별', COUNT(*) AS '명' FROM employees GROUP BY gender

 

• CASE: similar to switch statement in Java

SELECT 

    CASE

      WHEN gender = 'M' THEN '남자'

         ELSE '여자'

  END AS '성별',

      COUNT(*) AS '명' / COUNT  명

FROM employees

GROUP BY gender;

---

DATE and DATE formats

https://mariadb.com/kb/en/date_format/

 

> SELECT DATE_FORMAT(b_date, '%Y-%M-%d') FROM board

or

shortened version:

 

with time:

 

---

VIEW

To prevent typing a long command into the sql variable in our Java code, we can simply create a view and load that.

• we can edit tables but not views as they are virtual and doesn't have its own values