SQL语通用语法及其分类
- Author:leon
- Date:2024-10-15
只有执行力，才是破除你迷茫的关键人生有一大悲剧，就是你只知道在心中排兵布阵，生活中瞻前顾后，浑噩度日，不肯尝试，不肯付出
DDL(Data Definition Language )
数据库定义语言，用来定义数据库对象（数据库，表，字段）
数据库操作
//查询
show databases; //查询所有数据库
select database();//查询当前数据库
//创建
create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];
//删除
drop database [if exists] 数据库名;
//使用
use 数据库名；
表操作-创建&查询
创建表
create table 表名（
字段1 字段1类型[comment 字段1注释]，
字段2 字段2类型[comment 字段2注释]，
字段3 字段3类型[comment 字段3注释]，
......
字段n 字段n类型[comment 字段n注释]，
）[comment 表注释];
show tables; //查询当前数据库所有表
desc 表名; //查询表结构
show create table 表名； //查询指定表的建表语句
查询当前数据库的所有表
查询表结构
查询指定表的建表语句
表操作—数据类型
主要分三类：数值类型，字符串类型，日期时间类型
数值类型：
-- 整数类型
TINYINT -- 小整数，占用 1 字节
-- signed 范围：-128 ~ 127
-- unsigned 范围：0 ~ 255
SMALLINT -- 小整数，占用 2 字节
-- signed 范围：-32768 ~ 32767
-- unsigned 范围：0 ~ 65535
MEDIUMINT -- 中等整数，占用 3 字节
-- signed 范围：-8388608 ~ 8388607
-- unsigned 范围：0 ~ 16777215
INT / INTEGER -- 标准整数，占用 4 字节
-- signed 范围：-2147483648 ~ 2147483647
-- unsigned 范围：0 ~ 4294967295
BIGINT -- 大整数，占用 8 字节
-- signed 范围：-2^63 ~ 2^63 - 1
-- unsigned 范围：0 ~ 2^64 - 1
-- 浮点与定点类型
FLOAT -- 单精度浮点数（近似值），通常占用 4 字节
-- 存在精度误差
DOUBLE -- 双精度浮点数（近似值），通常占用 8 字节
-- 精度高于 FLOAT，但仍有误差
DECIMAL(M, D) -- 定点小数（精确值）
-- M：总位数（精度）
-- D：小数位数（标度）
-- 例如：DECIMAL(5,2) 可表示 123.45
字符串类型：
char
//定长字符串
varchar
//变长字符串
tinyblob
//不超过255个字符的二进制数据
tinytext
//短文本字符串
blob
//二进制形式的长文本数据
text
//长文本数据
mediumblob
//二进制形式的中等长度文本数据
mediumtext
//中等长度文本数据
longblob
二进制形式的极大文本数据
longtext
极大文本数据
日期类型：
date
1000-01-01~9999-12-31 日期值
time
-838:59:59~838:59:59 时间或者持续时间
year
1901～2155 年份值
datetime
1000-01-01 00:00:00～9999-12-31 23:59:59 混合日期和时间值
timestamp
1970-01-01 00:00:01～2038-01-19 03:14:01 混合日期和时间值，时间戳
表操作—修改
添加字段
alter table 表名 add 字段名 类型（长度）[comment 注释] [约束];
eg.为empinfor表添加一个新的字段“昵称”为nickname，类型为varchar（20）
alter table empinfor add nickname varchar(20) comment'昵称';
修改数据类型
alter table 表名 modify 字段名 新数据类型（长度）;
eg.将empinfor中的nickname的长度改为30
alter table empinfor modify nickname varchar(30);
修改字段名和字段类型
alter table 表名 change 旧字段名 新字段名 类型（长度）[comment 注释][约束];
将empinfor中的nickname字段改为username,仍保持原类型
alter table empinfor change nickname username varchar(30) comment'昵称';
注意：不能直接在列级添加注释和唯一约束，需要分步完成
删除字段
alter table 表名 drop 字段名;
eg.删除empinfor表中的username字段
alter table empinfor drop username;
修改表名
alter table 表名 rename to 新表名;
eg.将empinfor改为emp
alter table empinfor rename to emp;
删除表
drop table [if exists] 表名;
eg.删除emp表
drop table if exists emp;
删除指定表，并重新创建该表(表结构不变，表中存储的信息全部清空)
truncate table 表名；
eg.删除emp表，并重新创建改表
truncate table emp;
表操作总结：对于数据库表的操作，基本都以alter table 关键字开头，删除表以及删除并重新创建表结构除外
DML(Data Manipulation Language)
数据操作语言，用来对数据库表中的数据进行增删改查
DML-添加数据
1.给指定字段添加数据
insert into 表名 (字段名1，字段名2...)values(值1，值2...)
2.给全部字段添加数据
insert into 表名 values（值1，值2...）
3.批量添加数据
insert into 表名 values (值1，值2...),(值1，值2...);
注意：
- 插入数据时，指定的字段顺序需要与值一一对应
- 字符串和日期型数据应包含在引号范围内
- 插入的数据大小，应该在字段的规定范围内
DML-修改数据
update 表名 set 字段名1=值1,字段名2=值2,...[where 条件]
注意：修改语句的条件可有可无，如果没有修改条件，则会修改该字段整张表的所有数据
DML-删除数据
delete from 表名 [where 条件]
注意：
delete语句的条件可有可无，如果没有条件，则会删除整张表的所有数据
delete语句不能删除某一个字段的值（可以使用update）
DQL(Data Query Language)
数据查询语言，用来查询数据库中表的记录
DQL-基本查询
1.查询多个字段
select 字段1，字段2，字段3... from 表名;
2.查询所有字段
select * from 表名；
3.设置字段别名
select 字段1[As 别名1]，字段2[As 别名2]，字段3[As 别名3]... from 表名;
4.通过distinct去除重复数据
select distinct 字段名 [As 别名] from 表名；
DQL-条件查询
1.语法
select 字段列表 from 表名 where 条件列表；
2.条件
...
<> / != 不等于
between...and... 在某个范围之内（含最小，最大值）
in (...) 在in之后的列表中的值，多选一
like 占位符（模糊匹配，_ 匹配单个字符，%匹配任意个字符）
is/not null 是null/不是null
and / && 并且
or / || 或者
not / ! 非
Eg.
select * from student;
insert into student values("张三",18,"001"),
("李四",19,"002"),
("王五",20,"003");
insert into student values("美丽",21,null),
("漂亮",22,"003");
# 查询年龄等于18的学生信息
select * from student where age=18;
# 查询年龄小于20的学生信息
select * from student where age<20;
# 查询没有学号的学生信息
select * from student where id is null;
# 查询有学号的学生信息
select * from student where id is not null ;
# 查询年龄不等于99的学生信息
select * from student where age<>99;
select * from student where age !=99;
# 查询年龄在15-20岁之间的学生信息
select * from student where age between 15 and 20;
#查询学号为001 且姓名为张三的学生信息
select * from student where id="001" and name="张三";
#查询年龄为18或19或20或21的学生信息
select * from student where age=18 or age=19 or age=20 or age=21;
select * from student where age in (18,19,20,21);
# 查询姓名为两个字的学生信息
select * from student where name like "__";
# 查询姓名是以五结尾的学生信息
select * from student where name like "%五"；
DQL-聚合函数
将一列数据作为一个整体，进行纵向计算
1.常见聚合函数
| 函数 | 功能 |
|---|---|
| count | 统计数量 |
| max | 最大值 |
| min | 最小值 |
| avg | 平均值 |
| Sum | 求和 |
2.语法
select 聚合函数（字段列表）from 表名
注意：null值不参与聚合函数的运算
Eg.
# 统计学生人数
select count(*) from student;
# 计算学生的平均年龄
select avg(age) from student;
# 获取学生的最大年龄
select max(age) from student;
# 获取学生的最小年龄
select min(age) from student;
# 获取18岁学生年龄之和
select sum(age) from student where age=18;
DQL-分组查询
主要意义在于对数据进行分类和聚合，以便进行汇总分析,通过
GROUP BY和聚合函数，可以高效处理和分析大量数据。
1.语法
select 字段列表 from 表名[where 条件] group by 分组字段名 [having 分组后的过滤条件];
2.where与having的区别
- 执行时机不同：where是分组之前进行过滤，不满足where条件的不参与分组，而having是分组之后对结果进行过滤
- 判断条件不同：where不能对聚合函数进行判断，而having可以
注意：
- 执行顺序：where>聚合函数>having
- 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义
DQL-排序查询
1.语法
select 字段列表 from 表名 order by 字段1 排序方式1，字段2 排序方式2;
2.排序方式
asc 升序（默认值）
desc 降序
注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序
DQL-分页查询
1.语法
select 字段列表 from 表名 limit 起始索引，查询记录数;
注意：
-
起始索引从0开始，起始索引=（查询页码-1）*每页显示记录数
-
分页查询是数据库的“方言”，不同的数据库有不同的实现方式，Mysql中是limit
-
如果查询的是第一页的数据，起始索引可以省略，直接写查询记录数即可
DCL(Data Control Language)
数据控制语言，用来创建数据库用户，控制数据库的访问权限
DCL-用户管理
1.查询用户
use mysql;
select * from user;
2.创建用户
create user '用户名'@'主机名' identified by '密码';
主机名可以用%进行通配，表示任意主机都可访问该数据库
3.修改用户密码
alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';
4.删除用户
drop user '用户名'@'主机名';
DCL-权限控制
1.查询权限
show grants for '用户名'@'主机名';
2.授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
3.撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
注意：
- 多个权限之间使用逗号分隔
- 授权时，数据库名和表名可以使用*进行通配，代表所有
System.out.println("hello")