PHP & MySQL
1.MySQL简介:
(1)MySQL是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放
在一个大仓库内，这样就增加了速度并提高了灵活性。
(2)MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政
策，它分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特
点，一般中小型网站的开发都选择 MySQL 作为网站数据库。
(3)由于其社区版的性能卓越，搭配 PHP 和 Apache 可组成良好的开发环境。
2.MySQL数据库相关:
(1)PHP通过结构化查询语言(SQL)和关系型数据库(如MySQL,Oracle)进行通信,可以使用SQL来创
建,修改,查询关系数据库.
(2)MySQL的相关操作命令:
(1.)通过用户名和密码登录命令: sudo /usr/local/mysql/bin/mysql -u root -p (注:root为默认用户
名,若已修改请使用修改后的用户名)
(2.)重置密码:
1: SET PASSWORD = PASSWORD( 'your new password' );
2: ALTER USER ‘root’@‘localhost' PASSWORD EXPIRE NEVER;
3: flush privileges; //冲洗权限
(3.)添加新的用户:CREATE USER 'username'@'host' IDENTIFIED BY 'password';
(4.)为用户开放权限:GRANT ALL ON *.* TO 'lee'@'localhost';
（5. ）常用命令：
1. show databases; 显示所有数据库
2.create database 数据库名称; 创建数据库
3.use 数据库名称；选择数据库
4.show tables; 显示所有表
5.\q 退出
3.PHP & MySQL:
(1)链接: mysqli_connect(“主机地址", “用户名", "密码", “数据库名");
(2)链接失败信息: mysqli_connect_errno();
(3)选择某个数据库:mysqli_select_db();
(4)执行sql语句:$result = mysqli_query();
(5)操作得到的结果:
(1.)mysqli_fetch_row()函数从结果集中取得一行，并作为索引数组返回。
(2.)mysqli_fetch_assoc()函数从结果集中取得一行作为关联数组。
(6)释放结果集:mysqli_free_result($result);
(7)关闭先前打开的链接:mysqli_close($con).
4.SQL语句:
(1)点击前往
127.0.0.1
官方建议：
phpAdmin 、 命令
使用MySQL的工具
Naviat
#
CREATE DATABASE school;
DROP DATABASE school;
SHOW DATABASES ;
# 选择或切换数据库
USE blue;
# 创建表格
CREATE TABLE person(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
age SMALLINT,
gender CHAR(1) DEFAULT 'M',# 默认值
birthday DATE
);
#
SHOW TABLES ;
# 查看表格结果
DESCRIBE person;
DROP TABLE person;
# 插入数据
INSERT INTO person(name, age, gender, birthday) VALUES ('Derk', 38, 'M', '1976-12-18');
# 查看数据
SELECT * FROM person;
# 更新表的信息
UPDATE person
SET gender = 'M'
WHERE id = 3;
#
SELECT *
FROM person WHERE id = 3;
#
CREATE TABLE login(
id INT PRIMARY KEY ,
loginTime DATETIME,
ip VARCHAR(20),
location VARCHAR(30)
);
DESCRIBE login;
# 快捷键 ins 回车: insert into () values ();
INSERT INTO login (id, loginTime, ip, location) VALUES (3, current_time(), '172.0.0.1', 'USA');
SELECT * FROM login;
# 查询Id = 3的人的姓名
SELECT name FROM person WHERE id = 3;
SELECT name,age FROM person WHERE id = 3;
SELECT name AS NAME FROM person WHERE id = 3; #为结果集的列起别名
SELECT name AS NAME, age AS AGE FROM person WHERE id = 3;
SELECT NAME FROM person WHERE id = 3;
# 新列
SELECT name AS NAME, 'Not Marry' AS MARRY FROM person;
# 显示数据库版本
SELECT version();
# 查询当前用户
SELECT user();
# 查询当前数据库
SELECT database();
SELECT * FROM person;
# 子查询的结果作为目的表
SELECT ip FROM (SELECT * FROM login) record;# record 是为子查询的结果集起的名字
SELECT ip FROM (SELECT ip,location FROM (SELECT * FROM login) record) result;
# 按照某一列排序 ASC升序/DESC降序
SELECT * FROM person ORDER BY age DESC ;
# 限制结果数量 limit
SELECT * FROM person ORDER BY age DESC LIMIT 4;
# 模糊搜索
# %占n个字符位, _占一个字符位
SELECT * FROM person WHERE name LIKE '%R_';
#
SELECT * FROM person WHERE name NOT LIKE '_i%';
SELECT * FROM person,login WHERE login.id = person.id;
# 为表起别名
SELECT * FROM person p, login l WHERE l.id = p.id;
CREATE DATABASE school;
DROP DATABASE school;
SHOW DATABASES ;
# 选择或切换数据库
USE blue;
# 创建表格
CREATE TABLE person(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
age SMALLINT,
gender CHAR(1) DEFAULT 'M',# 默认值
birthday DATE
);
#
SHOW TABLES ;
# 查看表格结果
DESCRIBE person;
DROP TABLE person;
# 插入数据
INSERT INTO person(name, age, gender, birthday) VALUES ('Derk', 38, 'M', '1976-12-18');
# 查看数据
SELECT * FROM person;
# 更新表的信息
UPDATE person
SET gender = 'M'
WHERE id = 3;
#
SELECT *
FROM person WHERE id = 3;
#
CREATE TABLE login(
id INT PRIMARY KEY ,
loginTime DATETIME,
ip VARCHAR(20),
location VARCHAR(30)
);
DESCRIBE login;
# 快捷键 ins 回车: insert into () values ();
INSERT INTO login (id, loginTime, ip, location) VALUES (3, current_time(), '172.0.0.1', 'USA');
SELECT * FROM login;
# 查询Id = 3的人的姓名
SELECT name FROM person WHERE id = 3;
SELECT name,age FROM person WHERE id = 3;
SELECT name AS NAME FROM person WHERE id = 3; #为结果集的列起别名
SELECT name AS NAME, age AS AGE FROM person WHERE id = 3;
SELECT NAME FROM person WHERE id = 3;
# 新列
SELECT name AS NAME, 'Not Marry' AS MARRY FROM person;
# 显示数据库版本
SELECT version();
# 查询当前用户
SELECT user();
# 查询当前数据库
SELECT database();
SELECT * FROM person;
# 子查询的结果作为目的表
SELECT ip FROM (SELECT * FROM login) record;# record 是为子查询的结果集起的名字
SELECT ip FROM (SELECT ip,location FROM (SELECT * FROM login) record) result;
# 按照某一列排序 ASC升序/DESC降序
SELECT * FROM person ORDER BY age DESC ;
# 限制结果数量 limit
SELECT * FROM person ORDER BY age DESC LIMIT 4;
# 模糊搜索
# %占n个字符位, _占一个字符位
SELECT * FROM person WHERE name LIKE '%R_';
#
SELECT * FROM person WHERE name NOT LIKE '_i%';
SELECT * FROM person,login WHERE login.id = person.id;
# 为表起别名
SELECT * FROM person p, login l WHERE l.id = p.id;
