Apply for Zend Framework Certification Training

Mysql


< DML DQL and Other SQL Language Categories Insert query in mysql >



Creating a Table in MySQL (Detailed Notes)
A table in MySQL is a collection of related data organized into rows and columns. Tables are created inside a database using the CREATE TABLE statement.
Syntax
CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    column3 datatype constraints,
    ...
);
Explanation
CREATE TABLE → Command used to create a new table.
table_name → Name of the table.
column_name → Name of the field.
datatype → Specifies the type of data that can be stored.
constraints → Rules applied to the column (optional).
Example 1: Simple Table
CREATE TABLE student (
    id INT,
    name VARCHAR(50),
    age INT,
    city VARCHAR(30)
);
Output Structure
id name age city
Example 2: Student Table with Constraints
CREATE TABLE student (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT,
    gender CHAR(1),
    email VARCHAR(100) UNIQUE,
    city VARCHAR(50),
    admission_date DATE
);
Description of Each Column
Column DataType   Description
id          INT          Student ID
name VARCHAR(100) Student Name
age        INT Student Age
gender  CHAR(1) M/F
email     VARCHAR(100) Email Address
city        VARCHAR(50) City Name
admission_date DATE Date of Admission
 
Common MySQL Data Types
Numeric Data Types
Data              Type Description
INT               Integer value
BIGINT          Large integer
SMALLINT      Small integer
TINYINT         Very small integer
FLOAT            Decimal number
DOUBLE         Double precision number
DECIMAL(10,2) Fixed decimal value
 
String Data Types
Data Type          Description
CHAR(n)            Fixed-length string
VARCHAR(n)      Variable-length string
TEXT                 Large text
Date and Time
Data Type Description
DATE                YYYY-MM-DD
TIME                 HH:MM:SS
DATETIME         Date and Time
TIMESTAMP       Timestamp
 
Constraints in MySQL
1. PRIMARY KEY
Uniquely identifies every row.
id INT PRIMARY KEY
Example
 
CREATE TABLE employee(
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50)
);
 
2. NOT NULL
Does not allow NULL values.
name VARCHAR(50) NOT NULL
 
3. UNIQUE
Prevents duplicate values.
email VARCHAR(100) UNIQUE
 
4. DEFAULT
Provides a default value.
city VARCHAR(50) DEFAULT 'Chandigarh'
Example
CREATE TABLE student(
    id INT,
    city VARCHAR(50) DEFAULT 'Delhi'
);
 
5. AUTO_INCREMENT
Automatically generates numbers.
id INT AUTO_INCREMENT PRIMARY KEY
Example
CREATE TABLE student(
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);
Result
id name
1 Rahul
2 Aman
3 Neha
 
6. CHECK
Restricts values.
age INT CHECK(age >=18)
Example
CREATE TABLE employee(
    id INT,
    age INT CHECK(age>=18)
);
7. FOREIGN KEY
Creates a relationship between two tables.
Department Table
CREATE TABLE department(
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50)
);
 
Employee Table
CREATE TABLE employee(
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    dept_id INT,
    FOREIGN KEY(dept_id) REFERENCES department(dept_id)
);
Complete Example
CREATE TABLE employee(
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL,
    gender CHAR(1),
    age INT CHECK(age>=18),
    salary DECIMAL(10,2),
    email VARCHAR(100) UNIQUE,
    city VARCHAR(50) DEFAULT 'Delhi',
    joining_date DATE,
    department VARCHAR(50)
);
View Table Structure
DESC employee;
or
DESCRIBE employee;
Output
Field Type Null Key Default Extra
 
Show All Tables
SHOW TABLES;
Display the CREATE TABLE Statement
SHOW CREATE TABLE employee;
 
Rename a Table
RENAME TABLE employee TO staff;
or
ALTER TABLE employee
RENAME TO staff;
 
Delete a Table
DROP TABLE employee;
 
Real-World Example: Student Management System
CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50),
    gender CHAR(1),
    date_of_birth DATE,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(15),
    address VARCHAR(255),
    course VARCHAR(100),
    admission_date DATE DEFAULT (CURRENT_DATE),
    fees DECIMAL(10,2),
    status VARCHAR(20) DEFAULT 'Active'
);

< DML DQL and Other SQL Language Categories Insert query in mysql >



Ask a question



  • Question: 
    {{questionlistdata.blog_question_description}}
    • Answer:
      {{answer.blog_answer_description  }}
    Replay to Question

(+91)-9910699802
Become A Member     Become A Teacher
Back to Top