最全的数据库笔记

数据库笔记

第一章 了解Sql

创建数据库表

• 不应该将客户得清单和订单得清单混合在一起，因为他们之间得逻辑相差比较大，日后得索引和查找 都比较困难

多列组合主键

• 多列作为主键时，所有列得组合必须是唯一的

第二章 MySql简介

MySql受欢迎

• 免费，性能高，可信赖，简单

第三章 使用MySql

MySql用户列表

• MySql内部维护了一张用户信息权限表，不同得用户具有不同得权限，并且把用户得权限关联起来

数据库关键字（语句结束加；）

• 函数	说明
  use crashcourse	使用crashcourse数据库
  show databases	返回可用数据库得一个列表
  show tables	返回数据库中可用表得列表
  show columns from customers	返回表得结构信息，相当于：describe customers

第四章 检索数据

检索单个列

• select prod_name from products;
  • 返回所在列名得所有行，数据没有经过过滤，也没有经过排序

检索多个列

• select prod_id，prod_name，prod_price from products;
  • 返回所在列名得所有行，数据没有经过过滤，也没有经过排序
• 数据得格式化是一个表示问题，而不是一个检索问题

检索所有列

• select * from products;
  • 返回所有列名得所有行，数据没有经过过滤，也没有经过排序

检索所在列不重复行

• select distinct prod_name from products;
  • 只返回prod_name所在列不同的行
• distinct应用于所有的列，而不仅仅是他前置的列，除非指定的几个列都不相同，否则所有的行都要被检索出来

检索所在列的n条数据

• select prod_name from products limit 5;
  • 返回所在列名得5行，数据没有经过过滤，也没有经过排序
• limit 5，5：会从标记为5的行开始的5行数据展示出来

使用完全限定表明

• select products.prod_name from products;
  • 返回所在列名得所有行，数据没有经过过滤，也没有经过排序

第五章 排序检索数据

排序数据

• 数据最初被添加到表中，但是数据后来进行更新或者删除，他们初始的顺序会受到MySql重用回收存储空间的影响

按单个列排序

• select prod_name from products order by prod_name;
  • 返回所在列名得所有行，数据经过prod_name为规则进行排序
• order by子句中所使用的列是为了显示所选择的列，用非检索的列排 序数据也是合法的

按多个列排序

• select prod_name，prod_id，prod_price from products order by prod_price，prod_name;
  • 先按prod_price进行排序，然后按照 prod_name进行排序 -
• 如果prod_price所有的值都是唯一的，那么将不会按照prod_name 的值进行排序

指定排序方向

• select prod_name，prod_id，prod_price from products order by prod_price desc，prod_name;
  • prod_price列进行降序排序，而另外两列则会按照prod_name的值进行标准升序排列

第六章 过滤数据

使用WHERE子句

• SELECT id FROM acl_role_permission WHERE id = 1;
  • 查找出id列所有行中id = 1的行
• 能在数据库中过滤的数据，就在数据库阶段进行过滤，因为在业务层过滤数据的时候，会传递很多数据，造成运行缓慢

检测单个值

• SELECT id，price FROM acl_role_permission WHERE id = 1;

不匹配检查

• SELECT id，price FROM acl_role_permission WHERE id <> 1;

范围值检查

• SELECT id，price FROM acl_role_permission WHERE id between 1 and 5
  • 查询范围中所有的值，包括开始值和结束值

空值检查

• SELECT id，price FROM acl_role_permission WHERE id is null;
  • 检查id=null的行

第七章 数据过滤

组合WHERE子句

• 通过组合子句的方式进行数据的过滤，使得过滤的数据更加的准确，包括and和or两种方式

And操作符

• SELECT id，price FROM acl_role_permission WHERE id = 1 and price = 5;
  • 查找id = 1 and price = 5所在行的数据（与关系）

Or操作符

• SELECT id，price FROM acl_role_permission WHERE id = 1 or price = 5;
  • 查找id = 1 or price = 5所在行的数据（或关系）

计算次序

• Where子句中可以包含无限多的and和or，他们之间的运算顺序。And操作符号的优先级比or的，可以使用（）来调整他们之间的优先级，一般情况使用（），有利于理解和正确的运行结果

In操作符

• SELECT id,price FROM acl_role_permission WHERE id in (1,5);
  • 范围内的 id in (1,5) 条件都会进行匹配，会检索id = 1 or price = 5，他的作用和or的作 用是一样的

not操作符

• SELECT id,price FROM acl_role_permission WHERE id not in (1,5);
  • 范围内的 id in (1,5) 条件都不会进行匹配，否定他之后所跟的条件

第八章 用通配符进行过滤

Link操作符

• 通配符：用来匹配一部分的特殊字符 在搜索子句中添加通配符，必须使用Link

%通配符

• SELECT id,price FROM acl_role_permission WHERE id link 1%;
  • 检索以1开头的字符，并且接受1之后的任意长度字符，%可以在任意位置

_ 通配符

• SELECT id,price FROM acl_role_permission WHERE id link 1_;
  • 检索以1开头的字符，并且接受1之后一个字符的长度的字符
• 注意
  • 通配符运算比较慢，不要过度使用不要放在检索的首要条件

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

使用正则表达式

• 正则表达式的作用是为了匹配文本，将一个模式与一个文本字符串进行比较，MySql用where子句对 正则表达式提供了初步的支持，允许使用正则表达事过滤select检索出数据

基本字符匹配

• SELECT id,price FROM acl_role_permission WHERE id regexp 1000 order by id;

  • 筛选出id regexp 1000的字符出所在的行

• SELECT id,price FROM acl_role_permission WHERE id regexp .000 order by id;

  • 筛选出id regexp （匹配任意一个字符）000的字符出所在的行

• 使用regexp进行正则表达式的匹配，不区分大小写的，想要区分大小写的话regexp binary筛选出id regexp 1000|2000的字符出所在的行（或关系）

匹配几个字符之一

• SELECT id,price FROM acl_role_permission WHERE id regexp [123] order by id;
  • 筛选出id regexp [123]的字符出所在的行（或关系），匹配 id=1，2，3
• 一般来说，整个的进行或关系，可以使用`1000|2000`，但是只有部分匹配的话，使用[123]

匹配范围

• SELECT id,price FROM acl_role_permission WHERE name regexp [1-9]ton order by id;
  • 筛选出name regexp [1-9]ton的字符出所在的行（或关系），匹配 name = 1ton，2ton，3ton，…
• 注意 这里的关系是包含关系，即只要他们的关系中包含[1-9]ton就可以进行匹配

匹配特殊字符

• SELECT id,price FROM acl_role_permission WHERE name regexp \\. order by id;
  • 对.进行转义，使得按照.进行匹配使用or进行匹配
• SELECT id,price FROM acl_role_permission WHERE id regexp 1000|2000 order by id;

匹配字符类

• 类	说明
  [:alnum:]	任意字母和数字
  [:alpha:]	任意字符
  [:blank:]	空格和制表
  [:cntrl:]	ASCII控制字符
  [:dight:]	任意数字
  [:graph:]	与[:print:]相同，但不包括空格
  [:lower:]	任意小写字母
  [:print:]	任意可打印字符
  [:punct:]	既不在[:alnum:]又不在[:cntrl:]中的任意字符
  [:space:]	包括空格在内的任意空白字符
  [:upper:]	任意大写字母
  [:xdight:]	任意十六进制数字

匹配多个实例

• 元字符	说明
  *	0个或者多个匹配
  +	1个或者多个匹配
  ？	0个或者1个匹配
  {n}	指定数目的匹配
  {n,}	不少于指定数目的匹配
  {n,m}	在指定数目之间的匹配

• SELECT id,price FROM acl_role_permission WHERE name regexp \\([1-9] ton?\\) order by id;

  • 筛选出name regexp \\([1-9] ton?\\)的字符出所在的行

定位符

• 为了匹配特定位置的文本

• 元字符	说明
  ^	文本的开始
  $	文本的结束
  [[:<:]]	词的开始
  [[:>:]]	词的结束

第十章 创建计算字段

拼接字段

• 在数据库中有很多字段并不是我们所需要的格式，所以我们进行拼接字段达到我们想要的格式

concat函数

• SELECT concat(id,` `price) FROM acl_role_permission order by id;
  • 对id，price字段进行连接

RTtim函数

• SELECT concat(RTtim(id),` `price) FROM acl_role_permission order by id;
  • RTtim函数会去掉数据库中值的右侧的所有空格
  • LTtim函数会去掉数据库中值的左侧的所有空格
  • Ttim函数会去掉数据库中值的两侧侧的所有空格

使用别名

• SELECT concat(RTtim(id),` `LTtim(price)) as title FROM acl_role_permission order by id;
  • 方便辨识

执行算数计算

• SELECT id,quantity,price,quantity*price as expanded_price FROM acl_role_permission order by id;
  • 会在客户机上多输出一列quantity*price as expanded_price的信息
• 在SQL中可以使用的算计计算
  • 加减乘除

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

使用函数

• 用于处理文本字符串
• 在数值数据上进行算数运算
• 用于处理日期和时间值
• 返回DBMS正使用的特殊信息

文本处理函数

• SELECT concat(RTtim(id),` `upper(LTtim(price)) as title FROM acl_role_permission order by id;

  • 变成大写

• 函数	说明
  Left()	返回串左边的字符
  Length()	返回串的长度
  Locate()	找出串的一个字串
  Lower()	将串变成小写
  LTrim(),RTrim(),Tirm()	去掉串左边（右边，两边）的空格
  Right()	返回串右边的字符
  Soundex()	返回串的Soundex值
  Substring()	返回子串的字符
  Upper()	将串转换为大写

日期和时间处理函数

• 常用的日期和时间处理函数

• 函数	说明
  AddDate()	增加一个日期
  AddTime()	增加一个时间
  CurTime()	返回当前时间
  Date()	返回时间的日期部分
  DateDiff()	计算两个日期之差
  Date_Add()	高度灵活的日期运算函数
  Date_format()	返回一个格式化的日期或时间串
  Day()	返回日期的天数部分
  DayOfWeek()	对于一个日期，返回对应星期几
  Hour()	返回时间的小时部分
  Minute()	返回时间的分钟部分
  Month()	返回时间的月部分
  Now()	返回当前日期和时间
  Second()	返回时间的秒部分
  Time()	返回日期时间的时间部分
  Year()	返回时间的年部分
  CurDate()	返回当前日期

数值处理函数

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

第十二章 汇总函数

聚集函数

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

AVG()

• SELECT AVG(price) AS myprice FROM products;

COUNT()

• SELECT COUNT(price) AS myprice FROM products;

MAX()

• SELECT MAX(price) AS myprice FROM products;

MIN()

• SELECT MIN(price) AS myprice FROM products;

SUM()

• SELECT SUM(price) AS myprice FROM products;

组合聚集函数

• SELECT SUM(price) , MIN(price) , MAX(price) FROM products;

第十三章 分组数据

规定

• GROUP BY可以包含任意数目的列
• 如果嵌套分组，那么数据将在最后规定的分组上进行汇总
• GROUP BY子句中列出的每个列都必须是检索列或者有效表达式
• 除聚集计算语句外，Select语句中的，每个列都必须在GROUP BY子句中给出
• 如果分组列中有NULL，则NULL将作为一个分组返回
• GROUP BY子句必须实在where语句后，ORDER BY语句之前

过滤分组

• where是为了过滤行，having是为了过滤分组
• SELECT id,COUNT(*) AS orders FROM orders GROUP BY id HAVING COUNT(*) >= 2;

第十四章 使用子查询

• 查询语句中可以嵌套查询语句

第十五章 联结表

好处

• 分解数据可以使表更加的容易管理，更方便的处理，并且有很大的伸缩性

• SELECT vend_name,prod_name,prod_price FROM vendors,products

  WHERE vendors.vend_id, products.vend_id ORDER BY vend_name,

  prod_name;

注意

• 表与表之间的联结是在运行中建立的
• 当没有where语句时，将第一个表的每一行与第二个表进行匹配，不管有没有逻辑

内部联结

• SELECT vend_name,prod_name,prod_price FROM vendors inner jion products on vendors.vend_id = products.vend_id ;

联结多个表

• SELECT vend_name,prod_name,prod_price,quantity FROM vendors ,orderitems, products where vendors.vend_id = products.vend_id

  AND orderitems.prod_id = products.vend_id AND order_num = 20005;

• 联结的表越多，性能下降的越厉害

第十六章 使用带聚集函数的联结

使用表别名

• SELECT vend_name,prod_name,prod_price,quantity FROM vendors as v,orderitems as o, products as p where v.vend_id = p.vend_id

  AND o.prod_id = p.vend_id AND order_num = 20005;

第十七章 组合查询

第十八章 全文本搜索

第十九章 插入数据

第二十章 更新和删除数据

第二十一章 创建和操纵表

第二十二章 使用视图