SQL入门大全，个人总结！

最新推荐文章于 2024-09-19 22:55:57 发布
最新推荐文章于 2024-09-19 22:55:57 发布
阅读量1k 收藏 9
点赞数 2
分类专栏： # SQL 文章标签： mysql sql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44425934/article/details/108712842
版权

SQL(Structured Query Language)

由于最近心血来潮，就把SQL重新过了一遍，顺便总结了一份小宝典，应该算是挺全面的哈~~

导语：SQL之路–SQL大全

作者：变优秀的小白

QQ学习交流群(new): 811792998

爱好：美式一定加冰！

话不多说，展翅(shi) ！

页面右侧有文章目录索引！！

数据模型

数据库按照数据结构来组织、存储和管理数据

  • 层次模型
  • 网状模型
  • 关系模型

数据类型

名称类型说明
INT整型4字节
BIGINT长整型8字节
REAL浮点型4字节
DOUBLE浮点型8字节
DECIMAL高精度小数DECIMAL(20,10)表示一共20位，其中小数10位，通常用于财务计算
CHAR定长字符串存储指定长度的字符串，例如，CHAR(100)总是存储100个字符的字符串
VARCHAR变长字符串存储可变长度的字符串
BOOLEAN布尔类型True或者False
DATE日期类型日期
TIME时间类型时间
DATETIME日期和时间类型存储日期+时间

SQL基本能力

  1. DDL：Data Definition Language
    DDL允许用户定义数据，也就是创建表、删除表、修改表结构这些操作。通常，DDL由数据库管理员执行
  2. DML：Data Manipulation Language
    DML为用户提供添加、删除、更新数据的能力，这些是应用程序对数据库的日常操作
  3. DQL：Data Query Language
    DQL允许用户查询数据，这也是通常最频繁的数据库日常操作

运行SQL

若是windows版本，百度上有很多安装教程，

# 若是使用brew安装的(系统为macOS)
# 需要通过brew启动mysql
$ brew services start mysql
$ mysql -u root -p

关系模型

  • 关系数据库是建立在关系模型上的。而关系模型本质上就是若干个存储数据的二维表，可以把它们看作很多Excel表
  • 表的每一行称为记录（Record），记录是一个逻辑意义上的数据
  • 表的每一列称为字段（Column），同一个表的每一行记录都拥有相同的若干字段

主键

  • 最关键的一点：记录一旦插入到表中，主键最好不要再修改，因为主键是用来唯一定位记录的，修改了主键，会造成一系列的影响
常见的可作为id字段的类型
  1. 自增整数类型
    • BIGINT NOT NULL AUTO_INCREMENT(满足大部分场景)
    • INT：一张表的记录数超过2147483647（约21亿）时，会达到上限而出错
    • BIGINT：922亿
  2. 全局唯一GUID类型
联合主键

没有必要的情况下，我们尽量不使用联合主键，因为它给关系表带来了复杂度的上升

  • 定义：通过多个字段唯一标识记录，即两个或更多的字段都设置为主键

外键

  • 外键是通过定义外键约束实现的
  • 外键约束会降低数据库的性能，一般不设置

增加一个外键约束

ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id);
  • CONSTRAINT: 外键约束的名称可自定义
  • FOREIGN KEY: class_id作为外键
  • REFERENCES 关联classes表的id列

删除一个外键约束

ALTER TABLE students
DROP FOREIGN KEY fk_class_id;

几种关系

均为表之间的对应关系

  • 一对一(q:问题，a:回答，ex: 例子)
    • q: 既然是一对一，为啥不把另一个表(只有1、2列)的数据放入表的一个列中
    • a: 如果业务运行是可以的，但是一个大表拆成两个一对一的表，目的是把经常读取和不经常读取的字段分开，以获得更高的性能
    • ex: 把一个大的用户表分拆为用户基本信息表user_info和用户详细信息表user_profiles，大部分时候，只需要查询user_info表，并不需要查询user_profiles表，这样就提高了查询速度
  • 一对多
  • 多对多
    • ex: 两个一对多

索引

概念

关系数据库中对某一列或多个列的值进行预排序的数据结构

目的

可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度

使用效率
  • 取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高
  • 若存在大量数据具有相同值，索引就没有意义
  • 对于主键，关系数据库会自动对其创建主键索引。使用主键索引的效率是最高的，因为主键会保证绝对唯一
优缺点
  • 优点：提高查询效率
  • 缺点：插入、更新和删除记录时，需要同时修改索引，因此，索引越多，插入、更新和删除记录的速度就越慢
例子ex
idclass_idnamegenderscore
11小明M90
21小红F95
31小军M88

场景：要经常根据score列进行查询
解决：对score列创建索引

ALTER TABLE students
ADD INDEX idx_score (score);

则创建了一个名称未idx_score的索引

场景：索引名称是任意的，索引如果有多列
解决：括号内增加列名称

ALTER TABLE students
ADD INDEX idx_name_score (name, score);
唯一索引

  1. 运用场景：

    • 看上去唯一的列，例如身份证号、邮箱地址等，因为他们具有业务含义，因此不宜作为主键
    • 这些列根据业务要求，又具有唯一性约束（即不能出现两条记录存储了同一个身份证号）

  2. 解决：该列添加一个唯一索引

  3. ex例子：

添加一个唯一索引

ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);

添加一个唯一约束而不创建唯一索引(具有唯一性保证)

ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name);

查询数据

基本查询

ex. *

SELECT *
FROM students;
条件查询

ex. where & and & or & ()

SELECT *
FROM students
WHERE (score < 80 OR score > 90)
AND gender = 'M';
投影查询

ex. 列1、列2…

SELECT id, score, name
FROM students;
排序

ex. ORDER BY & DESC & ASC

SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;
分页查询

ex. LIMIT M OFFSET N

  • LIMIT M OFFSET N 可简写为 LIMIT N, M
  • LIMIT总是设定为pageSize
  • OFFSET = pageSize * (pageIndex - 1)
-- 查询第一页
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;
-- 查询第二页，需要 "跳过" 前三条记录
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 3;
-- 查询第四页，类似
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 9;
-- 若查询页没有数据，不报错，得到空结果集
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 20;
聚合查询

注意点：如果聚合查询的WHERE条件没有匹配到任何行，COUNT()会返回0，而SUM()、AVG()、MAX()和MIN()会返回NULL

  • ex. COUNT()统计记录数量
-- 使用聚合查询
SELECT COUNT(*) FROM students;
-- 使用聚合查询并设置结果集的列名为num
SELECT COUNT(*) num FROM students;
-- 使用聚合查询并设置WHERE条件
SELECT COUNT(*) boys FROM students WHERE gender = 'M';
-- 分组聚合
SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;
-- 聚合查询的列中，只能放入分组的列
SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;
  • ex. AVG()平均值，该列必须为数值类型
  • SUM()合计值，类似
  • MAX()最大值，类似
  • MIN()最小值，类似
-- 使用聚合查询计算男生平均成绩
-- average为自定义名称
SELECT AVG(score) average FROM students WHERE gender = 'M';
-- 每页3条记录，如何通过聚合查询获得总页数
SELECT CEILING(COUNT(*)/3) FROM student;
多表查询
  • 语法：SELECT * FROM <表1> <表2>
  • 笛卡尔乘积，数据量巨大M*N
-- 两个表的乘积
SELECT * FROM students, classes;
-- 可设置列的别名区分
SELECT
    students.id sid,
    students.name,
    students.gender,
    students.score,
    classes.id cid,
    classes.name cname
FROM students, classes;
-- sql可给表设置别名，相对于上面会简洁些
SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c;
-- 加上where条件判断
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;
连接查询
INNER JOIN 内连接
  • INNER JOIN
    1. 确定主表，FROM 表1
    2. 确定需要连接的表，INNER JOIN 表2
    3. 确定连接条件，ON 条件
    4. 可选：WHERE Or ORDER BY等
-- 内连接，运用别名，id对应取出
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;
OUTER JOIN 外连接
LEFT OUTER JOIN 左外连接
  • 返回左表都存在的行，若仅左表存在会返回NULL
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
LEFT OUTER JOIN classes c
ON s.class_id = c.id;
RIGHT OUTER JOIN 右外连接
  • 返回右表都存在的行，若仅右表存在会返回NULL
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
RIGHT OUTER JOIN classes c
ON s.class_id = c.id;
FULL OUTER JOIN
  • 所有记录全部选择出来，把对方不存在的列填充为NULL：
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
FULL OUTER JOIN classes c
ON s.class_id = c.id;

修改数据

INSERT 插入数据
  • 语法：INSERT INTO <表名> (字段1, 字段2, ...) VALUES (值1, 值2, ...);
  • 插入数据的字段顺序不需要一致，但字段和值要对应
  • 自增主键和默认值可不填
INSERT INTO students (class_id, name, gender, score) VALUES
  (1, '张三', 'M', 87),
  (2, '李四', 'M', 81);
-- 查询更新结果
SELECT * FROM students;
UPDATE 更新数据
  • 语法：UPDATE <表名> SET 字段1=值1, 字段2=值2, ... WHERE ...;
  • 需十分注意使用！
-- 更新多条记录
UPDATE students SET name='小牛', score=77 WHERE id>=5 AND id<=7;
-- 使用表达式，将所有80下同学成绩加10分
UPDATE students SET score=score+10 WHERE score<80;
-- 查询并观察结果:
SELECT * FROM students;
DELETE
  • 语法：DELETE FROM <表名> WHERE …;
  • 与UPDATE语法类似，但需十分注意使用！
-- 删除id=1的记录
DELETE FROM students WHERE id=1;
-- 删除id=5,6,7的记录
DELETE FROM students WHERE id>=5 AND id<=7;
-- 查询并观察结果:
SELECT * FROM students;

mysql

管理mysql

  • 几个系统库
    • imformation_schema
    • mysql
    • performance_shema
    • sys
数据库
-- 创建一个新的DB
mysql> CREATE DATABASE test
Query OK, 1 row affected (0.01 sec)
-- 删除一个DB
mysql> DROP DATABASE test
Query OK, 0 rows affected (0.01 sec)
-- 切换DB
mysql> USE test;
Database changed
表
-- 查看DB的所有表
mysql> SHOW TABLES;
-- 查看一个表的结构
mysql> DESC students;
-- 查看创建表的SQL语句
mysql> SHOW CREATE TABLE students;
-- 删除表
mysql> DROP TABLE students;
Query OK, 0 rows affected (0.01 sec)
-- 修改表，如新增一列
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
Bye

实用SQL

插入或替换

-- 若记录已经存在，删除原记录，再插入新记录
REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

插入或更新

-- 语法：INSERT INTO ... ON DUPLICATE KEY UPDATE ...
-- 如果记录已经存在，就更新该记录
INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;

插入或忽略

-- 如果记录已经存在，就啥事也不干直接忽略
INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

快速复制

-- 对class_id=1的记录进行快照，并存储为新表students_of_class1:
CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;

强制索引

-- FORCE INDEX
SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;

事务

某些业务要求必须完成一系列的操作，而不是只执行一部分

可见，数据库事务有ACID 4个特性

  • A：Atomic，原子性，将所有SQL作为原子工作单元执行，要么全部执行，要么全部不执行
  • C：Consistent，一致性，事务完成后，所有数据的状态都是一致的，即A账户只要减去了100，B账户则必定加上了100
  • I：Isolation，隔离性，如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离
  • D：Duration，持久性，即事务完成后，对数据库数据的修改被持久化存储

隐式事务

解释：单条SQL语句，数据库系统自动将其作为一个事务执行

显式事务

解释：多条SQL语句作为一个事务执行，使用BEGIN开启一个事务，使用COMMIT提交一个事务

-- 显式事务例子
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- 若希望它主动失败，ROLLBACK回滚事务就很棒
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK;

隔离级别

目的：对于两个并发执行的事务，如果涉及到操作同一条记录的时候，可能会发生问题

Read Uncommitted(最低)

解释：是隔离级别最低的一种事务级别。在这种隔离级别下，一个事务会读到另一个事务更新后但未提交的数据，如果另一个事务回滚，那么当前事务读到的数据就是脏数据，这就是脏读（Dirty Read）

Read Committed(较低)

解释：不可重复读是指，在一个事务内，多次读同一数据，在这个事务还没有结束时，如果另一个事务恰好修改了这个数据，那么，在第一个事务中，两次读取的数据就可能不一致

Repeatable Read(较高)

  • mysql中InnoDB模式级别

解释：一个事务可能会遇到幻读（Phantom Read）的问题。

幻读是指，在一个事务中，第一次查询某条记录，发现没有，但是，当试图更新这条不存在的记录时，竟然能成功，并且，再次读取同一条记录，它就神奇地出现了。

Serializable(最高)

解释：在Serializable隔离级别下，所有事务按照次序依次执行，因此，脏读、不可重复读、幻读都不会出现。

  • 缺点：由于事务是串行执行，所以效率会大大下降，应用程序的性能会急剧降低
结束语：大家如果遇到什么疑问或者建议的地方，可直接留言评论！本人会一一回复！
如果小白的博客有建议或批评的，下方留言即可！如果觉得小白还不错的，留下你的点赞👍，关注❤️和收藏🤘哦！谢谢谢！
变优秀的小白
关注
专栏目录
SQL快速入门（基本用法）
Strengthen123的博客
07-29 1万+
基本语句用法
sql2000_个人版
11-15
总结来说，SQL Server 2000个人版是一款面向个人用户的数据库解决方案，它提供了基础但全面的数据库管理功能，适用于学习、个人项目和小型网站的数据库需求。在使用过程中，用户需要注意其功能限制，并根据自身需求...
SQL语句大全解析及示例（个人总结-入门级）
08-02
根据个人经验总结的常用SQL语句，对关键词进行解释，对特殊语句进行详细说明，并分别给出示例，共初学者参考。
转载：SQL入门教程
心成_111的博客
12-22 979
转载自：https://code.ziqiangxuetang.com/sql/sql-syntax.html 感谢分享
全新SQL Server教程
chinaherolts2008的博客
12-04 2393
SQL基础教程 一、SQL简介 1：什么是SQL？ A:SQL指结构化查询语句 B:SQL使我们有能力访问数据库 C:SQL是一种ANSI(美国国家标准化组织)的标准计算机语言 2：SQL能做什么？ *面向数据库执行查询 *从数据库中取出数据 *向数据库插入新的记录 *更新数据库中数据 *从数据库删除记录 *创建数据库 *创建表 *创建存储过程 *创建视图 *设置表、存储过程和视图的权限 3：RDBMS RDBMS是指关系型数据库管理系统 RDBMS是SQL的基础，
SQL学习笔记1
weixin_34293141的博客
07-23 3397
* 以下内容是我在学习SQL（http://www.w3school.com.cn/sql/index.asp）的时候的学习笔记 * 学习时使用的数据库软件是MySQL数据库可视化工具SQLyogEnt * 如果大家有发现什么不对的地方请告诉我。谢啦!!☆⌒(*＾-゜)v * 在这里需要注意的是，和SqlServer相比MySQL不支持事务处理，没有视图，没有存储过程和触发器，没有数...
基础篇：数据库 SQL 入门教程
热门推荐
LuciferLiu_DBA
09-12 35万+
超全SQL教程，收藏就完事了！
史上最强最常用SQL语句大全【超详细注释】
沉淀、分享、成长，让自己和他人都能有所收获！
06-09 5万+
作为数据库操作的核心工具，SQL（结构化查询语言）在数据管理和查询中扮演着至关重要的角色。本文将全面介绍最常用的SQL语句，覆盖数据定义语言（DDL）、数据操作语言（DML）、数据查询语言（DQL）和数据控制语言（DCL）。通过丰富的代码示例和详细注释，帮助您深入理解和灵活运用SQL。创建数据库-- 创建一个名为my_database的数据库-- 创建一个名为test_db的数据库，带有字符集和排序规则删除数据库-- 删除名为my_database的数据库-- 删除名为test_db的数据库。
SQL Server 入门学习总结---基础篇
m0_59679869的博客
05-24 1131
SQL Server 是微软公司推出的关系型数据库管理系统，一起学习吧！
SQL基础语句总结
DDDBOOM的博客
04-13 2万+
主要包含：数据相关知识，针对库的指令，针对表的指令，以及SQL函数等
SQL入门全攻略（一）
qq_64192931的博客
05-31 814
本文主要介绍了sql语言的基本概念和一些基础的用法，包括对数据库和对表的操作，这里还需要说的是，创建、删除数据库和创建、删除表等这些基本操作也可以用图形用户界面工具（比如navicat）来完成，具体使用哪种方式看个人的习惯。希望本文能为你提供一个良好的起点，让你在学习大数据技术的过程中畅游无阻！
课题分享：宿舍管理系统小程序，基于微信小程序+SSM+mysql
zhongshu_luo的博客
09-19 913
互联网概念的产生到如今的蓬勃发展，用了短短的几十年时间就风靡全球，使得全球各个行业都进行了互联网的改造升级，标志着互联网浪潮的来临。在这个新的时代，各行各业都充分考虑互联网是否能与本行业进行结合，是否能解决本行业或者本行业中某些方面的自古以来的痛点。长期以来，数据内容，数据传播，数据储存等都是一门门专业的内容，因为需要记住的内容很多，造成古人的言简意赅，如今互联网的到来让数据更加的清晰好认不复杂，一目了然不出错，毕竟在整个历史上数据的丢失，传承的断代，都是因为数据不能好好的保存，不能好好的流传。
MySQL练手题--体育馆的人流量(困难)
weixin_66919047的博客
09-15 550
此题稍微微有一点难度，如果看不明白可以对题目进行分解分析，分析出一步之后再接下一步来查询，题目已经要求人数大于等于100且id连续三行，就可以先筛选出大于等于100 的id 然后排序求差 差值相等的均为id连续的 ，最后筛选差值总数大于等于3 的，便出来了最后结果；
Mysql中DML和DQL
最新发布
2302_81019764的博客
09-19 147
DML操控的是表中的数据，DQL操控的则是表的查询。
基于SpringBoot+Vue+MySQL的网上甜品蛋糕售卖店管理系统
程序员大金的博客
09-12 873
基于SpringBoot+Vue+MySQL的网上甜品蛋糕售卖店管理系统，附源码。
基于SpringBoot+Vue+MySQL的招聘管理系统
程序员大金的博客
09-13 1107
基于SpringBoot+Vue+MySQL的招聘管理系统，附源码。
梧桐数据库（WuTongDB）：MySQL 优化器简介
lunan的博客
09-19 584
MySQL 优化器是一个复杂的系统，它通过生成多个查询计划并基于代价模型选择最优计划来提升查询效率。优化器依赖多种技术，包括索引优化、连接重排序、子查询优化等，来尽可能减少查询的代价。在 MySQL 的最新版本中，优化器功能得到了进一步增强，例如引入了哈希连接、并行查询等功能，极大提升了查询性能。梧桐数据库（WuTongDB）是基于 Apache HAWQ 打造的一款分布式 OLAP 数据库。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值