第二十章
INSERT是用来插入(或添加)行到数据库表的。插入数据的几种方式:
1
2
3
4
INSERT
INSERT
SELECT
列省略
1
2
提高整体性能
更新和删除数据
1
2
更新行和删除行的时候一定要加上WHERE子句,否则后果自负
UPDATE
UPDATE
删除数据
为了从表中删除(去掉)数据,使用DELETE语句,可以使用两种方式:
2
更新和删除的指导原则
下面是许多sql程序员使用的UPDATE或DELETE是所遵循的习惯
第二十一章
创建表
一般两种创建表的方法
1
2
在使用交互式工具时,工具也是生成MySQL语句操纵数据库
表创建基础
为了利用CREATE
1
2
CREATE
(
column_id
columnOne
columnOne
column
.....
PRIMARY
)
使用NULL值
主键再介绍
索引创建
使用AUTO_INCREMENT
如果两个表关联,添加时一表时需要另一表的主键,该怎么获得呢:
指定默认值
MySQL不允许使用函数作为默认值,它只支持常量
引擎搜索
以下几个需要知道的引擎
1
更新表
CREATE
DORP
在整型数据列后加上
ZEROFILL
CREATE
Id
Num
Sex
Name
)
Mysql默认字符集设置:win
Character-set-server
Collation-server =gbk_chinese_ci;
创建数据库时设置字符集语句:
CREATE
数据库文件结构
一个MyISAM数据表会有三个文件,以.frm为后缀的结构定义文件,以.MYD为后缀名的数据文件,一个以.MYI为后缀名的索引文件
一个InnoDB数据表只有一个文件
CREATE
CREATE
Mysql_db_query(''sql........,$linke)
当query函数出错时可以用mysql_errno()【错误号】和mysql_error()函数来确定
当query函数执行后可以使用mysql_affected_rows()来查看他们到底修改了多少行
还可以mysql_insert_id()查看最后插入的自增字段(id)值
查询结果集处理
$result
$rows=
$cols
Mysql_fetch_row(); 将一条结果记录返回并以一个普通索引数组的形式保存
Mysql_fetch_assoc(); 将一条结果记录返回并以一个普通关联数组的形式保存
Mysql_fetch_array(); 将一条结果记录返回一个关联数组或索引数组,或同同时获得索引关联数组,通过传递MYSQL_ASSOC
Mysql_fetch_object();
获取列数信息(数据类型,长度,索引)
php默认把结果集一直保存到php脚本执行结束为止,如果想提前释放结果集,使用mysql_free_result()函数。
标识列所用的计数值重置
flystone 整理
首先说两种可以重置种子的方法:
--
-- a:
TRUNCATE TABLE name
--新行标识所用的计数值重置为该列的种子。如果想保留标识计数值,请改用 DELETE。
/*
TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同:二者均删除表中的全部行。
但 TRUNCATE TABLE 比 DELETE 速度快,且使用的系统和事务日志资源少。
DELETE 语句每次删除一行,并在事务日志中为所删除的每行记录一项。
TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放。
TRUNCATE TABLE 删除表中的所有行,但表结构及其列、约束、索引等保持不变。
新行标识所用的计数值重置为该列的种子。如果想保留标识计数值,请改用 DELETE。
如果要删除表定义及其数据,请使用 DROP TABLE 语句。
对于由 FOREIGN KEY 约束引用的表,不能使用 TRUNCATE TABLE,而应使用不带 WHERE 子句的 DELETE 语句。
由于 TRUNCATE TABLE 不记录在日志中,所以它不能激活触发器。
TRUNCATE TABLE 不能用于参与了索引视图的表。
*/
-- b:
DBCC CHECKIDENT (表, RESEED, 1)
/*
DBCC CHECKIDENT ('table_name', NORESEED) | 不重置当前标识值。DBCC CHECKIDENT 返回一个报表,它指明当前标识值和应有的标识值。 |
DBCC CHECKIDENT ('table_name') 或 DBCC CHECKIDENT ('table_name', RESEED) | 如果表的当前标识值小于列中存储的最大标识值,则使用标识列中的最大值对其进行重置。 |
DBCC CHECKIDENT ('table_name', RESEED, new_reseed_value) | 当前值设置为 new_reseed_value。如果自创建表后没有将行插入该表,则在执行 DBCC CHECKIDENT 后插入的第一行将使用new_reseed_value 作为标识。否则,下一个插入的行将使用 new_reseed_value + 1。如果 new_reseed_value 的值小于标识列中的最大值,以后引用该表时将产生 2627 号错误信息。 |
由此我想起上次有一个网友的问题能很好的让大家认识这个问题,
网友问题如下:
--有张表
Create table A(
A1 int identity(1,1) primary key,
A2 varchar(20),
A3 decimal(15,10)
)
--有个存储过程
Create proc deletes(@nA int)
as
begin tran
delete A where A1=@nA
if(@@Error=0)
Commit transaction
else
Rollback transaction
go
--当执行完
exec deletes 1
--以后
--我想让表里的所有记录A1从新从1开始排列,该怎么修改存储过程deletes,请高手指点
很显然在不利用SET IDENTITY_INSERT 开关项的前提下是无法在自增列完成这个功能的。
很自然的我们就引出这个SET IDENTITY_INSERT 开关项
SET IDENTITY_INSERT
允许将显式值插入表的标识列中。
语法
SET IDENTITY_INSERT [ database.[ owner.] ] {table } { ON | OFF }
好下面我们就先利用这个开发项完成 这个网友的功能
createtable tb(id int identity(1,1),colchar(1))
insert tb select 'a'
insert tb select 'b'
insert tb select 'c'
go
select * from tb
go
Create proc deletes(@nA int)
as
begin tran
SET IDENTITY_INSERT tbON
delete from tb where id=@nA
if(@@Error=0)
begin
select * into #from tb where id >@nA
delete from tb where id> @na
insert tb(id,col)select id - 1 ,colfrom #
Commit transaction
drop table #
end
else
Rollback transaction
SET IDENTITY_INSERT tbOFF
go
exec deletes 2
select * from tb
drop proc deletes
drop table tb
结果一:
id col
----------- ----
1 a
2 b
3 c
结果二:
id col
----------- ----
1 a
2 c
(所影响的行数为 2 行)
到此以为完全实现这个网友的功能了,可是发现在调用exec deletes 2 删除第二记录后,
马上进行一条的记录的插入时发种子值仍是在原来的基础上增加了,比如我们一共三记录,删除第二条后经重新整理以为再插入时id 应该是3, 可是发现不对,测试删除重新整理后查看@@identity值仍是3,显然这是达到不预想的效果 的.
insert tbselect 'd'
select * from tb
/*
id col
----------- ----
1 a
2 c
4 d -----显然这儿是不理想的,
*/
那怎么办呢,这个时我们想起前面讲的重置种子值的方法:dbcc CHECKIDENT,
哈哈,所以我们有了下面的比较完美的方法:
createtable tb(id int identity(1,1),colchar(1))
insert tb select 'a'
insert tb select 'b'
insert tb select 'c'
go
select * from tb
go
Create proc deletes(@nA int)
as
declare @i int
begin tran
SET IDENTITY_INSERT tbON
delete from tb where id=@nA
if(@@Error=0)
begin
select * into #from tb where id >@nA
delete from tb where id> @na
insert tb(id,col)select id - 1 ,colfrom #
Commit transaction
drop table #
end
else
Rollback transaction
select @i =count(1)from tb
SET IDENTITY_INSERT tbOFF
DBCC CHECKIDENT ('tb',RESEED,@i)
go
exec deletes 2
select * from tb
insert tb select 'd' ---- add data again
select * from tb
select @@IDENTITY
insert tbselect 'd'
select * from tb
/*
id col
----------- ----
1 a
2 c
3 d -----显然这儿是理想的结果,
*/
dropproc deletes
drop table tb
至此发现完全 满足这个朋友的预想目的。
看你的mysql现在已提供什么存储引擎:
mysql> show engines;
看你的mysql当前默认的存储引擎:
mysql> show variables like '%storage_engine%';
你要看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎):
mysql> show create table 表名;