# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# # MySQL - Basics
#
# - [SQL Tutorial](https://www.w3schools.com/sql/default.asp)
# - [MySQL Documentation](https://dev.mysql.com/doc/refman/5.7/en/create-table.html)
# - [MySQL - Tutorial](https://www.tutorialspoint.com/mysql/index.htm)
# - [Quickstart for Cloud SQL for MySQL](https://cloud.google.com/sql/docs/mysql/quickstart)

# ## Load ipython-sql
#
# `ipython-sql`:
#
#     - 是jupyter notebook的extension，用來擴充jupyter對SQL的支援
#     - 其底層是使用SQLAlchemy

# %load_ext sql
# for engines that do not support autocomit
# %config SqlMagic.autocommit=False

# ## Connect Database
#
# Because `ipython-sql` is based on `SQLAlchemy`, 
# we use the SQLAlchemy's DBAPI to connect the MySQL database via the mysqlclient 
# (maintained fork of MySQL-Python) driver.
#
# [SQLAlchemy - MySQL DBAPI](https://docs.sqlalchemy.org/en/13/dialects/mysql.html#module-sqlalchemy.dialects.mysql.pymysql)
#
#
# ```bash
# mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>
# ```

# %sql mysql+mysqldb://root:abc123456@192.168.3.11/kaka_test

# + language="sql"
#
# SELECT * FROM entries;
# -

# ## MySQL Version

# %sql SHOW VARIABLES LIKE '%version%';

# ## Create Table
#
# - [MySQL - Data Type](https://www.tutorialspoint.com/mysql/mysql-data-types.htm)

# + language="sql"
# CREATE TABLE persons(
#     PRIMARY KEY (person_id),
#     person_id INT          NOT NULL AUTO_INCREMENT,
#     firstname VARCHAR(255) NOT NULL,
#     lastname  VARCHAR(255),
#     age       INT,
#     height    FLOAT,
#     weight    FLOAT,
#     city      VARCHAR(255)
# );
# -

# ## CRUD for Data
#
# - C: Create
# - R: Read
# - U: Update
# - D: Delete
#
# ![](https://raw.githubusercontent.com/kaka-lin/Notes/master/DB/images/crud.png)

# ### Create Data: SQL INSERT INTO

# + language="sql"
# INSERT INTO persons
# VALUES (10, 'kaka','Lin', 28, 175, 70, 'Taipei');
#
# INSERT INTO persons (firstname, lastname, age, height, weight, city) 
# VALUES ('kiwi','Li', 30, 173, 70, 'Taipei');
# -

# ### Read Data: SQL SELECT

# + language="sql"
#
# SELECT * FROM persons; 
# -

# ### Update Data: SQL UPDATE

# + language="sql"
#
# UPDATE persons
# SET    weight = 68
# WHERE  firstname = 'kaka';

# + language="sql"
#
# SELECT * FROM persons;
# -

# ### Delete Data: SQL DELETE
#
# Before we delete data,
# we first add the data that we want to delete.

# + language="sql"
#
# INSERT INTO persons
# VALUES (3, 'albert','Lin', 28, 180, 70, 'Taipei');

# + language="sql"
#
# SELECT * FROM persons;

# + language="sql"
#
# DELETE FROM persons
# WHERE person_id = 3;

# + language="sql"
#
# SELECT * FROM persons;
# -

# ## SQL WHERE

# + language="sql"
#     
# INSERT INTO persons (firstname, lastname, age, height, weight, city) 
# VALUES ('Albert', 'Lin', 28, 160, 70, 'Taipei'),
#        ('Andy', 'Wei', 24, 175, 72, 'Teipei'),
#        ('kevin', 'Wang', 30, 174, 63, 'San Francisco'),
#        ('kevin', 'Wei', 27, 178, 65, 'Taipei'),
#        ('David', 'Kang', 26, 175, 65, 'Washington'),
#        ('Matt', 'Wang', 26, 172, 72, 'Taipei'),
#        ('kaka-ideal', 'Lin', 28, 178, 70, 'Janpan');

# + language="sql"
#
# SELECT * FROM persons

# + language="sql"
#
# SELECT *
# FROM   persons
# WHERE  age = 28;
# -

# ## SQL AND, OR and NOT

# ### AND

# + language="sql"
#
# SELECT *
# FROM   persons
# WHERE  age = 28
# AND    height > 170;
# -

# ### OR

# + language="sql"
#
# SELECT *
# FROM   persons
# WHERE  age = 28
# OR     height > 170;
# -

# #### SQL IN Operator
#
# The IN operator allows you to specify multiple values in a WHERE clause.

# + language="sql"
#
# SELECT *
# FROM   persons
# WHERE  age = 28 OR age = 26;

# + language="sql"
#
# SELECT *
# FROM   persons
# WHERE  age IN (26, 28);
# -

# ### Not

# + language="sql"
#
# SELECT *
# FROM   persons
# WHERE  age != 28;
# -

# ## SQL ORDER BY
#
# ```
# SELECT column1, column2, ...
#     FROM table_name
#     ORDER BY column1, column2, ... ASC|DESC;
# ```
#
# - Default: ASC

# + language="sql"
#
# SELECT *
# FROM   persons
# ORDER BY age;
# -

# ## SQL LIKE Operator
#
# The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
#
# There are two wildcards often used in conjunction with the LIKE operator:
#
# - % : The percent sign represents zero, one, or multiple characters
# - _ : The underscore represents a single character

# + language="sql"
#
# SELECT *
# FROM   persons
# WHERE  city LIKE '%pei%';
# -

# ## MySQL -  Functions
#
# - [MySQL Function](https://www.w3schools.com/sql/sql_ref_mysql.asp)
#
# ### Modify Table: SQL ALTER TABLE
#
# - Add a new column in an existing table
#     
#     ```
#     ALTER TABLE table_name ADD column_name datatype; 
#     ```

# + language="sql"
#
# ALTER TABLE persons
# ADD height_meters REAL;
#
# UPDATE persons
# SET    height_meters = round(height / 100, 2);
#
# SELECT * FROM persons;
# -

# ## Drop Table

# + language="sql"
#
# DROP TABLE persons;

StarcoderdataJupyterScriptsDedupFiltered