4.mysql应用管理实践(1)

[root@oldboyedu-01 ~]# select user,host,password from mysql.user;
[root@oldboyedu-01 ~]# drop user 'oldgirl'@'localhost';
[root@oldboyedu-01 ~]# delete from mysql.user where user='root' and host='oldgirl';

[root@oldboyedu-01 ~]# create database oldboy;
[root@oldboyedu-01 ~]# show databases;

[root@oldboyedu-01 ~]# use oldboy
[root@oldboyedu-01 ~]# select database();

[root@oldboyedu-01 ~]# drop database oldboy;

mysql应用管理实践
=========================
1.mysql服务启动与关闭深入理解
（1）mysql单实例启动与关闭
[root@crmn mysql-5.5.32]# /etc/init.d/mysqld start //启动mysql
Starting MySQL... [确定]
[root@crmn mysql-5.5.32]# netstat -lntup|grep 3306 //查看端口服务是否开启，等价于netstat -lntup|grep mysql
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 18527/mysqld
[root@oldboyedu-01 ~]# ps -ef|grep mysql|grep -v grep //查看mysql进程

[root@crmn mysql-5.5.32]# cp support-files/mysql.server /etc/init.d/mysqld //拷贝mysql启动脚本到/etc/init.d下
$bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null 2>&1 &

（2）初始化数据库时mysql系统输出给出的启动方法：
[root@crmn mysql-5.5.32]# /etc/init.d/mysqld start //等价于mysqld_safe --user=mysql &

（3）常规方法关闭数据库
[root@crmn mysql-5.5.32]# /etc/init.d/mysqld stop //关闭mysql
（4）强制关闭数据库：（除非万不得已，尽量不要用！）
killall mysqld
pkill mysqld
killall -9 mysqld

案例：数据库强制关闭导致故障排除实战案例
注意：尽量不要野蛮粗鲁杀死数据库，生产高并发环境可能会引起数据丢失。
http://www.cnblogs.com/peida/archive/2012/12/20/2825837.html
http://blog.51cto.com/oldboy/1431161 //最好自己经历一下。
http://blog.51cto.com/oldboy/1431172

（5）优雅的关闭mysql数据库方法：
法一：
[root@oldboyedu-01 ~]# mysqladmin -uroot -poldboy123 shutdown //多实例常用；mysqladmin方法，自己写脚本可用
法二：
[root@oldboyedu-01 ~]# /etc/init.d/mysqld stop //自带的脚本
法三：
[root@oldboyedu-01 ~]# kill -USR2 `cat path/pid` //kill信号的方法

（6）mysql多实例启动与关闭
[root@oldboyedu-01 ~]# /data/3306/mysql start
Starting MySQL...
[root@oldboyedu-01 ~]# /data/3307/mysql start
Starting MySQL...
[root@oldboyedu-01 ~]# /data/3306/mysql stop
MySQL is stopped...
[root@oldboyedu-01 ~]# /data/3307/mysql stop
MySQL is stopped...

说明：脚本里/data/3306/mysql指定了关闭
启动：/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null &
关闭：${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown

=============================
2.登录mysql数据库知识深入理解
（1）mysql单实例登录方法
法一：
[root@oldboyedu-01 ~]# mysql //刚装完系统无密码登录方式
法二：
[root@oldboyedu-01 ~]# mysql -uroot //刚装完系统无密码登录方式
法三：
[root@oldboyedu-01 ~]# mysql -uroot -p //标准的dba命令行登录命令
法四：
[root@oldboyedu-01 ~]# mysql -uroot -p'oldboy123' //非脚本一般不用，明文不安全。现在的脚本支持加密！
[root@oldboyedu-01 ~]# history -d 998 //删除指定历史行
[root@oldboyedu-01 ~]# cat ~/.mysql_history
[root@oldboyedu-01 ~]# echo "HISTCONTROL=ignorespase" >>/etc/profile //强制linux把不记录敏感命令。
[root@oldboyedu-01 ~]# tail -1 /etc/profile
HISTCONTROL=ignorespase
[root@oldboyedu-01 ~]# source /etc/profile

mysql登录修改提示符：
mysql> prompt \u@oldboy \r:\m:\s->
PROMPT set to '\u@oldboy \r:\m:\s->'
root@oldboy 05:05:21->
临时生效；放在my.cnf脚本文件中[mysql]模块下添加prompt \\u@oldboy \\r:\\m:\\s->（加转义字符）后重新登录即可永久生效！

（2）mysql多实例登录方法
法一：
[root@oldboyedu-01 ~]# mysql -uroot -p -S /data/3306/mysql.sock //无密码登录，标准的dba命令行登录命令
[root@oldboyedu-01 ~]# mysql -uroot -p -S /data/3307/mysql.sock
法二：
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock //加密码登录
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3307/mysql.sock
法三：
[root@oldboyedu-01 ~]# mysql -uroot -p -h 127.0.0.1 -P3306 //多实例远程连接指定连接端口，无需指定sock路径

=============================
3.mysql数据库help帮助
linux帮助：man,help
mysql> help show
mysql> help prompt

Nothing found
Please try to run 'help contents' for a list of all accessible topics

mysql> prompt
Returning to default PROMPT of mysql>
mysql> \R
Returning to default PROMPT of mysql>
mysql> system ls /opt
cmake-2.8.8 cmake-2.8.8.tar.gz data data.zip mysql-5.5.32 mysql-5.5.32.tar.gz num.txt rh test.txt

mysql> show databases like "my%";
+----------------+
| Database (my%) |
+----------------+
| mysql |
+----------------+
1 row in set (0.00 sec)

mysql> help show grants; //查询本地
mysql> SHOW GRANTS FOR 'oldboy'@'localhost';

初学者学习linux运维的几个问题及老鸟建议
http://blog.51cto.com/oldboy/1566703

退出mysql数据库方法：
mysql> quit //Ctrl-C Ctrl-D
Bye

=============================
4.设置及修改mysql root用户密码
mysql单实例做安全措施：
a.为root设置密码；
b.删除无用的mysql库内的用户账号；
c.删除默认的存在的test数据库。
（1）单实例初始设置密码：
[root@oldboyedu-01 ~]# mysqladmin -uroot password 'oldboy456'
（2）多实例初始设置密码：
[root@oldboyedu-01 ~]# mysqladmin -u root -S /data/3308/mysql.sock password 'oldboy123'

修改密码三种方式：
法一：（需要原来密码！）
（3）单实例修改密码：
[root@oldboyedu-01 ~]# mysqladmin -uroot -poldboy123 password '123'
（4）多实例修改密码：
[root@oldboyedu-01 ~]# mysqladmin -uroot -poldboy123 password '123' -S /data/3306/mysql.sock

法二：单双实例sql语法修改密码（适合于密码丢失！）：
mysql> update mysql.user set password=password('oldboy456') where user='root' and host='localhost';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> flush privileges; //刷新到数据磁盘文件里，此前在内存里。
Query OK, 0 rows affected (0.00 sec)

法三：（适合于修改密码）
mysql> set password=password('oldboy123');
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

提示：
a.必须指定where条件；
b.必须使用password()函数来加密更改密码。

=============================
5.单实例找回丢失的mysql root用户密码
（1）启动修改丢失的单实例密码：
[root@crmn mysql-5.5.32]# /etc/init.d/mysqld stop
[root@oldboyedu-01 ~]# mysqld_safe --skip-grant-tables --user=mysql &
[root@oldboyedu-01 ~]# mysql
mysql> update mysql.user set password=password('oldboy456') where user='root' and host='localhost';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> flush privileges; //刷新到数据磁盘文件里，此前在内存里。
Query OK, 0 rows affected (0.00 sec)

必须重新启动登录生效！
[root@oldboyedu-01 ~]# mysqladmin -uroot -poldboy456 shutdown
[root@crmn mysql-5.5.32]# /etc/init.d/mysqld start
[root@oldboyedu-01 ~]# mysql -uroot -poldboy456

=============================
6.多实例找回丢失的mysql root用户密码
[root@oldboyedu-01 oldboy]# killall mysqld //关闭mysql
[root@oldboyedu-01 oldboy]# mysqld_safe --defaults-file=/data/3306/my.cnf --skip-grant-table & //启动时加--skip-grant-table参数
[1] 34286
[root@oldboyedu-01 oldboy]# 171212 23:25:11 mysqld_safe Logging to '/data/3306/mysql_oldboy3306.err'.
171212 23:25:12 mysqld_safe A mysqld process already exists

[1]+ Exit 1 mysqld_safe --defaults-file=/data/3306/my.cnf --skip-grant-table
[root@oldboyedu-01 oldboy]# mysql -u root -p -S /data/3306/mysql.sock //登录时空密码
Enter password:
......
mysql>
mysql> update mysql.user set password=password("oldboy120") where user='root'; //修改密码
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> flush privileges; //刷新权限
Query OK, 0 rows affected (0.01 sec)
重新启动登录生效！
[root@oldboyedu-01 oldboy]# /data/3306/mysql start //启动3306
MySQL is running...
[root@oldboyedu-01 oldboy]# mysql -uroot -poldboy120 -S /data/3306/mysql.sock //登录3306
......
mysql>

定制方法：源码，编译安装之后做成rpm包，统一使用。
成功最有效的方式是向有经验的人学习！

=============================
7.结构化查询语言简介-SQL（structured query language）
一种对关系数据库中的数据进行定义和操作的语言方法，是大多数关系数据库管理系统所支持的工业标准。
结构化查询语言SQL是一种数据库查询语和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。

SQL分类，分为六个部分：
（1）数据查询语言/数据检索语言（DQL--data query language）
作用：从表中获取数据，确定数据怎样在应用程序给出。
关键字：select,where,order by,group by,having等。

mysql> select user,host,password from mysql.user order by user asc; //按顺序
mysql> select user,host,password from mysql.user order by user desc; //按倒序

（2）数据操作语言（DML--data manipulation language）
语句包括动词：insert,update,delete，它们分别用于添加、修改和删除表中的行（数据），称为动作查询语言。

mysql> delete from mysql.user where user='oldboy';
Query OK, 0 rows affected (0.00 sec)

（3）事务处理语言（TPL）
它的语句确保被DML语句影响的表的所有行及时得以更新。TPL语句包括：BEGIN TRANSACTION,COMMIT和ROLLBACK。

（4）数据控制语言（DCL--data control language）
它的语句通过：GRANT（授权）或者REVOKE（收回）获得许可。

（5）数据定义语言（DDL--data definition language）
语句包括动词：CREATE和DROP,ALTER。在数据库中创建新表或删除表（CREATE TABLE或者DROP TABLE）；为表加入索引等。
DDL包括许多与人数据库目录中获得数据有关的保留字，它也是动作查询的一部分。

（6）指针控制语言（CCL--cursor control language）
它的语句像：DECLARE CURSOR,FETCH INTO和UPDATE WHERE CURRENT用于对一个或者多个表单独进行的操作。

小结：SQL语句常见分类就是这三类：
DDL--数据定义语言（CREATE,ALTER,DROP）--运维
DML--数据操作语言（SELECT,INSERT,DELETE,UPDATE）--开发
DCL--数据控制语言（GRANT,REVOKE,COMMIT,ROLLBACK）--运维

=============================
mysql数据库常见管理应用
89.创建不同字符集数据库
（1）创建数据库：
mysql> create database oldboy; //a.创建一个名为oldboy的数据库，默认是拉丁字符集。
Query OK, 1 row affected (0.00 sec)

mysql> show create database oldboy; //显示数据库oldboy
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| oldboy | CREATE DATABASE `oldboy` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create database oldboy\G //显示数据库oldboy
*************************** 1. row ***************************
Database: oldboy
Create Database: CREATE DATABASE `oldboy` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)

mysql> create database oldboy_gbk default character set gbk collate gbk_chinese_ci;//b.建立一个名为oldboy_gbk的GBK字符集数据库。
Query OK, 1 row affected (0.00 sec)

mysql> show create database oldboy_gbk;
+------------+--------------------------------------------------------------------+
| Database | Create Database |
+------------+--------------------------------------------------------------------+
| oldboy_gbk | CREATE DATABASE `oldboy_gbk` /*!40100 DEFAULT CHARACTER SET gbk */ |
+------------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create database oldboy_utf8 default character set utf8 collate utf8_general_ci;//c.建立一个名为oldboy_utf8的GBK字符集数据库。
Query OK, 1 row affected (0.00 sec)

mysql> show create database oldboy_utf8;
+-------------+----------------------------------------------------------------------+
| Database | Create Database |
+-------------+----------------------------------------------------------------------+
| oldboy_utf8 | CREATE DATABASE `oldboy_utf8` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+-------------+----------------------------------------------------------------------+
1 row in set (0.00 sec)

说明：程序下载版本字符集选择要和数据库字符集一致，可根据下载程序确定字符集（一般UTF8）。否则可能乱码！
比如：https://www.discuzz.com/login?p=signin
注意：解压编译mysql时脚本已经指定了特定字符集（前提是系统得支持该字符集），则以后创建对应的数据库就不需要指定字符集了！
-DDEFAULT_CHARSET=utf8 \ //默认的字符集；安装时缺省
-DDEFAULT_COLLATION=utf8_general_ci \ //默认的校对规则；安装时缺省
mysql> create database oldboy;

mysql> select database(); //显示当前数据库，没有指定和进入数据库么！！！
+------------+
| database() |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)

mysql> use oldboy //必须指定数据库
Database changed
mysql> select database(); //显示当前数据库
+------------+
| database() |
+------------+
| oldboy |
+------------+
1 row in set (0.00 sec)
mysql> help show //寻求帮助！

mysql> drop database oldboy; //删除数据库oldboy
Query OK, 0 rows affected (0.05 sec)

mysql> select user(); //查看当前数据库用户
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> select now(); //查看当前数据库时间
+---------------------+
| now() |
+---------------------+
| 2017-12-13 01:44:31 |
+---------------------+
1 row in set (0.00 sec)

mysql> show tables; //显示当前数据库中表
Empty set (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

=============================
10.创建mysql用户并授权
（1）mysql> help grant
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;

（2）运维常用的创建用户做法为：
a.使用grant命令在创建用户同时进行授权：
GRANT ALL ON db1.* TO 'jeffrey'@'localhost' identified by 'mypass';
等价于：
b.创建用户，再授权：
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';

说明：
grant all privileges on dbname.* to username@localhost identified by 'passwd'
授权命令对应权限目标：库和表用户名和客户端主机用户密码

eg:创建oldboy用户，对test库具备所有权限，允许从localhost主机登录管理数据库，密码是oldboy123
法一：
mysql> grant all privileges on test.* to oldboy@localhost identified by 'oldboy123';//创建用户同时授权
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;//刷新，生效
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for oldboy@localhost;//查看所授权限
+---------------------------------------------------------------------------------------------------------------+
| Grants for oldboy@localhost |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'oldboy'@'localhost' IDENTIFIED BY PASSWORD '*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'oldboy'@'localhost' |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

法二：
mysql> help create
mysql> help create user
mysql> create user oldgirl@localhost identified by 'oldgirl123';//步骤一
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for oldgirl@localhost;
+----------------------------------------------------------------------------------------------------------------+
| Grants for oldgirl@localhost |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'oldgirl'@'localhost' IDENTIFIED BY PASSWORD '*2CADADD54086D5EB4C9F10E0430084D7F179885C' |
+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> grant all on test.* to oldgirl@localhost;//步骤二
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for oldgirl@localhost;//显示无权限
+----------------------------------------------------------------------------------------------------------------+
| Grants for oldgirl@localhost |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'oldgirl'@'localhost' IDENTIFIED BY PASSWORD '*2CADADD54086D5EB4C9F10E0430084D7F179885C' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'oldgirl'@'localhost' |
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
说明：可以在其他主机上登录测试！
[root@oldboyedu-01 ~]# mysql -uoldgirl -poldgirl123

=============================
11.授权局域网内主机远程连接数据库
根据grant命令语法，test@'localhost'位置为授权访问数据库的主机，localhost可以用域名、IP地址、IP段来替代，因此要授权局域网内主机可以如下方法实现：

（1）百分号匹配法：
[root@oldboyedu-01 ~]# netstat -lntup|grep 330
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 36479/mysqld
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock
......
mysql>
mysql> create user test@'10.0.0.%' identified by 'test'; //百分号匹配法
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
[root@oldboyedu-01 ~]# mysql -utest -ptest -h 10.0.0.200 //远程连接登录，这里自己连接自己，仅为测试！工作中可以是web服务器端连接数据库端。
......
mysql>

=============================
12.mysql的授权权限列表：
mysql> show grants for oldboy@localhost;//查看现有权限
+---------------------------------------------------------------------------------------------------------------+
| Grants for oldboy@localhost |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'oldboy'@'localhost' IDENTIFIED BY PASSWORD '*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'oldboy'@'localhost' |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> help revoke
REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';
mysql> REVOKE INSERT ON test.* FROM oldboy@'localhost';//收回权限，目标库要对应。
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for oldboy@localhost;//查看现有权限
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for oldboy@localhost |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'oldboy'@'localhost' IDENTIFIED BY PASSWORD '*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515' |
| GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test`.* TO 'oldboy'@'localhost' |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "show grants for oldboy@localhost;" //-e交互式查看执行，退出到外边执行内部命令
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for oldboy@localhost |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'oldboy'@'localhost' IDENTIFIED BY PASSWORD '*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515' |
| GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test`.* TO 'oldboy'@'localhost' |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "show grants for oldboy@localhost;"|grep -i grant
Grants for oldboy@localhost
GRANT USAGE ON *.* TO 'oldboy'@'localhost' IDENTIFIED BY PASSWORD '*FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515'
GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test`.* TO 'oldboy'@'localhost'
[root@oldboyedu-01 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "show grants for oldboy@localhost;"|grep -i grant|tail -1 //现有权限列表
GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `test`.* TO 'oldboy'@'localhost'

所有权限说明：
[root@oldboyedu-01 ~]# mysql -uroot -poldboy120 -S /data/3306/mysql.sock -e "show grants for oldboy@localhost;" |grep -i grant|tail -1|tr ',' '\n' >all.txt
[root@oldboyedu-01 ~]# cat all.txt -n
1 SELECT //查询
//INSERT //插入
2 UPDATE //更新
3 DELETE //删除
4 CREATE //创建库、表
5 DROP //删除库、表
6 REFERENCES
7 INDEX //索引
8 ALTER //修改
9 CREATE TEMPORARY TABLES //创建临时表
10 LOCK TABLES //锁表
11 EXECUTE //执行
12 CREATE VIEW //创建视图
13 SHOW VIEW //显示视图
14 CREATE ROUTINE //创建存储过程
15 ALTER ROUTINE //修改存储过程
16 EVENT //事件
17 TRIGGER //触发器

mysql> desc user;
//或者mysql> desc user\G
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | YES | | | |
| authentication_string | text | YES | | NULL | |
+------------------------+-----------------------------------+------+-----+---------+-------+
42 rows in set (0.00 sec)
查看详细用户授权表条目：
mysql> select * from mysql.user;
mysql> select * from mysql.user\G

=============================
13.企业生产环境如何授权用户权限
说明：在授权时可以授权用户最小的满足业务需求的权限，而不是一味的授权“ALL PRIVILEGES”
（1）博客，CMS等产品的数据库授权：
对于web连接用户授权尽量采用最小化原则，很多开源软件都是web界面安装，因此在安装期间除了select,insert,update,delete4个权限外，还需要create,drop等比较危险的权限。
常规情况下权限select,insert,update,delete4个权限即可，有的开源软件例如discuz bbs,还需要create,drop等比较危险的命令。
（2）生成数据表后，要收回create,drop授权。
（3）生产环境针对主库（写为主读为辅）用户的授权。
待定。。。

mysql> create database oldboy; //创建测试数据库oldboy
Query OK, 1 row affected (0.00 sec)

mysql> show create database oldboy;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| oldboy | CREATE DATABASE `oldboy` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create database oldboy\G
*************************** 1. row ***************************
Database: oldboy
Create Database: CREATE DATABASE `oldboy` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)
（1）建表语法：
create table <表名>(
<字段名1><类型1>,
......
<字段名n><类型n>
);
（2）建表语句：
mysql> use oldboy //使用数据库oldboy
Database changed
mysql> create table student( //建表
-> id int(4) not null,
-> name char(20) not null,
-> age tinyint(2) not null default '0',
-> dept varchar(16) default null
-> );
Query OK, 0 rows affected (0.11 sec)

mysql> show create table student;//显示该表内容
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`id` int(4) NOT NULL,
`name` char(20) NOT NULL,
`age` tinyint(2) NOT NULL DEFAULT '0',
`dept` varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.17 sec)
mysql> show create table student\G//显示该表内容
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`id` int(4) NOT NULL,
`name` char(20) NOT NULL,
`age` tinyint(2) NOT NULL DEFAULT '0',
`dept` varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
至此可以对该新建的表进行增删改查操作了。

小结：
mysql表的字段类型：
数字类型：
tinyint 1,smallint 2,mediumint 3,int 4,integer 4,bigint 8,float 4,float(x):若x<=24等于4；若25<=x<=53等于8,double 8
（1）INT[(M)]型：正常大小整数类型。
（2）CHAR(M)型：定长字符串类型，当存储时，总是用空格填满右边到指定的长度。
（3）VARCHAR型：变长字符串类型。
具体见手册。

小结：
mysql> desc student; //查看建立的表结构
mysql> desc student\G//垂直查看
mysql> show columns from student; //查看建立的表结构
mysql> show columns from student\G; //垂直查看

=============================
16.索引知识及索引创建多种方法实践
为表的字段创建索引
索引像书的目录一样，如果在字段上建立了索引，以索引列为查询条件时可以加快查询数据的速度，这是mysql优化的重要内容之一。
索引分类：
（1）创建主键索引
查询数据库，按照主键查询是最快的，每个表只能有一个主键列，但是可以有多个普通索引列。主键要求列的所有内容必须唯一，而普通索引列不要求内容必须唯一。
主键类似于我们的学号，班级内唯一。整个表的每一条记录的主键值在表内是唯一的，用来唯一标识一条记录。
主键就像班级的学号，无论建立主键索引还是普通索引，都要在表的对应列上创建，可以对单列建索引，也可以对多列创建索引。

mysql> drop table student;//删除之前建好的表
Query OK, 0 rows affected (0.05 sec)

mysql> desc student;
ERROR 1146 (42S02): Table 'oldboy.student' doesn't exist
法一：建表时增加主键索引：
mysql> create table student(
-> id int(4) not null AUTO_INCREMENT,
-> name char (20) not null,
-> age tinyint(2) NOT NULL default '0',
-> dept varchar(16) default NULL,
-> primary key(id), //主键索引
-> KEY index_name (name)//普通索引
-> );
Query OK, 0 rows affected (0.08 sec)

mysql> desc student; //查看新建立的表
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | MUL | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
法二：建表后通过alter命令增加主键索引（之前忘加了）：
mysql> drop table student;
Query OK, 0 rows affected (0.06 sec)

mysql> desc student;
ERROR 1146 (42S02): Table 'oldboy.student' doesn't exist
mysql> create table student(
-> id int(4) not null,
-> name char (20) not null,
-> age tinyint(2) NOT NULL default '0',
-> dept varchar(16) default NULL,
-> KEY index_name (name)
-> );
Query OK, 0 rows affected (0.06 sec)

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | NO | | NULL | |
| name | char(20) | NO | MUL | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table student change id id int primary key auto_increment;//建表后通过alter命令增加主键索引
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | MUL | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

（2）创建普通索引（包含唯一索引和不唯一索引（真正普通索引））
法一：
同上
法二：建表后利用alter增加普通索引
mysql> alter table student drop index index_name;//删除建表时创建的index_name普通索引
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> alter table student add index index_name(name);//在name列上添加索引，索引名为index_name
Query OK, 0 rows affected (0.23 sec)
Records: 0 Duplicates: 0 Warnings: 0

（3）对字段的前n个字符创建普通索引
当遇到表中比较大的列时，列内容的前n个字符在所有内容中已经接近唯一时，这时可以对列的前n个字符建立索引，而无需对整个列建立索引，这样可以节省创建索引占用的系统空间，以及降低读取和更新维护索引消耗的系统资源。
mysql> create index index_dept on student(dept(8));//dept列的前8个字符建立普通索引
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | MUL | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> show index from student\G //查看索引更详细。

（4）为表的多个字段创建联合索引
若查询数据的条件是多列时，可以为多个查询的列创建联合索引；甚至可以为多列的前n个字符列创建联合索引。
联合索引允许列上面有自己的索引。
mysql> create index ind_name_dept on student(name,dept);//a.多个查询的列创建联合索引
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | MUL | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> show index from student \G //查看索引更详细。

mysql> drop index ind_name_dept on student;//删除联合索引
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> create index ind_name_dept on student(name(8),dept(10));//b.多列的前n个字符列创建联合索引
Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from student \G

（5）创建唯一索引（非主键）
mysql> create unique index uni_ind_name on student(name);//创建唯一索引
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | UNI | NULL | |
| age | tinyint(2) | NO | | 0 | |
| dept | varchar(16) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

=============================
17.索引列的创建及生效条件
索引不但占用系统空间，更新数据库时还需要维护索引数据的，因此，索引是一把双刃剑，并不是越多越好。
例如：数十到几百行的小表上无需建立索引，写频繁，读少的业务要少建立索引。

select user,host from mysql.user where host=...，索引一定要创建在where后的条件列上，而不是select后的选择数据列。另外我们要尽量选择在唯一值多的大表上建立索引。

小结：
（1）要在表的列上创建索引；
（2）索引会加快查询速度，但是会影响更新的速度；
（3）索引不是越多越好，要在频繁查询的where后的条件列上创建索引；
（4）小表或者唯一值极少的列上不建索引，要在大表以及不同内容多的列上创建索引。
（5）
grant all privileges on test.* to oldboy@localhost identified by 'oldboy123';

create user oldgirl@localhost identified by 'oldgirl123';
grant all on test.* to oldgirl@localhost;

show grants for oldboy@localhost;
（6）
desc student;
建表后通过alter命令增加主键索引，主键名为key（法二）：
alter table student change id id int primary key auto_increment;
删除建表时创建的主键索引：
alter table student drop primary key;//法一

在name列上添加普通索引，索引名为index_name（法二）：
alter table student add index index_name(name);
删除建表时创建的普通索引:
alter table student drop index index_name;

dept列的前8个字符建立普通索引:
create index index_dept on student(dept(8));
a.多个查询的列创建联合索引:
create index ind_name_dept on student(name,dept);
删除联合索引:
drop index ind_name_dept on student;//法二

b.多列的前n个字符列创建联合索引:
create index ind_name_dept on student(name(8),dept(10));
删除联合索引:
drop index ind_name_dept on student;

创建唯一索引:
create unique index uni_ind_name on student(name);

posted @ 2018-01-18 21:01 bkycrmn 阅读(185) 评论(0) 收藏举报

刷新页面返回顶部

4.mysql应用管理实践(1)

公告