【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, null, 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, null, 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, null, 30);
insert into emp2022150212 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7782, 'CLARK', 'MANAGER', 7839, '09-JUN-88', 27500, null, 10);
insert into emp2022150212 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7788, 'SCOTT', 'ANALYST', 7566, '19-APR-87', 19500, null, 20);
insert into emp2022150212 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7839, 'KING', 'PRESIDENT', 7902, '17-NOV-83', 82500, null, 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, null, 20);
insert into emp2022150212 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7900, 'JAMES', 'CLERK', 7698, '03-DEC-95', 12500, null, 30);
insert into emp2022150212 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7902, 'FORD', 'ANALYST', 7566, '03-DEC-91', 21500, null, 20);
insert into emp2022150212 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7934, 'MILLER', 'CLERK', 7782, '23-JAN-95', 13250, null, 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, null, 50);
insert into emp2022150212 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (6548, 'BARNES', 'CLERK', 4422, '16-Jan-95', 11950, null, 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:从表格中检索数据

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/416918.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

Java健康养老智慧相伴养老护理小程序系统源码代办陪诊陪护更安心

健康养老&#xff0c;智慧相伴 —— 养老护理小程序&#xff0c;代办陪诊陪护更安心 &#x1f308;【开篇&#xff1a;智慧养老&#xff0c;新时代的温馨守护】&#x1f308; 在这个快节奏的时代&#xff0c;我们总希望能给予家人更多的关爱与陪伴&#xff0c;尤其是家中的长…

物联网技术推动灌区智能化管理

物联网技术&#xff0c;作为信息技术革命的重要组成部分&#xff0c;正深刻地改变着传统行业的运作模式&#xff0c;其中在农业灌溉领域的应用尤为显著&#xff0c;为灌区的智能化管理开辟了新径。这一技术通过将传感器、智能网关、大数据分析与云平台紧密融合&#xff0c;实现…

使用uni-app开发微信小程序

一、前提环境 1.1 &#xff1a;uniapp开发文档&#xff1a;https://uniapp.dcloud.net.cn/quickstart-cli.html 细节都在这一页&#xff0c;这里不过多解释 二、开发工具下载 2.1 微信开发者工具 下载链接&#xff1a;https://developers.weixin.qq.com/miniprogram/dev/dev…

【软考】设计模式之代理模式

目录 1. 说明2. 应用场景3. 结构图4. 构成5. 适用性6. 优点7. 缺点8. java示例 1. 说明 1.代理模式&#xff08;Proxy Pattern&#xff09;。2.意图&#xff1a;为其他对象提供一种代理以控制对这个对象的访问。3.通过提供与对象相同的接口来控制对这个对象的访问。4.是设计模…

是德KEYSIGHT3458A 8.5位万用表Agilent3458a资料介绍

Agilent 3458A、Keysight 3458A、HP 3458A 万用表、8.5 位 特征&#xff1a; 高达 100,000 个读数/秒内部测试设置>340/秒可编程积分时间从 500 纳秒到 1 秒 更快的测试&#xff1a; 更高的测试产量&#xff1a; 更高精度&#xff0c;满足更严格的测试要求高达 8.5 位分辨…

企业财税自动化解决方案如何提升财务效率与准确性

近年来&#xff0c;数字化转型将企业推到了时代的风口浪尖&#xff0c;对于企业而言&#xff0c;只有快速适应互联网时代的变革&#xff0c;学会利用数字信息&#xff0c;才能在竞争激烈的商业环境中脱颖而出。随着技术的不断发展&#xff0c;财务自动化解决方案成为企业实现这…

AUTO TECH 2025 华南展 第十二届广州国际汽车零部件加工技术及汽车模具展览会——探索未来出行的创新动力

AUTO TECH 2025 华南展 第十二届广州国际汽车零部件加工技术及汽车模具展览会——探索未来出行的创新动力 随着全球汽车工业的不断进步和新能源汽车技术的迅猛发展&#xff0c;2025年11月20-22日在广州保利世贸博览馆将迎来一场行业瞩目的盛会——2025 第十二届广州国际汽车零部…

Windows下基于VMware虚拟机安装Ubuntu20.04系统

Windows下基于VMware虚拟机安装Ubuntu20.04系统 前言安装VMware软件安装Ubuntu20.04下载Ubuntu镜像安装镜像 结语 前言 本教程旨在帮助想要接触linux的道友&#xff0c;为诸位道友们连接进入linux世界的桥梁&#xff0c;从此可以在linux的世界里任意翱翔&#xff0c;本博客为笔…

基于Tomcat的JavaWeb(ASP)项目构建(图解)

目录 配置IDEA的TOMCAT环境 环境设置 导入API(可选) 创建项目 构建项目 ​编辑 运行项目 项目结果 ​编辑 查看配置基础项目 配置IDEA的TOMCAT环境 环境设置 导入API(可选) 创建项目 构建项目 运行项目 项目结果 查看配置基础项目 了解Web Application: Exploded与…

JS设计模式之“语言之魂” - 原型模式

前言 当我们学习JavaScript的时候&#xff0c;经常会听到原型&#xff08;prototype)、原型链&#xff08;prototype chain&#xff09;和原型模式&#xff08;prototype pattern&#xff09;这些概念&#xff0c;它们之间有什么关联呢&#xff1f;怎么样才能使用好原型模式呢…

Unity数据持久化 之 反序列化2进制

Unity数据持久化 之 序列化C#类为2进制-CSDN博客 本文仅作笔记学习和分享&#xff0c;不用做任何商业用途 本文包括但不限于unity官方手册&#xff0c;unity唐老狮等教程知识&#xff0c;如有不足还请斧正​​ 有了序列化&#xff0c;肯定也要反序列化来用 反序列化文件流 pub…

逆向工程核心原理 Chapter27 | 代码注入

代码注入知识点 代码注入与DLL注入的区别 代码注入和DLL注入的区别在于&#xff1a;我们必须把参数也注入目标进程。 DLL注入不需要考虑参数地址&#xff0c;只需要考虑LPTHREAD_START_ROUTINE的地址和DLL字符串地址&#xff0c; 是因为参数在DLL里面&#xff0c;目标进程加…

如何在算家云搭建ChatGLM3(智能对话)

一、ChatGLM3 简介 ChatGLM3 是一款先进的语言模型&#xff0c;它通过增强的语言理解、逻辑推理和数学计算能力&#xff0c;提供了更精准的问题解答和更流畅的对话体验。 模型支持多模态交互&#xff0c;能够处理图像与文本的结合&#xff0c;同时拥有深化的知识图谱&#xf…

海洋运输船5G智能工厂物联数字孪生平台,推进制造业数字化转型

海洋运输船5G智能工厂物联数字孪生平台&#xff0c;推进制造业数字化转型。在当今全球制造业的浪潮中&#xff0c;数字化转型已成为不可逆转的趋势&#xff0c;它不仅重塑了生产流程&#xff0c;更深刻影响着企业的竞争力与可持续发展能力。其中&#xff0c;海洋运输船5G智能工…

SprinBoot+Vue在线商城微信小程序的设计与实现

目录 1 项目介绍2 项目截图3 核心代码3.1 Controller3.2 Service3.3 Dao3.4 application.yml3.5 SpringbootApplication3.5 Vue3.6 uniapp代码 4 数据库表设计5 文档参考6 计算机毕设选题推荐7 源码获取 1 项目介绍 博主个人介绍&#xff1a;CSDN认证博客专家&#xff0c;CSDN平…

50ETF期权和股指期权有什么区别?ETF期权应该怎么做?

今天期权懂带你了解50ETF期权和股指期权有什么区别&#xff1f;ETF期权应该怎么做&#xff1f;ETF是对个股期权&#xff0c;股指期权是对应该股指期货的&#xff0c;那么股指期权和etf期权有什么区别&#xff1f; 股指期权怎么交易 股指期权交易要开通股指期货账户&#xff0…

k8s 部署 jenkins【详细步骤】

文章目录 部署介绍部署步骤第 1 步:创建 namespace第 2 步:创建 ServiceAccount第 3 步:创建持久卷第 4 步:创建 Deployment第 5 步:创建 Service第 6 步:浏览器访问 Jenkins第 7 步:修改默认时区参考⭐ 本文目标:在 k8s 集群中部署一个 jenkins。 部署介绍 🚀 在 K…

C盘红了怎么办?C盘快满了怎么办?如何提高电脑运行速度?

在电脑的日常使用中&#xff0c;C盘红了&#xff08;也就是C盘满了&#xff09;&#xff0c;那是常有的事&#xff0c;本文将详述一下&#xff0c;C盘红了之后的多种处理方法&#xff0c;只要你看完了&#xff0c;就必有一款适合你。 一、系统自带的磁盘清理 当你的C盘红了&…

9、Django Admin优化查询

如果你的Admin后台中有很多计算字段&#xff0c;那么你需要对每个对象运行多个查询&#xff0c;这会使你的Admin后台变得非常慢。要解决此问题&#xff0c;你可以重写管理模型中的get_queryset方法使用annotate聚合函数来计算相关的字段。 以下示例为Origin模型的中ModelAdmin…

vue3 antdv3 TypeError: date1.isAfter is not a function的解决

1、先上个报错的图&#xff1a; 2、这个一看是因为date报错的问题&#xff0c;这里面用了TimeRangePicker. const TimeRangePicker TimePicker.TimeRangePicker; import dayjs, { Dayjs } from dayjs; let time1 [dayjs(dayjs(new Date()).format(YYYY-MM-DD) record.…