up

2020-01-30

homework 1 review

Single quotes are required for string literals and dates, as double quotes do not work in some DBMS systems.

What we have so far:

• DDL - data definition language
• DML - data manipulation language

Commands:

• USE <name>
• SHOW <name>
• DESCRIBE <name>
• CREATE [DATABASE|TABLE] <name>
• DROP [DATABASE|TABLE] <name>

CREATE DATABASE conman;
SHOW DATABASES; -- ensure all goes well
USE conman; -- use the db we just created
SHOW TABLES; -- throws an error if not using a db

Datatypes:

• CHAR (size)
  • fixed-length string
• VARCHAR (size)
  • variable length string
• INT or INTEGER
• DATE

Rules that limit the values of an attribute, aka Constraints:

• in creating a table: CONSTRAINT constraint_name CONSTRAINT TYPE (arguments)
• PRIMARY KEY
  • every value must be unique
  • not null

Creating a table:

CREATE TABLE tablename(
	attr1 datatype,
	attr2 datatype,
	CONSTRAINT tablename_pk PRIMARY KEY (primary key attributes)
);

Look at the table’s metadata with SHOW or DESCRIBE.

Looks like DESCRIBE is just a shortcut for SHOW COLUMNS that only exists for oracle compatability.

Scripts

myScript.sql contains a semi-colon (;) delimited list of SQL commands.

load in a script with SOURCE <filepath>, or the shortcut \.

in-class example

• conman database
• contact_info table

Now - inserting data into the db

INSERT INTO tablename (attr1, attr2) VALUES (attr1val, attr2val);

If a value is being added for every attribute, then the attr names do not need to be specified. However, the order must still be maintained.

Getting info out

SELECT *| attr1, attr2 FROM tablename;

use an alias for an attribute:

SELECT attr1 AS 'alias' FROM tablename;

Conditional Selection - WHERE

SELECT * FROM tablename WHERE attr1='clean warm socks';

finding null things: WHERE attr1 IS NULL;