|
|
| Line 1: |
Line 1: |
| − | | + | ~ Migrated |
| − | <br />
| |
| − | We will be using a number of sample databases throughout this module:
| |
| − | * '''Sakila database''', which can be found at http://downloads.mysql.com/docs/sakila-db.zip
| |
| − | * '''NorthwindDB:''' http://perso.sinfronteras.ws/index.php/File:NorthwindDB.zip
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ==What is SQL==
| |
| − | SQL (Structured Query Language) is a special-purpose programming language designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS). https://en.wikipedia.org/wiki/SQL
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ==What is a Database management system - DBMS==
| |
| − | https://en.wikipedia.org/wiki/Database
| |
| − | | |
| − | A database management system (DBMS) is a computer software application that interacts with the user, other applications, and the database itself to capture and analyze data. A general-purpose DBMS is designed to allow the definition, creation, querying, update, and administration of databases.
| |
| − | | |
| − | Well-known DBMSs include MySQL, PostgreSQL, MongoDB, Microsoft SQL Server, Oracle, Sybase, SAP HANA, and IBM DB2.
| |
| − | | |
| − | A database is not generally portable across different DBMSs, but different DBMS can interoperate by using standards such as SQL and ODBC or JDBC to allow a single application to work with more than one DBMS. Database management systems are often classified according to the database model that they support; the most popular database systems since the 1980s have all supported the relational model as represented by the SQL language.
| |
| − | | |
| − | The DBMS acronym is sometimes extended to indicate the underlying database model, with '''RDBMS''' for the relational, OODBMS for the object (oriented), and ORDBMS for the object-relational model. Other extensions can indicate some other characteristic, such as DDBMS for distributed database management systems.
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ==What is MySQL==
| |
| − | MySQL est un système de gestion de bases de données relationnelles (SGBDR). Il fait partie des logiciels de gestion de base de données les plus utilisés au monde. En informatique, un système de gestion de base de données (abr. SGBD) est un logiciel système destiné à stocker et à partager des informations dans une base de données. SQL fait référence au Structured Query Language, le langage de requête utilisé. SQL (sigle de Structured Query Language, en français langage de requête structurée) est un langage informatique normalisé servant à exploiter des bases de données relationnelles.
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ==Installing MySQL==
| |
| − | https://www.mysql.com/
| |
| − | | |
| − | https://en.wikipedia.org/wiki/MySQL
| |
| − | | |
| − | Para instalar la última versión disponible en los repositorios de Ubuntu:
| |
| − | sudo apt update
| |
| − | sudo apt-get install mysql-server
| |
| − | | |
| − | | |
| − | <br />
| |
| − | Para instalar la última versión (8.0.12): https://www.tecmint.com/install-mysql-8-in-ubuntu/
| |
| − | <span style="background:#00FF00">La instalación la última versión (si ésta es muy reciente) puede generar errores que no han sido aún corregidos y documentados por la comunidad. La última vez, al instalar la versión 8.0.12, no pude ingresar a mysql a través de «mycli» y la instalación de phpMyAdmin generó errores; ninguno de los dos problemas pudieron ser solutionados debido a que no se encontró suficiente documentación en la web. '''Recomiendo instalar la última versión incluida por defecto en los repositorios de Ubuntu.'''</span>
| |
| − | | |
| − | Add MySQL Apt Repository:
| |
| − | wget -c https://dev.mysql.com/get/mysql-apt-config_0.8.10-1_all.deb
| |
| − | | |
| − | Then install the MySQL repository package using the following dpkg command:
| |
| − | sudo dpkg -i mysql-apt-config_0.8.10-1_all.deb
| |
| − | | |
| − | Note that in the package installation process, you will be prompted to choose MySQL server version and other components such as cluster, shared client libraries, or the MySQL workbench that you want to configure for installation. MySQL server version mysql-8.0 will be auto-selected, then scroll down to the last option Ok and click [Enter]
| |
| − | | |
| − | sudo apt update
| |
| − | sudo apt-get install mysql-server
| |
| − | | |
| − | | |
| − | <br />
| |
| − | Si la versión instalada de la forma anterior no es la deseada, se puede intentar lo siguiente:
| |
| − | sudo apt install mysql-server-5.6
| |
| − | sudo apt install mysql-client-5.6 (He generalmente usado el server. El client realmente no sé para que se usa)
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ===Secure MySQL Server Installation===
| |
| − | | |
| − | Run the security script which comes with the binary package by issuing the following command:
| |
| − | sudo mysql_secure_installation
| |
| − | | |
| − | By default, the MySQL installation is unsecure. To secure it, run the security script which comes with the binary package. You will be asked to enter the root password you set during the installation process. Then also choose whether to use the VALIDATE PASSWORD plugin or not. <span style="background:#00FF00">En esta primera pregunta elegí «N» porque creo que este plugin genera problemas al tratar de ingresar a «mysql» con «mycli».</span>
| |
| − | | |
| − | You can also change the root password you set before (as we have done in this example). Then enter yes/y to the following security questions:
| |
| − | | |
| − | * Remove anonymous users? (Press y|Y for Yes, any other key for No) : '''y'''
| |
| − | * Disallow root login remotely? (Press y|Y for Yes, any other key for No) : '''y'''
| |
| − | * Remove test database and access to it? (Press y|Y for Yes, any other key for No) : '''y'''
| |
| − | * Reload privilege tables now? (Press y|Y for Yes, any other key for No) : '''y'''
| |
| − | | |
| − | <blockquote>
| |
| − | <span style="color:red">La última vez se generó este error:</span>
| |
| − | | |
| − | ... Failed! Error: SET PASSWORD has no significance for user 'root'@'localhost' as the authentication method used doesn't store authentication data in the MySQL server. Please consider using ALTER USER instead if you want to change authentication parameters.
| |
| − | | |
| − | La solución se encuentra en este post: https://askubuntu.com/questions/1406395/mysql-root-password-setup-error
| |
| − | </blockquote>
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ===Testing and connecting to MySQL===
| |
| − | | |
| − | Si se genera este Error: Luego de instalar MySQL, no podía ingresar a través de «mysql -u root -p». Se generaba el error ERROR 1698 (28000): Access denied for user 'root'@'localhost'». La solución se encuentra en https://stackoverflow.com/questions/39281594/error-1698-28000-access-denied-for-user-rootlocalhost
| |
| − | | |
| − | Al realizar los pasos sugeridos por el método 1 de la solución indicada en el link de arriba:
| |
| − | * «mysql -u root -p» no funcionó la primera vez.
| |
| − | * Luego intente ajustar de nuevo el password ejecutando «sudo mysql_secure_installation» nuevamente.
| |
| − | * Luego no funcionaba ni siquiera de esta forma: «sudo mysql -u root», lo que estaba funcionando antes.
| |
| − | * Luego intenté con «sudo mysql -u root -p» y funcionó.
| |
| − | * Luego de eso, también funcionó con «mysql -u root -p»
| |
| − | | |
| − | | |
| − | <br />
| |
| − | Regardless of how you installed it, MySQL should have started running automatically. To test this, check its status:
| |
| − | systemctl status mysql.service
| |
| − | | |
| − | | |
| − | If MySQL isn't running, you can start it with:
| |
| − | sudo systemctl start MySQL.
| |
| − | | |
| − | | |
| − | For an additional check:
| |
| − | mysqladmin -u root -p version
| |
| − | | |
| − | | |
| − | '''Connecting to MySQL from the terminal:'''
| |
| − | mysql -u root -p
| |
| − | mysql -u usuario -p
| |
| − | | |
| − | | |
| − | <br/>
| |
| − | ===Uninstall MySQL===
| |
| − | https://askubuntu.com/questions/172514/how-do-i-uninstall-mysql
| |
| − | | |
| − | To remove mysql completely from your system Just type in terminal:
| |
| − | sudo apt-get purge mysql-server mysql-client mysql-common mysql-server-core-* mysql-client-core-*
| |
| − | sudo rm -rf /etc/mysql /var/lib/mysql
| |
| − | sudo apt-get autoremove
| |
| − | sudo apt-get autoclean
| |
| − | | |
| − | | |
| − | En el siguiente link se incluyen algunos otros comandos to remove mysql completely from your system: https://linuxscriptshub.com/uninstall-completely-remove-mysql-ubuntu-16-04/
| |
| − | | |
| − | | |
| − | <br/>
| |
| − | | |
| − | ==Customizing MySQL prompt==
| |
| − | Personalizar (customize) MySQL prompt: http://www.thegeekstuff.com/2010/02/mysql_ps1-6-examples-to-make-your-mysql-prompt-like-angelina-jolie/
| |
| − | | |
| − | Display username, hostname and current database name in the mysql prompt:
| |
| − | <syntaxhighlight lang="bash">
| |
| − | export MYSQL_PS1="\u@\h[\d]> "
| |
| − | export MYSQL_PS1="Adelo_Vieira(2017279):\u@\h[\d]> "
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | O desde el prompt de MySQL:
| |
| − | <syntaxhighlight lang="bash">
| |
| − | prompt Adelo_Vieira(2017279):\u@\h[\d]>
| |
| − | prompt // Returning to default PROMPT of mysql
| |
| − | </syntaxhighlight>
| |
| − |
| |
| − | Ingresar al prompt MySQL desde la línea de comandos:
| |
| − | mysql -u root -p
| |
| − | O a través de «mycli»: mycli -u root
| |
| − | | |
| − | | |
| − | <br/>
| |
| − | ==Mycli==
| |
| − | https://www.mycli.net/
| |
| − | | |
| − | MyCLI is a command line interface for MySQL, MariaDB, and Percona with auto-completion and syntax highlighting.
| |
| − | | |
| − | | |
| − | Installation:
| |
| − | sudo apt-get install mycli
| |
| − | | |
| − | | |
| − | '''Para ingresar a la línea de comandos de «mycli»:'''
| |
| − | mycli -u root
| |
| − | mycli -u usuario
| |
| − | mycli -u usuario -p db_name
| |
| − | | |
| − | | |
| − | <br/>
| |
| − | ==Some configurations==
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ===Display version and other details like socket===
| |
| − | mysqladmin -u root -p version
| |
| − | | |
| − | | |
| − | '''Check what port mysql is running on:'''
| |
| − | mysqladmin -p -u root variables | egrep port
| |
| − | or
| |
| − | mysql> SHOW GLOBAL VARIABLES LIKE 'PORT';
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ===Configure port and other variables===
| |
| − | | |
| − | https://askubuntu.com/questions/407847/how-to-change-mysql-port-number-in-ubuntu
| |
| − | | |
| − | There may be multiple files containing mysql configuration. Their full path may exist in the file:
| |
| − | /etc/mysql/my.cnf
| |
| − | | |
| − | Lines starting «!includedir» include different paths from where configuration files are taken. For a sample, in my «/etc/mysql/my.cnf» there are:
| |
| − | !includedir /etc/mysql/conf.d/
| |
| − | !includedir /etc/mysql/mysql.conf.d/
| |
| − | | |
| − | The following file contains several configurations, including port:
| |
| − | /etc/mysql/mysql.conf.d/mysqld.cnf
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ===show and create users===
| |
| − | mysql> select host, user from mysql.user;
| |
| − | | |
| − |
| |
| − | | |
| − | <br />
| |
| − | ===Allow remote connection===
| |
| − | | |
| − | https://www.digitalocean.com/community/questions/can-t-connect-to-mysql-server-on-ipaddress-10061
| |
| − | | |
| − | By default MySQL will listen for connections only from the localhost. To enable remote connections like the one used by mysqlworkbench you will need to modify:
| |
| − | | |
| − | vi /etc/mysql/mysql.conf.d/mysqld.cnf
| |
| − | | |
| − | Comment this line:
| |
| − | bind-address = 127.0.0.1
| |
| − | | |
| − | The above line is telling MySQL to only accept local connections (127.0.0.1 / localhost).
| |
| − | | |
| − | If you know the IP that you are trying to remotely connect from, you should enter it here to restrict remote connections to that IP.
| |
| − | | |
| − | If you have to allow all IP addresses to connect, you can comment out the line by adding # before it so it looks like:
| |
| − | | |
| − | | |
| − | After commenting the above line, I got another error saying: Host «MyLaptopIP» Is Not Allowed to Connect to This MySQL Server. The solution is create a new administrator user or modify the root user to allow connections from «MyLaptopIP»:
| |
| − | * https://support.infrasightlabs.com/article/host-is-not-allowed-to-connect-to-this-mysql-server/
| |
| − | * https://stackoverflow.com/questions/1559955/host-xxx-xx-xxx-xxx-is-not-allowed-to-connect-to-this-mysql-server
| |
| − | | |
| − | CREATE USER 'usuario'@'localhost' IDENTIFIED BY 'a*****1';
| |
| − | GRANT ALL PRIVILEGES ON *.* TO 'usuario'@'localhost' WITH GRANT OPTION;
| |
| − | CREATE USER 'usuario'@'%' IDENTIFIED BY 'a*****1';
| |
| − | GRANT ALL PRIVILEGES ON *.* TO 'usuario'@'%' WITH GRANT OPTION;
| |
| − | FLUSH PRIVILEGES;
| |
| − | | |
| − | | |
| − | <br />
| |
| − | | |
| − | ==GUI DB administration tools for MySQL==
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ===[[Visual Studio Code#Databases|Visual Studio Code]]===
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ===MySQL Workbench===
| |
| − | https://www.mysql.com/products/workbench/
| |
| − | | |
| − | https://en.wikipedia.org/wiki/MySQL_Workbench
| |
| − | | |
| − | MySQL Workbench is a visual database design tool that integrates SQL development, administration, database design, creation and maintenance into a single integrated development environment for the MySQL database system.
| |
| − | | |
| − | | |
| − | Para isntalarlo se puede hacer desde los repositorios de Ubuntu:
| |
| − | sudo apt-get install mysql-workbench
| |
| − | | |
| − | También se puede descargar workbench de la página oficial de MySQL and double click the downloaded package; the software center takes over the installation.
| |
| − | | |
| − | La última vez que lo intalé, la versión de los repositorios estaba bastante desactualizada y presentó errores. Recomiendo instalar la última versión descargada de la página oficial.
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ===Other GUI DB administration tools for MySQL===
| |
| − | Aparte de PhpMyAdmin, también existen otras aplicaciones de este tipo. Entre las más comunes se encuentran:
| |
| − | | |
| − | * Para Windows: '''HeidiSQL''' - MySQL Database Viewer: https://www.heidisql.com/
| |
| − | * Para Ubuntu: '''Mysql workbench'''
| |
| − | * Para que estas aplicaciones se conecten a nuestra base de datos MySQL, debemos configurar:
| |
| − | ** Hostanme/IP: 127.0.0.1
| |
| − | ** User: root
| |
| − | ** Port: 3306 (Mysql port)
| |
| − | | |
| − | '''En HeidiSQL, por ejemplo, para crear una base de datos:'''
| |
| − | * Unnamed-1 (clic derecho) > Database > Create new > Nombre_database (crear esta base de datos)
| |
| − | | |
| − | * Crear una tabla: Nombre_database (clic d) > create table: Name: nombre_tabla
| |
| − | * Crear un campo en la tabla:
| |
| − | ** Add: nombre_campo (tipo VARCHAR)
| |
| − | ** Clic derecho en nombre_campo > create new index > primary (para hacer que dicho campo sea nuestra primary key)
| |
| − | * Luego podemos ingresar datos a nuestro campo:
| |
| − | ** ...
| |
| − | | |
| − | | |
| − | <br/>
| |
| − | ==Some fundamental commands==
| |
| − | Listar las bases de datos:
| |
| − | show databases;
| |
| − | | |
| − | Ingresar o cambiar de base de datos:
| |
| − | use nombre_database;
| |
| − | | |
| − | Mostrar la base de datos que se está usando:
| |
| − | select database();
| |
| − | | |
| − | Crear una base de datos:
| |
| − | create database nombre_database;
| |
| − | | |
| − | Borrar una base de datos:
| |
| − | drop database nombre_de_la_base
| |
| − | | |
| − | Listar las tablas en la base de datos:
| |
| − | show tables;
| |
| − | | |
| − | Borrar tablas por separado:
| |
| − | drop table nombre_tabla;
| |
| − | DROP TABLE IF EXISTS authors;
| |
| − | * Use IF EXISTS to prevent an error from occurring for tables that do not exist. Instead of an error, a NOTE is generated for each nonexistent table; these notes can be displayed with SHOW WARNINGS.
| |
| − | | |
| − | Para desplegar los campos de una tabla. Esta orden no muestra los valores de cada campo; sólo los campos y especifica el Tipo, Key y otras descripciones:
| |
| − | describe nombre_tabla;
| |
| − | | |
| − | Para desplegar todos los campos de una tabla con sus respectivos valores:
| |
| − | select * from nombre_tabla;
| |
| − | | |
| − | Para desplegar algunos campos de una tabla:
| |
| − | select au_fname, au_lname, phone from authors;
| |
| − | | |
| − | | |
| − | <br/>
| |
| − | ==Backup and Restore MySQL/MariaDB Databases==
| |
| − | | |
| − | | |
| − | <br/>
| |
| − | ===Using Command-Line===
| |
| − | | |
| − | | |
| − | <br/>
| |
| − | ====Backup====
| |
| − | The '''mysqldump''' command is used to export the contents of a database to a text file.
| |
| − | | |
| − | Ejecutar la siguiente orden en el command line (bash) prompt ('''NO''' en la línea de comandos de MySQL):
| |
| − | <syntaxhighlight lang="sql">
| |
| − | mysqldump --user=root --password="" nombre_database > nombre_database.sql
| |
| − | | |
| − | OR
| |
| − | | |
| − | mysqldump -u root -p nombre_database > nombre_database.sql
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | Al parecer, la opción '''-A''' permite realizar un backup de todas las bases de datos:
| |
| − | <syntaxhighlight lang="sql">
| |
| − | mysqldump -A -u root -p > backup.sql
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br/>
| |
| − | ====Restore/Import====
| |
| − | The '''mysql''' command-line client can be used to import data from a text file into a MySQL/MariaDB database.
| |
| − | | |
| − | '''1. create a new, empty database to hold your data:'''
| |
| − | | |
| − | Este paso dependerá del archivo .sql que se quiera importar. A veces la creación de la DB se encuentra incluida en el archivo .sql por lo que no se necesario crear una base de datos antes de importar el archivo.
| |
| − | | |
| − | Ejecutar la siguiente orden en el command line (bash) prompt ('''NO''' en la línea de comandos de MySQL):
| |
| − | <syntaxhighlight lang="bash">
| |
| − | mysql --user=root --password="" -e "CREATE DATABASE nombre_database"
| |
| − | | |
| − | // OR
| |
| − | | |
| − | mysql -u root -p -e "CREATE DATABASE nombre_database"
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | '''2. Import the contents of the backup file into the new database:'''
| |
| − | <syntaxhighlight lang="bash">
| |
| − | mysql --user=root --password="" --database=nombre_database < nombre_database.sql
| |
| − | | |
| − | // OR
| |
| − | | |
| − | mysql -u root -p nombre_database < nombre_database.sql
| |
| − | | |
| − | // Si el archivo .sql incluye la creación de la DB, no es necesario indicar el nombre de la DB:
| |
| − | mysql -u root -p < nombre_database.sql
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | You can then show the content of the database de esta forma:
| |
| − | <syntaxhighlight lang="bash">
| |
| − | mysql --user=root --password="" --database=nombre_database -e "SHOW TABLES"
| |
| − | | |
| − | OR
| |
| − | | |
| − | mysql -u root -p nombre_database -e "SHOW TABLES"
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | <span style="background:#00FF00">'''You should now update your application’s configuration and point it to your newly-created database. For example, with the Bitnami WordPress module, you will need to edit the wp-config.php file in the apps\wordpress\htdocs\ subdirectory of your XAMPP installation directory. Within this file, you will need to update the DB_NAME constant to use the new database name.'''</span>
| |
| − | | |
| − | | |
| − | <br/>
| |
| − | =====Example - Importing the Sakila database=====
| |
| − | http://downloads.mysql.com/docs/sakila-db.zip
| |
| − | | |
| − | Dos archivos son proporcionados para crear esta DB:
| |
| − | sakila-schema.sql
| |
| − | sakila-data.sql
| |
| − | | |
| − | Debemos primero importar el archivos sakila-schema.sql y luego sakila-data.sql:
| |
| − | mysql -u root -p < sakila-schema.sql
| |
| − | mysql -u root -p < sakila-data.sql
| |
| − | | |
| − | <span style="background:DarkKhaki">Note que en este caso no es necesario crear la DB antes de importar el archivo .sql ni indicar el nombre de la DB en la syntax.</span>
| |
| − | | |
| − | | |
| − | <br/>
| |
| − | ===Using phpMyAdmin===
| |
| − | | |
| − | | |
| − | <br/>
| |
| − | ==Import a CSV file into a MySQL database==
| |
| − | https://medium.com/@AviGoom/how-to-import-a-csv-file-into-a-mysql-database-ef8860878a68
| |
| − | | |
| − | Example using [[File:SP500Companies.csv]]
| |
| − | <syntaxhighlight lang="sql">
| |
| − | DROP TABLE IF EXISTS SP500Companies;
| |
| − | CREATE TABLE SP500Companies
| |
| − | (
| |
| − | id INT AUTO_INCREMENT,
| |
| − | Name VARCHAR(50) NOT NULL,
| |
| − | Weight float,
| |
| − | Sector VARCHAR(50),
| |
| − | Shares_Held float,
| |
| − | CONSTRAINT pk_SP500Companies PRIMARY KEY (id)
| |
| − | );
| |
| − | | |
| − | LOAD DATA LOCAL INFILE '/root/SP500Companies.csv'
| |
| − | INTO TABLE SP500Companies
| |
| − | FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
| |
| − | IGNORE 1 ROWS (Name, Weight, Sector, Shares_Held);
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | Es problable: <span style="color:#FF0000">ERROR 1148: The used command is not allowed with this MySQL version</span>. La solución se encuentra explicada aquí: https://stackoverflow.com/questions/18437689/error-1148-the-used-command-is-not-allowed-with-this-mysql-version
| |
| − | | |
| − | You must specify that as an additional option when setting up your client connection:
| |
| − | mysql -u myuser -p --local-infile somedatabase
| |
| − | | |
| − | This is because that feature opens a security hole. So you have to enable it in an explicit manner in case you really want to use it.
| |
| − | | |
| − | Both client and server should enable the local-file option. Otherwise it doesn't work.To enable it for files on the server side server add following to the my.cnf configuration file:
| |
| − | loose-local-infile = 1
| |
| − | | |
| − | | |
| − | <br/>
| |
| − | ==Reset the MySQL/MariaDB Password==
| |
| − | Si ningún password ha sido configurado:
| |
| − | mysqladmin --user=root password "newpassword"
| |
| − | | |
| − | For example, to change the MySQL/MariaDB root password from its default empty value to the password gue55me, you would execute:
| |
| − | mysqladmin --user=root password "gue55me"
| |
| − | | |
| − | If a password has already been previously set and you’d like to change it to a new one, you can use the following syntax:
| |
| − | mysqladmin --user=root --password=oldpassword password "newpassword"
| |
| − | | |
| − | For example, to change the root password from 12345 to gue55me, you would execute:
| |
| − | mysqladmin --user=root --password=12345 password "gue55me"
| |
| − | | |
| − | | |
| − | <br/>
| |
| − | ==Databases Authentication and Authorization==
| |
| − | '''Authentication''' is a mechanism that determines whether a user is, who he or she claims to be.
| |
| − | | |
| − | | |
| − | '''Create a new user called test and give a password of secret:'''
| |
| − | <syntaxhighlight lang="mysql">
| |
| − | CREATE USER 'test'@'localhost' IDENTIFIED BY 'secret';
| |
| − | </syntaxhighlight>
| |
| − | Las comillas en el user y el host no son obligatorias, por lo menos no en MySQL.
| |
| − | | |
| − | | |
| − | '''Para listar los usuarios:'''
| |
| − | <syntaxhighlight lang="mysql">
| |
| − | SELECT USER FROM mysql.user;
| |
| − | </syntaxhighlight>
| |
| − | Note: MariaDB /MySQL stores global privileges in the mysql.user system table.
| |
| − | | |
| − | | |
| − | '''Understand Authorization by granting and revoking of privileges Access control (related to authorization):'''
| |
| − | | |
| − | | |
| − | '''Authorization:'''
| |
| − | The granting of a right or privilege, which enables a subject to legitimately have access to a system or a system’s object.
| |
| − | | |
| − | | |
| − | '''Grant some privileges to your new user. GRANT specifies account privileges:'''
| |
| − | <syntaxhighlight lang="mysql">
| |
| − | GRANT SELECT, INSERT, UPDATE ON books.authors TO 'test'@'localhost';
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | We Grant the privileges to our books database to the authors table.
| |
| − | | |
| − | En este caso, el usuario 'test' es autorizado a SELECT, INSERT and UPDATE sólo en la la tabla 'authors' de la database 'books'. En este caso 'test' ni siquiera podrá ver las otras databases o tables de 'books'. La única database que 'test' puede ver (además de books) es 'information_schema'
| |
| − | | |
| − | Note: INFORMATION_SCHEMA is a database within each MySQL instance, the place that stores information about all the other databases that the MySQL server maintains. The INFORMATION_SCHEMA database contains several readonly tables.
| |
| − | | |
| − | If we want revoke INSERT on 'test' user:
| |
| − | <syntaxhighlight lang="mysql">
| |
| − | REVOKE INSERT ON books.authors FROM 'test'@'localhost';
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ==Creación de una base de datos de prueba en MySQL==
| |
| − | Si queremos crear una base de datos extensa, podemos utilizar la base de datos sakila, la cual se puede descargar de:
| |
| − | wget http://downloads.mysql.com/docs/sakila-db.tar.gz
| |
| − | | |
| − | Para nuestras prácticas, vamos a crear una pequeña base de datos: '''books'''.
| |
| − | | |
| − | <figure id="fig:dbscheme">
| |
| − | [[File:Sample_database_scheme.png |600px | thumb | center | Esquema de la base de datos ''books''.]]
| |
| − | </figure>
| |
| − | | |
| − | Ingresar al prompt MySQL y ejecutar los siguientes comandos:
| |
| − | | |
| − | <syntaxhighlight lang="mysql">
| |
| − | CREATE DATABASE books;
| |
| − | | |
| − | USE books;
| |
| − | | |
| − | DROP TABLE IF EXISTS authors;
| |
| − | CREATE TABLE authors
| |
| − | (
| |
| − | au_id CHAR(3) NOT NULL,
| |
| − | au_fname VARCHAR(15) NOT NULL,
| |
| − | au_lname VARCHAR(15) NOT NULL,
| |
| − | phone VARCHAR(12) ,
| |
| − | address VARCHAR(20) ,
| |
| − | city VARCHAR(15) ,
| |
| − | state CHAR(2) ,
| |
| − | zip CHAR(5) ,
| |
| − | CONSTRAINT pk_authors PRIMARY KEY (au_id)
| |
| − | );
| |
| − | INSERT INTO authors VALUES('A01','Sarah','Buchman','718-496-7223','75 West 205 St','Bronx','NY','10468');
| |
| − | INSERT INTO authors VALUES('A02','Wendy','Heydemark','303-986-7020','2922 Baseline Rd','Boulder','CO','80303');
| |
| − | INSERT INTO authors VALUES('A03','Hallie','Hull','415-549-4278','3800 Waldo Ave, #14F','San Francisco','CA','94123');
| |
| − | INSERT INTO authors VALUES('A04','Klee','Hull','415-549-4278','3800 Waldo Ave, #14F','San Francisco','CA','94123');
| |
| − | INSERT INTO authors VALUES('A05','Christian','Kells','212-771-4680','114 Horatio St','New York','NY','10014');
| |
| − | INSERT INTO authors VALUES('A06','','Kellsey','650-836-7128','390 Serra Mall','Palo Alto','CA','94305');
| |
| − | INSERT INTO authors VALUES('A07','Paddy','O''Furniture','941-925-0752','1442 Main St','Sarasota','FL','34236');
| |
| − | | |
| − | | |
| − | DROP TABLE IF EXISTS publishers;
| |
| − | CREATE TABLE publishers
| |
| − | (
| |
| − | pub_id CHAR(3) NOT NULL,
| |
| − | pub_name VARCHAR(20) NOT NULL,
| |
| − | city VARCHAR(15) NOT NULL,
| |
| − | state CHAR(2) ,
| |
| − | country VARCHAR(15) NOT NULL,
| |
| − | CONSTRAINT pk_publishers PRIMARY KEY (pub_id)
| |
| − | );
| |
| − | INSERT INTO publishers VALUES('P01','Abatis Publishers','New York','NY','USA');
| |
| − | INSERT INTO publishers VALUES('P02','Core Dump Books','San Francisco','CA','USA');
| |
| − | INSERT INTO publishers VALUES('P03','Schadenfreude Press','Hamburg',NULL,'Germany');
| |
| − | INSERT INTO publishers VALUES('P04','Tenterhooks Press','Berkeley','CA','USA');
| |
| − | | |
| − | | |
| − | DROP TABLE IF EXISTS titles;
| |
| − | CREATE TABLE titles
| |
| − | (
| |
| − | title_id CHAR(3) NOT NULL,
| |
| − | title_name VARCHAR(40) NOT NULL,
| |
| − | type VARCHAR(10) ,
| |
| − | pub_id CHAR(3) NOT NULL,
| |
| − | pages INTEGER ,
| |
| − | price DECIMAL(5,2) ,
| |
| − | sales INTEGER ,
| |
| − | pubdate DATE ,
| |
| − | contract SMALLINT NOT NULL,
| |
| − | CONSTRAINT pk_titles PRIMARY KEY (title_id)
| |
| − | );
| |
| − | INSERT INTO titles VALUES('T01','1977!','history','P01',107,21.99,566,'2000-08-01',1);
| |
| − | INSERT INTO titles VALUES('T02','200 Years of German Humor','history','P03',14,19.95,9566,'1998-04-01',1);
| |
| − | INSERT INTO titles VALUES('T03','Ask Your System Administrator','computer','P02',1226,39.95,25667,'2000-09-01',1);
| |
| − | INSERT INTO titles VALUES('T04','But I Did It Unconsciously','psychology','P04',510,12.99,13001,'1999-05-31',1);
| |
| − | INSERT INTO titles VALUES('T05','Exchange of Platitudes','psychology','P04',201,6.95,201440,'2001-01-01',1);
| |
| − | INSERT INTO titles VALUES('T06','How About Never?','biography','P01',473,19.95,11320,'2000-07-31',1);
| |
| − | INSERT INTO titles VALUES('T07','I Blame My Mother','biography','P03',333,23.95,1500200,'1999-10-01',1);
| |
| − | INSERT INTO titles VALUES('T08','Just Wait Until After School','children','P04',86,10.00,4095,'2001-06-01',1);
| |
| − | INSERT INTO titles VALUES('T09','Kiss My Boo-Boo','children','P04',22,13.95,5000,'2002-05-31',1);
| |
| − | INSERT INTO titles VALUES('T10','Not Without My Faberge Egg','biography','P01',NULL,NULL,NULL,NULL,0);
| |
| − | INSERT INTO titles VALUES('T11','Perhaps It''s a Glandular Problem','psychology','P04',826,7.99,94123,'2000-11-30',1);
| |
| − | INSERT INTO titles VALUES('T12','Spontaneous, Not Annoying','biography','P01',507,12.99,100001,'2000-08-31',1);
| |
| − | INSERT INTO titles VALUES('T13','What Are The Civilian Applications?','history','P03',802,29.99,10467,'1999-05-31',1);
| |
| − | | |
| − | | |
| − | DROP TABLE IF EXISTS title_authors;
| |
| − | CREATE TABLE title_authors
| |
| − | (
| |
| − | title_id CHAR(3) NOT NULL,
| |
| − | au_id CHAR(3) NOT NULL,
| |
| − | au_order SMALLINT NOT NULL,
| |
| − | royalty_share DECIMAL(5,2) NOT NULL,
| |
| − | CONSTRAINT pk_title_authors PRIMARY KEY (title_id, au_id)
| |
| − | );
| |
| − | INSERT INTO title_authors VALUES('T01','A01',1,1.0);
| |
| − | INSERT INTO title_authors VALUES('T02','A01',1,1.0);
| |
| − | INSERT INTO title_authors VALUES('T03','A05',1,1.0);
| |
| − | INSERT INTO title_authors VALUES('T04','A03',1,0.6);
| |
| − | INSERT INTO title_authors VALUES('T04','A04',2,0.4);
| |
| − | INSERT INTO title_authors VALUES('T05','A04',1,1.0);
| |
| − | INSERT INTO title_authors VALUES('T06','A02',1,1.0);
| |
| − | INSERT INTO title_authors VALUES('T07','A02',1,0.5);
| |
| − | INSERT INTO title_authors VALUES('T07','A04',2,0.5);
| |
| − | INSERT INTO title_authors VALUES('T08','A06',1,1.0);
| |
| − | INSERT INTO title_authors VALUES('T09','A06',1,1.0);
| |
| − | INSERT INTO title_authors VALUES('T10','A02',1,1.0);
| |
| − | INSERT INTO title_authors VALUES('T11','A03',2,0.3);
| |
| − | INSERT INTO title_authors VALUES('T11','A04',3,0.3);
| |
| − | INSERT INTO title_authors VALUES('T11','A06',1,0.4);
| |
| − | INSERT INTO title_authors VALUES('T12','A02',1,1.0);
| |
| − | INSERT INTO title_authors VALUES('T13','A01',1,1.0);
| |
| − | | |
| − | | |
| − | DROP TABLE IF EXISTS royalties;
| |
| − | CREATE TABLE royalties
| |
| − | (
| |
| − | title_id CHAR(3) NOT NULL,
| |
| − | advance DECIMAL(9,2) ,
| |
| − | royalty_rate DECIMAL(5,2) ,
| |
| − | CONSTRAINT pk_royalties PRIMARY KEY (title_id)
| |
| − | );
| |
| − | INSERT INTO royalties VALUES('T01',10000,0.05);
| |
| − | INSERT INTO royalties VALUES('T02',1000,0.06);
| |
| − | INSERT INTO royalties VALUES('T03',15000,0.07);
| |
| − | INSERT INTO royalties VALUES('T04',20000,0.08);
| |
| − | INSERT INTO royalties VALUES('T05',100000,0.09);
| |
| − | INSERT INTO royalties VALUES('T06',20000,0.08);
| |
| − | INSERT INTO royalties VALUES('T07',1000000,0.11);
| |
| − | INSERT INTO royalties VALUES('T08',0,0.04);
| |
| − | INSERT INTO royalties VALUES('T09',0,0.05);
| |
| − | INSERT INTO royalties VALUES('T10',NULL,NULL);
| |
| − | INSERT INTO royalties VALUES('T11',100000,0.07);
| |
| − | INSERT INTO royalties VALUES('T12',50000,0.09);
| |
| − | INSERT INTO royalties VALUES('T13',20000,0.06);
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br/>
| |
| − | ==SQL Practice Lab 1==
| |
| − | * '''WHERE - OR - AND - DISTINCT - IN - LIKE'''
| |
| − | * '''AS:''' Usado para hacer ''aliases'' (renombrar una columna): select f_name as 'First name' from ...
| |
| − | | |
| − | '''All unique book types in the database:'''
| |
| − | <syntaxhighlight lang="mysql">
| |
| − | select distinct type from titles;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | '''List the full name, phone number, zip code of authors who live in New York or San Francisco:'''
| |
| − | <syntaxhighlight lang="mysql">
| |
| − | select au_fname, au_lname, phone, zip from authors where city="New York" or city="San Francisco";
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | '''All the publisher names and IDs, who are based in the USA and in New York city:'''
| |
| − | <syntaxhighlight lang="mysql">
| |
| − | select pub_name as 'Publisher name', pub_id as 'Publisher ID' from publishers where country='USA' and city='New York';
| |
| − | | |
| − | // or
| |
| − | | |
| − | select pub_name, pub_id from publishers where country in ('USA') and city in ('New York');
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | '''All the title IDs where the advance was between 50000 and 100000:'''
| |
| − | <syntaxhighlight lang="mysql">
| |
| − | select title_id, advance from royalties where advance >= 50000 and advance <= 100000;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | '''All the book title IDs, and book names where the type is either history or children (Using a set Operator):'''
| |
| − | <syntaxhighlight lang="mysql">
| |
| − | select title_id, title_name from titles where type="history" or type="children";
| |
| − | | |
| − | // or
| |
| − | | |
| − | select title_id, title_name from titles where type in ("history","children");
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | '''List all authors who live at an address that starts with the number 14:'''
| |
| − | <syntaxhighlight lang="mysql">
| |
| − | select * from authors where address LIKE "14%";
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | '''List all the book titles that contain a word that is 4 letters long, and starts with «w» and ends with «t»:'''
| |
| − | | |
| − | El profesor colocó que la respuesta es:
| |
| − | <syntaxhighlight lang="mysql">
| |
| − | select title_name from titles where title_name like '%w__t%';
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | Yo creo que esa respuesta no es completamente válida pues incluirá los títulos que tengan una palabra que contenga 'w__t' sin que necesariamente sea la primera y última letra de la palabra. Es decir una palabra de más de 4 lettres long que contenga el patrón 'w__t'.
| |
| − | | |
| − | La respuesta que creo debe acercarse más a la adecuada es:
| |
| − | <syntaxhighlight lang="mysql">
| |
| − | select title_name from titles
| |
| − | where title_name like 'w__t %'
| |
| − | or title_name like '% w__t %'
| |
| − | or title_name like '% w__t'
| |
| − | or title_name like 'w__t';
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | <span style="color:#FFFFFF; background:#483D8B">En estos casos MySQL no hace distinción entre mayúsculas o minúsculas.</span>
| |
| − | | |
| − | | |
| − | <br/>
| |
| − | ==SQL Practice Lab 2==
| |
| − | | |
| − | | |
| − | <br/>
| |
| − | ===Fecha y hora===
| |
| − | Para crear un campo de fecha y hora que se cree con la fecha y hora actual:
| |
| − | <syntaxhighlight lang="mysql">
| |
| − | CREATE TABLE fechas(
| |
| − | id INT(10) PRIMARY KEY AUTO_INCREMENT,
| |
| − | date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
| |
| − | );
| |
| − | | |
| − | INSERT INTO fechas() VALUES();
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | Si queremos que el campo date pueda ser nulo debemos usar «DATETIME» en vez de «TIMESTAMP»
| |
| − | | |
| − | <span style="color:#FFFFFF; background:#483D8B">NOTE: MySQL retrieves and displays DATE values in 'YYYY-MM-DD'</span>
| |
| − | | |
| − | Lo anterior creará el campo con el siguiente formato:
| |
| − | | |
| − | '''2017-12-01 00:33:25'''
| |
| − | | |
| − | Ahora, si queremos seleccionar las fechas con un formato distinto:
| |
| − | | |
| − | <syntaxhighlight lang="mysql">
| |
| − | select DATE_FORMAT(date,'%d-%m-%y') AS date from fechas;
| |
| − | | |
| − | select DATE_FORMAT(date,'%d-%m-%Y') AS date from fechas;
| |
| − | | |
| − | select DATE_FORMAT(date,'%D %M, %Y') AS date from fechas;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | The Name of the book published on the 1st August 2000?:
| |
| − | <syntaxhighlight lang="mysql">
| |
| − | select title_name as 'Titles of books published on the 1st August 2000'
| |
| − | from titles
| |
| − | where pubdate='2000-08-01';
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br/>
| |
| − | ===Ordering and pattern Matching===
| |
| − | *'''ORDER:''' Ordenar alfabética o numéricamente: '''ASC''' (por defecto) , '''DESC'''
| |
| − | | |
| − | List the type, pages (i.e. page size) and title name of all book titles, alphabetically ordered by type:
| |
| − | | |
| − | <syntaxhighlight lang="sql">
| |
| − | select type as 'Type', pages as 'Page size', title_name as 'Title name'
| |
| − | from titles
| |
| − | order by type;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | The type, page size, price and title name of all book titles, alphabetically ordered by type and increasing price:
| |
| − | <syntaxhighlight lang="sql">
| |
| − | select type as 'Type', pages as 'Page size', price as 'Price', title_name as 'Title name'
| |
| − | from titles
| |
| − | order by type, price;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br/>
| |
| − | ===Aggregate Functions===
| |
| − | *'''COUNT:''' Contar filas: select count(*) from titles where ...;
| |
| − | * '''MAX''' / '''MIN''' / '''AVG:''' select avg(pages) from titles;
| |
| − | *'''SUM():''' Sumar
| |
| − | | |
| − | How many book titles:
| |
| − | <syntaxhighlight lang="sql">
| |
| − | select count(title_name) as 'Amount of book titles' from titles;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | '''How many book titles have less than or equal to 200 pages:'''
| |
| − | <syntaxhighlight lang="sql">
| |
| − | select count(*) as 'Books less that or equal to 200 pages' from titles where pages <= 200;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | '''The minimum, maximum, and average page size of a book:'''
| |
| − | <syntaxhighlight lang="sql">
| |
| − | select min(pages) as 'Min pages', max(pages) as 'Max pages', avg(pages) as 'Average pages' from titles;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | '''The total price of all books:'''
| |
| − | <syntaxhighlight lang="mysql">
| |
| − | select sum(price) as 'Total price of all books (€)' from titles;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br/>
| |
| − | ===Joins===
| |
| − | * '''INNER JOIN'''
| |
| − | | |
| − | '''List all book titles that were published in New York:'''
| |
| − | <syntaxhighlight lang="sql">
| |
| − | select title_name as 'Title'
| |
| − | from titles, publishers
| |
| − | where titles.pub_id=publishers.pub_id and city="New York";
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | or,
| |
| − | | |
| − | <syntaxhighlight lang="sql">
| |
| − | select title_name as 'Title'
| |
| − | from titles t, publishers p
| |
| − | where t.pub_id=p.pub_id and city="New York";
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | or, in the NEW ANSI way:
| |
| − | | |
| − | <syntaxhighlight lang="sql">
| |
| − | select title_name as 'Title'
| |
| − | from titles t inner join publishers p
| |
| − | on t.pub_id=p.pub_id and city="New York";
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | '''List all publishers who have published children books:'''
| |
| − | <syntaxhighlight lang="sql">
| |
| − | select distinct pub_name as 'Publisher'
| |
| − | from publishers p inner join titles t
| |
| − | on p.pub_id=t.pub_id and type='children';
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | '''List all book titles that were published in New York, ordered alphabetically desc:'''
| |
| − | <syntaxhighlight lang="sql">
| |
| − | select title_name as 'Title'
| |
| − | from titles t inner join publishers p
| |
| − | on t.pub_id=p.pub_id and city='New York'
| |
| − | order by title_name desc;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | '''The name of the publisher who published the book titled 1977!:'''
| |
| − | <syntaxhighlight lang="mysql">
| |
| − | select pub_name as 'Publisher who published the book «1977!»'
| |
| − | from titles t inner join publishers p on t.pub_id=p.pub_id
| |
| − | where title_name='1977!';
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ===Having and grouping===
| |
| − | {| style="color: black; background-color: #9370DB; width: 100%;"
| |
| − | | colspan="2" |
| |
| − | |}
| |
| − | {| style="color: black; background-color: #D8BFD8; width: 100%;"
| |
| − | | colspan="2" |
| |
| − | * '''GROUP BY'''
| |
| − | * '''HAVING'''
| |
| − | |}
| |
| − | {| style="color: black; background-color: #9370DB; width: 100%;"
| |
| − | | colspan="2" |
| |
| − | |}
| |
| − | | |
| − | '''For each author_id: The number of books they have wrote:'''
| |
| − | <syntaxhighlight lang="sql">
| |
| − | select au_id as 'Author ID', count(title_id) as 'Number of books written'
| |
| − | from title_authors
| |
| − | group by au_id;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | '''For each author_id (who has wrote more than 2 books) the number of books they have wrote:'''
| |
| − | <syntaxhighlight lang="sql">
| |
| − | select au_id as 'Author ID (authors who wrote more than 2 books)', count(title_id) as 'Number of books they wrote'
| |
| − | from title_authors
| |
| − | group by au_id
| |
| − | having count(title_id) > 2;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | '''For each author_id and author firstname (who has wrote more than 2 books) the number of books they have wrote:'''
| |
| − | <syntaxhighlight lang="sql">
| |
| − | select t.au_id as 'Author ID (authors who wrote more than 2 books)', au_fname as 'Author firstname', count(title_id) as 'Number of books they wrote'
| |
| − | from title_authors t inner join authors a on t.au_id=a.au_id
| |
| − | group by t.au_id
| |
| − | having count(title_id) > 2;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br/>
| |
| − | ==SQL Practice Lab 3==
| |
| − | | |
| − | | |
| − | <br/>
| |
| − | ===Table creation===
| |
| − | '''Create an «instructors» table with 3 columns: ID, name and salary. The ID is the primary key. Choose appropriate data types:'''
| |
| − | <syntaxhighlight lang="mysql">
| |
| − | create table instructors(
| |
| − | id char(5),
| |
| − | name varchar(50) not null,
| |
| − | salary numeric(8,2),
| |
| − | primary key(id)
| |
| − | );
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | Note que '''«numeric()»''' también puede ser introducido como '''«decimal()»'''
| |
| − | | |
| − | | |
| − | <syntaxhighlight lang="mysql">
| |
| − | CREATE TABLE Work_on(
| |
| − | c_id INT NOT NULL,
| |
| − | p_id INT NOT NULL,
| |
| − | hours_worked INT,
| |
| − | CONSTRAINT Work_on_PK PRIMARY KEY (c_id, p_id),
| |
| − | CONSTRAINT Work_on_Chemist_FK FOREIGN KEY (c_id) REFERENCES Chemist (c_id),
| |
| − | CONSTRAINT Work_on_Projects_FK FOREIGN KEY (p_id) REFERENCES Project (p_id)
| |
| − | );
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <syntaxhighlight lang="mysql">
| |
| − | CREATE TABLE fechas(
| |
| − | id INT(10) PRIMARY KEY AUTO_INCREMENT,
| |
| − | date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
| |
| − | );
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br/>
| |
| − | ====Auto increment====
| |
| − | Si queremos que el '''«id»''' sea introducido automáticamente debemos usar '''«AUTO_INCREMENT»'''. En este caso el '''id''' debe ser de tipo '''«int»'''. De esta forma, si omitimos el '''id''' al ingresar un valor en la table, éste será agregado automáticamente con el valor numérico que corresponda (el siguiente número del mayor valor registrado)'''
| |
| − | <syntaxhighlight lang="mysql">
| |
| − | create table instructors(
| |
| − | id int auto_increment,
| |
| − | name varchar(50) not null,
| |
| − | salary numeric(8,2),
| |
| − | primary key(id)
| |
| − | );
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br/>
| |
| − | ===Insert values===
| |
| − | '''Insert two rows into the instructors table:'''
| |
| − | <syntaxhighlight lang="mysql">
| |
| − | insert into instructors values('1','Kike Dominguez',40000.70);
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | Si queremos dejar algunos campos vacíos (null), hay dos formas:
| |
| − | <syntaxhighlight lang="mysql">
| |
| − | insert into instructors values('2','Ernesto Vieira',null);
| |
| − | | |
| − | insert into instructors(id,name) values('3','Wilder Raben');
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br/>
| |
| − | ===Update/Changes===
| |
| − | '''Update/Changes the value of the name of one of the instructors:'''
| |
| − | <syntaxhighlight lang="mysql">
| |
| − | update instructors set name='Enrique Dominguez' where id='1';
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | '''Give all instructors a 5% pay increase:'''
| |
| − | <syntaxhighlight lang="mysql">
| |
| − | update instructors set salary=salary*1.05;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br/>
| |
| − | ===Delete rows===
| |
| − | '''Delete one of the rows based on the value of the ID:'''
| |
| − | <syntaxhighlight lang="mysql">
| |
| − | delete from instructors where id='3';
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br/>
| |
| − | ===Add attributes===
| |
| − | '''Add a new attribute/column (address) to the table:'''
| |
| − | <syntaxhighlight lang="mysql">
| |
| − | alter table instructors add address varchar(200);
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br/>
| |
| − | ===Chage the name of a attribute/column===
| |
| − | En MySQL, para cambiar el nombre de una columna debemos utilizar esta sintaxis. Notar que es necesario especificar el tipo de datos aunque no lo estemos cambiando, en este caso (NUMERIC(8,2)). El NOT NULL no es necesario especificarlo a menos que queramos cambiar esta opción:
| |
| − | | |
| − | <syntaxhighlight lang="mysql">
| |
| − | alter table instructors change column salary salary_a_year numeric(8,2) not null;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ==What is SQL PSM - Persistent Stored Module==
| |
| − | * SQL PSM is an ISO standard defining procedural language extensions to SQL to allow the creation of stored procedures and has been part of the SQL standard since SQL:1999.
| |
| − | * It provides syntatical elements to defne:
| |
| − | ** Control fow;
| |
| − | ** Exception handling;
| |
| − | ** Local variables;
| |
| − | ** Assignment of expressions to variables and parameters; and
| |
| − | ** Procedural use of cursors.
| |
| − | * DB2 and MySQL’s procedure languages are the closest to the SQL/PSM standard, although the language was heavily infuenced by PL/pgSQL.
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ==Stored Procedures==
| |
| − | * A stored procedure (SP) is a set of program statements compiled and stored in the RDBMS.
| |
| − | * Given the required permissions, they are accessible by end users and applications.
| |
| − | * Unlike regular SQL, which is a declarative language, they permit imperative programming styles, using control-flow constructs such as IF and FOR.
| |
| − | * SPs can be nested, where one procedure can call another.
| |
| − | * They support a limited degree of recursion, where a procedure can call itself.
| |
| − | * Each RDBMS supports a different range of programming languages for creating SPs:
| |
| − | ** MySQL: SQL/PSM;
| |
| − | ** Oracle: PL/SQL, Java;
| |
| − | ** ...
| |
| − | | |
| − | | |
| − | '''A complex Query:'''
| |
| − | | |
| − | Which employee has the highest sales?
| |
| − | | |
| − | <syntaxhighlight lang="sql">
| |
| − | SELECT employeeNumber, CONCAT(lastName,’,’,firstName) AS employeeName, SUM(customerTotal) AS totalSales
| |
| − | FROM employees JOIN
| |
| − | (SELECT salesRepEmployeeNumber, c.customerNumber, SUM(orderTotal) AS customerTotal
| |
| − | FROM customers c
| |
| − | JOIN orders o
| |
| − | ON c.customerNumber = o.customerNumber
| |
| − | JOIN
| |
| − | (SELECT orderNumber, SUM(quantityOrdered * priceEach) AS orderTotal
| |
| − | FROM orderdetails
| |
| − | GROUP BY orderNumber) AS details
| |
| − | ON o.orderNumber = details.orderNumber
| |
| − | GROUP BY customerNumber) AS salesByCustomer
| |
| − | ON employeeNumber = salesRepEmployeeNumber
| |
| − | GROUP BY employeeNumber
| |
| − | ORDER BY totalSales DESC;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | * In order to run this query without a stored procedure, the user would need permissions to access a number of different tables.
| |
| − | * Standard RDBMS permissions are not suficiently fine-grained to allow only these queries.
| |
| − | * In most scenarios we would want specific users to be able to execute only particular queries.
| |
| − | * Using stored procedures, access permissions can be limited to only certain queries.
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ===Generic Syntax===
| |
| − | NorthwindDB
| |
| − | <syntaxhighlight lang="sql">
| |
| − | DELIMITER //
| |
| − | CREATE PROCEDURE getAllProducts()
| |
| − | BEGIN
| |
| − | SELECT *
| |
| − | FROM Products;
| |
| − | END //
| |
| − | DELIMITER ;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | Calling this procedure is simple:
| |
| − | <syntaxhighlight lang="sql">
| |
| − | CALL getAllProducts();
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ===Parameters===
| |
| − | * A stored procedure can take zero or more parameters and each can be one of three types of parameter:
| |
| − | ** '''IN''' parameters. Used to pass values from the caller to the procedure. These need to be passed in with the CALL statement. Any changes made to the parameter as part of the script are not passed back to the caller. This is the default if no type is specifed.
| |
| − | ** '''OUT''' parameters. Used to pass values from the procedure to the caller. The caller will need to declare and provide a suitable variable.
| |
| − | ** '''INOUT''' parameters. Used to pass values from the caller to the procedure and vice-versa. Any changes made to the parameter during the procedure are available to the caller when the procedure finishes executing.
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ====IN Parameter Example====
| |
| − | NorthwindDB
| |
| − | <syntaxhighlight lang="sql">
| |
| − | DELIMITER //
| |
| − | CREATE PROCEDURE getEmployeeByID(IN employee_id int)
| |
| − | BEGIN
| |
| − | SELECT EmployeeID, LastName
| |
| − | FROM Employees
| |
| − | WHERE EmployeeID = employee_id;
| |
| − | END //
| |
| − | DELIMITER ;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | <syntaxhighlight lang="sql">
| |
| − | CALL getEmployeeByID(7);
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | Como siempre, en este tipo de operaciones (''EmployeeID = employee_id'') el nombre del parameter tiene que ser distinto al nombre del Attribute. <span style="background:#00FF00">Es muy importante notar que ''SQL'' no hace distinción entre Mayúsculas y Minúsculas. Por tanto, definir la operación de arriba de la siguiente forma: '''''EmployeeID = employeeid''''' sería incorrecto.</span>
| |
| − | | |
| − | | |
| − | Ejemplo con otra DB
| |
| − | <syntaxhighlight lang="sql">
| |
| − | DELIMITER //
| |
| − | CREATE PROCEDURE GetSalesByCountry (IN countryid int)
| |
| − | BEGIN
| |
| − | SELECT transaction_id,country_id, trans_date_time,product_id,quantity,amount
| |
| − | FROM sales
| |
| − | WHERE country_id = countryid;
| |
| − | END//
| |
| − | DELIMITER ;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | <syntaxhighlight lang="sql">
| |
| − | CALL GetSalesByCountry(353); 10/30
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ====OUT Parameter Example====
| |
| − | NorthwindDB
| |
| − | <syntaxhighlight lang="sql">
| |
| − | DELIMITER //
| |
| − | CREATE PROCEDURE GetTotalPrice(OUT totalprice decimal(19,4))
| |
| − | BEGIN
| |
| − | SELECT SUM(UnitPrice) INTO totalprice
| |
| − | FROM Products;
| |
| − | END //
| |
| − | DELIMITER ;
| |
| − | </syntaxhighlight>
| |
| − | «decimal(19,4)» is the Type of variable. You can check it with DESCRIBE Products;
| |
| − | | |
| − | <syntaxhighlight lang="sql">
| |
| − | CALL GetTotalPrice(@totalprice);
| |
| − | SELECT @totalprice AS Total_price;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | NorthwindDB
| |
| − | <syntaxhighlight lang="sql">
| |
| − | DELIMITER //
| |
| − | CREATE PROCEDURE GetTotalPriceUntilID(IN product_id int, OUT totalprice decimal(19,4))
| |
| − | BEGIN
| |
| − | SELECT SUM(UnitPrice) INTO totalprice
| |
| − | FROM Products
| |
| − | WHERE ProductID < product_id;
| |
| − | END //
| |
| − | DELIMITER ;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | <syntaxhighlight lang="sql">
| |
| − | CALL GetTotalPriceUntilID(6, @totalprice);
| |
| − | SELECT @totalprice AS Total_price;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ====INOUT Parameter Example====
| |
| − | No DB involved in this operation
| |
| − | <syntaxhighlight lang="sql">
| |
| − | DROP PROCEDURE AddSalesTax;
| |
| − | DELIMITER //
| |
| − | CREATE PROCEDURE AddSalesTax(INOUT sales_amount DECIMAL(8,2), IN tax_percentage double)
| |
| − | BEGIN
| |
| − | SET sales_amount = sales_amount + (sales_amount * tax_percentage);
| |
| − | END//
| |
| − | DELIMITER ;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | <syntaxhighlight lang="sql">
| |
| − | SET @salesamount = 100.11;
| |
| − | CALL AddSalesTax(@salesamount, 50.20);
| |
| − | SELECT @salesamount;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ===Variables===
| |
| − | * Variables are used to temporarily hold values for processing during the execution of a stored procedure.
| |
| − | * They are created using the DECLARE keyword and can be assigned a default value using the DEFAULT keyword.
| |
| − | * With the exception of user-defined variables (those starting with a @), variables must be assigned a type, such as VARCHAR(10), INT.
| |
| − | * Values can be assigned to variables either using:
| |
| − | ** SET: for assigning a variable a literal value or the value of another variable (or itself)
| |
| − | ** SELECT . . . INTO: to assign the results of a SELECT statement to the variable.
| |
| − | | |
| − | | |
| − | '''An example of the use of variables:'''
| |
| − | <syntaxhighlight lang="sql">
| |
| − | DELIMITER //
| |
| − | CREATE PROCEDURE GetTotalProductCount()
| |
| − | BEGIN
| |
| − | DECLARE totalcount INT DEFAULT 0 ;
| |
| − | SELECT COUNT (DISTINCT productCode)
| |
| − | FROM current_products
| |
| − | INTO totalcount;
| |
| − | SELECT COUNT (DISTINCT productCode) + totalcount AS totalproductcount
| |
| − | FROM archived_products;
| |
| − | END //
| |
| − | DELIMITER;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ===Control Flow===
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ====The IF statement====
| |
| − | <syntaxhighlight lang="sql">
| |
| − | IF condition THEN
| |
| − | ...statements...
| |
| − | ELSEIF condition THEN
| |
| − | ...statements...
| |
| − | ELSE
| |
| − | ...statements...
| |
| − | END IF;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ====The IF ELSE statement====
| |
| − | <syntaxhighlight lang="sql">
| |
| − | IF condition THEN
| |
| − | ...statements...
| |
| − | ELSE
| |
| − | ...statements...
| |
| − | END IF;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ====The IF ELSEIF ELSE statement====
| |
| − | <syntaxhighlight lang="sql">
| |
| − | IF condition THEN
| |
| − | ...statements...
| |
| − | ELSEIF condition THEN
| |
| − | ...statements...
| |
| − | ELSE
| |
| − | ...statements...
| |
| − | END IF;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ====The CASE statement====
| |
| − | * The CASE statement is an alternative to IF statments with multiple ELSEIF clauses.
| |
| − | * Code written with the CASE statement is more readble and also gives a slight increase in performance over the IF. . . ELSEIF alternative.
| |
| − | * The CASE statement comes in two forms, "simple" and "searched"
| |
| − | | |
| − | | |
| − | '''The Simple CASE statement:'''
| |
| − | | |
| − | The simple CASE statement checks for a match against one or more non-conditional expressions. The ELSE clause is optional but will raise an error if no match is found
| |
| − | | |
| − | <syntaxhighlight lang="sql">
| |
| − | CASE expression
| |
| − | WHEN expression_a THEN statements;
| |
| − | WHEN expression_b THEN statements;
| |
| − | ...
| |
| − | ELSE statements;
| |
| − | END CASE;
| |
| − | | |
| − | CASE shippingType
| |
| − | WHEN 'Express' THEN SET shipping_time = 1;
| |
| − | WHEN 'Normal' THEN SET shipping_time = 3;
| |
| − | ELSE SET shipping_time = 5;
| |
| − | END CASE;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | '''The Searched CASE statement:'''
| |
| − | The searched CASE statement allows the use of checks for a match against one or more conditional expressions. The ELSE clause is optional but will raise an error if no match is found.
| |
| − | | |
| − | <syntaxhighlight lang="sql">
| |
| − | CASE
| |
| − | WHEN condition_a THEN statements;
| |
| − | WHEN condition_b THEN statements;
| |
| − | ...
| |
| − | ELSE statements;
| |
| − | END CASE;
| |
| − | | |
| − | CASE
| |
| − | WHEN (num_orders > 100 AND total_value > 100000) THEN SET cust_category = 'Premium';
| |
| − | WHEN (num_orders > 50 OR total_value > 50000) THEN SET cust_category = 'Advantage';
| |
| − | ELSE SET customer_category = 'Standard';
| |
| − | END CASE;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ====The WHILE statement====
| |
| − | <syntaxhighlight lang="sql">
| |
| − | WHILE condition DO
| |
| − | ...statements...
| |
| − | END WHILE;
| |
| − | WHILE a < 20 DO
| |
| − | a = a + 1;
| |
| − | END WHILE;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ====The REPEAT statement====
| |
| − | <syntaxhighlight lang="sql">
| |
| − | REPEAT
| |
| − | ...statements...
| |
| − | UNTIL condition;
| |
| − | | |
| − | REPEAT
| |
| − | a = a + 1;
| |
| − | UNTIL a = 20;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ====The LOOP statement====
| |
| − | <syntaxhighlight lang="sql">
| |
| − | label : LOOP
| |
| − | ...statements...
| |
| − | IF condition THEN LEAVE label;
| |
| − | END IF;
| |
| − | ...statements...
| |
| − | END LOOP label;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ==Stored Functions==
| |
| − | * A stored function is stored routine that returns a single value.
| |
| − | * It is not called like a stored procedure, but is used as part of an expression inside SQL queries or stored procedures.
| |
| − | * Stored functions take only IN parameters and must specify a return data type (INT, VARCHAR, etc.)
| |
| − | * The function must include at least one RETURN statement
| |
| − | * If the function is deterministic (produces identical output for any given input) then this must be declared.
| |
| − | | |
| − | <syntaxhighlight lang="sql">
| |
| − | CREATE FUNCTION function_name(param1,param2,...) RETURNS datatype [NOT] DETERMINISTIC
| |
| − | BEGIN
| |
| − | statements
| |
| − | END
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <syntaxhighlight lang="sql">
| |
| − | DELIMITER //
| |
| − | CREATE FUNCTION inverse(value DOUBLE) RETURNS DOUBLE DETERMINISTIC
| |
| − | BEGIN
| |
| − | RETURN 1/value;
| |
| − | END //
| |
| − | DELIMITER ;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <syntaxhighlight lang="sql">
| |
| − | DELIMITER //
| |
| − | CREATE FUNCTION customerLevel(creditLimit DOUBLE) RETURNS VARCHAR(10)
| |
| − | DETERMINISTIC
| |
| − | BEGIN
| |
| − | DECLARE level VARCHAR(10);
| |
| − | IF creditLimit > 50000 THEN SET level='PLATINUM';
| |
| − | ELSEIF (creditLimit <=50000 AND creditLimit >=10000) THEN SET level='GOLD';
| |
| − | ELSEIF creditLimit <10000 THEN SET level=’SILVER’;
| |
| − | END IF;
| |
| − | RETURN(lvl);
| |
| − | END //
| |
| − | DELIMITER ;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ==Triggers==
| |
| − | * Triggers are a special kind of stored procedure that executes automatically when certain operations are carried out on table.
| |
| − | * Depending on the RDBMS in use they can be defined to run before, after or instead of DML (Data Manipulation Language) operations such as INSERT, UPDATE, and DELETE.
| |
| − | * MySQL does not have an "instead of" option
| |
| − | * They are useful for implementing complex integrity checks as well as auditing.
| |
| − | * Although useful, they add some overhead to the RDBMS
| |
| − | | |
| − | | |
| − | '''Generic syntax for creating a trigger in MySQL:'''
| |
| − | <syntaxhighlight lang="sql">
| |
| − | CREATE TRIGGER «triggername» {BEFORE|AFTER} {INSERT|UPDATE|DELETE }
| |
| − | ON tablename
| |
| − | FOR EACH ROW {FOLLOWS|PRECEDES} «othertriggername»
| |
| − | BEGIN
| |
| − | END;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | * On creation, the trigger becomes associated with the table named tablename, which must refer to a permanent table.
| |
| − | * Triggers can not be created for a TEMPORARY table or a view.
| |
| − | * The optional FOLLOWS or PRECEDES «othertriggername» allows triggers to be fired in a specific sequence.
| |
| − | | |
| − | | |
| − | '''Creating triggers:'''
| |
| − | | |
| − | Although not required, it is good practice to follow a naming convention such as:
| |
| − | <syntaxhighlight lang="sql">
| |
| − | time_tablename_operation (for example: before_customer_update)
| |
| − | tablename_time_operation (for example: customer_before_update)
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | * Inside the body of the trigger, the keyword OLD is used to refer to the row (tuple) before it is updated or deleted and NEW is used to refer to the row that will be inserted or an existing row after it is updated.
| |
| − | | |
| − | * Triggers on INSERT operations can only use the NEW values, DELETE only to the OLD values, and UPDATE can use both.
| |
| − | | |
| − | | |
| − | '''Trigger Example:'''
| |
| − | <syntaxhighlight lang="sql">
| |
| − | DELIMITER \\
| |
| − | CREATE TRIGGER after_payroll_update AFTER INSERT ON payroll FOR EACH ROW
| |
| − | BEGIN
| |
| − | INSERT INTO payroll_audit SET
| |
| − | operation = 'INSERT',
| |
| − | employeeNumber = OLD.employeeNumber,
| |
| − | gross_pay = OLD.gross_pay,
| |
| − | tax = OLD.tax,
| |
| − | prsi = OLD.prsi,
| |
| − | net_pay = OLD.net_pay,
| |
| − | user = CURRENT_USER(),
| |
| − | audit_timestamp = NOW();
| |
| − | END \\
| |
| − | DELIMITER ;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | '''Triggers Exercise:'''
| |
| − | | |
| − | Using the Sakila database, create a trigger that sets the "Active" column of the customer record to True when they rent a video, if and only if the record is no longer false.
| |
| − | | |
| − | <syntaxhighlight lang="sql">
| |
| − | USE sakila;
| |
| − | DELIMITER \\
| |
| − | CREATE TRIGGER SetCustomerActive AFTER INSERT ON rental
| |
| − | FOR EACH ROW
| |
| − | BEGIN
| |
| − | UPDATE customer
| |
| − | SET active = TRUE
| |
| − | WHERE customer_id = NEW.customer_id;
| |
| − | END \\
| |
| − | DELIMITER ;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ==Events==
| |
| − | An event is a special kind of trigger that executes according to a schedule rather than when a particular operation is carried out on a table. As such they can be considered '''time-based''' triggers.
| |
| − | | |
| − | Events require that the event scheduler thread is enabled. This can be done using the following code:
| |
| − | <syntaxhighlight lang="sql">
| |
| − | SET GLOBAL event_scheduler = ON;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | '''To verify that the event scheduler is running, use the following command:'''
| |
| − | <syntaxhighlight lang="sql">
| |
| − | SHOW PROCESSLIST;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | '''Generic syntax for creating an event in MySQL:'''
| |
| − | <syntaxhighlight lang="sql">
| |
| − | CREATE EVENT eventname
| |
| − | ON SCHEDULE eventschedule DO
| |
| − | eventbody;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | <syntaxhighlight lang="sql">
| |
| − | CREATE EVENT IF NOT EXISTS test_event
| |
| − | ON SCHEDULE AT CURRENT_TIMESTAMP DO
| |
| − | INSERT INTO messages(message,created at)
| |
| − | VALUES('Test MySQL Event 1',NOW());
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | The eventschedule clause takes one of two forms, depending whether the event is a 'one-shot' or repeated. See the following slides for details.
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ===Event Schedules===
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ====One shot====
| |
| − | These examples assume the existence of a table created as follows:
| |
| − | <syntaxhighlight lang="sql">
| |
| − | CREATE TABLE IF NOT EXISTS messages (
| |
| − | id INT PRIMARY KEY AUTO_INCREMENT,
| |
| − | message VARCHAR(255) NOT NULL,
| |
| − | created_at DATETIME NOT NULL
| |
| − | );
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | The AT timestamp syntax is used for a one-time event. In this case the event will execute once at
| |
| − | the date and time given by timestamp:
| |
| − | <syntaxhighlight lang="sql">
| |
| − | CREATE EVENT IF NOT EXISTS test_event2 ON SCHEDULE
| |
| − | AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO
| |
| − | INSERT INTO messages(message,created at)
| |
| − | VALUES(’Test MySQL Event 2’,NOW());
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ====Repeated====
| |
| − | To have an event repeat at a regular interval, the EVERY interval syntax is used. The generic form is:
| |
| − | EVERY interval
| |
| − | STARTS timestamp [+ interval] ENDS timestamp [+ interval]
| |
| − | | |
| − | | |
| − | Example:
| |
| − | <syntaxhighlight lang="sql">
| |
| − | CREATE EVENT IF NOT EXISTS test_event ON SCHEDULE
| |
| − | EVERY 1 HOUR STARTS CURRENT_TIMESTAMP
| |
| − | ENDS CURRENT_TIMESTAMP + 1 DAY DO
| |
| − | INSERT INTO messages(message,created at)
| |
| − | VALUES(’Test MySQL Event 3’,NOW());
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | Events Exercise:
| |
| − | * Using the Sakila database, create an event that runs every night at midnight and returns a list of email addresses for customers who are overdue.
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ==Cursors==
| |
| − | A database cursor is a movable pointer to an individual row within a query result. This allows rows to be operated on individually, using the imperative programming style, instead of as a set, using the declarative programming style of standard SQL.
| |
| − | | |
| − | A cursor can be used inside any stored program, such as a stored procedure. Any given stored program may use more than one cursor provided that each is given a unique name.
| |
| − | | |
| − | Cursors in MySQL have the following properties:
| |
| − | * They are read only, in other words, the result set is not updatable
| |
| − | * Cursor read the entire results of their select statement into memory, so should be used with caution
| |
| − | * They are non-scrollable. Cursors can only be traversed in one direction and rows can not be skipped.
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ===Declaring and using cursors===
| |
| − | Cursors are declared for a SELECT statement using the syntax below:
| |
| − | <syntaxhighlight lang="sql">
| |
| − | DECLARE cursor_name CURSOR
| |
| − | FOR SELECT ...;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | In order to use the cursor, it must be opened. This is when the SELECT statement is executed:
| |
| − | <syntaxhighlight lang="sql">
| |
| − | OPEN cursor_name;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | Rows in the cursor are accessed using the FETCH keyword:
| |
| − | <syntaxhighlight lang="sql">
| |
| − | FETCH cursor_name INTO variables;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | The FETCH statement will raise a NOT FOUND condition when no more rows are available. In order to handle this, a NOT FOUND handler must be declared as part of the stored program. This usually sets a boolean variable that is used as part of a WHILE loop to a state where the loop terminates:
| |
| − | <syntaxhighlight lang="sql">
| |
| − | DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = 1;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | When they are no longer needed, cursors need to be closed to release any associated memory used on the server:
| |
| − | <syntaxhighlight lang="sql">
| |
| − | CLOSE cursor_name;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ===Cursor Example===
| |
| − | <syntaxhighlight lang="sql">
| |
| − | DELIMITER //
| |
| − | CREATE PROCEDURE build_email_list (INOUT email_list varchar(4000))
| |
| − | BEGIN
| |
| − | DECLARE v_done INTEGER DEFAULT 0;
| |
| − | DECLARE v_email varchar(100) DEFAULT "";
| |
| − | DECLARE email_cursor CURSOR FOR SELECT email FROM employees; -- declare cursor
| |
| − | DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1; -- declare NOT FOUND handler
| |
| − | OPEN email_cursor; -- open the cursor
| |
| − | FETCH email_cursor INTO v_email;
| |
| − | WHILE NOT v_done DO -- loop over result set
| |
| − | SET email_list = CONCAT(v_email,";",email_list);
| |
| − | FETCH email_cursor INTO v_email;
| |
| − | END WHILE;
| |
| − | END//
| |
| − | DELIMITER ;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ===Cursors Exercise===
| |
| − | Using the Sakila database, create a stored procedure that uses a cursor to iterate through a query on the the rentals table giving the last rental for each customer and sets the active column in the customer table to false if this date is more than a year ago.
| |
| − | | |
| − | You will need to back up the database before doing this and restore it afterwards, as the procedure changes the data. The query to return the most recent rental for each customer is shown below. Use this as the select statement for your cursor.
| |
| − | | |
| − | <syntaxhighlight lang="sql">
| |
| − | SELECT r1.
| |
| − | FROM rental r1
| |
| − | WHERE r1.rental_date = (SELECT MAX(r2.rental_date)
| |
| − | FROM rental r2
| |
| − | WHERE r2.customer_id = r1.customer_id)
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ==Transactions==
| |
| − | A transaction is a collection of one or more database operations that must all either complete successfully or fail entirely.
| |
| − | | |
| − | These typically constitute a single real-world transaction, such as the transfer of money between bank accounts, where if one account is debited, the other must be simultaneously credited.
| |
| − | | |
| − | A transaction is an all or nothing proposition. There are only to possible outcomes for a transaction. Either it is committed and all the requested changes are made to the database or it aborts and no changes are made. This is what is referred to as '''atomicity'''.
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ===ACID Properties===
| |
| − | In a relational database, all transactions have four fundamental properties:
| |
| − | * '''Atomicity''', all parts of the transaction must either succeed or fail.
| |
| − | * '''Consistency''', the database must be transformed from one consistent state to another.
| |
| − | * '''Isolation''', the effects of incomplete transactions should not be visible to other transactions.
| |
| − | * '''Durability''', the effects of a committed transaction are permanent and should not be lost due to later failure.
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ===Transactions in MySQL===
| |
| − | There are four statements in MySQL that provide transaction functionality. The SET TRANSACTION statement is used to specify the properties of transactions.
| |
| − | | |
| − | <syntaxhighlight lang="SQL">
| |
| − | SET [GLOBAL | SESSION] TRANSACTION
| |
| − | [REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE]
| |
| − | [READ WRITE | READ ONLY]
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | When the GLOBAL option is specified, the statement applies globally for all subsequent sessions. Existing sessions are unaffected. If the SESSION keyword is used, the statement applies to all subsequent transactions performed within the current session.
| |
| − | | |
| − | If neither is specified, the statement applies to the next transaction performed in the current session. Any subsequent transactions will use the SESSION isolation level.
| |
| − | | |
| − | | |
| − | '''Transaction isolation:'''
| |
| − | | |
| − | The SQL standard specifies four transaction isolation levels, the first two of which are described below:
| |
| − | * Read Uncommitted - Any transaction may read changes made by other transaction that have not yet been committed, allowing "dirty reads" to take place. Transactions with this level are not isolated from each other.
| |
| − | * Read Committed - A transaction may only read is changes that have been committed by other transactions, disallowing "dirty reads." In order to achieve this, a transaction will place a read or write lock on the row it is currently updating, preventing other transactions from reading, updating or deleting it.
| |
| − | * Repeatable Read - A transaction will place read locks on all rows it references and write locks on all rows it will modify. Other transactions cannot read, update or delete these rows, ensuring all reads are repeatable.
| |
| − | * Serializable - This is the most restrictive level and guarantees that a transaction will retrieve exactly the same data when it repeats a read operation. However, the locking strategy associated with this level can cause problems for other users in multi-user systems.
| |
| − | | |
| − | All four are supported by MySQL when the InnoDB engine is used. The default isolation level is REPEATABLE READ.
| |
| − | | |
| − | | |
| − | '''Starting a transaction:'''
| |
| − | | |
| − | Transactions are initiated with the START TRANSACTION command:
| |
| − | <syntaxhighlight lang="SQL">
| |
| − | START TRANSACTION [WITH CONSISTENT SNAPSHOT | READ WRITE | READ ONLY]
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | The WITH CONSISTENT SNAPSHOT option applies only to InnoDB databases and is effectively the same as issuing a START TRANSACTION followed by a SELECT from any InnoDB table.
| |
| − | | |
| − | The READ WRITE and READ ONLY options are used to allow or disallow changes to the tables used in the transaction. The READ ONLY option prevents the transaction from modifying or locking both transactional and nontransactional tables that are visible to other transactions.
| |
| − | | |
| − | | |
| − | '''Committing and rolling back a transaction:'''
| |
| − | | |
| − | Assuming a transaction has been started with START TRANSACTION:
| |
| − | * The COMMIT statement will commit all the changes made by the transaction, making its changes permanent.
| |
| − | * The ROLLBACK statement will “roll back”" all of the transaction, cancelling its changes.
| |
| − | | |
| − | Rollbacks can happen for a variety of reasons, either under the control of the database programmer or the system:
| |
| − | * Account balance is not sufficient to complete transfer
| |
| − | * Order quantity is less than the quantity in stock
| |
| − | * Timeout of a transaction or recovery from a system crash
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ==Exception Handling==
| |
| − | When an error occurs in a stored procedure, stored function or trigger, it is important that it is handled appropriately, rather than allowing the database engine to return a (cryptic) error message.
| |
| − | | |
| − | MySQL provides straightforward functionality to define handlers that deal with a range of error conditions from the more general, such as warnings or exceptions, to more specific error codes.
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ===Declaring Exception Handlers===
| |
| − | Handlers must be declared after variables and cursors, and use the following syntax:
| |
| − | | |
| − | <syntaxhighlight lang="sql">
| |
| − | DECLARE <handler action> HANDLER FOR <condition value>
| |
| − | <statement>
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | Where handler action can be one of:
| |
| − | * CONTINUE - Execution of the current program continues.
| |
| − | * EXIT - Execution terminates for the BEGIN . . . END compound statement in which the handler is declared. This is true even if the condition occurs in an inner block.
| |
| − | | |
| − | A further value "UNDO" is currently not supported in MySQL but is intended to execute a rollback.
| |
| − | | |
| − | | |
| − | Where condition value can be one of:
| |
| − | * An integer literal indicating a MySQL error code;
| |
| − | * SQLSTATE value other than those that begin with ‘00’, as these indicate success rather than an error condition;
| |
| − | * SQLWARNING condition - shorthand for any SQLSTATE value that begins with ‘01’; or
| |
| − | * SQLEXCEPTION condition - shorthand any SQLSTATE value that do not begin with '00', '01', or '02'.
| |
| − | * NOT FOUND condition - shorthand for SQLSTATE values that begin with ‘02’; or
| |
| − | * Named condition associated with a MySQL error code or SQLSTATE value.
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ===Examples===
| |
| − | If SQLSTATE 23000 (duplicate key value) is encountered, issue an error message for the calling program (using the SELECT statement) and then continue execution:
| |
| − | | |
| − | <syntaxhighlight lang="sql">
| |
| − | DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
| |
| − | SELECT 'Duplicate key in index';
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | If MySQL error value is 1216 (FK constraint violated) issue an error message to the calling program and exit.
| |
| − | <syntaxhighlight lang="sql">
| |
| − | BEGIN
| |
| − | DECLARE CONTINUE HANDLER FOR 1216 SELECT 'Foreign key constraint violated';
| |
| − | INSERT INTO employees(first_name,surname, date_of_birth, department_id)
| |
| − | VALUES ('Jane','Smith','1997-03-12',12);
| |
| − | END;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | Example using Sakila Film table
| |
| − | If the title of a film being inserted into the film table are identical to those that already exist, then return a message to calling program.
| |
| − | | |
| − | <syntaxhighlight lang="sql">
| |
| − | DELIMITER $$
| |
| − | CREATE PROCEDURE insert_film(IN title VARCHAR(255) ... <other columns>)
| |
| − | BEGIN
| |
| − | -- declare a continue handler
| |
| − | DECLARE CONTINUE HANDLER FOR 1062
| |
| − | SELECT CONCAT('Warning: Film with title ',title,' already found') AS msg;
| |
| − | -- insert a new record into film
| |
| − | INSERT INTO film(title, <other columns>) VALUES(title, <other values>);
| |
| − | END
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | '''Exercise:'''
| |
| − | * Using the Sakila database, add a unique index for film title and year, as shown below. We will revisit indexing in the coming weeks.
| |
| − | | |
| − | <syntaxhighlight lang="sql">
| |
| − | ALTER TABLE film ADD CONSTRAINT uk_film_year
| |
| − | UNIQUE KEY (title,release_year);
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | * Create a stored procedure to add a film to the film table, taking IN parameters for all columns except film_id and last_update.
| |
| − | * In the stored procedure, output a message if the combination of the title and year are not unique.
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ==Assessment==
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ===Part I - Procedural SQL - Individual Assessment===
| |
| − | Submit your code for this assessment as a single SQL file, naming it with your student number xxxxxxxx.sql, where xxxxxxxx is your student number. This should be submitted to the Individual Assessment link on Moodle.
| |
| − | | |
| − | Due date: 18th November 2018, 23:55 (at the very latest).
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ====Question 1 - Load the NorthWind database using the supplied SQL format backup file====
| |
| − | http://perso.sinfronteras.ws/index.php/File:NorthwindDB.zip
| |
| − | | |
| − | mysql -u root -p < /home/adelo/1-system/desktop/it_cct/3-Advanced_Databases/0-1-databases_usadas_en_este_curso/NorthwindDB/NorthwindDB.sql
| |
| − | | |
| − |
| |
| − | <br />
| |
| − | ====Question 2 - Create a stored procedure====
| |
| − | Using the NorthWind database, create a stored procedure named sp_LowStock to return the following columns (in order) for all products where the stock level is below the reorder level:
| |
| − | * Supplier Name;
| |
| − | * Product Name;
| |
| − | * Stock Deficit (units in stock minus reorder level); and
| |
| − | * Reorder Level
| |
| − | | |
| − | Create SQL statements to execute this procedure and display the results.
| |
| − | | |
| − | NorthwindDB
| |
| − | <syntaxhighlight lang="sql">
| |
| − | DELIMITER //
| |
| − | DROP PROCEDURE IF EXISTS sp_LowStock()//
| |
| − | CREATE PROCEDURE sp_LowStock()
| |
| − | BEGIN
| |
| − | SELECT CompanyName as "Supplier Name",
| |
| − | ProductName as "Product Name",
| |
| − | UnitsInStock - ReorderLevel as "Stock Deficit",
| |
| − | ReorderLevel as "Reorder Level"
| |
| − | FROM Products p INNER JOIN Suppliers s
| |
| − | ON p.ProductID = s.SupplierID and UnitsInStock < ReorderLevel;
| |
| − | END //
| |
| − | DELIMITER ;
| |
| − | | |
| − | CALL sp_LowStock();
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ====Question 3 - Create a stored procedure====
| |
| − | Create a stored procedure called sp_EmployeeTerritories that takes one IN parameter (the Employee ID) and returns the following columns using two OUT parameters:
| |
| − | * Employee Name (joining Title, FirstName and LastName as one column); and
| |
| − | * Territories (a comma-separated list of territories assigned to the employee).
| |
| − | | |
| − | Create SQL statements to execute this procedure for an employee id of your choosing and display the results.
| |
| − | | |
| − | NorthwindDB
| |
| − | <syntaxhighlight lang="sql">
| |
| − | DELIMITER //
| |
| − | DROP PROCEDURE IF EXISTS sp_EmployeeTerritories//
| |
| − | CREATE PROCEDURE sp_EmployeeTerritories(
| |
| − | IN employee_id int,
| |
| − | OUT employee_details char(200),
| |
| − | OUT terr_assigned char(200)
| |
| − | )
| |
| − | BEGIN
| |
| − | SELECT DISTINCT
| |
| − | CONCAT(Title,", ",FirstName," ", LastName) AS "Employee",
| |
| − | GROUP_CONCAT(TerritoryDescription SEPARATOR ", ") AS "Territories assigned"
| |
| − | INTO employee_details, terr_assigned
| |
| − | FROM Employees e INNER JOIN EmployeeTerritories et INNER JOIN Territories t
| |
| − | ON e.EmployeeID = et.EmployeeID AND
| |
| − | t.TerritoryID = et.TerritoryID AND
| |
| − | e.EmployeeID = employee_id;
| |
| − | END //
| |
| − | DELIMITER ;
| |
| − | | |
| − | CALL sp_EmployeeTerritories(4, @employee_details, @terr_assigned);
| |
| − | SELECT @employee_details AS "Employee", @terr_assigned AS "Territories assigned";
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ====Question 4 - Create a stored procedure====
| |
| − | Create a stored procedure called sp_AddOrderItem that takes four IN parameters:
| |
| − | * The order id;
| |
| − | * The product id;
| |
| − | * The quantity; and
| |
| − | * A discount amount;
| |
| − | and returns the quantity added to the order.
| |
| − | | |
| − | This procedure will need to:
| |
| − | * check the quantity of the product in stock and add the requested quantity if sufficient stock is available, otherwise add the available quantity;
| |
| − | * fetch the current price from the products table and use that as the unit price;
| |
| − | * deduct the quantity ordered from the units in stock and adjust the units in stock accordingly; and
| |
| − | * Contain an exception hander to handle duplicated order items. You will need to decide if this should be a CONTINUE or EXIT handler.
| |
| − | | |
| − | <syntaxhighlight lang="SQL">
| |
| − | DELIMITER //
| |
| − | DROP PROCEDURE IF EXISTS sp_AddOrderItem//
| |
| − | CREATE PROCEDURE sp_AddOrderItem(
| |
| − | IN in_order_id int,
| |
| − | IN in_product_id int,
| |
| − | IN in_quantity_asked int,
| |
| − | IN in_disc_amount float,
| |
| − | OUT out_quantity_added int
| |
| − | )
| |
| − | | |
| − | BEGIN
| |
| − | DECLARE v_units_in_stock int;
| |
| − | DECLARE v_unit_price decimal(19,4);
| |
| − | DECLARE v_in_order_id int;
| |
| − | DECLARE v_in_product_id int;
| |
| − | DECLARE v_quantity_order_details int;
| |
| − | | |
| − | /*
| |
| − | DECLARE EXIT HANDLER FOR NOT FOUND
| |
| − | BEGIN
| |
| − | CASE
| |
| − | WHEN v_in_order_id IS NULL THEN SELECT CONCAT('OrderID: ', in_order_id, ' Not found') AS WARNING;
| |
| − | WHEN v_in_product_id IS NULL THEN SELECT CONCAT('ProductID: ', in_product_id, ' Not found') AS WARNING;
| |
| − | WHEN v_units_in_stock IS NULL THEN SELECT CONCAT('UnitsInStock Not found for ProductID: ', in_product_id) AS WARNING;
| |
| − | WHEN v_unit_price IS NULL THEN SELECT CONCAT('UnitPrice not found for ProductID: ', in_product_id) AS WARNING;
| |
| − | END CASE;
| |
| − | END;
| |
| − | */
| |
| − |
| |
| − | SELECT OrderID INTO v_in_order_id FROM Orders WHERE OrderID = in_order_id;
| |
| − | | |
| − | SELECT ProductID, UnitPrice, UnitsInStock
| |
| − | INTO v_in_product_id, v_unit_price, v_units_in_stock
| |
| − | FROM Products
| |
| − | WHERE ProductID = in_product_id;
| |
| − |
| |
| − | SELECT Quantity
| |
| − | INTO v_quantity_order_details
| |
| − | FROM Order_Details
| |
| − | WHERE OrderID = in_order_id AND ProductID = in_product_id;
| |
| − |
| |
| − |
| |
| − | CASE
| |
| − | WHEN v_in_order_id IS NULL THEN
| |
| − | SELECT CONCAT('OrderID: ', in_order_id, ' Not found') AS WARNING;
| |
| − | SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'OrderID Not found';
| |
| − |
| |
| − | WHEN v_in_product_id IS NULL THEN
| |
| − | SELECT CONCAT('ProductID: ', in_product_id, ' Not found') AS WARNING;
| |
| − | SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'ProductID Not found';
| |
| − |
| |
| − | WHEN v_units_in_stock IS NULL THEN
| |
| − | SELECT CONCAT('UnitsInStock Not found for ProductID: ', in_product_id) AS WARNING;
| |
| − | SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'UnitsInStock Not found (NULL) for ProductID entered';
| |
| − |
| |
| − | WHEN v_unit_price IS NULL THEN
| |
| − | SELECT CONCAT('UnitPrice not found for ProductID: ', in_product_id) AS WARNING;
| |
| − | SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'UnitPrice not found (NULL) for ProductID entered';
| |
| − |
| |
| − | ELSE BEGIN END;
| |
| − | END CASE;
| |
| − |
| |
| − |
| |
| − | IF v_quantity_order_details IS NOT NULL THEN
| |
| − | SET v_units_in_stock = v_units_in_stock + v_quantity_order_details;
| |
| − | END IF;
| |
| − |
| |
| − |
| |
| − | IF (v_units_in_stock = 0) THEN
| |
| − | SELECT CONCAT("No units in stock for ProductID: ", v_in_product_id) AS WARNING;
| |
| − | SIGNAL SQLSTATE '45000'
| |
| − | SET MESSAGE_TEXT = 'No units in stock for selected product';
| |
| − | END IF;
| |
| − |
| |
| − |
| |
| − | IF (v_units_in_stock >= in_quantity_asked) THEN
| |
| − | SET out_quantity_added = in_quantity_asked;
| |
| − | ELSE
| |
| − | SET out_quantity_added = v_units_in_stock;
| |
| − | SELECT CONCAT("Units in stock (",v_units_in_stock,") less than units asked (",in_quantity_asked,"). Only ", v_units_in_stock, " units were added to to order") AS WARNING;
| |
| − | END IF;
| |
| − | | |
| − | | |
| − | START TRANSACTION;
| |
| − |
| |
| − | UPDATE Products
| |
| − | SET UnitsInStock = v_units_in_stock - out_quantity_added
| |
| − | WHERE ProductID = in_product_id;
| |
| − |
| |
| − |
| |
| − | IF v_quantity_order_details IS NOT NULL THEN
| |
| − | UPDATE Order_Details
| |
| − | SET UnitPrice = v_unit_price,
| |
| − | Quantity = out_quantity_added,
| |
| − | Discount = in_disc_amount
| |
| − | WHERE OrderID = in_order_id AND ProductID = in_product_id;
| |
| − | ELSE
| |
| − | INSERT INTO Order_Details values(in_order_id,
| |
| − | in_product_id,
| |
| − | v_unit_price,
| |
| − | out_quantity_added,
| |
| − | in_disc_amount);
| |
| − | END IF;
| |
| − |
| |
| − | COMMIT;
| |
| − | | |
| − | END //
| |
| − | DELIMITER ;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | <syntaxhighlight lang="SQL">
| |
| − | CALL sp_AddOrderItem(10248,9,9,0,@out_quantity_added);
| |
| − | SELECT @out_quantity_added AS "Quantity added to the order";
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ====Question 5 - Create a stored function====
| |
| − | Create a stored function called OrderFulfilmentCycleTime to return the average difference between the date the order was placed and the date it was shipped.
| |
| − | | |
| − | <syntaxhighlight lang="SQL">
| |
| − | DELIMITER //
| |
| − | DROP FUNCTION IF EXISTS OrderFulfilmentCycleTime//
| |
| − | CREATE FUNCTION OrderFulfilmentCycleTime() RETURNS decimal(19,1) DETERMINISTIC
| |
| − | BEGIN
| |
| − | RETURN(SELECT AVG(DATEDIFF(ShippedDate, OrderDate)) FROM Orders);
| |
| − | END //
| |
| − | DELIMITER ;
| |
| − | | |
| − | select OrderFulfilmentCycleTime();
| |
| − | | |
| − | | |
| − | -- -- Pruebas:
| |
| − | -- SELECT AVG(DATEDIFF(ShippedDate, OrderDate))
| |
| − | -- FROM Orders;
| |
| − | | |
| − | -- SELECT OrderID, ShippedDate, OrderDate, (DATEDIFF(ShippedDate, OrderDate))
| |
| − | -- FROM Orders;
| |
| − | | |
| − | -- --
| |
| − | | |
| − | -- SELECT AVG(DATE(ShippedDate)-DATE(OrderDate))
| |
| − | -- FROM Orders;
| |
| − |
| |
| − | -- SELECT OrderID, ShippedDate, OrderDate, (DATE(ShippedDate)-DATE(OrderDate))
| |
| − | -- FROM Orders;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ====Question 6 - Create a trigger====
| |
| − | Create a trigger for the orders table to prevent more than one order being deleted at a given time and returning an error message to the client. You will need to determine when this trigger should be fired.
| |
| − | | |
| − | <syntaxhighlight lang="sql">
| |
| − | DELIMITER //
| |
| − | DROP TRIGGER IF EXISTS before_Orders_delete//
| |
| − | CREATE TRIGGER before_Orders_delete BEFORE DELETE ON Orders FOR EACH ROW
| |
| − | BEGIN
| |
| − | -- DECLARE contar1 int;
| |
| − | -- DECLARE contar2 int;
| |
| − | -- SELECT COUNT(*) INTO contar1 FROM Orders where Orders.OrderID=old.OrderID;
| |
| − | -- SELECT COUNT(*) INTO contar2 FROM Orders;
| |
| − | -- set @a = ROW_COUNT();
| |
| − | | |
| − | -- IF ROW_COUNT() > 0 THEN
| |
| − | -- IF contar2 < (contar1 - 1) THEN
| |
| − |
| |
| − | IF @contar IS NULL THEN
| |
| − | SET @contar = 1;
| |
| − | ELSE
| |
| − | SET @contar = NULL;
| |
| − | SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'You are trying to delete more than one row at the same time and that is not allowed. No column has been deleted.';
| |
| − | END IF;
| |
| − | END //
| |
| − | DELIMITER ;
| |
| − | | |
| − | DELETE FROM Orders WHERE OrderID=10469 OR OrderID=10473;
| |
| − | -- DELETE FROM Orders WHERE OrderID > 10374;
| |
| − | SELECT ROW_COUNT();
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ====Question 7 - Create a Trigger====
| |
| − | Create a table SupplierOrders with the following structure.
| |
| − | * ProductID int(11)
| |
| − | * SupplierID int(11)
| |
| − | * Date datetime
| |
| − | * Quantity smallint(6)
| |
| − | | |
| − | You will need to choose an appropriate primary key for the table.
| |
| − | | |
| − | Once you have created the table, then create a trigger that automatically inserts an order in this table when the UnitsInStock value for the product goes below ReorderLevel. You will need to determine when this trigger will be fired.
| |
| − | | |
| − | | |
| − | <syntaxhighlight lang="sql">
| |
| − | DROP TABLE IF EXISTS SupplierOrders;
| |
| − | | |
| − | CREATE TABLE SupplierOrders(
| |
| − | ProductID INT(11) NOT NULL,
| |
| − | SupplierID INT(11) NOT NULL,
| |
| − | Date DATETIME NOT NULL,
| |
| − | Quantity SMALLINT(6),
| |
| − | CONSTRAINT SupplierOrders_PK PRIMARY KEY (ProductID, SupplierID, Date),
| |
| − | CONSTRAINT SupplierOrders_Products_FK FOREIGN KEY (ProductID) REFERENCES Products (ProductID),
| |
| − | CONSTRAINT SupplierOrders_Suppliers_FK FOREIGN KEY (SupplierID) REFERENCES Suppliers (SupplierID)
| |
| − | );
| |
| − | | |
| − | DELIMITER //
| |
| − | DROP TRIGGER IF EXISTS after_Products_update//
| |
| − | CREATE TRIGGER after_Products_update AFTER UPDATE ON Products FOR EACH ROW
| |
| − | BEGIN
| |
| − | IF (NEW.UnitsInStock < NEW.ReorderLevel) THEN
| |
| − | INSERT INTO SupplierOrders values(NEW.ProductID,
| |
| − | NEW.SupplierID,
| |
| − | CURRENT_TIMESTAMP,
| |
| − | 100 + NEW.ReorderLevel);
| |
| − | END IF;
| |
| − | END //
| |
| − | DELIMITER ;
| |
| − | | |
| − | UPDATE Products
| |
| − | SET UnitsInStock = 24
| |
| − | WHERE ProductID = 22;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ====Question 8 - Create a Event====
| |
| − | Add a column to the Products table to indicate whether or not the product has not been sold during the last 12 months. Call this column LegacyProduct giving it a default value of FALSE. Create an event called LegacyProducts that checks if the product has been ordered within the last twelve months and sets the value of the LegacyProduct to TRUE if it has not. This event should fire once per week, at 2330 on Sundays.
| |
| − | | |
| − | <syntaxhighlight lang="sql">
| |
| − | ALTER TABLE Products DROP LegacyProduct;
| |
| − | | |
| − | ALTER TABLE Products ADD LegacyProduct boolean NOT NULL DEFAULT FALSE;
| |
| − | | |
| − | SET GLOBAL event_scheduler = ON;
| |
| − | | |
| − | DELIMITER //
| |
| − | CREATE EVENT IF NOT EXISTS LegacyProducts ON SCHEDULE
| |
| − | -- EVERY 2 MINUTE STARTS "2018-11-25 01:54:00" DO
| |
| − | EVERY 1 WEEK STARTS "2018-11-25 23:30:00" DO
| |
| − | BEGIN
| |
| − | -- SELECT * FROM Products WHERE ProductID IN (
| |
| − | UPDATE Products SET LegacyProduct = TRUE WHERE ProductID NOT IN (
| |
| − | SELECT ProductID
| |
| − | FROM Orders o INNER JOIN Order_Details od
| |
| − | ON o.OrderID = od.OrderID
| |
| − | WHERE OrderDate > DATE_SUB(NOW(), INTERVAL 1 YEAR)
| |
| − | );
| |
| − | END //
| |
| − | DELIMITER ;
| |
| − | </syntaxhighlight>
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ===Part II - Group Assessment===
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ====Part II a - Query Optimisation====
| |
| − | | |
| − | | |
| − | <br />
| |
| − | =====Question 1 - Propose an example query to demonstrate something you have studied in the recommended reading or found online=====
| |
| − | Propose an example query to demonstrate something you have studied in the recommended reading or found online. Use either the Employees Database or the Northwind database for this task and reference the pages of the recommended reading or provide the URL of the web site where you got the idea to test this type of SQL query.
| |
| − | | |
| − | Note: A summary of your proposal should be posted to Moodle in the All Questions Forum under the topic Assessment Part IIa. The query you intend to test cannot be already posted by another group.
| |
| − | | |
| − | | |
| − | <br />
| |
| − | =====Question 2 - State what you expect to happen for the chosen database=====
| |
| − | State what you expect to happen for the chosen database. Give a detailed explanation, including calculations of rows you expect to be accessed etc., clearly outlining what you think should happen.
| |
| − | | |
| − | | |
| − | <br />
| |
| − | =====Question 3 - Write imaginary usage statistics for your chosen query=====
| |
| − | Write imaginary usage statistics for your chosen query: i.e. "we will suppose that this query runs 60 times per hour on average & 150 times per hour at peak". Provide a transaction analysis as per the Connolly and Begg book, figure 18.4 in 5th and 6th Editions, figure 17.4 in 4th Edition.
| |
| − | | |
| − | | |
| − | <br />
| |
| − | =====Question 4 - Give a detailed account of what you did=====
| |
| − | Give a detailed account of what you did, including:
| |
| − | : a) a full SQL audit trail;
| |
| − | : b) an explanation of each step you carried out;
| |
| − | : c) the state of the database before the query execution;
| |
| − | : d) the output of the EXPLAIN command;
| |
| − | : e) the state of the database after the query execution; and
| |
| − | : f) the time taken for query execution
| |
| − | | |
| − | | |
| − | <br />
| |
| − | =====Question 5 - Comment on the actual outcome versus the expected outcome=====
| |
| − | Comment on the actual outcome versus the expected outcome. State whether or not there is a difference between what you expected and what you got – give an explanation.
| |
| − | | |
| − | | |
| − | <br />
| |
| − | =====Question 6 - Propose an optimisation of for an update, delete or insert operation=====
| |
| − | Propose an optimisation of for an update, delete or insert operation. Outline the effect your optimisation can be expected to have.
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ====Part II b - Database Design====
| |
| − | A university's central library wishes to create a database to store details of its titles and borrowers. Details include the following:
| |
| − | | |
| − | * Each book has a unique ISBN number, a title, an edition and one or more authors.
| |
| − | | |
| − | * Each serial has a unique ISSN number, a title, an issue date, an editor and one or more authors
| |
| − | | |
| − | * The library often stocks several copies of any book or serial publication, each of which is given a unique accession number. The price that was paid for each copy and its location is also recorded.
| |
| − | | |
| − | * The central library has one or more branch libraries in individual faculty buildings.
| |
| − | | |
| − | * Each borrower has a name and student number. A borrower can have more than one book or serial publication on loan, but each physical copy can only be on loan to one borrower.
| |
| − | | |
| − | * Some books are designated "reference only" and can not be borrowed, but only read in the library.
| |
| − | | |
| − | * A borrower may borrow the same book on several separate occasions.
| |
| − | | |
| − | | |
| − | * Using an appropriate tool, create an Extended-Entity-Relationship model to comply with the above specification.
| |
| − | | |
| − | | |
| − | <br />
| |
| − | =====Extended Entity-Relationship model=====
| |
| − | We are going to use the Chen's ERD style.
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ======Regular entities and attributes======
| |
| − | <blockquote>
| |
| − | | |
| − | {| class="wikitable"
| |
| − | !Book ||Serial ||Publication ||Borrower
| |
| − | |-
| |
| − | |bgcolor="#EEE8AA"|<span style="color:#72777d>'''<u>accessionNumber</u>'''</span> ||bgcolor="#EEE8AA"|<span style="color:#72777d>'''<u>accessionNumber</u>'''</span> ||bgcolor="#EEE8AA"|'''<u>accessionNumber</u>''' ||'''<u>studentNumber</u>'''
| |
| − | |-
| |
| − | |bgcolor="#EEE8AA"|<span style="color:#72777d>ISBNnumber</span> ||bgcolor="#EEE8AA"|<span style="color:#72777d>ISBNnumber</span> ||bgcolor="#EEE8AA"|ISBNnumber ||name
| |
| − | |-
| |
| − | |bgcolor="#EEE8AA"|<span style="color:#72777d>title</span> ||bgcolor="#EEE8AA"|<span style="color:#72777d>title</span> ||bgcolor="#EEE8AA"|title
| |
| − | |-
| |
| − | |bgcolor="#EEE8AA"|<span style="color:#72777d>authors</span> ||bgcolor="#EEE8AA"|<span style="color:#72777d>authors</span> ||bgcolor="#EEE8AA"|authors
| |
| − | |-
| |
| − | |bgcolor="#EEE8AA"|<span style="color:#72777d>location</span> ||bgcolor="#EEE8AA"|<span style="color:#72777d>location</span> ||bgcolor="#EEE8AA"|location
| |
| − | |-
| |
| − | |bgcolor="#EEE8AA"|<span style="color:#72777d>pricePaid</span> ||bgcolor="#EEE8AA"|<span style="color:#72777d>pricePaid</span> ||bgcolor="#EEE8AA"|pricePaid
| |
| − | |-
| |
| − | |bgcolor="#cc99ff"|edition ||bgcolor="#cc99ff"|editor ||
| |
| − | |-
| |
| − | |bgcolor="#cc99ff"|referenceOnly ||bgcolor="#cc99ff"|issueDate ||
| |
| − | |}
| |
| − | | |
| − | * Each book has a unique ISBN number. The library often stocks several copies of any book or serial publication, each of which is given a:
| |
| − | ** Unique accession number: So, this would be the primary key because is unique for each copy
| |
| − | ** The price that was paid for each copy and
| |
| − | ** Its location is also recorded
| |
| − | | |
| − | * Some books are designated "reference only" and can not be borrowed, but only read in the library.
| |
| − | | |
| − | | |
| − | * '''authors:''' Multi-value attribute. A publication can have more than one author.
| |
| − | ** In this case, a second table named «Authors» is needed. The primary key will includes the ISBNnumber referencing the primary key in the Publication table. There will be an '''identifying relationship''' between «Publication» and «Author».
| |
| − | | |
| − | </blockquote>
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ======Relationship between entities======
| |
| − | <blockquote>
| |
| − | | |
| − | * '''Cardinality ratios:'''
| |
| − | ** A borrower can have more than one book or serial publication on loan, but each physical copy can only be on loan to one borrower:
| |
| − | *** One-to-many (1:M) relationship.
| |
| − | | |
| − | | |
| − | * '''Partial/Total participation:'''
| |
| − | ** Logical Assumptions:
| |
| − | *** An item (Book or Serial) can not be on loan at a given time: ''Partial participation''
| |
| − | *** A Borrower can have no books on loan at a given time: ''Partial participation''
| |
| − | | |
| − | | |
| − | * '''Generalization:'''
| |
| − | ** In this case «Books» and «Serials» can be generalized into «Publications»
| |
| − | ** This generalization must be disjointed. A Publication can either be a Book or a Serial, but not both.
| |
| − | ** <span style="background:DarkKhaki">No me queda completamente clara la diferencia entre Disjoint/Overlap con Union
| |
| − | </span>
| |
| − | | |
| − | * '''«Borrower»Partial 1<borrows>N «Publication»Partial'''
| |
| − | | |
| − | | |
| − | * A borrower may borrow the same book on several separate occasions.
| |
| − | </blockquote>
| |
| − | | |
| − | | |
| − | <br />
| |
| − | ===Part III - Individual Report on Group Work===
| |
| − | * What parts did you participate in for Parts IIa and IIb above?
| |
| − | * What did you learn from working within a team?
| |
| − | * What would you do differently if you had to build it again?
| |
| − | * What did you find most difficult to implement or understand.
| |
| − | | |
| − | | |
| − | <br />
| |
