Last active
August 13, 2022 11:23
-
-
Save abdulrahmanAlotaibi/0c2a20dc04d97059ee935bd8dc2c1d25 to your computer and use it in GitHub Desktop.
SQL Cheatsheet for MySQL client CLI
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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