Mysql常用命令、简单查询、游标、存储过程、触发器简单介绍

一、Mysql数据库基本常用的命令
1、服务启动与停止
net stop mysql
net start mysql

2、登陆mysql
  mysql -uroot -p回车 输入密码
  注意，如果是连接到另外的机器上，则需要加入一个参数-h机器IP110.110.110.110，用户名为root,密码为abcd123。则键入以下命令：
  mysql -h110.110.110.110 -uroot -pabcd123
 

mysql -u root -p/mysql -h localhost -u root -p databaseName;

3、 显示数据库列表
  show databases;缺省有两个数据库：mysql和test。 mysql库存放着mysql的系统和用户权限信息，我们改密码和新增用户，实际上就是对这个库进行操作

4、选择数据库和显示数据表
   use mysql;
   show tables;

5、显示表结构
   desc tablename

6、建库、删库
    create database dbName；
    drop database   dbName；

7、基本的a建表、b插入信息、c增加字段、d修改字段名、e修改字段数据类型、f更新数据
   a.create table emp(id int not null auto_increment primary key,name varchar(50));
   b.insert into tableName values ("hyq","M");
   c.alter  table tableName add columnName type;
   d.alter table tableName1 change tableName2 char(10) not null;
   e.alter table tableName modify columnName  type
   f.update tableName set age="25" where id="";
   
8、清空表记录与删除表
   delete from  tableName
   drop table tableName;

9、导出导入数据
   导出数据和数据结构：输入：mysqldump -u [数据库用户名] -p [要备份的数据库名称]>[备份文件的保存路径]
   例子：mysqldump -u root -p test>E:\tt.sql
   导入：mysql -u root -p<[备份文件的保存路径]
    恢复备份文件：
    进入MYSQL Command Line Client
    先创建数据库：create database test 注：test是创建数据库的名称
    再切换到当前数据库：use test
    再输入：\. d:/test.sql 或 souce E:\tt.sql

10、退出mysql   exit 或快捷键：Ctrl+C；

二、Mysql查询

1、几个常用的函数
   a.count();统计
   b.sum();求和
   c.avg（）求平均
   d.max、min 最大和最小

2、几个高级查询运算
   a.UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。
   b.EXCEPT运算符  通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行
   c.INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复
 （使用以上运算符的几个查询结果必须一致）

3、几个常用的连接查询
   a.left outer join：左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c；
   b.右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。
   c. full outer join：全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。
   d.inner join：等值连接 只返回两个表中联结字段相等的行。

三、Mysql视图
   
1、概念：是一种虚表--不能在存放数据,只能使用select语句引用已存在表(一个或多个)的数据是一种代理模式的实现；

2、作用：使查询更清晰，视图存放我们所需要的数据，简化操作；让数据更安全，视图中的数据，不存在视图中，还是在基本表里面，通过视图这层关系，我们可以有效的保护我们的重要数据

3、MySql视图类型：mysql的视图有三种类型：MERGE、TEMPTABLE、UNDEFINED。如果没有ALGORITHM子句，默认算法是UNDEFINED（未定义的）。算法会影响MySQL处理视图的方式。
  a.MERGE，会将引用视图的语句的文本与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分。
  b.TEMPTABLE，视图的结果将被置于临时表中，然后使用它执行语句。
  c.UNDEFINED，MySQL将选择所要使用的算法。如果可能，它倾向于MERGE而不是TEMPTABLE，这是因为MERGE通常更有效，而且如果使用了临时表，视图是不可更新的。

4、基本创建：a.CREATE VIEW db_name.view_name AS SELECT * FROM t;（表和视图共享数据库中相同的名称空间，同一数据库中view名不能与表明相同。）
             b.CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
              VIEW view_name [(column_list)]
              AS select_statement
             [WITH [CASCADED | LOCAL] CHECK OPTION]

四、Mysql索引
   a.索引是双刃剑   EXPLAIN SELECT * from tablename   加上explain后用于描述MySQL如何执行查询操作、以及MySQL成功返回结果集需要执行的行数

五、存储过程（Mysql5之后才支持存储过程）
   1、概念：一组为了完成特殊功能的sql语句集，编译后放在数据库中，通过过程名调用执行；
   2、优点：a.增强sql灵活性与功能，能完成复杂的判断与运算；
            b.执行速度快，能有效节省流量（只传调用语句）；
            c.数据安全起很大作用.
   3、一个简单的例子
      delimiter//
      create procedure pro_test(OUT s int)
      begin
      select count(*) total INTO s from user;
      end
      //
      delimiter;
解释：以上创建了一个存储过程，有一个输出参数 s，将统计出来的记录数赋值给s；
      a.delimiter，分割符意思，MySQL默认以";"为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，
      则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符。
      b.三种参数类型：IN 输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值；
                      OUT 输出参数:该值可在存储过程内部被改变，并可返回；
                      INOUT 输入输出参数:调用时指定，并且可被改变和返回；
      c.变量的声明：declare age int default 18；
      d.变量的赋值：set 变量名=表达式值   
      e.用户变量： select “hello Kitty” into @X 、 声明后 select @X   的结果为 “hello Kitty”用户变量名一般以@开头滥用用户变量会导致程序难以理解及管理
  4、Mysql存储过程的调用
     call存储过程名（）；
  5、存储过程的查询
      select name frommysql.proc where db='dbName';
      show procedure status where db='dbName';
      SHOW CREATE PROCEDURE
 
  6、存储过程的修改、删除
     alter procedure;
     drop procedure;

六、触发器
   1、概念：当执行delete、update或insert操作时，可以使用触发器来触发某些操作。
   2、创建：CREATE TRIGGER trigger_name trigger_time trigger_event   ON tbl_name FOR EACH ROW trigger_stmt
   3、解释：其中 trigger_name是触发器名，
            trigger_time:BEFORE,AFTER
            trigger_event:INSERT、UPDATE、DELETE
            tbl_name:关联的表名
            注意，INSERT除了插入操作，load data也能激活该事件。对于同一trigger_event,不能有两个相同trigger_time的触发器。
            trigger_stmt：触发器被激活时执行的语句，可以使用单条语句，也可以使用BEGIN——END这样的复合语句。
   
  4、举例：mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
 
      mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
 
      -> FOR EACH ROW SET @sum = @sum + NEW.amount;
 
     mysql> SET @sum = 0;
     mysql> INSERT INTO account values(5,12.5);
      mysql> SELECT @num;
 
在该例子中，关键字NEW.col_name在INSERT触发程序中引用；
另外一个关键字OLD.col_name可用于DELETE中
NEW和OLD均可用于UPDATE触发程序中。
old命令的列为只读的，new命名的列，如果具有select权限，可引用它，如果在before出发程序中，具有update权限，可使用set new.col_name = value的方法，在插入前更改值

七、游标
  1、定义： DECLARE cursor_name CURSOR FOR SELECT_statement;    
  2、打开游标：open cursor_name;
  3、FETCH：获取游标当前指针的记录，并传给指定变量列表,注意变量数必须与MySQL游标返回的字段数一致,要获得多行数据，使用循环语句去执行FETCH
      FETCH cursor_name INTO variable list;
  4、关闭游标：close cursor_name；MySQL的游标是向前只读的,也就是说，你只能顺序地从开始往后读取结果集，不能从后往前，也不能直接跳到中间的记录.
  5、示例：
      -- 定义本地变量
    DECLARE o varchar(128);

   -- 定义游标
   DECLARE ordernumbers CURSOR
   FOR
   SELECT callee_name FROM account_tbl where acct_timeduration=10800;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments=1;
   SET no_more_departments=0;
   
   -- 打开游标
   OPEN ordernumbers;

   -- 循环所有的行
   REPEAT
       -- Get order number
      FETCH ordernumbers INTO o;
      update account set allMoney=allMoney+72,lastMonthConsume=lastMonthConsume-72 where NumTg=@o;
     
     
  -- 循环结束
      UNTIL no_more_departments
     END REPEAT;
   -- 关闭游标
   CLOSE ordernumbers;