Basics of SQL: CREATE DATABASE And CREATE TABLE

Basics of SQL

MySQL, MariaDB, Oracle, PostgreSQL, and SQL Server handle table-structured data. These systems manage data using SQL. SQL is the most common RDBMS language. Another name for SQL commands is queries. Data Definition Language (DDL), which is used to define database structures, and Data Manipulation Language (DML), which is used to query and alter data, make up the SQL syntax. DDL commands are CREATE TABLE and CREATE DATABASE.

Database Concepts

Databases / Schemas: Relational databases combine tables into databases. ‘Databases’ and’schemas’ are synonymous in MySQL. A schema is a group of tables that have a common context and are typically part of the same application. The database schema is the entire collection of table designs for a database. Similar to a blueprint, it displays the tables, columns, and keys but excludes the actual data.

Tables: Relations, or tables as they are more widely known, are the building blocks of databases. As the name suggests, a table is a table of data, much like an electronic spreadsheet. Structured data is found in tables.

Columns: There are several columns in every database table. There is a kind and a name for every column.

Rows / Records: Tables, which are part of relational databases, contain rows of data. Typically, each row in a table describes a relationship or an actual thing. Details about the actual object are stored in the row’s column values. Records are another name for rows in database tables.

Data Types: Data types specify the types of information that can be stored in a column. Basic column types in MySQL include string, date and time, and numeric. It is advised to use the appropriate datatype based on the concept or intrinsic characteristics of the information.

The SHOW DATABASES; or SHOW SCHEMAS; commands can be used to see the databases that are available.

Basic SQL: CREATE DATABASE

The establish DATABASE command, followed by the desired database name, can be used to establish a new database in MySQL.
A semicolon after this command instructs MySQL to carry out the command. CREATE SCHEMA is another term for CREATE DATABASE in MySQL. To make a database called bookshop, for example:

mysql> create database books;

Since SQLite doesn’t need a daemon, setting up a database only involves creating a specially structured file. You try to open an existing database to build a new one if it doesn’t already exist. The SQLiteDatabase constructor can be used for this.

mysql> CREATE SCHEMA bookstore;

The path and file name are specified by the first option, and permissions for the generated database can be set using the second parameter. The specific term “:memory:” can also be used as the first parameter when creating in-memory databases.

Example:

<?php
    $db = new SQLiteDatabase("./crm.db", 0666, &$error)
        or die("Failed: $error");
    // ...
    unset($db);
?>

After connecting to the database server, PHP lets you run SQL queries like CREATE DATABASE. Something like this could be used with the MySQLi extension:

<?php
$cn = mysqli_connect("localhost","your_username","my_password");
//connecting the server
if (mysqli_connect_errno()) {
    echo "Error in establishing the connection:" . mysqli_connect_error();
}
$sql_query = "CREATE DATABASE MyDB";
if (mysqli_query($cn,$sql_query)) {
    echo "Database has been created";
} else {
    echo "Error while creating the database: ” . mysqli_error($cn);
}
?>

To establish databases, administrative rights are essential. Once a database has been created, it must be secured by assigning a username and password, and allowing users access to only the databases and tables they require.

Basic SQL: CREATE TABLE

Making tables in a database is the next step after creating one. The SQL command CREATE TABLE can be used for this. CREATE TABLE tablename(columns) is the standard format. In place of tablename, you enter the name of the table you wish to construct and a list of the columns separated by commas. A data type will appear after the name of each column.

This is an example of a CREATE TABLE statement in SQLite for a table called document:

CREATE TABLE document (
    id INTEGER PRIMARY KEY,
    title,
    intro,
    body
);

In this example:

• The customer ID is a numeric data type (INT UNSIGNED). The field cannot be blank if it is NOT NULL. When adding rows, MySQL’s AUTO_INCREMENT function automatically creates a unique identifier value if the field is left empty. This column is designated as the primary key by the notation PRIMARY KEY.
• Name, Address, and City are NOT NULL and are CHAR (string data type) with a given length.

Another illustration of various data kinds and restrictions for a recipes table is as follows:

CREATE TABLE recipes(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    ingredients TEXT NOT NULL,
    instructions TEXT NOT NULL,
    time INT UNSIGNED NOT NULL
);

In this case, INT UNSIGNED is a numeric data type, whereas VARCHAR and TEXT are text data types.

phpMyAdmin is a program that can generate the SQL queries for you, or you can type these CREATE TABLE commands directly in a MySQL command-line client. As an alternative, you can use methods like mysqli_query to run these commands from within your PHP scripts.

SHOW TABLES displays tables in the specified database when they are created. The DESCRIBE or SHOW COLUMNS FROM tablename command displays a table’s columns, data types, and constraints.

Example:

mysql> desc campers;

The campers table arrangement will be displayed by this command.