Mysql基础（三）----- 查询

最新推荐文章于 2022-08-02 10:56:08 发布
最新推荐文章于 2022-08-02 10:56:08 发布
阅读量162 收藏
点赞数
分类专栏： 笔记 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/mangogogo321/article/details/106133074
版权

文章目录

一. 数据的操作

1. DML

  1. 更新数据
--更新用户名为4位的用户，让其年龄-3
UPDATE cms_user SET age=age-3 WHERE username LIKE '____';
--更新前三条记录，让已有年龄+10,更新或删除时不能用偏移量
UPDATE cms_user SET age=age+10 LIMIT 3;
--按照id降序排列，更新前3条
UPDATE cms_user SET age=age+10 ORDER BY id DESC LIMIT 3;
--删除用户性别为男的，按照年龄降序排列，删除前1条记录
DELETE FROM cms_user WHERE sex='male' ORDER BY age DESC LIMIT 1;

2. DQL

  1. ORDER BY 对查询结果排序
--按照id降序排列，默认是ASC
SELECT * FROM cms_iser ORDER BY id;
SELECT * FROM cms_iser ORDER BY id DESC;
--按照年龄升序排列
SELECT * FROM cms_user ORDER BY age ASC;
SELECT * FROM cms_user ORDER BY 1 DESC;
--按照多个字段排序
SELECT * FROM cms_user ORDER BY age ASC,id DESC;
SELECT id,age,sex,GROUP_CONCAT(username), COUNT (*) AS totalUsers
FROM cms_user
WHERE id>=2
GROUP BY sex
HAVING COUNT (*)>=2
ORDER BY age DESC,id ASC;
--随机提取记录
SELECT * FROM cms_user ORDER BY RAND();
  1. 通过limit限制显示条数
    (1) 显示条数
--查询表中前三条记录
SELECT * FROM cms_user LIMIT 3;
SELECT * FROM cms_user ORDER BY id DESC LIMIT 5;

（2）偏移量，显示条数

--查询表中前一条记录,第一个表示第几条记录开始，第二数表示显示多少条记录
SELECT * FROM cms_user LIMIT 1;
SELECT * FROM cms_user LIMIT 0,1;
SELECT * FROM cms_user LIMIT 1,1;
SELECT * FROM cms_user LIMIT 0,5;

Select完整形式

SELECT id,sex,age,GROUP_CONCAT(username),
COUNT (*) AS totalUsers,
MAX(age) AS max_age,
MIN(age) AS min_age,
AVG(age) AS avg_age,
SUM(age) AS sum_age
FROM cms_user
WHERE id>=1
GROUP BY sex
HAVING COUNT (*)>=2
ORDER BY age DESC
LIMIT 0,2;

二. 连接查询

1. 内连接查询

  1. JOIN|CROSS JOINNER
  2. 通过ON连接查询
  3. 显示两个表中符合连接条件的记录
--查询cms_user id,username
--provinces, proName
SELECT cms_user.id,username,proName FROM cms_user,provinces;

--cms_user的proId对应省份表中的id
SELECT cms_user.id,username,proName FROM cms_user,provinces
WHERE cms_id.proId=provinces.id;

--查询cms_user表中的id,username,email,sex
--查询provinces表中的proName
SELECT u.id,u.username,u.email,u.sex,p.proName
FROM cms_user AS u
INNER JOIN provinces AS p 
ON u.proId=p.id;

SELECT u.id,u.username,u.email,u.sex,p.proName
FROM provinces AS p 
CROSS JOIN cms_user AS u
ON u.proId=p.id;

--查询cms_user id,username,sex
--查询provinces  proName
--条件是cms_user的性别为男
SELECT u.id,u.username,u.email,u.sex,p.proName
FROM cms_user AS u
JOIN provinces AS p 
ON u.proId=p.id
WHERE u.sex='male';

--根据proName分组
SELECT u.id,u.username,u.email,u.sex,p.proName,COUNT(*) AS totalUsers,GROUP_CONCAT(username)
FROM cms_user AS u
JOIN provinces AS p 
ON u.proId=p.id
WHERE u.sex='male'
GROUP BY p.proName;

--对分组结果进行筛选，挑出组中人数大于等于1的
SELECT u.id,u.username,u.email,u.sex,p.proName,COUNT(*) AS totalUsers,GROUP_CONCAT(username)
FROM cms_user AS u
JOIN provinces AS p 
ON u.proId=p.id
WHERE u.sex='male'
GROUP BY p.proName
HAVING COUNT (*)>=1;

--按照id升序排列
SELECT u.id,u.username,u.email,u.sex,p.proName,COUNT(*) AS totalUsers,GROUP_CONCAT(username)
FROM cms_user AS u
JOIN provinces AS p 
ON u.proId=p.id
WHERE u.sex='male'
GROUP BY p.proName
HAVING COUNT (*)>=1
ORDER BY u.id ASC;

--  限制显示条数，前两条
SELECT u.id,u.username,u.email,u.sex,p.proName,COUNT(*) AS totalUsers,GROUP_CONCAT(username)
FROM cms_user AS u
JOIN provinces AS p 
ON u.proId=p.id
WHERE u.sex='male'
GROUP BY p.proName
HAVING COUNT (*)>=1
ORDER BY u.id ASC
LIMIT 0,2;

--查询新闻表中的id,title
--查询cms_cate中的cateName
SELECT n.id,n.title,c.cateName
FROM cms_news AS n 
JOIN cms_cate AS c
ON n.aId=c.id;

--查询cms_new id,title
--cms_admin username,role
SELECT n.id,n.title,a.username,a.role
FROM cms_news AS n 
JOIN cms_admin AS a 
ON n.aId=a.id;

--cms_news id,title
--cms_cate cateName
--cms_admin username,role
SELECT n.id,n.title,c.cateName,a.username,a.role
FROM cms_cate AS c 
JOIN cms_news AS n 
ON n.cId=c.id
JOIN 
cms_admin AS a 
ON n.aId=a.id;

2. 外连接查询

  1. 左外连接，显示左表中的全部记录及右表符合连接条件的记录
--左外连接，left join左边为主表
SELECT u.id,u.username,u.email,u.sex,p.proName
FROM cms_user AS u
LEFT JOIN provinces AS p
ON u.proId=p.id;
  1. 右外连接，显示右表中的全部记录及左表符合连接条件的记录
--右外连接
SELECT u.id,u.username,u.email,u.sex,p.proName
FROM cms_user AS u
RIGHT JOIN provinces AS p
ON u.proId=p.id;

3. 外键操作

  1. 保证数据一致性和完整性
    被参照的表是主表，外键所在的字段表为字表。
    父表对记录做记录时，字表中与之对应的信息也应有相应的改变。
    可以实现一对一或一对多的关系。
    外键列和参照列必须具有相似的数据类型。
    外键列和参照列必须创建索引。
--创建部门表department(主表)
--id,depName
CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR (20) NOT NULL UNIQUE
)ENGINE=INNODB;
INSERT department(depName) VALUES('教学部'),
('市场部'),
('运营部'),
('督导部');

--创建员工表employee（子表）
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR (20) NOT NULL UNIQUE ,
depId TINYINT UNSIGNED,
FOREIGN KEY (depId) REFERENCES department(id)
)ENGINE=INNODB;

INSERT employee(username,depId) VALUES ('king',1),
('queen',2),
('zhangsan',3),
('lisi',4),-加外键名称em_fk_dep
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR (20) NOT NULL UNIQUE ,
depId TINYINT UNSIGNED,
CONSTRAINT em_fk_dep FOREIGN KEY (depId) REFERENCES department(id)
)ENGINE=INNODB;

INSERT employee(username,depId) VALUES ('king',3),
('queen',2),
('zhangsan',3),
('lisi',4),
('wangwu',2);

--删除外键
ALTER TABLE employee DROP FOREIGN KEY emp_fn_dep;

--添加外键
ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department(id);

('wangwu',1);

--删除主表中的记录
--先删除子表中的属于1部门的人
DELETE FROM employee WHERE depId=1;
DELETE FROM department WHERE id=1;
  1. 添加删除外键
在这里插入代码片

(1) CASCADE

--创建部门表department(主表)
--id,depName
CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR (20) NOT NULL UNIQUE
)ENGINE=INNODB;
INSERT department(depName) VALUES('教学部'),
('市场部'),
('运营部'),
('督导部');

--父表中删除相应记录，字表中也会删除CASCADE
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR (20) NOT NULL UNIQUE ,
depId TINYINT UNSIGNED,
CONSTRAINT em_fk_dep FOREIGN KEY (depId) REFERENCES department(id) ON DELETE CASCADE
)ENGINE=INNODB;

INSERT employee(username,depId) VALUES ('king',3),
('queen',2),
('zhangsan',3),
('lisi',4),
('wangwu',2);

--删除部门表中的第一个部门
DELETE FROM department WHERE id=1;

-----------
CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR (20) NOT NULL UNIQUE
)ENGINE=INNODB;
INSERT department(depName) VALUES('教学部'),
('市场部'),
('运营部'),
('督导部');

--父表中删除相应记录，字表中也会删除CASCADE,能让父表更新，字表跟着更新
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR (20) NOT NULL UNIQUE ,
depId TINYINT UNSIGNED,
CONSTRAINT em_fk_dep FOREIGN KEY (depId) REFERENCES department(id) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=INNODB;

INSERT employee(username,depId) VALUES ('king',1),
('queen',2),
('zhangsan',3),
('lisi',4),
('wangwu',1);

UPDATE department SET id=id+10;

(2) SET NULL

--父表中删除相应记录，没有改变的值是NULL
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR (20) NOT NULL UNIQUE ,
depId TINYINT UNSIGNED,
CONSTRAINT em_fk_dep FOREIGN KEY (depId) REFERENCES department(id) ON DELETE SET NULL ON UPDATE SET NULL
)ENGINE=INNODB;

4. 联合查询

  1. UNION 去掉相同的记录
SELECT usrname FROM employee UNION SELECT username FROM cms_user;
  1. UNION ALL简单的合并
SELECT usrname FROM employee UNION ALL SELECT username FROM cms_user;

三. 子查询

1. 引发子查询的情况

子查询是将一个查询语句嵌套在另一个查询语句中。内层查询语句的查询结果，可以作为外层查询语句提供条件。

  1. 使用[NOT]IN的子查询
SELECT id FROM department;
SELECT id,username FROM employee WHERE depId IN(1,2,3,4)
SELECT id,username FROM employee WHERE depId IN(SELECT id FROM department);
SELECT id,username FROM employee WHERE depId NOT IN(SELECT id FROM department);
  1. 使用比较运算符的子查询
--创建student
CREATE TABLE IF NOT EXISTS student(
id TINYINT UNSIGNED AUTO_INCREMENT KEY ,
username VARCHAR (20) NOT NULL UNIQUE,
score TINYINT UNSIGNED
);

INSERT  student(username,score)  VALUES ('king',95),
('king1',95),
('king3',75),
('king2',85),
('king4',25),
('king5',90);
--scholarship
CREATE TABLE IF NOT EXISTS scholarship(
id TINYINT UNSIGNED AUTO_INCREMENT KEY ,
level TINYINT UNSIGNED
);
INSERT scholarship(level) VALUES (90),(80),(70);


--查询获得一等奖学金的学员有
SELECT level FROM scholarship WHERE id=1;
SELECT id,username FROM student WHERE score>=90;
SELECT id,username FROM student WHERE score>=(SELECT level FROM scholarship WHERE id=1);
  1. 使用[NOT]EXISTS的子查询
--查询部门中,判断内侧是不是真
SELECT * FROM department WHERE id=5;
SELECT id,username FROM employee WHERE EXISTS (SELECT * FROM department WHERE id=5);
SELECT id,username FROM employee WHERE NOT EXISTS (SELECT * FROM department WHERE id=5);
  1. 使用ANY]SOME或者ALL查询
    | | ANY | SOME | ALL
    |>,>=| 最小值|最小值｜最大值
    |<.<=| 最大值|最大值｜最小值
    ｜= | 任意值|任意值｜
    |<>.!= | | ｜任意值
--查询所有获得奖学金的学生
SELECT id,username,score FROM student WHERE score>=ANY(SELECT level FROM scholarship);
SELECT id,username,score FROM student WHERE score>=SOME(SELECT level FROM scholarship);
--查询获得一等奖学金的学生
SELECT id,username,score FROM student WHERE score>=ALL(SELECT level FROM scholarship);
--查询没有获得奖学金的学生
SELECT id,username,score FROM student WHERE score<ALL(SELECT level FROM scholarship);
--相当于IN
SELECT id,username,score FROM student WHERE score=ANY(SELECT level FROM scholarship);
SELECT id,username,score FROM student WHERE score=IN(SELECT level FROM scholarship);
--相当于NOT IN
SELECT id,username,score FROM student WHERE score<>ALL(SELECT level FROM scholarship);
SELECT id,username,score FROM student WHERE score= NOT IN(SELECT level FROM scholarship);

2. 将查询结果写入数据表中

CREATE TABLE test1(
id TINYINT UNSIGNED AUTO_INCREMENT KEY ,
number TINYINT UNSIGNED
);
INSERT test1(id,number)
SELECT id,score FROM student;

3. 创建数据表时同时将查询结果写入数据表中

CREATE TABLE test2(
id TINYINT UNSIGNED AUTO_INCREMENT KEY ,
number TINYINT UNSIGNED
)SELECT id,score FROM student;

CREATE TABLE test3(
id TINYINT UNSIGNED AUTO_INCREMENT KEY ,
score TINYINT UNSIGNED
)SELECT id,score FROM student;

四. 正则表达式查询

常用匹配方式

  1. ^匹配字符开始的地方
--查询用户名以t开始
SELECT * FROM cms_user WHERE username REGEXP '^t';
  1. $匹配字符结尾的地方
SELECT * FROM cms_user WHERE username REGEXP'g$';
  1. . 匹配字符串中的任意一个字符，包括回车和换行
SELECT * FROM cms_user WHERE username REGEXP'r..g';
  1. [字符集合]匹配字符集合中的任意一个字符
SELECT * FROM cms_user WHERE username REGEXP'[lto]';
  1. [^字符集合]匹配除了字符集合中的任意一个字符
SELECT * FROM cms_user WHERE username REGEXP'[^lto]';
  1. S1|S2|S3 匹配S1,S2,S3中的任意一个
SELECT * FROM cms_user WHERE username REGEXP'ng|qu|te';
  1. *代表0个1个或多个其前的字符
SELECT * FROM cms_user WHERE username REGEXP'que*';
  1. +代表1个或多个其前的字符
SELECT * FROM cms_user WHERE username REGEXP'que+';
  1. String{N}字符串出现N次
SELECT * FROM cms_user WHERE username REGEXP'que{2}';
  1. String{M,N}字符串至少出现M次,最多出现N次
SELECT * FROM cms_user WHERE username REGEXP'que{1，3}';
mangogogo321
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Mysql查询数据库表中前几条记录
weixin_30267697的博客
11-05 1924
Mysql查询数据库表中前几条记录问题，我想好多朋友也会碰到，下面我简单的说下我遇到的情况，且解决方法，希望对好多朋友有许多帮助： 下面是我数据库test中表student的数据，其中第二条记录被我删除了 在查询分析器中输入select*fromstudentlimit5;即可查询到下图的情况： 所以说，在Mysql中取出表中前几条记录是用关键字 limit,而在...
SQL 找出一个表中各个分类的前三条记录
liuyun1987的专栏
01-25 4235
SQL2000的写法一：select QuestionId,CategoryId,Title  from QA_Questions a where a.QuestionId(select max(c.QuestionId) from (select top 3  QuestionId  from QA_Questions b where b.CategoryId=a.CategoryId) c
Mysql数据库中，要查询前三条记录
weixin_30256901的博客
10-29 1875
select * from table limit 3 转载于:https://www.cnblogs.com/linson0116/p/4060216.html
oracle或mysql获取分组后每组的前三条数据
上善若泪
06-01 9693
转载于：https://blog.csdn.net/Alex_81D/article/details/104901075 mysql : select a.* from ( select t1.*,(select count(*)+1 from 表 where 分组字段=t1.分组字段 and 排序字段<t1.排序字段) as group_id from 表 t1 ) a where a.group_id<=3 oracle： SELECT t.* FROM (SEL
SQL中显示查询结果的前几条记录
流楚丶格念的博客
12-22 6244
在使用数据库查询语句的过程中，我们经常需要返回查询结果的前几条或者中间几条数据，下面是我总结的几种数据库常用的方法：（table是数据库表名，table2是table表的别名） 1、SQLServer sqlserver支持top关键字，返回前若干条数据。select top 5 * from table;// 返回前5行数据 2、MySQL mysql支持limit，只能适用于mysql。limit子句用于强制select语句返回置顶的记录数，接受一个或两个数字参数，必须是整数常量。一个参数是返回前几条记
mysql-connector-java-8.0.16.jar
01-20
- **下载**：可以从MySQL官方网站或第三方源下载mysql-connector-java-8.0.16.jar文件。 - **添加至项目**：将jar文件添加到Java项目的类路径中，这可以通过IDE（如Eclipse、IntelliJ IDEA）的设置完成，或者在...
mysql-connector-java-6.0.2.jar
最新发布
05-30
在实际开发中，`mysql-connector-java-6.0.2.jar`是连接MySQL数据库的基础，通过它，开发者可以方便地进行数据的增删改查操作，构建复杂的数据库应用。同时，随着技术的发展，更高级的框架如MyBatis、Hibernate等也...
mysql-cluster-8.0.20-winx64.zip
05-17
MySQL Cluster是一种高可用性、高性能的数据库解决方案，它在MySQL服务器的基础上增加了分布式数据存储和实时事务处理的能力。在“mysql-cluster-8.0.20-winx64.zip”这个压缩包中，我们找到了MySQL Cluster的...
ojdbc14+sqljdbc4-2.0+mysql-connector-java-5.1.41
09-19
本主题聚焦于三个主流数据库系统——Oracle、MySQL和SQL Server的数据库驱动，这些都是Kettle（Pentaho Data Integration）在进行数据集成时可能会用到的组件。 1. **Oracle JDBC驱动 (ojdbc14)**: Oracle JDBC...
mysql-5.7.27-winx64 压缩包.zip
07-04
3. 数据表操作：创建数据表，插入数据，查询数据，更新或删除数据，这些都是MySQL的基础操作，可以通过SQL语句完成。 六、安全与备份 1. 定期备份：为了防止数据丢失，定期对数据库进行备份至关重要。可以使用...
SQL查询语句之查询数据
热门推荐
m0_67402236的博客
08-02 1万+
查询数据是指从数据库中获取所需要的数据。查询数据是数据库操作中最常用，也是最重要的操作。用户可以根据自己对数据的需求，使用不同的查询方式。通过不同的查询方式，可以获得不同的数据。在MySQL中是使用SELECT语句来查询数据的。...
数据库查询操作DQL
qq_39818331的博客
06-04 795
数据库查询操作DQL 1.查询所有的信息 select * from cms_admin； 2.查询单独的一列或者几列 select id,username,role from cms_admin; select cms_admin.id,cms_admin.username,cms_admin.role from cms_admin； 3.给表明起别名 select id,username,role from cms_admin AS a; select a.id,a.username,a.ro
简单查询和联合查询两方面介绍SQL查询语句
aiqixiao2017的博客
02-02 185
　简单查询和联合查询两方面介绍SQL查询语句?br> 一、简单查询 　　　　　　简单的Transact-SQL查询只包括选择列表、FROM子句和WHERE子句。它们分别说明所查询列、查询的表或视图、以及搜索条件等。 　　　　　　例如，下面的语句查询testtable表中姓名为"张三"的nickname字段和email字段。 　　　　SELECT nickname,email 　　　...
常见的几中数据库查询前N条记录的方法
菜鸟_zhengke的博客
02-27 2317
1.ORACLE  SELECT * FROM TABLE1 WHERE ROWNUM&lt;=N （N代表的是前几行） 2. INFORMIX  SELECT FIRST N  * FROM TABLE1（N代表的是前几行） 3. DB2  SELECT * ROW_NUMBER() OVER(ORDER BY COL1 DESC) AS ROWNUM WHERE ROWNUM&lt;...
mysql如何只查询表中的前几条数据？多表查询前提了解
秃头的博客
09-15 4340
clazz是表名，0代表的是开始下标，默认就是第一条数据，2不是结束下标，而是你想在这一页显示多少条数据，比如你想显示5条数据，那就是0，5
查询多个课程成绩前三名的记录
骑大马挎大刀
02-15 1843
count(字段)，根绝字段判断为不为不空，根据字段定义，考虑要不要累加返回值，既然你引擎都返回值了，那我server层 “ +1 ” count(id),根据id主键取值，累加返回值，也是server层 “ +1 ” count(1)，同样会遍历，但不取值，引擎告诉不为空那我就 “+1” count(*),也不取值，而且人家还是经过优化的 方法一 ： 笛卡尔积（取 a 的最高分与本表比较） -- 方法一 ： 笛卡尔积 -- 思路：判断这个成绩是否为前三名，只要这个课程的所有成绩(最高分)和本表成绩进行.
MySQL注入实战指南——Sqli-labs教程解析
- **Less-23 至 Less-37** 包含了更多高级攻击场景，例如盲注、时间基注入、联合查询等。 5. **第三部分：Stacked Injection** **Background-8** 引入了堆叠注入的概念，这是一种将多条SQL语句组合在一起执行的...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值