Import and Manage MySQL Databases: Step-by-Step Guide
Learn how to download, unzip, import, and manage MySQL databases using the classicmodels database as an example. This comprehensive guide covers connecting to the MySQL server, loading data, viewing databases, creating stored functions, calling functions in SQL statements, and dropping functions. Improve your database management skills with this detailed tutorial.
Uploaded on | 0 Views
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
Databases 2 MySQL
Import classicmodels database Step 1 Download the classicmodels.zip database from the people.vts.su.ac.rs/~simon/bp2 section. Step 2 Unzip the downloaded file into a temporary folder. You can use any folder you want. To make it simple, we will unzip the file to the C:\temp folder. Step 3 Connect to the MySQL server using the mysql client program. The mysql program is located in the bin directory of the MySQL installation folder. > mysql -u root -p Enter password: ******** Code language: SQL (Structured Query Language) (sql) You will need to enter the password for the root user account to log in. Step 4 Use the source command to load data into the MySQL Server: mysql> source c:\temp\mysqlsampledatabase.sql Code language: SQL (Structured Query Language) (sql) Step 5 Use the SHOW DATABASES command to list all databases in the current server: mysql> show databases; Code language: SQL (Structured Query Language) (sql) The output will look like the following that includes the newly created classicmodels database
Stored functions MySQL
MySQL CREATE FUNCTION syntax DELIMITER $$ CREATE FUNCTION function_name( param1, param2, ) RETURNS datatype [NOT] DETERMINISTIC BEGIN -- statements END $$ DELIMITER ;
Stored function DELIMITER $$ CREATE FUNCTION CustomerLevel( credit DECIMAL(10,2) ) RETURNS VARCHAR(20) DETERMINISTIC BEGIN DECLARE customerLevel VARCHAR(20); IF credit > 50000 THEN ELSEIF (credit >= 50000 AND SET customerLevel = 'GOLD'; ELSEIF credit < 10000 THEN SET customerLevel = 'SILVER'; END IF; -- return the customer level RETURN (customerLevel); END$$ DELIMITER ; SET customerLevel = 'PLATINUM'; credit <= 10000) THEN
Calling a stored function in an SQL statement SELECT customerName, CustomerLevel(creditLimit) FROM customers ORDER BY customerName;
MySQL DROP FUNCTION DROP FUNCTION [IF EXISTS] function_name;
Stored procedures MySQL
MySQL CREATE PROCEDURE syntax DELIMITER $$ CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body proc_parameter: [ IN | OUT | INOUT ] param_name type type: Any valid MySQL data type characteristic: COMMENT 'string' | LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } routine_body: Valid SQL routine statement END $$ DELIMITER ;
Stored procedures that return multiple values DELIMITER $$ CREATE PROCEDURE get_order_by_cust( IN cust_no INT, OUT shipped INT, OUT canceled INT, OUT resolved INT, OUT disputed INT) BEGIN -- shipped SELECT count(*) INTO shipped FROM orders WHERE customerNumber = cust_no AND status = 'Shipped';
-- canceled SELECT -- disputed count(*) INTO disputed FROM orders WHERE customerNumber = cust_no AND status = 'Disputed'; count(*) INTO canceled FROM orders WHERE customerNumber = cust_no AND status = 'Canceled'; SELECT count(*) INTO resolved FROM orders WHERE customerNumber = cust_no AND status = 'Resolved'; -- resolved SELECT END $$ DELIMITER ;
Calling a stored procedure in an SQL statement CALL get_order_by_cust(141,@shipped,@canceled,@resolved,@disputed); SELECT @shipped,@canceled,@resolved,@disputed;
MySQL DROP PROCEDURE DROP PROCEDURE [IF EXISTS] procedure_name;
PHP MySQL: Call MySQL Stored Procedures The steps of calling a MySQL stored procedure that returns a result set using PHP PDO are similar to querying data from MySQL database table using the SELECT statement. Instead of sending a SELECT statement to MySQL database, you send a stored procedure call statement. DELIMITER $$ CREATE PROCEDURE GetCustomers() BEGIN SELECT customerName, creditlimit FROM customers; END $$ DELIMITER ;
phpmysqlstoredprocedure1.php <table> <tr> <th>Customer Name</th> <th>Credit Limit</th> </tr> <?php while ($r = $q->fetch()): ?> <tr> <td><?php echo $r['customerName'] ?></td> <td><?php echo '$' . number_format($r['creditlimit'], 2) ?> </td> </tr> <?php endwhile; ?> </table> </body> </html> <!DOCTYPE html> <html> <head> <title>PHP MySQL Stored Procedure Demo 1</title> <link rel="stylesheet" href="css/table.css" type="text/css" /> </head> <body> <?php require_once 'dbconfig.php'; try { $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password); // execute the stored procedure $sql = 'CALL GetCustomers()'; // call the stored procedure $q = $pdo->query($sql); $q->setFetchMode(PDO::FETCH_ASSOC); } catch (PDOException $e) { die("Error occurred:" . $e->getMessage()); } ?>
table.css dbconfig.php table { font-family: arial,sans-serif; font-size:14px; color:#333333; border: solid 1px #666; border-collapse: collapse; } table th { border: solid 1px #025B85; padding: 10px; background-color: #025B85; color:#fff; } table td { padding: 10px; border: solid 1px #666; background-color: #ffffff; } <?php $host = 'localhost'; $dbname = 'classicmodels'; $username = 'root'; $password = ''; ?>
Transactions MySQL
Introducing to MySQL transactions To understand what a transaction in MySQL is, let s take a look at an example of adding a new sales order in our sample database. The steps of adding a sales order are as described as follows: First, query the latest sales order number from the orders table and use the next sales order number as the new sales order number. Next, insert a new sales order into the orders table. Then, get the newly inserted sales order number After that, insert the new sales order items into the orderdetails table with the sales order number Finally, select data from both orders and orderdetails tables to confirm the changes Now, imagine what would happen to the sales order data if one or more steps above fail due to some reasons such as table locking? For example, if the step of adding order s items into orderdetails table fails, you will have an empty sales order. That is why the transaction processing comes to the rescue. MySQL transaction allows you to execute a set of MySQL operations to ensure that the database never contains the result of partial operations. In a set of operations, if one of them fails, the rollback occurs to restore the database to its original state. If no error occurs, the entire set of statements is committed to the database.
MySQL transaction statements MySQL provides us with the following important statement to control transactions: To start a transaction, you use the START TRANSACTION statement. The BEGIN or BEGIN WORK are the aliases of the START TRANSACTION. To commit the current transaction and make its changes permanent, you use the COMMIT statement. To roll back the current transaction and cancel its changes, you use the ROLLBACK statement. To disable or enable the auto-commit mode for the current transaction, you use the SET autocommit statement.
By default, MySQL automatically commits the changes permanently to the database. To force MySQL not to commit changes automatically, you use the following statement: SET autocommit = 0; Or SET autocommit = OFF You use the following statement to enable the autocommit mode explicitly: SET autocommit = 1; Or SET autocommit = ON;
MySQL transaction example We will use the orders and orderDetails table from the sample database for the demonstration.
COMMIT example In order to use a transaction, we first have to break the SQL statements into logical portions and determine when data should be committed or rolled back. The following illustrates the step of creating a new sales order: First, start a transaction by using the START TRANSACTION statement. Next, select the latest sales order number from the orders table and use the next sales order number as the new sales order number. Then, insert a new sales order into the orders table. After that, insert sales order items into the orderdetails table. Finally, commit the transaction using the COMMIT statement. Optionally, we can select data from both orders and orderdetails tables to check the new sales order.
The following is the script that performs the above steps: -- 1. start a new transaction START TRANSACTION; -- 2. Get the latest order number SELECT @orderNumber:=MAX(orderNUmber)+1 FROM orders; -- 3. insert a new order for customer 145 INSERT INTO orders(orderNumber, orderDate, requiredDate, shippedDate, status, customerNumber) VALUES(@orderNumber, '2005-05-31', '2005-06-10', '2005-06-11', 'In Process', 145);
-- 4. Insert order line items INSERT INTO orderdetails(orderNumber, productCode, quantityOrdered, priceEach, orderLineNumber) VALUES(@orderNumber,'S18_1749', 30, '136', 1), (@orderNumber,'S18_2248', 50, '55.09', 2); -- 5. commit changes COMMIT;
To get the newly created sales order, you use the following query: SELECT a.orderNumber, orderDate, requiredDate, shippedDate, status, comments, customerNumber, orderLineNumber, productCode, quantityOrdered, priceEach FROM orders a INNER JOIN orderdetails b USING (orderNumber) WHERE a.ordernumber = 10426; Here is the output:
ROLLBACK example First, log in to the MySQL database server and delete data from the orders table: mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> DELETE FROM orders; Query OK, 327 rows affected (0.03 sec) As we can see from the output, MySQL confirmed that all the rows from the orders table were deleted.
Second, log in to the MySQL database server in a separate session and query data from the orders table: mysql> SELECT COUNT(*) FROM orders; +----------+ | COUNT(*) | +----------+ | 327 | +----------+ 1 row in set (0.00 sec) In this second session, we still can see the data from the orders table. We have made the changes in the first session. However, the changes are not permanent. In the first session, we can either commit or roll back the changes.
For the demonstration purpose, we will roll back the changes in the first session. mysql> ROLLBACK; Query OK, 0 rows affected (0.04 sec) in the first session, we will also verify the contents of the orders table: mysql> SELECT COUNT(*) FROM orders; +----------+ | COUNT(*) | +----------+ | 327 | +----------+ 1 row in set (0.00 sec) As we can see clearly from the output, the changes have been rolled back.
Triggers MySQL
Triggers In MySQL, a trigger is a stored program invoked automatically in response to an event such as insert, update, or delete that occurs in the associated table. For example, you can define a trigger that is invoked automatically before a new row is inserted into a table. MySQL supports triggers that are invoked in response to the INSERT, UPDATE or DELETE event.
Managing MySQL triggers Create triggers describe steps of how to create a trigger in MySQL. Drop triggers show you how to drop a trigger. Create a BEFORE INSERT trigger show you how to create a BEFORE INSERT trigger to maintain a summary table from another table. Create an AFTER INSERT trigger describe how to create an AFTER INSERT trigger to insert data into a table after inserting data into another table. Create a BEFORE UPDATE trigger learn how to create a BEFORE UPDATE trigger that validates data before it is updated to the table. Create an AFTER UPDATE trigger show you how to create an AFTER UPDATE trigger to log the changes of data in a table. Create a BEFORE DELETE trigger show how to create a BEFORE DELETE trigger. Create an AFTER DELETE trigger describe how to create an AFTER DELETE trigger. Create multiple triggers for a table that have the same trigger event and time MySQL 8.0 allows you to define multiple triggers for a table that have the same trigger event and time. Show triggers list triggers in a database, table by specific patterns.
Create Trigger in MySQL CREATE TRIGGER trigger_name {BEFORE | AFTER} {INSERT | UPDATE| DELETE } ON table_name FOR EACH ROW trigger_body;
Create a new table named employees_audit to keep the changes to the employees table CREATE TABLE employees_audit ( id INT AUTO_INCREMENT PRIMARY KEY, employeeNumber INT NOT NULL, lastname VARCHAR(50) NOT NULL, changedat DATETIME DEFAULT NULL, action VARCHAR(50) DEFAULT NULL ); CREATE TRIGGER before_employee_update BEFORE UPDATE ON employees FOR EACH ROW INSERT INTO employees_audit SET action = 'update', employeeNumber = OLD.employeeNumber, lastname = OLD.lastname, changedat = NOW(); SHOW TRIGGERS;
UPDATE employees SET lastName = 'Phan' WHERE employeeNumber = 1056; SELECT * FROM employees_audit;
MySQL DROP TRIGGER DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name; First, create a table called billings for demonstration: Second, create a new trigger called BEFORE UPDATE that is associated with the billings table: CREATE TABLE billings ( billingNo INT AUTO_INCREMENT, customerNo INT, billingDate DATE, amount DEC(10 , 2 ), PRIMARY KEY (billingNo) ); DELIMITER $$ CREATE TRIGGER before_billing_update BEFORE UPDATE ON billings FOR EACH ROW BEGIN IF new.amount > old.amount * 10 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'New amount cannot be 10 times greater than the current amount.'; END IF; END$$ DELIMITER ; Note: The trigger activates before any update. If the new amount is 10 times greater than the current amount, the trigger raises an error.
Third, show the triggers: Fourth, drop the before_billing_update trigger: SHOW TRIGGERS; DROP TRIGGER before_billing_update; Finally, show the triggers again to verify the removal: SHOW TRIGGERS;
MySQL BEFORE INSERT Trigger MySQL BEFORE INSERT triggers are automatically fired before an insert event occurs on the table. DELIMITER $$ Note that in a BEFORE INSERT trigger, you can access and change the NEW values. However, you cannot access the OLD values because OLD values obviously do not exist. CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW BEGIN -- statements END$$ DELIMITER ;
MySQL BEFORE INSERT trigger example We will create a BEFORE INSERT trigger to maintain a summary table from another table. First, create a new table called WorkCenters: Second, create another table called WorkCenterStats that stores the summary of the capacity of the work centers: DROP TABLE IF EXISTS WorkCenters; DROP TABLE IF EXISTS WorkCenterStats; CREATE TABLE WorkCenters ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, capacity INT NOT NULL ); CREATE TABLE WorkCenterStats( totalCapacity INT NOT NULL );
The following trigger updates the total capacity in the WorkCenterStats table before a new work center is inserted into the WorkCenter table: DELIMITER $$ CREATE TRIGGER before_workcenters_insert BEFORE INSERT ON WorkCenters FOR EACH ROW BEGIN DECLARE rowcount INT; SELECT COUNT(*) INTO rowcount FROM WorkCenterStats; IF rowcount > 0 THEN UPDATE WorkCenterStats SET totalCapacity = totalCapacity + new.capacity; ELSE INSERT INTO WorkCenterStats(totalCapacity) VALUES(new.capacity); END IF; END $$ DELIMITER ;
Testing the MySQL BEFORE INSERT trigger INSERT INTO WorkCenters(name, capacity) VALUES('Mold Machine',100); INSERT INTO WorkCenters(name, capacity) VALUES('Packing',200); SELECT * FROM WorkCenterStats; SELECT * FROM WorkCenterStats; Note: The trigger has updated the total capacity from 100 to 200 as expected.
MySQL AFTER INSERT Trigger MySQL AFTER INSERT triggers are automatically invoked after an insert event occurs on the table. DELIMITER $$ In an AFTER INSERT trigger, you can access the NEW values but you cannot change them. Also, you cannot access the OLD values because there is no OLD on INSERT triggers. CREATE TRIGGER trigger_name AFTER INSERT ON table_name FOR EACH ROW BEGIN -- statements END$$ DELIMITER ;
MySQL AFTER INSERT trigger example First, create a new table called members: Second, create another table called reminders that stores reminder messages to members. DROP TABLE IF EXISTS members; DROP TABLE IF EXISTS reminders; CREATE TABLE members ( id INT AUTO_INCREMENT, name VARCHAR(100) NOT NULL, email VARCHAR(255), birthDate DATE, PRIMARY KEY (id) ); CREATE TABLE reminders ( id INT AUTO_INCREMENT, memberId INT, message VARCHAR(255) NOT NULL, PRIMARY KEY (id , memberId) );
Creating AFTER INSERT trigger example The following statement creates an AFTER INSERT trigger that inserts a reminder into the reminders table if the birth date of the member is NULL. DELIMITER $$ CREATE TRIGGER after_members_insert AFTER INSERT ON members FOR EACH ROW BEGIN IF NEW.birthDate IS NULL THEN INSERT INTO reminders(memberId, message) VALUES(new.id,CONCAT('Hi ', NEW.name, ', please update your date of birth.')); END IF; END$$ DELIMITER ;
Testing the MySQL AFTER INSERT trigger First, insert two rows into the members table: Third, query data from reminders table: INSERT INTO members(name, email, birthDate) VALUES ('John Doe', 'john.doe@example.com', NULL), ('Jane Doe', 'jane.doe@example.com','2000-01-01'); SELECT * FROM reminders; Note: We inserted two rows into the members table. However, only the first row that has a birth date value NULL, therefore, the trigger inserted only one row into the reminders table. Second, query data from the members table: SELECT * FROM members;
MySQL BEFORE UPDATE Trigger MySQL BEFORE UPDATE triggers are invoked automatically before an update event occurs on the table associated with the triggers. DELIMITER $$ CREATE TRIGGER trigger_name BEFORE UPDATE ON table_name FOR EACH ROW BEGIN -- statements END$$ DELIMITER ;
MySQL BEFORE UPDATE trigger example Second, insert some rows into the sales table: First, create a new table called sales to store sales volumes: INSERT INTO sales(product, quantity, fiscalYear, fiscalMonth) VALUES ('2003 Harley-Davidson Eagle Drag Bike',120, 2020,1), ('1969 Corvair Monza', 150,2020,1), ('1970 Plymouth Hemi Cuda', 200,2020,1); DROP TABLE IF EXISTS sales; CREATE TABLE sales ( id INT AUTO_INCREMENT, product VARCHAR(100) NOT NULL, quantity INT NOT NULL DEFAULT 0, fiscalYear SMALLINT NOT NULL, fiscalMonth TINYINT NOT NULL, CHECK(fiscalMonth >= 1 AND fiscalMonth <= 12), CHECK(fiscalYear BETWEEN 2000 and 2050), CHECK (quantity >=0), UNIQUE(product, fiscalYear, fiscalMonth), PRIMARY KEY(id) ); Third, query data from the sales table to verify the insert: SELECT * FROM sales;
