Mysql相关知识

1、sql执行顺序

  from、join、  on、  where、 group by、avg sum...、having、select、distinct、order by、limit

2、mysql搜索引擎

     2.1、myisam 如果对表的事物要求不高，同时是以查询和添加为主的，例如bbs中发帖表，回复表

     2.2、innodb对事物要求高，保持的数据都是重要的数据，例如订单、账户

     2.3、memory数据变化频繁，不需要入库同时又经常查询和修改

3、myisam与innodb的区别

     3.1、myisam批量插入速度快，innodb慢，myisam插入数据时不排序

     3.2、innodb支持事物，myisam不支持

     3.3、myisam支持全文索引

     3.4、myisam不支持外键，innodb支持外键

4、存储过程

     4.1、分页存储过程

CREATE PROCEDURE `pr_page`(in p_table_name varchar(2048),/*表名*/
in p_fields varchar(1024),/*查询字段*/
in p_page_size bigint , /*每页记录数*/
in p_page_now bigint,  /*当前页*/
in P_order_string varchar(128),/*排序条件(包含ORDER关键字,可为空)*/
in p_where_string varchar(3000),/*WHERE条件(包含WHERE关键字,可为空)*/   
out p_out_rows bigint     /*输出记录总数*/
)
    COMMENT '数据分页'
BEGIN

    DECLARE m_begin_row INT DEFAULT 0;

    DECLARE m_limit_string CHAR(64);

    DECLARE m_sqlwhere VARCHAR(3000);

    DECLARE m_fields VARCHAR(3000);

    SET m_begin_row = (p_page_now - 1) * p_page_size;

    SET m_limit_string = CONCAT(' LIMIT ', m_begin_row, ', ', p_page_size);

    SET m_sqlwhere=replace(replace(p_where_string,'|',''''),'$w$','where');  

    SET m_fields=replace(replace(p_fields,'|',''''),'$w$','where');  

    SET @COUNT_STRING = CONCAT('SELECT COUNT(1) INTO @ROWS_TOTAL FROM ', p_table_name, ' where 1=1 ', m_sqlwhere);

    SET @MAIN_STRING = CONCAT('SELECT ', m_fields, ' FROM ', p_table_name,' where 1=1 ' , m_sqlwhere ,' ', P_order_string , m_limit_string);
     /*预处理得到Rows 准备 执行 解除 设置值*/
    PREPARE count_stmt FROM @COUNT_STRING;
    EXECUTE count_stmt;
    DEALLOCATE PREPARE count_stmt;
    /*得到行数*/
    SET p_out_rows = @ROWS_TOTAL;
    PREPARE main_stmt FROM @MAIN_STRING;
    EXECUTE main_stmt;
    DEALLOCATE PREPARE main_stmt;
END

  4.2、相关内容

1.show procedure status

显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等

2.show create procedure sp_name

显示某一个存储过程的详细信息，查看存储过程或函数的创建代码；

3、查看当前自定义的存储过程

select specific_name from mysql.proc where db = 'your_db_name' and type = 'procedure'

  4.3、循环

    -- repeat   until循环

     CREATE PROCEDURE insertUserByRepeat()

     BEGIN

        DECLARE i int DEFAULT 0;

        REPEAT
          INSERT into t_user2(username,version) VALUES('aaa',i);

          set i = i+1;

          UNTIL i >=5

          END REPEAT;

         end

   -- loop     LEAVE语句的意思是离开循环，LEAVE的格式是：LEAVE 循环标号。

    CREATE PROCEDURE insertUserByloop()

    BEGIN

      DECLARE i int DEFAULT 0;

      loop_label : loop

            INSERT into t_user2(username,version) VALUES('aaa',i + 10);

            set i = i+1;

            if i >= 5 THEN

                LEAVE loop_label;

             END IF;

            end LOOP;

      END

     END

  --while循环插入

      CREATE DEFINER=`root`@`localhost` PROCEDURE `batchInsertUsert`(in n BIGINT)
      BEGIN
            DECLARE i int;
            set i = 200;
            WHILE i < n DO
               INSERT INTO t_user(username,password) VALUES(CONCAT('admin',i),'123456');
               set i = i +1;
            END WHILE;
      END

  --游标插入

     CREATE PROCEDURE insertUserByCursor()

     BEGIN

       DECLARE done int DEFAULT 0;/*是否达到记录的末尾控制变量*/

       DECLARE username1 VARCHAR(100);

       DECLARE version1 VARCHAR(100);

       DECLARE cur_1 CURSOR FOR SELECT username, VERSION  from t_user2;

       DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

       OPEN cur_1;

             FETCH cur_1 into username1,version1;/*获取第一条记录*/

             WHILE done <> 1 DO

                    INSERT into t_user(username,password) VALUES(username1,version1);

                    FETCH cur_1 into username1,version1;/*取下一条记录*/

             END WHILE;

       CLOSE cur_1;

       END

4.4、使用存储过程的好处:

         1、减少网络通信量，调用一个行数不多的存储过程与直接调用sql语句的网络通信量可能不会有很大的差别，可是如果存储过程包含上百行sql语句，那么其性能绝对比一条一条的调用sql语句要高很多。

         2、执行速度更快。有两个原因:首先，在存储过程创建的时候，数据库已经对其进行了一次解析和优化。其次，存储过程一旦执行，在内存中中就会保留一份这个存储过程，这样下次再执行相同的存储过程时，可以从内存中直接调用。

        3、更强的适应性:开发人员可以在不改动存储过程接口的情况下对数据库进行任何改动，而这些改动不会对应用程序造成影响。

       4、布式工作:应用程序和数据库的编码工作可以分别进行，而不会相互压制。

优点：

1、由于应用程序随着时间推移会不断更改，增加功能，T-SQL过程代码会变得更复杂，StroedProcedure为封装此代码提供了一个替换位置。

2、执行计划(存储过程在首次运行时将被编译，这将产生一个执行计划，实际上是Microsoft SQL Server为在存储过程中获取由T-SQL指定的结果而必须采取的步骤的记录。)缓存改善性能。

3、存储过程可以用于降低网络流量，存储过程代码直接存储于数据库，所以不会产生大量T-SQL语句的代码流量。

4、使用存储过程使您能够增强对执行计划的重复使用，由此可以通过使用远程过程调用（RPC）处理服务器上存储过程而提高性能。RPC封装参数和调用服务器端过程的方式是引擎能够轻松地找到匹配的执行计划，并只需插入更新的参数值。

5、可维护性高，更新存储过程通常比更改、测试以及重新部署程序集需要较少的时间和精力。

6、代码精简一致，一个存储过程可以用于应用程序代码的不同位置。

7、更好的版本控制，通过使用Microsoft Visual Sourcesafe或某个其他源代码控制工具，您可以轻松地恢复或引用旧版本的存储过程。

8、增强安全性:

  8.1、通过向用户授权对存储过程(而不是基于表)的访问权限，它们可以提供对特定数据的访问

  8.2、提高代码安全，防止sql注入

  8.3、sqlparameter类指定存储过程参数的数据类型，作为深层次防御性策略的一部分，可以验证用户提供的值类型(但也不是万无一失，还是应该传递至数据库前得到附加验证)

缺点:

1、如果更改范围大到需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，则您还需要更新程序集中的代码以添加参数、更新GetValue()调用等等这时候估计比较繁琐了。

2、可移植性差

   由于存储过程将应用程序绑定到SQL Server，因此使用存储过程封装业务逻辑将限制应该程序的可移植性。如果应用程序的可移植性在您的环境中非常重要，则将业务逻辑封装在不特定于RDMS的中间层中可能是一个更佳的选择。

区别一: 存储过程保存在数据库里面，存储过程可以被连接此数据库的所以程序设计语言和程序使用，自定义函数不能。

区别二: 存储过程可以有数据库管理软件修改，使得多层结构程序调整系统逻辑时，并不需要编译和分发程序。

区别三: 存储过程执行中不会引起网络流量，不占用程序服务器的内存和CPU资源。

5、触发器

触发器是一种特殊的存储过程，它在插入，删除或修改特定表中的数据时触发执行，它比数据库本身标准的功能有更精细和更复杂的数据控制能力。

数据库触发器有以下的作用：

1.安全性。可以基于数据库的值使用户具有操作数据库的某种权利。

  # 可以基于时间限制用户的操作，例如不允许下班后和节假日修改数据库数据。

  # 可以基于数据库中的数据限制用户的操作，例如不允许股票的价格的升幅一次超过10%。

2.审计。可以跟踪用户对数据库的操作。   

  # 审计用户操作数据库的语句。

  # 把用户对数据库的更新写入审计表。

3.实现复杂的数据完整性规则

  # 实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。与规则不同，触发器可以引用列或数据库对象。例如，触发器可回退任何企图吃进超过自己保证金的期货。

  # 提供可变的缺省值。

4.实现复杂的非标准的数据库相关完整性规则。触发器可以对数据库中相关的表进行连环更新。例如，在auths表author_code列上的删除触发器可导致相应删除在其它表中的与之匹配的行。

  # 在修改或删除时级联修改或删除其它表中的与之匹配的行。

  # 在修改或删除时把其它表中的与之匹配的行设成NULL值。

  # 在修改或删除时把其它表中的与之匹配的行级联设成缺省值。

  # 触发器能够拒绝或回退那些破坏相关完整性的变化，取消试图进行数据更新的事务。当插入一个与其主健不匹配的外部键时，这种触发器会起作用。例如，可以在books.author_code 列上生成一个插入触发器，如果新值与auths.author_code列中的某值不匹配时，插入被回退。

5.同步实时地复制表中的数据。

6.自动计算数据值，如果数据的值达到了一定的要求，则进行特定的处理。例如，如果公司的帐号上的资金低于5万元则立即给财务人员发送警告数据。

7.下面的触发器是统计用户数量的

CREATE TABLE `t_user` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT NULL,
  `password` varchar(50) DEFAULT NULL,
  `status` int(2) DEFAULT NULL,
  `birth` datetime DEFAULT NULL,
  `idcard` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniqueusername` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=49914 DEFAULT CHARSET=utf8;

CREATE TRIGGER `t1` AFTER INSERT ON `t_user` FOR EACH ROW BEGIN
DECLARE i int;
SELECT sum into i from t_total WHERE tablename = 't_user';
set i = i+1;
UPDATE t_total set sum = i WHERE tablename = 't_user';
end;

CREATE TRIGGER `t2` AFTER DELETE ON `t_user` FOR EACH ROW BEGIN
DECLARE i int;
SELECT sum INTO i from t_total WHERE tablename = 't_user'  ;
set i = i-1;
IF i < 0 THEN  set i =0;
end IF;
UPDATE t_total set sum=i where tablename = 't_user'  ;

END;

CREATE TABLE `t_total` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tablename` varchar(50) NOT NULL,
  `sum` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- 下面是错误的哦

CREATE TRIGGER t10 AFTER INSERT  ON t_login_log FOR
 EACH ROW
BEGIN
UPDATE t_user set lastLoginTime = NEW.loginTime,status = OLD.status + 1 WHERE id = NEW.userId;
END

注意:对于INSERT语句,只有NEW是合法的；对于DELETE语句，只有OLD才合法；而UPDATE语句可以在和NEW以及 OLD同时使用。

5、delete、truncate、drop区别

      5.1、truncate和delete只删除数据不删除表的结构

               drop语句将删除表的结构被依赖的约束，触发器、索引；依赖该表的存储过程/函数将保留，但是变为invalid状态

      5.2、delete语句是dml，这个操作会放到rollback segment中，事物提交之后才生效；如果有相应的trigger执行的时候将被触发。

                truncate、drop是ddl，操作立即生效，原数据不放到rollback segment中，不能回滚，操作不触发trigger。

      5.3、delete语句不影响表锁占用的extent，高水线保持原位置不动，显然drop语句将表所占用的空间全部释放，truncate语句缺省情况下见空间释放到minextents个

                extent,除非使用reuse storage;truncate会将高水线复位

      5.4、速度  drop > truncate > delete

      5.5、安全性:小心使用drop和truncate，尤其没有设备的时候

6、mysql的distinct用法-mysql中如何筛选出非重复的数据

CREATE TABLE `t_repayment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `productId` int(11) DEFAULT NULL,
  `repayDate` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

INSERT INTO `wl`.`t_repayment` (`id`, `productId`, `repayDate`) VALUES ('1', '1', '2016-11-02');
INSERT INTO `wl`.`t_repayment` (`id`, `productId`, `repayDate`) VALUES ('2', '2', '2016-11-04');
INSERT INTO `wl`.`t_repayment` (`id`, `productId`, `repayDate`) VALUES ('3', '2', '2016-11-04');

6.1、 SELECT * FROM `t_repayment` GROUP BY productId;

1              1        2016-11-02
2              2        2016-11-04

6.2、SELECT distinct productId,id,repayDate FROM t_repayment;

1              1        2016-11-02
2              2        2016-11-04

2              3        2016-11-04

6.3、SELECT distinct productId,repayDate FROM t_repayment;（注意:没有查询主键）
1                    2016-11-02
2                    2016-11-04

6.4、SELECT *,COUNT(distinct productId) as num FROM t_repayment GROUP BY productId;

1              1        2016-11-02        1
2              2        2016-11-04        1

7、方法创建

CREATE FUNCTION t_f_add (a INT, b INT) RETURNS INT
BEGIN
    DECLARE c INT;
SET c = a + b;http://write.blog.csdn.net/postedit/52589619
RETURN c;
END;

CREATE FUNCTION t_f_str1 (a INT, title VARCHAR(50)) RETURNS VARCHAR(50)
BEGIN
    DECLARE c VARCHAR(50);
  IF ISNULL(title) OR title = '' THEN
     set c = CONCAT('admin',a);
  ELSE
     SET c = CONCAT(title,a);
  END IF;
RETURN(c);
END;

8、大小写

      Linux下mysql安装完成后是默认:区分表名的大小写，不区分列名的大小写。

     用root账户登录后，在/etc/my.cnf中[mysqld]后面添加lower_case_table_names = 1

     0:区分大小写，1:不区分大小写。

     mysql在linux下数据库名、表名、列名、别名大小写规则是这样的:

     1、数据库名与表名严格区分大小写；

      2、表的别名是严格区分大小写

      3、列名与列的别名在所有的情况下均是忽略大小写

      4、变量名也是严格区分大小写

mysql在windows下都不区分大小写。

     如果现在查询时区分字段值的大小写，则:字段值需要设置binary属性，

A、创建时设置： 
CREATE TABLE T( 
A VARCHAR(10) BINARY 
); 

B、使用alter修改： 
ALTER TABLE `tablename` MODIFY COLUMN `cloname` VARCHAR(45) BINARY; 

C、mysql table editor中直接勾选BINARY项。

一、启动方式
1、使用linux命令service 启动：
service mysqld start
2、使用 mysqld 脚本启动：
/etc/inint.d/mysqld start
3、使用 safe_mysqld 启动：
safe_mysqld&

二、停止
1、使用 service 启动：
service mysqld stop
2、使用 mysqld 脚本启动：
/etc/inint.d/mysqld stop
3、 mysqladmin shutdown

三、重启
1、使用 service 启动：
service mysqld restart
2、使用 mysqld 脚本启动：
/etc/inint.d/mysqld restart

备注：查看mysql端口是否已经使用，使用netstat -anp 命令查看服务器端口使用情况。