SQL 구문 정리 (mySQL 기준)

set password for 'root'@'localhost' = password('password');

create database my_girl default character set utf8mb4 collate utf8mb4_general_ci;
1. 접속해서 schema1의 테이블, 데이터 dump 하기
$ mysqldump --single-transaction -uroot -ppassword han > han_dump.sql
: 스키마 새로 생성한 뒤
5. 생성한 dump.sql 파일 newschema에 생성하기 
mysql -uroot -p1ehdgns han < mydb_dump.sql

select * from ...where... group by...having... order by... limit...
 
show tables;  //테이블 모두보기
show databases; //데이터베이스, 스키마 모두 보기
rename table old_table to new; //리네임
drop table author; // 테이블 삭제
create table 생성할테이블 (select * from 테이블명);
delete from topic where id = 5
CREATE TABLE 새테이블 AS SELECT * FROM 기존테이블; -- 테이블 복사

#인서트
insert into topic (title, description, created, author, profile) values('MongoDB', 'MongoDB is ...', NOW(), 'egoing', 'developer');
# 인서트 다중
insert into tb_1 (name, address) 
values ('Choi', 'Incheon'), ('Kwon', 'Jeju'), ('Ryu', 'Daegu');
# 인서트 시 컬럼명 생략
insert into person values(2, 'charles', 30, 'M'); # 반드시 순서대로

update topic set description = 'Oracle is ...', title='Oracle' where id = 2;
UPDATE 테이블이름 SET 필드이름1=데이터값1, 필드이름2=데이터값2, ... WHERE 필드이름=데이터값

create table author (select distinct author as name, profile from topic_backup);
컬럼 삽입, 프라이머리키, 유일번호 제일 앞에 ID

alter table author add column id int(11);
alter table author drop column 삭제할컬럼;

변수 설정: set @변수명 = 변수값;
select @변수명;
SET @count = 3;
PREPARE mySQL FROM 'SELECT mem_name, height FROM member ORDER BY height LIMIT ?'; -- 변수 사용
EXECUTE mySQL USING @count;

# auto_increment 관련
create table honong3 (
	toy_id int auto_increment primary key,
	toy_name char(4),
	age int);
alter table hongong3 auto_increment=1000; -- 1000번부터 번호 부여
set @@auto_increment_increment = 3;  -- 숫자를 3씩 건너뛰어 부여

#######alter table author add primary key(컬럼명 가령id); //프라이머리 키 지정
ALTER TABLE 테이블명 ADD COLUMN 컬럼명 INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
특정 컬럼 뒤에 컬럼을 넣으려면 AFTER 기존컬럼명; 입니다
alter table 테이블_이름 change 현재_컬럼_이름 새로운_컬럼_이름 타입;
+-----+--------+---------------------------+
| aid | name   | profile                   |
+-----+--------+---------------------------+
|   1 | egoing | developer                 |
|   2 | duru   | data administrator        |
|   3 | taeho  | data scientist, developer |
+-----+--------+---------------------------+

**** topic_backup과 author 테이블을 조인해서 topic 테이블을 새로 생성.
create table topic (select T.id, T.title, T.description, T.created, A.aid from topic_backup as T join author as A on T.author = A.name);
+----+------------+-------------------+---------------------+-----+
| id | title      | description       | created             | aid |
+----+------------+-------------------+---------------------+-----+
|  1 | Mysql      | Mysqlis ...       | 2024-10-16 11:14:39 |   1 |
|  2 | Oracle     | Oracle is ...     | 2024-10-16 11:16:24 |   1 |
|  3 | SQL Server | SQL Server is ... | 2024-10-16 11:17:14 |   2 |
|  4 | PostgreSQL | PostgreSQL is...  | 2024-10-16 11:19:04 |   3 |
|  5 | MongoDB    | MongoDB is ...    | 2024-10-16 11:56:05 |   1 |
+----+------------+-------------------+---------------------+-----+

create table topic(
	id INT(11) NOT NULL Auto_INCREMENT,
	title VARCHAR(100) NOT NULL,
	description TEXT NULL,
	created DATETIME NOT NULL,
	author VARCHAR(30) NULL,
	profile VARCHAR(100) NULL,
	PRIMARY KEY(id));
+----+------------+-------------------+---------------------+--------+---------------------------+
| id | title      | description       | created             | author | profile                   |
+----+------------+-------------------+---------------------+--------+---------------------------+
|  1 | Mysql      | Mysqlis ...       | 2024-10-16 11:14:39 | egoing | developer                 |
|  2 | ORALCE     | ORACLE is         | 2024-10-16 11:16:24 | egoing | developer                 |
|  3 | SQL Server | SQL Server is ... | 2024-10-16 11:17:14 | duru   | data administrator        |
|  4 | PostgreSQL | PostgreSQL is...  | 2024-10-16 11:19:04 | taeho  | data scientist, developer |
|  5 | MongoDB    | MongoDB is ...    | 2024-10-16 11:19:42 | egoing | developer                 |
+----+------------+-------------------+---------------------+--------+---------------------------+
create table topic (select id, title, description, created, A.id from topic_backup T join author A on T.author = A.name)

* 분리된 테이블을 조합해서 원래의 테이블 표현
select id, title, description, created, name, profile from topic T join author A on T.aid = A.aid;

create table comment(
	id int(11) not null auto_increment,
	description varchar(100) not null,
	author_id	int(11) not null,
	Primary key(id));
# 뷰 생성
create view total_cost as
	select mem_id, sum(price*amount) from buy
	group by mem_id;
# 뷰 삭제 
DROP VIEW myview;
# 뷰 수정
ALTER VIEW MyView AS
SELECT ID, Name
FROM Reservation;