MySQL
-
常用数据库 mySQL, SQL Sever ,Oracle
- 打开phpstudy，设置站点域名管理
- 不允许出现中文和空格
- 设置配置文件
- 打开MySQL管理器
- 登录用户名密码都为root，开启服务
- 有两个站点域名设置，第一个为默认的，网络端口为80，第二个为新增的，为自己设置的网络端口，然后点击新增，保存设置并生成配置文件
-
打开数据库
mysqli_connect("localhost","root","root","test2","3306");//地址，用户名，密码，数据库表，端口号 mysqli_connect_errno()//如果连接失败该函数返回为真 -
创建数据库
-
点击新建数据表
-
CREATE DATABASE 数据库名
-
PHP操作
$sql=mysqli_connect("localhost","root","root","","3306"); mysqli_query($sql,"create database testdata");
-
-
删除数据库
-
drop database <数据库名>
-
PHP操作
$sql=mysqli_connect("localhost","root","root","","3306"); mysqli_query($sql,"drop database testdata");
-
创建表
CREATE TABLE runoob_tbl( ".
"runoob_id INT NOT NULL AUTO_INCREMENT, ".
"runoob_title VARCHAR(100) NOT NULL, ".
"runoob_author VARCHAR(40) NOT NULL, ".
"submission_date DATE, ".
"PRIMARY KEY ( runoob_id ))ENGINE=InnoDB DEFAULT CHARSET=utf8;
名字
- 字段名
类型
-
varchar
- varchar可变长度
- varchar次之
-
char
- char长度固定
- char最快
-
text
- text不设置长度
- text最慢
-
int 数字类型;
-
float
- 浮点型
-
date 日期类型(年月日);
-
time 时间类型(时分秒);
长度/值
- 字段值的长度
默认
-
定义
- 确定一个默认值
-
NULL
- 没有默认值
-
CURRENT_TIMESTAMP
- 时间戳
整理
- 数据的编码类型
属性
-
binarg
- CHAR VARCHAR：值根据缺省字符集以大小写不区分的方式排序和比较，除非给出BINARY关键字。使用BINARY的例子见
-
unsigned
- 既为非负数，用此类型可以增加数据长度
-
unsigned zerofill
- 当数值的显示宽度小于指定的列宽度时候，则默认补充的空格用0代替
-
on update CURRENT_TIMESTAMP
- 更新时间戳
空
- 默认空值
索引
-
primary
-
主键
- 我们在这里称呼每一行独一无二的数据标识为主键 ( PRIMARY KEY)。一个表中只能存在一个主键，主键不接受空值。可以允许自增;
-
-
unique
-
唯一
- 不能重复
-
-
index
- 索引
-
FULLTEXT
- 全文搜索
A_I
- AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
注释
- 注释内容
PHP操作
mysqli_query($sql,"create table userbase( ".
"pid INT NOT NULL AUTO_INCREMENT, ".
"user VARCHAR(30) NOT NULL, ".
"password VARCHAR(20) NOT NULL, ".
"age int NOT NULL, ".
"PRIMARY KEY (pid))ENGINE=InnoDB DEFAULT CHARSET=utf8; ");
- 删除表
DROP TABLE table_name
mysqli_query($sql,"DROP TABLE userbase");
- 插入数据项
INSERT INTO table_name ( field1, field2,...fieldN )
VALUES ( value1, value2,...valueN );
PHP操作
mysqli_query($sql,"insert into userdata (pid, user, password, sex, age, phone) VALUES ('','xt','123456','男',30,18611042356)");
- 删除数据项
DELETE FROM table_name [WHERE Clause]
PHP操作
mysqli_query($sql,"delete from userdata where pid=2");
- 查询数据表
SELECT column_name,column_name
FROM table_name
[WHERE Clause]
SELECT runoob_id, runoob_title,
runoob_author, submission_date
FROM runoob_tbl
WHERE runoob_author LIKE "%COM"// %是表示任意字符
$result=mysqli_query($sql,"select user from userdata where 1");
print_r($result);
//where 1是条件为真
// $result->num_rows数据的长度
- 转换为数组内容
mysqli_fetch_array($result)
$result=mysqli_query($sql,"select user from userdata where password like '1234%'");
print_r(mysqli_fetch_array($result));
- echo “
<table width='400' border='1'>”;
echo "<tr><td>编号</td><td>用户名</td><td>性别</td><td>年龄</td><td>电话</td></tr>";
$len=$result->num_rows;
for ($i=0;$i<$len;$i++){
$arr=mysqli_fetch_array($result);
echo "<tr><td>{$arr['pid']}</td><td>{$arr['user']}</td><td>{$arr['sex']}</td><td>{$arr['age']}</td><td>{$arr['phone']}</td></tr>";
}
echo "</table>";
// - 通过{$变量}调用可以得到值连接
- 修改表
UPDATE table_name SET field1=new-value1, field2=new-value2
PHP操作
mysqli_query($sql,"update userdata set user='xt2' where pid=6");
登录
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>Document</title>
<style>
form
{
width: 800px;
margin: auto;
border: 1px solid #000000;
}
form>h2
{
text-align: center;
}
form input
{
margin-left: 20px;
font-size: 25px;
margin-bottom: 20px;
}
form>div
{
margin-left: 200px;
font-size: 25px;
}
form>div:last-of-type{
margin-top: 20px;
margin-bottom: 20px;
text-align: center;
margin-left: 0;
}
form>div:last-of-type>input:last-of-type{
margin-left: 100px;
}
</style>
</head>
<body>
<form action="http://10.9.65.239:4010/signIn.php" method="POST">
<h2>登录</h2>
<div>
<label for="user">用户名:</label>
<input type="text" id="user" name="user">
</div>
<div>
<label for="password">密 码:</label>
<input type="password" id="password" name="password">
</div>
<div>
<input type="submit">
<input id="register" type="button" value="注册">
</div>
</form>
<script>
init();
function init(){
var register=document.querySelector("#register");
register.addEventListener("click",clickHandler);
}
function clickHandler(){
location.href="./signUp.html";
}
</script>
</body>
</html>
php操作
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Document</title>
<style>
table
{
width: 800px;
border-collapse: collapse;
}
th,td{
border:1px solid #000000;
height:30px;
line-height: 30px;
text-align: center;
}
</style>
</head>
<body>
<?php
header("content-type:text/html;charset=utf-8");
open_mysql();
function open_mysql()
{
$sql = mysqli_connect("localhost", "root", "root", "game", 3306);
if (mysqli_connect_error()) {
echo "数据库打开失败";
} else {
$res = mysqli_query($sql, "SELECT * FROM `user` WHERE `user`='$_POST[user]'");
if ($res->num_rows === 0) {
echo "没有找到该用户";
} else {
$r = mysqli_fetch_assoc($res);
if ($r['password'] === $_POST["password"]) {
show_user_table($sql);
} else {
echo "密码错误";
}
}
}
}
function show_user_table($sql)
{
$res = mysqli_query($sql, "SELECT `pid`,`user`, `name`, `age`, `sex`, `tel`, `email` FROM `user` WHERE 1");
echo '<table>';
for ($i = 0; $i < $res->num_rows; $i++) {
$r = mysqli_fetch_assoc($res);
if ($i === 0) {
echo '<tr>';
foreach ($r as $key => $value) {
echo "<th>{$key}</th>";
}
echo "<th>删除</th>";
echo '</tr>';
}
echo '<tr>';
foreach ($r as $key => $value) {
echo "<td>{$value}</td>";
}
// echo "<td><form action='./delet.php' method='GET'><input type='text' name='pid' style='display:none' value={$r['pid']}><button type='submit'>删除</button></form></td>";
echo "<td><button onclick=clickHandler({$r['pid']})>删除</button></td>";
echo "<script>
function clickHandler(a){
location.href='./delet.php?pid='+a;
}
</script>";
echo '</tr>';
}
echo '</table>';
}
?>
</body>
</html>
注册
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>Document</title>
<style>
form
{
width: 800px;
margin: auto;
border: 1px solid #000000;
}
form>h2
{
text-align: center;
}
form input
{
margin-left: 20px;
font-size: 25px;
margin-bottom: 20px;
}
form>div
{
margin-left: 200px;
font-size: 25px;
}
/* ~后续兄弟选择器：选取所有指定元素之后的相邻兄弟元素，选择所有符合条件的兄弟元素*/
.sex~label
{
margin-left: 20px;
}
form>div:last-of-type{
margin-top: 20px;
margin-bottom: 20px;
text-align: center;
margin-left: 0;
}
form>div:last-of-type>input:last-of-type{
margin-left: 100px;
}
[type=radio]
{
width: 20px;
height: 20px;
}
span{
color:red;
}
</style>
</head>
<body>
<form action="http://10.9.65.239:4010/signUp.php" method="POST">
<h2>用户注册系统</h2>
<div>
<label for="user">用户名:</label>
<input type="text" id="user" name="user">
<span></span>
</div>
<div>
<label for="password">密 码:</label>
<input type="password" id="password" name="password">
<span></span>
</div>
<div>
<label for="name">姓 名:</label>
<input type="text" id="name" name="name">
<span></span>
</div>
<div>
<label class="sex">性 别:</label>
<label for="man">男</label>
<input type="radio" id="man" name="sex" value="男" checked>
<label for="women">女</label>
<input type="radio" id="women" name="sex" value="女">
</div>
<div>
<label for="age">年 龄:</label>
<input type="text" id="age" name="age">
<span></span>
</div>
<div>
<label for="tel">电 话:</label>
<input type="text" id="tel" name="tel">
<span></span>
</div>
<div>
<label for="email">邮 箱:</label>
<input type="text" id="email" name="email">
<span></span>
</div>
<div>
<input type="submit">
<input type="reset">
</div>
</form>
</body>
</html>
php操作
<?php
header("content-type:text/html;charset=utf-8");
open_mysql();
function open_mysql(){
$sql=mysqli_connect("localhost","root","root","game",3306);
if(mysqli_connect_error()){
echo "数据库打开失败";
}else{
$res=mysqli_query($sql,"INSERT INTO `user`(`user`, `password`, `name`, `age`, `sex`, `tel`, `email`) VALUES ('$_POST[user]','$_POST[password]','$_POST[name]',$_POST[age],'$_POST[sex]','$_POST[tel]','$_POST[email]')");
if($res){
echo "<script> location.href='http://10.9.65.239:4010/signIn.html'; </script>";
}else{
echo "<script> history.back(); </script>";
}
}
}
删除PHP操作
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Document</title>
<style>
table {
width: 800px;
border-collapse: collapse;
}
th,
td {
border: 1px solid #000000;
height: 30px;
line-height: 30px;
text-align: center;
}
</style>
</head>
<body>
<?php
header("content-type:text/html;charset=utf-8");
open_mysql();//打开mysql
function open_mysql()
{
$sql = mysqli_connect("localhost", "root", "root", "game", 3306);
if (mysqli_connect_error()) {
echo "数据库打开失败";
} else {
$res = mysqli_query($sql, "DELETE FROM `user` WHERE `pid`='$_GET[pid]'");
if ($res) {
show_user_table($sql);
}
}
}
function show_user_table($sql)
{
$res = mysqli_query($sql, "SELECT `pid`,`user`, `name`, `age`, `sex`, `tel`, `email` FROM `user` WHERE 1");
echo '<table>';
for ($i = 0; $i < $res->num_rows; $i++) {
$r = mysqli_fetch_assoc($res);
if ($i === 0) {
echo '<tr>';
foreach ($r as $key => $value) {
echo "<th>{$key}</th>";
}
echo "<th>删除</th>";
echo '</tr>';
}
echo '<tr>';
foreach ($r as $key => $value) {
echo "<td>{$value}</td>";
}
// echo "<td><form action='./delet.php' method='GET'><input type='text' name='pid' style='display:none' value={$r['pid']}><button type='submit'>删除</button></form></td>";
echo "<td><button onclick=clickHandler({$r['pid']})>删除</button></td>";
echo "<script>
function clickHandler(a){
location.href='./delet.php?pid='+a;
}
</script>";
echo '</tr>';
}
echo '</table>';
}
?>
</body>
</html>
扫一扫
16万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
