---
# System prepended metadata

title: SQL

---

# SQL
comandos terminam em ";"
show databases;
create database nova;
use nova;
show tables;

create table tabela1 (id int, nome varchar(100));

desc tabela1;
(descreve)

insert into tabela1 (id, nome) values(1, 'Ricardo');

select id,nome
from tabela1;

create table tabela (id int not null primary key auto_increment, nome varchar(100));


update tabela2  set nome='gil' where id=1;
delete from tabela2 where id>2 and more='Gil'

source c:/sql/hr.sql;
#importa comandos
 select max(salary) from employees;

select min(salary) from employees;
count (conta quantos há)
sum (soma tudo)
avg (media)
select first_name from employees where salary > (select avg(salary)from employees);


select first name, (select avg(salary)from employees), salary * 2 from employees;

alias= as total
exemplo: select first name, (select avg(salary)from employees) as total, salary * 2 from employees;


select first_name, last_name, department_name from employees, departments where employees.department_id=departments.departments_id

uma maneira diferente de fazer o de cima:

select first_name, last_name, department_name, region_name from employees left join departments on employees.department_id=departments.department_id left join locations on departments.location_id=locations.location_id left join countries on locations.country_id=countries.country_id left join regions on countries.region_id=regions.region_id;

select first_name, last_name, department_name, salary from employees left join departments on employees.department_id=departments.department_id where salary >(select avg(salary) from employees);


select first_name, last_name, department_name, salary from employees where salary >(select avg(salary) from employees where department_name='IT');

https://www.w3resource.com/mysql-exercises/
 mostrar quem ganha mais de todos os departamentos
 
 
 
 
 
 select first_name, last_name from employees 
 
 
 select=escolher informaçao de tabelas
 from = indicar qual a tabela
 join = buscar info de outras tabelas
 where (if) = por condiçoes 
 order by = ordernar asc /  des
 
 left('string',num)=>string
 left('Portugal',4)=>Port
 right('Portugal',4)=>ugal
 
 length('string')=
 length('portugal')=8
 
 reverse (string)->string
 reverse ('portugal')= lagutrop
 
 locate(string, string[num])->num
 locate('tuga','portugal')->4
 locate ('a','portugal')->7
 locate ('a', 'espanha',5)=>7
 
 
 locate ('a',string,locate('a',string)+1)
 
 exercisio:
 ficheiro.html
 documento.xls
 carta.docx
 outro.tar.gz
 pasta.tx