数据库Day2：MySQL从0到1

王中阳Go

发布于 2022-10-26 13:57:19

3.7K0

发布于 2022-10-26 13:57:19

文章被收录于专栏：Go语言学习专栏

术语

冗余：存储2倍数据，冗余可以是系统速度变快。
复合键（组合键）：将多个列作为一个索引键，一般用于复合索引。
索引：类似书籍中的目录，使用索引可以快速访问数据库中的特定信息，是对数据库表中一列或者多列值进行排序的一种结构
参照完整性：要求关系中不允许引用不存在的实体，保证数据一致性。

数据库

创建数据库 CREATE
删除数据库 DROP
选择使用指定的数据库

mysql_select_db( 'RUNOOB' );

数据类型

数值型 1.precision 精准的 2.decimal 十进制；小数
日期/时间型
字符串类型 1.char 定长字符串 2.varchar 变长字符串 3.BLOB 二进制形式的长文本数据：可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。 4.BINARY和VARBINARY：它们包含二进制（字节）字符串，没有字符集 5.有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的最大长度和存储需求。

创建数据表

key：表名，表字段名，定义每个表字段

CREATE TABLE table_name (column_name column_type);

PRIMARY KEY关键字用于定义列为主键。您可以使用多列来定义主键，列间以逗号分隔。

删除数据表

DROP TABLE table_name ;

插入数据 INSERT INTO

INSERT INTO table_name (field1,field2,...fieldN) VALUES (value1,value2,...valueN);

如果数据是字符型，必须使用单引号或者双引号，如：”value”。

//php的sql语句可以这么写
$sql = "INSERT INTO runoob_tbl ".
       "(runoob_title,runoob_author, submission_date) ".
       "VALUES ".
       "('$runoob_title','$runoob_author','$submission_date')";

点. 之后的内容回车折行，方便阅读；SQL语句的命令结束符为分号（;）
注意插入数据的时候要进行安全性的检验，get_magic_quotes_gpc特殊字符转义

查询数据 SELECT

SQL语法：

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[OFFSET M ][LIMIT N]

查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件
SELECT 命令可以读取一条或者多条记录。
你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
你可以使用 WHERE 语句来包含任何条件。
你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。
你可以使用 LIMIT 属性来设定返回的记录数。

PHP语法：

$sql = 'SELECT runoob_id, runoob_title, 
               runoob_author, submission_date
        FROM runoob_tbl';

while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
    echo "Tutorial ID :{$row['runoob_id']}  <br> ".
         "Title: {$row['runoob_title']} <br> ".
         "Author: {$row['runoob_author']} <br> ".
         "Submission Date : {$row['submission_date']} <br> ".
         "--------------------------------<br>";
} 
//以上实例中，读取的每行记录赋值给变量$row，然后再打印出每个值。

MYSQL_ASSOC这个返回的数组是以数据表中的字段为键的,而MYSQL_NUM是以数字为键的
记住如果你需要在字符串中使用变量，请将变量置于花括号中。
PHP mysql_fetch_array()函数第二个参数为MYSQL_ASSOC，设置该参数查询结果返回关联数组，你可以使用字段名称来作为数组的索引。
PHP提供了另外一个函数mysql_fetch_assoc(), 该函数从结果集中取得一行作为关联数组。返回根据从结果集取得的行生成的关联数组，如果没有更多行，则返回 false。

while($row = mysql_fetch_assoc($retval))
{
    echo "Tutorial ID :{$row['runoob_id']}  <br> ".
         "Title: {$row['runoob_title']} <br> ".
         "Author: {$row['runoob_author']} <br> ".
         "Submission Date : {$row['submission_date']} <br> ".
         "--------------------------------<br>";
}

可以使用常量 MYSQL_NUM 作为PHP mysql_fetch_array()函数的第二个参数，返回数字数组。

while($row = mysql_fetch_array($retval, MYSQL_NUM))
{
    echo "Tutorial ID :{$row[0]}  <br> ".
         "Title: {$row[1]} <br> ".
         "Author: {$row[2]} <br> ".
         "Submission Date : {$row[3]} <br> ".
         "--------------------------------<br>";
}

以上三个实例输出结果都一样。

内存释放

在我们执行完SELECT语句后，释放游标内存是一个很好的习惯。。可以通过PHP函数mysql_free_result()来实现内存的释放。以下实例演示了该函数的使用方法。

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT runoob_id, runoob_title, 
               runoob_author, submission_date
        FROM runoob_tbl';

mysql_select_db('RUNOOB');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_NUM))
{
    echo "Tutorial ID :{$row[0]}  <br> ".
         "Title: {$row[1]} <br> ".
         "Author: {$row[2]} <br> ".
         "Submission Date : {$row[3]} <br> ".
         "--------------------------------<br>";
}
mysql_free_result($retval);
echo "Fetched data successfully\\\\\\\\\\\\\\\\n";
mysql_close($conn);
?>

WHERE子句

sql语法

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
你可以在WHERE子句中指定任何条件。
你可以使用AND或者OR指定一个或多个条件。
WHERE子句也可以运用于SQL的 DELETE 或者 UPDATE 命令。
WHERE 子句类似于程序语言中的if条件，根据 MySQL 表中的字段值来读取指定的数据。
除非使用 LIKE 来比较字符串，否则MySQL的WHERE子句的字符串比较是不区分大小写的。可以使用 BINARY 关键字来设定WHERE子句的字符串比较是区分大小写的。

php语法

$sql = 'SELECT runoob_id, runoob_title, 
               runoob_author, submission_date
        FROM runoob_tbl
        WHERE runoob_author="Sanjay"';
$retval = mysql_query( $sql, $conn );
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
    echo "Tutorial ID :{$row['runoob_id']}  <br> ".
         "Title: {$row['runoob_title']} <br> ".
         "Author: {$row['runoob_author']} <br> ".
         "Submission Date : {$row['submission_date']} <br> ".
         "--------------------------------<br>";
}

可以使用PHP函数的mysql_query()及相同的SQL SELECT 带上 WHERE 子句的命令来获取数据。
通过 PHP 函数 mysql_fetch_array() 来输出所有查询的数据。

UPDATE查询

sql语法：

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

你可以同时更新一个或多个字段。
你可以在 WHERE 子句中指定任何条件。
你可以在一个单独表中同时更新数据。

php语法：

$sql = 'UPDATE runoob_tbl
        SET runoob_title="Learning JAVA"
        WHERE runoob_id=3';

mysql_select_db('RUNOOB');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not update data: ' . mysql_error());
}
echo "Updated data successfully\\\\\\\\\\\\\\\\n";

DELETE删除语句

sql语法：

DELETE FROM table_name [WHERE Clause]

如果没有指定 WHERE 子句，MySQL表中的所有记录将被删除。
你可以在 WHERE 子句中指定任何条件
您可以在单个表中一次性删除记录。

php语法：

$sql = 'DELETE FROM runoob_tbl
        WHERE runoob_id=3';

mysql_select_db('RUNOOB');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not delete data: ' . mysql_error());
}
echo "Deleted data successfully\\\\\\\\\\\\\\\\n";
mysql_close($conn);

PHP使用 mysql_query() 函数来执行SQL语句，你可以在SQL DELETE命令中使用或不使用 WHERE 子句。
该函数与 mysql>命令符执行SQL命令的效果是一样的。

Like子句

sql语法：

SELECT field1, field2,...fieldN table_name1, table_name2...
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

你可以在WHERE子句中指定任何条件。
你可以在WHERE子句中使用LIKE子句。
你可以使用LIKE子句代替等号(=)。
LIKE 通常与 % 一同使用，类似于一个元字符的搜索。
你可以使用AND或者OR指定一个或多个条件。
你可以在 DELETE 或 UPDATE 命令中使用 WHERE…LIKE 子句来指定条件。

php语法：

$sql = 'SELECT runoob_id, runoob_title, 
               runoob_author, submission_date
        FROM runoob_tbl
        WHERE runoob_author LIKE "%jay%"';

mysql_select_db('RUNOOB');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
    echo "Tutorial ID :{$row['runoob_id']}  <br> ".
         "Title: {$row['runoob_title']} <br> ".
         "Author: {$row['runoob_author']} <br> ".
         "Submission Date : {$row['submission_date']} <br> ".
         "--------------------------------<br>";
}

like需要和%配合使用，否则like的作用就是=
%A->以A结尾；A%->以A开头；%A%->包括A

　Mysql排序 ORDER BY

sql语法

SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]

你可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。
你可以设定多个字段来排序。
你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。默认情况下，它是按升序排列。
你可以添加 WHERE…LIKE 子句来设置条件。

php语法：

$sql = 'SELECT runoob_id, runoob_title, 
               runoob_author, submission_date
        FROM runoob_tbl
        ORDER BY  runoob_author DESC';

mysql_select_db('RUNOOB');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
    echo "Tutorial ID :{$row['runoob_id']}  <br> ".
         "Title: {$row['runoob_title']} <br> ".
         "Author: {$row['runoob_author']} <br> ".
         "Submission Date : {$row['submission_date']} <br> ".
         "--------------------------------<br>";
} 
echo "Fetched data successfully\\\\\\\\\\\\\\\\n";

ASC升序；DESC降序。

分组 GROUP BY

sql语法：

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

GROUP BY 语句根据一个或多个列对结果集进行分组。
在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

演示代码

//需求：使用 GROUP BY 语句 将数据表按名字进行分组，并统计每个人有多少条记录：

mysql> SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;
+--------+----------+
| name   | COUNT(*) |
+--------+----------+
| 小丽 |        1 |
| 小明 |        3 |
| 小王 |        2 |
+--------+----------+

WITH ROLLUP

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 |
+--------+--------------+

上面演示代码中的NULL是所有人的登录次数，这样显示显然不友好；我们可以引入coalesce

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 |
+--------------------------+--------------+

链接的使用 INNER JOIN,LEFT JOIN,RIGHT JOIN

JOIN 按照功能大致分为如下三类：

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）：与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

INNER JOIN

mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
+-----------+---------------+--------------+
| runoob_id | runoob_author | runoob_count |
+-----------+---------------+--------------+
|         1 | John Poul     |            1 |
|         3 | Sanjay        |            1 |
+-----------+---------------+--------------+
等价于下面的代码
mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a, tcount_tbl b WHERE a.runoob_author = b.runoob_author;
+-------------+-----------------+----------------+
| runoob_id | runoob_author | runoob_count |
+-------------+-----------------+----------------+
|           1 | John Poul       |              1 |
|           3 | Sanjay          |              1 |
+-------------+-----------------+----------------+

MySQL LEFT JOIN

mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
+-------------+-----------------+----------------+
| runoob_id | runoob_author | runoob_count |
+-------------+-----------------+----------------+
|           1 | John Poul       |              1 |
|           2 | Abdul S         |           NULL |
|           3 | Sanjay          |              1 |
+-------------+-----------------+----------------+

以上实例中使用了LEFT JOIN，该语句会读取左边的数据表runoob_tbl的所有选取的字段数据，即便在右侧表tcount_tbl中没有对应的runoob_author字段值。

MySQL RIGHT JOIN

mysql> SELECT b.runoob_id, b.runoob_author, a.runoob_count FROM tcount_tbl a RIGHT JOIN runoob_tbl b ON a.runoob_author = b.runoob_author;
+-------------+-----------------+----------------+
| runoob_id | runoob_author | runoob_count |
+-------------+-----------------+----------------+
|           1 | John Poul       |              1 |
|           2 | Abdul S         |           NULL |
|           3 | Sanjay          |              1 |
+-------------+-----------------+----------------+

以上实例中使用了 RIGHT JOIN，该语句会读取右边的数据表 runoob_tbl 的所有选取的字段数据，即便在左侧表tcount_tbl中没有对应的runoob_author字段值。

PHP语法：

$sql = 'SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author';

mysql_select_db('RUNOOB');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
    echo "Author:{$row['runoob_author']}  <br> ".
         "Count: {$row['runoob_count']} <br> ".
         "Tutorial ID: {$row['runoob_id']} <br> ".
         "--------------------------------<br>";
}

Mysql NULL值处理

产生背景

MySQL使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作。

三大运算符

IS NULL: 当列的值是NULL,此运算符返回true。
IS NOT NULL: 当列的值不为NULL, 运算符返回true。
<=>: 比较操作符（不同于=运算符），当比较的的两个值为NULL时返回true。

mysql语法

mysql> SELECT * FROM tcount_tbl 
    -> WHERE runoob_count IS NULL;
+-----------------+----------------+
| runoob_author | runoob_count |
+-----------------+----------------+
| mahnaz          |           NULL |
| Jen             |           NULL |
+-----------------+----------------+
2 rows in set (0.00 sec)
mysql> SELECT * from tcount_tbl 
    -> WHERE runoob_count IS NOT NULL;
+-----------------+----------------+
| runoob_author | runoob_count |
+-----------------+----------------+
| mahran          |             20 |
| Gill            |             20 |
+-----------------+----------------+
2 rows in set (0.00 sec)

注意：查找数据表中 runoob_count 列是否为 NULL，必须使用IS NULL和IS NOT NULL，如上实例。

php语法：

PHP脚本中你可以在 if…else 语句来处理变量是否为空，并生成相应的条件语句。以下实例中PHP设置了$runoob_count变量，然后使用该变量与数据表中的 runoob_count 字段进行比较：

if( isset($runoob_count ))
{
   $sql = 'SELECT runoob_author, runoob_count
           FROM  tcount_tbl
           WHERE runoob_count = $runoob_count';
}
else
{
   $sql = 'SELECT runoob_author, runoob_count
           FROM  tcount_tbl
           WHERE runoob_count IS $runoob_count';
}

mysql_select_db('RUNOOB');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
    echo "Author:{$row['runoob_author']}  <br> ".
         "Count: {$row['runoob_count']} <br> ".
         "--------------------------------<br>";
}