牛客MySQL必知必会刷题总结

总的来说，关于要用having的时候会出错，此为不熟练。另外如通配符等函数运用属实是落后不了解。

1.

Mysql中表student_table(id,name,birth,sex)，插入如下记录：

('1001' , '' , '2000-01-01' , '男');
('1002' , null , '2000-12-21' , '男');
('1003' , NULL , '2000-05-20' , '男');
('1004' , '张三' , '2000-08-06' , '男');
('1005' , '李四' , '2001-12-01' , '女');

查询2001年及之后出生的男生、女生总数，正确的SQL是（）？

select sex**,**count(*) from student_table where birth >='2001' group by sex ;

//where用来卡条件，此题是先卡条件再计数。having是对计数后的结果做条件判断。

2.

Mysql中表student_table(id,name,birth,sex)，插入如下记录：

('1004' , '张三' , '2000-08-06' , '男');
('1005' , NULL , '2001-12-01' , '女');

('1006' , '张三' , '2000-08-06' , '女');

('1007' , ‘王五’ , '2001-12-01' , '男');

('1008' , '李四' , NULL, '女');

('1009' , '李四' , NULL, '男');

('1010' , '李四' , '2001-12-01', '女');

执行

select t1.*,t2.* from ( select * from student_table where sex = '男' ) t1 **right join** (select * from student_table where sex = '女') t2 on t1.birth = t2.birth and t1.name = t2.name ;
的结果行数是（）？

：4

right join意思是包含inner join的结果（左右表中的birth、name都不为NULL时才会匹配上），无法匹配t1中一个字段为NULL或两个字段都为NULL的记录。所以t1结果不含有'李四'、‘王五’。

RIGHT JOIN 关键字会返回右表 (t2) 所有的行（本题***4行），即使在左表 (t1) 中没有匹配的行。或者更多。如果右表有某记录与左表多条记录匹配（满足 on 子句条件）则 RIGHT JOIN 结果行数多于右表。

https://blog.csdn.net/Li_Jian_Hui_/article/details/105801454?time=1676533806327 不同的join最终会展示两个表的不同行数。

3.

Mysql中表student_table(id,name,birth,sex)，插入如下记录：

('1003' , '' , '2000-01-01' , '男');
('1004' , '张三' , '2000-08-06' , '男');
('1005' , NULL , '2001-12-01' , '女');

('1006' , '张三' , '2000-08-06' , '女');

('1007' , ‘王五’ , '2001-12-01' , '男');

('1008' , '李四' , NULL, '女');

('1009' , '李四' , NULL, '男');

('1010' , '李四' , '2001-12-01', '女');

执行

select count(t2.birth) as c1 from ( select * from student_table where sex = '男' ) t1 inner join (select * from student_table where sex = '女')t2 on t1.birth = t2.birth and t1.name = t2.name ;
的结果行数是（）？

解析：inner join意思是左右表中的birth、name都不为NULL时才会匹配上，结果中不含有一个字段为NULL或两个字段都为NULL的记录

使用count(*)对表中行的数目进行计数，不管表列中包含的是空值（null）还是非空值；

使用count（column）对特定列中具有值得行进行计数，不包含null值。

4.

现要把Course表中user_id为2的course_status更新为'学习SQL'，course_date更新为'2021-10-01'。下列MySQL语句中，正确的是：

更新多列数据的间隔符号是逗号，不是AND语句；

5.

INSERT命令无法批量更新数据

COALESCE用法顺口溜，帮助大家理解：

COALESCE,查空值，

按顺序不能乱；

核心规则要记牢；

不为空为自己；

为空就看下1位；

一直看下去；

除非只剩后1位；

为空就为它。

6.

链接：https://www.nowcoder.com/questionTerminal/78c494f70be54cf2ac93134b9e2aa7ba
来源：牛客网

Mysql中表student_table(id,name,birth,sex)，插入如下记录：

('1004' , '张三' , '2000-08-06' , '男')；

('1005' , NULL , '2001-12-01' , '女');

('1006' , '张三' , '2000-08-06' , '女');

('1007' , ‘王五’ , '2001-12-01' , '男');
('1008' , '李四' , NULL, '女');

('1009' , '李四' , NULL, '男');

('1010' , '李四' , '2001-12-01', '女');

执行

select count(t1.birth) as c1 from ( select * from student_table where sex = '男' ) t1 **right join** (select * from student_table where sex = '女') t2 on t1.birth = t2.birth and t1.name = t2.name ;
的结果行数是（）？

```
2
```
```
1 √
```
```
3
```
```
4
```

遇到这种题直接屏蔽了 count(*)，问的行数

7.

若要“查询选修了3门以上课程的学生的学号”，则正确的SQL语句是( )

SELECT Sno FROM SC GROUP BY Sno WHERE COUNT(*)> 3

SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*)> 3 √

SELECT Sno FROM SC ORDER BY Sno WHERE COUNT(*)> 3

SELECT Sno FROM SC ORDER BY Sno HAVING COUNT(*)> 3

若须引入聚合函数来对group by 结果进行过滤则只能用having。

HAVING语句通常与GROUP BY语句联合使用，用来过滤由GROUP BY语句返回的记录集。

HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。

1、where 后不能跟聚合函数，因为where执行顺序大于聚合函数。

2、where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。

3、having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件显示特定的组，也可以使用多个分组标准进行分组。

https://blog.csdn.net/qq_35269216/article/details/90812872

8.

几个数据库关键角色。

9.

下面哪些字符最可能会导致sql注入?

```
'（单引号）		√
```
```
/
```
```
"(双引号)
```
```
$
```

防止SQL注入，需要注意以下几个要点：

1.永远不要信任用户的输入。对用户的输入进行校验，可以通过正则表达式，或限制长度；对单引号和双"-"进行转换等。

2.永远不要使用动态拼装sql，可以使用参数化的sql或者直接使用存储过程进行数据查询存取。

3.永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。

4.不要把机密信息直接存放，加密或者hash掉密码和敏感的信息。

5.应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装

6.sql注入的检测方法一般采取辅助软件或网站平台来检测，软件一般采用sql注入检测工具jsky，网站平台就有亿思网站安全平台检测工具。MDCSOFT SCAN等。采用MDCSOFT-IPS可以有效的防御SQL注入，XSS攻击等。

10.

distinct是对搜索结果去重

select distinct prod_id from OrderItems;

加上排序，（order by 列名）默认按照某列升序排列，（order by 列名 desc ）则是降序排列。

having max( ) 取组内最大值

having sum( )也是取组内最大值

11.

SELECT [DISTINCT|DISINCTROW|ALL] select_expression,… – 查询结果
[FROM table_references – 指定查询的表
[WHERE where_definition] – where子句，查询数据的过滤条件
[GROUP BY col_name,…] – 对[匹配where子句的]查询结果进行分组
[HAVING where_definition] – 对分组后的结果进行条件限制
[ORDER BY{unsigned_integer | col_name | formula} [ASC | DESC],…] – 对查询结果进行排序
[LIMIT [offset,] rows] – 对查询的显示结果进行条数限制
[PROCEDURE procedure_name] --查询存储过程返回的结果集数据

12.

SQL_ERROR_INFO: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near"

程序异常退出, 请检查代码"是否有数组越界等异常"或者"是否有语法错误"。一般来说，出现这种情况大概率是语法错误。

#编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中包含 toy 一词的产品名称

#like 用法：[字符] like '%_[]字符' %表示任何字符出现任意次数,0或多次 _表示单个字符 []表示一个字符集
#REGEXP 正则匹配，'toy' 意味着查找记录中有toy的记录.效率不如like
select
    prod_name,
    prod_desc
from Products
#where prod_desc REGEXP 'toy'
where prod_desc LIKE '%toy%'


#仅返回描述中未出现 toy 一词的产品
SELECT prod_name,prod_desc
FROM Products
WHERE prod_desc NOT LIKE '%toy%';
#https://www.w3school.com.cn/sql/sql_wildcards.asp

13.

#编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），
#仅返回描述中同时出现 toy 和 carrots 的产品。
#有好几种方法可以执行此操作，但对于这个挑战题，请使用 AND 和两个 LIKE 比较。
SELECT prod_name,prod_desc

FROM Products

# WHERE prod_desc LIKE '%toy%' AND prod_desc LIKE '%carrots%';#行

# WHERE prod_desc LIKE '%toy%' AND '%carrots%';不行

WHERE prod_desc LIKE '%carrots%toy%';#行,按同时出现的顺序来排列

# WHERE prod_desc LIKE '%toy%carrots%';不行

14.

对表头进行重命名用AS

#重命名
select vend_id,vend_name as vname,vend_address as vaddress,vend_city as vcity
from Vendors 
order by vend_name

15.

#sale_price 是一个包含促销价格的计算字段。可以乘以 0.9，得到原价的 90%（即 10%的折扣）
#sale_price这一列原先并不存在，逻辑上是prod_price * 0.9 as sale_price
SELECT   
  prod_id,   
  prod_price,  
  prod_price * 0.9 sale_price
 #prod_price * 0.9 as sale_price
FROM
  Products

题解 | SQL81#顾客登录名#

分析

关键词：substing,concat,upper

用法：

字符串的截取：substring(字符串，起始位置，截取字符数）
字符串的拼接：concat(字符串1，字符串2，字符串3,...)
字母大写：upper(字符串）

代码

#编写 SQL 语句，返回顾客 ID（cust_id）、顾客名称（cust_name）和登录名（user_login），其中登录名全部为大写字母，并由顾客联系人的前两个字符（cust_contact）和其所在城市的前三个字符（cust_city）组成。提示：需要使用函数、拼接和别名。
select cust_id,cust_name,upper(concat(substring(cust_name,1,2),substring(cust_city,1,3))) as user_login
from Customers

16.

#编写 SQL 语句，返回顾客 ID（cust_id）、顾客名称（cust_name）和登录名（user_login），其中登录名全部为大写字母，并由顾客联系人的前两个字符（cust_contact）和其所在城市的前三个字符（cust_city）组成。提示：需要使用函数、拼接和别名。
SELECT cust_id,cust_name, 
upper(concat(substring(cust_name,1,2),substring(cust_city,1,3))) AS user_login
From Customers;
#字符串的截取：substring(字符串，起始位置，截取字符数）
#字符串的拼接：concat(字符串1，字符串2，字符串3,...)
#字母大写：upper(字符串）

17.☆☆☆△

#编写 SQL 语句，返回 2020 年 1 月的所有订单的订单号（order_num）和订单日期（order_date），并按订单日期升序排序
#6种解法

#用like来查找
select order_num, order_date 
from Orders
where order_date like '2020-01%'
order by order_date

#切割字符串
select order_num, order_date 
from Orders
where left(order_date, 7) = '2020-01'
order by order_date

#字符串比较
select *
from Orders
where order_date >= '2020-01-01 00:00:00' and order_date <= '2020-01-31 23:59:59'
order by order_date;

#用正则来查找（效率不如like，能用like就用like）
select order_num, order_date 
from Orders
where order_date regexp '2020-01'
order by order_date

#时间函数匹配
select order_num, order_date 
from Orders
where year(order_date) = '2020' and month(order_date) = '1'
order by order_date

#利用date_formal函数 （参考其中的匹配规则进行匹配）
select order_num, order_date 
from Orders
where date_format(order_date, '%Y-%m')='2020-01'
order by order_date

18.

分组聚合一定不要忘记加上 group by ,不然只会有一行结果!!

#请编写 SQL 语句，返回订单数量总和不小于100的所有订单号，最后结果按照订单号升序排序。

select order_num
from OrderItems
#自己写的时候缺下面一行代码,并且用的是having而非where
group by order_num
having sum(quantity) >= 100	#题目说的是总和，故要用sum而非count
order by order_num asc;

#where---过滤过滤指定的行，即where后面只能跟表中存在的字段。后面不能加聚合函数（分组函数）
#having--过滤分组，必须与group by连用

#编写 SQL 语句，根据订单号聚合，返回订单总价不小于1000 的所有订单号，最后的结果按订单号进行升序排序。总价 = item_price 乘以 quantity

select order_num, sum(item_price*quantity) total_price	#为什么此处要用sum？
from OrderItems
group by order_num
having sum(item_price*quantity) >= 1000
order by order_num asc;	#加asc运行时间会比默认升序快个几毫秒

19.

查两张不同的表

SQL91 使用子查询，返回购买价格为 10 美元或以上产品的顾客列表，结果无需排序。
注意：你需要使用 OrderItems 表查找匹配的订单号（order_num），然后使用Order 表检索这些匹配订单的顾客 ID（cust_id）

#方法在本科学过
select distinct b.cust_id
from OrderItems.a, Orders b
where a.order_num = b.order_num
and item_price >= 10

#返回购买 prod_id 为BR01 的产品的所有顾客的电子邮件（Customers 表中的 cust_email），结果无需排序。
#除了这一种还有连续嵌套的方法，即如下
SELECT 
    cust_email
FROM
    Customers
WHERE
    cust_id IN (
        SELECT 
            cust_id
        FROM
            Orders
        WHERE
            order_num IN (
                SELECT
                    order_num
                FROM
                    OrderItems
                WHERE
                    prod_id = 'BR01'
            )
    );

#编写 SQL 语句，从 Products 表中检索所有的产品名称（prod_name），以及名为 quant_sold 的计算列，
#！！！其中包含所售产品的总数（在 OrderItems 表上使用子查询和 SUM(quantity)检索）。！！！

select prod_name, 
(select sum(quantity) from OrderItems where OrderItems.prod_id = Products.prod_id group by prod_id)  quant_sold
from Products;

#除了返回顾客名称和订单号，返回 Customers 表中的顾客名称（cust_name）和Orders 表中的相关订单号（order_num），
#添加第三列 OrderTotal，其中包含每个订单的总价，并按顾客名称再按订单号对结果进行升序排序。

select a.cust_name, b.order_num, sum(quantity *item_price) OrderTotal
from Customers a, Orders b, OrderItems c
where b.order_num = c.order_num and a.cust_id = b.cust_id
group by a.cust_name,b.order_num
order by 1,2;

20.

INNER JOIN，又称内连接。主要通过设置连接条件的方式，来移除查询结果中某些数据行的交叉连接

#语法格式：SELECT <字段名> FROM <表1> INNER JOIN <表2> [ON子句]
#字段名：需要查询的字段名称。<表1><表2>：需要内连接的表名。INNER JOIN ：内连接中可以省略 INNER 关键字，只用关键字 JOIN。ON 子句：用来设置内连接的连接条件。

#多个表内连接时，在 FROM 后连续使用 INNER JOIN 或 JOIN 即可。
SELECT s.name,c.course_name FROM tb_students_info s INNER JOIN tb_course c ON s.course_id = c.id;

21.

题解 | SQL47#将两个 SELECT 语句结合起来（一）#

发表于 2022-03-05 13:09

分析

关键词：union

用法：

join---连接表，对列操作
union--连接表，对行操作。
union--将两个表做行拼接，同时自动删除重复的行。
union all---将两个表做行拼接，保留重复的行。

思路：

筛选条件：like用法。where quantity=100，where prod_id like 'BNBG%'
排序：放在最后进行排序，不能先排序在拼接。order by prod_id

代码

# 将两个 SELECT 语句结合起来，以便从 OrderItems表中检索产品 id（prod_id）和 quantity。
# 其中，一个 SELECT 语句过滤数量为 100 的行，另一个 SELECT 语句过滤 id 以 BNBG 开头的产品，最后按产品 id 对结果进行升序排序。
select prod_id,quantity
from OrderItems
where quantity=100
union 
select prod_id,quantity
from OrderItems
where prod_id like 'BNBG%'
order by prod_id;

# 使用子句
select *
from OrderItems
where quantity in (
    select quantity
    from OrderItems
    where quantity = 100
    )
    or prod_id like "BNBG%"
order by prod_id;

#编写 SQL 语句，组合 Products 表中的产品名称（prod_name）和 Customers 表中的顾客名称（cust_name）并返回，然后按产品名称对结果进行升序排序。
#最终结果只有prod_name一列数据，故需要处理cust_name这一列数据

select prod_name from Products
union 
select cust_name as prod_name from Customers
order by prod_name

UNION对于处理表的行关系真的很重要，真的很重要！！！！！

MySQL进阶挑战

1.

INSERT INTO 插入

SQL插入多行数据的方法

普通插入（全字段）：INSERT INTO table_name VALUES (value1, value2, ...)
普通插入（限定字段）：INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...)
多条一次性插入：INSERT INTO table_name (column1, column2, ...) VALUES (value1_1, value1_2, ...), (value2_1, value2_2, ...), ...
从另一个表导入：INSERT INTO table_name SELECT * FROM table_name2 [WHERE key=value]

#INSERT INTO tab_name (col_name) VALUES (要插入的数据,这里是第一行数据), (要插入的数据,这里是第二行数据)...(要插入的数据，第n行数据);
#方法一： 可以指定插入的列名
INSERT INTO exam_record (uid, exam_id, start_time, submit_time, score) VALUES
(1001, 9001, '2021-09-01 22:11:12', '2021-09-01 23:01:12', 90),
(1002, 9002, '2021-09-04 07:01:02', NULL, NULL);

#方法二：把id的值设置为NULL或0，这样MySQL会自己处理这个自增的id列。
INSERT INTO exam_record VALUES
(NULL, 1001, 9001, '2021-09-01 22:11:12', '2021-09-01 23:01:12', 90),
(NULL, 1002, 9002, '2021-09-04 07:01:02', NULL, NULL);


#方法三：直接填入id值
INSERT INTO exam_record VALUES
(1, 1001, 9001, '2021-09-01 22:11:12', '2021-09-01 23:01:12', 90),
(2, 1002, 9002, '2021-09-04 07:01:02', NULL, NULL);

REPLACE INTO 带更新的插入

跟与insert into功能类似，不同点在于：replace into 首先尝试插入数据到表中，

如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据；
否则，直接插入新数据。

要注意的是：插入数据的表必须有主键或者是唯一索引！否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据。

UPDATE SET修改记录的方式汇总：

设置为新值：UPDATE table_name SET column_name=new_value [, column_name2=new_value2] [WHERE column_name3=value3]
根据已有值替换：UPDATE table_name SET key1=replace(key1, '查找内容', '替换成内容') [WHERE column_name3=value3]

细节剖析：tag为PYTHON的tag字段全部修改为Python

#思路实现：本题采用两种修改方式均可，语义为『当tag为PYTHON时，修改tag为Python』，
#先用第一种:
UPDATE examination_info
SET tag = "Python"
WHERE tag = "PYTHON";
#采用第二种
UPDATE examination_info
SET tag = REPLACE(tag, "PYTHON", "Python")
WHERE tag = "PYTHON";

#思维扩展：第二种方式不仅可用于整体替换，还能做子串替换，例如要实现将tag中所有的PYTHON替换为Python（如CPYTHON=>CPython），可写作：	
UPDATE examination_info
SET tag = REPLACE(tag, "PYTHON", "Python")
WHERE tag LIKE "%PYTHON%";

replace对一整条记录的值做修改 update可以只修改某个或者某些记录的值

posted @ 2023-03-05 09:54 Ankitone 阅读(410) 评论(0) 编辑收藏举报

刷新页面返回顶部

Ankitone

牛客MySQL必知必会刷题总结

1.

2.

3.

4.

5.

6.

7.

8.

9.

10.

11.

12.

13.

14.

15.

16.

17.☆☆☆△

18.

19.

20.

21.

MySQL进阶挑战

1.

公告