My SQL Tutorial
-
Creating and Selecting a Database
-
Creating a Table & Inserting Records
-
Creating Table
-
Inserting Records in a Table
-
-
Retrieving Information from a Table
-
Selecting All Data
-
Selecting Particular Rows
-
Selecting Particular Columns
-
Sorting Rows
-
Date Calculations
-
Working with NULL Values
-
Pattern Matching
-
-
Counting Rows
-
Using More Than one Table
-
Getting Information About Databases and Tables
-
Using mysql in Batch Mode
-
Examples of Common Queries .
-
The Maximum Value for a Column
-
The Row Holding the Maximum of a Certain Column
-
Maximum of Column per Group
-
The Rows Holding the Group-wise Maximum of a Certain Column
-
Using User-Defined Variables
-
Using Foreign Keys
-
Searching on Two Keys
-
Calculating Visits Per Day
-
Using AUTO_INCREMENT
-
Using MySQL with Apache
Creating Databases
After writing successful queries of MySQL, its time to create database in MySQL. Before going to create database first check the list of databases with the help of SHOW DATABASES sql command
Listing Databases of My SQL
SHOW DATABASES command displays list of databases in tabular form. Initially it will shows the name of default databases -
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
Creating Databases in My SQL
CREATE DATABASE <databasename> command is use to create a database
mysql> CREATE DATABASE school;
Query OK, 1 row in set (0.20 sec)
mysql> CREATE DATABASE school;
ERROR 1007 (HY000): Can't create database 'school'; database exists
The above command create an error message because database school is already exists. If you want mysql server ignore this command if database is already exists, then use this-CREATE DATABASE IF NOT EXISTS <databasename>, IF NOT EXISTS clause tells the mysql server, if given database does not exists, then only create it, otherwise ignore this command.
mysql> CREATE DATABASE IF NOT EXISTS school;
Query OK, 1 row affected, 1 warning (0.05 sec)
Selecting Database
Creating a database does not select it for use; you must do that explicitly. To make your database school the current database, use this statement:
mysql> USE school
Database changed
mysql> USE school;
Database changed
Note : With USE command semicolon is an optional.
Database needs to be created only once, but it must be selected for use each time you begin a mysql session. You can do this by issuing a USE statement as shown in the example.
Alternatively, you can select the database on the command line when you invoke mysql. Just specify database name after any connection parameters that you might need to provide. For example:
shell> mysql -h host -u user -p school
Enter password: ********
Note: - school is a database name, not a password.
To know which database is currently active or selected by you, use SELECT DATABASE( ) command.
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| school |
+------------+
1 row in set (0.00 sec)