做一个MySQL有关的基础实训练习题附带代码
时间: 2024-02-29 09:52:21 浏览: 14
好的,以下是一些MySQL基础实训练习题和代码示例:
1. 创建一个名为“employees”的表,包含以下列:编号(id),名字(name),年龄(age),性别(gender),地址(address)和薪水(salary)。
```mysql
CREATE TABLE employees (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL,
age INT(3) NOT NULL,
gender VARCHAR(10),
address VARCHAR(50),
salary DECIMAL(10,2)
);
```
2. 向“employees”表中插入一些数据:
```mysql
INSERT INTO employees (name, age, gender, address, salary)
VALUES ('John Smith', 25, 'Male', '123 Main St, Anytown USA', 50000.00),
('Jane Doe', 30, 'Female', '456 Broad St, Anytown USA', 60000.00),
('Bob Johnson', 40, 'Male', '789 Oak St, Anytown USA', 70000.00);
```
3. 查询“employees”表中所有员工的信息:
```mysql
SELECT * FROM employees;
```
4. 查询“employees”表中薪水高于60000的员工信息:
```mysql
SELECT * FROM employees WHERE salary > 60000;
```
5. 修改“employees”表中编号为2的员工姓名为“Mary Smith”:
```mysql
UPDATE employees SET name = 'Mary Smith' WHERE id = 2;
```
6. 删除“employees”表中年龄小于30的员工:
```mysql
DELETE FROM employees WHERE age < 30;
```
7. 查询“employees”表中男性员工的平均薪水:
```mysql
SELECT AVG(salary) FROM employees WHERE gender = 'Male';
```
希望这些示例能够帮助你进行MySQL基础实训练习。