MySQL基础命令总结

[luoxn28]

MySQL 最流行的关系型数据库管理系统，在 WEB 应用方面MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。MySQL将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

数据库基础概念

• 数据库(database): 保存有组织的数据的容器。
• 表(table): 某种特定类型数据的结构化清单。
• 列(column): 表中的一个字段，所有表都是由一个或多个列组成的。
• 数据类型(datatype): 所容许的数据类型，每个表列都有相应的数据类型，它限制该类所存储的类型。
• 行(row): 是表中的一个记录。
• 主键(primary key): 是一列(或者一组列)，其值能够唯一区分表中每个行。

MySQL逻辑架构

MySQL服务器逻辑分为3层架构，最上层是网络服务层，比如连接处理、授权认证、安全等。第二层是核心服务层，包括查询解析、分析、优化、缓存以及所有的内置函数（例如日期/时间/数学等），所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图。

每个客户端连接都会在服务器中拥有一个线程，这个连接的查询只会在这个单独的线程中执行。MySQL中处理并发操作，会使用锁机制，锁包括表锁和行级锁，表锁会锁定整个表，行级锁最大程序支持了并发处理，行级锁只在存储引擎中实现，而MySQL服务器层没有实现。
MySQL实现了各种死锁检测和死锁超时机制，InnoDB目前处理死锁的机制是，将持有最少行级排它锁的事务进行回滚。

事务

事务是一组原子性的SQL查询（或者说独立的一个执行单元），事务中的语句，要么全部执行成功，要么全部执行失败。MySQL服务层不管理事务，事务是由下层的存储引擎实现的。
事务的四个特性：

• 原子性：一个事务为一个不可分割的最小工作单元，事务中操作要么全部提交成功，要不全部执行失败回滚。
• 一致性：数据库总是从一个一致性状态转换到另一个一致性状态，所以事务中部分的修改提交不会保存到数据库中。
• 隔离性：通常来说，一个事务所做的修改在最终提交之前，对其他事务是不可见的。
• 持久性：一旦事务提交，其所做的修改会永久保存到数据库中。

事务的四种隔离级别

• READ UNCOMMITTED(未提交读)：事物中的修改，即使没有提交，对其他事务也是可见的。
• READ COMMITTED(提交读)：一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的，只能”看到”自己事务提交的修改。
• REPEATABLE READ(可重复读)：保证了一个事务多次读取相同的记录的结果是一样的，但是该级别还是无法解决”幻读”问题，即当某个事务在读取某个范围内的数据时，另外一个事务又在该范围内插入了新的数据，当之前的事务再次读取该范围内的数据时，会产生幻行，InnoDB通过多版本并发机制(MVCC)解决幻读的问题。
• SERIALIZABLE(可串行化)：最高的隔离级别，强制事务串行化，避免了前面提到的幻行问题。

存储引擎

在MySQL中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。

InnoDB

InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，InnoDB是MySQL默认的引擎。

MyISAM

MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事物。

MEMORY存储引擎

MEMORY存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。

使用MySQL

mysql -h host -u username -p 与数据库建立连接
use database_name 选择数据库
show databases 显示所有的数据库
show tables 显示当前数据库下所有数据表
show columns from table_name 显示表的列信息，作用和desc table_name是一样的

MySQL支持的其他show语句还有：

show status 显示MySQL服务器状态信息
show create database xxx和show create table xxx 分别显示创建特定数据库和数据表的MySQL语句
show grants 显示授予用户的安全权限
show errors和show warnings 显示服务器错误或警告信息

检索数据

SQL语句中最常用的就是select语句了，它用来在一个或多个表中检索数据，select使用示例如下：

select column_name from table_name 从table_name数据表中检索column_name列，检索单个列
select column1_name, column2_name from table_name 检索多个列
select * from table_name 检索所有列

注意，select返回所有匹配的行，但是，如果我们不想某个值每次都出现，那怎么办呢？例如，想让select id from xxx结果中id值唯一，则可以在select语句中添加distinct关键字：
select distinct id from goods
有时我们想限制输出的结果，比如返回第一行或前几行，可使用limit子句，如下所示：

select * from goods limit 2 返回前2行结果，注意，当行数不够时，只能返回数据表中有限的行数
select * from goods limit 2, 2 返回从行2开始的2行

当然，我们也可以使用完全限制来进行数据检索：
select goods.id, goods.name from goods 输出goods表中所有记录
测试数据表内容：

mysql> select * from student;
+----+------+------+----------+
| id | name | age  | class_id |
+----+------+------+----------+
|  1 | Tom  |   23 |        1 |
|  2 | John |   22 |        1 |
|  3 | Red  |   23 |        2 |
|  4 | Fork |   24 |        3 |
+----+------+------+----------+
4 rows in set (0.04 sec)

mysql> select * from class;
+----+---------+-----------+
| id | name    | teacher   |
+----+---------+-----------+
|  1 | class 1 | teacher 1 |
|  2 | class 2 | teacher 2 |
|  4 | class 4 | teacher 4 |
+----+---------+-----------+
3 rows in set (0.00 sec)

排序检索数据

排序检索数据主要使用select语句的order by子句，根据需要排序检索出的数据，select语句默认返回结果是没有特定顺序的，在排序检索数据时也可以指定排序的方向，比如升序或者降序等，order by子句默认为升序排列。

select * from student order by name 升序排列
select * from student order by name desc 降序排列

有时，我们需要对多个列排序怎么办呢？这时可以使用如下sql语句来执行，select * from student order by id desc, name，注意，这里是对id进行降序排列，如果id相同时，对name进行升序排列。如果想对多个列进行降序排列，需要对每个列指定desc关键字。
select * from student order by id desc, name
使用order by和limit的组合，我们能够找到一个列中最高或者最低的值，比如这里还用student表做测试:

select * from student order by class_id desc limit 1;
+----+------+------+----------+
| id | name | age  | class_id |
+----+------+------+----------+
|  4 | Fork |   24 |        3 |
+----+------+------+----------+

select语句的order by子句对检索出的数据进行排序，这个字句必须出现在select语句中的最后一条子句。

过滤数据

select语句中，数据可以根据where子句指定的过滤条件进行过滤，where子句在表名(from子句)之后给出，比如，select id, name from student where id = 2，该语句只显示id为2记录的id和name。注意：如果同时使用where和order by子句，应该让order by子句在where之后，否则会产生错误。
where子句操作符如下：

=   等于
<> 不等于
!= 不等于
<  小于
>  大于
>= 大于等于
between  在指定的两个值之间(比如select * from student where id between 1 and 2)

我们在创建表时，可以指定其中的列是否可以不包含值，在一个列不包含值时，其值为空值null，select语句有一个特殊的where子句，用来检测具有null值的列，比如：select * from student where name is null 就把name是空值的记录给打印出来。
常用的select子句在过滤数据时使用的是单一的条件，为了进行更强的过滤控制，可以下多个where子句，这些子句有两种方式：以and子句和or子句的方式使用。

select * from student where id = 2 and age > 10 检索id为2并且age大于10的记录
select * from student where id = 3 or age >  15 检索id为3并且age大于15的记录

假如多个and和or语句放在一起，则优先处理and操作符，此时可以使用圆括号来改变其优先顺序。圆括号还可以指定in操作符的条件范围，范围中的每个条件都可以进行匹配。

select * from student where id in (2, 3) order by age
select * from student where id not in (2, 3) order by age

用通配符进行过滤

使用like操作符进行通配搜索，以便对数据进行复杂过滤。百分号(%)操作符 搜索中，%表示任何字符出现任意次数。

select * from student where name like '%o%' 查询name中包含o的数据项
select * from student where name like '_o' 下划线(_)通配符匹配单个字符而不是多个字符

通配符很有用，但这是有代价的，通配符的搜索处理一般比其他搜索花费时间长，这里有一些技巧：

• 不要过度使用通配符，如果其他操作符能达到同样的目的，就应该使用其他操作符。
• 在确实需要使用通配符时，除非绝对必要，否则不要把它们用在搜索模式的开始处，把通配符放在开始处，搜索起来是最慢的。
• 注意通配符位置，位置不对可能不会返回想要的结果。

正则表达式

• 正则表达式的作用是匹配文本，将一个模式(正则表达式)与一个文本串进行比较。mysql的where子句对正则表达式提供了初步支持，允许你指定正则表达式 ，过滤select检索出的数据。
• select * from student where name regexp 'o' order by age desc 查询名字中包含o的数据项，并以age大小倒序排列
• select * from student where name regexp 'o|r' 查询名字中包含o或者r的数据项
  如果记录匹配正则表达式，则就会被检索出来，使用下面正则表达式重复元字符可以进行更强的控制。
• • 匹配0个或多个
• • 匹配1个或多个(等于{1,})
• ? 匹配0个或1个(等于{0,1})
• {n} 指定书目的匹配
• {n,} 不少于指定数据的匹配
• {n,m} 匹配指定数据的范围(m不超过255)
• ^ 文本的开始
• $ 文本的结束
• [[:<:]] 词的开始
• [[:>:]] 词的结束
  注意：regexp和like作用类似，regexp和like不同之处在于，like匹配整个串而regexp匹配子串，利用定位符，通过'^'开始每个表达式，用'$'结束每个表达式，可以使regexp的作用和like一样。

数据处理

拼接将值连接到一起构成单个值，在mysql的select语句中，可使用concat()函数来拼接两个列。

mysql> select concat(id, '_', name) from student;
+-----------------------+
| concat(id, '_', name) |
+-----------------------+
| 1_Tom                 |
| 2_John                |
| 3_Red                 |
| 4_Fork                |
+-----------------------+

常用的文本处理函数：

left()	返回串左边的字
length() 返回串的长度
locate() 找出串的一个子串
lower() 将串转换为小写
ltrim() 去掉串左边的空格
right() 返回串右边的字符
rtrim() 返回串右边的字符
soundex()	 返回串的soundex值
substring() 返回子串的字符
upper() 将串转换为大写
select id, upper(name) from student 将name变成大写

经常需要汇总数据而不是把它们检索出来，为此mysql提供了专门的函数，以便分析和报表生成。常用的例子有：确定表中行数、获取表中行组的和、找出表列的最大值(最小值或平均值)。聚集函数运行在行组上，计算和放回单个值得函数，mysql提供了5个聚集函数，这些函数一般比自己的客户端应用程序中计算要快得多。

AVG()   返回某列的平均值
COUNT() 返回某列的行数
MAX()   返回某列的最大值
MIN()   返回某列的最小值
SUM()   返回某列之和
select count(*) as nums from student 统计表中记录个数
select count(distinct age) as nums from student 统计表中不同age有多少个

子查询

什么是子查询呢？子查询就是嵌套在其他查询中的查询， 在where子句中使用子查询，应该保证select语句具有和where子句中相同数目的列，通常，子查询将返回单个列并且与单个列匹配。

mysql> select * from student where id in (select id from student where age > 22);
+----+------+------+----------+
| id | name | age  | class_id |
+----+------+------+----------+
|  1 | Tom  |   23 |        1 |
|  3 | Red  |   23 |        2 |
|  4 | Fork |   24 |        3 |
+----+------+------+----------+

联结

外键为某一个表中的一列，它包含另一个表的主键值，定义了两个表的关系。如果数据存储在多个表中，使用联结可用单条select语句检索出需要的数据。应该保证所有的连联结都有where子句，否则mysql将返回比想要的多的多的数据，因为此时检索出的行数目是第一个表行数乘以第二个表行数。
联结是SQL中最重要最强大的特性。关系表的设计就是要保证把信息分解成多个表，一类数据一个表。各表通过某些常用的值(即关系设计中的关系relational)互相关联。联结的创建非常简单，规定要联结的所有表以及它们如何关联即可：

mysql> select s.name, s.age, c.id from student s, class c where s.class_id = c.id;
+------+------+----+
| name | age  | id |
+------+------+----+
| Tom  |   23 |  1 |
| John |   22 |  1 |
| Red  |   23 |  2 |
+------+------+----+

使用WHERE子句作为过滤条件，它只包含匹配给定给定条件的行。没有WHERE子句，第一个表中的每个行将于第二个表中的每个行配对，而不管它们逻辑上是否可以配对在一起。SQL对一条SELECT语句中可以联结的表的数目没有限制。基于两个表之间相等测试的联接称为内部联接。其实，对于这种联结可以使用稍微不同的语法来明确指定联结的类型，下面的select语句返回与前面例子完全相同的数据。
select s.name, s.age, c.id from student s inner join class c where s.class_id = c.id
除了内部联接之外还有外部联结，外部联结包括左外联结、右外联结、完整外部联结：
left join子句指定左表的所有行，而不仅仅是联结所匹配的行，如果左表某个行在右表中没有匹配行，则在关联的结果集中对应值为空值。
right join子句指定右表的所有行，而不仅仅是联结所匹配的行，如果右表某个行在左表中没有匹配行，则在关联的结果集中对应值为空值。
full join完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的对应列是空值。

mysql> select s.name, s.age, c.id from student s left join class c on s.class_id = c.id;
+------+------+------+
| name | age  | id   |
+------+------+------+
| Tom  |   23 |    1 |
| John |   22 |    1 |
| Red  |   23 |    2 |
| Fork |   24 | NULL |
+------+------+------+
4 rows in set (0.01 sec)

mysql> select s.name, s.age, c.id from student s right join class c on s.class_id = c.id;
+------+------+----+
| name | age  | id |
+------+------+----+
| Tom  |   23 |  1 |
| John |   22 |  1 |
| Red  |   23 |  2 |
| NULL | NULL |  4 |
+------+------+----+
4 rows in set (0.00 sec)

mysql> select s.name, s.age, c.name from student s right join class c on s.class_id = c.id;
+------+------+---------+
| name | age  | name    |
+------+------+---------+
| Tom  |   23 | class 1 |
| John |   22 | class 1 |
| Red  |   23 | class 2 |
| NULL | NULL | class 4 |
+------+------+---------+
4 rows in set (0.00 sec)

组合查询

利用union操作符和组合多条SQL查询，让给出的多条select语句结果组合成单个结果集。注意，union必须有2条或者2条以上的select语句组成，union中每个查询必须包含相同的列、表达式或聚集函数（不过每个列不需要以相同的次序列出）。union会自动去除重复的行，这个是默认的行为，如果不想这样，使用union all而不是使用union。
select * from student where id > 1 union select * from student where age > 23

插入数据

插入语句insert一般会有产生输出，一般只会打印影响的行数。insert时如果不提供列名，则必须给每个表列提供一个值，如果提供列名，则必须对每个列出的列给出一个值，否则报错。

insert student values(5, 'luoxn28', 23, 1)
insert student (name, age, class_id) value('luoxn28', 23, 1)
insert student (name, age, class_id) values('luoxn28', 23, 1), ('luo', 23, 2) 插入多行

更新和删除数据

分别使用update语句和delete语句来进行更新和删除数据操作。

update student set age = 24 where id = 5
delete from student where id = 5

注意，这里有一些使用update和delete语句的一些注意事项：

• 除非确实打算更新和删除每一行，否则绝对不要使用不带where子句的update或delete语句。
• 保证每个表都有主键，尽可能像where子句那样使用它。
• 在对update或delete语句使用where子句前，应该先用select进行测试，保证过滤的是正确的记录，以防止编写的where子句不正确。
• mysql没有撤销功能，应小心使用update或delete功能。

视图

视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询。视图仅仅是用来查看存储在别处数据的一种设施，视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。视图提供了一种MySQL的SELECT语句层次的封装，可用来简化数据处理以及重新格式化基础数据或保护基础数据。
使用视图的常见应用：

• 重用SQL语句
• 简化复杂的SQL操作，在编写查询后，可以方便地重用它而不必知道具体的查询细节
• 使用表的组成部分而不是整个表
• 保护数据，授予客户表的特定部分访问权限而不是全部
• 更改数据格式和表示，视图可返回与底层表的表示和格式不同的数据
  使用视图简化复杂的联结，视图使用create view语句来创建，使用show create view viewname来查看创建视图的语句；用drop删除视图，语法为drop view viewname。

mysql> create view students as select * from student;
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+---------------+
| Tables_in_zzz |
+---------------+
| class         |
| student       |
| students      |
+---------------+
3 rows in set (0.00 sec)

mysql> select * from students;
+----+------+------+----------+
| id | name | age  | class_id |
+----+------+------+----------+
|  1 | Tom  |   23 |        1 |
|  2 | John |   22 |        1 |
|  3 | Red  |   23 |        2 |
|  4 | Fork |   24 |        3 |
+----+------+------+----------+
4 rows in set (0.00 sec)

通常，视图是可更新的（可以对它们使用insert、update和delete），更新一个视图将更新其基表，因为视图本身是没有任何数据的。但是，并非所有的视图是可更新的，如果mysql不能正确的确定被更新的基数据，则不能被更新，即如果有以下操作，视图不允许更新：分组、联结、子查询、并、聚集函数、distinct等。

[golny]

very good conclusion, learned...