数据库

数据表

进程

其他

内连接 t1 和 t2

SELECT c1, c2 FROM t1INNER JOIN t2 ON condition

左连接 t1 和 t1

SELECT c1, c2 FROM t1LEFT JOIN t2 ON condition

右连接 t1 和 t2

SELECT c1, c2 FROM t1RIGHT JOIN t2 ON condition

执行全外连接

SELECT c1, c2 FROM t1FULL OUTER JOIN t2 ON condition

生成表中行的笛卡尔积

SELECT c1, c2 FROM t1CROSS JOIN t2

另一种执行交叉连接的方法

SELECT c1, c2 FROM t1, t2

使用 INNER JOIN 子句将 t1 连接到自身

SELECT c1, c2FROM t1 AINNER JOIN t1 B ON condition

使用 SQL 运算符组合来自两个查询的行

SELECT c1, c2 FROM t1UNION [ALL]SELECT c1, c2 FROM t2

返回两个查询的交集

SELECT c1, c2 FROM t1INTERSECTSELECT c1, c2 FROM t2

从另一个结果集中减去一个结果集

SELECT c1, c2 FROM t1MINUSSELECT c1, c2 FROM t2

使用模式匹配 %, _ 查询行

SELECT c1, c2 FROM t1WHERE c1 [NOT] LIKE pattern

查询列表中的行

SELECT c1, c2 FROM tWHERE c1 [NOT] IN value_list

查询两个值之间的行

SELECT c1, c2 FROM tWHERE  c1 BETWEEN low AND high

检查表中的值是否为 NULL

SELECT c1, c2 FROM tWHERE  c1 IS [NOT] NULL

• ASCII()
• BIN()
• BIT_LENGTH()
• CHAR()
• CHARACTER_LENGTH()
• CHAR_LENGTH()
• CONCAT()
• CONCAT_WS()
• ELT()
• EXPORT_SET()
• FIELD()
• FIND_IN_SET()
• FORMAT()
• FROM_BASE64()
• HEX()
• INSERT()
• INSTR()
• LCASE()
• LEFT()
• LENGTH()
• LIKE
• LOAD_FILE()
• LOCATE()
• LOWER()
• LPAD()
• LTRIM()
• MAKE_SET()
• MATCH
• MID()
• NOT LIKE
• NOT REGEXP
• OCT()
• OCTET_LENGTH()
• ORD()
• POSITION()
• QUOTE()
• REGEXP
• REGEXP_INSTR()
• REGEXP_LIKE()
• REGEXP_REPLACE()
• REGEXP_SUBSTR()
• REPEAT()
• REPLACE()
• REVERSE()
• RIGHT()
• RLIKE
• RPAD()
• RTRIM()
• SOUNDEX()
• SOUNDS LIKE
• SPACE()
• STRCMP()
• SUBSTR()
• SUBSTRING()
• SUBSTRING_INDEX()
• TO_BASE64()
• TRIM()
• UCASE()
• UNHEX()
• UPPER()
• WEIGHT_STRING()

• ADDDATE()
• ADDTIME()
• CONVERT_TZ()
• CURDATE()
• CURRENT_DATE()
• CURRENT_TIME()
• CURRENT_TIMESTAMP()
• CURTIME()
• DATE()
• DATE_ADD()
• DATE_FORMAT()
• DATE_SUB()
• DATEDIFF()
• DAY()
• DAYNAME()
• DAYOFMONTH()
• DAYOFWEEK()
• DAYOFYEAR()
• EXTRACT()
• FROM_DAYS()
• FROM_UNIXTIME()
• GET_FORMAT()
• HOUR()
• LAST_DAY
• LOCALTIME()
• LOCALTIMESTAMP()
• MAKEDATE()
• MAKETIME()
• MICROSECOND()
• MINUTE()
• MONTH()
• MONTHNAME()
• NOW()
• PERIOD_ADD()
• PERIOD_DIFF()
• QUARTER()
• SEC_TO_TIME()
• SECOND()
• STR_TO_DATE()
• SUBDATE()
• SUBTIME()
• SYSDATE()
• TIME()
• TIME_FORMAT()
• TIME_TO_SEC()
• TIMEDIFF()
• TIMESTAMP()
• TIMESTAMPADD()
• TIMESTAMPDIFF()
• TO_DAYS()
• TO_SECONDS()
• UNIX_TIMESTAMP()
• UTC_DATE()
• UTC_TIME()
• UTC_TIMESTAMP()
• WEEK()
• WEEKDAY()
• WEEKOFYEAR()
• YEAR()
• YEARWEEK()
• GET_FORMAT()

• ->
• ->>
• JSON_ARRAY()
• JSON_ARRAY_APPEND()
• JSON_ARRAY_INSERT()
• JSON_CONTAINS()
• JSON_CONTAINS_PATH()
• JSON_DEPTH()
• JSON_EXTRACT()
• JSON_INSERT()
• JSON_KEYS()
• JSON_LENGTH()
• JSON_MERGE()（已弃用）
• JSON_MERGE_PATCH()
• JSON_MERGE_PRESERVE()
• JSON_OBJECT()
• JSON_OVERLAPS()（8.0.17 引入）
• JSON_PRETTY()
• JSON_QUOTE()
• JSON_REMOVE()
• JSON_REPLACE()

• DROP PROCEDURE IF EXISTS FillDateTable;delimiter //CREATE PROCEDURE FillDateTable()    LANGUAGE SQL    NOT DETERMINISTIC    CONTAINS SQL    SQL SECURITY DEFINER    COMMENT ''BEGIN  drop table if exists datetable;  create table datetable (thedate datetime primary key, isweekday smallint);  SET @x := date('2000-01-01');  REPEAT     insert into datetable (thedate, isweekday) SELECT @x, case when dayofweek(@x) in (1,7) then 0 else 1 end;    SET @x := date_add(@x, interval 1 day);    UNTIL @x >= '2030-12-31' END REPEAT;END//delimiter ;CALL FillDateTable;-- 可用于查询缺失的日期数据

  
  

  select date from (SELECT DATE_FORMAT(DATE_SUB(last_day(curdate()), INTERVAL xc-1 day), '%Y-%m-%d') as dateFROM ( SELECT @xi:=@xi+1 as xc from (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) xc1, (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) xc2,  (SELECT @xi:=0) xc0 ) xcxc) x0 where x0.date >= (select date_add(curdate(),interval-day(curdate())+1 day)); 

  
  

  输出结果：

  "date" 

  "2022-02-28" 

  "2022-02-27" 

  "2022-02-26" 

  "2022-02-25" 

  "2022-02-24" 

  "2022-02-23" 

  "2022-02-22" 

  "2022-02-21" 

  "2022-02-20" 

  "2022-02-19" 

  "2022-02-18" 

  "2022-02-17" 

  "2022-02-16" 

  "2022-02-15"

  "2022-02-14"

  "2022-02-13"

  "2022-02-12"

  "2022-02-11"

  "2022-02-10" 

  "2022-02-09"

  "2022-02-08"

  "2022-02-07"

  "2022-02-06"

  "2022-02-05"

  "2022-02-04"

  "2022-02-03"

  "2022-02-02"

  "2022-02-01"

  
  
  

  MySQL是最流行的关系型数据库管理系统，在WEB应用方面MySQL是最好的RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。

  在本教程中，会让大家快速掌握MySQL的基本知识，并轻松使用MySQL数据库。

  ---

  什么是数据库？

  数据库（Database）是按照数据结构来组织、存储和管理数据的仓库，

  每个数据库都有一个或多个不同的API用于创建，访问，管理，搜索和复制所保存的数据。

  我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。

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

  RDBMS即关系数据库管理系统(Relational Database Management System)的特点：

  • 1.数据以表格的形式出现
  • 2.每行为各种记录名称
  • 3.每列为记录名称所对应的数据域
  • 4.许多的行和列组成一张表单
  • 5.若干的表单组成database

  ---

  RDBMS 术语

  在我们开始学习MySQL 数据库前，让我们先了解下RDBMS的一些术语：

  • 数据库: 数据库是一些关联表的集合。.
  • 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
  • 列: 一列(数据元素) 包含了相同的数据, 例如邮政编码的数据。
  • 行：一行（元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
  • 冗余：存储两倍数据，冗余可以使系统速度更快。
  • 主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
  • 外键：外键用于关联两个表。
  • 复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
  • 索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
  • 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

  ---

  MySQL数据库

  MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle公司。MySQL是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

  • MySQL是开源的，所以你不需要支付额外的费用。
  • MySQL支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
  • MySQL使用标准的SQL数据语言形式。
  • MySQL可以允许于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。
  • MySQL对PHP有很好的支持，PHP是目前最流行的Web开发语言。
  • MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB。
  • MySQL是可以定制的，采用了GPL协议，你可以修改源码来开发自己的MySQL系统。

  ---

  在开始学习本教程前你应该了解？

  在开始学习本教程前你应该了解PHP和HTML的基础知识，并能简单的应用。

  本教程的很多例子都跟PHP语言有关，我们的实例基本上是采用PHP语言来演示。

  如果你还不了解PHP，你可以通过本站的PHP教程来了解该语言。

  
  

  所有平台的 MySQL 下载地址为： MySQL 下载. 挑选你需要的 MySQL Community Server 版本及对应的平台。
  

  注意：安装过程我们需要通过开启管理员权限来安装，否则会由于权限不足导致无法安装。

  ---

  Linux / UNIX 上安装 MySQL

  Linux 平台上推荐使用 RPM 包来安装 MySQL ，MySQL AB 提供了以下 RPM 包的下载地址：

  • MySQL - MySQL 服务器。如果不是只想连接运行在另一台机器上的 MySQL 服务器，请你选择该选项。
  • MySQL-client - MySQL 客户端程序，用于连接并操作 Mysql 服务器。
  • MySQL-devel - 库和包含文件，如果你想要编译其它如 Perl 模块等 MySQL 客户端，则需要安装该 RPM 包。
  • MySQL-shared - 该软件包包含某些语言和应用程序需要动态装载的共享库(libmysqlclient.so*)，使用 MySQL。
  • MySQL-bench - MySQL 数据库服务器的基准和性能测试工具。

  以下安装 MySQL RMP 的实例是在 SuSE Linux 系统上进行，当然该安装步骤也适合应用于其他支持 RPM 的 Linux 系统，如: CentOS。

  
  

  安装前，我们可以检测系统是否自带安装 MySQL：

  rpm -qa | grep mysql

  如果你系统有安装，那可以选择进行卸载:

  rpm -e mysql　　// 普通删除模式rpm -e --nodeps mysql　　// 强力删除模式，如果使用上面命令删除时，提示有依赖的其它文件，则用该命令可以对其进行强力删除

  MySQL 安装步骤如下：

  使用 root 用户登陆你的 Linux 系统。

  下载 MySQL RPM 包，下载地址为：MySQL 下载。

  通过以下命令执行 MySQ L安装，rpm 包为你下载的 rpm 包：

  [root@host]# rpm -i MySQL-5.0.9-0.i386.rpm

  以上安装MySQL服务器的过程会创建MySQL用户，并创建一个MySQL配置文件my.cnf。

  你可以在/usr/bin和/usr/sbin中找到所有与MySQL相关的二进制文件。所有数据表和数据库将在/var/lib/mysql目录中创建。

  以下是一些MySQL可选包的安装过程，你可以根据自己的需要来安装：

  [root@host]# rpm -i MySQL-client-5.0.9-0.i386.rpm[root@host]# rpm -i MySQL-devel-5.0.9-0.i386.rpm[root@host]# rpm -i MySQL-shared-5.0.9-0.i386.rpm[root@host]# rpm -i MySQL-bench-5.0.9-0.i386.rpm

  ---

  Window上安装MySQL

  Window上安装MySQL相对来说会较为简单，你只需要在 MySQL 下载中下载window版本的MySQL安装包，并解压安装包。

  双击 setup.exe 文件，接下来你只需要安装默认的配置点击"next"即可，默认情况下安装信息会在C:mysql目录中。

  接下来你可以通过"开始" =》在搜索框中输入 " cmd" 命令 =》 在命令提示符上切换到 C:mysqlin 目录，并输入一下命令：

  mysqld.exe --console

  如果安装成功以上命令将输出一些MySQL启动及InnoDB信息。

  ---

  验证MySQL安装

  在成功安装MySQL后，一些基础表会表初始化，在服务器启动后，你可以通过简单的测试来验证MySQL是否工作正常。

  使用 mysqladmin 工具来获取服务器状态：

  使用 mysqladmin 命令俩检查服务器的版本,在linux上该二进制文件位于 /usr/bin on linux ，在window上该二进制文件位于C:mysqlin 。

  [root@host]# mysqladmin --version

  linux上该命令将输出以下结果，该结果基于你的系统信息：

  mysqladmin  Ver 8.23 Distrib 5.0.9-0, for redhat-linux-gnu on i386

  如果以上命令执行后未输入任何信息，说明你的MySQL未安装成功。

  ---

  使用 MySQL Client(MySQL客户端) 执行简单的SQL命令

  你可以在 MySQL Client(MySQL客户端) 使用 MySQL 命令连接到MySQL服务器上，默认情况下MySQL服务器的密码为空，所以本实例不需要输入密码。

  命令如下：

  [root@host]# mysql

  以上命令执行后会输出 mysql > 提示符，这说明你已经成功连接到 MySQL 服务器上，你可以在 mysql > 提示符执行 SQL 命令：

  mysql> SHOW DATABASES;+----------+| Database |+----------+| mysql    || test     |+----------+2 rows in set (0.13 sec)

  ---

  MySQL安装后需要做的

  MySQL 安装成功后，默认的 root 用户密码为空，你可以使用以下命令来创建root用户的密码：

  [root@host]# mysqladmin -u root password "new_password";

  现在你可以通过以下命令来连接到MySQL服务器：

  [root@host]# mysql -u root -pEnter password:*******

  注意：在输入密码时，密码是不会显示了，你正确输入即可。

  ---

  Linux系统启动时启动 MySQL

  如果你需要在Linux系统启动时启动 MySQL 服务器，你需要在 /etc/rc.local 文件中添加以下命令：

  /etc/init.d/mysqld start

  同样，你需要将 mysqld 二进制文件添加到 /etc/init.d/ 目录中。

  
  

  启动及关闭 MySQL 服务器

  首先，我们需要通过以下命令来检查MySQL服务器是否启动：

  ps -ef | grep mysqld

  如果MySQL已经启动，以上命令将输出MySQL进程列表， 如果MySQL未启动，你可以使用以下命令来启动MySQL服务器:

  root@host# cd /usr/bin./safe_mysqld &

  如果你想关闭目前运行的 MySQL 服务器, 你可以执行以下命令:

  root@host# cd /usr/bin./mysqladmin -u root -p shutdownEnter password: ******

  ---

  MySQL 用户设置

  如果你需要添加 MySQL 用户，你只需要在 MySQL 数据库中的 user 表添加新用户即可。

  以下为添加用户的的实例，用户名为guest，密码为guest123，并授权用户可进行 SELECT, INSERT 和 UPDATE操作权限：

  root@host# mysql -u root -pEnter password:*******mysql> use mysql;Database changedmysql> INSERT INTO user           (host, user, password,            select_priv, insert_priv, update_priv)            VALUES ('localhost', 'guest',            PASSWORD('guest123'), 'Y', 'Y', 'Y');Query OK, 1 row affected (0.20 sec)mysql> FLUSH PRIVILEGES;Query OK, 1 row affected (0.01 sec)mysql> SELECT host, user, password FROM user WHERE user = 'guest';+-----------+---------+------------------+| host      | user    | password         |+-----------+---------+------------------+| localhost | guest | 6f8c114b58f2ce9e |+-----------+---------+------------------+1 row in set (0.00 sec)

  在添加用户时，请注意使用MySQL提供的 PASSWORD() 函数来对密码进行加密。 你可以在以上实例看到用户密码加密后为： 6f8c114b58f2ce9e.

  注意：在 MySQL5.7 中 user 表的 password 已换成了authentication_string。
  

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

  如果你不使用该命令，你就无法使用新创建的用户来连接MySQL服务器，除非你重启MySQL服务器。

  你可以在创建用户时，为用户指定权限，在对应的权限列中，在插入语句中设置为 'Y' 即可，用户权限列表如下：

  • Select_priv
  • Insert_priv
  • Update_priv
  • Delete_priv
  • Create_priv
  • Drop_priv
  • Reload_priv
  • Shutdown_priv
  • Process_priv
  • File_priv
  • Grant_priv
  • References_priv
  • Index_priv
  • Alter_priv

  另外一种添加用户的方法为通过SQL的 GRANT 命令，你下命令会给指定数据库TUTORIALS添加用户 zara ，密码为 zara123 。

  root@host# mysql -u root -p password;Enter password:*******mysql> use mysql;Database changedmysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP    -> ON TUTORIALS.*    -> TO 'zara'@'localhost'    -> IDENTIFIED BY 'zara123';

  以上命令会在MySQL数据库中的user表创建一条用户信息记录。

  注意: MySQL 的SQL语句以分号 (;) 作为结束标识。

  ---

  /etc/my.cnf 文件配置

  一般情况下，你不需要修改该配置文件，该文件默认配置如下：

  [mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock[mysql.server]user=mysqlbasedir=/var/lib[safe_mysqld]err-log=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

  在配置文件中，你可以指定不同的错误日志文件存放的目录，一般你不需要改动这些配置。

  ---

  管理MySQL的命令

  以下列出了使用MySQL数据库过程中常用的命令：

  • USE 数据库名 :选择要操作的MySQL数据库，使用该命令后所有MySQL命令都只针对该数据库。

  mysql> use W3CSCHOOL;Database changed

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

  mysql> SHOW DATABASES;+--------------------+| Database           |+--------------------+| information_schema || W3CSCHOOL             || cdcol              || mysql              || onethink           || performance_schema || phpmyadmin         || test               || wecenter           || wordpress          |+--------------------+10 rows in set (0.02 sec)

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

  mysql> use W3CSCHOOL;Database changedmysql> SHOW TABLES;+------------------+| Tables_in_W3Cschool |+------------------+| employee_tbl     || W3Cschool_tbl       || tcount_tbl       |+------------------+3 rows in set (0.00 sec)

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

  mysql> SHOW COLUMNS FROM W3Cschool_tbl;+-----------------+--------------+------+-----+---------+-------+| Field           | Type         | Null | Key | Default | Extra |+-----------------+--------------+------+-----+---------+-------+| W3Cschool_id       | int(11)      | NO   | PRI | NULL    |       || W3Cschool_title    | varchar(255) | YES  |     | NULL    |       || W3Cschool_author   | varchar(255) | YES  |     | NULL    |       || submission_date | date         | YES  |     | NULL    |       |+-----------------+--------------+------+-----+---------+-------+4 rows in set (0.01 sec)

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

  mysql> SHOW INDEX FROM W3Cschool_tbl;+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| W3Cschool_tbl |          0 | PRIMARY  |            1 | W3Cschool_id   | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+1 row in set (0.00 sec)

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

  mysql> SHOW TABLE STATUS  FROM W3CSCHOOL;   # 显示数据库 W3CSCHOOL 中所有表的信息mysql> SHOW TABLE STATUS from W3CSCHOOL LIKE 'W3Cschool%';     # 表名以W3Cschool开头的表的信息mysql> SHOW TABLE STATUS from W3CSCHOOL LIKE 'W3Cschool%'G;   # 加上 G，查询结果按列打印

  
  

  MySQL 可应用于多种语言，包括 PERL, C, C++, JAVA 和 PHP。 在这些语言中，MySQL在PHP的web开发中是应用最广泛。
  

  在本教程中我们大部分实例都采用了PHP语言。如果你想了解MySQL在PHP中的应用，可以访问我们的 PHP中使用MySQL介绍。

  PHP提供了多种方式来访问和操作MySQL数据库记录。PHP Mysqli函数格式如下：

  mysqli_function(value,value,...);

  以上格式中 function 部分描述了 MySQL 函数的功能，如

  mysqli_connect($connect);
  mysqli_query($connect,"SQL 语句");
  mysqli_fetch_array()
  mysqli_close()

  以下实例展示了PHP调用MySQL函数的语法：

  <?php
  $retval = mysqli_function(value, [value,...]);
  if( !$retval ){
     die ( "相关错误信息" );
  }
  // 其他 MySQL 或 PHP 语句
  ?>

  从下一章开始，我们将学习到更多的MySQL功能函数。

  
  

  使用MySQL二进制方式连接
  

  您可以使用MySQL二进制方式进入到MySQL命令提示符下来连接MySQL数据库。

  实例

  以下是从命令行中连接MySQL服务器的简单实例：

  [root@host]# mysql -u root -pEnter password:******

  在登录成功后会出现 mysql> 命令提示窗口，你可以在上面执行任何 SQL 语句。

  以上命令执行后，登录成功输出结果如下:

  Welcome to the MySQL monitor.  Commands end with ; or g.Your MySQL connection id is 2854760 to server version: 5.0.9Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

  在以上实例中，我们使用了root用户登录到MySQL服务器，当然你也可以使用其他MySQL用户登录。

  如果用户权限足够，任何用户都可以在MySQL的命令提示窗口中进行SQL操作。

  退出 mysql> 命令提示窗口可以使用 exit 命令，如下所示：

  mysql> exitBye

  使用 PHP 脚本连接 MySQL

  PHP 提供了 mysql_connect() 函数来连接数据库。

  该函数有5个参数，在成功链接到MySQL后返回连接标识，失败返回 FALSE 。

  语法

  mysqli_connect(host,username,password,dbname,port,socket);

  参数说明：

  参数	描述
  host	可选。规定主机名或 IP 地址。
  username	可选。规定 MySQL 用户名。
  password	可选。规定 MySQL 密码。
  dbname	可选。规定默认使用的数据库。
  port	可选。规定尝试连接到 MySQL 服务器的端口号。
  socket	可选。规定 socket 或要使用的已命名 pipe。

  你可以使用 PHP 的 mysql_close() 函数来断开与 MySQL 数据库的链接。

  该函数只有一个参数为 mysql_connect() 函数创建连接成功后返回的 MySQL 连接标识符。

  语法

  bool mysqli_close ( mysqli $link )

  本函数关闭指定的连接标识所关联的到 MySQL 服务器的非持久连接。如本函数关闭指定的连接标识所关联的到 MySQL 服务器的非持久连接。如果没有指定 link_identifier，则关闭上一个打开的连接。

  提示：通常不需要使用 mysqli_close()，因为已打开的非持久连接会在脚本执行完毕后自动关闭。

  实例

  你可以尝试以下实例来连接到你的 MySQL 服务器:

  <?php
  $dbhost = 'localhost';  // mysql服务器主机地址
  $dbuser = 'root';            // mysql用户名
  $dbpass = '123456';          // mysql用户名密码
  $conn = mysqli_connect($dbhost, $dbuser, $dbpass);
  if(! $conn ){
      die('Could not connect: ' . mysqli_error());
  }
  echo '数据库连接成功！';
  mysqli_close($conn);
  ?>

  
  

  使用 mysqladmin 创建数据库

  使用普通用户，你可能需要特定的权限来创建或者删除 MySQL 数据库。

  所以我们这边使用root用户登录，root用户拥有最高权限，可以使用 mysql mysqladmin 命令来创建数据库。

  实例

  以下命令简单的演示了创建数据库的过程，数据名为 W3CSCHOOL:

  [root@host]# mysqladmin -u root -p create W3CSCHOOLEnter password:******

  以上命令执行成功后会创建 MySQL 数据库 W3CSCHOOL。

  ---

  使用 PHP脚本 创建数据库

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

  该函数有两个参数，在执行成功时返回 TRUE，否则返回 FALSE。

  语法

  bool mysql_query( sql, connection );

  参数	描述
  sql	必需。规定要发送的 SQL 查询。注释：查询字符串不应以分号结束。
  connection	可选。规定 SQL 连接标识符。如果未规定，则使用上一个打开的连接。

  实例

  以下实例演示了使用PHP来创建一个数据库：

  <html><head><meta charset="utf-8"> <title>创建 MySQL 数据库</title></head><body><?php$dbhost = 'localhost:3036';$dbuser = 'root';$dbpass = 'rootpassword';$conn = mysql_connect($dbhost, $dbuser, $dbpass);if(! $conn ){  die('连接错误: ' . mysql_error());}echo '连接成功<br />';$sql = 'CREATE DATABASE W3CSCHOOL';$retval = mysql_query( $sql, $conn );if(! $retval ){  die('创建数据库失败: ' . mysql_error());}echo "数据库 W3CSCHOOL 创建成功
  ";mysql_close($conn);?></body></html>

  使用 mysqladmin 删除数据库
  

  使用普通用户登陆MySQL服务器，你可能需要特定的权限来创建或者删除 MySQL 数据库。

  所以我们这边使用root用户登录，root用户拥有最高权限，可以使用 MySQL mysqladmin 命令来删除数据库。

  在删除数据库过程中，务必要十分谨慎，因为在执行删除命令后，所有数据将会消失。

  以下实例删除数据库TUTORIALS(该数据库在前一章节已创建)：

  [root@host]# mysqladmin -u root -p drop W3CSCHOOLEnter password:******

  执行以上删除数据库命令后，会出现一个提示框，来确认是否真的删除数据库：

  Dropping the database is potentially a very bad thing to do.Any data stored in the database will be destroyed.Do you really want to drop the 'W3CSCHOOL' database [y/N] yDatabase "W3CSCHOOL" dropped

  ---

  使用PHP脚本删除数据库

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

  该函数有两个参数，在执行成功时返回 TRUE，否则返回 FALSE。

  语法

  bool mysql_query( sql, connection );

  参数	描述
  sql	必需。规定要发送的 SQL 查询。注释：查询字符串不应以分号结束。
  connection	可选。规定 SQL 连接标识符。如果未规定，则使用上一个打开的连接。

  实例

  以下实例演示了使用PHP mysql_query函数来删除数据库：

  <html><head><meta charset="utf-8"><title>删除 MySQL 数据库</title></head><body><?php$dbhost = 'localhost:3036';$dbuser = 'root';$dbpass = 'rootpassword';$conn = mysql_connect($dbhost, $dbuser, $dbpass);if(! $conn ){   die('连接失败: ' . mysql_error()); }echo '连接成功<br />';$sql = 'DROP DATABASE W3CSCHOOL';$retval = mysql_query( $sql, $conn );if(! $retval ){  die('删除数据库失败: ' . mysql_error());}echo "数据库 W3CSCHOOL 删除成功
  ";mysql_close($conn);?></body></html>

  注意： 在使用PHP脚本删除数据库时，不会出现确认是否删除信息，会直接删除指定数据库，所以你在删除数据库时要特别小心。

  在你连接到 MySQL 数据库后，可能有多个可以操作的数据库，所以你需要选择你要操作的数据库。
  

  ---

  从命令提示窗口中选择MySQL数据库

  在 mysql> 提示窗口中可以很简单的选择特定的数据库。你可以使用SQL命令来选择指定的数据库。

  实例

  以下实例选取了数据库 W3CSCHOOL:

  [root@host]# mysql -u root -pEnter password:******mysql> use W3CSCHOOL;Database changedmysql>

  执行以上命令后，你就已经成功选择了 W3CSCHOOL 数据库，在后续的操作中都会在 W3CSCHOOL 数据库中执行。

  注意:所有的数据库名，表名，表字段都是区分大小写的。所以你在使用SQL命令时需要输入正确的名称。

  ---

  使用PHP脚本选择MySQL数据库

  PHP 提供了函数 mysql_select_db 来选取一个数据库。函数在执行成功后返回 TRUE ，否则返回 FALSE 。

  语法

  bool mysql_select_db( db_name, connection );

  参数	描述
  db_name	必需。规定要选择的数据库。
  connection	可选。规定 MySQL 连接。如果未指定，则使用上一个连接。

  实例

  以下实例展示了如何使用 mysql_select_db 函数来选取一个数据库：

  <html><head><meta charset="utf-8"> <title>选择 MySQL 数据库</title></head><body><?php$dbhost = 'localhost:3036';$dbuser = 'guest';$dbpass = 'guest123';$conn = mysql_connect($dbhost, $dbuser, $dbpass);if(! $conn ){  die('连接失败: ' . mysql_error());}echo '连接成功';mysql_select_db($conn, 'W3CSCHOOL' );mysql_close($conn);?></body></html>

  
  

  MySQL 中定义数据字段的类型对数据库的优化是非常重要的。
  

  MySQL 支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

  ---

  数值类型

  MySQL 支持所有标准 SQL 数值数据类型。

  这些类型包括严格数值数据类型( INTEGER、SMALLINT、DECIMAL 和 NUMERIC )，以及近似数值数据类型( FLOAT、REAL 和 DOUBLE PRECISION )。

  关键字 INT 是 INTEGER 的同义词，关键字 DEC 是 DECIMAL 的同义词。

  BIT数据类型保存位字段值，并且支持 MyISAM、MEMORY、InnoDB 和 BDB 表。

  作为 SQL 标准的扩展，MySQL 也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

  类型	大小	范围（有符号）	范围（无符号）	用途
  TINYINT	1 字节	(-128，127)	(0，255)	小整数值
  SMALLINT	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
  MEDIUMINT	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
  INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
  BIGINT	8 字节	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
  FLOAT	4 字节	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度 浮点数值
  DOUBLE	8 字节	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度 浮点数值
  DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

  ---

  日期和时间类型

  表示时间值的日期和时间类型为 DATETIME、DATE、TIMESTAMP、TIME 和 YEAR。

  每个时间类型有一个有效值范围和一个"零"值，当指定不合法的 MySQL 不能表示的值时使用"零"值。

  TIMESTAMP 类型有专有的自动更新特性，将在后面描述。

  类型	大小 (字节)	范围	格式	用途
  DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
  TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	时间值或持续时间
  YEAR	1	1901/2155	YYYY	年份值
  DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
  TIMESTAMP	4	1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038-1-19 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

  ---

  字符串类型

  字符串类型指 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

  类型	大小	用途
  CHAR	0-255字节	定长字符串
  VARCHAR	0-65535 字节	变长字符串
  TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
  TINYTEXT	0-255字节	短文本字符串
  BLOB	0-65 535字节	二进制形式的长文本数据
  TEXT	0-65 535字节	长文本数据
  MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
  MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
  LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
  LONGTEXT	0-4 294 967 295字节	极大文本数据

  提示：CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

  BINARY 和 VARBINARY 类类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

  BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们只是可容纳值的最大长度不同。

  有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。这些对应 4 种BLOB 类型，有相同的最大长度和存储需求。

  
  

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

  • 表名
  • 表字段名
  • 定义每个表字段

  语法

  以下为创建MySQL数据表的SQL通用语法：

  CREATE TABLE table_name (column_name column_type);

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

  CREATE TABLE IF NOT EXISTS 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 ( tutorial_id ));

  实例解析：

  • 如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。
  • AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
  • PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。

  ---

  通过命令提示符创建表

  通过 mysql> 命令窗口可以很简单的创建MySQL数据表。你可以使用 SQL 语句 CREATE TABLE 来创建数据表。

  实例

  以下为创建数据表 51coolma_tbl 实例:

  root@host# mysql -u root -pEnter password:*mysql> use W3CSCHOOL;Database changedmysql> CREATE TABLE 51coolma_tbl(   -> 51coolma_id INT NOT NULL AUTO_INCREMENT,   -> 51coolma_title VARCHAR(100) NOT NULL,   -> 51coolma_author VARCHAR(40) NOT NULL,   -> submission_date DATE,   -> PRIMARY KEY ( 51coolma_id )   -> );Query OK, 0 rows affected (0.16 sec)mysql>

  注意：MySQL命令终止符为分号 (;)  ；-> 是换行符标识，不要复制。

  ---

  使用PHP脚本创建数据表

  你可以使用PHP的 mysqli_query() 函数来创建已存在数据库的数据表。

  该函数有两个参数，在执行成功时返回 TRUE，否则返回 FALSE。

  语法

  mysqli_query( sql, connection );

  参数	描述
  connection	必需。规定要使用的 MySQL 连接。
  query	必需，规定查询字符串。
  resultmode	可选。一个常量。可以是下列值中的任意一个：MYSQLI_USE_RESULT（如果需要检索大量数据，请使用这个）MYSQLI_STORE_RESULT（默认）

  实例

  以下实例使用了PHP脚本来创建数据表：

  <?php$dbhost = 'localhost:3036';$dbuser = 'root';$dbpass = 'rootpassword';$conn = mysql_connect($dbhost, $dbuser, $dbpass);if(! $conn ){  die('Could not connect: ' . mysql_error());}echo 'Connected successfully';$sql = "CREATE TABLE 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 ( tutorial_id )); ";mysql_select_db( 'TUTORIALS' );$retval = mysql_query( $sql, $conn );if(! $retval ){  die('数据表创建失败: ' . mysql_error());}echo "数据表创建成功
  ";mysql_close($conn);?>

  执行成功后，就可以通过命令行查看表结构：
  

  
  

  
  

  MySQL中删除数据表是非常容易操作的， 但是你再进行删除表操作时要非常小心，因为执行删除命令后所有数据都会消失。
  

  语法

  以下为删除MySQL数据表的通用语法：

  DROP TABLE table_name ;

  ---

  在命令提示窗口中删除数据表

  在mysql>命令提示窗口中删除数据表SQL语句为 DROP TABLE ：

  实例

  以下实例删除了数据表51coolma_tbl:

  root@host# mysql -u root -pEnter password:*******mysql> use W3CSCHOOL;Database changedmysql> DROP TABLE 51coolma_tblQuery OK, 0 rows affected (0.8 sec)mysql>

  ---

  使用PHP脚本删除数据表

  PHP使用 mysql_query 函数来删除 MySQL 数据表。

  该函数有两个参数，在执行成功时返回 TRUE，否则返回 FALSE。

   语法

  mysql_query( sql, connection ,resultmode);

  参数	描述
  sql	必需。规定要发送的 SQL 查询。注释：查询字符串不应以分号结束。
  connection	可选。规定 SQL 连接标识符。如果未规定，则使用上一个打开的连接。
  resultmode	可选。一个常量。可以是下列值中的任意一个： MYSQLI_USE_RESULT（如果需要检索大量数据，请使用这个） MYSQLI_STORE_RESULT（默认）

  实例

  以下实例使用了PHP脚本删除数据表51coolma_tbl:

  <html><head><meta charset="utf-8"><title>创建 MySQL 数据表</title></head><body><?php$dbhost = 'localhost:3036';$dbuser = 'root';$dbpass = 'rootpassword';$conn = mysql_connect($dbhost, $dbuser, $dbpass);if(! $conn ){  die('连接失败: ' . mysql_error());}echo '连接成功<br />';$sql = "DROP TABLE 51coolma_tbl";mysql_select_db( 'W3CSCHOOL' );$retval = mysql_query( $sql, $conn );if(! $retval ){  die('数据表删除失败: ' . mysql_error());}echo "数据表删除成功
  ";mysql_close($conn);?></body></html>

  
  

  MySQL 表中使用 INSERT INTO SQL语句来插入数据。
  

  你可以通过 mysql> 命令提示窗口中向数据表中插入数据，或者通过PHP脚本来插入数据。

  语法

  以下为向MySQL数据表插入数据通用的 INSERT INTO SQL语法：

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

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

  ---

  通过命令提示窗口插入数据

  以下我们将使用 SQL INSERT INTO 语句向 MySQL 数据表 51coolma_tbl 插入数据

  实例

  以下实例中我们将向 51coolma_tbl 表插入三条数据:

  root@host# mysql -u root -p password;Enter password:*mysql> use W3CSCHOOL;Database changedmysql> INSERT INTO 51coolma_tbl      ->(51coolma_title, 51coolma_author, submission_date)     ->VALUES     ->("Learn PHP", "John Poul", NOW());Query OK, 1 row affected (0.01 sec)mysql> INSERT INTO 51coolma_tbl     ->(51coolma_title, 51coolma_author, submission_date)     ->VALUES     ->("Learn MySQL", "Abdul S", NOW());Query OK, 1 row affected (0.01 sec)mysql> INSERT INTO 51coolma_tbl     ->(51coolma_title, 51coolma_author, submission_date)     ->VALUES     ->("JAVA Tutorial", "Sanjay", '2007-05-06');Query OK, 1 row affected (0.01 sec)mysql>

  注意： 使用箭头标记(->)不是SQL语句的一部分，它仅仅表示一个新行，如果一条SQL语句太长，我们可以通过回车键来创建一个新行来编写SQL语句，SQL语句的命令结束符为分号（;）。

  在以上实例中，我们并没有提供 51coolma_id 的数据，因为该字段我们在创建表的时候已经设置它为 AUTO_INCREMENT(自动增加) 属性。 所以，该字段会自动递增而不需要我们去设置。实例中 NOW() 是一个 MySQL 函数，该函数返回日期和时间。

  ---

  使用PHP脚本插入数据

  你可以使用PHP 的 mysql_query() 函数来执行 SQL INSERT INTO命令来插入数据。

  该函数有两个参数，在执行成功时返回 TRUE，否则返回 FALSE。

  语法

  bool mysql_query( sql, connection );

  参数	描述
  sql	必需。规定要发送的 SQL 查询。注释：查询字符串不应以分号结束。
  connection	可选。规定 SQL 连接标识符。如果未规定，则使用上一个打开的连接。

  实例

  以下实例中程序接收用户输入的三个字段数据，并插入数据表中：

  <?phpif(isset($_POST['add'])){$dbhost = 'localhost:3036';$dbuser = 'root';$dbpass = 'rootpassword';$conn = mysql_connect($dbhost, $dbuser, $dbpass);if(! $conn ){  die('Could not connect: ' . mysql_error());}
  if(! get_magic_quotes_gpc() ){   $51coolma_title = addslashes ($_POST['51coolma_title']);   $51coolma_author = addslashes ($_POST['51coolma_author']);}else{   $51coolma_title = $_POST['51coolma_title'];   $51coolma_author = $_POST['51coolma_author'];}$submission_date = $_POST['submission_date'];
  $sql = "INSERT INTO 51coolma_tbl ".       "(51coolma_title,51coolma_author, submission_date) ".       "VALUES ".       "('$51coolma_title','$51coolma_author','$submission_date')";mysql_select_db('W3CSCHOOL');$retval = mysql_query( $sql, $conn );if(! $retval ){  die('Could not enter data: ' . mysql_error());}echo "Entered data successfully
  ";mysql_close($conn);}else{Tutorial TitleTutorial AuthorSubmission Date [ yyyy-mm-dd ]Add Tutorial}?>

  在我们接收用户提交的数据时，为了数据的安全性我们需要使用 get_magic_quotes_gpc() 函数来判断特殊字符的转义是否已经开启。如果这个选项为off（未开启），返回0，那么我们就必须调用addslashes 这个函数来为字符串增加转义。

  你也可以添加其他检查数据的方法，比如邮箱格式验证，电话号码验证，是否为整数验证等。

  
  

  MySQL 数据库使用SQL SELECT语句来查询数据。
  

  你可以通过 mysql> 命令提示窗口中在数据库中查询数据，或者通过PHP脚本来查询数据。

  语法

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

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

  • 查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
  • SELECT 命令可以读取一条或者多条记录。
  • 你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
  • 你可以使用 WHERE 语句来包含任何条件。
  • 你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。
  • 你可以使用 LIMIT 属性来设定返回的记录数。

  ---

  通过命令提示符获取数据

  以下实例我们将通过 SQL SELECT 命令来获取 MySQL 数据表 51coolma_tbl 的数据：

  实例

  以下实例将返回数据表51coolma_tbl的所有记录:

  root@host# mysql -u root -p password;Enter password:*******mysql> use W3CSCHOOL;Database changedmysql> SELECT * from 51coolma_tbl; +-------------+----------------+-----------------+-----------------+| 51coolma_id | 51coolma_title | 51coolma_author | submission_date |+-------------+----------------+-----------------+-----------------+|           1 | Learn PHP      | John Poul       | 2007-05-21      ||           2 | Learn MySQL    | Abdul S         | 2007-05-21      ||           3 | JAVA Tutorial  | Sanjay          | 2007-05-21      |+-------------+----------------+-----------------+-----------------+3 rows in set (0.01 sec)mysql>

  ---

  使用PHP脚本来获取数据

  使用PHP函数的mysql_query()及SQL SELECT命令来获取数据。

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

  mysql_fetch_array() 函数从结果集中取得一行作为关联数组，或数字数组，或二者兼有 返回根据从结果集取得的行生成的数组，如果没有更多行则返回 false。

  以下实例为从数据表 51coolma_tbl 中读取所有记录。

  实例

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

  <?php$dbhost = 'localhost:3036';$dbuser = 'root';$dbpass = 'rootpassword';$conn = mysql_connect($dbhost, $dbuser, $dbpass);if(! $conn ){  die('Could not connect: ' . mysql_error());}$sql = 'SELECT 51coolma_id, 51coolma_title,               51coolma_author, submission_date        FROM 51coolma_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)){    echo "Tutorial ID :{$row['51coolma_id']}  <br> ".         "Title: {$row['51coolma_title']} <br> ".         "Author: {$row['51coolma_author']} <br> ".         "Submission Date : {$row['submission_date']} <br> ".         "--------------------------------<br>";} echo "Fetched data successfully
  ";mysql_close($conn);?>

  以上实例中，读取的每行记录赋值给变量$row，然后再打印出每个值。

  注意：记住如果你需要在字符串中使用变量，请将变量置于花括号。

  在上面的例子中，PHP mysql_fetch_array()函数第二个参数为MYSQL_ASSOC， 设置该参数查询结果返回关联数组，你可以使用字段名称来作为数组的索引。

  PHP提供了另外一个函数mysql_fetch_assoc(), 该函数从结果集中取得一行作为关联数组。 返回根据从结果集取得的行生成的关联数组，如果没有更多行，则返回 false。

  实例

  尝试以下实例，该实例使用了mysql_fetch_assoc()函数来输出数据表51coolma_tbl的所有记录：

  <?php$dbhost = 'localhost:3036';$dbuser = 'root';$dbpass = 'rootpassword';$conn = mysql_connect($dbhost, $dbuser, $dbpass);if(! $conn ){  die('Could not connect: ' . mysql_error());}$sql = 'SELECT 51coolma_id, 51coolma_title,               51coolma_author, submission_date        FROM 51coolma_tbl';mysql_select_db('W3CSCHOOL');$retval = mysql_query( $sql, $conn );if(! $retval ){  die('Could not get data: ' . mysql_error());}while($row = mysql_fetch_assoc($retval)){    echo "Tutorial ID :{$row['51coolma_id']}  <br> ".         "Title: {$row['51coolma_title']} <br> ".         "Author: {$row['51coolma_author']} <br> ".         "Submission Date : {$row['submission_date']} <br> ".         "--------------------------------<br>";} echo "Fetched data successfully
  ";mysql_close($conn);?>

  你也可以使用常量 MYSQL_NUM 作为PHP mysql_fetch_array()函数的第二个参数，返回数字数组。

  实例

  以下实例使用MYSQL_NUM参数显示数据表51coolma_tbl的所有记录:

  <?php$dbhost = 'localhost:3036';$dbuser = 'root';$dbpass = 'rootpassword';$conn = mysql_connect($dbhost, $dbuser, $dbpass);if(! $conn ){  die('Could not connect: ' . mysql_error());}$sql = 'SELECT 51coolma_id, 51coolma_title,               51coolma_author, submission_date        FROM 51coolma_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_NUM)){    echo "Tutorial ID :{$row[0]}  <br> ".         "Title: {$row[1]} <br> ".         "Author: {$row[2]} <br> ".         "Submission Date : {$row[3]} <br> ".         "--------------------------------<br>";}echo "Fetched data successfully
  ";mysql_close($conn);?>

  以上三个实例输出结果都一样。

  ---

  内存释放

  在我们执行完SELECT语句后，释放游标内存是一个很好的习惯。 可以通过PHP函数mysql_free_result()来实现内存的释放。

  以下实例演示了该函数的使用方法。

  实例

  尝试以下实例:

  <?php$dbhost = 'localhost:3036';$dbuser = 'root';$dbpass = 'rootpassword';$conn = mysql_connect($dbhost, $dbuser, $dbpass);if(! $conn ){  die('Could not connect: ' . mysql_error());}$sql = 'SELECT 51coolma_id, 51coolma_title,               51coolma_author, submission_date        FROM 51coolma_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_NUM)){    echo "Tutorial ID :{$row[0]}  <br> ".         "Title: {$row[1]} <br> ".         "Author: {$row[2]} <br> ".         "Submission Date : {$row[3]} <br> ".         "--------------------------------<br>";}mysql_free_result($retval);echo "Fetched data successfully
  ";mysql_close($conn);?>

  
  

  我们知道从MySQL表中使用SQL SELECT 语句来读取数据。

  如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句中。

  语法

  以下是SQL SELECT 语句使用 WHERE 子句从数据表中读取数据的通用语法：

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

  • 查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
  • 你可以在WHERE子句中指定任何条件。
  • 你可以使用AND或者OR指定一个或多个条件。
  • WHERE子句也可以运用于SQL的 DELETE 或者 UPDATE 命令。
  • WHERE 子句类似于程序语言中的if条件，根据 MySQL 表中的字段值来读取指定的数据。

  以下为操作符列表，可用于 WHERE 子句中。

  下表中实例假定 A为10 B为20

  操作符	描述	实例
  =	等号，检测两个值是否相等，如果相等返回true	(A = B) 返回false。
  <> 或 !=	不等于，检测两个值是否相等，如果不相等返回true	(A != B) 返回 true。
  >	大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true	(A > B) 返回false。
  <	小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true	(A < B) 返回 true。
  >=	大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true	(A >= B) 返回false。
  <=	小于等于号，检测左边的值是否小于于或等于右边的值, 如果左边的值小于或等于右边的值返回true	(A <= B) 返回 true。

  如果我们想再MySQL数据表中读取指定的数据，WHERE 子句是非常有用的。

  使用主键来作为 WHERE 子句的条件查询是非常快速的。

  如果给定的条件在表中没有任何匹配的记录，那么查询不会返回任何数据。

  ---

  从命令提示符中读取数据

  我们将在SQL SELECT语句使用WHERE子句来读取MySQL数据表 51coolma_tbl 中的数据：

  实例

  以下实例将读取 51coolma_tbl 表中 51coolma_author 字段值为 Sanjay 的所有记录：

  root@host# mysql -u root -p password;Enter password:mysql> use W3CSCHOOL;Database changedmysql> SELECT * from 51coolma_tbl WHERE 51coolma_author='Sanjay';+-------------+----------------+-----------------+-----------------+| 51coolma_id | 51coolma_title | 51coolma_author | submission_date |+-------------+----------------+-----------------+-----------------+|           3 | JAVA Tutorial  | Sanjay          | 2007-05-21      |+-------------+----------------+-----------------+-----------------+1 rows in set (0.01 sec)
  mysql>

  除非你使用 LIKE 来比较字符串，否则MySQL的WHERE子句的字符串比较是不区分大小写的。 你可以使用 BINARY 关键字来设定WHERE子句的字符串比较是区分大小写的。

  如下实例

  root@host# mysql -u root -p password;Enter password:mysql> use W3CSCHOOL;Database changedmysql> SELECT  from 51coolma_tbl           WHERE BINARY 51coolma_author='sanjay';Empty set (0.02 sec)
  mysql>

  ---

  使用PHP脚本读取数据

  你可以使用PHP函数的mysql_query()及相同的SQL SELECT 带上 WHERE 子句的命令来获取数据。

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

  实例

  以下实例将从 51coolma_tbl 表中返回使用 51coolma_author 字段值为 Sanjay 的记录：

  <?php$dbhost = 'localhost:3036';$dbuser = 'root';$dbpass = 'rootpassword';$conn = mysql_connect($dbhost, $dbuser, $dbpass);if(! $conn ){  die('Could not connect: ' . mysql_error());}$sql = 'SELECT 51coolma_id, 51coolma_title,               51coolma_author, submission_date        FROM 51coolma_tbl        WHERE 51coolma_author="Sanjay"';
  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)){    echo "Tutorial ID :{$row['51coolma_id']}
   ".         "Title: {$row['51coolma_title']}  ".         "Author: {$row['51coolma_author']}  ".         "Submission Date : {$row['submission_date']}  ".         "--------------------------------";} echo "Fetched data successfully
  ";mysql_close($conn);?>

  
  

  如果我们需要修改或更新MySQL中的数据，我们可以使用 SQL UPDATE 命令来操作。.
  

  语法

  以下是 UPDATE 命令修改 MySQL 数据表数据的通用SQL语法：

  UPDATE table_name SET field1=new-value1, field2=new-value2[WHERE Clause]

  • 你可以同时更新一个或多个字段。
  • 你可以在 WHERE 子句中指定任何条件。
  • 你可以在一个单独表中同时更新数据。

  当你需要更新数据表中指定行的数据时 WHERE 子句是非常有用的。

  ---

  通过命令提示符更新数据

  以下我们将在 SQL UPDATE 命令使用 WHERE子句来更新51coolma_tbl表中指定的数据：

  实例

  以下实例将更新数据表中 51coolma_id 为 3 的 51coolma_title 字段值：

  root@host# mysql -u root -p password;Enter password:*******mysql> use W3CSCHOOL;Database changedmysql> UPDATE 51coolma_tbl     -> SET 51coolma_title='Learning JAVA'     -> WHERE 51coolma_id=3;Query OK, 1 row affected (0.04 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql>

  ---

  使用PHP脚本更新数据

  PHP中使用函数mysql_query()来执行SQL语句，你可以在SQL UPDATE语句中使用或者不使用WHERE子句。

  该函数与在mysql>命令提示符中执行SQL语句的效果是一样的。

  实例

  以下实例将更新 51coolma_id 为3的 51coolma_title 字段的数据。

  <?php$dbhost = 'localhost:3036';$dbuser = 'root';$dbpass = 'rootpassword';$conn = mysql_connect($dbhost, $dbuser, $dbpass);if(! $conn ){  die('Could not connect: ' . mysql_error());} $sql = 'UPDATE 51coolma_tbl        SET 51coolma_title="Learning JAVA"        WHERE 51coolma_id=3';mysql_select_db('W3CSCHOOL');$retval = mysql_query( $sql, $conn );if(! $retval ){  die('Could not update data: ' . mysql_error());}echo "Updated data successfully
  ";mysql_close($conn);?>

  
  

  你可以使用 SQL 的 DELETE FROM 命令来删除 MySQL 数据表中的记录。
  

  你可以在mysql>命令提示符或PHP脚本中执行该命令。

  语法

  以下是SQL DELETE 语句从MySQL数据表中删除数据的通用语法：

  DELETE FROM table_name [WHERE Clause]

  • 如果没有指定 WHERE 子句，MySQL表中的所有记录将被删除。
  • 你可以在 WHERE 子句中指定任何条件
  • 您可以在单个表中一次性删除记录。

  当你想删除数据表中指定的记录时 WHERE 子句是非常有用的。

  ---

  从命令行中删除数据

  这里我们将在 SQL DELETE 命令中使用 WHERE 子句来删除MySQL数据表51coolma_tbl所选的数据。

  实例

  以下实例将删除 51coolma_tbl 表中 51coolma_id 为3 的记录：

  root@host# mysql -u root -p password;Enter password:*******mysql> use W3CSCHOOL;Database changedmysql> DELETE FROM 51coolma_tbl WHERE 51coolma_id=3;Query OK, 1 row affected (0.23 sec)mysql>

  ---

  使用 PHP 脚本删除数据

  PHP使用 mysql_query() 函数来执行SQL语句， 你可以在SQL DELETE命令中使用或不使用 WHERE 子句。

  该函数与 mysql>命令符执行SQL命令的效果是一样的。

  实例

  以下PHP实例将删除51coolma_tbl表中51coolma_id为3的记录:

  <?php$dbhost = 'localhost:3036';$dbuser = 'root';$dbpass = 'rootpassword';$conn = mysql_connect($dbhost, $dbuser, $dbpass);if(! $conn ){  die('Could not connect: ' . mysql_error());}$sql = 'DELETE FROM 51coolma_tbl        WHERE 51coolma_id=3';mysql_select_db('W3CSCHOOL');$retval = mysql_query( $sql, $conn );if(! $retval ){  die('Could not delete data: ' . mysql_error());}echo "Deleted data successfully
  ";mysql_close($conn);?>

  
  

  我们知道在MySQL中使用 SQL SELECT 命令来读取数据， 同时我们可以在 SELECT 语句中使用 WHERE 子句来获取指定的记录。
  

  WHERE 子句中可以使用等号 (=) 来设定获取数据的条件，如 "51coolma_author = 'Sanjay'"。

  但是有时候我们需要获取 51coolma_author 字段含有 "jay" 字符的所有记录，这时我们就需要在 WHERE 子句中使用 SQL LIKE 子句。

  SQL LIKE 子句中使用百分号(%)字符来表示任意字符，类似于UNIX或正则表达式中的星号 (*)。

  如果没有使用百分号(%), LIKE 子句与等号（=）的效果是一样的。

  语法

  以下是SQL SELECT 语句使用 LIKE 子句从数据表中读取数据的通用语法：

  SELECT field1, field2,...fieldN 
  FROM table_name1, table_name2...WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

  • 你可以在WHERE子句中指定任何条件。
  • 你可以在WHERE子句中使用LIKE子句。
  • 你可以使用LIKE子句代替等号(=)。
  • LIKE 通常与 % 一同使用，类似于一个元字符的搜索。
  • 你可以使用AND或者OR指定一个或多个条件。
  • 你可以在 DELETE 或 UPDATE 命令中使用 WHERE...LIKE 子句来指定条件。

  ---

  在命令提示符中使用 LIKE 子句

  以下我们将在 SQL SELECT 命令中使用 WHERE...LIKE 子句来从MySQL数据表 51coolma_tbl 中读取数据。

  实例

  以下是我们将51coolma_tbl表中获取51coolma_author字段中以"jay"为结尾的的所有记录：

  root@host# mysql -u root -p password;Enter password:*******mysql> use W3CSCHOOL;Database changedmysql> SELECT * from 51coolma_tbl     -> WHERE 51coolma_author LIKE '%jay';+-------------+----------------+-----------------+-----------------+| 51coolma_id | 51coolma_title | 51coolma_author | submission_date |+-------------+----------------+-----------------+-----------------+|           3 | JAVA Tutorial  | Sanjay          | 2007-05-21      |+-------------+----------------+-----------------+-----------------+1 rows in set (0.01 sec)mysql>

  ---

  在PHP脚本中使用 LIKE 子句

  你可以使用PHP函数的mysql_query()及相同的SQL SELECT 带上 WHERE...LIKE 子句的命令来获取数据。

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

  但是如果是DELETE或者UPDATE中使用 WHERE...LIKE 子句的SQL语句，则无需使用mysql_fetch_array() 函数。

  实例

  以下是我们使用PHP脚本在51coolma_tbl表中读取51coolma_author字段中以"jay"为结尾的的所有记录：

  <?php$dbhost = 'localhost:3036';$dbuser = 'root';$dbpass = 'rootpassword';$conn = mysql_connect($dbhost, $dbuser, $dbpass);if(! $conn ){  die('Could not connect: ' . mysql_error());}$sql = 'SELECT 51coolma_id, 51coolma_title,               51coolma_author, submission_date        FROM 51coolma_tbl        WHERE 51coolma_author LIKE "%jay%"';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)){    echo "Tutorial ID :{$row['51coolma_id']}  <br> ".         "Title: {$row['51coolma_title']} <br> ".         "Author: {$row['51coolma_author']} <br> ".         "Submission Date : {$row['submission_date']} <br> ".         "--------------------------------<br>";} echo "Fetched data successfully
  ";mysql_close($conn);?>

  
  

  我们知道从MySQL表中使用SQL SELECT 语句来读取数据。
  

  如果我们需要对读取的数据进行排序，我们就可以使用MySQL的 ORDER BY 子句来设定你想按哪个字段哪中方式来进行排序，再返回搜索结果。

  本章节使用的数据库结构及数据下载：MySQL.sql

  语法

  以下是SQL SELECT 语句使用 ORDER BY 子句将查询数据排序后再返回数据：

  SELECT field1, field2,...fieldN FROM table_name1, table_name2...ORDER BY field1, [field2...] [ASC [DESC]]

  • 你可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。
  • 你可以设定多个字段来排序。
  • 你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升排列。
  • 你可以添加 WHERE...LIKE 子句来设置条件。

  ---

  在命令提示符中使用 ORDER BY 子句

  以下将在 SQL SELECT 语句中使用 ORDER BY 子句来读取MySQL 数据表 51coolma_tbl 中的数据：

  实例

  尝试以下实例，结果将按升序排列

  root@host# mysql -u root -p password;Enter password:mysql> use W3CSCHOOL;Database changedmysql> SELECT * from 51coolma_tbl ORDER BY 51coolma_author ASC;+-------------+----------------+-----------------+-----------------+| 51coolma_id | 51coolma_title | 51coolma_author | submission_date |+-------------+----------------+-----------------+-----------------+|           2 | Learn MySQL    | Abdul S         | 2007-05-24      ||           1 | Learn PHP      | John Poul       | 2007-05-24      ||           3 | JAVA Tutorial  | Sanjay          | 2007-05-06      |+-------------+----------------+-----------------+-----------------+3 rows in set (0.00 sec)
  mysql> SELECT * from 51coolma_tbl ORDER BY 51coolma_author DESC;+-----------+------------------+-----------------+-----------------+| 51coolma_id | 51coolma_title | 51coolma_author | submission_date |+-----------+------------------+-----------------+-----------------+|         3 | JAVA Tutorial    | Sanjay          | 2007-05-06      ||         1 | Learn PHP        | John Poul       | 2007-05-24      ||         2 | Learn MySQL      | Abdul S         | 2007-05-24      |3 rows in set (0.00 sec)
  mysql>

  读取 51coolma_tbl 表中所有数据并按 51coolma_author 字段的降序排列。

  ---

  在PHP脚本中使用 ORDER BY 子句

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

  实例

  尝试以下实例，查询后的数据按 51coolma_author 字段的降序排列后返回。

  <?php$dbhost = 'localhost:3036';$dbuser = 'root';$dbpass = 'rootpassword';$conn = mysql_connect($dbhost, $dbuser, $dbpass);if(! $conn ){  die('Could not connect: ' . mysql_error());}$sql = 'SELECT 51coolma_id, 51coolma_title,               51coolma_author, submission_date        FROM 51coolma_tbl        ORDER BY  51coolma_author DESC';
  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)){    echo "Tutorial ID :{$row['51coolma_id']}
   ".         "Title: {$row['51coolma_title']}  ".         "Author: {$row['51coolma_author']}  ".         "Submission Date : {$row['submission_date']}  ".         "--------------------------------";} echo "Fetched data successfully
  ";mysql_close($conn);?>

  
  

  GROUP BY 语句根据一个或多个列对结果集进行分组。
  

  在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

  GROUP BY 语法

  SELECT column_name, function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_name;

  ---

  实例演示

  本章节实例使用到了以下表结构及数据，使用前我们可以先将以下数据导入数据库中。

  SET NAMES utf8;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------  Table structure for `employee_tbl`-- ----------------------------DROP TABLE IF EXISTS `employee_tbl`;CREATE TABLE `employee_tbl` (  `id` int(11) NOT NULL,  `name` char(10) NOT NULL DEFAULT '',  `date` datetime NOT NULL,  `singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------  Records of `employee_tbl`-- ----------------------------BEGIN;INSERT INTO `employee_tbl` VALUES ('1', '小明', '2016-04-22 15:25:33', '1'), ('2', '小王', '2016-04-20 15:25:47', '3'), ('3', '小丽', '2016-04-19 15:26:02', '2'), ('4', '小王', '2016-04-07 15:26:14', '4'), ('5', '小明', '2016-04-11 15:26:40', '4'), ('6', '小明', '2016-04-04 15:26:54', '2');COMMIT;SET FOREIGN_KEY_CHECKS = 1;

  导入成功后，执行以下 SQL 语句：

  mysql> set names utf8;mysql> SELECT * FROM employee_tbl;+----+--------+---------------------+--------+| id | name   | date                | singin |+----+--------+---------------------+--------+|  1 | 小明 | 2016-04-22 15:25:33 |      1 ||  2 | 小王 | 2016-04-20 15:25:47 |      3 ||  3 | 小丽 | 2016-04-19 15:26:02 |      2 ||  4 | 小王 | 2016-04-07 15:26:14 |      4 ||  5 | 小明 | 2016-04-11 15:26:40 |      4 ||  6 | 小明 | 2016-04-04 15:26:54 |      2 |+----+--------+---------------------+--------+6 rows in set (0.00 sec)

  接下来我们使用 GROUP BY 语句 将数据表按名字进行分组，并统计每个人有多少条记录：

  mysql> SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;+--------+----------+| name   | COUNT(*) |+--------+----------+| 小丽 |        1 || 小明 |        3 || 小王 |        2 |+--------+----------+3 rows in set (0.01 sec)

  使用 WITH ROLLUP

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

  例如我们将以上的数据表按名字进行分组，再统计每个人登录的次数：

  mysql> SELECT name, SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;+--------+--------------+| name   | singin_count |+--------+--------------+| 小丽 |            2 || 小明 |            7 || 小王 |            7 || NULL   |           16 |+--------+--------------+4 rows in set (0.00 sec)

  其中记录 NULL 表示所有人的登录次数。

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

  select coalesce(a,b,c);

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

  以下实例中如果名字为空我们使用总数代替：

  mysql> SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;+--------------------------+--------------+| coalesce(name, '总数') | singin_count |+--------------------------+--------------+| 小丽                   |            2 || 小明                   |            7 || 小王                   |            7 || 总数                   |           16 |+--------------------------+--------------+4 rows in set (0.01 sec)

  
  

  在前几章节中，我们已经学会了如果在一张表中读取数据，这是相对简单的，但是在真正的应用中经常需要从多个数据表中读取数据。
  

  本章节我们将向大家介绍如何使用 MySQL 的 JOIN 在两个或多个表中查询数据。

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

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

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

  本章节使用的数据库结构及数据下载：W3CSCHOOL.sql。
  

  ---

  
  

  
  

  在命令提示符中使用 INNER JOIN

  我们在W3CSCHOOL数据库中有两张表 tcount_tbl 和 51coolma_tbl。两张数据表数据如下：

  实例

  尝试以下实例：

  root@host# mysql -u root -p password;Enter password:*******mysql> use W3CSCHOOL;Database changedmysql> SELECT * FROM tcount_tbl;+-----------------+----------------+| 51coolma_author | 51coolma_count |+-----------------+----------------+| mahran          |             20 || mahnaz          |           NULL || Jen             |           NULL || Gill            |             20 || John Poul       |              1 || Sanjay          |              1 |+-----------------+----------------+6 rows in set (0.01 sec)mysql> SELECT * from 51coolma_tbl;+-------------+----------------+-----------------+-----------------+| 51coolma_id | 51coolma_title | 51coolma_author | submission_date |+-------------+----------------+-----------------+-----------------+|           1 | Learn PHP      | John Poul       | 2007-05-24      ||           2 | Learn MySQL    | Abdul S         | 2007-05-24      ||           3 | JAVA Tutorial  | Sanjay          | 2007-05-06      |+-------------+----------------+-----------------+-----------------+3 rows in set (0.00 sec)mysql>

  接下来我们就使用MySQL的INNER JOIN(也可以省略 INNER 使用 JOIN，效果一样)来连接以上两张表来读取51coolma_tbl表中所有51coolma_author字段在tcount_tbl表对应的51coolma_count字段值：

  mysql> SELECT a.51coolma_id, a.51coolma_author, b.51coolma_count FROM 51coolma_tbl a INNER JOIN tcount_tbl b ON a.51coolma_author = b.51coolma_author;+-----------+---------------+--------------+| 51coolma_id | 51coolma_author | 51coolma_count |+-----------+---------------+--------------+|         1 | John Poul     |            1 ||         3 | Sanjay        |            1 |+-----------+---------------+--------------+2 rows in set (0.00 sec)

  以上 SQL 语句等价于：

  mysql> SELECT a.51coolma_id, a.51coolma_author, b.51coolma_count FROM 51coolma_tbl a, tcount_tbl b WHERE a.51coolma_author = b.51coolma_author;+-------------+-----------------+----------------+| 51coolma_id | 51coolma_author | 51coolma_count |+-------------+-----------------+----------------+|           1 | John Poul       |              1 ||           3 | Sanjay          |              1 |+-------------+-----------------+----------------+2 rows in set (0.01 sec)mysql>

  

  ---

  MySQL LEFT JOIN

  MySQL left join 与 join 有所不同。 MySQL LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。

  实例

  尝试以下实例，以 51coolma_tbl 为左表，tcount_tbl 为右表，理解MySQL LEFT JOIN的应用：

  root@host# mysql -u root -p password;Enter password:*******mysql> use W3CSCHOOL;Database changedmysql> SELECT a.51coolma_id, a.51coolma_author, b.51coolma_count FROM 51coolma_tbl a LEFT JOIN tcount_tbl b ON a.51coolma_author = b.51coolma_author;+-------------+-----------------+----------------+| 51coolma_id | 51coolma_author | 51coolma_count |+-------------+-----------------+----------------+|           1 | John Poul       |              1 ||           2 | Abdul S         |           NULL ||           3 | Sanjay          |              1 |+-------------+-----------------+----------------+3 rows in set (0.02 sec)

  以上实例中使用了LEFT JOIN，该语句会读取左边的数据表51coolma_tbl的所有选取的字段数据，即便在右侧表tcount_tbl中没有对应的51coolma_author字段值。

  

  ---

  MySQL RIGHT JOIN

  MySQL RIGHT JOIN 会读取右边数据表的全部数据，即便左边边表无对应数据。

  实例

  尝试以下实例，以 tcount_tbl 为左表，51coolma_tbl 为右表，理解MySQL RIGHT JOIN的应用：

  root@host# mysql -u root -p password;Enter password:*******mysql> use W3CSCHOOL;Database changedmysql> SELECT b.51coolma_id, b.51coolma_author, a.51coolma_count FROM tcount_tbl a RIGHT JOIN 51coolma_tbl b ON a.51coolma_author = b.51coolma_author;+-------------+-----------------+----------------+| 51coolma_id | 51coolma_author | 51coolma_count |+-------------+-----------------+----------------+|           1 | John Poul       |              1 ||           2 | Abdul S         |           NULL ||           3 | Sanjay          |              1 |+-------------+-----------------+----------------+3 rows in set (0.02 sec)

  以上实例中使用了 RIGHT JOIN，该语句会读取右边的数据表 51coolma_tbl 的所有选取的字段数据，即便在左侧表tcount_tbl中没有对应的51coolma_author字段值。

  

  ---

  在PHP脚本中使用JOIN

  PHP 中使用mysql_query()函数来执行SQL语句，你可以使用以上的相同的SQL语句作为mysql_query()函数的参数。

  尝试如下实例:

  <?php$dbhost = 'localhost:3036';$dbuser = 'root';$dbpass = 'rootpassword';$conn = mysql_connect($dbhost, $dbuser, $dbpass);if(! $conn ){  die('Could not connect: ' . mysql_error());}$sql = 'SELECT a.51coolma_id, a.51coolma_author, b.51coolma_count FROM 51coolma_tbl a INNER JOIN tcount_tbl b ON a.51coolma_author = b.51coolma_author';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)){    echo "Author:{$row['51coolma_author']}  <br> ".         "Count: {$row['51coolma_count']} <br> ".         "Tutorial ID: {$row['51coolma_id']} <br> ".         "--------------------------------<br>";} echo "Fetched data successfully
  ";mysql_close($conn);?>

  
  

  我们已经知道MySQL使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作。
  

  为了处理这种情况，MySQL提供了三大运算符:

  • IS NULL: 当列的值是NULL,此运算符返回true。
  • IS NOT NULL: 当列的值不为NULL, 运算符返回true。
  • <=>: 比较操作符（不同于=运算符），当比较的的两个值为NULL时返回true。

  关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。

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

  MySQL中处理NULL使用IS NULL和IS NOT NULL运算符。

  ---

  在命令提示符中使用 NULL 值

  以下实例中假设数据库 W3CSCHOOL 中的表 tcount_tbl 含有两列 51coolma_author 和 51coolma_count, 51coolma_count 中设置插入NULL值。

  实例

  尝试以下实例:

  root@host# mysql -u root -p password;Enter password:*******mysql> use W3CSCHOOL;Database changedmysql> create table tcount_tbl    -> (    -> 51coolma_author varchar(40) NOT NULL,    -> 51coolma_count  INT    -> );Query OK, 0 rows affected (0.05 sec)mysql> INSERT INTO tcount_tbl    -> (51coolma_author, 51coolma_count) values ('mahran', 20);mysql> INSERT INTO tcount_tbl    -> (51coolma_author, 51coolma_count) values ('mahnaz', NULL);mysql> INSERT INTO tcount_tbl    -> (51coolma_author, 51coolma_count) values ('Jen', NULL);mysql> INSERT INTO tcount_tbl    -> (51coolma_author, 51coolma_count) values ('Gill', 20);mysql> SELECT * from tcount_tbl;+-----------------+----------------+| 51coolma_author | 51coolma_count |+-----------------+----------------+| mahran          |             20 || mahnaz          |           NULL || Jen             |           NULL || Gill            |             20 |+-----------------+----------------+4 rows in set (0.00 sec)mysql>

  以下实例中你可以看到 = 和 != 运算符是不起作用的：

  mysql> SELECT * FROM tcount_tbl WHERE 51coolma_count = NULL;Empty set (0.00 sec)mysql> SELECT * FROM tcount_tbl WHERE 51coolma_count != NULL;Empty set (0.01 sec)

  查找数据表中 51coolma_count 列是否为 NULL，必须使用IS NULL和IS NOT NULL，如下实例：

  mysql> SELECT * FROM tcount_tbl     -> WHERE 51coolma_count IS NULL;+-----------------+----------------+| 51coolma_author | 51coolma_count |+-----------------+----------------+| mahnaz          |           NULL || Jen             |           NULL |+-----------------+----------------+2 rows in set (0.00 sec)mysql> SELECT * from tcount_tbl     -> WHERE 51coolma_count IS NOT NULL;+-----------------+----------------+| 51coolma_author | 51coolma_count |+-----------------+----------------+| mahran          |             20 || Gill            |             20 |+-----------------+----------------+2 rows in set (0.00 sec)

  ---

  使用PHP脚本处理 NULL 值

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

  以下实例中PHP设置了$51coolma_count变量，然后使用该变量与数据表中的 51coolma_count 字段进行比较：

  关于其中php的语法可以见于https://www.51coolma.cn/php

  isset()   https://www.51coolma.cn/php/php-isset.html 

  <?php$dbhost = 'localhost:3036';$dbuser = 'root';$dbpass = 'rootpassword';$conn = mysql_connect($dbhost, $dbuser, $dbpass);if(! $conn ){  die('Could not connect: ' . mysql_error());}if( isset($51coolma_count )){   $sql = 'SELECT 51coolma_author, 51coolma_count           FROM  tcount_tbl           WHERE 51coolma_count = $51coolma_count';}else{   $sql = 'SELECT 51coolma_author, 51coolma_count           FROM  tcount_tbl           WHERE 51coolma_count IS $51coolma_count';}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)){    echo "Author:{$row['51coolma_author']}  <br> ".         "Count: {$row['51coolma_count']} <br> ".         "--------------------------------<br>";} echo "Fetched data successfully
  ";mysql_close($conn);?>        

  在前面的章节我们已经了解到MySQL可以通过 LIKE ...% 来进行模糊匹配。
  

  MySQL 同样也支持其他正则表达式的匹配， MySQL中使用 REGEXP 操作符来进行正则表达式匹配。

  如果您了解PHP或Perl，那么操作起来就非常简单，因为MySQL的正则表达式匹配与这些脚本的类似。

  下表中的正则模式可应用于 REGEXP 操作符中。

  模式	描述
  ^	匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 ' ' 或 ' ' 之后的位置。
  $	匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 ' ' 或 ' ' 之前的位置。
  .	匹配除 " " 之外的任何单个字符。要匹配包括 ' ' 在内的任何字符，请使用象 '[. ]' 的模式。
  [...]	字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。
  [^...]	负值字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 "plain" 中的'p'。
  p1|p2|p3	匹配 p1 或 p2 或 p3。例如，'z|food' 能匹配 "z" 或 "food"。'(z|f)ood' 则匹配 "zood" 或 "food"。
  	匹配前面的子表达式零次或多次。例如，zo 能匹配 "z" 以及 "zoo"。* 等价于{0,}。
  +	匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。
  {n}	n 是一个非负整数。匹配确定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的两个 o。
  {n,m}	m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。

  实例

  了解以上的正则需求后，我们就可以根据自己的需求来编写带有正则表达式的SQL语句。以下我们将列出几个小实例(表名：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 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！
  

  • 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
  • 事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。
  • 事务用来管理 insert , update , delete 语句。

  一般来说，事务是必须满足4个条件（ACID）： Atomicity（原子性或不可分割性）、Consistency（一致性）、Isolation（隔离性或独立性）、Durability（持久性）

  • 1、原子性：一组事务，要么成功；要么撤回，即事务在执行过程中出错会回滚到事务开始前的状态。
  • 2、一致性 ： 一个事务不论是开始前还是结束后，数据库的完整性都没有被破坏。因此写入的数据必须完全符合所有预设规则（资料精确度、串联性以及后续数据库能够自发完成预定工作）。
  • 3、隔离性：数据库允许多个事务并发的同时对其数据进行读写修改等操作，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离可分为：Read uncommitted（读未提交）、Read committed（读提交）、Repeatable read（可重复读）、Serializable（串行化）。
  • 4、持久性：事务在处理结束后对数据做出的修改是永久的，无法丢失。

  事务控制语句

  1，显式的开始一个事务：

  start transaction

  或

  begin

  2, 做保存点，一个事务中可以有多个保存点：

  savepoint 保存点名称

  3, 提交事务，并使数据库中进行的所有修改成为永久性的：

  commit

  或

  commit work

  4，回滚结束用户的事务，并撤销正在进行的所有未提交的修改：

  rollback

  或

  rollback work

  5.删除一个事务的保存点，若没有指定保存点，执行该语句操作会抛错。

  release savepoint 保存点名称

  6.将事务滚回标记点：

  rollback to 标记点

  7.设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

  set transaction

  事务处理方法

  1.用 begin ， rollback ， commit 来实现事务处理。

  2.用 set 来改变 MySQL 的自动提交模式。

  • set autocommit = 0 （禁止自动提交）。
  • set autocommit = 1 （开启自动提交）。

  PHP中使用事务实例

  <?php$handler=mysql_connect("localhost","root","password");mysql_select_db("task");mysql_query("SET AUTOCOMMIT=0");//设置为不自动提交，因为MYSQL默认立即执行 mysql_query("BEGIN");//开始事务定义if(!mysql_query("insert into trans (id) values('2')")){mysql_query("ROOLBACK");//判断当执行失败时回滚}if(!mysql_query("insert into trans (id) values('4')")){mysql_query("ROOLBACK");//判断执行失败回滚}mysql_query("COMMIT");//执行事务mysql_close($handler);?>

  
  

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

  开始本章教程前让我们先创建一张表，表名为：testalter_tbl。

  root@host# mysql -u root -p password;Enter password:mysql> use W3CSCHOOL;Database changedmysql> create table testalter_tbl    -> (    -> i INT,    -> c CHAR(1)    -> );Query OK, 0 rows affected (0.05 sec)mysql> SHOW COLUMNS FROM testalter_tbl;+-------+---------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| i     | int(11) | YES  |     | NULL    |       || c     | char(1) | YES  |     | NULL    |       |+-------+---------+------+-----+---------+-------+2 rows in set (0.00 sec)

  ---

  删除，添加或修改表字段

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

  mysql> ALTER TABLE testalter_tbl  DROP i;

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

  MySQL 中使用 ADD 子句来想数据表中添加列，如下实例在表 testalter_tbl 中添加 i 字段，并定义数据类型:

  mysql> ALTER TABLE testalter_tbl ADD i INT;

  执行以上命令后，i 字段会自动添加到数据表字段的末尾。

  mysql> SHOW COLUMNS FROM testalter_tbl;+-------+---------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| c     | char(1) | YES  |     | NULL    |       || i     | int(11) | YES  |     | NULL    |       |+-------+---------+------+-----+---------+-------+2 rows in set (0.00 sec)

  如果你需要指定新增字段的位置，可以使用MySQL提供的关键字 FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）。

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

  ALTER TABLE testalter_tbl DROP i;ALTER TABLE testalter_tbl ADD i INT FIRST;ALTER TABLE testalter_tbl DROP i;ALTER TABLE testalter_tbl ADD i INT AFTER c;

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

  ---

  修改字段类型及名称

  如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句 。

  例如，把字段 c 的类型从 CHAR(1) 改为 CHAR(10)，可以执行以下命令:

  mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);

  使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段的类型及名称。尝试如下实例：

  mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;

  如果你现在想把字段 j 从 BIGINT 修改为 INT，SQL语句如下：

  mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

  ---

  ALTER TABLE 对 Null 值和默认值的影响

  当你修改字段时，你可以指定是否包含只或者是否设置默认值。

  以下实例，指定字段 j 为 NOT NULL 且默认值为100 。

  mysql> ALTER TABLE testalter_tbl     -> MODIFY j BIGINT NOT NULL DEFAULT 100;

  如果你不设置默认值，MySQL会自动设置该字段默认为 NULL。

  ---

  修改字段默认值

  你可以使用 ALTER 来修改字段的默认值，尝试以下实例：

  mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;mysql> SHOW COLUMNS FROM testalter_tbl;+-------+---------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| c     | char(1) | YES  |     | NULL    |       || i     | int(11) | YES  |     | 1000    |       |+-------+---------+------+-----+---------+-------+2 rows in set (0.00 sec)

  你也可以使用 ALTER 命令及 DROP子句来删除字段的默认值，如下实例：

  mysql> ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;mysql> SHOW COLUMNS FROM testalter_tbl;+-------+---------+------+-----+---------+-------+| Field | Type    | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| c     | char(1) | YES  |     | NULL    |       || i     | int(11) | YES  |     | NULL    |       |+-------+---------+------+-----+---------+-------+2 rows in set (0.00 sec)Changing a Table Type:

  修改数据表类型，可以使用 ALTER 命令及 TYPE 子句来完成。尝试以下实例，我们将表 testalter_tbl 的类型修改为 MYISAM ：

  注意：查看数据表类型可以使用 SHOW TABLE STATUS 语句。

  mysql> ALTER TABLE testalter_tbl TYPE = MYISAM;mysql>  SHOW TABLE STATUS LIKE 'testalter_tbl'G 1. row **           Name: testalter_tbl           Type: MyISAM     Row_format: Fixed           Rows: 0 Avg_row_length: 0    Data_length: 0Max_data_length: 25769803775   Index_length: 1024      Data_free: 0 Auto_increment: NULL    Create_time: 2007-06-03 08:04:36    Update_time: 2007-06-03 08:04:36     Check_time: NULL Create_options:        Comment:1 row in set (0.00 sec)

  ---

  修改表名

  如果需要修改数据表的名称，可以在 ALTER TABLE 语句中使用 RENAME 子句来实现。

  尝试以下实例将数据表 testalter_tbl 重命名为 alter_tbl：

  mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

  ALTER 命令还可以用来创建及删除MySQL数据表的索引，该功能我们会在接下来的章节中介绍。

  
  

  MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

  打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

  拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

  索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

  创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

  实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

  上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，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))   );  

  删除索引的语法

  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);

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

  mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

  删除主键时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。

  显示索引信息

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

  尝试以下实例:

  mysql> SHOW INDEX FROM table_name; G........

  
  

  MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，MySQL会自动删除表并释放所有空间。
  

  临时表在MySQL 3.23版本中添加，如果你的MySQL版本低于 3.23版本就无法使用MySQL的临时表。不过现在一般很少有再使用这么低版本的MySQL数据库服务了。

  MySQL临时表只在当前连接可见，如果你使用PHP脚本来创建MySQL临时表，那每当PHP脚本执行完成后，该临时表也会自动销毁。

  如果你使用了其他MySQL客户端程序连接MySQL数据库服务器来创建临时表，那么只有在关闭客户端程序时才会销毁临时表，当然你也可以手动销毁。

  实例

  以下展示了使用MySQL 临时表的简单实例，以下的SQL代码可以适用于PHP脚本的mysql_query()函数。

  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 0);Query 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 0);Query 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 'W3CSCHOOL.SalesSummary' doesn't exist

  
  

  如果我们需要完全的复制MySQL的数据表，包括表的结构，索引，默认值等。 如果仅仅使用CREATE TABLE ... SELECT 命令，是无法实现的。

  本章节将为大家介绍如何完整的复制MySQL数据表，步骤如下：

  • 使用 SHOW CREATE TABLE 命令获取创建数据表(CREATE TABLE) 语句，该语句包含了原数据表的结构，索引等。
  • 复制以下命令显示的SQL语句，修改数据表名，并执行SQL语句，通过以上命令 将完全的复制数据表结构。
  • 如果你想复制表的内容，你就可以使用 INSERT INTO ... SELECT 语句来实现。

  实例

  尝试以下实例来复制表 51coolma_tbl 。

  步骤一：

  获取数据表的完整结构。

  mysql> SHOW CREATE TABLE 51coolma_tbl G;*************************** 1. row ***************************       Table: 51coolma_tblCreate Table: CREATE TABLE `51coolma_tbl` (  `51coolma_id` int(11) NOT NULL auto_increment,  `51coolma_title` varchar(100) NOT NULL default '',  `51coolma_author` varchar(40) NOT NULL default '',  `submission_date` date default NULL,  PRIMARY KEY  (`51coolma_id`),  UNIQUE KEY `AUTHOR_INDEX` (`51coolma_author`)) ENGINE=InnoDB1 row in set (0.00 sec)ERROR:No query specified

  步骤二：

  修改SQL语句的数据表名，并执行SQL语句。

  mysql> CREATE TABLE `clone_tbl` (  -> `51coolma_id` int(11) NOT NULL auto_increment,  -> `51coolma_title` varchar(100) NOT NULL default '',  -> `51coolma_author` varchar(40) NOT NULL default '',  -> `submission_date` date default NULL,  -> PRIMARY KEY  (`51coolma_id`),  -> UNIQUE KEY `AUTHOR_INDEX` (`51coolma_author`)-> ) ENGINE=InnoDB;Query OK, 0 rows affected (1.80 sec)

  步骤三：

  执行完第二步骤后，你将在数据库中创建新的克隆表 clone_tbl。 如果你想拷贝数据表的数据你可以使用 INSERT INTO... SELECT 语句来实现。

  mysql> INSERT INTO clone_tbl (51coolma_id,    ->                        51coolma_title,    ->                        51coolma_author,    ->                        submission_date)    -> SELECT 51coolma_id,51coolma_title,    ->        51coolma_author,submission_date    -> FROM 51coolma_tbl;Query OK, 3 rows affected (0.07 sec)Records: 3  Duplicates: 0  Warnings: 0

  执行以上步骤后，你将完整的复制表，包括表结构及表数据。

  
  

  你可能想知道MySQL以下三种信息：
  

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

  在MySQL的命令提示符中，我们可以很容易的获取以上服务器信息。 但如果使用Perl或PHP等脚本语言，你就需要调用特定的接口函数来获取。 接下来我们会详细介绍。

  ---

  获取查询语句影响的记录数

  PERL 实例

  在 DBI 脚本中， 语句影响的记录数通过函数 do( ) 或 execute( )返回：

  # 方法 1# 使用do( ) 执行  $query my $count = $dbh->do ($query);# 如果发生错误会输出 0printf "%d rows were affected
  ", (defined ($count) ? $count : 0);# 方法 2# 使用prepare( ) 及 execute( ) 执行  $query my $sth = $dbh->prepare ($query);my $count = $sth->execute ( );printf "%d rows were affected
  ", (defined ($count) ? $count : 0);

  PHP 实例

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

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

  ---

  数据库和数据表列表

  你可以很容易的在MySQL服务器中获取数据库和数据表列表。 如果你没有足够的权限，结果将返回 null。

  你也可以使用 SHOW TABLES 或 SHOW DATABASES 语句来获取数据库和数据表列表。

  PERL 实例

  # 获取当前数据库中所有可用的表。my @tables = $dbh->tables ( );foreach $table (@tables ){   print "Table Name $table
  ";}

  PHP 实例

  <?php$con = mysql_connect("localhost", "userid", "password");if (!$con){  die('Could not connect: ' . mysql_error());}$db_list = mysql_list_dbs($con);while ($db = mysql_fetch_object($db_list)){  echo $db->Database . "<br />";}mysql_close($con);?>

  ---

  获取服务器元数据

  以下命令语句可以在MySQL的命令提示符使用，也可以在脚本中 使用，如PHP脚本。

  命令	描述
  SELECT VERSION( )	服务器版本信息
  SELECT DATABASE( )	当前数据库名 (或者返回空)
  SELECT USER( )	当前用户名
  SHOW STATUS	服务器状态
  SHOW VARIABLES	服务器配置变量

  
  

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

  本章我们将介绍如何使用MySQL的序列。

  ---

  使用AUTO_INCREMENT

  MySQL中最简单使用序列的方法就是使用 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 collected);Query 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)

  ---

  获取AUTO_INCREMENT值

  在MySQL的客户端中你可以使用 SQL中的LAST_INSERT_ID( ) 函数来获取最后的插入表中的自增列的值。

  在PHP或PERL脚本中也提供了相应的函数来获取最后的插入表中的自增列的值。

  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 TABLE insect DROP id;mysql> ALTER TABLE insect    -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,    -> ADD PRIMARY KEY (id);

  ---

  设置序列的开始值

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

  mysql> CREATE TABLE insect    -> (    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT = 100,    -> PRIMARY KEY (id),    -> name VARCHAR(30) NOT NULL, # type of insect    -> date DATE NOT NULL, # date collected    -> origin VARCHAR(30) NOT NULL # where collected);

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

  mysql> ALTER TABLE t AUTO_INCREMENT = 100;

  
  

  有些 MySQL 数据表中可能存在重复的记录，有些情况我们允许重复数据的存在，但有时候我们也需要删除这些重复的数据。
  

  本章节我们将为大家介绍如何防止数据表出现重复数据及如何删除数据表中的重复数据。

  ---

  防止表中出现重复数据

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

  让我们尝试一个实例：下表中无索引及主键，所以该表允许出现多条重复记录。

  CREATE TABLE person_tbl(    first_name CHAR(20),    last_name CHAR(20),    sex CHAR(10));

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

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

  如果我们设置了唯一索引，那么在插入重复数据时，SQL语句将无法执行成功,并抛出错。

  INSERT IGNORE INTO与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)mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)    -> VALUES( 'Jay', 'Thomas');Query OK, 0 rows affected (0.00 sec)

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

  另一种设置数据的唯一性方法是添加一个UNIQUE索引，如下所示：

  CREATE TABLE person_tbl(   first_name CHAR(20) NOT NULL,   last_name CHAR(20) NOT NULL,   sex CHAR(10),   UNIQUE (last_name, first_name));

  ---

  查询重复记录

  select user_name,count(*) as count from user_table group by user_name having count>1;select * from people where peopleId in (select peopleId from people group 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;

  以上查询语句将返回 person_tbl 表中重复的记录数。 一般情况下，查询重复的值，请执行以下操作：

  • 确定哪一列包含的值可能会重复。
  • 在列选择列表使用COUNT(*)列出的那些列。
  • 在GROUP BY子句中列出的列。
  • HAVING子句设置重复数大于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    ->                  FROM person_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注入，并通过脚本来过滤SQL中注入的字符。

  所谓SQL注入，就是通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。

  我们永远不要信任用户的输入，我们必须认定用户输入的数据都是不安全的，我们都需要对用户输入的数据进行过滤处理。

  以下实例中，输入的用户名必须为字母、数字及下划线的组合，且用户名长度为 8 到 20 个字符之间：

  if (preg_match("/^w{8,20}$/", $_GET['username'], $matches)){   $result = mysql_query("SELECT * FROM users                           WHERE username=$matches[0]");} else {   echo "username 输入异常";}

  让我们看下在没有过滤特殊字符时，出现的SQL情况：

  // 设定$name 中插入了我们不需要的SQL语句$name = "Qadir'; DELETE FROM users;";mysql_query("SELECT * FROM users WHERE name='{$name}'");

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

  在PHP中的 mysql_query() 是不允许执行多个SQL语句的，但是在 SQLite 和 PostgreSQL 是可以同时执行多条SQL语句的，所以我们对这些用户的数据需要进行严格的验证。

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

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

  ---

  防止SQL注入

  在脚本语言，如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 * FROM users WHERE name='{$name}'");

  ---

  Like语句中的注入

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

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

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

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

  语法格式:

  addcslashes(string,characters)

  参数	描述
  string	必需。规定要检查的字符串。
  characters	可选。规定受 addcslashes() 影响的字符或字符范围。

  具体应用可以查看：PHP addcslashes() 函数

  
  

  MySQL中你可以使用SELECT...INTO OUTFILE语句来简单的导出数据到文本文件上。
  

  ---

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

  以下实例中我们将数据表 51coolma_tbl 数据导出到 /tmp/tutorials.txt 文件中:

  mysql> SELECT * FROM tutorials_tbl     -> INTO OUTFILE '/tmp/tutorials.txt';

  你可以通过命令选项来设置数据输出的指定格式，以下实例为导出 CSV 格式：

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

  在下面的例子中，生成一个文件，各值用逗号隔开。这种格式可以被许多程序使用。

  SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'LINES TERMINATED BY '
  '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用于转存储数据库的实用程序。它主要产生一个SQL脚本，其中包含从头重新创建数据库所必需的命令CREATE TABLE INSERT等。

  使用mysqldump导出数据需要使用 --tab 选项来指定导出文件指定的目录，该目标必须是可写的。

  以下实例将数据表 tutorials_tbl 导出到 /tmp 目录中：

  $ mysqldump -u root -p --no-create-info             --tab=/tmp W3CSCHOOL 51coolma_tblpassword ******

  ---

  导出SQL格式的数据

  导出SQL格式的数据到指定文件，如下所示：

  $ mysqldump -u root -p W3CSCHOOL 51coolma_tbl > dump.txtpassword ******

  以上命令创建的文件内容如下：

  -- MySQL dump 8.23---- Host: localhost    Database: W3CSCHOOL----------------------------------------------------------- Server version       3.23.58---- Table structure for table `51coolma_tbl`--CREATE TABLE 51coolma_tbl (  51coolma_id int(11) NOT NULL auto_increment,  51coolma_title varchar(100) NOT NULL default '',  51coolma_author varchar(40) NOT NULL default '',  submission_date date default NULL,  PRIMARY KEY  (51coolma_id),  UNIQUE KEY AUTHOR_INDEX (51coolma_author)) TYPE=MyISAM;---- Dumping data for table `51coolma_tbl`--INSERT INTO 51coolma_tbl        VALUES (1,'Learn PHP','John Poul','2007-05-24');INSERT INTO 51coolma_tbl        VALUES (2,'Learn MySQL','Abdul S','2007-05-24');INSERT INTO 51coolma_tbl        VALUES (3,'JAVA Tutorial','Sanjay','2007-05-06');

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

  $ mysqldump -u root -p W3CSCHOOL > database_dump.txtpassword ******

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

  $ mysqldump -u root -p --all-databases > database_dump.txtpassword ******

  --all-databases 选项在 MySQL 3.23.12 及以后版本加入。

  该方法可用于实现数据库的备份策略。

  ---

  将数据表及数据库拷贝至其他主机

  如果你需要将数据拷贝至其他的 MySQL 服务器上, 你可以在 mysqldump 命令中指定数据库名及数据表。

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

  $ mysqldump -u root -p database_name table_name > dump.txtpassword *****

  如果完整备份数据库，则无需使用特定的表名称。

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

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

  你也可以使用以下命令将导出的数据直接导入到远程的服务器上，但请确保两台服务器是相通的，是可以相互访问的：</p>$ mysqldump -u root -p database_name        | mysql -h other-host.com database_name

  以上命令中使用了管道来将导出的数据导入到指定的远程主机上。

  
  

  MySQL中可以使用两种简单的方式来导入MySQL导出的数据。
  

  ---

  使用 LOAD DATA 导入数据

  MySQL 中提供了LOAD DATA INFILE语句来插入数据。 以下实例中将从当前目录中读取文件 dump.txt ，将该文件中的数据插入到当前数据库的 mytbl 表中。

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

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

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

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

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

  mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl  -> FIELDS TERMINATED BY ':'  -> LINES TERMINATED BY '
  ';

  LOAD DATA 默认情况下是按照数据文件中列的顺序插入数据的，如果数据文件中的列与插入表中的列不一致，则需要指定列的顺序。

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

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

  ---

  使用 mysqlimport 导入数据

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

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

  $ mysqlimport -u root -p --local database_name dump.txtpassword *****

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

  $ mysqlimport -u root -p --local --fields-terminated-by=":"    --lines-terminated-by="
  "  database_name dump.txtpassword *****

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

  $ mysqlimport -u root -p --local --columns=b,c,a     database_name dump.txtpassword *****

  ---

  mysqlimport的常用选项介绍

  选项	功能
  -d or --delete	新数据导入数据表中之前删除数据数据表中的所有信息
  -f or --force	不管是否遇到错误，mysqlimport将强制继续插入数据
  -i or --ignore	mysqlimport跳过或者忽略那些有相同唯一 关键字的行， 导入文件中的数据将被忽略。
  -l or -lock-tables	数据被插入之前锁住表，这样就防止了， 你在更新数据库时，用户的查询和更新受到影响。
  -r or -replace	这个选项与－i选项的作用相反；此选项将替代 表中有相同唯一关键字的记录。
  --fields-enclosed- by= char	指定文本文件中数据的记录时以什么括起的， 很多情况下 数据以双引号括起。 默认的情况下数据是没有被字符括起的。
  --fields-terminated- by=char	指定各个数据的值之间的分隔符，在句号分隔的文件中， 分隔符是句号。您可以用此选项指定数据之间的分隔符。 默认的分隔符是跳格符（Tab）
  --lines-terminated- by=str	此选项指定文本文件中行与行之间数据的分隔字符串 或者字符。 默认的情况下mysqlimport以newline为行分隔符。 您可以选择用一个字符串来替代一个单个的字符： 一个新行或者一个回车。

  mysqlimport命令常用的选项还有-v 显示版本（version）， -p 提示输入密码（password）等。

  
  

  MySQL Date 函数

  ---

  定义和用法

  DATE_ADD() 函数向日期添加指定的时间间隔。

  语法

  DATE_ADD(date,INTERVAL expr type)

  date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。

  type 参数可以是下列值：

  Type 值
  MICROSECOND
  SECOND
  MINUTE
  HOUR
  DAY
  WEEK
  MONTH
  QUARTER
  YEAR
  SECOND_MICROSECOND
  MINUTE_MICROSECOND
  MINUTE_SECOND
  HOUR_MICROSECOND
  HOUR_SECOND
  HOUR_MINUTE
  DAY_MICROSECOND
  DAY_SECOND
  DAY_MINUTE
  DAY_HOUR
  YEAR_MONTH

  
  

  ---

  实例

  假设我们有如下的 "Orders" 表：

  OrderId	ProductName	OrderDate
  1	Jarlsberg Cheese	2008-11-11 13:23:44.657

  现在，我们想要向 "OrderDate" 添加 45 天，这样就可以找到付款日期。

  我们使用下面的 SELECT 语句：

  SELECT OrderId,DATE_ADD(OrderDate,INTERVAL 45 DAY) AS OrderPayDate
  FROM Orders

  结果：

  OrderId	OrderPayDate
  1	2008-12-26 13:23:44.657

  
  

  ---

  MySQL Date 函数

  
  

  MySQL Date 函数

  ---

  定义和用法

  DATE_SUB() 函数从日期减去指定的时间间隔。

  语法

  DATE_SUB(date,INTERVAL expr type)

  date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。

  type 参数可以是下列值：

  Type 值
  MICROSECOND
  SECOND
  MINUTE
  HOUR
  DAY
  WEEK
  MONTH
  QUARTER
  YEAR
  SECOND_MICROSECOND
  MINUTE_MICROSECOND
  MINUTE_SECOND
  HOUR_MICROSECOND
  HOUR_SECOND
  HOUR_MINUTE
  DAY_MICROSECOND
  DAY_SECOND
  DAY_MINUTE
  DAY_HOUR
  YEAR_MONTH

  
  

  ---

  实例

  假设我们有如下的 "Orders" 表：

  OrderId	ProductName	OrderDate
  1	Jarlsberg Cheese	2008-11-11 13:23:44.657

  现在，我们想要向 "OrderDate" 减去 5 天。

  我们使用下面的 SELECT 语句：

  SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 5 DAY) AS SubtractDate
  FROM Orders

  结果：

  OrderId	SubtractDate
  1	2008-11-06 13:23:44.657

  
  

  ---

  MySQL Date 函数

  
  

  MySQL Date 函数

  ---

  定义和用法

  DATEDIFF() 函数返回两个日期之间的天数。

  语法

  DATEDIFF(date1,date2)

  date1 和 date2 参数是合法的日期或日期/时间表达式。

  注释：只有值的日期部分参与计算。

  ---

  实例

  下面是 SELECT 语句：

  SELECT DATEDIFF('2008-11-30','2008-11-29') AS DiffDate

  结果如下所示：

  DiffDate
  1

  实例

  下面是 SELECT 语句：

  SELECT DATEDIFF('2008-11-29','2008-11-30') AS DiffDate

  结果如下所示：

  DiffDate
  -1

  
  

  ---

  MySQL Date 函数

  
  

  MySQL Date 函数

  ---

  定义和用法

  DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。

  语法

  DATE_FORMAT(date,format)

  date 参数是合法的日期。format 规定日期/时间的输出格式。

  可以使用的格式有：

  格式	描述
  %a	缩写星期名
  %b	缩写月名
  %c	月，数值
  %D	带有英文前缀的月中的天
  %d	月的天，数值（00-31）
  %e	月的天，数值（0-31）
  %f	微秒
  %H	小时（00-23）
  %h	小时（01-12）
  %I	小时（01-12）
  %i	分钟，数值（00-59）
  %j	年的天（001-366）
  %k	小时（0-23）
  %l	小时（1-12）
  %M	月名
  %m	月，数值（00-12）
  %p	AM 或 PM
  %r	时间，12-小时（hh:mm:ss AM 或 PM）
  %S	秒（00-59）
  %s	秒（00-59）
  %T	时间, 24-小时（hh:mm:ss）
  %U	周（00-53）星期日是一周的第一天
  %u	周（00-53）星期一是一周的第一天
  %V	周（01-53）星期日是一周的第一天，与 %X 使用
  %v	周（01-53）星期一是一周的第一天，与 %x 使用
  %W	星期名
  %w	周的天（0=星期日, 6=星期六）
  %X	年，其中的星期日是周的第一天，4 位，与 %V 使用
  %x	年，其中的星期一是周的第一天，4 位，与 %v 使用
  %Y	年，4 位
  %y	年，2 位

  
  

  ---

  实例

  下面的脚本使用 DATE_FORMAT() 函数来显示不同的格式。我们使用 NOW() 来获得当前的日期/时间：

  select  DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p'),DATE_FORMAT(NOW(),'%m-%d-%Y'),DATE_FORMAT(NOW(),'%d %b %y'),DATE_FORMAT(NOW(),'%d %b %Y %T:%f')

  结果如下所示：

  Nov 04 2008 11:45 PM
  11-04-2008
  04 Nov 08
  04 Nov 2008 11:45:34:243

  
  

  ---

  MySQL Date 函数

  
  

  MySQL Date 函数

  ---

  定义和用法

  NOW() 返回当前的日期和时间。

  语法

  NOW()

  
  

  ---

  实例

  下面是 SELECT 语句：

  SELECT NOW(),CURDATE(),CURTIME()

  结果如下所示：

  NOW()	CURDATE()	CURTIME()
  2008-11-11 12:45:34	2008-11-11	12:45:34

  实例

  下面的 SQL 创建带有日期时间列（OrderDate）的 "Orders" 表：

  CREATE TABLE Orders
  (
  OrderId int NOT NULL,
  ProductName varchar(50) NOT NULL,
  OrderDate datetime NOT NULL DEFAULT NOW(),
  PRIMARY KEY (OrderId)
  )

  请注意，OrderDate 列规定 NOW() 作为默认值。作为结果，当您向表中插入行时，当前日期和时间自动插入列中。

  现在，我们想要在 "Orders" 表中插入一条记录：

  INSERT INTO Orders (ProductName) VALUES ('Jarlsberg Cheese')

  "Orders" 表将如下所示：

  OrderId	ProductName	OrderDate
  1	Jarlsberg Cheese	2008-11-11 13:23:44.657

  
  

  ---

  MySQL Date 函数

  
  

  MySQL Date 函数

  ---

  定义和用法

  CURDATE() 返回当前的日期。

  语法

  CURDATE()

  
  

  ---

  实例

  下面是 SELECT 语句：

  SELECT NOW(),CURDATE(),CURTIME()

  结果如下所示：

  NOW()	CURDATE()	CURTIME()
  2008-11-11 12:45:34	2008-11-11	12:45:34

  实例

  下面的 SQL 创建带有日期时间列（OrderDate）的 "Orders" 表：

  CREATE TABLE Orders
  (
  OrderId int NOT NULL,
  ProductName varchar(50) NOT NULL,
  OrderDate datetime NOT NULL DEFAULT CURDATE(),
  PRIMARY KEY (OrderId)
  )

  请注意，OrderDate 列规定 CURDATE() 作为默认值。作为结果，当您向表中插入行时，当前日期和时间自动插入列中。

  现在，我们想要在 "Orders" 表中插入一条记录：

  INSERT INTO Orders (ProductName) VALUES ('Jarlsberg Cheese')

  "Orders" 表将如下所示：

  OrderId	ProductName	OrderDate
  1	Jarlsberg Cheese	2008-11-11

  
  

  ---

  MySQL Date 函数

  
  

  MySQL Date 函数

  ---

  定义和用法

  CURTIME() 返回当前的时间。

  语法

  CURTIME()

  
  

  ---

  实例

  下面是 SELECT 语句：

  SELECT NOW(),CURDATE(),CURTIME()

  结果如下所示：

  NOW()	CURDATE()	CURTIME()
  2008-11-11 12:45:34	2008-11-11	12:45:34

  
  

  ---

  MySQL Date 函数

  
  

  MySQL Date 函数

  ---

  定义和用法

  DATE() 函数提取日期或日期/时间表达式的日期部分。

  语法

  DATE(date)

  date 参数是合法的日期表达式。

  ---

  实例

  假设我们有如下的 "Orders" 表：

  OrderId	ProductName	OrderDate
  1	Jarlsberg Cheese	2008-11-11 13:23:44.657

  下面是 SELECT 语句：

  SELECT ProductName, DATE(OrderDate) AS OrderDate
  FROM Orders
  WHERE OrderId=1

  结果如下所示：

  ProductName	OrderDate
  Jarlsberg Cheese	2008-11-11

  
  

  ---

  MySQL Date 函数

  
  

  MySQL Date 函数

  ---

  定义和用法

  EXTRACT() 函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。

  语法

  EXTRACT(unit FROM date)

  date 参数是合法的日期表达式。unit 参数可以是下列的值：

  Unit 值
  MICROSECOND
  SECOND
  MINUTE
  HOUR
  DAY
  WEEK
  MONTH
  QUARTER
  YEAR
  SECOND_MICROSECOND
  MINUTE_MICROSECOND
  MINUTE_SECOND
  HOUR_MICROSECOND
  HOUR_SECOND
  HOUR_MINUTE
  DAY_MICROSECOND
  DAY_SECOND
  DAY_MINUTE
  DAY_HOUR
  YEAR_MONTH

  
  

  ---

  实例

  假设我们有如下的 "Orders" 表：

  OrderId	ProductName	OrderDate
  1	Jarlsberg Cheese	2008-11-11 13:23:44.657

  下面是 SELECT 语句：

  SELECT EXTRACT(YEAR FROM OrderDate) AS OrderYear,
  EXTRACT(MONTH FROM OrderDate) AS OrderMonth,
  EXTRACT(DAY FROM OrderDate) AS OrderDay,
  FROM Orders
  WHERE OrderId=1

  结果如下所示：

  OrderYear	OrderMonth	OrderDay
  2008	11	11

  
  

  ---

  MySQL Date 函数

  
  

  
  

  使用方法：
  CONCAT(str1,str2,…)  

  返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。

  注意：
  如果所有参数均为非二进制字符串，则结果为非二进制字符串。 
  如果自变量中含有任一二进制字符串，则结果为一个二进制字符串。
  一个数字参数被转化为与之相等的二进制字符串格式；若要避免这种情况，可使用显式类型 cast, 例如：
  

  SELECT CONCAT(CAST(int_col AS CHAR), char_col)

  MySQL的concat函数可以连接一个或者多个字符串,如
  

  mysql> select concat('10');+--------------+| concat('10') |+--------------+| 10   |+--------------+1 row in set (0.00 sec)mysql> select concat('11','22','33');+------------------------+| concat('11','22','33') |+------------------------+| 112233 |+------------------------+1 row in set (0.00 sec)

  
  

  MySQL的concat函数在连接字符串的时候，只要其中一个是NULL,那么将返回NULL
  

  mysql> select concat('11','22',null);+------------------------+| concat('11','22',null) |+------------------------+| NULL   |+------------------------+1 row in set (0.00 sec)

  
  

  MySQL中 concat_ws 函数
  使用方法： 
  CONCAT_WS(separator,str1,str2,...)

  CONCAT_WS() 代表 CONCAT With Separator ，是CONCAT()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。
  注意：
  如果分隔符为 NULL，则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。

  如连接后以逗号分隔 

  mysql> select concat_ws(',','11','22','33');+-------------------------------+| concat_ws(',','11','22','33') |+-------------------------------+| 11,22,33 |+-------------------------------+1 row in set (0.00 sec)和MySQL中concat函数不同的是, concat_ws函数在执行的时候,不会因为NULL值而返回NULL mysql> select concat_ws(',','11','22',NULL);+-------------------------------+| concat_ws(',','11','22',NULL) |+-------------------------------+| 11,22 |+-------------------------------+1 row in set (0.00 sec)

  MySQL中 group_concat 函数
  完整的语法如下：
  group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

  基本查询

  mysql> select * from aa;+------+------+| id| name |+------+------+|1 | 10||1 | 20||1 | 20||2 | 20||3 | 200   ||3 | 500   |+------+------+6 rows in set (0.00 sec)

  以id分组，把name字段的值打印在一行，逗号分隔(默认)

  mysql> select id,group_concat(name) from aa group by id;+------+--------------------+| id| group_concat(name) |+------+--------------------+|1 | 10,20,20||2 | 20 ||3 | 200,500|+------+--------------------+3 rows in set (0.00 sec)

  以id分组，把name字段的值打印在一行，分号分隔

  mysql> select id,group_concat(name separator ';') from aa group by id;+------+----------------------------------+| id| group_concat(name separator ';') |+------+----------------------------------+|1 | 10;20;20 ||2 | 20||3 | 200;500   |+------+----------------------------------+3 rows in set (0.00 sec)

  以id分组，把去冗余的name字段的值打印在一行，

  逗号分隔

  mysql> select id,group_concat(distinct name) from aa group by id;+------+-----------------------------+| id| group_concat(distinct name) |+------+-----------------------------+|1 | 10,20||2 | 20   ||3 | 200,500 |+------+-----------------------------+3 rows in set (0.00 sec)

  
  

  以id分组，把name字段的值打印在一行，逗号分隔，以name排倒序
  

  mysql> select id,group_concat(name order by name desc) from aa group by id;+------+---------------------------------------+| id| group_concat(name order by name desc) |+------+---------------------------------------+|1 | 20,20,10   ||2 | 20||3 | 500,200|+------+---------------------------------------+3 rows in set (0.00 sec)

  
  

  repeat()函数

      用来复制字符串,如下'ab'表示要复制的字符串，2表示复制的份数

    

   mysql> select repeat('ab',2);+----------------+| repeat('ab',2) |+----------------+| abab           |+----------------+   1 row in set (0.00 sec)

  又如

  mysql> select repeat('a',2);+---------------+| repeat('a',2) |+---------------+| aa            |+---------------+1 row in set (0.00 sec)

  
  mysql向表中某字段后追加一段字符串:
  

  update table_name set field=CONCAT(field,'',str)

  mysql 向表中某字段前加字符串
  

  update table_name set field=CONCAT('str',field)

  
  

  
  

  
  

  1、从左开始截取字符串 
  

  left（str, length） 
  说明：left（被截取字段，截取长度） 
  例：

  select left(content,200) as abstract from my_content_t 

  2、从右开始截取字符串 
  right（str, length） 
  说明：right（被截取字段，截取长度） 
  例：

  select right(content,200) as abstract from my_content_t 

  3、截取字符串 
  substring（str, pos） 
  substring（str, pos, length） 
  说明：substring（被截取字段，从第几位开始截取） 
  substring（被截取字段，从第几位开始截取，截取长度） 
  

  例：

  select substring(content,5) as abstract from my_content_t select substring(content,5,200) as abstract from my_content_t 

  （注：如果位数是负数 如-5 则是从后倒数位数，到字符串结束或截取的长度） 
  4、按关键字截取字符串 
  substring_index（str,delim,count） 
  说明：substring_index（被截取字段，分隔符，关键字出现的次数） 
  

  例：

  select substring_index("www.51coolma.cn",".",2) as abstract from wiki_user 

  结果：www.51coolma
  （注：如果关键字出现的次数是负数 如-2 则是从后倒数，到字符串结束） 
  

  函数简介：

  SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)

  不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。

  
  

  所有的数学函数在发生错误的情况下，均返回 NULL。

  -元减。改变参数的符号

  mysql> SELECT - 2; -> -2

  注意，如果这个操作符被用于一个 BIGINT，返回值也是一个 BIGINT！这就意味着，应该避免在一个可能有值-2^63 的整数上使用 - 操作符！

  ABS(X)返回 X 的绝对值：

  mysql> SELECT ABS(2); -> 2 mysql> SELECT ABS(-32); -> 32

  这个函数可安全地使用于 BIGINT 值。

  SIGN(X)以 -1、0 或 1 方式返回参数的符号，它取决于参数 X 是负数、0 或正数。

  mysql> SELECT SIGN(-32); -> -1 mysql> SELECT SIGN(0); -> 0 mysql> SELECT SIGN(234); -> 1

  MOD(N,M)% 取模 (就如 C 中的 % 操作符)。返回 N 被 M 除后的余数：

  mysql> SELECT MOD(234, 10); -> 4 mysql> SELECT 253 % 7; -> 1 mysql> SELECT MOD(29,9); -> 2 mysql> SELECT 29 MOD 9; -> 2

  这个函数可安全地使用于 BIGINT 值。最后一个示例可在 MySQL 4.1 中工作。

  FLOOR(X)返回不大于 X 的最大整数值：

  mysql> SELECT FLOOR(1.23); -> 1 mysql> SELECT FLOOR(-1.23); -> -2

  注意，返回值被转换为一个 BIGINT！

  CEILING(X)返回不小于 X 的最小整数：

  mysql> SELECT CEILING(1.23); -> 2 mysql> SELECT CEILING(-1.23); -> -1

  注意，返回值被转换为一个 BIGINT！

   

  ROUND(X)

  ROUND(X,D)将参数 X 四舍五入到最近的整数，然后返回。两个参数的形式是将一个数字四舍五入到 D 个小数后返回。

  mysql> SELECT ROUND(-1.23); -> -1 mysql> SELECT ROUND(-1.58); -> -2 mysql> SELECT ROUND(1.58); -> 2 mysql> SELECT ROUND(1.298, 1); -> 1.3 mysql> SELECT ROUND(1.298, 0); -> 1

  注意，当参数在两个整数之间时， ROUND() 的行为取决于 C 库的实现。某些取整到最近的偶数，总是向下取，总是向上取，也可能总是接近于零。如果你需要某种取整类型，应该使用一个明确定义的函数比如 TRUNCATE() 或 FLOOR() 代替。

  DIV整除。类似于 FLOOR()，但是它可安全地用于 BIGINT 值。

  mysql> SELECT 5 DIV 2 -> 2

  DIV 在 MySQL 4.1.0 中新加入。

  EXP(X)返回值 e (自然对数的底) 的 X 次方：

  mysql> SELECT EXP(2); -> 7.389056 mysql> SELECT EXP(-2); -> 0.135335

  LN(X)返回 X 的自然对数：

  mysql> SELECT LN(2); -> 0.693147 mysql> SELECT LN(-2); -> NULL

  这个函数在 MySQL 4.0.3 被新加入。在 MySQL 中，它是 LOG(X) 的同义词。

  LOG(X)

  LOG(B,X)如果以一个参数调用，它返回 X 的自然对数：

  mysql> SELECT LOG(2); -> 0.693147 mysql> SELECT LOG(-2); -> NULL

  如果以两个参数调用，这个函数返回 X 任意底 B 的对数：

  mysql> SELECT LOG(2,65536); -> 16.000000 mysql> SELECT LOG(1,100); -> NULL

  任意底选项在 MySQL 4.0.3 中被加入。LOG(B,X) 等价于 LOG(X)/LOG(B)。

  LOG2(X)返回 X 的以 2 为底的对数：

  mysql> SELECT LOG2(65536); -> 16.000000 mysql> SELECT LOG2(-100); -> NULL

  LOG2() 通常可以用于计数出一个数字需要多少个比特位用于存储它。这个函数在 MySQL 4.0.3 中被添加。在更早的版本中，可以使用 LOG(X)/LOG(2) 来代替它。

  LOG10(X)返回 X 以 10 为底的对数：

  mysql> SELECT LOG10(2); -> 0.301030 mysql> SELECT LOG10(100); -> 2.000000 mysql> SELECT LOG10(-100); -> NULL

  POW(X,Y)

  POWER(X,Y)返回 X 的 Y 幂：

  mysql> SELECT POW(2,2); -> 4.000000 mysql> SELECT POW(2,-2); -> 0.250000

  SQRT(X)返回 X 的非否平方根：

  mysql> SELECT SQRT(4); -> 2.000000 mysql> SELECT SQRT(20); -> 4.472136

  PI()返回 PI 值(圆周率)。缺少显示 5 位小数，但是在 MySQL 内部，为 PI 使用全部的双精度。

  mysql> SELECT PI(); -> 3.141593 mysql> SELECT PI()+0.000000000000000000; -> 3.141592653589793116

  COS(X)返回 X 的余弦，在这里，X 以弧度给出：

  mysql> SELECT COS(PI()); -> -1.000000

  SIN(X)返回 X 的正弦，在这里，X 以弧度给出：

  mysql> SELECT SIN(PI()); -> 0.000000

  TAN(X)返回 X 的正切，在这里，X 以弧度给出：

  mysql> SELECT TAN(PI()+1); -> 1.557408

  ACOS(X)返回 X 的反余弦，更确切地说，返回余弦值为 X 的值。如果 X 不在 -1 到 1 之间的范围内，返回 NULL：

  mysql> SELECT ACOS(1); -> 0.000000 mysql> SELECT ACOS(1.0001); -> NULL mysql> SELECT ACOS(0); -> 1.570796

  ASIN(X)返回 X 的反正弦，更确切地说，返回正弦值为 X 的值。如果 X 不在 -1 到 1 之间的范围内，返回 NULL：

  mysql> SELECT ASIN(0.2); -> 0.201358 mysql> SELECT ASIN('foo'); -> 0.000000

  ATAN(X)返回 X 的反正切，更确切地说，返回正切值为 X 的值：

  mysql> SELECT ATAN(2); -> 1.107149 mysql> SELECT ATAN(-2); -> -1.107149

  ATAN(Y,X)

  ATAN2(Y,X)返回两个变量 X 和 Y 的反正切。它类似于计算 Y / X 的反正切，除了两个参数的符号用于决定结果的象限：

  mysql> SELECT ATAN(-2,2); -> -0.785398 mysql> SELECT ATAN2(PI(),0); -> 1.570796

  COT(X)返回 X 的余切：

  mysql> SELECT COT(12); -> -1.57267341 mysql> SELECT COT(0); -> NULL

  RAND()

  RAND(N)返回一个范围在 0 到 1.0 之间的随机浮点值。如果一个整数参数 N 被指定，它被当做种子值使用(用于产生一个可重复的数值)：

  mysql> SELECT RAND(); -> 0.9233482386203 mysql> SELECT RAND(20); -> 0.15888261251047 mysql> SELECT RAND(20); -> 0.15888261251047 mysql> SELECT RAND(); -> 0.63553050033332 mysql> SELECT RAND(); -> 0.70100469486881

  在一个 ORDER BY 子句中，不可以使用 RAND() 值使用一个列，因为 ORDER BY 将多次重复计算列。从 MySQL 3.23 开始，你可以使用：SELECT * FROM table_name ORDER BY RAND()，这有利于得到一个来自 SELECT * FROM table1,table2 WHERE a=b AND c<d ORDER BY RAND() LIMIT 1000 的集合中的随机样本。 注意，在一个 WHERE 子句中的 RAND() 将在每次 WHERE 执行时被重新计算。 RAND() 并不是预期完美的随机数发生器，但是可以代替做为产生特别的随机数一个快速的方法，这样便于在两个不同平台下的同一 MySQL 版本间移动。

  LEAST(X,Y,...)

  有两个或更多个参数，返回最小(最小值)的参数。参数使用下列规则进行比较：

  • 如果返回值用于一个 INTEGER 语境，或所有的参数是整数值，它们作为整数比较。
  • 如果返回值用于一个 REAL 语境，或所有的参数均是实数值，它们作为实数被比较。
  • 如果任何一个参数是字母大小写敏感的，参数作为大小写敏感的字符串进行比较。
  • 在其它情况下，参数做为忽略大小写的字符中进行比较：

  mysql> SELECT LEAST(2,0); -> 0 mysql> SELECT LEAST(34.0,3.0,5.0,767.0); -> 3.0 mysql> SELECT LEAST("B","A","C"); -> "A"

  在早于 MySQL 3.22.5 的版本中，你可以使用 MIN() 代替 LEAST。

  GREATEST(X,Y,...)返回最大(最大值)参数。参数使用与 LEAST 一致的规则进行比较：

  mysql> SELECT GREATEST(2,0); -> 2 mysql> SELECT GREATEST(34.0,3.0,5.0,767.0); -> 767.0 mysql> SELECT GREATEST("B","A","C"); -> "C"

  在早于 MySQL 3.22.5 的版本中，可以使用 MAX() 代替 GREATEST。

  DEGREES(X)将参数 X 从弧度转换为角度，然后返回：

  mysql> SELECT DEGREES(PI()); -> 180.000000

  RADIANS(X)将参数 X 从角度转换为弧度，然后返回：

  mysql> SELECT RADIANS(90); -> 1.570796

  TRUNCATE(X,D)将数值 X 截到 D 个小数，然后返回。如果 D 为 0，结果将不包含小数点和小数部分：

  mysql> SELECT TRUNCATE(1.223,1); -> 1.2 mysql> SELECT TRUNCATE(1.999,1); -> 1.9 mysql> SELECT TRUNCATE(1.999,0); -> 1 mysql> SELECT TRUNCATE(-1.999,1); -> -1.9

  从 MySQL 3.23.51 开始，所有数字被四舍五入到零。 如果 D 是负数，那么数字的整个部分被对准零位输出：

  mysql> SELECT TRUNCATE(122,-2); -> 100

  注意，十进值小数在计算机中通常不以精确数字存储，而是双精度型的值，你可能会被下列结果所愚弄：

  mysql> SELECT TRUNCATE(10.28*100,0); -> 1027

  上面结果的发生是因为 10.28 实际上是以某些像 10.2799999999999999 的形式被存储的。

  
  

  截取字符串

  用法：substr(string string,num start,num length);

  select  substr(参数1，参数2，参数3)  from  表名
  

  
  

  string为字符串；start为起始位置；length为长度。

  注意：mysql中的start是从1开始的。

  例子:(查出kename字段中第一次出现.之前的字符串)

  select kename,substr(kename,1,locate('.',kename)) as subkename from  web_dev_api where 1;

  
  

  
  

  截取前两位字符
  

  
  

  
  

  对于开发人员来说，mysql管理工具可以说是必不可缺的，它能帮助开发人员在许多方面支持包括PostgreSQL、MySQL、Redis、MongoDB等在内的多种数据库；同时还能提供包括触发器、事件、视图、数据备份、对象结构等多种功能。
  

  本文帮大家整理了15款优秀的MySQL管理工具和应用软件，大家可以从中选择一个，从而帮助你轻松快速完成工作。

  1. Induction

  
  

  Induction是一款用于理解数据关系的开源管理工具，它可用来探索行/列，运行查询和数据可视化等方面。该工具支持PostgreSQL、MySQL、SQLite、Redis及MongoDB等数据库。此外，Induction还可以通过编写添加其他新的适配器。

  2. Pinba

  
  

  Pinba 是一种MySQL存储引擎，其功能在于PHP实时监控和数据服务器的MySQL只读接口。它会对通过UDP发送的数据进行整理和处理整，并将多个PHP进程通过可读的简单报告的形式加以统计显示。为了获取下一代更为复杂的报告和统计数据，Pinba提供了原始数据的只读接口。

  
  

  3. DB Ninja

  
  

  DbNinja是款基于Web的MySQL数据库管理与开发应用程序，非常适合用来远程访问托管服务器。DbNinja支持包括触发器、事件、视图、存储过程和外键等最新功能。此外,它还可以导入和备份数据、MySQL对象结构以及管理用户等。DbNinj的用户界面不仅美观而且功能很齐全，可在任何浏览器及操作系统中安全运行。

  
  

  4. DB Tools Manager

  
  

  DBManager是一款功能强大的数据管理应用程序，它支持MySQL、PostgreSQL、Interbase/Firebird、SQLite,DBF表、MSAccess,MSSQL服务器,Sybase,Oracle和ODBC数据库引擎等一些新特性。目前，DBManager有个人和企业两种版本，用户可以自行选择。

  
  

  5. Dbeaver

  
  

  DBeaver是一款数据库管理应用程序，可运用于多种不同的引擎，包括MySQL，MSSQL，Oracle、SQLite、Sybase和Firebird等等，而且最关键的是它是免费的。DBeaver由Java编写而成，适用于当下所有主流操作系统。它能处理的任务很多，包括元数据编辑(表、列、键、索引)、自定义SQL执行、用户管理、多连接等。

  
  

  6. SqlWave

  
  

  SQLWave是一种简单易用的MySQL客户端，通过该工具用户可以轻松地连接到远程主机。SqlWave支持所有MySQL的最新版本，用它可以管理数据库结构的所有最新功能，包括工作表、视图、存储过程、函数、事件、外键和触发器等。

  
  

  7. MyWebSQL

  
  

  MyWebSQL主要用于管理基于Web的MySQL数据库。使用该工具，用户无需切换网页即可完成一些简单的操作。如果您正在操作桌面，只用登数据库，就可以管理您的数据库了。

  
  

  8. Navicat

  
  

  Navicat是MySQL和MariaDB数据库管理与开发理想的解决方案。它可以实现在一个应用程序上同时连接MySQL和MariaDB数据库，这为数据库提供了一个直观而强大的图形界面管理、开发和维护功能，是非常适合初级MySQL和MariaDB开发人员和专业开发人员的开发工具。

  
  

  9. SQLyog

  
  

  SQLyog作为MySQL管理工具的功能非常强大，它结合了MySQL工作台、php MyAdmin和其他MySQL前端及MySQL GUI工具的特点。该款应用程序可以同时连接任意数量级的MySQL服务器，用于测试和生产。所有流程仅需登录MySQL root以收集数据，用户无需安装在MySQL服务器上。

  
  

  10. Sequel Pro

  
  

  Sequel Pro是基于MySQL数据库的一种快速易用的Mac数据库管理应用程序。用户可通过Sequel Pro在本地和远程服务器上直接访问MySQL数据库。不过，Sequel Pro的最新版本开始添加全屏支持内置。

  
  

  11. HeidiSQL

  
  

  HeidiSQL是一种专为web开发人员设计的有用且可靠的工具，可用于当前最受欢迎的MySQL服务器，微软SQL数据库和PostgreSQL。该工具可提供浏览和编辑数据、创建和编辑表格、视图、过程、触发器和日志安排等事件。此外，您还可以导出结构和数据至SQL文件、剪贴板或其他服务器。

  
  

  12. MyDB Studio

  
  

  MyDB Studio是一款免费的MySQL数据库管理器应用程序。该工具强大到您几乎可以获取到任何想要的功能，并能够连接到无限量级的数据库。通过创建、编辑或删除数据库、表格和记录，就可以备份/恢复并导出为多个格式。

  
  

  13. SQL Lite Manger

  
  

  SQL Lite Manager是一款基于web的开源应用程序，用于管理无服务器、零配置SQL Lite数据库。该程序是用PHP写成，除了记录和应用表格格上的每一步操作，还可以控制多个数据库。SQL Lite Manager可用于查询数据，将MySQL查询转化为兼容SQL Lite数据库，并能创建和编辑触发器。SQL Lite Manager有多种皮肤选项，是一个含现成语言文件的多语言资源。

  
  

  14. Database Master

  
  

  Database Master是一个现代的、强大的、直观且易用的数据库管理程序。它以一个一致而现代的界面适用于MongoDB、MySQL、PostgreSQL、FireBird、 SQL Lite、MS SQL Server、SQL Azure、Oracle、IBM DB2、IBM Informix、Netezza、Ingres以及EffiProz等数据库。Database Master简化了管理、查询、编辑、可视化、设计和报告数据库系统。用户可以通过ODBC与OleDB连接任何数据库系统，也可以访问MS Access，MS FoxPro Database、Dbase和XML文件。

  
  

  15. Chive

  
  

  Chive由PHP搭建而成，是一款基于web的开源MySQL管理应用程序。此款应用程式有一个内置的编辑器，当运行复杂的查询或易用的界面时，可用于快速浏览。

  以上就是51coolma为大家整理的15款优秀的MySQL管理工具和应用软件，希望大家喜欢。

  
  

  Ubuntu是一个比较流行的Linux操作系统，不仅简单易用，而且和Windows相容性非常好。那么在ubuntu下如何安装mysql数据库呢？
  

  在Ubuntu上安装mysql数据库，一般分为两种方法，分别是使用Ubuntu Software Center或者apt命令来安装，而且过程都相对比较简单。

  
  

  1、使用Ubuntu Software Center安装

  打开Ubuntu Software Center，在右上角的搜索框查询mysql，然后选定MySQL Server，点击安装即可。

  
  

  2、使用apt命令安装

  打开终端执行 ”sudo apt-get install mysql-server“ 即可。

  
  

  MySQL初始配置

  在成功安装mysql后，可以直接使用root账户登录，注意这个账户是默认没有密码的。因此为了数据库的安全，需要第一时间给root用户设置密码。

  mysql> GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY "<password>";

  将以上命令中的<password>替换为你要设定的密码即可。设置密码后，如果再以root用户登录就需要输入密码了，如：

  $ mysql -u rootERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)$ mysql -u root -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or g.Your MySQL connection id is 75Server version: 5.5.34-0ubuntu0.13.10.1 (Ubuntu)Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.mysql> 

  
  

  建立数据库独立用户

  root用户拥有数据库的所有操作权限，因此不能轻易给别人用。在一个MySQL实例中，我们可以创建多个数据库，而这些数据库可能会分属不同的项目，那么每个数据库的操作角色也就不一样。对此，我们可以针对不同的数据库，去指定用户进行访问。

  首先使用root角色创建一个数据库mysql> create database db_web_monitor然后将这个数据库授予一个叫xavier的用户使用mysql> GRANT ALL PRIVILEGES ON db_web_monitor.* TO xavier@localhost IDENTIFIED BY "xavier";

  这样就可以使用xavier用户，密码为xavier在本机登录MySQL操作db_web_monitor数据库了。

  $ mysql -u xavierERROR 1045 (28000): Access denied for user 'xavier'@'localhost' (using password: NO)$ mysql -u xavier -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or g.Your MySQL connection id is 77Server version: 5.5.34-0ubuntu0.13.10.1 (Ubuntu)Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || db_web_monitor     || test               |+--------------------+3 rows in set (0.00 sec)mysql> 

  开放远程登录权限

  1. 首先修改MySQL的配置文件，允许监听远程登录。

  $ sudo vi /etc/mysql/my.cnf找到bind-address所在行 45 # Instead of skip-networking the default is now to listen only on 46 # localhost which is more compatible and is not less secure. 47 bind-address        = 127.0.0.1将 bind-address值修改为本机IP即可。注意注释说明，如果是较老版本的MySQL，此处就应该是skip-networking，直接将其注释即可。

  2. 授予用户远程登录权限。

  mysql>GRANT ALL PRIVILEGES ON db_web_monitor.* TO xavier@"%" IDENTIFIED BY "xavier";

  如此这般，xavier用户就可以在任意主机通过IP访问到本机MySQL，对db_web_monitor数据库进行操作了

  
  

  推荐阅读：

  Ubuntu官方帮助文档
  

  MySQL教程

  
  

  在windows系统中，mysql修改密码的方法还是比较多的。本文就为大家介绍四种MySQL修改root密码的方法。

  
  

  方法一：用SET PASSWORD命令修改

  首先登陆mysql，一般命令格式为：mysql> set password for 用户名@localhost = password('新密码');

  例子：

  mysql> set password for root@localhost = password('123'); 

  
  

  方法二：用mysqladmin修改

  格式：mysqladmin -u用户名 -p旧密码 password 新密码

  例子：

  mysqladmin -uroot -p123456 password 123 

  这个命令就是将uroot这个用户的密码由123456改成了123

  
  

  方法三：用UPDATE直接编辑user表

  首先登陆mysql

  mysql> use mysql; mysql> update user set password=password('123') where user='root' and host='localhost'; mysql> flush privileges; 

  以上的代码是将root的密码更改为123

  
  

  以上三种方法都是在记得旧密码的情况下，如果用户忘记了密码，那么该怎么办呢？

  
  

  方法四：忘记旧密码的修改方法

  1. 关闭正在运行的MySQL服务。

  2. 打开DOS窗口，转到mysqlin目录。

  3. 输入mysqld--skip-grant-tables回车。--skip-grant-tables这个指令是让用户再次启动MySQL服务的时候，可以直接跳过权限表认证。

  4. 再开一个DOS窗口（因为刚才那个DOS窗口已经不能动了），然后转到mysqlin目录。

  5. 输入mysql回车，如果成功，将出现MySQL提示符 >。

  6. 连接权限数据库： use mysql; 。

  6. 改密码：update user set password=password("123") where user="root";（别忘了最后加分号） 。

  7. 刷新权限（必须步骤）：flush privileges;　。

  8. 退出 quit。

  9. 注销系统，再进入，使用用户名root和刚才设置的新密码123登录。

  
  

  以上就是51coolma为大家带来的mysql数据库修改密码的方法，希望对大家有所帮助。

  
  

  mysql front一种小巧的管理Mysql的应用工具，那么这个工具该如何安装和使用呢？一起来看看吧。
  

  
  

  一，mysql前端下载：

  mysql front下载地址：点击下载

  
  

  二，mysql前端安装

  1，点击下载的MySQL-Front_Setup.exe程序，开始安装，出现以下界面：

  
  

  2，点击“下一步”，出现以下界面：

  
  

  3，点击“浏览”，选择安装目录，这个目录可以根据自己的需要随便设置。这里的示例选择的安装目录为：D： mysql-front MySQL-front-setup MySQL-Front，即出现以下界面：

  
  

  4，然后一直点击下一步，知道出现下面的页面。

  
  

  5，点击安装即可，安装需要一些时间，安装成功后会出现以下页面，点击完成即可。

  
  

  
  

  三，mysql前端使用

  运行mysql front后会发现全是英文，这时我们可以在系统设置里设置语言，选择简体中文版，如下图：

  
  

  设置好语言后，打开登陆信息界面，输入MYSQL数据库服务器相关参数：

  
  

  输入MYSQL服务器的IP地址，例如：202.101.98.55，如下图：

  
  

  输入MYSQL服务器的用户名和密码，以及MYSQL的数据库名称，点击确定后，选择“打开”，如下图：

  
  

  如果输入的MYSQL数据库的IP，用户名，密码，数据库都正确后，就可以打开服务器了，然后通过“视图”->“ SQL编辑器”将MYSQL的查询语句复制到SQL编辑器中，便可以迅速的将MYSQL数据库引入了。

  
  

  MYSQL管理器的“对象浏览器”，“数据浏览器”对于建立和修改数据库表的结构及数据都是非常方便的。

  
  

  当数据库的数据量很大时，一次性查询结果就会变得很慢，为了提高查询效率，我们可以使用MySQL的分页查询功能。本文就为大家带来MySQL分页查询方法及优化。
  

  
  

  推荐阅读：

  21分钟MySQL入门教程

  MySQL完整教程

  
  

  分页查询方法：
  

  在MySQL中，分页查询一般都是使用limit子句实现，limit子句声明如下：

  SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

  LIMIT子句可以被用于指定 SELECT 语句返回的记录数。需注意以下几点：

  1、第一个参数指定第一个返回记录行的偏移量

  2、第二个参数指定返回记录行的最大数目

  3、如果只给定一个参数：它表示返回最大的记录行数目

  4、第二个参数为 -1 表示检索从某一个偏移量到记录集的结束所有的记录行

  5、初始记录行的偏移量是0(而不是 1)

  
  

  下面是一个应用实例：

  select * from orders_history where type=8 limit 1000,10;

  该条语句将会从表 orders_history 中查询第1000条数据之后的10条数据，也就是第1001条到第1010条数据。

  数据表中的记录默认使用主键（一般为id）排序，上面的结果相当于：

  select * from orders_history where type=8 order by id limit 10000,10;

  三次查询时间分别为：

  3040 ms

  3063 ms

  3018 ms

  针对这种查询方式，下面测试查询记录量对时间的影响：

  select * from orders_history where type=8 limit 10000,1;select * from orders_history where type=8 limit 10000,10;select * from orders_history where type=8 limit 10000,100;select * from orders_history where type=8 limit 10000,1000;select * from orders_history where type=8 limit 10000,10000;

  三次查询时间如下：

  查询1条记录：3072ms 3092ms 3002ms

  查询10条记录：3081ms 3077ms 3032ms

  查询100条记录：3118ms 3200ms 3128ms

  查询1000条记录：3412ms 3468ms 3394ms

  查询10000条记录：3749ms 3802ms 3696ms

  另外我还做了十来次查询，从查询时间来看，基本可以确定，在查询记录量低于100时，查询时间基本没有差距，随着查询记录量越来越大，所花费的时间也会越来越多。

  
  

  针对查询偏移量的测试：

  select * from orders_history where type=8 limit 100,100;select * from orders_history where type=8 limit 1000,100;select * from orders_history where type=8 limit 10000,100;select * from orders_history where type=8 limit 100000,100;select * from orders_history where type=8 limit 1000000,100;

  三次查询时间如下：

  查询100偏移：25ms 24ms 24ms

  查询1000偏移：78ms 76ms 77ms

  查询10000偏移：3092ms 3212ms 3128ms

  查询100000偏移：3878ms 3812ms 3798ms

  查询1000000偏移：14608ms 14062ms 14700ms

  随着查询偏移的增大，尤其查询偏移大于10万以后，查询时间急剧增加。

  这种分页查询方式会从数据库第一条记录开始扫描，所以越往后，查询速度越慢，而且查询的数据越多，也会拖慢总查询速度。

  
  

  使用子查询优化

  这种方式先定位偏移位置的 id，然后往后查询，这种方式适用于 id 递增的情况。

  select * from orders_history where type=8 limit 100000,1;select id from orders_history where type=8 limit 100000,1;select * from orders_history where type=8 and id>=(select id from orders_history where type=8 limit 100000,1) limit 100;select * from orders_history where type=8 limit 100000,100;

  4条语句的查询时间如下：

  第1条语句：3674ms

  第2条语句：1315ms

  第3条语句：1327ms

  第4条语句：3710ms

  针对上面的查询需要注意：

  1、比较第1条语句和第2条语句：使用 select id 代替 select * 速度增加了3倍

  2、比较第2条语句和第3条语句：速度相差几十毫秒

  3、比较第3条语句和第4条语句：得益于 select id 速度增加，第3条语句查询速度增加了3倍

  这种方式相较于原始一般的查询方法，将会增快数倍。

  
  

  使用 id 限定优化

  这种方式假设数据表的id是连续递增的，则我们根据查询的页数和查询的记录数可以算出查询的id的范围，可以使用 id between and 来查询：

  select * from orders_history where type=2 and id between 1000000 and 1000100 limit 100;

  查询时间：15ms 12ms 9ms

  这种查询方式能够极大地优化查询速度，基本能够在几十毫秒之内完成。限制是只能使用于明确知道id的情况，不过一般建立表的时候，都会添加基本的id字段，这为分页查询带来很多便利。

  还可以有另外一种写法：

  select * from orders_history where id >= 1000001 limit 100;

  当然还可以使用 in 的方式来进行查询，这种方式经常用在多表关联的时候进行查询，使用其他表查询的id集合，来进行查询：

  select * from orders_history where id in(select order_id from trade_2 where goods = 'pen')limit 100;

  这种 in 查询的方式要注意：某些 mysql 版本不支持在 in 子句中使用 limit。

  
  

  关于数据表的id说明

  一般情况下，在数据库中建立表的时候，每一张表强制添加 id 递增字段，这样更方便我们查询数据。

  如果数据量很大，比如像订单这类，一般会推荐进行分库分表。这个时候 id 就不建议作为唯一标识了，而应该使用分布式的高并发唯一 id 生成器来生成，并在数据表中使用另外的字段来存储这个唯一标识。

  首先使用范围查询定位 id （或者索引），然后再使用索引进行定位数据，即先 select id，然后在 select *；这样查询的速度将会提升好几倍。

  
  

  原文地址：http://uusama.com/458.html

  
  

  在mysql查询中，经常会用到通配符，而且mysql的通配符和pgsql是有所不同的，甚至mysql中还可以使用正则表达式。本文就为大家带来mysql查询中通配符的使用。
  

  
  

  SQL模式匹配：

  “_” 匹配单个字符,”\_” 匹配”_”

  “%” 匹配任意个字符,包括零个字符

  sql模式下的匹配，缺省是对于字母的大小写没有要求，并且sql模式下，“=”或”!=”是不能在模糊匹配中使用的，而是使用 like 或 not like.

  例如：

  SELECT * FROM ［user］ WHERE u_name LIKE ‘%三%’;SELECT * FROM ［user］ WHERE u_name LIKE ‘_三_’;

  
  

  正则模式匹配：

  当使用正则匹配时，使用REGEXP和NOT REGEXP操作符（或RLIKE和NOT RLIKE，功能是一样的）。

  其中涉及到的字符是：

  “.” 匹配任何单个的字符。

  “[…]” 表示匹配在方括号内的任何字符。如，”[abc]” 则匹配”a”、”b”或者”c”，“［a-z］”匹配任何小写字母，而“［0-9］”匹配任何数字。

  “ * ”表示匹配零个或多个在它前面的东西。例如，“x*”匹配任何数量的“x”字符，“［0-9］*”匹配的任何数量的数字，而“.*”匹配任何数量的任何东西。

  注意：正则表达式是区分大小写的，但是我们也能使用一个字符类匹配两种写法。例如，“［aA］”匹配小写或大写的“a”而“［a-zA-Z］”匹配两种写法的任何字母。

  为了定位一个模式以便它必须匹配被测试值的开始或结尾，在模式开始处使用“^”或在模式的结尾用“$”。

  例如：

  — 查寻以 三 开头的名字

  FROM ［user］ WHERE u_name REGEXP ‘^三’;

  — 查寻以 三 结尾的名字

  FROM ［user］ WHERE u_name REGEXP ‘三$’;

  — “重复n次”操作符重写先前的查询：

  FROM ［user］ WHERE u_name REGEXP ‘b{2}$’;

  推荐阅读：

  MySQL正则表达式

  MySQL 入门教程

  
  

  原文地址：26点的博客 » mysql查询中通配符的使用

  
  

  在MySQL中，只支持Union(并集)集合运算，而对于交集Intersect和差集Except并不支持。那么如何才能在MySQL中实现交集和差集呢？
  

  
  

  一般在MySQL中，我们可以通过in和not in来间接实现交集和差集，当然也有一定局限性，面对少量数据还可以，但数据量大了效率就会变得很低。

  创建table1

  /*DDL 信息*/------------    CREATE TABLE `t1` (    `id` int(11) NOT NULL,    `name` varchar(20) DEFAULT NULL,    `age` int(11) DEFAULT NULL,    PRIMARY KEY (`id`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8  

  创建table2

  /*DDL 信息*/------------    CREATE TABLE `t2` (    `id` int(11) NOT NULL,    `name` varchar(20) DEFAULT NULL,    `age` int(11) DEFAULT NULL,    PRIMARY KEY (`id`)  ) ENGINE=InnoDB DEFAULT CHARSET=utf8  

  插入

  INSERT INTO t1 VALUES(1,'小王',10);  INSERT INTO t1 VALUES(2,'小宋',20);  INSERT INTO t1 VALUES(3,'小白',30);  INSERT INTO t1 VALUES(4,'hello',40);      INSERT INTO t2 VALUES(1,'小王',10);  INSERT INTO t2 VALUES(2,'小宋',22);  INSERT INTO t2 VALUES(3,'小肖',31);  INSERT INTO t2 VALUES(4,'hello',40);  

  SELECT t1.* FROM t1     id  name    age  1   小王      10  2   小宋      20  3   小白      30  4   hello   40  

  SELECT t2.* FROM t2     id  name    age  1   小王  10  2   小宋  22  3   小肖  31  4   hello   40  

  使用not in 求差集，但效率低

  SELECT t1.* FROM t1   WHERE   name NOT IN  (SELECT name FROM t2)    id  name    age  3   小白      30  

  SELECT t1.id, t1.name, t1.age  FROM t1   LEFT JOIN t2   ON t1.id = t2.id  WHERE t1.name != t2.name       OR t1.age != t2.age;      id  name    age  2   小宋      20  3   小白      30  

  求交集，此时只有id name age 所有都一样才是符合要求的。

  SELECT  id,  NAME,  age, COUNT(*)      FROM (SELECT id, NAME, age          FROM t1                    UNION ALL                    SELECT id, NAME, age          FROM t2          ) a      GROUP BY id, NAME, age      HAVING COUNT(*) > 1            id  NAME    age COUNT(*)      1   小王      10  2      4   hello   40  2  

  
  

  union all和union的区别

  UNION和UNION ALL的功能都是将两个结果集合并为一个，但是这两个关键字不管从使用还是效率上来说，都是有一定区别的。

  使用上：

  1、对重复结果的处理：UNION在进行表链接后会筛选掉重复的记录，而Union All则不会去除重复记录。

  2、对排序的处理：Union将会按照字段的顺序进行排序；UNION ALL只是将两个结果合并后就返回，并不会进行排序处理。

  效率上：

  从效率上说，UNION ALL的处理效率要比UNION高很多，所以，如果可以确认合并的两个结果集中，且不包含重复数据和不需要进行排序的话，推荐使用UNION ALL。

  
  

  相关阅读：

  不同场景下 MySQL 的迁移方案

  MySQL FAQ系列整理

  
  

  原文地址：https://blog.csdn.net/mine_song/article/details/70184072

  
  

  postgresql和mysql都是免费且功能强大的开源数据库，很多用户面对这两个库都会有一个问题，那就是哪一个才是最好的开源数据库，MySQL还是PostgreSQL呢？该选择哪一个开源数据库呢？
  

  
  

  
  

  
  

  一.PostgreSQL相对于MySQL的优势

  1、在SQL的标准实现上要比MySQL完善，而且功能实现比较严谨；

  2、存储过程的功能支持要比MySQL好，具备本地缓存执行计划的能力；

  3、对表连接支持较完整，优化器的功能较完整，支持的索引类型很多，复杂查询能力较强；

  4、postgresql主表采用堆表存放，MySQL采用索引组织表，能够支持比MySQL更大的数据量。

  5、postgresql的主备复制属于物理复制，相对于MySQL基于binlog的逻辑复制，数据的一致性更加可靠，复制性能更高，对主机性能的影响也更小。

  6、MySQL的存储引擎插件化机制，存在锁机制复杂影响并发的问题，而postgresql不存在。

  
  

  二、MySQL相对于PostgreSQL的优势：

  1、innodb的基于回滚段实现的MVCC机制，相对PG新老数据一起存放的基于XID的MVCC机制，是占优的。新老数据一起存放，需要定时触发VACUUM，会带来多余的IO和数据库对象加锁开销，引起数据库整体的并发能力下降。而且VACUUM清理不及时，还可能会引发数据膨胀；

  2、MySQL采用索引组织表，这种存储方式非常适合基于主键匹配的查询、删改操作，但是对表结构设计存在约束；

  3、MySQL的优化器较简单，系统表、运算符、数据类型的实现都很精简，非常适合简单的查询操作；

  4、MySQL分区表的实现要优于PG的基于继承表的分区实现，主要体现在分区个数达到上千上万后的处理性能差异较大。

  5、MySQL的存储引擎插件化机制，使得它的应用场景更加广泛，比如除了innodb适合事务处理场景外，myisam适合静态数据的查询场景。

  
  

  三、结论

  总的来说，开源数据库都还不是很完善，和这两者相比，商业数据库oracle无论在架构还是功能方面都要完善很多。

  postgresql和mysql相比，postgresql更加适合严格的企业应用场景（比如金融、电信、ERP、CRM），而MySQL则是更加适合业务逻辑相对简单、对数据可靠性要求比较低的互联网场景（比如google、facebook、alibaba）。

  
  

  相关阅读：

  数据库入门级开发教程

  MySQL数据库笔记

  
  

  遇到锁表快速解决办法

  依次执行1-6步，运行第6步生成的语句即可。

  如果特别着急，运行 1 2 6 步 以及第6步生成的kill语句 即可。

  第1步　查看表是否在使用。

  ​show open tables where in_use > 0 ;​如果查询结果为空。则证明表没有在使用。结束。

  mysql>  show open tables where in_use > 0 ;Empty set (0.00 sec)

  如果查询结果不为空，继续后续的步骤。

  mysql>  show open tables where in_use > 0 ;+----------+-------+--------+-------------+| Database | Table | In_use | Name_locked |+----------+-------+--------+-------------+| test     | t     |      1 |           0 |+----------+-------+--------+-------------+1 row in set (0.00 sec)

  第2步　查看数据库当前的进程，看一下有无正在执行的慢SQL记录线程。

  ​show processlist;​show processlist 是显示用户正在运行的线程，需要注意的是，除了 root 用户能看到所有正在运行的线程外，其他用户都只能看到自己正在运行的线程（即与您正在使用的MySQL帐户关联的线程），看不到其它用户正在运行的线程。如果不使用FULL关键字，Info字段中只显示每条语句的前100个字符。

  第3步　当前运行的所有事务

  SELECT * FROM information_schema.INNODB_TRX;

  第4步　当前出现的锁

  SELECT * FROM information_schema.INNODB_LOCKs;

  第5步　锁等待的对应关系

  SELECT * FROM information_schema.INNODB_LOCK_waits;

  看事务表INNODB_TRX，里面是否有正在锁定的事务线程，看看ID是否在show processlist里面的sleep线程中，如果是，就证明这个sleep的线程事务一直没有commit或者rollback而是卡住了，我们需要手动kill掉。

  搜索的结果是在事务表发现了很多任务，这时候最好都kill掉。

  第6步　批量删除事务表中的事务

  这里用的方法是：通过information_schema.processlist表中的连接信息生成需要处理掉的MySQL连接的语句临时文件，然后执行临时文件中生成的指令。

  SELECT concat('KILL ',id,';')FROM information_schema.processlist pINNER JOIN  information_schema.INNODB_TRX xON p.id=x.trx_mysql_thread_idWHERE db='test';

  记得修改对应的数据库名。

  这个语句执行后结果如下：

  mysql>  SELECT concat('KILL ',id,';')  FROM information_schema.processlist p  INNER JOIN  information_schema.INNODB_TRX x  ON p.id=x.trx_mysql_thread_id  WHERE db='test';+------------------------+| concat('KILL ',id,';') |+------------------------+| KILL 42;               || KILL 40;               |+------------------------+2 rows in set (0.00 sec)

  执行结果里的两个kill语句即可解决锁表。

  
  

  在本教程中，您将学习如何从node.js应用程序连接到MySQL数据库服务器。

  有一些选项可以从Node.js应用程序与MySQL进行交互。在本教程中，我们将向您展示如何使用名为mysqljs/mysql的MySQL的node.js驱动程序。

  首先，创建一个用于存储node.js应用程序的文件夹，例如，在F:workspmysql odejs odejs-connect目录，并使用npm init命令创建package.json文件：

  F:workspmysql
  odejs
  odejs-connect> npm init
  Shell

  其次，使用以下命令为MySQL软件包安装node.js：

  F:workspmysql
  odejs
  odejs-connect> npm install mysql
  Shell

  第三步，在nodejs-connect文件夹内创建connect.js，用于存储连接到MySQL数据库服务器的代码。

  我们将使用todoapp数据库进行演示，因此，先运行以下命令在MySQL数据库服务器中创建数据库：

  CREATE DATABASE todoapp DEFAULT CHARSET utf8;
  SQL

  创建数据库后，就可以从Node.js应用程序连接到MySQL数据库了。接下来编写Node.js的相关代码。

  首先，使用以下语句导入mysql模块：

  let mysql = require('mysql');
  Js

  其次，通过调用createConnection()方法并提供MySQL服务器上的详细信息(如主机，用户，密码和数据库)，建立与MySQL数据库的连接，如下所示：

  let connection = mysql.createConnection({    host: 'localhost',    user: 'root',    password: '',    database: 'todoapp'});
  Js

  在本示例中，我们在本地数据库服务品上与todoapp数据库的连接。

  第三步，在连接对象上调用connect()方法连接到MySQL数据库服务器：

  connect()方法接受一个具有err参数的回调函数，如果发生任何错误，它将提供详细的错误。

  connection.connect(function(err) {  if (err) {    return console.error('error: ' + err.message);  }  console.log('Connected to the MySQL server.');});
  Js

  完整的connect.js程序代码如下所示 -

  let mysql = require('mysql');let connection = mysql.createConnection({    host: 'localhost',    user: 'root',    password: '123456',    database: 'todoapp'});connection.connect(function(err) {  if (err) {    return console.error('error: ' + err.message);  }  console.log('Connected to the MySQL server.');});
  Js

  我们来运行并测试一下connect.js程序 -

  F:workspmysql
  odejs
  odejs-connect>node connect.jsopenssl config failed: error:02001003:system library:fopen:No such processConnected to the MySQL server.
  Shell

  如果看到如上所示的“connected to the MySQL server”的消息，那么恭喜，您已经从node.js应用程序成功连接到MySQL数据库服务器。

  假设使用MySQL用户账号的密码有错，并尝试连接到数据，您将收到一条错误消息：

  F:workspmysql
  odejs
  odejs-connect>node connect.jsopenssl config failed: error:02001003:system library:fopen:No such processerror: ER_ACCESS_DENIED_ERROR: Access denied for user 'root'@'localhost' (using password: YES)
  SQL

  请注意，您在connection对象上调用的每个方法都按顺序排队和执行。

  要正常关闭数据库连接，请在connection对象上调用end()方法。

  end()方法确保在数据库连接关闭之前始终执行所有剩余的查询。

  connection.end(function(err) {  if (err) {    return console.log('error:' + err.message);  }  console.log('Close the database connection.');});
  SQL

  要立即强制连接，可以使用destroy()方法。 destroy()方法保证不会再为连接触发回调或事件。

  connection.destroy();
  Js

  请注意，destroy()方法不会像end()方法那样采取任何回调参数。

  到目前为止，整个connect.js文件的完整代码如下 -

  let mysql = require('mysql');let connection = mysql.createConnection({    host: 'localhost',    user: 'root',    password: '123456',    database: 'todoapp'});connection.connect(function(err) {  if (err) {    return console.error('error: ' + err.message);  }  console.log('Connected to the MySQL server.');});//connection.destroy();connection.end(function(err) {  if (err) {    return console.log('error:' + err.message);  }  console.log('Close the database connection.');});

  node.js模块的MySQL驱动程序为您提供了内置的连接池功能 假设您要创建一个具有5个连接的连接池：

  var pool = mysql.createPool({    connectionLimit: 5,    host: 'localhost',    user: 'root',    password: '',     database: 'todoapp'});
  Js

  要从池中获取连接，可以使用getConnection()方法：

  pool.getConnection(function(err, connection) {  // execute query  // ...});
  Js

  要在完成连接后将其连接到池，可以调用connection.release()。 之后，连接将在池中可用，并可以由其他人再次使用。

  pool.getConnection(function(err, connection) {  // execute query  // ...  connection.release();});
  Js

  要关闭连接并将其从池中删除，请使用connection.destroy()方法。 如果下次需要，将在池中创建一个新的连接。

  请注意，连接池中所建立的连接是懒惰的。例如，如果您使用5个连接配置连接池，但是同时仅使用2个连接，则连接池仅创建2个连接。

  要关闭池中的所有连接，请使用pool对象的end()方法，如下所示：

  pool.end(function(err) {  if (err) {    return console.log(err.message);  }  // close all connections});
  SQL

  在本教程中，您已经学会了如何从node.js应用程序连接到MySQL数据库。

  Python 标准数据库接口为 Python DB-API，Python DB-API为开发人员提供了数据库应用编程接口。

  Python 数据库接口支持非常多的数据库，你可以选择适合你项目的数据库：

  • GadFly
  • mSQL
  • MySQL
  • PostgreSQL
  • Microsoft SQL Server 2000
  • Informix
  • Interbase
  • Oracle
  • Sybase

  你可以访问Python数据库接口及API查看详细的支持数据库列表。

  不同的数据库你需要下载不同的DB API模块，例如你需要访问Oracle数据库和Mysql数据，你需要下载Oracle和MySQL数据库模块。

  DB-API 是一个规范. 它定义了一系列必须的对象和数据库存取方式, 以便为各种各样的底层数据库系统和多种多样的数据库接口程序提供一致的访问接口 。

  Python的DB-API，为大多数的数据库实现了接口，使用它连接各数据库后，就可以用相同的方式操作各数据库。

  Python DB-API使用流程：

  • 引入 API 模块。
  • 获取与数据库的连接。
  • 执行SQL语句和存储过程。
  • 关闭数据库连接。

  MySQLdb 是用于Python链接Mysql数据库的接口，它实现了 Python 数据库 API 规范 V2.0，基于 MySQL C API 上建立的。

  如何安装MySQLdb?

  为了用DB-API编写MySQL脚本，必须确保已经安装了MySQL。复制以下代码，并执行：

  #!/usr/bin/python# -*- coding: UTF-8 -*-import MySQLdb

  如果执行后的输出结果如下所示，意味着你没有安装 MySQLdb 模块：

  Traceback (most recent call last):  File "test.py", line 3, in <module>    import MySQLdbImportError: No module named MySQLdb

  安装MySQLdb，请访问 http://sourceforge.net/projects/mysql-python ，(Linux平台可以访问：https://pypi.python.org/pypi/MySQL-python)从这里可选择适合您的平台的安装包，分为预编译的二进制文件和源代码安装包。

  如果您选择二进制文件发行版本的话，安装过程基本安装提示即可完成。如果从源代码进行安装的话，则需要切换到MySQLdb发行版本的顶级目录，并键入下列命令:

  $ gunzip MySQL-python-1.2.2.tar.gz$ tar -xvf MySQL-python-1.2.2.tar$ cd MySQL-python-1.2.2$ python setup.py build$ python setup.py install

  注意：请确保您有root权限来安装上述模块。

  连接数据库前，请先确认以下事项：

  • 您已经创建了数据库 TESTDB.
  • 在TESTDB数据库中您已经创建了表 EMPLOYEE
  • EMPLOYEE表字段为 FIRST_NAME, LAST_NAME, AGE, SEX 和 INCOME。
  • 连接数据库TESTDB使用的用户名为 "testuser" ，密码为 "test123",你可以可以自己设定或者直接使用root用户名及其密码，Mysql数据库用户授权请使用Grant命令。
  • 在你的机子上已经安装了 Python MySQLdb 模块。
  • 如果您对sql语句不熟悉，可以访问我们的 SQL基础教程

  实例：

  以下实例链接Mysql的TESTDB数据库：

  #!/usr/bin/python# -*- coding: UTF-8 -*-import MySQLdb# 打开数据库连接db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )# 使用cursor()方法获取操作游标 cursor = db.cursor()# 使用execute方法执行SQL语句cursor.execute("SELECT VERSION()")# 使用 fetchone() 方法获取一条数据data = cursor.fetchone()print "Database version : %s " % data# 关闭数据库连接db.close()

  执行以上脚本输出结果如下：

  Database version : 5.0.45

  如果数据库连接存在我们可以使用execute()方法来为数据库创建表，如下所示创建表EMPLOYEE：

  #!/usr/bin/python# -*- coding: UTF-8 -*-import MySQLdb# 打开数据库连接db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )# 使用cursor()方法获取操作游标 cursor = db.cursor()# 如果数据表已经存在使用 execute() 方法删除表。cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")# 创建数据表SQL语句sql = """CREATE TABLE EMPLOYEE (         FIRST_NAME  CHAR(20) NOT NULL,         LAST_NAME  CHAR(20),         AGE INT,           SEX CHAR(1),         INCOME FLOAT )"""cursor.execute(sql)# 关闭数据库连接db.close()

  以下实例使用执行 SQL INSERT 语句向表 EMPLOYEE 插入记录：

  #!/usr/bin/python# -*- coding: UTF-8 -*-import MySQLdb# 打开数据库连接db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )# 使用cursor()方法获取操作游标 cursor = db.cursor()# SQL 插入语句sql = """INSERT INTO EMPLOYEE(FIRST_NAME,         LAST_NAME, AGE, SEX, INCOME)         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""try:   # 执行sql语句   cursor.execute(sql)   # 提交到数据库执行   db.commit()except:   # Rollback in case there is any error   db.rollback()# 关闭数据库连接db.close()

  以上例子也可以写成如下形式：

  #!/usr/bin/python# -*- coding: UTF-8 -*-import MySQLdb# 打开数据库连接db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )# 使用cursor()方法获取操作游标 cursor = db.cursor()# SQL 插入语句sql = "INSERT INTO EMPLOYEE(FIRST_NAME,        LAST_NAME, AGE, SEX, INCOME)        VALUES (%s, %s, %s, %s, %s )" %        ('Mac', 'Mohan', 20, 'M', 2000)try:   # 执行sql语句   cursor.execute(sql)   # 提交到数据库执行   db.commit()except:   # 发生错误时回滚   db.rollback()# 关闭数据库连接db.close()

  实例：

  以下代码使用变量向SQL语句中传递参数:

  ..................................user_id = "test123"password = "password"con.execute('insert into Login values(%s, %s)' %              (user_id, password))..................................

  Python查询Mysql使用 fetchone() 方法获取单条数据, 使用fetchall() 方法获取多条数据。

  • fetchone(): 该方法获取下一个查询结果集。结果集是一个对象
  • fetchall():接收全部的返回结果行.
  • rowcount: 这是一个只读属性，并返回执行execute()方法后影响的行数。

  实例：

  查询EMPLOYEE表中salary（工资）字段大于1000的所有数据：

  #!/usr/bin/python# -*- coding: UTF-8 -*-import MySQLdb# 打开数据库连接db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )# 使用cursor()方法获取操作游标 cursor = db.cursor()# SQL 查询语句sql = "SELECT * FROM EMPLOYEE        WHERE INCOME > %s" % (1000)try:   # 执行SQL语句   cursor.execute(sql)   # 获取所有记录列表   results = cursor.fetchall()   for row in results:      fname = row[0]      lname = row[1]      age = row[2]      sex = row[3]      income = row[4]      # 打印结果      print "fname=%s,lname=%s,age=%s,sex=%s,income=%s" %              (fname, lname, age, sex, income )except:   print "Error: unable to fecth data"# 关闭数据库连接db.close()

  以上脚本执行结果如下：

  fname=Mac, lname=Mohan, age=20, sex=M, income=2000

  更新操作用于更新数据表的的数据，以下实例将 EMPLOYEE 表中的 SEX 字段为 'M' 的 AGE 字段递增 1：

  #!/usr/bin/python# -*- coding: UTF-8 -*-import MySQLdb# 打开数据库连接db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )# 使用cursor()方法获取操作游标 cursor = db.cursor()# SQL 更新语句sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')try:   # 执行SQL语句   cursor.execute(sql)   # 提交到数据库执行   db.commit()except:   # 发生错误时回滚   db.rollback()# 关闭数据库连接db.close()

  删除操作用于删除数据表中的数据，以下实例演示了删除数据表 EMPLOYEE 中 AGE 大于 20 的所有数据：

  #!/usr/bin/python# -*- coding: UTF-8 -*-import MySQLdb# 打开数据库连接db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )# 使用cursor()方法获取操作游标 cursor = db.cursor()# SQL 删除语句sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)try:   # 执行SQL语句   cursor.execute(sql)   # 提交修改   db.commit()except:   # 发生错误时回滚   db.rollback()# 关闭连接db.close()

  事务机制可以确保数据一致性。

  事务应该具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。

  • 原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。
  • 一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
  • 隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
  • 持久性（durability）。持续性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

  Python DB API 2.0 的事务提供了两个方法 commit 或 rollback。

  实例：

  # SQL删除记录语句sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)try:   # 执行SQL语句   cursor.execute(sql)   # 向数据库提交   db.commit()except:   # 发生错误时回滚   db.rollback()

  对于支持事务的数据库， 在Python数据库编程中，当游标建立之时，就自动开始了一个隐形的数据库事务。

  commit()方法游标的所有更新操作，rollback（）方法回滚当前游标的所有操作。每一个方法都开始了一个新的事务。

  DB API中定义了一些数据库操作的错误及异常，下表列出了这些错误和异常:

  异常	描述
  Warning	当有严重警告时触发，例如插入数据是被截断等等。必须是 StandardError 的子类。
  Error	警告以外所有其他错误类。必须是 StandardError 的子类。
  InterfaceError	当有数据库接口模块本身的错误（而不是数据库的错误）发生时触发。 必须是Error的子类。
  DatabaseError	和数据库有关的错误发生时触发。 必须是Error的子类。
  DataError	当有数据处理时的错误发生时触发，例如：除零错误，数据超范围等等。 必须是DatabaseError的子类。
  OperationalError	指非用户控制的，而是操作数据库时发生的错误。例如：连接意外断开、 数据库名未找到、事务处理失败、内存分配错误等等操作数据库是发生的错误。 必须是DatabaseError的子类。
  IntegrityError	完整性相关的错误，例如外键检查失败等。必须是DatabaseError子类。
  InternalError	数据库的内部错误，例如游标（cursor）失效了、事务同步失败等等。 必须是DatabaseError子类。
  ProgrammingError	程序错误，例如数据表（table）没找到或已存在、SQL语句语法错误、 参数数量错误等等。必须是DatabaseError的子类。
  NotSupportedError	不支持错误，指使用了数据库不支持的函数或API等。例如在连接对象上 使用.rollback()函数，然而数据库并不支持事务或者事务已关闭。 必须是DatabaseError的子类。

  SQL 备忘单为您提供了最常用的 SQL 语句供您参考。

  开始

  连接 MySQL

  mysql -u <user> -pmysql [db_name]mysql -h <host> -P <port> -u <user> -p [db_name]mysql -h <host> -u <user> -p [db_name]

  公共部分

  数据库

  create database db ;	创建数据库
  show databases;	列出数据库
  use db;	切换到数据库
  drop database db;	删除数据库

  数据表

  show tables;	列出当前数据库的表
  show fields from t;	列出表的字段
  desc t;	显示表结构
  show create table t;	显示创建表sql
  truncate table t;	删除表中的所有数据
  drop table t;	删除表

  进程

  show processlist;	列出进程
  kill 进程号;	杀死进程

  其他

  exit 或者 q	退出 MySQL 会话

  备份

  创建备份

  mysqldump -u user -p db_name > db.sql

  导出没有架构的数据库

  mysqldump -u user -p db_name --no-data=true --add-drop-table=false > db.sql

  恢复备份

  mysql -u user -p db_name < db.sql

  MySQL 示例

  管理表

  创建一个包含三列的新表

  CREATE TABLE t (     id INT PRIMARY KEY,     name VARCHAR NOT NULL,     price INT DEFAULT 0);

  从数据库中删除表

  DROP TABLE t ;

  向表中添加一个新列

  ALTER TABLE t ADD column;

  从表中删除列 c

  ALTER TABLE t DROP COLUMN c ;

  添加约束

  ALTER TABLE t ADD constraint;

  删除约束

  ALTER TABLE t DROP constraint;

  将表从 t1 重命名为 t2

  ALTER TABLE t1 RENAME TO t2;

  将列 c1 重命名为 c2

  ALTER TABLE t1 RENAME c1 TO c2 ;

  删除表中的所有数据

  TRUNCATE TABLE t;

  从表中查询数据

  从表中查询 c1、c2 列中的数据

  SELECT c1, c2 FROM t

  查询表中的所有行和列

  SELECT * FROM t

  查询数据并使用条件过滤行

  SELECT c1, c2 FROM tWHERE condition

  从表中查询不同的行

  SELECT DISTINCT c1 FROM tWHERE condition

  按升序或降序对结果集进行排序

  SELECT c1, c2 FROM tORDER BY c1 ASC [DESC]

  跳过行的偏移量并返回接下来的 n 行

  SELECT c1, c2 FROM tORDER BY c1 LIMIT n OFFSET offset

  使用聚合函数对行进行分组

  SELECT c1, aggregate(c2)FROM tGROUP BY c1

  使用 HAVING 子句过滤组

  SELECT c1, aggregate(c2)FROM tGROUP BY c1HAVING condition

  从多个表查询

  内连接 t1 和 t2

  SELECT c1, c2 FROM t1INNER JOIN t2 ON condition

  左连接 t1 和 t1

  SELECT c1, c2 FROM t1LEFT JOIN t2 ON condition

  右连接 t1 和 t2

  SELECT c1, c2 FROM t1RIGHT JOIN t2 ON condition

  执行全外连接

  SELECT c1, c2 FROM t1FULL OUTER JOIN t2 ON condition

  生成表中行的笛卡尔积

  SELECT c1, c2 FROM t1CROSS JOIN t2

  另一种执行交叉连接的方法

  SELECT c1, c2 FROM t1, t2

  使用 INNER JOIN 子句将 t1 连接到自身

  SELECT c1, c2FROM t1 AINNER JOIN t1 B ON condition

  使用 SQL 运算符组合来自两个查询的行

  SELECT c1, c2 FROM t1UNION [ALL]SELECT c1, c2 FROM t2

  返回两个查询的交集

  SELECT c1, c2 FROM t1INTERSECTSELECT c1, c2 FROM t2

  从另一个结果集中减去一个结果集

  SELECT c1, c2 FROM t1MINUSSELECT c1, c2 FROM t2

  使用模式匹配 %, _ 查询行

  SELECT c1, c2 FROM t1WHERE c1 [NOT] LIKE pattern

  查询列表中的行

  SELECT c1, c2 FROM tWHERE c1 [NOT] IN value_list

  查询两个值之间的行

  SELECT c1, c2 FROM tWHERE  c1 BETWEEN low AND high

  检查表中的值是否为 NULL

  SELECT c1, c2 FROM tWHERE  c1 IS [NOT] NULL

  使用 SQL 约束

  将 c1 和 c2 设置为主键

  CREATE TABLE t(    c1 INT, c2 INT, c3 VARCHAR,    PRIMARY KEY (c1,c2));

  将 c2 列设置为外键

  CREATE TABLE t1(    c1 INT PRIMARY KEY,      c2 INT,    FOREIGN KEY (c2) REFERENCES t2(c2));

  使 c1 和 c2 中的值唯一

  CREATE TABLE t(    c1 INT, c1 INT,    UNIQUE(c2,c3));

  确保 c1 > 0 和 c1 >= c2 中的值

  CREATE TABLE t(  c1 INT, c2 INT,  CHECK(c1> 0 AND c1 >= c2));

  在 c2 列中设置值不为 NULL

  CREATE TABLE t(     c1 INT PRIMARY KEY,     c2 VARCHAR NOT NULL);

  修改数据

  在表格中插入一行

  INSERT INTO t(column_list)VALUES(value_list);

  在表中插入多行

  INSERT INTO t(column_list)VALUES (value_list),        (value_list), …;

  将 t2 中的行插入到 t1

  INSERT INTO t1(column_list)SELECT column_listFROM t2;

  为所有行更新 c1 列中的新值

  UPDATE tSET c1 = new_value;

  更新 c1、c2 列中与条件匹配的值

  UPDATE tSET c1 = new_value,         c2 = new_valueWHERE condition;

  删除表中的所有数据

  DELETE FROM t;

  删除表中的行子集

  DELETE FROM tWHERE condition;

  管理视图

  创建一个包含 c1 和 c2 的新视图

  CREATE VIEW v(c1,c2) ASSELECT c1, c2FROM t;

  使用检查选项创建新视图

  CREATE VIEW v(c1,c2) ASSELECT c1, c2FROM t;WITH [CASCADED | LOCAL] CHECK OPTION;

  创建递归视图

  CREATE RECURSIVE VIEW v ASselect-statement -- anchor partUNION [ALL]select-statement; -- recursive part

  创建临时视图

  CREATE TEMPORARY VIEW v ASSELECT c1, c2FROM t;

  删除视图

  DROP VIEW view_name;

  管理触发器

  创建或修改触发器

  CREATE OR MODIFY TRIGGER trigger_nameWHEN EVENTON table_name TRIGGER_TYPEEXECUTE stored_procedure;

  什么时候调用

  BEFORE	在事件发生之前调用
  AFTER	事件发生后调用

  事件

  INSERT	调用 INSERT
  UPDATE	调用更新
  DELETE	调用删除

  触发器类型

  FOR EACH ROW
  FOR EACH STATEMENT

  管理索引

  在 t 表的 c1 和 c2 上创建索引

  CREATE INDEX idx_name ON t(c1,c2);

  在t表的c3、c4上创建唯一索引

  CREATE UNIQUE INDEX idx_nameON t(c3,c4)

  删除索引

  DROP INDEX idx_name;

  MySQL 数据类型

  字符串

  CHAR	字符串 (0 - 255)
  VARCHAR	字符串 (0 - 255)
  TINYTEXT	字符串 (0 - 255)
  TEXT	字符串 (0 - 65535)
  BLOB	字符串 (0 - 65535)
  MEDIUMTEXT	字符串 (0 - 16777215)
  MEDIUMBLOB	字符串 (0 - 16777215)
  LONGTEXT	字符串 (0 - 4294967295)
  LONGBLOB	字符串 (0 - 4294967295)
  ENUM	预设选项之一
  SET	预设选项的选择

  日期时间

  DATE	yyyy-MM-dd
  TIME	hh:mm:ss
  DATETIME	yyyy-MM-dd hh:mm:ss
  TIMESTAMP	yyyy-MM-dd hh:mm:ss
  YEAR	yyyy

  数值

  TINYINT x	整数（-128 到 127）
  SMALLINT x	整数（-32768 到 32767）
  MEDIUMINT x	整数（-8388608 到 8388607）
  INT x	整数（-2147483648 到 2147483647）
  BIGINT x	整数（-9223372036854775808 到 9223372036854775807）
  FLOAT	十进制（精确到 23 位）
  DOUBLE	十进制（24 到 53 位）
  DECIMAL	“DOUBLE”存储为字符串

  MySQL 函数和运算符

  字符串

  • ASCII()
  • BIN()
  • BIT_LENGTH()
  • CHAR()
  • CHARACTER_LENGTH()
  • CHAR_LENGTH()
  • CONCAT()
  • CONCAT_WS()
  • ELT()
  • EXPORT_SET()
  • FIELD()
  • FIND_IN_SET()
  • FORMAT()
  • FROM_BASE64()
  • HEX()
  • INSERT()
  • INSTR()
  • LCASE()
  • LEFT()
  • LENGTH()
  • LIKE
  • LOAD_FILE()
  • LOCATE()
  • LOWER()
  • LPAD()
  • LTRIM()
  • MAKE_SET()
  • MATCH
  • MID()
  • NOT LIKE
  • NOT REGEXP
  • OCT()
  • OCTET_LENGTH()
  • ORD()
  • POSITION()
  • QUOTE()
  • REGEXP
  • REGEXP_INSTR()
  • REGEXP_LIKE()
  • REGEXP_REPLACE()
  • REGEXP_SUBSTR()
  • REPEAT()
  • REPLACE()
  • REVERSE()
  • RIGHT()
  • RLIKE
  • RPAD()
  • RTRIM()
  • SOUNDEX()
  • SOUNDS LIKE
  • SPACE()
  • STRCMP()
  • SUBSTR()
  • SUBSTRING()
  • SUBSTRING_INDEX()
  • TO_BASE64()
  • TRIM()
  • UCASE()
  • UNHEX()
  • UPPER()
  • WEIGHT_STRING()

  日期和时间

  • ADDDATE()
  • ADDTIME()
  • CONVERT_TZ()
  • CURDATE()
  • CURRENT_DATE()
  • CURRENT_TIME()
  • CURRENT_TIMESTAMP()
  • CURTIME()
  • DATE()
  • DATE_ADD()
  • DATE_FORMAT()
  • DATE_SUB()
  • DATEDIFF()
  • DAY()
  • DAYNAME()
  • DAYOFMONTH()
  • DAYOFWEEK()
  • DAYOFYEAR()
  • EXTRACT()
  • FROM_DAYS()
  • FROM_UNIXTIME()
  • GET_FORMAT()
  • HOUR()
  • LAST_DAY
  • LOCALTIME()
  • LOCALTIMESTAMP()
  • MAKEDATE()
  • MAKETIME()
  • MICROSECOND()
  • MINUTE()
  • MONTH()
  • MONTHNAME()
  • NOW()
  • PERIOD_ADD()
  • PERIOD_DIFF()
  • QUARTER()
  • SEC_TO_TIME()
  • SECOND()
  • STR_TO_DATE()
  • SUBDATE()
  • SUBTIME()
  • SYSDATE()
  • TIME()
  • TIME_FORMAT()
  • TIME_TO_SEC()
  • TIMEDIFF()
  • TIMESTAMP()
  • TIMESTAMPADD()
  • TIMESTAMPDIFF()
  • TO_DAYS()
  • TO_SECONDS()
  • UNIX_TIMESTAMP()
  • UTC_DATE()
  • UTC_TIME()
  • UTC_TIMESTAMP()
  • WEEK()
  • WEEKDAY()
  • WEEKOFYEAR()
  • YEAR()
  • YEARWEEK()
  • GET_FORMAT()

  数字

  • %, MOD
  • *
  • +
  • -
  • -
  • /
  • ABS()
  • ACOS()
  • ASIN()
  • ATAN()
  • ATAN2(), ATAN()
  • CEIL()
  • CEILING()
  • CONV()
  • COS()
  • COT()
  • CRC32()
  • DEGREES()
  • DIV
  • EXP()
  • FLOOR()
  • LN()
  • LOG()
  • LOG10()
  • LOG2()
  • MOD()
  • PI()
  • POW()
  • POWER()
  • RADIANS()
  • RAND()
  • ROUND()
  • SIGN()
  • SIN()
  • SQRT()
  • TAN()
  • TRUNCATE()

  统计

  • 平均（）
  • BIT_AND()
  • BIT_OR()
  • BIT_XOR()
  • 数数（）
  • 计数（不同）
  • GROUP_CONCAT()
  • JSON_ARRAYAGG()
  • JSON_OBJECTAGG()
  • 最大限度（）
  • 最小值()
  • 性病()
  • 标准差（）
  • STDDEV_POP()
  • STDDEV_SAMP()
  • 和（）
  • VAR_POP()
  • VAR_SAMP()
  • 方差（）

  JSON

  • ->
  • ->>
  • JSON_ARRAY()
  • JSON_ARRAY_APPEND()
  • JSON_ARRAY_INSERT()
  • JSON_CONTAINS()
  • JSON_CONTAINS_PATH()
  • JSON_DEPTH()
  • JSON_EXTRACT()
  • JSON_INSERT()
  • JSON_KEYS()
  • JSON_LENGTH()
  • JSON_MERGE()（已弃用）
  • JSON_MERGE_PATCH()
  • JSON_MERGE_PRESERVE()
  • JSON_OBJECT()
  • JSON_OVERLAPS()（8.0.17 引入）
  • JSON_PRETTY()
  • JSON_QUOTE()
  • JSON_REMOVE()
  • JSON_REPLACE()

  • DROP PROCEDURE IF EXISTS FillDateTable;delimiter //CREATE PROCEDURE FillDateTable()    LANGUAGE SQL    NOT DETERMINISTIC    CONTAINS SQL    SQL SECURITY DEFINER    COMMENT ''BEGIN  drop table if exists datetable;  create table datetable (thedate datetime primary key, isweekday smallint);  SET @x := date('2000-01-01');  REPEAT     insert into datetable (thedate, isweekday) SELECT @x, case when dayofweek(@x) in (1,7) then 0 else 1 end;    SET @x := date_add(@x, interval 1 day);    UNTIL @x >= '2030-12-31' END REPEAT;END//delimiter ;CALL FillDateTable;-- 可用于查询缺失的日期数据

    
    

    select date from (SELECT DATE_FORMAT(DATE_SUB(last_day(curdate()), INTERVAL xc-1 day), '%Y-%m-%d') as dateFROM ( SELECT @xi:=@xi+1 as xc from (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) xc1, (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) xc2,  (SELECT @xi:=0) xc0 ) xcxc) x0 where x0.date >= (select date_add(curdate(),interval-day(curdate())+1 day)); 

    
    

    输出结果：

    "date" 

    "2022-02-28" 

    "2022-02-27" 

    "2022-02-26" 

    "2022-02-25" 

    "2022-02-24" 

    "2022-02-23" 

    "2022-02-22" 

    "2022-02-21" 

    "2022-02-20" 

    "2022-02-19" 

    "2022-02-18" 

    "2022-02-17" 

    "2022-02-16" 

    "2022-02-15"

    "2022-02-14"

    "2022-02-13"

    "2022-02-12"

    "2022-02-11"

    "2022-02-10" 

    "2022-02-09"

    "2022-02-08"

    "2022-02-07"

    "2022-02-06"

    "2022-02-05"

    "2022-02-04"

    "2022-02-03"

    "2022-02-02"

    "2022-02-01"