SQL 기본문법 4

use market_db;
update city_popul set city_name = '서울' where city_name = 'Seoul';
select *from city_popul where city_name = '서울'

데이터 입력 : insert

insert 기본문법

use market_db;
create table hongong1 (toy_id int, toy_name char(4), age int);
insert into hongong1 values (1, '우디',25);

insert into hongong1(toy_id, toy_name) values (2, '버즈');

insert into hongong1(toy_name, age, toy_id) values ('제시', 20, 3);

자동으로 증가하는 auto_increment

create table hongong2(toy_id int auto_increment primary key, toy_name char(4), age int);

insert into hongong2 values (null, '보핍', 25);
insert into hongong2 values (null, '슬링키', 22);
insert into hongong2 values (null, '렉스', 21);
select * from hongong2;

select last_insert_id();

alter table hongong2 auto_increment=100;
insert into hongong2 values (null, '재남', 35);
select * from hongong2;

create table hongong3(
	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으로 지정

insert into hongong3 values (null, '토마스', 20);
insert into hongong3 values (null, '제임스', 23);
insert into hongong3 values (null, '고든', 25);
select * from hongong3;

다른 테이블의 데이터를 한 번에 입려하는 insert into ~ select

select count(*) from world.city;

desc world.city;

select * from world.sity limit 5;

create table city_popul (city_name char(35), population int);

insert into city_popul select name, population from world.city;

---

데이터 수정 : update

update문의 기본 문법

update 테이블_이름
	set 열1 = 값1, 열2 = 값2, .......
    whee 조건 ;

use market_db;
update city_popul set city_name = '서울' where city_name = 'Seoul';
select *from city_popul where city_name = '서울';

update city_popul set city_name = '뉴욕', population = 0 where city_name = 'new york';
select *from city_popul where city_name = '뉴욕';

where가 없는 update문

update city_popul set population = populaiton/10000;
select * from city_popul limit 5;

---

데이터 삭제 : delete

delete from 테이블이름 where 조건;

delete from city_popul where city_name like 'new%';
--new로 시작하는 모든 도시 삭제

delete from city_popul where city_name like 'new%' limit 5;
-- new로 시작하는 상위 5개만 삭제

 

 

참조 : 혼자 공부하는 SQL