数据库学习笔记

目录

数据库简介
数据库的安装
　　安装MySQL
　　安装MariaDB
数据库操作
数据类型
　　数值类型
　　字符串类型
　　日期和时间类型
　　复合类型
数据表操作
　　创建表
　　查看表
　　修改表
　　删除表
数据字段操作
　　增加字段
　　查看字段
　　修改字段
　　删除字段
索引
　　索引类型
　　索引操作
数据记录操作
　　插入记录
　　查询记录
　　修改记录
　　处理重复数据
　　删除记录
权限操作
　　添加权限
　　查看权限
　　删除权限
数据库事务
　　事务条件
　　事务控制语句
　　事务处理方法
SQL注入

数据库+PHP：MySQLi
　　MySQLi安装
　　连接数据库
　　操作数据库
　　断开连接

 

 

 

 

数据库简介

MySQL

MariaDB

数据库的安装

安装MySQL

www.mysql.com

安装MariaDB

MariaDB 数据库管理系统是 MySQL 的一个分支，主要由开源社区在维护，采用 GPL 授权许可。开发这个分支的原因之一是：甲骨文公司收购了 MySQL 后，有将 MySQL 闭源的潜在风险，因此社区采用分支的方式来避开这个风险。
MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。

安装数据库：

//yum方法安装mariadb-server和mariadb，两个都需要安装，一个是服务管理，一个是数据库工具：
yum install mariadb-server mariadb

启动数据库：

systemctl start mariadb    //启动MariaDB
systemctl stop mariadb    //停止MariaDB
systemctl restart mariadb    //重启MariaDB
systemctl enable mariadb    //设置开机启动

登录数据库：

//【设置密码】Mysql安装成功后，默认的root用户密码为空，使用以下命令来创建root用户的密码：
mysqladmin -u root password "新密码";

//【修改密码】进入数据库后执行以下命令修改密码：
set password for 'root'@'localhost' = password('新密码')

//通过以下命令来连接到Mysql服务器：
mysql -u root -p    //-u：用户名参数，-p：密码参数，也可以在这里直接输入密码，会显示明文，不建议。
Enter password:

  

数据库操作

创建数据库：

create database 库名;    //库名要用英文

查看数据库：

show databases;    //显示当前服务器的所有数据库 

选择数据库：

use 库名;    //选择需要进去操作的数据库

删除数据库：

drop database 库名;
//【切记】数据库删除后，里面的所有数据都会全部删除，所以删除前一定要慎重并做好相应的备份。

 

数据类型

MySQL支持多种类型，大致可以分为三类：数值类型、字符串类型和日期/时间类型，也支持复合类型。

数值类型

类型大小范围（有符号）范围（无符号）用途
TINYINT 1 byte (-128，127) (0，255) 小整数值
SMALLINT 2 bytes (-32 768，32 767) (0，65 535) 大整数值
MEDIUMINT 3 bytes (-8 388 608，8 388 607) (0，16 777 215) 大整数值
INT或INTEGER 4 bytes (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值
BIGINT 8 bytes (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值
FLOAT 4 bytes (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度
浮点数值
DOUBLE 8 bytes (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度
浮点数值
DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值

字符串类型 

类型 大小 范围（有符号） 范围（无符号） 用途
TINYINT 1 byte (-128，127) (0，255) 小整数值
SMALLINT 2 bytes (-32 768，32 767) (0，65 535) 大整数值
MEDIUMINT 3 bytes (-8 388 608，8 388 607) (0，16 777 215) 大整数值
INT或INTEGER 4 bytes (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值
BIGINT 8 bytes (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值
FLOAT 4 bytes (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度
浮点数值
DOUBLE 8 bytes (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度
浮点数值
DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值

日期和时间类型 

类型 大小
( bytes) 		范围 格式 用途
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3 '-838:59:59'/'838:59:59' HH:MM:SS 时间值或持续时间
YEAR 1 1901/2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 4

1970-01-01 00:00:00/2038

结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07

 YYYYMMDD HHMMSS 混合日期和时间值，时间戳

复合类型 

    MySQL数据类型   说明    　　举例
    set     　　　　集合类型    set(“member”, “member2″, … “member64″)
    enum    　　　　枚举类型    enum(“member1″, “member2″, … “member65535″)

 

数据表操作

创建表

语法：

create table 表名(字段名1 字段类型, ……字段名n 字段类型n);

创建表时指定表引擎和字符集：

CREATE TABLE emp ( 
useraname varchar(10) DEFAULT NULL, 
password date DEFAULT NULL, 
)ENGINE=InnoDB DEFAULT CHARSET=utf8;    //指定引擎和字符集

查看表

show tables;    //查看数据库中所有的表
desc 表名;        //查看表字段结构信息
show create table 表名 \G;    //查看表创建语句
show table status like '表' \G;    //查看数据表类型 

修改表

 修改表名：

alter table 原表名 rename 新表名;
alter table 原表名 rename to 新表名;
//注：一般不建议修改表名，因为可能有其他程序已经连接到数据表，修改表名会使该程序无法连接到原数据表

修改表类型：

alter table 表 engine=引擎;
//MySQL和MariaDB有两个引擎：MyISAM、InnoDB

修改表结构（添加索引）：

alter table 表 add index 索引名(字段);    //添加索引
drop index 索引字段 on 表;    //删除索引

//创建表的时候直接指定索引：
CREATE TABLE mytable(
ID INT NOT NULL, 
username VARCHAR(16) NOT NULL, 
INDEX [indexName] (username(length))
); 

复制表：

完整地复制MySQL数据表：
1.使用 SHOW CREATE TABLE 命令获取创建数据表(CREATE TABLE) 语句，该语句包含了原数据表的结构，索引等。
SHOW CREATE TABLE 表 \G;

2.复制命令显示的SQL语句，修改数据表名，并执行SQL语句，将完全的复制数据表结构：
CREATE TABLE 表 (【 上面第1步命令显示的SQL语句】)

3.如果想复制表的内容，可以使用 INSERT INTO ... SELECT 语句来实现：
INSERT INTO 新表 (字段1,字段2,...字段n) SELECT 字段1,字段2,...字段n FROM 原表;

临时表：

临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。如果使用PHP脚本来创建MySQL临时表，那每当PHP脚本执行完成后，该临时表也会自动销毁。当然也可以手动销毁。
    创建：
        CREATE TEMPORARY TABLE 表;
    删除：
        DROP TABLE 表;
默认情况下，当你断开与数据库的连接后，临时表就会自动被销毁。当然也可以在当前MySQL会话使用 DROP TABLE 命令来手动删除临时表。 

删除表

drop table 表名;
//注：删除表。表和数据均会丢失，请勿必删除重要表之前备份数据。

 

数据字段操作 

增加字段

alter table 表名 add column 字段名 类型;    //在后面增加字段

增加字段时控制字段顺序

alter table 表名 add 字段名 字段类型 after 已有的字段名;    //在已有的字段名后面增加
alter table 表名 add 字段名 字段类型 first;    //在第一行增加字段 

查看字段

desc 表名;        //查看表字段结构信息 

修改字段 

//修改表字段类型：
    alter table 表名 modify 字段名 varchar(20);
//修改表字段名：
    alter table 表名 change 字段原名 字段新名 字段类型;
//修改表字段排列顺序：
    ALTER TABLE 表 ADD 字段 字段类型 FIRST;    //设定位于第一列 first
    ALTER TABLE 表 ADD 新字段 字段类型 AFTER 现有字段;    //设定位于某个字段之后 after
    //注：FIRST 和 AFTER 关键字可用于 ADD 与 MODIFY 子句

字段默认值
    设置/修改
        ALTER TABLE 表 ALTER 字段 SET DEFAULT 默认值;
    删除
        ALTER TABLE 表 ALTER 字段 DROP DEFAULT;

删除字段 

alter table 表 drop column 字段;
//可以不写column
//如果数据表中只剩余一个字段则无法使用DROP来删除字段

 

索引

索引可以大大提高MySQL的检索速度。索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。建立索引会占用磁盘空间的索引文件。

索引类型

普通索引

最基本的索引，它没有任何限制。如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

唯一索引

与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

主键索引

主键作用于列上（可以一个列或多个列联合主键），添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。

全文索引

【待】

索引操作

创建索引

create index 索引名 on 表(字段);
create unique index 索引名 on 表(字段(length());

//创建表时也可以声明索引，在创建表语句后加上对应的类型即可声明索引：
    PRIMARY KEY(字段) 
    INDEX [索引名] (字段)
    FULLTEXT [索引名] (字段)
    UNIQUE[索引名] (字段)
    
例：
CREATE TABLE mytable(
ID INT NOT NULL, 
username VARCHAR(16) NOT NULL, 
INDEX [indexName] (username(length))
);
注：中括号中的索引名，代表可选。

查看索引

SHOW INDEX FROM 表; \G

修改索引

alter table 表 add index 索引名(字段);    //普通索引，索引值可出现多次。
alter table 表  add UNIQUE(字段(length());    //索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
alter table 表  add PRIMARY KEY(字段);    //索引值必须是唯一的，且确保该主键默认不为空：ALTER TABLE 表 MODIFY 字段 字段类型 NOT NULL;
alter table 表  add FULLTEXT(字段);    //索引为 FULLTEXT ，用于全文索引。

删除索引

DROP INDEX 索引字段 ON 表;
ALTER TABLE 表 DROP INDEX 带索引字段;
ALTER TABLE 表 DROP PRIMARY KEY;

//删除主键时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。
//【注意】在企业环境不能轻易删除索引！！！

 

数据记录操作

插入记录

方式一：
insert into 表 values(值1,值2,值n);
//表中有多少个字段就必须要插入多少个值。一个不能多，一个也不能少。若有默认值，不想传，可以写上null。

方式二：
insert into 表(字段1,字段2,字段n) values(值1,值2,值n);
//除非有必填字段必须要写入值外。如果有默认值的不想写可以忽略不写，mysql会自动补主默认值。 

查询记录

基础查询

select * from 表;    //查询表中所有字段中的所有结果。”*” 是一种正则表达式的写法，表示匹配所有。

指定字段查询

select 字段 from 表;
select distinct 字段 from 表;    //查询单个字段不重复记录用 distinct

条件查询where子句

1.基本语法：

select 字段 from 表 where 条件;
where后面可接的条件：
    比较运算符：>大于、<小于、>=大于等于、<=小于等于、!=不等于、=等于
    逻辑运算符：or或者、and并且

MySQL 的 WHERE 子句的字符串比较是不区分大小写的。 你可以使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的：
    select 字段 from 表 where BINARY 条件;

2.子查询in和like：

in子句：
    select 字段  from 表 where 字段 in(条件);
like子句：
    select 字段 from 表 where 字段 like 条件;
LIKE 通常与 % 一同使用，类似于一个元字符的搜索。LIKE 子句中使用百分号 %字符来表示任意字符，类似于UNIX或正则表达式中的星号 *。如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。
    例：SELECT * from tbl  WHERE author LIKE '%COM';

3.NULL值：

//IS NULL：
    SELECT * FROM 表 WHERE 字段 IS NULL;    //当列的值是 NULL,此运算符返回 true。
//IS NOT NULL：
    SELECT * FROM 表 WHERE 字段 IS NOT NULL;    //当列的值不为 NULL, 运算符返回 true。
//<=>：
    比较操作符<=>（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true。

4.MySQL 正则表达式：

SELECT 字段 FROM 表 WHERE 字段 REGEXP '匹配模式';
匹配模式描述
^ 匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 '\n' 或 '\r' 之后的位置。
$ 匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 '\n' 或 '\r' 之前的位置。
. 匹配除 "\n" 之外的任何单个字符。要匹配包括 '\n' 在内的任何字符，请使用像 '[.\n]' 的模式。
[...] 字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。
[^...] 负值字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 "plain" 中的'p'。
p1|p2|p3 匹配 p1 或 p2 或 p3。例如，'z|food' 能匹配 "z" 或 "food"。'(z|f)ood' 则匹配 "zood" 或 "food"。
* 匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}。
+ 匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。
{n} n 是一个非负整数。匹配确定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的两个 o。
{n,m} m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。

多表联合查询

1.多表联合join

//inner join内连接,或等值连接：获取两个表中字段匹配关系的记录(也可以省略 INNER 使用 JOIN，效果一样)：
    select 表1.字段 [as 别名], 表n.字段 from 表1 inner join 表2 on 条件;
    //等价于：select 表1.字段 [as 别名], 表n.字段 from 表1 表2 where 条件;

//left join左连接：获取左表所有记录，即使右表没有对应匹配的记录:
    select 表1.字段 [as 别名], 表n.字段 from 表1 left join 表2 on 条件;

//right join右连接：与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录:
    select 表1.字段 [as 别名], 表n.字段 from 表1 right join 表2 on 条件;

2.记录联合

select 语句1 union [all] select 语句2;

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
expression1, expression2, ... expression_n: 要检索的列。
tables: 要检索的数据表。
WHERE conditions: 可选， 检索条件。
DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。
ALL: 可选，返回所有结果集，包含重复数据。

查询结果操作

1.结果集排序 order by

select 字段 from 表 order  by 字段 排序关键词;
    //asc    升序排列关键词，从小到大（默认）
    //desc    降序排列关键词，从大到小

//根据多个列进行排序:
SELECT column_name(s)
FROM table_name
ORDER BY column1, column2
//当按照多个列进行排序时，只有第一列的值相同时才使用第二列

2.结果集限制 limit

//对于查询或者排序后的结果集，如果希望只显示一部分而不是全部，使用 limit 关键字结果集数量限制：
select 字段 from 表 limit 数量;

//限制结果集并排序：
select 字段 from 表 order by 字段 关键词 limit 数量;

3.分组 group by

select * from 表 group by 字段;
//在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

//在分组基础上进行统计:
    select * from 表 group by 字段 with rollup;

//分组结果再过滤having
    select * from 表 group by 字段 having 条件; 

4.统计类函数使用

select 函数(字段) from 表;

函数：
    sum：求和
    count：计数
    max：最大值
    min：最小值
    avg：平均值 

查询的整体使用SQL语句

//语法：
SELECT 
[字段1 [as 别名1],[函数(字段2) ,]......字段n] 
FROM 表名 
[WHERE where条件] 
[GROUP BY 字段] 
[HAVING where_contition] 
[order 条件] 
[limit 条件];

//注释：
select：选择的列
[where 条件]：查询的条件
[group by 字段]：分组属性
[having where_contition]：分组过滤的条件
[order by 条件]： 排序属性
[limit 条件]： 起始记录位置，取记录的条数

修改(更新)记录

//语法：
update 表名 set 字段1=值1,字段2=值2,字段n=值n where 条件;

//同时对两个表进行更新：
update 表1,表2 set 字段1=值1,字段2=值2,字段n=值n where 条件;

处理重复数据 

1.防止表中出现重复数据

在 MySQL 数据表中设置指定的字段为 PRIMARY KEY（主键） 或者 UNIQUE（唯一） 索引来保证数据的唯一性。
也可以设置双主键模式来设置数据的唯一性：
        PRIMARY KEY (last_name, first_name)


如果设置了唯一索引，那么在插入重复数据时，SQL 语句将无法执行成功，并抛出错：
一、INSERT IGNORE INTO
    INSERT IGNORE INTO 与 INSERT INTO 的区别就是 INSERT IGNORE INTO 会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。
    INSERT IGNORE INTO person_tbl (last_name, first_name) VALUES( 'Jay', 'Thomas');
二、REPLACE INTO 
    如果存在 primary 或 unique 相同的记录，则先删除掉。再插入新记录。

2.统计重复数据

查询重复的值的操作：
1.确定哪一列包含的值可能会重复。
2.在列选择列表使用COUNT(*)列出的那些列。
3.在GROUP BY子句中列出的列。
4.HAVING子句设置重复数大于1。

SELECT COUNT(*) as repetitions, last_name, first_name 
FROM person_tbl 
GROUP BY last_name, first_name 
HAVING repetitions > 1;

3.过滤重复数据

//DISTINCT方法：
    SELECT DISTINCT 字段1, 字段2 FROM 表;
//GROUP BY方法：
    SELECT 字段1, 字段2 FROM 表 GROUP BY (字段1, 字段2);

4.删除重复数据

一、使用SQL语句：
    1.新建临时表，去除重复数据
        CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl  GROUP BY (last_name, first_name, sex);
    2.删除旧表
        DROP TABLE person_tbl;
    3.将临时表名改为旧表名
        ALTER TABLE tmp RENAME TO person_tbl;

二、添加 INDEX（索引）和 PRIMAY KEY（主键）的方法：
    ALTER IGNORE TABLE 表 ADD PRIMARY KEY (字段1, 字段2);

删除记录

delete from 表 [where 条件];    //删除时一定要记住加上where条件，不然会清空掉整个表的记录。

TRUNCATE TABLE 表名;    //清空表记录
//delete和truncate是一样的，但它们有一点不同，那就是DELETE可以返回被删除的记录数，而TRUNCATE TABLE返回的是0。
//如果一个表中有自增字段，使用truncate table 这个自增字段将起始值恢复成1
//【切记】删除重要数据前一定要备份！！！

  

权限操作

添加权限

grant 权限 on 库.表 to '用户'@'主机' identified by '密码';
//grant all：在grant后接all说明给予所有权限
//权限 on .    . 说明给予所有库所有表的操作权限
//'用户'@'主机'    主机里面若为%。任意来源的主机均可以使用这个用户来访问
例：grant select, insert on test.* to 'liwenkai'@'localhost' identified by '4311';

查看权限

flush privileges;    //查看前先刷新
show grants;    //查看当前用户权限
show grants for 'test'@'localhost';    //查看其它用户权限

删除权限

revoke 权限 on 库.表 from '用户'@'主机';    //在revoke后接all说明删除所有权限

 

数据库事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。

  • 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
  • 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
  • 事务用来管理 insert,update,delete 语句

事务必须满足以下4个条件（ACID）

原子性（Atomicity，或称不可分割性）

一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性（Consistency）

在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

隔离性（Isolation，又称独立性）

数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

持久性（Durability）

事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务控制语句

  • BEGIN 或 START TRANSACTION 显式地开启一个事务；

  • COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；

  • ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

  • SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；

  • RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

  • ROLLBACK TO identifier 把事务回滚到标记点；

  • SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

事务处理方法

1、用 BEGIN, ROLLBACK, COMMIT来实现：

BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0    //禁止自动提交
SET AUTOCOMMIT=1    //开启自动提交

 

SQL注入

所谓SQL注入，就是通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。

防止SQL注入，需要注意以下几个要点：

  1. 永远不要信任用户的输入。对用户的输入进行校验，可以通过正则表达式，或限制长度；对单引号和 双"-"进行转换等。
  2. 永远不要使用动态拼装sql，可以使用参数化的sql或者直接使用存储过程进行数据查询存取。
  3. 永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。
  4. 不要把机密信息直接存放，加密或者hash掉密码和敏感的信息。
  5. 应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装
  6. sql注入的检测方法一般采取辅助软件或网站平台来检测，软件一般采用sql注入检测工具jsky，网站平台就有亿思网站安全平台检测工具。MDCSOFT SCAN等。采用MDCSOFT-IPS可以有效的防御SQL注入，XSS攻击等。

PHP的MySQL扩展提供了mysqli_real_escape_string()函数来转义特殊的输入字符：

if (get_magic_quotes_gpc()) {
  $name = stripslashes($name);
}
$name = mysqli_real_escape_string($conn, $name);
mysqli_query($conn, "SELECT * FROM users WHERE name='{$name}'");

Like语句中的注入：

like查询时，如果用户输入的值有"_"和"%"，则会出现这种情况：用户本来只是想查询"abcd_"，查询结果中却有"abcd_"、"abcde"、"abcdf"等等；用户要查询"30%"（注：百分之三十）时也会出现问题。

//在PHP脚本中可以使用addcslashes()函数来处理以上情况:
$sub = addcslashes(mysqli_real_escape_string($conn, "%something_"), "%_");
// $sub == \%something\_
mysqli_query($conn, "SELECT * FROM messages WHERE subject LIKE '{$sub}%'");

//addcslashes() 函数在指定的字符前添加反斜杠。语法格式:
addcslashes(string,characters)
    //string：必需。规定要检查的字符串。
    //characters：可选。规定受 addcslashes() 影响的字符或字符范围。

 

数据库+PHP：MySQLi

通过 PHP，您可以连接和操作数据库。

PHP 5 及以上版本可以使用MySQLi extension ("i" 意为 improved)连接数据库。

MySQLi 安装

在 php5 mysql 包安装时 MySQLi 扩展多数情况下是自动安装的。

安装详细信息，请查看： http://php.net/manual/en/mysqli.installation.php

可以通过 phpinfo() 查看是否安装成功：

若没有看到mysqli扩展，在windows服务器下，打开php.ini文件，将php_mysqli.dll打开即可。

连接数据库

用mysqli连接数据库，有面向对象和面向过程两种方式，这里介绍面向过程的方式：

<?php
//服务器、用户名、密码、数据库
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = 'database_name';
 // 创建连接
$conn = mysqli_connect ($servername, $username, $password, $dbname);
// 检测连接
if (!$conn) {die("连接失败：" . mysqli_connect_error());}
echo "连接成功";
// 设置编码，防止中文乱码
mysqli_query($conn , "set names utf8");
?> 

操作数据库

//使用SQL操作数据库
$sql = "SQL语句";
if (mysqli_query ($conn, $sql)){echo "成功";}else{ echo "失败：".mysqli_error($conn);}

//执行多条SQL语句操作数据库
$sql = "SQL语句1;";
$sql .= "SQL语句2;";
$sql .= "SQL语句3";
if (mysqli_multi_query ($conn, $sql)){echo "成功";}else{ echo "失败：".mysqli_error($conn);}
//注意，每个SQL语句必须用分号隔开。

读取数据

$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = mysqli_query($conn, $sql);
 
if (mysqli_num_rows($result) > 0) {
    // 输出数据
    while($row = mysqli_fetch_assoc($result)) {
        echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";}
} else {echo "0 结果";} 

过滤记录

$result = mysqli_query($con,"SELECT * FROM Persons
WHERE FirstName='Peter'");

while($row = mysqli_fetch_array($result))
{
echo $row['FirstName'] . " " . $row['LastName'];
echo "<br>";
} 

对记录集中的数据进行排序

$result = mysqli_query($con,"SELECT * FROM Persons ORDER BY age");

while($row = mysqli_fetch_array($result))
{
    echo $row['FirstName'];
    echo " " . $row['LastName'];
    echo " " . $row['Age'];
    echo "<br>";
} 

断开连接

mysqli_close($conn);

注意：完整的过程应该是建立连接-操作-断开连接，操作完成之后一定要记得断开与数据库的连接。

 

posted @ 2020-10-04 01:14  101010101  阅读(254)  评论(0)    收藏  举报