【MySQL】深圳大学数据库实验一

一、实验目的

了解DBMS系统的功能、软件组成；
掌握利用SQL语句定义、操纵数据库的方法。

二、实验要求

在课外安装相关软件并浏览软件自带的帮助文件和功能菜单，了解DBMS的功能、结构；
创建一个有两个关系表的数据库；（建议采用MYSQL）
数据库、关系表定义；
学习定义关系表的约束(主键、外键、自定义)；
了解SQL的数据定义功能；
了解SQL的操纵功能；
掌握典型的SQL语句的功能；
了解视图的概念；

三、实验设备

    计算机、数据库管理系统如MYSQL等软件。

四、建议的实验步骤

    使用SQL语句建立关系数据库模式及数据如下：（注：数据要自己输入）

4.1 使用SQL语句创建如上两张关系表

创建表格（CREATE TABLE）：用于创建一个新表格并定义其结构。

CREATE TABLE table_name (
    column1 datatype [constraint],
    column2 datatype [constraint],
    ...
    [table_constraints]
);

**拓展： **

修改表格**(

ALTER TABLE

)**：

ALTER TABLE table_name
    ADD column_name datatype [constraint],
    DROP column_name,
    MODIFY COLUMN column_name datatype [constraint],
    CHANGE COLUMN old_column_name new_column_name datatype [constraint];

**删除表格(

DROP TABLE

)**：

DROP TABLE table_name;

**重命名表格(

RENAME TABLE

)**：

RENAME TABLE old_table_name TO new_table_name;

**创建和删除索引 (

CREATE INDEX // DROP INDEX

):**

CREATE INDEX index_name ON table_name (column_name);

DROP INDEX index_name ON table_name;

**创建和删除视图(

CREATE VIEW //

DROP VIEW)**

-- 创建视图
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

-- 删除视图
DROP VIEW view_name;

**创建emp和dept表格 **

create table dept2022150212
(
    DEPTNO int not null ,
    DNAME varchar(30) not null ,
    LOC varchar(30) not null ,
    PRIMARY KEY (DEPTNO)
);

create table emp2022150212
(
    EMPNO int not null,
    ENAME varchar(30) not null,
    JOB varchar(30) not null,
    MGR int,
    HIREDATE varchar(30),
    SAL int,
    COMM int,
    DEPTNO int not null,
    primary key (EMPNO)
    
);

** 插入数据 (

INSERT INTO

) ：用于向表格中插入新记录。**

** 拓展：
更新数据 (

UPDATE

)：**

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

**删除数据 (

DELETE

)：**

DELETE FROM table_name
WHERE condition;

** 批量插入、更新、删除**

-- 批量插入
INSERT INTO employees (name, salary, hire_date, manager_id)
VALUES 
    ('Emily Davis', 6200.00, '2024-09-01', 4),
    ('Michael Brown', 6700.00, '2024-08-20', 4);

-- 批量更新
UPDATE employees
SET salary = salary * 1.05
WHERE hire_date < '2024-01-01';

-- 批量删除
DELETE FROM employees
WHERE hire_date < '2024-01-01';

使用事务：在执行 DML 操作时，特别是涉及多条记录的操作时，可以使用事务来确保数据的完整性。事务包括

START TRANSACTION

、

COMMIT

和

ROLLBACK

操作。

-- 开始事务
START TRANSACTION;

-- 执行多个 DML 操作
UPDATE employees SET salary = salary * 1.10 WHERE department = 'Sales';
DELETE FROM employees WHERE hire_date < '2023-01-01';

-- 提交事务
COMMIT;

-- 如果出现错误，可以回滚事务
ROLLBACK;

插入数据

// 插入部门数据
insert into dept2022150212 (DEPTNO, DNAME, LOC) values (10, 'ACCOUNTING', 'LONDON');
insert into dept2022150212 (DEPTNO, DNAME, LOC) values (20, 'RESEARCH', 'PRESTON');
insert into dept2022150212 (DEPTNO, DNAME, LOC) values (30, 'SALES', 'LIVERPOOL');
insert into dept2022150212 (DEPTNO, DNAME, LOC) values (40, 'OPERATIONS', 'STAFFORD');
insert into dept2022150212 (DEPTNO, DNAME, LOC) values (50, 'MARKETING', 'LUTON');

// 插入员工数据
insert into emp2022150212 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7369, 'SMITH', 'CLERK', 7902, '17-Dec-90', 13750, 0, 20);
insert into emp2022150212 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7499, 'ALLEN', 'SALESMAN', 7698, '20-FEB-89', 19000, 6400, 30);
insert into emp2022150212 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7521, 'WARD', 'SALESMAN', 7698, '22-FEB-93', 18500, 4250, 30);
insert into emp2022150212 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7566, 'JONES', 'MANAGER', 7839, '02-APR-89', 26850, 0, 20);
insert into emp2022150212 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7654, 'MARTIN', 'SALESMAN', 7698, '28-SEP-97', 15675, 3500, 30);
insert into emp2022150212 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7698, 'BLAKE', 'MANAGER', 7839, '01-MAY-90', 24000, 0, 30);
insert into emp2022150212 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7782, 'CLARK', 'MANAGER', 7839, '09-JUN-88', 27500, 0, 10);
insert into emp2022150212 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7788, 'SCOTT', 'ANALYST', 7566, '19-APR-87', 19500, 0, 20);
insert into emp2022150212 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7839, 'KING', 'PRESIDENT', 7902, '17-NOV-83', 82500, 0, 10);
insert into emp2022150212 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7844, 'TURNER', 'SALESMAN', 7698, '08-SEP-92', 18500, 6250, 30);
insert into emp2022150212 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7876, 'ADAMS', 'CLERK', 7788, '23-MAY-96', 11900, 0, 20);
insert into emp2022150212 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7900, 'JAMES', 'CLERK', 7698, '03-DEC-95', 12500, 0, 30);
insert into emp2022150212 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7902, 'FORD', 'ANALYST', 7566, '03-DEC-91', 21500, 0, 20);
insert into emp2022150212 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7934, 'MILLER', 'CLERK', 7782, '23-JAN-95', 13250, 0, 10);
insert into emp2022150212 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (3258, 'GREEN', 'SALESMAN', 4422, '24-Jul-95', 18500, 2750, 50);
insert into emp2022150212 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (4422, 'STEVENS', 'MANAGER', 7839, '14-Jan-94', 24750, 0, 50);
insert into emp2022150212 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (6548, 'BARNES', 'CLERK', 4422, '16-Jan-95', 11950, 0, 50);

4.2 EXERCISES.1SIMPLE COMMANDS

-- 1.List all information about the employees.
select * from emp2022150212;

-- 2.List all information about the departments
select * from dept2022150212;

-- 3.List only the following information from the EMP table ( Employee     name, employee number, salary, department number)
select ENAME, EMPNO, SAl, DEPTNO  from emp2022150212;

-- 4.List details of employees in departments 10 and 30.
select * from emp2022150212 where DEPTNO between 10 and 30;

-- 5.List all the jobs in the EMP table eliminating duplicates.
select JOB from emp2022150212 group by JOB;

-- 6.What are the names of the employees who earn less than £20,000?
select ENAME from emp2022150212 where SAL <= 20000;

-- 7.What is the name, job title and employee number of the person in department 20 who earns more than £25000?
select ENAME, JOB, EMPNO from emp2022150212 where DEPTNO = 20 and SAL > 25000;

-- 8.Find all employees whose job is either Clerk or Salesman.
select * from emp2022150212 where JOB = 'Clerk' or JOB = 'Salesman';

-- 9.Find any Clerk who is not in department 10.
select * from emp2022150212 where DEPTNO != 10 and JOB = 'Clerk';

-- 10.Find everyone whose job is Salesman and all the Analysts in department 20.
select * from emp2022150212 where JOB = 'Salesman' and  DEPTNO = 20;

-- 11.Find all the employees who earn between £15,000 and £20,000. Show the employee name, department and salary.
select ENAME, DEPTNO, SAL from emp2022150212 where SAL between  15000 and 20000;

-- 12.Find the name of the President.
select ENAME from emp2022150212 where JOb = 'President';

-- 13.Find all the employees whose last names end with S
select * from emp2022150212 where ENAME like '%S';

-- 14.List the employees whose names have TH or LL in them
Select * from emp2022150212 where ENAME like '%TH%' or '%LL%';

-- 15.List only those employees who receive commission.
select * from emp2022150212 where COMM != 0;

-- 16.Find the name, job, salary, hiredate, and department number of all employees by alphabetical order of name.
select ENAMe, JOB, SAL, HIREDATE, DEPTNO from emp2022150212 order by ENAME;

-- 17.Find the name, job, salary, hiredate and department number of all employees in ascending order by their salaries.
select ENAME, JOB, SAL, HIREDATE, DEPTNO from emp2022150212 order by SAL asc ;

-- 18.List all salesmen in descending order by commission divided by their salary.
select * from emp2022150212 where JOB = 'Salesman' order by (COMM / SAL) desc;

-- 19.Order employees in department 30 who receive commision, in ascending order by commission
select * from emp2022150212 where DEPTNO = 30 and COMM != 0 order by COMM desc;

-- 20.Find the names, jobs, salaries and commissions of all employees who do not have managers.
select ENAME, JOB, SAL, COMM from emp2022150212 where JOB != 'Managers';

-- 21.Find all the salesmen in department 30 who have a salary greater than or equal to £18000.
select * from emp2022150212 where JOB = 'Salesman' and DEPTNO = 30 and SAL >= 18000;

4.3 EXERCISES 2 JOINS

JOIN

用于将两个或多个表格中的记录基于相关列连接在一起。

JOIN

分为内连接（

INNER JOIN

）、左外连接（

LEFT JOIN

或

LEFT OUTER JOIN

）、右外连接（

RIGHT JOIN

或

RIGHT OUTER JOIN

）和全外连接（

FULL JOIN

或

FULL OUTER JOIN

）。

4.3.1 左外连接（

LEFT JOIN

或

LEFT OUTER JOIN

）

左外连接返回左表中的所有记录以及右表中匹配的记录。如果右表中没有匹配的记录，结果集中会包含左表中的记录和右表中列的

NULL

值。

语法：

SELECT columns
FROM left_table
LEFT JOIN right_table
ON left_table.common_column = right_table.common_column;

4.3.2 右外连接（

RIGHT JOIN

或

RIGHT OUTER JOIN

）

右外连接返回右表中的所有记录以及左表中匹配的记录。如果左表中没有匹配的记录，结果集中会包含右表中的记录和左表中列的

NULL

值。

语法：

SELECT columns
FROM left_table
RIGHT JOIN right_table
ON left_table.common_column = right_table.common_column;

4.3.3 全外连接（

FULL JOIN

或

FULL OUTER JOIN

）

全外连接返回左表和右表中的所有记录，匹配的记录会连接在一起。如果某一表中的记录在另一表中没有匹配项，则结果集中会包含该记录以及另一表中列的

NULL

值。注意，MySQL 并不直接支持

FULL OUTER JOIN

，可以通过联合

LEFT JOIN

和

RIGHT JOIN

实现。

语法：

SELECT columns
FROM left_table
LEFT JOIN right_table
ON left_table.common_column = right_table.common_column

UNION

SELECT columns
FROM left_table
RIGHT JOIN right_table
ON left_table.common_column = right_table.common_column;

4.3.4 段小结

左外连接（LEFT JOIN 或 LEFT OUTER JOIN）：返回左表中的所有记录及右表中匹配的记录，如果没有匹配，则右表的列为 NULL。
右外连接（RIGHT JOIN 或 RIGHT OUTER JOIN）：返回右表中的所有记录及左表中匹配的记录，如果没有匹配，则左表的列为 NULL。
全外连接（FULL JOIN 或 FULL OUTER JOIN）：返回左表和右表中的所有记录，包括没有匹配的记录，MySQL 需要通过联合 LEFT JOIN 和 RIGHT JOIN 来实现。

-- 1.Find the name and salary of employees in Luton.
select e.ENAME, e.SAL
from emp2022150212 e
         left join dept2022150212 d on e.DEPTNO = d.DEPTNO
where e.DEPTNO = d.DEPTNO
  and d.LOC = 'LUTON';

-- 2.Join the DEPT table to the EMP table and show in department number order.
select *
from emp2022150212 e
         left join dept2022150212 d on e.DEPTNO = d.DEPTNO
order by e.DEPTNO;

-- 3.List the names of all salesmen who work in SALES
select e.ENAME
from emp2022150212 e
         left join dept2022150212 d on e.DEPTNO = d.DEPTNO
where d.DNAME = 'SALES'
  and e.JOB = 'Salesman';

-- 4.List all departments that do not have any employees.
select d.DNAME
from emp2022150212 e
         right join dept2022150212 d on e.DEPTNO = d.DEPTNO
group by d.DNAME
having count(e.DEPTNO) = 0;

-- 5.For each employee whose salary exceeds his manager's salary, list the     employee's name and salary and the manager's name and salary.
select e.ENAME, e.SAL, t.ENAME, t.SAL
from emp2022150212 e
         left join emp2022150212 t on e.DEPTNO = t.DEPTNO
where e.SAL > t.SAL
  and t.JOB = 'Manager'
  and e.JOB != 'Manager';

-- 6.List the employees who have BLAKE as their manager.
select e.DEPTNO, e.ENAME, t.DEPTNO, t.ENAME
from emp2022150212 e
         right join emp2022150212 t on e.DEPTNO = t.DEPTNO
where t.ENAME = 'BLAKE'
  and e.ENAME != 'BLAKE';

4.4 EXERCISES 3 FUNCTIONS

在 MySQL 中，聚合函数用于对一组值执行计算，通常用于生成汇总数据。这些函数可以在 SQL 查询中帮助你统计、计算或总结数据。以下是 MySQL 中常见的聚合函数及其用法：

1.

COUNT()

计算行数或特定列中的非空值的数量。

SELECT COUNT(column_name) FROM table_name;

2.

SUM()

计算一列数值的总和。

SELECT SUM(column_name) FROM table_name;

3.

AVG()

计算一列数值的平均值。

SELECT AVG(column_name) FROM table_name;

4.

MIN()

获取一列数值中的最小值。

SELECT MIN(column_name) FROM table_name;

5.

MAX()

获取一列数值中的最大值。

SELECT MAX(column_name) FROM table_name;

**练习： **

-- 1.Find how many employees have a title of manager without listing them.
select count(*)
from emp2022150212 e
where e.JOB = 'Manager';

-- 2.Compute the average annual salary plus commission for all salesmen
select avg(e.SAL + e.COMM)
from emp2022150212 e
where e.JOB = 'Salesman';

-- 3.Find the highest and lowest salaries and the difference between them (single SELECT statement)
select max(e.SAL), min(e.SAL), max(e.SAL) - min(e.SAL)
from emp2022150212 e;

-- 4.Find the number of characters in the longest department name
select max(LENGTH(d.DNAME))
from dept2022150212 d;

-- 5.Count the number of people in department 30 who receive a salary and the number of people who receive a commission (single statement).
select count(e.SAL), count(e.COMM)
from emp2022150212 e
where e.DEPTNO = 30;

-- 6.List the average commission of employees who receive a commission, and the average commission of all employees (assume employees who do not receive a commission attract zero commission)
select avg(e.COMM), avg(t.COMM)
from emp2022150212 e,
     emp2022150212 t
where e.COMM != 0;

-- 7.List the average salary of employees that receive a salary, the average commission of employees that receive a commission, the average salary     plus commission of only those employees that receive a     commission and the average salary plus commission of all employees including  those that do not receive a commission. (single statement)
select avg(e.SAL), avg(t.COMM), avg(g.SAL + g.COMM), avg(h.SAL + h.COMM)
from emp2022150212 e,
     emp2022150212 t,
     emp2022150212 g,
     emp2022150212 h
where e.SAL != 0
  and t.COMM != 0
  and g.COMM != 0;

-- 8.Compute the daily and hourly salary for employees in department 30, round to the nearest penny. Assume there are 22 working days in a month and 8 working hours in a day.
select e.ENAME, round(e.SAL / 22) 月薪, round(e.SAl / 22 / 8) 日薪
from emp2022150212 e
where e.DEPTNO = 30;

-- 9.Issue the same query as the previous one except that this time truncate (TRUNC) to the nearest penny rather than round.
select e.ENAME, floor(e.SAL / 22) 月薪, floor(e.SAl / 22 / 8) 日薪
from emp2022150212 e
where e.DEPTNO = 30;

小结：

4.5 EXERCISES 4 DATES

4.5.1 日期的各种方法

-- 获取当前日期 : CURDATE() 或 CURRENT_DATE() 
-- => 返回当前日期，不包含时间部分。
SELECT CURDATE();
-- 或
SELECT CURRENT_DATE();

-- ============================================ --

-- 获取当前时间戳 : NOW() 
-- => 返回当前的日期和时间。
SELECT NOW();

-- ============================================ --

-- 日期计算和操作
-- DATE_ADD()：向日期添加指定的时间间隔。
-- DATE_SUB()：从日期减去指定的时间间隔。
-- DATEDIFF()：计算两个日期之间的天数差。
-- DATE_FORMAT()：格式化日期为特定的字符串格式。
-- ===实例=== --
-- 向日期添加 10 天
SELECT DATE_ADD('2024-09-05', INTERVAL 10 DAY);

-- 从日期中减去 1 个月
SELECT DATE_SUB('2024-09-05', INTERVAL 1 MONTH);

-- 计算两个日期之间的天数
SELECT DATEDIFF('2024-09-15', '2024-09-05');

-- 格式化日期
SELECT DATE_FORMAT('2024-09-05', '%Y年%m月%d日');

-- ============================================ --

-- 提取日期部分
-- YEAR()：提取年份。
-- MONTH()：提取月份。
-- DAY()：提取日期中的日。
-- WEEKDAY()：提取星期几（0 = 周一, 6 = 周日）。
-- ===实例=== --
-- 提取年份
SELECT YEAR('2024-09-05');

-- 提取月份
SELECT MONTH('2024-09-05');

-- 提取日期
SELECT DAY('2024-09-05');

-- 提取星期几
SELECT WEEKDAY('2024-09-05');

-- ============================================ --

-- 日期处理函数
-- LAST_DAY()：返回指定日期所在月的最后一天。
-- STR_TO_DATE()：将字符串转换为日期。
-- DATE()：提取日期部分（无时间部分）。
-- ===实例=== --
-- 返回指定日期所在月的最后一天
SELECT LAST_DAY('2024-09-05');

-- 将字符串转换为日期
SELECT STR_TO_DATE('05-09-2024', '%d-%m-%Y');

-- 提取日期部分
SELECT DATE(NOW());

**练习： **

-- 1.Select the name, job, and date of hire of the employees in department 20. (Format the HIREDATE column to MM/DD/YY)
select ENAME, JOB, HIREDATE
from emp2022150212
where DEPTNO = 20;

-- 2.Then format the HIREDATE column into DoW (day of the week), Day (day of the month), MONTH (name of the month) and YYYY(year)
SELECT HIREDATE,
       DAYOFWEEK(STR_TO_DATE(HIREDATE, '%d-%b-%y')) AS week,
       DAY(STR_TO_DATE(HIREDATE, '%d-%b-%y'))       AS day,
       MONTH(STR_TO_DATE(HIREDATE, '%d-%b-%y'))     AS month,
       YEAR(STR_TO_DATE(HIREDATE, '%d-%b-%y'))      AS year
FROM emp2022150212;

-- 3.3Which employees were hired in April?
select *
from emp2022150212
where MONTH(STR_TO_DATE(HIREDATE, '%d-%b-%y')) = 4;

-- 4.Which employees were hired on a Tuesday?
select *
from emp2022150212
where DAYOFWEEK(STR_TO_DATE(HIREDATE, '%d-%b-%y')) = 2;

-- 5.Are there any employees who have worked more than 30 years for the company?
select *
from emp2022150212
where YEAR(CURDATE()) - YEAR(STR_TO_DATE(HIREDATE, '%d-%b-%y')) > 30;

-- 6.Show the weekday of the first day of the month in which each employee was hired. (plus their names)
select ENAME,
       DAYNAME(STR_TO_DATE(CONCAT('01-', DATE_FORMAT(STR_TO_DATE(HIREDATE, '%d-%b-%y'), '%b-%y')),
                           '%d-%b-%y')) AS first_day_weekday
from emp2022150212;

-- 7.Show details of employee hiredates and the date of their first payday. (Paydays occur on the last Friday of each month) (plus their names)
select ENAME,
       -- 如果入职日期在当前月的最后一个星期五之后，计算下下一个月的最后一个星期五
       CASE
           WHEN STR_TO_DATE(HIREDATE, '%d-%b-%y') > DATE_SUB(
                   LAST_DAY(STR_TO_DATE(HIREDATE, '%d-%b-%y')), INTERVAL
                   (WEEKDAY(LAST_DAY(STR_TO_DATE(HIREDATE, '%d-%b-%y'))) + 3) % 7 DAY
                                                    ) THEN
               -- 计算下一个月的最后一个星期五
               DATE_SUB(LAST_DAY(STR_TO_DATE(CONCAT(DATE_FORMAT(STR_TO_DATE(HIREDATE, '%d-%b-%y'), '%y-%m'), '-01'),
                                             '%Y-%m-%d') + INTERVAL 1 MONTH),
                        INTERVAL (WEEKDAY(LAST_DAY(
                       STR_TO_DATE(CONCAT(DATE_FORMAT(STR_TO_DATE(HIREDATE, '%d-%b-%y'), '%Y-%m'), '-01'), '%Y-%m-%d') +
                       INTERVAL 1 MONTH)) + 3) % 7 DAY
               )
           ELSE
               -- 计算当前月的最后一个星期五
               DATE_SUB(LAST_DAY(STR_TO_DATE(HIREDATE, '%d-%b-%y')), INTERVAL
                        (WEEKDAY(LAST_DAY(STR_TO_DATE(HIREDATE, '%d-%b-%y'))) + 3) % 7 DAY)
           END AS first_payday
from emp2022150212;

-- 8.Refine your answer to 7 such that it works even if an employee is hired after the last Friday of the month (cf Martin)
select ENAME,
       HIREDATE,
       DATE_SUB(LAST_DAY(STR_TO_DATE(HIREDATE, '%d-%b-%y')), INTERVAL
                (WEEKDAY(LAST_DAY(STR_TO_DATE(HIREDATE, '%d-%b-%y'))) + 3) % 7 DAY)
from emp2022150212;

五、实验总结

5.1 数据库定义语言（DDL）

DDL 用于定义和管理数据库的结构，包括创建、修改和删除表格、索引、视图等对象。常见的 DDL 操作包括：

**CREATE TABLE**：创建新的表格
**ALTER TABLE**：修改现有表格的结构
**DROP TABLE**：删除表格及其数据
**CREATE INDEX**：创建索引
**DROP INDEX**：删除索引
**CREATE VIEW**：创建视图
**DROP VIEW**：删除视图

示例：

-- 创建表格
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    salary DECIMAL(10, 2) NOT NULL,
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employees(id)
);

-- 修改表格
ALTER TABLE employees ADD COLUMN department VARCHAR(50);

-- 删除表格
DROP TABLE employees;

5.2 数据操作语言（DML）

DML 用于操作表格中的数据，包括插入、更新和删除记录。常见的 DML 操作包括：

**INSERT INTO**：插入新记录
**UPDATE**：更新现有记录
**DELETE**：删除记录

-- 插入数据
INSERT INTO employees (name, salary, manager_id) VALUES ('John Doe', 5000.00, 2);

-- 更新数据
UPDATE employees SET salary = 5500.00 WHERE name = 'John Doe';

-- 删除数据
DELETE FROM employees WHERE name = 'John Doe';

5.3 数据查询语言（DQL）

DQL 用于查询数据库中的数据。主要的 DQL 操作是：

**SELECT**：从表格中检索数据

标签：数据库 mysql

本文转载自: https://blog.csdn.net/2201_75414908/article/details/141889616
版权归原作者 看未来捏 所有，如有侵权，请联系我们删除。

【MySQL】深圳大学数据库实验一

一、实验目的

二、实验要求

三、实验设备

四、建议的实验步骤

4.1 使用SQL语句创建如上两张关系表

4.2 EXERCISES.1SIMPLE COMMANDS

4.3 EXERCISES 2 JOINS

4.3.1 左外连接（

4.3.2 右外连接（

4.3.3 全外连接（

4.3.4 段小结

4.4 EXERCISES 3 FUNCTIONS

1.

2.

3.

4.

5.

4.5 EXERCISES 4 DATES

4.5.1 日期的各种方法

五、实验总结

5.1 数据库定义语言（DDL）

5.2 数据操作语言（DML）

5.3 数据查询语言（DQL）

发表评论