mysql（在linux环境中）

一、什么是mysql?

一种关系型数据库管理系统

二、使用mysql的软件环境

1、安装软件
apache(就是httpd)
mysql-server(mysql连接到服务器，用来管理mysql)
php-mysql(php连接到mysql)
mysql

三、mysql

1、初始化mysql默认的数据

/etc/init.d/mysql restart

2、开始使用

[root@myp ~]# mysql_secure_installation （进入mysql的使用向导）
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!


In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
#为root用户（不是系统的root用户）输入密码
Enter current password for root (enter for none):  

按照向导使用完之后，重启。

/etc/init.d/mysql restart

• mysql的客户端使用
  mysql -u 主机名
  -p 密码
  -h 主机
  -p 协议
• mysql的服务端的使用

#登录成功的页面（如果输入密码登录不成功的话，就再进入向导修改密码）
[root@myp ~]# mysql -u root -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

• 服务端常用命令
• mysql>
  show databases: 列出所有的数据库
  show tables: 列出所有表
  show engines: 查看引擎信息
  show character set:查看字符集
  use DB_NAME(数据库名):切换当前数据库
  select * from user（table name） :列出表中的内容.

四、常用的sql语句(进入mysql使用)

1、创建数据库：CREATE DATABASE dbname(数据库名字)
2、删除：DROP DATABASE dbname

• 实现用户的授权和撤权
  授权
  格式： grant pri1,pri2,pri3 on db_name.tbname to ‘username’ @ ‘host’ identified by ‘passwd’
  一般使用为：
  grant all on db_name.* to ‘username’ @ ‘host(其他主机也可以)’ identified by ‘passwd’
  撤权
  revoke pri1,pri2,pri3 on db_name.tbname to ‘username’ @ ‘host’ identified by ‘passwd’
  修改之后都要刷新权限

flush privileges

五、实际使用

1、查看数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
| wordb              |
+--------------------+
4 rows in set (0.00 sec)

2、切换数据库

mysql> use mysql

3、查看当前数据库列表

mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
23 rows in set (0.00 sec)

4、授权某个用户访问mysql

grant all on extradb.* to 'myp'@'192.168.160.9（客户端主机）' identified by '123';

5、查看已经授权的用户

mysql> select * from user;

6、从客户端（192.168.160.9）登录

[root@bogon ~]# mysql -u myp -p -h 192.168.160.3

(但是好像不能访问数据库mysql)

mysql> use mysql
ERROR 1044 (42000): Access denied for user 'myp'@'192.168.160.9' to database 'mysql'
mysql> use test
Database changed
mysql> use wordb
Database changed