Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQL Cheatsheet for MySQL client CLI
create table table_name (
id integer primary key
name varchar(255)
);
insert into table_name (column1,c2) values ('','') ;
drop user '<user_name>'@'localhost';
update table_name set first_name = value where id = '1';
alter table employees add new_column varchar(255)
alter table employees modify column varchar(255)
select * from employees as e inner join dept_emp as de on e.emp_no = de.emp_no inner join departments as d on d.dept_no = de.dept_no limit 1;
select * from employees order by id desc limit 1;
select *, users.id as userrr from users inner join tasks on tasks.user_id = users.id ;
select * from employees where age between (20,49)
-- a virtual table based on the result of an sql query
create view virtual_emp as select * from employees;
-- Creating and droping indexes
-- Creating prodecures
Sorting null valurs (if null is less with 0 (last) if you are desc)
select ename,sal,comm
2 from (
3 select ename,sal,comm,
4 case when comm is null then 0 else 1 end as is_null
5 from emp
6 ) x
7 order by is_null desc,comm
ENAME SAL COMM
------ ----- ----------
TURNER 1500 0
ALLEN 1600 300
WARD 1250 500
MARTIN 1250 1400
SMITH 800
JONES 2975
JAMES 950
MILLER 1300
FORD 3000
ADAMS 1100
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
--- sort based on condition
select ename,sal,job,comm
2 from emp
3 order by case when job = 'SALESMAN' then comm else sal end
select customer,first_name,last_name,age,country, shipping_id, status from (
select status, customer, shipping_id from shippings where status = 'Pending'
)
left join customers where customer_id = customer
@abdulrahmanAlotaibi
Copy link
Author

abdulrahmanAlotaibi commented Aug 13, 2022

Use the COALESCE function to convert NULLs to zero so they will be included in
the aggregation:
1 select avg(coalesce(comm,0)) as avg_comm
2 from emp
3 where deptno=30

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment