sql语句全解析!

M~Dragon

已于 2023-02-13 10:01:56 修改

阅读量3.2k

点赞数

分类专栏： sql 文章标签： sql 数据库 java

于 2023-02-08 10:47:00 首次发布

本文链接：https://blog.csdn.net/qq_45703593/article/details/128931513

版权

sql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一、分类

根据SQL指令完成的数据库操作的不同，可以将SQL指令分为四类

DDL Data Defintion language 数据库定义语言

- 用于完成对数据库对象(数据表,数据库,视图,索引)的创建，删除，修改

DML Data Manipulation language 数据操作语言

- 用于完成对数据表中的数据添加，删除，修改

DQL Data Query language 数据查询语言

- 用于将数据表中的数据查询出来

DCL Data Control Laguage 数据控制语言

- 用于完成事务管理等控制型操作

二、基本语法

SQL指令不区分大小写

每条SQL表达式结束之后都以;结束

SQL关键字之间都以空格进行分割

SQL之间可以不限制换行(可以有空格的地方就可以有换行)

三、数据类型

数值类型

类型	大小(Bytes)	范围
tinyint	１	有符号 (-128，127)无符号 (0，255)
smallint	２	有符号 (-32 768，32 767)无符号 (0，65 535)
mediumint	３	有符号 (-8 388 608，8 388 607)无符号 (0，16 777 215)
int/integer	４	有符号(-231,231- 1)无符号(0,2^32 - 1)
bigint	８	有符号(-263,263- 1)无符号(0,2^64 - 1)
float	４	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)有符号0，(1.175 494 351 E-38，3.402 823 466 E+38)
double	８	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)有符号 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)
decimal	DECIMAL(M<D)为 max(M+2,D+2)	依赖于M和D的值 decimal(m,n)表示数值一共有10位小数有2位

字符类型

类型	字符序列的长度范围	说明
char	0~255字节	定长字符串,最多可以存储255个字节；当我们指定数据表字段char(n) 此列中的数据最多长为n个字符，如果添加的字符串少于n则补’\u0000’至长n长度
varchar	0~655535字节	可变长度字符串,此类型的类最大长度65535
tinyblob	0~255字节	存储二进制字符串
blob	0~65535字节	存储二进制字符串
mediumblob	0~1677215	存储二进制字符串
longblob	0~2^64 -1字节	存储二进制字符串
tinytext	0~255字节	文本数据(字符串)
text	0~65535字节	文本数据(字符串)
mediumtext	0~2^24 -1字节	文本数据(字符串)
longtext	0~2^64 -1字节	文本数据(字符串)

日期类型

类型	格式	说明
date	2021-09-13	日期,只存储年月日
time	11:20:31	时间,只存储时分秒
year	2021	年份
datetime	2021-09-13 11:20:31	日期加时间,存储年月日时分秒
timestamp	20210913 112031	日期+时间(时间戳)

四、CREATE TABLE – 创建表

CREATE TABLE 表名称
(
  列名称1 数据类型,
  列名称2 数据类型,
  列名称3 数据类型,
  ....
);

4.1字段约束

字段常见的约束？

非空约束(not null):限制此列的值必须提供,不能null
唯一约束(unique):在表中的多条数据,此列的值不能重复
主键约束(primary key):非空+唯一,能够唯一标示数据表中的一条数据.
外键约束(foreign key):建立不同表之间的关联关系.

主键约束

主键–就是数据表中的记录中的唯一标识，在一张表中只能有一个主键(主键可以是一个字段,也可以是多个列组合)

当一个字段声明为主键之后，添加数据时;

此字段数据不能为null;
此字段数据不能重复;

创建表时添加主键约束：

create table books(
    book_isbn char(4),
    book_name varchar(20) primary key
);

或者

create table books(
    book_isbn char(4);
    book_name varchar(20),
    primary key(book_name)
);

追加主键：

## 创建表时没有添加主键约束
create table books(
    book_isbn char(4),
    book_name varchar(10) 
);

## 创建表之后添加主键约束
alter table books modify book_isbn char(4) primary key;

删除主键：

alter table books drop primary key;

主键自增长：

在我们创建一张数据表时，如果数据表中有可以作为主键(列如:学生表的学号,图书表的isbn)我们可以直接设为这个字段为主键,

当有些数据没有合适的字段作为主键的时候，我们可以定义一个与记录无关的列(ID)作为主键,此数据无具体含义主要作为标识唯一,在mysql中我们可以将此定义为int ，同时设置为自动增长

当我们想数据表中新曾一条数据时，无需提供ID列的值，它会自动增长.

定义主键自动增长：

create table types(
    type_id double primary key auto_increment,
    type_name varchar(20) not null,
    type_reamrk varchar(20)
);

联合主键

用两个主键包含的字段作为主键，这个组合在数据表中是唯一，且加了主键索引。这两个字段有一个字段不相同就可以区别两个数据，例如：你的订单表里有很多字段，一般情况只要有个订单号bill_no做主键就可以了，但是，现在要求可能会有补充订单，使用相同的订单号，
那么这时单独使用订单号就不可以了，因为会有重复。那么你可以再使用个订单序列号bill_seq来作为区别。把bill_no和bill_seq设成联合主键。即使bill_no相同，bill_seq不同也是可以的。

create table grades(
    stu_num char(8),
    course_id int,
    score int,
    primary key(stu_num,course_id)

);

外键约束

如果一个实体的某个字段指向另一个实体的主键，就称为外键。被指向的实体，称之为主实体（主表），也叫父实体（父表）。负责指向的实体，称之为从实体（从表），也叫子实体（子表）

外键的作用：
①为了一张表记录的数据不要太过冗余。

②保持数据的一致性、完整性。

not null约束

强制列不接受null值，强制字段始终包含值，意味着，如果不向字段添加值，就无法插入新记录或更新记录。

create table 表名称（ 
	列名称1 数据类型 not null，
	列名称2 数据类型 not null，
	列名称3 数据类型，。。。
）;

五、DELETE – 删除数据

5.1删除部分数据

删除某行：

DELETE FROM 表名称 WHERE 列名称 = 值;

删除所有行：

DELETE FROM table_name;

5.2删除表

drop table persons;

六、UPDATE – 更新数据

更新某一行中的一个列：
UPDATE Persons SET FirstName = 'Fred' WHERE LastName = 'Wilson';

更新某一行中的若干列：

UPDATE Persons SET ID_P = 6,city= 'London' WHERE LastName = 'Wilson';

七、INSERT – 插入数据

插入新的行

INSERT INTO Persons VALUES (1, 'Gates', 'Bill', 'Xuanwumen 10', 'Beijing');

在指定列插入数据

INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees');

八、SELECT – 查询数据

查询指定列

SELECT 列名称 FROM 表名称;

查询所有列

SELECT * FROM Persons;

8.1 DISTINCT – 去除重复值

SELECT DISTINCT 列名称 FROM 表名称;

8.2 WHERE – 条件过滤

SELECT 列名称 FROM 表名称 WHERE 列 运算符 值;

运算符：

操作符	描述
=	等于
<>	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于
BETWEEN	在某个范围内
LIKE	搜索某种模式

## ＝　等于
select * from stus where sut_num = '20202';
## != <>不等于
select * from stus where sut_num != '20202';
select * from stus where sut_num <> '20202';
## >大于
select * from stus where sut_age>18;
## <小于
select * from stus where sut_age<18;
## >=大于等于
select * from stus where sut_age>=18;
## <=小于等于
select * from stus where stu_age<=18;
## between and 区间查询
select * from stus where stu_age between 18 and 20;

8.3 AND & OR – 运算符

AND 和 OR 可在 WHERE 子语句中把两个或多个条件结合起来。

如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。
如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。

SELECT * FROM Persons WHERE FirstName='Thomas' AND LastName='Carter';

SELECT * FROM Persons WHERE firstname='Thomas' OR lastname='Carter';

8.4 ORDER BY – 排序

ORDER BY 语句用于根据指定的列对结果集进行排序，默认按照升序对记录进行排序，如果您希望按照降序对记录进行排序，可以使用 DESC 关键字。

SELECT * FROM 表名称 ORDER BY 列1,列2 DESC;

默认排序为 ASC 升序，DESC 代表降序。

SELECT * FROM Persons ORDER BY ID_P,LASTNAME;

8.5 like-模糊查询

select * from tableName where columName like 'reg';

在like关键字后的reg表达式中

- %表示任意多字符［%o%表示含有o］
- _表示任意一个字符

## 查询学生姓名包含字符o的学生信息
select * from stus where stu_name like '%o%';
## 查询学生姓名第一个字为张的学生信息
select * from stus where stu_name like '张%';
## 查询学生姓名最后一个字母为o的学生信息
select * from stus where stu_name like '%o';
##　查询学生姓名中第二个字母为o的学生信息
select * from stus where stu_name like '_o％';

8.6 聚合函数

sql中提供了一些可以对查询记录进行列计算的的函数–聚合函数

count() 统计函数,统计满足条件的指定字段的个数(记录数)

- COUNT(*) ：返回表中的记录数。
- COUNT(DISTINCT 列名) ：返回指定列的不同值的数目。
- COUNT(列名) ：返回指定列的值的数目（NULL 不计入）。

## 统计学生个数
select count(id) from stu;
## 统计女生个数
select count(sex) from stu WHERE sex='男';

max()记录指定列中的最大值
min()记录中指定列的最小值
sum()记录中指定列的和
avg()记录中指定记录的平均值

8.7 GROUP BY – 分组

GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。

SELECT 列名A, 统计函数(列名B)
FROM 表名
WHERE 查询条件
GROUP BY 列名A;、

# 先对查询的学生信息按性别进行分组(分成男,女),然后分别统计每组学生的个数
select sex,count(sex)from stu group by sex;
# 先对查询的学生信息按性别进行分组　然后计算
select sex,avg(scroe) from stu group by sex;
# 先对学生按照班级分组　然后统计各组的学生数量，在排序
select age,count(stu) from stu group by age order by age asc;
# 查询所有学生,按年龄进行分组,然后分别统计每组>1的人数,再筛选年龄升序 (having　隐藏记录　有group by 才能有　having)
select age,count(num)from stugroup by agehaving count(num)>1 order by age asc;

8.8 HAVING – 句尾连接

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。

SELECT 列名A, 统计函数(列名B)
FROM table_name
WHERE 查询条件
GROUP BY 列名A
HAVING 统计函数(列名B) 查询条件;

获取 Persons 表中住在北京的总人数大于1的 LASTNAME，根据 LASTNAME 分组

select lastname,count(city) from persons 
where city='Beijing' 
group by lastname
having count(city) > 1;

8.9 连接查询

通过DQL的学习,我们可以很轻松的从一张数据表中查询出需要的数据:在企业的应用开发,我们经常需要从多张表中查询数据(列如:我们查询学生信息的时候),可以通过连接查询从多张数据表提取数据:

在MySQL中可以使用join实现多表的联合查询–链接查询,

inner join 内连接
left join 左连接
right join 右连接

select 列名
from 表A
INNER|LEFT|RIGHT|FULL JOIN 表B
ON 表A主键列 = 表B外键列;

内连接

select ... from tableName inner join tableName2;

笛卡尔积

笛卡尔积(A集合&B集合):使用A中的每个记录依次关联B中每个记录,笛卡尔集的总数=A总数*B总数
如果直接执行select … from tableNmae1 inner join tableName2; 会获取两张数据表中的数据集合的笛卡尔积(依次使用tableName1表中的每条数据去匹配tableName2的每条数据)

内连接条件

两张表同时用inner join连接查询之后产生笛卡尔积数据很多是无意义的,我们如何消除无意义的数据----添加两张进行连接的查询时的条件

使用on设置两张表连接查询的匹配条件

-- 使用where设置过滤条件:先生成笛卡尔积再从笛卡尔积中过滤数据(效率很低)
select * from students 
inner join classes 
where students.cid=classes.class_id;
-- 使用ON设置连接查询条件:先判断连接条件是否成立,如果成立两张表的数据进行组合在生成一条结果记录
select * from students 
inner join classes 
on students.cid=classes.class_id;

左连接 LEFT JOIN

需求:查询出所有的学生信息,如果学生有对应的班级信息,则将对应的班级信息也查询出来.

左连接:显示左表中的所有数据,如果在右表中满足条件的数据,则进行匹配;如果右表中不存在匹配数据,则显示为null

select * from leftTable join rightTable on 匹配条件;

右连接 RIGHT JOIN

右连接:示右表中的所有数据,如果在右表中满足条件的数据,则进行匹配;如果左表表中不存在匹配数据,则显示为null

如果我们希望列出所有人的定购，可以使用下面的 SELECT 语句：

SELECT p.LastName, p.FirstName, o.OrderNo
FROM Persons p
INNER JOIN Orders o
ON p.Id_P = o.Id_P
ORDER BY p.LastName DESC;

8.10 UNION – 合并结果集

UNION 操作符用于合并两个或多个 SELECT 语句的结果集

SELECT 列名 FROM 表A
UNION
SELECT 列名 FROM 表B;

UNION 操作符默认为选取不同的值

UNION ALL 语法：

SELECT 列名 FROM 表A
UNION ALL
SELECT 列名 FROM 表B;

结果显示重复的值.

8.11 子查询/嵌套查询

子查询–先进行一次查询,第一次查询的结果作为第二次查询的/条件(源) (第二次查询是基于第一次查询的结果进行的)

案例:查询班级班级名为(mysql)的学生信息(只知道班级名称,而不知道班级ID)

传统方式

-- a.查询mysql的班级的编号
select class_id from classes where class_name='mysql';
-- b.查询此班级编号的学生信息
select * from students where cid=2

子查询单行单列

子查询:

select * from students 
where cid=(select class_id from classes where class_name='mysql');

案例2:查询所有Java班级中的学生信息

传统方式

-- a查询所有Java班的班级编号
select class_id from classes where class_name like 'Java%';
-- b查询这些班级编号中的学生信息(union 将多个查询语句的结果整合到一起)
select * from students 
where cid = 1
union select * from students 
where cid = 2
union select * from students where cid = 3

子查询多行单列

子查询

-- 如果查询返回的结果是多个值(单列多行),条件使用**in / not in**
select * from students 
where cid in (select class_id from classes where class_name like 'mysql%');

子查询返回多个值,多行多列

案例3:查询cid=1的班级中性别为男的学生信息

-- 传统的多条件查询
select * from students where cid=1 and stu_gender='男';
-- 子查询(先查询cid=1班级中的所有信息,将这些信息作为一个整体虚拟表 在基于这个虚拟表查询性别为男的学生信息)
select * from (select * from students where cid=1) t where t.stu_gender='男';

8.12 BETWEEN – 选取区间数据

操作符 BETWEEN … AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。

SELECT 列名/(*) FROM 表名称 WHERE 列名称 BETWEEN 值1 AND 值2;

查询上述结果相反的结果，可以使用 NOT：

SELECT * FROM Persons WHERE LastName NOT BETWEEN 'Adams' AND 'Carter';

8.13 AS – 别名

通过使用 SQL，可以为列名称和表名称指定别名（Alias），别名使查询程序更易阅读和书写。

表别名

SELECT 列名称/(*) FROM 表名称 AS 别名;、

SELECT p.LastName, p.FirstName
FROM Persons p 
WHERE p.LastName='Adams' AND p.FirstName='John';

列别名

SELECT 列名称 as 别名 FROM 表名称;

SELECT LastName "Family", FirstName "Name" FROM Persons;

8.14 IN – 锁定多个值

IN 操作符允许我们在 WHERE 子句中规定多个值。

SELECT 列名/(*) FROM 表名称 WHERE 列名称 IN (值1,值2,值3);

从 Persons 表中选取姓氏为 Adams 和 Carter 的人：

SELECT * FROM Persons WHERE LastName IN ('Adams','Carter');

8.15 分页查询

当数据表中的记录比较多的时候,如果一次性全部查询出来的显示给用户,用户的可读性/体验性就不太好,因为此我们可以将这些数据分页进行展示.

select ... from ... where ... limit param1,param2;

param1 int 获取查询语句的结果的第一条数据的索引(索引从0开始)
param2 int 获取查询语句的结果的条数(如果剩下的数据条数<pram2,则返回剩下的记录)

对数据表中的学生信息进行分页显示,总共59条数据,我们每页显示20条

总记录数: count 59

每页显示 page 20

总页数 pageCount = cpimt%page ?count/page:count/page +1;

# 查询第一页:
select * from stu limit 0,20;
# 查询第二页
select * from stu limit 20,20;
# 查询第三页
select * from stu limit 40,20;
# 如果一张表中,pageNum表示查询的页码,pageSize表示查询每页的条数:                                                                                                                                                       select * from stu limit (pageNum-1)*pageSize,pageSize;

九、VIEW – 视图

在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。

视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表。

CREATE VIEW 视图名 AS
SELECT 列名
FROM 表名
WHERE 查询条件;

视图总是显示最近的数据。每当用户查询视图时，数据库引擎通过使用 SQL 语句来重建数据。

将 Persons 表中住在 Beijing 的人筛选出来创建视图：

create view persons_beijing as
select * from persons where city='Beijing';

如果需要更新视图中的列或者其他信息，无需删除，使用 CREATE OR REPLACE VIEW 选项：

CREATE OR REPLACE VIEW 视图名 AS
SELECT 列名
FROM 表名
WHERE 查询条件;

M~Dragon

关注

0
点赞
踩
19

收藏

觉得还不错? 一键收藏
0
评论
sql语句全解析!

根据SQL指令完成的数据库操作的不同，可以将SQL指令分为四类SQL指令不区分大小写每条SQL表达式结束之后都以;结束SQL关键字之间都以空格进行分割SQL之间可以不限制换行(可以有空格的地方就可以有换行)数值类型类型大小(Bytes)范围tinyint１有符号 (-128，127)无符号 (0，255)smallint２有符号 (-32 768，32 767)无符号 (0，65 535)mediumint３有符号 (-8 388 608，8 388 607)无符号 (0，16 777 215)int/i
复制链接

扫一扫

专栏目录