MySQL「查询/联表查询/索引/序列/导出」

MySQL,关系型数据库(RDBMS),有几个关键术语需要巩固一下

  • 冗余:存储两倍数据,冗余降低了性能,但提高了数据的安全性。
  • 主键:主键是唯一的。一个数据表中职能包含一个主键,你可以使用主键来查询。
  • 复合键:复合键(组合键)将 多个列作为一个索引值,一般用于复合索引。
  • 索引:使用索引可以快速访问数据库表中的特定信息。索引是对数据库表中的一列或多了的值进行排序的一种结构,类似与书籍的目录。
  • 参照完整性:参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件。目的是保证数据的一致性。

# SELECT 基本语法

  MySQL数据库使用SQL SELECT语句来查询数据,其基础语法如下:

SELECT field1, filed2, ...filedn
FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2...]
[LIMIT N][ OFFSET M]

SELECT命令可以读取一条或多条记录
SELECT关键字,必填,跟不同的列名,用逗号隔开,表示要返回的查询结果集;
FROM 必填,跟表名,表示从哪个表查询;指定多个表时用逗号隔开。
WHERE 可选,跟查询条件;使用AND(逻辑与)OR(逻辑或)可添加条件组合,
LIMIT 可选,对查询结果进行条数的限制,当库扫描表查询结果等于limit值时将不再扫描。
OFFSET 可选,指定开始查询的偏移量。


WHERE 查询条件

WHERE在语句中,可以指定查询条件,可以使用AND或者OR指定一个或多个条件;他有如下操作符可供选择,假设 A = 10, B = 20,则:

操作符 描述 实例
= 等于 (A = B) 返回false
!=, <> 不等于 (A != B) 返回true
> 大于 (A > B) 返回false
< 小于 (A < B) 返回true
>= 大于等于 (A >= B) 返回false
<= 小于等于 (A <= B) 返回true

  在常规的查询操作中,使用主键来作为WHERE子句的查询条件能够提高很大查询效率

SELECT f_code, f_name, f_type, f_cors_type
FROM t_label
WHERE f_type = 5 AND f_cors_type=1
LIMIT 30;

WHERE ... LIKE 模糊匹配

  在WHERE中使用等号=来设定获取数据的条件,MySQL支持使用LIKE子句替代 = 来实现模糊匹配,表示查询数据源中包含查询条件的记录。

LIKE通常和%一同使用,类似于一个元字符的搜索,同样,可以使用 AND 或者 OR 来组合条件查询。

SELECT f_code, f_name, f_type, f_source, f_cors_type
FROM t_iam_label
WHERE f_name LIKE '%负责人' AND f_cors_type=1

值得注意的是,如果没有使用%来修饰,如以上为 LIKE '负责人',则效果等同于 =,为精确匹配。

几中常见的用法如下

用法 说明
‘%A’ 以A为结尾的数据
‘A%’ 以A为开头的数据
‘%A%’ 含有a的数据
'_A_' 三个字符且中间字母是A
'_A' 两位且以A结尾
‘A_’ 两位且以A开头
[AB] 出现A或B
[^A] 不出现 A
[%] 含字符%的数据 (%识别为普通字符)

UNION 操作符

  MySQL UNION 操作符用于连接两个以上的 SELECT 语句的查询结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。语法如下:

SELECT field1, field2, ...fieldn
FROM table1
[WHERE condition]
UNION [ALL | DISTINCT]
SELECT fielda, fieldb, ...fieldn
FROM table2
[WHERE condition]

ALL:可选,表示返回所有结果集,包含重复数据
DISTINCT:可选,表示返回已过滤重复数据的结果集。注,UNION默认会过滤重复数据。

需要注意的是,联合的两个表列数必须相同。以上案例均为n列。两个select查询可以查询不同的表。查询结果会被拼接成一个表。先写的 select查询结果在前。

ORDER_BY 排序

  使用ORDER BY 子句来设定你想按哪个字段那种方式来进行排序,再返回结果。

SELECT field1, field2, field3
FROM table1
ORDER BY field1 [ASC [DESC][默认ASC]], [field2...] [ASC [DESC]]

ASC:升序
DESC:降序
  你可以使用任何字段来作为排序条件,也可以设定多个字段来进行排序,使用ASC来指定升序,或DESC来指定降序排列,默认为ASC升序。

GROUP BY 分组

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

SELECT field1, field2, function(field3) as another_name
FROM table
WHERE field operator value
GROUP BY field

function:COUNT,SUM,AVG等计算函数,as表示将计算的结果重新命名
WITH ROLLUP: 可以实现在分组统计数据基础上再进行相同的统计


# 联表查询

  在MySQL中,使用JOIN来联合查询,JOIN按照功能可分为三类:

(0), 逗号连接

  逗号连接其实也是内连接(INNER JOIN),但其效率没有内连接使用的效率高

SELECT a.field1, a.field2, b.field1
FROM table1 a, table2 b
ON a.field3 = b.field3
(1)INNER JOIN 或 JOIN:内连接或等值连接

 用来获取两个表中字段匹配的关系的记录。

SELECT a.field1, a.field2, b.field1
FROM table1 a INNER JOIN table2 b
ON a.field3 = b.field3
内连接查询结果集
(2)LEFT JOIN

  MySQLLEFT JOINJOIN有所不同。 它会读取左数据表的全部数据,即便右数据表无对应数据。

SELECT a.field1, a.field2, b.field1
FROM table1 a LEFT JOIN table2 b
ON a.field3 = b.field3
左连接查询结果集
(3)RIGHT JOIN

  MySQL RIGHT JOIN 会读取右边数据表的全部数据,即便左边边表无对应数据。

SELECT a.field1, a.field2, b.field1
FROM table1 a RIGHT JOIN table2 b
ON a.field3 = b.field3
由连接查询结果集

# 索引

  索引的建立对于MySQL来说很重要,他可以大大提高MySQL的检索速度。
  索引其实也是一张表,该表存储了主键与索引字段,并指向实体表的记录。适当的建立索引可以大大提高了检索速度,滥用则所降低更新表的速度。因为在使用INSER、UPDATE、DELETE等命令时,他们不仅需要更新表,还需要更新索引。

1. 普通索引

(1)创建一个基本的索引

CREATE INDEX indexName ON table1

如果是CHARVARCHAR类型,length可以小于字段实际长度;如果是BLOBTEXT类型,必须指定length
(2)为某个表添加索引(修改表结构)

ALTER table table1 ADD INDEX indexName(columnName)

(3)创建表的时候直接指定

CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAT(16) NOT NULL,
INDEX [indexName] (username(length))
)

(4)删除索引

DROP INDEX [indexName] ON table1

2. 唯一索引

  它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。它有以下几种创建方式:
(1)创建索引

CREATE UNIQUE INDEX indexName ON table1(username(length))

(2)为某个表添加索引 (修改表结构)

ALTER table mytable ADD UNIQUE [indexName] (username(length))

(3)创建表的时候直接指定

CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAT(16) NOT NULL,
UNIQUE [indexName] (username(length))
)

3. 使用 ALTER 命令添加和删除索引

有四种方式添加索引
(1)添加一个主键,这意味着索引值必须是唯一的,且不能为NULL

ALTER TABLE table1 ADD PRIMARY KEY (column_list)

(2)创建唯一索引,索引值必须唯一,可以为NULL,且NULL可能出现多次

ALTER TABLE table1 ADD UNIQUE index_name (column_list)

(3)添加普通索引,索引值可出现多次

ALTER TABLE table1 ADD INDEX index_name (column_list)

(4)创建全文索引 FULLTEXT

ALTER TABLE table1 ADD FULLTEXT index_name (column_list)


使用DROP来删除索引

ALTER TABLE table1 DROP INDEX index_name;

4. 使用 ALTER 命令添加和删除主键

(1)添加主键时需要先确保主键默认不为空,在执行ADD操作

ALTER TABLE table1  MODIFY i INT NOT NULL
ALTER TABLE testalter_tbl ADD PRIMARY KEY (i)

(2)删除主键:删除主键时只需指定PRIMARY KEY,但在删除索引时,你必须知道索引名

ALTER TABLE table1 DROP PRIMARY KEY
5. 显示索引信息

  你可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。

SHOW INDEX FROM table1; \G


# 临时表 TEMPORARY TABLE

  临时表只在当前连接可见,当关闭连接时,Mysql会自动删除表并释放所有空间。

创建一个临时表
CREATE TEMPORARY TABLE table1 (
ID INT NOT NULL,
username VARCHAT(16) NOT NULL,
UNIQUE [indexName] (username(length))
)

  使用SHOW TABLES命令显示数据表列表时,无法查看到临时表。

删除临时表

  删除临时表和删除普通标没什么区别,使用DROP命令如下:

DROP TABLE table1


# 序列

  序列是一组整数:1, 2, 3, ...,由于一张数据表只能有一个自增字段即主键, 如果你想让其他字段也实现自动增加,就会使用到MySQL序列。

(1)使用 AUTO_INCREMENT
CREATE TEMPORARY TABLE table1 (
ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
username VARCHAT(16) NOT NULL,
UNIQUE [indexName] (username(length))
)
(2)获取AUTO_INCREMENT

  可以使用 SQL中的LAST_INSERT_ID( ) 函数来获取最后的插入表中的自增列的值

(3)重置序列

  此操作需要先删除原有序列,再添加。如果在删除的同时又有新记录添加,有可能会出现数据混乱,因此库不易变更时谨慎操作

ALTER TABLE insect DROP id;
ALTER TABLE insect
ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (id);
(4)设置序列的开始值

  一般情况下序列的开始值为1,但如果你需要指定一个开始值100,那我们可以通过以下语句来实现:

CREATE TABLE insect (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id),
  name VARCHAR(30) NOT NULL, 
  date DATE NOT NULL,
  origin VARCHAR(30) NOT NULL
) engine=gee auto_increment=100 charset=utf8;

或者使用修改表的方式设置

ALTER TABLE t AUTO_INCREMENT = 100;


# 导出数据

  MySQL中你可以使用SELECT...INTO OUTFILE语句来简单的导出数据到文本文件上。

  以下实例中我们将数据表 table1 数据导出到 /tmp/table1.txt 文件中:

SELECT * FROM table1
INTO OUTFILE '/tmp/table1.txt';

  你可以通过命令选项来设置数据输出的指定格式,以下实例为导出 CSV 格式:

SELECT * FROM passwd INTO OUTFILE '/tmp/runoob.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

  在下面的例子中,生成一个文件,各值用逗号隔开。这种格式可以被许多程序使用。

SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;