MySQL basic syntax, extra keywords to study
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
今は食事の時間です
let's desgin table and first colum(id)
as below instructorsCREATE 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 columsCREATE 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 syntaxSELECT * 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
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
今は食事の時間です
let's desgin table and first colum(id)
as below instructorsCREATE 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 columsCREATE 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 syntaxSELECT * 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
USE opentutorials
search keyword : SQL cheet sheet
after access sever and using Scheama
今は食事の時間です
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 syntaxSELECT * 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
USE opentutorials
SHOW TABLES;
DESC topic;
INSERT INTO topic (title,decrpition,author,prifile) VALUES('MySQL','MyiSQL is ...', NOW(),'egoing','developer').
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 topic SET description = 'ORACLE is ...', title = 'Oracle' WHERE id=2;
SELECT + FROM topics
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
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
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 ;
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
Reference
この問題について(MySQL basic syntax, extra keywords to study), 我々は、より多くの情報をここで見つけました https://velog.io/@marintelli/MySQL-basic-syntax-extra-keywords-to-studyテキストは自由に共有またはコピーできます。ただし、このドキュメントのURLは参考URLとして残しておいてください。
Collection and Share based on the CC Protocol