首页 > 代码库 > my sql(二)

my sql(二)

1.数据约束

  a.默认值约束

    默认值(default)

     CREATE TABLE test(

         NAME VARCHAR(20),

         gender VARCHAR(2) DEFAULT ‘

        )

    注:当前没有插入默认值字段的时候,默认值才会起作用

 b.非空约束

    非空(not null)

      CREATE TABLE test(

          NAME VARCHAR(20) NOT NULL,

          gender VARCHAR(2)

         )

     注:不能不插入值,不能插入null

 c.唯一约束

    唯一(unique)

    CREATE TABLE test(

         idINT UNIQUE,

         NAMEVARCHAR(20)

    )

    注:不能插入重复的值,唯一约束不能约束null(可以插入多个null)

 d.主键约束

    唯一+非空(primary key)

   CREATE TABLE test(

         idINT PRIMARY KEY,

         NAMEVARCHAR(20)

    )

 e.自增长约束

    CREATE TABLE test(

         idINT PRIMARY KEY AUTO_INCREMENT,

         NAMEVARCHAR(20)

       )

      注:初始值为0,每次递增1

 f.外键约束

   外键作用:约束两种表的数据

    -- 部门表

    CREATE TABLE dept(

         idINT PRIMARY KEY AUTO_INCREMENT,

         NAMEVARCHAR(20)

     )

    -- 雇员表

    CREATE TABLE employee(

         idINT PRIMARY KEY AUTO_INCREMENT,

         NAMEVARCHAR(20),

         deptIdINT,

         CONSTRAINT employee_dept_fk FOREIGN KEY(deptId) REFERENCESdept(id)

         --                       外键名称                                         外键字段  参考         

     )

    注:有了外键约束之后,当往副表中插入,修改主表不存在的数据时外键就会起作用,当删除与副表  有关的主表数据时,外键起作用。当插入和修改数据时,应先向主表中添加;当删除数据时应该先删除    副表中的数据。

2.级联

   介绍:当有了外键的时候,我们希望修改或删除数据的时候,修改或删除了主表的数据,同时能够影响   副表的数据,这时就可以使用级联。

  CREATE TABLE employee(

         idINT PRIMARY KEY AUTO_INCREMENT,

         NAMEVARCHAR(20),

         deptIdINT,

         --添加级联修改 ON UPDATE CASCADE

         --添加级联删除: ON DELETE CASCADE

         CONSTRAINTemployee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) ON UPDATE       CASCADE ON DELETE CASCADE

         --                             外键名称                     外键字段  参考         

          )

3.数据库设计的三大范式    

    第一范式:要求表的每个字段必须独立的不可分割的单元。

   第二范式:在第一范式的基础上,要求表的除主键以外的字段都和主键有依赖关系的。

   第三范式:在第二范式基础上,要求表的除主键外的字段都只能和主键有直接决定的依赖关系。

4.多表查询

   a.交叉连接查询(笛卡尔乘积: 4*3=12,产生笛卡尔积的原因是没有足够的连接条件)

     SELECT employee.name,dept.name FROMemployee,dept;

   b.内连接查询

      效果:只有满足连接条件的数据才会显示出来

      SELECT e.name,d.name

         FROMemployee e,dept d

         WHEREe.deptId=d.id;

     -- 另一种表达

      SELECT e.name,d.name

         FROMemployee e

         INNERJOIN dept d

         ON e.deptId=d.id;

   c.左外连接查询

      介绍: 右表(部门表)的数据全部显示,左表(员工)的数据当满足连接条件的时候,就显示满足        条件的数据,但是如果不满足连接条件,则显示null

      SELECT d.name,e.name

         FROMdept d

         LEFTOUTER JOIN employee e

         ON d.id=e.deptId;

    d.自连接查询

        SELECT e.name AS ‘员工‘,b.name AS ‘上司‘

         FROMemployee e

         LEFTOUTER JOIN employee b

            ON e.bossId=b.id;

      --  查询员工姓名及其上司姓名(没有上司的员工也显示

4.MySQL存储过程

    a.特点:

       ①.存储过程保存到数据库服务器端,通过数据库客户端工具调用存储过程

     ②.存储过程的效率会非常高,因为存储过程是在数据库服务器端执行。

        ③.存储过程的移植性非常差的

   b.存储过程语法

      创建存储过程

      -- 定义结束符号

      DELIMITER 结束符号

      CREATE PROCEDURE 存储过程名称 (形式参数列表)

      BEGIN

           多个sql语句

      END 结束符号

      -- 调用存储过程

      CALL 存储过程名称(实际参数列表);

  参数类型

     IN: 输入参数,可以携带数据到存储过程中

     OUT: 输出参数,可以携带数据到存储过程外面

        INOUT: 输入输出参数

   c.几种常见存储过程

     . 带有输入参数的存储过程

        --  传入员工id查询对应的员工

        DELIMITER $

        CREATE PROCEDURE pro_testByIn(IN eidINT)  -- 参数类型(IN 参数名称 数据类型(int)

        BEGIN

              SELECT* FROM employee WHERE id=eid;

        END $

        -- 调用

        CALL pro_testByIn(2);

 

    .带有输出参数的存储过程

        DELIMITER $

        CREATE PROCEDURE pro_testByOut(OUT nVARCHAR(20))

        BEGIN

            --修改变量n

            SETn = ‘输出参数‘;

        END $

   ③.带有输入输出参数的存储过程

       DELIMITER $

       CREATE PROCEDURE pro_testByInOut(INOUT nVARCHAR(20))

       BEGIN

            --查看n变量

             SELECTn;

            --修改n变量

            SETn = ‘500‘;

       END $

       -- 定义会话变量调用存储过程

       SET @n=‘100‘;

       CALL pro_testByInOut(@n);

       -- 查看n

           SELECT @n;

   ④.带有判断条件的存储过程

        -- 输入一个num整数,num=1 ,输出‘星期一’,num=2,输出‘星期二’,num=3,输出‘星期   

        -- 三’,否则,输出‘错误参数’

       DELIMITER $

       CREATE PROCEDURE pro_testByIf(IN numINT,OUT str VARCHAR(20))

       BEGIN

              IFnum=1 THEN

                     SETstr = ‘星期一‘;

              ELSEIFnum= 2 THEN

                     SETstr =‘星期二‘;

              ELSEIFnum=3 THEN

                     SETstr = ‘星期三‘;

              ELSE

                     SETstr = ‘错误参数‘;

            ENDIF;

      END $

      CALL pro_testByIf(5,@str);

      SELECT @str;

   ⑤.带有循环条件的存储过程

       --  输入一个num,计算从1到num的总和。

      DELIMITER $

      CREATE PROCEDURE pro_testByWhile(IN numINT,OUT score INT)

      BEGIN

              -- 定义局部变量

             DECLARE i INT DEFAULT 1;

             DECLAREresult INT DEFAULT 0;

             WHILEi<=num DO

                      SETresult = result + i;

                      SETi = i+1;

             ENDWHILE;

             SETscore = result;

     END $

     CALL pro_testByWhile(200,@score);

        SELECT @score;

  ⑥.携带数据库的数据给输出参数(INTO)

      -- 需求: 传入员工id,查询对应的员工,输出员工姓名

     DELIMITER $

     CREATE PROCEDURE pro_testByData(IN eidINT,OUT sname VARCHAR(20))

     BEGIN

              SELECTNAME INTO sname FROM employee WHERE id=eid;

     END $

 

     CALL pro_testByData(2,@sname);

        SELECT @sname;

 d.mysql数据库三种变量:

         ①.全局变量。mysql内置的变量,mysql程序关闭的时候全局变量才会失效。

                          show variables:查看全局变量

                          character_set_client: mysql接收的客户端的数据编码

                          character_set_results mysql使用什么编码输出给客户端数据

                        查看某个全局变量select @@变量名

                        修改某个全局变量set @@变量名=

         ②.会话变量。变量只在某次登录的会话中有效!退出连接,会话变量数据失效。  

                        查看某个会话变量 select @变量名

                        修改/定义某个会话变量 set @变量名=

         ③. 局部变量:在存储过程中定义的变量。存储过程结束局部变量失效

                         查看某个局部变量 select 变量名

                          修改某个局部变量 set 变量名=

      

5.触发器

    定义:当往员工表插入/修改/删除一条数据的时候,同时往日志表记录下来,这时就要使用触发器完       成。

    触发器语法


        -- 当往员工表插入一条数据时,往员工日志插入一条记录。

        -- 员工日志表

        CREATE TABLE emp_log(

                 idINT PRIMARY KEY AUTO_INCREMENT,

        contentVARCHAR(20)

        )


        -- 创建增加触发器

        CREATE TRIGGER tri_empAdd AFTER INSERT ONemployee FOR EACH ROW

                 INSERTINTO emp_log(content) VALUES(‘员工被插入了一条记录‘);

        -- 创建修改触发器

        CREATE TRIGGER tri_empUpd AFTER UPDATE ONemployee FOR EACH ROW

                 INSERTINTO emp_log(content) VALUES(‘员工被修改了一条记录‘);

        -- 创建删除触发器

        CREATE TRIGGER tri_empDel AFTER DELETE ONemployee FOR EACH ROW

                 INSERTINTO emp_log(content) VALUES(‘员工被删除了一条记录‘); 

        DROP TRIGGER tri_empAdd;

        SELECT * FROM employee;

        SELECT * FROM emp_log; 

        INSERT INTO employee(NAME,deptId,bossId)VALUES(‘李四‘,5,1);

        UPDATE employee SET NAME=‘张三‘ WHERE id=10;

            DELETE FROM employee WHERE id=10;

6.mysql权限问题

         root用户: 超级管理员。    权限:增删改查(数据库,表,数据)

         给mysql数据库分配不同权限的不同用户

        mysql数据库:存放mysql配置信息,包括用户信息

        USE mysql;

        -- 用户表

        SELECT * FROM USER;

        -- 加密函数(md5算法--单向加密)

        SELECT PASSWORD(‘root‘);

        -- 修改用户密码

        UPDATE USER SET PASSWORD=PASSWORD(‘123456‘)WHERE USER=‘root‘;

        -- 分配权限给不同的用户

        -- 分配查询day15数据库的employee表的权限给eric用户,密码为‘123456’

        GRANT SELECT ON day15.employee TO‘eric‘@‘localhost‘ IDENTIFIED BY ‘123456‘;

        GRANT DELETE ON day15.employee TO‘eric‘@‘localhost‘ IDENTIFIED BY ‘123456‘;

        SHOW TABLES;



my sql(二)