15、sql语句集，linux 下php查询MySQL一、mysql 创建数据库
mysql密码，默认没有
如果想更改的话， mysqladmin -uroot passWord root123
登录 ： [root@localhost root]# mysql -uroot -PRoot123
1、创建book数据库
mysql> create database book;Query OK, 1 row affected (0.08 sec)
2、授权给 lili 用户
mysql> grant all on book.* to lili@localhost identified by "lili123";Query OK, 0 rows affected (0.06 sec)
mysql> exitBye
3、lili登录
创建lili用户
[root@localhost html]# mysql -u lili -pEnter password: （这里输密码）Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 5 to server version: 3.23.54
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
4、创建book表，添加数据
mysql> use book; （先确定要操作的数据库）Database changed
5、创建book表
mysql> create table book( -> id int not null, -> name char(20) not null, -> primary key(id) -> );Query OK, 0 rows affected (0.08 sec)
6、添加数据
mysql> insert into book values
-> (1,"语文"), -> (2,"数学"), -> (3,"英语");Query OK, 3 rows affected (0.00 sec)Records: 3 Duplicates: 0 Warnings: 0
（注意：上面操作也可以用sql语句集来执行： (好处是，去别的机子也可以执行sql语句集 就行，不用从头打过)
[root@localhost html]# cat > lili.sqluse book;create table book( id int not null, name char(20) not null, primary key(id) ); insert into book values (1,"语文",), (2,"数学"), (3,"英语");
[root@localhost html]# vi root.sqlcreate database book;grant all on book.* to lili@localhost identified by "lili123" ;
[root@localhost html]# mysql -u root -p < root.sqlEnter password:
[root@localhost html]# mysql -u lili -p < lili.sqlEnter password:
）
7、查询表
mysql> select * from book;+----+--------+| id | name |+----+--------+| 1 | 语文 || 2 | 数学 || 3 | 英语 |+----+--------+
退出mysqlmysql> exit;Bye
如果想直接命令行查询
[root@localhost root]# echo "select * from book;" | mysql -u lili -p book
Enter password:id name1 语文2 数学3 英语
二、在 /var/www/html 中 创建index.php
[root@localhost html]# vi index.php
<h1><?php mysql_connect(NULL,"lili","lili123","localhost"); mysql_selectdb("book"); $sql="select * from book"; $res=mysql_query($sql); while($row=mysql_fetch_row($res)){ print "$row[0] $row[1]<br/>"; } $a=array("阳光","沙滩","海浪","仙人掌"); for($i=0;$i<4;$i++){ print "$a[$i]<br/>"; }?></h1>
在浏览器输入 http://192.168.170.3/ , 结果如下：
如果想要网格线
<html><title></title><body><h1><?php mysql_connect(NULL,"lili","lili123","localhost"); mysql_selectdb("book"); $sql="select * from book"; $res=mysql_query($sql); print ('<table border=1>'); while($row=mysql_fetch_row($res)){ print ('<tr>'); for($i=0;$i<3;$i++){ print "<td>$row[$i]</td>"; } print ('</tr>'); } print ('</table>'); $a=array("阳光","沙滩","海浪","仙人掌"); for($i=0;$i<4;$i++){ print "$a[$i]<br/>"; }?></h1></body></html>
效果如下：