数据类型介绍

• 整型

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

• 浮点型

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

• 字符型

char([m]): # 固定长度的字符,占用m字节varchar[(m)]: # 可变长度的字符,占用m+1字节，大于255个字符：占用m+2tinytext, # 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 likeshow 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 ... onselect ... 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 joinselect * 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 nullselect * from students where height is not null;

MySQL 正则表达式

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

示例

• 了解以上的正则需求后，我们就可以根据自己的需求来编写带有正则表达式的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: 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是一辆兰博基尼的话，那么没有设计和使用索引的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

以下是手动删除临时表的实例：

MySQL 复制表

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

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

实例

• 尝试以下实例来复制表 runoob_tbl 。

# mysql> SHOW CREATE TABLE runoob_tbl \G;*************************** 1. row *************************** Table: runoob_tblCreate Table: CREATE TABLE `runoob_tbl` ( `runoob_id` int(11) NOT NULL auto_increment, `runoob_title` varchar(100) NOT NULL default ‘‘, `runoob_author` varchar(40) NOT NULL default ‘‘, `submission_date` date default NULL, PRIMARY KEY (`runoob_id`), UNIQUE KEY `AUTHOR_INDEX` (`runoob_author`)) ENGINE=InnoDB row in set (0.00 sec)ERROR:No query specified

步骤一：获取数据表的完整结构

# mysql> CREATE TABLE `clone_tbl` ( -> `runoob_id` int(11) NOT NULL auto_increment, -> `runoob_title` varchar(100) NOT NULL default ‘‘, -> `runoob_author` varchar(40) NOT NULL default ‘‘, -> `submission_date` date default NULL, -> PRIMARY KEY (`runoob_id`), -> UNIQUE KEY `AUTHOR_INDEX` (`runoob_author`)-> ) ENGINE=InnoDB;Query OK, 0 rows affected (1.80 sec)

步骤二：修改SQL语句的数据表名，并执行SQL语句

# 执行完第二步骤后，你将在数据库中创建新的克隆表 clone_tbl。 如果你想拷贝数据表的数据你可以使用 INSERT INTO... SELECT 语句来实现。mysql> INSERT INTO clone_tbl (runoob_id, -> runoob_title, -> runoob_author, -> submission_date) -> SELECT runoob_id,runoob_title, -> runoob_author,submission_date -> FROM runoob_tbl;Query OK, 3 rows affected (0.07 sec)Records: 3 Duplicates: 0 Warnings: 0# 执行以上步骤后，你将完整的复制表，包括表结构及表数据。

步骤三：

MySQL 序列使用

• MySQL 序列是一组整数：1, 2, 3, ...，由于一张数据表只能有一个字段自增主键， 如果你想实现其他字段也实现自动增加，就可以使用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脚本中也提供了相应的函数来获取最后的插入表中的自增列的值。

# 使用 mysql_insertid 属性来获取 AUTO_INCREMENT 的值。 实例如下：$dbh->do ("INSERT INTO insect (name,date,origin)VALUES(‘moth‘,‘2001-09-14‘,‘windowsill‘)");my $seq = $dbh->{mysql_insertid};

PERL实例

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

PHP实例

重置序列

• 如果你删除了数据表中的多条记录，并希望对剩下数据的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, -> PRIMARY KEY (id), -> name VARCHAR(30) NOT NULL, -> date DATE NOT NULL, -> origin VARCHAR(30) NOT NULL)engine=innodb auto_increment=100 charset=utf8;# 或者你也可以在表创建成功后，通过以下语句来实现：# mysql> ALTER TABLE t AUTO_INCREMENT = 100;

MySQL 处理重复数据

• 有些 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));

统计重复数据

• 以下我们将统计表中 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;# 你也可以使用 GROUP BY 来读取数据表中不重复的数据：# mysql> SELECT last_name, first_name -> FROM person_tbl -> GROUP BY (last_name, first_name);

• 删除重复数据

# 如果你想删除数据表中的重复数据，你可以使用以下的SQL语句：# mysql> CREATE TABLE tmp SELECTlast_name, first_name, sex FROM person_tblGROUP BY (last_name, first_name, sex);# 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 导出导入数据

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

# 以下实例中我们将数据表 runoob_tbl 数据导出到 /tmp/runoob.txt 文件中:mysql> SELECT * FROM runoob_tbl -> INTO OUTFILE ‘/tmp/runoob.txt‘;# 你可以通过命令选项来设置数据输出的指定格式，以下实例为导出 CSV 格式：mysql> SELECT * FROM passwd INTO OUTFILE ‘/tmp/runoob.txt‘ -> FIELDS TERMINATED BY ‘,‘ ENCLOSED BY ‘"‘ -> LINES TERMINATED BY ‘\r\n‘;# 在下面的例子中，生成一个文件，各值用逗号隔开。这种格式可以被许多程序使用。SELECT a,b,a+b INTO OUTFILE ‘/tmp/result.text‘FIELDS TERMINATED BY ‘,‘ OPTIONALLY ENCLOSED BY ‘"‘LINES TERMINATED BY ‘\n‘FROM test_table;

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

• LOAD DATA INFILE是SELECT ... INTO OUTFILE的逆操作，SELECT句法。为了将一个数据库的数据写入一个文件，使用SELECT ... INTO OUTFILE，为了将文件读回数据库，使用LOAD DATA INFILE。
• SELECT...INTO OUTFILE ‘file_name‘形式的SELECT可以把被选择的行写入一个文件中。该文件被创建到服务器主机上，因此您必须拥有FILE权限，才能使用此语法。
• 输出不能是一个已存在的文件。防止文件数据被篡改。
• 你需要有一个登陆服务器的账号来检索文件。否则 SELECT ... INTO OUTFILE 不会起任何作用。
• 在UNIX中，该文件被创建后是可读的，权限由MySQL服务器所拥有。这意味着，虽然你就可以读取该文件，但可能无法将其删除。

导出表作为原始数据

• mysqldump 是 mysql 用于转存储数据库的实用程序。它主要产生一个 SQL 脚本，其中包含从头重新创建数据库所必需的命令 CREATE TABLE INSERT 等。
• 使用 mysqldump 导出数据需要使用 --tab 选项来指定导出文件指定的目录，该目标必须是可写的。

#以下实例将数据表 runoob_tbl 导出到 /tmp 目录中：$ mysqldump -u root -p --no-create-info --tab=/tmp RUNOOB runoob_tblpassword ******

导出 SQL 格式的数据

$ mysqldump -u root -p RUNOOB runoob_tbl > dump.txtpassword ******以上命令创建的文件内容如下：-- MySQL dump 8.23---- Host: localhost Database: RUNOOB----------------------------------------------------------- Server version 3.23.58---- Table structure for table `runoob_tbl`--CREATE TABLE runoob_tbl ( runoob_id int(11) NOT NULL auto_increment, runoob_title varchar(100) NOT NULL default ‘‘, runoob_author varchar(40) NOT NULL default ‘‘, submission_date date default NULL, PRIMARY KEY (runoob_id), UNIQUE KEY AUTHOR_INDEX (runoob_author)) TYPE=MyISAM;---- Dumping data for table `runoob_tbl`--INSERT INTO runoob_tbl VALUES (1,‘Learn PHP‘,‘John Poul‘,‘2007-05-24‘);INSERT INTO runoob_tbl VALUES (2,‘Learn MySQL‘,‘Abdul S‘,‘2007-05-24‘);INSERT INTO runoob_tbl VALUES (3,‘JAVA Tutorial‘,‘Sanjay‘,‘2007-05-06‘);# 如果你需要导出整个数据库的数据，可以使用以下命令：$ mysqldump -u root -p RUNOOB > database_dump.txtpassword ******# 如果需要备份所有数据库，可以使用以下命令：$ mysqldump -u root -p --all-databases > database_dump.txtpassword ******--all-databases 选项在 MySQL 3.23.12 及以后版本加入。# 该方法可用于实现数据库的备份策略。

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

# 如果你需要将数据拷贝至其他的 MySQL 服务器上, 你可以mysqldump 命令中指定数据库名及数据表。# 在源主机上执行以下命令，将数据备份至 dump.txt 文件中:$ mysqldump -u root -p database_name table_name > dump.txtpassword *****#如果完整备份数据库，则无需使用特定的表名称。# 如果你需要将备份的数据库导入到MySQL服务器中，可以使用以下命令，使用以下命令你需要确认数据库已经创建：$ mysql -u root -p database_name < dump.txtpassword *****# 你也可以使用以下命令将导出的数据直接导入到远程的服务器上，但请确保两台服务器是相通的，是可以相互访问的：$ mysqldump -u root -p database_name | mysql -h other-host.com database_name# 以上命令中使用了管道来将导出的数据导入到指定的远程主机上。

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

MySQL 导入数据

• mysql 命令导入

# 使用 mysql 命令导入语法格式为：mysql -u用户名 -p密码 < 要导入的数据库数据(runoob.sql)# 实例：# mysql -uroot -p123456 < runoob.sql# 以上命令将将备份的整个数据库 runoob.sql 导入。

• source 命令导入

# source 命令导入数据库需要先登录到数库终端：mysql> create database abc; # 创建数据库mysql> use abc; # 使用已创建的数据库 mysql> set names utf8; # 设置编码mysql> source /home/abc/abc.sql # 导入备份数据库

• 使用 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 ‘\r\n‘;

• 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 mytbl dump.txtpassword *****# mysqlimport 命令可以指定选项来设置指定格式,命令语句格式如下：$ mysqlimport -u root -p --local --fields-terminated-by=":" --lines-terminated-by="\r\n" mytbl dump.txtpassword *****# mysqlimport 语句中使用 --columns 选项来设置列的顺序：$ mysqlimport -u root -p --local --columns=b,c,a mytbl dump.txtpassword *****

• mysqlimport的常用选项介绍

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

# 首先linux 下查看mysql相关目录[root@op-workorder bin]# whereis mysql mysql: /usr/bin/mysql /usr/lib64/mysql /usr/include/mysql# 导出数据库用mysqldump命令cd /home/work/mysql/bin # 先cd到mysql的运行路径下，再执行一下命令　# 1. 导出数据和表结构　　　./mysqldump -uroot -p bsp > bsp.sql　　　mysqldump -u用户名 -p密码 数据库名 > 数据库名.sql# 2. 只导出表结构　　　mysqldump -u用户名 -p密码 -d 数据库名 > 数据库名.sql mysqldump -uroot -p -d dbname > dbname .sql# 3. 导入数据库　　# 1、首先建空数据库　　　　　mysql>create database bsp charset utf8;　　# 2、导入数据库（方法一）　　　　　# 选择数据库　　　　　　mysql>use bsp;　　 # 导入数据（注意sql文件的路径）　　　　　　mysql>source /home/work/project/bsp/bsp.sql　　# 3、导入数据库（方法二）　　　　 mysql -u用户名 -p密码 数据库名 < 数据库名.sql　　# 4、执行导出数据库报错问题解决# 报错：mysqldump: Couldn‘t execute ‘SHOW VARIABLES LIKE ‘gtid\_mode‘‘: Table 
 ‘performance_schema.session_variables‘ doesn‘t exist (1146)1）mysql_upgrade -u root -p --force #更新2）service mysql restart #restart mysql service3）mysqldump -u root -p test > test.sql #重新备份数据

MySQL 函数

• MySQL 有很多内置的函数，以下列出了这些函数的说明。

• MySQL 字符串函数

SELECT ASCII(CustomerName) AS NumCodeOfFirstChar FROM Customers;

SELECT CHAR_LENGTH("RUNOOB") AS LengthOfString;

SELECT CHARACTER_LENGTH("RUNOOB") AS LengthOfString;

SELECT CONCAT("SQL ", "Runoob ", "Gooogle ", "Facebook") AS ConcatenatedString;

SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!")AS ConcatenatedString;

SELECT FIELD("c", "a", "b", "c", "d", "e");

SELECT FIND_IN_SET("c", "a,b,c,d,e");

SELECT FORMAT(250500.5634, 2); -- 输出 250,500.56

SELECT INSERT("google.com", 1, 6, "runnob"); -- 输出：runoob.com

SELECT LOCATE(‘st‘,‘myteststring‘); -- 5

SELECT LOCATE(‘b‘, ‘abc‘) -- 2

SELECT LCASE(‘RUNOOB‘) -- runoob

SELECT LEFT(‘runoob‘,2) -- ru

SELECT LOWER(‘RUNOOB‘) -- runoob

SELECT LPAD(‘abc‘,5,‘xx‘) -- xxabc

SELECT LTRIM(" RUNOOB") AS LeftTrimmedString;-- RUNOOB

SELECT MID("RUNOOB", 2, 3) AS ExtractString; -- UNO

SELECT POSITION(‘b‘ in ‘abc‘) -- 2

SELECT REPEAT(‘runoob‘,3) -- runoobrunoobrunoob

SELECT REPLACE(‘abc‘,‘a‘,‘x‘) --xbc

SELECT REVERSE(‘abc‘) -- cba

SELECT RIGHT(‘runoob‘,2) -- ob

SELECT RPAD(‘abc‘,5,‘xx‘) -- abcxx

SELECT RTRIM("RUNOOB ") AS RightTrimmedString; -- RUNOOB

SELECT SPACE(10);

SELECT STRCMP("runoob", "runoob"); -- 0

SELECT SUBSTR("RUNOOB", 2, 3) AS ExtractString; -- UNO

SELECT SUBSTRING("RUNOOB", 2, 3) AS ExtractString; -- UNO

SELECT SUBSTRING_INDEX(‘a*b‘,‘*‘,1) -- a SELECT SUBSTRING_INDEX(‘a*b‘,‘*‘,-1)    -- b SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(‘a*b*c*d*e‘,‘*‘,3),‘*‘,-1)    -- c

SELECT TRIM(‘ RUNOOB ‘) AS TrimmedString;

SELECT UCASE("runoob"); -- RUNOOB

SELECT UPPER("runoob"); -- RUNOOB

• MySQL 数字函数

SELECT ABS(-1) -- 返回1

SELECT ACOS(0.25);

SELECT ASIN(0.25);

SELECT ATAN(2.5);

SELECT ATAN2(-0.8, 2);

SELECT AVG(Price) AS AveragePrice FROM Products;

SELECT CEIL(1.5) -- 返回2

SELECT CEIL(1.5) -- 返回2

SELECT COS(2);

SELECT COT(6);

SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;

SELECT DEGREES(3.1415926535898) -- 180

SELECT 10 DIV 5; -- 2

SELECT EXP(3) -- 20.085536923188

SELECT FLOOR(1.5) -- 返回1

SELECT GREATEST(3, 12, 34, 8, 25); -- 34

SELECT GREATEST("Google", "Runoob", "Apple"); -- Runoob

SELECT LEAST(3, 12, 34, 8, 25); -- 3

SELECT LEAST("Google", "Runoob", "Apple"); -- Apple

SELECT LN(2); -- 0.6931471805599453

SELECT LOG(20.085536923188) -- 3

SELECT LOG10(100) -- 2

SELECT LOG2(6); -- 2.584962500721156

SELECT MAX(Price) AS LargestPrice FROM Products;

SELECT MIN(Price) AS LargestPrice FROM Products;

SELECT MOD(5,2) -- 1

SELECT PI() --3.141593

SELECT POW(2,3) -- 8

SELECT POWER(2,3) -- 8

SELECT RADIANS(180) -- 3.1415926535898

SELECT RAND() --0.93099315644334

SELECT ROUND(1.23456) --1

SELECT SIGN(-10) -- (-1)

SELECT SIN(RADIANS(30)) -- 0.5

SELECT SQRT(25) -- 5

SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;

SELECT TAN(1.75); -- -5.52037992250933

SELECT TRUNCATE(1.23456,3) -- 1.234

• MySQL 日期函数

SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY); ->2017-06-25

SELECT ADDTIME(‘2011-11-11 11:11:11‘, 5) ->2011-11-11 11:11:16 (秒)

SELECT CURDATE(); -> 2018-09-19

SELECT CURRENT_DATE(); -> 2018-09-19

SELECT CURRENT_TIME(); -> 19:59:02

SELECT CURRENT_TIMESTAMP() -> 2018-09-19 20:57:43

SELECT CURTIME(); -> 19:59:02

SELECT DATE("2017-06-15"); -> 2017-06-15

SELECT DATEDIFF(‘2001-01-01‘,‘2001-02-02‘) -> -32

SELECT ADDDATE(‘2011-11-11 11:11:11‘,1) -> 2011-11-12 11:11:11    (默认是天) SELECT ADDDATE(‘2011-11-11 11:11:11‘, INTERVAL 5 MINUTE) -> 2011-11-11 11:16:11 (TYPE的取值与上面那个列出来的函数类似)

SELECT DATE_FORMAT(‘2011-11-11 11:11:11‘,‘%Y-%m-%d %r‘) -> 2011-11-11 11:11:11 AM

SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDate FROM Orders

SELECT DAY("2017-06-15"); -> 15

SELECT DAYNAME(‘2011-11-11 11:11:11‘) ->Friday

SELECT DAYOFMONTH(‘2011-11-11 11:11:11‘) ->11

SELECT DAYOFWEEK(‘2011-11-11 11:11:11‘) ->6

SELECT DAYOFYEAR(‘2011-11-11 11:11:11‘) ->315

SELECT EXTRACT(MINUTE FROM ‘2011-11-11 11:11:11‘) -> 11

SELECT FROM_DAYS(1111) -> 0003-01-16

SELECT HOUR(‘1:2:3‘) -> 1

SELECT LAST_DAY("2017-06-20"); -> 2017-06-30

SELECT LOCALTIME() -> 2018-09-19 20:57:43

SELECT LOCALTIMESTAMP() -> 2018-09-19 20:57:43

SELECT MAKEDATE(2017, 3); -> 2017-01-03

SELECT MAKETIME(11, 35, 4); -> 11:35:04

SELECT MICROSECOND("2017-06-20 09:34:00.000023"); -> 23

SELECT MINUTE(‘1:2:3‘) -> 2

SELECT MONTHNAME(‘2011-11-11 11:11:11‘) -> November

SELECT MONTH(‘2011-11-11 11:11:11‘) ->11

SELECT NOW() -> 2018-09-19 20:57:43

SELECT PERIOD_ADD(201703, 5); -> 201708

SELECT PERIOD_DIFF(201710, 201703); -> 7

SELECT QUARTER(‘2011-11-11 11:11:11‘) -> 4

SELECT SECOND(‘1:2:3‘) -> 3

SELECT SEC_TO_TIME(4320) -> 01:12:00

SELECT STR_TO_DATE("August 10 2017", "%M %d %Y"); -> 2017-08-10

SELECT SUBDATE(‘2011-11-11 11:11:11‘, 1) ->2011-11-10 11:11:11 (默认是天)

SELECT SUBTIME(‘2011-11-11 11:11:11‘, 5) ->2011-11-11 11:11:06 (秒)

SELECT SYSDATE() -> 2018-09-19 20:57:43

SELECT TIME("19:30:10"); -> 19:30:10

SELECT TIME_FORMAT(‘11:11:11‘,‘%r‘) 11:11:11 AM

SELECT TIME_TO_SEC(‘1:12:00‘) -> 4320

SELECT TIMEDIFF("13:10:11", "13:10:10"); -> 00:00:01

SELECT TIMESTAMP("2017-07-23", "13:10:11"); -> 2017-07-23 13:10:11

SELECT TO_DAYS(‘0001-01-01 01:01:01‘) -> 366

SELECT WEEK(‘2011-11-11 11:11:11‘) -> 45

SELECT WEEKDAY("2017-06-15"); -> 3

SELECT WEEKOFYEAR(‘2011-11-11 11:11:11‘) -> 45

SELECT YEAR("2017-06-15"); -> 2017

SELECT YEARWEEK("2017-06-15"); -> 201724

• MySQL 高级函数

SELECT BIN(15); -- 1111

SELECT BINARY "RUNOOB"; -> RUNOOB

CASE expression WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... WHEN conditionN THEN resultN ELSE result END

SELECT CASE 　　WHEN 1 > 0 　　THEN ‘1 > 0‘ 　　WHEN 2 > 0 　　THEN ‘2 > 0‘ 　　ELSE ‘3 > 0‘ 　　END ->1 > 0

SELECT CAST("2017-08-29" AS DATE); -> 2017-08-29

SELECT COALESCE(NULL, NULL, NULL, ‘runoob.com‘, NULL, ‘google.com‘); -> runoob.com

SELECT CONNECTION_ID(); -> 4292835

SELECT CONV(15, 10, 2); -> 1111

SELECT CHARSET(‘ABC‘) ->utf-8 SELECT CHARSET(CONVERT(‘ABC‘ USING gbk)) ->gbk

SELECT CURRENT_USER(); -> guest@%

SELECT DATABASE(); -> runoob

SELECT IF(1 > 0,‘正确‘,‘错误‘) ->正确

SELECT IFNULL(null,‘Hello Word‘) ->Hello Word

SELECT ISNULL(NULL); ->1

SELECT LAST_INSERT_ID(); ->6

SELECT NULLIF(25, 25); ->

SELECT SESSION_USER(); -> guest@%

SELECT SYSTEM_USER(); -> guest@%

SELECT USER(); -> guest@%

SELECT VERSION() -> 5.6.34

MySQL 运算符

• 介绍 MySQL 的运算符及运算符的优先级。 MySQL 主要有以下几种运算符：
  • 算术运算符
  • 比较运算符
  • 逻辑运算符
  • 位运算符

算术运算符

• MySQL 支持的算术运算符包括:

# 运算符 # 作用+ # 加法- # 减法* # 乘法/ 或 DIV # 除法% 或 MOD # 取余

• 在除法运算和模运算中，如果除数为0，将是非法除数，返回结果为NULL。

# 1、加mysql> select 1+2;+-----+| 1+2 |+-----+| 3 |+-----+# 2、减mysql> select 1-2;+-----+| 1-2 |+-----+| -1 |+-----+# 3、乘mysql> select 2*3;+-----+| 2*3 |+-----+| 6 |+-----+# 4、除mysql> select 2/3;+--------+| 2/3 |+--------+| 0.6667 |+--------+# 5、商mysql> select 10 DIV 4;+----------+| 10 DIV 4 |+----------+| 2 |+----------+# 6、取余mysql> select 10 MOD 4;+----------+| 10 MOD 4 |+----------+| 2 |+----------+

比较运算符

• SELECT 语句中的条件语句经常要使用比较运算符。通过这些比较运算符，可以判断表中的哪些记录是符合条件的。比较结果为真，则返回 1，为假则返回 0，比较结果不确定则返回 NULL。

# mysql> select 2=3;+-----+| 2=3 |+-----+| 0 |+-----+# mysql> select NULL = NULL;+-------------+| NULL = NULL |+-------------+| NULL |+-------------+

1、等于

# mysql> select 2<>3;+------+| 2<>3 |+------+| 1 |+------+

2、不等于

# 与 = 的区别在于当两个操作码均为 NULL 时，其所得值为 1 而不为 NULL，而当一个操作码为 NULL 时，其所得值为 0而不为 NULL。# mysql> select 2<=>3;+-------+| 2<=>3 |+-------+| 0 |+-------+# mysql> select null=null;+-----------+| null=null |+-----------+| NULL |+-----------+ # mysql> select null<=>null;+-------------+| null<=>null |+-------------+| 1 |+-------------+

3、安全等于

# mysql> select 2<3;+-----+| 2<3 |+-----+| 1 |+-----+

4、小于

# mysql> select 2<=3;+------+| 2<=3 |+------+| 1 |+------+

5、小于等于

# mysql> select 2>3;+-----+| 2>3 |+-----+| 0 |+-----+# mysql> select 2>=3;+------+| 2>=3 |+------+| 0 |+------+

6、大于、大于等于

# mysql> select 5 between 1 and 10;+--------------------+| 5 between 1 and 10 |+--------------------+| 1 |+--------------------+

7、BETWEEN

# mysql> select 5 in (1,2,3,4,5);+------------------+| 5 in (1,2,3,4,5) |+------------------+| 1 |+------------------+# mysql> select 5 not in (1,2,3,4,5);+----------------------+| 5 not in (1,2,3,4,5) |+----------------------+| 0 |+----------------------+

8、IN、NOT IN

# mysql> select null is NULL;+--------------+| null is NULL |+--------------+| 1 |+--------------+mysql> select ‘a‘ is NULL;+-------------+| ‘a‘ is NULL |+-------------+| 0 |+-------------+

9、IS NULL

# mysql> select null IS NOT NULL;+------------------+| null IS NOT NULL |+------------------+| 0 |+------------------+ # mysql> select ‘a‘ IS NOT NULL;+-----------------+| ‘a‘ IS NOT NULL |+-----------------+| 1 |+-----------------+

10、IS NOT NULL

# mysql> select ‘12345‘ like ‘12%‘;+--------------------+| ‘12345‘ like ‘12%‘ |+--------------------+| 1 |+--------------------+# mysql> select ‘12345‘ like ‘12_‘;+--------------------+| ‘12345‘ like ‘12_‘ |+--------------------+| 0 |+--------------------+

11、LIKE

# mysql> select ‘beijing‘ REGEXP ‘jing‘;+-------------------------+| ‘beijing‘ REGEXP ‘jing‘ |+-------------------------+| 1 |+-------------------------+# mysql> select ‘beijing‘ REGEXP ‘xi‘;+-----------------------+| ‘beijing‘ REGEXP ‘xi‘ |+-----------------------+| 0 |+-----------------------+

12、REGEXP

逻辑运算符

• 逻辑运算符用来判断表达式的真假。如果表达式是真，结果返回 1。如果表达式是假，结果返回 0。

#运算符号 # 作用NOT 或 ! # 逻辑非AND # 逻辑与OR # 逻辑或XOR # 逻辑异或

# mysql> select 2 and 0;+---------+| 2 and 0 |+---------+| 0 |+---------+ # mysql> select 2 and 1; +---------+ | 2 and 1 | +---------+ | 1 | +---------+

1、与

# mysql> select 2 or 0;+--------+| 2 or 0 |+--------+| 1 |+--------+# mysql> select 2 or 1;+--------+| 2 or 1 |+--------+| 1 |+--------+# mysql> select 0 or 0;+--------+| 0 or 0 |+--------+| 0 |+--------+# mysql> select 1 || 0;+--------+| 1 || 0 |+--------+| 1 |+--------+

2、或

# mysql> select not 1;+-------+| not 1 |+-------+| 0 |+-------+# mysql> select !0;+----+| !0 |+----+| 1 |+----+

3、非

# mysql> select 1 xor 1;+---------+| 1 xor 1 |+---------+| 0 |+---------+# mysql> select 0 xor 0;+---------+| 0 xor 0 |+---------+| 0 |+---------+# mysql> select 1 xor 0;+---------+| 1 xor 0 |+---------+| 1 |+---------+# mysql> select null or 1;+-----------+| null or 1 |+-----------+| 1 |+-----------+# mysql> select 1 ^ 0;+-------+| 1 ^ 0 |+-------+| 1 |+-------+

4、异或

位运算符

• 位运算符是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数，进行位运算。然后再将计算结果从二进制数变回十进制数。

# 运算符号 # 作用& # 按位与| # 按位或^ # 按位异或! # 取反<< # 左移>> # 右移

# 1、按位与mysql> select 3&5;+-----+| 3&5 |+-----+| 1 |+-----+# 2、按位或mysql> select 3|5;+-----+| 3|5 |+-----+| 7 |+-----+# 3、按位异或mysql> select 3^5;+-----+| 3^5 |+-----+| 6 |+-----+# 4、按位取反mysql> select ~18446744073709551612;+-----------------------+| ~18446744073709551612 |+-----------------------+| 3 |+-----------------------+# 5、按位右移mysql> select 3>>1;+------+| 3>>1 |+------+| 1 |+------+# 6、按位左移mysql> select 3<<1;+------+| 3<<1 |+------+| 6 |+------+

运算符优先级

• 最低优先级为： :=

• 最高优先级为： !、BINARY、 COLLATE。

聚合函数

# 总数：count# 查询男性有多少人，女性有多少人select count(*) from students where gender = 1;select count(*) from students where gender = 2;

# 最大值：max# 查询最大的年龄select max(age) from students;# 查询女性的最高 身高select max(height) from students where gender = 2;# 最小值：minselect min(height) from students where gender = 2;

# 求和：sum# 计算所有人的年龄总和select sum(age) from students;

# 平均值：avg# 计算平均年龄select avg(age) from students;# 计算平均年龄 sum(age)/count(*)# 四舍五入 round(123.23 , 1) 保留1位小数# 计算所有人的平均年龄，保留3位小数select round(avg(age),3) from students;# 计算男性的平均身高 保留2位小数select round(avg(height),2) from students where gender = 1;

# 查询类型 cate_name 为 ‘超级本‘ 的商品名称 name 、价格 price ( where )select name,price from goods where cate_name = "超级本";# 显示商品的种类# 1 分组的方式( group by ) select cate_name from goods group by cate_name;# 2 去重的方法( distinct )select distinct cate_name from goods;# 求所有电脑产品的平均价格 avg ,并且保留两位小数( round )select round(avg(price),2) from goods;# 显示 每种类型 cate_name (由此可知需要分组)的 平均价格select cate_name,avg(price) from goods group by cate_name;#查询 每种类型 的商品中 最贵 max 、最便宜 min 、平均价 avg 、数量 countselect max(price),min(price),avg(price),count(*) from goods group by cate_name;# 查询所有价格大于 平均价格 的商品，并且按 价格降序 排序 order desc# 1 查询平局价格(avg_price)select avg(price) as avg_price from goods;# 2 使用子查询select * from goods where price > (select avg(price) as avg_price from goods) order by price desc;# 查询每种类型中最贵的电脑信息(难)# 1 查找 每种类型 中 最贵的 max_price 价格select cate_name,max(price) as max_price from goods group by cate_name;# 2 关联查询 inner join 每种类型 中最贵的物品信息select * from goods inner join(select cate_name,max(price) as max_price from goods group by cate_name) as max_price_goodson goods.cate_name=max_price_goods.cate_name and goods.price=max_price_goods.max_price;# 创建"商品分类"表# 第一步 创建表 (商品种类表 goods_cates )create table if not exists goods_cates(id int unsigned primary key auto_increment,name varchar(40) not null);# 第二步 同步 商品分类表 数据 将商品的所有 (种类信息) 写入到 (商品种类表) 中 按照 分组 的方式查询 goods 表中的所有 种类(cate_name)select cate_name from goods group by cate_name;# (注意) 把查询出来的 结果 写入 goods_cates 表里去 ( insert into ) 只插入nameinsert into goods_cates(name) (select cate_name from goods group by cate_name);# 第三部 同步 商品表 数据 通过 goods_cates 数据表来更新 goods 表# 因为要通过 goods_cates表 更新 goods 表 所以要把两个表连接起来select * from goods inner join goods_cates on goods.cate_name = goods_cates.name;# 把 商品表 goods 中的 cate_name 全部替换成 商品分类表中的 商品id ( update ... set )update (goods inner join goods_cates on goods.cate_name = goods_cates.name) set goods.cate_name = goods_cates.id;# 第四部 修改表结构# 查看表结构(注意 两个表中的 外键类型需要一致)desc goods;#修改表结构 alter table 字段名字不同 change,把 cate_name 改成 cate_id int unsigned not nullalter table goods change cate_name cate_id int unsigned not null;# 创建 商品品牌表 goods_brands# 第一步 创建 "商品品牌表" 表# 第一种方式 先创建表create table goods_brands (id int unsigned primary key auto_increment,name varchar(40) not null);# 插入数据 brand_name(分组)# 按照 分组 的方式查询 goods 表中的所有 种类(brand_name)select brand_name from goods group by brand_name;# (注意) 把查询出来的 结果 写入 goods_brands 表里去 ( insert into ) 只插入nameinsert into goods_brands(name) (select brand_name from goods group by brand_name);# 第二种方式 创建表的同时插入数据(了解,不建议使用)create table goods_brands (id int unsigned primary key auto_increment,name varchar(40) not null) select brand_name as name from goods group by brand_name;# 第二步 同步数据# 通过goods_brands数据表来更新goods数据表 g.brand_name=b.idupdate (goods inner join goods_brands on goods.brand_name = goods_brands.name) set goods.brand_name = goods_brands.id;# 第三步 修改表结构# 通过alter table语句修改表结构 brand_id int unsigned not nullalter table goods change brand_name brand_id int unsigned not null;# 外键的使用(了解)# 创建外键# -- 向goods表里插入任意一条数据insert into goods (name,cate_id,brand_id,price) values(‘老王牌拖拉机‘, 10, 10,‘6666‘);# 约束 数据的插入 使用 外键 foreign keyalter table goods add foreign key (brand_id) references goods_brands(id);alter table goods add foreign key (cate_id) references goods_cates(id);alter table goods add foreign key(brand_id) references goods_brands(id);# 失败原因 老王牌拖拉机 deletedelete from goods where name="老王牌拖拉机";# 创建表的同时设置外键 (注意 goods_cates 和 goods_brands 两个表必须事先存在)create table goods(id int primary key auto_increment not null,name varchar(40) default ‘‘,price decimal(5,2),cate_id int unsigned,brand_id int unsigned,is_show bit default 1,is_saleoff bit default 0,foreign key(cate_id) references goods_cates(id),foreign key(brand_id) references goods_brands(id));# 如何取消外键约束#需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语来获取名称show create table goods;# 获取名称之后就可以根据名称来删除外键约束alter table goods drop foreign key goods_ibfk_1;alter table goods drop foreign key goods_ibfk_2;# 总结:在实际开发中,很少会使用到外键约束,会极大的降低表更新的效率

sql强化演练( goods 表练习)

MySQL 的介绍到这篇基本就算完事了，后期我会继续更新，关注不迷路哦，多多支持！！！