MySQL

What is SQL?
• SQL is language for retrieving and manipulating data in a relational database 
 > Data definition
 > Data manipulation
 > Data control
• Open standard - ANSI
 > Vendor implementations add vendor-specific features, however

SQL Terminology


• Table
> A set of rows
> Analogous to a “file”
• Row
> Analogous to a record of a “file”
• Column
> A column is analogous to a field of a record
> Each column in a given row has a single value
• Primary Key
> One of more columns whose contents are unique within a table and thus can be used to identify a row of that table

Types of SQL Statements

• DDL (Data Definition Language)
> Used to build and modify the structure of your
tables and other objects in the database
> Examples: CREATE TABLE, ALTER TABLE, DROP
TABLE, CREATE VIEW, ...


• DML (Data Manipulation Language)
> Used to work with the data in tables
> INSERT INTO, UPDATE, DELETE


• DCL (Data Control Language)
> Used to control access rights
> GRANT, REVOKE

SQL Basics:
Databases


Creating a database

mysql> CREATE DATABASE mydb;

Query OK, 1 row affected (0.01 sec)

Setting a default database
mysql> USE mydb;
Database changed



Dropping Databases
mysql> DROP DATABASE temp_db;
Query OK, 0 rows affected (0.01 sec)
mysql> DROP DATABASE IF EXISTS temp_db;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database          |
+--------------------+
| information_schema |
| mydb                    |
| mysql                 |
| test                   |
+--------------------+
4 rows in set (0.00 sec)



SQL Basics:
Tables




Creating a Table
mysql> CREATE TABLE person (
-> person_id SMALLINT UNSIGNED NOT NULL,
-> first_name VARCHAR(45) NOT NULL,
-> last_name VARCHAR(45) NOT NULL,
-> PRIMARY KEY (person_id)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.14 sec)
mysql> SHOW TABLES;
+----------------+
| Tables_in_mydb |
+----------------+
| person           |
+----------------+
1 row in set (0.00 sec)



Altering table name (Two options)


mysql> ALTER TABLE person rename to person1;
Query OK, 0 rows affected (0.06 sec)
mysql> SHOW TABLES;
+----------------------+
| Tables_in_mydb |
+----------------------+
| person1 |
+----------------------+
1 row in set (0.00 sec)
mysql> RENAME TABLE person1 TO whatever;
Query OK, 0 rows affected (0.05 sec)
mysql> SHOW TABLES;
+----------------------+
| Tables_in_mydb |
+----------------------+
| whatever |
+----------------------+




Altering field name and type


mysql> ALTER TABLE person CHANGE last_name surname varchar(30);
Query OK, 0 rows affected (0.62 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESCRIBE person;
+------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------------------+------+-----+---------+----------------+
| person_id | smallint(5) unsigned | NO | PRI | NULL |
| first_name | varchar(45) | NO | | NULL | |
| surname | varchar(30) | YES | | NULL | |
+------------+----------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

Adding or removing fields

mysql> ALTER TABLE person ADD age smallint(3) unsigned not null;

Query OK, 0 rows affected (0.42 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> DESCRIBE person;

+------------+----------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+------------+----------------------+------+-----+---------+----------------+

| person_id | smallint(5) unsigned | NO | PRI | NULL |

| first_name | varchar(45) | NO | | NULL | |

| surname | varchar(30) | YES | | NULL | |

| age | smallint(3) unsigned | NO | | NULL | |

+------------+----------------------+------+-----+---------+----------------+

4 rows in set (0.01 sec)

mysql> ALTER TABLE person DROP first_name;

Query OK, 0 rows affected (0.25 sec)

Records: 0 Duplicates: 0 Warnings: 0

Dropping Tables

mysql> SHOW TABLES;

+-------------------+

| Tables_in_temp_db |

+-------------------+

| temp_table |

+-------------------+

1 row in set (0.00 sec)

Dropping Tables

mysql> DROP TABLE temp_table;

Query OK, 0 rows affected (0.06 sec)

mysql> DROP TABLE IF EXISTS temp_table;

Query OK, 0 rows affected, 1 warning (0.12 sec)

mysql> SHOW TABLES;

Empty set (0.00 sec)

Working with tables from Multiple Databases

mysql> SELECT * FROM temp_db.temp_table;

+---------+---------------------+

| temp_id | temp_whatever |

+---------+---------------------+

| 1 | life is good |

| 2 | life is even better |

+---------+---------------------+

2 rows in set (0.00 sec)

mysql> SELECT * FROM mydb.student;

+------------+------------+-----------+-----+-------+

| student_id | first_name | last_name | age | grade |

+------------+------------+-----------+-----+-------+

| 1 | yuna | kim | 19 | 4 |

| 2 | kelly | jones | 22 | 5 |

+------------+------------+-----------+-----+-------+

2 rows in set (0.00 sec)

Field Definitions

• Each field has

> Field name
> Data type
> Field modifier or constraint


Field Data types - Integers
• TINYINT
 > 1 byte, -128 to 127 (signed), 0 to 255 (unsigned)
• SMALLINT
 > 2 bytes, -32768 to 32767 (signed), 0 to 65535
(unsigned)
• MEDIUMINT
 > 3 bytes
• INT
  > 4 bytes
• BIGINT
 > 8 bytes


Field Data types
• FLOAT
> single precision floating-point value
• DOUBLE
> double precision floating-point value
• DECIMAL
> decimal values
• BIT
> bit value
> b'0101'


• CHAR
> Fixed length strings up to 255 characters
• VARCHAR
> Variable length strings up to 255 characters
• DATE, TIME, YEAR
• DATETIME, TIMESTAMP
• ENUM, SET
> Predefined set of values


Field Modifiers
• NULL or NOT NULL
> Indicates if the field can be null or not
• DEFAULT
> Assigns default value if no value is specified
when a new record is inserted
• AUTO_INCREMENT
> MySQL automatically generates a number (by
incrementing the previous value by 1)
> Used for creating primary key
• CHARACTER SET
> Specifies the character set for string values