MySQL必知必会笔记（六）插入数据 创建和操纵表

第二十章 插入数据

 

INSERT是用来插入（或添加）行到数据库表的。插入数据的几种方式：

1  插入完整的行

2 插入行的一部分

3  插入多行

4 插入某些查询结果

INSERT SELECT语句

INSERT INTO customers(cust_id,cust_name,cust_email) 

SELECT cust_id,cust_name,cust_email FROM custnew;

列省略

    如果表中定义允许，则可以在INSERT操作中省略某些列，省略的列必须满足以下某个条件。

1 该列定义为允许为NULL

2 在表定义中给出默认值，

 

提高整体性能

    数据库经常多个客户访问，对处理什么请求以及用什么次序处理进行管理MySQL的任务。INSERT操作可能很耗时（特别是由很多索引需要更新时），而且它可能降低等待处理的SELECT语句性能。

    如果数据检索是最重要的（通常是这样的），则你可以通过在INSERT 和INTO之间添加关键字 LOW_PRIORITY 指示mysql降低INSERT语句的优先集

 

更新和删除数据

 

    为了更新（修该）表中的数据，可使用UPDATE语句，可采用两种方式使用UPDATE

1 更新表中特定的行

2 更新表中所有的行

更新行和删除行的时候一定要加上WHERE子句，否则后果自负

UPDATE bname SET zda = ‘acontent’ , zdb = ‘bcontent’ WHERE zdid=’20005’;

    IGNORE 关键字    如果使用UPDATE更新多行，并且在更新这些行中的一行或多行时出现一个错误，则整个UPDATE操作被取消，（错误发生前更新的所有行被恢复到他们原来的值）为即使发生错误也继续进行更新。可使用IGNORE关键字

UPDATE  IGNORE bname;

 

删除数据

为了从表中删除(去掉)数据，使用DELETE语句，可以使用两种方式：

        1 从表中删除特定的行

2 从表中删除所有的行

    不要省略WHERE子句

 

    更快的删除  如果想从表中删除所有的行，不要使用DELETE，可以使用TRUNCATE TABLE语句，他完成相同的工作，但速度更快（TRUNCATE实际上是删除原来的表并重新创建一个表，而不是逐行去删除表中的数据）

 

更新和删除的指导原则

下面是许多sql程序员使用的UPDATE或DELETE是所遵循的习惯

        1 除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。

        2 保证每个表都有主键，尽可能的像WHERE子句那样使用它(可以指定各主键、多个值、和值的范围)

        3 在对UPDATE和DELETE语句操作使用WHERE前，应该先用SELECT进行测试，保证它过滤的数据是正确的记录，以防编写的WHERE子句不正确。

        4 使用强制实施引用完整的数据库，这样MYSQL将不允许删除具有与其他表相关联的数据的行

 

 

第二十一章  创建和操纵表

创建表

一般两种创建表的方法

1 使用具有交互式创建和管理表的工具

2 表也可以直接用MySQL语句操纵

在使用交互式工具时，工具也是生成MySQL语句操纵数据库

表创建基础

为了利用CREATE TABLE创建表，必须给出下列信息：

1 新表的名字，在关键字CREATE TABLE之后给出

2 表列的名字和定义，用逗号分隔

CREATE TABLE table

(

column_id  int       NOT NULL  AUTO_INCRMENT,

columnOne  char(20)  NOT NULL  DEFAULT 1,

columnOne  char(20)  NOT NULL ,

column  char(20)  NOT NULL ,

…..

PRIMARY  KEY ( column_id)

) ENGINE =  InnoDB

    如果表已经存在，则必须先删除后在创建他，不可以直接覆盖。如果仅想在表没有存在的情况下创建它，应该在表名后给出 IF NOT EXISTS

使用NULL值  如果不指定列为NOT NULL则它默认是NULL

主键再介绍  主键的值必须是唯一的。创建主键时可以用单列做主键PRIMARY KEY(columnOne,columnTwo)，也可以使用多列做主键PRIMARY KEY(columnOne,columnTwo).主键值不允许为NULL

索引创建

    CREATE INDEX indexnameON tablename (column [ASC|DESC],….);

使用AUTO_INCREMENT

    每个表只允许使用一个AUTO_INCREMENT列，而且它必须被索引，（如通过使它为主键）

如果两个表关联，添加时一表时需要另一表的主键，该怎么获得呢：

    可以使用last_insert_id()函数获得这个值 此语句返回最后一个AUTO_CREMENT的值。

指定默认值

    如果在插入行时没有给出值，mysql允许指定此时使用默认值。默认值是在CREATE TABLE语句的列定义中的DEFAULT关键字定义的。

        columnOne  char(20)  NOT NULL  DEFAULT 1,

MySQL不允许使用函数作为默认值，它只支持常量

引擎搜索

    你可能已经注意到，迄今为止使用的CREATE  TABLE语句全都以ENGINE = InnoDB语句结束

    与其他的DBMS一样，MySQL有一个具体管理和处理数据的内部引擎，在你使用CREATE TABLE语句是，该引擎具体创建表，在其他应用中区修改读取删除表等处理你的请求，多数时候它隐藏在DBMS内，不需要过多的关注它。

    但MySQL与其他的DBMS不一样，它具有多种引擎，它打包多个引擎，这些引擎都隐藏在MySQL服务器内，全都能处理用户的请求。

    为什么要发行多种引擎呢，因为他们具有各自不同的功能和特性，为了不同的任务选择正确的引擎能获得良好的功能和灵活性

    当然你也可以省略这些数据库引擎，如果省略ENGINE=语句，则使用默认引擎(很可能为MyISAM),多数sql语句都会默认使用它，但并不是所有的语句都默认使用它，这就是为什么ENGINE=语句很重要的原因。

以下几个需要知道的引擎

1 InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索。

    2 MEMORY 在功能上等同于MySAM，但由于数据存储在内存，所以速度更快（特别适用于临时表）

    3 MySAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理

        引擎类型可以混用。混用引擎类型的一个大缺陷。外键不能跨引擎，即使用一个引擎的表不能引用具有使用不同引擎的表的外键

更新表

        为了更新表，可以使用ALTER TABLE语句，但是在理想状态下，当表中存储了数据后就不应该再去更新表了。所以在设计表时需要花费大量的时间去考虑，以便后期不做太大的改动。

    ALERT TABLE更改表结构，必须给出下面的信息：

    1 在ALERT  TABLE之后给出要修改的表名

    2 所做更改的列表

    添加一列 ALERT TABLE column ADD columnFour CHAR(90);必须指明数据类型

    删除一列 ALERT TABLE column DORP columnFour ;

        定义外键    

            ALERT TABLE tableOne ADD CONSTRAINT columnOne 

            FOREIGN KEY (column_id) PEFERENCES tableTwo (column_id)

        可以单条ALTER TABLE语句对单个表进行多个修改，每个修改用逗号分隔开。

    复杂的表结构一般需要手动删除过程

        1 用新的列布局创建一个新表

        2 使用INSERT SELECT 语句从旧表复制数据当道新表。如果有必要，可以使用转换函数和计算字段

        3 检验包含所需数据的新表

        4 重名名旧表

        5 用旧表原来的名字重命名新表

        6 根据需要，重新创建触发器、存储过程、索引和外键

        小心使用ALTER TABLE 因为它是不可逆的操作，最好操作前完成已备份。

    删除表

        DORP TABLE table；   不可撤销，永久删除表

    重命名表

        RENAME TABLE table TO table2；  多个表重命名 逗号分隔开

 

CREATE DATABSE [IF NOT EXISTS] dataname;

DORP DATABSES [IF EXISTS] databses;

 

在整型数据列后加上 UNSIGNED 属性可禁止负数，取值从0开始。范围扩大一倍-125—125  0-250

ZEROFILL  数据字段属性，在数值之前自动用0补足不足的位数，声明一个int（3）ZEROFILL插入5 返回005

CREATE TABLE tablename(

Id int(5) UNSIGNED.……

Num ind(3) ZEROFILL….

Sex  int(1)  DEFAULT 0 ……

Name varchar(10) NOT NULL……

)

Mysql默认字符集设置：win my.ini    Linux   /etc/my.cnf

Character-set-server = gbk;

Collation-server =gbk_chinese_ci;

创建数据库时设置字符集语句：

CREATE DATABASE IF NOT mydb DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

数据库文件结构

一个MyISAM数据表会有三个文件，以.frm为后缀的结构定义文件，以.MYD为后缀名的数据文件，一个以.MYI为后缀名的索引文件

一个InnoDB数据表只有一个文件

CREATE TABLE t (i INT) ENGINE = INNODB;

CREATE TABLE t (i INT) TYPE = MYISAM;

 

Mysql_db_query(”sql……..,$linke)  当文件连接两个数据库时指定在哪个数据库上执行

当query函数出错时可以用mysql_errno()【错误号】和mysql_error()函数来确定

当query函数执行后可以使用mysql_affected_rows()来查看他们到底修改了多少行

还可以mysql_insert_id()查看最后插入的自增字段（id）值

查询结果集处理

$result = mysql_query(select * from books);

rows=&nbsp;<wbr></wbr>mysql_num_rows(result);   显示结果的行数

cols&nbsp;<wbr></wbr>=&nbsp;<wbr></wbr>mysql_num_fields(result); 显示结果的列数

 

Mysql_fetch_row(); 将一条结果记录返回并以一个普通索引数组的形式保存

Mysql_fetch_assoc(); 将一条结果记录返回并以一个普通关联数组的形式保存

Mysql_fetch_array(); 将一条结果记录返回一个关联数组或索引数组，或同同时获得索引关联数组，通过传递MYSQL_ASSOC 、MYSQL_NUM MYSQL_BOTH中的一个常量返回不同的数组形态。默认使用MYSQL_BOTH常量

Mysql_fetch_object();  以一个对象的形式返回一条结果记录，它的各个记录需要以对象的方式进行访问。

获取列数信息（数据类型，长度，索引）  mysql_fetch_fields()

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说明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(idint 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 tbwhere 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(idint 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 tbwhere 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 表名;