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...