xxxxxxxxxx
CREATE TABLE table_name(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255), # String 255 chars max
date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
longtext BLOB
);
xxxxxxxxxx
CREATE TABLE Persons (
PersonID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
LastName VARCHAR(255),
FirstName VARCHAR(255),
Address VARCHAR(255),
Number INT NOT NULL
);
xxxxxxxxxx
//to create a table
CREATE TABLE students
( student_id number(4) primary key,
last_name varchar2(30) NOT NULL,
course_id number(4) NULL );
//to insert value
INSERT INTO students VALUES (200, 'Jones', 101);
INSERT INTO students VALUES (201, 'Smith', 101);
INSERT INTO students VALUE (202, 'Lee' , 102);
xxxxxxxxxx
CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
xxxxxxxxxx
--Syntax for MySQL
CREATE TABLE Persons (
Personid int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (Personid)
);
-- Syntax for SQL Server
CREATE TABLE Persons (
Personid int IDENTITY(1,1) PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
xxxxxxxxxx
//to create a table
CREATE TABLE students
( student_id number(4) primary key,
last_name varchar2(30) NOT NULL,
course_id number(4) NULL );
//to insert value
INSERT INTO students VALUES (200, 'Jones', 101);
INSERT INTO students VALUES (201, 'Smith', 101);
INSERT INTO students VALUE (202, 'Lee' , 102);
xxxxxxxxxx
# During table creation
# MySQL and SQL Server
CREATE TABLE table_name (
id INT PRIMARY KEY NOT NULL [AUTO_INCREMENT],
col2 VARCHAR(50),
col3 DECIMAL(6,2) CHECK(col3>0),
FOREIGN KEY (other_id) REFERENCES Other_Table(id) [ON DELETE CASCADE]
);
# PostgreSQL
CREATE TABLE table_name (
id SERIAL PRIMARY KEY,
col2 VARCHAR(50),
col3 DECIMAL(6,2) CHECK(col3>0),
FOREIGN KEY (other_id) REFERENCES Other_Table(id) [ON UPDATE NO ACTION]
);
# If table already exists
# Adding primary key in MySQL, PostgreSQL and SQL Server
ALTER TABLE table_name
ADD CONSTRAINT table_pkey
PRIMARY KEY (col1,col2);
# Adding foreign key in MySQL, PostgreSQL and SQL Server
ALTER TABLE table_name
ADD CONSTRAINT table_fkey
FOREIGN KEY (col1) REFERENCES Other_Table(id);
xxxxxxxxxx
CREATE TABLE Companies (
id int,
name varchar(50),
address text,
email varchar(50),
phone varchar(10)
);
xxxxxxxxxx
CREATE TABLE courses(
course_id INT NOT NULL AUTO_INCREMENT,
course_name VARCHAR(50) NOT NULL,
PRIMARY KEY (course_id)
);
INSERT INTO courses(course_name)
VALUES('Btech'),
('BCA'),
('MBA');
xxxxxxxxxx
Creates a new table .
Example: Creates a new table called ‘users’ in the ‘websitesetup’ database.
CREATE TABLE users (
id int,
first_name varchar(255),
surname varchar(255),
address varchar(255),
contact_number int
);