Intro to MySQL.pdf

MySQL Introduction Prepared by: SHIRLEY D. MORAGA Introduction of MySQL  MySQL is an SQL (Structured Query Language) based relational database management system (DBMS)  MySQL is compatible with standard SQL  MySQL is frequently used by PHP and Perl  Commercial version o...

MySQL Introduction Prepared by: SHIRLEY D. MORAGA Introduction of MySQL  MySQL is an SQL (Structured Query Language) based relational database management system (DBMS)  MySQL is compatible with standard SQL  MySQL is frequently used by PHP and Perl  Commercial version of MySQL is also provided (including technical support) Introduction of MySQL  MySQL is a widely used relational database management system (RDBMS).  MySQL is free and open-source.  MySQL is ideal for both small and large applications. What is MySQL?  MySQL is a very popular open-source relational database management system (RDBMS).  MySQL is a relational database management system  MySQL is open-source  MySQL is free  MySQL is ideal for both small and large applications  MySQL is very fast, reliable, scalable, and easy to use  MySQL is cross-platform  MySQL is compliant with the ANSI SQL standard  MySQL was first released in 1995  MySQL is developed, distributed, and supported by Oracle Corporation Resource  MySQL and GUI Client can be downloaded from  http://dev.mysql.com/downloads/  https://dev.mysql.com/downloads/mysql/ Who Uses MySQL? Major Tech Companies Social Media: Facebook, Twitter, LinkedIn Streaming: Netflix E-commerce: Shopify, Uber, Airbnb Cloud Platforms: Many cloud-based applications rely on MySQL Other Industries: Web Development, Gaming, Finance, Healthcare, Education MySQL RDBMS  RDBMS stands for Relational Database Management System.  RDBMS is a program used to maintain a relational database.  RDBMS is the basis for all modern database systems such as MySQL, Microsoft SQL Server, Oracle, and Microsoft Access.  RDBMS uses SQL queries to access the data in the database. What is a Database Table?  A table is a collection of related data entries, and it consists of columns and rows.  A column holds specific information about every record in the table.  A record (or row) is each individual entry that exists in a table. What is a Database Table? Every table is broken up into smaller entities called fields. A field is a column in a table that is designed to maintain specific information about every record in the table. A record is also called as a row of data is each individual entry that exists in a table. A column is a vertical entity in a table that contains all information associated with a specific field in a table. MySQL Data Types Numeric Data Types  Used for storing numerical values.  Integer types: INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT (for whole numbers)  Decimal types: DECIMAL, NUMERIC (for precise decimal values)  Floating-point types: FLOAT, DOUBLE (for approximate decimal values)  BIT: Stores single-bit values (0 or 1) MySQL Data Types Date and Time Data Types  Used for storing date and time values.  DATE: Stores dates (YYYY-MM-DD)  TIME: Stores time (HH:MM:SS)  DATETIME: Stores date and time (YYYY- MM-DD HH:MM:SS)  TIMESTAMP: Stores date and time with timestamp functionality  YEAR: Stores year (2-digit or 4-digit) MySQL Data Types String Data Types  Used for storing text data.  CHAR: Fixed-length character string  VARCHAR: Variable-length character string  TEXT: Large text data  BLOB: For storing large binary data MySQL Data Types Other Data Types  ENUM: Defines a list of possible values for a column  SET: Stores a set of strings  JSON: Stores JSON data CREATE DATABASE  An SQL relation is defined using the CREATE DATABASE command: ◼ create database [database name]  Example ◼ create database mydatabase Query  To find all loan number for loans made at the Perryridge branch with loan amounts greater than $1100. select loan_number from loan where branch_name = ‘Perryridge’ and amount>1100;  Find the loan number of those loans with loan amounts between $1,000 and $1,500 (that is, $1,000 and $1,500) select loan_number from loan where amount between 1000 and 1500; Query Find the names of all branches that have greater assets than some branch located in Brooklyn. select distinct T.branch_name from branch as T, branch as S where T.assets > S.assets and S.branch_city = ‘Brooklyn’; Find the customer names and their loan numbers for all customers having a loan at some branch. select customer_name, T.loan_number, S.amount from borrower as T, loan as S where T.loan_number = S.loan_number; Set Operation  Find all customers who have a loan, an account, or both: (select customer_name from depositor) union (select customer_name from borrower);  Find all customers who have an account but no loan. (no minus operator provided in mysql) select customer_name from depositor where customer_name not in (select customer_name from borrower); Aggregate function  Find the number of depositors for each branch. select branch_name, count (distinct customer_name) from depositor, account where depositor.account_number = account.account_number group by branch_name;  Find the names of all branches where the average account balance is more than $500. select branch_name, avg (balance) from account group by branch_name having avg(balance) > 500; Nested Subqueries  Find all customers who have both an account and a loan at the bank. select distinct customer_name from borrower where customer_name in (select customer_name from depositor);  Find all customers who have a loan at the bank but do not have an account at the bank select distinct customer_name from borrower where customer_name not in (select customer_name from depositor); Nested Subquery  Find the names of all branches that have greater assets than all branches located in Horseneck. select branch_name from branch where assets > all (select assets from branch where branch_city = ‘Horseneck’); Modification of Database  Increase all accounts with balances over $800 by 7%, all other accounts receive 8%. update account set balance = balance  1.07 where balance > 800; update account set balance = balance  1.08 where balance  800; Modification of Database  Delete the record of all accounts with balances below the average at the bank. delete from account where balance < (select avg (balance) from account);  Add a new tuple to account insert into account values (‘A-9732’, ‘Perryridge’,1200);