up

2020-01-28

Today we get to jump into SQL

Step 4 - Creating a relational schema in a DBMS

• Data Definition Language (DDL)
  • Deals primarily with Metadata
  • Create DBs, Tables, Changing structure
• Data Manipulation Language (DML)
  • Deals primarily with User Data
  • INSERT, SELECT

Each manufacturer of a DBMS adds a few special features to differentiate their product. This can cause problems.

We will be using MySQL, which was open source and was then eaten by oracle.

There’s a fork called MariaDB, it’s a drop-in, open-source substitute.

metadata commands

Create a database

CREATE DATABASE databasename

Remove a database

DROP DATABASE databasename

Show databases with SHOW

Comments work like this:

-- Single line comments
-- hyphen hyphen space

/*
Multi-line comments are like Java
*/

Use

‘Open’ a database with USE databasename

Creating Tables

CREATE TABLE tablename (
	attributeName datatype,
	attributeName datatype,
	CONSTRAINT constraint_name PRIMARY KEY (attributeName)
);

Datatypes

TODO - insert link here to datatypes

DESCRIBE tablename will show the metadata about a table