当前位置:首页 » Mysql技术教程

学习MySQL(下)

2017-11-30 12:01 本站整理 浏览(1)

具体实例

22、MySQL ALTER命令

当我们需要修改数据表名或者修改数据表字段时,就需要使用到MySQL ALTER命令。

1、如果需要修改数据表的名称,可以在 ALTER TABLE 语句中使用 RENAME 子句来实现。以下实例将数据表 old_tablename 重命名为 new_tablename:

mysql> ALTER TABLE old_tablename RENAME TO new_tablename;

2、删除,添加或修改表字段,如下命令使用了 ALTER 命令及 DROP 子句来删除创建表的 table_title 字段:

mysql> ALTER TABLE blog_table_test  DROP table_title;

MySQL 中使用 ADD 子句来向数据表中添加列,如下实例在表 blog_table_test 中添加 table_title 字段,并定义数据类型:

mysql> ALTER TABLE blog_table_test ADD table_title INT;

修改字段类型及名称,如下命令在ALTER命令中使用 MODIFY把字段 table_title 的类型改为 CHAR(10),可以执行以下命令:

ysql> ALTER TABLE blog_table_test MODIFY table_title CHAR(10);

使用 CHANGE 子句,在 CHANGE 关键字之后,紧跟着的是要修改的字段名,然后指定新字段名及类型。尝试如下实例:

mysql> ALTER TABLE blog_table_test CHANGE oldfield newfield newtype;

3、ALTER TABLE 对 Null 值和默认值的影响,当你修改字段时,你可以指定是否包含值或者是否设置默认值。下例指定字段 table_title 为 NOT NULL 且默认值为100 。

mysql> ALTER TABLE blog_table_test
-> MODIFY table_title BIGINT NOT NULL DEFAULT 100;

也可以使用 ALTER 来修改字段的默认值,实例:

mysql> ALTER TABLE blog_table_test ALTER table_title SET DEFAULT 1000;

4、使用 ALTER 命令添加和删除主键。在添加主键索引时,你需要确保该主键默认不为空(NOT NULL)。实例:

mysql> ALTER TABLE blog_table_test MODIFY table_id INT NOT NULL;
mysql> ALTER TABLE blog_table_test ADD PRIMARY KEY (table_id);

5、使用ALTER 命令添加和删除索引,包含有四种方式来添加数据表的索引(关于索引在下面会有介绍):1、ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。2、ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。 3、ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引,索引值可出现多次。4、ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ,用于全文索引。

23、MySQL 索引

索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。其分为单列索引:一个索引只包含单个列。和 组合索引:一个索引包含多个列。。

索引可以大大提高MySQL的检索速度,当然也有缺陷,过多的使用索引将会造成滥用,会降低更新表的速度,而且建立索引会占用磁盘空间的索引文件。

1、普通索引的创建,最基本的索引,它没有任何限制。它有以下几种创建方式:

创建索引:

CREATE INDEX indexName ON blog_table_test(username(length)); 

如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length。

修改表结构(添加索引):

ALTER table tableName ADD INDEX indexName(columnName)

创建表的时候直接指定:

CREATE TABLE blog_table_test(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);

2、唯一索引:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。它有以下几种创建方式:

创建索引:

CREATE UNIQUE INDEX indexName ON blog_table_test(username(length)) 

修改表结构:

ALTER table blog_table_test ADD UNIQUE [indexName] (username(length))

创建表的时候直接指定:

REATE TABLE blog_table_test(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
UNIQUE [indexName] (username(length))
);

3、删除索引的语法:

DROP INDEX [indexName] ON blog_table_test;

4、显示索引信息:

mysql> SHOW INDEX FROM table_name; \G

24、MySQL 临时表

MySQL临时表只在当前连接可见,如果你使用PHP脚本来创建MySQL临时表,那每当PHP脚本执行完成后,该临时表也会自动销毁。若是使用MySQL客户端程序连接MySQL数据库服务器来创建临时表,那么只有在关闭客户端程序时才会销毁临时表,当然也可以手动销毁。

创建临时表的语句与创建数据表的语句基本相同,区别只在于 cerate table 语句的中间加入 temporary 。

25、MySQL 复制表

复制表的操作步骤:1、使用 SHOW CREATE TABLE 命令获取创建数据表(show create table tablename \g;) 语句,该语句包含了原数据表的结构,索引等。2、复制之后改变数据表的名称,在粘贴在mysql控制台执行,此时已经建立了一个克隆表。3、复制表的内容,可以使用 INSERT INTO ... SELECT 语句来实现。第三部的操作语句如下:

mysql> INSERT INTO new_table (runoob_id,
-> runoob_title,
-> runoob_author,
-> submission_date)
-> SELECT runoob_id,runoob_title,
-> runoob_author,submission_date
-> FROM blog_table_test;

26、MySQL 元数据

在MySQL的命令提示符中,我们可以很容易的获取以上服务器信息。 但如果使用Perl或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 服务器上的所有数据库:

&lt?php
$dbhost = 'localhost:3306'; // mysql服务器主机地址
$dbuser = 'root'; // mysql用户名
$dbpass = '123'; // 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 . "
";
}
mysqli_close($conn);
?&gt

以下命令语句可以在 MySQL 的命令提示符使用,也可以在脚本中 使用,如PHP脚本。1、SELECT VERSION( ):服务器版本信息。2、SELECT DATABASE( ):当前数据库名 (或者返回空)。3、SELECT USER( ):当前用户名。4、SHOW STATUS:服务器状态。5、SHOW VARIABLES:服务器配置变量。

27、MySQL 序列使用

MySQL序列是一组整数:1, 2, 3, ...,由于一张数据表只能有一个字段自增主键, 如果你想实现其他字段也实现自动增加,就可以使用MySQL序列来实现。

1、使用AUTO_INCREMENT:MySQL中最简单使用序列的方法就是使用 MySQL AUTO_INCREMENT 来定义列。AUTO_INCREMENT定义列为自增的属性,一般用于主键,数值会自动加1。

2、获取AUTO_INCREMENT值:在MySQL的客户端中你可以使用 SQL中的LAST_INSERT_ID( ) 函数来获取最后的插入表中的自增列的值。而PHP 通过 mysql_insert_id ()函数来获取执行的插入SQL语句中 AUTO_INCREMENT列的值:

mysql_query ("INSERT INTO blog_table_test (name,date,origin)
VALUES('moth','2001-09-14','windowsill')", $conn_id);
$seq = mysql_insert_id ($conn_id);

3、重置序列:如果删除了数据表中的多条记录,并希望对剩下数据的AUTO_INCREMENT列进行重新排列,那么你可以通过删除自增的列,然后重新添加来实现。 不过该操作要非常小心,如果在删除的同时又有新记录添加,有可能会出现数据混乱。操作如下所示:

mysql> ALTER TABLE insect DROP filed;
mysql> ALTER TABLE insect
-> ADD filed INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
-> ADD PRIMARY KEY (filed);

4、设置序列的开始值:一般情况下序列的开始值为1,但如果你需要指定一个开始值100,那我们可以通过以下语句来实现:

mysql> CREATE TABLE blog_table_test
-> (
-> table_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> PRIMARY KEY (table_id),
-> table_name VARCHAR(30) NOT NULL,
-> table_date DATE NOT NULL,
-> table_origin VARCHAR(30) NOT NULL
)engine=innodb auto_increment=100 charset=utf8;

或者:

mysql> ALTER TABLE t AUTO_INCREMENT = 100;

28、MySQL 处理重复数据

表中无索引及主键,则该表允许出现多条重复记录,如果想设置表中字段field_one,filed_two数据不能重复,可以设置双主键模式来设置数据的唯一性, 如果你设置了双主键,那么那个键的默认值不能为NULL,可设置为NOT NULL。例如:

CREATE TABLE blog_table_test
(
field_one CHAR(20) NOT NULL,
field_two CHAR(20) NOT NULL,
PRIMARY KEY (field_one, field_two)
);

如果我们设置了唯一索引,那么在插入重复数据时,SQL语句将无法执行成功,并抛出错。INSERT IGNORE INTO与INSERT INTO的区别就是INSERT IGNORE会忽略数据库中已经存在的数据,如果数据库没有数据,就插入新的数据,如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据,达到在间隙中插入数据的目的。

INSERT IGNORE INTO当插入数据时,在设置了记录的唯一性后,如果插入重复数据,将不返回错误,只以警告形式返回。 而REPLACE INTO into如果存在primary 或 unique相同的记录,则先删除掉。再插入新记录。

另一种设置数据的唯一性方法是添加一个UNIQUE索引。

1、统计重复数据

mysql> SELECT COUNT(*) as repetitions, field_one, field_two
-> FROM table_name
-> GROUP BY field_one, field_two
-> HAVING repetitions > 1;

查询重复的值,请执行以下操作:1、确定哪一列包含的值可能会重复。2、在列选择列表使用COUNT(*)列出的那些列。3、在GROUP BY子句中列出的列。4、HAVING子句设置重复数大于1。

2、过滤重复数据

读取不重复的数据可以在 SELECT 语句中使用 DISTINCT 关键字来过滤重复数据:

mysql> SELECT DISTINCT field_one, field_two
-> FROM blog_table_test;

也可以使用 GROUP BY 来读取数据表中不重复的数据:

mysql> SELECT field_one, field_two
-> FROM blog_table_test
-> GROUP BY (field_one, field_two);

3、删除重复数据

可以在数据表中添加 INDEX(索引) 和 PRIMAY KEY(主键)这种简单的方法来删除表中的重复记录:

mysql> ALTER IGNORE TABLE blod_table_test
-> ADD PRIMARY KEY (field_one, field_two);

可以使用以下的SQL语句删除表中的重复记录:

mysql> CREATE TABLE blog_table SELECT field_one, field_two
-> FROM blog_table_test;
-> GROUP BY (lfield_one, field_two);
mysql> DROP TABLE blog_table_test;
mysql> ALTER TABLE blog_table RENAME TO blog_table_test;

29、MySQL 及 SQL 注入

所谓SQL注入,就是通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串,最终达到欺骗服务器执行恶意的SQL命令。我们永远不要信任用户的输入,必须认定用户输入的数据都是不安全的,都需要对用户输入的数据进行过滤处理。例如下面的例子中没有对用户输入进行过滤:

$name = "Qadir'; DELETE FROM users;";
mysqli_query($conn, "SELECT * FROM users WHERE name='{$name}'");

以上的注入语句中,我们没有对 $name 的变量进行过滤,$name 中插入了我们不需要的SQL语句,将删除 users 表中的所有数据。在PHP中的 mysqli_query() 是不允许执行多个 SQL 语句的,但是在 SQLite 和 PostgreSQL 是可以同时执行多条SQL语句的,所以我们对这些用户的数据需要进行严格的验证。

防止SQL注入,我们需要注意:1、永远不要信任用户的输入。对用户的输入进行校验,可以通过正则表达式,或限制长度;对单引号和 双"-"进行转换等。2、永远不要使用动态拼装sql,可以使用参数化的sql或者直接使用存储过程进行数据查询存取。3、永远不要使用管理员权限的数据库连接,为每个应用使用单独的权限有限的数据库连接。4、不要把机密信息直接存放,加密或者hash掉密码和敏感的信息。5、应用的异常信息应该给出尽可能少的提示,最好使用自定义的错误信息对原始错误信息进行包装。6、sql注入的检测方法一般采取辅助软件或网站平台来检测,软件一般采用sql注入检测工具jsky,网站平台就有亿思网站安全平台检测工具。MDCSOFT SCAN等。采用MDCSOFT-IPS可以有效的防御SQL注入,XSS攻击等。

1、防止SQL注入:在脚本语言,如Perl和PHP你可以对用户输入的数据进行转义从而来防止SQL注入。PHP的MySQL扩展提供了mysqli_real_escape_string()函数来转义特殊的输入字符。例:

if (get_magic_quotes_gpc())
{
$name = stripslashes($name);
}
$name = mysqli_real_escape_string($conn, $name);
mysqli_query($conn, "SELECT * FROM users WHERE name='{$name}'");

2、Like语句中的注入:ike查询时,如果用户输入的值有"_"和"%",则会出现用户本来只是想查询"abcd_",查询结果中却有"abcd_"、"abcde"、"abcdf"等等;在PHP脚本中我们可以使用addcslashes()函数来处理以上情况,例:

$sub = addcslashes(mysqli_real_escape_string($conn, "%something_"), "%_");
// $sub == \%something\_
mysqli_query($conn, "SELECT * FROM messages WHERE subject LIKE '{$sub}%'");

addcslashes() 函数在指定的字符前添加反斜杠。其使用的语法格式:addcslashes(string,characters)。两个参数中 string:必需。规定要检查的字符串。characters:可选。规定受 addcslashes() 影响的字符或字符范围。

30、MySQL 导出数据

1、MySQL中可以使用SELECT...INTO OUTFILE语句来简单的导出数据到文本文件上,并通过命令选项来设置数据输出的指定格式,以下实例为导出 CSV 格式:

mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/tutorials.txt'
-> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
-> LINES TERMINATED BY '\r\n';

SELECT ... INTO OUTFILE 语句有以下属性:1、将一个数据库的数据写入一个文件,使用SELECT ... INTO OUTFILE,将文件读回数据库,使用LOAD DATA INFILE。2、ELECT...INTO OUTFILE 'file_name'形式的SELECT可以把被选择的行写入一个文件中。该文件被创建到服务器主机上,因此您必须拥有FILE权限,才能使用此语法。3、输出不能是一个已存在的文件。防止文件数据被篡改。4、需要有一个登陆服务器的账号来检索文件。否则 SELECT ... INTO OUTFILE 不会起任何作用。5、在UNIX中,该文件被创建后是可读的,权限由MySQL服务器所拥有。

2、导出表作为原始数据:mysqldump是mysql用于转存储数据库的实用程序。它主要产生一个SQL脚本,其中包含从头重新创建数据库所必需的命令。使用mysqldump导出数据需要使用 --tab 选项来指定导出文件指定的目录,该目标必须是可写的。例如将数据表 blog_table_test 导出到 /catalogname 目录中:

$ mysqldump -u root -p --no-create-info \
--tab=/catalogname blog blog_table_test
password ******

3、导出SQL格式的数据到指定文件,如下实例:

$ mysqldump -u root -p blog blog_table_test > test.txt
password ******

如果你需要导出整个数据库的数据,可以使用以下命令:

$ mysqldump -u root -p blog > database_test.txt
password ******

如果需要备份所有数据库,可以使用以下命令:

$ mysqldump -u root -p --all-databases > database_test.txt
password ******

4、将数据表及数据库拷贝至其他主机:如果你需要将数据拷贝至其他的 MySQL 服务器上, 你可以在 mysqldump 命令中指定数据库名及数据表,如果完整备份数据库,则无需使用特定的表名称。

如果需要将备份的数据库导入到MySQL服务器中,可以使用以下命令(需要确认数据库已经创建):

$ mysql -u root -p database_name < test.txt
password *****

你也可以使用以下命令将导出的数据直接导入到远程的服务器上,但请确保两台服务器是相通的,是可以相互访问的:

$ mysqldump -u root -p database_name \
| mysql -h other-host.com database_name

31、MySQL 导入数据

1、使用 LOAD DATA 导入数据,以下实例中将从当前目录中读取文件 test.txt ,将该文件中的数据插入到当前数据库的 blog_table_test 表中:

mysql> LOAD DATA LOCAL INFILE 'test.txt' INTO TABLE blog_table_test;

如果指定LOCAL关键词,则表明从客户主机上按路径读取文件。如果没有指定,则文件在服务器上按路径读取文件。

我们能明确地在LOAD DATA语句中指出列值的分隔符和行尾标记,但是默认标记是定位符和换行符

两个命令的 FIELDS 和 LINES 子句的语法是一样的。两个子句都是可选的,但是如果两个同时被指定,FIELDS 子句必须出现在 LINES 子句之前。

如果用户指定一个 FIELDS 子句,它的子句 (TERMINATED BY、[OPTIONALLY] ENCLOSED BY 和 ESCAPED BY) 也是可选的,不过,用户必须至少指定它们中的一个。

LOAD DATA 默认情况下是按照数据文件中列的顺序插入数据的,如果数据文件中的列与插入表中的列不一致,则需要指定列的顺序。例如在数据文件中的列顺序是 a,b,c,但在插入表的列顺序为b,c,a,则数据导入语法如下:

mysql> LOAD DATA LOCAL INFILE 'test.txt'
-> INTO TABLE blog_table_test (b, c, a);

2、使用 mysqlimport 导入数据:mysqlimport客户端提供了LOAD DATA INFILEQL语句的一个命令行接口。mysqlimport的大多数选项直接对应LOAD DATA INFILE子句。

从文件 test.txt 中将数据导入到 blog_table_test 数据表中, 可以使用以下命令:

$ mysqlimport -u root -p --local database_name test.txt
password *****

ysqlimport命令可以指定选项来设置指定格式,命令语句格式如下:

$ mysqlimport -u root -p --local --fields-terminated-by=":" \
--lines-terminated-by="\r\n" database_name test.txt
password *****

mysqlimport 语句中使用 --columns 选项来设置列的顺序:

$ mysqlimport -u root -p --local --columns=b,c,a \
database_name test.txt
password *****

32、注意事项

1、MySQL在Windows下数据库名、表名、列名、别名都不区分大小写。

2、数值类型括号后面的数字只是表示宽度而跟存储范围没有关系。

3、ORDER BY,GROUP BY,DISTINCT的字段需要添加在索引的后面。

4、不使用外键,高并发时容易引起死锁等问题

学习更多关于MySQL的知识请点击:学习MySQL(上)

申明

笔者也是学习前端不久,编辑整理这篇文章的目的,一方面是为了和大家一起分享和学习,另一方面也是为了自己能更好的理解其中的内容。若是文中有什么错误,欢迎大家指正批评,愿与大家在问题的争辩之中共同进步。愈激烈,愈深刻。