澳门新浦京娱乐场网站-www.146.net-新浦京娱乐场官网
做最好的网站

MYSQL基础操作之数据约束与关联查询,MySQL数据约

 个人博客地址:https://www.vastyun.com

MYSQL基础操作之数据约束与关联查询,mysql基础约束关联

 一、MYSQL约束

1.默认值约束,当字段没有插入值的时候,mysql自动给该字段分配默认值。
默认值的字段允许为空。
对默认值字段也可以插入null。

1 CREATE TABLE STUDENT(
2     ID INT,
3     NAME VARCHAR(20),
4     ADDRESS VARCHAR(20) DEFAULT '京口区'
5 );
6 INSERT INTO STUDENT (ID, NAME ) VALUES (1,'张三');
7 INSERT INTO STUDENT (ID , NAME,ADDRESS ) VALUES (2,'李四' ,NULL);

 

澳门新浦京娱乐场网站 1

2.非空约束

1 CREATE TABLE STUDENT(
2     ID INT,
3     NAME VARCHAR(20),
4     GENDER VARCHAR(2) NOT NULL
5 );

 

2.1非空字段必须赋值(错误显示)

INSERT INTO STUDENT (ID , NAME) VALUES(1, '李四');

 

澳门新浦京娱乐场网站 2

2.2不能插入null(错误显示)

INSERT INTO STUDENT (ID , NAME) VALUES (1, '张三' ,NULL);

 

澳门新浦京娱乐场网站 3

 

3.唯一约束

1 CREATE TABLE STUDENT (
2     ID INT UNIQUE,
3     NAME VARCHAR(20)
4 );
5 INSERT INTO STUDENT (ID , NAME) VALUES (1, '张三');

 

下面语句执行发生错误

INSERT INTO STUDENT (ID , NAME) VALUES (1, '李四');

 

错误提示

澳门新浦京娱乐场网站 4

4.主键约束(非空 唯一),通常每张表都会设置一个主键字段。用于标注表记录的唯一性。
 主键一般都是没有业务含义的。

1 CREATE TABLE STUDENT(
2     ID INT PRIMARY KEY,
3     NAME VARCHAR(20)
4 );
5 
6 INSERT INTO STUDENT (ID , NAME) VALUES (1, '张三');

下面语句执行显示错误

INSERT INTO STUDENT (ID , NAME ) VALUES (NULL , '李四');

错误提示

澳门新浦京娱乐场网站 5

下面错误也是一种错误

INSERT INTO STUDENT (ID ,NAME ) VALUES (1, '李四');

澳门新浦京娱乐场网站 6

澳门新浦京娱乐场网站,5.自增长约束
可以自动的递增

 CREATE TABLE STUDENT(
    ID INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
);
INSERT INTO STUDENT (NAME) VALUES ('张三');
INSERT INTO STUDENT (NAME) VALUES ('李四');

澳门新浦京娱乐场网站 7

整表数据删除,不影响自增的删除语句

DELETE FROM STUDENT;

删除表之后再次(不用再创建表),添加插入语句如图所示

澳门新浦京娱乐场网站 8

删除表中所有数据,删除外键也是用这个语句。

DELETE FROM STUDENT;

再次插入数据

澳门新浦京娱乐场网站 9

6.外键约束

被约束的表为副表,外键设置在副表上

eg:创建员工表(副表),创建部门表(主表),先创建主表,再创建副表

CREATE TABLE DEPT(
    ID INT PRIMARY KEY,
    DEPTID VARCHAR(20)
);

INSERT INTO DEPT (ID , DEPTNAME) VALUES (1, '软件设计部门');
INSERT INTO DEPT (ID , DEPTNAME) VALUES (2, '人事部');
INSERT INTO DEPT (ID , DEPTNAME) VALUES (3, '财务部');
INSERT INTO DEPT (ID , DEPTNAME) VALUES (4, '运营部');

CREATE TABLE EMPLOYEE(
    ID INT PRIMARY KEY AUTO_INCREMENT,
    EMPNAME VARCHAR(20),
    DEPTID INT,
    CONSTRAINT EMPLOYEE_DEPT_FK FOREIGN KEY (DEPTID) REFERENCES DEPT(ID)
);

INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('张三' , 1);
INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('李四' , 2);
INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('王五' , 3);
INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('赵六' , 4);

查询两张表

澳门新浦京娱乐场网站 10

澳门新浦京娱乐场网站 11

7.下面进行级联关联,意味着,当主表修改时,副表也修改

修改以上表的创建语句,并重新插入新的语句

 1 DROP TABLE EMPLOYEE;
 2 
 3 CREATE TABLE EMPLOYEE(
 4     ID INT PRIMARY KEY AUTO_INCREMENT,
 5     EMPNAME VARCHAR(20),
 6     DEPTID INT,
 7     CONSTRAINT EMPLOYEE_DEPT_FK FOREIGN KEY (DEPTID) REFERENCES DEPT(ID) ON UPDATE CASCADE ON DELETE CASCADE
 8 );
 9 
10 INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('张三' , 1);
11 INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('李四' , 2);
12 INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('王五' , 3);
13 INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('赵六' , 4);

查询如图所示

澳门新浦京娱乐场网站 12

修改主表的一条记录

UPDATE DEPT SET ID=5 WHERE ID=4;

主表的运行结果

澳门新浦京娱乐场网站 13

副表的运行结果

澳门新浦京娱乐场网站 14


二、关联查询

1.交叉查询

SELECT * FROM EMPLOYEE,DEPT;

澳门新浦京娱乐场网站 15

2.内连接查询

多表查询规则:1)查询哪些表    2)确定哪些字段    3)表与表之间的查询条件(连接表的数量-1)

1 SELECT EMPNAME , dept.DEPTNAME
2         FROM EMPLOYEE
3         INNER JOIN DEPT
4         ON EMPLOYEE.DEPTID = DEPT.ID;

澳门新浦京娱乐场网站 16

3.使用别名

1 SELECT EMPNAME AS '员工姓名', D.DEPTNAME AS '部门' -- 注意这里的D
2                 FROM EMPLOYEE AS E
3                 INNER JOIN DEPT AS D
4                 ON E.DEPTID = D.ID;

澳门新浦京娱乐场网站 17

4.左外连接查询,左边的数据一定会完全显示。查询时先写左表

首先我们改造一下副表

UPDATE EMPLOYEE SET DEPTID = NULL WHERE  ID= 4;

 

 

 澳门新浦京娱乐场网站 18

如果是内连接查询则显示

澳门新浦京娱乐场网站 19

如果左连接查询

1  SELECT    D.DEPTNAME, E.EMPNAME
2                  FROM DEPT AS D -- 左表
3                  LEFT OUTER JOIN EMPLOYEE AS E -- 右表
4                  ON D.ID = E.DEPTID;

 

澳门新浦京娱乐场网站 20

右连接其实和左连接一样,只是查询表的位置不同,下面是右连接,显示的和左连接的相同

1  SELECT D.DEPTNAME,E.EMPNAME
2                 FROM EMPLOYEE AS E-- 左表
3                 RIGHT JOIN DEPT AS D -- 右表
4                 ON E.ID = D.ID;

 

澳门新浦京娱乐场网站 21

5.自连接查询,自连接查询一般应用于表数据为树状结构。

首先我们创建表并查询

 1 CREATE TABLE PERSON(
 2     ID INT PRIMARY KEY AUTO_INCREMENT,
 3     NAME VARCHAR(20),
 4     BOSSID INT
 5 );
 6 
 7 INSERT INTO PERSON (NAME,BOSSID) VALUES ('张三',NULL);
 8 INSERT INTO PERSON (NAME,BOSSID) VALUES ('李四',1);
 9 INSERT INTO PERSON (NAME,BOSSID) VALUES ('王五',2);
10 INSERT INTO PERSON (NAME,BOSSID) VALUES ('赵六',3);
11 INSERT INTO PERSON (NAME,BOSSID) VALUES ('李七',3);
12 
13 SELECT P.NAME AS '老板' ,B.NAME AS '员工'
14     FROM PERSON AS P
15     RIGHT JOIN PERSON AS B
16     ON P.ID = B.BOSSID;

 

显示结果

澳门新浦京娱乐场网站 22

注意上面创建表的BOSSID的结构。

一、MYSQL约束 1.默认值约束,当字段没有插入值的时候,mysql自动给该字段分配默认...

 1 默认值deafult:在建表的时候字段后使用 default ,默认值字段允许为null。

数据约束

对用户操作表的数据进行约束

  1. 默认值【default】
    作用: 当用户对使用默认值的字段不插入值的时候,就使用默认值。
    1)对默认值字段插入null是可以的。
    2)对默认值字段可以插入非null

     create table student (
         id int,
         name varchar(20),
         address varchar(20) default '杭州滨江' -- 默认值       
     );
    
     当字段没有插入值的时候,mysql自动给 address 字段分配默认值为 '杭州滨江'
     insert into student (id,name) values(2,"yolo");     
    
  2. 非空【not null】
    作用: 限制字段必须赋值
    1)非空字符必须赋值
    2)非空字符不能赋null

    create table student (
        id int,
        name varchar(20),
        address varchar(20) not null    -- 非空       
    );
    
  3. 唯一【UNIQUE】
    作用:对字段的值不能重复
    1)唯一字段可以插入null
    2)唯一字段可以插入多个null
    3)唯一对null值不起作用,仅限制有值的情况

     CREATE TABLE student(
         id INT UNIQUE, -- 唯一 
         NAME VARCHAR(20)
     )
    
     INSERT INTO student(id,NAME) VALUES(1,'zs');
     INSERT INTO student(id,NAME) VALUES(1,'lisi'); -- ERROR 1062 (23000):       Duplicate entry '1' for key 'id'
    
  4. 主键 【RIMARY KEY】
    作用: 非空 唯一
    1)通常情况下,每张表都会设置一个主键字段。用于标记表中的每条记录的唯一性。
    2)建议不要选择表的包含业务含义的字段作为主键,建议给每张表独立设计一个非业务含义的id字段。

     CREATE TABLE student(
         id INT PRIMARY KEY, -- 主键
         NAME VARCHAR(20)
     )
    
     INSERT INTO student(id,NAME) VALUES(1,'张三');
     INSERT INTO student(id,NAME) VALUES(2,'张三');
     -- INSERT INTO student(id,NAME) VALUES(1,'李四'); -- 违反唯一约束: Duplicate entry '1' for key 'PRIMARY'
    
     -- insert into student(name) value('李四'); -- 违反非空约束: ERROR 1048 (23000): Column 'id' cannot be null
    
  5. 自增长
    作用: 自动递增

     create table student (
         id int(4) ZEROFILL PRIMARY KEY AUTO_INCREMENT, -- 主键自增长、不满4位,零填充(0001)  
         NAME varchar(10) 
     );
    
     -- 自增长字段可以不赋值,自动递增
     INSERT INTO student(NAME) VALUES('张三');
     INSERT INTO student(NAME) VALUES('李四');
     INSERT INTO student(NAME) VALUES('王五');
    
     SELECT * FROM student;
     -- 不能影响自增长约束
     DELETE FROM student;
     -- 可以影响自增长约束
     TRUNCATE TABLE student;
    
  6. 外键
    作用:约束两张表的数据
    出现两种表的情况:解决数据冗余高(字段重复)问题: 独立出一张表
    例如: 员工表 和 部门表

    问题:在插入员工表数据的时候,员工表的部门ID字段可以随便插入(部门表中仅有1、2、3 三个部门ID)

    解决办法: 在员工表的部门ID字段添加一个外键约束,约束插入员工表的部门ID字段值

     -- 部门表(主表-约束别人)
     CREATE TABLE dept(
         id INT PRIMARY KEY,         
         deptName VARCHAR(20)
     )
    
     -- 修改员工表(副表/从表-被别人约束)
     CREATE TABLE employee(
         id INT PRIMARY KEY,
         empName VARCHAR(20),
         deptId INT,             -- 外键(被约束字段)
         -- 声明一个外键约束
         CONSTRAINT emlyee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id)
         --           外键名称                  外键               参考表(参考字段)
     )   
    

     1)被约束的表称为副表,约束别人的表称为主表,外键设置在副表上的!!!
     2)主表的参考字段通用为主键!
     3)添加数据: 先添加主表,再添加副表
     4)修改数据: 先修改副表,再修改主表
     5)删除数据: 先删除副表,再删除主表
    
  7. 级联操作

    问题: 当有了外键约束的时候,必须先修改或删除副表中的所有关联数据,才能修改或删除主表!但是,我们希望直接修改或删除主表数据,从而影响副表数据。可以使用级联操作实现!

    作用:主表的数据更改,会同时将所有改动附加到副表里面所有相关联的外键的值

     级联修改: ON UPDATE CASCADE
     级联删除: ON DELETE CASCADE
     CREATE TABLE employee(
         id INT PRIMARY KEY,
         empName VARCHAR(20),
         deptId INT,-- 把部门名称改为部门ID
         -- 声明一个外键约束
         CONSTRAINT emlyee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE  
     )
     注意: 级联操作必须在外键基础上使用  
    

     -- 级联修改-直接修改部门
     UPDATE dept SET id=5 WHERE id=4;
    
     -- 级联删除-直接删除部门 
     DELETE FROM dept WHERE id=1;

一、MYSQL约束

 

数据库设计

  • 业务模型/实体模型 - > 数据模型 (硬盘)
  • 数据库设计三大范式

第一范式: 要求表的每个字段必须是不可分割的独立单元
第二范式: 在第一范式的基础上,要求每张表只表达一个意思。表的每个字段都和表的主键有依赖。
第三范式: 在第二范式基础,要求每张表的主键之外的其他字段都只能和主键有直接决定依赖关系。

1.默认值约束,当字段没有插入值的时候,mysql自动给该字段分配默认值。
默认值的字段允许为空。
对默认值字段也可以插入null。

2 非空 not null:在建表的时候字段后使用  not null。

关联查询(多表查询)

多表查询规则:

  1. 确定查询哪些表
  2. 确定哪些哪些字段
  3. 表与表之间连接条件 (规律:连接条件数量是表数量-1)

内连接查询:只有满足条件的结果才会显示(使用最频繁)

  • 查询员工及其所在部门(显示员工姓名,部门名称)

      select empName,deptName                 -- 要查的字段                        
          from employee,dept                  -- 要查的表
      where employee.deptId=dept.id;          -- 表与表之间连接条件
    

      内连接的另一种语法:
    
      select empName,deptName
          from employee 
          inner join dept
          on employee.deptId=dept.id;
    

      使用别名
      select e.empName,d.deptName
          from employee as e,dept as d
          where e.deptId=d.id;
    

左[外]连接查询: 使用左边表的数据去匹配右边表的数据,如果符合连接条件的结果则显示,如果不符合连接条件则显示null;(注意: 左外连接:左表的数据一定会完成显示!)使用较频繁

  • 查询每个部门的员工 -左连接
    -- 预期结果:
    -- 软件开发部 张三
    -- 软件开发部 李四
    -- 应用维护部 王五
    -- 秘书部 陈六
    -- 总经办 null

     select dept.deptName,employee.deptId
     from dept       
     left outer join employee
     on dept.id=employee.deptId;
    

右[外]连接查询: 使用右边表的数据去匹配左边表的数据,如果符合连接条件的结果则显示,如果不符合连接条件则显示null(注意: 右外连接:右表的数据一定会完成显示!)
和左连接查询一毛一样,反过来而已啦~

  • 查询每个部门的员工 -右连接

     SELECT dept.deptName,employee.empName
     FROM employee 
     RIGHT JOIN dept             -- OUTER可省略
     ON dept.id=employee.deptId;
    

自连接查询(常用与树状结构,如电商分类)
虚拟出一张自己表

  • 查询员工及其上司
    预期结果:
    -- 张三 null
    -- 李四 张三
    -- 王五 李四
    -- 陈六 王五

     select emp.empName,boss.empName
     from employee emp
     left join employee boss
     on emp.bossId=boss.id;
    
1 CREATE TABLE STUDENT(
2     ID INT,
3     NAME VARCHAR(20),
4     ADDRESS VARCHAR(20) DEFAULT '京口区'
5 );
6 INSERT INTO STUDENT (ID, NAME ) VALUES (1,'张三');
7 INSERT INTO STUDENT (ID , NAME,ADDRESS ) VALUES (2,'李四' ,NULL);

非空字段必须赋值,并且不能是null。

存储过程

存储过程:带有逻辑的sql语句

特点:

  1. 执行效率非常快!存储过程是在数据库的服务器端执行的!
  2. 移植性很差!不同数据库的存储过程是不能移植。

语法:

  • 创建存储过程

    DELIMITER $ -- 声明存储过程的结束符
    CREATE PROCEDURE pro_test() --存储过程名称(参数列表)
    BEGIN -- 开始
    -- 可以写多个sql语句; -- sql语句 流程控制
    SELECT * FROM employee;
    END $ -- 结束 结束符

  • 执行存储过程

      CALL pro_test();          -- CALL 存储过程名称(参数);   
    

带有参数的存储过程:

参数:
    * IN:  表示输入参数,可以携带数据到存储过程中
    * OUT: 表示输出参数,可以从存储过程中返回这个结果(类似 return)  
    * INOUT:表示输入输出参数,即可以出入,也可以输出
  1. 带有输入参数的存储过程
    传入一个员工的id,查询员工信息

     DELIMITER $    -- 修改定界符
     CREATE PROCEDURE pro_findById(IN eid INT)  -- IN: 输入参数 eid 参数名称 INT 参数类型
     BEGIN
         SELECT * FROM employee WHERE id=eid;
     END $ 
    
     -- 调用
     CALL pro_findById(4);
    
  2. 带有输出参数的存储过程 【相当于执行一个有返回值的方法】

     -- 定义存储过程
     DELIMITER $
     CREATE PROCEDURE pro_testOut(OUT str VARCHAR(20))  -- OUT:输出参数
     BEGIN
             -- 给参数赋值
         SET str='helljava';
     END $
    
     -- 1)定义一个会话变量name, 2)使用name会话变量接收存储过程的返回值
     set @NAME
    
     -- 2)调用带输出参数的存储过程
     call `pro_testOut`(@NAME);
    
     -- 3)查看被存储过程修改的值 
     select @name;
    
     -- 删除存储过程
     DROP PROCEDURE pro_testOut;
    

    mysql的变量

    1. 全局变量(内置变量):mysql 数据库内置的变量 (所有连接都起作用)
      -- 查看所有全局变量: show variables
      -- 查看某个全局变量: select @@变量名
      -- 修改全局变量: set 变量名=新值
      -- character_set_client: mysql服务器的接收数据的编码
      -- character_set_results:mysql服务器输出数据的编码

    2. 会话变量(用户变量): 只存在于当前客户端与数据库服务器端的一次连接当中。如果连接断开,那么会话变量全部丢失!
      -- 定义会话变量: set @变量=值
      -- 查看会话变量: select @变量

    3. 局部变量: 在存储过程中使用的变量就叫局部变量。只要存储过程执行完毕,局部变量就丢失!!BEGIN...END之间有效;
      -- 查看局部变量:select 变量

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

     DELIMITER $
     CREATE PROCEDURE pro_testInOut(INOUT n INT)  -- INOUT: 输入输出参数
     BEGIN
    
        SELECT n;        -- 查看变量
        SET n =500;  -- 设置变量
     END $
    
     -- 调用
     SET @n=10;
    
     CALL pro_testInOut(@n);
    
     SELECT @n;      -- 结果为500
    
  2. 带有条件判断的存储过程

     DELIMITER $
     CREATE PROCEDURE pro_testIf(IN num INT,OUT str VARCHAR(20))
     BEGIN
         IF num=1 THEN
             SET str='星期一';
         ELSEIF num=2 THEN
             SET str='星期二';
         ELSEIF num=3 THEN
             SET str='星期三';
         ELSE
             SET str='输入错误';
         END IF;
     END $
    
     CALL pro_testIf(4,@str);
    
     SELECT @str; 
    
  3. 带有循环功能的存储过程

     DELIMITER $
     CREATE PROCEDURE pro_testWhile(IN num INT,OUT result INT)
     BEGIN
         -- 定义一个局部变量
         DECLARE i INT DEFAULT 1;
         DECLARE vsum INT DEFAULT 0;
         WHILE i<=num DO
               SET vsum = vsum i;
               SET i=i 1;
         END WHILE;
         SET result=vsum;        -- 赋值
     END $
    
     CALL pro_testWhile(100,@result);      -- 执行
    
     SELECT @result;         -- 查看结果
    
  4. 使用查询的结果赋值给变量(INTO

     DELIMITER $
     CREATE PROCEDURE pro_findById2(IN eid INT,OUT vname VARCHAR(20) )
     BEGIN
         SELECT empName INTO vname FROM employee WHERE id=eid;
     END $
    
     CALL pro_findById2(1,@NAME);
    
     SELECT @NAME;   
    

练习: 编写一个存储过程
如果学生的英语平均分小于等于70分,则输出'一般'
如果学生的英语平均分大于70分,且小于等于90分,则输出‘良好’
如果学生的英语平均分大于90分,则输出‘优秀’

DELIMITER $
CREATE PROCEDURE pro_testAvg(OUT str VARCHAR(20))
BEGIN 
      -- 定义局部变量,接收平均分
      DECLARE savg DOUBLE;
      -- 计算英语平方分
      SELECT AVG(english) INTO savg FROM student2;
      IF savg<=70 THEN
           SET str='一般';
      ELSEIF savg>70 AND savg<=90 THEN
           SET str='良好';
      ELSE
       SET str='优秀';
      END IF;
END $

CALL pro_testAvg(@str);

SELECT @str;

 

 

触发器

作用:
当操作了某张表时,希望同时触发一些动作/行为,可以使用触发器完成!!

SELECT * FROM employee;

-- 日志表
CREATE TABLE test_log(
    id INT PRIMARY KEY AUTO_INCREMENT,
    content VARCHAR(100)
)

-- 需求: 当向员工表插入一条记录时,希望mysql自动同时往日志表插入数据
-- 创建触发器(添加)
CREATE TRIGGER tri_empAdd AFTER INSERT ON employee FOR EACH ROW    -- 当往员工表插入一条记录时
     INSERT INTO test_log(content) VALUES('员工表插入了一条记录');

-- 插入数据
INSERT INTO employee(id,empName,deptId) VALUES(7,'扎古斯',1);
INSERT INTO employee(id,empName,deptId) VALUES(8,'扎古斯2',1);

-- 创建触发器(修改)
CREATE TRIGGER tri_empUpd AFTER UPDATE ON employee FOR EACH ROW    -- 当往员工表修改一条记录时
     INSERT INTO test_log(content) VALUES('员工表修改了一条记录');

 -- 修改
 UPDATE employee SET empName='eric' WHERE id=7;

-- 创建触发器(删除)
CREATE TRIGGER tri_empDel AFTER DELETE ON employee FOR EACH ROW    -- 当往员工表删除一条记录时
     INSERT INTO test_log(content) VALUES('员工表删除了一条记录');

 -- 删除
 DELETE FROM employee WHERE id=7;

 SELECT * FROM employee;
 SELECT * FROM test_log;

澳门新浦京娱乐场网站 23

3 唯一:主键和unique 字段必须唯一。

2.非空约束

注意:unique 对 null 值不起作用。只对有值的字段数据进行约束。

1 CREATE TABLE STUDENT(
2     ID INT,
3     NAME VARCHAR(20),
4     GENDER VARCHAR(2) NOT NULL
5 );

 

 

4 主键primary key:就是not null 和 nuique的联合。

2.1非空字段必须赋值(错误显示)

 

INSERT INTO STUDENT (ID , NAME) VALUES(1, '李四');

5 自增长auto_increment:

 

 

澳门新浦京娱乐场网站 24

6 外键:约束两张表,主要是解决数据冗余。

2.2不能插入null(错误显示)

alter table tablename1

INSERT INTO STUDENT (ID , NAME) VALUES (1, '张三' ,NULL);

add foreign key column name1

 

references tablename2 column name2

澳门新浦京娱乐场网站 25

on delete restrict/cascade/set null;

 

 

3.唯一约束

在建表的时候创建外键,在添加完字段后 foreign key(column ame) reference table name2  (column name2) on delete cascade/restrict/set null。

1 CREATE TABLE STUDENT (
2     ID INT UNIQUE,
3     NAME VARCHAR(20)
4 );
5 INSERT INTO STUDENT (ID , NAME) VALUES (1, '张三');

注意:

 

1)被约束的表为附表,约束别人的表是主表,外键设置在附表上。

下面语句执行发生错误

2)添加数据是先添加主表数据,再添加附表数据

INSERT INTO STUDENT (ID , NAME) VALUES (1, '李四');

3)不能直接修改主表,先修改附表数据。

 

4)删除数据,先删除附表数据,再删除主表数据。

错误提示

 

澳门新浦京娱乐场网站 26

二、数据库表的设计原则,三大范式

4.主键约束(非空 唯一),通常每张表都会设置一个主键字段。用于标注表记录的唯一性。
MYSQL基础操作之数据约束与关联查询,MySQL数据约束和关联查询。 主键一般都是没有业务含义的。

第一范式:要求表中的每个字段必须是不可分割的独立单元,MYSQL基础操作之数据约束与关联查询,MySQL数据约束和关联查询。第一范式(确保每列保持原子性)

1 CREATE TABLE STUDENT(
2     ID INT PRIMARY KEY,
3     NAME VARCHAR(20)
4 );
5 
6 INSERT INTO STUDENT (ID , NAME) VALUES (1, '张三');

第二范式:要求每张表表达一个思想,表的每个字段都和主键有依赖,第二范式(确保表中的每列都和主键相关)

下面语句执行显示错误

第三范式:要求每张表主键之外的每个字段都只能和主键有直接依赖关系,第三范式(确保每列都和主键列直接相关,而不是间接相关)

INSERT INTO STUDENT (ID , NAME ) VALUES (NULL , '李四');

 

错误提示

三、关联查询

澳门新浦京娱乐场网站 27

查询规则:1)确定查询那些表 2)确定查询哪些字段 3)表与表之间的连接条件

下面错误也是一种错误

 

INSERT INTO STUDENT (ID ,NAME ) VALUES (1, '李四');

例如:

澳门新浦京娱乐场网站 28

表1: student

5.自增长约束
可以自动的递增

CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20) NOT NULL,
sgender CHAR(2) NOT NULL,
sage SMALLINT NOT NULL,
sphone VARCHAR(11),
address VARCHAR(400));
 CREATE TABLE STUDENT(
    ID INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
);
INSERT INTO STUDENT (NAME) VALUES ('张三');
INSERT INTO STUDENT (NAME) VALUES ('李四');

 

澳门新浦京娱乐场网站 29

表2:englishScore

整表数据删除,不影响自增的删除语句

CREATE TABLE englishScore(
id INT PRIMARY KEY AUTO_INCREMENT,
sscore SMALLINT,
sid INT NOT NULL,
FOREIGN KEY(sid) REFERENCES student(id) ON DELETE CASCADE
);
DELETE FROM STUDENT;

 

删除表之后再次(不用再创建表),添加插入语句如图所示

查询学生姓名和成绩

澳门新浦京娱乐场网站 30

3.1 内连接查询,两张表或多张表的连接条件都非空

删除表中所有数据,删除外键也是用这个语句。

SELECT sname, sscore
FROM student S, englishScore ES
WHERE S.id = ES.sid;
DELETE FROM STUDENT;

相当于是使用下面语句执行查询

再次插入数据

SELECT sname, sscore
FROM student S
INNER JOIN englishScore ES
ON S.id = ES.sid;

澳门新浦京娱乐场网站 31

 

6.外键约束

3.2 左外连接

被约束的表为副表,外键设置在副表上

SELECT S.sname, ES.sscore
FROM student S
LEFT JOIN englishScore ES
ON S.id = ES.sid;

eg:创建员工表(副表),创建部门表(主表),先创建主表,再创建副表

 

CREATE TABLE DEPT(
    ID INT PRIMARY KEY,
    DEPTID VARCHAR(20)
);

INSERT INTO DEPT (ID , DEPTNAME) VALUES (1, '软件设计部门');
INSERT INTO DEPT (ID , DEPTNAME) VALUES (2, '人事部');
INSERT INTO DEPT (ID , DEPTNAME) VALUES (3, '财务部');
INSERT INTO DEPT (ID , DEPTNAME) VALUES (4, '运营部');

CREATE TABLE EMPLOYEE(
    ID INT PRIMARY KEY AUTO_INCREMENT,
    EMPNAME VARCHAR(20),
    DEPTID INT,
    CONSTRAINT EMPLOYEE_DEPT_FK FOREIGN KEY (DEPTID) REFERENCES DEPT(ID)
);

INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('张三' , 1);
INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('李四' , 2);
INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('王五' , 3);
INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('赵六' , 4);

3.3 右外连接

查询两张表

SELECT S.sname, ES.sscore
FROM student S
RIGHT JOIN englishScore ES
ON S.id=ES.sid;

澳门新浦京娱乐场网站 32

 

澳门新浦京娱乐场网站 33

7.下面进行级联关联,意味着,当主表修改时,副表也修改

修改以上表的创建语句,并重新插入新的语句

 1 DROP TABLE EMPLOYEE;
 2 
 3 CREATE TABLE EMPLOYEE(
 4     ID INT PRIMARY KEY AUTO_INCREMENT,
 5     EMPNAME VARCHAR(20),
 6     DEPTID INT,
 7     CONSTRAINT EMPLOYEE_DEPT_FK FOREIGN KEY (DEPTID) REFERENCES DEPT(ID) ON UPDATE CASCADE ON DELETE CASCADE
 8 );
 9 
10 INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('张三' , 1);
11 INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('李四' , 2);
12 INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('王五' , 3);
13 INSERT INTO EMPLOYEE (EMPNAME , DEPTID) VALUES ('赵六' , 4);

查询如图所示

澳门新浦京娱乐场网站 34

修改主表的一条记录

UPDATE DEPT SET ID=5 WHERE ID=4;

主表的运行结果

澳门新浦京娱乐场网站 35

副表的运行结果

澳门新浦京娱乐场网站 36


二、关联查询

1.交叉查询

SELECT * FROM EMPLOYEE,DEPT;

澳门新浦京娱乐场网站 37

2.内连接查询

多表查询规则:1)查询哪些表    2)确定哪些字段    3)表与表之间的查询条件(连接表的数量-1)

1 SELECT EMPNAME , dept.DEPTNAME
2         FROM EMPLOYEE
3         INNER JOIN DEPT
4         ON EMPLOYEE.DEPTID = DEPT.ID;

澳门新浦京娱乐场网站 38

3.使用别名

1 SELECT EMPNAME AS '员工姓名', D.DEPTNAME AS '部门' -- 注意这里的D
2                 FROM EMPLOYEE AS E
3                 INNER JOIN DEPT AS D
4                 ON E.DEPTID = D.ID;

澳门新浦京娱乐场网站 39

4.左外连接查询,左边的数据一定会完全显示。查询时先写左表

首先我们改造一下副表

UPDATE EMPLOYEE SET DEPTID = NULL WHERE  ID= 4;

 

 

 澳门新浦京娱乐场网站 40

如果是内连接查询则显示

澳门新浦京娱乐场网站 41

如果左连接查询

1  SELECT    D.DEPTNAME, E.EMPNAME
2                  FROM DEPT AS D -- 左表
3                  LEFT OUTER JOIN EMPLOYEE AS E -- 右表
4                  ON D.ID = E.DEPTID;

 

澳门新浦京娱乐场网站 42

右连接其实和左连接一样,只是查询表的位置不同,下面是右连接,显示的和左连接的相同

1  SELECT D.DEPTNAME,E.EMPNAME
2                 FROM EMPLOYEE AS E-- 左表
3                 RIGHT JOIN DEPT AS D -- 右表
4                 ON E.ID = D.ID;

 

澳门新浦京娱乐场网站 43

5.自连接查询,自连接查询一般应用于表数据为树状结构。

首先我们创建表并查询

 1 CREATE TABLE PERSON(
 2     ID INT PRIMARY KEY AUTO_INCREMENT,
 3     NAME VARCHAR(20),
 4     BOSSID INT
 5 );
 6 
 7 INSERT INTO PERSON (NAME,BOSSID) VALUES ('张三',NULL);
 8 INSERT INTO PERSON (NAME,BOSSID) VALUES ('李四',1);
 9 INSERT INTO PERSON (NAME,BOSSID) VALUES ('王五',2);
10 INSERT INTO PERSON (NAME,BOSSID) VALUES ('赵六',3);
11 INSERT INTO PERSON (NAME,BOSSID) VALUES ('李七',3);
12 
13 SELECT P.NAME AS '老板' ,B.NAME AS '员工'
14     FROM PERSON AS P
15     RIGHT JOIN PERSON AS B
16     ON P.ID = B.BOSSID;

 

显示结果

澳门新浦京娱乐场网站 44

注意上面创建表的BOSSID的结构。

本文由澳门新浦京娱乐场网站发布于数据库,转载请注明出处:MYSQL基础操作之数据约束与关联查询,MySQL数据约