SQL Advanced (3)

SQL Advanced (3) SQL SELECT INTO statement the SQL SELECT INTO statement can be used to create a backup copy of the table.
The SELECT INTO statement 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 columns into a 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) to 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_backupfrom 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,firstnameinto Persons_backupfrom 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,firstnameinto persons_backupfrom personswhere city= ' Beijing '

SQL SELECT INTO instance-the connected table selects data from more than one table and can do so.
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.ordernointo persons_order_backupfrom personsinner JOIN Orderson Persons.Id_P= Orders.id_p

The SQL CREATE DATABASE statement creates the databases statement that is used to create the db.
SQL CREATE DATABASE Syntax

CREATE DATABASE database_name

SQL Create DB instance now we want to create a database named "my_db".
We use the following CREATE DATABASE statement:

CREATE DATABASE my_db

You can add database tables by using CREATE table.
The CREATE TABLE statement of the SQL CREATE TABLE statement creates a table statement 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,....)

The data type (DATA_TYPE) specifies what data type the column can hold. The following table contains the most commonly used data types in sql:

Data Type	Description
Integer (size) int (size) smallint (size) tinyint (size)	Holds integers only. Specify the maximum number of digits within the parentheses.
Decimal (SIZE,D) Numeric (SIZE,D)	Accommodates numbers with decimals. "Size" Specifies the maximum number of digits. "D" Specifies the maximum number of digits to the right of the decimal point.
char (size)	Holds a fixed-length string (which can hold letters, numbers, and special characters). Specifies the length of the string in parentheses.
varchar (size)	Accommodates variable-length strings that can hold letters, numbers, and special characters. Specifies the maximum length of the string in parentheses.
Date (YYYYMMDD)	accommodate the date.

SQL CREATE TABLE Instance This example shows how to create a table named "Person."
The table contains 5 columns with column names: "Id_p", "LastName", "FirstName", "Address", and "City":

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

The data type of the id_p column is int, which contains integers. The data type of the remaining 4 columns is varchar, with a maximum length of 255 characters.
An empty "Persons" table looks like this:
City

id_p	LastName	FirstName	Address

You can use the INSERT into statement to write data to an empty table.
SQL constraint (Constraints) SQL constraint 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

Note: In the following sections, we will explain each constraint in detail.
SQL NOT NULL constraint SQL NOT NULL constraint NOT NULL constraint forcing column does not accept null value.
A NOT NULL constraint forces a field to always contain a value. This means that if you do not add a value to a field, you cannot insert a new record or update it.
The following SQL statement enforces that the "id_p" column and the "LastName" column do not accept NULL values:

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

The SQL UNIQUE constraint SQL UNIQUE constraint unique constraint uniquely identifies 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.
SQL unique Constraint on CREATE table The following SQL creates a UNIQUE constraint in the "id_p" column when the "Persons" table is created:
Mysql:

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

SQL Server/oracle/ms Access:

CREATE TABLE Persons (id_p int not NULL 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))

SQL UNIQUE Constraint on ALTER table when a table has been created, if you want to create a UNIQUE constraint in the p_id column, use the following SQL:
Mysql/sql Server/oracle/ms Access:

ALTER TABLE personsadd UNIQUE (p_id)

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 personsadd CONSTRAINT uc_personid UNIQUE (p_id,lastname)

To revoke a unique constraint if you want to revoke a unique constraint, use the following SQL:
Mysql:

ALTER TABLE Personsdrop INDEX Uc_personid

SQL Server/oracle/ms Access:

ALTER TABLE Personsdrop CONSTRAINT Uc_personid

SQL PRIMARY key constraint SQL PRIMARY key constraint 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.
SQL PRIMARY key Constraint on CREATE table The following SQL creates a PRIMARY KEY constraint in the "id_p" column when the "Persons" table is created:
Mysql:

CREATE TABLE Persons (id_p int not null,lastname varchar (255) not null,firstname varchar (255), Address varchar (255), City VA Rchar (255), PRIMARY KEY (id_p))

SQL Server/oracle/ms Access:

CREATE TABLE Persons (id_p int not NULL PRIMARY key,lastname varchar (255) not null,firstname varchar (255), Address varchar (2 ), City varchar (255))

If you need to name the PRIMARY key constraint and define the PRIMARY key 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 Uc_personid PRIMARY KEY (id_p,lastname))

SQL PRIMARY key Constraint on ALTER table if you create a PRIMARY KEY constraint for the "id_p" column if the table already exists, use the following sql:
Mysql/sql Server/oracle/ms Access:

ALTER TABLE personsadd PRIMARY KEY (id_p)

If you need to name the PRIMARY key constraint and define the PRIMARY key constraint for more than one column, use the following SQL syntax:
Mysql/sql Server/oracle/ms Access:

ALTER TABLE personsadd CONSTRAINT pk_personid PRIMARY KEY (id_p,lastname)

Note: If you use the ALTER table statement to add a primary key, you must declare the primary key column to not contain a NULL value (when the table is first created).
To revoke the PRIMARY key constraint if you want to revoke the PRIMARY key constraint, use the following SQL:
Mysql:

ALTER TABLE Personsdrop PRIMARY KEY

SQL Server/oracle/ms Access:

ALTER TABLE Personsdrop CONSTRAINT Pk_personid

SQL FOREIGN key constraint SQL FOREIGN key constrains a table in which FOREIGN key points to PRIMARY key in another 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 a FOREIGN KEY for the "id_p" column when the "Orders" table is created:
Mysql:

CREATE TABLE Orders (o_id int not null,orderno int not null,id_p int,primary key (o_id), FOREIGN key (id_p) REFERENCES Perso NS (id_p))

SQL Server/oracle/ms Access:

CREATE TABLE Orders (o_id 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 (o_id int not null,orderno int not null,id_p int,primary key (o_id), 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)

To revoke the FOREIGN key constraint if you want 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 Advanced (3)