DATABASE _ CREATE
https://www.w3schools.com/mysql/default.asp
Create a new database
Write the correct SQL statement to create a new database called testDB.
CREATE DATABASE testDB;
Delete a database
Write the correct SQL statement to delete a database named testDB.
DROP DATABASE testDB;
Create a new table
Write the correct SQL statement to create a new table called Persons.
CREATE TABLE Persons
(PersonID int,LastName varchar(255),FirstName varchar(255),Address varchar(255),City varchar(255) );
Delete a table
Write the correct SQL statement to delete a table called Persons.
DROP TABLE Persons;
Truncate
Use the TRUNCATE statement to delete all data inside a table.
TRUNCATE TABLE Persons;
Alter add column
Add a column of type DATE called Birthday.
ALTER TABLE Persons ADD Birthday DATE;
Alter delete column
Delete the column Birthday from the Persons table.
ALTER TABLE Persons DROP COLUMN Birthday;
DATABASE_Constraints
NOT NULL
– Ensures that a column cannot have a NULL valueUNIQUE
– Ensures that all values in a column are differentPRIMARY KEY
– A combination of aNOT NULL
andUNIQUE
. Uniquely identifies each row in a tableFOREIGN KEY
– Prevents actions that would destroy links between tablesCHECK
– Ensures that the values in a column satisfies a specific conditionDEFAULT
– Sets a default value for a column if no value is specifiedCREATE INDEX
– Used to create and retrieve data from the database very quickly
NOT NULL on CREATE TABLE
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int
);
UNIQUE
The UNIQUE
constraint ensures that all values in a column are different.
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
UNIQUE (ID)
);
PRIMARY KEY
The PRIMARY KEY
constraint uniquely identifies each record in a table.
Primary keys must contain UNIQUE values, and cannot contain NULL values.
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);
FOREIGN KEY
The FOREIGN KEY
constraint is used to prevent actions that would destroy links between tables.
A FOREIGN KEY
is a field (or collection of fields) in one table, that refers to the PRIMARY KEY
in another table.
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
REFERENCES Persons(PersonID)
);
CHECK
The CHECK
constraint is used to limit the value range that can be placed in a column.
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CHECK (Age>=18)
);
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255),
CONSTRAINT CHK_Person CHECK (Age>=18 AND City=‘Sandnes’)
);
DEFAULT
The DEFAULT
constraint is used to set a default value for a column.
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255) DEFAULT ‘Sandnes’
);
CREATE TABLE Orders (
ID int NOT NULL,
OrderNumber int NOT NULL,
OrderDate date DEFAULT CURRENT_DATE()
);
AUTO INCREMENT
CREATE TABLE Persons (
Personid int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (Personid)
);
ALTER TABLE Persons AUTO_INCREMENT=100;
CREATE INDEX
Indexes are used to retrieve data from the database more quickly than otherwise. The users cannot see the indexes, they are just used to speed up searches/queries.
CREATE INDEX idx_pname ON Persons (LastName, FirstName);
CREATE VIEW
a view is a virtual table based on the result-set of an SQL statement.
CREATE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = ‘Brazil’;
WORKING WITH DATES
DATE
– format YYYY-MM-DDDATETIME
– format: YYYY-MM-DD HH:MI:SSTIMESTAMP
– format: YYYY-MM-DD HH:MI:SSYEAR
– format YYYY or YY
I’ve been browsing on-line greater than three hours lately, yet I by no means found any attention-grabbing
article like yours. It is beautiful value enough for me.
In my view, if all site owners and bloggers made good content as you probably did, the internet might be a lot more helpful than ever before.
I’ll right away snatch your rss feed as I can not in finding your e-mail
subscription hyperlink or e-newsletter service. Do you have any?
Kindly let me realize so that I may subscribe.
Thanks.