曾经``沧海

曾经``沧海

关注
发信
关注(28)粉丝(399)

【SQL】❤️数据库理论加实践详细教程❤️实践出真知❤️

SQL(结构化查询语言)

【SQL】❤️数据库理论加实践详细教程❤️实践出真知❤️-LMLPHP

基础部分

SQL作用

按照作用划分可以划分为四个模块，从而由此行文

  1. DDL（数据定义语言）:
    DDL涉及的命令允许用户定义或修改数据库的结构。主要命令包括：

    • CREATE：用于创建新的数据库对象，如表、视图、索引等。
    • ALTER：用于修改现有数据库对象的结构。
    • DROP：用于删除数据库对象。
    • TRUNCATE：用于删除表中的数据，但保留表结构。

  2. DML（数据操纵语言）:
    DML涉及的操作允许用户对数据库中的数据进行各种操纵。主要命令包括：

    • INSERT：向表中添加新行。
    • UPDATE：修改表中的现有数据。
    • DELETE：从表中删除数据。

  3. DQL（数据查询语言）:
    DQL基本上只涉及一个命令，那就是SELECT，但它非常强大和复杂，允许用户检索数据库中的数据，并支持多种选项和构造，如WHERE子句、JOINs和子查询等。

  4. DCL（数据控制语言）:
    DCL包括用于配置或限制用户对数据库所做更改的权限的命令。主要包括：

    • GRANT：授权用户访问和操纵数据库的权限。
    • REVOKE：撤销用户的权限。

  5. TCL（事务控制语言）：
    TCL用于管理数据库中的事务。这包括以下命令：

    • COMMIT：提交一个事务，使所有数据变更成为永久性的。
    • ROLLBACK：回滚事务，撤销自上一个提交以来执行的所有未提交的变更。
    • SAVEPOINT：在事务内部设置一个回滚点。

SQL数据类型

安装MySQL

  • 使用phpstudy安装MySQL

    1. 下载PHPstudy
      从phpstudy官网下载最新phpstudy版本(https://www.xp.cn/download.html)，目前最新版本为v8.1。根据需要选择对应版本下载，

    2. 安装phpstudy
      解压后，运行安装程序，默认安装即可。安装结束后，运行程序，启动mysql数据库。

    3. 将mysql添加到系统变量
      (1) 找到phpstudy安装文件的中mysql的bin文件目录，默认路径为D:\phpstudy_pro\Extensions\MySQL5.7.26\bin
      (2) 桌面右键计算机，win10 系统为“此电脑”。依次找到“属性”——“高级系统设置”——“环境变量”——“系统变量”——“Path”——“编辑”

    4. 登录数据库
      快捷键“win+R”，输入cmd，回车运行。
      mysql -u root -p , 随后输入口令即可,默认为root

  • 官网下载MySQLCommunityServer版本
  • Linux上安装MySQL
    可以使用发行版的包管理器。例如，Debian和Ubuntu用户可以简单地通过命令apt-get install mysql-server安装最新的MySQL版本。

关系模型

关系模型本质上就是若干个存储数据的二维表，可以把它们看作很多Excel表,一张表中的每一行数据被称为一条记录。一条记录就是由多个字段组成

主键

  • 1.什么是主键？
    对于关系表，有个很重要的约束，就是任意两条记录不能重复。不能重复不是指两条记录不完全相同，而是指能够通过某个字段唯一区分出不同的记录，这个字段被称为主键。

  • 2.如何选择主键？
    对主键的要求，最关键的一点是：记录一旦插入到表中，主键最好不要再修改，因为主键是用来唯一定位记录的，修改了主键，会造成一系列的影响。
    主键不要带有业务含义，而应该使用BIGINT自增或者GUID类型。主键也不应该允许NULL。

  • 3.常见的可作为id字段的类型？

    • INT自增类型：数据库会在插入数据时自动为每一条记录分配一个自增整数，这样我们就完全不用担心主键重复，也不用自己预先生成主键；
    • 唯一GUID类型：使用一种全局唯一的字符串作为主键，类似8f55d96b-8acc-4636-8cb8-76bf8abc2f57。GUID算法通过网卡MAC地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的，大部分编程语言都内置了GUID算法，可以自己预算出主键。

外键

关系数据库通过外键可以实现一对多、多对多和一对一的关系。外键既可以通过数据库来约束，也可以不设置约束，仅依靠应用程序的逻辑来保证。

  • 1.什么是外键？
    在students表中，通过class_id的字段（是class班级表的主键），可以把数据与另一张表关联起来，这种列称为外键。

  • 2.外键实现？
    外键并不是通过列名实现的，而是通过定义外键约束实现的：

ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id);

其中，外键约束的名称fk_class_id可以任意，FOREIGN KEY (class_id)指定了class_id作为外键，REFERENCES classes (id)指定了这个外键将关联到classes表的id列（即classes表的主键）。
通过定义外键约束，关系数据库可以保证无法插入无效的数据。即如果classes表不存在id=99的记录，students表就无法插入class_id=99的记录。
要删除一个外键约束，也是通过ALTER TABLE实现的：

ALTER TABLE students
DROP FOREIGN KEY fk_class_id;

注意：删除外键约束并没有删除外键这一列。删除列是通过DROP COLUMN …实现的。

  • 3.多对多关系？
    多对多关系实际上是通过两个一对多关系实现的，即通过一个中间表，关联两个一对多关系，就形成了多对多关系。

  • 4.一对一关系？
    一对一关系是指，一个表的记录对应到另一个表的唯一一个记录。

隐式索引

  • 1对列创建索引？
    如果要经常根据score列进行查询，就可以对score列创建索引：
ALTER TABLE students
ADD INDEX idx_score (score);

索引的效率取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高。

  • 2.索引的优缺点？
    索引的优点是提高了查询效率，缺点是在插入、更新和删除记录时，需要同时修改索引，因此，索引越多，插入、更新和删除记录的速度就越慢。
    对于主键，关系数据库会自动对其创建主键索引。使用主键索引的效率是最高的，因为主键会保证绝对唯一。

  • 3.创建唯一索引？
    有业务含义但是唯一的列，不适合作为主键但适合作为索引。
    我们假设students表的name不能重复,通过UNIQUE关键字我们就添加了一个唯一索引：

ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);

也可以只对某一列添加一个唯一约束而不创建唯一索引,这种情况下 name列没有索引，但仍然具有唯一性保证：

ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name);
  • 4.小结
    • 通过对数据库表创建索引，可以提高查询速度。
    • 通过创建唯一索引，可以保证某一列的值具有唯一性。
    • 数据库索引对于用户和应用程序来说都是透明的。

功能部分

查询数据（DQL）

准备数据

执行.sql脚本，这个过程需要熟悉SQL WorkBench工具

基本查询

SELECT * 
FROM <表名>

使用SELECT查询的基本语句SELECT * FROM <表名>可以查询一个表的所有行和所有列的数据。SELECT查询的结果是一个二维表。

条件查询

SELECT * 
FROM <表名> 
WHERE <条件表达式>
  • 常用的条件表达式

投影查询

SELECT 列名1  AS 别名1, 列名2 AS 别名2 
FROM <表名> 
WHERE <条件表达式>

排序

-- ASC升序排列|DESC降序列
SELECT <列名1>,<列名2>...
FROM <表名> 
WHERE <条件表达式> 
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];

分页显示

-- page_size 是每页要显示的记录数。  
-- offset_value 是要跳过的记录数。 
SELECT <列名1>,<列名2>...
FROM <表名> 
WHERE <条件表达式> 
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC]
LIMIT page_size OFFSET offset_value;

聚合查询

对于统计总数、平均数这类计算，SQL提供了专门的聚合函数，使用聚合函数进行查询，就是聚合查询,支持使用WHERE条件

SELECT <聚合函数名（列名）> AS <别名>
FROM <表名>
WHERE <条件表达式> ;
-- 例：使用聚合查询计算一班男生的平均成绩:
SELECT AVG(score) average FROM students WHERE class_id = '1' AND gender = 'M';

聚合函数

分组聚合

-- 例：按class_id分组:
SELECT class_id, COUNT(*) AS num 
FROM students GROUP BY class_id;

-- 例：使用分组聚合计算各班男女生成绩，多维度分组
SELECT class_id, gender, AVG(score) AS average_score
FROM students
GROUP BY class_id, gender
ORDER BY class_id, gender;

多表查询(笛卡尔查询)

使用多表查询可以获取M x N行记录；多表查询的结果集可能非常巨大，要小心使用。

SELECT * FROM <表1> <表2>;

多表查询时,为避免多表列名重复问题,要使用表名.列名这样的方式来引用列和设置别名.SQL还允许给表设置一个别名，让我们在投影查询中引用起来稍微简洁一点表名 AS 表别名(AS可以省略)

-- 例：多表查询
SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c
WHERE s.gender = 'M' AND c.id = 1;

连接查询

连接查询是另一种类型的多表查询。连接查询对多个表进行JOIN连接，连接查询需要先确定主表，然后把另一个表的数据“JOIN”到结果集上。连接查询仍然可以使用WHERE条件和ORDER BY排序。

SELECT ... FROM <表1> INNER JOIN <表2> ON <条件...>；
  • 内连接INNER JOIN查询的写法：
  1. 先确定主表，仍然使用FROM <表1>的语法；
  2. 再确定需要连接的表，使用INNER JOIN <表2>的语法；
  3. 然后确定连接条件，使用ON <条件…>，这里的条件是s.class_id = c.id，表示students表的class_id列与classes表的id列相同的行需要连接；
  4. 可选：加上WHERE子句、ORDER BY等子句。
-- 例子：内连接
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
INNER JOIN classes c
ON s.class_id = c.id;

  • 内连接，左外连接，右外连接的区别？

    • 匹配性：内连接仅返回两个表中匹配的记录；左外连接返回左表的所有记录，即便某些记录在右表中没有匹配；右外连接则返回右表的所有记录，即便某些记录在左表中没有匹配。
    • 结果集中的NULL：在左外连接和右外连接中，如果一个表中的行在另一个表中没有匹配项，结果集将包含NULL值，以填充缺失的字段。
    • 使用场景不同：内连接用于查询需要严格匹配的场景，而外连接（左外和右外）适用于需要包含未匹配记录的场景。

  • 如何选择连接方式？

    • INNER JOIN是选出两张表都存在的记录
    • LEFT OUTER JOIN是选出左表存在的记录
    • RIGHT OUTER JOIN是选出右表存在的记录
    • FULL OUTER JOIN则是选出左右表都存在的记录

修改数据（DML）

INSERT(增)

INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);

-- 例：添加一条新记录
INSERT INTO students (class_id, name, gender, score) VALUES (2, '大牛', 'M', 80);

-- 例：一次性添加多条新记录
INSERT INTO students (class_id, name, gender, score) VALUES
  (1, '大宝', 'M', 87),
  (2, '二宝', 'M', 81);

UPDATE(改)

UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;

-- 例：更新一条记录
UPDATE students SET name='大牛', score=66 WHERE id=1;

-- 例：一次性更新多条记录
UPDATE students SET name='小牛', score=77 WHERE id>=5 AND id<=7;

-- 例：使用表达式更新字段
UPDATE students SET score=score+10 WHERE score<80;

-- 特别小心：不带WHERE会全更新
UPDATE students SET name='大牛', score=66

DELETE(删)

DELETE FROM <表名> WHERE ...;

-- 例：删除一条记录
DELETE FROM students WHERE id=1;

-- 例：一次性删除多条记录
DELETE FROM students WHERE id>=5 AND id<=7;

-- 特别小心：不带WHERE会全删
DELETE FROM students;

修改结构（DDL）（以Mysql数据库为例）

要管理MySQL，可以使用可视化图形界面MySQL Workbench。它对MySQL的操作本质上仍然是发送SQL语句并执行。很多时候，通过SSH远程连接时，只能使用SQL命令，所以，了解并掌握常用的SQL管理操作是必须的。

数据库管理

-- 连接到数据库
mysql -u root -p

-- 创建数据库
CREATE DATABASE test;

-- 列出所有数据库,显示的数据库中，information_schema、mysql、performance_schema和sys是系统库，不要去改动它们
mysql> SHOW DATABASES;

-- 切换当前数据库
mysql> USE test;

表管理

-- 列出当前数据库的所有表
mysql> SHOW TABLES;

-- 查看一个表的结构
mysql> DESCRIBE students;

-- 查看创建表的SQL语句
mysql> SHOW CREATE TABLE students;

-- 创建和删除表
mysql> CREATE TABLE students;
mysql> DROP TABLE students;

列管理

-- 给students表新增一列birth
ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;

-- 把列名改为birthday，类型改为VARCHAR(20)
ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;

-- 删除列
ALTER TABLE students DROP COLUMN birthday;

-- 退出MySQL
mysql> EXIT

修改权限（DCL）

赋予用户或角色某种权限或角色

GRANT SELECT, INSERT ON database.table TO 'username'@'host';

这条命令赋予指定用户在特定数据库的特定表上执行SELECT和INSERT操作的权限。

移除用户或角色的权限或角色

REVOKE SELECT, INSERT ON database.table FROM 'username'@'host';

这条命令移除指定用户在特定数据库的特定表上执行SELECT和INSERT操作的权限。

05-02 17:33
Powered by LMLPHP ©2024 曾经``沧海 0.001464