MySQL Web Hosting
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;
Web Promotion FAQs
