MySQL数据库及表的操作（创建、删除、更新、分组查询、模糊查询、排序）

「爱情、让人受尽委屈。」 2022-05-12 19:22 96阅读 0赞

##### 创建数据库 #####

create database \[数据库名\]；

--------------------

##### 删除数据库 #####

drop database \[数据库名\]；

--------------------

##### 创建数据表 #####

CREATE TABLE table\_name (column\_name column\_type);  
eg:

CREATE TABLE tutorials_tbl(
       tutorial_id INT NOT NULL AUTO_INCREMENT,
       tutorial_title VARCHAR(100) NOT NULL,
       tutorial_author VARCHAR(40) NOT NULL,
       submission_date DATE,
       PRIMARY KEY ( w3cschool_id )                      --设置主键
    );

--------------------

##### 删除数据表 #####

DROP TABLE table\_name ;

--------------------

##### 插入数据 #####

INSERT INTO table\_name ( field1, field2,…fieldN ) VALUES ( value1, value2,…valueN );

--------------------

##### 查询数据 #####

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

--------------------

##### 更新数据 #####

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

--------------------

##### 删除数据 #####

DELETE FROM table_name [WHERE Clause]

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

--------------------

##### LIKE 子句 #####

SQL LIKE 子句中使用百分号(%)字符来表示任意字符，类似于UNIX或正则表达式中的星号 (\*)。  
如果没有使用百分号(%), LIKE 子句与等号（=）的效果是一样的。

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

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

--------------------

##### 排序 #####

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

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

--------------------

##### GROUP BY 语句 #####

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

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

实例~表结构及数据

CREATE TABLE `employee_tbl` (
      `id` int(11) NOT NULL,
      `name` char(10) NOT NULL DEFAULT '',
      `date` datetime NOT NULL,
      `singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    --  Records of `employee_tbl`
    -- ----------------------------
    BEGIN;
    INSERT INTO `employee_tbl` VALUES ('1', '小明', '2016-04-22 15:25:33', '1'), ('2', '小王', '2016-04-20 15:25:47', '3'), ('3', '小丽', '2016-04-19 15:26:02', '2'), ('4', '小王', '2016-04-07 15:26:14', '4'), ('5', '小明', '2016-04-11 15:26:40', '4'), ('6', '小明', '2016-04-04 15:26:54', '2');
    COMMIT;

导入成功后，执行SELECT \* FROM employee\_tbl;

mysql> set names utf8;
    mysql> SELECT * FROM employee_tbl;
    +----+--------+---------------------+--------+
    | id | name   | date                | singin |
    +----+--------+---------------------+--------+
    |  1 | 小明 | 2016-04-22 15:25:33 |      1 |
    |  2 | 小王 | 2016-04-20 15:25:47 |      3 |
    |  3 | 小丽 | 2016-04-19 15:26:02 |      2 |
    |  4 | 小王 | 2016-04-07 15:26:14 |      4 |
    |  5 | 小明 | 2016-04-11 15:26:40 |      4 |
    |  6 | 小明 | 2016-04-04 15:26:54 |      2 |
    +----+--------+---------------------+--------+
    6 rows in set (0.00 sec)

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

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

**使用 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 |
    +--------+--------------+
    4 rows in set (0.00 sec)

其中记录 NULL 表示所有人的登录次数。  
我们可以使用 coalesce 来设置一个可以取代 NUll 的名称，coalesce 语法：

select coalesce(a,b,c);

参数说明：如果anull,则选择b；如果bnull,则选择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)

--------------------

##### MySQL 连接的使用 #####

前面的内容都是在一张表中读取数据，当需要查询多张表的数据时会如何？  
使用 MySQL 的 JOIN 在两个或多个表中查询数据。  
你可以在SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询。  
JOIN 按照功能大致分为如下三类：

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