[MySQL] 생활코딩님 수업 연습
cd C:\Bitnami\wampstact-8 .0 .3-1\mysql\bin
mysql -uroot -p
CREATE DATABASE firstquarter;
DROP DATABASE firstquarter;
SHOW DATABASES;
USE firstquarter;
SHOW TABLES;
행, row, record
열, column
CREATE TABLE topic (
id INT(11) NOT NULL AUTO_INCREMENT,
title VARCHAR(100) NOT NILL,
description TEXT NULL,
created DATETIME NOT NULL,
author VARCHAR(30) NULL,
profile VARCHAR(100) NULL,
PRIMARY KEY(id)
);
ERROR 1820 (HYOOO): You must rest your password using ALTER USER statement before execution this statement
-> SET PASSWORD = PASSWORD('password');
CRUD
CREATE
READ
UPDATE
DELETE
DESC topic;
SELECT * FROM topic;
INSERT INTO topic (title, description, created, author, profile) VALUES('MySQL', 'MySQL is ...', NOW(), 'jangsang', 'developer');
INSERT INTO topic (title, description, created, author, profile) VALUES('ORACLE', 'ORACLE is', NOW(), 'sanghyun', 'developer');
INSERT INTO topic (title, description, created, author, profile) VALUES('SQL Server', 'SQL Server is ...', NOW(), 'firstquarter', 'data administrator');
INSERT INTO topic (title, description, created, author, profile) VALUES('PostgreSQL', 'PostgreSQL is ...', NOW(), 'sang', 'data scientist, developer');
INSERT INTO topic (title, description, created, author, profile) VALUES('MongoBD', 'MongoDB is ...', NOW(), 'jangsang', 'developer');
SELECT id, title, created, author FROM topic;
SELECT id, title, created, author FROM topic WHERE author='jangsang';
SELECT id, title, created, author, profile FROM topic WHERE profile='developer' ORDER BY id DESC;
SELECT id, title, created, author, profile FROM topic WHERE profile='developer' ORDER BY id DESC LIMIT 2;
UPDATE topic SET description='Oracle is ...' , title='Oracle' WHERE id=2;
DELETE FROM topic WHERE id = 5;
UPDATE topic SET title=MongoDB, description=MongoDB is ..., created=NOW(), author='jangsang', profile='developer' WHERE id=5; // 안되남?
RENAME TABLE topic TO topic_backup;
CREATE TABLE topic (
id INT(11) NOT NULL AUTO_INCREMENT,
title VARCHAR(30) NOT NULL,
description TEXT NULL,
created DATETIME NOT NULL,
author_id INT(11) NULL,
PRIMARY KEY(id)
);
CREATE TABLE author (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
profile VARCHAR(200) NULL,
PRIMARY KEY(id)
);
INSERT INTO author (id, name, profile) VALUES (1, 'jangsang', 'developer');
INSERT INTO `author` VALUES (2,'sang','database administrator');
INSERT INTO `author` VALUES (3,'hyun','data scientist, developer');
INSERT INTO topic(id, title, description, created, author_id) VALUES(1, 'My SQL', 'MySQL is...', NOW(), 1);
INSERT INTO `topic` VALUES (2,'Oracle','Oracle is ...',NOW(),1);
INSERT INTO `topic` VALUES (3,'SQL Server','SQL Server is ...',NOW(),2);
INSERT INTO `topic` VALUES (4,'PostgreSQL','PostgreSQL is ...',NOW(),3);
INSERT INTO `topic` VALUES (5,'MongoDB','MongoDB is ...',NOW(),1);
- JOIN -
SELECT * FROM topic LEFT JOIN author ON topic.author_id = author.id
SELECT topic.id, title, description, created, name, profile FROM topic LEFT JOIN author ON topic.author_id = author.id;
SELECT topic.id AS topic_id, title, description, created, name, profile, FROM topic LEFT JOIN author ON topic.author_id = author.id;
UPDATE author SET profile='database administrator' WHERE id=2;
'개발 => 복습 후 재정리 대기 > MySQL' 카테고리의 다른 글
[MySQL] 명령어 정리, 모음 (2) | 2021.07.30 |
---|---|
[sequelize] 9시간 차이 나는 이유, 해결법 (0) | 2021.07.27 |
댓글