MySQL（上）

什么是数据库

数据以表格的形式出现
每行为各种记录名称
每列为记录名称所对应的数据域
许多的行和列组成一张表单
若干的表单组成database

术语

数据库: 数据库是一些关联表的集合。.
数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
列: 一列(数据元素) 包含了相同的数据, 例如邮政编码的数据。
行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
外键：外键用于关联两个表。
复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性

启动关闭MySQL

启动：在偏好中启动服务后输入
1
mysql -u root -p
关闭服务
1
exit

MySQL管理命令

USE 数据库名 :
选择要操作的Mysql数据库，使用该命令后所有Mysql命令都只针对该数据库
1
2
mysql> use RUNOOB;
Database changed

SHOW DATABASES:
列出 MySQL 数据库管理系统的数据库列表

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| RUNOOB             |
| cdcol              |
| mysql              |
| onethink           |
| performance_schema |
| phpmyadmin         |
| test               |
| wecenter           |
| wordpress          |
+--------------------+
10 rows in set (0.02 sec)

SHOW TABLES:
显示指定数据库的所有表，使用该命令前需要使用 use 命令来选择要操作的数据库

mysql> use RUNOOB;
Database changed
mysql> SHOW TABLES;
+------------------+
| Tables_in_runoob |
+------------------+
| employee_tbl     |
| runoob_tbl       |
| tcount_tbl       |
+------------------+
3 rows in set (0.00 sec)

SHOW COLUMNS FROM 数据表:
显示数据表的属性，属性类型，主键信息，是否为 NULL，默认值等其他信息

mysql> SHOW COLUMNS FROM runoob_tbl;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| runoob_id       | int(11)      | NO   | PRI | NULL    |       |
| runoob_title    | varchar(255) | YES  |     | NULL    |       |
| runoob_author   | varchar(255) | YES  |     | NULL    |       |
| submission_date | date         | YES  |     | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

SHOW COLUMNS要求给出一个表名，它对每个字段返回一行，行中包含字段名、数据类型、是否允许NULL、键信息、默认值以及其他信息（如auto_increment）
auto_increment：自动增量，某些表列需要唯一值。例如，订单编号、雇员ID或（如上面例子中所示的）顾客ID。在每个行添加到表中时，MySQL可以自动地为每个行分配下一个可用编号，不用在添加一行时手动分配唯一值（这样做必须记住最后一次使用的值）。这个功能就是所谓的自动增量。如果需要它，则必须在用CREATE语句创建表时把它作为表定义的组成部分。
DESCRIBE语句：DESCRIBE runoob_tbl等同于SHOW COLUMNS FROM runoob_tbl

SHOW语句的其他支持：
- SHOW STATUS,用于显示广泛的服务器状态信息
- SHOW CREATE DATABASE + 数据库名;和SHOW CREATE TABLE + 表名;分别用来显示创建特定数据库或表的MySQL语句
- SHOW GRANTS，用来显示授权用户
- SHOW ERRORS和SHOW WARNINGS，用来显示服务器错误或警告信息

SHOW INDEX FROM 数据表:
显示数据表的详细索引信息，包括PRIMARY KEY（主键）

mysql> SHOW INDEX FROM runoob_tbl;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| runoob_tbl |          0 | PRIMARY  |            1 | runoob_id   | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

SHOW TABLE STATUS LIKE [FROM db_name] [LIKE ‘pattern’] \G:
该命令将输出Mysql数据库管理系统的性能及统计信息

mysql> SHOW TABLE STATUS  FROM RUNOOB;   # 显示数据库 RUNOOB 中所有表的信息

mysql> SHOW TABLE STATUS from RUNOOB LIKE 'runoob%';     # 表名以runoob开头的表的信息
mysql> SHOW TABLE STATUS from RUNOOB LIKE 'runoob%'\G;   # 加上 \G，查询结果按列打印

使用HELP SHOW来显示更多的命令

检索数据

检索单个列

SELECT 列名 FROM 表名
1
SELECT id FROM first_test
返回的数据是未排序数据

检索多个列

SELECT 列名,列名,列名 FROM 表名
1
SELECT id,title,author FROM first_test

检索所有列

SELECT * FROM 表名

一般，除非你确实需要表中的每个列，否则最好别使用*通配符。
通配符可以检索到未知列

检索不同的行

SELECT DISTINCT vend_id FROM products

SELECT DISTINCT title FROM first_test;
+-------+
| title |
+-------+
| js    |
| node  |
+-------+

DISTINCT关键字应用于所有列

SELECT DISTINCT title,author FROM first_test;
+-------+--------+
| title | author |
+-------+--------+
| js    | lala   |
| node  | nana   |
| node  | rere   |
+-------+--------+

限制结果

LIMIT可以返回第一行或前几行

SELECT id FROM first_test LIMIT 2;
+----+
| id |
+----+
|  1 |
|  2 |
+----+

可以指定开始行和行数（开始行从0开始）
1
SELECT id FROM first_test LIMIT 2,3;

使用完全限定的表名

1	SELECT first_test.id FROM first_test;

这个语句在功能上与开始的那一条相同，但是这里指定了一个完全限定的列名

1	SELECT first_test.id FROM test.first_test;

这个语句表名也是完全限定的

排序检索数据

使用ORDER BY子句进行排序

SELECT title FROM first_test ORDER BY id
SELECT title FROM first_test ORDER BY titile
SELECT title FROM first_test ORDER BY date
#这些都是合法的

按多个列排序

SELECT * FROM first_test ORDER BY title,date;
+----+-------+--------+------------+
| id | title | author | date       |
+----+-------+--------+------------+
|  1 | js    | lala   | 2018-01-29 |
|  2 | node  | nana   | 2018-01-30 |
|  3 | node  | rere   | 2018-01-31 |
|  5 | sql   | aeae   | 2018-01-08 |
+----+-------+--------+------------+

先按title排序，如果title相同，再按照date排序

指定排序方向

SELECT * FROM first_test ORDER BY date DESC;
+----+-------+--------+------------+
| id | title | author | date       |
+----+-------+--------+------------+
|  3 | node  | rere   | 2018-01-31 |
|  2 | node  | nana   | 2018-01-30 |
|  1 | js    | lala   | 2018-01-29 |
|  5 | sql   | aeae   | 2018-01-08 |
+----+-------+--------+------------+

DESC只是应用到直接位于其前面的列名，如下

SELECT * FROM first_test ORDER BY date DESC,author;
+----+-------+--------+------------+
| id | title | author | date       |
+----+-------+--------+------------+
|  3 | node  | rere   | 2018-01-31 |
|  2 | node  | nana   | 2018-01-30 |
|  1 | js    | lala   | 2018-01-29 |
|  6 | sql   | aaaa   | 2018-01-08 |
|  5 | sql   | aeae   | 2018-01-08 |
+----+-------+--------+------------+

在多个列上进行降序排序必须为每个列指定DESC关键字
LIMIT子句位于ORDER BY子句之后

过滤数据

使用WHERE子句

SELECT * FROM first_test WHERE title='node';
+----+-------+--------+------------+
| id | title | author | date       |
+----+-------+--------+------------+
|  2 | node  | nana   | 2018-01-30 |
|  3 | node  | rere   | 2018-01-31 |
+----+-------+--------+------------+

WHERE在ORDER BY之前

范围值检查

SELECT * FROM first_test WHERE date BETWEEN '2018-01-05' AND '2018-01-10';
+----+-------+--------+------------+
| id | title | author | date       |
+----+-------+--------+------------+
|  5 | sql   | aeae   | 2018-01-08 |
|  6 | sql   | aaaa   | 2018-01-08 |
+----+-------+--------+------------+

空值检查

SELECT * FROM first_test WHERE date IS NULL;
+----+-------+--------+------+
| id | title | author | date |
+----+-------+--------+------+
|  7 | html  | caca   | NULL |
+----+-------+--------+------+

WHERE高级应用

AND操作符

SELECT * FROM first_test WHERE title = 'node' AND author = 'rere';
+----+-------+--------+------------+
| id | title | author | date       |
+----+-------+--------+------------+
|  3 | node  | rere   | 2018-01-31 |
+----+-------+--------+------------+

AND可以使用多个来连接多个条件

OR操作符

SELECT * FROM first_test WHERE title = 'node' OR author = 'lala';
+----+-------+--------+------------+
| id | title | author | date       |
+----+-------+--------+------------+
|  1 | js    | lala   | 2018-01-29 |
|  2 | node  | nana   | 2018-01-30 |
|  3 | node  | rere   | 2018-01-31 |
+----+-------+--------+------------+

AND的优先级高于OR，可以使用圆括号来约束

IN操作符

FROM first_test WHERE title IN ('js','html');
+----+-------+--------+------------+
| id | title | author | date       |
+----+-------+--------+------------+
|  1 | js    | lala   | 2018-01-29 |
|  7 | html  | caca   | NULL       |
+----+-------+--------+------------+

匹配括号中的的条件

NOT操作符

SELECT * FROM first_test WHERE title NOT IN ('node','html');
+----+-------+--------+------------+
| id | title | author | date       |
+----+-------+--------+------------+
|  1 | js    | lala   | 2018-01-29 |
|  5 | sql   | aeae   | 2018-01-08 |
|  6 | sql   | aaaa   | 2018-01-08 |
+----+-------+--------+------------+

使用通配符过滤

百分号（%）通配符
表示任何字符出现任意次数

SELECT * FROM first_test WHERE title LIKE 'no%';
+----+-------+--------+------------+
| id | title | author | date       |
+----+-------+--------+------------+
|  2 | node  | nana   | 2018-01-30 |
|  3 | node  | rere   | 2018-01-31 |
+----+-------+--------+------------+

检索以no开头的词

SELECT * FROM first_test WHERE title LIKE '%od%';
+----+-------+--------+------------+
| id | title | author | date       |
+----+-------+--------+------------+
|  2 | node  | nana   | 2018-01-30 |
|  3 | node  | rere   | 2018-01-31 |
+----+-------+--------+------------+

检索包含od的词

下划线（_）通配符
只匹配单个字符

SELECT * FROM first_test WHERE title LIKE '_od_';
+----+-------+--------+------------+
| id | title | author | date       |
+----+-------+--------+------------+
|  2 | node  | nana   | 2018-01-30 |
|  3 | node  | rere   | 2018-01-31 |
+----+-------+--------+------------+

通配符使用注意事项
- 不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
- 在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。
- 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。

使用正则表达式

基本字符匹配

SELECT * FROM first_test WHERE title REGEXP 'od';
+----+-------+--------+------------+
| id | title | author | date       |
+----+-------+--------+------------+
|  2 | node  | nana   | 2018-01-30 |
|  3 | node  | rere   | 2018-01-31 |
+----+-------+--------+------------+

若区分大小写可以使用BINARY关键字，添加在REGEXP后

OR匹配

SELECT * FROM first_test WHERE title REGEXP 'no|j';
+----+-------+--------+------------+
| id | title | author | date       |
+----+-------+--------+------------+
|  1 | js    | lala   | 2018-01-29 |
|  2 | node  | nana   | 2018-01-30 |
|  3 | node  | rere   | 2018-01-31 |
+----+-------+--------+------------+

匹配几个字符之一（并不是之一，有就行）

SELECT * FROM first_test WHERE title REGEXP '[xht]ml';
+----+-------+--------+------+
| id | title | author | date |
+----+-------+--------+------+
|  7 | html  | caca   | NULL |
|  8 | xml   | aaa    | NULL |
+----+-------+--------+------+

创建计算字段

拼接字段

SELECT Concat(title, '-', author) FROM first_test;
+----------------------------+
| Concat(title, '-', author) |
+----------------------------+
| js-lala                    |
| node-nana                  |
| node-rere                  |
| sql-aeae                   |
| sql-aaaa                   |
| html-caca                  |
| xml-aaa                    |
+----------------------------+

RTrim()去掉值右边的空格（RTrim()是去除左边的空格）

使用别名

SELECT Concat(title,'-',author) AS new_title FROM first_test;
+-----------+
| new_title |
+-----------+
| js-lala   |
| node-nana |
| node-rere |
| sql-aeae  |
| sql-aaaa  |
| html-caca |
| xml-aaa   |
+-----------+

执行算数运算

1	SELECT prod_id, quantity, item_price, quantity* item_price AS expanded_price FROM orderitems WHERE order_num = 20005;

使用数据处理函数

日期和时间处理函数

AddDate()增加一个日期（天、周等）
AddTime()增加一个时间（时、分等）
CurDate()返回当前日期
CurTime()返回当前时间
Date()返回日期时间的日期部分
DateDiff()计算两个日期之差
Date_Add()高度灵活的日期运算函数
Date_Format()返回一个格式化的日期或时间串
Day()返回一个日期的天数部分
DayOfWeek()对于一个日期，返回对应的星期几
Hour()返回一个时间的小时部分
Minute()返回一个时间的分钟部分
Month()返回一个日期的月份部分
Now()返回当前日期和时间
Second()返回一个时间的秒部分
Time()返回一个日期时间的时间部分

Year()返回一个日期的年份部分

SELECT id,title FROM first_test WHERE DATE(date) = '2018-1-30';
+----+-------+
| id | title |
+----+-------+
|  2 | node  |
+----+-------+

检索2月份的数据

SELECT * FROM first_test WHERE Date(date) BETWEEN '2018-2-01' AND '2018-2-28';
+----+-------+--------+------------+
| id | title | author | date       |
+----+-------+--------+------------+
|  7 | html  | caca   | 2018-02-20 |
|  8 | xml   | aaa    | 2018-02-13 |
+----+-------+--------+------------+
#还有一种写法
SELECT * FROM first_test WHERE Year(date) = 2018 AND Month(date) = 2;
+----+-------+--------+------------+
| id | title | author | date       |
+----+-------+--------+------------+
|  7 | html  | caca   | 2018-02-20 |
|  8 | xml   | aaa    | 2018-02-13 |
+----+-------+--------+------------+

汇总数据

聚集函数
- AVG()返回某列的平均值
- COUNT()返回某列的行数
- MAX()返回某列的最大值
- MIN()返回某列的最小值
- SUM()返回某列值之和

COUNT()函数

COUNT(*)对表中行的数目进行计数

使用COUNT(column)对特定列中具有值得行计数，忽略NULL

SELECT COUNT(*) AS RAWNUM FROM first_test;
+--------+
| RAWNUM |
+--------+
|      7 |
+--------+
#
SELECT COUNT(date) AS nodeNum FROM first_test;
+---------+
| nodeNum |
+---------+
|       6 |
+---------+

分组数据

创建分组

SELECT title,count(*) AS titleNum from first_test GROUP BY title;
+-------+----------+
| title | titleNum |
+-------+----------+
| html  |        1 |
| js    |        1 |
| node  |        2 |
| sql   |        2 |
| xml   |        1 |
+-------+----------+

GROUP BY指示按vend_id排序并分组数据，所以count(*)不是直接计算整个表，而是每个组计算一遍
分组规定：GROUPBY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。
如果在GROUPBY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
GROUPBY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUPBY子句中指定相同的表达式。不能使用别名。
除聚集计算语句外，SELECT语句中的每个列都必须在GROUPBY子句中给出。
如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
GROUPBY子句必须出现在WHERE子句之后，ORDERBY子句之前。

#使用WITH ROLLUP关键字，可以得到每个分组及汇总级别
SELECT title,count(*) AS titleNum from first_test GROUP BY title WITH ROLLUP;
+-------+----------+
| title | titleNum |
+-------+----------+
| html  |        1 |
| js    |        1 |
| node  |        2 |
| sql   |        2 |
| xml   |        1 |
| NULL  |        7 |
+-------+----------+

过滤分组HAVING

SELECT date, COUNT(*) AS count FROM first_test GROUP BY date HAVING COUNT(*)>=2;
+------------+-------+
| date       | count |
+------------+-------+
| 2018-01-08 |     2 |
| 2018-02-20 |     2 |
+------------+-------+

将数据按照date分组，并选出数目大于等于2的组并显示

SELECT子句顺序
- SELECT
- FROM
- WHERE 行级过滤
- GROUP BY 分组说明
- HAVING 组级过滤
- ORDER BY 排列顺序
- LIMIT 紧缩行数

子查询

1	SELECT * FROM nirvana.user WHERE id IN (SELECT unique_key FROM nirvana_admin.user_action_record WHERE action_id = 'updateMobile');

子查询总是从内向外的

联结

关系表
- 外键：某个表中的一列，包含另一个表的主键
- 可伸缩性：能够适应不断增加的工作量而不失败。
联结不是物理实体。换句话说，它在实际的数据库表中不存在。联结由MySQL根据需要建立，它存在于查询的执行当中。

创建联结

1
2
3

SELECT * FROM 
	nirvana.user, nirvana_admin.user_action_record 
	WHERE nirvana.user.id=nirvana_admin.user_action_record.unique_key;

内部联结

1 2	SELECT * FROM nirvana.user INNER JOIN nirvana_admin.user_action_record ON nirvana.user.id=nirvana_admin.user_action_record.unique_key;

创建高级联结

使用表别名

1	SELECT * FROM nirvana.user AS user, nirvana_admin.user_action_record AS action WHERE user.id = action.unique_key;

在FROM后使用AS创建表别名，与列别名不同的是，表别名不返回给客户机

自联结
1
SELECT u2.name FROM nirvana.user AS u1, nirvana.user AS u2 WHERE u1.register_channel_code = u2.register_channel_code AND u1.name = '邰龙飞';
这条语句使用两个别名命名了一个表，用途是查到与名字叫邰龙飞的用户注册渠道相同的用户
虽然此功能也可用子查询替代，但是自联结的效率更好

自然联结
无论何时对表进行联结，应该至少有一个列出现在不止一个表中（被联结的列）。
内部联结返回所有数据，甚至相同的列多次出现，而自然联结排除多次出现，每列只返回一次

#L表：
SELECT * FROM L;
+---+------+------+
| A | B    | C    |
+---+------+------+
| 1 | a    |    3 |
| 2 | b    |    6 |
| 3 | c    |    7 |
+---+------+------+
#R表：
SELECT * FROM R;
+---+------+------+
| A | D    | E    |
+---+------+------+
| 1 | a    |    3 |
| 3 | c    |    7 |
| 4 | d    |    5 |
+---+------+------+
#使用自然联结
SELECT * FROM L natural JOIN R;
+---+------+------+------+------+
| A | B    | C    | D    | E    |
+---+------+------+------+------+
| 1 | a    |    3 | a    |    3 |
| 3 | c    |    7 | c    |    7 |
+---+------+------+------+------+
#相当于：
SELECT L.*,R.D,R.E FROM L,R WHERE L.A=R.A;
+---+------+------+------+------+
| A | B    | C    | D    | E    |
+---+------+------+------+------+
| 1 | a    |    3 | a    |    3 |
| 3 | c    |    7 | c    |    7 |
+---+------+------+------+------+

自然联结会自动将列名相同的列合并，然后返回对应行其余的列，如果L，R表是这样：

SELECT * FROM L;
+---+------+------+
| A | B    | C    |
+---+------+------+
| 1 | a    |    3 |
| 2 | b    |    6 |
| 3 | c    |    7 |
+---+------+------+
SELECT * FROM R;
+---+------+------+
| A | B    | C    |
+---+------+------+
| 1 | a    |    3 |
| 3 | c    |    7 |
| 4 | d    |    5 |
+---+------+------+
#使用自然联结
mysql> SELECT * FROM L natural JOIN R;
+---+------+------+
| A | B    | C    |
+---+------+------+
| 1 | a    |    3 |
| 3 | c    |    7 |
+---+------+------+

一旦有一列不同则此行不显示

外部联结
与内部联结类似不过当使用LEFT JOIN时左边的表会显示所有行，右表中的列没有对应数据时会显示NULL，使用RIGHT JOIN时右边的表会显示所有行

#左
SELECT * FROM L LEFT JOIN R ON L.A=R.A;
+---+------+------+------+------+------+
| A | B    | C    | A    | B    | C    |
+---+------+------+------+------+------+
| 1 | a    |    3 |    1 | a    |    3 |
| 2 | b    |    6 | NULL | NULL | NULL |
| 3 | c    |    7 |    3 | c    |    7 |
+---+------+------+------+------+------+
#右
SELECT * FROM L RIGHT JOIN R ON L.A=R.A;
+------+------+------+---+------+------+
| A    | B    | C    | A | B    | C    |
+------+------+------+---+------+------+
|    1 | a    |    3 | 1 | a    |    3 |
|    3 | c    |    7 | 3 | c    |    7 |
| NULL | NULL | NULL | 4 | d    |    5 |
+------+------+------+---+------+------+

#SQL