msql 必知必会笔记

Edit

Mysql 必知必会

第一章 理解SQL

• 什么是数据库 
  
  • 数据库（database） 保存有组织的数据的容器
• 什么是表 
  
  • 一组特定类型的数据的结构化清单
• 什么是模式 
  
  • 数据库和表的布局及特性的信息
• 什么是列 
  
  • 表中的一个字段，所有的表都是由一个或者多个列组成
  • 什么是数据类型 
    
    • 所容许的数据类型， 每个表的每个字段都有指定的字段和类型。
• 什么是行 
  
  • 表中的一个记录
• 什么是主键 
  
  • 一列或者一组列，其值能够唯一区分表中每一行
• 主键的规则 
  
  • 主键不能重复
  • 主键不能为空
• 什么是SQL 
  
  • 结构化查询语言（Structured Query 
    Language） 专门用来与数据库进行通信的
• SQL 的优点 
  
  • 非某门语言专有
  • SQL简单易学
  • SQL 灵活，且可以进行非常复杂和高级的数据库操作

第二章 mysql 简介

• 什么是mysql 
  
  • MySQL是一种DBMS，即它是一种数据库软件。
• 为什么使用mysql 
  
  • 成本 免费
  • 性能 可以搭建集群
  • 可信赖 很多大公司也在用
  • 简单 操作简单通用
• 客户机-服务器模型 
  
  • 服务器处理，客户机请求

第三章 使用MySQL

• 连接 ，如何连接到mysql服务器上

  • 计算机名或者以ip
  • 端口 默认3306
  • 合法用户名
  • 用户密码

• root 密码

  • 最高权限，可建库删库操作

• 选择数据库

  • USE product_db

• 了解数据库和表

  • 使用show 命令查看
  • SHOW DATABASES; 查看所有的数据库
  • SHOW TABLES 当前数据库中所有的表
  • SHOW COLUMNS FROM USER 查看当前表的所有字段信息
  • DESCRIBE USER 也可查看当前表中的所有字段
  • SHOW STATUS 显示服务器的状态信息
  • SHOW GRANTS 用来显示授予用户

第四章 检索数据

• SELECT 语句

  • 用途是从一个或多个表中检索 
    信息。

• 检索单个列

  • SELECT isvalid FROM user_db.user;
• SQL 语句和大小写 SQL是不区分大小写的，但是还是在书写的时候规范
• 处理SQL时 其中所有的空格都被忽略掉
• 检索多个列 
  
  • SELECT username, password FROM user_db.user;
• 检索所有列 
  
  • SELECT * FROM user_db.user;
• 检索不同的行，相同的不显示 
  
  • SELECT DISTINCT fk_employee FROM user_db.user;
• 限制返回结果 
  
  • SELECT fk_employee FROM user_db.user LIMIT 2;
  • SELECT fk_employee FROM user_db.user LIMIT 2, 6;
• 使用全路径表名 
  
  • SELECT user_db.user.isvalid FROM user_db.user;

第五章 排序检索数据

• 明确地排序用SELECT语句检索出的数据，可使用ORDER BY子句ORDER BY子句取一个或多个列的名字 
  SELECT prod_name FROM products ORDER BY product_name;
• 按多个列排序 
  SELECT prod_name FROM products ORDER BY product_price, product_name; 
  -指定排序方向 
  SELECT prod_name FROM products ORDER BY product_name DESC; 
  ASC没有多大用处，因为升序是默认的
• 使用ORDER BY和LIMIT的组合 
  SELECT prod_name FROM products ORDER BY product_name DESC LIMIT 1;

第六章 过滤数据

• 使用WHERE子句 
  SELECT prod_name FROM products WHERE product_price = 2.50
• WHERE子句操作符 
  操 作 符 说 明 
  = 等于 
  <> 不等于 
  != 不等于 
  < 小于 
  <= 小于等于

大于 
= 大于等于 
BETWEEN 在指定的两个值之间

• 检查单个值 
  SELECT prod_name FROM products WHERE product_price = 2.50 
  SELECT prod_name FROM products WHERE product_price < 2.50 
  SELECT prod_name FROM products WHERE product_price <= 2.50
• 不匹配检查 
  SELECT prod_name FROM products WHERE product_price <> 2.50 
  SELECT prod_name FROM products WHERE product_price != 2.50
• 范围值检查 
  SELECT prod_name FROM products WHERE product_price BETWEEN 2.50 AND 5.0
• 空值检查 
  SELECT prod_name FROM products WHERE product_price IS NULL

第七章 数据过滤

• 组合WHERE子句 
  使用 以AND子句的方式或OR子句的方式
• AND操作符 
  SELECT prod_name FROM products WHERE product_price = 2.50 AND id = 1003
• OR操作符 
  SELECT prod_name FROM products WHERE product_price = 2.50 OR id = 1003
• 计算次序 任意AND 和OR 操作的顺序 
  SELECT prod_name FROM products WHERE product_price = 2.50 OR id = 1003 AND product_price < 1.00 
  **SQL（像多数语言一样）在处理OR操作符前，优先处理AND操 
  作符** 
  可以使用 括号提高优先级 
  SELECT prod_name FROM products WHERE (product_price = 2.50 OR id = 1003 ) AND product_price < 1.00
• IN操作符 
  SELECT prod_name FROM products WHERE WHERE id in(1003, 1005) ORDER BY prod_name 
  上下两者相同 SELECT prod_name FROM products WHERE WHERE id = 10003 OR id = 1005 ORDER BY
• NOT操作符 
  SELECT prod_name FROM products WHERE WHERE id NOT IN (1003, 1005) ORDER BY prod_name

第八章 用通配符进行过滤

• LIKE操作符 
  LIKE指示MySQL，后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较
• 百分号（%）通配符 
  %表示任何字符出现任意次数 
  SELECT prod_id , prod_name FROM products WHERE prod_name LIKE ‘jet%’ 
  ‘%jet%’ 
  ‘%jet’ 
  ‘j%t’
• 下划线（_）通配符 
  SELECT prod_id , prod_name FROM products WHERE prod_name LIKE ‘jet_’
• 使用通配符的技巧 
  
  • 不要过度使用通配符
  • 不要把它们用在搜索模式的开始处
  • 仔细注意通配符的位置

第九章 用正则表达式进行搜索

第十章 创建计算字段

创建表中没有的字段，我们可以对这些字段进行定义和创建

• 拼接字段 
  SELECT Concat(vend_name, ‘()’, vend_country, ‘)’) 
  FROM vendors 
  ORDER BY vend_name
• 使用别名 
  SELECT Concat(vend_name, ‘()’, vend_country, ‘)’) AS vend_title 
  FROM vendors 
  ORDER BY vend_name
• 执行算术计算 
  SELECT prod_id, product_price * product_item AS all_price 
  FORM orderitems 
  WHERE order_num = 20005. 
  MySQL算术操作符
• 加
• 减
• 乘 
  / 除

第十一章 使用数据处理函数

• 大多数SQL实现支持以下类型的函数。

  • 用于处理文本串
  • 用于在数值数据上进行算术操作
  • 用于处理日期和时间值并从这些值中提取特定成分
  • 返回DBMS正使用的特殊信息

• 文本处理函数 
  SELECT vend_name , Upper(vend_name) as vend_name_upcase 
  FROM vendors 
  ORDER BY vend_name

• 常用的文本处理函数 
  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() 返回一个日期的年份部分 
  SELECT cust_id, order_enum 
  FROM orders 
  WHERE Date(order_date) = ‘2005-09-01’

SELECT cust_id, order_enum 
FROM orders 
WHERE Date(order_date) BETWEEN ‘2005-09-01’ AND ‘2005-09-30’

• 数值处理函数

Abs() 返回一个数的绝对值 
Cos() 返回一个角度的余弦 
Exp() 返回一个数的指数值 
Mod() 返回除操作的余数 
Pi() 返回圆周率 
Rand() 返回一个随机数 
Sin() 返回一个角度的正弦 
Sqrt() 返回一个数的平方根 
Tan() 返回一个角度的正切

SELECT COS(rectucle) AS rectucle_cos FROM orders

十二章 汇总数据

• 聚集函数 
  
  • 运行在行组上， 计算和返回单个的函数

AVG() 返回某列的平均值 
COUNT() 返回某列的行数 
MAX() 返回某列的最大值 
MIN() 返回某列的最小值 
SUM() 返回某列值之和

• AVG()函数 求特定列值之和 
  SELECT AVG(price) AS avg_price 
  FROM products

SELECT AVG(price) AS avg_price 
FROM products WHERE id = 1003;

• CONUT() 函数 计数确定表中行的数目是否符合

  • COUNT(*)对表中行的数目进行计数 
    SELECT CONUT(*) AS count_price 
    FROM products
  • 用COUNT(column)对特定列中具有值的行进行计数 
    SELECT CONUT(price) AS count_price 
    FROM products

• MAX()函数 返回列中的最大值 
  SELECT MAx(price) AS max_price 
  FROM products

• MIN()函数 返回列中的最小值 
  SELECT Min(price) AS min_price 
  FROM products

• SUM()函数 返回列值的和 
  SELECT SUM(price) AS sum_price 
  FROM products

第十三章 分组数据

• 数据分组 
  使用了GROUP BY，就不必指定要计算和估值的每个组了。系统 
  会自动完成。GROUP BY子句指示MySQL分组数据，然后对每个组而不是 
  整个结果集进行聚集 
  SELECT vend_id , COUNT(*) AS num_prods 
  FROM products 
  GROUP BY vend_id

• 过滤分组 
  过滤分组不使用where关键字而是使用HAVING字段来代替 
  SELECT vend_id , COUNT(*) AS num_prods 
  FROM products 
  GROUP BY vend_id 
  HAVING COUNT(*) >= 2

• 列出具有2个（含）以上、价格为10（含）以上的产品的供应商 
  SELECT vend_id , COUNT(*) AS num_prods 
  FROM products 
  WHERE prod_price >= 10 
  GROUP BY vend_id 
  HAVING COUNT(*) >= 2

• 分组和排序 
  GROUP BY和ORDER BY的区别 
  SELECT vend_id , COUNT(*) AS num_prods 
  FROM products 
  WHERE prod_price >= 10 
  GROUP BY vend_id 
  HAVING COUNT(*) >= 2 
  ORDER BY vend_id

• SELECT子句顺序 
  一个查询中字句中的关键字的顺序 
  子 句 说 明 是否必须使用 
  SELECT 要返回的列或表达式 是 
  FROM 从中检索数据的表 仅在从表选择数据时使用 
  WHERE 行级过滤 否 
  GROUP BY 分组说明 仅在按组计算聚集时使用 
  HAVING 组级过滤 否 
  ORDER BY 输出排序顺序 否 
  LIMIT 要检索的行数 否

第十四章 使用子查询

• 子查询 
  
  • 嵌套在其他查询中的查询

我们可以对下面的这两条SQL合并为一条SQL 
SELECT order_num 
FROM orderiterms 
WHERE prod_id = ‘TNT2’

SELECT cust_id 
FROM orders 
WHERE order_num IN(20005 , 2007)

我们可以这样合并这两个SQL语句 
SELECT cust_id 
FROM orders 
WHERE order_num IN( SELECT order_num 
FROM orderiterms 
WHERE prod_id = ‘TNT2’)

• 作为计算字段使用子查询 
  SELECT COUNT(*) AS orders 
  FROM orders 
  WHERE cust_id = 10001

SELECT cust_name, 
cust_state, 
(SELECT COUNT(*) AS orders 
FROM orders 
WHERE orders.cust_id = customers.cust_id ) 
FROM customers 
ORDER BY cust_name

第十五章 联结表

• 什么是联结表 
  
  • 关系型数据库，每张表可以想象为一个单个的对象。我们可以通过对每个对象之间的属性关系建立连接。相当于每张表根据特定属性联系起来
• 外键（foreign key） 
  
  • 外键为某个表中的一列，它包含另一个表 
    的主键值，定义了两个表之间的关系。
• 为什么使用联结 
  
  • 联结是一种机制，用来在一条SELECT 
    语句中关联表，因此称之为联结
• 一个最简单的联结表查询 
  SELECT vend_name, prod_name, prod_price 
  FROM vendors, products 
  WHERE vendors.vend_id = porducts.vend_id 
  ORDER BY vend_name, prod_name;

SELECT vend_name, prod_name, prod_price 
FROM vendors, INNER JOIN products 
ON vendors.vend_id = porducts.vend_id 
ORDER BY vend_name, prod_name;

上面两种联结方式相同的

mysql 在运行关联表时是十分耗费资源的，当联结的表越多，性能下降的越厉害

十六章 创建高级联结

• 使用表别名，

  • 缩短SQL语
  • 允许在单条SELECT中多次使用相同的表 
    SELECT cust_name, cust_contact 
    FROM customers AS c , orders AS o, orderiterms AS oi 
    WHERE c.cust_id = o.cust_id 
    AND oi.order_num = o.order_num 
    AND prod_id = ‘TNT2’

• 使用不同类型的联结

  • 自联结
  • 自然联结
  • 外部联结
• 自联结 
  
  • 自己可以关联自己的查询
• 外部联结 
  
  • left Join
  • right join

外部联结语法类似。为了检索所有客户，包括那些没有订单的客户

第十七章 组合查询

组合查询的作用 
MySQL也允许执行多个查询（多条SELECT语句），并将结果作为单个 
查询结果集返回

• 创建并使用UNION

可以将上面两个查询合并为一个查询

我们也可以不使用UNION，而是使用WHERE字句来构造相同的查询

• UNION 规则

  • UNION必须由两条或两条以上的SELECT语句组成，语句之间用关 
    键字UNION分隔
  • UNION中的每个查询必须包含相同的列、表达式或聚集函数
  • 列数据类型必须兼容 
    在默认的情况下，UNION操作会取消掉重复的行 
    如果想返回所有匹配行，可使用UNION ALL而不是UNION

• 对组合查询结果排序 
  使用ORDER By但是要在最后一个SELECT语句之后

第十八章 全文本搜索

MyISAM和InnoDB，前者支持全文本搜索，而后者不支持 
为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改 
变不断地重新索引

• 启用全文本搜索支持 
  
  • CREATE语句演示了FULLTEXT子句的使用

• 进行全文本搜索 
  
  • 使用两个函数Match()和Against()执行全文本搜索， 
    其中Match()指定被搜索的列，Against()指定要使用的搜索表达式。

搜索不区分大小写

第十九章 插入数据

• 插入完整的行 
  INSERT INTO Customers 
  VALUES ( 
  ‘MAN’, 
  ‘CHINA’, 
  ‘IT’, 
  NULL 
  )
• 指定列名插入数据，较为繁琐但是比较安全 
  INSERT INTO Customers 
  (femlai， 
  country， 
  career， 
  name 
  ) 
  VALUES ( 
  ‘MAN’, 
  ‘CHINA’, 
  ‘IT’, 
  NULL 
  )

使用两种插入方式时需要注意的一些事项

如何一次性插入多行数据 
INSERT INTO Customers 
(femlai， 
country， 
career， 
name 
) 
VALUES ( 
‘MAN’, 
‘CHINA’, 
‘IT’, 
NULL 
)， 
( 
‘MAN’， 
‘USA’, 
‘TERCHER’, 
‘JACK MA’ 
)

第二十章 更新和删除数据

在使用UPDATE DELETE 时一定要加上WHERE字句，否则它会将你整张表进行修改 
UPDATE customers 
SET email = ‘1010@QQ.com’ 
WHERE id = 10002

DELETE FROM customers 
WHERE id = 1006

DELETE 删除表中的内容，DROP是删除整张表，包括表结构

第二十一章 创建和操纵表

• 如何创建表 
  
  • 使用客户端工具
  • 使用命令行脚本

• 几种常见的数据库引擎 
  
  • InnoDB 可处理事务引擎
  • MyISAM 性能极高的引擎，支持全文本搜索
• 更新表 
  使用ALTER TABLE更改表结构
• 删除表 
  DROP TABLE customers2
• 重命名表 
  RENAME TABLE customers2 TO customer;

第二十二章 使用视图

• 为什么要使用视图

• 视图的规则和限制

• 使用视图

第二十三章 使用存储过程