月份：2017年10月

1、在/etc/mysql/my.cnf中的[mysqld]段注释掉bind-address = 127.0.0.1

2、用mysql -uroot -p 登陆mysql，然后采用以下方法开启远程访问权限：

方法1：mysql>use mysql;

mysql>update user set host = ‘%’ where user = ‘root’;

mysql>FLUSH RIVILEGES；

方法2：mysql>GRANT ALL PRIVILEGES ON . TO ‘myuser’@’%’ IDENTIFIED BY ‘mypassword’ WITH GRANT OPTION;

数据很重要，这点用脚趾头想都知道，为了保证数据的安全，因此需要定期对数据备份

下面来写一点关于数据备份与还原相关的笔记

数据备份

使用mysqldump命令备份一个数据库

mysqldump -u username -p dbname table1 table2...>BackupName.sql

其中，dbname表示数据库名称，table1和table2表示表的名称，没有该参数则备份整个数据库

BackupName.sql参数表示备份文件的名称，文件名前面可以加上一个绝对路径

需要注意的是，备份的时候只备份数据库中的表，如果要还原则必须还原在已有数据库中

tips：通常情况下备份文件的后缀名为.sql，当然也可以是.txt等，只不过不专业

使用mysqldump命令备份多个数据库

mysqldump -u username -p--databases dbname1 dbname2...>BackupName.sql

此处–databases代表备份多个数据库的意思，后面接数据库名

此外还能备份所有数据库

使用mysqldump命令备份所有数据库

mysqldump -u username -p--all-databases>BackupName.sql

直接复制整个数据库目录

直接复制数据库目录是最简单的备份方法，但并非最好

使用这种方法时为了避免数据变化需要暂时停止数据库服务，但实际中不允许

而且，这种方法对InnoDB存储引擎的表不适用，对于MyISAM存储引擎的表比较方便

数据还原

使用mysql命令还原

 mysql -u root -p[dbname]<backup.sql

[dbname]是可选的，如果使用–all-databases参数备份整个数据库而不是单独备份表

那么便可以不使用dbname参数

直接复制到数据库目录

使用这种方式必须保证数据库的主版本号一致

通常对MyISAM比较有效，对InnoDB不适用。

在查询时，可以为表和字段取一个别名。这个别名可以代替其指定的表和字段

为表取别名

mysql> SELECT * FROM department d
    -> WHERE d.d_id=1001;
+------+-----------+--------------+-------------+
| d_id | d_name    | function     | address     |
+------+-----------+--------------+-------------+
| 1001 | 科技部    | 研发产品       | 3号楼5层     |
+------+-----------+--------------+-------------+
 row in set (0.00 sec)

这里为department表取了一个别名为d

为字段取别名

mysql> SELECT d_id AS department_id, d_name AS department_name
    -> FROM department;
+---------------+-----------------+
| department_id | department_name |
+---------------+-----------------+
|          1001 | 科技部          |
|          1002 | 生产部          |
|          1003 | 销售部          |
+---------------+-----------------+
 rows in set (0.00 sec)

此处语法与上面类似，只不过加了AS。

子查询是将一个查询语句嵌套在另一个查询语句中

内层查询语句的查询结果，可以为外层查询语句提供查询条件

因为在特定情况下，一个查询语句的条件需要另一个查询语句来获取

参考表：employee

参考表：department

带IN关键字的子查询

mysql> SELECT * FROM employee
    -> WHERE d_id IN
    -> (SELECT d_id FROM department);
+------+------+--------+------+------+--------------------+
| num  | d_id | name   | age  | sex  | homeaddr           |
+------+------+--------+------+------+--------------------+
|    1 | 1001 | 张三   |   26 | 男   | 北京市海淀区         |
|    2 | 1001 | 李四   |   24 | 女   | 北京市昌平区         |
|    3 | 1002 | 王五   |   25 | 男   | 湖南长沙市           |
+------+------+--------+------+------+--------------------+
 rows in set (0.00 sec)

此处首先查询出department表中所有d_id字段的信息，并将结果作为条件

接着查询employee表中以d_id为条件的所有字段信息

NOT IN的效果与上面刚好相反

带比较运算符的子查询

mysql> SELECT d_id, d_name FROM department
    -> WHERE d_id!=
    -> (SELECT d_id FROM employee WHERE age=24);
+------+-----------+
| d_id | d_name    |
+------+-----------+
| 1002 | 生产部    |
| 1003 | 销售部    |
+------+-----------+
 rows in set (0.00 sec)

这里查询出了哪些部门没有年龄为24岁的员工，看起来有点复杂

此外，运算符还有很多，这里不再赘述

带EXISTS关键字的子查询

EXISTS关键字表示存在。使用EXISTS关键字时，内层查询语句不返回查询的记录，而是返回一个真假值，如果内层查询语句查询到满足条件的记录，就返回true，否则返回false

当返回的值为true时，外层查询语句将进行查询，否则不进行查询

mysql> SELECT * FROM employee
    -> WHERE EXISTS
    -> (SELECT d_name FROM department WHERE d_id=1004);
Empty set (0.00 sec)

此处内层循环并没有查询到满足条件的结果，因此返回false，外层查询不执行

NOT EXISTS刚好与之相反

当然，EXISTS关键字可以与其他的查询条件一起使用

条件表达式与EXISTS关键字之间用AND或者OR来连接

mysql> SELECT * FROM employee
    -> WHERE age>24 AND EXISTS
    -> (SELECT d_name FROM department WHERE d_id=1003);
+------+------+--------+------+------+--------------------+
| num  | d_id | name   | age  | sex  | homeaddr           |
+------+------+--------+------+------+--------------------+
|    1 | 1001 | 张三   |   26 | 男   | 北京市海淀区         |
|    3 | 1002 | 王五   |   25 | 男   | 湖南长沙市           |
+------+------+--------+------+------+--------------------+
 rows in set (0.00 sec)

带ANY关键字的子查询

ANY关键字表示满足其中任一条件

mysql> SELECT * FROM employee
    -> WHERE d_id!=ANY
    -> (SELECT d_id FROM department);
+------+------+--------+------+------+--------------------+
| num  | d_id | name   | age  | sex  | homeaddr           |
+------+------+--------+------+------+--------------------+
|    1 | 1001 | 张三   |   26 | 男   | 北京市海淀区         |
|    2 | 1001 | 李四   |   24 | 女   | 北京市昌平区         |
|    3 | 1002 | 王五   |   25 | 男   | 湖南长沙市           |
|    4 | 1004 | Aric   |   15 | 男   | England            |
+------+------+--------+------+------+--------------------+
 rows in set (0.00 sec)

带ALL关键字的子查询

ALL关键字表示满足其中所有条件

mysql> SELECT * FROM employee
    -> WHERE d_id>=ALL
    -> (SELECT d_id FROM department);
+------+------+------+------+------+----------+
| num  | d_id | name | age  | sex  | homeaddr |
+------+------+------+------+------+----------+
|    4 | 1004 | Aric |   15 | 男   | England  |
+------+------+------+------+------+----------+
 row in set (0.00 sec)

暂时不明白这两条语句是什么意思，到时候再补上

UNION合并查询结果

mysql> SELECT d_id FROM employee
    -> UNION
    -> SELECT d_id FROM department;
+------+
| d_id |
+------+
| 1001 |
| 1002 |
| 1004 |
| 1003 |
+------+
 rows in set (0.00 sec)

合并比较好理解，也就是将多个查询的结果合并在一起，然后去除其中的重复记录。

如果想保存重复记录可以使用UNION ALL语句。

创建触发器

创建只有一个执行语句的触发器

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW 执行语句

其中，触发器名参数指要创建的触发器的名字

BEFORE和AFTER参数指定了触发执行的时间，在事件之前或是之后

FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器

mysql> CREATE TRIGGER trig1 AFTER INSERT
    -> ON work FOR EACH ROW
    -> INSERT INTO time VALUES(NOW());
Query OK, 0 rows affected (0.09 sec)

上面创建了一个名为trig1的触发器，一旦在work中有插入动作，就会自动往time表里插入当前时间

创建有多个执行语句的触发器

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
    执行语句列表
END

其中，BEGIN与END之间的执行语句列表参数表示需要执行的多个语句，不同语句用分号隔开

tips：一般情况下，mysql默认是以 ; 作为结束执行语句，与触发器中需要的分行起冲突

为解决此问题可用DELIMITER，如：DELIMITER ||，可以将结束符号变成||

当触发器创建完成后，可以用DELIMITER ;来将结束符号变成;

mysql> DELIMITER ||
mysql> CREATE TRIGGER trig2 BEFORE DELETE
    -> ON work FOR EACH ROW
    -> BEGIN
    -> INSERT INTO time VALUES(NOW());
    -> INSERT INTO time VALUES(NOW());
    -> END
    -> ||
Query OK, 0 rows affected (0.06 sec)

mysql> DELIMITER ;

上面的语句中，开头将结束符号定义为||，中间定义一个触发器，一旦有满足条件的删除操作

就会执行BEGIN和END中的语句，接着使用||结束

最后使用DELIMITER ; 将结束符号还原

查看触发器

SHOW TRIGGERS语句查看触发器信息

mysql> SHOW TRIGGERSG;
*************************** 1. row ***************************
             Trigger: trig1
               Event: INSERT
               Table: work
           Statement: INSERT INTO time VALUES(NOW())
              Timing: AFTER
             Created: NULL
            sql_mode: 
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci

结果会显示所有触发器的基本信息

tips：SHOW TRIGGERS语句无法查询指定的触发器

在triggers表中查看触发器信息

mysql> SELECT * FROM information_schema.triggersG
*************************** 1. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: person
              TRIGGER_NAME: trig1
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: person
        EVENT_OBJECT_TABLE: work
              ACTION_ORDER: 0
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: INSERT INTO time VALUES(NOW())

结果显示了所有触发器的详细信息，同时，该方法可以查询制定触发器的详细信息

mysql> SELECT * FROM information_schema.triggers WHERE TRIGGER_NAME='trig1'G
*************************** 1. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: person
              TRIGGER_NAME: trig1
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: person
        EVENT_OBJECT_TABLE: work

tips：所有触发器信息都存储在information_schema数据库下的triggers表中

可以使用SELECT语句查询，如果触发器信息过多，最好通过TRIGGER_NAME字段指定查询

删除触发器

mysql> DROP TRIGGER trig1;
Query OK, 0 rows affected (0.04 sec)

删除触发器之后最好使用上面的方法查看一遍

同时，也可以使用database.trig来指定某个数据库中的触发器

tips：如果不需要某个触发器时一定要将这个触发器删除，以免造成意外操作

字符串操作在程序设计中是非常重要的组成部分，而MySQL数据库中的字符串操作却相当简单

需要注意的是：下面所有的函数只是将修改后的结果当查询返回，均不对原数据做出改变

选出指定数量字符

mysql> SELECT RIGHT('nihao',3);
+------------------+
| RIGHT('nihao',3) |
+------------------+
| hao              |
+------------------+
 row in set (0.00 sec)

这里的RIGHT()函数代表从字符串中选定从右往左数的三个字符

与此类似，还有LEFT()函数

SUBSTRING_INDEX截取字符串

mysql> SELECT SUBSTRING_INDEX('HH,MM,SS',',',2);
+-----------------------------------+
| SUBSTRING_INDEX('HH,MM,SS',',',2) |
+-----------------------------------+
| HH,MM                             |
+-----------------------------------+
 row in set (0.00 sec)

此函数内部的第一个参数代表需要截取的内容，第二个参数代表按什么截取

最后一个是截取到第几个，1是截取到第一个逗号，2是截取到第二个

SUBSTRING截取字符串

mysql> SELECT SUBSTRING('helloworld',1,5);
+-----------------------------+
| SUBSTRING('helloworld',1,5) |
+-----------------------------+
| hello                       |
+-----------------------------+
 row in set (0.00 sec)

此处截取字符串中1～5的内容

UPPER字符串改大写

mysql> SELECT UPPER('hello');
+----------------+
| UPPER('hello') |
+----------------+
| HELLO          |
+----------------+
 row in set (0.00 sec)

LOWER字符串改小写

mysql> SELECT LOWER('HELLO');
+----------------+
| LOWER('HELLO') |
+----------------+
| hello          |
+----------------+
 row in set (0.00 sec)

REVERSE反转字符串

mysql> SELECT REVERSE('hello');
+------------------+
| REVERSE('hello') |
+------------------+
| olleh            |
+------------------+
 row in set (0.00 sec)

LTRIM清除左边多余空格

mysql> SELECT LTRIM('  hello     ');
+-----------------------+
| LTRIM('  hello     ') |
+-----------------------+
| hello                 |
+-----------------------+
 row in set (0.00 sec)

此外还有RTRIM清除右边空格，TRIM清除两边全部空格

LENGTH返回字符串中的字符数量

mysql> SELECT LENGTH('helo');
+----------------+
| LENGTH('helo') |
+----------------+
|              4 |
+----------------+
 row in set (0.00 sec)

连接查询是将两个或两个以上的表按某个条件连接起来，从中选取需要的数据

当不同的表中存在表示相同意义的字段时，可以通过该字段来连接这几张表

参考表：employee

参考表：department

可以看到，上面两张表都有同一个字段d_id

当两张表含有相同意义的字段（可以不同名）时就可以进行连接查询

内连接查询

mysql> SELECT num, name, employee.d_id, sex, d_name, function
    -> FROM employee, department
    -> WHERE employee.d_id=department.d_id;
+------+--------+------+------+-----------+--------------+
| num  | name   | d_id | sex  | d_name    | function     |
+------+--------+------+------+-----------+--------------+
|    1 | 张三   | 1001 | 男   | 科技部    | 研发产品     |
|    2 | 李四   | 1001 | 女   | 科技部    | 研发产品     |
|    3 | 王五   | 1002 | 男   | 生产部    | 生产产品     |
+------+--------+------+------+-----------+--------------+
 rows in set (0.00 sec)

内连接查询只会查询完全匹配的结果，此处使用d_id字段进行连接

下面的表中也同样使用这一字段

外连接查询

外查询也需要通过指定字段来进行连接，当该字段取值相等时，可以查询出该记录

而且，该字段取值不相等的记录也可以查询出来

外连接查询包括左连接查询和右连接查询

左连接查询

mysql> SELECT num, name, employee.d_id, d_name, function
    -> FROM employee LEFT JOIN department
    -> ON employee.d_id=department.d_id;
+------+--------+------+-----------+--------------+
| num  | name   | d_id | d_name    | function     |
+------+--------+------+-----------+--------------+
|    1 | 张三   | 1001 | 科技部    | 研发产品     |
|    2 | 李四   | 1001 | 科技部    | 研发产品     |
|    3 | 王五   | 1002 | 生产部    | 生产产品     |
|    4 | Aric   | 1004 | NULL      | NULL         |
+------+--------+------+-----------+--------------+
 rows in set (0.00 sec)

此处不仅查询出了两表中d_id字段相匹配的信息

并且通过LEFT JOIN查询出了employee表中所有指定字段的信息

由于Aric没有对应d_name和function信息，所以显示null

右连接查询

mysql> SELECT num, name, employee.d_id, d_name, function
    -> FROM employee RIGHT JOIN department
    -> ON employee.d_id=department.d_id;
+------+--------+------+-----------+--------------+
| num  | name   | d_id | d_name    | function     |
+------+--------+------+-----------+--------------+
|    1 | 张三   | 1001 | 科技部    | 研发产品     |
|    2 | 李四   | 1001 | 科技部    | 研发产品     |
|    3 | 王五   | 1002 | 生产部    | 生产产品     |
| NULL | NULL   | NULL | 销售部    | 策划销售     |
+------+--------+------+-----------+--------------+
 rows in set (0.00 sec)

与上面相反，这里查询出了匹配的信息和department表中的所有指定字段的信息

但是由于employee表中部分字段没有对应，因此最后一行记录有显示NULL

复合条件连接查询

mysql> SELECT num, name, employee.d_id, sex, age, address
    -> FROM employee, department
    -> WHERE employee.d_id=department.d_id
    -> AND age>=25;
+------+--------+------+------+------+-------------+
| num  | name   | d_id | sex  | age  | address     |
+------+--------+------+------+------+-------------+
|    1 | 张三   | 1001 | 男   |   26 | 3号楼5层    |
|    3 | 王五   | 1002 | 男   |   25 | 5号楼1层    |
+------+--------+------+------+------+-------------+
 rows in set (0.00 sec)

复合条件连接查询是在进行连接查询的时候加入限制条件，此处的age>=25便是

通常情况下，限制条件越多，查询越精确，限制条件可用AND累加

此外，还可以用复合条件进行ORDER BY 排序

tips：连接查询中使用最多的是内连接查询，而外连接查询使用频率较低