Introdução aos Bancos de Dados Relacionais

Transcript

1. BANCOS DE DADOS RELACIONAIS
2. TABELA
3. LINHA
4. COLUNA
5. matricula,nome,telefone,nacionalidade 000001,Alexandre,81333388,brasileiro 000002,Zeca,9123745,brasileiro 000003,Paulinho,99736544,frances
6. matricula,nome,telefone,nacionalidade 000001,Alexandre,81333388,brasileiro 000002,Zeca,9123745,brasileiro 000003,Paulinho,99736544,frances matricula nome telefone nacionalidade 000001 Alexandre 81333388 brasileiro 000002 Zeca 9123745 brasileiro 000003 Paulinho 99736544 frances
7. { matricula: 1, nome: ‘Alexandre', telefone: ‘81338383’}, { matricula: 2, nome: ‘Zeca', telefone: ‘9123745’}, { matricula: 3, nome: ‘Paulinho', telefone: ‘99736544’}
8. { matricula: 1, nome: ‘Alexandre', telefone: ‘81338383’}, { matricula: 2, nome: ‘Zeca', telefone: ‘9123745’}, { matricula: 3, nome: ‘Paulinho', telefone: ‘99736544’}
9. { matricula: 1, nome: ‘Alexandre', telefone: ‘81338383’}, { matricula: 2, nome: ‘Zeca', telefone: ‘9123745’}, { matricula: 3, nome: ‘Paulinho', telefone: ‘99736544’}
10. { matricula: 1, nome: ‘Alexandre', telefone: ‘81338383’}, { matricula: 2, nome: ‘Zeca', telefone: ‘9123745’}, { matricula: 3, nome: ‘Paulinho', telefone: ‘99736544’}
11. { matricula: 1, nome: ‘Alexandre', telefone: ‘81338383’}, { matricula: 2, nome: ‘Zeca', telefone: ‘9123745’}, { matricula: 3, nome: ‘Paulinho', telefone: ‘99736544’} matricula nome telefone 000001 Alexandre 81333388 000002 Zeca 9123745 000003 Paulinho 99736544
12. TABELA 1 TABELA 2 TABELA 3 BANCO DE DADOS
13. BANCO DE DADOS
14. SISTEMAS GERENCIADORES DE BANCOS DE DADOS
16. SQL STRUCTURED QUERY LANGUAGE
18. create database dojodb;
19. create table tweets ( id varchar(20), user varchar(255), text varchar(140) ); id user text
20. insert into tweets(id,user,text) values ( "675870416917962754", "mgrif14", "RT @br45ilnocorrupt: Transmissao #BrazilNoCorrupt #PovoNaRua13Dez #ForaDilma @lobaoeletrico @VEJA" ); id user text 67587 mgrif RT…
21. insert into tweets(id,user,text) values ( "99999999999999", "ale", “Viva a Vida!” ); id user text 67587 mgrif RT… 99999 ale Viva
22. 99999 ale Viva id user text select * from tweets; 67587 mgrif RT…
23. 99999 ale Viva id user text select * from tweets; 67587 mgrif RT… select id from tweets;
24. 99999 ale Viva id user text select * from tweets; 67587 mgrif RT… select id from tweets; select id,user from tweets;
25. 67587 mgrif RT… 99999 ale Viva id user text select * from tweets; select id from tweets; select id,user from tweets; select id,user from tweets where id = "6758";
26. 67587 mgrif RT… 99999 ale Viva id user text select * from tweets; select id from tweets; select id,user from tweets; select id,user from tweets where id = "6758"; select user from tweets where id = “6758" or text = "Viva";
28. INSTALAÇÃO http://dev.mysql.com/downloads/mysql/
29. $ mysql.server start Starting MySQL .. SUCCESS!
30. $ mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.12 Homebrew Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
31. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec)
32. mysql> create database dojodb; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | dojodb | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.01 sec) mysql> use dojodb; Database changed
33. mysql> show tables; Empty set (0.00 sec) mysql> create table tweets ( id varchar(20), user_from_id varchar(20), user_screenname varchar(255), text varchar(140)); Query OK, 0 rows affected (0.02 sec) mysql> show tables; +------------------+ | Tables_in_dojodb | +------------------+ | tweets | +------------------+ 1 row in set (0.00 sec)
34. mysql> desc tweets; +-----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+-------+ | id | varchar(20) | YES | | NULL | | | user_from_id | varchar(20) | YES | | NULL | | | user_screenname | varchar(255) | YES | | NULL | | | text | varchar(140) | YES | | NULL | | +-----------------+--------------+------+-----+---------+-------+ 4 rows in set (0.03 sec)
35. mysql> select * from tweets; Empty set (0.00 sec) mysql> insert into tweets (id, user_from_id, user_screenname,text) values ( "675870416917962754", "2679713815", "mgrif14", "RT @br45ilnocorrupt: Transmissao #BrazilNoCorrupt AMANHA #PovoNaRua13Dez #ForaDil Query OK, 1 row affected (0.01 sec) mysql> select * from tweets; +--------------------+--------------+-----------------+--------------------------------------- | id | user_from_id | user_screenname | text +--------------------+--------------+-----------------+--------------------------------------- | 675870416917962754 | 2679713815 | mgrif14 | RT @br45ilnocorrupt: Transmissao #Braz +--------------------+--------------+-----------------+--------------------------------------- 1 row in set (0.00 sec)
36. mysql> insert into tweets (id, user_from_id, user_screenname,text) values ("999999999999999999", "9999999999", Query OK, 1 row affected (0.01 sec) mysql> insert into tweets (id, user_from_id, user_screenname,text) values ("888888888888888888", "8888888888", Query OK, 1 row affected (0.01 sec) mysql> insert into tweets (id, user_from_id, user_screenname,text) values ("777777777777777777", "7777777777", Query OK, 1 row affected (0.00 sec) mysql> select * from tweets; +--------------------+--------------+-----------------+-------------------------------------------------------- | id | user_from_id | user_screenname | text +--------------------+--------------+-----------------+-------------------------------------------------------- | 675870416917962754 | 2679713815 | mgrif14 | RT @br45ilnocorrupt: Transmissao #BrazilNoCorrupt AMANH | 999999999999999999 | 9999999999 | nove | nove neuf nine IX nove neuf nine IX nove neuf nine IX n | 888888888888888888 | 8888888888 | nove | oito huit eight VIII oito huit eight VIII oito huit eig | 777777777777777777 | 7777777777 | nove | sete sept seven VII sete sept seven VII +--------------------+--------------+-----------------+-------------------------------------------------------- 4 rows in set (0.00 sec)
37. mysql> select * from tweets; +--------------------+--------------+-----------------+-------------------------------------------------- | id | user_from_id | user_screenname | text +--------------------+--------------+-----------------+-------------------------------------------------- | 675870416917962754 | 2679713815 | mgrif14 | RT @br45ilnocorrupt: Transmissao #BrazilNoCorrupt | 999999999999999999 | 9999999999 | nove | nove neuf nine IX nove neuf nine IX nove neuf nin | 888888888888888888 | 8888888888 | nove | oito huit eight VIII oito huit eight VIII oito hu | 777777777777777777 | 7777777777 | nove | sete sept seven VII sete sept seven VII +--------------------+--------------+-----------------+-------------------------------------------------- 4 rows in set (0.01 sec) mysql> update tweets set user_screenname="oito" where id = "8888888888"; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from tweets; +--------------------+--------------+-----------------+-------------------------------------------------- | id | user_from_id | user_screenname | text +--------------------+--------------+-----------------+-------------------------------------------------- | 675870416917962754 | 2679713815 | mgrif14 | RT @br45ilnocorrupt: Transmissao #BrazilNoCorrupt | 999999999999999999 | 9999999999 | nove | nove neuf nine IX nove neuf nine IX nove neuf nin | 888888888888888888 | 8888888888 | oito | oito huit eight VIII oito huit eight VIII oito hu | 777777777777777777 | 7777777777 | nove | sete sept seven VII sete sept seven VII +--------------------+--------------+-----------------+-------------------------------------------------- 4 rows in set (0.01 sec) mysql> update tweets set user_screenname="sete" where id like "7%"; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> select * from tweets; +--------------------+--------------+-----------------+-------------------------------------------------- | id | user_from_id | user_screenname | text +--------------------+--------------+-----------------+-------------------------------------------------- | 675870416917962754 | 2679713815 | mgrif14 | RT @br45ilnocorrupt: Transmissao #BrazilNoCorrupt | 999999999999999999 | 9999999999 | nove | nove neuf nine IX nove neuf nine IX nove neuf nin | 888888888888888888 | 8888888888 | oito | oito huit eight VIII oito huit eight VIII oito hu | 777777777777777777 | 7777777777 | sete | sete sept seven VII sete sept seven VII +--------------------+--------------+-----------------+-------------------------------------------------- 4 rows in set (0.01 sec)
38. mysql> select id, user_screenname from tweets where id = "888888888888888888" or user_screenname = "sete"; +--------------------+-----------------+ | id | user_screenname | +--------------------+-----------------+ | 888888888888888888 | oito | | 777777777777777777 | sete | +--------------------+-----------------+ 2 rows in set (0.00 sec) mysql> select id from tweets where id = "888888888888888888" or user_screenname = "sete"; +--------------------+ | id | +--------------------+ | 888888888888888888 | | 777777777777777777 | +--------------------+ 2 rows in set (0.00 sec) mysql> select sum(id) from tweets where id = "888888888888888888" or user_screenname = "sete"; +-----------------------+ | sum(id) | +-----------------------+ | 1.6666666666666665e18 | +-----------------------+ 1 row in set (0.01 sec) mysql> select count(id) from tweets where id = "888888888888888888" or user_screenname = "sete"; +-----------+ | count(id) | +-----------+ | 2 | +-----------+ 1 row in set (0.00 sec)
40. Python Programa SGBD
41. Python Programa SGBD Pacote de Acesso a Banco de Dados
42. Python Programa SGBD Records
43. $ pip install wheel pymysql records Collecting wheel Using cached wheel-0.29.0-py2.py3-none-any.whl Collecting pymysql Using cached PyMySQL-0.7.4-py2.py3-none-any.whl Collecting records Using cached records-0.4.3-py2.py3-none-any.whl Requirement already satisfied (use --upgrade to upgrade): tablib in /Users/alegomes/.pyenv/versions/ipol-dojo/lib/python3.5/site- packages (from records) Requirement already satisfied (use --upgrade to upgrade): docopt in /Users/alegomes/.pyenv/versions/ipol-dojo/lib/python3.5/site- packages (from records) Requirement already satisfied (use --upgrade to upgrade): SQLAlchemy in /Users/alegomes/.pyenv/versions/ipol-dojo/lib/ python3.5/site-packages (from records) Installing collected packages: wheel, pymysql, records Successfully installed pymysql-0.7.4 records-0.4.3 wheel-0.29.0
44. $ python Python 3.5.0 (default, Dec 10 2015, 21:32:09) [GCC 4.2.1 Compatible Apple LLVM 7.0.0 (clang-700.1.76)] on darwin Type "help", "copyright", "credits" or "license" for more information. >>> import records >>> import pymysql >>> pymysql.install_as_MySQLdb() >>> db = records.Database("mysql://root:@localhost/dojodb") sgbd:/ /usuario:senha@servidor/bancodedados
45. >>> rows = db.query('select * from tweets') >>> rows[0] <Record {"id": "675870416917962754", "user_from_id": "2679713815", "user_ Transmissao #BrazilNoCorrupt AMANHA #PovoNaRua13Dez #ForaDilma @lobaoelet >>> rows[1] <Record {"id": "999999999999999999", "user_from_id": "9999999999", "user_ nine IX nove neuf nine IX nove neuf nine IX nove neuf nine IX "}> >>> rows[2] <Record {"id": "888888888888888888", "user_from_id": "8888888888", "user_ eight VIII oito huit eight VIII oito huit eight VIII”}> >>> rows[3] <Record {"id": "777777777777777777", "user_from_id": "7777777777", "user_ "sete sept seven VII sete sept seven VII"}> >>> rows[4] Traceback (most recent call last): File "<stdin>", line 1, in <module> File "/Users/alegomes/.pyenv/versions/ipol-dojo/lib/python3.5/site-pack return rows[0] IndexError: list index out of range >>> rows.all() [<Record {"id": "675870416917962754", "user_from_id": "2679713815", "user #BrazilNoCorrupt AMANHA #PovoNaRua13Dez #ForaDilma @lobaoeletrico @VEJA"} "user_screenname": "nove", "text": "nove neuf nine IX nove neuf nine IX n "user_from_id": "8888888888", "user_screenname": "oito", "text": "oito hu "777777777777777777", "user_from_id": "7777777777", "user_screenname": "s
46. >>> rows[0].id '675870416917962754' >>> rows[0].user_screenname 'mgrif14' >>> for row in rows: ... print(row.id,row.user_screenname) ... 675870416917962754 mgrif14 999999999999999999 nove 888888888888888888 oito 777777777777777777 sete >>> for row in rows: ... print("%s,%s" % (row.id,row.user_screenname)) ... 675870416917962754,mgrif14 999999999999999999,nove 888888888888888888,oito 777777777777777777,sete
47. >>> print(rows.dataset) id |user_from_id|user_screenname|text ------------------|------------|---------------|--------------------------------------------- 675870416917962754|2679713815 |mgrif14 |RT @br45ilnocorrupt: Transmissao #BrazilNoCor 999999999999999999|9999999999 |nove |nove neuf nine IX nove neuf nine IX nove neuf 888888888888888888|8888888888 |oito |oito huit eight VIII oito huit eight VIII oit 777777777777777777|7777777777 |sete |sete sept seven VII sete sept seven VII >>> print(rows.export('csv')) id,user_from_id,user_screenname,text 675870416917962754,2679713815,mgrif14,RT @br45ilnocorrupt: Transmissao #BrazilNoCorrupt AMANH 999999999999999999,9999999999,nove,nove neuf nine IX nove neuf nine IX nove neuf nine IX nove 888888888888888888,8888888888,oito,oito huit eight VIII oito huit eight VIII oito huit eight 777777777777777777,7777777777,sete,sete sept seven VII sete sept seven VII >>> print(rows.export('json')) [{"id": "675870416917962754", "user_from_id": "2679713815", "user_screenname": "mgrif14", "te #PovoNaRua13Dez #ForaDilma @lobaoeletrico @VEJA"}, {"id": "999999999999999999", "user_from_id nine IX nove neuf nine IX nove neuf nine IX nove neuf nine IX "}, {"id": "888888888888888888" "text": "oito huit eight VIII oito huit eight VIII oito huit eight VIII"}, {"id": "7777777777 "sete", "text": "sete sept seven VII sete sept seven VII"}] >>> f = open('tweets.xls', 'wb') >>> f.write(rows.export('xls')) 5632 >>> $ ls -l *.xls -rw-r--r-- 1 alegomes staff 5632 Jun 9 08:40 tweets.xls
49. >>> rows = db.query('select * from tweets') >>> rows.all() [<Record {"id": "675870416917962754", "user_from_id": "2679713815", "user_screenname <Record {"id": "999999999999999999", "user_from_id": "9999999999", "user_screenname" "user_from_id": "8888888888", "user_screenname": "oito", "text": "oito huit eight VI "user_screenname": "sete", "text": "sete sept seven VII sete sept seven VII"}>] >>> rows = db.query("insert into tweets values(‘111111111111111111’,’1111111111’ one')") mysql> select * from tweets; +--------------------+--------------+-----------------+----------------------------- | id | user_from_id | user_screenname | text +--------------------+--------------+-----------------+----------------------------- | 675870416917962754 | 2679713815 | mgrif14 | RT @br45ilnocorrupt: Transmi | 999999999999999999 | 9999999999 | nove | nove neuf nine IX nove neuf | 888888888888888888 | 8888888888 | oito | oito huit eight VIII oito hu | 777777777777777777 | 7777777777 | sete | sete sept seven VII sete sep +--------------------+--------------+-----------------+----------------------------- 4 rows in set (0.00 sec) mysql> select * from tweets; +--------------------+--------------+-----------------+---------------------------------- | id | user_from_id | user_screenname | text +--------------------+--------------+-----------------+---------------------------------- | 675870416917962754 | 2679713815 | mgrif14 | RT @br45ilnocorrupt: Transmissao | 999999999999999999 | 9999999999 | nove | nove neuf nine IX nove neuf nine | 888888888888888888 | 8888888888 | oito | oito huit eight VIII oito huit ei | 777777777777777777 | 7777777777 | sete | sete sept seven VII sete sept sev | 111111111111111111 | 1111111111 | um | um un uno one +--------------------+--------------+-----------------+---------------------------------- 5 rows in set (0.00 sec)
51. GABRIEL BASEADO EM FATOS REAIS
52. $ ls -lh ../ernesto/RESOCIE/Gabriel/DATOS\ MARISA\ MYSQL\ KATY\ enero\ 2016/ total 3743232 drwxrwxrwx 3 alegomes staff 102B Jan 22 15:18 PROYECTO MARISA scripts Katy y visualización enero 2016 -rwxrwxrwx 1 alegomes staff 11K Jan 8 14:17 wim2_dev_actors.sql -rwxrwxrwx 1 alegomes staff 1.3G Jan 8 14:19 wim2_dev_mentions.sql -rwxrwxrwx 1 alegomes staff 17M Jan 8 14:17 wim2_dev_protests.sql -rwxrwxrwx 1 alegomes staff 20M Jan 8 14:17 wim2_dev_ranking_tweets.sql -rwxrwxrwx 1 alegomes staff 169M Jan 8 14:17 wim2_dev_retweets.sql -rwxrwxrwx 1 alegomes staff 314M Jan 8 14:17 wim2_dev_tweets.sql
53. $ head wim2_dev_protests.sql -- MySQL dump 10.13 Distrib 5.6.22, for osx10.8 (x86_64) -- -- Host: localhost Database: wim2_dev -- ------------------------------------------------------ -- Server version 5.7.10 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */;
54. $ brew info mysql mysql: stable 5.7.12 (bottled) Open source relational database management system https://dev.mysql.com/doc/refman/5.7/en/ Conflicts with: mariadb, mariadb-connector-c, mysql-cluster, mysql-connector-c, percona-server /usr/local/Cellar/mysql/5.7.12 (13,281 files, 444.8M) * Poured from bottle on 2016-06-08 at 12:11:41 From: https://github.com/Homebrew/homebrew-core/blob/master/ Formula/mysql.rb ==> Dependencies Build: cmake ✘ Required: openssl ✔ ==> Options --with-archive-storage-engine Compile with the ARCHIVE storage engine enabled (…)
55. $ ls 50linhasfd.txt wim2_dev_protests.sql $ mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 5.7.12 Homebrew Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | dojodb | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> create database chiledb; Query OK, 1 row affected (0.01 sec) mysql> use chiledb; Database changed mysql> show tables; Empty set (0.00 sec)
56. mysql> source wim2_dev_protests.sql; Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.05 sec)
57. Query OK, 13979 rows affected (0.57 sec) Records: 13979 Duplicates: 0 Warnings: 0 Query OK, 13492 rows affected (0.43 sec) Records: 13492 Duplicates: 0 Warnings: 0 Query OK, 13581 rows affected (0.44 sec) Records: 13581 Duplicates: 0 Warnings: 0 Query OK, 13640 rows affected (0.46 sec) Records: 13640 Duplicates: 0 Warnings: 0 Query OK, 13533 rows affected (0.52 sec) Records: 13533 Duplicates: 0 Warnings: 0 Query OK, 13523 rows affected (1.16 sec) Records: 13523 Duplicates: 0 Warnings: 0 Query OK, 13760 rows affected (0.65 sec) Records: 13760 Duplicates: 0 Warnings: 0 Query OK, 13514 rows affected (0.63 sec) Records: 13514 Duplicates: 0 Warnings: 0 Query OK, 13507 rows affected (0.64 sec) Records: 13507 Duplicates: 0 Warnings: 0 Query OK, 13489 rows affected (0.61 sec) Records: 13489 Duplicates: 0 Warnings: 0
58. Query OK, 13538 rows affected (0.69 sec) Records: 13538 Duplicates: 0 Warnings: 0 Query OK, 13430 rows affected (0.61 sec) Records: 13430 Duplicates: 0 Warnings: 0 Query OK, 6467 rows affected (0.32 sec) Records: 6467 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec)
59. mysql> show tables; +-------------------+ | Tables_in_chiledb | +-------------------+ | protests | +-------------------+ 1 row in set (0.00 sec) mysql> desc protests; +--------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | to | varchar(191) | YES | MUL | NULL | | | from | varchar(191) | YES | | NULL | | | created_date | datetime | YES | | NULL | | | protest | date | YES | MUL | NULL | | | actor_id | int(11) | YES | | NULL | | +--------------+--------------+------+-----+---------+----------------+ 6 rows in set (0.00 sec) mysql> select count(*) from protests; +----------+ | count(*) | +----------+ | 236729 | +----------+ 1 row in set (0.07 sec)
60. mysql> select * from protests limit 100; +-----+------------------+-----------------+---------------------+------------+----------+ | id | to | from | created_date | protest | actor_id | +-----+------------------+-----------------+---------------------+------------+----------+ | 1 | @camila_vallejo | HenriquezNicole | 2011-05-12 04:23:16 | 2011-05-12 | 1 | | 2 | @camila_vallejo | HenriquezNicole | 2011-05-12 04:28:32 | 2011-05-12 | 1 | | 3 | @camila_vallejo | FranciscoYira_ | 2011-05-12 04:30:09 | 2011-05-12 | 1 | | 4 | @camila_vallejo | MaribelQR | 2011-05-12 21:32:31 | 2011-05-12 | 1 | | 5 | @camila_vallejo | gustavodv_ | 2011-05-12 22:04:44 | 2011-05-12 | 1 | | 6 | @camila_vallejo | aliroazocar | 2011-05-13 01:10:07 | 2011-05-12 | 1 | | 7 | @camila_vallejo | LETY1970 | 2011-05-14 19:41:15 | 2011-05-12 | 1 | | 8 | @camila_vallejo | CamiCardenas | 2011-05-15 16:09:52 | 2011-05-12 | 1 | | 9 | @Felipesalga | Gvovish | 2011-05-09 19:24:26 | 2011-05-12 | 5 | | 10 | @Felipesalga | abrlinconl | 2011-05-10 01:36:24 | 2011-05-12 | 5 | | 11 | @Felipesalga | Chik_Pizarro | 2011-05-10 01:37:44 | 2011-05-12 | 5 | | 12 | @Felipesalga | Darkyrian | 2011-05-10 01:38:15 | 2011-05-12 | 5 | | 13 | @Felipesalga | agathapca | 2011-05-10 01:38:57 | 2011-05-12 | 5 | | 14 | @Felipesalga | PaulinaHudson | 2011-05-10 01:39:05 | 2011-05-12 | 5 | | 15 | @Felipesalga | ProfeRaulOpazo | 2011-05-10 01:39:06 | 2011-05-12 | 5 | | 16 | @Felipesalga | lunitacaramelos | 2011-05-10 01:42:08 | 2011-05-12 | 5 | | 17 | @Felipesalga | enChileHoy | 2011-05-10 01:52:54 | 2011-05-12 | 5 | | 18 | @Felipesalga | Naiitta | 2011-05-10 01:53:32 | 2011-05-12 | 5 | | 19 | @Felipesalga | shuritox | 2011-05-10 15:44:36 | 2011-05-12 | 5 | | 20 | @Felipesalga | inti_illimani | 2011-05-12 13:19:34 | 2011-05-12 | 5 | | 21 | @Felipesalga | JenLopez_ | 2011-05-12 18:59:34 | 2011-05-12 | 5 | | 22 | @Felipesalga | gab000 | 2011-05-12 23:39:22 | 2011-05-12 | 5 | | 23 | @Felipesalga | KathyuskaNevada | 2011-05-13 01:02:54 | 2011-05-12 | 5 | | 24 | @ConstanzaLeyton | Abel_ORian | 2011-05-09 01:26:34 | 2011-05-12 | 9 | | 25 | @ConstanzaLeyton | Feuls | 2011-05-09 01:40:26 | 2011-05-12 | 9 | | 26 | @ConstanzaLeyton | Jaime_Guerra | 2011-05-09 03:11:28 | 2011-05-12 | 9 | | 27 | @ConstanzaLeyton | coniperez | 2011-05-10 15:46:04 | 2011-05-12 | 9 | | 28 | @ConstanzaLeyton | VictorHugo__23 | 2011-05-10 22:30:57 | 2011-05-12 | 9 | | 29 | @ConstanzaLeyton | Abel_ORian | 2011-05-11 03:34:01 | 2011-05-12 | 9 | | 30 | @ConstanzaLeyton | DaniaLazo | 2011-05-11 04:59:30 | 2011-05-12 | 9 | | 31 | @ConstanzaLeyton | Shlomitha | 2011-05-11 05:34:27 | 2011-05-12 | 9 | | 32 | @ConstanzaLeyton | shimbe | 2011-05-11 17:36:54 | 2011-05-12 | 9 | | 33 | @ConstanzaLeyton | Feuls | 2011-05-11 18:44:30 | 2011-05-12 | 9 |
61. | 65 | @GiorgioJackson | DaniValenccia | 2011-05-10 15:47:41 | 2011-05-12 | 15 | | 66 | @GiorgioJackson | valenzuelalevi | 2011-05-10 16:03:13 | 2011-05-12 | 15 | | 67 | @GiorgioJackson | vincegeratorix | 2011-05-10 16:17:01 | 2011-05-12 | 15 | | 68 | @GiorgioJackson | Feuc | 2011-05-11 14:29:14 | 2011-05-12 | 15 | | 69 | @GiorgioJackson | gonzpt | 2011-05-11 14:32:40 | 2011-05-12 | 15 | | 70 | @GiorgioJackson | cristobalotero | 2011-05-11 14:43:51 | 2011-05-12 | 15 | | 71 | @GiorgioJackson | LucecitaInfante | 2011-05-11 15:33:16 | 2011-05-12 | 15 | | 72 | @GiorgioJackson | nacho_oliva | 2011-05-11 17:21:56 | 2011-05-12 | 15 | | 73 | @GiorgioJackson | JPAlvarez_ | 2011-05-12 03:08:45 | 2011-05-12 | 15 | | 74 | @GiorgioJackson | politicarock | 2011-05-12 17:14:52 | 2011-05-12 | 15 | | 75 | @GiorgioJackson | ro_meza | 2011-05-12 19:00:27 | 2011-05-12 | 15 | | 76 | @GiorgioJackson | Soyshuerloca | 2011-05-13 04:09:02 | 2011-05-12 | 15 | | 77 | @GiorgioJackson | weonsupertela | 2011-05-13 04:10:21 | 2011-05-12 | 15 | | 78 | @GiorgioJackson | condorsebas | 2011-05-13 04:33:06 | 2011-05-12 | 15 | | 79 | @GiorgioJackson | CarlosFigue | 2011-05-13 13:09:22 | 2011-05-12 | 15 | | 80 | @GiorgioJackson | milafuentes | 2011-05-13 19:12:46 | 2011-05-12 | 15 | | 81 | @GiorgioJackson | aimanen | 2011-05-13 19:16:02 | 2011-05-12 | 15 | | 82 | @GiorgioJackson | jmorande | 2011-05-14 16:14:21 | 2011-05-12 | 15 | | 83 | @GiorgioJackson | KarlaMorenoM | 2011-05-14 16:20:44 | 2011-05-12 | 15 | | 84 | @GiorgioJackson | bicarrio | 2011-05-14 17:46:52 | 2011-05-12 | 15 | | 85 | @GiorgioJackson | noseastancruel_ | 2011-05-15 19:53:36 | 2011-05-12 | 15 | | 86 | @pepoglatz | cathyfm | 2011-05-09 02:41:46 | 2011-05-12 | 16 | | 87 | @pepoglatz | sebavielmas | 2011-05-09 14:56:19 | 2011-05-12 | 16 | | 88 | @pepoglatz | esopino | 2011-05-09 14:56:55 | 2011-05-12 | 16 | | 89 | @pepoglatz | fireyesr | 2011-05-09 14:59:48 | 2011-05-12 | 16 | | 90 | @pepoglatz | TomasMery | 2011-05-09 15:01:12 | 2011-05-12 | 16 | | 91 | @pepoglatz | fcamiranda | 2011-05-09 15:53:48 | 2011-05-12 | 16 | | 92 | @pepoglatz | CarlosFigue | 2011-05-09 16:04:49 | 2011-05-12 | 16 | | 93 | @pepoglatz | camiponcec | 2011-05-09 17:16:44 | 2011-05-12 | 16 | | 94 | @pepoglatz | raecheco | 2011-05-09 17:48:14 | 2011-05-12 | 16 | | 95 | @pepoglatz | camimi_verdugo | 2011-05-12 12:49:28 | 2011-05-12 | 16 | | 96 | @pepoglatz | fcamiranda | 2011-05-14 01:45:50 | 2011-05-12 | 16 | | 97 | @pepoglatz | nacho_oliva | 2011-05-14 02:18:35 | 2011-05-12 | 16 | | 98 | @pepoglatz | EliSA_Torr3s | 2011-05-14 23:49:58 | 2011-05-12 | 16 | | 99 | @pepoglatz | valenzuelalevi | 2011-05-14 23:51:51 | 2011-05-12 | 16 | | 100 | @pepoglatz | sentidoscomunes | 2011-05-14 23:51:52 | 2011-05-12 | 16 | +-----+------------------+-----------------+---------------------+------------+----------+ 100 rows in set (0.00 sec)
62. mysql> desc protests; +--------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | to | varchar(191) | YES | MUL | NULL | | | from | varchar(191) | YES | | NULL | | | created_date | datetime | YES | | NULL | | | protest | date | YES | MUL | NULL | | | actor_id | int(11) | YES | | NULL | | +--------------+--------------+------+-----+---------+----------------+ 6 rows in set (0.00 sec) mysql> select from from protests; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from from protests' at line 1 mysql> select `from` from protests limit 5; +-----------------+ | from | +-----------------+ | HenriquezNicole | | HenriquezNicole | | FranciscoYira_ | | MaribelQR | | gustavodv_ | +-----------------+ 5 rows in set (0.00 sec)
63. mysql> select count(distinct `from`) from protests; +------------------------+ | count(distinct `from`) | +------------------------+ | 66761 | +------------------------+ 1 row in set (2.65 sec) mysql> select count(distinct `to`) from protests; +----------------------+ | count(distinct `to`) | +----------------------+ | 147 | +----------------------+ 1 row in set (0.01 sec) mysql> select count(distinct protest) from protests; +-------------------------+ | count(distinct protest) | +-------------------------+ | 26 | +-------------------------+ 1 row in set (0.00 sec)
64. mysql> select distinct protest from protests; +------------+ | protest | +------------+ | 2011-05-12 | | 2011-05-26 | | 2011-06-01 | | 2011-06-16 | | 2011-06-23 | | 2011-06-30 | | 2011-07-14 | | 2011-08-04 | | 2011-08-18 | | 2011-08-24 | | 2011-09-22 | | 2011-09-29 | | 2011-10-18 | | 2012-04-25 | | 2012-05-16 | | 2012-08-08 | | 2012-08-28 | | 2012-09-27 | | 2013-04-11 | | 2013-05-08 | | 2013-05-28 | | 2013-06-13 | | 2013-06-26 | | 2013-07-11 | | 2013-09-05 | | 2013-10-17 | +------------+ 26 rows in set (0.00 sec) mysql> select distinct protest from protests order by protest; +------------+ | protest | +------------+ | 2011-05-12 | | 2011-05-26 | | 2011-06-01 | | 2011-06-16 | | 2011-06-23 | | 2011-06-30 | | 2011-07-14 | | 2011-08-04 | | 2011-08-18 | | 2011-08-24 | | 2011-09-22 | | 2011-09-29 | | 2011-10-18 | | 2012-04-25 | | 2012-05-16 | | 2012-08-08 | | 2012-08-28 | | 2012-09-27 | | 2013-04-11 | | 2013-05-08 | | 2013-05-28 | | 2013-06-13 | | 2013-06-26 | | 2013-07-11 | | 2013-09-05 | | 2013-10-17 | +------------+ 26 rows in set (0.00 sec)
65. mysql> select protest, count(protest) from protests group by protest; +------------+----------------+ | protest | count(protest) | +------------+----------------+ | 2011-05-12 | 1511 | | 2011-05-26 | 459 | | 2011-06-01 | 1027 | | 2011-06-16 | 1926 | | 2011-06-23 | 1339 | | 2011-06-30 | 3037 | | 2011-07-14 | 3508 | | 2011-08-04 | 26439 | | 2011-08-18 | 15552 | | 2011-08-24 | 17039 | | 2011-09-22 | 8926 | | 2011-09-29 | 9295 | | 2011-10-18 | 7272 | | 2012-04-25 | 9434 | | 2012-05-16 | 7383 | | 2012-08-08 | 11850 | | 2012-08-28 | 10416 | | 2012-09-27 | 7643 | | 2013-04-11 | 14917 | | 2013-05-08 | 11862 | | 2013-05-28 | 13066 | | 2013-06-13 | 14968 | | 2013-06-26 | 16138 | | 2013-07-11 | 8392 | | 2013-09-05 | 8076 | | 2013-10-17 | 5254 | +------------+----------------+ 26 rows in set (0.12 sec)
66. mysql> select protest, `to`, count(`to`) from protests where protest > '2011-05-01' and protest < '2011-05-20' group by protest,`to` order by protest,`to`; +------------+------------------+-------------+ | protest | to | count(`to`) | +------------+------------------+-------------+ | 2011-05-12 | @ConstanzaLeyton | 29 | | 2011-05-12 | @EliasLonconado | 3 | | 2011-05-12 | @FECUdeC | 56 | | 2011-05-12 | @FEL_Stgo | 32 | | 2011-05-12 | @FEPUCV | 1 | | 2011-05-12 | @FEUAntofagasta | 14 | | 2011-05-12 | @FEUCN | 1 | | 2011-05-12 | @FEUSAM | 6 | | 2011-05-12 | @Felipesalga | 15 | | 2011-05-12 | @Feuls | 10 | | 2011-05-12 | @GiorgioJackson | 33 | | 2011-05-12 | @Javijadue | 2 | | 2011-05-12 | @SolidaridadUC | 17 | | 2011-05-12 | @Valessoncilla | 86 | | 2011-05-12 | @andresdouglasr | 2 | | 2011-05-12 | @camila_vallejo | 8 | | 2011-05-12 | @camiloriffo | 368 | | 2011-05-12 | @confech | 238 | | 2011-05-12 | @creceruc | 11 | | 2011-05-12 | @elperrotracio | 7 | | 2011-05-12 | @feliperasa | 11 | | 2011-05-12 | @feuc | 182 | | 2011-05-12 | @feuv | 21 | | 2011-05-12 | @feuvsantiago | 1 | | 2011-05-12 | @jjcc_chile | 214 | | 2011-05-12 | @mguc | 26 | | 2011-05-12 | @naupuc | 37 | | 2011-05-12 | @pepoglatz | 19 | | 2011-05-12 | @raecheco | 10 | | 2011-05-12 | @recarex | 1 | | 2011-05-12 | @sebavielmas | 23 | | 2011-05-12 | @secundarios | 1 | | 2011-05-12 | @ucscconcepcion | 1 | | 2011-05-12 | @velagrau | 7 | | 2011-05-12 | @vlatorre | 18 | +------------+------------------+-------------+ 35 rows in set (0.01 sec)
68. mysql> select * from protests limit 100; +-----+------------------+-----------------+---------------------+------------+----------+ | id | to | from | created_date | protest | actor_id | +-----+------------------+-----------------+---------------------+------------+----------+ | 1 | @camila_vallejo | HenriquezNicole | 2011-05-12 04:23:16 | 2011-05-12 | 1 | | 2 | @camila_vallejo | HenriquezNicole | 2011-05-12 04:28:32 | 2011-05-12 | 1 | | 3 | @camila_vallejo | FranciscoYira_ | 2011-05-12 04:30:09 | 2011-05-12 | 1 | | 4 | @camila_vallejo | MaribelQR | 2011-05-12 21:32:31 | 2011-05-12 | 1 | | 5 | @camila_vallejo | gustavodv_ | 2011-05-12 22:04:44 | 2011-05-12 | 1 | | 6 | @camila_vallejo | aliroazocar | 2011-05-13 01:10:07 | 2011-05-12 | 1 | | 7 | @camila_vallejo | LETY1970 | 2011-05-14 19:41:15 | 2011-05-12 | 1 | | 8 | @camila_vallejo | CamiCardenas | 2011-05-15 16:09:52 | 2011-05-12 | 1 | | 9 | @Felipesalga | Gvovish | 2011-05-09 19:24:26 | 2011-05-12 | 5 | | 10 | @Felipesalga | abrlinconl | 2011-05-10 01:36:24 | 2011-05-12 | 5 | | 11 | @Felipesalga | Chik_Pizarro | 2011-05-10 01:37:44 | 2011-05-12 | 5 | | 12 | @Felipesalga | Darkyrian | 2011-05-10 01:38:15 | 2011-05-12 | 5 | | 13 | @Felipesalga | agathapca | 2011-05-10 01:38:57 | 2011-05-12 | 5 | | 14 | @Felipesalga | PaulinaHudson | 2011-05-10 01:39:05 | 2011-05-12 | 5 | | 15 | @Felipesalga | ProfeRaulOpazo | 2011-05-10 01:39:06 | 2011-05-12 | 5 | | 16 | @Felipesalga | lunitacaramelos | 2011-05-10 01:42:08 | 2011-05-12 | 5 | | 17 | @Felipesalga | enChileHoy | 2011-05-10 01:52:54 | 2011-05-12 | 5 | | 18 | @Felipesalga | Naiitta | 2011-05-10 01:53:32 | 2011-05-12 | 5 | | 19 | @Felipesalga | shuritox | 2011-05-10 15:44:36 | 2011-05-12 | 5 | | 20 | @Felipesalga | inti_illimani | 2011-05-12 13:19:34 | 2011-05-12 | 5 | | 21 | @Felipesalga | JenLopez_ | 2011-05-12 18:59:34 | 2011-05-12 | 5 | | 22 | @Felipesalga | gab000 | 2011-05-12 23:39:22 | 2011-05-12 | 5 | | 23 | @Felipesalga | KathyuskaNevada | 2011-05-13 01:02:54 | 2011-05-12 | 5 | | 24 | @ConstanzaLeyton | Abel_ORian | 2011-05-09 01:26:34 | 2011-05-12 | 9 | | 25 | @ConstanzaLeyton | Feuls | 2011-05-09 01:40:26 | 2011-05-12 | 9 | | 26 | @ConstanzaLeyton | Jaime_Guerra | 2011-05-09 03:11:28 | 2011-05-12 | 9 | | 27 | @ConstanzaLeyton | coniperez | 2011-05-10 15:46:04 | 2011-05-12 | 9 | | 28 | @ConstanzaLeyton | VictorHugo__23 | 2011-05-10 22:30:57 | 2011-05-12 | 9 | | 29 | @ConstanzaLeyton | Abel_ORian | 2011-05-11 03:34:01 | 2011-05-12 | 9 | | 30 | @ConstanzaLeyton | DaniaLazo | 2011-05-11 04:59:30 | 2011-05-12 | 9 | | 31 | @ConstanzaLeyton | Shlomitha | 2011-05-11 05:34:27 | 2011-05-12 | 9 | | 32 | @ConstanzaLeyton | shimbe | 2011-05-11 17:36:54 | 2011-05-12 | 9 | | 33 | @ConstanzaLeyton | Feuls | 2011-05-11 18:44:30 | 2011-05-12 | 9 |
69. mysql> select * from protests limit 100; +-----+------------------+-----------------+---------------------+------------+----------+ | id | to | from | created_date | protest | actor_id | +-----+------------------+-----------------+---------------------+------------+----------+ | 1 | @camila_vallejo | HenriquezNicole | 2011-05-12 04:23:16 | 2011-05-12 | 1 | | 2 | @camila_vallejo | HenriquezNicole | 2011-05-12 04:28:32 | 2011-05-12 | 1 | | 3 | @camila_vallejo | FranciscoYira_ | 2011-05-12 04:30:09 | 2011-05-12 | 1 | | 4 | @camila_vallejo | MaribelQR | 2011-05-12 21:32:31 | 2011-05-12 | 1 | | 5 | @camila_vallejo | gustavodv_ | 2011-05-12 22:04:44 | 2011-05-12 | 1 | | 6 | @camila_vallejo | aliroazocar | 2011-05-13 01:10:07 | 2011-05-12 | 1 | | 7 | @camila_vallejo | LETY1970 | 2011-05-14 19:41:15 | 2011-05-12 | 1 | | 8 | @camila_vallejo | CamiCardenas | 2011-05-15 16:09:52 | 2011-05-12 | 1 | | 9 | @Felipesalga | Gvovish | 2011-05-09 19:24:26 | 2011-05-12 | 5 | | 10 | @Felipesalga | abrlinconl | 2011-05-10 01:36:24 | 2011-05-12 | 5 | | 11 | @Felipesalga | Chik_Pizarro | 2011-05-10 01:37:44 | 2011-05-12 | 5 | | 12 | @Felipesalga | Darkyrian | 2011-05-10 01:38:15 | 2011-05-12 | 5 | | 13 | @Felipesalga | agathapca | 2011-05-10 01:38:57 | 2011-05-12 | 5 | | 14 | @Felipesalga | PaulinaHudson | 2011-05-10 01:39:05 | 2011-05-12 | 5 | | 15 | @Felipesalga | ProfeRaulOpazo | 2011-05-10 01:39:06 | 2011-05-12 | 5 | | 16 | @Felipesalga | lunitacaramelos | 2011-05-10 01:42:08 | 2011-05-12 | 5 | | 17 | @Felipesalga | enChileHoy | 2011-05-10 01:52:54 | 2011-05-12 | 5 | | 18 | @Felipesalga | Naiitta | 2011-05-10 01:53:32 | 2011-05-12 | 5 | | 19 | @Felipesalga | shuritox | 2011-05-10 15:44:36 | 2011-05-12 | 5 | | 20 | @Felipesalga | inti_illimani | 2011-05-12 13:19:34 | 2011-05-12 | 5 | | 21 | @Felipesalga | JenLopez_ | 2011-05-12 18:59:34 | 2011-05-12 | 5 | | 22 | @Felipesalga | gab000 | 2011-05-12 23:39:22 | 2011-05-12 | 5 | | 23 | @Felipesalga | KathyuskaNevada | 2011-05-13 01:02:54 | 2011-05-12 | 5 | | 24 | @ConstanzaLeyton | Abel_ORian | 2011-05-09 01:26:34 | 2011-05-12 | 9 | | 25 | @ConstanzaLeyton | Feuls | 2011-05-09 01:40:26 | 2011-05-12 | 9 | | 26 | @ConstanzaLeyton | Jaime_Guerra | 2011-05-09 03:11:28 | 2011-05-12 | 9 | | 27 | @ConstanzaLeyton | coniperez | 2011-05-10 15:46:04 | 2011-05-12 | 9 | | 28 | @ConstanzaLeyton | VictorHugo__23 | 2011-05-10 22:30:57 | 2011-05-12 | 9 | | 29 | @ConstanzaLeyton | Abel_ORian | 2011-05-11 03:34:01 | 2011-05-12 | 9 | | 30 | @ConstanzaLeyton | DaniaLazo | 2011-05-11 04:59:30 | 2011-05-12 | 9 | | 31 | @ConstanzaLeyton | Shlomitha | 2011-05-11 05:34:27 | 2011-05-12 | 9 | | 32 | @ConstanzaLeyton | shimbe | 2011-05-11 17:36:54 | 2011-05-12 | 9 | | 33 | @ConstanzaLeyton | Feuls | 2011-05-11 18:44:30 | 2011-05-12 | 9 |
70. mysql> desc protests; +--------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | to | varchar(191) | YES | MUL | NULL | | | from | varchar(191) | YES | | NULL | | | created_date | datetime | YES | | NULL | | | protest | date | YES | MUL | NULL | | | actor_id | int(11) | YES | | NULL | | +--------------+--------------+------+-----+---------+----------------+ 6 rows in set (0.00 sec)
71. $ ls -l total 35752 -rwxrwxrwx 1 alegomes staff 11411 Jan 8 14:17 wim2_dev_actors.sql -rwxrwxrwx 1 alegomes staff 18055938 Jan 8 14:17 wim2_dev_protests.sql $ head wim2_dev_actors.sql -- MySQL dump 10.13 Distrib 5.6.22, for osx10.8 (x86_64) -- -- Host: localhost Database: wim2_dev -- ------------------------------------------------------ -- Server version 5.7.10 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */;
72. $ mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 20 Server version: 5.7.12 Homebrew Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use chiledb; 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 mysql>
73. mysql> source wim2_dev_actors.sql Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.05 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 157 rows affected (0.02 sec) Records: 157 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec
74. mysql> show tables; +-------------------+ | Tables_in_chiledb | +-------------------+ | actors | | protests | +-------------------+ 2 rows in set (0.01 sec) mysql> desc actors; +---------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(191) | YES | | NULL | | | screen_name | varchar(191) | YES | MUL | NULL | | | nature | int(11) | YES | | 0 | | | year_2011 | tinyint(1) | YES | | 0 | | | real_username | varchar(191) | YES | | NULL | | | year_2012 | tinyint(1) | YES | | 0 | | | year_2013 | tinyint(1) | YES | | 0 | | +---------------+--------------+------+-----+---------+----------------+ 8 rows in set (0.00 sec)
75. mysql> select * from actors limit 10; +----+--------------------+------------------+--------+-----------+------------------+-----------+-----------+ | id | name | screen_name | nature | year_2011 | real_username | year_2012 | year_2013 | +----+--------------------+------------------+--------+-----------+------------------+-----------+-----------+ | 1 | Camila Vallejo | @camila_vallejo | 0 | 1 | @camila_vallejo | 1 | 0 | | 2 | Francisco Figueroa | @panchofigueroa | 0 | 1 | @panchofigueroa | 0 | 0 | | 3 | Cristóbal Lagos | @crislagos | 0 | 1 | @CrisLagos | 0 | 0 | | 4 | Camilo Ballesteros | @sr_ballesteros | 0 | 1 | @Sr_Ballesteros | 1 | 0 | | 5 | Felipe Salgado | @felipesalga | 0 | 1 | @Felipesalga | 0 | 0 | | 6 | Constanza Nogués | @coni_nogues | 0 | 1 | @Coni_nogues | 0 | 0 | | 7 | Damaniel Cid | @d_cid | 0 | 1 | @d_cid | 0 | 0 | | 8 | Inti Alavia | @intialavia | 0 | 1 | @intialavia | 0 | 0 | | 9 | Constanza Leyton | @constanzaleyton | 0 | 1 | @ConstanzaLeyton | 0 | 0 | | 10 | Juan Carlos Godoy | @godoy_jc | 0 | 1 | @Godoy_JC | 0 | 0 | +----+--------------------+------------------+--------+-----------+------------------+-----------+-----------+ 10 rows in set (0.00 sec) mysql> select * from protests limit 100; +-----+------------------+-----------------+---------------------+------------+----------+ | id | to | from | created_date | protest | actor_id | +-----+------------------+-----------------+---------------------+------------+----------+ | 1 | @camila_vallejo | HenriquezNicole | 2011-05-12 04:23:16 | 2011-05-12 | 1 | | 2 | @camila_vallejo | HenriquezNicole | 2011-05-12 04:28:32 | 2011-05-12 | 1 | | 3 | @camila_vallejo | FranciscoYira_ | 2011-05-12 04:30:09 | 2011-05-12 | 1 | | 4 | @camila_vallejo | MaribelQR | 2011-05-12 21:32:31 | 2011-05-12 | 1 | | 5 | @camila_vallejo | gustavodv_ | 2011-05-12 22:04:44 | 2011-05-12 | 1 | | 6 | @camila_vallejo | aliroazocar | 2011-05-13 01:10:07 | 2011-05-12 | 1 | | 7 | @camila_vallejo | LETY1970 | 2011-05-14 19:41:15 | 2011-05-12 | 1 | | 8 | @camila_vallejo | CamiCardenas | 2011-05-15 16:09:52 | 2011-05-12 | 1 | | 9 | @Felipesalga | Gvovish | 2011-05-09 19:24:26 | 2011-05-12 | 5 | | 10 | @Felipesalga | abrlinconl | 2011-05-10 01:36:24 | 2011-05-12 | 5 | | 11 | @Felipesalga | Chik_Pizarro | 2011-05-10 01:37:44 | 2011-05-12 | 5 | | 12 | @Felipesalga | Darkyrian | 2011-05-10 01:38:15 | 2011-05-12 | 5 | | 13 | @Felipesalga | agathapca | 2011-05-10 01:38:57 | 2011-05-12 | 5 | | 14 | @Felipesalga | PaulinaHudson | 2011-05-10 01:39:05 | 2011-05-12 | 5 | | 15 | @Felipesalga | ProfeRaulOpazo | 2011-05-10 01:39:06 | 2011-05-12 | 5 | | 16 | @Felipesalga | lunitacaramelos | 2011-05-10 01:42:08 | 2011-05-12 | 5 | | 17 | @Felipesalga | enChileHoy | 2011-05-10 01:52:54 | 2011-05-12 | 5 | | 18 | @Felipesalga | Naiitta | 2011-05-10 01:53:32 | 2011-05-12 | 5 |
76. BANCOS DE DADOS RELACIONAIS
77. EXERCÍCIO Processar dados coletados do Twitter armazenando-os em sistema gerenciador de banco de dados e, partir dele, extrair informações relevantes para a pesquisa.
78. EXERCÍCIO PROCESSAR DADOS COLETADOS DO TWITTER ARMAZENANDO-OS EM SISTEMA GERENCIADOR DE BANCO DE DADOS E, PARTIR DELE, EXTRAIR INFORMAÇÕES RELEVANTES PARA A PESQUISA. Varrer o arquivo linha a linha, criando objeto JSON e selecionando as propriedades desejadas.
79. EXERCÍCIO PROCESSAR DADOS COLETADOS DO TWITTER ARMAZENANDO-OS EM SISTEMA GERENCIADOR DE BANCO DE DADOS E, PARTIR DELE, EXTRAIR INFORMAÇÕES RELEVANTES PARA A PESQUISA. Projetar e criar banco de dados no MySQL e utilizar o pacote ‘records’ para realização dos INSERTs.
80. EXERCÍCIO PROCESSAR DADOS COLETADOS DO TWITTER ARMAZENANDO-OS EM SISTEMA GERENCIADOR DE BANCO DE DADOS E, PARTIR DELE, EXTRAIR INFORMAÇÕES RELEVANTES PARA A PESQUISA. SELECT, COUNT(), SUM(), ORDER BY, GROUP BY…
81. …
82. mysql> show tables; +-------------------+ | Tables_in_chiledb | +-------------------+ | actors | | mentions | | protests | | ranking_tweets | | retweets | | tweets | +-------------------+ 6 rows in set (0.00 sec)
83. mysql> select count(*) from actors; +----------+ | count(*) | +----------+ | 157 | +----------+ 1 row in set (0.01 sec) mysql> select count(*) from mentions; +----------+ | count(*) | +----------+ | 2867294 | +----------+ 1 row in set (9.17 sec) mysql> select count(*) from protests; +----------+ | count(*) | +----------+ | 236729 | +----------+ 1 row in set (0.17 sec) mysql> select count(*) from ranking_tweets; +----------+ | count(*) | +----------+ | 42822 | +----------+ 1 row in set (0.14 sec) mysql> select count(*) from retweets; +----------+ | count(*) | +----------+ | 921900 | +----------+ 1 row in set (1.42 sec) mysql> select count(*) from tweets; +----------+ | count(*) | +----------+ | 671949 | +----------+ 1 row in set (1.67 sec)
84. mysql> desc actors; +---------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(191) | YES | | NULL | | | screen_name | varchar(191) | YES | MUL | NULL | | | nature | int(11) | YES | | 0 | | | year_2011 | tinyint(1) | YES | | 0 | | | real_username | varchar(191) | YES | | NULL | | | year_2012 | tinyint(1) | YES | | 0 | | | year_2013 | tinyint(1) | YES | | 0 | | +---------------+--------------+------+-----+---------+----------------+ 8 rows in set (0.03 sec)
85. mysql> desc mentions; +--------------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | mention_date | varchar(191) | YES | | NULL | | | favorited | tinyint(1) | YES | | NULL | | | in_reply_to_screen_name | varchar(191) | YES | | NULL | | | permanent_link | varchar(191) | YES | | NULL | | | lang | varchar(191) | YES | | NULL | | | source | varchar(191) | YES | | NULL | | | text | text | YES | | NULL | | | user_profile_description | text | YES | | NULL | | | user_profile_location | varchar(191) | YES | | NULL | | | user_date | varchar(191) | YES | | NULL | | | user_followers_count | int(11) | YES | | NULL | | | user_name | varchar(191) | YES | | NULL | | | user_screen_name | varchar(191) | YES | | NULL | | | user_time_zone | varchar(191) | YES | | NULL | | | actor_id | int(11) | YES | MUL | NULL | | +--------------------------+--------------+------+-----+---------+----------------+ 16 rows in set (0.01 sec)
86. mysql> desc protests; +--------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | to | varchar(191) | YES | MUL | NULL | | | from | varchar(191) | YES | | NULL | | | created_date | datetime | YES | | NULL | | | protest | date | YES | MUL | NULL | | | actor_id | int(11) | YES | | NULL | | +--------------+--------------+------+-----+---------+----------------+ 6 rows in set (0.00 sec)
87. mysql> desc ranking_tweets; +--------------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | tweet_date | varchar(191) | YES | | NULL | | | favorited | tinyint(1) | YES | | NULL | | | in_reply_to_screen_name | varchar(191) | YES | | NULL | | | permanent_link | varchar(191) | YES | | NULL | | | lang | varchar(191) | YES | | NULL | | | source | varchar(191) | YES | | NULL | | | text | text | YES | | NULL | | | user_profile_description | text | YES | | NULL | | | user_profile_location | varchar(191) | YES | | NULL | | | user_date | varchar(191) | YES | | NULL | | | user_followers_count | int(11) | YES | | NULL | | | user_name | varchar(191) | YES | | NULL | | | user_screen_name | varchar(191) | YES | | NULL | | | user_time_zone | varchar(191) | YES | | NULL | | | retweets | int(11) | YES | | NULL | | | actor_id | int(11) | YES | | NULL | | +--------------------------+--------------+------+-----+---------+----------------+ 17 rows in set (0.01 sec)
88. mysql> desc retweets; +------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | actor_id | int(11) | YES | MUL | NULL | | | mention_id | int(11) | YES | | NULL | | | text | text | YES | | NULL | | | user_screen_name | varchar(191) | YES | MUL | NULL | | | rt_date | datetime | YES | | NULL | | | tweet_id | int(11) | YES | MUL | NULL | | +------------------+--------------+------+-----+---------+----------------+ 7 rows in set (0.00 sec)
89. mysql> desc tweets; +--------------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | tweet_date | varchar(191) | YES | | NULL | | | favorited | tinyint(1) | YES | | NULL | | | in_reply_to_screen_name | varchar(191) | YES | | NULL | | | permanent_link | varchar(191) | YES | | NULL | | | lang | varchar(191) | YES | | NULL | | | source | varchar(191) | YES | | NULL | | | text | text | YES | | NULL | | | user_profile_description | text | YES | | NULL | | | user_profile_location | varchar(191) | YES | | NULL | | | user_date | varchar(191) | YES | | NULL | | | user_followers_count | int(11) | YES | | NULL | | | user_name | varchar(191) | YES | | NULL | | | user_screen_name | varchar(191) | YES | MUL | NULL | | | user_time_zone | varchar(191) | YES | | NULL | | | actor_id | int(11) | YES | MUL | NULL | | +--------------------------+--------------+------+-----+---------+----------------+ 16 rows in set (0.01 sec)
91. Continua em…. https://gist.github.com/alegomes/fbde8c13f4d73aa7270749afb0c7d980