我的博客

# DQL查询语句

关键字 select

# 查询全部

SELECT * FROM '表名'

# 查询特定列

SELECT '列名1','列名2' FROM '表名'

SELECT name,age FROM userTable;

# 条件查询(WHERE)

sql条件查询运算符 运算符

SELECT * FROM '表名' WHERE '条件'

SELECT * FROM userTable WHERE age>20;

# 多选一(in)

SELECT * FROM userTable WHERE age IN (1,2,3);

# 模糊查询(LIKE)

  • _任意字符
  • %任意多个字符
SELECT * FROM '表名' WHERE '条件' LIKE '模糊表达式'

SELECT * FROM userTable WHERE name LIKE '张%';

# 去重查询(DISTINCT)

SELECT DISTINCT '列名' FROM '表名'

SELECT DISTINCT name FROM userTable;

# 排序查询(ORDER BY)

  • ASC 升序(默认)
  • DESC 降序
SELECT * FROM '表名' ORDER BY '列名' [ASC/DESC]

-- 按age 降序
SELECT * FROM userTable ORDER BY age DESC;
-- 按age 升序 按名字降序 只有当第一个字段值相同时 第二个字段值才会生效
SELECT * FROM userTable ORDER BY age,name DESC;

# 分组查询(GROUP BY HAVING)

分组查询用于将具有相同值的行归为一组，并可以使用聚合函数对这些组进行操作。

SELECT '列名','聚合函数（列名）' FROM '表名' GROUP BY '列名' [HAVING '条件']

--按age分组，统计每个年龄段的人数
SELECT age,COUNT(*) AS Count FROM userTable GROUP BY age;

-- 使用聚合函数统计性别男和女总共有多少人
select  FK_DICT_101_NAME,count(*) from ONL_PERSON t group by t.FK_DICT_101_NAME;

--使用聚合函数统计性别男和女总共有多少人 并按每组的人数降序
select t.FK_DICT_101_NAME,count(*) s from ONL_PERSON t group by t.FK_DICT_101_NAME order by s desc;

--使用聚合函数统计性别男和女总共有多少人 并按每组的人数降序 获取人数大于等于2的性别
select t.FK_DICT_101_NAME,count(*) s from ONL_PERSON t group by t.FK_DICT_101_NAME order by s desc count(*)>=2;

# 分页查询

  • 起始索引 = (当前页码 - 1) * 每页显示条数
SELECT * FROM '表名' LIMIT '起始行','查询条数'

SELECT * FROM userTable LIMIT 0,5;

# 常用聚合函数

注意

null值不参与所有聚合函数的运算

名称 说明
COUNT(列) 计算非空值的数量
SUM(列) 计算列的总和
MAX(列) 返回最大值
MIN(列) 返回最小值
AVG(列) 返回平均值
--统计表中总共有多少条数据 需要非空数据
SELECT COUNT(*) FROM userTable;
-- 统计最早入职的员工
SELECT MIN(create_time) FROM userTable;

# 多表查询(A表外键=B表外键)

select * from user_info a,user b where a.user_id=b.id;

# 联接查询

联接查询用于从多个表中基于相关列的条件组合数据。常见的联接类型有内联接（INNER JOIN）、全外联接（FULL OUTER JOIN）、左联接（LEFT JOIN）、右联接（RIGHT JOIN）。

  • 内连接:相当于查询A、B交集部分数据

外连接

  • 左外连接:查询左表所有数据(包括两张表交集部分数据)
  • 右外连接:查询右表所有数据(包括两张表交集部分数据)

# 示例

假设有两个表

  • students表包含 id、name、age字段
  • courses表包含 student_id、course_name字段

# 内联接

  • 隐式内连接:select字段列表 from 表1,表2 where 条件…;
#a.user_id=b.id 使用外键消除无效的笛卡尔集
select b.name,if(a.sex=1,'男','女')  from user_info a,user b where a.user_id=b.id and a.user_id=1;
  • 显式内连接:select 字段列表 from 表1[inner]join 表2 on 连接条件 …;
  • 只返回两个表中匹配的行
SELECT '表1.列名1', '表2.列名' FROM '表1' INNER JOIN '表2' ON '表1.条件列名' = '表2.条件列名'

--联表查询（内联接）返回两个表中匹配行的全部字段
select * from ONL_PERSON t1 inner join ONL.ONL_PERSON_RELATION t2  on t1.PK_ID=t2.FK_ONL_PERS;

--联表查询（内联接）返回两个表中匹配行
select * from ONL_PERSON t1 inner join ONL.ONL_PERSON_RELATION t2  on t1.PK_ID='2304060001';

# 外连接

# 左联接

返回左表中所有的行 即使右表中没有匹配的行

--左联接
select * from ONL_PERSON t1 left join ONL.ONL_PERSON_RELATION t2  on t1.PK_ID=t2.FK_ONL_PERS;
# 右联接

返回右表中的所有行，即使左表中没有匹配的行。

--右联接
select * from ONL_PERSON t1 right join ONL.ONL_PERSON_RELATION t2  on t1.PK_ID=t2.FK_ONL_PERS;
# 全右联接

返回两个表中所有的行，无论是否有匹配的行(某些数据库可能不支持)

select * from ONL_PERSON t1 full join ONL.ONL_PERSON_RELATION t2  on t1.PK_ID=t2.FK_ONL_PERS;

# 子查询

子查询是嵌套另一个查询中的查询，子查询可以用于SELECT、INSERT、UPDATE、DELETE语句中

SELECT * FROM '表名' WHERE '列名' IN (SELECT '列名' FROM '表名' WHERE '条件')

SELECT * FROM students WHERE age IN (SELECT age FROM students WHERE name LIKE '%张%');

select * from (select * from ONL_PERSON t1 where t1.FK_DICT_101_NAME='男') t2 where t2.FK_DICT_105_NAME='汉族';

select * from ONL_PERSON t1  where t1.FK_DICT_105_NAME in (select t1.FK_DICT_105_NAME from ONL_PERSON where t1.FK_DICT_101_NAME='男');

select * from ONL_PERSON_RELATION t2  where t2.FK_ONL_PERS in (select t2.FK_ONL_PERS from ONL_PERSON t1 where t1.FK_DICT_101_NAME='男' and t2.FK_ONL_PERS='2304060001' );

# 分页查询

分页查询用于限制返回的记录数量，以便于处理大量数据时进行分页显示

SELECT * FROM '表名' LIMIT '偏移量', '每页记录数'

select  * from ONL_ORDER t1 where t1.HIS_PERSON_ID in (select t2.PK_ID from ONL_PERSON t2 where t2.PERSON_NAME='沈浩龙') OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY ;

# 事务

事务用于保证一组数据库操作，要么同时成功，要么同时失败

注意

默认mysql中事务都是自动提交的，即执行SQL语句后就会马上执行COMMIT操作。因此要显式地开启一个事务务须使用命令BEGIN，用来禁止使用当前会话的自动提交

  • 开启事务 BEGIN 或 START TRANSACTION
  • 事务提交 COMMIT
  • 事务回滚 ROLLBACK
#事务操作
#开启事务
#两条语句要么同时成功 要么同时失败
start transaction;
#删除人员 删除人员表数据及人员详情数据
#删除人员表数据
delete from user where name = 'admin';
#删除人员详情表数据
delete from user_info where user_id = (select id from user a where a.name == 'admin');
#提交事务
commit;
#回滚事务
rollback;

# 事务的四大特性

  • 原子性（Atomicity）：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
  • 一致性（Consistency）：执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
  • 隔离性（Isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
  • 持久性（Durability）：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

# 索引

索引（index）是帮助数据库高效获取数据的数据结构,常见的索引本质是二叉搜索树

# 优点

  • 大大减少了服务器需要扫描的数据量，降低数据库的io成本
  • 通过索引列对数据进行排序，降低数据排序的成本，降低cpu消耗

# 缺点

  • 索引也是一张表，保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
  • 索引大大提高了查询的效率，同时也降低了insert、update、delete的效率，因为insert、update、delete时，还要操作索引表

# 索引的数据结构

  • B+Tree(多路平衡搜索树)索引结构

# 语法

  • 在表中只要我们指定了主键，mysql就会自动创建一个索引 叫做主键索引
  • 如果表中字段具有唯一约束，那么mysql也会自动创建一个索引 叫做唯一索引
# 创建索引
CREATE INDEX 索引名字 ON mytable(表名(字段名));
       
       create index index_name on user(id)
       
#查询索引
 show index from 表名;

show index from user;

       
# 删除索引
drop index 索引名字 on 表名;
     
drop index index_name on user;     
     
# 在创建表时指定索引
CREATE TABLE mytable(
    ID INT NOT NULL,
    username VARCHAR(16) NOT NULL,
    INDEX [index_name] (username(length))
);
上次更新: 2/21/2025, 5:30:13 PM

← 改 →