Contents
1. MySQL 的基本使用
1.1 连接 MySQL
1) 使用 MySQL 二进制方式连接
[root@host]# mysql -u root -p
Enter password:******
2) 使用 PHP 脚本连接数据库
<?php
$host = 'adomikao.com';
$user = 'root';
$password = '123456';
$con = mysqli_connect($host, $user, $password);
if(!$con){
die('connect is fail:'.mysql_error());
}
echo 'success!';
//mysqli_close($con);
1.2 创建、选择数据库
1) 使用 mysqladmin 命令创建数据库
[root@host]# mysqladmin -u root -p create ADOMIKAO
Enter password:******
2) 使用 mysqli_query 来创建或删除数据库
$sql = 'CREATE DATABASE ADOMIKAO';
/*
$sql = 'DROP DATABASE ADOMIKAO';// 删除数据库
*/
$resource = mysqli_query($con,$sql);
if(!$resource){
die('创建数据库失败:'.mysqli_error());
}
echo 'success';
3) 使用mysqli_select_db选择数据库
mysqli_select_db($con,'ADOMIKAO');
1.3 创建、删除数据表
1) 创建数据表的通用语法
CREATE TABLE table_name (column_name,column_type);
2) 使用命令创建数据表
root@host# mysql -u root -p
Enter password:*******
mysql> use ADOMIKAO;
Database changed
mysql> CREATE TABLE IF NOT EXISTS table1(
-> id INT NOT NULL AUTO_INCREMENT,
-> title VARCHAR(100) NOT NULL,
-> author VARCHAR(40) NOT NULL,
-> date DATE,
-> PRIMARY KEY ( id )
-> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.16 sec)
mysql>
3) 使用 PHP 脚本创建数据表
$sql = "CREATE TABLE table1( ".
"id INT NOT NULL AUTO_INCREMENT, ".
"title VARCHAR(100) NOT NULL, ".
"author VARCHAR(40) NOT NULL, ".
"date DATE, ".
"PRIMARY KEY ( runoob_id ))ENGINE=InnoDB DEFAULT CHARSET=utf8; ";
$resource= mysqli_query( $con, $sql );
?>
4) 使用命令删除数据表
DROP TABLE table_name ;
1.4 MySQL 的增删改查
1) 使用INSERT INTO语句插入数据
INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN );
2) 使用 SELECT 命令获取数据表中的数据
SELECT * FROM table1;
3) 使用 mysqli_fetch_array 函数从结果集中取得一行作为关联数组,或数字数组,或二者兼有 返回根据从结果集取得的行生成的数组
echo '<table border="1"><tr><td>教程 ID</td><td>标题</td><td>作者</td><td>提交日期</td></tr>';
while($row = mysqli_fetch_array($resource, MYSQL_ASSOC))
//PHP 提供了另外一个函数 mysqli_fetch_assoc(), 该函数从结果集中取得一行作为关联数组。
{
echo "<tr><td> {$row['id']}</td> ".
"<td>{$row['title']} </td> ".
"<td>{$row['author']} </td> ".
"<td>{$row['date']} </td> ".
"</tr>";
}
echo '</table>'
4) 使用 WHERE 子句设置查询条件
mysql> SELECT * FROM table1 WHERE author='adomikao';
//MySQL 的 WHERE 子句的字符串比较是不区分大小写的。 你可以使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的。
mysql> SELECT * FROM table1 WHERE BINARY author='ADOMIKAO';
5) 使用 UPDATE 命令更新表中的数据
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause] //注意:不使用 WHERE 子句将数据表的全部数据进行更新,所以要慎重。
6) 使用 DELETE FROM 命令删除数据表中的数据
DELETE FROM table_name [WHERE Clause]
7) 使用 LIKE 子句进行模糊查询
SELECT * FROM table1 WHERE author LIKE %COM;
//你可以使用 AND 或者 OR 指定一个或多个条件,LIKE 通常与 % 一同使用。
8) 使用 UNION 指令将两个 SQL 语句的结果合并起来
//默认情况下,会删除重复的结果数据。
[SQL 语句 1]
UNION
[SQL 语句 2];
9) 使用 ORDER BY 子句进行排序
//使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下,它是按升序排列。
mysql> SELECT * FROM table1 ORDER BY date DESC;
10) 使用 GROUP BY 语句根据一个或多个列对结果集进行分组
//在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
mysql> SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;
+--------+----------+
| name | COUNT(*) |
+--------+----------+
| 小丽 | 1 |
| 小明 | 3 |
| 小王 | 2 |
+--------+----------+
3 rows in set (0.01 sec)
/*
WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计(SUM,AVG,COUNT…)。
例如我们将以上的数据表按名字进行分组,再统计每个人登录的次数:
*/
mysql> SELECT name, SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;
+--------+--------------+
| name | singin_count |
+--------+--------------+
| 小丽 | 2 |
| 小明 | 7 |
| 小王 | 7 |
| NULL | 16 |
+--------+--------------+
4 rows in set (0.00 sec)
//其中记录 NULL 表示所有人的登录次数。
//我们可以使用 coalesce 来设置一个可以取代 NUll 的名称,coalesce 语法:
select coalesce(a,b,c);
//参数说明:如果a==null,则选择b;如果b==null,则选择c;如果a!=null,则选择a;如果a b c 都为null ,则返回为null(没意义)。
mysql> SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM employee_tbl GROUP BY name WITH ROLLUP;
+--------------------------+--------------+
| coalesce(name, '总数') | singin_count |
+--------------------------+--------------+
| 小丽 | 2 |
| 小明 | 7 |
| 小王 | 7 |
| 总数 | 16 |
+--------------------------+--------------+
4 rows in set (0.01 sec)
2. MySQL 的高级特性
2.1 事务处理
MySQL 事务主要用于处理操作量大,复杂度高的数据。比如说,在人员管理系统中,你删除一个人员,你即需要删除人员的基本资料,也要删除和该人员相关的信息,如信箱,文章等等,这样,这些数据库操作语句就构成一个事务! * 用 BEGIN, ROLLBACK, COMMIT 来实现事务处理。 BEGIN 开始一个事务,ROLLBACK 事务回滚,COMMIT 事务确认。
- 在 MySQL 命令行的默认设置下,事务都是自动提交的,即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION,或者执行命令 SET AUTOCOMMIT=0,用来禁止使用当前会话的自动提交。
- 事务的
原子性
:一组事务,要么成功;要么撤回。稳定性
:有非法数据(外键约束之类),事务撤回。隔离性
:事务独立运行。一个事务处理后的结果,影响了其他事务,那么其他事务会撤回。事务的100%隔离,需要牺牲速度。可靠性
:软、硬件崩溃后,InnoDB 数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得, innodb_flush_log_at_trx_commit 选项 决定什么时候吧事务保存到日志里。
PHP中使用事务实例
mysqli_query($con, "SET AUTOCOMMIT=0"); // 设置为不自动提交,因为MYSQL默认立即执行
mysqli_begin_transaction($con); // 开始事务定义
if(!mysqli_query($con, "insert into adomikao_transaction_test (id) values(8)"))
{
mysqli_query($con, "ROLLBACK"); // 判断当执行失败时回滚
}
mysqli_commit($con);