Create MySQL Database and tables
on the Shell
Steps for creating MySQL database and tables
from the 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(15) 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.
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 the missing values you can use NULL values.
To represent these in your text file, use \N (backslash, capital-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;
|
