How to import, change and format data from CSV file.
Table definition:
|
1 2 3 4 5 6 7 8 9 10 11 |
<pre><code>CREATE TABLE emp( empno INT(11) NOT NULL, ename VARCHAR(10) DEFAULT NULL, job VARCHAR(9) DEFAULT NULL, mgr DECIMAL(4, 0) DEFAULT NULL, hiredate DATE DEFAULT NULL, sal DECIMAL(7, 2) DEFAULT NULL, comm DECIMAL(7, 2) DEFAULT NULL, deptno INT(11) DEFAULT NULL, PRIMARY KEY (empno) ); |
|
1 |
<br /> |
|
1 |
<span style=\"white-space: normal;\">We have next CSV-file \'emp2.csv\' with custom formatted date (\'DD-MM-YYYY\'):</span> |
|
1 2 3 4 5 6 |
<pre><code>\"empno\";\"ename\";\"job\";\"mgr\";\"hiredate\";\"sal\";\"comm\";\"deptno\" 7369;\"SMITH\";\"CLERK\";7902;17-12-1980;800,0;null;20 7499;\"ALLEN\";\"SALESMAN\";7698;20-02-1981;1600,0;300,0;30 7521;\"WARD\";\"SALESMAN\";7698;22-02-1981;1250,0;500,0;30 7566;\"JONES\";\"MANAGER\";7839;02-04-1981;2975,0;null;20 7654;\"MARTIN\";\"SALESMAN\";7698;28-09-1981;1250,0;1400,0;30 |
And suppose, we have to increase a salary (for example: salary = salary * 2:
|
1 2 3 4 5 6 7 8 9 10 11 |
<pre><code>CREATE TABLE emp( empno INT(11) NOT NULL, ename VARCHAR(10) DEFAULT NULL, job VARCHAR(9) DEFAULT NULL, mgr DECIMAL(4, 0) DEFAULT NULL, hiredate DATE DEFAULT NULL, sal DECIMAL(7, 2) DEFAULT NULL, comm DECIMAL(7, 2) DEFAULT NULL, deptno INT(11) DEFAULT NULL, PRIMARY KEY (empno) ); |
Check result:
|
1 2 3 4 5 6 7 8 9 10 |
<pre><code>SELECT * FROM emp; +-------+--------+----------+------+------------+---------+---------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+----------+------+------------+---------+---------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 1600.00 | 0.00 | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 3200.00 | 300.00 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 2500.00 | 500.00 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 5950.00 | 0.00 | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 2500.00 | 1400.00 | 30 | +-------+--------+----------+------+------------+---------+---------+--------+ |