©2020-2024 ioDraw All rights reserved,
Privacy Policy
CREATE TABLE employee(
id INT,
name VARCHAR(20),
PASSWORD VARCHAR(20),
gender VARCHAR(10),
age INT DEFAULT 25,
email VARCHAR(50),
salary DOUBLE(8,2),
state INT,
dept VARCHAR(10),
registTime datetime
);
INSERT INTO EMPLOYEE
VALUES(1,'丛建华','jhcongabc123','男',18,'[email protected]',5000,1,'财务部','2015-09-10
12:18:38');
INSERT INTO EMPLOYEE
VALUES(2,'金国学','yjgx','男',32,'[email protected]',4500,0,'咨询部','2015-09-12 12:18:38');
INSERT INTO EMPLOYEE
VALUES(3,'林靖儿','753951','女',42,'[email protected]',5200,1,'技术部','2015-09-18
12:18:38');
INSERT INTO EMPLOYEE
VALUES(4,'李彦彬','pangzi','男',29,'[email protected]',NULL,0,'技术部','2015-08-10
12:18:38');
INSERT INTO EMPLOYEE
VALUES(5,'赵雅君','zhaoyajun123','女',22,'[email protected]',3000,1,'咨询部','2014-09-19
12:18:38');
INSERT INTO EMPLOYEE
VALUES(6,'石萌','sm123','女',25,'[email protected]',8000,0,'技术部','2014-09-10 12:18:38');
INSERT INTO EMPLOYEE
VALUES(7,'王雯娟','lcywang123','女',58,'[email protected]',12000,0,'技术部','2015-03-28
12:18:38');
INSERT INTO EMPLOYEE
VALUES(8,'石秀清','sxq123','女',42,'[email protected]',5900,1,'财务部','2015-09-10
12:18:38');
INSERT INTO EMPLOYEE
VALUES(9,'王洪亮','szg123','男',18,'[email protected]',8000,0,'财务部','2015-09-10
12:18:38');
INSERT INTO EMPLOYEE
VALUES(10,'李国兴','gdlgx123','男',33,'[email protected]',8500,1,'咨询部','2014-12-12
12:18:38');
INSERT INTO EMPLOYEE VALUES(11,'Edward
Cullen','jhcongabc123','男',18,'[email protected]',5300,1,'财务部','2015-09-10
12:18:38');
INSERT INTO EMPLOYEE VALUES(12,'Jasper
Hale','yjgx','男',20,'[email protected]',4800,0,'行政部','2015-09-12 12:18:38');
INSERT INTO EMPLOYEE VALUES(13,'Bella
Swan','753951','女',42,'[email protected]',5600,1,'技术部','2015-09-18 12:18:38');
INSERT INTO EMPLOYEE VALUES(14,'Jacob
Black','pangzi','男',29,'[email protected]',NULL,0,'行政部','2015-08-10 12:18:38');
INSERT INTO EMPLOYEE VALUES(15,'Esme
Cullen','esme123','女',26,'[email protected]',4000,1,'行政部','2014-09-19 12:18:38');
INSERT INTO EMPLOYEE VALUES(16,'Alice
Cullen','alice123','女',25,'[email protected]',8300,0,'技术部','2014-09-10 12:18:38');
INSERT INTO EMPLOYEE VALUES(17,'Rose
Hale','lcywang123','女',18,'[email protected]',12800,0,'行政部','2015-03-28 12:18:38');
INSERT INTO EMPLOYEE VALUES(18,'Emmett
Cullen','sxq123','女',42,'[email protected]',5100,1,'财务部','2015-09-10 12:18:38');
INSERT INTO EMPLOYEE VALUES(19,'Carlise
Cullen','szg123','男',18,'[email protected]',8500,0,'财务部','2015-09-10 12:18:38');
INSERT INTO EMPLOYEE VALUES(20,'Charlie
Swan','gdlgx123','男',33,'[email protected]',8500,1,'技术部','2014-12-12 12:18:38');
#------------------------基本查询--------------------------
#####全部查询#####
#1. 查询表的所有数据
#####指定字段查询#####
#2. 查询每个员工的姓名和工资
#####去重查询#####
#3. 查询所有的部门
#####运算查询#####
#4. 查询每个员工的姓名，和增加500奖金之后的工资
# 工资为null，按0计算
# 查询结果列起别名
#------------------------条件查询--------------------------
#####单条件查询#####
#5. 查询所有年纪大于30岁的员工信息(或者：小于30的、大于等于30、小于等于30、不等于30)
######多条件查询#####
#6. 查询所有年纪大于30岁的女员工信息
#7. 查询收入大于8000的男员工 或者 年纪小于30的女员工
#####范围查询#####
#8. 查询年纪大于25 并且 小于40的员工
#9. 查询年纪范围处于25~40（包含25 和 40）的员工
#10. 查询id的值为1，或者3，或者5的员工信息
#####空查询#####
#11. 查询工资salary为空的员工信息
#12. 查询工资salary不为空的员工信息
#####模糊查询#####
# %匹配任意个任意字符
#13. 查询所有姓王的员工
#14. 查询所有名字中带有“国”字的员工
# _匹配单个任意字符
#15. 查询所有姓石的，名字为两个字的员工
#16. 查询所有名字为两个字的员工
#####排序查询#####
#17. 查询所有员工信息，按照工资从高到低排序
#18. 查询所有员工信息，按照工资从高到低排序，如果工资一样，按照年龄从小到大排序
#####聚合查询#####
#19. 查询employee表里员工的数量
#20. 查询给所有员工总共发了多少工资
#21. 查询所有员工的平均工资
#22. 查询男性员工里最大的年龄是多少，最低的工资是多少
#####分组查询#####
#23. 查询每个部门有多少个人
#24. 查询人数大于5的部门名称，及这个部门的人数