数据库高级教程（二）

1.SQL UNION 和UNION ALL操作符

SQL UNION操作符

UNION 操作符用于合并多个或者多个SELECT语句的结果集

注意：UINION 内部的SELECT语句必须拥有相同的列，列也必须拥有相同的数据类型。并且每条SELECT语句的列的顺序必须相同。

SQL UNION 语法

SELECT column_name from table_name1 UNION sell column_name from table_name2 

注：默认 UNION操作符选取不同的值，如果允许重复的话，请使用UNION ALL

SQL UNION ALL语法

SELECT column_name from table_name1 UNION ALL sell column_name from table_name2 

UNION结果集中的列明总是等于UNION中第一个SELECT语句中的名称。

原始的表

Person_Chinese表

P_ID      P_Name

01           Li,Ming

02           Wang,Qiang

03         Cates,Bill

04           Sun,Qi

Person_USA

P_ID      P_Name

01           Adas,John

02           Bush,Geodge

03           Cater,Thomes

04           Cates,Bill

使用UNION命令

列出所有在中国和美国的人名字

SELECT P_Name from Person_Chinese UNION SELECT  P_Name from  Person_USA 

结果为

P_Name

 Li,Ming

Wang,Qiang

 Cates,Bill

Sun,Qi

Adas,John

Bush,Geodge

Cater,Thomes

而

SELECT P_Name from Person_Chinese UNION ALL SELECT  P_Name from  Person_USA 

结果为

P_Name

 Li,Ming

Wang,Qiang

 Cates,Bill

Sun,Qi

Adas,John

Bush,Geodge

Cater,Thomes

Cater，Bills

2.SQL INTO 语句

用于创建表的备份文件。

SELECT INTO 语句从一个表中选取数据，然后插入另一个表中。

SELECT INTO语句常用语创建表的备份复件或者用于对记录进行存档。

语法。

把所有的列插入新表。

SELECT * INTO new_table_name[IN externaldatabase] FROM old_name

只把希望的列插入新表。

SELECT column_name(s) INTO new_table_name[IN esternaldatabase]

from  old_tablename

例如

制作备份文件。

制作Person表的备份文件。

SELECT *INTO Person_backup FROM Person

IN 字句可以用于向另一个数据库中拷贝表

SELECT * INTO Person IN ‘Backup.mdb’ from Person

如果我们希望拷贝某些域，可以在SELECT语句中列出这些域。

SELECT firstName，LastName INTO Person_backup From Person

SQL SELECT INTO实例， 带有where字句

我们也可以添加Where字句。

下面的例子通过从Person表中提取居住在北京的人的信息，创建了一个带有两个列的名为Person_backup的表

SELECT firstName，LastName INTO Person_backup from Person where city=‘Beijing’

例 被连接的表

从一个以上的表中选取数据

下面例子会创建一个名为，“Person_Number_Backup” from Person INNER JOIN Orders ON Persons.Id_P=number.Id_P

3.Create DataBase

用于创建数据库

语法  create database database_name

实例  创建一个my_database的数据库

create database my_database

可以通过create table来添加数据库表

4.SQL create table 语句。

用于创建数据库中的表。

SQL create table 语法

create table 表名称

{

列名称1 数据类型

列名称2 数据类型

列名称3 数据类型

............

...........

}

数据类型规定了列可容纳何种数据类型，

数据类型                        描述

integer（size）           仅容纳证书，在括号中规定数字的最大位数

int（size）                   

smallint（size）           

tinyint(size)

decimal(size,d)          容纳带有小数的数字

numeric(size,d)         size规定数字的最大位数。“d”规定小数点右侧的最大位数

char(size)                 容纳固定长度的字符串，（可以容纳字母、数字以及特殊的字符） 在括号中规定字符串的最大长度

varchar(size）        容纳固定长度的字符串，（可以容纳字母、数字以及特殊的字符） 在括号中规定字符串的最大长度      

date(yyyymmdd)     容纳日期

实例

创建名为Person的表

create table Person

{

Id_P  int， 

LastName  varchar（255）

FirstName  varchar （255）

Address     varchar （255）

city    varchar（255）

}

Id_P 列的数据类型为int 包含整数，其余4列的数据类型是varchar，最大长度为255个字符，空的Person表为

Id_P     LastName    FirstName   Address   city

可以使用insert into语句向空表中写入数据

5.SQL约束

约束用于限制加入表的数据的类型

可以在创建表时规定约束（通过create table语句），或者在表创建以后也可以，（通过Alert table语句）

下面将来讲以下几种约束（not null  UNIQUE  PRIMARY KEY  FOREIGN KEY CHECK  DEFAULT）

（1）NOT  NULL

SQL NOT 约束

约束强制列不接受NULL值

NOT NULL约束强制字段始终包含值，意为，如果不同字段添加值，就无法插入新纪录或者更新记录

下面SQL语句强制Id_列和firstName列不接受NULL值

create table Person

{

Id_P  int  NOT NULL， 

LastName  varchar（255）

FirstName  varchar （255） NOT NULL

Address     varchar （255）

city    varchar（255）

}

（2）Unique 约束

Unique 约束唯一标识的数据库表中的每条记录。

Unique和PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。

PRIMARY KEY拥有自定义的UNIQUE约束

注：每个表可以有多个Unique约束，但是每个表只能有一个PRIMARY KEY约束

SQL Unique Contraint  on   create table

下面的SQL在Persons 表创建时在Id_P列创建UNIQUE约束

MySQL

create table Person

{

Id_P  int NOT NULL， 

LastName  varchar（255）NOT NULL，

FirstName  varchar （255），

Address     varchar （255），

city    varchar（255），

UNIQUE（Id_P）

}

SQL Server /Oracle/MS Access:

create table Person

{

Id_P  int NOT NULL UNIQUE， 

LastName  varchar（255）NOT NULL，

FirstName  varchar （255），

Address     varchar （255），

city    varchar（255）

}

如果需要命名UNIQUE约束，以及为多个列定义UNIQUE约束，请使用下面的SQL语法

create table Person

{

Id_P  int  NOT NULL， 

LastName  varchar（255），NOT NULL

FirstName  varchar （255），

Address     varchar （255），

city    varchar（255），

constraint uc_PersonID UNIQUE  (Id_P,LastName)

}

SQL UNIQUE Contraint on ALTER TABLE

当表已经被建立的时候，如需在Id_P列中创建UNIQUE约束，使用

MySQL/SQL Server/Oracle/MS Access

Alert Table Person

ADD UNIQUE （Id_P）

如需要命名UNIQUE约束，并且定义多个列的UNIQUE约束，请使用

MySQL/SQL Server/Oracle/MS Access

ALERT TABLE Person

ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)

撤销UNIQUE约束

如需要撤销UNIQUE约束，

MySql 

ALERT Table Person

SQL Server/Oracle/MS Access

ALERT TABLE Person

drop constraint uc_PersonID

(3)SQL PRIMARY KEY约束

约束唯一标示数据库表中的每条记录

主键必须包含唯一的值  主键不能包含NULL值

每个表都应该有一个主键，并且每个表只能有一个主键

SQL PRIMARY KEY  Constraint on create TABLE

在Person表中创建Id_P列创建PRIMARY KEY约束

MySQL

CREATE TABLE PERSON

{

Id_P int NOT NULL,

FirstName varchar（255），

LastName varchar （255）NOT NULL，

Address varchar （255），

City varchar（255），

PRIMARY KEY （Id_P）

}

SQL Server/Oracle/Ms Access

CREATE TABLE PERSON

{

Id_P int NOT NULL PRIMARY KEY，

FirstName varchar（255），

LastName varchar （255）NOT NULL，

Address varchar （255），

City varchar（255），

}

但是如果需要命名PRIMARY KEY约束，以及为多个列定义PRIMARY KEY约束，则

CREATE TABLE PERSON

{

Id_P int NOT NULL,

FirstName varchar（255），

LastName varchar （255）NOT NULL，

Address varchar （255），

City varchar（255），

Constraint  pk_Person  PRIMARY KEY （Id_P,LastName）

}

SQL PRIMARY KEY constraint on Alert TABLE

如果在表中已经存在的情况下位Id_P列创建PRIMARY KEY约束，

MySQL/SQL Server/Oracle/Ms Access

Alert TABLE Person

ADD PRIMARY KEY （Id_P）

但是如果需要命名PRIMARY KEY约束，以及为多个列定义PRIMARY KEY约束，

MySQL/SQL Server/Oracle/Ms Access

Alert TABLE Person

ADD   Constraint pk_PersonId     PRIMARY KEY （Id_P,LastName）

注：

如果使用Alert  TABLE 添加主键，必须把主键列声明为不含NULL值（在表首次创建时）

撤销 PRIMARY KEY约束

如果撤销Primary约束

则 MYSQL

ALERT Table Person   DROP PRIMARY KEY

SQL Server/Oracle/MS Access

ALERT Table Person   DROP Constraint pk_PersonID

(4) SQL FOREIGN KEY 约束

一个表中的FOREIGN KEY指向另一个表中的PRIMARY KEY约束

例如

Person表

Id_P     LastName    FirstName    City

1               adams       john           London

2               bush          george       New York

3               carter        thomas       BeiJIng

Number表

Id_O           number            Id_P

1                 7777                3

2                 4444                 3

3                 2222                 1

4                 2333                  1

注意：Number表中的Id_P列指向Person表中的Id_P列

Person表中的Id_P列式Person表中的PRIMARY KEY

Number表中的Id_P列是Number表中的FOREIGN KEY

FOREIGN KEY约束用于预防破坏表之间连接的动作

FOREIGN Key 约束也能防止非法数据插入外键列，因为他必须是他指向的那个表中的值之一

SQL FOREIGN KEY  Constraint on create TABLE

在Number表创建时为Id_P列创建FOREIGN KEY

MySQL

CREATE TABLE Number

{

Id_O int NOT NULL,

number  int NOT Null，

Id_P int,

PRIMARY KEY （Id_O）,

FOREIGN KEY （Id_P）REFERENCES Person（Id_P）

}

SQL Server/Oracle/Ms Access

CREATE TABLE Number

{

Id_O int NOT NULL PRIMARY KEY,

number  int NOT Null，

Id_P int  FOREIGN KEY （Id_P）REFERENCES Person（Id_P）

}

但是如果需要命名FOREIGN KEY约束，以及为多个列定义FOREIGN KEY约束，则

MySQL/SQL Server/Oracle/Ms Access

CREATE TABLE Number

{

Id_O int NOT NULL,

number  int NOT Null，

Id_P int,

PRIMARY KEY （Id_O）,

 constraint  fk_PerOrders    FOREIGN KEY （Id_P）REFERENCES Person（Id_P）

}

SQL FOREIGN KEY constraint on Alert TABLE

如果在表中已经存在的情况下位Id_P列创建FOREIGN KEY约束，

MySQL/SQL Server/Oracle/Ms Access

Alert table Number

add  FOREIGN KEY （Id_P）

references Person(Id_P)

但是如果需要命名FOREIGN KEY约束，以及为多个列定义FOREIGN KEY约束，则

MySQL/SQL Server/Oracle/Ms Access

Alert table Number

add constraint fk_PerOrder  FOREIGN KEY （Id_P）

references Person(Id_P)

撤销FOREIGN Key约束

MySQL

Alert table Number

drop FOREIGN Key fk_PerNumber

SQL/Server/Oracle/Ms Access

Alert table Number

drop constraint fk_PerNumbers

(5)SQL CHECK约束

check约束用于限制列中值的范围

如果对单个列定义check约束，那么该列只允许特定的值

如果对一个表定义check约束，那么此约束会在特定的列中对值进行限制。

在Person表中创建Id_P列创建check约束，check约束规定Id_P列必须只包含大于0的整数

MySQL

CREATE TABLE PERSON

{

Id_P int NOT NULL,

FirstName varchar（255），

LastName varchar （255）NOT NULL，

Address varchar （255），

City varchar（255），

CHECK （Id_P>0）

}

SQL Server/Oracle/Ms Access

CREATE TABLE PERSON

{

Id_P int NOT NULL CHECK (Id_P>0),

FirstName varchar（255），

LastName varchar （255）NOT NULL，

Address varchar （255），

City varchar（255），

}

但是如果需要命名CHECK约束，以及为多个列定义CHECK约束，则

MySQL/SQL Server/Oracle/Ms Access

CREATE TABLE PERSON

{

Id_P int NOT NULL,

FirstName varchar（255），

LastName varchar （255）NOT NULL，

Address varchar （255），

City varchar（255），

constraint  chk_Person check （Id_P>0 AND City='sandnes'）

}

SQL check  constraint on Alert TABLE 

如果在表已经存在的情况下为Id_P列创建check约束，

MySQL/SQL Server/Oracle/Ms Access

Alert Table Person

ADD check （Id_P>0）

如果需要命名check约束，以及为多个列定义check约束，

MySQL/SQL Server/Oracle/Ms Access

Alert Table Person

ADD constraint  chk_Person  check （Id_P>0 and city='Sandnes'）

撤销check约束

如果撤销check约束，使用下面语句

SQL Server/Oracle/Ms Access

Alert  TABLE Person

DROP constraint chk_Person

MySQL

Alert  TABLE  Person

DROP  check chk_Person

(6)SQL Default约束

用于向列中插入默认值

如果没有规定其他的值，那么会将默认值添加到所有的新纪录。

SQL Default constraint on create TABLE

SQL在Person 表创建时为city 列创建Default约束

MySQL/SQL Server/Oracle/Ms Access

CREATE TABLE PERSON

{

Id_P int NOT NULL,

FirstName varchar（255），

LastName varchar （255）NOT NULL，

Address varchar （255），

City varchar（255）Default ‘Sandnes’

}

通过使用类似getdate()这样的函数，Default 约束也可以用于插入系统值

create  TABLE number

{

  Id_O int  NOT NULL，

orderNO int  NOT  NULL，

Id_P int,

numberDate date Default getdate()

}

SQL Default constraint on alert TABLE

如果在表已经存在的情况下，为city列创建Default约束，

MySQL

Alert TABLE Person

Alert city set Default ‘SANDNES’

SQL Server/Oracle/MS Access

Alert  TABLE Person

Alert  column  city   set  Default ’sandnes‘ 

撤销Default约束

如果撤销Default约束，请使用下面的SQL

MySQL

Alert   TABLE Person

Alert city DROP DeFault

SQL Server/Oracle/MS Access

Alert  TABLE Person

Alert  column city  DROP Default

本文是看网上资料整理的笔记