MySQL数据库常见SQL语句宝典

news/2024/9/24 18:29:43/

一 、常用操作数据库的命令

 1.查看所有的数据库 : show databases;2.创建一个数据库   : create database if not exists 数据库名;3.删除一个数据库   : drop database if exists 数据库名;4.选择一张表 (注意在建表之前必须要选择数据库) : use `表名`;* --tab 键的上面，如果你的表名或字段名是一个特殊字段符，就需要带 ``  *5.在选中的数据库之中查看所有的表 :  show tables;6.查看创建数据库的语句           ：show create database 数据库名;7.查看student数据表的定义语句    ：show create table 表名;8.显示表的结构                   ：desc 表名;9.删除表                        :  drop table 表名;10.查看创建库的详细信息          ：show create database 库名;11.查看创建表的详细信息          : show create table 表名;

二、建一张表

– 目标:创建一个schoo1数据库
– 创建学生表(列,字段)使用SQL 创建
– 学号int 登录密码varchar(20)姓名,性别varchar(2),出生日期(datatime)，家庭住址，emai1

分析：
id INT(4) NOT NULL AUTO_INCREMENT COMMENT ‘学号’,
id ：字段名 （要加个票 Tab键下面的 ）
INT(4)：类型(长度)
NOT NULL：不为空
AUTO_INCREMENT：自增
COMMENT ‘xxx’：注释 （这里 ’ ’ 是回车键隔壁的那个 ‘ ）
DEFAULT ‘xxx’：默认值xxx

-- 注意点，使用英文()，表的名称 和 字段 尽量使用 `` (Tab键下面的) 括起来
-- AUTO_ INCREMENT 自增
-- 字符串使用单引号括起来!
-- 所有的语句后面加，(英文的)，最后一个不用加
-- PRIMARY KEY 主键，一般一个表只有一个唯一的主键!CREATE TABLE IF NOT EXISTS `student2`(`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',`name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT'密码',`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT'性别',`birthday` DATETIME DEFAULT NULL COMMENT'出生日期',`address` VARBINARY(100) DEFAULT NULL COMMENT'家庭住址',`email` VARBINARY(50) DEFAULT NULL COMMENT'邮箱',PRIMARY KEY(`id`) --设置主键
)ENGINE=INNODB DEFAULT CHARSET=utf8  --设置编码

格式

CREATE TABLE [IF NOT EXISTS] `表名`（`字段名` 列类型[属性][索引][注释],`字段名` 列类型[属性][索引][注释],`字段名` 列类型[属性][索引][注释] (最后一句可以不要逗号)
）[表类型][表的字符集设置][注释]``

常用命令
SHOW CREATE DATABASE school – 查看创建数据库的语句


SHOW CREATE TABLE student – 查看student数据表的定义语句


DESC student – 显示表的结构

三、修改表的命令

3.1 修改

修改

 1.  修改表名:  alter  table  旧表名  rename  as  新表名例子：alter  table  teacher  rename as teacher12.  增加表的字段:  alter  table  表名  add  表字段名  字段类型例子：alter table teacher1 add age int(11)3.   修改表的字段的类型--alter table 表名 modify 字段名 字段类型[ ]例子：alter table teacher1 moify age varchar(11)  -- 修改约束(例如：由int修改为varchar类型)4.   修改指定的字段名--alter table 表名 change 旧名字  新名字  字段类型[ ]例子：alter table teacher1 change age age1 int(1) -- 字段重命名5.  删除表的字段 : alter table 表名 deop 字段名例子：alter table teacher1 drop age1    6.  添加字段并指定位置  alter table 表名 add 字段 字段类型   after 字段

3.2 删除

删除

1.   删除表（如果表存在再删除）例子：drop table if exists teacher1

四、DML 数据库操作语言（重要）

4.1 添加

添加 insert

语法：
insert into 表名 (`字段名1`,`字段名2`,`字段名3`,……)  values (`值1`,`值2`,`值3`, ……)

还可以同时插入多条数据，VALUES后面的值需要使用，隔开即可
语法：
insert into 表名 (`字段名1`,`字段名2`,`字段名3`,……)  values (`值1`,`值2`,`值3`, ……),(`值1`,`值2`,`值3`, ……),……

例子
-- 一般写插入语句，我们一定要数据和字段一一对应。
-- 插入多个字段
INSERT INTO `grade`(`gradename`) VALUES ('大二'),('大一');--单独只插入一个字段
INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES ('张三','aaaaa','男')--可以同时插入多条数据，VALUES后面的值需要使用，隔开即可
INSERT INTO `student`(`name`,`pwd`,`sex`) 
VALUES ('李四','aaaaa','男'),('王五','23232','女')

4.2 修改

修改：

update 修改谁（条件） set 原来的值=新值

语法：
update 表名 set `字段名1` = '值1' ,`字段名2` = '值2' … where [条件]

-- 修改学员名字UPDATE `student` SET `name`='囷' WHERE id =1;
-- 不指定条件的情况下，会改动所有表
UPDATE `student` SET `name`='233'-- 语法；
-- UPDATE 表名 set column_name,[] = value where 条件

条件：where 子句 运算符 id 等于 某个值，大于某个值，在某个区间内修改

操作符返回布尔值


注意：

  • column_name 是数据库的列，带上
  • 条件，是筛选的条件，如果没有指定，则会修改所有的列
  • value 是一个具体的值，也可以是一个变量
  • 多个设置的属性之间，使用英文逗号隔开
UPDATE `student` SET `birthday`=CURRENT_TIME where `name`='李四' AND SEX = '男'

4.3 删除

delete 命令

语法  delete from 表名 [where 条件]

-- 删除数据 (避免这样写,会删除所有的数据)
DELETE FROM `student`-- 删除指定
DELETE FROM `student` where id= 1

TRUNCATE 命令

作用：完全清空一个数据库，表的结构和索引不会变

delete 和 truncate 区别

  • 相同点： 都能删除数据，都不会删除表结构
  • 不同：
    - TRUNCATE 重新设置自增列 计数器会归零
    - TRUNCATE 不会影响事务
测试代码：-- 测试delete 和 truncate 区别CREATE TABLE `test`(
`id` INT(4) NOT NULL AUTO_INCREMENT,
`coll` VARCHAR(20) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8INSERT INTO `test`(`coll`) VALUES('1'),('2'),('3')DELETE FROM `test` -- 不会影响自增TRUNCATE TABLE `test` -- 自增会归零

了解即可：delete删除的问题 重启数据库，现象

  • innoDB 自增列会从1开始（存在内存当中，断电即失）
  • MyISAM 继续从上一个自增量开始（存在文件中，不会丢失）

五、DQL查询数据（最重点）

5.1 DQL

(Data Query Language) :数据查询语言

  • 所有的查询操作都用它 Select（select 选择）
  • 简单的查询，复杂的查询它都能做
  • 数据库中最核心的语言，最重要的语句
  • 使用频率最高的语言

 

5.2 指定查询字段

1. 查询全部的学生：select *(通配符) from 表名例子：select * from student2.查询指定字段：select  `字段1`,（英文逗号隔开）`字段2`,…   from 表例子：select `studentNo` , `studentName` from student3.别名，给查询结果表头起一个名字  as  可以给字段起别名，也可以给表起别名例子：selsct `studentNo` as 学号 , `studentName` as 学生名字 from student as s4.函数 Concat（a,b）给查出来的表添加字体例子：select concat('姓名：' ,StudentName) as 新名字 from student

 

去重(将查询出来重复的数据去除掉)

  语法：在select后面加上一个distinctselect distinct `字段名` from 表名

 

数据库的列 (表达式)

select 表达式 from 表

5.3where 条件子句

作用：检索数据中符合条件的值

逻辑运算符

运算符语法结果
and &&a and b , a&&b逻辑与,两个都为真，结果为真
or ||a or b , a||b逻辑或，其中一个为真，则结果为真
Not !=not a ,!a逻辑非，真为假，假为真！

尽量使用英文

题目：查询考试成绩在95分到100分之间
方式一：
SELECT `StduentNo`,`StudentResult` FROM result
WHERE StudentResult >=95 AND StudentResult<=100方式二：
-- 模糊查询（区间）
SELECT `StduentNo`,`StudentResult` FROM result
WHERE StudentResult BETWEEN 95 AND 100方式三：
-- 除了100分学生之外的同学成绩
SELECT `StduentNo`,`StudentResult` FROM result
WHERE  StudentResult != 100方式四： !=  not
SELECT `StduentNo`,`StudentResult` FROM result
WHERE NOT StudentResult = 100

模糊查询：比较运算符

运算符语法描述
IS NULLa is null如果操作符为null 结果为真
IS NOT NULLa is not null如果操作符为not null 结果为真
BETWEENa between b and c若a在b 和 c之间则为真
LIKEa like bSQL匹配，如果a 匹配到b 则为真
INa in (a1,a2,a3…)假设a 在 a1,a2,a3其中的某一个中，为真
==================like （% 和 _ 只能用在like中）===========================
--  查询姓刘的同学
-- like结合 %（代表0到任意字符）  _(一个字符)
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '刘%';-- 查询姓刘的同学，名字后只有一个字
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '刘_';-- 查询姓刘的同学，名字后只有两个字
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '刘__';-- 查询名字中间有嘉字的同学 %嘉%
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '%嘉%';===================IN(具体的一个或者多个值)===========================
-- 查询1001 1002 1003 学员信息
之前的方法：
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo = 1001
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo = 1002
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo = 1003用in来操作的方法：
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo IN (1001,1002,1003);-- 查询在北京的学生（注意in查询的是一个具体的值才能查询出来）
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `Address` IN('安徽','河南洛阳');===================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;

5.4 联表查询

JOIN 对比

 

操作描述
Inner join如果表中至少有一个匹配，就返回行
left join即使左表中没有匹配，也会从左表中返回所有的值
right jion即使右表中没有匹配，也会从右表中返回所有的值

两表查询

语法：
select 别名.共同字段,字段1, 字段2,字段3……
from 表1 as(as可以省略) 别名1
(inner\left\right) join 表2 as 别名2
(where\on) 别名1.共同字段 = 别名2.共同字段 

/*
1. 分析需求，分析查询的字段来自哪些表
2.确定使用哪种连接查询？7种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件： 学生表中 studentNo = 成绩表中 studentNo 
-- JION（表） ON （判断的条件）连接查询
-- where 等值查询
SELECT studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
INNER JOIN result AS r
WHERE s.studentNo=r.studentNo--Right Join
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentNo = r.studentNo--LEFT Join
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentNo = r.studentNo

有条件的联表查询

语法：
select 别名.共同字段,字段1, 字段2,字段3……
from 表1 as(as可以省略) 别名1
(inner\left\right) join 表2 as 别名2
on 别名1.共同字段 = 别名2.共同字段 
where 条件
and 条件2 and 条件3……

查询没有考试的同学
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentNo = r.studentNo
WHERE StudentResult IS NULL

三表或多表查询

语法：
select 别名.共同字段,字段1, 字段2,字段3……
from 表1  别名1
(inner\left\right) join 表2  别名2
on 别名1.共同字段1 = 别名2.共同字段1
(inner\left\right) join 表3  别名3
on 别名1.共同字段2 = 别名3.共同字段2 
 查询了参加考试同学的信息：学号：学生姓名：科目名：分数
SELECT s.`studentNo`,`studentName`,`SubjectName`,`studentResult`
FROM student s
RIGHT JOIN result r
ON r.studentNo=s.studentNo
INNER JOIN `subject` sub
ON r.SubjectNo=sub.SubjectNo-- 我要查询哪些数据 SELECT ....
-- 从哪几个表中查 FROM 表 xxx JOIN 连接的表 ON 交叉条件
-- 假设存在一中多张表查询，先查询两章表，然后再慢慢增加FROM a LEFT JOIN b   左为准
FROM a RIGHT JOIN b	右为准

5.5 分页和排序

============================分页 limit 和排序order by=================-- 排序：  升序ASC  降序  DESC
SELECT  xx
FROM xx
JOIN xx
WHERE  xx
ORDER BY  xx
ASC   ||  DESC

排序

语法：order by 通过那个字段排序 怎么排
order by 字段名 asc（升序）\desc（降序）

分页

语法： limit 起始项，每页的数量

-- 为什么要分页
-- 缓解数据库压力，给人的体验更好-- 分页，每页显示五条数据-- 语法： limit 当前页，页面的大小
-- limit 0,5 1-5
-- limit 1,5 1-5
-- limit 6,5
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM student s
INNER JOIN `result` r
ON s.`StudentNo`=r.`StudentNo`
INNER JOIN `subject` sub
ON r.`subjectNo`=sub.`subjectNo`
WHERE subjectName='数据结构-1'
ORDER BY StudentResult ASC
LIMIT 0,5-- 第一页 limit 0,5
-- 第二页 limit 5,5
-- 第三页 limit 10,5
-- 第N页 limit 5*（n-1）,5

5.6 子查询

where（这个值是计算出来的，即不确定的），之前的where后面是跟一个固定的值
本质：在where语句中嵌套一个子查询语句

where （select * from）
解析：where 后加( ) 括号里面放新的查询语句

-- ===========================where=========================-- 1.查询 数据库结构-1的所有考试结构（学号，科目编号，成绩） 降序
-- 方式一： 连接查询
SELECT `StudentNo`,r.`SubjectName`,`StudentResult`
FROM `result` r
INNER JOIN `subject` sub
ON r.SubjectNo = sun.SubjectNo
WHERE subjectName = '数据库结构-1'
ORDER BY StudentResult DESC-- 方式二：使用子查询(由里及外)
SELECT `StudentNo`,r.`SubjectName`,`StudentResult`
FROM `result`
WHERE StudentNo=(SELECT SubjectNo FROM  `subject` WHERE SubjectName = '数据库结构-1'
)
ORDER BY StudentResult DESC-- 分数不少于80分的学生的学号和姓名
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON r.StudentNo = s.StudentNo
WHERE StudentResult>=80-- 在这个基础上 增加一个科目 ，高等数学-2
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON r.StudentNo = s.StudentNo
WHERE StudentResult>=80 AND `SubjectNo`=(SELECT Subject FROM `subject`WHERE SubjectName='高等数学-2'
)-- 查询课程为 高等数学-2 且分数不小于80分的同学的学号和姓名
SELECT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON s.StudentNo = r.StudentNo
INNER JOIN `subject` sub
ON r.`SubjectName`='高等数学-2'
WHERE `SubjectaName`='高等数学-2' AND StudentResult >=80-- 再改造 (由里即外)
SELECT `StudentNo`,`StudentName` FROM student
WHERE StudentNo IN(
SELECT StudentNo result WHERE StudentResult >80 AND SubjectNo =(
SELECT SubjectNo FROM `subject` WHERE `SubjectaName`='高等数学-2'
)
)

六、MySQL函数

6.1 常用函数

 数学运算SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4)  -- 向下取整
SELECT RAND() -- 返回0-1随机数
SELECT SIGN(-10) -- 判断一个数的符号 0-0 负数返回-1 正数返回1-- 字符串函数
SELECT CHAR_LENGTH('即使再小的帆也能远航') -- 返回字符串长度
SELECT CONCAT('我','爱','你') -- 拼接字符串
SELECT INSERT('我爱编程helloword',1,2,'超级热爱') -- 从某个位置开始替换某个长度 ：1为替换的开启下标，2为替换的字字符的长度   结果为：超级热爱编程helloword
SELECT UPPER('Abc') --小写字母
SELECT LOWER('Abc')  --大写字母
SELECT INSTR('kuangshen','h')  -- 返回第一次出现的字符串的索引
SELECT REPLACE('坚持就能成功','坚持','努力')  -- 替换出现的指定字符串
SELECT SUBSTR('狂神说坚持就能成功',4,6)  --返回指定的子字符串（源字符串，截取的位置，截取的长度）
SELECT REVERSE('清晨我上马')  -- 反转-- 查询姓 周 的同学 ，改成邹
SELECT REPLACE(studentname,'周','邹') FROM student
WHERE studentname LIKE '周%'-- 时间跟日期函数（记住）
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE() -- 获取当前日期
SELECT NOW() -- 获取当前日期
SELECT LOCATIME()  -- 本地时间
SELECT SYSDATE()  -- 系统时间SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())-- 系统
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()

6.2 聚合函数（常用）

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

56fdf9097c484094bc7ed22a33b773b8.png
997e575a5e1646a7a06c60e5a6e62ed9.png
0f93e87e71b14e098d65aa1062992af6.png

分组

group by

分组后有条件判断用

naving 条件​​​​​​​

6.3 数据库级别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,'张三','123456'),(2,'李四','123456'),(3,'王五','123456')-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id =1
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id !=1  -- 加密全部-- 插入时加密INSERT INTO testmd5 VALUES(4,'小明',MD5('123456'))
INSERT INTO testmd5 VALUES(5,'红',MD5('123456'))-- 如何校验，将用户传递过来的密码，进行MD5加密，然后对比加密后的值
SELECT * FROM testmd5 WHERE `name`='红' AND pwd=MD5('123456')

明码
6a5370e9d1284196b7a00365043e444c.png
MD5加密后

插入数据的时候就加密：​​​​​​​

     感谢大家的阅读，觉得有帮助的朋友们点点赞！


http://www.ppmy.cn/news/1450552.html

相关文章

SQL Sever无法连接服务器

SQL Sever无法连接服务器

SQL Sever无法连接服务器&#xff0c;报错证书链是由不受信任的颁发机构颁发的 解决方法&#xff1a;不用ssl方式连接 1、点击弹框中按钮“选项” 2、连接安全加密选择可选 3、不勾选“信任服务器证书” 4、点击“连接”&#xff0c;可连接成功
阅读更多...
LabVIEW机械臂控制与图像处理示教平台

LabVIEW机械臂控制与图像处理示教平台

LabVIEW机械臂控制与图像处理示教平台 随着工业自动化技术的快速发展&#xff0c;工业机器人在制造业中的应用越来越广泛&#xff0c;它们在提高生产效率、降低人工成本以及保证产品质量方面发挥着重要作用。然而&#xff0c;传统的工业机器人编程和操作需要专业知识&#xff…
阅读更多...
如何用 Redis 实现延迟队列？

如何用 Redis 实现延迟队列？

延迟队列是一种常见的消息队列模式&#xff0c;用于处理需要延迟执行的任务或消息。Redis 是一种快速、开源的键值对存储数据库&#xff0c;具有高性能、持久性和丰富的数据结构&#xff0c;因此很适合用于实现延迟队列。在这篇文章中&#xff0c;我们将详细讨论如何使用 Redis…
阅读更多...
关于用户体验和设计思维

关于用户体验和设计思维

介绍 要开发有效的原型并为用户提供出色的体验&#xff0c;了解用户体验 (UX) 和设计思维的原则至关重要。 用户体验是用户与产品、服务或系统交互并获得相应体验的过程。 设计思维是一种解决问题的方法&#xff0c;侧重于创新和创造。 在启动期实现用户体验和设计思维时&#…
阅读更多...
亲子公园剧本杀小程序ar互动寻宝游戏开发搭建

亲子公园剧本杀小程序ar互动寻宝游戏开发搭建

开发亲子公园剧本杀小程序AR互动寻宝游戏&#xff0c;你需要考虑以下几个步骤&#xff1a; 1. 需求分析&#xff1a;明确游戏的功能和玩法&#xff0c;确定AR技术应用的需求。 2. 设计与规划&#xff1a;根据需求设计游戏场景、角色、道具和交互方式等&#xff0c;并规划技术…
阅读更多...
自定义表单元素组件内容变化触发ElForm重新校验

自定义表单元素组件内容变化触发ElForm重新校验

对于下图中“付费类型”怎么实现有很多种方式&#xff0c;我能想到的是以下两种&#xff1a; Element Plus的RadioButton自定义组件 1. RadioButton 它本质上就是一个单选组件&#xff0c;它跟Element Plus的RadioButton本质上没有区别&#xff0c;无非是外观上的差别。那么…
阅读更多...
SpringBoot配置HTTPS及开发调试

SpringBoot配置HTTPS及开发调试

前言 在实际开发过程中&#xff0c;如果后端需要启用https访问&#xff0c;通常项目启动后配置nginx代理再配置https&#xff0c;前端调用时高版本的chrome还会因为证书未信任导致调用失败&#xff0c;通过摸索整理一套开发调试下的https方案&#xff0c;特此分享 后端配置 …
阅读更多...
macOS asdf 工具版本管理器

macOS asdf 工具版本管理器

一、区别于Homebrew "asdf"和"Homebrew"都是用于管理软件包的工具&#xff0c;但它们的主要区别在于适用范围和管理的内容&#xff1a; 1.适用范围&#xff1a; asdf&#xff1a;是一个通用的版本管理工具&#xff0c;可以用来管理多种不同的软件工具和…
阅读更多...
最新文章

Copyright @ 2022~2023