Linux应用：php、mysql基础

原创

风过_无痕 2017-01-03 16:28:15 博主文章分类：Linux 基础 ©著作权

©著作权归作者所有：来自51CTO博客作者风过_无痕的原创作品，请联系作者获取转载授权，否则将追究法律责任

php、mysql基础

一、安装PHP

[root@test tmp]# rpm -ql php53
package php53 is not installed
[root@test tmp]# 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@test tmp]# 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@test tmp]# 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@test tmp]# 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@test tmp]# rpm -ql php53
/etc/httpd/conf.d/php.conf #PHP配制文件
/usr/lib/httpd/modules/libphp5.so #PHP模块
/var/lib/php/session #PHP会话文件
/var/www/icons/php.gif

[root@test conf.d]# service httpd restart
Stopping httpd: [ OK ]
Starting httpd: [ OK ]
[root@test conf.d]# cd /www/a.org/
[root@test a.org]# ls
index.html
[root@test a.org]# mv index.html index.php
[root@test a.org]# vi index.php

二、安装mysql

[root@test a.org]# yum -y install mysql-server
...
Installing     : perl-DBI                        1/4
Installing     : mysql                          2/4
Installing     : perl-DBD-MySQL         3/4
Installing     : mysql-server            4/4
...
Complete!
[root@test a.org]# service mysqld start
Initializing MySQL database: Installing MySQL system tables...
161229 16:01:45 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
161229 16:01:45 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
OK
Filling help tables...
161229 16:01:45 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
161229 16:01:45 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
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 jacktest 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
                       [ OK ]
Starting MySQL:        [ OK ]
[root@jacktest yum.repos.d]# 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> DROP DATABASE IF EXISTS testdb; #删除表后无法恢复
Query OK, 0 rows affected (0.00 sec)

mysql> USE mydb;
Database changed
mysql> CREATE TABLE students(Name CHAR(10) NOT NULL,Age TINYINT UNSIGNED, Gender CHAR(1) NOT NULL); #创建表
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO students VALUES ('WFX',33,'M','XXX');
Query OK, 1 row affected, 1 warning (0.00 sec) #新增表字段值2

mysql> SELECT * FROM students;
+------+------+--------+--------+
| Name | Age | Gender | course |
+------+------+--------+--------+
| LHH | NULL | M | NULL   |
| LCC | NULL | F | NULL   |
| WWW | 33 |   M | XXX   |
+------+------+--------+--------+
3 rows in set (0.01 sec)

mysql> UPDATE students SET Course='Tec';
Query OK, 4 rows affected (0.01 sec)   #更新表中所有Course值
Rows matched: 4 Changed: 4 Warnings: 0
mysql> select * from students;
+------+--------+------+--------+
| Name | Course | Age | Gender |
+------+--------+------+--------+
| LHH | Tec    | NULL | M      |
| LCC | Tec    | NULL | F      |
| WFX | Tec    |   22 | M      |
| WWW | Tec    |   33 | M      |
+------+--------+------+--------+
4 rows in set (0.00 sec)

mysql> DELETE FROM students WHERE Gender='M';
Query OK, 3 rows affected (0.00 sec) #删除表中所有男性
mysql> select * from students;
+------+--------+------+--------+
| Name | Course | Age | Gender |
+------+--------+------+--------+
| LCC | Tec | NULL | F |
+------+--------+------+--------+
1 row in set (0.00 sec)

三、创建mysql用户

mysql> CREATE USER 'jerry'@'%' IDENTIFIED BY 'jerry';
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW GRANTS FOR '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'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR '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@jacktest yum.repos.d]# mysql -ujerry -p -h10.109.131.209 #换一台主机远程登录

四、无密码登录、改密码

[root@jacktest ~]# mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
[root@jacktest ~]# mysql -uroot -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)

解决方案

[root@jacktest ~]# service mysqld stop #1 中止mysqld服务

[root@jacktest ~]# mysqld_safe --skip-grant-tables & #2 安全模式启动mysqld服务
[1] 9671

[root@jacktest ~]# Starting mysqld daemon with databases from /var/lib/mysql

启动mysqld_safe ，跳过启动授权表。启动时加上skip-grant-tables参数目的是在启动mysql时不启动grant-tables，授权表。这样就可以修改root的密码了

[root@jacktest ~]# mysql -uroot -p    #3 进入mysql
Enter password:     #4 要求输入密码时，直接回车即可。
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.77 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use mysql;    #5 修改密码
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> update user set password=PASSWORD('123456') where user="root" and host=127.0.0.1;
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 0

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

[root@jacktest ~]# 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@jacktest ~]# kill -9 9585
[root@jacktest ~]# 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

五、基础练习

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));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO students VALUES ('1','LiHZ',54,'M','Hamagong');

mysql> INSERT INTO students VALUES ('2','Huangrong',24,'F','Dagou Bangfa');
mysql> INSERT INTO students VALUES ('3','LuWS',16,'F','Jiuyang Shenggong');

mysql> INSERT INTO students VALUES ('4','ZhuZL',56,'M','Pixie Jianfa');
mysql> INSERT INTO students VALUES ('5','WFX',26,'M','XiangLong 18Zhang');

mysql> SELECT * FROM students;
+----+-----------+------+--------+-------------------+
| ID | Name   | Age | Gender | Course |
+----+-----------+------+--------+-------------------+
| 1 | LiHZ    |   54 | M      | Hamagong   |
| 2 | Huangrong |   24 | F   | Dagou Bangfa |
| 3 | LuWS   |   16 | F      | Jiuyang Shenggong |
| 4 | ZhuZL |   56 | M      | Pixie Jianfa   |
| 5 | WFX    |   26 | M      | XiangLong 18Zhang |
+----+-----------+------+--------+-------------------+
5 rows in set (0.00 sec)

5.1. 找出性别为女性的所有人；

mysql> SELECT * FROM testdb.students WHERE Gender='F';
+----+-----------+------+--------+-------------------+
| ID | Name      | Age | Gender | Course|
+----+-----------+------+--------+-------------------+
| 2 | Huangrong |   24 | F      | Dagou Bangfa |
| 3 | LuWS      |   16 | F      | Jiuyang Shenggong |
+----+-----------+------+--------+-------------------+
2 rows in set (0.00 sec)

5.2. 找出年龄大于30的所有人；

mysql> SELECT * FROM testdb.students WHERE Age>30;
+----+-------+------+--------+--------------+
| ID | Name | Age | Gender | Course |
+----+-------+------+--------+--------------+
| 1 | LiHZ | 54 | M | Hamagong |
| 4 | ZhuZL | 56 | M | Pixie Jianfa |
+----+-------+------+--------+--------------+
2 rows in set (0.00 sec)

5.3. 修改LiHZ的课程为小李飞刀

mysql> UPDATE testdb.students SET Course='Xiaoli Feidao' WHERE Name='LiHZ';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM testdb.students WHERE Name='LiHZ';
+----+------+------+--------+---------------+
| ID | Name | Age | Gender | Course |
+----+------+------+--------+---------------+
| 1 | LiHZ | 54 | M | Xiaoli Feidao |
+----+------+------+--------+---------------+
1 row in set (0.00 sec)

5.4. 删除年龄小于30的所有人

mysql> DELETE FROM testdb.students WHERE Age<30;
Query OK, 3 rows affected (0.00 sec)
mysql> SELECT * FROM testdb.students ;
+----+-------+------+--------+---------------+
| ID | Name | Age | Gender | Course   |
+----+-------+------+--------+---------------+
| 1 | LiHZ |   54 | M      | Xiaoli Feidao |
| 4 | ZhuZL |   56 | M      | Pixie Jianfa |
+----+-------+------+--------+---------------+
2 rows in set (0.00 sec)

5.5. 授权给testuser对testdb库所有权限；

mysql> CREATE USER 'testuser1'@'%' IDENTIFIED BY 'testuser1';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON testdb.* TO 'testuser1'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR 'testuser1'@'%';
+---------------------------------------------------------------------------------+
| Grants for testuser1@% |
+---------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser1'@'%' IDENTIFIED BY PASSWORD '54b9a8960504b686' |
| GRANT ALL PRIVILEGES ON `testdb`.* TO 'testuser1'@'%' |
+---------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

[root@jacktest ~]# mysql -u testuser1 -p -h10.109.131.208
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.0.77 Source distribution
mysql> select * from testdb.students