SQL基础

type
Post
status
Published
summary
最近面试了一个大数据异常行为检测的岗位,所以重新温习整理一下 SQL 相关的内容。 数据库主要分为两大类,分别是关系型数据库(SQL)和非关系型数据库(NOSQL);在关系型数据库中Oracle和MySQL是最具有代表性的两个,在非关系型数据库中MongoDB和Redis也是榜上有名。 SQL(结构化查询语言)是一种用于操作和查询数据库的标准化语言。它被大多数现代数据库系统如MySQL,Oracle,SQL Server,PostgreSQL等广泛采用。尽管这些数据库系统都使用SQL,但是他们之间可能会有一些语法上的差异,这些差异主要是由于各个数据库系统提供的特定功能和优化。
slug
sql-base
date
Jun 6, 2024
tags
SQL
category
基础知识
password
icon
URL
Property
Jun 6, 2024 09:52 AM

一、概述

数据库主要分为两大类,分别是关系型数据库(SQL)和非关系型数据库(NOSQL);在关系型数据库中Oracle和MySQL是最具有代表性的两个,在非关系型数据库中MongoDB和Redis也是榜上有名。
SQL(结构化查询语言)是一种用于操作和查询数据库的标准化语言。它被大多数现代数据库系统如MySQL,Oracle,SQL Server,PostgreSQL等广泛采用。尽管这些数据库系统都使用SQL,但是他们之间可能会有一些语法上的差异,这些差异主要是由于各个数据库系统提供的特定功能和优化。例如,Oracle数据库使用PL/SQL(过程语言/结构化查询语言)作为其SQL方言,它包含了一些特定的函数和过程,可以进行更复杂的数据操作。而MySQL使用的是标准SQL,但也有一些自定义的函数和过程。所以可以把SQL语法看作是各种类型数据库的通用语言,虽然每种数据库可能会有自己的一些特殊语法,但是他们的核心操作和概念都是一样的。
SQL主要分为三种类型:数据定义语言(DDL)、数据操作语言(DML)和数据控制语言(DCL)。
  • 数据定义语言(DDL):这是用来定义或修改数据库结构的语言。主要的DDL语句包括CREATE(创建)、ALTER(修改)和DROP(删除)。例如,你可以使用DDL来创建新的数据库,表,视图,索引等。
  • 数据操作语言(DML):这是用来获取、插入、修改或删除数据库中的数据的语言。主要的DML语句包括SELECT(查询)、INSERT(插入)、UPDATE(更新)和DELETE(删除)。例如,你可以使用DML来查询某个表中的数据,或者更新某个表中的数据。
  • 数据控制语言(DCL):这是用来控制用户对数据库中数据访问权限的语言。主要的DCL语句包括GRANT(授权)和REVOKE(撤销)。例如,你可以使用DCL来给用户赋予或者撤销访问某个数据库或表的权限。
SQL 的注释:
  • 单行注释:两条横杠(—),在 MySQL 中井号(#)也能注释
  • 多行注释:斜杠和星号(/* */)

二、DDL数据定义

2.1、数据库操作

2.1.1、创建数据库

CREATE DATABASE 数据库名称; -- 创建指定数据库,如果这个数据库已经存在,会报错 CREATE DATABASE IF NOT EXISTS 数据库名称; -- 当指定数据库不存在的时候创建对应的数据库 CREATE DATABASE IF NOT EXISTS school; CREATE DATABASE IF NOT EXISTS 数据库名称 DEFAULT CHARSET utf8; -- 创建数据库的时候指定数据文件编码方式 CREATE DATABASE IF NOT EXISTS school CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- 指定数据文件编码方式,COLLATE 指定排序规则

2.1.2、删除数据库

DROP DATABASE 数据库名称; -- 删除指定数据库,如果这个数据库不存在,会报错 DROP DATABASE IF EXISTS 数据库名; DROP DATABASE school; DROP DATABASE IF EXISTS school;

2.1.3、切换数据库

USE 数据库; -- 切换/使用指定数据库;切换后所有数据库相关操作都是针对这个数据库 USE school;

2.2、数据表操作

2.2.1、创建数据表

语法:CREATE TABLE IF NOT EXISTS 表名(字段名1 类型1 约束1, 字段名2 类型2 约束2, ...);
说明:
①表名:见名知义,一般需要加前缀 t 或者 tb 来表示这是数据表
②字段:见名知义;
注意:字段中一定要有一个字段来作为主键(可以表示唯一一条记录);主键要求不为空,并且是唯一的,而且数据类型是整型
③类型:
MySQL中常用的数据类型
💡
MySQL中常用的数据类型
数值类型:
  • 整数类型:包括TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT。例如INT UNSIGNED表示无符号整数。大的整数类型可以存储更大的数值,但它们也占用更多的存储空间
    • TINYINT:占用1个字节,有符号的范围是-128到127,无符号的范围是0到255。
    • SMALLINT:占用2个字节,有符号的范围是-32768到32767,无符号的范围是0到65535。
    • MEDIUMINT:占用3个字节,有符号的范围是-8388608到8388607,无符号的范围是0到16777215。
    • INT:占用4个字节,有符号的范围是-2147483648到2147483647,无符号的范围是0到4294967295。
    • BIGINT:占用8个字节,有符号的范围是-9223372036854775808到9223372036854775807,无符号的范围是0到18446744073709551615。
  • 浮点数类型:FLOAT和DOUBLE。
    • FLOAT:占用4个字节,精度大约是7位小数。
    • DOUBLE:占用8个字节,精度大约是15位小数。
  • 定点数类型:DECIMAL,用于存储精确的小数,可以避免浮点数的舍入误差,货币金额常用
字符串类型:
  • CHAR和VARCHAR:
    • CHAR用于存储固定长度的字符串;如果存储的字符串长度小于定义的长度,CHAR会在字符串右侧添加空格以填充剩余的空间。所以如果定义一个CHAR(10)的字段,无论你存储的字符串长度是1还是10,都会占用10个字符的空间。
    • VARCHAR用于存储可变长度的字符串;如果存储的字符串长度小于定义的长度,VARCHAR只会存储实际的字符和一个额外用来记录这个字符串长度的字节,字段占用的存储空间会随着存储的数据长度变化。所以如果定义一个VARCHAR(10)的字段,存储一个3个字符长的字符串,那么它只会占用4个字节的空间(3个字符的长度加上1个长度字节)。
  • TEXT:用于存储长文本数据;可以存储最大长度为65535(2^16 - 1)个字符的字符串。TEXT类型的存储需求是:2 + c 字节,其中 c 是实际文本长度。
    • 如果经常需要排序,应避免使用TEXT类型。
    • TEXT字段不能有默认值。
    • 在TEXT字段上,只能对前3072个字节进行搜索。
  • BLOB:用于存储二进制数据。如图像、音频、视频或其他非文本数据。
    • TINYBLOB:最大长度 255 字节。
    • BLOB:最大长度 65,535 字节。
    • MEDIUMBLOB:最大长度 16,777,215 字节。
    • LONGBLOB:最大长度 4,294,967,295 字节。
  • BIT:用于存储位值。BIT(1)可以存储0或1,BIT(2)可以存储从0到3的任何值,以此类推。BIT类型的列最多可以存储64位的值。
日期和时间类型:
  • DATE:用于表示日期。格式为'YYYY-MM-DD',范围从1000-01-01到9999-12-31。
  • TIME:用于表示时间。格式为'HH:MM:SS',范围从'-838:59:59'到'838:59:59'。
  • DATETIME:用于表示日期和时间。格式为'YYYY-MM-DD HH:MM:SS',范围从'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。
  • TIMESTAMP:也用于表示日期和时间,但它的范围较小,从'1970-01-01 00:00:01' UTC到'2038-01-19 03:14:07' UTC。它的一个特性是,TIMESTAMP值在存储时会转换为UTC,检索时会转换回服务器的当前时区。
  • YEAR:用于表示年份,格式为YYYY,范围从1901到2155,以及0000。
其他类型:
  • ENUM:用于存储预定义的值列表中的一个值。这个列表最多可以包含65535个不同的值。
  • SET:用于存储预定义的值列表中的0个、1个或多个值,最大可以存储 64 个不同的值
 
④约束:可以给每个字段设置一个或多个约束,或者不设置约束
💡
MySQL中常用的约束类型
  • 非空约束(NOT NULL):确保字段中的值不能为NULL
  • 唯一约束(UNIQUE):确保在表的所有行中,某列的所有值都是唯一的
  • 默认值约束(DEFAULT):为列提供默认值。如果插入记录时没有为某列指定值,那么将使用DEFAULT约束提供的默认值
  • 主键约束(PRIMARY KEY):是一种特殊的UNIQUE约束,不仅确保某列的值唯一,而且还确保该列的值不会为NULL
  • 自动增长(AUTO_INCREMENT):在新记录插入表时自动为特定的列生成一个唯一的数值。这个功能通常用于主键字段,因为主键字段需要唯一的值来唯一标识每一行
  • 外键约束(FOREIGN KEY):与另一个表的主键字段相关联,每个字段只能设置一个外键约束;也可以多个字段组合形成复合键。
复合外键约束示例
-- 创建 Courses 表 CREATE TABLE Courses ( id INT, name VARCHAR(50), PRIMARY KEY (id, name) ); -- 插入一些示例数据到 Courses 表 INSERT INTO Courses (id, name) VALUES (1, 'Math'); INSERT INTO Courses (id, name) VALUES (2, 'Science'); -- 创建 tb_student 表,并添加复合外键约束 CREATE TABLE tb_student ( stuid INT PRIMARY KEY AUTO_INCREMENT, stuname VARCHAR(20) NOT NULL, stubirth DATE, stugender BIT DEFAULT 1, stutel VARCHAR(11) UNIQUE, course_id INT, course_name VARCHAR(50), CONSTRAINT fk_course FOREIGN KEY (course_id, course_name) REFERENCES Courses (id, name) ); -- 插入一些示例数据到 tb_student 表 -- 插入有效数据 INSERT INTO tb_student (stuname, stubirth, stugender, stutel, course_id, course_name) VALUES ('John Doe', '2000-01-01', 1, '1234567890', 1, 'Math'); -- 尝试插入无效数据(会失败) INSERT INTO tb_student (stuname, stubirth, stugender, stutel, course_id, course_name) VALUES ('Jane Doe', '2001-02-02', 0, '0987654321', 3, 'History'); -- 这行会失败,因为 (3, 'History') 不在 Courses 表中
CREATE TABLE IF NOT EXISTS tb_student ( stuid INT PRIMARY KEY AUTO_INCREMENT, -- 主键约束,自动增长 stuname VARCHAR(20) NOT NULL, -- 非空约束 stubirth DATE, -- 不设置约束 stugender bit DEFAULT 1, -- 默认值约束 stutel VARCHAR(11) UNIQUE, -- 唯一约束 course_id INT, course_name VARCHAR(20), PRIMARY KEY (stuid), -- 主键约束还可以单独设置 FOREIGN KEY (course_id, course_name) REFERENCES Courses(id, name), -- 外键约束;只能在 tb_student 表的 course_id 字段中插入在 Courses 表的 id 字段中存在的值,这样可以确保每个学生都注册了一个存在的课程,防止出现无效的数据。其中的 id 和 name 一一对应。 CONSTRAINT fk_course FOREIGN KEY (course_id, course_name) REFERENCES Courses (id, name) -- 复合外键约束 );

2.2.2、删除数据表

DROP TABLE IF EXISTS 表名; DROP TABLE IF EXISTS tb_student;

2.2.3、修改数据表

-- 添加字段 ALTER TABLE 表名 ADD COLUMN 字段名 类型 约束; ALTER TABLE tb_student ADD COLUMN stuaddr VARCHAR(200); -- 删除字段 ALTER TABLE 表明 DROP COLUMN 字段名; ALTER TABLE tb_student DROP COLUMN stuaddr; -- 修改字段 ALTER TABLE 表名 CHANGE [column] 原字段 新字段 新类型; -- 修改字段名 ALTER TABLE tb_student CHANGE tel stutel VARCHAR(11); ALTER TABLE 表名 MODIFY [COLUMN] 字段名 新数据类型 新类型长度 新默认值 新注释; -- 修改字段类型 -- 添加约束 ALTER TABLE 表名 ADD CONSTRAINT 约束索引名 约束名(字段); -- 给指定字段添加指定约束 ALTER TABLE 表1 ADD CONSTRAINT 约束索引名 FOREIGN KEY(字段1) REFERENCES 表2(字段2); -- 给表1中的字段1添加外键约束,并且字段1的值依赖表2中的字段2 -- 删除约束 ALTER TABLE 表名 DROP INDEX 约束索引名; -- 删除指定约束 ALTER TABLE 表名 DROP FOREIGN KEY 外键索引; -- 删除外键约束

2.3、账户操作

2.3.1、创建账户

-- 创建指定用户 CREATE USER '用户名'@'登陆地址'; -- 用户登陆时不用输入密码 CREATE USER '用户名'@'登陆地址' IDENTIFIED BY '密码'; -- 用户登陆时需要输入密 CREATE USER 'username'@'host' IDENTIFIED BY 'password';
💡
登录地址
  • ip地址
  • localhost(本机)
  • %(任何主机)

2.3.2、删除账户

DROP USER '用户名'; -- 删除所有主机上的账户 DROP USER '用户名'@'host'; -- 删除指定主机上的账户

三、DML数据操作

3.1、INSERT(增)

-- 按表中字段的顺序依次给每个字段赋值,最终形成一条新的数据记录 INSERT INTO 表名 VALUES(值1, 值2, 值3,...); INSERT INTO tb_student VALUES(1, '天明', '2000-10-2', 1, '12345678901'); -- 按指定顺序给指定字段赋值,最终形成一条新的记录 INSERT INTO 表名(字段名1,字段名2,...) VALUES(值1,值2,...); INSERT INTO tb_student(stuname, stubirth, stugender, stutel) VALUES('少羽', '2000-8-21', 1, '12345678902'); -- 同时插入多条记录 INSERT INTO 表名(字段名1,字段名2,...) VALUES(值1,值..…),(值11,值22,..),(值111,值2222,...),...; INSERT INTO tb_student(stuname, stubirth, stugender, stutel) VALUES ('月儿', '2000-11-21', 0, '12345678903'), ('卫庄', '1989-8-21', 1, '12345678904'), ('盖聂', '1990-8-21', 1, '12345678905'), ('胜七', '1977-8-21', 1, '12345678906'), ('惊鲵', '1971-8-21', 0, '12345678907'), ('掩日', '1972-8-21', 1, '12345678908'), ('黑白玄鉴', '1970-8-21', 1, '12345678909'), ('真刚', '1973-8-25', 1, '12345678910');

3.2、DELETE(删)

-- 删除当前表中所有的记录(清空表) DELETE FROM tb_student; -- 会记录删除操作日志 TRUNCATE TABLE 表名; -- 删除所有记录但保留表结构,更快,不会记录删除操作日志(推荐) -- 删除所有满足条件的记录 DELETE FROM 表名 WHERE 条件; DELETE FROM tb_student WHERE stuname='真刚'; -- 比较运算:=(等于),<>(不等于),>,<,>=,<= DELETE FROM tb_student WHERE stuid>=35 AND stugender<>1; -- 逻辑运算:and(逻辑与),or(逻辑或),not(逻辑非); DELETE FROM tb_student WHERE stuname in ('逍遥子', '韩信', '断水'); -- 集合包含:in DELETE FROM tb_student WHERE stubirth BETWEEN '1976-8-21' AND '1976-8-31'; -- 范围:between...and DELETE FROM tb_student WHERE (stubirth IS NULL) AND (stutel IS NULL); -- 判断是否为空:is null,is not null DELETE FROM tb_student WHERE stuname LIKE '盖_ '; -- LIKE,_:表示单个任意字符; DELETE FROM tb_student WHERE stutel LIKE '%2_'; -- LIKE,%:任意个任意字符;

3.3、SELECT(查)

-- 查询指定表中所有的记录中所有字段的数据 SELECT * FROM 表名; SELECT * FROM tb_student; -- 限制和偏移 SELECT * FROM 表名 LIMIT N; -- 查询前N条数据 SELECT * FROM tb_record LIMIT 5; SELECT * FROM 表名 LIMIT M OFFSET N; -- 跳过前N条数据获取M条数据(从第N+1条数据开始,获取M条数据) SELECT * FROM 表名 LIMIT N, M; -- 与上面👆的含义相同,只是写法不同 SELECT * FROM tb_record LIMIT 5, 10; -- 去重 SELECT DISTINCT 字段名 FROM 表名; SELECT DISTINCT redate FROM tb_record; SELECT COUNT(DISTINCT id) from table_1 -- 查询表中指定字段的所有数据 SELECT 字段1, 字段2, ... FROM 表名; SELECT stuname, stugender, stuaddr FROM tb_student; -- 列名重命名 SELECT 字段名1 AS '新字段名1', 字段名2,字段名3 AS '新字段名3', ... FROM 表名; SELECT stuname AS '姓名', stugender AS '性别', stuaddr AS '地址' from tb_student -- 结果重新赋值(主要针对布尔) SELECT IF(字段名, 值1, 值2) FROM 表名; -- 如果if中对应的字段的值是1,最后结果是值1,否则是值2(mysql特有写法) SELECT stuname AS '姓名', IF(stugender, '男', '女') AS '性别', stuaddr AS '地址' from tb_student SELECT CASE 字段 WHEN 1或0 THEN 新值1 ELSE 新值2 END FROM 表名; SELECT stuname AS '姓名', CASE stugender WHEN 1 THEN '男' ELSE '女' END AS '性别', stuaddr AS '地址' from tb_student -- 列合并(查询的时候将多个字段合并成一个数据返回结果) SELECT CONCAT(字段1,字段2,...) FROM 表名; SELECT CONCAT(stuname, stuid) as '姓名学号' FROM tb_student; SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM tb_student; -- 筛选(参考上一节【DELETE(删)】中的条件使用) SELECT * FROM 表名 WHERE 条件; SELECT stuname, stutel FROM tb_student WHERE stugender=0; SELECT * FROM tb_student WHERE stuname LIKE '月_'; -- 排序 SELECT * FROM 表名 ORDER BY 字段; -- 将查询结果按指定字段的值从小到大排序 SELECT * FROM 表名 ORDER BY 字段 ASC; -- 将查询结果按指定字段的值从小到大排序,可以省略 ASC SELECT * FROM tb_student ORDER BY stuid; SELECT * FROM 表名 ORDER BY 字段 DESC; -- 将查询结果按指定字段的值从大到小排序 SELECT * FROM tb_student ORDER BY stuid DESC; SELECT * FROM 表名 ORDER BY 字段1 DESC, 字段2 DESC; -- 先按字段1倒序,再按字段2倒序 SELECT * FROM tb_student ORDER BY stugender DESC, stubirth; -- 分组;注意:分组后要加条件,则用having代替where SELECT 聚合操作 FROM 表名 GROUP BY (字段); SELECT sid, AVG(score) FROM tb_record GROUP BY (sid); -- 获取每个学生所有学科的平均分 SELECT cid, AVG(score) FROM tb_record GROUP BY (cid); -- 获取每个学科的平均分 SELECT sid, COUNT(cid) FROM tb_record GROUP BY (sid); -- 获取每个学生选的课程的数量 -- 聚合 SELECT COUNT(score) AS '个数' FROM tb_record; -- 只会统计该列的非空值,不包含空值 SELECT COUNT(1) AS '个数' FROM tb_record; -- 会统计表中所有的行,包含空值 SELECT COUNT(*) AS '个数' FROM tb_record; -- 会统计表中所有的行,包含空值 SELECT MAX(score) AS '最高分' FROM tb_record; SELECT MIN(score) AS '最低分' FROM tb_record; SELECT SUM(score) AS '总分' FROM tb_record; -- 求和所有分数的和,如果某一个记录的分数是空,那么这条记录不存与运算 SELECT AVG(score) AS '平均分' FROM tb_record; -- 只统计非空的数据 SELECT GROUP_CONCAT(name) FROM students; -- MySQL特有函数,将同一列中的多个值连接成一个由逗号分隔的字符串 /* 子查询 */ -- 1、将一个查询的结果作为另外一个查询的条件 SELECT sid, score FROM tb_record WHERE score=(SELECT MAX(score) FROM tb_record); SELECT sid FROM tb_record WHERE score IN (SELECT DISTINCT(score) FROM tb_record ORDER BY (score)DESC LIMIT 3); SELECT sid FROM tb_record GROUP BY (sid) HAVING COUNT(cid)>2; SELECT stuname FROM tb_student WHERE stuid IN (SELECT sid FROM tb_record GROUP BY (sid) HAVING COUNT(cid)>2); -- 2、将一个查询的结果作为另外一个查询的对象 -- 注: 若将查询结果作为查询对象,则必须在使用时用as重命名 SELECT * FROM tb_student LIMIT 4,5; SELECT stuname FROM (SELECT * FROM tb_student LIMIT 4,5) AS t1; SELECT stuname AS sname, stuaddr AS saddr FROM tb_student WHERE stusex=0; SELECT sname, saddr FROM (SELECT stuname AS sname, stuaddr AS saddr FROM tb_student WHERE stusex=0) AS t2 WHERE saddr LIKE '%成都'; /* 连接查询 */ -- 1、内连接 -- 方法1:SELECT * FROM 表名1, 表名2, ... WHERE 连接条件 查询条件;(旧式的语法,可能导致笛卡尔积,不建议) SELECT ename, sal, dname FROM tb_emp, tb_dept, ( SELECT dno AS dno2, MAX(sal) AS max_sal FROM tb_emp GROUP BY(dno) ) AS t2 WHERE tb_emp.dno=tb_dept.dno AND tb_emp.dno=t2.dno2 AND sal=max_sal; -- 查询部门中薪水最高的人姓名、工资和所在部门名称 -- 方法2:SELECT * FROM t1 INNER JOIN t2 ON 连接条件1 INNER JOIN t3 ON 连接条件2; -- 注意:如果既有连接条件又有查询条件,查询条件必须放在连接条件的后面 SELECT ename, sal, dname, t2.max_sal FROM tb_emp INNER JOIN tb_dept ON tb_emp.dno=tb_dept.dno INNER JOIN ( SELECT dno AS dno2, MAX(sal) AS max_sal FROM tb_emp GROUP BY dno ) AS t2 ON tb_emp.dno=t2.dno2 WHERE sal=t2.max_sal; -- 查询部门中薪水最高的人姓名、工资和所在部门名称 -- 2、外连接;在MySQL中只支持左外连接(LEFT JOIN)和右外连接(RIGHT JOIN) -- 左外连接(LEFT JOIN):SELECT * FROM 表1 LEFT JOIN 表2; -- 返回包括左表中的所有记录和右表中联结字段相等的记录。如果在右表中没有匹配的记录,则结果是 NULL。 SELECT columns FROM table1 LEFT JOIN table2 ON table1.column = table2.column; -- 右外连接(RIGHT JOIN):SELECT * FROM 表1 RIGHT JOIN 表2; -- 返回包括右表中的所有记录和左表中联结字段相等的记录。如果在左表中没有匹配的记录,则结果是 NULL。 SELECT columns FROM table1 RIGHT JOIN table2 ON table1.column = table2.column; /* 查询(上下)拼接 */ -- 1、UNION;用于合并两个或多个 SELECT 语句的结果集;默认去除重复的记录 -- 要求:每个SELECT语句必须有相同数量的列,列的数据类型也必须相似,并且列的顺序必须一致。 SELECT column1, column2, ... FROM table1 UNION SELECT column1, column2, ... FROM table2; -- 2、UNION ALL;默认保留除重复的记录 SELECT id, name FROM employees UNION ALL SELECT id, name FROM managers;

3.4、UPDATE(改)

ALTER TABLE tb_student ADD COLUMN stuaddr VARCHAR(200); -- 将指定表中所有记录中指定的字段修改成指定的值 UPDATE 表名 SET 字段1=新值1,字段2=新值2,...; UPDATE tb_student SET stuaddr='成都'; -- 将满足条件的记录的指定字段设置为指定的值 UPDATE 表名 SET 字段1=新值1,字段2=新值2,... WHERE 条件; UPDATE tb_student SET stuaddr='昆明' WHERE stugender=0; -- 连表更新 UPDATE table1 JOIN table2 ON table1.column_name = table2.column_name SET table1.column1 = table2.column2, table1.column2 = expression WHERE condition;

四、DCL数据控制

DCL主要用于控制不同用户对数据库的访问权限。DCL包括两个主要的命令:GRANT和REVOKE。
GRANT 命令用于授予用户对数据库对象(数据库、表、视图)的访问权限。
REVOKE 命令用于撤销用户对数据库对象的访问权限。
-- 授权 GRANT 权限列表 ON 对象 TO 用户 [IDENTIFIED BY '密码']; GRANT SELECT, INSERT ON testdb.* TO 'user1'@'localhost'; GRANT ALL PRIVILEGES ON testdb.employees TO 'user2'@'localhost' IDENTIFIED BY 'password'; -- 数据库级别的授权 GRANT 权限列表 ON 数据库名.* TO '用户名'@'主机'; GRANT ALL PRIVILEGES ON testdb.* TO 'user1'@'localhost'; -- 授予用户 user1 对数据库 testdb 的所有权限 -- 表级别的授权 GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机'; GRANT SELECT, INSERT ON testdb.employees TO 'user1'@'localhost'; -- 授予用户 user1 对表 employees 的 SELECT 和 INSERT 权限 -- 列级别的授权 GRANT 权限 (列列表) ON 数据库名.表名 TO '用户名'@'主机'; GRANT SELECT (firstname, lastname) ON testdb.employees TO 'user1'@'localhost'; -- 授予用户 user1 对表 employees 的 firstname 和 lastname 列的 SELECT 权限 -- 存储过程和函数级别的授权 GRANT EXECUTE ON PROCEDURE 数据库名.存储过程名 TO '用户名'@'主机'; GRANT EXECUTE ON FUNCTION 数据库名.函数名 TO '用户名'@'主机'; GRANT EXECUTE ON PROCEDURE testdb.update_salary TO 'user1'@'localhost'; -- 授予用户 user1 执行存储过程 update_salary 的权限 GRANT EXECUTE ON FUNCTION testdb.calculate_bonus TO 'user1'@'localhost'; -- 授予用户 user1 执行函数 calculate_bonus 的权限 -- 全局级别的授权 GRANT 权限列表 ON *.* TO '用户名'@'主机'; GRANT ALL PRIVILEGES ON *.* TO 'user1'@'localhost'; -- 授予用户 user1 对所有数据库和表的所有权限 -- 撤销授权 REVOKE 权限列表 ON 对象 FROM 用户; REVOKE SELECT, INSERT ON testdb.* FROM 'user1'@'localhost'; REVOKE ALL PRIVILEGES ON testdb.employees FROM 'user2'@'localhost'; -- 显示用户的权限 SHOW GRANTS FOR 'user1'@'localhost'; -- 修改用户密码 SET PASSWORD FOR 'user1'@'localhost' = PASSWORD('newpassword');
💡
常见权限类型:
  • ALL PRIVILEGES:授予所有权限。
  • SELECT:授予读取数据的权限。
  • INSERT:授予插入数据的权限。
  • UPDATE:授予更新数据的权限。
  • DELETE:授予删除数据的权限。
  • CREATE:授予创建数据库和表的权限。
  • DROP:授予删除数据库和表的权限。
  • GRANT OPTION:授予用户授予其他用户权限的权限。

五、数据库对象

5.1、视图

在 SQL 中,视图(View)是一个虚拟表,它并不在数据库中存储数据,而是基于 SQL 查询的结果集。视图的创建通过 CREATE VIEW 语句,它可以简化复杂查询,提高安全性和数据抽象。视图通常是只读的
视图的优点:
  1. 简化复杂查询:视图可以将复杂的 SQL 查询封装成一个简单的查询。
  1. 提高安全性:可以通过视图限制用户访问特定的行和列,从而保护数据。
  1. 数据抽象:视图提供了一种抽象层,使得基础表的结构变化不影响用户查询。
  1. 方便数据处理:可以在视图中使用聚合函数、连接等操作,简化数据处理。
视图的限制:
  1. 性能影响:视图在每次查询时动态生成,可能会影响查询性能,特别是复杂视图。
  1. 不可更新:包含聚合、连接等操作的视图通常是不可更新的。
  1. 依赖性:视图依赖于基础表的结构,如果基础表结构变化,视图可能需要相应修改。
实际应用中的视图:
  1. 简化报表查询:创建视图用于生成复杂的报表。
  1. 数据安全性:创建只包含特定列或行的视图,限制用户访问敏感数据。
  1. 分层架构:在多层应用程序架构中,视图可以作为数据访问层的一部分,提供稳定的接口。

5.1.1、创建视图

-- 基础语法 CREATE VIEW 视图名 AS sql查询语句; CREATE VIEW 视图名 AS SELECT 列名1, 列名2, ... FROM 表名 WHERE 条件; -- 示例 CREATE VIEW emp_dept AS SELECT e.employee_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id;

5.1.2、使用视图

创建视图后,可以像操作表一样操作视图。
-- 查询视图 SELECT * FROM emp_dept; ---- 更新视图(视图的更新受限于视图的定义,特别是包含连接、多表、聚合函数的视图通常不能直接更新。) -- 语法 UPDATE 视图名 SET 列名 = 值 WHERE 条件; -- 示例开始 CREATE VIEW simple_view AS SELECT employee_id, employee_name FROM employees; UPDATE simple_view SET employee_name = 'John Doe' WHERE employee_id = 1; -- 示例结束 -- 删除视图 DROP VIEW 视图名; DROP VIEW emp_dept;

5.2、索引

在 SQL 中,索引是一种数据结构,用于提高数据库查询的速度。索引类似于书籍的目录,通过索引可以快速找到所需的数据,而不需要扫描整个表。常见的索引结构有 B-Tree 和 Hash。
  • B-Tree 索引:适用于范围查询、大量的插入、更新和删除操作。
  • Hash 索引:适用于精确匹配查询。

5.2.1、创建索引

-- 主键索引 (Primary Key Index) CREATE TABLE employees ( emp_id INT PRIMARY KEY, name VARCHAR(50), department VARCHAR(50) ); -- 唯一索引 (Unique Index):确保索引列的所有值唯一 CREATE UNIQUE INDEX idx_unique_name ON employees(name); -- 普通索引 (Normal Index):没有唯一性限制,用于加速查询 CREATE INDEX idx_department ON employees(department); -- 全文索引 (Full-Text Index):用于全文搜索(仅支持 CHAR、VARCHAR、TEXT 列) CREATE FULLTEXT INDEX idx_fulltext_name ON employees(name); -- 复合索引 (Composite Index):在多个列上创建索引 CREATE INDEX idx_name_department ON employees(name, department);

5.2.2、删除索引

-- 方式 1 DROP INDEX idx_name_department ON employees; -- 适用于仅需删除单个索引的场景,语法更简洁明了。 -- 方式 2 ALTER TABLE tb_student DROP INDEX idx_stuname; ALTER TABLE tb_student DROP INDEX idx_stuaddr; -- 适用于需要同时进行多种表结构修改的场景,例如在一个操作中添加和删除多个索引。 -- 通常与其他表修改操作结合使用。

5.2.3、其他

使用场景
  1. 加速查询:索引可以显著提高 SELECT 查询的速度,特别是 WHERE 子句、JOIN 操作和 ORDER BY 子句。
  1. 确保唯一性:唯一索引确保列的值唯一,例如电子邮件地址或用户名。
  1. 全文搜索:全文索引用于需要文本搜索的场景,如搜索文章内容或产品描述。
索引的注意事项
  1. 索引开销:索引会占用额外的存储空间,并在插入、更新和删除数据时增加维护开销。
  1. 选择性高的列:对选择性高(不同值较多)的列创建索引效果最好。
  1. 适度使用:不要在每个列上都创建索引,应根据查询需求和性能测试结果来决定。
  1. 复合索引顺序:复合索引的顺序非常重要,应该根据查询中 WHERE 子句的使用情况来决定。

5.3、事务

在 SQL 中,事务(Transaction)是一个或多个 SQL 语句的集合,这些语句作为一个单独的工作单元执行。事务的目的是确保所有操作要么全部成功,要么全部失败,以保持数据库的一致性和完整性。事务主要用于处理操作步骤多且必须保持一致性的场景,比如银行转账、订单处理等。

5.3.1、事务的特性(ACID)

事务具有四个主要特性,通常称为 ACID 属性:
  1. 原子性(Atomicity):事务中的所有操作要么全部成功,要么全部失败。失败时,数据库会回滚到事务开始前的状态。
  1. 一致性(Consistency):事务必须使数据库从一个一致性状态转换到另一个一致性状态。事务开始前和结束后,数据库的所有规则都必须保持有效。
  1. 隔离性(Isolation):一个事务的执行不应受到其他事务并发操作的影响。事务应该独立执行。
  1. 持久性(Durability):一旦事务提交,对数据库的修改就应该永久保存下来,即使系统发生故障也不应丢失。

5.3.2、事务的控制语句

  • START TRANSACTION:开始一个新的事务。
  • COMMIT:提交当前事务,使其更改永久保存到数据库。
  • ROLLBACK:回滚当前事务,撤销事务中所做的所有更改。
  • SAVEPOINT:设置事务中的保存点,可以回滚到这个保存点。
  • RELEASE SAVEPOINT:删除一个保存点。
  • ROLLBACK TO SAVEPOINT:回滚到指定的保存点。

5.3.3、事务的使用示例

账户表结构
CREATE TABLE accounts ( account_id INT PRIMARY KEY, balance DECIMAL(10, 2) ); INSERT INTO accounts (account_id, balance) VALUES (1, 1000.00), (2, 500.00);
使用事务进行转账操作
START TRANSACTION; -- 从账户 A 中扣除转账金额 UPDATE accounts SET balance = balance - 100.00 WHERE account_id = 1; -- 向账户 B 中添加转账金额 UPDATE accounts SET balance = balance + 100.00 WHERE account_id = 2; -- 检查余额是否足够(这只是一个简单的逻辑检查,实际情况可能更复杂) SELECT balance INTO @balance FROM accounts WHERE account_id = 1; -- INTO @variable 语法用于将查询结果赋值给用户定义的变量 IF @balance >= 0 THEN -- 如果余额足够,提交事务 COMMIT; ELSE -- 如果余额不足,回滚事务 ROLLBACK; END IF;
使用保存点
保存点允许在事务中设定多个回滚点,可以在需要时部分回滚而不是整个事务。
START TRANSACTION; -- 执行第一部分操作 UPDATE accounts SET balance = balance - 100.00 WHERE account_id = 1; SAVEPOINT sp1; -- 执行第二部分操作 UPDATE accounts SET balance = balance + 100.00 WHERE account_id = 2; SAVEPOINT sp2; -- 决定回滚到第一个保存点 ROLLBACK TO SAVEPOINT sp1; -- 提交事务 COMMIT;

5.3.4、事务隔离级别

事务的隔离级别决定了事务之间的相互影响程度。MySQL 支持以下四种隔离级别:
  • 读未提交(READ UNCOMMITTED):一个事务可以读取另一个事务未提交的数据。这可能导致脏读(dirty read)。
  • 读已提交(READ COMMITTED):一个事务只能读取另一个事务已提交的数据。避免了脏读,但可能导致不可重复读(non-repeatable read)。
  • 可重复读(REPEATABLE READ):一个事务在读取某数据时,总是看到相同的值,避免了不可重复读,但可能导致幻读(phantom read)。这是 MySQL 的默认隔离级别。
  • 可串行化(SERIALIZABLE):所有事务按顺序执行,避免了上述所有问题,但性能较差,因为事务可能需要等待。
设置隔离级别
-- 设置全局隔离级别 SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- 设置当前会话的隔离级别 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 
对于本文内容有任何疑问, 可与我联系.