myhome_bakcup ¼¿½ºÅ©¸³Æ®
#!/bin/bash T=$(echo `date +%Y%m%d%H%M`) MY=$(echo /home/nalabi/backup) echo "nalabi ÀÇ µ¥ÀÌŸ°¡ ¹é¾÷ µÇ¾ú½À´Ï´Ù. $T" mysqldump myhome > $MY/myhome$T.sql gzip $MY/myhome$T.sql |
/etc/crontab »ç¿ëÇϱâ
SHELL=/bin/bash PATH=/sbin:/bin:/usr/sbin:/usr/bin MAILTO=root HOME=/ # run-parts 01 * * * * root run-parts /etc/cron.hourly 02 4 * * * root run-parts /etc/cron.daily 22 4 * * 0 root run-parts /etc/cron.weekly 42 4 1 * * root run-parts /etc/cron.monthly |
Çü½Ä :
a b c d e id runparts directory
ºÐ(0~59) ½Ã(0~24) ³¯Â¥(0~31) ´Þ(0~12) ¿äÀÏ(0~7) id(Å©·ÐÀÌ »ç¿ëÇÒ °èÁ¤) directory(½ÇÇàÇÏ°ÔµÉ ÆÄÀÏ ³õÀÌ°Ô µÉ °æ·Î)
¿¹Á¦ :
15 14 1 * * root /home/nalabi/backup/myhome_backup
¸Å´Þ 1ÀÏ ¿ÀÈÄ 2½Ã 15¿¡ myhome_backupÀ» ½ÇÇàÇÑ´Ù.
0 22 * * 1-5 root /home/nalabi/backup/myhome_backup
¿ù¿äÀÏ ºÎÅÍ ±Ý¿äÀϱîÁö ¸ÅÀÏ ¿ÀÈÄ 8½Ã¿¡ myhome_backupÀ» ½ÇÇàÇÑ´Ù.
23 0-23/2 * * * root /home/nalabi/backup/myhome_backup
¸ÅÀÏ 0, 2, 4, ....½Ã 23ºÐ¿¡ ½ÇÇàÇÑ´Ù.
mysql ÁßÁöÇÑ´Ù.
/etc/init.d/mysql stop
µ¥¸óÀ» ¾Æ¹«³ª µé¾î°¥¼ö ÀÖ°Ô ´Ù½Ã ½ÇÇàÇÑ´Ù.
safe_mysqld --skip-grant & |
·çÆ® Æнº¿öµå¸¦ º¯°æÇÑ´Ù.
mysql> update user set password='' where user='root'; |
mysql À» Àç½Ãµ¿ ÇÕ´Ï´Ù.
/etc/init.d/mysql stop /etc/init.d/mysql start |
¼öÄ¡ µ¥ÀÌÅÍ Å¸ÀÔ
* INTEGER (INT) * SMALLINT * NUMBER * DECIMAL(DEC) (x,y) : x´Â ½Ç¼öÀÚ¸®, y´Â ¼Ò¼öÁ¡ ÀÌÇÏÀÚ¸® * FLOAT |
¹®ÀÚ µ¥ÀÌÅÍ Å¸ÀÔ
* VARCHAR (n) : 8ºñÆ® ¹®ÀÚ ±æÀÌ°¡ ÃÖ´ë n°³ÀÎ ¹®ÀÚ¿ *CHARACTER(CHAR) (n) : n°³ÀÇ 8ºñÆ® ¹®ÀÚ¿ * LONG |
±×¿ÜÀǵ¥ÀÌÅÍ Å¸ÀÔ
* DATE/TIME : ³¯Â¥/½Ã°£ * LONG RAW |
mysqlshow
+-----------+ |Databases | +-----------+ | board | | kedu | | mysql | | test | +-----------+ |
mysql test
Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with-A Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 7 to server version: 3.22.23b Type 'help' for help. mysql> |
mysqldump ¸¦ ÀÌ¿ëÇؼ sql µ¥ÀÌŸ¸¦ ¹é¾÷ ¹ÞÀ»¼ö ÀÖÀ¸¸ç DBÀ̸§¸¸ ÁöÁ¤Çϸé Àüü Å×À̺íÀÌ, Å×À̺í À̸§ ±îÁö ÁöÁ¤Çϸé ÇØ´ç Å×ÀÌºí¸¸ ¹é¾÷À» ÇÒ¼ö ÀÖ´Ù.
mysqldump DBÀ̸§ (tableÀ̸§) > ÈÀÏÀ̸§
mysqldump test test > test.sql
# MySQL dump 6.0 # # Host: localhost Database: test #-------------------------------------------------------- # Server version 3.22.23b # # Table structure for table 'test' # CREATE TABLE test ( no int(11), name char(10), tel char(15), addr char(20), sex char(1), birth date ); # # Dumping data for table 'test' # INSERT INTO test VALUES (2,'ÀÌÁÖ»ó','02-504-xxxx','°úõ½Ã','m','1971-02-02'); INSERT INTO test VALUES (3,'ÀÓÁ¤³²','02-420-xxxx','¼ÛÆı¸','f','1971-03-03'); INSERT INTO test VALUES (4,'°Çà¸ð','02-856-xxxx','°ü¾Ç±¸','f','1971-04-04'); INSERT INTO test VALUES (5,'ÀÌÇö¿µ','0342-718-xxxx','ºÐ´ç±¸','m','1971-05-05'); INSERT INTO test VALUES (6,'À̺´È¯','02-891-xxxx','ÇϾȵ¿','m','1980-06-06'); INSERT INTO test VALUES (1,'³²»ó¿í','333-4444','µ¿ÀÛ±¸','m','1971-01-01'); |
mysql test < test.sql
Âü°í·Î µ¥ÀÌŸ È®ÀÎÀº mysql -e "select * from test" test
+------+--------+---------------+--------+------+------------+ | no | name | tel | addr | sex | birth | +------+--------+---------------+--------+------+------------+ | 2 |ÀÌÁÖ»ó | 02-504-xxxx | °úõ½Ã | m | 1971-02-02 | | 3 | ÀÓÁ¤³² | 02-420-xxxx | ¼ÛÆı¸ | f | 1971-03-03 | | 4 | °Çà¸ð | 02-856-xxxx | °ü¾Ç±¸ | f | 1971-04-04 | | 5 | ÀÌÇö¿µ | 0342-718-xxxx | ºÐ´ç±¸ | m | 1971-05-05 | | 6 | À̺´È¯ | 02-891-xxxx | ÇϾȵ¿ | m | 1980-06-06 | | 1 | ³²»ó¿í | 333-4444 | µ¿ÀÛ±¸ | m | 1971-01-01 | +------+--------+---------------+--------+------+------------+ |
aaaa¶ó´Â »ç¿ëÀÚ(user)¸¦ ¸¸µì´Ï´Ù.
mysql mysql(mysql db·Î µé¾î°©´Ï´Ù.)
mysql>insert into user values ('localhost','aaaa','','y','y','y','y','y','y','y','y','y','y','y','y','y','y'); Query OK, 1 row affected (0.04 sec) |
reload ¸¦ ²À ½ÇÇà½Ãŵ´Ï´Ù.(½©¿¡¼)
mysqladmin reload
aaaa¶ó´Â »ç¿ëÀÚ¿¡°Ô aaaa¶ó´Â ºñ¹Ð¹øÈ£¸¦ ºÎ¿©ÇÕ´Ï´Ù.
mysqladmin -u aaaa password aaaa
mysqladmin reload
µ¥ÀÌŸ º£À̽º ¸¸µé±â
»ç¿ëÀÚ°¡ ¾øÀ» °æ¿ì : mysqladmin create aaaa
»ç¿ëÀÚ¿Í ºñ¹Ð¹øÈ£°¡ ÀÖ´Â °æ¿ì : mysqladmin -u aaaa -p create aaaa (or) mysqladmin -u aaaa -paaaa create aaaa
¼¹öÀÇ µ¥ÀÌŸ º£À̽º ²ø¾î¿À±â
¼¹ö »óÀÇ µ¥ÀÌŸ º£À̽º¸¦ ³ªÀÇ ÄÄÀÇ ÇÁ·Î±×·¥À¸·Î °¡Á® ¿Ã¶§ »ç¿ëÇÕ´Ï´Ù.
¼¹öÀÇ mysql mysql À» ½ÇÇà ÇÕ´Ï´Ù.
INSERT INTO db VALUES ('³ªÀÇ ÄÄ ¾ÆÀÌÇÇ','DBÀ̸§','»ç¿ëÀÚ','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
INSERT INTO user VALUES ('³ªÀÇ ÄÄ ¾ÆÀÌÇÇ '»ç¿ëÀÚ ','ºñ¹Ð¹øÈ£ ','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
±×¸®°í, À§ÀÇ Äù¸®¹®À» ÆíÁýÇؼ ½ÇÇà ÇÕ´Ï´Ù.
±×·Á¸é ³ªÀÇ ÄÄ¿¡ DB°¡ ¾ø¾îµµ ¼¹öÀÇ DB¸¦ °¡Á®¿Í¼ »ç¿ëÀ» ÇÒ¼ö°¡ ÀÖ½À´Ï´Ù.
»ç¿ë¹ý : create table Å×À̺íÀ̸§(º¯¼ö¸í º¯¼ö°ª, ......);
mysql> create table test (no int, name char(10), age int, tel char(15)); Query OK, 0 rows affected (0.00 sec) |
»ç¿ë¹ý : show tables;
mysql> show tables; +-----------------+ | Tables in test | +-----------------+ | test | +-----------------+ 2 rows in set (0.00 sec) mysql> show tables like 'ad_%'; +-------------------------+ | Tables_in_dbkedu (ad_%) | +-------------------------+ | ad_expose | | ad_img | | ad_redirect | | address | +-------------------------+ 4 rows in set (0.00 sec) |
mysql > insert into test values(1,'±è¿ëÀÏ','29','02-825-xxxx'); Query OK, 1 row affected (0.00 sec) |
mysql> insert into test (no,name) values ('7','³²»ó¿í'); Query OK,1 row affected (0.00 sec) |
select ¹®À» ÀÌ¿ëÇÑ´Ù.
mysql> select * from test; +------+--------+------+---------------+ | no | name | age | tel | +------+--------+------+---------------+ | 1 | ±è¿ëÀÏ | 29 | 02-825-xxxx | | 2 | ÀÌÁÖ»ó | 29 | 02-504-xxxx | | 3 | ÀÓÁ¤³² | 29 | 02-420-xxxx | | 4 | °Çà¸ð| 29 | 02-856-xxxx | | 5 | ÀÌÇö¿µ | 29 | 0342-718-xxxx | | 6 |À̺´È¯ | 12 |02-891-xxxx | | 7 | ³²»ó¿í | NULL | NULL | +------+--------+------+---------------+ 7 rows in set (0.00 sec) |
alter ¹®À» ÀÌ¿ëÇÑ´Ù.
mysql> alter table test add addr char(20); Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> desc test; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | no |int(11) | YES | | NULL | | | name | char(10) | YES | | NULL | | | age | int(11) | YES | | NULL | | | tel | char(15) | YES | | NULL | | | addr | char(20) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 5 rows in set (0.00 sec) |
alter ¹®À» ÀÌ¿ëÇÑ´Ù.
mysql> alter table test drop age; Query OK, 6 rows affected (0.01 sec) Records: 6 Duplicates: 0 Warnings: 0 |
alter ¹®À» ÀÌ¿ëÇÑ´Ù.
ALTER TABLE¿¡´Â ´ÙÀ½ ¼¼°¡Áö Çü½ÄÀÌ ÀÖ½À´Ï´Ù.
ALTER TABLE table_name ADD ( ... ) ;
ALTER TABLE table_name MODIFY( ... ) ;
ALTER TABLE table_name DROP PRIMARYKEY ;
ALTER TABLE t1 RENAME t2;
Å×À̺í À̸§À» t1¿¡¼ t2·Î ¹Ù²Þ
ALTER TABLE t2 CHANGE a a TINYINT NOT NULL, CHANGE b c CHAR(20);
Å×ÀÌºí ³»ÀÇ Ä÷³ ¼Ó¼ºÀ» ¹Ù²Þ
¿ø·¡ Ä÷³ a´Â INTEGER¿´´Âµ¥ TINYINT·Î,
Ä÷³b´Â CHAR(10)À̾ú´Âµ¥ Ä÷³À̸§Àº c·Î ÀÌŸ ŸÀÔÀº CHAR(20)À¸·Î ¼öÁ¤
ALTER TABLE t2 ADD d TIMESTAMP;
Å×À̺í t2¿¡ d¶ó´ÂÀ̸§À¸·Î TIMESTAMP ÇüÀ¸·Î Ãß°¡
ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);
»õ·Î¿î ÀÎÅؽº¸¦ »ý¼º Ä÷³ a¿¡ primary key »ý¼º.
ALTER TABLE test MODIFY (age NUMBER NULL );
NOT NULLÀ» NULL·Î ¹Ù²Ù±â
ALTER TABLE test MODIFY (age NUMBER NOT NULL );
NULLÀ» NOT NULL·Î ¹Ù²Ù±â
ALTER TABLEtest MODIFY (name VARCHAR2( 20 ) );
¿ÀÇ Æø ´ÃÀ̱â
ALTER TABLE test MODIFY (name VARCHAR2( 15 ) );
¿ÀÇ Æø ÁÙÀ̱â
ALTER TABLE t2 DROP COLUMN c;
Ä÷³ c¸¦ ¾ø¾Ö¹ö¸®°í
ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD INDEX (c);
t2 Å×ÀÌºí¿¡ c¶ó´Â Ä÷³À» »õ·Î »ý¼º½ÃÅ°´Âµ¥
INT (INTEGER) : Á¤¼ö
UNSIGNED : ¾ç¼ö
NOT NULL : ¾Æ¹«µ¥ÀÌŸ°¡ ¾øÀ¸¸é '0'
AUTO_INCREMENT : µ¥ÀÌŸ°¡ ÀÚµ¿ÀûÀ¸·Î ÀԷµÊ...
ADD INDEX (c) : Ä÷³ c¿¡ ´ëÇؼ ÀÎÅؽº »ý¼º
delete ¸¦ ÀÌ¿ëÇÑ´Ù.
mysql>delete from test where name='±è¿ëÀÏ'; Query OK, 1 row affected(0.00 sec) |
update¸¦ ÀÌ¿ëÇÑ´Ù.
mysql> update test set no=1,tel='333-4444' where name='³²»ó¿í'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 |
mysql> update test set addr='µ¿ÀÛ±¸' where no='1'; |
order¹®À» ÀÌ¿ë
mysql> select * from test order by no desc; +------+--------+------+---------------+--------+ | no | name | age | tel | addr | +------+--------+------+---------------+--------+ | 6 | À̺´È¯ |12 | 02-891-xxxx | NULL | | 5 | ÀÌÇö¿µ | 29 | 0342-718-xxxx | NULL | | 4 | °Çà¸ð | 29 | 02-856-xxxx | NULL | | 3 | ÀÓÁ¤³² | 29 | 02-420-xxxx | NULL | | 2| ÀÌÁÖ»ó | 29 | 02-504-xxxx | NULL | | 1 | ³²»ó¿í | NULL | 333-4444 | µ¿ÀÛ±¸ | +------+--------+------+---------------+--------+ 6 rows in set (0.01 sec) |
rand¹®À» ÀÌ¿ë
mysql> select * from test order by rand(); +------+--------+---------------+--------+------+------------+ | no | name | tel | addr | sex | birth | +------+--------+---------------+--------+------+------------+ | 5 | ÀÌÇö¿µ | 0342-718-xxxx | ºÐ´ç±¸ | m | 1971-05-05 | | 4 | °Çà¸ð | 02-856-xxxx | °ü¾Ç±¸ | f | 1971-04-04 | | 2 | ÀÌÁÖ»ó | 02-504-xxxx | °úõ½Ã | m | 1971-02-02 | | 1 | ³²»ó¿í | 333-4444 | µ¿ÀÛ±¸ | m | 1971-01-01 | | 6 | À̺´È¯ | 02-891-xxxx | ÇϾȵ¿ | m | 1980-06-06 | | 3 | ÀÓÁ¤³² | 02-420-xxxx | ¼ÛÆı¸ | f | 1971-03-03 | +------+--------+---------------+--------+------+------------+ 6 rows in set (0.01 sec) |
year¹® ÀÌ¿ë
mysql> select * from test order by year(birth) desc; +------+--------+---------------+--------+------+------------+ | no | name | tel | addr | sex | birth | +------+--------+---------------+--------+------+------------+ | 6 | À̺´È¯ | 02-891-xxxx | ÇϾȵ¿ | m | 1980-06-06 | | 2 | ÀÌÁÖ»ó | 02-504-xxxx | °úõ½Ã | m | 1971-02-02 | | 3 | ÀÓÁ¤³² | 02-420-xxxx | ¼ÛÆı¸ | f | 1971-03-03 | | 4 | °Çà¸ð | 02-856-xxxx | °ü¾Ç±¸ | f | 1971-04-04 | | 5 | ÀÌÇö¿µ | 0342-718-xxxx | ºÐ´ç±¸ | m | 1971-05-05 | | 1 | ³²»ó¿í | 333-4444 | µ¿ÀÛ±¸ | m | 1971-01-01 | +------+--------+---------------+--------+------+------------+ 6 rows in set (0.00 sec) |
year ¹® ÀÌ¿ë
mysql> select * from test order by year(birth) desc, no desc; +------+--------+---------------+--------+------+------------+ | no | name | tel | addr | sex | birth | +------+--------+---------------+--------+------+------------+ | 6 | À̺´È¯ | 02-891-xxxx | ÇϾȵ¿ | m | 1980-06-06 | | 5 | ÀÌÇö¿µ | 0342-718-xxxx | ºÐ´ç±¸ | m | 1971-05-05 | | 4 | °Çà¸ð | 02-856-xxxx | °ü¾Ç±¸ | f | 1971-04-04| | 3 | ÀÓÁ¤³² | 02-420-xxxx | ¼ÛÆı¸ | f | 1971-03-03 | | 2 | ÀÌÁÖ»ó | 02-504-xxxx | °úõ½Ã | m | 1971-02-02 | | 1 | ³²»ó¿í | 333-4444 | µ¿ÀÛ±¸ | m | 1971-01-01 | +------+--------+---------------+--------+------+------------+ |
where Àý ÀÌ¿ë
mysql> select count(*) from test where age >=20; +----------+ | count(*) | +----------+ | 4 | +----------+ 1 row in set (0.00 sec) |
null ¹® ÀÌ¿ë
mysql> select * from test where addr is not null; +------+--------+------+----------+--------+ | no | name | age | tel | addr | +------+--------+------+----------+--------+ | 1 | ³²»ó¿í | NULL | 333-4444 | µ¿ÀÛ±¸ | +------+--------+------+----------+--------+ 1 row in set (0.00 sec) |
null ¹® ÀÌ¿ë
mysql> select * from test whereaddr is null; +------+--------+------+---------------+------+ | no | name | age | tel | addr | +------+--------+------+---------------+------+ | 2 | ÀÌÁÖ»ó | 29 | 02-504-xxxx | NULL | | 3 | ÀÓÁ¤³² | 29| 02-420-xxxx | NULL | | 4 | °Çà¸ð | 29 | 02-856-xxxx | NULL | | 5 | ÀÌÇö¿µ | 29 | 0342-718-xxxx | NULL | | 6 | À̺´È¯ | 12| 02-891-xxxx | NULL | +------+--------+------+---------------+------+ 5 rows in set (0.01 sec) |
like ÀÌ¿ë
mysql> select * from test where name like '%ÀÌ%'; +------+--------+------+---------------+------+ | no | name | age | tel | addr | +------+--------+------+---------------+------+ | 2 | ÀÌÁÖ»ó | 29 | 02-504-xxxx | NULL | | 5 | ÀÌÇö¿µ | 29 | 0342-718-xxxx | NULL | | 6 | À̺´È¯ | 12 | 02-891-xxxx | NULL | +------+--------+------+---------------+------+ 3 rows in set (0.00 sec) |
µîÈ£, ºÎµîÈ£ ¸¦ ÀÌ¿ëÇؼ °Ë»ö
mysql> select * from test where age>=10 and age <=20; +------+--------+------+-------------+------+ | no | name | age | tel | addr | +------+--------+------+-------------+------+ | 6 | À̺´È¯ | 12 | 02-891-xxxx | NULL | +------+--------+------+-------------+------+ 1 row in set (0.00 sec) |
in ¹®À» ÀÌ¿ëÇؼ °Ë»ö
mysql> select * from test where addr in('µ¿ÀÛ±¸','°ü¾Ç±¸'); +------+--------+------+-------------+--------+ | no | name | age | tel | addr | +------+--------+------+-------------+--------+ | 4 | °Çà¸ð | 29 | 02-856-xxxx | °ü¾Ç±¸ | | 1 | ³²»ó¿í | NULL | 333-4444 | µ¿ÀÛ±¸ | +------+--------+------+-------------+--------+ 2 rows in set (0.00 sec) |
not in¹®À» ÀÌ¿ëÇؼ °Ë»ö
mysql> select * from test where addr not in('µ¿ÀÛ±¸','°ü¾Ç±¸'); +------+--------+------+---------------+--------+ | no | name | age | tel | addr | +------+--------+------+---------------+--------+ | 2 | ÀÌÁÖ»ó | 29 |02-504-xxxx | °úõ½Ã | | 3 | ÀÓÁ¤³² | 29 | 02-420-xxxx | ¼ÛÆı¸ | | 5 | ÀÌÇö¿µ | 29 | 0342-718-xxxx | ºÐ´ç±¸ | | 6 | À̺´È¯ | 12 | 02-891-xxxx | ÇϾȵ¿ | +------+--------+------+---------------+--------+ 4 rows in set (0.00 sec) |
ġȯ °Ë»ö
mysql> select * from cls_class as a, cls_member as b where b.g_code = 'h0001_3_2' and b.g_code=a.g_code; |
where Àý ÀÌ¿ë
mysql> SELECT COUNT(no) FROM brd_free WHERE date = '2000-02-17'; +-----------+ | COUNT(no) | +-----------+ | 1 | +-----------+ 1 row in set (0.03 sec) |
²À °æ·Î¸¦ ÁöÁ¤ÇؾßÇÔ
mysql> select * from test into outfile '/root/test.txt'; |
°á°ú
2 ÀÌÁÖ»ó 02-504-xxxx °úõ½Ã m 1971-02-02 3 ÀÓÁ¤³² 02-420-xxxx ¼ÛÆı¸ f 1971-03-03 4 °Çà¸ð 02-856-xxxx °ü¾Ç±¸ f 1971-04-04 5 ÀÌÇö¿µ 0342-718-xxxx ºÐ´ç±¸ m 1971-05-05 6 À̺´È¯ 02-891-xxxx ÇϾȵ¿ m 1980-06-06 1 ³²»ó¿í 333-4444 µ¿ÀÛ±¸ m 1971-01-01 |
terminated ¹®À» ¾´´Ù.
mysql> select * from test into outfile '/root/test.txt' fields terminated by '|'; |
°á°ú
2|ÀÌÁÖ»ó|02-504-xxxx|°úõ½Ã|m|1971-02-02 3|ÀÓÁ¤³²|02-420-xxxx|¼ÛÆı¸|f|1971-03-03 4|°Çà¸ð|02-856-xxxx|°ü¾Ç±¸|f|1971-04-04 5|ÀÌÇö¿µ|0342-718-xxxx|ºÐ´ç±¸|m|1971-05-05 6|À̺´È¯|02-891-xxxx|ÇϾȵ¿|m|1980-06-06 1|³²»ó¿í|333-4444|µ¿ÀÛ±¸|m|1971-01-01 |
load ¸í·É¾î¸¦ ¾´´Ù.
mysql> load data infile '/root/test.txt' replace into table test fields terminated by '|'; Query OK, 6 rows affected (0.02 sec) Records: 6 Deleted: 0 Skipped: 0 Warnings: 0 |
°á°ú
mysql> select * from test; +------+--------+---------------+--------+------+------------+ | no | name | tel | addr | sex | birth | +------+--------+---------------+--------+------+------------+ | 2 |ÀÌÁÖ»ó | 02-504-xxxx | °úõ½Ã | m | 1971-02-02 | | 3 | ÀÓÁ¤³² | 02-420-xxxx | ¼ÛÆı¸ | f | 1971-03-03 | | 4 | °Çà¸ð | 02-856-xxxx | °ü¾Ç±¸| f | 1971-04-04 | | 5 | ÀÌÇö¿µ | 0342-718-xxxx | ºÐ´ç±¸ | m | 1971-05-05 | | 6 | À̺´È¯ | 02-891-xxxx | ÇϾȵ¿ | m | 1980-06-06 | | 1 |³²»ó¿í | 333-4444 | µ¿ÀÛ±¸ | m | 1971-01-01 | +------+--------+---------------+--------+------+------------+ |
* SUM (Column_name) : ƯÁ¤ Ä÷³¿¡¼ Á¶°Ç¿¡ ¸¸Á·ÇÏ´Â ¸ðµç¿ÀÇ °ªÀ» ´Ù´õÇÔ
* AVG (Column_name) : Æò±Õ°ª ÃßÃâ
* MAX (Column_name) : ÃÖ´ë°ªÃßÃâ
* MIN (Column_name) : ÃÖ¼Ò°ª ÃßÃâ
* COUNT (Column_name): ¿ÀǼö¸¦ ÃßÃâ
sum ¹® »ç¿ëÇϱâ
mysql> select sum(age) from test; +----------+ | sum(age) | +----------+ | 128 | +----------+ 1 row in set (0.00 sec) |
/±â »ç¿ëÇϱâ
mysql> select no, age, (no+age), (no+age)/2 from test; +------+------+----------+------------+ | no | age | (no+age) | (no+age)/2 | +------+------+----------+------------+ | 2 | 29 | 31 | 15.50 | | 3 | 29 | 32 | 16.00| | 4 | 29 | 33 | 16.50 | |5 | 29 | 34 | 17.00 | | 6 | 12 | 18 | 9.00 | | 1 | NULL | NULL | NULL | +------+------+----------+------------+ 6 rows in set (0.00 sec) |
yser ¹® »ç¿ëÇϱâ
mysql> select *, year(curdate())-year(birth)+1 as age from test; +------+--------+---------------+--------+------+------------+------+ | no | name | tel | addr | sex | birth | age | +------+--------+---------------+--------+------+------------+------+ | 2 | ÀÌÁÖ»ó | 02-504-xxxx | °úõ½Ã | m | 1971-02-02 |29 | | 3 | ÀÓÁ¤³² | 02-420-xxxx | ¼ÛÆı¸ |f | 1971-03-03 | 29 | | 4 | °Çà¸ð | 02-856-xxxx | °ü¾Ç±¸ | f | 1971-04-04 | 29 | | 5 |ÀÌÇö¿µ | 0342-718-xxxx | ºÐ´ç±¸ | m | 1971-05-05 | 29 | | 6 | À̺´È¯ | 02-891-xxxx | ÇϾȵ¿ | m | 1980-06-06 | 20 | | 1 | ³²»ó¿í | 333-4444 | µ¿ÀÛ±¸ | m | 1971-01-01 | 29 | +------+--------+---------------+--------+------+------------+------+ 6 rows in set (0.01 sec) |
unix_timestamp¹®À» »ç¿ëÇÑ´Ù.
mysql> select unix_timestamp(curdate()); +---------------------------+ | unix_timestamp(curdate()) | +---------------------------+ | 934210800 | +---------------------------+ 1 row in set (0.05 sec) |
A¶ó´Â ¼¹ö¿¡ DB°¡ ÀÖ°í, 210.180.56.204¿¡¼ ÇÁ·Î±×·¥À» µ¹¸±¶§
A¶ó´Â ¼¿¡ÀÖ´Â mysql mysql ¿¡ ´ÙÀ½°ú °°ÀÌ ÀÔ·ÂÀ» ÇÕ´Ï´Ù.
INSERT INTO db VALUES ('210.180.56.204','dbname','user','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
INSERT INTO host VALUES ('210.180.56.204','dbname','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
INSERT INTO user VALUES ('210.180.56.204','user','passwd','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
210.180.56.204 ÀÇ ÄÄ¿¡¼ ÇÁ·Î±×·¥À» ÇÒ¶§ ´ÙÀ½°ú °°ÀÌ ¼³Á¤À» ÇØÁÖ¸é ¿ø°Ý DB ¸¦ »ç¿ëÇÒ¼ö ÀÖ½À´Ï´Ù.
$db_server ="servername"; // DB ¼¹ö ÁÖ¼Ò
$db_user = "root"; // DB »ç¿ëÀÚ
$db_name = ""; // DB À̸§
$db_pass = ""; // DB ¾ÏÈ£