Create, Show, If Not Exists and Constraint

Important Concept MySQL Part -II – Sagar Jaybhay

Create database:

Create database demodatabase;

In Unix, the name of the database is case sensitive but in the windows, it is not case sensitive. This query is used to create database in MySQL.

Select database

Use databasename;

To select the database you need to use keyword USE.

Show command:

To show the database used to show the list of current database present on that server.

show databases;

Show tables

To show tables below command is used to show the list of tables present on the selected database.

Show tables;

Show Create Statement

To show the create a statement of creating a database using below command

Show create database dbName;

To show the create statement of each table, procedure and view you can use the show.

How to find database size in MySQL?

In information_schema.tables is the table present in MySQL.  So use the below query to find out the size of that database.

SELECT table_schema "dbname", 
SUM(data_length + index_length)/1024/1024 "Size in MB" 
FROM information_schema.TABLES GROUP BY table_schema;
Create, Show, If Not Exists and Constraint

What is Limit of table size in MYSQL

From MySQL, there is no any kind of limit but the limit is applied by underlying OS. For example, mac OS has a 2 Tb table size.

Limits on column and rows

The maximum number of column is 4096 per table. Individual storage structure also limits the column count for ex. InnoDB provides 1000 column limit.

Create Table:

Create table table_Name (column_name data_type, column_name2 data_type, column_name3 data_type);

If not Exists

This is used for when you want to create a table or you fire a query to create a table of name SomeDemo as table name but if that names table already present in the database then if you do not use if not exists clause in creating statement then the MySQL throws an error. But when you use that if not exist then it will not throw an error even if the table is present on that database.

Create table if not exists table_Name (column_name data_type, column_name2 data_type, column_name3 data_type);
  1. To create a table like another table you can use below query
create table TABLE_name1 like table_Name

In this to create an identical table from an existing table you need to use like operator.

  1. As Select: to create a table with a selected row or total rows with table structure.
Create table new_table As select * from old_table;

MySQL Constraints:

It is used to define rules too restricting what values will be stored in columns. It is used to limit the type of data inserted into the table.

In this MySQL, constraints are of 2 types column level and table-level constraints. In this column level constraints apply to one column and table level is applied to the complete table which includes all columns.

You need to define or add constraint at the time of table creation. Following are the constraint which is useful.

    null
  • Not Null
  • Unique
  • Primary Key
  • Foreign Key
  • Check
  • Default

Not Null:

It is used to specify the column can not contain any null value. It is used to creating and alter.

CREATE TABLE IF NOT EXISTS tableName
(authid varchar(8) NOT NULL,
authname varchar(50) NOT NULL);

Unique:

It is used to maintain uniqueness in the column. It is not allowed to insert duplicate value.

CREATE TABLE IF NOT EXISTS tablename ( authid varchar(8) NOT NULL ,
authname varchar(50) NOT NULL,
country varchar(25) NOT NULL,
home_city varchar(25) NOT NULL, 
UNIQUE (authid)
);

Primary Key:-

in the t enforce on the table to accept only unique data. It is used to create a unique index for accessing faster data.

CREATE TABLE IF NOT EXISTS
tablename (autid varchar(8) NOT NULL ,   
autname varchar(50) NOT NULL, 
country varchar(25) NOT NULL,
homecity varchar(25) NOT NULL,         
PRIMARY KEY (autid));

Foreign Key:-  

It is a link between 2 tables and one column primary key referred by another table column as a foreign key.

CREATE TABLE IF NOT EXISTS tablename
(bookid varchar(15) NOT NULL PRIMARY KEY,
bookname varchar(50)  ,
isbnno varchar(15)  NOT NULL  ,
cateid varchar(8)  , 
autid varchar(8) , 
pubid varchar(8) ,          
dtof_pub date ,
publang varchar(15) ,
nopage decimal(5,0) ,         
bookprice decimal(8,2) ,
FOREIGN KEY (autid) REFERENCES newauthor(autid));

Check:

used for logical expression to check value is valid or not.

CREATE TABLE IF NOT EXISTS
tableName (authid varchar(8) NOT NULL , 
authname varchar(50) NOT NULL,
country varchar(25) NOT NULL CHECK (country IN ('USA','UK','India')), 
PRIMARY KEY (authid,home_city));

Default:

If you have not supplied any value to the column it will get null value but after using default value it is set to the default value which supplied.

CREATE TABLE IF NOT EXISTS tableName
(pubid varchar(8) NOT NULL UNIQUE DEFAULT '' ,           
pubname varchar(50) NOT NULL  DEFAULT '' ,          
pubcity varchar(25) NOT NULL  DEFAULT '' ,          
country varchar(25) NOT NULL DEFAULT 'India',          
countryoffice varchar(25) , 
no_of_branch int(3),        
estd date
CHECK ((country='India' AND pub_city='Mumbai')
OR (country='India' AND pub_city='New Delhi')) ,
PRIMARY KEY (pubid));

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *