1 create table employee (name varchar(30), sex char(2), age int, address varchar(30));
2
3 insert into employee values ('张三', '女', 19, '北京');
4 insert into employee values ('李四', '男', 20, '上海');
5 insert into employee values ('王五', '女', 25, '广州');
6 insert into employee values ('薛六', '女', 20, '北京');
7 insert into employee values ('王五', '男', 22, '北京');
8 insert into employee values ('赵七', '男', 28, '上海');
9 insert into employee values ('张四', '女', 23, '北京');
10
11 #（1）. 写出sql语句，查询所有年龄大于20岁的员工（2分）
12 select *from employee where age>20;
13 #（2）. 写出sql语句，查询所有年龄小于25岁的女性员工（3分）
14 select *from employee where sex='女' and age<25;
15 #（3）. 写出sql语句，统计男女员工各有多少名（3分）
16 #count 函数
17 select count(*) from employee;#统计表元素个数
18 select count(*) from employee where sex='女';#统计女生个数
19 select sex,count(*) from employee group by sex;#group by sex 按性别分组，用性别标记分组
20 select sex,count(*) as num from employee group by sex;#as 起别名
21
22 #（4）. 写出sql语句，按照年龄倒序获取员工信息（3分）
23 select * from employee order by age desc;
24 #（5）. 写出sql语句，获取员工中哪个姓名具有重名现象（3分）
25 SELECT *
26 FROM employee
27 WHERE name IN (SELECT name
28 FROM employee
29 GROUP BY name
30 HAVING COUNT(*) > 1)
31 #（6）. 写出sql语句，查询所有姓张的员工（3分）
32 select * from employee where name like '%张%';#模糊查询，只要含张字
33 select * from employee where name like '张%';#只要姓张
34 #占位符写法
35 select * from employee where name like '张__';
36 #（7）. 写出sql语句，查询住址为北京的前3条记录（3分）
37 select * from employee where address='北京' order by name asc limit 0,3;#从下标几开始 ，几个
38 select * from employee limit 3,3;
39 #（8）. 写出sql语句，查询员工总数（3分）
40 select count(*) as allnum from employee;
41 #（9）. 写出sql语句，向表中插入一条记录（2分）
42 insert into employee(name,sex,age,address) values('七七','男',20,'深圳');
43 #（10）.写出sql语句，修改员工张四的住址为南京（2分）
44 update employee set address='南京' where name='张四';
45 #（11）.写出sql语句，删除年龄大于24岁的女员工（2分）
46 delete from employee where age>24 and sex='女';
