数据库表查询实例

# 创建部门表

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 WHERE emp.dept_id=dept.id;

#查询员工表的名称，性别。部门表的名称

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

-- 查询工资最高的员工信息

SELECT *FROM emp WHERE emp.salary IN(SELECT MAX(salary)FROM emp);

-- 查询员工工资小于平均工资的人

SELECT *FROM emp WHERE emp.salary <(SELECT AVG(salary)FROM emp);