#멋쟁이사자처럼 #부트캠프 #백엔드 #JAVA

URLs to Check

Example: Table a and b

USE examplesdb;

CREATE TABLE a(
    name INT
);

CREATE TABLE b(
    name INT
);

SHOW TABLES;

INSERT INTO a VALUES(1);
INSERT INTO a VALUES(3);
INSERT INTO b VALUES(2);

SELECT * FROM a;
SELECT * FROM b;

DROP TABLE b;

TRUNCATE VS DROP

The DROP command is used to remove the whole database or table indexes, data, and more. Whereas the TRUNCATE command is used to remove all the rows from the table.

Primary Key Cannot Be Duplicated

CREATE TABLE example(
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

SELECT * FROM example;
INSERT INTO example VALUES(1, 'test');

-- SQL Error [1062] [23000]: Duplicate entry '1' for key 'exam.PRIMARY'
INSERT INTO example VALUES(1, 'test');

Column-Specified INSERT Statement

CREATE TABLE example(
    id INT primary key auto_increment,
    name VARCHAR(100)
);

-- SQL Error [1136] [21S01]: Column count doesn't match value count at row 1
INSERT INTO example VALUES('carami');

INSERT INTO example (name) VALUES('carami');

NOT NULL and DEFAULT

CREATE TABLE book (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(150) NOT NULL,
    author VARCHAR(100) NOT NULL,
    published_date DATE,
    price INT DEFAULT 5000
);

Changing Data Type

CREATE TABLE book (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(150) NOT NULL,
    author VARCHAR(100) NOT NULL,
    published_date DATE,
    price INT DEFAULT 5000
);

ALTER TABLE book
MODIFY title VARCHAR(300) NOT NULL;

DESC book;