美文网首页数据库首页投稿（暂停使用，暂停投稿）程序员
数据库Day2：MySQL从0到1

数据库Day2：MySQL从0到1

作者: 王中阳 | 来源:发表于2016-06-17 19:28 被阅读498次

    术语

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

    数据库

    1. 创建数据库 CREATE
    2. 删除数据库 DROP
    3. 选择使用指定的数据库
    mysql_select_db( 'RUNOOB' );

    数据类型

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

    创建数据表

    1. key：表名，表字段名，定义每个表字段
    CREATE TABLE table_name (column_name column_type);
    1. PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。

    删除数据表

    DROP TABLE table_name ;

    插入数据 INSERT INTO

    INSERT INTO table_name (field1,field2,...fieldN) VALUES (value1,value2,...valueN);
    1. 如果数据是字符型，必须使用单引号或者双引号，如："value"。
    //php的sql语句可以这么写
$sql = "INSERT INTO runoob_tbl ".
       "(runoob_title,runoob_author, submission_date) ".
       "VALUES ".
       "('$runoob_title','$runoob_author','$submission_date')";
    1. 点. 之后的内容回车折行，方便阅读；SQL语句的命令结束符为分号（;）
    2. 注意插入数据的时候要进行安全性的检验，get_magic_quotes_gpc特殊字符转义

    查询数据 SELECT

    SQL语法：
    SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[OFFSET M ][LIMIT N]
    1. 查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件
    2. SELECT 命令可以读取一条或者多条记录。
    3. 你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
    4. 你可以使用 WHERE 语句来包含任何条件。
    5. 你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。
    6. 你可以使用 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，然后再打印出每个值。
    1. MYSQL_ASSOC这个返回的数组是以数据表中的字段为键的,而MYSQL_NUM是以数字为键的
    2. 记住如果你需要在字符串中使用变量，请将变量置于花括号中。
    3. PHP mysql_fetch_array()函数第二个参数为MYSQL_ASSOC， 设置该参数查询结果返回关联数组，你可以使用字段名称来作为数组的索引。
    4. 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>";
}
    1. 可以使用常量 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>";
}
    1. 以上三个实例输出结果都一样。
    内存释放
    1. 在我们执行完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>";
}
    1. 可以使用PHP函数的mysql_query()及相同的SQL SELECT 带上 WHERE 子句的命令来获取数据。
    2. 通过 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);
    1. PHP使用 mysql_query() 函数来执行SQL语句， 你可以在SQL DELETE命令中使用或不使用 WHERE 子句。
    2. 该函数与 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>";
}
    1. like需要和%配合使用，否则like的作用就是=
    2. %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";
    1. 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
    1. 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 |
+--------+--------------+
    1. 上面演示代码中的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

    1. 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 |
+-------------+-----------------+----------------+
    INNER JOIN
    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字段值。

    LEFT JOIN
    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字段值。


    RIGHT JOIN
    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 时，该命令可能就无法正常工作。

    三大运算符
    1. IS NULL: 当列的值是NULL,此运算符返回true。
    2. IS NOT NULL: 当列的值不为NULL, 运算符返回true。
    3. <=>: 比较操作符（不同于=运算符），当比较的的两个值为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>";
}

    注意

    1. 所有的数据库名，表名，表字段都是区分大小写的。所以在使用SQL命令时需要输入正确的名称。
    2. return retval,retval应该是一个BOOL型的变量。在方法正常执行成功后应该会返回true，否则是false。一般来说返回值都是用来判断函数是否正常执行结束的。

    一个敲代码，爱分享的人，我在这里！

    来玩啊

    相关文章

      网友评论

      本文标题：数据库Day2：MySQL从0到1

      本文链接：https://www.haomeiwen.com/subject/jgpvdttx.html

      延伸阅读

      深度阅读

      • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

      栏目导航

      热点阅读

      数据库

      首页投稿（暂停使用，暂停投稿）

      程序员

      编程学习

      iOS 开发成长中心

      关于我们|服务条款|联系我们|数据库Day2：MySQL从0到1|投稿指南|网站地图|RSS订阅|排版工具|手机版

      提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

      Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

      备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

      本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

      所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！