约束
对表中的数据进行限定，保证数据的正确性、有效性和完整性
分类：
主键约束：primary key（相当于 非空约束+唯一约束）
非空约束：not null
唯一约束：unique
外键约束：foreign key
检查约束：check（只有个别数据库存在）
/*
?? ?约束的使用格式：
?? ??? ?1. 创建表格时， 添加约束(列级约束)
?? ??? ? ? create table 表名(
?? ??? ??? ?字段名称 数据类型 约束关键字,
?? ??? ??? ?...
?? ??? ? ? );
?? ??? ?2. 创建表格时, 添加约束(表级约束, notnull不支持)
?? ??? ? ? create table 表名(
?? ??? ??? ?字段名称1 数据类型,字段名称2 数据类型
?? ??? ??? ?...
?? ??? ??? ?,constraint 自定义的约束名称 约束关键字(字段名称)
?? ??? ??? ?
?? ??? ? ? );
?? ??? ?3. 修改表格字段,进行非空 唯一,主键 约束的添加
?? ??? ??? ?alter table 表名 modify 字段名称 数据类型 约束关键字;
?? ??? ?4. 修改表格字段, 进行非空约束的删除
?? ??? ??? ?alter table 表名 modify 字段名称 数据类型 ;
?? ??? ?5. 修改表格字段, 进行唯一约束的删除
?? ??? ??? ?alter table 表名 drop index 字段名称;
?? ??? ?6. 修改表格字段, 进行唯一约束的删除
?? ??? ??? ?alter table 表名 drop primary key;
?? ?1. 非空约束:
?? ??? ?关键字: not null
?? ??? ?
?? ?练习: 创建一个person表,字段如下:
?? ??? ?id int
?? ??? ?name varchar(20) 非空约束
?? ??? ?
*/
DROP TABLE person;
CREATE TABLE person(id INT,NAME VARCHAR(20) NOT NULL);
INSERT INTO person(id) VALUES(1);
CREATE TABLE person(
?? ?id INT,NAME VARCHAR(20),
?? ?CONSTRAINT person_name_nn UNIQUE(NAME)
);
/*
?? ?create table 表名(
?? ??? ?字段名称 数据类型 约束关键字,
?? ??? ?...
?? ?);
?? ?唯一约束: unique
?? ?练习: 创建一个person表, 字段列表如下:
?? ??? ?1. id int 唯一约束
?? ??? ?2. name varchar(20) 非空约束
?? ??? ?3. age int 非空约束.
*/
DROP TABLE person;
CREATE TABLE `person`(
?? ?`id` INT(11) UNIQUE,
?? ?`name` VARCHAR(20) NOT NULL,
?? ?`age` INT(11) NOT NULL
);
INSERT INTO person VALUES(1,"火鲤",18);
INSERT INTO person VALUES(1,"秦禄煜",19);
/*
?? ?主键约束: primary key (重点知识)
?? ??? ? ? create table 表名(
?? ??? ??? ?字段名称 数据类型 约束关键字,
?? ??? ??? ?...
?? ??? ? ? );
?? ?练习: 创建一个person表, 字段列表如下:
?? ??? ?1. id int 主键约束
?? ??? ?2. name varchar(20) 非空约束
?? ??? ?3. age int 非空约束.
?? ??? ?
*/
DROP TABLE person;
CREATE TABLE `person`(
?? ?`id` INT(11) PRIMARY KEY,
?? ?`name` VARCHAR(20) NOT NULL,
?? ?`age` INT(11) NOT NULL
);
/*
?? ?自增长: auto_increment
?? ?通常主键搭配自增长使用:primary key auto_increment
*/
CREATE TABLE `person`(
?? ?`id` INT(11) PRIMARY KEY AUTO_INCREMENT,
?? ?`name` VARCHAR(20) NOT NULL,
?? ?`age` INT(11) NOT NULL
);
INSERT INTO person(NAME,age) VALUES("火鲤",18);
INSERT INTO person(NAME,age) VALUES("嘉琦",18);
/*
?? ?练习: 创建一个学生表student,字段列表:
?? ??? ?id int 主键
?? ??? ?name varchar(20)
?? ??? ?age int
?? ??? ?indate date
?? ??? ?birthday date
*/
CREATE TABLE student (
?? ?id INT PRIMARY KEY AUTO_INCREMENT,
?? ?NAME VARCHAR(20),
?? ?age INT,
?? ?indate DATE,
?? ?birthday DATE
);
/*
?? ?外键约束
?? ?又叫完整性约束?
?? ??? ?
?? ?当一个表格作为主表, 被从表建立外键时. 主表无法删除. 只有当从表全部被删除 或 从表删除外键 后 才可以删除主表.
?? ?当从表的数据参考了从表的数据, 从表被参考数据无法直接删除.
?? ?
?? ?列级约束:
?? ?create table 表名(
?? ??? ??? ?字段名称 数据类型 foreign key references 主表表名(字段名),
?? ??? ??? ?...
?? ?);
?? ?表级约束:
?? ?create table 表名(
?? ??? ?字段列表,
?? ??? ?constraint 自定义的约束名称?
?? ??? ?foreign key(外键字段) references 主表表名(字段名)
?? ?);
?
?? ?
?? ?练习:?
?? ?创建一个部门表mydept:
?? ??? ?id int ?主键自增长,
?? ??? ?name varchar(20)
?? ?创建一个员工表myemp:
?? ??? ?id int 主键自增长,
?? ??? ?did int 外键mydept.id
?? ??? ?name varchar(20)
?? ??? ?sal double(10,2)
?? ??? ?
*/
CREATE TABLE mydept(
?? ?id INT PRIMARY KEY AUTO_INCREMENT,
?? ?NAME VARCHAR(20)
);
CREATE TABLE myemp(
?? ?id INT PRIMARY KEY AUTO_INCREMENT,
?? ?did INT,
?? ?NAME VARCHAR(20),
?? ?sal DOUBLE(10,2),
?? ?CONSTRAINT myemp_mydept_did?
?? ?FOREIGN KEY (did) REFERENCES mydept(id)
);
DROP TABLE mydept;
DROP TABLE myemp;
INSERT INTO mydept VALUES(1,"开发部");
INSERT INTO mydept VALUES(2,"产品部");
INSERT INTO myemp VALUES(1,1,"张三",18000);
INSERT INTO myemp VALUES(2,1,"李四",28000);
DELETE FROM mydept WHERE id=1;
/*
?? ?级联操作:
?? ??? ?1. 修改表,完成级联操作的添加
?? ??? ?语法:
?? ??? ??? ?alter table 表名 add constraint 外键名称
?? ??? ??? ?foreign key(外键字段) references 主表名称(被参考字段名称)
?? ??? ??? ?ON DELETE CASCADE
?? ?
?? ?
?? ??? ?1. 级联删除:
?? ??? ??? ?ON DELETE CASCADE
?? ??? ?2. 级联置空:
?? ??? ??? ?ON SET NULL
?? ??? ?3. 级联更新
?? ??? ??? ?ON UPDATE CASCADE?
*/
CREATE TABLE myemp(
?? ?id INT PRIMARY KEY AUTO_INCREMENT,
?? ?did INT,
?? ?NAME VARCHAR(20),
?? ?sal DOUBLE(10,2)
);
ALTER TABLE myemp ADD CONSTRAINT myemp_mydept_did
?? ??? ??? ?FOREIGN KEY(did) REFERENCES mydept(id)
?? ??? ??? ?ON DELETE CASCADE;
CREATE TABLE myemp(
?? ?id INT PRIMARY KEY AUTO_INCREMENT,
?? ?did INT,
?? ?NAME VARCHAR(20),
?? ?sal DOUBLE(10,2),
?? ?CONSTRAINT myemp_mydept_did?
?? ?FOREIGN KEY (did) REFERENCES mydept(id) ON DELETE CASCADE
);
DROP TABLE myemp;
/*
多表之间的关系
一对一
一对多（多对一）
多对多
?? ?多表查询:
?? ??? ?当要查询的信息, 在多个表格中时, 需要使用到多表查询技术.
?? ?语法格式:
?? ??? ?select 字段列表 from 表名列表 ...;
?? ??? ?
?? ??? ?在原有的查询语句的基础上, 表名可以写多个,多个表名之间通过逗号隔开.
? ? ? ? 一条SQL语句，显示不出两个格式
如果直接查询两表，会进行乘法操作
*/
DROP TABLE dept;
DROP TABLE emp;
DROP TABLE salgrade;
# 创建部门表
? ? CREATE TABLE dept(
? ? ? ? id INT PRIMARY KEY AUTO_INCREMENT,
? ? ? ? NAME VARCHAR(20)
? ? );
? ? INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');
? ? # 创建员工表
? ? CREATE TABLE emp (
? ? ? ? id INT PRIMARY KEY AUTO_INCREMENT,
? ? ? ? NAME VARCHAR(10),
? ? ? ? gender CHAR(1), -- 性别
? ? ? ? salary DOUBLE, -- 工资
? ? ? ? join_date DATE, -- 入职日期
? ? ? ? dept_id INT,
? ? ? ? FOREIGN KEY (dept_id) REFERENCES dept(id) -- 外键，关联部门表(部门表的主键)
? ? );
? ? INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1);
? ? INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2);
? ? INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);
? ? INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);
? ? INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);
内连接特点：完成能够匹配上这个条件的数据查询出来
消除笛卡尔积之间的关系就是建立关系
-- 通过隐式内连接,完成笛卡尔积的消除
SELECT 字段列表?FROM 表名1，表名2 WHERE 笛卡尔积的消除条件;?
SELECT * FROM emp,dept WHERE emp.dept_id=dept.id;
-- 通过显示内连接, 完成笛卡尔积的消除
SELECT 字段列表 FROM 表名1 inner join 表名2 on 笛卡尔积的消除条件;?
SELECT * FROM emp INNER JOIN dept ON emp.dept_id=dept.id;
多表查询的分类
内连接查询：隐式内连接：使用where条件消除无用数据?
外连接：左右外连接 主表和次表，主表全部显示，次表匹配显示
CREATE TABLE `dept` (
? `DEPTNO` INT(2) NOT NULL,
? `DNAME` VARCHAR(14) DEFAULT NULL,
? `LOC` VARCHAR(13) DEFAULT NULL,
? PRIMARY KEY (`DEPTNO`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
CREATE TABLE `emp` (
? `EMPNO` INT(4) NOT NULL,
? `ENAME` VARCHAR(10) DEFAULT NULL,
? `JOB` VARCHAR(9) DEFAULT NULL,
? `MGR` INT(4) DEFAULT NULL,
? `HIREDATE` DATE DEFAULT NULL,
? `SAL` INT(7) DEFAULT NULL,
? `COMM` INT(7) DEFAULT NULL,
? `DEPTNO` INT(2) DEFAULT NULL,
? PRIMARY KEY (`EMPNO`),
? KEY `FK_DEPTNO` (`DEPTNO`),
? CONSTRAINT `FK_DEPTNO` FOREIGN KEY (`DEPTNO`) REFERENCES `dept` (`DEPTNO`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
CREATE TABLE `salgrade` (
? `grade` DECIMAL(10,0) NOT NULL,
? `losal` DECIMAL(10,0) DEFAULT NULL,
? `hisal` DECIMAL(10,0) DEFAULT NULL,
? PRIMARY KEY (`grade`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO `dept` (`DEPTNO`, `DNAME`, `LOC`) VALUES ('10','会计','上海');
INSERT INTO `dept` (`DEPTNO`, `DNAME`, `LOC`) VALUES ('20','分析','北京');
INSERT INTO `dept` (`DEPTNO`, `DNAME`, `LOC`) VALUES ('30','销售','成都');
INSERT INTO `dept` (`DEPTNO`, `DNAME`, `LOC`) VALUES ('40','运营','北京');
INSERT INTO `emp` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES ('7369','史密斯','店员','7902','1980-12-17','800',NULL,'20');
INSERT INTO `emp` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES ('7499','艾伦','销售','7698','1981-02-20','1600','300','30');
INSERT INTO `emp` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES ('7521','沃德','销售','7698','1981-02-22','1250','500','30');
INSERT INTO `emp` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES ('7566','琼斯','主管','7839','1981-04-02','2975',NULL,'20');
INSERT INTO `emp` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES ('7654','马丁','销售','7698','1981-09-28','1250','1400','30');
INSERT INTO `emp` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES ('7698','布莱克','主管','7839','1981-05-01','2850',NULL,'30');
INSERT INTO `emp` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES ('7782','克拉克','主管','7839','1981-06-09','2450',NULL,'10');
INSERT INTO `emp` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES ('7788','斯科特','分析师','7566','1987-04-19','3000',NULL,'20');
INSERT INTO `emp` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES ('7839','老K','CEO',NULL,'1981-11-17','5000',NULL,'10');
INSERT INTO `emp` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES ('7844','图尔纳','销售','7698','1981-09-08','1500','0','30');
INSERT INTO `emp` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES ('7876','亚当斯','店员','7788','1987-05-23','1100',NULL,'20');
INSERT INTO `emp` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES ('7900','詹姆斯','店员','7698','1981-12-03','950',NULL,'30');
INSERT INTO `emp` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES ('7902','福特','分析师','7566','1981-12-03','3000',NULL,'20');
INSERT INTO `emp` (`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES ('7934','米乐','店员','7782','1982-01-23','1300',NULL,'10');
INSERT INTO `salgrade` (`grade`, `losal`, `hisal`) VALUES ('1','700','1200');
INSERT INTO `salgrade` (`grade`, `losal`, `hisal`) VALUES ('2','1201','1400');
INSERT INTO `salgrade` (`grade`, `losal`, `hisal`) VALUES ('3','1401','2000');
INSERT INTO `salgrade` (`grade`, `losal`, `hisal`) VALUES ('4','2001','3000');
INSERT INTO `salgrade` (`grade`, `losal`, `hisal`) VALUES ('5','3001','9999');
SELECT DISTINCT m.ename,m.job FROM emp e,emp m WHERE e.mgr=m.EMPNO;
?
|