mysql 基本操作练习

create table employee (name varchar(30), sex char(2), age int, address varchar(30));

insert into employee values ('张三', '女', 19, '北京');
insert into employee values ('李四', '男', 20, '上海');
insert into employee values ('王五', '女', 25, '广州');
insert into employee values ('薛六', '女', 20, '北京');
insert into employee values ('王五', '男', 22, '北京');
insert into employee values ('赵七', '男', 28, '上海');
insert into employee values ('张四', '女', 23, '北京');

#（1）. 写出sql语句，查询所有年龄大于20岁的员工（2分）
select *from employee where age>20;
#（2）. 写出sql语句，查询所有年龄小于25岁的女性员工（3分）
select *from employee where sex='女' and age<25;
#（3）. 写出sql语句，统计男女员工各有多少名（3分）
#count 函数 
select count(*) from employee;#统计表元素个数
select count(*) from employee where sex='女';#统计女生个数
select sex,count(*) from employee group by sex;#group by sex 按性别分组，用性别标记分组
select sex,count(*) as num from employee group by sex;#as 起别名

#（4）. 写出sql语句，按照年龄倒序获取员工信息（3分）
select * from employee order by age desc;
#（5）. 写出sql语句，获取员工中哪个姓名具有重名现象（3分）
SELECT *  
  FROM employee  
 WHERE name IN (SELECT name  
                  FROM employee  
                 GROUP BY name  
                HAVING COUNT(*) > 1)  
#（6）. 写出sql语句，查询所有姓张的员工（3分）
select * from employee where name like '%张%';#模糊查询，只要含张字
select * from employee where name like '张%';#只要姓张
#占位符写法
select * from employee where name like '张__';
#（7）. 写出sql语句，查询住址为北京的前3条记录（3分）
select * from employee where address='北京'  order by name asc limit 0,3;#从下标几开始 ，几个
select * from employee limit 3,3;
#（8）. 写出sql语句，查询员工总数（3分）
select count(*) as allnum from employee;
#（9）. 写出sql语句，向表中插入一条记录（2分）
insert into employee(name,sex,age,address) values('七七','男',20,'深圳');
#（10）.写出sql语句，修改员工张四的住址为南京（2分）
update employee set address='南京' where name='张四';
#（11）.写出sql语句，删除年龄大于24岁的女员工（2分）
delete from employee where age>24 and sex='女';