SQL——数据处理之增删改

练习1

1. 创建数据库dbtest11

CREATE DATABASE IF NOT EXISTS dbtest11 CHARACTER SET 'utf8';

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

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

3. 显示表my_employees的结构

DESC my_employees;

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

INSERT INTO my_employees
VALUES(1, 'patel', 'Ralph', 'Rpatel', 895),
(2, 'Dancs', 'Betty', 'Bdancs', 860),
(3, 'Biri', 'Ben', 'Bdiri', 1100),
(4, 'Newman', 'Chad', 'Cnewman', 750),
(5, 'Ropeburn', 'Audrey', 'Aropebur', 1550);

5. 向users表中插入数据

INSERT INTO users
VALUES(1, 'Rpatel', 10),
(2, 'Bdancs', 10),
(3, 'Bdiri', 20),
(4, 'Cnewman', 30),
(5, 'Aropebur', 40);

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

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

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

UPDATE my_employees SET salary = 1000 WHERE salary < 900;

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

DELETE FROM `users` WHERE userid = 'Bdiri';
DELETE FROM my_employees WHERE userid = 'Bdiri';

9. 删除my_employees、users表所有数据

DELETE FROM users;
DELETE FROM my_employees;

10. 检查所作的修正

SELECT * FROM users;
SELECT * FROM my_employees;

11. 清空表my_employees

TRUNCATE TABLE my_employees;

练习2

1. 使用现有数据库dbtest11

use dbtest11;

2. 创建表格pet

3. 添加记录

INSERT INTO pet
VALUES('Fluffy', 'harold', 'Cat', 'f', 2003, 2010),
('Claws', 'gwen', 'Cat', 'm', 2004, NULL),
('Buffy', NULL, 'Dog', 'f', 2009, NULL),
('Fang', 'benny', 'Dog', 'm', 2000, NULL),
('bowser', 'diane', 'Dog', 'm', 2003, 2009),
('Chirpy', NULL, 'Bird', 'f', 2008, NULL);

4. 添加字段:主人的生日owner_birth DATE类型。

ALTER TABLE pet ADD COLUMN owner_birth DATE;

5. 将名称为Claws的猫的主人改为kevin

UPDATE pet SET owner = 'kevin' WHERE name = 'Claws' AND species = 'Cat';

6. 将没有死的狗的主人改为duck

UPDATE pet SET owner = 'duck' WHERE death IS NULL AND species = 'Dog';

7. 查询没有主人的宠物的名字

SELECT name
FROM pet
WHERE owner IS NULL;

8. 查询已经死了的cat的姓名，主人，以及去世时间

SELECT name, owner, death
FROM pet
WHERE species = 'Cat'
AND death IS NOT NULL;

9. 删除已经死亡的狗

DELETE FROM pet WHERE species = 'Dog' AND death IS NOT NULL;

10. 查询所有宠物信息

SELECT * FROM pet;

练习3

1. 使用现有数据库dbtest11

use dbtest11;

2. 创建表employee，并添加记录

CREATE TABLE employee(
	id INT,
	name VARCHAR(20),
	sex char(1),
	tel VARCHAR(20),
	addr VARCHAR(20),
	salary DOUBLE
);

INSERT INTO employee
VALUES(10001, '张一一', '男', '13456789000', '山东青岛', 1001.58),
(10002, '刘小红', '女', '13454319000', '河北保定', 1201.21),
(10003, '李四', '男', '0751-1234567', '广东佛山', 1004.11),
(10004, '刘小强', '男', '0755-5555555', '广东深圳', 1501.23),
(10005, '王艳', '女', '020-1232133', '广东广州', 1405.16);

3. 查询出薪资在1200~1300之间的员工信息。

SELECT * 
FROM employee
WHERE salary BETWEEN 1200 AND 1300;

4. 查询出姓“刘”的员工的工号，姓名，家庭住址。

SELECT id, name, addr
FROM employee
WHERE name LIKE '刘%';

5. 将“李四”的家庭住址改为“广东韶关”

UPDATE employee SET addr = '广东韶关' WHERE name = '李四';

6. 查询出名字中带“小”的员工

SELECT * 
FROM employee
WHERE name LIKE '%小%';