SQL Cheatsheet for MySQL client CLI

cheatsheet.sql

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

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