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 JOIN 与JOIN有所不同。 它会读取左数据表的全部数据，即便右数据表无对应数据。

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

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定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;
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 206,126评论 6赞 481
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 88,254评论 2赞 382
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 152,445评论 0赞 341
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 55,185评论 1赞 278
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 64,178评论 5赞 371
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,970评论 1赞 284
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,276评论 3赞 399
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,927评论 0赞 259
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 43,400评论 1赞 300
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,883评论 2赞 323
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,997评论 1赞 333
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,646评论 4赞 322
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,213评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,204评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,423评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,423评论 2赞 352
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,722评论 2赞 345