SQL Basic Series (1)-Basic grammar--reprint W3school

1. Build the original table

Use [Northwind]

GO

/****** object:table [dbo].    [Persons] Script DATE:2016/6/8 7:31:57 ******/

SET ANSI_NULLS on

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo]. [Persons] (

[ID] [INT] IDENTITY (*) Not NULL,

[LastName] [NVARCHAR] () NULL,

[FirstName] [NVARCHAR] () NULL,

[Address] [NVARCHAR] (+) NULL,

[City] [NVARCHAR] (+) NULL,

CONSTRAINT [pk_persions] PRIMARY KEY CLUSTERED

(

[ID] ASC

) with (Pad_index = off, Statistics_norecompute = off, Ignore_dup_key = off, Allow_row_locks = on, allow_page_locks = ON) O N [PRIMARY]

) on [PRIMARY]

GO

Use [Northwind]

GO

/****** object:table [dbo].    [Orders] Script DATE:2016/6/8 7:32:44 ******/

SET ANSI_NULLS on

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo]. [Orders] (

[Id_o] [INT] IDENTITY (*) is not NULL,

[OrderNo] [NVARCHAR] () NULL,

[Id_p] [INT] Null

CONSTRAINT [Pk_orders_1] PRIMARY KEY CLUSTERED

(

[Id_o] Asc

) with (Pad_index = off, Statistics_norecompute = off, Ignore_dup_key = off, Allow_row_locks = on, allow_page_locks = ON) O N [PRIMARY]

) on [PRIMARY]

GO

2. Connection Query 2.1 Inner join

The INNER join keyword returns a row when there is at least one match in the table, equivalent to join

SELECT persons.*,

Orders.orderno

FROM dbo. Persons

INNER JOIN dbo. Orders on dbo. Persons.id = dbo. Orders.id_p

ORDER by dbo. Persons.lastname

2.2 LEFT Join

The left JOIN keyword returns all rows from the table (TABLE_NAME1), even if there are no matching rows in the right table (table_name2)

SELECT persons.*,

Orders.orderno

FROM dbo. Persons

Left JOIN dbo. Orders on dbo. Persons.id = dbo. Orders.id_p

ORDER by dbo. Persons.lastname

2.3 Right Join

The right JOIN keyword returns all rows from the table (table_name2), even if there are no matching rows in the left table (table_name1).

SELECT persons.*,

Orders.orderno

FROM dbo. Persons

Right JOIN dbo. Orders on dbo. Persons.id = dbo. Orders.id_p

ORDER by dbo. Persons.lastname

2.4 Full Join

The full JOIN keyword returns all rows from the left table (Persons) and the right table (Orders). If the rows in "Persons" do not match in the table "orders", or if the rows in "orders" do not have a match in the table "Persons", the rows are also listed.

SELECT Persons.lastname,

Persons.firstname,

Orders.orderno

From Persons

Full JOIN Orders on persons.id = orders.id_p

ORDER by Persons.lastname

2.5 Union

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.

SELECT LastName from Employees

UNION

SELECT LastName from Employees123

2.6 Union All

The union ALL command is almost equivalent to the Union command, but the union ALL command lists all values

SELECT LastName from Employees

UNION All

SELECT LastName from Employees123

3. Copy table Data 3.1 Select into

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.

SELECT *

Into Persons_backup

From Persons

3.2 Copy Partial column

SELECT Lastname,firstname

Into Persons_backup

From Persons

3.3 With conditional statements

SELECT Persons.lastname,

Orders.orderno

Into Persons_order_backup

From Persons

INNER JOIN Orders on persons.id = orders.id_p

4. Constraint 4.1 not Null

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.

CREATE TABLE Persons1

(

Id_p INT not NULL,

LastName VARCHAR (255) is not NULL,

FirstName VARCHAR (255),

Address VARCHAR (255),

City VARCHAR (255)

)

4.2 UNIQUE Constraint

Mysql:

CREATE TABLE Persons

(

id_p int not NULL,

LastName varchar (255) is not NULL,

FirstName varchar (255),

Address varchar (255),

City varchar (255),

UNIQUE (id_p)

)

Sql server

CREATE TABLE Persons

(

id_p int not NULL UNIQUE,

LastName varchar (255) is 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 MSSQL Oracle

CREATE TABLE Persons

(

id_p int not NULL,

LastName varchar (255) is not NULL,

FirstName varchar (255),

Address varchar (255),

City varchar (255),

CONSTRAINT Uc_personid UNIQUE (id_p,lastname)

)

Add a unique constraint above the table you have created

ALTER TABLE Persons

ADD UNIQUE (id_p)

Delete Constraint

Mysql

ALTER TABLE Persons

DROP INDEX Uc_personid

Mssql

ALTER TABLE Persons

DROP CONSTRAINT Uc_personid

4.3 PRIMARY KEY constraint

Mysql

CREATE TABLE Persons

(

id_p int not NULL,

LastName varchar (255) is not NULL,

FirstName varchar (255),

Address varchar (255),

City varchar (255),

PRIMARY KEY (id_p)

)

Msql

CREATE TABLE Persons

(

id_p int not NULL PRIMARY KEY,

LastName varchar (255) is not NULL,

FirstName varchar (255),

Address varchar (255),

City varchar (255)

)

Multiple fields

Mysql MSSQL Oracle Consistent

CREATE TABLE Persons

(

id_p int not NULL,

LastName varchar (255) is not NULL,

FirstName varchar (255),

Address varchar (255),

City varchar (255),

CONSTRAINT Pk_personid PRIMARY KEY (id_p,lastname)

)

Create a constraint for a table that is already in

ALTER TABLE Persons

ADD PRIMARY KEY (id_p)

ALTER TABLE Persons

ADD CONSTRAINT Pk_personid PRIMARY KEY (id_p,lastname)

Delete Constraint

Mysql

ALTER TABLE Persons

DROP PRIMARY KEY

Mssql

ALTER TABLE Persons

DROP CONSTRAINT Pk_personid

4.4 FOREIGN KEY

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

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)

)

Mssql Oracle

CREATE TABLE Orders

(

id_o int not NULL PRIMARY KEY,

OrderNo int not NULL,

id_p int FOREIGN KEY REFERENCES Persons (id_p)

)

Defining Multi-column constraints

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)

)

Add constraint

ALTER TABLE Orders

ADD FOREIGN KEY (id_p)

REFERENCES Persons (id_p)

naming constraints

ALTER TABLE Orders

ADD CONSTRAINT Fk_perorders

FOREIGN KEY (id_p)

REFERENCES Persons (id_p)

Delete Constraint

Mysql

ALTER TABLE Orders

DROP FOREIGN KEY fk_perorders

Mssql

ALTER TABLE Orders

DROP CONSTRAINT Fk_perorders

5. 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.

Mysql

CREATE TABLE Persons

(

id_p int not NULL,

LastName varchar (255) is not NULL,

FirstName varchar (255),

Address varchar (255),

City varchar (255),

CHECK (id_p>0)

)

Mssql

CREATE TABLE Persons

(

id_p int not NULL CHECK (id_p>0),

LastName varchar (255) is not NULL,

FirstName varchar (255),

Address varchar (255),

City varchar (255)

)

naming constraints

CREATE TABLE Persons

(

id_p int not NULL,

LastName varchar (255) is not NULL,

FirstName varchar (255),

Address varchar (255),

City varchar (255),

CONSTRAINT Chk_person CHECK (id_p>0 and city= ' Sandnes ')

)

Table already exists

Mysql

ALTER TABLE Persons

ADD CHECK (id_p>0)

Mssql

ALTER TABLE Persons

ADD CONSTRAINT Chk_person CHECK (id_p>0 and city= ' Sandnes ')

Delete Constraint

Mysql

ALTER TABLE Persons

DROP CHECK Chk_person

Mssql

ALTER TABLE Persons

DROP CONSTRAINT Chk_person

6. Index

Create indexes in tables to query data more quickly and efficiently.

Users cannot see the index, they can only be used to speed up search/query.

Updating a table that contains an index requires more time than updating a table that does not have an index, because the index itself needs to be updated. Therefore, it is ideal to create indexes only on columns (and tables) that are often searched.

To create a repeatable index

CREATE INDEX index_name

On table_name (COLUMN_NAME)

Create a unique index

CREATE UNIQUE INDEX index_name

On table_name (COLUMN_NAME)

To index a value in a column in descending order, you can add a reserved word after the column name DESC

CREATE INDEX Personindex

On person (LastName DESC)

Add indexes on multiple columns

CREATE INDEX Personindex

On person (LastName, FirstName)

7. Drop

Delete Index

Mssql

DROP INDEX Table_name.index_name

Mysql

ALTER TABLE table_name DROP INDEX index_name

Only need to drop the data in the table, but not delete the table itself

8. ALTER TABLE

Add columns

ALTER TABLE table_name

ADD column_name datatype

Delete Column

ALTER TABLE table_name

DROP COLUMN column_name

modifying columns

ALTER TABLE table_name

ALTER COLUMN column_name datatype

9. AUTO INCREMENT Field

Mysql

CREATE TABLE Persons

(

p_id int not NULL auto_increment,

LastName varchar (255) is not NULL,

FirstName varchar (255),

Address varchar (255),

City varchar (255),

PRIMARY KEY (p_id)

)

Modifying the starting Value

ALTER TABLE Persons auto_increment=100

Mssql

CREATE TABLE Persons

(

p_id int PRIMARY KEY IDENTITY,

LastName varchar (255) is not NULL,

FirstName varchar (255),

Address varchar (255),

City varchar (255)

)

By default, the start value of the IDENTITY is 1, and each new record is incremented by 1.

To specify that the "p_id" column starts at 20 and increments by 10, change the identity to identity (20,10)

SQL Basic Series (1)-Basic grammar--reprint W3school