DML数据操作练习

1. 创建表my_employees、users

   create table my_employees(
   	id int(10),
   	first_name varchar(10),
   	last_name varchar(10),
   	userid varchar(10),
   	salary double(10,2)
   );
   create table users(
   	id int,
   	userid varchar(10),
   	department_id int
   );
   

   

2. 显示表my_employees的结构

   desc my_employees;
   

   

3. 向my_employees表中插入下列数据
   

   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)
   

4. 将3号员工的last_name修改为“drelxer”

   update my_employees set last_name = 'drelxer' where id = 3;
   

5. 将所有工资少于900的员工的工资修改为1000

   update my_employees set salary = '1000' where salary < 900;
   

6. 将userid 为Bbiri的user表和my_employees表的记录全部删除

   delete u,m
   from users u,my_employees m
   where u.userid = m.userid and u.userid = 'Bbiri';
   

7. 删除所有数据

   delete from users;
   delete from my_employees;
   

8. 清空表my_employees

   truncate table users;
   truncate table my_employees;