mysql入门之1小时学会MySQL基础

MySQL入门

mySQL （关系型数据库管理系统）

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件。

MySQL是一种关系数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。
由于其社区版的性能卓越，搭配 PHP 和 Apache 可组成良好的开发环境。

1． 数据库（Database）是按照数据结构来组织、存储和管理数据的仓库，每个数据库都有一个或多个不同的API用于创建，访问，管理，搜索和复制所保存的数据。

2． 使用关系型数据库管理系统（RDBMS）来存储和管理的大数据量。关系型数据库是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。

3． RDBMS特点：

1.数据以表格的形式出现

2.每行为各种记录名称

3.每列为记录名称所对应的数据域

4.许多的行和列组成一张表单

5.若干的表单组成database

4． RDBMS 术语

冗余：存储两倍数据，冗余可以使系统速度更快。

主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。

外键：外键用于关联两个表。

复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。

索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。

参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性

5． MySQL是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

MySQL管理

6． 启动及关闭 MySQL服务器:

（1） 检查MySQL服务器是否启动：

ps -ef | grepmysqld

（2） 启动MySQL服务器:

root@host# cd/usr/bin

./safe_mysqld

（3） 关闭目前运行的 MySQL 服务器：

root@host# cd/usr/bin

./mysqladmin-u root -p shutdown

Enterpassword: ******

7． MySQL 用户设置

在 MySQL 数据库中的 user 表添加新用户：

root@host# mysql -u root –p //选择数据库
Enter password:*******
mysql> use mysql;
Database changed

mysql> INSERT INTO user
(host, user, password,
select_priv, insert_priv,update_priv) //设置权限Y
VALUES ('localhost', 'guest',
PASSWORD('guest123'), 'Y', 'Y','Y');

Query OK, 1 row affected (0.20 sec)

mysql> FLUSH PRIVILEGES;

注意需要执行 FLUSH PRIVILEGES 语句。这个命令执行后会重新载入授权表。

另外一种添加用户的方法为通过SQL的 GRANT命令

mysql> GRANTSELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON TUTORIALS.*
-> TO 'zara'@'localhost'
-> IDENTIFIED BY'zara123';

8． 管理MySQL的命令

USE 数据库名 :选择要操作的MySQL数据库：

mysql> use W3CSCHOOL;

Database changed

SHOW DATABASES: 列出 MySQL 数据库管理系统的数据库列表：

mysql> SHOWDATABASES;

SHOW TABLES: 显示指定数据库所有表，用该命令前需用 use 命令选择操作的数据库。

mysql> useW3CSCHOOL;

Database changed

mysql> SHOW TABLES;

SHOW COLUMNS FROM 数据表:显示数据表的属性，属性类型，主键信息，是否NULL，默认值等其他信息。

mysql> SHOW COLUMNSFROM W3Cschool_tbl;

+-----------------+--------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-----------------+--------------+------+-----+---------+-------+

SHOW INDEX FROM 数据表:显示数据表的详细索引信息，包括PRIMARY KEY（主键）。

SHOW TABLE STATUS LIKE 数据表\G: 该命令将输出MySQL数据库管理系统的性能及统计信息。

mysql> SHOW TABLESTATUS FROM W3CSCHOOL; # 显示数据库 W3CSCHOOL 中所有表的信息

mysql> SHOW TABLESTATUS from W3CSCHOOL LIKE 'W3Cschool%'; #表名以W3Cschool开头的表的信息

mysql> SHOW TABLESTATUS from W3CSCHOOL LIKE 'W3Cschool%'\G; #加上 \G，查询结果按列打印

9． PHP MySQL函数格式：mysql_function(value,value,...);

MySQL 连接

10．使用MySQL二进制方式连接

[root@host]# mysql -uroot -p
Enter password:******
登录成功后会出现mysql> 命令提示窗口，你可以在上面执行任何 SQL 语句。
退出 mysql> 命令提示窗口可以使用exit 命令：mysql> exit

11．使用 PHP脚本连接 MySQL

PHP 提供了 mysql_connect() 函数来连接数据库。
connectionmysql_connect(server,user,passwd,new_link,client_flag);5各参数均可选
使用PHP的mysql_close() 函数来断开与MySQL数据库的链接。
bool mysql_close (resource $link_identifier );
通常不要用mysql_close()，因为已打开的非持久连接会在脚本执行完毕后自动关闭。
mysql_close() 不会关闭由 mysql_pconnect() 建立的持久连接

$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Could not connect: ' .mysql_error());
}
echo 'Connected successfully';
mysql_close($conn);

MySQL 创建/删除数据库

12．使用 mysqladmin创建数据库

用root用户登录，root用户拥有最高权限，可以使用 mysql mysqladmin 命令来创建数据库。
[root@host]#mysqladmin -u root -pcreate/drop W3CSCHOOL
Enter password:***** 

13．使用 PHP脚本创建数据库

PHP使用 mysql_query 函数来创建或者删除 MySQL 数据库。

bool mysql_query( sql, connection);
 
$conn = mysql_connect($dbhost,$dbuser, $dbpass);
if(! $conn )
{
 die('连接错误: ' . mysql_error());
}
echo '连接成功br />';
$sql= 'CREATE/DROP DATABASE W3CSCHOOL';
$retval= mysql_query( $sql, $conn );
if(! $retval )
{
 die('创建数据库失败: ' . mysql_error());
}
echo "数据库 W3CSCHOOL创建成功\n";
mysql_close($conn);

MySQL 选择数据库

14．使用PHP脚本选择MySQL数据库

PHP 提供了函数 mysql_select_db来选取一个数据库。

bool mysql_select_db( db_name,connection );
$conn = mysql_connect($dbhost,$dbuser, $dbpass);
if(! $conn )
{
 die('连接失败: ' . mysql_error());
}

echo '连接成功';
mysql_select_db('W3CSCHOOL' );
mysql_close($conn);

MySQL 创建/删除数据表

15．MySQL 创建数据表

创建MySQL数据表需要以下信息：

表名

表字段名

定义每个表字段

创建语法：CREATE TABLE table_name (column_name column_type);

删除语法：DROP TABLE table_name ;

以下例子中我们将在 W3CSCHOOL 数据库中创建数据表w3cschool_tbl：

tutorials_tbl(
 tutorial_id INT NOT NULL AUTO_INCREMENT,
 tutorial_title VARCHAR(100) NOT NULL,
 tutorial_author VARCHAR(40) NOT NULL,
 submission_date DATE,
 PRIMARY KEY ( w3cschool_id )
);

16．通过命令提示符创建表

使用 SQL 语句 CREATE TABLE 来创建数据表。

mysql> CREATE TABLE w3cschool_tbl(
 -> w3cschool_id INT NOTNULL AUTO_INCREMENT,
 -> w3cschool_titleVARCHAR(100) NOT NULL,
 -> w3cschool_authorVARCHAR(40) NOT NULL,
 -> submission_date DATE,
 -> PRIMARY KEY (w3cschool_id )
 -> );

MySQL命令终止符为分号 (;) 。 

17．使用PHP脚本创建/删除数据表或插入数据

语法：bool mysql_query( sql, connection);

$sql = "CREATE TABLEtutorials_tbl( "创建
  "tutorial_id INT NOTNULL AUTO_INCREMENT, ".
  "tutorial_titleVARCHAR(100) NOT NULL, ".
  "tutorial_authorVARCHAR(40) NOT NULL, ".
  "submission_dateDATE, ".
  "PRIMARY KEY (tutorial_id )); "
;
$sql = "DROP TABLEw3cschool_tbl";删除
 
mysql_select_db( 'TUTORIALS' );
$retval = mysql_query( $sql, $conn);   //判断是否成功而设置的参数；
if(! $retval )
{
 die('数据表创建失败: ' . mysql_error());
}
echo "数据表创建成功\n";
mysql_close($conn);

MySQL 插入数据

18．向MySQL数据表插入数据通用的 INSERT INTO SQL语法：

INSERT INTO table_name (field1, field2,...fieldN )
                       VALUES
                       ( value1,value2,...valueN );

如果数据是字符型，必须使用单引号或者双引号，如："value"。

w3cschool_tbl表插入一条数据:

mysql> INSERT INTOw3cschool_tbl
     ->(w3cschool_title, w3cschool_author,submission_date)
     ->VALUES
     ->("Learn PHP", "JohnPoul", NOW());

(->)不是SQL语句的一部分，它仅表示一个新行，如SQL语句太长，可通过回车键创建一个新行编写SQL语句，SQL语句的命令结束符为分号（;）。

19．使用PHP脚本插入数据

  $sql = "INSERT INTO w3cschool_tbl ".
       "(w3cschool_title,w3cschool_author,submission_date) ".
       "VALUES ".
      "('$w3cschool_title','$w3cschool_author','$submission_date')";

MySQL 查询数据

20．为在MySQL数据库中查询数据通用的 SELECT语法：

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[OFFSET M ][LIMIT N]

可以通过SELECT 命令读取一条或者多条记录。

可以通过OFFSET指定SELECT语句开始查询的数据偏移量默认情况下偏移量为0。

可以使用 LIMIT 属性来设定返回的记录数。

你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据

mysql> SELECT * from w3cschool_tbl

 
21．使用PHP脚本来获取数据

使用PHP函数的mysql_query()及SQL SELECT命令来获取数据。
该函数用于执行SQL命令，然后通过 PHP 函数 mysql_fetch_array() 来使用或输出所有查询的数据。

尝试以下实例来显示数据表w3cschool_tbl 的所有记录

$sql = 'SELECT w3cschool_id,w3cschool_title,
               w3cschool_author,submission_date
      FROM w3cschool_tbl';
mysql_select_db('W3CSCHOOL');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' .mysql_error());
}
while($row =mysql_fetch_array($retval, MYSQL_ASSOC))
while($row =mysql_fetch_assoc($retval))

用MYSQL_NUM参数显示数据表

while($row =mysql_fetch_array($retval, MYSQL_NUM))

用MYSQL_NUM参数显示数据表

{
    echo "Tutorial ID:{$row['w3cschool_id']}  br>".
         "Title:{$row['w3cschool_title']} br> ".
         "Author:{$row['w3cschool_author']} br> ".
         "Submission Date : {$row['submission_date']}br> ".
        "--------------------------------br>";
}
mysql_free_result($retval);     释放游标内存
echo "Fetched data successfully\n";
mysql_close($conn);

MYSQL_ASSOC， 设置该参数查询结果返回关联数组，你可以使用字段名称来作为数组的索引。

MySQL 选择数据

22．从MySQL表中使用SQL SELECT语句来读取数据。

如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句中
以下是SQL SELECT语句使用 WHERE 子句从数据表中读取数据的通用语法：

SELECT field1,field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND[OR]] condition2.....

WHERE子句也可以运用于SQL的 DELETE 或者 UPDATE 命令。
使用 LIKE 来比较字符串，否则MySQL的WHERE子句的字符串比较是不区分大小写的。 你可以使用 BINARY 关键字来设定WHERE子句的字符串比较是区分大小写的。

 23．使用PHP脚本读取数据

使用PHP函数的mysql_query()及相同的SQL SELECT 带上 WHERE 子句的命令来获取数据。该函数用于执行SQL命令，然后通过 mysql_fetch_array() 来输出所有查询的数据。

$sql = 'SELECT w3cschool_id,w3cschool_title,
               w3cschool_author, submission_date
        FROM w3cschool_tbl
        WHEREw3cschool_author="Sanjay"';

 MySQL UPDATE

24．修改或更新MySQL中的数据，我们可以使用SQL UPDATE 命令来操作。

通用SQL语法：

UPDATE table_name SET field1=new-value1, field2=new-value2

[WHERE Clause]

更新数据表中 w3cschool_id 为 3 的 w3cschool_title 字段值：

mysql>UPDATE w3cschool_tbl
   -> SET w3cschool_title='Learning JAVA'
-    > WHERE w3cschool_id=3;

使用PHP脚本更新数据

$sql = 'UPDATE w3cschool_tbl
         SETw3cschool_title="Learning JAVA"
          WHERE w3cschool_id=3';

MySQL DELETE

25．DELETE FROM table_name[WHERE Clause

如果没有指定 WHERE 子句，MySQL表中的所有记录将被删除。

可以在 WHERE 子句中指定任何条件

删除w3cschool_tbl 表中 w3cschool_id 为3 的记录

mysql> DELETE FROMw3cschool_tbl WHERE w3cschool_id=3;

用 PHP 脚本删除数据

$sql = 'DELETE FROMw3cschool_tbl
        WHERE w3cschool_id=3';

MySQL LIKE 子句

QL LIKE 子句中使用百分号(%)字符来表示任意字符

没有使用百分号(%),LIKE 子句与等号（=）的效果是一样的。
26．QL SELECT语句使用 LIKE子句从数据表中读取数据的通用语法：
SELECT field1,field2,...fieldN table_name1, table_name2...
WHERE field1 LIKEcondition1 [AND [OR]] filed2 = 'somevalue'
LIKE 通常与 % 一同使用，类似于一个元字符的搜索

在PHP脚本中使用 LIKE 子句

$sql = 'SELECTw3cschool_id, w3cschool_title,
               w3cschool_author,submission_date
        FROM w3cschool_tbl
        WHERE w3cschool_author LIKE"%jay%"';

MySQL 排序

SELECT field1,field2,...fieldN table_name1, table_name2...

ORDER BY field1,[field2...] [ASC [DESC]]

ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。默认情况下，它是按升排列。

MySQL 分组

SELECT column_name,function(column_name)
FROM table_name
WHERE column_nameoperator value
GROUP BY column_name;

WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。

coalesce 来设置一个可以取代NUll 的名称，coalesce 语法：

select coalesce(a,b,c);

参数说明：如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。

mysql> SELECTcoalesce(name, '总数'), SUM(singin) as singin_countFROM  employee_tbl GROUP BY name WITHROLLUP;

+--------------------------+--------------+

| coalesce(name, '总数') | singin_count |

+--------------------------+--------------+

| 小丽                   |            2 |

| 小明                   |            7 |

| 小王                   |            7 |

| 总数                   |           16 |

+--------------------------+--------------+

 MySQL 多表查询

27．在SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。

JOIN 按照功能大致分为如下三类：

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）：与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

| w3cschool_author |w3cschool_count |

+-----------------+----------------+

| mahran          |             20 |

| mahnaz          |           NULL |

| Jen             |           NULL |

| Gill            |             20 |

| John Poul       |              1 |

| Sanjay          |              1 |

+-----------------+----------------+

mysql> SELECT * fromw3cschool_tbl;

+-------------+----------------+-----------------+-----------------+

| w3cschool_id | w3cschool_title | w3cschool_author |submission_date |

+-------------+----------------+-----------------+-----------------+

|           1 | Learn PHP      | John Poul       |2007-05-24      |
|           2 | LearnMySQL    | Abdul S         | 2007-05-24      |
|           3 | JAVATutorial  | Sanjay          | 2007-05-06      |

连接以上两张表来读取w3cschool_tbl表中所有w3cschool_author字段在tcount_tbl表对应的w3cschool_count字段值：

mysql> SELECTa.w3cschool_id, a.w3cschool_author, b.w3cschool_count FROM w3cschool_tbl aINNER JOIN tcount_tbl b ON a.w3cschool_author = b.w3cschool_author;

+-----------+---------------+--------------+

| w3cschool_id | w3cschool_author | w3cschool_count |

+-----------+---------------+--------------+

|         1 | John Poul     |            1 |

|         3 | Sanjay        |            1 |

w3cschool_tbl 为左表，tcount_tbl 为右表，

mysql> SELECTa.w3cschool_id, a.w3cschool_author, b.w3cschool_count FROM w3cschool_tbl a LEFTJOIN tcount_tbl b ON a.w3cschool_author = b.w3cschool_author;

+-------------+-----------------+----------------+

| w3cschool_id | w3cschool_author | w3cschool_count |

+-------------+-----------------+----------------+

|           1 | John Poul       |             1 |

|           2 | Abdul S         |           NULL |

|           3 | Sanjay          |              1 |

左边的数据表w3cschool_tbl的所有选取的字段数据，即便在右侧表tcount_tbl中没有对应的w3cschool_author字段值Abdul S。

MySQL NULL

IS NULL: 当列的值是NULL,此运算符返回true。
IS NOT NULL: 当列的值不为NULL, 运算符返回true。

NULL值与任何其它值的比较（即使是NULL）永远返回false，

使用PHP脚本处理 NULL 值:

PHP脚本中你可以在 if...else 语句来处理变量是否为空，并生成相应的条件语句。

MySQL 正则表达式

28．MySQL中使用 REGEXP 操作符来进行正则表达式匹配。

^     匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 '\n' 或 '\r' 之后的位置。
$     匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 '\n' 或 '\r' 之前的位置。
.      匹配除"\n" 之外的任何单个字符。要匹配包括 '\n' 在内的任何字符，请使用象 '[.\n]' 的模式。

实例(表名：person_tbl )来加深我们的理解：

查找name字段中以'st'为开头的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';

查找name字段中以'ok'为结尾的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

查找name字段中包含'mar'字符串的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';

查找name字段中以元音字符开头或以'ok'字符串结尾的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP'^[aeiou]|ok$';

 MySQL 事务

29．MySQL 事务主要用于处理操作量大，复杂度高的数据。

在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务

事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行

事务用来管理insert,update,delete语句

事务必须满足4个条件（ACID）： Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）

1、事务的原子性：一组事务，要么成功；要么撤回。

2、稳定性： 有非法数据（外键约束之类），事务撤回。

3、隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。

4、可靠性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里。

 在MySQL控制台使用事务来操作:

1，开始一个事务

start transaction

2, 做保存点

savepoint 保存点名称

3, 操作

4，可以回滚，可以提交，没有问题，就提交，有问题就回滚。

 PHP中使用事务实例

mysql_query("SETAUTOCOMMIT=0");//设置为不自动提交，因为MYSQL默认立即执行mysql_query("BEGIN");//开始事务定义

if(!mysql_query("insertinto trans (id) values('2')"))
{
mysql_query("ROOLBACK");//判断当执行失败时回滚
}
mysql_query("COMMIT");//执行事务
mysql_close($handler);
MySQL ALTER

30．修改数据表名或者修改数据表字段时，就需要使用到MySQL ALTER命令。

使用了 ALTER 命令及 DROP 子句来删除以上创建表的 i字段：

mysql> ALTER TABLEtestalter_tbl  DROP i;

数据表中只剩余一个字段则无法使用DROP来删除字段。

ADD 子句来想数据表中添加列，在表 testalter_tbl 中添加 i 字段，并定义数据类型:

mysql> ALTER TABLEtestalter_tbl ADD i INT;

以下 ALTERTABLE 语句, 在执行成功后，使用 SHOW COLUMNS 查看表结构的变化：

ALTER TABLEtestalter_tbl DROP i;
ALTER TABLEtestalter_tbl ADD i INT FIRST;
ALTER TABLEtestalter_tbl DROP i;
ALTER TABLEtestalter_tbl ADD i INT AFTER c;

FIRST 和 AFTER 关键字只占用于 ADD 子句，所以如果你想重置数据表字段的位置就需要先使用 DROP 删除字段然后使用 ADD 来添加字段并设置位置。

修改字段类型及名称:

31．在ALTER命令中使用 MODIFY 或 CHANGE 子句 。

把字段 c 的类型从 CHAR(1) 改为 CHAR(10)，可以执行以下命令:
mysql> ALTER TABLEtestalter_tbl MODIFY c CHAR(10);
使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段的类型及名称。尝试如下实例：
mysql> ALTER TABLEtestalter_tbl CHANGE i j BIGINT;
p如果你现在想把字段 j 从 BIGINT 修改为 INT，SQL语句如下：
mysql> ALTER TABLEtestalter_tbl CHANGE j j INT;

ALTER修改字段的默认值，mysql> ALTER TABLEtestalter_tbl ALTER i SET DEFAULT 1000;
ALTER 及DROP删除字段的默认值, ALTER TABLEtestalter_tbl ALTER i DROP DEFAULT;
ALTER及 TYPE修改数据表类型，mysql> ALTER TABLEtestalter_tbl TYPE = MYISAM;
ALTER TABLE 使用RENAME修改数据表的名称，mysql> ALTER TABLEtestalter_tbl RENAME TO alter_tbl;

MySQL 索引

索引可以大大提高MySQL的检索速度
索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索包含多个列。
创建索引，确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
3方式创建普通索引

CREATE INDEX indexName ONmytable(username(length));

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

修改表结构

ALTER mytable ADD INDEX[indexName] ON (username(length))

创建表的时候直接指定

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

删除索引的语法

DROP INDEX [indexName] ONmytable;

 唯一索引：前面加UNIQUE

 使用ALTER 命令添加和删除索引

有四种方式来添加数据表的索引：

ALTER TABLE tbl_name ADD PRIMARYKEY (column_list):该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUEindex_name (column_list):这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD INDEXindex_name (column_list):添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXTindex_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

 使用 ALTER 命令添加和删除主键

主键只能作用于一个列上，添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。实例如下：

mysql> ALTER TABLEtestalter_tbl MODIFY i INT NOT NULL;

mysql> ALTER TABLEtestalter_tbl ADD PRIMARY KEY (i);

你也可以使用 ALTER 命令删除主键：

mysql> ALTER TABLEtestalter_tbl DROP PRIMARY KEY;

SHOW INDEX 命令列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。

mysql> SHOW INDEX FROMtable_name\G

MySQL 临时表

临时表只在当前连接可见，当关闭连接时，MySQL会自动删除表并释放所有空间。

mysql> CREATE TEMPORARY TABLE SalesSummary

mysql> DROP TABLE SalesSummary;

MySQL 复制表

创建新的克隆表clone_tbl。如果你想拷贝数据表的数据你可以使用 INSERT INTO... SELECT 语句来实现。

mysql> INSERT INTOclone_tbl (w3cschool_id,
    ->                        w3cschool_title,
    ->                        w3cschool_author,
    ->                        submission_date)
    -> SELECT w3cschool_id,w3cschool_title,
    ->       w3cschool_author,submission_date
->FROM w3cschool_tbl;

 MySQL 元数据

想知道MySQL以下三种信息：

查询结果信息： SELECT,UPDATE 或 DELETE语句影响的记录数。
数据库和数据表的信息： 包含了数据库及数据表的结构信息。
MySQL服务器信息： 包含了数据库服务器的当前状态，版本号等。

（1）使用do( ) 执行  $query

my $count = $dbh->do($query);

（2）使用prepare( )及 execute( ) 执行  $query

my $sth =$dbh->prepare ($query);

my $count =$sth->execute ( ); 

在PHP中，使用mysql_affected_rows( ) 函数获取查询语句影响的记录数。

$result_id =mysql_query ($query, $conn_id);
# 如果查询失败返回
$count = ($result_id ?mysql_affected_rows ($conn_id) : 0);
print ("$countrows were affected\n");

 数据库和数据表列表

PERL 实例

# 获取当前数据库中所有可用的表。

my @tables =$dbh->tables ( );
foreach $table (@tables){
 print "Table Name $table\n";
}

PHP 实例：

$db_list =mysql_list_dbs($con);
while ($db = mysql_fetch_object($db_list))
{
 echo $db->Database . "br/>";
}

MySQL 序列

MySQL序列是一组整数：1, 2, 3, ...，

用 MySQLAUTO_INCREMENT 来定义列。

mysql> CREATE TABLEinsect

    -> (

    -> id INT UNSIGNED NOT NULLAUTO_INCREMENT,

用 SQL中的LAST_INSERT_ID() 函数来获取最后的插入表中的自增列的值。

 PERL实例

使用mysql_insertid 属性来获取 AUTO_INCREMENT 的值。实例如下：

$dbh->do("INSERT INTO insect (name,date,origin)

VALUES('moth','2001-09-14','windowsill')");

my $seq =$dbh->{mysql_insertid};

PHP实例

PHP 通过 mysql_insert_id ()函数来获取执行的插入SQL语句中 AUTO_INCREMENT列的值。

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

重置序列

删除了数据表中的多条记录，并对剩下数据的AUTO_INCREMENT列进行重新排列，那么你可以通过删除自增的列，然后重新添加来实现。

mysql> ALTER TABLEinsect DROP id;

mysql> ALTER TABLEinsect

    -> ADD id INT UNSIGNED NOT NULLAUTO_INCREMENT FIRST,

    -> ADD PRIMARY KEY (id);

一般情况下序列的开始值为1，但如果你需要指定一个开始值100：

-> id INT UNSIGNEDNOT NULL AUTO_INCREMENT = 100,

或在表创建成功后，通过以下语句来实现：

mysql> ALTER TABLE tAUTO_INCREMENT = 100;

 MySQL 处理重复数据

防止表中出现重复数据

在MySQL数据表中设置指定的字段为PRIMARY KEY（主键）或者UNIQUE（唯一）索引保证数据的唯一性。

设置表中字段first_name，last_name数据不能重复，你可以设置双主键模式来设置数据的唯一性， 如果你设置了双主键，那么那个键的默认值不能为NULL，可设置为NOT NULL。如下所示：

CREATE TABLE person_tbl
(
 first_name CHAR(20) NOT NULL,
 last_name CHAR(20) NOT NULL,
 sexCHAR(10),
 PRIMARY KEY (last_name, first_name)
);

INSERT IGNOREINTO与INSERT INTO的区别就是INSERT IGNORE会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。

用了INSERT IGNORE INTO，执行后不会出错，也不会向数据表中插入重复数据：

mysql> INSERT IGNORE INTO person_tbl(last_name, first_name)

    ->VALUES( 'Jay', 'Thomas');

Query OK, 1 row affected (0.00 sec)

REPLACE INTO into如果存在primary 或 unique相同的记录，则先删除掉。再插入新记录。

   UNIQUE(last_name, first_name)

查询重复记录

select user_name,count(*) as count fromuser_table group by user_name having count>1;

select * from people

where peopleId in (select peopleId from peoplegroup by peopleId having count(peopleId) > 1)

统计重复数据

统计表中 first_name 和 last_name的重复记录数：

mysql> SELECT COUNT(*) as repetitions,last_name, first_name

 ->FROM person_tbl
 ->GROUP BY last_name, first_name
 ->HAVING repetitions > 1;

 过滤重复数据

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

mysql> SELECT DISTINCT last_name, first_name

    ->FROM person_tbl

    ->ORDER BY last_name;

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

mysql> SELECT last_name, first_name
 ->FROM person_tbl
 ->GROUP BY (last_name, first_name);

 删除重复数据

删除数据表中的重复数据，你可以使用以下的SQL语句：

mysql> CREATE TABLE tmp SELECT last_name,first_name, sex

 ->     FROMperson_tbl;

 ->     GROUP BY(last_name, first_name);

mysql> DROP TABLE person_tbl;

mysql> ALTER TABLE tmp RENAME TO person_tbl;

也可以在数据表中添加 INDEX（索引） 和 PRIMAY KEY（主键）这种简单的方法来删除表中的重复记录。方法如下：

mysql> ALTER IGNORE TABLE person_tbl
 ->ADD PRIMARY KEY (last_name, first_name);

MySQL 及 SQL 注入

没有过滤特殊字符时，出现的SQL情况：

// 设定$name 中插入了我们不需要的SQL语句

$name = "Qadir';DELETE FROM users;";

mysql_query("SELECT* FROM users WHERE name='{$name}'");

以上的注入语句中，我们没有对 $name 的变量进行过滤，$name中插入了我们不需要的SQL语句，将删除 users 表中的所有数据。

防止SQL注入，注意以下几个要点：

1.永远不要信任用户的输入。对用户的输入进行校验，可以通过正则表达式，或限制长度；对单引号和双"-"进行转换等。

2.永远不要使用动态拼装sql，可以使用参数化的sql或者直接使用存储过程进行数据查询存取。

3.永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。

4.不要把机密信息直接存放，加密或者hash掉密码和敏感的信息。

5.应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装

6.sql注入的检测方法一般采取辅助软件或网站平台来检测，软件一般采用sql注入检测工具jsky，网站平台就有亿思网站安全平台检测工具。MDCSOFT SCAN等。采用MDCSOFT-IPS可以有效的防御SQL注入，XSS攻击等。

 在脚本语言，如Perl和PHP可以对用户输入的数据进行转义从而来防止SQL注入。

PHP的MySQL扩展提供了mysql_real_escape_string()函数来转义特殊的输入字符。

if (get_magic_quotes_gpc())
{
 $name = stripslashes($name);
}

$name =mysql_real_escape_string($name);
mysql_query("SELECT * FROMusers WHERE name='{$name}'");

Like语句中的注入

like查询时，如用户输入的值有"_"和"%"，则会出现这种情况：用户本只想查询"abcd_"，查询结果中却有"abcd_"、"abcde"、"abcdf"等等；用户要查询"30%"（注：百分之三十）时也会出现问题。

在PHP脚本中我们可以使用addcslashes()函数来处理以上情况，如下实例：

$sub =addcslashes(mysql_real_escape_string("%something_"), "%_");

// $sub == \%something\_

mysql_query("SELECT * FROMmessages WHERE subject LIKE '{$sub}%'");

addcslashes() 函数在指定的字符前添加反斜杠。

语法格式:

addcslashes(string,characters) 

MySQL 导出数据

使用 SELECT ...INTO OUTFILE 语句导出数据

将数据表w3cschool_tbl 数据导出到 /tmp/tutorials.txt 文件中:

mysql> SELECT * FROMtutorials_tbl

    -> INTO OUTFILE '/tmp/tutorials.txt';

生成一个文件，各值用逗号隔开。这种格式可以被许多程序使用。

SELECT a,b,a+b INTOOUTFILE '/tmp/result.text'
FIELDS TERMINATED BY',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY'\n'
FROM test_table;

SELECT ... INTO OUTFILE 语句有以下属性:

LOAD DATA INFILE是SELECT ... INTO OUTFILE的逆操作，SELECT句法。为了将一个数据库的数据写入一个文件，使用SELECT ... INTO OUTFILE，为了将文件读回数据库，使用LOAD DATA INFILE。

SELECT...INTO OUTFILE 'file_name'形式的SELECT可以把被选择的行写入一个文件中。该文件被创建到服务器主机上，因此您必须拥有FILE权限，才能使用此语法。

输出不能是一个已存在的文件。防止文件数据被篡改。

你需要有一个登陆服务器的账号来检索文件。否则SELECT ... INTO OUTFILE 不会起任何作用。

在UNIX中，该文件被创建后是可读的，权限由MySQL服务器所拥有。这意味着，虽然你就可以读取该文件，但可能无法将其删除

 导出表作为原始数据

mysqldump是MySQL用于转存储数据库的实用程序。

将数据表tutorials_tbl 导出到 /tmp 目录中：

$ mysqldump -u root -p--no-create-info \

            --tab=/tmp W3CSCHOOL w3cschool_tbl

password ******

导出整个数据库的数据，可以使用以下命令：

$ mysqldump -u root -pW3CSCHOOL > database_dump.txt

password ******

备份所有数据库，可以使用以下命令：

$ mysqldump -u root -p--all-databases > database_dump.txt

password ******

在 mysqldump 命令中指定数据库名及数据表。

在源主机上执行以下命令，将数据备份至 dump.txt 文件中:

$ mysqldump -u root -pdatabase_name table_name > dump.txt

password *****

将备份的数据库导入到MySQL服务器中，可以使用以下命令，使用以下命令你需要确认数据库已经创建：

$ mysql -u root -pdatabase_name dump.txt password *****

以下命令将导出的数据直接导入到远程的服务器上，但请确保两台服务器是相通的，是可以相互访问的：/p>

$ mysqldump -u root -pdatabase_name \

       | mysql -h other-host.com database_name

 MySQL 导入数据

从当前目录中读取文件dump.txt ，将该文件中的数据插入到当前数据库的 mytbl 表中。

mysql> LOAD DATALOCAL INFILE 'dump.txt' INTO TABLE mytbl;

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

mysql> LOAD DATALOCAL INFILE 'dump.txt' INTO TABLE mytbl

  -> FIELDS TERMINATED BY ':'

  -> LINES TERMINATED BY '\r\n';

指定列的顺序。

如，在数据文件中的列顺序是a,b,c，但在插入表的列顺序为b,c,a，则数据导入语法如下：

mysql> LOAD DATALOCAL INFILE 'dump.txt'

    -> INTO TABLE mytbl (b, c, a);

使用mysqlimport 导入数据

mysqlimport客户端提供了LOADDATA INFILEQL语句的一个命令行接口。mysqlimport的大多数选项直接对应LOAD DATA INFILE子句。

从文件 dump.txt 中将数据导入到 mytbl数据表中, 可以使用以下命令：

$ mysqlimport -u root-p --local database_name dump.txt

password *****

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

$ mysqlimport -u root-p --local --fields-terminated-by=":" \

   --lines-terminated-by="\r\n"  database_name dump.txt

password *****

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

$ mysqlimport -u root-p --local --columns=b,c,a \

    database_name dump.txt

password *****