SQL 基本2

最新推荐文章于 2024-03-28 09:59:00 发布
最新推荐文章于 2024-03-28 09:59:00 发布
阅读量622 收藏
点赞数
分类专栏： SQL 文章标签： sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_44230959/article/details/116406358
版权

文章目录

1. DQL查询数据（重点）

1.1 DQL

（Data Query Language:数据查询语言）

1.2 指定查询字段 SELECT（简单查询）

在这里插入图片描述

  1. select 去重 要查询的字段 from 表（注意：表和字段可以取别名） xxx
  2. join 要连接的表 on 等值判断
  3. where（具体的值，子查询语句） 只能有一个，可用and连接
  4. Group By（通过哪个字段来分组）
  5. Having（过滤分组后的信息，条件和where是一样的，位置不同） order By（通过那个字段排序）【升序/降序】 limit
  6. startindex，pagesize
    业务层面：
    查询，跨表，跨数据库
'查询全部的学生  select 字段 from 表'
SELECT * FROM `student`

'-- 查询指定字段'
SELECT `studentno`,`studentname` FROM `student`

'-- 别名，给结果起一个名字 AS ,可以给字段起别名，也可以给表起别名'
SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM `student` AS s

'-- 函数 Concat(a,b)连接字符串'
SELECT CONCAT('姓名：',`studentname`) AS 新名字 FROM `student`

– 语法：SELECT 字段。。。 FROM student

  • 有的时候，列名字不是那么见名知意。起别名 AS 字段名 as 别名 表名 as 别名

- 别名在这里插入图片描述

在这里插入图片描述
在这里插入图片描述

  • 函数
    在这里插入图片描述

1.3 去重 DISTINCT

  • 作用：去除SELECT 查询出来的结果中重复的数据，重复的数据只显示一条
'-- 查询全部的考试成绩'
SELECT * FROM `result`
'-- 查询有哪些同学参加了考试'
SELECT `studentno` FROM `result`
'-- 去重'
SELECT DISTINCT `studentno` FROM `result`

1.4 数据库的列（表达式）

'-- 查询系统版本'
SELECT VERSION()
'-- 计算结果'
SELECT 100*3-1 AS 计算结果
'-- 查询自增的步长(变量)' -- 增长的单位
SELECT @@auto_increment_increment

'-- 学员考试成绩 +1查看'
SELECT `studentno`,`studentresult`+1 AS '提分' FROM `result`
  • 数据库中的表达式：文本值，列，函数，计算表达式，系统变量…
  • 如 100*3-1 是计算表达上
select 表达式 from 表

1.5 where 条件字句

  • 作用：检索数据中心符合条件的值
  • 搜索的条件由一个或者多个表达式组成！ 结果 布尔值
  1. 逻辑运算符
    and &&
    or ||
    not ！
-- and
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult`>=95 AND`studentresult`<=100

-- 模糊查询（区间）
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult` BETWEEN 95 AND 100
-- c除了1000号学生以外的同学
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentno`!=1000
  1. 模糊查询
运算符语法描述
IS NULLA IS NULL如果操作符为null，结果为真
IS NOT NULLA IS NOT NULL如果操作符不为null，结果为真
BETWEENA BETWEEN B AND C若a在b和c之间，结果为真
LIKEA LIKE BSQL匹配，如果A匹配B，则结果为真%若干字符，_一个字符
INA IN(A1,A2,A3…)假设a在a1，或者a2.。其中一个值中，结果为真
-- 模糊查询（区间）
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult` BETWEEN 95 AND 100
-- c除了1000号学生以外的同学
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentno`!=1000

-- like 结合 %（代表0到任意个字符）  ——（代表只有一个字符） 
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '%强'

-- ====== in ====
-- 查询1001,1002,1003 的学员
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentno` IN (1001,1002,1003)

-- ========= null  not null =========
查询地址为空的学生 NULL ' '
SELECT `studentno`,`studentname` FROM `student`
WHERE address='' OR address IS NULL

-- ====== 查询有出生日期的同学  不为空
SELECT `studentno`,`studentname` FROM `student`
WHERE `borndate` IS NOT NULL

在这里插入图片描述
在这里插入图片描述

1.6 联表查询

在这里插入图片描述

- JOIN 对比

  1. 例1：
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student AS s
INNER JOIN result AS r
WHERE s.`studentno`=r.`subjectno`

在这里插入图片描述
2. 例2：

-- Right join 
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` s
RIGHT JOIN `result` r
ON s.`studentno`=r.`studentno`

在这里插入图片描述

  • 显示所有result匹配的值
  1. 例3：
-- left join
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` s
LEFT JOIN `result` r
ON s.`studentno`=r.`studentno`

在这里插入图片描述

  • 显示坐标中所有匹配的值

  1. 例4：
    在这里插入图片描述

  2. 例5：
    在这里插入图片描述

  3. 例6
    在这里插入图片描述

操作描述
Inner join如果表中至少有一个匹配，就返回行
left join会从左表中返回所有的值，即使右表中没有匹配
right join会从右表中返回所有的值，即使右表中没有匹配
  • join（连接的表） on（判断条件） 连接查询
  • where 等值查询
-- = 
-- 多表查询
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
RIGHT JOIN `result` r    -- 参加了考试的
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`

'-- 我要查询哪些数据 select 。。。
-- 从那几个表中查 FROM 表 XXX join 连接的表 on 交叉条件
-- 假设存在一种多张表查询，慢慢来，先查询两张表然后再慢慢增加·'

1.7 自连接

  • 自己的表和自己的表连接，核心：一张表拆为两张一样的表即可
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述
'查询父子信息，把一张表看成两个一模一样的表'
SELECT a.`categoryname` AS '父栏目', b.`categoryname` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid` = b.`pid`

1.8 分页和排序

  • 排序 ：升序ASC 降序 DESC
  • 排序：环节数据库压力，给用户更好的体验，瀑布流
  • 语法：order by 通过哪个字段排序，怎么排
SELECT `studentresult`
FROM `result` 
ORDER BY `studentresult` ASC
  • 瀑布流：百度图片，图片拉不到底，不断加载
  • 语法：limit(查询起始下标，pageSize)
-- 分页，每页只显示五条内容
-- 语法：limit 当前页，页面的大小
SELECT `studentresult`
FROM `result` 
ORDER BY `studentresult` ASC
LIMIT 1,5
-- 第一页  limit 0,5 (1-1)*5
-- 第二页  limit 5,5 (2-1)*5
-- 第三页  limit 10,5 (3-1)*5
-- 第N页   limit 0,5  (n-1)*pageSize,
-- [pageSize:页面大小]
-- [(n-1)*pageSize:起始值]
-- [n:当前页]
-- 【数据总数/页面大小 = 总页数】

例子

在这里插入图片描述

1.9 子查询

where（值固定，这个值是计算出来的）

  • 本质：在where语句中嵌套一个子查询语句
-- 1.查询 数据库结构-1 的所有考试结果 （学号，科目编号，成绩），降序排列
-- 方式一：使用连接查询
SELECT `studentno`,r.`subjectno`,`studentresult`
FROM `result` r
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname`='C语言-1'
ORDER BY `studentresult` DESC

-- 方式二：使用子查询（由里到外)
SELECT `studentno`,`subjectno`,`studentresult`
FROM `result` 
WHERE `subjectno` = (
       SELECT `subjectno` FROM `subject`
       WHERE `subjectname` = 'C语言-1'
)
ORDER BY `studentresult` DESC

例子：

在这里插入图片描述
在这里插入图片描述

2.0 函数

2.1 常用函数

官方文档

-- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4)  -- 向下取整
SELECT RAND()  -- 返回一个0-1的整数 
SELECT SIGN(10) -- 判断一个参数的符号 （正数1，负数-1,0 0）
-- 字符串函数
SELECT CHAR_LENGTH ('即使再小的帆也能远航')  -- 字符串长度
SELECT CONCAT ('我','爱','你')  -- 拼接字符串
SELECT INSERT ('我爱编程hello word',1,2,'超级热爱') -- 从某个位置替换某个长度
SELECT LOWER('I Love You') -- 小写字母
SELECT UPPER('I Love You') -- 大写字母
SELECT INSTR('kuangshen','h') -- 返回第一次出现索引的位置
SELECT REPLACE('坚持就能成功','坚持','努力') -- 替换出现的字符
SELECT SUBSTR('坚持就能成功',4,6)  -- 返回指定的字符串 	
SELECT REVERSE('坚持就能成功') -- 反转字符串
-- 时间和日期函数（记住）
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE()  -- 获取当前日期
SELECT NOW()  -- 获取当前时间
SELECT LOCALTIME() -- 本地时间
SELECT SYSDATE()  -- 系统时间
SELECT YEAR(NOW()) -- 月日时分秒
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())

-- 系统
SELECT SYSTEM_USER() -- 用户
SELECT VERSION() -- 版本

2.1 聚合函数

函数名称描述
count()计数
sum()求和
AVG()平均值
MAX()最大值
MIN()最小值
…

在这里插入图片描述

2.2 分组过滤

-- 查询不同课程的平均分，最高分，最低分，平均分大于80
-- 核心：根据不同的课程分组
SELECT `subjectname`,AVG(`studentresult`) AS 平均分,MAX(`studentresult`) AS 最高分,MIN(`studentresult`) AS 最低分
FROM `result` r
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
GROUP BY r.`subjectno` -- 通过什么字段来分组
HAVING 平均分>70

在这里插入图片描述
在这里插入图片描述

2.3 数据库级别的MD5加密

什么是MD5？
主要增强算法复杂度和不可逆性 MD5不可逆，具体值的 md5 是一样的
md5破解网站的原理，背后有一个字典，MD5
加密后的值，加密前的值

-- ===========测试md5加密========
CREATE TABLE `testmd5`(
`id` INT(4) NOT	NULL,
`name` VARCHAR(20) NOT NULL,
`pwd` VARCHAR(50) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 明文密码
INSERT INTO `testmd5` VALUES(1,'zhangsan','123456'),(2,'lisi','123456'),(3,'wangwu','123456')

-- 加密
UPDATE `testmd5` SET pwd=MD5(pwd) WHERE id = 1
UPDATE `testmd5` SET pwd=MD5(pwd) WHERE id = 2

-- 插入时加密
INSERT INTO `testmd5` VALUES(4,'xiaoming',MD5('123456'))

-- 如何校验：将用户传递进来的密码，进行MD5加密，然后比对加密后的值
SELECT * FROM `testmd5` WHERE `name` = 'xiaoming' AND pwd = MD5('123456')

3.0 事务

3.1 什么是事务

将一组sql放在一个批次中执行

  • 事务原则：ACID 原则 原子性，一致性，隔离性，持久性 （脏读，幻读…)
  1. 原子性：要么都成功，要么都失败
  2. 一致性：事务前后都保持一致，1000
  3. 持久性：事务一旦提交则不可逆，被持久化到数据库中
  4. 隔离性：多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他操作事务的操作数据所干扰的事务之间要像雾隔离 事务的隔离级别
  • 脏读：一个事务读取了另一个事务未提交的数据
  • 不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同（这个不一定是错误，只是某些场合不对）
  • 虚读（幻读）：是指一个事务内读取了别的事务插入的数据，导致前后读取的不一致
    在这里插入图片描述
-- ===========事务 ===========
-- mysql 是默认开启事务自动提交的
SET autocommit = 0 /* 关闭 */
SET autocommit = 1 /* 开启（默认) */

-- 手动处理事务
SET autocommit = 0 -- 关闭自动提交

-- 事务开启
START  TRANSACTION -- 标记一个事务的开始，从这个之后的sql都在同一个事务内
INSERT xx
INSERT xx
-- 提交: 持久化（成功！)
COMMIT 
-- 回滚：回到原来的样子（失败！）
ROLLBACK 
-- 事务结束
SET autocommit = 1 -- 开启自动提交

-- 了解
SAVEPOINT 保存点 名  -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 撤销保存点名

– 模拟转账

CREATE DATABASE  `shop` CHARACTER SET utf8 COLLATE utf8_general_ci  -- 创建一个数据库
USE shop
CREATE TABLE `account`(
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`money` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO account(`name`,`money`)
VALUES ('A',2000.0),('B', 10000.00)

-- 模拟转账：事务
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION -- 开启一个事务（一组事务）

UPDATE account SET money=money-500 WHERE `name`='A' -- A-500
UPDATE account SET money=money+500 WHERE `name`='B' -- B+500

COMMIT; -- 提交事务
ROLLBACK; -- 回滚

SET TRANSACTION = 1; -- 恢复默认值

在这里插入图片描述

当未提交事务时，回滚，恢复原来的值
提交事务后，再回滚则不再变化

4.0 索引

1.0

  • 主键索引：（primary key） 唯一的标识，主键不可重复，只能有一个列作为主键
  • 唯一索引：（unique key）避免重复的列出现，唯一索引可以重复，多个列都可以表示位 唯一索引
  • 常规索引：（key/index）默认的，index，key 关键字来设置
  • 全文索引：（fullText）再特定的数据库引擎下才有，MyISAM，快速定位数据
-- 索引的使用
-- 1.在创建表的时候给字段增加索引
-- 2.创建完毕后，增加索引

-- 显示所有的索引信息
SHOW INDEX FROM student
-- 增加一个全文索引（索引名） 列名
ALTER TABLE school.student add FULLTEXT INDEX `studentName`(`studentName`)
-- EXPLAIN 分析sql执行的情况
EXPLAIN select * from student; -- 非全文索引
EXPLAIN select * from student where match(studentName) against('刘');

1.2 测试索引

DELIMITER $$
-- 写函数之前必须要写，标志
CREATE FUNCTION mock_data ()
RETURNS INT
BEGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;
	WHILE i<num DO
		INSERT INTO `app_user`(`name`,`eamil`,`phone`,`gender`)VALUES(CONCAT(用户,i),19224305@qq.com,123456789,FLOOR(RAND()*2));
		SET i=i+1;
	END WHILE;
	RETURN i;
END;

SELECT mock_data() -- 执行此函数 生成一百万条数据

SELECT * FROM app_user WHERE `name` = '用户999'; -- 0.993 sec

EXPLAIN SELECT * FROM app_user WHERE `name`='用户999';

-- id_表明_字段名
-- create index 索引名 on 表（字段）
CREATE INDEX id_app_user_name ON app_user(`name`);-- 创建一个常规索引
SELECT * FROM app_user WHERE `name` = '用户999'; -- 0.001sec
explain select * from app_user where `name`='用户999';
在这里插入图片描述

1.3 索引原则

  • 索引不是越多越好
  • 不要对进程变动数据加索引
  • 小数据量的表不需要加索引
  • 索引一般加在常用来查询的字段上
  • 索引的数据结构：详细
    Hash类型的索引
    Btree：innoDb的默认数据结构

5. SQL练习

借鉴：https://blog.csdn.net/mrbcy/article/details/68965271

request小牛
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
.NET Entity Framework(EF)使用SqlQuery直接操作SQL查询语句或者执行过程
weixin_33700350的博客
03-29 1587
Entity Framework是微软出品的高级ORM框架，大多数.NET开发者对这个ORM框架应该不会陌生。本文主要罗列在.NET(ASP.NET/WINFORM)应用程序开发中使用Entity Framework直接执行SQL语句或者存储过程的一些代码片段。具体请见以下正文： 1.使用SqlQuery在已知的实体上执行SQL查询语句 using (var context = new M...
SQL基本语句（整理）
热门推荐
weixin_43294936的博客
03-04 13万+
一、DDL(Data Definition Language) 数据定义语言，用来定义数据库对象（数据库，表，字段） ①查询 查询所有数据库 show databases; 查询当前数据库 select database(); ②创建 create database [if not exists] 数据库名 [default charset 字符集][collate 排序规则]; #中括号里的可加可不加，具体情况而定 #第一个是如果不存在相同名称的数据库则创建 #..
SQL基本
qq_44230959的博客
05-04 798
1. 数据库SQL 创建数据表 create detabase <数据库文件名> [ON] //数据库参数 [LOG ON] 数据库日志文件参数 ([name=<逻辑文件名>, filename=<物理文件名>, size=<初始值大小>, maxsize= <可增长最大值>, filegrowth=<增长比例>]) 修改数据表 alter database &l
ElasticSearch 7.3 实战：ES7.X SQL新特性解析及使用Java api实现sql功能
最新发布
qq_33240556的博客
03-28 474
请注意，实际的API用法可能会根据Elasticsearch官方发布的具体版本有所不同，请查阅Elasticsearch 7.3的相关文档以获取最新和详细的API使用方法。同时，对于SQL新特性的完整列表和具体用法，建议直接查阅Elasticsearch官方文档。在Elasticsearch 7.x版本中，对SQL的支持得到了增强，使得用户可以直接使用SQL查询语法来操作Elasticsearch中的数据。
Hibernate SQLQuery原生sql查询
拉轰的大蜗牛
12-18 2万+
hibernate的查询方式有HQL,Cretiria和SQL. hql查询面向对象的语句风格确实要简洁一些(只是那么一点), cretiria本人目前没在项目中使用过. 有一半的时候, 我在Hibernate框架中使用的是SQL查询, 简单,可控, 原生. 使用起来风格原始,简单有效, 不增加学习成本,且利于多人开发的维护. Hibernate提供一个接口SQLQuery, 用于支持原生sql语句查询.
sql基本用法
09-02
数据库操作的sql用法，很齐全，可以直接使用。
易语言SQL基本语句模块
07-24
易语言SQL基本语句模块源码,SQL基本语句模块,SQL_查询,SQL_添加,SQL_更新,SQL_删除,SQL_删除表,SQL_清空表,SQL_取记录数,SQL_取平均值,SQL_取最小值,SQL_取最大值,SQL_取首记录字段值,SQL_取尾记录字段值,SQL_取总计...
SQLQuery的简单使用及部分符号解释
qq_41362215的博客
04-12 6566
Session session = null; List<UserTable> list = null; try { session = this.openSession(); String sql = "select {u.*} from user_table u " + "where u.project_id = :projectId)"; SQLQuery query = session.createSQLQuery(sql); query.a..
在PL/SQL中提交Request
01-05 167
declare l_request_id number; p_message varchar2(255); v_app varchar2(30); v_name varchar2(...
sqlserver关于session和request表
hzx11的博客
07-03 1673
session这三个参数很重要select session_id,status,host_name from sys.dm_exec_sessionsrequest这三个参数很重要select session_id,status,sql_handle,plan_handle from sys.dm_exec_requestssql_handle与plan_handle的用法select * fro...
在SQL中Connection, Session, 和Request三个概念的区别
weixin_34409822的博客
12-20 303
一般情况下, Connection就可以看做Session. 一个connection对应多个session的情况非常少见. 一个Session中可以有多个request, 比如说一个查询语句, 一次存储过程的调用都是request.   下面的语句可以分别从系统表中查询到当前的connection, sessions, 和requests.select * from sys.dm_exec...
Python——Requests库主要方法解析——request函数
Noob_Zhou的博客
03-15 4748
（一）request()方法**kwargs 13个控制访问参数params: 字节或字节序列，作为增加到url中的参数2.data3.json(http协议最经常使用的数据格式）4.head（可模拟任何我们想要模拟的浏览器向服务器发起访问）代表了向某url发起访问时的http的头字段定制协议头5.cookiesfiles 向链接提供文件https 使用代理服务器进行爬虫，隐藏用户原ip地址htt...
request方法
weixin_33922672的博客
05-15 1312
2019独角兽企业重金招聘Python工程师标准>>> ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值