MySQL - 基础题练习题 - 增删改(10)

增删改篇

11.1.运行以下脚本创建表my_employees

USE myemployees;
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 );

11.2.显示表my_employees的结构

DESC my_employees;

11.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 );
#方式二：
INSERT INTO my_employees SELECT
1,
'patel',
'ralph',
'rpatel',
895 UNION
SELECT
	2,
	'dancs',
	'betty',
	'bdancs',
	860 UNION
SELECT
	3,
	'biri',
	'ben',
	'bbiri',
	1100 UNION
SELECT
	4,
	'newman',
	'chad',
	'cnewman',
	750 UNION
SELECT
	5,
	'ropeburn',
	'audrey',
	'aropebur',
	1550;

11.4.向表users中插入以下数据

INSERT INTO users
VALUES
	( 1, 'rpatel', 10 ),
	( 2, 'bdancs', 10 ),
	( 3, 'bbiri', 20 ),
	( 4, 'cnewman', 30 ),
	( 5, 'aropebur', 40 );

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

UPDATE my_employees 
SET last_name = "drelxer" 
WHERE
	id = 3;

11.6.将所有工资少与900的员工的工资修改为1000

UPDATE my_employees 
SET salary = 1000 
WHERE
	salary < 900;

11.7.将userid为bbiri的user表和my_employees表的记录全部删除

DELETE u,
e 
FROM
	users u
	JOIN my_employees e ON u.userid = e.userid 
WHERE
	u.userid = "bbiri";

11.8.删除所有的数据

DELETE 
FROM	
	my_employees;
DELETE 
FROM
	users;

11.9.检查所做的修正

SELECT
	* 
FROM
	my_employees;
SELECT
	* 
FROM
	users;

11.10.清空表my_employees

TRUNCATE TABLE my_employees;

12.1.创建表dept1

name null type
id		int(7)
name	varchar(25)
#代码：
CREATE TABLE dept1 (
id INT ( 7 ),
NAME VARCHAR ( 25 ));

12.2.将表department中的数据插入新表dept2

CREATE TABLE dept2 SELECT
department_id,
department_name 
FROM
	myemployees.departments;	

12.3.创建表emp5

CREATE TABLE emp5 (
	id INT ( 7 ),
	first_name VARCHAR ( 25 ),
last_name VARCHAR ( 25 ),
dept_id INT ( 7 ));

12.4.将列last_name的长度增加到50

ALTER TABLE emp5 MODIFY COLUMN last_name VARCHAR ( 50 );

12.5.根据表employees创建employees2

CREATE TABLE employees2 LIKE myemployees.employees;

12.6.删除表emp5

DROP TABLE
IF
	EXISTS emp5;

12.7.将表employees重命名为emp5

ALTER TABLE employees RENAME TO emp5;

12.8.在表emp5中添加新列test_column，并检查所做的操作

ALTER TABLE emp5 ADD COLUMN test_column INT;

12.9.直接删除表emp5中的列dept_id

ALTER TABLE emp5 DROP COLUMN dept_id;

有问题的评论交流，及时更正，谢谢关注