Create a mysql database, tables and insert data

Q. How do I create a MySQL database and tables?
A. If you want to create a database and set up tables for the same use the following two sql commands:
=> CREATE DATABASE - create the database
=> CREATE TABLE - create the table
=> INSERT - To add/insert data to table

Procedure for creating a database and a sample table

Login as the mysql root user to create database:
$ mysql -u root -p
Output:
mysql>
Add a database called books
mysql> CREATE DATABASE s; 
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| s |
| test |
+--------------------+
4 rows in set (0.00 sec)


Now database is created. Use a database with use command:
mysql> USE s;
Now create a table called authors with name, email and id:
mysql> CREATE TABLE customer(id INT, name VARCHAR(20), status INT, city VARCHAR(20));
Display your table name just created:
mysql> SHOW TABLES;
Output:
+-----------------+
| Tables_in_books |
+-----------------+
| customer |
+-----------------+
1 row in set (0.00 sec)
Now add a data / row to table books using INSERT statement:
mysql> INSERT INTO customer(id,name,status,city) VALUES(1,"Smith","20","London");
Output:
Query OK, 1 row affected (0.00 sec)
Add few more rows:
mysql> INSERT INTO costumer(id,name,ststus,city) VALUES(2,"Jones","10","Paris");
mysql> INSERT INTO customer(id,name,status,city) VALUES(3,"Black","30","Paris");

mysql> INSERT INTO customer(id,name,status,city) VALUES(4,"Clark","20","London");
mysql> INSERT INTO customer(id,name,status,city) VALUES(5,"Adams","30","Athens");


Now display all rows:
mysql> SELECT * FROM costumer;
Output:
+------+--------------+--------+----------------------+
| id | name | status | city |
+------+--------------+--------+----------------------+
| 1 | Smith | 20 | London |
| 2 | Jones | 10 | Paris |
| 3 | Black | 30 | Paris |
| 4 | Clark | 20 | London |
| 5 | Adams | 30 | Athens |
+------+--------------+--------+----------------------+
6 rows in set (0.00 sec) 
 
mysql> select * from costumer where city='Paris';
+------+-------+--------+-------+
| id | name | status | city |
+------+-------+--------+-------+
| 2 | Jones | 10 | Paris |
| 3 | Black | 30 | Paris |
+------+-------+--------+-------+
2 rows in set (0.00 sec)

mysql> select name from costumer where city='Paris';
+-------+
| name |
+-------+
| Jones |
| Black |
+-------+
2 rows in set (0.00 sec)
 
SHARE

About Lasha Gogua

    Blogger Comment
    Facebook Comment

0 comments:

Post a Comment