SQL进阶

Author：子不语
发布时间：September 28, 2019
1783views
No comments
5987 words
Categories： mysql数据库

SQL进阶

1.约束之主键约束

非空、唯一、被引用

当表的某一列被指定为主键之后，该列就不能为空，不能有重复值出现。

创建表时指定主键的两种方式：

CREATE TABLE stu1(
    sid CHAR(6) PRIMARY KEY,
    sname VARCHAR(20),
    age INT,
    gender VARCHAR(10)
);

指定sid为主键列，既为sid添加主键约束

CREATE TABLE stu3(
    sid CHAR(6) ,
    sname VARCHAR(20),
    PRIMARY KEY(sid)
);

不能为空值：

mysql> insert into stu3 values(null,'zhangsan');
ERROR 1048 (23000): Column 'sid' cannot be null

也可以在创建完表之后再指定主键：

ALTER TABLE stu4 ADD PRIMARY KEY(sid);

删除主键：

ALTER TABLE stu4 DROP PRIMARY KEY;

2.主键自增长

因为主键的特性是：必须唯一、不能为空，所以通常会为指定主键类为整数型，让其自增长。

CREATE TABLE stu6(
    sid INT PRIMARY KEY AUTO_INCREMENT ,
    sname VARCHAR(20)
);

insert into stu6 values(8,'zhangsan');
DELETE FROM stu6;
insert into stu6 values(null,'zhangsan');

新插入为sid为9

3.非空约束和唯一约束

（1）非空约束

因为某些列不能设置为NULL，所以可以对列添加非空约束

CREATE TABLE stu6(
    sid INT NOT NULL,
    sname VARCHAR(20)
);

（2 ) 唯一约束

CREATE TABLE stu6(
    sid INT UNIQUE,
    sname VARCHAR(20)
);

4.概念模型、对象模型、关系模型

（1）概念模型

当我们完成一个软件系统时，需要把系统中的实体抽取出来，形成概念模型。

例如部门、员工都是系统的实体。概念模型中的实体最终会成为java中的类，数据库中的表。

实体中还存在着关系，关系有三种：

1对多，1对1，多对多：

概念模型在java中成为实体类（javaBean）

类就使用成员变量来完成关系，一般都是双向关联。

多对一双向中关联，既员工关联部门，部门也关联员工

class Employee{    //多方关联一方
    private Department;
}
class Department{    //一方关联多方
    peivate List<Employee> emplayees;
}

class Husband {
    private Wife wife;
}
class Wife {
    private Husband husband;
}

class Student {
    private List<Teacher> teacher;
}
class Teacher {
    private List<Student> student;
}

（2）对象模型

对象模型可以双向关联，而且引用的是对象，而不是一个主键。

关系模型：只能多方引用一方，而且引用的是主键，不是一行记录。

5.外键约束

外键必须是另一个表的主键的值（外键要引用主键）

外键可以重复

外键可以为空

一张表可以有多个外键

也可以自身关联

6.一对一关系

在表中建立一对一的关系比较特殊，需要让其中的一张表的主键，又是主键又是外键

CREATE TABLE hasband(
    hid INT PRIMARY KEY AUTO_INCREMENT,
    hname VARCHAR(20)
);
INSERT INTO hasband VALUES(NULL,'刘备');
INSERT INTO hasband VALUES(NULL,'关羽');
INSERT INTO hasband VALUES(NULL,'张飞');
CREATE TABLE wife(
    wid INT PRIMARY KEY AUTO_INCREMENT,
    wname VARCHAR(50),
    CONSTRAINT fk_wife_hasband FOREIGN KEY(wid) REFERENCES hasband(hid)
);
INSERT INTO wife VALUES(1,'杨贵妃');

INSERT INTO wife VALUES(1,'妲己');
-- ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
INSERT INTO wife VALUES(100,'妲己');
-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`wife`, CONSTRAINT `fk_wife_hasband` FOREIGN KEY (`wid`) REFERENCES `hasband` (`hid`))

从表的主键即使外键。

7.多对多关系

CREATE TABLE student(
    sid INT PRIMARY KEY AUTO_INCREMENT,
    sname VARCHAR(50)
);
CREATE TABLE teacher(
    tid INT PRIMARY KEY AUTO_INCREMENT,
    tname VARCHAR(50)    
);
CREATE TABLE stu_tea(
    sid INT,
    tid INT,
    CONSTRAINT fk_student FOREIGN KEY(sid) REFERENCES student(sid),
    CONSTRAINT fk_teacher FOREIGN KEY(tid) REFERENCES teacher(tid)
);

INSERT INTO student VALUES(NULL,'李一');
INSERT INTO student VALUES(NULL,'李二');
INSERT INTO student VALUES(NULL,'李三');
INSERT INTO student VALUES(NULL,'李四');
INSERT INTO student VALUES(NULL,'李五');

INSERT INTO teacher VALUES(NULL,'老一');
INSERT INTO teacher VALUES(NULL,'老二');
INSERT INTO teacher VALUES(NULL,'老三');

SELECT * FROM student;
SELECT * FROM student;
INSERT INTO stu_tea VALUES(1,1);
INSERT INTO stu_tea VALUES(2,1);
INSERT INTO stu_tea VALUES(3,1);
INSERT INTO stu_tea VALUES(4,1);
INSERT INTO stu_tea VALUES(5,1);

INSERT INTO stu_tea VALUES(2,2);
INSERT INTO stu_tea VALUES(3,2);
INSERT INTO stu_tea VALUES(4,2);

INSERT INTO stu_tea VALUES(3,3);
INSERT INTO stu_tea VALUES(4,3);
INSERT INTO stu_tea VALUES(5,3);

8.多表查询

（1）合并结果集

要求被合并的表中，列的类型和列数相同

UNION 去除重复行

UNION ALL 不去除重复行

CREATE TABLE ab(
    a INT,
    b VARCHAR(50)
);
INSERT INTO ab VALUES(1,'1');
INSERT INTO ab VALUES(2,'2');
INSERT INTO ab VALUES(3,'3');
CREATE TABLE cd(
    c INT,
    d VARCHAR(50)
);
INSERT INTO cd VALUES(3,'3');
INSERT INTO cd VALUES(4,'4');
INSERT INTO cd VALUES(5,'5');

SELECT * FROM ab
UNION ALL
SELECT * FROM cd;

SELECT * FROM ab
UNION 
SELECT * FROM cd;

（2）连接查询

（3）子查询

9.连接查询

（1）内连接

方言

SELECT * FROM 表1 别名1 ,表2, 别名2 WHERE 别名1.xx=别名2.xx

标准

SELECT * FROM 表1 别名1 INNER JOIN 表2, 别名2 ON 别名1.xx=别名2.xx

SELECT  *
FROM student,teacher;

笛卡尔积

{a，b，c} {1，2}

+-----+-------+-----+-------+
| sid | sname | tid | tname |
+-----+-------+-----+-------+
|   1 | 李一  |   1 | 老二  |
|   1 | 李一  |   2 | 老三  |
|   1 | 李一  |   3 | 老一  |
|   2 | 李二  |   1 | 老二  |
|   2 | 李二  |   2 | 老三  |
|   2 | 李二  |   3 | 老一  |
|   3 | 李三  |   1 | 老二  |
|   3 | 李三  |   2 | 老三  |
|   3 | 李三  |   3 | 老一  |
|   4 | 李四  |   1 | 老二  |
|   4 | 李四  |   2 | 老三  |
|   4 | 李四  |   3 | 老一  |
|   5 | 李五  |   1 | 老二  |
|   5 | 李五  |   2 | 老三  |
|   5 | 李五  |   3 | 老一  |
+-----+-------+-----+-------+

可以用条件去除无用的笛卡尔积。

SELECT  *
FROM student,teacher
WHERE student.sid = teacher.tid;

+-----+-------+-----+-------+
| sid | sname | tid | tname |
+-----+-------+-----+-------+
|   1 | 李一  |   1 | 老二  |
|   2 | 李二  |   2 | 老三  |
|   3 | 李三  |   3 | 老一  |
+-----+-------+-----+-------+

如果表名太长，也可以给表名起别名

SELECT  *
FROM student s,teacher t
WHERE s.sid = t.tid;

（2）外连接

左外链接

右外连接

全外连接（mysql不支持）

SELECT e.ename,e.sal,d.dname
FROM emp e LEFT OUTER JOIN dept d
ON e.deptno=d.deptno

（3）子查询

SELECT * FROM emp EHERE sal = (SELECT MAX(sal) FROM emp);

Last modification：February 12th, 2020 at 11:36 pm

如果觉得我的文章对你有用，请随意赞赏

SQL进阶

子不语 • 2019 年 09 月 28 日

SQL进阶

1.约束之主键约束

非空、唯一、被引用

当表的某一列被指定为主键之后，该列就不能为空，不能有重复值出现。

创建表时指定主键的两种方式：

CREATE TABLE stu1(
    sid CHAR(6) PRIMARY KEY,
    sname VARCHAR(20),
    age INT,
    gender VARCHAR(10)
);

指定sid为主键列，既为sid添加主键约束

CREATE TABLE stu3(
    sid CHAR(6) ,
    sname VARCHAR(20),
    PRIMARY KEY(sid)
);

不能为空值：

mysql> insert into stu3 values(null,'zhangsan');
ERROR 1048 (23000): Column 'sid' cannot be null

也可以在创建完表之后再指定主键：

ALTER TABLE stu4 ADD PRIMARY KEY(sid);

删除主键：

ALTER TABLE stu4 DROP PRIMARY KEY;

2.主键自增长

因为主键的特性是：必须唯一、不能为空，所以通常会为指定主键类为整数型，让其自增长。

CREATE TABLE stu6(
    sid INT PRIMARY KEY AUTO_INCREMENT ,
    sname VARCHAR(20)
);

insert into stu6 values(8,'zhangsan');
DELETE FROM stu6;
insert into stu6 values(null,'zhangsan');

新插入为sid为9

3.非空约束和唯一约束

（1）非空约束

因为某些列不能设置为NULL，所以可以对列添加非空约束

CREATE TABLE stu6(
    sid INT NOT NULL,
    sname VARCHAR(20)
);

（2 ) 唯一约束

CREATE TABLE stu6(
    sid INT UNIQUE,
    sname VARCHAR(20)
);

4.概念模型、对象模型、关系模型

（1）概念模型

当我们完成一个软件系统时，需要把系统中的实体抽取出来，形成概念模型。

例如部门、员工都是系统的实体。概念模型中的实体最终会成为java中的类，数据库中的表。

实体中还存在着关系，关系有三种：

1对多，1对1，多对多：

概念模型在java中成为实体类（javaBean）

类就使用成员变量来完成关系，一般都是双向关联。

多对一双向中关联，既员工关联部门，部门也关联员工

class Employee{    //多方关联一方
    private Department;
}
class Department{    //一方关联多方
    peivate List<Employee> emplayees;
}

class Husband {
    private Wife wife;
}
class Wife {
    private Husband husband;
}

class Student {
    private List<Teacher> teacher;
}
class Teacher {
    private List<Student> student;
}

（2）对象模型

对象模型可以双向关联，而且引用的是对象，而不是一个主键。

关系模型：只能多方引用一方，而且引用的是主键，不是一行记录。

5.外键约束

外键必须是另一个表的主键的值（外键要引用主键）

外键可以重复

外键可以为空

一张表可以有多个外键

也可以自身关联

6.一对一关系

在表中建立一对一的关系比较特殊，需要让其中的一张表的主键，又是主键又是外键

CREATE TABLE hasband(
    hid INT PRIMARY KEY AUTO_INCREMENT,
    hname VARCHAR(20)
);
INSERT INTO hasband VALUES(NULL,'刘备');
INSERT INTO hasband VALUES(NULL,'关羽');
INSERT INTO hasband VALUES(NULL,'张飞');
CREATE TABLE wife(
    wid INT PRIMARY KEY AUTO_INCREMENT,
    wname VARCHAR(50),
    CONSTRAINT fk_wife_hasband FOREIGN KEY(wid) REFERENCES hasband(hid)
);
INSERT INTO wife VALUES(1,'杨贵妃');

INSERT INTO wife VALUES(1,'妲己');
-- ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
INSERT INTO wife VALUES(100,'妲己');
-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`wife`, CONSTRAINT `fk_wife_hasband` FOREIGN KEY (`wid`) REFERENCES `hasband` (`hid`))

从表的主键即使外键。

7.多对多关系

CREATE TABLE student(
    sid INT PRIMARY KEY AUTO_INCREMENT,
    sname VARCHAR(50)
);
CREATE TABLE teacher(
    tid INT PRIMARY KEY AUTO_INCREMENT,
    tname VARCHAR(50)    
);
CREATE TABLE stu_tea(
    sid INT,
    tid INT,
    CONSTRAINT fk_student FOREIGN KEY(sid) REFERENCES student(sid),
    CONSTRAINT fk_teacher FOREIGN KEY(tid) REFERENCES teacher(tid)
);

INSERT INTO student VALUES(NULL,'李一');
INSERT INTO student VALUES(NULL,'李二');
INSERT INTO student VALUES(NULL,'李三');
INSERT INTO student VALUES(NULL,'李四');
INSERT INTO student VALUES(NULL,'李五');

INSERT INTO teacher VALUES(NULL,'老一');
INSERT INTO teacher VALUES(NULL,'老二');
INSERT INTO teacher VALUES(NULL,'老三');

SELECT * FROM student;
SELECT * FROM student;
INSERT INTO stu_tea VALUES(1,1);
INSERT INTO stu_tea VALUES(2,1);
INSERT INTO stu_tea VALUES(3,1);
INSERT INTO stu_tea VALUES(4,1);
INSERT INTO stu_tea VALUES(5,1);

INSERT INTO stu_tea VALUES(2,2);
INSERT INTO stu_tea VALUES(3,2);
INSERT INTO stu_tea VALUES(4,2);

INSERT INTO stu_tea VALUES(3,3);
INSERT INTO stu_tea VALUES(4,3);
INSERT INTO stu_tea VALUES(5,3);

8.多表查询

（1）合并结果集

要求被合并的表中，列的类型和列数相同

UNION 去除重复行

UNION ALL 不去除重复行

CREATE TABLE ab(
    a INT,
    b VARCHAR(50)
);
INSERT INTO ab VALUES(1,'1');
INSERT INTO ab VALUES(2,'2');
INSERT INTO ab VALUES(3,'3');
CREATE TABLE cd(
    c INT,
    d VARCHAR(50)
);
INSERT INTO cd VALUES(3,'3');
INSERT INTO cd VALUES(4,'4');
INSERT INTO cd VALUES(5,'5');

SELECT * FROM ab
UNION ALL
SELECT * FROM cd;

SELECT * FROM ab
UNION 
SELECT * FROM cd;

（2）连接查询

（3）子查询

9.连接查询

（1）内连接

方言

SELECT * FROM 表1 别名1 ,表2, 别名2 WHERE 别名1.xx=别名2.xx

标准

SELECT * FROM 表1 别名1 INNER JOIN 表2, 别名2 ON 别名1.xx=别名2.xx

SELECT  *
FROM student,teacher;

笛卡尔积

{a，b，c} {1，2}

+-----+-------+-----+-------+
| sid | sname | tid | tname |
+-----+-------+-----+-------+
|   1 | 李一  |   1 | 老二  |
|   1 | 李一  |   2 | 老三  |
|   1 | 李一  |   3 | 老一  |
|   2 | 李二  |   1 | 老二  |
|   2 | 李二  |   2 | 老三  |
|   2 | 李二  |   3 | 老一  |
|   3 | 李三  |   1 | 老二  |
|   3 | 李三  |   2 | 老三  |
|   3 | 李三  |   3 | 老一  |
|   4 | 李四  |   1 | 老二  |
|   4 | 李四  |   2 | 老三  |
|   4 | 李四  |   3 | 老一  |
|   5 | 李五  |   1 | 老二  |
|   5 | 李五  |   2 | 老三  |
|   5 | 李五  |   3 | 老一  |
+-----+-------+-----+-------+

可以用条件去除无用的笛卡尔积。

SELECT  *
FROM student,teacher
WHERE student.sid = teacher.tid;

+-----+-------+-----+-------+
| sid | sname | tid | tname |
+-----+-------+-----+-------+
|   1 | 李一  |   1 | 老二  |
|   2 | 李二  |   2 | 老三  |
|   3 | 李三  |   3 | 老一  |
+-----+-------+-----+-------+

如果表名太长，也可以给表名起别名

SELECT  *
FROM student s,teacher t
WHERE s.sid = t.tid;

（2）外连接

左外链接

右外连接

全外连接（mysql不支持）

SELECT e.ename,e.sal,d.dname
FROM emp e LEFT OUTER JOIN dept d
ON e.deptno=d.deptno

（3）子查询

SELECT * FROM emp EHERE sal = (SELECT MAX(sal) FROM emp);

SQL进阶

1.约束之主键约束

2.主键自增长

3.非空约束和唯一约束

（1）非空约束

（2 ) 唯一约束

4.概念模型、对象模型、关系模型

（1）概念模型

（2）对象模型

5.外键约束

6.一对一关系

7.多对多关系

8.多表查询

（1）合并结果集

（2）连接查询

（3）子查询

9.连接查询

（1）内连接

（2）外连接

（3）子查询

Leave a Comment Cancel reply

SQL进阶

SQL进阶

1.约束之主键约束

2.主键自增长

3.非空约束和唯一约束

（1）非空约束

（2 ) 唯一约束

4.概念模型、对象模型、关系模型

（1）概念模型

（2）对象模型

5.外键约束

6.一对一关系

7.多对多关系

8.多表查询

（1）合并结果集

（2）连接查询

（3）子查询

9.连接查询

（1）内连接

（2）外连接

（3）子查询