How to
Create a MySQL Database, Tables and Insert Data
in Linux, MySQL, UNIX
How do I create a MySQL database, tables, and insert (store) data into newly created tables?
MySQL is a free and open source database management system. You need to use sql commands to create database. You also need to login as mysql root user account. To create a database and set up tables for the same use the following sql commands:
Tutorial details
Difficulty Easy (rss)
Root privileges No
Requirements mysql
Estimated completion time 10m
CREATE DATABASE - create the database. To use this statement, you need the CREATE privilege for the database.
CREATE TABLE - create the table. You must have the CREATE privilege for the table.
INSERT - To add/insert data to table i.e. inserts new rows into an existing table.
Procedure for creating a database and a sample table
Login as the mysql root user to create database:
$ mysql -u root -p
Sample outputs:
mysql>
Add a database called books, enter:
mysql> CREATE DATABASE books;
Now, database is created. Use a database with use command, type:
mysql> USE books;
Next, create a table called authors with name, email and id as fields:
mysql> CREATE TABLE authors (id INT, name VARCHAR(20), email VARCHAR(20));
To display your tables in books database, enter:
mysql> SHOW TABLES;
Sample outputs:
+-----------------+
| Tables_in_books |
+-----------------+
| authors |
+-----------------+
1 row in set (0.00 sec)
Finally, add a data i.e. row to table books using INSERT statement, run:
mysql> INSERT INTO authors (id,name,email) VALUES(1,"Vivek","xuz@abc.com");
Sample outputs:
Query OK, 1 row affected (0.00 sec)
Try to add few more rows to your table:
mysql> INSERT INTO authors (id,name,email) VALUES(2,"Priya","p@gmail.com");
mysql> INSERT INTO authors (id,name,email) VALUES(3,"Tom","tom@yahoo.com");
To display all rows i.e. data stored in authors table, enter:
mysql> SELECT * FROM authors;
Sample outputs:
+------+-------+---------------+
| id | name | email |
+------+-------+---------------+
| 1 | Vivek | xuz@abc.com |
| 2 | Priya | p@gmail.com |
| 3 | Tom | tom@yahoo.com |
+------+-------+---------------+
3 rows in set (0.00 sec)
Now, you know how to create a database and a table. For further information please see MySQL data types and official documentation. CREATE INDEX Syntax
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[index_type]
ON tbl_name (index_col_name,...)
[index_type]
index_col_name:
col_name [(length)] [ASC | DESC]
index_type:
Import MySQL Dumpfile, SQL Datafile Into My Database
in Linux, MySQL, RedHat and Friends
How can I import a MySQL dumpfile into my database? I'm using CentOS Linux 5 server. My old hosting provider gave me a data.sql file and I do have access to my Unix / Linux server via ssh. So How do I restore my data using command line over the ssh session?
You can easily restore or import MySQL data with the mysql command itself. First you need to login to your system using ssh or putty client.
Step #1: Upload File To MySQL Server
You can upload data.sql file using the sftp or scp command, enter:
$ scp data.sql vivek@example.cyberciti.biz:/home/vivek
The data.sql file will be uploaded to /home/vivek directory. Avoid using /tmp or Apache document directory such as /var/www/html as anyone can see your data on the remote server.
Step #2: Login To Remote Server
Type the following command at the shell prompt:
$ ssh loginname@example.cyberciti.biz
Replace example.cyberciti.biz with actual server name or an IP address.
Step#3: Import Datafile
Type the following command to import sql data file:
$ mysql -u username -p -h localhost DATA-BASE-NAME < data.sql
In this example, import 'data.sql' file into 'blog' database using vivek as username:
$ mysql -u vivek -p -h localhost blog < data.sql
If you have a dedicated database server, replace localhost hostname with with actual server name or IP address as follows:
$ mysql -u username -p -h 202.54.1.10 databasename < data.sql
OR use hostname such as mysql.cyberciti.biz
$ mysql -u username -p -h mysql.cyberciti.biz database-name < data.sql
If you do not know the database name or database name is included in sql dump you can try out something as follows:
$ mysql -u username -p -h 202.54.1.10 < data.sql
A Note About Creating A New Database and Importing Data
In this example create a mysql database called foo and import data from bar.sql.gz as follows:
mysql -u root -p -h localhost
Sample outputs:
mysql> create database foo;
mysql> exit;
Import bar.sql.gz:
gunzip bar.sql.gz
ls -l
mysql -u root -p -h localhost foo
You can also create a username and password for foo database using the following syntax:
mysql -u root -p -h localhost
Sample outputs:
mysql> GRANT ALL ON foo.* TO NEW-USERNAME-HERE@localhost IDENTIFIED BY 'YOUR-PASSWORD-HERE';
### allow access from 192.168.1.5 too ##
mysql> GRANT ALL ON foo.* TO NEW-USERNAME-HERE@192.168.1.5 IDENTIFIED BY 'YOUR-PASSWORD-HERE';
mysql> quit;
To Download usefull and hottest Softwares and Games for your computer (PC) CLICK HERE
Today Quotes
".SOME ARE are born to rule. While others are born to serve,AND YOU must serve to RULE."
