手机版
你好,游客 登录 注册 搜索
背景:
阅读新闻

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

[日期:2016-10-25] 来源:Linux社区  作者:vastsum [字体: ]

一、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); 

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不能插入null(错误显示

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

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

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 , '李四');

错误提示

下面错误也是一种错误

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

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

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

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

DELETE FROM STUDENT;

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

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

DELETE FROM STUDENT;

再次插入数据

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);

查询两张表

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);
复制代码

查询如图所示

修改主表的一条记录

UPDATE DEPT SET ID=5 WHERE ID=4;

主表的运行结果

副表的运行结果

--------------------------------------------------------------------------------------

二、关联查询

1.交叉查询

SELECT * FROM EMPLOYEE,DEPT;

2.内连接查询

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

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

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;

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

首先我们改造一下副表

UPDATE EMPLOYEE SET DEPTID = NULL WHERE  ID= 4;

 

 

 

如果是内连接查询则显示

如果左连接查询

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

 

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

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

 

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; 

显示结果

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

本文永久更新链接地址http://www.linuxidc.com/Linux/2016-10/136368.htm

linux
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款