Data Base Command

show databases; <---- 데이터베이스 확인 명령어

create table stdtbl (

stdid int not null primary key,

name char( 15) not null,

age int( 10) not null,

height varchar( 10),

sex char( 5),

addr varchar( 25));

flush privileges;

desc stdtbl;    <--- 테이블 확인 명령어

create table haktbl (

num int not null auto_increment primary key,

haknum int not null,

name char( 15),

class1 char( 15),

group1 varchar( 25),

etc char (15));

flush privileges;

alter table stdtbl rename stdtbl7;     <--- 테이블명 변경

alter table stdtbl modify name char( 15) not null; <--- 속성(Type) 바꾸기

alter table stdtbl change name name7 char( 15) not null;  <--- 필드(Field)명 바꾸기 

alter table stdtbl drop primary key <--- 삭제

alter table stdtbl modify stdid int not null primary key; <--- primary key 복원

 

alter table stdtbl add addr varchar ( 25) not null;  <------- 추가

alter table stdtbl add weight int not null after name7  <------ name7 다음에 추가

alter table stdtbl add num int first;  <--------- 맨앞에 추가

> create table stdtbl2(select * from stdtbl);  <------- 복사해서 만들기

> insert into stdtbl values(001,001,'hongildong',100,150,'190','Man','Seoul');    <----- 데이터 추가

> update haktbl set haknum='6' where names='jun'; <---- 정보 수정

num 을 6 으로  names 가 = jun 인 

# mysql -p mysql

create table buytbl(

num int not null auto_increment primary key,

userid int not null,

name char( 15) not null,

sex char ( 10) not null,

product varchar( 20) not null,

price int not null,

number1 int not null,

addr varchar ( 25));

flush privileges;

insert into buytbl values(null,001,'honggildong','M','computer',1000000,2,'Seoul');  DB 생성

alter table buytbl add group1 char ( 15) after number;    DB 추가

update buytbl set group1='elec' where userid=001;    값 추가

select * from buytbl;    확인

flush privileges;    저장

select stdid as "아이디",name7 as "이름",age as "나이",sex as "성별" from stdtbl;

select * from <테이블명> where         <---- 이 순서는 생략은 할 수 있어도 순서를 어겨선 안된다

group by

having

oder by

select * from stdtbl order by age desc;   내림차순

select * from stdtbl order by age desc , weight asc; 내림차순 하고 오름차순

select * from stdtbl order by age desc , weight asc limit 1; <--- 하나 출력

select * from stdtbl order by age desc , weight asc limit 1 , 2;   <--- 하나를 제외하고 2개출력

select * from stdtbl where height >= 170 and height <= 190;  <--- 키가 170 같거나크고 190보다 같거나 작다 출력

select * from stdtbl where height >= 170 and height <= 190 order by age;   <--- 키가 170 같거나크고 190보다 같거나 작

            고 나이는 오름차순 출력

select * from stdtbl where height between 170 and 190; <--- 170 ~ 190 사이 출력

select name7,max(height),min(height) from stdtbl;

select * from stdtbl where name7 like "hong%";

select userid,name,sum(price) from buytbl group by userid;

select userid,name,sum(price) from buytbl group by userid order by sum(price);

select userid,name,sum(price),group1 from buytbl group by group1 order by sum(price);

select userid as "아이디",name as "이름",sum(price) "총계",group1 "분류" from buytbl group by group1 order by sum(price);

vi /etc/my.cnf.d    <----- 데이터베이스 접근통제

bind-address=192.168.91.140    <--- 허용