纯属笔记记录------大部分内容来自公众号SQL数据库开发
数据库:SQL Servers
本博客主要介绍的还是SQL Servers背景下关于sql的一些基本用法。笔者自身学习的是MySQL,若有疑问,欢迎留言。
目录
1、CREATE
作用:通过使用CREATE语句,可以轻松地创建数据库、表、索引等。
CREATE DATABASE语句用于创建数据库:
CREATE DATABASE database_name
CREATE TABLE语句用于创建表:
CREATE TABLE table_name(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
...
);
CREATE INDEX语句:用于创建索引,在表上创建一个普通可以重复数据的索引语法
CREATE INDEX index_name ON table_name(column_name);
在表上创建一个唯一(数据不重复)的索引,只需添加UNIQUE关键字即可
CREATE UNIQUE INDEX index_name ON table_name(column_name)
在表上创建一个普通的联合索引:
CREATE INDEX index_name ON table_name(column_name1,column_name2)
实例:
CREATE INDEX index_city_provice
ON Customers(城市,省份 DESC)
# 对Customers中城市,省份建立一个索引
批注:CREATE是一个数据定义语句(DDL),主要用来定义各种对象(数据库,表,索引,视图等)
2、ALTER TABLE
ALTER TABLE语句用于在已有的表中添加、修改或删除列。
语法:
# 向表中添加列
ALTER TABLE table_name ADD column_name datatype;
# 删除表中的列
ALTER TABLE table_name
DROP COLUMN column_name
# 更改表中列的数据类型
# SQL Server/MS Access
ALTER TABLE table_name
ALTER COLUMN column_name datatype
# MySQL
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
or
ALTER TABLE table_name
CHANGE column_name column_name1 datatype;
# Oracle
ALTER TABLE table_name
MODIFY column_name datatype;
实例:
ALTER TABLE Customers
ADD 出生日期 DATE;
UPDATE Customers
SET 出生日期='1990-05-14';
# 在Customers表中添加新列DATE,然后更新DATE列中的数据
ALTER TABLE Customers
ALTER COLUMN 出生日期 DATETIME;
# 多表Customers中的列'出生日期'修改数据类型
ALTER TABLE Customers
DROP COLUMN 出生日期;
# 删除Customers中列'出生日期'
PS:ALTER TABLE可以对已有表进行修改,可以将表的列名称、列的数据类型进行增加、修改或删除操作。
3、VIEW视图
视图:可视化的表
(1)CREATE VIEW
在SQL中,视图表是基于SQL语句的结果集的可视化表。试图包含行和列,就像真正的表一样。视图中的字段是一个或多个数据库中真实表中的字段。
CREATE VIEW view_name AS
SELECT column_name
FROM table_name
WHERE condition
PS:视图总是显示最新数据。每当用户查询视图时,数据库引擎就使用视图的SQL语句重新构建数据。
实例
CREATE VIEW Customer_GD AS
SELECT *
FROM Customers
WHERE 省份='广东省';
SELECT * FROM Custormer_GD;
# 创建视图Custormer_GD:从表Custormers选出 省份='广东省' 的所有行。
CREATE VIEW [Products_Above_Average_Price] AS
SELECT 名称,价格
FROM Products
WHERE 价格>(SEELCT AVG(价格) FROM Products);
SELECT * FROM [Products_Above_Average_Price];
SELECT * FROM [Products_Above_Average_Price] WHERE 名称='大米';
更新视图:CREATE OR REPLACE VIEW
ALTER VIEW view_name AS
SELECT column_name
FROM table_name
WHERE condition
实例:
ALTER VIEW [Products_Above_Average_Price] AS
SELECT 名称,价格,规格
FROM Products
WHERE 价格>(SELECT AVG(价格) FROM Products);
# 更新视图Products_Above_Average_Price为SELECT之后新的表
SELECT * FROM [Products_Above_Average_Price];
删除视图:DROP VIEW
DROP VIEW [Products_Above_Average_Price];
PS:视图时数据库中一个比较重要的组成部分,在隔离实体表的前提下还可以让用户查询到需要的数据,可以起到保护底层数据的作用。同时针对不同的人群,视图也可以起到简化用户对数据的理解。
4、NULL
NULL空值代表丢失的未知数据。
NULL值:需用IS NULL或者IS NOT NULL操作
IS NULL语法:
SELECT * FROM Customers
WHERE 地址 IS NULL;
IS NOT NULL语法:
SELECT * FROM Customers
WHERE 地址 IS NOT NULL;
PS:NULL值在建表时可默认分配,在查询过程中不能使用比较操作符来进行筛选或查找,只能使用IS NULL和IS NOT NULL,否则会报错。
5、AS别名
使用SQL可以为表名称或列名称指定别名。
别名的作用:
1)SQL别名用于表或表中的列提供临时名称;
2)SQL别名通常用于是列名更具可读性;
3)SQL一个别名只存在于查询期间。
使用别名的场景:
1)查询涉及多个表
2)用于查询函数
3)需要把两个或更多的列放在一起
4)列名长或可读性差
列的别名语法
SELECT column_name AS alias_name
FROM table_name;
表的别名语法
SELECT column_name
FROM talab_name
AS alias_name;
实例:
# SQL中创建一个名为"地址"的别名,包含4列(姓名,省份,城市,地址和邮编)
SELECT 姓名,
省份+
城市+
地址+',邮编:'+
邮编 AS 地址
FROM Customers;
# MySQL中创建一个名为"地址"的别名,包含4列(姓名,省份,城市,地址和邮编)
SELECT 姓名,CONCAT(
省份,
城市,
地址,
',邮编:',
邮编
) AS 地址
FROM Customers;
# 表别名实例
SELECT c.姓名 AS Custormer,
o.订单日期 AS Orderdate
FROM Custormers AS c
JOIN Orders AS o
ON c.客户ID=o.客户ID;
PS:AS别名正在多表进行关联时可以很好处理表名相同的情况,比如两个表都存在姓名列,可以将A表的姓名命名成A_NAME,B表的姓名命名成B_NAME。此外,AS一般只对查询的列和表以及ORDER BY重命名的别名才有效。
6、约束
约束作用于数据表中列上的规则,用于限制数据表中数据的类型。约束的存在保证了数据库中数据的精确性和可靠性。
约束有列级和表级之分,列级约束作用于单一的列,而表级约束作用于整张数据表。
如下是SQL中常用的约束:
NOT NULL约束:保证列中数据不能NULL值。
DEFAULT约束:提供该列数据末指定时所采用的默认值。
UNIQUE约束:保证列中的所有数据各不相同。
主键约束:唯一标识数据表中的行/记录。
外键约束:唯一标识其它表中的一条行/记录。
CHECK约束:此约束保证列中所有值满足某一条件。
索引:用于在数据库中快速创建或检索数据。
约束可以在创建表时规定(通过CREATE TABLE语句),或者在表创建之后规定(通过ALTER TABLE语句)。
创建约束:
CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
...,
)
删除约束:
任何现有约束都可以通过在ALTER TABLE命令中指定DROP CONSTRAINT选项的方法删除。
ALTER TABLE EMPLOYEES DROP CONSTR;
在Oracle中删除一张表的逐渐约束,可以使用如下命令:
ALTER TABLE EMPLOYEES DROP PRIMAR
完整性约束:
完整性约束用于保证关系型数据库中数据的精确性和一致性。对于关系型数据库来说,数据完整性由参照完整性(regerential integrity,RI)来保证。
有多种约束可以起到参照完整性的作用,这些约束包括主键约束(Primary Key)、外键约束(Foreign Key)、唯一性约束(Unique Constraint)以及上面提到的其他约束。
PS:约束的种类有很多,通过约束我们可以让进入数据库的数据更加规范。
7、约束(实例)
(1)NOT NULL约束
NOT NULL约束强制列不接受NULL值。下面SQL强制表"Customers"的“客户ID”列和“姓名”列不接受NULL值:
CREATE TABLE Customers(
客户ID INT NOT NULL,
姓名 VARCHAR(10) NOT NULL,
地址 VARCHAR(50) NULL,
城市 VARCHAR(20) NULL,
邮编 CHAR(6) NULL,
省份 VARCHAR(20) NULL
);
(2)UNIQUE约束
UNIQUE约束唯一标识数据库表中的每条记录。UNIQUE和PRIMARY KEY约束为列或列集合提供了唯一性的保证。
PS:每个表可以有多个UNIQUE约束,但是每个表只能有一个PRIMARY KEY约束。
CREATE TABLE时的SQL UNIQUE约束,下面的SQL在“Order"表创建时在“订单ID”列上创建UNIQUE约束:
# MySQL
CREATE TABLE dbo.Orders(
订单ID INT NOT NULL,
客户ID INT NULL,
员工ID INT NULL,
订单日期 DATETIME NULL,
发货ID INT NULL,
UNIQUE (订单ID)
);
# SQL Server/Oracle/MS Access
CREATE TABLE dbo.Orders(
订单ID INT NOT NULL UNIQUE,
客户ID INT NULL,
员工ID INT NULL,
订单日期 DATETIME NULL,
发货ID INT NULL
);
唯一约束是被约束的列在插入新数据时,如果和已经存在的列有相同的值,则会报错。
如需命名UNIQUE约束,并定义多个列的UNIQUE约束,请使用下面的SQL语法:
# MySQL/SQL Server /Oracle/MS Access
CREATE TABLE dbo.Orders(
订单ID INT NOT NULL,
客户ID INT NULL,
员工ID INT NULL,
订单日期 DATETIME NULL,
发货ID INT NULL,
CONSTRAINT uc_OrderID UNIQUE (订单ID,发货ID)
);
(3) ALTER TABLE时的UNIQUE约束
当表被创建时,如需在“订单ID”列创建UNIQUE约束,请使用下面的SQL:
# MySQL/SQL Server /Oracle/MS Access
ALTER TABLE Orders
ADD UNIQUE (订单ID);
# 定义多个列的UNIQUE约束
ALTER TABLE Customers
ADD CONSTRAINT uc_CustomerID UNIQUE (客户ID,姓名);
(4) 删除UNIQUE约束
# MySQL
ALTER TABLE Orders
DROP INDEX uc_OrderID
# SQL Server /Oracle/MS Access
ALTER TABLE Customers
DROP CONSTRAINT uc_CustomerID
(5) PRIMARY KEY
PRIMARY KEY约束唯一标识数据库表中的每条记录。主键必须包含唯一的值,且不包含NULL值。每个表都应该有一个主键,且只有一个主键。
# MySQL
CREATE TABLE dbo.Orders(
订单ID INT NOT NULL,
客户ID INT NOT NULL,
员工ID INT NULL,
订单日期 DATETIME NULL,
发货ID INT NULL,
PRIMARY KEY (客户ID)
);
# SQL Server /Oracle/MS Access
CREATE TABLE dbo.Orders(
订单ID INT NOT NULL,
客户ID INT NOT NULL PRIMARY KEY,
员工ID INT NULL,
订单日期 DATETIME NULL,
发货ID INT NULL
);
# MySQL/SQL Server /Oracle/MS Access
CREATE TABLE dbo.Orders(
订单ID INT NOT NULL,
客户ID INT NOT NULL PRIMARY KEY,
员工ID INT NULL,
订单日期 DATETIME NULL,
发货ID INT NULL,
CONSTAINT pk_CustomerIID PRIMARY KEY (客户ID,订单)
);
(6)ALTER TABLE时的PRIMARY KEY约束
在已创建表中需要进行PRIMARY KEY约束时,使用ALTER TABLE。例如:
# MySQL/SQL Server /Oracle/MS Access
ALTER TABLE Orders
ADD PRIMARY KEY (订单ID);
# 定义多个列的PRIMARY KEY约束,被声明为主键的列不为NULL
ALTER TABLE Customers
ADD CONSTRAINT pk_CustomerID PRIMARY KEY (客户ID,姓名);
删除PRIMARY KEY约束
# MySQL
ALTER TABLE Customers
DROP PRIMARY KEY
# SQL Server/Oracle/MS Access
ALTER TABLE Cusromers
DROP CONSTRAINT pk_CustomerID
(7)FOREIGN KEY约束
一个表中的FOREIGN KEY是指向另一个表中的PRIMARY KEY。FOREIGN KEY约束用于预防破坏表之间连接的行为。FOREIGN KEY约束也能防止非法数据插入外键列,因为它必须是它指向的那个表中的值之一。
CREATE TABLE时的FOREIGN KEY约束:下面的SQL在“Oracle”表创建时在“客户ID”列上创建FOREIGN KEY约束:
# MySQL
CREATE TABLE Orders
(
订单ID INT NOT NULL,
客户ID INT NULL,
员工ID INT NULL,
订单日期 DATETIME NULL,
发货ID INT NULL,
PRIMARY KEY (订单ID),
FOREIGN KEY (客户ID) REFERENCES Customers(客户ID)
)
# SQL Server/Oracle/MS Access
CREATE TABLE Orders
(
订单ID INT NOT NULL PRIMARY KEY,
客户ID INT NULL,
员工ID INT NULL,
订单日期 DATETIME NULL,
发货ID INT NULL,
FOREIGN KEY (客户ID) REFERENCES Customers(客户ID)
)
如需命名FOREIGN KEY约束,斌定义多个列的FOREIGN KEY约束,请使用下面的SQL语法:
# MySQL/SQL Server/Oracle/MS access
CREATE TABLE Orders
(
订单ID INT NOT NULL,
客户ID INT NULL,
员工ID INT NULL,
订单日期 DATETIME NULL,
发货ID INT NULL,
PRIMARY KEY (订单ID),
CONSTRAINT fk_CusOrders FOREIGN KEY (客户ID) REFERENCES Customers(客户ID)
)
ALTER TABLE 时的FOREIGN KEY约束:当表已被建立,如果需要在“客户ID”列创建FOREIGN KEY约束,请使用下面的SQL:
% MySQL/SQL Server/Oracle/MS Access
ALTER TABLE Orders
ADD FOREIGN KEY (客户ID)
REFERENCES Customers(客户ID)
多列FOREIGN KEY约束,请使用下面的SQL语法:
# MySQL/SQL Server/Oracle/MS Access
ALTER TABLE Orders
ADD CONTRAINT fk_CusOrders
FOREIGN KEY (客户ID)
REFERENCES Customers(客户ID)
如需删除FOREIGN KEY约束,请使用下面的SQL:
# MySQL
ALTER TABLE Orders
DROP FOREIGN KEY fk_CusOrders
# SQL Server/Oracle/MS Access
ALTER TABLE Orders
DROP CONSTRAINT fk_CusOrders
(8) DEFAULT约束
DEFAULT约束用于向列中插入默认值。如果没有规定其他的值,那么会将默认值添加到所有的新纪录。
CREATE TABLE时的DEFAULT约束:
# MySQL/SQL Server/Oracle/MS Access
CREATE TABLE dbo.Customers(
客户ID INT NOT NULL,
姓名 VARCHAR(10) NULL,
地址 VARCHAR(50) NULL,
城市 VARCHAR(20) NULL DEFAULT '北京市',
邮编 CHAR(6) NULL,
省份 VARCHAR(20) NULL
);
SQL Server中通过使用类似GETDATE()这样的函数,DEFAULT约束也可以用于插入系统值:
CREATE TABLE dbo.Orders(
订单ID INT NOT NULL,
客户ID INT NULL,
员工ID INT NULL,
订单日期 DATETIME NULL DEFAULT GETDATE(),
发货ID INT NULL
);
ALTER TABLE 时的DEFAULT约束:当表已被创建时,如需在‘城市’列创建DEFAULT约束,请使用下面的SQL:
# MySQL
ALTER TABLE Customers
ALTER 城市 SET DEFAULT '北京市';
# SQL Server/MS Access
ALTER TABLE Customers
ADD CONSTRAINT DF_Customers DEFAULT('北京市') FOR 城市;
# Oracle
ALTER TABLE Customers
MODIFY 城市 DEFAULT '北京市';
删除DEFAULT 约束:
# MySQL
ALTER TABLE Customers
ALTER 城市 DROP DEFAULT;
# SQL Server/Oracle/MS Access
ALTER TABLE Customers
ALTER COLUMN 城市 DROP DEFAULT;
(9) CHECK约束
CHECK约束用于限制列中的值的范围。如果对单个列定义CHECK约束,那么该列只允许特定的值。如果对一个表定义CHECK约束,那么此约束会基于行中其他列的值在特定的列中对值进行限制。
CREATE TABLE时的CHECK约束:下面的SQL在“Customers”表创建时在“客户ID”列上创建CHECK约束。CHECK约束归档“客户ID”列必须只包含大于0的整数。
# MySQL
CREATE TABLE dbo.Customers(
客户ID INT NOT NULL,
姓名 VARCHAR(10) NULL,
地址 VARCHAR(50) NULL,
城市 VARCHAR(20) NULL,
邮编 CHAR(20) NULL,
省份 VARCHAR(20) NULL,
CHECK (客户ID>0)
);
# SQL Server/Oracle/MS Access
CREATE TABLE dbo.Customers(
客户ID INT NOT NULL CHECK (客户ID>0),
姓名 VARCHAR(10) NULL,
地址 VARCHAR(50) NULL,
城市 VARCHAR(20) NULL,
邮编 CHAR(20) NULL,
省份 VARCHAR(20) NULL,
);
定义多个列的CHECK约束,请使用下面的SQL约束:
# MySQL/SQL Server/Oracle/MS Access
CREATE TABLE dbo.Customers(
客户ID INT NOT NULL,
姓名 VARCHAR(10) NULL,
地址 VARCHAR(50) NULL,
城市 VARCHAR(20) NULL,
邮编 CHAR(20) NULL,
省份 VARCHAR(20) NULL,
CONSTRAINT chk_Customers CHECK (客户ID>0 AND 城市='北京市')
);
ALTER TABLE时的CHECK约束:当表被创建时,如需在“客户ID”列创建CHECK约束,请使用下面的SQL:
# MySQL/SQL Server/Oracle/MS Access
# 单列
ALTER TABLE Customers
ADD CHECK (客户ID>0);
# 多列
ALTER TABLE Customers
ADD CONSTRAINT chk_Customers CHECK(客户ID>0 AND 城市='北京市');
删除CHECK约束:
# SQL Server/Oracle/MS Access
ALTER TABLE Customers
DROP CONSTRAINT chk_Customers;
# MySQL
ALTER TABLE Customers
DROP CHECK chk_Customers;
PS:以上六种约束是工作中经常使用到的,主要还是用来规范数据,随着数据量的增多,如果不对表结构加以约束,随着数据量的增多,如果不对表结构加以约束,那么会有越来越多的“脏数据”进入数据库,这对业务系统来说非常不愿意碰到的。所以为能够高效的使用数据库,请从表结构的设计上下功夫。