扩展 ：
SQL语句教程
http://www.runoob.com/sql/sql-tutorial.html
什么是事务？事务的特性有哪些？
http://blog.csdn.net/yenange/article/details/7556094
根据binlog恢复指定时间段的数据
http://www.centoscn.com/mysql/2015/0204/4630.html
mysql字符集调整
http://xjsunjie.blog.51cto.com/999372/1355013
使用xtrabackup备份innodb引擎的数据库 innobackupex 备份 Xtrabackup 增量备份
mysql用户管理
登录mysql，默认root用户操作：
1. 创建mysql用户(user1),设置密码(123456),设定指定ip(127.0.0.1) ：
[root@aminglinux-128 ~]# mysql -u'user1' -p'123456' -h'127.0.0.1' Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 5.6.39 MySQL Community Server (GPL)
16.4 常用的SQL语句
1.第一种形式
mysql> select count(*) from mysql.user; +----------+ | count(*) | +----------+ | 9 | +----------+ 1 row in set (0.05 sec)
第二种形式
mysql> select * from mysql.db; +------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+ | Host | Db | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv | Event_priv | Trigger_priv | +------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+ | % | test | | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | N | N | Y | Y | | % | test\_% | | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | N | N | Y | Y | +------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+------------+--------------+ 2 rows in set (0.10 sec)
mysql> select db from mysql.db; +---------+ | db | +---------+ | test | | test\_% | +---------+ 2 rows in set (0.00 sec)
mysql> select db,user from mysql.db; +---------+------+ | db | user | +---------+------+ | test | | | test\_% | | +---------+------+ 2 rows in set (0.00 sec)
mysql> select * from mysql.db where host like '192.168.%'; Empty set (0.06 sec)
上例中，在查询语句中可以使用万能匹配符。
插入一行
mysql> insert into db1.t1 values (1, 'abc'); Query OK, 1 row affected (0.05 sec) mysql> select * from db1.t1; +------+------+ | id | name | +------+------+ | 1 | abc | +------+------+ 1 row in set (0.00 sec)
更改表得某一行
mysql> update db1.t1 set name='aaa' where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from db1.t1; +------+------+ | id | name | +------+------+ | 1 | aaa | +------+------+ 1 row in set (0.00 sec)
清除某个表的数据
mysql> select * from db1.t1; +------+------+ | id | name | +------+------+ | 1 | aaa | +------+------+ 1 row in set (0.00 sec) mysql> truncate table db1.t1; Query OK, 0 rows affected (0.01 sec) mysql> select * from db1.t1; Empty set (0.00 sec)
删除表
mysql> drop table db1.t1; Query OK, 0 rows affected (0.01 sec)
删除数据库
mysql> drop table db1; ERROR 1051 (42S02): Unknown table 'db1.db1'
16.5 MySQL数据库的备份与恢复
Mysql备份
[root@aminglinux-128 ~]# mysqldump -uroot -p'haomima' mysql > /tmp/mysql.sql Warning: Using a password on the command line interface can be insecure.
1. 恢复库：备份的mysql库,恢复到mysql2库 ：
# mysql -uroot -p'haomima' mysql2< /tmp/mysql.sql
2. 备份mysql库下的user表(重定向到/tmp/user.sql文件)
# mysql -uroot -p'haomima' mysql user > /tmp/user.sql
3. 备份所有的数据库 ：
# mysqldump -uroot -p'haomima' -A > /tmp/mysql_all.sql
4. 只备份 数据库表结构(不备份表的数据) ：
# mysqldump -uroot -p'haomima' -d mysql > /tmp/mysql.sql
