Hello! Hola! Hej!

Create TestTable

USE TestData
GO
CREATE TABLE CSVTest
(ID INT,
 FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME)
GO

Create CSV file in drive C: with name csvtest.txt with following content. The location of the file is C:\csvtest.txt

1,James,Smith,19750101

2,Meggie,Smith,19790122

3,Robert,Smith,20071101

4,Alex,Smith,20040202

Now run following script to load all the data from CSV to database table. If there is any error in any row it will be not inserted but other rows will be inserted.

BULK
INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
FROM CSVTest
GO
--Drop the table to clean up database.
SELECT *
FROM CSVTest
GO

 
http://bullpeng.tistory.com/archive/20110711

CREATE  TABLE `mgsdb`.`mgsuser` (

  `mgs_userid` VARCHAR(10) NOT NULL ,
   `mgs_username` VARCHAR(45) NULL ,
   `mgs_passwd` VARCHAR(45) NULL ,
   `mgs_belongto` VARCHAR(45) NULL ,
   `mgs_userhp` VARCHAR(45) NULL ,
   `mgs_usertel` VARCHAR(45) NULL ,
   `mgs_useremail` VARCHAR(45) NULL ,
   `mgs_right` VARCHAR(45) NULL ,
   `mgs_date` DATETIME,
 PRIMARY KEY (`mgs_userid`) );

| 간단한 Table 만들기

Enter password: ****

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 17

Server version: 5.5.17 MySQL Community Server (GPL)

 
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

> Database 생성 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database mgsdb;

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mgsdb              |

| mysql              |

| performance_schema |

| sakila             |

| test               |

| world              |

+--------------------+

7 rows in set (0.00 sec)

> Table 생성 / 생성된 Table 보기  

mysql> use mgsdb;

Database changed

mysql> create table mgsdb;

mysql> show tables;

+-----------------+

| Tables_in_mgsdb |

+-----------------+

| mgsuser         |

+-----------------+

1 row in set (0.00 sec)

 

mysql> desc mgsuser;

+---------------+-------------+------+-----+---------+-------+

| Field         | Type        | Null | Key | Default | Extra |

+---------------+-------------+------+-----+---------+-------+

| mgs_index     | int(11)     | YES  |     | NULL    |       |

| mgs_userid    | varchar(10) | NO   | PRI | NULL    |       |

| mgs_username  | varchar(10) | YES  |     | NULL    |       |

| mgs_passwd    | varchar(10) | YES  |     | NULL    |       |

| mgs_belongto  | varchar(30) | YES  |     | NULL    |       |

| mgs_depart    | varchar(30) | YES  |     | NULL    |       |

| mgs_userhp    | varchar(30) | YES  |     | NULL    |       |

| mgs_usertel   | varchar(30) | YES  |     | NULL    |       |

| mgs_useremail | varchar(30) | YES  |     | NULL    |       |

+---------------+-------------+------+-----+---------+-------+

9 rows in set (0.04 sec)

 

mysql>

* root의 계정을 외부접속 가능하게 바꾸기

mysql> grant all privileges on *.* to ‘root’@'%’ identified by ‘root의 패스워드’;

mysql> flush privileges;

root 계정의 host필드에 %가 등록되어있는지 확인

mysql> select host, user, password from user; (이건 해도되고 안해도 되고)

 %기호 : 어디서든 접속 가능.

 Illegal operation on empty result set.
Unknown column 'chkpasswd' in 'field list'

컬럼명 중 'chkpasswd'라는 필드가 없다는 mysql 명렁어 에러!
디비 column과 sql문에서의 접근하는 변수명이 같은지 확인해보기!