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

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

MySQL入门
MySQL管理

6．启动及关闭 MySQL服务器:
7． MySQL 用户设置
8．管理MySQL的命令

MySQL 连接

10．使用MySQL二进制方式连接
11．使用 PHP脚本连接 MySQL

MySQL 创建/删除数据库

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

MySQL 选择数据库

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

MySQL 创建/删除数据表

15．MySQL 创建数据表
16．通过命令提示符创建表
17．使用PHP脚本创建/删除数据表或插入数据

MySQL 插入数据

MySQL 查询数据
MySQL 选择数据
MySQL UPDATE
MySQL DELETE
MySQL 分组

MySQL 多表查询

MySQL 正则表达式

MySQL 事务

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

MySQL 索引

MySQL 元数据

数据库和数据表列表

MySQL 处理重复数据

过滤重复数据

删除重复数据

Like语句中的注入

MySQL 导出数据

导出表作为原始数据

MySQL 导入数据

使用mysqlimport 导入数据

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;

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

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

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']} ".
 "Title:{$row['w3cschool_title']} ".
 "Author:{$row['w3cschool_author']} ".
 "Submission Date : {$row['submission_date']} ".
 "-------------------------------- ";
}
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;

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

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

|           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 *****

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

$ 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 *****

欢迎分享，转载请注明来源：内存溢出

原文地址: http://outofmemory.cn/sjk/887903.html

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

发表评论

评论列表（0条）