Mysql (约束、多表）

一．约束：

-- 1.主键约束（primary key）

CREATE TABLE stu(
id INT PRIMARY KEY,
sname VARCHAR(10)
);

-- 2.联合主键

CREATE TABLE stu(
stuid INT ,
clsid INT,
sname VARCHAR(10),
PRIMARY KEY(stuid,clsid)
);

-- 3

CREATE TABLE stu(
id INT,
NAME VARCHAR(50)
);
ALTER TABLE stu ADD PRIMARY KEY (id);

-- 4.唯一约束(unique)：

CREATE TABLE stu(
id INT PRIMARY KEY,
sname VARCHAR(10) UNIQUE
);

-- 5.自动增长列(auto_increment)

CREATE TABLE stu(
id INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(10)
);

-- 6.非空约束：not null

CREATE TABLE stu(
id INT PRIMARY KEY ,
sname VARCHAR(10) NOT NULL
);

-- 7.默认值约束 default

CREATE TABLE stu(
id INT PRIMARY KEY ,
sname VARCHAR(10) DEFAULT’小明’
);

-- 8.外键约束：FOREIGN KEY

CREATE TABLE score(
id INT,
score INT,
sid INT , – 外键列的数据类型一定要与主键的类型一致
CONSTRAINT fk_score_sid FOREIGN KEY (sid) REFERENCES stu(id)
);

二.多表

– 主表：从表通过外键匹配在主表中获取信息
– 从表：有外键的
– 结论1：从表只能添加主表已有的对内容
– 结论2：主表不能删除或修改从表中已有对应外键的数据
– 接触主键：ALTER TABLE 从表 DROP FOREIGN KEY 外键的约束名称;
ALTER TABLE scroe DROP FOREIGN KEY fk_id;

-- 2.内链接

SELECT * FROM stu s INNER JOIN score c ON s.id=c.id;

-- 2.外连接

-- 左外连接

SELECT stu.id,sname,score,sid
FROM stu LEFT JOIN score
ON stu.id=score.id;

-- 右外连接

SELECT stu.id,sname,score,sid
FROM stu RIGHT JOIN score
ON stu.id=score.id;

-- 3.自然连接

– SELECT * FROM t1 NATURAL JOIN t2;
SELECT * FROM stu NATURAL JOIN score;
– SELECT * FROM t1 NATURAL LEFT JOIN t2;
SELECT * FROM stu NATURAL LEFT JOIN score;
– SELECT * FROM t1 NATURAL RIGHT JOIN t2;
SELECT * FROM stu NATURAL RIGHT JOIN score;

-- 子查询

CREATE TABLE dept (
  deptid INT(11) NOT NULL,
  deptname VARCHAR(50) DEFAULT NULL,
  PRIMARY KEY (deptid)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

/*Data for the table `dept` */

INSERT  INTO dept(deptid,deptname) VALUES (10,'研发部'),(20,'市场'),(30,'后勤');

CREATE TABLE emp(
  emid INT(11) NOT NULL AUTO_INCREMENT,
  ename VARCHAR(50) NOT NULL,
  job VARCHAR(50) DEFAULT NULL,
  mgr INT(11) DEFAULT NULL,
  hiredate DATE DEFAULT NULL,
  sal DECIMAL(7,2) DEFAULT NULL,
  comm DECIMAL(7,2) DEFAULT NULL,
  deptid INT(11) DEFAULT NULL,
  PRIMARY KEY (emid),
  KEY deptid (deptid),
  CONSTRAINT emp_ibfk_1 FOREIGN KEY (deptid) REFERENCES dept (deptid)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO emp VALUES(1001,'后羿','项目经理',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp VALUES(1002,'鲁班','技术总监',7698,'1981-02-20',1600,300,30);
INSERT INTO emp VALUES(1003,'赵云','保安',7698,'1981-02-22',1250,500,30);
INSERT INTO emp VALUES(1004,'张飞','销售',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp VALUES(1005,'刘备','java开发',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp VALUES(1006,'诸葛亮','前台',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp VALUES(1007,'阿珂','人事',7839,'1981-06-09',2450,NULL,10);

-- 1.工资高于鲁班的员工===作为where条件
-- 1.1鲁班的工资
SELECT sal FROM emp WHERE ename='鲁班';
-- 1.2以鲁班的工资作为条件查询
SELECT sal FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='鲁班');
-- 2、查询与阿珂同一个部门的员工。
-- 阿珂的部门
SELECT deptid FROM emp WHERE ename='阿珂';
-- 以阿珂的部门作为条件查询员工
SELECT ename,deptid FROM emp
WHERE deptid=(SELECT deptid FROM emp WHERE ename='阿珂');
-- 3.工资高于30号部门所有人的员工信息
-- 30号部门所有员工的工资信息
SELECT sal FROM emp WHERE deptid='30';
-- 以30号部门所有员工的信息作为条件查询
SELECT * FROM emp WHERE sal>ALL(SELECT sal FROM emp WHERE deptid='30');
-- 4.查询工作和工资与赵云完全相同的员工信息
-- 4.赵云的工资工作
SELECT sal,job FROM emp WHERE ename='赵云';
-- 
SELECT ename,sal,job FROM emp 
WHERE (sal,job) IN(SELECT sal,job FROM emp WHERE ename='赵云');
-- 5.部门人数大于2的员工信息
SELECT deptid FROM emp GROUP BY deptid HAVING COUNT(deptid)>2;
SELECT*FROM emp WHERE deptid=ANY(SELECT deptid FROM emp GROUP BY deptid HAVING COUNT(deptid)>2);
-- 6.查询员工编号为1001的员工名称、员工工资、部门名称
SELECT ename,sal,deptname FROM emp INNER JOIN dept 
ON emid='1001' AND emp.`deptid`=dept.`deptid`;