案例讲解

最新推荐文章于 2025-12-10 13:02:27 发布

原创最新推荐文章于 2025-12-10 13:02:27 发布 · 118 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

本文通过一系列SQL指令，演示了如何创建数据库表、插入数据、更新记录、删除条目以及数据查询等基本操作，是理解SQL语言和数据库管理的实用指南。

#1.运行以下脚本创建表`myemployees`
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
);

#2.显示表`my_employees`结构
DESC `my_employees`;

#3.向`my_employees`表中插入下列数据
ID    FIRST_NAME       LAST_NAME    USERID   SALARY
1     patel               Ralph      Rpatel  895
2     Dance               Betty      Bdance  860
3     Biri                Ben        Bbiri   1100
4     Newman              Chad       Chewman  750
5     Ropeburn            Audrey     Aropebur  1550
#方式一
INSERT INTO `my_employees`
VALUES(1,'patel','Ralph','Rpatel',895),
(2,'Dance','Betty','Bdance',860),
(3,'Biri','Ben','Bbiri',1100),
(4,'Newman','Chad','Chewman',750),
(5,'Ropeburn','Audrey ','Aropebur',1550);

#方式二
INSERT INTO `my_employees`
SELECT 1,'patel','Ralph','Rpatel',895
UNION 
SELECT 2,'Dance','Betty','Bdance',860
UNION
SELECT 3,'Biri','Ben','Bbiri',1100
UNION
SELECT 4,'Newman','Chad','Chewman',750
UNION
SELECT 5,'Ropeburn','Audrey ','Aropebur',1550;







#4.向users表中插入数据
1      Rpatel     10
2      Bdance     10
3      Bbiri      20
4      Chewman    30
5      Aropebur   40


INSERT INTO `users`
VALUES(1,'Rpatel',10),
(2,'Bdance',10),
(3,'Bbiri',20),
(4,'Chewman',30),
(5,'Aropebur',40);

#5.将3号员工的last_name修改为”drelxer“
UPDATE `my_employees`
SET `Last_name`='drelxer'
WHERE `Id`=3;
#6.将所有工资少于900的员工的工资修改为1000
UPDATE `my_employees`
SET `Salary`=1000
WHERE `Salary`<900;
#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';
#8.删除所有数据
DELETE FROM `my_employees`;
DELETE FROM `users`;
#9.检查所作的修正
SELECT * FROM `my_employees`;
SELECT * fro `users`;
#10.清空表`my_employees`
TRUNCATE TABLE `my_employees`;