mysql笔记

检索不同的行：select distinct vend_id name from video;其中distinct应用于所有的列，而不只是前置的列。

select语句为了返回第一行后者前几行：select vend_id from video limit 0,5;其中1表示从第一行开始，5表示总共五行。数据库系统中第一行为行0.还有limit 5 offset 0，含义一样。

order by子句：select url from video order by url;对选出的结果用url进行排序。采用非检索的列进行排序也是合法的，如果按照倒序从高到低进行排序则用desc，而desc只对第一个列进行降序，后序的列还是升序，如果想在多个列进行降序则每列前边都要加desc。

采用order by和limit组合可以选出每列的最高值或最低值，select url from video order by url desc limit 1;在使用order by之后应该保证他处于from之后，使用limit时应保证他处于order by之后，否则出错。

在同时使用order by和where语句时应同时使用.。

mysql中where子句可以使用如下操作符：

select id from video where id between 1 and 10;

select id from video where id is not null;

select id from video where id <> null;

数据过滤：

1、and和or等组成的多过滤条件，其中and要比or的优先级高，如果同时使用尽量加上括号避免歧义：

select prod_name prod_price from products where (id<1005 or id>2000) and prod_price<10;

2、in操作符后跟由括号包围且用逗号分开的合法值清单，整个清单必须包含在括号中。语句如下：

select prod_name prod_price from products where id in (1002,1003) order by prod_name;取出id为1002或1003的商品，其中in其实和or作用相同。in操作符执行速度比or快。

3、not操作符：用来否定其后跟条件

select prod_name prod_price from products where id not in (1002,1003) order by prod_name;

在mysql中允许用not对between、in、exists取反。

用通配符进行过滤

1、百分号通配符（%）匹配0-n个任意的字符，但是不匹配null：

select prod_name from products where prod_name like '%hero%';

2、下划线和%一样，但是只匹配一个字符

总结：通配符搜索要比其他花费更多的时间，应尽量用其他操作符代替；不要把通配符放在字符串开始处，那样会使得搜索很慢。

正则表达式

1、regexp：

select prod_name from products where prod_name regexp '1000' order by prod_name;选出名字中包含1000的商品名；

select prod_name‘ from products where prod_name regexp '.000' order by prod_name;其中.用来匹配一个字符。

2、正则表达式中的“或”，

select prod_name‘ from products where prod_name regexp '1000|2000|3000' order by prod_name;匹配1000、2000和3000

3、匹配几个字符之一：select prod_name‘ from products where prod_name regexp '[123]ton' order by prod_name;匹配包括1ton、2ton、3ton的字符串。匹配中也可以用否“^”如"[^123]ton"

4、匹配范围[1-9]ton，

5、特殊字符要用\\进行转义，如要匹配包含.的字符串，则用\\.

6、匹配多个实例：

例子：select prod_name from products where prod_name regexp '[[:digit:]]{4}' order by prod_name;匹配包含四个数字的名字；

select prod_name from products where prod_name regexp '\\([0-9] sticks?\\)'匹配(数字 stick)或者(数字 sticks)。

7、定位符：到目前为止匹配的字符串在文本的任意位置，为了匹配特定位置的文本，需要如下定位符：

例如查找数或点开头的所有产品：

select prod_name from products where prod_name regexp '^[0-9|\\.]' order by prod_name;

^有双重作用：再集合中（以[和]括住），则是用来否定条件，否则用来指串的开始处。正则表达式可以用^做开始$做结束，达到和like作用相同的目的。

可以使用不代表的select测试正则表达式，匹配返回1，否则返回0；例如select 'hello' regexp '^hello$';则会返回1。

创建计算字段

1、concat()；select concat(id,url) from video where id=1;

2、RTrim()，去掉值右边的空格，LTrim()去掉左边的空格，Trim()去掉空格

3、用as产生别名：select concat(id,Trim(url)) as name from video where id=1;

4、执行算数计算：select item1_price+item2_price as price from products where order_num=2005 order by id;

5、select now();返回当前时间

使用数据处理函数

1、文本处理函数 upper  select id,upper(url) from video where id=1;常用的文本处理函数如下：

其中soudex()比较的是发音，防止字母输入错误造成的查找错误，实例如下：

2、日期处理函数

mysql中年份总是使用4位数如：2016。但同时支持两位数。

select order_num,order_price where order_date='2016-12-19' order by order_price;

检索9月下的所有订单：

select order_id,order_price from orders where Date(order_time) between '2016-09-01' and '2016-09-30' order by order_price;

select order_id,order_price from orders where Year(order_time)=2016 and Month(order_time)=9 order by order_price;避免润月或不知道本月有多少天。

3、数值处理函数

汇总数据

1、count(*)不忽略null；而count(column_name)则忽略null值。

2、distinct只用于列而不能用于计算和表达式，select count(distince order_price) from orders;

数据库中的函数是高效的，比在客户端上实现效率高的多。

分组数据

1、having过滤分组 select cust_id,count(*) as orders group by cust_id having count(*)>=2;

where在分组之前进行过滤，having在分组之后进行过滤

2、具有两个以上的产品且价格在10以上的供应商

select vend_id,count(*) as nums from products where prod_price>=10 group by vend_id having count(*)>=2;

3、order by在group by中的使用

子查询的使用

1、orderitems订单所包含的商品，orders订单时间、订购人、订单号，customs客户信息。下面是题目：查询订购商品apple的客户信息

select cust_name,cust_age from customs where cust_id in(select cust_id from orders where order_id in (select order_id from orderitems where prod_name='apple')) 

2、查找每个客户的订单总数

select cust_name,(select count(*) from orders where orders.cust_id=customs.cust_id) as orders form customs order by cust_name;

联结表

1、关系表的设计就是要把信息分成多个表，一类数据一个表。各表通过某些常用的值相互联结。

现有若干供应商，每个供应商生产若干商品，则分别建立供应商和产品的表，其中venders中的唯一标识为products表的外键。

select vender_name,pro_name,pro_price from venders,products where venders.vender_id=products.vender_id order by vender_name,pro_name;

这样设计使得可伸缩性好：可以适应不断增加的工作量而不失败。设计良好的数据库或应用程序可伸缩性好。

2、采用外联结实现相同的功能

select vender_name , pro_name from venders inner join products on venders.vender_id=products.vender_id;

3、多表连接

挑选包含产品apple的所有订单

select cust_name,cust_address from customs,orders,orderitems where customs.cust_id=orders.cust_id and orderitems.num=orders.num and orderitems.name='apple',

创建高级联结

1、自联结：如果商品apple有问题，现想寻找该商品供应商生产的其他商品是否有问题

select prod_id,prod_name from products where vender_id=(select vender_id from products where prod_name='apple');

上述查询使用了子查询，其实有更快的自联结可以使用，如下：

select prod_id,prod_name from products as p1,products as p2 where p1.vender_name=p2.vender_name and p1.prod_name='apple';

2、检查所有用户及其订单

select customs.cust_id,orders.order_id from customs inner join orders on customs.cust_id=orders.cust_id;

3、检查所有用户及其订单（包含没有订单的用户），结果如下。

select customs.cust_id,orders.order_id from customs from customs left outter join orders on customs.cust_id=orders.cust_id;

4、检测所有客户及其所下订单个数：

select customs.cust_id,customs.name count(orders.id) from customs inner join orders on customs.cust_id=orders.cust_idgroup by customs.cust_id;

组合查询

1、创建组合查询

查询价格在3-5之间的商品，和供应商1001和1002的商品

方案1：采用组合查询：select vender_id,prod_id,prod_price from products where prod_price<=5 and prod_price >=3 union select vender_id,prod_id,prod_price from products where vender_id in (1001,1002);

方案2：采用复杂where的多条件查询 select vender_id,prod_id,prod_price from products where ( prod_price<=5 and prod_price>=3 ) or vender_id in ( 1001,1002);

对于复杂的过滤条件或对于从多个表中查询，使用union可能会使处理更简单

2、包含或取消重复行

正常情况下union是重复行取其一的，也可以全都包含进去（使用union all），效果是全都返回。

3、对组合查询进行排序(直接在后边加order by)

select vender_id,prod_id,prod_price from products where prod_price<=5 and prod_price >=3 union select vender_id,prod_id,prod_price from products where vender_id in (1001,1002) order by vender_id,prod_id,prod_price ;

全文本搜索(myisam支持全文本搜索)

1、mysql的引擎：myisam ( Indexed Sequential Access Method)和innodb，其中前者为默认的数据库引擎

2、启用全文本扫描支持

CREATE TABLE productnotes

(

        note_id    int     NOT  NULL   AUTO_INCREMENT ,

          pro_id      int     char(10)         NOT    NULL,

       note_date    datatime    NOT  NULL,

       note_text text NULL,

       PRIMARY   KEY(note_id),

       FULLTEXT(note_text),

)ENGINE=MyISAM;

3、进行全文搜索

SELECT    note_text     FROM    productnotes     WHERE    Match(note_text)     Against(  'rabit'  );

Match()中列出的列必须在FULLTEXT()中指出，如果列出多个列，必须次序正确。全文搜索具有较高等级的结果（匹配的词在前面）先出现

4、使用查询扩展：不仅找出包含某个词的结果，还可以找出与你搜索结果相关的其他行

SELECT   note_text  FROM    productnotes    WHERE     Match(  note_text  )  Against ( ‘rabbit’   WITH    QUERY  EXPANSION )   ;

5、布尔文本搜索

SELECT   note_text  FROM   productnotes    WHERE     Match(note_text)      Against(   'rabit   -rope*'  IN BOOLEAN     MODE);其中-rope*明确指出不包含rope字符串；

举例如下：

插入数据

1、插入完整的行

INSERT INTO  customs  VALUES( NULL,

'LIANG'

'WUST'

'WUHAN'

NULL,

NULL  );

更安全的方法是指出列名，优点是即使表的结构改变了，只要语句中列名和值一一对应，即可重复使用。如下：

INSERT INTO customs

(

        cust_name,

        cust_depart,

        cust_city,

        cust_state,

        cust_country)     VALUES( NULL,

        'LIANG'

         'WUST'

         'WUHAN'

         NULL,

         NULL  );

2、插入多个行

INSERT INTO customs

(

      cust_name,

      cust_depart,

      cust_city,

      cust_state,

      cust_country

)     VALUES( 

                           NULL,

                           'LIANG'

                           'WUST'

                           'WUHAN'

                           NULL,

                           NULL  

) , (

        NULL,

         'goudar',

          'WUST' ,

           'WUHAN' ,

           'NULL',

           'NULL'

);

3、插入检索出的数据（ 其中两次列名不用一样，只要类型匹配即可）

INSERT INTO customs(

          cust_name,

          cust_depart,

          cust_city,

          cust_state,

          cust_country

) 

SELECT cust_name , cust_depart , cust_city  , cust_state , cust_country     FROM customnew;

更新和删除数据

1、更新数据

          更新客户1006的电子邮件  UPDATE   customs SET   cust_emal='ele@qq.com'    WHERE  cust_id=1006;

          更新多个列： UPDATE   customs    SET    cust_emal='ele@qq.com'  ,cust_name='liang'   WHERE   cust_id=1006;

          正常的update执行过程中发生错误则任务会回滚，而如果加上IGNORE则发生错误不回滚，UPDATE    IGNORE   customs……

          删除列值时可将列值置为NULL，UPDATE   customs   SET    cust_name=NULL     WHERE    cust_id=1006;

2、删除整行

         DELETE   FROM    customs     WHERE    cust_id=1006;

3、采用delete可以删除表中所有数据，但是删除所有行更快的方法是TRANCATE   table，他先删除整张表再建新的。

创建表

1、建表    CREATE  TABLE   orders   ( order_id    int  NOT  NULL    AUTO_INCREMENT ,

                                                                     order_datetime   datetime    NOT  NULL,

                                                                     cust_id     int   NOT  NULL ,

                                                                      PRIMARY  KEY(order_id)

                                                                     )ENGINE=InnoDB;

      建表时如果不指定是否为空，则默认为NULL;

2、一个表只能有一个AUTO_INCREMENT，并且它必须被索引（如使它成为主键）

3、创建表列时指定默认值：

CREATE   TABLE    customs  (  order_num   NOT  NULL    AUTO_INCREMENT,

                                                         order_date   NOT   NULL    DEFAULT    '2016-12-1'    ,

                                                          primary  key(order_num)   )ENGINE=InnoDB;

4、外键不能跨引擎，否则会出现比较大的缺陷

5、更新表，采用ALTER

      增加列：ALTER     TABLE    venders    ADD      vender_phone     CHAR(20);

      删除列：ALTER     TABLE    venders    DROP   COLUMN     vender_phone;

6、ALTER    TABLE常见的用途是定义外键：

         ALTER     TABLE   orders    ADD   CONSTRAINT    fk_orders_customs     FOREIGN  KEY   (cust_id)   REFERENCE    orders(cust_id);

7、删除表： DROP   TABLE   customs;该语句没有确认，也没有撤销。执行过之后永久删除表。

8、重命名表  RENAME   TABLE   customs   TO   custom1s,

                                                           venders     TO   vender1s,

                                                           orders        TO   order1s;

使用视图

1、视图语法：

      创建视图：CREATE     VIEW      viewname;

      查看创建视图时的语句：SHOW    CREATE   VIEW    viewname;

      删除视图：DROP    VIEW      viewname；

      更新视图：可是DROP再CREATE，也可以直接CREATE     OR     REPLACE     VIEW;

2、利用视图简化复杂的联结：视图的作用之一是隐藏复杂的语句

       订购了任意产品的所有客户列表    CREATE    VIEW    product_customs    AS     SELECT   cust_id,   cust_contract  ,prod_id   FROM   customs,    orderitems,   orders   WHERE orders.cust_id = customs.cust_id      AND    orders.ord_num=orderitems.ord_num  ;

       订购了产品‘apple’的客户：SELECT   cust_id   ,   cust_contract    FROM   product_customs     WHERE   prod_id='apple';在mysql处理数据时将该where添加到视图的where中以便过滤数据。

使用存储过程（mysql5以后才支持）

1、执行存储过程CALL   product_call(@price_low,@peice_high,@price_average);执行名为product_call的存储过程。存储过程可以返回结果，也可以不返回结果。

2、创建存储过程

       注意分隔符，如果分隔符为；，则执行到下一个；就会结束，所以存储过程创建失败。

       DELIMITER  //

      CREATE    PROCEDURE     productpricing()   

                                                  BEGIN

                                                  SELECT   AVG(pro_price)    AS   price_average    FROM   products;

                                                  END;

      DELIMITER   ;

3、删除存储过程   DROP   PROCEDURE   productpricing;主义不带括号

4、DROP   PROCEDURE   IF   EXISTS

5、创建带参数的存储过程   CREATE    PROCEDURE    productpricing(out   price_low    DECIMAL(8,2) ,  OUT   price_high  DECIMAL(8,2),   OUT  price_avg    DECIMAL(8,2))

                                                                                                   BEGIN

                                                                                                   SELECT    MIN(pro_price)   INTO   price_low    FROM    products;

                                                                                                   SELECT    MAX(pro_price)   INTO   price_high   FROM   products;

                                                                                                   SELECT    AVG(pro_price)   INTO   price_avg    FROM    products;

                                                                                                    END;

     调用   CALL   PROCEDURE  productpricing(@ price_low,@price_high,@price_avg);

     然后   SELECT   @price_low   ,@price_high  ,@price_avg;即可将结果显示出来。

     创建存储过程使用IN和OUT参数   CREATE  PROCEURE    ordertotal( IN   order_num1   INT,  OUT   number   DECIMAL(8,2))

                                                                                                             BEGIN

                                                                                                              SELECT   count(*)   FROM   orderitems   WHERE   order_num=order_num1   INTO  number;

                                                                                                            END;计算出订单order_num1的商品个数，并返回给number。

    CALL  PROCEDURE  ordertotal(1002,@number);

    然后通过SELECE   @number;返回结果。

6、建立智能存储过程

触发器

1、触发器是响应INSERT  DELETE  UPDATE而自动执行的语句。尽量保持数据库中触发器名字唯一，但mysql中允许同一数据库中触发器同名，只要同一表中唯一即可。

2、CREATE   TRIGGER   tri  AFTER   INSERT  ON  products   FRO  EACH   ROW   SELECT   'product  added';

3、只有表才支持触发器，视图不支持，而且每个表的每个事件只支持一个触发器，每个表智能有六个触发器（UPDATE  DELETE  INSERT之前和之后）。如果BEFORE触发器失败，mysql则不执行剩下的语句和AFTER触发器。

4、删除触发器 DROP    TRIGGER   tri;触发器不能更新和覆盖，为了修改触发器必须先删除后创建。

5、INSERT触发器   CREATE  TRIGGER   tri   AFTER   INSERT   ON products  FOR    EACH  ROW   SELECT   NEW.pro_num;

     触发器的BEFORE用于数据的验证和净化。

6、利用OLD将将被删除的行保存在一个存档表中

      CREATE    TRIGGER   deleteorder   BEFORE   DELETE  ON   orders   FOR   EACH  ROW   

      BEGIN 

       INSERT  INTO  copy_orders  value(OLD.order_num  ,  OLD.order_date  ,  OLD.cust_id  );

     END;相对于AFTER来说，如果由于某种原因不能存档，DELETE本身将被放弃。

7、设计触发器，在更新行之前将州名改成大写  CREATE  TRIGGER   tri  BEFORE  UPDATE   customs  FOR  EACH  ROW  SET    NEW.state=UPPER(NEW.state);

8、管理事务处理

1、事物处理：保证成批的mysql操作要不完全执行，要不完全不执行。

2、ROLLBACK   例子如下：SELECT  *   FROM  products；

                                                   START  TRANSACTION;

                                                   DELETE    FROM   products;

                                                   SELECT  *   FROM    products;

                                                   ROLLBACK;

                                                   SELECT   *   FROM  products;

3、只有INSERT   UPDATE   DELETE可以回退。

4、COMMIT仅在不出错的时候完整执行事务

      START   TRANSACTION;

      DELETE   FROM   products;

      DELETE   FROM   customs;

      COMMIT;

5、保留点。如果执行不完整撤销则需要保留点

               创建保留点：SAVEPOINT   point；

               会退到保留点：ROLLBACK  TO  point;

全球化和本地化

1、字符集及其校对

管理用户

1、mysql数据库中有一个名为mysql的数据库，其中有一个user表，用来保存其上的用户。

2、创建用户账号

      CREATE   USER   ll  IDENTIFIED  BY   'b';创建用户ll，口令为b。

3、重命名用户账户  RENAME   USER  ll   TO  LL;

4、删除用户账户   DROP   USER  ll;

5、查看用户权限 SHOW   GRANTS  FOR   ll;

6、设置用户权限  GRANT   SELECT  ON   customs.*  TO  ll;

7、更改口令  SET  PASSWORD  FOR  ll =PASSWORD('B');