MySQL基础命令总结 #2

luoxn28 · 2016-11-10T16:30:56Z

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等。

The text was updated successfully, but these errors were encountered:

golny · 2018-07-29T09:30:10Z

very good conclusion, learned...

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

MySQL基础命令总结 #2

MySQL基础命令总结 #2

luoxn28 commented Nov 10, 2016

golny commented Jul 29, 2018

MySQL基础命令总结 #2

MySQL基础命令总结 #2

Comments

luoxn28 commented Nov 10, 2016

数据库基础概念

MySQL逻辑架构

事务

事务的四种隔离级别

存储引擎

InnoDB

MyISAM

MEMORY存储引擎

使用MySQL

检索数据

排序检索数据

过滤数据

用通配符进行过滤

正则表达式

数据处理

子查询

联结

组合查询

插入数据

更新和删除数据

视图

golny commented Jul 29, 2018