Creating and Using a Database

Use the SHOW statement to find out what databases currently exist on the server:

mysql> SHOW DATABASES;

Note: A command normally consists of a SQL statement followed by a semicolon. There are some exceptions where a semicolon is not needed. When you issue a command, mysql sends it to the server for execution and displays the results, then prints another mysql> to indicate that it is ready for another command.

To create a new database, use the following command:

mysql> CREATE DATABASE database_name;

Note:while you're free to type your SQL commands in upper or lower case, a MySQL server running on a UNIX-based system will be case-sensitive when it comes to database and table names, as these correspond to directories and files in the MySQL data directory. Otherwise, MySQL is completely case-insensitive, but for one exception: table, column, and other names must be spelled exactly the same when they're used more than once in the same command.

Creating a database does not select it for use; you must do that explicitly. To make database_name the current database, use this command:

mysql> USE database_name
Database changed

Your database needs to be created only once, but you must select it for use each time you begin a mysql session. You can do this by issuing a USE statement as shown above.

Creating a Table

The basic form of the command is as follows:

mysql> CREATE TABLE table_name (
    -> column_1_name column_1_type column_1_details,
    -> column_2_name column_2_type column_2_details,
    -> ...
    -> );

Example:

mysql> CREATE TABLE StudentInfo(
    -> ID INT NOT NULL PRIMARY KEY,
    -> First_nameTEXT,
    -> Last_name TEXT NOT NULL
    -> );

The first line says that we want to create a new table called StudentInfo.
The second line says that we want a column called ID that will contain an integer ( INT ), that is, a whole number. The rest of this line deals with special details for this column. First, this column is not allowed to be left blank (NOT NULL ). Next, this column is to act as a unique identifier for the entries in this table, so all values in this column must be unique (PRIMARY KEY ).
The third line says that we want a column called First_name , which will contain text ( TEXT).
The fourth line defines our last column, called Last_name, which will contain data of type TEXT, and which cannot be left blank ( NOT NULL).

To verify the table, use the following :

mysql>DESCRIBE StudentInfo;

To delete a table, use the following:

mysql> DROP TABLE tableName;

Inserting Data into a Table

The command for inserting data into a database is called (appropriately enough) INSERT. There are two basic forms of this command:

mysql> INSERT INTO table_name SET
    -> columnName1 = value1,
    -> columnName2 = value2,
    -> ...
    -> ;

mysql> INSERT INTO table_name
-> (columnName1, columnName2, ...)
-> VALUES (value1, value2, ...);

You can use either of these commands:

mysql> INSERT INTO StudentInfo SET
-> ID = 1111,
-> Last_name = "Sam";

mysql> INSERT INTO StudentInfo
    -> (ID, Last_name) VALUES (
    -> 1111,
    -> "Sam"
    -> );

Note that in the second form of the INSERT command, the order in which you list the columns must match the order in which you list the values. Otherwise, the order of the columns doesn't matter, as long as you give values for all required fields.

Back