分页

• 方式1：
  select * from table order by id limit m, n;
  该语句的意思为，查询m+n条记录，去掉前m条，返回后n条记录。无疑该查询能够实现分页功能
  但是如果m的值越大，查询的性能会越低（越后面的页数，查询性能越低），因为MySQL同样需要扫描过m+n条记录。
• 方式2：
  select * from table where id > #max_id# order by id limit n;
  该查询每次会返回n条记录，却无需像方式1扫描过m条记录，在大数据量的分页情况下，性能可以明显好于方式1
  但该分页查询必须要每次查询时拿到上一次查询（上一页）的一个最值id。
  该查询的问题就在于，我们有时无法拿到上一次查询（上一页）的最值id
  比如当前在第3 页，需要查询第5页的数据，该方法便爱莫能助
• 方式3：
  为了避免能够实现方式2不能实现的查询，就同样需要使用到limit m, n子句
  为了性能，就需要将m的值尽力的小，比如当前在第3页，需要查询第5页，每页10条数据，当前第3页的最大id为#max_id#：
  select * from table where id > #max_id# order by id limit 20, 10;
  其实该查询方式是部分解决了方式2的问题，但如果当前在第2页，需要查询第100页或1000页，性能仍然会较差。
• 方式4：
  select * from table as a inner join (select id from table order by id limit m, n) as b on a.id = b.id order by a.id;
  该查询同方式1一样，m的值可能很大，但由于内部的子查询只扫描了字段id，而不是整张表，所以性能要强于方式1查询，并且该查询能够解决方式2和方式3不能解决的问题。
• 方式5：
  select * from table where id > (select id from table order by id limit m, 1) limit n;
  该查询方式同方式4，同样通过子查询扫描字段id，效果同方式4。
  至于性能的话，方式5的性能会略好于方式4，因为方式5不需要在进行表的关联，而是一个简单的比较。

where

如需有条件地从表中选取数据，可将 where 子句添加到select语句中。
SELECT field1, field2,...fieldN FROM table_name1, table_name2... [WHERE condition1 [AND [OR]] condition2.....

• 查询语句中你可以使用一个或者多个表，表之间使用逗号, 分割，并使用where设定查询条件
• 可以在 where 子句中指定任何条件
• 可以使用 and 或者 or 指定一个或多个条件
• where 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。

• where 类似于 if 条件，根据 MySQL 表中的字段值来读取指定的数据

  
  
  

  操作符列表，可用于 WHERE 子句
  
  

  想读取指定的数据，WHERE是非常有用的。
  使用主键来作为 WHERE 子句的条件查询是非常快速的
  如果给定的条件在表中没有任何匹配的记录，那么查询不会返回任何数据

• MySQL 的 where字符串比较是不区分大小写的。 可以使用 BINARY 关键字来设定 where字符串比较区分大小写

  
  
  

update

修改或更新 MySQL 中的数据
UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]

update 表 set 列=新值 where 更新条件;

• 可以同时更新若干个字段
• 可以在 where 子句中指定任何条件
  当你需要更新数据表中指定行的数据时 WHERE 子句是非常有用的
• 可以在一个单独表中同时更新数据

UPDATE替换某个字段中的某个字符
UPDATE table_name SET field=REPLACE(field, 'old-string', 'new-string') [WHERE Clause]

limit

实例：每行5页，返回第2页的数据

• 利用 limit 和 offset 。limit 后数代表返回几条记录，offset后数代表从第几条记录开始返回（第一条记录序号为0），即偏移记录条数
  SELECT * FROM employees LIMIT 5 OFFSET 5
• 在 limit x,y
  • y:返回几条记录
  • x:从第几条记录开始返回（第一条记录序号为0）
    SELECT * FROM employees LIMIT 5,5

limit y,x == limit x offset y

字符串截取

MySQL 字符串截取函数 ：left(), right(), substring(), substring_index()。还有 mid(), substr()。其中，mid(), substr() 等价于 substring() 函数，substring() 的功能非常强大和灵活。

• left(str, length)

  
  
  

• right(str, length)

  
  
  

• substring(字符串，起始位置); substring(字符串，起始位置，长度)

  
  
  

  从字符串的第 4 个字符位置开始取，直到结束
  
  
  

  从字符串的第 4 个字符位置开始取，只取 2 个字符
  
  
  

  从字符串的第 4 个字符位置（倒数）开始取，直到结束
  
  
  

  从字符串的第 4 个字符位置（倒数）开始取，只取 2 个字符

order by排序

设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。
SELECT field1, field2,...fieldN table_name1, table_name2... ORDER BY field1, [field2...] [ASC [DESC]]

• 可用任何字段作为排序条件
• 可设定任意个字段来排序
• 可用 asc 或 desc 设置查询结果按升/降序
  默认升序排列
• 可添加 where ... like 设置条件

ength和char_length

• length
  获取字符串长度的内置函数方法，utf8编码下一个汉字是算三个字符,一个数字或字母算一个字符
  其他编码下,一个汉字算两个字符, 一个数字或字母算一个字符。
• char_length
  在任何编码下，不管汉字还是数字或者是字母都算是一个字符

replace函数

根据主键确定被替换的是哪一条记录

• replace(object,search,replace)
  把object中出现search的全部替换为replace ,返回替换后的字符串
  select replace('www.jb51.net','w','Ww')--->WwWwWw.jb51.net
  把表table中的name字段中的aa替换为bb
  update table set name=replace(name,'aa','bb')
• replace into
  replace into table (id,name) values('1','aa'),('2','bb')
  向表中插入两条记录。如果主键id为1或2不存在
  就相当于
  insert into table (id,name) values('1','aa'),('2','bb')
  如果存在相同的值则不会插入数据

1 键

主 键：

数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。

联合主键

顾名思义就是多个主键联合形成一个主键组合(主键原则上是唯一的，别被唯一值所困扰。)

       联合主键的意义：用2个字段(或者多个字段,后面具体都是用2个字段组合)来确定一条记录，说明，这2个字段都不是唯一的，2个字段可以分别重复，这么设置的好处，可以很直观的看到某个重复字段的记录条数。

主键A跟主键B组成联合主键
主键A跟主键B的数据可以完全相同，联合就在于主键A跟主键B形成的联合主键是唯一的。

复合主键

表的主键含有一个以上的字段组成,不使用无业务含义的自增id作为主键
将多个字段设置为主键，形成复合主键，这多个字段联合标识唯一性，其中，某几个主键字段值出现重复是没有问题的，只要不是有多条记录的所有主键值完全一样，就不算重复

超键：

在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。

候选键：

是最小超键，即没有冗余元素的超键。

外键：

在一个表中存在的另一个表的主键称此表的外键。

2 数据库事务的ACID

数据库事务transanction正确执行的四个基本要素。ACID,原子性(Atomicity)、一致性(Correspondence)、隔离性(Isolation)、持久性(Durability)。

原子性:整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性:在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
隔离性:隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。
持久性:在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

3 视图

视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询；不包含任何列或数据。
使用视图可以简化复杂的sql操作，隐藏具体的细节，保护数据；视图创建后，可以使用与表相同的方式利用它们。
视图不能被索引，也不能有关联的触发器或默认值，如果视图本身内有order by 则对视图再次order by将被覆盖。
创建视图：create view XXX as XXXXXXXXXXXXXX;
对于某些视图比如未使用联结子查询分组聚集函数Distinct Union等，是可以对其更新的，对视图的更新将对基表进行更新；但是视图主要用于简化检索，保护数据，并不用于更新，而且大部分视图都不可以更新。

4 删除

• drop直接删掉表
• truncate删除表中数据，再插入时自增长id又从1开始
• delete删除表中数据，可以加where字句

（1）

• DELETE
  每次从表中删除一行，并同时将该行的删除操作作为事务记录在日志中保存,以便回滚
• TRUNCATE TABLE
  一次性地从表中删除所有的数据,并不把单独的删除操作记录记入日志保存，删除行是不能恢复的,在删除的过程中不会激活与表有关的删除触发器。执行速度快。

（2） ##表和索引所占空间。

• 表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小
• 而DELETE操作不会减少表或索引所占用的空间
• drop语句将表所占用的空间全释放掉

（3） 一般而言，drop > truncate > delete

（4） 应用范围

• TRUNCATE 只能对TABLE
• DELETE可以是table和view

（5）

• TRUNCATE 和DELETE只删除数据
• 而DROP则删除整个表（结构和数据）

（6）

• truncate与不带where的delete :只删除数据,而不删除表的结构（定义）
• drop语句将删除表的结构被依赖的约束（constrain),触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。

（7）

• delete语句为DML（data maintain Language),这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 tigger,执行的时候将被触发
• truncate、drop是DLL（data define language),操作立即生效，原数据不放到 rollback segment中，不能回滚

（8）

• 在没有备份情况下,谨慎使用 drop 与 truncate
• 要删除部分数据行采用delete且注意结合where来约束影响范围。回滚段要足够大。
• 要删除表用drop;
• 若想保留表而将表中数据删除
• 如果与事务无关，用truncate即可实现。
• 如果和事务有关，或老是想触发trigger,还是用delete

（9） Truncate table 表名 速度快,而且效率高,因为:
truncate table 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

（10）

• TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。
• 如果想保留标识计数值，请改用 DELETE。
• 如果要删除表定义及其数据，请使用 DROP TABLE 语句。

（11） 对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器

连接

在真正的应用中经常需要从多个数据表中读取数据。
如何使用 MySQL 的 JOIN 在两个或多个表中查询数据呢
可以在 SELECT, UPDATE 和 DELETE 语句中使用 MySQL 的 JOIN 来联合多表查询。

JOIN 按照功能大致分为如下三类：

• INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
• LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
• RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

在命令提示符中使用 INNER JOIN

我们在RUNOOB数据库中有两张表 tcount_tbl 和 runoob_tbl。两张数据表数据如下：

实例

tcount_tbl表

runoob_tbl表

使用INNER JOIN(也可以省略 INNER)来连接以上两张表来读取runoob_tbl表中所有runoob_author字段在tcount_tbl表对应的runoob_count字段值：

INNER JOIN

以上 SQL 语句等价于：

WHERE 子句

image

LEFT JOIN

left join 与 join 有所不同。 LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。

实例

尝试以下实例，以 runoob_tbl 为左表，tcount_tbl 为右表，理解 MySQL LEFT JOIN 的应用：

LEFT JOIN

以上实例中使用了 LEFT JOIN，该语句会读取左边的数据表 runoob_tbl 的所有选取的字段数据，即便在右侧表 tcount_tbl中 没有对应的 runoob_author 字段值。

MySQL RIGHT JOIN

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

实例

尝试以下实例，以 runoob_tbl 为左表，tcount_tbl 为右表，理解MySQL RIGHT JOIN的应用：

RIGHT JOIN

mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a RIGHT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author; +-------------+-----------------+----------------+ | a.runoob_id | a.runoob_author | b.runoob_count | +-------------+-----------------+----------------+ | 1 | 菜鸟教程 | 10 | | 2 | 菜鸟教程 | 10 | | 3 | RUNOOB.COM | 20 | | 4 | RUNOOB.COM | 20 | | NULL | NULL | 22 | +-------------+-----------------+----------------+ 5 rows in set (0.01 sec)

以上实例中使用了 RIGHT JOIN，该语句会读取右边的数据表 tcount_tbl 的所有选取的字段数据，即便在左侧表 runoob_tbl 中没有对应的runoob_author 字段值。

image