11.12 SQL学习

也就相当于复习~~~8号看的现在才进行整理，omg金铲铲上瘾了我恨！！！！

#11.8

十五、撤销索引、撤销表以及撤销数据库

DROP INDEX 语句

DROP INDEX 语句用于删除表中的索引。

用于 MySQL 的 DROP INDEX 语法

ALTER TABLE table_name DROP INDEX index_name;

DROP TABLE 语句

DROP TABLE 语句用于删除表。

DROP TABLE table_name;

DROP DATABASE 语句

DROP DATABASE database_name;

TRUNCATE TABLE 语句

如果我们仅仅需要删除表内的数据，但并不删除表本身，使用 TRUNCATE TABLE 语句：

TRUNCATE TABLE table_name;

十六、ALTER TABLE 语句

ALTER TABLE 语句用于在已有的表中添加、删除或修改列。

语法

如需在表中添加列，请使用下面的语法:

ALTER TABLE table_name
ADD column_name datatype;

如需删除表中的列，请使用下面的语法（请注意，某些数据库系统不允许这种在数据库表中删除列的方式）：

ALTER TABLE table_name
DROP COLUMN column_name;

要改变表中列的数据类型，请使用下面的语法：

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

SQL ALTER TABLE 实例

"Persons" 表：

在 "Persons" 表中添加一个名为 "DateOfBirth" 的列。

使用下面的 SQL 语句：

ALTER TABLE persons
ADD DateOfBirth date;

运行结果

请注意，新列 "DateOfBirth" 的类型是 date，可以存放日期。数据类型规定列中可以存放的数据的类型。

改变数据类型实例

想要改变 "Persons" 表中 "DateOfBirth" 列的数据类型。

ALTER TABLE persons
MODIFY column DateOfBirth year;

请注意，现在 "DateOfBirth" 列的类型是 year，可以存放 2 位或 4 位格式的年份。

DROP COLUMN 实例

接下来，我们想要删除 "Person" 表中的 "DateOfBirth" 列。

使用下面的 SQL 语句：

ALTER TABLE Persons
DROP COLUMN DateOfBirth;

运行结果

十七、AUTO INCREMENT 字段

Auto-increment 会在新记录插入表中时生成一个唯一的数字。

我们通常希望在每次插入新记录时，自动地创建主键字段的值。

我们可以在表中创建一个 auto-increment 字段。

下面的 SQL 语句把 "Persons" 表中的 "ID" 列定义为 auto-increment 主键字段：

语法

CREATE TABLE Persons
(
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (ID)
)

MySQL 使用 AUTO_INCREMENT 关键字来执行 auto-increment 任务。

默认地，AUTO_INCREMENT 的开始值是 1，每条新记录递增 1。

要让 AUTO_INCREMENT 序列以其他的值起始，请使用下面的 SQL 语法：

ALTER TABLE Persons AUTO_INCREMENT=100;

（先定义表，再定义初始值，随后再添加数据）

添加数据后，运行结果

 

要在 "Persons" 表中插入新记录，我们不必为 "ID" 列规定值（会自动添加一个唯一的值）：

INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen');

运行结果

十八、视图（Views）///没有找到相关的表  看不太懂

视图是可视化的表。

1.创建视图

CREATE VIEW 语句

在SQL中，视图是基于SQL语句的结果集的可视化的表。

视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。

可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，也可以呈现数据，就像这些数据来自于某个单一的表一样。

语法

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

注释：视图总是显示最新的数据！每当用户查询视图时，数据库引擎通过使用视图的SQL语句重建数据。

SQL CREATE VIEW 实例

样本数据库 Northwind 拥有一些被默认安装的视图。

视图 "JIAGE_WWEI_SANSIJIU" 会从 "Products" 表列出所有价格为3.49的商品。这个视图使用下面的 SQL 创建：

CREATE VIEW JIAGE_WWEI_SANSIJIU AS
SELECT prod_id,vend_id, prod_name
FROM products
WHERE prod_price = 3.49;

我们可以像这样查询上面这个视图：

SELECT*FROM JIAGE_WWEI_SANSIJIU;

运行结果

 

在上一个视图中查询“Fish bean bag toy”

CREATE VIEW FIshing AS
SELECT prod_id,vend_id
FROM JIAGE_WWEI_SANSIJIU
WHERE prod_name = 'Fish bean bag toy';

我们可以像这样查询上面这个视图：

SELECT*FROM FIshing;

运行结果

2.更新视图

CREATE OR REPLACE VIEW 语法

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

 但是在运行过程中使用ALTER语句

ALTER VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

现在，我们希望向 "JIAGE_WWEI_SANSIJIU" 视图添加 "prod_price" 列。我们将通过下列 SQL 更新视图：

ALTER VIEW JIAGE_WWEI_SANSIJIU AS
SELECT prod_id,vend_id, prod_name,prod_price
FROM products
WHERE prod_price = 3.49;

3.删除视图

可以通过 DROP VIEW 命令来删除视图。

DROP VIEW 语法

DROP VIEW view_name;

视图的作用：

1、视图隐藏了底层的表结构，简化了数据访问操作，客户端不再需要知道底层表的结构及其之间的关系。

2、视图提供了一个统一访问数据的接口。（即可以允许用户通过视图访问数据的安全机制，而不授予用户直接访问底层表的权限）

3、从而加强了安全性，使用户只能看到视图所显示的数据。

4、视图还可以被嵌套，一个视图中可以嵌套另一个视图。

十九、Date 函数

日期（Dates）

当我们处理日期时，最难的任务恐怕是确保所插入的日期的格式，与数据库中日期列的格式相匹配。

只要您的数据包含的只是日期部分，运行查询就不会出问题。但是，如果涉及时间部分，情况就有点复杂了。

在讨论日期查询的复杂性之前，我们先来看看最重要的内建日期处理函数。

MySQL Date 函数

下面的表格列出了 MySQL 中最重要的内建日期函数：

函数	描述
NOW()	返回当前的日期和时间
CURDATE()	返回当前的日期
CURTIME()	返回当前的时间
DATE()	提取日期或日期/时间表达式的日期部分
EXTRACT()	返回日期/时间的单独部分
DATE_ADD()	向日期添加指定的时间间隔
DATE_SUB()	从日期减去指定的时间间隔
DATEDIFF()	返回两个日期之间的天数
DATE_FORMATE()	用不同的格式显示日期/时间

Date 数据类型

MySQL 使用下列数据类型在数据库中存储日期或日期/时间值：

• DATE - 格式：YYYY-MM-DD
• DATETIME - 格式：YYYY-MM-DD HH:MM:SS
• TIMESTAMP - 格式：YYYY-MM-DD HH:MM:SS
• YEAR - 格式：YYYY 或 YY

注释：当您在数据库中创建一个新表时，需要为列选择数据类型！

如果不涉及时间部分，那么我们可以轻松地比较两个日期！

"Orders" 表：

我们希望从上表中选取 OrderDate 为 "2008-11-11" 的记录。

我们使用下面的 SELECT 语句

SELECT * FROM Orders WHERE OrderDate='2008-11-11';

假设 "Orders" 表如下所示（请注意 "OrderDate" 列中的时间部分）：

OrderId	ProductName	OrderDate
1	Geitost	2008-11-11 13:23:44
2	Camembert Pierrot	2008-11-09 15:45:21
3	Mozzarella di Giovanni	2008-11-11 11:12:01
4	Mascarpone Fabioli	2008-10-29 14:56:59

如果我们使用和上面一样的 SELECT 语句：

SELECT * FROM Orders WHERE OrderDate='2008-11-11';

或 

SELECT * FROM Orders WHERE OrderDate='2008-11-11 00：00：00';

那么我们将得不到结果！因为表中没有"2008-11-11 00:00:00"日期。如果没有时间部分，默认时间为 00:00:00。

提示：如果您希望使查询简单且更易维护，那么请不要在日期中使用时间部分！

二十、NULL 值

NULL 值代表遗漏的未知数据。

默认地，表的列可以存放 NULL 值

NULL 值

如果表中的某个列是可选的，那么我们可以在不向该列添加值的情况下插入新记录或更新已有的记录。这意味着该字段将以 NULL 值保存。

NULL 值的处理方式与其他值不同。

NULL 用作未知的或不适用的值的占位符。

注释：无法比较 NULL 和 0；它们是不等价的。

SQL 的 NULL 值处理

请看下面的 "Persons" 表：

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola		Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari		Stavanger

假如 "Persons" 表中的 "Address" 列是可选的。这意味着如果在 "Address" 列插入一条不带值的记录，"Address" 列会使用 NULL 值保存。

那么我们如何测试 NULL 值呢？

无法使用比较运算符来测试 NULL 值，比如 =、< 或 <>。

我们必须使用 IS NULL 和 IS NOT NULL 操作符。

IS NULL

选取在 "Address" 列中带有 NULL 值的记录

使用 IS NULL 操作符：

select renmenID, LastName,FirstName from renmen where Address IS NULL;

运行结果

选取在 "Address" 列中不带有 NULL 值的记录

使用 IS NOT NULL 操作符：

select renmenID, LastName,FirstName from renmen where Address IS NOT NULL;

运行结果

##11.12

二十一、NULL 函数

SQL ISNULL()、NVL()、IFNULL() 和 COALESCE() 函数

"Products" 表：

P_Id	ProductName	UnitPrice	UnitsInStock	UnitsOnOrder
1	Jarlsberg	10.45	16	15
2	Mascarpone	32.56	23
3	Gorgonzola	15.67	9	20

假如 "UnitsOnOrder" 是可选的，而且可以包含 NULL 值。

使用下面的 SELECT 语句：

SELECT ProductName,UnitPrice*(UnitsInStock+UnitsOnOrder)
FROM Products;

运行结果

 

在上面的实例中，如果有 "UnitsOnOrder" 值是 NULL，那么结果是 NULL。

MySQL 也拥有类似 ISNULL() 的函数。不过它的工作方式与微软的 ISNULL() 函数(返回的NULL值为0)有点不同。

在 MySQL 中，我们可以使用 IFNULL() 函数，如下所示：

SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))
FROM Products;

运行结果

 

或者我们可以使用 COALESCE() 函数，如下所示：

SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))
FROM Products;

运行结果

即把NULL值看作0进行数据处理 

二十二、通用数据类型

数据类型定义列中存放的值的种类。

数据库表中的每个列都要求有名称和数据类型。

SQL 开发人员必须在创建 SQL 表时决定表中的每个列将要存储的数据的类型。数据类型是一个标签，是便于 SQL 了解每个列期望存储什么类型的数据的指南，它也标识了 SQL 如何与存储的数据进行交互。

下面的表格列出了 SQL 中通用的数据类型：

数据类型	描述
CHARACTER(n)	字符/字符串。固定长度 n。
VARCHAR(n)或 CHARACTER VARYING(n)	字符/字符串。可变长度。最大长度 n。
BINARY(n)	二进制串。固定长度 n。
BOOLEAN	存储 TRUE 或 FALSE 值
VARBINARY(n) 或 BINARY VARYING(n)	二进制串。可变长度。最大长度 n。
INTEGER(p)	整数值（没有小数点）。精度 p。
SMALLINT	整数值（没有小数点）。精度 5。
INTEGER	整数值（没有小数点）。精度 10。
BIGINT	整数值（没有小数点）。精度 19。
DECIMAL(p,s)	精确数值，精度 p，小数点后位数 s。例如：decimal(5,2) 是一个小数点前有 3 位数，小数点后有 2 位数的数字。
NUMERIC(p,s)	精确数值，精度 p，小数点后位数 s。（与 DECIMAL 相同）
FLOAT(p)	近似数值，尾数精度 p。一个采用以 10 为基数的指数计数法的浮点数。该类型的 size 参数由一个指定最小精度的单一数字组成。
REAL	近似数值，尾数精度 7。
FLOAT	近似数值，尾数精度 16。
DOUBLE PRECISION	近似数值，尾数精度 16。
DATE	存储年、月、日的值。
TIME	存储小时、分、秒的值。
TIMESTAMP	存储年、月、日、小时、分、秒的值。
INTERVAL	由一些整数字段组成，代表一段时间，取决于区间的类型。
ARRAY	元素的固定长度的有序集合
MULTISET	元素的可变长度的无序集合
XML	存储 XML 数据

二十三、用于各种数据库的数据类型

MySQL 数据类型

在 MySQL 中，有三种主要的类型：Text（文本）、Number（数字）和 Date/Time（日期/时间）类型。

Text 类型：

数据类型	描述
CHAR(size)	保存固定长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的长度。最多 255 个字符。
VARCHAR(size)	保存可变长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的最大长度。最多 255 个字符。注释：如果值的长度大于 255，则被转换为 TEXT 类型。
TINYTEXT	存放最大长度为 255 个字符的字符串。
TEXT	存放最大长度为 65,535 个字符的字符串。
BLOB	用于 BLOBs（Binary Large OBjects）。存放最多 65,535 字节的数据。
MEDIUMTEXT	存放最大长度为 16,777,215 个字符的字符串。
MEDIUMBLOB	用于 BLOBs（Binary Large OBjects）。存放最多 16,777,215 字节的数据。
LONGTEXT	存放最大长度为 4,294,967,295 个字符的字符串。
LONGBLOB	用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。
ENUM(x,y,z,etc.)	允许您输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值，则插入空值。 注释：这些值是按照您输入的顺序排序的。 可以按照此格式输入可能的值： ENUM('X','Y','Z')
SET	与 ENUM 类似，不同的是，SET 最多只能包含 64 个列表项且 SET 可存储一个以上的选择。

Number 类型：

数据类型	描述
TINYINT(size)	带符号-128到127 ，无符号0到255。
SMALLINT(size)	带符号范围-32768到32767，无符号0到65535, size 默认为 6。
MEDIUMINT(size)	带符号范围-8388608到8388607，无符号的范围是0到16777215。 size 默认为9
INT(size)	带符号范围-2147483648到2147483647，无符号的范围是0到4294967295。 size 默认为 11
BIGINT(size)	带符号的范围是-9223372036854775808到9223372036854775807，无符号的范围是0到18446744073709551615。size 默认为 20
FLOAT(size,d)	带有浮动小数点的小数字。在 size 参数中规定显示最大位数。在 d 参数中规定小数点右侧的最大位数。
DOUBLE(size,d)	带有浮动小数点的大数字。在 size 参数中规显示定最大位数。在 d 参数中规定小数点右侧的最大位数。
DECIMAL(size,d)	作为字符串存储的 DOUBLE 类型，允许固定的小数点。在 size 参数中规定显示最大位数。在 d 参数中规定小数点右侧的最大位数。

注意：以上的 size 代表的并不是存储在数据库中的具体的长度，如 int(4) 并不是只能存储4个长度的数字。

实际上int(size)所占多少存储空间并无任何关系。int(3)、int(4)、int(8) 在磁盘上都是占用 4 btyes 的存储空间。就是在显示给用户的方式有点不同外，int(M) 跟 int 数据类型是相同的。

例如：

1、int的值为10 （指定zerofill）

int（9）显示结果为000000010

int（3）显示结果为010

就是显示的长度不一样而已 都是占用四个字节的空间。

Date 类型：

数据类型	描述
DATE()	日期。格式：YYYY-MM-DD 注释：支持的范围是从 '1000-01-01' 到 '9999-12-31'
DATETIME()	*日期和时间的组合。格式：YYYY-MM-DD HH:MM:SS 注释：支持的范围是从 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'
TIMESTAMP()	*时间戳。TIMESTAMP 值使用 Unix 纪元('1970-01-01 00:00:00' UTC) 至今的秒数来存储。格式：YYYY-MM-DD HH:MM:SS 注释：支持的范围是从 '1970-01-01 00:00:01' UTC 到 '2038-01-09 03:14:07' UTC
TIME()	时间。格式：HH:MM:SS 注释：支持的范围是从 '-838:59:59' 到 '838:59:59'
YEAR()	2 位或 4 位格式的年。 注释：4 位格式所允许的值：1901 到 2155。2 位格式所允许的值：70 到 69，表示从 1970 到 2069。

*即便 DATETIME 和 TIMESTAMP 返回相同的格式，它们的工作方式很不同。在 INSERT 或 UPDATE 查询中，TIMESTAMP 自动把自身设置为当前的日期和时间。TIMESTAMP 也接受不同的格式，比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。