15、sql語句集，Linux 下PHP查詢mysql

2024-06-28 13:18:55

字體：大中小

來源：轉載

供稿：網友

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>

效果如下：