🍭 SQL查询语句用于从数据库中检索数据，常见语句有SELECT、FROM等，通过它们，用户可对数据进行灵活的查询和分析。

1 数据库介绍

数据库介绍
- 数据库，是一种用于存储和管理数据的数据存储系统。
- 根据数据模型、存储方式、应用领域等分为多种类型。
  - 关系型数据库(Relational DataBase)，RDBMS。
    - 基于关系模型，使用SQL(结构化查询语言)进行数据操作。
    - MySQL、PostgreSQL、Oracle、SQL Server、SQLite等。
  - NoSQL数据库(Non-relational DataBase)，非关系型数据库。
    - 不基于关系模型，根据不同的数据模型进行设计，如：文档型、键值对、列族等。
    - MongoDB、Redis(键值)、HBase(列族)、InfluxDB(时间序列)、Neo4j(图形)等。
    - 适合处理结构化、非结构化或半结构化数据，如社交网络、内容管理、实时分析。
- NoSQL数据库
  - 非关系型数据库使用的查询语言多样，具体取决于其底层数据存储模型。
  - 文档型数据库，如MongoDB，使用基于集合的语言(如MongoDB查询语言，MQL)。
  - 键值对数据库，如Redis，使用键值对查询语言，包括键值操作命令：SET、GET等。
  - 列族数据库，如HBase或Cassandra，使用SQL变体查询语言(Cassandra查询语言，CQL)。
  - 图形数据库，如Neo4j，使用的是图形查询语言，如Cypher可用来查询节点、关系及属性。

2 DBeaver工具

DBeaver工具
- DBeaver是一款免费且开源的多平台数据库管理工具，支持多种数据库。
- DBeaver通过JDBC连接到数据库，几乎可以支持所有的数据库产品。
- 采用Eclipse框架开发，支持插件扩展，并提供许多数据库管理工具。
- 下载安装前需要安装Jre，官网地址：https://dbeaver.io/download/。
- 基本使用：新建数据库 -> 测试连接 -> 执行查询 -> 查看数据 -> 导入导出数据。
- 执行查询：使用SQL编辑器执行查询语句，或者使用可视化查询构建器生成查询语句。

3 SQL语句类型

SQL语句类型
- 数据定义语言：DDL (Data Definition Language)，定义和管理数据库中的结构和对象。
- 数据操作语言：DML (Data Manipulation Language)，操作数据库中的数据，增删改操作。
- 数据查询语言：DQL (Data Query Language)，用于查询数据库中的数据，常用命令SELECT。
- 数据控制语言：DCL (Data Control Language)，控制对数据库的访问权限，授予或撤销权限。

4 数据定义DDL

数据定义DDL
- CREATE：创建新的数据库、表、视图、索引等对象。
- ALTER：修改现有对象的结构，比如添加或者删除列。
- DROP：删除数据库或表。
- TRUNCATE：删除表中的所有记录，但不删除表结构。

4-1 操作库

-- 创建库
create database db1;

-- 创建库，判断是否存在，不存在则创建
create database if not exists db2;
create database if not exists db3;

-- 查看所有数据库
show databases;

-- 查看数据库的结构定义信息
show create database db1;

-- 修改数据库的字符集为utf8
alter database db1 character set utf8;

-- 删除数据库
drop database db3;

-- 使用指定的数据库
use db1;

-- 查看当前指定的数据库包含的数据表
show tables;

4-2 操作表

-- 创建数据表
create table student(
  id int,
  name varchar(32),
  age int,
  score double(4,1),
  birthday date,
  insert_time timestamp
);

create table stu2(
  id int,
  name varchar(32),
  age int,
  score double(4,1),
  birthday date,
  insert_time timestamp
);

create table stu3(
  id int,
  name varchar(32),
  age int,
  score double(4,1),
  birthday date,
  insert_time timestamp
);

-- 查看表结构
desc student;

-- 查看创建表的SQL语句
show create table student;

-- 修改表名
alter table student rename to stu1;

-- 添加列：top、test
alter table stu1 add top float;
alter table stu1 add test float;

-- 修改列名
alter table stu1 rename column top to tops;

-- 修改列类型，注意修改前后的数据是否兼容
alter table stu1 modify tops int;

-- 查看表结构
desc stu1;

-- 删除列
alter table stu1 drop test;

-- 删除表：删除整张表及其结构
drop table stu3;
drop table if exists stu3;

-- 删除表所有数据：高效，先删除表，再创建一张一样的表
truncate table stu1;

5 数据操作DML

数据操作DML
- INSERT：表中插入新数据。
- UPDATE：更新现有数据行。
- DELETE：删除表中的数据。

-- 写全所有列名
insert into 
	stu1 (id, name, age, score, birthday, insert_time, tops) 
values (001, '刘一', 20, 87, '2004-12-03', '2024-11-08', 21);

-- 不写列名
insert into 
	stu1 
values (002, '陈二', 20, 94, '2004-06-14', '2024-11-08', 7);

-- 插入部分数据
insert into 
	stu1 (name, age, score, birthday, tops) 
values ('张三', 21, 99, '2003-01-09', 3);

insert into 
	stu1 (name, age, score, birthday, tops) 
values ('李四', 201, 93, '2003-01-19', 11);

-- 不带条件的修改，会修改所有行
update stu1 set age = 21;

-- 带条件的修改，年龄2004年的全改为20岁，%指多个字符，_指单个字符
update stu1 set age = 20 where birthday like '2004-%';

-- 删除表中数据
delete from stu1 where name = '张三';

-- 删除表所有数据
delete from stu1;

6 数据查询DQL

数据查询DQL
- SELECT：选择和返回数据。
- WHERE：指定条件过滤查询结果。
- ORDER BY：对查询结果进行排序。
- GROUP BY：将结果按一或多个列分组，常与聚合函数一起使用。
- HAVING：对聚合后的结果进行过滤，常与GROUP BY结合使用。

01	刘一	20	93.0	2004-01-01	2024-11-08 00:00:00	7
02	陈二	22	100.0	2002-06-14	2024-11-08 00:00:00	1
03	张三	20	97.0	2004-07-12	2024-11-08 00:00:00	4
04	李四	20	94.0	2004-12-03	2024-11-08 00:00:00	6
05	王五	21	99.0	2003-06-19	2024-11-08 00:00:00	2
06	赵六	20	88.0	2004-11-10	2024-11-08 00:00:00	12
07	孙七	20	85.0	2004-06-14	2024-11-08 00:00:00	13
08	周八	19	93.0	2005-04-29	2024-11-08 00:00:00	5
09	吴九	20	73.0	2004-02-17	2024-11-08 00:00:00	15
10	郑十	21	90.0	2003-03-16	2024-11-08 00:00:00	10
11	曾月	23	NULL	2002-06-15	2024-11-08 00:00:00	NULL
12	刘洋	21	96.0	2003-06-19	2024-11-08 00:00:00	5
13	陈辉	20	66.0	2004-03-25	2024-11-08 00:00:00	16
14	赵宝	21	90.0	2003-07-02	2024-11-08 00:00:00	11
15	王华	20	91.0	2004-06-17	2024-11-08 00:00:00	9
16	周成	20	98.0	2004-04-10	2024-11-08 00:00:00	3
17	郑佳	21	76.0	2003-04-30	2024-11-08 00:00:00	14

6-1 简单查询

-- 查询年龄大于等于20，小于等于21
select * from stu1 where age between 20 and 21;
select * from stu1 where age >= 20 && age <= 21;
select * from stu1 where age >= 20 and age <= 21;

-- 查询年龄19岁，22岁，23岁的信息
select * from stu1 where age in (19, 22, 23);
select * from stu1 where age = 19 or age = 22 or age = 23;

-- 查询成绩为null
select * from stu1 where score is null;

6-2 模糊查询

-- 查询刘姓
select * from stu1 where name like '刘_';
select * from stu1 where name like '刘%';

-- 查询姓名带宝
select * from stu1 where name like '_宝';
select * from stu1 where name like '%宝';

-- 返回唯一不同的值
select distinct age from stu1;

6-3 排序查询

-- 升序排序
select * from stu1 order by score;

-- 降序排序
select * from stu1 order by score desc;

-- 分数升序，生日降序排序
select * from stu1 order by score, birthday desc;

6-4 聚合函数

-- 计算和
select sum(score) from stu1;
select sum(score) as sum_value from stu1;

-- 计算最大值
select max(score) from stu1;
select max(score) as max_value from stu1;

-- 计算最小值
select min(score) from stu1;
select min(score) as min_value from stu1;

-- 计算平均值
select avg(score) from stu1;
select avg(score) as avg_value from stu1;

-- 计算总个数
select count(score) from stu1;
select count(score) as total_count from stu1;

6-5 分组查询

-- 按年龄分组，查询成绩平均分
select age, avg(score) from stu1 group by age;

-- 按年龄分组，查询成绩平均分，人数
select age, avg(score), count(id) from stu1 group by age;

-- 按年龄分组，查询成绩平均分，人数，分数为null的人不参与分组
select 
	age, 
	avg(score), 
	count(id) 
from 
	stu1 
where 
	score 
is not null group by age;

-- 按年龄分组，查询成绩平均分和人数，分数为null则不参与分组，分组后人数要大于等于2人
select 
	age, 
	avg(score), 
	count(id) 
from 
	stu1 
where 
	score 
is not null group by age having count(id) >= 2;

6-6 分页查询

-- 查询表中前5条记录，第1条记录索引为0
select * from stu1 limit 5;

-- 查询表中第2~8条记录
select * from stu1 limit 1, 7;

-- 每页显示6条记录，分3页显示
select * from stu1 limit 0, 6;
select * from stu1 limit 6, 6;
select * from stu1 limit 12, 6;

6-7 关联表查询

01	武汉	18859665110	18859665110@qq.com
02	长沙	18859665111	18859665111@qq.com
03	赣州	18859665112	18859665112@qq.com
04	合肥	18859665113	18859665113@qq.com
05	厦门	18859665114	18859665114@qq.com
06	龙岩	18859665115	18859665115@qq.com
07	漳浦	18859665116	18859665116@qq.com
08	临江	18859665117	18859665117@qq.com
09	昆阳	18859665118	18859665118@qq.com
10	福州	18859665119	18859665119@qq.com
11	莆田	18859665120	18859665120@qq.com
12	皖南	18859665121	18859665121@qq.com
13	沈阳	18859665122	18859665122@qq.com
14	信宜	18859665123	18859665123@qq.com
15	南京	18859665124	18859665124@qq.com
16	济南	18859665125	18859665125@qq.com
17	深圳	18859665126	18859665126@qq.com

(1) 创建表

-- 创建关联数据表，stu_info.id与stu1.id关联
create table stu_info(
  id int,
  city varchar(255),
  phone varchar(20),
  email varchar(50),
  primary key (id)
);

-- 修改email为 “手机号 + @qq.com” 格式
update stu_info set email = concat(phone, '@qq.com');

-- 修改email为 “手机号 + @qq.com” 格式
update 
	stu_info 
set 
	email = concat(phone, '@qq.com') 
where email like '%@qq.com';

-- 修改email为 “手机号 + @qq.com” 格式
update 
	stu_info 
set 
	email = concat(phone, '@qq.com') 
where email like '{phone}@qq.com';

(2) 内连接

-- 隐式内连接
select 
	stu1.name, 
	stu_info.city 
from 
	stu1, 
	stu_info
where 
	stu1.id = stu_info.id;

-- 显式内连接（推荐，可读性和维护性更好）
select 
	stu1.name, 
	stu_info.email 
from 
	stu1 
inner join 
	stu_info 
on 
	stu1.id = stu_info.id;

(3) 外连接

-- 左外连接查询（需要保留左表中所有记录的情况）
select 
	stu1.name, 
	stu_info.city 
from 
	stu_info
left join
	stu1
on
	stu1.id = stu_info.id;

-- 左外连接查询（需要保留左表中所有记录的情况）
select 
	stu1.name, 
	stu_info.email
from 
	stu_info
left outer join
	stu1
on
	stu1.id = stu_info.id;

-- 右外连接查询（需要保留右表中所有记录的情况，可读性较差）
select 
	stu1.name, 
	stu_info.phone 
from 
	stu_info
right join 
	stu1
on
	stu1.id = stu_info.id;

-- 右外连接查询（需要保留右表中所有记录的情况，可读性较差）
select 
	stu1.name, 
	stu_info.phone 
from 
	stu_info
right outer join 
	stu1
on
	stu1.id = stu_info.id;

(4) 子查询

-- 查找表中分数最高的学生信息（单表查询）
select * from
	stu1
where score = (select max(score) from stu1);

-- 查找表中分数小于等于平均分的所有学生信息（单表查询）
select * from
	stu1
where score <= (select avg(score) from stu1);

-- 查找表中分数最低的学生信息，作为判断条件输出（单表查询）
select * from
	stu1
where
	name
in (
	select
		name
	from
		stu1
	where score = (select min(score) from stu1)
);

-- 查询所有学生的name和他们的city（关联表查询，子查询方式）
select 
	stu1.name,
	(
		select 
			stu_info.city 
		from 
			stu_info 
		where stu_info.id = stu1.id
	)
	as city 
from stu1;

-- 查询所有学生的name和他们的city（关联表查询，JOIN方式，更直观高效）
select
	stu1.name,
	stu_info.city
from
	stu1
join stu_info on stu1.id = stu_info.id;

7 数据控制DCL

-- 添加用户
create user 'test'@'localhost' identified by '123456';

-- 查询用户
select user, host from mysql.user;

-- 查询权限
show grants for 'test'@'localhost';

-- 授予权限，给test用户授予db1库的stu1表所有权限
grant all on db1.stu1 to 'test'@'localhost';

-- 撤销权限
revoke update on db1.stu1 from 'test'@'localhost';

-- 删除用户
drop user 'test'@'localhost';

数据库

#MySQL #数据库 #DBeaver #Insert #Select

SQL

https://stitch-top.github.io/2024/10/25/shu-ju-ku/db01-sql/

作者

Dr.626

发布于

2024年10月25日 22:10:00

许可协议

Js ES6 特性下一篇