【MySQL学习】：关系数据库标准语言SQL
📃个人主页：island1314
🔥个人专栏：MySQL学习
⛺️ 欢迎关注：👍点赞 👂🏽留言 😍收藏 💞 💞 💞
引言
下面的操作都是在windows 的操作，此时MySQL在Windows下不区分大小写，但在Linux下默认是区分大小写的。
SQL通用语法：
- SQL 语句可以单行 或 多行书写，以分号结尾
- SQL 语句可以使用 空格 / 缩进来增强语句可读性
- MySQL 数据库的SQL语句不区分大小写，关键字一般建议使用大写
- 注释： 单行注释： -- 注释内容 或 # 注释内容 （MySQL 特有） 多行注释：/* 注释内容 */
SQL分类
| 分类 | 全称 | 说明 |
| DDL | Data Defintion Language | 数据定义语言，用来定义数据库对象（数据库，表，字段） |
| DML | Data Manipulation Language | 数据操纵语言，用来对数据库表中的数据进行增删改 |
| DQL | Data Query Language | 数据定义语言，用来查询数据库表的记录 |
| DCL | Data Control Language | 数据定义语言，用来创建数据库用户，控制数据库的访问权限。 |
1. 数据库操作（DDL）
输入密码 进入MySql数据库
1.1 建立数据库
create database [IF NOT EXIST] 数据库名字 [DEFAULT CHARSET 字符集] [COLIATE 排序规则];
注：分号记得打，单词与单词之间至少有一个空格。
由于数据库创建的时候，要求不能重复，此时则可以在创建的时候，加上上面第一个[ ]内 的命令
1.2 查看数据库
- 查看所有数据库
SHOW DATABASES; -- 注意：databases 后面带 's'
- 查看当前数据库
SELECT DATABASES(数据库名)
1.3 选中数据库
- use 数据库名;
1.4 删除数据库
DROP DATABASE 数据库名;
2. 常用数据类型
2.1 数值类型
分为整型和浮点型
数据类型 大小（字节） 说明 对应java类型 对应C类型 BIT[ (M) ] M指定位数，默认为1 M指定位数，默认为1 常用Boolean对应BIT，此时默认是1位，即只能存0和1 char[] TINYINT 1 Byte signed char SMALLINT 2 Short short int INT 4 Integer int BIGINT 8 Long long long int FLOAT(M, D) 4 单精度，M指定长度，D指定 小数位数。会发生精度丢失
Float float DOUBLE(M, D)
8 Double double DECIMAL(M, D)
M/D最大值+2 双精度，M指定长度，D表示小数点位数。精确数值
BigDecimal char[] NUMERIC(M, D)
M/D最大值+2 和DECIMAL一样 BigDecimal char[] 扩展资料
数值类型可以指定为无符号（unsigned），表示不取负数。
1字节（bytes）= 8bit。
对于整型类型的范围：
- 有符号范围：-2^（类型字节数*8-1）到2^（类型字节数*8-1）-1，如int是4字节，就是-2^31到2^31-1
- 无符号范围：0到2^（类型字节数*8）-1，如int就是2^32-1
尽量不使用unsigned，对于int类型可能存放不下的数据，int unsigned同样可能存放不下，与其
如此，还不如设计时，将int类型提升为bigint类型。
2.2 字符类型
数据类型 大小（字节） 说明 对应java类型 对应C类型 VARCHAR
(SIZE)
0-65,535 可变长度字符串 String char[] TEXT 0-65,535 中等长度文本数据 String char[] MEDIUMTEXT 0-16 777 215 中等长度文本数据 String char[] BLOB 0-65,535 二进制形式的长文本数据 byte[] char[] 注：上面的size表示该类型最多存储几个字符（不是字节）比如：当写了 size = 10 时，不是说当前就立即分配10 个字符的存储空间......，而是先会分配一个比较小的空间，如果不够，再自动扩展，最大的空间不超过10
2.3 时间类型
数据类型 大小（字节） 说明 对应java类型 对应C类型 DATETIME 8 范围从1000到9999年，不会进行时区的检索及转换。 java.util.Date、 java.sql.Timestamp
MYSQL、TIME TIMESTAMP 4 范围从1970到2038年，自动检索当前时区并进行转换。 java.util.Date、 java.sql.Timestamp
MYSQL、TIME 一般常用：
- 整数：int，long
- 小数：double，decimal
- 字符串：varchar
- 时间日期：datetime
3. 数据表操作（DDL）
针对数据表的操作，前提是选中数据库（use 数据库）
3.1 创建表
CREATE TABLE ( [列级完整性约束], [列级完整性约束], ... [, ]);🔥 建表的同时，通常还可以定义与该表有关的完整性约束，这些完整性约束被存入系统的数据字典。
- 当用户操作表中数据时，由关系数据库管理系统自动检查该操作是否违背这些完整性约束。
- 如果完整性约束涉及该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。
注：若确实想让表名/列名 和关键字一样，可以使用 反引号 ` （键盘左上角，ESC下面，tab上面的那个按键）来吧表名 列名引起来，此处列名 在前面，类型 在后面。但是也不是所有语言都类型在前的，有些语言类型可以在后面！！！比如C++，Go，Python。
3.2 查看该数据库中的所有表
- show tables; (查看当前数据库下有那些表，比如我们刚刚使用了use来去使用数据库，则就在当前数据库进行查找)
3.3 查看指定表的结果
desc 表名;
- 查看表中的列
SHOW COLUMNS FROM 表名; (查看表中有那些列，更便捷的方式是：describe 表名;)
案例：
其中：
- field： 表示字段名字
- Type：表示字段类型，注：此处的（11）表示的是显示的宽度，显示这个int类型时，最多占据 11 个字符的宽度（和存储时候的容量无关）
- Null： 表示是否为空，注：此处写成 YES允许这一列为NULL，也可以写成null、Null
- Key：索引类型
- Default：默认值,是NULL可以通过 default 约束来修改
- Extra：扩充（额外信息）
show create table 表名 \G; -- 查看创建表的sql语句， \G 是格式化显示的意思
案例：
3.4 删除表
drop tables [IF EXISTS] [restrict | cascade]
- 若选择RESTRICT，则该表的删除有限制条件，即该表不能被其他表的约束所引用（如CHECK、FOREIGN KEY等约束），不能有视图，不能有触发器（trigger），不能有存储过程或函数等。如果存在这些依赖该表的对象，则此表不能被删除
- 若选择CASCADE，则该表的删除没有限制条件。在删除基本表的同时，相关的依赖对象.都可能被一起删除
- 该语句的默认选项是 RESTRICT
- 删除指定表，并且重新创建该表
TRUNCATE TABLE 列名
3.5 修改表
- 添加字段
alter table [add[column] [完整性约束]] [add ] [drop [column] [cascade | restrict]] [drop constraint [cascade | restrict]] [rename column to ] [alter column type ] 分析： ① ：要修改的基本表 ② add 子句用于增加新列、新的列级完整性约束和新的表级完整性约束。 ③ drop column 子句用于删除表中的列，如果指定了CASCADE 短语，则自动删除引用了该列的其他对象，比如视图；如果指定了RESTRICT 短语，则如果该列被其他对象引用，关系数据库管理系统将拒绝删除该列。 ④ drop constraint 子句用于删除指定的完整性约束。 ⑤ rename column 子句用于修改列名。 ⑥ alter column 子句用于修改列的数据类型。
-
修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
-
修改字段名 和 字段类型
ALTER TABLE 表名 CHANFGE 旧字段名 新字段名 类型(长度)[comment 注释] [约束]
注意：无论 基本表中是否 已有数据，新增加的一列均为空值
4. MySQL 增删改查（DML & DQL）
4.1 数据更新（DML）
4.1.1 插入数据
insert into values(, ...);
- 指定列插入：insert into 表名（列名, 列名...）values(值,值...);
此时我们只插入 name，id 这一列就会被填充为默认值（此处默认值为null）
- 我们还可以一次插入多行记录~~ insert into 表名 values(值, 值...), (值, 值...);
注：上面使用到的 ' '，可以用来表示字符串，或者用 " "，SQL没有 “字符” 这个类型。
- 小知识：（datetime 类型的插入）
若我们插入时间要是当前时刻
insert into student2 select * from student1;
-
插入查询结果
这个用到了下面我们会讲到的查询，查询搭配插入使用，把查询语句的查询结果，作为插入的数值。如将 student1 的查询结果插入到 student2 中。
insert into student2 select * from student1;
注：此处要求查询出来的结果集合， 列数/类型 要和插入的这个表 匹配
4.1.2 修改数据
update set = where ];
案例：
注：修改NULL数据时，如下：
明明我们有英语成绩为NULL的情况，但是仍然没有匹配到，原因是为什么呢？
在修改列允许Null时，可能会涉及到已存在的数据。如果列属性从不允许Null变为允许Null，可能需要处理现有数据以确保数据的一致性和完整性。
以下是常见的处理的方法：
4.1.3 删除数据
delete from 表名 where 条件 / order by /limit;
4.2 数据库约束
4.2.1 约束类型
- NOT NULL - 指示某列不能存储 NULL 值。
- UNIQUE - 保证某列的每行必须有唯一的值。
- DEFAULT - 规定没有给列赋值时的默认值。
- PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
注：一张表里只能有一个 primary key.
- FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
- CHECK - 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略CHECK子句。
4.2.2 NULL 约束
创建表时，可以指定某列不为空：
4.2.3 UNIQUE：唯一约束
指定id列为唯一的、不重复的：
4.2.4 DEFAULT：默认值约束
指定插入数据时，name列为空，默认值为未命名：
4.2.5 PRIMARY KEY：主键约束
- 主键，是表中一列或者多列的组合
- 主键约束（ Primary Key Constraint ）要求主键列的数据唯一，并且不允许为空。
- 主键能够唯一的标识表中的每一条记录，可以结合外键，来定义与不同数据表之间的关系。
怎么理解主键呢？我们知道数据库中的表可以存放很多数据，如果把表中的每一条数据比作一个人的话，那么表的主键就可以看做是人的身份证。
有两种方式可以添加主键约束：
- 在定义列的同时指定主键
- 在定义完字段之后，再指定主键
注：一张表只能有且仅有一个主键
小知识：主键自动生成索引
4.2.6 FOREIGN KEY：外键约束
外部关键字
在以后，我们统一将外部关键字叫做外键，外键就是另一张表中的主键。问：外键有啥用啊？
答：外键的主要作用就是保持数据的一致性，完整性。
如图有两张表，classId 是 T_Student 的外键，同时是 T_class 表的主键。如果我们要删除 T_class 表中 classId 为 1 的字段，程序是会报错的。因为 T_Student 表中有数据和 classId 为 1 的字段关联了，是不能删除的，这样子就保证了数据的一致性和完整性。
问：那怎么才能删除呢？
答：需要先删除 T_Student 表中 classId 为 1 的两个字段。
外键用于关联其他表的主键或唯一键
案例：
对学生表进行数据插入：
注：当我们想删除父表时，则需要先删除 子表，再删除 父表。
- 指定外界约束时，要求父表这一列，得是主键或者 unique，范例：
4.3 查询(DQL)
- SELECT 字段列表
- FROM 表名列表
- WHERE 条件列表
- GROUP BY 分组字段列表
- HAVIJNG 分组后条件列表
- ORDER BY 排序字段列表
- LIMIT 分页参数
执行顺序：②③④⑤①⑥⑦
select[all | distinct] [别名][, [别名]] ... from [别名][, [别名]] ... | (语句) [as] [where ] [group by [having 条件表达式>]] [order by [asc | desc]] [limit [offset ]]
在查询前，我们先创建一个表。
mysql > --创建学生成绩表 mysql > create table student(id int, name varchar(20), math decimal(3, 1), english decimal(3, 1); mysql > --插入测试数据 insert into student(id, name, math, english) values (1, '张三', 78, 90), (2, '李四', 78, 45), (3, '王五', 67.9, 45.6), (4, '赵六', NULL, 67.7), (5, '孙行者', 76.8, 90.0), (6, '者行孙', 80.4, 67.3);
4.2.1 基本查询：SELECT
- 全列查询
select * from 表名; // 把表中所有行所有列都查询出来
注：其中上面的 * 是通配符，可以代指所有列
- 指定列查询 select 列名, 列名... from 表名;
- 查询字段为表达式 （一边查询，一边计算）
在查询的时候，写作由列名构成的表达式，把这一列的所有行都带入到表达式中
注：但是原始数据不变，这个只是产生的临时数据。因此我们可以发现，查询的时候，是把服务器这里的数据读出来，返回给客户端，并且以临时表的形式进行展示。
SQL 查询的时候可以进行一些简单的统计操作，表达式查询吗，是列和列之间的运算，把每一行都带入到这样的运算中。
- 查询时指定别名
查询的时候给 列 / 表达式 指定别名（给表也能指定别名）
select 表达式 as 别名 from 表名;
-
去重查询
distinct 修饰某个列 / 多个列，值相同的行只保留一个。
注： 其中 单独的distinct只能放在开头，否则报错，语法错误
4.2.2 排序查询：ORDER BY
查询的时候进行排序，把行进行排序
注：需要明确排序规则~~
- 针对哪个列作为比较规则
- 排序的时候式升序还是降序
SELECT 列名 FROM 表名 ORDER BY 列名 ASC/DESC; // asc 升序，desc 降序
如果一个 sql 不加 order by 此时查询的结果数据的顺序是： “不确定的” / “无序”
注：其可以按照表达式进行排序，还可以多个列进行排序。比如数学成绩相同时，再按照语文顺序排序。
4.2.3 条件查询：WHERE
会指定具体的条件，按照条件针对数据进行筛选
SELECT 列名 FROM 表名 WHERE 条件
原理如下：遍历这个表每一行记录，把每一行的数据分别带入到条件中，如果条件成立，这个记录就会被放入结果集合中，不成立，这个记录就 pass
比较运算符：
运算符 说明 >, >=, = ALL
大于或等于子查询结果中的所有值 any(select sage from student where ssex='女'); +-------+-------+------+------+-------+ | sno | sname | ssex | sage | sdept | +-------+-------+------+------+-------+ | 95001 | 李勇 | 男 | 20 | CS | | 95002 | 刘晨 | 女 | 19 | IS | | 95004 | 张立 | 男 | 19 | IS | +-------+-------+------+------+-------+ ④ 带有 EXISTS 谓词的子查询
带有 EXISTS 谓词的子查询不返回任何数据，只产生逻辑真/假值
select sname from student where exists (select * from student where ssex='女'); +-------+ | sname | +-------+ | 李勇 | | 刘晨 | | 王敏 | | 张立 | | 刘云 | +-------+ // 上面的有问题，需要用别名 select sname from student s1 where exists (select * from student s2 where s2.ssex='女' and s1.ssex=s2.ssex); +-------+ | sname | +-------+ | 刘晨 | | 王敏 | | 刘云 | +-------+
注意：由 EXISTS 引出的子查询，其目标列表表达式通常都用 *，因为带有 EXISTS 的子查询只返回真值/假值，给出列名无实际意义
4.2.8 集合查询
select 语句的查询结果是元组的集合，所以对多个 SELECT 语句的结果可进行集合操作，集合操作主要包括 并操作（UNION）、交操作（INTERSECT）和 差操作（EXCEPT）
演示如下：
# student 表 +-------+-------+------+------+-------+ | sno | sname | ssex | sage | sdept | +-------+-------+------+------+-------+ | 95001 | 李勇 | 男 | 20 | CS | | 95002 | 刘晨 | 女 | 19 | IS | | 95003 | 王敏 | 女 | 18 | MA | | 95004 | 张立 | 男 | 19 | IS | | 95005 | 刘云 | 女 | 18 | CS | +-------+-------+------+------+-------+ 5 rows in set (0.01 sec) # course 表 +-----+-------------+--------+------+ | cno | cname | credit | pcno | +-----+-------------+--------+------+ | 1 | 数据库 | 4 | 5 | | 2 | 数学 | 6 | NULL | | 3 | 信息系统 | 3 | 1 | | 4 | 操作系统 | 4 | 6 | | 5 | 数据结构 | 4 | 7 | | 6 | 数据处理 | 3 | NULL | | 7 | PASCAL 语言 | 4 | 6 | +-----+-------------+--------+------+
操作如下：
# 查询性别为女生以及年龄小于19岁的学生 select * from student where ssex='女' UNION select * from student where sage
-
- 查询时指定别名
- 查询字段为表达式 （一边查询，一边计算）
- 指定列查询 select 列名, 列名... from 表名;
- 在定义列的同时指定主键
-
- 小知识：（datetime 类型的插入）
- 添加字段
- 查看表中的列
- show tables; (查看当前数据库下有那些表，比如我们刚刚使用了use来去使用数据库，则就在当前数据库进行查找)
