MySQL - Basics

• SQL Tutorial
• MySQL Documentation
• MySQL - Tutorial
• Quickstart for Cloud SQL for MySQL

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

mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>

%sql mysql+mysqldb://root:<passwd>@35.201.196.222/kaka_test

%%sql

SELECT * FROM entries;

 * mysql+mysqldb://root:***@35.201.196.222/kaka_test
2 rows affected.

MySQL Version

%sql SHOW VARIABLES LIKE '%version%';

 * mysql+mysqldb://root:***@35.201.196.222/kaka_test
8 rows affected.

Create Table

• MySQL - Data Type

%%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)
);

 * mysql+mysqldb://root:***@35.201.196.222/kaka_test
0 rows affected.
[]

CRUD for Data

• C: Create
• R: Read
• U: Update
• D: Delete

Create Data: SQL INSERT INTO

%%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');

 * mysql+mysqldb://root:***@35.201.196.222/kaka_test
1 rows affected.
1 rows affected.
[]

Read Data: SQL SELECT

%%sql

SELECT * FROM persons;

 * mysql+mysqldb://root:***@35.201.196.222/kaka_test
2 rows affected.

Update Data: SQL UPDATE

%%sql

UPDATE persons
SET    weight = 68
WHERE  firstname = 'kaka';

 * mysql+mysqldb://root:***@35.201.196.222/kaka_test
1 rows affected.
[]

%%sql

SELECT * FROM persons;

 * mysql+mysqldb://root:***@35.201.196.222/kaka_test
2 rows affected.

Delete Data: SQL DELETE

Before we delete data, we first add the data that we want to delete.

%%sql

INSERT INTO persons
VALUES (3, 'albert','Lin', 28, 180, 70, 'Taipei');

 * mysql+mysqldb://root:***@35.201.196.222/kaka_test
1 rows affected.
[]

%%sql

SELECT * FROM persons;

 * mysql+mysqldb://root:***@35.201.196.222/kaka_test
3 rows affected.

%%sql

DELETE FROM persons
WHERE person_id = 3;

 * mysql+mysqldb://root:***@35.201.196.222/kaka_test
1 rows affected.
[]

%%sql

SELECT * FROM persons;

 * mysql+mysqldb://root:***@35.201.196.222/kaka_test
2 rows affected.

SQL WHERE

%%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');

 * mysql+mysqldb://root:***@35.201.196.222/kaka_test
7 rows affected.
[]

%%sql

SELECT * FROM persons

 * mysql+mysqldb://root:***@35.201.196.222/kaka_test
9 rows affected.

%%sql

SELECT *
FROM   persons
WHERE  age = 28;

 * mysql+mysqldb://root:***@35.201.196.222/kaka_test
3 rows affected.

SQL AND, OR and NOT

AND

%%sql

SELECT *
FROM   persons
WHERE  age = 28
AND    height > 170;

 * mysql+mysqldb://root:***@35.201.196.222/kaka_test
2 rows affected.

OR

%%sql

SELECT *
FROM   persons
WHERE  age = 28
OR     height > 170;

 * mysql+mysqldb://root:***@35.201.196.222/kaka_test
9 rows affected.

SQL IN Operator

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

%%sql

SELECT *
FROM   persons
WHERE  age = 28 OR age = 26;

 * mysql+mysqldb://root:***@35.201.196.222/kaka_test
5 rows affected.

%%sql

SELECT *
FROM   persons
WHERE  age IN (26, 28);

 * mysql+mysqldb://root:***@35.201.196.222/kaka_test
5 rows affected.

Not

%%sql

SELECT *
FROM   persons
WHERE  age != 28;

 * mysql+mysqldb://root:***@35.201.196.222/kaka_test
6 rows affected.

SQL ORDER BY

SELECT column1, column2, ...
    FROM table_name
    ORDER BY column1, column2, ... ASC|DESC;

• Default: ASC

%%sql

SELECT *
FROM   persons
ORDER BY age;

 * mysql+mysqldb://root:***@35.201.196.222/kaka_test
9 rows affected.

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

%%sql

SELECT *
FROM   persons
WHERE  city LIKE '%pei%';

 * mysql+mysqldb://root:***@35.201.196.222/kaka_test
6 rows affected.

MySQL - Functions

• MySQL Function

Modify Table: SQL ALTER TABLE

• Add a new column in an existing table

  ALTER TABLE table_name ADD column_name datatype;
  

%%sql

ALTER TABLE persons
ADD height_meters REAL;

UPDATE persons
SET    height_meters = round(height / 100, 2);

SELECT * FROM persons;

     * mysql+mysqldb://root:***@35.201.196.222/kaka_test
    0 rows affected.
    9 rows affected.
    9 rows affected.

Drop Table

%%sql

DROP TABLE persons;

 * mysql+mysqldb://root:***@35.201.196.222/kaka_test
0 rows affected.
[]