How to use MySQL?
Download
Report
Transcript How to use MySQL?
How to use MySQL?
Speaker: Ching-Chen
Chang
1
Outline
Command Line Interface (CLI)
Web-based Interface
2
Using MySQL CLI
$ mysql –u username –p
use databasename;
3
Information
show databases;
show tables;
describe tablename;
select * from tablename;
select col1, col2.. from tablename;
4
Create Table
Create table tablename(….);
create table phonebook (
id int, username char(50), phone varchar(50) );
)
5
Insert Values to a Table
INSERT INTO phonebook
VALUES(1 , ’Jane’ , ‘0911111111’);
INSERT INTO phonebook (id, username, phone)
VALUES(2 , ‘Bob’ , ’0912222222’);
INSERT INTO phonebook
VALUES(1 , ’Jane’ , ‘0911111111’), (2 , ‘Bob’ , ’0912222222’);
INSERT INTO phonebook (id, username) VALUES(3, ‘Peter’);
+------------+--------------+------------------------+
|
id |
username |
phone
|
+------------+--------------+------------------------+
|
1 |
Jane
|
0911111111
|
|
2 |
Bob
|
0912222222
|
|
3 |
Peter
|
|
+------------+--------------+------------------------+
6
Update tables
UPDATE phonebook SET phone=‘0911333333’
WHERE id=3;
UPDATE phonebook SET phone=‘0912444444’
WHERE username=‘Bob’;
+------------+--------------+------------------------+
|
id |
username |
phone
|
+------------+--------------+------------------------+
|
1 |
Jane
|
0911111111
|
|
2 |
Bob
|
0912444444
|
|
3 |
Peter
|
0911333333
|
+------------+--------------+------------------------+
7
Delete
DELETE FROM phonebook WHERE id=3;
+------------+--------------+------------------------+
|
id |
username |
phone
|
+------------+--------------+------------------------+
|
1 |
Jane
|
0911111111
|
|
2 |
Bob
|
0914444444
|
+------------+--------------+------------------------+
drop table tablename;
8
JOIN (1/2)
select * from phonebook;
+------------+--------------+------------------------+
|
id |
username |
phone
|
+------------+--------------+------------------------+
|
1 |
Jane
|
0911111111
|
|
2 |
Bob
|
0912444444
|
+------------+--------------+------------------------+
select * from student;
+---------------+--------------+
| studentid
|
name |
+---------------+--------------+
| 91321021
|
Jane |
| 91321033
|
Bob |
| 91321040
|
Peter |
+---------------+--------------+
9
JOIN (2/2)
SELECT phonebook.id, student.name, student.studentid,
phonebook.phone AS phonenumber
FROM phonebook, student
WHERE phonebook.username = student.name;
+--------------+---------------+---------------+-------------------+
| id
| name
| studentid
|
phonenumber
|
+--------------+---------------+---------------+-------------------+
| 1
| Jane
|
91321021 |
0911111111 |
| 2
| Bob
|
91321033 |
0912444444 |
+--------------+---------------+---------------+-------------------+
10
Web-base Interface: phpMyAdmin
http://stu.csie.ncnu.edu.tw/myadmin/
11
connect_db.php
<?
define('HOST', 'localhost');
define('USER', '91321021');
define('PASS', '262615');
define('DB', '91321021');
mysql_connect(HOST, USER, PASS);
mysql_select_db(DB);
?>
12
authentication(1/2) - login.php
<html>
<head><title>authentication</title>
<body>
<h2>Sign In</h2>
<form action="auth1.php" method="POST">
username:<input type="text" name="user"><br>
password:<input type="password" name="pass"><br>
<input type="submit">
</form>
</body>
</html>
http://stu.csie.ncnu.edu.tw/~beautidays.99/db/login2.php
13
authentication(2/2) - auth1.php
<?
include("connect_db.php");
$result = mysql_query(“
SELECT COUNT(*) AS numfound FROM login
WHERE id='{$HTTP_POST_VARS['user']}'
AND pwd='{$HTTP_POST_VARS['pass']}'");
$result_ar = mysql_fetch_array($result);
if ($result_ar['numfound'] < 1)
{
header('Location: login.php?error=1');
}
else{
echo "success!";
}
?>
14
Mysql_Query
<?
include("connect_db.php");
$result = mysql_query("SELECT * FROM phonebook;");
echo "id"." "." name"." phonenumber<br>";
$i=1;
$num=mysql_num_rows($result);
while($i<=$num)
{
$r=mysql_fetch_array($result);
echo $r[0]." ".$r[1]." ".$r[2]."<br>";
$i++;
}
?>
http://stu.csie.ncnu.edu.tw/~beautidays.99/db/mysql.php
15
References
MySQL Tutorial
– http://dev.mysql.com/doc/refman/4.1/en/tutorial.html
16
The end
17