《MySQL必知必会》学习笔记

 

1.Select distinct 列名 from 表名； 其中distinct关键字可用于筛选掉重复的项，只显示一次，如几行里有相同的号码就只显示一行。

2.子语句顺序：

某语句必须在特定语句的前面或后面，顺序不对就会报错。

3.where语句每增加一个附加选项，就要加一个and，如where x<10 and y>5 and z=2,且where中的and优先级比or高，如果需要优先满足or需要加括号，如where (x<10 or y<10) and z=10。

4.not关键字在MySQL中仅局限于与in、between和exists子句搭配在一起取反，与其他数据库可以将not搭配各种条件取反有很大差别。

5.在where子句中的like用通配符进行匹配时，%表示匹配零至多个任何字符，_表示只匹配一个字符，如果需要不包含某字符，可以使用not like。 

6.通配符的性能比较慢，所以能用其他语句代替时尽量不用通配符，且除非有必要，否则不要把通配符放在搜索条件的开始处，这样速度是最慢的。

7.在where子句中like和regexp区别：like需要完全匹配整个列的列值，如果需要匹配的文字包含在一个列的值中，而不是整个值完全精确匹配，会找不到这个值，除非在匹配条件中加上%X或者_X等(X为想要匹配的值)。而regexp只需要匹配值在列值中出现被包含，就能找到，相当于需要完全精确查询和模糊查询的区别。

8.regexp正则表达式不区分大小写，若要区分可以加上binary关键字，如where X regexp binary 'JetPack';。

9.多数正则表达式使用一个反斜杠“\”来转义其他字符，但是MySQL需要两个反斜杠“\\”，因为MySQL自己解析一个，正则表达式解析另一个。

10.大多数数据库使用+或者||来拼接两列的值并显示，但是MySQL使用select Concat(列名，列名，…) from来实现。

11.Concat()等函数计算后的结果只是个值，没有列名，如果也想给计算结果起个别名(alias)，可以使用as，如：

select Concat(RTrim(vend_name),RTrim(vend_country)) as vend_title from...

12.可以通过近似读音函数Soundex()来查询出原本写错个别字符的数据项，如：

select cust_name,cust_contact from customers where Soundex(cust_contact)=Soundex('Y Lie');

13.如果查询涉及到时间，不要直接where cust_time='2005-09-10'，而是where Date(cust_time)=，这样可以搜到数据中包含该日期的条目，就避免了万一数据不仅仅有日期还有时间的情况，前者是完全严格匹配某个日期，可能会搜不到不仅仅有该日期还有时间的条目。

14.不允许使用COUNT(DISTINCT)，而且distinct内必须使用列名，不能用于计算或表达式。

15.with rollup用于将group by之后的值再进行聚合，例如group by列出了每个vend_id下的产品总数量，用with rollup会显示出所有vend_id中全部产品数量的总和。但是与order by语句互斥，不能同时使用，limit语句在with rollup后面。

16.在分组group by后面不能使用where，因为前者针对分组过滤，而where针对行过滤，如果想要在group by的后面使用类似于where的分组筛选语句，可以使用having，如：

select cust_id,COUNT(*) as orders from orders group by cust_id having COUNT(*)>=2;

但是where可以加在group by前面。

17.使用聚合函数如SUM()之后必须使用group by，否则会报错。

18.当两张表具有外键互相关联的关系，可以使用内连接语句来一次查询在两张表上的不同列，例如：

select vend_name,prod_name,prod_price from vendors inner join products on vendors.vend_id=products.vend_id;

19.如果知道一张表内，某个产品ID为“DTNTR”，但是不知道该产品的制造商ID(制造商ID在同一张表内)，但又想知道该制造商有哪些其他产品，可以有子语句或者自联结两种方法：

select prod_id,prod_name from products where vend_id=(select vend_id from products where prod_id='DTNTR')；

select p1.prod_id,p1.prod_name from products as p1,products as p2 where p1.vend_id=p2.vend_id and p2.prod_id='DTNTR';

其中联结比子语句性能高。 

20.左外部连接left outer join会将左表的所有行列出，不管是否每行都能匹配到右表的每一行，所以可能会出现右表的某几行数据是null或0，比如原本inner join不会显示出没有订单的客户，但是left outer join会显示出所有客户，即使某个客户没有订单: 

21.union可以组合两条select查询为一组结果返回，但是两条select语句查询的列需要相同，可以组合查询不同的表。使用order by对组合查询结果排序时，只能使用一次，且放在select语句末，作用于前面所有联合的查询。如:

22.可以在where后面加上Match(列) Against(包含在全文中的词)来进行全文(fulltext)查询，同时Against括号内可以加上in boolean mode来进行多种条件查询，如:

23.使用insert into 表名插入数据时，为了顾及同时另一个用户查询数据的性能，可以降低插入语句的优先级，加入low_priority语句，如insert low_priority into customers values...。为了增强同时插入多条语句的性能，可以列出列名，在一条insert语句中插入多条数据，例如:

24.将另一张表的数据导入到某个表，可以使用insert into 表名…… select from 表名。例如：

25.可以使用update 表名 set 值 where 条件来更新表中数据，如：

26.可以使用delete from 表名 where 条件来删除表中某行，如：

如果是删除列，需要使用alter table 表名 drop column 列名 语句，如果是清空某个表，可以使用truncate table 表名。

27.如果不确定要创建的表是否存在，可以将if not exists语句加在建表语句中，例如：

28.可以通过select last_insert_id()函数获得auto_increment的数值：

29.在建表时，创建列时MySQL可以设置默认值，如quantity int NOT NULL DEFAULT 1,但是default关键字后面只支持常量，不支持其他数据库所支持的函数默认值。

30.外键foreign key不能跨引擎（如InnoDB和MyISAM），使用一个引擎的表不能引用具有另一个引擎的表的外键。两种引擎的详细差别：https://www.jianshu.com/p/a957b18ba40d

31.关于使用alter table语句对表结构进行修改：

32.使用rename table 表名 to 表名来重命名表：

33.创建视图可以使原本复杂的查询语句化为简单基本的查询语句，相当于从一至几张表中选取需要的列重新组合成一个新的表，也可以通过视图控制不同权限的用户有权看到的内容。例如：

34.在创建存储过程时，因为分号“;”会被作为命令结束符号。然而存储过程中涉及多条语句，很可能出现多个分号，所以会导致存储过程的脚本写到分号的时候就中断报错，可以用delimiter命令先把结束符替换成别的符号，再换回来，例如：

同时，也可以用drop procedure if exists 存储过程名 来删除存储过程。如果需要执行存储过程，使用call 存储过程名（@参数1，@参数2，…）来执行，例如call productpricing()。传入参数onumber的例子如下：

35.与大多数数据库不同，MySQL的游标只支持存储过程和函数。在存储过程中变量和游标的定义是有顺序的，如下所示：

36.使用create trigger 触发器名 before/after insert/delete/update on 表名 for eachrow语句可以在表的任一行发生变动时自动触发特定操作，如：

37.为了防止某些数据被误删，可以利用start transaction命令开启事务处理，当需要返回时使用回退命令rollback，从start transaction开始的所有指令都会撤销，例如：

38.使用grant 权限 on 数据库名.表名/* to 用户可以对用户授予针对某个表或某个库的某个权限，revoke 权限 on 数据库名.表名/* from 用户可以撤回授予的权限，例如：

39.对用户设置密码，可以使用set password for 用户 =’密码’，如果省略for 用户，则对当前用户设置密码。

40复习：最重要的语法select语句的查询顺序：