Create Command

Create is DDL command that is used to create the database, table, view etc in SQL.

Syntax

CREATE database_object database_object_name;

---

Create Database

Create database is very simple command that simply work to create new database in system that you can see by used SHOW command. If the database already exists, Error 1007 is returned.

Syntax

CREATE Database database_name;

Example

mysql> CREATE Database new_database;
Query OK, 1 row affected (0.00 sec)

mysql> SHOW Databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| new_database       |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql>

Create Table

The keyword CREATE TABLE is followed by the name of the table that you want to create. Then within parenthesis, you write the column definition that consists of column name followed by its datatypes and optional constraints. There can be as many columns as you require. Each column definition is separated with a comma (,). All SQL statements should end with a semicolon (;).

Syntax

CREATE Table table_name(column_name datatype constraint, ...);

Example

mysql> CREATE Table staff(
    -> id tinyint unique,
    -> name char(30) not null,
    -> dob date,
    -> gender char(1) default 'T'
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql>

Create View

The VIEW is concept to present the data with selected data. It is as structure of user that data is select by other table. Is is a representation of table with struture change. It Create to use compand CREATE VIEW and it delete to use Drop view command. If you display the data of view you may be select view with select command.

Syntax

CREATE View view_name AS select_query;

Example

mysql>  CREATE View new_view AS 
    ->  SELECT id, name, contact FROM table_name;
Query OK, 0 rows affected (0.04 sec)

mysql> SELECT * FROM new_view;
+------+--------+------------+
|  ID  |  Name  |  Contact   |
+------+--------+------------+
|   1  |   Ram  | 9211420420 |
+------+--------+------------+
1 row in set (0.02 sec)

mysql>

Create User

The above creates a user John123, able to connect with any hostname due to the % wildcard. The Password for the user is set to 'OpenSesame' which is hashed.

Syntax

CREATE User user_name @ host_name IDENTIFIED BY password;

Example

mysql> CREATE USER 'sqluser'@'%' IDENTIFIED BY 'password123';
Query OK, 0 rows affected (0.22 sec)

mysql>