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
'Database & SQL' 카테고리의 다른 글
Accessing DB through cmd (0) | 2024.07.20 |
---|---|
Setting up MariaDB, Heidi SQL (0) | 2024.07.19 |