首页 > 代码库 > linux命令：MYSQL、PHP相关概念及安装配置
linux命令：MYSQL、PHP相关概念及安装配置
PHP源码目录结构：
php的源码在结构上非常清晰，其代码根目录中主要包含了一些说明文件以及设计方案，
并提供了如下子目录：
1、build -- 顾名思义，这里主要放置一些跟源码编译相关的文件。
2、ext -- 官方的扩展目录，包括了绝大多数PHP的函数的定义和实现，如array系列，pdo系列，sql 系列等函数的实现。
3、main -- 这里存放的是PHP最为核心的文件了，是实现PHP的基础设施，这里和Zend引擎不一样
Zend引擎主要实现语言最核心的语言运行环境。
4、Zend -- Zend引擎的实现目录，比如脚本的词法语法解析，opcode的执行以及扩展机制的实现
5、pear -- PHP扩展与应用仓库，包含PEAR的核心文件。
6、sapi -- 包含了各种服务器抽象层的代码，例如apache的mod_php,cgi，fastcgi以及fpm
7、TSRM -- PHP的线程安全是构建在TSRM库之上的，PHP实现中常见的*G宏通常是对TSRM的封装
8、tests -- PHP的测试脚本集合，包含PHP各项功能的测试文件。
9、win32 -- 这个目录主要包括windows平台相关的一些实现。
MYSQL数据库功能：
1、数据库创建、删除
2、创建表、删除表、修改表
3、索引的创建、删除
4、用户和权限
5、数据增、删、改
6、查询
MySQL二进制程序：（mysql数据库的database存放路径为/var/lib/mysql目录中）
客户端命令：mysql
参数：
-u USERNAME #以那个用户连接mysql （不指定默认用户为root）
-p #指定输入用户密码 (不指定默认是空密码)
-h MYSQL_SERVER #指定mysql服务器主机 （不指定默认为localhost本机）
Usage: mysql -uroot -p -h 10.109.134.249 -uroot 指定用户
-p：输入用户密码 -h：指定连接那台mysql服务器（ip地址）
mysql客户端：
交互式模式：一步一步的手动输入执行
批处理模式：执行mysql脚本，批处理执行
交互式模式中的命令类别：
客户端命令：?(获取帮助) quit(退出mysql客户端)
服务器端命令：都必须使用语句结束符，默认结束符为分号；
Usage：SHOW DATABASES; #查看数据库
关系数据库对象：库、表、索引、视图、约束、存储过程、存储函数、触发器、游标、
用户、权限、事务。
数据库最基本的对象-->表：行(row)，列（field,column）
服务端命名：mysqld
Mysql常用命令不区分大小写，但是需保持一致（都大写或者都小写）：
DDL：定义数据对象，关键字：CREATE(创建)、ALTER(修改)、DROP(删除)
* 这些命令主要作用的范围：数据库，表，表的字段
创建用户：
CREATE USER ‘USERNAME‘@‘HOST‘ IDENTIFIED BY ‘PASSWORD‘; #可以不指定密码
USERNAME：用户名 HOST：主机
IDENTIFIED：指定密码 BY‘密码’
删除用户：
DROP USER ‘USERNAME‘@‘HOST‘; 删除用户（需具体指定删除那个用户）
为用户设定密码方法：（建议用1和3这两种方法设定或修改密码）
*1、mysql>SET PASSWORD FOR ‘USERNAME‘@‘HOST‘=PASSWORD(‘PASSWORD‘)
Usage: SET PASSWORD FOR ‘root‘@‘localhost‘=PASSWORD(‘123456‘) #设定
root@localhost用户名密码为123456
2、直接在linux下输入，不需要进入mysql客户端输入（此方法容易导致修改相同用户的密码）：
# mysqladmin -uUSERNAME -hHOST -p password ‘password‘ #设定用户USERNAME
主机HOST 密码为password
Usage: [root@johntest ~]#mysqladmin -uroot -h10.109.134.249 -p password
‘123456‘ #设定10.109.134.249主机的root用户密码为123456
*3、直接修改表中的内容：
mysql>UPDATE user SET password=PASSWORD(‘password‘) WHERE USER=‘root‘
AND Host=‘10.109.134.249‘; #设定 主机10.109.134.249的数据库root用户密码
AND与关系（需同时满足） OR或关系（两者满足一个即可） NOT非关系（取反）
HOST可以使用的格式：
IP ， HOSTNAME(主机名)，NETWORK(网络地址)，通配符(需用‘通配符‘引号)
通配符包含：
_：匹配任意单个字符，172.16.0._
%：匹配任意字符
Usage:
创建数据库：CREATE DATABASE IF NOT EXISTS db_name; 如果数据库不存在
创建db_name数据库
创建表：CREATE TABLE tb_name(col1,col2,...); 创建tb_name表 col表示字段
查看库中的表：SHOW TABLES FROM db_name 查看db_name数据库中的表
查看表的结构：DESC tb_name 查看tb_name表的结构
删除表：DROP TABLE IF EXISTS tb_name 如果表存在删除tb_name表
修改表：ALTER TABLE tb_nameMODIFY：修改某字段（变更属性，权限）
CHANGE: 改变某字段 （变更字段名称）
ADD：增加某字段
DROP : 删除某字段
Usage: ALTER TABLE students ADD course VARCHAR(100); #对表students新增course字段且VARCHAR(100)空间长度为100
DML：数据操纵语言，关键字：INSERT(插入)、DELETE(删除)、UPDATE(更新、修改)
* 这些命令主要作用的范围：针对表中字段的值进行操作
插入数据： INSERT INTO tb_name (col1,col2,...) VALUES|VALUE (‘STRING‘,NUM,...), (‘STRING‘,NUM,...); col表示字段
更新、修改数据： UPDATE tb_name SET column=value WHERE ..... ; WHERE表示修改那个
具体数据，并不是把column整个这个字段的值修改为value.
Usage: UPDATE students SET Course=‘Jiuyinzhenjing‘ WHERE Name=‘LHC‘; #表示修改表students中Name值为LHC的Course字段的值变为‘Jiuyinzhenjing’
DCL：数据库控制语言 ，关键字：GRANT(允许、授权)、REVOKE(取消权限)
ALL PRIVILEGES：表示所有权限
GRANT给用户授权:
Usage: GRANT pri1,pri2,... ON DB_NAME.TB_NAME TO ‘USERNAME‘@‘HOST‘ IDENTIFIED BY ‘PASSWORD‘ #给DB_NAME数据库中的TB_NAME表中的USERNAME@‘HOST‘用户授予pri1，pri2等权限也可以设定密码IDENTIFIED BY ‘PASSWORD‘(如果该用户不存在，会直接新建该用户并授权)
REVOKE取消用户权限：
Usage: REVOKE pri1,pri2,... ON DB_NAME.TB_NAME FROM ‘USERNAME‘@‘HOST‘; #取消DB_NAME数据库中的TB_NAME表中的‘USERNAME‘@‘HOST‘用户的pri1，pri2等权限
查看用户的授权： SHOW GRANTS FOR ‘USERNAME‘@‘HOST‘;
刷新用户权限列表： FLUSH PRIVILEGES;
DQL：数据库查询语言，关键字：SELECT(挑选，选择)
SELECT 字段 FROM tb_name WHERE CONDITION;
*：表示所有字段
WHERE：没有条件表示显示所有行；
Usage: SELECT Name,Course FROM students WHERE Gender=‘M‘;
#表示只选择表students中的Gender字段值为M的Name和Course字段的信息
php、mysql基础
需先安装httpd服务，使得web服务器启用。
一、安装PHP
[root@johntest ~]# ls #先下载rpm安装包
php53-5.3.3-23.el5_10.i386.rpm sysroo1.gz
Desktop inittab.des3 php53-cli-5.3.3-23.el5_10.i386.rpm sysroot
php53-common-5.3.3-23.el5_10.i386.rpm php53-mbstring-5.3.3-23.el5_10.i386.rpm
[root@johntest ~]# rpm -ivh php53-common-5.3.3-23.el5_10.i386.rpm
warning: php53-common-5.3.3-23.el5_10.i386.rpm: Header V3 DSA signature: NOKEY, key ID e8562897
Preparing... ########################################### [100%]
1:php53-common ########################################### [100%]
[root@johntest ~]# rpm -ivh php53-cli-5.3.3-23.el5_10.i386.rpm
warning: php53-cli-5.3.3-23.el5_10.i386.rpm: Header V3 DSA signature: NOKEY, key ID e8562897
Preparing... ########################################### [100%]
1:php53-cli ########################################### [100%]
[root@johntest ~]# rpm -ivh php53-mbstring-5.3.3-23.el5_10.i386.rpm
warning: php53-mbstring-5.3.3-23.el5_10.i386.rpm: Header V3 DSA signature: NOKEY, key ID e8562897
Preparing... ########################################### [100%]
1:php53-mbstring ########################################### [100%]
[root@johntest ~]# rpm -ivh php53-5.3.3-23.el5_10.i386.rpm
warning: php53-5.3.3-23.el5_10.i386.rpm: Header V3 DSA signature: NOKEY, key ID e8562897
Preparing... ########################################### [100%]
1:php53 ########################################### [100%]
[root@johntest ~]# rpm -ql php53 #查看安装后的相关目录
/etc/httpd/conf.d/php.conf
/usr/lib/httpd/modules/libphp5.so
/var/lib/php/session
/var/www/icons/php.gif
[root@johntest conf.d]# service httpd restart #重启httpd服务
Stopping httpd: [ OK ]
Starting httpd: [ OK ]
[root@johntest conf.d]# cd /var/www/a.com/
[root@johntest a.com]# ls
index.html
[root@johntest a.com]# mv index.html index.php
[root@johntest a.com]# vi index.php
<title>A</title>
<h1>test.a.com<h1>
<?php #红色三行为嵌入PHP程序固定格式,每个语句必须以;结尾
phpinfo();
?>
二、安装mysql
[root@johntest a.com]# yum -y install mysql-server #安装mysql
...
Installing : perl-DBI 1/4
Installing : mysql 2/4
Installing : perl-DBD-MySQL 3/4
Installing : mysql-server 4/4
...
Complete!
[root@johntest ~]# service mysqld start #启用mysql
初始化 MySQL 数据库： Installing MySQL system tables...
OK
Filling help tables...
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password ‘new-password‘
/usr/bin/mysqladmin -u root -h johntest.com password ‘new-password‘
Alternatively you can run:
/usr/bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the manual for more instructions.
You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl
Please report any problems with the /usr/bin/mysqlbug script!
The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com
[确定]
启动 MySQL： [确定]
[root@johntest ~]# mysql #运行mysql客户端命令
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.77 Source distribution
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the buffer.
mysql> SHOW DATABASES; #查看数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.01 sec)
mysql> CREATE DATABASE IF NOT EXISTS testdb; #创建数据库如果数据库不存在
Query OK, 1 row affected (0.01 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
| testdb |
+--------------------+
4 rows in set (0.00 sec)
mysql>DROP DATABASE testdb; #删除数据库后无法恢复
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE DATABASE IF NOT EXISTS mydb; #如果mydb数据库不存在，则建立该数据库
Query OK, 1 row affected (0.00 sec)
mysql> SHOW DATABASES; #查看mysql数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> USE mydb #设定客户端默认数据库为mydb(此命令为客户端命令)
Database changed
mysql> CREATE TABLE students(Name CHAR(10) NOT NULL,Age TINYINT UNSIGNED,Gender CHAR(1) NOT NULL); #创建students表包含Name(姓名CHAR(10)长度为10 NOT NULL表示不能为空)，
Age(年龄 TINYINT一个字节 UNSIGNED不能为负数），Gender(性别 CHAR(1)一个字符)
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW TABLES; #查看表
+----------------+
| Tables_in_mydb | #在mydb数据库中有students表
+----------------+
| students |
+----------------+
1 row in set (0.00 sec)
mysql> DESC students; #查看表结构
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| Name | char(10) | NO | | NULL | |
| Age | tinyint(3) unsigned | YES | | NULL | |
| Gender | char(1) | NO | | NULL | |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> ALTER TABLE students ADD course VARCHAR(100); #对表students新增course字段且空间（VARCHAR(100)） 长度为100
Query OK, 0 rows affected (0.01 sec) #新增表字段
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC students;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| Name | char(10) | NO | | NULL | |
| Age | tinyint(3) unsigned | YES | | NULL | |
| Gender | char(1) | NO | | NULL | |
| course | varchar(100) | YES | | NULL | |
+--------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> ALTER TABLE students CHANGE course Course VARCHAR(100) AFTER Name; #变更字段course 的名称为Course并把该字段放置在Name字段后面
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC students;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| Name | char(10) | NO | | NULL | |
| Course | varchar(100) | YES | | NULL | |
| Age | tinyint(3) unsigned | YES | | NULL | |
| Gender | char(1) | NO | | NULL | |
+--------+---------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> INSERT INTO students (Name,Gender) VALUE (‘LHC‘,‘M‘),(‘XLN‘,‘F‘); #在表students中Name和Gender 字段增加2个用户，其值分别为姓名LHC，性别M；姓名XLN,性别F；
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM students; #查询表中所有字段的信息
+------+--------+------+--------+
| Name | Course | Age | Gender |
+------+--------+------+--------+
| LHC | NULL | NULL | M |
| XLN | NULL | NULL | F |
+------+--------+------+--------+
2 rows in set (0.00 sec)
mysql> INSERT INTO students VALUES (‘XiaoXiangzi‘,‘HaMagong‘,‘57‘,‘M‘); #在表students中增加 用户XiaoXiangzi,课程HaMagong,年龄57，性别M的行
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> SELECT * FROM students;
+------------+----------+------+--------+
| Name | Course | Age | Gender |
+------------+----------+------+--------+
| LHC | NULL | NULL | M |
| XLN | NULL | NULL | F |
| XiaoXiangz | HaMagong | 57 | M |
+------------+----------+------+--------+
3 rows in set (0.00 sec)
mysql> UPDATE students SET Course=‘Jiuyinzhenjing‘ WHERE Name=‘LHC‘;
#表示修改表students中Name值为LHC的Course字段的值变为‘Jiuyinzhenjing’
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM students;
+------------+----------------+------+--------+
| Name | Course | Age | Gender |
+------------+----------------+------+--------+
| LHC | Jiuyinzhenjing | NULL | M |
| XLN | NULL | NULL | F |
| XiaoXiangz | HaMagong | 57 | M |
+------------+----------------+------+--------+
3 rows in set (0.00 sec)
mysql> UPDATE students SET Age=‘18‘; #把表students中Age字段的值全部变更为18
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> SELECT * FROM students; #查询表中所有字段的信息
+------------+----------------+------+--------+
| Name | Course | Age | Gender |
+------------+----------------+------+--------+
| LHC | Jiuyinzhenjing| 18 | M |
| XLN | NULL | 18 | F |
| XiaoXiangz | HaMagong | 18 | M |
+------------+----------------+------+--------+
3 rows in set (0.00 sec)
mysql> SELECT Name,Course FROM students WHERE Gender=‘M‘;
#表示只选择表students中的Gender字段值为M的Name和Course字段的信息
+------------+----------------+
| Name | Course |
+------------+----------------+
| LHC | Jiuyinzhenjing |
| XiaoXiangz | HaMagong |
+------------+----------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM students; #查询表students的信息
+------------+----------------+------+--------+
| Name | Course | Age | Gender |
+------------+----------------+------+--------+
| LHC | Jiuyinzhenjing | 18 | M |
| XLN | NULL | 18 | F |
| XiaoXiangz | HaMagong | 18 | M |
+------------+----------------+------+--------+
3 rows in set (0.00 sec)
mysql> DELETE FROM students WHERE Name=‘XLN‘; #删除表students中Name字段值为XLN的行
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM students; #查询表students的信息
+------------+----------------+------+--------+
| Name | Course | Age | Gender |
+------------+----------------+------+--------+
| LHC | Jiuyinzhenjing | 18 | M |
| XiaoXiangz | HaMagong | 18 | M |
+------------+----------------+------+--------+
2 rows in set (0.00 sec)
mysql> DELETE FROM students WHERE Course=‘jiuyinzhenjing‘; #删除表students中Course字段值 为jiuyinzhenjing的行
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM students; #查询表students的信息
+------------+----------+------+--------+
| Name | Course | Age | Gender |
+------------+----------+------+--------+
| XiaoXiangz | HaMagong | 18 | M |
+------------+----------+------+--------+
1 row in set (0.00 sec)
mysql> \q #退出mysql程序
Bye
三、创建mysql用户
mysql> CREATE USER ‘jerry‘@‘%‘ IDENTIFIED BY ‘jerry‘; #创建jerry用户，密码为jerry
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR ‘jerry‘@‘%‘; #查看jerry用户的授权
+-----------------------------------------------------------------------------+
| Grants for jerry@% |
+-----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO ‘jerry‘@‘%‘ IDENTIFIED BY PASSWORD ‘7e82afb618ffeb73‘ |
+-----------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON mydb.* TO ‘jerry‘@‘%‘; #给用户jerry授权mydb数据库的所有权限
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR ‘jerry‘@‘%‘; #查看jerry用户的授权
+-----------------------------------------------------------------------------+
| Grants for jerry@% |
+-----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO ‘jerry‘@‘%‘ IDENTIFIED BY PASSWORD ‘7e82afb618ffeb73‘ |
| GRANT ALL PRIVILEGES ON `mydb`.* TO ‘jerry‘@‘%‘ |
+-----------------------------------------------------------------------------+
2 rows in set (0.00 sec)
[root@xuelinux ~]# mysql -ujerry -p -h10.109.134.249 #换一台主机远程登录
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.0.77 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> SHOW DATABASES; #成功登陆到10.109.134.249mysql数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| test |
+--------------------+
3 rows in set (0.00 sec)
mysql> \q #退出mysql程序
Bye
四、远端空密码无法登录问题：
先切换到远端mysql客户端主机:
[root@xuelinux ~]# mysql #没有指定需要连接的mysql主机
ERROR 2002 (HY000): Can‘t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock‘ (2)
[root@xuelinux ~]# mysql -uroot -p -h10.109.134.249 #指定以root用户登录 10.109.134.249mysql主机
Enter password: #默认root初始密码为空
ERROR 1045 (28000): Access denied for user ‘root‘@‘10.109.134.236‘ (using password: NO)
#无法远端登录，提示密码为空
查看mysql数据库中的root授权信息：
mysql> SHOW DATABASES; #先查看数据库中有哪些库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql | #mysql为主库
| test |
| testdb |
+--------------------+
5 rows in set (0.00 sec)
mysql> USE mysql #设定默认数据库为mysql库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SHOW TABLES; #查看库中的表
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| func |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| proc |
| procs_priv |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
17 rows in set (0.00 sec)
mysql> DESC user; #查看表user结构
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
+-----------------------+-----------------------------------+------+-----+---------+-------+
37 rows in set (0.00 sec)
mysql> SELECT User,Host,Password FROM user; #查看表user中包含User,Host,Password的字段
+----------+--------------+------------------+
| User | Host | Password |
+----------+--------------+------------------+
| root | localhost | | #红色部分表示root用户只能在loclhost，
| root | johntest.com | | johntest.com,127.0.0.1这些主机上登录
| root | 127.0.0.1 | | 且都没有设置密码。
| | localhost | |
| | johntest.com | |
| jerry | % | 7e82afb618ffeb73 |
| testuser | % | 0d59dfc910205df0 |
+----------+--------------+------------------+
7 rows in set (0.00 sec)
mysql> SET PASSWORD FOR ‘root‘@‘localhost‘=PASSWORD(‘123456‘); #设定root@localhost用户的
密码为123456
Query OK, 0 rows affected (0.02 sec)
mysql> SELECT User,Host,Password FROM user; #查看表user中包含User,Host,Password的字段
+----------+--------------+------------------+
| User | Host | Password |
+----------+--------------+------------------+
| root | localhost | 565491d704013245 | #此处已经生成了密码加密串
| root | johntest.com | |
| root | 127.0.0.1 | |
| | localhost |
| | johntest.com| |
| jerry | % | 7e82afb618ffeb73 |
| testuser | % | 0d59dfc910205df0 |
+----------+--------------+------------------+
7 rows in set (0.00 sec)
mysql> FLUSH PRIVILEGES; #刷新数据库授权列表
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE user SET Password=PASSWORD(‘123456‘) WHERE USER=‘root‘ AND Host=‘127.0.0.1‘;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT User,Host,Password FROM user;
+----------+--------------+------------------+
| User | Host | Password |
+----------+--------------+------------------+
| root | localhost | 565491d704013245 |
| root | johntest.com | |
| root | 127.0.0.1 | 565491d704013245 |
| | localhost | |
| | johntest.com | |
| jerry | % | 7e82afb618ffeb73 |
| testuser | % | 0d59dfc910205df0 |
+----------+--------------+------------------+
7 rows in set (0.00 sec)
mysql> FLUSH PRIVILEGES; #刷新数据库授权列表
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO ‘root‘@‘10.109.%.%‘ IDENTIFIED BY ‘redhat‘; #设定10.109段所有主机都可以通过root用户登录到该数据库，并拥有所有权限。
Query OK, 0 rows affected (0.00 sec)
切换到10.109段IP地址的远程主机，通过远程登录到数据库：
[root@xuelinux ~]#mysql -uroot -p -h10.109.134.249； #远程通过root用户登录到
10.109.134.249数据库
Enter password: #输入密码
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.0.77 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> SHOW DATABASES; #查看数据库信息
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| test |
| testdb |
+--------------------+
5 rows in set (0.00 sec)
mysql> SHOW GRANTS; #查看授权信息
+----------------------------------------------------------------------------------------------+
| Grants for root@10.109.%.% |
+----------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO ‘root‘@‘10.109.%.%‘ IDENTIFIED BY PASSWORD ‘27c30f0241a5b69f‘ |
+----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> FLUSH PRIVILEGES; #刷新数据库授权列表
Query OK, 0 rows affected (0.00 sec)
mysql> quit
[root@johntest ~]# ps -au mysql #6 查看mysql进程且清除
PID TTY TIME CMD
9585 pts/1 00:00:00 mysqld_safe
9618 pts/1 00:00:00 mysqld
9658 pts/0 00:00:00 ps
[root@johntest ~]# kill -9 9585
[root@johntest ~]# ps -au mysql
PID TTY TIME CMD
9618 pts/1 00:00:00 mysqld
9659 pts/0 00:00:00 ps
[root@jacktest ~]# kill -9 9618
五、基础练习
1、新建如下表（包括结构和内容，所属库为testdb）
ID Name Age Gender Course
1 Ling Huchong 24 Male Hamagong
2 Huang Rong 19 Female Da gougunfa
3 Lu Wushuang 18 Female Jiuyang shengong
4 Zhu Ziliu 52 Male Pixie Jianfa
5 Chen Jialuo 22 Male Xianglong Shiba zhang
[root@johntest ~]# mysql #进入mysql客户端
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.0.77 Source distribution
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the buffer.
mysql> CREATE DATABASES IF NOT EXISTS testdb; #创建testdb数据库
Query OK, 1 row affected (0.01 sec)
mysql> SHOW DATABASES; #查看数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| test |
| testdb |
+--------------------+
5 rows in set (0.00 sec)
mysql> USE testdb #设定testdb为默认数据库
Database changed
mysql> CREATE TABLE students(ID TINYINT(6) NOT NULL,Name CHAR(10) NOT NULL,Age TINYINT(3) UNSIGNED,Gender CHAR(1) NOT NULL,Course VARCHAR(100)); #在默认的数据库中新建表students，包含ID Name
Age Gender Course字段，并多每个字段做一些空间长度等限定
Query OK, 0 rows affected (0.01 sec)
mysql> DESC students; #查看表students的结构信息
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| ID | tinyint(6) | NO | | NULL | |
| Name | char(10) | NO | | NULL | |
| Age | tinyint(3) unsigned | YES | | NULL | |
| Gender | char(1) | NO | | NULL | |
| Course | varchar(100) | YES | | NULL | |
+--------+---------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> INSERT INTO students VALUES (‘1‘,‘Ling Huchong‘,‘24‘,‘Male‘,‘Hamagong‘); #在表 students中新增一行1 Ling Huchong 24 Male Hamagong
Query OK, 1 row affected, 2 warnings (0.00 sec)
mysql> INSERT INTO students VALUES (‘2‘,‘Huang Rong‘,‘19‘,‘Female‘,‘Da gougunfa‘);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> INSERT INTO students VALUES(‘3‘,‘Lu Wushuang‘,‘18‘,‘Female‘,‘Jiuyang shengong‘);
Query OK, 1 row affected, 2 warnings (0.00 sec)
mysql> INSERT INTO students VALUES(‘4‘,‘Zhu Ziliu‘,‘52‘,‘Male‘,‘Pixie Jianfa‘);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> INSERT INTO students VALUES(‘5‘,‘Chen Jialuo‘,‘22‘,‘Male‘,‘Xianglong Shiba zhang‘);
Query OK, 1 row affected, 2 warnings (0.00 sec)
mysql> SELECT * FROM students; #查看表中字段的内容
+----+------------+------+--------+-----------------------+
| ID | Name | Age | Gender | Course |
+----+------------+------+--------+-----------------------+
| 1 | Ling Hucho | 24 | M | Hamagong |
| 2 | Huang Rong | 19 | F | Da gougunfa |
| 3 | Lu Wushuan | 18 | F | Jiuyang shengong |
| 4 | Zhu Ziliu | 52 | M | Pixie Jianfa |
| 5 | Chen Jialu | 22 | M | Xianglong Shiba zhang |
+----+------------+------+--------+-----------------------+
5 rows in set (0.00 sec)
5.1. 找出性别为女性的所有人：
mysql> SELECT * FROM testdb.students WHERE Gender=‘F‘;#查找testdb数据库students表中所有女性
+----+------------+------+--------+------------------+
| ID | Name | Age | Gender | Course |
+----+------------+------+--------+------------------+
| 2 | Huang Rong | 19 | F | Da gougunfa |
| 3 | Lu Wushuan | 18 | F | Jiuyang shengong |
+----+------------+------+--------+------------------+
2 rows in set (0.00 sec)
5.2. 找出年龄大于30的所有人；
mysql> SELECT * FROM testdb.students WHERE Age>‘30‘; #查找表中所有年龄大于30岁的
+----+-----------+------+--------+--------------+
| ID | Name | Age | Gender | Course |
+----+-----------+------+--------+--------------+
| 4 | Zhu Ziliu | 52 | M | Pixie Jianfa |
+----+-----------+------+--------+--------------+
1 row in set (0.01 sec)
5.3. 修改Zhu ziliu的课程Course为Kuihua Baodian;
mysql> UPDATE testdb.students SET Course=‘Kuihua Baodian‘ WHERE Name=‘Zhu Ziliu‘;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM testdb.students WHERE Name=‘Zhu Ziliu‘;#查找表中Name字段值为Zhu Ziliu的行
+----+-----------+------+--------+----------------+
| ID | Name | Age | Gender | Course |
+----+-----------+------+--------+----------------+
| 4 | Zhu Ziliu | 52 | M | Kuihua Baodian |
+----+-----------+------+--------+----------------+
1 row in set (0.00 sec)
5.4. 删除年龄小于等于19的所有人
mysql> DELETE FROM testdb.students WHERE Age<=‘19‘;
Query OK, 2 rows affected (0.00 sec)
mysql> SELECT * FROM testdb.students;
+----+------------+------+--------+-----------------------+
| ID | Name | Age | Gender | Course |
+----+------------+------+--------+-----------------------+
| 1 | Ling Hucho | 24 | M | Hamagong |
| 4 | Zhu Ziliu | 52 | M | Kuihua Baodian |
| 5 | Chen Jialu | 22 | M | Xianglong Shiba zhang |
+----+------------+------+--------+-----------------------+
3 rows in set (0.00 sec)
5.5. 授权给testuser对testdb库所有权限；
mysql> CREATE USER ‘testuser‘@‘%‘ IDENTIFIED BY ‘testuser‘; #新建一个testuser用户并设置密码
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON testdb.* TO ‘testuser‘@‘%‘; #给新建的testuser用户授予对testdb 库的所有权限
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS FOR ‘testuser‘@‘%‘; #查看testuser用户的授权信息
+--------------------------------------------------------------------------------+
| Grants for testuser@% |
+--------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO ‘testuser‘@‘%‘ IDENTIFIED BY PASSWORD ‘0d59dfc910205df0‘ |
| GRANT ALL PRIVILEGES ON `testdb`.* TO ‘testuser‘@‘%‘ |
+--------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
[root@xuelinux ~]# mysql -utestuser -p -h10.109.134.249; #通过另一台服务器远程以testuser 用户登录10.109.134.249的数据库。
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.0.77 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> SELECT * FROM testdb.students; #查询testdb数据库中的students表
+----+------------+------+--------+-----------------------+
| ID | Name | Age | Gender | Course |
+----+------------+------+--------+-----------------------+
| 1 | Ling Hucho | 24 | M | Hamagong |
| 4 | Zhu Ziliu | 52 | M | Kuihua Baodian |
| 5 | Chen Jialu | 22 | M | Xianglong Shiba zhang |
+----+------------+------+--------+-----------------------+
3 rows in set (0.00 sec)
附件：
php、mysql下载地址： http://mirrors.163.com/centos/5/os/i386/CentOS/
本文出自 “学linux历程” 博客，请务必保留此出处http://woyaoxuelinux.blog.51cto.com/5663865/1896279
linux命令：MYSQL、PHP相关概念及安装配置