首页 > 数据库 >0.mysql基础sql

0.mysql基础sql

时间：2023-07-15 22:44:10浏览次数：38  
标签：表中 -- sql 基础 查询 mysql column1 SELECT column2

常用的数据库sql语句，数据库相关的技术和理论是成体系的，从基础使用到数据库原理，到性能优化，海量数据处理，但不同的技术角色所需掌握的深度是不同的：
如果你是一位普通系统软件开发人员掌握基本sql操作、数据库索引、存储结构等也够用
如果你是一位高并发系统的架构设计与开发者，那海量数据的数据库处理、锁机制、数据库性能优化也得深入理解
如果你是数据库管理员，那处理以上的技术知识，数据库原理以及其中的算法理论自然必不可少

一、建表

直接以案例说明，当前创建一张student_info的表

1.1 表的创建

CREATE TABLE `student_info` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `stu_no` bigint(20) NOT NULL COMMENT '学生编号',
  `sex` tinyint(2) DEFAULT NULL COMMENT '性别，1-男，2-女',
  `enrollment_date` varchar(10) NOT NULL COMMENT '入学日期,YYYYMMDD',
  `address` varchar(500) DEFAULT NULL COMMENT '住址',
  `class` varchar(20) DEFAULT NULL COMMENT '所在班级',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_deleted` tinyint(2) NOT NULL DEFAULT '0' COMMENT '软删除：0-未删/1-已删',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_stu_no` (`stu_no`),
  KEY `idx_enro_date` (`enrollment_date`),
  KEY `idx_update_time` (`update_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='学生信息表'

相比于一般教程里的入门建表语句，这个表稍微复杂了点，当然实际开发中几十个字段的表比比皆是

以下是对建表语句中的相关语句进行说明

1.2 mysql中的字段类型

  1. 整数类型：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT。分别用于存储不同范围的整数值。

  2. 浮点数类型：FLOAT、DOUBLE、DECIMAL。分别用于存储单精度浮点数、双精度浮点数和高精度小数。

  3. 字符串类型：CHAR、VARCHAR、TEXT、BLOB。分别用于存储定长字符串、变长字符串、长文本和二进制数据。

  4. 日期时间类型：DATE、TIME、DATETIME、TIMESTAMP。分别用于存储日期、时间、日期时间和时间戳。

  5. 枚举类型：ENUM。用于存储预定义的枚举值。

  6. 集合类型：SET。用于存储预定义的集合值。

  7. 布尔类型：BOOLEAN、BOOL、TINYINT(1)。用于存储布尔值。

更详细的类型内容就不展开了，需要注意的是，不同的字段类型有不同的存储空间和取值范围，应根据实际需求选择合适的字段类型。
NOT NULL、DEFAULT用来约束该字段的取值内容。

1.3 索引、主键与唯一键

UNIQUE KEY 和 PRIMARY KEY 都是 MySQL 中用于定义表的键的关键字，它们的区别如下：

  • 主键（PRIMARY KEY）是一种特殊的唯一键，用于唯一标识表中的每一行数据。每个表只能有一个主键，主键列的值不能为空（NOT NULL）。主键可以由一个或多个列组成，但是一般情况下只使用一个列作为主键。主键可以用于建立表之间的关系，如外键约束等。

  • 唯一键（UNIQUE KEY）用于保证表中某一列或多列的值唯一。每个表可以有多个唯一键，唯一键列的值可以为空（NULL）。唯一键可以由一个或多个列组成，唯一键列的值不能重复，但是可以有多个 NULL 值。

  • 普通索引（KEY）：用于加速查询操作，可以由一个或多个列组成。普通索引可以在查询中使用，但不强制要求列的值唯一。

主键是特殊的唯一键，唯一键是特殊的索引。
主键和唯一键的区别在于，主键是一种特殊的唯一键，用于唯一标识表中的每一行数据，而唯一键只是保证表中某一列或多列的值唯一。
主键列的值不能为空，而唯一键列的值可以为空。主键可以用于建立表之间的关系，而唯一键不能。

综上所述，主键和唯一键都可以用于保证表中某一列或多列的值唯一，但是主键是一种特殊的唯一键，用于唯一标识表中的每一行数据，而唯一键只是保证表中某一列或多列的值唯一。

1.4 数据库引擎与字符集

  • ENGINE=InnoDB：这里在建表时指定InnoDB引擎，这也是当前Mysql默认的存储引擎

  • DEFAULT CHARSET=utf8mb4
    MySQL数据库中的一种字符集设置，用于指定数据库、表或列的字符集，utf8mb4是一种字符集，支持Unicode编码，可以存储包括Emoji表情在内的各种字符。
    在MySQL数据库中，字符集是指用于存储和处理文本数据的编码方式。如果不指定字符集，MySQL默认使用Latin1字符集，只能存储ASCII码范围内的字符，无法存储中文、日文、韩文等非ASCII字符。
    因此，为了支持更广泛的字符集和多语言环境，需要在创建数据库、表或列时指定字符集。在MySQL 5.5.3及以上版本中，推荐使用utf8mb4字符集，以支持更广泛的字符范围。

  • COLLATE=utf8mb4_bin
    MySQL数据库中的一种排序规则设置，用于指定数据库、表或列的排序规则。其中，utf8mb4_bin是一种排序规则，它区分大小写，按照二进制编码进行排序。
    在MySQL数据库中，排序规则是指用于比较和排序文本数据的规则。如果不指定排序规则，MySQL默认使用utf8mb4_general_ci排序规则，它不区分大小写，忽略了一些特殊字符的差异，可能会导致一些不符合预期的排序结果。
    因此，为了避免这种情况，需要在创建数据库、表或列时指定排序规则。在MySQL 5.5.3及以上版本中，推荐使用utf8mb4_bin排序规则，以保证排序的准确性和一致性。

1.5 建表的建议

  1. 指定字符集和排序规则：推荐使用utf8mb4字符集和utf8mb4_bin排序规则。

  2. 选择合适的数据类型：数据的实际需求选择合适的数据类型，避免浪费存储空间和影响查询效率。例如，对于较小的整数可以使用TINYINT或SMALLINT类型，对于较大的整数可以使用BIGINT类型。

  3. 添加索引：在表中添加索引可以提高查询效率，但也会增加写入数据的时间和空间开销。应该根据实际需求选择合适的索引类型和字段，避免过多或不必要的索引。

  4. 设计合理的表结构：在设计表结构时，应该遵循范式设计原则，尽量避免冗余和重复的数据，以提高数据的存储效率和查询效率。

二、表操作

对于表中记录的操作当然是增删改查，当然查询是贯穿在所有指令中的，也就删表删库操作不需要

1. INSERT

--插入包含字符串类型的值，需要使用单引号将其括起来
--插入日期类型的值，需要使用日期函数将其转换为日期类型
--插入的数据中包含自增长列，可以使用 NULL 值或者不指定该列的值，MySQL 会自动为该列生成一个唯一的值
INSERT INTO table_name (column1, column1, column1, ...) VALUES (value1, value1, value1, ...);

2. UPDATE

--UPDATE 语句可能会影响到多行数据，需要确保更新的条件正确，否则可能会导致更新错误的数据
UPDATE table_name SET column1=value1, column2=value2, ... WHERE condition;

3. DELETE

--可能会影响到多行数据，需要确保删除的条件正确，否则可能会导致删除错误的数据
DELETE FROM table_name WHERE condition;

4. DROP
DROP 语句用于删除数据库、表或索引，而DELETE是删除数据表中记录

--删除数据库
DROP DATABASE 数据库名;

--删除表
DROP TABLE 表名;

--删除索引
DROP INDEX 索引名 ON 表名;

5. TRUNCATE

--用于删除表中的所有数据，但保留表的结构，其本质是先删除表，再执行当前的建表语句
--TRUNCATE 比 DELETE 语句更快，因为它不会通过binlog日志记录删除的每一行，而是直接删除整个表
--因此 TRUNCATE 语句无法回滚，因此需要确保清空数据的操作是正确的
TRUNCATE TABLE 表名;

6. ALTER
ALTER 语句用于修改表的结构，包括添加、修改和删除列，修改列的数据类型、长度、默认值等。

-- 表中新增列
ALTER TABLE `表名` ADD COLUMN `列名` varchar(256) NOT NULL COMMENT 'xxxx';

-- 表中删除列
ALTER TABLE `表名` DROP COLUMN `列名`;

-- 表中字段相关属性修改
ALTER TABLE `表名` MODIFY `列名` varchar(256) NOT NULL COMMENT 'xxxx';

-- 表中列名修改
ALTER TABLE `表名` CHANGE `旧列名` `新列名` varchar(256) NOT NULL COMMENT 'xxxx';

索引相关：

-- 表中新增索引
ALTER TABLE `表名` ADD INDEX `索引名` (`列名1`,`列名2`)

-- 表中新增唯一索引
ALTER TABLE `表名` ADD UNIQUE KEY `索引名` (`列名1`,`列名2`)

-- 删除已有索引
ALTER TABLE `表名` DROP INDEX `索引名`;

三、单表查询

3.1 常规查询

--多列查询
SELECT column1, column2, ... FROM table_name WHERE condition;

--空值查询
--不能使用=运算符判断一个值是否等于NULL，因为在MySQL中，NULL不是一个值，而是一个特殊的标记，表示缺少值，而应该使用IS NULL运算符。
SELECT column1, column2, ... FROM table_name WHERE column_name IS NULL;

--通配符*
--表中所有列查询，除非你确实需要表中的每个列，否则最好别使用*通配符。
--虽然使用通配符可能会使你自己省事，不用明确列出所需列，但检索不需要的列通常会降低检索和应用程序的性能。
SELECT * FROM table_name WHERE condition;

--DISTINCT关键字只能用于查询语句中的第一个SELECT关键字之后，不能用于子查询中
SELECT DISTINCT column1, column2, ... FROM table_name WHERE condition;

--LIMIT关键字用于限制查询结果的行数，可以指定查询结果的起始位置和行数，以下的sql指定返回从查询结果的第10行开始之后的100条记录，即11~110
SELECT column1 FROM table_name WHERE condition LIMIT 10, 100;

--ORDER BY关键字默认按照升序排序，可以使用DESC关键字指定降序排序
--尽量避免使用SELECT *，选择需要的字段进行查询并排序
--使用LIMIT，它必须位于ORDER BY之后
SELECT column1, column2, ... FROM table_name WHERE condition ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

--AND和OR
--在处理OR操作符前，优先处理AND操作符，所以想优先处理OR的话，使用圆括号明确地分组相应的操作符
SELECT column1, column2, ... FROM table_name WHERE (condition1 OR condition2) AND condition3;

3.2 分组查询

MySQL 的分组查询是指对表中的数据按照指定的列进行分组，通常还会对每个分组进行聚合计算，返回每个分组的计算结果。常用的聚合函数包括 COUNT、SUM、AVG、MAX、MIN 等。

--aggregate_function为聚合函数包括 COUNT、SUM、AVG、MAX、MIN 等
--HAVING 子句用于对分组后的结果进行筛选，
SELECT column1, column2, ..., aggregate_function(column)
FROM table_name
WHERE condition
GROUP BY column1, column2, ...
HAVING condition
ORDER BY column1, column2, ... ASC|DESC;

  • WHERE 关键字用于在查询之前对表中的数据进行筛选，它可以用于过滤行，只返回符合条件的行。WHERE 关键字只能用于对列进行筛选，不能用于对聚合结果进行筛选。

  • HAVING 关键字用于在查询之后对分组结果进行筛选，它可以用于过滤组，只返回符合条件的组。HAVING 关键字只能用于对聚合结果进行筛选，不能用于对列进行筛选。

因此，WHERE 关键字用于过滤行，HAVING 关键字用于过滤组，它们的作用范围不同。

查询案例说明：
一个学生表 student_info，包含 id、name、age 和 score 四个字段，我们想要统计每个年龄段的学生人数和平均分数，并只返回平均分数大于 80 分的年龄段

SELECT age, COUNT(*) AS count, AVG(score) AS avg_score
FROM student_info
GROUP BY age
HAVING AVG(score) > 80;

常用聚合函数

下面是常用的聚合函数及其作用：

  • COUNT(column)：统计指定列的行数，如果不指定列，则统计所有行的行数。
  • SUM(column)：计算指定列的总和。
  • AVG(column)：计算指定列的平均值。
  • MAX(column)：计算指定列的最大值。
  • MIN(column)：计算指定列的最小值。

聚合函数只能用于数值类型的列，如果对非数值类型的列使用聚合函数，会返回错误。
聚合函数还可以与 DISTINCT 关键字一起使用，用于统计不重复的值。例如，COUNT(DISTINCT column) 表示统计指定列中不重复的值的个数。

四、多表联合查询

  • 多表联合的查询其sql远比单表复杂的多，毕竟从一维上升到了多维，但如果梳理清楚各表之间的联系，再将相关sql进行分解，其本质也就是单表查询之间通过表间字段的关联进行联合查询，当表的数量、相关字段和关联关系不断增加时，联合查询的复杂度也会同步增加。
  • 单表查询中的所有操作都可在多表查询中使用

4.1 子查询

  • 子查询本质也就是将一个查询结果作为另一个查询的条件，表现在sql语句中就是一个SQL语句中嵌套另一个SQL语句。
  • 子查询可以嵌套多层，可以用于SELECT、UPDATE、DELETE、INSERT等语句中。
  • 子查询的语句可以在sql语句的多处地方使用，如SELECT、FROM、WHERE、HAVING中

1. 在SELECT语句中使用子查询

--SELECT语句中使用子查询时，子查询通常是作为一个列的值返回，必须确保它只返回一行结果，返回多行会报错
--所以这的column2仅仅是一个结果值附加在对table1查询到的column1之后
SELECT column1, (SELECT column2 FROM table2 WHERE table1.id = table2.id) AS column2 FROM table1;

2. 在FROM语句中使用子查询

--将子查询的结果作为一个虚拟表（或称为派生表）来使用，这也是平时常用的子查询
--在FROM中子查询时，必须给虚拟表指定一个别名，否则mysql会报错
SELECT column1, column2
FROM (SELECT column1, column2 FROM table1 WHERE column1 >= 10) AS t1;

3. 在WHERE语句中使用子查询

--根据子查询的结果来过滤数据，这里的子查询结果是你用来筛选数据的条件
--IN作为筛选条件时子查询可以返回多个结果，但>、<、=这些条件的话要注意子查询只能返回一个结果
SELECT column1, column2
FROM table1
WHERE column1 IN (SELECT column1 FROM table2 WHERE column2 = 'value');

4. 在HAVING语句中使用子查询

--和在WHERE中使用类似
SELECT column1, COUNT(column2) AS count
FROM table1
GROUP BY column1
HAVING COUNT(column2) > (SELECT AVG(count) FROM (SELECT COUNT(column2) AS count FROM table1 GROUP BY column1) AS t1);

5. 在INSERT语句中使用子查询

--子查询的结果集可以看成是一张虚拟表，自然也可以将其中的记录插入其他表，当然其字段类型肯定要符合插入表的要求
INSERT INTO table1 (column1, column2)
SELECT column1, column2 FROM table2 WHERE column1 >= 10;

4.2 联合查询

  • MySQL的联合查询（UNION）是一种将多个SELECT语句的结果集合并成一个结果集的方法。
  • 联合查询可以用于将多个表中的数据合并在一起，或者将同一表中的不同条件的数据合并在一起。
  • 联合查询和子查询看上去都是对多个表的查询，但其实它们有各自适用的查询领域，如果需要将多个表或多个条件的查询结果合并在一起，可以使用联合查询；如果需要在一个查询中嵌套另一个查询来过滤、排序或聚合数据，可以使用子查询。
--将两个或多个 SELECT 语句的结果集合并成一个结果集，并去除重复的行
--两个 SELECT 语句的列数和数据类型必须相同
--UNION 操作符会对结果集进行排序和去重，因此可能会影响查询的性能
SELECT column1 FROM table1
UNION
SELECT column2 FROM table2

4.3 连接查询

  • 其语法的关键字是JOIN用于指定要连接的表，包括INNER JOIN、LEFT JOIN和RIGHT JOIN等，ON关键字用于指定连接条件

1. INNER JOIN
内连接，或等值连接：获取两个表中字段匹配关系的记录，是最常用的JOIN类型

--使用时也可以省略 INNER 使用 JOIN，效果一样
--它返回两个表中匹配的行，如果两个表中没有匹配的行，则不返回任何结果
--使用时，需要确保连接条件的正确性和唯一性，否则可能会导致查询结果不正确
SELECT column1 FROM table1 INNER JOIN table2 ON table1.column1 = table2.column2;

表现为两表的交集：

2. LEFT JOIN
左连接：获取左表所有记录，即使右表没有对应匹配的记录

SELECT column1 FROM table1 LEFT JOIN table2 ON table1.column1 = table2.column2;

3. RIGHT JOIN
右连接： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

SELECT column1 FROM table1 RIGHT JOIN table2 ON table1.column1 = table2.column2;

标签：表中,--,sql,基础,查询,mysql,column1,SELECT,column2
From： https://www.cnblogs.com/wzq-blogs/p/17542466.html

相关文章

  • 腾讯技术官编写的594页MySQL优化手册，竟意外冲上GitHub调优热榜
    前言：当下互联网时代，MySQL受热捧的程度不容置疑，位列全球最流行的开源数据库也当之无愧，它广泛应用的因由不仅是产品本身的优势，更重要的是有无数社区技术爱好者为其默默耕耘。近几年MySQL发展迅速，各分支在性能与架构方面优化都有所突破与发展;与此同时，互联网应用业务也更加复杂，更具有......
  • flex and bison usage in PostgreSQL
    flex/bisonusageinpgsqlInregularbisonusage,wecallyyparse()togetanAST.So,IsearchedforyyparseinPostgreSQLsourcecode,whicheventuallyledmetothebase_yyparse()function.Whatisthat?Ingram.y:%name-prefix="base_yy"%par......
  • oracle pl/sql与lightdb plorasql面向对象支持
    虽说现在新系统pl/sql已经很少用了，但是在信创迁移过程中，仍然有很多oraclepl/sql过程、函数、包需要能够无缝的移植到信创数据库，比如在很多风控系统移植lightdb的过程中，就遇到了很多，甚至还有pro*c。所以才有了本文。--最常用，用于集合处理DECLARECURSORallrows_curISSELECT*......
  • 五、函数的基础--2.函数的参数与返回值
    C语言函数的参数和返回值是函数定义时用来传递数据和返回结果的机制。函数参数：1.参数是函数定义中用于接收外部数据的变量。函数可以有零个或多个参数，每个参数包括类型和名称。2.参数允许将数据从函数的调用者传递到函数内部，函数在执行时可以使用这些参数来完成特定任务。3.参数......
  • dotnet 连接sqlite数据库 orm EntityFrameworkCore
    dotnet_learn/appsettings.json{"Logging":{"LogLevel":{"Default":"Information","Microsoft.AspNetCore":"Warning"}},"AllowedHosts":"*"}d......
  • mysql-8.0.23-winx64_解压版本的配置和使用
    1、下载我给大家提供的压缩包链接：https://pan.baidu.com/s/1m0WQfU3SoKDwgIwcxeSujw提取码：xv9u2、文件解压在你的英文目录下，最好和我保持一致哈 3、浏览下原始文件列表 4、在mysql的解压目录下创建my.ini文件且编辑，我已经给大家写好了，只要以上路径和我保持一致，不用再修改......
  • BFS和DFS基础
    BFS和DFS基础搜索简介搜索是"暴力法"算法的具体实现，是一种吧所有可能的情况都罗列出来，然后逐一检查，从中找到答案的方法。一般步骤找到所有可能的数据，并且永数据结构表示和存储。优化：尽量多的排除不符合条件的数据，以减少搜索空间。用某个算法快速检索这些数据。搜索算法的......
  • Python练手小项目——简易版基础SQL模板代码生成器
    1、效果图2、代码源码-ui.py：fromtkinterimport*fromtkinterimportscrolledtext,messageboxfromtkinter.ttkimportComboboximportpymysqldefinit():#创建窗口：实例化一个窗口对象window=Tk()#窗口大小window.geometry("900x550")......
  • jvm基础知识
    1.jvm基础知识说明：jvm除了是压测过程中重点关注的内容，也是面试的重点1.1基础回顾java特点：跨平台内存泄漏是过程，内存溢出是结果jdk，jre，jvm的关系jdk<jre<jvm数据类型及存储基本数据类型：存储在栈内存引用数据类型：值存在堆内存，栈里存堆内存中存放的值的首地址堆栈：堆：线程......
  • lightdb plpgsql函数in/inout参数示例
    lightdb支持oraclepl/sql以及开源postgresqlPlpgsql两种过程性（增强）语言。本文讲解pgpgsql函数出参的典型用法及限制。注：匿名块实际上走的是plorasql，而非plpgsql，即使调用的是plpgsql过程。本文我们假设对于函数、存储过程的调用是进行逻辑处理，而不是返回结果集或游......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99