Practica1 PC (Laboratori Integrat) (2016)

Ejercicio Catalán
Universidad Universidad Autónoma de Barcelona (UAB)
Grado Genética - 2º curso
Asignatura Tècniques Instrumentals
Año del apunte 2016
Páginas 15
Fecha de subida 02/04/2016
Descargas 10
Subido por

Vista previa del texto

CREGrau de Genètica curs 2015-2016 Laboratori Integrat IV Tècniques Instrumentals pràctica 1: Creació i consulta de bases de dades (MySQL) Professors: Raquel Egea (raquel.egea@uab.cat) Sergi Hervás (sergi.hervas@uab.cat) Introducció: Llenguatge, Gestor i GUI.
En aquesta pràctica ens familiaritzarem amb els elements bàsics per a la creació, consulta i modificació de bases de dades relacionals. Utilitzarem el llenguatge SQL (Structured Query Language) sobre el sistema gestor de bases de dades MySQL per a crear una base de dades de gens.
Per treballar amb les nostres bases de dades podem utilitzar diferents interfícies gràfiques (GUI - Graphic User Interface). Per a la pràctica treballarem tant utilitzant la línia de comandes del MySQL (Figura 1) com el MySQL Workbench (Figura 2). Treballarem de forma local, així que les bases de dades que creeu s'emmagatzemaran al vostre ordinador.
La paraula de pas per poder connectar-vos és ‘mysql’. Si ens connectéssim a un servidor, posteriorment, es podrien consultar des de qualsevol lloc via internet.
Figura 1. MySQL 5.6 Command Line Client Figura 2. MySQL Workbench 5.2 CE La 'Query' La unitat bàsica del llenguatge SQL és la Query o consulta, i consisteix en un conjunt d'instruccions que són enviades al gestor de bases de dades i aquest interpretarà per a retornar-nos un resultat o crear/modificar/eliminar un element. Tota consulta en MySQL ha d'acabar en punt i coma (;). Es considerat bona pràctica en MySQL escriure les paraules clau en majúscules.
Les consultes són sensibles o insensibles a majúscules en certes condicions:     Paraules clau (insensible) Sobrenoms de columnes (insensible) Noms de taules i bases de dades (sensible) Sobrenoms de taules (sensible) Activitat 1: Creació, modificació i alteració de Bases de dades i taules.
1.1 CREATE DATABASE / TABLE La instrucció bàsica per crear una base de dades té aquest format: CREATE DATABASE my_db ; On simplement indiquem el nom de la nova base de dades després de les paraules clau.
L'equivalent per a crear una taula és similar (elements opcionals entre claudàtors): CREATE TABLE table_name ( column_name TYPE [OPTIONS], column_name TYPE [OPTIONS], column_name TYPE [OPTIONS], ...
PRIMARY KEY (column_name), [Foreign Keys] ) ENGINE = InnoDB ; Com podeu veure, simplement dins del parèntesis separem amb una coma cada columna que volem afegir a la nostra taula.
Tipus Treballarem bàsicament amb aquests tipus de dades:  VARCHAR (text fins a uns 60.000 caràcters). Cal especificar la llargada.
 (MEDIUM / LONG) TEXT (textos més llargs)  INTEGER (o INT) (valor numèric sense decimals)  FLOAT (valor numèric amb decimals). Cal especificar l’escala i la precisió.
Opcions de Columna Cada columna pot tenir opcions especials, algunes són:  NOT NULL (la taula no accepta una entrada sense valor en aquesta columna)  AUTO_INCREMENT (donat un camp numèric, a cada nou element afegit a la taula, aquest camp suma +1 automàticament al valor més gran. Bàsic per als identificadors) Finalment, forcem que la taula sigui InnoDB per optimitzar les claus forànees.
Una bona pràctica és crear a cada taula una columna ID (identificador) interna autoincremental.
Recorda que per no haver d’especificar constantment la base de dades amb la que es vol treballar es pot seleccionar directament amb la següent sentència: USE my_db ; Excercici A: Des de la línia de comandaments de MySQL crea una base de dades ‘ti_p1’.
A continuació crea una taula ‘gens’ amb la següent estructura (pots afegir més columnes):  ID (autoincremental) (clau primària)  nom (camp de text per al nom del gen)  organisme (camp de text per al nom científic)  mida (nombre de nucleòtids)  sequencia (camp de text) Quines queries has fet servir? T'ha sorgit algun error? CREATE DATABASE ti_p1; USE ti_p1; CREATE TABLE gens ( ID INT AUTO_INCREMENT, nom VARCHAR (100), organismes VARCHAR (100), mida INT, sequencia LONGTEXT, PRIMARY KEY (ID) ) INSEENGINE=InnoDB; SHOW TABLES; 1.2 ALTER Una taula pot ser alterada principalment per:  Modificar-ne alguna col·lumna  Eliminar/Inserir columnes  Afegir/eliminar/modificar límits/claus Per afegir un nou camp a la taula que volguem: ALTER TABLE table_name ADD COLUMN col_name type [options]; Podem modificar una mateixa columna existent a partir del seu nom: ALTER TABLE table_name MODIFY COLUMN col_name type [options]; Límits/Claus Si no hem definit prèviament els límits (constraints) quan hem creat les taules, podem afegirlos posteriorment. La opció NOT NULL ja n'és un tipus, però ja hem vist que s'aplica a cada columna. Alguns límits bàsics que podem afegir a la taula són:  UNIQUE (evita repetir valors a una columna)  PRIMARY KEY (columna o conjunt de columnes identificadores, porta associat de forma implícita el límit UNIQUE i NOT NULL. Obligatòria a cada taula!)  FOREIGN KEY (columna associada a una altra columna d'una altra taula per crear una relació entre ambdues taules) Per afegir un nou límit i donar-li nom. Pot afectar 1 o diverses columnes: ALTER TABLE table_name ADD CONSTRAINT const_name LIMIT_TYPE (col_name[s]) [OPTIONS] ; El concepte de foreign key és la clau de les bases de dades relacionals. És l'element que serveix de nexe entre diferents taules. Penseu en la taula anterior i el camp per al nom d'organisme. Us imagineu un cas amb diversos gens del mateix organisme? per a cada gen hauriem d'escriure cada vegada el mateix nom d'organisme.
En un cas de base de dades relacionals podríem tenir 2 taules (gens i organismes) relacionades entre elles d'aquesta manera: organismes     id nom descripcio ...
1:N gens     id nom organisme ...
En aquest cas la relació (foreign key) és entre la taula organismes i gens mitjançant l'ID de la taula organisme. És a dir, no ens caldrà escriure el nom de l'organisme a la taula gens.
Aquesta relació és una relació de "un a molts" (1:N), que s'interpreta com que un organisme sol pot tenir molts gens, però un gen només pertany a un organisme.
La foreing key la creem a la taula depenent, és a dir la taula ‘gens’ i apuntarà a la clau primària de l’altra taula, d’aquesta manera, no podrem assignar un gen a un organisme que no existeixi prèviament a la taula 'organismes'.
Per afegir una foreign key a una taula que ja hem creat: ALTER TABLE table_name ADD CONSTRAINT const_name FOREIGN KEY (col_name) REFERENCES other_table (other_col_name) ; Una bona pràctica en el cas d’una foreign key és posar el prefix ‘fk_’ seguit dels noms de les dues taules o d’una combinació dels seus noms.
Fixa’t que els contingut de les columnes ens permetrà veure la relació que hi ha entre les dues taules, tot i que els noms de los columnes són diferents.
Si vols consultar les taules que hi ha a la base de dades, fes servir la query: SHOW TABLES ; Si vols consultar l’estructura d’una taula, fes servir la query: DESCRIBE table_name ; Excercici B: Primer de tot, crea la taula per als organismes:  id (autoincremental) (clau primària)  nom (camp de text per al nom científic)  descripcio (camp de text pel nom comú o altra informació) A continuació, podràs crear una foreign key a la taula de gens ja existent que relacioni la taula a través de l'ID de la taula d'organismes.
Quines queries has fet servir? Has modificat alguna columna ja existent? Per què? CREATE TABLE organisme ( ID INT AUTO_INCREMENT, nom VARCHAR (100), descripcio MEDIUMTEXT, PRIMARY KEY (ID) ) ENGINE = InnoDB; DESCRIBE organisme; ##crear clau foranea #modificar columna organisme a taula gens ALTER TABLE gens MODIFY COLUMN organismes INT; #afegir la clau ALTER TABLE gens ADD CONSTRAINT fk_gens_organisme FOREIGN KEY (organismes) REFERENCES organisme (ID); 1.3 DROP / TRUNCATE Finalment, existeixen diferents instruccions per a eliminar elements. DROP es fa servir per eliminar columnes o taules senceres: ALTER TABLE table_name DROP COLUMN col_name ; DROP TABLE table_name ; Podem fer servir TRUNCATE per eliminar les dades d'una taula sense eliminar aquesta taula: TRUNCATE TABLE table_name ; Excercici C (opcional): Prova de fer DROP a les teves taules i columnes. IMPORTANT: assegura’t de tenir les queries que has fet servir per crear les diferents taules abans d’esborrarles, així les podràs tornar a crear fàcilment després del DROP.
Activitat 2: Inserció, modificació i consulta.
2.1 INSERT / MODIFY / DELETE Un cop creades les taules només cal omplir-les amb les nostres dades, per això farem servir la comanda INSERT: INSERT INTO table_name (col_name1, col_name2, ...) VALUES ( 'value1', 'value2', ...) ; Cal tenir present l'ordre en que insertem els valors, és independent en quin ordre existeixin les col·lumnes a la nostra taula, però si en l'ordre en que hem definit les columnes a la nostra query. En el cas en que vulguem introduir valors a totes les columnes, no cal indicar tots els noms de les columnes, però aleshores sí hem de respectar l’ordre en el que existeixen.
INSERT INTO table_name VALUES ( 'val_col1', 'val_col2', ...) ; Una columna que no rebi un valor automàticament insertarà el valor NULL (excepte si està limitada per NOT NULL). Els valors que vulguem introduir i siguin tipus text s'han d'escriure entre cometes simples, els valors numèrics poden anar sense.
Per modificar o eliminar una entrada ja existent a la nostra taula fem servir: UPDATE table_name SET col_name = 'new_value' WHERE col_name = 'value' ; DELETE FROM table_name WHERE col_name = 'value' ; Atents a la clàusula WHERE, és un dels elements que ens donarà més flexibilitat a partir d'ara. Com podeu veure, simplement consisteix a afegir una condició a la nostra query.
Podem utilitzar igualtats (=) de text o numèriques i desigualtats (més gran > , més petit <) per a valors numèrics.
Si no posem la clàusula WHERE, canviaríem els valors de totes les files en el cas del UPDATE o esborraríem tot el contingut de la taula en el cas del DELETE.
Per afegir més d'una condició NO posarem múltiples clàusules WHERE, sinó que introduirem la primera condició amb WHERE i les següents començaran amb un AND (si volem que es compleixin totes les condicions) o bé un OR (si volem que es compleixí una o altra condició). Si combinem els operadors lògics AND i OR hem de fer servir parèntesis per combinar les condicions de forma correcta.
Excercici A: És hora d'omplir les taules (anota les queries que facis servir junt amb qualsevol comentari que consideris rellevant). Omple la taula 'organismes' amb els que vulguis.
Per exemple:  Homo sapiens  humà  Pan troglodytes  ximpanzé  Gorilla gorilla  goril·la  Mus musculus  ratolí A continuació, omple la taula de gens amb uns quants gens repetits per a cada organisme. Fes servir noms curts de gens reals o inventats (BRCA2, HTT, SRY, OCA2, ...). Utilitza diferents valors al camp mida.
Utilitza les instruccions DELETE i UPDATE per assegurar-te que no tots els organismes tinguin exactament els mateixos gens ni el mateix nombre de gens.
Quines restriccions has trobat degudes a la clau forànea? INSERT INTO organisme (nom, descripcio) VALUES ('Homo sapiens','huma'), ('Pan troglodytes', 'ximpanze'), ('Gorilla gorilla', 'gorilla'), ('Mus musculus','ratoli'); INSERT INTO gens (nom, organismes, mida, sequencia) VALUES ('AAA', 1, 23, 'ATGCGTAGCTGTGTACGATGCGT'), ('AAB', 1, 10, 'TTGACACCTG'), ('AAA', 3, 23, 'TTGACCTGATGCGTAAAACTAGA'), ('AAB', 4, 12, 'CGATGCGTATT'); 2.2 SELECT  LO MÉS IMPORTANT DE LA PRÀCTICA Finalment podem consultar una base de dades i taules plenes de dades mitjançant la instrucció SELECT: SELECT col_name1, col_name2,...
FROM table_name [WHERE col_name1 = 'value' [AND col_name2 = 'value'] ] [ORDER BY col_name2] ; Fixa't que pots sel·leccionar les columnes que vulguis que es mostrin després de SELECT.
Podem encadenar diferents condicions amb WHERE i AND/OR. Finalment, podem ordenar els resultats per l'ordre alfabètic dels valors d'alguna columna amb ORDER BY.
També podem fer servir 'comodins' (wildcards), en aquest cas l'asterisc (*) ens serveix per a seleccionar totes les columnes d'una taula.
SELECT * FROM table_name ; Una funció interessant és count(), que ens comptarà el nombre de resultats per una columna en comptes de donar-nos els valors.
SELECT count(col_name1) FROM table_name ; Finalment podem limitar el nombre de resultats d'un select de manera molt simple posant al final la instrucció LIMIT indicant els resultats que volem mostrar: SELECT ...
...
LIMIT x; Excercici B: A partir d’ara pots treballar amb el MySQL Workbench (o continuar amb la línia de comandes). A la part de l’esquerra pots visualitzar les diferents bases de dades i si vas desplegant pots veure les taules i les columnes que contenen.
A la part central pots escriure les queries i t’apareixeran els resultats a sota. Per executar una query, has de clicar sobre una d’aquestes icones: Realitza els següents SELECT (i anota’ls a continuació):  Tota la informació dels gens d'un mateix nom.
 El nombre de gens més petits d'X nucleòtids (tria el valor d'X).
 Les seqüències dels 5 gens més grans de la teva base de dades.
SELECT * FROM organisme; #tota la info dels gens del mateix nom SELECT * FROM gens WHERE nom='AAA'; #nombre gens mes petits X nt SELECT count(mida) FROM gens WHERE mida < 20 AND nom = 'AAA'; #sequencia dels cins gens mes grans SELECT sequencia FROM gens ORDER BY mida DESC LIMIT 5; 2.3 JOIN Per acabar, veurem com relacionar taules en un SELECT. Hi ha diverses maneres de ferho, una forma és simplement indicant quines columnes i de quines taules volem fer servir, i hem d’incloure la instrucció WHERE on la condició serà la relació que hi ha entre les columnes de les taules que hem seleccionat.
SELECT table1.col1, table1.col2, table2.col2, table2.col5, ...
FROM table1, table2 WHERE table1.col2 = table2.col1 ; (molt important FK) Podem fer servir sobrenoms de les taules per simplificar la query anterior, per exemple lletres de l'abecedari simplement afegint-les a cada taula amb la instrucció FROM: SELECT a.col1, a.col2, b.col2, b.col5, ...
FROM table1 a, table2 b WHERE a.col2 = b.col1 ; Una altra opció més intuïtiva per grans bases de dades és posar la inicial del nom de la taula o una abreviatura del nom.
Tot i que no és obligatori posar el nom de la taula cada vegada que indiquem una columna, si ho és per aquelles columnes que tenen el mateix nom a diverses taules incloses al FROM.
Si no especifiquem el nom de la taula, el sistema gestor de bases de dades no sabrà a quina columna fem referència i no es podrà executar la consulta. També és molt recomanable fer-ho en un join per facilitar la comprensió de la relació que hi ha entre les taules.
Una altra opció és utilitzar l’INNER JOIN. En aquest cas, indiquem totes les columnes que volem seleccionar i al FROM només posem una taula. La següent taula la unim mitjançant l’INNER JOIN i després de la instrucció ON indiquem la relació que hi ha entre les columnes de les taules. Podem afegir tants INNER JOIN i ON com taules hàgim de seleccionar.
SELECT table1.col1, table1.col2, table2.col2, table2.col5, ...
FROM table1 INNER JOIN table2 ON table1.col2 = table2.col1 ; Excercici C: Realitza les següents consultes unint les taules 'organismes' i 'gens' per tal que en comptes de l'identificador dels organismes surti el nom de l'organisme als resultats:  Tota la informació dels gens d'un mateix nom.
 Tota la informació dels 5 gens més grans de la teva base de dades.
 Tota la informació dels gens humans més petits que X nucleòtids.
Quines queries has fet servir? #Tota la informació dels gens d’un mateix nom SELECT * FROM gens INNER JOIN organisme ON gens.organismes=organisme.id WHERE gens.nom=’AAA’; #Tota la informació dels 5 gens més grans de la teva base de dades.
SELECT gens.nom, mida, sequencia, organisme.nom, FROM gens INNER JOIN organisme ON organisme.id=gens.organismes ORDER BY mida DESC LIMIT 5; #Tota la informacio dels gens humans mes petits que X (15) nucleòtids SELECT organisme.descripcio, gens.nom, mida, sequencia, organisme.nom FROM gens INNER JOIN organisme ON organisme.id=gens.organismes WHERE descripcio=’huma’ AND mida < 15; *** Aquestes querys s’haurien de corregir  A la primera query s’ha d'especificar quines columnes vols mostrar per tal d'evitar veure columnes repetides. La opció * ens mostra TOTES les columnes de les dues taules, per tant hi ha informació redundant que no ens interessa. A les altres dues querys falten la columna gens.id, i en una d'elles també hi falta la columna organisme.descripció.
...

Comprar Previsualizar