# bbdd-repte01 ## (2ona part) <!-- Put the link to this slide here so people can follow --> slide: https://hackmd.io/@joaniznardo/bbdd-repte01 --- ## referències https://dev.mysql.com/doc/refman/8.0/en/ https://www.javatpoint.com/mysql-tutorial --- ## plan (1er bloc) 1. gestió dels **comptes i privilegis** 1. crear la base de dades 1. crear les taules 1. crear una **vista** 1. crear les claus 1. crear un índex 1. crear un **activador** (trigger) --- ## plan (2on bloc) 1. comprovar (**select**) abans i després de cada operació 1. inserir registres, actualitzar algun registre, esborrar algun registre 1. comprovar (**select**) abans i després d'esborrar de les 2 taules 1. **modificar** la taula per fer que **esborre en cascada** 1. **exportar** la base de dades 1. **importar** la base de dades --- ## gestió de comptes alta baixa modificació --- ## alta de comptes https://dev.mysql.com/doc/refman/8.0/en/create-user.html ``` CREATE USER [IF NOT EXISTS] user [auth_option] [, user [auth_option]] ... DEFAULT ROLE role [, role ] ... [REQUIRE {NONE | tls_option [[AND] tls_option] ...}] [WITH resource_option [resource_option] ...] [password_option | lock_option] ... [COMMENT 'comment_string' | ATTRIBUTE 'json_object'] ``` --- ## alta de comptes (pensant-hi) creació **múltiple**? (una sola instrucció) què són els **rols**? quins rols tenim **disponibles**? --- ## modificació comptes https://dev.mysql.com/doc/refman/8.0/en/alter-user.html ``` ALTER USER [IF EXISTS] user [auth_option] [, user [auth_option]] ... [REQUIRE {NONE | tls_option [[AND] tls_option] ...}] [WITH resource_option [resource_option] ...] [password_option | lock_option] ... [COMMENT 'comment_string' | ATTRIBUTE 'json_object'] ``` --- ## baixa de comptes https://dev.mysql.com/doc/refman/8.0/en/drop-user.html ``` DROP USER [IF EXISTS] user [, user] ... ``` --- ## gestió de privilegis (permissos) concesió (**grant**) comprovació (**show grants**) retirada (**revoke**) --- ## privilegis - concesió **acumulativa!!** https://dev.mysql.com/doc/refman/8.0/en/grant.html ``` GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] priv_level TO user_or_role [, user_or_role] ... [WITH GRANT OPTION] [AS user [WITH ROLE DEFAULT | NONE | ALL | ALL EXCEPT role [, role ] ... | role [, role ] ... ] ] ``` --- ## privilegis - comprovació https://dev.mysql.com/doc/refman/8.0/en/show-grants.html ``` SHOW GRANTS [FOR user_or_role [USING role [, role] ...]] user_or_role: { user (see Section 6.2.4, “Specifying Account Names”) | role (see Section 6.2.5, “Specifying Role Names”. } ``` --- ## privilegis - retirada https://dev.mysql.com/doc/refman/8.0/en/revoke.html **selectiva!!** ``` REVOKE [IF EXISTS] priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] priv_level FROM user_or_role [, user_or_role] ... [IGNORE UNKNOWN USER] REVOKE [IF EXISTS] ALL [PRIVILEGES], GRANT OPTION FROM user_or_role [, user_or_role] ... [IGNORE UNKNOWN USER] ``` --- ## gestió de bases de dades creació eliminació modificació --- ## creació de base de dades https://dev.mysql.com/doc/refman/8.0/en/create-database.html ``` CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_option] ... create_option: [DEFAULT] { CHARACTER SET [=] charset_name | COLLATE [=] collation_name | ENCRYPTION [=] {'Y' | 'N'} } ``` --- ## eliminar base de dades https://dev.mysql.com/doc/refman/8.0/en/drop-database.html ``` DROP {DATABASE | SCHEMA} [IF EXISTS] db_name ``` --- ## modificar base de dades https://dev.mysql.com/doc/refman/8.0/en/alter-database.html ``` ALTER {DATABASE | SCHEMA} [db_name] alter_option ... alter_option: { [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name | [DEFAULT] ENCRYPTION [=] {'Y' | 'N'} | READ ONLY [=] {DEFAULT | 0 | 1} } ``` --- ## gestió de les taules crear taula eliminar taula modificar taula --- ## crear taula (1/3) https://dev.mysql.com/doc/refman/8.0/en/create-table.html ``` CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options] [partition_options] ``` --- ## crear taula (2/3) https://dev.mysql.com/doc/refman/8.0/en/create-table.html ``` create_definition: { col_name column_definition | {INDEX | KEY} [index_name] [index_type] (key_part,...) [index_option] ... | {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name] (key_part,...) [index_option] ... | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (key_part,...) [index_option] ... | [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY] [index_name] [index_type] (key_part,...) [index_option] ... | [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (col_name,...) reference_definition | check_constraint_definition } ``` --- ## crear taula (3/3) https://dev.mysql.com/doc/refman/8.0/en/create-table.html ``` column_definition: { data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ] [VISIBLE | INVISIBLE] [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY] [COMMENT 'string'] [COLLATE collation_name] [COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}] [ENGINE_ATTRIBUTE [=] 'string'] [SECONDARY_ENGINE_ATTRIBUTE [=] 'string'] [STORAGE {DISK | MEMORY}] [reference_definition] [check_constraint_definition] | data_type [COLLATE collation_name] [GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED] [NOT NULL | NULL] [VISIBLE | INVISIBLE] [UNIQUE [KEY]] [[PRIMARY] KEY] [COMMENT 'string'] [reference_definition] [check_constraint_definition] } ``` --- ## eliminar taula ``` ``` --- ## modificar taula ``` ``` --- ## gestió de les vistes ``` ``` --- ## gestió de les claus ``` ``` --- ## gestió dels índex ``` ``` --- ## gestió dels activadors (triggers) ``` ``` --- ## gestió del contingut de les taules ``` ``` --- ## exportació/importació de bases de dades ``` ``` ---