一、MySQL 索引
创建索引时,你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。
上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。
- 普通索引
创建索引
这是最基本的索引,它没有任何限制。它有以下几种创建方式:
CREATE INDEX indexName ON table_name (column_name)
如果是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))
);
删除索引的语法
DROP INDEX [indexName] ON mytable;
- 唯一索引
它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。它有以下几种创建方式:
- 创建索引
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))
);
-
使用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);
modify用于修改表中字段的数据长度,数据类型以及字段的约束条件的。
你也可以使用 ALTER 命令删除主键:
mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;
删除主键时只需指定PRIMARY KEY,但在删除索引时,你必须知道索引名。
显示索引信息
你可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。
尝试以下实例:
mysql> SHOW INDEX FROM table_name\G
二、MySQL 临时表
MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间。
MySQL临时表只在当前连接可见,如果你使用PHP脚本来创建MySQL临时表,那每当PHP脚本执行完成后,该临时表也会自动销毁。
如果你使用了其他MySQL客户端程序连接MySQL数据库服务器来创建临时表,那么只有在关闭客户端程序时才会销毁临时表,当然你也可以手动销毁。
- 实例
以下展示了使用MySQL 临时表的简单实例,以下的SQL代码可以适用于PHP脚本的mysql_query()函数。
- 删除MySQL 临时表
默认情况下,当你断开与数据库的连接后,临时表就会自动被销毁。当然你也可以在当前MySQL会话使用 DROP TABLE 命令来手动删除临时表。
以下是手动删除临时表的实例:
三、MySQL 复制表
如果我们需要完全的复制MySQL的数据表,包括表的结构,索引,默认值等。 如果仅仅使用CREATE TABLE … SELECT 命令,是无法实现的。
步骤如下:
使用 SHOW CREATE TABLE 命令获取创建数据表(CREATE TABLE) 语句,该语句包含了原数据表的结构,索引等。
复制以下命令显示的SQL语句,修改数据表名,并执行SQL语句,通过以上命令 将完全的复制数据表结构。
如果你想复制表的内容,你就可以使用 INSERT INTO ... SELECT 语句来实现。
实例
尝试以下实例来复制表 runoob_tbl 。
步骤一:
获取数据表的完整结构。
步骤二:
修改SQL语句的数据表名,并执行SQL语句。
步骤三:
执行完第二步骤后,你将在数据库中创建新的克隆表 clone_tbl。 如果你想拷贝数据表的数据你可以使用 INSERT INTO… SELECT 语句来实现。
四、MySQL 元数据
你可能想知道MySQL以下三种信息:
查询结果信息: SELECT, UPDATE 或 DELETE语句影响的记录数。
数据库和数据表的信息: 包含了数据库及数据表的结构信息。
MySQL服务器信息: 包含了数据库服务器的当前状态,版本号等。
在MySQL的命令提示符中,我们可以很容易的获取以上服务器信息。 但如果使用Perl或PHP等脚本语言,你就需要调用特定的接口函数来获取。
- 获取查询语句影响的记录数
PERL 实例
在 DBI 脚本中, 语句影响的记录数通过函数 do( ) 或 execute( )返回:
# 方法 1
# 使用do( ) 执行 $query
my $count = $dbh->do ($query);
# 如果发生错误会输出 0
printf "%d 条数据被影响\n", (defined ($count) ? $count : 0);
# 方法 2
# 使用prepare( ) 及 execute( ) 执行 $query
my $sth = $dbh->prepare ($query);
my $count = $sth->execute ( );
printf "%d 条数据被影响\n", (defined ($count) ? $count : 0);
- PHP 实例
在PHP中,你可以使用 mysqli_affected_rows( ) 函数来获取查询语句影响的记录数。
$result_id = mysqli_query ($conn_id, $query);
# 如果查询失败返回
$count = ($result_id ? mysqli_affected_rows ($conn_id) : 0);
print ("$count 条数据被影响\n");
- 数据库和数据表列表
你可以很容易的在MySQL服务器中获取数据库和数据表列表。 如果你没有足够的权限,结果将返回 null。
你也可以使用 SHOW TABLES 或 SHOW DATABASES 语句来获取数据库和数据表列表。
PERL 实例
# 获取当前数据库中所有可用的表。
my @tables = $dbh->tables ( );
foreach $table (@tables ){
print "表名 $table\n";
}
- PHP 实例
以下实例输出 MySQL 服务器上的所有数据库:
<?php
$dbhost = 'localhost'; // mysql服务器主机地址
$dbuser = 'root'; // mysql用户名
$dbpass = '123456'; // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('连接失败: ' . mysqli_error($conn));
}
// 设置编码,防止中文乱码
$db_list = mysqli_query($conn, 'SHOW DATABASES');
while ($db = mysqli_fetch_object($db_list))
{
echo $db->Database . "<br />";
}
mysqli_close($conn);
?>
- 获取服务器元数据
以下命令语句可以在 MySQL 的命令提示符使用,也可以在脚本中 使用,如PHP脚本。
学习来自“https://www.runoob.com/mysql”
总结
人必须从失败中总结经验,否则很难进步。我思故我在,应该理解为我怀疑,所以我存在。