Solved

insert into mysql database

Posted on 2004-04-20
3
688 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article describes some very basic things about SQL Server filegroups.
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
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…

749 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