MySQL常见操作之3

来源:转载


MySQL常见操作之3,有需要的朋友可以参考下。


MySQL常见操作之3

外键操作、添加删除外键操作、联合查询、子查询

--创建部门表department(主表)

--id depName


CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR(20) NOT NULL UNIQUE
)ENGINE=INNODB;


INSERT department(depName) VALUES('教学部'),
('市场部'),
('运营部'),
('督导部');


--创建员工表employee(子表)
--id ,username ,depID
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED
)ENGINE=INNODB;


INSERT employee(username,depId) VALUES('king',1),
('queen',2),
('张三',3),
('李四',4),
('王五',1);


SELECT e.id,e.username,d.depName FROM


employee AS e


JOIN


department AS d


ON e.depId=d.id;


--删除监督部


DELETE FROM department WHERE depName='督导部';




CREATE TABLE IF NOT EXISTS department2(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR(20) NOT NULL UNIQUE
)ENGINE=INNODB;


INSERT department2(depName) VALUES('教学部'),
('市场部'),
('运营部'),
('督导部');


--创建员工表employee(子表)
--id ,username ,depID
CREATE TABLE IF NOT EXISTS employee2(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED,
FOREIGN KEY(depId) REFERENCES department2(id)
)ENGINE=INNODB;


INSERT employee2(username,depId) VALUES('king',1),
('queen',2),
('张三',3),
('李四',4),
('王五',1);


--删除主表中的记录
DELETE FROM department WHERE id=1;


--删除employ中的属于1部门的人
DELETE FROM employee WHERE depId=1;


INSERT employee(username,depId) VALUE('test',11);


--删除员工表
DROP TABLE employee;




CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED,
CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department(id)
)ENGINE=INNODB;




INSERT employee(username,depId) VALUES('king',1),
('queen',2),
('张三',3),
('李四',4),
('王五',1);


--删除外键
ALTER TABLE employee DROP FOREIGN KEY emp_fk_dep;


--添加外键
--添加外键前要先删除外键相关的table
ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department;
DELETE FROM employee WHERE depId=2;


------------------------
DROP TABLE employee,department;


CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR(20) NOT NULL UNIQUE
)ENGINE=INNODB;


INSERT department(depName) VALUES('教学部'),
('市场部'),
('运营部'),
('督导部');


--创建员工表employee(子表)
--id ,username ,depID
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED,
FOREIGN KEY(depId) REFERENCES department(id) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=INNODB;


INSERT employee(username,depId) VALUES('king',1),
('queen',2),
('张三',3),
('李四',4),
('王五',1);




--删除部门表中的第一个部门
DELETE FROM department WHERE id=1;


UPDATE department SET id=id+10;


DELETE FROM employee;




----------------
DROP TABLE employee,department;


CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR(20) NOT NULL UNIQUE
)ENGINE=INNODB;


INSERT department(depName) VALUES('教学部'),
('市场部'),
('运营部'),
('督导部');


--创建员工表employee(子表)
--id ,username ,depID
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED,
FOREIGN KEY(depId) REFERENCES department(id) ON DELETE SET NULL ON UPDATE SET NULL
)ENGINE=INNODB;


INSERT employee(username,depId) VALUES('king',1),
('queen',2),
('张三',3),
('李四',4),
('王五',1);


--联合查询
SELECT username FROM employee UNION SELECT username FROM cms_user;


SELECT username FROM employee UNION ALL SELECT username FROM cms_user;


--由[NOT] IN引发的子查询


SELECT id FROM department;


SELECT id,username FROM employee WHERE depId IN(1,2,3,4);


SELECT id,username FROM employee WHERE depId IN(SELECT id FROM department);


INSERT employee(username,depId) VALUES('testtest',8);


--创建学院表student
--id username score
CREATE TABLE IF NOT EXISTS student(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
score TINYINT UNSIGNED
);


INSERT student(username,score) VALUES('king',95),
('king1',35),
('king2',45),
('king3',55),
('king4',65),
('king5',75),
('king6',80),
('king7',90),
('king8',25);


--创建奖学金schoolarship
--id,level


CREATE TABLE IF NOT EXISTS scholarship(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
level TINYINT UNSIGNED
);


INSERT scholarship(level) VALUES('90'),('80'),('70');




--查询获得1等奖学金的学员有


SELECT level FROM scholarship WHERE id=1;
SELECT id,username FROM student WHERE score>=90;
SELECT id,username FROM student WHERE score>=(SELECT level FROM scholarship WHERE id=1);


--查询部门表中
SELECT * FROM department WHERE id=5;


SELECT id,username FROM employee WHERE EXISTS(SELECT * FROM department WHERE id=5);
SELECT id,username FROM employee WHERE EXISTS(SELECT * FROM department WHERE id=4);

















版权声明:本文为博主原创文章,未经博主允许不得转载。



分享给朋友:
您可能感兴趣的文章:
随机阅读: