Solved

insert into mysql database

Posted on 2004-04-20
3
642 Views
Last Modified: 2008-03-10
Can anyone please tell me how to insert the following into the test database of mysql
i currently have it in a text document

Create table orderForm
(  orderNumber Varchar(22),
ProductName Varchar(22),
Quantity Integer,
Price Varchar(22),
Comment Varchar(220),
status Varchar(22),
primary key (orderNumber)
);

create table staff
(
 username Varchar(22),
 password Varchar(22),
 primary key(username)
);

create table supervisor
(
 username Varchar(22),
 password Varchar(22),
 primary key(username)
);

create table financeDepartment
(
 username Varchar(22),
 password Varchar(22),
 primary key(username)
);


create table product
(
 name Varchar(22),
 price Varchar(22),
 quantity Integer,
 budget double,
 primary key(name)
);

insert into staff values('staff1','staff1');
insert into supervisor values('supervisor1','supervisor1');
insert into financeDepartment values('finance1','finance1');
insert into product values('test1','12.30','12','123,213');
0
Comment
Question by:aka_damo
  • 2
3 Comments
 

Expert Comment

by:S1monP
ID: 10873670
1. Log in to MySQL
2. Type "Use test" (assuming test is the name of your database)
3. Assuming your source code is in the bin directory of MySql and your text file is "Tables.sql"
    Type "source Tables.sql;" and press return.

Simple eh?
As a note, if you run the source code above you will get an error telling you that a primary key must be NOT NULL, all you need to do is change the tables as below (and get into the habit of dropping existing tables, it saves a load of hassle)

DROP TABLE IF EXISTS orderform;
DROP TABLE IF EXISTS staff;
DROP TABLE IF EXISTS supervisor;
DROP TABLE IF EXISTS financeDepartment;
DROP TABLE IF EXISTS product;

Create table orderForm
(  orderNumber Varchar(22)NOT NULL,
ProductName Varchar(22),
Quantity Integer,
Price Varchar(22),
Comment Varchar(220),
status Varchar(22),
primary key (orderNumber)
);

create table staff
(
 username Varchar(22) NOT NULL,
 password Varchar(22),
 primary key(username)
);

create table supervisor
(
 username Varchar(22)NOT NULL,
 password Varchar(22),
 primary key(username)
);

create table financeDepartment
(
 username Varchar(22)NOT NULL,
 password Varchar(22),
 primary key(username)
);

create table product
(
 name Varchar(22)NOT NULL,
 price Varchar(22),
 quantity Integer,
 budget double,
 primary key(name)
);

Regards
Simon
0
 

Author Comment

by:aka_damo
ID: 10873768
sorry Simon do i just save the text file as database.sql and place it in C:\mysql\bin ?
I did this and got errors
Sorry im a very novice user
thanx
aka_damo
0
 

Accepted Solution

by:
S1monP earned 80 total points
ID: 10874036
Try copying and pasting the modified text below from my answer above and save as c:\mysql\bin\test1.txt then log into the database and type: source test1.txt
I've assumed you've installed to C: as above and that you are logged in to a database, ie use test..
Just tried this on my machine and it works fine

DROP TABLE IF EXISTS orderform;
DROP TABLE IF EXISTS staff;
DROP TABLE IF EXISTS supervisor;
DROP TABLE IF EXISTS financeDepartment;
DROP TABLE IF EXISTS product;

Create table orderForm
(  orderNumber Varchar(22) NOT NULL,
ProductName Varchar(22),
Quantity Integer,
Price Varchar(22),
Comment Varchar(220),
status Varchar(22),
primary key (orderNumber)
);

create table staff
(
 username Varchar(22) NOT NULL,
 password Varchar(22),
 primary key(username)
);

create table supervisor
(
 username Varchar(22) NOT NULL,
 password Varchar(22),
 primary key(username)
);

create table financeDepartment
(
 username Varchar(22) NOT NULL,
 password Varchar(22),
 primary key(username)
);

create table product
(
 name Varchar(22) NOT NULL,
 price Varchar(22),
 quantity Integer,
 budget double,
 primary key(name)
);


Simon
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now