简介

数据库简介

数据库是按照数据结构来组织、存储和管理数据的

RDBMS术语

关系型数据库管理系统（RDBMS）来存储和管理的大数据量

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

管理

安装MySQL

1	mysql install

启动及关闭MySQL服务

1 2	net start mysql net stop mysql

初始化data目录

1	mysqld --initialize-insecure

连接

cmd命令行连接

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

1	mysql -u root -p

退出 mysql> 命令提示窗口可以使用 exit 命令

exit

创建数据库

登陆 MySQL 服务后，使用 create 命令创建数据库

1 2	mysql -u root -p mysql> create DATABASE RUNOOB;

普通用户创建数据库

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

1 2	// 使用root用户登录，root用户拥有最高权限，使用 mysql mysqladmin 命令来创建数据库 mysqladmin -u root -p create RUNOOB

删除数据库

drop 命令删除数据库

删除名为 RUNOOB 的数据库

1	mysql> drop database RUNOOB;

普通用户删除数据库

使用 mysql mysqladmin 命令在终端来执行删除命令

1	mysqladmin -u root -p drop RUNOOB

选择数据库

// 选取了数据库 RUNOOB
mysql -u root -p
mysql> use RUNOOB;
Database changed

数据类型

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

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

数值类型

整型

tinyint 0-255 1个字节长
smallint 0-65535 2个字节长
int 0-21亿 4个字节长
bigint 0-42亿 8个字节长

浮点型

float(M,D) 单精度浮点数。可以精确到小数点后7位。M代表总宽度，D代表小数位
double(M,D) 双精度浮点数。可以精确到小数点后15位。M代表总宽度，D代表小数位

日期和时间类型

date 形如： “YYYY-MM-DD”
time 形如： “HH:MM:SS”
datetime 形如： “YYYY-MM-DD HH:MM:SS”

字符串类型

tinytext 0-255 最小文本型
text 0-1677万中型文本
longtext 0-42亿大型文本

创建数据表

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

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

语法:CREATE TABLE table_name (column_name column_type);

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

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

通过命令提示符创建表

通过 mysql> 命令窗口可以很简单的创建MySQL数据表

mysql -u root -p
mysql> use RUNOOB;
Database changed
mysql> CREATE TABLE runoob_tbl(
   -> runoob_id INT NOT NULL AUTO_INCREMENT,
   -> runoob_title VARCHAR(100) NOT NULL,
   -> runoob_author VARCHAR(40) NOT NULL,
   -> submission_date DATE,
   -> PRIMARY KEY ( runoob_id )
   -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.16 sec)
mysql>

删除数据表

语法:DROP TABLE table_name ;

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

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

删除了数据表runoob_tbl

root@host# mysql -u root -p
Enter password:*******
mysql> use RUNOOB;
Database changed
mysql> DROP TABLE runoob_tbl
Query OK, 0 rows affected (0.8 sec)
mysql>

插入数据

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

语法:INSERT INTO table_name ( field1, field2,…fieldN ) VALUES ( value1, value2,…valueN );

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

使用 SQL INSERT INTO 语句向 MySQL 数据表 runoob_tbl 插入数据

mysql -u root -p password;
mysql> use RUNOOB;
Database changed
mysql> INSERT INTO runoob_tbl 
    -> (runoob_title, runoob_author, submission_date)
    -> VALUES
    -> ("学习 PHP", "菜鸟教程", NOW());
Query OK, 1 rows affected, 1 warnings (0.01 sec)
mysql> INSERT INTO runoob_tbl
    -> (runoob_title, runoob_author, submission_date)
    -> VALUES
    -> ("学习 MySQL", "菜鸟教程", NOW());
Query OK, 1 rows affected, 1 warnings (0.01 sec)
mysql> INSERT INTO runoob_tbl
    -> (runoob_title, runoob_author, submission_date)
    -> VALUES
    -> ("JAVA 教程", "RUNOOB.COM", '2016-05-06');
Query OK, 1 rows affected (0.00 sec)
mysql>
// 读取数据表
select * from runoob_tbl;

查询数据

语法:SELECT column_name,column_nameFROM table_name[WHERE Clause][LIMIT N][ OFFSET M];

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

通过命令提示符获取数据

通过 SQL SELECT 命令来获取 MySQL 数据表 runoob_tbl 的所有数据

1	select * from runoob_tbl;

WHERE 子句

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

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

从命令提示符中读取数据

读取 runoob_tbl 表中 runoob_author 字段值为 Sanjay 的所有记录

1	SELECT * from runoob_tbl WHERE runoob_author='菜鸟教程';

MySQL 的 WHERE 子句的字符串比较是不区分大小写的

// 因为使用了 BINARY 关键字，是区分大小写的，所以 runoob_author='runoob.com' 的查询条件是没有数据的
mysql> SELECT * from runoob_tbl WHERE BINARY runoob_author='runoob.com';
Empty set (0.01 sec)
 
mysql> SELECT * from runoob_tbl WHERE BINARY runoob_author='RUNOOB.COM';
+-----------+---------------+---------------+-----------------+
| runoob_id | runoob_title  | runoob_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 3         | JAVA 教程   | RUNOOB.COM    | 2016-05-06      |
| 4         | 学习 Python | RUNOOB.COM    | 2016-03-06      |
+-----------+---------------+---------------+-----------------+
2 rows in set (0.01 sec)

UPDATE 查询

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

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

通过命令提示符更新数据

将更新数据表中 runoob_id 为 3 的 runoob_title 字段值

mysql> UPDATE runoob_tbl SET runoob_title='学习 C++' WHERE runoob_id=3;
Query OK, 1 rows affected (0.01 sec)
 
mysql> SELECT * from runoob_tbl WHERE runoob_id=3;
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
| 3         | 学习 C++   | RUNOOB.COM    | 2016-05-06      |
+-----------+--------------+---------------+-----------------+
1 rows in set (0.01 sec)

DELETE 语句

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

语法:DELETE FROM table_name [WHERE Clause]

从命令行中删除数据

在 SQL DELETE 命令中使用 WHERE 子句来删除 MySQL 数据表 runoob_tbl 所选的数据

// 将删除 runoob_tbl 表中 runoob_id 为3
mysql> use RUNOOB;
Database changed
mysql> DELETE FROM runoob_tbl WHERE runoob_id=3;
Query OK, 1 row affected (0.23 sec)

LIKE 子句

“runoob_author = ‘RUNOOB.COM’”,获取 runoob_author 字段含有 “COM” 字符的所有记录，需要在 WHERE 子句中使用 SQL LIKE 子句

1
2
3

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

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

runoob_tbl 表中获取 runoob_author 字段中以 COM 为结尾的的所有记录

mysql> use RUNOOB;
Database changed
mysql> SELECT * from runoob_tbl  WHERE runoob_author LIKE '%COM';
+-----------+---------------+---------------+-----------------+
| runoob_id | runoob_title  | runoob_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 3         | 学习 Java   | RUNOOB.COM    | 2015-05-01      |
| 4         | 学习 Python | RUNOOB.COM    | 2016-03-06      |
+-----------+---------------+---------------+-----------------+
2 rows in set (0.01 sec)

UNION 操作符

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合

参数

expression1, expression2, … expression_n: 要检索的列
tables: 要检索的数据表
WHERE conditions: 可选，检索条件
DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响
ALL: 可选，返回所有结果集，包含重复数据

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

演示数据库

// websites数据库
mysql> SELECT * FROM Websites;
+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/    | 1     | USA     |
| 2  | 淘宝          | https://www.taobao.com/   | 13    | CN      |
| 3  | 菜鸟教程      | http://www.runoob.com/    | 4689  | CN      |
| 4  | 微博          | http://weibo.com/         | 20    | CN      |
| 5  | Facebook     | https://www.facebook.com/ | 3     | USA     |
| 7  | stackoverflow | http://stackoverflow.com/ |   0 | IND     |
+----+---------------+---------------------------+-------+---------+
// app数据库
mysql> SELECT * FROM apps;
+----+------------+-------------------------+---------+
| id | app_name   | url                     | country |
+----+------------+-------------------------+---------+
|  1 | QQ APP     | http://im.qq.com/       | CN      |
|  2 | 微博 APP | http://weibo.com/       | CN      |
|  3 | 淘宝 APP | https://www.taobao.com/ | CN      |
+----+------------+-------------------------+---------+
3 rows in set (0.00 sec)

SQL UNION 实例

SQL 语句从 “Websites” 和 “apps” 表中选取所有不同的country（只有不同的值）

SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country;

SQL UNION ALL 实例

SQL 语句使用 UNION ALL 从 “Websites” 和 “apps” 表中选取所有的country（也有重复的值）

SELECT country FROM Websites
UNION ALL
SELECT country FROM apps
ORDER BY country;

带有 WHERE 的 SQL UNION ALL

SQL 语句使用 UNION ALL 从 “Websites” 和 “apps” 表中选取所有的中国(CN)的数据（也有重复的值）

SELECT country, name FROM Websites
WHERE country='CN'
UNION ALL
SELECT country, app_name FROM apps
WHERE country='CN'
ORDER BY country;

排序

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

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

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

ASC 或 DESC 关键字来设置查询结果是按升序或降序排列

mysql> use RUNOOB;
Database changed
mysql> SELECT * from runoob_tbl ORDER BY submission_date ASC;
+-----------+---------------+---------------+-----------------+
| runoob_id | runoob_title  | runoob_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 3         | 学习 Java   | RUNOOB.COM    | 2015-05-01      |
| 4         | 学习 Python | RUNOOB.COM    | 2016-03-06      |
| 1         | 学习 PHP    | 菜鸟教程  | 2017-04-12      |
| 2         | 学习 MySQL  | 菜鸟教程  | 2017-04-12      |
+-----------+---------------+---------------+-----------------+
4 rows in set (0.01 sec)
 
mysql> SELECT * from runoob_tbl ORDER BY submission_date DESC;
+-----------+---------------+---------------+-----------------+
| runoob_id | runoob_title  | runoob_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 1         | 学习 PHP    | 菜鸟教程  | 2017-04-12      |
| 2         | 学习 MySQL  | 菜鸟教程  | 2017-04-12      |
| 4         | 学习 Python | RUNOOB.COM    | 2016-03-06      |
| 3         | 学习 Java   | RUNOOB.COM    | 2015-05-01      |
+-----------+---------------+---------------+-----------------+
4 rows in set (0.01 sec)

GROUP BY 语句

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

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

语法

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-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…）

// 将以上的数据表按名字进行分组，再统计每个人登录的次数
// 记录 NULL 表示所有人的登录次数
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)

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

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

连接的使用

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

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

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

在命令提示符中使用 INNER JOIN

RUNOOB数据库中有两张表 tcount_tbl 和 runoob_tbl

mysql> use RUNOOB;
Database changed
mysql> SELECT * FROM tcount_tbl;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| 菜鸟教程  | 10           |
| RUNOOB.COM    | 20           |
| Google        | 22           |
+---------------+--------------+
3 rows in set (0.01 sec)
 
mysql> SELECT * from runoob_tbl;
+-----------+---------------+---------------+-----------------+
| runoob_id | runoob_title  | runoob_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 1         | 学习 PHP    | 菜鸟教程  | 2017-04-12      |
| 2         | 学习 MySQL  | 菜鸟教程  | 2017-04-12      |
| 3         | 学习 Java   | RUNOOB.COM    | 2015-05-01      |
| 4         | 学习 Python | RUNOOB.COM    | 2016-03-06      |
| 5         | 学习 C      | FK            | 2017-04-05      |
+-----------+---------------+---------------+-----------------+
5 rows in set (0.01 sec)

MySQL的INNER JOIN来连接以上两张表来读取runoob_tbl表中所有runoob_author字段在tcount_tbl表对应的runoob_count字段值

mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
+-------------+-----------------+----------------+
| a.runoob_id | a.runoob_author | b.runoob_count |
+-------------+-----------------+----------------+
| 1           | 菜鸟教程    | 10             |
| 2           | 菜鸟教程    | 10             |
| 3           | RUNOOB.COM      | 20             |
| 4           | RUNOOB.COM      | 20             |
+-------------+-----------------+----------------+
4 rows in set (0.00 sec)
// 以上 SQL 语句等价于
mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a, tcount_tbl b WHERE a.runoob_author = b.runoob_author;
+-------------+-----------------+----------------+
| a.runoob_id | a.runoob_author | b.runoob_count |
+-------------+-----------------+----------------+
| 1           | 菜鸟教程    | 10             |
| 2           | 菜鸟教程    | 10             |
| 3           | RUNOOB.COM      | 20             |
| 4           | RUNOOB.COM      | 20             |
+-------------+-----------------+----------------+
4 rows in set (0.01 sec)

MySQL LEFT JOIN

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

// 以 runoob_tbl 为左表，tcount_tbl 为右表
// 读取左边的数据表 runoob_tbl 的所有选取的字段数据，即便在右侧表 tcount_tbl中 没有对应的 runoob_author 字段值
mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
+-------------+-----------------+----------------+
| a.runoob_id | a.runoob_author | b.runoob_count |
+-------------+-----------------+----------------+
| 1           | 菜鸟教程    | 10             |
| 2           | 菜鸟教程    | 10             |
| 3           | RUNOOB.COM      | 20             |
| 4           | RUNOOB.COM      | 20             |
| 5           | FK              | NULL           |
+-------------+-----------------+----------------+
5 rows in set (0.01 sec)

MySQL RIGHT JOIN

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

// 以 runoob_tbl 为左表，tcount_tbl 为右表，理解MySQL RIGHT JOIN的应用
mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a RIGHT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
+-------------+-----------------+----------------+
| a.runoob_id | a.runoob_author | b.runoob_count |
+-------------+-----------------+----------------+
| 1           | 菜鸟教程    | 10             |
| 2           | 菜鸟教程    | 10             |
| 3           | RUNOOB.COM      | 20             |
| 4           | RUNOOB.COM      | 20             |
| NULL        | NULL            | 22             |
+-------------+-----------------+----------------+
5 rows in set (0.01 sec)

NULL 值处理

SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作

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

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

在命令提示符中使用 NULL 值

数据库 RUNOOB 中的表 runoob_test_tbl 含有两列 runoob_author 和 runoob_count, runoob_count 中设置插入NULL值

root@host# mysql -u root -p password;
Enter password:*******
mysql> use RUNOOB;
Database changed
mysql> create table runoob_test_tbl
    -> (
    -> runoob_author varchar(40) NOT NULL,
    -> runoob_count  INT
    -> );
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO runoob_test_tbl (runoob_author, runoob_count) values ('RUNOOB', 20);
mysql> INSERT INTO runoob_test_tbl (runoob_author, runoob_count) values ('菜鸟教程', NULL);
mysql> INSERT INTO runoob_test_tbl (runoob_author, runoob_count) values ('Google', NULL);
mysql> INSERT INTO runoob_test_tbl (runoob_author, runoob_count) values ('FK', 20);
 
mysql> SELECT * from runoob_test_tbl;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| RUNOOB        | 20           |
| 菜鸟教程  | NULL         |
| Google        | NULL         |
| FK            | 20           |
+---------------+--------------+
4 rows in set (0.01 sec)

可以看到 = 和 != 运算符是不起作用的

mysql> SELECT * FROM runoob_test_tbl WHERE runoob_count = NULL;
Empty set (0.00 sec)
mysql> SELECT * FROM runoob_test_tbl WHERE runoob_count != NULL;
Empty set (0.01 sec)

查找数据表中 runoob_test_tbl 列是否为 NULL，必须使用 IS NULL 和 IS NOT NULL

mysql> SELECT * FROM runoob_test_tbl WHERE runoob_count IS NULL;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| 菜鸟教程  | NULL         |
| Google        | NULL         |
+---------------+--------------+
2 rows in set (0.01 sec)
 
mysql> SELECT * from runoob_test_tbl WHERE runoob_count IS NOT NULL;
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| RUNOOB        | 20           |
| FK            | 20           |
+---------------+--------------+
2 rows in set (0.01 sec)

正则表达式

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 次

// 查找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个条件:原子性、一致性、隔离性、持久性

原子性：一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节
一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失

事务控制语句

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

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    |
+------+
2 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    |
+------+
2 rows in set (0.01 sec)
 
mysql>

ALTER命令

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

// 创建一张表，表名为：testalter_tbl
mysql -u root -p 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;
// 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 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;

修改字段类型及名称

需要修改字段类型及名称, 你可以在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 值和默认值的影响

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

1
2
3

// 指定字段 j 为 NOT NULL 且默认值为100 
mysql> ALTER TABLE testalter_tbl 
    -> MODIFY j BIGINT NOT NULL DEFAULT 100;

修改字段默认值

可以使用 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

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 子句来实现

1 2	// 将数据表 testalter_tbl 重命名为 alter_tbl mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

索引

索引分单列索引和组合索引

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

组合索引，即一个索引包含多个列

普通索引

这是最基本的索引，它没有任何限制。

// 创建索引
CREATE INDEX indexName ON mytable(username(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): 这条语句创建索引的值必须是唯一的
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;

显示索引信息

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

1 2	mysql> SHOW INDEX FROM table_name; \G ........

临时表

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

使用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)

删除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 'RUNOOB.SalesSummary' doesn't exist

复制表

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

获取数据表的完整结构

mysql> SHOW CREATE TABLE runoob_tbl \G;
*************************** 1. row ***************************
       Table: runoob_tbl
Create 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 
1 row in set (0.00 sec)
ERROR:
No query specified

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

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)

执行完第二步骤后，你将在数据库中创建新的克隆表 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

元数据

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

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

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

# 方法 1
# 使用do( ) 执行  $query 
my $count = $dbh->do ($query);
# 如果发生错误会输出 0
printf "%d 条数据被影响\n", (defined ($count) ? $count : 0);
# 方法 2
# 使用prepare( ) 及 execute( ) 执行  $query 
my $sth = $dbh->prepare ($query);
my $count = $sth->execute ( );
printf "%d 条数据被影响\n", (defined ($count) ? $count : 0);

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

$result_id = mysqli_query ($conn_id, $query);
# 如果查询失败返回 
$count = ($result_id ? mysqli_affected_rows ($conn_id) : 0);
print ("$count 条数据被影响\n");

数据库和数据表列表

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

使用 SHOW TABLES 或 SHOW DATABASES 语句来获取数据库和数据表列表

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

输出 MySQL 服务器上的所有数据库

<?php
$dbhost = 'localhost:3306';  // mysql服务器主机地址
$dbuser = 'root';            // mysql用户名
$dbpass = '123456';          // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die('连接失败: ' . mysqli_error($conn));
}
// 设置编码，防止中文乱码
$db_list = mysqli_query($conn, 'SHOW DATABASES');
while ($db = mysqli_fetch_object($db_list))
{
  echo $db->Database . "<br />";
}
mysqli_close($conn);
?>

获取服务器元数据

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

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

序列使用

MySQL序列是一组整数：1, 2, 3, …，由于一张数据表只能有一个字段自增主键

使用AUTO_INCREMENT

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: 0
mysql> 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_insertid 属性来获取 AUTO_INCREMENT 的值

1
2
3

$dbh->do ("INSERT INTO insect (name,date,origin)
VALUES('moth','2001-09-14','windowsill')");
my $seq = $dbh->{mysql_insertid};

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

1
2
3

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,
    -> PRIMARY KEY (id),
    -> name VARCHAR(30) NOT NULL, 
    -> date DATE NOT NULL,
    -> origin VARCHAR(30) NOT NULL
)engine=innodb auto_increment=100 charset=utf8;

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

1	mysql> ALTER TABLE t AUTO_INCREMENT = 100;