SQL语句

nihaoakekeke

已于 2024-04-21 18:45:55 修改

阅读量921

点赞数 12

文章标签： sql 数据库

于 2024-04-14 14:25:22 首次发布

本文链接：https://blog.csdn.net/nihaoakekeke/article/details/137200061

版权

5. 学生选课关系表 (StudentCourses)

SQL 是什么？

SQL 指结构化查询语言，全称是 Structured Query Language。
SQL 让您可以访问和处理数据库，包括数据插入、查询、更新和删除。
SQL 在1986年成为 ANSI（American National Standards Institute 美国国家标准化组织）的一项标准，在 1987 年成为国际标准化组织（ISO）标准。

SQL 能做什么？

SQL 面向数据库执行查询
SQL 可从数据库取回数据
SQL 可在数据库中插入新的记录
SQL 可更新数据库中的数据
SQL 可从数据库删除记录
SQL 可创建新数据库
SQL 可在数据库中创建新表
SQL 可在数据库中创建存储过程
SQL 可在数据库中创建视图
SQL 可以设置表、存储过程和视图的权限

SQL包括了所有对数据库的操作，主要是由数据定义、数据操纵、数据查询、数据控制、事务控制以及嵌入式SQL语言的使用规定组成。

1.数据定义：又称为“DDL语言”，定义数据库的逻辑结构，包括定义数据库、基本表、视图和索引4部分。

2.数据操纵：又称为“DML语言”，包括插入、删除和更新三种操作。

3.数据查询：又称为“DQL语言”，包括数据查询操作。

4.数据控制：又称为“DCL语言”，对用户访问数据的控制有基本表和视图的授权及回收。

5.事务控制：又称为“TCL语言”，包括事务的提交与回滚。

6.嵌入式SQL语言的使用规定：规定SQL语句在宿主语言的程序中使用的规则。

关键术语的介绍

行（row）:
表是一种结构化的文件，可用于存储特定类型的数据，表中的每一行，也称为一条记录。

列（column）：
表中的一个字段，所有表都是由一个或多个列组成的。表中的每一列，称为属性，字段。

索引：
将表中的一个或多个字段中的数据复制一份另存，并且按特定次序排序存储。

视图：
视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。

约束(constraint)条件：
表中的数据要遵守的限制。

主键：
一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行。

唯一键：
一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行；允许为NULL，一个表可以存在多个

外键：
一个表中的某字段可填入的数据取决于另一个表的主键或唯一键已有的数据。

检查：
字段值在一定范围内。

UNSIGNED ：
无符号，值从0开始，无负数。

ZEROFILL：
零填充，当数据的显示长度不够的时候，使用前补0的效果填充至指定长度,字段会自动添加UNSIGNED。

NOT NULL：
非空约束，表示该字段的值不能为空。

DEFAULT：
表示如果插入数据时没有给该字段赋值，那么就使用默认值。

AUTO_INCREMENT：
自增长，只能用于数值列，而且配合索引使用,默认起始值从1开始，只能增长。

FOREIGN KEY：
外键约束，目的是为了保证数据的完成性和唯一性，以及实现一对一或一对多关系。

范式：
设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。

1NF：无重复的列，同一列中不能有多个值。
说明：第一范式（ 1NF）是对关系模式的基本要求，不满足第一范式（ 1NF）的数据库就不是关系数据库。

2NF：属性完全依赖于主键，第二范式必须先满足第一范式，要求表中的每个行必须可以被唯一地区分。

3NF：属性不依赖于其它非主属性，满足第三范式必须先满足第二范式。

SQL语句分类：
DDL: Data Defination Language 数据定义语言
CREATE， DROP， ALTER

DML: Data Manipulation Language 数据操纵语言
INSERT， DELETE， UPDATE

DCL：Data Control Language 数据控制语言
GRANT， REVOKE， COMMIT， ROLLBACK

DQL：Data Query Language 数据查询语言
SELECT

数值类型介绍：

数值类型:
MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

数值类型：
整数类型：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT

浮点数类型：FLOAT、DOUBLE、DECIMAL

字符串类型：
CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT等。

日期类型：
Date、DateTime、TimeStamp、Time、Year

数据表的操作：

1.操作数据库
创建数据库：CREATE DATABASE [IF NOT EXISTS] mydb1；
例如：CREATE DATABASE mydb1，创建一个名为 mydb1 的数据库。如果这个数据已经存在，那么会报错。
例如 CREATE DATABASE IF NOT EXISTSmydb1，在名为 mydb1 的数据库不存在时创建该库，这样可以避免报错。

删除数据库：DROP DATABASE [IF EXISTS] mydb1；
例如：DROP DATABASE mydb1，删除名为 mydb1 的数据库。如果这个数据库不存在，那么会报错。DROP DATABASE IF EXISTS mydb1，就算 mydb1不存在，也不会的报错。

修改数据库编码：ALTER DATABASE mydb1 CHARACTER SET utf8
修改数据库 mydb1 的编码为 utf8。注意，在 MySQL 中所有的 UTF-8 编码都
不能使用中间的“-”，即 UTF-8 要书写为 UTF8。

1.创建数据表：

CREATE TABLE 表名( 列名列类型, 列名列类型, ...... );

//老师表
CREATE TABLE Teachers (
    TeacherID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL
);
//班级表
CREATE TABLE Classes (
    ClassID INT AUTO_INCREMENT PRIMARY KEY,
    ClassName VARCHAR(100) NOT NULL,
    TeacherID INT,
    CONSTRAINT fk_Teacher FOREIGN KEY (TeacherID) REFERENCES Teachers(TeacherID)
);
//学生表
CREATE TABLE Students (
    StudentID INT AUTO_INCREMENT PRIMARY KEY,
    FirstName VARCHAR(100) NOT NULL,
    LastName VARCHAR(100) NOT NULL,
    ClassID INT,
    CONSTRAINT fk_Class FOREIGN KEY (ClassID) REFERENCES Classes(ClassID)
);

//课程表
CREATE TABLE Courses (
    CourseID INT AUTO_INCREMENT PRIMARY KEY,
    Title VARCHAR(100) NOT NULL,
    TeacherID INT,
    CONSTRAINT fk_TeacherCourse FOREIGN KEY (TeacherID) REFERENCES Teachers(TeacherID)
);

//学生与课程关系表
CREATE TABLE StudentCourses (
    StudentID INT,
    CourseID INT,
    EnrollmentDate DATE,
    CONSTRAINT pk_StudentCourses PRIMARY KEY (StudentID, CourseID),
    CONSTRAINT fk_Student FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    CONSTRAINT fk_Course FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

1.老师表

TeacherID 是老师的主键，自增。
Name 是老师的姓名，不能为空。
Email 是老师的电子邮件地址，需要是唯一的。

2. 班级表 (Classes)

ClassID 是班级的主键，自增。
ClassName 是班级的名称，不能为空。
TeacherID 是外键，引用老师表中的TeacherID。

3. 学生表 (Students)

StudentID 是学生的主键，自增。
FirstName 和 LastName 分别是学生的名和姓，不能为空。
ClassID 是外键，引用班级表中的ClassID。

4. 课程表 (Courses)

CourseID 是课程的主键，自增。
Title 是课程的标题，不能为空。
TeacherID 是外键，引用老师表中的TeacherID。

5. 学生选课关系表 (StudentCourses)

由于学生和课程之间是多对多关系，我们需要创建一个关系表来表示这种关系。

StudentID 和 CourseID 分别是学生和课程的外键，它们共同构成复合主键，确保每个学生只能为每个课程注册一次。
EnrollmentDate 是学生选课的日期。

2.查看表的结构

1.DESC 表名；

2.删除表（DROP）

DROP TABLE 表名；

delete：

1、delete是DML，执行delete操作时，每次从表中删除一行，并且同时将该行的的删除操作记录在redo和undo表空间中以便进行回滚（rollback）和重做操作，但要注意表空间要足够大，需要手动提交（commit）操作才能生效，可以通过rollback撤消操作。

2、delete可根据条件删除表中满足条件的数据，如果不指定where子句，那么删除表中所有记录。

3、delete语句不影响表所占用的extent，高水线(high watermark)保持原位置不变。

drop：

1、drop是DDL，会隐式提交，所以，不能回滚，不会触发触发器。

2、drop语句删除表结构及所有数据，并将表所占用的空间全部释放。

3、drop语句将删除表的结构所依赖的约束，触发器，索引，依赖于该表的存储过程/函数将保留,但是变为invalid状态。

3.修改表（ALTER）：

添加列

如果我们想要给Teachers表添加一个新的列，比如Department（部门），可以使用以下语句：

ALTER TABLE Teachers ADD COLUMN Department VARCHAR(100);

这将在Teachers表的最后添加一个名为Department的列，其数据类型为可变长度字符串，最大长度为100个字符。

修改列的数据类型

如果我们需要修改现有列的数据类型，比如将Email列的数据类型从VARCHAR(100)更改为VARCHAR(255)，可以使用以下语句：

ALTER TABLE Teachers MODIFY Email VARCHAR(255);

这将改变Email列的数据类型，允许更长的电子邮件地址。

修改列名

如果我们想要重命名一个列，比如将Name列重命名为FullName，可以使用以下语句：

ALTER TABLE Teachers CHANGE Name FullName VARCHAR(100);

这将把Name列的名字更改为FullName，但保持其数据类型不变。

删除列

如果我们需要从表中删除一个列，比如不再需要Department列，可以使用以下语句：

ALTER TABLE Teachers DROP COLUMN Department;

这将从Teachers表中删除Department列。

修改表名称

如果我们想要更改表的名称，比如将Teachers表重命名为Instructors，可以使用以下语句：

ALTER TABLE Teachers RENAME TO Instructors;

这将把Teachers表的名称更改为Instructors。

4.数据插入（INSERT)：

语法 1：

INSERT INTO 表名(列名 1,列名 2, …) VALUES(值 1,值 2,…) ;

INSERT INTO Classes(ClassName, TeacherID) VALUES('一年级一班', '1');

语法2：

INSERT INTO 表名 VALUES(值 1,值 2,…);

因为没有指定要插入的列，表示按创建表时列的顺序插入所有列的值：

INSERT INTO Classes VALUES('1','一年级一班', '1');

INSERT INTO Students (FirstName, LastName, ClassID) VALUES
('赵', '钱', 1),
('孙', '李', 1),
('周', '吴', 2),
('吴', '郑', 2),
('王', '冯', 3);


INSERT INTO Classes (ClassName, TeacherID) VALUES
('一年级一班', 1),
('二年级二班', 2),
('三年级三班', 3);


INSERT INTO Courses (Title, TeacherID) VALUES
('数学', 1),
('语文', 2),
('英语', 3);

INSERT INTO Students (FirstName, LastName, ClassID) VALUES
('赵', '钱', 1),
('孙', '李', 1),
('周', '吴', 2),
('吴', '郑', 2),
('王', '冯', 3);

INSERT INTO StudentCourses (StudentID, CourseID, EnrollmentDate) VALUES
(1, 1, '2024-01-10'),
(1, 2, '2024-01-11'),
(2, 1, '2024-01-12'),
(3, 2, '2024-01-13'),
(3, 3, '2024-01-14'),
(4, 3, '2024-02-01');

5.数据修改（UPDATE）：

UPDATE 表名 SET 列名 1=值 1, … 列名 n=值 n [WHERE 条件];

//单列修改
UPDATE Teachers SET Email='newemail@example.com' WHERE TeacherID = 1;

//多列修改
UPDATE Classes SET ClassName='新一年级一班', TeacherID=2 WHERE ClassID = 1;

//更新表中所有记录
UPDATE Courses SET TeacherID=4;

//更新满足特殊要求的记录
UPDATE Students SET ClassID=4 WHERE ClassID=1;

//使用别名更新表中的数据
UPDATE StudentCourses sc
SET CourseID=(SELECT CourseID FROM Courses WHERE Title='新英语')
WHERE StudentID IN (SELECT StudentID FROM Students WHERE ClassID=4);

//用函数更新表中的数据
UPDATE Teachers SET Email=CONCAT(Email, '.newdomain.com');
//更新Teachers表中所有老师的电子邮件地址，添加域名后缀：


//使用join语句 更新相互关联的表
UPDATE StudentCourses sc
JOIN Courses c ON sc.CourseID = c.CourseID
SET sc.EnrollmentDate='2024-03-01'
WHERE c.Title='数学';
//通过JOIN语句连接了StudentCourses和Courses表，然后为所有选修了“数学”课程的学生设置了选课日期。

6.删除数据（DELETE）

语法 1：
DELETE FROM 表名 [WHERE 条件];

//删除特定的记录
DELETE FROM Teachers WHERE TeacherID = 1;

//删除表中的所有数据
DELETE FROM Classes;

//删除满足特定条件的记录
DELETE FROM Students WHERE ClassID = 1;

//删除与之相关表中的数据
DELETE FROM StudentCourses WHERE CourseID = 1;

DELETE sc
FROM StudentCourses sc
JOIN Students s ON sc.StudentID = s.StudentID
WHERE s.FirstName = '赵';

//删除表中的一个记录，并且返回相应的记录
DELETE FROM Teachers t
WHERE TeacherID = (
    SELECT TeacherID
    FROM Teachers
    ORDER BY TeacherID DESC
    LIMIT 1
);
//删除了Teachers表中ID最大的老师记录。LIMIT 1确保只返回一条记录，然后外层的DELETE语句使用子查询的结果来定位并删除这条记录。

语法 2：
TRUNCATE TABLE 表名;

虽然 TRUNCATE 和 DELETE 都可以删除表的所有记录，但有原理不同。DELETE的效率没有 TRUNCATE 高！
TRUNCATE 其实属性 DDL 语句，因为它是先 DROP TABLE，再 CREATE TABLE。
而且TRUNCATE删除的记录是无法回滚的，但DELETE删除的记录是可以回滚的（回滚是事务的知识！）。

3.数据查询语言：

语法：
select 列名 ----> 要查询的列名称
from 表名 ----> 要查询的表名称
where 条件 ----> 行条件
group by 分组列 ----> 对结果分组
having 分组条件 ----> 分组后的行条件
order by 排序列 ----> 对结果分组
limit 起始行, 行数 ----> 结果限定

//基础查询
SELECT Name, Email
FROM Teachers;

//条件查询
SELECT *
FROM Students
WHERE LastName = '赵';

//模糊查询
SELECT *
FROM Teachers
WHERE Email LIKE '%example.com';

//排序查询
SELECT *
FROM Classes
ORDER BY ClassName ASC;

//聚合函数查询
SELECT Title, AVG(EnrollmentDate) AS AverageEnrollment
FROM Courses
GROUP BY Title;

//分组查询
SELECT StudentID, COUNT(*) AS CourseCount
FROM StudentCourses
GROUP BY StudentID;


//子查询
SELECT *
FROM Students
WHERE StudentID IN (
    SELECT DISTINCT StudentID
    FROM StudentCourses
);


//多表连接查询
SELECT t.Name AS TeacherName, c.ClassName, co.Title
FROM Teachers t
JOIN Classes c ON t.TeacherID = c.TeacherID
JOIN Courses co ON c.ClassID = co.ClassID;


//group by 和 having
SELECT 
    c.Title AS CourseTitle,
    AVG(sc.Score) AS AverageScore
FROM 
    StudentCourses sc
JOIN 
    Courses c ON sc.CourseID = c.CourseID
GROUP BY 
    c.CourseID
HAVING 
    AVG(sc.Score) > 85;

1.基础查询

查询所有列
SELECT * FROM 表名;
(* ：通配符，表示所有列)

查询指定列
SELECT 列名 1, 列名 2, …列名 n FROM 表名;

2.条件查询

条件查询介绍
条件查询就是在查询时给出 WHERE 子句，在 WHERE 子句中可以使用如下运算符及关键字：

=、!=、<>、<、<=、>、>=；
BETWEEN…AND；
IN(set)；
IS NULL；
AND；
OR；
NOT；

3.模糊查询

SELECT 字段 FROM 表 WHERE 某字段 Like 条件
其中关于条件，SQL 提供了两种匹配模式：

% ：表示任意 0 个或多个字符。可匹配任意类型和长度的字符，有些情
况下若是中文，请使用两个百分号（%%）表示。
_ ：表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句。

4.字段控制查询

去掉重复记录
去除重复记录（两行或两行以上记录中系列的上的数据都相同），例如 emp 表中 sal 字段就存在相同的记录。当只查询 emp 表的 sal 字段时，那么会出现重复记录，那么想去除重复记录，需要使用 DISTINCT：
SELECT DISTINCT sal FROM emp;

查看雇员的月薪与佣金之和
因为 sal 和 comm 两列的类型都是数值类型，所以可以做加运算。如果 sal 或 comm 中有一个字段不是数值类型，那么会出错。
SELECT *,
sal+comm FROM emp;
comm 列有很多记录的值为 NULL，因为任何东西与 NULL 相加结果还是 NULL，所以结算结果可能会出现 NULL。下面使用了把 NULL 转换成数值 0 的函数 IFNULL：
SELECT *, sal+IFNULL(comm,0) FROM emp;

给列名添加别名
在上面查询中出现列名为 sal+IFNULL(comm,0)，这很不美观，现在我们给这一列给出一个别名，为 total：
SELECT *, sal+IFNULL(comm,0) AS total FROM emp;
给列起别名时，是可以省略 AS 关键字的：
SELECT *, sal+IFNULL(comm,0) total FROM emp;

5.排序

查询所有学生记录，按年龄升序排序
SELECT * FROM stu
ORDER BY sage ASC;
或者
SELECT * FROM stu ORDER BY sage;

查询所有学生记录，按年龄降序排序
SELECT * FROM stu
ORDER BY age DESC;

查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序
SELECT * FROM emp
ORDER BY sal DESC ,empno ASC;

6.聚合函数

聚合函数是用来做纵向运算的函数：

COUNT()：统计指定列不为 NULL 的记录行数；
MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为 0；
AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为 0；
COUNT：当需要纵向统计时可以使用 COUNT()。

7.分组查询

分组查询

当需要分组查询时需要使用 GROUP BY 子句，例如查询每个部门的工资和，这说明要使用部分来分组。

8.LIMIT：用来限定查询结果的起始行，以及总行数。
查询 5 行记录，起始行从 0 开始
SELECT * FROM emp LIMIT 0, 5;
注意，起始行从 0 开始，即第一行开始！

查询 10 行记录，起始行从 3 开始
SELECT * FROM emp LIMIT 3, 10;

分页查询
如果一页记录为 10 条，希望查看第 3 页记录应该怎么查呢？
 第一页记录起始行为 0，一共查询 10 行；
 第二页记录起始行为 10，一共查询 10 行；
 第三页记录起始行为 20，一共查询 10 行；

8.多表连接查询：

表连接分为内连接和外连接。

他们之间最主要的区别：内连接仅选出两张表中互相匹配的记录，外连接会选出其他不匹配的记录。

外连接分为左连接和右连接

左连接：包含左边表中所有的记录，右边表中没有匹配的记录显示为 NULL。
右连接：包含右边表中所有的记录，左边表中没有匹配的记录显示为 NULL。

图解MySQL 内连接、外连接、左连接、右连接、全连接……太多了-CSDN博客https://blog.csdn.net/plg17/article/details/78758593

9.语法顺序

select语句的语法格式如下。
select 字段列表
from 数据源
[ where条件表达式 ]
[ group by 分组字段
[ having条件表达式 ]
[ order by 排序字段 [ asc | desc ] ]

SELECT department, SUM(salary) AS total_salary
FROM employees
WHERE hire_date > '2010-01-01'
GROUP BY department
HAVING SUM(salary) > 500000
ORDER BY total_salary DESC;

注意：

navicat 要学会刷新，数据库表也要学会刷新，有些时候可能数据没有同步。

nihaoakekeke

关注

12
点赞
踩
20

收藏

觉得还不错? 一键收藏
0
评论
SQL语句

行（row）:表是一种结构化的文件，可用于存储特定类型的数据，表中的每一行，也称为一条记录。列（column）：表中的一个字段，所有表都是由一个或多个列组成的。表中的每一列，称为属性，字段。索引：将表中的一个或多个字段中的数据复制一份另存，并且按特定次序排序存储。视图：视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。约束(constraint)条件：表中的数据要遵守的限制。
复制链接

扫一扫