如果需要修改数据表的名称 可以在 ALTER TABLE 语句中使用 RENAME 子句来实现。
尝试以下实例将数据表 testalter_tbl 重命名为 alter_tbl
mysql ALTER TABLE testalter_tbl RENAME TO alter_tbl;注意
alter其他用途
修改存储引擎 修改为myisam
alter table tableName engine myisam;
删除外键约束 keyName是外键别名
alter table tableName drop foreign key keyName;
修改字段的相对位置 这里name1为想要修改的字段 type1为该字段原来类型 first和after二选一 这应该显而易见 first放在第一位 after放在name2字段后面
alter table tableName modify name1 type1 first|after name2;
55、MySQL 索引
索引分单列索引和组合索引。单列索引 即一个索引只包含单个列 一个表可以有多个单列索引 但这不是组合索引。组合索引 即一个索引包含多个列。
创建索引时 你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
实际上 索引也是一张表 该表保存了主键与索引字段 并指向实体表的记录。
更新表时 MySQL不仅要保存数据 还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。
普通索引
创建索引CREATE INDEX indexName ON mytable(username(length));
如果是CHAR VARCHAR类型 length可以小于字段实际长度 如果是BLOB和TEXT类型 必须指定 length。
修改表结构(添加索引)ALTER table tableName ADD INDEX indexName(columnName)创建表的时候直接指定
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );唯一索引
它与前面的普通索引类似 不同的就是 索引列的值必须唯一 但允许有空值。如果是组合索引 则列值的组合必须唯一。它有以下几种创建方式
首先创建一个表 create table t1 (id int primary key,username varchar(20),password varchar(20));
创建索引索引名一般是 表名_字段名
创建单个索引的语法 CREATE INDEX 索引名 on 表名 字段名
CREATE UNIQUE INDEX indexName ON mytable(username(length)),修改表结构
ALTER table mytable ADD UNIQUE [indexName] (username(length))创建表的时候直接指定
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );
给id创建索引 CREATE INDEX t1_id on t1(id);
给username和password创建联合索引 CREATE index t1_username_password ON t1(username,password);
其中index还可以替换成unique primary key 分别代表唯一索引和主键索引;
注意 : 索引列的基数越大 数据区分度越高 索引的效果越好。
避免创建过多的索引 索引会额外占用磁盘空间 降低写操作效率。
主键尽可能选择较短的数据类型 可以有效减少索引的磁盘占用提高查询效率。
一个表只能有一个主键索引 但是可以有多个唯一索引。
主键索引一定是唯一索引 唯一索引不是主键索引。
联合索引 将多个列组合在一起创建索引 可以覆盖多个列。 也叫复合索引 组合索引
外键索引 只有InnoDB类型的表才可以使用外键索引 保证数据的一致性、完整性、和实现级联操作 基本不用 。
全文索引 MySQL自带的全文索引只能用于MyISAM 并且只能对英文进行全文检索 基本不用
删除索引 删除指定表中指定名称的索引 语法为
ALTER TABLE table_nameDROP INDEX index_name;DROP INDEX [indexName] ON mytable;
例如删除名称为idx_cust_name的索引 其SQL语句为
ALTER TABLE customers
DROP INDEX idx_cust_name;
修改索引
在MySQL中并没有提供修改索引的直接指令 一般情况下 我们需要先删除掉原索引 再根据需要创建一个同名的索引 从而变相地实现修改索引操作。
--先删除
ALTER TABLE user
DROP INDEX idx_user_username;
--再以修改后的内容创建同名索引
CREATE INDEX idx_cust_name ON customers (cust_name(8));
查看索引
在MySQL中 要查看某个数据库表中的索引也非常简单 只需要使用以下两个命令中的任意一种即可。
--如果查看索引前 没有使用user db_name等命令指定具体的数据库 则必须加上FROM db_name
SHOW INDEX FROM table_name [FROM db_name]
--如果查看索引前 没有使用user db_name等命令指定具体的数据库 则必须加上db_name.前缀
SHOW INDEX FROM [db_name.]table_name
使用ALTER 命令添加和删除索引
有四种方式来添加数据表的索引
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键 这意味着索引值必须是唯一的 且不能为NULL。ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的 除了NULL外 NULL可能会出现多次 。ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引 索引值可出现多次。ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT 用于全文索引。
实例为在表中添加索引。
mysql ALTER TABLE testalter_tbl ADD INDEX (c);
在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下实例删除索引:
mysql ALTER TABLE testalter_tbl DROP INDEX c;使用 ALTER 命令添加和删除主键
主键只能作用于一个列上 添加主键索引时 你需要确保该主键默认不为空 NOT NULL 。实例如下
mysql ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;mysql ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);
你也可以使用 ALTER 命令删除主键
mysql ALTER TABLE testalter_tbl DROP PRIMARY KEY;
删除主键时只需指定PRIMARY KEY 但在删除索引时 你必须知道索引名。
显示索引信息使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \\G 来格式化输出信息。
尝试以下实例:
mysql SHOW INDEX FROM table_name; \\G........
56、MySQL 临时表
MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见 当关闭连接时 Mysql会自动删除表并释放所有空间。
实例
mysql CREATE TEMPORARY TABLE SalesSummary ( - product_name VARCHAR(50) NOT NULL - , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00 - , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00 - , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0Query OK, 0 rows affected (0.00 sec)mysql INSERT INTO SalesSummary - (product_name, total_sales, avg_unit_price, total_units_sold) - VALUES - ( cucumber , 100.25, 90, 2);mysql SELECT * FROM SalesSummary; -------------- ------------- ---------------- ------------------ | product_name | total_sales | avg_unit_price | total_units_sold | -------------- ------------- ---------------- ------------------ | cucumber | 100.25 | 90.00 | 2 | -------------- ------------- ---------------- ------------------ 1 row in set (0.00 sec)
当你使用 SHOW TABLES命令显示数据表列表时 你将无法看到 SalesSummary表。
如果你退出当前MySQL会话 再使用 SELECT命令来读取原先创建的临时表数据 那你会发现数据库中没有该表的存在 因为在你退出时该临时表已经被销毁了。
删除MySQL 临时表默认情况下 当你断开与数据库的连接后 临时表就会自动被销毁。当然你也可以在当前MySQL会话使用 DROP TABLE 命令来手动删除临时表。
手动删除临时表的实例
mysql CREATE TEMPORARY TABLE SalesSummary ( - product_name VARCHAR(50) NOT NULL - , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00 - , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00 - , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0Query OK, 0 rows affected (0.00 sec)mysql INSERT INTO SalesSummary - (product_name, total_sales, avg_unit_price, total_units_sold) - VALUES - ( cucumber , 100.25, 90, 2);mysql SELECT * FROM SalesSummary; -------------- ------------- ---------------- ------------------ | product_name | total_sales | avg_unit_price | total_units_sold | -------------- ------------- ---------------- ------------------ | cucumber | 100.25 | 90.00 | 2 | -------------- ------------- ---------------- ------------------ 1 row in set (0.00 sec)mysql DROP TABLE SalesSummary;mysql SELECT * FROM SalesSummary;ERROR 1146: Table RUNOOB.SalesSummary doesn t exist
57、MySQL 复制表
完全的复制MySQL的数据表 包括表的结构 索引 默认值等。 如果仅仅使用CREATE TABLE ... SELECT 命令 是无法实现的。
完整的复制MySQL数据表 步骤如下
使用 SHOW CREATE TABLE 命令获取创建数据表(CREATE TABLE) 语句 该语句包含了原数据表的结构 索引等。 复制以下命令显示的SQL语句 修改数据表名 并执行SQL语句 通过以上命令 将完全的复制数据表结构。如果你想复制表的内容 你就可以使用 INSERT INTO ... SELECT 语句来实现。尝试以下实例来复制表 runoob_tbl 。
步骤一
获取数据表的完整结构。
mysql SHOW CREATE TABLE runoob_tbl \\G;*************************** 1. row *************************** Table: runoob_tblCreate Table: CREATE TABLE runoob_tbl ( runoob_id int(11) NOT NULL auto_increment, runoob_title varchar(100) NOT NULL default , runoob_author varchar(40) NOT NULL default , submission_date date default NULL, PRIMARY KEY ( runoob_id ), UNIQUE KEY AUTHOR_INDEX ( runoob_author )) ENGINE InnoDB 1 row in set (0.00 sec)ERROR:No query specified
步骤二
修改SQL语句的数据表名 并执行SQL语句。
mysql CREATE TABLE clone_tbl ( - runoob_id int(11) NOT NULL auto_increment, - runoob_title varchar(100) NOT NULL default , - runoob_author varchar(40) NOT NULL default , - submission_date date default NULL, - PRIMARY KEY ( runoob_id ), - UNIQUE KEY AUTHOR_INDEX ( runoob_author )- ) ENGINE InnoDB;Query OK, 0 rows affected (1.80 sec)
步骤三
执行完第二步骤后 你将在数据库中创建新的克隆表 clone_tbl。 如果你想拷贝数据表的数据你可以使用INSERT INTO... SELECT 语句来实现。
mysql INSERT INTO clone_tbl (runoob_id, - runoob_title, - runoob_author, - submission_date) - SELECT runoob_id,runoob_title, - runoob_author,submission_date - FROM runoob_tbl;Query OK, 3 rows affected (0.07 sec)Records: 3 Duplicates: 0 Warnings: 0
执行以上步骤后 你将完整的复制表 包括表结构及表数据。
另一种完整复制表的方法 :CREATE TABLE targetTable LIKE sourceTable;INSERT INTO targetTable SELECT * FROM sourceTable;
其他:
可以拷贝一个表中其中的一些字段:
CREATE TABLE newadmin AS SELECT username, password FROM admin)
可以将新建的表的字段改名:
CREATE TABLE newadmin AS SELECT id, username AS uname, password AS pass FROM admin)
可以拷贝一部分数据:
CREATE TABLE newadmin AS SELECT * FROM admin WHERE LEFT(username,1) s )
可以在创建表的同时定义表中的字段信息:
CREATE TABLE newadmin id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY SELECT * FROM admin)
注意
区分下mysql复制表的两种方式。
第一、只复制表结构到新表
create table 新表 select * from 旧表 where 1 2
或者
create table 新表 like 旧表
第二、复制表结构及数据到新表
create table新表 select * from 旧表
58、MySQL 序列使用
mySQL 序列是一组整数 1, 2, 3, ... 由于一张数据表只能有一个字段自增主键 如果你想实现其他字段也实现自动增加 就可以使用MySQL序列来实现。
使用 AUTO_INCREMENTMySQL 中最简单使用序列的方法就是使用 MySQL AUTO_INCREMENT 来定义列。
以下实例中创建了数据表 insect insect 表中 id 无需指定值可实现自动增长。
mysql CREATE TABLE insect - id INT UNSIGNED NOT NULL AUTO_INCREMENT, - PRIMARY KEY (id), - name VARCHAR(30) NOT NULL, # type of insect - date DATE NOT NULL, # date collected - origin VARCHAR(30) NOT NULL # where collectedQuery OK, 0 rows affected (0.02 sec)mysql INSERT INTO insect (id,name,date,origin) VALUES - (NULL, housefly , 2001-09-10 , kitchen ), - (NULL, millipede , 2001-09-10 , driveway ), - (NULL, grasshopper , 2001-09-10 , front yard Query OK, 3 rows affected (0.02 sec)Records: 3 Duplicates: 0 Warnings: 0mysql SELECT * FROM insect ORDER BY id; ---- ------------- ------------ ------------ | id | name | date | origin | ---- ------------- ------------ ------------ | 1 | housefly | 2001-09-10 | kitchen || 2 | millipede | 2001-09-10 | driveway || 3 | grasshopper | 2001-09-10 | front yard | ---- ------------- ------------ ------------ 3 rows in set (0.00 sec)
本文链接： http://feresawa.immuno-online.com/view-740972.html
