[Bases de Dados II] #sql

comandos.sql

--Insert files into dept from csv file
LOAD DATA LOCAL INFILE 'C:\\Users\\danie\\Desktop\\db2.csv' INTO TABLE dept
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'

LOAD DATA LOCAL INFILE 'C:\\Users\\danie\\Desktop\\db21.csv' INTO TABLE emp
FIELDS TERMINATED BY ','
ENCLOSED BY ''
LINES TERMINATED BY '\r\n';



To run SQL programs/s from a '.sql' file
1. Edit a .sql file, e.g.:

~$ nano count_usr.sql 
use mysql;
select count(*) from user;
use db1;
~$ ls *.sql 
count_usr.sql
~$
-
MariaDB [db1]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
-- To avoid that, launch your client with the -A option (or --no-auto-rehash)
E.g.:
$ sudo mariadb -h localhost -u root -A
[sudo] password for ice-o-star: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 38
Server version: 10.4.12-MariaDB-1:10.4.12+maria~eoan-log mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [mysql]> source count_usr.sql
Database changed
+------------------------+
| Number of active users |
+------------------------+
|                      2 |
+------------------------+
1 row in set (0.002 sec)

Database changed
MariaDB [db1]> 
/----------------------------------------------------------------------------------------------------------------/
/----------------------------------------------------------------------------------------------------------------/
https://demo.phpmyadmin.net/master-config/
https://mariadb.com/kb/en/mysql-command-line-client/ 

mysql Commands:
There are also a number of commands that can be run inside the client. Note that all text commands 
must be first on line and end with ';'
Command	          Definition
?, \?                           Synonym for `help'.
clear, \c                     Clear the current input statement. -- VERY HANDY 
connect, \r                 Reconnect to the server. Optional arguments are db and host.
delimiter, \d               Set statement delimiter.
edit, \e                       Edit command with $EDITOR.
ego, \G                      Send command to mysql server, display result vertically.
exit, \q                       Exit mysql. Same as quit.
go, \g                         Send command to mysql server.
help, \h                      Display this help.
nopager, \n                Disable pager, print to stdout.
notee, \t                     Don't write into outfile.
pager, \P                   Set PAGER [to_pager]. Print the query results via PAGER.
print, \p                     Print current command.
prompt, \R                Change your mysql prompt. See prompt command for options.
quit, \q                      Quit mysql.
rehash, \#                 Rebuild completion hash.
source, \.                  Execute an SQL  file. Takes a file name as an argument.
status, \s                  Get status information from the server.
system, \!                 Execute a system shell command. Only works in Unix-like systems.
tee, \T                      Set outfile [to_outfile]. Append everything into given outfile.
use, \u                     Use another database. Takes database name as argument.
charset, \C               Switch to another charset. Might be needed for processing binlog 
                                   with multi-byte charsets.
warnings, \W           Show warnings after every statement.
nowarning, \w          Don't show warnings after every statement.
/----------------------------------------------------------------------------------------------------------------/
https://mariadb.com/kb/en/connect-data-types/ 
https://mariadb.com/kb/en/format/


/----------------------------------------------------------------------------------------------------------------/
CREATE TABLE IF NOT EXISTS comissao (
  Id int(2) NOT NULL,
  Id_Msg int(2) NOT NULL,
  Valor decimal(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO comissao (Id, Id_Msg, Valor) 
VALUES
('14', '10', '10500'),
('25', '10', '2500'),
('14', '100', '3750'),
('14', '70', '400'),
('37', '40', '20'),
('37', '30', '14230'),
('37', '10', '5500'),
('14', '60', '2600'),
('25', '30', '370'),
('40', '20', '20'),
('37', '50', '120'),
('42', '20', '170'),
('49', '20', '2300');
--
CREATE TABLE IF NOT EXISTS mensagem (
  Id_Msg tinyint UNSIGNED NOT NULL,
  Mensagem char(30) NOT NULL,
  PRIMARY KEY (Id_Msg)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO mensagem (Id_Msg, Mensagem) 
VALUES
(10, 'Comissão de Vendas'),
(20, 'Fretes Individuais'),
(30, 'Fretes Empresas'),
(40, 'Vendas Extra'),
(50, 'Deslocações'),
(60, 'Refeições'),
(70, 'Combustíveis'),
(80, 'Transportes'),
(90, 'Telefonemas'),
(100, 'Ofertas');
--
CREATE TABLE IF NOT EXISTS funcionario (
  Id smallint UNSIGNED NOT NULL,
  Nome char(30) NOT NULL,
  data_nasc date NOT NULL,
  Salario decimal(10,2) NOT NULL,
  Telefone char(13) DEFAULT NULL, 
  Cod_Postal mediumint UNSIGNED NOT NULL,
  PRIMARY KEY (Id),
  KEY Cod_Postal (Cod_Postal)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
INSERT INTO funcionario (Id, Nome, data_nasc, Salario, Telefone, Cod_Postal) 
VALUES
('5', 'Célia Morais', '1995-10-20', '170000.00', '+35191657106', '1100'),
('14', 'Nascimento Augusto','1985-01-09', '220000.00', '+35191657987', '2300'),
('25', 'Paulo Viegas', '1987-06-30', '95000.00', NULL, '1500'),
('32', 'Florinda Simões', '1960-12-20', '147000.00', NULL, '4000'),
('37', 'Isabel Espada', '1988-07-25', '86000.00', NULL, '1100'),
('42', 'António Dias', '1970-04-05', '74000.00', '+35193657199', '1500'),
('49', 'José António', '2003-04-04', '210000.00', NULL, '1500');
--
CREATE TABLE IF NOT EXISTS postal (
  Codigo mediumint NOT NULL,
  Local char(30) NOT NULL,
  PRIMARY KEY (Codigo)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
INSERT INTO postal (Codigo, Local) 
VALUES
('1000', 'LISBOA'),
('1100', 'LISBOA'),
('1200', 'LISBOA'),
('1500', 'LISBOA'),
('2000', 'SANTAREM'),
('2300', 'TOMAR'),
('3000', 'COIMBRA'),
('4000', 'PORTO'),
('9000', 'FUNCHAL');
--
CREATE TABLE IF NOT EXISTS postal (
  Codigo mediumint UNSIGNED NOT NULL,
  Local char(30) NOT NULL,
  PRIMARY KEY (Codigo)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
INSERT INTO postal (Codigo, Local) 
VALUES
('1000', 'LISBOA'),
('1100', 'LISBOA'),
('1200', 'LISBOA'),
('1500', 'LISBOA'),
('2000', 'SANTAREM'),
('2300', 'TOMAR'),
('3000', 'COIMBRA'),
('4000', 'PORTO'),
('9000', 'FUNCHAL');
--
ALTER TABLE funcionario
  ADD CONSTRAINT funcionario_ibfk_1 FOREIGN KEY (Cod_Postal) 
  REFERENCES postal (Codigo);
/----------------------------------------------------------------------------------------------------------------/
select id, nome, data_nasc, telefone, local
  from funcionario, postal
  where cod_postal=codigo; 
---
select *  from comissao  
where id<40  
order by id desc, valor desc;
/----------------------------------------------------------------------------------------------------------------/
select f.nome, (Year(CURDATE())-Year(f.data_nasc)) + 1 AS idade2 from funcionario f;
-
select nome NOME, Year(CURDATE())-Year(f.data_nasc) IDADE, IDADE+1 "Idade Proximo Ano"
from funcionario f 
order by 3;
-
select nome AS Nome, @IDADE := Year(CURDATE())-Year(f.data_nasc) AS Idade, @IDADE+1 AS "Idade Proximo Ano"
from funcionario f 
order by 3;
- 
create database db2 charset latin1 collate latin1_swedish_ci;
-
create table  dept 
(depno tinyint unsigned not null, 
dname char(12) not null , 
loc char(12) not null , 
 constraint depno_key primary key (depno) 
)engine=innodb;
-
10,Accounting,New York
20,Research,Dallas
30,Sales,Chicago
40,Operations,Boston
-
load data infile '/home/ice-o-star/dept.csv' 
into table db2.dept
fields terminated by ',' 
enclosed by '"' 
lines terminated by '\n';
-
INSERT INTO dept (depno, dname,loc) 
 VALUES 
(10,'Accounting','New York'), 
(20,'Research','Dallas'), 
(30,'Sales','Chicago'), 
(40,'Operations','Boston');
-
create table  projects 
(projid smallint unsigned not null, 
p_desc char(25), 
p_start_date date, 
p_end_date date, 
budget_amount numeric(7,2), 
max_no_staff tinyint, 
comments text(100), 
 constraint proj_key primary key (projid) 
)engine=innodb default character set latin1 
   collate latin1_swedish_ci;
-
PROJID,P_DESC,P_START_DATE,P_END_DATE,BUDGET_AMOUNT,MAX_NO_STAFF
1,'Hydro-geological study','2010-02-15','2014-01-01,50000,5,'Start phase'
2,'Project River-Bridge','2013-04-10','2016-02-28',60000,8,'Start phase'
3,'Project Highway W2','2016-03-01','2019-02-28',60000,7,''
-
load data infile '/home/ice-o-star/projects.csv' 
into table db2.projects
fields terminated by ',' 
enclosed by '"' 
lines terminated by '\n';
-
INSERT INTO projects 
 VALUES 
(1,'Hydro-geological study','2010-02-15','2014-01-01',50000,5,'Start phase'),
(2,'Project River-Bridge','2013-04-10','2016-02-28',60000,8,'Start phase'),
(3,'Project Highway W2','2016-03-01','2019-02-28',60000,7,'');
--
create table  emp 
(empno smallint unsigned not null, 
ename char(12) not null, 
job char(12) not null, 
mgr smallint unsigned not null, 
hiredate date default current_date(),  
sal numeric(8,2), 
comm numeric(8,2), 
depno tinyint unsigned not null, 
 constraint val_sal check (sal between 500 and 6000), 
 constraint emp_key primary key (empno) 
)engine=innodb;
-
emp.csv

EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPNO
7521,Ward,Salesman,7782,'2000-01-01',1250,1500,30
7566,Jones,Manager,7839,'1999-10-01',2975,,20
7654,Martin,Salesman,7782,'2010-06-01',1250,2400,30
7698,Blake,Manager,7839,'1999-08-01',3850,,30
7782,Clark,Manager,7839,'2011-10-01',3450,,10
7788,Scott,Analyst,7782,'2012-03-01',3000,,20
7839,King,President,,'2001-01-01',5000,,10
7844,Turner,Salesman,7782,'2015-02-01',1500,1500,30
7876,Adams,Clerk,7566,'2007-03-01',1800,,20
7900,James,Clerk,7566,'2004-12-01',1950,,30
7902,Ford,Analyst,7782,'2015-09-01',3000,,20
7934,Miller,Clerk,7566,'2018-01-01',1800,,10
7369,Smith,Clerk,7566,'2009-04-01',1800,,20
7499,Allen,Salesman,7782,'2012-08-01',1600,1300,30
-
INSERT INTO emp 
VALUES 
(7521,'Ward','Salesman',7782,'2000-01-01',1250,1500,30),
(7566,'Jones','Manager',7839,'1999-10-01',2975,NULL,20),
(7654,'Martin','Salesman',7782,'2010-06-01',1250,2400,30),
(7698,'Blake','Manager',7839,'1999-08-01',3850,NULL,30),
(7782,'Clark','Manager',7839,'2011-10-01',3450,NULL,10),
(7788,'Scott','Analyst',7782,'2012-03-01',3000,NULL,20),
(7839,'King','President','','2001-01-01',5000,NULL,10),
(7844,'Turner','Salesman',7782,'2015-02-01',1500,1500,30),
(7876,'Adams','Clerk',7566,'2007-03-01',1800,NULL,20),
(7900,'James','Clerk',7566,'2004-12-01',1950,NULL,30),
(7902,'Ford','Analyst',7782,'2015-09-01',3000,NULL,20),
(7934,'Miller','Clerk',7566,'2018-01-01',1800,NULL,10),
(7369,'Smith','Clerk',7566,'2009-04-01',1800,NULL,20),
(7499,'Allen','Salesman',7782,'2012-08-01',1600,1300,30);
/----------------------------------------------------------------------------------------------------------------/
create the file (/home/ios/salgrade.csv):

1,700,1400
2,1401,1600
3,1601,2000
4,2001,3000
5,3001,9999
--
create table  salgrade 
(grade tinyint unsigned not null, 
losal numeric(7,2) not null, 
hisal numeric(7,2) not null, 
 constraint grade_key primary key (grade) 
)engine=innodb;
-
MariaDB [db2]> load data local infile '/home/ice-o-star/salgrade.csv' 
                                       -- 'local' was missed....................................LINUX WORKS!!
    -> into table db2.salgrade
    -> fields terminated by ',' 
    -> enclosed by '"'
    -> lines terminated by '\n';
Query OK, 5 rows affected (0.011 sec)                
Records: 5  Deleted: 0  Skipped: 0  Warnings: 0

MariaDB [db2]> select * from salgrade;  -- NOW YOU SEE, YOUR "WINDOWS BELIEVERS" 
+-------+-------+-------+
| grade | losal | hisal |
+-------+-------+-------+
|     1 |   700 |  1400 |
|     2 |  1401 |  1600 |
|     3 |  1601 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.001 sec)
--
MariaDB [db1]> select host,
    ->        user as username,
    ->        password,
    ->        password_expired
    -> from mysql.user
    -> order by user;
+-----------+----------+----------+------------------+
| Host      | username | Password | password_expired |
+-----------+----------+----------+------------------+
| localhost | mysql    | invalid  | N                |
| localhost | root     | invalid  | N                |
+-----------+----------+----------+------------------+
2 rows in set (0.003 sec) 
--

/----------------------------------------------------------------------------------------------------------------/
PLEASE DON'T USE THE SPACE ABOVE ** 
PLEASE DON'T CHANGE THE ENTRIES WRITTEN ABOVE 
/----------------------------------------------------------------------------------------------------------------/

--- DANIEL ---
LOAD DATA LOCAL INFILE 'C:\\Users\\danie\\Desktop\\DB2.csv' INTO TABLE dept
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'

LOAD DATA LOCAL INFILE 'C:\\Users\\bruno\\OneDrive\\Desktop\\db2.csv' INTO TABLE dept
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'

C:\Users\bruno\OneDrive\Desktop\DB2.csv
C:\Users\bruno\OneDrive\Desktop\db2.csv



LOAD DATA LOCAL INFILE 'C:\\Users\\danie\\Desktop\\db21.csv' INTO TABLE emp
FIELDS TERMINATED BY ','
ENCLOSED BY ''
LINES TERMINATED BY '\r\n';

--- db21.csv ---
7521,Ward,Salesman,7782,2000-01-01,1250,1500,30
7566,Jones,Manager,7839,1999-10-01,2975,,20
7654,Martin,Salesman,7782,2010-06-01,1250,2400,30
7698,Blake,Manager,7839,1999-08-01,3850,,30
7782,Clark,Manager,7839,2011-10-01,3450,,10
7788,Scott,Analyst,7782,2012-03-01,3000,,20
7839,King,President,,2001-01-01,5000,,10
7844,Turner,Salesman,7782,2015-02-01,1500,1500,30
7876,Adams,Clerk,7566,2007-03-01,1800,,20
7900,James,Clerk,7566,2004-12-01,1950,,30
7902,Ford,Analyst,7782,2015-09-01,3000,,20
7934,Miller,Clerk,7566,2018-01-01,1800,,10
7369,Smith,Clerk,7566,2009-04-01,1800,,20
7499,Allen,Salesman,7782,2012-08-01,1600,1300,30 
--