数据库知识归纳

数据库知识归纳

---

数据库信息

• show databases 返回可用数据库的列表
• show tables 返回选择数据库的可用表的列表
• show columns tableName 给定一个表名，返回表每个字段的字段名，数据类型，是否允许为null，键信息，默认值以其他信息（auto_increment）
• show status 用于显示广泛的服务器状态信息
• show create database/show create table 用来显示创建特定数据库和表的mysql语句

检索数据

• select col_name from table_name 返回对应表的对应列的所有行，没有过滤和排序
• select col_name, col_name2,col_name3 from table_name 返回多列
• select * from table_name 返回所有列
• select distince col_name from table_name 只返回指定列的不同值
• select col_name from table_name limit start_index , offset 返回匹配行的的特定行。

排序检索数据

• select col_name from table_name order by col_name 使用order by子句对检索出来的数据根据 col_name进行排序

• select col_name ,col_name2 from table_name order by col_name ,col_name1 多列排序，当col_name相等的时候，再跟据col_name1排序。

• select col_name from table_name order by col_name desc,col_name2 [asc] 指定排序方向，默认为升序（asc），降序是（desc）

过滤数据

• select col_name from table_name where col_name <>[=][!=] 'col_name' 匹配检查

• select col_name from table_name where col_name between 'value1' and 'value2' 范围值检查匹配

• select col_name from table_name where col_name is NULL 空值匹配

• select col_name ,col_name1 from table_name where col_name = 'value1' and col_name='value2' 检索满足所有条件的行

• select col_name ,col_name1 from table_name where col_name = 'value1' or col_name != 'value2'
  检索满足匹配任意条件的行

  ps : 数据库优先处理and操作符，应该合理使用括号来明确分组运算符

• select col_name from table_name where col_name in ('value1','value1') 指定检索范围，与or功能相同，不过可以
  包含其他select字句

• select col_name from table_name where col_name not in ('value1','value1') 否定之后的检索条件

通配符过滤

• select col_name from table_name where col_name like '%value1%' 通配符必须使用like, %匹配任意字符的任意次数
  ,但是不会匹配null,而且尾空格会影响通配符匹配

• select col_name from table_name where col_name like '_value' 匹配单个字符，不多不少，不应该放在开始处，影响性能。

正则表达式过滤

• select col_name from table_name regexp '[123]value?' 将regexp后的作为正则表达式处理。

  ps: like是匹配整个列的值，如果value在列中出现，就不会被匹配；正则表达式的话，即使在列中出现，也会被匹配
  如果要匹配特殊字符要使用\为前导来转义

  ^ 在集合中否定该集合，否则就用来指字串的开始。
  $ 字串的结束

创建计算字段

• select concat(col_name,'(',col_name2,')') from table_name; mysql使用concat来拼接字符串

  ps :
  RTrim(str) 去字符串的右边空白 ，LTrim（str）去掉字符串的左边空白 ，Trim(str)去掉字符串两边空白
  old_col_name as new_col_name 使用别名 as
  还可以使用/*-+基本运算符

• 常见的文本处理函数

  Left（） 返回字串左边的字符
  Length（）返回字串的长度
  Locate（） 找出串的字串
  Lower（） 转换字串为小写
  Right（） 返回字串右边的字符
  Soundex() 返回串的soudex值

• 常见的时间处理函数

  Date() 返回时间的日期部分
  CurDate() 返回当前日期
  CurTime() 返回当前时间
  Now() 返回当前的日期和时间

• 常见的数值处理函数

  Abs() 返回数值的绝对值
  Cos() 返回一个角度的余弦值
  Sqrt() 返回一个数的平方根

汇总数据

• Avg() 返回某列的平均值
• Count() 返回某列的行数
• Max() 返回某列的最大值，如果在用于文本数据时，如果数据按相应的列排列，则max()返回最后一行
• Min() 返回某列的最小值，如果在用于文本数据时，则返回最前面的行
• Sum() 返回某列的值和
• 聚集不同值：默认为all，如果想要包含不同的值，指定distinct参数。
  eg : select AVG(distinct col_name) from table_name

分组数据

• select count(*), col_name from table_name group by col_name， group by指明mysql按col_name排序和分组

  • group by 语句可以嵌套任意数目的列，能够对分组进行嵌套，为数据分组提供更细致的控制。
  • group by 语句嵌套了分组，数据会在最后规定的分组汇总
  • group by 语句要求每一列都是检索列或者有效表达式
  • 除了聚集计算语句，select 语句中每一列都要在group by字句中出现
  • 如果分组列中有null值，则将null作为一个分组返回，如果列中有多个null，则将他们分为一组

• select ,count() from from col_name group by col_name having count(*) >= number 使用having过滤分组，使用与where类似，不过where是过滤行，having是过滤分组，where是分组之前进行过滤，
  having是分组之后进行过滤。group by虽然是分组顺序输出的，但有时候，我们可能需要不同于分组的顺序排序
  输出。我们应该使用order by字句来明确输出。

子查询

• select col_name from table_name1 where col_name1 in (select col_name1 from table_name2) 在where字句中使用
  自查询，select语句应该具有跟where子句中相同数目的列，子查询总是从内往外处理的。

• select table1_col_name,
  table1_col_name1,
  (select count(*) from table2_name where table2_name.col_name1 =table1_name.col_name1)
  from table1_name where col_name1 = 'value'
  相关子查询，当列名可能会有多义的时候，就应该使用完全限定名。

联结表

• select col_name, col_name1,col_name2 from table_name1,table_name2 where table_name1.col_name = table_name2.col_name
  联接并不是物理实体，它在实际的数据库表中并不存在，在使用联接的时候，必须要保证关系列的输入数据必须合法，
  跟业务相关，否则对应关系列的值是不可访问的。
  联接其实是在运行时发生的，它将table_name1中每一行与table_name2中的每一行进行配对，where子句作为过滤条件，只返回给定条件的行，如果没有where字句，返回笛卡尔积，返回的数目为table_name1的行数乘以table_name2的行数的乘积。

• select col_name ,col_name1,col_name2 from table_name1 inner join table_name2 on table_name1.col_name = table_name2.col_name
  内部联接跟等值联接类似，只不过联接条件一个是where子句和一个on子句。

• select col_name ,col_name1 ,col_name2 from table_name1 left[right] join table_name2 on table_name.col_name
  外部关联可以包括没有关联行的行，在使用外部关联的时候，必须指定是right或者left关联来指定要包括所有行的表
  （right join是要outer join右边的表，而left join是要关联左边的表），在使用联接的时候，应该使用正确的联接条件
  以免出现不正确的条件和笛卡尔积

组合查询

• select col_name from table_name where col_name = 'value' union select col_name1 from table_name2 where col_name = 'value1'
  union可以把多条select语句的结果组合成一个结果集，只需要在select子句之间添加关键字union，union默认会从结果集中去除重复的行，如果想要返回全部的行，可以去使用union all。union all 不会取消重复的行。
  如果想要对结果集排序的话，在使用union组合查询时，只能使用一条查询子句，而且必须是最后一条select子句，union组合查询不允许对结果集，使用不同的排序方式去排序每一个部分。

全文本搜索

要使用全文本搜索的话，需要数据库引擎支持，一般常用的数据库引擎是MyISAM和Innodb,MyISAM支持全文检索；
在innodb5.6.4版本之后，innodb也支持全文检索。
要全文检索，需要索引被搜索的列 ，一般是在创建表的时候使用FULLTEXT来指定要全文检索的列，当定义索引之后，
mysql会自动维护索引，在增删改行的时候，索引就会自动更新。如果在导入数据的时候，不应该创建全文搜索索引
，因为在导入数据的时候，索引会不停地更新。应该是在导入完数据之后，再对表进行修改，定义全文检索索引。

• select col_name from table_name where Match(col_name) Against(value)

  col_name是被索引的列，Match是指示要对指定的列进行全文检索，Against是指定词（value）来作为搜索文本，如果被FULLTEXT的是多列，使用Match的时候也是指定全部被FUllTEXT的列，且顺序相同。

• select col_name from table_name where Match(col_name) Against(value WITH QUERY EXPANSION)
  在使用查询扩展，mysql会对数据和索引进行两次扫描来完成搜索

  • 第一个扫描的时候，进行普通的全文检索，找出与搜索条件匹配的所有行。
  • mysql会检查匹配的词，然后选择所有有用的词。
  • mysql再根据搜索条件和有用的词再一次进行全文检索。

• select col_name from table_name where Match(col_name) Against('value -value2*' IN BOOLEAN MODE)
  布尔方式全文检索，根据布尔操作符，来返回指定的行

  + 包含 词必须存在
  - 排除 词必须不出现
  > 包含 而且增加等级值
  < 包含 减少等级值
  () 把词组成子表达式
  ~ 取消一个值的排序值
  * 词尾的通配符
  "" 定义一个短语

• 注意点

  • 在索引全文数据时，在那些具有三个或者三个以下的短词会被忽略且从索引中排除。

  • mysql内建了一个非用词列表，在索引全文数据的时候会被忽略

  • 如果一个词出现在50%以上的行中，会被当成非用词忽略，50%准则不用于iN boolean Mode中

  • 如果表中的行数少于3行，则全文检索不返回结果

  • 忽略词中的单引号

  • 不具备词分割符的语言不能正确返回全文检索结果

插入数据

• insert into table_name (col_name,col_name1,col_name2,col_name3) values(col_value,col1_value,col2_value,col3_value);
  插入数据的时候最好指定列的名称来插入数据，如果不指定列的话，插入数据依赖创建表时的结构和每一列必须赋予一个值，如果某列上没有值，应该使用null来填充。如果表结构发生了改变，可能会发生问题。
  如果给出了列名，就需要给列出的列名提供值。否则就会报错。

• insert into table_name (col_name,col_name1,col_name2) select col_name,col_name1,col_name2 from table_name1;
  select字句将从table_name1中检索出要插入table_name的行，插入的数据依赖与table_name1中的数据，插入的数据并不依赖
  select字句的返回值，它使用的是返回列的次序。

更新和删除

• update table_name set col_name = 'value' where col_name = 'value' 根据where字句检索出来的行将新值赋予要更新的行。如果在更新多行的时候，在更新某些行出现错误的时候，整个update操作都会被取消，已经更新的行将会恢复原来的值。如果想发生错误也可以继续更新，可以使用ignore关键字。

• update ignore table_name set col_name = 'value' ,col_name1 = 'value1' where col_name = 'value'

• delete from table_name where col_name = 'value' mysql会根据where子句去删除指定的行，delete只能删除整行，不能删除整列，如果想要删除整列的话，应该使用update。如果想要整个表的数据，应该使用truncate table_name，truncate会drop掉整个表，再去重新创建一个表，而不是删除数据

创建和操纵

• create table table_name{
  col_id int not null auto_increment,
  col_name varchar(60) not null,
  col_name char(10) not null,
  primary key(col_id)
  }engine =Innodb

create table关键字加表名来创建新表，表定义包括在圆括号中，每一列以列名，数据类型组成，列与列之间通过逗号分割。可以使用not null与null来指定列是否允许缺值，如果是not null的时候，在插入和更新列的时候，该列必须要有值。默认为null。在表中主键必须唯一，如果主键为单列的话，那么主键值必须唯一，如果是多列的话，它们的组合值必须唯一。主键不允许为null.每个表只能有一个auto_increment列，而且这个列必须要被索引，它指明该列每增加一行的时候自动增量。每执行一个insert操作时，mysql自动对该列增量，给该列赋予下一个可用的值。如果在插入行的时候没有给出值，可以通过default来指定该列的默认值。默认值不允许为函数。一般情况使用默认值而不使用null值。

• alter table table_name add col_name char(20) 使用alter table 更改表结构，该表必须存在，否则就会报错，增加列的时候需要指明数据类型。

• alter table table_name add constraint symbol foreign key (col_name) references table_name1(col_name1)
  使用alter table修改表结构的时候一般需要手动删除过程

  • 用新的列布局来创建一张新表
  • 使用insert into语句将旧表的数据复制到新表。
  • 校验新表的数据
  • 重命名旧表（如果确定，可以删除旧表）
  • 用旧表原来的名字来命名新表
  • 重新创建触发器，存储过程，索引和外键。

• drop table table_name 删除整个表

• rename table old_table_name to new_table_name 重命名表名

视图

• create view view_name as select col_name ,col_name1 from table_name1,table_name1 where table_name.col_name1 = table_name1.col_name1
  使用create view加视图名来创建视图，视图是只包含运行时动态检索数据的查询，是一个虚拟的表。视图可以对sql进行重用跟简化sql操作。视图必须是唯一命名的，但不可以被索引，可以使用order by，但会被select语句中order by子句覆盖。
  使用视图可以对检索的数据进行重新格式化，也可以利用select子句来过滤不需要的数据，也可以利用计算字段来简化数据处理

视图可以看成是对select子句的一种封装，简化数据的处理和格式化和保护基础数据。
一般来说，视图是可以进行更新的，是对基表的更新。但是如果视图包含以下操作的话，则不能进行更新（包括删除和插入）

• 分组，group by和having
• 联结 inner join 、outer join
• 子查询
• 并 union union all
• 聚集函数 count() min() max()
• distinct
• 计算字段

存储过程

• create procedure procedure_name(
  in var_name1 int,
  out var_name2 varchar(60)
  )
  begin
  select count(*) into var_name2 from table_name;
  select max(col_name) from table_name1 where col_name = var_name1;
  end

  使用create procedure 加存储过程名来创建存储过程。使用begin跟end来限制存储过程体。mysql在执行这段代码的时候
  没有返回数据。因为并没有调用存储过程.mysql使用in来传递参数给存储过程和out从存储过程中流出。通过in和out来将检索的数据，保存到相应变量中（通过指定into）。

• drop procedure procedure_name 删除存储过程

• call procedure_name(value，@val_name2) 调用存储过程，将value的值流入var_name1,执行存储过程，将检索的值流出到val_name2。

• select @val_name2 通过select 加@变量名获取变量的值。

• show create procedure procedure_name 使用show create procedure语句可以获取创建存储过程的语句信息

• show procedure status like 'value' 使用show procedure status获取存储过程的信息，使用like来过滤信息

触发器

• create trigger trigger_name AFTER[BEFORE] INSERT[UPDATE,DELETE] ON table_name FOR EACH ROW select 'message'

  • 使用create trigger加触发器名来创建触发器，触发器是在某个表在执行insert,update,delete语句的时候自动执行的一条mysql语句根据before或者after来判断执行语句的时期。可以指定for each row来指明每执行insert、update、delete一行就会执行一次指定的mysql语句（或者在begin和end之间的多条语句）。
    每一个表每个事件只能允许指定一个触发器，也就是说一个表最多也就能有六个触发器。
    一个触发器不能与多个表或者多个事件关联。
  • 如果before触发器执行失败的话，mysql不会执行请求的mysql语句，因此如果before触发器或者mysql语句失败，after触发器也不会执行。
  • 在insert触发器中，可以引用一个new的虚拟表，来访问被插入的行，在before insert触发器中，new中的值可以进行更新。对于auto_increment的列，可以用after insert触发器来获取新的自动生成的值
  • 在delete触发器中，可以引用一个old的虚拟表，来访问被删除的行。
  • 在update触发器中，可以引用一个old的虚拟表，来访问将要被修改或者已经被修改的旧数据。也可以引用new的虚拟表来访问将要修改或者已经修改了的新数据。
  • old是只读，只能访问而不可以被修改。new的话可以用set来进行赋值，而不会触发触发器，导致循环调用。

• DROP TRIGGER if exists trigger_name 删除触发器

• show triggers from database_name 可以访问指定数据库的触发器。