-------------접속---------------------
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;
댓글 없음:
댓글 쓰기