Sql--select into,create database,create table,constraints

The SQL SELECT into statement can be used to create a backup copy of the table.
SELECT into statement
The SELECT INTO statement selects data from one table and then inserts the data into another table.
The SELECT into statement is commonly used to create a backup copy of a table or to archive records.

SQL SELECT into syntax

You can insert all the columns into the new table:
SELECT *
into new_table_name [in Externaldatabase]
From Old_tablename
Or just insert the desired column into the new table:
SELECT column_name (s)
into new_table_name [in Externaldatabase]
From Old_tablename

SQL SELECT into instance-make backup copy
The following example makes a backup copy of the "Persons" table:
SELECT *
Into Persons_backup
From Persons
The IN clause can be used to copy a table to another database:
SELECT *
Into Persons in ' Backup.mdb '
From Persons

If we want to copy some of the fields, we can list them after the SELECT statement:
SELECT Lastname,firstname
Into Persons_backup
From Persons
SQL SELECT into instance-with a WHERE clause
We can also add a WHERE clause.
The following example creates a table named "Persons_backup" with two columns by extracting information from the "Persons" table of people residing in "Beijing":

select lastname,firstname
into persons_backup
from Persons
where city= ' Beijing '
SQL SELECT INTO instance-connected table
It is also possible to select data from more than one table.

The following example creates a new table named "Persons_order_backup" that contains the information obtained from the Persons and Orders two tables:
SELECT Persons.lastname,orders.orderno
Into Persons_order_backup
From Persons
INNER JOIN Orders
On persons.id_p=orders.id_p

CREATE DATABASE Statement

Create database is used for creating databases.

SQL CREATE DATABASE Syntax

CREATE DATABASE database_name

CREATE TABLE Statement

The CREATE TABLE statement is used for creating tables in the database.

SQL CREATE TABLE Syntax

CREATE table table name (column name 1 data type, column name 2 data type, column name 3 data type,....)

CREATE TABLE Persons (id_p int,lastname varchar (255), FirstName varchar (255), Address varchar (255), City varchar (255))

SQL constraints (Constraints)

Constraints are used to restrict the type of data that is joined to a table.

You can specify constraints (through the CREATE TABLE statement) When you create the table, or you can (via the ALTER table statement) after the table is created.

We will focus on the following constraints:

• Not NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
• DEFAULT

SQL not NULL constraint

The NOT NULL constraint enforces that the column does not accept null values.

A NOT NULL constraint forces a field to always contain a value. This means that if you do not add a value to the field, you cannot insert a new record or update the record.

The following SQL statement enforces that the "id_p" column and the "LastName" column do not accept NULL values:

CREATE TABLE Persons ( NOT NULL NOT NULL ,FirstName varchar (255), Address varchar (255), City varchar (255))

SQL UNIQUE constraints

Unique constraints uniquely identify each record in a database table.

Both the unique and PRIMARY KEY constraints provide a unique guarantee for a column or column collection.

PRIMARY KEY has a UNIQUE constraint that is automatically defined.

Note that each table can have multiple UNIQUE constraints, but there can be only one PRIMARY KEY constraint per table.

CREATE TABLE Persons ( UNIQUE ,LastName varchar (255) not null,firstname varchar (255), Address varchar (255), City varchar (255))

If you need to name a unique constraint and define a UNIQUE constraint for multiple columns, use the following SQL syntax:

Mysql/sql Server/oracle/ms Access:

CREATE TABLE Persons (id_p int not null,lastname varchar (255) not null,firstname varchar (255), Address varchar (255), City VA Rchar (255), CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName) )

When a table has been created, to create a UNIQUE constraint in the id_p column, use the following SQL:
Mysql/sql Server/oracle/ms Access:
ALTER TABLE Persons
ADD UNIQUE (id_p)
To name a unique constraint and define a UNIQUE constraint for multiple columns, use the following SQL syntax:
Mysql/sql Server/oracle/ms Access:
ALTER TABLE Persons
ADD CONSTRAINT Uc_personid UNIQUE (id_p,lastname)

Revoke a UNIQUE constraint
To revoke a UNIQUE constraint, use the following SQL:

ALTER TABLE Persons
DROP CONSTRAINT Uc_personid

SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a database table.

The primary key must contain a unique value.

Primary key columns cannot contain NULL values.

Each table should have a primary key, and each table can have only one primary key.

PRIMARY KEY

SQL FOREIGN KEY constraint

The FOREIGN key in one table points to the PRIMARY key in the other table.

Let's use an example to explain the foreign key. Take a look at the following two tables:

"Persons" table:

City

id_p	LastName	FirstName	Address
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

"Orders" table:

Id_o	OrderNo	id_p
1	77895	3
2	44678	3
3	22456	1
4	24562	1

Notice that the Id_p column in Orders points to the id_p column in the Persons table.

The "id_p" column in the "Persons" table is the PRIMARY KEY in the Persons table.

The "id_p" column in the Orders table is the FOREIGN KEY in the Orders table.

The FOREIGN KEY constraint is used to prevent actions that disrupt the connection between tables.

The FOREIGN key constraint can also prevent illegal data from being inserted into the foreign key column, because it must be one of the values in the table it points to.

SQL FOREIGN KEY Constraint on CREATE TABLE

The following SQL creates FOREIGN KEY for the "id_p" column when the Orders table is created:

Mysql:

CREATE TABLE Orders (id_o int not null,orderno int not null,id_p int,primary KEY (id_o), FOREIGN KEY (Id_P) REFERENCES Persons(Id_P) )

SQL Server/oracle/ms Access:

CREATE TABLE Orders (id_o int NOT null PRIMARY Key,orderno int. NOT NULL Id_P int FOREIGN KEY REFERENCES Persons(Id_P) )

If you need to name the FOREIGN key constraint and define the FOREIGN key constraint for more than one column, use the following SQL syntax:

Mysql/sql Server/oracle/ms Access:

CREATE TABLE Orders (id_o int not null,orderno int not null,id_p int,primary KEY (id_o), CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P)REFERENCES Persons(Id_P) )

SQL FOREIGN KEY Constraint on ALTER TABLE

If you create a FOREIGN KEY constraint for the id_p column if the Orders table already exists, use the following SQL:

Mysql/sql Server/oracle/ms Access:

ALTER TABLE OrdersADD FOREIGN KEY (Id_P)REFERENCES Persons(Id_P)

If you need to name the FOREIGN key constraint and define the FOREIGN key constraint for more than one column, use the following SQL syntax:

Mysql/sql Server/oracle/ms Access:

ALTER TABLE OrdersADD CONSTRAINT fk_PerOrdersFOREIGN KEY (Id_P)REFERENCES Persons(Id_P)

revoke FOREIGN KEY constraint

To revoke the FOREIGN KEY constraint, use the following SQL:

Mysql:

ALTER TABLE OrdersDROP FOREIGN KEY fk_PerOrders

SQL Server/oracle/ms Access:

ALTER TABLE OrdersDROP CONSTRAINT fk_PerOrders

SQL CHECK Constraints

A CHECK constraint is used to limit the range of values in a column.

If you define a CHECK constraint on a single column, the column only allows a specific value.

If a CHECK constraint is defined on a table, the constraint restricts the value in a specific column.

SQL CHECK Constraint on CREATE TABLE

The following SQL creates a CHECK constraint for the "id_p" column when the "Persons" table is created. The CHECK constraint specifies that the "id_p" column must contain only integers greater than 0.

My SQL:

CREATE TABLE Persons (id_p int not null,lastname varchar (255) not null,firstname varchar (255), Address varchar (255), City VA Rchar (255), CHECK (Id_P>0) )

SQL Server/oracle/ms Access:

CHECK (Id_P>0), LastName varchar (255) not null,firstname varchar (255), Address varchar (255), City varchar (255))

If you need to name a check constraint and define a CHECK constraint for more than one column, use the following SQL syntax:

Mysql/sql Server/oracle/ms Access:

CREATE TABLE Persons (id_p int not null,lastname varchar (255) not null,firstname varchar (255), Address varchar (255), City VA Rchar (255), CONSTRAINT chk_Person CHECK (Id_P>0 AND City=‘Sandnes‘) )

SQL CHECK Constraint on ALTER TABLE

If you create a CHECK constraint for the "id_p" column if the table already exists, use the following SQL:

Mysql/sql Server/oracle/ms Access:

ALTER TABLE PersonsADD CHECK (Id_P>0)

If you need to name a check constraint and define a CHECK constraint for more than one column, use the following SQL syntax:

Mysql/sql Server/oracle/ms Access:

ALTER TABLE PersonsADD CONSTRAINT chk_Person CHECK (Id_P>0 AND City=‘Sandnes‘)

revoke a CHECK constraint

To revoke a check constraint, use the following SQL:

SQL Server/oracle/ms Access:

ALTER TABLE PersonsDROP CONSTRAINT chk_Person

Mysql:

ALTER TABLE PersonsDROP CHECK chk_Person

SQL DEFAULT constraints

The default constraint is used to insert defaults into the column.

If no other value is specified, the default value is added to all new records.

SQL DEFAULT Constraint on CREATE TABLE

The following SQL creates a DEFAULT constraint for the "City" column when the "Persons" table is created:

My sql/sql server/oracle/ms Access:

CREATE TABLE Persons (id_p int not null,lastname varchar (255) not null,firstname varchar (255), Address varchar (255), City VA Rchar (255) DEFAULT ' Sandnes ')

By using a function like GETDATE (), the default constraint can also be used to insert system values:

CREATE TABLE Orders (id_o int not Null,orderno int. not null,id_p int,orderdate date DEFAULT GETDATE ())

SQL DEFAULT Constraint on ALTER TABLE

If you create a DEFAULT constraint for the city column if the table already exists, use the following SQL:

Mysql:

ALTER TABLE personsalter City SET DEFAULT ' Sandnes '

SQL Server/oracle/ms Access:

ALTER TABLE personsalter COLUMN city SET DEFAULT ' Sandnes '

undo DEFAULT Constraint

To undo the DEFAULT constraint, use the following SQL:

Mysql:

ALTER TABLE personsalter City DROP DEFAULT

SQL Server/oracle/ms Access:

ALTER TABLE personsalter COLUMN City DROP DEFAULT

Sql--select into,create database,create table,constraints