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

一、实验目的

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

二、实验要求

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

三、实验设备

    计算机、数据库管理系统如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**：从表格中检索数据