MySQL必知必会笔记.docx

1、MySQL必知必会笔记MySQL必知必会笔记（一）基础知识和基本操作第一章 了解MySQL 数据库 保存有组织的数据的容器。（通常是一个文件或一组文件） 人们经常使用数据库这个术语代替他们使用的软件。这是不正确的，确切的说，数据库软件应称为DBMS(数据库管理系统)，数据库是通过DBMS创建和操纵的容器。漱口可以是保存在硬件设备上的文件，但也可以不是。你使用DBMS来代替自己访问数据库。 表 表是一种结构化的文件可用来存储某种特定的类型的数据。 某种特定类型数据的结构化清单。 模式 关于数据库和标的布局及特性的信息 列 表中的一个字段。表由列组成。列中存储着表里某部分的信息。 数据类型 所容许

2、的数据的类型。每个表列都有相应的数据类型， 它限制（或容许）该列中存储的数据。 行 表中的一个记录 主键 一列（或一组列），其值能够唯一区分表中的每个行。 虽然不是必须的，但是一般都建立主键。便于以后的数据管理 表中任何列都可以作为主键，只要满足以下条件： 1 任意两行都不具有相同的主键值。 2 每个行都必须有一主键值（主键值不允许为NULL） 主键的最好习惯： 不更新主键列的值 不重用主键列的值 不在主键列中使用可能会更改的值 什么是sql Sql是结构化查询语言的缩写。Sql是一种专门用类与数据库通信的语言。第二章 MySQL简介 什么是Mysql Mysql是一种DBMS，即它是一种数据

3、库软件。 Mysql版本主要更改 4-InnoDB引擎，增加了事务处理、并、改进全文搜索等支持 4.1对函数库、子查询、集成帮助等的重要增加、 5存储过程、触发器、游标、试图等。第三章 使用mysql 连接 主机名 端口 一个合法用户 用户口令 Mysql -u root -p -h myserver -P 9999 选择数据库 可使用USE关键字，mysql语言组成部分的一个关键字，绝不要使用关键字命名一个表或列 USE dataname; 显示数据库列表 SHOW DATABASES; 一个数据库内的表的列表（USE进入数据库的情况下） SHOW TABLES; SHOW也可以用来显示表列

4、 SHOW COLUMNS FROM column; DESCRIBE 是SHOW COLUMNS的一种快捷方式；DESCRIBE cust; 其他的SHOW 语句 SHOW STATUS 显示广泛的服务器状态信息 SHOW CREATE DATABASE 显示创建特定数据库的MYSQL语句 SHOW CREATE TABLE 显示创建特定表的MYSQL语句 SHOW GRANTS 显示授权用户的安全权限 SHOW ERRORS 显示服务器的错误信息 SHOW WARNINGS 显示服务器的警告信息MySQL必知必会笔记（二）SELECT语句检索排序过滤通配符搜索正则表达式搜索第四章 检索数据

5、 检索单列 SELECT columnOne FROM table; 检索多列 SELECT columnOne,columnTwo,columnThire FORM table; 检索所有列 SELECT * FROM products; /一般，除非你确实需要表中的每个列，否则最好不要用*通配符 检索不同的行 检索出来的数据不重复DISTINCT关键字，顾名思义返回不同的值 SELECT DISTINCT columnOne FROM table; /检索出来的columnOne没有重复值 DISTINCT关键字应用于所有列而不仅是前置它的列 SELECT DISTINCT vend_id

6、，prod_price.要求vend_id，prod_price这两列都不出现重复的值 限制结果条数 sql语句后面加入下面sql语句 LIMIT 5 显示结果的前5条 LIMIT 3,4 从行3开始的后4行 LIMIT 4 OFFSET 3 从行3开始的后4行第五章 排序检索数据 子句 Sql语句是由子句构成，有些子句是必须的，有些事可选的。一个子句通常是由一个关键字和所提供的数据组成。 按单列排序SELECT columnOne FROM table ORDER BY columnOne; SELECT columnOne FROM table ORDER BY columnTwo; /用

7、非检索列也是可以的，如根据columnTwo 按多列排序SELECT columnOne,columnTwo,columnThire FROM products ORDER BY prod_price,prod_name;排序是先根据前面的columnOne排序 ，如果一样再根据后面的columnTwo排序 指定排序方向ASC 升序 默认 DESC 降序 关键字仅作用到直接位于前面的列名SELECT columnOne,columnTwo,columnThire FROM table ORDER BY columnOne ASC, columnTwo DESC; /先按columnOne正排序

8、 再按columnTwo倒序排序第六章 过滤数据 使用WHERE 语句 SELECT columnOne,columnTwo FROM table WHERE columnOne = 3;WHERE子句操作符 操作符 说明 = 等于 不等于（数字比较） != 不等于（数字和字符串比较） 大于 大于 = 大于等于BETWEEN 在指定的两个指之间 必须指定2个值。这两个值必须使用AND连接 SELECT columnOne,columnTwo, FROM table WHERE columnOne BETWEEN 3 AND 8; / 检索columnOne值为3到8之间的行空值检查 SELEC

9、T 语句中有一个子句检查具有NULL值的列,IS NULL子句。 SELECT columnOne FROM table WHERE columnOne IS NULL; /检索 columnOne值为NULL的行 注意：在数据库中NULL是不能被匹配和不匹配的语句找出来，所以一定要注意表中是否存在NULL值。第七章 数据过滤 组合WHERE子句 组合方式AND组合 和 OR组合 必须满足所有条件SELECT columnOne,columnTwo,columnThire FROM table WHERE columnOne = 2009 AND columnTwo = 10; /检索colu

10、mnOne =2009 且 columnTwo = 10 的行 满足任意一个条件SELECT columnOne,columnTwo,columnThire FROM table WHERE columnOne = 2009 OR columnTwo = 10;/检索columnOne =2009 或 columnTwo = 10 的行 计算次序 先计算AND再计算OR,不要过分依赖默认计算次序，可以使用括号改变计算次序，它能消除歧义。 IN操作符IN操作符用来指定范围，范围中的每个条件进行匹配。IN取合法值的逗号分隔的清单。 SELECT columnOne,columnTwo FROM t

11、able WHERE columnOne IN (1002,1005,1006) ORDER BY columnTwo; /检索columnOne为1002或1005或1006的行并且按columnTwo分组 IN操作符完成与OR相同的功能，优点如下： 1 使用长的合法选项清单时，IN操作符的预防更清楚且直观 2 使用IN时，计算的次序更容易管理（以为使用的操作符更少） 3 IN操作符一般比OR操作符执行更快 4 IN操作符最大的优点可以包含其他SELECT语句，使得能够更动态的创建WHERE子句。 NOT操作符 WHERE子句中的NOT操作符有且只有一个功能，那就是否定它之后所跟的任何条件

12、SELECT columnOne,columnTwo FROM table WHERE columnOne NOT IN (1002,1005,1006) ORDER BY columnTwo; /检索columnOne不为1002或1005或1006的行并且按columnTwo分组 MySQL支持使用NOT对IN、BETWEEN和EXISTS子句取反。第八章 用通配符进行过滤 LINK操作符 通配符：用来匹配值的一部分的特殊字符。 搜索模式：又字面值、通配符或两者组成构成的搜索条件 % 任何字符出现任何位置区分大小写 /检索以jet开头的词或句子 SELECT columnOne,colum

13、nTwo FROM table WHERE columnOne LINK jet% ; /检索以jet结尾的词或句子 SELECT columnOne,columnTwo FROM table WHERE columnOne LINK %jet ; /检索以jet包含的词或句子 SELECT columnOne,columnTwo FROM table WHERE columnOne LINK %jet% ; /检索以e开头，以u 结尾的词或句子 /检索 e%u ; %还可以匹配0字符，注意尾空格会影响搜索模式的结果。 _ 匹配单个字符 SELECT columnOne,columnTwo F

14、ROM table WHERE columnOne LINK _abc /匹配aabc eabc eabc 等前面一个字母的词 让like区分大小写的方法 在WHERE和列名之间加BINARY关键字，或者再建立表时就指定区分大小写name varhar(50) binary 使用通配符是有代价的，提供以下的技巧： 不要过度的使用通配符 除非是必要的，否则通配符不要用在搜索模式的开始处 仔细注意通配符的位置。不要放错位置第九章 用正则表达式进行搜索 仅支持正则表达式的一小部分 基本字符匹配 检索列prod_name包含1000 的所有行 SELECT columnOne FROM table W

15、HERE columnOne REGEXP 1000 ORDER BY columnOne . 匹配任意一个字符1000 2000 3000 a000 SELECT columnOne FROM table WHERE columnOne REGEXP .000 ORDER BY columnOne 正则匹配不区分大小写 ,如想区分匹配可在REGEXP 后面加上BINARY关键字 进行OR匹配 为搜索两个或n个字符串之一 SELECT columnOne FROM table WHERE columnOne REGEXP 1000|2000|3000 ; 匹配单个字符 SELECT colum

16、nOne FROM table WHERE columnOne REGEXP 123 Ton; /匹配1 Ton 或2 Ton或3 Ton当有非匹配的内容时使用,它是 | 的另一种形式，如1|2|3 Ton这时匹配的只有3带有Ton 如果想要得到非匹配的内容可以使用123的形式 匹配范围 0-9 a-zA-Z SELECT columnOne FROM table WHERE columnOne REGEXP 1-5 Ton ; 匹配特殊字符 想要匹配 . | 这些字符串怎么办呢，可以在这些字符前加 进行转义,第一个 mysql自己解释，第二个给正则解释的 元字符 说明 f 换页 n 换行 r

17、 回车 t 制表 v 纵向制表 匹配字符串类 自己经常使用的数字、所有字母或所有数字字母字符等的匹配。为了方便工作，可以使用预定义的字符集，称为字符集： 类 说明 :alnum: 任意字母和数字，同0-9a-zA-Z :alpha: 任意字符，同a-zA-Z :blank: 空格和制表，同t :cntrl: ASCII控制字符，ASCII0到31和127 :digit: 任意数字 同0-9 :graph: 与:print:相同，但不包括空格 :lower: 任意小写字母，同:a-z: :print: 任意可打印字符 :punct: 既不在:alnum:有不在:cntrl: 的字符 :space

18、: 包括空格在内的任意空白字符，同fnrtv :upper: 任意大写字母A-Z :xdigit: 任意十六进制数字，同a-fA-F0-9 匹配多个实例 以前的匹配都是单次匹配。如果存在一个匹配，改行就检索出来，如果不存在，检索不出任何行。但有时需要对匹配的数目进行更强的控制。 重复元字符 元字符 说 明 * 0个或多个匹配 + 1个或多个匹配 ? 0个或1个匹配 n 指定数目匹配 n, 不少于n个匹配 n,m 匹配数目的范围 m不超过255 列：SELECT columnOne FROM table WHERE columnOne REGEXP (0-9 sticks?);Sticks?匹配

19、的是 stick 或sticks（？号决定前面的s出现一次或0次） 列：SELECT columnOne FROM table WHERE columnOne REGEXP :digit: ORDER BY columnOne;:digit:匹配任意的数字，4要求前面匹配的数字出现4次 定位符 前面的所有例子都是匹配一个串中的任意位置的文本。为了匹配特定位置的文本 元字符 说 明 文本的开始 $ 文本的结束 : 词的结束 例如：你要找一个数（包括以小数点开始的数）开始的所有产品，怎么办，前面都是在行内任意位置匹配。所以不行 SELECT columnOne FROM table WHERE c

20、olumnOne REGEXP 0-9 . ; 简单的正则测试,可以不在数据库操作的情况下练习 SELECT hello REGEXP 0-9;MySQL必知必会笔记（三）SELECT语句计算字段数据处理函数汇总函数分组数据子查询第十章 创建计算字段 计算字段 存储在表中的数据一般不是应用程序所需要的格式。我们需要直接从数据库中检索出转换、计算或格式化的数据。而不只是检索出数据，然后再到应用程序或报告程序中区格式化。 这就发挥了计算字段的作用了。与前面的字段不同，计算字段并不实际存在于数据库中。计算字段是运行时在SELECT 语句中创建的。 需要注意的是，只有SELECT语句知道那些列是实际列

21、，哪些列不是，客户机的角度来看，计算字段和其他字段是一样的。 拼接字段 拼接：将值联结到一起构成单个值。 生成供应商 columnOne（columnTwo） 的格式 SELECT Concat(columnOne, ( , columnTwo, ) FROM table ORDER BY columnOne; 使用别名 别名使用AS关键字赋予 执行算术运算 另一常见的用途就是对检索出来的数据进行算术运算。 例如：检索出column_id 为2005的columnOne乘以columnTwo的值 SELECT column_id, columnOne, columnTwo, columnOne

22、*columnTwo AS column_price FROM table WHERE column_id = 2005 操作符 + - * / SELECT 3*2;将返回6 SELECT Now(); 返回当前的日期和时间第十一章 使用数据处理函数 SQL实现了一下类型的函数 1 用于处理文本串，如删除、填充、装换大小写 2 用于数据上进行的算术操作，如返回绝对值，进行代数运算 3用于处理日期和时间值并从这些值中提取特定的成分，如返回两个日期差，检查日期有效性 4 返回DBMS正使用的特殊信息 ，如用户登录信息，检查版本细节信息 文本处理函数 Upper() 将文本转换为大写 SELECT

23、 vend_name, Upper(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name; 常用的文本处理函数 Left() 返回串左边的字符 Length() 返回串的长度 Locate() 找出串的一个子串 Lower() 将串转换为小写 Right() 返回右边的字符 Soundex() 返回串的SOUNDEX值 SubString() 返回串的字符 Upper() 将串转换ewing大写 Soundex()是一个将任何文字串转换为描述语音表示的字母数字模式的算法。他考虑了类似发信字符和音节，使得能对串进行发音的比较

24、而不是字母比较，如：Y.lee搜索可以匹配Y.lie SELECT cust_name , cust_contact FROM customers WHERE Soundex(cust_contact) = Soundex(Y Lie) 删除多余空格的函数 RTrim() LTrim() Trim() 依次是删除 右边 左边 两边的空格 SELECT Concat(RTrim(vend_name), ( ,RTrim(vend_country), ) FROM vendors ORDER BY vend_name; 日期和时间处理函数 日期和时间常用相应的数据类型和特色的格式存储，以便能快速和

25、有效的排序或过滤，并节省物理存储空间。 AddDate() 增加一个日期 天、周等 AddTime() 增加一个时间 时、分等 CurDate() 返回当前日期 CurTime() 返回当前时间 Date() 返回日期时间的日期部分 DateDiff() 计算两个日期之差 Date_Add() 高度灵活的日期或时间串 Date_Format() 返回一个格式的日期或时间串 Day() 返回一个日期的天数部分 DayOfWeek() 对于一个日期，返回对于星期几 Hour() 返回一个时间的小时部分 Minute() 返回一个时间的分钟部分 Moth() 返回一个日期的月份部分 Now() 返回当前的日期和时间 Second() 返回一个时间的秒部分 Time() 返回一个日期时间的时间部分 Year() 返回一个日期的年份部分 My