MySQL: 2 Creating Table

posted Aug 7, 2011, 3:43 AM by Neil Mathew [ updated Aug 8, 2011, 9:38 AM ]

Once the database has been created, we can create the table. A shown below:

mysql> create database School
    -> ;
Query OK, 1 row affected (0.01 sec)

mysql> use school;
Database changed


mysql> create table emp
    -> ( Empno Integer,
    -> Ename varchar(20),
    -> Job char(10),
    -> Mgr Integer,
    -> Hiredate Date,
    -> Sal Decimal,
    -> Comm Decimal,
    -> DeptNo Integer
    -> );
Query OK, 0 rows affected (0.13 sec)

mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| Empno    | int(11)       | YES  |     | NULL    |       |
| Ename    | varchar(20)   | YES  |     | NULL    |       |
| Job      | char(10)      | YES  |     | NULL    |       |
| Mgr      | int(11)       | YES  |     | NULL    |       |
| Hiredate | date          | YES  |     | NULL    |       |
| Sal      | decimal(10,0) | YES  |     | NULL    |       |
| Comm     | decimal(10,0) | YES  |     | NULL    |       |
| DeptNo   | int(11)       | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+
8 rows in set (0.01 sec)

However, what concerned me was the difference in FORMATS. The Data Format in particular.

The MySQL date format is actually YYYY-MM-DD while
Oracle's is DD-MM-YYYY .

Also, unlike Oracle, the datatypes are different.

INT and DECIMAL would not be available in Oracle just as NUMBER is not available on MySQL.

Below is the table list copied from notepad. (MySQL format). The Tabs have been accordingly put.

7369	Smith	Clerk		7902	1980-12-17	800	NULL	20

7499	Allen	Salesman	7698	1981-02-20	1600	300	30					
7521	Ward	Salesman	7698	1981-02-22	1250	500	30					
7566	Jones	Manager		7839	1981-04-02	2975	NULL	20					
7654	Martin	Salesman	7698	1981-09-28	1250	1400	30					
7698	Blake	Manager		7839	1981-05-01	2850	NULL	30					
7782	Clark	Manager		7839	1981-06-09	2450	NULL	10					
7788	Scott	Analyst		7566	1982-12-09	3000	NULL	20					
7839	King	President	NULL	1981-11-17	5000	NULL	10					
7844	Turner	Salesman	7698	1981-09-08	1500	0	30					
7876	Adams	Clerk		7788	1989-12-03	950	NULL	30					
7902	Ford	Analyst		7566	1981-12-04	3000	NULL	20

Manipulating the TABS to commas, and adding inverted commas to the String and Date entries, I've got one easy INSERT line.

mysql> INSERT INTO EMP
    -> VALUES (7499,'Allen','Salesman',7698,'1981-02-20',1600,300,30);
Query OK, 1 row affected (0.14 sec)

mysql> select * from emp;
+-------+-------+----------+------+------------+------+------+--------+
| Empno | Ename | Job      | Mgr  | Hiredate   | Sal  | Comm | DeptNo |
+-------+-------+----------+------+------------+------+------+--------+
|  7499 | Allen | Salesman | 7698 | 1981-02-20 | 1600 |  300 |     30 |
+-------+-------+----------+------+------------+------+------+--------+
1 row in set (0.05 sec)

Success. Now the rest:

mysql> INSERT INTO EMP VALUES(7521,'Ward','Salesman',7698,'1981-02-22',1250,500,
30);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO EMP VALUES(7566,'Jones','Manager',7839,'1981-04-02',2975,NULL
,20);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO EMP VALUES(7654,'Martin','Salesman',7698,'1981-09-28',1250,14
00,30);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO EMP VALUES(7698,'Blake','Manager',7839,'1981-05-01',2850,NULL
,30);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO EMP VALUES(7782,'Clark','Manager',7839,'1981-06-09',2450,NULL
,10);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO EMP VALUES(7788,'Scott','Analyst',7566,'1982-12-09',3000,NULL
,20);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO EMP VALUES(7839,'King','President',NULL,'1981-11-17',5000,NUL
L,10);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO EMP VALUES(7844,'Turner','Salesman',7698,'1981-09-08',1500,0,30);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO EMP VALUES(7876,'Adams','Clerk',7788,'1989-12-03',950,NULL,30
);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO EMP VALUES(7902,'Ford','Analyst',7566,'1981-12-04',3000,NULL,
20);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO EMP VALUES(7369,'Smith','Clerk',7902,'1980-12-17',800,NULL,20
);
Query OK, 1 row affected (0.01 sec)


mysql> select * from emp;
+-------+--------+-----------+------+------------+------+------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL  | COMM | DEPTNO |
+-------+--------+-----------+------+------------+------+------+--------+
|  7499 | Allen  | Salesman  | 7698 | 1981-02-20 | 1600 |  300 |     30 |
|  7521 | Ward   | Salesman  | 7698 | 1981-02-22 | 1250 |  500 |     30 |
|  7566 | Jones  | Manager   | 7839 | 1981-04-02 | 2975 | NULL |     20 |
|  7654 | Martin | Salesman  | 7698 | 1981-09-28 | 1250 | 1400 |     30 |
|  7698 | Blake  | Manager   | 7839 | 1981-05-01 | 2850 | NULL |     30 |
|  7782 | Clark  | Manager   | 7839 | 1981-06-09 | 2450 | NULL |     10 |
|  7788 | Scott  | Analyst   | 7566 | 1982-12-09 | 3000 | NULL |     20 |
|  7839 | King   | President | NULL | 1981-11-17 | 5000 | NULL |     10 |
|  7844 | Turner | Salesman  | 7698 | 1981-09-08 | 1500 |    0 |     30 |
|  7876 | Adams  | Clerk     | 7788 | 1989-12-03 |  950 | NULL |     30 |
|  7902 | Ford   | Analyst   | 7566 | 1981-12-04 | 3000 | NULL |     20 |
|  7369 | Smith  | Clerk     | 7902 | 1980-12-17 |  800 | NULL |     20 |
+-------+--------+-----------+------+------------+------+------+--------+
12 rows in set (0.00 sec)

INSTANT COPY & PASTE FOR MYSQL USERS:

TABLE : EMP

Create TABLE code:

CREATE TABLE EMP ( EMPNO INTEGER, ENAME VARCHAR(20), JOB CHAR(10), MGR INTEGER, HIREDATE DATE, SAL DECIMAL, COMM DECIMAL, DEPTNO INTEGER );

Values to insert:

INSERT INTO EMP VALUES(7369,'Smith','Clerk',7902,'1980-12-17',800,NULL,20);
INSERT INTO EMP VALUES(7499,'Allen','Salesman',7698,'1981-02-20',1600,300,30);
INSERT INTO EMP VALUES(7521,'Ward','Salesman',7698,'1981-02-22',1250,500,30);
INSERT INTO EMP VALUES(7566,'Jones','Manager',7839,'1981-04-02',2975,NULL,20);
INSERT INTO EMP VALUES(7654,'Martin','Salesman',7698,'1981-09-28',1250,1400,30);
INSERT INTO EMP VALUES(7698,'Blake','Manager',7839,'1981-05-01',2850,NULL,30);
INSERT INTO EMP VALUES(7782,'Clark','Manager',7839,'1981-06-09',2450,NULL,10);
INSERT INTO EMP VALUES(7788,'Scott','Analyst',7566,'1982-12-09',3000,NULL,20);
INSERT INTO EMP VALUES(7839,'King','President',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO EMP VALUES(7844,'Turner','Salesman',7698,'1981-09-08',1500,0,30);
INSERT INTO EMP VALUES(7876,'Adams','Clerk',7788,'1989-12-03',950,NULL,30);
INSERT INTO EMP VALUES(7902,'Ford','Analyst',7566,'1981-12-04',3000,NULL,20);

TABLE : DEPT

Create TABLE code:

CREATE TABLE DEPT ( DEPTNO INTEGER, DNAME varchar(20), LOC varchar(20) );

Values to insert:

INSERT INTO DEPT VALUES(10,'Accounting','New York');

INSERT INTO DEPT VALUES(20,'Research','Dallas');

INSERT INTO DEPT VALUES(30,'Sales','Chicago');

INSERT INTO DEPT VALUES(40,'Operations','Boston');

Comments