[02] 테이블기초

use mydb
create database mydb --DB생성
drop database mydb   --DB삭제

형식)
create table 테이블명
(
 열이름1 속성,
 열이름2 속성...
)
아이디 이름 나이 주소 생일 수당
문자    문자  숫자 문자 날짜 숫자 -자료형data type
create table tb_test
(
 uid    varchar(20),
 uname  varchar(30),
 uage   int,
 uaddress varchar(255),
 ubirth   datetime,
 umoney  int
)
drop table tb_test

형식)
select 칼럼명
from 테이블명
where 조건절
order by 칼럼명 asc|desc
group by 칼럼명
having 칼럼명

select uid,uname,uage
from tb_test

select *
from tb_test

--레코드삽입
형식)
insert into 테이블명(열1,열2,열3...)
values (값1,값2,값3...)

 

insert into tb_test(uid,uname,uage,uaddress,ubirth,umoney)
values ('lee5','민들레',20,'서울','1988-12-25',123000)

 

insert into 테이블명
values(값1,값2,값3...)

--모든 열에 대한 값을 삽입하는 경우 열이름들은 생략가능

insert into tb_test
values('kim3','진달래',25,'부산','1990-3-15',150000)
insert into tb_test
values('kim8','개나리',22,'부산','1991-1-5',151000)
insert into tb_test
values('kim4','라일락',35,'서울','1991-3-25',130000)
insert into tb_test
values('park2','홍길동',25,'경기도','1990-10-5',150000)
insert into tb_test
values('park6','무궁화',30,'안양','1988-7-15',50000)
insert into tb_test
values('lee3','기무라',40,'서울','1970-3-5',150000)
insert into tb_test
values('lee4','타쿠야',30,'동경','1977-2-17',250000)
insert into tb_test
values('kim7','마사미',20,'나라','1990-4-20',10000)
insert into tb_test
values('park1','해바라기',55,'부산','1955-3-5',150000)

 

select *
from tb_test
order by uage asc --default오름차순asc/내림차순desc

--수정/삭제 나중에
--where조건절

select *
from tb_test
where uaddress='서울'

문)수당10만원이하 검색
select *
from tb_test
where umoney<=100000
order by umoney

 

문)나이20~29 검색

select *
from tb_test
where uage>=20
and uage<=29
order by uage desc

select *
from tb_test
where uage between 20 and 29
order by uage

 

begin tran
update tb_test
set uage=uage+1

commit
rollback

select * from tb_test

begin tran
delete from tb_test