database 관리

데이터 확인

show database;

데이터 생성

create database testdb;
show database;

데이터 제거

drop database testdb;
show database;

User 관리

create user 'xktm'@'localhost' identified by '1234';
select host, user from user;

create user 'xktm'@'%' identified by '1234';
select host, user from user;

drop user 'xktm'@'%';
drop user 'xktm'@'localhost'
select host, user from user;

권한 확인

show grants for 'xktm'@'localhost';

권한 부여

grant all on testdb.* to 'xktm'@'localhost';
show grants for 'xktm'@'localhost';

테이블

생성

use zerobase

create table mytable
    -> (
    ->     id int,
    ->     name varchar(16)
    -> );


show tables

 desc mytable;

변경

# 테이블명 변경
alter table mytable rename person;

# 컬럼 추가
alter table person add column agee double;

# 타입 변경
alter table person modify column agee int;

# 컬럼 변경
alter table person change column agee age int;

# 컬럼 삭제
 alter table person drop column age;

insert

insert into person(id, name, age, sex)
    -> values (1, '이효리', 43, 'F')
    -> ;

insert into person
    -> values (2, '이상순', 48, 'M');

select

select column명 from 테이블명

# *을 활용하여 전체 컬럼
select * from person;

# 특정 컬럼명
select name,age,sex from person;

where

select column명 from 테이블명 where 조건절

# 성별이 F인 경우

select * from person where sex='F';

# 나이가 50인 경우
select * from person where age=50;

update

update 테이블명 set 바꿀 내용 where 바꿀 곳;

update person set sex='F' where name='이미주';
select * from person;

update person set id=2 where name='유재석';
update person set id=3 where name='이미주';
select * from person;

delete

delete from 테이블명 where 삭제할내용이 있는곳;

delete from person where name='이상순';
select * from person;

delete from person where sex='F';
select * from person;

order by

select 컬럼명 from 테이블 order by 정렬;

select age, name from celab order by age ASC;

select age, name from celab order by age DESC;

select age,name from celab order by age, name;

select name, brithday, sex, agency from celab order by agency;

select * from celab order by agency, name;