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

2024-09-06 10:20

本文主要是介绍【MySQL】深圳大学数据库实验一,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

目录

一、实验目的

二、实验要求

三、实验设备

四、建议的实验步骤

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

4.2 EXERCISES. 1 SIMPLE COMMANDS

4.3 EXERCISES 2 JOINS

4.4 EXERCISES 3 FUNCTIONS

4.5 EXERCISES 4 DATES

五、实验总结

5.1 数据库定义语言(DDL)

5.2 数据操作语言(DML)

5.3 数据查询语言(DQL)


一、实验目的

  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_nameADD 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 TRANSACTIONCOMMITROLLBACK 操作。

-- 开始事务
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. 1 SIMPLE 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 JOINLEFT OUTER JOIN)、右外连接(RIGHT JOINRIGHT OUTER JOIN)和全外连接(FULL JOINFULL OUTER JOIN)。

4.3.1 左外连接(LEFT JOINLEFT 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 JOINRIGHT 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 JOINFULL OUTER JOIN

全外连接返回左表和右表中的所有记录,匹配的记录会连接在一起。如果某一表中的记录在另一表中没有匹配项,则结果集中会包含该记录以及另一表中列的 NULL 值。注意,MySQL 并不直接支持 FULL OUTER JOIN,可以通过联合 LEFT JOINRIGHT JOIN 实现。

语法:

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

4.3.4 段小结

  • 左外连接(LEFT JOINLEFT OUTER JOIN:返回左表中的所有记录及右表中匹配的记录,如果没有匹配,则右表的列为 NULL
  • 右外连接(RIGHT JOINRIGHT OUTER JOIN:返回右表中的所有记录及左表中匹配的记录,如果没有匹配,则左表的列为 NULL
  • 全外连接(FULL JOINFULL OUTER JOIN:返回左表和右表中的所有记录,包括没有匹配的记录,MySQL 需要通过联合 LEFT JOINRIGHT JOIN 来实现。
-- 1.Find the name and salary of employees in Luton.
select e.ENAME, e.SAL
from emp2022150212 eleft join dept2022150212 d on e.DEPTNO = d.DEPTNO
where e.DEPTNO = d.DEPTNOand d.LOC = 'LUTON';-- 2.Join the DEPT table to the EMP table and show in department number order.
select *
from emp2022150212 eleft 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 eleft 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 eright 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 eleft join emp2022150212 t on e.DEPTNO = t.DEPTNO
where e.SAL > t.SALand 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 eright 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 != 0and t.COMM != 0and 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,-- 如果入职日期在当前月的最后一个星期五之后,计算下下一个月的最后一个星期五CASEWHEN 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】深圳大学数据库实验一的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1141751

相关文章

MySQL 中的 JSON 查询案例详解

《MySQL中的JSON查询案例详解》:本文主要介绍MySQL的JSON查询的相关知识,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录mysql 的 jsON 路径格式基本结构路径组件详解特殊语法元素实际示例简单路径复杂路径简写操作符注意MySQL 的 J

Windows 上如果忘记了 MySQL 密码 重置密码的两种方法

《Windows上如果忘记了MySQL密码重置密码的两种方法》:本文主要介绍Windows上如果忘记了MySQL密码重置密码的两种方法,本文通过两种方法结合实例代码给大家介绍的非常详细,感... 目录方法 1:以跳过权限验证模式启动 mysql 并重置密码方法 2:使用 my.ini 文件的临时配置在 Wi

MySQL重复数据处理的七种高效方法

《MySQL重复数据处理的七种高效方法》你是不是也曾遇到过这样的烦恼:明明系统测试时一切正常,上线后却频频出现重复数据,大批量导数据时,总有那么几条不听话的记录导致整个事务莫名回滚,今天,我就跟大家分... 目录1. 重复数据插入问题分析1.1 问题本质1.2 常见场景图2. 基础解决方案:使用异常捕获3.

SQL中redo log 刷⼊磁盘的常见方法

《SQL中redolog刷⼊磁盘的常见方法》本文主要介绍了SQL中redolog刷⼊磁盘的常见方法,将redolog刷入磁盘的方法确保了数据的持久性和一致性,下面就来具体介绍一下,感兴趣的可以了解... 目录Redo Log 刷入磁盘的方法Redo Log 刷入磁盘的过程代码示例(伪代码)在数据库系统中,r

mysql中的group by高级用法

《mysql中的groupby高级用法》MySQL中的GROUPBY是数据聚合分析的核心功能,主要用于将结果集按指定列分组,并结合聚合函数进行统计计算,下面给大家介绍mysql中的groupby用法... 目录一、基本语法与核心功能二、基础用法示例1. 单列分组统计2. 多列组合分组3. 与WHERE结合使

Mysql用户授权(GRANT)语法及示例解读

《Mysql用户授权(GRANT)语法及示例解读》:本文主要介绍Mysql用户授权(GRANT)语法及示例,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录mysql用户授权(GRANT)语法授予用户权限语法GRANT语句中的<权限类型>的使用WITH GRANT

Mysql如何解决死锁问题

《Mysql如何解决死锁问题》:本文主要介绍Mysql如何解决死锁问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录【一】mysql中锁分类和加锁情况【1】按锁的粒度分类全局锁表级锁行级锁【2】按锁的模式分类【二】加锁方式的影响因素【三】Mysql的死锁情况【1

SQL BETWEEN 的常见用法小结

《SQLBETWEEN的常见用法小结》BETWEEN操作符是SQL中非常有用的工具,它允许你快速选取某个范围内的值,本文给大家介绍SQLBETWEEN的常见用法,感兴趣的朋友一起看看吧... 在SQL中,BETWEEN是一个操作符,用于选取介于两个值之间的数据。它包含这两个边界值。BETWEEN操作符常用

MySQL索引的优化之LIKE模糊查询功能实现

《MySQL索引的优化之LIKE模糊查询功能实现》:本文主要介绍MySQL索引的优化之LIKE模糊查询功能实现,本文通过示例代码给大家介绍的非常详细,感兴趣的朋友一起看看吧... 目录一、前缀匹配优化二、后缀匹配优化三、中间匹配优化四、覆盖索引优化五、减少查询范围六、避免通配符开头七、使用外部搜索引擎八、分

MySql match against工具详细用法

《MySqlmatchagainst工具详细用法》在MySQL中,MATCH……AGAINST是全文索引(Full-Textindex)的查询语法,它允许你对文本进行高效的全文搜素,支持自然语言搜... 目录一、全文索引的基本概念二、创建全文索引三、自然语言搜索四、布尔搜索五、相关性排序六、全文索引的限制七