MySQL进阶_create table篇

MySQL中create table语句的基本语法是：
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
[table_options] [select_statement]
TEMPORARY：该关键字表示用create table新建的表为临时表，此表在当前会话结束后将自动消失。临时表主要被应用于存储过程中，对于目前尚不支持存储过程的MySQL，该关键字一般不用。
IF NOT EXISTS：实际上是在建表前加上一个判断，只有该表目前尚不存在时才执行create table操作。用此选项可以避免出现表已经存在无法再新建的错误。
tbl_name：你所要创建的表的表名。该表名必须符合标识符规则。通常的做法是在表名中仅使用字母、数字及下划线。例如titles、our_sales、my_user1等都应该算是比较规范的表名。
create_definition：这是create table语句中关键部分所在。在该部分具体定义了表中各列的属性。
create_definition的基本语句是：
col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
[PRIMARY KEY] [reference_definition]
or PRIMARY KEY (index_col_name,...)
or KEY [index_name] (index_col_name,...)
or INDEX [index_name] (index_col_name,...)
or UNIQUE [INDEX] [index_name] (index_col_name,...)
or [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...)
[reference_definition]
or CHECK (expr)
col_name：表中列的名字。必须符合标识符规则，而且在表中要唯一。
type：列的数据类型。有的数据类型需要指明长度n，并用括号括起。目前MySQL提供的数据类型详见MySQL进阶_列类型篇。
NOT NULL | NULL：指定该列是否允许为空。如果既不指定NULL也不指定NOT NULL，列被认为指定了NULL。
DEFAULT default_value：为列指定默认值。如果没有为列指定默认值，MySQL自动地分配一个。如果列可以取NULL作为值，缺省值是NULL。如果列被声明为NOT NULL，缺省值取决于列类型： 1、对于没有声明AUTO_INCREMENT属性的数字类型，缺省值是0。对于一个AUTO_INCREMENT列，缺省值是在顺序中的下一个值。
　2、对于除TIMESTAMP的日期和时间类型，缺省值是该类型适当的“零”值。对于表中第一个TIMESTAMP列，缺省值是当前的日期和时间。3、对于除ENUM的字符串类型，缺省是空字符串。对于ENUM，缺省值是第一个枚举值。
AUTO_INCREMENT：设置该列有自增属性，只有整型列才能设置此属性。当你插入NULL值或0到一个AUTO_INCREMENT列中时，列被设置为value 1，在这里value是此前表中该列的最大值。AUTO_INCREMENT顺序从1开始。每个表只能有一个AUTO_INCREMENT列，并且它必须被索引。

交互式SQL（Transact SQL，TSQL）
它是SQL Server的查询语言。提供以下命令：
· 创建和数据库对象。
· 访问和修改数据。
· 数据聚合（aggregation，又称聚集）。
· 实施安全措施。
数据库对象
数据库对象是数据库中的物理对象。这些对象拥有唯一的名字，并保存数据和数据关系信息。SQL Server定义了如下几种对象：
1、 表（table）
表是用来存储数据的两维数组，它有行和列。列也称为表属性或字段，表中的每一列拥有唯一的名字，每一列包含具体的数据类型，这个数据类型由列中的数据类型定义。
2、 视图（view）
视图是虚拟的表，它只包含表的一部分。与表不同的是，保存在视图中的数据并不是物理存储的数据，它是由表派生的，派生表被称为视图的基本表。视图的定义存储在数据库中。
3、 约束条件（constraint）
约束条件定义数据的完整性和有效性。约束条件为列中的值建立规则。换而言之，如果在某一列上定义了结束条件，则插入这一列的每一个值需要通过约束条件检查。约束条件是在触发器和规则上保证数据完整性和有效性的较佳选择。SQL Server Query Optimizer利用约束条件产生出低成本的查询计划。约束条件有如下类型：
· NOT NULL 这个约束条件要求列中不能有NULL值。
· CHECK 为列指定能拥有的值的集合后，检查约束条件。列中任何在定义之外的数据都为无效数据。有效值集合称为列的域。
· PRIMARY KEY 主关键字是列或列组合，它用来唯一标识一行。
· FOREIGN KEY 用来定义两个表之间的父子关系。如果一个关键字既是一个表的主关键字的一部分，同时又是另一个表的主关键字，则称它为外来关键字。外来关键字用来定义数据的引用完整性。
· UNIQUE 唯一约束条件是指无任何两行在列中有相同的NON-NULL值。唯一性由主关键字保证，但他们不允许NULL值，并且每一个表只能有一个关键字。
4、 默认值（default）
默认值是为列定义的值，如果在插入一行时没有提供某一列的值，则此列使用默认值，默认值可以是以下的一种：
· 常量
· 数学表达式
· 内部函数（Built-in function）
5、 规则（rule）
规则执行和CHECK约束条件同样的功能。但规则和约束条件不同的是：规则作为独立的对象存在，它可以用于多个表，而约束条件作为表的一部分存储。然而，规则是作为向后兼容特点而提供的，建议用户使用约束条件。
如何不通过其他工具，把图片、声音等存储到sql中
用image类型
方法：
1、建立过程
CREATE PROCEDURE sp_textcopy (
@srvname varchar (30),
@login varchar (30),
@password varchar (30),
@dbname varchar (30),
@tbname varchar (30),
@colname varchar (30),
@filename varchar (30),
@whereclause varchar (40),
@direction char(1))
AS
DECLARE @exec_str varchar (255)
SELECT @exec_str =
'textcopy /S ' @srvname
' /U ' @login
' /P ' @password
' /D ' @dbname
' /T ' @tbname
' /C ' @colname
' /W "' @whereclause
'" /F ' @filename
' /' @direction
EXEC master..xp_cmdshell @exec_str
2、建表和初始化数据
create table 表名 (编号 int,image列名 image)
go
insert 表名 values(1,0x) -- 必须的，且不是null
insert 表名 values(2,0x) -- 必须的，且不是null
go
3、读入
sp_textcopy '你的服务器名','sa','你的密码','库名','表名','image列名','c:图片.bmp','where 编号=1','I' --注意条件是 编号=1
sp_textcopy '你的服务器名','sa','你的密码','库名','表名','image列名','c:b.doc','where 编号=2','I' --注意条件是 编号=2
go
4、读出成文件
sp_textcopy '你的服务器名','sa','你的密码','库名','表名','image列名','c:图片.bmp','where 编号=1','O' --注意条件是 编号=1
sp_textcopy '你的服务器名','sa','你的密码','库名','表名','image列名','c:b.doc','where 编号=2','O' --注意条件是 编号=2
go
如果报textcopy不是可执行文件的话，你就到
C:Program FilesMicrosoft SQL ServerMSSQLBinn
目录下拷备 textcopy.exe到：
C:Program FilesMicrosoft SQL Server80ToolsBinn

cube操作符
要使用cube，首先要了解group by
其实cube和rollup区别不太大，只是在基于group by 子句创建和汇总分组的可能的组合上有一定差别，
cube将返回的更多的可能组合。如果在 group by 子句中有n个列或者是有n个表达式的话，
sqlserver在结果集上会返回2的n－1次幂个可能组合。
注意：
使用cube操作符时，最多可以有10个分组表达式
在cube中不能使用all关键字
例子：
我们在数据库统计中常常要查询以下情况：
如一个定单数据库，我们要知道每个定单的每个产品数量，每个定单的所有产品数量，所有定单的某一产品数量，所有定单所有产品总量这些汇总信息。这时使用cube就十分方便了。当然不需要这么多信息或者只想知道某一具体产品、具体某一定单，某一时间关系（前，后，之间）等等具体信息的话，只需在where中限定即可
先举一个例子，是所有情况的：
一个数据库表中记载了一个产品定购情况：
现共有三种产品(1,2,3)，已经下了两个定单(1,2)
sql语句：
select productid,orderid SUM(quantity) AS total FROM order GROUP BY productid,orderid WITH CUBE
ORDER BY productid,orderid
运行后得到结果：
productid orderid total
null null 95 所有定单所有产品总量
null 1 30 定单1所有产品数量
null 2 65 定单2所有产品数量
1 null 15 所有定单产品1总量
1 1 5 定单1产品1数量
1 2 10 定单2产品1数量
2 null 35 所有定单产品2总量
2 1 10 定单1产品2数量
2 2 25 定单2产品2数量
3 null 45 所有定单产品3总量
3 1 15 定单1产品3数量
3 2 30 定单2产品3数量
如果您对sqlserver group by 聚集有一定理解的话，您就可以理解cube操作符的用法和作用。其实在现实运用中cube还是很好有的，我们经常要对一些数据库数据进行统计，以利于我们更好的掌握情况
我想电子商务可以更好的实现合理配置资源，尽量减少库存，只有更好的掌握生产、销售数据的具体情况，才能实现资源的合理配置。希望以后的企业象dell一样，不要象长虹等彩电厂商。

SQL Server里函数的两种用法（可以代替游标）
1． 因为update里不能用存储过程，然而要根据更新表的某些字段还要进行计算。我们常常采用游标的方法，这里用函数的方法实现。
　
函数部分：
CREATE FUNCTION [DBO].[FUN_GETTIME] (@TASKPHASEID INT)
RETURNS FLOAT AS
BEGIN
DECLARE @TASKID INT,
@HOUR FLOAT,
@PERCENT FLOAT,
@RETURN FLOAT
IF @TASKPHASEID IS NULL
BEGIN
RETURN(0.0)
END
SELECT @TASKID=TASKID,@PERCENT=ISNULL(WORKPERCENT,0)/100
FROM TABLETASKPHASE
WHERE ID=@TASKPHASEID
SELECT @HOUR=ISNULL(TASKTIME,0) FROM TABLETASK
WHERE ID=@TASKID
SET @RETURN=@HOUR*@PERCENT
RETURN (@RETURN)
END
调用函数的存储过程部分
CREATE PROCEDURE [DBO].[PROC_CALCCA]
@ROID INT
AS
BEGIN
DECLARE @CA FLOAT
UPDATE TABLEFMECA
SET
Cvalue_M= ISNULL(MODERATE,0)*ISNULL(FMERATE,0)*ISNULL(B.BASFAILURERATE,0)*[DBO].[FUN_GETTIME](C.ID)
FROM TABLEFMECA ,TABLERELATION B,TABLETASKPHASE C
WHERE ROID=@ROID AND TASKPHASEID=C.ID AND B.ID=@ROID
SELECT @CA=SUM(ISNULL(Cvalue_M,0)) FROM TABLEFMECA WHERE ROID=@ROID
UPDATE TABLERELATION
SET CRITICALITY=@CA
WHERE ID=@ROID
END
GO
2． 我们要根据某表的某些记录，先计算后求和，因为无法存储中间值，平时我们也用游标的方法进行计算。但sqlserver2000里支持
SUM ( [ ALL | DISTINCT ] expression )
expression
是常量、列或函数，或者是算术、按位与字符串等运算符的任意组合。因此我们可以利用这一功能。
函数部分：
CREATE FUNCTION [DBO].[FUN_RATE] (@PARTID INT,@ENID INT,@SOURCEID INT, @QUALITYID INT,@COUNT INT)
RETURNS FLOAT AS
BEGIN
DECLARE @QXS FLOAT, @G FLOAT, @RATE FLOAT