2024.03.27 - 12일차 sql 기본 문법

카테고리 없음

2024.03.27 - 12일차 sql 기본 문법

동동순이 2024. 3. 27. 16:38

use kdt;

데이터 베이스를 지정해준다.

elect concat('안녕하세요!','mysql') as concat;

다음과 같이 나온다.

select concat(address1, ' ', address2, ' ', address3) as address
from member where userid='apple';

#left, right: 왼쪽 또는 오른쪽에서 길이만큼 문자열을 가져옴
select left('ABCDEFGHIJKLMN', 5) as dd ;

- A,B,C,D,E 만 나온다.

# substring : 문자열의 일부를 가져옴
# substring(문자열, 시작위치): 시작위치부터 끝까지
# substring(문자열, 시작위치, 길이) : 시작위치부터 길이만큼
select substring('ABCDEFGHIJKLMN', 5) as sub;
select substring('ABCDEFGHIJKLMN', 5, 3) as sub;
select userid, substring(userpw, 1, 3) as password from member ;

- EFGHIJKLMN

- EFG

- 비밀번호의 1-3까지의 수가 표현된다.

# char_length : 문자열의 길이를 반환
select char_length('ABCDEFGHIJKLMN') as ant ;
select email,char_length(email) as len from member;

-문자의 길이를 알려준다.

# lpad, rpad : 왼쪽 또는 오른쪽의 해당 길이만큼 늘리고, 빈 공간을 채울 문자열을 반환
# lpad(문자열, 총길이, 채울문자열)
select lpad('ABSDEFG',10,'0') as pad;
select userid, rpad(userid,20,'*') as pad from member ;

# ltrim, rtrim, trim : 왼쪽, 오른쪽, 모든 공백을 제거
select ltrim(' ABCDEF ') as ltrim;
select trim(' ABCDEF ') as trim;

-왼쪽 공백이 사라진다.

-모든 공백이 사라진다.

#replace : 문자열에서 특정 문자열을 변경
#replace(문자열, 대상, 바꿀 문자열)
select replace ('ABCDEFG', 'CD', '') as repl;

-CD자리에 아무것도 안들어가짐

유니온(union)
    합집합을 나타내는 연산자로, 중복된 값을 제거함
    서로 같은 종류의 테이블(컬럼이 같아야 함)에서만 적용이 가능
    select 컬럼명1, 컬럼며2, .. from 테이블1 union select 컬럼명1,컬럼명2,.. from 테이블2

create table product(
code varchar(5) not null,
    name varchar(20) not null,
    detail varchar(1000),
    price int default 0,
    regdate datetime default now()
);

- 새로운 PRODUCT테이블을 만들어준다

insert into product values ('00001','그래픽카드','엄청빨라요',400000,now());
insert into product values ('00002','맥북','너무 이뻐요',1400000,now());
insert into product values ('00003','밀키트','맛있어요',10000,now());
insert into product values ('00004','서버','잘돌아요',10000000,now());
insert into product values ('00005','스포츠카','빨라요',100000000,now());

- 테이블안에 데이터를 넣어준다.

create table product_new(
code varchar(5) not null,
    name varchar(20) not null,
    detail varchar(1000),
    price int default 0,
    regdate datetime default now()
);

-유니온을 해보기 위해 새로운 테이블 PRODUCT_NEW 을 생성한다.

insert into product_new values ('10001','게임','재밌어요',91000,now());
insert into product_new values ('10002','집','넓어요',500000000,now());
insert into product_new values ('10003','고양이','야옹',500000,now());
insert into product_new values ('10004','강아지','멍멍',400000,now());

- 데이터를 넣어준다

# 두 테이블의 모든 로우가 합쳐짐
select code, name, price, regdate from product
union
select code, name, price, regdate from product_new;

- 두테이블이 모두 합쳐진다.

# 날짜/시간이 다르기 때문에 중복 데이터가 모두 출력
select code, name, price, regdate from product
union
select code, name, price, regdate from product_new;

- REGDATE의 시간이 다르게 때문에 출력됨

서브쿼리(sub query)
    - 다른 쿼리 내부에 포함되어 있는 select문을 의미
    - 서브쿼리를 포함하고 있는 쿼리를 외부쿼리라고 부르고, 서브쿼리는 내부쿼리라고도 부름
    - 서브쿼리는 괄호()를 사용해서 표현
    - select, where, from, having 절 등에서 사용할 수 있음

#예제 : 상품코드가 '00001'의 가격보다 크거나 같은 가격을 가지고 있는 상품의 모든 정보를 출력

select *from product where price >= (select price from product where code='00001');

# 예제 : product 테이블에서 모든 상품의 code, name, price, 가장비싼가격을 모두출력

select code, name, price, (select max(price) from product) as max_price from product;

# auto_increment : 필드에 identity한 숫자를 자동으로 부여

create table orders(
no int auto_increment primary key,
    userid varchar(20) not null,
    product_code varchar(5) not null,
    cnt int default 0,
    regdate datetime default now(),
    foreign key(userid) references member(userid)
);

-NO 가 알아서 자동으로 1씩 올라가면 숫자를 부여한다.

insert into orders values(1, 'apple', '00005',1,null);
insert into orders values(10, 'banana', '00004',1,null);
-- insert into orders values(10, 'apple', '00003',2,null); # 중복된 넘버가 있기 때문
insert into orders (userid, product_code, cnt) values ('apple', '00003',1);
insert into orders (userid, product_code, cnt) values ('banana', '00002',1);
insert into orders (userid, product_code, cnt) values ('orange', '00004',1);
insert into orders (userid, product_code, cnt) values ('avocado', '00003',1);
insert into orders (userid, product_code, cnt) values ('cherry', '00001',1);

- ORDER테이블에 데이터를 넣어준다.

# 예제 : 상품을 최소 2번이상 구입한 횟수가 있는 회원의 아이디와 이름, 성별을 출력

select userid,name,gender from member where userid
in (select userid from orders group by userid having count(no)>=2);

# 예제 : 상품을 최소 2번이상 구입한 아이디의 앞 2글자와 이름, 상품 구입횟수를 출력

# 조인을 사용해서 코딩
select left(m.userid,2) as userid ,m.name, count(o.no) as cnt from member as m inner join
orders as o on m.userid=o.userid group by m.userid having cnt>=2 ;

create table orders_new(
no int auto_increment primary key,
    userid varchar(20) not null,
    product_code varchar(5) not null,
    cnt int default 0,
    regdate datetime default now(),
    foreign key(userid) references member(userid)
);

- 새로운 ORDER_NEW테이블 작성

뷰(view)
    - 가상의 테이블을 생성
    - 실제 테이블처럼 행과 열을 가지고 있지만, 데이터를 직접 저장하고 있지 않음

    뷰를 사용하는 이유
    - SQL코드를 간결하게 만들어 줌
    - 삽입, 삭제, 수정 작업에 대한 제한 사항을 가짐
    - 내부 데이터를 전체 공개하고 싶지 않을 때

create view 뷰이름 as 쿼리문 ...

select *from member;
select userid,name,hp,gender from member;

create view vw_member as select userid,name,hp,gender from member;
select *from vw_member;

-가상의 뷰를 만들어보자.

# 예제 : member 테이블의 userid, name, hp 와 profile 테이블의 mbti를 가지는 가상 테이블(뷰)를 만들어보자
# vw_member_profile

create view vw_member_profile as select m.userid,m.name,m.hp,p.mbti from member as m left join profile p on m.userid=p.userid;
select *from vw_member_profile;

다이어그램 보기
ER - Diagram
Database - > Reverse Engineer

MYSQL 사용자
    1. 사용자 추가하기
    MYSQL 8.0 COMMAND Line Client에서 root 계정으로 로그인
    접속 가능한 사용자 추가하기
    create user '사용자명'@'localhost' identified by '비밀번호';
    create user 'apple'@'localhost' identified by '1111';
    - 사용자 목록 조회
    user mysql;
    select user, host from user;
    - 할당 권한 상세 옵션
    create, drop, alter : 테이블에 대한 생성, 삭제, 변경 권한
select, insert, update, delete : 테이블의 테이터를 조회,삽입,변경,삭제에 대한 권한
    all : 모든 권한
    usage : 권한을 부여하지 않고 계정만 생성

    grant 권한 on 데이터베이스명,테이블명 to '사용자'@'localhost';
    grant all on*.* to 'apple'@'localhost';

    grant all on*.* to 'apple'@'%'; # 모든 ip에서 접근이 가능

    flush privileges;
    변경사항이 즉시 적용됨
2. 사용자 삭제하기
drop user 계정명;
3. 사용자 권한 조회하기
show grants for '계정명'@'localhost';
4. 사용자 권한 제거하기
remove 권한명 privileges on *.* from '계정명'@'localhost' ;

use mysql;
create user 'apple'@'localhost' identified by '1111';
select user, host from user;

-apple 사용자를 만들어준다.

#apple.member에 select 권한만 가능한 apple 계정을 생성
  create user 'apple'@'localhost' identified by '1111';
  grant select on apple.member to 'apple'@'localhost';
  flush privileges;