MySQL Browsing - LO Man Hei

Download notebook

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "5c02fefb-09ac-4837-8883-e9b27d3c2f17",
   "metadata": {},
   "source": [
    "### MySQL Browsing"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d46049be-7597-4335-942e-b49f8fb0b1b8",
   "metadata": {},
   "source": [
    "##### Pre-requisites\n",
    "1. Install MySQL kernel for Jupyter Notebook `pip install mysql_kernel`\n",
    "\n",
    "2. [Create](/notebook/MySQL%20Users%20and%20Privileges/) a user with privileges to the database `classicmodels`\n",
    "\n",
    "3. [Download](/notebook/raw/mysql_example_database.sql) and [import](/notebook/MySQL%20Import%20Database%20from%20SQL%20File/) example database `classicmodels`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fc0968e3-eb8e-46eb-b2e8-b3caa85d0861",
   "metadata": {},
   "source": [
    "Connect to the example database `classicmodels`  \n",
    "*Change user:password with your own user name and password*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "b5504d7b-6ab7-4908-b7fd-305db36c77a9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": []
     },
     "metadata": {},
     "output_type": "display_data",
     "source": "kernel"
    }
   ],
   "source": [
    "mysql://user:password@localhost/classicmodels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "d3011aa8-8587-4050-a28b-5f743081e0d2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Database</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>classicmodels</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>information_schema</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>performance_schema</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ]
     },
     "metadata": {},
     "output_type": "display_data",
     "source": "kernel"
    }
   ],
   "source": [
    "SHOW DATABASES;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "7ccb7cfd-6fa9-4686-a327-34657e05d77b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Tables_in_classicmodels</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>customers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>employees</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>offices</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>orderdetails</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>orders</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>payments</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>productlines</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>products</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>projects</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ]
     },
     "metadata": {},
     "output_type": "display_data",
     "source": "kernel"
    }
   ],
   "source": [
    "SHOW TABLES;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "26de9f17-83dd-49ed-ba1a-dc47947fa80a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Field</th>\n",
       "      <th>Type</th>\n",
       "      <th>Null</th>\n",
       "      <th>Key</th>\n",
       "      <th>Default</th>\n",
       "      <th>Extra</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>customerNumber</td>\n",
       "      <td>int</td>\n",
       "      <td>NO</td>\n",
       "      <td>PRI</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>customerName</td>\n",
       "      <td>varchar(50)</td>\n",
       "      <td>NO</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>contactLastName</td>\n",
       "      <td>varchar(50)</td>\n",
       "      <td>NO</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>contactFirstName</td>\n",
       "      <td>varchar(50)</td>\n",
       "      <td>NO</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>phone</td>\n",
       "      <td>varchar(50)</td>\n",
       "      <td>NO</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>addressLine1</td>\n",
       "      <td>varchar(50)</td>\n",
       "      <td>NO</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>addressLine2</td>\n",
       "      <td>varchar(50)</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>city</td>\n",
       "      <td>varchar(50)</td>\n",
       "      <td>NO</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>state</td>\n",
       "      <td>varchar(50)</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>postalCode</td>\n",
       "      <td>varchar(15)</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>country</td>\n",
       "      <td>varchar(50)</td>\n",
       "      <td>NO</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>salesRepEmployeeNumber</td>\n",
       "      <td>int</td>\n",
       "      <td>YES</td>\n",
       "      <td>MUL</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>creditLimit</td>\n",
       "      <td>decimal(10,2)</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ]
     },
     "metadata": {},
     "output_type": "display_data",
     "source": "kernel"
    }
   ],
   "source": [
    "SHOW FIELDS FROM customers;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "f05e1580-bf42-422c-8aca-cc51430773f4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Field</th>\n",
       "      <th>Type</th>\n",
       "      <th>Null</th>\n",
       "      <th>Key</th>\n",
       "      <th>Default</th>\n",
       "      <th>Extra</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>customerNumber</td>\n",
       "      <td>int</td>\n",
       "      <td>NO</td>\n",
       "      <td>PRI</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>customerName</td>\n",
       "      <td>varchar(50)</td>\n",
       "      <td>NO</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>contactLastName</td>\n",
       "      <td>varchar(50)</td>\n",
       "      <td>NO</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>contactFirstName</td>\n",
       "      <td>varchar(50)</td>\n",
       "      <td>NO</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>phone</td>\n",
       "      <td>varchar(50)</td>\n",
       "      <td>NO</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>addressLine1</td>\n",
       "      <td>varchar(50)</td>\n",
       "      <td>NO</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>addressLine2</td>\n",
       "      <td>varchar(50)</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>city</td>\n",
       "      <td>varchar(50)</td>\n",
       "      <td>NO</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>state</td>\n",
       "      <td>varchar(50)</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>postalCode</td>\n",
       "      <td>varchar(15)</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>country</td>\n",
       "      <td>varchar(50)</td>\n",
       "      <td>NO</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>salesRepEmployeeNumber</td>\n",
       "      <td>int</td>\n",
       "      <td>YES</td>\n",
       "      <td>MUL</td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>creditLimit</td>\n",
       "      <td>decimal(10,2)</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ]
     },
     "metadata": {},
     "output_type": "display_data",
     "source": "kernel"
    }
   ],
   "source": [
    "DESCRIBE customers;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "77a815fd-28a7-4b83-b018-9dbc348b7e4e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Table</th>\n",
       "      <th>Create Table</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>customers</td>\n",
       "      <td>CREATE TABLE `customers` (\\n  `customerNumber` int NOT NULL,\\n  `customerName` varchar(50) NOT NULL,\\n  `contactLastName` varchar(50) NOT NULL,\\n  `contactFirstName` varchar(50) NOT NULL,\\n  `phone` varchar(50) NOT NULL,\\n  `addressLine1` varchar(50) NOT NULL,\\n  `addressLine2` varchar(50) DEFAULT NULL,\\n  `city` varchar(50) NOT NULL,\\n  `state` varchar(50) DEFAULT NULL,\\n  `postalCode` varchar(15) DEFAULT NULL,\\n  `country` varchar(50) NOT NULL,\\n  `salesRepEmployeeNumber` int DEFAULT NULL,\\n  `creditLimit` decimal(10,2) DEFAULT NULL,\\n  PRIMARY KEY (`customerNumber`),\\n  KEY `salesRepEmployeeNumber` (`salesRepEmployeeNumber`),\\n  CONSTRAINT `customers_ibfk_1` FOREIGN KEY (`salesRepEmployeeNumber`) REFERENCES `employees` (`employeeNumber`)\\n) ENGINE=InnoDB DEFAULT CHARSET=latin1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ]
     },
     "metadata": {},
     "output_type": "display_data",
     "source": "kernel"
    }
   ],
   "source": [
    "SHOW CREATE TABLE customers;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "645acd18-89aa-45a7-a00d-5a75bb86cf45",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Id</th>\n",
       "      <th>User</th>\n",
       "      <th>Host</th>\n",
       "      <th>db</th>\n",
       "      <th>Command</th>\n",
       "      <th>Time</th>\n",
       "      <th>State</th>\n",
       "      <th>Info</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>16</td>\n",
       "      <td>user</td>\n",
       "      <td>localhost:44574</td>\n",
       "      <td>classicmodels</td>\n",
       "      <td>Query</td>\n",
       "      <td>0</td>\n",
       "      <td>init</td>\n",
       "      <td>SHOW PROCESSLIST</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ]
     },
     "metadata": {},
     "output_type": "display_data",
     "source": "kernel"
    }
   ],
   "source": [
    "SHOW PROCESSLIST;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0f90975b-dc68-4891-b09b-306d9576a402",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Mysql",
   "language": "sql",
   "name": "mysql"
  },
  "language_info": {
   "file_extension": ".sql",
   "mimetype": "text/x-sh",
   "name": "sql"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}

Loading