Create MySQL database and tables on shell

Here are steps explained below to create mysql database and tables from shell

1)Create database from shell

Use the SHOW statement to find out what databases currently exist on the server:
mysql> SHOW DATABASES;

Create database from shell as per following

mysql> create datbase testdb;

mysql> USE testdb;
Database changed

2) Grant privileges of database to specific user

mysql> GRANT ALL ON testdb.* TO user@’localhost’; /*here you can specify username to which you want assign privileges */

Connect database testdb as per following

shell> mysql -h host -u user -p testdb

host = localhost
u= username
p= password

3)Check for tables in database testdb

mysql> SHOW TABLES;
Empty set (0.00 sec)

4)Use a CREATE TABLE statement to specify the layout of your table:

mysql> CREATE TABLE testtab (name VARCHAR(20), age int(3), sex CHAR(1), birth DATE);

varchar,int, char are datatype used in mysql to store data in specific rows and columns

5)To verify that your table was created the way you expected, use a DESCRIBE statement:

mysql> DESCRIBE testtab;
+———+————-+——+—– +———+——-+
| Field | Type | Null | Key |Default | Extra |
+———+————-+——+—– +———+——-+
| name | varchar(20) | YES | NULL | | |
| Age | int(3) | YES | NULL | |
| sex | char(1) | YES | NULL |
| birth | date | YES | NULL | | |
+—— +————- +——+—– +———+——-+
6)
Loading Data into a Table
After creating your table, you need to populate it. The LOAD DATA and INSERT statements are useful for this.

Suppose that your pet records can be described as shown here. (Observe that MySQL expects dates in ‘YYYY-MM-DD’ format; this

may be different from what you are used to.)

name owner species sex birth death
Alan m 1993-02-04
Jason m 1994-03-17
Nancy f 1989-05-13
Steven m 1990-08-27

7)Load data in rows

Create a text file testtab.txt containing one record per line, with values separated by tabs, and given in the order in which

the columns were listed in the CREATE TABLE statement. For missing values (such as unknown sexes or death dates for animals

that are still living), you can use NULL values. To represent these in your text file, use \N (backslash, capital-N). For

example, the record for Steven m 1990-08-27 would look like this (where the whitespace between values is a single tab

character):

Steven m \n 1990-08-27 \n

To load the text file pet.txt into the pet table, use this command:

mysql> LOAD DATA LOCAL INFILE ‘/path/testtab.txt’ INTO TABLE testtab;