PostgreSQL鐃緒申MySQL

鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申 鐃叔￥申鐃緒申鐃駿￥申鐃緒申名:mydb 鐃緒申$:鐃緒申鐃藷ソ￥申鐃緒申鐃� 鐃緒申:Enter 鐃緒申tako:鐃醇ー鐃緒申名 鐃緒申tbl:鐃銃￥申鐃瞬ワ申名

鐃緒申PostgreSQL	鐃緒申鐃緒申鐃緒申鐃緒申	鐃緒申MySQL
鐃緒申/var/lib/pgsql	RPM:DBdir	鐃緒申/var/lib/mysql
鐃緒申5432	鐃緒申続鐃楯￥申鐃緒申No	鐃緒申3306
鐃緒申/var/lib/pgsql/data/pg_hba.conf	鐃緒申鐃緒申侫鐃緒申鐃緒申鐃�	鐃緒申/etc/my.cnf
鐃緒申動/鐃緒申鐃�
鐃緒申$ psql mydb	鐃緒申動	鐃緒申$ mysql -u tako -h hostname -p
鐃緒申=> \q;	鐃緒申了	鐃緒申mysql> \q;
鐃緒申$ psql -c "vacuum;" mydb	鐃出ワ申鐃遵ー鐃緒申	鐃緒申
鐃緒申$ vacuumdb --analyze mydb	鐃出ワ申鐃遵ー鐃緒申	鐃緒申
鐃緒申$ pg_ctl -w start	postmaster鐃塾居申動	-----------------
鐃緒申$ pg_ctl stop	postmaster鐃緒申鐃緒申鐃�	-----------------
-----------------	MySQL鐃緒申鐃緒申鐃�	# mysqladmin -u tako -p shutdown
鐃緒申=> create user tako with oassword 'tako';	crypt authentication	-----------------
鐃緒申$ pg_dump mydb > 鐃春ワ申鐃緒申鐃緒申名	backup	鐃緒申mysql> mysqldump mydb tbl > dumpfile名
鐃緒申	backup	鐃緒申mysql> mysqldump --database mydb > dumpfile名 鐃緒申
鐃緒申$ pg_dumpall -o > 鐃春ワ申鐃緒申鐃緒申名	鐃緒申backup	鐃緒申mysql> mysqldump --all-database > dumpfile名 鐃緒申
鐃緒申$ psql mydb < 鐃緒申鐃緒申廛侫鐃緒申鐃緒申鐃�	鐃所ス鐃夙ワ申	鐃緒申
鐃緒申$ psql -e template1 < 鐃緒申鐃緒申廛侫鐃緒申鐃緒申鐃�	鐃緒申鐃所ス鐃夙ワ申	鐃緒申
鐃緒申	table鐃緒申鐃緒申鐃出わ申	鐃緒申mysql> SELECT * INTO OUTFILE '/tmp/tako.out' FROM tbl;
鐃緒申	同CSV	鐃緒申mysql> SELECT * INTO OUTFILE '/tmp/tako.out' FILEDS TERMINATED BY ',' ENCLOSED BY '"' FROM tbl;
鐃緒申psql mydb < /home/tako/鐃春ワ申鐃緒申鐃緒申名	鐃緒申鐃緒申鐃緒申譽�鐃緒申鐃緒申鐃�	鐃緒申$ mysql -u root -p mydb < sql.txt鐃緒申
鐃緒申=> \i /home/tako/鐃春ワ申鐃緒申鐃緒申名	鐃緒申鐃緒申鐃緒申譽�鐃緒申鐃緒申鐃�	鐃緒申mysql> LOAD DATA INFILE '/home/tako/xxx/txt' IGNORE INFO TABLE tbl
鐃緒申	鐃緒申:鐃竣駈申鐃准わ申鐃宿み刻申鐃緒申	鐃緒申mysql> LOAD DATA INFILE '/home/tako/xxx/txt' INFO TABLE tbl FILEDS TERMINATED BY ':';
鐃緒申	鐃緒申'"'鐃緒申CSV同	鐃緒申mysql> LOAD DATA INFILE '/home/tako/xxx/txt' INFO TABLE tbl FILEDS TERMINATED BY ',' ENCLOSED BY '"';
user
-----------------	rootpasswoord	鐃緒申# mysqladmin -u root password '********'
$ createuser tako => alter user tako with encrypted password 'xxxxxx';	user鐃緒申録	鐃緒申mysql> grant all on mydb.* to tako@localhost identified by "password";
鐃緒申	password鐃緒申鐃術刻申	鐃緒申mysql> SET PASSWORD FOR tako@localhost = PASSWORD('password');
-----------------	鐃緒申侫譽件申鐃�	鐃緒申mysql> FLUSH PRIVILEGES;
鐃緒申$ dropuser tako	user鐃緒申鐃�	鐃緒申
鐃緒申=> select usename from pg_user	user鐃緒申鐃緒申	鐃緒申mysql> SELECT host,user,password FROM mysql.user;   鐃緒申鐃緒申鐃緒申録鐃緒申鐃緒申討鐃緒申鐃� host,user,password 鐃緒申鐃塾るこ鐃夙わ申鐃叔わ申鐃暑。
DB鐃緒申鐃緒申
鐃緒申$ createdb mydb 鐃緒申	DB鐃緒申鐃緒申	鐃緒申mysql> create database mydb; 鐃緒申
鐃緒申$ dropdb mydb	DB鐃緒申鐃�	鐃緒申mysql> drop database mydb; 鐃緒申
鐃緒申$ psql mydb	DB鐃緒申鐃緒申	鐃緒申mysql> use mydb;
鐃緒申$ psql -l	DB鐃緒申鐃緒申	鐃緒申mysql> show databases;
鐃緒申=> \l;	DB鐃緒申鐃緒申	鐃緒申mysql> show databases;
table
鐃緒申=> create table tbl;	table鐃緒申鐃緒申	鐃緒申mysql> create table tbl;
鐃緒申=> drop table tbl;	table鐃緒申鐃�	鐃緒申mysql> drop table tbl; 鐃緒申
-----------------	table鐃緒申適鐃緒申	鐃緒申mysql> optimize table tbl ;
鐃緒申=> \dt;	table鐃緒申鐃緒申	鐃緒申mysql> show tables;
鐃緒申	table鐃緒申鐃緒申	鐃緒申mysql> DESCRIBE tbl;    鐃銃￥申鐃瞬ル構造鐃塾逸申鐃緒申 鐃緒申mysql> SHOW FIELDS FROM tbl;    鐃銃￥申鐃瞬ル構造鐃塾逸申鐃緒申   鐃舜器申鐃緒申PHP鐃緒申p208
鐃緒申鐃緒申鐃�(鐃春ワ申鐃緒申鐃緒申鐃�)
鐃緒申Sample	鐃緒申鐃竣ﾑわ申鐃宿駕申	鐃緒申mysql> alter table <鐃獣�鐃緒申�ﾞ�鐃縮�> add <鐃緒申鐃緒申鐃縮�> <鐃緒申鐃緒申鐃緒申鐃緒申> [FIRST|AFTER <鐃緒申鐃竣�鐃縮�>] 鐃緒申mysql> alter table book_table add bbuy varchar(20) NOT NULL;    MYSQL鐃緒申徹鐃曙攻略鐃緒申鐃緒申鐃宿￥申p175
鐃緒申=> \d tbl;	鐃緒申鐃緒申鐃緒申鐃緒申	鐃緒申mysql> desc tb1;
鐃緒申	鐃緒申鐃緒申鐃緒申媛鐃�	鐃緒申mysql> alter table tb1 add column(鐃緒申鐃緒申鐃縮� char(20));
鐃緒申	鐃叔￥申鐃緒申鐃緒申録	鐃緒申mysql> insert into tb1(鐃緒申鐃緒申鐃縮�) values('鐃叔￥申鐃緒申');
鐃緒申	table鐃緒申鐃銃逸申鐃緒申	鐃緒申mysql> SELECT * FROM tbl    鐃緒申鐃緒申鐃叔￥申鐃緒申鐃塾逸申鐃緒申表鐃緒申
鐃緒申=> Sample	鐃緒申鐃竣ﾑの削申鐃�	鐃緒申mysql> alter table <鐃獣�鐃緒申�ﾞ�鐃縮�> drop <鐃緒申鐃竣�鐃縮�>
鐃緒申Sample	鐃緒申鐃竣ﾑわ申鐃宿駕申(鐃緒申鐃緒申鐃緒申)	鐃緒申mysql> alter table <鐃獣�鐃緒申�ﾞ�鐃縮�> add <鐃緒申鐃竣�鐃縮�> first
鐃緒申=> Sample	鐃緒申鐃竣ﾑわ申鐃宿駕申(鐃叔醐申鐃緒申)	鐃緒申mysql> alter table <鐃獣�鐃緒申�ﾞ�鐃縮�> add <鐃緒申鐃竣�鐃縮�>
鐃緒申Sample	鐃緒申鐃竣ﾑにﾌﾟﾗ�鐃緒申�ﾘ�鐃緒申鐃緒申鐃緒申鐃緒申媛鐃�	鐃緒申mysql> alter table <鐃獣�鐃緒申�ﾞ�鐃縮�> add primary key (<鐃緒申鐃竣�鐃縮�>)
鐃叔￥申鐃緒申鐃塾種申鐃緒申鐃緒申
鐃緒申 鐃緒申	鐃銃ワ申鐃緒申鐃夙フワ申鐃緒申鐃緒申	SOURCE 鐃緒申鐃緒申鐃緒申廛肇侫鐃緒申鐃緒申鐃縮�   鐃舜器申鐃緒申PHP鐃緒申p213 mysql> LOAD DATA INFILE '/home/tako/../../../data.txt' INTO TABLE tablename;   鐃緒申MySQL徹鐃緒申鐃緒申僉鐃�p63 mysql -u 鐃醇ー鐃緒申名 -p <鐃緒申鐃春ワ申鐃緒申鐃緒申名   鐃緒申PHP鐃緒申鐃緒申鐃�p202
鐃緒申	CVS鐃春ワ申鐃緒申鐃緒申	鐃緒申鐃緒申
鐃緒申psql mydb < /home/tako/鐃春ワ申鐃緒申鐃緒申名	鐃緒申鐃緒申鐃緒申譽�鐃緒申鐃緒申鐃�	鐃緒申$ mysql -u root -p mydb < sql.txt鐃緒申
鐃緒申=> \i /home/tako/鐃春ワ申鐃緒申鐃緒申名	鐃緒申鐃緒申鐃緒申譽�鐃緒申鐃緒申鐃�	鐃緒申mysql> LOAD DATA INFILE '/home/tako/xxx/txt' IGNORE INFO TABLE tbl
鐃緒申	鐃緒申:鐃竣駈申鐃准わ申鐃宿み刻申鐃緒申	鐃緒申mysql> LOAD DATA INFILE '/home/tako/xxx/txt' INFO TABLE tbl FILEDS TERMINATED BY ':';   鐃緒申MySQL徹鐃緒申鐃緒申僉鐃�p65
鐃緒申	鐃緒申'"'鐃緒申CSV同	鐃緒申mysql> LOAD DATA INFILE '/home/tako/xxx/txt' INFO TABLE tbl FILEDS TERMINATED BY ',' ENCLOSED BY '"';   鐃緒申MySQL徹鐃緒申鐃緒申僉鐃�p65
鐃叔￥申鐃緒申鐃塾書きわ申鐃緒申
鐃緒申 鐃緒申	鐃銃ワ申鐃緒申鐃夙フワ申鐃緒申鐃緒申	鐃緒申
鐃緒申	CVS鐃春ワ申鐃緒申鐃緒申	鐃緒申鐃緒申
鐃緒申鐃緒申鐃緒申鐃緒申
鐃緒申=> grant insert on tbl,tb2 to nobody;	鐃緒申鐃緒申鐃緒申鐃緒申 鐃緒申	鐃緒申
鐃緒申=> grant select on tbl,tb2 to nobody;	鐃緒申鐃緒申鐃緒申鐃緒申 鐃緒申鐃緒申鐃緒申鐃緒申	鐃緒申
鐃緒申=> grant all on tbl to tako;	鐃緒申鐃緒申鐃緒申鐃緒申 all鐃緒申鐃緒申	鐃緒申
鐃緒申=> revoke all on tbl from tako;	鐃緒申鐃緒申鐃術刻申 鐃緒申鐃�	鐃緒申mysql> REVOKE SELECT ON mydb.* FROM tako; 鐃緒申
鐃緒申=> create group group1;	鐃緒申鐃暑ー鐃竣削申鐃緒申	鐃緒申
鐃緒申=> alter group group1 add user tako;	tako鐃薯グルー鐃緒申1	鐃緒申
鐃緒申=> grant select on tbl to group group1;	鐃緒申鐃暑ー鐃緒申1鐃舜醐申鐃緒申	鐃緒申
鐃緒申=> \z;	鐃緒申鐃渋縁申鐃緒申	鐃緒申mysql> GRANTS FOM tako@localhost;
SQL文
鐃緒申	鐃曙コ鐃緒申鐃緒申鐃宿駕申	鐃緒申
鐃緒申	鐃曙コ鐃緒申鐃宿削申鐃�	鐃緒申
鐃緒申	鐃曙コ鐃緒申鐃緒申鐃術刻申	鐃緒申
鐃緒申=> SELECT * FROM tbl;	鐃曙コ鐃緒申鐃緒申表鐃緒申	鐃緒申
鐃緒申	鐃曙コ鐃緒申鐃緒申鐃緒申鐃緒申	鐃緒申
鐃緒申=> copy tb1 from /mnt/mo/file.data;	鐃曙コ鐃緒申鐃宿種申鐃緒申	鐃緒申
鐃緒申=> copy tb1 to /mnt/mo/file.data;	鐃曙コ鐃緒申鐃宿緒申鐃�	鐃緒申
鐃緒申=> copy tb1 '/mnt/mo/file.data' using delimiters '.';	鐃曙コ鐃緒申鐃宿わ申copy	鐃緒申
鐃緒申	鐃曙コ鐃緒申鐃宿常申鐃緒申鐃緒申	鐃緒申
鐃緒申	鐃春ワ申鐃緒申鐃緒申匹鐃緒申儿鐃�	mysql> alter table tb1 modify field text not null; 鐃緒申
鐃緒申	複鐃緒申鐃緒申鐃緒申鐃夙刻申	鐃緒申
鐃緒申	鐃緒申鐃緒申妊奪鐃緒申鐃緒申鐃緒申鐃�	鐃緒申
鐃叔￥申鐃緒申鐃緒申鐃緒申鐃緒申(data鐃緒申)
鐃緒申int	鐃緒申鐃緒申	鐃緒申
鐃緒申float	鐃緒申動鐃緒申鐃緒申鐃緒申	鐃緒申
鐃緒申real	鐃渋随申	鐃緒申
鐃緒申char	文鐃緒申鐃緒申	鐃緒申
鐃緒申varchar	鐃緒申鐃緒申文鐃緒申鐃緒申	鐃緒申
鐃緒申date	data鐃緒申 鐃緒申鐃緒申	鐃緒申
鐃緒申time	鐃緒申鐃緒申	鐃緒申
PHP鐃舜随申
鐃緒申	鐃緒申続ID鐃緒申鐃緒申	鐃緒申mysql_connect("host", "usr","password")
鐃緒申pg_connect()	db鐃緒申続	鐃緒申mysql_select_db(db名, 鐃緒申続ID)
鐃緒申pg_exec()	SQL文鐃渋刻申	鐃緒申mysql_query(鐃緒申鐃緒申鐃緒申, 鐃緒申続ID)
鐃緒申	連鐃緒申鐃緒申鐃緒申亮鐃緒申鐃�	鐃緒申mysql_fetch_array(鐃緒申鐃�ID)
鐃緒申pg_freeresult()	鐃緒申鐃緒申粒鐃緒申鐃�	鐃緒申mysql_free_result(鐃緒申鐃�ID)
鐃緒申pg_close()	db鐃緒申鐃緒申	鐃緒申
鐃緒申鐃緒申/鐃緒申鐃緒申URL
PostgreSQL ODBC Driver 鐃緒申鐃旬醐申鐃緒申
PostgreSQL鐃緒申MySQL(鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃祝栽随申任鐃�)
MySQL(鐃緒申鐃緒申鐃緒申鐃緒申鐃緒申鐃祝栽随申任鐃�)
Sample
鐃緒申=> Sample	Sample	鐃緒申mysql> Sample
鐃緒申Sample	Sample	鐃緒申Sample

top     home:top     server library:top     PHP PostgreSQL MySQL 鐃緒申鐃緒申廛襯刻申鐃緒申鐃竣ワ申