数据库基本查询创建

创建部门表

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 emp,dept;

查询所有员工信息和对应的部门信息
SELECT * FROM emp,dept WHERE emp.dept_id = dept.id;

查询员工表的名称，性别和部门表的名称
SELECT emp.NAME,emp.gender,dept.NAME FROM emp,dept WHERE emp.dept_id = dept.id;

SELECT
e.NAME,
e.gender,
d.NAME
FROM
emp e,
dept d
WHERE e.dept_id = d.id

3.子查询
查询工资最高的员工信息
1.查最高的工资
SELECT MAX(salary) FROM emp ;
2.查员工信息，条件是工资等于最高工资(9000)的员工
SELECT * FROM emp WHERE salary = 9000;
SELECT * FROM emp WHERE salary = (SELECT MAX(salary) FROM emp);

查询员工信息，工资小于平均工资的员工
SELECT * FROM emp WHERE salary < (SELECT AVG(salary) FROM emp);

查询财务部和市场所有员工信息
SELECT id FROM dept WHERE NAME = ‘市场部’ OR NAME = ‘财务部’;
SELECT * FROM emp WHERE dept_id = 1 OR dept_id = 2;
SELECT * FROM emp WHERE dept_id IN(SELECT id FROM dept WHERE NAME = ‘市场部’ OR NAME = ‘财务部’)