Structure

database server > database(schema) > tables

1. Enter server

bin ./mysql -uroot -p 
Enter password ? 

-u(user)root(user name)

Each user has differrent access authrization.
But root has all right to asccess, modify, and delete.

-p(password)

2. Enter Schema(Database) Usage

after entering databse server

USE (database name)
: accessing schema

 USE opentutorials

3. CREATE TABLE

search keyword : SQL cheet sheet

after access sever and using Scheama

now it's time to crㄷate table

let's desgin table and first colum(id)
as below instructors

CREATE TABLE TOPIC(
-> id INT(11) NOT NULL AUTO_INCREMENT,

One of DBMS's stregnths is fact that you can designate specific type of data User should put.

id means Colums name
int(11) means only integer can be input
NOT NULL means not allowed input NULL
AUTO-INCREMENT means whenever new input ++

In this way, you can design rest of colums

CREATE TABLE TOPIC(
-> id INT(11) NOT NULL AUTO_INCREMENT,
-> titile VARCHAR(100) NOU NULL,
-> description TEXT NULL,
-> created DATETIME NOT NULL,
-> author VARCHAR(20) NULL,
-> profile VARCHAR(100) NULL,
-> PRIMARY KEY(id));
 

4. C R U D

Create

USE opentutorials
SHOW TABLES;

DESC topic;

INSERT INTO topic (title,decrpition,author,prifile) VALUES('MySQL','MyiSQL is ...', NOW(),'egoing','developer').

Read :: SELECT
keyworkd mysql select syntax

SELECT * FROM topic

SELECT id,title,created,author FROM topic;

SELECT " id,title,created,author FROM topic WHERE author = 'egoing';

SELECT " id,title,created,author FROM topic WHERE author = 'egoing' ORDER BY id DESC;

SELECT " id,title,created,author FROM topic WHERE author = 'egoing' ORDER BY id DESC LIMIT 2;

UPDATE

UPDATE topic SET description = 'ORACLE is ...', title = 'Oracle' WHERE id=2;

SELECT + FROM topics

• Don forget WHERE! it can occur Disaster ^^;;

DELETE

SELECT * FROM topics;

DELETE FROM topic WHERE id = 5 ;
SELECT * FROM topic

5. Relational Database

what is essence of 'Database'?

besides mysql,

Common ground of datebases is C R U D.

From now on we gonna one step more regarding

'Relational'

Relational means more than word.

IN this table we gonna create one more table which is calledauthor

and one more new table created called topic

Finally we can eliminate orgical topic

What's diffrences compared to former one?

pros

when we update value in author table
automatically changed all data

6. Table seperated

Source code for seperating TABLE

7. join

SELECT * FROM topic LEFT JOIN author ON topic.author.id = author.id ; 

SELECT topic.id,title,description,name,profile FROM topic LEFT JOIN author ON topic.author.id = author.id ;

SELECT topic.id AS topic.id,title,description,name,profile FROM topic LEFT JOIN author ON topic.author.id = author.id ;

8. Interanet & Database

MySQL moinor is also one of databease clients,

and the other popular MySQL client is MySQL Workbench which is based on GUI(graphic user interface)

beside those two clients, Loos of client exists. ^^

9. Extra keywords for study

• index(how to arrange data more effectively)
• modeling(how to construct table structure)
• backup(mysqldumo, binay log)
• cloud(AWS RDS, Google Cloud SQL for MySQL)
• programming(python mysql api, java mysql api)