1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123
| CREATE TABLE dept(id INT,deptName VARCHAR(20));
CREATE TABLE employee (id INT,empName VARCHAR(20),deptId INT);
CREATE TABLE salary (id INT PRIMARY KEY AUTO_INCREMENT,empId INT,money INT);
DELETE FROM dept; INSERT INTO dept VALUES(1,'工程部'); INSERT INTO dept VALUES(2,'销售部'),(3,'客服部'); INSERT INTO dept VALUES(5,'市场部');
DELETE FROM employee; INSERT INTO employee VALUES(1,'张三',1); INSERT INTO employee VALUES(2,'李四',1); INSERT INTO employee VALUES(3,'王五',1); INSERT INTO employee VALUES(4,'乔布斯',3); INSERT INTO employee VALUES(5,'比尔盖茨',2); INSERT INTO employee VALUES(6,'克林顿',2); INSERT INTO employee VALUES(7,'小布什',3); INSERT INTO employee VALUES(8,'希拉里',6);
INSERT INTO salary VALUES (NULL,1,1000); INSERT INTO salary VALUES (NULL,2,10000); INSERT INTO salary VALUES (NULL,3,10500); INSERT INTO salary VALUES (NULL,4,1300); INSERT INTO salary VALUES (NULL,5,1300); INSERT INTO salary VALUES (NULL,6,1300); INSERT INTO salary VALUES (NULL,7,1300); SELECT * FROM salary; DELETE FROM salary WHERE id = 7;
SELECT * FROM dept; SELECT * FROM employee;
SELECT employee.id,empName,deptName FROM employee,dept;
SELECT employee.id,employee.empName,dept.deptName FROM employee,dept WHERE employee.deptId = dept.id;
SELECT employee.id,employee.empName,dept.deptName,salary.money FROM employee,dept,salary WHERE employee.deptId = dept.id AND employee.id = salary.empId;
worldcity
SELECT dept.deptName,employee.empName FROM dept LEFT OUTER JOIN employee ON dept.id=employee.deptId;
SELECT dept.deptName,employee.empName FROM dept RIGHT OUTER JOIN employee ON dept.id=employee.deptId;
ALTER TABLE employee ADD bossId INT; UPDATE employee SET bossId = 1 WHERE id = 2; UPDATE employee SET bossId = 1 WHERE id = 3; UPDATE employee SET bossId = 1 WHERE id = 4; UPDATE employee SET bossId = 2 WHERE id = 5; UPDATE employee SET bossId = 2 WHERE id = 6;
SELECT a.empName,b.empName FROM employee AS a LEFT OUTER JOIN employee b ON a.bossId = b.id;
SELECT * FROM employee WHERE deptId IN (SELECT id FROM dept); SELECT * FROM employee WHERE deptId NOT IN (SELECT id FROM dept);
SELECT id FROM dept UNION SELECT deptId FROM employee; SELECT id FROM dept UNION ALL SELECT deptId FROM employee;
CREATE TABLE computer_stu( id INT PRIMARY KEY, NAME VARCHAR(20), score FLOAT );
CREATE TABLE scholarship ( LEVEL INT PRIMARY KEY, score INT );
INSERT INTO computer_stu VALUES(1001,'张三',85); INSERT INTO computer_stu VALUES(1002,'lisi',91); INSERT INTO computer_stu VALUES(1003,'乔布斯',75); INSERT INTO computer_stu VALUES(1004,'王五',77); INSERT INTO computer_stu VALUES(1005,'库克',65); INSERT INTO computer_stu VALUES(1006,'比尔',99); INSERT INTO computer_stu VALUES(1007,'克林顿',86); INSERT INTO computer_stu VALUES(1008,'小布什',70);
INSERT INTO scholarship VALUES(1,90); INSERT INTO scholarship VALUES(2,80); INSERT INTO scholarship VALUES(3,70);
SELECT id,NAME,score FROM computer_stu WHERE score >= (SELECT score FROM scholarship WHERE LEVEL = 1);
SELECT id,NAME,score FROM computer_stu WHERE score >= (SELECT score FROM scholarship WHERE LEVEL = 3); SELECT id,NAME,score FROM computer_stu WHERE score >= ANY (SELECT score FROM scholarship);
SELECT id,NAME,score FROM computer_stu WHERE score >= ALL (SELECT score FROM scholarship);
|