MySql 使用总结 – 🎄 Adomikao 🧸

Contents

1 1. MySQL 的基本使用
2 2. MySQL 的高级特性
- 2.1 2.1 事务处理

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);