已知表名my_employees,结构如下
要求完成以下功能
1.创建表结构
2.按上图添加5条记录
3.将3号员工的last_name修改为“drelxer”
4.将所有工资少于900的员工的工资修改为1000
5.查询出工资高800的员工信息
6.查询出userid以B开头的员工
7.按员工的工资从高到低排序后,只显示前3位员工的信息
8.删除ID为2的员工
1.
drop table my_employees;
create table my_employees(
ID int ,
FIRST_NAME varchar(16),
LAST_NAME varchar(16),
USERID varchar(16),
SALARY int,
constraint my_employees primary key(ID)
)
2.
insert into my_employees
values(1,'patel','Ralph','Rpatel',895),
(2,'Dancs','Betty','Bdancs',860),
(3,'Biri','Ben','Bbiri',1100),
(4,'Newman','Chad','Cnewman',750),
(5,'Ropeburn','Audrey','Aropebur',1550)
3.
alter table my_employees change LAST_NAME drelxer varchar(16)
4.
update my_employees set salary =1000 where salary<900
5.
select * from my_employees where salary>800
6.
select * from my_employees where userid like 'B%'
7.
select * from my_employees order by salary desc limit 3
8.
delete from my_employees where id=2
完整代码:
drop table my_employees;
create table my_employees(
ID int default 1,
FIRST_NAME varchar(16),
LAST_NAME varchar(16),
USERID varchar(16),
SALARY int,
constraint my_employees primary key(ID)
);
insert into my_employees
values(1,'patel','Ralph','Rpatel',895),
(2,'Dancs','Betty','Bdancs',860),
(3,'Biri','Ben','Bbiri',1100),
(4,'Newman','Chad','Cnewman',750),
(5,'Ropeburn','Audrey','Aropebur',1550);
alter table my_employees change LAST_NAME drelxer varchar(16);
-- 将所有工资少于900的员工的工资修改为1000
update my_employees set salary =1000 where salary<900;
-- 查询出工资高800的员工信息
select * from my_employees where salary>800;
-- 查询出userid以B开头的员工
select * from my_employees where userid like 'B%';
-- 按员工的工资从高到低排序后,只显示前3位员工的信息
select * from my_employees order by salary desc limit 3;
-- 删除ID为2的员工
delete from my_employees where id=2;