Practical 1: Getting Started with MYSQL

1. 
   

 Getting Started with MySQL

1. Introduction to MySQL
   • Definition: MySQL is an open-source relational database management system (RDBMS)
   • Uses: Web applications, data warehousing, e-commerce, logging applications
   • Key features: Speed, reliability, scalability, and ease of use
2. Installing MySQL
   • Download MySQL Community Server from official website
   • Follow installation wizard for your operating system
   • Set root password during installation
   • Verify installation:

     mysql --version

3. MySQL Command-line Client
   • Accessing MySQL:

     mysql -u root -p

   • Basic commands:

     
     SHOW DATABASES;
     CREATE DATABASE mydb;
     USE mydb;
     SHOW TABLES;

4. MySQL Workbench
   • Introduction: Visual tool for database design and management
   • Key features:
     • SQL development
     • Data modeling
     • Server administration
   • Example: Creating a new connection
     • New Connection > Enter details (hostname, username, password)
5. PHPMyAdmin
   • Web-based MySQL administration tool
   • Often comes pre-installed with web hosting packages
   • Key features:
     • Manage databases, tables, columns, relations, indexes, users, permissions
     • Execute SQL statements
     • Import/export data in various formats
   • Accessing PHPMyAdmin:
     • Usually via URL like http://localhost/phpmyadmin or provided by your host
6. Creating Your First Database and Table Using command-line:

   
   CREATE DATABASE bookstore;
   USE bookstore;
   CREATE TABLE books (
     id INT AUTO_INCREMENT PRIMARY KEY,
     title VARCHAR(100) NOT NULL,
     author VARCHAR(100) NOT NULL,
     publication_year INT,
     isbn VARCHAR(13) UNIQUE
   );

   Using PHPMyAdmin:
   • Click "New" to create a database
   • Enter database name and click "Create"
   • Click on the new database
   • Click "Create table", enter name and number of columns
   • Fill in column details and click "Save"
7. Inserting and Querying Data Inserting data:

   
   INSERT INTO books (title, author, publication_year, isbn)
   VALUES ('To Kill a Mockingbird', 'Harper Lee', 1960, '9780446310789');

   Querying data:

   
   SELECT * FROM books;
   SELECT title, author FROM books WHERE publication_year > 1950;

Basic Commands everyone should know:

The following commands are particularly useful for navigating the MySQL environment, managing your session, and controlling how results are displayed. They can significantly improve your efficiency when working with MySQL from the command line.

1. ? or \h (help) Displays the help menu, showing available commands and their descriptions.
2. \c (clear) Clears the current input statement, useful if you make a mistake and want to start over.
3. \r (connect) Reconnects to the MySQL server. You can optionally specify a database and host.
4. \d (delimiter) Sets a new statement delimiter. Useful when writing stored procedures or functions.
5. \G (ego) Sends the command to the MySQL server and displays the result vertically, which can be more readable for wide result sets.
6. \q (quit/exit) Exits the MySQL client.
7. \g (go) Sends the current command to the MySQL server for execution.
8. \n (nopager) Disables the pager and prints output directly to stdout.
9. \P (pager) Sets a pager for output. Useful for viewing large result sets.
10. . (source) Executes an SQL script file. You need to provide the file name as an argument.
11. \s (status) Retrieves and displays status information from the server.
12. ! (system) Allows you to execute a system shell command without leaving the MySQL client.
13. \u (use) Switches to another database. You need to provide the database name as an argument.
14. \C (charset) Switches to another character set. This can be necessary when processing binary logs with multi-byte character sets.
15. \W (warnings) Enables the display of warnings after every statement.
16. \w (nowarning) Disables the display of warnings after every statement.

Exercises:

1. Installation and Setup
   • Install MySQL on your computer
   • Create a new user with username 'student' and password 'password123'
   • Grant this user all privileges on a new database called 'school'
2. Database and Table Creation
   • Create a database named 'library'
   • In this database, create a table named 'authors' with the following columns:
     • id (integer, auto-increment, primary key)
     • name (varchar, maximum 100 characters, not null)
     • birth_year (integer)
     • nationality (varchar, maximum 50 characters)
3. Data Manipulation
   • Insert at least 5 authors into the 'authors' table
   • Write a query to select all authors born after 1950
   • Update the nationality of one author
   • Delete an author from the table
4. PHPMyAdmin Practice
   • Log into PHPMyAdmin
   • Create a new database called 'inventory'
   • Create a table named 'products' with columns: id, name, price, and quantity
   • Insert 3 products using the PHPMyAdmin interface
   • Use the SQL tab to write a query that selects all products with a price greater than 10
5. MySQL Workbench
   • Create a new connection in MySQL Workbench
   • Design an Entity-Relationship Diagram (ERD) for a simple blog system with tables for posts, users, and comments
   • Forward engineer this design to create the actual database and tables