MYSQL----基础函数

1.了解SQL

1.1数据库基础

• 数据库是保存有组织的数据的容器（通常是一个文件或一组文件）
  注意：区别数据库软件，数据库软件应成为DBMS（数据库管理系统）。
• 主键：一列（或一组列），其值能够唯一区分表中每个行。
• 注意：主键并不是必须的；
• 条件（MySql本身强制实施）：1.任一两行都不具有相应的主键值；
  2.每行都必须具有一个主键值（主键列不允许NULL值）

1.2什么是SQL

• Structured Query Language的缩写，结构化查询语言

2.MySql简介

2.1什么是MySql

• MySQL是一种DBMS，数据库软件。
• List item
• 客户机-服务器软件
  DBMS可分为两类：1.基于共享文件系统的DBMS；2.基于客户机-服务器的DBMS。（MYSQL基于后者）

3.使用MySql

3.1链接

• 登录到DBMS
• 打开数据库： use 数据库名
• 显示数据库的表：show databases
• 获得一个数据可内的表的列表：show tables
• 显示表的列表：show columns from 表名；快捷方式是 describe 表名

4.检索数据

4.1 SELECT语句

• 检索列：
  SELECT 列名1,列名2
  FROM 表名；
  （mysql必须用；结束，列名中间用，英文字符下）

• 检索所有列：
  SELECT *
  FROM 表名；

• 使用DISTINCT +列名，只保留不重复的列

• LIMIT m ，只显示签m行

• LIMIT m，n 返回从行m开始的n行。注意：第一行为行0

• 限定列名：表名.列名

5.排序检索数据

5.1 排序数据

• 对单个列排序：
  SELECT 列名
  FROM 表名
  ORDER BY 列名；
  数字从小到大排序，英文字符按字母顺序（A-Z）默认是升序

• 多个列排序
  SELECT 列名1，列名2，列名3
  FROM 表名
  ORDER BY 列名1，列名2；
  首先按列名1排序，若列名1有重复值时再按照列名2排；若列名1唯一，则不会按列名2排序。

• 降序排序：
  ORDER BY 列名 DESC；
  ORDER BY 列名1 DESC，列名2 ASC ；表名按照列名1降序，列名2升序。

• 最高
  ORDER BY 列名 DESC
  LIMIT1;

6.过滤数据

WHERE 子句

注意：如果将值与串类型的列进行比较，则需要限定引号。用来与数值列进行比较的值不用引号。

• 空值检查：
  IS NULL

• 大小比较，范围内：BETWEEN 最小值 AND 最大值

7.数据过滤

7.1 组合WHERE子句

• AND
  多个过滤条件用AND组合,结果需同时满足多个条件。

• OR
  多个条件是需要匹配任一一个。

• AND 和OR组合使用
  条件1 OR 条件2 AND 条件3 .计算次序为优先处理AND操作符，所以第一步是满足条件2和条件3，然后是满足条件1，容易出错。
  使用圆括号明确分组：
  优先级：圆括号>AND>OR

7.2 IN操作符

• IN 与OR有相同功能

7.3 NOT 操作符

• NOT IN ()
  e.g.：
  esex not in(0,1)等价于：
  esex != ANY(0,1)，也等价于
  esex != 0 AND esex != 1
  注意：当查询的列有NULL值时，结果可能是空集。
  not exists 避免
  

• NOT BETWEEN AND

• NOT EXISTS

8. 使用通配符进行过滤

8.1 LIKE操作符

• 百分号（%）通配符
  %表示任意字符出现任意次数
  ‘%abc’–以abc开头的词
  ‘%d%’–含有d的词
  ‘e%g’–以e开头g结尾的词
• 下划线（_）通配符
  和%用途一样，但值匹配单个字符而不是多个字符
  区别：%可以匹配0个和多个，_只能是一个
  建议通配符不要过度使用，搜索时间过长。

9. 用正则表达式进行搜索

正则表达式的作用是匹配文本，可以弥补使用通配符而过慢的缺点。

• 基本字符匹配
  where 列名 regexp ‘1000’–表示包含文本1000的所有行
  注意：LIKE 和REGEXP的区别：
  比如：LIKE’1000’， LIKE 匹配整个列。如果被匹配的文本在列值中出现，LIKE将不会找到它，相应的行不被返回，除非使用通配符。
  REGEXP’1000’,REGEXP在列值内进行匹配，如果被配的文本在列值中出现，REGEXP将会找到它,相应的行将被返回。
  简单来说就是，LIKE 匹配整个串而REGEXP匹配子串。

• 区分大小写

• 进行OR匹配
  搜索两个串之一使用|隔开，

• 匹配几个字符之一
  【123】ton 匹配1 ton 2 ton 3 ton
  【】是另一种形式的OR语句，是【1|2|3】 ton

• 否定字符
  ^ 【^123】–匹配除这些字符外的任何东西

• 匹配范围
  【0-9】–表示0-9之内的所有数字

• 查找特殊符号
  前加//（转义）

• 元字符
  \f 换页；
  \n 换行；
  \r 回车；
  \t 制表；
  \v 纵向制表；

• 匹配多个实例
  |元字符| 说明 |
  |* |0个或多个匹配|
  | + |1个或多个匹配（等于{1，}） |
  |？ |0个或1个匹配（等于{0，1}） |
  |{n} |指定数目的匹配|
  | {n，m} | 匹配数目的范围（m不超过255） |
  例：REGEXP’\\([0-9] sticks?\\)’
  匹配连在一起的4位数字
  REGEXP ‘【【：digit：】】{4}’
  等同于REGEXP ‘【0-9】【0-9】【0-9】【0-9】’

• 定位符
  元字符 说明
  ^ 文本的开始
  $ 文本的结尾
  【【：<：】】词的开始
  【【：>：】】词的结尾

10.创建计算字符

10.1 计算字段

• 拼接字段
  concat()函数
  concat（连接1，连接2）中间用，隔开，结果为连接1连接2
  RTrim（字段）删除数据右侧对于的空格
  LTrim（字段） 去掉串左边的空格
  Trim（去掉串左右两边的空格）
  计算完起别名生成新的列

10.2 执行算数计算

加+ ，减-，乘*，除/

11.使用数据处理函数

注：函数不具有可移植性，SQL实现之间有差异

11.1 使用函数

• 文本处理函数
  left() 返回串左边的字符
  length（）返回串的长度
  locate（）找出串的一个子符
  lower（）将串转换为小写
  ltrim（） 去掉串左边的空格
  right（）返回串右边的字符
  rtrim（）去掉串右边的空格‘
  soundex（）返回串的soundex值
  substring（） 返回子串的字符（返回发音一样的字符，防止错别字）
  upper（）将串转换为大写

• 日期和时间处理函数
  adddate（） 增加一个日期（天/周等）
  addtime（）增加一个时间（时/份等）
  curdate（） 返回当前日期
  curtime（）返回当前时间
  date()返回日期时间的日期部分
  datediff（） 计算两个日期之差
  date_add（）高度灵活的日期运算函数
  date_format() 返回一个格式化的日期或时间串
  day（）返回一个日期的天数部分
  dayofweek（） 对于一个日期，返回对应的星期几
  hour（）返回一个时间的小时部分
  minute（）返回一个数据的分钟部分
  month（）返回一个日期的月份部分
  now（）返回当前日期和时间
  second（） 返回一个时间的秒的部分
  time（）返回一个日期时间的时间部分
  year（）返回一个日期的年份部分
  例：检索出2005年9月的数据
  方法1：where date（时间） between ’2005-09-01‘ and ’2005-09-30‘；
  方法2：where year（时间）=2005 and month（时间）=9；

• 数值处理函数
  abs（）返回一个数的绝对值
  cos（）返回一个角度的余弦
  exp（）返回一个数的指数值
  mod（）返回除操作的余数
  pi（）返回圆周率
  rand（） 返回一个随机数
  sin（）返回一个角度的正弦
  sqrt（）返回一个数的平方根
  tan（）返回一个角度的正切

12.汇总函数

avg（） 返回某列的平均值，忽略了列值为null的行
count（） 返回某列的行数
count（*）对表中行的数目进行计数，不忽略空值
count(列名）忽略空值
max（）返回某列的最大值，忽略值是NULL的行
min（） 返回某列的最小值，忽略值是NULL的行
sum（） 返回某列值之和，忽略值是NULL的行

13.分组数据

13.1 数据分组

group by
注意：group by 子句中列出的每个列都必须的hi检索列或有效的表达式（但不能是聚集函数）。是表达式时不能使用别名。除聚集计算语句外，select语句中的每个列都必须在group by子句中给出。

13.3 过滤分组

having

• having 和 where区别：
  where是过滤行，而having过滤分组
  也可以理解为：where在数据分组前进行过滤，having在数据分组后进行分组。

14.使用子查询

14.1 利用子查询进行过滤

where 列 in（select 子查询）
注意：where 后的列与select语句中的列数目相同，通常使用单个列，可以使用 where 连接条件代替。

14.2 作为计算字段使用子查询

在selct 后使用子查询，需要有主键约束；可以用left join 替代

15.联结表

外键：为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。
inner join 内链接也叫等值连接。

16.创建高级联结

使用别名，对表和列都可以

16.1 不同类型的联结

• 自联结

• 自然联结，每个内部联结都是自然联结

• 外部联结：outer join
  right join–保留右边所有行
  left join–保留左边所有的行

17 组合查询

17.1 union操作符

• 对单个表进行操作，不同条件返回相同的列名，使用union相当于两个条件使用OR连接。
• union中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）
• union all保留所有行（此时，完成了where 无法完成的任务）；union重复的行被自动取消；

17.2 对组合查询结果排序

只能在union组合之后，出现在最后一条select语句之后，只能只用一个order by

注：union也可以应用不同的表

18.全文本搜索

两个最常用的引擎：MyISAM和InnoDB，前者支持全文本搜索，后者不支持。
特点：不需要分别查看每个行，不需要分别分析和处理每个词。mysql创建指定列中各词的一个索引，搜索可以针对这些词进行。

• 增加索引：FULLTEXT（列名） ，对列进行索引
  可以在创建时指定，或者在稍后指定。不要在导入数据时使用FULLTEXT，先导入，再修改表定义FULLTEXT。

• MACTH()指定被搜索的列

• AGAINST（）指定要使用的搜索表达式

• 列：SELECT note_text
  FROM productnotes
  WHERE Match(note_text) Against(‘rabbit’);
  对note_text进行搜索，指定rabbit作为搜索文本，

• 搜索不区分大小写：除非使用BINARY方式，否则全文本搜索不区分大小写。

• 使用LIKE代替：
  SELECT note_text
  FROM productnotes
  WHERE note_text LIKE ‘%rabbit%’;

• 全文本搜索与LIKE加通配符的区别：
  结果上顺序不同，全文本搜索返回以文本匹配的良好程度排序的数据，LIKE
  以不是特别有用的顺序返回。

• 使用位置：1.可以像上述例子一样跟在where后面；
  2.在select后，生成词的优先等级，等级为0的结果是不包含指定词的。

• 优先级：只有一个词是，词越靠前等级越高；有多个搜索项时，包含词越多等级越高。

• 使用查询扩展：即在有搜索词的结果之外，还想要其他线管的所有行，返回的行会更多。
  步骤：先基于搜索项进行基本的全文本搜索；
  其次，mysql检查这些匹配行并选择所有有用的词；
  在其次，mysql再次进行全文本搜索，这次不仅使用原来的条件，而且还使用所有有用的词。
  例：SELECT note_text
  FRPM productnotes
  WHERE Match(note_text) against(‘anvils’ WITH QUERY EXPANSION);

18.2 布尔文本搜索

与全文本搜索的区别：即使不定义FULLTEXT索引，也可以使用，但是是一种非常缓慢的操作。

• 优点：可以提供：要匹配的词；要排斥的词；排列提示；表达式分组；另外一些内容。

• 例子：WHERE Match（note_text) against(‘heavy -rope*’ IN BOOLEAN MODE);
  表示匹配heavy，但排除rope*（任何以repo开始的词）

• 全文本布尔操作符
  +包含，此必须存在
  –排除，词必须不出现
  >包含，而且增加等级值
  <包含，且减少等级值
  （）把词组成子表达式
  ~ 取消一个词的排序值
  * 词尾的通配符（截断）
  ""定义一个短句（与单个词的列表不一样，它匹配整个短语以便包含或排除这个短语）

• 说明：
  在搜索全文本数据时，短词被忽略（字符小于等于3个）
  mysql带有一个内建的非用词列表，这些词的索引总被忽略。
  高频词（出现在50%以上的行中）则作为一个非用词被忽略。
  表中的行数少于三行，则全文本搜索不返回结果。
  忽略词中的单引号。
  不具有词分隔符。
  仅MyISAM数据库引擎中支持全文本搜索。

19.插入数据

19.1数据插入

• INSERT：
  1.插入完整行
  insert into 表名 values（），以列的次序填充，没有值时填充NULL。（比较不安全）
  2.插入行的一部分
  insert into 表名（列名） values（相应的值）
  注意：省略列的条件是允许有NULL值，或者表定义中给出默认值。
  3.插入多行
  插入列名相同时：
  insert into 表名（列名） values（相应的值1），（相应的值2），……
  插入表不同时，就吧插入2的方式重复使用，每次用分号隔开。
  4.插入某些查询的结果
  比如两个表合并：
  insert into customers(列名）
  select 列名 from 表名
  将表中的数据导入到需要插入的表里，要求列之间对应，两个表的主键不要相同。

20 更新和删除数据

数据操纵语言

20.1 更新数据

• UPDATE语句：
  1.更新一列：
  UPDATE 表名
  SET 列名=’‘（赋值）
  where 筛选条件（更新哪一列）
  2.更新多列：
  UPDATE 表名
  SET 列名1=’‘（赋值），
  列名2=’‘（赋值）
  where 筛选条件

• IGNORE关键字
  在更新多行时一行或多行发生错误，整个操作都取消，为了即使发生错误也继续进行更新，使用：UPDATE IGNORE ……
  3.删除某个列的值
  把列值直接赋值为’NULL’。

20.2 删除数据

• DELETE语句
  1.删除特定行
  DELETE FROM 表名
  WHERE 筛选条件（锁定行）
  注意：DELETE删除的是整行而不是列，删除指定列，请使用UPDATE语句。
  DELETE删除表的内容而不是删除表。
  2。删除所有行：
  不加where条件
  或者使用TRUNCATE TABLE ，速度更快，时间是删除原来的表并重新创建一个表。

21 创建和操纵表

21.1创建表

CREATE TABLE 新表名（IF NOT EXISTS)
（列名 数据类型 NOT NULL/NULL AUTO_INCREMENT，
列名 数据类型 NOT NULL/NULL DEFAULT 1,–默认值
……
PRIMARY KEY (列名))
ENGINE=InnoDB；

• last_insert_id() 可以获取最后一个AUTO_INCREMENT值。
• 每个表只允许一个AUTO_INCREMENT列，而且它必须被索引（如，通过使他成为主键）
• 引擎类型：
  1.InnoDB 是一个可靠的事务处理引擎，不支持全文本搜索；
  2.MEMORY在功能等同与MyISAM，但由于数据存储在内存中，速度很快；
  3.MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。

21.2 更新表

ALTER

• 添加一列： ALTER TABLE 表名 DROP COLUMN 列名；

• 添加外键：ALTER TABLE 表名1 ADD CONSTRAINT 外键名 FOREIGN KEY （列名1）
  REFERENCES 表名2 （列名2）;

• 删除表：drop table 表名；
  注意：永久删除该表。

• 重命名表：RENAME TABLE 表名（原来） TO 表名（新）；
  可一次对多个表名重命名。

22.使用视图

22.1视图

• 什么是视图：视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询。

• 应用：重用SQL语句；
  简化复杂的SQL操作；
  使用表的组合部分；
  更改数据格式和表示；
  保护数据。
  创建好视图后可以当作表一样操作。

• 视图的规则和限制：
  必须唯一命名；
  数目没有限制；
  必须有足够的访问权限；
  视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图。
  order by可以用到视图中，但如果从该视图检索数据SELECT中也含有ORDER BY，那么该视图中的ORDER BY将被覆盖。
  视图不能索引，也不能有关联的触发器或默认值。
  视图可以和表一起使用。

22.2使用视图

• 创建视图：CREATE VIEW（CREATE OR REPLACE VIEW）
• 查看创建视图的语句：SHOW CREATE VIEW 视图名称；
• 删除视图：DROP VIEW 视图名；
• 可更新但有具体条件：含有分组，联结，子查询，并，聚集函数，DISTINCT，导出列时不可更新。

23 使用存储过程

23.1 存储过程

简单来说就是为以后的使用而保存的一条或多条mysql语句的集合。可视为批文件。

23.2使用存储过程

• 执行存储过程
  CALL 存储过程名称 （@参数，……）

• 创建存储过程
  CREATE PROCEDURE 存储过程名称（）
  BEGIN
  查询语句
  END；
  注：在（）中列出参数；
  因为MYSQL语句使用；作为分隔符，所以存储过程结束的时候改一下
  DELIMITER//
  CRATE PROCEDUER 存储过程名称（）
  BEGIN
  ……
  END//
  DELIMITER;
  使用//作为新的语句结束分隔符。

• 删除存储过程
  DROP PROCEDURE (IF EXISTS) 存储过程名称；

• 使用存储过程
  CALL 存储过程名（）

• 使用参数
  一般不显示结果，而是把结果返回给你指定的变量。
  （变量：临时储存数据）
  CREATE PROCEDURE 存储过程名称（
  OUT 变量名 类型
  )
  BEGIN
  SELCT MIN()
  INTO 变量
  FROM 表
  END；
  结果储存到变量里。
  CALL 存储过程名（@变量名）；
  可以直接显示需要的结果。

• 还可以在存储过程中使用游标。

24.管理事务处理

24.1 事务处理

MYISAM不支持事务处理管理；InnoDB支持。

• 作用：维护数据库的完整性，保证成批的MySQL操作要么完全执行，要么完全不执行。。