Mysql数据库操作

#数据库的操作

        

         //创建数据库

          create  database  if not  exists  school;  //如果school不存在则创建数据库

        //使用数据库

         use school;  //如果school不存在则创建数据库

       //删除数据库

       drop database  school;  //如果school不存在则创建数据库

     

 

#数据表的操作

            //创建表

             create table  student(

              `studentNo`   int(4)  not  null  auto_increment,

              `studentName`  varchar(20)  not null,

               `sex`  char(2)  default  '男' not null,

               `age`  int(3) unsigned  zerofill,

               `borndate`  datetime comment'出生日期',

                 primary key(studentNo)

               )engine=innodb charset=utf-8

             //修改表名

             alter table student rename as stu;

             //添加字段

             alter  table stu  add phone varchar(11);

            //修改字段属性

            alter table stu modify phone char(11) not null;

           //可同时修改字段名和列属性

           alter table stu change phone telphone varchar(11);

          //删除字段

          alter table stu  drop  telphone;

          //显示表结构

          desc  stu;

          //显示表创建语句

           show  create table stu;

          //删除表

          drop  table  stu;

#数据的操作

 

         //插入数据

        插入单行数据    insert   into  tb(字段1,字段2,...)  values(值1,值2)

        插入多行数据    insert   into  tb(字段1,字段2,...)  values(值1,值2),(值1,值2),(值1,值2)...

      

        //添加外键约束

          alter  table student  add  constraint  fk_gradeid    foreign key(g1)   references  grade(g2)

                   student  :从表表明    fk_gradeid:主从键名          g1: grade：从表字段名          g2；主表字段名  

                   //删除数据,有主从关系的表先删从表，再删主表,添加时先添加主表再从表
                    设置为主键的表为主表
                    设置为从键的表为从表

        //删除外键

          alter  table  student  drop  foreign  key  fk_GradeID  

                   fk_GradeID:主从键之间的关系名

        //删除数据

        delete from  tb  [where  条件]  

 

        删除整张表的数据下次插入此表的数据的主键值从之前删除时的主键值开始  delete from  tb  

        删除整张表的数据下次插入此表的数据的主键值从1开始  truncate   table  student  

       //修改数据

          update   tb  set  字段1=值1,字段2=值2.。。。[where   条件]

 

       //查看数据

         select[distinct]  字段,字段  from  tb

         [where  条件]

         [group  by  字段]

         [having  条件]

         [order by  字段 desc/asc]

         [limit 0,5]

 

     复制表

         create  table  tb(select   *  from    student)

 

    //函数

     select abs(-8);  获取绝对值

     select ceiling(9.3)   向上取整数

     select floor(9.3)  向下取整

     select   rand();  0-1随机数

     select   sign(10); 符号函数 整数返回1,负数-1,0返回0

     select char_length('好好学习');  返回字符数

    select concat('你','好我是')  合并字符串

     select insert('你好我是谁',1,3','不知道');    向指定位置插入字符串 下标从1开始    返回结果:不知道是谁

     select lower('helLo');  转小写

     select upper('hello');  转大写
     select  left('北京欢迎你',3);  从左边截取几个字符  返回结果:北京欢

     select replace('北京欢迎你','北','南');  替换字符串

     select substr('我爱rap',2,3)  从某个位置截取某个长度  下标从1开始 返回结果：爱ra

    select  reverse('我爱rap');  反转   返回结果:par爱我

     获取当前时间

     select current_date();  select  curdate();

      获取日期和时间

     select now();          select curdate();     

    分别获取日期中的某个部分

    select   year('1998-02-16');    select month(now());   select  day(now())  select hour(now()) select second(now())

 

select  datediff(now(),'2018-8-8');    返回两个日期之间相隔的天数

select adddate(now(),5)    计算某个日期加上5天后的日期

exists 判断时候存在此表   not exists 

inner   join   on  内连接

left      join  on  左外连接

right   join  on  右外连接

                        自连接

#事务的基本流程

 #关闭自动提交
   SET autocommit=0;
   
   #开启事务
   START TRANSACTION;
   
   #需要执行的sql语句
   UPDATE account SET cash=cash-500 WHERE id=1;
   UPDATE account SET cash=cash+500 WHERE id=2;

   #根据具体情况提交或者回滚

   #提交
   COMMIT;
   
   #回滚
   ROLLBACK;
   
   #开启自动提交
   SET autocommit=1;    
    

 

#索引

 索引的作用:提高数据库的检索速度，改善数据库性能
     索引的分类:
          唯一索引：不允许有重复的键值，它的列允许有空值
          主键索引：是唯一索引的特殊类型，他的每个值是非空，唯一
          普通索引：只为那些最常出现在查询条件（where）
                    或排序条件（order by）的数据列创建索引
                    它的列允许插入重复值和空值
          复合索引：将多个列组合作为索引
          全文索引：用于在大量文本文字中搜索字符串，
                    它的效率大大高于sql的like关键字的效率
          空间索引
    索引增删改查：
          显示索引
            SHOW INDEX FROM test；
          添加索引
            ALTER TABLE test ADD PRIMARY KEY(id);  #主键
        ALTER TABLE test ADD UNIQUE KEY(testNo);#唯一键
        ALTER TABLE test ADD INDEX index_cd(c,d); #常规索引
        ALTER TABLE test ADD FULLTEXT(e);   #全文索引
           删除索引
             DROP INDEX [索引名] ON 表名
             ALTER TABLE 表名 DROP INDEX [索引名]                          
           删除主键索引
             ALTER TABLE 表名 DROP PRIMARY KEY

#视图

             #创建视图
              CREATE VIEW 视图名称 AS（select语句）
            #删除视图
               drop view[if exists] 视图名称
            #查询视图
                 select * from 视图名称[where]

#储存过程

 

#数据库设计

    软件项目开发周期中数据库设计步骤
             需求分析阶段：分析客户的业务和数据处理需求
                      收集信息
                      标识实体 （Entity）
                      标识每个实体的属性（Attribute）
                      标识实体之间的关系（Relationship）
         概要设计阶段：设计数据库的E-R模型图
                      E-R图
                      E-R图转化为关系模式（R(U)）
                      E-R图转化为数据库模型图
         详细设计阶段：应用三大范式审核数据库结构 
                     1NF:保证每列数据的原子性
                     2NF：保证每张表只做一件事
                     3NF：保证其他列与主键是直接关系           
         代码编写阶段：物理实现数据库，编码实现应用
         软件测试阶段
         安装部署
20、权限控制
        GRANT ALL ON bankDB.* TO 'bankMaster';
    #方式二，创建用户同时授权
    GRANT ALL ON bankDB.* TO 'bankMaster'@'localhost' IDENTIFIED BY '1234'
    GRANT INSERT,SELECT ON Myschool.stutab TO 'bankMaster'@'localhost' IDENTIFIED 
    #删除用户
    DROP USER 'bankMaster'@'localhost'
    #修改当前登录密码
    SET PASSWORD=PASSWORD('0000');
    #修改其他用户的密码
    SET PASSWORD FOR 'bankMaster'@'localhost'=PASSWORD('8888');

Mysql root用户权限恢复或忘记密码
      先停止mysql服务
      运行CMD
进入Mysql Bin目录

      1.执行 mysqld -nt --skip-grant-tables?（以不检查权限的方式启动）

      2.重新开打一个命令提示符的窗口(CMD)用空密码方式使用root用户登录MySQL；
             mysql -u root 
      3.执行SQL
             恢复root权限
              UPDATE USER SET select_priv='Y',Insert_priv='Y',
                         Update_priv='Y',Delete_priv='Y',
            Create_priv='Y',Drop_priv='Y',
            Reload_priv='Y',File_priv='Y',
            Grant_priv='Y',References_priv='Y',
            Index_priv='Y',Alter_priv='Y',
            Show_db_priv='Y',Super_priv='Y',
            Create_tmp_table_priv='Y',Lock_tables_priv='Y',
            Execute_priv='Y',Repl_slave_priv='Y',
            Repl_client_priv='Y',Create_view_priv='Y',
            Show_view_priv='Y',Create_routine_priv='Y',
            Alter_routine_priv='Y',Create_user_priv='Y',
            Event_priv='Y',Trigger_priv='Y',
            Create_tablespace_priv='Y'WHERE user='root';
              若是忘记密码
                 update mysql.user set password=PASSWORD('password') where User='root';
      4. mysql> flush privileges;(刷新权限)
         mysql> quit (退出)
      5.重新Mysql服务，即可。

 

Mysql中视图和储存过程和函数的区别:      来源：https://www.jianshu.com/p/507fcd1d7b47

表：

• 表是真正的存在与数据库中（也就是硬件介质上）的数据组合

视图：

• 是一张虚拟的表，其内容由查询定义；简单的说视图就是由select结果集组成的表。每次访问视图的时候，系统会自动根据视图的规则去组织筛选数据

存储过程&视图

• 相比之下存储过程范围更广，可以提供参数、输入参数 以及每次返回多个表数据 而视图一次只能返回一个二维表的数据 且不能接受 任何参数

存储过程&自定义函数：

• 存储过程可以返回参数，而函数只能返回值或者表对象
• 函数限制比较多，比如不能用临时表，只能用表变量．还有一些函数都不可用等
• 存储过程可以实现很复杂的业务逻辑；而自定义函数实现的功能针对性比较强，只完成查询的工作，可接受输入参数并返回一个结果，不能在函数中使用insert,update,delete,create等语句；
• 存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用。
• 存储过程可以调用存储函数。但函数不能调用存储过程