A 38-Year-Old's MYSQL Primer 06 — DB Objects, Schema, Table Management (create, insert, alter, drop), ALTER DEFAULT, INDEX, EXPLAIN, VIEW

A 38-year-old's MYSQL primer 06 — database objects, schema, table management (create, insert, alter, drop), 

 ALTER DEFAULT settings, INDEX, EXPLAIN, VIEW

Alright, step six. Somehow I'm about to pull the radish — now let's wash it off! 

(Heads-up: I use the same practice tables, but my examples won't match the book's one-to-one. ><) 

0. These little slip-ups can happen. 

If there's one thing that frustrates me as a non-major learning dev as a latecomer, it's that no one ever tells you the supposedly 'obvious' stuff. Before each post I'll jot down whatever I remember. 

1) Nothing comes to mind yet — leaving it blank for now~ ;ㅇ

1. Database objects  - the EXISTS predicate lets you judge the state of another table (whether the subquery returns rows) and use subqueries in UPDATE, SELECT, or DELETE.

1) A database object is something that has concrete existence inside a database. Tables, views, indexes, procedures all qualify. 

2. Schema  - Am I the only one who briefly confused it with 'scalar'? ;< 

1) A database is created inside a container called a schema. So two tables can share a name, but their schemas can't be the same.

That's why sometimes people just call database objects 'schemas.' The whole process of designing and actually building a database is called schema design. Schema design proceeds by DDL (Data Definition Language) command rules.

This kind of confusion feels a lot like calling columns 'columns' or sometimes 'rows' 'records'... and yet the book, unfazed, drops another confusion bomb near the end of the chapter:

Schemas and tables are also namespaces.

+ α )  Here's a blog with a nice summary of basic SQL theory. Not one I know well — just a first-time find via Google. Thankfully, it doesn't block copy-paste. 

 SQL (Structured Query Language) definition and types (DML, DDL, DCL, TCL) 

3. Managing tables with ALTER TABLE - extending a table's max length, adding columns.

1) ALTER is the command for modification. But first, let's start by creating a table. 

(1) The code below declares:

- column name: no, data type: integer, constraint: not null (a value must be provided)

- column name: string, data type: varchar / size 30, constraint: none

- column name: days, data type: date, constraint: none

create 
table 
  sample62(
    no      integer not null,
    string  varchar(30),
    days    date
  );

 select * from sample62;

                (2) Oh, right — SELECT, what was I expecting... let me use DESC instead.

 desc sample62;

2) Next, let's add rows (records) to the created table.

insert into sample62
values( 1, '꺄~', current_date);

insert into sample62
values( 1, 'hello', current_date);

insert into sample62
values( 1, 'world', current_date);

 select * from sample62;

3) Finally, let's alter the table — come in, ALTER 

(1) Adding a column to a table.   

alter table 
  sample62
add 
  addColum 
integer;

 desc sample62;

+ α )   Look who's back~ round and round...

DEFAULT settings revisited (via ALTER) <-- See this blog I found (link)

α 01. Adding a new column with a default value

alter table 
  sample62
add 
  addSetDefaultCol 
varchar(50) DEFAULT '디폴트값';

 desc sample62;

α 02. Adding just a default value to an existing column

a) alter modify column approach

alter table 
  sample62
modify column  
  addSetDefaultCol varchar(50) DEFAULT '디폴트값 수정';

 desc sample62;

b) alter alter set approach

alter table 
  sample62
alter addSetDefaultCol set DEFAULT '디폴트값 한번 더 수정';

 desc sample62;

(2) Changing a column's attributes.

alter table
  sample62
modify
  addColum
varchar(20);

 desc sample62;

(3) Renaming a column.

alter table
  sample62
change
  addColum reNameColum
varchar(20);

 desc sample62;

(4) Dropping a column.

alter table
  sample62
drop
  reNameColum;

 desc sample62;

4. Defining constraints

1) Defining them up front 

(1) Column constraint - when creating a table, you can define constraints on the columns it contains.

create table sample631(
  a integer not null,
  b integer not null unique,
  c varchar(30)
 );

 desc sample631;

(2) Table (primary key) constraint - a primary key (primary key) constraint across multiple columns (describes a constraint on multiple columns with one constraint).

create table sample632(
  no1 integer not null,
  no2 integer not null,
  name varchar(30),
  primary key(no1, no2)
 );

 desc sample632;

(3) Naming a primary key - use the CONSTRAINT keyword in a table constraint to give it a name.

create table sample632(
  no1 integer not null,
  no2 integer not null,
  name varchar(30),
  constraint pk_colkey primary key (no1, no2)
 );

 desc sample632;

*) Huh? There's a weirdness I don't get. 

- When naming the PK named_4_key, named_4key — using those names produces the error below. 

- In MySQL, name_ isn't allowed as a prefix? If anyone knows, please chime in.

2) Adding constraints later

(1) Adding a column constraint 

desc sample631; 

alter table 
  sample631 
modify 
  c varchar(30) not null;

desc sample631; 

(2) Adding a named primary key constraint - using add constraint pk_name primary key(column_name)

desc sample631; 

alter table 
  sample631 
add constraint 
  pk_s631 
primary key(a);

 desc sample631;

3) Removing constraints 

(1) Removing a column constraint - redefine by overwriting: drop what's no longer needed and add what is

desc sample631; 

alter table 
  sample631 
modify c varchar(30);

 desc sample631;

(2) Removing a table (primary key) constraint - use drop primary key 

desc sample631; 

alter table 
  sample631 
drop primary key;

 desc sample631;

create table sample634(
  p integer not null,
  a varchar(30),
constraint 
  pk_a634 
primary key(p)
);   

desc sample634;

(2) Adding rows

insert into sample634 values(1, '첫째줄');

insert into sample634 values(2, '둘째줄');

insert into sample634 values(3, '셋째줄');

select * from sample634;

(3) Confirming the role 

a) Duplicate insert -> error: the 'p' column was set as primary key when the table was created. PKs don't accept duplicates. Since a row with p=2 already exists from (2), the duplicate raises an error. 

insert into sample634 values(2, '2행에 중복해서 추가');

b) Duplicate update -> error: setting p=2 on the row where p=3 collides with the existing row with p=2, triggering the same cause.

update sample634 set p=2 where p=3;

5. INDEX and EXPLAIN 

An index is a named lookup catalogue attached to a table. Its purpose is to speed up searches. Indexes use algorithms such as 'binary tree' or 'hash' to do their job. 

An index is created as a database object that's independent of the table. Data added to a table aren't always arranged sequentially, but the index keeps things sequential. It's a table-dependent object — deleting the table deletes the index too. 

Indexes are DB objects created/dropped with DDL, but there isn't a standard SQL command. Indexes are considered optional and DB-specific. Still, all DB products include them, and you can mostly use them in similar ways. 

1) Creating an INDEX

create index indexNoName on sample62(no);

(1) Checking the index

show index from sample62; 

2) Dropping an INDEX - index + index_name + on + table_name 

drop index indexNoName on sample62;

3) EXPLAIN

A command that lets you check whether an index is actually being used.

(1) To practice a case where the index is used: (1) create a new index, (2) run a conditional search on the indexed column. 

-> 'indexNoName' shows up under possible_keys and key.

create index indexNoName on sample62(string);

explain select * from sample62 where string = 'hello';

(2) Case where no index is used -> both possible_keys and key are NULL. 

 explain select * from sample62 where no = 1;

6. Creating and dropping views

A view is sometimes called a virtual table. It doesn't store results; it stores the SELECT that runs to produce the same result as if you queried a table directly. 

A view is a database object that saves SELECT statements that couldn't otherwise be stored as DB objects — think of it as a user-defined shortcut. Used smartly in subqueries, views can make complex queries intuitive.

1) Create a view - make a view called testView01 that's the same (as) the sample54 table.

select * from sample54;

create view testView01 as select * from sample54;

select * from testView01;

2) Drop a view.

drop view testView01;

3) Create a view specifying columns.

select * from sample54;

create view testView02(n,v,v2) as select no, a, a*2 from sample54;

select * from testView02 where n=1;

3) Drawbacks of views

(1) When doing aggregations, when the table the view references holds a lot of data, or when views are nested, performance tends to degrade.

(2) Subqueries that are in any way correlated with the parent query can't use the view's SELECT. That can be worked around with table-valued functions.