mysql 基本操作练习
时间：2021-07-01 10:21:17
帮助过：2人阅读
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
=‘女‘;
View Code
mysql 基本操作练习
标签：
