Mysql学习历程（一）

最近在重新学习Mysql数据库，从浅至深，打算找几本书来看看，第一本就是《Mysql必知必会》。在学习的过程中，记录一些要点，以防忘记，也方便以后温习。

1. 数据库：是一个以某种有组织的方式存储的数据集合。
2. mysql架构：客户机－服务器架构。
3. show databases：显示所有数据库
   show tables：显示选中数据库的所有表
   show columns from test：显示test表的所有字段属性
   show status：显示服务器状态信息
   show create database test：显示创建数据库test所用的msyql语句
   show create table test： 显示创建表test所用的mysql语句
   show grants：显示已授权信息
   show errors：显示服务器错误信息
   show warnings：显示服务器警告信息
   help show：显示show命令的帮助信息
4. 当心逗号：在选择多个列时，一定要在列名之间加上逗号，但最后一个列名后不加。
5. select distinct schoolname,address from school：distinct关键字是为了取唯一，不过是作用于所有列出的字段，即schoolname与address合在一起进行比较筛选。
6. select * from school limit 3,1：第一个数为开始位置，第二个数为要检索的行数。行号从0开始，依次类推。等价语句select * from school limit 1 offset 3。
7. select * from school limit 3：表示从第一行开始，要检索3行。单个数字总是表示从第一行开始。
8. 子句：clause，SQL语句由子句构成，一个子句通常由一个关键字和所提供的数据组成，例如order by子句。
9. order by：升序asc，降序desc，order by子句可以使用非检索的列进行排序，例如select schoolname from school order by address desc。
10. select * from school order by schoolname desc,address asc：先按schoolname降序，再按address升序。
11. 子句位置：在同时使用order by 和where子句时，应该让order by位于where之后。
12. between关键字：between num1 and num2，匹配范围中所有的值，包括指定的开始值和结束值。
13. select * from product where id = 1002 or id = 1003 and price >=10：因为and在计算次序中优先级更高，此句并不能达到原先的目的，其等价于select * from product where id = 1002 or (id = 1003 and price >= 10),为避免出错，应使用括号明确分组，正确语句为select * from product where (id = 1002 or id = 1003) and price >= 10。
14. 通配符：百分号（%），匹配任何字符出现任意次数，例如select * from school where address like ‘da%’,检索所有以‘da’开头的记录；下划线（_），只匹配单个字符。
15. 正则表达式：关键字regexp，例如，select * from school where address regexp ‘.xue’；
     (.)：匹配任意一个字符；
     (|)：相当于or匹配,‘100|200’匹配100或200；
     ([])：匹配其中之一， ‘[123]’匹配1或2或3，等价于 ‘[1|2|3]’, ‘[^123]’匹配除了这些字符外的任何东西，[012345]等价于[0-5],“-”表示范围；
     (\)：转义，为匹配特殊字符，必须在前面加上 ‘\’， ‘\.’匹配“.”；
     {n}：匹配n个指定字符内容， ‘(da){3}’匹配 ‘dadada’，不匹配 ‘daxuedada’；
     {n,}：匹配不少于n个指定字符内容；
     {n,m}：匹配至少n个，至多m个指定字符内容；
     (*)：匹配0个或多个，等价于{0,}；
     (+)：匹配1个或多个，等价于{1,}；
     (?)：匹配0个或1个，等价于{0,1}；
     (^)：匹配文本的开始；
     ($)：匹配文本的结尾；
16. like子句与正则表达式的区别：like匹配整个列，即使被匹配的文本在列值中出现，like也不会找到它，除非使用通配符；而regexp是在列值中进行匹配，如果被匹配的文本在列值中出现，regexp将会找到它，regexp可以配合使用定位符“^”和“$”实现like的功能。
17. 正则表达式测试：可以在不使用数据库的情况下用select来测试正则表达式，返回0表示不匹配，返回1表示匹配。例如，select ‘hello’ regexp ‘[0-9]’返回0。
18. 数据库自带函数：
     Concat()：拼接字符串，例如，select concat(schoolname,’-’,address) from school；
     Trim()：去除左右两边的空格，RTrim()只去除右边的空格，LTrim()只去除左边的空格；
     Lower()：将字符串转换为小写，例如，select Lower(‘ABc’)；
     Upper()：将字符串转换为大写，例如，select Upper(schoolname) from school；
     Now()：返回系统当前时间，例如，select Now()；
     CurDate()：返回当前日期，同理，CurTime()返回当前时间；
     Date()：返回日期时间的日期部分，例如，select Date(Now())。同理，Time()，Year()，Month()，Day()，Hour()，Minute()，Second()，DayOfWeek()函数都是返回日期时间的相应部分；
     DateDiff()：计算两个日期之差，例如，select DateDiff(Now(),’2015-12-10’)；
     AddDate()：增加一个日期，例如，select AddDate(Now(),90)；
     AddTime()：增加一个时间,例如，select AddTime(Now(),’8:00’)；
     Date_Add()：高灵活度的日期运算函数，例如，select Date_Add(Now(),Interval ‘1 1 10’day_minute)，详情请看help Date_Add；
     Rand()：返回一个随机数，select Rand()；
19. 列别名：使用as关键字，例如，select Now() as time；
20. 日期格式：mysql首选的日期格式为yyyy-mm-dd，也尽量使用这种格式，例如，select * from order where Date(order_date) = ‘2005-09-01’；
21. 聚集函数：运行在行组上，计算和返回单个值的函数；
     AVG()：返回某列的平均值，忽略列值为NULL的行；
     COUNT()：返回某列的行数。使用COUNT(column)进行计数，会忽略NULL值；使用COUNT(*)进行计数，包含NULL值；
     MAX()：返回某列的最大值，忽略列值为NULL的行；
     MIN()：返回某列的最小值，忽略列值为NULL的行；
     SUM()：返回某列值之和，忽略列值为NULL的行。
     DISTINCT：加上DISTINCT关键字，统计时会忽略多余的相同值，例如，select COUNT(DISTINCT schoolname) from school；
22. 数据分组：关键字group by ，having
     如果分组列中具有NULL值，则NULL将作为一个分组返回；
     Group by子句必须出现在where子句之后，order by子句之前；
     Having子句可以过滤分组，而where子句过滤的是行而不是分组，where并没有分组的概念，也可以理解为，where在数据分组前进行过滤，而having在数据分组后进行过滤；
     Group by子句中列出的每个列都必须是检索列或有效的表达式，但不能是聚集函数，如果在select中使用表达式，那么在group by子句中也必须指定相同的表达式，不能使用别名；
23. Select子句顺序：
     Select：要返回的列或表达式；
     From：从中检索数据的表；
     Where：行级过滤；
     Group by：分组说明；
     Having：组级过滤；
     Order by：输出排序顺序;
     Limit：要检索的行数；
24. 相关子查询：涉及外部查询的子查询，例如，select cust_name,cust_state,(select COUNT(*) from order where order.id = customer.id) as orders from customer，该子查询对检索出的每个客户都会执行一次；
25. 笛卡儿积：由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数，例如，select * from student,school；
26. 内部联结：基于两个表之间的相等测试，也称等值联结。例如，select * from student inner join school on student.schoolId = school.schoolId，等价于select * from student,school where student.schoolId = school.schoolId；
27. 自然联结：标准的联结如内部联结返回所有数据，甚至相同的列多次出现，自然联结就是排除多次出现，使每个列只返回一次；
28. 外部联结：联结包含了那些在相关表中没有关联行的行，
     左外联结：关键字left outer join on，以左边的表为准；
     右外联结：关键字right outer join on，以右边的表为准；
29. 组合查询：关键字union，
     union中的每个查询必须包含相同的列、表达式或聚焦函数，不过各个列不需要以相同的次序列出；
     列数据类型必须兼容：类型不必完全相同，但必须是数据库可以隐含转换的类型；
     重复的行会被自动取消，想要返回所有匹配行，需使用union all；
     只能使用一条order by子句，并且必须是在最后一条select语句之后；
30. 全文本搜索：关键字match()、against()，
     MyISAM引擎支持全文本搜索，InnoDB不支持；
     为了进行全文本搜索，必须索引被搜索的列，使用FULLTEXT索引，例如，create table course(id int not null auto_increment, courseName varchar(128), remark text, PRIMARY KEY(id), FULLTEXT(note_text))Engine=MyISAM；
     使用全文本搜索查询，select * from course where match(remark) against(‘english’)；
     布尔文本搜索：功能更强大的全文本搜索，不一定需要FULLTEXT索引，但查询速度缓慢。例如，select * from course where match(remark) against(‘chinese -english’ IN BOOLEAN MODE),搜索包含“chinese”但不包含“english”的remark；
31. 插入数据：关键字insert into，values，
     插入查询的结果：需要对应列的类型相兼容，例如，insert into user(username,address) select name,address from customer；
     可以使用关键字low_priority降低insert语句的执行优先级，如，insert low_priority into，同样适用于update和delete语句；
32. 更新数据：关键字update，set
     使用update语句更新多行，如果在更新这些行中有一行或多行出现错误，那么整个update操作都将被撤消，即错误发生前更新的所有行也会被恢复到原来的值。为即使发生错误，也继续进行更新，可使用关键字ignore，如，update ignore table；
33. 删除数据：关键字delete
     如果想删除表中所有的行，可使用truncate table语句，速度更快，truncate语句实际是删除原来的表然后重新创建一个表，而不是逐行删除表中的数据；
34. last_insert_id()：获取最后一个auto_increment值，例如，select last_insert_id()。
35. 外键不能跨引擎，即使用一个引擎的表不能引用使用另外一种引擎的表的外键。
36. 更新表结构：关键字alter table
     新增列：alter table school add column id int；
     删除列：alter table school drop column id；
     新增外键：alter table student add constraint fk_1 foreign key(schooled) references school(id)，外键引用的必须是主键；
     删除外键：alter table student drop foreign key fk_1；
37. 重命名表：关键字rename table，例如，rename table stu to student；
38. 视图：关键字view。视图本身不包含数据，只包含使用时动态检索数据的查询，即在添加或更改这些表中的数据时，视图将返回改变过的数据。
     简化复杂的sql操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节；
     保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限；
     更改数据格式和表示；
     视图不能索引，也不能有关联的触发器或默认值；
39. 视图操作：
     新建视图：关键字create view as，例如，create or replace view test as select * from school；
     删除视图：drop view test；
40. 存储过程：关键字 create procedure，begin end
     如果是使用mysql命令行创建procedure，首先需要修改命令结束符，默认的是“;”，使用delimiter ，修改为“”；
     新建：

create procedure test()         Begin            Select * from school;         End 

 删除：drop if exists procedure test，需要给出存储过程的名字，但不需要加括号；
 调用：关键字call，例如，call test()；
 信息：show procedure status，show create procedure test；
41. 游标：关键字cursor，mysql游标只能用于存储过程或函数。
 创建游标：关键字declare cursor for，例如，

Create procedure test()Begin  Declare cur_test() cursor for  Select * from school;End;

 打开与关闭：使用游标前需先打开游标，例如，open cur_test；使用完后，需关闭以释放资源，例如，close cur_test；
 使用游标：关键字fetch，例如，fetch cur_test into result；
42. 触发器：关键字trigger，只有delete、insert、update语句支持触发器，
 只有表支持触发器，视图不支持，临时表也不支持；
 每个表每个事件每次只允许一个触发器，因此，每个表最多支持6个触发器（每条insert、update和delete的之前和之后）。单一触发器不能与多个事件或多个表关联；
 新建：关键字create trigger before after insert on for each row，例如，

create trigger school_tri after insert on school for each row begin  insert into school_bak(schoolName,address) values(‘guanggong’,’wushan’);end

 删除：delete trigger test；
 显示：show triggers；
43. 事务处理：关键字start transaction，rollback，commit
 Innodb引擎支持事务，MyISAM引擎不支持；
 事务处理可以管理insert,update,delete语句，但不能回退create或drop操作。事务处理块中可以使用这两条语句，但如果执行回退，它们不会被撤销；
 使用事务：start transaction; delete from student; rollback；
 当commit或rollback语句执行后，事务会自动关闭，即后面的更改会隐含提交；
 部分回退：关键字savepoint，例如，savepoint delete_1; rollback to delete_1；保留点在事务处理完成（执行一条rollback或commit）后自动释放，自Mysql 5以来，也可以用release savepoint 明确了释放保留点；
 关闭自动提交：set autocommit=0；
44. 字符集：关键字character set
 显示：show variables like ‘character%’；show character set；
 设置：表级设置，例如，create table( )default character set utf-8；列级设置，例如，studentName varchar(128) character set gbk;
45. 用户管理：用户信息保存在mysql.user表中
 新建：create user admin@192.168.1.1 identified by ‘admin’；
 删除：drop user admin@192.168.1.1；
 改名：rename user admin@localhost to administrator@localhost；
 修改密码：密码必须使用password()函数加密。例如，修改自己的密码set password = password(‘admin’)；修改某个用户的密码set password for admin = password(‘admin’)；
46. 权限管理：关键字grant、revoke。
 授权：授权使用grant语句，至少要给出三方面信息：要授予的权限；被授予访问权限的数据库或表；用户名。例如，grant select on ywr.* to admin；
 撤消授权：revoke select on ywr.* from admin；
 查看权限：show grants for admin；
47. 数据备份：为了保证所有数据被写到磁盘，可能需要在进行备份前使用flush tables语句；
48. 数据库维护：关键字analyze、check、repair、optimize
 analyze table：检查表键是否正确；
 check table：检查表的多种问题；
 repair table：修复表，只对MyISAM表有用，但不应该经常使用；
 optimize table：如果从一个表中删除了大量数据，应该使用optimize table 回收所有的空间，优化表的性能；
49. 改善性能：
 mysql是一个多用户多线程的DBMS，可使用show processlist显示所有活动进程，可使用kill id终结某个进程；
 可使用explain语句让mysql解释它将如何执行一条select语句；