The SELECT/SELECT: SELECT (query) data INSERT/INSERT: add data to the database UPDATE/UPDATE: modify the data in the database to DELETE/DELETE: DML — select * from employees; select * from employees;
Select employee_id, first_name from employees;
Select employee_id as employee, first_name as name from employees;
select employee_id employee, first_name name from employees;
Select * from employees where department_id = 90;
The comparison operation (=: equals (not ==); > : greater than; >=: greater than or equal to; < : less than; <=: less than or equal to; <>: is not equal to (also can be! =)) select * from employees where salary > 10000;
Other comparison operations BETWEEN… The AND… Between two values (including the boundary)
select * from employees where salary between 5000 and 10000; In (set) is equal to one of the values IN the list
select * from employees where department_id in(90, 100);
LIKE fuzzy query
Select * from employees where first_name = ‘%s%’;
IS NULL NULL values
select * from employees where manager_id is null;
Logical operation (AND logical union; Logic C. SELECT * FROM employees WHERE salary > 10000 AND MANAGER_ID IS NULL;
select * from employees where salary > 10000 OR manager_id is null;
Select * from employees where department_id not in(department_id);
Using the ORDEY BY clause, default ascending order (ASC (ASEND) : ascending order; Desc (descend) : descend
select * from employees order by salary asc;
select * from employees order by salary desc;
Select employee_id, salary*12 salaryyear from employees order by salaryyear asc;
Select * from employees order by department_id,salary asc; select * from employees order by department_id,salary asc;
Select – function group function types avg () the average count Max () () count Max min min sum () combined
select avg(salary), max(salary), min(salary), sum(salary), count(salary) from employees;
SELECT – Grouping Function Single Column Grouping: Department Average Salary
select department_id, avg(salary) from employees group by department_id;
Multi-column grouping: average salary of positions in the department
select department_id, job_id, avg(salary) from employees group by department_id, job_id;
Select group having clause —— select department having maximum salary > 10000
select department_id, max(salary) from employees group by department_id having max(salary) > 10000;
SELECT – Multiply the amount of data from multiple tables
select name, boyName from beauty, boys;
ID same, equivalent connection
select beauty.name, boys.boyName from beauty, boys where beauty.id = boys.id;
Use the alias
select bt.name, bs.boyName from beauty bt, boys bs where bt.id = bs.id;
Join multiple tables (requires n-1 join conditions)
On clause creates multiple table joins (association conditions department_id, location_id)
select employee_id, last_name, department_name, city from employees e join departments d on d.department_id = e.department_id join locations l on l.location_id = d.location_id;
Left [outer] join on intersection
select bt.name, bs.boyName from beauty bt left join boys bs on bt.boyfriend_id = bs.id;
Inner join on intersection
select bt.name, bs.boyName from beauty bt inner join boys bs on bt.boyfriend_id = bs.id;
Right [outer] join on left intersection
select bt.name, bs.boyName from beauty bt right join boys bs on bt.boyfriend_id = bs.id;
Other special circumstances
SELECT FROM A LEFT JOIN B ON A.key=B.key WHERE B.key is null;
SELECT FROM A RIGHT JOIN B ON A.key=B.key WHERE A.key is null;
SELECT FROM A FULL JOIN B ON A.key=B.key;
SELECT FROM A FULL JOIN B ON A.key=B.key WHERE A.key is null OR B.key is null;
Select upper() from lower(); select upper() from lower()
select lower(e.first_name) from employees e;
select upper(e.first_name) from employees e;
Concat () character concatenation function
select concat(e.first_name, e.last_name) from employees e;
Substr () intercepts the specified character
select substr(e.first_name, 1, 4) from employees e;
Instr () looks for the specified character position
Select instr(e.irst_name, ‘s’) from employees e;
Length () counts the character length function
select length(e.first_name) from employees e;
Lpad () shows that 10 bits are not enough to fill the front *
Select lpad(e.ferst_name, 10, ‘*’) from employees e;
Rpad () shows that 10 bits are insufficient after completion *
Select rpad(e.ferst_name, 10, ‘*’) from employees e;
Trim () removes leading and trailing Spaces or does not remove leading and trailing Spaces by default
Select trim(‘ N ‘from E.trist_name) from employees e; select trim(‘ N’ from E.trist_name) from employees e;
select trim(e.first_name) from employees e;
Example: wq replaces the specified character e
Select replace(e.ferst_name, ‘e’, ‘wq’) from employees e;
The mathematical function round() is rounded to save two decimal places
Select round (45.33445, 2)
Truncate () truncates to hold tens
The select truncate (45.33445, 1)
Mod () for remainder divided by sample remainder 3
select mod(45, 7)
The date function now() gets the current time
select now();