2009년 6월 28일 일요일
Define foreign key
Drop table Authors;
Drop table AuthorBook;
CREATE TABLE Books (
BookID SMALLINT NOT NULL PRIMARY KEY,
BookTitle VARCHAR(60) NOT NULL,
Copyright YEAR NOT NULL
)
ENGINE=INNODB;
INSERT INTO Books VALUES (1, 'Letters', 1934),
(2, 'Ohio', 1919),
(3, 'Angels', 1966),
(4, 'Speaks', 1932),
(5, 'Man', 1996),
(6, 'A', 1980),
(7, 'Card', 1992),
(8, 'The', 1993);
CREATE TABLE Authors (
AuthID SMALLINT NOT NULL PRIMARY KEY,
AuthFN VARCHAR(20),
AuthMN VARCHAR(20),
AuthLN VARCHAR(20)
)
ENGINE=INNODB;
INSERT INTO Authors VALUES (1, 'Henry', 'S.', 'Thompson'),
(2, 'Jack', 'Carol', 'Oates'),
(3, 'Red', NULL, 'Elk'),
(4, 'White', 'Maria', 'Rilke'),
(5, 'Anne', 'Kennedy', 'Toole'),
(6, 'Jane', 'G.', 'Neihardt'),
(7, 'Jane', NULL, 'Yin'),
(8, 'Alan', NULL, 'Wang');
CREATE TABLE AuthorBook (
AuthID SMALLINT NOT NULL,
BookID SMALLINT NOT NULL,
PRIMARY KEY (AuthID, BookID),
FOREIGN KEY (AuthID) REFERENCES Authors (AuthID),
FOREIGN KEY (BookID) REFERENCES Books (BookID)
)
ENGINE=INNODB;
INSERT INTO AuthorBook VALUES (1, 8),
(2, 7),
(3, 6),
(4, 5),
(5, 4),
(6, 2),
(8, 1);
긴 문자열 짧게 보여주기
[mysql]명령어모음
-------------접속---------------------
C:\Documents and Settings\황재호> cd \mysql\bin
C:\mysql\bin> mysql
C:\mysql\bin> mysql -u계정 -p비밀번호 데이터베이스명
mysql> quit
-------------------DB생성---------------------
create database daios;
-----------------테이블 검색---------------------
show tables;
----------------필드정보 검색-----------------------
show columns from tablename;
------------------테이블 구조 정보-------------------
desc tablename;
----------------테이블 생성---------------
create table friend(
num int not null,
name varchar(10),
address varchar(80),
tel varchar(20),
PRIMARY KEY(num)
);
----------------데이터 삽입-----------------
mysql> insert into 테이블명 (필드명1, 필드명2, ....)
values (필드값1, 필드값2, ...);
mysql> insert into friend (num, name, address, tel)
values (1, ‘배성진‘, ’서울 동작구 노량진동‘, ’234-7693‘);
mysql> insert into friend values(1,'박문수','서울시 노원구','010-8886-0487');
mysql> insert into friend values(2,'김성복','서울시 성북구','010-7776-0123');
------------------데이터 확인------------------
mysql> select * from friend;
-----------------조건검색------------------
mysql> select 필드명1, 필드명2, … from 테이블명 where 조건식;
mysql> select id, name, address, tel, sex from mem where sex='W';
mysql> select * from mem where age>=50;
mysql> select name, id, address, post_num from mem where
age>=20 and age<30;
mysql> select name, id, address, post_num, age from mem where
name='김진모';
mysql> select name, address, age from mem where
(age>=40 and age<50) and sex='M';
mysql> select name, id, address, tel, age from mem where
( (age>=20 and age<30) or (age>=40 and age<50) )
and sex='W';
mysql> select name, address, tel from mem where name like '김%';
mysql> select * from mem where address like '서울%';
mysql> select name, address, sex from mem where
-> address like '부산%' and sex='W' ;
mysql> select name, id from mem where name like '__용%' ;
mysql> select name, address, tel from mem where
-> address like '광주%' and name like '김%';
mysql> select 필드명1, 필드명2 from 테이블명 order by 필드명;
mysql> select age, id, name, sex, tel from mem order by age;
mysql> select age, id, name, sex, tel from mem order by age desc;
mysql> select age, name, address from mem where address like '서울%‘
order by age desc;
-------------------데이터 수정-------------------------
mysql> update 테이블명 set 필드명=필드값 [where 조건식]
mysql> update mem set tel='123-4567' where id='yjhwang';
mysql> select id, name, tel from mem where id='yjhwang';
mysql> update mem set age=27 where name='신수진';
mysql> select name, age from mem where name='신수진';
ALTER TABLE `members` ADD `home` VARCHAR( 50 ) NULL AFTER `email` ;
------------------데이터 삭제하기---------------
mysql> delete from 테이블명 [where 조건식]
mysql> delete from mem where name='김길수';
mysql> delete from mem where age>=30 and age<=50;
mysql> select name, address, age from mem;
mysql> delete from mem;
----------------데이터 백업--------------------
C:\mysql\bin> mysqldump -u계정 -p비밀번호 데이터베이스 이름 >
백업파일명
C:\mysql\bin> mysqldump -uphp5 -p1234 php5_db >
php5_db.sql
------------데이터 복원-------------------
C:\mysql\bin> mysql -u계정 -p비밀번호 데이터베이스 이름 <
백업파일명
C:\mysql\bin> mysql -utest -p1234 test_db < php5_db.sql
--------------생성된 DB확인----------------
mysql> show databases;
------------계정 비번 등록---------------
mysql> insert into user (host, user, password)
values ('localhost', 'php5', password('1234'));
-------------계정 확인---------------
mysql> select host, user, password from user;
---------------권한 등록----------------
mysql> insert into db values ('localhost', 'php5_db', 'php5', 'y', 'y',
'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y');
--------------변경된 내용 저장하기------------
mysql> flush privileges;
--------------새로운 계정 접속---------------
C:\mysql\bin> mysql -uphp5 -p1234 php5_db
------------관리자 계정 접속----------------
C:\mysql\bin> mysql mysql
-----------비밀번호 변경-----------------------
mysql> update user set password = password('1234') where
user = 'root';
-------------시스템에 적용---------------
mysql> flush privileges;
------------데이터 베이스 삭제------------
mysql> drop database 데이터베이스명;
-------------테이블 목록 보기------------
mysql> show tables;
--------------테이블 구조 보기-----------
mysql> desc 테이블명;
----------새로운 테이블 추가----------------
mysql> alter table 테이블명 add 새로운필드명 타입
[first 또는 after 필드명] ;
mysql> alter table friend add age int;
--------------address 다음에 필드 추가---------------
mysql> alter table friend add email char(30) after address;
---------------필드 삭제------------------
mysql> alter table 테이블명 drop 삭제할필드명1, 삭제할필드명2;
mysql> alter table friend drop email;
---------------필드 수정------------------
mysql> alter table 테이블명 change 이전필드명 새로운필드명 타입;
mysql> alter table friend change tel phone int;
------------필드 타입 수정--------------
mysql> alter table 테이블명 modify 필드명 새로운타입;
mysql> alter table friend modify name int;
----------테이블명 수정----------------
mysql> alter table 이전테이블명 rename 새테이블명;
mysql> alter table friend rename student;
-----------테이블 삭제----------------
mysql> drop table friend;
----------------.sql명 실행--------------------
C:\mysql\bin> mysql -uphp5 -p1234 php5_db < friend.sql
-----------------table 생성--------------------
create table testboard(
num int not null default '0' auto_increment,
writer varchar(12),
passwd varchar(12),
subject varchar(50),
content blob,
visit int(4),
primary key(num)
);
--------------조건검색---------------------
select * from user_info where LIKE '이%'
--성이 '이'성만 가진 사람을 검색
select * from user_info where LIKE '%이%'
--'이'를 가진 사람을 모두 검색
---------------조건삭제-------------------
delete from 테이블명 where 필드네임=130;
delete from board where num=130;
delete from board where nun<130;
--넘버가 130아래로 전부삭제
delete from board where subject LIKE '%민%'
--서브젝트에서 '민'이란 단어들어가면 전부 삭제
----------------조건 업데이트------------------
update test set name='다이오스' where num=120;
-------------------인서트문---------------
insert into test(name, id)
values('다이오스','kim')
----------------권한부여--------------------------
grant select, insert, update, delete, create, drop
on chap11.* to 'jspexam'@'localhost'
identified by '1234';
grant select, insert, update, delete, create, drop
on chap11.* to 'jspexam'@'%'
identified by '1234';
-------------공백관련 레고드 검색-----------
select * from MEMBER where NAME <> "" ;
select * from MEMBER where NAME is null;
select * from MEMBER where NAME is not null;
MySQL Like 한글 문제시
2) select * from member where binary(addr) like '%금정%'
1) => 2)번 방법으로..