SQL Full JOIN
(1) SQL full join keyword
(2) syntax
SELECT column_name (s) from Table_name1full JOIN table_name2 on Table_name1.column_name=table_name2.column_name
Note: In some databases, full join is called full OUTER join.
(3) Example
2.SQL Union and SQL UNION ALL
(1) SQL Union operator
The UNION operator is used to combine the result set of two or more SELECT statements.
Note that the SELECT statement inside the UNION must have the same number of columns . The column must also have a similar data type . Also, the order of the columns in each SELECT statement must be the same.
(2) SQL Union syntax
SELECT column_name (s) from Table_name1unionselect column_name (s) from table_name2
Note: By default, the UNION operator chooses a different value. If duplicate values are allowed, use UNION all.
(3) SQL UNION all syntax
SELECT column_name (s) from Table_name1union Allselect column_name (s) from table_name2
In addition, the column name in the union result set is always equal to the column name in the first SELECT statement in the Union.
(4) Using the SQL Union command
(5) UNION all
The union ALL command is almost equivalent to the Union command, but the union ALL command lists all values.
SQL Statement 1UNION allsql Statement 2
(6) Use the Union ALL command
3.SQL SELECT into
The SQL SELECT into statement can be used to create a backup copy of the table.
(1) SELECT into statement
(2) SELECT into syntax
SELECT *into new_table_name [in Externaldatabase] from Old_tablename
SELECT column_name (s) to new_table_name [in Externaldatabase] from Old_tablename
(3) SELECT into instance-make backup copy
SELECT
*
INTO
Persons_backupfrom Persons
SELECT
*
INTO
IN
' Backup.mdb ' from Persons
SELECT
Lastname,firstname
INTO
Persons_backupfrom Persons
(4) SELECT into instance-with a WHERE clause
SELECT
Lastname,firstname
INTO
Persons_backupfrom Persons
WHERE
city= ' Beijing '
(5) 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
4.SQL creat DATABASE
(1) Creat DATABASE statement
(2) SQL creat DATABASE syntax
(3) SQL creat DATABASE Instance
5.SQL creat TABLE
(1) Creat TABLE statement
(2) creat 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. |
(3) Creat 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:
id_p |
LastName |
FirstName |
Address |
| City
|
|
|
|
|
6.SQL CONSTRAINTS
(1) SQL 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
In the following sections, we will explain each constraint in detail.
7.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:
NOT NULL
NOT NULL
, FirstName varchar (255), Address varchar (255), City varchar (255))
8.SQL UNIQUE Constraint
(1) SQL UNIQUE constraint
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.
(2) SQL UNIQUE CONSTRAINT on creat TABLE
The following SQL creates a UNIQUE constraint in the "id_p" column when the "Persons" table is created:
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)
)
UNIQUE
, LastName varchar (255) not null,firstname varchar (255), Address varchar (255), City varchar (255))
Mysql/sol 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)
)
(3) SQL UNIQUE CONSTRAINT on ALTER TABLE
When a table has been created, to create a UNIQUE constraint in the id_p column, use the following SQL:
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:
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)
(4) revoke a UNIQUE constraint
To revoke a UNIQUE constraint, use the following SQL:
ALTER TABLE Persons
DROP INDEX uc_PersonID
ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID
SQL Basics (3)