MySQL 【进阶查询】

占1字节,有符号：-128~127,无符号位:0~255 smallint, # 占2字节,有符号：-32768~32767,无符号位:0~65535 mediumint,# 占3字节,有符号：-8388608~8388607,无符位:0~16777215 int, # 占4字节,有符号：-2147483648~2147483647,无符号位:0~4284967295 bigint,# 占8字节 bool 等价于tinyint(1) 布尔型

• 浮点型

float([m[,d]]) # 占4字节,1.17E-38~3.4E+38
double([m[,d]]) # 占8字节
decimal([m[,d]]) # 以字符串形式表示的浮点数

• 字符型

char([m]): # 固定长度的字符,占用m字节
varchar[(m)]: # 可变长度的字符,占用m+1字节，大于255个字符：占用m+2
tinytext, # 255个字符(2的8次方)
text, # 65535个字符(2的16次方)
mediumtext, # 16777215字符(2的24次方)
longtext, # (2的32次方)
enum(value,value,...) # 占1/2个字节 最多可以有65535个成员
set(value,value,...) # 占1/2/3/4/8个字节，最多可以有64个成员

 

常用select命令

# 使用select命令查看mysql数据库系统信息：

#-- 打印当前的日期和时间
select now();  

#-- 打印当前的日期
select curdate();  

# -- 打印当前的时间
select curtime();  

# -- 打印当前数据库
select database();  

# -- 打印MySQL版本
select version();  

#-- 打印当前用户
select user();  

# --查看系统信息
show variables;  
show global variables;  
show global variables like ‘%version%‘;  
show variables like ‘%storage_engine%‘; #  默认的存储引擎  

# like模糊搜索还可用户where字句，例如
select * from students where stname like ‘%l%1%2%3%‘;  
# 除了like 还有not like
show engines; # 查看支持哪些存储引擎

# --查看系统运行状态信息
show status;  
show global status like ‘Thread%‘;  

 

MySQL 排序

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

语法

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

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

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

案例

• asc从小到大排列，即升序
• desc从大到小排序，即降序

# 查询年龄在18到34岁之间的男性，按照年龄从小到大到排序
# select * from students where (age between 18 and 34) and gender = 1 order by age asc;

# 查询年龄在18到34岁之间的女性，身高从高到矮排序
# select * from students where (age between 18 and 34) and gender = 2 order by height desc;

• order by 多个字段

# 查询年龄在18到34岁之间的女性，身高从高到矮排序, 如果身高相同的情况下按照年龄从小到大排序    
# select * from students where (age between 18 and 34) and gender = 2 order by height desc,age asc;

# 查询年龄在18到34岁之间的女性，身高从高到矮排序, 如果身高相同的情况下按照年龄从小到大排序,如果年龄也相同那么按照id从大到小排序
# select * from students where (age between 18 and 34) and gender = 2 order by height desc,age asc,id desc;

# mysql> select bName,price from books where price  in (50,60,70) order by price asc;  
+--------------------------------------+-------+  
| bName                          | price |  
+--------------------------------------+-------+  
| Illustrator 10完全手册      |    50 |  
| FreeHand 10基础教程      |    50 |  
| 网站设计全程教程             |    50 |  
| ASP数据库系统开发实例导航  |    60 |  
| Delphi 5程序设计与控件参考  |    60 |  
| ASP数据库系统开发实例导航   |    60 |  

升序：order by “排序的字段” asc 默认

# mysql> select bName,price from books where price  in (50,60,70) order by price desc;  
+--------------------------------+-----------------+  
| bName                                | price |  
+--------------------------------+-----------------+  
| ASP数据库系统开发实例导航     |    60 |  
| Delphi 5程序设计与控件参考    |    60 |  
| ASP数据库系统开发实例导航    |    60 |  
| Illustrator 10完全手册           |    50 |  
| FreeHand 10基础教程           |    50 |  
| 网站设计全程教程                  |    50 |  

降序：oredr by “排序的字段” desc

select bName,price from books where price  in (50,60,70) order by price desc,bName desc; 

多个字段排序

 

MySQL 分组

MySQL GROUP BY 语句

• GROUP BY 语句根据一个或多个列对结果集进行分组。
• 在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

GROUP BY 语法

# SELECT column_name, function(column_name)
# FROM table_name
# WHERE column_name operator value
# GROUP 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-2215:25:33 |1|
|2   |小王   |2016-04-2015:25:47 |3|
|3   |小丽   |2016-04-1915:26:02 |2|
|4   |小王   |2016-04-0715:26:14 |4|
|5   |小明   |2016-04-1115:26:40 |4|
|6   |小明   |2016-04-0415:26:54 |2|
+----+--------+------------------+--------+
6 rows inset(0.00 sec)

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

# mysql> SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;
+--------+----------+
| name  | COUNT(*)|
+--------+----------+
|小丽　　|　　1|
|小明　　|　　3|
|小王　　|　　2|
+--------+----------+
3 rows inset(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 inset(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 inset(0.01 sec)

 

MySQL 连接查询

• 在前几章节中，我们已经学会了如何在一张表中读取数据，这是相对简单的，但是在真正的应用中经常需要从多个数据表中读取数据。
• 本章节我们将向大家介绍如何使用 MySQL 的 JOIN 在两个或多个表中查询数据。
• 你可以在 SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。

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

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

示例：

• 以一个共同的字段，求两张表当中符合条件的并集。 通过共同字段把这两张表连接起来。

常用的连接：

• 内连接：根据表中的共同字段进行匹配
• 外连接分两种：左外连接、右外链接。

内连接：

• 语法：

# select 字段  from 表1 inner join 表2  on 表1.字段=表2.字段

• 内连接：根据表中的共同字段进行匹配

# 测试
select a.bname,a.price,b.btypename from books a inner join category b on a.btypeid=b.btypeid;  
# 实际使用中inner可省略掉  
# 跟WHERE 子句结果一样  
select a.bname,a.price,b.btypename from books a, category b where a.btypeid=b.btypeid;  

• 外连接 (分为左外连接；右外连接)

            

# 左连接： select 字段 from a表 left join b表 on 连接条件
# a表是主表，都显示。
# b表从表
# 主表内容全都有，从表内没有的显示null。
Select a.bname,a.price,b.btypename from books a left join category b on a.btypeid=b.btypeid; 

# 右连接：select 字段 from a表 right join b表 on 条件
# a表是从表，
# b表主表，都显示。
Select a.bname,b.* from books a right join category b on a.btypeid=b.btypeid;  
# 右连接，可以多表连接  

inner join ... on
select ... from 表A inner join 表B;
select * from students inner join classes;

# 查询 有能够对应班级的学生以及班级信息
select * from students inner join classes on students.cls_id = classes.id;

# 按照要求显示姓名、班级
select students.name,classes.name from students inner join classes on students.cls_id = classes.id;

# 给数据表起名字
select s.name,c.name from students as s inner join classes as c on s.cls_id = c.id;

# 查询 有能够对应班级的学生以及班级信息，显示学生的所有信息 students.*，只显示班级名称 classes.name.
select s.*,c.name from students as s inner join classes as c on s.cls_id = c.id;

# 在以上的查询中，将班级姓名显示在第1列
select c.name,s.* from students as s inner join classes as c on s.cls_id = c.id;

# 查询 有能够对应班级的学生以及班级信息, 按照班级进行排序
select c.xxx s.xxx from students as s inner join clssses as c on .... order by ....;
select c.name,s.* from students as s inner join classes as c on s.cls_id = c.id order by c.name;

# 当时同一个班级的时候，按照学生的id进行从小到大排序
select c.name,s.* from students as s inner join classes as c on s.cls_id = c.id order by c.name,id asc;

# left join
# 查询每位学生对应的班级信息
select * from students left join classes on students.cls_id = classes.id;

# right join
select * from students right join classes on students.cls_id = classes.id;

# 查询没有对应班级信息的学生
# 语句
select ... from xxx as s left join xxx as c on..... where .....
select ... from xxx as s left join xxx as c on..... having .....

# 列
select * from students left join classes on students.cls_id = classes.id where classes.name is null;
#(注意)不建议使用 select * from students left join classes on students.cls_id=classes.id having classes.id is null;
# right join   on
# 将数据表名字互换位置，用left join完成

应用示例

应用示例

 

MySQL NULL 值处理

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

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

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

1. 关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。
2. 在 MySQL 中，NULL 值与任何其它值的比较（即使是 NULL）永远返回 NULL，即 NULL = NULL 返回 NULL 。
3. MySQL 中处理 NULL 使用 IS NULL 和 IS NOT NULL 运算符。

# 注意：
select * , columnName1+ifnull(columnName2,0) from tableName;
# columnName1，columnName2 为 int 型，当 columnName2 中，有值为 null 时，columnName1+columnName2=null， 
ifnull(columnName2,0) 把 columnName2 中 null 值转为 0

案例

•  判空is null

# 查询身高为空的信息
select * from students where height is null;

# 判非空is not null
select * from students where height is not null;

 

MySQL 正则表达式

• 在前面的章节我们已经了解到MySQL可以通过 LIKE ...% 来进行模糊匹配。
• MySQL 同样也支持其他正则表达式的匹配， MySQL中使用 REGEXP 操作符来进行正则表达式匹配。
• 如果您了解PHP或Perl，那么操作起来就非常简单，因为MySQL的正则表达式匹配与这些脚本的类似。
• 下表中的正则模式可应用于 REGEXP 操作符中。

模式	描述
^	匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 ‘\n‘ 或 ‘\r‘ 之后的位置。
$	匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 ‘\n‘ 或 ‘\r‘ 之前的位置。
.	匹配除 "\n" 之外的任何单个字符。要匹配包括 ‘\n‘ 在内的任何字符，请使用象 ‘[.\n]‘ 的模式。
[...]	字符集合。匹配所包含的任意一个字符。例如， ‘[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 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务，详细了解可以看一下这篇【常识与进阶】！

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

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

• 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
• 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
• 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

# 在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。
　因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SETAUTOCOMMIT=0，
  用来禁止使用当前会话的自动提交。

事务控制语句：

• BEGIN 或 START TRANSACTION 显式地开启一个事务；
• COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
• ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
• SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
• RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
• ROLLBACK TO identifier 把事务回滚到标记点；
• SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

MYSQL 事务处理主要有两种方法：

• 用 BEGIN, ROLLBACK, COMMIT来实现
  • BEGIN 开始一个事务
  • ROLLBACK事务回滚
  • COMMIT事务确认
• 直接用 SET 来改变 MySQL 的自动提交模式:
  • SET AUTOCOMMIT=0 禁止自动提交
  • SET AUTOCOMMIT=1开启自动提交

# mysql> use RUNOOB;
Database changed
# mysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb;  # 创建数据表
Query OK, 0 rows affected (0.04 sec)
 
# mysql> select * from runoob_transaction_test;
Empty set (0.01 sec)
 
# mysql> begin;  # 开始事务
Query OK, 0 rows affected (0.00 sec)
 
# mysql> insert into runoob_transaction_test value(5);
Query OK, 1 rows affected (0.01 sec)
 
# mysql> insert into runoob_transaction_test value(6);
Query OK, 1 rows affected (0.00 sec)
 
# mysql> commit; # 提交事务
Query OK, 0 rows affected (0.01 sec)
 
# mysql>  select * from runoob_transaction_test;
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
rows in set (0.01 sec)
 
# mysql> begin;    # 开始事务
Query OK, 0 rows affected (0.00 sec)
 
# mysql>  insert into runoob_transaction_test values(7);
Query OK, 1 rows affected (0.00 sec)
 
# mysql> rollback;   # 回滚
Query OK, 0 rows affected (0.00 sec)
 
# mysql>   select * from runoob_transaction_test;   # 因为回滚所以数据没有插入
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
rows in set (0.01 sec)
 
# mysql>

事物测试

事物测试

<?php
$dbhost = ‘localhost:3306‘;  // mysql服务器主机地址
$dbuser = ‘root‘;            // mysql用户名
$dbpass = ‘123456‘;          // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die(‘连接失败: ‘ . mysqli_error($conn));
}
// 设置编码，防止中文乱码
mysqli_query($conn, "set names utf8");
mysqli_select_db( $conn, ‘RUNOOB‘ );
mysqli_query($conn, "SET AUTOCOMMIT=0"); // 设置为不自动提交，因为MYSQL默认立即执行
mysqli_begin_transaction($conn);            // 开始事务定义
 
if(!mysqli_query($conn, "insert into runoob_transaction_test (id) values(8)"))
{
    mysqli_query($conn, "ROLLBACK");     // 判断当执行失败时回滚
}
 
if(!mysqli_query($conn, "insert into runoob_transaction_test (id) values(9)"))
{
    mysqli_query($conn, "ROLLBACK");      // 判断执行失败时回滚
}
mysqli_commit($conn);            //执行事务
mysqli_close($conn);
?>

PHP中使用事物示例

PHP中使用事务实例

 

MySQL ALTER命令

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

# root@host# mysql -u root -p password;
Enter password:*******
# mysql> use RUNOOB;
Database changed
# mysql> 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 与 MODIFY 子句，所以如果你想重置数据表字段的位置就需要先使用 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;
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

修改字段默认值

# 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    |       |
+-------+---------+------+-----+---------+-------+
rows in set (0.00 sec)

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

# 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    |       |
+-------+---------+------+-----+---------+-------+
rows in set (0.00 sec)
Changing a Table Type:

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

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

# mysql> ALTER TABLE testalter_tbl ENGINE = 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: 0
Max_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是一辆兰博基尼的话，那么没有设计和使用索引的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会自动删除表并释放所有空间。
• 临时表在MySQL 3.23版本中添加，如果你的MySQL版本低于 3.23版本就无法使用MySQL的临时表。不过现在一般很少有再使用这么低版本的MySQL数据库服务了。
• MySQL临时表只在当前连接可见，如果你使用PHP脚本来创建MySQL临时表，那每当PHP脚本执行完成后，该临时表也会自动销毁。
• 如果你使用了其他MySQL客户端程序连接MySQL数据库服务器来创建临时表，那么只有在关闭客户端程序时才会销毁临时表，当然你也可以手动销毁。

示例

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 |
+--------------+-------------+----------------+------------------+
row in set (0.00 sec)

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

• 当你使用 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 |
+--------------+-------------+----------------+------------------+
row in set (0.00 sec)
mysql> DROP TABLE SalesSummary;
mysql>  SELECT * FROM SalesSummary;
ERROR 1146: Table ‘RUNOOB.SalesSummary‘ doesn‘t exist